数据库笔记

一、视图

视图是通过SQL语句查询得到一张虚拟的表，然后保存下来供下次直接使用，节省拼接表的时间消耗。视图就是这张虚拟表。

创建视图语句

语法：
create view 视图名 as sql语句

注意：１.创建的视图只有表结构文件，数据还是来源于原来的表
　　　２.视图通常是用于查询，尽量不要修改视图中的数据

二、触发器

顾名思义,即达到某个条件时自动触发
在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

完整语法:
	create trigger 触发器的名字 before/after insert/update/delete on for each row
	begin
		sql语句...
	end

触发器命名规范: tri_before_insert_t1
			    同理推导

知识点补充:
sql语句默认是以分号结束,但是我们可以修改结束符

delimiter $$  # 将mysql默认的结束符由;换成$$
delimiter $$  是临时修改    只在当前库有效 并且当前窗口一旦关闭再次进入则失效

案例:

CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');

# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;

三、事务

可以保证数据操作的安全性，一致性
如果事务中有操作失败了，整体就算失败
支持回滚操作：一旦数据操作的不符合预期，则可以回滚到上一个状态

事务的四大特性ACID

原子性：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性密切相关

隔离性：一个事务的执行不能被其他事务干扰。

持久性：指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

如何使用?

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元

# 回滚到上一个状态
rollback;

# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""

若站在python代码的角度，应该实现的伪代码逻辑:
try:
    update user set balance=900 where name='wsb'; #买支付100元
    update user set balance=1010 where name='egon'; #中介拿走10元
    update user set balance=1090 where name='ysb'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

四、存储过程

类似于自定义的函数，内部封装了一系列的sql语句，不会mysql操作的人也能够通过调用该存储过程来操作数据库

三种开发模型

第一种

"""
应用程序：只需要开发应用程序的逻辑
mysql：编写好存储过程，以供应用程序调用
优点：开发效率，执行效率都高
缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差
"""

第二种

"""
应用程序：除了开发应用程序的逻辑，还需要编写原生sql
优点：比方式1，扩展性高(非技术性的)
缺点：
1、开发效率，执行效率都不如方式1
2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题
"""

第三种

"""
应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处
缺点：执行效率连方式2都比不过
"""

创建存储过程

语法结构:

delimiter $$
create procedure 存储过程的名字(
	形参1,
	形参2,
	形参3,
)
begin
	sql语句;
end;

无参:
delimiter $$
create procedure p1()
begin
	select * from user;
end $$
delimiter ;
#mysql中调用
call p1(); #相当于执行select * from user;


有参:
delimiter $$
create procedure p1(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select name from teacher where id > m and id < n;
    set res=0;  # 修改形参的值   
end $$
delimiter ;

# 返回值 用来判断当前存储过程是否执行成功
"""
res = 10
p1(1,2,res)
print(res)
"""

如何用存储过程

# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！

# 1、直接在mysql中调用
set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功，@res变量值发生了变化

# 2、在python程序中调用
pymysql链接mysql

产生的游表:
cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')

五、函数

注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！

CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);

INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

六、流程控制

if条件语句:

delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END //
delimiter ;

while循环:

delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;
END //
delimiter ;

posted @ 2020-01-03 11:32 Hank·Paul 阅读(129) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Hank·Paul

原CSDN博客已不用，转到此处

数据库笔记

一、视图

二、触发器

三、事务

如何使用?

四、存储过程

三种开发模型

第一种

第二种

第三种

创建存储过程

如何用存储过程

五、函数

六、流程控制

公告

Hank·Paul

原CSDN博客已不用，转到此处

数据库 笔记

一、视图

二、触发器

三、事务

如何使用?

四、存储过程

三种开发模型

第一种

第二种

第三种

创建存储过程

如何用存储过程

五、函数

六、流程控制

公告

数据库笔记