MySQL中表的增删查改操作(CRUD)

向右看齐 2023-09-25 12:48 145阅读 0赞

![在这里插入图片描述][3f9026f5c260455c8ecd6dfef03f83ed.gif_pic_center]✨**博客主页:**[ **心荣~**][Link 1]  
✨**系列专栏:**[**【MySQL】**][MySQL]  
✨**一句短话:** **难在坚持,贵在坚持,成在坚持!**

#### 文章目录 ####

*  一. 新增
 *   *  1. 全列插入
     *  2. 指定列插入
     *  3. 效率问题
 *  二. 查询(Retrieve)
 *   *  1. 全列查询(整表查询)
     *  2. 指定列查询
     *  3. 指定字段表达式运算查询
     *  4. 将表达式或字段指定别名查询
     *  5. 去重查询
     *  6. 排序查询
     *  7. 条件查询
     *  8. 分页查询
 *  三. 修改(Update)
 *  四. 删除(Delete)

所谓的CRUD即增加(Create), 查询(Retrieve), 更新(Update), 删除(Deleete)四种操作.

这里在开头补充一下sql当中的注释可以使用"–空格+描述"来表示注释说明.

## 一. 新增 ##

我们这里先创建一张学生表来演示对表的相关操作.

-- 创建一张学生表
    create database student
    (
        id int,
        sn int, -- 学号
        name varchar(20), -- 姓名
        mail varchar(20) -- qq邮箱
    );

mysql中通过`insert`关键字来插入(新增)记录.

### 1. 全列插入 ###

**语法**:

insert into 表名 values(对应列的实参列表); -- 一次插入一行
    insert into 表名 values(对应列的实参列表), (对应列的实参列表).... -- 一次插入多行

**注意**:

values 后面( )中的内容, 个数和类型要和表的结构匹配.

**示例**:

-- 单行插入
    insert into student values(101, 10001, '喜羊羊', '12345');
    -- 多行插入
    insert into student values
    (102, 10002, '美羊羊', '23456'), 
    (103, 10003, '懒羊羊', '34567'),
    (104, 10004, '沸羊羊', '45678');

**插入结果**:

![img][]

有关日期数据的插入, 尽量使用`datetime`, 日期的插入格式为 : `yyyy-mm-dd hh:mm:ss`, 其中now()能够获取当前日期。

create table times (id int, time datetime);
    insert into times values (1, '2022-08-12 13:14:00'), (2, now());

![img][img 1]

### 2. 指定列插入 ###

**语法**:

insert into 表名 (需要插入的列) values(对应列的实参列表); -- 一次插入一行
    insert into 表名 (需要插入的列)values(对应列的实参列表), (), ().... -- 一次插入多行

**注意**:

*  values 后面( )中的内容, 个数和类型要和表名后面( )中指定的结构匹配.
 *  未被指定的列会以默认值进行填充.

**示例**:

-- 单行插入
    insert into student (id, name) values(105, '暖羊羊');
    --多行插入
    insert into student (id, name) values
    (106, '慢羊羊'), (107, '灰太狼'), (108, '红太狼');

**插入结果**:

![img][img 2]

### 3. 效率问题 ###

在mysql当中, 多条记录逐次插入的效率是要低于一次把多条纪录一起插入的.

具体受如下方面的影响:

1.  网络请求和响应的时间开销引起, 多次请求和响应的时间开销更大.
2.  数据库服务器是把数据保存在硬盘上的, 在进行lO操作的时候,数据量会对效率有影响, 但是影响一般不会很大, 更大的则是IO操作的次数.
3.  mysql关系型数据库, 每次进行一个sql操作, 内部都会开启一个事务, 每次开启事务也有一定的时间开销.

补充:内存和外存的区别.

