数据库操作

视图操作：

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

create table user(
id int primary key auto_increment,
name char(32),
dep_id int,
foreign key(dep_id) references dep(id)
);

insert into dep(name) values('外交部'),('销售'),('拆迁部');
insert into user(name,dep_id) values('egon',1),('alex',3),('yuanhao',2);


create view user_dep as select
user.id uid,user.name uname,dep.id depid,dep.name depname
from user left join dep
on user.dep_id=dep.id;

触发器：

#创建触发器的语法
CREATE
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

#第一步：准备表
create table cmd_log(
id int primary key auto_increment,
cmd_name char(64),
sub_time datetime,
user_name char(32),
is_success enum('yes','no')
);

create table err_log(
id int primary key auto_increment,
cname char(64),
stime datetime
);

#第二步：创建触发器
delimiter //
CREATE
    TRIGGER tri_after_insert_cmd_log
    after insert
    ON cmd_log FOR EACH ROW
BEGIN
    if new.is_success = 'no' then
        insert into err_log(cname,stime) values(new.cmd_name,new.sub_time);
    end if;
END //
delimiter ;

#测试
insert into cmd_log(cmd_name,sub_time,user_name,is_success) values
('ls -l /etc | grep *.conf',now(),'root','no'), #NEW.id,NEW.cmd_name,NEW.sub_time
('ps aux |grep mysqld',now(),'root','yes'),
('cat /etc/passwd |grep root',now(),'root','yes'),
('netstat -tunalp |grep 3306',now(),'egon','no');

事务：

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

insert into user1(name,balance) values
('庞文废',200),
('萧pt',200),
('八哥',200);

start transaction;
update user1 set balance=100 where name='庞文废';
update user1 set balance=210 where name='萧pt';
updsate user1 set balance=290 where name='八哥';
rollback; #如果任意一条sql出现异常都应该回滚到初始状态
commit; #如果所有的sql都正常，应该执行commit

存储过程：

#使用存储过程的优点
#1 程序与数据实现解耦
#2 减少网络传输的数据量

#===============================================
#创建无参存储过程
delimiter //
create procedure p1()
begin
    select * from test;
    insert into test(username,dep_id) values('wsb',2);
end //
delimiter ;

#调用存储过程
call p1(); #在mysql中调用
cursor.callproc('p1') #在python中通过pymysql模块调用



#===============================================
#创建有参存储过程之in的使用
delimiter //
create procedure p2(
    in m int,
    in n int,
)
begin
    select * from test where id between m and n;
end //
delimiter ;

#调用存储过程
call p2(3,7); #在mysql中调用
cursor.callproc('p2',args=(3,7)) #在python中通过pymysql模块调用

#===============================================
#创建有参存储过程之out的使用
delimiter //
create procedure p3(
    in m int,
    in n int,
    out res int
)
begin
    select * from test where id between m and n;
    set res=1;
end //
delimiter ;

#调用存储过程
#在mysql中
set @x=11111111111
call p3(3,7,@x); #在mysql中调用, 查看结果：select @x;

#在python中
res=cursor.callproc('p3',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123
print(cursor.fetchall()) #只是拿到存储过程中select的查询结果
cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
print(cursor.fetchall()) #可以拿到的是返回值



#===============================================
#创建有参存储过程之inout的使用

delimiter //
create procedure p4(
    inout m int
)
begin
    select * from test where id > m;
    set m=1;
end //
delimiter ;

#在mysql中
set @x=2;
call p4(@x);
select @x;



delimiter //
create procedure p5(
    inout m int
)
begin
    select * from test11111111 where id > m;
    set m=1;
end //
delimiter ;

set @x=2;
call p5(@x);
select @x;

#====================捕捉异常+事务===========================
delimiter //
create PROCEDURE p6(
    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;
        insert into test(username,dep_id) values('egon',1);
        DELETE from tb1111111; #执行失败
    COMMIT;

    -- SUCCESS
    set p_return_code = 0; #0代表执行成功

END //
delimiter ;


#用python模拟
try:
    START TRANSACTION;
        DELETE from tb1; #执行失败
        insert into blog(name,sub_time) values('yyy',now());
    COMMIT;
    set p_return_code = 0; #0代表执行成功
except sqlexception:
    set p_return_code = 1;
    rollback;
except sqlwaring:
    set p_return_code = 2;
    rollback;




mysql> show procedure status like 'p1%'; #查看某一类存储过程

函数：

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');



#自定义函数
mysql> select f1(1,2)
    -> ;
+---------+
| f1(1,2) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

mysql> select f1(1,2) into @res;
Query OK, 1 row affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

流程控制：

#函数中不要写sql语句，它仅仅只是一个功能，是一个在sql中被应用的功能
#若要想在begin...end...中写sql，请用存储过程
delimiter //
create function f5(
    i int
)
returns int
begin
    declare res int default 0;
    if i = 10 then
        set res=100;
    elseif i = 20 then
        set res=200;
    elseif i = 30 then
        set res=300;
    else
        set res=400;
    end if;
    return res;
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 ;