连接语法的测试环境
测试用表结构
CREATE TABLE left_table(ID INT UNSIGNED NOT NULL AUTO_INCREMENT, Username VARCHAR(40) NOT NULL, Birthday DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', CityID SMALLINT NOT NULL DEFAULT 0, CreatDate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', AlterDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(ID), KEY idx_username(Username) )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';CREATE TABLE right_table(UID INT UNSIGNED NOT NULL , CollectNum MEDIUMINT NOT NULL DEFAULT 0, BuyNum MEDIUMINT NOT NULL DEFAULT 0, SearchNum MEDIUMINT NOT NULL DEFAULT 0, CreatDate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', AlterDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(UID) )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';用于测试的数据
重复执行10次INSERT*** VALUE ***语句：
INSERT INTO left_table(Username,Birthday,CityID,CreatDate,AlterDate)VALUES(CONCAT(SUBSTRING(RAND(),3,8),'@qq.com'),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) YEAR),SUBSTRING(RAND(),3,2),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,3) DAY),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) DAY));执行一次INSERT ** SELECT **语句：
INSERT INTO right_table SELECT ID,SUBSTRING(RAND(),3,4) AS CollectNum,SUBSTRING(RAND(),3,2) AS BuyNum,SUBSTRING(RAND(),3,3) AS SearchNum,CreatDate,AlterDateFROM left_table WHERE ID%5=1;注释：表left_table将会有ID值1,2,3,***,10连续的记录10条，表right_table中有ID值1,6离散的记录2条，并且2表关联条件为：left_table.ID=right_table.UID实现。