*  内存上读写数据的速度快 ,外存的读写速度慢(速度能差3-4个数量级,也就是几千倍,甚至上万倍).
 *  内存空间比较小,外存空间比较大(比如电脑配置内存16GB,外存是512GB).
 *  内存比外存贵.
 *  内存的数据"易失", 断电之后, 数据就会丢失; 外存的数据是 “持久” 的,断电之后,数据还在.

## 二. 查询(Retrieve) ##

mysql中通过`select`关键字来完成查询操作.

### 1. 全列查询(整表查询) ###

**语法**:

select * from 表名;

**注意**:

*  上面的 `*` 表示通配符, 可以匹配表中的所有列.
 *  这个操作要慎重使用, 在企业级别的数据库中, 数据量是非常大的(TB级别), 查询操作会遍历所有的数据,把数据从硬盘上读出来通过网卡来发送到客户端上, 同时硬盘读写也是有限的, 这样的操作, 如果数据量非常大, 就容易把 IO 吃满, 或者把网络宽带吃满; 此时如果有外面的用户客户端要通过宽带访问服务器时, 服务器就无法及时做出响应.

**示例**:

查询上面创建的学生表和日期表.

select * from student;
    select * from times;

**查询结果**:

![img][img 3]

注意: 这里查询结果所显示的表是服务器端数据库返回给客户端的一个临时表，使用查询操作不会对服务器中的数据造成影响, 该临时表在客户端打印后就销毁了.

### 2. 指定列查询 ###

**语法**:

select 列名, 列名, ... from 表名;

**示例**:

查询学生表中的姓名和邮箱,

select name, mail form student;

**插入结果**:

![img][img 4]

### 3. 指定字段表达式运算查询 ###

这里再创建一张考试成绩表如下:

-- 创建考试成绩表
    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.5, 90),
    (4, '沸羊羊', 82, 84, 67),
    (5, '暖羊羊', 55.5, 85, 45),
    (6, '黑大帅', 70, 73, 78.5),
    (7, '潇洒哥', null, 75, 65);

**语法**:

select 字段或表达式, 字段或表达式... from 表名;

**注意**:

这里的表达式运算是同一行中不同列之间的运算, 是不可以跨行进行运算的.

**示例**:

查询将语文成绩和数学成绩在原来的基础上减少10分的结果,

select * from exam_result;
    select name, chinese-10, math-10 from exam_result;

**查询结果**:

![img][img 5]

### 4. 将表达式或字段指定别名查询 ###

上面的查询结果中显示的表头是一个表达式, 但如果这一个表达式很长时就不适合作为表头显示了, mysql中支持在查询时给表达式起一个别名, 让查询结果直接显示别名, 查询结果更加直观.

**语法**:

select 列名或表达式 as 别名, ... from 表名;

**注意**:

上面的`as`可以省略, 但是不建议省略, 省略以后代码的可读性变差, 而且容易出错;

**示例**:

查询成绩表中每个人的总成绩和平均成绩.

select name, chinese+math+english as total, 
    (chinese+math+english)/4 as average from exam_result;

**查询结果**:

![img][img 6]

注意 :

*  有null参与的运算最终的结果还是为null.
 *  每一个列或者表达式的别名只在当前字段有效, 如果运用到下一个字段就会报错![img][img 7]

### 5. 去重查询 ###

mysql支持对列的去重查询, 重复的数据只会输出一个, 用关键字`distinct`来表示去重.

**语法**:

select distinct 列名 from 表名;

**注意**:

*  去重查询只支持单列进行去重, 如果一次查询多列就会失去去重效果.

**示例**:

先插入三个人的成绩, 使三人的英语成绩相同, 然后对英语成绩进行去重查询.

nsert into exam_result values 
    (8, '灰太郎', 55, 65, 88),
    (9, '红太狼', 85.5, 78, 88),
    (10, '小白狼', 78, 88, 88);
    select english from exam_result;
    select distinct english from exam_result;

**查询结果**:

![img][img 8]

看查询结果英语成绩中有三个88, 最后只显示输出了一个88, 达到了去重效果.

