MySQL 数据类型、列属性、数据完整性

发表于 | 分类于

数据类型

数据类型——值类型

整型
整型 占用字节 范围 范围
tinyint 1 -2^7~2^7-1 -128~127
smallint 2 -2^15~2^15-1 -32768~32767
mediumint 3 -2^23~2^23-1 -8388608~8388607
int 4 -2^31~2^31-1 -2147483648~2147483647
bigint 8 -2^63~2^63-1 -

一个字节=8位，8位就是8个二进制数

  • 整型支持显示宽度：最小的显示位数，比如int(5)，如果数值的位数小于5位，前面加上前导0。比如输入12，显示00012；大于5位就不添加前导0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> describe student;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type                     | Null | Key | Default | Extra          |
+-------+--------------------------+------+-----+---------+----------------+
| id    | int(11)                  | NO   | PRI | NULL    | auto_increment |
| name  | char(1)                  | YES  |     | NULL    |                |
| num   | int(8) unsigned zerofill | YES  |     | NULL    |                |
+-------+--------------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> select * from student;
+----+------+------------+
| id | name | num        |
+----+------+------------+
|  1 | a    |   00002485 |
|  2 | b    |   03678928 |
|  3 | c    | 1234567890 |
+----+------+------------+
3 rows in set (0.00 sec)

脚下留心：必须结合zerofill才起作用
显示宽度不决定显示范围，只是在数值不够指定位数时用0做前导

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> alter table student add num2 tinyint;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into student values (null,'d','1',128);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from student;
+----+------+------------+------+
| id | name | num        | num2 |
+----+------+------------+------+
|  1 | a    |   00002485 | NULL |
|  2 | b    |   03678928 | NULL |
|  3 | c    | 1234567890 | NULL |
|  4 | d    |   00000001 |  127 |
+----+------+------------+------+
4 rows in set (0.00 sec)

超出最大范围会有警告提示，同时值保存最大范围值

  • 无符号整数（unsigned）：无符号数没有负数，正数部分是有符号的两倍
1
2
3
4
5
6
7
8
9
10
mysql> insert into student values (null,666);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from student;
+----+------+
| id | num  |
+----+------+
|  7 |  255 |
+----+------+
1 row in set (0.00 sec)
浮点型（保存近似值小数）
浮点型 占用字节 范围
float（单精度） 4 -3.4E+38~3.4E+38
double（双精度） 8 -1.8E+308~1.8E+308
  • 浮点数声明: float(M,D) double(M,D)
    M：总位数
    D：小数位数

浮点数也是支持显示宽度和无符号数

  • 浮点数支持科学计数法
    insert into uiste values (5E2,4E-1);
