Mysql學習記錄(1)

Mysql學習記錄(3)

Day02

7.DQL語言
- 7.3排序查詢
- 7.4常見函式
- - 7.4.1單行函式
  - - 7.4.1.1字符函式
    - 7.4.1.2數學函式
    - 7.4.1.3日期函式
    - 7.4.1.4其它函式
    - 7.4.1.5流程控制函式
- 7.5分組函式
- - 7.5.1 COUNT函式
- 7.6分組查詢
- - 7.6.1簡單分組查詢
  - 7.6.2添加分組前的篩選
  - 7.6.3添加分組后的篩選--HAVING
  - 7.6.4按運算式/函式分組
  - 7.6.5按多個欄位分組
  - 7.6.6添加排序
- 7.7連接/多表查詢
- - 7.7.1 sql92標準
  - - 7.7.1.1等值連接
    - 7.7.1.2非等值連接
    - 7.7.1.3自連接

7.DQL語言

7.3排序查詢

語法：
關鍵字order by

SELECT 查詢串列
FROM 表
【WHERE 篩選條件】
ORDER BY 排序串列 【asc|desc】

特點：
1.升序asc，降序desc，如果不寫，默認asc
2.支持單欄位、多欄位、運算式、函式、別名
3.一般放在查詢陳述句的最后面，在limit子句之前
案例：

#1.查詢員工資訊，要求工資從高到低排序【按數值型排序】
SELECT * FROM employees ORDER BY salary DESC;

#2.查詢部門編號>=90的員工資訊，按入職時間的先后進行排序【按時間型排序】
SELECT * 
FROM employees 
WHERE department_id>=90
ORDER BY hiredate ASC;

#3.按年薪的高低顯示員工的資訊和年薪【按運算式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees 
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#4.按年薪的高低顯示員工的資訊和年薪【按別名排序】//運算式太長
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees 
ORDER BY 年薪 DESC;

#5.按姓名的長度顯示員工的姓名和工資【按函式排序】
SELECT LENGTH(last_name) 位元組長度,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;

#6.查詢員工資訊，先按工資升序排序，再按編號降序排序【按多個欄位排序,即嵌套排序】
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

7.4常見函式

呼叫：
select 函式名(實參串列) 【FROM 表】;//若用到表中的欄位則寫FROM
分類：
①單行函式：concat、length、ifnull.etc
②分組函式(統計函式、聚合函式、組函式)：作統計使用

7.4.1單行函式

7.4.1.1字符函式

①length：獲取引數值的位元組個數

SELECT LENGTH('john');			//4
SELECT LENGTH('張三豐john');	//15，utf8漢字3個位元組，gbk漢字2個位元組

②concat：拼接字串

SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;

③upper、lower：大小寫轉換

SELECT UPPER('john');			//JOHN
SELECT UPPER('JOHN');			//john
#將姓變大寫，名變小寫，并拼接
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) AS 姓名 FROM employees;

④substr、substring：字串
注：索引從1開始

SELECT SUBSTR('Mysql學習記錄',6) AS out_put;		//學習記錄，截取從第6及以后全部
SELECT SUBSTR('Mysql學習記錄',1,5) AS out_put;	//Mysql，截取從第1數5個字符長度
#姓中首字符大寫，其它字符小寫然后用_拼接，顯示出來
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) 
AS out_put
FROM employees;

⑤instr：回傳子串的第一次出現起始索引，找不到回傳0

SELECT INSTR('Mysql學習記錄','記錄') AS out_put;	//8

⑥trim：去掉前后相同字符(串)，默認為空格

SELECT LENGTH(TRIM('      學習記錄      ')) AS out_put;		//12=3*4
SELECT TRIM('a' FROM 'aaa學習記aa錄aaaaaa') AS out_put;		//學習記aa錄

⑦lpad：用指定字符，左填充指定長度，若原有長度大于指定長度，則不填充，且右截斷原有字串

SELECT LPAD('學習記錄',10,'*') AS out_put;		//******學習記錄
SELECT LPAD('學習記錄',2,'*') AS out_put;		//學習

⑧rpad：用指定字符，右填充指定長度，若原有長度大于指定長度，則不填充，且仍是右截斷原有字串

SELECT RPAD('學習記錄',11,'ab') AS out_put;		//學習記錄abababa
SELECT RPAD('學習記錄',2,'ab') AS out_put;		//學習

⑨replace：全部替換

