[SQL] SQL 기본

데이터 베이스

Database란?

넓은 의미에서의 데이터베이스는 일상적인 정보들을 모아 놓은 것 자체를 의미합니다. 하지만 여기서 일반적으로 데이터베이스라고 말할 때는 특정 기업이나 조직 또는 개인이 필요에 의해(e x: 부가가치가 발생하는) 데이터를 일정한 형태로 저장해 놓은 것을 의미한다.

DBMS

많은 사용자들은 보다 효율적인 데이터의 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 소프트웨어를 필요로 하게 되었고 이러한 기본적인 요구사항을 만족시켜주는 시스템입니다.

SQL(Structured Query Language)

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

테이블

DB 기본 단위, 데이터를 저장하는 객체로써 관계형 데이터베이스의 기본 단위입니다.

---

DDL

데이터 유형

• CHAR(s) : 고정 길이 문자열 정보. 최대 길이 만큼 공간 채움 ‘AA’ =‘AA’
• VARCHAR2(s) : 가변 길이 문자열 정보. 할당된 변수 값의 바이트만 적용 ‘AA’ != ‘AA ’
• NUMBER : 정수, 실수 등 숫자 정보
• DATE : 날짜와 시각 정보

테이블 명명규칙

• 테이블 명은 다른 테이블의 이름과 중복되면 안 됩니다.
• 테이블 내의 칼럼명은 중복될 수 없습니다.
• 각 칼럼들은 , 로 구분되고 ; 로 끝납니다.
• 칼럼 뒤에 데이터 유형은 꼭 지정되어야 합니다.
• 테이블명과 칼럼명은 반드시 문자로 시작해야 합니다.
• A-Z,a-z,0-9,_,$,#만 사용 가능

제약조건

데이터의 무결성 유지

1. PRIMARY KEY(기본키) : UNIQUE & NOT NULL
2. UNIQUE KEY(고유키) : 고유키 정의 NULL가능
3. NOT NULL : NULL 값 입력금지
4. CHECK : 입력 값 범위 제한
5. FOREIGN KEY(외래키) : NULL 가능, 여러 속성 가능

테이블 작업

테이블 생성

CREATE TABLE PLAYER (
PLAYER_ID CHAER(7) NOT NULL,
PLAYER_NAME VARCHAR2(20) NOT NULL);

테이블 구조 변경

- 추가 : ALTER TABLE PLAYER ADD(ADDRESS VARCHAR2(80));

- 삭제 : ALTER TABLE PLAYER DROP COLUMN ADDRESS;

- 수정 : ALTER TABLE TEAM_TEMP MODIFY (
ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

- 이름 변경: ALTER TABLE table_name1 RENAME table_name2;

제약조건

제약조건 삭제 : DROP CONSTRAINT 조건명;
제약조건 추가 : ADD CONSTRAINT 조건명 조건 (칼럼명);
테이블명 변경 : RENAME PLAYER TO PLAYER_BACKUP;
테이블 삭제 : DROP TABLE PLAYER;
테이블 데이터 삭제 : TRUNCATE TABLE PLAYER;
컬럼명 변경 : RENAME COLUMN TEAM_ID TO T_ID;

---

DML

DDL 명령어의 경우 실행시 AUTO COMMIT 하지만 DML의 경우 COMMIT을 입력해야 합니다.(oracle의 경우만 해당 sql server는 auto commit을 지원하지 않습니다.)

데이터 조작

INSERT INTO PLAYER (PLAYER) VALUES (‘PJS’);
UPDATE PLAYER SET BACK_NO = 60;
DELETE FROM PLAYER;
SELECT PLAYER_ID FROM PLAYER;
SELECT PLAYER AS “선수명” FROM PLAYER;

DISTINCT : 중복 시 1회만 출력 (기본 ALL)

와일드 카드

• * : 모든
• %: 모든
• - : 한 글자

산술연산자

• NULL과의 연산은 NULL이 반환

합성 연산자

• 문자와 문자 연결 : ||

---

TCL

트랜잭션

밀접히 관련되어 분리될 수 없는 1개 이상의 DB 조작. 논리적 연산단위

트랜젝션 관리

• COMMIT : 올바르게 반영된 데이터를 DB에 반영
• ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌림 COMMIT 되지 않은 모든 트랜잭션을 롤백함.
• SAVEPOINT : 저장 지점
• SAVE TRANSACTION (SQL SERVER)

다른 사용자 접근 유무

• COMMIT 이전:
  • 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.
  • 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
  • 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경할 수 없다.
• COMMIT 이후:
  • 데이터에 대한 변경 사항이 데이터베이스에 반영된다.
  • 이전 데이터는 영원히 잃어버리게 된다.
  • 모든 사용자는 결과를 볼 수 있다.
  • 관련된 행에 대한 잠 금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

예시

COMMIT;
SAVEPOINT SVPT1;
ROLLBACK TO SVPT1;
COMMIT;

---

where 절

사용자들은 자신이 원하는 자료만을 검색하기 위해서 SQL 문장에 WHERE 절을 이용하여 자료들에 대하여 제한할 수 있습니다.
WHERE 절에 조건이 없는 FTS(Full Table Scan) 문장은 SQL 튜닝의 1차적인 검토 대상이 됩니다. (FTS가 무조건 나쁜 것은 아니며 병렬 처리 등을 이용해 유용하게 사용하는 경우도 많긴 합니다.)

연산자의 종류

• 비교 연산자 (부정 비교 연산자 포함)
• SQL 연산자 (부정 SQL 연산자 포함)
• 논리 연산자

연산자 우선순위

• 괄호로 묶은 연산이 제일 먼저 연산 처리됩니다.
• 연산자들 중에는 부정 연산자(NOT)가 먼저 처리되고,
• 비교 연산자(=,>,>=,<,<=), SQL 비교 연산자(BETWE EN a AND b, IN (list), LIKE, IS NULL)가 처리되고,
• 논리 연산자 중에서는 AND, OR의 순으로 처리됩니다.

문자 유형 비교방법

NULL과의 연산

• NULL 값과의 수치연산은 NULL 값을 리턴한다.
• NULL 값과의 비교연산은 거짓(FALSE)를 리턴한다.

값의 페이징

• ROWNUM (oracle): 원하는 만큼의 행을 가져올 때 사용
  예시)

  • SELECT * PLAYER_NAME FROM PLAYER WHERE ROWNUM = 1;
  • SELECT * PLAYER_NAME FROM PLAYER WHERE ROWNUM < 2;

