用法（下）：查詢進(jìn)階與核心特性)
本篇為 MySQL 入門第二篇重點(diǎn)講解數(shù)據(jù)查詢語(yǔ)法、常用函數(shù)、索引、事務(wù)與權(quán)限管理是日常開(kāi)發(fā)的核心內(nèi)容。一、數(shù)據(jù)查詢SELECT詳解查詢是 MySQL 最常用的操作語(yǔ)法靈活功能豐富。以下示例均基于上一篇的student表。1. 基礎(chǔ)查詢sql-- 查詢所有字段生產(chǎn)環(huán)境不推薦 *影響性能且不明確 SELECT * FROM student; -- 查詢指定字段 SELECT id, name, age FROM student; -- 字段別名 SELECT name AS 姓名, age 年齡 FROM student; -- 去重查詢 SELECT DISTINCT gender FROM student;2. 條件查詢WHEREsql-- 比較運(yùn)算符 SELECT * FROM student WHERE age 18; SELECT * FROM student WHERE gender 男; -- 邏輯運(yùn)算符 AND / OR / NOT SELECT * FROM student WHERE age 18 AND gender 女; -- 范圍查詢 BETWEEN ... AND ... SELECT * FROM student WHERE age BETWEEN 18 AND 20; -- 枚舉查詢 IN SELECT * FROM student WHERE age IN (18, 20, 22); -- 模糊查詢 LIKE% 匹配任意字符_ 匹配單個(gè)字符 SELECT * FROM student WHERE name LIKE 張%; SELECT * FROM student WHERE name LIKE _三; -- 空值判斷 IS NULL / IS NOT NULL SELECT * FROM student WHERE gender IS NULL;3. 排序與分頁(yè)sql-- 排序ASC 升序默認(rèn)DESC 降序 SELECT * FROM student ORDER BY age DESC; -- 多字段排序 SELECT * FROM student ORDER BY age DESC, id ASC; -- 分頁(yè)LIMIT 偏移量, 每頁(yè)條數(shù) SELECT * FROM student LIMIT 0, 3; -- 第1頁(yè)每頁(yè)3條 SELECT * FROM student LIMIT 3, 3; -- 第2頁(yè)分頁(yè)公式第 n 頁(yè)每頁(yè) size 條 →LIMIT (n-1)*size, size4. 分組聚合查詢sql-- 聚合函數(shù) SELECT COUNT(*) 總?cè)藬?shù) FROM student; SELECT MAX(age) 最大年齡, MIN(age) 最小年齡, AVG(age) 平均年齡 FROM student; SELECT SUM(age) 年齡總和 FROM student; -- 分組統(tǒng)計(jì)按性別統(tǒng)計(jì)人數(shù) SELECT gender, COUNT(*) 人數(shù) FROM student GROUP BY gender; -- 分組后篩選 HAVINGWHERE 篩選行HAVING 篩選分組結(jié)果 SELECT gender, AVG(age) 平均年齡 FROM student GROUP BY gender HAVING 平均年齡 19;5. 聯(lián)表查詢當(dāng)數(shù)據(jù)分布在多張表中時(shí)通過(guò)關(guān)聯(lián)字段連接查詢。假設(shè)有另一張表class班級(jí)表student表有class_id字段關(guān)聯(lián)班級(jí)。sql-- 內(nèi)連接只返回兩張表匹配的數(shù)據(jù) SELECT s.name, c.class_name FROM student s INNER JOIN class c ON s.class_id c.id; -- 左連接返回左表全部數(shù)據(jù)右表無(wú)匹配則顯示 NULL SELECT s.name, c.class_name FROM student s LEFT JOIN class c ON s.class_id c.id; -- 右連接返回右表全部數(shù)據(jù)左表無(wú)匹配則顯示 NULL SELECT s.name, c.class_name FROM student s RIGHT JOIN class c ON s.class_id c.id;6. 子查詢查詢語(yǔ)句中嵌套另一個(gè)查詢sql-- 查詢年齡大于平均年齡的學(xué)生 SELECT * FROM student WHERE age (SELECT AVG(age) FROM student);二、常用內(nèi)置函數(shù)1. 字符串函數(shù)sqlSELECT CONCAT(姓名, name) FROM student; -- 拼接字符串 SELECT LENGTH(name) FROM student; -- 字節(jié)長(zhǎng)度 SELECT SUBSTRING(name, 1, 2) FROM student; -- 截取字符串 SELECT UPPER(name) FROM student; -- 轉(zhuǎn)大寫2. 數(shù)值函數(shù)sqlSELECT ROUND(3.1415, 2); -- 四舍五入保留2位 SELECT CEIL(3.1); -- 向上取整 SELECT FLOOR(3.9); -- 向下取整 SELECT ABS(-10); -- 絕對(duì)值3. 日期函數(shù)sqlSELECT NOW(); -- 當(dāng)前日期時(shí)間 SELECT CURDATE(); -- 當(dāng)前日期 SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 加7天 SELECT DATEDIFF(NOW(), 2020-01-01); -- 日期相差天數(shù)三、索引基礎(chǔ)索引是提升查詢速度的核心手段類似書籍的目錄通過(guò)犧牲少量寫入性能換取查詢效率。1. 索引類型主鍵索引主鍵自動(dòng)創(chuàng)建唯一且非空唯一索引字段值不允許重復(fù)普通索引最基礎(chǔ)的索引聯(lián)合索引多個(gè)字段組成的索引2. 索引操作sql-- 創(chuàng)建普通索引 CREATE INDEX idx_name ON student(name); -- 創(chuàng)建唯一索引 CREATE UNIQUE INDEX idx_name_unique ON student(name); -- 創(chuàng)建聯(lián)合索引 CREATE INDEX idx_name_age ON student(name, age); -- 查看索引 SHOW INDEX FROM student; -- 刪除索引 DROP INDEX idx_name ON student;3. 查看執(zhí)行計(jì)劃判斷查詢是否命中索引sqlEXPLAIN SELECT * FROM student WHERE name 張三;重點(diǎn)關(guān)注type列ALL表示全表掃描ref、range等表示命中索引。四、事務(wù)與存儲(chǔ)引擎1. 事務(wù)四大特性ACID原子性Atomicity事務(wù)內(nèi)操作要么全部成功要么全部失敗一致性Consistency事務(wù)前后數(shù)據(jù)完整性保持一致隔離性Isolation多個(gè)事務(wù)之間互不干擾持久性Durability事務(wù)提交后數(shù)據(jù)永久生效2. 事務(wù)控制sql-- 開(kāi)啟事務(wù) START TRANSACTION; -- 執(zhí)行多條 SQL UPDATE account SET money money - 100 WHERE id 1; UPDATE account SET money money 100 WHERE id 2; -- 提交事務(wù) COMMIT; -- 回滾事務(wù)出錯(cuò)時(shí)執(zhí)行 ROLLBACK;3. 常用存儲(chǔ)引擎InnoDBMySQL 默認(rèn)引擎支持事務(wù)、外鍵、行級(jí)鎖適合高并發(fā)寫入場(chǎng)景MyISAM不支持事務(wù)表級(jí)鎖查詢速度快適合讀多寫少場(chǎng)景五、用戶與權(quán)限管理1. 用戶管理sql-- 創(chuàng)建用戶指定用戶名和訪問(wèn)主機(jī) CREATE USER test_userlocalhost IDENTIFIED BY 123456; -- 修改密碼 ALTER USER test_userlocalhost IDENTIFIED BY new_password; -- 刪除用戶 DROP USER test_userlocalhost;2. 權(quán)限控制sql-- 授予所有庫(kù)所有表的查詢、插入權(quán)限 GRANT SELECT, INSERT ON *.* TO test_userlocalhost; -- 授予所有權(quán)限 GRANT ALL PRIVILEGES ON test_db.* TO test_userlocalhost; -- 刷新權(quán)限 FLUSH PRIVILEGES; -- 撤銷權(quán)限 REVOKE INSERT ON *.* FROM test_userlocalhost; -- 查看用戶權(quán)限 SHOW GRANTS FOR test_userlocalhost;謝謝 MySQL 基礎(chǔ)用法（下）：查詢進(jìn)階與核心特性
本篇為 MySQL 入門第二篇重點(diǎn)講解數(shù)據(jù)查詢語(yǔ)法、常用函數(shù)、索引、事務(wù)與權(quán)限管理是日常開(kāi)發(fā)的核心內(nèi)容。一、數(shù)據(jù)查詢SELECT詳解查詢是 MySQL 最常用的操作語(yǔ)法靈活功能豐富。以下示例均基于上一篇的student表。1. 基礎(chǔ)查詢sql-- 查詢所有字段生產(chǎn)環(huán)境不推薦 *影響性能且不明確 SELECT * FROM student; -- 查詢指定字段 SELECT id, name, age FROM student; -- 字段別名 SELECT name AS 姓名, age 年齡 FROM student; -- 去重查詢 SELECT DISTINCT gender FROM student;2. 條件查詢WHEREsql-- 比較運(yùn)算符 SELECT * FROM student WHERE age 18; SELECT * FROM student WHERE gender 男; -- 邏輯運(yùn)算符 AND / OR / NOT SELECT * FROM student WHERE age 18 AND gender 女; -- 范圍查詢 BETWEEN ... AND ... SELECT * FROM student WHERE age BETWEEN 18 AND 20; -- 枚舉查詢 IN SELECT * FROM student WHERE age IN (18, 20, 22); -- 模糊查詢 LIKE% 匹配任意字符_ 匹配單個(gè)字符 SELECT * FROM student WHERE name LIKE 張%; SELECT * FROM student WHERE name LIKE _三; -- 空值判斷 IS NULL / IS NOT NULL SELECT * FROM student WHERE gender IS NULL;3. 排序與分頁(yè)sql-- 排序ASC 升序默認(rèn)DESC 降序 SELECT * FROM student ORDER BY age DESC; -- 多字段排序 SELECT * FROM student ORDER BY age DESC, id ASC; -- 分頁(yè)LIMIT 偏移量, 每頁(yè)條數(shù) SELECT * FROM student LIMIT 0, 3; -- 第1頁(yè)每頁(yè)3條 SELECT * FROM student LIMIT 3, 3; -- 第2頁(yè)分頁(yè)公式第 n 頁(yè)每頁(yè) size 條 →LIMIT (n-1)*size, size4. 分組聚合查詢sql-- 聚合函數(shù) SELECT COUNT(*) 總?cè)藬?shù) FROM student; SELECT MAX(age) 最大年齡, MIN(age) 最小年齡, AVG(age) 平均年齡 FROM student; SELECT SUM(age) 年齡總和 FROM student; -- 分組統(tǒng)計(jì)按性別統(tǒng)計(jì)人數(shù) SELECT gender, COUNT(*) 人數(shù) FROM student GROUP BY gender; -- 分組后篩選 HAVINGWHERE 篩選行HAVING 篩選分組結(jié)果 SELECT gender, AVG(age) 平均年齡 FROM student GROUP BY gender HAVING 平均年齡 19;5. 聯(lián)表查詢當(dāng)數(shù)據(jù)分布在多張表中時(shí)通過(guò)關(guān)聯(lián)字段連接查詢。假設(shè)有另一張表class班級(jí)表student表有class_id字段關(guān)聯(lián)班級(jí)。sql-- 內(nèi)連接只返回兩張表匹配的數(shù)據(jù) SELECT s.name, c.class_name FROM student s INNER JOIN class c ON s.class_id c.id; -- 左連接返回左表全部數(shù)據(jù)右表無(wú)匹配則顯示 NULL SELECT s.name, c.class_name FROM student s LEFT JOIN class c ON s.class_id c.id; -- 右連接返回右表全部數(shù)據(jù)左表無(wú)匹配則顯示 NULL SELECT s.name, c.class_name FROM student s RIGHT JOIN class c ON s.class_id c.id;6. 子查詢查詢語(yǔ)句中嵌套另一個(gè)查詢sql-- 查詢年齡大于平均年齡的學(xué)生 SELECT * FROM student WHERE age (SELECT AVG(age) FROM student);二、常用內(nèi)置函數(shù)1. 字符串函數(shù)sqlSELECT CONCAT(姓名, name) FROM student; -- 拼接字符串 SELECT LENGTH(name) FROM student; -- 字節(jié)長(zhǎng)度 SELECT SUBSTRING(name, 1, 2) FROM student; -- 截取字符串 SELECT UPPER(name) FROM student; -- 轉(zhuǎn)大寫2. 數(shù)值函數(shù)sqlSELECT ROUND(3.1415, 2); -- 四舍五入保留2位 SELECT CEIL(3.1); -- 向上取整 SELECT FLOOR(3.9); -- 向下取整 SELECT ABS(-10); -- 絕對(duì)值3. 日期函數(shù)sqlSELECT NOW(); -- 當(dāng)前日期時(shí)間 SELECT CURDATE(); -- 當(dāng)前日期 SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 加7天 SELECT DATEDIFF(NOW(), 2020-01-01); -- 日期相差天數(shù)三、索引基礎(chǔ)索引是提升查詢速度的核心手段類似書籍的目錄通過(guò)犧牲少量寫入性能換取查詢效率。1. 索引類型主鍵索引主鍵自動(dòng)創(chuàng)建唯一且非空唯一索引字段值不允許重復(fù)普通索引最基礎(chǔ)的索引聯(lián)合索引多個(gè)字段組成的索引2. 索引操作sql-- 創(chuàng)建普通索引 CREATE INDEX idx_name ON student(name); -- 創(chuàng)建唯一索引 CREATE UNIQUE INDEX idx_name_unique ON student(name); -- 創(chuàng)建聯(lián)合索引 CREATE INDEX idx_name_age ON student(name, age); -- 查看索引 SHOW INDEX FROM student; -- 刪除索引 DROP INDEX idx_name ON student;3. 查看執(zhí)行計(jì)劃判斷查詢是否命中索引sqlEXPLAIN SELECT * FROM student WHERE name 張三;重點(diǎn)關(guān)注type列ALL表示全表掃描ref、range等表示命中索引。四、事務(wù)與存儲(chǔ)引擎1. 事務(wù)四大特性ACID原子性Atomicity事務(wù)內(nèi)操作要么全部成功要么全部失敗一致性Consistency事務(wù)前后數(shù)據(jù)完整性保持一致隔離性Isolation多個(gè)事務(wù)之間互不干擾持久性Durability事務(wù)提交后數(shù)據(jù)永久生效2. 事務(wù)控制sql-- 開(kāi)啟事務(wù) START TRANSACTION; -- 執(zhí)行多條 SQL UPDATE account SET money money - 100 WHERE id 1; UPDATE account SET money money 100 WHERE id 2; -- 提交事務(wù) COMMIT; -- 回滾事務(wù)出錯(cuò)時(shí)執(zhí)行 ROLLBACK;3. 常用存儲(chǔ)引擎InnoDBMySQL 默認(rèn)引擎支持事務(wù)、外鍵、行級(jí)鎖適合高并發(fā)寫入場(chǎng)景MyISAM不支持事務(wù)表級(jí)鎖查詢速度快適合讀多寫少場(chǎng)景五、用戶與權(quán)限管理1. 用戶管理sql-- 創(chuàng)建用戶指定用戶名和訪問(wèn)主機(jī) CREATE USER test_userlocalhost IDENTIFIED BY 123456; -- 修改密碼 ALTER USER test_userlocalhost IDENTIFIED BY new_password; -- 刪除用戶 DROP USER test_userlocalhost;2. 權(quán)限控制sql-- 授予所有庫(kù)所有表的查詢、插入權(quán)限 GRANT SELECT, INSERT ON *.* TO test_userlocalhost; -- 授予所有權(quán)限 GRANT ALL PRIVILEGES ON test_db.* TO test_userlocalhost; -- 刷新權(quán)限 FLUSH PRIVILEGES; -- 撤銷權(quán)限 REVOKE INSERT ON *.* FROM test_userlocalhost; -- 查看用戶權(quán)限 SHOW GRANTS FOR test_userlocalhost;謝謝