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;新闻热点
疑难解答
