MySQL表的增删查改

每天都要进步呀

发布于 2023-10-16 11:16:46

2400

发布于 2023-10-16 11:16:46

文章被收录于专栏：C++/LinuxC++/Linux

MySQL基本查询

表的增删改查：CRUD

Create（创建）包含insert
Retrieve（读取）包含select
Update（更新）
Delete（删除）

一.增加数据Create

主要是insert。

创建一个学生表：

1.单行数据

单行数据指定列插入：

values左侧为表中属性，右侧为自定义插入的内容，左右两侧安装顺序是一一对应的，如果顺序不同就会导致类型不同而出错。

单行数据全列插入：

将values左侧的属性列全部省略，那么所有属性对应的值都必须插入。

注：into可以省略。

2.多行数据

若想一次插入一批数据，则只需把上面的;替换成,然后继续（数据），最后再分号结束。

多行数据指定列插入：

多行数据全列插入：

3.插入是否更新

由于主键或者 唯一键 对应的值已经存在而导致插入失败。

若插入的数据发生主键或者唯一键冲突，就会插入失败，但是我们仍然希望将主键和唯一键之外的的值进行特定的修改，这就用到了 on duplicate key ，即不存在则插入，存在则修改：

其中：

0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等 1 row affected: 表中没有冲突数据，数据被插入 2 row affected: 表中有冲突数据，并且数据已经被更新

此外，通过 MySQL 函数获取受到影响的数据行数

4.替换

还有一种方式能够替代上面的on duplicate key，用replace替换insert同样可以使数据：不存在则插入，存在则修改。

二.查询数据Retrieve

主要是select。（使用频率最高的语句）

表中结构以及插入信息如下。

下面用各种方式进行基本查询。

1.全列查询

全列查询需要 * 通配符完成。

2.指定列查询

指定列查询仍是将表中所有的列拿出来的，只不过我们所筛选的属性并不是全部的数据。

如下语句，都为筛选指定字段的数据：

3.查询字段为表达式

在MySQL数据类型的介绍中，select可以执行相应的各种表达式，函数。而在select的查询中，也可以将这些属性与查询字段拼凑一起。

比如，可以将常数10与特定查询的字段一起显示，这个10也可以换成表达式（1+1）：

既然可以查1+1，那么一定也可以查询math+chinese+english：

这样，查询结果中的字段就存在math+chinese+english字段，当然可以用as将这个字段重命名成一个简短的名字total：

甚至as可以省略，这样一来，在查询中，我们可以任意将表字段名在查询结果中重新显示成我们想要的字段名，即表中字段的别名：

查询指定字段中，里面的数据避免不了出现相同的情况，若指向让相同的数据出现一次，则用distinct去重：

需要注意的是null不参与查询。

4.where子句查询

之前的筛选是对表中数据的整体做筛选，即字段筛选，而同一个字段根据数值的不同，通过不同的筛选条件的不同，得到的结果自然也就不同，比如对于分数，想查询60分以上的，那就需要通过where子句的条件来进行筛选。

where筛选影响的是未来查询结果的行数。

where子句中的各种运算符

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 个）任意字符；_ 表示任意一个字符

通过select + 表达式的方式可以一一验证。

逻辑运算符：

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

案例演示：按照条件进行筛选

案例1：英语不及格的同学及英语成绩 ( < 60 )

案例2：语文成绩在 [80, 90] 分的同学及语文成绩

方式一：and连接条件

方式二：BETWEEN a0 AND a1

案例3：数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

方式一：使用 OR 进行条件连接

方式二：使用 IN 条件

案例4：姓孙的同学及孙某同学

类似于这种很模糊的条件，选择like比较运算符进行筛选。

like ‘孙%’：后面任意多个字符。

like ‘孙_’：后面一个字符。

案例5：语文成绩好于英语成绩的同学

案例6：总分在200分以下的同学

为了方便，这个时候就可以对列进行重命名了，但事实上没有执行成功：

查找语句的执行顺序：

先执行from找到对应表
然后where，在表中拿着条件做筛选
最后select显示

所以，因为在执行where子句时还没有重命名，故也就找不到对应的字段。此外，也不能在筛选条件where中做重命名。故不能缩短语句，只能通过重命名的方式让数据在显示时将字段缩短。

案例7：语文成绩 > 80 并且不姓孙的同学

策略：and与not连用

案例8：孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

这是一个综合案例

分成两种情况：

孙某同学(名字任意字数)
总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

这两种情况满足其一即可，因此用or连接这两个大条件，通过括号将后面的整体括起来。

案例9：NULL的查询

exam_result表没有空置，所以新建一个表：

查找name=null的人：

查找name不为空的人：

可见，空串不为null。

5.结果排序

