MySql表操作

MySql表操作

1.题干：某公司举办了一次竞赛活动，已知有如下三张表:
（A）员工信息表user_info

字段名	字段中文名	字段类型
award name	奖项名称	varchar(50)
user_id	员工编号	varchar(50)
user_nm	员工姓名	varchar(50)
age	年龄	integer
gender	性别	char(1)

枚举值F/M，其中F女生，M:男生

（B）竞赛获奖信息表: rank_info

字段名	字段中文名	字段类型
award_name	奖项名称	varchar(50)
rank	名次(一/二/三等奖)	varchar(50)
user_id	员工编号	varchar(50)

（C）岗位信息表: situation_info

字段名	字段中文名	字段类型
department_id	部门编号	varchar(50)
situation	岗位名称)	varchar(50)
user_id	员工编号	varchar(50)

2.问题：请根据你个人的理解，分别用1条SQL查询语句完成下面题目：
（1）查询获得”最佳歌手”奖项且为女生的所有员工编号和员工姓名名单(人员去重)。

SELECT distinct user_id,user_nm FROM user_info,rank_info WHERE award_name='最佳歌手 and gender=F

（2）查询岗位为“测试执行岗”中部门获奖数量超过3个(大于)的部门编号。

SELECT department_id FROM situation_info AS t1 JOIN rank_info AS t2 on t1.user_id=t2.user.id group by department_id WHERE situation="测试执行岗" having count(department_id)>3

一、delete

delete主要用于删除数据，即将表中所有数据都删除了，但该表依然存在。

两种方式使用delete：

1、从表中删除特定的行（where指定条件）
删除学生表中id为2的记录：
DELETE FROM student WHERE ID=2;

2、从表中删除所有的行（不带where的）
DELETE FROM student;

二、truncate

truncate主要用于删除数据，但保留表结构，且数据不可恢复，该命令也不能添加查询条件。truncate的删除原理是重新创建一个表（不包含数据），然后将原来的表删除。

格式:
truncate table 表名

TRUNCATE table student;

truncate在功能上与不带 where 子句的delete语句相同：二者均删除表中的全部行。但 truncate比 delete速度快，且使用的系统和事务日志资源少。delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

三、drop

drop主要用于删除结构，即删除的时候不仅删除了表中的数据，还删除了表结构。如果我们要删除数据库或数据表，一般都会使用drop来删除。（使用drop删除表后，数据库中查询不到该表）

格式:
drop table 表名

DROP database student;

四、联系与区别

相同点：

 1. truncate和不带where子句的delete、以及drop都会删除表内的数据。
 2. drop、truncate都是DDL语句(数据定义语言)，执行后会自动提交。

不同点：

 - 删除的范围：drop（删除表中所有数据及表结构）>truncate（删除表中所有数据）>=delete（删除表中所有数据或部分数据）
 - 查询条件：delete可以使用查询条件进行表中数据删除，drop和truncate不可以
 - 命令类型：delete属于DML，这个操作会放到rollbacksegement 中，事务提交之后才生效；如果有相应的
 - trigger，执行的时候将被触发。drop和truncate属于DDL，操作立即生效，原数据不放到 rollback segment中，不能回滚，操作不触发 trigger。
 - 数据能否恢复：delete删除的数据可以恢复，但是drop和truncate删除的数据不能恢复。
 - 速度：drop>truncate>delet 
 - 安全性：小心使用 drop 和truncate，尤其没有备份的时候。使用上，想删除部分数据行用 delete，注意带上where子句。想删除表，当然用drop。想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。
 - delete是DML语句，不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。
 

总结来说：

• 当你不再需要该表时，用 drop；
• 当你仍要保留该表，但要删除所有记录时， 用 truncate；
• 当你要删除部分记录时，用delete。

引用

[1]https://cloud.tencent.com/developer/news/249241
[2]https://blog.csdn.net/m0_49622667/article/details/125079671
[3]https://baijiahao.baidu.com/s?id=1694020063890653960&wfr=spider&for=pc