MySQL學習

— Java攻城獅學習路線 —

入門：

• 1. 簡介
• 2. 安裝
• 3. 資料庫連接
• 4. 資料庫操作
• 5. 註釋
• 6. 數據類型
• 7. 數據表操作
• 8. 數據操作
• 9. 常用函數
• 10. 導入導出數據
• 11. 圖形化工具
• 12. SQL快速參考

進階：

• 13. 存儲引擎
• 14. 索引
• 15. 查詢性能優化
• 16. 切分
• 17. 故障轉移與故障恢復

1. 簡介

MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，目前屬於 Oracle 旗下產品。MySQL 是最流行的關係型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關係資料庫管理系統) 應用軟體。

1.1 什麼是據庫？

資料庫（Database）是按照數據結構來組織、存儲和管理數據的倉庫。每個資料庫都有一個或多個不同的API用於創建，訪問，管理，搜索和複製所保存的數據。基於讀寫速度考慮，現在使用關係型資料庫管理系統（RDBMS）來存儲和管理大數據量。所謂的關係型資料庫，是建立在關係模型基礎上的資料庫，藉助於集合代數等數學概念和方法來處理資料庫中的數據。其特點為：

• 數據以表格的形式出現
• 每行為各種記錄
• 每列為記錄所對應的數據項
• 許多的行和列組成一張表單
• 若幹的表單組成資料庫

1.2 RDBMS 術語

• 資料庫: 資料庫是一些關聯表的集合。
• 數據表: 表是數據的矩陣。在一個資料庫中的表看起來像一個簡單的電子錶格。
• 列: 一列(數據元素) 包含了相同的數據, 例如郵政編碼的數據。
• 行：一行（=元組，或記錄）是一組相關的數據，例如一條用戶訂閱的數據。
• 冗餘：存儲兩倍數據，冗餘降低了性能，但提高了數據的安全性。
• 主鍵：主鍵是唯一的。一個數據表中只能包含一個主鍵。你可以使用主鍵來查詢數據。
• 外鍵：外鍵用於關聯兩個表。
• 複合鍵：複合鍵（組合鍵）將多個列作為一個索引鍵，一般用於複合索引。
• 索引：使用索引可快速訪問資料庫表中的特定信息。索引是對資料庫表中一列或多列的值進行排序的一種結構。類似於書籍的目錄。
• 參照完整性: 參照的完整性要求關係中不允許引用不存在的實體。與實體完整性是關係模型必須滿足的完整性約束條件，目的是保證數據的一致性。

2. 安裝

MySQL下載地址：https://www.mysql.com/downloads/
相關安裝教程較為簡單，可自行查詢。

3. 資料庫連接

3.1 使用命令連接

Linux和Windows連接方式一樣，在命令行輸入：

[root@host]# mysql -uroot -p
Enter password:******

登錄後成功後輸出：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is XXXXXX to server version: 5.X.X

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

基本操作：

• 查看幫助：
  • hlep;
  • \h
  • ?加上相關關鍵字
• 取消當前命令執行：\c
• 命令行結束符：
  • ;
  • \g
• 退出系統：
  • exit
  • quit
  • \q

3.2 JDBC連接

利用Java API實現資料庫連接，詳情見JDBC相關資料。

4. 資料庫操作

4.1 創建資料庫

使用create關鍵詞創建資料庫，database和schema等效。

CREATE DATABASE dbname;
CREATE SCHEMA dbname;

指定編碼方式創建資料庫。

CREATE DATABASE [IF NOT EXISTS] dbname [DEFAULT] CHARACTER SET [=] utf-8;

4.2 查看資料庫

使用show關鍵字查看當前伺服器下全部資料庫。

SHOW DATABASE;
SHOW SCHEMA;

4.3 查看資料庫詳細信息

SHOW CREATE DATABASE dbname;

4.4 修改資料庫編碼方式

ALTER DATABASE dbname [DEFAULT] CHARACTER SET [=] utf-8;

註：[]表示可以省略

4.5 打開資料庫

