mysql 基本命令

命令分类

• SQL 语句可以分为以下三类:
  • DDL(data definition language): CREATE, DROP, ALTER
    • 用来创建或者删除数据库及数据表等对象
  • DML(data manipulation language): SELECT, INSERT, UPDATE, DELETE
    • 用来查询或者变更表中的记录
    • 实际使用的 SQL 语句当中有 90% 属于 DML
  • DCL(data control language): COMMIT, ROLLBACK, GRANT, REVOKE
    • 用来确认或者取消数据变更,以及对用户是否有权限操作数据库中的对象进行设定

通用规范

• SQL 语句大小写：
  • 不区分： 关键字、列名及其别名
  • 区分： 库名、表名及其别名、插入到表中的数据
  • 但在Windows下，都不区分大小写
• SQL 语句中直接书写的字符串、日期或者数字等称为常数
  • 日期和字符串 必须用单引号括起来
    • 例如'abc', '2010-01-26'
  • 但数字常数不需要使用单引号括起来
• MySQL中的标准日期格式为yyyy-mm-dd
• 注释
  • 单行：-- xxx
  • 多行：/* xxx */
  • 注释可以插在SQL语句的中间

DDL

• 在同一个数据库中不能创建两个相同名称的表，在同一个表中也不能创建两个名称相同的列
• 只能使用英文字母、数字、下划线(_) 作为 数据库、表和列 的名称
  • 且必须以英文字母开头
• 表名和列名一般用反引号括起来(可以省略)
  • 用于区分于MySQL的保留字符
• 数据类型包括：数字型（INTERGER, FLOAT等），字符型（CHAR, VARCHAR, TEXT等），日期型（DATE, YEAR等）
  • 所有的列都必须指定数据类型
  • 不能存储与该列数据类型不符的数据
  • 数值型数据会省略前导0，因此最好不要将手机号或邮编用数值型存储
  • 货币可以用有特定取值范围的MONEY|CURRENCY类型数据存储
    • MySQL中没有这种专用类型，所以一般使用DECIMAL(8,2)来存储
• 一个表只能有一个自增长列，且该列必须被索引
  • SELECT last_insert_id()返回最后插入的自增ID值
• 增加约束和索引

-- 建表时添加
CREATE TABLE [IF NOT EXISTS] t1(
	`id` int NOT NULL AUTO_INCREMENT, 
	`age` smallint NOT NULL,
    `name` varchar(255) NOT NULL,
	`gender` char(10) NOT NULL DEFAULT 'M',
	`job_id` int NOT NULL,
	PRIMARY KEY(`id`) USING BTREE, -- 主键约束
    CONSTRAINT FK_t1_t2 FOREIGN KEY (`job_id`) REFERENCES t2(`other_id`), -- 外键约束
	CONSTRAINT UNI_NAME UNIQUE(`name`), -- 唯一[索引]约束
    CONSTRAINT CEHCK_GENDER CHECK(gender LIKE '[MF]'),  -- 检查约束
	INDEX IDX_AGE(`age`) -- 普通索引
)ENGINE=InnoDB CHARSET=utf8;

-- 建表后添加
ALTER TABLE <table_name> ADD CONSTRAINT PRIMARY KEY(`id`);
ALTER TABLE <table_name> ADD CONSTRAINT FK_t1_t2 FOREIGN KEY (`age`) REFERENCES <other_table>(`other_id`);
ALTER TABLE <table_name> ADD CONSTRAINT CHECK(`gender` LIKE '[MF]');
ALTER TABLE <table_name> ADD CONSTRAINT UNI_NAME UNIQUE(`name`);
ALTER TABLE <table_name> ADD INDEX <index_name>(`<name>`);

• 一个数据库中的不同表可以混用不同的底层引擎
  • 但是外键不能跨引擎
• 复制数据表:
  • CREATE TABLE xxx AS SELECT * FROM yyy只能复制数据，无法复制索引、外键·、触发器等设置
  • 想同时复制上述额外设置，需要执行下列步骤：
    • 通过CREATE TABLE xxx LIKE yyy复制表结构和索引等设置
    • 通过INSERT INTO xxx SELECT * FROM yyy复制数据
      • 此时使用的是SELECT中列的位置，不一定要求和INSERT的列名匹配
• 列出当前数据库的所有表，使用命令：SHOW TABLES;
• 要查看一个表的结构，使用命令: DESCRIBE xxx;
• 查看创建表/库的SQL语句:SHOW CREATE [TABLE|DATABASE] xxx;

