当前位置:   article > 正文

MySQL学习--多表查询总结_多表查询 mysql心得

多表查询 mysql心得

MySQL学习–多表查询总结

概述

  • 从多张表中查询出指定的数据;
  • “笛卡尔积”:笛卡尔乘积是指在数学中,两个集合:A集合 和 B集合 的所有组合情况。
    • 注意:在多表查询时,需要消除无效的笛卡尔积

在这里插入图片描述

-- 单表查询
select * from emp;

-- 多表查询 
select * from emp, dept where dept_id = dept.id;# 笛卡尔积,使用where消除无效的数据
  • 1
  • 2
  • 3
  • 4
  • 5

分类

连接查询

内连接
  • 相当于查询A,B两集合交集部分的数据;
隐式内连接

语法

select 字段列表 from 表1, 表2 where 条件...;
  • 1

案例

-- 查询每一个员工的姓名、关联的部门名称 (隐式内连接实现)
select emp.name 员工姓名, dept.name 部门 from emp, dept where dept_id = dept.id;
-- 给表起别名
select e.name 员工姓名, d.name 部门 from emp e, dept d where e.dept_id = d.id;
  • 1
  • 2
  • 3
  • 4
显式内连接

语法

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
  • 1

案例

-- 查询每一个员工的姓名、关联的部门名称 (显式内连接实现)
select emp.name 员工姓名, dept.name 部门 from emp inner join dept on emp.dept_id = dept.id;
select e.name 员工姓名, d.name 部门 from emp e join dept d on e.dept_id = d.id;
  • 1
  • 2
  • 3
显式和隐式内连接的区别
  • 两种内连接的作用相同;
  • 但是,通常建议使用显式内连接语法,因为它更易于阅读和理解,尤其是在复杂查询中;
外连接
  • 左表是指outer join 左边的表;
  • 右表是指outer join 右边的表;
左外连接
  • 查询左表所有的数据,以及两张表交集部分的数据;

语法

select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
  • 1

案例

-- 查询emp中的所有数据,和对应的部门信息(左外连接实现)
select * from emp left outer join dept on emp.dept_id = dept.id;
  • 1
  • 2

注意:如果emp表中,有一条记录的dept_id字段值为null,则:若使用内连接查询,则不显示该记录;而使用左外连接可以显式;

右外连接
  • 查询右表所有的数据,以及两张表交集部分的数据;

语法

select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;
  • 1

案例

-- 查询dept中的所有数据,和对应的员工信息(右外连接实现)
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

-- 改成左外连接实现
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;
  • 1
  • 2
  • 3
  • 4
  • 5
自连接
  • 查询当前表与自身的连接查询,自连接必须使用表的别名

语法

select 字段列表 from 表1 别名1 join 表2 别名2 on 连接条件...;
  • 1

案例

-- 查询员工 及其 所属领导的名字(自连接-内连接查询)
select e1.*, e2.name 所属领导 from emp e1 join emp e2 on e1.managerid = e2.id;


-- 查询所有员工 及其 领导的名字,如果没有领导,也要查询出来;(自连接-外连接查询)
select e1.*, e2.name 所属领导 from emp e1 left join emp e2 on e1.managerid = e2.id;

-- 其实就是,自己和自己连接,再查询;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

联合查询(union/union all)

  • 把多次查询的结果合并起来,形成一个新的查询结果集;
  • 多次查询的字段数量类型需要相同;
  • union和union all区别:去掉all,可以去除查询出来的重复记录;

语法

select 字段列表 from 表1 ...
union [all]
select 字段列表 from 表2 ...;
  • 1
  • 2
  • 3

案例

-- 把薪资低于8000 的员工, 和 年龄小于 25 的员工都查询出来
select * from emp where salary < 80000
union all
select * from emp where age < 25;

-- 去掉all,可以去除重复记录
select * from emp where salary < 80000
union
select * from emp where age < 25;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

子查询

概念
  • SQL语句中嵌套select语句,又称嵌套查询
select * from 表1 where 字段名1 = (select 字段名1 from 表2);

-- 子查询外部的语句可以insert/update/delete/select的任意一个;
  • 1
  • 2
  • 3
分类
  • 根据子查询结果的不同,分为:

    • 标量子查询(子查询结果为单个值)

    • 列子查询(子查询结果为一列)

    • 行子查询(子查询结果为一行)

    • 表子查询(子查询结果为多行多列)

  • 根据子查询出现的位置,分为:

    • where之后
    • from之后
    • select之后
标量子查询
  • 子查询结果为单个值

  • 案例

 -- 标量子查询(子查询结果为单个值)
 
 select * from emp;
 
 -- 1.查询“销售部”的所有员工信息
