sql 纪录

用于记录开发过程中的sql容易遇到的问题：

1、delete,truncate,drop 区别
delete：精确删除。如删除 学生表(student)里id为5的记录。可以写为 delete from studen where id ='5'
删除表里面的所有数据，可以写为delete from studnet ；
truncate：删除表里面的所有数据 table student 对日志的记录要少；
drop 比较简单，是用来删除表的 如 drop table student 。drop是将表及其里面的数据全部删除。

2、left join on
t1:
+------+--------+------+
| t_id | amount | mode |
+------+--------+------+
| 1 | 1 | 13 |
| 2 | 1 | 13 |
| 3 | 1 | 13 |
| 4 | 1 | 13 |
| 5 | 1 | 10 |
+------+--------+------+

t2:
+------+-------+-------+
| t_id | price | class |
+------+-------+-------+
| 1 | 10 | 1 |
| 1 | 11 | 2 |
| 1 | 12 | 3 |
| 2 | 1 | 1 |
| 2 | 1 | 2 |
| 2 | 1 | 3 |
| 2 | 1 | 4 |
| 3 | 1 | 0 |
| 4 | 1 | 1 |
| 4 | 1 | 2 |
| 4 | 1 | 3 |
+------+-------+-------+

select t_1.t_id as id1, amount, t_2.t_id as id2,price, mode
from t_1 left join t_2 on t_1.t_id = t_2.t_id and t_2.class <2;
+------+--------+------+-------+------+
| id1 | amount | id2 | price | mode |
+------+--------+------+-------+------+
| 1 | 1 | 1 | 10 | 13 |
| 2 | 1 | 2 | 1 | 13 |
| 3 | 1 | 3 | 1 | 13 |
| 4 | 1 | 4 | 1 | 13 |
| 5 | 1 | NULL | NULL | 10 |
+------+--------+------+-------+------+

select t_1.t_id as id1, amount, t_2.t_id as id2,price, mode
from t_1 left join t_2 on t_1.t_id = t_2.t_id where t_2.class <2;
+------+--------+------+-------+------+
| id1 | amount | id2 | price | mode |
+------+--------+------+-------+------+
| 1 | 1 | 1 | 10 | 13 |
| 2 | 1 | 2 | 1 | 13 |
| 3 | 1 | 3 | 1 | 13 |
| 4 | 1 | 4 | 1 | 13 |
+------+--------+------+-------+------+