MySQL學習筆記匯總

02-03

在網上找了很久終於找到一個很適合初學者學習的MySQL視頻教程——與MySQL的零距離接觸（慕課網），這裡我把視頻教程的筆記匯總，方便大家查閱。

本文使用的是MySQL 5.5版本，在windows的cmd命令行里操作mysql。

鑒於文章筆記字數較多，這裡簡要做一個目錄。

一、安裝與入門

二、數據類型與操作數據表

三、約束以及修改數據表

四、操作數據表中的記錄

五、子查詢與連接

六、運算符和函數

七、自定義函數

八、MySQL存儲過程

九、MySQL存儲引擎

十、MySQL圖形化管理工具

一、安裝與入門

1.1 下載安裝

在MySQL官網下載安裝文件，這裡選擇mysql5.5版本的msi文件格式。

1.2 啟動和關閉MySQL服務

可以在「我的電腦-管理」找到MySQL服務，對其進行啟動和關閉。也可以在windows的cmd里輸入「net start mysql」/「net stop mysql」來啟動/關閉mysql服務。可能會碰到錯誤，這裡是許可權不足，需要修改許可權。

1.3 登錄和退出

mysql -uroot -p -P3306 -h127.0.0.1 /* 登錄 */n

「root」為mysql用戶名，「3306」為埠號，「127.0.0.1」為伺服器地址，回車後會提示輸入密碼。登錄成功後就可以操作資料庫了。

exit; /* 退出 */nquit; /* 退出 */nq; /* 退出 */n

輸入以上3個語句中任意一個即可退出MySQL。

1.4 MySQL語句規範

關鍵字與函數名稱要大寫；
資料庫名稱、表名稱、欄位名稱全部小寫；
SQL語句必須以分號結尾。

1.5 創建資料庫

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;n

另外，可以用

SHOW {DATABASES | SCHEMAS} [LIKE pattern | WHERE expr];n

來查看當前伺服器下的資料庫列表。

1.6 修改資料庫

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;n

1.7 刪除資料庫

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;n

二、數據類型與操作數據表

2.1 數據類型

下圖是MySQL的數據類型匯總。

截圖自菜鳥教程

2.2 連接資料庫

USE db_name; /* 打開資料庫 */nSELECT DATABASE(); /* 顯示當前打開的資料庫 */n

2.3 創建數據表

# 格式nCREATE TABLE [IF NOT EXISTS] table_name(n column_name1 data_type1, /* 如果希望某欄位為非空，可以加上「NOT NULL」限定 */n column_name2 data_type2,n ...n);nn# 例nCREATE TABLE IF NOT EXISTS tb1(n username VARCHAR(20),n age TINYINT UNSIGNED,n salary FLOAT(8,2) UNSIGNEDn);n

2.4 主鍵約束

每張數據表只能存在一個主鍵KEY，可添加「PRIMARY KEY」或「KEY」，主鍵默認NOT NULL。如果希望主鍵可自動編號，則可加上「AUTO_INCREMENT」。默認情況下，自動編號的起始值為1，自動增量也為1。

2.5 唯一約束

唯一約束UNIQUE KEY，也可以保證記錄的唯一性，但和主鍵不同的是，唯一約束的欄位可以為NULL，同時每張數據表可以存在多個UNIQUE KEY。

2.6 默認約束

默認約束DEFAULT，當插入記錄時，如果沒有明確為欄位賦值，則系統自動賦予默認值。

2.7 查看數據表

SHOW TABLES [FROM db_name] [LIKE pattern | WHERE expr]; /* 查看某個資料庫的數據表列表 */ nSHOW COLUMNS FROM table_name; /* 查看數據表結構 */n

2.8 插入記錄

INSERT [INTO] table_name [(col_name,...)] VALUES (val,...);n

三、約束以及修改數據表

這裡繼續延伸約束的概念。

約束分為表級約束和列級約束（列級約束即只針對某一個欄位），按功能劃分其類型包括：

主鍵約束（PRIMARY KEY）
唯一約束（UNIQUE KEY）
非空約束（NOT NULL）
默認約束（DEFAULT）
外鍵約束（FOREIGN KEY）

3.1 外鍵約束

父表和子表必須使用相同的引擎，且引擎只能為INNODB。修改引擎可在my.ini文件中修改：default-storage-engine=INNODB；
外鍵列和參照列必須具有相似的數據類型，其中數字的長度及有無符號必須相同，而字元長度則可以不同；
外鍵列和參照列必須創建索引。參數列必須顯式地創建索引，外鍵列沒有索引，MySQL將會自動隱式地創建索引。

