前言

進階三 排序查詢

語法

 select 
        查詢串列
    from
        表
    where
        篩選條件
        order by 排序串列【asc/desc】
    特點：
        1.asc代表的是升序，desc代表的是降序
        2.如果不寫的話就表示默認的升序
        3.order by子句中可以支持單個欄位，多個欄位，運算式，函式，別名
        4.order by子句一般是放在查詢陳述句的最后面，limit子句除外

案例

#案例一、查詢出工資從高到底的所有員工的資訊
SELECT 
    *
FROM
    employees
ORDER BY salary DESC;
#案例二、查詢出部門>=90按入職的時間的先后順序排序的所有員工的資訊（添加篩選條件）
SELECT
    *
FROM
    employees
WHERE 
    `department_id`>=90
ORDER BY
    `hiredate` ASC;
#案例三、按年薪的高低顯示員工的資訊，年薪（按運算式排序）
SELECT
    *,
    `salary`*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM
    `employees`
ORDER BY
    `salary`*12*(1+IFNULL(`commission_pct`,0)) DESC
#案例四、按年薪的高低顯示員工的資訊，年薪（按別名排序）
SELECT
    *,
    `salary`*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM
    `employees`
ORDER BY
    年薪 DESC
#案例五、按姓名的長度顯示員工的姓名和工資（按函式排序）
SELECT
    LENGTH(`last_name`) AS 姓名的長度,
    `last_name`,
    `salary`
FROM
    `employees`
ORDER BY LENGTH(`last_name`) DESC;
#案例六、按員工的編號降序，工資升序查詢所有的員工的資訊（按多個欄位排序）
SELECT
    *
FROM
    `employees`
ORDER BY `employee_id` DESC,`salary`;

測驗題

#查詢員工的姓名和部門號和年薪，按年薪降序，姓名升序
SELECT
    last_name,
    department_id,
    salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
    employees
ORDER BY 年薪 DESC,last_name;
#查詢工資不在8000-17000的員工的姓名和工資，工資降序
SELECT
    last_name,
    salary
FROM
    `employees`
WHERE
    NOT salary BETWEEN 8000 AND 17000 ORDER BY salary DESC;
#查詢員工郵箱中含e的資訊，并先按郵箱的位元組數排序，再按部門號升序
SELECT
    *
FROM
    `employees`
WHERE
    `email` LIKE '%e%' ORDER BY LENGTH (email) DESC,`department_id`; 

進階四：常見函式

概念

類似于Java中的方法，將一組邏輯陳述句封裝在方法體中，對外暴露方法名

好處：
①隱藏了實作細節 ②提高了代碼的重用性
呼叫：select 函式名（實參串列） 【from 表】;
特點：
叫什么（函式名）
干什么（函式功能）
分類：
1.單行函式
如：concat、length、ifnull等
字符函式
數字函式
日期函式
其他函式
流程控制函式
2.分組函式

功能：做統計使用，又稱為統計函式，聚合函式，組函式

一、字符函式

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

SELECT LENGTH('hahaha');
SELECT LENGTH('留個名hahaha');

2.concat拼接字串

SELECT CONCAT(`first_name`,':',`last_name`) FROM employees;

3.upper(變大寫) lower(變小寫)

SELECT UPPER('jdk');
SELECT LOWER('JDK');
#例子：將姓變大寫名變小寫打出來
SELECT CONCAT(UPPER(`first_name`),':',LOWER(`last_name`)) FROM `employees`;

4.substr,subtring

#截取從指定索引處后面的所有字符
注意：索引從1開始的（Java索引是從0開始的）
SELECT SUBSTR('黎豪愛吃屎',2);
#截取從指定索引出指定字符長度的字符
SELECT SUBSTR('黎豪愛吃屎',1,2);
#案例、名字中的首字母大寫，其余的小寫然后用_連接起來
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))
FROM `employees`

5.instr 回傳字串第一次出現的索引，如果找不到的話就回傳0

SELECT INSTR('黎豪好愛吃屎，沒人攔得住他','攔得住');
#6.trim 去掉前后的空格
SELECT TRIM('   黎豪吃屎   ') AS out_put;
SELECT TRIM('a' FROM 'aaaaa黎aaa豪aaa');#去掉的是前后的a,中間的a去不掉

