SQL数据类型

MySQL配置文件及常用语句

• 字符编码与配置文件

• show status
  可以使用show status查看系统运行的实时状态，便于做出相应优化，不可认为更改

• show variables
  查看系统参数，系统默认设置或者dba调整后的参数，静态的，可以通过set或者配置文件更改

• MySQL字符相关

Mysql5.6之前版本的编码需要人为统一，之后的版本默认统一

5.6版本可以通过修改配置文件来永久修改编码配置

vim /etc/my.conf
[mysqld]  # 服务端
# add 
charcter-set-server=utf8mb4
collation-server=utf8mb4_general_ci

[client]  # 第三方客户端
default-character-set=utf8mb4

[mysql]  # mysql自己的客户端
default-character-set=utf8mb4

上述更改为utf8mb4 可以支持表情，比utf8支持更多

• 数据库存储引擎

数据库正对数据采用的存储方式

# 查看常用的存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)
mysql> 

# 解析
1. engine 字段是数据库的存储引擎
2. Support 字段是当前数据库支持和不支持以及默认使用的引擎(yes/no/default)
3. comment 字段是针对于这个引擎的说明

# 这里需要了解一下4个存储引擎
1. MyISAM
	MySQL5.5之前默认使用的存储引擎
	该引擎存储速度比较快，但是功能较为少，不支持数据库事务，安全性低
2. InnoDB
	MySQL5.5之后默认使用的存储引擎
	该引擎支持事务，行级锁，外键等，安全性高，但是速度与MyISAM相比较为弱
3. Memory
	该引擎基于内存存取数据，仅仅用于临时表数据读取，当数据库重启之后数据全部消失
4. BlackHole
	该引擎会将数据写入黑洞，类似于linux 下的/dev/null块设备，给什么吃什么

接下来看一下这个四个存储引擎在创建表的时候底层的数据文件的创建

