数据库刷题笔记1

简化等值查询

多表连接查询,且去掉重复列,使用 USING()

select * from school left join course USING (courseid) 
#相当于
select * from school left join course on school.courseid=course.courseid;

mysql中limit和offset的用法

mysql里分页一般用limit来实现，例如：

1、select* from user limit 3；

表示直接取前三条数据

2、select * from user limit 1,3;

表示取2,3,4三条条数据

3、select * from user limit 3 offset 1;

表示取2,3,4三条条数据

解释：

1、当 limit后面跟一个参数的时候，该参数表示要取的数据的数量

例如 select* from user limit 3； 表示直接取前三条数据

2、当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量,例如

select * from user limit 1,3;

就是跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

3、当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。

例如select * from user limit 3 offset 1;
表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

LIMIT 分页查询

方法）LIMIT

SELECT *
FROM employees
LIMIT 5,5

LIMIT 语句结构： LIMIT X,Y

• Y ：返回几条记录
• X：从第几条记录开始返回（第一条记录序号为0，默认为0）

分页查询employees表，每5行一页，返回第2页的数据
一般分页使用 order by + limit 。如要求5行/页，则第二页显示第6-10行。

1.limit x,y x:偏移量 y:要获取的个数
limit 5,5; 偏移量为5，取5条记录
2.limit y offset x
limit 5 offset 5; 取5条记录，偏移量为5

select * from employees
order by emp_no
limit 5,5;

拓展：若每页显示n条记录，要显示第i页数据，则可以用 limit n*(i-1),n
因此，得到如下代码

> select * from
> employees order by emp_no limit 5*(2-1),5;

In 与 exists 的区别

IN 语句：只执行一次

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

EXISTS语句：执行student.length次

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

区别及应用场景

in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。

in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

not in 和not exists

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

日期函数格式转换

DATE_FORMAT(date,format)

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
date 参数是合法的日期。format 规定日期/时间的输出格式。
可以使用的格式有：

常用格式	对应描述
%Y	–年，4 位
%m	–月，数值（00-12）
%M	–月名
%k	–小时（0-23）

• %Y 四位数字表示的年份
• %y 两位数字表示的年份
• %m 两位数字表示的月份（ 01, 02, . . ., 12）
• %d 两位数字表示月中的天数（ 00, 01, . . ., 31）

开窗函数

函数简介

MySQL从8.0开始支持开窗函数，这个功能在大多商业数据库中早已支持，也叫分析函数；

开窗函数与分组聚合比较像，分组聚合是通过制定字段将数据分成多份，每一份执行聚合函数，每份数据返回一条结果；

开窗函数也是通过指定字段将数据分成多份，也就是多个窗口，对每个窗口的每一行执行函数，每个窗口返回等行数的结果；

窗口函数分为静态窗口和滑动窗口，静态窗口的大小是固定的，滑动窗口的大小可以根据设置进行变化，在当前窗口下生成子窗口。

语法简介

语法：函数名([参数]) over(partition by [分组字段] order by [排序字段] asc/desc rows/range between 起始位置 and 结束位置)

函数解读 函数分为两个部分，函数名称 + over()语句

# 函数名称 + over(...)
开窗函数(字段) over(partition by 分组字段 order by 排序字段 range between 起始位置 and 结束位置)

第一部分是函数名称，开窗函数的数量较少，只有11个窗口函数+聚合函数*（所有聚合函数都可以用作开窗函数）*，根据函数性质，有的要写参数，有的不需要写参数；
按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

• 所有聚合函数：如sum()/avg()/count()/max()/min()/…
• 序号函数：row_number() / rank() / dense_rank()
• 分布函数：percent_rank() / cume_dist()
• 前后函数：lag()/ lead()
• 头尾函数：first_val() / last_val()
• 其他函数：nth_value() / nfile()

第二部分是over语句，over()是必须要写的，里面有三个参数，都是非必须参数，根据需求选写：

1. 第一个参数是 partition by +分组字段，将数据根据此字段分成多份，如果不加partition by参数，那会把整个数据当做一个窗口。
2. 第二个参数是 order by +排序字段，每个窗口的数据要不要进行排序。
3. 第三个参数 rows/range between 起始位置 and 结束位置，这个参数仅针对滑动窗口函数有用，是在当前窗口下分出更小的子窗口。其中起始位置和结束位置可写：current row
   边界是当前行，unbounded preceding 边界是分区中的第一行，unbounded following
   边界是分区中的最后一行，expr preceding 边界是当前行减去expr的值，expr following
   边界是当前行加上expr的值。rows是基于行数，range是基于值的大小，到讲解到滑动窗口函数时再详细介绍。

最差是第几名(一)题解
本题出题的题意其实主要是考察sum() over (order by ) 开窗函数，sum(a) over (order by b) 的含义是:
例如
a b
1 2
3 4
5 6
按照b列排序，将a依次相加，得到结果，如下:
a b sum(a) over (order by b)：
1 2 1
3 4 1+3
5 6 1+3+5

此题就是将b换成了grade，即使b列乱序也没关系，因为有order by b：
number grade sum(number) over(order by grade)：
2 A 2
2 B 2+2
2 C 2+2+2
1 D 2+2+2+1

聚合函数

group_concat（）函数将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

排名函数

select emp_no, salary, dense_rank() over (order by salary desc) as t_rank
from salaries
order by t_rank, emp_no

ROW_NUMBER()：行号。SQL Server2005中，使用ROW_NUMBER()非常方便

SELECT  * , ROW_NUMBER() OVER ( ORDER BY createTime) AS 行号
FROM  表名

备注：数据在排行号时已经排序，如果有根据行号分页的显示，注意ORDER BY排序

RANK()：rank函数用于返回结果集的分区内每行的排名

select RANK() OVER(order by [id]) as rank,* from 表名

备注：RANK() 排名，如果OVER后排序后，有（id）值相同，会排名相同，后续排名，将按照记录数排名号排下一个。例如，成绩，1个第一名（排名序号1），2个第二名分数相同（排名序号都是2），第3名（排名序号4）

DENSE_RANK() :dense_rank函数的功能与rank函数类似. 区别：dense_rank函数在生成序号时是连续的

select DENSE_RANK() OVER(order by [id]) as den_rank,* from 表名

NTILE():ntile函数可以对序号进行分组处理，将有序分区中的行分发到指定数目的组中

select NTILE(3) OVER(order by [id] desc) as ntile,* from 表名

sql server的随机函数**newID()**和RAND()

备注：newid():随机函数, 在扫描每条记录的时候都生成一个值, 而生成的值是随机的, 没有大小写顺序

SELECT * FROM Northwind..Orders ORDER BY NEWID()
--随机排序
SELECT TOP 10 * FROM Northwind..Orders ORDER BY NEWID()
--从Orders表中随机取出10条记录