MySQL 的常见操作（基础）

1. 基础操作

1.1 创建数据库 create database；

create database testdb;

运行这个命令，我们就可以创建出名为 testdb 的数据库

1.2. 创建表 create table 表名(字段)

create table student(id int, name varchar(20));

这就表示创建了一张名为 student 的表，有两个字段，一个是 id，类型是 int；一个是 name，类型是 varchar（字符串）。

1.3. 查看指定表的结构：desc 表名

举个例子：

desc student;

这里有 6 列，第一列是 Field，意思是字段，描述了这张都有什么字段，就是列的名字，我们这张表有两列，一列是 id，一列是 name。第二列是 Type，描述了列的类型（字段的类型），我们这里 id 是 int，这里的 11 意思是这一列在客户端查询时结果最多是 11 个字符，只和显示有关和存储无关；name 是 varchar，这里的 20 意思是最多 20 个字符。第三列是 Null，就是我们熟悉的空，YES 表示这一列可以为空，NO 就是不能为空。然后是 Default，表示列的默认值，我们这里 id 和 name 默认值都是 NULL。最后的 Extra 表示列的额外属性，比如递增之类的。

1.4. 删除表： drop 表名

drop table student;

运行这条指令，我们就会把名为 student 的表删掉。

2. 核心操作 CRUD

2.1 CRUD 介绍

C 即 增加 - create，R 即 查询 - retrieve， U 即更新 - update， D 即删除 - delete。

2.2 新增/插入数据

insert into 表名 values(值, 值, …);
举例：

 insert into student values(1, '张三');

这就说明我们插入成功了。这个 values 里的参数类型和数量要和表的字段相一致。SQL 没有字符类型，‘’ 和 “” 都可以表示字符串。
除此之外，我们也可以直插入指定列，比如 student 表有两列 id 和 name，我们可以只插入 id 或 name，我们以 name 为例：

insert into student (name) values('李四');

插入成功。此时 values 里的内容要和 表名后面括号里的内容匹配，这里的 李四 和 name 匹配。
我们还可以一次插入多条语句：

insert into student values(2, '李四'), (3, '王五');

执行成功。
这个时候可能就有人会好奇，我们插入进去的数据到底是什么样子，我们可以使用查询语句来查询：

select * from student;

这个查询语句我们在后面会讲到，这里先不讲。

还有关于时间日期数据的插入，我们新建一个表来演示：

create table homework(id int, createtime datetime);

插入时间日期我们使用特定格式的字符串，形如：‘2023-01-11 11:11:11’，当然也支持其他很多的格式，但是知道这个就够用了，想了解其他格式可以自行了解，笔者这里就不展开了。

insert into homework values(1, '2023-01-11 11:11:11');

插入成功。

此时如果说我们就像把时间日期设置成当前时刻，sql 提供了特殊函数 now();

insert into homework values (2, now());

此时就可以得到挡风墙爱你时刻的日期时间。

2.3 查找

2.3.1 全列查找

查找整个表的所有行所有列。
写法为： select * from 表名;
这里的 * 表示 所有的列，这种特殊含义的符号计算机中叫做“通配符”。
其实上面我们用到的查询就是这个全列查找：

这种查找效率很低，一般不推荐使用

2.3.2 指定列查找

语句格式为 ：select 列名, 列名… from 表名;
有的时候我们只想知道某一列或者某些列的内容，其他列我们并不关心，这个时候就可以使用这样的查询。
举个例子：

select name from student;

运行结果：

2.3.3 查询还可以使表达式

在查询过程中，可以做一些简单的运算，这个是列与列之间进行的运算。
我们创建新表 exam_result，以这张表为例：

create table exam_result (id int, name varchar(20), chinese decimal(3,1), math decimal(3,1), english decimal(3,1));
 insert into exam_result values (1, '唐三藏', 67, 98, 56),
    (2, '孙悟空', 87.5, 78, 77),
    (3, '猪悟能', 88, 98, 90),
    (4, '曹玄德', 84, 82, 67),
    (5, '刘玄德', 55.5, 85, 45),
    (6, '孙仲谋', 70, 73, 78.5);

首先我们使用全列查询来看一下表是什么样子：

我们可以在查询的时候针对分数进行变换，比如让查询的 math 成绩都在原来的基础上 +10 分：

select name, math + 10 from exam_result;

我们进行这样的查询，不会改变数据库中原来的数据，原因是 MySQL 是一个 “客户端-服务器”结构的程序，用户在客户端输入 SQL 语句。通过请求发送给服务器，服务器解析并执行 SQL，把查询的结果从硬盘读取出来，通过网络响应返回给客户端，客户端把这些数据以临时表的形式展示出来，所以我们上述看到的结果是一张临时表。这也是为什么我们在设计表的使用明明成绩是 decimal(3, 1) 为 三位数，有一位是小数，也就是形如 99.5，像100 这样的是插入不了的，而由于是临时表，所以就没有这个限制。

接下来我们再演示一下查询三个总成绩：

