學習MySQL這一篇就夠了

2020 年 7 月 28 日
筆記
資料庫

配套資料，免費下載
鏈接: //pan.baidu.com/s/1dgDE-NaX48KWqIdI9Bi-_Q
提取碼: ip49
複製這段內容後打開百度網盤手機App，操作更方便哦

第一章資料庫概述

1.1、資料庫的好處

將數據持久化到本地
提供結構化查詢功能

1.2、資料庫的常見概念

DB：資料庫，存儲數據的倉庫
DBS：資料庫管理系統，又稱為資料庫軟體或者資料庫產品，用於創建和管理DB，常見的有MySQL、Oracle、DB2、SQL Server
SQL：結構化查詢語言，用於和資料庫通訊的語言，不是某個資料庫軟體特有的，而是幾乎所有的主流資料庫軟體通用的語言

1.3、資料庫的存儲特點

數據存放到表中，然後表再放到庫中
一個庫中可以有多張表，每張表具有唯一的表名用來標識自己
表中有一個或多個列，列又稱為「欄位」，相當於Java中「屬性」
表中的每一行數據，相當於Java中「對象」

1.4、資料庫的常見分類

關係型資料庫：MySQL、Oracle、DB2、SQL Server
非關係型資料庫：
- 鍵值存儲資料庫：Redis、Memcached、MemcacheDB
- 列存儲資料庫：HBase、Cassandra
- 面向文檔的資料庫：MongDB、CouchDB
- 圖形資料庫：Neo4J

1.5、SQL語言的分類

DQL：數據查詢語言
DML：數據操作語言
DDL：數據定義語言
DCL：數據控制語言
TCL：事務控制語言

第二章 MySQL概述

2.1、MySQL的背景

MySQL的前身是屬於MySQL AB，08年被SUN公司收購，09年SUN公司又被Oracle公司收購

2.2、MySQL的優點

成本低、開源免費
性能高、移植性好
體積小、便於安裝

2.3、MySQL的安裝

2.4、MySQL的啟動

net start MySQL

2.5、MySQL的停止

net stop MySQL

2.6、MySQL的登錄

mysql -h主機名 -P埠號 -u用戶名 -p密碼

2.7、MySQL的退出

exit

第三章 DQL語言

3.1、基礎查詢

一、語法

SELECT 查詢列表 FROM 表名;

二、特點

查詢列表可以是欄位、常量、函數、表達式
查詢結果是一個虛擬表

三、示例

1、查詢單個欄位

SELECT 欄位名 FROM 表名;

2、查詢多個欄位

SELECT 欄位名,欄位名 FROM 表名;

3、查詢所有欄位

SELECT * FROM 表名;

4、查詢常量

SELECT 常量值;

注意：字元型和日期型的常量值必須用單引號引起來，數值型不需要

5、查詢函數

SELECT 函數名(實參列表);

6、查詢表達式

SELECT 100/25;

7、起別名

1、 SELECT 欄位名 AS "別名" FROM 表名;

注意：別名可以使用單引號、雙引號引起來，當只有一個單詞時，可以省略引號，當有多個單詞且有空格或特殊符號時，不能省略，AS可以省略

8、去重複

SELECT DISTINCT 欄位名 FROM 表名;

9、做加法

1、 SELECT 數值+數值; 直接運算
2、 SELECT 字元+數值; 首先先將字元轉換為整數，如果轉換成功，則繼續運算，如果轉換失敗，則默認為0，然後繼續運算
3、 SELECT NULL+數值; NULL和任何數值參與運算結果都是NULL

10、【補充】ifnull函數

功能：判斷某欄位或表達式是否為null，如果為null，返回指定的值，否則返回原本的值

SELECT IFNULL(欄位名, 指定值) FROM 表名;

11、【補充】isnull函數

功能：判斷某欄位或表達式是否為null，如果是null，則返回1，否則返回0

SELECT ISNULL(欄位名) FROM 表名;

3.2、條件查詢

一、語法

SELECT 查詢列表 FROM 表名 WHERE 篩選條件;

二、分類

條件運算符：>、>=、<、<=、=、<=>、!=、<>
邏輯運算符：and、or、not
模糊運算符：
- like：%任意多個字元、_任意單個字元
- between and
- not between and
- in
- is null
- is not null

三、演示

1、查詢工資>12000的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE salary > 12000 ;

2、查詢工資>=14000的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE salary >= 14000 ;

3、查詢工資<12000的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE salary < 12000 ;

4、查詢工資<=14000的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE salary <= 14000 ;

5、查詢員工編號=100的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE employee_id = 100 ;

6、查詢員工編號<=>100的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE employee_id <=> 100 ;

注意：=只能判斷普通類型的數值，而<=>不僅可以判斷普通類型的數值還可以判斷NULL

7、查詢員工編號!=100的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE employee_id != 100 ;

8、查詢員工編號<>100的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE employee_id <> 100 ;

注意：!=和<>都是判斷不等於的意思，但是MySQL推薦使用<>

9、查詢工資>12000&&工資<18000的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE salary > 12000 AND salary < 18000 ;

10、查詢工資<=12000||工資>=18000的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE salary <= 12000 OR salary >= 18000 ;

