- 1美团java工程师，成都外包面试笔试题_美团外包笔试
- 2paddlenlp Windows本地搭建语义检索系统
- 3Android——发送和接收广播
- 4C语言初阶--12数组
- 5gitcode代码仓库的基本使用_code 仓
- 6VHDL语法入门 （二） 数据类型_vhdl 0 downto 7
- 7Hadoop基础学习_hadoop学习
- 8Hadoop学习之虚拟机环境配置，防火墙、selinux关闭、集群时间同步(LinuxcentOS7版本)_连接虚拟机需要关闭防火墙吗
- 9MQTT入门指南（一）ubuntu16.04上安装MQTT服务端_ppa:mosquitto-dev 不存在
- 10华为OD机试 - 靠谱的车（C语言）_华为od靠谱的车
MySQL创建表和约束条件(四)_创建数据表hzb,里面包含以下字段:bh整数字符(此项数据不能为空)并设置为主键约束,
赞
踩
古语有云: 万恶淫为首，百善孝为先。 我们后辈当自勉。
上一章简单介绍了 MySQL的数据类型(三),如果没有看过,请观看上一章
一. 创建表
标识符命名规则:
- 数据库名、表名不得超过30个字符，变量名限制为29个
- 必须只能包含 A–Z, a–z, 0–9, _共63个字符
- 数据库名、表名、字段名等对象名中间不要包含空格
- 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
- 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
- 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了
上一章时，我们学习了 MySQL的数据类型， 就像Java 知道了 int,string 之后，就该学习类了， 现在，我们知道了数据类型，就该去学习表 Table了。
在创建表时，一定要在 database 里面进行创建， 既先使用 use 数据库名来选择数据库， 再进行创建， 否则会提示 “No database selected” 的错误。
创建表语句:
create table 表名(
列名1 数据类型 [列级别约束条件] [默认值],
列名2 数据类型 [列级别约束条件] [默认值],
......
列名n 数据类型 [列级别约束条件] [默认值]，
[表级别约束条件]
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
其中 表名，列名1，列名2，列名n 就像Java 里面的变量一样，不能用关键字。 但与Java 不同的是， MySQL不区分大小写。 这一点，与HTML一样。
一.一 查询该数据库下所有的表
使用 show tables; 命令进行查询该数据库下的表。
要先使用 use 数据 库名先选择数据库。 使用 yuejl数据库。
查询所具有的表 show tables;
这个表 t 是上一章在测试timestamp时创建的。
一.二 创建一个简单的表 temp1
创建一个简单的表， 里面只有一个字段 name, 类型是 varchar 类型， 长度是10.
不要忘记 先 use 数据库名 来选择数据库。
按照上面的创建表的语句进行创建:
create table t2(
name varchar(10)
);
- 1
- 2
- 3
一.三 创建一个复杂的表 temp2
创建一个复杂的表，与老蝴蝶常用的User.java 类一样，具有 id(int类型),name(varchar类型),sex(varchar类型),age(int类型),description（varchar类型） 五个字段。
(创建时，要按照一定的层次进行书写，这样显的帅气。)
create table t3(
id int(11),
name varchar(20),
sex varchar(10),
age int(3),
description varchar(100)
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
一.四 展示所有的表
用 show tables; 命令进行展示
刚才的两个表 t2,t3 放置到这里面了。
一.五 创建表时常见错误
1 . 表名已经存在， 会报 表已经存在的错误
2 . 列名最后一个 写添加了 , 号， 报错。
3 . 列与列之间，没有加 ,号报错
要想 用sql语句创建表时，不报错，只有多练，多写。 前期学习者建议用sql去创建表，不建议使用数据库连接工具去创建表。
二. 约束条件
二.一 约束条件的由来
我们在创建表的命令时， 用这么两个东西， 列级别约束条件和表级别约束条件。 约束条件是干什么用的呢? 用什么作用效果呢?
数据库是存储，管理和操作数据的的仓库， 而表是真正存储数据的， 更准确的说，所有的数据都是放置在表里面的。 我们希望在存储数据之前，就希望能对数据进行一下验证， 就像 Web网站里面的前端验证和后端数据验证， 登录过滤器，权限过滤器一样， 使正确的数据才能够正常的插入，使错误的数据不能够正常插入，提示报错， 使表有个自我检查的功能。 数据库设计者们发现有这么几个常用的小验证，小约束:
1 . 这个列上面的属性值 必须要存在。 这就是非空约束
2 . 这个列上面的属性值必须要唯一，不能重复。 这就是唯一约束
3 . 这个列或者这两个列可以唯一确定这一行，这一行能够通过这个列或者这两列与 其他的行区别开来。 这就是主键约束
4 . 这个列上的值如果没有手动填充值，数据库表默认提供一下，如性别，没有填写，默认是男。 这就是默认值约束
5 . 这个列的值是往上增加的，并且与它上一行的值有关系。 如 num次数， 它上一行的值是1， 它这一行的值是2， 它下一行的值是 3 ，下下一行的值是4。 希望这个1,2,3,4 是表自己递增的， 并不是手动插入的。 这就是自增约束。
6 . 这个列上的值，一定来源于其他表中的值，并不是凭空出现的。发生在两个表之间。 如员工的部门属性的值，一定来源于部门表中的值。 这就是外键约束
7 . 这个列上的值，只能是规定好的值，不能是其他以外的值。 如性别， 只能是男或者女。 手动添加其他值，包括 保密，未知，太监，人妖 之类的，都不能正确插入。 这叫做检查约束(MySQL数据库中不起作用)。
其中，1,2,3,4,5,7 是发生在一个表内的约束， 而6是发生在两个表之间的约束。
上面专业的说法，叫做 数据完整性。
数据完整性分为三个部分:
1 . 实体完整性
2 . 域完整性
3 . 引用完整性
实体完整性这项规则要求每个数据表都必须有主键，而作为主键的所有字段，其属性必须是独一及非空值。
限制字段中的数据必须乎合预设的数据类型。
参照的完整性不允许关系中有不存在的实体引用。
使用约束，可以很方便的保证插入数据的正确性，避免逻辑错误数据的出现，但不能保证业务错误数据的出现。 业务错误数据，需要通过业务流程控制来避免。
二.二 约束条件的分类
- 主键约束 Primary Key (简称 PK)
- 非空约束 Not Null
- 唯一约束 Unique
- 外键约束 Foreign Key (简称 FK)
- 默认约束 Default
- 检查约束 Check(MySQL中不起作用)
- 自增约束 AUTO_INCREMENT
下面，老蝴蝶分别对其进行详细的讲解。
三. 主键约束 Primary Key
主键约束 分为两种， 一种是 单字段主键， 另外一种是多字段联合主键。
三.一 单字段主键
1 . 在创建字段时， 指定 列级别约束条件
列名 数据类型 Primary Key [默认值]
- 1
如:
create table pk4(
id int(11) primary key,
name varchar(10)
);
- 1
- 2
- 3
- 4
2 . 在创建完所有列之后， 指定 表级别约束条件
[constraint 约束名] primary key (列名)
- 1
其中， 约束名通常命名为: PK_表名。 常常省略 constraint 约束名， 直接用 primary key (列名)
create table pk5(
id int(11),
name varchar(10),
primary key (id)
);
- 1
- 2
- 3
- 4
- 5
三.二 多字段联合主键
多字段联合主键只能使用 表级别约束条件
[constraint 约束名] primary key (列名1，列名2)
- 1
也常常省略 constraint 约束名。
如 创建学生与课程 的成绩表。 其中，学生编号和课程编号是联合 主键。
create table pk6(
stuId int(11), -- 学生编号
courseId int(11), -- 课程编号
score double(5,2),
primary key (stuId,courseId)
);
- 1
- 2
- 3
- 4
- 5
- 6
三.三 演示主键约束
用 pk4 表做例子吧。 （关于数据的插入，后面章节会详细讲解）
1 . 先插入一个编号为 1,名称为两个蝴蝶飞 的记录。
insert into pk4(id,name) values(1,'两个蝴蝶飞');
- 1
2 . 再插入一个编号为1，名称为老蝴蝶的记录。
insert into pk4(id,name) values(1,'老蝴蝶');
- 1
编号 id是重复的，不能重复性插入， 会报错。
四. 非空约束 not null
只能指定一个单字段为非空约束，没有联合字段做为非空约束。 可以让多个字段都为非空约束。
列名 数据类型 not null [默认值]
- 1
四.一 创建非空约束
活学活用，将前面的主键约束再练习一遍。
create table n7(
id int(11) primary key,
name varchar(20) not null
);
- 1
- 2
- 3
- 4
注意，只有这一种写法。 没有 not null (name) 作为表级别约束条件的， 只能放在列级别约束。
四.二 演示非空约束
1 . name有值， 正常插入
insert into n7(id,name) values(1,'两个蝴蝶飞');
- 1
2 . name 没有值 或者插入值为 null
insert into n7(id) values(2);
- 1
insert into n7(id,name) values(2,null);
- 1
五. 唯一约束 Unique
可以为空，MySQL也允许其他的列也可以为null.这一点不像SQL Server 数据库。
五.一 创建列约束条件的 唯一约束
命令:
列名 数据类型 unique
- 1
创建语句:
create table u8(
id int(11) primary key,
name varchar(20) unique
);
- 1
- 2
- 3
- 4
五.二 创建表约束条件的 唯一约束
也可以创建表级别的唯一约束
[constraint 唯一约束名] unique (列名)
- 1
创建语句:
create table u9(
id int(11) primary key,
name varchar(20),
unique (name)
);
- 1
- 2
- 3
- 4
- 5
五.二 演示唯一约束
以 u8 表为例。
1 .先插入一条数据， id为1， name为两个蝴蝶飞
insert into u8(id,name) values(1,'两个蝴蝶飞');
- 1
2 . 再创造一条数据， id为3， name仍然为两个蝴蝶飞。 name 重复了。
insert into u8(id,name) values(3,'两个蝴蝶飞');
- 1
3 . 但可以插入一条空的值， 如name 的值插入为 null
insert into u8(id,name) values(4,null);
- 1
但 null的值，可以有多个。 再插入 id为5，名称仍然为null 的。
insert into u8(id,name) values(5,null);
- 1
查询一下: select * from u8;
说明，唯一约束可以插入多个 null的值，并不是唯一的null值。
六. 默认约束 default
命令:
列名 数据类型 default 默认值
- 1
默认约束是，如果不填入这个属性值，就用默认值代替，如果填入了，哪怕填入的值是空，也用填入的值。
六.一 创建默认约束
令 性别默认是 男.
create table d10(
id int(11) primary key,
name varchar(20),
sex varchar(10) default '男'
);
- 1
- 2
- 3
- 4
- 5
六.二 演示默认约束
1 . 插入一条正常的数据， 性别是男。
insert into d10(id,name,sex) values(1,'两个蝴蝶飞','男');
- 1
2 . 插入一条正常的数据，性别是女.
insert into d10(id,name,sex) values(2,'精灵妹','女');
- 1
3 . 插入一条数据，不指定性别。
insert into d10(id,name) values(3,'老蝴蝶');
- 1
4 . 插入一条数据，性别为null 值。
insert into d10(id,name,sex) values(4,'岳泽霖',null);
- 1
5 . 查询刚才的数据 select * from d10;
七 检查约束 check (MySQL数据库被分析，但是会被忽略)
七.一 MySQL 创建列级别约束 (不起作用)
命令:
列名 数据类型 check (sql表达式)
- 1
可以放置值列表， 表示插入的值必须在值列表里面。 也可以放置sql表达式，表示插入的值必须符合sql表达式
1 . 创建和演示 性别只能是男或者女
create table d11(
id int(11) primary key,
name varchar(20),
sex varchar(10) check(sex in ('男','女'))
);
- 1
- 2
- 3
- 4
- 5
插入性别是男 数据。
insert into d11(id,name,sex) values(1,'两个蝴蝶飞','男');
- 1
插入性别是保密的数据
insert into d11(id,name,sex) values(2,'老蝴蝶','保密');
- 1
发现，竟然可以插入， 检查约束并没有起作用。
去谷歌一下， 里面提到了 MySQL 检查约束不起作用的问题。
CHECK
The CHECK clause is parsed but ignored by all storage engines. See Section 1.8.2.3, “Foreign Key Differences”
但MySQL 可以使用其他的方式来代替， 如enum 枚举类型，或者触发器。 但老蝴蝶这儿不讲解。
为了系统的连贯性，另外检查约束 check 确实我也不太懂，所以决定用 Oracle 数据库来讲解。 Oracle 数据库支持 检查约束 Check.
以下的内容，都是用Oracle 进行创建的。 plsql工具。
七.二 Oracle 创建列级别约束 (起作用)
1 . 创建和演示性别只能是男或者女
创建表：
create table t1(
id int primary key,
name varchar2(20),
sex varchar(10) check(sex in ('男','女')) -- 也可以用 check(sex='男' or sex='女')
);
- 1
- 2
- 3
- 4
- 5
插入数据：
insert into t1(id,name,sex) values(1,'两个蝴蝶飞','男'); -- 正常插入
insert into t1(id,name,sex) values(2,'老蝴蝶','保密'); -- 会提示错误
- 1
- 2
检查约束好使。
2 . 创建和演示 年龄必须大于18岁 小于60
创建表:
create table t2(
id int primary key,
name varchar2(20),
age int check(age>=18 and age<=60)
);
- 1
- 2
- 3
- 4
- 5
插入数据：
insert into t2(id,name,age) values(1,'两个蝴蝶飞',20); -- 正常插入
insert into t2(id,name,age) values(2,'岳泽霖',16); -- 会提示错误
insert into t2(id,name,age) values(3,'老蝴蝶',62); --会提示错误
- 1
- 2
- 3
- 4
- 5
检查约束好使。
七.三 Oracle 创建表级别约束 (起作用)
与列级别约束 基本类似 。
创建表:
create table t3(
id int primary key,
name varchar2(20),
sex varchar(10),
check(sex in ('男','女')) -- 也可以用 check(sex='男' or sex='女')
);
- 1
- 2
- 3
- 4
- 5
- 6
插入数据:
insert into t3(id,name,sex) values(1,'两个蝴蝶飞','男'); -- 正常插入
insert into t3(id,name,sex) values(2,'老蝴蝶','保密'); -- 会提示错误
- 1
- 2
年龄的那个类似:
创建表:
create table t4(
id int primary key,
name varchar2(20),
age int,
check(age>=18 and age<=60)
);
- 1
- 2
- 3
- 4
- 5
- 6
插入数据:
insert into t4(id,name,age) values(1,'两个蝴蝶飞',20); -- 正常插入
insert into t4(id,name,age) values(2,'岳泽霖',16); -- 会提示错误
insert into t4(id,name,age) values(3,'老蝴蝶',62); --会提示错误
- 1
- 2
- 3
- 4
- 5
八. 自增约束 AUTO_INCREMENT
自增约束，只能用于整数类型，默认开始值是1，每次增加1，用于数据库生成不重复的主键。(但在高并发环境下，这种方式生成的主键不一定正确)
列名 数据类型 AUTO_INCREMENT
- 1
八.一 创建自增约束
create table a12(
id int(11) primary key auto_increment,
name varchar(20)
);
- 1
- 2
- 3
- 4
八.二 演示自增约束
1 . 插入第一个数据，不插入id 的值
insert into a12(name) values('两个蝴蝶飞');
- 1
2 . 插入第二个数据，不插入id的值
insert into a12(name) values('老蝴蝶');
- 1
3 . 查询数据
MySQL 8.0新特性—自增变量的持久化
在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。
下面通过案例来对比不同的版本中自增变量是否持久化。
在MySQL 5.7版本中，测试步骤如下：
创建的数据表中包含自增主键的id字段，语句如下：
CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);
- 1
- 2
- 3
插入4个空值，执行如下：
INSERT INTO test1
VALUES(0),(0),(0),(0);
- 1
- 2
查询数据表test1中的数据，结果如下：
mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
删除id为4的记录，语句如下：
DELETE FROM test1 WHERE id = 4;
- 1
再次插入一个空值，语句如下：
INSERT INTO test1 VALUES(0);
- 1
查询此时数据表test1中的数据，结果如下：
mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
+----+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
从结果可以看出，虽然删除了id为4的记录，但是再次插入空值时，并没有重用被删除的4，而是分配了5。
删除id为5的记录，结果如下：
DELETE FROM test1 where id=5;
- 1
重启数据库，重新插入一个空值。
INSERT INTO test1 values(0);
- 1
再次查询数据表test1中的数据，结果如下：
mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
从结果可以看出，新插入的0值分配的是4，按照重启前的操作逻辑，此处应该分配6。出现上述结果的主要原因是自增主键没有持久化。
在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。
在MySQL 8.0版本中，上述测试步骤最后一步的结果如下：
mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
+----+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
从结果可以看出，自增变量已经持久化了。
MySQL 8.0将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。
九. 外键约束 Foreign Key
外键约束不像上面的那些约束，是发生在一个表之间的， 外键约束 Foreign Key 是发生在两个表之间的， 这两个表，一个叫主表(父表)， 一个叫从表(子表)。 其中，在子表里面定义外键约束， 引用主表中的主键。 外键可以是一列也可以是多列， 但一般都是一列。 一个表里面可以给多个列都定义外键。 就像 员工的课程成绩表一样， 员工编号是员工表的外键， 课程编号是课程表的外键。 外键是参数完整性， 里面的值可以为null值， 但如果不为null值， 则必须是主表主键的某个值。 注意，子表的外键必须引用的是主表的主键，主表的 unqiue 属性都不行，必须是主键。
九.一 创建外键约束 Foreign Key
命令:
[constraint 外键约束名] foreign key (列名) references 主表名(主键列名)
- 1
是表级别的约束条件。
建议是用户自己手动添加外键约束名， constraint 外键约束名 不省略。
常见的部门和员工表。 即员工表里面的部门编号一定是部门表的主键。
1 . 创建部门表 dept
create table dept(
id int(11) primary key,
name varchar(20)
);
- 1
- 2
- 3
- 4
2 .创建员工表 user
create table user(
id int(11) primary key,
name varchar(20),
description varchar(100),
deptId int(11),
constraint fk_user_deptId foreign key(deptId) references dept(id)
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
创建外键成功。
九.二 外键约束演示
1 . 先往部门表里面插入两条数据
insert into dept(id,name) values(1,'信息部'),(2,'开发部');
- 1
2 .往user 表里面插入 部门编号为 1的数据， 是正确的数据
insert into user(id,name,description,deptId) values(1,'两个蝴蝶飞','一个快乐的程序员',1);
- 1
3 . 往user 表里面插入部门编号为3的数据。 没有这个部门，是错误的数据。
insert into user(id,name,description,deptId) values(2,'老蝴蝶','一个快乐的程序员',3);
- 1
报外键约束的错误。 子表中插入父表中主键没有的属性值会报错。
4 . 删除父表中的数据， 即删除父表中 部门编号为1的那条数据
delete from dept where id=1;
- 1
因为父表中的数据在子表中被引用了，所以是无法直接删除的， 可以先将子表中引用的那条数据删除，或者将deptId 更新成null, 再删除父表中的数据才可以。
update user set deptId=null where id=1;
delete from dept where id=1;
- 1
- 2
- 3
总结：约束关系是针对双方的
-
添加了外键约束后，主表的修改和删除数据受约束
-
添加了外键约束后，从表的添加和修改数据受约束
-
在从表上建立外键，要求主表必须存在
-
删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除
约束等级
-
Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录 -
Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null -
No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作 -
Restrict方式：同no action， 都是立即检查外键约束 -
Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
如果没有指定等级，就相当于Restrict方式。
对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
（1）演示1：on update cascade on delete set null
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update cascade on delete set null
#把修改操作设置为级联修改等级，把删除操作设置为set null等级
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
insert into dept values(1001,'教学部');
insert into dept values(1002, '财务部');
insert into dept values(1003, '咨询部');
insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门
insert into emp values(2,'李四',1001);
insert into emp values(3,'王五',1002);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
mysql> select * from dept;
mysql> select * from emp;
- 1
- 2
- 3
- 4
#修改主表成功，从表也跟着修改，修改了主表被引用的字段1002为1004，从表的引用字段就跟着修改为1004了 mysql> update dept set did = 1004 where did = 1002; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dept; +------+--------+ | did | dname | +------+--------+ | 1001 | 教学部 | | 1003 | 咨询部 | | 1004 | 财务部 | #原来是1002，修改为1004 +------+--------+ 3 rows in set (0.00 sec) mysql> select * from emp; +-----+-------+--------+ | eid | ename | deptid | +-----+-------+--------+ | 1 | 张三 | 1001 | | 2 | 李四 | 1001 | | 3 | 王五 | 1004 | #原来是1002，跟着修改为1004 +-----+-------+--------+ 3 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
- 21
- 22
- 23
- 24
#删除主表的记录成功，从表对应的字段的值被修改为null mysql> delete from dept where did = 1001; Query OK, 1 row affected (0.01 sec) mysql> select * from dept; +------+--------+ | did | dname | #记录1001部门被删除了 +------+--------+ | 1003 | 咨询部 | | 1004 | 财务部 | +------+--------+ 2 rows in set (0.00 sec) mysql> select * from emp; +-----+-------+--------+ | eid | ename | deptid | +-----+-------+--------+ | 1 | 张三 | NULL | #原来引用1001部门的员工，deptid字段变为null | 2 | 李四 | NULL | | 3 | 王五 | 1004 | +-----+-------+--------+ 3 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
- 21
- 22
（2）演示2：on update set null on delete cascade
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update set null on delete cascade
#把修改操作设置为set null等级，把删除操作设置为级联删除等级
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
insert into dept values(1001,'教学部');
insert into dept values(1002, '财务部');
insert into dept values(1003, '咨询部');
insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门
insert into emp values(2,'李四',1001);
insert into emp values(3,'王五',1002);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
mysql> select * from dept; +------+--------+ | did | dname | +------+--------+ | 1001 | 教学部 | | 1002 | 财务部 | | 1003 | 咨询部 | +------+--------+ 3 rows in set (0.00 sec) mysql> select * from emp; +-----+-------+--------+ | eid | ename | deptid | +-----+-------+--------+ | 1 | 张三 | 1001 | | 2 | 李四 | 1001 | | 3 | 王五 | 1002 | +-----+-------+--------+ 3 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
#修改主表，从表对应的字段设置为null mysql> update dept set did = 1004 where did = 1002; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dept; +------+--------+ | did | dname | +------+--------+ | 1001 | 教学部 | | 1003 | 咨询部 | | 1004 | 财务部 | #原来did是1002 +------+--------+ 3 rows in set (0.00 sec) mysql> select * from emp; +-----+-------+--------+ | eid | ename | deptid | +-----+-------+--------+ | 1 | 张三 | 1001 | | 2 | 李四 | 1001 | | 3 | 王五 | NULL | #原来deptid是1002，因为部门表1002被修改了，1002没有对应的了，就设置为null +-----+-------+--------+ 3 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
- 21
- 22
- 23
- 24
#删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了 mysql> delete from dept where did=1001; Query OK, 1 row affected (0.00 sec) mysql> select * from dept; +------+--------+ | did | dname | #部门表中1001部门被删除 +------+--------+ | 1003 | 咨询部 | | 1004 | 财务部 | +------+--------+ 2 rows in set (0.00 sec) mysql> select * from emp; +-----+-------+--------+ | eid | ename | deptid |#原来1001部门的员工也被删除了 +-----+-------+--------+ | 3 | 王五 | NULL | +-----+-------+--------+ 1 row 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
- 21
（3）演示：on update cascade on delete cascade
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update cascade on delete cascade
#把修改操作设置为级联修改等级，把删除操作也设置为级联删除等级
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
insert into dept values(1001,'教学部');
insert into dept values(1002, '财务部');
insert into dept values(1003, '咨询部');
insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门
insert into emp values(2,'李四',1001);
insert into emp values(3,'王五',1002);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
mysql> select * from dept; +------+--------+ | did | dname | +------+--------+ | 1001 | 教学部 | | 1002 | 财务部 | | 1003 | 咨询部 | +------+--------+ 3 rows in set (0.00 sec) mysql> select * from emp; +-----+-------+--------+ | eid | ename | deptid | +-----+-------+--------+ | 1 | 张三 | 1001 | | 2 | 李四 | 1001 | | 3 | 王五 | 1002 | +-----+-------+--------+ 3 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
#修改主表，从表对应的字段自动修改 mysql> update dept set did = 1004 where did = 1002; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dept; +------+--------+ | did | dname | +------+--------+ | 1001 | 教学部 | | 1003 | 咨询部 | | 1004 | 财务部 | #部门1002修改为1004 +------+--------+ 3 rows in set (0.00 sec) mysql> select * from emp; +-----+-------+--------+ | eid | ename | deptid | +-----+-------+--------+ | 1 | 张三 | 1001 | | 2 | 李四 | 1001 | | 3 | 王五 | 1004 | #级联修改 +-----+-------+--------+ 3 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
- 21
- 22
- 23
- 24
#删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了 mysql> delete from dept where did=1001; Query OK, 1 row affected (0.00 sec) mysql> select * from dept; +------+--------+ | did | dname | #1001部门被删除了 +------+--------+ | 1003 | 咨询部 | | 1004 | 财务部 | +------+--------+ 2 rows in set (0.00 sec) mysql> select * from emp; +-----+-------+--------+ | eid | ename | deptid | #1001部门的员工也被删除了 +-----+-------+--------+ | 3 | 王五 | 1004 | +-----+-------+--------+ 1 row 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
删除外键约束
流程如下：
(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
举例：
mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
mysql> alter table emp drop foreign key emp_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 1
- 2
- 3
- 4
- 5
- 6
mysql> show index from emp;
mysql> alter table emp drop index deptid;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from emp;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
开发场景
问题1：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？
答：不是的
问题2：建和不建外键约束有什么区别？
答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。
不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的引用完整性，只能依靠程序员的自觉，或者是在Java程序中进行限定。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。
问题3：那么建和不建外键约束和查询有没有关系？
答：没有
在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会
因为外键约束的系统开销而变得非常慢。所以， MySQL 允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。
阿里开发规范
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
十. 查看表结构
十.一 describe 表名/desc 表名 查看
describe user;
- 1
也可以用简写的形式 : desc 表名
desc user;
- 1
侧重点是各个列的信息。
其中:
- filed 指的是列名
- type 指的是列名的数据类型
- null 表示该列是否可以存储null值
- key 是否已经编制索引。 PRI 表示主键，UNI 表示 unique 索引的一部分， MUL 表示可允许出现多次
- Default 是否有默认值
- extra: 该列的附加信息， 如AUTO_INCREMENT
十.一 show create table 表名 查看
show create table user\G
- 1
会将创建 table 时的sql 语句打印出来， 还包括引擎和编码格式。
侧重点是sql 创建语句。
加\G 不加 \G 都可以， 加\G 可以使显示结果更加直观。
面试1、为什么建表时，加 not null default ‘’ 或 default 0
答：不想让表中出现null值。
面试2、为什么不想要 null 的值
答:（1）不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。
（2）效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0
面试3、带AUTO_INCREMENT约束的字段值是从1开始的吗？
在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。
面试4、并不是每个表都可以任意选择存储引擎？
外键约束（FOREIGN KEY）不能跨引擎使用。
MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。
谢谢!!!
