1.数据库字符集

1.1字符集

字符集: 可见字符在保存和传输的时候对应的二进制代码集合. 
字符集在两个地方用到

1.保存数据
2.数据传输12

1.2在存续时使用字符集

1. MySQl可以在服务器上, 数据库, 表, 字段上指定字符编码.2. 在服务器上指定字符编码是安装服务器的时候指定的.3. 数据库, 表, 字段上是创建时候指定的123

create table test(
-> name varchar(10) charset utf8
->)charset=gbk;123

1.3 gbk, gb2312, utf8的区别

这三种都是常用的字符编码方式, gbk和gb2312(自带)是简体中文的编码, utf8是国际通用编码. 
Python, MySQL, jQuery等等都是开源的产品, 开源产品建议使用utf8编码 
gbk和gb2312都是简体中文, 但是内部编码方式是不一样的. 
在python中

在gbk或gb2312下，一个中文占用2个字节
在utf8下，一个中文占用3个字节12

注意:在数据库中如果字符编码是utf8的，一个汉字字符长度就是1，gbk一个汉字的字节长度是2。

create table `emp`(
sex varchar(21844)
)engine=myisam charset=utf8;65535/3-1create table `emp2`(
sex varchar(32766)
)engine=myisam charset=gbk;65535/2-1123456789101112

注意;数据库如果是UTF8,那么你在此库中建立的’表’如果没有设定字符集,默认使用数据库的字符集 

1.4 在传输的时候字符编码

 
 
mysql 服务器接收的编码

mysql> show variables like 'collation%'; 查看排序集mysql> show variables like 'character_%';
+--------------------------+-----------------------------------------------+| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+| character_set_client     | utf8  #服务器接受客户端的字符码                 |
| character_set_connection | utf8                                          |
| character_set_database   | gbk                                           |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8  #返回结果的字符编码                       |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          || character_sets_dir       | C:\phpStudy\PHPTutorial\MySQL\share\charsets\ |
+--------------------------+-----------------------------------------------+8 rows in set (0.00 sec)123456789101112131415

现在客户端发送的编码和服务器接收的编码不一致

解决:#windows默认的是GBKset character_set_client=gbk;字符编码不一样返回的就不一样返回的是utf8   windows不认识会乱码解决:set character_set_results=gbk;小技巧:set names gbk; ->set names 可以一次设置修改3个字符编码;mysql> show variables like 'character_%';+--------------------------+-----------------------------------------------+| Variable_name            | Value                                         |+--------------------------+-----------------------------------------------+| character_set_client     | gbk                                           || character_set_connection | gbk                                           || character_set_database   | gbk                                           || character_set_filesystem | binary                                        || character_set_results    | gbk                                           || character_set_server     | utf8                                          || character_set_system     | utf8                                          || character_sets_dir       | C:\phpStudy\PHPTutorial\MySQL\share\charsets\ |+--------------------------+-----------------------------------------------+8 rows in set (0.00 sec)1

2.校对集

在某种字符集下, 字符和字符的关系成为校对集. 比如(ASCII)a和B的大小关系, 如果区分大小写 a>B, 如果不区分大小写a

#utf8_general_ci  使用_ci这种校对集不区分大小写create table t1(
name char(1)
)charset=utf8 collate=utf8_general_ci;create table t2(
name char(1)
)charset=utf8 collate=utf8_bin;insert into t1 values ('a'),('B');insert into t2 values ('a'),('B');1234567891011

mysql> select * from t1 order by name;
+------+| name |
+------+| a    || B    |
+------+2 rows in set (0.00 sec)

#排序查询以后没有区分大小写说明a和b不区分大小写;12345678910

mysql> select * from t2 order by name;
+------+| name |
+------+| B    || a    |
+------+2 rows in set (0.00 sec)

#按照正序排列,对照ASCLL编码,B<a,B在a前面那么这种二进制校对集区分大小写;12345678910

牢记:
_bin：按二进制编码比较
_ci：不区分大小写比较123

| Charset  | Description                 | Default collation   | Maxlen |12

#显示所有校对集mysql> show collation;12

3.MySQL的数据类型–值类型

(1)整型

(2)unsigned(无符号)

一个数是无符号数, 那么这个数肯定是非负数 
数据库mysql第一条记录一定是1(起码是),绝对不能是0

无符号数的范围相当于是有符号数的两倍.

mysql> create table test1(
    -> age tinyint unsigned    -> );Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (128);
Query OK, 1 row affected (0.00 sec)1234567

(3)显示宽度

整形支持显示宽度, 显示宽度就是最小的显示位数, 比如int(11)表示最少用11 
位数字表示这个数, 如果不够用0来做前导. 默认情况下显示位数不起作用, 必须集合zerofill才起作用

create table stu(
id int(5),
age int(5) zerofill
);

