MySQL表的增删改查（基础）

👨‍💻作者简介：👨🏻‍🎓告别，今天

📔高质量专栏 ：☕java趣味之旅

📔（零基础）专栏：MSQL数据库​​​​​​​

欢迎🙏点赞🗣️评论📥收藏💓关注

💖衷心的希望我的📙作品能给大家带来收获。

1. 新增（Create）

语法：

 create table student (id int,name varchar(20));
 insert [into] student  values (值，值);

以上的into通过大括号括起来,表示的意思是可以省略,但一般都以写为主。以上的值与上面的列的类型相对应,注意这里的顺序和上面的列的类型的顺序一定要一样，不然会报错.

 例子如下：

create table student (id int,name varchar(20));
 insert into student value (1,'张三');
 insert into student value (2,'zhangsan');
 

 

在SQL中'和“都是来表示字符串;因为在SQL中没有"字符"这个类型。

注意：这里还有一种常见的出错，当我们插入的字符串带有中文时，会出现错误，这是因为你创建数据库时没有指定utf8mb4字符集，这里参考我上一篇博客的1.2创建数据库,可以得知。

小技巧:

通过在命令行中按上下方向键，可以得到上一列的SQL语句。

1.1 单行数据 + 全列插入

使用insert的时候可以指定列插入,默认情况下是必须把这一行的所有列都进行插入的

 //插入两行数据 数量必须和定义表的列的数量及顺序一致
INSERT INTO student VALUES (100, '唐三藏');
INSERT INTO student VALUES (101, '孙悟空');

1.2 多行数据 + 指定列插入

这里的列明个数和顺序，就不需要和表头信息一致，但是你要确保你写的列明是存在的。

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
INSERT INTO student (id,name) VALUES
 (102, '曹孟德'),
 (103, '孙仲谋');

 默认值为NULL，相当于啥都没填

可以一次性插入多行数据

例子如图：

当然，也可以在插入多行的时候，也可以指定列。和上面的原理差不多，相同这里就不多展示了。

1.3补充：

如何进行日期类型插入？

 create table test (date datetime);
 insert into test values ('2024-4-27 18:30:00');

有的时候插入的数据，要指定的时间，就是当前系统时间

mysql 中有一个专门的函数now（）来获取.

SQL作为一个编程语言也提供了一些库函数。

2. 查询（Retrieve）

查询  select 中文（选择）
select 执行过程 ，本质上就是在选择。
在增删改查过程中，查询是最复杂的

2.1 全列查询

语法：

 --通常情况下不建议使用 * 进行全列查询
-- 1. 查询的列越多，意味着需要传输的数据量越大；
-- 2. 可能会影响到索引的使用。（索引待后面课程讲解）
SELECT * FROM 表名;

*表示“通配符”可以代指所有列

能够查询出表中的所有行和所有列

提示：

select*也是一个危险操作，在实际工作和生产环境中要慎用!当数据库中的数据量很大，这个就麻烦了。

案例：

select * from student；

2.2 指定列查询

语法：

SELECT 列名，列名…… FROM  表名;

要查询哪些列才会真正返回对应的列。

案例：

-- 指定列的顺序不需要按定义表的顺序来
select id,name from student;
select id from student;
select name,id from student;

2.3 查询字段为表达式

案例：

-- 创建考试成绩表
DROP TABLE IF EXISTS 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 (id,name, chinese, math, english) VALUES
 (1,'唐三藏', 67, 98, 56),
 (2,'孙悟空', 87.5, 78, 77),
 (3,'猪悟能', 88, 98.5, 90),
 (4,'曹孟德', 82, 84, 67),
 (5,'刘玄德', 55.5, 85, 45),
 (6,'孙权', 70, 73, 78.5),
 (7,'宋公明', 75, 65, 30)

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

decimal(3,1)限制的是硬盘上的数据，而不是临时表的数据。临时数值可以超过100，临时表能够尽可能地保证你最终的计算结果是正确的。

表达式查询，可以多个列一起参与运算

注意:
MySQL是一个客户端-服务器结构的程序，此时的数据是存储在服务器的硬盘上的。
上述的表达式查询只是针对服务器响应得到的临时结果进行了计算，不影响硬盘上存储的数据本体

2.4 别名

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

SELECT 表达式 [ AS ] 别名 [...] FROM 表名;

as 可以省略,但为了提高代码的可读性，一般不省略