SELECT REPLACE('Mysql學習記錄Mysql','Mysql','Java');	//Java學習記錄Java

7.4.1.2數學函式

①round：四舍五入（先取絕對值）

SELECT ROUND(1.45);		//1
SELECT ROUND(1.5);		//2
SELECT ROUND(-1.65);	//-2
SELECT ROUND(-1.5);		//-2

SELECT ROUND(1.567,2);	//1.57

②ceil：向上取整（>=該引數的最小整數）

SELECT CEIL(1.02);		//2
SELECT CEIL(-1.02);		//-1

③floor：向下取整（<=該引數的最小整數）

SELECT FLOOR(9.99);		//9
SELECT FLOOR(-1.01);		//-2

④truncate：截斷，從小數點后開始幾位

SELECT TRUNCATE(1.66,1);		//1.6 
SELECT TRUNCATE(-1.321,2);		//-1.32

⑤mod：取余

【MOD(a,b)=a-a/b*b】
SELECT MOD(10,3);		//1
SELECT MOD(-10,-3);		//-1

7.4.1.3日期函式

①now：回傳當前系統日期+時間

SELECT NOW();		//2021-02-15 15:28:36

②curdate：回傳當前系統日期，不包含時間

SELECT CURDATE();		//2021-02-15

③curtime：回傳當前系統時間，不包含日期

SELECT CURTIME();		//15:30:44

④獲取指定的部分：年、月、日、小時、分鐘、秒

SELECT YEAR(NOW());				//2021
SELECT MONTHNAME('1998-12-31');		//December，月名稱
SELECT DAY(hiredate) AS 日 FROM employees;

⑤str_to_date：將字符通過指定的格式轉換為日期

格式符	功能
%Y	四位年份
%y	兩位年份
%m	兩位月份
%c	最簡年份
%d	兩位日
%H	24小時
%h	12小時
%i	分鐘
%s	秒

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

⑥date_format：將日期轉成字符，格式符同上

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;	//21年02月15日
#查詢有獎金的員工名和入職日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入職日期 
FROM employees 
WHERE commission_pct IS NOT NULL;

⑦datediff：回傳兩個日期相差的天數

7.4.1.4其它函式

SELECT VERSION();
SELECT DATABASE();	//當前打開的資料庫
SELECT USER();
SELECT MD5(’字符‘);	//MD5加密

7.4.1.5流程控制函式

①if函式：實作if else的效果，類似三元運算子？：

SELECT IF(10>5,'大','小')；
#查詢員工是否有獎金
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'無獎金','有獎金')  AS 備注 
FROM employees;

②case結構
使用一：類似switch-case：case-when
適用于等值判斷

case 要判斷的欄位或運算式
when 常量1 then 要顯示的值1或(陳述句1；)
when 常量2 then 要顯示的值2或(陳述句2；)
……
else then 要顯示的值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;

使用二：類似多重if
適用于區間判斷

case
when 條件1 then 要顯示的值1或(陳述句1;)
when 條件2 then 要顯示的值2或(陳述句2;)
……
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;

7.5分組函式

分類：sum求和、avg平均值、max、min、count計算個數

#簡單使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最大,MIN(salary) 最小,COUNT(salary) 計數  FROM employees;

特點：
①SUM、AVG：建議支持數值型
MAX、MIN：可以處理任何型別
COUNT：計算非空值的個數
②以上分組函式都忽略NULL值
③可以和DISTINCT搭配實作去重
④和分組函式一同查詢的欄位要求是group by后的欄位

#去重效果
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;	//計算有幾種工資

7.5.1 COUNT函式

統計行數/“個數”

SELECT COUNT(*) FROM employees;	//只要有一個欄位不為null，行數+1 
SELECT COUNT(1) FROM employees;	
#兩種方法在INNODB下效率差不多，一般用COUNT(*)

7.6分組查詢

應用：統計各部門平均工資
語法：

	select 分組函式,列 		//5，分組函式，"的"后
	from 表					//1
	【where 篩選條件】		//2
	group by 分組的串列		//3
	【having 子句】			//4
	【order by 子句】		//6
注：查詢串列，要求是分組函式和group by后出現的欄位,group by后欄位必須出現在select中,"每個"/"各個"
原表有的用where，原表沒有的用having

特點：

篩選條件	資料源	位置	關鍵字
分組前篩選	原始表	group by子句的前面	where
分組后篩選	分組后的結果集	group by子句的后面	having