### 6. 排序查询 ###

将要查寻的数据排序得到临时表输出, 排序相关的关键字`order by`.

**语法**:

select 列名, ... from 表名 order by 列名(要排序的列) asc(或者desc), ...;

**注意**:

*  上面末尾的asc表示升序, 升序查询时asc可以省略; desc表示降序查询.
 *  如果查询的数据当中存在 `null`, 那么`null`默认是最小的.
 *  当排序指定多个列时, 不是去将每列单独进行排序, 指定多个列属于复杂规则的比较, 比如指定的两个列, 先以第一个列的比较规则为准, 如果第一列的规则比较不出结果, 再去按第二列的规则去比较, 多个列以此类推.

**示例**:

*  查询上面成绩的总分(降序).

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

**查询结果**:

![img][img 9]

*  指定英语(降序)和数学(升序)两个列进行排序查询.

select name, english, math from exam_result order by english desc, math asc;

**查询结果**:![img][img 10]

### 7. 条件查询 ###

条件查询就是通过设定的条件, 将满足条件的记录查询显示, mysql中通过关键字`where` \+ 条件来设置查询条件.

**语法**:

select 列名, ... from 表名 where 条件;

**要正确的设置条件是离不开运算符的, 下面列出mysql中常用的运算符**.

**比较运算符**:

<table> 
 <thead> 
  <tr> 
   <th><strong>运算符</strong></th> 
   <th><strong>说明</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&gt;, &gt;=, &lt;, &lt;=</td> 
   <td>大于，大于等于，小于，小于等于</td> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>等于，null 不安全，例如 null = null 的结果是 null(false)</td> 
  </tr> 
  <tr> 
   <td>&lt;=&gt;</td> 
   <td>等于，null 安全，例如 null &lt;=&gt; null 的结果是 true(1)</td> 
  </tr> 
  <tr> 
   <td>!=, &lt;&gt;</td> 
   <td>不等于</td> 
  </tr> 
  <tr> 
   <td>between a0 and a1</td> 
   <td>范围匹配，[a0, a1]，如果 a0 &lt;= value &lt;= a1，返回 true(1)</td> 
  </tr> 
  <tr> 
   <td>in (option, …)</td> 
   <td>如果是 option 中的任意一个，返回 true(1)</td> 
  </tr> 
  <tr> 
   <td>is null</td> 
   <td>是 null</td> 
  </tr> 
  <tr> 
   <td>is not null</td> 
   <td>不是 null</td> 
  </tr> 
  <tr> 
   <td>lisk</td> 
   <td>模糊匹配; % 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符</td> 
  </tr> 
 </tbody> 
</table>

**逻辑运算符**:

<table> 
 <thead> 
  <tr> 
   <th><strong>运算符</strong></th> 
   <th><strong>说明</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>and</td> 
   <td>多个条件必须都为 true(1)，结果才是 true(1)</td> 
  </tr> 
  <tr> 
   <td>or</td> 
   <td>任意一个条件为 true(1), 结果为 true(1)</td> 
  </tr> 
  <tr> 
   <td>not</td> 
   <td>条件为 true(1)，结果为 false(0)</td> 
  </tr> 
 </tbody> 
</table>

**注意:**

1.  where条件可以使用表达式，但不能使用别名。
2.  and的优先级高于or，在同时使用时，需要使用小括号()包裹优先执行的部分

**示例**:

*  **>=…**

查询英语成绩不及格的同学及英语成绩(小于60)

查询语文成绩好于英语成绩的同学

查询总分在 200 分以下的同学