-- 结果集中，表头的列名=别名
SELECT id, name, chinese + math + english 总分 FROM exam_result;

案例：

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

2.5 去重：DISTINCT

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

针对查询结果进行去重存在重复的数据，就会把重复的一行合成一行

格式：

select distinct 列名 from 表名；

-- 98 分重复了

SELECT math FROM exam_result;

--去重结果

SELECT DISTINCT math FROM exam_result;

如果有多个列也是按上面的格式那样写。

--去重结果

SELECT DISTINCT math,english FROM exam_result;

 我们发现之前重复的数学成绩，在我们进行两个列，就没有了重复，这是因为当两行的两个列都相同的时候，才算重复

案例：

 插入数据：insert into  exam_selt (math,english) values (98.0,56.0);

2.6 排序：ORDER BY

select的查询结果默认是无序的。

语法：

-- ASC 为升序（从小到大）

-- DESC 为降序（从大到小）

-- 默认为 ASC

SELECT ... FROM 表名 [ WHERE ...]

ORDER BY 列名和表达式 [ ASC | DESC ], [...];

案例：

如上图的顺序，这个代码MySQL并不承诺按照id升序排序

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

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

-- 查询同学姓名和 math，按 math 成绩升序/降序 排序显示
SELECT name, math FROM exam_result ORDER BY math;
SELECT name, math FROM exam_result  ORDER BY math DESC;

此处的排序默认是按照升序排列，如果要降序排列，还需要加上desc关键字

3. 使用 表达式 及 别名 排序

-- 查询同学及总分，由高到低
SELECT name, chinese + english + math FROM exam_result 
 ORDER BY chinese + english + math DESC;
SELECT name, chinese + english + math total FROM exam_result 
 ORDER BY total DESC;

SELECT name FROM exam_result ORDER BY chinese + english + math DESC;

这里是按照总成绩排序的，但是总成绩不一定非得要显示

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

-- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
SELECT name, math, english, chinese FROM exam_result 
 ORDER BY math DESC, english, chinese;

注意:
SQL中在进行算术运算的时候,如果其中某个操作数结果是NULL，最后结果也就是NULL

2.7 条件查询：WHERE

格式：

select 列名 from 表名 where 条件（逻辑运算符/关系运算符描述这里的条件）;

比较运算符：

注意：

1. 没有==，是使用=表示比较相等
2. NULL 和其他值进行关系运算结果也是NULL
3. NULL 在 SQL 中可以隐式转成false
4. NULL 和 NULL 比较，到底是 true 还是 false 也要看实际的场景的!!

逻辑运算符：

注 ：

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

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

案例：

• 基本查询：

