8. SQL의 기본기 - 데이터베이스구축

728x90

8. SQL의 기본기 - 데이터베이스구축

지난 시간은 CRUD, DML문을 입력했을 때 최적의 결과를 가져오는 옵티마이저(규칙, 비용), 튜닝(설계, 환경, SQL문장), 트랜잭션, 투명성 등을 공부했습니다. 정보처리기사에서 데이터베이스 구축 자체가 많이 나오기 때문에 꼭 공부해야하는 파트입니다.

이번 시간은 시험에서 문제를 직접 풀어서 답을 유추해야하는 SQL에 대해 배우게 됩니다. 이번 파트는 다른 파트들과 달리 연산에 대해 집중해서 보셔야하며, 명령어들을 기억해주셔야합니다.

SQL이란?

SQL은 데이터베이스와 대화할 수 있는 언어입니다.

데이터베이스는 정보를 저장하는 곳으로, 예를 들어 고객 정보나 주문 내역 같은 것들이 저장됩니다. 이 정보에 접근하고 수정하는 방법이 필요한데, 그게 바로 SQL입니다.

SQL은 데이터베이스에게 내리는 명령어의 모음입니다.

이 명령어를 사용하여 데이터베이스에게 정보를 요청하거나 업데이트하고, 필요한 데이터를 찾을 수 있습니다.

예를 들어, "우리의 고객 목록을 보여줘"라고 말하는 것처럼, SQL을 사용하여 데이터베이스에게 특정 정보를 요청할 수 있습니다.

DDL이란?

DDL(데이터 정의 언어, Data Definition Language)은 데이터베이스 구조를 정의하고 관리하기 위한 언어입니다. 주로 데이터베이스의 스키마(schema)를 정의하거나 수정하는 데 사용됩니다.

DDL 객체 유형

객체 유형	설명
스키마(Schema)	데이터베이스 내에 있는 모든 객체의 집합을 나타냅니다.
도메인(Domain)	데이터베이스에서 사용될 수 있는 값들의 집합을 정의합니다.
테이블(Table)	데이터를 저장하는 가장 기본적인 객체입니다.
뷰(View)	기존의 테이블로부터 유도된 가상의 테이블입니다.
인덱스(Index)	특정 열에 대한 검색 속도를 향상시키기 위해 사용됩니

명령 방법

CREATE: 데이터베이스 객체를 생성합니다. 예를 들어 테이블, 뷰, 인덱스 등을 생성할 때 사용됩니다.
ALTER: 이미 존재하는 데이터베이스 객체의 구조를 변경합니다. 예를 들어 테이블에 새로운 열을 추가하거나 기존 열의 데이터 타입을 변경할 때 사용됩니다.
DROP: 데이터베이스 객체를 삭제합니다. 예를 들어 테이블을 삭제할 때 사용됩니다.
- 아예 테이블이 삭제되는 명령
TRUNCATE: 테이블의 모든 데이터를 삭제하지만 테이블의 구조는 유지한 채 초기화합니다.
- 표가 초기화가 되는 명령
RENAME: 테이블의 모든 데이터를 삭제하지만 테이블의 구조는 유지한 채 초기화합니다.

CREATE 예시

테이블(Table) 생성

CREATE TABLE my_table (
    column1 datatype1,
    column2 datatype2,
    ...
    CONSTRAINT constraint_name PRIMARY KEY (column_name)
);

뷰(View) 생성

CREATE VIEW____AS

CREATE VIEW my_view AS
SELECT column1, column2
FROM my_table
WHERE condition;

인덱스(Index) 생성

CREATE INDEX index_name
ON my_table (column_name);

ALTER

속성 추가(Add a Column)

ALTER TABLE table_name
ADD column_name datatype;

속성 변경(Modify a Column)

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

속성 삭제(Drop a Column)

ALTER TABLE table_name
DROP COLUMN column_name;

인덱스 변경(Modify an Index)

REBUILD를 기억해야함.

ALTER INDEX index_name
REBUILD;

DROP

문제에서 테이블 자체를 삭제한다고 나오면 무조건 DROP TABLE 테이블명

DROP TABLE my_table;

TRUNCATE

초기화, 구조만 남기고 데이터만 싹~ 삭제하는 과정

TRUNCATE TABLE my_table;

DDL문 제약 조건

