MySQL常用数据查询语言

本文介绍MySQL经常用到的数据查询语言。数据查询语言(DQL)：用于查询数据库表中的记录，是业务分析人员最常用的语言类型，贯穿信息获取、数据获取、数据理解、数据清洗等多个环节，其主体结构为select <字段名> from <表或视图名> where <查询条件>。

数据查询语言

一、单表查询

1、基础查询语句

（1）打印常量值

-- 单独使用select，打印一行1
select 1;

（2）公式使用

-- 单独使用select，打印一行2
select 1+1;

（3）表达式使用

-- 1+1=3不成立，返回0
select 1+1=3;
-- 1+1=2成立，返回1
select 1+1=2;

（4）打印字段

-- 查询empty表中员工及其工资
select empno,ename,sal from emp;
-- 查询emp表的所有字段
select * from emp;

（5）去重

-- 单字段去重，查询emp表中的部门
select distinct deptno from emp;
-- 多字段去重，查询emp表中不同的部门有哪些岗位
select distinct deptno,job from emp;

（6）设置别名

-- 查询每位员工调整后的薪资(基本工资+1000) 
-- 使用as设置别名
select *,sal+1000 as 调薪 from emp;

2、条件查询

（1）单一条件查询

-- emp表中的领导岗位员工
select * from emp where job='manager';

（2）多条件查询

-- 查询10号部门和20号部门中sal低于2000的员工信息
select * from emp where deptno in (10,20) and sal<2000;

（3）连续范围查询

-- 查询基本工资大于或等于2000且小于或等于3000的员工信息
select * from emp where sal>=2000 and sal<=3000; 
-- 写法等价，返回相同的结果
select * from emp where sal between 2000 and 3000;

（4）空值查询

-- 找出emp 表中的最高领导人(没有更高的上级员工) 
select * from emp where mgr is null;

（5）模糊查询

-- 查询姓名不以a开头的员工信息
select * from emp where ename not like 'a%';
-- 查询姓名中包含a的员工信息
select * from emp where ename like '%a%';
-- 查询姓名中第二个字符为a的员工信息
select * from emp where ename like '_a%';

3、查询结果排序

-- 查询部门10的员工信息并按sal降序显示
select * from emp where deptno = 10 order by sal desc;
-- 查询所有员工信息并按deptno升序、sal降序显示 
select * from emp order by deptno,sal desc；

4、限制数量

-- 查询基本工资最高的前5位员工
select * from emp order by sal desc limit 5;
-- 查询基本工资从高到低顺序第6行~第10行的员工
select * from emp order by sal desc limit 5,5;

5、聚合函数

-- 查询 emp 表中员工的最高工资、最低工资、平均工资及工资总和
select max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 工资总和 from emp;
-- 返回emp 表的员工总数
select count(*) 员工总数 from emp; 
-- 本质也是统计全表的记录数，与*效果一致 
select count(1) 员工总数 from emp;
-- 返回 emp 表的部门总数
select count(distinct deptno) 部门总数 from emp; 
-- 统计有奖金的员工人数	
-- 忽略了 null，但是计算了0
select count(comm) from emp;
-- 从业务逻辑上来讲，奖金金额为0的员工不应该视为有奖金的员工
select count(comm) from emp where comm!=0;

6、分组查询

组内聚合：

-- 查询公司内部不同奖金档位的获得人数
select comm,count(*) from emp group by comm;
-- 查询各部门不同职位的平均工资
select deptno,job,avg(sal) as 平均工资 from emp group by deptno,job;

分组后筛选：

-- 查询各部门 clerk 的平均工资 - 用 having 子句筛选
select deptno, job,avg(sal) 平均工资 from emp group by deptno, job having job='clerk';
-- 用 where 子句筛选
select deptno,job,avg(sal) 平均工资 from emp where job='clerk' group by deptno,job;
-- 查询平均工资大于2000 的部门
select deptno,avg(sal) 平均工资from emp group by deptno having avg(sal)>2000;

二、多表查询

1、纵向合并查询

纵向合并并不需要指明连接条件，而是单纯地把多个结果集进行纵向追加，因此被合并的多个结果集的字段数量、顺序必须完全一致，而其数据类型必须可以兼容。

-- union 去重
select * from t1 union
select * from t2;
-- union all，其中(a，12)出现了2次 ，不去重
select * from t1 union all
select * from t2;

2、横向连接查询

（1）内连接

内连接会按照连接条件，返回两个表中满足条件的记录，本质上是对两个表中记录取交集。
查询manager的姓名、所属部门名称和入职日期：

select ename,dname,job,hiredate from emp
inner join dept on emp.deptno=dept.deptno
where job='manager';

（2）左外连接

左外连接会按照连接条件，返回两个表中满足条件的记录，包括左表中的所有记录，右表匹配不到则显示null。左外连接左表作为主表。
查询每位员工的ename,dname,sal:

select ename,dname,sal from emp
left join dept on emp.deptno=dept.deptno;

（3）右外连接

右外连接会按照连接条件，返回两个表中满足条件的记录，包括右表中的所有记录，左表匹配不到则显示null。右外连接右表作为主表。
查询各地区的员工数量：

select loc,count(empno) 员工数 from emp
right join dept on emp.deptno=dept.deptno
group by loc;

（4）全外连接

