03. SQL 기본
1. 관계형 데이터베이스 개요
1) 데이터베이스
용도와 목적에 맞는 데이터들끼리 저장하는 공간.
2) 관계형 데이터베이스(RDB, Relational Database)
관계형 데이터 모델에 기초를 둔 데이터베이스.
모든 데이터를 2차원 테이블 형태로 표현한 뒤 각 테이블 간의 관계를 정의.
Oracle, SQL Server(MSSQL), MySQL, MariaDB, PostgreSQL 등
3) TABLE
항목을 나타내는 각각의 세로 열을 컬럼(Column)이라고 하고 각각의 가로 행을 로우(Row)라고 한다.
데이터 모델에서 테이블은 엔터티에 해당하고 인스턴스는 로우, 속성에 해당하는 것은 컬럼이다.
관계형 데이터베이스의 기본 단위이고 데이터를 저장하기 위해 사용되며 일반적으로 여러개의 테이블로 구성된다.
4) SQL(Structured Query Language)
관계형 데이터베이스에서 데이터를 다루기 위해 사용하는 언어.
tip! SQL 테스트 사이트 (https://sqltest.net/#)
아래 예문들은 오라클 기준으로 작성되었습니다
2. SELECT 문
1) SELECT
저장되어 있는 데이터를 조회하고자 할 때 사용하는 명령어.
SELECT 컬럼1, 컬럼2, 컬럼3, ... FROM 테이블 WHERE 조건;
-- 별칭(Alias) 사용
SELECT 컬럼1 AS 별명, 컬럼2, ... FROM 테이블 AS 별명 WHERE 조건;
-- 전체 컬럼 조회(테이블의 컬럼 순서대로 조회된다.)
SELECT * FROM 테이블;
tip! 테이블명에 Alias를 설정했을 경우 테이블명 대신 Alias를 사용해야 한다.
2) 산술 연산자
사칙연산의 기능을 가진 연산자.
NUMBER DATE 유형의 데이터와 같이 사용할 수 있다.
| 연산자 | 의미 | 우선순위 |
|---|---|---|
| () | 괄호, 우선순위 조정 | 1 |
| * | 곱하기 | 2 |
| / | 나누기 | 2 |
| + | 더하기 | 3 |
| - | 빼기 | 3 |
SELECT
10*2 AS A,
10/2 AS B,
10+2 AS C,
10-2 AS D,
10*2/(8-7+1) AS E
FROM DUAL;
-- 조회 결과 각 20, 5, 12, 8, 10
SELECT
10*NULL AS A,
10-NULL AS B
FROM DUAL;
-- 조회 결과 각 NULL, NULL(다른 컬럼끼리 연산(가로 연산)에서 NULL이 포함되어 있으면 결과값은 NULL이 된다.)3) 합성 연산자
문자와 문자를 연결할 때 사용하는 연산자
SELECT '퓨' || '전' AS A FROM DUAL;
-- 조회 결과 '퓨전'(일부 DBMS에서만 사용 가능하다.)3. 함수
1) 문자 함수
CHR(ASCII 코드) : ASCII 코드를 인수로 입력했을 때 매핑되는 문자가 무엇인지 알려주는 함수
SELECT CHR(65) FROM DUAL;
-- 조회 결과 'A' LOWER(문자열) : 소문자 변환
SELECT LOWER('APPLE') FROM DUAL;
-- 조회 결과 'apple'UPPER(문자열) : 대문자 변환
SELECT UPPER('apple') FROM DUAL;
-- 조회 결과 'APPLE'LTRIM(문자열 [, 특정 문자]) / RTRIM(문자열 [, 옵션 문자]) : 각각 특정 문자를 명시해주지 않으면 각각 왼쪽/오른쪽 공백을 제거하고, 명시해주었을 경우 문자열을 왼쪽부터 한 글자씩 특정 문자와 비교하여 특정 문자에 포함되어 있으면 제거하고 포함되지 않았으면 멈춘다.
SELECT LTRIM(' APPLE ') FROM DUAL;
-- 조회 결과 'APPLE '
SELECT RTRIM(' APPLE ') FROM DUAL;
-- 조회 결과 ' APPLE'
SELECT LTRIM('APPLE PANCLE','AP') FROM DUAL;
-- 조회 결과 'LE PANCLE'
SELECT LTRIM('APPLE PANCLE','A') FROM DUAL;
-- 조회 결과 'PPLE PANCLE'TRIM([위치] [특정 문자] [FROM] 문자열) : 옵션이 하나도 없을 경우 문자열의 왼쪽과 오른쪽 공백을 제거하고, 그렇지 않을 경우 문자열 위치(LEADING/TRAILING/BOTH)로 지정된 곳부터 한 글자씩 특정 문자와 비교하여 같으면 제거하고 같지 않으면 멈춘다. 한 글자만 지정할 수 있다.
SELECT TRIM(' APPLE ') FROM DUAL;
-- 조회 결과 'APPLE'
SELECT TRIM(LEADING 'A' FROM 'APPLE') FROM DUAL;
-- 조회 결과 'PPLE'
SELECT TRIM(BOTH 'S' FROM 'SOS') FROM DUAL;
-- 조회 결과 'O'SUBSTR(문자열, 시작점 [, 길이]) : 문자열의 원하는 부분만 잘라서 반환한다. 길이를 명시하지 않았을 경우 문자열의 시작점부터 문자열의 끝까지 반환된다.
SELECT SUBSTR('APPLE',2,2) FROM DUAL;
-- 조회 결과 'PP'
SELECT SUBSTR('APPLE',2) FROM DUAL;
-- 조회 결과 'PPLE'LENGTH(문자열) : 문자열의 길이 반환
SELECT LENGTH('APPLE') FROM DUAL;
-- 조회 결과 5REPLACE(문자열, 변경 전 문자열 [, 변경 후 문자열]) : 문자열에서 변경 전 문자열을 찾아 변경 후 문자열로 바꿔준다. 변경 후 문자열을 명시해주지 않으면 문자열에서 변경 전 문자열을 제거한다.
SELECT REPLACE('APPLE PANCIL', 'PANCIL', 'WATCH') FROM DUAL;
-- 조회 결과 'APPLE WATCH'
SELECT REPLACE('APPLE PANCIL', 'PANCIL') FROM DUAL;
-- 조회 결과 'APPLE'2) 숫자 함수
ABS(수) : 절대값 반환
SELECT ABS(-1) FROM DUAL;
-- 조회 결과 1
SELECT ABS(1) FROM DUAL;
-- 조회 결과 1
SELECT ABS(33.5) FROM DUAL;
-- 조회 결과 33.5
SELECT ABS(-33.5) FROM DUAL;
-- 조회 결과 33.5SIGN(수) : 수의 부호 반환 양수이면 1, 음수이면 -1, 0이면 0을 반환한다.
SELECT SIGN(-99) FROM DUAL;
-- 조회 결과 -1
SELECT SIGN(99) FROM DUAL;
-- 조회 결과 1ROUND(수 [, 자릿수]) : 수를 지정된 자릿수까지 반올림하여 반환. 자릿수를 명시하지 않았을 경우 기본값은 0이며 반올림된 정수로 반환하고 자릿수가 음수일 경우 지정된 정수부를 반올림하여 반환한다.
SELECT ROUND(2008.0525, 1) FROM DUAL;
-- 조회 결과 2008.1
SELECT ROUND(120.99) FROM DUAL;
-- 조회 결과 121
SELECT ROUND(2023.083, -3) FROM DUAL;
-- 조회 결과 2000TRUNC(수 [, 자릿수]) : 수를 지정된 소수점 자릿수까지 버림하여 반환해주는 함수. 자릿수를 명시하지 않았을 경우 기본값은 0이며 버림된 정수로 반환하고 자릿수가 음수일 경우 지정된 정수부에서 버림하여 반환한다.
SELECT TRUNC(2008.0525, 1) FROM DUAL;
-- 조회 결과 2008
SELECT TRUNC(120.99) FROM DUAL;
-- 조회 결과 120
SELECT TRUNC(2023.083, -3) FROM DUAL;
-- 조회 결과 2000CEIL(수) : 소수점 이하의 수를 올림한 정수를 반환한다.
SELECT CEIL(78.98) FROM DUAL;
-- 조회 결과 79
SELECT CEIL(-78.98) FROM DUAL;
-- 조회 결과 -78
SELECT CEIL(-22.2) FROM DUAL;
-- 조회 결과 -22
SELECT CEIL(-22.2) FROM DUAL;
-- 조회 결과 23FLOOR(수) : 소수점 이하의 수를 버림한 정수를 반환해주는 함수.(n보다 '작거나 같은' 가장 큰 정수값을 반환한다.)
SELECT FLOOR(22.3) FROM DUAL;
-- 조회 결과 22
SELECT FLOOR(-22.3) FROM DUAL;
-- 조회 결과 -23MOD(수1, 수2) : 수1을 수2로 나눈 나머지를 반환한다. MOD 함수의 두 번째 인자값이 0이면 첫 번째 인자값이 결과로 도출된다. 또 두 인자값이 모두 음수이면 나머지도 그대로 음수값으로 도출된다.
SELECT MOD(15,7) FROM DUAL;
-- 조회 결과 1
SELECT MOD(15,-4) FROM DUAL;
-- 조회 결과 3
SELECT MOD(15,0) FROM DUAL;
-- 조회 결과 15
SELECT MOD(-15,-4) FROM DUAL;
-- 조회 결과 -33) 날짜 함수
SYSDATE : 현재의 연, 월, 일, 시, 분, 초를 반환한다.(nls_date_format에 따라서 sysdate의 출력 양식은 달라질 수 있다.)
SELECT SYSDATE FROM DUAL;
-- 조회 결과 2023/08/30EXTRACT(특정 단위 FROM 날짜 데이터) : 날짜 데이터에서 특정 단위(YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)만을 출력해서 반환한다.
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
-- 조회 결과 2023ADD_MONTHS(날짜 데이터, 특정 개월 수) : 날짜 데이터에서 특정 개월 수를 더한 날짜를 반환한다. 날짜의 이전 달이나 다음 달에 기준 날짜의 일자가 존재하지 않으면 해당 월의 마지막 일자가 반환된다.
SELECT ADD_MONTHS(TO_DATE('2023-08-15', 'YYYY-MM-DD'), 3) FROM DUAL;
-- 조회 결과 2023/11/15
SELECT ADD_MONTHS(TO_DATE('2023-08-31', 'YYYY-MM-DD'), -2) FROM DUAL;
-- 조회 결과 2023/06/30 (6월은 30일까지만 있으므로 마지막 일자가 반환됨)4) 변환 함수
(1) 명시적 형변환과 암시적 형변환
명시적 형변환 : 변환 함수를 사용하여 데이터 유형 변환을 명시적으로 나타냄
암시적 형변환 : 데이터베이스가 내부적으로 알아서 데이터 유형을 변환함. 때에 따라서 인덱스를 사용할 수 없는 등의 성능상 문제를 일으킬 수 있으므로 되도록 명시적 형변환을 하는 것이 바람직하다.
(2) 명시적 형변환에 쓰이는 함수
TO_NUMBER(문자열) : 문자열을 숫자로 변환한다.
SELECT TO_NUMBER('12345') FROM DUAL;
-- 조회 결과 12345
SELECT TO_NUMBER('AAA') FROM DUAL;
-- 오류 발생TO_CHAR(수 or 날짜 [, 포맷]) : 수나 날짜형의 데이터를 포맷 형식의 문자형으로 변환한다.
SELECT TO_CHAR(12345) FROM DUAL;
-- 조회 결과 '12345'
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH24MISS') FROM DUAL;
-- 조회 결과 '23/08/30 064046'TO_DATE(문자열, 포맷) : 포맷 형식의 문자형의 데이터를 날짜형으로 변환한다.
| 포맷 표현 | 의미 |
|---|---|
| YYYY | 년 |
| MM | 월 |
| DD | 일 |
| HH | 시(12) |
| HH24 | 시(24) |
| MI | 분 |
| SS | 초 |
SELECT TO_DATE('20230830', 'YYYYMMDD') FROM DUAL;
-- 조회 결과 2023/08/305) NULL 관련 함수
NVL(인수1, 인수2) : 인수1의 값이 NULL일 경우 인수2를 반환하고 NULL이 아닐 경우 인수1을 반환해주는 함수이다.
SELECT NVL(NULL, 1) FROM DUAL;
-- 조회 결과 1
SELECT NVL(2, 1) FROM DUAL;
-- 조회 결과 2NULLIF(인수1, 인수2) : 인수1과 인수2가 같으면 NULL을 반환하고 같지 않으면 인수1을 반환한다.
SELECT NULLIF(5, 5) FROM DUAL;
-- 조회 결과 NULL
SELECT NULLIF(5, 1) FROM DUAL;
-- 조회 결과 5COALESCE(인수1, 인수2, 인수3 ...) : NULL이 아닌 최초의 인수를 반환한다.
SELECT COALESCE(5, NULL, 3) FROM DUAL;
-- 조회 결과 5
SELECT COALESCE(NULL, 7, 1) FROM DUAL;
-- 조회 결과 7
SELECT COALESCE(NULL, NULL, NULL) FROM DUAL;
-- 조회 결과 NULL6) CASE
SELECT
CASE
WHEN 100 = 100 THEN 'A'
WHEN 100 = 90 THEN 'B'
WHEN 100 = 80 THEN 'C'
[ELSE 'ETC']
END
FROM DUAL;
-- 조회 결과 'A'
SELECT
CASE 100
WHEN 100 THEN 'A'
WHEN 90 THEN 'B'
WHEN 80 THEN 'C'
[ELSE 'ETC']
END
FROM DUAL;
-- 조회 결과 'A'
SELECT
DECODE(100,100,'A',90,'B',80,'C')
FROM DUAL;
-- 조회 결과 'A'(DECODE는 오라클에서 제공하는 함수이다.)4. WHERE 절
INSERT를 제외한 DML문을 수행할 때 원하는 데이터만 골라 수행할 수 있도록 해주는 구문이다.
SELECT 컬럼명1, 컬럼명2 ...
FROM 테이블명
WHERE 조건절;
UPDATE 테이블명
SET 컬럼명 = 새로운 데이터
WHERE 조건절;
DELETE
FROM 테이블명
WHERE 조건절;1) 비교 연산자
=, <, >, <=, >=
문자형 컬럼을 비교 조건으로 사용하려면 우측 상수값을 반드시 인용부호(')로 감싸주어야 한다.
2) 부정 비교 연산자
!=, ^=, <>, NOT 컬럼명 =, NOT 컬럼명 >
tip! 논리 연산자는 SQL에 명시된 순서와는 관계없이 () -> NOT -> AND -> OR 순으로 처리된다.
3) SQL 연산자
| 연산자 | 의미 | 예시 |
|---|---|---|
| BETWEEN A AND B | A와 B의 사이(A,B 포함) | WHERE COL BETWEEN 1 AND 10 |
| LIKE '비교 문자열' | 비교 문자열을 포함 | WHERE COL LIKE 'APPLE%' |
| IN(LIST) | LIST 중 하나와 일치 | WHERE COL IN (5, 2, 15) |
| IS NULL | NULL 값 | WHERE COL IS NULL |
tip! ESCAPE
'' 혹은 '%'가 포함된 문자를 검색하고자 할 때 ESCAPE를 지정해서 쿼리를 작성할 수 있다. ESCAPE '#'로 지정해주면 '#%' 혹은 '#'가 문자 그대로 인식될 수 있다.
4) 부정 SQL 연산자
| 연산자 | 의미 | 예시 |
|---|---|---|
| NOT BETWEEN A AND B | A와 B의 사이가 아님(A,B 미포함) | WHERE COL NOT BETWEEN 1 AND 10 |
| NOT IN(LIST) | LIST 중 일치하는 것이 없음 | WHERE COL NOT IN (5, 2, 15) |
| IS NOT NULL | NULL 값이 아님 | WHERE COL NOT IS NULL |
5) 논리 연산자
| 연산자 | 의미 | 예시 |
|---|---|---|
| AND | 모든 조건이 TRUE여야 함 | WHERE COL>1 AND COL<10 |
| OR | 하나 이상의 조건이 TRUE여야 함 | WHERE COL=1 OR COL=10 |
| NOT | TRUE면 FALSE이고 FALSE이면 TRUE | WHERE NOT COL>10 |
5. GROUP BY, HAVING 절
1) GROUP BY
데이터를 그룹별로 묶을 수 있도록 해준다.
SELECT 컬럼명1, 컬럼명2 ...
FROM 테이블명
WHERE 조건절
GROUP BY 컬럼명1, 컬럼명2 ...2) 집계 함수
데이터를 그룹별로 나누면 그룹별로 집계 데이터를 도출하는 것이 가능해진다.
함수명 | 설명
:---: |:---:
COUNT(*) | 전체 ROW를 COUNT하여 반환
COUNT(컬럼명) | 컬럼값이 NULL인 ROW를 제외하고 COUNT하여 반환
COUNT(DISTINCT 컬럼명) | 컬럼값이 NULL인 ROW에서 중복을 제외한 COUNT를 반환
SUM(컬럼) | 합계
AVG(컬럼) | 평균
MIN(컬럼) | 최솟값
MAX(컬럼) | 최댓값
3) HAVING
GROUP BY 절을 사용할 때 WHERE 절처럼 사용하는 조건절, 주로 데이터를 그룹핑한 후 특정 그룹을 골라낼때 사용한다.
SELECT 컬럼명1, 컬럼명2 ...
FROM 테이블명
WHERE 조건절
GROUP BY 컬럼명1, 컬럼명2 ...
HAVING 조건절6. ORDER BY 절
1) ORDER BY
SELECT한 데이터를 정렬한다. ORDER BY절을 명시하지 않으면 데이터는 임의의 순서대로 출력된다.
ASC(Ascending) : 오름차순(기본값)
DESC(Descending) : 내림차순
SELECT 컬럼명1, 컬럼명2 ...
FROM 테이블명
WHERE 조건절
GROUP BY 컬럼명1, 컬럼명2 ...
HAVING 조건절
ORDER BY 컬럼명1, 컬럼명2 ...
tip! SELECT 문의 논리적 수행 순서
⑤ SELECT
① FROM
② WHERE
③ GROUP BY
④ HAVING
⑥ ORDER BY
tip! 정렬 속 NULL?!
정렬의 기준이 되는 컬럼에 NULL 데이터가 포함되어 있을 경우 데이터베이스 종류에 따라 정렬의 위치가 달라지는데 Oracle의 경우에는 NULL을 최대값으로 취급하기 때문에 오름차순을 했을 경우 맨 마지막에 위치하게 된다.(SQL Server는 반대) 만약 순서를 변경하고 싶은 경우 NULLS FIRST, NULLS LAST 옵션을 써서 변경할 수 있다.
SELECT 7. JOIN
1) JOIN
각기 다른 테이블을 한 번에 조회할 때 사용한다.
2) EQUI JOIN
EQUAL(=) 조건으로 JOIN하는 것
SELECT A.COL, B.COL ...
FROM 테이블 A, 테이블 B
WHERE A.COL = B.COL3) Non EQUI JOIN
EQUAL(=) 조건이 아닌 다른 조건(BETWEEN, >, >=, <, <=)으로 JOIN하는 것
4) 3개 이상 TABLE JOIN
JOIN 쿼리를 좀 더 확장해서 3개 이상의 테이블을 JOIN할 수도 있다.
SELECT A.COL, B.COL ...
FROM 테이블 A, 테이블 B, 테이블 C
WHERE A.COL = B.COL
AND B.COL2 BETWEEN C.COL AND C.COL2;
tip! 동일한 컬럼명이 각각의 테이블의 존재할 경우 컬럼명 앞에 반드시 테이블명이나 Alias를 명시해주어야 한다.
5) OUTHER JOIN
JOIN 조건에 만족하지 않는 행들도 출력되는 형태이다.
LEFT OUTER JOIN의 경우 LEFT TABLE과 RIGTH TABLE의 데이터 중 JOIN에 성공한 데이터와 JOIN에 성공하지 못한 나머지 LEFT TABLE의 데이터가 함께 출력된다. Oracle에서는 모든 행이 출력되는 테이블의 반대편 테이블의 옆에 (+) 기호를 붙여 작성한다.
SELECT *
FROM 테이블 A, 테이블 B
WHERE A.COL = B.COL(+)8. STANDARD JOIN
DBMS에 여러 벤더가 존재한다.(Oracle, SQL Server(MSSQL), MySQL, MariaDB, PostgreSQL 등) 벤더마다 SQL 문법에 차이가 너무 클 경우 호환성 이슈가 발생하고 SQL을 사용하는 사람들 입장에서도 효율성이 떨어지기 때문에 표준이 되는 ANSI SQL을 지정하게 되었다.(STANDARD JOIN 보다는 ANSI JOIN, 표준 조인이라 칭한다.)
1) INNER JOIN
JOIN 조건에 충족하는 데이터만 출력하는 방식
SELECT *
FROM 테이블 A INNER JOIN 테이블 B
ON A.COL = B.COL2) OUTHER JOIN
JOIN 조건에 충족하는 데이터가 아니어도 출력될 수 있는 방식
(1) LEFT OUTHER JOIN
SQL에서 왼쪽에 표기된 테이블의 데이터는 무조건 출력되는 JOIN. 오른쪽 테이블에 JOIN되는 데이터가 없는 ROW들은 오른쪽 테이블 컬럼의 값이 NULL로 출력된다.
(2) RIGHT OUTHER JOIN
SQL에서 오른쪽에 표기된 테이블의 데이터는 무조건 출력되는 JOIN
SELECT *
FROM 테이블 A LEFT OUTHER JOIN 테이블 B
ON A.COL = B.COL(3) FULL OUTHER JOIN
왼쪽, 오른쪽 테이블의 데이터가 모두 출력되는 방식이다. (단, 중복값은 제거)
SELECT *
FROM 테이블 A FULL OUTHER JOIN 테이블 B
ON A.COL = B.COL3) NATURAL JOIN
A 테이블과 B 테이블에서 같은 이름을 가진 컬럼들이 모두 동일한 데이터를 가지고 있을 경우 JOIN되는 방식. 공통 컬럼 앞에 OWNER 명을 붙이면 에러가 발생한다. SQL Server(MSSQL)에서는 지원하지 않는다.
- A청과 테이블
| 과일 | 가격 |
|---|---|
| 사과 | 3000 |
| 배 | 3500 |
| 복숭아 | 4000 |
| 바나나 | 1800 |
- B청과 테이블
| 과일 | 가격 |
|---|---|
| 사과 | 3000 |
| 체리 | 5000 |
| 귤 | 1000 |
| 바나나 | 1800 |
SELECT 과일, 가격
FROM A청과 A NATURAL JOIN B청과 B;| 과일 | 가격 |
|---|---|
| 사과 | 3000 |
| 바나나 | 1800 |
이때 B청과의 사과 가격을 변경하면 동일하지 않은 데이터를 가지고 있기 때문에 JOIN시 출력되지 않는다. 이 경우 Oracle에서는 USING 조건절을 이용하여 같은 이름을 가진 컬럼중 원하는 컬럼만 JOIN에 이용할 수 있다. 단, SELECT 절에서 USING 절로 정의된 컬럼 앞에는 별도의 ALIAS나 테이블명을 붙이지 않아야 한다.
SELECT 과일, A.가격, B.가격
FROM A청과 A JOIN B청과 B
USING (과일)| 과일 | A.가격 | B.가격 |
|---|---|---|
| 사과 | 3000 | 6000 |
| 바나나 | 1800 | 1800 |
4) CROSS JOIN
A 테이블과 B테이블 사이에 JOIN 조건이 없는 경우, 조합할 수 있는 모든 경우를 출력하는 방식. 다른 말로 Cartesian Product라고 표현하기도 한다.
SELECT *
FROM 테이블 A CROSS JOIN 테이블 B
SELECT *
FROM 테이블 A, 테이블 B'SQLD' 카테고리의 다른 글
| 02. 데이터 모델과 SQL (0) | 2023.08.28 |
|---|---|
| 01. 데이터 모델링의 이해 (0) | 2023.08.24 |