제약 조건	설명
PRIMARY KEY	테이블의 기본 키를 지정합니다. 기본 키는 해당 테이블의 각 행을 고유하게 식별합니다. NOT NULL, UNIQUE (개체 무결성 조건) 제약 포함
FOREIGN KEY	다른 테이블의 외래 키(Foreign Key)를 지정합니다. 외래 키는 다른 테이블과의 관계를 나타냅니다. 예시 CONSTRAINT fk_department_id -- CONSTRAINT는 제약조건 정의할 때 쓰는 명령어 FOREIGN KEY (department_id) -- 나의 테이블에 있는 컬럼 (department_id) REFERENCES departments(department_id) -- departments테이블의 (department_id) 컬럼을 참조 DELETE CASCADE
UNIQUE	열의 값이 고유해야 함을 지정합니다. student_name VARCHAR(50) UNIQUE UNIQUE NOT NULL
NOT NULL	열에 NULL 값을 허용하지 않음을 지정합니다.
CHECK	특정 조건이 충족되어야 하는 값을 지정합니다. 도메인 무결성 product_price DECIMAL(10, 2) CHECK (product_price > 0)
DEFAULT	열의 기본값을 지정합니다. order_date DATE DEFAULT CURRENT_DATE

제약 조건 예시 SQL 문

실제로 작성할 수도 있지만, 내용이 방대하므로 아래에 SQL문을 접어두었습니다.

제약 조건 예시 코드

-- 'employees' 테이블 생성
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,            -- PRIMARY KEY 제약 조건: employee_id 열을 기본 키로 지정
    first_name VARCHAR(50) NOT NULL,        -- NOT NULL 제약 조건: first_name 열에 NULL 값을 허용하지 않음
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,              -- UNIQUE 제약 조건: email 열의 값은 고유해야 함
    department_id INT,
    CONSTRAINT fk_department_id FOREIGN KEY (department_id) REFERENCES departments (department_id)
    -- FOREIGN KEY 제약 조건: department_id 열이 departments 테이블의 department_id 열을 참조함
);

-- 'orders' 테이블 생성
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    total_amount DECIMAL(10, 2),
    CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
    -- FOREIGN KEY 제약 조건: customer_id 열이 customers 테이블의 customer_id 열을 참조함
);

-- 'products' 테이블 생성
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    CONSTRAINT chk_price CHECK (price >= 0)  -- CHECK 제약 조건: price 열의 값이 0보다 크거나 같아야 함
);

-- 'shipments' 테이블 생성
CREATE TABLE shipments (
    shipment_id INT PRIMARY KEY,
    order_id INT,
    ship_date DATE NOT NULL,
    CONSTRAINT fk_order_id FOREIGN KEY (order_id) REFERENCES orders (order_id),
    CONSTRAINT fk_order_id UNIQUE (order_id)  -- UNIQUE 제약 조건: order_id 열의 값은 고유해야 함
);

DML이란?

DML(Data Manipulation Language)은 데이터베이스에서 데이터를 조작하고 관리하는 언어입니다. DML은 데이터의 삽입, 갱신, 삭제, 조회 등의 작업을 수행하여 데이터베이스를 변경하는데 사용됩니다.

DML 명령문

INSERT INTO

새로운 데이터를 데이터베이스에 삽입

