- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

【第4天】SQL快速入门-多表查询（SQL 小虚竹)

小虚竹发表于 2022/08/30 22:55:49 2022/08/30

【摘要】回城传送–》《32天SQL筑基》文章目录零、前言一、练习题目二、SQL思路子查询：SQL21 浙江大学用户题目回答情况（inner join）初始化数据解法链接查询：SQL22 统计每...

回城传送–》《32天SQL筑基》

文章目录

零、前言
一、练习题目
二、SQL思路

零、前言

今天是学习 SQL 打卡的第 4 天，每天我会提供一篇文章供群成员阅读（ 不需要订阅付钱 ）。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区中对应的【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：多表查询

一、练习题目

题目链接	难度
子查询：SQL21 浙江大学用户题目回答情况	★★★☆☆
链接查询：SQL22 统计每个学校的答过题的用户的平均答题数	★★★☆☆
链接查询：SQL23 统计每个学校各难度的用户平均刷题数	★★★☆☆
链接查询：SQL24 统计每个用户的平均刷题数	★★★☆☆
组合查询：SQL25 查找山东大学或者性别为男生的信息	★★★☆☆

二、SQL思路

子查询：SQL21 浙江大学用户题目回答情况（inner join）

初始化数据

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,118,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,114,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');

  
 
  
 
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37

解法

要求统计：

查看所有来自浙江大学的用户
指定用户题目回答明细情况

分析：

user_profile表取university 字段值是浙江大学的数据

SELECT
up.device_id
from user_profile up
where
up.university = ‘浙江大学’

用户表和用户题目回答明细表要关联，可用关键词：inner join

inner join(等值连接) 只返回两个表中联结字段相等的行
本题就是user_profile表中的device_id 字段跟question_practice_detail 表中的device_id进行关联

SELECT
  up.device_id,
  qpd.question_id,
  qpd.result
from
  user_profile up
  inner join question_practice_detail qpd
where
  up.university = '浙江大学'
  and qpd.device_id = up.device_id
order by
  qpd.question_id asc

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

链接查询：SQL22 统计每个学校的答过题的用户的平均答题数

