MySQL_查询

1. 单表查询

1.1 带 in / not in 关键字的查询：

语法规则： select 字段名 from 表名 where 字段名 in / not in (n1,n2,n3,…);

示例: 查询当 Id 不等于 1 时，字段 Name 和 Salary 的内容。

select Name,Salary from tb_emp where Id not in(1);

1.2 带 (not)between and 的范围查询：

语法规则：select 字段名 from 表名 where 字段名 between n1 and n2;

示例: 查询当字段 Salary 范围在 3000~5000 时，字段 Name 和 Salary 的内容。

select Name,Salary from tb_emp where Salary between 3000 and 5000;

1.3 带 like 的字符匹配查询：

**语法规则 : select 字段名 from 表名 where 字段名 like ‘字符%’; **

通配符 % 可以匹配任意长度的字符，甚至包括零字符；但 _ 字符只能匹配一个

示例: 查询所有 Name 以字母 C 为起始的员工的 Name 和 Salary 的内容。

select Name,Salary from tb_emp where Name like 'C%';

1.4 查询空值与去除重复结果：

查询空值语法规则 ： select 字段名 from 表名 where 字段名 is null;

去除重复结果语法规则 : select distinct 字段名 from 表名;

示例1 : 使用关键字 is null 返回数据表中字段 DeptId 为 NULL 的所有字段的内容，注意，返回的是指定行所有字段的内容。

select * from tb_emp where DeptId is null;

示例2 : 使用关键字 distinct 返回数据表中字段 Name 不重复的内容，注意，只需返回字段 Name 的内容。

select distinct Name from tb_emp;

1.5 带 AND 与 OR 的多条件查询：

语法规则为：SELECT 字段名 FROM 表名 WHERE 表达式1 OR / AND 表达式2;

小提示： OR 可以和 AND 一起使用，但 AND 的优先级要高于 OR 的优先级 !

示例: 使用关键字 AND 返回数据表中字段 DeptId 为 301 并且薪水大于 3000 的所有字段的内容。

select * from tb_emp where DeptId=301 and Salary>3000;

1.6 对查询结果进行排序：

语法规则为：SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC[DESC]];

示例: 查询1班同学的所有信息以成绩降序的方式显示结果。

select * from tb_score where class_id in (1) order by score desc;

1.7 分组查询:

语法规则为：SELECT 字段名 FROM 表名 GROUP BY 字段名;

示例: 对班级名称进行分组查询。

select * from tb_class group by class_id;

1.8 使用 LIMIT 限制查询结果的数量：

语法规则为：SELECT 字段名 FROM 表名 LIMIT [OFFSET,] 记录数;
    
    参数说明：
    
    第一个参数，OFFSET，可选参数，表示偏移量，如果不指定默认值为0，表示从查询结果的第一条记录开始，若偏移量为1，则从查询结果中的第二条记录开始，以此类推。
    
    第二个参数，记录数，表示返回查询结果的条数。
    
    注意 : 从第二条记录开始往后查看4条数据（不包含第二条)

示例: 查询班级中第2名到第5名的学生信息，并根据学生成绩进行降序排序。

select * from tb_score order by score desc limit 1,4;

---

2. 联接查询

2.1 内连接查询：

语法规则为：表1 [inner] join 表2 on 表1.字段=表2.字段；

语法解释 : 从表1中取出每一条记录，去表2中与所有的记录进行匹配，匹配必须是某个条件在表1中与表2中相同，最终才会保留结果，否则不保留。inner 关键字可省略不写；on 表示连接条件：大多数情况下为两张表中的主外键关系。

自然连接：natural join（去掉重复列）

示例: 查询数据表中学生姓名以及对应的班级名称，将其对应的列名分别另命名为 studentName 和 className。

select s.name studentName,c.name className from tb_student as s join tb_class as c on s.class_id=c.id;

2.2 外连接查询：

语法规则为：表1 left / right [outer] join 表2 on 表1.字段=表2.字段；

语法解释：（1）左外连接：在内连接的基础上，还包含表1中所有不符合条件的数据行，并在其中的表2列填写 NULL；
 （2）右外连接：在内连接的基础上，还包含表2中所有不符合条件的数据行，并在其中的表1列填写 NULL。

full join表示全连接

