學習中的思考

在 mysql 學習和使用中，我遇到了不少的難題，我覺得我應該形成一套邏輯思考體系，可以讓我在初識 mysql 的程序中加入理性思考，從一開始就探求原理，了解所學內容的核心和關鍵點，做到一葉知秋而不是只見樹木不見森林，

mysql 幾種資料型別的底層是如何存盤的，不同的 sql 陳述句對其有何限制？
學習 sql 陳述句時，需要思考：這個 sql 陳述句的底層是如何實作的，對哪些資料型別起作用，有什么限制，如果這個 sql 陳述句對單欄位可以使用，可否在多欄位中使用，在多欄位使用的程序中，可否做到只對一個欄位起作用或者對多個欄位同時起作用？
隔離級別是如何實作的？

`MySQL` 基礎

一、為什么要學習資料庫

二、資料庫的相關概念：

DB：資料庫(database)，存盤資料的“倉庫”，它保存了一系列有組織的資料，
DBMS：資料庫管理系統(Database Management System)，資料庫是通過 DBMS 創建和操作的容器，
SQL：結構化查詢語言(Structure Query Language)，專門用來與資料庫通信的語言，

SQL的優點：

不是某個特定資料庫供應商專有的語言，幾乎所有DBMS都支持 SQL ，
簡單易學，
強有力，靈活使用，

三、資料庫存盤結構的特點：

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

四、初識`MySQL`

`MySQL`產品的介紹

DBMS分為兩類：

基于共享檔案系統的DBMS(Access)
基于客戶機-服務器的DBMS(MySQL、Oracle、SqlServer)

`MySQL`產品的安裝

`MySQL`安裝

`MySQL`卸載

控制面板卸載
安裝路徑洗掉 mysql 檔案夾 + 洗掉 C 盤根目錄下 ProgramData下 mysql 檔案夾；
清理注冊表：

A. HKEY_LOCAL_MAACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL 目錄
B. HKEY_LOCAL_MAACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL 目錄
C. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL 目錄
D. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControl001\Services\MYSQL 目錄
E. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControl002\Services\MYSQL 目錄
F. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControlSet\Services\MYSQL 目錄

洗掉 C：\Document and Settings\All Users\Application Data\MySQL 目錄（隱藏的目錄）

`MySQL`服務的啟動和停止

方式一：計算機 -> 管理 -> 服務
方式二：命令列(管理員)

net start 服務名(啟動服務)
net stop 服務名(停止服務)

`MySQL`服務端的登錄和退出

方式一：通過 mysql 自帶的客戶端；只限于 root 用戶
方式二：通過 windows 自帶的客戶端

登錄：mysql [-h 主機名 -P 埠號] -u 用戶名 -p密碼

退出：exit 或者 ctrl + c

`MySQL`常見的命令

查看當前所有資料庫 show databases;
打開指定的庫 use 庫名;
查看當前庫的所有表 show tables;
查看其它庫的所有表 show tables from 庫名;
查看目前位于哪個庫 select database();
創建表

create table 表名(
    列名     列型別，
    列名     列型別，
    ......
);

查看表結構 desc 表名;
查看服務器的版本

方式一：登錄到mysql服務器 select version();
方式二：沒有登錄到mysql服務器 mysql --version

`MySQL`語法規范

不區分大小寫，但建議關鍵字大寫，表名、列名小寫，
每條命名最好用分號或 \G、\g 結尾（會格式化輸出資料）
每條命令根據需要，可以進行縮進或換行，
注釋

單行注釋：# 注釋文字
單行注釋：-- 注釋文字
多行注釋：/* 注釋文字 */

試說出查詢陳述句中涉及到的所有關鍵字，以及執行先后順序

SELECT 查詢串列             7
FROM 表1 別名                第1步
連接型別 join 表2             2
ON 連接條件                 3
WHERE 篩選條件                 4
GROUP BY 分組串列             5
HAVING 分組后的篩選            6
ORDER BY 排序串列             8
LIMIT 偏移，條目數;            9

五、`DQL`(Data Query Language)語言的學習

基礎查詢

語法：

SELECT 查詢串列 FROM 表名;

特點：

1.查詢串列可以是：表中的欄位、常量值、運算式、函式；
2.查詢的結果是一個虛擬的表格；查詢表中的某個欄位

查詢表中的所有欄位

> 方式一：
    SELECT `employee_id`,
        `first_name`,
    FROM
        employees;
> 方式二：
    SELECT * FROM employees;

查詢常量值

SELECT 100;
SELECT 'john';    //字符和日期型的常量值必須用單引號，數值型不需要；

查詢運算式

SELECT 100*99;

查詢函式

SELECT VERSION();
SELECT 函式名(實參串列);

起別名

便于理解；
如果要查詢的欄位有重名的情況，使用別名可以區分開來；
如果別名中含有關鍵詞，用 單引號 或者 雙引號 將別名括起來；

方式一：使用 AS
    SELECT 100%98 AS 結果;
    SELECT last_name AS 姓, first_name AS 名 FROM employees;

方式二：使用空格
    SELECT last_name 姓,first_name 名 FROM employees;

去重 distinct
DISTINCT 需要放到所有列名的前面，
DISTINCT 其實對后面所有列名的組合進行去重，

SELECT DISTINCT 欄位名 FROM 表名;

案例：查詢員工表中涉及到的所有部門編號
    SELECT DISTINCT department_id FROM employees;

concat 函式：和null拼接結果為null

案例：查詢員工名和姓連接成一個欄位，并顯示為 姓名
  SELECT 
      CONCAT(last_name,first_name) AS 姓名
  FROM
      employees;

案例：顯示出表employee的全部列，各個列之間用逗號連接，列頭顯示成OUT_PUT
  SELECT 
      CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct，0)) AS out_put
  FROM
      employees;

ifnull 函式

功能：判斷某欄位或運算式是否為null，如果為null，回傳指定值，否則回傳原本的值
SELECT ifnull(commissiop_pct,0) from employees;

空值參與運算

所有運算子或列值遇到 null 值，運算的結果都為 null，
在 MySQL 里面，空值不等于空字串，一個空字串的長度是 0，而一個空值的長度是空，而且，在 MySQL 里面，空值是占用空間的，

運算子

算數運算子

加法與減法運算子

mysql 中的加號，只有一個功能：運算子，做加法運算，

SELECT 100+90; 兩個運算元都為數值型，則做加法運算

SELECT '123'+90;只要其中一方為字符型，試圖將字符型數值轉成數值型，
                如果轉換成功，則繼續做加法運算，
                如果轉換失敗，則將字符型數值轉換成0

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

乘法與除法運算子

一個數乘以整數1和除以整數1后仍得原數；
個數乘以浮點數1 和除以浮點數1后變成浮點數，數值與原數相等；
一個數除以整數后，不管是否能除盡，結果都為一個 浮點數；
一個數除以另一個數，除不盡時，結果為一個浮點數，并保留到小數點后4位；
乘法和除法的優先級相同，進行先乘后除操作與先除后乘操作，得出的結果相同，
在數學運算中，0不能用作除數，在MySQL中，一個數除以0為NULL，

求模（求余）運算子

結果的符號與被模數的符號一致，

比較運算子

比較運算子用來對運算式左邊的運算元和右邊的運算元進行比較，比較的結果為真則回傳1，比較的結果為假則回傳0，其他情況則回傳 NULL，

等號運算子

如果等號兩邊的值一個是整數，另一個是字串，則MySQL會將字串轉化為數字進行比較，
如果等號兩邊的值、字串或運算式中有一個為NULL，則比較結果為NULL，
使用安全等于運算子時，兩邊的運算元的值都為NULL時，回傳的結果為1而不是NULL，其他回傳結果與等于運算子相同，

不等于運算子

LEAST(值1，值2，...，值n)：在有兩個或多個引數的情況下，回傳最小值，當比較值串列中有NULL時，不能判斷大小，回傳值為NULL，
GREASTEST：最大值運算子，

邏輯運算子

邏輯非運算子邏輯非（NOT或!）運算子表示當給定的值為0時回傳1；當給定的值為非0值時回傳0；當給定的值為NULL時，回傳NULL，
邏輯與運算子邏輯與（AND或&&）運算子是當給定的所有值均為非0值，并且都不為NULL時，回傳1；當給定的一個值或者多個值為0時則回傳0；否則回傳NULL，
邏輯或運算子邏輯或（OR或||）運算子是當給定的值都不為NULL，并且有任何一個值為非0值時，則回傳1，否則回傳0；當一個值為NULL，并且另一個值為非0值時，回傳1，否則回傳NULL；當兩個值都為NULL時，回傳NULL，
邏輯異或運算子邏輯異或（XOR）運算子是當給定的值中任意一個值為NULL時，則回傳NULL；如果兩個非NULL的值都是0或者都不等于0時，則回傳0；如果一個值為0，另一個值不為0時，則回傳1，
AND 的優先級高于OR，

位運算子

條件查詢

語法：

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

分類：
- 按條件運算式篩選
  
  條件運算子： > < = != <> >= <= <=>
- 按邏輯運算式篩選
  
  邏輯運算子: && || !
  
  ? and or not
  
  && 和 and：兩個條件都為 true，結果為 true，反之為 false；
  
  || 和 or：只要有一個條件為 true，結果為 true；
- 模糊查詢
  
  like
  between and
  in
  is null / is not null

按條件運算式篩選

#案例1：查詢工資>12000的員工資訊
    SELECT
        *
    FROM
        employees
    WHERE
        salary>120000;

#案例2：查詢部分編號不等于90的員工名和部門編號
    SELECT
        last_name,
        department_id
    FROM
        employees;
    WHERE
        department_id<>90;

按邏輯運算式篩選

#案例一：查詢工資在10000到20000之間的員工名、工資以及獎金
    SELECT
        last_name,
        salary,
        commission_pct
    FROM
        employees
    WHERE
        salary >= 10000 
    AND
        salary <= 20000

模糊查詢

一般和通配符搭配使用，可以判斷 字符型 或 數值型；

通配符：%s任意多個字符，_任意單個字符

#案例1：查詢員工名中包含符a的員工資訊
    SELECT
        *
    FROM
        employees
    WHERE
        last_name LIKE '%a%';

#案例2：查詢員工名中第三個字符為e，第五個字符為a的員工名和工資
    SELECT
        last_name,
        salary,
    FROM
        employees
    WHERE
        last_name LIKE '__e_a%';

#案例3：查詢員工名中第二個字符為_的員工名
    SELECT 
        last_name
    FROM 
        employees
    WHERE
        last_name LIKE '_\_%';    或者        last_name LIKE '_$_%' ESCAPE '$';

between and

使用 between and 可以提高陳述句的簡介度；
包含臨界值；
兩個臨街值不要調換順序；

案例1：查詢員工編號在100到120之間的員工資訊
    SELECT
        *
    FROM
        employees
    WHERE
        employee_id BETWEEN 100 AND 120;

in

含義：判斷某欄位的值是否屬于 in 串列中的某一項；

使用 in 提高陳述句簡介度；
in 串列的值型別必須一致或兼容；
不支持通配符；

案例：查詢員工的工種編號是 IT_PROG、AD_VP、AD_PRES中的一個員工名和工種編號，
    SELECT
        last_name,
        job_id
    FROM
        employees
    WHERE    
        job_id IN ('IT_PROT','AD_VP','AD_PRES');

is null

判斷某欄位或運算式是否為 null，如果是，則回傳1，否則回傳0，

= 或 <> 不能用于判斷 null 值；
is null 或 is not null 可以判斷null值；

案例1：查詢沒有獎金的員工名和獎金率
    SELECT
        last_name,
        commission_pct
    FROM
        employees
    WHERE
        commission_pct IS NULL;        IS NOT NULL

安全等于 <=>

案例1：查詢沒有獎金的員工名和獎金率
    SELECT
        last_name,
        commision_pct
    FROM
        employees
    WHERE
        commission_pct <=> NULL;

案例2：查詢工資為12000的員工名和獎金率
    SELECT
        last_name,
        commision_pct
    FROM
        employees
    WHERE
        salary <=> 12000;

is null pk <=>
- IS NULL：僅僅可以判斷NULL值，可讀性較高，建議使用；
- <=>：既可以判斷 NULL 值，又可以判斷普通的數值，可讀性較低，
經典面試題：

試問，select * from employees;和select * from employees where commission_pct like '%%' and last_name like '%%';結果是否一樣？并說明原因，

答：不一樣，如果判斷的欄位有null值，

排序查詢

語法：

SELECT     查詢串列
FROM     表
[WHERE     篩選條件]
order by 排序串列 [asc/desc]

特點：

asc 代表的是升序，desc 代表的是降序；如果不寫，默認是升序，
order by 子句中可以支持單個欄位、多個欄位、運算式、函式、別名，
order by 后面的列必須是在 select 后面存在的，
order by 子句一般是放在查詢陳述句的最后面，limit 子句除外，
可以使用列的別名進行排序，但別名只能在 order by 中使用，不能在 WHERE 中使用，

#案例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_prt,0)) 年薪
 FROM employees
 ORDER BY salary*12*(1+IFNULL(commission_prt,0)) DESC; 或 ORDER BY 年薪 DESC;

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

#案例6：查詢員工資訊，要求先按工資升序，再按員工編號降序[按多個欄位排序]
 SELECT *
 FROM employees
 ORDER BY salary ASC,employee_id DESC;

常見函式

select 函式名(實參串列) [from 表]
(1) 單行函式
    concat、length、ifnull等
(2) 分組函式
    做統計使用，又稱為統計函式、聚合函式、組函式

單行函式

操作資料物件
接受引數回傳一個結果
只對一行進行變換
每行回傳一個結果
可以嵌套
引數可以是一列或一個值

字符函式