USE dbname;

4.6 得到當前打開資料庫

SELECT DATABASE();
SELECT SCHEMA();

4.7 刪除資料庫

DROP DATABASE [IF EXISTS] dbname;

5. 註釋

兩種註釋方式。

# 這是一段註釋1
-- 這是一段註釋2

6. 數據類型

6.1 數值類型

6.2 字元串類型

6.3 日期和時間類型

7. 數據表操作

數據表由行和列組成，每個數據表中至少有1列，行可以0,1或多行，表名必須唯一，且含義明確。

7.1 創建表

CREATE TABLE [IF NOT EXISTS] tblname(
    欄位名稱 欄位類型 [完整性約束條件]，
    欄位名稱 欄位類型 [完整性約束條件]，
    欄位名稱 欄位類型 [完整性約束條件]，
    ...
)ENGINE=存儲引擎 CHARSET=編碼方式;

MySQL中在表創建時指定數據項的約束條件：

# 實例
CREATE TABLE IF NOT EXISTS `tbl`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

7.2 查看表

SHOW [FULL] TABLES [[FROM/IN] dbname] [LIKE 'pattern' / WHERE expr]
#實例
SHOW TABLES;

7.2.1 查看指定數據表的詳細信息：

SHOW CREATE TABLE tlbname;

7.2.2 查看表結構：

DESC tblname;
DESCRIBE tblname;
SHOW COLUMNS FROM tblname；

7.4 刪除表

DROP TABLE [IF EXISTS] tblname;

7.5 表結構操作

7.5.1 添加欄位

ALTER TABLE tblname
ADD 欄位名稱 欄位屬性 [完整性約束條件]
[FIRST|AFTER 欄位名稱];

7.5.2 刪除欄位

ALTER TABLE tblname
DROP 欄位名稱;

7.5.3 添加預設值

ALTER TABLE tblname
ALTER 欄位名稱 SET DEFAULT 預設值;

7.5.4 刪除預設值

ALTER TABLE tblname
ALTER 欄位名稱 DROP DEFAULT;

7.5.5 修改欄位類型，屬性

ALTER TABLE tblname
MODIFY 欄位名稱 欄位類型 [欄位屬性] [FIRST|AFTER 欄位名稱];

7.5.6 修改欄位名稱、類型、屬性

ALTER TABLE tblname
CHANGE 原欄位名稱 新欄位名稱 欄位類型 欄位屬性
[FIRST|AFTER 欄位名稱];

7.5.7 添加主鍵

ALTER TABLE tblname
ADD PRIMARY KEY(欄位名稱);

7.5.8 刪除主鍵

ALTER TABLE tblname
DROP PRIMARY KEY;

7.5.9 添加唯一索引

# 1
ALTER TABLE tblname
ADD UNIQUE KEY indexname(欄位名稱);
# 2
ALTER TABLE tblname
ADD UNIQUE INDEX indexname(欄位名稱);

7.5.10 刪除唯一索引

ALTER TABLE tblname
DROP indexname;

7.5.11 修改數據表名稱

# 1
ALTER TABLE tblname
RENAME [TO|AS] newtblname;
# 2
RENAME TABLE tblname TO newtblname;

8. 數據操作

8.1 添加記錄

INSERT [INTO] tblname ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );

8.1.1 不指定欄位名稱

INSERT tblname VALUES ( value1, value2,...valueN );

8.1.2 列出指定欄位

INSERT tblname ( field1, field2,...fieldN ) 
VALUES ( value1, value2,...valueN );

8.1.3 一次添加多條記錄

# 1
INSERT tblname ( field1, field2,...fieldN ) VALUES 
( value1, value2,...valueN ),
( value1, value2,...valueN ),
...
( value1, value2,...valueN );
# 2 
INSERT tblname SET field1 = value1, field2 = value2,...;
# 3
INSERT tblname [(field1, field2,...fieldN)] 
SELECT field1, field2,...fieldN
FROM tblname [WHERE 條件];

8.2 修改記錄

