mysql视图里面可以传参数吗 mysql视图可以修改数据吗

转载

mob64ca1411e411 2024-06-27 16:04:51

文章标签 mysql视图里面可以传参数吗 mysql 数据库 ci 数据 文章分类 MySQL 数据库

1. 概念及作用

1.1 概念
视图，就是由数据库中⼀张表或多张表 根据特定条件查询出来的数据 所构造成的一张 虚拟表。

1.2 作用
安全性：如果我们直接将数据表授权给⽤户操作，那么⽤户可以CRUD数据表中所有数据，假如我们想要对数据表中的部分数据进⾏保护，可以将公开的数据⽣成视图，授权⽤户访问视图；⽤户通过查询视图可以获取数据表中公开的数据，从⽽达到将数据表中的部分数据对⽤户隐藏。
简单性：如果我们需要查询的数据来源于多张数据表，可以使⽤多表连接查询来实现；我们通过视图将这些多表连接查询的结果对⽤户开放，⽤户则可以直接通过查询视图获取多表数据，操作更便捷。

2. 创建视图

2.0 数据源

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80016
 Source Host           : localhost:3306
 Source Schema         : book_db

 Target Server Type    : MySQL
 Target Server Version : 80016
 File Encoding         : 65001

 Date: 23/05/2023 00:00:23
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books`  (
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `book_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `book_author` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `book_price` decimal(10, 2) NOT NULL,
  `book_stock` int(11) NOT NULL,
  `book_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`book_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of books
-- ----------------------------
INSERT INTO `books` VALUES (1, 'Java无难事', '孙鑫', 38.80, 7, '零基础讲解java');
INSERT INTO `books` VALUES (2, 'linux就该这么学', '鸟哥', 44.40, 2, '跟我从零开始学linux');

