MySQL数据的增删改查（一）

在本篇文章中，我们来学习 C（create 增加）R（retrieve 查询）U（update 更新）D（delete 删除），即 数据的增删改查

新增（create）

要想插入数据，我们首先得有数据表，因此，我们先创建一张学生表：

USE test;

DROP TABLE IF EXISTS student; -- 若学生表已经存在，则删除
CREATE TABLE student(
id INT,
name VARCHAR(20),
age INT,
class VARCHAR(10)
);

学生表已经创建好了

接下来，我们就可以往表中插入数据了

插入单条记录

语法：

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

全列插入（所有数据都插入）时，可以省略 (column1, column2, column3, ...)

insert into student values (1, "张三", 18, "1班");

插入成功

其中，

Query OK：表示执行成功

1 row affected：表示操作影响了一行记录

**(0.01 sec)**：表示执行时间

ERROR：表示操作失败，出现了错误

Column count doesn't match value count at row 1：描述了错误原因，列数量与第一行中的值数量不匹配

而若只插入指定列，就需要使用 (column1, column2, column3, ...)，column 的数量必须和 value 的数量一致

insert into student (id, name, age) values (2, "李四", 19);

插入多条记录

语法：

INSERT INTO table_name (column1, column2, column3, ...) VALUES

    (value1_1, value2_1, value3_1, ...),
    (value1_2, value2_2, value3_2, ...),
    ...;

插入两条数据：

insert into student values
(3, "王五", 18, "2班"),
(4, "赵六", 19, "1班");

指定列插入两条数据：

insert into student (id, name, age)values

(5, "一一", 18), 
 (6, "二二", 19); 

注意：

（1）插入的数据类型要与表中列的数据类型匹配

（2）插入数据时，列的顺序必须和 VALUES 中的顺序一致

（3）若表的列中有 NOT NULL 约束，必须提供这些列的值（除非有默认值）

查询（retrieve）

查询所有列

语法：

select * from table_name;

我们查询刚才插入的所有数据：

由于 student 表中的数据不多，因此很快就将所有的数据都查询出来了，但是，当表中的数据很多时，此时查询所有数据就需要花费很多时间

由于 mysql 是** 客户端服务器 **结构的：

当查询的数据量很大时，select * from table_name 操作就会产生大量的硬盘 IO 和网络 IO，就可能把硬盘和网卡的带宽吃满，服务器就无法正常响应了，而若此时其他的客户端向服务器发送请求，服务器就无法响应数据，其他客户端就会认为服务器挂了

因此，通常情况下，并不建议使用 * 进行全列查询（查询的列越多，意味着需要传输的数据量越大）

查询特定列

语法：

SELECT column1 column2, ... FROM table_name;

注：指定列的顺序不需要安装定义表的顺序来

示例：

我们查询 id 和 年龄：

select id, age from student;

查询字段为表达式

语法：

select expr, ... from table_name;

示例：

表达式中不包含字段：

select id, name, 20 from student;

表达式中包含一个字段：

select id, name, age + 20 from student;

在查询出结果后，会将每一行带入表达式进行运算

当前的表达式查询，并没有修改服务器上硬盘存储的数据本体，只是在查询结果的基础上进行运算，得到的是一个 临时表，当这个查询操作结束时，这里的数据（age + 20）也就没有了，数据库服务器硬盘内容不会有任何改变

为了方便进行后续演示，我们再创建一个 考试成绩表：

drop table if exists exam_result;
create table exam_result(
id int,
name varchar(10),
chinese decimal(4,1),
math decimal(4,1),
english decimal(4,1)
);

-- 插入数据
insert into exam_result values
(1, "一一", 80.1, 70.9, 90.0),
(2, "二二", 60.9, 76.9, 90.5),
(3, "三三", 70.3, 96.3, 83.4),
(4, "四四", 83.7, 84.6, 75.3);

表达式中包含多个字段：

select id, name, chinese + math + english from exam_result;

别名

上述 chinese + math + english 计算的是成绩总和，我们一般会选择使用 总分 进行表示，因此，我们就可以以 总分 作为计算结果的临时别名

别名（alias）：用于为表或字段指定临时名称，以简化查询和提高可读性，别名在查询的执行结果过程中不会改变数据库中的实际表名或列名

语法：

SELECT column [AS] alias_name FROM table_name;

例如：

select id, chinese + math + english as '总分' from exam_result;

as 可以省略，即：

select id, chinese + math + english '总分' from exam_result;

去重

当某列中有重复记录时，我们就可以使用 **DISTINCT **对其进行去重

例如：

select age from student;

去重：

select distinct age from student;

排序

在 MySQL 中，可以使用** ORDER BY 来对查询结果进行排序，当没有使用 order by 子句时进行查询，返回的结果是未定义（无序）的**

按单列排序

语法：

SELECT column1, column2 FROM table_name ORDER BY column_name [ASC|DESC];

其中，ASC 表示 升序，排序时默认是升序，因此可以省略；DESC 表示降序

例如：

对数学成绩进行降序排列：

select id, name, math from exam_result order by math desc;

对语文成绩进行升序排列：

select id, name, chinese from exam_result order by chinese;

按多列排序

** 语法：**

SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

按照多个列进行排序时，先按照第一个列排序，若第一个列出现值相同的情况，则按照第二个列排序，以此类推