• TOP (SQL Server): 결과 집합으로 출력되는 행의 수를 제한
  예시)

  • SELECT TOP(1) PLAYER_NAME FROM PLAYER;
    - SELECT TOP(2) WITH TIES ENAME, SAL FROM EMP ORDER BY SAL DESC;
    • 급여가 높은 2명을 내림차순으로 출력하는데, 같은 급 여를 받는 사원은 같이 출력한다.

---

함수

단일행 함수

• SELECT, WHERE, ORDER BY 절에서 사용 가능
• 행에 개별적 조작
• 여러 인자가 있어도 결과는 1개만 출력
• 함수 인자에 상수, 변수, 표현식 사용 가능
• 함수 중첩 가능

단일행 함수 종류

문자형 함수

• LOWER : 문자열을 소문자로
• UPPER : 문자열을 대문자로
• ASCII : 문자의 ASCII 값 반환
• CHR : ASCII 값에 해당하는 문자 반환
• CONCAT : 문자열1, 2를 연결
• SUBSTR : 문자열 중 m위치에서 n개의 문자 반환
• LENGTH : 문자열 길이를 숫자 값으로 반환
  .
• CONCAT(‘RDBMS’,‘ SQL’) -> ‘RDBMS SQL’
• SUBSTR(‘SQL Expert’,5,3) -> ‘Exp’
• LTRIM(‘xxxYYZZxYZ’,‘x’) -> ‘YYZZxYZ’
• TRIM(‘x’ FROM ‘xxYYZZxYZxx’) -> ‘YYZZxYZ’

숫자형 함수

• SIGN(숫자) : 숫자가 양수면1 음수면-1 0이면 0 반환
• MOD(숫자1, 숫자2) : 숫자1을 숫자2로 나누어 나머지 반환
• CEIL/CEILING(숫자) : 크거나 같은 최소 정수 반환
• FLOOR(숫자) : 작거나 같은 최대 정수 리턴
• ROUND: 숫자를 소수 m자리에서 반올림하는 함수
  (38.5235,3) -> 38.524
• TRUNC: 숫자를 소수 m자리에서 잘라서 버림
  (38.5235,3) -> 38.523
• SIN, COS, TAN: 삼각함수 값

날짜형 함수

• SYSDATE/GETDATE : 현재날짜와 시각 출력
• EXTRACT('YEAR'|'MONTH'|'DAY' from d) : 날짜에서 데이터 출력
• TO_NUMBER(TO_CHAR(d,‘YYYY’)) : 연도를 숫자로 출력

날짜 계산

• 1 = 하루, 1/24 = 1시간, 1/24/60 = 1분

CASE 표현

• SEARCHED_CASE_EXPRESSION: CASE WHEN LOC = ‘a’ THEN ‘b’
• SIMPLE_CASE_EXPRESSION: CASE LOC WHEN ‘a’ THEN ‘b’
• DECODE(LOC, 'a', 'b')
  ELSE NULL이 생략되어 있음.

NULL 관련 함수

• 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다.
  0은 숫자이고, 공백은 하나의 문자이다.

