MySQL——复合查询表的内外连

目录

复合查询

回顾基本查询

多表查询

自连接

子查询 

where 字句中使用子查询

单行子查询

多行子查询

多列子查询

from 字句中使用子查询

合并查询

实战OJ

查找所有员工入职时候的薪水情况

获取所有非manager的员工emp_no

获取所有员工当前的manager

表的内外连

内连接

外连接

左连接

右外连接

实战OJ

分数排名

换座位

---

复合查询

回顾基本查询

        查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

        按照部门号升序而雇员的工资降序排序

        使用年薪进行降序排序

        显示工资最高的员工的名字和工作岗位

        显示工资高于平均工资的员工信息

        显示每个部门的平均工资和最高工资

        显示(按照每个部门)平均工资低于2000的部门号和它的平均工资

        显示每种岗位的雇员总数，平均工资

多表查询

        实际开发中往往数据来自不同的表，所以需要多表查询

        使用 select * from 连接两张表时用逗号隔开，结果是两张表的所有记录组合，称为笛卡尔积

        显示雇员名、雇员工资以及所在部门的名字

        显示部门号为10的部门名，员工名和工资

        显示各个员工的姓名，工资，及工资级别

自连接

        自连接是指在同一张表连接查询

        显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号 empno是公司员工编号）

子查询 

        子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

where 字句中使用子查询

单行子查询

        返回一行记录的子查询

        显示SMITH同一部门的员工

多行子查询

        返回多行记录的子查询

        查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的（使用 in 关键字）

        显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号（可以使用 all 关键字）

        显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（比部门30的最小工资大即可）（使用 any 关键字）

多列子查询

        单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句

        查询和SMITH的部门和岗位完全相同的所有雇员的名字，岗位和部门，不含SMITH本人

from 字句中使用子查询

        显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

        查找每个部门工资最高的人的姓名、工资、部门、最高工资（太长省略as关键字）

        显示每个部门的信息（部门名，编号，地址）和人员数量

合并查询

        在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all找并集

        将工资大于2500或职位是MANAGER的人找出来（使用 union 把不出现重复行）

        使用union all 可出现结果重复（出现交集的地方允许重复）

实战OJ

查找所有员工入职时候的薪水情况

select s.emp_no,s.salary from employees e,salaries s 
where e.emp_no=s.emp_no and e.hire_date=s.from_date 
order by emp_no desc;

获取所有非manager的员工emp_no

select emp_no from employees where emp_no not in (select emp_no from dept_manager);

获取所有员工当前的manager

select e.emp_no,m.emp_no from dept_emp e,dept_manager m
where e.dept_no=m.dept_no and e.emp_no!=m.emp_no;

表的内外连

内连接

        内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面的多表查询都是内连接，也是在开发过程中使用的最多的连接查询

        显示SMITH的名字和部门名称

外连接

        外连接分为左连接与右连接

左连接

        多表查询，左侧的表显示所有信息我们就说是左外连接

        建两张表：学生表与成绩表并在里面插入对应的数据

        查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

右外连接

        多表查询，右侧的表显示所有信息我们就说是右外连接

        对stu表和stu_exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它应，也要显示出来

        列出部门名称和这些部门的员工名，同时列出没有员工的部门

实战OJ

分数排名

相同的表查询：

第一张a表中我们对分数进行降序排序；

第二张b表中我们去除重复的分数后得到集合X：在X中找到比a表的分数大于等于的数的元素个数，此时该元素个数就是a表的分数在表中的排名，比如：最高为98，在集合X中找到的分数有98，99，100：此时元素个数为3也就是98分的排名

select a.score score,
(select count(distinct b.score) from Scores b where b.score>=a.score) 'rank'
from Scores a order by a.score desc;

换座位

遇到id遇到偶数-2 / 偶数-1，奇数+1

select rank() over(order by if(id%2=0,id-2,id)) as id,student from Seat;
-- select id,student from Seat order by if(id%2=0,id-2,id);

以上便是全部内容，有问题欢迎在评论区指正，感谢观看！