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;
USE dbtest14;
CREATE OR REPLACE VIEW employee_vu(lname,emp_id,dept_id)
AS
SELECT last_name,employee_id,department_id
FROM emps;
DESC employee_vu;
SELECT * FROM employee_vu;
CREATE OR REPLACE VIEW employee_vu(lname,emp_id,dept_id)
AS
SELECT last_name,employee_id,department_id
FROM emps
WHERE department_id = 80;
CREATE TABLE emps
AS
SELECT * FROM atguigudb.employees;
DESC emps;
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM emps
WHERE phone_number LIKE '011%';
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,email,phone_number,salary
FROM emps
WHERE phone_number LIKE '011%'
AND email LIKE '%e%';
SELECT * FROM emp_v1;
DESC emps;
INSERT INTO emp_v1
VALUES('Tom','tom@126.com','01012345');
SELECT *
FROM emp_v1;
UPDATE emp_v1
SET salary = salary + 1000;
DELETE FROM emp_v1
WHERE last_name = 'Olsen';
CREATE OR REPLACE VIEW emp_v2(dept_id,max_sal)
AS
SELECT department_id,MAX(salary)
FROM emps
GROUP BY department_id
HAVING MAX(salary) > 12000;
SELECT * FROM emp_v2;
不可以!
INSERT INTO emp_v2(dept_id,max_sal)
VALUES(4000,20000);
DROP VIEW IF EXISTS emp_v1,emp_v2;
SHOW TABLES;