一常見函式（單行函式）

二分組函式

三分組查詢

四多表連接查詢

1.SQL99標準

1.1 內連接

1.2外連接

1.3交叉連接

2.子查詢

3 分頁查詢

4 聯合查詢

接著上一期的資料庫繼續講，上回我們說到簡單的排序查詢，接下來我們繼續進階，分組查詢，多表連接查詢，這篇博客基本都是知識點學起來比較枯燥，不過沒辦法為了掙錢，必須堅持下去，在學習分組查詢之前不得不提到函式這個概念，因為分組查詢常常是跟著分組函式聯系起來的，

一常見函式（單行函式）

在常見函式里，我把它分為字符函式，數學函式，日期函式，流程控制函式，其他函式，知識點挺多的，全是干貨，大家一定要仔細學習，

字符函式：

concat: 拼接
substr: 截取子串
upper: 轉換成大寫
lower: 轉換成小寫
trim: 去前后指定的空格和字符
ltrim: 去左邊空格
rtrim: 去右邊空格
replace: 替換
lpad: 左填充
rpad: 右填充
instr: 回傳子串第一次出現的索引
length: 獲取位元組個數

1.length獲取引數值得位元組個數

 SELECT LENGTH('john');
==============================================================
2.concat 拼接字串

 SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
================================================================
3.upper,lower 改變大小寫

 SELECT UPPER('qweA');

 SELECT LOWER('GHUe');
實體：將姓變大寫，名變小寫，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))
FROM employees;

====================================================================
4.substr

注意：索引從一開始
截取從指定索引處開始后面所有的字符

 SELECT SUBSTR('李莫愁愛上了陸展元',7);

截取從指定索引處指定字符長度的字符

SELECT SUBSTR('李莫愁愛上了陸展元',1,3);

實體：姓名中首字母大寫，其他字符小寫
SELECT CONCAT( UPPER( SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))
FROM employees;
===================================================================
5.instr 回傳子串第一次出現的索引，找不到回傳0

 SELECT INSTR('楊不悔殷六俠愛上殷六了殷六俠','殷六俠');
===============================================================
6.trim去前后空格，不去中間

 SELECT TRIM('     張翠山       ')
 SELECT TRIM('a' FROM 'aaaaaaaaaa張aaaa翠山aaaaaaa') qw
=================================================================
7.lpad 用指定字符實作左填充指定長度

  SELECT LPAD('殷素素',10,'*')
  SELECT LPAD('殷素素',2,'*')
=======================================================================
8.rpad 用指定字符實作右填充指定長度

      SELECT RPAD('殷素素',10,'*')
    
      SELECT RPAD('殷素素',12,'ab')
==========================================================================
9.replace

SELECT REPLACE('張無忌周芷若周若愛上了周芷若','周芷若','1' )

數學函式：

1.round 四舍五入
 SELECT ROUND(1.45)
 SELECT ROUND(1.456,2)
=============================
2.ceil 進一法，向上取整，>=
SELECT CEIL(1.52)   2
SELECT CEIL(-1.52)   -1
============================= 
3.floor 向下取整
 SELECT FLOOR(-9.99)   -10 
 SELECT FLOOR(1.52)  1
=============================
4.truncate 截斷
SELECT TRUNCATE(1.65,1)   1.6
SELECT TRUNCATE(1.6999,1)  1.6

=============================
5.mod 取余
SELECT MOD(10,-3)
SELECT 10%3;

日期函式:

1.now 回傳當前系統日期+時間
 SELECT NOW();

2.curdate 回傳當前日期
 SELECT CURDATE()

3.curtime 回傳當前時間
 SELECT CURTIME()

4.獲取指定的部分，年，月，日，小時，分鐘，秒
 SELECT YEAR(NOW()) 年

 SELECT MONTH(NOW()) 月

5.str_to_date 字串轉日期  
SELECT STR_TO_DATE('1998-3-4','%Y-%c-%d')

例子：查詢入職日期為1992-4-3的員工資訊
 
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');


6.date_format 日期轉字符 

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')

流程控制函式：

1.if函式：
SELECT IF('10>5','大','小');

SELECT last_name,commission_pct ,IF(commission_pct IS NULL,'沒','有')
FROM employees;

2.case函式:
使用一：swith case
case 欄位或運算式 when 常量  then 要顯示的值或陳述句

案例1：

 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;

使用二：類似多重if
 case when 條件 then值1 else 值 end，

SELECT `salary`,CASE
WHEN `salary`>20000 THEN 'A'
WHEN `salary`>15000 THEN 'B'
ELSE 'C'
END 工資級別
FROM employees;

其它函式：

version: 版本
database: 當前庫
user: 當前連接用戶

SELECT VERSION()

SELECT DATABASE()

SELECT USER()

二分組函式

