MySQL篇(三)--DQL数据查询语言_查询小乔的籍贯用mysql查询语句-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_21369769/article/details/110083873

本文详细讲解了MySQL中DQL的基本操作，包括精确、模糊、范围查询，去重、统计、分组、过滤、排序和限制，以及复杂的连接查询和子查询。通过实例演示如何进行数据筛选、汇总和部门分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql操作语句之数据查询语言DQL example:对数据进行查询。
查询顺序 where --- group by --- having --- order by

简单查询

select * from employee;#不推荐使用
select empno,ename,job,deptnu from employee;

精确条件查询

select * from employee where ename = '小乔';
select * from employee where sal = 8000;
select * from employee where sal != 8000; #不推荐
select * from employee where sal <> 8000; #不推荐

模糊查询

show variables like 'character%';

范围查询

select * from employee where sal between 8000 and 14000;

离散查询

select * from employee where sal in (8000,14000);

去重查询

select distinct(job) from employee;

统计查询(聚合函数)

#统计数量
select count(*) from employee;
#计算总和
select sum(sal) from employee;
#计算最大值
select max(sal) from employee;
#计算最小值
select min(sal) from employee;
#计算平均值
select avg(sal) from employee;
#concat()函数
select concat(ename, '-' , job) from employee;

分组查询group by

select deptnu, count(*) as total from employee group by deptnu; 
select deptnu,job, count(*) as total from employee group by deptnu,job;

having条件查询

作用: 对查询结果进行筛选操作
语法： having 条件 / having 聚合函数 条件
适用场合： 一般跟在 group by 之后
select deptnu,job, count(*) as total from employee group by deptnu,job having total > 2;

排序查询order by

select deptnu,job,count(*) as total from employee group by deptnu,job having total >= 2 order by deptnu desc;

限制查询limit

作用： 限制查询结果的条数
语法： limit m,n m：代表起始条数值，不写默认为0；n：代表取出条数
select * from employee limit 3,6;

exists子查询

用法：exists型子查询后面是一个受限制的select查询语句，如果exists后的内层查询能查出数据，则返回TRUE,否则返回FALSE
select * from 表名1 a where exists (select 1 from 表名2 b where 条件);
#example: 查出公司有员工的部门详细信息
select * from dept a where exists(select * from employee b where a.deptnu = b.deptnu);

左右连接查询(外连接)

左连接： 左表记录会全部展示出来，右表只显示符合搜索条件的记录，右表记录不足的地方均为NULL
left join 表名 on 条件
右连接： 右表记录会全部展示出来，左表只显示符合搜索条件的记录，左表记录不足的地方均为NULL
右连接： right join 表名 on 条件
# example 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select a.dname,b.* from dept a left join employee b on a.deptnu=b.deptnu;
select b.dname,a.* from employee a right join dept b on a.deptnu=b.deptnu;

内连接查询

语法： inner join 表名 on 条件
# example 列出员工小乔所在部门的地址
select a.addr from dept a inner join employee b on a.deptnu=b.deptnu and b.ename = '小乔';#不推荐
select a.addr from dept a,employee b where a.deptnu =b.deptnu and b.ename = '小乔';#常用

联合查询

作用：把多个查询结果结合在一起
语法： ... union ...  (去除重复结果)
	  ... union all ... (不去除重复结果)
注意事项：
1)两个 select 语句的查询结果的字段数必须一致;
2)通常也该让两个查询语句的字段类型具有一致性;
3)可以联合更多的查询结果;
4)用到 order by 时需加上limit限制，子句用括号括起来
# example 对销售员工资从低到高排序，对文员工资从高到低排序
(select * from employee a where a.job = '销售员' order by a.sal limit 50) union (select * from employee b where b.job = '文员' order by b.sal desc limit 50);

exercise

