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

心已赠人 2023-01-11 13:22 179阅读 0赞

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

\#事务

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)

)

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;

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL 视图、索引

相关 mysql 游标视图_MYSQL视图事务游标索引及其使用

相关 mysql游标

相关【MySQL】使用游标

相关 mysql游标

相关 mysql游标

相关 MySQL游标

相关 Oracle 视图、序列、索引、游标、触发器、事务

相关 mysql 游标

相关 mysql 游标的使用