#五条舔、流程控制函數(shù)
#1. if函數(shù)：if else 的函數(shù)
SELECT IF(10 > 5, '大', '小')
#2.case函數(shù)的使用一：switch case 的效果
java中
switch(變量或表達式){
? ? case 常量1：語句1跺嗽；break；
? ?...
? ? default:語句n; break
}
mysql中
case 要判斷的字段或表達式
when 常量1 then 要顯示的值1或者語句1;
when 常量2 then 要顯示的值2或者語句2;
...
else 要顯示的值n或語句n;
end
#案例：查詢員工的工資，要求
部門號=30钠右，顯示的工資為1.1倍
部門號=40，顯示的工資為1.2倍
部門號=50粪牲，顯示的工資為1.3倍
select salary 原始工資, department_id,
CASE department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end AS 新工資
FROM employees;
#3.case 函數(shù)的使用二：類似于多重if
java中
/*
if(條件1){
? ? 語句1;
}else if(條件2) {
? ? ?語句2;
}...
else{
? ? 語句n;
}
*/
mysql中：
case?
when 條件1 then 要顯示的值1或語句1
when 條件2 then 要顯示的值2或語句2
...
else 條件n then 要顯示的值n或語句n
#案例：查詢員工的工資情況
如果工資>20000,顯示A級別
如果工資>15000,顯示B級別
如果工資>10000,顯示C級別
否則斗塘，顯示D級別
select salary,
CASE
WHEN salary > 20000 then 'A'
WHEN salary > 15000 then 'B'
WHEN salary > 10000 then 'C'
ELSE 'D'
END AS 工資級別
FROM?employees
#二赢织、分組函數(shù)
/*
功能：用作統(tǒng)計使用，又稱為聚合函數(shù)或統(tǒng)計函數(shù)或組函數(shù)
分類：
sum 求和馍盟、avg 平均于置、max 最大值、min 最小值贞岭、count 計算個數(shù)
特點：
1八毯、sum、avg一般用于處理數(shù)值型
? ? max瞄桨、min话速、count 可以處理任何類型
2、以上分組函數(shù)都忽略null值
3芯侥、可以和distinct搭配實現(xiàn)去重的運算
4泊交、count函數(shù)的單獨介紹
? ? 一般使用count(*)用作統(tǒng)計行數(shù)
5、和分組函數(shù)一同查詢的字段要求是group by 后的字段
*/
#1柱查、簡單使用
select sum(salary) from employees
select count(salary) from employees
select sum(salary) 和 , count(salary) 個數(shù)?from employees
#2廓俭、參數(shù)支持哪類類型
#3、是否忽略null
select sum(commission_pct), avg(comission_pct), sum(commission_pct)/35, sum(commission_pct)/107 from employees
#4物赶、和distinct搭配
select sum(distinct salary), sum(salary ) from employees
#5白指、count函數(shù)的詳細介紹
select count(salary) from emplyoees
select count(*) from emplyoees ? //統(tǒng)計個數(shù)
select count(1) from emplyoees ? 有多少行就有多少個1
效率：
innodb存儲引擎下，count(*)和count(1)的效率差不多酵紫，比count(字段)要高一些
#6告嘲、和分組函數(shù)一同查詢的子彈有限制
select avg(salary), employee_id from employees;
#進階5：分組查詢
/*
select column, group_function(column)
from table
[where condition]
[group by group_by_expression]
[order by column] ?
select 分組函數(shù)，列（要求出現(xiàn)在group by的后面）
from 表
【where 篩選條件】
【group by 分組的列表】
【order by 子句】
注意：
? ? 查詢列表必須特殊奖地，要求是分組函數(shù)和group by后出現(xiàn)的字段
特點：
? ? 1橄唬、分組查詢中的篩選條件分類兩類
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 數(shù)據(jù)源 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?位置 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?關(guān)鍵字
? ? 分組前篩選 ? ? ? ? ? ? ? ?原始表 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?group by 字句的前面 ? ? ? ? ? ? ? ? ? ? ? ?where
? ? 分組后篩選 ? ? ? ? ? ? ? ?分組后的結(jié)果集 ? ? ? ? ? ? ? ?group by字句的后面 ? ? ? ? ? ? ? ? ? ? ? ?having
? ? 1.分組函數(shù)條件肯定是放在having子句中
? ? 2.能用分組前篩選的，就優(yōu)先考慮使用分組前篩選
2参歹、group by 子句支持單個字段分組仰楚，多個字段分組（多個字段之間用逗號隔開沒有順序要求），表表達式或函數(shù)（用得較少）
3、也可以添加排序（排序放在整個分組查詢的最后）
*/
#案例1：查詢每個工種的最高工資
select max(salary), job_id?
from employees
group by job_id
#案例2僧界、查詢每個位置上的部門個數(shù)
select count(*), location_id
from departments
group by location_id
#添加篩選條件
#案例1：查詢郵箱中包含a字符的侨嘀，每個部門的平均工資
select avg(salary), department_id
from employees
where email like '%a%'
group by department_id
#案例2、查詢有獎金的每個領(lǐng)導(dǎo)手下員工的最高工資
select max(salary), manager_id
from employees
where commission_pct is not null
group by manager_id
#添加復(fù)雜的篩選條件
#實例1：查詢哪個部門的員工個數(shù)>2
1.查詢每個部門的員工個數(shù)
select count(*), department_id
from employees
group by department_id
2.根據(jù)1的結(jié)果進行篩選捂襟，查詢哪個部門對賭員工個數(shù)>2
select count(*), department_id
from employees
group by department_id
having count(*)>2
#案例2：查詢每個工種有獎金的員工的最高工資>12000的工種編號和最高工資
#1.查詢每個工種有獎金的員工的最高工資
select max(salary), job_id
from employees
where?commission_pct is not null
group by job_id
2.根據(jù)1的結(jié)果繼續(xù)篩選咬腕，最高工資>12000
select max(salary), job_id
from employees
where?commission_pct is not null
group by job_id
having max(salary) > 12000
#案例3：查詢領(lǐng)導(dǎo)編號>102的每個領(lǐng)導(dǎo)手下的最低工資>5000的領(lǐng)導(dǎo)編號是哪個，以及其最低工資
1.?查詢每個領(lǐng)導(dǎo)手下的最低工資
select min(salary), manager_id
from employees
group bu manager_id
2.添加篩選條件：編號>102
select min(salary), manager_id
from employees
where manager_id > 102
group bu manager_id
3.添加篩選條件最低工資>5000
select min(salary), manager_id
from employees
where manager_id > 102
group bu manager_id
having min(salary) > 5000
#按表達式或函數(shù)分組
#案例：按員工姓名的長度分組葬荷，查詢每一組的員工個數(shù)涨共，篩選員工個數(shù)>5的有哪些
1.查詢每個長度的員工個數(shù)
select count(*) , length(last_name) len_name
from employees
group by length(last_name)r
2.添加篩選條件
select count(*) ?, length(last_name) len_name
from employees
group by length(last_name)
having count(*) > 5
別名，【不通用】
select count(*) ?c, length(last_name) len_name
from employees
group bylen_name
having c
#按多個字段分組
#案例：查詢每個部門每個工種的員工的平均工資
select avg(salary), department_id, job_id
from employees
group_by department_id, job_id
#添加排序
#案例：查詢每個部門每個工種的員工的平均工資宠漩，并且按平均工資的高低顯示
select avg(salary), department_id, job_id
from employees
where department_id is not null
group_by department_id, job_id
having avg(salary) > 10000
order by avg(salary) desc
別名
select avg(salary) a, department_id, job_id
from employees
where department_id is not null
group_by department_id, job_id
having a > 10000
order by a desc
案例：查詢各個管理者手下員工的最低工資举反，其中最低工資不能低于6000，沒有管理者的員工不計算在內(nèi)
select min(salary), manager_id
from employees
where manager_id is not null
group by manager_id
having min(ssalary) >= 6000
#進階6：連接查詢
含義：又稱多表查詢扒吁、當(dāng)查詢的字段來自于多個表時火鼻，就會用到連接查詢
笛卡爾乘積現(xiàn)象：表1 有m行，表2有n行瘦陈，結(jié)果=m*n行
發(fā)生原因：沒有有效的連接條件
如何避免：添加有效的連接條件
分類：
? ? ? ? 按年代分類：
? ? ? ? sql192標準：僅僅支持內(nèi)連接
? ? ? ? sql199標準【推薦】：支持內(nèi)連接+外連接（左外和右外）+交叉連接
? ? ? ? 按功能分類：
? ? ? ? ? ? 內(nèi)連接：
? ? ? ? ? ? ? ? 等值連接
? ? ? ? ? ? ? ? 非等值連接
? ? ? ? ? ? ? ? 自連接
? ? ? ? ? ? 外連接
? ? ? ? ? ? ? ? 左外連接
? ? ? ? ? ? ? ? 右外連接
? ? ? ? ? ? ? ? 全外連接
? ? ? ? ? ? 交叉連接
#一凝危、sql192標準
#1、等值連接
? ? -多表等值連接的結(jié)果為多表的角幾部分
? ? -n表連接晨逝，至少需要n-1個連接條件
? ? -多表的順序沒有要求
? ? -一般需要為表起別名
? ? -可以搭配簽名介紹的所有字句使用蛾默，比如排序、分組捉貌、篩選
#案例1：查詢女神名和對應(yīng)的男神名
select name, boyName form boys, beauty
where beauty.boyfriend_id = boys.id
#案例2：查詢員工名和對應(yīng)的部門名
select last_name, department_name
?from employees,departments
where employees.department_id = departments.department_id
#2支鸡、查詢員工名、工種號趁窃、工種名牧挣；為表起別名，提高語句的簡潔度醒陆，區(qū)分多個重名的字段
注意：如果為表起了別名瀑构，則查詢的字段就不能使用原來的表名去限定
select last_name, e.job_id, job_title
from employees AS e, jobs j
where e.job_id = j.job_id
#3、兩個表的順序是否可以調(diào)換
select last_name,?e.job_id, job_title
from ?jobs j,?employees AS e
where e.job_id = j.job_id
#4刨摩、可以加篩選寺晌？
#案例：查詢有獎金的員工名、部門名
select last_name, department_name,?commission_pct I
from employees e, departments d
where e.department_id = d.department_id AND e.commission_pct IS NOT NULL
#案例2：查詢城市名中第二個字符為o的部門名和城市名
select department_name, city
from departments d, locations l
where d.location_id = l.location_id?
AND city LIKE '_o%'
#5澡刹、可以加分組?
#案例1：查詢每個城市的部門個數(shù)
select count(*) 個數(shù) , city?
from departments d, locations l
where d.location_id = l.location_id
GROUP BY city?
#案例2：查詢有獎金的每個部門的部門名和部門的領(lǐng)導(dǎo)編號和該部門的最低工資
select department_name, d.manager_id, min(salary)
from departments d, employees e
where d.department = e.department_id
AND commisson_pch IS NOT NULL
GROUP BY department_name, d.manager_id
#6呻征、可以加排序
#案例：查詢每個工種的工種名和員工的個數(shù)，并且按員工個數(shù)降序
select job_title, count(*)
from employees e, jobs j
where e.job_id = j.job_id?
GROUP BY job_title
ORDER BY count(*) DESC
#7罢浇、可以實現(xiàn)三表連接陆赋？
#案例：查詢員工名沐祷、部門名和所在的城市
select last_name, department_name, city
from employees e, departments d, locations l
where e.department_id = d.department_id
AND d.location_id = l.location_id
AND city LIKE 'S%'
ORDER BY department_name DESC
#2、非等值連接
#案例1：查詢員工的工資和工資級別
select salary, grade_level
from employess e, job_grades j
where salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level = 'A'
#3攒岛、自連接
#案例：查詢員工名和上級的名稱
select e.employee_id, e.last_name, m.employee_id, m.last_name
from employees e, employees m
where e.manager_id = m.employee_id
#二赖临、sql99語法
/*
? ? 語法：select 查詢列表
? ? ? ? ? ? ? ? form 表1 別名 【連接類型】
? ? ? ? ? ? ? ? join 表2 別名 on 連接條件
? ? ? ? ? ? ? ? where 篩選條件
? ? ? ? ? ? ? ? 【group by 分組】
? ? ? ? ? ? ? ? 【having 篩選條件】
? ? ? ? ? ? ? ? 【order by 排序列表】
? ? 內(nèi)連接：inner
? ? 外連接
? ? ? ? 左外：left【outer】
? ? ? ? 右外：right ?【outer】
? ? ? ? 全外：full ?【outer】
? ? 交叉連接：cross
*/
一：內(nèi)連接
語法：
select 查詢列表
from 表1 別名
inner join 表2 別名
on 連接條件
分類：
等值
非等值
自連接
特點：
? ? -添加排序、分組阵子、篩選
? ? -inner 可以省略
? ? -篩選條件放在where后面思杯，連接條件放在on后面，提高分離性挠进，便于閱讀
? ? -inner join 連接和sql92語法中的等值連接效果是一樣的,都是查詢多表的交集
#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 employees e
inner join jobs j
ON e.job_id = j.job_id
where e.last_name LIKE '%e%'
案例3：查詢部門個數(shù)>3的城市名和部門個數(shù)（分組+篩選）
-查詢每個城市的部門個數(shù)
-在1結(jié)果上篩選
select city, count(*) 部門個數(shù)
from departments d
inner join locations l
ON d.location_id = l.location_id
GROUP BY city?
having count(*) > 3
案例4：查詢哪個部門的部門員工個數(shù)>3的部門名和員工個數(shù)领突，并按照個數(shù)降序（排序）
-查詢每個部門的員工個數(shù)
select count(*), department_name
from employees e
innet join departments d
ON e.department_id = d.department_id
GROUP BY department_name
-在1結(jié)果上篩選員工個數(shù)>3的記錄，并排序
select count(*), department_name
from employees e
innet join departments d
ON e.department_id = d.department_id
GROUP BY department_name
having count(*) > 3
ORDER BY count(*) DESC?
案例5：查詢員工名案怯、部門名君旦、工種名，并按照部門名降序(添加三表連接)
select last_name, department_name, job_title
from employees e
inner join departments d ON e.department_id = d.department_id
innert join j ON e,job_id = j.job_id
ORDER BY department_name DESC
二：非等值連接
#查詢員工的工資級別
select salary, grade_level
from employees e
inner join job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
#查詢工資級別的個數(shù)>2的個數(shù)嘲碱，并且按工資級別降序
select count(*), grade_level
from employees e
inner join job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
having count(*) > 20
ORDER BY grade_level DESC
三：自連接
#查詢員工的名字金砍，上級的名字
select e.last_name, m.last_name
from employees e
inner join employees m
on e.manager_id = m.employee_id
where e.last_name LIKE '%k%'
二、外連接
/*
應(yīng)用場景：用于查詢一個表中有麦锯，另一個表沒有的記錄
特點：
1恕稠、外連接的查詢結(jié)果為主表中的所有記錄
? ? 如果從表中有和他匹配的，則顯示匹配的值
? ? 如果從表中沒有和他匹配的扶欣，則顯示null
? ? 外連接查詢結(jié)果=內(nèi)連接結(jié)果+主表中有而從表沒有的記錄
2.左外連接鹅巍， left join 左邊的是主表
? ? 右外鏈接，right join 右邊的是主表
3.左外和右外交換兩個表的順序料祠，可以實現(xiàn)同樣的結(jié)果
4.全外連接=內(nèi)連接的結(jié)果+表1中有但表2沒有的+表2中有單表1沒有的
*/
#案例1：查詢哪個部門沒有員工
#左外
select d.*, e.employee_id
from departments d
left outer join employees e
ON d.department_id = e.department_id
where e.employee_id IS NULL
#右外
select d.*, e.employee_id
from employees?e
left outer join department d
ON d.department_id = e.department_id
where e.employee_id IS NULL
#全外
select b.*, bo.*
from beauty b
full outer join boys bo
ON b.boyfiiend_id IS NULL
#交叉連接
select b.*, bo.*
from beauty b
CROSS JOIN boys bo
#sql92 和 sql99
功能：sql99支持的較多
可讀性：sql99
#案例一骆捧、查詢編號>3的女神的男朋友信息，如果有則列出詳細髓绽，如果沒有敛苇，用null填充
select b.id, b.name, bo.*
from beauty b
left outer join boys bo
ON b.boyfriend_id ?= bo.id
where b.id > 3
#案例二、查詢哪個城市沒有部門
select city, d.*
from departments d
right outer join locations l
ON d.location_id = l.location_id
where d.department_id is null
#三顺呕、查詢部門名為SAL或IT的員工信息
select e.*, d.department_name
from departments d
left join employees e
ON d.department_id = e.department_id
where d.department_name IN ('SAL', 'IT')