숫자에는 홑따옴표(')를 안붙여도 되지만, 문자는 반드시 붙여야함.

-- 'employees' 테이블에 새로운 직원 데이터 삽입
INSERT INTO employees (employee_id, employee_name, department_id)
VALUES (1, 'John Doe', 101);

무조건 INSERT INTO로 시작하고 table명을 적어야합니다.

table내의 컬럼을 ()안에 넣어줍니다.

그리고 값을 바꿔야하니까. Values('문자', 숫자)가 들어가야 합니다.

여러행을 추가 하는 경우

SELECT로 컬럼을 선택해서 값을 넣음.

SELECT __ FROM

데이터베이스에서 데이터를 조회

SELECT * FROM 티스토리 
--  티스토리의 모든 데이터를 가져온다

ALIAS (AS)

ALIAS는 SQL에서 열이나 테이블에 대한 별칭을 지정하는 데 사용됩니다. 별칭을 지정함으로써 결과를 더 읽기 쉽고 명확하게 만들 수 있습니다. 일반적으로 열의 이름이나 테이블 이름이 길거나 복잡한 경우에 유용하게 사용됩니다.

SELECT employee_name AS name, department_id AS dept_id
FROM employees;

WEHRE

특정 조건의 행들을 조회하기 위해 사용, 조건이 해당되는 행들을 가져오는 명령어

SELECT 사원명, 사번 FROM 사원정보
WHERE 부서 = '개발팀'
-- 사원정보 테이블에서 부서가 개발팀인 사원명, 사번을 가져와라

AND / OR 사용법

특정 컬럼의 값을 선택한 WHERE에서 여러 조건을 붙이기 위해 사용하는 명령어

SELECT * FROM 사원정보
WHERE 부서 = '개발팀'
OR 부서 ='마케팅팀'

부서가 개발팀이거나 마케팅팀인 정보를 가져와라.라는 명령어가 됩니다.

LIKE 사용법

LIKE는 패턴 매칭을 위해 사용되며, 특정 문자열을 포함하거나 특정 패턴을 가진 문자열을 검색하는 데 사용됩니다. 주로 WHERE 절과 함께 사용되어 데이터를 필터링하는 데에 활용됩니다. LIKE 연산자는 일반적으로 두 가지 와일드카드 문자인 '%'와 '_'와 함께 사용됩니다.

%: 임의의 문자열을 나타냅니다. 이것은 0개 이상의 문자와 매칭됩니다.
_: 임의의 단일 문자를 나타냅니다. 이것은 한 개의 문자와 매칭됩니다.

SELECT * FROM 사원정보
WHERE (부서 = '개발팀' OR 부서 ='마케팅팀') 
AND 이름 LIKE'이%''
-- 이로 시작하는 데이터

LIKE는 시험에 생각보다 자주 출제됩니다. ~로 시작하는 데이터, ~로 끝나는 데이터를 찾으시오와 같이 출제됩니다.

아래 LIKE 예시를 살펴보시길 바랍니다.

-- 접두어 일치: 특정 문자열로 시작하는 모든 값 검색
SELECT * FROM table_name WHERE column_name LIKE 'pattern%';

--접미어 일치: 특정 문자열로 끝나는 모든 값 검색
SELECT * FROM table_name WHERE column_name LIKE '%pattern';

-- 문자열 포함: 특정 문자열을 포함하는 모든 값 검색
SELECT * FROM table_name WHERE column_name LIKE '%pattern%';

-- 특정 길이의 문자열 검색: 특정 길이의 문자열을 가진 값 검색
SELECT * FROM table_name WHERE column_name LIKE '___'; -- 세 글자

--특정 문자로 시작하는 값 검색: 특정 문자로 시작하는 모든 값 검색
SELECT * FROM table_name WHERE column_name LIKE 'p%';

--특정 문자로 끝나는 값 검색: 특정 문자로 끝나는 모든 값 검색
SELECT * FROM table_name WHERE column_name LIKE '%p';

--특정 범위의 문자 검색: 특정 범위의 문자를 포함하는 값 검색
SELECT * FROM table_name WHERE column_name LIKE '[a-c]%';

--특정 문자 집합에 속하는 값 검색: 특정 문자 집합에 속하는 값 검색
SELECT * FROM table_name WHERE column_name LIKE '[abc]%';

--반전된 문자 집합에 속하는 값 검색: 특정 문자 집합에 속하지 않는 값 검색
SELECT * FROM table_name WHERE column_name LIKE '[^abc]%';

IS NULL / IS NOT NULL

널 값만 찾거나 널 값을 제외하고 찾을 때 씁니다.

SELECT * FROM employees
WHERE email IS NULL;

SELECT * FROM employees
WHERE email IS NOT NULL;

BETWEEN __ AND

~와 ~사이의 값을 가져올 떄 씁니다. A =<, B>= 와 같은 효과를 가집니다.

SELECT *
FROM orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-01-31';

COUNT

COUNT 함수는 특정 열의 행 수를 세는 데 사용됩니다. 이 함수는 NULL 값을 무시하고 행의 개수를 반환합니다.

SELECT COUNT(*) AS total_employees
FROM employees;

DISTINCT

DISTINCT 키워드는 결과 집합에서 중복된 값을 제거하는 데 사용됩니다. 즉, 중복된 값을 하나로 줄여서 반환합니다.

SELECT COUNT(DISTINCT department_id) AS unique_departments
FROM employees;

GROUP BY

GROUP BY 절은 특정 열을 기준으로 데이터를 그룹화합니다. 그룹화된 결과를 기반으로 집계 함수를 사용하여 데이터를 요약할 수 있습니다.

이를 통해 데이터를 논리적인 그룹으로 나누고 집계할 수 있습니다.

아래는 부서아이디로 데이터를 모으는 역할을 합니다.

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING employee_count >= 5
ORDER BY employee_count DESC;

HAVING

HAVING 절은 GROUP BY 절 다음에 사용되며, 그룹화된 결과에 대한 조건을 지정합니다. HAVING 절은 그룹별로 필터링을 수행하므로, WHERE 절은 개별 행을 필터링하는 데 사용되고 HAVING 절은 그룹을 필터링하는 데 사용됩니다.

위의 부서아이디로 데이터를 모으는 뒤, 직원수가 5개 이하인걸 선택합니다.

다른 예시

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= 5000;

ORDER BY

ORDER BY 절은 조회된 결과를 특정 열의 값에 따라 정렬합니다. 기본적으로는 오름차순(ASC)으로 정렬되지만, DESC 키워드를 사용하여 내림차순으로 정렬할 수도 있습니다.

위의 부서아이디로 데이터를 모으는 뒤, 직원수가 5개 이하인걸 선택하고 순서를 내림차순으로 정렬하는 것입니다.

다른 예시

SELECT * FROM employees
ORDER BY salary DESC;

UPDATE 테이블명 SET

데이터베이스 내의 기존 데이터를 갱신

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

table_name: 업데이트할 테이블의 이름을 지정합니다.
SET: 업데이트할 열과 해당 값들을 지정합니다.
column1 = value1, column2 = value2, ...: 업데이트할 열과 새로운 값을 지정합니다.
WHERE: 업데이트할 행을 선택하는 조건을 지정합니다. 이 부분은 선택 사항입니다. 만약 지정하지 않으면 모든 행이 업데이트됩니다.
condition: 업데이트할 행을 선택하는 조건을 지정합니다. 만약 지정하지 않으면 모든 행이 업데이트됩니다.

UPDATE employees
SET salary = 5000
WHERE employee_id = 101;

DELETE FROM

데이터베이스에서 특정 데이터를 삭제하는 명령어인데, WHERE문이 없으면 모든 정보가 다 사라지게 됩니다.

UPDATE employees
SET salary = 5000
WHERE employee_id = 101;

DCL란?

DCL은 데이터베이스 제어 언어(Database Control Language)의 약자로, 데이터베이스의 보안과 권한을 관리하는 데 사용됩니다.

DCL은 데이터베이스에 접근하고 데이터를 조작할 수 있는 권한을 부여하거나 제거하는 등의 작업을 수행합니다.

대표적인 DCL 명령어로는 GRANT과 REVOKE가 있습니다.

REVOKE

REVOKE 명령어는 사용자로부터 특정 데이터베이스 객체에 대한 권한을 제거합니다. 이를 통해 사용자에게 부여된 권한을 취소할 수 있습니다.

REVOKE ALL ON employees FROM username;

[CASCADE] : 전부 회수

예를 들어, 부서 테이블과 직원 테이블이 있을 때, 부서 테이블의 부서 ID(primary key)를 참조하는 외래 키 제약 조건이 직원 테이블에 있다고 가정해보겠습니다. 이때 부서 테이블에서 특정 부서가 삭제될 때, 해당 부서를 참조하는 모든 직원 레코드도 삭제되어야 한다면 CASCADE 옵션을 사용할 수 있습니다

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE
);

