MySQL中如何实现事务管理、视图创建和触发器应用？
- 内容介绍
- 文章标签
- 相关推荐
本文共计878个文字，预计阅读时间需要4分钟。
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();
本文共计878个文字，预计阅读时间需要4分钟。
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();