MySQL學習筆記【基礎篇】

Z發表於2020-08-17
MySql筆記

從前曾經學過一下mysql的基礎內容，不過由於當時沒有認真學導致會的東西太少，現根據一個教程視訊的學習【此內容對應基礎篇的P1-P178】，完成了這個筆記，主要涉及mysql的基礎知識。

一、資料庫相關概念

1、DB：資料庫，儲存一組有組織的資料的容器
2、DBMS：資料庫管理系統，又稱為資料庫軟體（產品），用於管理DB中的資料
3、SQL:結構化查詢語言，用於和DBMS通訊的語言

二、資料庫的好處

1.持久化資料到本地

2.可以實現結構化查詢，方便管理

三、資料庫儲存資料的特點

1、將資料放到表中，表再放到庫中
2、一個資料庫中可以有多個表，每個表都有一個的名字，用來標識自己。表名具有唯一性。
3、表具有一些特性，這些特性定義了資料在表中如何儲存，類似java中 “類”的設計。
4、表由列組成，我們也稱為欄位。所有表都是由一個或多個列組成的，每一列類似java 中的”屬性”
5、表中的資料是按行儲存的，每一行類似於java中的“物件”。

四、MySQL服務的啟動和停止

方式一：計算機——右擊管理——服務
方式二：通過管理員身份執行，輸入

net start 服務名（啟動服務）
net stop 服務名（停止服務）

服務名是根據你當初的設定決定的，如果你忘了，可以參考這個連結。

你的服務名是MySQL57

五、mysql服務的登入和退出

方式一：通過mysql自帶的客戶端
只限於root使用者

方式二：通過windows自帶的客戶端
登入：
mysql 【-h主機名 -P埠號 】-u使用者名稱 -p密碼

退出：
exit或ctrl+C

六、MySQL的常用命令

1.檢視當前所有的資料庫
show databases;
2.開啟指定的庫
use 庫名
3.檢視當前庫的所有表
show tables;
4.檢視其它庫的所有表
show tables from 庫名;
5.建立表

create table 表名(

	列名 列型別,
	列名 列型別，
	。。。
);

6.檢視錶結構
desc 表名;

7.檢視伺服器的版本
方式一：登入到mysql服務端
select version();
方式二：沒有登入到mysql服務端（在cmd控制檯上輸入）
mysql --version
或
mysql --V

七、MySQL的語法規範

1.不區分大小寫,但建議關鍵字大寫，表名、列名小寫
2.每條命令最好用分號結尾
3.每條命令根據需要，可以進行縮排或換行
4.註釋

​ 單行註釋：#註釋文字

​ 單行註釋：-- 註釋文字

​ 多行註釋：/* 註釋文字 */

八、SQL的語言分類

DQL（Data Query Language）：資料查詢語言
	select 
DML(Data Manipulate Language):資料操作語言
	insert 、update、delete
DDL（Data Define Languge）：資料定義語言
	create、drop、alter
TCL（Transaction Control Language）：事務控制語言
	commit、rollback

九、DQL語言的學習

基礎查詢

語法：

SELECT 查詢列表 FROM 表名;

注意：

  • 查詢列表可以是：表中的欄位、常量值、表示式、函式。
  • 查詢的結果是一個虛擬的表格。
  • 常量值可以用單引號雙引號引上，欄位值可以用(鍵盤上數字1左邊那個鍵的符號引上)，注意二者區別。一般情況下可以不用引，當欄位與關鍵字重名或者是特殊符號時引上比較好。

舉例：

1.查詢表中的單個欄位

SELECT last_name FROM employees;

2.查詢表中的多個欄位

SELECT last_name, salary, email FROM employees;

3.查詢表中的所有欄位

SELECT * FROM employees;

4.查詢常量值

SELECT 100;

SELECT 'john';

5.查詢表示式

SELECT 100%98;

6.查詢函式

SELECT VERSION();

7.起別名

方式一：

SELECT 100%98 as 結果;

SELECT last_name AS 姓,first_name AS 名 FROM employees;

起別名好處：

  • 便於理解。
  • 如果要查詢的欄位有重名的情況，可以使用別名區別開來。

方式二：

SELECT last_name 姓,first_name 名 FROM employees;

8.去重

案例：查詢員工表中涉及到的所有部門編號。

SELECT DISTINCT department_id FROM employees;

9.+號的作用

注意mysql中+號只有一個功能，充當運算子，不能連線字串。

select 100+90;如果兩個運算元均為數值型，則做加法運算。
select '123' + 90;	若其中一方為字元型，則試圖將字元型數值轉化成數值型
						如果轉換成功，則繼續做加法運算；
						否則，則將字元型轉換成0
//select '123'+90的結果為213
select 'john'+90;		//結果為90

select null+10;			只要其中一方為null，則結果肯定為null

案例：查詢員工名和姓連線成一個欄位，並顯示為 姓名。

既然+號不能起到連線欄位的作用，我們可以利用concat函式：

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

括號內的引數可以為多個。

10.concat函式

功能：拼接字元

select concat(字元1，字元2，字元3，...);

11.ifnull函式

功能：判斷某欄位是否為null，如果為null返回指定的值，否則返回原來的值。

select ifnull(commission_pct,0) from employees;

12.isnull函式

功能：判斷該欄位或表示式的值是否為null，是返回1，否則返回0

條件查詢

語法：

select 查詢列表 from 表名 where 條件;

條件的分類：

  • 條件表示式
    • 示例：salary>10000
    • 條件運算子：> < >= <= = !=
  • 邏輯表示式
    • 示例：salary>10000 && salary<20000
    • 邏輯運算子：
      • and（&&）:兩個條件如果同時成立，結果為true，否則為false；
      • or(||)：兩個條件只要有一個成立，結果為true，否則為false；
      • not(!)：如果條件成立，則not後為false，否則為true；
  • 模糊查詢
    • 運算子
      • like
        • 一般和萬用字元搭配使用。
        • 萬用字元有：
          • % 表示任意多個字元，包含0個字元
          • _ 表示任意單個字元
      • between and
        • 注意它包含臨界值
        • 兩個臨界值不要隨意調換順序
      • in
        • 用於判斷某欄位的值是否屬於in列表中的某一項
        • in列表的值型別必須統一或相容,比如'123'可以轉換成123
        • 不支援萬用字元
      • is null
        • =或者<>不能用於判斷null值
        • is null或is not null可以判斷null值
    • 例項：
      • 查詢員工名中包含字元a的員工資訊
        • SELECT * FROM employees WHERE last_name LIKE '%a%';
      • 查詢員工名中第三個字元為e,第五個字元為a的員工名和工資(第幾個就在前面畫幾個下劃線，注意不要忘記末尾要加個百分號，表示後面還有0個或多個字元)
        • SELECT last_name, salary FROM employees WHERE last_name LIKE '__n_l%';
      • 查詢員工名中第二個字元為_的員工名(由於萬用字元中有下劃線，這就需要用到轉義字元或者用escape說明，比如下面那個說明$符後面的下劃線不作為萬用字元)
        • SELECT * FROM employees WHERE last_name LIKE '_\_%';
        • SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$'; （推薦使用第二種方法）
      • 查詢員工編號在100到120之間的員工資訊
        • SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120
      • 查詢員工的工種編號是IT_PROG、AD_VP、AD_PRES中的員工名和工種編號
        • SELECT last_name, job_id FROM employees WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');
      • 查詢沒有獎金的員工名和獎金率
        • SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL;
      • 查詢有獎金的員工名和獎金率
        • SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL;

安全等於 <=>

  • 比如
    • SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL;
    • SELECT last_name, commission_pct FROM employees WHERE salary <=> 12000;
  • 不過它用的較少，可讀性差

is null 與 <=>的比較：

  • is null:僅僅可以判斷NULL值。可讀性較高。
  • <=> :該可以判斷NULL值，又可以判斷普通的數值。可讀性較低

階段測試：

查詢員工號為176的員工的姓名和部門號和年薪：
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct, 0)) AS 年薪 FROM employees;

排序查詢

語法：

  • select 查詢列表 from 表名 【where 篩選條件】 order by 排序列表 【asc | desc】;
  • 注意上面的asc表示升序，desc表示降序。如果不寫，預設是升序，不過前提是你寫了 order by。
  • order by子句中可以支援單個欄位、多個欄位、表示式、函式、別名。
  • order by子句一般放在查詢語句的最後面，不過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)) 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

4.【按別名排序】按年薪的高低顯示員工的資訊和年薪。

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;

5.按姓名的長度顯示員工的員工和工資【按函式排序】

SELECT LENGTH(last_name)位元組長度,last_name,salary FROM employees ORDER BY 位元組長度 DESC;

6.查詢員工資訊，要求先按工資升序排序，如果一樣的話再按員工編號降序排序【按多個欄位排序】

SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

常見函式

單行函式

1.字元函式

(1)length：獲取引數值的位元組個數

SELECT LENGTH('john');

(2)concat：拼接字串

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

(3)upper、lower

