简介
➜ ~ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
创建视图
# 只是创建一个简单的视图
CREATE VIEW `user_view` AS
SELECT name FROM table_xxx
示例:
mysql> SELECT name FROM user_view;
+------+
| name |
+------+
| 1 |
| 3 |
| 5 |
| 6 |
| 7 |
+------+
5 rows in set (0.00 sec)
创建存储过程
# 该存储过程比较简单,接收一个varchar(16)的参数,插入table_x表
CREATE PROCEDURE `ADD_USER` (name VARCHAR(16))
BEGIN
IF name IS NULL THEN
SET name = '(empty)';
END IF;
INSERT INTO table_x(name) VALUES(name);
END
示例:
# 使用call 即可调用存储过程
mysql> call ADD_USER('xxx');
创建函数
# 该函数的功能比较简单,判断传入的int型参数大于或者小于5.
CREATE FUNCTION `THAN_FIVE` (id INT)
RETURNS VARCHAR(10)
BEGIN
DECLARE result VARCHAR(10) DEFAULT 0;
IF id < 5 THEN
SET result = '小于5';
END IF;
IF id > 5 THEN
SET result = '大于5';
END IF;
RETURN result;
END
示例:
mysql> select THAN_FIVE(id), id, name from b;
+---------------+----+------+
| THAN_FIVE(id) | id | name |
+---------------+----+------+
| 小于5 | 1 | 1 |
| 小于5 | 2 | 3 |
| 0 | 5 | 5 |
| 大于5 | 6 | 6 |
| 大于5 | 7 | 7 |
+---------------+----+------+
5 rows in set (0.00 sec)