03_MySQL_第二课_数据库的数据类型、列属性、数据完整性、外键

2021-10-19

字数统计: 3.2k字 | 阅读时长≈ 12分

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

一、数据类型

01、Number类型:

数据类型描述TINYINT(size)-128 到 127 常规。0 到 255 无符号**UNSIGNED*。在括号中规定最大位数。
SMALLINT(size)-32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。MEDIUMINT(size)-8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。INT(size)-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。BIGINT(size)-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。FLOAT(size,d)带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。4个字节DOUBLE(size,d)带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。8个字节DECIMAL**(size,d)作为字符串存储的 DOUBLE 类型，允许固定的小数点。每9个数用4个字节保存。

*** 这些整数类型拥有额外的选项 **UNSIGNED无符号。
- 通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。
整型支持显示宽度（最小的显示位数）
- 比如int(5)，如果数值的位数小于5，前面加上前导0。比如输入12，显示00012；大于5位就不添加前导0。
- 必须结合 zerofill 才起作用。
小数载计算机内部保存的是近似值。
02、Text 类型：

数据类型描述CHAR(size)保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。VARCHAR(size)

保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。

注释：如果值的长度大于 255，则被转换为 TEXT 类型。

TINYTEXT存放最大长度为 255 个字符的字符串。TEXT存放最大长度为 65,535 个字符的字符串。BLOB用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。MEDIUMTEXT存放最大长度为 16,777,215 个字符的字符串。MEDIUMBLOB用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。LONGTEXT存放最大长度为 4,294,967,295 个字符的字符串。LONGBLOB用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。ENUM(x,y,z,etc.)

允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。

注释：这些值是按照你输入的顺序存储的。

可以按照此格式输入可能的值：ENUM(‘X’,’Y’,’Z’)

SET 集合与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

总的字段空间占用的总长度不能超过65535。
set集合为多选。
- 插入顺序不一样，显示顺序一样。
- 每一个集合的元素都分配一个固定的值，分配的方式从左往右按2的0、1、2…次方分配

03、Data类型：

数据类型描述DATE()

日期。格式：YYYY-MM-DD

注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’

DATETIME()

*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS

注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’

TIMESTAMP()

*时间戳。TIMESTAMP 值使用** Unix 纪元** **(‘1970-01-01 00:00:00’ UTC) **至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS

注释：支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC

TIME()时间。格式：HH:MM:SS 注释：支持的范围是从 **’-838:59:59’ 到 ‘838:59:59’**YEAR()

2 位或 4 位格式的年。

注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。
- 在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

04、Boolean类型：

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

05、常用数据类型方式：

手机号一般使用什么数据类型存储？
- char
电话号码？
- varchar
性别
- char
- enum
年龄信息
- tinyint
照片信息
- binary—–理论上可行，实际上不使用。一般将照片的地址存放在数据库中。
薪水
- decimal 定点数，不能出任何问题。

二、列属性

是否为空
默认值
自动增长
主键
唯一键
注释

01、是否为空（null | not null）

Null: 可以为空
not null: 不可以为空

** 常用场景：**

学员姓名：非空
家庭地址：非空
电子邮件信息：可以为空
考试成绩：可以为空

02、默认值（ default ）

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

create table t1(
  id int unsigned,
  addr varchar(20) not null default '地址不详'
);
set names gbk;

1	insert into t1 values(1,'北京'),(2,default);

03、自动增长（auto_increment）

字段的值从1开始，每次递增1，特点就在字段中的数据不可能重复，适应为记录生成唯一的id。
既然从1开始，每次递增1，所以一般自动增长都是无符号整数。
在MySQL中，auto_increment必须是主键，但是主键不一定是自动增长的。
如果要给自动增长列插入数据，使用null关键字。
自动增长列上的数据被删除，默认情况下此记录的编号不再使用。

create table t11(
  id int auto_increment primary key, //自动增长必须是主键
  name varchar(20)
);

04、主键（primary key）

主键：唯一标识表中记录的一个或一组列
主键的特点：不能重复，不能为空———保证了数据完整性。
一个表只能有一个主键，主键可****以有多个字段组成。

** 主键的作用：**

保证数据的完整性
**加速查询速度 **

create table t2(
  id varchar(5) primary key, //设置了主键，一定不为空了
  name varchar(10)not null
);

1	insert into t2 values('s2531','tom'),('s2532','berry');

主键核心：

添加主键
查看主键
删除主键
选择主键的原则

4.1、添加主键

** 方法一：创建表的时候添加主键01**

create table t2(
  id varchar(5) primary key, //设置了主键，一定不为空了
  name varchar(10)not null
);

** 方法二：创建表的时候添加主键02**

create table t3(
  id int,
  name varchar(10),
  primary key(id)
);

** 创建组合键的主键 **

create table t4(
  classId int,
  stuId int,
  stuName varchar
  primary key(classId,stuId) //多个字段组成的主键
);