1
2
3
4
5
6
7
8
9
10
11
mysql> insert into student values (null,5E2,4E-1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------------+------+
| id | num1         | num2 |
+----+--------------+------+
|  7 |         NULL | NULL |
|  8 | 000000000500 | 00.4 |
+----+--------------+------+
2 rows in set (0.00 sec)
  • 浮点的精度可能会丢失【指的是小数】
    insert into uiste values (99.999999999);
1
2
3
4
5
6
7
8
9
10
11
12
mysql> insert into student values (null,9.99999999999999,1.1111111111111);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+--------------+------+
| id | num1         | num2 |
+----+--------------+------+
|  7 |         NULL | NULL |
|  8 | 000000000500 | 00.4 |
|  9 | 000000000010 | 01.1 |
+----+--------------+------+
3 rows in set (0.00 sec)

小数部分如果超过D的限制会四舍五入，整数部分不能超过M-D的限制

  • 定点数
    语法：decimal(M,D)

定点数是变长的，大致每9个数字用4个字节来存储。定点数之所以能保存精确的小数，因为整数和小数是分开存储的。占用的资源比浮点数要多。
定点数和浮点数都支持显示宽度和无符号数

数据类型——字符型

数据类型 描述
char(L) 定长
varchar(L) 变长
tinytext 2^8-1=255
text 2^16-1=65535
mediumtext 2^24-1
longtext 2^32-1

char(10)和varchar(10)的区别？
答：相同点：它们最多只能保存10个字符；
不同点：char不回收富余的字符，varchar会回收富余的字符。
char效率高，浪费空间，varchar节省空间，效率比char低。

char的最大长度是255。varchar理论长度是65535字节,实际根本达不到。具体长度与字符编码有关

大块文本（text）不计算在总长度中,一个大块文本只占用10个字节来保存文本的地址。

数据类型——枚举（enum）

从集合中选择一个数据（单选）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> create database uiste charset=utf8;
Query OK, 1 row affected (0.01 sec)

mysql> create table student(
    -> name varchar(20),
    -> sex enum('男','女','保密')
    -> )charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into student values ('杜敏','男');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values ('钟程程','男或女');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from student;
+-----------+------+
| name      | sex  |
+-----------+------+
| 杜敏      | 男   |
| 钟程程    |      |
+-----------+------+
2 rows in set (0.00 sec)

使用枚举进行数据选择的时候，没有屏蔽错误信息只是提示了警告信息

MySQL的枚举类型是通过整数来管理的，第一个值是1，第二个值是2，以此类推
既然枚举在数据库内部存储的是整数，那么可以直接插入数字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select sex+0 from student;
+-------+
| sex+0 |
+-------+
|     1 |
|     0 |
+-------+
2 rows in set (0.01 sec)

mysql> insert into student values ('赵哥',2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+-----------+------+
| name      | sex  |
+-----------+------+
| 杜敏      | 男   |
| 钟程程    |      |
| 赵哥      | 女   |
+-----------+------+
3 rows in set (0.00 sec)

枚举的优点：
1、 运行速度快（数字比字符串运算速度快）
2、 限制数据
3、 节省空间

已知枚举占用2个字节，请问最多有多少个枚举值？
答：2个字节=16位，可以保存216个数字（0-65535），枚举是从1开始，所以枚举最多可以有65535个枚举值

数据类型——集合（set)

从集合中选择一些数据（多选）

1
2
3
4
5
mysql> create table student(
    -> name char,
    -> hobby set('看书','玩游戏','敲代码','看电视')
    -> )charset=utf8;
Query OK, 0 rows affected (0.03 sec)

插入集合的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> insert into student values ('杜敏','玩游戏');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into student values ('小兰','看书,敲代码');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into student values ('兰','敲代码,看书');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+------------------+
| name | hobby            |
+------+------------------+
| 杜   | 玩游戏           |
| 小   | 看书,敲代码      |
| 兰   | 看书,敲代码      |
+------+------------------+
3 rows in set (0.00 sec)

插入的顺序不一样，但是显示的顺序是一样的

集合和枚举一样，每个集合的元素都分配一个固定的值，分配的方式从左往右按2的0、1、2、…次方

如果是多选，他们的关系是按位或
按位与：相同的位上都是1，结果才为1，只要有一个为0，结果为0。
按位或：相同的位上只要有一个为1结果就是1。

1
2
3
4
5
6
7
8
9
mysql> select hobby+0 from student;
+---------+
| hobby+0 |
+---------+
|       2 |
|       5 |
|       5 |
+---------+
3 rows in set (0.01 sec)

思考：已知集合占用8个字节，最多可以表示几个选项？
答：8个字节=64位，一个位表示1个选项，最多可以表示64个选项。

数据类型——日期类型

数据类型 描述
datetime 日期时间，占用8个字节
date 日期
time 时间
timestamp 时间戳 占用4个字节
year 年份 占用1个字节
  • datetime 格式：年-月-日 小时:分钟:秒

测试

1
2
3
4
mysql> insert into test10 values ('2015-6-24 15:15:15');	--正确
mysql> insert into test10 values ('100-6-24 15:15:15');		--正确
mysql> insert into test10 values ('9999-6-24 15:15:15');	--正确
mysql> insert into test10 values ('10000-6-24 15:15:15');	--错误

datetime保存范围是：1~9999年

  • timestamp：时间戳
1
2
3
4
mysql> insert into test11 values ('2015-6-24 15:15:15');	--正确的
mysql> insert into test11 values ('1969-12-31 23:59:59');	--超过范围
mysql> insert into test11 values ('2038-01-19 11:14:08');	--超出范围
mysql> insert into test11 values ('2038-01-19 11:14:07');	--正确

datetime类型timestamp类型在表现上是一样的。他们的区别：
datetime是从1到9999，而timestamp从1970年~2038年，2038年01月19日11:14:07秒以后就超出timestamp范围了。

  • year
    因为只占用1个字节，最多只能表示255个年份，范围是1901-2155之间的年份

  • time
    表示时间或时间间隔，范围是-838:59:59~838:59:59

1
2
3
4
5
mysql> insert into test13 values (12:12:12);	--正确
mysql> insert into test13 values ('212:12:12');	--正确
mysql> insert into test13 values ('-838:59:59');--正确
mysql> insert into test13 values ('838:59:59');	--正确
mysql> insert into test13 values ('839:00:00');	--错误

time支持以天的方式插入

insert into student values ('10 12:12:12');

数据类型——boolean

MySQL不支持boolean类型，true和false在数据库中对应1和0。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> create table test1(
    -> `field` boolean
    -> )charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> describe test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| field | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)