-- ----------------------------
-- Table structure for records
-- ----------------------------
DROP TABLE IF EXISTS `records`;
CREATE TABLE `records`  (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `snum` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `bid` int(11) NOT NULL,
  `borrow_num` int(11) NOT NULL,
  `is_return` int(11) NOT NULL,
  `borrow_date` date NOT NULL,
  PRIMARY KEY (`rid`) USING BTREE,
  INDEX `FK_RECORDS_STUDENTS`(`snum`) USING BTREE,
  INDEX `FK_RECORDS_BOOKS`(`bid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of records
-- ----------------------------
INSERT INTO `records` VALUES (2, '1001', 1, 2, 0, '2023-05-21');
INSERT INTO `records` VALUES (6, '1002', 1, 1, 0, '2023-05-22');
INSERT INTO `records` VALUES (7, '1003', 2, 3, 0, '2023-05-22');

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`  (
  `stu_num` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `stu_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `stu_gender` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `stu_age` int(11) NOT NULL,
  PRIMARY KEY (`stu_num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('1001', '张三三', '男', 20);
INSERT INTO `students` VALUES ('1002', '李四', '男', 20);
INSERT INTO `students` VALUES ('1003', '王五五', '女', 20);
INSERT INTO `students` VALUES ('1111', '高俅', '男', 45);

-- ----------------------------
-- Table structure for stulogs
-- ----------------------------
DROP TABLE IF EXISTS `stulogs`;
CREATE TABLE `stulogs`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` timestamp(0) NULL DEFAULT NULL,
  `log_text` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of stulogs
-- ----------------------------
INSERT INTO `stulogs` VALUES (4, '2023-05-22 22:08:23', '添加1005学生信息');
INSERT INTO `stulogs` VALUES (5, '2023-05-22 22:08:23', '添加1006学生信息');
INSERT INTO `stulogs` VALUES (6, '2023-05-22 22:14:13', '修改学生信息为1006赵八ba');
INSERT INTO `stulogs` VALUES (7, '2023-05-22 22:21:41', '删除1005学生信息');
INSERT INTO `stulogs` VALUES (18, '2023-05-22 22:32:25', '修改学生信息为1003王五五');
INSERT INTO `stulogs` VALUES (19, '2023-05-22 22:32:25', '将学生姓名从 王五 修改为王五五');
INSERT INTO `stulogs` VALUES (20, '2023-05-22 22:49:40', '修改学生信息为1003王五五');
INSERT INTO `stulogs` VALUES (21, '2023-05-22 22:49:40', '将学生姓名从 王五五 修改为王五五');
INSERT INTO `stulogs` VALUES (22, '2023-05-22 23:37:01', '添加1111学生信息');
INSERT INTO `stulogs` VALUES (23, '2023-05-22 23:37:45', '修改学生信息为1001张三三');
INSERT INTO `stulogs` VALUES (24, '2023-05-22 23:37:45', '将学生姓名从 张三 修改为张三三');
INSERT INTO `stulogs` VALUES (25, '2023-05-22 23:47:34', '删除1111学生信息');
INSERT INTO `stulogs` VALUES (26, '2023-05-22 23:47:36', '修改学生信息为1001张三');
INSERT INTO `stulogs` VALUES (27, '2023-05-22 23:47:36', '将学生姓名从 张三三 修改为张三');
INSERT INTO `stulogs` VALUES (28, '2023-05-22 23:48:00', '添加1111学生信息');
INSERT INTO `stulogs` VALUES (29, '2023-05-22 23:49:47', '修改学生信息为1001张三三');
INSERT INTO `stulogs` VALUES (30, '2023-05-22 23:49:47', '将学生姓名从 张三 修改为张三三');

-- ----------------------------
-- Table structure for temp
-- ----------------------------
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp`  (
  `num` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of temp
-- ----------------------------
INSERT INTO `temp` VALUES (102);
INSERT INTO `temp` VALUES (103);
INSERT INTO `temp` VALUES (104);

-- ----------------------------
-- View structure for view_test1
-- ----------------------------
DROP VIEW IF EXISTS `view_test1`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_test1` AS select `students`.`stu_num` AS `stu_num`,`students`.`stu_name` AS `stu_name`,`students`.`stu_gender` AS `stu_gender`,`students`.`stu_age` AS `stu_age` from `students` where (`students`.`stu_gender` = '男');

-- ----------------------------
-- View structure for view_test2
-- ----------------------------
DROP VIEW IF EXISTS `view_test2`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_test2` AS select `s`.`stu_name` AS `stu_name`,`b`.`book_name` AS `book_name`,`r`.`borrow_num` AS `borrow_num` from ((`books` `b` join `records` `r`) join `students` `s` on(((`b`.`book_id` = `r`.`bid`) and (`r`.`snum` = `s`.`stu_num`))));

-- ----------------------------
-- Procedure structure for proc_borrow_book
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_borrow_book`;
delimiter ;;
CREATE PROCEDURE `proc_borrow_book`(IN a char(4), IN b int, IN m int, OUT state int)
begin
	 declare stu_count int default 0;
	 declare book_count int default 0;
	 declare stock int default 0;
 
	 -- 判断学号是否存在：根据参数 a 到学⽣信息表查询是否有stu_num=a的记录
	 select count(stu_num) INTO stu_count from students where stu_num=a;
	 if stu_count>0 then
		 -- 学号存在
		 -- 判断图书ID是否存在：根据参数b 查询图书记录总数
		 select count(book_id) INTO book_count from books where book_id=b;
		 if book_count >0 then
		 -- 图书存在
		 -- 判断图书库存是否充足？查询当前图书库存，然后和参数m进行比较
			 select book_stock INTO stock from books where book_id=b;
			 if stock >= m then
			 -- 执行借书
			 -- 操作1：在借书记录表中添加记录
			 insert into records(snum,bid,borrow_num,is_return,borrow_date) values(a,b,m,0,sysdate());
			 -- 操作2：修改图书库存
			 update books set book_stock=stock-m where book_id=b;
			 -- 借书成功
			 set state=1;
			 else
				 -- 库存不足
				 set state=4;
			 end if;
		 else
			 -- 图书不存在
			 set state = 3;
		 end if;
	 else
		 -- 不存在
		 set state = 2;
	 end if;
end
;;
delimiter ;

-- ----------------------------
-- Function structure for test_fun1
-- ----------------------------
DROP FUNCTION IF EXISTS `test_fun1`;
delimiter ;;
CREATE FUNCTION `test_fun1`(id int)
 RETURNS varchar(100) CHARSET utf8mb4
  READS SQL DATA 
begin
	declare name varchar(20);
	select book_name INTO name from books where book_id=id;
	return name;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for use_cursor
-- ----------------------------
DROP PROCEDURE IF EXISTS `use_cursor`;
delimiter ;;
CREATE PROCEDURE `use_cursor`(OUT res varchar(100))
begin
	declare bname varchar(20);
	declare bauthor varchar(20);
	declare bprice decimal(10, 2);
	declare count int default 0;
	declare i int default 0;
	declare string varchar(100); 
	
	declare MyCursor cursor for select book_name, book_author, book_price from books;
	select count(1) INTO count from books;
	
	open MyCursor;
	while i < count do
		fetch MyCursor INTO bname, bauthor, bprice; 
		set i = i + 1;
-- 		set string = CONCAT_WS('#', bname, bauthor, bprice); 
		select CONCAT_WS('#', bname, bauthor, bprice) INTO string;
		set res = CONCAT_WS(',', res, string);
	end while;
	close MyCursor;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table students
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger_test4`;
delimiter ;;
CREATE TRIGGER `trigger_test4` AFTER UPDATE ON `students` FOR EACH ROW insert into stulogs(time, log_text) values(now(), concat('将学生姓名从 ', OLD.stu_name, ' 修改为', NEW.stu_name))
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table students
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger_test1`;
delimiter ;;
CREATE TRIGGER `trigger_test1` AFTER INSERT ON `students` FOR EACH ROW insert into stulogs(time, log_text) values(now(), concat('添加', NEW.stu_num,'学生信息'))
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table students
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger_test2`;
delimiter ;;
CREATE TRIGGER `trigger_test2` AFTER UPDATE ON `students` FOR EACH ROW insert into stulogs(time, log_text) values(now(), concat('修改学生信息为', NEW.stu_num,NEW.stu_name))
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table students
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger_test3`;
delimiter ;;
CREATE TRIGGER `trigger_test3` AFTER DELETE ON `students` FOR EACH ROW insert into stulogs(time, log_text) values(now(), concat('删除', OLD.stu_num, '学生信息'))
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;

2.1 语法及实例

-- 语法格式
create view 视图名
as
select_statement

-- 创建视图实例1：将学⽣表中性别为男的学⽣⽣成⼀个视图
create view view_test1
as 
select * from students where stu_gender='男';

select * from view_test1;

mysql视图里面可以传参数吗 mysql视图可以修改数据吗_数据库

-- 创建视图示例2：查询学⽣借书的信息（学⽣名、图书名、借书数量）
create VIEW view_test2
AS 
SELECT s.stu_name, b.book_name, r.borrow_num FROM books AS b INNER JOIN records AS r INNER JOIN students AS s
ON b.book_id=r.bid AND r.snum=s.stu_num; 

SELECT * FROM view_test2;

mysql视图里面可以传参数吗 mysql视图可以修改数据吗_数据库_02

3. 视图特性

视图是虚拟表，查询视图的数据是来源于数据表的。当对视图进⾏操作时，对原数据表中的数据是否由影响呢？？？

（1）查询操作：如果在数据表中添加了新的数据，同时这个数据满⾜创建视图时查询语句的条件，通过查询视图也可以查询出新增的数据；当删除原表中满⾜查询条件的数据时，也会从视图中删除；
（2）新增数据：如果在视图中添加数据，数据会被添加到原数据表；
（3）删除数据：如果从视图删除数据，数据也将从原表中删除；
（4）修改操作：如果通过修改数据，则也将修改原数据表中的数据。

视图的使⽤建议 : 对复杂查询简化操作，并且不会对数据进⾏修改的情况下可以使⽤视图。

-- 在视图中插入数据
insert into view_test1 values('1111', '高俅', '男', 45);

select * from view_test1;
select * from students;

mysql视图里面可以传参数吗 mysql视图可以修改数据吗_数据库_03

mysql视图里面可以传参数吗 mysql视图可以修改数据吗_ci_04

-- 修改视图中的数据
update view_test1 set stu_name='张三三' where stu_name='张三';

select * from view_test1;
select * from students;

mysql视图里面可以传参数吗 mysql视图可以修改数据吗_数据库_05

-- 删除视图中的数据
create view view_test3 as select * from temp;
delete from view_test3 where num=101; 

select * from view_test3;
select * from temp;

mysql视图里面可以传参数吗 mysql视图可以修改数据吗_数据_06

4. 查询视图结构

desc view_test1;

mysql视图里面可以传参数吗 mysql视图可以修改数据吗_mysql视图里面可以传参数吗_07

5. 修改视图

5.1 方式1(OR REPLACE)

-- 修改视图view_test1中的查询语句中的条件为女
create OR REPLACE view view_test1
as 
select * from students where stu_gender='女';

5.2 方式2(alter)

alter view view_test1
as
select * from students where stu_gender='男';

6. 删除视图

删除数据表时会同时删除数据表中的数据，删除视图时不会影响原数据表中的数据。

drop view view_test3;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：python怎么将骨架提取出来 python骨架提取算法

下一篇：python 日期格式和时间合并 python的日期

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