# 父表nCREATE TABLE province(n id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, /* 參照列 */n pname VARCHAR(20) NOT NULLn );nn# 子表nCREATE TABLE users(n id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,n username VARCHAR(10) NOT NULL,n pid SMALLINT UNSIGNED, /* 外鍵列，外鍵列和參照列必須具有相似的數據類型 */n FOREIGN KEY (pid) REFERENCES province (id)n );n

3.2 外鍵約束的參照操作

CASCADE：父表的刪除或更新操作會使得子表中匹配的行也自動進行刪除或更新；
SET NULL：父表的刪除或更新操作會使得子表中的外鍵列為NULL，並且前提是外鍵列沒有指定為NOT NULL；
RESTRICT：拒絕對父表的刪除或更新操作；
NO ACTION：標準SQL關鍵字，在MySQL中與RESTRICT相同。

# 例nCREATE TABLE user3(n id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,n username VARCHAR(10) NOT NULL,n pid SMALLINT UNSIGNED,n FOREIGN KEY (pid) REFERENCES province (id) ON DELETE CASCADEn);n

3.3 表級約束與列級約束

表級約束：對多個數據列建立的約束，只能在列定義後聲明；
列級約束：對單個數據列建立的約束，可在列定義時聲明，也可在列定義後聲明。在實際項目中，列級約束用得更多。NOT NULL和DEFAULT約束不存在表級約束，只有列級約束。

3.4 修改數據表

添加列：

# 添加單列nALTER TABLE table_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];n/* 例： */ ALTER TABLE user2 ADD age TINYINT UNSIGNED NOT NULL;nn# 添加多列nALTER TABLE table_name ADD [COLUMN] (col_name column_definition,...);n/* 例： */ ALTER TABLE user2 ADD (truename VARCHAR(20) NOT NULL,salary SMALLINT UNSIGNED NOT NULL);nn# 刪除列nALTER TABLE table_name DROP [COLUMN] col_name;n/* 例： */ ALTER TABLE user2 DROP salary;n

添加約束：

# 添加主鍵約束nALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...);n/* 例： */ ALTER TABLE user2 ADD CONSTRAINT PK_user5_id PRIMARY KEY(id);nn# 添加唯一約束nALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,...);n/* 例： */ ALTER TABLE user2 ADD UNIQUE (username);nn# 添加外鍵約束nALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition;n/* 例： */ ALTER TABLE user2 ADD FOREIGN KEY (pid) REFERENCES province (id);nn# 添加/刪除默認約束nALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};n/* 例： */ ALTER TABLE user2 ALTER age SET DEFAULT 18;n

刪除約束：

# 刪除主鍵約束nALTER TABLE table_name DROP PRIMARY KEY;n/* 例： */ ALTER TABLE user2 DROP PRIMARY KEY;nn# 刪除唯一約束nALTER TABLE table_name DROP {INDEX | KEY} index_name;n/* 例： */ ALTER TABLE user2 DROP INDEX username;nn# 刪除外鍵約束nALTER TABLE table_name DROP FOREIGN KEY fk_symbol;n/* 例： */ ALTER TABLE user5 DROP FOREIGN KEY user5_ibfk_1; /* user5_ibfk_1為MySQL給的外鍵別名，可通過 SHOW CREATE TABLE table_name; 查詢 */n

修改列定義和更名數據表：

# 修改列定義nALTER TABLE table_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];n/* 例： */ ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;nn# 修改列名稱nALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];n/* 例： */ ALTER TABLE user2 CHANGE age userage TINYINT UNSIGNED;nn# 數據表更名nALTER TABLE table_name RENAME [TO | AS] new_table_name; /* 或 */ RENAME TABLE table_name1 TO new_table_name1 [,table_name2 TO new_table_name2];n/* 例： */ ALTER TABLE user2 RENAME user22; /* 或 */ RENAME TABLE user22 TO user2;n

四、操作數據表中的記錄

4.1 插入記錄

INSERT [INTO] table_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...;n

可插入多條記錄，可用表達式（expr）賦值，可用默認值（DEFAULT）賦值。插入新記錄時，如果不指定欄位名，就必須把所有欄位值都寫入，但如果主鍵欄位是自動自增的，那麼只要在主鍵欄位處寫NULL或DEFAULT即可，但主鍵不能為NOT NULL。