注意：MySQL中，字串的位置是從1開始的，

函式	作用
ASCII(S)	回傳字串 S 中的第一個字符的 ASCII 碼值
CHAR_LENGTH(s)	回傳字串s的字符數，作用與CHARACTER_LENGTH(s)相同
LENGTH(s)	回傳字串s的個數，和字符集有關
CONCAT(s1,s2,...,sn)	連接s1,s2,......sn 為一個字串
CONACT_WS(x,s1,s2,......,sn)	同CONCAT(s1,s2,...)函式，但是每個字串之間要加上x
INSERT(str,idx,len,replacestr)	將字串str從第idx位置開始，len個字符長的子串替換為字串replacestr
REPLACE(str,a,b)	用字串b替換字串str中所有出現的字串a
UPPER(s) 或 UCASE(s)	將字串s的所有字母都轉換成大寫字母
LOWER(s) 或 LCASE(s)	將字串s的所有字母都轉成小寫字母
LEFT(str,n)	回傳字串str最左邊的n個字符
RIGHT(str,n)	回傳字串str最右邊的n個字符
LPAD(str,len,pad)	用字串pad對str最左邊進行填充，直到str的長度為len個字符
RPAD(str,len,pad)	用字串pad對str最右邊進行填充，直到str的長度為len個字符
LTRIM(s)	去掉字串s左側的空格
RTRIM(s)	去掉字串s右側的空格
TRIM(s)	去掉字串s開始與結尾的空格
TRIM(s1 FROM s)	去掉字串s開始與結尾的s1
TRIM(LEADING s1 FROM s)	去掉字串s開始處的s1
TRIM(TRAILING s1 FROM s)	去掉字串s結尾處的s1
REPEAT(str,n)	回傳str重復n次的結果
SPACE(n)	回傳n個空格
STRCMP(s1,s2)	比較字串s1,s2的ASCII碼值的大小
SUBSTR(s,index,len)	回傳從字串s的index位置其len個字符，作用與SUBSTING(s,n,len)相同
LOCATE(substr,str)	回傳字串 substr 在字串 str 中首次出現的位置，作用與 POSITION(substr IN str)、INSTR(str,substr) 相同，未找到，回傳0
ELT(m,s1,s2,......,sn)	回傳指定位置的字串，如果m=1，則回傳s1，如果m=2，則回傳s2，如果m=n，則回傳sn
FIELD(s,s1,s2,...,sn)	回傳字串s在字串串列中第一次出現的位置
FIND_IN_SET(s1,s2)	回傳字串s1在字串s2中出現的位置，其中，字串s2是一個以逗號分隔的字串
REVERSE(s)	回傳s反轉后的字串
NULLIF(value1,value2)	比較兩個字串，如果value1與value2相等，則回傳NULL，否則回傳value

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

SELECT LENGTH('張三豐hahaha');

SHOW VARIVALE LIKE '%char%'; 查看字符集

concat()：拼接字串

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

upper()、lower()

substr()、substring()：

注意：索引從1開始

# 截取從指定索引處后面所有字符
    SELECT SUBSTR('李莫愁愛上了路站元',7) out_put;

# 截取從指定所引處指定字符長度的字符
    SELECT SUBSTR('李莫愁愛上了路站元',1,3) out_put;

#案例：姓名中首字符大寫，其他字符小寫，然后用 _ 拼接，顯示出來
SELECT CONTACT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name),2)) out_put;

instr()：回傳字串第一次出現的索引，如果找不到回傳0

trim()：

SELECT TRIM('a' FROM 'aaaaa張aa翠山aaaaaa') AS out_put;

lpad()：用指定的字符實作左填充指定長度

SELECT LPAD('殷素素',10,'*') AS out_put;     //10為總字符數
SELECT LPAD('殷素素',2,'*') AS out_put;     //殷素

rpad()：用指定的字符實作右填充指定長度
```
SELECT RPAD('殷素素',12,'ab') AS out_put;
```
replace()：替換

數學函式

round()：四舍五入

SELECT ROUND(1.65);
SELECT ROUND(1.567,2);         // 1.57

ceil()：向上取整，回傳 >= 該引數的最小整數
floor()：向下取整，回傳 <= 該引數的最大整數
truncate()：截斷
```
SELECT TRUNCATE(1.6999,1);    //1.6
```
mod()：取余
```
mod(a,b) = a-a/b*b
```
rand()：獲取亂數，回傳 0-1 之間的小數

日期函式

now()：回傳當前系統日期+時間
```
SELECT NOW();
```
curdate()：回傳當前系統日期，不包含時間

curtime()：回傳當前時間，不包含日期
可以獲取指定的部分，年、月、日、小時、分鐘、秒
```
SELECT YEAR(now());
SELECT YEAR('1998-1-1') 年;
```
str_to_date：將日期格式的字符轉換成指定格式的日期
```
STR_TO_DATE('9-13-1999','%m-%d-%Y')        1999-09-13
```

date_format：將日期格式轉換成字符

DATE_FORMAT('2018/6/6','%Y年%m月%d日')    2018年06月06日

year()、month()、day()、hour()、minute()、second()
datediff()：回傳兩個日期相差的天數
monthname()：以英文形式回傳月

其他函式

SELECT VERSION();
SELECT DATABASE();
SELECT USER();
SELECT password('字符')：回傳該字符的密碼形式 自動加密
MD5('字符')：回傳該字符的md5加密形式

流程控制函式

if 函式：if else 的效果

if(條件運算式,運算式1,運算式2)：如果條件運算式成立，回傳運算式1;否則回傳運算式2

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'沒獎金，呵呵','有獎金，嘻嘻') 備注
FROM employees;

case函式的使用一：switch case 的效果

case 要判斷的欄位或運算式
when 常量1 then 要顯示的值1或陳述句1;
when 常量2 then 要顯示的值2或陳述句2;
...
else 要顯示的值n或陳述句n;
end

#案例：要查詢員工的工資，要求    
部門號=30，顯示的工資為1.3倍
部門號=40，顯示的工資為1.4倍
部門號=50，顯示的工資為1.5 倍
其他部門，顯示的工資為原工資

SELECT salary 原始工資,department_id,
    CASE department_id
    WHEN 30 THEN salary*1.3
    WHEN 40 THEN salary*1.4
    WHEN 50 THEN salary*1.5
    ELSE salary
    END AS 新工資
    FROM employees;

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;

加密與解密函式

函式	用法
PASSWORD(str)	回傳字串str的加密版本，41位長的字串，加密結果不可逆，常用語用戶的密碼加密，在mysql8.0中被棄用，
MD5(str)	回傳字串str的md5加密后的值，也是一種加密方式，若引數為NULL，則會回傳NULL，不可逆，
SHA(str)	從原明文密碼str計算并回傳加密后的密碼字串，當引數為 NULL 時，回傳 NULL，不可逆，
ENCODE(value,password_seed)	回傳使用 password_seed 作為加密密碼加密value，mysql8.0后被棄用，
DECODE(value,password_seed)	回傳使用password_seed作為加密密碼解密value

mysql 資訊函式

函式	用法
VERSION()	回傳當前MySQL的版本號
CONNECTION_ID()	回傳當前MySQL服務器的連接id
DATABASE()，SCHEMA()	回傳MySQL命令列當前所在的資料庫
USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()	回傳當前連接MySQL的用戶名，回傳結果格式為 “主機名@用戶名”
CHARSET(value)	回傳字串value自變數的字符集
COLLATION(value)	回傳字串value的比較規則

其他函式

函式	用法
FORMAT(value,n)	回傳對數字value進行格式化后的結果資料，n表示四舍五入后保留到小數點后n位
CONV(value,from,to)	將value的值進行不同進制之間的轉換
INET_ATON(ipvalue)	將以點分隔的IP地址轉化為一個數字
INET_NTOA(value)	將數字形式的IP地址轉化為以點分隔的IP地址
BENCHMARK(n,expr)	將運算式expr重復執行n次，用于測驗MySQL處理expr運算式所耗費的時間
CONVERT(value USING char_code)	將value所使用的字符編碼修改為char_code

分組函式

功能：用作統計使用，又稱為聚合函式或統計函式或組函式，輸入的是一組資料的集合，輸出的是單個值，
分類：sum 求和、 avg 平均值、 max 最大值、 min 最小值、 count 計算個數；
特點：
sum、avg一般用于處理數值型；max、min、count 可以處理任何型別；
以上分組函式都忽略 null 值；
可以和 distinct 搭配實作去重的運算；

```sql
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
```

count函式的詳細介紹，一般使用 COUNT(*) 統計行數；

```sql
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;    //只要某一行有一個不為null，就相當于+1(不包含NULL值)
SELECT COUNT(1) FROM employees; //統計1的個數，相當于計算行數

效率：
    如果使用的是 MyISAM 存盤引擎，則三者效率相同，都是O(1)
    如果使用的是 InnoDB 存盤引擎，則三者效率：COUNT(*) = COUNT(1) > COUNT(欄位)
```

和分組函式一同查詢的欄位要求是 group by 后面的欄位；

```
SELECT AVG(salary),employee_id FROM employees; 有問題，AVG(salary)只有一行，employee_id有很多行
```

分組查詢

語法

                                                 #執行順序
SELECT         column，group_function(column)           5
FROM         table                                    1
[WHERE         condition]                                2
GROUP BY     group_by_expression                     3
[HAVING     分組后的篩選]                                4
[ORDER BY     column];                                 6

明確：WHERE 一定放在 FROM 后面；
注意：查詢串列必須特殊，要求是分組函式和 group by 后出現的欄位；
當使用 ROLLUP 時，不能同時使用 ORDER BY 子句進行結果排序，即 ROLLUP 和 ORDER BY 是互相排斥的，

為了更好的理解 grouy by 多個列和聚合函式的應用，我們可以假設在group by執行之后，生成了一個虛擬的中間表，相同的group by欄位合并成一行，其余的欄位分別寫到一個單元格里，
對于id和number里面的單元格有多個資料的情況，使用聚合函式，聚合函式就是用來輸入多個資料，輸出一個資料的，
#對name欄位進行分組
id    name    number
1    aa        2
2            3

3            4
4    bb        5
8            6

特點：
分組查詢中的篩選條件分為兩類：

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

group by 子句支持單個欄位分組，多個欄位分組(多個欄位之間用逗號隔開，沒有順序要求)，運算式或函式(用的較少)；
也可以添加排序(排序放在整個分組查詢的最后)；
從 mysql8.0 開始，group by不再支持隱式排序，
當 group by 這列有 null 值時，group 會把他們當成是同一個直接聚合，
如果過濾條件中使用了聚合函式，則必須使用 HAVING 來替換 WHERE，否則，報錯，
案例：

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

#案例2：查詢每個位置上的部門個數
    SELECT COUNT(*),location_id
    FROM employees
    GROUP BY location_id;

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

#案例4：查詢有獎金的每個領導手下員工的最高工資
    SELECT MAX(salary),manager_id
    FROM employees
    WHERE commission_pct IS NOT NULL
    GROUP BY manager_id;

#案例5：查詢哪個部門的員工個數>2
(1) 查詢每個部門員工個數
    SELECT COUNT(*),department_id
    FROM employees
    GROUP BY department_id;
(2) 根據(1)的結果進行篩選，查詢哪個部門的員工個數>2
    SELECT COUNT(*),department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*)>2;

#案例：查詢每個工種有獎金的員工的最高工資>12000的工種編號和最高工資
(1)查詢每個工種有獎金的員工的最高工資
    SELECT MAX(salary),job_id
    FROM employees
    WHERE commission_pct IS NOT NULL
    GROUP BY job_id;

(2)根據(1)結果繼續篩選，最高工資>12000
    SELECT MAX(salary),job_id
    FROM employees
    WHERE commission_pct IS NOT NULL
    GROUP BY job_id
    HAVING MAX(salary)>12000;

#案例：查詢領導編號>102的每個領導手下的最低工資>5000的領導編號是哪個，以及其最低工資；
(1)查詢每個領導手下的員工最低工資
    SELECT MIN(salary),manager_id
    FROM employees
    GROUP BY manager_id;
(2)添加篩選條件：編號>102
    SELECT MIN(salary),manager_id
    FROM employees
    WHERE manager_id > 102
    GROUP BY manager_id;
(3)添加篩選條件：最低工資>5000
    SELECT MIN(salary),manager_id
    FROM employees
    WHERE manager_id > 102
    GROUP BY manager_id
    HAVING MIN(salary)>5000;

     > 按運算式或函式分組
             > 案例：按員工姓名的長度分組，查詢每一組的員工個數，篩選員工個數>5的的有哪些
                 (1)查詢每個長度的員工個數
                     SELECT COUNT(*),LENGTH(last_name)
                     FROM employees
                     GROUP BY LENGTH(last_name);
                 (2)添加篩選條件
                     SELECT COUNT(*),LENGTH(last_name)
                     FROM employees
                     GROUP BY LENGTH(last_name)
                     HAVING COUNT(*)>5;
     > 按多個欄位分組
         #案例：查詢每個部門每個工種的員工的平均工資
             SELECT AVG(salary),department_id,job_id
             FROM employees
             GROUP BY job_id,department_id;

         #添加排序
        #案例：查詢每個部門每個工種的員工的平均工資，并且按平均工資的高低顯示
            SELECT AVG(salary),department_id,job_id
             FROM employees
             WHERE department_id IS NOT NULL
             GROUP BY job_id,department_id
             ORDER BY AVG(salary) DESC;

連接查詢

含義：又稱多表查詢，當查詢的欄位來自多個表時；
笛卡爾乘積現象：表1有 m 行，表2有 n 行，結果 m*n 行

發生原因：沒有有效的連接條件；

如何避免：添加有效的連接條件；

分類：

按年代分類：

