【MySQL】表的增删改查

目录

前言：

新增（Create）：

查询（Retrieve）：

别名：

去重：DISTINCT 

排序：ORDER BY ：

条件查询：WHERE ：

分页查询：LIMIT：

修改（Update）：

删除（Delete）：

内容总结：

---

前言：

在学会对数据库的操作后，我们就要进入到更为复杂的对表的操作，可以说MySQL的难点和数据库的核心操作基本都在对表的操作，包括后面要学到约束什么的基本也都是对表的操作，所以我们就要开始表的基本内容增删改查（CRUD）。

CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

为了方便叙述下面均采用下面参数的表（student）来进行表的各类操作的演示。

新增（Create）：

语法：

INSERT [INTO] table_name 
 [(column [, column] ...)] 
 VALUES (value_list) [, (value_list)] ...
 
value_list: value, [, value] ...

小写简单版：

在表名后面可以指定某些列用括号括起来，里面的参数用逗号分开，values要对应。

基本语法如上，下面为它的多种用法👍👍👍。

（1）单行数据 + 全列插入

insert into student values ('唐三藏',65,71,83);

（2）多行数据 + 全列插入

如果可以的话我们是更加推荐多行数据插入的因为这样只需要向数据库发起一次请求，效率肯定要比访问数据库多次要高得多。

用逗号隔开即可。

insert into student values ('孙悟空', 81,45,63), ('张三', 73,84,56);

（3）多行数据 + 指定列插入

insert into student (name,math) values ('李四', 35), ('王五',83);

还有一个单行数据 + 指点列插入，这个由于和前面的这些类似，这里就不再演示。 

最终插入的数据如下：

扩展SQL语言特性：

友友们可以看看下面这个SQL语句，math的类型是int，但是这里插入varchar类型却显示成功了，这是为什么呢？

解释如下： SQL是一个“弱类型”的编程语言，和Java是不太一样的（强类型），当然C语言也是弱类型语言，那么我们如何理解弱类型和强类型呢？

在一个语言中，越是支持“隐式类型转换”，类型系统就越弱，越不支持“隐式类型转换”类型系统就越强，我们在学习Java的过程中不难看到Java基本都要自己手动转换（例如字符和整形，在c语言中二者可以直接相减，在Java中我们要进行手动转换后才能运算）。

查询（Retrieve）：

语法：

SELECT 
 [DISTINCT] {* | {column [, column] ...} 
 [FROM table_name]
 [WHERE ...]
 [ORDER BY column [ASC | DESC], ...]
 LIMIT ...

简单小写如下： 

（1）全列查询：

用法如下：

SELECT * FROM exam_result;

查找我们上面插入的数据。

但是在通常情况下不建议使用 * 进行全列查询。

原因主要有以下两点： 

（1）查询的列越多，需要传输的数据量越大，数据的压力越大。

（2）可能会影响到索引的使用。

（2）指定列查询：

SELECT id, name, english FROM exam_result;

 注意：指定列的顺序不需要按定义表的顺序来。

（3）查询字段为表达式： 

-- 表达式不包含字段
SELECT id, name, 10 FROM exam_result;
-- 表达式包含一个字段
SELECT id, name, english + 10 FROM exam_result;
-- 表达式包含多个字段
SELECT id, name, chinese + math + english FROM exam_result;

运用我们上面演示来进行。

注意：还记得一开始就和大家说的，MySQL是一个“客户端-服务器”结构的程序，此时数据是存储在服务器上的硬盘上的。上述的表达式查询，只是针对服务器响应得到的临时结果进行了计算，不会影响硬盘上存储的数据本身。

别名：

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称，语法：

SELECT column [AS] alias_name [...] FROM table_name;

用法如下： 

去重：DISTINCT 

使用DISTINCT关键字对某列数据进行去重：

语法：

演示如下： 

使用distinct语句：

排序：ORDER BY ：

具备排序功能我们才能更好的管理数据。

语法：

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...] 
 ORDER BY column [ASC|DESC], [...];

小写： 

注意点：

（1）没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

（2）NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面  。

针对一列排序：

演示如下：

使用表达式及别名排序：

演示如下：这里会出现NULL的原因是，元素只要和NULL发生运算都为NULL。

可以对多个字段进行排序，排序优先级随书写顺序：

演示如下：

原始数据如下：我们要对math和english排序，math的优先级大于english

排序前：

排序后：

降序排序：

默认排序是升序，我们可以在要排序的列后面加一个desc就可以升序排序。

条件查询：WHERE ：

比较运算符：

运算符	说明
>, >=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, ...)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

 注意点：

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

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

这里给大家解释一下为什么在使用where表达式不可以使用别名而order by却可以。这是因为一个SQL的执行顺序：

（1）遍历表

（2）带入条件(where)

（3）计算列名中的表达式（定义别名）