SELECT * FROM emp e WHERE e.dept_id = (
	SELECT d.id FROM dept d WHERE d.name = "销售部"
)
 
 -- 2.查询在“赵敏” 之后入职的员工
 SELECT * FROM emp WHERE entrydate > (
	SELECT entrydate from emp where name ="赵敏"
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
列子查询
  • 子查询结果为一列(或多列)
  • 常用的操作符
操作符描述
IN在指定的集合范围之内,多选一
NOT IN不在指定的集合范围之内
ANY子查询返回列表中,有任意一个满足即可
SOME与ANY等同,使用SOME的地方都可以使用ANY
ALL子查询返回列表的所有值都必须满足
  • 案例
-- 列子查询(子查询结果为一列或多列)

-- 1.查询“销售部”和“市场部”的所有员工信息   => IN
SELECT * FROM emp WHERE dept_id in (
	SELECT id FROM dept WHERE name in ("销售部","市场部")
)

-- 2.查询比财务部所有人工资都高的员工信息   => ALL 
SELECT * FROM emp WHERE salary > ALL(
	SELECT salary FROM emp WHERE dept_id = (
		SELECT id FROM dept WHERE name = "财务部"
	) 
)

SELECT * FROM emp WHERE salary > ( # max()
	SELECT max(salary) FROM emp WHERE dept_id = (
		SELECT id FROM dept WHERE name = "财务部"
	) 
)

-- 3.查询比研发部其中任意一人工资高的员工信息   => ANY/SOME
SELECT * FROM emp WHERE salary > ANY(
	SELECT salary FROM emp WHERE dept_id = (
		SELECT id FROM dept WHERE name = "研发部"
	)
)

SELECT * FROM emp WHERE salary > SOME(
	SELECT salary FROM emp WHERE dept_id = (
		SELECT id FROM dept WHERE name = "研发部"
	)
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
行子查询
  • 子查询结果为一行(或多行)
  • 常用的操作符
操作符描述
=等于
<>不等于
IN在指定集合范围内,多选一
NOT IN不在指定集合范围内
  • 案例
-- 行子查询(返回结果为一行或多行)

-- 1. 查询与“张无忌”的薪资及直属领导相同的员工信息;
SELECT * FROM emp WHERE salary = (
	SELECT salary FROM emp WHERE name = "张无忌"
)
AND 
managerid = (
	SELECT managerid FROM emp WHERE name = "张无忌"
)


SELECT * FROM emp WHERE (salary, managerid) = (
	SELECT salary, managerid FROM emp WHERE name = "张无忌"
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
表子查询
  • 子查询结果为多行多列

  • 常用操作符:IN

  • 案例

-- 表子查询

-- 1.查询与“小张”,“小陈”的薪资和岗位相同的员工信息
SELECT * FROM emp WHERE (salary, job) IN (
	SELECT salary, job FROM emp WHERE name IN ("小张", "小陈")
);


-- 2.查询入职日期是“2002-01-01”之后的员工及其部门信息

-- 隐式内连接 + 列子查询
SELECT e.*, d.name 部门 FROM emp e, dept d WHERE e.dept_id = d.id AND entrydate IN (
	SELECT entrydate FROM emp WHERE entrydate > "2002-01-01"
)

-- 左外连接 + 表子查询
SELECT e.*, d.name 部门 FROM (SELECT * FROM emp WHERE entrydate > "2002-01-01") e LEFT JOIN dept d ON e.dept_id = d.id
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

多表查询案例

  • 案例描述

-- 多表查询总结

-- 1.查询员工的姓名、年龄、职位、部门信息。
SELECT emp.name, age, job, dept_id, d.name 部门 FROM emp, dept d WHERE emp.dept_id = d.id

-- 2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
SELECT emp.name, age, job, dept_id, d.name 部门 FROM emp, dept d WHERE emp.dept_id = d.id AND age < 30

-- 3.查询拥有员工的部门ID、部门名称。
SELECT id, name FROM dept WHERE (
	SELECT COUNT(id) FROM emp
) > 0

-- 4.查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来。
SELECT e.*,d.name 部门 FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.age > 40

-- 5.查询所有员工的工资等级。
create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,300000);

SELECT e.name, e.salary, s.grade 薪资等级 FROM emp e LEFT JOIN salgrade s ON e.salary >= s.losal AND e.salary < s.hisal

-- 6.查询“研发部”所有员工的信息及工资等级。
SELECT e.*, s.grade 薪资等级 FROM emp e LEFT JOIN salgrade s ON e.salary >= s.losal AND e.salary < s.hisal WHERE e.dept_id = (
	SELECT id FROM dept WHERE name = "研发部"
)

-- 7.查询“研发部”员工的平均工资。
SELECT AVG(e.salary) 平均薪资 FROM emp e JOIN dept d on e.dept_id = d.id WHERE d.name = "研发部" 

-- 8.查询工资比“赵敏"高的员工信息。
SELECT * FROM emp WHERE salary > (
	SELECT salary FROM emp WHERE name = "赵敏"
)

-- 9.查询比平均薪资高的员工信息。
SELECT * FROM emp WHERE salary > (
	SELECT AVG(e.salary) FROM emp e
)

-- 10.查询低于本部门平均工资的员工信息。
SELECT e.* FROM emp e
	LEFT JOIN ( SELECT dept_id, AVG( salary ) avg_salary FROM emp GROUP BY dept_id ) avg_s ON e.salary > avg_s.avg_salary 
WHERE
	e.dept_id = avg_s.dept_id

-- 11.查询所有的部门信息,并统计部门的员工人数。
SELECT d.name, COUNT(e.id) 部门人数 FROM emp e JOIN dept d ON e.dept_id = d.id GROUP BY e.dept_id

-- 12.查询所有学生的选课情况,展示出学生名称,学号,课程名称。
-- (1)3张表连接
SELECT s.name, s.no, c.name 课程名称 FROM student s, course c, student_course s_c WHERE s.id = s_c.stu_id AND s_c.course_id = c.id 

-- (2) 表子查询 + 显示内连接
SELECT s.name, s.no, s_c_c.name 课程名称 FROM student s JOIN (SELECT s_c.stu_id, s_c.course_id, c.name FROM student_course s_c, course c WHERE s_c.course_id = c.id) s_c_c ON s.id = s_c_c.stu_id
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/人工智能uu/article/detail/834006
推荐阅读
相关标签
  

闽ICP备14008679号