MySQL数据库篇：字符编码与配置文件、存储引擎、创建表语法、MySQL字段类型及约束条件

2022.5.4MySQL字符编码配置及字段

• 字符编码与配置文件介绍
• 存储引擎
• 创建表的完整语法
• MySQL字段类型
• MySQL字段约束条件

一、字符编码与配置文件

1、查看MySQL默认字符编码

复制代码
1
2
3
\s
# 如果是5.x系列，显示的编码又多种>>>：latin1、gbk
# 如果是8.x系列，显示的统一是>>>：utf8mb4（utf8优化版本，支持存储表情）

2、统一字符编码

5.X默认编码有多种 可能会导致乱码的情况 所以应该统一编码；

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# MySQL安装列表中找到配置文件
my-default.ini  # 配置文件

# 更改配置文件
步骤1:拷贝一份该配置文件并修改名称为my.ini
步骤2:清空my.ini文件内的内容
步骤3:添加固定的配置信息如下：
    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
    [client]
    default-character-set=utf8
    [mysql]
    default-character-set=utf8
步骤4:保存并重启服务端即可生效
    net stop mysql  # 关闭服务端
    net start mysql  # 打开服务端

二、存储引擎

1、什么是存储引擎

可以理解为处理数据的不同方式，不同的存储引擎存储的特点各不相同；

2、查看存储引擎

复制代码
1
show engines;

3、需要了解的引擎

MyISAM:

5.1之前版本MySQL默认的存储引擎；

特点：存取数据速度快，但功能很少，安全性较低；

InnoDB:

5.1之后版本MySQL”默认“的存储引擎；

特点:有诸多功能，安全性较高，但存取速度没有MyISAM快；

BlackHole:

任何写入的数据都会立刻消失（类似于垃圾回收站）；

Memory:

以内存作为数据存储地，速度快但是断电立刻丢失；

4、自定义选择存储引擎

复制代码
1
2
3
4
5
6
7
8
create table t1(id int)engine=myisam;
create table t2(id int)engine=innodb;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=memory;

# 思考？
1.不同存储引擎产生的表文件有几个？
2.研究blackhole与memory存取数据的特征

三、创建表的完整语法

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
# 创建表的完整语法
create table 表名(
	字段名1 字段类型(数字) 约束条件;
	字段名2 字段类型(数字) 约束条件;
	字段名3 字段类型(数字) 约束条件;
);  # 创建有三个字段的表
注意：
1.字段名称和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件可以写多个 空格隔开即可
	eg：字段名 字段类型 约束条件1 约束条件2
4.最后一行字段结尾不能加逗号！否则会报错，此细节容易被忽略！

四、字段类型

1、字段类型之整型

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 整型字段类型
	tinyint							1bytes				
	smallint						2bytes
	int								4bytes
	bigint							8bytes
  上述整型的区别在于从上往下能够存储的数字范围越来越大
  注意事项
  	1.需要考虑正负数的问题,如果需要存储负数,则需要占据一个比特位;
    2.注意手机号如果使用整型来存储 需要使用bigint才可以;
    
"""
工作小技巧:有时候看似需要使用数字类型存储的数据其实可能使用的是字符串
	因为字符串可以解决不同语言对数字不精确的缺陷!!!
"""
整形的范围如下表：

整数类型	字节	有符号范围	无符号范围
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT/INTEGER	4	-2147483648 ~ 2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
eg：
# 创建表t5，字段类型为tinyint
	create table t5(id tinyint);
# 添加超出字段类型范围的数字
	insert into t5 values(-129),(256);
# 如果是在5.6版本不会报错，会自动处理成最大范围(没有意义)
因此，可以设置全局sql_mode,调整范围，步骤如下>>>:
    步骤1：set global sql_mode = 'STRICT_TRANS_TABLES';
    步骤2：退出客户端，重新登录
    注意：
    （1）全局修改的话，需高级权限，仅对下次连接生效；
    （2）再下一次MySQL重启时会重新读取配置文件里对应值，如果需永久生效需要修改配置文件里的值。
# 如果是在5.7及以上版本 则会直接报错(更加合理)
验证(结论)发现所有的整型都默认带有正负号,如何修改不带正负号呢，可以添加一个约束条件unsignd,使符号失效；
	create table t6(id tinyint unsigned);