11、查詢工資<=12000||工資>=18000的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE NOT (salary > 12000 AND salary < 18000) ;

12、查詢員工名中第三個字元為B、第四個字元為d的員工資訊

SELECT 
  *
FROM
  employees 
WHERE last_name LIKE 'B__d%' ;

13、查詢員工編號在100到120之間的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE employee_id BETWEEN 100 AND 120 ;

14、查詢員工編號不在100到120之間的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE employee_id NOT BETWEEN 100 AND 120 ;

15、查詢員工的工種編號是 IT_PROG、AD_VP、AD_PRES中的一個員工名和工種編號

SELECT 
  last_name,
  job_id 
FROM
  employees 
WHERE job_id IN ('IT_PROT', 'AD_VP', 'AD_PRES') ;

注意：in列表的值類型必須一致或兼容，in列表中不支援通配符%和_

16、查詢沒有獎金的員工名和獎金率

SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NULL ;

17、查詢有獎金的員工名和獎金率

SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NOT NULL ;

注意：=、!=不能用來判斷NULL、而<=>、is null 、 is not null可以用來判斷NULL，但注意<=>也可以判斷普通類型的數值

3.3、排序查詢

一、語法

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

二、注意

排序列表可以是單個欄位、多個欄位、別名、函數、表達式
asc代表升序，desc代表降序，如果不寫，默認是asc
order by的位置一般放在查詢語句的最後（除limit語句之外）

三、示例

1、按單個欄位排序：查詢員工資訊，要求按工資降序

SELECT 
  * 
FROM
  employees 
ORDER BY salary DESC ;

2、按多個欄位查詢：查詢員工資訊，要求先按工資降序，再按員工編號升序

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

3、按別名排序查詢：查詢員工資訊，要求按員工年薪升序

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

4、按函數排序查詢：查詢員工資訊，要求按員工名字的長度降序

SELECT 
  LENGTH(last_name),
  last_name 
FROM
  employees 
ORDER BY LENGTH(last_name) DESC ;

5、按表達式排序：查詢員工資訊，要求按員工年薪升序

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

3.4、單行函數

一、語法

SELECT 函數名(實參列表) 【FROM 表】;

二、分類

字元函數
- concat：連接字元
- substr：截取子串
- replace：替換字元
- upper：變大寫
- lower：變小寫
- lpad：左填充
- rpad：右填充
- length：獲取位元組長度
- trim：去除前後空格
- instr：獲取子串第一次出現的索引
```
1、注意MySQL中的索引是從1開始的
```
數學函數
- round：四捨五入
- ceil：向上取整
- floor：向下取整
- mod：取模運算（a-a/b*b）
- truncate：保留小數的位數，不進行四捨五入
- rand：獲取隨機數，返回0-1之間的小數
日期函數
- now：返回當前日期+時間
- curdate：返回當前日期
- curtime：返回當前時間
- year：返回年
- month：返回月
- day：返回日
- hour：小時
- minute：分鐘
- second：秒
- monthname：以英文形式返回月
- datediff：返回兩個日期相差的天數
- date_format：將日期轉換成字元
- str_to_date：將字元轉換成日期
```
格式符：
%Y：四位的年份
%y：二位的年份
%m：二位的月份（01，02，...，12）
%c：一位的月份（1，2，...，12）
%d：日（01，02，...，31）
%H：小時（24小時制）
%h：小時（12小時制）
%i：分鐘（00，01，02，...，59）
%s：秒（00，01，02，...，59）    
```

控制函數

if：判斷函數
case：分支函數

1、IF(條件表達式，表達式1，表達式2)：如果條件表達式成立，返回表達式1，否則返回表達式2

2、case的格式一：
    CASE 變數或欄位或表達式 
      WHEN 常量1 THEN 值1 
      WHEN 常量2 THEN 值2
      ...
      ELSE 值n 
    END ;
    
3、case的格式二：
    CASE
      WHEN 條件1 THEN 值1 
      WHEN 條件2 THEN 值2
      ...
      ELSE 值n 
    END

其它函數
- version：當前資料庫的版本
- database：當前打開的資料庫
- user：當前登錄的用戶
- password('字元')：返回該字元的密碼形式
- md5('字元')：返回該字元的md5加密形式

三、演示

1、concat

SELECT CONCAT('Hello',' ','World') AS out_put;

2、substr

#截取從指定索引處後面所有字元
SELECT SUBSTR('李莫愁愛上了陸展元',7)  AS out_put;

#截取從指定索引處指定字元長度的字元
SELECT SUBSTR('李莫愁愛上了陸展元',1,3) AS out_put;

3、replace

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

4、upper

SELECT UPPER('john') AS out_put;

5、lower

SELECT LOWER('john') AS out_put;

6、lpad

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

7、rpad

SELECT RPAD('殷素素',10,'*') AS out_put;

8、length

SELECT LENGTH('john') AS out_put;

9、trim

#刪除指定字元的左右空格
SELECT LENGTH(TRIM('    張翠山    ')) AS out_put;

#刪除指定字元的指定字元
SELECT TRIM('aaa' FROM 'aaaaaaaaa張翠山aaaaaaaaa')  AS out_put;

10、instr

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

