MySQL

概述

为了更好的理解这些概念，首先我们来思考一下：

• 什么是数据库?
• 什么是数据库管理系统?
• 什么是SQL?
• 他们之间的关系是什么?

数据库：

英文单词DataBase， 简称DB。按照一定格式存储数据的一些文件的组合。顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

数据库管理系统：

DataBaseManagement，简称DBMS。
12 数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。
常见的数据库管理系统：
MySQL、Oracle、 MS Sqlserver、DB2、sybase等….

结构化查询语言：SQL

程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL 语句，最终来完成数据库中数据的增删改查操作。

三者之间的关系

DBMS–执行–> SRL –操作–> DB

也就是说：

• MySQL通过执行SQL语句，来操作数据库

可以先安装数据库管理系统MySQL，然后学习SQL语句怎么写，编写SQL语句之后，DBMS 对SQL语句进行执行，最终来完成数据库的数据管理。

关系模型

关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。

• 表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
• 表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

主键

主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：

1. 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
2. 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

• 对于联合主键，允许一列有重复，只要不是所有主键列都重复即可
• 通过使用联合主键，可以使得关系表变得更加复杂

小结

主键是关系表中记录的唯一标识。

主键的选取非常重要：

• 主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。
• 主键也不应该允许NULL。

可以使用多个列作为联合主键，但联合主键并不常用

外键

一对多

通过使用外键，可以实现表与表之间的联系

例如：

• 小明的class_id是1，因此，对应的classes表的记录是id=1的一班；
• 小红的class_id是1，因此，对应的classes表的记录是id=1的一班；
• 小白的class_id是2，因此，对应的classes表的记录是id=2的二班。

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。

注：外键并不是通过列名实现的，而是通过定义外键约束实现的

多对多

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：

teachers表：

classes表：

中间表teacher_class关联两个一对多关系：

通过中间表teacher_class可知teachers到classes的关系：

• id=1的张老师对应id=1,2的一班和二班；
• id=2的王老师对应id=1,2的一班和二班；
• id=3的李老师对应id=1的一班；
• id=4的赵老师对应id=2的二班。

同理可知classes到teachers的关系：

• id=1的一班对应id=1,2,3的张老师、王老师和李老师；
• id=2的二班对应id=1,2,4的张老师、王老师和赵老师；

因此，通过中间表，我们就定义了一个“多对多”关系。

索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。

• 通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

下面举个例子，对score创建索引：

• 如果要经常根据score列进行查询，就可以对score列创建索引：

1
2

ALTER TABLE students
ADD INDEX idx_score (score);

使用ADD INDEX idx_score (score)就创建了一个

• 名称为idx_score

• 使用列score的索引。

  索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

1
2

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设students表的name不能重复：

1
2

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

• 通过UNIQUE关键字我们就添加了一个唯一索引。

也可以只对某一列添加一个唯一约束而不创建唯一索引：

1
2

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

这种情况下，name列没有索引，但仍然具有唯一性保证。

小结

• 通过对数据库表创建索引，可以提高查询速度。

• 通过创建唯一索引，可以保证某一列的值具有唯一性。

查询数据

基本查询

要查询数据库表的数据，我们使用如下的SQL语句：

1

SELECT * FROM <表名>

假设表名是students，要查询students表的所有行，我们用如下SQL语句：

1

SELECT * FROM students;

运行结果如下：

SELECT语句其实并不要求一定要有FROM子句。我们来试试下面的SELECT语句：

1

SELECT 100+200;

运行结果如下：

• 上述查询会直接计算出表达式的结果。

虽然SELECT可以用作计算，但它并不是SQL的强项,不带FROM子句的SELECT语句有一个有用的用途:

• 就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

小结

使用SELECT查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。

SELECT查询的结果是一个二维表。

条件查询

where

SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。

例如，要指定条件“分数在80分或以上的学生”

• 写成WHERE条件就是SELECT * FROM students WHERE score >= 80。

其中，WHERE关键字后面的score >= 80就是条件。

• score是列名，该列存储了学生的成绩
• 因此，score >= 80就筛选出了指定条件的记录

实操如下：

1

SELECT * FROM students WHERE score >= 80;

运行结果：

因此，条件查询的语法就是：

1

SELECT * FROM <表名> WHERE <条件表达式>

and

条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。

