MySql特殊语句

MySQL循环插入数据 DROP PROCEDURE test_insert; DELIMITER # CREATE PROCEDURE test_insert() BEGIN DECLARE i INT DEFAULT 1; WHILE i < 100 DO INSERT INTO tb_user (id) VALUES (i); set i=i+1; END WHILE; COMMIT; END # CALL test_insert(); 导入导出数据 导出数据： mysqldump -uroot -p '数据库名称' > 数据库名称_$(date +%F).sql 导入数据： mysql -uroot -p < 数据库脚本.sql SQL Server数据迁移 --删除tb_bind数据 --DELETE FROM tb_bind WHERE id > 0; --DELETE FROM tb_device_whitelist WHERE id > 0; --创建network、iccidsn临时表 CREATE TABLE [dbo].[tb_tmp_network] ( [id] int NOT NULL IDENTITY(1,1) , [account] varchar(32) NULL , [device] varchar(32) NULL , [create_time] datetime NOT NULL DEFAULT (getdate()) ) CREATE TABLE [dbo].[tb_tmp_iccidsn] ( [id] int NOT NULL IDENTITY(1,1) , [account] varchar(32) NULL , [device] varchar(32) NULL , [create_time] datetime NOT NULL DEFAULT (getdate()) ) --查询tb_NetWorkUser的UserAccount是否存在tb_User的User_Account中 --把tb_NetWorkUser的UserAccount、SN存入tb_tmp_network的account、device中 INSERT INTO tb_tmp_network (account, device, create_time) (SELECT UserAccount as account, SN as device, CreateTime as create_time FROM tb_NetWorkUser WHERE EXISTS(SELECT User_Account FROM tb_User WHERE tb_User.User_Account = tb_NetWorkUser.UserAccount)); --查询tb_IccidSn的Iccid存在tb_User的User_Account中 --把tb_IccidSn的Iccid、Sn存入tb_tmp_iccidsn的account、device中 INSERT INTO tb_tmp_iccidsn (account, device, create_time) (SELECT Iccid as account, Sn as device, CreationTime as create_time FROM tb_IccidSn WHERE EXISTS(SELECT User_Account FROM tb_User WHERE tb_User.User_Account = tb_IccidSn.Iccid)); --更新tb_tmp_network的account、create_time来自于tb_tmp_iccidsn和tb_tmp_network的device相同并且tb_tmp_iccidsn的create_time晚于和tb_tmp_network的create_time时间 --SELECT * FROM tb_tmp_iccidsn WHERE EXISTS(SELECT account FROM tb_tmp_network WHERE tb_tmp_iccidsn.device = tb_tmp_network.device AND tb_tmp_iccidsn.create_time > tb_tmp_network.create_time); UPDATE tb_tmp_network SET account=(SELECT account FROM tb_tmp_iccidsn WHERE tb_tmp_iccidsn.device = tb_tmp_network.device) WHERE EXISTS(SELECT device FROM tb_tmp_iccidsn WHERE tb_tmp_network.device = tb_tmp_iccidsn.device AND tb_tmp_iccidsn.create_time > tb_tmp_network.create_time); UPDATE tb_tmp_network SET create_time=(SELECT create_time FROM tb_tmp_iccidsn WHERE tb_tmp_iccidsn.device = tb_tmp_network.device) WHERE EXISTS(SELECT device FROM tb_tmp_iccidsn WHERE tb_tmp_iccidsn.device = tb_tmp_network.device AND tb_tmp_iccidsn.create_time > tb_tmp_network.create_time); --删除tb_tmp_iccidsn中tb_tmp_iccidsn和tb_tmp_network的device重复 DELETE FROM tb_tmp_iccidsn WHERE EXISTS(SELECT device FROM tb_tmp_network WHERE tb_tmp_iccidsn.device = tb_tmp_network.device); --更新tb_tmp_network的device、create_time来自于tb_tmp_iccidsn和tb_tmp_network的account相同并且tb_tmp_iccidsn的create_time晚于和tb_tmp_network的create_time时间 --SELECT * FROM tb_tmp_iccidsn WHERE EXISTS(SELECT device FROM tb_tmp_network WHERE tb_tmp_iccidsn.account = tb_tmp_network.account AND tb_tmp_iccidsn.create_time > tb_tmp_network.create_time); UPDATE tb_tmp_network SET device=(SELECT device FROM tb_tmp_iccidsn WHERE tb_tmp_iccidsn.account = tb_tmp_network.account) WHERE EXISTS(SELECT account FROM tb_tmp_iccidsn WHERE tb_tmp_iccidsn.account = tb_tmp_network.account AND tb_tmp_iccidsn.create_time > tb_tmp_network.create_time); UPDATE tb_tmp_network SET create_time=(SELECT create_time FROM tb_tmp_iccidsn WHERE tb_tmp_iccidsn.account = tb_tmp_network.account) WHERE EXISTS(SELECT account FROM tb_tmp_iccidsn WHERE tb_tmp_iccidsn.account = tb_tmp_network.account AND tb_tmp_iccidsn.create_time > tb_tmp_network.create_time); --删除tb_tmp_iccidsn中tb_tmp_iccidsn和tb_tmp_network的account重复 DELETE FROM tb_tmp_iccidsn WHERE EXISTS(SELECT account FROM tb_tmp_network WHERE tb_tmp_iccidsn.account = tb_tmp_network.account); --查询tb_tmp_network中SN重复的数据 --把重复的SN存入tb_device_whitelist的device中; INSERT INTO tb_device_whitelist (device) (SELECT device FROM tb_tmp_network GROUP BY device HAVING COUNT(*) > 1); --删除SN加入白名单的数据 DELETE FROM tb_tmp_network WHERE EXISTS(SELECT device FROM tb_device_whitelist WHERE tb_tmp_network.device = tb_device_whitelist.device); --插入tb_bind中的数据来自tb_tmp_network INSERT INTO tb_bind (account, device) (SELECT account, device FROM tb_tmp_network); ---插入tb_bind中的数据来自tb_tmp_iccidsn INSERT INTO tb_bind (account, device) (SELECT account, device FROM tb_tmp_iccidsn); --更新tb_bind的agent来自tb_User的User_AgentID UPDATE tb_bind SET agent=(SELECT User_AgentID FROM tb_User WHERE tb_User.User_Account = tb_bind.account) WHERE EXISTS(SELECT account FROM tb_User WHERE tb_bind.account = tb_User.User_Account); --删除临时表 DROP TABLE tb_tmp_network; DROP TABLE tb_tmp_iccidsn; --SELECT account FROM tb_bind GROUP BY account HAVING COUNT(*) > 1; --SELECT device FROM tb_bind GROUP BY device HAVING COUNT(*) > 1;