SQL入门语法

1.查询所有列

SELECT * FROM [表名]

2.查询多列

SELECT [列名一]，[列名二]，[列名三]... FROM [表名]

3.查询结果去重

SELECT DISTINCT [列名] FROM [表名]

4.查询结果限制返回行数

SELECT [列名] FROM  [表名] LIMIT 2  //输出前2行
SELECT [列名] FROM  [表名] LIMIT 5，5    //输出6-10行
SELECT [列名] FROM  [表名] LIMIT 10，-1  //输出11-last行

5.将查询后的列重新命名

SELECT device_id AS user_infos_example FROM user_profile LIMIT 2

6.查找后排序（按照年龄升序排列），升序—ASC；降序—DESC

SELECT device_id,age FROM user_profile order by age ASC 

7.查找后多列排序，先按照gpa升序，在按照age升序

SELECT device_id,gpa,age FROM user_profile order by gpa ASC,age ASC

8.查找后多列排序，先按照gpa降序，在按照age降序

SELECT device_id,gpa,age FROM user_profile order by gpa DESC,age DESC

9.查找学校是北大的学生信息

SELECT device_id,university FROM user_profile WHERE university='北京大学'

10.查找某个年龄段的用户信息

SELECT device_id,gender,age FROM user_profile WHERE age>='20' and age <='23'

11.用where过滤空值练习

SELECT device_id,gender,age,university FROM user_profile WHERE age is not null

12.Where in 和 Not in（找三所大学学生的信息）

SELECT device_id,gender,age,university,gpa 
FROM user_profile 
WHERE university IN ('北京大学','山东大学','复旦大学') 

13.操作符混用（注意，and的优先级高于or！！！）

SELECT device_id,gender,age,university,gpa 
FROM user_profile 
WHERE gpa>'3.5' AND university='山东大学' OR gpa>'3.8' AND university='复旦大学'

14.查看学校名字中包含北京的用户

SELECT device_id,age,university
FROM user_profile 
WHERE university LIKE '北京%'

_：匹配任意一个字符； LIKE '北京__'  ，查询表中北京相关的，四个字的详细信息。
%：匹配0或多个字符；  LIKE '北京%'  ，查询表中北京相关的详细信息。
[]：匹配[]中的任意一个字符（若需要比较的字符是连续的，可以用连字符“-”表示）；LIKE '[北京]%'。
[ ^ ]：不匹配[ ]中的任意一个字符； LIKE '%[ ^235]' ，查询最后一位不是2、3、5的学生信息。

15.查找GPA最高值

方法一：筛选出复旦大学的所有gpa，按照gpa降序排列，用limit限制输出第一位，也就是最高的gpa。

select gpa 
from user_profile
where university='复旦大学'
order by gpa desc limit 1

方法二：使用max(gpa)

select max(gpa) gpa
from user_profile
where university='复旦大学'

16.计算男生人数以及平均GPA

select 
count(gender) as male_num,
round(avg(gpa),1) as avg_gpa
from user_profile 
where gender='male'

17.分组计算

题目：想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，分别计算出每个学校每种性别的用户数，30天内平均活跃人数和平均发帖的数量。

select
    gender,university,
    count(device_id) as user_num,
    avg(active_days_within_30) as avg_active_days,
    avg(question_cnt) as avg_question_cnt
from user_profile
group by gender,university

18.分组过滤

题目：想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

基本思想：
（1）平均发帖数低于5或平均回帖数小于20，聚合函数结果作为筛选条件时，不能用where，要用having。
（2）按学校输出，需要对每个学校统计其平均发帖数和平均回帖数。

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'

19.浙江大学用户题目回答情况

题目：想要查看浙江大学的用户题目回答明细情况，回答详情的表question_practice_detail，用户信息表user_profile，最后根据查询结果的question_id升序排列

方法一：

select
    device_id,question_id,result
from question_practice_detail
where device_id in(
    select device_id from user_profile
    where university ='浙江大学'
)
order by question_id

方法二：学校信息在user_profile，回答信息在question_practice_detail，需要通过device_id关联两个表的数据，使用join的inner join条件。联合的条件是on up.device_id=qpd.device_id and up.university=‘浙江大学’。

