sql学习

-- 学生表
CREATE TABLE t_student (                 
    id BIGINT PRIMARY KEY,               
    name VARCHAR(255),                   
    gender VARCHAR(255),                 
    age INT,                             
    address VARCHAR(255),                
    INDEX idx_age (age)                  
);                                       

-- 分数表
CREATE TABLE t_stu_score (
    stu_id BIGINT,
    course VARCHAR(255),
    score INT(255),
    INDEX idx_userid_course (user_id, course),
    INDEX idx_score (score),
    UNIQUE INDEX uk_userid_course (user_id, course)
);

in和exists有什么异同点？

相同点：in和exists都可以作为where的条件，用于子查询

exists用法：

select a.* from A a where exists(select 1 from B b where a.id=b.id)；

select stu.* from t_student stu where exists (select stu_score.* from t_stu_score stu_score where stu_score.stu_id = stu.id)

说明：                  A表是主查询的表，B表是子查询的表， A表驱动B表，exists的作用是检查子查询是否有返回结果，如果有，主查询中指定的列。如果子查询返回空集，则主查询将返回空集。

执行先后和次数：先执行一次主查询（有a条结果），并将结果放在内存，然后执行a次子查询，共a+1次数据库交互

命中索引情况：   子查询可以命中索引，主查询不走索引

适用的情况：       A表能命中的记录少，B表能命中的记录多

in用法：

select a.* from A a where a.id in (select id from B)；

select stu.* from t_student stu where stu.id in (select stu_score.stu_id from t_stu_score stu_score)

说明：                   A表是主查询的表，B表是子查询的表， B表驱动A表，先查出子查询的结果集，在执行多次主查询，判断主表的字段是否存在于子查询的返回结果集里

执行先后和次数： 先执行子查询（有b条结果），并将结果放在内存，后针对b个结果，执行b次主查询，共b+1次数据库交互

命中索引情况：    主查询和子查询都可以走索引

适用情况：            A表能命中的记录多，B表能命中的记录少，in还可以固定几个值

怎么选：

子查询结果集小选in，主查询结果集小选exists，差不多大小再结合索引来看
小表驱动大表（结果集小的驱动结果集大的）
IN查询在主查询表和子查询表上都可以命中索引
Exists查询只能在子查询表上命中索引

not exists和not in的比较

not exists能对子查询走索引

not in （通常）主查询和子查询都不能走索引

结论：子查询基本上选择not exists

 常用的一种写法，从多表查询出结果

select a.*, b.* from A a, B b where a.id = b.id  等价于使用inner join

left join ：左连接  A为主，B为辅

right join：右连接 B为主，A为辅

inner join：内连接，A与B

full join：全连接，A或B

full join:两个查询的列合并

union：两个查询的行合并，去重

union all：两个查询的行合并，不去重

常见的sql题目

1. 查找不在表里的数据

2. 查找第N高的分数

3. 分组排序

4. 连续出现N类问题

1.有两个表A和B，以id关联，查找在A中但是不在B中的数据

方案a：使用left join

select A.id from A left join B on A.id = B.id where B.id is null 

方案b：使用not exists 

select A.id from A where not exists (select 1 from B where A.id = B.id)

方案c：使用not in

select A.id from A where A.id not in(select id from B)

2. 有一个课程分数表，score，cource，id（学生id），查找某门课程第N高的分数或者学生， 如果没有需要返回null值

select ifnull ((select distinct score from A order by score desc limit 1 offset n-1), null) as theNTopScore

3. 分数排名（三种规则  1134 / 1234 / 1123）使用窗口函数

-- 仅根据分数排名
select score, rank() over(partition by course ORDER by score desc) as 'Rank' FROM table;

-- 根据课程分组，分数排名
select score, rank() over(ORDER by score desc) as 'Rank' FROM table;

4. 有N个相同成绩的分数

select score from table group by score having count(*) = N
-- having： 对group by产生的分组进行筛选

一些用的比较少的关键词

窗口函数：

像排序的几个窗口函数需要MySQL8才支持，常规的聚合函数5.7也支持 

聚合函数：SUM、COUNT、MAX、MIN

序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()

分布函数：PERCENT_RANK()、CUME_DIST()

前后函数：LAG()、LEAD()

头尾函数：FIRST_VALUE()、LAST_VALUE()

其它函数：NTH_VALUE()、NTILE()