[SQLD] 2과목. SQL 기본 및 활용 (1) - SQL 기본 요약

과목 2. SQL 기본 및 활용

SQL 기본

 

1절. 관계형 데이터베이스

● DB

특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것

(DBMS : DB 관리 SW)

 

● SQL

관계형 DB에서 데이터 정의, 조작, 제어를 위해 사용하는 언어

 

● SQL 문장들의 종류

1) DML (데이터 조작어)

SELECT, INSERT, UPDATE, DELETE 등

2) DDL (데이터 정의어)

CREATE, ALTER, DROP, RENAME 등

3) DCL (데이터 제어어)

GRANT, REVOKE

4) TCL (트랜잭션 제어어)

COMMIT, ROLLBACK, SAVEPOINT 등

 

● 테이블

데이터를 저장하는 객체로 관계형 데이터베이스의 기본 단위

로우(가로, 행)와 칼럼(세로, 열)으로 구성

 

 

 

2절. DDL

● 데이터 유형

1) CHAR(s) : 고정 길이 문자열 정보 (‘AA’ = ‘AA ’)

2) VARCHAR(s) : 가변 길이 문자열 정보 (‘AA’ != ‘AA ’)

3) NUMERIC : 정수, 실수 등 숫자 정보

4) DATE : 날짜와 시각 정보

 

[참고]

1) CHAR형은 사이즈에 비해 작은 값이 들어올 경우 나머지 사이즈를 ‘ ’ (스페이스)로 채운다.

Oracle : length(' ') -> 1

MSSQL : len(' ') -> 0

ex. CHAR(10) 컬럼 A에 VALUE('1')를 넣을 경우

Oracle : length(A) -> 10

MSSQL : len(A) -> 1

2) 공백, 스페이스, NULL 크기

	len('')	len(' ')	len(NULL)
Oracle	NULL	1	NULL
MSSQL	0	0	NULL

 

● CREATE TABLE

1) 테이블 명은 다른 테이블의 이름과 중복 X

2) 테이블 내의 칼럼명은 중복 X

3) 각 칼럼들은 , 로 구분되고 ; 로 끝남

4) 칼럼 뒤에 데이터 유형은 꼭 지정

5) 테이블명과 칼럼명은 반드시 문자로 시작 (숫자로 시작 X)

6) A-Z,a-z,0-9,_,$,#만 사용 가능

7) DATETIME 데이터 유형에는 별도로 크기를 지정 X

 

● 제약조건 (CONSTRAINT)

1) PRIMARY KEY : 기본키 정의 (UNIQUE & NOT NULL)

2) UNIQUE KEY : 고유키 정의

3) NOT NULL : NULL 값 입력금지

4) CHECK : 입력 값 범위 제한 (NULL은 무시되어 입력가능)

5) FOREIGN KEY : 외래키 정의

- NULL 값 가능

- 한 테이블에 여러개 존재 가능

6) DEFAULT : 기본값

- 데이터 입력시 값을 지정하지 않는 경우 NULL값 대신 DEFAULT 값이 입력됨

- NOT NULL이 아닐 경우 중간에 DEFAULT로 바꾼다고 이전 NULL 값들이 변경되지는 않음

 

[참고]

NULL : 아직 정의되지 않은 미지의 값

 

● 테이블 구조 변경 (ALTER TABLE)

1) ADD COLUMN 컬럼 추가

ALTER TABLE 테이블명 ADD 컬럼명 데이터유형;

2) DROP COLUMN 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

3) MODIFY COLUMN 컬럼 수정

Oracle : ALTER TABLE 테이블명 MODIFY (컬럼명 데이터유형 [DEFAULT식] [NOT NULL], 컬럼명2 ...);

SQL Server : ALTER TABLE 테이블명 ALTER (컬럼명 데이터유형 [DEFAULT식] [NOT NULL], 컬럼명2 ...);

4) RENAME 컬럼명 변경

Oracle: RENAME COLUMN 변경전칼럼명 TO 뉴칼럼명;

