MySQL(视图、变量与函数)

第1章 视图

1.1 视图概述

MySQL从5.0.1版本开始提供视图功能。

1、什么是视图

视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行。但是,视图只保存了sql逻辑,不保存查询结果,行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。

2、视图的好处

(1)视点集中

视图集中即是使用户只关心它感兴趣的某些特定数据和他们所负责的特定任务。

(2)简化操作

视图大大简化了用户对数据的操作。因为在定义视图时,若视图本身就是一个复杂查询的结果集,这样在每一次执行相同的查询时,不必重新写这些复杂的查询语句,只要一条简单的查询视图语句即可。使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对于用户来说,视图是已经过滤好的复合条件的结果集。

(3)定制数据

视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。因此,当有许多不同水平的用户共用同一数据库时,这显得极为重要。

(4)数据独立

一旦视图的结构确定了,可以屏蔽表结构变化对于用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。例如:在有些情况下,由于表中数据量太大,故在表的设计时常将表进行水平分割或垂直分割,但表的结构的变化却对应用程序产生不良的影响。视图可以使应用程序和数据库表在一定程度上独立。如果没有视图,应用一定是建立在表上的。有了视图之后,程序可以建立在视图之上,从而程序与数据库表被视图分割开来。

(5)安全性

视图可以作为一种安全机制。通过视图用户只能查看和修改他们所能看到的数据。其它数据库或表既不可见也不可以访问。如果某一用户想要访问视图的结果集,必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。

1.2 使用视图

1、创建视图

create view 视图名 As select的语句;

示例:

CREATE VIEW old_emp AS SELECT * FROM t_employee WHERE birthday < '1985-04-03';

MySQL5.5视图中不允许有from 后面的子查询,但oracle可以; MySQL5.7没问题。如果是5.5可以将子查询创建为一个视图,然后再这个视图基础上再建另一个视图的方式解决。

CREATE VIEW low_emp AS 
SELECT eid,ename,salary,temp.did,avg_salary 
FROM t_employee,(SELECT did,AVG(salary) AS avg_salary FROM t_employee GROUP BY did) temp 
WHERE t_employee.did = temp.did AND t_employee.`salary` < temp.avg_salary;
错误代码: 1349
View's SELECT contains a subquery in the FROM clause
CREATE VIEW avg_salary_view AS SELECT did,AVG(salary) AS avg_salary FROM t_employee GROUP BY did;

CREATE VIEW low_emp AS SELECT eid,ename,salary,avg_salary_view.did,avg_salary FROM t_employee ,avg_salary_view 
WHERE t_employee.did = avg_salary_view.did AND t_employee.`salary` < avg_salary_view.avg_salary;

2、查看视图定义

show create view 视图名;

select * from information_schema.views where TABLE_NAME = '视图名';

3、查看所有视图

从MySQL5.1开始,时候用show tables命令的时候不仅显示表的名称,也会显示视图的名称。不存在单独的show views命令。

show tables;

4、修改视图

create [or replace] view 视图名 As select的语句;
alter view 视图名 As select的语句 ;
create or replace VIEW old_emp AS SELECT * FROM t_employee WHERE birthday < '1985-04-03';
或
ALTER VIEW old_emp AS SELECT * FROM t_employee WHERE birthday < '1985-04-03';

5、删除视图

drop viewif exists】 视图名1,视图名2 …  【restrict|cascade;

示例:

CREATE VIEW young_emp AS SELECT eid,ename,birthday FROM t_employee WHERE birthday > '1985-04-03' ;
CREATE VIEW middle_emp1 AS SELECT eid,ename,birthday  FROM young_emp WHERE birthday < '1987-04-09' ;
CREATE VIEW middle_emp2 AS SELECT eid,ename,birthday  FROM young_emp WHERE birthday < '1987-04-09' ;

DROP VIEW IF EXISTS young_emp,middle_emp1,middle_emp2;

特别说明:

DROP VIEW IF EXISTS young_emp restrict;
  • restrict:表示被删除的视图如果被其他视图依赖,将不能删除;
  • cascade:表示被删除的视图如果被其他视图依赖,则与依赖它的视图一起删除;
  • 但是在mysql中指定了“restrict|cascade”也会被忽略。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bj9fDgGO-1596333854399)(imgs/image-20200406223731014.png)]

6、视图的使用

视图一旦定义,就可以当成“表”一样使用,进行查询了

CREATE OR REPLACE VIEW avg_salary_view AS SELECT did,AVG(salary) AS avg_salary FROM t_employee GROUP BY did;

SELECT t_department.did,dname,avg_salary_view.`avg_salary` 
FROM t_department,avg_salary_view
WHERE t_department.did = avg_salary_view.did

