MySQL 查询、修改

3 年前 · 来自专栏 MySQL

泥溪

stay hungry，stay foolish

干货:
使用SELECT查询的基本语句 SELECT * FROM <表名> 可以查询一个表的所有行和所有列的数据。SELECT查询的结果是一个二维表。
使用 SELECT * 表示查询表的所有列，使用 SELECT 列1, 列2, 列3 则可以仅返回指定列，这种操作称为投影。SELECT`语句可以对结果集的列进行重命名。
使用 ORDER BY 可以对结果集进行排序；可以对多列进行升序、倒序排序。
使用 LIMIT <M> OFFSET <N> 可以对结果集进行分页，每次查询返回结果集的一部分；分页查询需要先确定每页的数量和当前页数，然后确定 LIMIT 和 OFFSET 的值。
使用SQL提供的聚合查询，我们可以方便地计算总数、合计值、平均值、最大值和最小值；聚合查询也可以添加 WHERE 条件。
使用多表查询可以获取M x N行记录；多表查询的结果集可能非常巨大，要小心使用。
JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；INNER JOIN是最常用的一种JOIN查询，它的语法是 SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...> ；JOIN查询仍然可以使用 WHERE 条件和 ORDER BY 排序。
使用 INSERT ，我们就可以一次向一个表中插入一条或多条记录。
使用 UPDATE ，我们就可以一次更新表中的一条或多条记录。
使用 DELETE ，我们就可以一次删除表中的一条或多条记录。

三、查询数据

1.基本查询

SELECT * FROM <表名>

2.条件查询

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

优先级：NOT、AND、OR

3.投影查询

SELECT 列1, 列2, 列3 FROM <表名> WHERE <条件>

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

给查询的列起列名，结果集的列名就可以与原表的列名不同

SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM <表名> WHERE <条件>

4.排序

使用SELECT查询时，查询结果集通常是按照 id 排序的，也就是根据主键排序。如果我们要根据其他条件排序怎么办？可以加上 ORDER BY 子句（默认升序）。

升序：ORDER BY 列名 (ASC可省略)
降序：ORDER BY 列名 DESC

如果 score 列有相同的数据，要进一步排序，可以继续添加列名。例如，使用 ORDER BY score DESC, gender 表示先按 score 列倒序，如果有相同分数的，再按 gender 列排序：

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

如果有 WHERE 子句，那么 ORDER BY 子句要放到 WHERE 子句后面。例如，查询一班的学生成绩，并按照倒序排序：

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

5.分页查询

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

SELECT 列1, 列2, 列3 FROM <表名> WHERE <条件> LIMIT <M> OFFSET <N>

要实现分页功能，实际上就是从结果集中显示第1-100条记录作为第1页，显示第101-200条记录作为第2页，以此类推。因此，分页实际上就是从结果集中“截取”出第M~N条记录。

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

LIMIT 总是设定为 pageSize ；
OFFSET 计算公式为 pageSize * (pageIndex - 1) 。

注意

OFFSET 是可选的，如果只写 LIMIT 15 ，那么相当于 LIMIT 15 OFFSET 0 。
在MySQL中， LIMIT 15 OFFSET 30 还可以简写成 LIMIT 30, 15 。
使用 LIMIT <M> OFFSET <N> 分页时，随着 N 越来越大，查询效率也会越来越低。

例：把结果集分页，每页3条记录。要获取第1页的记录，可以使用 LIMIT 3 OFFSET 0 (注意SQL记录集的索引从0开始)

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

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

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

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

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

6.聚合查询

函数说明 SUM计算某一列的合计值，该列必须为数值类型AVG计算某一列的平均值，该列必须为数值类型MAX计算某一列的最大值MIN计算某一列的最小值COUNT查询所有列的行数

count

例子：查询 students 表一共有多少条记录

SELECT COUNT(*) FROM students;

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

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

SELECT COUNT(*) num FROM students;

COUNT(*) 和 COUNT(id) 实际上是一样的效果。另外注意，聚合查询同样可以使用 WHERE 条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：

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

MAX()和MIN()

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

AVG

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

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

要特别注意：如果聚合查询的 WHERE 条件没有匹配到任何行， COUNT() 会返回0，而 SUM() 、 AVG() 、 MAX() 和 MIN() 会返回 NULL .

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

SELECT CEILING(COUNT(*) / 3) FROM students;

分组

统计各班的男生和女生人数：

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

having

是将分组后的数据加上条件筛选，区别于where分组前的筛选

7.多表查询

笛卡尔查询： 查询的结果也是一个二维表，它是 students 表和 classes 表的“乘积”，即 students 表的每一行与 classes 表的每一行都两两拼在一起返回。结果集的列数是 students 表和 classes 表的列数之和，行数是 students 表和 classes 表的行数之积。

可能会出现结果集有两列 id 和两列 name ，两列 id 是因为其中一列是 students 表的 id ，而另一列是 classes 表的 id ，但是在结果集中，不好区分。两列 name 同理。要解决这个问题，我们仍然可以利用投影查询的“设置列的别名”来给两个表各自的 id 和 name 列起别名：

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

简洁写法：

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

8.连接查询

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

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

INNER JOIN

INNER JOIN只返回同时存在于两张表的行数据

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

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

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;

OUTER JOIN

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

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

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

四、修改数据

关系数据库的基本操作就是增删改查，即 CRUD：Create、Retrieve、Update、Delete

1.INSERT

基本语法：

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

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

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

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

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

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

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);
SELECT * FROM students;

2.UPDATE

基本语法：

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

例如，我们想更新 students 表 id=1 的记录的 name 和 score 这两个字段，先写出 UPDATE students SET name='大牛', score=66 ，然后在 WHERE 子句中写出需要更新的行的筛选条件 id=1

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

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

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

在 UPDATE 语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：

UPDATE students SET score=score+10 WHERE score<80;