数据库：DQL操作

目录

一、基本查询

1.基本语法格式

2.常见查询

二、高级查询

1.关联查询（也称为连接查询）

2.子查询

3.联合查询：合并结果集

---

一、基本查询

1.基本语法格式

语法格式	作用
select 字段或表达式	展示结果集
from 表、视图或结果集等	指定数据源
where 检索条件	指定检索条件
group by 字段	指定按某字段分组
having 检索条件	用在group by之后，指定检索条件
order by 字段	指定按某字段进行排序，默认增序asc，降序desc
limit 初始索引, 长度	限制结果，索引从0开始，若为0，则可省略仅写长度

2.常见查询

2.1 select 指定展示结果集

#导入了3个sql文件，老师给的！
#1.查询emp表中所有的员工信息
select * from emp;
#2.在emp表中查询所有员工的姓名和职位信息
select ename, job from emp;

2.2 where指定检索条件：关系运算符、逻辑运算符、in关键词、是否空值、范围查询between and

#3.在emp表中查询所有的20号部门的员工信息
select * from emp where deptno=20;
#4.在emp表中查询工作大于2000的员工的姓名和工资
select ename, sal from emp where sal > 2000;
#5.在emp表中查询工作在1000到2000之间的员工信息（范围查询）
select * from emp where sal > 1000 and sal < 2000;
select * from emp where sal between 1000 and 2000;
#6.在emp表中查询员工编号为7788，7369，7521的员工信息
select * from emp where empno = 7788 or empno = 7369 or empno = 7521;
select * from emp where empno in (7788,7369,7521);
#9.在emp表中查询所有没有奖金的员工信息。（comm值为空）
select * from emp where comm is null;
select * from emp where comm is not null;

2.3 去重distinct 关键字

#7.在emp表中查询所有的职位信息（去重）
select distinct job from emp;

2.4 取别名

#8.在emp中查询工资提升5%后的员工姓名及工资（别名：字段、表达式、表、结果集等）
# 别名格式：[as] 别名
select ename,sal * 1.05 nsal from emp;
select emp.ename,emp.sal from emp;
select e.ename,e.sal from emp e;

2.5 排序 order by 和限制结果查询 limit

#10.在emp中查询工资最高的员工信息
#方法一：使用SQL提供的函数
#方法二：倒序排序取第一个
#排序：order by 排序的字段（默认升序，asc 升序，desc 降序）
#先按照工资降序排序，如果工资一样，则按照编号降序排序
select * from emp order by sal desc, empno desc;
#限制结果查询（格式：limit 开始索引, 长度，其中索引从0开始，如果是从0开始，则索引可以不
#写，直接写 limit 5，表示去前5条），限制结果查询功能仅适用于mysql。
select * from emp order by sal desc limit 1;

2.6 模糊查询

关键字和通配符：

• 模糊查询：like
• %：代表0到多个字符
• _：代表1位字符

示例代码：

#11.查询名称中包含s的员工信息（模糊查询：like %：代表0到多个字符 _：代表1位字符）
select * from emp where ename like '%s%';
select * from emp where ename like 's%';
select * from emp where ename like '%s';
#查询名称第3个字符是L的员工信息
select * from emp where ename like '__L%';

二、高级查询

1.关联查询（也称为连接查询）

1.1 定义：查询多张表或结果集

1.2 分类：内连接和外连接

1.3 内连接

语法格式：

#在emp查询所有的员工及其所在部门信息
#语法一：
#where的条件相当于从笛卡尔积中进行检索
select * from emp,dept where emp.deptno = dept.deptno;
#语法二：inner join … on
select * from emp inner join dept on emp.deptno = dept.deptno;
#语法三：存在通用列，所以可以这样写
select * from emp inner join dept  using(deptno);

特点总结：

• 多张关联表共有的记录才能出现在结果集中。
• 内连接的结果与连接顺序无关。

1.4 外连接

语法格式：

• 左外连接：left [outer] join … on …
• 右外连接：right [outer] join … on …

示例代码：

