1. 簡單概述
MySQL安裝后默認有4個庫不可以刪除，存儲的是服務運行時加載的不同功能的程序和數據
- information_schema：是MySQL數據庫提供的一個虛擬的數據庫，存儲了MySQL數據庫中的相關信息，比如數據庫、表、列、索引、權限、角色等信息。它并不存儲實際的數據，而是提供了一些視圖和存儲過程，用于查詢和管理數據庫的元數據信息。
- mysql：存儲了MySQL服務器的系統配置、用戶、賬號和權限信息等。它是MySQL數據庫最基本的庫，存儲了MySQL服務器的核心信息。
- performance_schema：存儲了MySQL數據庫的性能指標、事件和統計信息等數據，可以用于性能分析和優化。
- sys：是MySQL 8.0引入的一個新庫，它基于information_schema和performance_schema視圖，提供了更方便、更直觀的方式來查詢和管理MySQL數據庫的元數據和性能數據。
MySQL圖形工具
- phpMyAdmin
- DBeaver
- MySOL-WorkBench
1.1 相關信息&配置文件
| 說明 | MySQL信息 |
|---|---|
| 主配置文件 | /etc/my.cnf.d/mysql-server.cnf |
| 數據庫目錄 | /var/lib/mysql |
| 端口號 | 3306 |
| 進程號 | mysql |
| 傳輸協議 | TCP |
| 進程所有者 | mysql |
| 進程所屬組 | mysql |
| 錯誤日志文件 | /var/log/mysql/mysql.log |
主配置文件默認配置
| 配置項 | 功能說明 |
|---|---|
| datadir | 定義數據庫目錄位置 |
| socket | 定義socket文件名稱及存放位置 |
| log-error | 定義錯誤日志文件名稱及存放位置 |
| pid-file | 定義存放數據庫服務進程pid號文件名及存放位置 |
# 主配置文件
[root@mysql50 ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
2. 增刪改查
2.1 insert 插入表記錄
2.1.1 不指定列名插入
- 不指定列名插入記錄（必須給所有列賦值）
- insert into 庫名.表名 values(值列表)； //插入一行
- insert into 庫名.表名 values(值列表),(值列表)； //插入多行
mysql> insert into tarena.user values-> (101,'user1','x',3001,3001,'test','/home/user1','/bin/sh'),-> (101,'user2','x',3002,3002,'test','/home/user1','/bin/false');
Query OK, 2 rows affected (0.23 sec)
Records: 2 Duplicates: 0 Warnings: 0
6.1.2 指定列名插入
- 指定列名插入記錄（僅須給指定列賦值）
- insert into 庫名.表名(列名列表) values(值列表)； //插入一行
- insert into 庫名.表名(列名列表) values(值列表),(值列表);//插多行
mysql> insert into tarena.user(name,uid,shell) values-> ('user3','3003','/sbin/nologin'),-> ('user4',3004,'/bin/bash');
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
6.1.3 使用select查詢結果賦值
mysql> select user from mysql.user;
+------------------+
| user |
+------------------+
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
4 rows in set (0.00 sec)mysql> insert into tarena.user(name) (select user from mysql.user);
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0# 查看插入后的數據
mysql> select * from tarena.user where name like "mysql%" or name="root";
+----+------------------+----------+------+------+--------------+----------------+------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------------------+----------+------+------+--------------+----------------+------------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
| 26 | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false |
| 44 | mysql.infoschema | NULL | NULL | NULL | NULL | NULL | NULL |
| 45 | mysql.session | NULL | NULL | NULL | NULL | NULL | NULL |
| 46 | mysql.sys | NULL | NULL | NULL | NULL | NULL | NULL |
| 47 | root | NULL | NULL | NULL | NULL | NULL | NULL |
+----+------------------+----------+------+------+--------------+----------------+------------+
6 rows in set (0.00 sec)
6.1.4 使用set命令賦值
# 實際情況下用set命令不常見
mysql> insert into tarena.user set name="yaya" , uid=99 , gid=99 ;
Query OK, 1 row affected (0.06 sec)mysql> select * from tarena.user where name="yaya";
+----+------+----------+------+------+---------+---------+-------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+-------+
| 28 | yaya | NULL | 99 | 99 | NULL | NULL | NULL |
+----+------+----------+------+------+---------+---------+-------+
1 row in set (0.00 sec)
2.2 delete 刪除表記錄
delete from 庫名.表名 where 條件
# 刪除的時候務必看好，一定要加where條件，要不然你會后悔的
mysql> delete from tarena.user where name is null;
Query OK, 2 rows affected (0.08 sec)
2.3 truncate 刪除表記錄
-
DELETE：-
用于刪除表中的部分或全部數據。
-
可以使用
WHERE子句指定刪除條件，如果不加條件，則刪除表中的所有數據。 -
示例：
DELETE FROM table_name WHERE condition; -- 刪除滿足條件的行 DELETE FROM table_name; -- 刪除表中所有數據
-
-
TRUNCATE：-
僅用于刪除表中的全部數據，不能指定條件。
-
相當于“清空”表，但保留表的結構。
-
示例：
TRUNCATE TABLE table_name;
-
2.4 update 修改表記錄
update 庫名.表名 set 字段1=值,字段2=值 where 條件
# 改的時候一定要加上where條件，要不然會影響整個表
mysql> update tarena.user -> set homedir='/home/user5',shell='/bin/bash' -> where name='user5';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2.5 select 查詢
2.5.1 select語法
語法格式一：select 字段列表 from 庫名.表名;
語法格式二：select 字段列表 from 庫名.表名 where 篩選條件;
2.5.2 條件篩選
數值比較
< <= > >= = !=
字符串比較
- =
- !=
范圍匹配
select 字段名 from 庫名.表名 where 字段名 not in ("值1","值2");
-
in （值列表） //在…里
-
not in （值列表） //不在…里
-
between 數字1 and 數字2 //在…之間
like 模糊匹配
select 字段名 from 庫名.表名 where 字段名 like "通配符";
- _ 表示 1個字符
- % 表示零個或多個字符
regexp 正則匹配
select 字段名 from 庫名.表名 where 字段名 regexp '正則表達式';
- ^ 匹配行首
- $ 匹配行尾
- [] 匹配范圍內任意一個
- 前邊的表達式出現零次或多次
- | 或者
- . 任意一個字符
邏輯運算
-
邏輯與 and （&&） 多個判斷條件必須同時成立
-
邏輯或 or （||） 多個判斷條件其中某個條件成立即可
-
在這里插入圖片描述
邏輯非 not （!） 取反 ， 放在表達式前面
-
邏輯與and 優先級高于邏輯或 or
-
（）提高優先級
示例：
# 沒加（） 的查詢結果 mysql> select name , uid from tarena.user where name = "root" or name = "bin" and uid = 1 ; +------+------+ | name | uid | +------+------+ | root | 0 | | bin | 1 | +------+------+ 2 rows in set (0.00 sec)# 加（）的查詢結果 mysql> select name , uid from tarena.user where (name = "root" or name = "bin") and uid = 1 ; +------+------+ | name | uid | +------+------+ | bin | 1 | +------+------+ 1 row in set (0.00 sec) mysql>
空與非空
select 字段名 from 庫名.表名 where 字段名 is null;
- is null表頭下沒有數據 ----> 空
- is not null表頭下有數據 ----> 非空
查詢結果的顯示結果
3. 常用函數
格式一：select 函數(表頭名) from 庫名.表名;
格式一：select 函數(表頭名) from 庫名.表名 where 條件;
3.1 字符函數
作用：處理字符或字符類型的表頭
- length(str) 返回字符串長度，以字節為單位 （一個漢字3個字節）
- char_length(str) 返回字符串長度，以字符單位
- upper(str)和ucase(str) 將str中的字母全部轉換成大寫
- lower(str)和lcase(str) 將str中的字母全部轉換成小寫
- substr(‘hello’,‘2’,‘3’) 從hello的第2個字符e開始取出3個字符長度，結果為ell
- instr(‘hello’,‘l’) 返回’l’參數在’hello’參數內的位置，結果為3
- trim(s) 返回字符串s刪除了兩邊空格之后的字符
3.2 數字函數
作用：處理數字或數值類型的表頭
- abs(x) 返回x的絕對值
- pi() 返回圓周率π，默認顯示6位小數
- mod(x,y) 返回x被y除后的余數
- ceil(x)、ceiling(x) 向上取整
- floor(x) 向下取整
- round(9.5) 四舍五入取整為10
- round(9.58585,3) 代表取小數點后三位為9.586
mysql> select ceil(9.6),ceil(9.2),floor(9.2),floor(9.6);
+-----------+-----------+------------+------------+
| ceil(9.6) | ceil(9.2) | floor(9.2) | floor(9.6) |
+-----------+-----------+------------+------------+
| 10 | 10 | 9 | 9 |
+-----------+-----------+------------+------------+
1 row in set (0.00 sec)
mysql> select round(9.2),round(9.6),round(pi(),3);
+------------+------------+---------------+
| round(9.2) | round(9.6) | round(pi(),3) |
+------------+------------+---------------+
| 9 | 10 | 3.142 |
+------------+------------+---------------+
1 row in set (0.01 sec)
3.3 日期函數
mysql> select now(),curdate(),curtime();
+---------------------+------------+-----------+
| now() | curdate() | curtime() |
+---------------------+------------+-----------+
| 2024-12-12 10:28:07 | 2024-12-12 | 10:28:07 |
+---------------------+------------+-----------+
1 row in set (0.00 sec)mysql> select date(now()),time(now());
+-------------+-------------+
| date(now()) | time(now()) |
+-------------+-------------+
| 2024-12-12 | 10:33:19 |
+-------------+-------------+
1 row in set (0.00 sec)mysql> select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());
+-------------+--------------+------------+-------------+---------------+---------------+
| year(now()) | month(now()) | day(now()) | hour(now()) | minute(now()) | second(now()) |
+-------------+--------------+------------+-------------+---------------+---------------+
| 2024 | 12 | 12 | 10 | 34 | 25 |
+-------------+--------------+------------+-------------+---------------+---------------+
1 row in set (0.00 sec)mysql> select week(now()),weekday(now()),monthname(now()),dayname(now());
+-------------+----------------+------------------+----------------+
| week(now()) | weekday(now()) | monthname(now()) | dayname(now()) |
+-------------+----------------+------------------+----------------+
| 49 | 3 | December | Thursday |
+-------------+----------------+------------------+----------------+
1 row in set (0.00 sec)mysql> select quarter(now()),dayofyear(now()),dayofmonth(now());
+----------------+------------------+-------------------+
| quarter(now()) | dayofyear(now()) | dayofmonth(now()) |
+----------------+------------------+-------------------+
| 4 | 347 | 12 |
+----------------+------------------+-------------------+
1 row in set (0.00 sec)
3.4 聚集函數
作用：數據統計
聚合函數還是挺重要的
- avg(表頭名) //計算平均值
- sum(表頭名) //求和
- min(表頭名) //獲取最小值
- max(表頭名) //獲取最大值
- count(表頭名) //統計字段值個數
3.5 數學計算
作用：對行中的列做計算
| 符號 | 用途 | 例子 |
|---|---|---|
| + | 加法 | uid + gid |
| - | 減法 | uid - gid |
| * | 乘法 | uid * gid |
| / | 除法 | uid / gid |
| % | 取余數(求模) | uid % gid |
| () | 提高優先級 | (uid + gid) / 2 |
mysql> select employee_id,date,basic,bonus from tarena.salary where employee_id=8 and year(date)=2019;
+-------------+------------+-------+-------+
| employee_id | date | basic | bonus |
+-------------+------------+-------+-------+
| 8 | 2019-01-10 | 23093 | 1000 |
| 8 | 2019-02-10 | 23093 | 4000 |
| 8 | 2019-03-10 | 23093 | 10000 |
| 8 | 2019-04-10 | 23093 | 1000 |
| 8 | 2019-05-10 | 23093 | 4000 |
| 8 | 2019-06-10 | 23093 | 7000 |
| 8 | 2019-07-10 | 23093 | 6000 |
| 8 | 2019-08-10 | 23093 | 1000 |
| 8 | 2019-09-10 | 23093 | 1000 |
| 8 | 2019-10-10 | 23093 | 8000 |
| 8 | 2019-11-10 | 23093 | 10000 |
| 8 | 2019-12-10 | 24247 | 10000 |
+-------------+------------+-------+-------+
12 rows in set (0.00 sec)
mysql> select employee_id,date,basic+bonus 月薪 from tarena.salary where employee_id=8 and year(date)=2019;
+-------------+------------+--------+
| employee_id | date | 月薪 |
+-------------+------------+--------+
| 8 | 2019-01-10 | 24093 |
| 8 | 2019-02-10 | 27093 |
| 8 | 2019-03-10 | 33093 |
| 8 | 2019-04-10 | 24093 |
| 8 | 2019-05-10 | 27093 |
| 8 | 2019-06-10 | 30093 |
| 8 | 2019-07-10 | 29093 |
| 8 | 2019-08-10 | 24093 |
| 8 | 2019-09-10 | 24093 |
| 8 | 2019-10-10 | 31093 |
| 8 | 2019-11-10 | 33093 |
| 8 | 2019-12-10 | 34247 |
+-------------+------------+--------+
12 rows in set (0.00 sec)
mysql> select employee_id,sum(basic+bonus) 年薪 from tarena.salary where employee_id=8 and year(date)=2019;
+-------------+--------+
| employee_id | 年薪 |
+-------------+--------+
| 8 | 341270 |
+-------------+--------+
1 row in set (0.00 sec)mysql> select * from tarena.employees limit 1;
+-------------+--------+------------+------------+------------------+--------------+---------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
+-------------+--------+------------+------------+------------------+--------------+---------+
| 1 | 梁偉 | 2018-06-21 | 1971-08-19 | liangwei@tedu.cn | 13591491431 | 1 |
+-------------+--------+------------+------------+------------------+--------------+---------+
1 row in set (0.00 sec)mysql> select name,year(now()) - year(hire_date) as 工齡,-> year(now()) - year(birth_date) as 年齡-> from tarena.employees-> where employee_id=1;
+--------+----------+------+
| name | 工齡 | 年齡 |
+--------+----------+------+
| 梁偉 | 6 | 53 |
+--------+----------+------+
1 row in set (0.00 sec)
3.6 if 函數
# if(條件，v1，v2)如果條件是TRUE則返回v1，否則返回v2
mysql> select if(1 = 2 , "a","b");
+---------------------+
| if(1 = 2 , "a","b") |
+---------------------+
| b |
+---------------------+
1 row in set (0.00 sec)
mysql> select if( 1 = 1 , "a","b");
+---------------------+
| if(1 = 1 , "a","b") |
+---------------------+
| a |
+---------------------+
1 row in set (0.00 sec)# ifnull(v1,v2)如果v1不為NULL，則返回v1，否則返回v2
mysql> select ifnull("abc","xxx");
+---------------------+
| ifnull("abc","xxx") |
+---------------------+
| abc |
+---------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null,"xxx");
+--------------------+
| ifnull(null,"xxx") |
+--------------------+
| xxx |
+--------------------+
1 row in set (0.00 sec)# 根據UID判斷用戶類型
mysql> select name as 姓名 , uid as UID號 ,
if(uid < 1000 , "系統用戶","創建用戶") as 用戶類型 from tarena.user;
+-----------------+-------+--------------+
| 姓名 | UID號 | 用戶類型 |
+-----------------+-------+--------------+
| root | 0 | 系統用戶 |
| bin | 1 | 系統用戶 |
| daemon | 2 | 系統用戶 |
| adm | 3 | 系統用戶 |
| lp | 4 | 系統用戶 |
| sync | 5 | 系統用戶 |
| shutdown | 6 | 系統用戶 |
| halt | 7 | 系統用戶 |
| mail | 8 | 系統用戶 |
| operator | 11 | 系統用戶 |
| games | 12 | 系統用戶 |
| ftp | 14 | 系統用戶 |
| nobody | 99 | 系統用戶 |
| systemd-network | 192 | 系統用戶 |
| dbus | 81 | 系統用戶 |
| polkitd | 999 | 系統用戶 |
| sshd | 74 | 系統用戶 |
| postfix | 89 | 系統用戶 |
| chrony | 998 | 系統用戶 |
| rpc | 32 | 系統用戶 |
| rpcuser | 29 | 系統用戶 |
| nfsnobody | 65534 | 創建用戶 |
| haproxy | 188 | 系統用戶 |
| plj | 1000 | 創建用戶 |
| apache | 48 | 系統用戶 |
| mysql | 27 | 系統用戶 |
| bob | NULL | 創建用戶 |
+-----------------+-------+--------------+
27 rows in set (0.00 sec)
3.7 case 函數
命令格式:
CASE 表頭名
WHEN 值1 THEN 輸出結果
WHEN 值2 THEN 輸出結果
WHEN 值3 THEN 輸出結果
ELSE 輸出結果
END
或
CASE
WHEN 判斷條件1 THEN 輸出結果
WHEN 判斷條件2 THEN 輸出結果
WHEN 判斷條件3 THEN 輸出結果
ELSE 輸出結果
END
或
CASE
WHEN 表頭名 in ("值1","值2","值3") THEN 輸出結果
ELSE 輸出結果
END# 如果表頭名等于某個值，則返回對應位置then后面的值并結束判斷，
# 如果與所有值都不相等，則返回else后面的結果并結束判斷
示例：
mysql> select * from tarena.departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 財務部 |
| 3 | 運維部 |
| 4 | 開發部 |
| 5 | 測試部 |
| 6 | 市場部 |
| 7 | 銷售部 |
| 8 | 法務部 |
+---------+-----------+
8 rows in set (0.03 sec)# 第一種方法
mysql> select dept_id,dept_name,-> case dept_name-> when '運維部' then '技術部門'-> when '開發部' then '技術部門'-> when '測試部' then '技術部門'-> else '非技術部門'-> end as 部門類型 from tarena.departments;
+---------+-----------+-----------------+
| dept_id | dept_name | 部門類型 |
+---------+-----------+-----------------+
| 1 | 人事部 | 非技術部門 |
| 2 | 財務部 | 非技術部門 |
| 3 | 運維部 | 技術部門 |
| 4 | 開發部 | 技術部門 |
| 5 | 測試部 | 技術部門 |
| 6 | 市場部 | 非技術部門 |
| 7 | 銷售部 | 非技術部門 |
| 8 | 法務部 | 非技術部門 |
+---------+-----------+-----------------+
8 rows in set (0.00 sec)# 第二種方法
mysql> select dept_id,dept_name,-> case-> when dept_name="運維部" then "技術部"-> when dept_name="開發部" then "技術部"-> when dept_name="測試部" then "技術部"-> else "非技術部"-> end as 部門類型 from tarena.departments;
+---------+-----------+--------------+
| dept_id | dept_name | 部門類型 |
+---------+-----------+--------------+
| 1 | 人事部 | 非技術部 |
| 2 | 財務部 | 非技術部 |
| 3 | 運維部 | 技術部 |
| 4 | 開發部 | 技術部 |
| 5 | 測試部 | 技術部 |
| 6 | 市場部 | 非技術部 |
| 7 | 銷售部 | 非技術部 |
| 8 | 法務部 | 非技術部 |
+---------+-----------+--------------+
8 rows in set (0.00 sec)# 第三種方法
mysql> select dept_id,dept_name,-> case-> when dept_name in ('運維部','開發部','測試部') then '技術部門'-> else '非技術部門'-> end as 部門類型 from tarena.departments;
+---------+-----------+-----------------+
| dept_id | dept_name | 部門類型 |
+---------+-----------+-----------------+
| 1 | 人事部 | 非技術部門 |
| 2 | 財務部 | 非技術部門 |
| 3 | 運維部 | 技術部門 |
| 4 | 開發部 | 技術部門 |
| 5 | 測試部 | 技術部門 |
| 6 | 市場部 | 非技術部門 |
| 7 | 銷售部 | 非技術部門 |
| 8 | 法務部 | 非技術部門 |
+---------+-----------+-----------------+
8 rows in set (0.00 sec)
4. select 查詢結果處理
4.1 SQL語句執行順序
- FROM子句:確定數據來源，包括JOIN的表
- ON:執行JOIN條件
- JOIN:如果有JOIN，根據JOIN類型(如INNER、LEFT)，將相關行組合起來
- WHERE子句:過濾記錄
- GROUP BY子句:根據指定的列分組記錄，通常與聚合函數一起使用。
- 聚合函數:分組后對分組外其他字段進行聚合處理
- HAVING子句:過濾分組
- SELECT子句:選取特定的列
- DISTINCT子句:去除重復數據
- ORDERBY子句:最后對結果進行排序
- LIMIT子句:限制輸出行數
4.2 group by 分組
語法：select 表頭名，...... from 庫名.表名 where 條件 group by 表頭名；
注意事項：在使用 GROUP BY 時，SELECT 列表中的列要么是聚合函數的結果，要么必須出現在 GROUP BY 子句中。否則，MySQL 會報錯（在嚴格模式下）。
mysql> select shell,count(name)-> from tarena.user-> where shell is not null-> group by shell;
+----------------+-------------+
| shell | count(name) |
+----------------+-------------+
| /bin/bash | 2 |
| /sbin/nologin | 20 |
| /bin/sync | 1 |
| /sbin/shutdown | 1 |
| /sbin/halt | 1 |
| /bin/false | 1 |
+----------------+-------------+
6 rows in set (0.00 sec)
4.3 having 過濾
語法：select 表頭名 from 庫.表 where 篩選條件 having 篩選條件;
關于where與having區別:
-
對于表中真實的字段篩選用where
-
where 子句用于在分組之前篩選數據
-
having 子句用于在分組之后篩選分組結果
-
group by 后邊只能用 having
mysql> select dept_id,count(name)-> from tarena.employees-> group by dept_id;
+---------+-------------+
| dept_id | count(name) |
+---------+-------------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
+---------+-------------+
8 rows in set (0.00 sec)# 找出部門總人數少于10人的，并把總人數降序顯示
mysql> select dept_id 部門編號,count(name) 總人數-> from tarena.employees-> group by 部門編號-> having 總人數 < 10-> order by 總人數 desc;
+--------------+-----------+
| 部門編號 | 總人數 |
+--------------+-----------+
| 6 | 9 |
| 1 | 8 |
| 3 | 6 |
| 2 | 5 |
| 8 | 3 |
+--------------+-----------+
5 rows in set (0.00 sec)
4.4 order by 排序
語法：select 表頭名 from 庫.表 where 篩選條件 order by 表頭名 asc|desc
- asc：升序排序（默認）
- desc：降序排序
mysql> select name,uid,shell -> from tarena.user-> where id between 8 and 15-> order by uid desc;
+-----------------+------+---------------+
| name | uid | shell |
+-----------------+------+---------------+
| systemd-network | 192 | /sbin/nologin |
| nobody | 99 | /sbin/nologin |
| dbus | 81 | /sbin/nologin |
| ftp | 14 | /sbin/nologin |
| games | 12 | /sbin/nologin |
| operator | 11 | /sbin/nologin |
| mail | 8 | /sbin/nologin |
| halt | 7 | /sbin/halt |
+-----------------+------+---------------+
8 rows in set (0.00 sec)
示例：
mysql> select * from tarena.salary limit 5;
+----+------------+-------------+-------+-------+
| id | date | employee_id | basic | bonus |
+----+------------+-------------+-------+-------+
| 2 | 2015-01-10 | 2 | 17000 | 10000 |
| 3 | 2015-01-10 | 3 | 8000 | 2000 |
| 4 | 2015-01-10 | 4 | 14000 | 9000 |
| 6 | 2015-01-10 | 6 | 14000 | 10000 |
| 7 | 2015-01-10 | 7 | 19000 | 10000 |
+----+------------+-------------+-------+-------+
5 rows in set (0.00 sec)mysql> select employee_id 員工編號,sum(basic+bonus) 年薪-> from tarena.salary-> where year(date)=2018-> group by 員工編號-> having 年薪 > 350000-> order by 年薪 desc;
+--------------+--------+
| 員工編號 | 年薪 |
+--------------+--------+
| 31 | 374923 |
| 117 | 374923 |
| 37 | 362981 |
| 68 | 360923 |
| 48 | 359923 |
| 61 | 357981 |
| 25 | 353027 |
+--------------+--------+
7 rows in set (0.01 sec)
4.5 limit 分頁
作用：限制查詢結果顯示行數（默認顯示全部的查詢結果）
# SELECT語句 LIMIT 數字; //顯示查詢結果前多少條記錄
# SELECT語句 LIMIT 數字1,數字2; //顯示指定范圍內的查詢記錄
# 數字1 表示起始行 （0表示第1行） 數字2表示總行數
mysql> select id,name from tarena.user limit 5;
+----+--------+
| id | name |
+----+--------+
| 1 | root |
| 2 | bin |
| 3 | daemon |
| 4 | adm |
| 5 | lp |
+----+--------+
5 rows in set (0.00 sec)mysql> select id,name from tarena.user limit 0,2;
+----+------+
| id | name |
+----+------+
| 1 | root |
| 2 | bin |
+----+------+
2 rows in set (0.00 sec)mysql> select id,name from tarena.user limit 1,2;
+----+--------+
| id | name |
+----+--------+
| 2 | bin |
| 3 | daemon |
+----+--------+
2 rows in set (0.00 sec)
5. select 連接查詢
5.1 連接查詢概述
連接查詢也叫多表查詢，常用于查詢表頭來自于多張表，通過不同連接方式把多張表重新組成一張新的臨時表，再對臨時表做數據處理
- 內連接（INNER JOIN）：只返回匹配的記錄。
- 左連接（LEFT JOIN）：返回左表的所有記錄，即使右表中沒有匹配的記錄。
- 右連接（RIGHT JOIN）：返回右表的所有記錄，即使左表中沒有匹配的記錄。
- 全連接（FULL JOIN）：返回兩個表的所有記錄，無論是否匹配（MySQL不支持全連接，但可以通過
UNION實現）
[!CAUTION]
在實際生產環境中，通常建議連表查詢的表數量不超過5張。例如，阿里巴巴的數據庫規范中明確禁止超過3張表的
JOIN操作。這種限制主要是基于以下考慮：
- 性能問題：多表連接會顯著增加查詢的復雜性，導致查詢效率下降。每增加一個表，查詢的笛卡爾積會呈指數級增長。
- 資源消耗：多表連接會占用更多的內存和CPU資源，尤其是當
join_buffer_size設置不合理時，可能導致服務器內存溢出。- 可維護性：過多的表連接會使SQL語句難以維護和優化。
下面的示例主要圍繞著如下的表來操作：
5.2 內連接
特點
-
匹配行：內連接只返回兩個表中連接條件相匹配的行。
-
性能：通常比外連接（LEFT JOIN 或 RIGHT JOIN）更高效，因為它只需要處理匹配的行。
5.2.1 等值連接
- 使用相等判斷做連接條件，表里必須有存儲相同數據的表頭，等值連接是最常見的。
select 表頭名列表 from 表1 as 別名 inner join 表2 as 別名 on 連接條件 [where篩選條件] | [group by 分組] | [having分組后篩選] | [order by 排序列表]
# 給表名定義別名后，必須使用別名表示表名
# 查詢工號小于6的員工的所有信息及所在的部門名稱
mysql> select e.* , d.dept_name from employees e -> inner join departments d on e.dept_id=d.dept_id-> where e.employee_id < 6;
+-------------+-----------+------------+------------+----------------------+--------------+---------+-----------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id | dept_name |
+-------------+-----------+------------+------------+----------------------+--------------+---------+-----------+
| 1 | 梁偉 | 2018-06-21 | 1971-08-19 | liangwei@tedu.cn | 13591491431 | 1 | 人事部 |
| 2 | 郭巖 | 2010-03-21 | 1974-05-13 | guoyan@tedu.cn | 13845285867 | 1 | 人事部 |
| 3 | 李玉英 | 2012-01-19 | 1974-01-25 | liyuying@tedu.cn | 15628557234 | 1 | 人事部 |
| 4 | 張健 | 2008-09-17 | 1972-06-07 | zhangjian@tarena.com | 13835990213 | 1 | 人事部 |
| 5 | 鄭靜 | 2018-02-03 | 1997-02-14 | zhengjing@tedu.cn | 14508936730 | 1 | 人事部 |
+-------------+-----------+------------+------------+----------------------+--------------+---------+-----------+
5 rows in set (0.00 sec)# 查詢2018年總工資大于35萬的員工名字，按2018年總工資降序排列
mysql> select e.name 姓名,year(date) 年份,sum(basic+bonus) 年薪 -> from employees e inner join salary s -> on e.employee_id=s.employee_id where year(date)=2018-> group by e.name,year(date) -> having 年薪>350000 order by 年薪 desc;
+-----------+--------+--------+
| 姓名 | 年份 | 年薪 |
+-----------+--------+--------+
| 劉海燕 | 2018 | 374923 |
| 和林 | 2018 | 374923 |
| 朱淑蘭 | 2018 | 362981 |
| 柴冬梅 | 2018 | 360923 |
| 范秀英 | 2018 | 359923 |
| 沈秀梅 | 2018 | 357981 |
| 邵佳 | 2018 | 353027 |
+-----------+--------+--------+
7 rows in set (0.01 sec)
5.2.2 非等直連接
- 不使用相等判斷做連接條件，適合表里沒有存儲相同數據的表頭時使用
select 表頭名列表 from 表1 as 別名 inner join 表2 as 別名 on 連接條件 [where篩選條件] | [group by 分組] | [having分組后篩選] | [order by 排序列表]
# 查詢工資等級表
mysql> select * from wage_grade;
+------+-------+-------+--------+
| id | grade | low | high |
+------+-------+-------+--------+
| 1 | A | 5000 | 8000 |
| 2 | B | 8001 | 10000 |
| 3 | C | 10001 | 15000 |
| 4 | D | 15001 | 20000 |
| 5 | E | 20001 | 100000 |
+------+-------+-------+--------+
5 rows in set (0.00 sec)# 查詢2018年12月員工基本工資級別
mysql> select s.employee_id, s.date, s.basic, g.grade-> from salary as s inner join wage_grade as g -> on s.basic between g.low and g.high-> where year(s.date)=2018 and month(s.date)=12-> having s.employee_id <10;
+-------------+------------+-------+-------+
| employee_id | date | basic | grade |
+-------------+------------+-------+-------+
| 1 | 2018-12-10 | 17016 | D |
| 2 | 2018-12-10 | 20662 | E |
| 3 | 2018-12-10 | 9724 | B |
| 4 | 2018-12-10 | 17016 | D |
| 5 | 2018-12-10 | 17016 | D |
| 6 | 2018-12-10 | 17016 | D |
| 7 | 2018-12-10 | 23093 | E |
| 8 | 2018-12-10 | 23093 | E |
| 9 | 2018-12-10 | 13369 | C |
+-------------+------------+-------+-------+
9 rows in set (0.00 sec)# 查詢2018年12月員工各基本工資級別的人數
mysql> select grade 工資級別,count(s.employee_id) 總人數 -> from salary s inner join wage_grade g -> on s.basic between g.low and g.high-> where year(date)=2018 and month(date)=12-> group by 工資級別;
+--------------+-----------+
| 工資級別 | 總人數 |
+--------------+-----------+
| D | 32 |
| E | 33 |
| B | 12 |
| C | 30 |
| A | 13 |
+--------------+-----------+
5 rows in set (0.00 sec)3連表連接示例：
# 查詢2018年12月員工基本工資級別，員工需要顯示姓名
mysql> select s.date 日期,e.name 姓名, basic 基本工資,grade 工資級別 -> from salary s inner join wage_grade g -> on s.basic between g.low and g.high-> inner join employees e on e.employee_id=s.employee_id -> where year(date)=2018 and month(date)=12-> limit 8;
+------------+-----------+--------------+--------------+
| 日期 | 姓名 | 基本工資 | 工資級別 |
+------------+-----------+--------------+--------------+
| 2018-12-10 | 梁偉 | 17016 | D |
| 2018-12-10 | 郭巖 | 20662 | E |
| 2018-12-10 | 李玉英 | 9724 | B |
| 2018-12-10 | 張健 | 17016 | D |
| 2018-12-10 | 鄭靜 | 17016 | D |
| 2018-12-10 | 牛建軍 | 17016 | D |
| 2018-12-10 | 劉斌 | 23093 | E |
| 2018-12-10 | 汪云 | 23093 | E |
+------------+-----------+--------------+--------------+
8 rows in set (0.00 sec)
4連表示例：
[生產環境下禁止超過3張表JOIN操作,這里只是舉個示例](###5.1 連接查詢概述)
# 查詢2018年12月發薪日期時的員工的姓名、部門、基本工資、工資級別
mysql> select e.name 姓名,dept_name 部門,-> s.date 發薪日期,s.basic 基本工資,-> g.grade 工資級別 from salary as s -> inner join employees e-> on s.employee_id=e.employee_id-> inner join departments d-> on e.dept_id=d.dept_id-> inner join wage_grade as g -> on s.basic between g.low and g.high-> where year(s.date)=2018 and month(s.date)=12-> and e.employee_id < 10;
+-----------+-----------+--------------+--------------+--------------+
| 姓名 | 部門 | 發薪日期 | 基本工資 | 工資級別 |
+-----------+-----------+--------------+--------------+--------------+
| 梁偉 | 人事部 | 2018-12-10 | 17016 | D |
| 郭巖 | 人事部 | 2018-12-10 | 20662 | E |
| 李玉英 | 人事部 | 2018-12-10 | 9724 | B |
| 張健 | 人事部 | 2018-12-10 | 17016 | D |
| 鄭靜 | 人事部 | 2018-12-10 | 17016 | D |
| 牛建軍 | 人事部 | 2018-12-10 | 17016 | D |
| 劉斌 | 人事部 | 2018-12-10 | 23093 | E |
| 汪云 | 人事部 | 2018-12-10 | 23093 | E |
| 張建平 | 財務部 | 2018-12-10 | 13369 | C |
+-----------+-----------+--------------+--------------+--------------+
9 rows in set (0.00 sec)
5.3 外連接
分類
-
左外連接 left join
-
右外連接 right join
5.3.1 左外連接
select tb1.字段......,tb2.字段 from table1 as tb1 left join table2 as tb2 on tb1.字段=tb2.字段;
查詢輸出結果：返回左表的所有記錄以及右表中匹配的記錄，如果右表中沒有匹配的記錄，則結果集中對應的字段為NULL。
mysql> select * from tarena.departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 財務部 |
| 3 | 運維部 |
| 4 | 開發部 |
| 5 | 測試部 |
| 6 | 市場部 |
| 7 | 銷售部 |
| 8 | 法務部 |
| 9 | 小賣部 |
| 10 | 行政部 |
| 11 | 公關部 |
+---------+-----------+
11 rows in set (0.00 sec)mysql> select d.dept_id,d.dept_name,e.name-> from departments d-> left join employees e -> on d.dept_id=e.dept_id-> where e.name is null;
+---------+-----------+------+
| dept_id | dept_name | name |
+---------+-----------+------+
| 9 | 小賣部 | NULL |
| 10 | 行政部 | NULL |
| 11 | 公關部 | NULL |
+---------+-----------+------+
3 rows in set (0.01 sec)
5.3.2 右外連接
select tb1.字段......,tb2.字段 from table1 as tb1 right join table2 as tb2 on tb1.字段=tb2.字段;
查詢輸出結果：返回右表的所有記錄以及左表中匹配的記錄，如果左表中沒有匹配的記錄，則結果集中對應的字段為NULL。
mysql> select * from tarena.employees-> where dept_id is null;
+-------------+------+-----------+------------+-------+--------------+---------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
+-------------+------+-----------+------------+-------+--------------+---------+
| 134 | bob | NULL | NULL | NULL | NULL | NULL |
| 135 | tom | NULL | NULL | NULL | NULL | NULL |
| 136 | lily | NULL | NULL | NULL | NULL | NULL |
+-------------+------+-----------+------------+-------+--------------+---------+
3 rows in set (0.00 sec)mysql> select d.dept_id,d.dept_name,e.name-> from departments d right join employees e -> on d.dept_id=e.dept_id-> where d.dept_name is null;
+---------+-----------+------+
| dept_id | dept_name | name |
+---------+-----------+------+
| NULL | NULL | bob |
| NULL | NULL | tom |
| NULL | NULL | lily |
+---------+-----------+------+
3 rows in set (0.00 sec)
5.3.3 全外連接
特點：
- 返回所有記錄：全外連接會返回左表和右表的所有記錄，即使它們之間沒有匹配的行。
- 匹配與不匹配的記錄：對于匹配的記錄，會顯示兩個表中的字段值；對于不匹配的記錄，不匹配的字段會顯示為
NULL。 - 用途：全外連接常用于需要同時查看兩個表中所有數據的場景，尤其是當不確定兩個表之間是否存在匹配關系時。
語法格式：
-
(select語句) union (select語句); //去重
-
(select語句) union all (select語句); //不去重
# union 去掉查詢結果中重復的行
mysql> (select employee_id,name from employees where employee_id <=5) union-> (select employee_id,name from employees where employee_id <=6);
+-------------+-----------+
| employee_id | name |
+-------------+-----------+
| 1 | 梁偉 |
| 2 | 郭巖 |
| 3 | 李玉英 |
| 4 | 張健 |
| 5 | 鄭靜 |
| 6 | 牛建軍 |
+-------------+-----------+
6 rows in set (0.01 sec)#union all 不去重顯示查詢結果
mysql> (select employee_id,name from employees where employee_id <=5) union all-> (select employee_id,name from employees where employee_id <=6);
+-------------+-----------+
| employee_id | name |
+-------------+-----------+
| 1 | 梁偉 |
| 2 | 郭巖 |
| 3 | 李玉英 |
| 4 | 張健 |
| 5 | 鄭靜 |
| 1 | 梁偉 |
| 2 | 郭巖 |
| 3 | 李玉英 |
| 4 | 張健 |
| 5 | 鄭靜 |
| 6 | 牛建軍 |
+-------------+-----------+
11 rows in set (0.00 sec)# 使用union將左右連接兩個查詢結果合并
mysql> select d.dept_id,d.dept_name,e.name-> from departments d -> left join employees e -> on d.dept_id=e.dept_id-> where e.name is null-> union -> select d.dept_id,d.dept_name,e.name-> from departments d right join employees e -> on d.dept_id=e.dept_id-> where d.dept_name is null;
+---------+-----------+------+
| dept_id | dept_name | name |
+---------+-----------+------+
| 9 | 小賣部 | NULL |
| 10 | 行政部 | NULL |
| 11 | 公關部 | NULL |
| NULL | NULL | bob |
| NULL | NULL | tom |
| NULL | NULL | lily |
+---------+-----------+------+
6 rows in set (0.00 sec)
6. 嵌套查詢
是指在一個完整的查詢語句之中，包含若干個不同功能的小查詢從而一起完成復雜查詢的一種編寫形式。
包含的查詢放在（）里
包含的查詢出現的位置
-
select之后
-
from之后
-
where
-
having之后
6.1 where 后
# 查看人事部的部門id
mysql> select dept_id from departments where dept_name='人事部';
+---------+
| dept_id |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)# 查找employees表里 人事部的員工id
mysql> select employee_id from employees -> where dept_id=(select dept_id from -> departments where dept_name='人事部');
+-------------+
| employee_id |
+-------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+-------------+
8 rows in set (0.00 sec)# 查找2018年12月人事部的員工的工資情況
mysql> select * from salary -> where year(date)=2018 and month(date)=12-> and employee_id in (-> select employee_id from employees-> where dept_id=(-> select dept_id from departments-> where dept_name='人事部'-> )-> );
+------+------------+-------------+-------+-------+
| id | date | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+
| 6252 | 2018-12-10 | 1 | 17016 | 7000 |
| 6253 | 2018-12-10 | 2 | 20662 | 9000 |
| 6254 | 2018-12-10 | 3 | 9724 | 8000 |
| 6255 | 2018-12-10 | 4 | 17016 | 2000 |
| 6256 | 2018-12-10 | 5 | 17016 | 3000 |
| 6257 | 2018-12-10 | 6 | 17016 | 1000 |
| 6258 | 2018-12-10 | 7 | 23093 | 4000 |
| 6259 | 2018-12-10 | 8 | 23093 | 2000 |
+------+------------+-------------+-------+-------+
8 rows in set (0.00 sec)
6.2 having 后
# 統計開發部員工總人數
mysql> select count(name) from employees where dept_id = (select -> dept_id from departments where dept_name="開發部");
+-------------+
| count(name) |
+-------------+
| 55 |
+-------------+
1 row in set (0.00 sec)# 統計每個部門總人數
mysql> select dept_id , count(name) -> from employees group by dept_id;
+---------+-------------+
| dept_id | count(name) |
+---------+-------------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
+---------+-------------+
8 rows in set (0.00 sec)# 輸出總人數比開發部總人數少的部門名及總人數
mysql> select e.dept_id ,d.dept_name,count(name) total -> from employees e -> inner join departments d on e.dept_id=d.dept_id-> group by e.dept_id having total < (-> select count(name) from employees where dept_id=(-> select dept_id from departments -> where dept_name='開發部')-> );
+---------+-----------+-------+
| dept_id | dept_name | total |
+---------+-----------+-------+
| 1 | 人事部 | 8 |
| 2 | 財務部 | 5 |
| 3 | 運維部 | 6 |
| 5 | 測試部 | 12 |
| 6 | 市場部 | 9 |
| 7 | 銷售部 | 35 |
| 8 | 法務部 | 3 |
+---------+-----------+-------+
7 rows in set (0.00 sec)
6.3 from 后
# 在from之后做嵌套，很少用
# 查詢3號部門及其部門內員工的編號，名字和生日
mysql> select dept_id,dept_name,name,birth_date from -> (select d.dept_name,e.* from departments d-> inner join employees e-> on d.dept_id=e.dept_id) as tmp #必須要起別名，否則報錯-> where dept_id=3;
+---------+-----------+-----------+------------+
| dept_id | dept_name | name | birth_date |
+---------+-----------+-----------+------------+
| 3 | 運維部 | 廖娜 | 1982-06-22 |
| 3 | 運維部 | 竇紅梅 | 1971-09-09 |
| 3 | 運維部 | 聶想 | 1999-06-05 |
| 3 | 運維部 | 陳陽 | 1991-04-10 |
| 3 | 運維部 | 戴璐 | 1975-05-16 |
| 3 | 運維部 | 陳斌 | 2000-01-22 |
+---------+-----------+-----------+------------+
6 rows in set (0.00 sec)
6.4 select 后
# 查詢每個部門的人數
mysql> select d.*,-> (select count(name) from employees e -> where e.dept_id=d.dept_id) as 部門人數-> from departments d;
+---------+-----------+--------------+
| dept_id | dept_name | 部門人數 |
+---------+-----------+--------------+
| 1 | 人事部 | 8 |
| 2 | 財務部 | 5 |
| 3 | 運維部 | 6 |
| 4 | 開發部 | 55 |
| 5 | 測試部 | 12 |
| 6 | 市場部 | 9 |
| 7 | 銷售部 | 35 |
| 8 | 法務部 | 3 |
| 9 | 小賣部 | 0 |
| 10 | 行政部 | 0 |
| 11 | 公關部 | 0 |
+---------+-----------+--------------+
11 rows in set (0.00 sec)
7. 數據類型
7.1 字符類型
一般用來存儲姓名，收貨地址，工作單位，家庭住址
| 類型 | 名稱 | 說明 |
|---|---|---|
| char(字符個數) | 定長類型 | 最多255個字符 |
| varchar(字符個數) | 變長類型 | 最多65532個字符 |
- char類型說明：不能指定字符個數時在左右邊用空格補全字符個數，超出時無法寫入數據。
- varchar類型說明：按數據實際大小分配存儲空間，字符個數超出時無法寫入數據。
7.2 數型類型
僅存儲數值的整數部分
| 類型 | 名稱 | 有符號范圍 | 無符號范圍 |
|---|---|---|---|
| tinyint | 微小整數 | -128~127 | 0~255 |
| smallint | 小整數 | -32768~32767 | 0~65535 |
| mediumint | 中整數 | -223~223-1 | 0~224-1 |
| int | 大整數 | -231~231-1 | 0~232-1 |
| bingint | 極大整數 | -263~263-1 | 0~264-1 |
| unsigned | 使用無符號存儲范圍 |
7.3 浮點類型
存儲有小數點的數
| 類型 | 名稱 | 存儲空間 |
|---|---|---|
| float | 單精度 | 4字節 |
| double | 雙精度 | 8字節 |
7.4 枚舉類型
- enum類型 單選
| 類型 | 說明 |
|---|---|
| enum(值列表) | 字段值僅能在范圍內選擇1個值 |
- set類型 多選
| 類型 | 說明 |
|---|---|
| set(值列表) | 字段值能在范圍內選擇1個或多個值 |
mysql> create table studb.t8(-> 姓名 char(10),-> 性別 enum("男","女","保密"), -> 愛好 set("帥哥","金錢","吃","睡") -> );
Query OK, 0 rows affected (0.29 sec)mysql> desc studb.t8 ;
+--------+------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| 性別 | enum('男','女','保密') | YES | | NULL | |
| 愛好 | set('帥哥','金錢','吃','睡') | YES | | NULL | |
+--------+------------------------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> insert into studb.t8 values ("喬碧羅","女","帥哥,吃");
Query OK, 1 row affected (0.09 sec)
mysql> select * from studb.t8;
+--------+--------+------------+
| 姓名 | 性別 | 愛好 |
+--------+--------+------------+
| 喬碧羅 | 女 | 帥哥,吃 |
+--------+--------+------------+
1 row in set (0.00 sec)
7.5 日期類型
存儲如生日，注冊時間，出生年份，入職日期
| 類型 | 名稱 | 范圍 | 賦值格式 |
|---|---|---|---|
| year | 年 | 1901~2155 | YYYY |
| date | 日期 | 0001-01-01~9999-12-31 | YYYYMMDD |
| time | 時間 | 01:00:00-23:59:59 | HHMMSS |
| datetime | 日期時間 | 1000-01-01 00:00:00~9999-12-31 23:59:59 | YYYYMMDDHHMMSS |
| timestamp | 1970-01-01 00:00:00~2038-01-19 00:00:00 |
mysql> create table studb.t6(-> 姓名 char(10),-> 生日 date,-> 出生年份 year,-> 家庭聚會 datetime ,-> 聚會地點 varchar(15),-> 上班時間 time);
Query OK, 0 rows affected (0.65 sec)mysql> desc studb.t6;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| 生日 | date | YES | | NULL | |
| 出生年份 | year | YES | | NULL | |
| 家庭聚會 | datetime | YES | | NULL | |
| 聚會地點 | varchar(15) | YES | | NULL | |
| 上班時間 | time | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> insert into studb.t6 values-> ("坤坤",19980228,1998,20250412090000,"鄭州金水",100000);
Query OK, 1 row affected (0.11 sec)mysql> select * from studb.t6;
+--------+------------+--------------+---------------------+--------------+--------------+
| 姓名 | 生日 | 出生年份 | 家庭聚會 | 聚會地點 | 上班時間 |
+--------+------------+--------------+---------------------+--------------+--------------+
| 坤坤 | 1998-02-28 | 1998 | 2025-04-12 09:00:00 | 鄭州金水 | 10:00:00 |
+--------+------------+--------------+---------------------+--------------+--------------+
1 row in set (0.00 sec)
8. 庫操作/表操作
8.1 建刪庫
建庫
# if not exists 避免建庫重名報錯，也可以不加
mysql> create database if not exists 數據庫名
刪庫
# if exists 避免刪除的庫名不存在而報錯,也可以不加
mysql> drop database if exists 數據庫名
8.2 建刪表
建表語法
create table 庫名.表名
(
表頭名1 數據類型,
表頭名2 數據類型,
表頭名3 數據類型,
......
);
建表示例：
# 注意：表必須存放在庫里
mysql> create database studb; //建庫
Query OK, 1 row affected (0.11 sec)
mysql> create table studb.stu( //建表-> name char(10), -> class char(9), -> gender char(4), -> age int -> );
Query OK, 0 rows affected (1.17 sec)# 查看表頭
mysql> desc studb.stu;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| class | char(9) | YES | | NULL | |
| gender | char(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
刪除表
mysql> drop table studb.stu;
# 再刪除庫
mysql> drop database studb;
8.3 alter 修改表
| 操作命令 | 說明 |
|---|---|
| add | 添加新字段，一起添加多個字段使用，以,分隔 add命令（first是把表頭添加到首位，after是添加在指定表頭名的下方） |
| modify | 修改字段類型，也可以修改字段的位置 |
| change | 修改字段名，也可以同時修改字段類型 |
| rename | 修改表名 |
| drop | 刪除字段，刪除多個字段使用以,分隔drop命令 |
# 查看表頭
mysql> desc studb.stu;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| class | char(9) | YES | | NULL | |
| gender | char(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)# 修改表名
mysql> alter table studb.stu rename studb.stuinfo;
Query OK, 0 rows affected (0.28 sec)mysql> alter table studb.stuinfo-> drop age, # 刪除age字段-> add email char(30), # 添加emali字段（默認添加最下面）-> add school char(10) after name, # 在name字段下面添加school字段-> add number char (10) first; # 在首位添加number字段
Query OK, 0 rows affected (1.31 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc studb.stuinfo;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(10) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| school | char(10) | YES | | NULL | |
| class | char(9) | YES | | NULL | |
| gender | char(4) | YES | | NULL | |
| email | char(30) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> alter table studb.stuinfo -> modify email varchar(50), # 修改emali字段的數據類型-> change class 班級 char(8); # 修改class字段的字段名和數據類型
Query OK, 0 rows affected (1.17 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc studb.stuinfo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | char(9) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| school | char(10) | YES | | NULL | |
| 班級 | char(8) | YES | | NULL | |
| gender | char(4) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)# 將gender字段放在number字段的下面，并修改數據類型
mysql> alter table studb.stuinfo-> modify gender varchar (4) after number;
Query OK, 0 rows affected (1.72 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc studb.stuinfo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | char(9) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| school | char(10) | YES | | NULL | |
| 班級 | char(8) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
8.4 復制表
- 復制表結構及數據
create table 庫.表 select 列名 from 庫.表 [where 條件]
[!CAUTION]
原表的key 不會復制給新表，新表數據與select語句決定
- 僅復制表結構
create table 庫.表 like 庫.表
[!CAUTION]
僅僅復制表頭，源表的key 會被復制
mysql> create table studb.salary select * from tarena.salary;
Query OK, 8055 rows affected (3.51 sec)
Records: 8055 Duplicates: 0 Warnings: 0# 數據復制了，但是key不會被復制
mysql> desc studb.salary;
+-------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+-------+
| id | int | NO | | 0 | |
| date | date | YES | | NULL | |
| employee_id | int | YES | | NULL | |
| basic | int | YES | | NULL | |
| bonus | int | YES | | NULL | |
+-------------+------+------+-----+---------+-------+
5 rows in set (0.00 sec)# 僅僅復制表頭，源表的key會被復制
mysql> create table studb.salary2 like tarena.salary;
Query OK, 0 rows affected (0.56 sec)mysql> desc studb.salary2;
+-------------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| date | date | YES | | NULL | |
| employee_id | int | YES | MUL | NULL | |
| basic | int | YES | | NULL | |
| bonus | int | YES | | NULL | |
+-------------+------+------+-----+---------+----------------+
5 rows in set (0.00 sec)# 數據沒被復制
mysql> select count(*) from studb.salary2;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
9. 數據導入/導出
9.1 數據導入
操作步驟：
拷貝文件到檢索目錄 ---> 建表 --->> 導入數據
詳情請看博客大牛
# 查看默認檢索目錄
mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
mysql> exit[root@mysql50 ~]# ls -ld /var/lib/mysql-files/
drwxr-x--- 2 mysql mysql 6 Sep 22 2021 /var/lib/mysql-files/[root@mysql50 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
secure_file_priv=/myload 添加此行
:wq[root@mysql50 ~]# mkdir /myload
[root@mysql50 ~]# chown mysql /myload
# 關閉selinux
[root@mysql50 ~]# setenforce 0 //禁用selinux
[root@mysql50 ~]# systemctl restart mysqld# 進入mysql查看檢索目錄
mysql> show variables like "secure_file_priv";
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| secure_file_priv | /myload/ |
+------------------+----------+
1 row in set (0.01 sec)# 建表（ 根據導入的文件內容 創建表頭）,這里是拿/etc/passwd舉例
mysql> create table db1.user3(-> name varchar(30),-> password char(1),-> uid int , -> gid int , -> comment varchar(200),-> homedir varchar(50),-> shell varchar(30)-> );
Query OK, 0 rows affected (0.41 sec)# 將/etc/passwd拷貝到檢索目錄
mysql> system cp /etc/passwd /myload/
mysql> system ls /myload/ 查看文件
passwdmysql> load data infile '/檢索目錄名/文件名' into table 庫名.表名-> fields terminated by '分隔符'-> lines terminated by '\';
9.2 數據導出
語法格式1：
mysql> select * from 庫名.表名 into outfile '/檢索目錄名/文件名';
語法格式2：
mysql> select * from 庫名.表名-> where 條件 into outfile '/檢索目錄名/文件名'-> fields terminated by ':'-> lines terminated by '/n';
10. 約束分類
約束是一種限制，設置在表頭上，用來控制表頭的賦值，包括以下幾種：
-
NOT NULL ：非空，用于保證該字段的值不能為空。
-
DEFAULT：默認值，用于保證該字段有默認值。
-
UNIQUE：唯一索引，用于保證該字段的值具有唯一性，可以為空。
-
PRIMARY KEY：主鍵，用于保證該字段的值具有唯一性并且非空。
-
FOREIGN KEY：外鍵，用于限制兩個表的關系，用于保證該字段的值必須來自于主表的關聯列的值，在從表添加外鍵約束，用于引用主表中某些的值。
10.1 not null 非空
用于保證該字段的值不能為空
# 創建帶非空的字段的表
mysql> create table db1.t33(-> name char(10) not null,-> class char(7));
Query OK, 0 rows affected (0.65 sec)# name字段為空，報錯
mysql> insert into db1.t33 values(null,"linux");
ERROR 1048 (23000): Column 'name' cannot be null# name字段賦值，成功插入
mysql> insert into db1.t33 values("hehe","linux");
Query OK, 1 row affected (0.08 sec)mysql> select * from db1.t33;
+------+-------+
| name | class |
+------+-------+
| hehe | linux |
+------+-------+
1 row in set (0.01 sec)
10.2 default 默認值
用于保證該字段有默認值
mysql> create table db1.t31(-> name char(10) not null,-> class char(7) default "Go",-> likes set ("吃","喝","辣","撒") not null default "吃,喝");
Query OK, 0 rows affected (0.55 sec)mysql> desc db1.t31;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(10) | NO | | NULL | |
| class | char(7) | YES | | Go | |
| likes | set('吃','喝','辣','撒') | NO | | 吃,喝 | |
+-------+------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into db1.t31 values(null,null,null);
ERROR 1048 (23000): Column 'name' cannot be nullmysql> insert into db1.t31 values("hehe",null,null);
ERROR 1048 (23000): Column 'likes' cannot be nullmysql> insert into db1.t31(name) values("jim");
Query OK, 1 row affected (0.19 sec)mysql> insert into db1.t31 values("lucy","linux","撒");
Query OK, 1 row affected (0.24 sec)mysql> select * from db1.t31;
+------+-------+---------+
| name | class | likes |
+------+-------+---------+
| jim | Go | 吃,喝 |
| lucy | linux | 撒 |
+------+-------+---------+
2 rows in set (0.00 sec)
10.3 unique 唯一索引
用于保證該字段的值具有唯一性，可以為空
mysql> create table db1.t43 (-> 姓名 char(10),-> 護照 char(18) unique);
Query OK, 0 rows affected (1.26 sec)mysql> desc db1.t43;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| 護照 | char(18) | YES | UNI | NULL | |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)# 可以賦空值
mysql> insert into db1.t43 values("bob",null);
Query OK, 1 row affected (0.08 sec)mysql> insert into db1.t43 values("tom",888);
Query OK, 1 row affected (0.20 sec)# 不可以賦重復的值
mysql> insert into db1.t43 values("haha",888);
ERROR 1062 (23000): Duplicate entry '888' for key 't43.護照'mysql> insert into db1.t43 values("haha",666);
Query OK, 1 row affected (0.09 sec)mysql> select * from db1.t43;
+--------+--------+
| 姓名 | 護照 |
+--------+--------+
| bob | NULL |
| tom | 888 |
| haha | 666 |
+--------+--------+
3 rows in set (0.00 sec)10.4 primary key 主鍵
用于保證該字段的值具有唯一性并且非空
主鍵使用規則：
-
表頭值不允許重復，不允許賦NULL值
-
一個表中只能有一個primary key 表頭
-
多個表頭做主鍵，稱為復合主鍵，必須一起創建和刪除
-
主鍵標志PRI
-
主鍵通常與auto_increment連用
-
通常把表中唯一標識記錄的表頭設置為主鍵[行號表頭]
創建主鍵的格式
create table 庫.表 (
字段名 數據類型 primary key,
字段名 數據類型,
......);
或
create table 庫.表 (
字段名 數據類型,
字段名 數據類型,
......
primary key (字段名));
刪除主鍵命令格式 alter table 庫.表 drop primary key;
添加主鍵命令格式 alter table 庫.表 add primary key (字段名);
復合主鍵
-
多個字段一起做主鍵，相當于多個字段共享一個主鍵
-
復合主鍵的值不允許同時重復
創建復合主鍵語法格式：
create table 庫.表(
字段名 數據類型,
字段名 數據類型,
......
primary key(字段名1,字段名2)
);
刪除復合主鍵：alter table 庫名.表名 drop primary key;
添加復合主鍵：alter table 庫.表 add primary key (字段名1,字段名2);
與 auto_increment 連用
作用：自增長，通過表頭自加1計算結果賦值
-
通常與數據類型整型類型連用，如統計行號
-
想要讓表頭有自增長，表頭必須有主鍵設置才可以
-
當給自增長表頭賦值后，后面的自增長表頭會以最后一條記錄表頭的值+1
caret table 庫名.表名(
字段名 int primary key auto_increment,
字段名 數據類型,
字段名 數據類型,
......
);
10.5 foreign key 外鍵
用于限制兩個表的關系，用于保證該字段的值必須來自于主表的關聯列的值，在從表添加外鍵約束，用于引用主表中某些的值。
作用：插入記錄時，表頭值在另一個表的表頭值范圍內選擇
使用規則
-
表存儲引擎必須是innodb
-
字段類型要一致
-
被參照字段必須要是索引類型的一種(primary key)
創建外鍵語法格式：
create table 庫.表(
字段名 數據類型,
......
foreign key(字段名) #指定外鍵
references 庫.表(字段名) #指定參考的字段名
on update cascade #同步更新
on delete cascade #同步刪除
)engine=innodb; #指定存儲引擎
創建外鍵示例
# 創建員工表
mysql> create table db1.yg(-> yg_id int primary key auto_increment,-> name char(20));
Query OK, 0 rows affected (0.49 sec)# 創建工資表，給已有的員工發工資
mysql> create table db1.gz(-> gz_id int, pay float,-> foreign key(gz_id) references db1.yg(yg_id)-> on update cascade on delete cascade);
Query OK, 0 rows affected (1.02 sec)
刪除與添加
# 查看外鍵
mysql> show create table db1.gz \G
*************************** 1. row ***************************Table: gz
Create Table: CREATE TABLE `gz` (`gz_id` int(11) DEFAULT NULL,`pay` float DEFAULT NULL,KEY `gz_id` (`gz_id`),CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1# 刪除外鍵
mysql> alter table db1.gz drop FOREIGN KEY gz_ibfk_1;# 刪除后，查看不到外鍵
mysql> show create table db1.gz \G
*************************** 1. row ***************************Table: gz
Create Table: CREATE TABLE `gz` (`gz_id` int(11) DEFAULT NULL,`pay` float DEFAULT NULL,KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)# 添加外鍵
mysql> alter table db1.gz
add foreign key(gz_id) references db1.yg(yg_id)
on update cascade on delete cascade ;# 添加后就又有外鍵了
mysql> show create table db1.gz \G
*************************** 1. row ***************************Table: gz
Create Table: CREATE TABLE `gz` (`gz_id` int(11) DEFAULT NULL,`pay` float DEFAULT NULL,KEY `gz_id` (`gz_id`),CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
驗證外鍵功能
- 外鍵字段的值必須在參考表字段值范圍內
- 驗證同步更新（on update cascade)
- 驗證同步刪除（on delete cascade)
外鍵字段的值必須在參考表字段值范圍內
# 在員工表插入記錄
mysql> insert into db1.yg(name) values("haha"),("tom");mysql> select * from db1.yg;
+-------+------+
| yg_id | name |
+-------+------+
| 1 | haha |
| 2 | tom |
+-------+------+
2 rows in set (0.00 sec)# 工資表插入記錄
mysql> insert into db1.gz values(1,20000);
mysql> insert into db1.gz values(2,30000);mysql> select * from db1.gz;
+-------+-------+
| gz_id | pay |
+-------+-------+
| 1 | 20000 |
| 2 | 30000 |
+-------+-------+
2 rows in set (0.00 sec)# 沒有的3號員工 工資表插入記錄報錯
mysql> insert into db1.gz values(3,40000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)# 員工表插入編號3的員工
mysql> insert into db1.yg (name) values ("Lucy");
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | haha |
| 2 | tom |
| 3 | lufei |
+-------+-------+
3 rows in set (0.00 sec)# 這樣就執行成功了
mysql> insert into db1.gz values(3,40000);
驗證同步更新
# 更新員工表的id
mysql> update db1.yg set yg_id=9 where yg_id=3;
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | haha |
| 2 | tom |
| 9 | lufei |
+-------+-------+
3 rows in set (0.00 sec)# 查看gz表的id也變化了，這就是同步更新了
mysql> select * from db1.gz;
+-------+-------+
| gz_id | pay |
+-------+-------+
| 1 | 20000 |
| 2 | 30000 |
| 9 | 40000 |
+-------+-------+
3 rows in set (0.00 sec)
驗證同步刪除
mysql> delete from db1.yg where yg_id=2;
Query OK, 1 row affected (0.11 sec)# 查看工資表的id2也同時刪除了
mysql> select * from db1.gz;
+-------+-------+
| gz_id | pay |
+-------+-------+
| 1 | 20000 |
| 9 | 40000 |
+-------+-------+
2 rows in set (0.00 sec)
外鍵使用注意事項
# 被參考的表不能刪除
mysql> drop table db1.yg;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails# 給工資表的gz_id添加主鍵，以防重復
mysql> alter table db1.gz add primary key(gz_id); 添加主鍵
11. Mysql 索引
11.1 什么是索引 (index)
索引類似于書籍的目錄，它為數據庫中的數據提供了一種快速查找的方式。在沒有索引的情況下，數據庫需要掃描整個表來查找滿足條件的記錄，這種操作稱為全表掃描（Full Table Scan），效率非常低。而索引通過創建數據的有序結構，使得數據庫可以快速定位到目標數據，從而大大提高了查詢速度。
11.2 索引的原理
索引通過預先對數據進行排序和組織，使得數據庫在查詢時可以快速定位目標數據，從而避免了全表掃描。B+ 樹是 MySQL 中最常用的索引結構，它通過多級索引和有序性，大大減少了磁盤 I/O 操作，提高了查詢效率。索引雖然強大，但也需要合理使用，避免過多或不恰當的索引帶來的額外開銷。
11.3 索引分類
普通索引（Normal Index）
普通索引是最基本的索引類型，它沒有唯一性約束。可以為表中的一個或多個列創建普通索引。例如：
sql復制
CREATE INDEX idx_name ON table_name (column_name);
唯一索引（Unique Index）
唯一索引要求索引列的值必須是唯一的，但允許有空值。它不僅可以加快查詢速度，還可以防止數據重復。例如：
CREATE UNIQUE INDEX idx_unique_name ON table_name (column_name);
主鍵索引（Primary Key Index）
主鍵索引是一種特殊的唯一索引，它要求索引列的值必須唯一且非空。每個表只能有一個主鍵索引。主鍵索引通常用于唯一標識表中的每一行記錄。例如：
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
復合索引（Composite Index）
復合索引是為表中的多個列創建的索引。在查詢時，如果查詢條件中包含了復合索引的前導列，MySQL 可以利用該索引來加速查詢。例如：
CREATE INDEX idx_composite ON table_name (column1, column2);
全文索引（Full-Text Index）
全文索引用于對文本數據進行全文搜索，適用于 CHAR、VARCHAR 和 TEXT 類型的列。它可以幫助快速查找文本內容中的關鍵字。例如：
CREATE FULLTEXT INDEX idx_fulltext ON table_name (column_name);
11.4 索引優點
-
可以大大提高MySQL的檢索速度
-
索引大大減小了服務器需要掃描的數據量
-
索引可以幫助服務器避免排序和臨時表
11.5 索引缺點
-
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存索引文件。
-
建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創建了多種組合索引，索引文件的會膨脹很快。
-
如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。
-
對于非常小的表，大部分情況下簡單的全表掃描更高效。
11.6 使用規則
-
一個表中可以有多個index
-
任何數據類型的表頭都可以建索引
-
字段的值可以重復，可以賦NULL值
-
通常在where條件中的字段上配置index
-
index索引標志MUL
11.7 創建索引
creare table 庫.表(
字段名 數據類型,
字段名 數據類型,
......
index(字段名),
index(字段名),
);create index 索引名 on 庫.表(字段名); # 添加索引
drop index 索引名 on 庫.表; # 刪除索引
explain select 查詢語句; # 驗證查詢是否使用索引
mysql> create database home;
mysql> use home;
# 創建索引
mysql> create table t4(-> id char(6),-> name varchar(6),-> age int(3),-> gender enum("boy","girl") default "boy",-> index(id),index(name)-> );
Query OK, 0 rows affected, 1 warning (0.04 sec)mysql> desc home.t4;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | YES | MUL | NULL | |
| name | varchar(6) | YES | MUL | NULL | |
| age | int | YES | | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)# 查看索引詳細信息
mysql> show index from home.t4\G
*************************** 1. row ***************************Table: t4 # 表名Non_unique: 1Key_name: id # 索引名 （默認索引名和表頭名相同，刪除索引時，使用的索引名）Seq_in_index: 1Column_name: id # 表頭名Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREE # 索引類型Comment:
Index_comment: Visible: YESExpression: NULL
*************************** 2. row ***************************Table: t4Non_unique: 1Key_name: nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
2 rows in set (0.01 sec)# 刪除索引
mysql> drop index id on home.t4;
mysql> desc home.t4;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | YES | | NULL | |
| name | varchar(6) | YES | MUL | NULL | |
| age | int | YES | | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> show index from home.t4\G
*************************** 1. row ***************************Table: t4Non_unique: 1Key_name: nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
1 row in set (0.01 sec)# 已有表添加索引命令
mysql> create index nl on home.t4(age);
mysql> desc home.t4;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | YES | | NULL | |
| name | varchar(6) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show index from home.t4\G
*************************** 1. row ***************************Table: t4Non_unique: 1Key_name: nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
*************************** 2. row ***************************Table: t4Non_unique: 1Key_name: nl # 設置的索引名Seq_in_index: 1Column_name: age # 表頭名Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
2 rows in set (0.00 sec)
11.8 驗證索引
# 驗證是否使用索引
mysql> explain select * from tarena.user where name="sshd" \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: user # 表名partitions: NULLtype: ALL
possible_keys: NULLkey: NULL # 使用的索引名key_len: NULLref: NULLrows: 27 # 查找的總行數filtered: 10.00Extra: Using where # 額外說明
1 row in set, 1 warning (0.00 sec)# 查看表的總行數，查找sshd用做的是全表掃描
mysql> select count(*) from tarena.user;
+----------+
| count(*) |
+----------+
| 27 |
+----------+
1 row in set (0.01 sec)# 給name表頭設置索引
msyql> create index name on tarena.user(name);
# 驗證索引
mysql> explain select * from tarena.user where name="sshd" \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: user #表名partitions: NULLtype: ref
possible_keys: namekey: name #使用的索引名key_len: 21ref: constrows: 1 #查找的總行數filtered: 100.00Extra: NULL #額外說明
1 row in set, 1 warning (0.00 sec)
12. 用戶管理
12.1 創建用戶
用戶名：授權時自定義，要有標識性，存儲在mysql庫的user表里
客戶端地址
-
% //所有主機
-
192.168.88.0/24 //網段內的所有主機
-
192.168.88.52 //固定1臺主機
-
localhost //數據庫服務器本機
create user 用戶名@"客戶端地址" identified by "密碼";
#創建用戶如下
mysql> create user tom@'localhost' identified by '123';
Query OK, 0 rows affected (0.39 sec)
#修改用戶密碼如下
mysql> alter user tom@'localhost' identified by 'tarena';
Query OK, 0 rows affected (0.08 sec)
#修改用戶名如下
mysql> rename user tom@'localhost' to jerry@'localhost';
Query OK, 0 rows affected (0.07 sec)
#刪除用戶如下
mysql> drop user jerry@localhost;
Query OK, 0 rows affected (0.11 sec)
12.2 用戶授權
-
授權是在數據庫服務器里添加用戶并設置權限及密碼
-
重復執行grant命令時如果庫名和用戶名不變時，是追加權限
-
語法：
grant 權限列表 on 庫名 to 用戶名@"客戶端地址";
權限表示方法如下:
- all //所有權限
- usage //登陸權限或無權限
- select,update,insert //個別權限
- select,update(字段1,字段n) //指定字段庫名用法如下:
- *.* //所有庫所有表
- 庫名.* //一個庫
- 庫名.表名 //一張表
mysql> grant select on *.* to tom@'localhost';
Query OK, 0 rows affected (0.06 sec)mysql> grant delete on tarena.* to tom@'localhost';
Query OK, 0 rows affected (0.13 sec)mysql> grant insert on tarena.departments to tom@'localhost';
Query OK, 0 rows affected (0.08 sec)mysql> grant update(name) on tarena.user to tom@'localhost';
Query OK, 0 rows affected (0.07 sec)mysql> show grants for tom@'localhost';
+---------------------------------------------------------------+
| Grants for tom@localhost |
+---------------------------------------------------------------+
| GRANT SELECT ON *.* TO `tom`@`localhost` |
| GRANT DELETE ON `tarena`.* TO `tom`@`localhost` |
| GRANT INSERT ON `tarena`.`departments` TO `tom`@`localhost` |
| GRANT UPDATE (`name`) ON `tarena`.`user` TO `tom`@`localhost` |
+---------------------------------------------------------------+
4 rows in set (0.00 sec)
12.3 授權庫
mysql庫,存儲用戶權限信息,主要表如下:
-
user表 //保存已有的授權用戶及用戶對所有庫的權限
-
db表 //保存已有授權用戶對某一個庫的訪問權限
-
tables_priv表 //記錄已有授權用戶對某一張表的訪問權限
-
columns_priv表 //記錄已有授權用戶對某一個字段的訪問權限
12.4 撤銷權限
-
刪除已有授權用戶的權限
-
庫名必須和授權時的表示方式一樣
語法：revoke 權限列表 on 庫名 from 用戶名@"客戶端地址";
mysql> revoke drop,delete on *.* from tom@'localhost';
Query OK, 0 rows affected (0.34 sec)
12.5 用戶管理相關命令
| 命令 | 作用 |
|---|---|
| select user(); | 顯示登錄用戶名和客戶端地址 |
| show grants; | 用戶顯示自身訪問權限 |
| show grants for 用戶名@“客戶端地址”; | 管理員查看已有授權用戶權限 |
| set password for 用戶名@“客戶端地址”=“密碼”; | 管理員重置授權用戶連接密碼 |
| drop user 用戶名@“客戶端地址”; | 刪除授權用戶(必須有管理員權限) |
13. 數據備份/ 恢復
13.1 備份方式及策略
按照數據庫服務狀態分為：
-
冷備份：在在備份時停止數據庫服務，將整個數據庫復制到備份設備中。
-
熱備份：在備份時數據庫是運行的。
按照備份的數據分為：
-
物理備份：備份數據庫服務器上存儲的原始數據和文件，可以直接拷貝和恢復。
-
邏輯備份：備份的是 SQL語句，可用于不同版本和不同類型的MySQL數據庫之間的數據遷移。
備份策略
13.2 物理備份及恢復
-
定義: 備份數據庫的物理文件，如數據文件和日志文件。
-
優點: 恢復速度快。
-
缺點: 依賴于數據庫系統，可讀性差。
示例：
# 物理備份時要停掉mysql服務
[root@mysql50 ~]# systemctl stop mysqld
[root@mysql50 ~]# mkdir /bakdir #創建備份目錄
# 這里使用的是拷貝的方式,也可以使用tar備份的方式
[root@mysql50 ~]# cp -r /var/lib/mysql /bakdir/mysql.bak #拷貝數據庫目錄
[root@mysql50 ~]# rsync -av /bakdir/mysql.bak root@192.168.88.51:/root/#在192.168.88.51主機實現恢復操作
[root@mysql51 ~]# systemctl stop mysqld #停止服務
[root@mysql51 ~]# rm -rf /var/lib/mysql/* #清空數據庫目錄
# 如果是tar備份的方式,就使用tar -xf的命令,解壓到/var/lib/mysql/
[root@mysql51 ~]# cp -r /bakdir/mysql.bak/* /var/lib/mysql/
[root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql #修改所有者和組用戶
[root@mysql51 ~]# systemctl start mysqld #啟動服務
#連接服務查看數據,會發現數據就被恢復了
[root@mysql51 ~]# mysql -uroot -p'123'
13.3 邏輯備份及恢復
-
定義: 備份數據庫的邏輯數據，如表、視圖、存儲過程等。
-
優點: 獨立于數據庫系統，可讀性好。
-
缺點: 恢復速度慢
庫名表示方法
-
庫名 表名列表 //備份1張表或多張表所有數據
-
-B 庫名 //備份1個庫或多個庫所有數據
-
–all-database或-A //備份1臺服務器所有數據
示例：
#備份1張表或多張表如下
[root@mysql50 ~]# mysqldump -u用戶名 -p密碼 庫名 表名列表 > /bakdir/xxx.sql
#備份1個庫或多個庫如下
[root@mysql50 ~]# mysqldump -u用戶名 -p密碼 -B 庫名1 庫名2 > /bakdir/xxx.sql
#備份所有庫如下
[root@mysql50 ~]# mysqldump -u用戶名 -p密碼 -A > /bakdir/xxx.sql
[root@mysql50 ~]# rsync -av /bakdir/*.sql root@192.168.88.51:/root/#恢復庫數據如下
[root@mysql51 ~]# mysql -uroot -p密碼 < /root/xxx.sql
#恢復表數據如下
[root@mysql51 ~]# mysql -uroot -p密碼 庫名 < /root/xxx.sql
13.4 增量備份及恢復
xtrabackup 是一個開源的、基于MySQL數據庫文件級別的備份工具，由Percona公司開發。它主要用于熱備份MySQL數據庫，即在數據庫運行時進行備份，而不需要關閉數據庫服務。xtrabackup 利用了MySQL的InnoDB存儲引擎特性，能夠實現一致性的備份，并且備份過程對數據庫性能的影響較小。
對數據做增量備份前，必須先有一次備份，也就是首次備份，通常首次備份備份所有數據；比如每周周一完全備份，每周周二到周日增量備份。
首次完成備份
xtrabackup --host=127.0.0.1 --user=用戶名 --password=密碼 --datadir=數據庫目錄 --backup --target-dir=備份目錄
# 周一完全備份（備份所有數據）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password --databir=/var/lib/mysql --backup --target-dir=/fullbak
增量備份
xtrabackup --host=127.0.0.1 --user=用戶名 --password=密碼 --datadir=數據庫目錄 --backup --incremental-basedir=參考目錄 --target-dir=備份目錄
# 周二增量備份（備份周一備份后新產生的數據）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/fullbak --target-dir=/new2# 周三增量備份（備份周二備份后新產生的數據）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/new2 --target-dir=/new3# 周四增量備份（備份周三備份后新產生的數據）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/new3 --target-dir=/new4# 周五增量備份（備份周四備份后新產生的數據）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/new4 --target-dir=/new5# 周六增量備份（備份周五備份后新產生的數據）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/new5 --target-dir=/new6# 周日增量備份（備份周六備份后新產生的數據）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/new6 --target-dir=/new7
數據增量恢復
增量恢復步驟:
- 準備恢復數據
- 合并數據
- 清空數據庫目錄
- 拷貝數據
- 修改數據庫目錄所有者/組用戶為mysql
- 重啟數據庫服務
xtrabackup --prepare --apply-log-only --target-dir=/完全備份目錄
# 拷貝完全備份文件
[root@mysql50 ~]# rsync -av /fullbak root@192.168.88.51:/opt/
# 拷貝增量備份文件
[root@mysql50 ~]# rsync -av /new* root@192.168.88.51:/opt/[root@mysql51 ~]# xtrabackup --prepare --apply-log-noly --target-dir=/opt/fullbak
合并數據
xtrabackup --prepare --apply-log-noly --target-dir=/完全備份目錄 --incremental-dir=/新數據目錄
# 將周二的增量數據拷貝到周一備份目錄里，合并后周一的目錄里存放的是周一 + 周二 的數據
[root@mysql51 ~]# xtrabackup --prepare --apply-log-noly --target-dir=/opt/fullbak --incremental-dir=/opt/new2# 將周三的增量數據拷貝到周一備份目錄里，合并后周一的目錄里存放的是周一 + 周二 + 周三 的數據
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/fullbak --incremental-dir=/opt/new3
......#依此類推# 將周日的增量數據拷貝到周一備份目錄里，合并后周一的目錄里存放的是周一 + 周二 + 周三 + 周四 + 周五 + 周六 + 周日 的數據
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/fullbak --incremental-dir=/opt/new7
拷貝數據
xtrabackup --copy-back --target-dir=/完全備份目錄
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
[root@mysql51 ~]# xtrabackup --copy-back --target-dir=/fullbak
[root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql
[root@mysql51 ~]# systemctl restart mysqld
# 進入數據庫,會發現從周一到周日的數據都有
[root@mysql51 ~]# mysql -u用戶 -p密碼
13.5 差異備份與恢復
首次完成備份
xtrabackup --host=127.0.0.1 --user=用戶名 --password=密碼 --datadir=數據庫目錄 --backup --target-dir=備份目錄
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --target-dir=/allbak
差異備份
xtrabackup --host=127.0.0.1 --user=用戶名 --password=密碼 --datadir=數據庫目錄 --backup --incremental-basedir=完全備份目錄 --target-dir=備份目錄
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/allbak --target-dir=/dir2[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/allbak --target-dir=/dir3[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/allbak --target-dir=/dir4
差異數據恢復
差異恢復數據步驟
-
準備恢復數據
-
合并數據
-
清空數據庫目錄
-
拷貝數據
-
修改數據庫目錄所有者/組用戶為mysql
-
重啟數據庫服務
xtrabackup --prepare --apply-log-only --target-dir=完全備份目錄
[root@mysql50 ~]# rsync -av /allbak root@192.168.88.51:/root/ 第一次完全備份
[root@mysql50 ~]# rsync -av /dir4 root@192.168.88.51:/root/ 最后一次差異備份[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/allbak
合并數據
xtrabackup --prepare --apply-log-only --target-dir=/完全備份目錄 --incremental-dir=/最后一次差異備份
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/allbak --incremental-dir=/root/dir4
拷貝數據
xtrabackup --copy-back --target-dir=/完全備份目錄
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
[root@mysql51 ~]# xtrabackup --copy-back --target-dir=/root/allbak
[root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql
[root@mysql51 ~]# systemctl restart mysqld
# 數據會被成功恢復
[root@mysql51 ~]# mysql -u用戶 -p密碼
13.6 數據實時備份與恢復
binlog日志管理
-
binlog日志也稱做二進制日志
-
MySQL服務日志文件的一種
-
保存出查詢之外的所有sql命令
-
可用于數據的備份和恢復
-
配置mysql主從同步的必要條件
相關命令
mysql> show master status; # 查看正在使用的日志
mysql> show binary logs; # 查看已有的日志文件
mysql> purge master logs to "日志名"; # 刪除編號之前的日志
mysql> flush logs; # 刷新日志
mysql> reset master; # 刪除所有日志，重新創建日志
mysql> show binlog events in "日志文件名";# 查看日志文件內容
自定義日志
| 配置項 | 用途 |
|---|---|
| server_id=數字 | 指定id值(1-255) |
| log_bin=/目錄/日志名 | 啟用binlog日志 |
| max_binlog_size=數值m | 指定日志文件容量，默認1G |
[root@mysql52 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log-bin=/mylog/mysql52 //定義日志目錄和日志文件名(手動添加)
:wq
[root@mysql52 ~]# mkdir /mylog 創建目錄
[root@mysql52 ~]# chown mysql /mylog 修改目錄所有者mysql用戶
[root@mysql52 ~]# setenforce 0 關閉selinux
[root@mysql52 ~]# systemctl restart mysqld 重啟服務
[root@mysql52 ~]# ls /mylog/ 查看日志目錄
mysql52.000001 mysql52.index
恢復命令
把查看到的文件內容管道給連接mysql服務的命令執行
mysqlbinlog /目錄/文件名 | mysql -u用戶名 -p密碼
# 把日志文件拷貝給恢復數據的服務器,比如 mysql50
[root@mysql52 ~]# rsync -av /mylog/mysql52.000001 root@192.168.88.50:/root/# 執行日志恢復數據
[root@mysql50 ~]# mysqlbinlog /root/mysql52.000001 | mysql -uroot -p123# 進入數據庫,會發現數據通過binlog日志恢復了
[root@mysql50 ~]# mysql -u用戶 -p密碼
13.7 推薦備份策略
選擇哪種備份策略取決于組織的具體需求、資源限制和恢復目標。以下是一些考慮因素
-
如果數據量不大或者初期備份時，可以使用完整備份，因為它最直觀也最全面
-
如果數據量較大且變化頻繁，增量備份是一個節省空間的選擇，但需要注意恢復過程的復雜性
-
如果需要快速恢復且有足夠的存儲空間，差異備份是一個較好的選擇，因為它結合了全備份和增量備份的優點
-
對于需要實時備份和數據同步的場景，Binlog備份是非常合適的
14. MySQL 主從同步
14.1 原理&結構概括
數據庫復制技術
可以將一個MySQL數據庫的變更操作同步到另外一個或多個MySQL數據庫中，分為2種角色：
-
主（master）服務器：接受客戶端訪問連接
-
從 (slave）服務器：自動同步主服務器數據
主從同步原理
通過復現主服務器binlog日志中SQL語句的方式實現數據實時同步,在標準的 MySQL 主從同步（Replication）配置中，從服務器（Slave）是只讀的，不允許進行寫操作。所有的寫操作都應該在主服務器（Master）上進行，然后通過二進制日志（Binary Log）同步到從服務器。
數據同步的步驟
-
主庫將數據的事務操作（DML）記錄到binlog日志文件里。
-
從庫監聽主庫binlog日志變化，當發生變化后，IO線程去請求主庫的binlog日志，并將日志變化寫入到relaylog日志中，此時主庫生產logdump線程，給從庫IO線程傳輸binlog日志文件內容
-
從庫更新relaylog文件，SQL線程將relaylog文件中的更新執行一遍，達到了與主庫數據一致的目的。
Master:啟用binlog日志，記錄所有的數據庫更新和修改操作
Slave:
- Slave_IO：復制master主機 binlog日志文件里的SQL命令到本機的 relay-log文件里。
- Slave_SQL：執行本機relay-log文件里的SQL語句，實現與Master數 據一致。
結構模式（宏觀模式）
區分數據庫服務器的角色
-
一主一從：搭建簡單，數據副本數少
-
一主多從：數據多副本且無關聯，主節點負載高
-
級聯復制：相對主節點負載低，數據多副本，但有關聯
-
互為主從：多節點寫，容易出現死鎖
復制模式 (微觀模式) 保證MySQL主從同步性能
-
異步復制:主服務器處理完SQL直接返回結果
-
半同步復制:主服務器處理完SQL等待至少1個從完成數據同步后返回結果
-
全同步復制:主服務器處理完SQL等待所有從完成數據同步后返回結果
14.2 構建主從同步
主要步驟如下:主服務器上的配置:
1. 安裝MySQL并啟動服務
2. 配置binlog日志和server_id
[root@mysql53 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=53 # 設置唯一的server-id
log-bin=mysql53 # 開啟binlog，指定日志文件前綴
:wq
[root@mysql53 ~]# systemctl restart mysqld3. 授權主從同步用戶
[root@mysql53 ~]# mysql
mysql> create user repluser@"%" identified by "123qqq...A";
mysql> grant replication slave on *.* to repluser@"%";4. 備份已有數據
[root@mysql53 ~]# mysqldump -uroot -p -A > all.sql
[root@mysql53 ~]# rsync -av all.sql 192.168.88.54:/root從服務器上的配置: //如果想要實現一主多從的場景,就可以把以下對從服務器的配置在目標服務器上執行,目標服務器就是從服務器了,要注意日志文件名和偏移量
1. 安裝MySQL并啟動服務
2. 還原主服務器備份數據
[root@mysql54 ~]# mysql -u用戶 -p < all.sql 3. 配置server_id
[root@mysql54 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=54 # 設置唯一的server-id
:wq
[root@mysql54 ~]# systemctl restart mysqld4.[root@mysql54 ~]# mysqlmysql> change master to //設置主服務器信息-> master_host=主服務器地址-> master_port=主服務器端口-> master_user=主服務器授權用戶-> master_password=主服務器授權用戶密碼-> master_log_file=主服務器正在使用的binlog日志-> master_log_pos=主服務器當前偏移量5.mysql> start slave //啟動io/sql線程 mysql> show slave status\G //查看狀態信息
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.88.53 #主服務器ip地址Master_User: repluser #主服務器授權用戶Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql53.000001 #主服務器正在使用的binlog日志Read_Master_Log_Pos: 667 #主服務器當前偏移量Relay_Log_File: mysql54-relay-bin.000002Relay_Log_Pos: 322Relay_Master_Log_File: mysql53.000001Slave_IO_Running: Yes #IO線程已運行Slave_SQL_Running: Yes #SQL線程已運行
錯誤處理
stop slave; //停止io/sql 線程
reset slave; //重置io/sql 線程
change master to 語句 //設置主服務器信息
start slave; //啟動io/sql 線程
show slave status\G //查看狀態信息
15. MySQL 讀寫分離
15.1 讀寫分離概述
是指將MySQL數據庫的讀操作和寫操作分別分配到不同的服務器上，通過這種方式可以提高數據庫的并發處理能力和性能、降低系統失敗的風險。要保證負責讀訪問主機與負責寫訪問主機的數據一致
解決MySQL主從同步中主服務器負載高的解決方案
實現方法
-
客戶端分離：開發者手工分離
-
服務端分離：加設MySQL主從復制組的代理服務器
解決方案：插入中間件軟件
運行在應用程序和數據庫之間的軟件，通過攔截和處理數據庫請求來實現讀寫分離、負載均衡、故障切換等功能。工作原理就是識別SQL類型按照主寫讀從的規則進行分發。
| 軟件 | 介紹 |
|---|---|
| MySQL Proxy | 基于Lua腳本語言編寫的代理服務器 |
| MaxScale | 開源的MySQL數據庫代理服務器 |
| Cobar | 阿里巴巴開發的一款開源的MySQL數據庫中間件 |
| TcaplusDB | 騰訊云開發的一款分布式NoSQL數據庫 |
| Mycat2 | 阿里巴巴旗下的一個項目 |
15.2 構建讀寫分離思路
以下是基于mycat2實現讀寫分離的步驟：1. 部署MySQL主從同步
2. 部署MyCat服務：裝包，改配置文件，配置數據庫服務，啟動服務
3. 進入mycat2數據庫，創建添加數據源和集群（添加master角色主機、添加slave角色主機）
4. 修改master角色主機僅負責寫訪問，修改slave角色主機僅負責讀訪問
5. 修改自己創建的集群的讀策略
6. 指定庫存儲數據使用的集群，然后重啟mycat2服務，驗證讀寫分離###############mycat2熱配置模板####################
# 用戶相關配置
/*+ mycat: showusers{} */;
/*+ mycat: createuser{"name":"xx","password":"xx"...} */;
/*+ mycat: dropuser{"name":"xx"} */;# 數據源相關配置
/*+ mycat: showdatasources{} */;
/*+ mycat: createdatasource{"name":"xx"...} */;
/*+ mycat: dropdatasource{"name":"xx"} */;
示例：
mysql> /*+ mycat:createdatasource{-> "name":"whost56",-> "url":"jdbc:mysql://192.168.88.56:3306",-> "user":"plja","password":"123456"-> }*/;
mysql> /*+ mycat:createdatasource{-> "name":"rhost57",-> "url":"jdbc:mysql://192.168.88.57:3306",-> "user":"plja","password":"123456"-> }*/;
mysql> /*+ mycat: showdatasources{} */\G# 集群相關配置
/*+ mycat: showclusters{} */;
/*! mycat: createcluster{"name":"xx"...} */;
/*! mycat: dropcluster{"name":"xx"} */;
示例：
mysql> /*! mycat:createcluster{-> "name":"rwcluster",-> "masters":["whost56"],-> "replicas":["rhost57"]-> }*/;
mysql> /*! mycat:showclusters{}*/\G# 邏輯庫相關配置
/*+ mycat: showschemas{} */;
/*+ mycat: createschema{"name":"xx"...} */;
/*+ mycat: dropschema{"name":"xx"} */;
16. MySQL 分庫分表
16.1 什么是分庫分表
將存放在一臺數據庫服務器中的數據，按照特定方式進行拆分，分散存放到多臺數據庫服務器中，以達到分散單臺服務器負載的效果
16.2 相關概念
-
分庫：指在表數量不變的情況下對庫進行切分
-
分表：指在庫數量不變的情況下對表進行切分
-
分庫分表：指庫和表都切分，數據量都發生改變
垂直分割
-
垂直分庫：指的是將單個庫中的表分到多個庫，每個庫包含的表不一樣
-
垂直分表：指的是將存在一張表中的表頭切分到多張，切分后每張表的結構不一樣、數據不一樣，所有表的表頭和一起是原表的表頭
水平分割
-
水平分庫：將表水平切分后分散到不同庫里，每個庫有相同的表，但表里的數據不同
-
水平分表：表的結構不變，數據分散存儲到不同表中，每個表的結構一樣、數據不一樣，所有表的數據合并是表的總數據
企業常見的場景是：垂直分庫+水平分表
解決方案：使用mycat中間件
16.3 Mycat是什么
-
Mycat是一個開源的基于MySQL協議的分布式數據庫中間件
-
能夠將多個MySQL節點組合成一個邏輯上的數據庫集群，并提供讀寫分離、分庫分表、自動切換、自動負載均衡等功能。其主要目的是為了解決MySQL單點故障、性能瓶頸、數據擴容等問題，從而提升MySQL的可用性和性能
-
Mycat使用Java語言開發，運行于JVM之上，支持高并發的SQL處理，同時也具有一定的自動化管理能力，可以根據配置文件自動化地完成各種數據庫操作
16.4 分庫分表的思路
1. 先搭建兩一主一從的主從復制，并創建mycat連接主從mysql的用戶
2. 部署MyCat服務：裝包，改配置文件，配置數據庫服務，啟動服務
3. 再MyCat里執行以下模板命令：
/*+ mycat:createdatasource{
"url":"jdbc:mysql://192.168.88.59:3306", # 主庫
"user":"plj", # 連接mysql數據庫的用戶
"password":"123456", # 連接mysql數據庫的密碼
"name":"dw0" # 數據源的名字
}*/;/*+ mycat:createdatasource{
"url":"jdbc:mysql://192.168.88.60:3306", # 從庫
"user":"plj", # 連接mysql數據庫的用戶
"password":"123456", # 連接mysql數據庫的密碼
"name":"dr0" # 數據源的名字
}*/;/*+ mycat:createdatasource{
"url":"jdbc:mysql://192.168.88.61:3306", # 主庫
"name":"dw1",
"user":"plj",
"password":"123456"
}*/;/*+ mycat:createdatasource{
"url":"jdbc:mysql://192.168.88.62:3306", # 從庫
"name":"dr1",
"user":"plj",
"password":"123456"
}*/;# 以下是創建集群
mysql> /*!mycat:createcluster{-> "name":"c0", # 做分庫分表必須是c開頭，從0開始-> "masters":["dw0"],-> "replicas":["dr0"]-> }*/;
Query OK, 0 rows affected (0.02 sec)mysql> /*!mycat:createcluster{-> "name":"c1", # 做分庫分表必須是c開頭-> "masters":["dw1"],-> "replicas":["dr1"]-> }*/;
Query OK, 0 rows affected (0.01 sec)mysql> /*!mycat:showcluster{}*/\G4. 就是驗證測試了，看下面16.5的內容
16.5 測試配置
分片表
根據分片規則計算結果存儲數據
-
dbpartition by //數據分區，表示將表的數據分為多個分區，每個分區存儲在不同的數據庫節點中。
-
tbpartition by //表分區，表示在每個數據分區中，再將數據分為多個表分區。
-
mod_hash() //取模哈希分片規則
-
tbpartition //表分片數量
-
dbpatition //庫分片數量
# 客戶端進入mycat，創建表來進行實驗
mysql> create table tarena.employees(-> employee_id int primary key,-> name char(10),-> dept_id int,-> mail varchar(30)-> )-> dbpartition by mod_hash(employee_id)-> tbpartition by mod_hash(employee_id)-> dbpartitions 2 tbpartitions 1;
Query OK, 0 rows affected (3.35 sec)# 來到第一個主庫來查看庫和表
[root@mysql59 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tarena |
| tarena_0 |
+--------------------+
[root@mysql59 ~]# mysql -e 'use tarena_0 ; show tables'
+--------------------+
| Tables_in_tarena_0 |
+--------------------+
| employees_0 |
+--------------------+# 來到第二個主庫來查看庫和表
[root@mysql61 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tarena |
| tarena_1 |
+--------------------+
[root@mysql61 ~]# mysql -e 'use tarena_1;show tables'
+--------------------+
| Tables_in_tarena_1 |
+--------------------+
| employees_1 |
+--------------------+# 客戶端再次進入mycat，來存儲數據
mysql> insert into tarena.employees values (9,"jim","1","jim@163.com");
mysql> insert into tarena.employees values (8,"tom","3","tom@QQ.com");
mysql> insert into tarena.employees values (7,"lucy","2","lucy@QQ.com");
mysql> insert into tarena.employees values (6,"john","2","john@QQ.com");
mysql> select * from tarena.employees;
+-------------+------+---------+-----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+-----------+
| 6 | jim | 2 | jim@QQ.com |
| 8 | tom | 3 | tom@QQ.com |
| 7 | lucy | 2 | lucy@QQ.com |
| 9 | john | 1 | john@163.com |
+-------------+------+---------+-----------+
4 rows in set (2.07 sec)# 因為上面創建表的時候使用了mod_hash(employee_id)，意思就是employee_id字段對集群的數量取余，該案例有兩個集群，也就是employee_id對2取余，結果不是0就是1，所以當employee_id的值為偶數時，結果鐵定是0，正好對應了上面定義的c0集群，所以被分到了c0集群中，當employee_id的值為奇數時，結果鐵定是1，正好對應了上面定義的c1集群，所以被分到了c1集群中
# 查看c0集群中 數據庫服務器存儲的數據
[root@mysql59 ~]# mysql -e 'select * from tarena_0.employees_0'
+-------------+------+---------+----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+----------+
| 6 | jim | 2 | jim@QQ.com |
| 8 | tom | 3 | tom@QQ.com |
+-------------+------+---------+----------+# 查看c1集群中 數據庫服務器存儲的數據
[root@mysql61 ~]# mysql -e 'select * from tarena_1.employees_1'
+-------------+------+---------+-----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+-----------+
| 7 | lucy | 2 | lucy@QQ.com |
| 9 | john | 1 | john@163.com |
+-------------+------+---------+-----------+
ER表
稱為關聯表，表示數據邏輯上有關聯性的兩個或多個表，例如工資表和員工表。對于關聯表，通常希望他們能夠有相同的分片規則，這樣在進行關聯查詢時，能夠快速定位到同一個數據分片中。MyCat2中對于關聯表，不需要有過多的聲明，他可以根據分片規則自行判斷。
# 客戶端連接mycat，創建表
mysql> create table tarena.salary(-> employee_id int primary key,-> p_date date,basic int,bonux int-> )dbpartition by mod_hash(employee_id)-> tbpartition by mod_hash(employee_id)-> tbpartitions 1;
Query OK, 1 row affected (1.63 sec)
# 在MyCat2終端查看關聯表關系
mysql> /*+ mycat:showErGroup{}*/;
+---------+------------+-----------+
| groupId | schemaName | tableName |
+---------+------------+-----------+
| 0 | tarena | employees |
| 0 | tarena | salary |
+---------+------------+-----------+
2 rows in set (0.01 sec)# 在c0集群master服務器查看表
[root@mysql59 ~]# mysql -e 'use tarena_0 ; show tables'
+--------------------+
| Tables_in_tarena_0 |
+--------------------+
| employees_0 |
| salary_0 |
+--------------------+# 在c1集群master服務器查看表
[root@mysql61 ~]# mysql -e 'use tarena_1;show tables'
+--------------------+
| Tables_in_tarena_1 |
+--------------------+
| employees_1 |
| salary_1 |
+--------------------+# 客戶端連接mycat服務并插入數據
mysql> insert into tarena.salary values(6,20230110,20000,2000);
mysql> insert into tarena.salary values(7,20230210,25000,2500);
mysql> insert into tarena.salary values(8,20230310,30000,3000);
mysql> insert into tarena.salary values(9,20230410,35000,3500);# 在c0集群master服務器查看表
[root@mysql59 ~]# mysql -e 'select * from tarena_0.employees_0'
+-------------+------+---------+----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+----------+
| 6 | C | 2 | c@QQ.com |
| 8 | B | 3 | B@QQ.com |
+-------------+------+---------+----------+# 在c1集群master服務器查看表
[root@mysql61 ~]# mysql -e 'select * from tarena_1.employees_1'
+-------------+------+---------+-----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+-----------+
| 7 | C | 2 | c@QQ.com |
| 9 | a | 1 | a@163.com |
+-------------+------+---------+-----------+
全局表
所有庫都有表的全部數據
# 客戶端連接mycat，創建表
mysql> create table tarena.dept(-> dept_id int primary key,-> dept_name char(10)-> )default charset utf8mb4-> broadcast;
Query OK, 4 rows affected (2.45 sec)
mysql> insert into tarena.dept values(1,"開發部"),(2,"運維部"),(3,"測試部");# 在4臺數據庫服務器查看數據
[root@mysql59 ~]# mysql -e 'select * from tarena.dept'
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 開發部 |
| 2 | 運維部 |
| 3 | 測試部 |
+---------+-----------+[root@mysql60 ~]# mysql -e 'select * from tarena.dept'
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 開發部 |
| 2 | 運維部 |
| 3 | 測試部 |
+---------+-----------+[root@mysql61 ~]# mysql -e 'select * from tarena.dept'
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 開發部 |
| 2 | 運維部 |
| 3 | 測試部 |
+---------+-----------+[root@mysql62 ~]# mysql -e 'select * from tarena.dept'
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 開發部 |
| 2 | 運維部 |
| 3 | 測試部 |
+---------+-----------+
 深入使用cdk 練習aws athena)
