MySQL 뷰와 가상 테이블

소개

이 랩에서는 MySQL 뷰 (view) 를 다루는 방법을 배우게 됩니다. 뷰는 SQL 문의 결과 집합을 기반으로 하는 가상 테이블입니다. 뷰는 복잡한 쿼리를 단순화하고, 데이터 복잡성을 숨기며, 데이터 액세스에 대한 추상화 수준을 제공하는 데 유용합니다. MySQL 뷰를 생성, 업데이트, 쿼리 및 삭제하는 방법을 배우게 됩니다.

먼저 MySQL 서버에 연결하고 company라는 데이터베이스를 생성한 다음, employees 테이블로 채우는 것부터 시작합니다. 그런 다음 'Sales' 부서의 직원만 표시하도록 employees 테이블을 필터링하는 sales_employees라는 뷰를 생성합니다. 뷰의 구조를 확인하고 일반 테이블처럼 쿼리하는 방법을 배우게 됩니다.

MySQL 에 연결하고 데이터베이스/테이블 생성하기

이 단계에서는 MySQL 서버에 연결하고 랩에 필요한 데이터베이스와 테이블을 설정합니다. 여기에는 company라는 데이터베이스와 그 안에 employees 테이블을 생성하고 샘플 데이터로 채우는 작업이 포함됩니다.

먼저 LabEx VM 에서 터미널을 엽니다. 이미 ~/project 디렉토리에 있어야 합니다.

root 사용자로 MySQL 서버에 연결합니다. sudo 권한이 있고 MySQL 의 root 사용자가 auth_socket 플러그인을 사용하도록 구성되어 있으므로 sudo를 사용하여 암호 없이 연결할 수 있습니다.

sudo mysql -u root

이제 MySQL 쉘에 접속했습니다. 프롬프트가 mysql>으로 변경됩니다.

MySQL 쉘 내에서 company라는 데이터베이스를 생성합니다.

CREATE DATABASE company;

이후 명령이 이 데이터베이스 내에서 작동하도록 company 데이터베이스로 전환합니다.

USE company;

이제 직원 정보를 저장할 employees라는 테이블을 생성합니다.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department VARCHAR(50)
);

employees 테이블에 샘플 데이터를 삽입합니다.

INSERT INTO employees (id, first_name, last_name, salary, department) VALUES
(1, 'John', 'Doe', 60000.00, 'Sales'),
(2, 'Jane', 'Smith', 75000.00, 'Marketing'),
(3, 'Robert', 'Jones', 50000.00, 'Sales'),
(4, 'Emily', 'Brown', 80000.00, 'Engineering'),
(5, 'Michael', 'Davis', 65000.00, 'Marketing');

모든 행을 선택하여 employees 테이블의 데이터를 확인할 수 있습니다.

SELECT * FROM employees;

출력에는 삽입된 직원 데이터가 표시됩니다.

+----+------------+-----------+----------+-------------+
| id | first_name | last_name | last_name | department  |
+----+------------+-----------+----------+-------------+
|  1 | John       | Doe       | 60000.00 | Sales       |
|  2 | Jane       | Smith     | 75000.00 | Marketing   |
|  3 | Robert     | Jones     | 50000.00 | Sales       |
|  4 | Emily      | Brown     | 80000.00 | Engineering |
|  5 | Michael    | Davis     | 65000.00 | Marketing   |
+----+------------+-----------+----------+-------------+
5 rows in set (0.00 sec)

MySQL 에 성공적으로 연결하고 데이터베이스를 생성했으며 데이터로 테이블을 채웠습니다.

뷰 생성하기

이 단계에서는 employees 테이블을 기반으로 뷰를 생성합니다. 뷰는 가상 테이블처럼 작동하는 저장된 SQL 쿼리입니다. 뷰 자체는 데이터를 저장하지 않고 하나 이상의 기본 테이블에서 데이터를 표시합니다.

아직 MySQL 쉘에 있고 company 데이터베이스에 연결되어 있어야 합니다. 그렇지 않은 경우 sudo mysql -u root로 다시 연결한 다음 USE company;를 실행하십시오.

'Sales' 부서에서 일하는 직원만 표시하는 sales_employees라는 뷰를 생성할 것입니다. 이 뷰는 employees 테이블에서 특정 열을 선택합니다.

CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

이 문은 sales_employees라는 뷰를 정의합니다. 뷰의 정의는 AS 키워드 뒤에 오는 SELECT 쿼리입니다. 이 쿼리는 employees 테이블에서 id, first_name, last_name, salary 열을 선택하지만, department 열이 'Sales'와 같은 행만 해당됩니다.

뷰가 생성되었는지 확인하기 위해 현재 데이터베이스의 테이블과 뷰를 나열할 수 있습니다.

SHOW TABLES;

employees와 sales_employees가 모두 나열된 것을 볼 수 있습니다.

+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
| sales_employees   |
+-------------------+
2 rows in set (0.00 sec)

테이블처럼 뷰의 구조를 설명할 수도 있습니다.

DESCRIBE sales_employees;

이렇게 하면 뷰에 포함된 열이 표시됩니다.

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

sales_employees라는 뷰를 성공적으로 생성했습니다.

뷰를 사용하여 데이터 조회하기

이 단계에서는 생성한 sales_employees 뷰를 사용하여 데이터를 쿼리하는 방법을 배웁니다. 뷰는 일반 테이블처럼 쿼리할 수 있으며, 특정 데이터 하위 집합에 액세스하는 간소화된 방법을 제공합니다.