2、字段类型之浮点型

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 浮点型字段类型
    float 
    double
    decimal
上述浮点型从上往下精确度越来越高>>>:
	float(255,30)  # 总共255位，小数位占30位
    double(255,30)  # 总共255位，小数位占30位
    decimal(65,30)  # 总共165位，小数位占30位
重点研究精度问题>>>:
    create table t7(id float(255,30));
    create table t8(id double(255,30));
    create table t9(id decimal(65,30));
    
# 验证不同类型的精确长度
	insert into t7 values(1.11111111111111111);
    # 1.111111164093017600000000000000
    # 精确到小数点后7位
    
    insert into t8 values(1.11111111111111111);
    # 1.111111111111111200000000000000
    # 精确到小数点后15位
    
    insert into t9 values(1.11111111111111111);
    # 1.111111111111111110000000000000
    # 精确到小数点后17位，与原数据相同
结论：  精确度>>> decimal>double>float

"""
虽然三者精确度有差距 但是具体用哪个应该结合实际情况
	比如正常业务 使用float足够
	如果是高精尖 可以使用decimal
"""

3、字段类型之字符类型

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
# 1.字符类型
    char
    varchar
上述两个字符类型的区别在于一个是定长一个是变长
区别>>>:
    char(4)
    	定长，最大只能存储4个字符，超出则报错，不够则空格填充至四个
    varchar(4)
    	变长，最大只能存储四个字符，超出则报错，不够则有几个存几个
# 2.验证定长与变长特性
	create table t10(name char(4));
    create table t11(name varchar(4));
    insert into t10 values('jason');
    # ERROR 1406 (22001): Data too long for column 'name' at row 1
    insert into t11 values('jason');
    # ERROR 1406 (22001): Data too long for column 'name' at row 1

如果是5.6版本并且没有修改严格模式，则会自动截取四个字符，这样并不合理，失去了设置长度的意义；

# 解决方法
    # 修改全局sql_mode（临时修改）
        步骤1；set global sql_mode = 'STRICT_TRANS_TABLES';
        步骤2：退出客户端，重新登录
    # 永久修改
        修改my.ini配置文件
        sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY'
        重启服务端后永久生效

# 补充知识
	char_length()  # 获取字段数据的长度
    该方法无法直接获取到定长的真实长度，因为MySQL在存数据的时候会自动填充空格在取数据的时候又会自动移除空格然后展示；
    # 如何让MySQL在取数据的时候不自动移除空格？
    单次修改>>>:
        set session sql_mode = 'pad_char_to_full_length';
        
# 工作中使用char还是varchar？
	char
    整存整取，速度快；但会造成一定的资源浪费；
    varchar
    节省存储空间；取数据速度没有char块且不知道数据的精确长度
    '''
    varchar在存数据的时候会生成一个1bytes的报头 记录数据长度
    varchar在取数据的时候先会读取1bytes的报头 从中获取真实数据长度
 	  1bytesjason1bytes+kevin1bytes+tony
    '''
    使用场景>>>:
        针对统一中国人的姓名，应该采取那个类型？
        >>> varchar
        规模较小，数据量相对固定的字典？
        >>> char

4、字段类型括号内数字的含义

（1）字段类型括号内的数字大部分情况下是用来限制存储的长度；
（2）但是在整型中并不是用来限制长度，而是用来控制展示长度；

复制代码
1
2
3
4
5
6
7
8
# 验证int类型括号内数字的作用
	create table t12(id int(3));
    insert into t12 values(1111);  # 不会报错
    select * from t12;  # id 1111
    
    create table t13(id int(3) zerofill);  # 使用约束条件zerofill>>>:数据不够用0填充
    insert into t13 values(1),(1111);
    select * from t13;  # id 001 1111

结论：以后涉及整型int字段，都无需自己定义长度，使用自带的即可，针对其他类型字段需要自己添加数字设置长度；

5、字段类型之枚举与集合

（1）枚举

​ 多选一

作用：给某字段设置指定选项，针对某字段只能添加指定的选项

复制代码
1
2
create table t14(gender enum('male','female','others'));
insert into t14 values('jason');  # 报错，Data truncated for column 'gender' at row 1

（2）集合

​ 多选多（也可多选一）

作用：给某字段设置多个可选选项，同时可以选取多个或一个

