MySQL 뷰 생성 CREATE VIEW

1 개요[ | ]

MySQL CREATE VIEW
MySQL 뷰 생성

sql

Copy

CREATE VIEW 뷰이름
AS SELECT문

2 예시[ | ]

Console

Copy

mysql> SELECT * FROM Orders;
+---------+------------+------------+------------+-----------+
| OrderID | CustomerID | EmployeeID | OrderDate  | ShipperID |
+---------+------------+------------+------------+-----------+
|   10354 |         58 |          8 | 1996-11-14 |         3 |
|   10355 |          4 |          6 | 1996-11-15 |         1 |
|   10356 |         86 |          6 | 1996-11-18 |         2 |
+---------+------------+------------+------------+-----------+
3 rows in set (0.00 sec)

Console

Copy

mysql> CREATE VIEW OrdersForEmployee6 AS
    -> SELECT OrderID,CustomerID,OrderDate FROM Orders WHERE EmployeeID=6;
Query OK, 0 rows affected (0.00 sec)

Console

Copy

mysql> SELECT * FROM OrdersForEmployee6;
+---------+------------+------------+
| OrderID | CustomerID | OrderDate  |
+---------+------------+------------+
|   10355 |          4 | 1996-11-15 |
|   10356 |         86 | 1996-11-18 |
+---------+------------+------------+
2 rows in set (0.00 sec)

3 실습[ | ]

사전작업1: 직원 테이블 샘플
사전작업2: 부서 테이블 샘플

Console

Copy

mysql> CREATE VIEW emp_dept AS
    -> SELECT e.*, d.name department_name FROM employee e, department d
    -> WHERE d.id = e.department_id;
Query OK, 0 rows affected (0.07 sec)

Console

Copy

mysql> SELECT * FROM emp_dept;
+----+-----------+---------------+------------+-----------------+
| id | name      | department_id | birth_date | department_name |
+----+-----------+---------------+------------+-----------------+
|  1 | 한놈      |             1 | 1999-01-01 | 영업팀          |
|  2 | 두시기    |             2 | 2000-01-01 | 개발팀          |
|  3 | 석삼      |             2 | 1999-01-01 | 개발팀          |
+----+-----------+---------------+------------+-----------------+
3 rows in set (0.00 sec)

테이블명 변경 테스트

Console

Copy

mysql> RENAME TABLE employee TO emplyoee2;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM emp_dept;
ERROR 1356 (HY000): View 'test.emp_dept' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

→ 원본 employee 테이블을 참조할 수 없어서 오류

4 같이 보기[ | ]

5 참고[ | ]