第2章 变量

任何程序中都有变量的概念。例如:操作系统有环境变量;Java中有成员变量和局部变量。它们都是代表某个可能变化的值,在程序中我们使用变量来代表那个可能变化的值,如果这个值需要修改,我们只要修改变量的值即可,否则需要修改很多地方的值。

double radius = 1.2;
double area = 3.14 * radius * radius;
double perimeter = 2 * 3.14 * radius;

在MySQL中也有变量。

  • 系统变量

    • 全局变量
    • 会话变量
  • 自定义变量

    • 用户变量
    • 局部变量

2.1 系统变量

系统变量就是mysql系统已经声明好的具有特定意义的变量。根据它们的作用范围,又分为全局变量和会话变量。

  • 全局变量:影响服务器整体操作,影响所有与mysql服务器连接的会话;当服务启动时,它将所有全局变量初始化为默认值。
  • 会话变量:只影响当前会话,和其他链接无关;

提示:以下所有操作把global换成session,或省略global,都是指会话变量

1、显示所有的全局/会话变量

SHOW GLOBAL VARIABLES; 

2、查看某个全局/会话变量

select @@global.变量名;
SHOW GLOBAL VARIABLES LIKE 'xxx';
SELECT @@global.autocommit;

SHOW GLOBAL VARIABLES LIKE '%character_set%';

3、修改某个全局/会话变量值

#修改全局变量
SET GLOBAL 变量名=值;        
SET @@global.变量名=值;
#修改全局变量SQL_WARNINGS的值
SET GLOBAL SQL_WARNINGS=ON;        
SET @@global.sql_warnings=OFF;

2.2 自定义变量

用户自己定义的,根据作用范围不同,又分为用户变量和局部变量;

  • 用户变量:作用域和会话变量一样,只对当前连接有效
  • 局部变量:只在begin/end语句块中有效

1、用户变量

声明用户变量,并且初始化

#方式一:使用set,使用set时可以用“=”或“:=”两种赋值符号赋值
set @用户变量名 = 值;
set @用户变量名 := 值;

#方式二:使用select,使用select时只能用“:=”赋值符号赋值
select @用户变量名 := 值 【from ...】;
select 值 into @用户变量名 【from ...】;

注意:一条select语句只能给一个变量赋值;

之后就可以使用select来查看它的值。如果该变量没有声明过,以下语句得到NULL值。

select @变量名

示例:

SET @count=0;
SET @count:=1;
SELECT @count;

示例:

SELECT @c := COUNT(*) FROM t_employee;
SELECT @c;

create table temp(empcount int);
insert into temp values(@c);

示例:

SELECT COUNT(*) INTO @nums FROM t_employee;
SELECT @nums;

示例:

SET @a = 1;
SET @b = 2;
SET @sum = @a + @b;
SELECT @sum;

2、局部变量

#声明局部变量
DECLARE 变量 数据类型;
DECLARE 变量 数据类型 DEFAULT 默认值;
#局部变量的赋值
#方式一:使用set,使用set时可以用“=”或“:=”两种赋值符号赋值
set 局部变量名 = 值;
set 局部变量名 := 值;

#方式二:使用select,使用select时只能用“:=”赋值符号赋值
select 局部变量名 := 值 【from ...】;
select 值 into 局部变量名 【from ...】;

可以使用select来查看它的值。

select 局部变量名

3、用户变量与局部变量区别

作用域定义和使用位置语法
用户变量当前连接当前会话的任意位置必须加@,不需要指定数据类型,没有声明默认为NULL
局部变量begin和end之间begin和end之间,且在所有复合的开头一般不用加@,需要指定数据类型,必须使用declare先声明后使用,否则报错

第3章 存储过程与函数

MySQL从5.0版本开始支持存储过程和函数。

存储过程和函数时事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器直接的传输,对于提高数据处理的效率是有好处的。

3.1 DELIMITER

通常我们在执行创建过程和函数之前,都会通过DELIMITER命令将语句的结束符从“;“修改成其他符号,常用的有”$$“或”//“,这样在过程和函数中的”;"就不会被MySQL解释成语句的结束而提示错误。

在存储过程或函数创建完毕,通过“DELIMITER ;"命令再将语句结束符改回”;“。

3.2 存储过程

1、创建存储过程

DELIMITER$$
CREATE PROCEDURE 存储过程名称(【参数列表】) 【characteristic ...】
BEGIN
	存储过程体(一组合法的SQL语句)
END$$
DELIMITER;

说明:

(1)如果存储过程体只有一句语句,BEGIN END可以省略;

