[DB] SQL 명령어 분류와 사용법 정리

jina·2024년 5월 12일

DB

목록 보기

2/3

📌 SQL

SQL(Structured Query Language)은 데이터베이스 관리 시스템(DBMS)에서 사용되는 언어입니다. 테이블을 만들고 테이블에 데이터를 추가하거나 수정하며, 필요한 정보를 조회하는 데 사용합니다. SQL Server, Oracle, MySQL, PostgreSQL, DB2 등 다양한 DBMS에서 사용됩니다.

명령어의 종류는 DDL(테이블 생성, 변경, 삭제), DML(데이터 조회, 수정), DCL(권한 관리), TCL(트랜잭션 제어)로 나눌 수 있습니다.

데이터 정의어 DDL: CREATE, ALTER, DROP
데이터 조작어 DML: SELECT, INSERT, UPDATE, DELETE
데이터 제어어 DCL: GRANT, REVOKE
트랜젝션 제어어 TCL: COMMIT, ROLLBACK

📌주요 SQL 명령어

✍️ MySQL을 기준으로 작성되었습니다.

✅ DDL (정의어)

관계형 데이터베이스의 기본 구조(테이블)를 잡는데 사용하는 언어

CREATE: 새로운 테이블, 뷰, 데이터베이스를 생성
- 데이터 타입 지정, NULL 가능 여부 설정, 기본키 설정, 제약 조건 설정 (유니크, 외래키)

CREATE TABLE addresses (
    id INT PRIMARY KEY, 
    member_id INT, 
    email VARCHAR(50)
    address VARCHAR(100)
    );

ALTER: 기존 테이블 변경
- 테이블 수정: ADD, DROP, MODIFY, RENAME, ADD CONSTRAINT, DROP CONSTRAINT
- 인덱스 수정: CREATE INDEX, DROP INDEX
- 데이터베이스 이름 변경: RENAME

ALTER TABLE members 
ADD COLUMN phone_number VARCHAR(20);
-- 새로운 컬럼을 추가하고 속성 지정

ALTER TABLE members
ALTER COLUMN class_a VARCHAR(30) NOT NULL;
-- NOT NULL을 명시하지 않으면 자동으로 NULL 허용
-- 한번에 하나의 컬럼만 변경할 수 있으며 괄호를 쓰지 않는다

DROP:기존 테이블이나 뷰를 삭제
- SQL에서 DROP과 ROLLBACK은 auto-commit이 되기 때문에 되돌릴 수 없음
- 외래 키 설정에 DROP 제약 조건을 추가하려면 ON DELETE CASCADE 나 ON DELETE RESTRICT를 사용
- CASCADE: 한 테이블의 행을 삭제할 때 이 행을 참조하는 자식 테이블의 해당 외래 키를 가진 모든 행도 연쇄적으로 함께 삭제
- RESTRICT: 다른 테이블이 의존하고 있는 경우 삭제 작업이 제한됨

DROP TABLE members; -- 테이블의 내용 및 구조 자체를 완전히 삭제

CREATE TABLE parent ( -- 상위 테이블 생성
    id INT PRIMARY KEY -- id 열을 기본키로 지정
);

CREATE TABLE child ( -- 하위 테이블 생성
    id INT PRIMARY KEY, -- id 열을 기본키로 지정
    parent_id INT, -- parent_id 열을 생성
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE 
    -- parent_id 열을 외래키로 지정 + 상위 테이블의 id 열을 참조하도록 설정 + 상위 테이블 행 삭제시 의존하는 하위 테이블 행도 삭제되도록 제약
);

TRUNCATE: 테이블에 들어 있는 전체 데이터를 삭제

TRUNCATE TABLE employees; -- employees 테이블의 모든 데이터를 빠르게 삭제

💡 DROP vs TRUNCATE vs DELETE

DROP: DDL 명령으로, 테이블과 데이터를 완전히 삭제합니다. 로그를 남기지 않아 ROLLBACK이 불가능하고 자동으로 COMMIT됩니다.