例如，符合条件“分数在80分或以上”，并且还符合条件“男生”，把这两个条件写出来：

• 条件1：根据score列的数据判断：score >= 80；
• 条件2：根据gender列的数据判断：gender = 'M'，注意gender列存储的是字符串，需要用单引号括起来。

就可以写出WHERE条件：score >= 80 AND gender = 'M'：

实操如下：

1

SELECT * FROM students WHERE score >= 80 AND gender = 'M';

运行结果:

or

第二种条件是<条件1> OR <条件2>，表示满足条件1或者满足条件2。

例如，把上述AND查询的两个条件改为OR，查询结果就是

• “分数在80分或以上”

• “男生”

满足任意之一的条件即选出该记录

实操如下：

1

SELECT * FROM students WHERE score >= 80 OR gender = 'M';

运行如下：

很显然OR条件要比AND条件宽松，返回的符合条件的记录也更多。

not

第三种条件是NOT <条件>，表示“不符合该条件”的记录。

例如，写一个“不是2班的学生”这个条件，可以

• 先写出“是2班的学生”：class_id = 2
• 再加上NOT：NOT class_id = 2：

实操如下：

1

SELECT * FROM students WHERE NOT class_id = 2;

运行如下：

上述NOT条件NOT class_id = 2其实等价于class_id <> 2，因此，NOT查询不是很常用。

（ ）

要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。

例如，编写一个复杂的条件：分数在80以下或者90以上，并且是男生,代码如下：

1

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

运行结果：

优先级

如果不加括号，条件运算按照以下优先级：

• NOT

• AND

• OR

即NOT优先级最高，其次是AND，最后是OR。但加上括号可以改变优先级。

常用的条件表达式

小练习

查询分数在60分(含)～90分(含)之间的学生可以使用的WHERE语句是哪些：

1. WHERE score >= 60 OR score <= 90

2. WHERE score >= 60 AND score <= 90

3. WHERE score IN (60, 90)

4. WHERE score BETWEEN 60 AND 90

5. WHERE 60 <= score <= 90

1

答案是2和4

投影查询

投影

使用SELECT * FROM <表名> WHERE <条件>可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的，即结果集的所有列与原表的所有列都一一对应。

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

例如，从students表中返回id、score和name这三列：

1

SELECT id, score, name FROM students;

运行结果：

这样返回的结果集就只包含了我们指定的列

• 并且结果集的列的顺序和原表可以不一样。

使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。

• 它的语法是

1

SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM······

例如，以下SELECT语句将列名score重命名为points，而id和name列名保持不变：

1

SELECT id, score points, name FROM students;

投影查询同样可以接WHERE条件，实现复杂的查询：

1

SELECT id, score points, name FROM students WHERE gender = 'M';

结果如下：

小结

使用SELECT *表示查询表的所有列

• 使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。

• SELECT语句可以对结果集的列进行重命名。

排序

升序（从小到大）

一般来说，查询结果集通常是按照id排序的，也就是根据主键排序。

• 我们可以加上ORDER BY子句，对数据进行排序

例如按照成绩从低到高进行排序：

1

SELECT id, name, gender, score FROM students ORDER BY score;

运行结果：

倒序

如果要反过来，按照成绩从高到底排序

• 我们可以加上DESC表示“倒序”：

1

SELECT id, name, gender, score FROM students ORDER BY score DESC;

结果如下：

二级排序

如果score列有相同的数据，要进一步排序，可以继续添加列名。

例如，使用ORDER BY score DESC, gender表示

• 先按score列倒序
• 如果有相同分数的，再按gender列排序：

实操如下：

1

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

运行结果：

注：默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即下面良种写法效果一样

• ORDER BY score ASC
• ORDER BY score

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。

• 例如，查询一班的学生成绩，并按照倒序排序：

1
2
3
4

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

小结

• 使用ORDER BY可以对结果集进行排序；

• 可以对多列进行升序、倒序排序。

分页查询

分页

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

• 要实现分页功能，实际上就是从结果集中显示第1100条记录作为第1页，显示第101200条记录作为第2页，以此类推。

• 因此，分页实际上就是从结果集中“截取”出第M~N条记录。

这个查询可以通过LIMIT <N-M> OFFSET <M>子句实现。我们先把所有学生按照成绩从高到低进行排序：

1

SELECT id, name, gender, score FROM students ORDER BY score DESC;

现在，我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：

