MySQL之视图与函数
VIEW 视图
视图：虚拟表，保存有实表的查询结果，相当于别名
利用视图,可以隐藏表的真实结构,在程序中利用视图进行查询,可以避免表结构的变化,而修改程序,降低程
序和数据库之间的耦合度
创建方法：
CREATE VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
查看视图定义：
SHOW CREATE VIEW view_name #只能看视图定义
SHOW CREATE TABLE view_name # 可以查看表和视图
删除视图：
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
注意：视图中的数据事实上存储于"基表"中，因此，其修改操作也会针对基表实现；其修改操作受基表
限制
MariaDB [hellodb]> SHOW TABLE STATUS LIKE 'v_st_co_sc'\G;
FUNCTION 函数
函数：分为系统内置函数和自定义函数
https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html
https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html
自定义函数：user-defined function UDF，保存在mysql.proc (MySQL8.0 中已经取消此表)表中
创建UDF语法
CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name
type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
说明：
参数可以有多个,也可以没有参数
无论有无参数，小括号（）是必须的
必须有且只有一个返回值
查看函数列表：
SHOW FUNCTION STATUS;
查看函数定义
SHOW CREATE FUNCTION function_name
删除UDF
DROP FUNCTION function_name
调用自定义函数语法
SELECT function_name(parameter_value,...)
范例：
#无参UDF
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";
#有参数UDF
DELIMITER //
CREATE FUNCTION deleteById(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
BEGIN
DELETE FROM students WHERE stuid = id;
RETURN (SELECT COUNT(*) FROM students);
END//
DELIMITER ;
范例: MySQL8.0 默认开启二进制不允许创建函数
#默认MySQL8.0开启二进制日志,而不允许创建函数
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
#打开此变量允许二进制日志信息函数创建
mysql> set global log_bin_trust_function_creators=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW FUNCTION STATUS like 'simple%'\G;
范例: Mariadb10.3 默认没有开启二进制日志,所以可以创建函数
#Mariadb默认没有开启二进制日志,所以可以创建函数
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin
[root@centos8 ~]#systemctl restart mariadb
MariaDB [hellodb]> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.000 sec)
#开启二进制功能后,也不能创建函数
MariaDB [hellodb]> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello
World";
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS
SQL DATA in its declaration and binary logging is enabled (you *might* want to
use the less safe log_bin_trust_function_creators variable)
MariaDB [hellodb]> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.001 sec)
#修改变量允许创建函数
MariaDB [hellodb]> set global log_bin_trust_function_creators=ON;
MariaDB [hellodb]> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "HelloWorld";
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> SHOW FUNCTION STATUS\G;
*************************** 1. row ***************************
Db: hellodb
Name: simpleFun
Type: FUNCTION
Definer: root@localhost
Modified: 2021-02-01 21:32:23
Created: 2021-02-01 21:32:23
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.001 sec)
MySQL中的变量
两种变量：系统内置变量和用户自定义变量
系统变量：MySQL数据库中内置的变量，可用@@var_name引用
用户自定义变量分为以下两种
普通变量：在当前会话中有效，可用@var_name引用
局部变量：在函数或存储过程内才有效，需要用DECLARE 声明，之后直接用 var_name引用
自定义函数中定义局部变量语法
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
说明：局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一
行定义
为变量赋值语法
SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name
范例:
DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED)
RETURNS SMALLINT
BEGIN
DECLARE a, b SMALLINT UNSIGNED;
SET a = x, b = y;
RETURN a+b;
END//
DELIMITER ;
范例:
.....
DECLARE x int;
SELECT COUNT(*) FROM tdb_name INTO x;
RETURN x;
END//
范例：自定义的普通变量
#方法1
MariaDB [hellodb]> select count(*) from students into @num ;
#方法2
MariaDB [hellodb]> select count(*) into @num from students;
#查看变量
MariaDB [hellodb]> select @num;
+------+
| @num |
+------+
| 24 |
+------+
1 row in set (0.000 sec)