• NVL(식1,식2) : 식1의 값이 NULL 이면 식2 출력. 공집합을 바꿔주진 않음

• NULLIF(식1,식2) : 식1이 식2와 같으면 NULL을 아니면 식1을 출력

• COALESCE(식1,식2) : NULL이 아닌 최초의 표현 식, 모두 NULL이면 NULL 반환 e.g. COALESCE(NULL, NULL, ‘abc’) -> ‘abc’

• 인수의 값이 공집합인 경우는 NVL/ISNULL 함수를 사용해도 역시 공집합이 출력

---

GROUP BY , HAVING 절

다중행 집계 함수

• 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수입니다.
• GROUP BY 절은 행들을 소그룹화 합니다.
• SELECT, HAVING, ORDER BY 절에 사용 가능합니다.
• ALL : Default 옵션. 생략 가능
• DISTINCT : 같은 값을 하나의 데이터로 간주 옵션

다중행 집계 함수 종류

Group by절의 특성

• GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용합니다.
• 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행합니다.
• GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없습니다.
• 집계 함수는 WHERE 절에는 올 수 없습니다. (집계 함수를 사용할 수 있는 GROUP BY 절 보다 WHERE 절이 먼저 수행된다)
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킵니다.
• HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있습니다.
• GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력합니다.
• HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치합니다.
• ORDER BY 절을 명시해야 데이터 정렬을 수행합니다.

---

ORDER BY 절

ORDER BY 특징

• SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정한 칼럼을 기준으로 정렬하여 출력하는데 사용한다.
• ORDER BY 절에 칼럼명 대신 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능하다.
• DEFAULT 값으로 오름차순(ASC)이 적용되며 DESC 옵션을 통해 내림차순으로 정렬이 가능하다.
• SQL 문장의 제일 마지막에 위치한다.
• SELECT 절에서 정의하지 않은 칼럼 사용 가능
• Oracle이 NULL 값을 가장 큰 값으로 취급하고,SQL Server는 반대의 정렬 순서를 가진다.
• ORDER BY 절에서 칼럼명, ALIAS명, 칼럼 순서를 같이 혼용하는 것도 가능

SELECT 문장 실행 순서

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

1. 발췌 대상 테이블을 참조한다. (FROM)
2. 발췌 대상 데이터가 아닌 것은 제거한다.(WHERE)
3. 행들을 소그룹화 한다. (GROUP BY)
4. 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING)
5. 데이터 값을 출력/계산한다. (SELECT)
6. 데이터를 정렬한다. (ORDER BY)

• ORDER BY 절에는 SELECT 목록에 나타나지 않은 문자형 항목이 포함될 수 있지만, SELECT DISTINCT를 지정하거나 SQL 문장에 GROUP BY 절이 있거나 또는 SELECT 문에 UNION 연산자가 있으면 열 정의가 SELECT 목록에 표시되어야 한다.

• 관계형 데이터베이스가 데이터를 메모리에 올릴 때 행 단위로 모든 칼럼을 가져오게 되므로, SELECT 절에서 일부 칼럼만 선택하더라도 ORDER BY 절에서 메모리에 올라와 있는 다른 칼럼의 데이터를 사용할 수 있다.

• SELECT 절에 정의하지 않은 MAX, SUM, COUNT 집계 함수도 ORDER BY 절에서 사용할 수 있다.

---

Join 기능

JOIN이란?

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

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

• FROM 절에 여러 테이블이 나열되더라도 SQL에서 데이터를 처리할 때는 단 두 개의 집합 간에만 조인할 수 있습니다.

• 5가지 테이블을 JOIN 하기 위해서는 최소 4번의 JOIN 과정이 필요합니다. (N-1)

• 테이블에 대한 ALIAS를 적용해서 SQL 문장을 작성했을 경우, WHERE 절과 SELECT 절에는 테이블명이 아닌 테이블에 대한 ALIAS를 사용해야 합니다.

EQUI JOIN

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

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... 
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2;

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...
FROM 테이블1 INNER JOIN 테이블2
ON 테이블1.칼럼명1 = 테이블2.칼럼명2;

• 두 개의 테이블에 같은 칼럼명이 존재하는 경우에는 DBMS의 옵티마이저는 어떤 칼럼을 사용해야 할지 모르기 때문에 파싱 단계에서 에러가 발생되기 때문에,
• 위 SQL처럼 칼럼이 어느 테이블에 존재하는 칼럼인지를 명시해줘야 합니다.

NON EQUI JOIN

2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용합니다.

• ‘=’ 연산자가 아닌 BETWEEN, >, <= 등 연산자 사용

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼명1
BETWEEN 테이블2.칼럼명1 AND 테이블2.칼럼명2;

---

출처: SQL 전문가 가이드 서적