下面查询第1页：

1
2
3
4
5

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

运行结果如下：

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：

1
2
3
4

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

结果如下：

类似的，查询第3页的时候，OFFSET应该设定为6:

1
2
3
4

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 6;

查询第4页的时候，OFFSET应该设定为9:

1
2
3
4

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 9;xxxxxxxxxx SELECT id, name, gender, scoreFROM studentsORDER BY score DESCLIMIT 3 OFFSET 9;-- 查询第4页

技巧

可见，分页查询的关键在于

• 首先要确定每页需要显示的结果数量pageSize（这里是3）
• 然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

总结出以下公式：

• LIMIT总是设定为pageSize，也就是每一页有多少条记录；
• OFFSET计算公式为pageSize * (pageIndex - 1)。

例如：每一页要包含4条记录，那么limit就是4

• 查询第1页是，就是LIMIT 4 OFFSET 0；
• 查询第2页是，就是LIMIT 4 OFFSET 4；
• 查询第3页是，就是LIMIT 4 OFFSET 8；
• 查询第4页是，就是LIMIT 4 OFFSET 12；
• 查询第5页是，就是LIMIT 4 OFFSET 16；

以此类推，这样就能正确查询出第N页的记录集啦

注：

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

聚合查询

计数

情景：

​ 假设我们要统计一张表的数据量，例如：想查询students表一共有多少条记录，难道必须用SELECT * FROM students查出来然后再数一数有多少行吗？

​ 显然不太可行，对于统计总数、平均数这类计算，SQL提供了专门的聚合函数

• 使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

仍然以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询：

1

SELECT COUNT(*) FROM students;

结果如下：

注：

COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表

• 只是这个二维表只有一行一列
• 并且列名是COUNT(*)。

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：

例如，使用聚合查询并设置结果集的列名为num:

1

SELECT COUNT(*) num FROM students;

结果如下：

COUNT(*)和COUNT(id)实际上是一样的效果。

• 此外，聚合查询同样可以使用WHERE条件

因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：

1

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

结果如下：

其他聚合函数

除了COUNT()函数外，SQL还提供了如下聚合函数：

注意

• MAX()和MIN()函数并不限于数值类型。
• 如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

平均数avg

要统计男生的平均成绩，我们用下面的聚合查询：

1

SELECT AVG(score) average FROM students WHERE gender = 'M';

1

SELECT AVG(score) average FROM students WHERE gender = 'M';

注：

要特别注意：如果聚合查询的WHERE条件没有匹配到任何行

• COUNT()会返回0
• SUM()、AVG()、MAX()和MIN()会返回NULL：

例如：

1

SELECT AVG(score) average FROM students WHERE gender = 'X';

匹配不到gender为X的数据，于是：

小练习1

每页3条记录，如何通过聚合查询获得总页数？

1. SELECT COUNT(*) / 3 FROM students;
2. SELECT FLOOR(COUNT(*) / 3) FROM students;
3. SELECT CEILING(COUNT(*) / 3) FROM students；

答案：选3.

分组

如果我们要统计一班的学生数量，我们知道，可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;。

• 那如果要继续统计二班、三班的学生数量，难道必须不断修改WHERE条件来执行SELECT语句吗？

对于聚合查询，SQL还提供了“分组聚合”的功能。

• 我们观察下面的聚合查询：按class_id分组:：

1

SELECT COUNT(*) num FROM students GROUP BY class_id;

结果如下：

执行这个查询，COUNT()的结果不再是一个，而是3个

• 这是因为，GROUP BY子句指定了按class_id分组
• 因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算
• 于是得到了3行结果。

但是这3行结果分别是哪三个班级的，不好看出来，所以我们可以把class_id列也放入结果集中：

按class_id分组:

1

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

结果如下：

• 这下结果集就可以一目了然地看出各个班级的学生人数。

重点：我们再试试把name放入结果集：

1

SELECT name, class_id, COUNT(*) num FROM students GROUP BY class_id;

结果如下：

！！！可以看到，执行这条查询我们会得到一个语法错误

• 因为在任意一个分组中，只有class_id都相同，name是不同的
• SQL引擎不能把多个name的值放入一行记录中
• 因此，聚合查询的列中，只能放入分组的列。

也可以使用多个列进行分组。例如，我们想统计各班的男生和女生人数：

1

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

结果如下：