DML

• SQL语句顺序
  • 书写： SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT
  • 执行：FROM -> WHERE -> GROUP BY -> 计算聚合函数 -> HAVING -> 计算表达式 -> SELECT -> ORDER BY -> LIMIT
• LIMIT的OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集
• DISTINCT 会将后面所有列视为一个组合，将重复的组合合并为一条
  • 只能用在第一个列名之前，不能加在中间
• 不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效
  • 许多检测工具会执行一条SELECT 1;来测试数据库连接
• 如果我们知道如何选择索引，可以使用FORCE INDEX强制使用指定的索引来查询
• 全文搜索利用了倒排索引原理

NULL相关的特殊处理

• NULL不是''
• 所有对于NULL的运算符计算结果都是NULL
• NULL无法进行常规比较，只能通过IS (NOT) NULL判断
• ORDER BY时NULL会在结果的开头或末尾汇总
• GROUP BY时为NULL的行都会归一个独立的分组
• IN和NOT IN的结果中都不会包含NULL
  • NOT IN的参数中不能包含NULL, 否则结果为空
• 匹配值为NULL的行不能使用= NULL，只能使用IS NULL
  • 匹配NULL意外的行不能使用<> NULL，只能使用IS NOT NULL

结果排序

• 默认的排序规则是ASC(升序)，即从小到大
• ORDER BY子句中可以使用SELECT子句中未出现的列，甚至是聚合函数
• ORDER BY子句中也可以使用SELECT中定义的别名
• 使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序

运算符

• 默认条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR
  • 加上括号可以改变优先级
• 对字符串类型的数据进行大小比较时，使用的是和数字比较不同的规则
  • 按照字典顺序进行比较
  • 例如'2' > '11','1-3' < '2'
• x IN(a,b,c)等价于x=a OR x=b OR x=c
• 不仅可以单个值使用IN，多个值的组合也可以使用
  • 例如where (name, age) IN ...
• 使用LIKE进行模糊查询：
  • %匹配0,1或以上的任意字符串
    • 但是不能匹配NULL
  • _匹配任意1个字符
    • _ _ 匹配任意2个连续字符
  • []匹配指定字符集
• 使用 OR 会使索引会失效，在数据量较大的时候查找效率较低，通常建议使用 UNION 代替 OR

控制语句

• if表达式：IF(expr1,expr2,expr3)
  • 写法类似三元操作符
  • mysql独有，可以用case表达式替换
• case表达式分为简单表达式和搜索表达式
  • 前者类似于SWITCH, 后者类似于IF
• case表达式的ELSE部分不写时默认为ELSE NULL
• 当我们需要通过表中并不存在的分类条件进行分割时，可以使用case表达式

子查询

• 子查询就是一次性视图，在 SELECT 语句执行完毕之后就会消失
• 作为子查询的SELECT 语句只能查询单个列
• 子查询既可以出现在WHERE子句中，也可以出现在SELECT子句中
  • 后者会对每一个select出来的行进行子查询
• 标量子查询： 只返回 1 行 1列的结果，即一个值
  • 可以用在 = 或者 <> 这样需要单一值的比较运算符之中
    • 例如WHERE子句中不能使用聚合函数，此时可以改为标量子查询
  • 可用在任何需要标量的地方，例如WHERE子句、SELECT 子句、GROUP BY 子句、HAVING 子句、 ORDER BY子句
• 关联子查询： 在子查询中含有WHERE 子句且与外部表中的项进行比较
  • 关联子查询和GROUP BY子句有一样作用，即可以对集合进行切分
    • 因此关联子查询内部不需要再GROUP BY了
  • 在使用关联子查询时，需要在表所对应的列名之前加上表名
  • WHERE子句只能加在子查询而不是外部查询中
    • 因为运行外部查询时内部子查询的表已经不存在了（只保留了查询完成的数据）

• 大部分子查询SQL中IN和EXISTS可以互相替换
  • 此时IN的列名会转变成EXISTS子句中WHERE的条件
  • 不同于IN，EXISTS的左侧没有参数，此时的参数实际是WHERE中的条件
    • 由于EXIST只关心WHERE中的列，因此可以SELECT任意项；例如SELECT *, SELECT 1
  • 如果外表行数小于内表，则使用IN进行子查询；反之则使用EXISTS实现
    • 因为前者先循环内表再循环外表；后者先循环外表再循环内表

