SQL总结：哪些知识点千万别忘？

看完大神的博客和网站，为了方便复习做了这篇学习笔记，原链接都附在文中了。
吾等博客小白，阅读量甚少。但如有侵权请告知删除，支持原创！

一。SQL理论基础

SQL：Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.
访问和处理关系数据库的计算机标准语言

什么是事务？

廖雪峰的官方网站

把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。
使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

什么是隔离级别？

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性。
数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

1.Read Uncommitted
在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

当事务A执行完第3步时，它更新了id=1的记录，但并未提交，而事务B在第4步读取到的数据就是未提交的数据。

随后，事务A在第5步进行了回滚，事务B再次读取id=1的记录，发现和上一次读取到的数据不一致，这就是脏读。

总结：脏读：在写过程中读数，这个数可能不是最终数

2.Read Committed

在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。

不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

当事务B第一次执行第3步的查询时，得到的结果是Alice，随后，由于事务A在第4步更新了这条记录并提交，所以，事务B在第6步再次执行同样的查询时，得到的结果就变成了Bob，因此，在Read Committed隔离级别下，事务不可重复读同一条记录，因为很可能读到的结果不一致。
总结：不可重复读：读到修改前和修改后的数，不一致！

3.Repeatable Read
在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。
幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了

分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：
事务B在第3步第一次读取id=99的记录时，读到的记录为空，说明不存在id=99的记录。随后，事务A在第4步插入了一条id=99的记录并提交。事务B在第6步再次读取id=99的记录时，读到的记录仍然为空，但是，事务B在第7步试图更新这条不存在的记录时，竟然成功了，并且，事务B在第8步再次读取id=99的记录时，记录出现了。

可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

总结：幻读：commit之前和之后读，都没有读到，更新的时候却存在

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

4.Serializable
虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

在MySQL中，如果使用InnoDB，默认的隔离级别是3.Repeatable Read。

什么是ACID？

数据库事务具有ACID这4个特性：

A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

什么是CAP？

Consistency
All clients see the same data at the same time, no matter which node they connect to.
Availability
Any client making a request for data gets a response, even if one or more nodes are down.
Partition Tolerance
(A partition is a communications break within a distributed system—a lost or temporarily delayed connection between two nodes.)
Partition tolerance means that the cluster must continue to work despite any number of communication breakdowns between nodes in the system.

–> A CP database delivers consistency and partition tolerance at the expense of availability.

=============

支持原创！
大神博客：Sql Or NoSql，看完这一篇你就懂了
https://www.cnblogs.com/xrq730/p/11039384.html

1.1结构化数据、非结构化数据与半结构化数据

结构化数据：由二维表结构来逻辑表达和实现的数据，严格遵循数据格式与长度规范，也称作为行数据，特点为：数据以行为单位，一行数据表示一个实体的信息，每一行数据的属性是相同的。
非结构化数据：数据结构不规则或不完整，没有任何预定义的数据模型，不方便用二维逻辑表来表现的数据，
例如办公文档（Word）、文本、图片、HTML、各类报表、视频音频等。

1.2以关系型数据库的方式做存储的架构演进

可以看出，
阶段1:一个应用服务器配一个关系型数据库，每次读写数据库

阶段2:随着企业规模的扩大，一台应用服务器扛不住上游过来的流量，且一台应用服务器会产生单点故障的问题，因此加应用服务器并且在流量入口使用Nginx做一层负载均衡，保证把流量均匀打到应用服务器上。

阶段3:由于读写都在同一个数据库上，数据库性能出现一定的瓶颈，此时简单地做一层读写分离，每次写主库，读备库，主备库之间通过binlog同步数据，就能很大程度上解决这个阶段的数据库性能问题。

阶段4:一台数据库扛不住，那我们就分几台吧，做分库分表，对表做垂直拆分，对库做水平拆分。
以扩数据库为例，扩出两台数据库，以一定的单号（例如交易单号），以一定的规则（例如取模），交易单号对2取模为0的丢到数据库1去，交易单号对2取模为1的丢到数据库2去，通过这样的方式将写数据库的流量均分到两台数据库上。一般分库分表会使用Shard的方式，通过一个中间件，便于连接管理、数据监控且客户端无需感知数据库ip。

1.3关系型数据库的优缺点

优点：结构化易理解，操作方便，数据一致（支持ACID特性，可以维护数据之间的一致性），数据稳定（数据持久化到磁盘），服务稳定。

