1 上下连表
union # 自动去重
union # 不去重
select sid,sname from student
union
select sid,teacher from teacher
select sid,sname from student
union all
select sid,teacher from student
2 基于角色的权限管理
用户信息表
id username pwd role_id
权限表
id privilege_name
角色表
id role_name
角色权限管理
id privilege_id role_id
1.基于角色的权限管理
2.需求分析
http://www.cnblogs.com/wupeiqi/articles/5713323.html
3 视图(虚拟表)
相当于给临时表起了个别名，方便日后使用
- 创建视图
create view v1 as select * from student where sid > 10;
- 使用视图
select * from v1;
- 修改视图
alter view v1 as select * from student where sid < 10;
- 删除视图
drop view v1;
4 触发器(不常使用)
当对某张表做增删改操作时，可以使用触发器在操作前后自定义关联行为
1 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
2 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
3 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
4 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
5 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
6 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
实例：
delimiter //
create trigger t1 before insert on student for each row
begin
insert into teacher(tname) values('teacher01');
end
delimiter ;
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW.NAME == 'alex' THEN
INSERT INTO tb2 (NAME)
VALUES
('aa')
END
END//
delimiter ;
delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW.num = 666 THEN
INSERT INTO tb2 (NAME)
VALUES
('666'),
('666') ;
ELSEIF NEW.num = 555 THEN
INSERT INTO tb2 (NAME)
VALUES
('555'),
('555') ;
END IF;
END//
delimiter ;
NEW 代指新数据 insert操作
OLD 代指老数据 delete update
5 函数
- 内置函数：max min avg sum count curdate
- 自定义函数
内置函数实例：
blog 文章按月分组，显示月份和文章数
id title ctime
1 t1 2019-11-10 11:10
2 t2 2019-11-10 13:10
3 t3 2019-12-10 14:10
4 t4 2019-12-10 16:10
select ctime,count(1) from blog group by ctime
select DATA_FORMAT(ctime,"%Y-%m),count(1) from blog group by DATA_FORMAT(ctime,"%Y-%m)
delimiter \
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END \
delimiter ;
select f1(1,100);
6 存储过程
保存在mysql服务端上的一个别名 => 一坨SQL语句
简单的存储过程
delimiter //
create procedure p1()
BEGIN
select * from student;
insert into teacher(tname) values ('teacher02');
END //
delimiter ;
call p1();
# pymysql执行存储过程
cursor.callproc('p1')
result = cursor.fetchall()
print(result)
传参数的存储过程（in out inout 三种参数类型）
参数in 传给函数内部使用
delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN
select * from student where sid > n1;
insert into teacher(tname) values ('teacher02');
END //
delimiter ;
call p2(5,10);
# pymysql执行存储过程
cursor.callproc('p2',(5,10))
result = cursor.fetchall()
print(result)
参数out 返回给外面使用
delimiter //
create procedure p3(
in n1 int,
out n2 int
)
BEGIN
set n2 = 123123;
select * from student where sid > n1;
insert into teacher(tname) values ('teacher02');
END //
delimiter ;
set @v1 = 0; -- session级别全局变量
call p3(5,@v1); -- 返回值赋给@v1
select @v1;
# pymysql执行存储过程
cursor.callproc('p3',(5,10))
result = cursor.fetchall()
print(result)
cursor.execute('select @_p3_0,@_p3_1')
result = cursor.fetchall()
print(result)
参数inout：既能传进去使用，也能返回来使用
支持事务的存储过程
delimiter \
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT;
-- SUCCESS
set p_return_code = 0;
END\
delimiter ;
存储过程里的游标
注意：游标的效率不高，当要对单独的表的每一行数据进行分门别类的操作时才使用游标
delimiter //
create procedure p6()
begin
declare row_id int; -- 自定义变量1
declare row_num int; -- 自定义变量2
DECLARE done INT DEFAULT FALSE;
declare temp int;
DECLARE my_cursor CURSOR FOR select id,num from a;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open my_cursor;
xxoo: LOOP
fetch my_cursor into row_id,row_num;
if done then
leave xxoo;
END IF;
set temp = row_id + row_num;
insert into b(num) values(temp);
end LOOP xxoo;
close my_cursor;
end //
delimiter ;
CALL p6()
动态执行SQL（防SQL注入）
delimiter \
CREATE PROCEDURE p7 (
in nid int
)
BEGIN
set @nid = nid;
PREPARE prod FROM 'select * from student where sid > ?';
EXECUTE prod USING @nid;
DEALLOCATE prepare prod;
END\
delimiter ;
call p7(10);
总结：
1.可传参 in out inout
out或inout 可用来标识存储过程执行成功与否
2.pymysql操作
3.数据库相关操作
- SQL语句 *****
- 利用MySQL内部提供的功能