SQLServer : SP_RENAME 변경전칼럼명, 뉴칼럼명, ‘COLUMN’;

5) RENAME 테이블명 변경

Oracle: RENAME 변경전테이블명 TO 뉴테이블명;

SQL Server : SP_RENAME 변경전테이블명, 뉴테이블명;

5) ADD CONSTRAINT 제약조건 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 조건명 조건 (칼럼명);

6) DROP CONSTRAINT 제약조건 삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 조건명;

7) DROP 테이블 삭제

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

8) TRUNCATE 테이블행 삭제 및 저장 공간 재사용

TRUNCATE 테이블명

[참고]

DELETE는 테이블행 삭제, TRUNCATE는 테이블을 초기상태로 돌려놓음 (속도 빠름)

 

 

 

3절. DML

● DML

DDL 명령어 실행시 AUTO COMMIT

DML 명령어는 실행 후 COMMIT 입력 필요

SQL Server의 경우 DDL, DML 모두 AUTO COMMIT

 

1) INSERT 데이터 입력

INSERT INTO STUDENT (NAME) VALUES (‘GGONG’);

2) UPDATE 데이터 수정

UPDATE STUDENT SET NAME = 'GGONG';

3) DELETE 데이터 삭제

DELETE FROM STUDENT;

4) SELECT 데이터 조회

SELECT NAME FROM STUDENT;

 

● 합성 연산자

문자와 문자 연결

Oracle : 문자 || 문자 (SELECT 'GGMOUSE' || ' GOOD')

SQL Server : 문자 + 문자 (SELECT 'GGMOUSE' + ' GOOD')

 

 

 

4절. TCL

● 트랜잭션

밀접히 관련되어 분리될 수 없는 1개 이상의 DB 조작

 

● 트랜잭션의 특성

1) 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않아야 함

2) 일관성 : 트랜잭션 실행 전 DB 내용이 잘못 되지 않으면 실행 후도 잘못 되지 않아야 함

3) 고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 된다.

4) 지속성 : 트랜잭션이 성공적으로 수행되면 DB의 내용은 영구적으로 저장된다.

 

● COMMIT / ROLLBACK / SAVEPOINT

1) COMMIT : 올바르게 반영된 데이터를 DB에 반영

2) ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌림

3) SAVEPOINT : 저장 지점

[참고]

COMMIT과 ROLLBACK의 장점

1) 데이터 무결성 보장

2) 영구적인 변경 전 데이터 변경사항 확인 가능

3) 논리적으로 연관된 작업을 그룹핑하여 처리 가능

 

 

 

5절. WHERE 조건절

● 연산자 우선순위

() -> NOT -> 비교연산자 -> AND -> OR

 

● SQL 연산자의 종류

1) BETWEEN a AND b : a와 b 값 사이에 있으면 됨

2) IN (list) : 리스트에 있는 값중 어느 하나라도 일치

3) LIKE ‘비교문자열’ : 비교문자열과 형태가 일치

4) IS NULL : NULL 값인 경우

5) IS NOT NULL : NULL 값을 갖지 않는 경우

[참고]

= NULL, <> NULL 안됨 (오류는 발생하지 않으나 정상적인 결과 X -> IS NULL, IS NOT NULL 이용)

 

● 와일드카드

1) % : 0개 이상의 어떤 문자 (모든)

2) _ : 1개인 단일 문자

ex.

‘G로 시작’ : LIKE 'G%'

‘두 번째 글자가 G로 시작’ : LIKE '_G%'

‘G가 포함’ : LIKE '%G%'

 

● 논리 연산자

1) AND : 앞 뒤 조건 모두 참(TRUE)일 때 결과가 참(TRUE)

2) OR : 앞 뒤 조건 중 하나 이상 참일 때 결과 참

3) NOT : 뒤에 오는 조건에 반대되는 결과

 

ex.

WHERE TEAM_ID = ‘K2’; -> 팀ID가 K2인 선수

