垃圾sql

1.Minus

　　MINUS运用在两个SQL语句上，先找出第一条SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。如果有的话，那这一笔记录就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃.

2.DISTINCT

       去重

3.ALTER

       --修改表的编码

       alter table `tablename` convert to character set utf8;

4.drop         .

        删除表

5.truncate   

        清空表

6.coalesce

  

select COALESCE (SUM(CASE WHEN type=2 THEN amount ELSE  0 END),0) as amount from order

7.正则表达式

select * from all_staff_promote_data where area_name REGEXP ('(区域){1,}')

8.is  null

9.is not null

10.group by..... having....

select
    userId as userId,
    coalesce(sum(amount),0) as amount
FROM
    order
group by
    userId
HAVING 
    SUM(amount)>10000

11.UNION all ... DISTINCT

select
    count(DISTINCT d.id) count
from
(
    select idFROM o2 where createTime between #{startTime} and  #{endTime}
UNION all
    SELECT idfrom o1 where createTime between #{startTime} and  #{endTime}
) as d

12.DATE_FORMAT

select DATE_FORMAT("2020-06-01 00:00:00","%Y年%m月%d日 %H时%i分%s秒");

select DATE_FORMAT("2020-06-01 00:00:00","  %x年%v周");

13.删除重复数据

delete from  tb_user where id not in (select * from (select  min(id) from tb_user  GROUP BY user_name) as e)

14.复制表

mysqldump oldDb -u root  -p123456 --add-drop-table | mysql newDb -u root -p123456

15.插入规则

1. insert into :插入数据，重复插入会出错
2. replace into:如果数据已经存在，就会用新值替换旧的值，没有存在相同的id,就新插入一条
3. insert ignore:如果已存在，就不会进行插入，跳过这个

---