mysql4

今日内容：

1.视图

2.触发器

3.函数

4.存储过程

5.索引

6.ORM操作（类对象对数据进行操作）

 

回顾：

　　sql语句：

　　　　数据行：

　　　　　　　　临时表：（select * from tb where id<10)

　　　　　　　　指定映射:select id,name,1,sum(x)/count()

　　　　　　　　条件：

　　　　　　　　　　case when id>8 then xx else xx end

　　　　　　　　三元运算符: if(isnull(xx),0,1)

　　　　　　　　补充：

　　　　　　　　　　左右连表 ：join

　　　　　　　　　　上下链表：union 

　　　　　　　　　　

　　　　　　　　　　

　　　　　　　　SELECT sid,sname FROM student
　　　　　　　　UNION  （去重）  如果是UNION ALL 则会去掉重复的数据。
　　　　　　　　SELECT tid,tname FROM teacher

　　　　　　

参考表结构：

　　用户表

　　id  　　username 　　　　pwd

　　1　　　　castiel　　　　　　123456

 

　　权限表

　　1　　　　订单管理

　　2　　　　用户券

　　3　　　　bug管理

　　

　　用户类型&权限

　　　　1　　　1

　　　　1　　　　2

　　　　2　　　　1

 

 

在上面基础上创建角色表

　　角色表:

　　　　1　　　　IT部门

　　　　2　　　　IT主管

　　　　3　　　　　咨询

　　用户角色关系表

　　　　castiel　　1

　　　　castiel 　　2

　　　　

　　角色权限管理:

　　　　1　　　1

　　　　1　　　　2

　　　　2　　　　1

 

 

　　　　　　1.基于角色的权限管理

　　　　　　2需求分析

 

 

1.视图(他是虚拟的，动态的存在于内存中的表，不能对视图进行插入)

　　某个查询语句设置别名，日后好方便使用

　　当做一张表来调用，SQL语句只能写一个查询

　　-创建 

　　　　create　view  视图名称　　as  SQL

　　-修改

　　　　alter view 视图名称 as SQL

　　-删除

　　　　drop view 视图名称

 

　　

2.触发器（查询不会引起触发器），对某张表做增删改的操作时，可以使用用触发器自定义关联行为。

需要换结束符   ';'

-- delimiter //　　　　　　　　　　(After insert ,before delete,after delete,before update,after update)
-- CREATE TRIGGER t1 BEFORE INSERT ON student FOR EACH ROW
-- BEGIN
-- INSERT INTO teacher(tname) VALUES("asdfgg");
--
-- END //
--
-- delimiter ;

在begin   end里面的语句可以使用NEW，OLD。

-- NEW　代指新数据

--OLD  代指老数据

 

DROP TRIGGER t1  删掉触发器

 

3.函数

　　　内置函数

　　　自定义函数

 

　　执行函数： select  curdate()

　　主要是DATE_FORMAT（）这个方法

　　blog表

id　　title　　ctime

1　　asd　　2018-8-9 10:09

select 　date_format(ctime,"%Y-%m"),count(1) from blog group by DATE_FORMAT(ctime,"%Y-%m")

 

自定义函数(是有返回值的)：

delimiter \\
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;      
set num = i1 + i2;    里面不能写这样的：select * from student;
return(num);
END \\
delimiter ;

 上面三种都是在程序里面没法直接调用执行的，都是在mysql数据库里。

4.存储过程 （写在并存储在mysql服务端。）

　　保存在Mysql上的一个别名  =》一坨SQL语句。

　　调用方法：别名()

　　用于替代人写SQL语句。

　　方式一：

　　　　mysql：存储过程

　　　　程序：调用存储过程

　　方式二：

　　　　mysql：什么都不做。。。

　　　　程序：sql语句

　　方式三：

　　　　mysql：。。。

　　　　程序：类和对象（sql语句）

 

 

1.简单存储过程

　　　　

-- delimiter //
-- CREATE PROCEDURE p1()
-- BEGIN
-- SELECT * FROM student;
-- INSERT INTO teacher(tname) VALUES("你好");
--
-- END //
--
-- delimiter ;

调用: call p1()

　　pymysql 里面调用存储过程方法： curser.callproc("p1")

　　pymysql连接时可以设置 charset="utf8"

 2.传参数

-- delimiter //
-- CREATE PROCEDURE p2(in n1 int,in n2 int)
-- BEGIN
-- SELECT * FROM student where sid > n1;
-- INSERT INTO teacher(tname) VALUES("你好");
-- 
-- END //
-- 
-- delimiter ;

 

调用：

call p2(2,1)

cursor.callproc("p2",(2,1))

 

3.参数 out

-- delimiter //
-- CREATE PROCEDURE p3(in n1 int,out n2 int)
-- BEGIN

-- set n2 = 123456;
-- SELECT * FROM student where sid > n1;
-- INSERT INTO teacher(tname) VALUES("你好");
-- 
-- END //
-- 
-- delimiter ;

 

调用： @v1表示全局变量,与当前客户端是绑定的。

mysql 的调用

set @v1 = 0;

call p3(12,@v1);

select @v1;

 

pymysql的调用：需要再一次执行一遍。

cursor.execute("select @_p3_0,@_p3_1")
r2 = cursor.fetchall()
print(r2)

==》特性

　　a.可传参数： in外面只能拿到它传进去的值  ， out只能往外拿   inout

　　b.没有返回值（通过out来伪造返回值）

　　c.pymysql　　拿到结果集跟返回值。

　　

为什么有结果集又有out伪造返回值。如果存储过程只有insert语句，那就不知道数据库是否执行成功。

 　　out 用于标识存储过程的执行结果。

 

5.事务

-- delimiter //
-- CREATE PROCEDURE p1()
-- BEGIN

-- 1.声明如果出现异常则执行{

　　set status = 1;

　　rollback;

　　}

　　开始事务
-- A 账户减去100
-- B账户加90
-- C账户加10

　　comit;

　　结束事务

　　set status = 2
-- END //
-- 
-- delimiter ;

 mysql代码

delimiter \\
CREATE PROCEDURE p5(OUT p_return_code TINYINT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    -- ERROR 
    SET p_return_code = 1;
    ROLLBACK;
    
    END;
    START TRANSACTION;
        INSERT INTO test(name) VALUES("hahha");
        DELETE FROM tb1;
    COMMIT;
    -- success
    SET p_return_code = 2;
END    \\
delimiter ;

 

 

6.游标 (效率不高)

1.声明游标

2.获取A表数据

　　my_cursor  select id,num from A;

3.for row_id,row_num in my cursor:

　　#手动检测循环是否还有数据，如果无数据

　　#break

　　insert into B(num) values(row_id+row_num)