Mysql入门第四课数据查询

jiezi

5 年前

原文在我的 Github 中，欢迎订阅。

前几篇文章

Mysql 入门第一课《建表、改表、删表》
Mysql 入门第二课《数据类型》
Mysql 入门第三课《数据的增删改》

之所以把数据查询单拉一个文章，是因为查询牵扯的知识点比较多，可以说在增删改查里，查的复杂度也是最高的。

之前已经了解一点像WHERE id=2 这种非常简单的条件语句。

单表查询非常简单，但开发中更多的是多表查询，那我们以多表查询来说道说道。

我们在处理数据时通过某个字段来查另一个跟它有关的信息，除了在数据库中经常这样操作，在前端也有类似情况。

先看一段前端经常遇到的数据：

{
    province:'江苏省',
    citys:[
        '南京市',
        '苏州市',
        '无锡市'
    ]
}

上面是把省市都揉到一起了，只嵌套了两层，但如果嵌套个四五层，就像这样：

{
   province:'江苏省',
   children:[
    {
        name:'城市 1',
        children:[
            name:'江宁区',
            children:[name:'XX 小区']
        ]
    },
    {
        name:'城市 2',
        children:[
            name:'AA 区',
            children:[name:'BB 小区']
        ]
    }
   ]
}

这种数据解析起来会疯。

我们一直说数据扁平化，来我们扁平一把：

// 省
const provice = [
    {
        province:'江苏省',
        province_id: 1001
    },
    {
        province:'浙江省',
        province_id: 1002
    },
    ...
]

// 市
const citys = [
    {
      name:'南京市',
      province_id: 1001
    },
    {
      name:'苏州市',
      province_id: 1001
    },
    
    {
      name:'杭州市',
      province_id: 1002
    },
    {
      name:'嘉兴市',
      province_id: 1002
    },
    ...
]

// 找到江苏省下所有的城市
const result = citys.filter(i => i.province_id === 1001);

数据扁平化的好处就是，当不需要找 城市 的时候，citys 数据跟我无关，只需关心 province 就可以了，而且在查找性能上更快（有时候能免了递归）。

上面的例子引出下面这句话：在数据库中，通过某些字段将表与表关联起来，这就是关系型数据库的核心。

在图中可以看到 student 表里有 class_id，这样 学生 和 班级 通过 class_id 就有了关联，在开发中，我们可以通过它来查找 class 信息。

我们通过上面几个表来查询几个需求：

查询成绩大于 60 分的学生，显示学生的姓名和成绩
查询姓 马的老师的个数
查询没有学过 马上来 老师课的学生姓名
查询所有学生的姓名、选课数量、成绩总和

我们一个一个来并分析。

SELECT t1.student_name, t2.number FROM 
student t1 LEFT JOIN score t2 ON t1.id=t2.student_id 
WHERE t2.number>60;

先看结果：

得到了正确数据。

分析语句：
t1和 t2 分别是 student 和 score 的别名。
细心的同学能看出，我把上面的 sql 语句用三行来显示，这是有寓意的哟：

第一行：要查询的字段，这个非常好理解
第二行：其实它的结果是个临时表！即对应查询语句里的 table_name !
第三行：通俗易通的 WHERE 条件语句

也就是说，它依然是符合通用语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][OFFSET M]

只不过第二行生成了一个临时表。

这里牵扯到了 JOIN ON 语法，我会在后面的章节中专门细说，这里推荐几篇相关文章：