INSERT [INTO] table_name SET col_name = {expr | DEFAULT},...;n

與上一種插入方式的區別是，此方式可以使用子查詢（SubQuery）。

INSERT [INTO] table_name [(col_name,...)] SELECT ...;n

此方法可將查詢結果插入到指定數據表。後面這兩種方式會在5.1 INSERT...SELECT中講解。

如果在增刪改查記錄時出現亂碼，可使用：

SET NAMES charset_name;n

來修改客戶端的字元編碼。此操作只會更改客戶端字元編碼，不會修改伺服器資料庫的字元編碼。

4.2 單表更新記錄

UPDATE [LOW_PRIORITY] [IGNORE] table_name SET col_name1 = {expr1 | DEFAULT} [,col_name2 = {expr2 | DEFAULT}]...[WHERE where_condition];n/* 例： */ UPDATE user2 SET age = 19 WHERE id = 2;n

4.3 單表刪除記錄

DELETE FROM table_name [WHERE where_condition];n/* 例： */ DELETE FROM user2 WHERE id = 3;n

4.4 查詢表達式

SELECT select_expr1[,select_expr2,...]n[n FROM table_namen [WHERE where_condition]n [GROUP BY {col_name | position} [ASC | DESC],...]n [HAVING where_condition]n [ORDER BY {col_name | expr | position} [ASC | DESC],...]n [LIMIT {[offset,] row_count | row_count OFFSET offset}]n];n

查詢時可以用AS給需要查詢的欄位設置別名，那麼查詢結果集里將會顯示別名作為欄位名。

/* 例： */ SELECT id AS userid,age AS userage FROM user2;n

可以使用table_name.col_name的格式選擇某數據表的某列。

4.5 GROUP BY分組

[GROUP BY {col_name | position} [ASC | DESC],...];n/* 例： */ SELECT truename,age FROM user2 GROUP BY age ASC;n

另外，可以用HAVING關鍵字進行條件分組。

[HAVING where_condition]n/* 例： */ SELECT truename,age FROM user2 GROUP BY age ASC HAVING age >= 18;n

有一點需要注意，HAVING後的條件欄位必須出現在SELECT後所要查詢的欄位里，但如果HAVING後為聚合函數作為條件，則可忽略。

4.6 查詢結果排序ORDER BY

[ORDER BY {col_name | expr | position} [ASC | DESC],...]n

和GROUP BY的區別在於，GROUP BY查詢的結果集中會去掉重複值，而且GROUP BY有一個原則，就是SELECT後面的所有列中，沒有使用聚合函數的列，必須出現在GROUP BY後面。

另外，去除重複值，一般會在查詢欄位前加關鍵詞DISTINCT，以返回唯一不同的值。

SELECT DISTINCT Company FROM Orders;n

4.7 限制查詢數量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]n/* 例： */ SELECT truename,age FROM user2 ORDER BY age ASC LIMIT 3,4; /* 從查詢結果中的第3行開始（從0開始計數），共返回4行 */n

五、子查詢與連接

# 例：nSELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT AVG(goods_price) FROM tdb_goods);n

子查詢的結果作為上一層查詢的條件。可使用IN()/NOT IN()、ANY()、SOME()、ALL()等操作符和比較運算符搭配使用。

# 例：nSELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = 台式機);n

5.1 INSERT...SELECT

INSERT [INTO] table_name [(col_name,...)] SELECT ...;n/* 例： */ INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;n

5.2 INNER JOIN內連接

UPDATE table_name SET col_name1 = {expr1 | DEFAULT} [,col_name2 = {expr2 | DEFAULT}] ... [WHERE where_condition]n/* 例： */ UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;n

ON後跟著連接條件。

所謂內連接，即兩張表產生交集的部分，僅顯示符合連接條件的記錄。

當兩張表所要查詢的欄位名相同時，必須要給各欄位加上別名，或者採用「table_name.col_name」的形式區分。

/* 例： */nSELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;n

5.3 LEFT/RIGHT [OUTER] JOIN左外連接/右外連接

顯示左/右表的全部和右/左表中符合連接條件的記錄。

/* 例： */nSELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;n

即這個例子將顯示tdb_goods表中的全部記錄和tdb_goods_cates表中符合條件的記錄。右表反之。

5.4 多表連接查詢