WHERE TEAM_ID IN (‘K2’,‘K7’); -> K2,K7인 선수

WHERE HEIGHT BETWEEN 170 AND 180; -> 키가 170~180인 선수

WHERE POSITION IS NULL; -> 포지션 없는 선수

 

● ROWNUM / TOP

원하는 만큼의 행을 가져오고 싶을 때

Oracle : ROWNUM

SQL Server : TOP [WITH TIES]

WITH TIES : ORDER BY절이 지정된 경우에만 사용할 수 있으며, 결과행과 같은 값이 있는 경우 같이 출력

 

ex. 1개 행 가져오기

WHERE ROWNUM =1;

SELECT TOP(1) PLAYER_NAME FROM PLAYER;

SELECT TOP(1) WITH TIES PLAYER_NAME FROM PLAYER; -> 동일 값 있을 경우 1개 이상 행 반환 (동일 값의 행 수)

 

 

 

6절. 함수 (FUNCTION)

● 문자형 함수 (문제형식 => 인수 채우기 or 결과)

1) LOWER : 문자열을 소문자로 변경

2) UPPER : 문자열을 대문자로 변경

3) ASCII : 문자의 ASCII 값 반환

4) CHR/CHAR : ASCII 값에 해당하는 문자 반환

5) CONCAT : 문자열1, 2를 연결

6) SUBSTR/SUBSTRING : 문자열 m위치에서 n개 문자 반환

7) LENGTH/LEN : 문자열 길이를 숫자 값으로 반환

8) TRIM : 문자열에서 양쪽 지정 문자 제거

10) LPAD : 문자열 좌측에 자리수만큼 문자를 채움

 

ex.

CONCAT(‘RDBMS’,‘ SQL’) -> ‘RDBMS SQL’

SUBSTR(‘SQL Expert’,5,3) -> ‘Exp’

LTRIM(‘xxxYYZZxYZ’,‘x’) -> ‘YYZZxYZ’

RTRIM(‘XXYYzzXYzz’,‘z’) -> ‘XXYYzzXY’

TRIM(‘x’ FROM ‘xxYYZZxYZxx’) -> ‘YYZZxYZ’

LPAD(‘ABCD’, 7 ‘*’) -> ‘***ABCD’

 

● 숫자형 함수 (문제형식 => 인수 채우기 or 결과)

1) ABS(숫자) : tntw의 절대값

2) SIGN(숫자) : 숫자가 양수면1, 음수면-1, 0이면 0 반환

3) MOD(숫자1,숫자2) : 숫자1을 숫자2로 나누어 나머지 반환

4) CEIL/CEILING(숫자) : 크거나 같은 최소 정수 반환

5) FLOOR(숫자) : 작거나 같은 최대 정수 리턴

6) ROUND(숫자 [,m]) : 소수점 m자리에서 반올림

7) TRUNC(숫자 [,m]) : 소수점 m자리에서 자름

 

ex.

MOD(7,3) -> 1

CEIL(38.123) -> 39

CELILING(-38.123) -> -38

ROUND(38.5235,3) -> 38.524

ROUND(38.5235,1) -> 38.5

ROUND(38.5235) -> 39

TRUNC(38.5235,3) -> 38.523

TRUNC(38.5235,1) -> 38.5

TRUNC(38.5235) -> 38

 

● 날짜형 함수

1) SYSDATE/GETDATE() : 현재날짜와 시각 출력

2) EXTRACT/DATEPART : 날짜에서 데이터 출력

3) TO_NUMBER(TO_CHAR(d,‘YYYY’))/YEAR(d) : 날짜에서 년/월/일 출력

 

[참고] (문제형식 => 시간계산)

1 -> 1일

1/24 -> 1시간

1/24/60 -> 1분

1/24/60/60 -> 1초

 

 

● CASE

SELECT ENAME,

         (CASE WHEN SAL >=3000 THEN ‘HIGH’

                 WHEN SAL >=1000 THEN ‘MID’

                 ELSE ‘LOW’

         END) AS SALARY_GRADE

