[Database] SQL 기초

728x90

SQL 기본 개념

SQL(Structured Query Language)
- 관계형 데이터베이스에서 데이터 조회 및 조작, DBMS 시스템 관리 기능을 명령하는 언어
- DDL, DML, DCL 등으로 구분
- SQL 문법은 대소문자 구분 XSQL 기능에 따른 분류
데이터 정의어(DDL) -> CREATE, ALTER, DROP, TRUNCATE
데이터 조작어(DML) -> INSERT, UPDATE, DELETE, MERGE
데이터 제어어(DCL) -> GRANT, REVOKE
트랜잭션 제어 언어(TCL) -> COMMIT, ROLLBACK
데이터 질의 언어(DQL) -> SELECT

DDL

CREATE

create table EMPLOYEE (
    id INT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    birth_date DATE,
    sex CHAR(1) CHECK(sex in ('M', 'F')),
    position VARCHAR(10),
    salary INT DEFAULT 50000000,
    dept_id INT,
    FOREIGN KEY (dept_id) references DEPARTMENT(id) on delete SET NULL on update CASCADE,
    CHECK (salary >= 50000000)
);

Attribute Data Type

숫자
- 정수: 정수를 저장할 때 사용
  - TINYINT - 1byte (postgresql에는없음)
  - SMALLINT - 2byte
  - MEDIUMINT - 3byte (postgresql에는없음)
  - INT or INTEGER - 4byte
  - BIGINT - 8byte
- 부동 소수점 방식(floating-point): 실수를 저장할 때 사용, 고정 소수점 방식에 비해 정확하지 않음
  - FLOAT - 4byte
  - DOUBLE or DOUBLE PRECISION - 8byte
- 고정 소수점 방식(fixed-point): 실수를 정확하게 저장할 때 사용
  - DECIMAL or NUMERIC - variable
  - DECIMAL(precision, scale)
    - precision: 전체 몇자리인지
    - scale: 소수점 이하 몇자리인지
    - e.g.) DECIMAL(5,2) ⇒ [-999.99 ~ 999.99]
문자열
- 고정 크기 문자열(char)
  - 최대 몇 개의 ‘문자’를 가지는 문자열을 저장할지를 지정
  - 저장될 문자열의 길이가 최대 길이보다 작으면 나머지를 space로 채워서 저장
  - name char(4)일 때 다음과 같이 저장: ‘a ‘한국 ‘, ‘고고고고’, ‘wow ‘
  - CHAR(n) (단, 0 ≤ n ≤ 255)
- 가변 크기 문자열(varchar)
  - 최대 몇 개의 ‘문자’를 가지는 문자열을 저장할지를 지정
  - 저장될 문자열의 길이 만큼만 저장
  - name varchar(4)일 때 다음과 같이 저장: ‘a’, ‘한국’, ‘고고고고’, ‘wow’
  - postgresql의 경우 varchar를 쓸 것을 권유하며 mysql에서는 스토리지 상 이점은 있겠지만, 성능 상의 이점은 없기에 가변적인 경우에만 사용
  - VARCHAR(n) (단, 0 ≤ n ≤ 65,535)
- 사이즈가 큰 문자열
  - 사이즈가 큰 문자열을 저장할 때 사용
  - TINYTEXT
  - TEXT (postgresql에는 TEXT만 있음)
  - MEDIUMTEXT
  - LONGTEXT
날짜와 시간
- 날짜
  - 년, 월, 일을 저장
  - YYYY-MM-DD
  - DATE(’1000-01-01’ ~ ‘9999-12-31’)
- 시간
  - 시, 분, 초를 저장
  - hh:mm:ss or hhh:mm:ss
  - TIME(’-838:59:59’ ~ ‘838:59:59’)
- 날짜와 시간
  - 날짜와 시간을 같이 표현
  - YYYY-MM-DD hh:mm:ss
  - TIMESTAMP는 time-zone이 반영됨
  - DATETIME (’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’)
  - TIMESTAMP (’1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC)
그 외
- btye-string
  - BINARY
  - VARBINARY
  - BLOB type
- boolean
  - true, false 저장
  - MySQL에는 따로 없음(PostgreSQL에는 있음) -> TINYINT로 대체
- 위치 관련
  - GEOMETRY
  - etc