아직 MySQL 쉘에 있고 company 데이터베이스에 연결되어 있어야 합니다. 그렇지 않은 경우 sudo mysql -u root로 다시 연결한 다음 USE company;를 실행하십시오.

sales_employees 뷰에서 모든 데이터를 검색하려면 표준 SELECT 문을 사용합니다.

SELECT * FROM sales_employees;

이 쿼리는 뷰에 정의된 기본 SELECT 문을 실행하고 결과를 반환합니다. 'Sales' 부서의 직원만 표시되어야 합니다.

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
|    3 | Robert     | Jones     | 50000.00 |
+------+------------+-----------+----------+
2 rows in set (0.00 sec)

뷰에서 검색된 데이터에 추가 필터링 또는 정렬을 적용할 수도 있습니다. 예를 들어, 급여가 55000 보다 많은 영업 직원을 찾으려면 다음과 같이 합니다.

SELECT * FROM sales_employees WHERE salary > 55000;

이렇게 하면 뷰에서 급여가 55000 보다 많은 행만 반환됩니다.

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
+------+------------+-----------+----------+
1 row in set (0.00 sec)

뷰에서 특정 열을 선택할 수도 있습니다.

SELECT first_name, last_name FROM sales_employees;

이렇게 하면 영업 직원의 이름과 성만 표시됩니다.

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
| Robert     | Jones     |
+------------+-----------+
2 rows in set (0.00 sec)

뷰를 쿼리하는 것은 기본적으로 테이블을 쿼리하는 것과 동일하지만, 뷰는 기본 데이터 소스에서 미리 정의된 필터링 및 열 선택을 제공합니다.

ALTER VIEW 로 뷰 정의 업데이트하기

이 단계에서는 ALTER VIEW 문을 사용하여 기존 뷰의 정의를 수정하는 방법을 배웁니다. 뷰에 포함될 열이나 필터링 기준을 변경해야 할 때 유용합니다.

아직 MySQL 쉘에 있고 company 데이터베이스에 연결되어 있어야 합니다. 그렇지 않은 경우 sudo mysql -u root로 다시 연결한 다음 USE company;를 실행하십시오.

현재 sales_employees 뷰에는 id, first_name, last_name, salary가 포함되어 있습니다. 이제 department 열을 포함하도록 뷰를 수정해 보겠습니다.

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';

이 문은 ALTER VIEW 다음에 뷰 이름과 뷰를 정의하는 새 SELECT 쿼리를 사용합니다. 새 쿼리에는 이제 department 열이 포함됩니다.

뷰가 업데이트되었는지 확인하려면 다시 설명하십시오.

DESCRIBE sales_employees;

이제 출력에서 department 열을 볼 수 있습니다.

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
| department | varchar(50)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

업데이트된 뷰를 쿼리하여 새 열을 확인합니다.

SELECT * FROM sales_employees;

이제 출력에 department 열이 포함됩니다.

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
|    3 | Robert     | Jones     | 50000.00 | Sales      |
+------+------------+-----------+----------+------------+
2 rows in set (0.00 sec)

sales_employees 뷰의 정의를 성공적으로 업데이트했습니다.

뷰 삭제 및 정리

이 마지막 단계에서는 뷰를 삭제하고 이 실습 중에 생성된 데이터베이스 및 테이블을 정리하는 방법을 배웁니다.

아직 MySQL 쉘에 있고 company 데이터베이스에 연결되어 있어야 합니다. 그렇지 않은 경우 sudo mysql -u root로 다시 연결한 다음 USE company;를 실행하십시오.

sales_employees 뷰를 제거하려면 DROP VIEW 문을 사용합니다.

DROP VIEW sales_employees;

이 명령은 company 데이터베이스에서 sales_employees 뷰를 영구적으로 제거합니다.

뷰가 삭제되었는지 확인하려면 다시 설명해 볼 수 있습니다.

DESCRIBE sales_employees;

뷰가 존재하지 않음을 나타내는 오류 메시지가 표시되어야 합니다.

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

이제 employees 테이블과 company 데이터베이스를 정리해 보겠습니다.

employees 테이블을 삭제합니다.

DROP TABLE employees;

company 데이터베이스를 삭제합니다.

DROP DATABASE company;

다음과 같이 입력하여 MySQL 쉘을 종료할 수 있습니다.

exit

뷰, 테이블 및 데이터베이스를 성공적으로 삭제하여 이 실습에서 사용된 리소스를 정리했습니다.

요약

이 실습에서는 MySQL 뷰를 다루는 방법을 배웠습니다. MySQL 서버에 연결하고 데이터베이스 및 테이블을 설정하는 것으로 시작했습니다. 그런 다음 employees 테이블의 필터링된 뷰를 제공하기 위해 sales_employees라는 뷰를 생성했습니다.

SELECT 문을 사용하여 뷰를 쿼리하는 연습을 했으며, 뷰가 데이터 액세스를 단순화하는 방법을 보여주었습니다. 또한 추가 열을 포함하도록 기존 뷰의 정의를 수정하기 위해 ALTER VIEW 문을 사용하는 방법을 배웠습니다. 마지막으로 DROP VIEW 문을 사용하여 뷰를 제거하고 데이터베이스 및 테이블을 정리하는 방법을 배웠습니다.

이제 데이터베이스에서 데이터를 관리하고 액세스하는 데 유용한 도구인 MySQL 뷰 작업에 대한 기본 개념과 작업을 이해하게 되었습니다.