示例: 分别使用左外连接和右外连接查询数据表中所有学生姓名和对应的班级名称，查询结果列分别另命名为 studentName 和 className。

-- 左外连接
select s.name studentName,c.name className from tb_student s left join tb_class c on s.class_id=c.id;

-- 右外连接
select s.name studentName,c.name className from tb_student s right join tb_class c on s.class_id=c.id;

2.3 复合条件连接查询：

复合条件连接查询，就是在连接查询的过程中，通过添加过滤条件来限制查询结果，使查询结果更加精确。

示例: 查询所有班级里分数在 90 分以上的学生的姓名和学生的成绩以及学生所在的班级，其中学生的姓名和学生所在班级分别另命名为 studentName 和 className。

select s.name studentName,s.score,c.name className from tb_student s join tb_class c on s.class_id=c.id and s.score>=90;

---

3. 子查询

子查询指嵌套在查询内部，且必须始终出现在圆括号内，子查询可分为四类：

• 标量子查询：返回单一值的标量，最简单的形式；
• 列子查询：返回的结果集是 N 行一列；
• 行子查询：返回的结果集是一行 N 列；
• 表子查询：返回的结果集是 N 行 N 列。

3.1 带比较运算符的子查询：

使用场景：当用户能够确切的知道内层查询返回的是单个值时,可以使用比较运算符。

示例: 查询大于所有平均年龄的员工姓名与年龄。（平均年龄是一个确切值）

select name,age from tb_emp where age > (select avg(age) from tb_emp);

3.2 关键字子查询：

相关知识 ： 由于列子查询返回的结果集是 N 行一列，因此不能直接使用 = 、>、<、>=、<=、<>这些比较标量结果的操作符。在列子查询中可以使用 ALL、ANY、SOME 和 IN关键字操作符。

ALL关键字 ：ALL必须接在一个比较运算符的后面，表示与子查询返回的所有值比较都为 TRUE 则返回 TRUE。如 ：WHERE col1 > ALL (SELECT col2 FROM table2)

ANY和SOME关键字 ： ANY与比较操作符联合使用，表示与子查询返回的任何值比较为 TRUE ，则返回 TRUE。SOME 是ANY的别名，一般用的比较少。
    
    IN关键字：
   （1）IN的意思就是指定的一个值是否在这个集合中，如果在就返回TRUE；否则返回FALSE，同IN(项1，项2，…)；
   （2）IN 是 = ANY 的别名，二者相同，但 NOT IN 的别名却不是 <> ANY 而是 <> SOME。

示例：

（1）查询薪资表中比Java最高工资高的所有员工职位名称和薪资；

（2）查询薪资表中比Java最低工资高的所有员工职位名称和薪资；

（3）查询薪资表中职位为Java的所有员工职位名称和薪资。

-- 1.使用 ALL 关键字进行查询
select position,salary from tb_salary where salary > (select max(salary) from tb_salary where position='java');

select position,salary from tb_salary where salary > all(select salary from tb_salary where position='java');

-- 2.使用 ANY 关键字进行查询
select position,salary from tb_salary where salary > (select min(salary) from tb_salary where position='java');

select position,salary from tb_salary where salary > any(select salary from tb_salary where position='java');

-- 3.使用 IN 关键字进行查询
select position,salary from tb_salary where position in('java');

---

4. 复杂查询

4.1 示例: 计算每个班的语文总成绩和数学总成绩，其中低于 60 分的成绩不记入总成绩。其中 tb_score 字段：name、chinese、maths； tb_class字段 : stuname、classname。

select classname,sum(if(chinese<60,0,chinese)) as chinese, sum(if(maths<60,0,maths)) as maths
from tb_score s,tb_class c where s.name=c.stuname group by classname;

4.2 示例: 给定一张 tb_Salary 表，其中有 m = 男性 和 f = 女性的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。

update tb_Salary set sex = if(sex='f','m','f');

4.3 示例: 查询平均成绩小于 60 分的同学的学生编号（s_id）、学生姓名（s_name）和平均成绩（avg_score），要求平均成绩保留 2 位小数点。（注意：包括有成绩的和无成绩的）其中, student 表字段：s_id、s_name、s_sex；course 表字段：c_id、c_name、t_id；teacher表字段：t_id 、t_name；score 表字段：s_id、c_id、s_score。

