数分-工具-SQL

最新推荐文章于 2023-07-25 14:11:39 发布
最新推荐文章于 2023-07-25 14:11:39 发布
阅读量257 收藏
点赞数
分类专栏： 数据分析 文章标签： sql 数分准备
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u012835414/article/details/120828313
版权
本文详细介绍了SQL的基础知识和高级用法，包括按条件查询、分组查询、子查询、联结、排序、去重、聚合函数、窗口函数、存储过程、CASE WHEN语句等。还提供了丰富的实例练习，帮助读者掌握SQL在数据分析中的应用。
摘要由CSDN通过智能技术生成

数分-工具-SQL

(数据分析系列)

文章目录

1 知识点

1.1 必须掌握的基础

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

1.2 难点

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

2 具体内容

2.1 准备

  1. 安装MySQL（navicat），连接（注意非root用户）
  2. 库 (create+drop+use)
//1建库
CREATE database shop;
//2删库
DROP database shop;
//3选库
USE shop;
  1. 表 (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;
  1. 数据 (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. 数据类型
    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）

  1. where
    在初始表中筛选查询，约束数据，在返回结果集之前起作用
SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

结合：
- like
- between
- in\or
- 逻辑判断
  1. 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. 查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]”括起便可正常查询。
  1. 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;
  1. 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 窗口函数

  1. 业务：排名问题+topN问题
  2. 功能：同时具有分组和排序的功能，不减少原表的行数
<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)
  1. 窗口函数的位置可以放：
    • 专用窗口函数，包括后面要讲到的rank(1,1,1,4), dense_rank(1,1,1,2), row_number(1,2,3,4)等专用窗口函数
    • 聚合函数，如sum. avg, count, max, min等
  2. 窗口函数原则上只能写在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）

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

  1. 查询名字中含有「风」字的学生信息【Like】
    • 查询姓名中最后一个字是“猴”的学生名单
    • 查询姓名中姓“猴”的学生名单
-- 1. 查询姓“风”的学生名单
SELECT * 
from student
WHERE s_name like '%风%';
-- '%风'，'风%'
  1. 查询姓“李”老师的个数
--  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后面）

  1. 查询男生、女生人数【聚合函数】
SELECT s_sex, count(s_id) 
from student
GROUP BY s_sex;
  1. 查询课程编号为02的总成绩【聚合函数】
SELECT c_id, sum(s_score)
FROM score
GROUP BY c_id
HAVING c_id='02';
-- 本题指定了课程号，只计算02号课程，GROUP BY分组后having过滤取出02课程的成绩，sum
  1. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列【聚合函数】
SELECT c_id, AVG(s_score)as avg0 
FROM score
GROUP BY c_id
ORDER BY avg0 DESC, c_id;
  1. 求每门课程的学生人数 【聚合函数】
SELECT c_id, COUNT(s_id)
FROM score
GROUP BY c_id;
  1. 统计每门课程的学生选修人数（超过 5 人的课程才统计）【聚合函数】
SELECT c_id, COUNT(s_id) as stu_count
FROM score
GROUP BY c_id
HAVING stu_count>5;
  1. 检索至少选修两门课程的学生学号 【聚合函数】
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求补集

  1. 查询在 SC 表存在成绩的学生信息【子查询】
SELECT *
FROM student
WHERE s_id in (SELECT s_id FROM score where s_score is not NULL);
  1. 查询不存在" 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');
  1. 查询同时存在" 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);
  1. 查询出只选修两门课程的学生学号和姓名【子查询】
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. 查询没有学全所有课程的同学的信息 【子查询】
-- 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));
  1. 查询选修了全部课程的学生信息【子查询】
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));
  1. 查询所有课程成绩均小于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);
  1. 查询课程编号为 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. 查询学过「张三」老师授课的同学的信息 【子查询】
-- 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. 查询没学过"张三"老师讲授的任一门课程的学生姓名 【子查询】
-- 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);
  1. 查询至少有一门课与学号为" 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;
  1. 查询和" 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. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 【子查询】
