mysql事务、视图、触发器

#创建数据库表
create table if not exists runoob_transaction_test(id int(5))engine=innodb;

select * from runoob_transaction_test;

#禁止自动提交
set autocommit=0;


#开启事务
BEGIN;

#sql语句
insert into runoob_transaction_test(id) values (1);

insert into runoob_transaction_test(id) values (2);

select * from runoob_transaction_test;

#提交事务
commit;

select * from  runoob_transaction_test;


#开启事务
begin;

#插入语句
insert into runoob_transaction_test(id) values(7);
#回滚
rollback;
select * from  runoob_transaction_test;

USE info;
#创建作者表
CREATE TABLE IF NOT EXISTS author(
id INT NOT NULL AUTO_INCREMENT,
author_name VARCHAR(50) DEFAULT NULL,
PRIMARY KEY(id)
);
INSERT INTO author(author_name)VALUES('naamman'),
('lucy'),('lily'),('jack');
#创建博客表
CREATE TABLE IF NOT EXISTS blog(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(50) DEFAULT NULL,
content VARCHAR(100) DEFAULT NULL,
author_id INT DEFAULT NULL
);
INSERT INTO blog(title,content,author_id)VALUES
('测试博客01','博客内容01',1),
('测试博客02','博客内容02',2),
('测试博客03','博客内容03',3),
('测试博客04','博客内容04',4);
#创建视图:逻辑上的虚拟表
CREATE VIEW v_author(编号,姓名)
AS
SELECT * FROM author WITH CHECK OPTION;
#查询视图
SELECT * FROM v_author;

create view v_a_b(作者名字,博客标题,博客内容)
as
SELECT a.author_name,b.title,b.content FROM author a left join blog b on a.id=b.author_id;

#修改视图
create or replace view v_blog(编号,标题,内容,作者编号) 
as select * from blog with check option;

#修改视图的数据 ->修改基表数据
update v_blog set 内容='修改后的内容' where 编号=1;

#部分数据创建视图
create or replace view v_blog_1(编号,标题,内容,作者编号) 
as
select * from blog where author_id=1
with check option;

#with check option:受where条件约束
insert into v_blog_1(编号,标题,内容,作者编号)
values
(10,'123','123',1) ;

USE test;

#创建学生表
CREATE TABLE IF NOT EXISTS student
(
 username VARCHAR(50),
 PASSWORD VARCHAR(50),
 stuid INT PRIMARY KEY AUTO_INCREMENT,
 birthday DATE
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


#插入数据
INSERT INTO student(username,PASSWORD,birthday)
VALUES
(
 '王二','11111','2016-08-23'
);


#成绩表
CREATE TABLE IF NOT EXISTS cj
(
 num INT PRIMARY KEY AUTO_INCREMENT,
 stu_id INT,
 stu_name VARCHAR(50),
 math FLOAT,
 chinese FLOAT,
 english FLOAT
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


#触发器 插入数据
DELIMITER $
CREATE TRIGGER stu_cj 
AFTER INSERT ON student FOR EACH ROW
BEGIN
 INSERT INTO cj(stu_id,stu_name)VALUES(new.stuid,new.username);
END $
DELIMITER ;

#插入数据
INSERT INTO student(username,PASSWORD,birthday)
VALUES
(
 '张三','1113311','2011-08-23'
);

#触发器2 删除数据
DELIMITER $
CREATE TRIGGER del 
AFTER DELETE ON student FOR EACH ROW
BEGIN
 DELETE FROM cj WHERE cj.stu_id=old.stuid;
END $
DELIMITER ;


#触发器3 修改数据
DELIMITER $
CREATE TRIGGER upd 
AFTER UPDATE ON student FOR EACH ROW
BEGIN
 UPDATE cj SET cj.stu_name=old.username WHERE cj.stu_id=old.stuid;
END $
DELIMITER ;

UPDATE student SET username='小明' WHERE username='王二';

#创建存储过程
DELIMITER $

CREATE PROCEDURE testa()
BEGIN
	SELECT * FROM student;
	SELECT * FROM cj;
END $
DELIMITER ;

#调用存储过程
CALL testa();


#创建存储过程
DELIMITER $
CREATE PROCEDURE testb()
BEGIN
	#声明变量类型
	DECLARE un VARCHAR(32) DEFAULT '';
	#给username变量赋值
	SET un='李白';
	#将查询结果赋值给un变量
	SELECT username INTO un FROM student WHERE stuid = 2;
	#查询un变量,返回
	SELECT un;
END $
DELIMITER ;

#调用存储过程
CALL testb();


#变量的作用范围

DELIMITER $
CREATE PROCEDURE testc()
BEGIN
	BEGIN
		#声明变量类型
		DECLARE un VARCHAR(32) DEFAULT '';
		#给username变量赋值
		SET un='李白';
		#将查询结果赋值给un变量
		SELECT username INTO un FROM student WHERE stuid = 1;
		#查询un变量,返回
		SELECT un;
	END;
	BEGIN
		#声明变量类型
		DECLARE us VARCHAR(32) DEFAULT '';
		#给username变量赋值
		SET us='里斯';
		#将查询结果赋值给un变量
		SELECT username INTO us FROM student WHERE stuid = 2;
		#查询un变量,返回
		SELECT us;
	END;
END $
DELIMITER ;


#调用存储过程
CALL testc();