-- 查询英语不及格的同学及英语成绩 ( < 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;

上述代码如何执行的呢？

数据库服务器收到这个SQL之后，就会遍历表中的每一个数据行，取出当前行,带入到条件之中
如果条件成立，当前这一行就会进入到结果集合中。
如果不成立，跳过，进入下一条数据

上述条件查询的过程就可以理解成按照条件进行 “筛选”
因此 select 本身就是 “选择”

上述条件查询的过程就可以理解成按照条件进行 “筛选”
因此 select 本身就是 “选择”

通过上面的执行过程，我们可以发现。执行条件的司机要比定义别名更靠前，第一别名，在计算列名中的表达式那一步。因为排序在定义别名的后一步，所以我们可以在排序聚合等操作定义别名，而不能在条件中定义表明。

上述解释where不能使用别名的原因是执行顺序的缘故，其实这样的解释非常牵强。

• 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;

AND:两边同时满足条件

OR:满足一方条件

我们发现在第三列SQL语句中，and先执行。
带给我们的启示:虽然优先级存在，但在实际写代码的时候最好还是多写括号()，通过括号的显示指定计算顺序。依赖默认的优先级可能导致出错以及代码的不可读性。

• 范围查询

1. BETWEEN ... AND ...

-- 查询语文成绩在 [80, 90] 分的同学及语文成绩 此处包含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;

在编程中谈到“区间”大概率是前闭后开区间，少数情况是闭区间，开区间和前开后闭，基本看不到

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;

执行过程:针对每个记录，带入到条件中看，当前数字的值是否在()描述的集合中存在。

between...and...与in的区别，between...and...表示一个连续的区间，in描述的是“离散”的集合

• 模糊查询：LIKE

= 精确查询，要求查询出的结果，和条件中的指定内容，完全一致。
like 模糊查询，不要求完全一致，只要一部分一致即可。
此出也需要引入通配符，来描述的匹配规则

  % 匹配任意个任意字符，你可以匹配0个字符
   _ 匹配一个任意字符

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

 当我们插入以下数据时:

insert into exam_result (name) values ('孙'),('孙行者'),('者行孙'),('行者孙');

匹配孙开头的内容：SELECT name FROM exam_result WHERE name LIKE '孙%';

匹配孙结尾的内容：SELECT name FROM exam_result WHERE name LIKE '%孙';

匹配包含孙的内容： SELECT name FROM exam_result WHERE name LIKE '%孙%';

SELECT name FROM exam_result WHERE name LIKE '孙_';
SELECT name FROM exam_result WHERE name LIKE '孙__';
SELECT name FROM exam_result WHERE name LIKE '_孙_';

注意：

like 这种模糊匹配，在sql中要慎用。这样的操作，开销是很大的，性能非常低。

特别是在数据库的中的数据特别多的时候

• NULL 的查询：IS [NOT] NULL

select * from exam_result where name = null;

SQL 中，null = null => null (false) 条件不成立

-- 查询 exam_result 为空的数据.
select * from exam_result where name <=> null;
select * from exam_result where name is null;

is null也可以针对两个列比较,适用范围更广

同样是null,是视为相等，还是不相等，要根据实际的场景来灵活调整。

2.8 分页查询：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;

limit 关键字来进行描述限制。这次查询最多返回多少条记录。
带有limit的SQL执行速度，往往会更快一点，遍历表/构造结果结合的时候，如果发现结果集合已经满了，就会停止遍历。

offset 为偏移量(相当于下标)
不写 offset 就相当于是 offset  0

案例：按 id 进行分页，每页 3 条记录，分别显示 第 1 、 2 、 3 页

-- 第 1 页
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 0;
-- 第 2 页
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 3;
-- 第 3 页，如果结果不足 3 个，不会有影响
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 6;

 3. 修改（Update）

语法：

表名 set 列名 = 值 where 条件；

update 会修改服务器硬盘上存储的数据

案例：

-- 将孙悟空同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
--也可以一次性修改多个列set，后面写多组列分别进行=赋值就可以
-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';

原则上说，匹配到的行都要修改，但是有的时候，比如修改的值是非法的值，修改失败，此时修改成功的行数就会少于匹配的行数。

如果我们想要把唐三藏的语文成绩加10分，可以借助表达式

UPDATE exam_result SET chinese = chinese + 10 where name = "唐三藏";

此处不能写作chinese + = 10

上述的代码为什么会报错呢？

因为上述代码超过了100，超出了我们在定义类型时decimal（3,1）范围。

sql中 给出的这些类型，尤其是这些限制是非常有用的。
自动对数据进行校验
防止不小心构造出一些不合理的数据

如果update 不指定任何条件，相当于条件永远为true，所有数据都会被修改!!

 update exam_result set chinese = 80;

因此，修改操作也是一个危险操作，一定要确保你指定的条件是合理的，正确的。
否则就可能不小心被其他的数据影响

4. 删除（Delete）

语法：

DELETE FROM  表名 where 条件;

删除都是以 行 为维度进行删除

 一定要确保删除是正确的，因为数据一旦被删除就无法恢复

案例：

-- 删除孙悟空同学的考试成绩
DELETE FROM exam_result WHERE name = '孙悟空';
-- 删除整张表数据
-- 准备测试表
DROP TABLE IF EXISTS for_delete;
CREATE TABLE for_delete (
 id INT,
 name VARCHAR(20)
);
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
-- 删除整表数据
DELETE FROM for_delete;

如果不指定条件，就是删除所有数据

 delete from exam_result;

delete 是删除的表的内容，删完之后表的内容是空着的，表本身是存在的
drop table 是删除整个表内容和表都没了

5. 内容重点总结

• 新增

-- create table student (id int,name varchar(20));
-- insert [into] student  values (值，值);

• 查询

 --通常情况下不建议使用 * 进行全列查询
-- 1. 查询的列越多，意味着需要传输的数据量越大；
-- 2. 可能会影响到索引的使用。（索引待后面课程讲解）
SELECT * FROM 表名;

• 修改

--表名 set 列名 = 值 where 条件；

• 删除

DELETE FROM  表名 where 条件;