-- 注意：题目本身有歧义，理解是找出选了不止一门课，且自己所有课程分数相同的学生。 

-- 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

在这里插入图片描述

  1. 检索" 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;
  1. 查询不及格的课程及学生名，学号，按课程号从大到小排列【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;
  1. 查询课程名称为「数学」，且分数低于 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='数学');
  1. 查询平均成绩大于等于 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;
  1. 查询不同老师所教不同课程平均分从高到低显示【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;
  1. 查询平均成绩大于等于 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;
  1. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩【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;
  1. 查询同名同性学生名单，并统计同名同性人数【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;
  1. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）【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
  1. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。(没成绩的显示为 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;
  1. 查询存在" 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;
  1. 查询任何一门课程成绩在 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;
  1. 查询" 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 的省略
  1. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩【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;
  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+列别名完成

  1. 按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺【窗口函数】
-- 1）各科成绩排序，排名
-- 2）重复保留名词空缺-> rank
SELECT *, 
rank() over(partition by c_id ORDER BY s_score desc) as score_rank
FROM score;
  1. 按各科成绩进行排序，并显示排名，Score 重复时合并名次【窗口函数】
-- 1）重复时合并名次-> dense_rank
SELECT *, 
dense_rank() over(partition by c_id ORDER BY s_score desc) as score_rank
FROM score;
  1. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺【窗口函数】
SELECT s_id, sum(s_score), 
rank() over (ORDER BY sum(s_score)desc) as sum_rank
FROM score
GROUP BY s_id;
  1. 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺【窗口函数】
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. 查询每门功成绩最好的前两名【窗口函数】
-- 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. 查询各科成绩前三名的记录【窗口函数】
-- 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;
  1. 查询所有课程成绩第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 结合聚合函数来旋转行列字段是一种常用技巧

  1. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩【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;
  1. 使用分段[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;
  1. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 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;
  1. 统计各科成绩各分数段人数：课程编号，课程名称，[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终)
    令人崩溃的一点是，即便是如此相近的两个时间差函数，其后面时间参数的顺序居然是相反的，如果计算结果出现了负数，考虑参数写反的可能性。
  1. 查询各学生的年龄，只按年份来算【时间函数】
-- YEAR(date)函数，求出current_date与s_birth差
select s_id, s_name, year(current_date())-year(s_birth) as age
FROM student;
  1. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一【时间函数】
-- 使用timestampdiff()求差，将时间戳设置为年即可
select s_id, s_name,year(current_date())-year(s_birth) as age, TIMESTAMPDIFF(year,s_birth, CURRENT_DATE()) as age2
FROM student;
  1. 询本月过生日的学生【时间函数】
-- month函数分别求出current_date和sage的月份，相同即可
select s_id, s_name, s_birth FROM student
WHERE month(s_birth)=month(CURRENT_DATE())-3;
  1. 查询下月过生日的学生【时间函数】
select s_id, s_name, s_birth FROM student
WHERE month(s_birth)=month(CURRENT_DATE())+1;
  1. 查询本周过生日的学生【时间函数】*
select s_id, s_name, s_birth FROM student
WHERE month(s_birth)=month(CURRENT_DATE())+1;
  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 参考

  1. http://note.youdao.com/noteshare?id=efd762832d91e1e30bc8e61278041101&sub=F019BD8EC79A4EE39CCC37C826A553DC
  2. https://zhuanlan.zhihu.com/p/113173133
yxyibb
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL2022-SSEI-Dev
11-14
由于提供的压缩包只有一个名为 "SQL2022-SSEI-Dev.exe" 的文件，这很可能是安装程序或工具包，用于在开发环境中设置 SQL Server 2022 的 SSEI 特性。 SQL Server 2022 是 Microsoft 提供的企业级数据库管理系统，...
【收藏】数据分析必备SQL技能！
菜鸟学Python数据分析
05-18 289
如果真的要说程序员普遍常用的语言，那应该就是 SQL 了。作为一门和数据库打交道的语言，无论是前端、后端，还是运维、web 开发，多少都需要写一些 SQL，尤其是随着互联网业务数据量的暴...
数分面试题-SQL常见面试题型1
最新发布
Sun123234的博客
07-25 1156
数分面试题-SQL常见面试题型1
数据分析师基本技能——SQL
古月哲亭
05-17 7584
我们做数据分析工作时，多数数据来源于数据库，SQL非常方便我们访问和查询数据库。SQL 作为数据分析师的基本技能，那么需要掌握哪些SQL核心技能理解数据库SQL基础重点知识：查询，更新，提取，插入，删除等数据操作 典型实例sql1.理解数据库数据库基本定义：数据库（Database）基本含义为：存储数据的地方，确切地来说是一张张表格，每张表通过一定联系链接在一起，最后组成了数据库可以简单的理解为：...
数分-工具-Pandas2-pandas基础
yxyibb
08-24 387
(AI算法系列)
数学分析常用的4款软件对比！
热门推荐
dashenghuahua的博客
12-19 2万+
从事数据分析都知道软件是必不可少的，目前比较流行和著名的数学软件主要有四个，分别是Maple、MATLAB、MathCAD和Mathematica。但它们都有各自的优缺点。下面我们大圣众包跟大家分享一篇来自化学数据联盟关于数学软件的对比文章。 　　Maple V 系统 　　Maple V是由Waterloo大学开发的数学系统软件，它不但具有精确的数值处理功能，而且具有无以伦比的
SQL-Front 5.3
04-07
《SQL-Front 5.3：轻量级SQL客户端工具深度解析》 SQL-Front 5.3是一款专为数据库管理设计的轻量级客户端工具，尤其适用于那些需要高效、简洁且易于使用的SQL接口的用户。它支持多种数据库管理系统，包括但不限于...
x-pack-sql-jdbc-7.9.1.jar
03-04
x-pack-sql-jdbc-7.9.1.jar
sql-assistant 12.0.191
12-09
SoftTree SQL Assistant破解版是数据库开发人员和 DBA 的终极数据库工具。为数据库开发人员和 DBA 提供了加快数据库开发过程、提高代码质量和准确性所需的工具。SQL 助手将使您的数据库开发效率提高 300% 甚至更多。...
wjz-sqlFormat-1.4
02-22
DBeaver工具很强大，但在sql格式化方面不尽人意，所以利用它的扩展功能开发出来的一个功能。说明文档: https://blog.csdn.net/wangjz2008/article/details/114082969 1、首选项中找到：sql编辑器->sql格式化 ,【格式...
数分笔记整理1 - 机器学习分析工具简介（七大库）
Sevieryang/FinTech/Statistics/Quant
01-20 872
关于执行机器学习任务的开源科学python库，我比较熟的有numpy, matplotlib, pandas, 较熟的是scikitlearn，看过相关的相关书籍，探索一下其他的科学python库。 python有一些常用于执行数据处理和机器学习的开源软件库。有很多的**科学Python库（Scientific Python Libraries）**可用于执行基本的机器学习任务，如Numpy、Pa...
数分工具库网站汇总（更新）
Sevieryang/FinTech/Statistics/Quant
03-23 205
工具库网站： python官网 https://www.python.org/ pandas官网 http://pandas.pydata.org/ numpy官网 http://www.numpy.org/ matplotlib官网 https://matplotlib.org/ geoplotlib官网和文档 https://github.com/andrea-cuttone/geoplotl...
再见了Python，Tableau数分工具确实牛逼！
小詹学python的博客
01-07 480
数据分析界有一句经典名言，字不如表，表不如图。淘宝双十一就是一个经典案例，通过可视化手段实时动态展示交易数据，加深和强化受众对于数据的记忆。在工作中，很多程序员朋友会用python做数据分...
数分-理论-大数据6-Hive
yxyibb
01-24 2761
数分-理论-大数据6-Hive（数据仓库） (数据分析系列) 文章目录数分-理论-大数据6-Hive（数据仓库）1知识点2具体内容2.1数据仓库2.1.1起源2.1.2数仓概念2.1.3体系结构2.1.4存在挑战2.2Hive基础2.2.1概述2.2.2背景2.2.3Hive与Hadoop生态系统2.2.4对比2.2.5模拟实现2.3Hive核心2.3.1数据类型2.3.2数据模型2.4Hive系统结构2.4.1用户接口模块2.4.2驱动模块Driver2.4.3元数据存储模块Metastore2.4.4H
数分-理论-大数据7-Spark
yxyibb
01-25 2755
数分-理论-大数据7-Spark（大数据框架） (数据分析系列) 文章目录数分-理论-大数据7-Spark（大数据框架）1知识点2具体内容2.1概述2.1.1起源2.1.2诞生2.1.3Spark与Hadoop、MapReduce、HDFS的关系2.1.4生态体系2.2编程模型-核心2.2.1RDD概述2.2.2RDD定义2.2.3RDD五大特性2.2.4RDD操作函数2.3架构原理2.3.1计算阶段2.3.2划分计算阶段2.3.3作业管理2.3.4执行过程2.4实战应用3参考 1知识点 概述 编程模型
数分-理论-大数据1-概述
yxyibb
01-04 1891
数分-理论-大数据1-概述 (数据分析系列) 文章目录数分-理论-大数据1-概述1知识点2具体内容2.1大数据基础2.2概念2.3应用2.4关键技术3参考 1知识点 大数据基础 概念 应用 关键技术 2具体内容 2.1大数据基础 “互联网+”信息时代，信息化数据共享 对所占有的数据进行深入分析，实现开发利用，从中发现新知识、创造新价值、提升新能力，取得实实在在的工作成效，才能够真正实现数据的价值 2.2概念 4V：数据量大（Volume）、数据类型繁多（Variety）、处理速度快（ Velocit
数分-理论-大数据4-HBase
yxyibb
01-13 1481
数分-理论-大数据4-HBase（分布式数据库） (数据分析系列) 文章目录数分-理论-大数据4-HBase（分布式数据库）1知识点2具体内容2.1Hadoop局限2.2简介2.3HBase数据模型2.3.1模型2.3.2相关概念2.3.3数据坐标2.3.4概念视图2.3.5物理视图2.3.6面向列存储2.4实现原理2.4.1HBase功能组件2.4.2表和Region2.4.3Region的定位2.5运行机制2.5.1HBase系统架构2.5.2Region服务器工作原理2.5.3Store的工作原理2.
Mysql必知必会笔记
yxyibb
03-05 1414
1 了解SQL 1.1基本概念 数据库：一个以某种有组织的方式存储的数据集合。 数据库：保存有组织的数据的容器(通常是一个文件或一组文件），不直接访问，通过DBMS创建和操纵的容器。 数据库软件：DBMS，数据库管理系统 表：某种特定类型数据的结构化清单。存储某种特定类型的数据。表可以保存顾客清单、产品目录,或者其他信息清单。 存在表中的数据是一种类型的数据或一个清单。 每个表有自己名字，标识自己。名字唯一。 模式（schema）关于数据库和表的布局及特性的信息。 列、数据类型 列（colu
数分-理论-大数据8-总结
yxyibb
01-25 1377
数分-理论-大数据8-总结 (数据分析系列) 文章目录数分-理论-大数据8-总结1知识点2具体内容2.1框架综述2.2发展简史2.2.1萌芽（2003-2008）2.2.2快速进展（2009-2014）2.2.3成熟（2015-）2.2.4大数据分析引擎2.3结语3参考 1知识点 框架综述 发展简史 结语 2具体内容 2.1框架综述 大数据框架在总体上分为存储引擎和计算分析引擎 存储引擎通常用来存储海量数据 分析引擎通常用来分析海量数据 2.2发展简史 2.2.1萌芽（2003-2008） Go
T-SQL详解：从基础到高级
"T-SQL从入门到精通" 在学习T-SQL时，了解其基本语法是至关重要的。T-SQL，全称为Transact-SQL，是Microsoft SQL Server中用于管理和处理关系数据库的标准编程语言。本资源深入讲解了T-SQL语言，旨在帮助用户提升在...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值