#插入测试数据
insert into stu values (1,23),(2,123456);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from stu;
+------+--------+| id   | age    |
+------+--------+|    1 |  00023 ||    2 | 123456 |    #超过显示宽度5就显示值本身
+------+--------+1234567891011121314151617

显示宽度不决定整形的显示大小, 只是在值的位置不够的时候用前导0来填充, 如果超过显示位数, 就不加前导0, 显示值本身

(4)浮点型

create table test2(
  num1 float,
  num2 double
  );insert into test2 values(23.123,2.123);#float理论上只保留小数点一位,根据实际版本
#double理论上只保留小数点两位, 根据实际版本insert into test2 values(2.2E2,2.123);#也支持科学计数法insert into test2 values(999.999999999999999999999999999,2.123456);#丢失精度1234567891011121314

python中我们没有双精度这一类型, float代表浮点数.

(5)定点数

decimal(M,D),M的最大值是65,D的最大值是30,默认是(10,0)

create table test3(    num1 float(10,2),    num2 decimal(10,2)    );insert into test3 values(123.57,1234567.00);mysql> select * from test3;
+-------------+-------------+| num1        | num2        |
+-------------+-------------+| 12345679.00 |      123.57 || 12345679.00 | 12346623.57 |
+-------------+-------------+2 rows in set (0.00 sec)


#定点数可以保留多个小数点位,它在有些情况下也会失去精度.浮点数的执行效率要高于定点数.
#浮点数和定点数都支持 无符号.1234567891011121314151617181920

4.MySQL的数据类型–字符类型

char(10)和varchar(10)

    相同点:都是不能超过10个字符
    不同点:char你输入5个字符,它还是占用了10个字符
        varchar你输入5个字符,他会返还多于的空间        char的最大长度是255
        varchar的理论长度是65535
        实际上达不到,我使用的是gbk(mysql中占用2个字符),那么65535/2.12345678

create table test(
  name varchar(32766)
  )charset=gbk;#utf8在创建的时候报错了,最多支持21845个字符,因为utf8一个汉字要使用3个字符create table test(
  name varchar(65535)
  )charset=gbk

#utf8在创建的时候报错了,最多支持32767个字符,因为gbk一个汉字要使用2个字符123456789101112

5.MySQL数据类型–枚举(enum)

列出一些选项的, 单选.

create table test(
name varchar(32),
sex enum('男','女','保密')
);insert into test values('贾乃亮','男');#插入正常insert into test values('PGONE','人妖');#插入报错123456789

枚举型在数据库内部是通过整型来管理的,第一个值1,第二个值是2,依次向后推insert into test values('王宝强',1);insert into test values('刘运斌',3);枚举型的优点:
1.限制值
2.节省空间
3.运行效率高
思考:已知枚举型占用2个字节,请问最多可以设置多少个枚举值?

2个字节16位,2^16-1,最多可保存65535/2个值12345678910111213

6.MySQL数据类型–集合(set)

列出数据类型, 保存多选

create table test2(
name varchar(32),
hobby set('吃','睡觉','看书','鉴黄')
);insert into test2 values('刘运斌','吃,睡觉,鉴黄');insert into test2 values('刘运斌-2','吃,睡觉,李小璐');1234567

集合和枚举一样,为每个元素分配一个固定的值,分配方式不一样.
它的管理也是使用整型
集合是这么分配的:'吃','睡觉','看书','鉴黄'2^0   2^1    2^2   2^3  如果后面还有依次向后推
保存的时候,把多个值(转换为整型了)加起来,这样值会边的很大(浪费资源)
一个集合他的元素通过一个位表示,有几个集合元素就需要几个位.

思考:已知集合占用8个字节,那么集合能保存多少个选项?
只能保存64个选项.1234567891011

7.MySQL数据类型–日期时间型

(1)datetime

#格式 年-月-日 时:分:秒create table test4(
create_at datetime
);insert into test4 values('2018-01-12 15:00:53');insert into test4 values(now());#表示的是当前的时间insert into test4 values('10000-12-31 23:59:59'); #错误12345678

(2)date

仅表示时间的日期部分

(3)time

表示时间部分, 也表示时间间隔, 范围是-838:59:59 ~ 838:59:59

create table test5(
create_at time);insert into test5 values('12:12:12');insert into test5 values('212:12:12');insert into test5 values('-800:12:12');insert into test5 values('-839:59:59');  #错误的,超出范围

#time支持以天的方式表示时间间隔insert into test5 values('10 12:59:59');1234567891011

(4)timestamp

从1970年1月1日 00:00:00秒的格林威治时间开始计算,在python中是特殊类型,但在mysql中显示为datatime格式

create table test6(
create_at timestamp);insert into test6 values(now());insert into test6 values('2018-01-12 15:43:30');insert into test6 values('2038-01-19 11:14:07');#这里就是一个节点

