数分-工具-SQL

最新推荐文章于 2024-06-26 11:32:33 发布

yxyibb

最新推荐文章于 2024-06-26 11:32:33 发布

阅读量258

点赞数

分类专栏：数据分析文章标签： sql 数分准备

本文链接：https://blog.csdn.net/u012835414/article/details/120828313

版权

数据分析专栏收录该内容

19 篇文章 0 订阅

订阅专栏

本文详细介绍了SQL的基础知识和高级用法，包括按条件查询、分组查询、子查询、联结、排序、去重、聚合函数、窗口函数、存储过程、CASE WHEN语句等。还提供了丰富的实例练习，帮助读者掌握SQL在数据分析中的应用。

摘要由CSDN通过智能技术生成

数分-工具-SQL

(数据分析系列)

1 知识点

1.1 必须掌握的基础

按各种条件查询 select … from…where…
分组 group by
子句 where+update+delete+like
where语句（like,between,in\or,逻辑判断）
聚合函数
排序+去重 order by + distinct
联结（自联结、内联、外联）inner join + left join + right join
存储过程
case when
窗口函数
执行顺序
变量
空值处理 is null + is not null
count(*)，count(xx)
exist
正则
函数
索引
事务

1.2 难点

根据业务场景自己拆条件进行查询；
复杂场景，需要多层嵌套，不追求一次写好；按条件一条条写，再组合

2 具体内容

2.1 准备

安装MySQL（navicat），连接（注意非root用户）
库 (create+drop+use)

//1建库
CREATE database shop;
//2删库
DROP database shop;
//3选库
USE shop;

表 (create+alter+drop)

//1创表
//CREATE TABLE table_name (column_name column_type);
CREATE TABLE table_name (column_name column_type);
CREATE TABLE product
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER ,
 purchase_price INTEGER ,
 regist_date DATE ,
 PRIMARY KEY (product_id));
 
 //2改表
ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);  
ALTER TABLE product DROP COLUMN product_name_pinyin;
 //3删表
 DROP TABLE product;

数据 (select+insert)
1. 查询 select
2. 插入insert into

//1查询
SELECT xx FROM xx WHERE xx //最简单最复杂 

//2INSERT INTO
//包含清单
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
//不包含清单
INSERT INTO productins VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');  
//多行
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
                              ('0003', '运动T恤', '衣服', 4000, 2800, NULL),
                              ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');  
//2从其他表复制数据INSERT … SELECT 语句
INSERT INTO productcopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
  FROM Product;

数据类型
1. integer
2. float
3. DATETIME, DATE, TIMESTAMP, TIME, YEAR
4. CHAR(N), VARCHAR;N字符个数

2.2 按各种条件查询

select … from…where…
数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用-
having
用于对where和group by查询出来的分组经行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。
- having子句即可包含聚合函数作用的字段也可包括普通的标量字段
- having子句必须于group by 子句同时使用，不能单独使用
group by
对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值

2.3 分组查询 group by

//分组列上使用 COUNT, SUM, AVG,等函数
SELECT column_name, function(column_name)//聚合函数
FROM table_name
WHERE column_name operator value
GROUP BY column_name;//group by后，只能select该列名及func

//WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
//eg:数据表按名字进行分组，再统计每个人登录的次数
SELECT name, SUM(singin) as singin_count 
FROM  employee_tbl 
GROUP BY name WITH ROLLUP;

2.4 子查询（where + update + delete +like）

where
在初始表中筛选查询，约束数据，在返回结果集之前起作用

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

结合：
- like
- between
- in\or
- 逻辑判断

like
使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

注意：
1. %：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示 
2. '%a%'  ，含有a的数据
3.  _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语
4. '_a_'   ，三位且中间字母是a的
5. []：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
6. [^]：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
7. 查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]”括起便可正常查询。

update （指定where子句，否则所有数据都更新）

//update 表名称 set 列名称=新值 where 更新条件;
UPDATE runoob 
set runoob_title = 'study' 
where runoob_id = 4