查询得到的结果，都是按照一行一行排列的，这时候，就可以根据指定的字段将查询到的结果进行排序。

语法：

ASC 为升序（从小到大）默认为ASC
DESC 为降序（从大到小）

对此表进行结果排序

1. 同学及数学成绩，按数学成绩升序显示

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

降序也就出现在最下面，不进行演示了。

3. 查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

其中，asc可以省略，因为默认是升序。

4. 查询同学及总分，由高到低

不过，这里却可以使用别名，即如下方式也可以：

为什么这里又能使用别名了？排序的原则是先要有合适的数据，在排序。因为这种策略可以最大化提高速度。所以排序的动作是在拿出数据之后的，因此我们select起别名之后就能使用别名了。所以能不能使用别名完全取决于子句的执行顺序。

5. 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

根据上面的分析，执行顺序一定是先筛选满足条件的同学，然后再将结果排序。所以思想上自然的就将这两个需求肢解，先筛，再排。

6.筛选分页结果

limit可以限制查询到的数据记录。有三种方式：

方式一：limit n 表示显示前n条数据。

方式二：也可以在一个指定的范围进行筛选：limit m,n表示显示从第m+1条开始显示，显示n条数据。

方式三：limit n offset m;表示从第m条开始，筛选n条结果。m从0算起。

只有数据准备好了，才能显示，limit本质的功能就是“显示”，因此limit执行顺序更靠后（比排序靠后）

对未知表进行查询时，limit能够避免因为表中数据过大，查询全表数据导致数据库卡死

三.更新数据update

一般update会和where子句配合使用，即筛选特定的数据将其进行修改，一旦不用where子句配合筛选，则整个表中的数据都将会被修改。

语法：

案例1：将孙悟空同学的数学成绩变更为 80 分

案例2：将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

和上一个相比，这个大原则不变，不过这次同时修改两个数据。

案例3：将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

与前两个不同的是，这个需要order by进行配合。也就是说，分成先查找满足条件的order by，通过limit筛选总成绩倒数前三名，再进行update。

不支持math += 30这种语法。

案例4：将所有同学的语文成绩更新为原来的 2 倍

这个更新的范围就是全表，没有筛选条件

不支持chinese *= 2这种语法。

注意：更新全表的语句慎用

四.删除数据Delete

删除数据是以行为单位的删除，如果删除时不用where子句筛选特定行数据，那么表中的所有数据都会被删除。

语法：

案例1：删除孙悟空同学的考试成绩

案例2：删除总分倒数第一名同学的考试成绩

案例3：删除整张表数据

表内容被删，结构不会被删，结构删用的是drop

方式一：delete from表名

需要注意的是，在这里如果有auto_increment字段，删除数据之后，通过语句：show create table for_delete\G;得到的结果中，其中的auto_increment仍为4，计数器值不会随着表中数据的清除而改变。当插入数据时，会随着此值继续自增。

方式二：截断表

语法：

table可省略。与方式一的区别：

只能对整表操作，不能像 DELETE 一样针对部分数据操作。
实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚。
会重置 AUTO_INCREMENT 项

扩展内容

比如bin log, redo log, undo log，这些都是sql溯源之前操作所需要的日志，即为事务。平时的各种sql优化好的语句都会将记录保存在bin log（持久化，保存在文件中），redo log中；undo log是用来做事务回滚的，以及事务的隔离性。数据库一旦出现宕机，redo log可以将数据恢复过来。

持久化方式：

记录历史SQL语句
记录数据本身。bin log可以做到主从同步（两个数据库）

所以，truncate不把自己的操作包装成事务，它的操作也不会记录在这些日志里，只是单纯的清空，所以truncate操作一定更快。

五.去重表数据

案例：删除表中的的重复复记录，重复的数据只能有一份

distinct只是在select显示时的去重，不会影响原表中的数据。而现在要的是将表中数据的去重。

去重表数据分三步：

首先，创建另一个表，此表与需要去重的表的结构完全一致。
然后通过select distinct * from 表名，在显示时将重复的去重，再与insert组合，将显示的去重的数据插入到新表中。
最后，将原表重命名进行备份或者直接删掉，新表命名为原表的名字，此新表中的数据就是原表去重后的数据。

实例演示：

原表数据如下：

步骤1.创建新表，命名为no_duplicate_table，结构和duplicate_table相同。

步骤2.select distinct与insert组合，将duplicate_table表中数据筛选去重并插入到no_duplicate_table中。

步骤3.重命名原表与新表，原表备份，新表改名为原表同名

这就完成了去重表数据。

为什么最后是通过rename方式进行的？

通过rename方式，就是单纯的想等一切都就绪了，然后一起放入、更新、生效的。

六.聚合函数

1.聚合函数汇总

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义