進階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');

看一下連接配接的各種狀況

SQL第2篇 DQL高階查詢語句進階5 分組查詢進階六：連接配接查詢

image.png

SQL第2篇 DQL高階查詢語句進階5 分組查詢進階六：連接配接查詢

image.png

說明一下這個beauty和boys表

SQL第2篇 DQL高階查詢語句進階5 分組查詢進階六：連接配接查詢

beauty.png

SQL第2篇 DQL高階查詢語句進階5 分組查詢進階六：連接配接查詢

boys.png

表裡的資訊不一定對，僅供操作實驗。

作者：粉紅狐狸_dhf

連結：https://www.jianshu.com/p/c06b4195202c

來源：簡書

著作權歸作者所有。商業轉載請聯系作者獲得授權，非商業轉載請注明出處。

SQL第2篇 DQL高階查詢語句進階5 分組查詢進階六：連接配接查詢

進階5 分組查詢

進階六：連接配接查詢

繼續閱讀

oracle中的start with connect by用法

JDBC連接配接資料庫（statement）

MyBatis-Plus 之AR模式

ASP程式設計經典例子

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離