mysql视图，存储过程与存储函数

第十四章 视图

CREATE TABLE emps
AS
SELECT* FROM atguigudb.employees;

CREATE TABLE depts
AS
SELECT*FROM atguigudb.departments;

DESC emps;
DESC depts;

#创建视图
#1.针对于单表
CREATE VIEW vu_emp1
AS SELECT employee_id,last_name,salary
FROM emps;

SELECT * FROM vu_emp1;

CREATE VIEW vu_emp2
AS
SELECT employee_id emp_id,last_name lname,salary #查询语句中字段的别名会作为视图中的字段显示
FROM emps
WHERE salary>8000;
SELECT * FROM vu_emp2;

CREATE VIEW vu_emp3(emp_id,NAME,monthly_sal)
#查询语句中字段的别名会依次匹配上面自定义的字段
AS
SELECT employee_id,last_name,salary
FROM emps
WHERE salary>8000;

SELECT * FROM vu_emp3;

#情况二 视图中的字段在基表中不存在
CREATE VIEW vu_emp_sal
AS
SELECT department_id,AVG(salary)avg_sal
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;

SELECT * FROM vu_emp_sal;

#针对于多表的情况
CREATE VIEW vu_emp_dept
AS
SELECT e.employee_id,e.department_id,d.department_name
FROM emps e JOIN depts d
ON e.department_id=d.department_id;

SELECT * FROM vu_emp_dept;
/* 规则为
create view 视图名
as
多表查询
*/

#利用视图对数据进行格式化
CREATE VIEW vu_emp_dept1
AS
SELECT CONCAT(e.last_name,‘(’,d.department_name,‘)’)emp_info
FROM emps e JOIN depts d
ON e.department_id=d.department_id;

SELECT *FROM vu_emp_dept1;

CREATE VIEW vu_emp4
AS SELECT employee_id,last_name
FROM vu_emp1;

SELECT * FROM vu_emp1;

#查看视图结构
DESC vu_emp1;
DESCRIBE vu_emp1;

#查看数据库中的表对象，视图对象
SHOW TABLES;

#查看视图的属性信息
SHOW TABLE STATUS LIKE’vu_emp1’;

#查看视图的详细定义信息
SHOW CREATE VIEW vu_emp1;

增删改视图中的数据，表中的数据也会跟着改变
视图的优点：1.操作简单 2.减少数据冗余 3.数据安全 4.适合灵活多变的业务需求 5.能够分解复杂的查询逻辑
视图的缺点：如果实际的数据表发生了变更，需要及时对相关的数据库进行维护；视图过多会造成数据库维护成本的问题

第十五章 存储过程与存储函数

CREATE TABLE employees
AS
SELECT *FROM atguigudb.employees;

CREATE TABLE departments
AS
SELECT *FROM atguigudb.departments;

#1.创建存储过程
#举例一：创建存储过程select_all_data(),查看employees表的所有数据
DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM employees;
END $

DELIMITER ;

CALL select_all_data();

#2.存储过程的调用（不同的存储过程调用方法不同，是否有参数）
CALL select_all_data();

#举例二：创建存储过程avg_employee_salary(),返回所有员工的平均工资
DELIMITER //
CREATE PROCEDURE avg_employee_salary ()
BEGIN
SELECT AVG(salary) AS avg_salary FROM emps;
END //
DELIMITER ;

#调用如下
CALL avg_employee_salary ()

#举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值
DELIMITER //
CREATE PROCEDURE show_max_salary()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ‘查看最高薪资’
BEGIN
SELECT MAX(salary) FROM emps;
END //
DELIMITER ;

#调用
CALL show_max_salary()

#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms FROM employees;
END //
DELIMITER ;

#调用带参数的存储过程
CALL show_min_salary(@ms)
#再查看
SELECT @ms;

#举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname
#输入员工姓名。
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
SELECT salary FROM employees WHERE last_name = empname;
END //
DELIMITER ;
#调用
SET @empname1=‘King’
CALL show_someone_salary(@empname1)
#或者
CALL show_someone_salary(‘King’);

#举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname
#输入员工姓名，用OUT参数empsalary输出员工薪资。
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
BEGIN
SELECT salary INTO empsalary FROM emps WHERE last_name = empname;
END //
DELIMITER ;
#调用
SET @empname=‘Abel’;
CALL show_someone_salary2(@empname,@empsalary)
SELECT @empsalary

#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员
#工姓名，输出领导的姓名。
DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGIN
SELECT last_name INTO empname FROM employees
WHERE employee_id = (SELECT manager_id FROM employees WHERE last_name=empname);
END //
DELIMITER ;
#调用
SET @empname=‘Abel’
CALL show_mgr_name(@empname)
SELECT @empname

#存储函数的使用
#创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型。
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name=‘Abel’)
END //
DELIMITER ;
#不是必须return 语句最前面有缩进，注意格式的变化
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = ‘Abel’);
END //
DELIMITER ;
#调用
SELECT email_by_name();

#不检验函数特征的设置
SET GLOBAL log_bin_trust_function_creators=1;
#例2：创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型
#为字符串型。
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
RETURN(SELECT email FROM employees WHERE employee_id=emp_id);
END //
DELIMITER ;

#调用方式一
SET @emp_id=103;
SELECT email_by_id(@emp_id);

#调用方式二
SELECT email_by_id(102);

#例3：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型
#为整型。
DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
BEGIN
RETURN(SELECT COUNT(*)FROM employees WHERE department_id=dept_id);
END //
DELIMITER ;

#调用方式一
SET @dept_id=50;
SELECT count_by_id(@dept_id);

#调用方式二
SELECT count_by_id(50);

#存储过程和函数的查看
#1.使用show create语句查看存储过程和存储函数的创建信息
SHOW CREATE PROCEDURE show_max_salary;
SHOW CREATE FUNCTION count_by_id;

#2.显示存储过程或存储函数的状态信息
SHOW PROCEDURE STATUS LIKE ‘show%’;

#3.举例：从Routines表中查询名称为CountProc的存储函数的信息，代码如下：
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME=‘count_by_id’ AND ROUTINE_TYPE = ‘FUNCTION’;

#查看数据库中所有的存储过程和存储函数
SELECT NAME FROM mysql.proc WHERE db=‘dbtest1’;

#显示所有存储过程的状态信息
SHOW PROCEDURE STATUS WHERE db=‘dbtest1’

#显示所有存储函数的状态信息
SHOW FUNCTION STATUS WHERE db=‘dbtest1’

#查看单个存储过程的状态信息
SHOW PROCEDURE STATUS LIKE ‘show_max_salary’