GRANT

GRANT 명령어는 사용자에게 특정 데이터베이스 객체(테이블, 뷰, 프로시저 등)에 대한 권한을 부여합니다. 이를 통해 사용자가 특정 작업을 수행할 수 있도록 허용할 수 있습니다.

GRANT SELECT ON employees TO username;

TCL이란?

TCL(Transaction Control Language)은 트랜잭션의 처리를 제어하기 위한 언어입니다.

TCL은 데이터베이스 내에서 트랜잭션의 시작, 종료, 롤백 등의 작업을 수행합니다.

TCL 명령 방법

COMMIT: 현재 진행 중인 트랜잭션의 모든 변경 사항을 영구적으로 데이터베이스에 적용합니다.
- 즉, 트랜잭션을 성공적으로 완료하고 데이터의 일관성을 유지하기 위해 사용됩니다.
ROLLBACK: 현재 진행 중인 트랜잭션의 모든 변경 사항을 취소하고 이전 상태로 되돌립니다.
- 즉, 트랜잭션을 실패로 처리하고 데이터베이스를 이전 상태로 복원하는 데 사용됩니다.
SAVEPOINT: 현재 트랜잭션 내에서 특정 지점에 저장점을 설정합니다.
- 저장점을 설정한 후 롤백 시 해당 저장점까지만 복원할 수 있습니다.