SQL第2/n篇（持续更新中）DQL高阶查询语句

进阶5 分组查询

1、语法：
select 分组函数，列（要求出现在group by后面）
from 表
where 条件
group by 分组列表
order by 排序列表

2、执行过程：
from 表 where条件 group by分组 having筛选 select查询 order by 排序

3、要求：查询列表是分组函数或者出现在group by之后
4、特点：
  1、分组筛选分为两类
                    数据源         位置及关键字
  分组前筛选       原始表中有      where 放在group by前面
  分组后筛选       需要先查一下    hanving  放在group by 后面
  
  ①分组函数做条件放在having中，因为分组函数是不在原始表中的
  ②都可以筛选时优先使用原始表里的where筛选
  
  2、多字段分组，逗号隔开，顺序无影响

查询每个部门的平均工资

SELECT department_id 部门,AVG(salary) 平均工资
FROM employees
GROUP BY department_id;
分组前的查询(在表中可以找到筛选字段，如：email）

查询邮箱中有a字母的每个部门的员工数

SELECT COUNT(*) ,department_id 
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
分组后查询（在表中找不到要筛选的字段）

在每个员工数>2的部门查询员工奖金

#①每个部门的员工数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
#②对①的结果进行筛选，筛选员工数>2
SELECT commission_pct,COUNT(*) AS 员工人数,department_id
FROM employees
GROUP BY department_id
HAVING 员工人数>2;

查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary) AS 最高工资
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id 
HAVING 最高工资>12000;

查询领导编号>102的每个领导手下的最低工资>5000的领导编号，及最低工资

SELECT manager_id,MIN(salary) 最低工资
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING 最低工资>5000;
按表达式或者函数分组

按员工姓名的长度分组，查询每组的员工个数，筛选员工个数>5

SELECT COUNT(*) AS 员工个数,LENGTH(last_name) AS 姓名长度
FROM employees
GROUP BY 姓名长度
HAVING 员工个数>5;
#不同数据库where 不支持别名查询
按多个字段进行分组

查询不同工种不同部门的平均工资

SELECT AVG(salary),job_id,department_id
FROM employees
GROUP BY job_id,department_id;
#前后循序没有影响

查询不同工种不同部门的平均工资，降序排列

SELECT AVG(salary) 平均工资,job_id,department_id
FROM employees
GROUP BY job_id,department_id
ORDER BY 平均工资 DESC;

进阶六：连接查询

1、分类

按年代分类
sql92：仅支持内连接
sql99【推荐】：支持内连接+外连接（左外，右外）+交叉连接

按功能分类
(1)内连接：
等值连接,非等值连接,自连接
(2)外连接：
左外连接,右外连接,全外连接
(3) 交叉连接：

一、sql92标准：直只支持内连接

①、多表等值连接的结果为多表的交集
②、n个表连接至少需要n-1个连接条件
③、多表的顺序没有要求
④、一般需要为表起别名，用了别名就不能再用原来的表名了
⑤、可以搭配前面所有的子句使用：where、group by 、(两者的后面一般不用别名)order by（可以使用别名）

查询女神和对应的男神的名字

SELECT NAME,boyName FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;
#其实他还是笛卡尔积的形式去匹配，只不过有where条件进行了筛选

查询员工名和对应的部门名

SELECT last_name,department_name FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
为表起别名，如果用了别名，在限定时就不能再用原来的表名了

查询员工名，工种号、工种名

SELECT last_name,a.job_id,job_title
FROM employees a,jobs b
WHERE a.`job_id`=b.`job_id`;
#当要查的字段在两个表中都存在时，要通过表名进行限定
加筛选条件 where 等值连接后用 and 关键词连接

查询城市中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';
加分组条件

查询有奖金的每个部门的部门名和领导编号以及最低工资

SELECT department_name,e.manager_id,MIN(salary)
FROM departments d ,employees e
WHERE d.`department_id`=e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY department_name,e.manager_id;

有了group by就要时刻关注查询的后面的字段除了聚合函数之外有没有都写上,不写在group by后面默认的是查询的字段一一对应，最好都写上，尽管分组也用不到

ps:

最后根据e.namage_id 和 根据 d.manager_id分组的结果是不一样的
根据employees表的领导分，会出现Sal部门里的多个领导
根据departments表的领导分，只会出现一个领导
employees里有departments不存在的领导编号【好像发现了什么，哈哈】

实现三表连接 (表的顺序无所谓）

查询员工名、、部门名、城市

SELECT last_name,department_name,city
FROM departments d ,locations l,employees e 
WHERE d.`location_id`=l.`location_id`
AND e.`department_id`=d.`department_id`;
非等值连接 （表的顺序没有关系，之后也可正常加其他子句）
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  INT,
 highest_sal INT);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

查询员工的工资和工资级别

SELECT salary,grade_level
FROM employees e ,job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;
自连接

