1 VIEW 视图
- 视图:虚拟表,保存有实表的查询结果
1.1 创建视图
- 创建方法
CREATE VIEW view_name [(column_list)]AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
CREATE VIEW <视图名> AS <SELECT语句> #上述解释
WITH CHECK OPTION 的意思是,修改视图时,检查插入的数据是否符合 WHERE 设置的条件
- <视图名>:指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或视图同名。
- <SELECT语句>:指定创建视图的 SELECT 语句,可用于查询多个基础表或源视图。
示例:
MariaDB [hellodb]> create view view_classes as select *from classes;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SHOW CREATE VIEW view_classes\G;
*************************** 1. row ***************************
View: view_classes #view <name>
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_classes` AS select `classes`.`ClassID` AS `ClassID`,`classes`.`Class` AS `Class`,`classes`.`NumOfStu` AS `NumOfStu` from `classes`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
1.2 查看视图定义
SHOW CREATE VIEW view_name #只能看视图定义
SHOW CREATE TABLE view_name # 可以查看表和视图
示例:
MariaDB [hellodb]> SHOW CREATE VIEW view_classes\G;
*************************** 1. row ***************************
View: view_classes #view <name>
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_classes` AS select `classes`.`ClassID` AS `ClassID`,`classes`.`Class` AS `Class`,`classes`.`NumOfStu` AS `NumOfStu` from `classes`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [hellodb]> show create table view_classes\G;
*************************** 1. row ***************************
View: view_classes
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_classes` AS select `classes`.`ClassID` AS `ClassID`,`classes`.`Class` AS `Class`,`classes`.`NumOfStu` AS `NumOfStu` from `classes`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
1.3 删除视图
语法:
DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]
注意:视图中的数据事实上存储于“基表”中,因此,其修改操作也会针对基表实现;其修改操作受基表限制
2 FUNCTION 函数
- 函数:分为系统函数和自定义函数
- 系统函数参考:
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html - 自定义函数:user-defined function UDF,保存在mysql.proc表中
2.1 创建UDF语法
CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_nametype,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
说明:
参数可以有多个,也可以没有参数
必须有且只有一个返回值 #
2.2 查看函数列表
SHOW FUNCTION STATUS;
2.3 查看函数定义
SHOW CREATE FUNCTION function_name;
2.4 删除UDF
DROP FUNCTION function_name;
2.5 调用自定义函数语法
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 ;
2.5.1 MySQL中的变量
两种变量:系统变量和用户自定义变量
2.5.1.1 系统变量:MySQL数据库中内置的变量,可用@@var_name引用
- 用户自定义变量分为以下两种:
- 普通变量:在当前会话中有效,可用@var_name引用
- 局部变量:在函数或存储过程内才有效,需要用DECLARE 声明,之后直接用 var_name引用
2.5.1.2 自定义函数中定义局部变量语法
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
说明:局部变量的作用范围是在BEGIN…END程序中,而且定义局部变量语句必须在BEGIN…END的第一行定义
范例:
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 ;
2.5.1.3 为变量赋值语法
SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name
范例:
DECLARE x int;
SELECT COUNT(*) FROM tdb_name INTO x;
RETURN x;
END//
3 TRIGGER触发器
触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发、激活从而实现执行
3.1 创建触发器
语法:
CREATE [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body ?
**说明:**
trigger_name:触发器的名称 #
trigger_time:{ BEFORE | AFTER },表示在事件之前或之后触发 #
trigger_event::{ INSERT |UPDATE | DELETE },触发的具体事件 #
tbl_name:该触发器作用在表名#
范例:
CREATE TABLE student_info (
stu_id INT(11) NOT NULL AUTO_INCREMENT ,
stu_name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (stu_id)
);
CREATE TABLE student_count (
student_count INT(11) DEFAULT 0
);
INSERT INTO student_count VALUES(0);
CREATE TRIGGER trigger_student_count_insert
AFTER INSERT
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count+1;
CREATE TRIGGER trigger_student_count_delete
AFTER DELETE
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;
3.2 查看触发器
#在当前数据库对应的目录下,可以查看到新生成的相关文件:trigger_name.TRN,table_name.TRG
SHOW TRIGGERS
#查询系统表information_schema.triggers的方式指定查询条件,查看指定的触发器信息。
USE information_schema;
SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert';
3.3 删除触发器
DROP TRIGGER trigger_name;