# 如果不添加條件，整個表中的記錄都會更新
UPDATE tblname SET field1 = value1, field2 = value2,... 
[WHERE 條件];

8.3 刪除記錄

# 如果不加入條件，所有記錄都會刪除，增長值不會改變
DELETE FROM tblname [WHERE 條件];
# 設置增長值為1
ALTER TABLE tblname AUTO_INCREMENT = 1;
# 徹底清空數據表
TRUNCATE [TABLE] tblname;

8.4 查詢記錄

SELECT 條件1，條件2... FROM tblname
[WHERE 條件]
[GROUP BY colname或position HAVING 二次篩選]
[ORDER BY colname,postion或expr [ASC|DESC]]
[LIMIT 限制結果集顯示條數];

8.4.1 查詢所有記錄

SELECT * FROM tblname;
SELECT * FROM dbname.tblname;

8.4.2 指定欄位查詢

SELECT 欄位名稱,... FROM tblname;

8.4.3 給欄位起別名

SELECT 欄位名稱,... [AS] 別名名稱,... FROM dbname.tblname;

8.4.4 給表起別名

SELECT 欄位名稱,... FROM tblname [AS] 別名;

8.4.5 WHERE條件篩選記錄

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND|OR] condition2.....

8.4.6 GROUP BY分組

把值放到一個組中，結果只會顯示組中一條記錄

• 配合GROUP_CONCAT()查看組中某個欄位的詳細信息

SELECT GROUP_CONCAT(username),age,sex,addr
FROM tblname
GROUP BY sex;

• 配合聚合函數使用 COUNT()統計總數; SUM()求和; MAX()最大值; MIN()最小值; AVG()平均值
  註：COUNT(欄位名稱),不統計值為NULL記錄; COUNT(*)統計所有

SELECT sex, username, COUNT(*) FROM tblname
GROUP BY sex;

• 配合WITH ROLLUP使用，在記錄末尾添加一條，為所有記錄總和

SELECT sex, username FROM tblname
GROUP BY sex
WITH ROLLUP;

• HAVING字句對分組結果進行二次篩選

SELECT sex, username FROM tblname
GROUP BY sex
HAVING sex = '男';

8.4.7 ORDER BY排序

# 預設排序為升序 ASC，可指定降序 DESC
# 1.按照多個欄位排序
SELECT id, username, age
FROM tblname
ORDER BY age ASC, id ASC;
# 2.隨機排序
SELECT id, username, age
FROM tblname
ORDER BY RAND();

8.4.8 LIMIT 限制結果集展示條數

# 1.顯示前count條記錄
SELECT * FROM tblname
LIMIT count;
# 2. 從offset頁開始，顯示count條記錄
# offset從0開始編號
SELECT * FROM tblname
LIMIT offset, count;

8.5 多表查詢

8.5.1 笛卡爾積形式

# 列出所有可能連接
SELECT * FROM tblname1 CROSS JOIN tblname2;

8.5.2 內連接形式

# 查詢兩個表中符合連接條件的記錄
SELECT 欄位名稱,... FROM tblname1 
INNER JOIN tblname2
ON 連接條件;
# 例
SELECT e.id, e.username, e.age, d.depName 
FROM emp AS e 
INNER JOIN dep AS d
ON d.depID = d.id;

8.5.3 外連接形式

• 左外連接

# 先顯示左表中的全部記錄，再去右表中查詢複合條件記錄，不符合以NULL代替
SELECT 欄位名稱,... FROM tblname1 
LEFT OUTER JOIN tblname2
ON 連接條件;

• 右外連接

# 先顯示右表中的全部記錄，再去左表中查詢複合條件記錄，不符合以NULL代替
SELECT 欄位名稱,... FROM tblname1 
RIGHT OUTER JOIN tblname2
ON 連接條件;

8.6 外鍵約束

只有InnoDB支持外鍵，詳情查看存儲引擎相關資料。

8.6.1 創建外鍵

• 建表時指定