/* 例： */nSELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS a INNER JOIN tdb_goods_cates AS b ON a.cate_id = b.cate_id INNER JOIN tdb_goods_brands AS c ON a.brand_id = c.brand_id;n

該例子連接了三張表進行查詢。

5.5 無限級分類表設計

使用自身連接的方式對同一個數據表進行連接查詢。

/* 例： */nSELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;n

數據表tdb_goods_types中記錄著每種類別的名稱和其父類別id（其實該父類別id就是type_id），通過自身連接的方式可查詢到每種類別的名稱和其對應的父類別id。

5.6 多表刪除

/* 例： */nDELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;n

該例子使用子查詢找出需要刪除的記錄，將子查詢結果集和本身的表外連接進行刪除。

六、運算符和函數

6.1 字元函數

CONCAT(str1, str2, ...)：字元連接；
CONCAT_WS(separator, str1, str2, ...)：使用指定的分隔符進行字元連接；
FORMAT(x, d)：x為某數字，d為小數位；
LOWER(str)：將字元串轉化為小寫字母；
UPPER(str)：將字元串轉化為大寫字母；
LEFT(str, len)：返回指定長度的字元串的左側部分；
RIGHT(str, len)：返回指定長度的字元串的右側部分；
MID(str, pos[, len])：返回str里從pos位置開始，長度為len的字元串部分；
SUBSTRING(str, pos, len)：返回str里從pos位置開始，長度為len的字元串部分；
LENGTH(str)：返回字元串str的長度，空格也會一起計算長度，以位元組為單位；
LTRIM(str)：刪除前導空格；
RTRIM(str)：刪除後續空格；
TRIM([{BOTH | LEADING | TRAILING} [removed_str]] FROM str)：刪除前後綴不需要的字元；
[NOT]LIKE()：與通配符一起使用選擇數據。MySQL提供兩個通配符（%和_），其中，「%」用於匹配任何字元串，「_」用於匹配任何單個字元。如果需要匹配的字元本身就是通配符，可使用ESCAPE；
REPLACE(str, old_str, new_str)：將str字元串里的old_str字元串部分替換為new_str；
LOCATE(substr,str)：返回子符串substr在字元串str的第一個出現的位置；
LOCATE(substr,str,pos)：返回子符串substr在字元串str，從pos處開始的第一次出現的位置。

/* LIKE()舉例： */nSELECT * FROM test WHERE first_name LIKE %1%% ESCAPE 1; /* 不將「1」後的「%」認為是通配符 */n

6.2 數值運算

CEIL(x)：返回大於x的最小整數值；
DIV：整數除法，即結果中只保留整數部分；
FLOOR(x)：返回小於x的最大整數值；
MOD：取余；
POWER(x, y)：冪運算，即x的y次方；
ROUND(x, y)：四捨五入，即將數值x四捨五入為y位小數。
TRUNCATE(x, y)：數字截取，將數值x保留y位小數（不進行四捨五入）。

6.3 比較運算

[NOT]BETWEEN...AND...：【不】在範圍之內；
[NOT]IN()：【不】在列出值範圍內；
IS[NOT]NULL：【非】空。

6.4 日期時間函數

NOW()：當前日期和時間；
DATE(date)：返回日期；
CURDATE()：當前日期；
CURTIME()：當前時間；
YEAR(date)：返回date中的年份；
MONTH(date)：返回date中的月份；
DAY(date)：返回date的中的日；
DATE_ADD(date, INTERVAL expr type)：日期加減。type類型可以是DAY、WEEK、MONTH、YEAR等。例：SELECT DATE_ADD(2017-11-20, INTERVAL 2 DAY);
DATEDIFF()：返回兩個日期之間的天數；
DATE_FORMAT(date, format)：日期時間格式化。例如%d、%m、%Y等。

6.5 信息函數

CONNECTION_ID()：返回資料庫的連接次數；
DATABASE()：當前資料庫；
LAST_INSERT_ID()：最後插入記錄的id；
USER()：當前用戶；
VERSION()：版本信息。

6.6 聚合函數

AVG()：求平均值；
COUNT()：計數；
MAX()：求最大值；
MIN()：求最小值；
SUM()：求和。

6.7 加密函數

MD5()：信息摘要演算法；
PASSWORD()：密碼演算法。

七、自定義函數

7.1 創建不帶參數的自定義函數

