MySQL数据的增删改查（一）

目录

新增（create）

插入单条记录

插入多条记录

查询（retrieve）

查询所有列

查询特定列

查询字段为表达式

别名

去重

排序

按单列排序

按多列排序

使用表达式或别名排序

排序NULL值

条件查询

比较运算符

逻辑运算符

基本查询

AND和OR

范围查询

BETWEEN AND

IN

模糊查询

NULL 的查询

分页查询

修改（update）

删除（delete） 

---

在本篇文章中，我们来学习 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，返回 true
IN (option, ...)	若是 option 中的任意一个，返回 true
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配，% 表示任意多个字符，_ 表示任意一个字符

逻辑运算符

运算符	说明
AND	多个条件必须都为 true，结果才为 true
OR	任意一个条件为 true，结果都为 true
NOT	条件为 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;