MySQL - MySQL 8.0(二)基本操作：对象

最新推荐文章于 2023-03-04 09:13:58 发布

写虫师

最新推荐文章于 2023-03-04 09:13:58 发布

阅读量590

点赞数 1

分类专栏： # MySQL 文章标签： MySQL MySQL 8.0

写虫师

本文链接：https://blog.csdn.net/nangy2514/article/details/96993505

版权

MySQL 专栏收录该内容

19 篇文章 6 订阅

订阅专栏

此学习文是基于MySQL 8.0写的
得益于大神朋友的悉心指导解决不少坑，才写出此文，向大神奉上膝盖

表（TABLE）

参考文章	备注
MySQL - MySQL 8.0基本操作：数据	单独拎出来写写

存储过程（PROCEDURE）

假设你需要在MySQL中执行一系列语句，可以将所有语句封装在单个程序中，并在需要时调用这个程序，而不是每次发送所有SQL语句。存储过程处理的是一组SQL语句，且没有返回值。
除了SQL语句，还可以使用变量来存储结果并在存储过程中执行程序化的内容。例如，你可以使用IF/CASE子句、逻辑操作和WHILE循环。

存储的函数（function）和过程（procedure）都称为存储例程（routine）。
要创建存储过程，你应该具有CREATE ROUTINE权限。
存储函数具有返回值。
存储过程没有返回值。
所有代码都写在BEGIN和END块之间。
存储函数可以直接在SELECT语句中调用。
可以使用CALL语句调用存储过程。

1. 先授权（管理员用户）

要使用存储过程，至少要将execute权限授予相应用户

alter routine：修改与删除存储过程/函数
create routine：创建存储过程/函数
execute：调用存储过程/函数

mysql> grant execute, create routine, alter routine on employees.* to 'emp_read_write'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'emp_read_write'@'%';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for emp_read_write@%                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT FILE ON *.* TO `emp_read_write`@`%`                                                                             |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `employees`.* TO `emp_read_write`@`%` |
| GRANT `app_read`@`%`,`app_write`@`%` TO `emp_read_write`@`%`                                                          |
+-----------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

2. 创建存储过程（普通用户）

由于存储例程中的语句应以分隔符（;）结尾，因此必须更改MySQL的分隔符，以便MySQL不会用正常语句解释存储例程中的SQL语句。创建过程结束后，可以将分隔符更改回默认值。

2.1. 登录数据库

# 登录用户
# nangy @ nangy-vm in ~ [15:22:48] 
$ mysql -u emp_read_write -p

# 切换数据库
mysql> use employees;

2.2. 清理旧过程

在创建之前，如果存在任何相同名字的存储过程，则删除已经存在的存储过程

drop procedure if exists schema.proc_name;

mysql> drop procedure if exists proc_create_employee;
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.3. 修改分隔符为`$$`

分隔符可以自定义，只要和默认的不冲突就可以

mysql> delimiter $$

2.4. 创建存储过程

语法

create procedure proc_name (out, in, ……)
begin
	# sql语句
end $$

写个栗子，养成好习惯，加上前缀proc_方便后面开发人员维护时好识别

mysql> create procedure employees.proc_create_employee(out new_emp_no int, in birth_date date, in first_name varchar(14), in last_name varchar(16), in gender enum('M','F'))
    -> begin
    -> /* 定义变量 declare 变量名 类型(长度) default 默认值 */
    -> declare emp_dept_no char(4);
    -> 
    -> /* 查询当前最大值，并赋值给new_emp_no */
    -> select max(emp_no) into new_emp_no from employees.employees;
    -> /* 增加new_emp_no，set 变量名=值 */
    -> set new_emp_no = new_emp_no + 1;
    -> 
    -> /* 插入到目标表，curdate()当前日期 */
    -> insert into employees.employees values (new_emp_no, birth_date, first_name, last_name, gender, curdate());
    -> end $$
Query OK, 0 rows affected (0.14 sec)

