备份单张表:
create table a (
id int auto_increment ,
name char(16),
address char(128),
primary key(id)
)
insert into a(name,address) values ('aaa',null) , ('bbb',null)
/* 先拷贝表结构, 再插入数据 */
create table c like a ;
insert into c select * from a ;
/* 直接拷贝数据, 但是丢失了表a 的 primary key,,auto_increment等属性约束 */
create table d select * from a ;
查看ddl如下:
CREATE TABLE
c
(
id INT NOT NULL AUTO_INCREMENT,
name CHAR(16),
address CHAR(128),
PRIMARY KEY (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci;
CREATE TABLE
d
(
id INT DEFAULT 0 NOT NULL,
name CHAR(16),
address CHAR(128)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci;
Mysql 更新字段来自于另一张表:
# update select 语句(注意:必须使用inner join)
# 语法 update a inner join (select yy from b) c on a.id =c.id set a.xx = c.yy
例子:update a join b on a.name=b.name and b.status=1 set a.address=b.address