#查询所有的部门及其部门下的员工信息（内连接就解决不了了）
select * from dept left join emp on emp.deptno = dept.deptno;
select * from dept left join emp on dept.deptno = emp.deptno;

#自然连接（自然连接都是等值连接，等值连接不一定是自然连接）
select * from emp natural join dept;

#查询20号部门所有员工及其所在部门信息
select * from emp , dept where emp.deptno = dept.deptno and emp.deptno=20;
select * from (select * from emp where deptno = 20) e,dept where e.deptno=dept.deptno;

#查询所有员工及其上级领导（mgr）的名称（自连接）
select e1.ename '姓名', e2.ename '领导' from emp e1 left join emp e2 on e1.mgr=e2.empno;

特点总结：

• 外连接结果与连接顺序有关。
• 有主从表之分，依次遍历主表每条记录并与关联表进行匹配，若匹配成功则关联展示到结果集，若匹配失败则以null填充。

2.子查询

1.1 定义
将一个查询结果作为另一个查询的条件或组成继续进行检索。

2.2 分类

单行子查询：子查询返回的结果是一条记录

多行子查询：子查询返回的结果是多条记录

2.3 单行子查询

示例代码：

#查询编号为7788的员工所在部门的信息
#方法一：关联查询实现
select dept.* from (select * from emp where empno = 7788) e, dept where e.deptno=dept.deptno;
#方法二：子查询（或嵌套查询）实现
#查询7788员工的部门编号，将条件和结果放到一张表中（查找通用列）
select * from dept where deptno = (select deptno from emp where empno = 7788);

#查询工资>20号部门平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp where deptno = 20);
#查询工资>20号部门平均工资并且不在20号部门的员工信息
select * from emp where sal > (select avg(sal) from emp where deptno = 20) and deptno<>20;

2.4 多行子查询

2.4.1 关键词： any 与all

# 关键词：any / all：
# =any：相当于in >any：大于最小值 <any：小于最大值
# >all：大于最大值 <all：小于最小值

#查询工资大于2000的员工所在部门信息
select * from dept where deptno in (select distinct deptno from emp where sal > 2000);

#查询工资大于所在部门的平均工资的员工信息
#关联查询实现
select emp.* from emp , (select deptno , avg(sal) avg from emp group by deptno) e where 
emp.deptno = e.deptno and sal > e.avg;
#子查询实现
#步骤：
#1.主查询将deptno传给子查询
#2.子查询根据主查询的deptno查询指定部门的平均工资
#3.子查询将该部门平均工资返回给主查询
#4.主查询根据返回结果进行最后检索。
select * from emp e1 where sal > (
    select avg(sal) from emp e2 where e2.deptno = e1.deptno
);

2.4.2 关键词：exists

示例代码：

#查询工资>2000的员工所在的部门信息
#exists：
#1.将主查询的记录一次次交给子查询
#2.在子查询中匹配记录，如果能够找到匹配的记录，返回true，此时将展示主查询该条记录；如
#果不匹配则返回false，主查询该条记录不展示
select * from dept where exists (
    select * from emp where sal > 2000 and emp.deptno = dept.deptno
);

in和exsits的区别：

• in先执行子查询，exists先执行主查询
• in需要关注子查询返回值的字段信息；exists不需要关心返回字段
• exists将主查询的记录依次在子查询中匹配，如果匹配返回true展示。

2.5  总结

什么时候使用关联查询？什么时候使用子查询？

• 当查询结果存在多张表，使用关联查询；当查询结果存在于一张表，用子查询。
• 关联查询资源消耗高，子查询资源消耗低但可能存在多次查询。

3.联合查询：合并结果集

3.1 条件

多个结果集的字段必须保持一致，才能进行联合查询

3.2 关键词

union：联合结果集并去重

union all：联合结果集但不去重

3.3 为什么使用联合查询？

理论上可以用or关键词代替联合查询，但or关键词会导致索引失效故此使用联合查询。

3.4 示例代码

#查询20号部门以及工资>2000的员工信息
select * from emp where deptno = 20 or sal > 2000;
select * from emp where deptno = 20 union select * from emp where sal > 2000;
select * from emp where deptno = 20 union all select * from emp where sal > 2000;