소개
이 랩에서는 SQLite 뷰 생성에 대해 살펴보고, 복잡한 뷰 구축, 뷰를 통한 쿼리, 업데이트 가능한 뷰를 통한 업데이트, 그리고 더 이상 필요 없는 뷰 제거에 중점을 둡니다.
먼저 SQL 문 결과 집합을 기반으로 하는 가상 테이블로서의 뷰를 이해하고, 쿼리를 단순화하고 데이터 접근을 제어하는 데 유용함을 알게 될 것입니다. 이 랩에서는 employees 테이블과 같은 단일 테이블에서 간단한 뷰를 생성하는 방법을 안내하고, employees 및 departments 테이블을 조인하는 것과 같이 테이블 간의 조인을 포함하는 더 복잡한 뷰로 진행합니다. 이러한 뷰를 일반 테이블처럼 쿼리하는 방법을 배우고, 업데이트 가능한 뷰를 통해 데이터를 업데이트하는 가능성을 탐구하며, 더 이상 필요하지 않은 뷰를 적절하게 제거하는 방법을 배우게 됩니다.
테이블 생성: employees 및 departments
이 단계에서는 employees 및 departments 두 개의 테이블을 생성하고 샘플 데이터를 삽입합니다. 이 테이블은 다음 단계에서 뷰를 생성하고 쿼리하는 데 사용됩니다.
먼저, 터미널에서 다음 명령을 실행하여 SQLite 셸을 엽니다.
sqlite3 /home/labex/project/employees.db
이 명령은 SQLite 셸을 열고 employees.db 데이터베이스에 연결합니다. 데이터베이스 파일이 존재하지 않으면 SQLite 가 생성합니다.
이제 다음 SQL 문을 사용하여 employees 테이블을 생성합니다.
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
department TEXT,
salary REAL
);
이 SQL 문은 id, first_name, last_name, department, 및 salary의 다섯 개의 열을 가진 employees라는 테이블을 생성합니다. id 열은 테이블의 기본 키 (primary key) 입니다.
다음으로, employees 테이블에 샘플 데이터를 삽입합니다.
INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 50000.00),
('Jane', 'Smith', 'Marketing', 60000.00),
('Peter', 'Jones', 'Sales', 55000.00),
('Mary', 'Brown', 'IT', 70000.00);
이 SQL 문은 employees 테이블에 네 개의 행을 삽입합니다.
이제 다음 SQL 문을 사용하여 departments 테이블을 생성합니다.
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT,
location TEXT
);
이 SQL 문은 id, name, 및 location의 세 개의 열을 가진 departments라는 테이블을 생성합니다. id 열은 테이블의 기본 키입니다.
다음으로, departments 테이블에 샘플 데이터를 삽입합니다.
INSERT INTO departments (name, location) VALUES
('Sales', 'New York'),
('Marketing', 'London'),
('IT', 'San Francisco');
이 SQL 문은 departments 테이블에 세 개의 행을 삽입합니다.
테이블 생성 및 데이터 삽입을 확인하려면 테이블을 쿼리할 수 있습니다.
SELECT * FROM employees;
SELECT * FROM departments;
이 명령은 각각 employees 및 departments 테이블의 내용을 표시합니다.
간단한 뷰 생성
이 단계에서는 employees 테이블에서 특정 열을 선택하는 employee_info라는 간단한 뷰를 생성합니다.
뷰는 SQL 문의 결과 집합을 기반으로 하는 가상 테이블입니다. 복잡한 쿼리를 단순화하고 추상화 (abstraction) 수준을 제공합니다.
employee_info 뷰를 생성하려면 SQLite 셸에서 다음 SQL 문을 실행합니다.
CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;
이 SQL 문은 employees 테이블에서 id, first_name, last_name, 및 department 열을 선택하는 employee_info라는 뷰를 생성합니다.
이제 테이블처럼 뷰를 쿼리할 수 있습니다.
SELECT * FROM employee_info;
이 명령은 employees 테이블의 열의 하위 집합인 employee_info 뷰의 내용을 표시합니다.
Join 을 사용한 복잡한 뷰 생성
이 단계에서는 employees 및 departments 테이블을 조인하는 employee_department_info라는 더 복잡한 뷰를 생성합니다.
테이블을 조인하면 관련 열을 기반으로 여러 테이블의 데이터를 결합할 수 있습니다.
employee_department_info 뷰를 생성하려면 SQLite 셸에서 다음 SQL 문을 실행합니다.
CREATE VIEW employee_department_info AS
SELECT
e.first_name,
e.last_name,
e.department,
d.location
FROM
employees e
JOIN
departments d ON e.department = d.name;
이 SQL 문은 department 열을 기준으로 employees 및 departments 테이블을 조인하는 employee_department_info라는 뷰를 생성합니다. 직원의 이름, 성, 부서 및 부서의 위치를 선택합니다.
이제 뷰를 쿼리하여 결합된 데이터를 볼 수 있습니다.
SELECT * FROM employee_department_info;
이 명령은 employee_department_info 뷰의 내용을 표시하여 직원 정보와 해당 부서의 위치를 함께 보여줍니다.
집계 함수를 사용한 뷰 생성
이 단계에서는 집계 함수를 사용하여 각 부서의 평균 급여를 계산하는 department_salary_stats라는 뷰를 생성합니다.
집계 함수는 일련의 값에 대해 계산을 수행하고 단일 결과를 반환합니다.
department_salary_stats 뷰를 생성하려면 SQLite 셸에서 다음 SQL 문을 실행합니다.
CREATE VIEW department_salary_stats AS
SELECT
department,
AVG(salary) AS average_salary
FROM
employees
GROUP BY
department;
이 SQL 문은 AVG 함수를 사용하여 각 부서의 평균 급여를 계산하고 GROUP BY 절을 사용하여 결과를 부서별로 그룹화하는 department_salary_stats라는 뷰를 생성합니다.
이제 뷰를 쿼리하여 각 부서의 평균 급여를 볼 수 있습니다.
SELECT * FROM department_salary_stats;
이 명령은 department_salary_stats 뷰의 내용을 표시하여 각 부서의 평균 급여를 보여줍니다.
업데이트 가능한 뷰를 통한 데이터 업데이트
이 단계에서는 SQLite 뷰의 제한 사항과 INSTEAD OF 트리거를 사용하여 진정으로 업데이트 가능한 뷰를 만드는 방법을 배우게 됩니다.
중요: 기본적으로 SQLite 뷰는 읽기 전용입니다. 뷰를 통해 데이터를 직접 UPDATE, INSERT 또는 DELETE 할 수 없습니다. 그러나 INSTEAD OF 트리거를 사용하여 업데이트 가능한 뷰를 만들 수 있습니다.
먼저, 기존 뷰에 대한 직접적인 업데이트를 시도하여 제한 사항을 이해해 보겠습니다.
UPDATE employee_info
SET department = 'HR'
WHERE id = 1;
이것은 SQLite 뷰가 기본적으로 읽기 전용이기 때문에 오류와 함께 실패합니다.
진정으로 업데이트 가능한 뷰를 만들려면 INSTEAD OF 트리거를 사용해야 합니다. employee_info 뷰를 다시 만들고 업데이트를 위한 INSTEAD OF 트리거를 추가해 보겠습니다.
DROP VIEW IF EXISTS employee_info;
CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;
이제 UPDATE 작업을 처리하기 위해 INSTEAD OF 트리거를 만듭니다.
CREATE TRIGGER update_employee_info
INSTEAD OF UPDATE ON employee_info
BEGIN
UPDATE employees
SET first_name = NEW.first_name,
last_name = NEW.last_name,
department = NEW.department
WHERE id = OLD.id;
END;
이제 뷰를 통해 데이터를 업데이트할 수 있습니다.
UPDATE employee_info
SET department = 'HR'
WHERE id = 1;
이 UPDATE 문은 이제 INSTEAD OF 트리거가 업데이트를 기본 employees 테이블로 리디렉션하기 때문에 작동합니다.
employees 테이블을 직접 쿼리하여 업데이트를 확인할 수 있습니다.
SELECT * FROM employees WHERE id = 1;
이 명령은 id = 1인 employees 테이블의 행을 표시하며, department 열이 'HR'로 업데이트된 것을 확인할 수 있습니다.
사용하지 않는 뷰 및 트리거 제거
이 단계에서는 이전 단계에서 생성한 뷰와 트리거를 제거합니다.
데이터베이스가 발전함에 따라 일부 뷰와 트리거는 오래되거나 불필요해질 수 있습니다. 깨끗하고 효율적인 데이터베이스 스키마 (schema) 를 유지하려면 이러한 객체를 제거하는 것이 중요합니다.
먼저, INSTEAD OF 트리거를 제거합니다.
DROP TRIGGER IF EXISTS update_employee_info;
그런 다음 뷰를 제거합니다. 뷰를 제거하려면 DROP VIEW 문을 사용합니다. 예를 들어, employee_info 뷰를 제거하려면 다음 명령을 실행합니다.
DROP VIEW IF EXISTS employee_info;
IF EXISTS 절은 뷰가 존재하지 않는 경우 오류를 방지합니다.
다른 뷰도 제거합니다.
DROP VIEW IF EXISTS employee_department_info;
DROP VIEW IF EXISTS department_salary_stats;
sqlite_master 테이블을 쿼리하여 뷰가 제거되었는지 확인할 수 있습니다.
SELECT name FROM sqlite_master WHERE type='view';
이 명령은 빈 결과를 반환해야 하며, 이는 데이터베이스에 뷰가 없음을 나타냅니다.
마지막으로 SQLite 셸을 종료합니다.
.exit
이 명령은 employees.db 데이터베이스에 대한 연결을 닫고 Linux 터미널로 돌아갑니다.
요약
이번 랩에서는 SQLite 에서 뷰 (view) 를 생성, 쿼리, 업데이트 및 제거하는 방법을 배웠습니다. 단일 테이블을 기반으로 하는 간단한 뷰를 생성하는 것으로 시작하여 조인 (join) 과 집계 함수 (aggregate function) 를 포함하는 더 복잡한 뷰로 발전했습니다. SQLite 뷰는 기본적으로 읽기 전용 (read-only) 이라는 것을 알게 되었지만, INSTEAD OF 트리거 (trigger) 를 사용하여 실제로 업데이트 가능한 뷰를 만드는 방법을 배웠습니다. 또한, 깔끔한 데이터베이스 스키마 (schema) 를 유지하기 위해 쓸모없어진 뷰와 트리거를 적절하게 제거하는 방법도 배웠습니다. 이러한 기술은 복잡한 쿼리를 단순화하고, 데이터 접근을 제어하며, SQLite 데이터베이스를 효과적으로 관리하는 데 필수적입니다.