要创建存储过程，可以这样操作：

将上述代码粘贴到命令行客户端中
将其保存在文件中，并使用mysql -u {user} -p employees < stored_procedure.sql将其导人MySQL中
使用SOURCE从文件加载mysql> source stored_procedure.sql

2.5. 把分隔符改回默认`;`

mysql> delimiter ;

2.6. 查看存储过程

查看全部过程：show procedure status\G;
查看过程定义：show create procedure schema.proc_name\G;

mysql> show create procedure employees.proc_create_employee\G;
*************************** 1. row ***************************
           Procedure: proc_create_employee
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`emp_read_write`@`%` PROCEDURE `proc_create_employee`(out new_emp_no int, in birth_date date, in first_name varchar(14), in last_name varchar(16), in gender enum('M','F'))
begin

declare emp_dept_no char(4);


select max(emp_no) into new_emp_no from employees.employees;

set new_emp_no = new_emp_no + 1;


insert into employees.employees values (new_emp_no, birth_date, first_name, last_name, gender, curdate());
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

3. 调用存储过程（普通用户）

使用CALL procedure_name (OUT变量，IN值)语句和例程的名称调用存储过程。

call procedure_name (@out, 'in1', 'in2',……);

mysql> call employees.proc_create_employee(@new_emp_no, '2019-01-01', 'John', 'Smith', 'M');
Query OK, 1 row affected (0.00 sec)

mysql> select @new_emp_no;
+-------------+
| @new_emp_no |
+-------------+
|      500000 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from employees.employees where emp_no = (select max(emp_no) from employees.employees);
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500000 | 2019-01-01 | John       | Smith     | M      | 2019-07-23 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

4. 安全问题

上面的存储过程使用具有读写权限的用户创建，然而具有执行权限的只读用户，也可以通过调用存储过程来写入，这就产生了安全问题

# emp_read只读权限
mysql> show grants for emp_read;
+----------------------------------------------------------+
| Grants for emp_read@%                                    |
+----------------------------------------------------------+
| GRANT FILE ON *.* TO `emp_read`@`%`                      |
| GRANT SELECT, EXECUTE ON `employees`.* TO `emp_read`@`%` |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

# 也执行成功了
mysql> call employees.proc_create_employee(@new_emp_no, '2019-01-01', 'John', 'Smith', 'M');
Query OK, 1 row affected (0.00 sec)

mysql> select @new_emp_no;
+-------------+
| @new_emp_no |
+-------------+
|      500001 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from employees.employees where emp_no = (select max(emp_no) from employees.employees);
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500001 | 2019-01-01 | John       | Smith     | M      | 2019-07-23 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

为了避免这种问题，如果存储过程的SQL SECURITY创建为INVOKER，则只读用户不能修改数据。
根据存储例程的定义：

DEFINER子句指定存储例程的创建者。如果没有指定，则获取当前用户。
SQL SECURITY子句指定存储例程的执行上下文。它可以是DEFINER或INVOKER。
DEFINER：即使只有EXECUTE权限的用户也可以调用并获取存储例程的输出，而不管该用户是否具有对基础表的操作权限。如果DEFINER具有权限，那就足够了。
INVOKER（推荐）：安全上下文被切换到调用存储例程的用户。在这种情况下，调用者应具有读写基础表权限才能执行成功。

CREATE [DEFINER = '{用户名}'@'{主机地址}'] PROCEDURE schema.proc_name()
SQL SECURITY [DEFINER | INVOKER]
BEGIN
/* 计算逻辑 */
END $$

# 重新创建后，只读用户执行失败，无写入权限
mysql> call employees.proc_create_employee(@new_emp_no, '2019-01-01', 'John', 'Smith', 'M');
ERROR 1142 (42000): INSERT command denied to user 'emp_read'@'localhost' for table 'employees'

延伸阅读 https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

函数（FUNCTION）