-- 使用 IN
SELECT * FROM A WHERE id IN (SELECT id FROM B)
-- 执行逻辑
for i in B
	for j in A
		if j.id == i.id then ...

-- 使用 EXISTS
SELECT * FROM A EXISTS (SELECT id FROM B WHERE B.id == A.id)
-- 执行逻辑
for i in A
	for j in B
		if j.id == i.id then ...

• 同过WITH <name> AS (...)可以在一次SELECT查询中增加一个临时表
  • 后续多个子查询都可以共用这个临时表
  • 同一个SELECT前可以有多个临时表
    • 写一个with就可以，用逗号隔开，最后一个with语句不要用逗号
  • MySQL版本需要>= 8.0

连接（JOIN）

• 集合运算会导致结果集行的增减，而连接运算会导致列的增减
  • 也就是可以将其他表中的列添加过来
    
• 连接发生在FROM子句里，可以同时连接多个表
• 多表联结时，某个列到底属于哪张表比较容易混乱，因此需要采用<表名>.< 列名>的形式来指定列
  • 这只针对同时存在于多个表的列
  • 如果该列只存在于其中一个表，则可以不加上表名前缀
• CROSS JOIN结果是两表的笛卡尔积，且不能使用ON语句
  • 结果中的记录数通常是两张表中行数的乘积
• INNER JOIN也叫等值连接
  • A INNER JOIN B等价于A, B等价于A JOIN B
  • INNER JOIN逻辑上等价于 CROSS JOIN + ON
• WHERE和ON的区别
  • 内连接中，ON 对整行进行保留或者过滤
    • 此时WHERE和ON的作用实际是一样的，可以替换
    • 也就是说过滤条件不论是放在WHERE 条件还是ON .. AND 条件中都可以过滤
  • 外连接中，ON对另一侧表的记录进行过
    • 此时ON不会过滤驱动表的数据，只有WHERE能够过滤
      • ON是在生成临时表时使用的条件，它不管ON中的条件是否为真，都会返回驱动表中的记录
      • WHERE是在临时表生成好后，再对临时表进行过滤的条件，条件不为真的就全部过滤掉
  • 参考：https://www.zhihu.com/question/34559578/answer/729460750
• RIGHT OUTER JOIN一定会返回所有右表存在的行，LEFT OUTER JOIN则一定会返回所有左表存在的行
  • 如果某一行仅在右/左表存在，那么结果集就会以NULL填充剩下的字段
• FULL OUTER JOIN 会把两张表的所有记录全部选择出来，并且自动把对方不存在的列填充为NULL
  • MySQL没有 FULL OUTER JOIN , 但是可以使用LEFT JOIN + RIGHT JOIN + UNION实现

函数

• 分类： 算数函数、字符串函数、日期函数、转换函数、聚合函数
• 使用函数可能会导致查询不走索引，直接全表遍历，进而造成慢查询
  • 一般禁止where子句中出现函数
• 常用字符串函数：
  • CONCAT(str1, str2, str3)：拼接字符串
    • 如果其中包含NULL，结果为NULL
  • LENGTH(str)：字符串的字节长度
    • 中文占2个字节
  • CHAR_LENGTH(str): 字符串的字符长度
    • 中文也算一个字符
  • REPLACE( str, old, new): 将str的old部分替换为new
  • SUBSTRING(str FROM start FOR step)：从左起第start个字节开始截取step个字符
    • 如果开始位置在某字符中间，则视为从该字符开始截取
    • 例如SUBSTRING('我是谁' FROM 1 FOR 3)截取到的是'我是谁'
  • LOWER(str):把所有英文字母大写
    • 不适用于英文外的字符
