學習MySQL這一篇就夠了

輕鬆的小希發表於2020-09-02

MySql

第一章資料庫概述

1.1、資料庫的好處

將資料持久化到本地
提供結構化查詢功能

1.2、資料庫的常見概念

DB：資料庫，儲存資料的倉庫
DBMS：資料庫管理系統，又稱為資料庫軟體或者資料庫產品，用於建立和管理資料庫，常見的有MySQL、Oracle、SQL Server
DBS：資料庫系統，資料庫系統是一個通稱，包括資料庫、資料庫管理系統、資料庫管理人員等，是最大的範疇
SQL：結構化查詢語言，用於和資料庫通訊的語言，不是某個資料庫軟體特有的，而是幾乎所有的主流資料庫軟體通用的語言

1.3、資料庫的儲存特點

資料存放到表中，然後表再放到庫中
一個庫中可以有多張表，每張表具有唯一的表名用來標識自己
表中有一個或多個列，列又稱為“欄位”，相當於Java中“屬性”
表中的每一行資料，相當於Java中“物件”

1.4、資料庫的常見分類

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

1.5、SQL語言的分類

DQL：資料查詢語言：select、from、where
DML：資料操作語言：insert、update、delete
DDL：資料定義語言：create、alter、drop、truncate
DCL：資料控制語言：grant、revoke
TCL：事務控制語言：commit、rollback

第二章 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：%任意多個字元、_任意單個字元，如果有特殊字元，需要使用escape轉義
- 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)) ASC ;

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、not in、any、some、all、exits

四、演示

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、修改語句

一、語法

1、單表更新
UPDATE 表名 SET 列 = 值,... WHERE 查詢條件;

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

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

二、演示

1、單表更新

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

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

2、多表更新

#修改張無忌的女朋友的手機號為13899888899，魅力值為1000
sql92語法：
UPDATE 
  boys bo,
  beauty b 
  SET b.`phone` = '13899888899',
      bo.`userCP` = 1000 
WHERE bo.`id` = b.`boyfriend_id` AND bo.`boyName` = '張無忌' ;

#修改張無忌的女朋友的手機號為13899888899，魅力值為1000
sql99語法：
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、刪除語句

一、語法

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 篩選條件 ;

