Mysql视图+存储过程+函数的一些小案例

视图+存储过程+函数的一些小案例

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查询返回的值