sql92標準：僅僅支持內連接
    等值
    非等值
    自連接
    也支持一部分外連接(用于 oracle、sqlserver，mysql 不支持)
     sql99標準(推薦)：支持內連接+外連接(左外和右外)+交叉連接

按功能分類：

內連接
等值連接
非等值連接
自連接
外連接
左外連接
右外連接
全外連接(mysql不支持)
交叉連接

sql92 標準

等值連接

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

語法：

SELECT 查詢串列
FROM 表1 別名, 表2 別名
WHERE 表1.key = 表2.key
[AND 篩選條件]
[GROUP BY 分組欄位]
[HAVING 分組后的篩選]
[ORDER BY 排序欄位]

案例：

#案列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`;

為表起別名
提高陳述句的簡潔度；
區分多個重名的欄位；
注意：如果為表起了別名，則查詢的欄位就不能使用原來的表名去限定；

#案例：查詢員工名、工種號、工種名
    SELECT e.last_name,e.job_id,j.job_title     #employees.last_name錯誤
    FROM employees AS e,jobs j
    WHERE e.`job_id` = j.`job_id`;

兩個表的順序可以調換
加篩選

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

#案例2:查詢城市名中第二個字符為o的部門名和城市名
    SELECT dapartment_name,city
    FROM departments d,location l
    WHERE d.`location_id` = l.`location_id`
    AND city LIKE `_o%`;

加分組

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

#案例2：查詢有獎金的部門的部門名和部門的領導編號和該部門的最低工資
    SELECT department_name,d.`manager_id`,MIN(salary)
    FROM dapartment d,employees e
    WHERE d.`department_id` = e.`department_id`
    AND commission_pct IS NOT NULL
    GROUP BY department_name,d.mamager_id;

加排序

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

三表連接

#案列：查詢員工名、部門名和所在的城市
    SELECT last_name,department_name,city
    FROM employees e,department d,location l
    WHERE e.`department_id` = d.`department_id`
    AND d.`location_id` = l.`location_id`

    AND city LIKE 's%'
    ORDER BY department_name DESC;

非等值連接

SELECT     查詢串列
FROM     表1 別名, 表2 別名
WHERE     非等值的連接條件
[AND     篩選條件]
[GROUP BY 分組欄位]
[HAVING 分組后的篩選]
[ORDER BY 排序欄位]

#案例1：查詢員工的工資和工資級別
    SELECT salary,grade_level
    FROM employess e,job_grades g
    WHERE salary BETWEEN g.`lowest_sal` AND g.`hightest_sal`
    //AND g.`grade_level` = 'A';

自連接

#案例：查詢員工名和上級的名稱
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 別名 [連接型別]
JOIN 表2 別名
ON     連接條件
[WHERE 篩選條件]
[GROUP BY 分組]
[HAVING 篩選條件]
[ORDER BY 排序串列]

內連接：inner

SELECT 查詢串列
FROM 表1 別名
INNER JOIN 表2 別名
ON 連接條件;

分類：等值、非等值、自連接

等值連接

特點：

添加排序、分組、篩選；
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：查詢員工名字中包含e的員工名和工種名(添加篩選)
    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的城市名稱和部門個數(添加分組+篩選)
#(1)查詢每個城市部門的個數
#(2)在(1)結果上篩選滿足條件的
    SELECT city,COUNT(*) 部門個數
    FROM departments d
    INNER JOIN locations l
    ON d.`location_id` = l.`location_id`
    GROUP BY city
    HAING COUNT(*)>3;

#案例4：查詢哪個部門的員工個數>3的部門名和員工個數，并按個數降序(添加排序)
#(1)查詢每個部門的員工個數
    SELECT COUNT(*),department_name
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id` = d.`department_id`
    GROUP BY department_name;
#(2)在(1)結果上篩選員工個數>3的記錄，并排序
    SELECT COUNT(*),department_name
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id` = d.`department_id`
    GROUP BY department_name
    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`
    ODER BY department_name DESC;

非等值連接

#案例1：查詢員工的工資級別
    SELECT salary,grade_level
    FROM employees e
    JOIN job_grade e
    ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#案例2：查詢工資級別的個數>20的個數，并且按工資級別降序
    SELECT COUNT(*),grade_level
    FROM employees e
    JOIN job_grade e
    ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
    GROUP BY grade_level
    HAVING COUNT(*)>20
    ORDER BY grade_level DESC;

自連接

#案例：查詢姓名中包含字符k的員工的名字、上級的名字
    SELECT e.last_name,m.last_name
    FROM employees e
    JOIN employees m
    ON e.`manager_id` = m.`manager_id`
    WHERE e.`last_name` LIKE '%k%';

外連接

左外: left [outer]
右外: right [outer]
全外: full [outer]

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

特點：

外連接的查詢結果為主表中的所有記錄；

如果從表中有和它匹配的，則顯示匹配的值；

如果從表中沒有和它匹配的，則顯示 null；

外連接查詢結果=內連接結果 + 主表中有而從表中沒有的記錄；

左外連接，left join 左邊的是主表；
右外連接，right join 右邊的是主表；

全外連接，兩邊都是主表，

左外和右外交換兩個表的順序，可以實作同樣的效果；
全外連接=內連接的結果+表1中有但表2沒有的+表2中有的但表1沒有的；

#案例：查詢男朋友 不在男明星表的女明星名
    SELECT b.name,bo.*
    FROM beauty b
    LEFT OUTERF JOIN boys bo
    ON b.`boyfriend_id` = bo.`id`
    WHERE bo.`id` IS NULL;  //問題：為什么用boys.id為NULL就可以了呢，實際上的boys表里的id是主鍵，根本不可能為NULL啊？

#案例1：查詢哪個部門沒有員工
#左外
    SELECT d.*,e.employee_id
    FROM departments d
    LEFT OUTER JOIN employees e
    ON d.`department_id`=e.`department_id`
    WHERE e.`employee_id` IS NULL;

#案例2：查詢哪個城市沒有部門
    SELECT city
    FROM department d
    RIGHT OUTER JOIN locations l
    ON d.location_id = l.location_id
    WHERE d.department_id IS NULL;

#案例3：查詢部門為SAL或IT的員工資訊
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');

交叉連接：cross

SELECT 查詢串列
FROM 表1 別名
CROSS JOIN 表2 別名;

#特點：類似于笛卡爾乘積

sql99 語法新特性

自然連接

NATURAL JOIN 用來表示自然連接，我們可以把自然連接理解為 SQL92 中的等值連接，它會幫你自動查詢兩張連接表中所有相同的欄位，然后進行等值連接，

#sql92中：
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

#在sql99中可寫成：
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING 連接

當我們進行連接的時候，SQL99還支持使用 USING 指定資料表里的同名欄位進行等值連接，但是只能配合JOIN一起使用，比如：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

子查詢

含義：出現在其他陳述句中的 select 陳述句，稱為子查詢或內查詢；
外部的查詢陳述句，稱為主查詢或外查詢，
外面的陳述句可以是 insert、update、delete、select 等，一般 select 作為外面陳述句較多，

分類：

按子查詢出現的位置：

select 后面：
- 僅僅支持標量子查詢
from 后面：
- 支持表子查詢
where 或 having 后面
- 標量子查詢(單行)
- 列子查詢 (多行)
- 行子查詢
exists 后面(相關子查詢)（回傳的結果1或0，類似布爾操作）
- 表子查詢

按結果集的行列數不同：

標量子查詢(單行子查詢)(結果集是一個資料：一行一列)
列子查詢(多行子查詢)(結果集是一列：一列多行)：一般搭配著多行運算子使用 in、any/some、 all
行子查詢(結果集是一行：一行多列)
表子查詢(結果集一般為多行多列)

where 或 haing 后面

標量子查詢
列子查詢
行子查詢

特點：

子查詢放在小括號內
子查詢一般放在條件的右側
標量子查詢，一般搭配著單行運算子使用 > < >= <= = <>
子查詢的執行順序優于主查詢執行，主查詢的條件用到了子查詢的結果

標量子查詢

#案例1：誰的工資比Abel高？
#(1)查詢Abel的工資
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
#(2)查詢員工資訊，滿足 salary>(1)結果
    SELECT *
    FROM employees
    WHERE salary>(
        SELECT salary
        FROM employees
        WHERE last_name = 'Abel'
    );

#案例2：回傳job_id與141號員工相同，salary比143號員工多的員工姓名，job_id和工資
#(1)查詢141號員工的job_id
    SELECT job_id
    FROM employees
    WHERE employee_id = 141
#(2)查詢143號員工的salary
    SELECT salary
    FROM employees
    WHERE employee_id = 143
#(3)查詢員工的姓名、job_id和工資，要求job_id=(1)并且salary>(2)
    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
#(1)查詢公司的最低工資
    SELECT MIN(salary)
    FROM employees;
#(2)查詢last_name，job_id和salary，要求salary=(1)
    SELECT last_name,job_id,salary
    FROM employees
    WHERE salary = (
        SELECT MIN(salary)
        FROM employees;
    );

#案例4：查詢最低工資大于50號部門最低工資的部門id和其最低工資
#(1)查詢50號部門的最低工資
    SELECT MIN(salary)
    FROM employees
    WHERE department_id =50
#(2)查詢每個部門的最低工資
    SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
#(3)在(2)的基礎上，滿足min(salary)>(1)
    SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING MIN(salary)>(
        SELECT MIN(salary)
        FROM employees
        WHERE department_id =50
    );

非法使用標量子查詢：

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

列子查詢(多行子查詢)

回傳多行
使用多行比較運算子

運算子	含義
IN / NOT IN	等于串列中的任意一個
ANY \| SOME	和子查詢回傳的某一個值比較
ALL	和子查詢回傳的所有值比較

#案例1：回傳 location_id 是 1400 或 1700 的部門中所有員工姓名
#(1)查詢location_id是1400或1700的部門編號
    SELECT DISTINCT department_id
    FROM departments
    where location_id IN(1400,1700)
#(2)查詢員工姓名，要求部門號是(1)串列中的某一個
    SELECT last_name
    FROM employees
    WHERE deapartment_id IN(
        SELECT DISTINCT department_id
        FROM departments
        where location_id IN(1400,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<(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 MAX(salary)
        FROM employees
        WHERE job_id='IT_PROG'
    )AND job_id<>'IT_PROG';

行子查詢(結果集一行多列或多行多列)

#案例：查詢員工編號最小且工資最高的員工資訊
#(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
        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：查詢每個部門的平均工資的工資等級
#(1)查詢每個部門的平均工資
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
#(2)連接(1)的結果集和job_grade表，篩選條件平均工資between lowest_sal and 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_grade g
    ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

#案例2：查詢各部門中工資比本部門平均工資高的員工的員工號，姓名和工資
#(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;

exists后面(相關子查詢)

語法：
    exists(完整的查詢陳述句)            問題：exists(NULL)？
結果：
    1或0

#案例1：查詢有員工的部門名
    SELECT department_name
    FROM departments d
    WHERE EXISTS(
        SELECT *
        FROM employees e
        WHERE e.`department_id`= d.`department_id`
    );
#in
    SELECT department_name
    FROM departments d
    WHERE d.`deparment_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 bo.id = b.boyfriend_id
    );

子查詢經典案例

# 1.查詢工資最低的員工資訊：last_name，salary
#(1)查詢最低的工資
    SELECT MIN(salary)
    FROM employees
#(2)查詢last_name，salary，要求salary=(1)
    SELECT last_name，salary
    FROM employees
    WHERE salary = (
        SELECT MIN(salary)
        FROM employees
    )



# 2.查詢平均工資最低的部門資訊
#方法一：
    #(1)各部門的平均工資
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    #(2)查詢(1)結果上的最低平均工資
    SELECT MIN(ag)
    FROM (
        SELECT AVG(salary) ag,department_id
        FROM employees
        GROUP BY department_id
    ) ag_dep
    #(3)查詢哪個部門的平均工資=(2)
    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
    )
    #(4)查詢部門資訊
    SELECT d.*
    FROM departments d
    WHERE d,department = (
        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
        )
    )

#方法二：
    #(1)各部門的平均工資
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    #(2)求出最低平均工資的部門編號
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 1;



# 3.查詢平均工資最低的部門資訊和該部門的平均工資
#(1)各部門的平均工資
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
#(2)求出最低平均工資的部門編號和平均工資
    SELECT department_id,AVG(salary)
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 1;
#(3)查詢部門資訊（內連接）
    SELECT d.*,
    FROM departments d
    INNER JOIN (
        SELECT department_id,AVG(salary) ag
        FROM employees
        GROUP BY department_id
        ORDER BY AVG(salary)
        LIMIT 1;
    )ag_dep
    ON d.department_id = ag_dep.department_id;


# 4.查詢平均工資最高的 job 資訊
#(1)查詢每個job的平均工資
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id
#(2)在(1)的結果上獲取平均工資最高的job
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id
    ORDER BY AVG(salary) DESC
    LIMIT 1
#(3)查詢job資訊
    SELECT *
    FROM jobs
    WHERE job_id = (
        SELECT job_id
        FROM employees
        GROUP BY job_id
        ORDER BY AVG(salary) DESC   #order by后面存在的非聚合列必須在select后面出現
        LIMIT 1
    )


# 5.查詢平均工資高于公司平均工資的部門有哪些
#(1)查詢公司的平均工資
    SELECT AVG(salary)
    FROM employees
#(2)查詢每個部門的平均工資
    SELECT AVG(salary)
    FROM employees
    GROUP BY department_id
#(3)篩選(2)結果集，滿足平均工資>(1)
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) > (
        SELECT AVG(salary)
        FROM employees
    )


# 6.查詢出公司中所有 manager 的詳細資訊
#(1)查詢所有manager的員工編號
    SELECT DISTINCT manager_id
    FROM employees