初始化数据

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int
);
CREATE TABLE `question_practice_detail` (
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(2138,'male',21,'北京大学',3.4,7);
INSERT INTO user_profile VALUES(3214,'male',null,'复旦大学',4.0,15);
INSERT INTO user_profile VALUES(6543,'female',20,'北京大学',3.2,12);
INSERT INTO user_profile VALUES(2315,'female',23,'浙江大学',3.6,5);
INSERT INTO user_profile VALUES(5432,'male',25,'山东大学',3.8,20);
INSERT INTO user_profile VALUES(2131,'male',28,'山东大学',3.3,15);
INSERT INTO user_profile VALUES(4321,'male',28,'复旦大学',3.6,9);
INSERT INTO question_practice_detail VALUES(2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(6543,111,'right');
INSERT INTO question_practice_detail VALUES(2315,115,'right');
INSERT INTO question_practice_detail VALUES(2315,116,'right');
INSERT INTO question_practice_detail VALUES(2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(5432,118,'wrong');
INSERT INTO question_practice_detail VALUES(5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(2131,114,'right');
INSERT INTO question_practice_detail VALUES(5432,113,'wrong');

  
 
  
 
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34

解法

要求统计：

查找每个学校用户的平均答题数目
某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数
结果保留4位小数
结果按照university升序排序

分析：

查找每个学校用户，这里就是要对user_profile 表中的university字段进行分组排序,使用关键词： group by

select
u.university
from
user_profile u
group by
u.university

求某学校用户平均答题数量：计算方式为该学校用户答题总次数除以答过题的不同用户个数
这里要对user_profile 表和question_practice_detail 进行链接，关键词：inner join on
链接字段为：device_id

select
u.university,
count(q.question_id) / count(DISTINCT u.device_id) as avg_answer_cnt
from
user_profile u inner join question_practice_detail q on u.device_id = q.device_id
group by
u.university

结果保留4位小数:要对avg_answer_cnt值进行四舍五入，使用关键词：round(字段/表达式,4)

select
u.university,
round(count(q.question_id) / count(DISTINCT u.device_id),4) as avg_answer_cnt
from
user_profile u inner join question_practice_detail q on u.device_id = q.device_id
group by
u.university

结果按照university升序排序,使用关键词：order by

select
  u.university,
  round(count(q.question_id) / count(DISTINCT u.device_id),4) as avg_answer_cnt
from
  user_profile  u
  inner join question_practice_detail  q on u.device_id = q.device_id
group by
  u.university
order by
  u.university

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

链接查询：SQL23 统计每个学校各难度的用户平均刷题数

初始化数据

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

  
 
  
 
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51
  52
  53
  54
  55
  56
  57
  58
  59
  60
  61
  62

解法

要求统计：

计算不同学校、不同难度的用户平均答题量
结果在小数点位数保留4位，4位之后四舍五入

分析：

计算不同学校、不同难度的用户平均答题量:结果列有三列：学校，难度，用户平均答题量
核心是求平均答题量，所以主表是question_practice_detail 。学校，难度可通过链接去关联其它表。

SELECT
u.university,
qd.difficult_level,
avg_answer_cnt
FROM
question_practice_detail qp
left join user_profile u on qp.device_id = u.device_id
left join question_detail qd on qp.question_id = qd.question_id

且要对学校，难度，进行分组

SELECT
u.university,
qd.difficult_level,
avg_answer_cnt
FROM
question_practice_detail qp
left join user_profile u on qp.device_id = u.device_id
left join question_detail qd on qp.question_id = qd.question_id
group by
u.university,
qd.difficult_level

用户平均答题量计算规则：总的题目数/用户数，取均值

count(qp.question_id) / count(distinct qp.device_id) as avg_answer_cnt

均值结果在小数点位数保留4位，4位之后四舍五入，使用关键词：round(字段/表达式,4)

SELECT
  u.university,
  qd.difficult_level,
  round(count(qp.question_id) / count(distinct qp.device_id),4) as avg_answer_cnt
FROM
  question_practice_detail qp
  left join user_profile u on qp.device_id = u.device_id
  left join question_detail qd on qp.question_id = qd.question_id
group by
  u.university,
  qd.difficult_level

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

链接查询：SQL24 统计每个用户的平均刷题数(inner join)

初始化数据

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

  
 
  
 
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51
  52
  53
  54
  55
  56
  57
  58
  59
  60
  61

解法

要求统计：

山东大学、不同难度的用户平均答题量
结果在小数点位数保留4位，4位之后四舍五入

分析：

先找山东大学的用户

select
university
from user_profile
where university = ‘山东大学’

不同难度的用户平均答题量
先对难度进行分组

select
university,
difficult_level,
from user_profile u
inner join question_practice_detail qpd on u.device_id = qpd.device_id
inner join question_detail qd on qpd.question_id = qd.question_id
where university = ‘山东大学’
group by
difficult_level

求用户平均答题量：用户答题题目数/设备Id

count(qpd.question_id) / count(DISTINCT qpd.device_id)

结果在小数点位数保留4位，4位之后四舍五入：用关键词：round(字段/表达式,4)

select
  university,
  difficult_level,
  round(count(qpd.question_id) / count(DISTINCT qpd.device_id),4) as avg_answer_cnt
from
  user_profile  u
  inner join question_practice_detail  qpd on u.device_id = qpd.device_id
  inner join question_detail  qd on qpd.question_id = qd.question_id
where
  university = '山东大学'
group by
  difficult_level

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

组合查询：SQL25 查找山东大学或者性别为男生的信息(union all)

初始化数据

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

  
 
  
 
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51
  52
  53
  54
  55
  56
  57
  58
  59
  60
  61

解法

要求统计：

查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据

分析：

查看学校为山东大学的device_id、gender、age和gpa数据

SELECT device_id,gender,age,gpa
from user_profile
where university=‘山东大学’

性别为男性的用户的device_id、gender、age和gpa数据

SELECT device_id,gender,age,gpa
from user_profile
where gender=‘male’

符合这两个查询的数据都输出，用关键词：union all

SELECT device_id,gender,age,gpa
from user_profile
where university='山东大学'
union all
SELECT device_id,gender,age,gpa
from user_profile
where gender='male'

  
 
  1
  2
  3
  4
  5
  6
  7

文章来源: xiaoxuzhu.blog.csdn.net，作者：小虚竹，版权归原作者所有，如需转载，请联系作者。

原文链接：xiaoxuzhu.blog.csdn.net/article/details/126046379

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

【第4天】SQL快速入门-多表查询（SQL 小虚竹)

文章目录

零、前言

一、练习题目

二、SQL思路

子查询：SQL21 浙江大学用户题目回答情况（inner join）

初始化数据

解法

链接查询：SQL22 统计每个学校的答过题的用户的平均答题数

初始化数据

解法

链接查询：SQL23 统计每个学校各难度的用户平均刷题数

初始化数据

解法

链接查询：SQL24 统计每个用户的平均刷题数(inner join)

初始化数据

解法

组合查询：SQL25 查找山东大学或者性别为男生的信息(union all)

初始化数据

解法

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品