SQL综合练习

知识点回顾

根据对RDBMS赋予的指令种类的不同，SQL语句可以分为以下三类

• DDL

DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令:

1）CREATE：创建数据库和表等对象

2）DROP：删除数据库和表等对象

3） ALTER：修改数据库和表等对象的结构

• DML

DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录。DML 包含以下几种指令：

1） SELECT：查询表中的数据

2）INSERT：向表中插入新数据

3）UPDATE：更新表中的数据

4）DELETE：删除表中的数据

• DCL

DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令：

1）COMMIT：确认对数据库中的数据进行的变更

2）ROLLBACK：取消对数据库中的数据进行的变更
3）GRANT：赋予用户操作权限

4）REVOKE：取消用户的操作权限

一、搭框架

数据库的创建

create database <数据库名称>;

使用该数据库

use <数据库名称>;

创建表格

create table <表名>
( <列名 1> < 数据类型 > < 该列所需约束 >,
  <列名 2> < 数据类型 > < 该列所需约束 >,
  ·
  ·
  ·
  primary key (作为主键的列名));

表的删除【注意：删除的表无法恢复，只能重新插入！！！】

drop table < 表名 >;

添加一个新的列

alter table <表名>
add column
< 列的定义 >;

删除列

alter table < 表名 > drop column <列名>;

清空表的内容（速度比 drop / delete更快 ）

truncate table < 表名 >;

数据的更新

update < 表名 >
set < 列名 > = < 表达式 > [, < 列名2 > = < 表达式2 >……]/null;
where < 条件 >; -- 可选
order by 子句; -- 可选
limit 子句; --可选

向表中插入数据

insert into < 表名 > (列1， 列2， 列3,……) values
(值1, 值2, 值3,……);

二、SELECT

select < 列名 >,
  from < 表名 >
  where < 条件表达式 >;

① SQL中常用比较运算符

运算符	含义
=	和……相等
<>	和……不相等 （not 也可以表示否定）
>=	大于等于
>	大于
<=	小于等于
<	小于

② select distinct 列名

可以删除这个列中重复的数据

③ 希望选取null记录时，需要在条件表达式中使用 is null 运算符；

​ 希望选取不是null记录时，需要在条件表达式中使用is not null运算符。

④ SQL 中用于汇总的函数叫做聚合函数，以下五个师最常用的聚合函数：

函数名	功能
COUNT	计算表中的记录数（行数）
SUM	计算表中数值列中数据的合计值
AVG	计算表中数值列中数据的平均值
MAX	求出表中任意列中数据的最大值
MIN	求出表中任意列中数据的最小值

聚合函数会将 NULL 排除在外，但COUNT(*)除外，它会得到包含NULL的数据行数；而COUNT(<列名>)会得到NULL之外的数据行数

三、GROUP BY 语句

将现有的数据按照某列来汇总统计

select <列名1>,<列名2>,<列名3>,……
from <表名>
group by <列名1>,<列名2>,<列名3>,……；

① 会将NULL作为一组特殊数据进行处理

② select 后面出现的除了聚合函数的列之外，只能是group by 子句后面出现的列名

四、HAVING语句

在使用group by 进行分组后，再使用having语句对分组进行过滤，可以使用数字、聚合函数和GROUP BY中指定的列名。

五、ORDER BY语句

当SQL执行的结果需要按照特定顺序排序时，可以使用order by 语句

select <列名1>,<列名2>,<列名3>,……
  from <表名>
  order by <排序基准列1>,<排序基准列2>,……

默认为升序 ASC，降序排列为DESC

六、视图

创建视图

create view <视图名称>（<列名1>,<列名2>,……）
as 
<select 语句>

修改视图

alter view <视图名>
as
<select 语句>

注： 如果包含以下结构的任意一种都是不可以被更新的：

聚合函数 SUM()、MIN()、COUNT()等
DISTINCT关键字
GROUP BY子句
HAVING子句
UNION或UNION ALL运算符
FROM 子句中包含多个表