（4）排序/聚合等操作

 我们可以看见带入条件的顺序在定义别名之前，所以在where中不能够使用别名。

上述的演示如下🌸🌸🌸：

1.基本查询：

这个和一般语言的用法差不多。

-- 查询英语不及格的同学及英语成绩 ( < 60 )
SELECT name, english FROM exam_result WHERE english < 60;
-- 查询语文成绩好于英语成绩的同学
SELECT name, chinese, english FROM exam_result WHERE chinese > english;
-- 查询总分在 200 分以下的同学
SELECT name, chinese + math + english 总分 FROM exam_result 
 WHERE chinese + math + english < 200;

 2.AND与OR：

-- 查询语文成绩大于80分，且英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 and english > 80;
-- 查询语文成绩大于80分，或英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 or english > 80;
-- 观察AND 和 OR 的优先级：
SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;

 这里和我们一般学的语言&& 和 || 不一样友友们要区分清楚。

3.范围查询：

（1）BETWEEN ... AND ...  

语法：

-- 查询语文成绩在 [80, 90] 分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
-- 使用 AND 也可以实现
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese 
<= 90;

注意我们一般语言的范围默认是左闭右开，这里的between and 是左闭右闭。 

（2）IN

语法：

-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
-- 使用 OR 也可以实现
SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math 
= 98 OR math = 99;

where是连续的，in就是一个一个点。

4.模糊查询：LIKE

-- % 匹配任意多个（包括 0 个）字符
SELECT name FROM exam_result WHERE name LIKE '孙%';-- 匹配到孙悟空、孙权
-- _ 匹配严格的一个任意字符
SELECT name FROM exam_result WHERE name LIKE '孙_';-- 匹配到孙权

 “ = ” 是精确查询，要求查询出的结果和条件中的指定的内容完全一致。

“ like ” 是模糊查询，不要求完全一致，只要有一部分一致即可。这里需要引入通配符。注意：*不是like的通配符，like的通配符是“%”，“_”.

%:可以匹配任意多个字符（包括0），而_不能匹配0个字符。

例如：

 like这种模糊匹配在SQL中要慎用，因为开销很大，性能低下，本来SQL就不适合做这种😭😭😭。

5.NULL 的查询：IS [NOT] NULL  

-- 查询 qq_mail 已知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL;
-- 查询 qq_mail 未知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NULL;

这个简单就是和英文句子的意思一样。

分页查询：LIMIT：

有的时候虽然是进行条件查询，但是查询到的结果仍然非常多（例如浏览器就给你进行进行分页）

语法：

-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

总共有三种用法： 

1.limit n；

从 0 开始，筛选 n 条结果

2.limit s, n;

从 s 开始，筛选 n 条结果

3.limit n offset s;

从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用这种

修改（Update）：

语法：

UPDATE table_name SET column = expr [, column = expr ...]
 [WHERE ...] [ORDER BY ...] [LIMIT ...]

小写：

也可以一次修改多个列，set后面写多组列用‘ ，’ 分别进行 = 赋值即可。

例如：

原来唐三藏的math为71现在我们要把它改成98. 

修改结果如下：

注意：

（1）update 会修改服务器硬盘上存储的数据，且此操作不能撤回。

（2）注意MySQL语法不支持math += 10;这种写法，只能math = math + 10；

删除（Delete）：

语法：

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

 

删除唐三藏的数据。 

如果不指定条件的话就是删除所有数据，它和drop table 的区别是：drop是表和内容都没了，delete是只有数据没了，表还在。

内容总结：

新增：

-- 单行插入
insert into 表(字段1, ..., 字段N) values (value1, ..., value N);
-- 多行插入
insert into 表(字段1, ..., 字段N) values 
(value1, ...),
(value2, ...),
(value3, ...);

查询：

-- 全列查询
select * from 表
-- 指定列查询
select 字段1,字段2... from 表
-- 查询表达式字段
select 字段1+100,字段2+字段3 from 表
-- 别名
select 字段1 别名1, 字段2 别名2 from 表
-- 去重DISTINCT
select distinct 字段 from 表
-- 排序ORDER BY
select * from 表 order by 排序字段
-- 条件查询WHERE：
-- (1)比较运算符 (2)BETWEEN ... AND ... (3)IN (4)IS NULL (5)LIKE (6)AND (7)OR 
(8)NOT
select * from 表 where 条件

修改：

update 表 set 字段1=value1, 字段2=value2... where 条件

删除：

delete from 表 where 条件

希望友友们要自己实际操作一下，本身和数据结构比起来就不难，就是一个熟练度的问题。

结语：

其实写博客不仅仅是为了教大家，同时这也有利于我巩固知识点，和做一个学习的总结，由于作者水平有限，对文章有任何问题还请指出，非常感谢。如果大家有所收获的话还请不要吝啬你们的点赞收藏和关注，这可以激励我写出更加优秀的文章。