mysql 学习总结

mysql 查询结果建立新表

create table table_name select * from tab1;

txt导入mysql

load data infile ‘D:\DeskTop\abc.txt’ into table tab2
fields terminated by ‘;’
lines terminated by ‘\r\n’;

复制表结构

CREATE TABLE t_1 LIKE db_1.t_1

复制表数据

INSERT table_name SELECT * FROM db_1.t_1//

设置允许安全更新

set sql_safe_updates=0;

数据库 删除重复项:

delete A from bdmol as A,(SELECT mol_index, molid, bdid, bdname FROM bdmol group by molid, bdid, bdname having count(1)>1 order by mol_index ) as B where A.mol_index>B.mol_index and A.molid=B.molid;

where col_name is null 而不是 col_name = null;
explain，设置主键、索引。

根据一个表更新另一个表

update tab1,tab2 set tab1.col1=tab2.col1 where tab1.col2=tab2.col2;

根据一个表删除另一个表

delete tab1 from tab1,tab2 where tab1.col1=tab2.col2;

本地文件导入数据库:

load data infile “E:\test\test.txt” into table tablename fields terminated by ‘,’ # 字段分割符
lines terminated by ‘\r\n’;

导出数据库

select * into outfile “E:\test\test.txt” fields terminated by ‘,’ lines terminated by ‘\r\n’ from tablename;

改变表中字段顺序。

alter table tab_name change col_name col_name char(10) after col2_name;
alter table tab_name add column colname char(4); 增加新字段
alter table tab1 add column Cindex int AUTO_INCREMENT primary key first(第一列）；自增字段必须是主键。