删除视图（需要有相应的权限才能成功删除）

drop view <视图名1>[,<视图名2>……]

七、各种函数

算术函数

• abs(数值)

用于计算一个数字的绝对值，表示一个数到原点的距离

当参数为null时，返回值也是null

• mod(被除数，除数)

计算除法余数（求余），只能对整数列求余数

• round(对象数值，保留小数的位数)

用来进行四舍五入操作

注意：当参数 保留小数的位数 为变量时，可能会遇到错误

字符串函数

• concat( str1, str2, str3 )

进行拼接

• length( 字符串 )

求字符串的长度

• lower( 字符串 )

把字符串中的字母都换成小写

• upper( 字符串 )

把字符串中的字母都换成大写

• replace( 对象字符串，替换前的字符串，替换后的字符串 )
• substring( 对象字符串 FROM 截取的起始位置 FOR 截取的字符数)

使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

• substring_index( 原始字符串，分隔符，n)

该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。

日期函数( select )

• current_date

获取当前日期

• current_time

当前时间

• current_timestamp

当前日期和时间

• extract(日期元素 from 日期)

截取日期元素（年、月、小时、秒）

该函数的返回值并不是日期类型而是数值类型

转换函数

• cast( 转换前的值 as 想要转换的数据类型 )
• coalesce( 数据1，数据2，数据3……)

该函数会返回从左侧开始第1个不是null的值

谓词

• like

like ‘ddd%’ 前方一致：代表以ddd开头的所有字符串

like ‘%ddd%’ 中间一致：查询对象字符串中含有作为查询条件的字符串，无论该字符串出现在字符串的前面、中间还是最后

like ‘%ddd’ 后方一致：作为查询条件的字符串与查询对象字符串的末尾部分相同

使用_下划线来代替%，并且1个下划线代表1个字符

• between

用于范围查询

between a and b 结果是闭区间，包含a，b这两个临界值

• is null、is not null

用于判断是否为null

• or

多个查询条件取并集时可以选择使用or语句

也可以用 (not) in(值1，值2，值3……)来替换

CASE 表达式

类似于if else的功能！！！

case when <求值表达式> then <表达式>
	 when <求值表达式> then <表达式>
	 when <求值表达式> then <表达式>
	 ·
	 ·
	 ·
else <表达式>
end

① 一般写在select里

② 可以写sum(case when then end )

集合运算

• union

表的加法，通常会除去重复的记录

• union all

保留重复行

• except

表的减法，不支持哦，一般可以用not in

• except all

不支持哦

• 对称差

不支持哦，但是可以通过a-b 并 b-a

• 连结

1）inner join

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

注：select 子句中的列最好按照 表名.列名 的格式来使用

2）natural join

SELECT *  FROM shopproduct NATURAL JOIN product

3）外连结

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

窗口函数

<窗口函数> OVER ([PARTITION BY <列名>]
                     ORDER BY <排序用列名>)  

PARTITON BY是用来分组，即选择要看哪个窗口，类似于GROUP BY 子句的分组功能，但是PARTITION BY 子句并不具备GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。

ORDER BY是用来排序，即决定窗口内，是按那种规则(字段)来排序的。

1）专用窗口函数

• rank函数

（1，1，1，4）

• dense_rank函数

（1，1，1，2）

• row_number函数

（1，2，3，4）

2）聚合函数

出来的结果是一个累计的聚合函数值

sum avg max min

3）移动平均

# PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行
<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS n PRECEDING )  
# FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行         # BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”        
<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)

4）rollup

在 group by 后面使用with rollup不仅可以得到每个分类的小计，而且还可以得到分类的总计

书写位置

select （窗口函数）$\Rightarrow$ from $\Rightarrow$ join $\Rightarrow$ where $\Rightarrow$ group by $\Rightarrow$ having $\Rightarrow$ order by

执行顺序