#(2)查詢詳細資訊，滿足employee_id=(1)
    SELECT *
    FROM employees
    WHERE employee_id = ANY(
        SELECT DISTINCT manager_id
        FROM employees
    )


# 7.各個部門中，最高工資中最低的那個部門的最低工資是多少
#(1)查詢各部門的最高工資中最低的
    SELECT MAX(salary),department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary) ASC
    LIMIT 1
#(2)查詢(1)結果的那個部門的最低工資
SELECT MIN(salary),department_id
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary) ASC
    LIMIT 1
)


# 8.查詢平均工資最高的部門的 manager 的詳細資訊：last_name，department_id，email，salary
#(1)查詢平均工資最高的部門編號
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) DESC
    LIMIT 1
#(2)將employees和departments表連接查詢，篩選條件是(1)
    SELECT last_name，d.department_id，email，salary
    FROM employeese e
    INNER JOIN departments d
    ON d.department_id = e.department_id
    WHERE d.department_id = (
        SELECT department_id
        FROM employees
        GROUP BY department_id
        ORDER BY AVG(salary) DESC
        LIMIT 1
    )

分頁查詢

應用查詢：當要顯示的資料，一頁顯示不全，需要分頁提交 sql 請求，

語法：

    SELECT 查詢串列                    7
    FROM 表                          1
    [JOIN type join 表2              2
    ON 連接條件                        3
    WHERE 篩選條件                    4
    GROUP BY 分組欄位                5
    HAVING 分組后的篩選               6
    ORDER BY 排序的欄位]                8
    LIMIT offset,size;               9
    # offset 要顯示條目的起始索引(起始索引從0開始)
    # size 要顯示的條目個數

根據執行順訊，可以得出：
1. order by 后面的列必須是在 select 后面存在的；
2. select、having 或 order by 后面存在的非聚合列必須全部在 group by 中存在，

特點：

limit 陳述句放在查詢陳述句的最后
公式

#要顯示的頁數page    每頁的條目數size
    SELECT 查詢串列
    FROM 表
    LIMIET (page-1)*size,size;

#案例1：查詢前五條員工資訊
    SELECT * FROM employees LIMIT 0,5;
    或
    SELECT * FROM employees LIMIT 5;

#案例2：查詢第11條——第25條
    SELECT * FROM employees LIMIT 10,15;

union 聯合查詢

union：將多條查詢陳述句的結果合并成一個結果

語法：

查詢陳述句1
union [all]
查詢陳述句2
union [all]
......



#案例：查詢部門編號>90或郵箱包含a的員工資訊
    SELECT * FROM employees WHERE email LIKE '%a%'
    UNION
    SELECT * FROM employees WHERE department_id >90;

應用場景：要查詢的結果來自于多個表，且多個表沒有直接的連接關系，但查詢的資訊一致時，

特點：

要求多條查詢陳述句的查詢列數是一致的；
要求多條查詢陳述句的查詢的每一列的型別和順序最好一致；
union 關鍵字默認去重，如果使用 union all 可以包含重復項；

SELECT 的執行程序

查詢的結構

SELECT .....
FROM ... JOIN ...
ON 多表的連接條件
JOIN ...
ON ...
WHERE 不包含組函式的濾條件
AND/OR 不包含組函式的過濾條件
GROUP BY ...,...
HAVING 包含組函式的過濾條件
ORDER BY ... ASC/DESC
LIMIT ...

SELECT 執行順序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的欄位 -> DISTINCT -> ORDER BY -> LIMIT

在 SELECT 陳述句執行這些步驟的時候，每個步驟都會產生一個 虛擬表，然后將這個虛擬表傳入下一個步驟中作為輸入，

SQL 的執行原理

首先先通過 CROSS JOIN 求笛卡爾積，相當于得到虛擬表 vt（virtual table） 1-1；
通過 ON 進行篩選，在虛擬表 vt1-1 的基礎上進行篩選，得到虛擬表 vt1-2；
添加外部行，如果我們使用的是左連接、右連接或者全連接，就會涉及到外部行，也就是在虛擬表 vt1-2 的基礎上增加外部行，得到虛擬表 vt1-3，
當我們拿到了查詢資料表的原始資料，也就是最終的虛擬表 vt1，就可以在此基礎上再進行 WHERE 階段，在這個階段中，會根據 vt1 表的結果進行篩選過濾，得到虛擬表 vt2.
......

六、`DML`語言的學習

插入陳述句

方式一：經典的插入

語法：

insert into 表名(列名,....) values(值1,...)

特點：

插入的值的型別要與列的型別一致或兼容；
不可以為 null 的列必須插入值，可以為 Null 的列如何插入值：

方式一：對應的 value 填 null；
方式二：欄位和值都省略；

欄位的個數和順序不一定與原始表中的欄位個數和順序一致，但必須保證值和欄位一一對應；
列數和值的個數必須一致；
可以省略列名，默認所有列，而且列的順序和表中列的順序一致；

方式二：

insert into 表名 set 列名=值,列名=值,...

兩種方式大pk

方式一支持插入多行，方式二不支持

INSERT INTO beauty VALUES(23,'唐藝昕'...),(24,'唐藝昕2'...);

方式一支持子查詢，方式二不支持

INSERT INTO 表名 查詢陳述句;

INSERT INTO beauty(id,name,phone)
SELECT 26,'劉亦菲','11908425';

修改陳述句

修改單表的記錄

語法：

update 表名                1
set 列=新值,列=新值...      3
[where 篩選條件];           2

修改多表的記錄(補充)

sql92 語法：

update 表1 別名,表2 別名
set 列=值,...
where 連接條件
and 篩選條件;

sql99 語法:

update 表1 別名
inner|left|right join 表2 別名
on 連接條件
set 列=值,...
where 篩選條件;

#案例：修改沒有男朋友的女神的男朋友編號都為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 篩選條件] [limit 條目數]

#案例：洗掉手機號以9結尾的女神系資訊
    DELETE FROM beauty WHERE phone LIKE '%9';

多表的洗掉

sql92 語法

delete [表1的別名,表2的別名] #如果要洗掉表1，就寫表1的別名；要刪表2，就寫表2的別名；兩個都刪，寫兩個表的別名，
from 表1 別名,表2 別名
where 連接條件
and 篩選條件

sql99 語法

delete [表1的別名,表2的別名]
from 表1 別名
inner|left|right join 表2 別名 on 連接條件
where 篩選條件

#案例1：洗掉張無忌的女朋友的資訊
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='張無忌'; 


#案例2：洗掉黃曉明的資訊以及他女朋友的資訊
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.boyName = '黃曉明';

方式二：truncate (清空)

truncate table 表名;

delete PK truncate (重點)

delete 可以添加 where 條件，truncate 不能加；
truncate 洗掉，效率高一丟丟；
假如要洗掉的表中有自增長列，如果用 delete 洗掉后，再插入資料，自增長列的值從斷點開始，而 truncate 洗掉后，再插入資料，自增長列的值從1開始；
truncate 洗掉沒有回傳值，delete 洗掉有回傳值；
truncate 洗掉不能回滾，delete 洗掉可以回滾；

MySQL8 新特性：計算列

什么叫計算列呢？簡單來說就是某一列的值是通過別的列計算得來的，例如，a列值為1、b列值為2，c列不需要手動插入，定義a+b的結果為c的值，那么c就是計算列，是通過別的列計算得來的，

CREATE TABLE tb1( id INT, a INT, b INT, c INT GENERATED ALWAYS AS (a + b) VIRTUAL );

七、`DDL`語言的學習

識別符號命名規則

資料庫名、表名不得超過30個字符，變數名限制為29個，
必須只能包含 A-Z，a-z，0-9，_ 共63個字符，
資料庫名、表名、欄位名等物件中間不要包含空格，
同一個 MySQL 軟體中，資料庫不能同名；同一個庫中，表不能重名；同一個表中，欄位不能重名，
必須保證你的欄位沒有和保留字、資料庫系統或常用方法沖突，如果堅持使用，請在 SQL 陳述句中使用著重號引起來，
保持欄位名和型別的一致性：在命名欄位并為其指定資料型別的時候一定要保證一致性，假如資料型別在一個表里是整數，那在另一個表里可別變成字符型了，

庫的管理

創建：create
修改：alter
洗掉：drop

1. 庫的創建

語法：
    create database [if not exists] 庫名 [character set 字符集名];

#案例：創建庫books
    CREATE DATABASE IF NOT EXISTS books;

2. 庫的修改

RENAME DATABASE books TO 新庫名;(現已不用)

更改庫的字符集：
    ALTER DATABASE 庫名 CHARCATER SET 字符集;

3. 庫的洗掉

DROP DATABASE IF EXISTS 庫名;

表的管理

創建：create
修改：alter
洗掉：drop

1. 表的創建

create table [if not exists] 表名(
    列名 列的型別[(長度) 約束],
    列名 列的型別[(長度) 約束],
    ...
    列名 列的型別[(長度) 約束]
);

#案例：創建表book
    CREATE TABLE IF NOT EXISTS book( #需要用戶具備創建表的權限
        id INT,#編號
        bName VARCHAR(20),#圖書名
        price DOUBLE,#價格
        authorID INT,#作者編號
        publishDate DATETIME #出版日期
    );

2. 表的修改

alter table 表名 add|drop|modify|change column 列名 [列型別 約束] [first|after 欄位名];

(1)修改列名
    ALTER TABLE 表名 CHANGE COLUMN 舊列名 新列名 型別;

(2)修改列的型別或約束
    ALTER TABLE 表名 MODIFY COLUMN 列名 新型別 [新約束];

(3)添加新列
    ALTER TABLE 表名 ADD COLUMN 列名 型別 [first|after 欄位名];

(4)洗掉列
    ALTER TABLE 表名 DROP COLUMN 列名;

(5)修改表名
    ALTER TABLE 表名 RENAME [TO] 新表名;

3. 表的洗掉

DROP TABLE IF EXISTS 表名;

4. 表的復制

1.僅僅復制表的結構
    CREATE TABLE 表名 LIKE 舊表;

2.復制表的結構+資料
    CREATE TABLE 表名
    SELECT * FROM 舊表;

3.僅僅復制某些欄位和資料 查詢陳述句中欄位的別名，可以作為新創建的表的欄位的名稱
    CREATE TABLE 表名
    SELECT 查詢串列
    FROM 舊表
    WHERE 篩選;

常見資料型別介紹

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

型別	型別舉例
整數型別	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮點型別	FLOAT、DOUBLE
定點數型別	DECIMAL
位型別	BIT
日期時間型別	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字串型別	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
列舉型別	ENUM
集合型別	SET
二進制字串型別	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON型別	JSON物件、JSON陣列
空間資料型別	單值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

其中，常用的幾型別別介紹如下：

資料型別	描述
INT	從-2^31到231-1的整型資料，存盤大小為 4個位元組
CHAR(size)	定長字符資料，若未指定，默認為1個字符，最大長度255
VARCHAR(size)	可變長字符資料，根據字串實際長度保存，必須指定長度
FLOAT(M,D)	單精度，占用4個位元組，M=整數位+小數位，D=小數位， D<=M<=255,0<=D<=30，默認M+D<=6
DOUBLE(M,D)	雙精度，占用8個位元組，D<=M<=255,0<=D<=30，默認M+D<=15
DECIMAL(M,D)	高精度小數，占用M+2個位元組，D<=M<=65，0<=D<=30，最大取值范圍與DOUBLE相同
DATE	日期型資料，格式'YYYY-MM-DD
BLOB	二進制形式的長文本資料，最大可達4G
TEXT	長文本資料，最大可達4G

整型

分類：

    tinyint、smallint、mediumint、int/integer、bigint
位元組數  1        2        3            4            8

可設為有符號/無符號

特點：
如果不設定無符號還是有符號，默認是有符號，如果想設定無符號，需要添加 unsigned 關鍵字；
如果插入的數值超出了整型的范圍，會報 out of range 例外，并且插入臨界值；
如果不設定長度，會有默認的長度；長度代表了顯示的最大寬度，如果不夠會用 0 在左邊填充，但必須搭配 zerofill 使用，并且默認變為無符號整型；
案例

#案例1：如何設定無符號和有符號
    CREATE TABLE tab_int(
        t1 INT
        t2 INT UNSIGNED 
    );

小數

分類：

1.浮點型
    float(M,D)    4位元組
    double(M,D)    8位元組

2.定點型
    dec(M,D)    占用M+2位元組的存盤空間 最大范圍與double相同，給定decimal的有效取值范圍由M和D決定
    decimal(M,D) 定點數以字串形式進行存盤

特點：

(1) M：整數部位+小數部位
    D：小數部位
    如果超過范圍，則報out of range，并插入臨界值

(2)M和D都可以省略
    如果是decimal，則M默認為10，D默認為0
    如果是float和double，則會根據插入的數值的精度來決定精度

(3)定點型的精度較高，如果要求插入的數值精度較高，如貨幣運算則考慮使用，

原則：所選擇的型別越簡單越好，能保存數值的型別越小越好；

字符型

char、varchar、binary、varbinary、enum、set、text、blob

BLOB是一個二進制大物件，四種BLOB型別是：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOG
//todo    https://blog.csdn.net/wb1046329430/article/details/114783334
使用臨時表處理的查詢結果實體中的BLOB和TEXT列，會導致服務器在磁盤上而不是在記憶體中使用表


前綴索引

分類：

較短的文本：char、varchar

字串型別	最多字符數	描述及存盤需求	特點	占用的存盤空間	效率
char(M)	M，可以省略，默認為1	M為 0~255 之間的整數	固定長度的字符	M個位元組	高
varchar(M)	M，不可以省略	M為 0~65535 之間的整數	可變長度的字符	M+1個位元組	低

