MySQL总结

MySQL学习

多表查询

现在有两个表，“学生表”记录了学生的基本信息，有“学号”、“姓名”。

“成绩”表记录了学生选修的课程，以及对应课程的成绩。

这两个表通过**“学号”**进行关联。

现在要查找出所有学生的学号，姓名，课程和成绩。

【解题思路】

1.确定查询结果

• 题目要求查询所有学生的姓名，学号，课程和成绩信息

• select 学号,姓名,课程,成绩

• 查询结果的列名“学号”、“姓名”，在“学生”表里，列名“课程”、“成绩”在“成绩”表里，所以需要进行多表查询。

2.哪种联结呢？

• 涉及到多表查询，在之前的课程《从零学会sql：多表查询》里讲过需要用到联结。

• 多表的联结又分为以下几种类型：

  1）左联结（left join），联结结果保留左表的全部数据

  2）右联结（right join），联结结果保留右表的全部数据

  3）内联结（inner join），取两表的公共数据

• 这个题目里要求“所有学生”，而“所有学生”在“学生”表里。为什么不在“成绩”表里呢？

• 如果有的学生没有选修课程，那么他就不会出现在“成绩”表里，所以“成绩”表没有包含“所有学生”。

• 所以要以“学生”表进行左联结，保留左边表（学生表）里的全部数据。

• from 学生信息表 as a left join 成绩表 as b

3.两个表联结条件是什么？

• 两个表都有“学号”，所以联结条件为学号。

• on a.学号=b.学号

4.最终sql

select a.学号,a.姓名,b.课程,b.成绩
from 学生 as a
left join 成绩 as b
on a.学号=b.学号;

【本题考点】

考察多表联结，以及如何选择联结的类型。记住课程里讲过的下面这张图，遇到多表联结的时候从这张图选择对于的sql。

【举一反三】

有下面两个表

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

【思路】

从表的结构可以看出，表1（Person）是人的姓名信息，表2（Address）是人的地址信息。

​ 1）查询结果是两个表里的列名，所以需要多表查询

​ 2）考虑到有的人可能没有地址信息，要是查询结构要查所有人，需要保留表1（Person）里的全部数据，所以用左联结（left join）

​ 3）两个表联结条件：两个表通过personId产生联结。

【参考答案】

select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId;

如何查找第N高的数据

【题目】

现在有“课程表”，记录了学生选修课程的名称以及成绩。

现在需要找出语文课中成绩第二高的学生成绩。如果不存在第二高成绩的学生，那么查询应返回 null。

【解题思路】

1.找出所有选修了“语文”课的学生成绩

select * 
from 成绩表
where 课程='语文';

2.查找语文课程成绩的第二名

考虑到成绩可能有一样的值，所以使用distinct 成绩进行成绩去重。

思路1：

使用子查询找出语文成绩查询最大的成绩记为a，然后再找出小于a的最大值就是课程成绩的第二高值。

max(列名) 可以返回该列的最大值

可以用下面的sql语句得到语文课的最大值

select max(distinct 成绩) 
from 成绩表
where 课程='语文';

然后再找出小于a的最大值就是课程成绩的第二高值。

select max(distinct 成绩) 
from 成绩表
where 课程='语文' and
      成绩 < (select max(distinct 成绩) 
              from 成绩表 
              where 课程='语文');

思路2：使用 limit 和 offset

在《猴子 从零学会sql》中讲过：

limit n 子句表示查询结果返回前n条数据

offset n 表示跳过x条语句

limit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据

使用limit和offset，降序排列再返回第二条记录可以得到第二大的值。

select distinct 成绩  
from 成绩表
where 课程='语文'
order by 课程,成绩 desc
limit 1,1;

3.考虑特殊情况

题目要求，如果没有第二高的成绩，返回空值，所以这里用判断空值的函数（ifnull）函数来处理特殊情况。

ifnull(a,b)函数解释：

• 如果value1不是空，结果返回a

• 如果value1是空，结果返回b

对于本题的sql就是：

select ifnull(第2步的sql,null) as '语文课第二名成绩';

我们把第2步的sql语句套入上面的sql语句，本题最终sql如下：

select ifnull(
(select max(distinct 成绩) from 成绩表
where 成绩<(select max(成绩) from 成绩表 where 课程='语文')
and 课程='语文')
,null) as '语文课第二名成绩';

【题目】：

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

【本题考点】

​ 1） 第二高的查询思路，利用本题的解决办法可以解决这类问题：查询第N高的数据

​ 2） limit字句的用法

​ 3） ifnull的用法

【举一反三】
查找 Employee 表中第二高的薪水（Salary）。查询结果返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

【参考答案】

select ifNull(
(select distinct salary
from Employee 
order by Salary Desc
limit 1,1),null
) as SecondHighestSalary;

官方答案一:因为本表可能只有一项记录。为了克服这个问题，我们可以将其作为临时表。

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

官方答案二：用ifnull

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

基础语法

插入数据

先准备一张学生表，代码如下：

 create table student(
 id int,
 name varchar(30),
 age int,
 gender varchar(30)
 );

插入一条数据：

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);

示例：向学生表中插入一条学生信息 MySQL命令：

insert into student (id,name,age,gender) values (1,'bob',16,'male');

同时插入多条记录：