//update 表名称 set 列名称=REPLACE(列名，旧址，新值) where 更新条件;
UPDATE runoob_tbl  
SET runoob_title = REPLACE(runoob_title, 'C++', 'Python') 
where runoob_id = 3;

delete （指定where子句，否则所有数据都删除）

//delete from 表名称 where 删除条件;
DELETE FROM table_name [WHERE Clause]
//
DELETE FROM runoob_tbl WHERE runoob_id=3;

注意：
1. delete，drop，truncate 都有删除表的作用
2. delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除
3. delete 是 DML 语句，操作完以后如果不想提交事务还可以回滚
4. truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚
5. 执行的速度上，drop>truncate>delete

2.5 聚合函数

函数：

avg
- 某行的值为null时，计算平均值会忽略带有null值一行
- 想把null转为0，avg(isnull(score,0))
count

count(*) 包括空值
count(xx) 不包括空值
sum 必须是数字列
floor 返回小于或等于 x 的最大整数
min
max
mod 不咋用

使用：

select语句，选择列表（子查询+外部查询）
与分组group by结合
做过滤条件
having子句
compute+compute by 子句

2.6 联结（自联结、内联、外联）

INNER JOIN（内连接,或等值连接）：获取两个表中字段交集匹配关系的记录
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录
RIGHT JOIN（右连接）：与 LEFT JOIN相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

2.7 排序+去重

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], 
         [field2...] [ASC [DESC][默认 ASC]]

2.8 存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它

//修改默认的sql语句的结尾标志为%
delimiter %
//创建：
create procedure selCg()
begin 
select * from test.`user`;
end%
//调用：
call selCg()%

2.9 case when

//简单case函数
case xx 
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

//Case搜索函数 
CASE WHEN sex = '1' THEN '男' 
WHEN sex = '2' THEN '女' 
ELSE '其他' END

2.10 窗口函数

业务：排名问题+topN问题
功能：同时具有分组和排序的功能，不减少原表的行数

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

窗口函数的位置可以放：
- 专用窗口函数，包括后面要讲到的rank(1,1,1,4), dense_rank(1,1,1,2), row_number(1,2,3,4)等专用窗口函数
- 聚合函数，如sum. avg, count, max, min等
窗口函数原则上只能写在select子句中

2.11 执行顺序

FROM, including JOINs
WHERE
GROUP BY
HAVING
WINDOW functions
SELECT
DISTINCT
UNION
ORDER BY
LIMIT and OFFSET

2.12 空值处理

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
使用 IS NULL 和 IS NOT NULL 运算符，不可以使用=null，!=null

2.13 exist、not exist

检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测行的存在，exists返回true
in，后面的子查询是返回结果集
执行次序和exists()不一样。子查询先产生结果集，主查询再去结果集里去找符合要求的字段列表去，符合要求的输出,反之则不输出

3 练习

在这里插入图片描述

3.1like（2.4.2）

‘%字符’/’%字符%’/‘字符%’

查询名字中含有「风」字的学生信息【Like】
- 查询姓名中最后一个字是“猴”的学生名单
- 查询姓名中姓“猴”的学生名单

-- 1. 查询姓“风”的学生名单
SELECT * 
from student
WHERE s_name like '%风%';
-- '%风'，'风%'

查询姓“李”老师的个数

--  2. 查询姓“李”老师的个数
SELECT * 
from teacher
WHERE t_name like '李%';

3.2 聚合函数、group by和where

1.聚合函数sum/avg/count/max/min经常与好基友group by搭配使用
2.在使用group by时，select后面只能放

常数（如数字/字符/时间）
聚合函数
聚合键（也就是group by后面的列名）；

因此，在使用group by时，千万不要在select后面放聚合键以外的列名！

3.where函数后面不能直接使用聚合函数！（考虑放在having后面/变成子查询放在where后面）

查询男生、女生人数【聚合函数】

SELECT s_sex, count(s_id) 
from student
GROUP BY s_sex;

查询课程编号为02的总成绩【聚合函数】