功能：統計使用，又稱為統計函式，聚合函式，組函式分類：sum 求和，avg 平均值，max 最大值，min 最小值，count 計算個數、
特點：

1.sum，AVG 一般處理數值型
max，min count 都行
2.忽略null
3.可以和distinct搭配

1.簡單使用：

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary),COUNT(salary) FROM employees;

三分組查詢

語法：

   select 查詢的欄位，分組函式
    from 表
    group by 分組的欄位

特點：

1、可以按單個欄位分組
2、和分組函式一同查詢的欄位最好是分組后的欄位
3、可以按多個欄位分組，欄位之間用逗號隔開
4、可以支持排序
5、having后可以支持別名

面試：

* where 和 having 的區別？
1. where 在分組之前進行限定，如果不滿足條件，則不參與分組，having在分組之后進行限定，如果不滿足結果，則不會被查詢出來
2. where 后不可以跟聚合函式，having可以進行聚合函式的判斷，

例子：

1.查詢郵箱中包含a字符的，每個部門的平均工資

SELECT AVG(salary),`department_id`
FROM employees
WHERE email LIKE '%a%'
GROUP BY `department_id`

案例1.查詢每個工種的工資

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

案例2：查詢每個位置上的部門個數

 SELECT COUNT(*),location_id
 FROM `departments`
 GROUP BY `location_id`
 
 案例3.查詢哪個部門的員工個數>2
 SELECT COUNT(*),`department_id`
 FROM employees
 GROUP BY `department_id`
 HAVING COUNT(*)>2
 
 案例4.查詢每個工種有獎金的員工的最高工資>12000的員工編號，最高工資
 SELECT MAX(salary),`job_id`
 FROM employees
 GROUP BY `job_id`
 HAVING MAX(salary)>12000
 
 案例5.查詢領導編號>102的每個領導手下的最低工資>5000的領導編號是哪個，以及最低工資
SELECT MIN(salary), `manager_id`
FROM employees
WHERE `manager_id`>102
GROUP BY `manager_id`
HAVING MIN(salary)>5000
 
#按運算式或函式分組
#案例：按員工姓名的長度分組，查詢每一組的員工個數，篩選員工個數>5的有哪些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5

#按多個欄位分組
#案例：查詢每個部門每個工種的員工的平均工資
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)

接下來來到下一個重點：多表連接查詢

四多表連接查詢

含義：又稱多表查詢，當查詢的欄位來自于多個表時，就會用到連接查詢

笛卡爾乘積現象：表一 m行，表二 n行結果：m*n行
解決辦法：添加連接條件

分類：
按年代分類：sql92，
sql99【推薦】

按功能分類：內連接：等值連接，非等值連接，自鏈接
外連接：左外連接，右外連接，全外連接
交叉連接

提到內連接，先了解了解笛卡爾集：

* 產生條件：
1. 省略連接條件
2. 連接條件無效
3. 所有表中所有行互相連接
* 解決方案：
添加有效篩選條件

1.SQL99標準

語法：

select 查詢串列  from 表1 別名【連接型別】
    join 表2 別名
    on 連接條件
    【where 篩選條件】
    【group by 】
    【having 】
    【order by】

特點：

篩選條件放在where后面，如like

分類：
內連接：inner
外連接：左外連接:left【outer】
右外連接:right【outer】
全外連接:full【outer】
交叉連接:cross

1.1 內連接

	select 查詢串列
	from 表1 別名【inner】
	join 表2 別名
	on 連接條件

內連接分類：等值連接，非等值，自連接

接下來通過幾個案例講解一下：

1.等值連接
===================================================================
/*①案例1：查詢員工名，部門名，*/
 SELECT `last_name`,`department_name`
 FROM employees e
 JOIN departments d
 ON e.`department_id` = d.`department_id`;


================================================================
/*②案例2：查詢部門個數>3的城市名和部門個數，（添加分組，篩選）*/
SELECT `city`,COUNT(*)
FROM `locations` l
INNER JOIN `departments` d
ON l.`location_id`=d.`location_id`
GROUP BY `city`
HAVING COUNT(*)>3;
====================================================================
/*③案例3：查詢哪個部門的員工個數>3的部門名和員工個數，并按個數降序(添加排序)*/
SELECT COUNT(*),`department_name`
FROM `departments` d
INNER JOIN `employees` e
ON d.`department_id`=e.`department_id`
GROUP BY d.`department_name`
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

========================================================================
========================================================================
2.非等值連接

/*查詢員工的工資級別*/

SELECT salary,`grade_level`
FROM `employees` e
JOIN `job_grades` g
ON e.`salary` BETWEEN g.`lowest_sal`AND g.`highest_sal` ;

===========================================================================

3.自連接
/*查詢員工名，上級名*/

SELECT e.`last_name`,m.`last_name`
FROM `employees` e
JOIN employees m
ON e.`manager_id`=m.`employee_id`

