MySQL第14章_视图(View)
CREATE DATABASE dbtest14;
USE dbtest14;
CREATE TABLE emps
AS
SELECT *
FROM atguigudb.`employees`;
CREATE TABLE depts
AS
SELECT *
FROM atguigudb.`departments`;
SELECT * FROM emps;
SELECT * FROM depts;
DESC emps;
DESC atguigudb.employees;
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;
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 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_emp4;
SHOW TABLES;
DESCRIBE vu_emp1;
SHOW TABLE STATUS LIKE 'vu_emp1';
SHOW CREATE VIEW vu_emp1;
SELECT * FROM vu_emp1;
SELECT employee_id,last_name,salary
FROM emps;
UPDATE vu_emp1
SET salary = 20000
WHERE employee_id = 101;
UPDATE emps
SET salary = 10000
WHERE employee_id = 101;
DELETE FROM vu_emp1
WHERE employee_id = 101;
SELECT employee_id,last_name,salary
FROM emps
WHERE employee_id = 101;
SELECT * FROM vu_emp_sal;
UPDATE vu_emp_sal
SET avg_sal = 5000
WHERE department_id = 30;
DELETE FROM vu_emp_sal
WHERE department_id = 30;
DESC vu_emp1;
CREATE OR REPLACE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,email
FROM emps
WHERE salary > 7000;
ALTER VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,email,hire_date
FROM emps;
SHOW TABLES;
DROP VIEW vu_emp4;
DROP VIEW IF EXISTS vu_emp2,vu_emp3;