乐趣区

Mysql入门第四课数据查询

原文在我的 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 信息。

查询

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

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

我们一个一个来并分析。

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

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

先看结果:

得到了正确数据。

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

  1. 第一行:要查询的字段,这个非常好理解
  2. 第二行:其实它的结果是个临时表!即对应查询语句里的 table_name !
  3. 第三行:通俗易通的 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;

哈哈,是不是一下就看懂了呢?

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

  1. IFNULL(a,b):类似常见的 if 语句,判断 a 是否为 null,如果是则显示 b。
  2. COUNT():对读取的数据中的某字段计算出个数,一般用于查询出数据的条数。
  3. SUM():求和,对读取数据中的某个字段求和。
  4. 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'
);

总结

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

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

退出移动版