一些mysql语句

来源：互联网发布：vb command透明编辑：程序博客网时间：2024/05/17 07:19

创建新表

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;

复制一条数据

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_namemysqldump -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;

0 0