SELECT c_id, sum(s_score)
FROM score
GROUP BY c_id
HAVING c_id='02';
-- 本题指定了课程号，只计算02号课程，GROUP BY分组后having过滤取出02课程的成绩，sum

查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列【聚合函数】

SELECT c_id, AVG(s_score)as avg0 
FROM score
GROUP BY c_id
ORDER BY avg0 DESC, c_id;

求每门课程的学生人数【聚合函数】

SELECT c_id, COUNT(s_id)
FROM score
GROUP BY c_id;

统计每门课程的学生选修人数（超过 5 人的课程才统计）【聚合函数】

SELECT c_id, COUNT(s_id) as stu_count
FROM score
GROUP BY c_id
HAVING stu_count>5;

检索至少选修两门课程的学生学号【聚合函数】

SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(c_id)>2;

3.3 子查询

1.select的结果列全部来自同一张表（而select的条件列来自不同表里），考虑子查询；
select的结果列来自多张表，考虑联结
2.子查询与in/not in是好基友
3.“所有/全部/都"类型的查询可以考虑not in求补集

查询在 SC 表存在成绩的学生信息【子查询】

SELECT *
FROM student
WHERE s_id in (SELECT s_id FROM score where s_score is not NULL);

查询不存在" 01 “课程但存在” 02 "课程的情况【子查询】

SELECT *
FROM student
WHERE s_id not in (
	SELECT s_id 
	FROM score
	WHERE c_id = '01'
)AND
s_id in (
	SELECT s_id 
	FROM score
	WHERE c_id = '02');

查询同时存在" 01 “课程和” 02 "课程的情况【子查询】

-- 思路1
-- 1)选择01课程+选02课程（两个子查询）
-- 2）student表中，id在选1的 AND id在选2的
SELECT *
FROM student
WHERE s_id in 
(SELECT s_id FROM score WHERE c_id='01')
AND s_id in 
(SELECT s_id FROM score WHERE c_id='02');

-- 思路2
-- 1)选择01课程+选02课程（两个子查询）
-- 2）join连接到一起，形成同时存在的新表
-- 3）学生表查s_id存在的情况
SELECT *
FROM student
WHERE s_id IN( 
SELECT a.s_id 
FROM 
(SELECT s_id FROM score WHERE c_id='01') a
JOIN
(SELECT s_id FROM score WHERE c_id='02') b
on a.s_id = b.s_id);

查询出只选修两门课程的学生学号和姓名【子查询】

SELECT s_id, s_name
FROM student
WHERE s_id in (
SELECT s_id FROM score
GROUP BY s_id
HAVING COUNT(c_id)=2);

查询没有学全所有课程的同学的信息【子查询】

-- 1)count计算所有课程数
-- 2）GROUP BY+having 查c_id=1）条件的所有s_id
-- 3）student表查s_id 在2）条件下的所有id

select * FROM student
WHERE s_id not in (
select s_id FROM score
GROUP BY s_id
HAVING COUNT(c_id)=(
SELECT count(c_id)
FROM course));

查询选修了全部课程的学生信息【子查询】

select * FROM student
WHERE s_id in (
select s_id FROM score
GROUP BY s_id
HAVING COUNT(c_id)=(
SELECT count(c_id)
FROM course));

查询所有课程成绩均小于60分的学号、姓名【子查询】

-- 1)有课程>=60
-- 2)not in 求补集
-- 3)!!!有学生未选课，刨除(不在score表里)
SELECT * FROM student
WHERE s_id not in 
(SELECT DISTINCT s_id FROM score 
WHERE s_score >=60
)AND s_id in 
(SELECT DISTINCT s_id FROM score);

查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名【子查询】

SELECT s_id, s_name
FROM student
WHERE s_id in (
select s_id
FROM score
WHERE c_id = '01' AND s_score>=80);

查询学过「张三」老师授课的同学的信息【子查询】