• 常用日期函数
  • CURRENT_DATE: 返回该SQL执行的日期
    • 无参数，不需要使用括号
  • EXTRACT( [YEAR|MONTH|DAY|HOUR|MINuTE|SECOND] FROM <TS>: 截取部分日期元素
  • YEAR(order_date) = 2005 AND Month(order_date) = 9：找出所有2005年9月的订单
    • 不需要记住一个月有几天以及是否是闰月
• 常用转换函数
  • CAST( str AS [SIGNED INTEGER| DATE]: 类型转换
    • 一般用于MySQL的内部处理
  • CASTESCE(a,b,c)：将NULL依次转换为后续的非NULL值
    • 如果a为NULL则转化为b,如果b也为NULL则接着转化为c
    • 如果都为NULL则返回NULL

聚合查询

• WHERE过滤行，HAVING过滤分组
• 使用GROUP BY聚合查询时，在SELECT和HAVING子句中只能使用：聚合函数、GROUP BY中使用的列名、常数
  • 除此之外的列名会导致语法错误
• GROUP BY子句中不能使用SELECT中定义的别名
  • 因为实际执行时, GROUP BY在SELECT前执行
• 聚合查询的WHERE条件没有匹配到任何行时, SUM()、AVG()、MAX()和MIN()函数会返回NULL
• GROUP BY会把所有值为NULL的行聚合成单独的一组
• GROUP BY子句中可以存在多个列
  • GROUP BY X是将所有具有相同X字段值的记录放到一个分组里
  • GROUP BY X, Y是将所有具有相同X字段值和Y字段值的记录放到一个分组里

聚合函数

• 聚集函数一般只包括以下五个：COUNT, SUM, AVG, MAX, MIN
• 聚集函数会自动忽略值为 NULL 的数据行
• COUNT(*)除外,他会统计所有行
• 如果该列所有行都是NULL的话，SUM, AVG, MAX, MIN会返回NULL
• MAX(), MIN()函数适用于任何数据类型的列，而SUM(), AVG() 函数只能对数值类型的列使用
  • 例如字符类型，MAX()和MIN()会返回排序最后和排序最前的字符
• 使用DISTINCT可以在聚合函数计算前剔除重复值
  • 例如COUNT(DISTINCT xxx), SUM(DISTINCT yyy)
• 聚合函数的参数可以是包含多个列的算数表达式
  • 例如SUM(A+B), SUM(A*B)

集合运算

• 集合运算以行为单位进行操作，会导致结果行数的增减
  • 使用UNION 会增加记录行，而使用INTERSECT或者EXCEPT 会减少记录行
• 使用场景：
  • 单次查询中从不同表查询类似结构的数据
  • 在单个表执行多次查询，并按单次查询返回数据
• 优先级：INTERSCT > UNION = EXCEPT

SELECT product_id, product_name
FROM Product
[UNION|INTERSECT|EXCEPT]
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

• 做集合运算时，每个查询必须包含相同的列、表达式或聚集函数
  • 不过各个列不需要以相同的次序列出
• 做集合运算时，ORDER BY 只能在最后被使用一次
• 集合运算会自动除去重复的记录
  • 通过[UNION|INTERSECT] ALL可以保留重复记录
• A EXCEPT B和B EXCEPT A结果是不一样的
  • 前者是A中剔除B的部分，后者相反
  • 而UNION 和INTERSECT 不在乎顺序

插入、更新与删除

• 如果WHERE条件没有匹配到任何记录，UPDATE和DELETE语句不会报错，但也不会有任何记录被更新/删除
• UPDATE和DELETE语句可以没有WHERE条件，此时整个表的所有记录都会被更新/删除
  • 所以最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再执行UPDATE或DELETE语句
• DELETE 用于删除行，删除列用UPDATE
• 条件插入
  • REPLACE：如果记录已经存在，就先删除原记录，再插入新记录
  • INSERT INTO ... ON DUPLICATE KEY UPDATE ...：如果记录已经存在就更新该记录
  • INSERT IGNORE INTO ...：如果记录已经存在，就啥事也不干直接忽略
    • UPDATE也可以同样操作
• 查询并插入：INSERT INTO ... SELECT ... FROM...
• 低优先级插入、更新与删除：INSERT LOW_PRIORITY INTO, UPDATE LOW_PRIORITY, DELETE LOW_PRIORITY
  • 因为插入、更新与删除操作往往很耗时（需要更新索引）且往往查询操作的性能更重要

窗口函数（version >= mysql 8.0)

• 窗口函数就是为了实现 OLAP 而添加的
• 窗口函数只能在SELECT子句中使用
  • 因为窗口函数是对WHERE 子句或者GROUP BY 子句处理后的“结果”进行的操作

<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)

• 语法规则
  • PARTITION BY 能够设定排序的对象范围
  • ORDER BY 能够指定按照哪一列、何种顺序进行排序
    • 会默认按照ASC排序
  • PARTITION BY 在横向上对表进行分组，而ORDER BY决定了同一组内纵向排序的规则
  • PARTITION BY 可以省略，此时全表按照ORDER BY排序
    
• 窗口函数兼具GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能
  • 但PARTITION BY并不具备GROUP BY的汇总功能,因此使用RANK 函数并不会减少原表中记录的行数
• OVER 子句中的ORDER BY决定窗口函数按照什么样的顺序进行计算，对结果的排列顺序并没有影响
  
