MySQL学习（二、列属性和数据完整性）

目录

1、数据类型

1.1 值类型

1.1.1  整型

1.1.2  浮点型（保存近似值小数）

1.1.3  定点数

1.2  字符型

1.3  枚举（enum）

1.4  集合（set）

1.5  日期

1.6  boolean

1.7  练习题

1.8 列属性——是否为空（null | not null）

1.9 列属性——默认值（default）

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

1.11 列属性——主键（primary key）

1.11.1 添加主键

1.11.2 创建组合键

1.11.2 查看主键

1.11.3  删除主键

1.11.4 选择主键的原则

1.11.5 主键思考题

1.12 列属性——唯一键

1.12.1 添加唯一键

1.12.2 查看唯一键

1.12.3 删除唯一键

1.13 列属性——备注（comment）

1.14 SQL注释

1.15 数据完整性介绍

1.15.1 保证实体完整性

1.15.2 保证域完整性

1.15.3 保证引用完整性

1.16 引用完整性

1.16.1 主表和从表

1.16.2 外键（foreign key）

1.16.3 添加外键

1.16.4 查看外键

1.16.5 删除外键

1.17 外键操作

1.18 客户端介绍

---

1、数据类型

1.1 值类型

1.1.1  整型

       作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

提示：无符号整数（unsigned）:无符号没有负数，正数部分是有符号的两倍。

整型支持显示宽度（最小的显示位数） 比如int(5)，如果数值的位数小于5位，前面加上前导0。比如输入12，显示00012；大于5位就不添加前导0。

注意：必须结合zerofill才起作用

1.1.2  浮点型（保存近似值小数）

       FLOAT类型用于表示近似数值数据类型。SQL标准允许在关键字FLOAT后面的括号内选择用位指定精度(但不能为指数范围)。MySQL还支持可选的只用于确定存储大小的精度规定。0到23的精度对应FLOAT列的4字节单精度。24到53的精度对应DOUBLE列的8字节双精度。

浮点型	占用字节	范围
float（单精度）	4	-3.4E+38~3.4E+38
double（双精度）	8	-1.8E+308~1.8E+308

浮点数声明：float(M,D)     double(M,D) 

M：总位数

D：小数位数

如果精度超过允许范围，会四舍五入。

超过允许位数范围会报错：

浮点数存的是近似值，精度可能丢失。

1.1.3  定点数

decimal(M,D)

定点数是将小数的整数部分和小数部分分开保存，定点数不知道用几个字节，它是变长的。

1.2  字符型

数据类型	描述	长度
char	定长	最大255
varchar	变长	最大65535
tinytext	大段文本	2^8-1=255
text	大段文本	2^16-1=65535
mediumtext	大段文本	2^24-1
longtext	大段文本	2^32-1

1）char(10)和varchar(10)的区别？

答：相同点：它们最多只能保存10个字符；

       不同点：char不回收富余的字符，varchar会回收多余的字符；

                      char效率高，浪费空间，varchar节省空间，效率比char低。

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

编码格式不同，实际的长度也不同。

一条记录的长度不能超过65535.

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

1.3  枚举（enum）

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

报错原因：只能插入枚举值。

MySQL的枚举类型是通过整数来管理的，第一个值是1，第二个值是2，以此类推。

枚举的优点：运行速度快（数字比字符串运算速度快）；限制数据，保证数据完整性；节省空间。

思考：已知枚举占用2个字节，请问最多有多少个枚举值？

答：2个字节=16位，可以保存数字（0-65535），枚举是从1开始，所以枚举最多可以有65535个枚举值。

1.4  集合（set）

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

注意：插入顺序不同，但是显示的顺序是一样的。

插入集合中没有的选项会报错。

每个集合的元素都分配一个固定的数字，分配的方式从左往右按2的0、1、2、...次方

思考：已知集合占用8个字节，最多可以表示几个选项？

64  （8*8）    8个字节=64位，一个位表示1个选项，最多可以表示64个选项。

如果是多选，他们的关系是按位或。

1.5  日期

数据类型	描述
datetime	日期时间，占用8个字节
data	日期 占用3个字节
time	时间 占用3个字节
timestamp	时间戳 占用4个字节
year	年份 占用1个字节

1）datetime

 格式：年-月-日 小时:分钟:秒

加载不到10000年。

2）date

3）timestamp:时间戳

从1970年0时0分0秒到现在的秒数

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

4）year

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

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

注意：time支持以天的方式插入。

1.6  boolean

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

true和false在数据库中对应1和0.

1.7  练习题

下列事物用什么数据类型存储？

手机号码    char

电话号码     varchar

性别           char    enum

人的年龄        tinyint

照片信息       binary（理论）  一般存地址

薪水            decimal

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

1.8 列属性——是否为空（null | not null）

null：可以为空

not null：不可为空

思考题：