就像存储过程一样，我们可以创建存储函数。二者的主要区别是，函数应该有一个返回值，并且可以在SELECT中调用函数。通常，创建存储函数是为了简化复杂的计算。
先授权，和存储过程一样

1. 编写函数

语法

create function func_name(参数 数据类型) returns 返回值类型
deterministic	# 确定性函数
begin 
	# sql语句
end $$

写个栗子，养成好习惯，加上前缀func_方便后面开发人员维护时好识别

# nangy @ nangy-vm in ~/Documents/MysqlFiles [17:27:17] 
$ vim func_test.sql

drop function if exists employees.func_test;  -- 清理旧函数
delimiter $$  -- 修改分隔符为$$
create function employees.func_test(name varchar(50)) returns varchar(50)
deterministic
begin
	declare str varchar(100) default '';
	set @tablename = name;
	set str = concat('返回测试数据：', @tablename);
	return str;
end $$
delimiter ;  -- 修改回默认分隔符；

1.1. TIP

在函数创建中给出DETERMINISTIC（确定性函数）关键字非常重要。
如果一个例程对于相同的输入参数总是产生相同的结果，则认为该函数为DETERMINISTIC（即输入和输出为一对一的固定关系），否则为NOT DETERMINISTIC。
如果在例程定义中既未给出DETERMINISTIC，也未给出NOT DETERMINISTIC，则默认是NOT DETERMINISTIC。
如果要声明一个函数是确定性的，则必须明确指定DETERMINISTIC。
如果将一个NON DETERMINISTIC例程声明为DETERMINISTIC，可能导致意想不到的结果，因为它会导致优化器选择不正确的执行计划。将DETERMINISTIC例程声明为NON DETERMINISTIC可能会导致可用的优化未被使用，降低性能。

2. 部署函数

source /路劲/文件名.sql;

mysql> source /home/nangy/Documents/MysqlFiles/func_test.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

2.1. 查看函数

查看全部函数：show function status\G;
查看函数定义：show create function schema.func_name\G;

3. 修改函数

可以使用ALTER FUNCTION语句来修改自定义函数的某些相关特征。若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。

4. 执行函数

select func_name();

mysql> select employees.func_test('表名');
+-------------------------------+
| employees.func_test('表名')   |
+-------------------------------+
| 返回测试数据：表名               |
+-------------------------------+
1 row in set (0.00 sec)

5. 安全问题

类比存储过程，创建时指定SQL SECURITY [DEFINER | INVOKER]就可以

6. 内置函数

MySQL提供了许多内置函数。我们前面已经使用CORDATE()函数获取了当前日期、CONCAT()拼接字符串，等等。

延伸阅读 https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html

触发器（TRIGGER）

触发器用于在触发器事件之前或之后激活某些内容。例如，可以在插入表中的每行之前或更新的每行之后激活触发器。
触发器动作时间可以是BEFORE或AFTER，表示触发器是在每行要修改之前或之后被激活。
触发事件可以是INSERT、DELETE或UPDATE。

INSERT：无论何时通过INSERT、REPLACE或LOAD DATA语句插入新行，都
会激活INSERT触发事件。
UPDATE：通过UPDATE语句激活UPDATE触发事件。
DELETE：通过DELETE或REPLACE语句激活DELETE触发事件。

触发器经常用于加强数据的完整性约束和业务规则等。触发器创建语法四要素：

触发时间(after/before)
监视事件(insert/update/delete)
监视地点(table_name)
触发事件(insert/update/delete)

从MySQL5.7 开始，一个表可以同时具有多个触发器。例如，一个表可以有两个BEFORE INSERT触发器。必须使用FOLLOWS或PRECEDES指定先行的触发器。

1. 编写触发器

语法

create trigger trigger_name
after/before insert/update/delete on table_name
for each row  # 这句话在mysql是固定的
begin
    # sql语句;
end;

写个栗子，养成好习惯，加上前缀trigger_方便后面开发人员维护时好识别