注意：返回子串第一次出現的索引，如果找不到返回0

11、round

#默認四捨五入
SELECT ROUND(-1.55) AS out_put;

#指定小數位數
SELECT ROUND(1.567,2) AS out_put;

注意：四捨五入和符號無關

12、ceil

SELECT CEIL(-1.02) AS out_put;

注意：向上取整，返回>=該參數的最小整數

13、floor

SELECT FLOOR(-9.99) AS out_put;

注意：向下取整，返回<=該參數的最大整數

14、mod

SELECT MOD(10,3) AS out_put;

15、truncate

SELECT TRUNCATE(1.69999,1) AS out_put;

16、rand

SELECT RAND() AS out_put;

17、now

SELECT NOW() AS out_put;

18、curdate

SELECT CURDATE() AS out_put;

19、curtime

SELECT CURTIME() AS out_put;

20、year

SELECT YEAR(NOW()) 年;

21、month

SELECT MONTH(NOW()) 月;

22、day

SELECT DAY(NOW()) 日;

23、hour

SELECT HOUR(NOW()) 時;

24、minute

SELECT MINUTE(NOW()) 分;

25、second

SELECT SECOND(NOW()) 秒;

26、monthname

SELECT MONTHNAME(NOW()) 月名;

27、datediff

SELECT DATEDIFF('1995-2-7','1995-2-6') AS out_put;

28、date_format

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

29、str_to_date

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

30、if

SELECT IF(10 < 5, '大', '小') AS out_put;

31、case的格式一

