mysql 修改视图没所属 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’