数据库拓展操作

目录

一、截断表：

操作目的：

操作内容：

性能影响：

基本语法：

例子：

二、插入查询结果：

基本语法：

例子：

三、聚合函数：

常用函数：

基本语法：

例子：

1.统计exam表中有多少记录：

2.查询 > 70 分以上的数学最低分：

四、Group by 分组查询：

 基本语法：

例子：

 1.统计每个角色的人数：

2.统计每个角色的平均工资，最高工资，最低工资：

 3.显示平均工资低于1500的角色和它的平均工资：

 总结：

语法总结：

---

一、截断表：

截断表和删除表是数据库中两种删除数据的操作，但是又有不同之处：

操作目的：

截断表：

主要目的是快速清空表中的所有数据，但保留表的结构，包括表的定义、列名、数据类型、约束条件（如主键、外键、唯一约束等）以及索引等，以便后续可以继续向该表中插入新的数据。

删除表：

是要将整个表从数据库中彻底移除，包括表的结构和表中的所有数据，删除后该表将不复存在，不能再对其进行任何数据操作。

操作内容：

截断表：

仅删除表中的数据行，不会删除表的定义和相关的数据库对象。例如，在 MySQL 中使用 TRUNCATE TABLE table_name; 语句，只是把 table_name 表中的数据清空。

删除表：

会删除表的所有信息，不仅包括数据，还包括表的元数据（如列定义、约束、索引等）。在 MySQL 里执行 DROP TABLE table_name; 后，table_name 表及其相关的一切都会被删除。

性能影响：

截断表：

由于是直接释放数据页，不需要逐行删除数据，所以在处理大量数据时，截断表的性能通常比逐行删除（如使用 DELETE 语句）要好得多。

删除表：

删除表的操作涉及到更多的元数据处理，需要更新数据库的系统目录来移除表的定义信息，因此在某些情况下可能会比截断表稍微慢一些，尤其是当表存在大量相关依赖对象时。

基本语法： truncate table table_name;

table_name 是要截断的表的名称。

        如果表中有自增列（如 MySQL 中的 AUTO_INCREMENT 列），截断表会将自增列的值重置为初始值（通常为 1）。这在需要重新开始计数的场景中非常有用。

例子： -- 创建测试表 create table t_truncate ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); -- 插入测试数据 insert into t_truncate (name) values ('A'), ('B'), ('C'); -- 查看测试表 select * from t_truncate;

-- 截断表 truncate table t_truncate; -- 查看表 select * from t_truncate;

