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