数据修改、数据查询（二）

Mr.he...大约 9 分钟

1. 创建表

首先创建一张表如下，创建表的方法介绍过了，这里就不再赘述。

2. 数据修改(DML)

添加新数据
更改数据
删除数据（行）

2.1. 添加新数据：

INSERT INTO < 表名 > 
  (< 列名列表 >)
VALUES
  (< 值列表 >)

如：

INSERT INTO t_student (
    student_id,
    student_name,
    student_age,
    student_sex
  )
VALUES
  (1, '大毛', 18, '男');

其中列名可以省略，省略之后要求插入的值必须与列一一对应：

INSERT INTO t_student
VALUES
  (2, '王二', 20, '男');

多行数据添加：

INSERT INTO t_student
VALUES
  (3, '张三', 22, '男'),
  (4, '李四', 17, '女'),
  (5, '王五', 23, '男');

2.2. 更改数据：

UPDATE 表名
SET
  列1 = 新值1,
  列2 = 新值2
WHERE
  过滤条件

假如要修改李四的年龄为21岁

UPDATE t_student
SET
  student_age = 21
WHERE
  student_name = '李四';

注意

修改多个列的值时用逗号隔开。要想设置某一列的值为空，只需让<列名>=NULL 即可。WHERE表示过滤条件。

2.3. 删除数据（行）

DELETE FROM 表名
WHERE
  过滤条件

现要删除20到22岁的学生信息：

DELETE FROM t_student
WHERE
  student_age BETWEEN 20
  AND 22;

WHERE的判断条件之后会更详细的介绍。

删除除了DELETE还有一种方法TRUNCATE，写法：

TRUNCATE TABLE 表名;

二者区别在于：DELETE会记录日志，意味着删除后的数据还可以恢复，但是效率低。TRUNCATE不会记录日志，删除后的数据不能恢复，但是效率高。需要注意的是，TRUNCATE不能用于有外键约束引用的表。

3. 查询操作(DQL)

3.1. 基本查询

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

SELECT * FROM <表名>;

要查询t_students表的所有行，我们用如下SQL语句：

SELECT * FROM t_students;

其中SELECT是关键字，表示将要执行一个查询，*表示“所有列（字段）”，FROM表示将要从哪个表查询，本例中是t_students表。

该SQL将查询出t_students表的所有数据。

再来看一条查询语句。

SELECT 100+200;

上述查询会直接计算出表达式的结果300。虽然SELECT可以用作计算，但它并不是SQL的强项。但是，不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

SELECT 1;

这也是我们在使用大部分数据库连接软件测试连接时使用的语句。

3.2. 条件查询

也就是执行选择操作，关键字是：WHERE

指定哪些行出现在结果中

单条件选择
多条件选择
选择范围

3.2.1.单条件选择

标准结构

SELECT
  列1,
  列2
FROM 表名
WHERE
  列3 = 值

关系运算符包括：> >= < <= = !=

3.2.2.多条件选择

标准结构

SELECT
  列A,
  列B
FROM 表
WHERE
  条件1 
    AND 或者 OR 或者 NOT
  条件2

其中AND表示并且，OR表示或者，NOT表示排序符合此条件的记录。

如：

select 
  s_id , 
  s_name 
from students 
where 
  s_id < 8 
    and 
  s_sex = '男';

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

select 
  s_id , 
  s_name 
from students 
where 
  (s_id < 8 or s_age>23)
and 
  s_sex = '男';

这里就会先执行or，然后再执行后面的and，我们在实际使用中遇到多个条件运算符时，尽量加上括号，方便更好的阅读。

3.2.3. 选择范围

关键字：BETWEEN

BETWEEN后的值为从下限到上限。

如：

SELECT
  *
FROM t_student
WHERE
  student_age BETWEEN 20
  AND 23;

3.3. 投影查询

用于指定查询结果中能显示哪些列。

SELECT 列1, 列2 FROM 表名

多个列中间用逗号隔开，如果选择所有列可以用*号简写。

还是此表：

现在只想要查看姓名和年龄列：

SELECT student_name, student_age FROM t_student;

3.3.1. 组合查询

当我们需要将两列的数据放在一列（字段）中时，可以使用CONCAT来进行组合查询。

注意这里不是把其他列删除了，而是只显示我们想看见的部分。

SELECT CONCAT(student_name, '——', student_age) '组合值' FROM t_student;

CONCAT中除了需要的列（字段），还可以添加字符将列与列之间用想要的符号连接起来，这里使用的是字符'--' 。

3.3.2. 排除重复

使用DISTINCT。

现给原表加入一班级列student_class。

查询班级列得到：

SELECT student_class FROM t_student;

但是我们只想查看具体有哪些班级，这里就需要用到去重，也就是DISTINCT。

SELECT DISTINCT student_class FROM t_student;

3.3.3. 定义集合

IN或NOT IN。

现在想查看年龄为17、20、23的学生信息：

SELECT
  *
FROM t_student
WHERE
  student_age IN (17, 20, 23);

反之NOT IN就是选择不包括在集合里的学生信息。

3.3.4. 模糊查询

LIKE。

为了更好的解释模糊查询，这里重新建张表tt_student。

