Notes
Search
⌃K

SQL

自从 SQL 加入 TIOBE å°±ä¸€ç›´ä¿æŒåœ¨ Top 10。SQL 从诞生到现在,å˜åŒ–很少。SQL 有两个标准 SQL92 å’Œ SQL99。
å¯ä»¥æŠŠ SQL 语言按照功能分为四ç§ï¼š
  • DDL(Data Definition Language):创建ã€åˆ é™¤ã€ä¿®æ”¹æ•°æ®åº“和表结构。
  • DML(Data Manipulation Language):增加ã€åˆ é™¤ã€ä¿®æ”¹æ•°æ®è¡¨ä¸­çš„记录。
  • DCL(Data Control Language):定义访问æƒé™å’Œå®‰å…¨çº§åˆ«ã€‚
  • DQL(Data Query Language):查询想è¦çš„记录。
书写 SQL è¯­å¥æˆ‘们éµå¾ªå¦‚下规范:
  • 表åã€è¡¨åˆ«åã€å­—段åã€å­—段别åç­‰å°å†™ï¼›
  • SQL ä¿ç•™å­—ã€å‡½æ•°åã€ç»‘定å˜é‡ç­‰å¤§å†™ï¼›
主æµçš„ DBMS(数æ®åº“管ç†ç³»ç»Ÿï¼‰æœ‰ Oracleã€MySQLã€SQL Serverã€PostgreSQLã€DB2ã€MongoDB 等。
  • DBMS(Database Management System):å¯ä»¥å¯¹å¤šä¸ªæ•°æ®åº“进行管ç†ï¼ŒDBMS = 多个 DB + 管ç†ç¨‹åºã€‚
  • DB(Database):数æ®åº“,å¯ç†è§£ä¸ºå¤šå¼ æ•°æ®è¡¨ã€‚
  • DBS(Database System):数æ®åº“系统,包括数æ®åº“ã€æ•°æ®åº“管ç†ç³»ç»Ÿã€æ•°æ®ç®¡ç†äººå‘˜ï¼ˆDBA)等。
  • RDBMS,关系型数æ®åº“。
我们采用 E-R 图(Entity Relationship Diagram)æ¥è®¾è®¡æ•°æ®å…³ç³»ï¼Œ 这个图有 3 个è¦ç´ ï¼šå®žä½“ã€å±žæ€§ã€å…³ç³»ã€‚

DDL

