开发基础----牛客SQL速成(持续)

一.单表查询(非技术快速入门)

1.基础查询

SQL1 查询所有列

SELECT id, device_id, gender, age, university, province FROM user_profile;

使用SELECT +(所有列名) from 表名 然后结尾 ; 这样读取效率最高 SELECT* from 表名 ; 只适合自己看一眼，实际开发中效率低（因为需要将* 转化为每一个列名）

SQL2 查询多列

SELECT device_id, gender, age, university from user_profile

SQL3 查询结果去重

select distinct university from user_profile;

SQL4 查询结果限制返回行数

在 SQL 中，LIMIT 是用于限制查询结果返回的行数的关键字。
LIMIT 0,2 表示从结果集的第 0 行开始，返回 2 行数据。这样的语法可以用于分页查询，例如获取第一页的数据。
LIMIT 2 表示返回结果集的前 2 行数据，不指定起始位置，默认从第 0 行开始。这种语法可以用于获取前几行数据，而不需要指定具体的起始位置。
简而言之，它们的区别在于：
LIMIT 0,2 指定了起始位置和返回的行数，可以用于分页查询。
LIMIT 2 只指定了返回的行数，从第 0 行开始，默认从第 0 行开始。
注意，在一些数据库系统中，也可以使用 OFFSET 关键字来指定起始位置，例如 LIMIT 2 OFFSET 0 和 LIMIT 2 是等价的。
不用打;也行

select device_id from user_profile limit 2

SQL5 将查询后的列重新命名

select device_id as user_infos_example from user_profile limit 2 offset 0

SQL6 查找学校是北大的学生信息

select device_id, university from user_profile where university = '北京大学'

SQL7 查找年龄大于24岁的用户信息

select device_id, gender, age, university from user_profile where age > 24

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

select device_id, gender, age from user_profile where age between 20 and 23
select device_id, gender, age from user_profile where age >= 20 and age <= 23

SQL9 查找除复旦大学的用户信息

select device_id,gender,age,university from user_profile where university !='复旦大学';
SELECT device_id,gender,age,university from user_profile where university <> '复旦大学';
SELECT device_id,gender,age,university from user_profile where university not in ('复旦大学');
## 模糊查询
SELECT device_id,gender,age,university from user_profile where university not like '%复旦%';

SQL10 用where过滤空值练习

select device_id, gender, age, university from user_profile where age is not null
select device_id, gender, age, university from user_profile where age != ''

SQL11找到男性且GPA在3.5以上(不包括3.5)的用户

select device_id, gender, age, university, gpa from user_profile where gpa > 3.5 AND gender = 'male'

SQL12 找到学校为北大或GPA在3.7以上(不包括3.7)的用户

select device_id, gender, age, university, gpa from user_profile where university = '北京大学' or gpa > 3.7

SQL13 找到学校为北大、复旦和山大的同学

select device_id, gender, age, university, gpa from user_profile where university in ('北京大学','复旦大学','山东大学');

SQL14 找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学

--时间长
select device_id,gender,age,university,gpa from user_profile where university='山东大学' and gpa>3.5 or university='复旦大学' and gpa>3.8
-- 子查询,麻烦, 但时间短
select device_id,gender, age,university,gpa from user_profile 
where device_id in (select device_id from user_profile where university = '山东大学' and gpa > 3.5) 
or device_id in (select device_id from user_profile where university = '复旦大学' and gpa > 3.8)

SQL15 查看学校名称中含北京的用户

select device_id,age,university from user_profile where university like '%北京%'

SQL16 复旦大学学生gpa最高值

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

SQL17 男性用户有多少人以及他们的平均gpa是多少

1. 表头重命名，用as语法
2. 浮点数的平均值可能小数点位数很多，按照示例保存一位小数，用round函数

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

SQL18 分组计算练习题

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

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

SQL19 分组过滤练习题

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

select不同字段记得加,
分组查询过滤条件用having 不用 where

select 
    university, 
    round(avg(question_cnt),3) as avg_question_cnt,
    round(avg(answer_cnt),3) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt < 5 or avg_answer_cnt < 20

SQL20 分组排序练习题

不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列

select 
    university,
    round(avg(question_cnt), 4) as avg_question_cnt 
from user_profile
group by university
order by avg_question_cnt asc

二.多表查询