• 上述查询结果集一共有6条记录，分别对应各班级的男生和女生人数

小练习2

• 使用一条SELECT查询查出每个班级的平均分：

1

SELECT AVG(score) average FROM students GROUP BY class_id;

结果：

• 使用一条SELECT查询查出每个班级男生和女生的平均分：

  1 2 3

  SELECT class_id,gender,avg(score) FROM students GROUP BY class_id,gender;

细节：

• 第一行的class_id和gender相当于是在表里找这两列，所以必须是已经存在的
• class_id和gender之间用”，“隔开
• 每一个分语句之间用空格隔开（换行不影响）
• 最后一行就是根据班级和性别来分组：也是用”，“隔开

结果如下：

小结

使用SQL提供的聚合查询，我们可以通过函数，方便地计算

• 总数count
• 合计值sum
• 平均值avg
• 最大值max
• 最小值min

多表查询

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：

1

SELECT * FROM <表1> <表2>

例如，同时从students表和classes表的“乘积”查询数据，可以这么写：

1

SELECT * FROM students, classes;

这种一次查询两个表的数据，查询的结果也是一个二维表

• 它是students表和classes表的“乘积”
• 即students表的每一行与classes表的每一行都两两拼在一起返回
• 结果集的列数是students表和classes表的列数之和
• 而行数则是students表和classes表的行数之积

值得一提的是，上述查询的结果集有两列id和两列name

• 两列id是因为其中一列是students表的id
• 而另一列是classes表的id
• 但是在结果集中，不好区分
• 两列name同理

要解决这个问题，我们仍然可以利用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：

1
2
3
4
5
6
7
8

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。

• 但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦
• 所以SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点：

1
2
3
4
5
6
7
8

    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

重点：从最后一行我们可以看到：

• FROM子句给表设置别名的语法是FROM <表名1> <别名1>, <表名2> <别名2>

• 这样我们用别名s和c分别表示students表和classes表。

此外，多表查询也是可以添加WHERE条件的

1
2
3
4
5
6
7
8
9

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

结果如下：

这个查询的结果集每行记录都满足条件s.gender = 'M'和c.id = 1。添加WHERE条件后结果集的数量大大减少了。

小结

使用多表查询可以获取M x N行记录；

多表查询的结果集可能非常巨大，要小心使用。

连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

选出所有学生：

1
2

SELECT s.id, s.name, s.class_id, s.gender, s.score 
FROM students s;

结果如下：

假设我们希望结果集同时包含所在班级的名称，上面的结果集只有class_id列，缺少对应班级的name列。

现在问题来了，存放班级名称的name列存储在classes表中，只要根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称。

这时，连接查询就派上了用场。我们先使用最常用的一种内连接——INNER JOIN来实现：

选出所有学生，同时返回班级名称：

1
2
3
4
5

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

结果如下：

INNER JOIN查询的写法是：

1. 先确定主表，仍然使用FROM <表1>的语法；
2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
4. 可选：加上WHERE子句、ORDER BY等子句。

使用别名不是必须的，但可以更好地简化查询语句。

那什么是内连接（INNER JOIN）呢？先别着急，有内连接（INNER JOIN）就有外连接（OUTER JOIN）。我们把内连接查询改成外连接查询，看看效果：

使用OUTER JOIN：

1
2
3
4

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

执行上述RIGHT OUTER JOIN可以看到，和INNER JOIN相比，RIGHT OUTER JOIN多了一行，多出来的一行是“四班”，但是，学生相关的列如name、gender、score都为NULL。

这也容易理解，因为根据ON条件s.class_id = c.id，classes表的id=4的行正是“四班”，但是，students表中并不存在class_id=4的行。

有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。它们的区别是：

INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。

RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

LEFT OUTER JOIN则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL：

• 先增加一列class_id=5: INSERT INTO students (class_id, name, gender, score) values (5, ‘新生’, ‘M’, 88);
• 使用LEFT OUTER JOIN

1
2
3
4

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER JOIN classes c
ON s.class_id = c.id;

结果如下：

最后，我们使用FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL：

使用FULL OUTER JOIN

1
2
3
4

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;

结果如下:

对于这么多种JOIN查询，到底什么使用应该用哪种呢？其实我们用图来表示结果集就一目了然了。

假设查询语句是：

1

SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：

LEFT OUTER JOIN是选出左表存在的记录：

