七周成为数据分析师 | 数据库

数据库是数据存储的集合

表是数据结构化的信息

列存储表中的组织信息，行存储表中的明细记录

主键是表中的唯一标示，主键不具备业务意义

表的主键不做强制要求，但建议设立

主键值必须唯一

每行必须有一个主键，不可为空

主键值不可被修改

主键值被删除后不可重用

表A的主键，可以作为表B的字段，此时不受约束

书写规则

1.以英文分号结尾

2.不区分关键字的大小写，但是插入的数据区分大小

3.写列名不能加单引号，列名命名不能有空格

4.符号只能用英文符号

一.简单查询

--我是单行注释
/*我是
多行注释*/

select 列名1，列名2
from 表名

select 列名1，列名2 as '列名12'

select distinct 列名1，列名2

where 列名 is null;
where 列名 is not null;

二.汇总分析

1.汇总函数

如果汇总函数后面写的是列名，会排除空值，如果输入*，才包含空值

count：求某列的行数（得到除去null之后的数目，count(*)可以计算包含控制的数目）

sum：求某列的数据和

avg：求某列数据的平均值

max：求某列数据最大值

min：求某列数据最小值

2.分组

group by分组

select 性别,count(*) as '学生人数'
from student
group by 性别;

分组和指定查询组合

select 性别,count(*) as '学生人数'
from student
where 出生日期 > '2000-1-1'
group by 性别;

3.对分组结果指定条件

where只能指定行的条件，having语句可以在分组之后指定条件

查询平均成绩大于60的学生的学号和平均成绩

select 学号,avg(成绩) as '平均成绩'
from score
group by 学号
having avg(成绩) > 60;

4.用SQL解决业务问题

①将问题翻译成通俗易懂的大白话

②写出分析思路

③写出对应的SQL子句。先把SQL各个部分的关键字写出来，再把问题拆解之后的对应部分写到子句上

5.对查询结果进行排序

关键字：order by，desc，asc

将成绩表先按成绩升序排，再按课程号降序排

select *
from score
order by 成绩 asc，课程号 desc;

三.复杂查询

1.视图

从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是select语句（视图本身并不存储数据）

在使用视图时，视图会先运行其保存的SQL语句，从表里查找结果并生成一张临时表

创建视图

create view 视图名称（视图的列名在这里定义）
as
select 查询语句（select语句中列的排列顺序和视图中列的排列顺序相同）

视图的用法：在from子句中使用视图名称代替表名称

删除视图：在客户端中选中要删除的视图，右键并选择删除视图；或者用SQL语句删除

drop view 视图名称

视图的优点：

可以将频繁使用的SQL语句保存成视图，以后使用的时候不用重新书写，从而提高效率

视图中的数据会随原表的变化自动更新，保持数据的最新状态

视图不保存数据，可以节省储存设备的容量

使用视图注意事项：

避免在视图的基础上再创建视图，多重视图会降低SQL运行性能

通过汇总得到的视图无法进行更新

2.子查询

子查询是一次性视图，在select语句执行完之后就会消失

子查询将用来定义视图的select语句直接用在from子句后面，即在select查询语句中嵌套了另一个select查询语句

SQL运行顺序：先运行子查询，再运行子查询外面的语句

子查询不仅可以放在from子句后面，也可以放在where子句后面，具体是放在in，any（有任何一个满足就返回true），all（全部都满足才返回true）后面的括号里构成复杂的查询条件

all和any要与比较运算符使用，any和some用法相同

偶尔使用的SQL查询语句可以使用子查询，不用保存为视图

使用子查询注意事项：

如果两个数值进行比较，比如all得到的是数据，不能写a>3*all(b)，正确的写法是a/3>all(b)

避免使用多层嵌套子查询

子查询的as和子查询名称可以省略，但是最好不要省略，养成良好的书写习惯，以便所有人能看懂子查询的意思

3.标量子查询

必须且只能返回一行一列的结果