-- 1）teacher与course表,找到张三老师授课号c_id
-- 2）score找到相关c_id的s_id，student查信息
SELECT * FROM student WHERE s_id in (
SELECT s_id FROM score WHERE c_id in (
SELECT c_id FROM course WHERE t_id in (
select t_id FROM teacher WHERE t_name='张三')));
--  四表串联查询。
select * from student 
where s_id in (select s_id from score, course, teacher
where teacher.t_name='张三' and course.t_id=teacher.t_id and score.c_id=course.c_id);

查询没学过"张三"老师讲授的任一门课程的学生姓名【子查询】

-- 1)学过张三课程的s_id
-- 2）student里求补集
-- 3)使用join连结表的形式，找条件
SELECT s_id, s_name FROM student
WHERE s_id not in (
select DISTINCT s_id FROM score
JOIN (
	SELECT c_id FROM course
	JOIN (SELECT t_id FROM teacher WHERE t_name='张三') as tt
	ON course.t_id = tt.t_id) as cc
on score.c_id = cc.c_id);

select s_id, s_name from student
where s_id not in (select s_id from score, course, teacher
where teacher.t_name='张三' and course.t_id=teacher.t_id and score.c_id=course.c_id);

查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息【子查询】

-- 1)01学的课程
-- 2）c_id在1）里的s_id！！！！注意刨除01同学
-- 3）查student
SELECT * FROM student
WHERE s_id in 
(SELECT DISTINCT s_id FROM score
WHERE c_id in 
(SELECT c_id FROM score WHERE s_id='01') 
AND s_id != '01');

-- 用join的方式连结两个字表(表比较大的时候，效率相对高)
SELECT a.* FROM student as a
INNER JOIN 
(SELECT DISTINCT s_id FROM score
WHERE c_id in 
(SELECT c_id FROM score WHERE s_id='01') 
AND s_id != '01') as b
on a.s_id=b.s_id;

查询和" 01 "号的同学学习的课程完全相同的其他同学的信息【子查询】

-- 1）选s_id，所学课程不咋01学的里面-排除
-- 2）剩下选了01,02,03某几门，看看选课数目是否是3
SELECT * from student
WHERE
s_id not in
(SELECT s_id FROM score
WHERE c_id not in 
(select c_id FROM score WHERE s_id='01'))
AND
s_id in 
(select s_id from score
WHERE s_id !='01'
GROUP BY s_id
HAVING count(DISTINCT c_id)=(select count(c_id) FROM score WHERE s_id='01')
)

查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩【子查询】

-- 注意：题目本身有歧义，理解是找出选了不止一门课，且自己所有课程分数相同的学生。 

-- 1.选了不同课
-- 2.所有课程分数相同-》最大值=最小值
SELECT * from score
WHERE s_id in 
(SELECT s_id FROM score
GROUP BY s_id
HAVING min(s_score) = max(s_score) AND COUNT(*)>1);

3.4 联结：inner join/outer join

在这里插入图片描述

检索" 01 "课程分数小于 60，按分数降序排列的学生信息【inner join】

-- 1)01课程的score<60连结学生表
-- 2)降序，学生表
SELECT stu.*, sc.s_score
FROM student stu
INNER JOIN
(SELECT s_id, s_score from score
WHERE c_id='01' and s_score < 60) sc
on stu.s_id = sc.s_id
ORDER BY sc.s_score DESC;

查询不及格的课程及学生名，学号，按课程号从大到小排列【inner join】

-- 1）课程<60，部分哪门课，score表只要<60
-- 2）连结student表找到对应s_id，s_name
-- 3）课程号降序
select sc.c_id, stu.s_id,stu.s_name
FROM student as stu
JOIN score sc
on stu.s_id = sc.s_id
WHERE sc.s_score < 60
ORDER BY sc.c_id desc;

查询课程名称为「数学」，且分数低于 60 的学生姓名和分数【inner join】

-- 1）数学-》score的c_id
-- 2)s_score<60
select stu.s_name, scc.c_id, scc.s_score FROM student stu
join
(select sc.* FROM score sc
join (SELECT c_id FROM course WHERE c_name='数学') ci
on sc.c_id = ci.c_id
WHERE sc.s_score<60) as scc
on stu.s_id= scc.s_id;


