sql学习

news/发布时间2024/5/19 11:09:53

-- 学生表
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 joinselect 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 inselect 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产生的分组进行筛选

一些用的比较少的关键词

avg	平均值
min	最小值
max	最大值
sum	总和
count	计数
distinct	表示将distinct后的属性去重
group by	将在group by上取值相同的信息分在一个组里
having	对group by产生的分组进行筛选，可以使用聚集函数