from $\Rightarrow$ join $\Rightarrow$ where $\Rightarrow$ group by $\Rightarrow$ having $\Rightarrow$ select $\Rightarrow$ 窗口函数 $\Rightarrow$ order by $\Rightarrow$ limit

练习一：各部门工资最高的员工（难度：中等）

创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
±—±------±-------±-------------+
创建Department 表，包含公司所有部门的信息。

±—±---------+
| Id | Name |
±—±---------+
| 1 | IT |
| 2 | Sales |
±—±---------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
±-----------±---------±-------+

【解答】
首先创建两张表，在创建表时需要注意：

1. Name 是字符，在插入表中这一列的值时，要加引号；
2. 一般都把 Id 设置为字符型，插入时也要用引号哦~

-- 创建表Employee
create table Employee
( Id varchar(4) ,
  Name varchar(15),
  Salary integer,
  DepartmentId varchar(4), 
  primary key (Id));
  -- 插入数据
  insert into Employee 
  values
  ('1', 'Joe', 70000, '1'),
  ('2', 'Henry', 80000, '2'),
  ('3', 'Sam', 60000, '2'),
  ('4', 'Max', 90000, '1');

 -- 创建表Department
  create table Department
  (Id varchar(4),
   Name varchar(20),
   primary key(Id));
   -- 插入数据
   insert into Department
   values
   ('1', 'IT'),
   ('2', 'Sales');

先把薪资由高到低排好序，再与部门表连结~

 select b.Name as Department, a.Name as Employee, a.Salary
  from
  (select  Salary, Name, DepartmentId,
  rank () over (partition by DepartmentId order by Salary DESC) as ranking
  from Employee) a 
  join Department as b
  on a.DepartmentID = b.Id
  where a.ranking = 1;

练习二: 换座位（难度：中等）

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的id是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

请创建如下所示seat表：

示例：

±--------±--------+
| id | student |
±--------±--------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
±--------±--------+
假如数据输入的是上表，则输出结果如下：

±--------±--------+
| id | student |
±--------±--------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
±--------±--------+
注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。

【解答】
上面想要的就是相邻两个学生互换！
先创建表

-- 创建表seat
create table seat
(id varchar(10),
 student varchar(20),
 primary key(id));
 -- 插入数据
 insert into seat
 values
 ('1', 'Abbot'),
 ('2', 'Doris'),
 ('3', 'Emerson'),
 ('4', 'Green'),
 ('5', 'Jeames');

思路：如果学号是奇数，则id+1；如果学号是偶数，则id-1；另外需要特别注意的是，如果学生人数为奇数，最后一个学生的 id 不变
因此在判断时，大体分为三种情况：
① id 为奇数， 且id不是学生总人数 则id+1；
② id 为奇数， 且id为学生总人数 则id不变；
③ id为偶数，则id-1

select seat.student,
(case 
when mod(id,2) != 0 and id != stu_sum then id+1
when mod(id,2) != 0 and id = stu_sum then id
else id-1 end) as id
from seat, (select count(*) as stu_sum from seat) as a
order by id;

如果不写order by id， 则不会按顺序排！！！（下图不对）

【答案（思路更加简单一丢丢了）】

select
	(case when id = (select max(id) from seat) then id
		 when mod(id,2) = 1 then id+1
		 when mod(id,2) = 0 then id-1
		 else null end)  as id,
	student
from seat
order by id;

练习三: 分数排名（难度：中等）

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

创建以下score表：

±—±------+
| Id | Score |
±—±------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
±—±------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

±------±-----+
| Score | Rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
±------±-----+
【解答】
创建表并插入数据

create table score
(Id varchar(5),
 Score float
);
insert into score
values
(1, 3.50),
(2, 3.65),
(3, 4.00),
(4, 3.85),
(5, 4.00),
(6, 3.65);

注：
① 创建表时，如果想某一列为小数可以用decimal(a,b)表示，a表示小数点左右两边总的位数，b表示小数点右边的位数；
② 在select里边要写round来控制最后输出的成绩的小数位数。

select round(Score,2),
dense_rank() over( order by Score DESC) as 'Rank'
from score;