\insert into test6 values('2038-01-19 11:14:08');#超过了4个字节
timestamp在格式上和datetime是一样的,它们的区别在于:
datetime 从1到9999,而timestamp从1970年~2038年.
timestamp占用4个字节,到2038年超过4个字节的长度了.12345678910111213

(5)year

占用一个字节, 只能是255个数, 以1900年为基数, 范围1900+1 ~ 1900+255.

create table test7(
create_at year);insert into test7 values(1900);#错误的insert into test7 values(1901);insert into test7 values(2155);insert into test7 values(2156);#错误的,不在范围内12345678

8.布尔型(boolean)

mysql不支持布尔型, 所以用1和0代替

create table test8(
num boolean
);insert into test8 values(True);insert into test8 values(1);insert into test8 values(False);insert into test8 values(0);insert into test8 values('A');#错误

布尔型的使用是比较少的,可以用tinyint代替,或者用enum代替1234567891011

9.列属性–是否为空(null|not null)

create table test9(
id int auto_increment primary key not null comment'主键',
username varchar(64) not null,
pwd char(64) null);insert into test9 values(null,'admin','');insert into test9 values(null,'','');insert into test9 values(null,null,'');insert into test9 values(null,'',null);#空字符串不等于null123456789101112

10.列属性–(default)

create table test10(
id int auto_increment primary key not null comment'主键',
username varchar(64) not null,
pwd char(64) null default '123456');insert into test10(username) values('admin2');#推荐的SQL语句create table test11(
age int  default '123456');insert into test11 values(null);#输入null加插入null,不写是默认1234567891011121314

11.自动增长列

auto_increment
字段值默认从1开始,每次递增1,特点:不会有重复的值,主键常用.

友情提示:在mysql中自动增长的列必须作为主键.
自动增长的烈在插入的时候可以输入(null)

被删除的id不能被再次使用:
如果中间的摸个ID值比较大,那么后面插入的id根据最大值依次+112345678

12.列属性–主键

primary key
主键:主键是唯一的
特点:不能为空,也不能重复
一个表只有一个主键CREATE TABLE `test12` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `num` int(11) NOT NULL,  `age` int(11) NOT NULL,  PRIMARY KEY (`id`,`num`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;#以上的主键有'3'个,其实一个主键,这个3个字段组成(一个联合主键),在开发中几乎不会被用上
#(教学使用)create table demo1(
id int auto_increment primary key);create table demo2(
id int auto_increment ,primary key(id)
);create table demo3(
id int(11) 
);alter table demo3 add primary key(id);选择主键的原则:
1.最少性:必须选择单个键作为主键
2.稳定性:作为主键的这个字段最好不要被修改123456789101112131415161718192021222324252627282930313233

13.列属性–唯一键(unique)

特点:不能重复, 不能为空 
一个表可以有多个唯一键

create table test13(
uid int auto_increment primary key,
mobile char(11) unique,
email char(32) unique,
pwd char(32)
);insert into test13 values(null,'13877776666','this_dog@qq.com','123456');insert into test13 values(null,'13877776665','this_do@qq.com','123456');#修改表属性方法添加唯一键alter table test13 add unique `my`(mobile);#删除唯一键还是使用删除的方法alter table test13 drop index mobile;#有别名删除别名,没别名删除字段名12345678910111213141516

14.列属性–备注(comment)

备注用来给程序员相互交流使用的
有点:起码你能看的动这个字段是用来干吗的.

stauts tinyint comment'描述一个人的状态 1表示死了,2表示活的,0表示半死不活' 1234

15.SQL注释

python的注释# 单行注释'''

''' 多行注释

mysql的注释# 单行注释--单行注释

/*注释的内容*/ 多行注释1234567891011

select * from test13 where uname=” ” or 1=1 –” ” and pwd=” “; 
练习

#手机号码一般使用什么数据类型储存?字符型 char(11)#性别使用什么数据类型?字符型 (枚举型) 布尔型#年龄用什么?整型 tinyint unsigned#照片用什么?binary  字符(只保存路径,不保存资源)
varchar()#工资用什么类型?decimal 定点型 
#学员的姓名允许为空吗?不允许#家庭地址可以为空吗?最好不可以#电子邮箱可以为空吗?对于目前来说有一点分量,随意,最好不用#考试成绩可以为空?不允许 default 0 #在主键列输入的数值,能为空吗?肯定不能 null当作占位符#一个表可以有多个主键吗?不可以#在一个学校的数据库中,如果这个学校允许学员重名, 班级不允许有重名的,那么把班级和学生的姓名作为组合主键可以吗?primary key(id,name)
可以,但是不合理#标识列允许使用字符串类型吗?可以,但不允许#表中没有合适的列(字段)作为主键怎么办?自己添加一个自增长的字段作为主键

原文地址https://blog.csdn.net/qq_41637554/article/details/80859645