-- 数据来源于 student 表和 score 表  注意此处要用左外连接并将 null 值置为 0.00

select s.s_id,s.s_name,round(ifnull(avg(s1.s_score),0.00),2) as avg_score 

from student s left join score s1 on s.s_id=s1.s_id group by s.s_id having avg_score<60;

/*
 * ifnull(v1,v2): 如果 v1 不为 null，则 ifnull 函数返回 v1; 否则返回 v2 的结果。
 */

4.4 示例: 查询与s_id=01号同学学习的课程完全相同的其他同学的信息（学号s_id，姓名s_name，性别s_sex）。其中, student表字段：s_id、s_name、s_sex；course表字段：c_id、c_name、t_id；teacher表字段：t_id 、t_name；score表字段：s_id、c_id、s_score。

-- 从score表中可得到 01 号同学所修的全部课程再在 student 表中查询
-- 思路 ：先查出 01 号修的课，再查出那些没修 01 号修的课的同学的 id，然后再查出不在所查 id 的同学的 id 即为大致符合要求的
-- 就是找同的但不好查，那就先查和它不同的学生id，再查其补集，那就是和它修相同课程的学生了，但要剔除 01
select * from student where s_id in 
(select s_id from score where s_id not in
(select s_id from score where c_id not in (select c_id from score where s_id='01'))
-- 分组筛选剔除01(从集合角度思考)
group by s_id having count(*)=(select count(*) from score where s_id='01') and s_id != '01');

4.5 示例: 查询各科成绩，进行排序并显示排名，按学生编号（s_id）、课程编号（c_id）、学生成绩（s_score）和排名（rank）进行输出，具体效果请查看测试集。其中，score表字段 : s_id、c_id、s_score。

select s.s_id,s.c_id,s.s_score,count(c.s_score)+1 rank 
from score s left join score c on s.c_id=c.c_id and s.s_score<c.s_score
group by s.s_id,s.c_id order by s.c_id,count(c.s_score);

4.6 示例: 查询选修“张三”老师所授课程的学生中，成绩最高的学生信息（具体输出信息请查看测试说明）及其成绩。其中, student 表字段：s_id、s_name、s_sex；course表字段：c_id、c_name、t_id；teacher表字段：t_id 、t_name；score 表字段：s_id、c_id、s_score。

select a.*,b.s_score, b.c_id,c.c_name 
from student a left join score b on a.s_id = b.s_id
left join course c on b.c_id = c.c_id
where b.s_score = (select max(s_score) from score where c_id in (select c_id from course c,teacher d where
c.t_id = d.t_id and d.t_name = '张三'));

4.7 示例: 查询两门及其以上不及格课程的同学的学号（s_id）、姓名（s_name）及其平均成绩（avg_score），要求计算平均成绩后为整数。（表结构同上）。

select s.s_id,s_name,round(avg(s1.s_score),0) as avg_score 
from student s join score s1 on s.s_id=s1.s_id 
group by s.s_id having avg_score<60 and count(*)>=2;

---

5. 分组选择数据

5.1 GROUP BY 与 聚合函数：

语法规则为：select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名]

需要注意：在 select 指定的字段要么就要包含在 Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中.

示例: 对年级 Id 和性别进行分组，分别统计表中2、3、4年级的男生总人数和女生总人数。

select gradeId,sex,count(*) from student where gradeId in(2,3,4) group by gradeId,sex;

5.2 使用 having 子句进行分组筛选：

简单来说，having 子句用来对分组后的数据进行筛选，即 having 针对查询结果中的列发挥筛选数据作用。

语法规则为：select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [having 字段名 筛选条件]

select 语句中，where、group by、having 子句和聚合函数的执行次序如下：

1.where 子句从数据源中去除不符合条件的数据；

2.然后 group by 子句搜集数据行到各个组中；

3.接着统计函数为各个组计算统计值；

4.最后 having 子句去掉不符合其组搜索条件的各组数据行。

Having 与 Where 的区别

     where 子句都可以用 having 代替，区别在于 where 过滤行，having 过滤分组；

     where 子句的作用是在对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，where 条件中不能包含聚组函数，使用 where 条件过滤出特定的行；

     having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用 having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