创建数据表的数据类型

手机号码一般使用什么数据类型存储? char
性别一般使用什么数据类型存储? char enum
年龄信息一般使用什么数据类型存储? tinyint
照片信息一般使用什么数据类型存储? binary(二进制)
薪水一般使用什么数据类型存储? decimal

一个字段到底选数字还是字符，取决于有没有计算的可能，如果没有计算的可能即使是数字也要用字符类型，比如手机号、QQ号，…

列属性-是否为空(null | not null)

null：可以为空
not null：不可以为空

列属性——默认值（default）

如果一个字段没有插入值，可以默认插入一个指定的值
没有插入的字段插入默认值
default关键字用来插入默认值

列属性——自动增长（auto_increment）

字段的值从1开始，每次递增1，特点就在字段中的数据不可能重复，适合为记录生成唯一的id

在MySQL中，auto_increment必须是主键。但是主键不一定是自动增长的

既然从1开始，每次递增1，所以一般自动增长都是无符号整数

如果要给自动增长列插入数据，使用null关键字。
自动增长列上的数据被删除，默认情况下此记录的编号不再使用。

思考题：在一个自动增长列上，插入三行，删除两行，插入三行，删除两行，插入三行，删除两行，再插入一个记录编号是多少？
答：编号从10开始。删除记录与自动增长的编号没有关系

列属性——主键（primary key）

主键：唯一标识表中记录的一个或一组列
主键的特点：不能重复，不能为空
一个表只能有一个主键，主键可以有多个字段组成

主键的作用：

  • 保证数据完整性
  • 加快查询速度
添加主键：方法一【创建表的时候添加主键】
1
2
3
4
5
mysql> create table stu(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)
添加主键：方法二【创建表的时候添加主键】
1
2
3
4
5
mysql> create table stu(
    -> id int,
    -> name varchar(20),
    -> primary key(id)
    -> );