全外连接会返回两个表中全部的记录，本质上是对两个表中的记录取并集。匹配不到null填充。

select * from t1 left join t2 on t1.公共字段1=t2.公共字段2
union
select * from t1 right join t2 on t1.公共字段1=t2.公共字段2;

（5）自连接查询

-- 查询所有员工的姓名及指数领导的姓名
select 员工表.ename 员工姓名,领导表.ename 领导姓名 from emp 员工表 left join emp 领导表 on 员工表.mgr=领导表.empno;

（6）多表连接

-- 查询入职日期早于指数领导的员工的姓名及所属部门
select 员工表.empno,员工表.ename,dname from emp 员工表
left join emp 领导表 on 员工表.mgr=领导表.empno
left join dept on 员工表.deptno=dept.deptno
where 员工表.hiredate<领导表.hiredate;

（7）交叉连接

M*N条记录。

select * from t1,t2;
select * from t1 cross join t2;

三、子查询

在日常业务中，分析人员要想获得最终分析所需的数据，需要对企业数据库中的数据进行多次处理，因此经常会遇到 单次查询无法找出某些需要的数据或者查询逻辑异常复杂的情况 。此时我们可以将单次完整查询所返回的虚拟结果集视为一个临时表，并对这个临时表进行再次的查询和处理，这就是子查询的作用。子查询又称为嵌套查询，指在一个查询语句中包含另一个或多个完整的查询语句。

1、子查询的语法规则

(1)子查询必须用圆括号括起来。
(2)子查询最多可以嵌套到255层(个别查询可能会不支持255层的嵌套)。
(3)执行顺序由内到外，先执行内部的子查询，再执行外部的主查询。
(4)若子查询返回的结果集在主查询中作为一个表，则必须添加表别名;若需要引用表子查询中的计算字段，则必须添加列别名才可以引用。

2、子查询分类

按照子查询返回的结果不同，子查询可分为 标量子查询 (返回的结果是一个单行单列的数据)、 行子查询 (返回的结果是一条有多个字段的记录)、 列子查询 (返回的结果是包含多条记录的单个字段)及 表子查询 (返回的结果是一个拥有多个字段和多条记录的临时表)。
子查询根据需求的不同，可以搭配不同子句使用，如果需要将子查询返回的结果作为主查询的计算字段，那么可以将子查询放在select子句中；如果需要将子查询返回的结果作为主查询的筛选条件，那么可以将子查询放在where或having子句中；如果需要将子查询返回的结果作为主查询的一个表，那么可以将子查询放在 from 或 join 子句中。

3、select子查询

查询各部门员工人数占比：

-- 第1步，查询整体员工数量
select count(*) from emp;
-- 第2步，将整体员工数量作为子查询来查询各部门员工人数占比
select deptno,count(*) 员工人数,count(*)/(select count(*) from emp) 员工人数占比 from emp group by deptno;

4、from/join子查询

查询各部门最高工资的员工：

-- 第1步，查询各部门最高工资
select deptno,max(sal) from emp group by deptno;
-- 第2步，找出所有部门内员工工资等于最高工资的员工
select empno,ename,sal,emp.deptno from emp left join (select deptno,max(sal) as 最高工资 from emp group by deptno) as t
on emp.deptno = t.deptno
where sal = 最高工资;

5、where或having子查询

（1）比较操作字符

查询基本工资高于公司平均工资的员工信息：

-- 第1步，查询公司所有员工的平均工资
select avg(sal) from emp;
-- 第2步，使用比较操作字符将员工工资与子查询返回的平均工资进行比较
select * from emp where sal>(select avg(sal) from emp);

查询和smith同部门、同职位的员工：

-- 第1步，查询smith的部门和职位
select deptno,job from emp where ename='smith';
-- 第2步，使用比较操作字符进行子查询
select empno,ename,deptno from emp
where (deptno,job) = (select deptno,job from emp where ename='smith') and ename<>'smith';

（2）in关键字

-- 查询各部门最高工资的员工
select empno,empname,sal,deptno from emp where (depno,sal) in (select depno,max(sal) from emp group by deptno);

查询普通员工的工资等级：

-- 第1步，查询那些人不是普通员工，即领导的工号
select distinct mgr from emp where mgr is not null;
-- 第2步，将领导的工号作为子查询来筛选数据
select empno,ename,sal from emp where empno not in (select distinct mgr from emp where mgr is not null);

（3）exists关键字

select* from emp where exists (select * from dept where deptno=20) and deptno =20; 

需要强调的是，exists后接的子查询并不对数据进行筛选，只决定外查询执不执行。

（4）any/some关键字

查询基本工资高于30号部门员工的员工信息：

-- 第1步，查询30号部门所有员工的基本工资
select sal from emp where deptno=30;
-- 第2步，将30号部门所有员工的基本工资作为资产寻对记录进行筛选
select * from emp where sal>any(select sal from emp where deptno=30) and deptno<>30;

（5）all关键字

查询基本工资高于30号部门所有员工的员工信息：

-- 第1步，查询30号部门所有员工的基本工资
select sal from emp where deptno=30;
-- 第2步，将30号部门所有员工的基本工资作为资产寻对记录进行筛选
select * from emp where sal>all(select sal from emp where deptno=30) and deptno<>30;

赏

谢谢你请我吃糖果

微信