复制代码
1
2
3
4
create table t15(name varchar(32),
                 hobby set('篮球','足球','排球'));
insert into t15 values('jason','篮球,排球');  # 给某字段添加多个值

6、字段类型之日期类型

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
date						年月日
datetime					年月日时分秒
time						时分秒
year						年
# 创建带日期字段的表
create table t16(
	id int,
  	name varchar(32),
  	reg_time datetime,
  	birth date,
  	study_time time,
  	join_time year
);
# 针对时间数据一般都是通过代码自动获取并添加，我们可以手动模拟
insert into t16 values(1,'jason','2000-11-11','2002-01-21','11:11:11','2015');
#  1 | jason | 2000-11-11 00:00:00 | 2002-01-21 | 11:11:11   |      2015

五、MySQL字段约束条件

1、字段类型与约束条件的关系

约束条件是基于字段类型之上的额外限制

复制代码
1
2
3
4
eg: 
create table t(id int unsigned);
  # 字段类型int规定了id字段只能存整数 
  # 约束条件unsigned指的是整数基础之上还必须是正数

2、约束条件类型

（1）unsigned

无需正负号

复制代码
1
create table t(id int unsigned);  # 无符号的整型，即必须是正整数

（2）zerofill

零填充

复制代码
1
create table t(id int(3) zerofill);  # 不够展示3位的用0填充  

（3）not null

非空

复制代码
1
2
3
4
create table t18(id int,name varchar(32) not null);  # name为非空字段
insert into t18(id) values(2);  # 报错，因为name字段未添加值
insert into t18(id,name) values(2,null);  # 报错，null也属于空
insert into t18(id,name) values(2,'');  # 不报错！空字符串可以 

（4）default

默认值

复制代码
1
2
3
create table t19(id int,name varchar(32) default 'jason');  # 设置name字段default默认值为jason
insert into t19(id) values(1);  # 不传name字段则默认为jason
insert into t19(id,name) values(2,'kevin');  # name字段传值kevin则不适用默认值jason

（5）unique

唯一值

复制代码
1
2
3
4
5
6
7
8
9
10
'''单列唯一:某个字段下对应的数据不能重复 是唯一的'''
create table t20(id int,
    name varchar(32) unique);  # name字段设置不能重复

  '''多列唯一:多个字段下对应的数据组合到一起的结果不能重复，是唯一的'''
create table t21(
  	id int,
    host varchar(32),
    port int,
    unique(host,port));  # 设置多个字段组合不能重复

（6）primary key

主键

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 主键
1.单从约束层面上而言 相当于not null + unique(非空且唯一)
  	create table t22(id int primary key);
    
2.是InnoDB存储引擎规定的一张表有且必须要有一个主键 用于构建表
  	主键可以加快数据的查询速度(类似于书的目录)
    
ps:如果创建表创建的时候没有设置主键也没有其他的键 那么InnoDB会采用一个隐藏的字段作为表的主键(隐藏就意味着而无法使用 即无法加快数据查询)
    
3.如果没有主键但是有非空且唯一的字段 那么会自动升级成主键(从上往下的第一个)
  		create table t23(
  			tid int,
  			pid int not null unique,
  			cid int not null unique
  		);

# 联合主键（了解）
create table t24(
  	id int,
    name varchar(32),
    pwd int,
    primary key(id,pwd)  # 设置多个字段组合为主键
  );

（7）auto_increment

自增

复制代码
1
2
3
4
5
6
7
8
9
10
11
# 专门配合主键一起使用，用户以后在添加数据的时候就不需要自己记忆主键值
create table t25(
  	id int primary key auto_increment,  # 设置id字段数据自增一
    name varchar(32)
  );
'''
总结：
	以后在创建规范的表的时候
		一般都会有一个主键字段的编写如下
			id int primary key auto_increment
'''

自增特性

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 自增不会随着数据删除而回退
    create table t1(
        id int primary key auto_increment,
        name varchar(32)
    );
    insert into t1(name) values('jason'),('kevin'),('tony');  # 添加三个数据
    delete from t1;  # 删除所有数据
    insert into t1(name) values('jack');
    select * from t1;  # id=4,name=jack

# 删除数据并重置主键值>>> truncate 表名
    truncate t1;

    insert into t1(name) values('mark');
    select * from t1;  # id=1,name=mark