#1.查出至少有一个员工的部门，显示部门编号，部门名称，部门位置，部门人数。
select a.deptnu,a.dname,a.addr,b.total from dept a, (select deptnu,count(*) as total from employee group by deptnu) b where a.deptnu=b.deptnu;
#2.列出薪资比安琪拉高的所有员工信息。
select * from employee where sal > (select sal from employee where ename = '安琪拉');
#3.列出所有员工的姓名及其直接上级的姓名。
select a.ename,b.ename from employee a left join employee b on a.mgr=b.empno;
#4.列出受雇日期早于直接上级的所有员工的编码，姓名，部门名称。
select a.empno,a.ename,c.dname from employee a left join employee b on a.mgr=b.empno left join dept c on a.deptnu=c.deptnu where a.hiredate < b.hiredate;
#5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select a.dname,b.* from dept a left join employee b on a.deptnu = b.deptnu;
#6.列出所有文员的姓名及其部门的名称，所在部门的总人数。
select a.ename,b.dname,a.job, c.total from employee a, dept b, (select deptnu, count(*) as total from employee group by deptnu) c where a.deptnu=b.deptnu and a.job = '文员' and b.deptnu = c.deptnu;
#7.列出最低薪资大于15000的各种工作及从事此工作的员工人数。
select job,count(*) from employee group by job having min(sal) > 15000;
#8.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号
select ename from employee where deptnu=(select deptnu from dept where dname='销售部');
#9.列出与诸葛亮从事相同工作的所有员工及部门名称。
select a.*,b.dname from employee a left join dept b on a.deptnu=b.deptnu where a.job=(select job from employee where ename = '诸葛亮');
#10.列出薪资比在部门30工作的员工的薪资还高的员工的姓名，薪资和部门名称。
select a.ename,a.sal,b.dname from employee a ,dept b where a.deptnu=b.deptnu and a.sal > (select max(sal) from employee where deptnu = 30);
#11.列出每个部门的员工数量，平均工资。
select deptnu,count(*) as total, avg(sal) from employee group by deptnu;
#12.列出薪资高于公司平均薪资的所有员工信息，所在部门名称，上级领导，工资等级。
select a.*,b.ename,c.dname,d.grade from employee a, employee b, dept c, salgrade d where
a.mgr=b.empno and a.deptnu=c.deptnu and a.sal > (select avg(sal) from employee) and a.sal between d.lowsal and d.higsal;

#初始化脚本
/*创建部门表*/ 
CREATE TABLE dept( 
	deptnu INT PRIMARY KEY comment '部门编号', 
	dname VARCHAR(50) comment '部门名称', 
	addr VARCHAR(50) comment '部门地址' 
)ENGINE=innodb DEFAULT CHARSET=utf8mb4;

/*创建员工表*/
CREATE TABLE employee( 
	empno INT PRIMARY KEY comment '雇员编号', 
	ename VARCHAR(50) comment '雇员姓名', 
	job VARCHAR(50) comment '雇员职位', 
	mgr INT comment '雇员上级编号', 
	hiredate DATE comment '雇佣日期', 
	sal DECIMAL(7,2) comment '薪资', 
	deptnu INT comment '部门编号' 
)ENGINE=innodb DEFAULT CHARSET=utf8mb4; 

/*创建工资等级表*/ 
CREATE TABLE salgrade(
	grade INT PRIMARY KEY comment '等级', 
	lowsal INT comment '最低薪资', 
	higsal INT comment '最高薪资' 
)ENGINE=innodb DEFAULT CHARSET=utf8mb4;

/*插入dept表数据*/ 
INSERT INTO dept VALUES (10, '研发部', '北京'); 
INSERT INTO dept VALUES (20, '工程部', '上海'); 
INSERT INTO dept VALUES (30, '销售部', '广州'); 
INSERT INTO dept VALUES (40, '财务部', '深圳'); 

/*插入emp表数据*/ 
INSERT INTO employee VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000, 10); 
INSERT INTO employee VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20); 
INSERT INTO employee VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30); 
INSERT INTO employee VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10); 
INSERT INTO employee VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20); 
INSERT INTO employee VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20); 
INSERT INTO employee VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30); 
INSERT INTO employee VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30); 
INSERT INTO employee VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30); 
INSERT INTO employee VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30); 
INSERT INTO employee VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500, 30); 
INSERT INTO employee VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10); 
INSERT INTO employee VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20); 
INSERT INTO employee VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20); 

/*插入salgrade表数据*/ 
INSERT INTO salgrade VALUES (1, 7000, 12000); 
INSERT INTO salgrade VALUES (2, 12010, 14000); 
INSERT INTO salgrade VALUES (3, 14010, 20000); 
INSERT INTO salgrade VALUES (4, 20010, 30000); 
INSERT INTO salgrade VALUES (5, 30010, 99990);