本文详细介绍了数据库中多表查询与子查询的概念、语法及应用案例，包括内连接、外连接、子查询的不同类型及其在实际场景中的运用。
多表查询
一. 查询语法：
select
列名列表
from
表名列表
where…
样表
# 创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
)
# 插入数据
insert into dept (id,name) values (1,"开发部"),(2,"市场部"),(3,"财务部");
select * from dept;
# 创建员工表
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);
select * from emp;
笛卡尔积：
有两个集合A,B .取这两个集合的所有组成情况。
要完成多表查询，需要消除无用的数据
select * from emp,dept; -- emp表中有5条数据，dept表中有3条数据，共查询 5 * 3 = 15条数据
二. 多表查询的分类：
内连接查询
隐式内连接：
使用where条件消除无用数据
select
t1.id,t1.name,t1.gender,t2.name
from
emp t1, dept t2
where
t1.dept_id = t2.id
显式内连接：
语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
# 显式内连接 inner join on
select
t1.`name`,t1.gender,t2.`name`
from
emp t1
inner join
dept t2
on
t1.dept_id = t2.id
内连接查询注意事项：
- 从哪些表中查询数据
- 条件是什么
- 查询哪些字段
外链接查询：
左外连接：
语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
- 查询的是左表所有数据以及其交集部分。
案例：
查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
** 左外连接**
left join on
select
t1.*,t2.name
from
emp t1
left join
dept t2
on
t1.dept_id = t2.id
右外连接
语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
- 查询的是右表所有数据以及其交集部分。
案例：
# 右外连接 right join
select
t1.*,t2.name
from
emp t1
right join
dept t2
on
t1.dept_id = t2.id
三. 子查询：
概念：查询中嵌套查询，称嵌套查询为子查询。
案例
# 子查询
#查询最高工资
select max(salary) from emp;
# 查询最高工资的人
select * from emp where salary = 9000；
# 查询员工表中员工工资最高的人
select * from emp where salary = (select max(salary) from emp);
- 子查询不同情况
- 子查询的结果是单行单列的：
- 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =
# 查询工资小于平均工资的人
# 第一步，查询平均工资
select avg(salary) from emp; -- 5383.333333333333
# 第二步，查询小于平均工资的人
select * FROM emp where salary < 5383.333333333333;
# 利用子查询
select * from emp where salary < (select avg(salary) from emp);
- 子查询的结果是多行单列的：
- 子查询可以作为条件，使用运算符in来判断
# 查询财务部和市场部所有的员工信息
# 第一步查询财务部和市场部的id
select id from dept where name = "财务部" or name = "市场部";
# 第二步，查询这两个信息，对应的所有人员
select * from emp where dept_id = 2 or dept_id = 3; -- 或者
select * from emp where dept_id in (2,3);
# 合并
select * from emp where dept_id in (select id from dept where name = "财务部" or name = "市场部");
- 子查询的结果是多行多列的：
- 子查询可以作为一张虚拟表参与查询
# 查询2011-11-11后入职的员工的信息和部门信息
select * from emp where join_date > '2011-11-11';
select * from dept t1, (select * from emp where emp.join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
– 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > ‘2011-11-11’
四. 案例
多表查询练习
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY 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);
需求：
1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
select
t1.id,
t1.ename,
t1.salary,
t2.jname,
t2.description
from
emp t1,
job t2
where
t1.job_id = t2.id;
2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
select
t1.id,
t1.ename,
t1.salary,
t2.jname,
t2.description,
t3.dname,
t3.loc
from
emp t1,
job t2,
dept t3
where
t1.job_id = t2.id and t1.dept_id = t3.id;
3.查询员工姓名，工资，工资等级
select
t1.ename,
t1.salary,
t2.grade
from
emp t1,
salarygrade t2
where
t1.salary between t2.losalary and t2.hisalary;
4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
select
t1.ename, -- 姓名
t1.salary, -- 工资
t2.jname, -- 职务名称
t2.description, -- 职务描述
t3.dname, -- 部门名称
t3.loc, -- 部门位置
t4.grade, -- 工资等级
t4.* -- 测试查看，等级是否正确
from
emp t1, -- 员工表
job t2, -- 职位表
dept t3, -- 部门表
salarygrade t4 -- 工资等级表
where
t1.job_id = t2.id
and
t1.dept_id = t3.id
and
t1.salary between t4.losalary and t4.hisalary;
5.查询出部门编号、部门名称、部门位置、部门人数
select
t1.id,
t1.dname,
t1.loc,
t2.total
from
dept t1,
(select dept_id, count(id) total from emp group by emp.dept_id) t2;
6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
select
t1.ename,
t1.mgr,
t2.id,
t2.ename
from
emp t1
left join
emp t2
on
t1.mgr = t2.id;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