这里姓王的兄弟们躺枪...别介意。

首先先说下占位符与通配符：

占位符 _，表示任何单个字符。

通配符 %，表示包含零或多个字符。

下面就来用模糊查询逐一选中我们想要的行。

名字只有两个字的：

SELECT
  *
FROM t_student
WHERE
  student_name LIKE '__';

这里可能看不清，引号里实际是两个占位符。

所有姓王的：

SELECT
  *
FROM t_student
WHERE
  student_name LIKE '王%';

最后一个字是“王”的：

SELECT
  *
FROM t_student
WHERE
  student_name LIKE '%王';

只要是名字中有“王”字的：

SELECT
  *
FROM t_student
WHERE
  student_name LIKE '%王%';

这下模糊查询就很明白了吧，当然还有其他组合，大家可以自己尝试。

LIKE是模糊查询这些数据，NOT LIKE刚好相反，也就是不需要这些模糊查询出来的数据。

3.3.5. 处理空值数据

判断条件不能用列名=NULL，而是要用IS NULL或IS NOT NULL。

标准写法：

SELECT
  *
FROM t_student
WHERE
  student_age IS NULL;

3.4. 排序查询

ORDER BY。

指定查询的结果以什么样的顺序显示。

使用ORDER BY时，列名上指定ASC或DESC。ASC表示正序，DESC表示倒序。如果不指定则默认为正序。

按年龄排：

SELECT
  *
FROM t_student
ORDER BY
  student_age ASC;

SELECT
  *
FROM t_student
ORDER BY
  student_age DESC;

3.5. 分页查询

LIMIT

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

要实现分页功能，实际上就是从结果集中显示第1_{100条记录作为第1页，显示第101}200条记录作为第2页，以此类推。

因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT N OFFSET M子句实现。

其中：

LIMIT 为返回限定行数的查询，

LIMIT N 表示从第一行开始返回 N 行结果;

LIMIT N OFFSET M中的OFFSET M为偏移量，就记录集索引M之前的数据不管，从索引M开始返回N行结果。

3.5.1. 实操步骤

这里使用上面模糊操作中的表tt_student训练。

我们先把所有学生按照年龄从高到低进行排序：

-- 按student_age从高到低
SELECT * FROM tt_students ORDER BY student_age DESC;

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

-- 查询第1页
SELECT * 
FROM tt_students 
ORDER BY student_age DESC 
LIMIT 4 OFFSET 0;
-- 或者使用下面语句
SELECT * 
FROM t_students 
ORDER BY student_age DESC 
LIMIT 4 ;

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

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

-- 查询第2页
SELECT * 
FROM t_students 
ORDER BY student_age DESC 
LIMIT 4 OFFSET 4;
-- 或者使用下面语句
SELECT * 
FROM t_students 
ORDER BY student_age DESC 
LIMIT 4 4;

由于表tt_students只有6条数据，因此第2页只有2条记录，因此最终的结果集按实际数量2显示，`LIMIT 4表示的意思是“最多4条记录”。

如果后续还存在别的页，那么查询第3页的时候，OFFSET应该设定为8；

查询第4页的时候，OFFSET应该设定为12。

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

LIMIT = pageSize；
OFFSET = pageSize * (pageIndex - 1)。

这样就能正确查询出第N页的记录集。

原本表tt_student一共就6条记录，但我们把OFFSET设置为20，会得到什么结果呢？

-- OFFSET设定为20
SELECT * 
FROM t_students 
ORDER BY student_age DESC 
LIMIT 4 OFFSET 20;

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

3.5.2. 注意

OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。

在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

3.5.3. 小结

使用LIMIT <M> OFFSET <N>可以对结果集进行分页，每次查询返回结果集的一部分；

分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值。

3.5.4. 思考

在分页查询之前，如何计算一共有几页（totalPage）？

这部分可以通过后面学习聚合查询时获取总的数据量（totalSize），然后可以整除的情况下totalPage = totalSize/pageSize，不能整除还有余数的情况下，totalPage = totalSize/pageSize+1，这里是否整除可以判断totalSize%pageSize==0是否为true来确定。

4. 基本查询SQL的执行顺序（极重要）

执行FROM
WHERE条件过滤
SELECT投影
ORDER BY排序

还有聚合查询和多表查询，我们在第三节学习。

数据修改、数据查询（二）

# 1. 创建表

# 2. 数据修改(DML)

# 2.1. 添加新数据：

# 2.2. 更改数据：

# 2.3. 删除数据（行）

# 3. 查询操作(DQL)

# 3.1. 基本查询

# 3.2. 条件查询

# 3.2.1.单条件选择

# 3.2.2.多条件选择

# 3.2.3. 选择范围

# 3.3. 投影查询

# 3.3.1. 组合查询

# 3.3.2. 排除重复

# 3.3.3. 定义集合

# 3.3.4. 模糊查询

# 3.3.5. 处理空值数据

# 3.4. 排序查询

# 3.5. 分页查询

# 3.5.1. 实操步骤

# 3.5.2. 注意

# 3.5.3. 小结

# 3.5.4. 思考

# 4. 基本查询SQL的执行顺序（极重要）