6.lpad 用指定的字符實作左填充指定字符的長度(當如果指定的長度小于最前面寫的字符的時候，前面的字符會被截斷)

SELECT LPAD('黎豪',10,'*');

7.rpad 用指定的字符實作右填充指定字符的長度

SELECT RPAD('黎豪',10,'#');

8.replace替換

SELECT REPLACE('黎豪愛上了屎','屎','尿');

二、數學函式

1.round四舍五入

SELECT ROUND(1.2);
SELECT ROUND(1.563,2);#保留小數點后兩位

2.ceil向上取整（回傳>=該引數的最小整數）

SELECT CEIL(1.02);

3.floor向下取整（回傳<=該引數的最大整數）

SELECT FLOOR(2.01);

4.truncate截斷

SELECT TRUNCATE(1.6523,2);

5.mod取余(mod(a,b)里面的運算就是（a-a/b*b）)

SELECT MOD(10,3);

三、日期函式

1.now 回傳當前系統的如期＋時間

SELECT NOW();

2.curdate 回傳當前的系統日期，不包含時間

SELECT CURDATE();

3.curtime 回傳當前的系統時間，不包含日期

SELECT CURTIME();

4.可以獲取指定的部分的年，月，日，小時，分鐘，秒

SELECT YEAR(NOW());
SELECT YEAR('2012-1-2');
SELECT YEAR(hiredate) FROM employees;

SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());#顯示的是英文的月

5.str_to_date 將字符通過指定的格式轉換成日期

SELECT STR_TO_DATE('5-20 1999','%m-%d %Y');
#案例、查詢入職時間為1992-4-3的員工的資訊
SELECT
	last_name,
	hiredate
FROM
	`employees`
WHERE
	hiredate=STR_TO_DATE('4-3 1992','%m-%d %Y');

6.date_format將日期轉換成字符

SELECT DATE_FORMAT('1523-2-3','%m月-%d日-%Y年');
#案查詢有獎金的員工的員工名和入職日期（xx月-xx日-xx年）
SELECT `last_name`,DATE_FORMAT(hiredate,'%m月-%d日-%Y年') AS 入職日期
FROM `employees`
WHERE `commission_pct` IS NOT NULL;

四、其他函式

SELECT VERSION();#查看版本
SELECT DATABASE();#查看當前的資料庫
SELECT USER();#查看當前的用戶

五、流程控制函式

#1.if函式->if else的效果：if（1，2，3）如果1成立則是結果2如果不成立的話則是結果3
#案例、查詢出有獎金（有獎金，厲害啊）的與沒有獎金（沒有獎金，加油啊）的員工進行備注
SELECT `last_name`,IF(`commission_pct` IS NOT NULL,'有獎金，厲害啊','沒有獎金，加油啊') AS 備注
FROM `employees`

#2.case函式的使用一、類似于Java中的swich
/*
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 AS 原工資,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的使用二、類似于多重if
/*
case 
when 條件1 then 要顯示的值1或陳述句1
when 條件2 then 要顯示的值2或陳述句2
when 條件3 then 要顯示的值3或陳述句3
，，，
else 要顯示的值n或陳述句n
end
*/
/*
案例、查詢員工的工資情況
如果工資大于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`;

六、分組函式

簡要概述

功能：
做統計使用，又稱為聚合函式統計函式和組函式
分類：
sum求和、avg平均值、max最大值、min最小值、count計算個數
特點：
1.sum,avg一般用于處理數值型
max,min,count可以處理任何型別
2.以上的分組函式都忽略null值
3.以上和distinct搭配
4.count函式的單獨介紹
5.和分組函式一同查詢的欄位要求是group by 后的欄位

1.簡單的使用

SELECT SUM(salary) FROM `employees`;

2.和distinct搭配使用

SELECT SUM(DISTINCT salary),SUM(salary) FROM `employees`;

3.count函式的單獨介紹

#一般使用count(*)用作統計行數
SELECT COUNT(*) FROM `employees`;
SELECT COUNT(1) FROM employees;#也可以統計行數
SELECT COUNT('黎豪') FROM employees;#也可以統計行數
#案例、查詢部門編號為90的員工個數
SELECT COUNT(*)
FROM `employees`
WHERE department_id = '90';