例如：

按照学生的年龄进行降序排列，若年龄相同，则按照 id 进行升序排列：

select id, name, age from student order by age desc, id;

使用表达式或别名排序

例如：

查询学生考试总分，按照升序进行排列：

select id, name, chinese + math + english as total from exam_result order by total;

排序NULL值

null 数据参与排序时，视为比任何值都小，升序时出现在最上面，降序时出现在最下面

条件查询

当我们只需要查询特定的数据时，就可以使用** WHERE** 进行条件查询，过滤数据，只返回满足特定条件的数据

在学习条件查询的语法之前，我们先来学习一些运算符

比较运算符

运算符说明>, >=, <, <=大于，大于等于，小于，小于等于=等于，NULL不安全**，出现 NULL = NULL 时结果为 NULL<=>等于，NULL安全**，出现 NULL <=> NULL 时结果为 true!=, <>不等于BETWEND a AND b范围匹配，[a, b]，若 a <= value <= b，返回 trueIN (option, ...)若是 option 中的任意一个，返回 trueIS NULL是 NULLIS NOT NULL不是 NULLLIKE模糊匹配，% 表示任意多个字符，_ 表示任意一个字符

逻辑运算符

运算符说明AND多个条件必须都为 true，结果才为 trueOR任意一个条件为 true，结果都为 trueNOT条件为 true 时，结果为 false
** 注意：**

（1）WHERE 条件中可以使用表达式，但是不能使用别名

（2）AND 的优先级高于 OR，在同时使用且需要先执行 or 时，需要使用 () 小括号包裹优先执行的部分

接下来，我们来学习如何进行条件查询

查询语法：

SELECT column1, column2 FROM table_name WHERE condition;

基本查询

例如：

查询年龄为 18 的学生：

select id, name, age from student where age = 18;

查询总分大于240的学生：

select name, chinese + math + english as '总分' from exam_result where chinese + math + english > 240;

查询英语成绩大于语文成绩的学生：

select name, chinese, english from exam_result where english > chinese;

AND和OR

查询 数学成绩高于80分 并且 语文成绩也高于80分 的同学：

select name, math, chinese from exam_result where math > 80 and chinese > 80;

查询 数学成绩高于80分 或 语文成绩也高于80分 的同学：

select name, math, chinese from exam_result where math > 80 or chinese > 80;

观察 AND 和 OR 的优先级：

select name, chinese, math, english from exam_result where chinese > 80 or math > 80 and english > 80;

上述查询相当于：

mysql> select name, chinese, math, english from exam_result where chinese > 80 or (math > 80 and english > 80);

若要先使用 OR，则需要加上 ()

select name, chinese, math, english from exam_result where (chinese > 80 or math> 80) and english > 80;

范围查询

BETWEEN AND

查询 数学成绩在 80 - 90 的同学：

select name, math from exam_result where math between 80 and 90;

IN

查询 id 为 1、3、4、9 的学生：

select id, name from exam_result where id in (1, 3, 4, 9);

模糊查询

使用** LIKE** 进行模糊查询

我们往 student 表中插入数据

insert into student (id, name, age) values (7, "张一一", 20), (8, "张二二", 17);

使用** _ 匹配任意一个字符**

select name from student where name like '张_';

使用 % 匹配任意多个（包括 0 个）字符

select name from student where name like '张%';

NULL 的查询

通过** IS [NOT] NULL **查询 NULL 值 或 过滤 NULL 值

查询班级已知的同学：

select name, class from student where class is not null;

查询班级未知的同学

select name, class from student where class is null;

分页查询

语法：

SELECT ... FROM table_name [WHERE...] [ORDER BY ... ] LIMIT count;

SELECT ... FROM table_name [WHERE...] [ORDER BY ... ] LIMIT offset, count;

SELECT ... FROM table_name [WHERE...] [ORDER BY ... ] LIMIT count OFFSET offset;

** offset**：开始的行号（行号从 0 开始）

count：返回的行数

当不指定 offset 时，默认从 0（也就是第一条记录）开始，若指定 offset ，则从 offset 开始，显示 n 条数据

例如：

按照 id 进行分页，获取第一页3条数据：

select id, name from student limit 3;

按照 id 进行分页，获取第二页3条数据：

select id, name from student limit 3, 3;

按照 id 进行分页，获取第三页3条数据：

select id, name from student limit 3 offset 6;

修改（update）

语法：

UPDATE table_name SET column1 = value1 [, column2 = value2] [WHERE condition] [ORDER BY condition] [LIMIT];

例如：

将 id 为 3 的学生的数学成绩修改为 87：

update exam_result set math = 87 where id = 3;

将所有学生的语文成绩加上 5 分：

update exam_result set chinese = chinese + 5;

将总成绩倒数的同学英语成绩加上 10 分：

update exam_result set english = english + 10 order by chinese + math + english limit 1;

删除（delete）

语法：

DELETE FROM table_name [ WHERE ...] [ ORDER BY ... ] [ LIMIT ... ];

例如：

删除 id 为 3 的学生数据：

delete from exam_result where id = 3;

删除总分最高的两名学生数据：

delete from exam_result order by chinese + math + english desc limit 2;

删除表中所有数据：

delete from exam_result;