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下一切皆表;