返回单一的值，所以可以和比较运算符一起使用

标量子查询的书写位置不局限在where查询语句中，通常任何可以使用单一值的位置都可以使用。能够使用常数或者列名的地方，无论是select子句，group by子句，having子句，order by子句，几乎所有地方都可以使用

什么时候用标量子查询？当我们需要单一值的时候

注意事项：不能返回多行结果

4.关联子查询

在细分的组内进行比较时，可以使用关联子查询

关联子查询起关键作用的是子查询语句中的where子句

注意事项

子查询内部设定的关联名称，只能在子查询内部使用（内部可以看到外部，外部看不到内部），所以结合条件一定要写在子查询中

四.表的加法，多表查询（联结）和case表达式

1.表的加法

关键词：union，将两个表的数据结合并在一起，会将两个表里重复的数据删除，只保留一个

union all可以保留两个表里重复的行

2.表的联结

关系数据库是由多张表组成的，表和表之间通过列产生联系

联结类型：交叉联结，内联结，左联结，右联结，全联结

交叉联结cross join：将一个表中的每一行都与另一个表中的每一行合并在一起，得出结果的行数是两张表中行数的乘积

交叉联结在实际业务中使用较少，原因在于：结果没有实用价值，结果行数太多，要花费大量的计算时间和高性能设备的支持、

内联结：查找出同时存在两张表中的数据，先取出两个表中符合条件的行，再进行交叉联结

左联结：将左侧的表作为主表，将主表中的数据全部取出来，右边的表选出和左表相同的行

全联结：返回左表和右表中的所有行，当某行有匹配的时候，两行进行合并，如果没有匹配的，对应用空值进行填充

总结：如果要得出两个表中的公共部分，使用内联结；在实际业务中，如果要生成固定行数的数据，或者取出某一个表的全部数据，就使用左联结或者右联结

3.case表达式

在区分情况时使用

else子句可以省略，表示默认else null，但是最好不要省略

end子句不可以省略

case表达式可以书写在任意位置

case表达式可以将select语句中的行列结果进行互换

五.窗口函数

<窗口函数> over (partition by <要分组的列名> order by <要排序的列名>)

①作为窗口函数使用的函数有两类

专用窗口函数：rank，dense_rank，row_number

聚合函数：sum，avg，count，max，min

②其中partition by可以省略

③窗口函数原则上只能写在select子句中，不能用在where和group by子句中

④order by子句中可以通过desc，asc指定降序或升序，如果省略，默认会按照升序asc排列

⑤专用窗口函数括号中可以为空，不用指定参数。聚合函数作为窗口函数要指定参数

窗口函数兼具分组和排序功能，但是其分组并不具备group by子句的汇总功能，而是保留了所有行的数据

聚合函数作为窗口函数的语法和专用窗口函数一样，把聚合函数写在窗口函数的位置即可。但是聚合函数后面的括号里不能为空，需要指定聚合的列名

聚合函数还可以和row preceding结合使用，计算移动值

rows n preceding表示“截止到之前n行”，通过这两个关键词限定了汇总对象的框架，汇总对象会随着当前记录的变化而变化。这种方法的适用场景：在公司业绩名单排名中，通过移动平均，直观的查看到与相邻名词业绩的平均，求和等统计数据。希望把握最近状态时非常方便，常常应用在对股市趋势的实时跟踪中

1.排名问题

将成绩表按照成绩排名

select *,rank() over(order by 成绩 desc) as ranking
from score;

2.topN问题

查询各科成绩前两名的记录

select *
from (select *,row_number() over (partition by 课程号 order by 成绩 desc) as 排名
from score) as a
where 排名 <= 2;

3.在每个组里进行比较

查找单科成绩高于该科目平均成绩的学生名单

select *
from(
select 姓名,科目,成绩,avg(成绩) over (partition by 科目) as 各科平均成绩
from 各科成绩表) as a
where a.成绩 > a.各科平均成绩;