- JSON
  - JSON
  - e.g. { "name": "messi", "age": 38 }PRIMARY KEY 선언하는 2가지 방법

attribute 하나로 구성될 때

create table PLAYER (
  id INT PRIMARY KEY
  ...
);

attribute가 하나 이상으로 구성될 때

create table PLAYER (
  team_id VARCHAR(12),
  back_number INT,
  ...
  PRIMARY KEY (team_id, back_number)
);

UNIQUE 선언하는 2가지 방법

attribute 하나로 구성될 때

create table PLAYER (
  id INT UNIQUE
  ...
);

attribute가 하나 이상으로 구성될 때

create table PLAYER (
  team_id VARCHAR(12),
  back_number INT,
  ...
  UNIQUE (team_id, back_number)
);

NOT NULL 적용DEFAULT 적용CHECK 적용

create table Orders ( ... menu varchar(15) DEFAULT '짜장면', ... );
create table Student ( ... phone_number INT NOT NULL UNIQUE, ... );

attribute 하나로 구성될 때

create table EMPLOYEE (
  ...
  age INT CHECK (age >= 20)
);

attribute가 하나 이상으로 구성될 때

create table PROJECT (
  start_date DATE,
  end_date DATE,
  ...
  CHECK (start_date < end_date)
);

Foreign Key 적용

CREATE TABLE Employee( ... dept_id INT, FOREIGN KEY (dept_id) references DEPARTMENT(id) on delete reference_option on update reference_option );
reference_option
- CASCADE: 참조값의 삭제/변경을 그대로 반영
- SET NULL: 참조값이 삭제/변경 시 NULL로 변경
- RESTRICT: 참조값이 삭제/변경되는 것을 금지
- NO ACTION: RESTRICT와 유사
```
- 한 트랜잭션 내의 여러개의 SQL이 실행되는 동안에는 참조값이 변경/삭제 되는 것을 허용하지만, 그 트랜잭션이 끝났을 때 여전히 referential integrity constraint를 위반하고 있다면, 이것은 금지한다는 의미.
```
- SET DEFAULT: 참조값이 삭제/변경 시 default 값으로 변경

ALTER

table이 생성이 되고 난 뒤에 스키마의 변경이 필요할 때 사용

ALTER TABLE department ADD FOREIGN KEY (leader_id)
REFERENCES employee(id)
on update CASCADE,
on delete SET NULL;

attribute 추가
- ALTER TABLE employee ADD blood VARCHAR(2);
attribute 이름 변경
- ALTER TABLE employee RENAME COLUMN phone TO phone_num;
attribute 타입 변경
- ALTER TABLE employee MODIFY COLUMN blood CHAR(2);
table 이름 변경
- ALTER TABLE logs RENAME TO backend_logs;
primary key 추가
- ALTER TABLE log ADD PRIMARY KEY (id);
…

DROP

테이블을 삭제하는 명령
테이블의 구조와 데이터를 모두 삭제하므로 사용에 주의해야 함

DROP TABLE book;

주의 DROP 문은 자동 COMMIT 되는 명령어 이므로, 이미 지운 데이터를 되돌릴 수 없다.

TRUNCATE

테이블의 구조는 남겨두고 데이터만 전부 삭제하는 명령

TRUNCATE TABLE book;

주의 TRUNCATE 문은 자동 COMMIT 되는 명령어 이므로, 이미 지운 데이터를 되돌릴 수 없다.

DELETE vs TRUNCATE vs DROP

DML

추가(Insert), 수정(Update), 삭제(Delete)

추가
```
-- (1)
INSERT INTO employee VALUES (1, 'MESSI', '1987-02-01', 'M', 'DEV_BACK', 100000000, null);

-- (2)
INSERT INTO employee (name, birth_date, sex, position, position, id) VALUES ('JENNY', '2000-10-12', 'F', 'DEV_BACK', 3);
```
- 테이블을 생성할 때 정의한 attribute 순서대로 값을 넣어야 함!
- 모든 대응되는 값을 넣어주어야 함. (갯수 맞춰서)
- 우리가 선언한 constraint에 맞지 않게 값을 넣으면 에러를 발생시킴.
- employee 다음에 나열한 attribute에 대응되는 값들만 넣어주는 방법도 있다.
- 이때 언급해주지 않은 값은 default 값으로 들어가거나, null이 됨.