# nangy @ nangy-vm in ~/Documents/MysqlFiles [11:34:52] 
$ vim trigger_test.sql

drop trigger if exists employees.salary_round;
delimiter $$
create triggr employees.salary_round before insert on employees.salaries
for each row
begin
	set new.salary = round(new.salary);
end $$
delimiter ;

2. 部署触发器

source /路劲/文件名.sql;

mysql> source /home/nangy/Documents/MysqlFiles/trigger_test.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

2.1. 查看触发器

查看全部触发器：show triggers status\G;
查看触发器定义：show create trigger schema.trigger_name\G;

3. 执行触发器

对相应的表执行insert/update/delete操作，将会执行触发器

4. 修改触发器

4.1. TIP

应先删除触发器，后再删除表。

延伸阅读：
https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
[数据库] Navicat for MySQL触发器更新和插入操作

视图（VIEW）

视图是一个基于SQL语句的结果集的虚拟表。它就像一个真正的表一样也具有行和列，但是有一些限制。视图隐藏了SQL的复杂性，更重要的是，它提供了额外的安全性。

1. 语法

养成好习惯，加上前缀v_方便后面开发人员维护时好识别。

CREATE 
   [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name] 
AS
[SELECT  statement]

查看处理算法
算法属性允许您控制MySQL在创建视图时使用的机制，MySQL提供了三种算法：MERGE，TEMPTABLE和UNDEFINED。
使用MERGE算法，MySQL首先将输入查询与定义视图的SELECT语句组合成单个查询。然后MySQL执行组合查询返回结果集。如果SELECT语句包含集合函数(如MIN，MAX，SUM，COUNT，AVG等)或DISTINCT，GROUP BY，HAVING，LIMIT，UNION，UNION ALL，子查询，则不允许使用MERGE算法。如果SELECT语句无引用表，则也不允许使用MERGE算法。如果不允许MERGE算法，MySQL将算法更改为UNDEFINED。请注意，将视图定义中的输入查询和查询组合成一个查询称为视图分辨率。
使用TEMPTABLE算法，MySQL首先根据定义视图的SELECT语句创建一个临时表，然后针对该临时表执行输入查询。因为MySQL必须创建临时表来存储结果集并将数据从基表移动到临时表，所以TEMPTABLE算法的效率比MERGE算法效率低。另外，使用TEMPTABLE算法的视图是不可更新的。
当您创建视图而不指定显式算法时，UNDEFINED是默认算法。 UNDEFINED算法使MySQL可以选择使用MERGE或TEMPTABLE算法。MySQL优先使用MERGE算法进行TEMPTABLE算法，因为MERGE算法效率更高。
查看名称
在数据库中，视图和表共享相同的命名空间，因此视图和表不能具有相同的名称。另外，视图的名称必须遵循表的命名规则。
SELECT语句
在SELECT语句中，可以从数据库中存在的任何表或视图查询数据。
SELECT语句必须遵循以下几个规则：
SELECT语句可以在WHERE子句中包含子查询，但FROM子句中的不能包含子查询。
SELECT语句不能引用任何变量，包括局部变量，用户变量和会话变量。
SELECT语句不能引用准备语句的参数。
视图定义中不能引用TEMPORARY表（临时表），不能创建TEMPORARY视图。

2. 查看视图定义

mysql> desc [database_name].[view_name];

3. 列出所有视图

mysql> SHOW TABLES;
mysql> SHOW FULL TABLES;
mysql> SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';

事件（EVENT）（未完成）

写虫师

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL - MySQL 8.0(二)基本操作：对象

文章目录存储过程1. 先授权（管理员用户）2. 创建存储过程（普通用户）2.1. 登陆数据库2.2. 清理旧过程2.3. 修改分隔符为`$$`2.4. 创建存储过程2.5. 把分隔符改回默认`;`2.6. 验证一下3. 调用存储过程（普通用户）4. 安全问题函数触发器视图事件
复制链接

扫一扫