首页  思维笔记  详情

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

查看不同年龄段的用户明细

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;

文本函数

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

六、综合练习

自由主题