①分組函式作條件肯定在HAVING子句中
②能用分組前篩選的，優先考慮where
③group by支持單欄位、多欄位分組(無順序要求)、運算式\函式分組
④可以添加排序分組order by
⑤由于Oracle不支持，因此不建議在group by和having中使用別名

7.6.1簡單分組查詢

案例

#查詢每個工種的最高工資
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

#查詢每個位置的部門個數
SELECT COUNT(*),location_id	//3
FROM departments	//1
GROUP BY location_id;	//2

7.6.2添加分組前的篩選

#查詢郵箱中包含a字符的，每個部門的平均工資
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

7.6.3添加分組后的篩選–HAVING

/*查詢哪個部門的員工個數>2
①查詢每個部門的員工個數
②根據①的結果進行篩選
*/
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;


#查詢每個工種有獎金的員工的最高工資>12000的工種編號和其最高工資
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL		//原表中有此欄位
GROUP BY job_id
HAVING MAX(salary)>12000;				//原表中沒有MAX(salary)

7.6.4按運算式/函式分組

#按員工姓名的長度分組，查詢每一組的員工個數，篩選員工個數>5的有哪些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

7.6.5按多個欄位分組

#查詢每個部門每個工種的員工的平均工資
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

7.6.6添加排序

#查詢每個非NULL部門每個工種的員工的>10000的平均工資，并按平均工資由高到低排列
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;

7.7連接/多表查詢

概念：當查詢的欄位來自于多個表時，用連接查詢，
背景：當查詢多個表時，沒有添加有效的連接條件，導致多個表所有行實作完全連接，【笛卡爾乘積現象】
分類：
按年代分：
①sql92標準：僅支持內連接和Oracle、SQL server使用的外連接
②sql99標準(推薦)：除全外連接不支持，都支持
按功能分：
①內連接：等值連接、非等值連接、自連接
②外連接：左外連接、右外連接、全外連接
③交叉連接

7.7.1 sql92標準

7.7.1.1等值連接

語法：

	select 查詢串列
	from 表1 別名,表2 別名
	where 表1.key=表2.key
	【and 篩選條件】
	【group by 分組欄位】
	【having 分組后的篩選】
	【order by 排序欄位】

特點：
①多表等值連接的結果為多表的交集部分
②n表連接至少需要n-1個連接條件
③多表順序無要求
④一般需要為表起別名
⑤可以搭配排序、分組、篩選子句

#查詢員工名對應的部門名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

當需要頻繁使用表名限定時，為表起別名
注：如果為表起了別名，查詢的欄位不能使用原來的表名去限定

#查詢員工號、工種號、工種名
SELECT last_name,e.job_id,job_title	//3
FROM employees AS e,jobs AS j		//1，此處兩個表的順序可以調換
WHERE e.job_id=j.job_id;			//2

可以加篩選

#查詢有獎金的員工名、部門名
SELECT last_name,department_name,commission_pct
FROM employees AS e,departments  AS d
WHERE  e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;

#查詢城市名中第二個字符為o的部門名和城市名
SELECT department_name,city
FROM departments AS d,locations AS l
WHERE d.location_id=l.location_id
AND city Like '_o%';

可以加分組

#查詢每個城市的部門個數
SELECT COUNT(*) 個數,city
FROM departments AS d,locations AS l
WHERE d.location_id=l.location_id
GROUP BY city;

可以加排序

#查詢每個工種的工種名和員工的個數，并且按員工個數降序
SELECT COUNT(*) 個數,job_title
FROM jobs AS j,employees AS e
WHERE j.job_id=e.job_id
GROUP BY job_title
ORDER BY 個數 DESC;

可以三表連接

#查詢員工名、部門名和所在的城市
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;

7.7.1.2非等值連接

語法：

	select 查詢串列
	from 表1 別名,表2 別名
	where 非等值的連接條件
	【and 篩選條件】
	【group by 分組欄位】
	【having 分組后的篩選】
	【order by 排序欄位】

#查詢員工的工資和工資級別
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

7.7.1.3自連接

將原始一張表當作多張表使用
語法：

	select 查詢串列
	from 表1 別名1,表1 別名2
	where 等值的連接條件
	【and 篩選條件】
	【group by 分組欄位】
	【having 分組后的篩選】
	【order by 排序欄位】

eg:employees表當作員工表和領導表

#查詢 員工名和上級的名稱
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;

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

標籤：其他

上一篇：SPARQL應用實體

下一篇：測驗real application security的ACL

Mysql學習記錄(2)