1）学生姓名允许为空吗？  非空

2）家庭地址                        非空

3）电子邮件信息                 可为空

4）考试成绩                        可为空

1.9 列属性——默认值（default）

1、如果一个字段没有插入值，可以默认插入一个指定的值。

2、default关键字用来插入默认值

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

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

2、自动增长都是无符号整数。

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

4、如果要给自动增长列插入数据，使用null关键字。

5、自动增长列上的数据被删除，默认情况下此记录的编号不再使用。

1.11 列属性——主键（primary key）

主键：唯一标识表中记录的一个或一组列

主键的特点：不能重复，不能为空

一个表只能有一个主键，主键可以有多个字段组成。

主键的作用：

1、 保证数据完整性

2、 加快查询速度

1.11.1 添加主键

1）创建表的时候添加主键

注意：

如果插入主键相同的数据就会报错

主键不能插入空值

2）创建表的时候添加主键

3）更改表的时候添加主键

1.11.2 创建组合键

1.11.2 查看主键

1.11.3  删除主键

1.11.4 选择主键的原则

1、 最少性：尽量选择一个字段做主键

2、 稳定性：尽量选择更新少的列做主键

3、 尽量选择数字型的列做主键

1.11.5 主键思考题

1、在主键列输入的数值，允许为空吗? 不可以

2、 一个表可以有多个主键吗? 不可以

3、 在一个学校数据库中，如果一个学校内允许重名的学员，但是一个班级内不允许学员重名，可以组合班级和姓名两个字段一起来作为主键吗？ 可以

4、 标识列（自动增长列）允许为字符数据类型吗？ 不可以

5、 表中没有合适的列作为主键怎么办？ 添加自动增加列

6、 如果标识列A的初始值为1，增长量为1，则输入三行数据以后，再删除两行，下次再输入数据行的时候，标识值从多少开始？ 从4开始

1.12 列属性——唯一键

特点：

1、不能重复，可以为空

2、一个表可以有多个唯一键

作用：

1、 保证数据不能重复。保证数据完整性

2、 加快数据访问

1.12.1 添加唯一键

方法一：创建表的时候添加唯一键

方法二：修改表的时候添加唯一键

添加一个唯一键

添加多个唯一键

与上面的不同，添加组合唯一键:

还有一种方法

1.12.2 查看唯一键

添加唯一键，给唯一键起名字：

1.12.3 删除唯一键

通过唯一键的名字来删除唯一键

alter table 表名 drop index 唯一键名称

eg:

显示结果：

问题：主键和唯一键的区别？

1、主键不能重复，不能为空，唯一键不能重复，可以为空

2、主键只有一个，唯一键可以有多个。

1.13 列属性——备注（comment）

为了程序员之间的相互交流

1.14 SQL注释

单行注释：--或#

多行注释：/* */

1.15 数据完整性介绍

1.15.1 保证实体完整性

1、 主键约束

2、 唯一约束

3、 自动增长列

1.15.2 保证域完整性

1、 数据类型约束

2、 非空约束

3、 默认值约束

1.15.3 保证引用完整性

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

1.16 引用完整性

1.16.1 主表和从表

两个表建立关系（两个表只要有公共字段就有关系），一个表称为主表，一个表称为从表。

外键约束可以实现：

1、 主表中没有的从表中不允许插入

2、 从表中有的主表中不允许删除

3、 不能更改主表中的值而导致从表中的记录孤立存在。

4、 先删除从表，再删除主表

1.16.2 外键（foreign key）

1、 外键：从表中的公共字段，公共字段的名字可以不一样，但是数据类型必须一样。

2、 外键约束用来保证引用完整性

1.16.3 添加外键

方法一：创建表的时候添加外键

添加如下字段失败，因为在stumarks中的id不是stuinfo的外键，主表中没有该id的信息：

在主表中添加该id信息后，

在从表中才能添加相应的信息：

直接无法删除主表的信息

必须先删除从表对应id的信息后方能删除

总结：主表中没有的，从表中不允许删除；从表中有的，主表中不能删除；不能更改主表的数据后使得从表的数据独立。

方法二：修改表的时候添加外键

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

1.16.4 查看外键

外键为：stuid

1.16.5 删除外键

通过外键的名字删除外键

mysql 语法：alter table 表名 drop foreign key 外键名

从表是：stumarks   主表是stuinfo

1.17 外键操作

1、 严格操作（前面讲的是严格操作）

2、 置空操作（set null）：如果主表记录删除或更新，从表置空

3、 级联操作（cascade）：如果主表记录删除或更新，从表级联

一般来说：主表删除的时候，从表置空操作，主表更新的时候，从表级联操作。

mysql 语法：foreign key(外键) references 主表(关键字段)[主表删除是的动作][主表更新时候的动作]

1.18 客户端介绍

第一：命令行

第二：MySQL-Front和Navicat

MySQL-Front