【MySQL探索之旅】MySQL数据表的增删查改（初阶）

📚博客主页：爱敲代码的小杨.

✨专栏：《Java SE语法》 | 《数据结构与算法》 | 《C生万物》 |《MySQL探索之旅》

❤️感谢大家点赞👍🏻收藏⭐评论✍🏻，您的三连就是我持续更新的动力❤️

🙏小杨水平有限，欢迎各位大佬指点，相互学习进步！

0. 前言

前面我们学习了MySQL数据库和数据表的创建、删除。接下来我们就需要将数据保存在数据表中。今天我们就来学习数据表的增删查改（CRUD）。CRUD即增加（Create），查询（Retrieve），更新（Update），删除（Delete）四个单词的首字母缩写。

1. 新增（Create）

一旦创建数据库和数据表，下一步就是向数据表中添加数据。MySQL 通过 insert 语句来添加一条或多条数据。

语法格式：

insert into 表名 values (表达式, ...., 表达式);

示例：创建学生表

-- 创建学生表 id 
create table student(id int, name varchar(20));

1.1 单行数据+全列插入

-- 插入两条记录，表达式数量必须和定义表的列的数量及顺序一致
insert into student values(1,'张三');
insert into student values(2,'李四');

1.2 多行数据+全列插入

-- 同时插入两条记录
insert into student values(4, '王五'),(5,'赵六');

【注意】：一次插入多条数据，效率往往比多次插入一条数据要高。

1.3 指定列插入

-- 表达式 数量必须和指定列数量及顺序一致
insert into student(name) values('王麻子')

2. 查询（Retrieve）

MySQL 通过 select 语句来查询数据，查询结果通常会生成一个临时表。

语法格式：

select 列名 from 表名;

示例：

-- 创建成绩表 学好 姓名 语文成绩 数学成绩 英语成绩
create table result(id int, name varchar(20), chinese decimal(3,1), math decimal(3,1), english decimal(3,1));

-- 插入数据
insert into result values (1,'张三',67, 98, 56);
insert into result values (2,'孙悟空', 87.5, 78, 77);
insert into result values (3,'李四',90,56.5,78);
insert into result values (4,'王五',90,98,78);

2.1 全列查询

-- 通常情况下不建议使用 * 进行全列查询
-- 查询的列越多，意味着需要传输的数据量越大，可能导致数据库崩溃
select * from result;

2.2 指定列查询

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

2.3 表达式查询

-- 表达式不包含表达式
select id,name,10 from result;

-- 表达式包含一个字段
select id,name,chinese+10 from result;

-- 表达式包含多个字段
select id,name,chinese+math+english from result;

2.4 别名

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

select 表达式 as 别名 from 表名;

-- 表中列名=别名
select id,name,chinese+math+english as 总分 from result;

2.5 去重

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

-- 数学成绩98重复了
select math from result;
select distinct math from result;

2.6 排序

语法：

select 列名 from 表名 order by 列名 [asc | desc];

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

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

   select id,name from result order by name;
   select id,name from result order by name desc;
   

   

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

   select id,name,chinese+math+english as 总分 from result order by 总分;
   select id,name,chinese+math+english as 总分 from result order by 总分 desc;
   

   

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

   -- 查询所有同学的成绩按数学升序、语文降序排序
   select * from result order by math,chinese desc;
   

   

2.7 条件查询

学习条件语法之间我们先学习条件查询需要用到的运算符：比较运算符和逻辑运算符

比较运算符：

运算符	说明
> < >= <=	大于、小于、大于等于、大于等于
=	等于 NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!= <>	不等于
between a1 and a2	范围匹配，[a1 a2]，如果 a1<= value <= a2，返回 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 条件可以使用表达式，但是不能使用as 别名

   SQL 的执行顺序

   1. 遍历表
   2. 代入条件
   3. 计算列名中的表达式（定义别名）
   4. 排序/聚合等操作

   示例：

   

   语句1中：遍历表的时候没有总成绩这个列名直接报错。

   语句2则正确执行。

2. and 的优先级高于or，在同时使用的，需要使用小括号()括起来优先执行的部分。

示例：

• 基础查询

  -- 查询语文成绩大于80分的同学
  select * from result where chinese > 80;
  -- 查询数学成绩小于英语成绩的同学
  select * from result where math < english;
  -- 查询总成绩大于230的同学
  select id,name,chinese+math+english as 总成绩 from result where chinese+math+english < 230;
  

  

• and 和 or

  -- 查询语文成绩大于80数学成绩小于80的同学
  select * from result where chinese>80 and math<80;
  -- 查询语文成绩大于90或者数学成绩大于80
  select * from result where chinese>90 or math>80;
  

• 范围查询

  1. between...and...

     -- 查询语文在[80,90]之间的同学
     select * from result where chinese between 80 and 90;
     
     -- 替换成 and 实现
     select * from result where chinese >= 80 and chinese <= 90;
     

     

  2. in

     -- 查询数学成绩是58 或者 68 或者 78 或者 88 或者 98的同学
     select * from result where math in(58,68,78,88,98);
     -- 替换成 or 实现
     select * from result where math=58 or math=68 or math 78 or math=88 or math=98;
     

     

• 模糊查询：like

  select * from result where name like "张%";
  select * from result where name like "%张";
  select * from result where name like "_张";
  select * from result where name like "张_";
  

  

• null 查询

  -- 查询名字为null的同学
  select * from result where name is null;
  -- 查询名字不为null的同学
  select * from result where name is not null;
  

  

2.8 分页查询

语法：

-- 起始下标为0
-- 从0开始筛选n条记录
select 列名 from 表名 [where] [order by] limit n;
-- 从s开始筛选n条记录
select 列名 from 表名 [where] [order by] limit s,n;
-- 从s开始筛选n条记录 建议使用第二种
select 列名 from 表名 [where] [order by] limit n offset s;

案例：

-- 查询表的前3条记录
select * from result order by id limit 3;
-- 查询表的3到6条记录
select * from result order by id limit 3,3;
select * from result order by id limit 3 offset 3;

3. 修改（Update）

语法：

update 表名 set 列名 = 值 where 表达式;

示例：

-- 将张三的数学成绩修改80
update result set math = 80 where name = '张三';
-- 将王五的数学成绩修改90，语文修改95
update result set math = 90,Chinese = 95 where name = '王五';

注意: 如果缺少 where 条件则修改表中的所有记录.

4. 删除（Delete）

语法：

delete from 表名 [where] [order by] [limit];

示例：

-- 删除张三同学这条记录
delete from result where name = '张三';
-- 删除整张表
 delete from result;