(说在前面)复杂SQL的三段式结构

本节进入多表查询阶段, 多表查询的SQL通常为大SQL, 又臭又长,因此我们需要一定的套路将其简化, 三段式结构就是一种很方便的简化思路

以SQL23 统计每个学校各难度的用户平均刷题数为例

# 第一段：select选择字段，含原生字段及构造字段，
#其中构造的过程中常用sum count round +-*/ 等函数及运算表达式
select
    university,
    difficult_level,
    round(count(qpd.question_id) / count(distinct qpd.device_id),4) as avg_answer_cnt

# 第二段：from选择表格，含合并表格操作
from question_practice_detail as qpd
    join user_profile as up
        on up.device_id=qpd.device_id
    join question_detail as qd
        on qd.question_id=qpd.question_id

# 第三段：数据操作段，含 分组/排序/筛选/取指定行数等操作
group by university,difficult_level;

多表查询方法一: 左连接 内连接 右连接 左外连接 右外连接

注意:

• 单独一个join是inner join 内连接
• left join:左连接 但是好像左连接就是 左外连接, 即left join 也可以写作left outer join, 右连接同理

• 技术上:
  在这里插入图片描述
• 业务上:
  业务上多表查询的时候选择哪种取决于你想要保留什么:
  • 想要左表中的所有数据----左连接
  • 想要右表中的所有数据----右连接
  • 想要左右两张表都存在的特殊数据----内连接
  • 只想要左边的且其所有字段都不为null----左外连接
  • 只想要右边的且其所有字段都不为null----右外连接

多表查询方法二:子查询

类似下面这种方式就是嵌套子查询

select device_id,gender, age,university,gpa from user_profile 
where device_id in (select device_id from user_profile where university = '山东大学' and gpa > 3.5) 
or device_id in (select device_id from user_profile where university = '复旦大学' and gpa > 3.8)

• 什么时候多表查询不能用子查询
  例如SQL22题中university和round(count(question_id) / count(distinct qpd.device_id), 4)这两个字段不在同一张表里, 必须靠链接两张表生成中间表,在中间表中查询;
  因此不能用子查询了,如果两个字段在同一张表的话就可以用子查询类似device_id in (select device_id from xx where ....)

非聚合字段和聚合函数同时出现,要用group by

什么是非聚合字段

• 在MySQL中，非聚合字段指的是不参与聚合函数计算的字段。我们有一个学生表，其中包含学生的姓名、年龄、性别、班级等信息，我们需要统计每个班级的学生数量，那么班级字段就是聚合字段，而姓名、年龄、性别等字段就是非聚合字段。

• 非聚合字段的作用非聚合字段在实际应用中非常重要，它们可以用于数据的筛选、排序和分组等操作。我们需要查询年龄大于18岁且性别为女性的学生信息，那么年龄和性别字段就是非聚合字段，在查询语句中需要使用WHERE子句进行筛选。

• 如何使用非聚合字段在MySQL中，使用非聚合字段的方法非常简单，只需要在SELECT语句中将需要查询的字段列出即可。我们需要查询学生表中的姓名、年龄和性别字段，那么查询语句如下：SELECT 姓名,年龄,性别 FROM 学生表;如果需要对查询结果进行排序，可以使用ORDER BY子句，例如按照年龄从小到大排序：SELECT 姓名,年龄,性别 FROM 学生表 ORDER BY 年龄 ASC;如果需要对查询结果进行分组，可以使用GROUP BY子句，例如按照班级分组统计学生数量：SELECT 班级,COUNT(*) FROM 学生表 GROUP BY 班级;

• 总结:非聚合字段在MySQL中的作用非常重要，它们可以用于数据的筛选、排序和分组等操作。在使用非聚合字段时，只需要在SELECT语句中将需要查询的字段列出即可，同时可以使用ORDER BY和GROUP BY子句对查询结果进行排序和分组。

我的理解: 聚合函数要聚合某一个字段的时候应当依据其他非聚合函数字段, 比如我要统计(一个班)多少人, 那么到底怎么统计呢,按性别统计还是按身高体重?....如果你写了group by class 那么我就知道你要按照同一个班级去统计去聚合了

非聚合字段和聚合函数同时出现要使用group by

select s.s_id,avg(s.s_score) as avgscore from score s group by s_id

只有聚合函数时，不用group by

