常用mysql语句总结(源自工作实践)

1.修改列默认值
mysql> alter table mail_user_record alter work_exp_id set default 0;                                             

2.增加枚举列
mysql> alter table mail_user_record add is_proof enum('0','1');

3.修改枚举默认值
mysql> alter table mail_user_record modify is_proof enum('0','1')  not null default '0';

4.删除列
mysql> alter table mail_user_record drop com_url;

5.查看建表sql
mysql> show create table career_talk;

6.复制表格
create table new like old;

7.删除表id从1开始
truncate table name;

8.修改列名
alter table position_info_temp change position_name name varchar(100) null

9.修改列长度
alter table hr_auth modify column uuid char(37) ;

10.去除职位表中公司id、职位名、工作地点都一样的重复记录，只保留一条即可。
1）create table tmp as select min(id) as id from position_info_temp group by company_id,addr,name;
2）delete from position_info_temp where id not in (select id from tmp);
3）drop table tmp;
不能使用:
select * from position_info_temp where id not in(select min(id) from position_info_temp group group by company_id,addr,name);
报错：#1093 - You can't specify target table 'blur_article' for update in FROM clause
不能先select出同一表中的某些值，再update这个表(在同一语句中)