mysql 游标 视图_MYSQL视图 触发器 事务 游标 索引及其使用

select * from(select nid,name from tb1 where nid > 2) As A where A.NAME > ‘alex‘;

#创建视图

create view vi As

select nid,name from A WHERE nid > a;

#删除视图

drop view vi

#修改视图

alter view vi As

select A.nid,B.NAME FROM A

left join B ON A.id = B.nid

left join C ON A.id = C.nid

WHERE

A.id > 2

AND C.nid < 5

#使用视图

select * from vi

#触发器

#创建基本语法

#插入前

CREATE trigger tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN

...

END

#插入后

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW

BEGIN

...

END

#删除前

CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE tb1 FOR EACH ROW

BEGIN

...

END

#删除后

CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE tb1 FOR EACH ROW

BEGIN

...

END

#更新前

CREATE TRIGGER tri_beofre_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW

BEGIN

...

END

#更新后

CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW

BEGIN

...

END

#插入前触发器

delimiter //

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN

IF NEW.NAME == ‘alex‘ THEN

VAlUES

(‘aa‘)

END

END //

delimiter;

#插入后触发器

delimiter //

CREATE TRIGGER tri_before_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW

BEGIN

IF NEW.num = 666 THEN

INSERT INTO tb2 (NAME)

VAlUES

(‘666‘),

(‘666‘);

ELSE IF NEW.num = 555 THEN

INSERT INTO tb2 (NAME)

VAlUES

(‘555‘),

(‘555‘);

END IF;

END//

delimiter;

#删除触发器

DROP TRIGGER tri_after_insert_tb1;

#使用触发器

insert into tb1(num) values(666);

#事务

delimiter //

create procedure p1(

OUT p_return_code tinyint

)

BEGIN

DECLEAR exit handler for sqlexception

BEGIN

--ERROR

set p_return_code = 1;

rollback;

END;

DECLEAR exit handler for sqlwarning

BEGIN

--WARNING

set p_return_code = 2;

rollback;

END;

START TRANSACTION;

DELETE from tb1;

insert into tb(name) values (‘seven‘);

COMMIT;

--SUCCESS

set p_return_code = 0;

END\\

delimiter ;

#游标

delimiter //

create procedure p3()

begin

declare ssid int; --自定义变量1

declare ssname varchar(50); --自定义变量2

DECLARE done INT DEFAULT FALSE;

DECLARE my_cursor CURSOR FOR select sid,sname from student;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = True;

open my_cursor;

xxoo: LOOP

fetch my_cursor into ssid,ssname;

if done then

leave xxoo;

END IF;

insert into teacher(tname) values(ssname);

end loop xxoo;

close my_cursor;

end //

delimiter ;

#动态执行SQL

delimiter \\

CREATE PROCEDURE p4(

in nid int

)

BEGIN

PREPARE prod FROM ‘select * from student where nid > ?‘;

EXECUTE prod USING @nid;

DEALLOCATE prepare prod;

END\\

delimiter ;

#删除存储过程

drop procedure proc_name;

#执行存储过程

--无参数

call proc_name()

--有参数

call proc_name(1,2)

--有参数，有in, out, inout

set @t1=0;

set @t2=3;

call proc_name(1,2,@t1,@t2)

#自定义函数

delimiter \\

create function f1(

i1 int,

i2 int)

return int

BEGIN

declare num int;

set num = i1 + i2;

return(num);

END \\

delimiter ;

#删除函数

drop function func_name;

#执行函数

#1.获取返回值

declare @i VARCHAR(32);

select UPPER(‘alex‘) into @i;

SELECT @i;

#2.在查询中使用

select f1(li,nid), name from tb2;

#支持事务的存储过程

set @i = 0;

call p1(@i);

select @i;

class UserInfoRespository:

def get_all(self):

sql = ‘select * from uesrinfo‘

return fetchall()

def get_one_by_user_pwd(self, username, password):

sql = ‘select * from userinfo where username=%s and password=%s‘

cursor.execute(sql,username,password)

return cursor.fetchone()

def add():

sql = "insert into userinfo(...) values (...)"

conn.commit()

obj = UserInfoRespository()

obj.get()

#索引

#创建表+索引

普通索引：加速查询

create table in1(

nid int not null auto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

index ix_name (name)

)

#创建索引

create index index_name on table_name(column_name);

#删除索引

drop index_name on table_name;

#查看索引

show index from table_name;

#如果创建索引时BLOB和TEXT类型，必须指定length

create index ix_extra on ini(extra(32))

2.唯一索引

加速查询和唯一约束(可含null)

创建表+唯一索引

create table in1(

nid int not null auto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

unique ix_name (name)

)

#创建唯一索引

create unique index 索引名 on 表名(列名);

#删除唯一索引

drop unique index 索引名 on 表名;

3.主键索引

加速查询和唯一约束(不可含null)

create table in1(

nid int not null auto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

index ix_name (name)

)

OR

create table in1(

nid int not null auto_increment,

name varchar(32) not null,

email varchar(64) not null,

extra text,

primary key(nid),

index ix_name (name)

)

#创建主键

alter table 表名 add primary key(列名)；

#删除主键

alter table 表名 drop primary key;

alter table 表名 modify 列名 int, drop primary key;

#组合索引

创建组合索引

create index ix_name_email on in3(name, email);

repeat循环

delimiter \\

CREATE PROCEDURE proc_repeat()

BEGIN

DECLARE i INT ;

SET i = 0;

repeat

select i;

set i = i + 1;

until i >= 5

end repeat;

END\\

delimiter ;

loop循环

BEGIN

declare i int default 0;

loop_label:loop

set i = i + 1;

if i < 8 then

iterate loop_label;

end if;

if i >= 10 then

leave loop_label;

end if;

select i;

end loop loop_label;

END

#动态执行SQL语句

delimiter \\

DROP PROCEDURE IF EXIST proc_sql \\

CREATE PROCEDURE proc_sql ()

BEGIN

declare p1 int;

set p1 = 11;

set @p1 = p1;

PREPARE prod FROM ‘select * from tb2 where nid > ?‘;

EXECUTE prod USING @p1;

DEALLOCATE prepare prod;

END\\

delimiter;

原文：https://www.cnblogs.com/gerenboke/p/11761393.html