原文在我的 Github 中,欢迎订阅。
前言
前几篇文章
之所以把数据查询单拉一个文章,是因为查询牵扯的知识点比较多,可以说在增删改查里,查的复杂度也是最高的。
之前已经了解一点像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 分的学生,显示学生的姓名和成绩
- 查询姓
马
的老师的个数 - 查询没有学过
马上来
老师课的学生姓名 - 查询所有学生的姓名、选课数量、成绩总和
我们一个一个来并分析。
查询成绩大于 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
语法,我会在后面的章节中专门细说,这里推荐几篇相关文章:
查询姓马的老师的个数
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
有几篇文章可以看看:
附建表语句
下面是几个表的建表语句:
-- 班级表
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'
);
总结
这篇文章主要了解查询,然而这也只是一个练习而已,实际开发中比这难的查询有很多,需要自己平常没事多练习。
今天工作比价忙,文章写的可能有点糙,如果有哪里不正确的地方欢迎指正。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。