示例: 评选三好学生，条件是至少有两门课程在90分以上（包括90分）才能有资格，请列出符合的学生的学号（sno）及其90分以上（包括90分）科目总数；

select sno,count(*) from tb_grade where score>=90 group by sno having count(pno)>=2;

5.3 Group By 和 Order By：

语法规则为：select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [order by 字段名 排序方向]

注意 : 若分组字段和排序字段一样时，可不需要order by关键字，只需告知排序方向,即可写为 group by score desc

示例: 学校评选先进学生，要求平均成绩大于 90分（包括90分）的学生都有资格，并且语文课必须在 95分 以上（包括95分），请列出有资格的学生的学号（sno）及其科目的平均分。

select sno,avg(score) from tb_grade where sno in ( select sno from tb_grade where pno='语文' and score>=95 ) group by sno having avg(score)>=90;

---

6. SQL实战

6.1 LeetCode_分数排名

题目内容:

编写 SQL 查询对分数进行排序。排名按以下规则计算:
 
分数应按从高到低排列。
 
如果两个分数相等，那么两个分数的排名应该相同。
 
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。
按 score 降序返回结果表。(Scores表字段: id, score)

select score,(dense_rank() over (order by Score desc)) 'rank' from Scores;

---

MySQL四大排名函数

1. rank() over

• 作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
• 说明：例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。即：1 1 3 4 5 5 7

2. dense_rank() over

• 作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
• 说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6

3. row_number() over

• 作用：查出指定条件后的进行排名，条件相同排名也不相同，排名间断不连续。
• 说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。即：1 2 3 4 5 6

4. ntile() : Ntile(group_num) 将所有记录分成group_num个组，每组序号一样。

---

6.2 第N高的薪水

题目内容:

编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。Employee表的字段: id, salary

CREATE FUNCTION getNthHighestSalary ( N INT ) RETURNS INT 
BEGIN

DECLARE m INT;

SET m = N - 1;

    RETURN ( 
        SELECT ifnull( ( SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT m, 1 ), NULL ) 
        );
END

注意点：limit 里面不能做运算,要对输入的变量进行处理。

limit 2,1: 跳过 2 条取出 1 条数据，即读取第 3 条数据

limit 2 offset 1: 跳过1条取两条，即读取第2,3条

所以，读取第 N 条数据，则跳过前 N - 1 条记录，再读取一条记录。第二高薪水则可以：limit 1 offset 1（记录已去重）

---

6.3 部门员工最高的工资

题目内容:

编写SQL查询以查找每个部门中薪资最高的员工。

按 任意顺序 返回结果表。

Employee表字段：（id, name, salary, departmentId）

Department表字段：（id, name）

select d.name Department,e.name Employee,e.salary Salary from Employee e join Department d on e.departmentId = d.id 
where (e.departmentId,salary) in (select departmentId,max(salary) from Employee group by departmentId);

注意点:  每个部门的最高工资持有者可能不止一人，所以可以先在Employee表中分组查出每个部门的最高工资，然后，再进行连表查询，用 in 进行判断。

---

6.4 统计每个学校各难度的用户平均刷题数

题目内容：计算一些参加了答题的不同学校、不同难度的用户平均答题量。（结果在小数点位数保留4位，4位之后四舍五入）

用户信息表：user_profile：id, device_id, gender, age, university, gpa, active_days_within_30, question_cnt, answer_cnt

题库练习明细表：question_practice_detail：id ，device_id，
question_id ，result

question_detail : id ,question_id, difficult_level

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;

---

6.5 找出每个学校GPA最低的同学

题目内容：找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

用户信息表：user_profile：id, device_id, gender, age, university, gpa, active_days_within_30, question_cnt, answer_cnt

输出结果按university升序排序, 输出字段:device_id，university，gpa

select
  device_id,
  university,
  gpa
from
  user_profile
where
  (university, gpa) in (
    select
      university,
      min(gpa)
    from
      user_profile
    group by
      university
  )
order by
  university

-- 方法二：
select device_id, university, gpa
from (
    select *,
    row_number() over (partition by university order by gpa) as rn
    from user_profile
) as univ_min
where rn=1
order by university

窗口函数 row_number() over partition by 的使用：先分组再排序, row_number() over (partition by col1 order by col2)

---