SQLD 문법 정리 - DML
2. DML(데이터 조작어, Data Manipulation Language)
2.1 SELECT
- 데이터를 조회하기 위해 사용함.
- SELECT [ALL | DISTINCT] 컬럼명, [,컬럼명2, …]
FROM 테이블명, [테이블명2, …]
[WHERE 조건식]
[GROUP BY 컬럼명 [HAVING 조건식]
[ORDER BY 조건식]]
[GROUP BY 컬럼명 [,컬럼명2, …]
[ORDER BY 컬럼명 [,컬럼명2, …]
2.1.1 ALL | DISTINCT
- ALL : 중복되는 데이터가 있어도 모든 데이터 반환.
- DISTINCT : 테이블에 중복된 값을 제거하고 1개만 출력.
2.1.2 컬럼 별칭
- 컬럼명 [AS] 별칭
- 컬럼 이름을 다른 이름으로 표시할수 있음.
2.1.3 단일 행 함수
- 함수를 사용해 데이터를 조작하여 반환할수 있음.
- SELECT, WHERE, ORDER BY 절에서 사용 가능.
- 문자 함수
- LOWER(str)
- 입력받은 문자열 소문자로 출력
- UPPER(str)
- 입력받은 문자열 대문자로 출력
- INITCAP(str)
- 첫 문자는 대문자로, 나머지는 소문자로 반환.
- 공백이나 알파벳이 아닌 문자를 만난 후 다음 첫 알파벳을 대문자로 출력.
- CONCAT(str1,str2, …)
- 여러 문자열을 하나로 합침.
- SUBSTR(str,pos,len)
- str의 pos번째 문자부터 len개를 출력함.
- len은 생략 가능.
- 생략시 pos번째부터 끝까지 출력
- INSTR(str,target,pos,itr)
- MSSQL에서는 CHARINDEX(target,str)로 사용.
- str에서 target과 일치하는 문자열의 위치를 반환.
- str의 pos번째부터 탐색을 시작함.
(기본값 = 1, 생략가능) - itr번째 나온 target의 위치를 반환
(기본값 = 1, 생략가능)
- LENGTH(str)
- ORACLE에서는 LENGTHB(),
MSSQL에서는 DATALENGTH() - 띄어쓰기를 포함한 문자열의 BYTE를 리턴함.
- ORACLE에서는 LENGTHB(),
- CHAR_LENGTH(str)
- ORACLE에서는 LENGTH(),
MSSQL에서는 LEN() - 띄어쓰기를 포함한 문자열의 문자 개수를 출력
- ORACLE에서는 LENGTH(),
- TRIM(str,option)
- str의 왼쪽, 오른쪽의 반복적인 option문자열을 제거한다.
- option문자열 생략 가능.
- 생략시 왼쪽, 오른쪽의 공백 제거.
- RTRIM(str, option), LTRIM(str,option)
- 각각 문자열 오른쪽,왼쪽의 반복되는 문자열/공백만 제거.
- REPLACE(str,target,change)
- str에서 target을 찾아내 change로 모두 바꾼다.
- LOWER(str)
- 숫자 함수
- ROUND(num,pos)
- num을 pos자리에서 반올림하여 반환.
- pos생략시 소수점 첫째자리에서 반올림.
ROUND(1234.545) => 1235
- pos가 양수면 소수점 pos번째 자리까지 표시후 반올림.
ROUND(1234.545, 1) => 1234.5
- pos가 음수면 정수의 pos번째 자리 반올림.
ROUND(1235.345, -1) => 1240
- 시간 역시 반올림이 가능하다.
이때, 정오를 기준으로 반올림 된다.ROUND(to_date((‘2021-08-27 15:10:59’, ‘yyyy-mm-dd hh24:mi’))) => 2021-08-28 00:00:00
- TRUNC(num,pos)
- num을 pos자리에서 버림 연산하여 반환.
- pos 생략시 소수점 버림.
TRUNC(1234.545) => 1234
- pos가 양수면 소수점 pos번째 자리까지 표시후 버림.
TRUNC(1234.567,1) => 1234.5
- pos가 음수면 정수의 pos번째 자리 반올림.
ROUND(1235.345, -1) => 1230
- 시간 역시 버림 연산이 가능하다.
- pos = ‘YYYY’ 또는 ‘YEAR’일때,
해당 년도를 초기화. - pos = ‘MM’ 또는 ‘MONTH’일때,
해당 달을 초기화. - pos = ‘DAY’일때,
해당 주를 초기화(일요일을 반환). - pos = ‘DD’ 또는 pos를 생략했을때,
시,분,초를 초기화. - pos = ‘HH24’ 일때,
분,초를 초기화. - pos = ‘MI’일때,
초를 초기화.
- pos = ‘YYYY’ 또는 ‘YEAR’일때,
- MOD(num, sep)
- num을 sep으로 나눈 나머지 값을 반환.
- 몫은 그냥 나누기 연산 하면 나온다.
- ROUND(num,pos)
- 날짜 함수
- 데이터 형식 변환 함수
- TO_CHAR
- TO_NUMBER
- TO_DATE
- 문자 함수
2.1.4 집계 함수
- 그룹화를 한 행에 대해 하나의 결과를 반환하는 함수.
- SELECT 절 (단, GROUP BY 절에 지정된 열만) HAVING 절, ORDER BY 절에서 사용할 수 있다.
- 중첩은 2단계까지 가능하다.
- SUM(컬럼)
- 지정한 컬럼의 합을 계산해준다.
- AVG(컬럼)
- 지정한 컬럼의 평균을 계산해준다.
- COUNT(컬럼)
- 지정한 컬럼의 총 갯수를 계산해준다.
- row의 총 갯수를 반환하며, 컬럼 이름 대신 *을 넣어도 됨.
- MAX(컬럼)
- 지정한 컬럼중 가장 큰 값을 반환.
- MIN(컬럼)
- 지정한 컬럼중 가장 작은 값을 반환.
- VARIANCE(컬럼)
- 지정한 컬럼의 분산을 계산하여 반환.
- STDDEV(컬럼)
- 지정한 컬럼의 표준편차를 계산하여 반환.
- SUM(컬럼)
2.1.5 그룹 함수
- 집계 함수를 사용할때 어느 컬럼에 대해 통계를 낼지 지정하는 부분.
조건절 GROUP BY 컬럼명1, 컬럼명2, …;
- 그룹에 대해 조건을 지정하고 싶을때는 HAVING 구문을 사용한다.
조건절 GROUP BY 컬럼명, … HAVING (그룹 함수 포함 조건)
- ROLLUP
- GROUP BY절을 통해 그룹 지어진 결과에 대해 더 상세한 통계를 제공.
- SELECT된 데이터와 그 데이터의 총계를 구할수 있다.
조건절 FROM 테이블 GROUP BY ROLLUP(컬럼명)
- CUBE
- Cross-Tab에 대한 Summary를 추출하는데 사용된다.
GROUP BY CUBE(컬럼명1, 컬럼명2)
- Cross-Tab에 대한 Summary를 추출하는데 사용된다.
- GROUPING
- 해당 ROW가 GROUP BY에 의해 산출된 Row일때 0을 반환,
ROLLUP이나 CUBE에 의해 산출된 Row일때 1을 반환.SELECT 컬럼명, GROUPING(컬럼명) “명칭” …
- 해당 ROW가 GROUP BY에 의해 산출된 Row일때 0을 반환,
- ROLLUP, CUBE, GROUPING에 대한 자세한 정보는 홍반장님의 글에 정리되어 있다.
2.1.6 집합 연산
- 여러 테이블의 질의결과를 참조하는 복합문을 처리하기 위한 구문.
- 다음과 같은 조건에서 사용할수 있다.
- 두 집합의 SELECT절에 오는 컬럼 개수가 동일해야 함.
- 두 집합의 SELECT절에 오는 컬럼의 데이터 타입이 동일해야 함.
- UNION
- 여러 sql 구문의 결과에 대한 합집합을 반환.
- 중복된 행은 하나만 출력됨.
(SELECT 구문) UNION (SELECT 구문)
- UNION ALL
- 여러 sql구문의 결과에 대한 합집합을 반환.
- UNION과 달리, 중복된 행도 모두 출력.
(SELECT 구문) UNION ALL (SELECT 구문)
- INTERSECT
- 여러 sql구문의 결과에 대한 교집합을 반환.
- 중복된 행은 하나만 출력된다.
(SELECT 구문) INTERSECT (SELECT 구문)
- EXCEPT
- 앞의 sql문의 결과에서 뒤의 sql문의 결과에 대한 차집합을 반환.
- 중복된 행은 하나로 만든다.
- 일부 데이터베이스는 MINUS로 사용한다.
(SELECT 구문) EXCEPT (SELECT 구문)
2.1.6 JOIN 연산
- 둘 이상의 테이블을 연결해 데이터를 검색하는 방법.
- 두 테이블 간에 적어도 하나의 컬럼을 공유하고 있어야함.
- 공유하는 컬럼을 PK 또는 FK값으로 사용한다.
- exp9405님이 정리해두신 UNION과 JOIN의 차이점.
- SQL문의 결과를 합집합, 교집합 하면 UNION
- 두 테이블에서 합집합, 교집합 한 결과를 출력하면 JOIN
- godpearl님의 DB JOIN 정리도 참고하면 좋다.
- INNER JOIN
- 공통되는 부분만 테이블을 합쳐서 반환.
- LEFT JOIN
- 공통되는 부분 + LEFT에 있는것만 테이블을 합쳐서 반환.
- FROM A LEFT OUTER JOIN B 일때, A의 데이터만 표기.
- 이때, LEFT에만 있는 데이터는 NULL로 표기된다.
- 따라서, WHERE 절을 통해 NULL값을 찾으면 차집합 느낌으로 사용가능.
WHERE B.ID = NULL
- RIGHT JOIN
- 공통되는 부분 + RIGHT에 있는것만 테이블을 합쳐서 반환.
- FROM A RIGHT OUTER JOIN B일때, B의 데이터만 표기.
- 이때, RIGHT에만 있는 데이터는 NULL로 표기된다.
- 따라서, WHERE 절을 통해 NULL값을 찾으면 차집합 느낌으로 사용 가능.
WHERE A.ID = NULL
- OUTER JOIN
- A,B 테이블 각자 가지고 있는것을 테이블 합쳐서 모두 반환.
- FROM A FULL OUTER JOIN B
- LEFT 또는 RIGHT만 가진 값은 NULL로 표기된다.
- 따라서, WHERE 절을 통해 NULL값을 찾으면 합집합-교집합 느낌으로 사용 가능.
WHERE A.ID is NULL OR B.ID is NULL
- INNER JOIN
2.1.7 패턴 매칭 검색
- WHERE 절에서 데이터를 추출하는 선택 조건식을 지정.
- 패턴 일치에 사용되는 와일드 카드로 ‘%’를 사용한다.
WHERE 컬럼명 LIKE ‘A%’
- 지정한 컬럼의 데이터 앞부분에 ‘A’문자값이 있다면 SELECT된다.
- ‘A’문자값 뒤에 어떠한 데이터가 와도 검색된다.
- ’%’는 문자 앞, 뒤 모두 사용 가능하다
‘A%’ ‘%A’ ‘%A%’
2.1.8 ORDER BY절
- 구문의 결과값을 정렬할때 사용한다.
- 쉼표로 구분하여 여러 열을 지정할수 있다.
- 일반적으로 ASC(ascending order, 오름차순) 지정이다.
- 내림차순 정렬하려면 DESC(descending order)로 표기한다.
2.1.9 SQL의 실행 순서
- FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
2.2 INSERT
- 테이블에 값을 넣을때 사용한다.
2.2.1 INSERT INTO VALUE
INSERT INTO 테이블명 (열1, 열2, …)
VALUE (값1, 값2, …)
- 지정한 값을 테이블에 넣을때 사용.
2.2.2 INSERT INTO SELECT
INSERT INTO 테이블1 (열1, 열2, …)
SELECT 컬럼1, 컬럼2, … FROM 테이블2
WHERE 조건식
- 테이블2에서 조건에 맞는 컬럼1,컬럼2 …를 테이블1에 넣을때 사용.
2.3 UPDATE
- 테이블의 값을 갱신할때 사용한다.
UPDATE 테이블명 SET 컬럼 = “갱신할 값” WHERE 조건
- 조건절 생략 가능.
- 생략시 지정한 테이블의 모든 해당 컬럼 값이 변경됨.
2.4 DELETE
- 테이블 값을 제거할때 사용한다.
DELETE FROM 테이블명 WHERE 조건
- 조건절 생략 가능
- 생략시 지정한 테이블내의 모든 행이 삭제됨.