mysql存储、function、触发器等实例

 1 DROP DATABASE IF EXISTS security;
 2 CREATE database security;
 3 USE security;
 4 CREATE TABLE users
 5 (
 6 id int(3) NOT NULL AUTO_INCREMENT,
 7 username varchar(20) NOT NULL,
 8 password varchar(20) NOT NULL,
 9 PRIMARY KEY (id)
10 );

 1 #--存储过程判断账号是否存在，不存在，插入用户信息，返回消息；存在，返回信息
 2 CREATE PROCEDURE users01 (
 3     IN un VARCHAR (20),
 4     IN pd VARCHAR (20),
 5     OUT result nvarchar (50)
 6 )
 7 BEGIN
 8 
 9 IF EXISTS (
10     SELECT
11         *
12     FROM
13         users
14     WHERE
15         username = un
16 ) THEN
17 
18 SET result = '对不起,账号已存在!';
19 
20 
21 ELSE
22     INSERT INTO users (username, PASSWORD)
23 VALUES
24     (un, pd);
25 
26 
27 SET result = '恭喜，用户信息插入成功!';
28 
29 
30 END
31 IF;
32 
33 SELECT
34     result;
35 
36 
37 END;
38 #--调用存储过程
39 CALL users01 (
40     'username01',
41     'password01' ,@result
42 );
43 #--删除存储过程
44 DROP PROCEDURE users01;
45 #--查看存储状态
46 SHOW PROCEDURE STATUS;

1 #---创建触发器
2 drop TRIGGER if exists test_tri;
3 CREATE TRIGGER test_tri BEFORE INSERT ON users FOR EACH row SET @num = @num + 1;
4 
5 #---实现触发器
6 SET @num = 0;
7 INSERT INTO users (username,password) VALUES ('username02','password02');
8 SELECT @num;

 1 #--创建方法/函数
 2 DROP FUNCTION
 3 IF EXISTS getusername;
 4 
 5 CREATE FUNCTION getusername (un VARCHAR(20)) RETURNS VARCHAR (300)
 6 BEGIN
 7     RETURN (
 8         SELECT
 9             PASSWORD
10         FROM
11             users
12         WHERE
13             username = un
14     );
15 
16 
17 END;
18 
19 #--调用函数
20 SELECT
21     getusername ('username01');

1 #--创建视图，查询视图
2 drop view if EXISTS test_view_1;
3 CREATE VIEW test_view_1 AS SELECT `username` FROM users;
4 select *from test_view_1;

truncate TABLE users;