這幾個案例講解的就是內連接，然后講解外連接，

1.2外連接

應用場景：用于查詢一個表中有，寧一個表中沒有的記錄

特點：
1.外連接的查詢結果為主表的所有記錄
如果從表中有和他匹配的，則顯示匹配的值
如果從表中沒有和他匹配的，則顯示null

2.左外連接，left左邊的是主表
右外連接，right右邊的是主表

還是通過一個案例，便于理解：

查詢哪個部門沒有員工

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;


2.右外

SELECT d.*,e.`employee_id`
FROM  employees e
RIGHT OUTER JOIN`departments` d
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

3.全外連接
USE girls;
SELECT b.*,bo.*
FROM`beauty` b
FULL OUTER `boys` bo
ON b.`id`=bo.`id`

1.3交叉連接

交叉連接
SELECT b.*,bo.*

FROM`beauty` b
CROSS JOIN boys bo

下一個重難點：子查詢

2.子查詢

含義：出現在其他陳述句中的select陳述句，稱為子查詢或內查詢，外部的查詢陳述句，稱為主查詢或外查詢
分類：
1.按子查詢出現的位置：
select后面標量子查詢
from后面表子查詢
※※where,having 標量子查詢（單行）√ 列子查詢（多行）√ 行子查詢
exists（相關子查詢）表子查詢
2.按結果集的`department_id`行列數不同：
標量子查詢(結果集只有一行一列)
列子查詢（結果集只有一列多行）
行子查詢（結果集只有一行多列）
表子查詢（結果集一般為多行多列）

特點：
1.子查詢放在小括號內
2.子查詢一般放在條件的右側
3.標量子查詢，一般搭配單行運算子
><=
列子查詢,一般搭配多行運算子
in,any/some/all

4.子查詢優先于主查詢

1.標量子查詢

/*案例一：誰的工資比abel高*/
SELECT *
FROM employees
WHERE salary >(
SELECT salary
FROM employees
WHERE last_name = 'Abel'

)

案例二：查詢job——ID與141號員工相同，salary比143號員工多的員工 姓名，job_id和工資
SELECT *
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE `employee_id`=141
) AND salary>(
SELECT salary
FROM employees
WHERE `employee_id`=143

2.列子查詢

案例：查詢`location_id`是1400或者1700的部門中所有員工姓名
SELECT `last_name`
FROM employees
WHERE `department_id` IN(

SELECT DISTINCT `department_id`
FROM `departments`
WHERE `location_id` IN (1400,1700
)
)


查詢其他部門中比job_id為‘IT_PROG’部門任一工資低的員工的，工號，姓名，job_id,salary


SELECT `employee_id`,`last_name`,`salary`
FROM employees
WHERE salary < ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG'

放在exists后面(相關子查詢)

語法：exists(完整查詢陳述句)
結果0,1

SELECT EXISTS(SELECT `employee_id`FROM `employees` WHERE salary=300000)

/*查詢有員工的部門名*/

SELECT `department_name`
FROM `departments` d
WHERE EXISTS (
SELECT * FROM employees e
WHERE d.`department_id`=e.`department_id`
)

3 分頁查詢

語法：

	select 欄位|運算式,...
	from 表
	where 條件
	group by 分組欄位
	having 條件
	order by 排序的欄位
	limit 起始的條目索引，條目數;

特點：

1.起始條目索引從0開始

2.limit子句放在查詢陳述句的最后

3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:
每頁顯示條目數sizePerPage
要顯示的頁數 page

查詢前五條
SELECT *
FROM employees
LIMIT 0,5

4 聯合查詢

語法：

	select 欄位|常量|運算式|函式 from 表 where 條件 union 【all】
	select 欄位|常量|運算式|函式 from 表 where 條件 union 【all】
	select 欄位|常量|運算式|函式 from 表 where 條件 union 【all】
	.....
	select 欄位|常量|運算式|函式 from 表 where 條件

特點：

1、多條查詢陳述句的查詢的列數必須是一致的
2、多條查詢陳述句的查詢的列的型別幾乎相同
3、union代表去重，union all代表不去重

代碼：

/*查詢部門編號>90或者郵箱包含a的員工資訊
*/
SELECT *
FROM employees
WHERE email LIKE'%a%'
UNION

SELECT *
FROM employees
WHERE `department_id`>90

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/330415.html

標籤：其他

上一篇：這是升級標準庫鎖的有效方法嗎？

下一篇：重復值listView-Android

MySQL進階（分組查詢，多表連接查詢）

一 常見函式（單行函式）

二 分組函式

三 分組查詢

四 多表連接查詢

1.SQL99標準

1.1 內連接

1.2外連接

1.3交叉連接

2.子查詢

3 分頁查詢

4 聯合查詢

一常見函式（單行函式）

二分組函式

三分組查詢

四多表連接查詢