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

 刚安装好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;