创键视图
create view 视图名称 as sql 语句
create view vi as
select nid,name from A where nid>4;
create view tem2 as
select score.sid,score.student_id from score inner join student on score.student_id = student.sid where nid>4
删除视图
drop view 视图名
修改视图
alter view 视图名 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 视图名 where ........
视图只能创建一个临时表
存储过程
创建存储过程
delimiter // # delimiter 改变结束符号
create procedure p1()
begin
select * from t1;
end //
delimiter ; # 把mysql 的终止符改成分号
delimiter @
create procedure 名字()
begin
.......写sql语句....
end @
delimiter ;
使用 存储过程 用call p1()
在pymysql 里 执行存储过程 cur.callproc('p1')
存储过程可以接受参数
procedure p1(
in 仅用于传入参数用 arg int
out 仅用于返回值用 arg2 int
inout 既可以传入又可以当作返回值 arg varchar(50)
)
begin
如果上面有in 这里就可以用in 里面传来的内容
......内容.....
end
call p1(1,@i1) @什么 @ 要有
创建存储过程
delimiter \\
CREATE PROCEDURE add_ClubCard (
IN club_number INT,
IN club_name CHAR (8),
IN club_phone CHAR (11)
)
BEGIN
INSERT INTO club_card (card_num, NAME, phone)
VALUES
(
club_number,
club_name,
club_phone
) ;
END\\
delimiter ;
delimiter \\
create procedure p1(
in i1 int,
in i2,
inout i3 int,
out r3 int
)
begin
declare temp1 int; -- declare 定义一个变量temp1
declare temp2 int default 0;
set temp1=1
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
end \\
delimiter;
-- 执行存储过程
set @t1 = 4;
set @t2 = 0;
call p1(1,2,@t1,@t2);
select @t1,@t2;
用python 执行 有参数的存储过程
import pymysql
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='123456',db='mysql')
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
cur.callproc(‘p1',args=(1,22,3,4))
result2 = fetchall() # 如果里面有select * from 表 那么这里面的result2 里面就是select 查询出来 的内容
# 获取执行完 存储的参数
cur.execute('select @_p1_0,@_p1_1,@p1_2,@p1_3')
result = cur.fetchall()
conn.commit()
cur.close()
conn.close()
print(result)
DELIMITER $$#修改结束符
AFTER INSERT ON record
FOR EACH ROW
BEGIN
DECLARE a CHAR(8);
DECLARE b CHAR(10);
DECLARE c CHAR(10);
DECLARE d INT;
SELECT gno,gname ,gtype,`count`INTO a,b,c,d FROM goods WHERE gno = new.gid;
IF d < 100 THEN
INSERT INTO not_enough VALUES(a,b,c,d)
ON DUPLICATE KEY UPDATE COUNT = @c;
END IF;
END $$
DELIMITER ;