select stu.s_name, sc.c_id, sc.s_score FROM student stu
join score sc
on stu.s_id= sc.s_id
WHERE sc.s_score<60 and sc.c_id=(SELECT c_id FROM course WHERE c_name='数学');

查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩【inner join】

-- 1)join student
-- 2)GROUP BY s_id, 再计算avg(s_score)>85
SELECT stu.s_id, stu.s_name, avg(sc.s_score)
from student as stu
JOIN score as sc
on sc.s_id = stu.s_id
GROUP BY sc.s_id
HAVING avg(sc.s_score) > 85;

查询不同老师所教不同课程平均分从高到低显示【inner join】

-- 1)t_name, c_id 表连结,一个老师一对一，一门课，c_id即唯一
-- 2）orderby avg
select co.t_id, sc.c_id, avg(sc.s_score) avg_co_score
FROM score sc
join course co
on sc.c_id = co.c_id
GROUP BY c_id 
ORDER BY avg(sc.s_score) desc;

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩【inner join】

-- 1）student信息 join AVG（score）>60
SELECT stu.s_id, stu.s_name, avg(sc.s_score)as avg_stu_score
FROM student stu
INNER JOIN score sc
on stu.s_id = sc.s_id
GROUP BY sc.s_id
HAVING avg(sc.s_score)>=60;

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩【inner join】

SELECT stu.s_id,  stu.s_name, avg(sc.s_score)as avg_stu_score
FROM student stu
INNER JOIN score sc
on stu.s_id = sc.s_id
WHERE sc.s_score <60 
GROUP BY sc.s_id
HAVING count(sc.s_score)>=2;

查询同名同性学生名单，并统计同名同性人数【inner join】

select stu.*, cs.stu_num FROM student as stu
join 
(SELECT s_name, s_sex, count(*) as stu_num FROM  student
GROUP BY s_name, s_sex
HAVING COUNT(*) >1) cs
on stu.s_name = cs.s_name
AND stu.s_sex = cs.s_sex;

查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）【outer join】

select stu.*, sc.c_id, sc.s_score FROM student stu
left join score sc
on stu.s_id=sc.s_id

-- 表不好看
select stu.*, c.c_name, sc.s_score FROM score sc
join course c on sc.c_id = c.c_id
join student stu on stu.s_id=sc.s_id
-- 想把表内，语数外拉成列名，显示
-- !!!case when 小技巧！！！行变列！！！
-- 一般只会返回第一条，没数的地方直接返回null了，有数的地方正常，所以，用max把有数的选出来
-- sum函数也可以，把数能取出来就行！
select stu.s_id, stu.s_name,
max(case when c.c_name='语文' then sc.s_score else null end) as '语文',
max(case when c.c_name='数学' then sc.s_score else null end) as '数学',
max(case when c.c_name='英语' then sc.s_score else null end) as '英语'
FROM score sc
join course c on sc.c_id = c.c_id
join student stu on stu.s_id=sc.s_id
GROUP BY stu.s_id, stu.s_name

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。(没成绩的显示为 null )【outer join】

select stu.s_id, stu.s_name, count(sc.c_id), sum(sc.s_score) FROM score sc
RIGHT JOIN student stu
on stu.s_id=sc.s_id
GROUP BY sc.s_id;

查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )【outer join】

-- 1）存在01
-- 2）存在02
-- 3）1）leftjoin 2）可能存在，只有选1，是否选2都要查
select a.*, b.c_id, b.s_score from
(select * FROM score WHERE c_id='01') a
LEFT JOIN
(select * FROM score WHERE c_id='02') b
on a.s_id = b.s_id;

查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数【三表联结】

-- 1）score>70
-- 2)三表连结

-- where约束条件在三遍连结里还是外面，无所谓
select stu.s_name, c.c_name,sc.s_score
from score as sc
inner join student stu on sc.s_id=stu.s_id
inner join course c on sc.c_id = c.c_id
WHERE sc.s_score>70;

