个人网站类型,软件开发工程师前景,杭州网站建设|网站设计,洮南住建局网站目录
3. 更新
4. 删除
截断表
日志的作用
5. (实验) 插入查询结果
6. 聚合函数
7. 分组查询 接着上篇文章[MySQL#6] 表的CRUD (1) | Create | Retrieve(查) | where继续讲解~
3. 更新
语法#xff1a;
UPDATE table_name SET column expr [, column expr ...][WHE…目录
3. 更新
4. 删除
截断表
日志的作用
5. (实验) 插入查询结果
6. 聚合函数
7. 分组查询 接着上篇文章[MySQL#6] 表的CRUD (1) | Create | Retrieve(查) | where继续讲解~
3. 更新
语法
UPDATE table_name SET column expr [, column expr ...][WHERE ...] [ORDER BY ...] [LIMIT ...]
set后面跟的是要重新设定的值可以是多列。一般在update的时候必须采用对应where子句进行条件筛选如果没有的话会把这个表中指定的列全部都更新这是不合理的。 对查询到的结果进行列值更新
1. 将孙悟空同学的数学成绩变更为 80 分
update exam_result set math80 where name孙悟空;
2. 将曹孟德同学的数学成绩变更为 60 分语文成绩变更为 70 分
update exam_result set math60, chinese70 where name曹孟德; 3. 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
更新值为原值基础上变更。 注意据更新不支持 math 30 这种语法。
update exam_result set mathmath30 order by chinesemathenglish limit 3; 4. 将所有同学的语文成绩更新为原来的 2 倍
update exam_result set chinesechinese*2; 注意更新全表的语句慎用 4. 删除
语法
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
一般都是拿着条件删除 where不加条件就是把整表的内容删除了不过表结构还在。删表结构drop 删除孙悟空同学的考试成绩
delete from exam_result where name孙悟空;
测试表
-- 准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);-- 插入测试数据
INSERT INTO for_delete (name) VALUES (A), (B), (C); 我们看到这个id设置了自增目前已经插入三条记录了如果在插入一条记录这个id就是4了。
但我们现在不插先删除 delete from for_delete;
我们看到表的内容已经没有了但是查看创建表结构这个语句还在并且自增长也还在。 当新插入一条记录时这个id是4并且自增长已经变成下一个id值了
说明delete from清空表的方式不会将自增值置0。
清空表还有一种做法叫做截断表。在效果和delete一模一样但是在细节和原理是有差别的。
截断表
语法
TRUNCATE [TABLE] table_name
注意这个操作慎用
只能对整表操作不能像 DELETE 一样针对部分数据操作实际上 MySQL 不对数据操作所以比 DELETE 更快但是TRUNCATE在删除数据的时候并不经过真正的事物所以无法回滚会重置 AUTO_INCREMENT 项 可以看到表结构还在但是内容已经被清空了。但是这里值得注意的一点是之前自增长是4现在truncata清空表后自增从已经没有了。然后新插一条记录id变成1了。自增长变成2了换句话说truncate会重置自增长。而delete并不会。 实际上TRUNCATE 和 DELETE 还存在一些差异。TRUNCATE 操作是直接将表中的数据清空并且这个操作不通过事务处理。而 DELETE 和其他 SQL 操作则会在执行时被包装进事务中再由 MySQL 处理。
事务的影响
事务的使用与否会影响 MySQL 对操作信息的记录方式。MySQL 使用其自身的日志系统来记录数据操作的信息主要包括以下几种日志
bin.log存储经过优化的历史 SQL 语句。redo.log用于在 MySQL 遭遇宕机时能够恢复数据。undo.log用于存储回滚段信息。
日志的作用
bin.log 记录的是历史 SQL 语句并支持 MySQL 的主从同步机制。当一个数据库执行完某些操作后可以通过 bin.log 将这些 SQL 同步到另一个数据库从而使两个数据库的数据保持一致主从同步 需要注意默认情况下 bin.log 是关闭的。redo.log 在 MySQL 遇到故障时提供数据恢复功能。
持久化方式
持久化方式指的是为了能够在系统崩溃后快速恢复数据库数据的方法。
将数据以文件的形式写入磁盘通常有两种方式
记录历史sql语句记录数据本身 Truncate的特点
由于 TRUNCATE 不记录自己的操作到日志中也不将其作为事务的一部分因此它仅是简单地清空表中的数据这样做的结果是 TRUNCATE 的执行速度较快。
TRUNCATE 因为其非事务性及不记录日志的特点在执行速度上有优势但在数据恢复和一致性方面不如 DELETE。 5. (实验) 插入查询结果
语法
INSERT INTO table_name [(column [, column ...])] SELECT ...
我们要插就插要删就删要改就改要查就查实际我们也可以将select和insert组合。可以把数据从其他表里面筛选出来然后插入到另一个表里面。
我们来实现如下一个小实验
删除表中的重复记录重复的数据只能有一份 我的做法是
创建一个和原表一样结构的空表 no_duplicate_table 从原始表中把去重之后的结果筛选出来插入到no_duplicate_table 这个表不就是不重复的吗然后对duplicate_table重命名no_duplicate_table改名字为duplicate_table。最终不就是完成了duplicate_table去重了吗。
create table no_duplicate_table like duplicate_table;
创建一个完全一样的表使用like即可
全列插入就不用指定列了
insert into no_duplicate_table select distinct * from duplicate_table;
现在有两个表一个duplicate_table一个no_duplicate_table。然后对这两个表做一下重命名
下面两个sql语句可以写一起
rename table duplicate_table to old_duplicata_table;
rename table no_duplicate_table to duplicate_table;
当前我们就完成了去重 ❓这里有个细节问题为什么最后是通过rename方式进行的 如果今天想把一个文件上传到linux上比如这个文件是1G上传时间可能是10分钟我们想把这个文件上传号之后放到一个目录下我要求它是为原子性方式放入的。所以一般我们不能直接把文件上传到对应的目录下因为它上传的过程一种在写入一定不是原子它太慢了。所以我们把这个文件上传到临时目标下全部上传之后然后再把文件move到那个目录下。直接move这个动作实际上是原子的。其实对一个文件进行重命名也是同一个道理所以我们最后通过rename方式就是单纯的想等一切都就绪了然后统一放入更新生效等 和冗长的其他动作相比这个动作非常轻。
6. 聚合函数 函数 说明 COUNT([DISTINCT] expr) 返回查询到的数据的数量 SUM([DISTINCT] expr) 返回查询到的数据的总和 AVG([DISTINCT] expr) 返回查询到的数据的平均值 MAX([DISTINCT] expr) 返回查询到的数据的最大值 MIN([DISTINCT] expr) 返回查询到的数据的最小值
这里是为select分组查询做准备的聚合函数是以查出来的记录为单位帮我们进行数据聚合统计的。这种聚合统计方式通常是产出一个期望的结果如个数、和、平均值、最大值、最小值。mysql中其实也是有函数的这个函数可以被直接调用我们可以在mysql直接使用聚合函数直接对一组结果进行聚合统计。聚合函数里面可以是全列可以是指定列。
示例
统计班级共有多少同学
select count(*) from exam_result;
统计班级去重后数学成绩有多少
select count(distinct math) from exam_result;
统计数学成绩总分
select sum(math) from exam_result;
统计平均总分
select avg(mathchineseenglish) from exam_result;
返回 70 分以上的数学最低分
select min(math) from exam_result where math70;
这里为什么不能把name带上呢 聚合统计的前提条件一定是你先把我要聚合的数据先拿出来然后才能聚合。做聚合的时候必须保证你要显示的或者你要查询的数据列是被允许聚合的。最低成绩只有一个但name每个人都不一样没有办法做聚合。例如三个人都是 73那返回谁的名字呢 7. 分组查询
分组是对表中的数据进行分组分完组之后在对表中每一组进行相关聚合统计。而分组的目的是为了进行分组之后方便进行聚合统计。如班级里有男生女生我们相对男生女生成绩分别统计所以可以对性别进行分组然后在进行成绩的聚合统计。在select中使用group by 子句可以对指定列进行分组查询。我们也可以把数据筛选之后再进行分组然后再聚合统计。
语法
select column1, column2, .. from table group by column;
测试
准备工作创建一个雇员信息表来自oracle 9i的经典测试表
EMP员工表DEPT部门表SALGRADE工资等级表 正是因为我们表有各种不同的信息因此我们可以对表进行分组查询。 如何显示每个部门的平均工资和最高工资
注意说的是每个部门我们未来是要分组的凭什么分组是由需求来决定的。这里很明显说的是每个部门公司员工信息全部都在员工表里部门号不同员工一定隶属于不同部门所以我们要显示每一个部门的平均和最高工资。以前我们找公司最高工资和平均工资。我们是把这一张表当作一个大的组这一张表在我看来就是一个整体的组我要统计的就是全公司最高工资和平均工资。
但今天需求是按照组来统计的根据的emo表中deptno列来进行分组。
select max(sal) 最高, avg(sal) 平均 from emp group by deptno; 首先再次强调group by不是你想用就能用一定要结合需求。其次group by的核心作用是让我们继续分组聚合统计的所以你要把需求分清楚然后和group by功能对上才能用group by。我们在进行分组统计的时候group by后面指定列名指明我们要分组的列是谁但实际分组是用该列的不同的行数据是否相同进行分组的当我们分完组之后那分组的条件deptno组内一定是相同的因此可以被聚合压缩。
理解
分组不就是把一组按照条件拆分多个组进行各组内的统计。分组 分表 不就是把一张表按照条件在逻辑上拆成多个子表然后分别对各自的子表进行聚合统计。拆成各个组不就是在逻辑上拆成各个表然后分别在每个表里做聚合统计以前我们做的聚合统计是在一张表里进行的换句话说只要掌握在一张表里查询在查询之前先做好分组后面的工作和思路理解上和之前单表上的聚合统计是一模一样的。 显示每个部门的每种岗位的平均工资和最低工资 需求是每个部门的每种岗位注定了一定是要分组的而且还不是分一组我们首先按部门来分组然后在按岗位来分组。
在我看来每一张组都是独立的组在组内做聚合也没什么问题。无非就是比以前多做一份工作先分组再聚合。 一张整表内做聚合和多个子表内做聚合总之还是在一张表内聚合。
select avg(sal) 平均, min(sal) 最低 from emp group by deptno, job; 在想看看是哪个部门的那个岗位的谁的平均工资和最高工资 故意加了一个ename然后就报错了说的是ename没有在分组条件内出现不属于分组条件所以无法进行压缩和聚合。刚才我们说了分组内一定是某个相同的。走到一个组内一定是部门号相同工作总类相同。所以相同的列可以进行压缩聚合。现在出来一个名字名字一定是人人都不同的没有办法进行聚合所以直接报错。
一般大原则只有在group by中出现的具体的列名称才可以在select后面具体出现
除此之外另一类可以直接出现的就是聚合函数其余不能在select后面直接跟不是具体分组条件的列。 显示平均工资低于 2000 的部门和它的平均工资 需求是平均工资低于2000的部门要的是部门和它的平均工资是多少。
我们统计出来每一个部门的平均工资(结果先聚合出来)在进行判断(对聚合的结果进行判断)
我要的不是所有部门我要的是平均工资低于2000的部门此时要对聚合的结果拿出来判断。我们再说一个语法结构
having经常和group by搭配使用作用是对聚合后的统计数据进行条件筛选作用有些像where。
select deptno, avg(sal) 平均 from emp group by deptno having 平均 2000; ❓ having 和 where 区别理解执行顺序构建对 “结果” 的理解。 首先having和where都是够进行条件筛选但是它们两个是完全不同的筛选。
where是对具体的任意列进行条件筛选having对分组聚合之后的结果进行条件筛选。
它们俩的应用场景是完全不同的。换句话说where是先对原始表进行条件过滤对过滤后的结果在进行分组。 执行顺序1. 从表拿数据2. 数据筛选条件3. 数据分组4. 分组结果聚合统计5. 结果筛选。 构建对 “结果” 的理解小小的建模
不要单纯的认为只有磁盘上表结构导入的mysql真实存在的表才叫做表。中间筛选出来的包括最终结果在我看来全部都是逻辑的表。在我看来MySQL一切皆表所以未来只要我们能够处理好单表的CURD所有的sql场景我们全部都能用统一的方式进行。