缺点：
·高并发下IO压力大
数据按行存储，即使只针对其中某一列进行运算，也会将整行数据从存储设备中读入内存，导致IO较高
·为维护索引付出的代价大
为了提供丰富的查询能力，通常热点表都会有多个二级索引，一旦有了二级索引，数据的新增必然伴随着所有二级索引的新增，数据的更新也必然伴随着所有二级索引的更新，这不可避免地降低了关系型数据库的读写能力，且索引越多读写能力越差。
·为维护数据一致性付出的代价大
SQL标准为事务定义了不同的隔离级别，从低到高依次是
读未提交、Read uncommitted
读已提交、Read committed
可重复读、Repeatable Read
串行化 Serializable。（隔离级别越高，读写性能必然越差）
事务隔离级别越低，可能出现的并发异常越多。
为了保证事务一致性，数据库就需要提供并发控制与故障恢复两种技术，前者用于减少并发异常，后者可以在系统异常的时候保证事务与数据库状态不会被破坏。对于并发控制，其核心思想就是加锁，无论是乐观锁还是悲观锁，只要提供的隔离级别越高，那么读写性能必然越差。
·水平扩展后带来的种种问题
分库之后，数据迁移（1个库的数据按照一定规则打到2个库中）、跨库join（订单数据里有用户数据，两条数据不在同一个库中）、分布式事务处理都是需要考虑的问题
·表结构扩展不方便
由于数据库存储的是结构化数据，因此表结构schema是固定的，扩展不方便，如果需要修改表结构，需要执行DDL（data definition language）语句修改，修改期间会导致锁表，部分服务不可用。
·全文搜索功能弱
如like “%中国真伟大%”，只能搜索到"2019年中国真伟大，爱祖国"，无法搜索到"中国真是太伟大了"这样的文本，即不具备分词能力。搜索无法命中索引，将会导致查询效率大大降低。

总结：关系型数据库在高并发下的能力是有瓶颈的，尤其是写入/更新频繁的情况下，出现瓶颈的结果就是数据库CPU高、Sql执行慢、客户端报数据库连接池不够等错误。

1.4结合NoSql的方式做存储

NoSql：Not Only SQL，泛指非关系型数据库，是对关系型数据库的一种补充

图：NoSql应该在DB上面的。 这么画上表示：会把DB的数据放在缓存中的意思。

针对那些读远多于写的数据，引入一层缓存，每次读从缓存中读取，缓存中读取不到，再去数据库中取，取完之后再写入到缓存。

NoSql在性能方面大大优于关系型数据库的同时，往往也伴随着一些特性的缺失，比较常见的就是事务功能的缺失。

KV型NoSql（代表–Redis）

KV型NoSql：以键值对形式存储的非关系型数据库，Redis、MemCache是其中的代表。利用Redis自带的BenchMark做基准测试，TPS可达到10万的级别，性能非常强劲。

优点：高性能

·数据基于内存，读写效率高
·KV型数据，时间复杂度为O(1)，查询速度快
缺点：
·查询方式单一，只有KV的方式（只能根据K查V，无法根据V查K），不支持条件查询，
·内存是有限的，无法支持海量数据存储
·存储是基于内存的，会有丢失数据的风险
适用于：
·读远多于写
·读取能力强
·没有持久化的需求，可容忍数据丢失，丢了再查询一把写入即可

搜索型NoSql（代表–ElasticSearch）

传统关系型数据库主要通过索引来达到快速查询的目的，但是在全文搜索的场景下，索引是无能为力的。搜索型NoSql诞生，解决关系型数据库全文搜索能力较弱的问题。
全文搜索的原理是倒排索引。
先看下什么是正排索引，传统的正排索引是文档–>关键字的映射，例如"Tom is my friend"这句话，会将其切分为"Tom"、“is”、“my”、“friend"四个单词，在搜索的时候对文档进行扫描，符合条件的查出来。这种方式原理非常简单，但是由于其检索效率太低。
倒排索引则完全相反，它是关键字–>文档的映射

Tom这个词语在"Tom is Tom”、“Tom is my friend”、"Tom is Betty’s husband"三句话中都有出现，因此这三条记录都会被检索出来，且由于"Tom is Tom"这句话中"Tom"出现了2次，因此这条记录对"Tom"这个单词的匹配度最高，最先展示。

优点：
·支持分词场景、全文搜索。
·支持条件查询，支持聚合操作，类似关系型数据库的Group By，但是功能更加强大，适合做数据分析。
·数据写文件无丢失风险，在集群环境下可以方便横向扩展，可承载PB级别的数据。
（PB：数据存储容量的单位，它等于2的50次方个字节，1024个TB）
·高可用，自动发现新的或者失败的节点，重组和重新平衡数据，确保数据是安全和可访问的。

