SQL速查文档

首先写这个文档记录一下我的学习记录

SQL（Structured Query Language）是一种数据库的结构化查询语言，常用的关系型数据库由 MySQL，SQL Server，Oracle，Access 等。那么什么是数据库呢，简而言之就是存储数据的仓库，Excel 为什么不算数据库呢，因为它能够存储的数据量真的太小了，Excel2016 到底是 1048576 行，意思是它最多存储 104 万条数据，但你要真存 104 万条数据在 Excel 里估计电脑不卡也残废。现在大数据时代数据动辄百万/千万，所以需要数据库来帮忙。总结一下，SQL 就是对存储在数据库中的数据进行查询等操作的一种语言，所以你知道了，SQL 本身是一种语言，如果你有语言学习的基础，那么学起 SQL 来就简单多了。

一、增 1.1创建数据表

CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

例子：
create table if not exists user_info_vip(
    id int primary key auto_increment comment '自增ID',
    uid int unique not null comment '用户ID',
    nick_name varchar(64) comment '昵称',
    achievement int default 0 comment '成就值',
     `level` int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',         
    register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
)character set utf8 collate utf8_general_ci;

1.2插入 1.2.1插入列

alter table 表名 add column 列字段名 数据类型

1.2.2插入行

insert into 表名 values (列 1 值，列 2 值，列 3 值）
例子：
insert into exam_record values 
(null,'1001','9001','2021-09-01 22:11:12','2021-09-01 23:01:12','90'),
(null,'1002','9002','2021-09-04 07:01:02',null,null)

1.3创建索引 1.3.1 create方式创建索引：

CREATE
[UNIQUE -- 唯一索引
| FULLTEXT -- 全文索引
] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
(column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引

例子
create index idx_duration on examination_info(duration);
create unique index uniq_idx_exam_id on examination_info(exam_id);
create fulltext index full_idx_tag on  examination_info(tag);

1.3.2 alter方式创建索引：

ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)

1.2.1 drop方式删除索引：

DROP INDEX <索引名> ON <表名>

1.2.2 alter方式删除索引：

ALTER TABLE <表名> DROP INDEX <索引名>

1.3.1 索引的使用：

• 索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；

• 索引不包含有NULL值的列
• 一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
• like做字段比较时只有前缀确定时才会使用索引
• 在列上进行运算后不会使用索引，如year(start_time)

二、删 2.1删除库

drop database 数据库名;

2.2删除表

drop table 表名;

2.2删除列

alter table 表名 drop column 列;

2.3删除行

删除一行： delete from 表名 where 列名 = 值 
删除所有行： delete from 表名

三、改 3.1修改行

设置为新值：UPDATe table_name SET column_name=new_value [, column_name2=new_value2] [WHERe column_name3=value3]
根据已有值替换：UPDATE table_name SET key1=replace(key1, '查找内容', '替换成内容') [WHERe column_name3=value3]

例子：
update examination_info set tag='Python' where tag='PYTHON'

3.2 修改表名（两种方法）

alter table 原表名 rename 新表名; 
rename table 原表名 to 新表名;

3.3修改列名

修改字段：
alter table 表名 change 原列字段名 新列字段名 数据类型;
修改别名：
select 原始字段名 as 别名;

四、查(重要)

查询语法的基本结构如下：
select 列名-----要查询的字段
from 表名-----要选项的表
where 限制条件-----过滤数据
group by 列名-----分组数据
having 列名-----分组后过滤
order by 列名-----排序数据
limit N-----限制条数

4.1聚合函数

聚合函数是对数值数据进行汇总的函数，主要有以下：

函数	说明
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

4.1.1count函数

select count(列) from 表 --对特定列的值计数
select count(*) from 表 --可以查看表的总行数
select count(DISTINCT 列) from 表 --去重以后的行数

4.1.2sum函数

select sum(列) from 表
例子
SELECt SUM(score) from sc where c_id ='02'

4.1.3max函数

select max(列) from 表
例子
select max(score) from sc;

4.1.4min函数

select min(列) from 表;
例子
select min(score) from sc;

4.1.5avg函数

select avg(列) from 表;
例子
select avg(score) from sc;

4.2分组查询 4.2.1创建分组group by

group by 命令用来创建分组，分组其实相当好理解，就是 Excel 里的数据透视表，汇总方式为计数、求和或平均的功能，所以通常和聚合函数结合起来使用

--语法
select 列,聚合函数
from 表 
where 子句
group by 列

例子
select s_id, count(*)
from scgroup 
group by s_id;

4.2.2分组后过滤having

--语法
select 列，聚合函数
from 表 
where 子句
group by 列
having 聚合函数 操作符 值；

例子：
SELECt s_id ,AVG(score) 
from scgroup 
group by s_id
having AVG(score) > 60

1、先用 group by 分组求每个学号学生的几门课的平均成绩

2、再用 having 过滤，查询平均成绩大于 60 分的记录

4.3多表查询 4.3.1嵌套子查询

子查询就是在查询里再嵌套一个查询，子查询也很好理解，类似 Excel 里的 IF 函数嵌套的概念。子查询有 3 种情况：

1、作为过滤条件

select 列名 from 表名
where 列 操作符（＞，＜，=，in） (select 列名 from 表名)

例子：月均完成试卷数不小于3的用户爱作答的类别
select tag, count(tag) as tag_cnt
from exam_record
join examination_info using(exam_id)
where uid in (
    select uid
    from exam_record
    where submit_time is not null 
    group by uid
    having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3
)
group by tag
order by tag_cnt desc

思路：

• 筛选完成了的试卷的记录。知识点：where
• 筛选月均完成数不小于3的用户。知识点：
  • 按用户分组group by uid；
  • 统计当前用户完成试卷总数count(exam_id)；
  • 统计该用户有完成试卷的月份数count(distinct DATE_FORMAT(start_time, "%Y%m"))；
  • 分组后过滤having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3；
• 关联试卷作答记录表和试卷信息表。知识点：join examination_info using(exam_id)
• 筛选满足条件的用户。知识点：where uid in (...)
• 统计这些用户作答的类别及计数。知识点：按用户分组group by uid；计数count(tag)；
• 按次数降序输出。知识点：order by tag_cnt desc

结果：

 

2、作为临时表

-- 语法
select 列名 from (select 列名 from 表名)

例子：查询所有课程成绩小于 60 分的学生学号、姓名
SELECt s.s_name ,a.s_id 
from(
    SELECt s_id ,COUNT(c_id ) as cnt
    from sc
    group by s_id
) as a
inner join
(
    SELECt s_id ,COUNT(c_id ) as cnt
    from sc
    where score <60
    group by s_id
) as b
on a.s_id = b.s_idinner 
join student s on s.s_id = a.s_id
where a.cnt = b.cnt

思路：

• 1、子查询临时表 a 表是把每个学生选课数目拿了出来
• 2、子查询临时表 b 表是把每个学生成绩小于 60 的课程数目拿了出来
• 3、将两个表连接，并给定过滤条件，让选课数=成绩小于 60 的课程数，就是这个学生所有课程成绩小于 60 的记录了。

结果：

3、作为字段

-- 语法
select 列名 ，(select 列名 from 表名)from 表名where 列 操作符 值

4.4连接（join）

SQL join 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

4.5合并表格(union)

在使用 union 时，每个 select 语句需有相同数量的列，列的数据类型和顺序也必须相同。

Select 列 1，列 2 from 表 1
Union
Select 列 11，列 22 from 表 2

将多个查询结果合并起来（行合并），会去重，如果想要不去重的，则使用 union all。

Select 列 1，列 2 from 表 1
Union all
Select 列 11，列 22 from 表 2

4.6常用的时间函数 4.6.1 date_format（）

我们从数据库中查询出来的日期数据的格式一般不是我们想要的格式，因此查询的时候要转换成我们需要的格式；

# 格式化参考：
select DATE_FORMAT(date,foramt);
select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s');

#字符串转日期
select str_to_date('2021-04-02 10:37:14', '%Y-%m-%d %H:%i:%s'); # 2021-04-02 10:37:14

常用的%Y年，%m月，%d日，%H小时，%i分钟，%s秒，其余的可以上csdn找

4.6.2 timestampdiff()时间差函数

timestampdiff(interval, datetime1,datetime2) #时间2-时间1的时间差
例子：
SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13'); # 3
SELECT TIMESTAMPDIFF(day,'2013-01-12','2013-01-13'); # 1

interval可以设置为：

second 秒

minute 分钟

hour 小时

day 天

week 周

month 月

quarter 季度

year 年

五、窗口函数

窗口函数，又叫 OLAP 函数，MySQL 8 以上的版本才支持。

-- 语法 
窗口函数()over (partition by 分区的字段 order by 排序的字段 desc/asc)

窗口函数语法解释：

• over：指定函数执行的窗口范围，若 over 后什么都不写，则指定满足 where 条件的所有行

• partition by：按哪些字段进行分组

• order by：按哪些字段进行排序

5.1排序函数

要显示排序的名次时，用到排序窗口函数，有 3 种用于排序的窗口函数

5.1.1 row_number

这个函数赋予唯一的连续位次，会根据顺序计算

例如，有 3 条排在第 1 位时，排序为：1，2，3，4······

row_number()over (partition by 分区的字段 order by 排序的字段 desc/asc)

例子：查询学生的平均成绩及其名次
SELECT s_id ,AVG(score) ,
row_number() OVER (order by AVG(score) DESC ) as r_row
from sc
group by s_id;

思路：

1、不用窗口函数直接降序也是可以的，但题目要求我们给出名次，那么用窗口函数就方便一些

2、这里没有用到分区 partition by，因为我们求的就是 3 门学科的平均成绩，不用分区

结果：

5.1.2 rank

在计算排序时，若存在相同位次，会跳过之后的位次。排序相同时会重复，总数不会变

例如，有 3 条排在第 1 位时，排序为：1，1，1，4······

rank()over (partition by 分区的字段 order by 排序的字段 desc/asc)

例子：查询“01”学科学生的平均成绩及其名次
SELECt s_id ,AVG(score),
rank() OVER (order by AVG(score) DESC ) as r_row
from sc
where c_id = "01"
group by s_id;

结果：

 

5.1.3 dense_rank

在计算排序时，若存在相同位次，不会跳过之后的位次。排序相同时会重复，总数会减少

例如，有 3 条排在第 1 位时，排序为：1，1，1，2······

dense_rank()over (partition by 分区的字段 order by 排序的字段 desc/asc)
例子
SELECt s_id ,AVG(score) ,
rank() OVER (order by AVG(score) DESC ) as r_row
from sc
where c_id = "01"
group by s_id;

结果：

5.2分布函数

对某个记录，看它低于/高于归属组内的比例（累计占比/分位数），通常有 2 种函数：

5.2.1 percent_rank

percent_rank()over (partition by 分区的字段 order by 排序的字段 desc/asc)

每行：(rank - 1)/(rows - 1)

如有重复值，取重复的第一行的值

5.2.2 cume_dist

cume_dist()over (partition by 分区的字段 order by 排序的字段 desc/asc)

每行：rank/rows

如有重复值，取重复的最后一行的值

例子：
SELECt s_id ,c_id ,AVG(score) ,
percent_rank() OVER (PARTITION by c_id order by score DESC ) as p1_p ,
cume_dist() OVER (PARTITION by c_id order by score DESC ) as p2_c
from sc
group by s_id,c_id ;

思路：

1、percent_rank() OVER (PARTITION by c_id order by score DESC ) as p1_p，是对c_id进行分区后对其中的score降序排列，看每个值在其中的占比，比如第三行的p1_p的值为0.4，它是(3-1)/(6-1)得到的，因为percent_rank的计算公式就是这样

2、同理cume_dist() OVER (PARTITION by c_id order by score DESC ) as p2_c，它的第3行p2_c的值为0.5，是3/6得到的。

3、这有点像分位数的算法

结果;

 

5.3 前后函数

求当前行的前 N 行或后 N 行的值，注意前后函数和其他窗口函数的区别是要指定字段。

5.3.1 lead

lead(字段,N)over (partition by 分区的字段 order by 排序的字段 desc/asc)

当前行的前 N 行对应的字段的值

5.3.2 lag

lag(字段,N)over (partition by 分区的字段 order by 排序的字段 desc/asc)

当前行的后 N 行对应的字段的值

例子
SELECt s_id ,s_name ,s_age ,
lead(s_name ,1) OVER (order by s_age DESC ) as l1 ,
lag(s_name,1) OVER (order by s_age DESC ) as l2
from student
group by s_id,s_name ,s_age ;

思路：

1、lead(s_name ,1) OVER (order by s_age DESC ) as l1，表示对 s_age 字段降序排列后取s_name 前 1 行的 s_name 的内容

2、Lead 函数相当于整体前移，lag 函数则是整体后移

结果：

5.4头尾函数

求第一个/最后一个字段的值

注意和最大最小值的区别。如果排序了，头尾函数就是最大最小值，但如果没有排序，头尾函数就是第一个和最后一个值。

5.4.1 first_value

指定字段在不同分区里的第一个字段的值

first_value(字段)over (partition by 分区的字段 order by 排序的字段 desc/asc)

5.4.2 last_value

指定字段在不同分区里的最后一个字段的值

last_value(字段)over (partition by 分区的字段 order by 排序的字段 desc/asc)