sql开发笔记

最新推荐文章于 2024-09-21 22:02:19 发布

蒙多_李

最新推荐文章于 2024-09-21 22:02:19 发布

阅读量501

点赞数

分类专栏：开发经验 MySql 文章标签： MySql 开发笔记 sql

本文链接：https://blog.csdn.net/MicroAndSmall/article/details/60326947

版权

开发经验同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

MySql

1 篇文章 0 订阅

订阅专栏

这篇博客主要介绍了MySQL的开发经验，包括创建和执行存储过程，数据统计案例，存储过程中的数组实现，数据库字段及备注的修改，以及LEFT JOIN和RIGHT JOIN的使用注意事项，强调了在JOIN操作中关联条件和筛选条件的正确位置。

摘要由CSDN通过智能技术生成

1.创建存储过程并执行。

create procedure pro10()
BEGIN
DECLARE 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;-- 结束操作，删除临时表
END

3.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_STATUS
FROM 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;