# 先創建主表，子表的外鍵欄位類型和主表要相似
# 如果外鍵欄位沒有創建索引，會自動創建
# 子表外鍵關聯必須是父表的主鍵
[CONSTRAINT 外鍵名稱] FOREIGN KEY (欄位名稱) 
REFERENCES 主表 (欄位名稱);

• 動態添加刪除外鍵

# 添加：要保證記錄合法
ALTER TABLE tblname 
ADD FOREIGN KEY (外鍵欄位)
REFERENCES 主表 (欄位名稱);
# 刪除
ALTER TABLE tblname 
DROP FOREIGN KEY (外鍵欄位);

8.6.2 外鍵約束

# 使用方法，設置時指定
# 刪除約束
ALTER TABLE tblname 
ADD FOREIGN KEY (外鍵欄位)
ON DELETE CASCADE;
# 更新約束
ALTER TABLE tblname 
ADD FOREIGN KEY (外鍵欄位)
ON UPDATE CASCADE;

8.7 特殊形式查詢

8.7.1 子查詢

SELECT 欄位名稱 FROM tblname
WHERE colname = (SELECT colname FROM tblname);

• 由IN引發的子查詢

# 也可以使用NOT IN
SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);

• 由比較運算符引出子查詢

SELECT id, username, score FROM stud
WHERE score >= (SELECT score FROM level WHERE id=1);

• 由EXISTS引發的子查詢

SELECT * FROM emp WHERE EXISTS (
SELECT depName FROM dep WHERE id = 10);

• 帶有ANY,SOME,ALL關鍵字的子查詢

SELECT * FROM stu
WHERE score >= ANY(
SELECT score FROM level);

• 其他方式

# INSERT ... SELECT
# CREATE ... SELECT
CREATE TABLE user(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20)
) SELECT id ,username FROM emp;
# 去掉欄位重覆值 DISTINCT
SELECT DISTINCT (username) FROM emp;

8.7.2 聯合查詢

# UNION 去掉重覆值
SELECT 欄位名稱,... FROM tblname1
UNION
SELECT 欄位名稱,... FROM tblname2
# UNION ALL 簡單合併
SELECT 欄位名稱,... FROM tblname1
UNION ALL
SELECT 欄位名稱,... FROM tblname2

8.7.3 自身連接查詢

可實現無極限分類。

9. 常用函數

9.1 數學函數

9.2 字元串函數

9.3 日期時間函數

9.4 其他函數

10. 導入導出數據

10.1 導出數據

10.1.1 SELECT ... INTO OUTFILE

SELECT * FROM runoob_tbl 
INTO OUTFILE '/tmp/tables.txt';
# 生成一個文件，各值用逗號隔開
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

10.1.2 mysqldump導出

mysqldump是mysql用於轉存儲資料庫的實用程式。它主要產生一個SQL腳本，其中包含從頭重新創建資料庫所必需的命令CREATE TABLE INSERT等。

# 1.命令欄導出
$ mysqldump -u root -p --no-create-info \
            --tab=/tmp DB tbl
password ******
# 2.導出表到指定文件
$ mysqldump -u root -p DB tbl > dump.txt
password ******
# 3.導出整個資料庫數據
$ mysqldump -u root -p DB > database_dump.txt
password ******
# 4.備份所有資料庫
$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

10.2 導入數據

10.2.1 LOAD DATA

# 1.導入dump.txt 
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
# 2.明確指出列的分隔符和尾行標記
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r\n';
# 3.指定列的順序
LOAD DATA LOCAL INFILE 'dump.txt' 
INTO TABLE mytbl (b, c, a);

10.2.2 mysqldump導入

# 1.導入dump.txt 
$ mysqlimport -u root -p --local database_name dump.txt
password *****
# 2.指定選項來設置指定格式
$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****
# 3.使用 --columns 選項來設置列的順序
$ mysqlimport -u root -p --local --columns=b,c,a \
    database_name dump.txt
password *****

11. 圖形化工具

MySQL Workbench 下載: https://dev.mysql.com/downloads/workbench/

12. 快速參考

更新中...