** 方法三：更改表的时候添加主键**

1	alter table t5 add primary key(id);

4.2、查看主键

desc t5;

4.3、删除主键

1	alter table t4 drop primary key;

4.4、选择主键的原则

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

4.5、主键思考

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

5、唯一键 ( Unique )

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

唯一键作用：

保证数据不能重复，保证数据的完整性
加快数据访问

5.1、添加唯一键

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

create table t5(
  id int primary key,
  name varchar(20)unique
  addr varchar(100)unique
);

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

create table t7(
  id int,
  name varchar(10),
  addr varchar(100),
  primary key(id)
  unique(name)
  unique(addr)
);

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

1	alter table t6 add unique(name),add unique(addr); //一次添加多个唯一键

** 方法四：修改表的时候添加组合唯一键 **

1	alter table t6 add unique(name,addr); //添加一个组合唯一键

** 方法五：修改**表的时候添加唯一键，给唯一键起名

1	alter table t6 add unique UQ_NA(name,addr)

5.2 查看唯一键

1	show create table t7\G

5.3 删除唯一键

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

1	alter table 表名 drop index 唯一键名称

1	alter table t8 drop index uniqueName;

主键和唯一键的区别？

** 主键不能重复，不能为空；唯一键不能重复，但可以为空。**
主键只有一个，唯一键可以有多个。

6、sql 备注（comment）

为了程序员直接的交流。

create table t9(
  id int primary key comment '主键',
  name varchar(20) comment '姓名'
);

7、SQL注释

单行主食：—或#
多行注释：/* */

三、数据完整性

![QQ截图20210915152604.png](resources/C13314A861E02E3632FE3C56613F3851.png =1326x770)

保证实体完整性
- 通过自动增长列（标识列）和主键
- 组合键
- 约束方法：唯一约束，主键约束、标识列
保证域完整性
- not null 非空约束
- defaul 默认值约束
- 数据类型约束
保证引用完整性
- 外键约束：从表中的公共字段是主键的外键
保证自定义完整性
- 略
主表和从表
*

四、主键—外键、主表—从表

![QQ截图20210915153910.png](resources/094A8C6E479CE04B2024C4D4D2B9E885.png

1、主表和从表

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

2、外键约束的核心：

** 主表中没有的，从表中不允许插入。**
** 从表中有的，主表中不允许删除。**
** 不能更改主表中的值，导致从表中的记录孤立存在。—–从表亦然。**
** 先删除从表，再删除主表。**

3、外键（foreign key）

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

** 3.1、创建外键**

** 方法一：创建表的时候添加外键，进行外键约束。**

create table stuInfo(
  stuNo char(4) primary key,
  name varchar(10) not null
);

create table stuMarks(
  stuId char(4) primary key,
  score tinyint unsigned,
  foreign key (stuId) references stuInfo(stuNo)  //创建表的时候添加外键，进行外键约束
);

** 方法二：修改表的时候添加外键，进行外键约束。**

create table stuInfo(
  stuNo char(4) primary key,
  name varchar(10) not null
);

create table stuMarks(
  stuId char(4) primary key,
  score tinyint unsigned,
);

1	语法：alter table stuMarks add foreign key 从表的公共字段 references 主表的公共字段

1	alter table stuMarks add foreign key (stuId) references stuInfo(stuNo);

** 外键的注意⚠️事项：**

要创建外键必须是innodb引擎，myisam不支持外键约束。
创建外键的时候，给外键取名。
外键放在从表中。

** 3.2、删除外键**

**** ** 通过外键的名字删除外键。**

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

** 删除外键提示：**

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

* 4、外键的操作

严格操作（前面讲的是严格操作）—–正常情况
置空操作（set null ）: 如果主表记录删除或更新，从表置空。置空：主表删除的时候，从表置空为null。
级联操作 （cascade）: 如果主表记录删除或更新，从表级联。级联：主表更新改动的时候，从表也改动。
一般来说：
- 主表删除的时候，从表置空操作。
- 主表更新的时候，从表级联操作。

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

create table stuInfo(
  stuNo char(4) primary key,
  name varchar(10) not null
);

create table stuMarks(
  stuId int auto_increment primary key,
  stuNo char(4),
  score tinyint unsigned,
  foreign key(stuNo)references stuInfo(stuNo) on delete set null on update cascade
);

1	insert into stuInfo values ('s101','tom');

1	insert into stuMarks values(null,'s101',88);

级联：

1 2	update stuInfo set stuNo='s102' where stuNo='s101'; select * from stuMarks;

置空：

1 2	delete from stuInfo where stuNo='s102'; select * from stuMarks;

本文作者： 梁俊可
本文链接： http://ljk3d.com/2021/10/19/mySqlNote/03_MySQL_第二课_数据库的数据类型、列属性、数据完整性、外键/
版权声明： 梁俊可工作室