小案例：將表中姓大寫，名小寫，然後拼接。

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

(4)substr、substring

注意：索引從1開始。

擷取從指定索引處後面的所有字元：

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

擷取從指定索引處指定字元長度的字元：

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

小案例：

將姓名中首字元大寫，其他字元小寫然後用_拼接，顯示出來。

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM employees;

(5)instr：返回子串第一次出現的索引，如果找不到則返回0

SELECT INSTR('楊不悔愛上了殷六俠','殷八俠') AS out_put ;

(6)trim

SELECT LENGTH(TRIM(' 張存山 ')) AS out_put;

SELECT TRIM('a' FROM 'aaa張aaaaaa翠aaaaaaaa山') out_put;

(7)lpad ：用指定的字元實現左填充指定長度

SELECT LPAD('因素是',10,'*') AS out_put;

(8)rpad：用指定的字元實現右填充指定長度

SELECT RPAD('訂單',12,'abb') AS out_put;

(9)replace

SELECT REPLACE('張無忌愛上了周芷若周芷若周芷若周芷若','周芷若','趙敏')AS out_put;

2.數學函式

(1)round： 四捨五入

SELECT ROUND(1.65);

SELECT ROUND(1.567,2);

(2)ceil： 向上取整

SELECT CEIL(1.52);

(3)floor：向下取整

SELECT FLOOR(9.99);

(4)truncate：截斷

SELECT TRUNCATE(1.69,1);

(5)mod：取餘

SELECT MOD(-10,-3);

3.日期函式

(1)now：返回當前系統日期

SELECT NOW();

(2)curdate：返回當前系統日期，不包含時間

SELECT CURDATE();

(3)curtime：返回當前時間，不包含日期

SELECT CURTIME();

(4)獲取指定的部分，年、月、日、小時、分鐘、秒。

SELECT YEAR(NOW()) 年;

SELECT MONTH(NOW()) 月;

SELECT MONTHNAME(NOW()) 月;

舉個例子：獲取employees表中的時間中的年。

SELECT YEAR(hiredate) 年 FROM employees;

(5)str_to_date：將字元通過指定的格式轉換成日期。

語法：

前一個為日期字串，後一個為日期格式，日期格式可以從下列表中挑選。

舉個例子：

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

小案例:查詢入職日期為4-3 1992的員工資訊

SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992', '%c-%d %Y');

(6)date_format：將日期轉換成字元

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;

小案例：查詢有獎金的員工名和入職日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') FROM employees WHERE commission_pct IS NOT NULL;

4.流程控制函式

(1)if函式：if else的效果

SELECT IF(10>5, '大', '小');

SELECT last_name, commission_pct,IF(commission_pct IS NULL, '沒獎金','有獎金') 備註 FROM employees;

(2)case函式的使用一：switch case的效果

語法：

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函式的使用二：類似 多重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;

分組函式

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

分類：

  • sum 求和
  • avg 平均值
  • max 最大值
  • min 最小值
  • count 計算個數。

簡單使用：

SELECT SUM(salary) FROM employees;

SELECT AVG(salary) FROM employees;

SELECT COUNT(salary) FROM employees;

SELECT SUM(salary)和,AVG(salary)平均,MAX(salary)最高,MIN(salary)最低,COUNT(salary)個數 FROM employees;

特點：

  • sum和avg一般用於處理數值型
  • max、min、count可以處理任何資料型別
  • count的引數可以支援：欄位、*、【常量值，一般放1】，不過建議使用count(*)
    • SELECT COUNT(*) FROM employees;
    • 查詢部門編號為90的員工個數
      • SELECT COUNT(*) FROM employees WHERE department_id = 90;
  • 它們都忽略null值
  • 可以和distinct搭配實現去重的運算
    • SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
    • SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;
  • 和分組函式一同查詢的欄位要求是group by後的欄位。

分組查詢

語法：

select 分組函式,列(要求出現在group by的後面) from 表 【where 篩選條件】group by 分組的列表 【order by子句】

注意：

查詢列表必須特殊，要求是分組函式和group by後出現的欄位

簡單的分組查詢：

  • 案例1：查詢每個工種的最高工資。
    • SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
  • 案例2：查詢每個位置上的部門個數。
    • 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：查詢有獎金的每個領導手下員工的最高工資。
    • SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

【再進階】新增分組後的篩選條件(用到了HAVING)

  • 案例1：查詢哪個部門的員工個數>2。

    • SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;

  • 案例2：查詢每個工種有獎金的員工的最高工資>12000的工種編號和最高工資。

    • 思路
      • ①查詢每個工種有獎金的的員工的最高工資；
      • ②根據①的結果繼續篩選，最高工資>12000
    • SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;

  • 案例3：查詢領導編號>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) len_name
FROM employees
GROUP BY LENGTH(last_name);

    • ②新增篩選條件

      • SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

-- 或者這樣寫：

SELECT COUNT(*) cnt,LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING cnt>5;

-- 發現group by支援別名,按where不支援

按多個欄位進行分組

  • 案例：查詢每個部門每個工種的員工的平均工資。

    • SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id, job_id;

新增排序

  • 案例：查詢每個部門每個工種的員工的平均工資，並且按平均工資的高低顯示。

    • SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id, job_id
ORDER BY AVG(salary) DESC;

特點：

  • 分組查詢中的篩選條件分為兩類
    • 分組前篩選：其篩選源是原始表，放在group by子句的前面，用到了where關鍵字。
    • 分組後篩選：其篩選表是分組後的結果集合，放在group by子句的後面，用到了having關鍵字。
  • 分組函式做條件時肯定是放在having子句後面
  • 能用分組前篩選的，就優先考慮分組前篩選
  • group by子句支援單個欄位分組，多個欄位分組（多個欄位之間用逗號隔開，沒有順序要求)
  • 也可以新增排序(排序放在整個分組查詢的最後)

連線查詢

含義：

又成為多表查詢，當查詢的欄位來自多個表時，就會用到連線查詢。

分類【按年代分類】：

  • sql92標準：僅僅支援內連線
  • sql99標準：支援內連線+外連線(左外和右外)+交叉連線

分類【按功能分類】：

  • 內連線
    • 等值連線
    • 非等值連線
    • 自連線
  • 外連線
    • 左外連線
    • 右外連線
    • 全外連線
  • 交叉連線

sql92標準

1.等值連線

案例1：查詢女神名和對應的男神名。

SELECT NAME,boyName 
FROM boys, beauty
WHERE beauty.`boyfriend_id`=boys.id;

案例2：查詢員工名和對應的部門名。

SELECT last_name, department_name
FROM employees, departments
WHERE employees.`department_id`=departments.`department_id`;

案例3：查詢員工名、工種號、工種名。

-- 當emplyees,jobs表中都有job_id欄位時，需要指明它來自哪個表，否則會執行錯誤。
SELECT last_name, employees.job_id, job_title
FROM employees,jobs
WHERE employees.`job_id`=jobs.`job_id`;

我們也可以給表起別名，用不用as都可以，如下：

-- as可省略
SELECT last_name, e.job_id, job_title
FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`;

注意：如果為表起了別名，則查詢的欄位不能使用原來的表名去限定。

案例4：查詢有獎金的員工名、部門名。

SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

案例5：查詢城市中第二個字元為o的部門名和城市名。

SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';

案例6：查詢每個城市的部門個數。

SELECT city,COUNT(*) 個數
FROM locations l, departments d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;

案例7：查詢有獎金的每個部門的部門名和部門的領導編號和該部門的最低工資。

SELECT department_name,e.manager_id,MIN(salary)
FROM employees e, departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY d.`department_name`, d.`manager_id`;

案例8：查詢每個工種的工種名和員工的個數，並且按員工個數降序。

SELECT job_title,COUNT(*)
FROM employees e, jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY j.`job_title`
ORDER BY COUNT(*) DESC;

案例9：查詢員工名、部門名和所在的城市。

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`;

總結一下等值連線的特點：

  • 多表等值連線的結果為多表的交集部分；
  • n表連線，至少需要n-1個連線條件；
  • 多表的順序沒有要求；
  • 一般需要為表起別名；
  • 可以搭配前面介紹的所有子句，比如排序、分組、篩選。
2.非等值連線

案例：查詢員工的工資和工資級別。

SELECT salary,grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
3.自連線

案例：查詢員工名和上級的名字。

-- 可以把employees看成兩個表e, m，e是員工表，m是上級表。
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 欄位，...
from 表1 別名
【inner|left outer|right outer|cross】join 表2 別名 on  連線條件
【inner|left outer|right outer|cross】join 表3 on  連線條件
【where 篩選條件】
【group by 分組欄位】
【having 分組後的篩選條件】
【order by 排序的欄位或表示式】
分類
  • 內連線：inner
    • 等值連線
    • 非等值連線
    • 自連線
  • 外連線
    • 左外：left 【outer】
    • 右外：right【outer】
    • 全外：full【outer】
  • 交叉連線：cross