图解 SQL 里的各种 JOIN
图解 SQL 的 inner join、left /right join、outer join 区别
[SQL 多表查询：SQL JOIN 连接查询各种用法总结

天空未蓝](https://zhuanlan.zhihu.com/p/…

SELECT COUNT(id) AS teacher_num FROM teacher WHERE teacher_name LIKE '马 %';

解析：

COUNT(fieldName): COUNT 函数用于统计某字段数量
AS: 取别名
LIKE：一般与 % 使用，模糊搜索，如果不用 % 相当于精确搜索。
%：表示任意字符，类似于正则表达式里的*

这个查询比较复杂，我们先上 sql :

SELECT 
t1.student_name, 
IFNULL(t2.course_num,0) AS course_num, 
IFNULL(t2.sum_number,0) AS sum_number FROM 
student t1 
LEFT JOIN 
(SELECT student_id,count(id) course_num, SUM(number) AS sum_number FROM score GROUP BY student_id) t2 
ON t1.id=t2.student_id;

再看下结果：

先！不！要！慌！我们一点一点来解析。

现在你脑海里应该先浮现出通用查询语句：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][OFFSET M]

而图中的查询语句翻译过来就是：

SELECT 学生名, 选课数量, 成绩总和 FROM 表;

然后我们来拆分上图中的查询：
先看 SELECT student_id,count(id) course_num, SUM(number) AS sum_number FROM score GROUP BY student_id，我们单独来执行这句看看结果：

这条语句为我们生成了一个表，它显示了学生 id、选课数、总成绩，所以这张表示核心，但需求是让我们展示所有的学生，所以我们必须依赖 student 查。

如果把上图中查出来的结果命名为t2，就会变成：

SELECT 
t1.student_name, IFNULL(t2.course_num,0) AS course_num, IFNULL(t2.sum_number,0) AS sum_number 
FROM student t1 LEFT JOIN t2 
ON t1.id=t2.student_id;

再去掉些“多余”的部分：

SELECT 
t1.student_name, t2.course_num, t2.sum_number 
FROM student t1 LEFT JOIN t2 
ON t1.id=t2.student_id;

哈哈，是不是一下就看懂了呢？

这里再介绍下语句里没见过的东东：

IFNULL(a,b)：类似常见的 if 语句，判断 a 是否为 null，如果是则显示 b。
COUNT()：对读取的数据中的某字段计算出个数，一般用于查询出数据的条数。
SUM()：求和，对读取数据中的某个字段求和。
GROUP BY：通过 GROUP BY 可以设定通过哪些字段对读取的数据进行分组排序(默认升序)，需要注意的是，GROUP BY 有分组聚合功能。

关于 GROUP BY 有几篇文章可以看看：

Group by 的理解
一张图让你详细理解 Group By 的分组聚合过程

下面是几个表的建表语句：

-- 班级表
CREATE TABLE class(
    id INT UNSIGNED PRIMARY KEY auto_increment,
    caption VARCHAR(30) COMMENT '班级名'
);

-- 学生表
CREATE TABLE student(
    id INT UNSIGNED PRIMARY KEY auto_increment,
    student_name CHAR(30) COMMENT '学生名',
    gender CHAR(30) DEFAULT NULL    COMMENT '学生性别',
    class_id INT DEFAULT NULL COMMENT '班级 id'
);

-- 老师表
CREATE TABLE teacher(
    id INT UNSIGNED PRIMARY KEY auto_increment,
    teacher_name CHAR(30) COMMENT '教师名'
);

-- 课程表
CREATE TABLE course(
 id INT UNSIGNED PRIMARY KEY auto_increment,
 course_name CHAR(30) COMMENT '课程名',
 teacher_id INT DEFAULT NULL COMMENT'教师 id'
);

-- 成绩表
CREATE TABLE score(
    id INT UNSIGNED PRIMARY KEY auto_increment,
    number INT DEFAULT NULL COMMENT '分数',
    student_id INT DEFAULT NULL COMMENT '学生 id',
    course_id INT DEFAULT NULL COMMENT '课程 id'
);

这篇文章主要了解查询，然而这也只是一个练习而已，实际开发中比这难的查询有很多，需要自己平常没事多练习。

今天工作比价忙，文章写的可能有点糙，如果有哪里不正确的地方欢迎指正。

Mysql入门第四课数据查询

前言

热热身

准备几张表

查询

查询成绩大于 60 分的学生，显示学生的姓名和成绩

查询姓马的老师的个数

查询所有学生的姓名、选课数量、成绩总和

附建表语句

总结