select name, math + chinese + english from exam_result;

此时我们可以发现，当前这个临时表的列名和我们查询时的表达式是一样的，但是很多时候表达式的含义是不直观的，此时就引出了我们的第四种查询方式：

2.3.4 查询的时候指定别名

所谓的别名，就相当于起了个“小名”，可以更直观的表达出含义。我们使用 as 关键字来将上述的三个成绩相加 改为 total：

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

这里的 as 其实可以省略，但是不建议省略，因为可读性很差，很容易看错。

2.3.5 去重查询

使用 distinct 针对指定列进行去重（把重复的行只保留一个）
此时我们先直接不去重：

select math from exam_result;

我们可以发现第一行和第三行都是 98.0，接下来我们使用去重查询：

select distinct math from exam_result;

此时就只有一行为 98.0。
此外，当 distinct 指定多个列的时候，要求这些列的值都相同蔡世伟重复，只有某一列或某几列相同不算。

2.3.6 查询结果排序

使用 order by 子句，指定某些列进行排序，可能是升序也可能是降序。
比如此时我想按照数学成绩升序排序：

select name, math from exam_result order by math;

升序还可以使用 asc 表示，但是省略不写默认就是升序。
我们再试试降序：

select name, math from exam_result order by math desc;

order by 是可以根据多个列来排序的，直接在 order by 后面通过 “,”来分割多个列，这个列越靠前，就是越关键的排序依据。先按照第一列排序，日过第一列的值相同了，再按照第二列排序…
举个例子：

select * from exam_result order  by math desc, chinese desc;

上述查询语句的意思是先按照数学成绩降序排序，如果数学成绩相同就按照语文降序排序。
在工作中遇到的大多数排序都是按照多个列来排序，我们最关键是要明确好排序的列的主次关系。

2.3.7 条件查询

就是在查询的时候可以指定筛选条件，也就是查询符合条件的数据。我们要先描述条件，SQL 中通过一系列的运算符来表示条件:


举些例子来操作一下上述运算符：
①

select * from exam_result where english < 60;

这个语句的意思就是查询所有列，英语成绩小于 60 分。

这里的 where English < 60 就相当于针对数据库中的表，依次进行遍历，取出每一行数据，把数据带入到条件中，看条件是否满足，如果满足就保留这条记录，作为结果集的一部分，如果不是就继续比较下一条。

②

select * from exam_result where chinese > english;

这个语句是列与列之间进行比较，查询出的记录是语文成绩大于英语成绩的数据。

③

select * from exam_result where chinese + math + english < 200;

条件查询可以使用表达式来作为条件，这条语句就是查询总分低于 200分的记录。这里的 chinese + math + english 可以在 select 查询的列中出现也可以不出现。
这里还有一点需要注意，我们可以起别名查询：

select name, chinese + math + english as total from exam_result where total < 200;

这个语句不难理解，就是查询name 和 语数外总分，语数外总分用 total 来代替，然后查询 total 小于 200 的数据，然后我们运行：

报错了，说有一个不认识的列叫做“total”，原因就是 SQL 语句的执行不是从前往后的执行，室友特定的规则的，而上述 SQL 语句的执行规则是这样的：

1. 遍历每一行
2. 把这一行带入到 where 的条件中
3. 符合条件的结果，再根据 select 这里指定的列进行查询/运算

很明显，这里先执行 where 后执行 查询，也就是说执行 where 的时候确实没有 total，所以我们只能把 where 后面的条件改成 chinese + math + english 即可：

select name, chinese + math + english as total from exam_result where chinese + math + english< 200;

④

select * from exam_result where chinese > 80 and english > 80;

我们这里就是使用 and 查询语文成绩 大于 80 并且 英语成绩 大于 80 的数据。

select * from exam_result where chinese > 80 or english > 80;

我们这里就是使用 or 查询语文成绩 大于 80 或者英语成绩 大于 80 的数据。

如果一个 where 中既存在 and 又存在 or，那么会先执行 and 后执行 or。对于这个优先级问题我们可以不强背下来，在 SQL 的时候记得加括号就行。

⑤
between … and… 查找的区间是前闭后闭区间（包含两侧边界）。
举例：

select * from exam_result where chinese between 80 and 90;

上述语句也可以替换成这个：

select * from exam_result where chinese >= 80 and chinese <= 90;

⑥

select * from exam_result where math in(58,59,98,99);

上述语句的意思是查找 math 值，有没有在 in括号里的，也就是有没有 58 或 59 或 98 或 99。也可以替换成下面的语句：

select * from exam_result where math = 58 or math = 59 or math = 98 or math = 99;

⑦ 模糊查询 like
能够进行模糊匹配，不要求元素完全相同，只要满足一定规则就可以。

我们先插入一条记录：

insert into exam_result values(7, '孙权', null, null, null);

select * from exam_result where name like '孙%';

上述查询的意思是查询 name 以 孙 开头的所有列。这里的 % 表示 孙 后面可以有 0 个 或多个字符。

