Mysql 高级语句

1.插入数据的同时判断某个字段是否有重复的值

1.1插入单条记录

INSERT INTO table (username, password) SELECT '小明', '123456' FROM dual WHERE NOT EXISTS (SELECT * FROM table WHERE table.username='小明');

1.2插入多条记录

INSERT INTO table1 (table1_id, table1_name, table1_type) SELECT table2_id, table2_name, 'admin' FROM table2 WHERE NOT EXISTS (SELECT * FROM table1 WHERE table1.table1_id = table2.table2_id);

 

2.清空数据表，重置自增字段

TRUNCATE TABLE tablename;

 

3.从另一个表，获取更新数据

UPDATE table1 INNER JOIN table2 ON table1.id =  table2.id SET table1.data = table2.data;