《MySQL多表操作》
回顾
- 管理数据库
-- 创建数据库
create database 库名
-- 删除数据库
drop database 库名
- 表的管理表
-- 查看所有表
show tables
-- 创建表(student(整数id,字符串name,整数age))
create table student(
id int,
name varchar(20),
age int
)
- 删除表
-- 删除学生表
drop table student;
- 数据增删改
-- 插入数据
insert into 表名 values(值)
-- 修改数据
update 表名 set 字段名=值 where 条件
-- 删除数据
delete from 表名 where 条件
- WHERE子句中使用的条件
| 说明 | 关键字 |
|---|---|
| 定义别名 | as |
| 去除重复 | distinct |
| 在一个指定的范围之类，包头又包尾 | between … and |
| 查询集合中任何一个元素 | in |
| 模糊查询 | like % _ |
| 为空，判断是否为空 | IS NULL |
- 聚合函数
| 聚合函数 |
|---|
| sum() |
| count() |
| max() |
| min() |
| avg() |
- 说说下面查询的关键字
| 功能 | 关键字 |
|---|---|
| 排序(升序/降序) | order by … asc/desc |
| 分组查询 | group by … having |
| 分页查询 | limit 起始行，返回行数 |
- 说说mysql中有哪些约束
| 约束名 | 约束关键字 |
|---|---|
| 主键 | primary key |
| 唯一 | unique |
| 非空 | not null |
| 默认 | default |
| 外键 | foreign key … references |
- 能够使用SQL语句添加主键、默认，外键、唯一、非空约束
- 能够使用DCL处理MySQL中的用户
- 创建数据库用户，分配权限的。root/root
- 能够理解三大范式
- 建立表的规范。
- 多表操作
- 能够说出多表之间的关系及其建表原则
- 能够使用内连接进行多表查询
- 能够使用左外连接和右外连接进行多表查询
- 能够使用子查询进行多表查询
- 能够理解多表查询的规律
01 约束：唯一，非空，默认值
目标
- 唯一约束
- 非空约束
- 默认值
唯一约束
什么是唯一约束
指定某一列不能出现相同的值
语法
在建表的时候创建
create table 表名 (
字段名 字段类型 unique
)
操作
-- 创建学生表st3, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
create table st3 (
id int,
name varchar(20) unique
);
desc st3;
-- 添加一个同名的学生
select * from st3;
insert into st3 values(1,'张三');
-- 添加重复的： Duplicate entry '张三' for key 'name'
insert into st3 values(2,'张三');
非空约束
什么是非空约束
设置某列数据不能为空，必须要输入
语法
create table 表名 (
字段名 字段类型 not null
)
操作
-- 创建表学生表st4, 包含字段(id,name,gender)其中name不能为NULL
create table st4(
id int,
name varchar(20) not null,
age int
);
desc st4;
-- 添加一条记录其中姓名不赋值
-- Column 'name' cannot be null
insert into st4 values(1,null,20);
select * from st4;
-- Field 'name' doesn't have a default value
insert into st4 (id,age) values(1,30);
默认值
什么是默认值
如果一个字段没有设置它的值，将使用默认值
语法
create table 表名 (
字段名 字段类型 default 默认值
)
操作
-- 创建一个学生表 st5，包含字段(id,name,address)， 地址默认值是广州
create table st5(
id int,
name varchar(20),
address varchar(30) default '广州'
);
desc st5;
-- 添加一条记录,使用默认地址
select * from st5;
insert into st5 (id,name) values (1,'张三');
insert into st5 values (1,'张三',default);
-- 添加一条记录,不使用默认地址
insert into st5 values(2,'李四','深圳');
小结
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT , -- 主键自增
NAME VARCHAR(20) NOT NULL , -- 非空约束
addr VARCHAR(20) DEFAULT '广州', -- 默认约束
email VARCHAR(20) UNIQUE -- 唯一约束
)
-
主键自增约束
id INT PRIMARY KEY AUTO_INCREMENT , – 主键自增 包含了非空和唯一约束！！
-
唯一约束的关键字是？
unique：不包含非空！！可以为NULL或者空字符。
-
非空约束的关键字是？
not null 只是不能是NULL 其他都可以
-
默认值的关键字是？
default
02 为什么要外键约束
目标
为什么要外键约束
外键的概念：在一张表建立一个字段关联另一张表的主键，这个字段称为外键。
单表的缺点
创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id主键并自动增长,添加5条数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3V5sCDil-1594611788136)(assets/1562061151559.png)]
-- 创建一个员工表
create table emp (
id int primary key auto_increment, -- 主键自增长
`name` varchar(20),
age int,
dep_name varchar(10), -- 部门名
dep_location varchar(20) -- 部门所在城市
);
select * from emp;
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
结论：单张表存储信息可能出现大量数据的冗余，例如此表中部门信息大量冗余。
解决方案
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yRVQriUX-1594611788140)(assets/1562061151560.png)]
/*
这种表设计上有问题：
1. 会出现大量重复的数据
2. 会出现修改或删除的问题，如：更新2号员工：销售部 广州。如果一个部门只有一个员工
*/
delete from emp where id = 7;
-- 解决方法：将部门设计成一张表
create table department (
id int primary key auto_increment,
dep_name varchar(10), -- 部门名
dep_location varchar(20) -- 部门所在城市
);
-- 员工表
create table employee (
id int primary key auto_increment, -- 主键自增长
`name` varchar(20),
age int,
dept_id int -- 外键，引用部门表中主键
);
-- 添加部门表的记录
insert into department values(null,'研发部', '广州'),(null, '销售部', '深圳');
select * from department;
-- 添加员工信息
INSERT INTO employee (NAME, age, dept_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dept_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dept_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dept_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dept_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dept_id) VALUES ('小王', 18, 2);
select * from employee;
-- 违反外键约束
INSERT INTO employee (NAME, age, dept_id) VALUES ('小乔', 18, 6);
出现新的问题
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r9y5qeLt-1594611788144)(assets/1562061433448.png)]
解决方案: 必须对外键添加约束
什么是外键约束
概念
一个部门包含多个员工，反之一个员工只属于一个部门。部门是一方，员工是多方。
-
主表： 1方是主表，包含主键。
-
从表： 多方是从表，包含外键
-
什么是外键：一定是出现在从表中。被主表中主键约束的。
-
创建表的时候，先创建主键，再创建从表。删除表的时候先删除从表，再删除主表。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GTi2wuom-1594611788149)(assets/1562061433449.png)]
小结
- 什么是主表？ 被参考主键的表称为主表 （部门表）
- 什么是从表？使用了外键的表是从表（员工表）
- 外键约束在哪个表中？ 从表中。
03 外键约束：创建和删除
目标
- 如何创建外键约束
- 如何删除外键约束
创建外键约束
语法
新建表时增加外键：
create table 表名 (
外键字段名 字段类型,
foreign key(外键字段名) references 主表(主键)
)
已有表增加外键：
alter table 表名 add constraint 约束名 foreign key(外键字段名) references 主表(主键)
-- 约束名用于后期删除外键约束
操作
-- 删除从表 employee
drop table employee;
-- 创建从表 employee 并添加外键约束emp_depid_fk
create table employee (
id int primary key auto_increment, -- 主键自增长
`name` varchar(20),
age int,
dept_id int, -- 外键，引用部门表中主键
foreign key (dept_id) references department(id)
);
-- 部门错误的数据添加失败
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dept_id) VALUES ('小乔', 18, 6);
删除外键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fSuQnH6H-1594611788152)(assets/1563329669857.png)]
-- 删除employee表的employee_ibfk_1外键
alter table employee drop foreign key employee_ibfk_1;
-- 在employee表情存在的情况下添加外键
alter table employee add foreign key (dept_id) references department(id);
小结
外键的作用：外键约束降低数据冗余的同时，还对数据的一致性和准确性有强力的约束。
创建外键约束的语法是什么？
constraint 外键别名 foreign key(外键) references 主表(主键)
主表：部门表。
从表：员工表。
部门和员工：一对多的关系。会把1的一方的主键拿来作为多的一方的外键。
建立外键约束有几种方式？2种
-
建表后：
-- 约束名用于后期删除外键约束 alter table 表名 add constraint 约束名 foreign key(外键字段名) references 主表(主键) alter table 表名 add foreign key(外键字段名) references 主表(主键) -
建表时：
-- 创建从表 employee 并添加外键约束emp_depid_fk create table employee ( id int primary key auto_increment, -- 主键自增长 `name` varchar(20), age int, dept_id int, -- 外键，引用部门表中主键 -- constraint 约束名 foreign key (dept_id) references department(id) -- 如果申明外键没有给外键约束名称，默认有默认名称：employee_ibfk_1 foreign key (dept_id) references department(id) );删除employee表的employee_ibfk_1外键
alter table employee drop foreign key employee_ibfk_1;
04 外键约束：级联更新和删除
目标
- 学习级联删除。
- 学习级联更新。
为什么要级联操作
select * from department;
select * from employee;
-- Cannot delete or update a parent row: a foreign key constraint fails (不能直接修改主表中主键)
update department set id=5 where id=2;
-- 要删除部门id等于1的部门, 能不能直接删除呢？
-- Cannot delete or update a parent row: a foreign key constraint fails(如果有从表引用了主表中主键，主表中这条记录不能删除)
delete from department where id=1;
什么是级联操作
修改主表中主键，或者删除主表中记录的时候，从表中外键也被同时修改，或者删除。
语法
写在外键约束的后面，在创建外键约束的时候创建级联操作
| 级联操作 | 语法 |
|---|---|
| 级联更新 | on update cascade |
| 级联删除 | on delete cascade |
操作
-- 删除外键约束
alter table employee drop foreign key employee_ibfk_1;
-- 添加外键约束，级联更新和级联删除
alter table employee add constraint fk_emp_dept
foreign key (dept_id) references department(id) on update cascade on delete cascade;
-- delete from employee where id > 6;
select * from employee;
-- 把部门表中id等于1的部门改成id等于9
select * from department;
update department set id=9 where id=1;
-- 删除部门号是2的部门
delete from department where id=2;
小结
| 级联操作 | 语法 |
|---|---|
| 级联更新 | on update cascade |
| 级联删除 | on delete cascade |
05 DCL：创建用户，授权，撤销权限(了解)
目标
- 创建mysql的用户
- 给用户授权
- 撤销权限
DCL (Data Control Language)
我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。
创建用户
语法
create user '用户名'@'主机名' identified by '密码'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IbW7xjsh-1594611788155)(assets/1562158759012.png)]
操作
-
创建user1用户，只能在localhost这个服务器登录mysql服务器，密码为123
-
创建user2用户可以在任何电脑上登录mysql服务器，密码为123
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wj0UG1Al-1594611788157)(assets/1563332035884.png)]
注：创建的用户名都在mysql数据库中的user表中可以查看到，密码经过了加密。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cAksOsCu-1594611788160)(assets/1563332059612.png)]
给用户授权
用户创建之后，没什么权限！需要给用户授权
语法
grant 权限 on 数据库名.表名 to '用户名'@'主机名'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FKwnY5uX-1594611788162)(assets/1562158784637.png)]
操作
-
给user1用户分配对test这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
-
给user2用户分配所有权限，对所有数据库的所有表 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DhhwcKPb-1594611788164)(assets/1563332503308.png)]
撤销授权
语法
revoke 权限 on 数据库名.表名 from '用户名'@'主机名'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FuJ45qEJ-1594611788166)(assets/1562158894949.png)]
操作
撤销user1用户对test数据库所有表的操作的权限
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yqaA8okE-1594611788169)(assets/1563332595494.png)]
注：用户名和主机名要与创建时相同，各自要加上单引号
小结
- 创建用户：create user ‘用户名’@‘主机名’ identified by ‘密码’
- 给用户授权：grant 权限 on 库名.表名 to ‘用户名’@‘主机名’
- 撤销权限：revoke 权限 on 库名.表名 from ‘用户名’@‘主机名’
06 DCL：删除用户，修改密码
目标
- 删除用户
- 修改管理员密码
- 修改自己密码
删除用户
语法
drop user '用户名'@'主机名'
具体操作：
- 删除user2
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eeFN8Xv4-1594611788172)(assets/1563333895288.png)]
修改管理员密码
语法
在DOS命令行下执行：
mysqladmin.exe 在bin文件夹下
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K8ZH1Xud-1594611788174)(assets/1562159677067.png)]
操作
- 将root管理员的新密码改成123456
- 要求输入旧密码
- 使用新密码登录
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KrsvEGm6-1594611788176)(assets/1563334038685.png)]
修改自己的密码
语法
登录mysql使用
set password=password('密码')
注：用户登录后操作
操作
- 以user1登录
- 修改user1的密码为abc
- 重新以新的密码登录
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CDNb7qjB-1594611788178)(assets/1563334141621.png)]
小结
- 删除用户：drop user ‘用户名’@‘主机名’
- 修改管理员密码： mysqladmin -u用户名 -p password 新密码
- 修改自己密码：set password=password(‘密码’)
07 数据库备份和还原（个人备份）
目标
- 使用命令对数据库进行备份和还原
- 使用图形界面对数据库进行备份和还原
备份的应用场景
在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。
备份与还原的语句
备份格式
注：这个操作不用登录，也是bin目录下可执行文件
mysqldump -u用户名 -p密码 数据库名 > 文件名
还原格式
注：这个操作需要登录，这是mysql中命令
use 数据库;
source 还原文件名;
-- 数据还原到当前的数据库中
备份操作
备份day15数据库中的数据到d:\day15.sql文件中
mysqldump -uroot -proot day15 > d:/day15.sql
还原操作
还原day15数据库中的数据，注意：还原的时候需要先登录MySQL,并选中对应的数据库。
- 删除day15数据库中的所有表
- 登录MySQL
- 选中数据库
- 使用SOURCE命令还原数据
- 查看还原结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3hMKuO05-1594611788181)(assets/1563334699726.png)]
图形化界面备份与还原
备份数据库中的数据
- 选中数据库，右键 ”备份/导出”
- 指定导出路径，保存成.sql文件即可。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7x039h4E-1594611788183)(assets/1562030233396.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QwX5jy4q-1594611788185)(assets/1562030253423.png)]
还原数据库中的数据
- 删除数据库
- 数据库列表区域右键“执行SQL脚本”， 指定要执行的SQL文件，执行即可
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zyzG6nOc-1594611788187)(/assets/1562030289605.png)]
小结
备份数据库的目的是什么？
防止数据丢失
备份有两种方式：
- 第一种图形界面：备份方便，备份的数据完整。包括结构，数据，还有数据库。
- 第二种方式是命令：操作麻烦一点，备份的数据完整，包括了结果，数据，不能备份数据库。
- 数据库要先自己创建再恢复。
- cmd开始备份：mysqldump -uroot -proot day15 > d:/day15.sql
- 登录:mysql -uroot -proot
- 创建新的数据库。
- use 数据库;
source 还原文件名;
– 数据还原到当前的数据库中
08 数据库范式：第一范式
目标
- 什么是范式
- 第一范式
数据规范化
什么是范式
一种规则，指导程序员创建表的规则。数据库的范式有6种范式，程序员在设计表的时候，只需要符合三大范式就可以了。
三大范式
分成几个等级，一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的。
满足最低要求的范式是第一范式（1NF），在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。
第一范式
概念
原子性：表中每一列都不可以再分割成更小的列。如果一列中包含的数据可以再拆分。
第一范式：满足列不可再分。
案例
班级表
| 学号 | 姓名 | 班级信息 |
|---|---|---|
| 1000 | 张三 | 一年级3班 |
| 1001 | 李四 | 一年级2班 |
| 2001 | 王五 | 二年级1班 |
解决方案
| 学号 | 姓名 | 年级 | 班号 |
|---|---|---|---|
| 1000 | 张三 | 一年级 | 3班 |
小结
什么是第一范式？
原子性：每一列不可再拆分，每一列都是唯一字段值。
09 数据库范式：第二范式
目标
什么是第二范式
概念
在满足第一范式的基础上，有更多的要求，才是第二范式。表中每一列都必须完全依赖于主键，
示例
借书证表
| 主键 | 学生证号 | 学生证名称 | 学生证办理时间 | 借书证号 | 借书证名称 | 借书证办理时间 |
|---|---|---|---|---|---|---|
| 1 | A2349U | 张三学生证 | 2018-01-23 | 29374 | 张三借书证 | 2019-03-10 |
小结
什么是第二范式？
表中每列都是完全依赖于主键的。
10 数据库范式：第三范式
目标
什么是第三范式
概念
在满足第二范式的基础上，有更多的要求。 表中每列都直接依赖于主键，而不是通过其它列间接依赖于主键。
依赖关系
所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则C传递依赖于A。
满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列x → 非主键列y
示例
学生信息表
| 学号 | 姓名 | 年龄 | 所在学院 | 学院地点 |
|---|---|---|---|---|
| 1000 | 张三 | 20 | 传智专修学院 | 江苏 |
| 2000 | 李四 | 19 | 广州美术学院 | 广州 |
存在传递的决定关系：学号 -> 学院 -> 地点
解决方案
从表：有外键 (多方)
| 主键 | 姓名 | 年龄 | 所在学院ID |
|---|---|---|---|
| 从表 | 外键 |
| 学院ID | 学院名字 | 学院地点 |
|---|---|---|
| 主表(1方) |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4SSNnjQ9-1594611788190)(assets/1563336139925.png)]
注：表使用的范式越高级，被拆分成表就越多。表过多，数据操作的性能会下降！
反三范式的概念：在性能和数据库规范下做折中！有时候为了追求性能可能会反三范式设计！！
小结
第一范式：列不可拆分。原子性。
第二范式：满足第一范式，同时每列都依赖于主键：建立主键。
第三范式：不存在传递依赖，拆表（一张表只描述一件事）
- 主键 员工 员工名称 外键主键 部门名称 部门地址。
- 主键 部门名称 部门地址。
降低数据的冗余，维护好数据的准确性。
11 表与表之间的关系概述
表关系的概念
表存储数据，数据就是实现生活中各种对象的抽象。
现实生活中，实体与实体之间肯定是有关系的。
比如：部门和员工，老师和学生等。
那么我们在设计表的时候，就应该体现出表与表之间的这种关系！
总结：表与表的关系可以实现数据的联系，准确性，某种程度上降低冗余。
| 记录之间的对应关系 | 表与表之间的三种关系 |
|---|---|
| A表和B表：A表中一条记录对应B表中一条记录。如：身份证 - 护照 | 一对一 |
| A表1方，B表多方： A表中一条记录对应了B表中多条记录。如：部门 - 员工 B表中一条记录对应了A表中一条记录。 | 一对多 |
| A表中一条记录对应了B表中多条记录 B表中一条记录对应了A表中多条记录 如： 学生与老师是多对多的关系。 | 多对多 |
一对一
一对一(1:1) 例如：
A表中一条记录对应B表中一条记录： 学生表 简历表。
直接建立外键即可。随便找一张表的主建做为另一张表的外键参考即可。
或者直接合并两张表。
一对多
一对多（1:n） 例如：部门与员工关系。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6Aaw5cK5-1594611788192)(assets/1562159677068.png)]
1对N建表的原则： 拿1的一方的主键作为多的 一方的外键
多对多
多对多（m:n） 例如：学生和课程 （50 10）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aVUGTFSZ-1594611788194)(assets/1562159677069.png)]
多对多关系建表原则：建立中间表，把两方的主键拿来作为中间表的联合主键。
小结
表与表之间有哪三种关系？
- 1对1： 随便找张表做一个外键参考对方的主键即可。也可以直接合并表！！
- 1对多：拿1的一方的主键作为多的一方的外键
- 多对多：建立中间表，把两方的主键拿来作为中间表的联合主键
12 案例：一对一
目标
创建1对1的表关系
建表原则
一对一（1:1） 在实际的开发中应用不多，因为一对一可以创建成一张表。
两种建表原则：
| 一对一的建表原则 | 说明 |
|---|---|
| 外键唯一 | 将从表的外键添加唯一约束，变成了一对一的关系。其实是一个特殊的一对多的关系。 |
| 外键是主键 |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ESID33zw-1594611788196)(assets/1562221725724.png)]
代码
-- 主表
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
NAME VARCHAR(20)
);
-- 从表
CREATE TABLE info(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
address VARCHAR(20),
use_name VARCHAR(10),
weight DOUBLE,
-- 直接把主键约束成外键。 主键也可以是外键。
FOREIGN KEY(id) REFERENCES stu(id)
);
架构图：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZVQ8KS7a-1594611788198)(assets/1563346173059.png)]
小结
一对一有哪两种创建方式？
- 也可以拆分成两张表，从表的主键又是外键。
- 可以把两张表合并成一张表。
13 案例：一对多关系案例
目标
实现一个"线路分类"中有多个"旅游线路"的一对多的关系 。
界面效果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9RTeHMjF-1594611788202)(assets/1562222089416.png)]
表与表的关系
route 线路 , category 分类。
一个分类对应多条线路：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tT15IKwV-1594611788205)(assets/1562222126818.png)]
代码
/*
一对多，一个分类对应多条线路
因为sql中不区分大小写：如果有多个单词，中间使用_分隔
*/
/*
创建旅游线路分类表tab_category
cid旅游线路分类主键，自动增长
cname旅游线路分类名称非空，唯一，字符串100
*/
create table tab_category(
cid int primary key auto_increment,
cname varchar(100) not null unique
);
/*
创建旅游线路表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)
);
小结
1对多的关系在表中通过什么来维护？
把1的一方的主键作为多的一方的外键。
14 案例：多对多关系案例
目标
一个用户收藏多个线路，一个线路被多个用户收藏，建表体现线路与用户之间的关系
效果
用户与线路之间是多对多的关系
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KotekBZm-1594611788208)(assets/1562222326359.png)]
解决方案
对于多对多的关系我们需要增加一张中间表来维护他们之间个关系
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lDarEKXw-1594611788211)(assets/1562222362257.png)]
代码
-- 多对多的关系
/*
创建用户表tab_user
uid用户主键，自增长
username用户名长度100，唯一，非空
password密码长度30，非空
name真实姓名长度100
birthday生日
sex性别，定长字符串1
telephone手机号，字符串11
email邮箱，字符串长度100
*/
create table tab_user(
uid int primary key auto_increment,
username varchar(100) unique not null,
password varchar(30) not null,
name varchar(100),
birthday date,
sex char(1),
telephone varchar(11),
email varchar(100)
);
/*
创建收藏表tab_favorite
rid 旅游线路id，外键
date 收藏时间
uid用户id，外键
rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
rid int, -- 线路id的外键
`date` datetime,
uid int , -- 用户id的外键
-- 创建复合主键
primary key(rid, uid),
foreign key(rid) references tab_route(rid), -- 关联了线路的主键
foreign key(uid) references tab_user(uid) -- 关联了用户的主键
);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gqt9FCCk-1594611788214)(assets/1563348042355.png)]
小结：表与表之间关系
| 表与表的关系 | 关系的维护 |
|---|---|
| 一对多 | 通过从表外键来维护 |
| 多对多 | 通过中间表，把两表的主键拿来作为中间表的联合主键。 |
| 一对一 | 1. 从表的外键唯一 2.从表的主键又是外键 3.也可以直接合并成一张表即可 |
三范式并不是绝对的，有时候需要反三范式：涉及到查询性能的时候，为了提高查询性能。
15 表连接：笛卡尔积和内连接
目标
- 笛卡尔积现象
- 内连接的两种方式
为什么需要有多表操作
准备数据：
# 创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept (name) values ('开发部'),('市场部'),('财务部');
# 创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
);
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);
查询某员工在哪个部门？
只查询一张表不能查询出员工名字和部门名字，需要使用多表操作。
完成多表操作的两种方式：
- 表连接
- 子查询
表连接的分类
隐式内连接：不出现join关键字
显示内连接：出现join…on关键字
左外连接，通常又叫左连接。右外连接通常又叫右连接。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bjs9FxqG-1594611788217)(assets/1562224531368.png)]
笛卡尔积现象
什么是笛卡尔积
-- 需求：查询所有的员工和所有的部门
结果分析
部门是左表，员工是右表。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aBLFFrba-1594611788221)(assets/1562224609615.png)]
内连接
如何清除笛卡尔积现象的影响
我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。
-- 查询孙悟空在哪个部门名字
select * from emp;
select * from dept;
-- 需求：查询所有的员工和所有的部门
-- 查询2张表结果是，是2张表记录的乘积，称为笛卡尔积
select * from emp,dept;
-- 如何消除笛卡尔积：条件是从表.外键=主表.主键
select * from emp,dept where emp.dept_id = dept.id;
-- 这就是隐式内连接，使用where，没有用到join...on
-- 给表起别名
select * from emp e ,dept d where e.dept_id = d.id;
-- 查询孙悟空在哪个部门名字
select * from emp e ,dept d where e.dept_id = d.id and e.id=1;
-- 只显示2列
select e.name 员工名,d.name 部门名 from emp e ,dept d where e.dept_id = d.id and e.id=1;
隐式内连接语法
select 列名 from 左表,右表 where 从表.外键=主表.主键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-i0khohRP-1594611788223)(assets/1563349640578.png)]
显式内连接语法
-- 显示内连接, on后面就是表连接的条件
select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键
案例
查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
步骤
- 确定查询哪些表
- 确定表连接条件，员工表.dept_id = 部门表.id 的数据才是有效的
- 确定查询条件，我们查询的是唐僧的信息，员工表.name=‘唐僧’
- 确定查询字段，查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
- 我们发现写表名有点长，可以给表取别名，显示的字段名也使用别名
代码
-- 查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
-- 1. 确定查询哪些表
select * from emp e inner join dept d;
-- 2. 确定表连接的条件
select * from emp e inner join dept d on e.dept_id = d.id;
-- 3. 如果有其它的查询条件，添加条件
select * from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';
-- 4. 确定查询哪些列
select e.id 编号, e.name 姓名, e.gender 性别, e.salary 工资, d.name 部门名
from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';
小结
-
笛卡尔积现象：左表中所有的记录乘以右边所有记录：交叉组合，形成大量错误的组合数据。
select * from employee , dept;
-
内连接的两种方式：可以解决笛卡尔积现象
-
显示：inner join … on
SELECT 列名 FROM 左表 INNER JOIN 右表 ON 从表.外键=主表.主键
-
隐式：条件跟在where的后面
SELECT 列名 FROM 左表,右表 WHERE 从表.外键=主表.主键
-
16 外连接：左连接和右连接
目标
- 左连接的实现
- 右连接的实现
左连接
语法
select 列名 from 左表 left join 右表 on 从表.外键=主表.主键
案例
-- 需求：在部门表中增加一个销售部，需要查询所有的部门和员工，将部门表设置成左表，员工表设置成右表
select * from dept;
insert into dept values(null,'销售部');
-- 使用内连接查询
select * from dept d inner join emp e on d.id = e.dept_id;
-- 使用左外连接查询
select * from dept d left join emp e on d.id = e.dept_id;
-- 左连接：左表中所有的记录都出现在结果，如果右表没有匹配的记录，使用NULL填充
概念
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SPqImMbm-1594611788227)(assets/1562225317114.png)]
右连接
语法
select 列名 from 左表 right join 右表 on 从表.外键=主表.主键
案例
-- 右连接
select * from emp;
-- 需求：在员工表中增加一个员工:'沙僧','男',6666,'2013-02-24',null
insert into emp values(null, '沙僧','男',6666,'2013-02-24',null);
-- 希望员工的信息全部显示出来
-- 使用内连接查询
select * from dept d inner join emp e on d.id = e.dept_id;
-- 使用右外连接查询
select * from dept d right join emp e on d.id = e.dept_id;
概念
右连接：保证右表中所有的数据都出现，如果左表没有对应的记录，使用NULL填充
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zw0kZ0hy-1594611788230)(assets/1562225378984.png)]
小结
-
什么是左连接，关键字是什么？
在内连接的基础之上保证左表的数据都出现，left join
-
什么是右连接，关键字是什么？
在内连接的基础之上保证右表的数据都出现，right join
17 子查询：子查询引入
目标
- 什么是子查询
- 子查询是单行单列的情况
什么是子查询
-- 需求：查询开发部中有哪些员工
select * from emp;
select id from dept where name='开发部';
select * from emp where dept_id=1;
-- 写成一句：使用子查询
select * from emp where dept_id=(select id from dept where name='开发部');
select * from emp where dept_id=(select id from dept where name='市场部');
子查询的概念
- 将一个查询的结果做为另一个查询的条件
- 这是一种查询语句的嵌套，嵌套的SQL查询称为子查询。
- 如果使用子查询必须要使用括号
18 子查询: 单行单列的情况
子查询结果的三种情况：
-
单行单列：一个值
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XGkiA5vZ-1594611788233)(assets/1562225585954.png)]
-
多行单列：可以认为是一个数组
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uHyWasrB-1594611788235)(assets/1562225605676.png)]
-
多行多列：可以认为是一张虚拟表，我们可以二次查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pfpq5pAm-1594611788238)(assets/1562225632007.png)]
单行单列的情况的语法
如果子查询是单行单列，父查询使用比较运算符：> < =
代码
-- 案例：查询工资最高的员工是谁？
-- 1. 查询最高工资是多少
select max(salary) from emp;
-- 2. 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary=(select max(salary) from emp);
-- 查询工资大于"蜘蛛精"的员工
-- 1. 查询蜘蛛精的工资是多少
select salary from emp where name='蜘蛛精';
-- 2. 查询大于这个工资的员工
select * from emp where salary > (select salary from emp where name='蜘蛛精');
小结
单行单列的结果，父查询使用什么运算符？
使用比较运算符: > < = !=
19 子查询: 多行单列的情况
目标
多行单列的子查询如何操作
语法
多行单列认为是一个数组，父查询使用in /any /all
代码
-- 查询工资大于5000的员工，来自于哪些部门，得到部门的名字
-- 1. 先查询大于5000的员工所在的部门id
select dept_id from emp where salary > 5000;
-- 2. 再查询在这些部门id中部门的名字
-- Subquery returns more than 1 row
select * from dept where id=(select dept_id from emp where salary > 5000);
select * from dept where id in(select dept_id from emp where salary > 5000);
-- 列出工资高于在1号部门工作的所有员工，显示员工姓名和工资、部门名称。
-- 1. 查询1号部门所有员工的工资，得到多行单列
select salary from emp where dept_id=1;
-- 2. 使用大于号不能计算，怎么办
select * from emp where salary > all (select salary from emp where dept_id=1);
-- any表示任何一个，all所有
select * from emp where salary > any (select salary from emp where dept_id=1);
小结
多行单列的子查询，父查询使用什么关键字？
in, any, all
20 子查询: 多行多列的情况
目标
多行多列的子查询如何操作
语法
认为它是一张虚拟表，可以使用表连接再次进行多表查询
子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段
代码
-- 查询出2011年以后入职的员工信息，包括部门名称
-- 1. 在员工表中查询2011-1-1以后入职的员工
select * from emp where join_date > '2011-1-1';
-- 2. 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
select * from dept d inner join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;
select * from dept d right join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;
小结
多行多列的子查询如何操作的？
可以做成一张虚拟表再次进行查询，或者继续进行表连接
21 多表查询案例1
目标
通过5个案例学习多表连接查询
准备数据
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
四张表的关系
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h2G3WXff-1594611788244)(assets/1562309009547.png)]
效果
查询所有员工姓名，工资，工资等级
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UAa6uvZD-1594611788247)(assets/1562309171440.png)]
步骤
- 确定要查询哪些表，emp e, salarygrade s
- 确定表连接条件：员工表中工资数额与工资等级表之间的关联条件是：工资在最低与最高工资之间
- 确定查询字段：员工姓名，工资，工资等级
代码
-- 查询所有员工姓名，工资，工资等级
-- 1. 确定查询哪些表
select * from emp e inner join salarygrade s;
-- 2. 确定表连接的条件
select * from emp e inner join salarygrade s on e.salary between s.losalary and s.hisalary;
-- 3. 确定查询哪些列
select e.ename 姓名, e.salary 工资, s.grade 工资等级
from emp e inner join salarygrade s on e.salary between s.losalary and s.hialary;
22 多表查询案例2
目标
查询经理的信息。显示经理姓名，工资，职务名称，部门名称，工资等级
效果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QtbAMOkM-1594611788252)(assets/1562309276885.png)]
步骤
- 确定要查询哪些表，emp e, job j, dept d, salarygrade s
- 确定表连接条件
- e.job_id=j.id
- e.dept_id=d.id
- e.salary between s.losalary and hisalary
- 确定查询字段：经理姓名，工资，职务名称，部门名称，工资等级
- 额外条件：只需要查询经理的信息（j.jname=‘经理’）
代码
select e.ename 姓名, e.salary 工资, j.jname 职务, d.dname 部门名, s.grade 工资等级
from emp e inner join dept d on e.dept_id = d.id
inner join job j on j.id = e.job_id inner join salarygrade s
on e.salary between s.losalary and s.hisalary where j.jname='经理';
23 多表查询案例3
目标
查询部门编号、部门名称、部门位置、部门人数
效果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iRIYnJJp-1594611788256)(assets/1562309368651.png)]
步骤
- 因为要统计部门的人数，所以需要查询员工表。
- 首先对部门和员工使用左连接，因为财务部没有员工。
- 然后进行分组统计，统计的字段为员工表的id
代码
select d.*, count(e.dept_id) 部门人数 from dept d left join emp e on d.id = e.dept_id group by d.id;
24 多表查询案例4
目标
列出所有员工的姓名及其直接上级的姓名，没有领导的员工也需要显示
自连接概念
不是一种新的连接，左表与右表是同一张表
效果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f8xjU18Y-1594611788259)(assets/1562309544494.png)]
步骤
- 确定要查询哪些表，emp e, emp m
- 确定表连接条件 e.mgr=m.id
- 发现员工表中少了一条数据，因为罗贯中是董事长没有上司，没有领导的员工也需要显示，所以左表的数据需要全部显示。应该改成左外连接。
- 确定查询字段：员工的姓名及其直接上级的姓名
- 注：IFNULL(字段名,默认值) 函数的作用是，如果前面的字段有值，则显示原有的值，如果没有值，则显示后面的参数
代码
select e.ename 员工名字, IFNULL(m.ename,'BOSS') 上级名字 from emp e left join emp m on e.mgr = m.id;
25 多表查询案例5
目标
查询工资高于公司平均工资的所有员工列：员工所有信息，部门名称，上级领导，工资等级。
注：所有员工都要显示出来，没有上级的员工显示为"自己"
效果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6Hm7LihL-1594611788262)(assets/1562309634144.png)]
步骤
- 使用子查询，先统计公司平均工资
- 确定要查询哪些表：emp e, emp m, dept d, salarygrade s
注：为了让所有的员工都显示出来，e与m的查询要使用左连接，后面的d和s使用内连接。
先将e与m进行左连接，得到结果再与d进行内连接，得到结果再与s进行内连接 - 确定表连接条件 e.dept_id=d.id、e.mgr=m.id、e.salary between s.losalary and hisalary、e.salary>公司平均薪金
- 确定查询字段：员工所有信息，部门名称，上级领导，工资等级。
代码
-- 查询工资高于公司平均工资 员工所有信息，部门名称，上级领导，工资等级。没有上级的员工显示为"自己"
-- 查询公司平均工资
select avg(salary) from emp;
-- 1. 确定查询哪些表：员工表，员工表，部门表，工资等级
select * from emp e left join emp m on e.mgr = m.id inner join dept d on d.id = e.dept_id inner join salarygrade s
on e.salary between s.losalary and s.hisalary;
-- 2. 确定表连接的条件
-- 3. 确定查询哪些列
select e.*,d.dname 部门名, ifnull(m.ename,'自己') 上级名字, s.grade 工资等级 from emp e left join emp m on
e.mgr = m.id inner join dept d on d.id = e.dept_id inner join salarygrade s
on e.salary between s.losalary and s.hisalary where e.salary > (select avg(salary) from emp);
小结：多表查询规律
-
通常关联字段：主表.主键=从表.外键，也不一定。
-
关联的条件数 = 表数量 - 1
-
多表查询的步骤：
- 确定查询哪些表
- 确定表连接条件
- 确定查询哪些列
- 如果还有过滤条件使用where
总结
-
能够使用SQL语句添加主键、外键、唯一、非空约束
约束名 约束关键字 主键 primary key 非空 not null 唯一 unique 外键 foreign key(外键列) references 主表(主键) 默认 default -
能够使用DCL处理MySQL中的用户（了解即可）
- 创建用户：create user ‘用户名’@‘主机名’ identified by ‘密码’
- 授权：grant 权限 on 库名.表名 to ‘用户名’@‘主机名’
- 撤销：revoke 权限 on 库名.表名 from ‘用户名’@‘主机名’
- 删除用户：drop user ‘用户名’@‘主机名’
-
能够理解三大范式
范式 特点 第一范式 原子性：表中每列不可再拆分 第二范式 表中每列完全依赖于主键。 第三范式 不产生传递依赖，开始拆表，表中和每列都是直接依赖于主键，建立外键关联数据 -
能够说出多表之间的关系及其建表原则
表与表的关系 关系的维护 一对多 拿1的一方的主键作为多的一方的外键。 多对多 创建一张中间表，拿两方的主键作为中间表的联合主键。 一对一 1. 外键约束设置唯一
2. 从表的主键又是外键 -
能够使用内连接进行多表查询
-- 1.隐式内连接语法: select 列名 from 左表,右表 where 从表.外键=主表.主键 SELECT * FROM employee ， dept WHERE employee.`dept_id` = dept.id; -- 2.显式内连接语法: select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键 SELECT * FROM employee INNER JOIN dept ON employee.`dept_id` = dept.`id`; -- inner可以省略不写 SELECT * FROM employee JOIN dept ON employee.`dept_id` = dept.`id`; -
能够使用左外连接和右外连接进行多表查询
select 列名 from 左表 left join 右表 on 条件select 列名 from 左表 right join 右表 on 条件 -
能够使用子查询进行多表查询
- 单行单列：子查询结果只有一个值，父查询使用比较运算符 = > < >= <= !=
- 多行单列：子查询结果有多个值，父查询使用in all any
- 多行多列：子查询结果是表了，将查询结果做成一张虚拟表，再次查询
-
能够理解多表查询的规律
-- a.确定表：员工表，部门，等级表 -- b.简单粗暴连接表。
– c.加条件 注意：边连表边加条件。
ue |
| **外键** | foreign key(外键列) references 主表(主键) |
| **默认** | default |
2. 能够使用DCL处理MySQL中的用户（了解即可）
1. 创建用户：create user '用户名'@'主机名' identified by '密码'
2. 授权：grant 权限 on 库名.表名 to '用户名'@'主机名'
3. 撤销：revoke 权限 on 库名.表名 from '用户名'@'主机名'
4. 删除用户：drop user '用户名'@'主机名'
3. 能够理解三大范式
| **范式** | **特点** |
| ------------ | ------------------------------------------------------------ |
| **第一范式** | 原子性：表中每列不可再拆分 |
| **第二范式** | 表中每列完全依赖于主键。 |
| **第三范式** | 不产生传递依赖，开始拆表，表中和每列都是直接依赖于主键，建立外键关联数据 |
4. 能够说出多表之间的关系及其建表原则
| **表与表的关系** | **关系的维护** |
| ---------------- | -------------------------------------------------- |
| **一对多** | 拿1的一方的主键作为多的一方的外键。 |
| **多对多** | 创建一张中间表，拿两方的主键作为中间表的联合主键。 |
| **一对一** | 1. 外键约束设置唯一<br />2. 从表的主键又是外键 |
5. 能够使用内连接进行多表查询
```mysql
-- 1.隐式内连接语法: select 列名 from 左表,右表 where 从表.外键=主表.主键
SELECT * FROM employee ， dept WHERE employee.`dept_id` = dept.id;
-- 2.显式内连接语法: select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键
SELECT * FROM employee INNER JOIN dept ON employee.`dept_id` = dept.`id`;
-- inner可以省略不写
SELECT * FROM employee JOIN dept ON employee.`dept_id` = dept.`id`;
-
能够使用左外连接和右外连接进行多表查询
select 列名 from 左表 left join 右表 on 条件select 列名 from 左表 right join 右表 on 条件 -
能够使用子查询进行多表查询
- 单行单列：子查询结果只有一个值，父查询使用比较运算符 = > < >= <= !=
- 多行单列：子查询结果有多个值，父查询使用in all any
- 多行多列：子查询结果是表了，将查询结果做成一张虚拟表，再次查询
-
能够理解多表查询的规律
-- a.确定表：员工表，部门，等级表 -- b.简单粗暴连接表。
– c.加条件 注意：边连表边加条件。
3853
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
