1.創建存儲過程并執行。
create PRocedure pro10()BEGINDECLARE i INT;set i=0; while i<20 do INSERT INTO `G2S_ZHSM`.`TCM_TERM_SCHOOL_COURSE` ( `SCHOOL_TERM_RECRUIT_ID`, `TERM_ID`, `ELECTIVE_SCHOOL_ID`, `COURSE_ID`, `RECRUIT_ID`, `COURSE_NAME`, `RUN_STANDARD`, `RUN_MODEL`, `PLAN_STUDENT_COUNT`, `IMPORT_STUDENT_COUNT`, `REPORT_STUDENT_COUNT`, `MYUNI_SYNC_TIME`, `CREDIT`, `HOURS`, `TEACHER_NAME`, `CLASS_ROOM`, `PRESELECT_DESC`, `STATUS`, `UPDATED_AT`, `CREATED_AT`, `DELETE_USER`, `CREATE_USER`, `IS_DELETED` ) VALUES ( '375', '5', '674', '2001799', '2831', '年輪', '1', '1', '1', NULL, NULL, NULL, '2.0', '0', '王小娟', '1', '1', '0', '2016-01-12 15:20:15', '2016-01-12 15:20:15', NULL, '1', '0' ); set i=i+1; end while;END;call pro10();2.數據統計的例子：
BEGIN -- 統計選課棄選分析表的數據并插入數據。 -- 聲明統計選課棄選分析表的列的變量。 DECLARE RPT_DAY DATE; DECLARE PROVINCE_COUNT , ABANDON_TERM_ID , TOTAL_COUNT , SCHOOL_NATURE_EYY, SCHOOL_NATURE_JBW , SCHOOL_NATURE_PTBK, SCHOOL_NATURE_GZGZ , SCHOOL_NATURE_ZZZZ, SCHOOL_NATURE_QT, SCHOOL_TYPE_JH , SCHOOL_TYPE_QY, SCHOOL_TYPE_NEW , LAST_ELECTIVE_YX , LAST_ELECTIVE_WX , ALLIANCE_FLMHY , ALLIANCE_LMFHY , ALLIANCE_LMHY , SOURCE_TYPE_QD , SOURCE_TYPE_DX , WHILE_INDEX-- （循環條件的索引） int(11) DEFAULT NULL; DECLARE AREA_NAME varchar(10) DEFAULT NULL; DECLARE PROVINCE_NAME varchar(20) DEFAULT NULL; START TRANSACTION;-- 事務開始 SET RPT_DAY = NOW();-- 數據統計時間。 -- 1.查詢出學期Id SELECT t.ID INTO ABANDON_TERM_ID FROM TRM_SCHOOL_TERM t WHERE NOW() >= t.BEGIN_DATE AND NOW() <= t.END_DATE; -- 創建臨時表，根據學期id查詢出區域、省份、辦學層次、會員類型、上學期是否選課（老會員）、聯盟屬性，用于數據統計 CREATE TEMPORARY TABLE TEMP_TABLE( CONTRACT_ID int(11) NOT NULL, AREA VARCHAR(20) DEFAULT NULL, PROVINCE VARCHAR(20) DEFAULT NULL, SCHOOL_NATURE VARCHAR(20) DEFAULT NULL, SCHOOL_TYPE VARCHAR(20) DEFAULT NULL, LAST_ELECTIVE_STATUS smallint(6) DEFAULT NULL, ALLIANCE VARCHAR(100) DEFAULT NULL, LIST_REPLY_STATUS smallint(6) DEFAULT NULL ); INSERT INTO TEMP_TABLE SELECT C.ID AS CONTRACT_ID ,C.AREA, C.PROVINCE ,C.SCHOOL_NATURE ,C.SCHOOL_TYPE , M.LAST_ELECTIVE_STATUS , C.ALLIANCE , M.LIST_REPLY_STATUS FROM G2S_ZHSM.TCM_CONTRACT AS C LEFT JOIN G2S_ZHSM.TCM_TERM_SCHOOL_MASTER AS M ON(M.IS_DELETED = 0 AND C.SCHOOL_ID = M.SCHOOL_ID AND M.TERM_ID=ABANDON_TERM_ID) WHERE C.IS_DELETED = 0 AND C.SHOW_STATUS = 1; -- 2.查詢出省份的總數量 SELECT COUNT(1) INTO PROVINCE_COUNT FROM (SELECT COUNT(1) FROM TEMP_TABLE GROUP BY PROVINCE) t; -- 根據省份總數量循環獲取需要插入的值，并插入數據。 SET WHILE_INDEX = 0;-- 設置索引值 WHILE WHILE_INDEX < PROVINCE_COUNT DO -- 獲得省份 SELECT PROVINCE INTO PROVINCE_NAME FROM TEMP_TABLE GROUP BY PROVINCE ORDER BY PROVINCE LIMIT WHILE_INDEX,1; -- 獲得區域 SELECT AREA INTO AREA_NAME FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME GROUP BY PROVINCE; -- 獲得省級棄選的數量 SELECT COUNT(1) INTO TOTAL_COUNT FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND LIST_REPLY_STATUS = 3; -- 獲得學校屬性-211棄選的數量 SELECT COUNT(1) INTO SCHOOL_NATURE_EYY FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND SCHOOL_NATURE = 1 AND LIST_REPLY_STATUS = 3; -- 獲得學校屬性-985棄選的數量 SELECT COUNT(1) INTO SCHOOL_NATURE_JBW FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND SCHOOL_NATURE = 0 AND LIST_REPLY_STATUS = 3; -- 獲得學校屬性-普通本科棄選的數量 SELECT COUNT(1) INTO SCHOOL_NATURE_PTBK FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND SCHOOL_NATURE = 2 AND LIST_REPLY_STATUS = 3; -- 獲得學校屬性-高職高專棄選的數量 SELECT COUNT(1) INTO SCHOOL_NATURE_GZGZ FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND SCHOOL_NATURE = 3 AND LIST_REPLY_STATUS = 3; -- 獲得學校屬性-中職中專棄選的數量 SELECT COUNT(1) INTO SCHOOL_NATURE_ZZZZ FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND SCHOOL_NATURE = 4 AND LIST_REPLY_STATUS = 3; -- 獲得學校屬性-其它棄選的數量 SELECT COUNT(1) INTO SCHOOL_NATURE_QT FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND SCHOOL_NATURE = 100 AND LIST_REPLY_STATUS = 3; -- 獲得會員類型-機會的數量 SELECT COUNT(1) INTO SCHOOL_TYPE_JH FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND SCHOOL_TYPE = '機會' AND LIST_REPLY_STATUS = 3; -- 獲得會員類型-簽約的數量 SELECT COUNT(1) INTO SCHOOL_TYPE_QY FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND SCHOOL_TYPE = '簽約' AND LIST_REPLY_STATUS = 3; -- 獲得會員類型-今年簽約的數量 SELECT COUNT(1) INTO SCHOOL_TYPE_NEW FROM G2S_ZHSM.TEMP_TABLE AS T LEFT JOIN G2S_ZHSM.TCM_CONTRACT AS C ON(T.CONTRACT_ID = C.ID AND C.PROVINCE = PROVINCE_NAME AND C.SCHOOL_TYPE = '簽約' AND C.IS_DELETED = 0) WHERE T.PROVINCE = PROVINCE_NAME AND T.SCHOOL_TYPE = '簽約' AND YEAR(C.SIGNING_DATE) = YEAR(NOW()) AND LIST_REPLY_STATUS = 3; -- 獲得上學期已選課（老會員）的數量 SELECT COUNT(1) INTO LAST_ELECTIVE_YX FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND LAST_ELECTIVE_STATUS = 1 AND LIST_REPLY_STATUS = 3; -- 獲得上學期未選課（新會員）的數量 SELECT COUNT(1) INTO LAST_ELECTIVE_WX FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND LAST_ELECTIVE_STATUS = 0 AND LIST_REPLY_STATUS = 3; -- 獲得非聯盟會員的數量 SELECT COUNT(1) INTO ALLIANCE_FLMHY FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND ALLIANCE = '其它' AND LIST_REPLY_STATUS = 3; -- 獲得聯盟會員的數量 SELECT COUNT(1) INTO ALLIANCE_LMHY FROM G2S_ZHSM.TEMP_TABLE WHERE PROVINCE = PROVINCE_NAME AND ALLIANCE <> '其它' AND LIST_REPLY_STATUS = 3; -- 插入數據 INSERT INTO RPT_ABANDON_ELECTIVE_DAY( RPT_DAY, TERM_ID, AREA_NAME, PROVINCE_NAME, TOTAL_COUNT, SCHOOL_NATURE_EYY, SCHOOL_NATURE_JBW, SCHOOL_NATURE_PTBK, SCHOOL_NATURE_GZGZ, SCHOOL_NATURE_ZZZZ, SCHOOL_NATURE_QT, SCHOOL_TYPE_JH, SCHOOL_TYPE_QY, SCHOOL_TYPE_NEW, LAST_ELECTIVE_YX, LAST_ELECTIVE_WX, ALLIANCE_FLMHY, ALLIANCE_LMFHY, ALLIANCE_LMHY, SOURCE_TYPE_QD, SOURCE_TYPE_DX, IS_DELETED ) VALUES( RPT_DAY, ABANDON_TERM_ID, AREA_NAME, PROVINCE_NAME, TOTAL_COUNT, SCHOOL_NATURE_EYY, SCHOOL_NATURE_JBW, SCHOOL_NATURE_PTBK, SCHOOL_NATURE_GZGZ, SCHOOL_NATURE_ZZZZ, SCHOOL_NATURE_QT, SCHOOL_TYPE_JH, SCHOOL_TYPE_QY, SCHOOL_TYPE_NEW, LAST_ELECTIVE_YX, LAST_ELECTIVE_WX, ALLIANCE_FLMHY, 0, ALLIANCE_LMHY, SOURCE_TYPE_QD, SOURCE_TYPE_DX, 0 ); SET WHILE_INDEX = WHILE_INDEX + 1; END WHILE; COMMIT;-- 事務提交 DROP TEMPORARY TABLE IF EXISTS G2S_ZHSM.TEMP_TABLE;-- 結束操作，刪除臨時表END3.MySQL存儲過程數組實現:
DELIMITER $$ DROP PROCEDURE IF EXISTS `array`$$ CREATE PROCEDURE `array`() BEGIN SET @array_content="www mysql com hcymysql blog 51cto com"; SET @i=1; SET @count=CHAR_LENGTH(@array_content)-CHAR_LENGTH(REPLACE(@array_content,' ','')) + 1; -- 得出數組成員總數 CREATE TABLE test.tmp(field1 VARCHAR(100)); WHILE @i <= @count DO INSERT INTO test.tmp VALUES (SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,' ',@i),' ',-1)); -- 依次插入每個成員 SET @i=@i+1; END WHILE; END$$ DELIMITER ;4.MySql的備注修改
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段類型 COMMENT 'xxx';5.MySql新增字段和備注
ALTER TABLE 表名ADD COLUMN `AUDIT_USER_ID` int(11) NULL DEFAULT NULL COMMENT '審核人ID' AFTER `AUDIT_STATUS`;6.關于LEFT JOIN和RIGHT JOIN的使用問題： 1.1當從表（M）沒有與主表（C）匹配的數據時，要把主表與從表的關聯條件和從表（M）數據的篩選要條件要寫在ON里面。例： 語句1：
SELECT C.ID AS CONTRACT_ID ,C.AREA, C.PROVINCE ,C.SCHOOL_NATURE ,C.SCHOOL_TYPE , M.LAST_ELECTIVE_STATUS ,C.ALLIANCE , M.LIST_REPLY_STATUSFROM G2S_ZHSM.TCM_CONTRACT AS C LEFT JOIN G2S_ZHSM.TCM_TERM_SCHOOL_MASTER M ON(M.IS_DELETED = 0 AND C.SCHOOL_ID = M.SCHOOL_ID AND M.TERM_ID=5) WHERE C.IS_DELETED = 0 AND C.SHOW_STATUS = 1 AND C.PROVINCE = '香港';語句2：
SELECT C.ID AS CONTRACT_ID ,C.AREA, C.PROVINCE ,C.SCHOOL_NATURE ,C.SCHOOL_TYPE , M.LAST_ELECTIVE_STATUS , C.ALLIANCE , M.LIST_REPLY_STATUS , M.IS_DELETED FROM G2S_ZHSM.TCM_TERM_SCHOOL_MASTER M RIGHT JOIN G2S_ZHSM.TCM_CONTRACT AS C ON(M.IS_DELETED = 0 AND M.TERM_ID = 5 AND C.SCHOOL_ID = M.SCHOOL_ID) WHERE C.PROVINCE = '香港' AND C.IS_DELETED = 0 AND C.SHOW_STATUS = 1;新聞熱點
疑難解答