較長的文本：text、blob(較大的二進制)
位型別

位型別位元組范圍

Bit(M) 1~8 Bit(1)~Bit(8)
binary 和 varbinary 型別

說明：用于保存較短的二進制，類似于char 和 varchar，不同的是它們包含二進制字串而不包含非二進制字串；
enum 型別

說明：列舉型別，要求插入的值必須屬于串列中指定值之一；
set 型別：用于保存集合；

位型別	位元組	范圍
Bit(M)	1~8	Bit(1)~Bit(8)

日期型

分類：

date：只保存日期
time：只保存時間
datetime：保存日期+時間
timestamp：保存日期+時間

特點：

日期和時間型別	位元組	最小值	最大值	受時區影響
date	4	1000-01-01	9999-12-32
datetime	8	1001-01-01 00:00:00	9999-12-31 23:59:59	不受
timestamp	4	19700101080001	2038年的某個時刻	受
time	3	-838:59:59	838:59:59
year	1	1901	2155

datetime 和 timestamp 的區別：

timestamp 支持的時間范圍較小，取值范圍：19700101080001 —— 2038年的某個時刻

datetime 的取值范圍：1001-01-01 00:00:00 —— 9999-12-31 23:59:59

timestamp 和實際時區有關，更能反映實際的日期，而 datetime 則只能反映出插入時的當地時區；
timestamp 的屬性受 mysql版本和 sqlmode 的影響很大；
timestamp 底層存盤的是毫秒值，兩個日期比較大小或日期計算時，timestamp 更方便、更快，

JSON 型別

當需要檢索JSON型別的欄位中資料的某個具體值時，可以使用“->”和“->>”符號，

常見約束

含義：一種限制，用于限制表中的資料，為了保證表中的資料的準確和可靠性，
分類：六大約束

NOT NULL：非空，用于保證該欄位的值不能為空，比如姓名、學號等
DEFAULT：默認，用于保證該欄位有默認值，比如性別
PRIMARY KEY：主鍵，用于保證該欄位的值具有唯一性，并且非空，比如學號、員工編號等，
UNIQUE：唯一，用于保證該欄位的值具有唯一性，可以有多個為空，唯一約束可以是某個列的值唯一，也可以是多個列組合的值唯一，
CHECK：檢查約束，比如年齡、性別，
FOREIGN KEY：外鍵，用于限制兩個表的關系，用于保證該欄位的值必須來自于主表的關聯列的值，在從表添加外鍵約束，用于參考主表中某列的值，比如學生表的專業編號，員工表的部門編號，員工表的工種編號，

添加約束的時機：

創建表時
修改表時

約束的添加分類：

列級約束：六大約束語法上都支持，但外鍵約束沒有效果，

表級約束：除了非空、默認，其他的都支持，

主鍵和唯一的區別

	保證唯一性	是否允許為空	一個表中可以有多個	是否允許組合(多個欄位組合成一個)
主鍵	√	×	至多有1個	√，但不推薦
唯一	√	√	可以有多個	√，但不推薦

外鍵：
要求在從表設定外鍵關系；
從表的外鍵列的型別和主表的關聯列的型別要求一致或兼容，名稱無要求；
主表的關聯列必須是一個key（一般是主鍵或唯一）；
插入資料時，先插入主表，再插入從表；
洗掉資料時，先洗掉從表，再洗掉主表；
可以通過以下兩種方式來洗掉主表的記錄

```sql
# 方式一：級聯洗掉
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majofid) REFERENCES major(id) ON DELETE CASCADE;
DELETE FROM major WHERE id = 3;

# 方式二：級聯置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majofid) REFERENCES major(id) ON DELETE SET NULL;
DELETE FROM major WHERE id = 3;
```

列級約束和表級約束的區別

	位置	支持的約束型別	是否可以起約束名
列級約束	列的后面	語法都支持，但外鍵沒有效果	不可以
表級約束	所有列的下面	默認和非空不支持，其他支持	可以(主鍵沒有效果)

查看表中的約束

SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名';

一、創建表時添加約束

1.添加列級約束

語法：直接在欄位名和型別后面追加約束型別即可，

只支持：默認、非空、主鍵、唯一；

CREATE TABLE stuinfo(
    id INT PRIMARY KEY,#主鍵
    stuName VARCHAR(20) NOT NULL,#非空
    gender CHAR(1) CHECK(gender='男' OR gneder='女'),#檢查
    seat INT UNIQUE,#唯一
    age INT DEFAULT 18,#默認約束
    majorID INT FOREIGN KEY REFERENCES major(id) #外鍵
);

CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);

#查看stuinfo中所有索引，包括主鍵、外鍵、唯一
    SHOW INDEX FROM stuinfo;

2.添加表級約束

語法：在各個欄位的最下面；

[constraint 約束名] 約束型別(欄位名)

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(gender='男' OR gender='女'),#檢查
    CONSTRAINT 約束名 FOREIGN KEY(欄位名) REFERENCES 主表(被參考列),#外鍵
);

#通用的寫法
CREATE TABLE IF NOT EXISTS stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    gender 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：添加非空約束
    ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

#案例2：添加默認約束
    #(1)列級約束
    ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
    #(2)表級約束
    ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#案例3：添加外鍵
    ALTER TABLE 從表名 ADD [CONSTRAINT fk_stuinfo_major] FOREIGN KEY(從表外鍵) REFERENCE 主表名(主表主鍵);

三、修改表時洗掉約束

1.洗掉非空約束
    ALTER TABLE 表名 MODIFY COLUNM 欄位名 欄位型別;

2.洗掉主鍵
    ALTER TABLE 表名 DROP PRIMARY KEY;

PRIMARY KEY 約束

一個表最多只能有一個主鍵約束，建立主鍵約束可以在列級別創建，也可以在表級別上創建，
主鍵約束對應著表中的一列或者多列（復合主鍵），
如果是多列組合的復合主鍵約束，那么這些列都不允許為空值，并且組合的值不允許重復，
MySQL 的主鍵名總是 PRIMARY，就算自己命名了主鍵約束名也沒用，
當創建主鍵約束時，系統默認會在所在列或列組合上建立對應的主鍵索引，如果洗掉主鍵約束了，主鍵約束對應的索引就自動洗掉了，

唯一性約束

特點：

同一個表可以有多個唯一約束，
唯一約束可以是某一個列的值唯一，也可以多個列組合的值唯一，
唯一性約束允許列值為空，
在創建唯一約束的時候，如果不給唯一約束命名，就默認和列名相同，
MySQL 會給唯一約束的列上默認創建一個唯一索引，

洗掉唯一約束：

添加唯一性約束的列上也會自動創建唯一索引，
洗掉唯一約束只能通過洗掉唯一索引的方式洗掉，
洗掉時需要指定唯一索引名，唯一索引名就和唯一約束名一樣，
如果創建唯一約束時未指定名稱，如果是單列，就默認和列名相同；如果是組合列，那么默認和 () 中排在第一個的列名相同，也可以自定義索引名，

ALTER TABLE USER
DROP INDEX 索引名;

FOREIGN KEY 約束

在創建外鍵約束時，如果不給外鍵約束命名，默認名不是列名，而是自動產生一個外鍵名，
從表的外鍵列與主表被參照的列名字可以不相同，但是資料型別必須一樣，邏輯意義一致，
當創建外鍵約束時，系統默認會在所在的列上建立對應的普通索引，但是索引名是列名，不是外鍵的約束名，
洗掉外鍵后，必須手動洗掉對應的索引，

約束等級：

Cascasde 方式：在父表上 update/delete 記錄時，同步 update/delete 掉子表的匹配記錄，
Set null 方式：在父表上 update/delete 記錄時，將子表上匹配記錄的列設定為 null，但是要注意子表的外鍵列不能為 not null，
No action 方式：如果子表中有匹配的記錄，則不允許對父表對應候選鍵進行 update/delete 操作，
Restrict 方式：同 no action，都是立即檢查外鍵約束，
Set default 方式：父表有變更時，子表將外鍵列設定成一個默認的值，但 Innodb 不能識別，

對于沒有指定等級，就相當于 Restrict 方式，

對于外鍵約束，最好是采用：ON UPDATE CASCADE ON DELETE RESTRICT 的方式，

create table emp(
eid int primary key, #員工編號 
ename varchar(5), #員工姓名 
deptid int, #員工所在的部門
foreign key (deptid) references dept(did) on update cascade on delete set null
#把修改操作設定為級聯修改等級，把洗掉操作設定為set null等級 
)

check 約束

作用：檢查某個欄位的值是否符合 xx 要求，一般指的是值的范圍，
說明：MySQL 5.7 不支持，MySQL 8.0 支持，

標識列：又稱為自增長列

含義：可以不用手動的插入值，系統提供默認的序列值，默認從1開始，步長為1，

特點：

標識列必須和主鍵搭配嗎？不一定，但要求是一個key，
自增長列約束的列必須是鍵列（主鍵列，唯一鍵列），
一個表可以有幾個標識列？至多一個，
標識列的型別只能是數值型，
標識列可以通過 SET auto_increment_increment=3 設定步長；可以通過手動插入值，設定起始值，
如果自增列指定了 0 或 NULL，會在當前最大值的基礎上自增；如果自增列手動指定了具體值，直接賦值為具體值，

一、創建表時設定標識列

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

二、修改表時設定標識列

ALTER TABLTE 表名 MODIFY COLUMN 欄位名 欄位型別 約束 AUTO_INCREMENT;

三、修改表時洗掉標識列

ALTER TABLE 表名 MODIFY COLUMN 欄位名 欄位型別 約束;

八、`TCL`語言的學習

事務和事務處理

Transcation Control Language 事務控制語言 ;
事務：一個或一組 sql 陳述句組成一個執行單元，這個執行單元要么全部執行，要么全部不執行，

事務的ACID(acid)屬性

原子性(Atomicity)：指事務是一個不可分割的作業單位，事務中的操作要么都發生，要么都不發生，
一致性(Consistency)：事務必須使資料庫從一個一致性狀態變換到另一個一致性狀態，
隔離性(Isolation)：是指一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的資料對并發的其他事務是隔離的，并發執行的各個事務之間不能互相干擾，
持久性(Durability)：指一個事務一旦被提交，它對資料庫中的資料的改變就是永久性的，接下來的其他操作和資料庫故障不應該對其有任何影響，

事務的創建

隱式事務

隱式事務：事務沒有明顯的開啟和結束的標記，

比如insert、update、delete陳述句

顯式事務

顯式事務：事務具有明顯的開啟和結束的標記，

前提：必須先設定自動提交功能為禁用， set autocommit=0;

#步驟1：開啟事務
    set autocommit=0;
    start transaction; 可以省略

#步驟2：撰寫事務中的sql陳述句(select、insert、update、delete)
    陳述句1;
    陳述句2;    
    ...

#步驟3：結束事務
    commit; 提交事務
    rollback; 回滾事務    rollback to 回滾點名;
    savapoint 節點名; 設定保存點

事務的隔離

并發事務

事務的并發問題如何發生的？

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

并發問題都有哪些？

臟讀：臟讀指的是讀到了其他事務未提交的資料，未提交意味著這些資料可能會回滾，也就是可能最終不會存到資料庫中，也就是不存在的資料，讀到了不一定最終存在的資料，就是臟讀，

一個事務讀取了其他事務還沒有提交的資料，讀取到的是其他事務“更新”的資料，

對于兩個事務 T1、T2，T1 讀取了已經被 T2 更新但還沒被提交的欄位，之后，若 T2 回滾，T1 讀取的內容就是臨時且無效的，

總結一句話：無論是臟寫還是臟讀，都是因為一個事務去更新或者查詢了另一個還沒有提交的事務更新過的資料，因為另一個事務還沒有提交，它隨時都可能回滾，那么必然導致你更新的資料就沒了，或者你之前查詢到的資料就沒了，這就是臟寫和臟讀兩種場景，
不可重復讀：指的是在同一事務內，不同的時刻讀到的同一批資料可能是不一樣的，可能會受到其他事務的影響，比如其他事務改了這批資料并提交了，通常針對資料 更新(update) 操作，

一個事務多次讀取，結果不一樣，

對于兩個事務 T1、T2，T1 讀取了一個欄位，然后 T2 更新了該欄位，之后，T1 再次讀取同一個欄位，值就不同了，
幻讀：幻讀是針對資料插入(INSERT)操作來說的，

一個事務讀取了其他事務還沒有提交的資料，只是讀到的是其他事務“插入”的資料，

對于兩個事務 T1、T2，T1 從一個表中讀取了一個欄位，然后 T2 在該表中插入了一些新的行，之后，如果 T1 再次讀取同一個表，就會多出幾行，

如何解決并發問題？通過設定隔離級別解決并發問題，

4 種隔離級別

read uncommitted（讀未提交資料）

允許事務讀取未被其他事務提交的變更，臟讀、不可重復讀和幻讀的問題都會出現，
read committed（讀已提交資料）

只允許事務讀取已經被其它事務提交的變更，可以避免臟讀，但不可重復讀和幻讀問題仍然可能出現，
repeatable read（可重復讀）

確保事務可以從一個欄位中讀取相同的值，在這個事務持續期間，禁止其它事務對這個欄位進行更新，可以避免臟讀和不可重復度，但幻讀的問題仍然存在，
serializable（串行化）

確保事務可以從一個表中讀取相同的行，在這個事務持續期間，禁止其它事務對該表執行插入，更新和洗掉操作，所有并發都可以避免，但性能低下，

注：Oracle 支持的 2 中事務隔離級別：READ COMMITED、SERIALIZABLE，Oracle 默認的事務隔離級別是：READ COMMITED；

Mysql 支持 4 種事務隔離級別，mysql 默認的事務隔離級別是：REPEATABLE READ，

	臟讀	不可重復讀	幻讀
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

