外键及数据库操作语言DML

系列文章目录

第一章 数据库基本命令及数据库定义语言DDL

---

---

一、外键

1.外键概念

SQL 外键（Foreign Key）用于将两个表连接在一起，让两个表的数据保持同步。外键由表中的一个字段或者多个字段构成，一个表的外键用来指向另一个表的主键（Primary Key）。
包含外键的表称为从表，被指向的表称为主表。从表的数据受到主表的约束，向从表中插入或者更新数据时，外键的值必须存在于主表的主键中。

外键作用：保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

2.一个例子

user 表（用户表）

id	username	password	email	phone
1	aaa	123456	aaa@qq.com	13112344444
2	bbb	xyzabc	bbb@qq.com	15028882233
3	ccc	123abc	ccc@qq.com	13290029028

order 表（订单表）

oid	uid	price	pay_time
1	3	99.5	2022-05-21 12:35:28
2	3	80.0	2022-06-23 02:12:09
3	1	13.5	2022-07-20 09:30:18
4	1	2020.0	2022-07-23 19:45:21

order 是从表，uid 字段是它的外键；user 是主表，id 字段是它的主键。
order 表的外键 uid 指向 user 表的主键 id，uid 的取值受到 id 的制约，uid 的值必须存在于 id 中，否则无法被接受。

2. 创建外键

student 是从表，gradeid 字段是它的外键；grade 是主表，gradeid 字段是它的主键。

• 方法一： 创建子表同时创建外键

-- 年级表
CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID';
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称';
	PRIMARY KEY (`gradeid`);
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 学生表
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`gradeid` INT(10) NOT NULL COMMENT '年级',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`address` VARCHAR(50) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '电子邮箱',
	PRIMARY KEY(`id`),
	
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) 
	
)ENGINE = INNODB DEFAULT CHARSET = utf8

• 方法二：创建子表完毕后,修改子表添加外键
  语句：ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 外键所在表(对应的字段)

-- 年级表
CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 学生表
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`gradeid` INT(10) NOT NULL COMMENT '年级',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`address` VARCHAR(50) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '电子邮箱',
	PRIMARY KEY(`id`)  -- 创建表的时候没有外键关系，
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 修改子表添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)

3. 删除外键

• 删除外键之后，要记得删除索引

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

• 删除具有主外键关系的表时 , 要先删子表 , 后删主表，顺序不能换。

DROP TABLE `student`;   -- 删除子表
DROP TABLE `grade`;     -- 删除主表

4. 对外键约束的几点说明：

• 外键约束用于防止破坏两个表之间的关联性，保证数据的完整性和一致性。
  外键约束能够防止非法数据进入外键字段，因为它的值必须存在于它指向的主键中。

• 一个表可以有多个外键，但是只能有一个主键。
  从表的外键类型，必须与主表的主键类型一致。

• 删除/更新主表记录时，可以选择同时删除/更新从表中的相关记录，也可以选择不删除/更新，可以在定义外键时对此进行设置。
  删除主表记录时，必须先删除从表中的关联记录，否则主表中的记录将无法删除。
  删除主表时，必须先删除从表，否则主表将无法删除。

5. 实践证明

• 以上操作都是物理外键，数据库级别的外键，为了避免数据库过多造成困扰，我们不建议使用！
• 数据库就是单纯的表，只是用来存储数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据（外键的功能），可以通过程序去实现

二、数据库操作语言DML

DML语言 ：数据操作语言，用于操作数据库对象中所包含的数据
包括 :添加数据 INSERT、 更新数据 UPDATE、删除数据 DELETE / TRUNCATE

1. 添加数据（INSERT）

语法

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3',...)

注意

• 字段或值之间用英文逗号隔开
• (字段1,字段2,字段3,…)可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致
• 可同时插入多条数据 , values 后用英文逗号隔开

例子

-- 语法 : INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
INSERT INTO grade(gradename) VALUES ('大一');

-- 主键自增，字段名(gradename)不能省略！！！
-- 查询:INSERT INTO grade VALUE ('大二')错误代码：1136
-- '字段1,字段2...'该部分可省略时：添加的值务必与表结构,数据列,顺序相对应,且数量一致。

-- 一次插入多条数据，values 后用英文逗号隔开
INSERT INTO grade(gradename) VALUES ('大二')('大三'),('大四');

INSERT INTO `student`(`name`,`pwd`,`gradeid`,`sex`,`birthday`,`address`,`email`)
VALUES('a','123','1','男','2003-01-05','江苏南京','aaa@qq.com'),
('b','123','2','女','2002-07-25','江苏苏州','bbb@qq.com'),
('c','123','1','女','2003-04-18','江西南昌','ccc@qq.com'),
('d','123','3','男','2001-07-05','广东珠海','ddd@qq.com'),
('e','123','4','女','2000-06-24','福建厦门','eee@qq.com');

2. 更新数据（UPDATE）

语法

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

注意

• column_name 为要更改的数据列
• value 为修改后的数据 , 可以为具体值、变量（通常是时间CURRENT_TIME）、表达式或者嵌套的SELECT结果
• condition 为筛选条件 , 如不指定则修改该表的所有列数据

where条件子句

运算符	含义	范围	结果
=	等于	1=2	false
!= 或 <>	不等于	1!=2	true
>	大于	1>2	false
<	小于	1<2	true
>=	大于等于	1>=2	false
<=	小于等于	1<=2	true
BETWEEN	在某个范围内	BETWEEN 1 AND 10	在[1, 10]之内？
AND	与	5 > 1 AND 1 !=2	false
OR	或	5 > 1 OR 1>=2	true

例子

-- 修改学生b的邮箱为‘bbb@163.com’
UPDATE `student` SET `email` = 'bbb@163.com' WHERE `name` = 'b';
-- 同时修改多个属性，用英文逗号隔开
UPDATE `student` SET `pwd` = 'abc123', `email` = 'ccc@163.com' WHERE `name` = 'c';

3. 删除数据（DELETE、TRUNCATE）

DELETE命令

语法

DELETE FROM 表名 [WHERE condition];

• condition为筛选条件 , 如不指定则删除该表的所有列数据

例子

DELETE FROM `student` WHERE `name` = 'e';  -- 删除学生e的记录

TRUNCATE命令

语法

TRUNCATE [TABLE] table_name;

• 作用：用于完全清空表数据 , 但表结构 , 索引 , 约束等不变

例子

TRUNCATE grade     -- 清空年级表

TRUNCATE、DELETE 对比

（1）相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
（2）不同 :
使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
使用TRUNCATE TABLE不会对事务有影响 （事务见后续文章）

测试案例

-- 创建一个测试表
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入三个测试数据
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

-- 删除表数据(不带where条件的delete)
DELETE FROM test;
-- 结论:不指定Where就会删除该表的所有列数据；自增当前值依然从原来基础上进行（即从4开始接着计数）,会记录日志

-- 删除表数据(truncate)
TRUNCATE TABLE test;
-- 结论:truncate删除数据——自增当前值会恢复到初始值重新开始（即从1开始重新计数）；不会记录日志

-- 同样使用DELETE清空不同引擎的数据库表数据，重启数据库服务后：
-- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
-- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)