select * from exam_result where name like '孙_';

此时只有一条记录，原因是 _ 只能匹配 sun 后面一个字符。

当然也可以写两个 _ ，表示匹配两个字符：

select * from exam_result where name like '孙__';

这里的 % 与 _ 位置可以在任何地方，比如在开头或者中间或者结尾，比如 ‘%孙’，‘%孙%’。
⑧
查询 null/NULL，SQL 不区分大小写：
我们先全列查询一下：

select * from exam_result;

此时我们可以知道孙权的三门成绩都是 NULL，现在我们想通过 NULL 查询到孙权这条记录：

select * from exam_result where chinese = NULL;

我们发现查询不到结果，原因是 null 和 其他数值进行运算，结果还是 null，null 结果在条件中就相当于 false 了。这里就相当于是 null = null 得到的结果还是 null，而 null 就相当于 false，也就是条件不成立，所以就不会查询出结果。
为了解决这个问题，我们可以使用 <=> 这个比较相等的运算符，这是可以处理 null 的：

select * from exam_result where chinese <=> NULL;

除此之外，我们还可以使用 is null / is not null 来判断是不是 null：

select * from exam_result where chinese is null;

⑨ 分页查询
使用 limit

此时我们全部数据是这样的：

但是如果我们指向查询前三条的话就可以用 limit 来限制：

 select * from exam_result limit 3;

limit 还可以搭配 offset 来决定从哪一条开始查询（从 0 开始计数），比如：

select * from exam_result limit 3 offset 0;

offset 就是偏移量，上述语句的意思就是查询三条数据，偏移 0 条数据，所以还是前三条。

select * from exam_result limit 3 offset 2;

此时就是查询三条数据，偏移两条，所以跳过了 1 和 2，从第 3 条开始显示，显示 3 条。这里的还可以将 limit 3 offset 2 等价写成 limit 2, 3，但是不建议这样写，因为不够直观。
由此我们可以知道，通过 limti 和 offset，可以实现分页功能。
这里的 limit 限制的是最多数量，如果不够的话，那就有多少显示多少。

select * from exam_result limit 10;

limit 也可以和前面的查询搭配着一起使用，比如我们要查询总分前三名的同学：

1. 计算每位同学的总成绩
2. 按照成绩排序（降序）
3. 取前三条记录

select name, chinese + math + english as total from exam_result order by total desc limit 3;

这里 order by 后面可以直接使用 total，因为 order by 的执行在查询后面。

此时此刻，关于查询的基础用法就介绍的差不多了。

2.4 修改

使用 update 关键字，update 表明 set 列名 = 值, 列名 = 值 … where 条件;当然也可以搭配 order by, limit 等使用，也可以没有。

update exam_result set math = 80 where name = '孙悟空';

这就代表我们修改成功了。

update exam_result set math = 60, chinese = 70 where name = '曹玄德';

（擦，才发现曹孟德搞成曹玄德了）我们同花顺地修改两列（多列）


接下来我们将总成绩倒数前三的同学的数学成绩加上 30 分：
我们先查询一下总成绩，看一下倒数后三名：

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

null 在排序的时候视为最小值。

update exam_result set math = math + 30 order by chinese + math + english limit 3;

此时修改成功。

此时我们发现孙权还是 null，就是因为 与 null 运算的结果还是 null，刘玄德本来 math 为 85.0，加上 30 后应该变成 115.0，但是由于我们设置成绩为 decimal(3,1)，也就是显示 3 位，包含一位小数，所以 99.9 就是能显示的最大值，修改到 99.9 就会进行截断操作。当然，有人也可能会报错：

表示修改的值超出了范围，修改也不会生效。

update exam_result set chinese = chinese / 2;

这个操作就相当于将所有人的语文成绩 修改为原来的 一半，此时 我们发现有两个 Warnings，我们可以使用 show warnings 来查看：

show warnings;

说明是 第 2 和 5 行出现了警告：

对比修改之前，孙悟空语文是 87.5，修改完按道理说是 43.75，刘玄德语文为 55.5，修改完按道理说是 27.75，但是由于我们设置了 三位有效数字，以为小数，所以这里也触发了截断，进行了四舍五入的操作。

2.5 删除

使用 delete，删除记录（行）
delete from 表名 where 条件; 也可以再搭配 order by/limit 等。

delete from exam_result where id = 7;

此时我们就将 id 为 7 的孙权删掉了：

delete from exam_result where name like '孙%';

此时我们就把 name 以孙开头的记录都删除掉：

3. 执行顺序

1. FROM 子句：指定要查询的数据表或视图。
2. WHERE 子句：根据指定的条件筛选数据。
3. SELECT 子句：选择要返回的列。
4. GROUP BY 子句：按照指定的列对结果进行分组。
5. HAVING 子句：对分组后的结果进行条件过滤。
6. ORDER BY 子句：对结果进行排序。
7. LIMIT 或 OFFSET 子句：限制返回的结果数量。