MySql特殊语句

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

2. 导入导出数据

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

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

4. SQL语句执行时间

   set global log_bin_trust_function_creators=TRUE; -- 报错临时修改变量
   -- 获取当前ms时间戳函数
   DROP FUNCTION IF EXISTS GET_MILLISECONDS;
   DELIMITER $$
   CREATE FUNCTION GET_MILLISECONDS()
   RETURNS BIGINT
   BEGIN
   	RETURN (SELECT UNIX_TIMESTAMP(SYSDATE(3))*1000); 
   END $$
   DELIMITER ;
   
   -- 执行1万次时间函数
   DROP FUNCTION IF EXISTS SQL_TIME;
   DELIMITER $$
   CREATE FUNCTION SQL_TIME()
   RETURNS CHAR(128)
   BEGIN
   	DECLARE Count INT;
   
   	DECLARE start_time BIGINT;
   	DECLARE end_time BIGINT;
   	DECLARE diff_time BIGINT;
   
   	DECLARE i INT DEFAULT 1;
   	SELECT GET_MILLISECONDS() into start_time;
   	
   	WHILE i < 10000 DO
   		SELECT COUNT(*) into Count FROM tb_Company;
   		set i=i+1;
   	END WHILE;
   	SELECT GET_MILLISECONDS() into end_time;
   	SET diff_time = end_time - start_time;
   	RETURN CONCAT_WS('|', diff_time, end_time, start_time);
   END $$
   -- 运行函数
   SELECT SQL_TIME();