Mysql——表、存储过程、函数、事件的创建

怼烎@ 2022-03-22 02:49 279阅读 0赞

刚安装好mysql及sqlyog,迫不及待的试一下数据库的基本操作，发现还是挺好用的。

/**********创建表***********/
    CREATE TABLE `account` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(20) NOT NULL,
      `money` DOUBLE DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    INSERT INTO `account` VALUES ('1', 'a', '1000');
    INSERT INTO `account` VALUES ('2', 'b', '1000');
    INSERT INTO `account` VALUES ('3', 'c', '1000');
    
    
    
    CREATE TABLE `userInfo` (
      `name` VARCHAR(20) NOT NULL,
      `age` INT DEFAULT NULL,
      `gender` VARCHAR(20) DEFAULT NULL
    ) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    INSERT INTO `userInfo` VALUES ('a', 20,'man');
    INSERT INTO `userInfo` VALUES ('b', 22,'man');
    INSERT INTO `userInfo` VALUES ('c', 30,'woman');
    
    
    /**********创建视图***********/
    CREATE
     VIEW v_user_info AS
        SELECT 
    	a.name AS username,
    	a.money AS salary,
    	b.age AS age,
    	b.gender AS gender
        FROM
          account a,
          userInfo b
        WHERE
            a.name = b.name
    	
    
    /**********创建存储过程***********/	
    DELIMITER $$
    CREATE
        PROCEDURE `test`.`v_salary1`(username VARCHAR(20),salaryNew DOUBLE)
        BEGIN
    	-- select * from `account` where `account`.name=username;
    	UPDATE `account`  SET `account`.`money`=salaryNew WHERE `account`.name=username;
        END$$
    DELIMITER ;
    
    /*********存储过程调用命令********/
    CALL v_salary1('b',1200)
    
    
    
    /**********创建函数***********/
    DELIMITER $$
    CREATE
        /*[DEFINER = { user | CURRENT_USER }]*/
        FUNCTION `test`.`f_stringAdd`(a VARCHAR(100),b VARCHAR(100))
        RETURNS VARCHAR(200)
        BEGIN
    	RETURN CONCAT(a,b);
        END$$
    DELIMITER ;
    
    /*********函数调用命令********/
    SELECT f_stringAdd('1234','afds')
    
    
    /**********创建事件***********/
    DELIMITER $$ 
     
    CREATE	EVENT `test`.`e_salaryAdd`
    ON SCHEDULE EVERY 5 SECOND ON COMPLETION PRESERVE
    DO
    	BEGIN
    	    UPDATE `account` SET `account`.`money`=`account`.`money`+1 WHERE `account`.`name`='a';
    	END$$
    
    DELIMITER ;
    
    /**********开启事件***********/
    SHOW VARIABLES LIKE 'event_scheduler';
    SET GLOBAL event_scheduler = 1;