缺点：
·性能全靠内存来顶，也是使用的时候最需要注意的点，非常吃硬件资源、吃内存，大数据量下64G + SSD基本是标配，算得上是数据库中的爱马仕。
·读写延迟，写入的数据差不多1s样子会被读取到，这也正常，写入的时候自动加入这么多索引肯定影响性能。
·数据结构灵活性不高，ElasticSearch这个东西，字段一旦建立就没法修改类型了，假如建立的数据表某个字段没有加全文索引，想加上，那么只能把整个表删了再重建。

另外，搜索型数据库还有一种特别重要的应用场景。我们可以想，一旦对数据库做了分库分表后，原来可以在单表中做的聚合操作、统计操作是否统统失效？
例如我把订单表分16个库，1024张表，那么订单数据就散落在1024张表中，我想要统计昨天浙江省单笔成交金额最高的订单是哪笔如何做？我想要把昨天的所有订单按照时间排序分页展示如何做？

搜索型NoSql的另一大作用，我们可以把分表之后的数据统一打在搜索型NoSql中，利用搜索型NoSql的搜索与聚合能力完成对全量数据的查询。

列式NoSql（代表–HBase）

按每一列进行组织的数据。观察id = 2的这条数据，即使phone字段没有，它也是占空间的。

使用列组织数据的好处：
·查询时只有指定的列会被读取，不会读取所有列。
·存储上节约空间，Null值不会被存储，一列中有时候会有很多重复数据（尤其是枚举数据，性别、状态等），这类数据可压缩，行式数据库压缩率通常在3:1~ 5:1之间，列式数据库的压缩率一般在8:1~ 30:1左右。
·列数据被组织到一起，一次磁盘IO可以将一列数据一次性读取到内存中。

优点：
·海量数据无限存储，PB级别数据随便存，底层基于HDFS（Hadoop文件系统），数据持久化
·读写性能好
·横向扩展在关系型数据库及非关系型数据库中都是最方便的之一
·本身没有单点故障，可用性高
·可存储结构化或者半结构化的数据
·列数理论上无限，HBase本身只对列族数量？有要求，建议1~3个

缺点：
·HBase是Hadoop生态的一部分，因此它本身是一款比较重的产品，依赖很多Hadoop组件，数据规模不大没必要用，运维还是有点复杂的
·KV式，不支持条件查询，或者说条件查询非常非常弱吧，HBase在Scan扫描一批数据的情况下还是提供了前缀匹配这种API的，条件查询除非定义多个RowKey做数据冗余
·不支持分页查询，因为统计不了数据总数

适用于
适用于那种KV型的且未来无法预估数据增长量的场景。

文档型NoSql（代表–MongoDB）

是将半结构化数据存储为文档的一种NoSql，文档型NoSql通常以JSON或者XML格式存储数据，因此文档型NoSql是没有Schema的，由于没有Schema的特性，我们可以随意地存储与读取数据，因此文档型NoSql的出现是解决关系型数据库表结构扩展不方便的问题的。
总之：是Free-Schema的关系型数据库

MongoDB是一款完全和关系型数据库对标的产品，就我们从存储上来看：

集合 collection
文档document
字段Field
字段里： key-value pair

优点：
·没有预定义的字段，扩展字段容易
·相较于关系型数据库，读写性能优越，
Mysql索引使用B+树，数据都在叶节点上，每次查询都需要访问到叶节点
MongoDB索引使用B-树，所有节点都有Data域，只要找到指定索引就可以进行访问，无疑单次查询平均快于Mysql。

缺点：
不支持事务操作，虽然Mongodb4.0之后宣称支持事务，但是效果待观测
多表之间的关联查询不支持（虽然有嵌入文档的方式），join查询还是需要多次操作
空间占用较大，这个是MongDB的设计问题，空间预分配机制 + 删除数据后空间不释放，只有用db.repairDatabase()去修复才能释放
目前没发现MongoDB有关系型数据库例如MySql的Navicat这种成熟的运维工具。

适用于：
比较适合处理那些没有join、没有强一致性要求且表Schema会常变化的数据。

归根到底：
何时选用关系型数据库，何时选用非关系型数据库
选用非关系型数据库，使用哪种非关系型数据库

第一点，一致性需求高，更适合放到关系型数据库
非关系型数据库都是通过牺牲了ACID特性来获取更高的性能的，假设两张表之间有比较强的一致性需求，那么这类数据是不适合放在非关系型数据库中的。