FROM EMP;

 

[참고]

CASE WHEN SAL = 1000 THEN ‘GOOD’

CASE SAL WHEN 1000 THEN ‘GOOD’

두 문장은 같은 의미

 

● NULL 관련 함수

- NULL은 아직 정의되지 않은 값 (0 또는 공백과는 다르다)

- NULL이 포함된 연산의 결과는 NULL

ex. NULL+2 -> NULL, NULL*2 -> NULL, NULL/2 -> NULL

 

[참고]

NULL과의 연산은 NULL이지만, 집계함수의 통계 정보는 NULL값을 가진 행을 제외하고 수행

ex.

COL1	COL2
100	200
NULL	300

SUM(COL1) -> 100

SUM(COL1 + COL2) -> 300

COL1 + COL2 -> 300 / NULL

 

 

1) NVL(표현식1,표현식2) / ISNULL(표현식1,표현식2)

: 식1의 값이 NULL 이면 식2 출력

2) NULLIF(식1,식2)

식1이 식2와 같으면 NULL을 아니면 식1을 출력

3) COALESCE(식1,식2)

: 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식, 모두 NULL이면 NULL 반환

ex. COALESCE(NULL,NULL,‘abc’) -> ‘abc’

COALESCE(100,60*50,50) -> 100,3000,50

 

 

 

7절. GROUP BY, HAVING 절

● 집계 함수

1) 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수

2) GROUP BY 절은 행들을 소그룹화

3) SELECT, HAVING, ORDER BY 절에 사용 가능

- ALL : Default (생략가능)

- DISTINCT : 같은 값을 하나의 데이터로 간주 (중복제거)

 

● 집계 함수 종류

1) COUNT(*) : NULL 포함 행의 수

2) COUNT(표현식) : NULL 제외 행의 수

3) SUM, AVG : NULL 제외 합계, 평균 연산

4) STDDEV : 표준 편차

5) VARIAN : 분산

6) MAX, MIN : 최대값, 최소값

 

● GROUP BY, HAVING 절의 특징

1) GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용

2) 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행

3) GROUP BY 절에서는 ALIAS 사용 불가

4) 집계 함수는 WHERE 절에 사용 불가

5) HAVING 절에는 집계함수를 이용하여 조건 표시

6) HAVING 절은 일반적으로 GROUP BY 뒤에 위치

 

 

 

8절. ORDER BY절

● ORDER BY 특징

1) SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정한 칼럼을 기준으로 정렬하여 출력하는데 사용

2) ORDER BY 절에 칼럼명 대신 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능

3) DEFAULT 값으로 오름차순(ASC)이 적용되며, 내림차순(DESC)옵션을 통해 정렬 가능

4) SQL 문장의 제일 마지막에 위치

5) SELECT 절에서 정의하지 않은 칼럼 사용 가능

 

[참고]

Oracle에서는 NULL을 가장 큰 값으로 취급

SQL Server에서는 NULL을 가장 작은 값으로 취급

 

● SELECT 문장 실행 순서

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

 

 

9절. 조인 (JOIN)

● 조인 (JOIN)

두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것

일반적인 경우 행들은 PK나 FK 값의 연관에 의해 JOIN이 성립된다. 하지만, 어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립가능하다.

 

[참고]

N개의 테이블을 JOIN 하기 위해서는 최소 N-1번의 JOIN 과정이 필요

 

● EQUI JOIN

2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용 (대부분 PK, FK의 관계를 기반)

 

ex.

SELECT PLAYER.PLAYER_NAME

FROM PLAYER

 

● NON EQUI JOIN

2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용 (‘=’ 연산자가 아닌 BETWEEN, >, <= 등 연산자 사용)

 

ex.

SELECT E.ENAME, E.JOB, E.SAL, S.GRADE

FROM EMP E, SALGRADE S

WHERE E.SAL BETWEEN S.LOSAL AND S.HSAL;

 

 

 

 

 

바르지 못한 내용 있으면 댓글 달아주세요 :)