【MySQL】表的增删查改
赞
踩
1. insert 插入
语法：
insert [into] table_name [ colum ] values (value_list)
into 和列属性 都可省略
values左侧为列属性 values右侧为以列属性匹配的数据
创建一张表 students ，内部包含 自增长的主键 id 、 唯一键 sn 、 不为空的name 、 唯一键 qq
输入 desc students; 查看students表结构
单行数据插入
指定列 进行单行插入
由于id为主键 并且是自增长的，所以默认从1开始
若将values 左侧的括号忽略 则为全列插入
多行数据插入
当单个插入后，还想继续插入，就需要加上 逗号进行分割
输入 insert into students values(14,1,‘赵六’,‘8888’),(15,2,‘田七’,‘9999’); 即插入成功赵六和田七的信息
插入替换
插入时，有可能出现 主键冲突 或者 唯一键冲突
主键冲突 即主键的id 值相同
唯一键冲突 即唯一键的qq相同
替换的第一种方案
为了 发生 主键或者唯一键 冲突时，可以修改
选择语句：
insert on duplicate key update colum=value [ ]
如果发生键值冲突，则对指定列进行修正
若插入id值为1的信息，则会发生主键冲突
输入 insert into students values(1,112,‘李四’,‘7777’) on duplicate key update sn=112,qq=‘7777’;
因为发生了主键冲突，所以更新数据库的信息，将原数据库的 sn 值改为112 qq改为7777
输入 select row_count(); 筛选受影响的行
0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
1 row affected: 表中没有冲突数据，数据被插入
2 row affected: 表中有冲突数据，并且数据已经被更新
替换的第二种方案
若没有冲突时，则直接插入
若发生冲突时，删除后再插入
replace into 表名 ( ) values( )
1 row affected: 表中没有冲突数据，数据被插入
2 row affected: 表中有冲突数据，删除后重新插入
由于插入的数据在students表中不存在 所以成功被插入， 显示 1 row affected
由于插入的数据 sn值 qq值 与表中数据相同，则删除原来表中的数据，再添加新插入的数据
显示 2 row affected
2. select 查找
语法：
SELECT
[DISTINCT] {* | {column [, column] …}
[FROM table_name]
[WHERE …]
[ORDER BY column [ASC | DESC], …]
LIMIT …
distinct 表示去重
可以使用* 将所有行全部列举出来，也可以使用 逗号 隔开一列或者多列 按照指定筛选
from 表示想从那个表中去提取
where 提取表结构数据时，想要设置的筛选条件
order by 表示 排序
创建一张表 exam_result ，其中包含 主键自增长的id、不为空的姓名、默认值为0.0 的语文成绩
默认值为0.0的数学成绩、默认值为0.0的英语成绩
在exam_result表中 插入 唐三藏、孙悟空 等人 的 语文 数学 英语 成绩
全列查询
输入 select * from +表名; 即可查看该表的所有信息
但并不推荐 使用 * 进行全列 查询
因为 查询的越多，意味着需要传输的量越大，可能会影响索引的使用
指定列查询
输入 select 指定列名 from 表名; 即可获得对应的表中指定列的信息
也可以通过逗号 获取多个列信息
输入 select 指定列名1,指定列名2 from 表名; 即可获得表中 列名1与列名2的信息
查询字段为表达式
当select 后面跟表达式 如 1+1 、5*6
都会帮助其进行运算出结果
表达式最终的结果是要把表达式计算完
由于10是常数，所以最终显示的每一行都会把10拼上
同样可以计算表中各种数据的加和
将英语 数学 语文 成绩 加在一起 与名字 显示出来
as 可以将列重命名
此时将 英语 、数学、语文成绩 的加和 重命名为 total
可以将as 省略，直接 重命名
将name 重命名为 姓名 、将英语 、数学、语文成绩 的加和 重命名为 总分
结果去重
输入 select distinct 指定列名 from 表名; 即在表的指定列中 去重
3. where子句
where后面跟特定的比较运算符 (比较运算符和逻辑运算符) 来判断 如何进行筛选
mysql中 判断两个子句 是否相等 用 一个等号 即可
NULL 就是为空，0 就是0 ，两者含义不同
无法判断 NULL是否等于 NULL ,等号左右的 NULL 是没办法参与运算的
当使用 <=>，即可判断 两个NULL 是否相等
不等于 ，既可使用 != 比较 ，也可使用 <> 比较
逻辑运算符
在之前创建的 exam_result 表中，查看之前插入的数据
首先查询exam_result表中 所有人的英语成绩
想要查找 表中 英语成绩 小于60分的人
所以在查询所有人英语成绩的基础上 添加 where english <60 ，即可查询到 英语成绩 小于60分的人
where子句案例
and 和 between and 的使用
语文成绩 在 80 和90之间 的同学 及语文成绩
首先查询exam_result表中 所有人的语文成绩
and 表示 并且
想要查找 表中 语文成绩 在 [80，90] 区间的人
所以在查询所有人语文成绩的基础上 添加 where chinese >=80 and chinese <=90 ，即可查询到 语文成绩 在 [80，90] 区间的人
也可以使用 between and ，达到同样的效果
between a0 and a1，表示 [a0,a1] 区间内的值 (可以取到a0和a1)
in 和 or 的使用
数学成绩 是58 或者 59 或者 98 或者 99 的同学 及数学成绩
首先查询exam_result表中 所有人的数学成绩
想要查找 表中 数学成绩 是58 或者 59 或者 98 或者 99 的同学
所以在查询所有人数学成绩的基础上 添加 where math=58 or math =59 or math=98 or math=99
即可查询到 数学成绩 是58 或者 59 或者 98 或者 99 的同学
也可以使用 in ，来达到同样的效果
将想要查询的数学成绩全部放入 in的括号中即可 ，如果有对应的数学成绩 ，则会返回
模糊匹配(like) 的使用
姓孙的同学及 孙某同学
首先查询exam_result表中 所有人的名字
% 表示任意多个(包括0个)任意字符
想要查找 表中 姓孙 的同学
所以在查询所有人名字的基础上 添加 where name like ‘孙%’
即可找到 姓孙的同学
_ 表示任意一个字符
想 要查找 表中 孙某 的同学
所以在查询所有人名字的基础上 添加 where name like ‘孙_’
即可找到 孙某的同学
NULL的查询
查询test表的结构
其中有名字为null 、id为null、名字为空串 的人存在
输入 select * from test where name is null;
查询test表中 名字是 null 的人，但空串并没有显示出来
因为空串和null的含义不同
输入 select * from test where name is not null;
查询test表中 名字不为空 的人 ，空串也显示出来了
因为空串不为空
4. order by ——排序
ASC为升序 (从小到大)
DESC为降序 (从大到小)
默认为ASC
(若没有ober by 子句的查询，则返回的顺序是未定义的)
同学及同学成绩，按数学成绩升序显示
输入 select *from exam_result; 显示exam_result 表的信息
在所有人的信息基础上，添加 order by math asc 表示在数学成绩列按升序排列
NULL视为比任何值小，升序出现在最上面
查询同学各门成绩，按照数学降序 、英语升序、语文升序的方式显示
在所有人的信息基础上，添加 order by math desc,english desc, chinese asc
多个列的升降序需求，使用逗号隔开
就可以查找到 数学降序 、英语升序、语文升序 的人存在
5. 筛选分页结果
limit n ：即将前n行数据截取
如 添加 limit 5 即可将前5行信息 截取出来
limit s,n： s表示下标 从s+1位置开始，连续读取n条记录
如 limit 1,3 从第2行开始截取3行数据
limit n offset s : 取n行数据，并从s+1行开始 (s表示下标)
如 limit 3 offset 0 取3行数据，并从第1行位置开始
为了防止表中数据过大，查询全表数据导致数据库卡死
假设按id进行分页，每页分3条记录
输入 limit 0,3 表示 每一页分3条，该页为第一页
跳过第一页的前3个数据，则从第4行开始 即下标为3
所以 第二页 为 limit 3,3
6. update——修改
语法：
where 条件筛选
order by 排序
limit 分页
对查询到的结果进行列值更新
若使用update时 ，不使用where ，则对整个表进行修改
将孙悟空同学的数学成绩变为80分
输入 update exam_result set math=80 where name =‘孙悟空’; 即将孙悟空的数学成绩改为80分
7. delete ——删除
语法：
删除孙悟空同学的考试成绩
输入 delete from exam_result where name=‘孙悟空’; 将孙悟空同学对应的成绩删除掉
exam_result 表中就找不到 孙悟空的成绩了
创建一张表结构
插入三行数据，分别为 A B C
删除表之前，查询发现 auto_increment 值为4
再将整张表的数据删除
再次查询表信息时，发现表已经空了
再次查询发现 auto_increment对应值依旧为4
所以删除表数据，不会将自增序列对应的值 置空
再次插入 数据E 时，发现 对应的id值从4开始
8. 截断表
语法：
创建一张表 ，id为主键 且 自增长
并向表中插入 A B C
删除表内容之前，自增长id值为4
使用 truncate for_truncate 将把表内容清空后
删除表内容后，自增长id值不存在了
所以删除表数据，会将自增序列对应的值 置空
向表中插入 E 后，再次查询表 发现 id值从1开始
截断表特点：
1.不会对整表操作，不能像delete一样针对不部分数据操作
2.不对数据操作，所以比delete快，但是truncate在删除数据时，不会真正经过事物，所以无法回滚
undefined.会重置 auto_increment项
9. 聚合统计
mysql中存在 聚合函数，这些函数可以直接调，进行聚合统计
继续使用之前创建的表结构
输入 select count(*) from 表名; 即可统计出表中多少行
输入 select math from exam_result; 统计对应的数学成绩
输入 select count(math) math from exam_result; 即可统计对应的数学成绩有多少个
输入 select sum(math) math from exam_result; 即可统计对应的数学成绩总分
输入 select sum(math)/count(*) math from exam_result;
通过数学总分除以总人数，得到数学的平均分
输入 select avg(math) from exam_result; 即可直接获得数学的平均分
输入 输select max(english) from exam_result; 输即可获取英语的最高分
输入 select min(math) from exam_result where math>70; 即可获得数学分数大于70的的最低分