-- 查询英语不及格的同学及英语成绩 ( < 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 as total from exam_result
    where chinese+math+english < 200;

![img][img 11]

*  **and 和 or**

查询语文成绩大于80分，且英语成绩大于80分的同学

查询语文成绩大于80分，或英语成绩大于80分的同学

查询语文成绩和数学成绩要有一门大于80且英语成绩要大于70的同学

-- 查询语文成绩大于80分，且英语成绩大于80分的同学
    select * from exam_result where chinese > 80 and english > 80;
    -- 查询语文成绩大于80分，或英语成绩大于80分的同学
    select * from exam_result where chinese > 80 or english > 80;
    -- 查询成绩语文成绩和数学成绩要有一门大于80且英语成绩要大于70的同学
    select * from exam_result where 
    (chinese > 80 or math > 80) and english > 70;

![img][img 12]

*  **范围查询**( **between and, in( )** );

查询语文成绩在 \[80, 90\] 分的同学及语文成绩

查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

-- 查询语文成绩在 [80, 90] 分的同学及语文成绩
    select name, chinese from exam_result where chinese between 80 and 90;
    -- 也可以使用and实现
    select name, chainese from exam_result where chainese >= 80 and chainese <= 90;
    
    -- 查询数学成绩是 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, math = 99;

![img][img 13]

*  **模糊查询(like)**

查找名字含有羊的同学

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

![img][img 14]

查找姓名最后一个字为狼的同学

select * from exam_result where name like '%狼';
    select * from exam_result where name like '__狼';

![img][img 15]

注意 :`%`可通配多个字符, 而`_`只能通配一个字符.

*  **null的查询(is null / is not null)**

查询语文成绩为null的同学

查询语文成绩不为null的同学

-- 查询语文成绩为null的同学
    select name, chinese from exam_result where chinese is null;
    -- 查询语文成绩不为null的同学
    select name, chinese from exam_result where chinese is not null;

![img][img 16]

判断是否为null也可以使用`<=>null`, 这里要注意`<=>null`和`is null`的区别,

`is null`要求只能比较一个列是否为空, 而`<=>`可以直接比较两个列.

### 8. 分页查询 ###

现实中分页查询最常见的例子就是浏览器搜索了, 浏览器搜索不会一次性将搜索到的结果都加载显示出来, 而是像下图中将搜索结果按页进行加载呈现, 这种搜索模式就是分页查询;

![img][img 17]

mysql中也支持分页查询, 使用关键字`limit`和`offset`来实现.

**语法**:

select 列名, ... from 表名 limit 限制个数 offset 起始下标;
    select 列名, ... from 表名 limit 限制个数, 起始下标;

**注意**:

*  分页查询本质上就是限制查询结果的条数，你可以设置最多输出几条结果，同时也可以指定从哪一条结果开始查询.
 *  分页查询也可以搭配排序和条件等其他语句使用.
 *  上面的offset可以省略, 但不建议省略, 会使代码的可读性变差, 且省略后代码容易出错.

**示例**:

*  以分页的方式上面的成绩表, 每页最多查询三条记录.

-- 第一页
    select * from exam_result limit 3 offset 0;
    -- 第二页
    select * from exam_result limit 3 offset 3;
    -- 第三页
    select * from exam_result limit 3 offset 6;
    -- 剩下的记录
    select * from exam_result limit 3 offset 9;

![img][img 18]

*  查询语文成绩前三名的同学.

select * from exam_result order by chinese desc limit 3;

![img][img 19]

## 三. 修改(Update) ##

mysql中使用关键字`updata`来表示修改.

**语法**:

update 表名 set 列名 = 值, 列名 = 值, ... where 限制条件下修改;

**注意**:

*  如果不加限制条件修改的就是列中的所有记录.
 *  除了搭配 where 外, 也可以搭配order 和 limit 使用

**示例**:

*  将喜羊羊的英语成绩修改为99.

select * from exam_result where name = '喜羊羊';
    update exam_result set english = 99 where name = '喜羊羊';
    select * from exam_result where name = '喜羊羊';

![img][img 20]

*  将总成绩在前三名的同学的语文成绩再加上10分.

select * from exam_result order by chinese+math+english desc limit 3;
    update exam_result set chinese = chinese+10 order by chinese+math+english desc limit 3;
    select * from exam_result order by chinese+math+english desc limit 3;

![img][img 21]

## 四. 删除(Delete) ##

mysql中使用关键字`delete`来表示删除.

语法:

delete from 表名 where 限制条件下删除;

注意:

*  如果不加限制条件删除的就是表中的所有记录.
 *  除了搭配 where 外, 也可以搭配order 和 limit 使用

示例:

删除潇洒哥的成绩

select * from exam_result;
    delete from exam_result where name = "潇洒哥";
    select * from exam_result;

![img][img 22]

[3f9026f5c260455c8ecd6dfef03f83ed.gif_pic_center]: https://img-blog.csdnimg.cn/3f9026f5c260455c8ecd6dfef03f83ed.gif#pic_center
[Link 1]: https://blog.csdn.net/Trong_?spm=1000.2115.3001.5343
[MySQL]: https://blog.csdn.net/trong_/category_12083042.html?spm=1001.2014.3001.5482
[img]: https://img-blog.csdnimg.cn/img_convert/83d02a8a81758dfe88e3feb25936e64b.png
[img 1]: https://img-blog.csdnimg.cn/img_convert/f1557f836bb0054f08912ca293a2e974.png
[img 2]: https://img-blog.csdnimg.cn/img_convert/d44bdfe90d2766dcd24805aa8d223155.png
[img 3]: https://img-blog.csdnimg.cn/img_convert/88ed72cdece149411729a87f94303281.png
[img 4]: https://img-blog.csdnimg.cn/img_convert/636d7e46d5a63bad3cc4324bd7cd1412.png
[img 5]: https://img-blog.csdnimg.cn/img_convert/9321ba130a6350731217b620dfc4f293.png
[img 6]: https://img-blog.csdnimg.cn/img_convert/046f6ccfbd32d0b7ef18a0364000bed0.png
[img 7]: https://img-blog.csdnimg.cn/img_convert/f4743de18f2c7fa65210f897c432df22.png
[img 8]: https://img-blog.csdnimg.cn/img_convert/20717eb34503a77ad881a0702f6d7acd.png
[img 9]: https://img-blog.csdnimg.cn/img_convert/7467243c2022d54ff4400d5a7ab63c78.png
[img 10]: https://img-blog.csdnimg.cn/img_convert/e3ebc65862d1a8672a02f6df8b2b6c0c.png
[img 11]: https://img-blog.csdnimg.cn/img_convert/36340bbec914c92bc3372eb18fabae69.png
[img 12]: https://img-blog.csdnimg.cn/img_convert/010667146cb25e0819bb13bd11d021fa.png
[img 13]: https://img-blog.csdnimg.cn/img_convert/db13616535f75a40a9a3803008c44ef7.png
[img 14]: https://img-blog.csdnimg.cn/img_convert/40649365cbaf75f596439a653ca6dcf5.png
[img 15]: https://img-blog.csdnimg.cn/img_convert/c292d71fb4c4ada78b3e4b0321dbd61d.png
[img 16]: https://img-blog.csdnimg.cn/img_convert/2a069301e07b993186fc7db880eec4ec.png
[img 17]: https://img-blog.csdnimg.cn/img_convert/0822e9d4c2837b742554f6d2fdfa0ff4.png
[img 18]: https://img-blog.csdnimg.cn/img_convert/7433810593a1eee92b3f0d075215ed27.png
[img 19]: https://img-blog.csdnimg.cn/img_convert/f78a831b88a84694aaf80f81a553ec98.png
[img 20]: https://img-blog.csdnimg.cn/img_convert/464bdeb8e238c6dfd29fd8ba79a589b0.png
[img 21]: https://img-blog.csdnimg.cn/img_convert/0c8bb8716e6d8fd1859254036429066c.png
[img 22]: https://img-blog.csdnimg.cn/img_convert/df147294bdd6fee5c9998a204f48e747.png