视图+存储过程+函数的一些小案例
CREATE TABLE admin( -- 创建表admin1
id INT PRIMARY KEY AUTO_INCREMENT, -- id（字段名） int型 pk主键，auto_increment自增
username VARCHAR(20), -- username(字段名)（varchar类型长度为20）
PASSWORD VARCHAR(20) -- password
)
CREATE VIEW vi1 -- 创建视图
AS
SELECT * FROM admin
SELECT * FROM vi1; -- 查询创建的视图
DESC vi1; -- 查看视图结构
SHOW CREATE VIEW vi1 -- 查看试图详细定义
SHOW VARIABLES LIKE 'autocommit'; -- 事物的四个特性acid，手动开启，手动提交或回滚 显示当前状态
SET autocommit=0 -- 设置成不自动提交，0为不自动提交，1为自动提交
DELETE FROM admin WHERE admin.`id`=1 -- 删除id=1的数据
ROLLBACK; -- 回滚不提交此sql语句,commit执行提交执行此sql语句
SET autocommit =0; -- 设置手动提交
START TRANSACTION; -- 手动开启事物
DELETE FROM admin WHERE admin.`id`=4; -- 删除admin表中admin.id=4的语句
SAVEPOINT a; -- 设置撤销点，撤销点前的不回滚
DELETE FROM admin WHERE admin.`id`=5; -- 删除admin表中admin.id=5的语句
ROLLBACK TO a; -- 回滚到a节点，a节点以上不回滚
SELECT * FROM admin; -- 查询admin表
DELIMITER $$; -- 开始
CREATE PROCEDURE mypro_10() -- 创建存储过程mypro_10()
BEGIN -- 开始sql语句
SELECT * FROM admin; -- 查询admin表
END $$; -- 结束sql语句
DROP PROCEDURE IF EXISTS `mypro_12` -- 删除存储过程名为my_pro7
DROP PROCEDURE IF EXISTS `mypro_10`
DELIMITER $$; -- 开始
CREATE PROCEDURE mypro_7() -- 创建mypro_1()存储过程
BEGIN -- Begin---->end 中写sql语句
SELECT * FROM admin; -- 查询admin表
END $$; -- 结束
CALL mypro_7(); -- 调用mypro_1();存储过程
DELIMITER $$; -- 有参数的存储过程
CREATE PROCEDURE mypro_11(IN num INT) -- 传入（in）一个num 为Int型的数值，传出(out),ji
BEGIN
SELECT * FROM admin WHERE admin.`id`=num; -- 查询admin表中admin.id=传入的值
END $$;
SET @num=0; -- 因为传入的值为是int型所以将num设置成0字符串型设置成''
CALL mypro_11(5); -- 传入5进去
DELIMITER $$;
CREATE PROCEDURE mypro_13(OUT num1 INT) -- 传出（out）
BEGIN
SELECT COUNT(1) INTO num1 FROM admin; -- 将admi的总条数得到的值赋值给num1
END $$;
SET @num1=0; -- 设置传入的值为int因为(out num1 int)是int的值
CALL mypro_13(@num1); -- 调用mypro_13(@num1) 存储过程（传入@num）@num的值被赋值成查询的总条数
SELECT @num1; -- 查询@num1的参数
INSERT INTO admin (username,PASSWORD) VALUES("1111","1111");
INSERT INTO admin (username,PASSWORD) VALUES("1111","1111");
INSERT INTO admin (username,PASSWORD) VALUES("1111","1111"); -- 插入三条username相同的语句，下一步存储过程一入一出，查询username相同的个数
DELIMITER $$;
CREATE PROCEDURE mypro_12(IN num VARCHAR(20) ,OUT num1 INT) -- 一入一出的存储过程参数int型可不给长度。
BEGIN
SELECT COUNT(1) INTO num1 FROM admin WHERE admin.username=num; -- into把查询到的值赋值给num
END $$;
SET @num='';
SET @num1=0;
CALL mypro_12('1111',@num1);
SELECT @num1;
DELIMITER $$;
CREATE PROCEDURE mypro_14(INOUT num INT) -- inout 输入输出
BEGIN
SELECT COUNT(1) INTO num FROM admin WHERE admin.`id`>num; -- 查询admin.id>10的总数据并把得到的数据赋值给num既传入又传出
END $$;
SET @num=10;
CALL mypro_14(@num);
SELECT @num; -- 查询@num的值
DELIMITER $$;
CREATE FUNCTION myfun_1() -- 创建函数
RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; -- 函数内定义c值为0
SELECT COUNT(1) INTO c FROM admin WHERE admin.`id`; -- 查询所有的admin.id
RETURN c; -- 返回c值
END $$;
SELECT myfun_1(); -- 调用函数
DELIMITER $$; -- 函数为存储过程的out类型
CREATE FUNCTION myfun_3(username1 VARCHAR(20)) -- 函数只能返回一行
RETURNS VARCHAR(20)
BEGIN
SET @sal='';
SELECT admin.`id` INTO @sal FROM admin
WHERE `username`=username1 LIMIT 1; -- 只显示一行
RETURN @sal;
END $$;
SELECT myfun_2('dd');
SHOW CREATE FUNCTION myfun_2 -- 显示函数
DROP FUNCTION myfun_2 -- 删除函数
DELIMITER $$;
CREATE FUNCTION myfun_add(a FLOAT,b FLOAT)
RETURNS FLOAT -- 定义返回的类型为double
BEGIN
DECLARE sum_1 FLOAT; -- 定义局部变量sum_1和fan一样
SET sum_1=a+b;
RETURN sum_1;
END $$;
SELECT myfun_add(1,2) -- 返回1+2的值为3 存储过程和函数的区别，存储过程能存多条，函数只能存一条
DROP FUNCTION IF EXISTS fun_my5;
DELIMITER $$;
CREATE FUNCTION myfun_4(num FLOAT)
RETURNS VARCHAR(20) -- varchar(20) varchar记得定型，int不用定型
BEGIN
DECLARE degree VARCHAR(20) DEFAULT 'D';
IF num>=8 THEN SET degree='A';
ELSEIF num>13 THEN SET degree='B';
ELSEIF num>16 THEN SET degree='C';
ELSE SET degree='D';
END IF;
RETURN degree;
END $$;
SELECT myfun_4(15); -- A
DROP FUNCTION IF EXISTS fun_myf5;
DELIMITER $$;
CREATE FUNCTION fun_myf5(score FLOAT) RETURNS VARCHAR(10)
BEGIN
DECLARE degree VARCHAR(20) DEFAULT 'D';
IF score>=90 THEN SET degree='A';
ELSEIF score >80 THEN SET degree='B';
ELSEIF score >60 THEN SET degree='C';
ELSE SET degree='D';
END IF;
RETURN degree;
END $$;
SELECT fun_myf5(90);
DROP FUNCTION IF EXISTS fun_myf5;
DELIMITER $
CREATE FUNCTION fun_myf5(score FLOAT) RETURNS VARCHAR(10)
BEGIN
DECLARE degree VARCHAR(20) DEFAULT 'D';
# if结构
IF score>=90 THEN SET degree='A';
ELSEIF score>80 THEN SET degree='B';
ELSEIF score>60 THEN SET degree='C';
ELSE SET degree='D';
END IF;
RETURN degree;
END $
DELIMITER ;
SELECT fun_myf5(87);
SELECT fun_myf5(57); -- 存储过程和函数都可以使用分支结构
SELECT -- if,while只能写在存储结构或函数中，case即可写在存储结构也可不写
CASE
WHEN Salary>20000 THEN '高薪'
WHEN Salary>10000 THEN '低能'
ELSE '戴哥说：低能太多你们公司要完蛋了'
END
FROM employees
DELIMITER $$; -- while循环
CREATE PROCEDURE mypro_15()
BEGIN
DECLARE i INT DEFAULT 0; -- 默认为i的值为0
WHILE i<5 DO -- whiie循环
INSERT INTO admin (username,PASSWORD) VALUES(CONCAT('1234',i),CONCAT('1111',i));
SET i = i+1; -- 循环一次加一次，不满足条件时不循环
END WHILE; -- 结束循环 while--end while,case -- end , if--end if
END $$;
CALL mypro_15(); -- 存储过程用call 其他用select,但是存储过程有返回值的也需要用select查询返回的值
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