添加主键：方法三【更改表的时候添加主键】
1
2
3
4
5
6
7
8
9
mysql> create table stu(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> alter table stu add primary key (id)
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
查看主键

mysql>desc stu;

创建组合键
1
2
3
4
5
6
mysql> create table stu(
    -> id int,
    -> name varchar(20),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.00 sec)

也可以通过更改表的方式来创建组合键
alter table stu add primary key(id,name)

删除主键

mysql>alter table stu drop primary key;

选择主键的原则

1、 最少性：尽量选择一个字段做主键
2、 稳定性：尽量选择更新少的列做主键
3、 尽量选择数字型的列做主键

主键思考题

在主键列输入的数值，允许为空吗? 不能为空
一个表可以有多个主键吗? 不能
在一个学校数据库中，如果一个学校内允许重名的学员，但是一个班级内不允许学员重名，可以组合班级和姓名两个字段一起来作为主键吗？可以
标识列（自动增长列）允许为字符数据类型吗？ 不允许
表中没有合适的列作为主键怎么办？ 添加一个自动增长列
如果标识列A的初始值为1，增长量为1，则输入三行数据以后，再删除两行，下次再输入数据行的时候，标识值从多少开始？ 从4开始

列属性——唯一键

特点：不能重复，可以为空
一个表可以有多个唯一键
作用：
1、 保证数据完整性
2、 加快数据访问

添加唯一键【创建表的时候添加唯一键】

unique key
unique

添加唯一键【创建表的时候添加唯一键】

unique key(stuname)
unique(stuadd)

设置名字的唯一键

unique key `name` (`stuname`)
name：唯一键名字
stuname：字段名字

添加唯一键【修改表的时候添加唯一键】
1
2
3
4
5
6
mysql> create table stu(
    -> id int primary key,
    -> stuname varchar(20),
    -> stuadd varchar(100)
    -> );
Query OK, 0 rows affected (0.00 sec)

添加唯一键

1
2
mysql> alter table stu add unique key(stuname);   --key可以省略
mysql> alter table stu add unique (stuadd);

一次添加多个唯一键
mysql>alter table stu add unique(stuname),add unique(stuadd);

查看唯一键

mysql>show create talbe stu\G

添加组合唯一键
删除唯一键

通过唯一键的名字来删除唯一键
语法：alter table 表名 drop index 唯一键名称
mysql>alter talbe stu drop index stuname;

列属性——备注（comment）
SQL注释

单行注释：–或#
多行注释：/ /

数据完整性

保证实体完整性

1、 主键约束
2、 唯一约束
3、 自动增长列

保证域完整性

1、 数据类型约束
2、 非空约束
3、 默认值约束

保证引用完整性

1、外键约束：从表中的公共字段是主表的外键

保证自定义完整性

1、 存储过程
2、 触发器

主表和从表

两个表建立关系（两个表只要有公共字段就有关系），一个表称为主表，一个表称为从表。
外键约束可以实现：
1、 主表中没有的从表中不允许插入
2、 从表中有的主表中不允许删除
3、 不能更改主表中的值而导致从表中的记录孤立存在。
4、 先删除从表，再删除主表

外键（foreign key）

1、 外键：从表中的公共字段，公共字段的名字可以不一样，但是数据类型必须一样。
2、 外键约束用来保证引用完整性

添加外键

foreign key (id) references stuinfo(stuno);
方法一：创建表的时候添加外键

references：参照

测试：

  • 主表中没有的，从表中不允许插入
1
2
3
4
5
6
先插主表，在插从表

mysql> insert into stuinfo values (1,'tom');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stumarks values (1,77);
Query OK, 1 row affected (0.00 sec)

  • 从表中有的，主表中不能删除

  • 不能更改主表的数据后使得从表的数据孤立

方法二：修改表的时候添加外键
语法：alter table stumarks add foreign key (id) references stuinfo(stuno);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> create table stuinfo(
    -> stuno char(4) primary key,
    -> stuname varchar(20) not null
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create table stumarks(
    -> id char(4) primary key,
    -> score int
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> alter table stumarks add foreign key (id) references stuinfo(stuno);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

要创建外键必须是innodb引擎，myisam不支持外键约束

创建外键的时候，给外键取名
alter table stumarks add constraint `FK1` foreign key(id) references stuinfo(stuno);

constraint：约束

删除外键

通过外键的名字删除外键
语法：alter table 表名 drop foreign key `外键名`

1
mysql>alter talbe stumarks drop foreign key `FK1`

如果要在某个字段上添加外键约束，改字段必须要有索引才可以。如果该字段已经存在索引，则直接使用，否则MySQL自动生成索引。

外键操作

1、 严格操作（前面讲的是严格操作）
2、 置空操作（set null）：如果主表记录删除或更新，从表置空
3、 级联操作（cascade）：如果主表记录删除或更新，从表级联

一般来说：主表删除的时候，从表置空操作，主表更新的时候，从表级联操作。
语法：foreign key(外键) references 主表(关键字段) [主表删除时候的动作] [主表更新时候的动作]

1
2
3
4
5
6
7
mysql> create table stumarks(
    -> id int auto_increment primary key,
    -> stuno char(4),
    -> stuscore tinyint(3),
    -> foreign key(stuno) references stuinfo(stuno) on delete set null on update cascade
    -> );
Query OK, 0 rows affected (0.03 sec)
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
--错误案例--
create table stuinfo(
stuno char(4) primary key,
stuname varchar(20) not null
);

create table stumarks(
id char(4) primary key,
score int
);

alter table stumarks add constraint FK1 foreign key (id) references stuinfo(stuno) on delete set null on update cascade;

--正确案例--
create table stuinfo(
stuno char(4) primary key,
stuname varchar(20) not null
);

create table stumarks(
id1 char(4) primary key,
id char(4),
score int
);

alter table stumarks add constraint FK1 foreign key (id) references stuinfo(stuno) on delete set null on update cascade;

使用alter修改表 去创建外键时，是否不能够同时指定制空约束和级联约束？
主键不能set null ,所以外键表中新增主键，将外键作为非主键，此问题与alter的设置无关
foreign key (stuno) references stuinfo(stuno) on delete set null on update cascade;

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
51
52
53
54
55
56
57
58
59
60
61
62
63
mysql> create table stuinfo(
    -> stuno char(4) primary key,
    -> stuname varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)
//主键与非主键之间的关系，主键是不能set null的，所有使用非主键作为外键
mysql> create table stumarks(
    -> id int auto_increment primary key,
    -> stuno char(4),
	    -> score int,
	    -> foreign key (stuno) references stuinfo(stuno) on delete set null on update cascade
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into stuinfo values ('1001','tom'),('1002','berry');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into stumarks values (null,'1001',88),(null,'1002',99);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from stumarks;
+----+-------+-------+
| id | stuno | score |
+----+-------+-------+
|  1 | 1001  |    88 |
|  2 | 1002  |    99 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql> select * from stuinfo;
+-------+---------+
| stuno | stuname |
+-------+---------+
| 1001  | tom     |
| 1002  | berry   |
+-------+---------+
2 rows in set (0.00 sec)

mysql> delete from stuinfo where stuno='1001';
Query OK, 1 row affected (0.00 sec)

mysql> select * from stumarks;
+----+-------+-------+
| id | stuno | score |
+----+-------+-------+
|  1 | NULL  |    88 |
|  2 | 1002  |    99 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql> update stuinfo set stuno='1003' where stuno='1002';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stumarks;
+----+-------+-------+
| id | stuno | score |
+----+-------+-------+
|  1 | NULL  |    88 |
|  2 | 1003  |    99 |
+----+-------+-------+
2 rows in set (0.00 sec)

再来一戳，外键级联

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ALTER TABLE stumarks DROP FOREIGN KEY `FK1`;

DROP TABLE
IF EXISTS stuinfo;

DROP TABLE
IF EXISTS stumarks;

CREATE TABLE stuinfo(
    stuno CHAR(4) PRIMARY KEY ,
    stuname VARCHAR(10)
);

CREATE TABLE stumarks(
    id INT auto_increment PRIMARY KEY ,
    stuno CHAR(4) UNIQUE KEY,
    stuscore TINYINT(3) ,
    CONSTRAINT `FK1` FOREIGN KEY(stuno) REFERENCES stuinfo(stuno) ON DELETE
SET NULL ON UPDATE CASCADE
);

经验总结：设置级联外键由于 on delete set null 的原因不能设置外键为主键，解决外键数据完整的方法是给外键增加为一件 unique key;