二、演示

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` = '黃曉明' ;

第五章 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】 表名;

方式二：TRUNCATE 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 欄位名 欄位型別 約束;

第六章 DCL語言

關於授權的許可權列表：

6.1、建立使用者

CREATE USER 使用者名稱@'IP地址' IDENTIFIED BY '密碼';
注意：'IP地址'可以設定為localhost(代表本機)或者'%'(代表允許所有IP地址登入)

6.2、刪除使用者

DROP USER 使用者名稱@'IP地址';
注意：'IP地址'可以設定為localhost(代表本機)或者'%'(代表允許所有IP地址登入)

6.3、使用者授權

GRANT 許可權1,許可權2,...... ON 資料庫名.* TO 使用者名稱@'IP地址' IDENTIFIED BY '密碼';
注意：所有的資料庫就用*.*，所有的許可權就用all或者all privileges

6.4、撤銷授權

REVOKE 許可權1,許可權2,...... ON 資料庫名.* FROM 使用者名稱@'IP地址' IDENTIFIED BY '密碼';
注意：所有的資料庫就用*.*，所有的許可權就用all或者all privileges

6.5、重新整理授權

FLUSH PRIVILEGES;

6.6、檢視授權

SHOW GRANTS FOR 使用者名稱@'IP地址';
注意：'IP地址'可以設定為localhost(代表本機)或者'%'(代表允許所有IP地址登入)

6.7、修改密碼

#修改密碼
SET PASSWORD = PASSWORD('123456');
#登入授權
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
#重新整理授權
FLUSH PRIVILEGES;

第七章 TCL語言

7.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 回滾點名;

7.2、事務併發（讀問題）

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

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

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

髒讀：一個事務讀到了另一個事務還未提交的update資料，導致多次查詢的結果不一樣
不可重複讀：一個事務讀到了另一個事務已經提交的update資料，導致多次查詢結果不一致
幻讀：一個事務讀到了另一個事務已經提交的insert資料，導致多次查詢的結果不一樣

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

通過設定隔離級別來解決併發問題

四、隔離級別

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

五、注意問題

mysql 中預設第三個隔離級別 REPEATABLE READ
oracle中預設第二個隔離級別 READ COMMITTED

檢視隔離級別 SELECT @@tx_isolation;
設定隔離級別 SET SESSION|GLOBAL TRANSACTION ISOLATION LEVEL 隔離級別;

7.3、丟失更新（寫問題）

一、定義

在事務的隔離級別內容中，能夠了解到兩個不同的事務在併發的時候可能會發生資料的影響。細心的話可以發現事務隔離級別章節中，髒讀、不可重複讀、幻讀三個問題都是由事務A對資料進行修改、增加，事務B總是在做讀操作。如果兩事務都在對資料進行修改則會導致另外的問題：丟失更新。

二、解決

悲觀鎖：認為兩個事務更新操作一定會發生丟失更新
- 解決：通過在語句後邊新增for update來實現行級上鎖，所以又稱為“行級鎖”，例如：select * from t_account t wheret.id=‘1’ for update;
樂觀鎖：認為事務不一定會產生丟失更新，讓事務進行併發修改，不對事務進行鎖定
- 解決：由程式設計師自己解決，可以通過給資料表新增自增的version欄位或時間戳timestamp，進行資料修改時，資料庫會檢測version欄位或者時間戳是否與原來的一致，若不一致，丟擲異常或者重新查詢

三、注意

對於賬戶交易建議直接使用悲觀鎖，資料庫的效能很高，併發度不是很高的場景兩者效能沒有太大差別。如果是交易減庫存的操作可以考慮樂觀鎖，保證併發度。

第八章高階部分

8.1、索引

一、含義

索引（index）是幫助MySQL高效獲取資料的一種有序的資料結構

二、特點

優勢：
- 類似於書籍的目錄索引，提高資料檢索的效率，降低資料庫的IO成本
- 通過索引列對資料進行排序，降低資料排序的成本，降低CPU的消耗
劣勢：
- 實際上索引也是一張表，該表中儲存了主鍵與索引欄位，並指向實體類的記錄，所以索引列也是要佔用空間的
- 雖然索引大大提高了查詢效率，同時卻也降低更新表的速度，如對錶進行INSERT、UPDATE、DELETE。因為更新表時，MySQL 不僅要儲存資料，還要儲存一下索引檔案每次更新新增了索引列的欄位，都會調整因為更新所帶來的鍵值變化後的索引資訊

三、語法

1、建立

CREATE 【 UNIQUE|FULLTEXT|SPATIAL 】 INDEX 索引名稱 ON 表名(欄位列表);

2、修改

先刪除，在建立

3、刪除

DROP INDEX 索引名稱 ON 表名;

4、檢視

SHOW INDEX FROM 表名;

5、alter命令

#該語句新增一個主鍵，這意味著索引值必須是唯一的，且不能為NULL
ALTER TABLE 表名 ADD PRIMARY KEY(欄位列表); 
	
#這條語句建立索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）
ALTER TABLE 表名 ADD UNIQUE 索引名稱(欄位列表);
	
#新增普通索引，索引值可以出現多次。
ALTER TABLE 表名 ADD INDEX 索引名稱(欄位列表);
	
#該語句指定了索引為FULLTEXT，用於全文索引	
ALTER TABLE 表名 ADD FULLTEXT 索引名稱(欄位列表);

四、注意

索引的設計可以遵循一些已有的原則，建立索引的時候請儘量考慮符合這些原則，便於提升索引的使用效率，更高效的使用索引：

索引欄位的選擇，最佳候選列應當從where子句的條件中提取，如果where子句中的組合比較多，那麼應當挑選最常用、過濾效果最好的列的組合
索引可以有效的提升查詢資料的效率，但索引數量不是多多益善，索引越多，維護索引的代價自然也就水漲船高。對於插入、更新、刪除等DML操作比較頻繁的表來說，索引過多，會引入相當高的維護代價，降低DML操作的效率，增加相應操作的時間消耗。另外索引過多的話，MySQL也會犯選擇困難病，雖然最終仍然會找到一個可用的索引，但無疑提高了選擇的代價
對查詢頻次較高，且資料量比較大的表建立索引
使用唯一索引，區分度越高，使用索引的效率越高
使用短索引，索引建立之後也是使用硬碟來儲存的，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率。假如構成索引的欄位總長度比較短，那麼在給定大小的儲存塊內可以儲存更多的索引值，相應的可以有效的提升MySQL訪問索引的I/O效率
使用組合索引，如果查詢時where子句中使用了組成該索引的前幾個欄位，那麼這條查詢SQL可以利用組合索引來提升查詢效率。例如：CREATE INDEX idx_name_email_status ON tb_seller(name,email,status); 就相當於對name 建立索引；對name , email 建立了索引；對name , email, status 建立了索引

8.2、檢視

一、含義

MySQL在5.1以後推出了檢視（VIEW），本身是一個虛擬表，它的資料來自於表，通過執行時動態生成

二、特點

簡化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中的表
用到了不可更新的檢視

8.3、儲存過程

8.3.1、語法

一、含義

儲存過程，類似於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、修改

先刪除，在建立

3、刪除

DROP PROCEDURE 儲存過程名;

4、檢視

SHOW CREATE PROCEDURE 儲存過程名;

8.3.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 變數名;

8.3.3、分支結構

8.3.3.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)$

8.3.3.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);

8.3.4、迴圈結構

一、分類

二、演示

8.3.4.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);

8.3.4.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);

8.3.4.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);

8.3.5、遊標

一、含義

遊標是用來儲存查詢結果集的資料型別 , 在儲存過程和函式中可以使用游標對結果集進行迴圈的處理

二、語法

1、宣告遊標

DECLARE 遊標名稱 CURSOR FOR select結果集;

2、開啟遊標

OPEN 遊標名稱;

3、獲取遊標

FETCH 遊標名稱 INTO 變數列表（需要跟select結果集的欄位對應）;

4、關閉遊標

CLOSE 遊標名稱;

三、演示

建資料表以用來演示

CREATE TABLE emp (
  id INT (11) NOT NULL AUTO_INCREMENT,
  NAME VARCHAR (50) NOT NULL COMMENT '姓名',
  age INT (11) COMMENT '年齡',
  salary INT (11) COMMENT '薪水',
  PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;

INSERT INTO emp(id,NAME,age,salary) 
VALUES
(NULL,'金毛獅王',55,3800),
(NULL,'白眉鷹王',60,4000),
(NULL,'青翼蝠王',38,2800),
(NULL,'紫衫龍王',42,1800);

迴圈獲取該表的內容

DELIMITER $
CREATE PROCEDURE getAllRowsForEmpTable()
BEGIN
  DECLARE e_id INT(11);
  DECLARE e_name VARCHAR(50);
  DECLARE e_age INT(11);
  DECLARE e_salary INT(11);
  DECLARE has_data INT DEFAULT 1;
  
  DECLARE emp_result CURSOR FOR SELECT * FROM emp;
  DECLARE EXIT HANDLER FOR NOT FOUND SET has_data=0;
  
  OPEN emp_result;
  
  REPEAT
    FETCH emp_result INTO e_id,e_name,e_age,e_salary;
    SELECT CONCAT('id=',e_id , ', name=',e_name, ', age=', e_age, ', salary=',e_salary) as emp_info;
    UNTIL has_data = 0
  END REPEAT;
  
  CLOSE emp_result;
END$
DELIMITER ;

開啟命令列呼叫檢視

CALL getAllRowsForEmpTable();

8.4、函式

一、含義

其實函式就是一個有返回值的過程

二、語法

1、建立

DELIMITER $
CREATE FUNCTION 函式名(引數名 引數型別) 
RETURNS 返回型別
BEGIN
	函式過程體（一組合法的SQL語句）
END $
DELIMITER ;

2、修改

先刪除，在建立

3、刪除

DROP FUNCTION 函式名；

4、檢視

SHOW CREATE FUNCTION 函式名;

5、呼叫

SELECT 函式名(實參列表);

三、注意

函式體中肯定需要有return語句
儲存過程和函式的區別在於函式必須有返回值，而儲存過程沒有

8.5、觸發器

一、含義

MySQL在5.0以後推出了觸發器（TRIGGER），觸發器是與表有關的資料庫物件，指在 insert/update/delete 之前或之後，觸發並執行觸發器中定義的SQL語句集合。觸發器的這種特性可以協助應用在資料庫端確保資料的完整性、日誌記錄、資料校驗等操作。使用別名 OLD 和 NEW 來引用觸發器中發生變化的記錄內容，這與其他的資料庫是相似的。現在MySQL觸發器還只支援行級觸發，不支援語句級觸發

二、分類

觸發器型別	NEW 和 OLD的使用
INSERT 型觸發器	NEW 表示將要或者已經新增的資料
UPDATE 型觸發器	OLD 表示修改之前的資料 , NEW 表示將要或已經修改後的資料
DELETE 型觸發器	OLD 表示將要或者已經刪除的資料

三、語法

1、建立

DELIMITER $
CREATE TRIGGER 觸發器名稱 
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名稱 
【 FOR EACH ROW 】--行級觸發器
BEGIN
	觸發器過程體（一組合法的SQL語句）
END $
DELIMITER ;

2、更新

先刪除，在建立

3、刪除

DROP TRIGGER 觸發器名稱;

4、檢視

SHOW TRIGGERS;

四、演示

建立資料表以用來演示

CREATE TABLE emp (
  id INT (11) NOT NULL AUTO_INCREMENT,
  NAME VARCHAR (50) NOT NULL COMMENT '姓名',
  age INT (11) COMMENT '年齡',
  salary INT (11) COMMENT '薪水',
  PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
INSERT INTO emp(id,NAME,age,salary) 
VALUES
(NULL,'金毛獅王',55,3800),
(NULL,'白眉鷹王',60,4000),
(NULL,'青翼蝠王',38,2800),
(NULL,'紫衫龍王',42,1800);

CREATE TABLE emp_logs (
  id INT (11) NOT NULL AUTO_INCREMENT,
  operation VARCHAR (20) NOT NULL COMMENT '操作型別, insert/update/delete',
  operate_time DATETIME NOT NULL COMMENT '操作時間',
  operate_id INT (11) NOT NULL COMMENT '操作表的ID',
  operate_params VARCHAR (500) COMMENT '操作引數',
  PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;

編寫觸發器進行測試

建立 insert 型觸發器，完成插入資料時的日誌記錄：

DELIMITER $
CREATE TRIGGER emp_logs_insert_trigger
AFTER INSERT 
ON emp 
FOR EACH ROW 
BEGIN
  INSERT INTO emp_logs (id,operation,operate_time,operate_id,operate_params) VALUES(NULL,'insert',NOW(),new.id,CONCAT('插入後(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));	
END $
DELIMITER ;

建立 update 型觸發器，完成更新資料時的日誌記錄：

DELIMITER $
CREATE TRIGGER emp_logs_update_trigger
AFTER UPDATE 
ON emp 
FOR EACH ROW 
BEGIN
  INSERT INTO emp_logs (id,operation,operate_time,operate_id,operate_params) VALUES(NULL,'update',NOW(),new.id,CONCAT('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改後(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));                                                                      
END $
DELIMITER ;

建立delete 行的觸發器 , 完成刪除資料時的日誌記錄：

DELIMITER $
CREATE TRIGGER emp_logs_delete_trigger
AFTER DELETE 
ON emp 
FOR EACH ROW 
BEGIN
  INSERT INTO emp_logs (id,operation,operate_time,operate_id,operate_params) VALUES(NULL,'delete',NOW(),old.id,CONCAT('刪除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));                                                                      
END $
DELIMITER ;

依次呼叫以下的語句，檢視emp_logs是否有觸發器儲存的日誌記錄

INSERT INTO emp(id,NAME,age,salary) VALUES(NULL, '光明左使',30,3500);

UPDATE emp SET age = 39 WHERE id = 3;

DELETE FROM emp WHERE id = 5;

8.6、事件

一、含義

MySQL在5.1以後推出了事件排程器（Event Scheduler），與triggers的事件觸發不同，event類似與linux crontab計劃任務，用於時間觸發

二、特點

事件是一組SQL集合，簡單說就是mysql中的定時器，時間到了就執行
事件由一個特定的執行緒來管理的，也就是所謂的事件排程器，但是事件不能直接呼叫，通過單獨或呼叫儲存過程使用，在某一特定的時間點，觸發相關的SQL語句或儲存過程

三、語法

1、建立

MySQL事件排程器event_scheduler負責呼叫事件，它預設是關閉的。這個排程器不斷地監視一個事件是否要呼叫，要建立事件，必須開啟排程器。

#開啟事件排程器（任意一個都可以）
SET GLOBAL event_scheduler = ON; 
SET @@global.event_scheduler = ON; 
SET GLOBAL event_scheduler = 1; 
SET @@global.event_scheduler = 1; 

#關閉事件排程器（任意一個都可以）
SET GLOBAL event_scheduler = OFF; 
SET @@global.event_scheduler = OFF; 
SET GLOBAL event_scheduler = 0; 
SET @@global.event_scheduler = 0;

DELIMITER $
CREATE EVENT 事件名稱
ON SCHEDULE 執行時間和頻率
-- 執行時間和頻率有兩種形式AT和EVERY
-- 	使用 AT     關鍵字只會執行一次，格式如下：
-- 	AT CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'
-- 	使用 EVERY  關鍵字指定時間間隔，格式如下：
-- 	EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]

-- 可選項，使用 STARTS 關鍵字指定開始時間，格式如下：
-- STARTS CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'
-- 可選項，使用 ENDS   關鍵字指定結束時間，格式如下：
-- ENDS CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'

-- [ON COMPLETION [NOT] PRESERVE] 
-- 可選項，設定event的生命
--		ON COMPLETION NOT PRESERVE ：即計劃任務執行完畢後自動drop該事件（預設）
--		ON COMPLETION PRESERVE     ：即計劃任務執行完畢後不會drop該事件

-- [ENABLE | DISABLE] 
-- 可選項，設定event的狀態
--		ENABLE	：表示系統嘗試執行這個事件（預設）
--		DISABLE	：表示系統嘗試關閉這個事件

-- [COMMENT 'comment'] 
-- 可選項，設定event的備註
DO
BEGIN
        /* 在這裡寫SQL語句或者呼叫儲存過程 */
END$
DELIMITER ;

2、修改

先刪除，在建立

3、刪除

DROP EVENT 事件名稱;

4、檢視

SHOW EVENTS;

5、呼叫

#啟用事件
ALTER EVENT 事件名稱 ENABLE;

#禁用事件
ALTER EVENT 事件名稱 DISABLE;

四、注意

預設建立事件儲存在當前庫中，也可顯示指定事件建立在哪個庫中
通過show events只能檢視當前庫中建立的事件
事件執行完即釋放，如立即執行事件，執行完後，事件便自動刪除，多次呼叫事件或等待執行事件可以檢視到
如果兩個事件需要在同一時刻呼叫，mysql會確定呼叫他們的順序，如果要指定順序，需要確保一個事件至少在另一個事件1秒後執行
對於遞迴排程的事件，結束日期不能在開始日期之前
select可以包含在一個事件中，然而他的結果消失了，就好像沒執行過

第九章資料備份與恢復

9.1、資料匯出

1、匯出某張資料表

#格式：mysqldump -u賬戶 -p密碼 資料庫名 資料表名 > 檔案儲存路徑
mysqldump -uroot -p123456 test emp > D:/emp.sql

2、匯出整個資料庫

#格式：mysqldump -u賬戶 -p密碼 資料庫名 > 檔案儲存路徑
mysqldump -uroot -p123456 test > D:/test.sql

9.2、資料匯入

1、匯入某張資料表

#格式：mysql -u賬戶 -p密碼
#     use 資料庫名;
#     source 檔案儲存路徑
mysql -uroot -p123456
>use test;
>source D:/emp.sql

2、匯入整個資料庫

#格式：mysql -u賬戶 -p密碼 < 檔案儲存路徑
mysql -uroot -p123456 < test.sql

第十章安裝MySQL單例項

10.1、準備虛擬機器

虛擬機器配置：全新系統，桌面版

虛擬機器地址：進入系統，可上網

連線的工具：自行百度，在下載

SecureCRT：7.0.0

10.2、刪除自帶庫

[root@caochenlei ~]# rpm -qa | grep mysql
mysql-libs-5.1.73-8.el6_8.x86_64
[root@caochenlei ~]# rpm -e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps

10.3、安裝依賴包

[root@caochenlei ~]# yum -y install numactl perl libaio wget

10.4、下載服務端

下載：

[root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-server-5.6.49-1.el6.x86_64.rpm

檢視：

[root@caochenlei ~]# ll MySQL-server-5.6.49-1.el6.x86_64.rpm
-rw-r--r--. 1 root root 65467136 6月   3 13:36 MySQL-server-5.6.49-1.el6.x86_64.rpm

10.5、安裝服務端

[root@caochenlei ~]# rpm -ivh MySQL-server-5.6.49-1.el6.x86_64.rpm

10.6、下載客戶端

下載：

[root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-client-5.6.49-1.el6.x86_64.rpm

檢視：

[root@caochenlei ~]# ll MySQL-client-5.6.49-1.el6.x86_64.rpm
-rw-r--r--. 1 root root 21584380 6月   3 13:36 MySQL-client-5.6.49-1.el6.x86_64.rpm

10.7、安裝客戶端

[root@caochenlei ~]# rpm -ivh MySQL-client-5.6.49-1.el6.x86_64.rpm

10.8、啟動資料庫服務

[root@caochenlei ~]# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/caochenlei.err'.
..                                                         [確定]

10.9、檢視初始化密碼

[root@caochenlei ~]# cat /root/.mysql_secret
# The random password set for the root user at Mon Aug 24 23:06:53 2020 (local time): E4qT_4CJ9YNf0daG

10.10、修改初始化密碼

登入：

[root@caochenlei ~]# mysql -uroot -pE4qT_4CJ9YNf0daG

修改：

mysql> set password = password('123456');
Query OK, 0 rows affected (0.01 sec)

10.11、遠端的授權

授權：

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

重新整理：

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

退出：

mysql> exit
Bye

10.12、開放防火牆

[root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
[root@caochenlei ~]# /etc/rc.d/init.d/iptables save
iptables：將防火牆規則儲存到 /etc/sysconfig/iptables：     [確定]

10.13、新增自啟動

[root@caochenlei ~]# chkconfig --add mysql
[root@caochenlei ~]# chkconfig mysql on

10.14、測試連線

10.15、關閉系統

[root@caochenlei ~]# poweroff

Broadcast message from root@caochenlei
        (/dev/pts/1) at 23:24 ...

The system is going down for power off NOW!

第十一章安裝MySQL主從複製

11.1、準備虛擬機器

複製CentOS 6 64 bit-1 > CentOS 6 64 bit-2

啟動CentOS 6 64 bit-1：

啟動CentOS 6 64 bit-2：選擇“我已複製該虛擬機器”

11.2、主從複製介紹

主從複製允許將來自一個MySQL資料庫伺服器（主伺服器）的資料複製到一個或多個MySQL資料庫伺服器（從伺服器）。

11.3、主從複製架構

實現目標：一主一從

11.4、主節點配置

編輯：

注意：192.168.206.128為主節點

[root@caochenlei ~]# vi /usr/my.cnf

新增：

注意：在[mysqld]下一行新增以下資訊

#日誌名稱
log-bin=mysql-bin
#日誌格式：row、statement、mixed
binlog-format=row
#伺服器ID標識
server-id=1
#刷寫事務日誌
innodb_flush_log_at_trx_commit=1
#執行寫入1次同步1次
sync_binlog=1
#及時更新master_info
sync_master_info=1

重啟：

[root@caochenlei ~]# service mysql restart
Shutting down MySQL...                                     [確定]
Starting MySQL.....                                        [確定]

連線：

[root@caochenlei ~]# mysql -uroot -p123456

授權：

mysql> grant replication slave on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)

重新整理：

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

檢視：

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 397
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

11.5、從節點配置

編輯：

注意：192.168.206.129為從節點

[root@caochenlei ~]# vi /usr/my.cnf

新增：

注意：在[mysqld]下一行新增以下資訊

#日誌名稱
log-bin=mysql-bin
#日誌格式：row、statement、mixed
binlog-format=row
#伺服器ID標識
server-id=2
#是否只讀
read_only=1

停止：

[root@caochenlei ~]# service mysql stop
Shutting down MySQL..                                      [確定]

刪除：

注意：這裡不能直接重啟mysql是因為它們的auto.cnf中的server-uuid是一樣的，在進行接下來的配置的時候會失敗，所以要刪除從庫的server-uuid，讓它在啟動的時候自動生成一個全新的server-uuid

[root@caochenlei ~]# rm -f /var/lib/mysql/auto.cnf

啟動：

[root@caochenlei ~]# service mysql start
Starting MySQL...                                          [確定]

連線：

[root@caochenlei ~]# mysql -uroot -p123456

執行：

注意：MASTER_LOG_FILE=show master status的File、MASTER_LOG_POS=show master status的Position

CHANGE MASTER TO
MASTER_HOST='192.168.206.128',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=397;

開啟：

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

檢視：

注意：重點檢查Master_Log_File、Read_Master_Log_Pos是否和主節點的資訊一致，Slave_IO_Running、Slave_SQL_Running是否為YES

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.206.128
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 397
               Relay_Log_File: caochenlei-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 397
              Relay_Log_Space: 461
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: d7200ffb-e698-11ea-87e0-000c29e99cce
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.02 sec)

11.6、連線測試

測試連線：192.168.206.128（master）

建立資料庫：

CREATE DATABASE `mytest` CHARACTER SET utf8 ;

建立資料表：

CREATE TABLE `mytest`.`myuser` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR (20) NOT NULL,
  `password` VARCHAR (20) NOT NULL,
  PRIMARY KEY (`id`)
) ;

新增資料集：

INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('1', 'zhangsan', '123456'); 
INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('2', 'lisi', '123456'); 
INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('3', 'wangwu', '123456');

檢視資料集：

use mytest;
SELECT * FROM myuser;

連線測試：192.168.206.129（slave）

檢視資料集：

USE mytest; 
SELECT * FROM myuser;

最終的結論：

我們會發現我們只在192.168.206.128（master）建立資料庫、建立表、新增資料，而它的從節點會自動的拉取主節點的內容，這就是所謂的主從複製了。

第十二章安裝MySQL讀寫分離

12.1、環境準備

複製CentOS 6 64 bit-2 > CentOS 6 64 bit-3

注意：因為CentOS 6 64 bit-2已經是配置好的slave節點了，我們只需要做少量修改就能實現一主兩從的架構了，所以我們複製CentOS 6 64 bit-2，當然，你要是時間允許，機器效能允許，您也可以重新按照主從複製的步驟在搭建一臺slave，在這裡我們就不搭建了，只是進行一下複製修改，複製的時候，注意關機，先關閉slave，再關閉master，再複製，再重新命名，先啟動master，在啟動slave，在啟動新複製的那臺slave

啟動CentOS 6 64 bit-3：選擇“我已複製該虛擬機器”

12.2、讀寫分離介紹

以上已經完成了主從複製（一主一從）配置，然而這只是一個基本配置，加上一個proxysql實現MySQL讀寫分離，proxysql支援MySQL協議的的資料庫代理，程式訪問的不再是具體的資料庫，而是proxysql這個代理程式，使用者請求發向proxysql，如果是寫請求發往主節點，讀請求發下從節點組中，以此實現讀寫分離一定程式上減輕了主資料庫的io壓力。

12.3、讀寫分離架構

實現目標：一主兩從，基於上文中的一主一從，我們重新複製一個從節點，進行少量修改即可

12.4、修改從節點二

注意：主節點：CentOS 6 64 bit-1【128】、從節點一：CentOS 6 64 bit-2【129】、從節點二：CentOS 6 64 bit-3【130】

停止：

[root@caochenlei ~]# service mysql stop
Shutting down MySQL..                                      [確定]

刪除：

[root@caochenlei ~]# rm -f /var/lib/mysql/auto.cnf

修改：

[root@caochenlei ~]# vi /usr/my.cnf

#伺服器ID標識
server-id=3

啟動：

[root@caochenlei ~]# service mysql start
Starting MySQL.......                                      [確定]

登入：

[root@caochenlei ~]# mysql -uroot -p123456

檢視：

USE mytest; 
SELECT * FROM myuser;

測試：

第一步：在主節點（192.168.206.128）插入資料

INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('9', 'xiaojiu', '123456');

USE mytest; 
SELECT * FROM myuser;

第二步：在從節點（192.168.206.129）檢視資料

USE mytest; 
SELECT * FROM myuser;

第三步：在從節點（192.168.206.130）檢視資料

USE mytest; 
SELECT * FROM myuser;

注意：如果說從節點一或者從節點二有一個不正常工作，也就是不能主從複製，主要是它們的伺服器ID標識複製的時候是一樣的，開機因為已經新增了自啟動，所以肯定會衝突，有一個不能夠連線到主節點，只要這個時候重啟一下不能正常工作的那臺機器的mysql服務就行了，因為從節點二我們已經修改server-id=3了，這樣它們兩個從節點肯定不會衝突了，到這裡，一主兩從的模式就搭建好了，接下來就是配置讀寫分離了。

12.5、安裝proxysql

設下載源：

cat << EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

安裝依賴：

[root@caochenlei ~]# yum install -y mysql-libs perl-DBI perl-DBD-MySQL

開始安裝：

[root@caochenlei ~]# yum install -y proxysql-2.0.13-1

開放埠：

[root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 6032 -j ACCEPT
[root@caochenlei ~]# /etc/rc.d/init.d/iptables save
iptables：將防火牆規則儲存到 /etc/sysconfig/iptables：[確定]

[root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 6033 -j ACCEPT
[root@caochenlei ~]# /etc/rc.d/init.d/iptables save
iptables：將防火牆規則儲存到 /etc/sysconfig/iptables：[確定]

新增自啟動：

[root@caochenlei ~]# chkconfig --add proxysql
[root@caochenlei ~]# chkconfig proxysql on

刪除檔案：

[root@caochenlei ~]# rm -f /etc/proxysql.cnf

新增檔案：

[root@caochenlei ~]# vi /etc/proxysql.cnf

datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
        #本地登入的賬號和密碼
        admin_credentials="admin:admin"
        #本地登入的IP和埠號
        mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
        #遠端登入的IP和埠號
        interfaces="0.0.0.0:6033"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"
        connect_timeout_server=3000
        #監控賬號的賬號和密碼
        monitor_username="monitor"
        monitor_password="monitor"
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
        monitor_read_only_interval=1500
        monitor_read_only_timeout=500
        ping_interval_server_msec=120000
        ping_timeout_server=500
        commands_stats=true
        sessions_sort=true
        connect_retries_on_failure=10
}
#以下的配置我們會採用動態語句的形式配置
mysql_servers =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)

注意：6032是連線proxysq的管理埠，6033是對外提供服務的埠。

啟動服務：

[root@caochenlei ~]# service proxysql start
Starting ProxySQL: 2020-08-25 20:30:00 [INFO] Using config file /etc/proxysql.cnf
2020-08-25 20:30:00 [INFO] Using OpenSSL version: OpenSSL 1.1.1d  10 Sep 2019
2020-08-25 20:30:00 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
DONE!

12.6、刪除自帶庫

[root@caochenlei ~]# rpm -qa | grep mysql
mysql-libs-5.1.73-8.el6_8.x86_64
[root@caochenlei ~]# rpm -e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps

12.7、安裝依賴包

[root@caochenlei ~]# yum -y install numactl perl libaio wget

12.8、下載服務端

下載：

[root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-server-5.6.49-1.el6.x86_64.rpm

檢視：

[root@caochenlei ~]# ll MySQL-server-5.6.49-1.el6.x86_64.rpm
-rw-r--r--. 1 root root 65467136 6月   3 13:36 MySQL-server-5.6.49-1.el6.x86_64.rpm

12.9、安裝服務端

[root@caochenlei ~]# rpm -ivh MySQL-server-5.6.49-1.el6.x86_64.rpm

12.10、下載客戶端

下載：

[root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-client-5.6.49-1.el6.x86_64.rpm

檢視：

[root@caochenlei ~]# ll MySQL-client-5.6.49-1.el6.x86_64.rpm
-rw-r--r--. 1 root root 21584380 6月   3 13:36 MySQL-client-5.6.49-1.el6.x86_64.rpm

12.11、安裝客戶端

[root@caochenlei ~]# rpm -ivh MySQL-client-5.6.49-1.el6.x86_64.rpm

12.12、啟動資料庫服務

[root@caochenlei ~]# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/caochenlei.err'.
..                                                         [確定]

12.13、檢視初始化密碼

[root@caochenlei ~]# cat /root/.mysql_secret
# The random password set for the root user at Tue Aug 25 20:31:19 2020 (local time): IFIn7pcTznYpoLm1

12.14、修改初始化密碼

登入：

[root@caochenlei ~]# mysql -uroot -pIFIn7pcTznYpoLm1

修改：

mysql> set password = password('123456');
Query OK, 0 rows affected (0.01 sec)

退出：

mysql> exit
Bye

12.15、建立兩個賬號

這個操作是在master節點進行的，建立完成後，會自動複製到從節點，方便proxysql後來的監控和遠端登入的認證連線

注意：可以使用 select user,host from mysql.user; 檢視所有使用者

為proxysql建立監控賬號（主要做監控使用）：

create user 'monitor'@'%' identified by 'monitor';
grant all privileges on *.* to 'monitor'@'%' identified by 'monitor';
flush privileges;

為proxysql建立遠端賬號（主要是遠端登入）：

create user 'proxysql'@'%' identified by 'proxysql';
grant all privileges on *.* to 'proxysql'@'%' identified by 'proxysql';
flush privileges;

退出：

mysql> exit
Bye

12.16、配置proxysql

這個操作是在安裝proxysql的這臺機器上的操作，6032埠的預設賬號密碼為admin，如果想要修改請在第12.5步修改，然後重啟。

[root@caochenlei ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin>'

分別執行以下語句

# 配置主從複製的資訊
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(10,'192.168.206.128',3306,1,1000,10,'write mysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(20,'192.168.206.129',3306,1,10000,10,'read mysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(20,'192.168.206.130',3306,1,10000,10,'read mysql');

# 本地主機登入的賬號：預設就是admin:admin不用再配置

# 配置遠端登入的賬號：之前在master主節點中已經建立了
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql',1,20,1);

# 設定轉發規則
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',20,1);

# 立刻載入配置
load mysql servers to runtime;
load mysql users to runtime;
load mysql variables to runtime;
load mysql query rules to runtime;

# 配置寫到磁碟
save mysql servers to disk;
save mysql users to disk;
save mysql variables to disk;
save mysql query rules to disk;

執行完成以後退出

12.17、連線proxysql

注意：如果遠端連線不上，請把防火牆全部關掉試試，如果不行，請重新啟動一下proxysql，還是不行，請重頭排查一下問題，或者檢視日誌tail /var/lib/proxysql/proxysql.log！

第一種：遠端命令列連線測試

主節點、兩個從節點隨便找一臺測試一下：

##連線
[root@caochenlei ~]# mysql -uproxysql -pproxysql -h192.168.206.131 -P6033

##檢視所有資料庫
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.05 sec)

第二種：遠端圖形化視窗連線

注意這個埠是6033而不是3306，賬號和密碼我之前設定的都是proxysql

12.18、查詢監控資料

如果想要檢視sql語句在哪個資料庫執行的，在proxysql機器上請執行以下語句

[root@caochenlei ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin>'
admin> select * from stats_mysql_query_digest;

第十三章安裝MySQL分片叢集

13.1、環境準備

我們使用之前的讀寫分離時候配置的一主兩從這三臺伺服器，至於那個讀寫分離的資料庫中介軟體proxysql伺服器就用不著了，直接關機就行了，以下三臺依次從主節點開始啟動即可也就是CentOS 6 64 bit-1、CentOS 6 64 bit-2、CentOS 6 64 bit-3

13.2、分片叢集介紹

如今隨著網際網路的發展，資料的量級也是撐指數的增長，從GB到TB到PB。對資料的各種操作也是愈加的困難，傳統的關係性資料庫已經無法滿足快速查詢與插入資料的需求。這個時候NoSQL的出現暫時解決了這一危機。它通過降低資料的安全性，減少對事務的支援，減少對複雜查詢的支援，來獲取效能上的提升。

但是，在有些場合NoSQL一些折衷是無法滿足使用場景的，就比如有些使用場景是絕對要有事務與安全指標的。這個時候NoSQL肯定是無法滿足的，所以還是需要使用關係性資料庫。如果使用關係型資料庫解決海量儲存的問題呢？此時就需要做資料庫叢集，為了提高查詢效能將一個資料庫的資料分散到不同的資料庫中儲存。簡單來說，就是指通過某種特定的條件，將我們存放在同一個資料庫中的資料分散存放到多個資料庫（主機）上面，以達到分散單臺裝置負載的效果。而這其中，我們需要使用一個資料庫中介軟體來完成，它就是Mycat。

13.3、分片叢集架構

實現目標：一主兩從需要三組，再加上一個資料庫中介軟體伺服器，一共需要10臺

如何演示：但實際上呢，這個分片叢集是在主從複製模式上進行的演進，說白了，上邊的3組一主兩從，隨便拿出來一個都是一個主從複製模式，那我們前一章已經完成了一主兩從的學習，也就是已經配置好了這三臺伺服器，我們要是再拷貝出來兩組就顯得太麻煩了，學習嘛，怎麼簡單怎麼來，現在我們手頭只有一組，那我們就在這一組上進行演示，也就是一組上建立三個資料庫，我們學習的架構就是下邊這樣了和上邊的效果一模一樣，如下圖：

13.4、Mycat的介紹

Mycat 背後是阿里曾經開源的知名產品——Cobar。Cobar 的核心功能和優勢是 MySQL 資料庫分片，此產品曾經廣為流傳，據說最早的發起者對 MySQL 很精通，後來從阿里跳槽了，阿里隨後開源的 Cobar，並維持到 2013 年年初，然後，就沒有然後了。

Cobar 的思路和實現路徑的確不錯。基於 Java 開發的，實現了 MySQL 公開的二進位制傳輸協議，巧妙地將自己偽裝成一個 MySQL Server，目前市面上絕大多數 MySQL 客戶端工具和應用都能相容。比自己實現一個新的資料庫協議要明智的多，因為生態環境在哪裡擺著。

Mycat 是基於 cobar 演變而來，對 cobar 的程式碼進行了徹底的重構，使用 NIO 重構了網路模組，並且優化了 Buffer 核心，增強了聚合，Join 等基本特性，同時相容絕大多數資料庫成為通用的資料庫中介軟體。

簡單的說，MyCAT就是一個新穎的資料庫中介軟體產品，支援mysql叢集或者mariadb叢集，提供高可用性資料分片叢集。你可以像使用mysql一樣使用mycat。對於開發人員來說根本感覺不到mycat的存在。

它支援的資料庫產品非常的多如：

13.5、Mycat的分片

分片就是指通過某種特定的條件，將我們存放在同一個資料庫中的資料分散存放到多個資料庫（主機）上面，以達到分散單臺裝置負載的效果。

資料的切分（Sharding）根據其切分規則的型別，可以分為兩種切分模式。

（1）一種是按照不同的表（或者Schema）來切分到不同的資料庫（主機）之上，這種切分可以稱之為資料的垂直（縱向）切分

（2）另外一種則是根據表中的資料的邏輯關係，將同一個表中的資料按照某種條件拆分到多臺資料庫（主機）上面，這種切分稱之為資料的水平（橫向）切分。

13.6、Mycat的安裝

官方網址：Mycat

預設埠：8066

JDK：要求jdk必須是1.7及以上版本

MySQL：推薦mysql是5.5及以上版本

安裝JDK8：

#檢視軟體
rpm -qa | grep java
rpm -qa | grep jdk

#批量解除安裝
rpm -qa | grep jdk | xargs rpm -e --nodeps
rpm -qa | grep java | xargs rpm -e --nodeps

#線上安裝
yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel

#通過yum方式安裝預設安裝在/usr/lib/jvm檔案下，看看你自己安裝的具體是哪個版本
ll /usr/lib/jvm/

#編輯/etc/profile檔案，在檔案最後寫入環境變數，然後儲存退出
vi /etc/profile

# java environment
export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.262.b10-0.el6_10.x86_64
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/jre/lib/tools.jar:$JRE_HOME/lib:$CLASSPATH
export PATH=$JAVA_HOME/bin:$PATH

#使環境變數生效
source /etc/profile

#檢視安裝是否成功，這兩個都能出來就安裝成功了
[root@caochenlei ~]# java -version
openjdk version "1.8.0_262"
OpenJDK Runtime Environment (build 1.8.0_262-b10)
OpenJDK 64-Bit Server VM (build 25.262-b10, mixed mode)
[root@caochenlei ~]# javac -version
javac 1.8.0_262

下載：

注意：失效的話，請自行百度，然後上傳到虛擬機器

[root@caochenlei ~]# wget http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

解壓：

[root@caochenlei ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

安裝：

[root@caochenlei ~]# mv mycat /usr/local

新增防火牆：

注意：mycat資料埠預設為8066，管理埠預設為9066，我們都需要對外開放

[root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 8066 -j ACCEPT
[root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 9066 -j ACCEPT
[root@caochenlei ~]# /etc/rc.d/init.d/iptables save
iptables：將防火牆規則儲存到 /etc/sysconfig/iptables：[確定]

新增域名解析：

[root@caochenlei ~]# vi /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

#配置當前主機名稱的域名解析
192.168.206.128 caochenlei

#配置一主兩從服務的域名解析
192.168.206.128 master1
192.168.206.129 slave1
192.168.206.130 slave2

常用命令：

/usr/local/mycat/bin/mycat start 啟動
/usr/local/mycat/bin/mycat stop 停止
/usr/local/mycat/bin/mycat console 前臺執行，可以顯示日誌，新手建議使用
/usr/local/mycat/bin/mycat restart 重啟服務
/usr/local/mycat/bin/mycat pause 暫停
/usr/local/mycat/bin/mycat status 檢視啟動狀態

13.7、Mycat的配置

第一步：登入主節點建立三個資料庫分別是db1、db2、db3

CREATE DATABASE `db1`CHARACTER SET utf8; 
CREATE DATABASE `db2`CHARACTER SET utf8; 
CREATE DATABASE `db3`CHARACTER SET utf8;

第二步：修改schema.xml檔案

刪除原有配置

[root@caochenlei ~]# rm -f /usr/local/mycat/conf/schema.xml

新增我們配置

[root@caochenlei ~]# vi /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="STOREDB" checkSQLschema="false" sqlMaxLimit="100">
		<!-- rule採用按主鍵範圍分片，主鍵名為id（預設） -->
		<table name="TB_USERS" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
		<!-- rule採用按雜湊一致分片，主鍵名為order_id（自己修改） -->
		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="sharding-by-murmur-order" />
	</schema>

	<dataNode name="dn1" dataHost="OneMasterAndTwoSlave" database="db1" />
	<dataNode name="dn2" dataHost="OneMasterAndTwoSlave" database="db2" />
	<dataNode name="dn3" dataHost="OneMasterAndTwoSlave" database="db3" />

	<dataHost name="OneMasterAndTwoSlave" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<!-- 心跳語句 -->
		<heartbeat>select user()</heartbeat>
		<!-- 一主兩從 -->
		<writeHost host="hostM1" url="master1:3306" user="root" password="123456">
			<readHost host="hostS1" url="slave1:3306" user="root" password="123456" />
			<readHost host="hostS2" url="slave2:3306" user="root" password="123456" />
		</writeHost>
	</dataHost>
</mycat:schema>

第三步：修改server.xml檔案

server.xml幾乎儲存了所有mycat需要的系統配置資訊，最常用的是在此配置使用者名稱、密碼及許可權。

[root@caochenlei ~]# vi /usr/local/mycat/conf/server.xml

在system中新增UTF-8字符集設定，否則儲存中文會出現問號。

<property name="charset">utf8</property>

修改user的設定 , 我們這裡為STOREDB資料庫設定了兩個使用者。把它之前的兩個使用者刪掉，把我們們自己設定的放進去。

<user name="test">
	<property name="password">test</property>
	<property name="schemas">STOREDB</property>
</user>
<user name="root">
	<property name="password">root</property>
	<property name="schemas">STOREDB</property>
</user>

第四步：修改auto-sharding-rang-mod.txt檔案

修改主鍵範圍分片大小

刪除：

[root@caochenlei ~]# rm -f /usr/local/mycat/conf/auto-sharding-rang-mod.txt

新增：

[root@caochenlei ~]# vi /usr/local/mycat/conf/auto-sharding-rang-mod.txt

0-500M=0
500M1-1000M=1
1000M1-1500M=2

注意：以上意思是主鍵id在0-500M的資料放在分片1中，500M1-1000M的資料放到分片2中，1000M1-1500M的資料放到分片3中，超過1500M1，就報錯

第五步：修改rule.xml檔案

[root@caochenlei ~]# vi /usr/local/mycat/conf/rule.xml

新增雜湊訂單主鍵，因為預設主鍵名都是id，要想是自己定義的，就拷貝一份，重新起個名，然後修改columns成你資料表的那個主鍵名稱就行了

        <tableRule name="sharding-by-murmur-order">
                <rule>
                        <columns>order_id</columns>
                        <algorithm>murmur</algorithm>
                </rule>
        </tableRule>

修改分片數量，因為現在有db1、db2、db3個分片，找到<function name="murmur"中的count，修改為3，如以下所示：

        <function name="murmur"
                class="io.mycat.route.function.PartitionByMurmurHash">
                <property name="seed">0</property><!-- 預設是0 -->
                <property name="count">3</property><!-- 要分片的資料庫節點數量，必須指定，否則沒法分片 -->
                <property name="virtualBucketTimes">160</property><!-- 一個實際的資料庫節點被對映為這麼多虛擬節點，預設是160倍，也就是虛擬節點數是物理節點數的160倍 -->
                <!-- <property name="weightMapFile">weightMapFile</property> 節點的權重，沒有指定權重的節點預設是1。以properties檔案的格式填寫，以從0開始到count-1的整數值>也就是節點索引為key，以節點權重值為值。所有權重值必須是正整數，否則以1代替 -->
                <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
                        用於測試時觀察各物理節點與虛擬節點的分佈情況，如果指定了這個屬性，會把虛擬節點的murmur hash值與物理節點的對映按行輸出到這個檔案，沒有預設值，如果不
指定，就不會輸出任何東西 -->
        </function>

13.8、Mycat的啟動

在啟動之前，我們需要做一個密碼重置的工作，也不知道這是不是個BUG，但是，你要不做密碼重置，很可能Mycat它啟動不起來，我也是醉了，就這一個問題，搞了我一晚上的心態，操作步驟如下：

依次在主節點、從節點一、從節點二執行以下四條語句：

use mysql;
update user set password = password('123456') where user = 'root';
flush privileges;
set password for 'root' = password('123456');
#如果上句報錯使用下邊這個，不報錯就算了
set password = password('123456');

然後再啟動Mycat

[root@caochenlei ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...

13.9、Mycat的測試

注意：在這裡我使用圖形化介面來操作，先測試是否連通，如果不能連上，請檢查虛擬機器防火牆是否關閉，那兩個使用者是否配對，如果Mycat重啟不成功，請使用ps -ef | grep mycat檢視，然後使用kill強制殺死，再然後重新啟動，如果不知道什麼問題，請使用前臺執行，它會顯示出錯誤資訊，然後再解決

建立使用者表，然後插入資料，直接拷貝以下SQL程式碼到視窗，然後執行，必須一步一步來並且不要進行視窗操作對連線Mycat的那個連線，因為會報錯

#第一步：刪表
USE `STOREDB`;
DROP TABLE IF EXISTS `TB_USERS`;

#第二步：建表
CREATE TABLE `TB_USERS` (
  `id` bigint(20) NOT NULL,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#第三步：插入資料
insert  into `TB_USERS`(`id`,`username`,`password`) values (1,'zhangsan1','123456');
insert  into `TB_USERS`(`id`,`username`,`password`) values (2,'zhangsan2','123456');
insert  into `TB_USERS`(`id`,`username`,`password`) values (3,'zhangsan3','123456');
insert  into `TB_USERS`(`id`,`username`,`password`) values (5000001,'lisi1','123456');
insert  into `TB_USERS`(`id`,`username`,`password`) values (5000002,'lisi2','123456');
insert  into `TB_USERS`(`id`,`username`,`password`) values (5000003,'lisi3','123456');
insert  into `TB_USERS`(`id`,`username`,`password`) values (10000001,'wangwu1','123456');
insert  into `TB_USERS`(`id`,`username`,`password`) values (10000002,'wangwu2','123456');
insert  into `TB_USERS`(`id`,`username`,`password`) values (10000003,'wangwu3','123456');

建立訂單表，然後插入資料，直接拷貝以下SQL程式碼到視窗，然後執行，必須一步一步來並且不要進行視窗操作對連線Mycat的那個連線，因為會報錯

#第一步：刪表
USE `STOREDB`;
DROP TABLE IF EXISTS `TB_ORDER`;

#第二步：建表
CREATE TABLE `TB_ORDER` (
  `order_id` varchar(30) NOT NULL,
  `create_time` datetime NOT NULL,
  `pay_time` datetime NOT NULL,
  `total_fee` bigint(20) NOT NULL,
  `user_id` varchar(50) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#第三步：插入資料
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('201708261157265358046058','2017-08-26 11:57:26','2017-08-26 12:48:22',1,'lijialong');
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('201708261221397410698125','2017-08-26 12:21:39','2017-08-26 12:48:22',1,'lijialong');
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('201708261234474784646997','2017-08-26 12:34:47','2017-08-26 12:48:22',1,'lijialong');
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('201708261247443132289031','2017-08-26 12:47:44','2017-08-26 12:48:22',3,'lijialong');
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('918773291320152064534354','2017-10-13 17:39:57','2017-08-26 12:48:22',5,'lijialong');
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('918780410236788736453543','2017-10-13 18:08:14','2017-10-13 18:09:36',1,'lijialong');
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('918806410983137280453453','2017-10-13 19:51:33','2017-10-13 19:51:47',1,'lijialong');
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('918833486129815552274522','2017-10-13 21:39:08','2017-08-26 12:48:22',1,'lijialong');
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('918835712999055360745325','2017-10-13 21:47:59','2017-10-13 21:49:28',1,'lijialong');
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('919055625042825216545334','2017-10-14 12:21:50','2017-08-26 12:48:22',1,'lijialong');
insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('919059761058607104453433','2017-10-14 12:38:16','2017-10-14 12:38:24',2,'lijialong');

13.10、Mycat的效果

重開一個視窗，連線主節點，依次檢視，db1、db2、db3上的TB_USERS、TB_ORDER，你會發現資料是均勻分佈的，這就實現了我們最終的分片叢集了，具體效果圖如下：

db1>TB_USERS

db2>TB_USERS

db3>TB_USERS

db1>TB_ORDER

db2>TB_ORDER

db3>TB_ORDER

13.11、Mycat的踩坑

Mycat只是一個資料庫中介軟體，它並不是一個真正的資料庫，所以用視窗不能直接操作，需要使用SQL語句
Mycat依賴於JDK1.7+，我建議使用JDK1.8
Mycat只是幫你做了資料分片，真正的主從複製還是靠你自己實現
當你建立表的時候，Mycat預設會把表名轉換為大寫，所以你建立的時候就直接把表名大寫和資料庫名大寫，否則可能會找不到表，因為大小寫不對應
當你的主鍵欄位不是id的時候，直接在ruls.xml中拷貝一份相對應型別的tableRule，然後改個規則名稱和欄位名稱就行了，然後再引用自定義的那個

學習JDBC這一篇就夠了
2020-07-31
JDBC
學習Jmeter，這一篇就夠了
2020-10-10
JMeter
學習git這一篇就夠了！！！
2020-10-07
Git
學Nginx,這一篇就夠了
2020-09-25
Nginx
MySQL查詢這一篇就夠了
2020-11-09
MySql
學習Hibernate5這一篇就夠了
2020-08-07
學習AIDL，這一篇文章就夠了
2016-12-28
AI
MySQL入門看這一篇就夠了
2020-12-09
MySql
學習Hibernate5 JPA這一篇就夠了
2020-08-08
學習Spring5 WebFlux這一篇就夠了
2020-12-29
SpringWebUX
學習HTML5 Canvas這一篇文章就夠了
2019-02-16
HTMLCanvas
iOS 動畫詳解（學習動畫看這一篇就夠了）
2016-12-13
iOS動畫
APP瘦身這一篇就夠了
2019-04-10
APP
深入Babel，這一篇就夠了
2018-12-25
Babel
Git 看這一篇就夠了
2020-10-14
Git
【詳細圖解】學習佇列，看這一篇就夠了！
2021-04-06
圖解佇列
rodert教你學FFmpeg實戰這一篇就夠了
2022-03-18
Ubuntu搭建Pytorch，就這一篇就夠了
2022-07-18
UbuntuPyTorch
學習Less-看這篇就夠了
2017-12-10
[收藏]學習sed，看這篇就夠了
2016-03-23
Flutter DataTable 看這一篇就夠了
2020-03-04
Flutter
Git【入門】這一篇就夠了
2020-07-04
Git
代理模式看這一篇就夠了
2018-11-13
模式
Java 集合看這一篇就夠了
2020-10-15
Java
[譯] 理解CORS這一篇就夠了
2018-02-02
CORS
AJAX入門這一篇就夠了
2018-02-13
學Mybatis，入門看這一篇就夠你學的了！
2020-11-19
MyBatis
學Redis這篇就夠了
2019-07-14
Redis
c語言入門這一篇就夠了-學習筆記(一萬字)
2018-07-19
C語言筆記
瞭解 MongoDB 看這一篇就夠了
2020-01-08
MongoDB
關於SwiftUI，看這一篇就夠了
2020-06-24
SwiftUI
瞭解HandlerThread這一篇就夠了
2019-02-26
thread
這一篇就夠了——APP瘦身總結
2019-04-11
APP
Spring入門這一篇就夠了
2019-06-23
Spring
入門Hbase，看這一篇就夠了
2019-02-15
jQuery入門看這一篇就夠了
2021-04-18
jQuery
Elasticsearch入門，看這一篇就夠了
2021-05-17
Elasticsearch
關於MyBatis框架這一篇就夠了
2021-05-21
MyBatis框架

學習MySQL這一篇就夠了

目錄

第一章 資料庫概述

1.1、資料庫的好處

1.2、資料庫的常見概念

1.3、資料庫的儲存特點

1.4、資料庫的常見分類

1.5、SQL語言的分類

第二章 MySQL概述

2.1、MySQL的背景

2.2、MySQL的優點

2.3、MySQL的安裝

2.4、MySQL的啟動

2.5、MySQL的停止

2.6、MySQL的登入

2.7、MySQL的退出

第三章 DQL語言

3.1、基礎查詢

3.2、條件查詢

3.3、排序查詢

3.4、單行函式

3.5、分組函式

3.6、分組查詢

3.7、連線查詢

3.8、sql99標準

3.9、子查詢

3.10、分頁查詢

3.11、聯合查詢

第四章 DML語言

4.1、插入語句

4.2、修改語句

4.3、刪除語句

第五章 DDL語言

5.1、庫的管理

5.1.1、庫的建立

5.1.2、庫的修改

5.1.3、庫的刪除

5.2、表的管理

5.2.1、表的建立

5.2.2、表的修改

5.2.3、表的刪除

5.2.4、表的複製

5.3、資料型別

5.3.1、數值型

5.3.2、浮點型

5.3.3、字元型

5.3.4、日期型

5.4、常見約束

5.5、自增長列

第六章 DCL語言

6.1、建立使用者

6.2、刪除使用者

6.3、使用者授權

6.4、撤銷授權

6.5、重新整理授權

6.6、檢視授權

6.7、修改密碼

第七章 TCL語言

7.1、事務

7.2、事務併發（讀問題）

7.3、丟失更新（寫問題）

第八章 高階部分

8.1、索引

8.2、檢視

8.3、儲存過程

8.3.1、語法

8.3.2、變數

8.3.3、分支結構

8.3.3.1、if結構

8.3.3.2、case結構

8.3.4、迴圈結構

8.3.4.1、while結構

8.3.4.2、repeat結構

8.3.4.3、loop結構

8.3.5、遊標

8.4、函式

8.5、觸發器

8.6、事件

第九章 資料備份與恢復

9.1、資料匯出

第一章資料庫概述

第八章高階部分

第九章資料備份與恢復

第十章安裝MySQL單例項

第十一章安裝MySQL主從複製

第十二章安裝MySQL讀寫分離

第十三章安裝MySQL分片叢集