TRUNCATE: DDL 명령으로, 테이블 구조를 남기고 데이터만 삭제합니다. 로그를 남기지 않아 ROLLBACK이 불가능하고 자동으로 COMMIT됩니다.

DELETE: DML 명령으로, 테이블 구조를 남기고 데이터만 삭제합니다. WHERE와 함께 원하는 데이터를 골라 삭제할 수 있습니다. 마지막 COMMIT 지점으로 ROLLBACK이 가능하며, 수동 COMMIT이 필요합니다.

✅ DML (조작어)

테이블에 저장한 데이터를 조작하는데 사용하는 언어

SELECT: 데이터베이스에서 데이터 가져오기
- DISTINCT: SELECT DISTINCT를 사용하면 중복되는 값을 단일 행으로 묶어서 조회 가능 (NULL도 단일 행으로 취급)
- COUNT: COUNT(*)는 전체 행, COUNT(컬럼)은 NULL을 제외한 행, COUNT(DISTINCT 컬럼)은 NULL을 포함한 행의 개수를 집계

SELECT * 
FROM members 
WHERE status = 'Active';

UPDATE: 데이터베이스에 있는 데이터를 변경

UPDATE members 
SET status = 'Inactive' 
WHERE id = 101;

DELETE: 데이터베이스에 있는 원하는 데이터를 삭제

DELETE FROM members 
WHERE id = 105;

💡 WHERE
SELECT, UPDATE, DELETE를 WHERE절과 함께 쓰면 원하는 조건으로 필터링된 값을 조회/수정/삭제할 수 있습니다.

-- '김'으로 시작하는 이름을 가진 사람을 조회
SELECT * FROM CUSTOMER WHERE NAME LIKE '김%';
-- 오더 개수가 5가 넘으면 CATEGORY를 VIP로 수정
UPDATE CUSTOMER SET CATEGORY = 'VIP' WHERE ORDER_TOTAL > 5;
-- 폰 번호가 NULL인 행을 삭제
DELETE FROM CUSTOMER WHERE PHONE_NO IS NULL;

💡 GROUP BY
집계함수(count, sum, min, max 등)를 조건으로 사용하고자 할 때는, WHERE절이 아닌 GROUP BY절에서 사용할 수 있습니다. [GROUP BY 칼럼이나 표현식]을 먼저 작성한 다음에 [HAVING 그룹의 조건식]의 형태로 추가합니다.
SELECT 
	attr, 
	AVG(weight) AS 몸무게 -- SELECT에서는 AS 사용 가능
FROM trainee
GROUP BY attr -- attr 칼럼을 기준으로 같은 값끼리 그룹화
HAVING AVG(weight) > 90;  -- 그룹화 조건: 몸무게 칼럼 값이 90 이상인 것만 그룹화
이때 주의할 점은, GROUP BY절에 나열된 열은 반드시 SELECT 절이나 혹은 집계 함수 안에 나타나야 한다는 것입니다.

💡 HAVING
데이터를 집계해서 그룹화했을 때 조건을 지정하는 용도로 사용합니다. 주로 GROUP BY와 함께 사용하지만, GROUP BY절이 없어도, 집계함수를 사용해서 조건을 지정하는 방식으로도 사용이 가능합니다.
SELECT SUM(salary) AS total_salary
FROM employees
HAVING SUM(salary) > 200000;

INSERT: 데이터베이스에 새로운 데이터를 추가

INSERT INTO members (name, email) 
VALUES ('Alice', 'alice@example.com');

JOIN: 둘 이상의 테이블을 결합해서 데이터를 가져오기
- INNER JOIN: 두 테이블에 일치하는 행만 결과에 포함 (ON 절이나 USING 절로 조건 지정 필요)
- LEFT JOIN: 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 결합 (값이 없으면 NULL)
- RIGHT JOIN: 오른쪽 테이블의 모든 행과 왼쪽 테이블에서 일치하는 행을 결합 (값이 없으면 NULL)
- FULL JOIN: 두 테이블의 모든 행을 결합 (값이 없으면 NULL)