select avg(s.s_score) as avgscore from score s

(2表))SQL21 浙江大学用户题目回答情况(含on 和 where的区别)

在多表查询时，on和where都表示筛选条件，on先执行，where后执行。
区别：
外连接时，on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。而where条件是在临时表生成好后，再对临时表进行过滤的条件。
下面这题是内连接,因此on和where的运行结果相同
链接

查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

--内连接法
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 qpd.question_id asc
--子查询法
select 
    device_id,
    question_id,
    result
from question_practice_detail 
where device_id in(
    select device_id from user_profile
    where university='浙江大学'
) 

(2表)SQL22 统计每个学校的答过题的用户的平均答题数

链接

查找每个学校用户的平均答题数目(说明：某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数)根据示例，你的查询应返回以下结果（结果保留4位小数），注意：结果按照university升序排序！！！

select 
    university, 
    round(count(question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from user_profile as up
inner join question_practice_detail as qpd
on up.device_id = qpd.device_id
group by university

本题university和round(count(question_id) / count(distinct qpd.device_id), 4)这两个字段不在同一张表里, 必须靠链接两张表生成中间表,在中间表中查询;
因此不能用子查询了,如果两个字段在同一张表的话就可以用子查询类似device_id in (select device_id from xx where ....)

(3表)SQL23 统计每个学校各难度的用户平均刷题数

注意:

• 其实三表联查，在互联网项目中，是不合适的，因为其性能很差，追求性能,可以通过前期的表设计或者其他代码逻辑维护表的数据
• 我们通过观察发现，这道题，跟前面一道相比，加入了另一张表的查询
• question_practice_detail这个表拥有其他两个表相同的数据
  通过关系连接表与表中间关系字段(up与qpd用device_id连接，qd与qpd用question_id连接)。进行两次的内连接，或者左外连接。
  链接
  写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

重命名字段或表的时候用不用as都行:
例如重命名表的时候:user_profile as up和user_profile up都行
例如重命字段的时候:university as u和university u都行

----连接三张表, 这里用left join或join(inner join)或right join都行, 因为两边每个id都有, 不存在有一行其他字段为null的情况;
select 
    university,
    difficult_level,
    round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpd
left join user_profile as up
on up.device_id=qpd.device_id
left join question_detail as qd
on qd.question_id=qpd.question_id
group by university, difficult_level
----直接暴力用where
SELECT 
    university,
    difficult_level,
    COUNT(q_p.question_id) / COUNT(DISTINCT q_p.device_id) avg_answer_cnt
FROM user_profile u,
     question_detail q,
     question_practice_detail q_p
WHERE
    u.device_id = q_p.device_id
    and q_p.question_id = q.question_id
GROUP BY
    university,difficult_level;

题目整理

1.字符匹配

一般形式为：

列名 [NOT ] LIKE
匹配串中可包含如下四种通配符：
_：匹配任意一个字符；
%：匹配0个或多个字符；
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；
[^ ]：不匹配[ ]中的任意一个字符。

例23．查询学生表中姓‘张’的学生的详细信息。

SELECT * FROM 学生表 WHERE 姓名 LIKE ‘张%’

例24．查询姓“张”且名字是3个字的学生姓名。

SELECT * FROM 学生表 WHERE 姓名 LIKE '张__’

如果把姓名列的类型改为nchar(20)，在SQL Server 2012中执行没有结果。原因是姓名列的类型是char(20)，当姓名少于20个汉字时，系统在存储这些数据时自动在后边补空格，空格作为一个字符，也参加LIKE的比较。可以用rtrim()去掉右空格。

SELECT * FROM 学生表 WHERE rtrim(姓名) LIKE '张__'

例25.查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。

SELECT * FROM 学生表 WHERE 姓名 LIKE '[张李刘]%’

例26.查询学生表表中名字的第2个字为“小”或“大”的学生的姓名和学号。

SELECT 姓名,学号 FROM 学生表 WHERE 姓名 LIKE '_[小大]%'

例27.查询学生表中所有不姓“刘”的学生。

SELECT 姓名 FROM 学生 WHERE 姓名 NOT LIKE '刘%’

例28.从学生表表中查询学号的最后一位不是2、3、5的学生信息。

SELECT * FROM 学生表 WHERE 学号 LIKE '%[^235]'