-- 继续写入数据(只写了name，没有写id） insert into t_truncate (name) values ('D'); -- 查看表（自增主键从1开如计数） select * from t_truncate;

如果是截断表，自增列被重置了，（如 上面的例子 id 重新从 1 开始计数）。

如果是删除表，表的自增列会随着表一起被删除。（这里不演示）。

二、插入查询结果：

        插入查询结果指的是将一个查询语句的结果插入到另一个表中。比如将一个表的部分数据复制到另一个表，或者合并多个表的数据等。

下面演示的例子是吧一张表的数据去重后给到另一张表。

基本语法： insert into target_table select column1, column2, ... from source_table [where condition]; target_table：要插入数据的目标表。column1, column2, ...：从源表中选择的列，这些列会对应插入到目标表中。source_table：查询数据的源表。WHERE condition（可选）：筛选源表数据的条件。

例子： -- 创建测试表 create table t_recored ( id int, name varchar(20) ); -- 构造测试数据 insert into t_recored VALUES (100, 'aaa'), (100, 'aaa'), (200, 'bbb'), (200, 'bbb'), (200, 'bbb'), (300, 'ccc'); -- 查看结果 select * from t_recored;

-- 创建一张新表，新表的结构与t_recored相同 create table t_recored_new like t_recored; -- 查看新表的结构 select * from t_recored_new;

 可以看到，新表没有任何数据。

-- 把原表数据去重后,写入去重结果到新表里 insert into t_recored_new (id,name) select distinct id,name from t_recored; -- 查询新表中的记录,得到去重结果 select * from t_recored_new;

 这里，新表就得到了去重后的结果。

如果有需要，就把旧表的表名给到新表来后续维护。

-- 先把旧表名变成 t_recored_old 让出 t_recored 这个名字,再把旧表名给到新表名 rename table t_recored to t_recored_old,t_recored_new to t_recored; -- 查询结果 select * from t_recored;

三、聚合函数：

聚合函数是 SQL 中用于对一组值进行计算并返回单个值的函数，常用于统计和汇总数据。

常用函数： 函数说明count（values）返回查询到的数据的数量sum（values）返回查询到的数据的总和，不是数字没有意义avg（values）返回查询到的数据的平均值，不是数字没有意义max（values）返回查询到的数据的最大值，不是数字没有意义min（values）返回查询到的数据的最小值，不是数字没有意义

count(*)：统计所有记录的数量，无论该记录的列值是否为 null。

count(列名)：统计指定列中非 null 值的数量。

COUNT、SUM、AVG、MAX、MIN 这些常见的聚合函数，括号内一般只能写一个列名。

基本语法： select aggregate_function(column_name) from table_name [where condition] aggregate_function：聚合函数名，如 COUNT、SUM 等。column_name：要进行聚合操作的列名。table_name：要查询数据的表名。where condition（可选）：筛选记录的条件。

例子： 1.统计exam表中有多少记录： select count(*) from exam; 2.查询 > 70 分以上的数学最低分： select min(math) from exam where math > 70;

四、Group by 分组查询：  基本语法： select column1, aggregate_function(column2) from table_name [where condition] group by column1 [having group_condition]; column1：用于分组的列名，可以是一个或多个列，多个列名之间用逗号分隔。aggregate_function(column2)：对分组后的数据应用的聚合函数，column2 是要进行聚合操作的列。table_name：要查询数据的表名。where condition（可选）：在分组之前筛选记录的条件。group by column1：指定按照 column1 列进行分组。having group_condition（可选）：在分组之后对分组结果进行筛选的条件

        通常group by 和 having 配合使用的，就如上面所说，如果group by 分组后的结果还需筛选，就不能使用 where 筛选了，要使用 having 。

例子： -- 建立测试表 create table emp ( id bigint primary key auto_increment comment '编号', name varchar(20) not null comment '名字', role varchar(20) not null comment '角色', salary decimal(10, 2) not null comment '工资' ); -- 插入数据 insert into emp values (1, '马云', '老板', 1500000.00); insert into emp values (2, '马化腾', '老板', 1800000.00); insert into emp values (3, '小王', '员工', 10000.00); insert into emp values (4, '小新', '员工', 12000.00); insert into emp values (5, '刘孟德', '组长', 9000.00); insert into emp values (6, '张三', '组长', 8000.00); insert into emp values (7, '孙悟空', '游戏⻆⾊', 956.8); insert into emp values (8, '猪悟能', '游戏⻆⾊', 700.5); insert into emp values (9, '沙和尚', '游戏⻆⾊', 333.3); -- 查看测试表 select * from emp;

 1.统计每个角色的人数： -- 第一种写法 select role, count(*) from emp group by role; -- 第二种写法 select role, count(role) from emp group by role;

 要注意的是：

select name,role, count(role) from emp group by role;

这样的写法会出错，因为对 role 进行 group by 分组时，会有不同的 name 对应着同一个 role 。

2.统计每个角色的平均工资，最高工资，最低工资： select role,avg(salary),max(salary),min(salary) from emp group by role;

 3.显示平均工资低于1500的角色和它的平均工资： select role,avg(salary) from emp group by role having avg(salary) < 1500;

 总结：

使用时，首先要知道对谁进行分组（group by），分组后要筛选什么条件的数据（having）。

语法总结： select [DISTINCT（去重）] 列1, 列2, 聚合函数(...) from 表名 [where 条件] [group by 分组列] [having 分组后条件] [order by 排序列 [ASC|DESC]] [limit 偏移量, 数量];

 执行顺序：where → group by → having → select → order by