官方文档,Data Definition Statements。
常用的有增ã€åˆ ã€æ”¹ï¼Œåˆ†åˆ«å¯¹åº” CREATEã€DROPã€ALTER。在执行 DDL 时,ä¸éœ€è¦ COMMIT å°±èƒ½å®Œæˆæ‰§è¡Œã€‚
# æ•°æ®åº“
CREATE DATABASE XXX;
DROP DATABASE XXX;
DROP SCHEMA XXX;
​
# 创建数æ®è¡¨
# æ•°æ®å’Œå­—段都是用了å引å·ï¼Œä»¥é˜²å’Œä¿ç•™å­—相åŒ
# int(11),其中 11 代表显示长度,与数值范围无关
# varchar(255),其中 255 代表最大长度
# player_name 的字符集是 uft8,排åºè§„则是 utf8_general_ci,表示大å°å†™ä¸æ•感
DROP TABLE IF EXISTS `player`;
CREATE TABLE `player` (
`player_id` int(11) NOT NULL AUTO_INCREMENT,
`team_id` int(11) NOT NULL,
`player_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`height` float(3, 2) NULL DEFAULT 0.00,
PRIMARY KEY (`player_id`) USING BTREE,
UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
​
# 增加列
ALTER TABLE {tb_name}
ADD [COLUMN] {col_name1} {col_definition1} [FIRST|AFTER existing_column1],
ADD [COLUMN] {col_name2} {col_definition2} [FIRST|AFTER existing_column2],...;
​
ALTER TABLE app ADD created_by varchar(32) NULL COMMENT '创建人';
​
# 删除列
ALTER TABLE {tb_name}
DROP COLUMN {col_name1},
DROP COLUMN {col_name2},...;
​
# é‡å‘½å列
ALTER TABLE XXX RENAME COLUMN age to player_age;
​
# 修改列数æ®ç±»åž‹
ALTER TABLE XXX MODIFY (player_age float(3,1));

æ•°æ®è¡¨çº¦æŸ

  • 主键约æŸï¼šä¸èƒ½é‡å¤ï¼Œä¸èƒ½ä¸ºç©ºï¼Œå³ UNIQUE + NOT NULLï¼›
  • 外键约æŸï¼šä¸€ä¸ªè¡¨çš„外键对应å¦ä¸€ä¸ªè¡¨çš„主键;
  • 唯一性约æŸï¼šè¡¨æ˜Žå­—段在表中的数值是唯一的;
  • NOT NULL 约æŸï¼šä¸èƒ½ä¸ºç©ºï¼›
  • DEFAULT:
  • CHECKï¼šç”¨æ¥æ£€æŸ¥ç‰¹å®šå­—段å–值范围的有效性;

æ•°æ®è¡¨çš„设计原则:

  • æ•°æ®è¡¨è¶Šå°‘越好;
  • æ•°æ®è¡¨ä¸­çš„字段个数越少越好;
  • è”åˆä¸»é”®çš„字段数越少越好;
  • 主键的利用率越高越好;

VIEW

# 创建视图
CREATE VIEW view_name AS select_statement;
​
CREATE VIEW player_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player);
# 视图å¯ä»¥åµŒå¥—
CREATE VIEW player_above_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player_above_avg_height);
# 修改视图
ALTER VIEW view_name AS select_statement;
​
ALTER VIEW player_above_avg_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) from player);
# 删除视图
DROP VIEW view_name;
视图的特点:
  • å¯ç”¨äºŽå®‰å…¨æ€§ï¼Œæ¯”如我们需è¦å¯¹è¡¨çš„å­—æ®µçº§åˆ«åšæƒé™è®¾ç½®ï¼Œé‚£ä¹ˆå¯ä»¥ç”¨è§†å›¾æ¥å®žçŽ°ã€‚
  • 简化 SQLï¼Œç¼–å†™å¥½å¤æ‚查询的视图åŽï¼Œæˆ‘们仅需è¦ç®€å•的查询就行。
  • 是虚拟表,本身ä¸å­˜å‚¨æ•°æ®ï¼Œæ‰€ä»¥å¯¹æ•°æ®çš„修改é™åˆ¶å¾ˆå¤šï¼Œæ‰€ä»¥è§†å›¾ä¸€èˆ¬ç”¨ä½œæŸ¥è¯¢ã€‚

PROCEDURE

存储过程和视图一样,也是对 SQL 代ç çš„å°è£…,å¯ä»¥åå¤åˆ©ç”¨ã€‚ä¸åŒçš„æ˜¯ï¼Œè§†å›¾æ˜¯è™šæ‹Ÿè¡¨ï¼Œé€šå¸¸ä¸ç”¨æ¥æ“作数æ®ï¼Œè€Œå­˜å‚¨è¿‡ç¨‹æ˜¯ç¨‹åºåŒ–çš„ SQL,å¯ä»¥æ“作底层数æ®ã€‚存储过程由 SQL 语å¥å’Œæµç¨‹æŽ§åˆ¶è¯­å¥æž„æˆã€‚
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
BEGIN
éœ€è¦æ‰§è¡Œçš„语å¥
END
​
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
比如计算 1 到 n 的和:
DELIMITER //
​
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
DECLARE i INT;
DECLARE sum INT;
SET i = 1;
SET sum = 0;
WHILE i <= n DO
SET sum = sum + i;
SET i = i + 1;
END WHILE;
SELECT sum;
END //
​
DELIMITER ;
​
CALL add_num(50);
默认 SQL 采用 ; 作为结æŸç¬¦ï¼Œè¿™æ ·å­˜å‚¨è¿‡ç¨‹ä¸­çš„æ¯ä¸€å¥ SQL éƒ½éœ€è¦æ‰§è¡Œã€‚所以通过 DELIMITER 临时修改结æŸç¬¦ï¼Œå­˜å‚¨è¿‡ç¨‹åˆ›å»ºå¥½åŽå†å˜å›žæ¥ã€‚若是 Navicat 等工具就ä¸éœ€è¦ï¼Œå®ƒä¼šè‡ªåŠ¨å¸®ä½ è®¾ç½® DELIMITER,用 MySQL client 就需è¦ã€‚

傿•°ç±»åž‹

  • IN:å‘å­˜å‚¨è¿‡ç¨‹ä¼ å…¥å‚æ•°ï¼Œä¸è¿”回。
  • OUTï¼šæŠŠå­˜å‚¨è¿‡ç¨‹è®¡ç®—ç»“æžœæ”¾å…¥è¯¥å‚æ•°ï¼Œè°ƒç”¨è€…å¯ä»¥å¾—到返回值。
  • INOUT:IN å’Œ OUT 的结åˆã€‚
CREATE PROCEDURE `get_hero_scores`(
OUT max_max_hp FLOAT,
OUT min_max_mp FLOAT,
OUT avg_max_attack FLOAT,
s VARCHAR(255))
BEGIN
SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max)
FROM heros
WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END
CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战⼠');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

æµç¨‹æŽ§åˆ¶è¯­å¥

  • BEGIN ... END:中间包å«å¤šä¸ªè¯­å¥ï¼Œæ¯ä¸ªè¯­å¥ä»¥ ; 结æŸã€‚
  • DECLARE:申明å˜é‡ã€‚
  • SET:赋值。
  • SELECT ... INTO:赋值。
  • IF ... THEN ... [ELSE|ELSEIF] ... ENDIF:æ¡ä»¶åˆ¤æ–­ã€‚
  • CASE:多æ¡ä»¶åˆ†æ”¯åˆ¤æ–­ã€‚
  • LOOPã€LEAVEã€ITERATE:LEAVE å¯ç†è§£ä¸º break,ITERATE å¯ç†è§£ä¸º continue。
  • REPEAT ... UNTIL ... END REPEAT:å¯ç†è§£ä¸º do while。
  • WHILE ... DO ... END WHILE:å¯ç†è§£ä¸º while。

优缺点

  • 优点:
    • 一次编译多次使用
    • 代ç å°è£…æˆæ¨¡å—ï¼Œå¤æ‚问题å¯ä»¥æ‹†è§£æˆç®€å•问题
    • 模å—之间å¯é‡å¤ä½¿ç”¨
    • 安全性强,å¯ä»¥ä¸ºå­˜å‚¨è¿‡ç¨‹è®¾å®šæƒé™
    • å¯å‡å°‘ç½‘ç»œä¼ è¾“ï¼ŒåŒ…æ‹¬ä»£ç æ•°æ®é‡ã€è¿žæŽ¥æ¬¡æ•°ç­‰
  • 缺点:
    • ç§»æ¤æ€§å·®ï¼Œå¾ˆéš¾è·¨æ•°æ®åº“ç§»æ¤
    • 调试困难
    • æ•°æ®è¡¨ç´¢å¼•å‘生å˜åŒ–,å¯èƒ½å¯¼è‡´å­˜å‚¨è¿‡ç¨‹å¤±æ•ˆ
    • ä¸é€‚åˆé«˜å¹¶å‘ï¼Œå› ä¸ºé«˜å¹¶å‘æ•°æ®åº“会分库分表

DQL

官方文档,SELECT Syntax。
# 查询æŸäº›åˆ—
SELECT name, create_time FROM tag;
​
# 查询所有列
SELECT * from tag;
​
# å–别å
SELECT name AS n, create_time AS ct FROM tag;
​
# 查询常数列,这一列的å–å€¼æ˜¯æˆ‘ä»¬æŒ‡å®šçš„ï¼Œä¸æ˜¯ä»Žæ•°æ®åº“中动æ€èŽ·å–ï¼Œé€‚åˆæ•´åˆä¸åŒçš„æ•°æ®æº
SELECT '标签' AS `type`, name FROM tag;
​
# 去除é‡å¤è¡Œ
# DISTINCT éœ€è¦æ”¾åˆ°æ‰€æœ‰åˆ—åå‰
# DISTINCT 是对åŽé¢æ‰€æœ‰åˆ—的组åˆè¿›è¡ŒåŽ»é‡
SELECT DISTINCT name FROM tag;
​
# 排åº
# ORDER BY åŽé¢å¯ä»¥æœ‰å¤šä¸ªåˆ—
# ORDER BY åŽé¢å¯ä»¥è‘—åæŽ’åºè§„则,ASC(default)ã€DESC,
# ORDER BY åŽé¢çš„列å¯ä»¥ä¸åœ¨é€‰æ‹©åˆ—里é¢
# ORDER BY 通常ä½äºŽ SELECT 语å¥çš„æœ€åŽ
SELECT name FROM tag ORDER BY create_time;
​
# 返回结果数é‡
SELECT name FROM tag LIMIT 5;

WHERE å­å¥

官方文档,Expressionsã€Functions and Operators。
比较è¿ç®—符有:= | >= | > | <= | < | <> | !=。
SELECT * FROM tag WHERE id > 10;
SELECT * FROM tag WHERE id BETWEEN 10 AND 20;
SELECT * FROM tag WHERE name IS NULL;
逻辑è¿ç®—符有:AND OR IN NOT。
通é…符:LIKE,查询英雄å字除了第一个字以外,包å«"太"字的英雄:
SELECT name FROM heros WHERE name LIKE '_%太%';
_ 匹é…ä»»æ„一个字符,% 匹é…大于等于 0 个字符。所以“太乙真人â€åŒ¹é…ä¸ä¸Šï¼Œâ€œä¸œçš‡å¤ªä¹™â€å¯ä»¥åŒ¹é…,“太乙真人太太â€å¯ä»¥åŒ¹é…。
å¦å¤– LIKE '%' 无法查出 NULL 值。

LIMIT

LIMIT æœ€åŽæ‰§è¡Œã€‚
å¦‚æžœç¡®å®šæŸ¥è¯¢ç»“æžœåªæœ‰ä¸€æ¡ï¼Œé‚£ä¹ˆéœ€ä¸éœ€è¦åŠ  LIMIT 1ï¼Ÿæœ‰ä¸¤ç§æƒ…况
  • 若对æŸä¸ªå­—段建立了唯一索引,那么对这个字段查询,ä¸éœ€è¦åŠ  LIMIT 1.
  • 若是全表扫æçš„ SQL 语å¥ï¼Œè‹¥åŠ äº† LIMIT 1,当找到一æ¡ç»“æžœåŽå°±ä¸ä¼šç»§ç»­æ‰«æäº†ï¼Œèƒ½åŠ å¿«æŸ¥è¯¢é€Ÿåº¦ã€‚

ORDER BY

å°½é‡åœ¨ ORDER BY 字段上加索引,为什么?
MySQL æœ‰ä¸¤ç§æŽ’åºæ–¹å¼ï¼ŒFileSort å’Œ IndexSort:
  • FileSort:一般在内存中排åºï¼ŒCPU å ç”¨è¾ƒå¤šï¼Œå¦‚果待排åºç»“果较大,还会产生临时文件到ç£ç›˜ï¼Œæ•ˆçŽ‡è¾ƒä½Žã€‚
  • IndexSort:索引å¯ä»¥ä¿è¯æ•°æ®çš„æœ‰åºæ€§ï¼Œä¸éœ€è¦è¿›è¡ŒæŽ’åºã€‚
所以当使用 ORDER BY 时,应尽é‡ä½¿ç”¨ IndexSort,å¯ä»¥ä½¿ç”¨ explain 查看是å¦ä½¿ç”¨ç´¢å¼•排åºã€‚
所以有如下优化建议:
  • WHERE å’Œ ORDER BY 都使用索引,WHERE 使用时为了é¿å…全表扫æï¼ŒORDER BY 使用时为了é¿å… FileSort。
  • è‹¥ WHERE å’Œ ORDER BY 是相åŒçš„列,就使用å•索引列;若ä¸åŒï¼Œå°±ä½¿ç”¨è”åˆç´¢å¼•。

GROUP BY

我们å¯ä»¥å¯¹æ•°æ®è¿›è¡Œåˆ†ç»„,并使用èšé›†å‡½æ•°ç»Ÿè®¡æ¯ç»„æ•°æ®çš„值。
SELECT COUNT(*) AS num, role_main, role_assist FROM heros GROUP BY role_main, role_assist ORDER BY num DESC;
è‹¥ GROUP BY åŽé¢è¿˜æœ‰ ORDER BY,那么排åºå®žé™…上是对分组åŽçš„æ•°æ®æŽ’åºï¼Œå› ä¸ºåˆ†ç»„å·²ç»æŠŠå¤šæ¡æ•°æ®èšåˆæˆäº†ä¸€æ¡è®°å½•。

HAVING

当我们使用 GROUP BY 创建分组之åŽï¼Œå¯èƒ½éœ€è¦å¯¹åˆ†ç»„æ•°æ®è¿›è¡Œè¿‡æ»¤ï¼Œæ­¤æ—¶å°±éœ€è¦ä½¿ç”¨ HAVING。WHERE 是作用于数æ®è¡Œï¼Œè€Œ HAVING 是作用于分组。
SELECT COUNT(*) as num, role_main, role_assist
FROM heros
WHERE hp_max > 6000
GROUP BY role_main, role_assist
HAVING num > 5
ORDER BY num;
The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.
SQL 中关键字是有顺åºçš„:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

Subquery

官方文档,Subquery Syntax。
éžå…³è”å­æŸ¥è¯¢ï¼šå­æŸ¥è¯¢åªæ‰§è¡Œä¸€æ¬¡ï¼ŒæŸ¥è¯¢çš„æ•°æ®ç»“果作为主查询的æ¡ä»¶ã€‚如:
# 查询最高身高的çƒå‘˜ï¼ŒæŸ¥è¯¢æœ€é«˜èº«é«˜åªæ‰§è¡Œä¸€æ¬¡ã€‚
SELECT player_name, height
FROM player
WHERE height = (SELECT max(height) FROM player);
å…³è”å­æŸ¥è¯¢ï¼šå­æŸ¥è¯¢éœ€è¦æ‰§è¡Œå¤šæ¬¡ï¼Œå³é‡‡ç”¨å¾ªçŽ¯çš„æ–¹å¼ï¼Œå…ˆä»Žå¤–é¢å¼€å§‹ï¼Œæ¯æ¬¡éƒ½ä¼ å…¥å­æŸ¥è¯¢ï¼Œæœ€åŽå°†ç»“æžœå馈给外部。如:
# 查询æ¯ä¸ªçƒé˜Ÿä¸­å¤§äºŽå¹³å‡èº«é«˜çš„çƒå‘˜ï¼ŒæŸ¥è¯¢çƒé˜Ÿçš„å¹³å‡èº«é«˜éœ€è¦æ‰§è¡Œå¤šæ¬¡ã€‚
SELECT player_name, height, team_id
FROM player AS a
WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id);
å­æŸ¥è¯¢è¿˜èƒ½ä½œä¸ºä¸»æŸ¥è¯¢çš„计算字段:
# 查询æ¯ä¸ªçƒé˜Ÿçš„çƒå‘˜æ•°é‡
SELECT team_name,
(SELECT count(*) FROM player WHERE player.team_id = team.team_id)
AS player_num
FROM team;

EXISTSã€NOT EXISTS

官方文档:Subqueries with EXISTS or NOT EXISTS。
å¦‚æžœå­æŸ¥è¯¢è¿”回任何行,则 EXISTS 为 true。
# 查询有出场纪录的çƒå‘˜
SELECT player_id, team_id, player_name
FROM player
WHERE EXISTS(SELECT player_id FROM player_score WHERE player.playe_id = player_score.player_id);

ANYã€INã€SOMEã€ALL

官方文档:Subqueries with ANY, IN, or SOME,Subqueries with ALL。语法:
operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
​
comparison_operator:
= > < >= <= <> !=
  • IN: 是å¦åœ¨é›†åˆä¸­ã€‚
  • ANY: ä¸Žå­æŸ¥è¯¢è¿”回的任æ„一个值比较为真,则整体为真。
  • ALL: ä¸Žå­æŸ¥è¯¢è¿”回的所有值比较。
  • SOME: 是 ANY 的别å,一般用 ANY。
如上文 EXISTS 查询有出场纪录的çƒå‘˜ä¹Ÿå¯å†™æˆï¼š
SELECT player_id, team_id, player_name
FROM player
WHERE player_id IN (SELECT player_id FROM player_score);
å³ EXISTS å’Œ IN 的查询结果å¯ç­‰ä»·ï¼š
SELECT * FROM A WHERE cc IN (SELECT cc FROM B);
# 先执行å­å¥ï¼Œåœ¨æ‰§è¡Œå¤–层 SQL
for i in B
for j in A
if j.cc == i.cc then ...
​
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)
# 对 A 表éåŽ†ï¼Œæ¯æ¡è®°å½•执行å­å¥è¿›è¡Œåˆ¤æ–­
for i in A
for j in B
if j.cc == i.cc then ...
查询结果等价,但是查询效率ä¸ç­‰ä»·ã€‚在上é¢çš„例å­ä¸­ï¼Œå‡è®¾ cc 列建立的索引。若 A 表较大,那么 IN 的效率较高;å之,EXISTS 效率较高。
# 查询比(çƒé˜Ÿ 1002 中任æ„一个çƒå‘˜èº«é«˜)高的çƒå‘˜
SELECT player_id, player_name, height
FROM player
WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002);
​
# 查询比(çƒé˜Ÿ 1002 中所有çƒå‘˜èº«é«˜)都高的çƒå‘˜
SELECT player_id, player_name, height
FROM player
WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002);

JOIN

上文æåˆ° SQL 常用的两个标准是 SQL92 å’Œ SQL99,两个标准的连接查询语法也ä¸ä¸€æ ·ã€‚

CROSS JOIN

实际上就是求两个表的笛å¡å°”积,å³è¡Œæ•° = A 行数 * B 行数。
# SQL99
SELECT * FROM player CROSS JOIN team;
SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3;
​
# SQL92
SELECT * FROM player, team;

NATURAL JOIN

帮你自动查询两张表中所有相åŒçš„字段,然åŽè¿›è¡Œç­‰å€¼è¿žæŽ¥ã€‚
# SQL99
SELECT player_id, team_id, player_name, height, team_name
FROM player
NATURAL JOIN team;
​
# SQL92
SELECT player_id, a.team_id, player_name, height, team_name
FROM player AS a,
team AS b
WHERE a.team_id = b.team_id;
若表使用了别å,那么查询字段中åªèƒ½ä½¿ç”¨åˆ«å,ä¸èƒ½ä½¿ç”¨åŽŸæœ‰çš„è¡¨å。

ON

ç”¨æ¥æŒ‡å®šè¿žæŽ¥æ¡ä»¶ã€‚
# SQL99
SELECT player_id, player.team_id, player_name, height, team_name
FROM player
JOIN team ON player.team_id = team.team_id;
SELECT p.player_name, p.height, h.height_level
FROM player as p
JOIN height_grades as h ON height BETWEEN h.height_lowest AND h.height_highest;
# SQL92
SELECT p.player_name, p.height, h.height_level
FROM player AS p,
height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest;

USING

用于指定数æ®è¡¨ä¸­çš„åŒå字段进行等值连接。å¯ä»¥ç®€åŒ– JOIN ON 的等值连接。
# SQL99
SELECT player_id, team_id, player_name, height, team_name
FROM player
JOIN team USING (team_id);

LEFT [OUTER] JOIN

å·¦è¾¹æ˜¯ä¸»è¡¨ï¼Œéœ€è¦æ˜¾ç¤ºå·¦è¾¹è¡¨çš„全部行。
# SQL99
SELECT *
from player
LEFT JOIN team USING (team_id);
​
# SQL 92, MySQL 䏿”¯æŒ
SELECT *
FROM player,
team
where player.team_id = team.team_id(+);

RIGHT [OUTER] JOIN

å³è¾¹æ˜¯ä¸»è¡¨ï¼Œéœ€è¦æ˜¾ç¤ºå³è¾¹è¡¨çš„全部行。
# SQL99
SELECT *
FROM player
RIGHT JOIN team USING (team_id);
​
# SQL 92, MySQL 䏿”¯æŒ
SELECT *
FROM player,
team
where player.team_id(+) = team.team_id;

FULL [OUTER] JOIN

MySQL䏿”¯æŒã€‚全外连接的结果 = å·¦å³è¡¨åŒ¹é…çš„æ•°æ® + 左表没有匹é…åˆ°çš„æ•°æ® + å³è¡¨æ²¡æœ‰åŒ¹é…到的数æ®ã€‚
SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id;

自连接

è‡ªè¿žæŽ¥çš„é€Ÿåº¦æ¯”å­æŸ¥è¯¢å¿«å¾ˆå¤šï¼Œæ‰€ä»¥å»ºè®®å°½é‡ä½¿ç”¨è‡ªè¿žæŽ¥ã€‚
# 查看比布雷克·格里芬身高高的çƒå‘˜éƒ½
SELECT b.player_name, b.height
FROM player as a
JOIN player as b ON a.player_name = '布雷克-格⾥芬' and a.height < b.height;

Functions

官方文档:Functions and Operators。
SQL 中的函数有很多类别,常用的有:
使用函数å¯èƒ½å¸¦æ¥çš„问题:
  • ä¸åŒ DBMS 支æŒçš„函数ä¸ä¸€æ ·ï¼Œç§»æ¤å®¹æ˜“产生兼容性问题;
  • 容易使用ä¸å½“,导致查询ä¸ä½¿ç”¨ç´¢å¼•ï¼›

COUNT 效率

结论:COUNT(*) = COUNT(1) > COUNT(col)
  • COUNT(*) å’Œ COUNT(1) 都是针对对所有结果,若没有 WHERE 则是所有行,若有则是所有符åˆç­›é€‰æ¡ä»¶çš„行。因此两者都是 O(N),采用全表扫æï¼Œå¾ªçޝ+计数。
  • 但若是 MyISAM å¼•æ“Žï¼Œåˆ™å¤æ‚度为 O(1),因为 MyISAM 表有 meta ä¿¡æ¯ï¼Œå­˜å‚¨äº† row_count,一致性由表级é”ä¿è¯ã€‚InnoDB 支æŒäº‹åŠ¡ï¼Œé‡‡ç”¨è¡Œçº§é”å’Œ MCC 机制,所以没法维护 row_countï¼Œéœ€è¦æ‰«æå…¨è¡¨ã€‚
  • 使用 InnoDB 时,若采用 COUNT(*) å’Œ COUNT(1),尽é‡åœ¨è¡¨ä¸Šå»ºç«‹äºŒçº§ç´¢å¼•,因为主键是èšç°‡ç´¢å¼•,包å«çš„ä¿¡æ¯è¾ƒå¤šã€‚这两个ä¸éœ€è¦æŸ¥æ‰¾å…·ä½“行,åªéœ€è¦è¡Œæ•°ï¼Œç³»ç»Ÿä¼šè‡ªåŠ¨ä½¿ç”¨ key_len å°çš„二级索引,若没有二级索引,æ‰ä¼šä½¿ç”¨ä¸»é”®ç´¢å¼•。
  • è‹¥è¦ä½¿ç”¨å…·ä½“的行,采用主键索引效率更高。

Cursor

官方文档,cursors。
DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH cursor_name INTO var_name ...;
CLOSE cursor_name;
DEALLOCATE PREPARE;

Utility Statements

# 获å–表信æ¯
DESCRIBE {tb_name};
DESCRIBE app;
​
# 获å–版本
SELECT version();
​
# 显示所有引擎
SHOW engines;
åˆ†æž SQL 执行时间:
# 查看是å¦å¼€å¯ï¼Œå…³é—­(0),开å¯(1)
SELECT @@profiling;
​
# å¼€å¯
SET profiling = 1;
​
# 执行任æ„语å¥
SELECT * FROM test;
​
# 查看当å‰ä¼šè¯æ‰€äº§ç”Ÿçš„ profiles
SHOW profiles;
​
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00185800 | select @@profiling |
| 2 | 0.00108000 | select * from test |
+----------+------------+--------------------+
2 rows in set, 1 warning (0.00 sec)
​
# 获å–上一次查询的详细执行时间
SHOW profile;
​
# 查询指定 queryId 的详细执行时间
SHOW profile FOR query 2;
​
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000112 |
| Executing hook on transaction | 0.000022 |
| starting | 0.000023 |
| checking permissions | 0.000021 |
| Opening tables | 0.000065 |
| init | 0.000022 |
| System lock | 0.000046 |
| optimizing | 0.000018 |
| statistics | 0.000032 |
| preparing | 0.000033 |
| executing | 0.000056 |
| end | 0.000018 |
| query end | 0.000016 |
| waiting for handler commit | 0.000036 |
| closing tables | 0.000022 |
| freeing items | 0.000293 |
| cleaning up | 0.000249 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.01 sec)
​