查询员工名和他的领导的名字
首先，这些字段都在一张表里
其次，我根据last_name找到manager_id,然后根据manager_id去找emploee_id(emploee_id实际就是要找的manager_id）

select e.last_name,e.employee_id,m.employee_id,m.last_name
from employees e ,employees m
where e.manager_id=m.`employee_id`;

sql99

1.语法：
select 字段列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where】
【group by】
【order by】
2.连接类型：
内连接：inner
    等值连接
    非等值连接
    自连接
外连接：
    左外：left 【outer】
    右外：right【outer】
    全外：full 【outer】
交叉连接：cross
一、内连接 inner join （inner可以省略）
1、等值连接

案例1：查询员工名、部门名

select last_name,department_name
from employees e
inner join departments d on e.`department_id`=d.`department_id`;

案例2：查询包含e的员工名和工种名（筛选）

select last_name,job_title
from jobs j
inner join employees e
on j.`job_id`=e.`job_id`
where last_name like '%e%';

案例3：查询每个城市部门个数>3的城市名和部门个数(分组+筛选）

select city,count(*) 部门个数
from locations l
inner join departments d
on l.`location_id`=d.`location_id`
group by l.`city`
having 部门个数>3;

案例4：查询每个部门的员工个数>3的员工个数和部门名，并按个数降序排列

select count(*) 员工个数,department_name
from employees e
inner join departments d
on d.`department_id`=e.`department_id`
group by d.department_name
having count(*)>3
order by 员工个数 desc;

案例5：查询员工名、工种名、部门名，并按部门名降序(三表连接）

select last_name,job_title,department_name
from employees e 
inner join jobs j on e.`job_id`=j.`job_id`
inner join departments d on d.`department_id`=e.`department_id`
order by department_name desc;
非等值连接

查询员工的工资级别

select salary ,`grade_level`
from employees e
join `job_grades` jg
on e.`salary` between jg.`lowest_sal` and jg.`highest_sal`;

查询每个工资级别的个数>20，降序排列

SELECT 
    #salary ,#写上salary也可以显示，但是不建议写，因为不是group by里的字段
    `grade_level`,count(*)
FROM employees e
JOIN `job_grades` jg
ON e.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`
group by jg.`grade_level`
having COUNT(*)>20
order by count(*) desc;
自连接

查询员工的名字、领导的名字

select e.last_name,m.last_name
from employees e
join employees m on e.`manager_id`=m.`employee_id`;

二、外连接

内连接干的都是些查询交集的事情，对于不是交集的事情就干不了了
外连接查的是：在一个表中有，另一个表没有的数据，没有的数据用null填充
怎么理解呢？就是两个表先join吧，
此时内连接只选取两个表都有数据的部分
外连接根据主表选数据，不管另一个表的数据是否完整
也就是说，会存在主表的字段有值，从表的字段是null填充的情况

特点：
1、外连接有主从表之分，主表会全部显示。
    对于匹配不上的用null值填充
    对于匹配上的正常显示
2、左外连接左边是主表，右外右边的是主表
3、通过交换两个表的顺序可以实现左外和右外的转换
4、全连接=内连接+表1有但表2没有（用null填充）+表2有表1没有（用null填充）

查询男朋友不在男神表的女神的名字

SELECT g.`name`, b.*
FROM beauty g
LEFT JOIN boys b ON g.`boyfriend_id`=b.`id`#看一下数据

select *
from beauty g
left join boys b on g.`boyfriend_id`=b.`id`
where b.`id` is null;

案例：查询没有员工的部门

#左外
select d.department_id,department_name
from departments d
left join employees e
on d.`department_id`=e.`department_id`
where e.`department_id` is null;

#右外
SELECT d.department_id,department_name
FROM employees e
right JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL;
3、全连接 （不支持全连接查询）只是演示一下

表1与表2的内连接+表1在表2中匹配不到用null填充的部分+表2在表1中匹配不到用null填充的部分

use girls；
select b.*,bo.*
from beauty b
full join boys bo
on b.`boyfriend_id`=bo.`id`
4、交叉连接 (笛卡尔乘积） 不需要on条件
SELECT b.*,bo.*
FROM beauty b
cross JOIN boys bo

—————————做题线——————————
1、查询编号>3的女神的男朋友的信息，如有信息详细列出，没有信息用null填充

select b.id,b.name ,bo.*
from beauty b
left join boys bo
on b.boyfriend_id=bo.id
where b.id>3;

2、查询哪个城市没有部门

select city 
from locations l
left join departments d
on l.`location_id`=d.`location_id`
where d.`department_id` is null;

3、查询部门名为SAL 或者IT的员工的信息
部门表为主表，为什么呢？因为有部门的不一定有员工

select department_name,e.*
from departments d
left join employees e
on e.`department_id`=d.`department_id`
where d.`department_name` ='SAL'
OR D.`department_name`='IT';

in的用法

select department_name,e.*
from departments d
left join employees e
on e.`department_id`=d.`department_id`
where d.`department_name` in('SAL','IT');

看一下连接的各种状况

image.png
image.png

说明一下这个beauty和boys表
beauty.png
boys.png
表里的信息不一定对，仅供操作实验。
参考出处真心讲的不错，推荐！！！这是跟课手敲的笔记，follow 我也是可以的！

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,530评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 86,403评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,120评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,770评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,758评论 5赞 367
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,649评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,021评论 3赞 398
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,675评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,931评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,659评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,751评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,410评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,004评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,969评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,203评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,042评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,493评论 2赞 343