# 分别创建4张不同的表
mysql> use db1
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table t1(id int) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2(id int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t3(id int) engine=memory;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t4(id int) engine=blackhole;
Query OK, 0 rows affected (0.01 sec)

[root@VM-4-10-centos db1]# ll
总用量 152
-rw-r----- 1 mysql mysql    65 11月 23 16:26 db.opt
# 创建的t1表，frm：表结构 ibd：表索引
-rw-r----- 1 mysql mysql  8556 11月 23 16:31 t1.frm
-rw-r----- 1 mysql mysql 98304 11月 23 16:31 t1.ibd
# 创建的t2表，frm：表结构 MYD：表数据 MYI：表索引
-rw-r----- 1 mysql mysql  8556 11月 23 16:32 t2.frm
-rw-r----- 1 mysql mysql     0 11月 23 16:32 t2.MYD
-rw-r----- 1 mysql mysql  1024 11月 23 16:32 t2.MYI
# 创建的t3表， frm：表结构
-rw-r----- 1 mysql mysql  8556 11月 23 16:32 t3.frm
# 创建的t4表， frm：表结构
-rw-r----- 1 mysql mysql  8556 11月 23 16:32 t4.frm
[root@VM-4-10-centos db1]#

# 接下来向这4张表里inster数据后进行查看，验证特性
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into t4 values(1);
Query OK, 1 row affected (0.00 sec)
# 这里发现就算想t4中insert数据也是没有任何结果的
mysql> select * from t4;
Empty set (0.00 sec)

# t3 的数据由于保存在内存中 所以这里需要从新启动，看一下是否在内存中保存的数据
mysql> exit
Bye
[root@VM-4-10-centos ~]# systemctl restart mysqld
[root@VM-4-10-centos ~]# mysql -uroot -p
Enter password:
······
mysql>
mysql> select * from t3;
Empty set (0.00 sec)
# 这里发现当数据库从新启动之后数据确实没有保存
"""
在数据库的5.6之后版本使用的是InnoDB引擎
"""

• 创建表的完整语法

create table tablename(
	字段名 字段类型(数字) 约束条件,
	字段名 字段类型(数字) 约束条件
)
"""
1. 字段名和字段类型是必须得
2. 数字和约束条件是可选的
3. 约束条件可以写多个，空格分隔
4. 最后一行结尾不能加顿号
"""

• 字段类型-整形

类型	字节	其他
tinyint	1bytes	正负号(占1bit)
smallint	2bytes	正负号(占1bit)
int	4bytes	正负号(占1bit)
bigint	8bytes	正负号(占1bit)

# 验证默认整型是否携带正负号
# 创建一个类型为tinyint类型的字段
create table t5(id tinyint);
insert into t5 values(-129), (128);
# 由于数据库的严格模式，所以插入时会直接报错，这里由于需要测试，先将严格模式提前删除，稍后介绍时再添加回来 
# 插入数据的结果是-128和127，这里也就意味着正负号默认占用了1bit

# 如何取消正负号，使用unsigned取消正负号，结果将会不准确，无法使用
create table t6(tinyint unsigned)
insert into t6 values(-129),(128),(1000);
# 这里会发现结果如下
0，128，255

• 严格模式

在使用数据库存储数据时，如果数据不符合规范，正常数据库都是会报错
这是应为数据库配置中的一行配置文件实现的效果
在5.7版本之前数据库中都会有一行配置
sql_mode=strict_trans_tables

1. 临时修改 在当前客户端有效
2. set session sql_mode='strict_trans_tables'
3. 当前服务端有效
4. set global sql_mode='strict_trans_tables'
5. 永久修改
6. 直接更改配置文件

• 字段类型-浮点型

数字1： 共存储位数
数字2：小数点后面的位数
float(20,10)
double(20,10)
decimal(20,10)

# 3者区别精确度不同
mysql> create table t7(id float(60,20))
    -> ;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t8(id double(60,20));
Query OK, 0 rows affected (0.03 sec)

mysql> create table t9(id decimal(60,20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t7 values(1.11111111111111111111);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t8 values(1.11111111111111111111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t9 values(1.11111111111111111111);
Query OK, 1 row affected (0.00 sec)

# 查询的存储结果
mysql> select * from t7;
+------------------------+
| id                     |
+------------------------+
| 1.11111116409301760000 |
+------------------------+
1 row in set (0.00 sec)

mysql> select * from t8;
+------------------------+
| id                     |
+------------------------+
| 1.11111111111111120000 |
+------------------------+
1 row in set (0.00 sec)

mysql> select * from t9;
+------------------------+
| id                     |
+------------------------+
| 1.11111111111111111111 |
+------------------------+
1 row in set (0.00 sec)

"""
每一种精度不同
核心精度区别
float < double < decimal
"""

字段类型-字符串类型

1. char 定长
2. varchar 变长
   区别：
   char(4) ：最多存储4个字符，超出就报错，不够四个字符使用空格填充到四个
   varchar(4): 最多存储4个字符，超出就报错，不够几位则存几位

• char VS varchar
  char
  优势:整存整取 速度快
  劣势:浪费存储空间
  varchar
  优势:节省存储空间
  劣势:存取数据的速度较char慢

mysql> create table t10(id int , name char(4));
Query OK, 0 rows affected (0.03 sec)

mysql> create table t11(id int, name varchar(4));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t10 values(1, 'wesl');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t11 values(1, 'wesl');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t11 values(1, 'li');
Query OK, 1 row affected (0.00 sec)

"""
1. 内置函数char_length()获取字段存储的数据长度
2. 默认情况下MySQL针对char的存储会自动填充空格和删除空格
3. 数据库中参数数据库中需要配置参数验证sql_mode='pad_char_to_full_length'
"""

• 类型中数字的含义

数字在很多地方都是用来表示限制存储数据的长度 
	但是在整型中数字却不是用来限制存储长度
 
create table t12(id int(3));  不是用来限制长度
insert into t12 values(12345);

create table t13(id int(5) zerofill);  而是用来控制展示的长度
insert into t13 values(123),(123456789);

create table t14(id int);

"""以后写整型无需添加数字"""

• 字段类型- 枚举
  多选一： 提供多个选项只能选择一个

# 设定gender的值只能是male female others
mysql> create table t15(id int, name varchar(32), gender enum('male', 'female', 'others'));
Query OK, 0 rows affected (0.03 sec)

# 没有猛男直接报错
mysql> insert into t15 values(1,'wesley','猛男');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
# 有male insert成功
mysql> insert into t15 values(1, 'wesley', 'male');
Query OK, 1 row affected (0.00 sec)

mysql>

• 字段类型- 集合
  多对一和多对多

# 创建一个可以多选的爱好
mysql> create table t16(id int, name varchar(16), hobbies set('basketabll','footabll','doubleclorball'));
Query OK, 0 rows affected (0.03 sec)

# 设定没有的爱好，直接报错
mysql> insert into t16 values(1,'wesley','study');
ERROR 1265 (01000): Data truncated for column 'hobbies' at row 1
# 设定已有的爱好，insert成功
mysql> insert into t16 values(1,'wesley','doubleclorball');
Query OK, 1 row affected (0.01 sec)
# 设定多个已有的爱好，insert成功
mysql> insert into t16 values(2,'wesley2','doubleclorball,footabll');
Query OK, 1 row affected (0.00 sec)

• 字段类型- 日期
  datetime 年月日时分秒
  date 年月日
  time 时分秒
  year 年

mysql> create table t17(id int, name varchar(16), register_time datetime, birthday date, study_time time, work_time year);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t17 values(1, 'wesley','2000-11-11 11:11:11','1998-01-21','11:11:11','2000');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t17;
+------+--------+---------------------+------------+------------+-----------+
| id   | name   | register_time       | birthday   | study_time | work_time |
+------+--------+---------------------+------------+------------+-----------+
|    1 | wesley | 2000-11-11 11:11:11 | 1998-01-21 | 11:11:11   |      2000 |
+------+--------+---------------------+------------+------------+-----------+
1 row in set (0.00 sec)

"""
注意：如果在代码中操作数据库，时间数据更多是获取系统时间写入数据库
"""