常用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這個表(在同一語句中)