RIGHT OUTER JOIN是选出右表存在的记录：

FULL OUTER JOIN则是选出左右表都存在的记录：

小结

JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；

INNER JOIN是最常用的一种JOIN查询,它的语法是:

1
2
3
4

SELECT ... 
FROM <表1>
INNER JOIN <表2> 
ON <条件...>

• JOIN查询仍然可以使用WHERE条件和ORDER BY排序。

修改 数据

关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete。其中，对于查询，我们已经详细讲述了SELECT语句的详细用法。

而对于增、删、改，对应的SQL语句分别是：

• INSERT：插入新记录；
• UPDATE：更新已有记录；
• DELETE：删除已有记录。

insert

当我们需要向数据库表中插入一条新记录时，就必须使用INSERT语句。

语法

INSERT语句的基本语法是：

1

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

例如，我们向students表插入一条新记录，先列举出需要插入的字段名称，然后在VALUES子句中依次写出对应字段的值：

例如：添加一条新记录：

1
2
3

INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
-- 查询并观察结果:
SELECT * FROM students;

结果如下：

可见，增加了一行字段 也就是第11行

可以注意到我们并没有列出id字段，也没有列出id字段对应的值，这是因为id字段是一个自增主键，它的值可以由数据库自己推算出来。此外，如果一个字段有默认值，那么在INSERT语句中也可以不出现。

注：

• 字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。
• 也就是说，可以写INSERT INTO students (score, gender, name, class_id) ...
• 但是对应的VALUES就得变成(80, 'M', '大牛', 2)。

还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值：

例如：

1
2
3
4
5

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);
--查看一下插入后的数据
SELECT * FROM students;

结果如下：

小结

使用INSERT，我们就可以一次向一个表中插入一条或多条记录。

update

若要更新数据库表中的记录，我们就必须使用UPDATE语句。

语法

UPDATE语句的基本语法是：

1

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

例如，我们想更新students表id=1的记录的name和score这两个字段

• 先写出UPDATE students SET name='大牛', score=66
• 然后在WHERE子句中写出需要更新的行的筛选条件id=1

更新id=1的记录如下：

1
2
3

UPDATE students SET name='大牛', score=66 WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students WHERE id=1;

结果如下：

注意到UPDATE语句的WHERE条件和SELECT语句的WHERE条件其实是一样的，因此完全可以一次更新多条记录：

例如更新id=5,6,7的记录：

1
2
3
4

UPDATE students SET name='小牛'，score=77
WHERE id >=5 AND id <=7;
-- 查询并观察结果:
SELECT * FROM students;

结果如下:

在UPDATE语句中，更新字段时可以使用表达式。

• 例如，把所有80分以下的同学的成绩加10分：

1
2
3
4

UPDATE students SET score=score+10
WHERE score<=80;
-- 查询并观察结果:
SELECT * FROM students;

其中，SET score=score+10就是给当前行的score字段的值加上了10。

没有匹配的记录时

如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。例如：

更新id=999的记录：

1
2
3

UPDATE students SET score=100 WHERE id=999;
-- 查询并观察结果:
SELECT * FROM students; 

注意！

最后，要特别小心的是，UPDATE语句可以没有WHERE条件，例如：

1

UPDATE students SET score=60;

这时，整个表的所有记录都会被更新！！！

所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。

delete

若要删除数据库表中的记录，我们可以使用DELETE语句。

语法

DELETE语句的基本语法是：

1

DELETE FROM <表名> WHERE ...;

例如，我们想删除students表中id=1的记录，就需要这么写：

1
2
3

DELETE FROM students WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students;

结果如下：

注意到DELETE语句的WHERE条件也是用来筛选需要删除的行，因此和UPDATE类似，DELETE语句也可以一次删除多条记录：

删除id=5,6,7的记录：

1
2
3

DELETE FROM students WHERE id>=5 AND id<=7;
-- 查询并观察结果:
SELECT * FROM students;

结果如下：

没有匹配结果时

如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除。例如：

删除id=999的记录：

1
2
3

DELETE FROM students WHERE id=999;
-- 查询并观察结果:
SELECT * FROM students;

结果如下：

注意！

最后，要特别小心的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据：

1

DELETE FROM students;

这时，整个表的所有记录都会被删除。所以，在执行DELETE语句时也要非常小心

• 最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集
• 然后再用DELETE删除。