(2)存储过程名不要与系统预定义的函数等对象重名

(3)关于存储过程和函数的【characteristic …】特性:

  • LANGUAGE SQL :说明下面过程体是使用SQL语音编写的,这条是系统默认的,为今后MySQL可能支持其他语言编写存储过程做准备
  • 【NOT】 DETERMINISTIC :如果是DETERMINISTIC ,表示确定的,即如果每次输入相同得到的输出也相同,如果是NOT DETERMINISTIC ,表示不确定的,默认是非确定的。
  • CONTAINS SQL 等:
    • NO SQL:表示子程序不包含SQL语句
    • CONTAINS SQL表示子程序包含读或写数据的语句
    • READS SQL DATA:表示子程序只包含读数据的语句
    • MODIFIES SQL DATA:表示子程序只包含写数据的语句。
    • 默认是CONTAINS SQL
  • SQL SECURITY xx:
    • SQL SECURITY DEFINER :用来指定子程序该用创建过程的用户的权限许可来执行,默认是它
    • SQL SECURITY INVOKER:用来指定子程序该用调用过程的用户的权限许可来执行
      • 当调用者有执行存储过程的权限,但是存储过程中涉及到使用某个表,而这个用户没有这个表的权限就会报错
  • COMMENT ‘注释信息’:为存储过程添加注释信息

2、修改存储过程

ALTER PROCEDURE 存储过程名称 [characteristic ...];

说明:

  • 修改存储过程,不影响存储过程功能,只是修改相关特性。
  • 修改存储过程和视图不同,不能使用create or replace,必须使用alter语句。

3、查看存储过程的定义

SHOW CREATE PROCEDURE 过程名;

select * from information_schema.routines where SPECIFIC_NAME = '过程名';

4、删除存储过程

DROP PROCEDURE 过程名;

5、调用存储过程

CALL PROCEDURE 存储过程名(【参数列表】)

6、示例

DELIMITER$$
CREATE PROCEDURE temp() NO SQL
BEGIN
	DECLARE c INT;
	SET c = 1;
	SELECT c;
END$$
DELIMITER;

CALL temp;

DROP PROCEDURE IF EXISTS temp;
DELIMITER$$
CREATE PROCEDURE look_all_emps() 
BEGIN
	SELECT * FROM t_employee;
END$$
DELIMITER;

CALL look_all_emps;

DROP PROCEDURE IF EXISTS look_all_emps;

7、带参数是存储过程

参数包括四要素:参数模式、参数类型、参数名、参数值,其中参数模式有:IN,OUT,INOUT

  • IN:该参数可以作为输入,也就是该参数需要调用者传入值
  • OUT:该参数可以作为输出,也就是该参数可以作为返回值
  • INOUT:该参数既可以作为输入又可以作为输出,也就是该参数既需要调用时传入值,又可以返回值

(1)带IN参数的存储过程

DELIMITER$$
CREATE PROCEDURE 存储过程名称(IN 参数名 数据类型【,其他参数】) 【characteristic ...】
BEGIN
	存储过程体(一组合法的SQL语句)
END$$
DELIMITER;

示例:

DELIMITER$$
CREATE PROCEDURE mysum(IN a INT,IN b INT) 
BEGIN
	SELECT a+b;
END$$
DELIMITER;

CALL mysum(1,2);
DELIMITER$$
CREATE PROCEDURE emp_salary(IN empname VARCHAR(20)) 
BEGIN
	SELECT ename,salary FROM t_employee WHERE ename = empname;
END$$
DELIMITER;

CALL emp_salary('孙红雷');

(2)带OUT参数的存储过程

DELIMITER$$
CREATE PROCEDURE 存储过程名称(OUT 参数名 数据类型【,其他参数】) 【characteristic ...】
BEGIN
	存储过程体(一组合法的SQL语句)
END$$
DELIMITER;

示例代码1:

DELIMITER$$
CREATE PROCEDURE emp_counts(OUT counts INT) 
BEGIN
	SELECT COUNT(*) INTO counts FROM t_employee;
END$$
DELIMITER;

SET @counts = 0 ;
CALL emp_counts(@counts);
SELECT @counts;

示例代码2:

DELIMITER$$
CREATE PROCEDURE emp_salary_info(OUT max_salary DOUBLE,OUT avg_salary DOUBLE,OUT min_salary DOUBLE) 
BEGIN
	SELECT MAX(salary) INTO max_salary FROM t_employee; 
	SELECT AVG(salary) INTO avg_salary FROM t_employee; 
	SELECT MIN(salary)INTO min_salary FROM t_employee;
END$$
DELIMITER;

