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

✨博客主页: 心荣~
✨系列专栏:【MySQL】
✨一句短话: 难在坚持,贵在坚持,成在坚持!

文章目录

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

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

一. 新增

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

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

mysql中通过

insert

关键字来插入(新增)记录.

1. 全列插入

语法:

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

注意:

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

示例:

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

插入结果:

有关日期数据的插入, 尽量使用

datetime

, 日期的插入格式为 :

yyyy-mm-dd hh:mm:ss

, 其中now()能够获取当前日期。

createtable times (id int,timedatetime);insertinto times values(1,'2022-08-12 13:14:00'),(2,now());

2. 指定列插入

语法:

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

注意:

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

示例:

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

插入结果:

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;

查询结果:

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

2. 指定列查询

语法:

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

示例:

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

select name, mail form student;

插入结果:

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

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

-- 创建考试成绩表createtable exam_result (
    id int, 
    name varchar(20),-- 姓名
    chinese decimal(3,1),-- 语文成绩
    math decimal(3,1),-- 数学成绩
    english decimal(3,1)-- 英语成绩);-- 插入数据insertinto 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-10from exam_result;

查询结果:

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

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

语法:

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

注意:

上面的

as

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

示例:

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

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

查询结果:

注意 :

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

5. 去重查询

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

distinct

来表示去重.

语法:

selectdistinct 列名 from 表名;

注意:

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

示例:

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

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

查询结果:

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

6. 排序查询

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

order by

.

语法:

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

注意:

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

示例:

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

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

查询结果:

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

select name, english, math from exam_result orderby english desc, math asc;

查询结果:

7. 条件查询

条件查询就是通过设定的条件, 将满足条件的记录查询显示, mysql中通过关键字

where

• 条件来设置查询条件.

语法:

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

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

比较运算符:
运算符****说明>, >=, <, <=大于，大于等于，小于，小于等于=等于，null 不安全，例如 null = null 的结果是 null(false)<=>等于，null 安全，例如 null <=> null 的结果是 true(1)!=, <>不等于between a0 and a1范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 true(1)in (option, …)如果是 option 中的任意一个，返回 true(1)is null是 nullis not null不是 nulllisk模糊匹配; % 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符
逻辑运算符:
运算符****说明and多个条件必须都为 true(1)，结果才是 true(1)or任意一个条件为 true(1), 结果为 true(1)not条件为 true(1)，结果为 false(0)
注意:

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;

• and 和 or

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

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

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

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

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

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

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

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

• 模糊查询(like)

查找名字含有羊的同学

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

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

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

注意 :

%

可通配多个字符, 而

_

只能通配一个字符.

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

查询语文成绩为null的同学

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

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

判断是否为null也可以使用

<=>null

, 这里要注意

<=>null

和

is null

的区别,

is null

要求只能比较一个列是否为空, 而

<=>

可以直接比较两个列.

8. 分页查询

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

mysql中也支持分页查询, 使用关键字

limit

和

offset

来实现.

语法:

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

注意:

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

示例:

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

-- 第一页select*from exam_result limit3offset0;-- 第二页select*from exam_result limit3offset3;-- 第三页select*from exam_result limit3offset6;-- 剩下的记录select*from exam_result limit3offset9;

• 查询英语成绩前三名的同学.

select*from exam_result orderby chinese desclimit3;

三. 修改(Update)

mysql中使用关键字

updata

来表示修改.

语法:

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

注意:

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

示例:

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

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

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

select*from exam_result orderby chinese+math+english desclimit3;update exam_result set chinese = chinese+10orderby chinese+math+english desclimit3;select*from exam_result orderby chinese+math+english desclimit3;

四. 删除(Delete)

mysql中使用关键字

delete

来表示删除.

语法:

deletefrom 表名 where 限制条件下删除;

注意:

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

示例:

删除潇洒哥的成绩

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