MySQL 뷰 생성 CREATE VIEW

Jmnote bot (토론 | 기여)님의 2022년 9월 21일 (수) 16:33 판 (봇: 자동으로 텍스트 교체 (-</source> +</syntaxhighlight>, -<source +<syntaxhighlight ))
(차이) ← 이전 판 | 최신판 (차이) | 다음 판 → (차이)

1 개요[ | ]

MySQL CREATE VIEW
MySQL 뷰 생성
CREATE VIEW 뷰이름
AS SELECT문

2 예시[ | ]

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)
mysql> CREATE VIEW OrdersForEmployee6 AS
    -> SELECT OrderID,CustomerID,OrderDate FROM Orders WHERE EmployeeID=6;
Query OK, 0 rows affected (0.00 sec)
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 실습[ | ]

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)
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)
테이블명 변경 테스트
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 참고[ | ]

문서 댓글 ({{ doc_comments.length }})
{{ comment.name }} {{ comment.created | snstime }}