MySQL中常用SQL語句的編寫

weixin_33727510發表於2017-09-14
MySql

簡述

之前一直使用的django的orm模型，最近想學習下原生sql語句的編寫。以後工作中可能不使用django，為了更好的工作和提高自己的知識全面點，記錄下常用的sql語句編寫。

一、建立、刪除、選擇資料庫

1. 如果資料庫不存在就建立

CREATE DATABASE IF NOT EXISTS blog CHARACTER SET utf8 COLLATE utf8_general_ci;

2. 如果資料庫存在就刪除

DROP DATABASE IF EXISTS blog;

3. 切換到我們選擇的資料庫，並檢視庫中所有表

USE blog;
SHOW TABLES;

4. 資料庫授權使用者建立

grant all on blog.* to blog@'%' identified by '123456';

5. 檢視資料庫結構

SHOW DATABASES LIKE 'blog%';

6. 查詢授權使用者

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

7. 修改資料庫使用者密碼

USE mysql;
UPDATE USER SET PASSWORD = PASSWORD ("new-password") WHERE USER = "root";
FLUSH PRIVILEGES;

二、MySQL資料型別介紹

MySQL 資料型別

MySQL中定義資料欄位的型別對你資料庫的優化是非常重要的。
MySQL支援多種型別，大致可以分為三類：數值、日期/時間和字串(字元)型別。

數值型別

