MySQL常用的查詢操作

一、MySQL查詢語法

MySQL數(shù)據(jù)庫使用SELECT語句來查詢數(shù)據(jù)。

1.1 語法

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

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

1.2 常用的操作

• 查看當(dāng)前的數(shù)據(jù)庫

SELECT database();

• 結(jié)果

• 查詢當(dāng)前數(shù)據(jù)庫版本

SELECT version();

• 結(jié)果

• 查看當(dāng)前時間

SELECT now();

• 結(jié)果

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

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

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 '創(chuàng)建日期',
  `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

數(shù)據(jù)表建完之后插入一些數(shù)據(jù)：

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;

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

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

2.2 分組查詢

• 1.示例：分別統(tǒng)計男女員工的數(shù)量

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

• 結(jié)果

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

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

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

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

• 以上兩種寫法，輸出的結(jié)果相同

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

• 2.示例：分組統(tǒng)計男女員工的平均年齡

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

• 結(jié)果

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

• 3.示例：統(tǒng)計年齡大于20的男性員工

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

• 結(jié)果

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

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;

• 結(jié)果

2.4 分頁查詢

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

• 示例：將數(shù)據(jù)分成每頁5條，查詢第3頁的數(shù)據(jù)

SELECT * FROM staffs LIMIT 5 OFFSET 10;

上述sql語句可以簡寫：

SELECT * FROM staffs LIMIT 5, 10;

• 結(jié)果

至于每一頁的其實偏移量應(yīng)該怎么計算，這里其實有一個公式，假設(shè)每頁的數(shù)據(jù)條數(shù)為num，當(dāng)前查詢的頁碼為page，那么，該公式可總結(jié)為：

offset = (page - 1) * num

關(guān)于分頁查詢的總結(jié)：

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

2.5 子查詢

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

SELECT * FROM staffs ORDER BY salary DESC LIMIT 1;

• 結(jié)果

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

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

• 結(jié)果

2.6 集合查詢

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

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

• 結(jié)果

2.7 模糊查詢

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

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

• 結(jié)果

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

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

• 結(jié)果

2.8 關(guān)聯(lián)查詢

關(guān)聯(lián)查詢也叫多表查詢，下面再創(chuàng)建兩張表作為示例數(shù)據(jù)。

2.8.1 創(chuàng)建一張文章表(article)并插入一些數(shù)據(jù)

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

• 表結(jié)構(gòu)和數(shù)據(jù)

2.8.2 創(chuàng)建一張欄目表(categories)并插入一些數(shù)據(jù)

-- 建表語句
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;
-- 插入數(shù)據(jù)
INSERT INTO categories (name) VALUES ('國內(nèi)新聞'),('國際新聞'),('娛樂新聞'),('軍事新聞');

• 表結(jié)構(gòu)和數(shù)據(jù)

2.8.4 內(nèi)連接

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

首先，不用內(nèi)連接的方式，sql語句應(yīng)該這樣寫：

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;

如果只看國內(nèi)新聞：

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

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

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

如果我還是想只獲取國內(nèi)新聞，那么應(yīng)該這樣寫：

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

如果多表關(guān)聯(lián)字段同名，可以使用關(guān)鍵字USING簡化：

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

2.8.5 外連接

首先再往articles表中插入一些演示數(shù)據(jù)：

INSERT INTO articles(title, cid) VALUES
('體育教育成兩會熱點話題：學(xué)生只有分數(shù)贏不了未來', 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;

• 左、右外連接轉(zhuǎn)內(nèi)連接

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

左外連接轉(zhuǎn)內(nèi)連接

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

右外連接轉(zhuǎn)內(nèi)連接

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

以上兩種連接轉(zhuǎn)換結(jié)果都是一樣的：

2.8.6 自然連接

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

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

三、視圖

3.1 創(chuàng)建視圖

-- 創(chuàng)建視圖
CREATE VIEW v_staffs AS SELECT * FROM staffs;

3.2 更新視圖

更新視圖，會同步更新基本表數(shù)據(jù)。

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

3.3 刪除視圖

DROP VIEW v_staffs;

四、索引

• 索引應(yīng)該創(chuàng)建在經(jīng)常被查詢的字段或者經(jīng)常出現(xiàn)在結(jié)果集的字段上。

• 索引分類：普通索引、主鍵索引、唯一索引、全文索引。

4.1 創(chuàng)建索引

4.1.1 創(chuàng)建一個普通索引

-- 創(chuàng)建索引
CREATE INDEX 索引名稱 ON 表名(字段名);
-- 示例
CREATE INDEX i_email ON staffs(email);

4.1.2 創(chuàng)建唯一索引

CREATE UNIQUE INDEX i_unique_email ON staffs(email);

4.1.3 創(chuàng)建主鍵索引

-- 創(chuàng)建主鍵索引
ALTER TABLE 表名 ADD PRIMARY KEY 索引名稱(字段名);
-- 為主鍵創(chuàng)建一個索引
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;

五、預(yù)處理

• 防止SQL注入攻擊；
• SQL語句中的數(shù)據(jù)，只有在執(zhí)行階段再與字段綁定。

-- 生成預(yù)處理sql語句
PREPARE STMT FROM 'SELECT uid, name, salary FROM staffs WHERE salary > ? LIMIT ?;';
-- 將真實數(shù)據(jù)綁定到預(yù)處理語句的占位符(?)上
-- SET @salary = 5000;
-- SET @LIMIT = 10;
-- 可以寫到一行代碼里
SET @salary = 5000, @LIMIT = 10;
-- 執(zhí)行
EXECUTE STMT USING @salary, @LIMIT;

批改老師：天蓬老師

批改狀態(tài)：合格

老師批語：命令行用得很棒