首页 思维笔记 详情

SQL

2024-07-10 11:23:46 2 举报
SQL
分析
作者其他创作
大纲/内容
一、基础查询
新建数据表
题目：现在运营想要查看用户信息表中所有的数据，请你取出相应结果
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,
`province` varchar(32)  NOT NULL);
插入数据
插入数据
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3，6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4，2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
INSERT INTO user_profile VALUES(6,null,'male',22,'重庆大学','ChongqQing');
查询数据
要求：查询 user_profile 表中所有数据，展示字段【全部】【2种方式】
select * from user_profile;
练习：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据
select device_id,gender,age,university from user_profile；
查询结果处理
查询结果去重
题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。
select university from user_profile
查询结果限制返回行数
题目：现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。
select device_id from user_profile limit 2
将查询后的列重新命名
题目：现在你需要查看前2个用户明细设备ID数据，并将列名改为 'user_infos_example',，请你从用户信息表取出相应结果。
select device_id as user_infos_example from user_profile limit 2
二、条件查询
基础排序
查找后排序
题目：现在运营想要取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序。
select device_id,age from user_profile order by age asc
查找后多列排序
题目：现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。
select device_id,gpa,age from user_profile order by gpa asc,age asc
查找后降序排列
题目：现在运营想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据。
select device_id,gpa,age from user_profile order by gpa desc,age desc
基础操作符
查找学校是北大的学生信息
题目：现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。
查找年龄大于24岁的用户
题目：现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。
查找某个年龄段的用户信息
题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄。
查找除复旦大学的用户信息
题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据
高级操作符
数据筛选
题目：现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据。
题目：现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据（使用OR实现）
Where in 和Not in
题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。
操作符混合运用
题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据
查看学校名称中含北京的用户
题目：现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。
三、高级查询
计算函数
查找GPA最高值
题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据
select max(gpa) as gpa from user_profile where university='复旦大学'
计算男生人数以及平均GPA
题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。
select count(*) as male_num,avg(gpa) as avg_gpa from user_profile where gender='male'
分组查询
【新增表数据】user_profile
用户信息表：user_profile
30天内活跃天数字段（active_days_within_30）
发帖数量字段（question_cnt）
回答数量字段（answer_cnt）
drop table if exists user_profile;
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` float,
`question_cnt` float,
`answer_cnt` float
);
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);
分组计算
题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。
SELECT
    gender,
    university,
    count(gender) as user_num,
    avg(active_days_within_30) as avg_active_days,
    avg(question_cnt) as avg_question_cnt
from
    user_profile
GROUP by
    university,
    gender;
分组过滤
题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
SELECT
    university,
    avg(question_cnt) AS avg_question_cnt,
    avg(answer_cnt) AS avg_answer_cnt
FROM
    user_profile
GROUP BY
    university
HAVING
    avg_question_cnt < 5
    OR avg_answer_cnt < 20
注意：
WHERE 关键字无法与合计函数一起使用；where 从记录中法过滤出某一条记录
having 可以从一组组记录中过滤掉其哪几组,having关键字放在group by关键字后面，针对分组后的数据进行筛选
分组排序
题目：现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。
方法一：需要单独命名avg_question
select
    university,
    avg(question_cnt) AS avg_question_cnt
from
    user_profile
group by
    university
order by
    avg_question_cnt
方法二：不需要单独命名
select university,avg(question_cnt) from user_profile
group by university
order by avg(question_cnt)
这样操作能运行成功的原因是 order by后面可以加聚合函数。但要注意，group by后面不可
有三类后面可以加聚合函数
1、select
2、order by
3、having
四、多表查询
子查询
浙江大学用户题目回答情况
【新增表数据】question_practice_detail
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 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');
题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据
链接查询
统计每个学校的答过题的用户的平均答题数
运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。
SELECT    -- 查询
    university,  -- 大学列名
    COUNT(question_id)/count(distinct qpd.device_id) AS avg_answer_cnt
    -- 题目编号总个数（题目数量） / 去掉重复之后的用户编号 总个数（用户数量）  使用别名
FROM
    question_practice_detail AS qpd  -- 来源表名 别名
INNER JOIN user_profile AS up    -- 连接第二张表  别名
ON qpd.device_id=up.device_id   -- 连接条件 第一表和第二表的用户ID相等
GROUP BY
    university  -- 按大学名 分组
#这里用inner不用left join 原因在于，在表a中，还存在北京理工学生情况，但北京理工学生并没有参与答题，left join 仍然会保留北京理工数据，但inner join 只会保留两表交集部分
用户信息表 user_profile，其中device_id指终端编号（认为每个用户有唯一的一个终端），gender指性别，age指年龄，university指用户所在的学校，gpa是该用户平均学分绩点，active_days_within_30是30天内的活跃天数。
【新增表数据】
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');
统计每个学校各难度的用户平均刷题数
【新增表数据】
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');
题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据
统计每个用户的平均刷题数
【新增表数据】
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');
题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据
SELECT
    up.university,
    qd.difficult_level,
    COUNT(qpd.question_id) / COUNT(DISTINCT   qpd.device_id) AS avg_answer_cnt
FROM
    question_practice_detail AS qpd
LEFT JOIN
    user_profile AS up
ON qpd.device_id=up.device_id

LEFT JOIN
    question_detail AS qd
ON  qd.question_id=qpd.question_id
WHERE
    up.university='山东大学'
GROUP BY
    qd.difficult_level;
组合查询
查找山东大学或者性别为男生的信息
题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。
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';
五、常用函数
条件函数
case when
查看不同年龄段的用户明细
题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）例：2138|male|20-24岁
3214|male|其他
6543|female|20-24岁
2315|female|20-24岁
5432|male|25岁及以上
2131|male|25岁及以上
4321|male|25岁及以上
select device_id,gender,
case
    when age<20 then '20岁以下'
    when age<25 then '20-24岁'
    when age>=25 then '25岁及以上'
    else '其他'
end age_cut
from user_profile;
文本函数
like
SUBSTRING(str  ,n ,m)：返回字符串str从第n个字符截取到第m个字符；
REPLACE(str, n, m)：将字符串str中的n字符替换成m字符
提取博客URL中的用户名
题目：对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

示例：user_submit
device_id
profile
blog_url
2138180cm,75kg,27,male
http:/ur/bisdgboy777
3214165cm,45kg,26,female
http:/url/dkittycc
6543178cm,65kg,25,male
http:/ur/tigaer
4321171 cm,55kg,23,female
http:/url/uhksd
2131168cm,45kg,22,female
http:/url/sydney

根据示例，你的查询应返回以下结果：
device_id
user_name
2138bisdgboy777
3214dkittycc
6543tigaer
4321uhsksd
2131sydney
LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；
LEFT(str, length)：从左边开始截取str，length是截取的长度；
RIGHT(str, length)：从右边开始截取str，length是截取的长度；
SUBSTRING_INDEX(str  ,substr  ,n)：返回字符substr在str中第n次出现位置之前的字符串;
LENGTH(str)：计算字符串str的长度。
窗口函数
找出每个学校GPA最低的同学
题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。
题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

示例：user_profile
iddevice_idgenderageuniversitygpaactive_days_within_30
question_cnt
answer_cnt
12138male21北京大学3.47212
23214male复旦大学415525
36543female20北京大学3.212330
42315female23浙江大学3.6512
55432male25山东大学3.8201570
62131male28山东大学3.315713
74321female26复旦大学3.69652
根据示例，你的查询结果应参考以下格式，输出结果按university升序排序：
device_id
university
gpa
6543
北京大学
3.2000
4321复旦大学3.6000
2131山东大学
3.3000
2315浙江大学
3.6000
Select
    device_id,
    university,
    gpa
From
    (
        Select
            device_id,
            university,
            gpa,
            row_number() over (
                partition by
                    university
                order by
                    gpa
            ) as rk
        From
            user_profile
    ) a
where
    rk = 1
【新增表数据】user_profile
drop table if exists user_profile;
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
);
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);
六、综合练习
自由主题
评论
0 条评论
下一页
为你推荐
查看更多
mybatis-动态sql解析
mybatis-动态sql解析
Spark-SQL@图解
Spark-SQL@图解
sql技能学习与项目实战
sql技能学习与项目实战
PL/SQL优点
PL/SQL优点
邮件表SQL
邮件表SQL
sql_allneeds
sql_allneeds
sql_user
sql_user
sql parse
sql parse
电子商城购物系统的设计和实现(ASP.NET,SQL)
电子商城购物系统的设计和实现(ASP.NET,SQL)
sql_manage
sql_manage