MySql特殊语句

迈不过友情╰ 2022-01-29 14:29 176阅读 0赞

1.  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();

1.  导入导出数据

导出数据：
    	mysqldump -uroot -p  '数据库名称' > 数据库名称_$(date +%F).sql
    导入数据：
    	mysql -uroot -p < 数据库脚本.sql

1.  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;