設定隔離級別

每啟動一個 mysql 程式，就會獲得一個單獨的資料庫連接，每個資料庫連接都有一個全域變數 @@tx_isolation，表示當前的事務隔離級別；

#查看隔離級別
    select @@tx_isolation;

#設定隔離級別
    set session|global transaction isolation level 隔離級別;

回滾點

#演示savepoint的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id = 25;
SAVEPOINT a;#設定保存點
DELETE FROM account WHERE id = 28;
ROLLBACK TO a;#回滾到保存點

delete和truncate區別

delete可以回滾，truncate不可以；

#演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

#演示truncate
SET autocommit=0;
START TRANSACTION;
TRUNCATE account;
ROLLBACK;

COMMIT 和 ROLLBACK

COMMIT：提交資料，一旦執行 COMMIT，則資料就被永久的保存在了資料庫匯總，意味著資料不可以回滾，

ROLLBACK：回滾資料，一旦執行 ROLLBACK，則可以實作資料的回滾，回滾到最近的一次 COMMIT 之后，

DDL 的操作一旦執行，就不可以回滾，指令 SET autocommi = FALSE 對 DDL 操作失效，

DML 的操作默認情況，一旦執行，也是不可回滾的，但是，如果在執行 DML 之前，執行了 SET autocommit = FALSE，則執行的 DML 操作就可以實作回滾，

九、視圖

含義：虛擬表，行和列的資料來自定義視圖的查詢中使用的表，并且是在使用視圖時動態生成的，只保存了sql邏輯，不保存查詢結果，

和普通表一樣使用，是通過表動態生成的資料，

簡化 sql 陳述句；
提高了 sql 的重要性；
保護基表的資料，提高了安全性；

創建視圖

