1 表连接:笛卡尔积和内连接。
为什么需要有多表操作
准备数据:
# 创建部门表
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);
查询某员工在哪个部门?
sql 只查询一张表不能查询出员工名字和部门名字,需要使用多表操作。
完成多表操作的两种方式:
- 表连接
- 子查询
表连接的分类
隐式内连接:不出现join关键字
显示内连接:出现join…on关键字
左外连接,通常又叫左连接。右外连接通常又叫右连接。
笛卡尔积现象
什么是笛卡尔积
-- 需求:查询所有的员工和所有的部门
内连接
如何清除笛卡尔积现象的影响
我们发现不是所有的数据组合都是有用的,只有员工表.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-qYQ2jSzC-1594720214575)(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 左表 JOIN 右表 ON 从表.外键=主表.主键- 隐式:条件跟在where的后面
SELECT 列名 FROM 左表,右表 WHERE 从表.外键=主表.主键
2 外连接:左连接和右连接
左连接
语法
select 列名 from 左表 left join 右表 on 从表.外键=主表.主键
案例
左连接:左表中所有的记录都出现在结果,如果右表没有匹配的记录,使用NULL填充
-- 需求:在部门表中增加一个销售部,需要查询所有的部门和员工,将部门表设置成左表,员工表设置成右表
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填充
右连接
语法
select 列名 from 左表 right join 右表 on 从表.外键=主表.主键
案例
右连接:保证右表中所有的数据都出现,如果左表没有对应的记录,使用NULL填充
-- 右连接
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;
- 什么是左连接,关键字是什么?
在内连接的基础之上保证左表的数据都出现,left join- 什么是右连接,关键字是什么?
在内连接的基础之上保证右表的数据都出现,right join
3 子查询:子查询引入
什么是子查询
-- 需求:查询开发部中有哪些员工
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查询称为子查询。
- 如果使用子查询必须要使用括号
4 子查询: 单行单列的情况
子查询结果的三种情况:
- 单行单列:一个值
- 多行单列:可以认为是一个数组
- 多行多列:可以认为是一张虚拟表,我们可以二次查询
单行单列的情况的语法
如果子查询是单行单列,父查询使用比较运算符:> < =
代码
-- 案例:查询工资最高的员工是谁?
-- 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='蜘蛛精');
单行单列的结果,父查询使用什么运算符?
使用比较运算符: > < = !=
5 子查询: 多行单列的情况
语法
多行单列认为是一个数组,父查询使用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
6 子查询: 多行多列的情况
语法
认为它是一张虚拟表,可以使用表连接再次进行多表查询
子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段
代码
-- 查询出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;
多行多列的子查询如何操作的? 可以做成一张虚拟表再次进行查询,或者表连接
7 多表查询案例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);
四张表的关系
步骤
- 确定要查询哪些表,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;
8 多表查询案例2
目标
查询经理的信息。显示经理姓名,工资,职务名称,部门名称,工资等级
步骤
- 确定要查询哪些表,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='经理';
9 多表查询案例3
目标
查询部门编号、部门名称、部门位置、部门人数
步骤
- 因为要统计部门的人数,所以需要查询员工表。
- 首先对部门和员工使用左连接,因为财务部没有员工。
- 然后进行分组统计,统计的字段为员工表的id
代码
select d.*, count(e.dept_id) 部门人数 from dept d left join emp e on d.id = e.dept_id group by d.id;
10 多表查询案例4
目标
列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示
自连接概念
不是一种新的连接,左表与右表是同一张表
步骤
- 确定要查询哪些表,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;
11 多表查询案例5
目标
查询工资高于公司平均工资的所有员工列:员工所有信息,部门名称,上级领导,工资等级。
注:所有员工都要显示出来,没有上级的员工显示为"自己"
效果
步骤
- 使用子查询,先统计公司平均工资
- 确定要查询哪些表: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.加条件 注意:边连表边加条件。