学习目标
-
能够使用SQL语句进行排序
-
能够使用聚合函数
-
能够使用SQL语句进行分组查询
-
能够完成数据的备份和恢复
-
能够使用SQL语句添加主键、外键、唯一、非空约束
-
能够说出多表之间的关系及其建表原则
重点
SELECT
字段名 FROM
表名
WHERE
条件
GROUP BY
分组列名
HAVING
条件
ORDER BY
排序列名
LIMIT
跳过行数, 返回行数;
1. DQL查询语句-条件查询
目标
能够掌握条件查询语法格式
讲解
前面我们的查询都是将所有数据都查询出来,但是有时候我们只想获取到满足条件
的数据
语法格式:
SELECT 字段名... FROM 表名 WHERE 条件;
流程:取出表中的每条数据,满足条件的记录就返回,不满足条件的记录不返回
准备数据
CREATE TABLE student (
id int,
name varchar(20),
age int,
sex varchar(5),
address varchar(100),
math int,
english int
);
INSERT INTO student(id,name,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
比较运算符
>
大于
<
小于
<=
小于等于
>=
大于等于
=
等于
<>
、!=
不等于
【练习】
- 查询math分数大于80分的学生
SELECT * FROM student WHERE math>80;
- 查询english分数小于或等于80分的学生
SELECT * FROM student WHERE english<=80;
- 查询age等于20岁的学生
SELECT * FROM student WHERE age=20;
- 查询age不等于20岁的学生
SELECT * FROM student WHERE age!=20;
SELECT * FROM sstudentWHERE age<>20;
逻辑运算符
and(&&)
多个条件同时满足
or(||)
多个条件其中一个满足
not(!)
不满足
【练习】
-
查询age大于35且性别为男的学生(两个条件同时满足)
SELECT * FROM student WHERE age>35 AND sex='男';
-
查询age大于35或性别为男的学生(两个条件其中一个满足)
SELECT * FROM student WHERE age>35 OR sex='男';
-
查询id是1或3或5的学生
SELECT * FROM student WHERE id=1 OR id=3 OR id=5;
in关键字
【语法格式】
SELECT * FROM 表名 WHERE 字段 in (值1, 值2, 值3);
in
里面的每个数据都会作为一次条件,只要满足条件的就会显示
【练习】
-
查询id是1或3或5的学生
SELECT * FROM student WHERE id IN (1,3,5);
-
查询id不是1或3或5的学生
SELECT * FROM student WHERE id NOT IN (1,3,5);
范围
between关键字
BETWEEN 值1 AND 值2 -- 表示从值1到值2范围,包头又包尾
比如:age BETWEEN 80 AND 100
相当于: age>=80 && age<=100
【练习】
-
查询english成绩大于等于75,且小于等于90的学生
SELECT * FROM student WHERE english>=75 AND english<=90; SELECT * FROM sstudentWHERE english BETWEEN 75 AND 90;
2. 模糊查询like
目标
能够掌握模糊查询语法格式
讲解
LIKE
表示模糊查询
【语法格式】
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
满足通配符字符串
规则的数据就会显示出来所谓的通配符字符串
就是含有通配符的字符串
MySQL通配符有两个:
%
: 表示0个1个n个字符(任意多个字符)_
: 表示1个字符
【练习】
-
查询姓马的学生
SELECT * FROM student WHERE NAME LIKE '马%';
-
查询姓名中包含
德
字的学生SELECT * FROM student WHERE NAME LIKE '%德%';
-
查询姓马,且姓名有三个字的学生
SELECT * FROM student WHERE NAME LIKE '马__';
小结
模糊查询格式: SELECT 字段 FROM 表名 WHERE 字段 LIKE ‘通配符字符串’;
%
:表示零个一个多个字符(任意多个字符)
_
:表示一个字符
3. DQL查询语句-排序
目标
- 能够掌握对查询的数据进行排序
讲解
通过ORDER BY
子句,可以将查询出的结果进行排序(排序只是显示方式,不会影响数据库中数据的顺序)
【语法格式】
SELECT 字段 FROM 表名 ORDER BY 排序的字段 [ASC|DESC];
ASC: 升序排序(默认)
DESC: 降序排序
1. 单列排序
单列排序就是使用一个字段排序
【练习】
-
查询所有数据,使用年龄降序排序
SELECT * FROM student ORDER BY age DESC;
2.组合排序
组合排序就是先按第一个字段进行排序,如果第一个字段相同,才按第二个字段进行排序,依次类推。
上面的例子中,年龄是有相同的。当年龄相同再使用math进行排序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
【练习】
-
查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩降序排序
SELECT * FROM student ORDER BY age DESC, math DESC;
小结
- 排序的关键字:
ORDER BY
字段名 - 升序:
ASC
- 降序:
DESC
4. DQL查询语句-聚合函数
目标
能够掌握五个聚合函数的使用
讲解
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个结果值。另外聚合函数会忽略空值。
五个聚合函数:
count
: 统计指定列记录数,记录为NULL的不统计sum
: 计算指定列的数值和,如果不是数值类型,那么计算结果为0max
: 计算指定列的最大值min
: 计算指定列的最小值avg
: 计算指定列的平均值
【语法格式】
聚合函数的使用:写在 SQL语句SELECT
后 字段名
的地方
SELECT 字段名... FROM 表名;
SELECT 聚合函数(字段) FROM 表名;
【练习】
-
查询学生总数
SELECT COUNT(english) FROM student;
我们发现对于NULL的记录不会统计
IFNULL(expr1, expr2)
的用法:
假如expr1
不为 NULL
,则 IFNULL()
的返回值为 expr1
; 否则其返回值为expr2
-- 查询英语成绩,若值为null,使用'未知成绩'字符串替换。
SELECT IFNULL(english,'未知成绩') FROM student;
统计数量常用:
SELECT COUNT(*) FROM student;
-
查询年龄大于40的总人数
SELECT COUNT(*) FROM student WHERE age>40;
-
查询数学成绩总分
SELECT SUM(math) FROM student;
-
查询数学成绩最高分
SELECT MAX(math) FROM student;
-
查询数学成绩最低分
SELECT MIN(math) FROM student;
-
查询数学成绩平均分
SELECT AVG(math) FROM student;
小结
sum(列名) | 求和 |
---|---|
count(列名) | 统计数量 |
max(列名) | 最大值 |
min(列名) | 最小值 |
avg(列名) | 平均值 |
5.DQL查询语句-分组
目标
能够对查询后的结果进行分组
讲解
group by关键字
分组查询是指使用 GROUP BY
语句对查询信息进行分组,相同数据作为一组
【语法格式】
SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 字段名;
GROUP BY怎么分组的?
将分组字段结果中相同内容作为一组
SELECT * FROM student GROUP BY sex;
这句话会将sex相同的数据作为一组
GROUP BY
将分组字段的相同值作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
分组后聚合函数的作用?不是操作所有数据,而是操作一组数据。
SELECT SUM(math), sex FROM student GROUP BY sex;
效果如下:
实际上是将每组的math进行求和,返回每组统计的结果
注意事项:
当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的
- 查询的时候没有查询出分组字段
- 查询的时候查询出分组字段
【练习】
-
按性别分组
SELECT sex FROM student GROUP BY sex;
-
查询男女各多少人
-- 1.查询所有数据,按性别分组。 -- 2.统计每组人数 SELECT sex, COUNT(*) FROM student GROUP BY sex;
-
查询年龄大于25岁的人,按性别分组,统计每组的人数
1.先过滤掉年龄小于25岁的人。 2.再分组。 3.最后统计每组的人数 SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex;
having关键字
查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
有很多同学可能会将SQL语句写出这样:
SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2;
注意: 并只显示性别人数>2的数据属于分组后的条件,对于分组后的条件需要使用
having
子句
SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex HAVING COUNT(*) >2;
只有分组后人数大于2的`男`这组数据显示出来
having与where的区别
区别 | having | where |
---|---|---|
使用场景 | 分组后对数据进行过滤 | 分组前对数据进行过滤 |
是否可以使用聚合函数 | 可以 | 不可以 |
小结
- 分组的语法格式?
SELECT
字段FROM
表名WHERE
条件GROUP BY
字段HAVING
条件; - 分组的原理?
先将相同数据作为一组,返回每组的第一条数据,单独分组没有意义,分组后跟聚合函数操作
6. DQL查询语句-limit语句
目标
能够掌握limit语句的使用
讲解
【准备数据】
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
LIMIT关键字
LIMIT
是限制
的意思,所以LIMIT
的作用就是限制查询记录的条数。
【语法格式】
SELECT 字段 FROM 表名 WHERE 条件 LIMIT offset, length;
offset
是指偏移量,可以认为是跳过的记录数量,不写则默认为0。length
是指需要显示的总记录数
【练习】
-
查询学生表中数据,跳过前面2条,显示6条
我们可以认为跳过前面2条,取6条数据 SELECT * FROM student LIMIT 2,6;
LIMIT的使用场景:分页
比如我们登录京东,淘宝,返回的商品信息可能有几万条,不是一次全部显示出来。是一页显示固定的条数。假设我们一每页显示5条记录的方式来分页。
假设我们一每页显示5条记录的方式来分页,SQL语句如下:
-- 每页显示5条
-- 第一页: LIMIT 0,5; 跳过0条,显示5条
-- 第二页: LIMIT 5,5; 跳过5条,显示5条
-- 第三页: LIMIT 10,5; 跳过10条,显示5条
SELECT * FROM student LIMIT 0,5;
SELECT * FROM student LIMIT 5,5;
SELECT * FROM student LIMIT 10,5;
注意:
- 如果第一个参数是0可以简写:
SELECT * FROM student LIMIT 0,5;
SELECT * FROM student LIMIT 5;
- LIMIT 10,5;
不够5条,有多少显示多少
小结
-
LIMIT语句的使用格式?
SELECT 字段 FROM 表名 LIMIT 跳过的条数, 显示条数;
-
SELECT 字段名 FROM 表名 WHERE 条件 GROUP BY 分组列名 HAVING 条件 ORDER BY 排序列名 LIMIT 跳过行数, 返回行数;
7. 数据库备份
目标
能够使用命令行的方式备份和还原表中的数据
讲解
备份的应用场景
在服务器进行数据传输、数据存储和数据交换,就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时,如果没有采取数据备份和数据恢复手段与措施,就会导致数据的丢失,造成的损失是无法弥补与估量的。
命令行方式备份与还原
备份格式:
注意:这个操作不用登录
mysqldump -u用户名 -p密码 数据库 > 文件的路径
还原格式:
注意:还原的时候需要先登录MySQL,并选中对应的数据库
SOURCE 导入文件的路径
【练习】:
-
备份day02数据库中的数据
mysqldump -uroot -proot day02 >D:\day02.sql
【注意】有些情况会出现备份不成功情况,报错如下
mysqldump: unknow option '--no-beep'
解决办法,在mysqldump后面加上:--no-defaults
如果-p后密码明写报错,那么-p留空。
数据库中的所有表和数据都会导出成SQL语句
-
还原day02数据库中的数据
- 删除day02数据库中的所有表
-
备份需要登录MySQL
mysql -uroot -proot
-
选中数据库
use day02; select database();--【查看当前选中数据库】
-
使用SOURCE命令还原数据
source D:\day02.sql
【注意】命令后面不要加分号
小结
使用命令行的方式备份和还原表中的数据
8. 数据库约束的概述
目标
能够说出数据库约束的作用
讲解
数据库约束的作用
对表中的数据进行进一步的限制,保证数据的正确性、有效性和完整性。
约束种类
PRIMARY KEY
: 主键约束UNIQUE
: 唯一约束NOT NULL
: 非空约束DEFAULT
: 默认值FOREIGN KEY
: 外键约束
小结
- 数据库约束的作用?
对表中的数据进行进一步的限制,保证数据的正确性、有效性和完整性。
9. 主键约束
目标
- 能够说出主键约束的作用
- 能够添加和删除主键
讲解
主键的作用
用来唯一标识一条记录。
为什么需要主键约束
有些记录的 name,age,score 字段的值都一样时,那么就没法区分这些数据,造成数据库的记录不唯一,这样就不方便管理数据。
每张表都应该有一个主键,并且每张表主键值要唯一。
哪个字段作为表的主键
通常不用业务字段作为主键,单独给每张表设计一个id的字段,把id作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
创建主键
主键:PRIMARY KEY
主键的特点:
- 主键必须包含唯一的值
- 主键列不能包含NULL值
创建主键方式:
-
在创建表的时候给字段添加主键
字段名 字段类型 PRIMARY KEY id int primary key
-
在已有表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名); alter table student add primary key(id);
【练习】
-
创建表学生表st5, 包含字段(id, name, age)将id做为主键
CREATE TABLE st5 ( id INT PRIMARY KEY, -- id是主键 NAME VARCHAR(20), age INT );
-
添加数据
INSERT INTO st5 (id, NAME) VALUES (1, '唐伯虎'); INSERT INTO st5 (id, NAME) VALUES (2, '周文宾'); INSERT INTO st5 (id, NAME) VALUES (3, '祝枝山'); INSERT INTO st5 (id, NAME) VALUES (4, '文征明');
-
插入重复的主键值
-- 主键是唯一的不能重复:Duplicate entry '1' for key 'PRIMARY' INSERT INTO st5 (id, NAME) VALUES (1, '文征明2');
-
插入NULL的主键值
-- 主键是不能为空的:Column 'id' cannot be null INSERT INTO st5 (id, NAME) VALUES (NULL, '文征明3');
删除主键
【语法格式】
ALTER TABLE 表名 DROP PRIMARY KEY;
【练习】
-
删除st5表的主键
ALTER TABLE st5 DROP PRIMARY KEY;
小结
-
说出主键约束的作用?
唯一区分一条记录
-
主键的特点?
唯一,不能为NULL
-
添加和删除主键?
字段名 字段类型 PRIMARY KEY ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
-
删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
10. 主键自增
目标
能够设置主键为自动增长
讲解
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
【语法格式】
字段名 字段类型 PRIMARY KEY AUTO_INCREMENT
AUTO_INCREMENT
表示自动增长【字段类型必须是整数类型】设置后默认从1开始自增
【练习】
-
创建学生表st6, 包含字段(id, name, age)将id做为主键并自动增长
CREATE TABLE st6 ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT );
-
插入数据
-- 主键默认从1开始自动增长 INSERT INTO st6 (NAME, age) VALUES ('唐僧', 22), ('孙悟空', 26), ('猪八戒', 25), ('沙僧', 20);
【扩展】
-
如果建表时需要指定某个值开始,可以在结尾加上
auto_increment=值
,如下:CREATE TABLE st6 ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT )AUTO_INCREMENT=100; #指定自增从100开始
-
如果希望修改起始值,请使用下列SQL语法
ALTER TABLE 表名 AUTO_INCREMENT=起始值;
【DELETE和TRUNCATE的区别】
这两个关键字可以用来对表进行删除内容。
-
DELETE 删除表中的数据,但不重置AUTO_INCREMENT的值。
-
TRUNCATE 摧毁表,重建表,AUTO_INCREMENT重置为1
小结
-
设置主键为自动增长格式?
字段名 数据类型 PRIMARY KEY AUTO_INCREMENT
11. 唯一约束
目标
- 能够说出唯一约束的作用
- 能够添加唯一约束
讲解
【作用】
在这张表中这个字段的值不能重复
【基本格式】
字段名 字段类型 UNIQUE
【练习】
-
创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
CREATE TABLE st7 ( id INT, NAME VARCHAR(20) UNIQUE );
-
添加一些学生
INSERT INTO st7 VALUES (1, '貂蝉'); INSERT INTO st7 VALUES (2, '西施'); INSERT INTO st7 VALUES (3, '王昭君'); INSERT INTO st7 VALUES (4, '杨玉环'); -- 插入相同的名字出现name重复: Duplicate entry '貂蝉' for key 'name' INSERT INTO st7 VALUES (5, '貂蝉'); -- 出现多个null的时候会怎样?因为null是没有值,所以不存在重复的问题 INSERT INTO st3 VALUES (5, NULL); INSERT INTO st3 VALUES (6, NULL);
小结
- 说出唯一约束的作用?
让这个字段的值不能重复 - 添加唯一约束格式?
字段名 字段类型 UNIQUE
12. 非空约束
目标
- 能够说出非空约束的作用
- 能够添加非空约束
讲解
【作用】这个字段必须设置值,不能是NULL
【语法格式】
字段名 字段类型 NOT NULL
【练习】
-
创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL
CREATE TABLE st8 ( id INT, NAME VARCHAR(20) NOT NULL, gender CHAR(2) );
-
添加一些完整的记录
INSERT INTO st8 VALUES (1, '郭富城', '男'); INSERT INTO st8 VALUES (2, '黎明', '男'); INSERT INTO st8 VALUES (3, '张学友', '男'); INSERT INTO st8 VALUES (4, '刘德华', '男'); -- 姓名不赋值出现姓名不能为null: Column 'name' cannot be null INSERT INTO st8 VALUES (5, NULL, '男');
小结
非空约束的格式:
字段名 数据类型 NOT NULL
13. 默认值
目标
- 能够说出默认值的作用
- 能够给字段添加默认值
讲解
【作用】
往表中添加数据时,如果不指定这个字段的数据,就使用默认值
【语法格式】
字段名 字段类型 DEFAULT 默认值
【练习】
- 创建一个学生表 st9,包含字段(id,name,address), 地址默认值是广州
CREATE TABLE st9 (
id INT,
NAME VARCHAR(20),
address VARCHAR(50) DEFAULT '广州'
);
- 添加一条记录,使用默认地址
INSERT INTO st9 (id, NAME) VALUES (1, '刘德华');
- 添加一条记录,不使用默认地址
INSERT INTO st9 VALUES (2, '张学友', '香港');
面试题:
如果一个字段设置了非空与唯一约束,该字段与主键的区别?
1.一张表只有一个主键
2.一张表可以多个字段添加非空与唯一约束
3.主键可以自动增长,自己添加的非空与唯一约束字段无法自动增长
小结
- 说出默认值的作用?
不添加这个字段,就会使用默认值 - 给字段添加默认值格式?
字段名 字段类型 DEFAULT 默认值
14. 外键约束
目标
- 能够说出外键约束的作用
- 能够创建外键约束
讲解
1. 单表的缺点
创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id主键并自动增长,添加5条数据
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES
('张三', 20, '研发部', '广州'),
('李四', 21, '研发部', '广州'),
('王五', 20, '研发部', '广州'),
('老王', 20, '销售部', '深圳'),
('大王', 22, '销售部', '深圳'),
('小王', 18, '销售部', '深圳');
【缺点】表中出现了很多重复的数据(数据冗余),如果要修改研发部的地址需要修改3个地方。
【解决方案】将一张表分成2张表(员工表和部门表)
-- 创建部门表
CREATE TABLE department (
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 创建员工表
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT
);
-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');
-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);
问题:
当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门,不能出现这种情况。employee的dep_id中的内容只能是department表中存在的id
需要达到目的:需要约束dep_id只能是department表中已经存在id
解决方式:使用外键约束
2. 什么是外键约束
一张表中的某个字段引用另一张表的主键
【主表】: 约束别人的表,把数据给别人使用功能
【副表/从表】: 被别人约束的表,使用别人的数据
3. 创建外键
-
新建表时增加外键:
CONSTRAINT [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
关键字解释:
CONSTRAINT: 添加约束
FOREIGN KEY(外键字段名): 将某个字段作为外键
REFERENCES 主表名(主键字段名) : 外键引用主表的主键 -
已有表增加外键:
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
【练习】
-
删除副表/从表 employee,如果存在
-
创建从表 employee 并添加外键约束
CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT, dep_id INT, -- 添加一个外键 -- 外键取名按公司要求,一般fk结尾 CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id) );
-
正常添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1), ('李四', 21, 1), ('王五', 20, 1), ('老王', 20, 2), ('大王', 22, 2), ('小王', 18, 2);
-
部门错误的数据添加失败
INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);
4. 删除外键
【语法格式】
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
【练习】
-
删除employee表的emp_depid_ref_dep_id_fk外键
ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;
-
在employee表情存在况下添加外键
ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);
小结
-
创建外键约束格式?
CONSTRAINT [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
-
删除外键格式?
ALTER TABLE 表名 DROP FROEIGN KEY 外键名称;
15. 外键的级联
目标
了解外键的级联操作
讲解
要把部门表中的id值2,改成5,能不能直接修改呢?
UPDATE department SET id=5 WHERE id=2;
不能直接修改:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(员工表)中有引用的数据,不能直接修改主表(部门表)主键
要删除部门id等于1的部门, 能不能直接删除呢?
DELETE FROM department WHERE id = 1;
不能直接删除:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(员工表)中有引用的数据,不能直接删除主表(部门表)数据
什么是级联操作
【概念】在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作
ON UPDATE CASCADE -- 级联更新,主键发生更新时,外键也会更新
ON DELETE CASCADE -- 级联删除,主键发生删除时,外键也会删除
以上语句放在外键定义后面。
【例如】级联更新+级联删除
CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
【练习】
-
删除employee表
-
重新创建employee表,添加级联更新和级联删除
CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT, dep_id INT, -- 添加外键约束,并且添加级联更新和级联删除 CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE );
-
再次添加数据到员工表和部门表
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1), ('李四', 21, 1), ('王五', 20, 1), ('老王', 20, 2), ('大王', 22, 2), ('小王', 18, 2);
-
把部门表中id等于1的部门改成id等于10
UPDATE department SET id=10 WHERE id=1;
-
删除部门号是2的部门
DELETE FROM department WHERE id=2;
小结
级联更新:ON UPDATE CASCADE 主键修改后,外键也会跟着修改
级联删除:ON DELETE CASCADE 主键删除后,外键对应的数据也会删除
16. 表关系的概念
目标
能够理解表之间的3种关系
讲解
现实生活中,实体与实体之间肯定是有关系的,比如:老公和老婆,部门和员工,老师和学生等。那么我们在设计表的时候,就应该体现出表与表之间的这种关系!分成三种:
- 一对一
- 一对多
- 多对多
1. 一对多
例如:班级和学生,部门和员工,客户和订单,分类和商品
一对多建表原则:
在从表(多方)创建一个字段作为外键指向主表(一方)的主键
2. 多对多
例如:老师和学生,学生和课程,用户和角色
多对多关系建表原则:
需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。
3. 一对一
在实际的开发中应用不多.因为一对一可以创建成一张表。
两种建表原则:
- 外键唯一:主表的主键和从表的外键(唯一),形成主外键关系,外键唯一
UNIQUE
- 外键是主键:主表的主键和从表的主键,形成主外键关系
17. 一对多关系练习
目标
以下案例是我们JavaWeb课程最后的小项目.我们拿出其中一部分需求,根据需求来设计数据库表之间的关系
一个旅游线路分类中有多个旅游线路
讲解
【实现步骤】
-
创建旅游线路分类表
-- 创建旅游线路分类表:tab_category -- cid旅游线路分类主键,自动增长 -- cname旅游线路分类名称非空,唯一,字符串100 CREATE TABLE tab_category ( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(100) NOT NULL UNIQUE );
-
添加旅游线路分类数据
INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游');
-
创建旅游线路表
-- 创建旅游线路表:tab_route -- rid旅游线路主键,自动增长 -- rname旅游线路名称非空,唯一,字符串100 -- price价格 -- rdate 上架时间,日期类型 -- cid 外键,所属分类 create table tab_route( rid int primary key auto_increment, rname varchar(100) not null unique, price double, rdate date, cid int, -- 外键 foreign key (cid) references tab_category(cid) );
-
添加旅游线路数据
INSERT INTO tab_route VALUES (NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁3天 惠贵团】尝味友鸭面线 住1晚鼓浪屿', 1499, '2018-01-27', 3), (NULL, '【浪漫桂林 阳朔西街高铁3天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-01-27', 3), (NULL, '【爆款¥1699秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州往返 特价团】', 1699, '2018-01-27', 2), (NULL, '【经典·狮航 ¥2399秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2018-01-27', 2), (NULL, '香港迪士尼乐园自由行2天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店暨会议中心标准房1晚住宿】', 799, '2018-01-27', 4);
小结
一对多的关系,创建表的顺序:先建主表,再建从表。
18. 多对多关系练习
目标
一个用户收藏多个线路,一个线路被多个用户收藏
讲解
对于多对多的关系我们需要增加一张中间表来维护他们之间的关系
-
创建用户表
-- 创建用户表tab_user -- 用户id主键,int类型,自动增长 -- 用户名username,字符串,长度30 -- 密码PASSWORD,字符串,长度30 -- 用户姓名NAME,字符串,长度100 -- 性别sex,字符串,长度1 CREATE TABLE tab_user ( uid INT PRIMARY KEY AUTO_INCREMENT, -- 用户id username VARCHAR(100) NOT NULL UNIQUE, -- 用户名 PASSWORD VARCHAR(30) NOT NULL, -- 密码 NAME VARCHAR(100), -- 真实姓名 sex CHAR(1) -- 性别 );
-
添加用户数据
INSERT INTO tab_user VALUES (NULL, 'cz110', 123456, '老王', '男'), (NULL, 'cz119', 654321, '小王', '男');
-
创建收藏表
-- 创建收藏表:tab_favorite -- 收藏主键fid,int类型,主键,自动增长 -- 用户id int,不为null -- 收藏时间date,不为null -- 旅游线路id,int类型,不为null CREATE TABLE tab_favorite ( fid INT PRIMARY KEY AUTO_INCREMENT, -- 收藏主键 uid INT NOT NULL, -- 用户id DATE DATE NOT NULL, -- 收藏时间 rid INT NOT NULL -- 旅游线路id );
-
增加收藏表数据
INSERT INTO tab_favorite VALUES (NULL, 1, '2018-01-01', 1), -- 老王选择厦门 (NULL, 1, '2018-01-01', 2), -- 老王选择桂林 (NULL, 1, '2018-01-01', 3), -- 老王选择泰国 (NULL, 2, '2018-01-01', 2), -- 小王选择桂林 (NULL, 2, '2018-01-01', 3), -- 小王选择泰国 (NULL, 2, '2018-01-01', 5); -- 小王选择迪士尼
小结
多对多:创建一张中间表,中间表与其它两张表是多对一的关系
总结
-
能够使用SQL语句进行排序
SELECT 字段 FROM 表名 WHERE 条件 ORDER BY 字段 [ASC|DESC];
ASC:升序
DESC:降序 -
能够使用聚合函数
count: 统计数量
sum:求和
max:获取最大值
min:获取最小值
avg:获取平均值 -
能够使用SQL语句进行分组查询
SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 字段;
分组是将相同数据作为一组,返回每组第一条数据(没有意义), 通常分组后使用聚合函数 -
能够完成数据的备份和恢复
命令行的方式
备份:不要登录:mysqldump -uroot -proot 数据库 > 文件名
还原:要登录,选中数据库:source 文件名图形界面的方式
-
能够使用SQL语句添加主键、外键、唯一、非空约束
主键: 字段名 字段类型 PRIMARY KEY
外键: CONSTRAINT 外键名 FOREIGN KEY(字段) REFERENCES 主表(主键)
唯一: 字段名 字段类型 UNIQUE
非空约束: 字段名 字段类型 NOT NULL
默认约束: 字段名 字段类型 DEFAULT 默认值 -
能够说出多表之间的关系及其建表原则
1对1(1:1)
1对多(1:n)
多对多(m:n)1对多:多方这张表建立外键引用一方的主键(先建立一方主表,后建立多方从表)
多对多:建立中间表,中间表的字段引用某张表的主键(外键)