#語法
    CREATE [OR REPLACE]
    [ALGORITM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW 視圖名稱 [(欄位串列]
    AS 查詢陳述句
    [WITH [CASCADED|LOCAL] CHECK OPTION]
#精簡版
    create view 視圖名 [(欄位名稱)]
    as
    查詢陳述句;

#案例1：查詢員工姓名中包含a字符的員工名、部門名和工種資訊
#(1)創建
    CREATE VIEW myv1
    AS
    SELECT last_name,department_name,job_title
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN jobs j ON j.job_id = e.job_id;
#(2)使用
    SELECT * FROM myv1 WHERE last_name LIKE '%a%';

#案例2：查詢各部門的平均工資級別
#(1)創建視圖查看某個部門的平均工資
    CREATE VIEW myv2
    AS
    SELECT AVG(salary) AS ag,department_id
    FROM employees
    GROUP BY department_id;
#(2)使用
    SELECT myv2.ag,g.grade_level
    FROM myv2
    JOIN job_grades g
    ON myv2.ag BETWEEN g.lowest_sal AND g.highest_sal;

修改視圖

#方式一：
    create or replace view 視圖名
    as
    查詢陳述句;

#方式二：
    alter view 視圖名
    as
    查詢陳述句;

洗掉視圖

drop view 視圖名1,視圖名2,...;

說明：基于視圖 a、b 創建了新的視圖 c，如果將視圖 a 或者視圖 b 洗掉，會導致視圖 c 的查詢失敗，這樣的視圖 c 需要手動洗掉或修改，否則影響使用，

查看視圖

desc 視圖名;
show create view 視圖名;

更新視圖

#1.插入
    INSERT INTO 視圖名 VALUES(...);

#2.修改
    UPDATE 視圖名 SET last_name = '張無忌' WHERE last_name='張無忌';

#3.洗掉
    DELETE FROM 視圖名 篩選條件;

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

包含以下關鍵字的 sql 陳述句：分組函式、distinct、group by、having、union 或者 union all；
常量視圖；
select 中包含子查詢；
join；
from 一個不能更新的視圖；
where 子句的子查詢參考了 from 子句的表；

視圖和表的對比

	創建語法的關鍵字	是否實際占用物理空間	使用
視圖	create view	只是保存了sql邏輯	增刪改查，一般不能增刪改
表	create table	保存了資料	增刪改查

十、變數

系統變數：變數是由系統提供的，不用自定義，
- 全域變數
  - 服務器層面上的，必須擁有 super 權限才能為系統變數賦值，
  - 作用域：服務器每次啟動都將為所有的全域變數賦初始值，針對于所有的會話(連接)有效，但不能跨重啟，
- 會話變數
  - 服務器為每一個連接的客戶端都提供了系統變數，作用域為當前的連接(會話)；
  - 作用域：僅僅針對于當前會話有效，
自定義變數
- 用戶變數
- 區域變數

系統變數

說明：變數由系統提供的，不是用戶定義，屬于服務器層面；

語法：

#1.查看所有的系統變數
    show global|session variables;

#2.查看滿足條件的部分系統變數
    show global | [session] variables like '%char%';

#3.查看指定的某個系統變數的值
    select @@global | [@@session].系統變數名

4.為某個系統變數賦值
    方式一：修改組態檔
    方式二：set global | [session] 系統變數名 = 值;
    方式三：set @@global | [session].系統變數名 = 值;

注意：如果是全域級別，則需要加 global，如果是會話級別，則需要加 session，如果不寫，則默認是 session

作為 MySQL 編碼規范，MySQL 中的系統變數以 兩個"@" 開頭，其中 "@@global" 僅用于標記全域系統變數，"@@session" 僅用于標記會話系統變數，"@@" 首先會標記會話系統變數，如果會話系統變數不存在，則標記全域系統變數，

全域變數持久化

MySQL 8.0 的新特性

在MySQL資料庫中，全域變數可以通過SET GLOBAL陳述句來設定，例如，設定服務器陳述句超時的限制，可以通過設定系統變數max_execution_time來實作：

SET GLOBAL MAX_EXECUTION_TIME=2000;

使用SET GLOBAL陳述句設定的變數值只會 臨時生效 ，資料庫重啟后，服務器又會從MySQL組態檔中讀取變數

的默認值， MySQL 8.0版本新增了 SET PERSIST 命令，例如，設定服務器的最大連接數為1000：

SET PERSIST global max_connections = 1000;

MySQL會將該命令的配置保存到資料目錄下的 mysqld-auto.cnf 檔案中，下次啟動時會讀取該檔案，用

其中的配置來覆寫默認的組態檔，

自定義變數

說明：變數是用戶自定義的，不是由系統的，

使用步驟：
宣告
賦值
使用(查看、比較、運算等)

用戶變數

作用域：針對于當前會話(連接)有效，同于會話變數的作用域，

賦值的運算子： = 或 :=

(1)宣告并初始化
方式一："=" 或 ":="
    set @用戶變數名 = 值;
    set @用戶變數名 := 值;
方式二：":=" 或 INTO 關鍵字
    select @用戶變數名:= 運算式 [FROM 等子句];
    SELECT 運算式 INTO @用戶變數 [FROM 等子句];

(2)賦值(更新用戶變數的值)
    方式一：通過 set 和 select
        set @用戶變數名=值;或
        set @用戶變數名:=值;或
        select @用戶變數名:=值;
    方式二：通過 select into
        select 欄位 into @變數名
        from 表;

(3)使用(查看用戶變數的值)
    select @用戶變數名;

區域變數

作用域：僅僅是在定義它的 begin end 中有效，應用在 begin end 中的第一句話！

(1)宣告：使用 DECLARE 陳述句定義一個區域變數
    DECLARE 變數名 型別 [DEFAULT 值]; #如果沒有 DEFAULT子句，初始值為 NULL

(2)賦值
    方式一：通過 set 和 select
        set 區域變數名=值;或
        set 區域變數名:=值;或
        select @區域變數名:=值;
    方式二：通過 select into
        select 欄位 into 區域變數名
        from 表;

(3)使用
    select 區域變數名;

對比用戶變數和區域變數

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

#案例：宣告兩個變數并賦初始值，求和，并列印
#1.用戶變數
    SET @m=1;
    SET @m=2;
    SET @sum = @m + @n;
    SET @sum;

#2.區域變數
    BEGIN
        DECLARE m INT DEFAULT 1;
        DECLARE n INT DEFAULT 2;
        DECLARE SUM INT;
        SET SUM = m + n;
        SELECT SUM;
    END

十一、存盤程序和函式

存盤程序和函式：類似于 java 中的方法，

存盤程序

含義：一組預先編譯好的 SQL 陳述句的集合，理解成批處理陳述句；

優點：
1.提高代碼的重用性；
2.簡化操作；
3.減少了編譯次數并且減少了和資料庫的連接次數，提高了效率；

一、創建語法

CREATE PROCEDURE 存盤程序名(引數串列)
[characteristics ...]
BEGIN
    存盤程序體(一組合法的SQL陳述句)
END

注意：

引數串列包含三部分：引數模式引數名引數型別

舉例：IN stuname VARCHAR(20)

引數模式：

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

舉例：
呼叫 IN 模式的引數：call sql('值');
呼叫 OUT 模式的引數：set @name; call sql(@name);
呼叫 INOUT 模式的引數：set @name; call sql(@name); select @name;

如果存盤程序僅僅只有一句話，BEGIN END 可以省略；
存盤程序體中的每條 SQL 陳述句的結尾要求必須加分號；
存盤程序的結尾可以使用 DELIMITER 重新設定；

語法：DELIMITER 結束標記
案例：DELIMITER $

characteristics 表示創建存盤程序時指定的對存盤程序的約束條件，

二、呼叫語法

CALL 存盤程序名(實參串列);

三、洗掉存盤程序

DROP PROCEDURE 存盤程序名;

四、查看存盤程序的資訊

SHOW CREATE PROCEDURE 存盤程序名;

五、案例

#1.空參串列
#案例：插入到 admin 表中五條記錄
    DELIMITER $
    CREATE PROCEDUER myp1()
    BEGIN
        INSERT INTO admin(username,`password`)  VALUES ('john1','0000'),('lily','0000');
    END $

    #呼叫
    CALL myp1() $


#2.創建帶 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
    WEHRE b.name = beautyName;
END $
    #呼叫
    CALL myp2('小昭')$

#案例2：創建存盤程序實作，用戶是否登錄成功
DELIMITER $
CREATE PROCEDURE myp4(IN usernaame VARCHAR(20),IN password VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0; #宣告并初始化

    SELECT COUNT(*) INTO result #賦值
    FROM admin
    WHERE admin.username = username
    AND admin.password = password;

    SELECT IF(result >0,'成功','失敗'); #使用
END $

    #呼叫 
    CALL mp4('張飛','6666') $


#3.創建帶 in 和 out 模式引數的存盤程序        
#案例：根據女神名，回傳對應的男神名和男神魅力值
DELIMITER $
CREATE PROCEDURE myp6(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 myp6('小昭'，@bName,@usercp)$
    SELECT @bName,@usercp$


#4.創建帶 inout 模式引數的存盤程序
#案例1：傳入 a 和 b 兩個值，最終 a 和 b 都翻倍并回傳
CREATE PROCEDURE myp8(INOUT a int,INOUT b int)
BEGIN
    SET a = a*2;
    SET b = b*2;
END $


    #呼叫
   SET @m=10$
   SET @n=20$
   CALL(@m,@n)$
   SELECT @m,@n$

存盤函式

區別：

存盤程序：可以有 0 個回傳，也可以有多個回傳，適合做批量插入，批量更新，
函式：有且僅有 1 個回傳，適合做處理資料后回傳一個結果，

一、創建語法

CREATE FUNCTION 函式名(引數串列) RETURNS 回傳型別
[characteristics]
BEGIN
    函式體
END

注意：

引數串列包含兩部分：引數名引數型別，
函式體：肯定會有 return 陳述句，如果沒有會報錯

如果 return 陳述句沒有放在函式體的最后也不會報錯，但不建議，

函式體重僅有一句話，則可以省略 begin end，
使用 delimiter 陳述句設定結束標記，

二、呼叫語法

select 函式名(引數串列)

案例

#案例：根據員工名，回傳它的工資
CREATE FUNCTION myf2(empName VARCHAR(20) RETURNS DOUBLE
BEGIN
    SET @sal=0; #定義用戶變數
    SELECT salary INTO @sal #賦值
    FROM employees
    WHERE last_name = empName
    LIMIT 1;

    RETURN @sal;
END $

SELECT myf2('k_king') $

#案例：根據部門名，回傳該部門的平均工資
CREATE FUNCTION myf3(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 myf3('IT') $


#創建函式，實作傳入兩個float，回傳二者之和
CREATE FUNCTION test_func1(num1 FLOAT,NUM2 FLOAT) RETURNS FLOAT
BEGIN
    DECLARE SUM FLOAT DEFAULT 0;
    SET SUM = num1 + num2;
    RETURN SUM;
END $

SELECT test_func(1,2) $

三、查看函式

SHOW CREATE FUNCTION 函式名;

四、洗掉函式

DROP FUNCTION 函式名;

對比存盤程序和存盤函式

	關鍵字	呼叫語法	回傳值	應用場景
存盤程序	PROCEDURE	CALL 存盤程序名()	理解為有0個或多個	一般用于更新
存盤函式	FUNCTION	SELECT 函式名()	只能是一個	一般用于查詢結果為一個值并回傳時

此外，存盤函式可以放在查詢陳述句中使用，存盤程序不行，反之，存盤程序的功能更加強大，包括能夠執行對表的操作（比如創建表，洗掉表等）和事務操作，這些功能是存盤函式不具備的，

定義條件與處理程式

定義條件 是事先定義程式執行程序中可能遇到的問題， 處理程式 定義了在遇到問題時應當采取的處理方式，并且保證存盤程序或函式在遇到警告或錯誤時能繼續執行，這樣可以增強存盤程式處理問題的能力，避免程式例外停止運行，

說明：定義條件和處理程式在存盤程序、存盤函式中都是支持的，

定義條件

定義條件就是給 MySQL 中的錯誤碼命名，這有助于存盤的程式代碼更清晰，它將一個錯誤名字和指定的錯誤條件 關聯起來，這個名字可以隨后被用在定義處理程式的``DECLARE HANDLER` 陳述句中，

DECLARE 錯誤名稱 CONDITION FOR 錯誤碼（或錯誤條件）

定義處理程式

可以為 SQL 執行程序中發生的某種錯誤型別的錯誤定義特殊的處理程式，

DECLARE 處理方式 HANDLER FOR 錯誤型別 處理陳述句

處理方式：處理方式有3個取值：CONTINUE、EXIT、UNDO，

案例解決

DELIMITER // 
CREATE PROCEDURE UpdateDataNoCondition() 
    BEGIN
        #定義處理程式 
        DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = https://www.cnblogs.com/slivermirror/archive/2022/12/02/-1;
        SET @x = 1;
        UPDATE employees SET email = NULL WHERE last_name ='Abel'; 
        SET @x = 2; 
        UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel'; 
        SET @x = 3; 
    END // 

DELIMITER ;

#呼叫程序：
CALL UpdateDataWithCondition();
SELECT @x,@proc_value;

十二、流程控制結構

順序結構
分支結構
回圈結構

分支結構

if 函式

功能：實作簡單的雙分支
語法： IF(運算式1,運算式2,運算式3)
執行順序：如果運算式1成立，則 if 函式回傳運算式2的值，否則回傳運算式3的值，
應用：任何地方，

case 結構

情況1：類似于 java 中的 switch 陳述句，一般用于實作等值判斷，

語法：

CASE 變數|運算式|欄位
WHEN 要判斷的值 THEN 回傳的值1或陳述句1;
WHEN 要判斷的值 THEN 回傳的值2或陳述句2;
...
ELSE 要回傳的值n或陳述句n;
END CASE;

情況2：類以 java 中的多重 if 陳述句，一般用于實作區間判斷，

語法：

CASE 要判斷的條件1 THEN 回傳的值1或陳述句1;
WHEN 要判斷的條件2 THEN 回傳的值2或陳述句2;
...
ELSE 要回傳的值n或陳述句n；
END CASE;

特點：
(1)可以作為運算式，嵌套在其他陳述句中使用，可以放在任何地方，BEGIN END 中或 BEGIN END 的外面，可以作為獨立的陳述句去使用，只能放在BEGIN END 中，
(2) 如果 WHEN 中的值滿足或條件成立，則執行對應對的 THEN 后面的陳述句，并且結束 CASE，如果不滿足，則執行 ELSE 中的陳述句或值，
(3) ELSE 可以省略，如果 ELSE 省略了，并且所有的 WHEN 條件不滿足，則回傳 NULL，

 #案例：創建存盤程序，根據傳入的成績，來顯示等級，比如傳入的成績：90-100，顯示A；80-90，顯示B；60-80，C；否則，顯示D
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 TEHN SELECT 'C';
    ELSE SELECT 'D';
    END CASE
END $

CASE test_case(86) $

if 結構

功能：實作多重分支，

語法：

IF 條件1 THEN 陳述句1;
ELSEIF 條件2 THEN 陳述句2;
...
[ELSE 陳述句n;]
END IF;

應用場景：應用在 begin end 中，

回圈結構

分類：while、loop、repeat

回圈控制：
iterate 類似于 continue，繼續，結束本次回圈，繼續下一次，只可以用在回圈陳述句內，
leave 類似于 break，跳出；結束當前所在的回圈，可以用在回圈陳述句內，或者以 BEGIN 和 END 包裹起來的程式體內，

1.while
語法：
    [標簽:] while 回圈條件 do
        回圈體
    end while [標簽];

2.loop
語法：
    [標簽:] loop
        回圈體
    end loop [標簽];
# LOOP 內的陳述句一直重復執行直到回圈被退出（使用LEAVE子句），跳出回圈程序，

3.repeat
語法：
    [標簽:] repeat
        回圈體
    until 結束回圈的條件
    end repeat [標簽];

#案例1：批量插入，根據次數插入到admin表中的多條記錄，    
#1.沒有添加回圈控制
    DROP PROCEDURE pro_while1$
    CREATE PROCEDURE pro_while1(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
            WHILE 1<=insertCount DO
                INSERT INTO admin(username,`password`) VALUES(CONTACT('Rose',i),'666')
                SET i=i+1;
            END WHILE;
    END $

    CALL pro_while1(100)$


#2.添加 leave 陳述句
#案例：批量插入，根據次數插入到 admin 表中的多條記錄，，如果次數 >20 則停止
    TRUNCATE TABEL admin5$
    DROP PROCEDURE test_while1$
    CREATE PROCEDURE test_while(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
            a:WHILE i <= insertCount DO
                INSERT INTO amdin5(username,`password`) VALUES(CONTACT('xiaohua',i),'0000');
                IF i>=20 TEHN LEAVE a;
                END IF;
                SET i=i+1;
            END WHILE a;
    END $

    CALL test_while1(100)$



#已知表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);#產生一個隨機的整數，代表截取長度
        SET startIndex = FLOOR(Rand()*26+1);#產生一個隨機的整數,代表起始索引
        INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
        SET i=i+1;#回圈變數更新
    END WHILE;

END $

對比

這三種回圈都可以省略名稱，但如果回圈中添加了回圈控制陳述句（leave 或 iterate），則必須添加名稱，
loop 一般用于實作簡單的死回圈；

while 先判斷，后執行；

repeat 先執行，后判斷，無條件至少執行一次，

游標

游標，提供了一種靈活的操作方式，讓我們能夠對結果集中的每一條記錄進行定位，并對指向的記錄中的資料進行操作的資料結果，游標讓 SQL 這種面相集合的語言有了面相程序的開發能力，

MySQL中游標可以在存盤程序和函式中使用，

觸發器

1.觸發器概述

觸發器是由 事件來觸發 某個操作，這些事件包括 INSERT 、 UPDATE 、 DELETE 事件，所謂事件就是指用戶的動作或者觸發某項行為，如果定義了觸發程式，當資料庫執行這些陳述句時候，就相當于事件發生了，就會自動激發觸發器執行相應的操作，

2.觸發器的創建

CREATE TRIGGER 觸發器名稱 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 
觸發器執行的陳述句塊;

說明：

表名 ：表示觸發器監控的物件，
BEFORE | AFTER：表示觸發的時間，BEFORE 表示在事件之前觸發；AFTER 表示在事件之后觸發，
INSERT | UPDATE | DELETE：表示觸發的事件，
- INSERT 表示插入記錄時觸發，
- UPDATE 表示更新記錄時觸發，
- DELETE 表示洗掉記錄時觸發，
觸發器執行的陳述句塊 ：可以是單條SQL陳述句，也可以是由 BEGIN…END 結構組成的復合陳述句塊，

#定義觸發器“salary_check_trigger”，基于員工表“employees”的INSERT事件，
#在INSERT之前檢查將要添加的新員工薪資是否大于他領導的薪資，
#如果大于領導薪資，則報sqlstate_value為'HY000'的錯誤，從而使得添加失敗，

DELIMITER // 

CREATE TRIGGER salary_check_trigger 
BEFORE INSERT ON employees FOR EACH ROW 
BEGIN
    DECLARE mgrsalary DOUBLE; 
    SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id; 
    IF NEW.salary > mgrsalary THEN 
        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪資高于領導薪資錯誤'; 
    END IF; 
END // 

DELIMITER ;

3.查看、洗掉觸發器

3.1 查看觸發器

方式一：查看當前資料庫的所有觸發器的定義

SHOW TRIGGERS\G

方式2：查看當前資料庫中某個觸發器的定義

SHOW CREATE TRIGGER 觸發器名

方式3：從系統庫information_schema的TRIGGERS表中查詢“salary_check_trigger”觸發器的資訊

SELECT * FROM information_schema.TRIGGERS;

3.2 洗掉觸發器

DROP TRIGGER IF EXISTS 觸發器名稱;

4.注意點

注意，如果在子表中定義了外鍵約束，并且外鍵指定了 ON UPDATE/DELETE CASCADE/SET NULL 子句，此時修改父表參考的鍵值或洗掉父表被參考的記錄行時，也會引起子表的修改和洗掉操作，此時基于子表的 UPDATE 和 DELETE 陳述句定于的觸發器并不會被激活，

MySQL 8.0 其他新特性

MySQL 高級

新特定1：視窗函式

視窗函式分類

MySQL 從 8.0 版本開始支持視窗函式，視窗函式的作用類似于在查詢中對資料進行分組，不同的是，分組操作會把分組的結果聚合成一條函式，而視窗函式是將結果置于每一條資料記錄中，

視窗函式的特點是可以分組，而且可以在分組內排序，另外，視窗函式不會因為分組而減少原表中的行數，這對我們在原表資料的基礎上進行統計和排序非常有用，

視窗函式可以分為 靜態視窗函式 和 動態視窗函式，

靜態視窗函式的視窗大小是固定的，不會因為記錄的不同而不同，
動態視窗函式的視窗大小會隨著記錄的不同而變化，

視窗函式總體上可以分為序號函式、分布函式、前后函式、首尾函式和其他函式，如下表：

函式分類	函式	函式說明
序號函式	ROW_NUMBER()	順序排序
	RNAK()	并列排序，會跳過重復的序號，比如序號為1、1、3
	DENSE_RANK()	并列函式，不會跳過重復的序號，比如序號為1、1、2
分布函式	PRECENT_RANK()	等級值百分比
	CUME_DIST()	累計分布值
前后函式	LAG(expr,n)	回傳當前行的前n行的expr的值
	LEAD(expr,n)	回傳當前行的后n行的expr的值
首尾函式	FIRST_VALUE(expr)	回傳第一個expr的值
	LAST_VALUE(expr)	回傳最后一個expr的值
其他函式	NTH_VALUE(expr,n)	回傳第n個expr的值
	NTILE(n)	將磁區中的有序資料分為n個桶，記錄桶編號

語法結構

函式 OVER（[PARTITION BY 欄位名 ORDER BY 欄位名 ASC|DESC]）
或者是
函式 OVER 視窗名 … WINDOW 視窗名 AS （[PARTITION BY 欄位名 ORDER BY 欄位名 ASC|DESC]）

OVER 關鍵字指定函式視窗的范圍，
- 如果省略后面括號中的內容，則視窗函式會包含滿足 WHERE 條件的所有記錄，視窗函式會基于所有滿足 WHERE 條件的記錄進行計算，
- 如果 OVER 關鍵字后面的括號不為空，則可以使用如下語法設定視窗，
視窗名：為視窗設定一個別名，用來標識視窗，
PARTITION BY 子句：指定視窗函式按照哪些欄位進行分組，分組后，視窗函式可以在每個分組中分別執行，
ORDRE BY 子句：指定視窗函式按照哪些欄位進行排序，執行排序操作使視窗函式按照排序后的資料記錄的順序進行編號，
FRAME 子句：為磁區中的某個子集定義規則，可以用來作為滑動視窗使用，

新特性2：公用運算式

公用表運算式（或通用表運算式）簡稱為CTE（Common Table Expressions），CTE是一個命名的臨時結果集，作用范圍是當前陳述句，CTE可以理解成一個可以復用的子查詢，當然跟子查詢還是有點區別的，CTE可以參考其他CTE，但子查詢不能參考其他子查詢，

依據語法結構和執行方式的不同，公用表運算式分為 普通公用表運算式 和 遞回公用表運算式 2 種，

# 普通公用運算式
WITH CTE名稱 
AS （子查詢） 
SELECT|DELETE|UPDATE 陳述句;


# 遞回公用表運算式
WITH RECURSIVE CTE名稱 
AS （子查詢） 
SELECT|DELETE|UPDATE 陳述句;

mysql 的架構介紹

mysql 簡介

概述

關系型資料庫；

高級 mysql

mysql 內核
sql 優化工程師
mysql 服務器的優化
各種引數常量設定
查詢陳述句優化
主從復制
軟硬體升級
容災備份
sql 編程

mysqlLinux 版的安裝

檢查當前系統是否安裝過 mysql

查詢命令：rpm -qa|grep -i mysql
洗掉命令：rpm -e RPM軟體包名

安裝 mysql 服務端

rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm

安裝 mysql 客戶端
查看 mysql 安裝時創建的 mysql 用戶和 mysql 組
```
cat /etc/passwd |grep mysql
cat /etc/group |grep mysql
```
mysql 服務的啟+停
```
service mysql start
service mysql stop
```

mysql 服務啟動后，開始連接

設定root密碼：/usr/bin/mysqladmin -u root password 123456

自啟動 mysql 服務

chkconfig mysql on  設定開機自動啟mysql
chkconfig --list |grep mysql
ntsysv：看到 [*]mysql 這一行，表示開機后會自動啟動mysql

修改組態檔位置

拷貝：cp /usr/share/mysql/my-default/cnf  /etc/my.cnf

修改字符集和資料存盤路徑

1.查看字符集
    show variables like 'character%';
    show variables like '%char%';

2.修改
    vim /etc/my.cnf        注：如何讓mysql使用指定的/etc/my.cnf組態檔呢？
    [client]
    #passsword = your_password
    port = 3306
    socket = ...
    default-character-set = utf8    新增

    [mysqld]
    port = 3306
    character_set_server = uft8     新增
    character_set_client = uft8        新增
    collation-server = uft8_general_ci     新增

    [mysql]
    no-auto-rehash
    default-character-set=uft8        新增

3.重啟mysq
    service mysql stop
    service mysql start

4.重新連接后重新create database并使用新建庫，然后再重新建表試試

mysql 的安裝位置

在linux下查看安裝目錄 ps -ef|grep mysql

路徑	解釋	備注
/var/lib/mysql	mysql資料庫檔案的存放路徑	/var/lib/mysql/atguigu.cloud.pid
/usr/share/mysql	組態檔目錄	mysql.service 命令及組態檔
/usr/bin	相關命令目錄	mysqladmin mysqldump 等命令
/etc/init.d/mysql	啟停相關腳本

mysql 組態檔

二進制日志 log-bin：主從復制
錯誤日志 log-error：默認是關閉的，記錄嚴重的警告和錯誤資訊
查詢日志 log：默認關閉，記錄查詢的 sql 陳述句，如果開啟
資料檔案
如何配置

mysql 邏輯架構介紹

(1)連接層
(2)服務層
(3)引擎層
(4)存盤層

mysql 存盤引擎

mysql存盤引擎
查看命令

    SHOW ENGINES;
    show variables like '%storage_engine%'; #查看mysql當前默認的存盤引擎

> MyISAM和InnoDB
    對比項         MyISAM         InnoDB

    主外鍵         不支持         支持
    事務         不支持         支持
    行表鎖         表鎖         行鎖
    快取         只快取索引， 索引和資料都快取
                不快取真實資料

    表空間         小             大
    關注點         性能          事務
    默認安裝     Y             Y

索引優化分析

> 性能下降SQL慢，執行時間長，等待時間長
    > 查詢陳述句寫的爛
    > 索引失效
    >
    >     > 單值
    >     > 復合
    > 關聯查詢太多join(設計缺陷或不得已的需求)
    > 服務器調優及各個引數設定(快取、執行緒等)

> 常見的join查詢
    > SQL執行順序
    > join圖
    > 建表sql
    > 7種join

> 索引簡介
    MySQL官方對索引的定義為：索引(Index)是幫助MySQL高效獲取資料的 資料結構，可以簡單理解為“排好序的快速查找資料結構”

    > mysql索引分類
        單值索引：即一個索引只包含單個列，一個表可以多個單列索引，
        唯一索引：索引列的值必須唯一，但允許有空值
        復合索引：即一個索引包含多個列，
        基本語法：
            > 創建
                create [unique] index indexName ON mytale(columnname(length));
                ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
            > 洗掉
                DROP INDEX [indexName] ON mytable;
            > 查看
                SHOW INDEX FROM table_name;
            > 使用ALTER命令：有四種方式來添加資料表的索引
                ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):該陳述句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL
                ALTER TALBE tbl_name ADD UNIQUE index_name (column_list)：這條陳述句創建索引的值必須是唯一的(除了NULL外，NULL可能會出現多次)
                ALTER TABLE tbl_name ADD INDEX index_name (column_list)：添加普通索引，索引值啃出現多次
                ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)：該陳述句指定了索引為 FULLTEXT，用于全文索引，

        > mysql索引結構
            BTree索引
            Hash索引
            full-text全文索引
            R-Tree索引

> 性能分析
    > MySql Query Optimize
    > MySQL常見瓶頸
    > explain
        > 是什么(查看執行計劃)：使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢陳述句，從而知道MySQL是如何處理你的SQL陳述句的，分析你的查詢陳述句或是表結構的性能瓶頸
        > 能干嘛
            表的讀取順序
            資料讀取操作的操作型別
            哪些索引可以使用
            哪些索引被實際使用
            表之間的參考
            每張表有多少行被優化器查詢
        > 怎么玩
            Explain + SQL陳述句
            執行計劃包含的資訊
                id select_type table type possible_keys key key_len ref rows Extra
        > 各欄位解釋
            id：
                select查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序
                三種情況
                    id相同，執行順序由上至下
                    id不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行
                    id相同不同，同時存在，
            select_table
                > 有哪些
                    id  select_type
                    1     SIMPLE
                    2     PRIMARY
                    3     SUBQUERY
                    4     DERIVED
                    5     UNION
                    6     UNION RESULT
                > 查詢的型別，主要是用于區別普通查詢、聯合查詢、子查詢等的復雜查詢
                    1.SIMPLE：簡單的select查詢，查詢中不包含子查詢或者UNION
                    2.PRIMARY：查詢中若包含任何復雜的子部分，最外層查詢則被標記為PRIMARY
                    3.SUBQUERY：在select或where串列中包含了子查詢
                    4.DERIVED：在FROM串列中包含的子查詢被標記為DERIVED(衍生)，MySQL會遞回執行這些子查詢，把結果放在臨時表里，
                    5.UNION
                    6.UNION RESULT

            table
            type
                > ALL  index  range ref  eq_ref  const,system  NULL
                >訪問型別排列
                    > 顯示查詢使用了何種型別，從最好到最差依次是：system > const > eq_ref > range > index > ALL
                    > system：表只有一行記錄(等于系統表)，這是const型別的特例，
                    > const：表示通過索引一次就找到了，const用于比較primary key或者unique索引
                    > eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配，常見于主鍵或唯一索引掃描，
                    > ref：非唯一性索引掃描，回傳匹配某個單獨值的所有行，

            possible_keys：
            key
            key_len：表示索引中使用的位元組數，可通過該列計算查詢中使用的索引長度，
            ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數，
            rows
            Extra
                1.Using filesort
                2.Using temporary
                3.Using index
                4.Using where
                5.using join buffer
                6.impossible where

> 索引優化
    > 索引分析
        單表
        兩表
        三表
    > 索引失效(應該避免)
        1.全值匹配我最愛
        2.最佳左前綴法則：如果索引了多列，要遵守最左前綴法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列，
        3.不在索引列上做任何操作(計算、函式、(自動or手動)型別轉換)，會導致索引失效而轉向全表掃描，
        4.存盤引擎不能使用索引中范圍條件右邊的列，
        5.盡量使用覆寫索引(只訪問索引的查詢(索引列和查詢列一致))，減少select *
        6.myslq在使用不等于(!=或<>)的時候無法使用索引會導致全表掃描，
        7.is null，is not null也無法使用索引，
        8.like以通配符開頭('%abc...')mysql索引失效會變成全表掃描的操作，
            問題：解決like '%字串%' 時索引不被使用的方法？
            答：覆寫索引
        9.字串不加單引號索引失效
        10.少用or，用它來連接時會索引失效
        11.小總結

    > 一般性建議

查詢截取分析

查詢優化

>     > 永遠小表驅動大表
    > order by關鍵字優化
        > 為排序使用索引
            mysql兩種排序方式：檔案排序或掃描有序索引排序
            mysql能為排序與查詢使用相同的索引

            key a_b_c(a,b,c)

            order by 能使用索引最左前綴
            - order by a
            - order by a,b
            - order by a,b,c
            - order by a DESC, b DESC, c DESC

            如果where使用索引的最左前綴定義為常量，則order by能使用索引
            - WHERE a = const ORDER BY b,c
            - WHERE a = const AND b = const ORDER BY c
            - WHERE a = const AND b > const ORDER BY b,c

            不能使用索引進行排序
            - ORDER BY a ASC,b DESC,c DESC  /*排序不一致*/
            - WHERE g = const ORDER BY b,c     /*丟失a索引*/
            - WHERE a = const ORDER BY c     /*丟失b索引*/
            - WHERE a = const ORDER BY a,d     /*d不是索引的一部分*/
            - WHERE a in(...) ODER BY b,c     /*對于排序來說，多個相等條件也是范圍查詢*/


    > group by關鍵字優化
        group by實質是先排序后進行分組，遵照索引建的最佳左前綴，
        當無法使用索引列，增大max_length_for_sort_data引數的設定 + 增大sort_buffer_size引數的設定
        where高于having，能寫在where限定的條件就不要去having限定了，

慢查詢日志

批量資料腳本

Show Profile

全域查詢日志

> 慢查詢日志
    默認情況下，mysql 資料庫沒有開啟慢查詢日志，
    SHOW VARIABLES LIKE ‘%slow_query_log%’
    開啟：set global slow_query_log = 1;  只對當前資料庫生效，MySql重啟后會失效，如果要永久生效，就必須修改組態檔my.cnf

> 批量資料腳本

> Show Profile
    > 是什么：是mysql提供可以用來分析當前會話中陳述句執行的資源消耗情況，可以用于SQL的調優的測量，
    > 默認情況下，引數處于關閉狀態，并保存最近15次的運行結果，

> 全域查詢日志

mysql鎖機制

> ?    讀鎖(共享鎖)：針對同一份資料，多個讀操作可以同時進行而不會互相影響，
?        當前session可以查詢該表記錄；當前session不能查詢其他沒有鎖定的表；當前session中插入或者更新鎖定的表都會提示錯誤，
?        其他session也可以查詢該表的記錄；其他session可以查詢或者更新未鎖定的表；其他session插入或者更新鎖定表會一直等待獲得鎖；

    寫鎖(排他鎖)：當前寫操作沒有完成前，它會阻斷其他讀鎖和寫鎖，
        當前session對鎖定表的查詢+更新+插入操作都可以執行
        其他session對鎖定表的查詢被阻塞，需要等待鎖被釋放，

> 表鎖(偏讀)
    > 手動添加表鎖：lock table 表名 read(write),表名2 read(write),其他;
    > 查看表上加過的鎖：show open tables;
    > 釋放表：unlock tables;

    MyISAM在執行查詢陳述句(SELECT)前，會自動給所有涉及的表加讀鎖，在執行增刪改操作前，會自動給涉及的表加寫鎖，
    MySQL的表級鎖有兩種模式：
        表共享讀鎖(Table Read Lock)
        表獨占寫鎖(Table Write Lock)
    1.對MyISAM表的讀操作(加讀鎖)，不會阻塞其他行程對同一表的讀請求，但會阻塞對同一表的寫請求，只有當讀鎖釋放后，才會執行其他行程的寫操作，
    2.對MyISAM表的寫操作(加寫鎖)，會阻塞其他行程對同一表的讀和寫操作，只有當寫鎖釋放后，才會執行其他行程讀寫操作，
    簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀，而寫鎖則會把讀和寫都阻塞，

> 行鎖(偏寫)

    更新丟失：版本覆寫
    臟讀：事務A讀取到了事務B已修改但尚未提交的資料，
    不可重復讀：事務A讀到了事務B已經提交的修改資料，不符合隔離性，
    幻讀：事務A讀取到了事務B提交的新增資料，

    臟讀是事務B里面修改了資料，幻讀是事務B里面新增了資料， 

    > 無索引行鎖升級為表鎖，

    > 如何鎖定一行： select * from 表名 where 條件 for update;


InnoDB和MyISAM的最大不同有兩點：一是支持事務，二是采用了行級鎖，

主從復制

參考

https://www.bilibili.com/video/BV12b411K7Zu  //尚硅谷mysql教程
https://www.bilibili.com/video/BV1iq4y1u7vj   //尚硅谷 mysql8.0.26 有課件
https://www.cnblogs.com/kerrycode/p/11836647.html
https://zhuanlan.zhihu.com/p/150107974    //大白話講解臟寫、臟讀、不可重復讀和幻讀
https://www.docs4dev.com/docs/zh/mysql/5.7/reference/    //mysql 5.7 中文檔案
https://blog.csdn.net/wb1046329430/article/details/114783334    //BLOB 和 TEXT 型別
https://zhuanlan.zhihu.com/p/117476959    //MySQL事務隔離級別和實作原理

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

標籤：其他

上一篇：子查詢優化之 Semi-join 優化 | StoneDB 研發分享 #2

下一篇：1.1 大資料簡介-hadoop-最全最完整的保姆級的java大資料學習資料

mysql 基礎知識

學習中的思考

MySQL 基礎

一、為什么要學習資料庫

二、資料庫的相關概念：

三、資料庫存盤結構的特點：

四、初識MySQL

MySQL產品的介紹

MySQL產品的安裝

MySQL安裝

MySQL卸載

MySQL服務的啟動和停止

MySQL服務端的登錄和退出

MySQL常見的命令

MySQL語法規范

五、DQL(Data Query Language)語言的學習

基礎查詢

空值參與運算

運算子

算數運算子

比較運算子

邏輯運算子

位運算子

條件查詢

排序查詢

常見函式

單行函式

字符函式

數學函式

日期函式

其他函式

流程控制函式

加密與解密函式

mysql 資訊函式

其他函式

分組函式

分組查詢

連接查詢

sql92 標準

等值連接

非等值連接

自連接

sql99 語法

內連接：inner

外連接

交叉連接：cross

sql99 語法新特性

自然連接

USING 連接

子查詢

where 或 haing 后面

標量子查詢

列子查詢(多行子查詢)

行子查詢(結果集一行多列或多行多列)

select后面：僅僅支持標量子查詢

from 后面

exists后面(相關子查詢)

子查詢經典案例

分頁查詢

union 聯合查詢

SELECT 的執行程序

查詢的結構

SELECT 執行順序

SQL 的執行原理

六、DML語言的學習

插入陳述句

方式一：經典的插入

方式二：

兩種方式大pk

修改陳述句

修改單表的記錄

修改多表的記錄(補充)

洗掉陳述句

方式一：delete

單表的洗掉

多表的洗掉

方式二：truncate (清空)

delete PK truncate (重點)

MySQL8 新特性：計算列

七、DDL語言的學習

`MySQL` 基礎

四、初識`MySQL`

`MySQL`產品的介紹

`MySQL`產品的安裝

`MySQL`安裝

`MySQL`卸載

`MySQL`服務的啟動和停止

`MySQL`服務端的登錄和退出

`MySQL`常見的命令

`MySQL`語法規范

五、`DQL`(Data Query Language)語言的學習

六、`DML`語言的學習

七、`DDL`語言的學習

八、`TCL`語言的學習

三、洗掉存盤程序

四、查看存盤程序的資訊