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
部分经典语句详解
- 子查询的别名与否
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')
- 一些常用的语法结构
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 -- 这里是作为一个新表，所以需要有别名
- if作为表达式【而非语句控制】
SELECT IF(gender=1,'男','女') AS sex,real_name FROM tb_users
- case用法
SELECT CASE gender
WHEN 1 THEN '男'
WHEN 0 THEN '女'
END AS sex
FROM tb_users
设计方面
- 三范式
1NF：表的所有的属性【所有列】都是原子级别的，也就是不可再分解；
2NF：表的每个属性都仅仅依赖于此表的主键，即一张表仅仅做一件事请；
3NF：表内的每个属性都和主键直接联系，其中属性不存在传递【包含】关系