內連線
語法
select 查詢列表
from 表1 別名
inner join 表2 別名
on 連線條件;
1.等值連線

特點：

  • 可以新增排序、分組、篩選；
  • inner可以省略；
  • 篩選條件放在where後面，連線條件放在on後面，提高分離性，便於閱讀；
  • inner join連線和sql92語法中的等值連線效果是一樣的，都是查詢多表的交集。

案例1：查詢員工名、部門名

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;

案例2：查詢員工名和對應的部門名。（篩選）

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：查詢部門個數>3的城市名和部門個數。（新增分組+篩選）

-- 根據要求，查詢部門個數>3的城市名和部門個數，也就是說先按照城市名進行分組，然後再用部門個數>3這個條件進行篩選（這就用到了having）

SELECT city,COUNT(*) 部門個數
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

案例4：查詢部門的員工個數>3的部門名和員工個數，並按個數降序。（新增排序）

-- ①查詢每個部門的員工個數
select count(*),department_name
from employees
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
group by department_name
-- ②在①的結果上選員工數>3的記錄，並排序
SELECT department_name 部門名,COUNT(*)
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
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`
INNER JOIN jobs j ON e.`job_id`=j.`job_id`
ORDER BY department_name DESC;
2.非等值連線

案例1：查詢員工的工資級別。

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

案例2：查詢每個工資級別的個數>20的，並且按工資級別降序排序。

SELECT COUNT(*) 個數,grade_level
FROM employees e
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;
3.自連線

案例1：查詢員工的名字、上級的名字。

SELECT e.last_name 員工名,m.last_name 上級名
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

案例2：查詢姓名中包含字元k的員工的名字，以及對應的上級的名字。

SELECT e.last_name 員工名,m.last_name 上級名
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
外連線(這裡沒有介紹全外連線)

應用場景：用於查詢一個表中有，另一個表中沒有的記錄。

特點：

  • 外連線的查詢結果為主表中的所有記錄，如果從表中有和它匹配的，則顯示匹配的值；若沒有，則顯示null。即：外連線查詢結果=內連線結果+主表中有而從表中沒有的記錄。
  • 左外連線中，left join左邊的是主表；右外連線中，right join 右邊的是主表。

引入：查詢男朋友不在男神表的女神名。

SELECT b.name 女神名
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

或者

SELECT b.name 女神名
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

案例：查詢哪個部門沒有員工。

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;
交叉連線

就是使用sql99標準實現兩個表的笛卡爾乘積。如：

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

join連線總結

綜合案例

1.查詢編號>3的女神的男朋友資訊，如果有則列出詳細，如果沒有，用NULL填充。

-- 根據要求，查詢女神的男朋友資訊，如果沒有則用NULL填充，從這裡可以看出女神表beauty為主表，男神表boys為從表。
--下面使用左外連線 left左面的為主表
SELECT b.id,b.`name`,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3;

2.查詢哪個城市沒有部門。

-- 從要求中可以推斷出locations表為主表，departments表為從表，因為有些部門沒有和城市匹配。
-- 下面使用右外連線 right右面的為主表
SELECT city
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;

3.查詢部門名為SAL或IT的員工資訊。

-- 題目要求查詢部門名為指定內容的員工資訊，考慮到可能會出現有些部門沒有員工，為了查到全，所以這裡採用外連線，不採用內連線。部門表departments為主表，員工表employees為從表
-- 下面使用左外連線
SELECT e.*,d.department_name
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE d.`department_name` IN('SAL','IT');

子查詢

含義：

出現在其他語句中的select語句，稱為子查詢或內查詢。

外部的查詢語句，成為主查詢或外查詢。

分類：

  • 按子查詢出現的位置分類：
    • select後面
      • 僅僅支援標量子查詢
    • from後面
      • 支援表子查詢
    • where或having後面
      • 支援標量子查詢、列子查詢
      • 也支援行子查詢(不過用的較少)
    • exists後面（相關子查詢）
      • 支援表子查詢
  • 按結果集的行列數不同分類：
    • 標量子查詢（結果集只有一行一列）
    • 列子查詢（結果集只有一列多行）
    • 行子查詢（結果集有一行多列，也支援多列多行）
    • 表子查詢（結果集一般為多行多列，也就是說具有上面三個子查詢的特性）

where或having後面

特點：
  • 子查詢放在小括號內
  • 子查詢一般放在條件的右側
  • 標量子查詢，一般搭配著單行操作符使用。
    • 單行操作符比如：> < >= <= = <>
  • 列子查詢，一般搭配著多行操作符使用
    • 多行操作符比如：IN、ANY/SOME、ALL
  • 子查詢的執行優先於主查詢的執行，也就是說主查詢的條件用到了子查詢的結果。
where或having後面的標量子查詢（也稱為單行子查詢）使用

案例1：誰的工資比Abel高？

-- ①：查詢Abel的工資
SELECT salary
FROM employees 
WHERE last_name='Abel';
-- ②：查詢員工的資訊，滿足salary>①的結果
SELECT *
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees 
	WHERE last_name='Abel'
);

案例2：返回job_id與141號員工相同，salary比143號員工多的員工的姓名、job_id和工資。

-- ①查詢141號員工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
-- ②查詢143號員工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
-- ③查詢員工的資訊，要求job_id=①並且salary>②
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

案例3：返回公司工資最少的員工的last_name、job_id和salary

-- ①查詢公司的最低工資
SELECT MIN(salary)
FROM employees;
-- ②查詢員工的last_name、job_id和salary,要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

案例4：查詢最低工資大於50號部門最低工資的部門id和其最低工資。

-- ①:查詢50號部門的最低工資
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;
-- ②:查詢每個部門的最低工資
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id;
-- ③:在②的基礎上篩選，滿足min(salary)>①
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

非法使用標量子查詢情況，比如

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT salary
	FROM employees
	WHERE department_id = 50
);


-- 上面這個是非法使用，原因是子查詢的結果不是單行單列
where或having後面的列子查詢（也稱為多行子查詢）使用

案例1：返回location_id是1400或1700的部門中的所有員工姓名。

-- 1.先查詢location_id是1400或1700的部門編號
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1400 
OR location_id = 1700;
-- 2.查詢員工姓名，要求部門號是1列表中的某一個
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1400 
	OR location_id = 1700
);

案例2：返回其他工種中比job_id為IT_PROG工種任意一個工資低的員工的員工號、姓名、job_id以及salary。

-- 1.查詢job_id=IT_PROG部門的任一工資
SELECT DISTINCT salary
FROM employees 
WHERE job_id = 'IT_PROG';
-- 2.查詢員工號、姓名、job_id以及salary，並且salary<any(1中的任意一個)
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees 
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
-- 或者這麼寫
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < (
	SELECT DISTINCT MAX(salary)
	FROM employees 
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

案例3：返回其他工種中比job_id為IT_PROG工種所有工資都低的員工的員工號、姓名、job_id以及salary。

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees 
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
-- 或者這麼寫
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < (
	SELECT DISTINCT MIN(salary)
	FROM employees 
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
where或having後面的行子查詢（結果集為一行多列或多列多行)使用

案例：查詢員工編號最小並且工資最高的員工資訊。

-- 1.查詢最小的員工編號
SELECT MIN(employee_id)
FROM employees;
-- 2.查詢最高工資
SELECT MAX(salary)
FROM employees;
-- 3.查詢員工資訊
SELECT * 
FROM employees
WHERE employee_id = (
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);


-- 用行子查詢解決
SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

select後面

案例1：查詢每個部門的員工個數。

SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id  = d.`department_id`
) 個數
FROM departments d;

案例2：查詢員工號=102的部門名。

SELECT(
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department_id = e.department_id
	WHERE e.employee_id = 102
) 部門名;

from後面

案例：查詢每個部門的平均工資的工資等級。

-- 1.先查詢每個部門的平均工資
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
-- 2.連線1的結果集何job_grades表，篩選條件是平均工資在lowest_sal和highest_sal之間。
SELECT ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;


-- 發現from將子查詢結果充當一張表，要求必須起別名

exists後面（相關子查詢）

語法：

exists(完整的查詢語句);

最終結果只有兩種情況，1或0。

案例1：查詢有員工的部門名。

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


 -- 或使用in解決
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
);

案例2：查詢沒有女朋友的男神資訊。

-- 使用in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
);

-- 使用exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE b.`boyfriend_id`=bo.`id`
);

練習

案例1：查詢和Zlotkey相同部門的員工姓名和工資。

-- 1.查詢Zlotkey的部門
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey';

-- 2.查詢部門號等於1.的結果的姓名和工資
SELECT last_name, salary
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name = 'Zlotkey'
);

案例2：查詢工資比公司平均工資高的員工的員工號，姓名和工資。

-- 1.查詢公司的平均工資
SELECT AVG(salary)
FROM employees;
-- 2.查詢工資比1.的結果高的員工的員工號，姓名和工資
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
);

案例3：查詢各部門中工資比本部門平均工資高的員工的員工號，姓名和工資。

-- 首先需要讀懂題意，問的是各部門中工資大於本部門平均工資的員工的資訊，意思就是篩選處每個部門大於自身部門的平均工資的員工

-- 1.查詢各部門的平均工資
SELECT AVG(salary),department_id
FROM employees 
GROUP BY department_id;
-- 2.連線1.的結果集和employees表，進行篩選
SELECT employee_id,last_name,salary, e.department_id
FROM employees e
INNER JOIN(
	SELECT AVG(salary) ag,department_id
	FROM employees 
	GROUP BY department_id
)ag_dep
ON e.`department_id`=ag_dep.department_id
WHERE salary > ag_dep.ag;

案例4：查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名。

-- 1.查詢姓名中包含字母u的員工的部門號
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';
-- 2.查詢部門號=1.的結果集的任意一個員工的員工號和姓名
SELECT last_name,employee_id
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
);

案例5：查詢在部門的location_id為1700的部門工作的員工的員工號。

-- 1.查詢location_id為1700的部門
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700;
-- 2.查詢部門號=1.的結果集中的任意一個的員工號。
SELECT employee_id
FROM employees
WHERE department_id = ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1700
);
-- 按照這裡的題意，any換成in也行

案例6：查詢管理者是K_ing的員工姓名和工資。

-- 1.查詢姓名為K_ing的員工編號
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing';
-- 2.查詢哪個員工的manager_id = 1.的結果集的任意一個
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
	SELECT employee_id
	FROM employees
	WHERE last_name = 'K_ing'
);

案例7：查詢工資最高的員工的姓名，要求first_name和last_name顯示為一列，列名為姓，名。

-- 1.查詢最高工資
SELECT MAX(salary)
FROM employees;
-- 2.查詢工資=1的結果集的姓、名
SELECT CONCAT(first_name,last_name) "姓名"
FROM employees
WHERE salary=(
	SELECT MAX(salary)
	FROM employees
);

經典案例

案例1：查詢工資最低的員工資訊: last_name, salary 。

-- 一、查詢最低工資
SELECT MIN(salary)
FROM employees;
-- 二、查詢last_name,salary，要求salary=一、的結果
SELECT last_name,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

案例2：查詢平均工資最低的部門資訊 。

-- 一、查詢各部門的平均工資
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
-- 二、查詢一、結果中的最低平均工資
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep;
-- 三、查詢哪個部門的平均工資=二、的結果
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	) ag_dep
);
-- 四、查詢部門資訊
SELECT d.*
FROM departments d
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep
	)
);

或者

-- 一、求出最低平均工資的部門編號
SELECT department_id
FROM employees
GROUP BY department_id
order by avg(salary)
limit 1;
-- 二、查詢部門資訊
SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
);

案例3：查詢平均工資最低的部門資訊和該部門的平均工資 。

-- 一、查詢各部門的平均工資
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
-- 二、求出最低平均工資的部門編號
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
order by avg(salary)
limit 1;
-- 三、查詢部門資訊(這裡用到了表子查詢)
SELECT d.*
FROM departments d
JOIN (
	SELECT AVG(salary),department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
)ag_dep
ON d.`department_id`=ag_dep.department_id;

案例4：查詢平均工資最高的 job 資訊 。

-- 一、查詢每個job的平均工資
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1;
-- 二、查詢job資訊
SELECT * 
FROM jobs
WHERE jobs.`job_id` = (
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

案例5：查詢平均工資高於公司平均工資的部門有哪些?

-- 一、查詢公司的平均工資
SELECT AVG(salary)
FROM employees;
-- 二、查詢每個部門的平均工資
SELECT AVG(salary)
FROM employees
GROUP BY department_id;
-- 三、篩選二、的結果集，滿足平均工資>一的結果

案例6：查詢出公司中所有 manager 的詳細資訊。

-- 一、查詢所有manager的員工編號
SELECT DISTINCT manager_id
FROM employees;
-- 二、查詢詳細資訊，滿足employee_id=1的結果
SELECT *
FROM employees
WHERE employee_id = ANY(
	SELECT DISTINCT manager_id
	FROM employees
);

案例7：各個部門中 最高工資中最低的那個部門的 最低工資是多少 。

-- 一、查詢各部門的最高工資
SELECT MAX(salary)
FROM employees
GROUP BY department_id;
-- 二、找一、結果集中最低的那個
SELECT MAX(salary)
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1;
-- 三、查詢哪個部門的最高工資等於二、結果，
SELECT MIN(salary),department_id
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

案例8：查詢平均工資最高的部門的 manager 的詳細資訊: last_name, department_id, email,
salary 。

-- 一、找到平均工資最高的部門編號
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;
-- 二、將employees和departments連線查詢，篩選條件是一、的結果
SELECT last_name,d.department_id,email,salary
FROM employees e
INNER JOIN departments d 
ON d.`manager_id` = e.`employee_id`
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

分頁查詢

應用場景：當要顯示的資料，一頁顯示不全時，需要分頁提交sql請求。

語法：

select 查詢列表
from 表
【join type join 表2
on 連線條件
where 篩選條件
group by 分組欄位
having 分組後的篩選
order by 排序的欄位】
limit offset,size;

-- offset表示要顯示條目的起始索引（起始索引從0開始）
-- size表示要顯示的條目個數



--執行順序：from表先走，再inner join on,再去篩選，再去group by，再去having，再走select，然後order by，最後執行limit

特點：

  • limit語句放在查詢語句的最後；

  • 在web開發的分頁顯示中會用到下面的公式：

    • 要顯示的頁數page，每頁的條目數size

    • select 查詢列表
from 表
limit (page-1)*size,size;

案例1：查詢前5條員工資訊。

SELECT * FROM employees LIMIT 0,5;

案例2：查詢第11條到第25條員工資訊。

SELECT * FROM employees LIMIT 10,15;

案例3：查詢有獎金的員工資訊，並且工資較高的前10名顯示出來。

SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;

union聯合查詢

union聯合，合併：將多條查詢語句的結果合成一個結果。

語法：

查詢語句1
union
查詢語句2
union
...

引入的案例：查詢部門編號>90或郵箱中包含a的員工資訊。

SELECT * FROM employees WHERE email LIKE '%a%'
UNION 
SELECT * FROM employees WHERE department_id>90;

應用場景：

要查詢的結果來自於多個表，且多個表沒有直接的連線關係，但查詢的資訊一致（表示列欄位意義差不多）時。

特點：

  • 要求多條查詢語句的查詢列數是一致的。
  • 要求多條查詢語句的查詢的每一列的型別和順序最好一致。
  • union關鍵字預設去重，如果使用union all，就可以包含重複項。

十、DML語言的學習

插入語句

語法：

-- 方式一、
insert into 表名(欄位名，...)
values(值1，...);

-- 方式二、
insert into 表名
set 列名=值,列名=值,...

-- 方式一支援插入多行資料，方式二不支援
-- 方式一支援子查詢，方式二不支援

特點：

  • 欄位型別和值型別一致或相容，而且一一對應；
  • 可以為空的欄位，可以不用插入值，或用null填充；
  • 不可以為空的欄位，必須插入值；
  • 欄位個數和值的個數必須一致；
  • 欄位可以省略，但預設所有欄位，並且順序和表中的儲存順序一致；

修改語句

語法：

-- 修改單表語法：
update 表名 set 欄位=新值,欄位=新值
【where 條件】

-- 修改多表語法【補充】：
-- sql92語法
update 表1 別名1,表2 別名2
set 欄位=新值，欄位=新值
where 連線條件
and 篩選條件
--sql99語法
update 表1 別名
inner|left|right join 表2 別名
on 連線條件
set 列=值,...
where 篩選條件

修改多表記錄案例：

案例1：修改張無忌的女朋友的手機號為114。

UPDATE boys bo
INNER JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='114'
WHERE bo.`boyName`='張無忌';

案例2：修改沒有男朋友的女神的男朋友編號為2號。

UPDATE boys bo
RIGHT JOIN beauty b 
ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;

刪除語句

語法：

--方式一：delete語句
-- 單表刪除
delete from 表名 【where 篩選條件】
-- 不加where的時候會清空該表

-- 多表刪除【補充】
--sql92語法
delete 別名1，別名2
from 表1 別名1，表2 別名2
where 連線條件
and 篩選條件;
--sql99語法
delete 表1的別名,表2的別名
from 表1 別名
inner|left|right join 表2 別名
where 篩選條件;

--方式二：truncate語句（不允許加where）
truncate table 表名
--執行它後會清空該表

兩種方式的區別：

  • truncate不能加where條件，而delete可以加where條件
  • truncate的效率高一丟丟
  • truncate 刪除帶自增長的列的表後，如果再插入資料，資料從1開始；delete 刪除帶自增長列的表後，如果再插入資料，資料從上一次的斷點處開始
  • truncate刪除不能回滾，delete刪除可以回滾

多表的刪除案例：

  • 案例：刪除張無忌的女朋友資訊。

    • DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='張無忌';

  • 案例：刪除黃曉明的資訊以及他女朋友的資訊。

    • DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黃曉明';

十一、DDL語言的學習

也稱為資料定義語言，包括庫和表的管理。

  • 庫的管理
    • 建立
    • 修改
    • 刪除
  • 表的管理
    • 建立
    • 修改
    • 刪除

建立：create；

修改：alter；

刪除：drop。

庫的管理

1.庫的建立

create database [if not exists]庫名;

案例：建立庫Books。

-- 加IF NOT EXISTS是為了避免當存在那個庫時再建立會報錯
CREATE DATABASE IF NOT EXISTS books;

2.庫的修改

更改庫的字符集

ALTER DATABASE books CHARACTER SET utf8;

3.庫的刪除

-- 加if exists是為了避免刪除不存在的庫報錯
drop database [if exists] 庫名

案例：刪除庫Books。

DROP DATABASE IF EXISTS books;

表的管理

1.表的建立

語法：

create table 表名(
	列名 列的型別【(長度)約束】,
    列名 列的型別【(長度)約束】,
	列名 列的型別【(長度)約束】,
    ...
    列名 列的型別【(長度)約束】
);

案例：建立表Book。

-- 加IF NOT EXISTS是為了增強容錯性
CREATE TABLE IF NOT EXISTS book(
	id INT,#編號
	bName VARCHAR(20),#圖書名
	price DOUBLE,#價格
	authorId INT,#作者
	publishDate DATETIME #出版日期
);

案例：建立表author。

CREATE TABLE author(
	id INT,#編號
	au_name VARCHAR(20),
	nation VARCHAR(10) #國籍
);

2.表的修改

語法：

alter table 表名 add|drop|modify|change column 欄位名【欄位型別 約束】;

作用範圍：

  • 可以修改列名

    • 將表book的publishdate欄位修改成pubDate，注意後面還要加上型別。

    • ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

  • 修改列的型別或約束

    • -- 注意它不區分大小寫
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

  • 新增新列

    • -- 注意型別也要寫上
ALTER TABLE author ADD COLUMN annual DOUBLE;

  • 刪除列

    • ALTER TABLE author DROP COLUMN annual;

  • 修改表名

    • ALTER TABLE author RENAME TO book_author;

3.表的刪除

-- 加IF EXISTS是為了容錯性
DROP TABLE IF EXISTS book_author;

表的複製

1.僅僅複製表的結構

-- 建立一個copy表，複製author表的結構
CREATE TABLE copy LIKE author;

2.複製表的結構+資料

CREATE TABLE copy2
SELECT * FROM author;

只複製部分資料:

CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中國';

僅僅複製某些欄位(不復制資料)：

CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 1=2;
-- 或
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;

常見的資料型別

  • 數值型
    • 整型
    • 小數
      • 定點數
      • 浮點數
  • 字元型
    • 較短的文字：char、varchar
    • 較長的文字：text、blob(較長的二進位制資料)
  • 日期型

1.整型

特點：

  • 如果不設定無符號還是有符號，預設時有符號，如果想設定無符號，則需要新增unsigned 關鍵字。

  • 如果插入的數值超出了整型的範圍，會報out of range異常，並且插入臨界值。

  • 如果不設定長度，會有預設的長度。

  • 長度代表了顯示的最大寬度，如果不夠會用0在左邊填充，但在建立表時需要搭配zerofill使用。如：

    • CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) ZEROFILL 
);
-- 但這樣就不能插入負數了

案例：如何設定無符號和有符號。

CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED
);

2.小數

浮點型：

float(M,D)

double(M,D)

定點型：

dec(M,D)

decimal(M,D)

特點：

  • M代表整數部位+小數部位的長度
  • D代表小數部位的長度
  • 如果超出範圍，則插入臨界值
  • 如果沒有特殊要求的話，M和D可以省略
  • 如果是decimal，則M預設為0，D預設為0
  • 如果是float和double，則會根據插入的數值的精度來決定精度.
  • 定點型的精確度較高，如果要求插入數值的精度較高比如貨幣運算，則用定點型的。

原則：

所選擇的型別越簡單越好，能儲存數值的型別越小越好。

3.字元型

  • 較短的文字：char、varchar
  • 較長的文字：text、blob(較長的二進位制資料)
  • 其他：
    • binary和varbinary用於儲存較短的二進位制
    • enum用於儲存列舉
    • set用於儲存集合

char代表固定長度的字元，varchar代表可變長度的字元。可以和char、string類比。

4.日期型

常見約束

約束的含義：

一種限制，用於限制表中的資料，為了保證表中的資料的準確和可靠性。

分類：

  • NOT NULL ：非空，用於保證該欄位的值不能為空，比如姓名、學號等；
  • DEFAULT：預設，用於保證該欄位有預設值，比如性別；
  • PRIMARY KEY：主鍵，用於保證該欄位的值具有唯一性，並且非空，比如學號、員工編號等；
  • UNIQUE：唯一，用於保證該欄位的值具有唯一性，可以為空，比如座位號；
  • CHECK：檢查約束【mysql中不支援】
  • FOREIGN KEY：外來鍵，用於限制兩個表的關係，用於保證該欄位的值必須來自於主表的關聯列的值。
    • 注意是在從表中新增外來鍵約束，用於引用主表中某列的值。比如學生表的專業編號，員工表的部門編號，員工表的工種編號。
    • 從表的外來鍵列的型別要求和主表中對應的列的型別一致。名稱無要求。
    • 主表的關聯列必須是一個key(一般是主鍵、唯一鍵)
    • 插入資料時，先插入主表，再插入從表的資料。刪除資料時先刪除從表，再刪除主表。

新增約束的時機：

  • 建立表時
  • 修改表時

約束的新增分類：

  • 列級約束
    • 六大約束語法上都支援，但外來鍵約束沒有效果；
  • 表級約束
    • 除了非空(NOT NULL)、預設(DEFAULT)，其他的都支援；

建立表時新增約束

1.新增列級約束

語法：

  • 直接在欄位名和型別後面追加約束型別即可。
  • 只支援預設、非空、主鍵、唯一。
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

--儘管這裡寫了外來鍵約束，但由於在列級約束中外來鍵約束沒有效果，故這裡沒有效果。
--這個check在mysql不支援
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主鍵
	stuName VARCHAR(20) NOT NULL,#非空約束
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#檢查約束
	seat INT UNIQUE,#唯一約束
	age INT DEFAULT 18,#預設約束
	majorId INT REFERENCES major(id)#外來鍵約束
);

-- 檢視stuinfo表中所有的索引，包括主鍵、外來鍵、唯一
SHOW INDEX FROM stuinfo;
2.新增表級約束

語法：

在各個欄位的最下面

【constraint 約束名】 約束型別(欄位名)

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	CONSTRAINT pk PRIMARY KEY(id),#主鍵
	CONSTRAINT uq UNIQUE(seat),#唯一鍵
	CONSTRAINT ck CHECK(genger = '男' OR gender = '女'),#檢查約束
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外來鍵

);

-- 或者這樣寫

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	PRIMARY KEY(id),#主鍵
	UNIQUE(seat),#唯一鍵
	CHECK(genger = '男' OR gender = '女'),#檢查約束
	FOREIGN KEY(majorid) REFERENCES major(id)#外來鍵
);
3.通用的寫法：

也就是說可以用列級約束的那樣子寫的就用列級約束寫，否則就用表級約束去寫。

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	studname VARCHAR(20) NOT NULL,
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

修改表時新增約束

語法
-- 1. 新增列級約束
alter table 表名 modify column 欄位名 欄位型別 新約束
-- 2. 新增表級約束
alter table 表名 add 【constraint 約束名】 約束型別(欄位名) 【外來鍵的引用】
1.新增非空約束
-- 這裡為了舉例，先重新建立一個表
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
);

-- 將stuname欄位新增非空約束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
2.新增預設約束
-- 將age欄位新增預設約束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
3.新增主鍵
-- 將id欄位新增主鍵約束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
--或者這麼寫
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
4.新增唯一
-- 將seat欄位新增唯一
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
-- 或者這麼寫
ALTER TABLE stuinfo ADD UNIQUE(seat);
5.新增外來鍵
-- 為majorid欄位新增外來鍵約束，與major表中的id欄位關聯
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

修改表時刪除約束

1.刪除非空約束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
2.刪除預設約束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
3.刪除主鍵
ALTER TABLE stuinfo DROP PRIMARY KEY;
4.刪除唯一
ALTER TABLE stuinfo DROP INDEX seat;
-- index後面要寫什麼需要先執行show index from stuinfo 檢視對應的Key_name是什麼,填到index後面
5.刪除外來鍵
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
-- foreign key 後面要寫什麼需要先執行show index from stuinfo 檢視對應的Key_name是什麼,填到key後面

標識列

又稱為自增長列，可以不用手動的新增值，系統提供預設的序列值。

特點

  • 標識列必須和主鍵搭配嗎？------> 不一定，但要求是一個key，比如主鍵、唯一、外來鍵;
  • 一個表中可以有幾個標識列？-------> 至多一個;
  • 標識列的型別必須是數值型別;
  • 標識列可以通過SET auto_increment_increment=3;設定步長。也可以通過手動插入值來設定起始值。

建立表時設定標識列

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,		-- 看AUTO_INCREMENT
	NAME VARCHAR(20)
);

INSERT INTO tab_identity VALUES(NULL,'join');
INSERT INTO tab_identity(NAME) VALUES('tom');

SELECT * FROM tab_identity;

修改表時設定標識列

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT,
	NAME VARCHAR(20)
);

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

修改表時刪除標識列

ALTER TABLE tab_identity MODIFY COLUMN id INT;

十二、TCL語言的學習

相關概念介紹

Transaction Control Language：事務控制語言。

事務：

一個或一組sql語句組成一個執行單元，這個執行單元要麼全部執行，要麼全部不執行。

通過一組邏輯操作單元（一組DML——sql語句），將資料從一種狀態切換到另外一種狀態

案例：轉賬。

初始時
張三丰 1000
郭襄   1000
update 表 set 張三丰的餘額=500 where name='張三丰'
update 表 set 郭襄的餘額=1500 where name='郭襄'

事務的ACID(acid)屬性：

  • 原子性：要麼都執行，要麼都回滾；
  • 一致性：保證資料的狀態操作前和操作後保持一致；
  • 隔離性：多個事務同時操作相同資料庫的同一個資料時，一個事務的執行不受另外一個事務的干擾；
  • 永續性：一個事務一旦提交，則資料將持久化到本地，除非其他事務對其進行修改；

事務的分類：

  • 隱式事務：沒有明顯的開啟和結束事務的標誌。

    • 比如insert、update、delete語句本身就是一個事務

  • 顯式事務：具有明顯的開啟和結束事務的標誌。

    • 前提：必須先設定自動提交功能(autocommit)為禁用。SET autocommit=0;不過只針對當前的事務有效。可以先執行SHOW VARIABLES LIKE '%autocommit%';檢視一下是否已經被禁用。

    • 步驟一：開啟事務

      • SET autocommit=0;
START TRANSACTION;	#可選

    • 步驟二：編寫事務的一組邏輯操作單元（多條sql語句[比如select、insert、update、delete]）

      • 語句1;
語句2;
...

    • 步驟三：結束事務

      • commit;	#提交事務
rollback;#回滾事務
savepoint 節點名;	#設定儲存點，聯想一下斷點

演示事務的使用步驟

  • 準備表並插入資料：

    • DROP TABLE IF EXISTS account;

CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);

INSERT INTO account(username,balance)
VALUES ('張無忌',1000),('郭襄',1000);

  • 開啟事務

    • SET autocommit = 0;
START TRANSACTION;	#可選

  • 編寫一組事務的語句

    • UPDATE account SET balance = 500 WHERE username='張無忌';
UPDATE account SET balance = 1500 WHERE username='郭襄';

  • 結束事務

    • COMMIT;
--如果寫rollback放在commit之前，或者不寫commit，會導致資料不會發生改變

delete 和 truncate 在事務使用時的區別：

  • 演示delete(支援回滾)

    • SET autocommit = 0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
-- 回滾後發現沒有刪除

  • 演示truncate(不支援回滾)

    • SET autocommit = 0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

事務的隔離級別

事務併發問題如何發生？

當多個事務同時操作同一個資料庫的相同資料時

事務的併發問題有哪些？

  • 髒讀：一個事務讀取了其他事務還沒有提交的資料，讀到的是其他事務“更新”的資料;
  • 不可重複讀：同一個事務中，多次讀取到的資料不一致;
  • 幻讀：一個事務讀取了其他事務還沒有提交的資料，只是讀到的是 其他事務“插入”的資料。

如何避免事務的併發問題？

通過設定事務的隔離級別來避免，隔離級別有

  • READ UNCOMMITTED(讀未提交資料)允許事務讀取未被其他事務提交的變更，髒讀、不可重複讀和幻讀的問題都會出現。
  • READ COMMITTED(讀已提交資料) 只允許事務讀取已經被其他事務提交的變更，可以避免髒讀，但不可重複讀和幻讀仍然可能出現。
  • REPEATABLE READ(可重複讀) 確保事務可以多次從一個欄位中讀取相同的值，在這個事務持續期間，禁止其他事務對這個欄位進行更新，可以避免髒讀、不可重複讀和一部分幻讀，但幻讀的問題依舊存在。
  • SERIALIZABLE(序列化) 確保事務可以從一個表中讀取相同的行，在這個事務持續期間，禁止其他事務對該表執行插入、更新和刪除操作，所有併發問題都可以避免，但效能十分低， 可以避免髒讀、不可重複讀和幻讀。

演示事務的隔離級別

-- 用cmd命令視窗(管理員模式)操作

-- 檢視當前的隔離級別
select @@tx_isolation;

-- 設定隔離級別
set session transaction isolation level 隔離級別名稱;

-- 運算元據庫
use 資料庫名;

-- 開啟事務
SET autocommit = 0;

-- 編寫sql語句

-- 結束事務
-- rollback; 寫rollback或commit，根據具體情況決定
commit;

演示savepoint的使用

SET autocommit = 0;
START TRANSACTION;		#在客戶端中一般使用這條，在控制檯裡不需要

DELETE FROM account WHERE id = 1;
SAVEPOINT a; #設定儲存點
DELETE FROM account WHERE id = 3;
ROLLBACK TO a;	#回滾到儲存點,這樣就導致id=1的資料刪除，id=3的資料沒有刪除

SELECT * FROM account;

十三、檢視

含義：

虛擬表，和普通表一樣使用。是mysql5.1版本中出現的新特性，是通過表動態生成的資料。它只儲存sql邏輯，不儲存查詢結果。

案例：查詢姓張的學生名和專業名。

-- 以前是這樣寫
SELECT stuname,majorName
FROM stuinfo s
INNER JOIN major m
ON s.`majorid`=m.`id`
WHERE s.`stuname` LIKE '張%';

-- 學了檢視後可以將主要部分封裝起來
CREATE VIEW v1
AS
SELECT stuname,majorName
FROM stuinfo s
INNER JOIN major m
ON s.`majorid`=m.`id`;
--再去使用它
SELECT * FROM v1 WHERE stuname LIKE '張%';

檢視和表的區別

建立語法的關鍵字 佔用物理空間 使用
檢視 create view 不佔用，僅僅儲存的是sql邏輯 增刪改查，一般不能增刪改
表 create table 佔用 增刪改查

檢視的建立

語法：

CREATE VIEW  檢視名
AS
查詢語句;

案例1：查詢姓名中包含a字元的員工名、部門名和工種資訊。

-- 建立檢視
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id;
-- 使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';

案例2：查詢各部門的平均工資級別。

# 建立檢視，檢視每個部門的平均工資
CREATE VIEW myv2
AS 
SELECT AVG(salary) ag, department_id
FROM employees 
GROUP BY department_id;
# 使用
SELECT myv2.`ag`, g.grade_level  
FROM myv2 
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

案例3：查詢平均工資最低的部門資訊。

#前面已經建立過檢視myv2，這裡就不需要再建立

# 使用
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

案例4：查詢平均工資最低的部門名和工資。

# 建立檢視myv3,將包含檢視myv2的sql操作再次封裝
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
# 使用
SELECT d.*, m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;

檢視的修改

語法：

-- 方式一
creat or replace view 檢視名
as
查詢語句;

-- 方式二
alter view 檢視名
as 
查詢語句;

檢視的刪除

語法：

drop view 檢視名,檢視名,...;

檢視的檢視

-- 這裡說的檢視是指檢視它的結構
desc 檢視名;

檢視的更新

注意這裡的更新是指更改檢視中的資料。

其餘的就可以，語法和普通表的一樣。更新包括插入、刪除、修改。

案例

案例1：建立檢視emp_v1，要求查詢電話號碼以011開頭的員工姓名和工資、郵箱。

CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees e
WHERE phone_number LIKE '011%';

SELECT * FROM emp_v1;

案例2：建立檢視emp_v2，要求查詢部門的最高工資高於12000的部門資訊。

CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx, department_id
FROM employees
GROUP BY department_id
HAVING mx > 12000;

SELECT d.*, e.mx
FROM emp_v2 e
INNER JOIN departments d
ON d.`department_id` = e.department_id;

小測試

CREATE TABLE bookType(
	id INT PRIMARY KEY,
	NAME VARCHAR(20)
);

CREATE TABLE Book(
	bid INT PRIMARY KEY,
	bname VARCHAR(32) UNIQUE NOT NULL,
	price DOUBLE DEFAULT 10,
	bypeId INT,
	CONSTRAINT fk_book_booktype FOREIGN KEY(bypeId) REFERENCES bookType(id)
);

SET autocommit = 0;
START TRANSACTION;
#先插入主表中資料
INSERT INTO bookType VALUES
(2333, '水滸');
#再插入從表中資料
INSERT INTO Book VALUES
(1, '張飛', 100000, 2333);
COMMIT;

CREATE VIEW myv1
AS
SELECT bname, NAME
FROM book bo
INNER JOIN booktype b 
ON bo.bypeId=b.id
WHERE price > 100;

SELECT * FROM myv1;

ALTER VIEW myv1
AS
SELECT bname, price
FROM book
WHERE price BETWEEN 90 AND 120;

SELECT * FROM myv1;

DROP VIEW myv1;

十四、變數

分類

  • 系統變數（按照作用範圍劃分）
    • 全域性變數
      • 作用域：伺服器每次啟動將為所有的全域性變數賦初始值。針對於所有的會話(連線)有效，但是不能跨重啟，就是重啟前的設定，在重啟後就恢復預設。
    • 會話變數
      • 作用域：僅僅針對於當前會話(連線)有效。
  • 自定義變數（按照作用範圍劃分）
    • 使用者變數
      • 作用域：針對於當前會話(連線)有效，同於會話變數的作用域。應用在任何地方，也就是begin end裡面或者begin end外面。
    • 區域性變數
      • 作用域：僅僅在定義它的begin end中有效。應用在begin end中的第一句話。

系統變數

說明：

變數由系統提供，不是使用者定義，屬於伺服器層面。

使用的語法：

  • 1.檢視所有的系統變數

    • -- 檢視全域性變數
SHOW GLOBAL VARIABLES;
-- 檢視會話變數(session可以不寫,預設是session)
SHOW SESSION VARIABLES;

  • 2.檢視滿足條件的部門系統變數

    • SHOW GLOBAL VARIABLES LIKE '%char%';

  • 3.檢視指定的某個系統變數的值

    • -- 如果這樣寫，預設是從會話變數中查詢
SELECT @@系統變數名;
-- 如果想從全域性變數中找
SELECT @@global.系統變數名;

  • 4.為某個系統變數複製

    • set 系統變數名 = 值;
set global 系統變數名 = 值;

set @@global.系統變數名=值;
set @@session.系統變數名=值;

  • 注意：如果是全域性級別，則需要加global，如果是會話級別，則可以加，也可以不加session。不寫預設是session。

全域性變數的演示

1.檢視所有的全域性變數：

SHOW GLOBAL VARIABLES;

2.檢視部分的全域性變數：

-- 檢視包含char的全域性變數
SHOW GLOBAL VARIABLES LIKE '%char%';

3.檢視指定的全域性變數的值：

-- 檢視autocommit的值
SELECT @@global.autocommit;
-- 檢視隔離級別
SELECT @@global.tx_isolation;

4.為某個指定的全域性變數賦值

SET @@global.autocommit=0;

會話變數的演示

1.檢視所有的會話變數

SHOW SESSION VARIABLES;
-- 或者
SHOW VARIABLES;

2.檢視部分的會話變數

SHOW SESSION VARIABLES LIKE '%char%';
-- 或者
SHOW VARIABLES LIKE '%char%';

3.檢視指定的會話變數的值

SELECT @@tx_isolation;
-- 或者
SELECT @@session.tx_isolation;

4.為某個會話變數賦值

SET @@session.tx_isolation = 'read-uncommitted';
-- 或者
SET SESSION tx_isolation = 'read-committed';

自定義變數

說明：

變數是由使用者自定義的，不是由系統提供的。

使用步驟：

宣告
賦值
使用(檢視、比較、運算等)

使用者變數的演示

1.宣告並初始化

-- 下面三種方式都可以
SET @使用者變數名=值;
SET @使用者變數名:=值;
SELECT @使用者變數名:=值;

2.賦值(更新使用者變數的值)

-- 方式一：通過set或select
SET @使用者變數名=值;
SET @使用者變數名:=值;
SELECT @使用者變數名:=值;
-- 方式二：通過select into
SELECT 欄位 INTO @變數名
FROM 表;

-- 例如：檢視employees表中的數量
SELECT COUNT(*) INTO @count
FROM employees;

3.使用(檢視使用者變數的值)

SELECT @使用者變數名;

-- 例如：
SELECT @count;

區域性變數的演示

1.宣告

-- 只宣告
DECLARE 變數名 型別;
-- 宣告並初始化
DECLARE 變數名 型別 DEFAULT 值;

2.賦值

-- 方式一：通過set或select
SET 區域性變數名=值;
SET 區域性變數名:=值;
SELECT @區域性變數名:=值;
-- 方式二：通過select into
SELECT 欄位 INTO 區域性變數名
FROM 表;

3.使用

SELECT 區域性變數名;

對比使用者變數和區域性變數

作用域 定義和使用的位置 語法
使用者變數 當前會話 當前會話中的任何地方 必須加上@符號，不用限定型別
區域性變數 begin end中 只能在begin end中，且為第一句話 一般不加@符號，需要限定型別

案例：宣告兩個變數並賦初值，求和，並列印

1.用使用者變數來做
SET @m = 1;
SET @n = 2;
SET @sum = @m + @n;
SELECT @sum;
2.用區域性變數來做

十五、儲存過程和函式

儲存過程

含義：

一組經過預先編譯的sql語句的集合，理解成批處理語句。

好處：

  • 提高了sql語句的重用性，減少了開發程式設計師的壓力；
  • 簡化操作；
  • 減少了編譯次數並且減少了和資料庫伺服器的連線次數，提高了效率；

語法：

1.建立儲存過程語法
CREATE PROCEDURE 儲存過程名(引數列表)
BEGIN 
	儲存過程體(一組合法的SQL語句)
END

注意：

1.引數列表包含三部分，分別是引數模式、引數名、引數型別。

舉例：

in stuname varchar(20)

引數模式：

  • IN：該引數可以作為輸入，也就是說該引數需要呼叫方傳入值；
  • OUT：該引數可以作為輸出，也就是該引數可以作為返回值；
  • INOUT：該引數既可以作為輸入，又可以作為輸出，也就是該引數既可以傳入值，又可以返回值。

2.如果儲存過程體僅僅只有一句話，BEGIN END可以省略。

3.儲存過程體中的每條sql語句的結尾都要加分號。儲存過程的結尾可以使用DELIMITER重新設定。

語法：

DELIMITER 結束標記
-- 如 DELIMITER $
2.呼叫儲存過程語法
CALL 儲存過程名(實參列表);
3.刪除儲存過程語法
DROP PROCEDURE 儲存過程名

舉例：

DROP PROCEDURE test_pro3;
4.檢視儲存過程的資訊
SHOW CREATE PROCEDURE test_pro3;

空參列表

案例：

插入到admin表中五條記錄。

-- 注意在cmd命令列視窗管理員模式下進行
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username, `password`) 
	VALUES('join1', '0000'),
	('lily', '0000'),
	('tom', '0000'),
	('zz', '0000'),
	('dd', '0000');
END $

# 呼叫
CALL myp1() $

建立帶in模式引數的儲存過程

案例1：

建立儲存過程實現，根據女神名，查詢對應的男神資訊。

DELIMITER $			#確定$符號為結束標記
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN 
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
 
END $

# 呼叫
CALL myp2('劉巖') $
案例2：

建立儲存過程實現，使用者是否登入成功。

DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN 
	DECLARE result INT DEFAULT 0; #變數宣告並初始化
	
	SELECT COUNT(*) INTO result	#變數賦值
	FROM admin a
	WHERE a.username = username
	AND a.password = PASSWORD;
	
	SELECT IF(result>0, '成功', '失敗');	#變數使用
END $
#呼叫
CALL myp3('john', '1000') $

建立帶out模式的儲存過程

案例1：

根據女神名，返回對應的男神名。

DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
	
	
END $

SET @bName$		#宣告使用者變數
#呼叫
CALL myp4('張飛',@bName)$

SELECT @bName$
案例2：

根據女神名，返回對應的男神名和男神魅力值。

DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN 
	SELECT bo.boyName, bo.userCP INTO boyName, userCP
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#呼叫
CALL myp5('張飛',@bName, @usercp) $
SELECT @bName,@usercp$

建立帶inout模式的儲存過程

案例：

傳入a和b兩個值，最終a和b都翻倍並返回。

DELIMITER $
CREATE PROCEDURE myp6(INOUT a INT, INOUT b INT)
BEGIN
	SET a = a * 2;	#這裡a和b都是區域性變數，設定值時不用加@
	SET b = b * 2;
END $

-- 呼叫的時候需要先建立兩個變數
SET @a = 10$
SET @b = 20$
CALL myp6(@a, @b)$
SELECT @a, @b$

案例

案例1：建立儲存過程實現傳入使用者名稱和密碼，插入到admin表中。

CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN loginPwd VARCHAR(20))
BEGIN 
	INSERT INTO admin(admin.username, PASSWORD)
	VALUES(username,loginPwd);
END $
#呼叫
CALL test_pro1('張飛','123123')$

案例2：建立儲存過程或函式實現傳入女神編號，返回女神名稱和女神電話。

DELIMITER $
CREATE PROCEDURE test_pro2(IN id INT, OUT gname VARCHAR(20), OUT phone VARCHAR(20))
BEGIN 
	SELECT b.`name`, b.`phone` INTO gname, phone
	FROM beauty b
	WHERE b.`id` = id;
END $
#呼叫
CALL test_pro2(1, @a, @b)$
SELECT @a, @b$

案例3：建立儲存過程或函式實現傳入兩個女神生日，返回大小。

DELIMITER $
CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)
BEGIN 
	SELECT DATEDIFF(birth1,birth2) INTO result;
END $

CALL test_pro3('1998-1-1',NOW(),@result)$
SELECT @result $

案例4：建立儲存過程或函式實現傳入一個日期，格式化成xx年xx月xx日並返回。

DELIMITER $
CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
	SELECT DATE_FORMAT(mydate, '%y年%m月%d日') INTO strDate;
END $

CALL test_pro4(NOW(), @str)$
SELECT @str $

案例5：建立儲存過程或函式實現傳入女神名稱，返回：女神 and 男神 格式的字串

DELIMITER $
CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(50), OUT str VARCHAR(100))
BEGIN 
	SELECT CONCAT(beautyName, ' and ', IFNULL(boyName, 'null')) INTO str
	FROM boys bo
	RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
	WHERE b.name = beautyName;
END $

CALL test_pro5('xx', @str) $
SELECT @str $

案例6：建立儲存過程或函式，根據傳入的條目數和起始索引，查詢beauty表中的記錄。

DELIMITER $
CREATE PROCEDURE test_pro6(IN size INT, IN startIndex INT)
BEGIN
	SELECT * FROM beauty LIMIT startIndex, size;
END$
CALL test_pro6(3, 5) $

函式

含義：

與儲存過程一樣。

與儲存過程的區別：

儲存過程可以有0個返回，也可以有多個返回， 適合做批量插入、批量更新；而函式必須且只能有1個返回，適合做處理資料後返回一個結果。

語法：

1.建立函式語法
CREATE FUNCTION 函式名(引數列表) RETURNS 返回型別
BEGIN
	函式體
END

注意：

  • 引數列表包含兩個部分，分別是引數名、引數型別；

  • 函式體：肯定有return語句。如果沒有return語句放在函式體的後面也不會報錯，但不建議。應寫成return 值;

  • 當函式體中只有一句話，則可以省略begin end；

  • 使用delimiter語句設定結束標記；

2.呼叫函式語法
SELECT 函式名(實參列表)
案例演示
1.無參有返回

案例：返回公司的員工個數。

DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;	#定義區域性變數
	SELECT COUNT(*) INTO c 		#為變數賦值
	FROM employees;
	
	RETURN c;			#返回值
END$

SELECT myf1()$
2.有參有返回

案例1：根據員工名，返回他的工資。

DELIMITER $
CREATE FUNCTION myf3(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal = 0;		#定義使用者變數
	SELECT salary INTO @sal		#賦值
	FROM employees
	WHERE last_name = empName;	
	
	RETURN @sal;				#返回值
END $

SELECT myf3('Olson')$

案例2：根據部門名，返回該部門的平均工資。

DELIMITER $
CREATE FUNCTION myf4(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name = deptName;
	RETURN sal;
END $

SELECT myf4('IT')$
3.檢視函式的資訊
SHOW CREATE FUNCTION myf4;
4.刪除函式
DROP FUNCTION myf4;

十六、流程控制結構

分類

  • 順序結構：程式從上往下依次執行。

  • 分支結構：程式從兩條或多條路徑中選擇一條去執行。

  • 迴圈結構：程式在滿足一定條件的基礎上，重複執行一段程式碼。

分支結構

1.if函式

功能：

實現簡單的雙分支。

語法：
SELECT IF(表示式1,表示式2,表示式3);
--執行順序
如果表示式1成立，則if函式會返回表示式2的值，否則會返回表示式3的值。
應用場合：

任何地方。

2.case結構

功能：

情況1：類似於Java中的switch語句，一般用於實現等值判斷。

情況2：類似於Java中的多重if語句，一般用於實現區間判斷。

語法：
-- 情況1
CASE 變數|表示式|欄位
WHEN 要判斷的值 THEN 返回的值1
WHEN 要判斷的值 THEN 返回的值2
WHEN 要判斷的值 THEN 返回的值3
...
ELSE 要返回的值n
END
-- 情況2
CASE
WHEN 要判斷的條件1 THEN 返回的值1
WHEN 要判斷的條件2 THEN 返回的值2
WHEN 要判斷的條件3 THEN 返回的值3
...
ELSE 要返回的值n
END

特點：
  • 可以作為表示式，巢狀在其他語句中使用，可以放在任何地方，begin end中或begin end外面；也可以作為獨立的語句去使用，但這樣只能放在begin end中。
  • 如果when中值滿足或者條件成立，則執行對應的then後面的語句，並且結束case；如果都不滿足，則執行else中的語句或值。
  • else可以省略，如果else省略了，並且所有when條件都不滿足，則返回NULL。
案例：

建立一個儲存過程，根據傳入的成績，來去顯示等級，比如傳入的成績在[90-100]中間，則顯示A；[80-90)之間，則顯示B；[60-80)之間，顯示C；否則，顯示D；

DELIMITER $
CREATE PROCEDURE test_case(IN score INT)
BEGIN
	CASE
	WHEN score>=90 AND score<=100 THEN SELECT 'A';
	WHEN score>=80 THEN SELECT 'B';
	WHEN score>=60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;
END $

CALL test_case(88)$

3.if結構

功能：

實現多重分支。

語法：
IF 條件1 THEN 語句1;
ELSEIF 條件2 THEN 語句2;
...
【ELSE 語句n;】		-- 這一句可以省略
END IF;
應用場合：

只能應用在begin end中。

案例：

建立一個儲存過程，根據傳入的成績，來去顯示等級，比如傳入的成績在[90-100]中間，則返回A；[80-90)之間，則返回B；[60-80)之間，返回C；否則，返回D；

DELIMITER $
CREATE FUNCTION test_if(score INT)RETURNS CHAR(1)
BEGIN
	
	IF score >= 90 AND score <= 100 THEN RETURN 'A';
	ELSEIF score >= 80 THEN RETURN 'B';
	ELSEIF score >= 60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END $

SELECT test_if(88)$

迴圈結構

分類：

  • while
  • loop
  • repeat

迴圈控制：

iterate類似於continue，繼續，結束本次迴圈，繼續下一次；

leave類似於break，跳出，結束當前所在的迴圈。

語法：

1.while
【標籤:】WHILE 迴圈條件  DO
	迴圈體
END WHILE 【標籤】;

-- 如果想要加入迴圈控制，則需要寫標籤。
2.loop
【標籤:】loop
	迴圈體
end loop 【標籤】;
-- 如果想要加入迴圈控制，則需要寫標籤。
-- 可以用來模擬簡單的死迴圈
3.repeat
【標籤:】repeat
	迴圈體;
until 結束迴圈的條件
end repeat【標籤】;
-- 如果想要加入迴圈控制，則需要寫標籤。

案例演示

案例1：批量插入，根據次數插入到admin表中多條記錄。(不新增迴圈控制語句)

DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i <= insertCount DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('hh',i),'adb');
		SET i = i + 1;
	END WHILE;
END $

CALL pro_while1(3)$

案例2：批量插入，根據次數插入到admin表中多條記錄，如果次數>20則停止。(使用迴圈控制語句)

DELIMITER $
CREATE PROCEDURE pro_while2(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i <= insertCount DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('hh',i),'adb');
		IF i >= 20 THEN LEAVE a;
		END IF;	
		SET i = i + 1;
	END WHILE a;
END $

CALL pro_while2(32)$

案例3：批量插入，根據次數插入到admin表中多條記錄，直插入偶數次。(新增iterate語句)

DELIMITER $
CREATE PROCEDURE pro_while3(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i <= insertCount DO
		SET i = i + 1;
		IF MOD(i,2) != 0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('hh',i),'adb');
		
	END WHILE a;
END $

CALL pro_while3(32)$

案例4：已知表stringcontent，其中欄位id 自增長、content varchar(20)，向該表插入指定個數的，隨機的字串。

-- 建立表
CREATE TABLE stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
);
-- 插入資料事務
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;		#定義一個迴圈變數i,表示插入次數
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT DEFAULT 1;	#代表起始索引長度
	DECLARE len INT DEFAULT 1;		#代表擷取的字元的長度
	WHILE i <= insertCount DO
		SET len = FLOOR(RAND()*(20-startIndex+1)+1);		#產生一個隨機的整數，代表擷取長度，1- (26-startIndex+1)
		SET startIndex = FLOOR(RAND()*26+1);			#產生一個隨機的整數，代表起始索引1-26
		INSERT INTO stringcontent(content)VALUES(SUBSTR(str,startIndex,len));
		SET i = i + 1;			#迴圈變數更新
	END WHILE;

END $

CALL test_randstr_insert(14)$

小結：

相關文章