/*
案例：查詢員工的工資，要求
	部門號=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 ;

32、case的格式二

/*
案例：查詢員工的工資情況
    如果工資>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 ;

33、version

SELECT VERSION();

34、database

SELECT DATABASE();

35、user

SELECT USER();

36、password('字元')

SELECT PASSWORD('123456');

37、md5('字元')

SELECT MD5('123456');

3.5、分組函數

一、語法

SELECT 函數名(實參列表) 【FROM 表】;

二、分類

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

1、sum、avg一般用於處理數值型，max、min、count可以處理任何類型

2、以上分組函數都忽略null值

3、可以和distinct搭配實現去重的運算：select sum(distinct 欄位) from 表;

4、一般使用count(*)用作統計行數

5、和分組函數一同查詢的欄位要求是group by後的欄位

三、演示

1、sum

SELECT SUM(salary) FROM employees;

2、avg

SELECT AVG(salary) FROM employees;

3、max

SELECT MAX(salary) FROM employees;

4、min

SELECT MIN(salary) FROM employees;

5、count

SELECT COUNT(salary) FROM employees;

3.6、分組查詢

一、語法

SELECT 
  查詢列表 
FROM
  表 
【where 篩選條件】 
GROUP BY 分組的欄位 
【having 分組後的篩選】
【order BY 排序的欄位】 ;

二、特點

1、和分組函數一同查詢的欄位必須是group by後出現的欄位

2、篩選分為兩類：分組前篩選和分組後篩選
            	針對的表				 語句位置			   連接的關鍵字
	分組前篩選	 分組前的原始表			group by前			where
	分組後篩選	 分組後的結果集			group by後			having
	
3、分組可以按單個欄位也可以按多個欄位

4、分組可以搭配著排序使用

三、演示

1、查詢每個工種的員工平均工資

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

2、查詢每個位置的員工部門個數

SELECT 
  COUNT(*),
  location_id 
FROM
  departments 
GROUP BY location_id ;

3、查詢有獎金的每個領導手下員工的平均工資

SELECT 
  AVG(salary),
  manager_id 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY manager_id ;

4、查詢哪個部門的員工個數>5

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

5、查詢每個工種有獎金的員工的最高工資>6000的最高工資和公眾編號，按最高工資升序

SELECT 
  MAX(salary) m,
  job_id
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING m > 6000 
ORDER BY m ;

6、查詢每個工種每個部門的最低工資並按最低工資降序

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

3.7、連接查詢

一、含義

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

二、注意

笛卡爾乘積現象：表1 有m行，表2有n行，結果=m*n行

發生原因：沒有有效的連接條件
如何避免：添加有效的連接條件

三、分類

按年代分類
- sql92標準：支援內連接
- sql99標準：支援內連接、部分外連接（左外、右外）、交叉連接
按功能分類
- 內連接
  - 等值連接
  - 非等值連接
  - 自連接
- 外連接
  - 左外連接
  - 右外連接
  - 全外連接
- 交叉連接

四、sql92標準演示

1、sql92標準：等值連接

#查詢員工名和對應的部門名

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

2、sql92標準：非等值連接

#查詢員工的工資和工資級別

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

3、sql92標準：自連接

#查詢員工名和它對應上級的名稱

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

3.8、sql99標準

一、語法

SELECT 
  查詢列表 
FROM 表1 別名1 
【連接類型】 JOIN 表2 別名2 ON 連接條件 
【where 分組前篩選條件】
【group BY 分組列表】
【having 分組後篩選條件】
【order BY 排序列表】 ;

二、連接類型

內連接：inner
外連接
- 左外連接：left 【outer】（左邊的是主表）
- 右外連接：right 【outer】（右邊的是主表）
- 全外連接：full 【outer】（兩邊都是主表，但是MySQL不支援全外連接、Oracle支援）
交叉連接：cross（交叉連接其實是用sql99語法實現笛卡爾乘積）

三、演示

1、內連接：等值連接

#查詢員工名和對應的部門名

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

2、內連接：非等值連接

#查詢員工的工資和工資級別

SELECT 
  salary,
  grade_level 
FROM
  employees e 
INNER 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 
INNER JOIN employees m ON e.`manager_id` = m.`employee_id` ;

4、外連接：左外連接

#查詢哪個部門沒有員工

SELECT 
  d.`department_name`
FROM
  departments d 
LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

5、外連接：右外連接

#查詢哪個部門沒有員工

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

6、交叉連接

#使用交叉連接進行笛卡爾乘積查詢

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

3.9、子查詢

一、含義

嵌套在其它語句內部的select語句稱為子查詢或內查詢，外面的語句可以是insert、delete、update、select等，一般select作為外面語句較多，外面如果為select語句，則此語句稱為外查詢或主查詢

二、分類

按出現的位置劃分
- select後面：標量子查詢
- from後面：表子查詢
- where或having後面
  - 標量子查詢
  - 列子查詢
  - 行子查詢
- exists後面
  - 標量子查詢
  - 列子查詢
  - 行子查詢
  - 表子查詢
按結果集行列數劃分
- 標量子查詢（單行子查詢）：結果集為一行一列
- 列子查詢（多行子查詢）：結果集為多行一列
- 行子查詢：結果集為多行多列
- 表子查詢：結果集為多行多列

三、特點

子查詢放在小括弧內
子查詢一般放在條件的右側
子查詢的執行優先於主查詢執行，主查詢的條件用到了子查詢的結果
標量子查詢，一般搭配著單行操作符使用：>、>=、<、<=、!=、<>、=、<=>
列子查詢，一般搭配著多行操作符使用：in、any/some、all

四、演示

1、select後面

#查詢每個部門的員工個數

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

2、from後面

#查詢每個部門平均工資的工資等級

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 ;

3、where或having後面

標量子查詢：查詢最低工資的員工姓名和工資

SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE salary = (
	SELECT MIN(salary) FROM employees
) ;

列子查詢：

#查詢所有是領導的員工姓名

SELECT 
  last_name 
FROM
  employees 
WHERE employee_id IN (
	SELECT DISTINCT manager_id FROM employees
) ;

#返回其它工種中比job_id為『IT_PROG』工種任一工資低的員工的員工號、姓名、job_id以及salary

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

#返回其它部門中比job_id為『IT_PROG』部門所有工資都低的員工的員工號、姓名、job_id以及salary

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

行子查詢：查詢員工編號最小並且工資最高的員工資訊

SELECT 
  * 
FROM
  employees 
WHERE (employee_id, salary) = (
	SELECT MIN(employee_id), MAX(salary) FROM employees
) ;

4、exists後面

#查詢有員工的部門名

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

3.10、分頁查詢

一、語法

SELECT 
  查詢列表 
FROM
  表1 別名1
【連接類型】 JOIN 表2 別名2 ON 連接條件 
【WHERE 分組前的篩選】
【GROUP BY 分組欄位】 
【HAVING 分組後的篩選 】
【ORDER BY 排序欄位 ASC|DESC】
LIMIT 【offset, 】size ;

二、特點

limit語句放在查詢語句的最後
offset代表起始索引，起始索引從0開始，size代表條目個數
分頁語句：select 查詢列表 from 表 limit (page-1)*size,size;

三、演示

#查詢前五條員工資訊

SELECT * FROM  employees LIMIT 0,5;

3.11、聯合查詢

一、語法

查詢語句1
union 【all】
查詢語句2
union 【all】
...

二、特點

要查詢的結果來自於多個表且多個表沒有直接的連接關係，但查詢的資訊一致時，可以使用聯合查詢
要求多條查詢語句的查詢列數是一致的
要求多條查詢語句的查詢的每一列的類型和順序最好一致
union關鍵字默認去重，如果使用union all可以包含重複項

三、演示

#查詢中國用戶中男性的資訊以及外國用戶中年男性的用戶資訊

SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

第四章 DML語言

4.1、插入語句

一、語法

#方式一：
INSERT INTO 表名(欄位名,...) VALUES(值,...);

#方式二：
INSERT INTO 表名 SET 欄位名=值,欄位名=值,...;

二、特點

要求值的類型和欄位的類型要一致或兼容
欄位的個數和順序不一定與原始表中的欄位個數和順序一致，但必須保證值和欄位一一對應
假如表中有可以為null的欄位，注意可以通過以下兩種方式插入null值：①欄位和值都省略、②欄位寫上，值使用null
欄位和值的個數必須一致
欄位名可以省略，默認所有列
方式一支援一次插入多行，語法如下：INSERT INTO 表名【(欄位名,..)】 VALUES(值,...),(值,...),...;
方式一支援子查詢，語法如下：INSERT INTO 表名查詢語句;

三、演示

1、方式一插入一條數據

INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id) VALUES(15,'唐藝昕','女','1997-12-05','15633029014',NULL,2);

2、方式二插入一條數據

INSERT INTO beauty SET id = 19,name = '張倩倩',sex = '女',borndate = '1997-12-05',phone = '15633029014',photo = NULL,boyfriend_id = 3 ;

4.2、修改語句

一、語法

#sql92語法： 
UPDATE 
  表1 別名,
  表2 別名 
SET
  列 = 值,
  ...
WHERE 連接條件 AND 篩選條件 ;

#sql99語法： 
UPDATE 
  表1 別名 
INNER | LEFT | RIGHT JOIN 表2 別名 ON 連接條件
  SET 列 = 值,
  ...
WHERE 篩選條件 ;

二、演示

1、sql92語法演示

#修改beauty表中姓唐的女神的電話為13899888899

UPDATE beauty SET phone = '13899888899' WHERE NAME LIKE '唐%';

2、sql99語法演示

#修改張無忌的女朋友的手機號為13899888899，魅力值為1000

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

4.3、刪除語句

一、語法

#方式一：使用delete
1、單表刪除 
DELETE FROM 表名 【WHERE 篩選條件 】;

2、多表刪除（級聯刪除）
sql92語法： 
DELETE 
  表1的別名,
  表2的別名 
FROM
  表1 別名,
  表2 別名 
WHERE 連接條件 AND 篩選條件 ;

sql99語法： 
DELETE 
  表1的別名,
  表2的別名 
FROM
  表1 別名 
INNER | LEFT | RIGHT JOIN 表2 別名 ON 連接條件 
WHERE 篩選條件 ;

#方式二：使用truncate
TRUNCATE TABLE 表名 ;

二、特點

delete刪除後，如果再插入，標識列從斷點開始；truncate刪除後，如果再插入，標識列從1開始
delete可以添加篩選條件；truncate不可以添加篩選條件
delete效率較低；truncate效率較高
delete可以返回受影響的行數；truncate沒有返回值
delete可以回滾；truncate不可以回滾

三、演示

1、方式一：單表刪除

#刪除手機號以9結尾的女神資訊

DELETE FROM beauty WHERE phone LIKE '%9';

2、方式一：多表刪除

#刪除黃曉明的資訊以及他女朋友的資訊
#sql92語法：
DELETE
  b,
  bo
FROM
  beauty b,
  boys bo
WHERE b.`boyfriend_id` = bo.`id` AND bo.`boyName` = '黃曉明' ; 

#刪除黃曉明的資訊以及他女朋友的資訊
#sql99語法：
DELETE 
  b,
  bo 
FROM
  beauty b 
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id` 
WHERE bo.`boyName` = '黃曉明' ;

3、方式二

#清空boys表

TRUNCATE TABLE boys ;

第五章 DDL語言

5.1、庫的管理

5.1.1、庫的創建

CREATE DATABASE 【IF NOT EXISTS】 庫名 【 CHARACTER SET 字符集名】;

5.1.2、庫的修改

#它已經被廢棄
RENAME DATABASE 舊庫名 TO 新庫名;

#修改庫字符集
ALTER DATABASE 庫名 CHARACTER SET 字符集名;

5.1.3、庫的刪除

DROP DATABASE 【IF EXISTS】 庫名;

5.2、表的管理

5.2.1、表的創建

CREATE TABLE 【IF NOT EXISTS】 表名 (
  欄位名 欄位類型 【約束】,
  欄位名 欄位類型 【約束】,
  ...
  欄位名 欄位類型 【約束】
) ;

5.2.2、表的修改

1、添加列
ALTER TABLE 表名 ADD COLUMN 列名 類型 【FIRST|AFTER 欄位名】;

2、修改列的類型或約束
ALTER TABLE 表名 MODIFY COLUMN 列名 新類型 【新約束】;

3、修改列名
ALTER TABLE 表名 CHANGE COLUMN 舊列名 新列名 類型;

4、刪除列
ALTER TABLE 表名 DROP COLUMN 列名;

5、修改表名
ALTER TABLE 表名 RENAME 【TO】 新表名;

5.2.3、表的刪除

DROP TABLE 【IF EXISTS】 表名;

5.2.4、表的複製

1、複製表的結構
CREATE TABLE 表名 LIKE 舊錶;

2、複製表的某些欄位
CREATE TABLE 表名 
SELECT 欄位1,欄位2,... FROM 舊錶 WHERE 0;

3、複製表的結構+數據
CREATE TABLE 表名 
SELECT 查詢列表 FROM 舊錶 【WHERE 篩選條件】;

4、複製表的某些欄位+數據
CREATE TABLE 表名 
SELECT 欄位1,欄位2,... FROM 舊錶 【WHERE 篩選條件】;

5.3、數據類型

5.3.1、數值型

一、類型

類型	TINYINT	SMALLINT	MEDIUMINT	INT/INTEGER	BIGINT
位元組	1	2	3	4	8

二、特點

都可以設置無符號和有符號，默認有符號，通過unsigned設置無符號
如果超出了範圍，會報out or range異常，插入臨界值（該類型的最大值或最小值即為臨界值）
長度可以不指定，默認會有一個長度，長度代表顯示的最大寬度，如果不夠則左邊用0填充，但需要搭配zerofill，並且默認變為無符號整型
如果對數據沒有特殊要求，則優先考慮使用INT/INTEGER

5.3.2、浮點型

一、類型

定點數
- DEC(M,D) ：M+2位元組
- DECIMAL(M,D)：M+2位元組
浮點數
- FLOAT(M,D) ：4位元組
- DOUBLE(M,D)：8位元組

二、特點

M代表整數部位+小數部位的個數，D代表小數部位
如果超出範圍，則報out or range異常，並且插入臨界值（該類型的最大值或最小值即為臨界值）
M和D都可以省略，但對於定點數，M默認為10，D默認為0
如果精度要求較高，則優先考慮使用定點數

5.3.3、字元型

一、類型

類型	CHAR	VARCHAR	BINARY	VARBINARY	ENUM	SET	TEXT	BLOB
描述	固定長度字元	可變長度字元	二進位字元串	二進位字元串	枚舉	集合	文本	二進位大型對象

二、特點

char：固定長度的字元，寫法為char(M)，最大長度不能超過M，其中M可以省略，默認為1
varchar：可變長度的字元，寫法為varchar(M)，最大長度不能超過M，其中M不可以省略
如果對數據沒有特殊要求，則優先考慮使用VARCHAR

5.3.4、日期型

一、類型

類型	YEAR	DATE	TIME	DATETIME	TIMESTAMP
描述	年份	日期	時間	日期+時間	日期+時間

二、特點

TIMESTAMP比較容易受時區、語法模式、版本的影響，更能反映當前時區的真實時間，而DATETIME則只能反映出插入時的當地時區
TIMESTAMP支援的時間範圍較小，DATETIME的取值範圍：1000-1-1 — 9999-12-31
TIMESTAMP的屬性受Mysql版本和SQLMode的影響很大
如果對數據沒有特殊要求，則優先考慮使用DATETIME

5.4、常見約束

一、含義

約束是一種限制，用於限制表中的數據，為了保證表中的數據的準確和可靠性

二、分類

NOT NULL：非空，該欄位的值必填
UNIQUE：唯一，該欄位的值不可重複
DEFAULT：默認，該欄位的值不用手動插入有默認值
CHECK：檢查，MySQL不支援
PRIMARY KEY：主鍵，該欄位的值不可重複並且非空 unique+not null
FOREIGN KEY：外鍵，該欄位的值引用了另外的表的欄位

三、特點

主鍵和唯一

#不同點：
1、一個表至多有一個主鍵，但可以有多個唯一
2、主鍵不允許為空，唯一可以為空

#相同點：
1、都具有唯一性
2、都支援組合鍵，但不推薦

外鍵

1、用於限制兩個表的關係，從表的欄位值引用了主表的某欄位值
2、外鍵列和主表的被引用列要求類型一致，意義一樣，名稱無要求
3、主表的被引用列要求是一個key（一般就是主鍵）
4、插入數據，先插入主表；刪除數據，先刪除從表


可以通過以下兩種方式來刪除主表的記錄
#方式一：級聯刪除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

#方式二：級聯置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

四、演示

1、創建表時添加約束

CREATE TABLE 表名(
	欄位名 欄位類型 NOT NULL,#非空
    欄位名 欄位類型 DEFAULT 值,#默認
	欄位名 欄位類型 PRIMARY KEY,#主鍵
	欄位名 欄位類型 UNIQUE,#唯一
	CONSTRAINT 約束名 FOREIGN KEY(欄位名) REFERENCES 主表(被引用列)
) ;

注意：

列級約束支援：非空、默認、主鍵、唯一，不可以起約束名
表級約束支援：主鍵、唯一、外鍵，可以起約束名，但是在MySQL中對主鍵無效
列級約束可以在一個欄位上追加多個，中間用空格隔開，沒有順序要求

2、修改表時添加或刪除約束

1、非空
添加非空（列級約束）
ALTER TABLE 表名 MODIFY COLUMN 欄位名 欄位類型 NOT NULL;
刪除非空
ALTER TABLE 表名 MODIFY COLUMN 欄位名 欄位類型;

2、默認
添加默認（列級約束）
ALTER TABLE 表名 MODIFY COLUMN 欄位名 欄位類型 DEFAULT 值;
刪除默認
ALTER TABLE 表名 MODIFY COLUMN 欄位名 欄位類型;

3、主鍵
添加主鍵（列級約束）
ALTER TABLE 表名 MODIFY COLUMN 欄位名 欄位類型 PRIMARY KEY;
添加主鍵（表級約束）
ALTER TABLE 表名 add 【CONSTRAINT 約束名】 PRIMARY KEY(欄位名);
刪除主鍵
ALTER TABLE 表名 DROP PRIMARY KEY;

4、唯一
添加唯一（列級約束）
ALTER TABLE 表名 MODIFY COLUMN 欄位名 欄位類型 UNIQUE;
添加唯一（表級約束）
ALTER TABLE 表名 add 【CONSTRAINT 約束名】 UNIQUE(欄位名);
刪除唯一
ALTER TABLE 表名 DROP INDEX 索引名;

5、外鍵
添加外鍵（表級約束）
ALTER TABLE 表名 add 【CONSTRAINT 約束名】 FOREIGN KEY(欄位名) REFERENCES 主表(被引用列);
刪除外鍵
ALTER TABLE 表名 DROP FOREIGN KEY 約束名;

5.5、自增長列

一、含義

自增長列又稱為標識列，它可以不用手動的插入值，系統提供默認的序列值

二、特點

不用手動插入值，可以自動提供序列值，默認從1開始，步長為1，如果要更改起始值，第一次手動插入值，後續使用NULL，如果要更改步長，修改更改系統變數：SET auto_increment_increment = 值;
一個表至多有一個自增長列
自增長列只能支援數值型
自增長列必須為一個key

三、演示

1、創建表時添加自增長列

CREATE TABLE 表名 (
  欄位名 欄位類型 約束 AUTO_INCREMENT
) ;

2、修改表時添加或刪除自增長列

添加自增長列
ALTER TABLE 表 MODIFY COLUMN 欄位名 欄位類型 約束 AUTO_INCREMENT;

刪除自增長列
ALTER TABLE 表 MODIFY COLUMN 欄位名 欄位類型 約束;

第六章 TCL語言

6.1、事務

一、含義

一條或多條sql語句組成一個執行單位，一組sql語句要麼都執行要麼都不執行

二、特點（ACID）

原子性：一個事務是不可再分割的整體，要麼都執行要麼都不執行
一致性：一個事務可以使數據從一個一致狀態切換到另外一個一致的狀態
隔離性：一個事務不受其它事務的干擾，多個事務互相隔離的
持久性：一個事務一旦提交了，則永久的持久化到本地

三、分類

隱式事務：沒有明顯的開啟和結束，本身就是一條事務可以自動提交，比如insert、update、delete
顯式事務：具有明顯的開啟和結束，例如如下格式：

1、開啟事務
SET autocommit=0;
START TRANSACTION;#可以省略

2、編寫一組邏輯sql語句
注意：sql語句支援的是insert、update、delete

【設置回滾點，可選】
SAVEPOINT 回滾點名;

3、結束事務
提交：commit;
回滾：rollback;
回滾到指定的地方： ROLLBACK TO 回滾點名;

6.2、並發事務

一、事物的並發問題如何發生？

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

二、事務的並發問題都有哪些？

臟讀：一個事務讀取了其它事務還沒有提交的數據，讀到的是其它事務「更新」的數據
不可重複讀：一個事務多次讀取，結果不一樣
幻讀：一個事務讀取了其它事務還沒有提交的數據，只是讀到的是其它事務「插入」的數據

三、事物的並發問題如何解決？

通過設置隔離級別來解決並發問題

四、隔離級別

隔離級別	隔離描述	臟讀	不可重複讀	幻讀
READ UNCOMMITTED	讀未提交	×	×	×
READ COMMITTED	讀已提交	√	×	×
REPEATABLE READ	可重複讀	√	√	×
SERIALIZABLE	串列化	√	√	√

五、注意問題

mysql 中默認第三個隔離級別 REPEATABLE READ
oracle中默認第二個隔離級別 READ COMMITTED

查看隔離級別 SELECT @@tx_isolation;
設置隔離級別 SET SESSION|GLOBAL TRANSACTION ISOLATION LEVEL 隔離級別;

第七章高級部分

7.1、視圖

一、含義

mysql5.1版本出現的新特性，本身是一個虛擬表，它的數據來自於表，通過執行時動態生成。

二、特點

簡化sql語句
提高了sql的重用性
保護基表的數據，提高了安全性

三、演示

1、創建

CREATE VIEW 視圖名
AS
查詢語句;

2、修改

#方式一：
CREATE OR REPLACE VIEW 視圖名
AS
查詢語句;

#方式二：
ALTER VIEW 視圖名
AS
查詢語句;

3、刪除

DROP VIEW 視圖1,視圖2,...;

4、查看

#方式一：
DESC 視圖名;

#方式二：
SHOW CREATE VIEW 視圖名;

四、注意

視圖一般用於查詢的，而不是更新的，所以具備以下特點的視圖都不允許更新：

包含分組函數、group by、distinct、having、union、join
常量視圖
where後的子查詢用到了from中的表
用到了不可更新的視圖

7.2、變數

一、分類

系統變數
- 全局變數
- 會話變數
自定義變數
- 用戶變數
- 局部變數

二、演示

1、全局變數

描述：伺服器層面上的，必須擁有super許可權才能為系統變數賦值，作用域為整個伺服器，也就是針對於所有連接（會話）有效，但不能跨重啟

1、查看所有系統變數
SHOW GLOBAL VARIABLES;

2、查看滿足條件的部分系統變數
SHOW GLOBAL VARIABLES LIKE '%char%';

3、查看指定的系統變數的值
SELECT @@global 系統變數名;

4、為某個系統變數賦值
方式一： SET GLOBAL 系統變數名=值;
方式二： SET @@global 系統變數名=值;

2、會話變數

描述：伺服器為每一個連接的客戶端都提供了系統變數，作用域為當前的連接（會話）

1、查看所有系統變數
SHOW 【 SESSION 】 VARIABLES;

2、查看滿足條件的部分系統變數
SHOW 【 SESSION 】 VARIABLES LIKE '%char%';

3、查看指定的系統變數的值
SELECT 【 SESSION 】系統變數名;

4、為某個系統變數賦值
SET 【 SESSION 】系統變數名=值;

3、用戶變數

描述：針對於當前連接（會話）生效

位置：可以在begin end裡面，也可以放在外面

1、聲明並賦值
SET @變數名=值; 或
SET @變數名:=值; 或
SELECT @變數名:=值;

2、更新值
方式一：
	SET @變數名=值; 或
	SET @變數名:=值; 或
	SELECT @變數名:=值;
方式二：
	SELECT xxx INTO @變數名 FROM 表;
	
3、查看值
SELECT @變數名;

4、局部變數

描述：作用域：僅僅在定義它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句

1、聲明並賦值
DECLARE 變數名 類型 【 DEFAULT 值 】;

2、更新值
方式一：
	SET 變數名=值; 或
	SET 變數名:=值; 或
	SELECT @變數名:=值;
方式二：
	SELECT xxx INTO 變數名 FROM 表;
	
3、查看值
SELECT 變數名;

7.3、存儲過程

一、含義

存儲過程，類似於Java中的方法，它是一組預先編譯好的SQL語句的集合，理解成批處理語句

二、特點

簡化sql語句
提高了sql的重用性
減少了編譯次數並且減少了和資料庫伺服器的連接次數，提高了效率

三、演示

1、創建

#標準格式如下：
DELIMITER $
CREATE PROCEDURE 存儲過程名(參數模式 參數名 參數類型,...)
BEGIN
	存儲過程體（一組合法的SQL語句）
END $
DELIMITER ;

#參數模式in：參數類型是輸入的
#參數模式out：參數類型是輸出的
#參數模式inout：參數類型既可以輸入也可以輸出

#調用in模式的參數： CALL sp1('Hello,World');
#調用out模式的參數： SET @name; CALL sp1(@name); SELECT @name;
#調用inout模式的參數： SET @name=值; CALL sp1(@name); SELECT @name;

2、刪除

DROP PROCEDURE 【 IF EXISTS 】 存儲過程名;

3、查看

SHOW CREATE PROCEDURE 存儲過程名;

7.4、控制結構

7.4.1、分支結構

7.4.1.1、if結構

一、語法

注意：只能應用在begin end中

IF 條件1 THEN 語句1;
ELSEIF 條件2 THEN 語句2;
ELSEIF 條件3 THEN 語句3;
....
【 ELSE 語句n; 】
END IF;

二、演示

#創建函數，實現傳入成績，如果成績>90,返回A，如果成績>80,返回B，如果成績>60,返回C，否則返回D
DELIMITER $
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $
DELIMITER ;

#函數調用
SELECT test_if(87)$

7.4.1.2、case結構

一、語法

注意：只能應用在begin end中

語法1：
CASE 表達式或欄位
WHEN 值1 THEN 語句1;
WHEN 值2 THEN 語句2;
...
【 ELSE 語句n; 】
END 【 CASE 】;

語法2：
CASE 
WHEN 條件1 THEN 語句1;
WHEN 條件2 THEN 語句2;
...
【 ELSE 語句n; 】
END 【 CASE 】;

二、演示

#創建函數，實現傳入成績，如果成績>90,返回A，如果成績>80,返回B，如果成績>60,返回C，否則返回D
DELIMITER $
CREATE FUNCTION test_case (score FLOAT) RETURNS CHAR 
BEGIN
	DECLARE ch CHAR DEFAULT 'A' ;
	
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	
	RETURN ch;
END $
DELIMITER ;

#函數調用
SELECT test_case(56);

7.4.2、循環結構

一、分類

二、演示

7.4.2.1、while結構

#批量插入，根據次數插入到admin表中多條記錄，如果次數>20則停止
#刪除過程
DROP PROCEDURE IF EXISTS test_while;

#定義過程
DELIMITER $
CREATE PROCEDURE test_while(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		#LEAVE代表Java中的break關鍵字；ITERATE代表Java中的continue關鍵字
		IF i>20 THEN LEAVE a;
		END IF;
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		SET i=i+1;
	END WHILE a;
END $
DELIMITER ;

#調用過程
CALL test_while(100);

7.4.2.2、repeat結構

#批量插入，根據次數插入到admin表中多條記錄，如果次數>20則停止
#刪除過程
DROP PROCEDURE IF EXISTS test_repeat;

#定義過程
DELIMITER $
CREATE PROCEDURE test_repeat(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:REPEAT 
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		SET i=i+1;
	UNTIL i>20		
	END REPEAT a;
END $
DELIMITER ;

#調用過程
CALL test_repeat(100);

7.4.2.3、loop結構

#批量插入，根據次數插入到admin表中多條記錄，如果次數>20則停止
#刪除過程
DROP PROCEDURE IF EXISTS test_loop;

#定義過程
DELIMITER $
CREATE PROCEDURE test_loop(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:LOOP 
		#LEAVE代表Java中的break關鍵字；ITERATE代表Java中的continue關鍵字
		IF i>20 THEN LEAVE a;
		END IF;
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		SET i=i+1;
	END LOOP a;
END $
DELIMITER ;

#調用過程
CALL test_loop(100);

Tags: 資料庫