MySQL——表内容的CURD操作

MySQL表内容的CURD

​ 与表内容相关的操作共有四种类型：Create(创建)，Retrieve(读取)，Update(更新)，Delete(删除)；

一、Create

1.1单行插入+多行插入+全列插入/指定列插入

#全列插入，忽略列属性；
#指定插入时要注意列属性要与列属性值相对应；

#单行插入
insert (into) 表名 (列属性) values (对应列属性的值)；#括号表示可以省略

#多行插入
insert (into) 表名 (列属性) values (对应列属性的值)，(对应列属性的值)...；

1.2插入替换

​ 发生了键值(主键或者唯一键)冲突，更新指定列的值；

方式一：

insert ... on duplicate key update 冲突的键 = 对应键的键值, 冲突的键 = 对应键的键值;
#需要注意的是更新的值要与原来的值不冲突；
插入的数据有三种情况，1.插入的数据冲突且更新，2行被影响；2.插入的数据没冲突，1行被影响；3.插入的数据冲突但是不更新，0行被影响；

方式二：主键或者唯一键没有发生冲突则知识进行插入，发生了冲突则是删除后再进行更新替换；

replace into 表名 (属性1，属性2) values (属性对应值1，属性对应值2)；

二、Retrieve

2.1select

​ 可以根据指定的条件进行筛选，也可以按照指定的列来筛选，还可以去重做排序和分组；

​ 指定多项时需要使用’,'进行分割；

语法：
select (distinct)/*表示去重*/ column1，column2 (from 表名) (where 筛选条件) (order by cloumn1)/*根据列进行排序*/ (limit) /*限定筛选出来的结果条数*/ (group by)/*分组*/；

2.1.1select column

select column；

2.1.1.1全列查询

​ 不建议直接使用*来进行查询，否则会有大量的数据进行刷新，大量的数据进行传输；

​ 由于是全列查询，本质上是进行了遍历，所以索引数据是没有用到的；

2.1.1.2指定列进行查询

​ 可以根据输入的列属性顺序，筛选出数据；需要注意的是：先将所有的数据先拿出来，再根据需求将内容呈现出来；

2.1.1.3查询字段为表达式

select (要被执行的表达式，包括自带的各种子句，对应的筛选条件，运算表达式等)
select 属性1，属性2，属性3，属性1+属性2+属性3 as new属性；
select 属性1 new属性，属性2 new属性，属性3 new属性 from table；#as可以忽略；

2.1.1.4去重

select distinct column from 表名；

2.1.2where子句

​ 可以筛选显示出来的行数或者条目数；

比较运算符：返回值是0/1，表示假或者真；

​ > >= < <=，比较整数或者是浮点数；

​ =，判断相等，判断NULL是不安全的；

​ <=>，判断相等，判断NULL是安全的，但是不建议使用；

​ is NULL/is not NULL，判断是否是NULL；

​ != <>，判断不等于，NULL不安全；

​ between a and b，判断是否在区间；

​ in(选项1，选项2)，判断选项是否在集合中；

​ like，模糊匹配，%0或者多个任意字符，_表示任意一个字符；

逻辑运算符：

​ and，逻辑与，大小写忽略，可以写成&&；

​ or，逻辑或，大小写忽略，可以写成||；

​ not，逻辑非，大小写忽略；

SQL语句的执行顺序：

select/*3*/ name 姓名, math+chinese+english 总分 /*1*/from exam_result /*2*/where math+chinese+english<200;/*4*/执行 order by语句；/*5*/执行 limit 分页子句；

​ 先执行from，再执行筛选条件，最后根据列需求筛选出来；起别名在最后一步；

2.1.3order by子句

​ 需要注意的是：NULL值比任何值都小；

asc#表示升序；默认使用的是升序；
desc#表示降序；
#语法格式：
select column 别名 from 表名 where表达式 order by column1 升序或者降序，column2 升序或者降序；

​ 排序子句执行顺序在最后，因为排序是已经将需要的数据筛选了出来，才可以进行排序；

2.1.4limit