수정(Update)

UPDATE table_name(s)
SET attribute = value [, attribute = value, .. ]
[ WHERE condition(s) ];

삭제(Delete)

DELETE FROM table_name
[ WHERE condition(s)];

DQL - 조회(Select)

SELECT attribute(s)
FROM table(s)
[ WHERE condition(s)];

AS 키워드
DISTINCT 키워드
LIKE 키워드
*(astreisk)
IN 키워드
EXISTS/NOT EXISTS 키워드
ANY 키워드
ALL 키워드

Subquery

SELECT birth_date FROM employee WHERE id = 14;
-- 결과로 '1992-08-04' 조회
-- 이후 그 생일보다 빠른 임직원을 찾기 위해
SELECT id, name, birth_date FROM employee WHERE birth_date < '1992-08-04';

-- 위처럼 두 번의 쿼리를 날려 확인할 수도 있겠지만 서브쿼리로 하나로 합칠 수 있음

SELECT id, name, birth_date FROM employee 
WHERE birth_date < (
        SELECT birth_date FROM employee WHERE id = 14
);

상관 부속질의(Correlated subquery)는 상위 부속질의의 튜플을 이용하여 하위 부속질의를 계산함
상위 부속질의와 하위 부속질의가 독립적이지 않고 서로 관련을 맺고 있음
subquery(nested query or inner query): SELECT, INSERT, UPDATE, DELETE에 포함된 query
- 위에서는 SELECT birth_date FROM employee WHERE id = 14가 해당
outer query(main query): subquery를 포함하는 query
subquery는 () 안에 기술된다.

NULL

NULL의 의미
- unknown: 알려지지 않음
- unavailable or withheld: 공개하지 않음
- not applicable: 해당 사항 없음
  하나의 null이 여러 의미를 갖게 됨.
NULL과 Three-values logic
- SQL에서 NULL과 비교 연산을 하게 되면 그 결과는 FALSE가 아니라 UNKNOWN이다.
- UNKNOWN은 ‘TRUE 일 수도 있고, FALSE일 수도 있다’라는 의미이다.
- three-valued logic: 비교/논리 연산의 결과로 TRUE, FALSE, UNKNOWN을 가진다.
  - 1 = 1 → TRUE
  - 1 ≠ 1 → FALSE
  - 1 = NULL → UNKNOWN
  - 1 ≠ NULL → UNKNOWN
  - 1 > NULL → UNKNOWN
  - 1 ≤ NULL → UNKNOWN
  - NULL = NULL → UNKNOWN

Join

Implicit Join vs Explicit Join

Implicit Join
```
  SELECT D.name
  FROM employee AS E, department AS D
  WHERE E.id = 1 AND E.dept_id = D.id;
```
- from 절에는 table들만 나열하고 where 절에 join condition을 명시하는 방식
- old-style join syntax
- where 절에 selection condition과 join condition이 같이 있기 때문에 가독성이 떨어진다.
- 복잡한 join 쿼리를 작성하다 보면 실수로 잘못된 쿼리를 작성할 가능성이 크다.
Explicit Join
```
  SELECT D.name
  FROM employee AS E JOIN department AS D ON E.dept_id = D.id
  WHERE E.id = 1;
```
- from 절에 JOIN 키워드와 함께 joined table들을 명시하는 방식
- from 절에서 ON 뒤에 join condition이 명시된다.
- 가독성이 좋다
- 복잡한 join 쿼리 작성 중에도 실수할 가능성이 적다.

Inner Join vs Outer Join

Inner Join
두 table에서 join condition을 만족하는 tuple들로 result table을 만드는 join
FROM table1 [INNER] JOIN table2 ON join_condition
- INNER는 생략해도 됨.
join condition에 사용 가능한 연산자: =, <, >, ≠ 등등 여러 비교 연산자 가능
join condition에서 null 값을 가지는 tuple은 result table에 포함되지 못한다.
- null이 있다면 연산 시 true가 아닌 false나 unknown을 반환하기 때문에 join condition을 만족하지 않음.
Outer Join
- 두 table에서 join condition을 만족하지 않는 tuple들도 result table에 포함하는 join
- FROM table1 LEFT [OUTER] JOIN table2 ON join_condition
- FROM table1 RIGHT [OUTER] JOIN table2 ON join_condition
- FROM table1 FULL [OUTER] JOIN table2 ON join_condition
- join condition에 사용 가능한 연산자: =, <, >, ≠ 등등 여러 비교 연산자 가능

