SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
SQL: SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)
笛卡尔面积：
SQL: SELECT * FROM player, team
等值相连：
SQL: SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id
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
非等值相连：
SQL：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
外连接：
左链接：(sql92(+)代表从表)
SQL：SELECT * FROM player, team where player.team_id = team.team_id(+)
SQL：SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id
SQL：SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id
自连接：
SQL：SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克 - 格里芬' and a.height < b.height
交叉连接：
实际就是笛卡尔面积：
SQL: SELECT * FROM player CROSS JOIN team
SQL: SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3
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
SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team
ON:
SQL99：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
using:
等名等值连接:
SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id)
left join = left outer join
right join = right outer join
full join = full outer join
全外连接：
实际就是左右连接的结合：
full join
SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id
SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克 - 格里芬' and a.height < b.height
SELECT b.player_name, b.height FROM player as a JOIN player as b ON a.player_name = '布雷克 - 格里芬' and a.height < b.height
交叉连接：也称为笛卡尔积，返回左表中每一行与右表中每一行的组合。在 SQL99 中使用的 CROSS JOIN。
视图：
简化复杂的sql
CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition
修改视图：
ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition
删除视图：
DROP VIEW view_name
存储过程：
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 //
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 ;
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
out 返回的值
in 传入参数
inout 即可传入参数，又可返回的值
事务：
acid:
a 原子性
c 一致性
i 隔离性
d 持久性
mysql> set autocommit =0; // 关闭自动提交
mysql> set autocommit =1; // 开启自动提交
你有没有发现什么异常？这个时候小张还没有提交事务，但是小李却读到了小张还没有提交的数据，这种现象我们称之为“脏读”。
这个时候你会发现，两次查询的结果并不一样。小张会想这是怎么回事呢？他明明刚执行了一次查询，马上又进行了一次查询，结果两次的查询结果不同。实际上小张遇到的情况我们称之为“不可重复读”，也就是同一条记录，两次读取的结果不同。
他发现这一次查询多了一个英雄，原来只有 3 个，现在变成了 4 个。这种异常情况我们称之为“幻读”。
脏读：读到了其他事务还没有提交的数据。
不可重复读：对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除。
幻读：事务 A 根据条件查询得到了 N 条数据，但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候发现会有 N+M 条数据，产生了幻读。
读未提交，也就是允许读到未提交的数据，这种情况下查询是不会使用锁的，可能会产生脏读、不可重复读、幻读等情况。
读已提交就是只能读到已经提交的内容，可以避免脏读的产生，属于 RDBMS 中常见的默认隔离级别（比如说 Oracle 和 SQL Server），但如果想要避免不可重复读或者幻读，就需要我们在 SQL 查询的时候编写带加锁的 SQL 语句（我会在进阶篇里讲加锁）。
可重复读，保证一个事务在相同查询条件下两次查询得到的数据结果是一致的，可以避免不可重复读和脏读，但无法避免幻读。MySQL 默认的隔离级别就是可重复读。
可串行化，将事务进行串行化，也就是在一个队列中按照顺序执行，可串行化是最高级别的隔离等级，可以解决事务读取中所有可能出现的异常情况，但是它牺牲了系统的并发性。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
游标：
DECLARE cur_hero CURSOR FOR
SELECT hp_max FROM heros;
FETCH cursor_name INTO var_name ...
CLOSE cursor_name
释放游标：
DEALLOCATE PREPARE
CREATE PROCEDURE `alter_attack_growth`()
BEGIN
-- 创建接收游标的变量
DECLARE temp_id INT;
DECLARE temp_growth, temp_max, temp_start, temp_diff FLOAT;
-- 创建结束标志变量
DECLARE done INT DEFAULT false;
-- 定义游标
DECLARE cur_hero CURSOR FOR SELECT id, attack_growth, attack_max, attack_start FROM heros;
-- 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
OPEN cur_hero;
FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
REPEAT
IF NOT done THEN
SET temp_diff = temp_max - temp_start;
IF temp_growth < 5 THEN
IF temp_diff > 200 THEN
SET temp_growth = temp_growth * 1.1;
ELSEIF temp_diff >= 150 AND temp_diff <=200 THEN
SET temp_growth = temp_growth * 1.08;
ELSEIF temp_diff < 150 THEN
SET temp_growth = temp_growth * 1.07;
END IF;
ELSEIF temp_growth >=5 AND temp_growth <=10 THEN
SET temp_growth = temp_growth * 1.05;
END IF;
UPDATE heros SET attack_growth = ROUND(temp_growth,3) WHERE id = temp_id;
END IF;
FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
UNTIL done = true END REPEAT;
CLOSE cur_hero;
DEALLOCATE PREPARE cur_hero;
END