select stu.s_name, c.c_name,sc.s_score
from student stu
join (select * from score WHERE s_score>70) sc on stu.s_id=sc.s_id
join course c on sc.c_id = c.c_id;

-- 表不好看，参考30题，行转列！！！
-- 1）注意，要按s_name，groupby,把科目分组
-- 2）避免取null，用max（casewhen）
-- 3）不会做的时候，一层一层调，把max函数去掉，从问题根源出发，取语文值，取数学值！
select stu.s_name,
max(case when c.c_name='语文' then sc.s_score else null end) as '语文',
max(case when c.c_name='数学' then sc.s_score else null end) as '数学',
max(case when c.c_name='英语' then sc.s_score else null end) as '英语'
from student stu
join score sc on stu.s_id=sc.s_id
join course c on sc.c_id = c.c_id
WHERE sc.s_score>70
GROUP BY stu.s_name;

查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数【三表联结】

-- 1）01比02高！sc，stu，c
-- 2）01查出来一个表，02查出来一个表
SELECT stu.*, a.s_score as '01', b.s_score as '02'
FROM student stu
INNER JOIN
(SELECT * FROM score WHERE c_id='01') a on stu.s_id=a.s_id
join
(SELECT * FROM score WHERE c_id='02') b on stu.s_id=b.s_id
and a.s_score > b.s_score;

3.5 连结+limit

【limit易错点】
- limit m,n 表示从第m+1条记录开始，共选n条记录，如limit 1,2 选的是第2、3条记录（很容易误以为是选1、2条记录）
- limit n 表示选择前n条记录，是limit 0,n 的省略

成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩【limit】

-- 1)张三t_id-> c_id,score表c_id->s_id
-- 2)DESC排序limit 1
 
#多表连结的思路，此题每个表唯一且成绩不重复(一对一)，也直接“=”
select stu.*, sc.s_score FROM score sc
join course c on sc.c_id=c.c_id
join teacher t on c.t_id=t.t_id
join student stu on sc.s_id = stu.s_id
WHERE t.t_name='张三'
ORDER BY sc.s_score DESC LIMIT 1;

select stu.*, sc.s_score FROM score sc
join student stu on sc.s_id = stu.s_id
WHERE sc.c_id=(SELECT c_id FROM course WHERE t_id = (SELECT t_id from teacher WHERE t_name='张三'))
ORDER BY sc.s_score DESC LIMIT 1;#（也可直接max）


#where根据约束条件+limit1（也可直接max）
select stu.*, sc.s_score 
FROM student stu, score sc, course c, teacher t
WHERE t.t_name='张三' AND c.t_id=t.t_id AND sc.c_id=c.c_id and stu.s_id=sc.s_id
ORDER BY sc.s_score DESC LIMIT 1;

成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩【limit】

-- 1）同上，但是成绩有重复，
-- 2)有重复的情况，需要把课程单拿出来，连结子表（stu与score的联结表，这里提前取出来）

select a.* FROM
(select stu.*, sc.c_id, sc.s_score FROM score sc, student stu
WHERE sc.s_id = stu.s_id) a
join 
(select c_id, max(s_score) as max_score
FROM score sc
WHERE sc.c_id=(
	SELECT c_id FROM course c, teacher t
	WHERE t.t_name='张三'
	AND t.t_id = c.t_id) 
	GROUP BY c_id) b
on a.c_id=b.c_id
and a.s_score = b.max_score;

3.6 连结+窗口函数 rank/dense_rank/row_number

rank/dense_rank/row_number等窗口函数只能写在select后面，不能直接写在where后面，要筛选窗口函数的结果列时，先写子查询，再用where+列别名完成

按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺【窗口函数】

-- 1）各科成绩排序，排名
-- 2）重复保留名词空缺-> rank
SELECT *, 
rank() over(partition by c_id ORDER BY s_score desc) as score_rank
FROM score;

按各科成绩进行排序，并显示排名，Score 重复时合并名次【窗口函数】