或
DELIMITER$$
CREATE PROCEDURE emp_salary_info(OUT max_salary DOUBLE,OUT avg_salary DOUBLE,OUT min_salary DOUBLE) 
BEGIN
	SELECT MAX(salary),AVG(salary),MIN(salary) INTO max_salary, avg_salary ,min_salary FROM t_employee; 
END$$
DELIMITER;

SET @max_salary = 0;
SET @avg_salary = 0;
SET @min_salary = 0;

CALL emp_salary_info(@max_salary,@avg_salary ,@min_salary );
SELECT @max_salary,@avg_salary ,@min_salary;

示例代码3:

DELIMITER$$
CREATE PROCEDURE mysum(IN a INT,IN b INT,OUT result INT) 
BEGIN
	SELECT a+b INTO result;
END$$
DELIMITER;

SET @result = 0;
CALL mysum(1,2,@result);
SELECT @result;

(3)带INOUT参数的存储过程

DELIMITER$$
CREATE PROCEDURE 存储过程名称(INOUT 参数名 数据类型【,其他参数】) 【characteristic ...】
BEGIN
	存储过程体(一组合法的SQL语句)
END$$
DELIMITER;

示例代码:

DELIMITER$$
CREATE PROCEDURE old_emp(INOUT old_date DATE) 
BEGIN
	#删除了在old_date之前出生的员工
	DELETE FROM t_employee WHERE birthday < old_date; 
	#查询现在公司最老的员工的生日并赋值给old_date
	SELECT MIN(birthday) INTO old_date FROM t_employee;
END$$
DELIMITER;

SET @old_date = '1986-01-01';
CALL old_emp(@old_date);
SELECT @old_date;

示例代码2:

DELIMITER$$
CREATE PROCEDURE swap(INOUT a INT, INOUT b INT)
BEGIN
	DECLARE temp INT; #声明局部变量
	SET temp = a;
	SET a = b;
	SET b = temp;
END$$
DELIMITER;

SET @a = 1;
SET @b = 2;
CALL swap(@a,@b);
SELECT @a,@b;

3.3 函数

存储过程和函数的区别在于:

  • 存储过程的参数可以使用IN,OUT,INOUT类型,而函数的参数只能是IN类型。
  • 函数必须有返回值,而且只能有一个返回值,而存储过程可以通过OUT或INOUT参数,间接获取0~n个返回值;
  • 一般一个函数代表一个可重复使用的独立的功能,而存储过程一般只是代表一组包含复杂逻辑并且会被多次使用的SQL等。

1、创建函数

DELIMITER$$
CREATE FUNCTION 函数名称(【参数列表】) RETURNS 返回值类型 【characteristic ...】
BEGIN
	函数体(一组合法的SQL语句)
END$$
DELIMITER;

提示:

  • 函数体中必须有return 值;语句
  • 当然如果函数体只有一句语句,也可以省略“BEGIN END”
  • 参数前面不用加IN

2、查看函数定义

show create function 函数名;

select * from information_schema.routines where SPECIFIC_NAME = '函数名';

3、修改函数

alter function 函数名 【characteristic ...】;

说明:修改函数,不影响函数功能,只是修改相关特性。

4、删除函数

drop function 函数名;

5、调用函数

select 函数名(【参数】) ;

6、示例

DELIMITER$$
CREATE FUNCTION mysum(a INT, b INT)RETURNS INT
BEGIN
	RETURN a+b;
END$$
DELIMITER;

SELECT mysum(1,2);
DELIMITER$$
CREATE FUNCTION emp_count()RETURNS INT
BEGIN
	DECLARE counts INT DEFAULT 0;
	SELECT COUNT(*) INTO counts FROM t_employee;
	RETURN counts;
END$$
DELIMITER;

SELECT emp_count();

select * from information_schema.routines where SPECIFIC_NAME = ‘函数名’;


### 3、修改函数

```mysql
alter function 函数名 【characteristic ...】;

说明:修改函数,不影响函数功能,只是修改相关特性。

4、删除函数

drop function 函数名;

5、调用函数

select 函数名(【参数】) ;

6、示例

DELIMITER$$
CREATE FUNCTION mysum(a INT, b INT)RETURNS INT
BEGIN
	RETURN a+b;
END$$
DELIMITER;

SELECT mysum(1,2);
DELIMITER$$
CREATE FUNCTION emp_count()RETURNS INT
BEGIN
	DECLARE counts INT DEFAULT 0;
	SELECT COUNT(*) INTO counts FROM t_employee;
	RETURN counts;
END$$
DELIMITER;

SELECT emp_count();
©️2020 CSDN 皮肤主题: 1024 设计师:上身试试 返回首页