MySQL支援所有標準SQL數值資料型別。
這些型別包括嚴格數值資料型別(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值資料型別(FLOAT、REAL和DOUBLE PRECISION)。
關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。
BIT資料型別儲存位欄位值，並且支援MyISAM、MEMORY、InnoDB和BDB表。
作為SQL標準的擴充套件，MySQL也支援整數型別TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數型別的儲存和範圍。

型別 大小 範圍（有符號） 範圍（無符號） 用途
TINYINT 1 位元組 (-128，127) (0，255) 小整數值
SMALLINT 2 位元組 (-32 768，32 767) (0，65 535) 大整數值
MEDIUMINT 3 位元組 (-8 388 608，8 388 607) (0，16 777 215) 大整數值
INT或INTEGER 4 位元組 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整數值
BIGINT 8 位元組 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 極大整數值
FLOAT 4 位元組 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 單精度
浮點數值
DOUBLE 8 位元組 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 雙精度
浮點數值
DECIMAL 對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2 依賴於M和D的值 依賴於M和D的值 小數值

日期和時間型別

表示時間值的日期和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每個時間型別有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。

型別 大小（位元組） 範圍 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 時間值或持續時間
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和時間值
TIMESTAMP 4 1970-01-01 00:00:00/2037 年某時 YYYYMMDD HHMMSS 混合日期和時間值，時間戳

字串型別

字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些型別如何工作以及如何在查詢中使用這些型別。

型別 大小 用途
CHAR 0-255位元組 定長字串
VARCHAR 0-65535 位元組 變長字串
TINYBLOB 0-255位元組 不超過 255 個字元的二進位制字串
TINYTEXT 0-255位元組 短文字字串
BLOB 0-65 535位元組 二進位制形式的長文字資料
TEXT 0-65 535位元組 長文字資料
MEDIUMBLOB 0-16 777 215位元組 二進位制形式的中等長度文字資料
MEDIUMTEXT 0-16 777 215位元組 中等長度文字資料
LONGBLOB 0-4 294 967 295位元組 二進位制形式的極大文字資料
LONGTEXT 0-4 294 967 295位元組 極大文字資料

CHAR和VARCHAR型別類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在儲存或檢索過程中不進行大小寫轉換。
BINARY和VARBINARY類類似於CHAR和VARCHAR，不同的是它們包含二進位制字串而不要非二進位制字串。也就是說，它們包含位元組字串而不是字元字串。這說明它們沒有字符集，並且排序和比較基於列值位元組的數值值。
BLOB是一個二進位制大物件，可以容納可變數量的資料。有4種BLOB型別：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同。
有4種TEXT型別：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB型別，有相同的最大長度和儲存需求。

三、MySQL資料的各種騷操作

1. 建立資料表

建立MySQL資料表需要以下資訊：
表名
表欄位名
定義每個表欄位

語法
以下為建立MySQL資料表的SQL通用語法：

CREATE TABLE table_name (column_name column_type);

以下例子中我們將在blog資料庫中建立資料表author、article、tag以及article和tag的關聯表article_tag
建立author表：

CREATE TABLE IF NOT EXISTS `author`(
   `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '作者ID',
   `name` VARCHAR(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '作者名字',
   `qq` BIGINT(20) NULL DEFAULT NULL COMMENT '作者QQ',
   `phone` BIGINT(20) NULL DEFAULT NULL COMMENT '作者電話',
   PRIMARY KEY ( `id` ),
   INDEX `name` (`name`) USING BTREE,
   UNIQUE INDEX `phone` (`phone`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=DYNAMIC
;

檢視author表結構：

mysql> desc author;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40) | YES  | MUL | NULL    |                |
| qq    | bigint(20)  | YES  |     | NULL    |                |
| phone | bigint(20)  | YES  | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

建立article表：

CREATE TABLE IF NOT EXISTS `article`(
   `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '文章ID',
   `title` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文章標題',
   `content` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文章內容',
   `author_id` INT(11) NOT NULL COMMENT '作者ID',
   `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '釋出時間',
   PRIMARY KEY ( `id` ),
   FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
   UNIQUE INDEX `author_id` (`author_id`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=DYNAMIC
;

檢視article表結構：

mysql> desc article;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| title       | varchar(100) | YES  |     | NULL    |                |
| content     | text         | YES  |     | NULL    |                |
| author_id   | int(11)      | NO   | MUL | NULL    |                |
| create_time | date         | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

建立tag表：

CREATE TABLE IF NOT EXISTS `tag`(
   `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '標籤ID',
   `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '標籤名稱',
   PRIMARY KEY ( `id` )
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=DYNAMIC
;

檢視tag表結構：

mysql> desc tag;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

建立article_tag表：

CREATE TABLE IF NOT EXISTS `article_tag`(
   `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '文章標籤關聯表ID',
   `article_id` INT(11) NOT NULL COMMENT '文章ID',
   `tag_id` INT(11) NOT NULL COMMENT '標籤ID',
   PRIMARY KEY ( `id` ),
   FOREIGN KEY (`article_id`) REFERENCES `article` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
   FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
   UNIQUE INDEX `article_tag_unique` (`article_id`, `tag_id`) USING BTREE ,
   INDEX `article_id` (`article_id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=DYNAMIC
;

檢視article_tag表結構：

mysql> desc article_tag;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| article_id | int(11) | NO   | MUL | NULL    |                |
| tag_id     | int(11) | NO   | MUL | NULL    |                |
+------------+---------+------+-----+---------+----------------+

語句解析：
如果你不想欄位為 NULL 可以設定欄位的屬性為 NOT NULL， 在運算元據庫時如果輸入該欄位的資料為NULL ，就會報錯。
AUTO_INCREMENT定義列為自增的屬性，一般用於主鍵，數值會自動加1。
PRIMARY KEY關鍵字用於定義列為主鍵。 您可以使用多列來定義主鍵，列間以逗號分隔。
ENGINE 設定儲存引擎， CHARACTER SET設定編碼。
INDEX設定該欄位為索引，UNIQUE INDEX設定欄位值唯一，並且設定該欄位為索引。
COMMENT給該欄位新增註釋。

ROW_FORMAT=DYNAMIC，設定表為動態表（在mysql中， 若一張表裡面不存在varchar、text以及其變形、blob以及其變形的欄位的話，那麼張這個表其實也叫靜態表，即該表的row_format是fixed，就是說每條記錄所佔用的位元組一樣。其優點讀取快，缺點浪費額外一部分空間。 若一張表裡面存在varchar、text以及其變形、blob以及其變形的欄位的話，那麼張這個表其實也叫動態表，即該表的row_format是dynamic，就是說每條記錄所佔用的位元組是動態的。其優點節省空間，缺點增加讀取的時間開銷。所以，做搜尋查詢量大的表一般都以空間來換取時間，設計成靜態表）。

資料表解析：
author是作者表，有4個欄位：id, name, qq, phone
article是文章表，文章和作者是多對一的關係，這裡使用外來鍵方式關聯。欄位author_id關聯的是author的id欄位。
tag是標籤表，有2個欄位：id, name
文章和標籤是多對多的關係（ManyToMany），這裡使用第三張表article_tag把它們關聯起來。欄位article_id外來鍵關聯的是article的id欄位，欄位tag_id外來鍵關聯的是tag的id欄位。

2. 刪除資料表

DROP TABLE IF EXISTS article_tag;

3. 插入資料
MySQL 表中使用 INSERT INTO SQL語句來插入資料。
你可以通過 mysql> 命令提示視窗中向資料表中插入資料，或者通過PHP指令碼來插入資料。

語法
以下為向MySQL資料表插入資料通用的 INSERT INTO SQL語法：

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

如果資料是字元型，必須使用單引號或者雙引號，如："value"。

author表插入幾條資料：

INSERT INTO author(name, qq, phone) VALUES('君惜', 123456, 18500178899), ('糖糖', 234567, 13256987582), ('琳琳', 345678, 15636589521);

檢視author表：

mysql> SELECT * FROM author;
+----+------+--------+-------------+
| id | name | qq     | phone       |
+----+------+--------+-------------+
|  1 | 君惜 | 123456 | 18500178899 |
|  2 | 糖糖 | 234567 | 13256987582 |
|  3 | 琳琳 | 345678 | 15636589521 |
+----+------+--------+-------------+

article表插入幾條資料：

INSERT INTO article(title, content, author_id) VALUES('流暢的python', 'Python各種拽', 1), ('嘻哈', '中國有嘻哈', 2), ('嚴肅', '你這輩子就是吃了太嚴肅的虧', 3);

檢視article表：

mysql> select * from article;
+----+--------------+----------------------------+-----------+---------------------+
| id | title        | content                    | author_id | create_time         |
+----+--------------+----------------------------+-----------+---------------------+
|  1 | 流暢的python | Python各種拽               |         1 | 2017-09-12 16:36:43 |
|  2 | 嘻哈         | 中國有嘻哈                 |         2 | 2017-09-12 16:36:43 |
|  3 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 |         3 | 2017-09-12 16:36:43 |
+----+--------------+----------------------------+-----------+---------------------+

tag表插入資料：

INSERT INTO tag(name) VALUES('技術'), ('娛樂'), ('文學');

檢視tag表：

mysql> select * from tag;
+----+------+
| id | name |
+----+------+
|  1 | 技術 |
|  2 | 娛樂 |
|  3 | 文學 |
+----+------+

article_tag表插入資料：

INSERT INTO article_tag(article_id, tag_id) VALUES(1, 1), (2, 2), (3, 3);

檢視article_tag表：

mysql> select * from article_tag;
+----+------------+--------+
| id | article_id | tag_id |
+----+------------+--------+
|  1 |          1 |      1 |
|  2 |          2 |      2 |
|  3 |          3 |      3 |
+----+------------+--------+

4. 查詢資料
MySQL 資料庫使用SQL SELECT語句來查詢資料。
你可以通過 mysql> 命令提示視窗中在資料庫中查詢資料，或者通過PHP指令碼來查詢資料。

語法
以下為在MySQL資料庫中查詢資料通用的 SELECT 語法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]
  • 查詢語句中你可以使用一個或者多個表，表之間使用逗號(,)分割，並使用WHERE語句來設定查詢條件。
  • SELECT 命令可以讀取一條或者多條記錄。
  • 你可以使用星號（*）來代替其他欄位，SELECT語句會返回表的所有欄位資料
  • 你可以使用 WHERE 語句來包含任何條件。
  • 你可以通過OFFSET指定SELECT語句開始查詢的資料偏移量。預設情況下偏移量為0。
  • 你可以使用 LIMIT 屬性來設定返回的記錄數。

例項
以下例項將返回資料表article的所有記錄

mysql> select * from article;
+----+--------------+----------------------------+-----------+---------------------+
| id | title        | content                    | author_id | create_time         |
+----+--------------+----------------------------+-----------+---------------------+
|  1 | 流暢的python | Python各種拽               |         1 | 2017-09-12 16:36:43 |
|  2 | 嘻哈         | 中國有嘻哈                 |         2 | 2017-09-12 16:36:43 |
|  3 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 |         3 | 2017-09-12 16:36:43 |
+----+--------------+----------------------------+-----------+---------------------+

查詢指定欄位資料

mysql> select title, content from article;
+--------------+----------------------------+
| title        | content                    |
+--------------+----------------------------+
| 流暢的python | Python各種拽               |
| 嘻哈         | 中國有嘻哈                 |
| 嚴肅         | 你這輩子就是吃了太嚴肅的虧 |
+--------------+----------------------------+

5. WHERE 子句
我們知道從 MySQL 表中使用 SQL SELECT 語句來讀取資料。
如需有條件地從表中選取資料，可將 WHERE 子句新增到 SELECT 語句中。

語法
以下是 SQL SELECT 語句使用 WHERE 子句從資料表中讀取資料的通用語法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
  • 查詢語句中你可以使用一個或者多個表，表之間使用逗號, 分割，並使用WHERE語句來設定查詢條件。
  • 你可以在 WHERE 子句中指定任何條件。
  • 你可以使用 AND 或者 OR 指定一個或多個條件。
  • WHERE 子句也可以運用於 SQL 的 DELETE 或者 UPDATE 命令。
  • WHERE 子句類似於程式語言中的 if 條件，根據 MySQL 表中的欄位值來讀取指定的資料。

以下為操作符列表，可用於 WHERE 子句中。
下表中例項假定 A 為 10, B 為 20

操作符 描述 例項
= 等號，檢測兩個值是否相等，如果相等返回true (A = B) 返回false。
<>, != 不等於，檢測兩個值是否相等，如果不相等返回true (A != B) 返回 true。
> 大於號，檢測左邊的值是否大於右邊的值, 如果左邊的值大於右邊的值返回true (A > B) 返回false。
< 小於號，檢測左邊的值是否小於右邊的值, 如果左邊的值小於右邊的值返回true (A < B) 返回 true。
>= 大於等於號，檢測左邊的值是否大於或等於右邊的值, 如果左邊的值大於或等於右邊的值返回true (A >= B) 返回false。
<= 小於等於號，檢測左邊的值是否小於於或等於右邊的值, 如果左邊的值小於或等於右邊的值返回true (A <= B) 返回 true。

如果我們想再 MySQL 資料表中讀取指定的資料，WHERE 子句是非常有用的。
使用主鍵來作為 WHERE 子句的條件查詢是非常快速的。
如果給定的條件在表中沒有任何匹配的記錄，那麼查詢不會返回任何資料。

例項
以下例項將讀取article表中title欄位值為 嘻哈 的所有記錄：

mysql> select * from article where title="嘻哈";
+----+-------+------------+-----------+---------------------+
| id | title | content    | author_id | create_time         |
+----+-------+------------+-----------+---------------------+
|  2 | 嘻哈  | 中國有嘻哈 |         2 | 2017-09-12 16:36:43 |
+----+-------+------------+-----------+---------------------+

6. UPDATE 語句
如果我們需要修改或更新 MySQL 中的資料，我們可以使用 SQL UPDATE 命令來操作。

語法
以下是 UPDATE 命令修改 MySQL 資料表資料的通用 SQL 語法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
  • 你可以同時更新一個或多個欄位。
  • 你可以在 WHERE 子句中指定任何條件。
  • 你可以在一個單獨表中同時更新資料。

當你需要更新資料表中指定行的資料時 WHERE 子句是非常有用的。

通過命令提示符更新資料
以下我們將在 SQL UPDATE 命令使用 WHERE 子句來更新 author 表中指定的資料：
例項
以下例項將更新資料表中 id 為 1 的 qq 欄位值：

mysql> update author set qq='2298630081' where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from author where id=1;
+----+------+------------+-------------+
| id | name | qq         | phone       |
+----+------+------------+-------------+
|  1 | 君惜 | 2298630081 | 18500178899 |
+----+------+------------+-------------+
1 row in set (0.00 sec)

7. DELETE 語句
你可以使用 SQL 的 DELETE FROM 命令來刪除 MySQL 資料表中的記錄。
你可以在 mysql> 命令提示符或 PHP 指令碼中執行該命令。

語法
以下是 SQL DELETE 語句從 MySQL 資料表中刪除資料的通用語法：

DELETE FROM table_name [WHERE Clause]
  • 如果沒有指定 WHERE 子句，MySQL 表中的所有記錄將被刪除。
  • 你可以在 WHERE 子句中指定任何條件
  • 您可以在單個表中一次性刪除記錄。

當你想刪除資料表中指定的記錄時 WHERE 子句是非常有用的。

例項
插入一條資料：

INSERT INTO author(name, qq, phone) VALUES('悅悅','456789','13343809438');

刪除 author 表中 name 為 悅悅 的記錄：

mysql> delete from author where name="悅悅";
Query OK, 1 row affected (0.01 sec)

mysql> select * from author;
+----+------+------------+-------------+
| id | name | qq         | phone       |
+----+------+------------+-------------+
|  1 | 君惜 | 2298630081 | 18500178899 |
|  2 | 糖糖 |     234567 | 13256987582 |
|  3 | 琳琳 |     345678 | 15636589521 |
+----+------+------------+-------------+
3 rows in set (0.00 sec)

8. LIKE 子句

我們知道在 MySQL 中使用 SQL SELECT 命令來讀取資料， 同時我們可以在 SELECT 語句中使用 WHERE 子句來獲取指定的記錄。
WHERE 子句中可以使用等號 = 來設定獲取資料的條件，如 "runoob_author = 'RUNOOB.COM'"。
但是有時候我們需要獲取 runoob_author 欄位含有 "COM" 字元的所有記錄，這時我們就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分號 %字元來表示任意字元，類似於UNIX或正規表示式中的星號 *。
如果沒有使用百分號 %, LIKE 子句與等號 = 的效果是一樣的。

語法
以下是 SQL SELECT 語句使用 LIKE 子句從資料表中讀取資料的通用語法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
  • 你可以在 WHERE 子句中指定任何條件。
  • 你可以在 WHERE 子句中使用LIKE子句。
  • 你可以使用LIKE子句代替等號 =。
  • LIKE 通常與 % 一同使用，類似於一個元字元的搜尋。
  • 你可以使用 AND 或者 OR 指定一個或多個條件。
  • 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句來指定條件。

例項
插入幾條資料：

insert into author(name, qq, phone) values('李天星', '5678911', '13345607861'), ('王星', '5678912', '13345607862'), ('張星星', '5678913', '13345607863');

查詢 author 表 name 欄位中以星為結尾的的所有記錄：

mysql> select * from author where name like '%星';
+----+--------+---------+-------------+
| id | name   | qq      | phone       |
+----+--------+---------+-------------+
|  5 | 李天星 | 5678911 | 13345607861 |
|  6 | 王星   | 5678912 | 13345607862 |
|  7 | 張星星 | 5678913 | 13345607863 |
+----+--------+---------+-------------+
3 rows in set (0.01 sec)

9. UNION 操作符
MySQL UNION 操作符用於連線兩個以上的 SELECT 語句的結果組合到一個結果集合中。多個 SELECT 語句會刪除重複的資料。

語法
MySQL UNION 操作符語法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

引數

  • expression1, expression2, ... expression_n: 要檢索的列。
  • tables: 要檢索的資料表。
  • WHERE conditions: 可選， 檢索條件。
  • DISTINCT: 可選，刪除結果集中重複的資料。預設情況下 UNION 操作符已經刪除了重複資料，所以 DISTINCT 修飾符對結果沒啥影響。
  • ALL: 可選，返回所有結果集，包含重複資料。

articles表

mysql> select * from articles;
+----+--------------+----------------------------+---------+
| id | title        | content                    | user_id |
+----+--------------+----------------------------+---------+
|  1 | 中國有嘻哈   | 哈哈哈                     |       1 |
|  2 | 星光大道     | 成名之路                   |       2 |
|  3 | 平凡的真諦   | 開心即完美                 |       3 |
|  4 | python進階   | Python高階用法             |       1 |
|  5 | 流暢的python | 就問你流暢不流暢            |       1 |
|  6 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 |       3 |
+----+--------------+----------------------------+---------+
6 rows in set (0.00 sec)

article表

mysql> select * from article;
+----+--------------+----------------------------+-----------+---------------------+
| id | title        | content                    | author_id | create_time         |
+----+--------------+----------------------------+-----------+---------------------+
|  1 | 流暢的python | Python各種拽               |         1 | 2017-09-12 16:36:43 |
|  2 | 嘻哈         | 中國有嘻哈                 |         2 | 2017-09-12 16:36:43 |
|  3 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 |         3 | 2017-09-12 16:36:43 |
+----+--------------+----------------------------+-----------+---------------------+
3 rows in set (0.00 sec)

SQL UNION 例項
下面的 SQL 語句從 article 和 articles 表中選取所有不同的title（只有不同的值）：

mysql> select title from article union select title from articles order by title;
+--------------+
| title        |
+--------------+
| python進階   |
| 嚴肅         |
| 中國有嘻哈   |
| 嘻哈         |
| 平凡的真諦   |
| 星光大道     |
| 流暢的python |
+--------------+
7 rows in set (0.00 sec)

註釋：UNION 不能用於列出兩個表中所有的title。如果出現重複的資料，只會列出一次。UNION 只會選取不同的值。請使用 UNION ALL 來選取重複的值！

SQL UNION ALL 例項
下面的 SQL 語句使用 UNION ALL 從 "article" 和 "articles" 表中選取所有的title（也有重複的值）：

mysql> select title from article union all select title from articles order by title;
+--------------+
| title        |
+--------------+
| python進階   |
| 嚴肅         |
| 嚴肅         |
| 中國有嘻哈   |
| 嘻哈         |
| 平凡的真諦   |
| 星光大道     |
| 流暢的python |
| 流暢的python |
+--------------+
9 rows in set (0.00 sec)

帶有 WHERE 的 SQL UNION ALL
下面的 SQL 語句使用 UNION ALL 從 "article" 和 "articles" 表中選取所標題(title)為流暢的python的書籍（也有重複的值）：

mysql> select title, content from article where title='流暢的python' union all select title, content from articles  where title='流暢的python' order by title;
+--------------+------------------+
| title        | content          |
+--------------+------------------+
| 流暢的python | Python各種拽     |
| 流暢的python | 就問你流暢不流暢 |
+--------------+------------------+
2 rows in set (0.00 sec)

10. 排序
我們知道從 MySQL 表中使用 SQL SELECT 語句來讀取資料。
如果我們需要對讀取的資料進行排序，我們就可以使用 MySQL 的 ORDER BY 子句來設定你想按哪個欄位哪種方式來進行排序，再返回搜尋結果。

語法
以下是 SQL SELECT 語句使用 ORDER BY 子句將查詢資料排序後再返回資料：

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
  • 你可以使用任何欄位來作為排序的條件，從而返回排序後的查詢結果。
  • 你可以設定多個欄位來排序。
  • 你可以使用 ASC 或 DESC 關鍵字來設定查詢結果是按升序或降序排列。 預設情況下，它是按升序排列。
  • 你可以新增 WHERE...LIKE 子句來設定條件。

例項
嘗試以下例項，結果將按升序及降序排列。

mysql> select * from article order by create_time asc;
+----+--------------+----------------------------+-----------+---------------------+
| id | title        | content                    | author_id | create_time         |
+----+--------------+----------------------------+-----------+---------------------+
|  1 | 流暢的python | Python各種拽               |         1 | 2017-09-12 16:36:41 |
|  2 | 嘻哈         | 中國有嘻哈                 |         2 | 2017-09-12 16:36:42 |
|  3 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 |         3 | 2017-09-12 16:36:43 |
+----+--------------+----------------------------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from article order by create_time desc;
+----+--------------+----------------------------+-----------+---------------------+
| id | title        | content                    | author_id | create_time         |
+----+--------------+----------------------------+-----------+---------------------+
|  3 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 |         3 | 2017-09-12 16:36:43 |
|  2 | 嘻哈         | 中國有嘻哈                 |         2 | 2017-09-12 16:36:42 |
|  1 | 流暢的python | Python各種拽               |         1 | 2017-09-12 16:36:41 |
+----+--------------+----------------------------+-----------+---------------------+
3 rows in set (0.00 sec)

11. GROUP BY 語句
GROUP BY 語句根據一個或多個列對結果集進行分組。
在分組的列上我們可以使用 COUNT, SUM, AVG,等函式。

GROUP BY 語法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

例項演示
例項使用到了以下表結構及資料，使用前我們可以先將以下資料匯入資料庫中。

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `user_login`
-- ----------------------------
DROP TABLE IF EXISTS `user_login`;
CREATE TABLE `user_login` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登入次數',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `user_login`
-- ----------------------------
BEGIN;
INSERT INTO `user_login` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小麗', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

匯入成功後，執行以下 SQL 語句：

mysql> select * from user_login;
+----+------+---------------------+--------+
| id | name | date                | singin |
+----+------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小麗 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+------+---------------------+--------+
6 rows in set (0.00 sec)

接下來我們使用 GROUP BY 語句 將資料表按名字進行分組，並統計每個人有多少條記錄：

mysql> select name, count(*) from user_login group by name;
+------+----------+
| name | count(*) |
+------+----------+
| 小麗 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+------+----------+
3 rows in set (0.00 sec)

使用 WITH ROLLUP
WITH ROLLUP 可以實現在分組統計資料基礎上再進行相同的統計（SUM,AVG,COUNT…）。
例如我們將以上的資料表按名字進行分組，再統計每個人登入的次數：

mysql> select name, sum(singin) as singin_count from user_login group by name with rollup;
+------+--------------+
| name | singin_count |
+------+--------------+
| 小麗 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL |           16 |
+------+--------------+
4 rows in set (0.00 sec)

其中記錄 NULL 表示所有人的登入次數。
我們可以使用 coalesce 來設定一個可以取代 NUll 的名稱，coalesce 語法：

select coalesce(a,b,c);

引數說明：如果a==null,則選擇b；如果b==null,則選擇c；如果a!=null,則選擇a；如果a b c 都為null ，則返回為null（沒意義）。

以下例項中如果名字為空我們使用總數代替：

mysql> select coalesce(name, '總數'), sum(singin) as singin_count from user_login group by name with rollup;
+------------------------+--------------+
| coalesce(name, '總數') | singin_count |
+------------------------+--------------+
| 小麗                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 總數                   |           16 |
+------------------------+--------------+
4 rows in set (0.00 sec)

12. 多表連查
在真正的應用中經常需要從多個資料表中讀取資料。下面將向大家介紹如何使用 MySQL 的 JOIN 在兩個或多個表中查詢資料。
你可以在 SELECT, UPDATE 和 DELETE 語句中使用 Mysql 的 JOIN 來聯合多表查詢。
JOIN 按照功能大致分為如下三類：

  • INNER JOIN（內連線,或等值連線）：獲取兩個表中欄位匹配關係的記錄。
  • LEFT JOIN（左連線）：獲取左表所有記錄，即使右表沒有對應匹配的記錄。
  • RIGHT JOIN（右連線）：與 LEFT JOIN 相反，用於獲取右表所有記錄，即使左表沒有對應匹配的記錄。

我們在blog資料庫中有四張表 author、article、tag、article_tag。資料表資料如下：

mysql> select * from author;
+----+--------+------------+-------------+
| id | name   | qq         | phone       |
+----+--------+------------+-------------+
|  1 | 君惜   | 2298630081 | 18500178899 |
|  2 | 糖糖   |     234567 | 13256987582 |
|  3 | 琳琳   |     345678 | 15636589521 |
|  5 | 李天星 |    5678911 | 13345607861 |
|  6 | 王星   |    5678912 | 13345607862 |
|  7 | 張星星 |    5678913 | 13345607863 |
+----+--------+------------+-------------+
6 rows in set (0.00 sec)

mysql> select * from article;
+----+--------------+----------------------------+-----------+---------------------+
| id | title        | content                    | author_id | create_time         |
+----+--------------+----------------------------+-----------+---------------------+
|  1 | 流暢的python | Python各種拽               |         1 | 2017-09-12 16:36:41 |
|  2 | 嘻哈         | 中國有嘻哈                 |         2 | 2017-09-12 16:36:42 |
|  3 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 |         3 | 2017-09-12 16:36:43 |
+----+--------------+----------------------------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from tag;
+----+------+
| id | name |
+----+------+
|  1 | 技術 |
|  2 | 娛樂 |
|  3 | 文學 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from article_tag;
+----+------------+--------+
| id | article_id | tag_id |
+----+------------+--------+
|  1 |          1 |      1 |
|  2 |          2 |      2 |
|  3 |          3 |      3 |
+----+------------+--------+
3 rows in set (0.01 sec)

INNER JOIN 例項
使用 INNER JOIN查詢article中author_id等於author的id的資料（這裡SQL語句中INNER可以省略）：

mysql> select name, qq, phone, title, content, create_time from author as u join article as a on u.id=a.author_id;
+------+------------+-------------+--------------+----------------------------+---------------------+
| name | qq         | phone       | title        | content                    | create_time         |
+------+------------+-------------+--------------+----------------------------+---------------------+
| 君惜 | 2298630081 | 18500178899 | 流暢的python | Python各種拽               | 2017-09-12 16:36:41 |
| 糖糖 |     234567 | 13256987582 | 嘻哈         | 中國有嘻哈                 | 2017-09-12 16:36:42 |
| 琳琳 |     345678 | 15636589521 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 | 2017-09-12 16:36:43 |
+------+------------+-------------+--------------+----------------------------+---------------------+
3 rows in set (0.00 sec)

以上SQL語句等價於：
where 子句

mysql> select name, qq, phone, title, content, create_time from author as u join article as a where u.id=a.author_id;
+------+------------+-------------+--------------+----------------------------+---------------------+
| name | qq         | phone       | title        | content                    | create_time         |
+------+------------+-------------+--------------+----------------------------+---------------------+
| 君惜 | 2298630081 | 18500178899 | 流暢的python | Python各種拽               | 2017-09-12 16:36:41 |
| 糖糖 |     234567 | 13256987582 | 嘻哈         | 中國有嘻哈                 | 2017-09-12 16:36:42 |
| 琳琳 |     345678 | 15636589521 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 | 2017-09-12 16:36:43 |
+------+------------+-------------+--------------+----------------------------+---------------------+
3 rows in set (0.00 sec)

利用第三張表連線查詢

mysql> select title as '書名', content as '內容', name as '標籤', create_time as "建立時間" from article, tag inner join article_tag as at where at.article_id=article.id and at.tag_id=tag.id;
+--------------+----------------------------+------+---------------------+
| 書名         | 內容                       | 標籤 | 建立時間            |
+--------------+----------------------------+------+---------------------+
| 流暢的python | Python各種拽               | 技術 | 2017-09-12 16:36:41 |
| 嘻哈         | 中國有嘻哈                 | 娛樂 | 2017-09-12 16:36:42 |
| 嚴肅         | 你這輩子就是吃了太嚴肅的虧 | 文學 | 2017-09-12 16:36:43 |
+--------------+----------------------------+------+---------------------+
3 rows in set (0.00 sec)

mysql> select au.name as '作者', ar.title as '書名', ar.content as '內容', t.name as '標籤', ar.create_time as '建立時間' from author as au, article as ar, tag as t inner join article_tag as at where au.id=ar.author_id and at.art
icle_id=ar.id and at.tag_id=t.id;
+------+--------------+----------------------------+------+---------------------+
| 作者 | 書名         | 內容                       | 標籤 | 建立時間            |
+------+--------------+----------------------------+------+---------------------+
| 君惜 | 流暢的python | Python各種拽               | 技術 | 2017-09-12 16:36:41 |
| 糖糖 | 嘻哈         | 中國有嘻哈                 | 娛樂 | 2017-09-12 16:36:42 |
| 琳琳 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 | 文學 | 2017-09-12 16:36:43 |
+------+--------------+----------------------------+------+---------------------+
3 rows in set (0.00 sec)

LEFT JOIN 例項
MySQL left join 與 join 有所不同。 MySQL LEFT JOIN 會讀取左邊資料表的全部資料，即便右邊表無對應資料。
以 author 為左表，article為右表。右表無對應資料自動填充為NULL：

mysql> select name, qq, phone, title, content, create_time from author as u left join article as a on u.id=a.author_id;
+--------+------------+-------------+--------------+----------------------------+---------------------+
| name   | qq         | phone       | title        | content                    | create_time         |
+--------+------------+-------------+--------------+----------------------------+---------------------+
| 君惜   | 2298630081 | 18500178899 | 流暢的python | Python各種拽               | 2017-09-12 16:36:41 |
| 糖糖   |     234567 | 13256987582 | 嘻哈         | 中國有嘻哈                 | 2017-09-12 16:36:42 |
| 琳琳   |     345678 | 15636589521 | 嚴肅         | 你這輩子就是吃了太嚴肅的虧 | 2017-09-12 16:36:43 |
| 李天星 |    5678911 | 13345607861 | NULL         | NULL                       | NULL                |
| 王星   |    5678912 | 13345607862 | NULL         | NULL                       | NULL                |
| 張星星 |    5678913 | 13345607863 | NULL         | NULL                       | NULL                |
+--------+------------+-------------+--------------+----------------------------+---------------------+
6 rows in set (0.00 sec)

RIGHT JOIN 例項
MySQL RIGHT JOIN 會讀取右邊資料表的全部資料，即便左邊邊表無對應資料。
以 article 為左表，author為右表，左表無對應資料自動填充為NULL。：

mysql> select title, content, create_time, name, qq, phone from article as a right join author as u on u.id=a.author_id;
+--------------+----------------------------+---------------------+--------+------------+-------------+
| title        | content                    | create_time         | name   | qq         | phone       |
+--------------+----------------------------+---------------------+--------+------------+-------------+
| 流暢的python | Python各種拽               | 2017-09-12 16:36:41 | 君惜   | 2298630081 | 18500178899 |
| 嘻哈         | 中國有嘻哈                 | 2017-09-12 16:36:42 | 糖糖   |     234567 | 13256987582 |
| 嚴肅         | 你這輩子就是吃了太嚴肅的虧 | 2017-09-12 16:36:43 | 琳琳   |     345678 | 15636589521 |
| NULL         | NULL                       | NULL                | 李天星 |    5678911 | 13345607861 |
| NULL         | NULL                       | NULL                | 王星   |    5678912 | 13345607862 |
| NULL         | NULL                       | NULL                | 張星星 |    5678913 | 13345607863 |
+--------------+----------------------------+---------------------+--------+------------+-------------+
6 rows in set (0.00 sec)

mysql> select title, content, create_time, name, qq, phone from article as a right join author as u on u.id=a.author_id where title is not null;
+--------------+----------------------------+---------------------+------+------------+-------------+
| title        | content                    | create_time         | name | qq         | phone       |
+--------------+----------------------------+---------------------+------+------------+-------------+
| 流暢的python | Python各種拽               | 2017-09-12 16:36:41 | 君惜 | 2298630081 | 18500178899 |
| 嘻哈         | 中國有嘻哈                 | 2017-09-12 16:36:42 | 糖糖 |     234567 | 13256987582 |
| 嚴肅         | 你這輩子就是吃了太嚴肅的虧 | 2017-09-12 16:36:43 | 琳琳 |     345678 | 15636589521 |
+--------------+----------------------------+---------------------+------+------------+-------------+
3 rows in set (0.00 sec)

先記錄到這了。

相關文章