第二点，核心数据不走非关系型数据库，例如用户表、订单表，有多种查询模式，走关系型数据库。
假设核心数据，但就是个KV形式，比如用户的聊天记录，那么HBase（非关系型数据库）一存就完事了。

尤其是日志、流水一类中间数据千万不要写在关系型数据库中，这一类数据通常有两个特点：
·写远高于读
·写入量巨大
一旦使用关系型数据库作为存储引擎，将大大降低关系型数据库的能力，正常读写QPS不高的核心服务会受这一类数据读写的拖累。

=============

二。SQL应用

practice on livesql.com

排序查询

如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一样。

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT OFFSET 子句实现。

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

聚合查询

**使用聚合查询计算男生平均成绩，并给列名设置一个别名average **

SELECT AVG(score) average FROM students WHERE gender = 'M';

如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

每页3条记录，如何通过聚合查询获得总页数？

 SELECT COUNT(*) / 3 FROM students;
 
 SELECT FLOOR(COUNT(*) / 3) FROM students;
 
SELECT CEILING(COUNT(*) / 3) FROM students;

答案：SELECT CEILING(COUNT(*) / 3) FROM students;

多表查询cross join

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。
cross join:交叉结合

SELECT * FROM students, classes;

一次查询两个表的数据，查询的结果也是一个二维表。
结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

上述查询的结果集有两列id和两列name，不好区分。
要解决这个问题，可以利用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

连接查询

Inner Join Vs Outer Join: Exact Difference With Examples
1.inner join：
2.left join：
3.right join：

4.FULL OUTER JOIN：把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;

This result is returned based on the join condition applied in the query.it uses the foreign key of one table to look up the column values by employing primary key in another table.

区别：Union

UNION has combined the rows from Table A and Table B.

In 语句

In clause

确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

SELECT * FROM customers;

SELECT * FROM orders;

Example 1:
selects all customers that are located in “London”, “Berlin”:

SELECT * FROM customers
WHERE City IN ('London', 'Berlin')

Example 2:
selects all customers who have made order:

SELECT * FROM customers
WHERE CustomerID IN (SELECT CustomerID FROM orders);

difference:
IN 语句：只执行一次

确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。from jcpp9527

select * from student s where s.stuid in(select stuid from score ss where ss.stuid = s.stuid)

首先会执行from语句找出student表，然后执行 in 里面的子查询，再然后将查询到的结果和原有的user表做一个笛卡尔积，再根据我们的student.stuid IN score.stuid的条件，将结果进行筛选（既比较stuid列的值是否相等，将不相等的删除）。最后，得到符合条件的数据。

EXISTS语句：执行student.length次

指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

select * from student s where EXISTS(select stuid from score ss where ss.stuid = s.stuid) 

having

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;     

like

The LIKE command is used in a WHERE clause to search for a specified pattern in a column

SELECT * FROM Customers
WHERE City LIKE 's%';

explain 语句

select语句进行分析并且输出详细的select执行过程的详细信息，让开发者从这些信息中获得优化的思路。

explain plan for 
select * from hr.employees where employee_id=100;
select * from table(dbms_xplan.display);

insert ignore 与 insert into

insert ignore 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。from 打工人小飞

Top SQL questions:

176. Second Highest Salary

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;

或者用IFNULL clause ：

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

175. Combine Two Tables
要求regardless if there is an address for each of those people:
以Person表为准，若无地址，补充null

select p.FirstName, p.LastName, a.City, a.State from 
Person p left join Address a
on p.PersonId=a.PersonId;

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1
  );
END

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
getNthHighestSalary(N INT)中，从第M+1行开始算，返回1条记录！
或者如下：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set M = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
      IFNULL((SELECT DISTINCT SALARY 
      FROM EMPLOYEE
      ORDER BY SALARY DESC
      LIMIT 1 OFFSET M), NULL)
  );
END

185. Department Top Three Salaries

select d.name as department,tmp.Name as employee,salary
from (
    select e.*,
dense_rank()over(partition by e.DepartmentId order by salary desc) as rank
from Employee e)tmp
join Department d
on tmp.DepartmentId = d.Id
where rank<=3
order by department

难：
select e.*, dense_rank()over…
partition by e.DepartmentId

181. Employees Earning More Than Their Managers

select e.Name as Employee from Employee e
join Employee e2
on e.ManagerId=e2.Id
where e.Salary>e2.Salary
;

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary
;

184. Department Highest Salary
IN clause:
query the (DepartmentId, Salary) are in the temp table using IN statement as below.

SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId

{“headers”: [“DepartmentId”, “MAX(Salary)”], “values”: [[1, 90000], [2, 80000]]}

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )
;

196. Delete Duplicate Emails

By joining this table with itself on the Email column, we can get the following code.

Delete p1 from Person p1, Person p2
where p1.Email=p2.Email AND p1.Id > p2.Id;

627. Swap Salary
if clause类似java中的三目运算符：
sex = sex == “m” ? “f” :“m”;

update salary set sex = IF (sex = "m", "f", "m");

1179. Reformat Department Table
if clause + sum(revenue)
如果month = ‘Jan’，就sum(revenue)，否则就NULL.

select id, 
sum(if(month = 'Jan', revenue, NULL)) as Jan_Revenue,
sum(if(month = 'Feb', revenue, NULL)) as Feb_Revenue,
sum(if(month = 'Mar', revenue, NULL)) as Mar_Revenue,
sum(if(month = 'Apr', revenue, NULL)) as Apr_Revenue,
sum(if(month = 'May', revenue, NULL)) as May_Revenue,
sum(if(month = 'Jun', revenue, NULL)) as Jun_Revenue,
sum(if(month = 'Jul', revenue, NULL)) as Jul_Revenue,
sum(if(month = 'Aug', revenue, NULL)) as Aug_Revenue,
sum(if(month = 'Sep', revenue, NULL)) as Sep_Revenue,
sum(if(month = 'Oct', revenue, NULL)) as Oct_Revenue,
sum(if(month = 'Nov', revenue, NULL)) as Nov_Revenue,
sum(if(month = 'Dec', revenue, NULL)) as Dec_Revenue
from Department d 
group by id;

262
Trips and Users 35.0% Hard
178
Rank Scores 50.2% Medium

1270
All People Report to the Given Manager 88.2% Medium
626
Exchange Seats 65.7% Medium

===========

知乎：50道SQL练习题
学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
insert into Student values(‘09’ , ‘张三’ , ‘2017-12-20’ , ‘女’);
insert into Student values(‘10’ , ‘李四’ , ‘2017-12-25’ , ‘女’);
insert into Student values(‘11’ , ‘李四’ , ‘2017-12-30’ , ‘女’);
insert into Student values(‘12’ , ‘赵六’ , ‘2017-01-01’ , ‘女’);
insert into Student values(‘13’ , ‘孙七’ , ‘2018-01-01’ , ‘女’);

科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);

教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);

成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values(‘01’ , ‘01’ , 80);
insert into SC values(‘01’ , ‘02’ , 90);
insert into SC values(‘01’ , ‘03’ , 99);
insert into SC values(‘02’ , ‘01’ , 70);
insert into SC values(‘02’ , ‘02’ , 60);
insert into SC values(‘02’ , ‘03’ , 80);
insert into SC values(‘03’ , ‘01’ , 80);
insert into SC values(‘03’ , ‘02’ , 80);
insert into SC values(‘03’ , ‘03’ , 80);
insert into SC values(‘04’ , ‘01’ , 50);
insert into SC values(‘04’ , ‘02’ , 30);
insert into SC values(‘04’ , ‘03’ , 20);
insert into SC values(‘05’ , ‘01’ , 76);
insert into SC values(‘05’ , ‘02’ , 87);
insert into SC values(‘06’ , ‘01’ , 31);
insert into SC values(‘06’ , ‘03’ , 34);
insert into SC values(‘07’ , ‘02’ , 89);
insert into SC values(‘07’ , ‘03’ , 98);

1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

select *
from (select SId ,score from sc where sc.CId='01')as t1 , (select SId ,score from sc where sc.CId='02') as t2
where t1.SId=t2.SId
and t1.score>t2.score

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

select *
from (select SId ,score from sc where sc.CId='01')as t1 , (select SId ,score from sc where sc.CId='02') as t2
where t1.SId=t2.SId

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

select *
from (select SId ,score from sc where sc.CId='01')as t1 left join  (select SId ,score from sc where sc.CId='02') as t2
on t1.SId=t2.SId

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

select *
from sc
where sc.SId not in (select SId from sc where sc.CId='01')
and  sc.CId='02'

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select student.*,t1.avgscore
from student inner JOIN(
select sc.SId ,AVG(sc.score)as avgscore
from sc 
GROUP BY sc.SId
HAVING AVG(sc.score)>=60)as t1 on student.SId=t1.SId 

在这里插入代码片

在这里插入代码片

在这里插入代码片

练习网站：
https://sqlbolt.com/lesson/select_queries_introduction