just_know

# mysql数据库的备份

参考链接: https://www.cnblogs.com/linhaifeng/articles/7525619.html

sql可以看作成是一个编程语言它也有很多好玩的东西.
视图触发器存储过程函数 ...

# 视图

视图其实是一个虚拟表(非真实存在), 其本质是【根据SQL语句获取动态的数据集，并为其命名】, 用户使用时只需使用【名称】即可获取结果集, 并可以将其当作表来使用。有点临时表的意思！

注意：基于视图只能查询, 不能对视图执行增加、修改、删除.
如果源表发生变化，视图表也会发生变化

一个致命的问题:视图是存放在数据库中的,如果我们程序中使用的sql过分依赖数据库中的视图,即强耦合.
那就意味着扩展sql极为不便,因此并不推荐使用.

select * from employee inner join department on employee.dep_id = department.id;

# 创建视图

语法：CREATE VIEW 视图名称 AS SQL语句

/* id 和 name 字段重复 保存不下来！
create view emp_dep as
select * from employee inner join department on employee.dep_id = department.id;
*/
create view emp_dep_view as
select employee.*,department.name as depname from employee inner join department on employee.dep_id = department.id;

show tables; -- 会有一张emp_dep_view表 且有数据
-- 注意:视图创建后 只会产生一个emp_dep_view.frm文件 它没有单独的ibd数据文件 它的数据来自两张物理表
-- 说白了视图存的是虚拟表的表结构,数据没有存

1
2
3
4
5
6
7
8
9
10

# 使用视图

select * from emp_dep_view;

# 删除视图

DROP VIEW 视图名称;

# 修改视图

ALTER VIEW 视图名称 AS SQL语句;

# 触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为。注意: 没有查询!

python里就可以实现的啦！不用它..

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行

# 创建触发器

-- 插入前 
-- tri_before_insert_tb1触发器名字;BEFORE之前;INSERT ON tb1;FOR EACH ROW针对每一行
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ... -- 一系列sql语句
END

AFTER INSERT ON 	# 插入后
BEFORE DELETE ON 	# 删除前
AFTER DELETE ON 	# 删除后
BEFORE UPDATE ON	# 更新前
AFTER UPDATE ON 	# 更新后

1
2
3
4
5
6
7
8
9
10
11
12

# 删除触发器

DROP TRIGGER tri_after_insert_tb1;

# 举例

[示例]：在 t1 表中插入数据之前,先在 t2 表中插入一行数据。

delimiter $$ -- 定义sql语句的结束符
CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
	-- NEW是一个对象 表示准备插入t1表的那一行记录;OLD表示原来的数据。
	IF NEW.name = 'alex' THEN
        INSERT INTO t2 (name) VALUES(NEW.id); -- 必须加分号
    END IF; -- 必须加分号

END $$
delimiter ;

1
2
3
4
5
6
7
8
9
10

insert into t1(id,name,email)values(1,"alex","[email protected]") -- 这里 NEW.id的值就是1

[示例]：在t1表中删除数据之后,再在t2表中插入一行数据。

delimiter $$
CREATE TRIGGER tri_after_insert_t1 AFTER DELETE ON t1 FOR EACH ROW
BEGIN

IF OLD.name = 'alex' THEN
    INSERT INTO t2 (name) VALUES(OLD.id);
END IF;

END $$
delimiter ;

1
2
3
4
5
6
7
8
9
10

# 存储过程

存储过程相当于把一堆操作数据的sql语句写完后,做成了一个接口,给应用程序开发人员用
当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行.
视图、触发器、函数、事务... 都可以往里面丢....

三大开发模式:
（1）应用开发人员(应用程序逻辑) + 开发型DBA(数据的操作存储过程)
（2）应用开发人员(应用程序逻辑+sql语句 ) + 管理型DBA(只管理数据库不写sql语句)
（3）应用开发人员(应用程序逻辑+ORM框架) + 管理型DBA(只管理数据库不写sql语句) ^_^选它！

基于ORM框架写类,类就相当于表,类产生的一条对象就是记录,通过操作类和对象它底层会帮忙转换为sql语句.

(1)理论上最好,撇开(1)不谈。(2)的执行效率最高,(3)的开发效率最高.

# 创建存储过程(无参)

delimiter //
create procedure p1() -- 存储过程的名字p1
BEGIN
		-- 放一堆sql语句
    select * from blog;
    INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;

-- 调用存储过程
# 在mysql数据库中调用
call p1() 

# 在python中基于pymysql调用
cursor.callproc('p1') 
print(cursor.fetchall())

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 创建存储过程(有参)

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值

delimiter //
create procedure p3(
    in n1 int,
    out res int
)
BEGIN
    select * from blog where id > n1;
    set res = 1;
END //
delimiter ;

# 在mysql中调用
set @res=0; -- 返回值不能直接传值,要先设置个变量
call p3(3,@res); -- 3传给了n1 @res变量绑定res
select @res;

# 在python中基于pymysql调用
cursor.callproc('p3',(3,0)) # 0相当于set @res=0
print(cursor.fetchall()) # 查询select的查询结果

cursor.execute('select @_p3_0,@_p3_1;') # @p3_0代表第一个参数，@p3_1代表第二个参数，即返回值
print(cursor.fetchall()) # 查询存储过程参数的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 插入300万条记录...
delimiter $$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy'));
        set i=i+1;
        select concat('egon',i,'_ok');
    end while;
END$$ #$$结束
delimiter ;

1
2
3
4
5
6
7
8
9
10
11
12

# 删除存储过程

drop procedure proc_name;

# 函数

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(1) 
FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m');
+-------------------------------+----------+
| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |
+-------------------------------+----------+
| 2015-03                       |        2 |
| 2016-07                       |        4 |
| 2017-03                       |        3 |
+-------------------------------+----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# 流程控制

略. 参考链接:https://www.cnblogs.com/linhaifeng/articles/7495918.html

用sql语句来完成数据的增删改查,逻辑判断完全可以python来搞定！

← pymysql 补充(1)→