文章目錄

创建新表

CREATE TABLE IF NOT EXISTS TAB(
id INT(10) UNSIGNED NOT NULL,
name VARCHAR(255),
grade INT(10) UNSIGNED NOT NULL,
PRIMARY KEY id
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

清空表

delete from A;

从一张表中选择数据插入另一张表中（id重复的数据不插入）

insert into A(id,name) (SELECT bid,bname FROM B as b where not exists(select * from A as a where a.id=b.bid));

用一张表中的数据更新另一张表中的数据

update A as a,B as b set a.name=b.bname where a.id=b.bid;

根据一张表的信息生成一张排名表

set @x:=0;insert into Order(rank,id,grade) select @x:=@x+1 as rownum,id,grade from User order by grade desc;

exists not exists

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)

复制一条数据

alter table A drop primary key;
insert into A select * from A where id=110;
update A set id=111 where id=110 limit 1;
alter table A add primary key(id);

导出数据

mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_pwd $mysql_db $mysql_table -t -c --skip-lock-tables  > $file_name  
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_pwd $mysql_db > $file_name

创建临时表更新原有表数据

set @x:=0;create table charactertemp select @x:=@x+1 as rownum,charid,pkvalue from character order by pkvalue desc where charid<100;  
set @y:=(select max(charid) from character);  
update character  set charid=charid+@y and charid<100;  
update character as a,charactertemp  as b set a.charid=b.rownum where a.charid=b.charid+@y and a.charid>@y;  
drop table charactertemp;