最近我在學習MySQL，以前對它也略有了解，但是并不太會運用，這段時間把MySQL的相關語句系統地學習了一下，下面是學習的相關總結，分享給大家。

MySQL

MySQL常用的查詢操作

一、MySQL查詢語法

MySQL數據庫使用SELECT語句來查詢數據。

1.1 語法

SELECT column_name,column_name
FROM table_name [WHERE Clause][LIMIT N][ OFFSET M]

• MySQL查詢可以使用一個或者多個表，表與表之間需要用逗號(,)分割開，并且需要使用WHERE語句設置查詢條件；
• SELECT可以讀取一條或多條記錄；
• 星號(*)表示SELECT語句會返回數據表的所有字段；
• WHERE語句是用來設置查詢條件的；
• LIMIT用于限制返回的數據條數。
• OFFSET用于指定SELECT語句開始查詢數據時的偏移量，默認值為0。

1.2 常用的操作

• 查看當前的數據庫

SELECT database();

• 結果

 

查看當前數據庫

• 查詢當前數據庫版本

SELECT version();

• 結果

查看當前數據庫版本

• 查看當前時間

SELECT now();

• 結果

查看當前時間

二、常用的查詢語句及示例

新建一張員工（staffs）表，并插入一下數據，建表語句如下：

CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',
  `age` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年齡',
  `gender` enum('male','female') COLLATE utf8mb4_unicode_ci NOT NULL,
  `salary` int(10) unsigned NOT NULL DEFAULT '2000',
  `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '郵箱',
  `birthday` date NOT NULL COMMENT '生日',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建日期',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改日期',
  `test` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

表結構

 

數據表建完之后插入一些數據：

插入一些數據

2.1 條件查詢

• 示例：查詢工資大于11000的員工。

SELECT uid id, name, salary FROM staffs WHERE salary > 11000;

• 示例：查詢工資在6500~8000之間的員工。

SELECT uid, name, salary FROM staffs WHERE salary > 6500 AND salary < 8000;

注意： 以上方法查詢的數據不包含6500和8000這兩個值，也就是說不包含邊界，如果要想使查詢的結果包含邊界值，應該使用BETWEEN...AND。

SELECT uid, name, salary FROM staffs WHERE BETWEEN 6500 AND 8000;

2.2 分組查詢

• 1.示例：分別統計男女員工的數量

SELECT gender, COUNT(1) FROM staffs GROUP BY gender;

• 結果

分組查詢

但是gender和COUNT(1)看起來不直觀，我們可以改個名字，sql語句需要這樣寫：

SELECT gender AS 性別, COUNT(1) AS 數量 FROM staffs GROUP BY gender;

以上sql語句還有一個簡寫的方法，省略AS：

SELECT gender 性別, COUNT(1) 數量 FROM staffs GROUP BY gender;

• 以上兩種寫法，輸出的結果相同

分組查詢

---

聚合函數：max()、min()、avg()、count()

• 2.示例：分組統計男女員工的平均年齡

SELECT gender, AVG(age) avg_age FROM staffs GROUP BY gender;

• 結果

分組統計男女員工的平均年齡

上面計算的avg_age值還可以使用ROUND()函數保留兩位小數，ROUND(AVG(age))。

• 3.示例：統計年齡大于20的男性員工

SELECT gender, COUNT(1) num FROM staffs GROUP BY gender HAVING gender = 'male';

• 結果

統計年齡大于20的男性員工

分組條件，只能使用HAVING，不能使用WHERE ，因為WHERE只能從現存的字段中作為條件。

2.3 排序

• 1.示例：按照員工工資升序排列。

SELECT uid, name, salary FROM staffs ORDER BY salary ASC;

按照員工工資升序排列

• 2.示例：按照員工工資降序排列

SELECT uid, name, salary FROM staffs ORDER BY salary DESC;

按照員工工資降序排列

• 3.示例：多字段排序，按照年齡降序排序，工資升序排序

SELECT * FROM staffs ORDER BY age DESC, salary ASC;

• 結果

按照年齡降序排序，工資升序排序

2.4 分頁查詢

當我們查詢出來的數據量太大的時候，一頁展示得又太多，一般情況下都會將其分成N頁，那么這時候就需要用到分頁查詢。

• 示例：將數據分成每頁5條，查詢第3頁的數據

SELECT * FROM staffs LIMIT 5 OFFSET 10;

上述sql語句可以簡寫：

SELECT * FROM staffs LIMIT 5, 10;

• 結果

分頁查詢

至于每一頁的其實偏移量應該怎么計算，這里其實有一個公式，假設每頁的數據條數為num，當前查詢的頁碼為page，那么，該公式可總結為：

offset = (page - 1) * num

關于分頁查詢的總結：

1. LIMIT 顯示數量 OFFSET 偏移量（跳過的記錄數量）;
2. LIMIT 偏移量， 顯示數量（這是簡寫）;
3. LIMIT 子句一定要放在SELECT的最后面。

2.5 子查詢

查詢工資最高的員工信息，通常的做法是：

SELECT * FROM staffs ORDER BY salary DESC LIMIT 1;

• 結果

查詢工資最高的員工信息

這樣肯定是可以查詢出來工資最高的員工信息的，但是還可以利用子查詢來查詢。

SELECT * FROM staffs WHERE salary = (SELECT MAX(salary) FROM staffs);

• 結果

利用子查詢來查詢

2.6 集合查詢

• 示例: 查詢uid為1、3、5的用戶信息

SELECT uid, name FROM staffs WHERE uid IN(1, 3, 5);

• 結果

查詢uid為1、3、5的用戶信息

2.7 模糊查詢

• 示例：匹配用戶姓名中包含a的員工

SELECT uid, name FROM staffs WHERE name LIKE 'a%';

• 結果

匹配用戶姓名中包含a的員工

• 示例：匹配用戶姓名第二個字母為i的員工

SELECT uid, name FROM staffs WHERE name LIKE '_i%';

• 結果

匹配用戶姓名第二個字母為i的員工

2.8 關聯查詢

關聯查詢也叫多表查詢，下面再創建兩張表作為示例數據。

2.8.1 創建一張文章表(article)并插入一些數據

-- 建表語句
CREATE TABLE articles (
  aid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL COMMENT '文章標題',
  cid INT UNSIGNED NOT NULL COMMENT '欄目ID'
  PRIMARY KEY (aid)
) ENGINE = InnoDB COLLATE = utf8mb4_unicode_ci;

-- 插入數據
INSERT INTO articles (title, cid) VALUES
('探索廢除生育限制，為何是東北？', 1),
('8210億元！春節零售餐飲消費迎開門紅', 1),
('為防控疫情，美加墨陸地邊界通行限制再延一月', 2),
('得州宣布重大災害700萬人遇新危機', 2);

• 表結構和數據

articles表結構

articles表數據

2.8.2 創建一張欄目表(categories)并插入一些數據

-- 建表語句
CREATE TABLE categories (
  cid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL COMMENT '欄目名稱',
  PRIMARY KEY (cid)
) ENGINE = INNODB COLLATE = utf8mb4_unicode_ci;

-- 插入數據
INSERT INTO categories (name) VALUES ('國內新聞'),('國際新聞'),('娛樂新聞'),('軍事新聞');

• 表結構和數據

categories表結構

categories表數據

2.8.4 內連接

現在我想讓articles表中的cid字段顯示新聞欄目的中文名稱，如下圖示例這樣：

 

首先，不用內連接的方式，sql語句應該這樣寫：

SELECT a.aid, a.title, c.name FROM articles a, categories c WHERE a.cid = c.cid;

由于兩張表的字段aid, title, name都不一樣因此可以不用在字段前面加上表名限制，所以上面的語句還可以這樣簡寫：

SELECT aid, title, name FROM articles a, categories c WHERE a.cid = c.cid; 

 

如果只看國內新聞：

SELECT aid, title, name FROM articles a, categories c WHERE a.cid = c.cid AND a.cid = 1;

 

上述寫法有點繁瑣，可以使用內連接改進(INNER JOIN + ON)：

SELECT aid, title, name FROM articles a INNER JOIN categories c ON a.cid = c.cid;

-- 默認就是內連接，因此INNER可省略
SELECT aid, title, name FROM articles a JOIN categories c ON a.cid = c.cid;

如果我還是想只獲取國內新聞，那么應該這樣寫：

SELECT aid, title, name FROM articles a JOIN categories c ON a.cid = c.cid WHERE a.cid = 1;

如果多表關聯字段同名，可以使用關鍵字USING簡化：

-- 關聯的兩張表cid字段一樣
SELECT aid, title, name FROM articles a JOIN categories c USING(cid);

2.8.5 外連接

首先再往articles表中插入一些演示數據：

INSERT INTO articles(title, cid) VALUES
('體育教育成兩會熱點話題：學生只有分數贏不了未來', 7),
('玩家眾籌25000美元做《Dota2》新教程：舊版本過時', 8);

插入一些演示數據

• 左外連接

SELECT * FROM articles a LEFT JOIN categories c ON a.cid = c.cid;

左外連接

• 右外連接

SELECT * FROM articles a RIGHT JOIN categories c ON a.cid = c.cid;

右外連接

---

 

• 左、右外連接轉內連接

原理：通過添加過濾器，過濾掉從表中的某一個為NULL的字段即可。

左外連接轉內連接

SELECT * FROM articles a LEFT JOIN categories c ON a.cid = c.cid WHERE c.cid IS NOT NULL;

右外連接轉內連接

SELECT * FROM articles a RIGHT JOIN categories c ON a.cid = c.cid WHERE a.cid IS NOT NULL;

以上兩種連接轉換結果都是一樣的：

 

 

---

2.8.6 自然連接

自然連接是內連接的一種特例，前提是關聯的表中存在同名字段，可以連USING()都省略了，如果不需要使用表別名，在寫sql語句的時候，表的別名也可以省略。

SELECT aid, title, name FROM articles NATURAL JOIN categories;

自然連接

三、視圖

3.1 創建視圖

-- 創建視圖
CREATE VIEW v_staffs AS SELECT * FROM staffs;

3.2 更新視圖

更新視圖，會同步更新基本表數據。

UPDATE v_staffs SET salary = salary + 1000 WHERE uid = 1;

3.3 刪除視圖

DROP VIEW v_staffs;

四、索引

• 索引應該創建在經常被查詢的字段或者經常出現在結果集的字段上。
• 索引分類：普通索引、主鍵索引、唯一索引、全文索引。

4.1 創建索引

4.1.1 創建一個普通索引

-- 創建索引
CREATE INDEX 索引名稱 ON 表名(字段名);

-- 示例
CREATE INDEX i_email ON staffs(email);

創建索引

4.1.2 創建唯一索引

CREATE UNIQUE INDEX i_unique_email ON staffs(email);

創建唯一索引

4.1.3 創建主鍵索引

-- 創建主鍵索引
ALTER TABLE 表名 ADD PRIMARY KEY 索引名稱(字段名);

-- 為主鍵創建一個索引
ALTER TABLE test ADD PRIMARY KEY i_id(id);

4.2 刪除索引

-- 刪除索引
DROP INDEX 索引名稱 ON 表名;

-- 示例
DROP INDEX i_email ON staffs;

4.3 查看索引

-- 查看索引
SHOW INDEX FROM 表名;

-- 示例
SHOW INDEX FROM staffs;

---

以上就是我這段時間學習總結的MySQL常用的一些查詢語句，可能內容比較淺顯，簡單，沒辦法，我也是個新手，只能總結成這樣了，歡迎各位大佬賜教，感激不盡。