提醒！！！！！
– 窗口函数
– PARTITION BY 用于分组，不分组时，省略即可
– ORDER BY 用于排序，默认升级，降序使用 DESC关键字
– RANK() 美式排名，跳过式排序，得分相同时排序相同
– DENSE_RANK() 中式排名， 递增式排序，得分相同时排序相同
– ROW_NUMBER() 赋予唯一连续的名次

练习四：连续出现的数字（难度：中等）

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

±—±----+
| Id | Num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

±----------------+
| ConsecutiveNums |
±----------------+
| 1 |
±----------------+

【解答】

create table Logs
(Id varchar(4),
 Num integer,
 primary key(Id));
 insert into Logs 
 values 
 ('1', 1),
 ('2', 1),
 ('3', 1),
 ('4', 2),
 ('5', 1),
 ('6', 2),
 ('7', 2);

-- 注意需要添加关键字 distinct，因为如果一个数字连续出现超过 3 次，会返回重复元素。
select distinct a.num as ConsecutiveNums
from 
Logs as a,
Logs as b,
Logs as c
where
a.Id = b.Id-1
and b.Id = c.Id-1
and a.Num = b.Num
and b.Num = c.Num;

练习五：树节点 （难度：中等）

对于tree表，id是树节点的标识，p_id是其父节点的id。

±—±-----+
| id | p_id |
±—±-----+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
±—±-----+
每个节点都是以下三种类型中的一种：

Root: 如果节点是根节点。
Leaf: 如果节点是叶子节点。
Inner: 如果节点既不是根节点也不是叶子节点。
写一条查询语句打印节点id及对应的节点类型。按照节点id排序。上面例子的对应结果为：

±—±-----+
| id | Type |
±—±-----+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
±—±-----+
说明

节点’1’是根节点，因为它的父节点为NULL，有’2’和’3’两个子节点。
节点’2’是内部节点，因为它的父节点是’1’，有子节点’4’和’5’。
节点’3’，‘4’，'5’是叶子节点，因为它们有父节点但没有子节点。
下面是树的图形：

1         

/ \
2 3
/
4 5
注意

如果一个树只有一个节点，只需要输出根节点属性。

【解答】
先创建tree表，并插入数据

create table tree
(id varchar(5),
 p_id varchar(10),
 primary key(id));
 insert into tree
 values
 ('1', null),
 ('2', 1),
 ('3', 1),
 ('4', 2),
 ('5', 2);

该题目分为以下几种情况：
① 树只有一个节点 则为根节点；
② p_id 为null 则为根节点；
③ id 中出现的但是没在p_id中出现，即为没有子节点 则为叶节点leaf；
④ 剩余情况则为 inner.

通俗来讲，p_id是爸爸，p_id为空的就是根节点；id是儿子，id为空的就是叶子；其余情况为inner

select distinct a.id,
(case 
when a.p_id is null then 'Root'
when b.id is null then 'Leaf'
else 'Inner' end ) as Type
from tree as a 
left join
tree as b
on a.id=b.p_id
order by id;

select id,
       (case when p_id is null then 'Root'
       		when id in (select p_id from tree) then 'Inner'
       		else 'Leaf' end) as Type
from tree; 

练习六：至少有五名直接下属的经理 （难度：中等）

Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。

±-----±---------±----------±---------+
|Id |Name |Department |ManagerId |
±-----±---------±----------±---------+
|101 |John |A |null |
|102 |Dan |A |101 |
|103 |James |A |101 |
|104 |Amy |A |101 |
|105 |Anne |A |101 |
|106 |Ron |B |101 |
±-----±---------±----------±---------+
针对Employee表，写一条SQL语句找出有5个下属的主管。对于上面的表，结果应输出：

±------+
| Name |
±------+
| John |
±------+
注意:

没有人向自己汇报。

【解答】

