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 STATUSG;
*************************** 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)