SQL查询大全 - 牛客SQL在线编程（2）条件/分组/连接查询

从零开始的MySQL生活

SQL查询大全 - 牛客SQL在线编程（2）

牛客练习地址:
https://www.nowcoder.com/ta/sql-quick-study

新发现：
今天在b站找到了一个很好的SQL知识合集，建议从P25附近开始食用，P28一定要看明白！敲黑板！
跳转b站 -> https://www.bilibili.com/video/BV1Vt411z7wy?p=28

参考：
SQL语句查询🔗
SQL查询语句大全🔗(个人总结)
博主小声嘟囔：其实参考的各种大全并不是很齐全，也经常不适用有时候看不明白，最好的方法还是百度

1. 找到某列为（或不为）xx1、xx2或xx3的记录：Where in 和Not in
   eg：找到学校为北大、复旦和山大的同学

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

2. 且或条件混合：WHERE (条件1 AND 条件2) OR (条件3 AND 条件4)
   eg：找到gpa在3.5以上的山东大学用户 或 gpa在3.8以上的复旦大学同学

SELECT device_id,gender,age,university,gpa 
FROM user_profile 
WHERE (university="山东大学" AND gpa>3.5) or (university="复旦大学" AND gpa>3.8)

3. 查询指定列包含xx的记录：LIKE
   SQL通配符:
   在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。
   SQL 通配符必须与 LIKE 运算符一起使用。
   在 SQL 中，可使用以下通配符：

通配符	描述
%	代表零个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	不在字符列中的任何单一字符

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

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

4. 寻找最大值：MAX(列名)
   eg：复旦大学学生gpa最高值

SELECT MAX(gpa)
FROM user_profile
WHERE university="复旦大学"

5. 统计总数：COUNT(列名)
   平均值：AVG(列名)
   保留x位小数：ROUND(数据, x)
   eg：计算男生人数以及平均GPA

SELECT COUNT(*) male_num,ROUND(AVG(gpa),1) avg_gpa 
FROM user_profile
WHERE gender="male"

6. 分组计算：GRUOP BY 分组条件1,分组条件2
   重点： 找出分组的条件，用逗号分隔
   eg：对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校 每种性别的用户数、30天内平均活跃天数和平均发帖数量
   题设分析：重点看到“每个学校 ”“每种性别”，则查询结果包括不同学校的不同性别，也就是一个学校还要按性别再分组

SELECT gender,university,count(*) user_num,
AVG(active_days_within_30) avg_active_days,
AVG(question_cnt) avg_question_cnt
FROM user_profile
GROUP BY university,gender

7. 对分组之后的结果做条件查询：HAVING √ （ 区别于WHERE ×
   SQL中where与having的区别：
   where:
   where是一个约束声明,使用where来约束来自数据库的数据;
   where是在结果返回之前起作用的;
   where中不能使用聚合函数。
   having:
   having是一个过滤声明;
   在查询返回结果集以后，对查询结果进行的过滤操作;
   在having中可以使用聚合函数。
   where和having的执行顺序：
   where 早于 group by 早于 having
   where子句在聚合前先筛选记录，也就是说作用在group by 子句和having子句前，而 having子句在聚合后对组记录进行筛选

   eg：取出平均发贴数低于5的学校或平均回帖数小于20的学校

SELECT university,AVG(question_cnt) avg_question_cnt,AVG(answer_cnt) avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING avg_question_cnt<5 OR avg_answer_cnt<20
//或：having avg(question_cnt)<5 or avg(answer_cnt)<20 可以用别名也可以用原聚合函数句

8. 子查询：
   eg：查看所有来自浙江大学的用户题目回答明细情况
   先查找来自浙江大学的用户的id名单，再到回答明细表中，查找用户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

9. 内连接查询:
   inner join / join内连查询
   SELECT * FROM 表1 INNER JOIN 表2 ON 表1.x列=表2.x列
   两张表中的数据，通过某个字段相等(该字段不相等的记录不会显示)，查询出相关数据记录

SELECT * FROM person INNER JOIN card ON person.cardid=card.id

如图, person表内连接card表, 卡号为6的记录没有出现, 因为在card表中不存在卡号为6的数据

jion的结果和inner join结果一致

10. 左外连接查询:
    left join
    SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.x列=表2.x列
    左外连接查询, 会把左边表里的全部数据都取出来, 而右边的数据, 有匹配的就显示出来,如果没有则补null

SELECT * FROM person LEFT JOIN card ON person.cardid=card.id

person表左连接card表, 看到左边的person表中的数据全都选出来了, 右边没有在card表中被匹配到的数据显示为空

11. 右外连接查询
    right join
    SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.x列=表2.x列
    右外连接查询, 与左连接相反, 会把右边表里的全部数据都取出来, 而左边表的数据, 有匹配的就显示出来,如果没有则补null

SELECT * FROM person RIGHT JOIN card ON person.cardid=card.id

person表右连接card表, 看到右边的card表中的数据全都选出来了, 左边没有在person表中被匹配到的数据显示为空

继续练习继续更新