-- 1）重复时合并名次-> dense_rank
SELECT *, 
dense_rank() over(partition by c_id ORDER BY s_score desc) as score_rank
FROM score;

查询学生的总成绩，并进行排名，总分重复时保留名次空缺【窗口函数】

SELECT s_id, sum(s_score), 
rank() over (ORDER BY sum(s_score)desc) as sum_rank
FROM score
GROUP BY s_id;

查询学生的总成绩，并进行排名，总分重复时不保留名次空缺【窗口函数】

SELECT s_id, sum(s_score),
DENSE_RANK() over (ORDER BY sum(s_score) desc) as sum_rank
FROM score
GROUP BY s_id;

查询每门功成绩最好的前两名【窗口函数】

-- 1)成绩最好的前两个，用rank，保留名次空缺
-- 2）rank函数只能用在select后面，不能在where后面，所以要先rank子查询形成子表，在select子表内容，用where约束
select * FROM(
select *, rank() over(PARTITION by c_id ORDER BY s_score desc) as c_rank
FROM score) a
where c_rank <=2;

查询各科成绩前三名的记录【窗口函数】

-- 1）前三名，不保留名次，就是前三
select * FROM(
select *, dense_rank() over(PARTITION by c_id ORDER BY s_score desc) as c_rank
FROM score) a
where c_rank <=3;

查询所有课程成绩第2名到第3名的学生信息及该课程成绩【窗口函数】

-- 1）同42，dense_rank，找2-3名
select stu.*, sr.c_id, sr.s_score, sr.c_rank FROM student stu
join
(select *, dense_rank() over(PARTITION by c_id ORDER BY s_score desc) as c_rank
FROM score) sr
on stu.s_id = sr.s_id
where c_rank in (2,3);

3.7 case 结合聚合函数来旋转行列字段是一种常用技巧

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩【case】

-- 1)平均成绩 按s_id分组
-- 2)每门课成绩，行转列,这里课程用name表示更好看，用到的表内join，course表
select stu.s_name, scm.* from
(select sc.s_id,
 max(case when c.c_name='语文' then s_score else null end) as '语文',
 max(case when c.c_name='数学' then s_score else null end) as '数学',
 max(case when c.c_name='英语' then s_score else null end) as '英语',
 avg(s_score) as avg_sc
FROM score sc 
join course c on c.c_id = sc.c_id
GROUP BY s_id) as scm
join student stu on stu.s_id = scm.s_id;

使用分段[100-85]，[85-70]，[70-60]，[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称【case】

-- 1）c_id，c_name
-- 2）分段 casewhen,人数sum+

select sc.c_id, c.c_name,
sum(case when sc.s_score >=85 then 1 else 0 end) as '[100-85]',
sum(case when sc.s_score >=70 and sc.s_score<85 then 1 else 0 end) as '[85-70]',
sum(case when sc.s_score >=60 and sc.s_score<70 then 1 else 0 end) as '[70-60]',
sum(case when sc.s_score <60 then 1 else 0 end) as '[<60]'
FROM score sc
join course c
on sc.c_id = c.c_id
GROUP BY sc.c_id;

查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID ，课程 name ，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 【case】

-- 1)casewhen+sum 计数
-- 2）分数比率=1.的结果/选课人数
select sc.c_id, c.c_name, max(sc.s_score) as max, min(sc.s_score) as min, avg(sc.s_score) as avg,
sum(case when sc.s_score >=60 then 1 else 0 end)/count(sc.s_score) as '及格率',
sum(case when sc.s_score >=70 and sc.s_score<80 then 1 else 0 end)/count(sc.s_score) as '中等率',
sum(case when sc.s_score >=80 and sc.s_score<90 then 1 else 0 end)/count(sc.s_score) as '优良率',
sum(case when sc.s_score >=90 then 1 else 0 end)/count(sc.s_score) as '优秀率'
FROM score sc
join course c
on sc.c_id = c.c_id
GROUP BY c_id;

统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比【case】