• 专用窗口函数：
  • RANK(): 排序，如果存在相同位次的记录，则会跳过之后的位次
  • DENSE_RANK(): 排序，即使存在相同位次的记录，也不会跳过之后的位次
  • ROW_NUMBER(): 赋予唯一的连续递增排名
  • 专用窗口函数无需任何参参数
    
• SUM(列名): 累计
  
• AVG(列名): 计算累计均值
  
• AVG (列名) OVER (ORDER BY 列名 ROWS <n> [PRECEDING|FOLLOWING]): 计算(前|后）最近的n行的移动平均值
  
  
• 通过GROUP BY <列名1, 列名2> WITH ROLLUP可以额外增加合计结果
  

DCL

事务

• 事务用来管理INSERT、UPDATE 和DELETE 语句
• ROLLBACK不能回退SELECT、CREATE、DROP 操作
  • 可以在事务中使用，但是回滚后并不不会撤销
• 使用ROLLBACK 和COMMIT语句，会一次性写入或撤销整个事务,但复杂的事务可能需要部分提交或回退
  • 要支持回退部分事务，必须在事务处理块中的合适位置放置占位符SAVEPOINT
    • 这样如果需要回退，可以回退到某个占位符
  • 占位符会在事务结束后自动销毁

START TRANSACTION;
...
SAVEPOINT <name>;
...
ROLLBACK TO <name>;
...
COMMIT| ROLLBACK;

权限管理

show grants [for root@localhost]; -- 查看用户的权限(默认当前用户）
select user, Host from mysql.user; -- 查看数据库的用户及其允许登录的地址

不常用命令

外键

• 外键是表中的一列，其值必须在另一表的主键中
• 外键主要用于保证引用完整性，也有助于避免以外删除
• 实际生产环境更多在业务层使用冗余字段取代外键
  • 外键存在的问题：
    • 在数据大到需要分库分表时，无法使用外键约束
    • 外键会导致表与表之间耦合，高并发时，外键的存在会增加耗时，还可能死锁
      • 因为插入、更新和删除都需要检查另一张表的数据
  • 业务层实现外键的问题：
    • 业务逻辑和数据耦合，也就是两者必须同时修改
• 外键不能跨引擎
  • 使用一种引擎的表不能应用另一种引擎的表的外键

视图

• 视图是虚表，是从其他实表（有存放数据的表）或其他视图经过重新查询整理出来的表
  • 视图不涉及数据存储，这些数据仍存放在原来的基本表中，所以视图不会占用磁盘空间
  • 视图可以视为对 SQL 查询语句的提前封装
  • 基本表中的数据发生变化，从视图中查询出的数据也就随之改变
  • 当我们创建好一张视图之后，还可以在它的基础上继续创建视图
  • 操作视图的很多命令都与普通表一样
    • 在业务代码中无法通过sql区分表和视图
• 视图是虚表，而临时表是真实存在的数据表
  • 不过它不用于长期存放数据，关闭当前连接后，临时表就会自动释放
• 优点：
  • 视图可以只给使用方暴露原始表中的部分数据，更好地限制了敏感数据的访问权限，提高了数据的保密性
  • 对于复杂SQL查询，视图隐藏了底层的表结构，隐藏了查询的复杂性，便于终端用户的使用
  • 用户对视图，不可以随意进行更改和删除，因此可以保证原始数据的安全性
  • 可以重新格式化检索出的数据，使其更清晰易读
• 缺点：
  • 在mysql的优化器中，视图的代码执行路径和普通SQL不同，无法直观的预测其执行性能

储存过程

• 存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集
  • 用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它
• 储存过程多被金融、银行等传统领域适用；而互联网企业基本不使用
• 存储过程类似于函数
  • 有参数和返回值
  • 甚至还可以给临时变量赋值，写if else, for循环等控制语句
• 优点：
  • 经过第一次编译后再次调用不需要再次编译，少了parse和查询优化步骤
  • 消除不必要的网络IO，极大地提高了系统性能
    • 特别是大量数据往返的报表、统计类功能
  • 可以将SQL代码封装成模块进行复用，减少开发量
  • 当底层数据库发生模式变更时，可以对上层应用保持透明
  • 设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性
• 缺点：
  • 没有版本控制，版本迭代麻烦
  • 难以调试，复杂业务处理的维护成本高
  • 和数据库强相关，当更换数据库时难以移植，需要重新编写
  • 不适合高并发的场景，不利于分库分表，scale out能力弱

触发器

• 触发器是一种特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发
  • 当触发器所在的表上出现指定事件时（insert，delete， update等）会激活触发器的执行
  • 触发器可在特定操作执行之前或之后执行
• 触发器与单个的表相关联, A表定义的触发器不会在B表被触发
• 常见用途：
  • 避免数据不一致
    • 例如，在删除A表后自动删除相关的B表中的记录
  • 基于某个表的变动在其他表上执行活动
    • 例如，每当更新或删除一行时将审计跟踪记录写入某个日志表
  • 进行额外的验证并根据需要回退数据
    • 例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入
  • 计算某列的值或更新时间戳
• 缺点：
  • 触发器的执行没有反馈和日志，隐藏了实际的执行步骤
    • 当数据库出现问题时，不能通过慢日志分析触发器的具体执行情况，不易发现问题、
  • MySQL中，触发器不能临时关闭或打开
    • 在数据迁移或数据恢复等场景下，需要临时drop触发器，可能影响到生产环境
  • 不能进行版本控制和单元测试

游标

• 编写 SQL 语句的时候通常是面向集合进行思考
  • 这种思考方式更让我们关注结果集的特征，而不是具体的实现过程
  • 让我们关注“获取什么”，而不是“如何获取”
  • 这也可以说是 SQL 与传统编程最大的区别之一
• 游标让 SQL 这种面向集合的语言有了面向过程开发的能力
  • 游标充当了指针的作用，可以指向存储在数据库表中的数据行
  • 游标可以让我们从数据结果集中每次提取一条数据记录进行相同或者不同的操作
• 优点：
  • 可以通过游标更加精细、灵活地查询和管理想要的数据行
• 缺点：
  • 使用游标的过程中，会对数据行进行加锁
  • 游标是在内存中进行的处理，可能造成内存不足
• 游标使用完毕后需要手动释放，否则游标会一直存在于内存中，直到进程结束后才会自动释放

经典题目

• 查出每个班级前3名的同学名字、成绩和班级名

SELECT s.name, s.points, c.name
FROM student s
	INNER JOIN class
    ON s.classid = c.id
WHERE 3 > (
    SELECT COUNT(DISTINCT s2.points)
	FROM student s2
    WHERE s2.points > s.points
    	AND s2.classid = s.classid 
)
ORDER BY classid, s.points DESC;

• 交换性别

update salary
set sex = 
    case sex
        when 'm' then 'f'
        when 'f' then 'm'
    end
;

• 重新格式化部门表

select
    id,
    sum(case when month = 'Jan' then revenue end) as jan_revenue,
    sum(case when month = 'Feb' then revenue end) as feb_revenue,
    sum(case when month = 'Mar' then revenue end) as mar_revenue,
    sum(case when month = 'Apr' then revenue end) as apr_revenue,
    sum(case when month = 'May' then revenue end) as may_revenue,
    sum(case when month = 'Jun' then revenue end) as jun_revenue,
    sum(case when month = 'Jul' then revenue end) as jul_revenue,
    sum(case when month = 'Aug' then revenue end) as aug_revenue,
    sum(case when month = 'Sep' then revenue end) as sep_revenue,
    sum(case when month = 'Oct' then revenue end) as oct_revenue,
    sum(case when month = 'Nov' then revenue end) as nov_revenue,
    sum(case when month = 'Dec' then revenue end) as dec_revenue
from Department
group by id;

• 体育馆连续三天人数超过100人的所有天数

with tmp as (
    select *, id - row_number() over (order by id) as rk
    from Stadium
    where people >= 100
)
select id, visit_date, people
from tmp
where rk in (
    select rk 
    from tmp
    group by rk
    having count(id) >= 3
)
order by visit_date;

• 查找每个部门工资最高的员工
  

select d.name as department, e.name as employee, e.salary 
from Employee as e 
    inner join Department as d
    on e.departmentid = d.id
where (e.salary, e.departmentid) in (
    select max(salary) as salary, departmentid
    from Employee
    group by departmentid
);

• 非禁止用户的取消率计算

select request_at as Day,
    round(
        sum(if(status = 'completed', 0, 1)) / count(status)
        , 2) as `Cancellation Rate`
from Trips as t
    inner join Users as u1
    on t.client_id = u1.users_id and u1.banned = 'No'
        inner join Users as u2
        on t.driver_id = u2.users_id and u2.banned = 'No'
where request_at between '2013-10-01' and '2013-10-03'
group by request_at;