08_MySQL数据库_复合查询

柿子__

已于 2022-11-05 11:43:42 修改

阅读量630

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2022-10-24 17:00:55 首次发布

本文链接：https://blog.csdn.net/Yohe1937/article/details/127495089

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

此专栏所有章节快速导航

01_MySQL数据库_CentOS7安装MySQL
02_MySQL数据库_数据库基础知识
 03_MySQL数据库_库的操作
 04_MySQL数据库_表的操作
 05_MySQL数据库_数据类型
 06_MySQL数据库_表的约束
 07_MySQL数据库_增删查改
 08_MySQL数据库_复合查询
 09_MySQL数据库_索引
 10_MySQL数据库_事务管理
 11_MySQL数据库_用户管理
 12_MySQL数据库_CentOS7下C-C++链接MySQL

一. 需要用到的表

1.1 emp

在这里插入图片描述

1.2 dept

在这里插入图片描述

1.3 salgrade

在这里插入图片描述

二. 多表查询

2.1 简述

有时候我们需要查询的数据来自多张表，这时候我们就要进行多表查询了。多表查询我们的总体思路是将多张表进行笛卡尔积，然后去除无用的选项得到一张表，我们就在这张表里面查询需要的信息。

2.2 案例

# 显示部门号为10的部门名员工名和工资
select emp.deptno ,dname, ename, sal from emp, dept where emp.deptno = dept.deptno and emp.deptno = 10;

# 显示各个员工的姓名，工资，及工资级别
 select ename, sal, grade from emp, salgrade where sal >= losal and sal <= hisal;
 select ename, sal, grade from emp, salgrade where sal between losal and hisal;

三. 子查询

3.1 简述

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

3.2 单行单列子查询

# 显示SMITH同一部门的员工
select * from emp where deptno = (select deptno from emp where ename='SMITH');

3.3 多行单列子查询

3.3.1 in关键字

# 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的
# job in (job1, job2, ...) 只要匹配上一个就是可以了
select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno=10) and deptno != 10;

3.3.2 all关键字

# 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
# sal > all(sal1, sal2...) 要比括号里面的所有的都高才可以
select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30);

3.3.3 any关键字

# 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号
# sal > any(sal1, sal2...) 只要大于在里面任意一个就可以了
select ename, sal, deptno from emp where sal > any(select sal from emp where deptno = 30);

3.4 单行多列子查询

# 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
select deptno, ename, job from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH') and ename != 'SMITH';

3.5 在from子句中用子查询

3.5.1 思路

把子查询出来的数据当成一个表，然后用from跟其他表做笛卡尔积。

3.5.2 案例

# 显示每个高于自己部门平均工资的员工的姓名、部门、工资、部门的平均工资
select ename, emp.deptno, sal, avgsal from emp, (select deptno, avg(sal) as avgsal from emp group by deptno) as avgsal where emp.deptno = avgsal.deptno and sal > avgsal;

# 显示每个部门的信息（部门名，编号，地址）和人员数量
# 方法一：用多表查询
select dept.deptno, dept.dname, dept.loc, count(*) from emp, dept where emp.deptno = dept.deptno group by dept.deptno, dept.dname, dept.loc;

# 方法二：用子查询
select dept.deptno, dept.dname, dept.loc, ct.ctnums from dept, (select deptno, count(*) as ctnums from emp group by deptno) as ct where dept.deptno = ct.deptno;

三. 自连接

3.1 简述

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

3.2 案例

# 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）
# 方法一：使用子查询
select empno, ename from emp where empno = (select mgr from emp where ename = 'FORD');

# 方法二：使用多表查询（）
select leader.empno, leader.ename from emp as leader, emp as worker where worker.mgr = leader.empno and worker.ename = 'FORD';

四. 合并查询

4.1 简述

union将多条查询结果合并，去掉重复的，就相当于是获取并集，前提是他们的列字段是一样的。 还有一个是union all，这个是不会直接合并，没有去掉重复的。

4.2 案例

# 将工资大于2500或职位是MANAGER的人找出来
# 方法一：用合并查询union
select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';

# 方法二：直接用or
select * from emp where sal > 2500 or job = 'MANAGER';

五. 自连接

5.1 简述

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

5.2 案例

# 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）
# 方法一：使用子查询
select empno, ename from emp where empno = (select mgr from emp where ename = 'FORD');

# 方法二：使用多表查询（）
select leader.empno, leader.ename from emp as leader, emp as worker where worker.mgr = leader.empno and worker.ename = 'FORD';

六. 内外连接

6.1 内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，这是我们之前常用的方法，还有一种比较标准的写法就是用表1 inner join 表2 on 连接条件这种写法。

# 显示SMITH的名字和部门名称
# 方法一：之前用的内连接方法
select ename, dname from emp, dept where emp.deptno = dept.deptno and ename = 'SMITH';

# 方法二：标准的内连接
select ename, dname from emp inner join dept on emp.deptno=dept.deptno and ename='SMITH';

6.2 外连接

6.2.1 需要用的数据库

create table student (id int, name varchar(30)); 
insert into student values(1,'诸葛神奇'),(2,'张大炮'),(3,'上官琪琪'),(4,'朱小明');
create table exam (id int, grade int);
insert into exam values(1, 90),(2,89),(11, 100);

在这里插入图片描述

6.2.2 左外连接

做笛卡尔积后，左侧的表完全显示我们就说是左外连接，如下图所示，左边exam表的数据是要显示完整的，右边的没有就置空。

# 查找哪些成绩无效的（即在考试表里有的id，在学生表里是没有的）
# 1. 先做左外连接
select * from exam left join student on exam.id = student.id;

在这里插入图片描述

# 2. 在上面左外连接的基础上筛选
select * from exam left join student on exam.id = student.id where student.id is null;

在这里插入图片描述

6.2.3 右外连接

做笛卡尔积后，右侧的表完全显示我们就说是右外连接,如下面图片所示。

# 找出不来考试（没有成绩的）的学生
# 1. 先做右外连接
select * from exam right join student on exam.id = student.id;

在这里插入图片描述

# 2. 在上面右外连接的基础上筛选
select * from exam right join student on exam.id = student.id where exam.id is null;

在这里插入图片描述

七. 视图

7.1 简述

前面我们说过我们可以把select查询到的数据当成一张表，当时这张表是在内存中的。视图就是将这张表命名，然后保存到硬盘中，变成一张表。 基表（我们用select查询的表）和它的视图，可以理解成之前的文件和它的软连接一样，修改视图上的数据，表上相应的数据也会发生改变，修改表中的数据，视图的数据也会改变。

7.2 创建视图

# 创建视图
# 语法：create view 视图名 as select语句；
create view v_ename_dname as select ename, dname from emp, dept where emp.deptno=dept.deptno;

7.3 删除视图

# 语法：drop view 视图名；
drop view v_ename_dname;

7.4 视图规则和限制

名字不能跟当前数据库中的表或者视图名相同
创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响
视图不能添加索引，也不能有关联的触发器或者默认值
视图可以提高安全性，必须具有足够的访问权限
order by 可以用在视图中，但是如果从该视图检索数据select中也含有order by，那么该视图中的order by将被覆盖
视图可以和表一起使用，即在他们任何一边修改表，两边的数据都会被改变