-- 1）比45加了百分比/count
select sc.c_id, c.c_name,
sum(case when sc.s_score >=85 then 1 else 0 end) as '[100-85]',
sum(case when sc.s_score >=85 then 1 else 0 end)/count(sc.s_score) as '[100-85]',
sum(case when sc.s_score >=70 and sc.s_score<85 then 1 else 0 end) as '[85-70]',
sum(case when sc.s_score >=70 and sc.s_score<85 then 1 else 0 end)/count(sc.s_id) as '[85-70]',
sum(case when sc.s_score >=60 and sc.s_score<70 then 1 else 0 end) as '[70-60]',
sum(case when sc.s_score >=60 and sc.s_score<70 then 1 else 0 end)/count(sc.s_id)  as '[70-60]',
sum(case when sc.s_score <60 then 1 else 0 end) as '[<60]',
sum(case when sc.s_score <60 then 1 else 0 end)/count(sc.s_id)  as '[<60]'
FROM score sc
join course c
on sc.c_id = c.c_id
GROUP BY sc.c_id;

3.8 时间函数

在mysql里计算时间差：

日期差：datediff(time终, time始)
指定时间差：timestampdiff(timestamp, time始, time终)
令人崩溃的一点是，即便是如此相近的两个时间差函数，其后面时间参数的顺序居然是相反的，如果计算结果出现了负数，考虑参数写反的可能性。

查询各学生的年龄，只按年份来算【时间函数】

-- YEAR(date)函数，求出current_date与s_birth差
select s_id, s_name, year(current_date())-year(s_birth) as age
FROM student;

按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一【时间函数】

-- 使用timestampdiff()求差，将时间戳设置为年即可
select s_id, s_name,year(current_date())-year(s_birth) as age, TIMESTAMPDIFF(year,s_birth, CURRENT_DATE()) as age2
FROM student;

询本月过生日的学生【时间函数】

-- month函数分别求出current_date和sage的月份，相同即可
select s_id, s_name, s_birth FROM student
WHERE month(s_birth)=month(CURRENT_DATE())-3;

查询下月过生日的学生【时间函数】

select s_id, s_name, s_birth FROM student
WHERE month(s_birth)=month(CURRENT_DATE())+1;

查询本周过生日的学生【时间函数】*

select s_id, s_name, s_birth FROM student
WHERE month(s_birth)=month(CURRENT_DATE())+1;

查询下周过生日的学生【时间函数】*

select s_id, s_name, s_birth FROM student
WHERE week (REPLACE(s_birth,year(s_birth),YEAR(CURRENT_DATE())),1)= week(current_date(),1)+1;

注：

WEEK(date[,mode])：
此函数返回日期的周数。双参数的形式WEEK()允许你指定星期是否开始于周日或周一，以及是否返回值应在范围从0到53或从1到53。如果省略了mode参数，系统default_week_format变量的值被使用。

4 参考

http://note.youdao.com/noteshare?id=efd762832d91e1e30bc8e61278041101&sub=F019BD8EC79A4EE39CCC37C826A553DC
https://zhuanlan.zhihu.com/p/113173133

yxyibb

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

数分-工具-SQL

数分-工具-SQL

文章目录

1 知识点

1.1 必须掌握的基础

1.2 难点

2 具体内容

2.1 准备

2.2 按各种条件查询

2.3 分组查询 group by

2.4 子查询 （where + update + delete +like）

2.5 聚合函数

2.6 联结（自联结、内联、外联）

2.7 排序+去重

2.8 存储过程

2.9 case when

2.10 窗口函数

2.11 执行顺序

2.12 空值处理

2.13 exist、not exist

3 练习

3.1like（2.4.2）

3.2 聚合函数、group by和where

3.3 子查询

3.4 联结：inner join/outer join

3.5 连结+limit

3.6 连结+窗口函数 rank/dense_rank/row_number

3.7 case 结合聚合函数来旋转行列字段是一种常用技巧

3.8 时间函数

4 参考

2.4 子查询（where + update + delete +like）