select qpd.device_id,qpd.question_id,qpd.result
from question_practice_detail as qpd
inner join user_profile as up
on up.device_id=qpd.device_id and up.university='浙江大学'
order by question_id

20.查找山东大学或者性别为男生的学生信息（注意，不去重要用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'

21.计算25岁以上和以下的用户数量

CASE函数：可以根据条件列表的值返回多个可能的结果表达式中的一个，一种多分支的函数，可以在任何允许使用表达式的地方，但是不能单独作为一个语句执行。

简单case函数：

CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

如果所有简单表达式的值与测试表达式的值都不相等，若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；若没有指定ELSE子句，则返回NULL。

搜索CASE函数：

CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2 …
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

所以本题的解题方法是：分别定义25岁以下和25以上，注意null代表25岁以下。

select case when age<25 or age is null then '25岁以下'
            when age>=25 then '25岁及以上'
            end age_cut,count(*)number
from user_profile
group by age_cut

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

题目：将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段的用户明细，若年龄为空返回其他。

select device_id,gender,
    case when age<20 then '20岁以下'
            when age>=20 and age<=24 then '20-24岁'
            when age>=25 then '25岁及以上'
            else '其他'
            end as age_cut
from user_profile

23.计算用户8月每天的练题数量

时间限制条件：where month(date)=8 and year(date)=2021，因为要调查每天的连体数量，所以按照天分组（group by day！！！）。

select 
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where month(date)=8 and year(date)=2021
group by day

24.统计每种性别的人数

分组条件：性别。因为没有gender字段，所以需要从profile字段中截取，按字符分割后取出。

substring_index（FIELD，sep，n）可以将字段FIELD按照sep分隔：
（1）当n大于0时取第n个分隔符（n从1开始）左边的全部内容；
（2）当n小于0时，取倒数第n个分隔符（n从-1开始）右边的全部内容取出；

select 
    substring_index(profile,',',-1) as gender,
    count(device_id) as number
from user_submit
group by gender

25.截取出年龄

题目：要求统计每个年龄的用户分别有多少参赛者。可以发现没有直接的age字段，需要从profile中截取，调用两次 substring_index。

select
    substring_index(substring_index(profile,',',3),',',-1) as age,
    count(device_id) as number
from user_submit
group by age

提出博客URL中的用户名（注意就是分割符号不一样，方法一样）！！

select
    device_id,
    substring_index(blog_url,'/',-1) as user_name
from user_submit

26.浙大不同难度题目的正确率

题目：要求输出浙江大学的用户在不同难度题目下答题的正确率情况，按照准确率升序输出

背景知识：IF语句。

SELECT IF(TRUE,1+1,1+2);
-> 2
SELECT IF(FALSE,1+1,1+2);
-> 3
SELECT IF(STRCMP("111","222"),"不相等","相等");
-> 不相等

解题思路：正确率的设定是结果为正确时才计数，这里结果正确计数为1，结果错误计数为0，计算平均就是正确率。需要按照difficult_level分组，correct_rate升序。

select
    difficult_level,
    avg(if(qpd.result='right',1,0)) as correct_rate
from question_practice_detail as qpd

inner join user_profile as up
on up.device_id=qpd.device_id

inner join question_detail as qd
on qd.question_id = qpd.question_id

where university='浙江大学'
group by difficult_level
order by correct_rate

27.统计复旦用户8月练题情况

题目：想要了解复旦大学大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，对于在8月份没有联系过的用户，答题数返回0。

背景知识：sum和count在SQL中的区别。
（1）sum用于求和，列值为空时不计算，返回结果为null。
（2）count用于行数的统计，为空时认为没有这一行，返回结果为0。

解题细节：
（1）需要输出每个用户的统计结果，所以需要group by up.device_id
（2）正确的题目数， sum(if(qpd.result=‘right’,1,0))
（3）八月份没有答题的用户返回0

select
    up.device_id,
    '复旦大学' as university,
    count(question_id) as quesrion_cnt,
    sum(if(qpd.result='right',1,0)) as right_question_cnt
from user_profile as up
left join question_practice_detail as qpd
on up.device_id=qpd.device_id and month(qpd.date)=8
where up.university='复旦大学' 
group by up.device_id