Equi Join

join condition에서 =(equality comparator)를 사용하는 join

Using

두 table이 equi join 할 때 join하는 attribute의 이름이 같다면, USING으로 간단하게 작성할 수 있다.
이 때 같은 이름의 attribute는 result table에서 한 번만 표시 된다.
FROM table1 [INNER] JOIN table2 USING (attribute(s))
FROM table1 LEFT [OUTER] JOIN table2 USING (attribute(s))
FROM table1 RIGHT [OUTER] JOIN table2 USING (attribute(s))
FROM table1 FULL [OUTER] JOIN table2 USING (attribute(s))

Natural Join

두 table에서 같은 이름을 가지는 모든 attribute pair에 대해서 equi join을 수행
join condition을 따로 명시하지 않는다
FROM table1 NATURAL [INNER] JOIN table2
FROM table1 NATURAL LEFT [OUTER] JOIN table2
FROM table1 NATURAL RIGHT [OUTER] JOIN table2
FROM table1 NATURAL FULL [OUTER] JOIN table2

Cross Join

두 table의 tuple pair로 만들 수 있는 모든 조합(= Cartesian product)을 result table로 반환한다
join condition이 없다
implicit cross join: FROM table1, table2 - (컴마로 연결)
explicit cross join: FROM table1 CROSS JOIN table2 (CROSS JOIN 키워드를 통해 명시적으로 연결)

Self Join

table이 자기 자신에게 join 하는 경우

Aggregate Function

여러 tuple들의 정보를 요약해서 하나의 값으로 추출하는 함수
대표적으로 COUNT, SUM, MAX, MIN, AVG 함수가 있다.
(주로) 관심있는 attribute에 사용된다. e.g.) AVG(salary), MAX(birth_date)
NULL 값들은 제외하고 요약 값을 추출한다.

Group By & HAVING

Group By
- 관심있는 attribute(s)를 기준으로 그룹을 나눠서 그룹별로 aggregate function을 적용하고 싶을 때 사용
- grouping attribute(s): 그룹을 나누는 기준이 되는 attribute(s)
- grouping attribute(s)에 NULL 값이 있을 때는 NULL 값을 가지는 tuple끼리 묶인다.
HAVING
- GROUP BY와 함께 사용한다
- aggregate function의 결과값을 바탕으로 그룹을 필터링하고 싶을 때 사용한다.
- HAVING 절에 명시된 조건을 만족하는 그룹만 결과에 포함된다.

SELECT 요약

SELECT attribute(s) or aggregate function(s)
FROM table(s)
[ WHERE condition(s) ]
[ GROUP BY group attribute(s) ]
[ HAVING group condition(s) ]
[ ORDER BY attribute(s) ];

FROM 먼저 실행 (조인이 있다면 조인 실행)
이후 WHERE 절로 필터링
GROUP BY가 있다면 그룹핑
HAVING 절이 있다면 조건에 맞춰서 그룹을 필터링
최종적으로 관심있는 attribute나 통계에 대해 select
ORDER BY가 있다면 정렬

728x90

'Database' 카테고리의 다른 글

[Database] SQL Trigger (0)	2024.10.05
[Database] Stored Function & Stored Procedure (2)	2024.10.05
[Database] SQL 고급 (1)	2024.10.05
[Database] Relational Database (0)	2024.10.05
[Database] 데이터베이스 기본 개념 (1)	2024.10.05

SQL 기본 개념

DDL

CREATE

Attribute Data Type

ALTER

DROP

TRUNCATE

DELETE vs TRUNCATE vs DROP

DML

추가(Insert), 수정(Update), 삭제(Delete)

DQL - 조회(Select)

Subquery

NULL

Join

Implicit Join vs Explicit Join

Inner Join vs Outer Join

Equi Join

Using

Natural Join

Cross Join

Self Join

Aggregate Function

Group By & HAVING

SELECT 요약

'Database' 카테고리의 다른 글

티스토리툴바