select ... limit m,n/*m表示从m+1行开始显示，n表示读取的行数或者条目数*/即下标从0开始；
select ... limit n offset m；
通过修改起始位置实现分页效果；n也表示每一页的大小；

注意：对未知表进行查询时，最好在最后位置加上limit 1，防止大量的数据从磁盘中载入到内存中，导致卡死；

三、Update

​ 对最后能显示的结果进行更新；必须添加筛选条件；

#语法格式：
update 表名 set column = 表达式或者是具体的值，column = 表达式或者是具体的值 where 表达式 order by limit m,n；

四、Delete

4.1删除列

delete from 表名 where... order by... limit...；

4.2删除表

方式一：删除表

delete from 表名；

​ 需要注意的是：不会将自增序列(表外属性)进行修改或者是置零；

方式二：截断表

truncate 表名；

​ 与删除表相比，会将表外属性自增序列进行删除，效果上就是直使得自增序列置零了；

​ 补充知识：

​ 直接就将表中的数据清空了，不走事务，之前的sql语句都会经过事务的包装才会叫给MySQL去执行；

​ MySQL会对操作信息进行记录，使用日志的方式进行记录，包括了bin log (文件级)，redo log(文件级)，undo log(内存级)；将操作保存在了bin log(保存的是历史sql语句，用于进行数据库的数据持久化) 和redo log(便于宕机时，内存中的数据进行恢复)中，便于溯源；undo log是用于事务回滚的；

​ bin log 实现持久化的方式是：1.记录历史SQL语句；2.记录数据本身；使用1方案可以实现主从数据库；默认MySQL的bin log是被关闭的；

五、插入查询结果

5.1去重表数据

​ 1.先创建一个相同结构的表；2.将原表数据插入到新表中；3.重命名新表和旧表；

create table 表名1 like 表名2；#创建一个与表名2结构一样的表名1；
insert into 表名1 select distinct * from 表名2；
rename table 旧表名 to 新表名；

​ 最后一步重命名的原因是重命名的过程是原子的，当前面的准备工作就绪之后，使用重命名生效就可以保证上传成功或者更新文件之前，文件是安全的；所以上传一般使用的方式是先生成一个临时文件，然后临时文件上传成功之后，再将临时文件进行重命名；

六、聚合统计

6.1聚合函数

​ 使用聚合函数来辅助实现分组查询；聚合函数如下：

count(distinct 表达式)#返回查询到的数据数量；表达式：column ，*，distinct column，不受null的影响；
sum(distinct 表达式)#返回查询到的数据总和，不是数字没有意义；
avg(distinct 表达式)#返回查询到的数据平均值，不是数字没有意义；
max(distinct 表达式)#返回查询到的数据最大值，不是数字没有意义；
min(distinct 表达式)#返回查询到的数据最小值，不是数字没有意义；

​ select不仅可以对column进行取别名，也可以函数或者表达式取别名；

​ 需要注意的是，聚合后的数据是单一的，所以不同列的数据聚合成一行，关系必须是一对一；

6.2分组聚合查询

​ 分组的目的就是为了分组之后方便进行聚合统计；

select deptno ，max(sal)最高,avg(sal)平均 from emp group by deptno;

​ 分组指定列名，然后根据不同的行数据进行分组，分组后的条件数据，在组内一定是相同的，换句话说就是可以被聚合的；

​ having是对聚合后的数据进行筛选；使用having和group by实现；

select deptno 部门,avg(sal) 平均工资 from emp  group by deptno having 平均工资<2000;

6.3having和where的区别

​ 都是进行条件筛选，where是对具体的任意列进行条件筛选，而having是对分组聚合之后的结果进行条件筛选；

​ 执行顺序一般是：1.先确定表名；2.根据where子句进行条件筛选；3.进行聚合，没有分组默认整张表就是一个组；4.对聚合后的数据进行条件筛选；5.此时的数据就已经准备就绪，可以进行排序或者是分页；

​ 不仅仅从磁盘中导入到MySQL的表才是表结构，筛选并且显示出来的表在逻辑上也是表；换句话说就是MySQL下一切皆表；