王新阳

CREATE TABLE tab2 AS (SELECT * FROM tab1)
这种做法表的存储引擎会采用服务器默认的存储引擎而不是源表的存储引擎，此种复制方法把表的内容也一起复制过来了。

CREATE TALBE tab2 ENGINE=MYISAM, CHARSET='UTF8' AS (SELECT * FROM tab1)
可以自己指定存储引擎和字符集，弥补方法一的不足

CREATE TABLE tab2 LIKE tab1
使用和tab1表相同的结构来创建一个新表，列名、数据类型、空指和索引也将复制，但是表的内容不会被复制。外键和专用的权限也没有被复制。

MySQL复制表结构及数据到新表
CREATE TABLE tab_new SELECT * FROM tab_old

复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO tab1 SELECT * FROM tab2

复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO tab1(字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM tab2

更改表名
ALTER TABLE employee RENAME TO staff

更改表注释
ALTER TABLE 表名 COMMENT='新注释';

更改列类型
ALTER TABLE employee MODIFY COLUMN truename VARCHAR(10) NOT NULL DEFAULT ''

修改自增字段下一条记录auto_increment的值为9
ALTER TABLE my_tablename AUTO_INCREMENT=9

修改列注释的时候需要指定{type}
ALTER TABLE student MODIFY COLUMN stu_id INT COMMENT '学号';
参考：http://blog.sina.com.cn/s/blog_72aace390102uwgg.html

更改列名
ALTER TABLE employee CHANGE COLUMN truename employeename VARCHAR(10) NOT NULL DEFAULT ''

添加默认值
ALTER TABLE employee ALTER COLUMN truename SET DEFAULT ''

删除默认值
ALTER TABLE employee ALTER COLUMN truename DEOP DEFAULT

添加列
ALTER TABLE mytable ADD COLUMN addprogram VARCHAR(100) NOT NULL COMMENT '文档添加程序' AFTER title;

删除列
ALTER TABLE mytable DROP COLUMN title

mysql创建索引
1、添加PRIMARY KEY（主键索引） 
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

2、添加UNIQUE(唯一索引) 
mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3、添加INDEX(普通索引) 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

4、添加FULLTEXT(全文索引) 
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5、添加多列索引 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

20240124更新，MYSQL（有多个重复时只保留第一行）：
DELETE FROM my_table WHERE  id NOT IN (SELECT id FROM (SELECT MIN(id) AS id FROM my_table GROUP BY fieldname123) AS a)

20210910更新，MYSQL（只能删除最后一行重复）：
DELETE FROM my_extend_weixin_fans WHERE wxid=123 AND id IN (SELECT id FROM (SELECT MAX(id) AS id FROM my_extend_weixin_fans WHERE wxid=123 GROUP BY openid HAVING COUNT(openid)>1)AS a)

CREATE TABLE mypwd
(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
pwd VARCHAR(14) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE KEY pwd(pwd)
)ENGINE=MYISAM, CHARSET='utf8'

INSERT INTO mypwd(pwd) SELECT DISTINCT pwd FROM oldtable

采用这种方式，从已简单处理过的1300多W的表中复制出DISTINCT pwd 1200多W条到mypwd，用时11分39秒，E5400的CPU占用率低于60%，内存占用也很少。
如果不给pwd字段建索引，仅用110秒就复制完，再给pwd字段创建UNIQUE KEY用时9分34秒。
所以用这种方式还是不错的


/**************下面的方法待验证********************/

如果要删除手机(mobilePhone)，电话(officePhone)，邮件(email)同时都相同的数据，以前一直使用这条语句

delete from 表 where id not in (
select max(id) from 表 group by mobilePhone,officePhone,email )

or

delete from 表 where id not in (
select min(id) from 表 group by mobilePhone,officePhone,email )

下面的方法效率更高

效率对比：用delete方法对500万数据去重（1/2重复）约4小时
用临时表插入对500万数据去重（1/2重复）不到10分钟


//查询出唯一数据的ID,并把他们导入临时表tmp中
select min(id) as mid into tmp from 表 group by mobilePhone,officePhone,email

//查询出去重后的数据并插入finally表中
insert into finally select (除ID以外的字段) from customers_1 where id in (select mid from tmp)

UPDATE customer SET customer.amount=(SELECT 
SUM(works_renewals.contractamount) FROM works_renewals 
WHERE customer.cid=works_renewals.cid)

UPDATE customer 
SET customer.photo = COALESCE(
    (SELECT works_renewals.photo FROM works_renewals 
    WHERE works_renewals.customid=customer.id),
    'http://abc.com/photo.jpg'  -- 当子查询返回 NULL 时使用这个默认值
);

MYSQL

UPDATE book SET price=100 WHERE id IN (SELECT id FROM book AS a WHERE a.price=99)

错误代码： 1093
You can't specify target table 'book' for update in FROM clause

mysql同表组合更新时，需要把SELECT出来的数据放到临时表中。SELECT的表和临时表都要有别名。正确语句：

UPDATE book SET price=100 WHERE id IN (SELECT b.id FROM  (SELECT id FROM book AS a WHERE a.price=99) AS b)

MySQL自表更新示例

update
wxy_enum, (select tmp.id, tmp.addfield1 from wxy_enum as tmp) tmp2
set wxy_enum.parentid=tmp2.id
where wxy_enum.addfield2=tmp2.addfield1 and wxy_enum.rootid>0

当表A.name=表B.name时，用 A.id 填充 B.aid

UPDATE tableA
JOIN tableB ON tableA.name = tableB.name
SET tableB.aid = tableA.id;