数据库&SQL知识

一、Alter和Update的区别

• 功能上的区别

Alter：修改表结构

           例如增加列：ALTER TABLE 【表名】 ADD 【列名】 【类型】

                   删除列：ALTER TABLE 【表名】 DROP 【列名】

                   重命名列：ALTER TABLE 【表名】 CHANGE 【列名】【新名】

                   修改表字段：alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空]。   （更换列名）

                                        alter table 表名称 modify 字段名称 字段类型 [是否允许非空]。                  （不更换列名）

UPdate：修改表数据

                例如修改字段id列的值：update 【表名】 set id=2

 

• 本质上的区别（了解）：

Alter：Alter是数据定义语言（Data difinition Language），在修改表的结构时，不需要Commit和Rollback

Update：Update是数据操作语言(Data manipulation Language)，在修改数据值时，需要Commit和Rollback，否则提交的结构无效。

二、drop、truncate和delete的区别

• DELETE

（1）delete是数据操作语言（DML），执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

（2）delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

• TRUNCATE

（1）truncate是数据定义语言，会隐式提交，所以，不能回滚，不会触发触发器。

（2）truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。

（3）对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。

（4）truncatetable不能用于参与了索引视图的表。

• DROP

（1）truncate是数据定义语言，会隐式提交，所以，不能回滚，不会触发触发器。

（2）drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

（3）drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态

总结：

1、在速度上，一般来说，drop> truncate > delete。

2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

   如果想删除表，当然用drop； 

   如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

   如果和事务有关，或者想触发trigger，还是用delete；

   如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

三、SQL的执行顺序

(7) select (8) distinct  

(1) from <table 1>

(3) <join_type> join <table2>

(2) on <join_condition>

(4) where <where_condition>

(5) group by <list>

(6) having <having_condition>

(9) order by <order_list>

以此导致的一个问题：SQL中把筛选条件放在left join 和 where 之后的区别：测试筛选条件在on后和where后的区别

总结：从上面的执行顺序我们可以看到where的执行顺序在on之后；

• 当把筛选条件放在on之后时，执行顺序是：取t1表的第一行,按谓词‘on’中的条件扫瞄t2表,如果满足条件,就加入返回结果表，不满足条件则只返回t1.然后取t1表的第二行,按谓词‘on’中的条件扫瞄t2表,如果满足条件,就加入返回结果表，不满足条件则只返回t1.重复以上过程,直到t1表扫描结束
• 当把筛选条件放在where之后时：join一行一行完事之后按照where后的条件再选。

所以如同链接里面的执行结果，当left join的时候放在on后面的条件会显示t1中不满足on后条件的行，原因是因为left_join是将t1一行一行比对的，即使不满足后面的on条件，那也会返回t1的所有，只不过不与t2表建立关联而已。而where后面加这种条件会join完之后按照where去筛选所有的行，会把所有的t1是这个条件的过滤掉。

当然，对于inner join的情况，结果不会有区别，只是执行顺序上面会有不同，导致执行的效率不同。

四、如何在mysql中不用窗口函数实现组内排序功能？

通过设置变量的方式：如下这段代码是通过id按照时间（info_update_time）排序的

SET @user_id:=0,@rownum:=0;
SELECT a.*
FROM 
(SELECT user_id,info_update_time,
@rownum:=(case when @user_id=user_id then @rownum+1 ELSE 1 END) COUNT,
@user_id:=user_id
FROM battery_code_update_info_es8 ORDER BY user_id,info_update_time desc
) AS a

有几个点需要注意：一定要有order by语句，如果只是对每个id增加新的一列（序号），那就直接order by user_id

拿上面的结果来说&#x