数据库拓展操作

目录

一、截断表：

操作目的：

操作内容：

性能影响：

基本语法：

例子：

二、插入查询结果：

基本语法：

例子：

三、聚合函数：

常用函数：

基本语法：

例子：

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(*)：统计所有记录的数量，无论该记录的列值是否为 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