CREATE FUNCTION function_name()nRETURNS typenroutine_bodynn/* 例： */nCREATE FUNCTION f1()nRETURNS VARCHAR(30)nRETURN DATE_FORMATE(NOW(),%Y年%m月%d日 %H點:%i分:%s秒);n

7.2 創建帶有參數的自定義函數

CREATE FUNCTION function_name(parameter1 [,parameter2,...])nRETURNS typenroutine_bodynn/* 例： */nCREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)nRETURNS FLOAT(10,2) UNSIGNEDnRETURN (num1 + num2) / 2;n

7.3 創建具有複合結構函數體的自定義函數

CREATE FUNCTION function_name(parameter1 [,parameter2,...])nRETURNS typenBEGINnroutine_bodynENDn//nn/* 例： */nCREATE FUNCTION addprovince(province_name VARCHAR(20))nRETURNS INT UNSIGNEDnBEGINnINSERT province (pname) VALUES (province_name);nRETURN LAST_INSERT_ID();nENDn//n

涉及到複合結構的自定義函數，需要用BEGIN...END來包含函數體。有一點需要說明，函數體的sql語句結尾會有「;」，系統會認為這是結束符，而停止函數創建。所以需要在創建函數時暫時修改結束符，函數創建結束後再改回來。

例如修改結束符為「//」，則應使用「DELIMITER //」語句來完成。

此外，如果有不需要再使用的函數，則可將其刪除。

DROP FUNCTION [IF EXISTS] function_name;n

八、MySQL存儲過程

存儲過程可以返回多個值，並且為獨立執行，而函數只能返回一個值。

8.1 創建不帶參數的存儲過程

CREATE [DEFINER = {user | CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[,...]])n[characteristic ...] routine_bodynn/* 例： */ CREATE PROCEDURE sp1() SELECT VERSION();n

調用存儲過程，用「CALL sp_name」。

8.2 創建帶有IN類型參數的存儲過程

IN：表示該參數的值必須在調用存儲過程時指定，在存儲過程中修改該參數的值不能被返回，為默認值。

/* 例： */nCREATE PROCEDURE removeProvinceById(IN pid INT UNSIGNED)nBEGINnDELETE FROM province WHERE id = pid;nENDn//n

8.3 創建帶有IN和OUT類型參數的存儲過程

OUT：該值可在存儲過程內部被改變，並可返回。

/* 例： */nCREATE PROCEDURE removeUserAndReturnUserNums(IN pid INT UNSIGNED, OUT userNums INT UNSIGNED)nBEGINnDELETE FROM province WHERE id = pid;nSELECT count(id) FROM province INTO userNums;nENDn//nnCALL removeUserAndReturnUserNums(12, @nums); /* 調用存儲過程 */nSELECT @nums; /* 顯示OUT參數的值 */n

調用時，OUT類型的參數用「@參數」的形式，執行成功後，再用「SELECT @參數;」輸出userNums的值。

8.4 創建帶有多個OUT類型參數的存儲過程

CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED, OUT deleteUsersNum SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED)nBEGINnDELETE FROM user2 WHERE age = p_age;nSELECT ROW_COUNT() INTO deleteUsersNum;nSELECT COUNT(id) FROM user2 INTO userCounts;nENDn//n

ROW_COUNT()可以得到插入、刪除或者更新的記錄總數。

九、MySQL存儲引擎

SHOW CREATE TABLE table_name; /* 查看數據表的創建命令 */n

上面的sql語句可以查看數據表的創建命令，結果會顯示出某數據表的存儲引擎。

MySQL的存儲引擎主要有：MyISAM、InnoDB、Memory、CSV和Archive等。

MySQL各存儲引擎特點

9.1 並發處理

當多個連接對記錄進行修改時保證數據的一致性和完整性。

共享鎖（讀鎖）：在同一時間段內，多個用戶可以讀取同一個資源，讀取過程中數據不會發生任何變化；
排他鎖（寫鎖）：在任何時候只能有一個用戶寫入資源，當進行寫鎖時會阻塞其他的讀鎖或者寫鎖操作。

9.2 事務處理

用於保證資料庫的完整性。

特性：

原子性
一致性
隔離性
持久性

9.3 外鍵和索引

外鍵：保證數據一致性的策略；
索引：對數據表中的一列或多列的值進行排序的一種結構。

十、MySQL圖形化管理工具

phpMyadmin、Navicat、MySQL Workbench。

文章首發於知乎，禁止轉載。