create table Employee
(Id char(3),
 Name varchar(10),
 Department varchar(8),
 ManagerId varchar(3),
 primary key(Id));
 
 insert into Employee
 values
 ('101', 'Jojn', 'A', null),
 ('102', 'Dan', 'A', '101'),
 ('103', 'James', 'A' ,'101'),
 ('104', 'Amy', 'A', '101'),
 ('105', 'Anne', 'A', '101'),
 ('106', 'Ron', 'B', '101');

select  a.name as Name
from
Employee as a
left join 
Employee as b
on
a.Id = b.ManagerId
where a.ManagerId is null
group by a.name
having count(b.ManagerId)>=5;

先把领导和从属员工连结起来，再一个where限制让a表出现的都是领导！！再按照领导分组，统计领导下边的员工数。

练习七: 分数排名 （难度：中等）

练习三的分数表，实现排名功能，但是排名需要是非连续的，如下：

±------±-----+
| Score | Rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 3 |
| 3.65 | 4 |
| 3.65 | 4 |
| 3.50 | 6 |
±------±-----
【解答】
因为是非连续排名，所以需要把练习三的窗口函数修改为rank()

select round(Score,2),
rank() over( order by Score DESC) as 'Rank'
from score;

练习八：查询回答率最高的问题 （难度：中等）

求出survey_log表中回答率最高的问题，表格的字段有：uid, action, question_id, answer_id, q_num, timestamp。

uid是用户id；action的值为：“show”， “answer”， “skip”；当action是"answer"时，answer_id不为空，相反，当action是"show"和"skip"时为空（null）；q_num是问题的数字序号。

写一条sql语句找出回答率最高的问题。

举例：

输入

输出

说明

问题285的回答率为1/1，然而问题369的回答率是0/1，所以输出是285。

**注意：**最高回答率的意思是：同一个问题出现的次数中回答的比例。
【解答】
思路：首先明确要group by 问题id，因此要选择问题id；然后计算回答率，分母是这个问题出现了几次(show)，分子是这个问题回答了几次(answer)，外边再套一个select选出回答率最高的问题。

create table survey_log
(uid varchar(5),
 action varchar(10),
 question_id varchar(5),
 answer_id varchar(10),
 q_num varchar(5),
 timestamp varchar(5));
 
 insert into survey_log
 values
 ('5', 'show', '285', 'null', '1', '123'),
 ('5', 'answer', '285', '124124', '1', '124'),
 ('5', 'show', '369', 'null', '2', '125'),
 ('5', 'skip', '369', 'null', '2', '126');

select question_id as 'survey_log'
from
(select question_id,
sum(case when action = 'answer' then 1 when action = 'skip' then 0 end)/sum(case when action = 'show' then 1 end) as answer_r
from survey_log
group by question_id
having answer_r > 0)a;

上面有点凑出来的答案的感觉，正确答案 ① 如下所示：

select case when max(a.answer_rate) then question_id end
from 
(
select question_id,
sum(case when action = 'answer' then 1 end)
/ sum(case when action <> 'skip' then 1 end) as answer_rate
from survey_log
group by 1
) a;

记住！！！case when max ()的用法哦！！！

正确答案②

select a.question_id
from (
 			select question_id,
 					   sum(case when answer_id is not null then 1 else 0 end) / sum(case when action = 'show' then 1 else 0 end) as ratio
 			from survey_log
 			group by question_id
 			order by ratio DESC
 			limit 1) a ;   

练习九：各部门前3高工资的员工（难度：中等）

将项目7中的employee表清空，重新插入以下数据（其实是多插入5,6两行）：

±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
±—±------±-------±-------------+
编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
±-----------±---------±-------+
此外，请考虑实现各部门前N高工资的员工功能。

【解答】

 select 
 b.Name as 'Department',
 a.Name as 'Employee',
 a.Salary
 from 
 (select DepartmentId,
		 Name,
         Salary,
         row_number() over
         (partition by DepartmentId order by Salary DESC) as ranking
           from Employee) as a
  join Department as b
  on a.DepartmentId = b.Id
  where a.ranking <= 3;