创建视图和存储过程

一、准备工作

create database book_manage_system

CREATE TABLE `book` (
  `BookId` int(50) NOT NULL AUTO_INCREMENT,
  `BookName` varchar(20) NOT NULL,
  `Writter` varchar(20) NOT NULL,
  `BookType` varchar(20) NOT NULL,
  `Price` double(255,2) NOT NULL,
  `IsBorrow` varchar(20) NOT NULL DEFAULT '否',
  PRIMARY KEY (`BookId`),
  KEY `BookId` (`BookId`,`BookName`)
) ENGINE=InnoDB AUTO_INCREMENT=20180921 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('20180901', '时间简史', '史蒂芬.霍金', '科技类', '98.00', '否');
INSERT INTO `book` VALUES ('20180903', '你坏', '大冰', '青春文学', '38.80', '否');
INSERT INTO `book` VALUES ('20180904', '阿弥陀佛么么哒', '大冰', '青春文学', '38.80', '是');
INSERT INTO `book` VALUES ('20180906', '他们最善良', '大冰', '青春文学', '38.80', '否');
INSERT INTO `book` VALUES ('20180911', '乖，摸摸头', '大冰', '青春文学', '38.60', '否');
INSERT INTO `book` VALUES ('20180915', '天工开物', '宋应星', '综合性', '78.00', '否');
INSERT INTO `book` VALUES ('20180916', '荣格', '尼采', '哲学', '58.00', '是');
INSERT INTO `book` VALUES ('20180917', 'Java从入门到精通', '国家863中部软件孵化器', '技术类', '98.00', '否');
INSERT INTO `book` VALUES ('20180918', '狂人日记', '鲁迅', '文学类', '62.00', '是');
INSERT INTO `book` VALUES ('20180919', '走遍中国', '阿纲', '人文自然', '36.90', '否');
INSERT INTO `book` VALUES ('20180920', '呐喊', '鲁迅', '文学类', '67.00', '否');

效果：

二、创建视图

-- 创建视图
CREATE VIEW view_book 
AS
SELECT BookName,Writter FROM book;

-- 查询视图
SELECT * FROM view_book

-- 默认情况下，创建的视图和基本表的字段是一样的，也可以通过指定视图字段的名称来创建视图
CREATE VIEW v_view_book
(v_BookName,v_Writter)
AS SELECT * FROM view_book;

SELECT * FROM v_view_book

-- MySQL 中也可以在两个以上的表中创建视图

-- DESCRIBE 可以用来查看视图，语法：DESCRIBE 视图名；
DESCRIBE v_view_book

-- DESCRIBE 一般情况下可以简写成 DESC，输入这个命令的执行结果和输入 DESCRIBE 是一样的。

三、创建存储过程

/*
delimiter是mysql分隔符，在mysql客户端中分隔符默认是分号（；）。
其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。 
如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的分隔符。
其中DELIMITER 定好结束符为"$$", 然后最后又定义为";", MYSQL的默认结束符为";"。

但有时候，不希望MySQL这么做。
在为可能输入较多的语句，且语句中包含有分号。 
这种情况下，就需要事先把delimiter换成其它符号，如//、$$或者;;。
创建一个存储过程，在创建该存储过程之前，将delimiter分隔符转换成符号“//”，最后在转换回符号“;


在mysql数据库客户端工具SQLyog中，
一般情况下，在客户端命令行中，如果一行SQL命令以“;”结尾，回车后，
会执行该条命令。如在创建存储过程，函数等，这时就需要全部输入一段命令，
最后再一次性执行，此时解释器就需要通过delimiter来处理，把分号换成其他符号，
如：“//”或“$$”,这样能够解释该段命令中的分号，实现一次性执行该段命令，否则会报错，无法执行。
*/

-- 创建存储过程,delimiter分隔符的意思
DELIMITER $$  -- 将语句的结束符号从分号；临时改为两个$
-- procedurename 存储过程名称
CREATE PROCEDURE bookProcedure()
BEGIN
-- 存储过程中要执行的sql语句
SELECT BookName,Writter FROM book;
END $$

DELIMITER;  -- 将语句的结束符号恢复为分号

-- 调用存储过程
CALL bookProcedure;

-- 查看存储过程  SHOW CREATE PROCEDURE [数据库.]存储过程名;
SHOW CREATE PROCEDURE book_manage_system.`bookProcedure`

-- 删除存储过程
DROP PROCEDURE IF EXISTS bookProcedure;


-- 测试
DELIMITER $$
CREATE PROCEDURE proc01()
BEGIN
CREATE TABLE student(id INT,`name` CHAR(10),age INT);
INSERT INTO student VALUES(1,'zhangsan',18);
INSERT INTO student VALUES(2,'lisi',18);
SELECT * FROM student;
END $$
DELIMITER;

CALL proc01;

-- 查看存储过程的具体信息
SHOW PROCEDURE STATUS LIKE '%proc01%'


-- 带参数的存储过程  IN代表传入的参数
DELIMITER $$
CREATE PROCEDURE proc02(IN stuname CHAR(20))
BEGIN
SELECT * FROM student WHERE `name`=stuname;
END $$
DELIMITER;

CALL proc02('zhangsan');


-- 出参out
DELIMITER $$
CREATE PROCEDURE proc06(OUT output INT)
BEGIN
DECLARE outnum INT DEFAULT 10;
SET output = outnum + 10;
END $$
DELIMITER;

SET @temp2 = 0;
CALL proc06(@temp2)
SELECT @temp2


-- 入参和出参一起使用
DELIMITER $$
CREATE PROCEDURE proc05(IN input VARCHAR(20) , OUT output INT)
BEGIN
SET output = (SELECT COUNT(*) FROM book WHERE BookType = input);
END $$
DELIMITER;

SET @temp = 0;
CALL proc05("青春文学",@temp);
SELECT @temp;

-- 查看数据默认的字符编码
SHOW VARIABLES LIKE 'character%'



-- 存储过程的控制语句
CREATE TABLE t(
	id INT(10)
);
INSERT INTO t VALUES(10);

DELIMITER $$
CREATE PROCEDURE proc03(IN innum INT)
BEGIN
DECLARE var INT;  -- 定义变量var为int类型
SET var = innum * 2; -- 设置变量的值等于传入的参数值乘2
IF var >= 10 THEN  -- 当var的值大于10时，id值会加1，否则减1 
UPDATE t SET id = id + 1;
ELSE
UPDATE t SET id = id -1;
END IF;
END $$
DELIMITER;

CALL proc03(8)

SELECT * FROM t;

CALL proc03(3)


-- 循环语句while......end while
DELIMITER $$
CREATE PROCEDURE proc04()
BEGIN -- 过程体以关键字begin开始
DECLARE var INT(10);
SET var = 0;
WHILE var < 6 DO
INSERT INTO t VALUES(var);
SET var = var + 1;
END WHILE; -- 结束while循环
END $$ -- 创建存储过程结束
DELIMITER; -- 重新修改默认结束符为原始的；

CALL proc04

四、可能报错

原因：在使用create database 创建数据库时，mysql默认使用Latin1编码和Latin1_swedish_d字符集

解决方法：将编码改为utf8，字符集改为utf8_general_ci