SELECT members.name, addresses.address
FROM members
INNER JOIN addresses 
ON members.id = addresses.member_id;

✅ DCL (제어어)

데이터베이스에 접근 및 제어 권한을 관리하는 언어

GRANT: 사용자에게 권한 부여

GRANT SELECT ON wifi_history TO user1234;
-- user1234라는 사용자에게 wifi_history라는 테이블 SELECT 권한을 부여

REVOKE: 사용자에게 부여된 권한 회수

REVOKE SELECT ON wifi_history FROM user1234;
-- user1234라는 사용자에게 wifi_history라는 테이블 SELECT 권한을 회수

ROLE: 공통적인 권한을 부여할 사용자 그룹 생성

CREATE ROLE manager_role; -- role을 생성하고 이름 지정
GRANT SELECT, INSERT, UPDATE ON employees TO manager_role; -- role에 권한 부여
GRANT manager_role TO 'john_doe'@'localhost';
 -- 로컬호스트의 특정 사용자에게 해당 role 부여

✅ TCL

DML로 조작된 결과물을 트랜잭션 단위로 묶어서 제어하는 언어

COMMIT: 데이터 변경사항을 데이터베이스에 영구적으로 반영
ROLLBACK: 진행 중인 트랜잭션을 취소하고 마지막으로 COMMIT이 완료된 지점으로 되돌아가기 (SAVEPOINT가 있으면 SAVEPOINT로 돌아간다)
SAVEPOINT: 트랜잭션 내에서 ROLLBACK 지점을 설정

💡 Auto Commit

SQL Server
DDL 명령어(CREATE, ALTER, DROP, TRUNCATE 등)는 Auto Commit입니다. DML 명령어(SELECT, INSERT, UPDATE, DELETE 등)도 기본적으로는 Auto Commit이지만, 1️⃣ BEGIN TRANSACITON을 사용해서 트랜잭션을 명시적으로 관리하거나 2️⃣ Auto Commit을 False로 설정하면 수동 커밋으로 전환이 가능합니다.

Oracle
DDL 명령어는 Auto Commit이지만, DML 명령어는 기본적으로 Auto Commit이 아닙니다. 명시적으로 COMMIT 또는 ROLLBACK 필요합니다.

📌 SQL 기본 구성 예시

SELECT [column1], [column2], COUNT([column3]) AS [alias_for_count] -- 조회할 컬럼 선택 및 별명 지정
FROM [table1] -- 조회할 테이블 지정
JOIN [table2] ON [table1].[matching_column] = [table2].[matching_column] -- 결합할 테이블 지정하고 결합 조건 명시
WHERE [condition_column] = '[value]' -- 행 필터링 기준
GROUP BY [column1], [column2] -- 그룹화 기준
HAVING COUNT([column3]) > [number] -- 그룹화 조건
ORDER BY [alias_for_count] DESC -- 내림차순 정렬
LIMIT [limit_number]; -- 출력할 최대 행 개수

📌 SQL 쿼리 처리 순서

FROM 절: 데이터 소스를 지정하고 데이터를 가져옵니다.
JOIN 절: 하나 이상의 테이블을 결합합니다.
WHERE 절: 가져온 데이터 중 특정 조건에 맞는 행만 필터링합니다.
GROUP BY 절: 필터링된 결과를 특정 기준에 따라 그룹화합니다.
HAVING 절: GROUP BY를 통해 생성된 그룹에 조건을 적용합니다.
SELECT 절: 최종적으로 선택하고자 하는 컬럼이나 표현식을 지정합니다.
ORDER BY 절: 결과 집합을 특정 순서로 정렬합니다.

💡 ORDER BY 절은 결과를 정렬하는 데 사용되며, 그 정렬 기준이 되는 열이 SELECT 절이나 GROUP BY 절에 포함되어야 합니다.
LIMIT 절: 결과 집합의 크기를 제한합니다.

참고
https://www.w3schools.com/mysql/mysql_sql.asp SQL