美文网首页
mysql细嚼

mysql细嚼

作者: 某言 | 来源:发表于2018-08-21 16:56 被阅读0次

    mysql虽然用的很多了,但是功能仅仅限于那几样,现在随着业务的增多和逻辑的复杂,那么要了解更多的东西,以便能够更好的工作.


    存储过程

    像编程一样,处理更加复杂的逻辑,可以进行条件判断,变量申明等.和存储函数相比,就像一段程序一样,实现一个功能.
    前久有需求要求能够实现自动审核,于是想用这个方案解决,copy上代码和注释,看看可以明了许多:

    drop procedure if exists auto_check;
    delimiter | 
    -- 输入的变量in,要输出的变量out
    create procedure auto_check(in check_num int)
        begin 
            -- 一定要定义变量和类型,这个是局部变量,用declare
            -- 而且这里命名要注意不要和表名,数据库名字一样,或者数据库里面的字段一样,否则很混淆
            declare user_id_var int;
            declare id_var int;
            declare politics_var varchar(20);
            declare residence_var varchar(50);
            declare idcard_var varchar(100);
            declare real_name_var varchar(50);
            declare mobile_var  varchar(100);
            declare company_unit_var  varchar(100);
            declare count_num int;
            declare tv varchar(5);
            declare tu varchar(5);
            -- 定义的变量不用马上赋值
            set tv = 'tv';
            set tu = 'tu';
            set count_num = 0;
            while count_num<check_num do
                -- 多个变量赋值也只能用一个into,set单复值,select多赋值
                select tv.id,tv.user_id,tv.politics,tv.residence,
                tv.idcard,tu.real_name,tu.mobile,tv.company_unit 
                into 
                id_var,user_id_var,politics_var,residence_var,
                idcard_var,real_name_var,mobile_var,company_unit_var   
                from tb_volunteer as tv left join 
                tb_users as tu on tv.user_id=tu.id 
                ORDER BY FIELD(tv.is_qualified,0,-1,1) limit count_num,1; 
                if (politics_var<>"" and politics_var is not null) 
                    and (residence_var<>"" and residence_var is not null) 
                    and (mobile_var<>"" and mobile_var is not null) 
                    and (company_unit_var<>"" and company_unit_var is not null) 
                    and (idcard_var<>"" and idcard_var is not null) 
                    and (real_name_var<>"" and real_name_var is not null) then
                    update tb_volunteer set is_qualified=1 where user_id=user_id_var;
                end if;
                set count_num = count_num+1;
            end while;
        end |
    delimiter ;
    -- 用户变量用@,不可用declare,全局变量用@@
    set @check_num = 20;
    -- 调用执行存储过程
    call auto_check(@check_num);
    

    再来一个循环的实例,用来实现查询修改数据库中重复的编号,很好的说明了过程使用:

    DROP PROCEDURE IF EXISTS get_rid_of_repeated_code;
    DELIMITER |
    CREATE PROCEDURE get_rid_of_repeated_code()
    BEGIN 
        DECLARE max_age_group_67 BIGINT; -- 存储最大的67开头组
        DECLARE max_age_group_23 BIGINT; -- 存储最大的23开头组
        DECLARE repeat_code BIGINT; -- 存储重复的每个code
        DECLARE repeat_code_count INT; -- 重复的code的个数
        DECLARE finished INT DEFAULT 0; -- 默认default为0 
        DECLARE volunteer_id int DEFAULT 0;
        DECLARE age_group_var int DEFAULT 0;
        DECLARE list text default ''; -- 测试输出的变量
        -- 所有相关的变量都要定义在cursor之前算是发现了
        DECLARE repeat_code_list CURSOR FOR (SELECT code FROM tb_volunteer WHERE code IS NOT NULL GROUP BY code HAVING COUNT(*) > 1);
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; -- 当cursor(游标)没有记录时候赋值为1,要定义在变量的最后才行
    
        select max(code) into max_age_group_67 from tb_volunteer where age_group = 1;
        select max(code) into max_age_group_23 from tb_volunteer where age_group = 2;
        select count(*) into repeat_code_count from (SELECT code FROM tb_volunteer WHERE code IS NOT NULL GROUP BY code HAVING COUNT(*) > 1) as n;
        -- select max_age_group_67,max_age_group_23,repeat_code_count;
    
        OPEN repeat_code_list;
            get_code:LOOP  -- 循环名
                FETCH repeat_code_list into repeat_code;  -- 从游标获取数据
                IF finished THEN
                    LEAVE get_code;  -- 终止循环
                END IF;
                    -- 循环的操作
                    -- 需要处理的数据的顺序:未通过的优先改变,后注册志愿者的优先改变
                    select id,age_group into volunteer_id,age_group_var from tb_volunteer where code = repeat_code ORDER BY is_qualified asc ,id desc limit 0,1;
                    IF (age_group_var=1) THEN
                        SET max_age_group_67 = max_age_group_67+1;
                        update tb_volunteer set code = max_age_group_67 where id = volunteer_id;
                    ELSEIF (age_group_var=2) THEN
                        SET max_age_group_23 = max_age_group_23+1;
                        update tb_volunteer set code = max_age_group_23 where id = volunteer_id;
                    END IF;
                -- SET list = CONCAT(list,'-',repeat_code); -- 测试打开该行数据【1】
            END LOOP get_code;
            -- select list;  -- 测试打开该行数据【2】
        CLOSE repeat_code_list;
    
    END |
    DELIMITER ;
    CALL get_rid_of_repeated_code();
    

    事件

    要让数据库没隔一段时间执行一个操作,这个很方便,代码也很简洁,这里是没60s就执行上面的自动审核的事务,如下:

    drop event if exists runautocheck;
    delimiter |
    create event runautocheck 
    on schedule 
    every 60 SECOND 
    on completion preserve enable 
    do 
        begin 
            call auto_check(50);
        end |
    delimiter ;
    

    函数

    就像一个方法,给过程调用,返回一个值,如max(),sum(),count()这些内置的方法.

    CREATE FUNCTION test() RETURNS int 
    BEGIN 
        DECLARE user_id INT;
        SELECT id into user_id FROM tb_users WHERE real_name = '曹孟德';
        RETURN user_id;
    END;
    SELECT test();
    

    视图

    在有的时候,一个表示很抽象的,可能仅仅有一些int类型的id,或者需要的数据来自于很多表的关联,实在不方便查看,于是,便有了视图这个东西.可能成天都在输入sql语句进行相关的查询,当sql很熟的时候但是又不得不重复输入的时候,它起了很大作用【注:视图实际上相当于子查询,从视图查询,实际上是相当于基于】.

    当发现对一件事情厌恶的时候,不要去厌恶它,证明自己该去突破它了.
    语法很简单,就是:

    CREATE VIEW nameOfView AS ...[sql select]...
    

    例子:

    DROP VIEW IF EXISTS vw_activity_users ; 
    CREATE VIEW vw_activity_users AS 
    SELECT u.real_name AS vol_true_name,u.id as user_id,
    va.title,va.work_date,va.activity_status,
    uva.status AS status_code,u.nickname  
    FROM tb_user_volunteer_activity AS uva 
    LEFT JOIN tb_volunteer_activity AS va ON uva.volunteer_activity_id=va.id 
    LEFT JOIN tb_users AS u ON uva.user_id=u.id 
    

    备份


    报表


    索引


    引擎

    • INNODB
    • MYISAM
    • MEMORY

    部分经典语句详解

    1. 子查询的别名与否
    select * from 
    (
            select name,count(*) as people_number from  -- 将查询出来的部分当做表,那么一定是需要别名的
            (
                    select d.name from tb_department d right join  -- 将查询出来的部分当做表,那么一定是需要别名的
                    (
                        select department_id from tb_volunteer where user_id in -- 查询出来的部分当做范围,就不需要别名
                            (
                                select id from tb_users where union_id in  -- 查询出来的部分当做范围,就不需要别名
                                    (
                                    SELECT union_id FROM tb_score where flag = 1
                                    )
                            )
                    ) as v 
                    on d.id=v.department_id 
            ) as r GROUP BY name
    ) n where name in ('xxx','yyy','mmm')
    
    1. 一些常用的语法结构
    SELECT
        count(r.total_score >= 2500 OR NULL) AS '2500及以上',
        count(
            (r.total_score >= 2000 AND r.total_score < 2500)
            OR NULL
        ) AS '大于等于2000小于2500',
    FROM
        (
            SELECT
                user_id,
                (
                    volunteer_work_hour * 25 + volunteer_work_score + cw_work_hour * 25 + cw_work_score 
                    + IF(youth_work_hour IS NULL,0,youth_work_hour) * 25 
                    + IF(youth_work_score IS NULL,0,youth_work_score)
                ) AS total_score
            FROM
                tb_volunteer
        ) r -- 这里是作为一个新表,所以需要有别名
    
    1. if作为表达式【而非语句控制】
    SELECT IF(gender=1,'男','女') AS sex,real_name FROM tb_users 
    
    1. case用法
    SELECT CASE gender 
    WHEN 1 THEN '男' 
    WHEN 0 THEN '女' 
    END AS sex 
    FROM tb_users
    

    设计方面

    1. 三范式
    1NF:表的所有的属性【所有列】都是原子级别的,也就是不可再分解;
    2NF:表的每个属性都仅仅依赖于此表的主键,即一张表仅仅做一件事请;
    3NF:表内的每个属性都和主键直接联系,其中属性不存在传递【包含】关系
    

    相关文章

      网友评论

          本文标题:mysql细嚼

          本文链接:https://www.haomeiwen.com/subject/ehjlbftx.html