SQL) SQL 기초

---

1. 관계형 데이터베이스

1. 데이터베이스

• 데이터를 일정한 형태로 저장해놓은 것
• 종류
  • 계층형 : 트리 자료구조 사용, 1:N 관계 표현
  • 네트워크형 : 오너와 멤버 형태, M:N
  • 관계형 : 릴레이션(테이블)에 데이터 저장, 집합연산, 관계 연산 가능

2. 관계형 데이터베이스

• 정규화를 통해 이상 현상 및 중복 데이터 제거
• 동시성 관리와 병행 제어를 통해 데이터 동시 조작 가능
• 집합 연산 합집합(union), 차집합(difference), 교집합(intersection), 곱집합(Cartesian Product)
• 관계 연산
  • 선택 (Selection) : 조건에 맞는 행(튜플|가로) 조회
  • 투영 (Projection) : 조건에 맞는 속성(컬럼|세로) 조회
  • 결합 (Join) : 공통 속성(컬럼)을 사용해서 새로운 릴레이션(테이블) 생성
  • 나누기 (Division) : 공통 요소를 추출하고 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거

3. SQL 개념

• Structured Query Language
• 데이터 입력, 수정, 삭제, 조회 기능을 위해 RDB에서 사용하는 언어
• 종류
  • DML (Data Manipulation Language) : 데이터 조작어 SELECT, INSERT, UPDATE, DELETE
  • DDL (Data Definition Language) : 데이터 정의어 CREATE, ALTER, DROP
  • DCL (Data Control Language) : 데이터 제어어 데이터베이스 접근 권한 부여 및 회수 GRANT, REVOKE
  • TCL (Transaction Control Language) : 트랜잭션 제어어 데이터 조작어로 조작한 결과를 논리적 작업 단위 별로 제어 COMMIT, ROLLBACK
• 테이블 RDB의 기본 단위, 데이터를 저장하는 객체, 칼럼과 행의 2차원 구조

2. DDL (Data Definition Language) 데이터 정의어

1. 데이터 타입 ORACLE(SQL SERVER)

• CHAR (L) : 고정 길이 문자열, 데이터 넣고 남은 공간은 공백으로 채움
• VARCHAR(L) : 가변 길이 문자열,
• NUMBER(L,D) : 숫자형 (D는 소수점 자리수)
• DATE, DATETIME : 날짜형 (데이터 크기 지정 x)

2. CREATE TABLE

• 테이블명과 컬럼명 명명 규칙
  1. 알파벳, 숫자, _, $, # 만 사용가능
  2. 대소문자 구별 X
  3. 단수형 권고
• 데이터 무결성 유지를 위해 제약 조건을 사용함
  • 복제 테이블은 원본 테이블의 제약조건중 NOT NULL 만 적용됨
  • PRIMARY KEY : 테이블 당 하나의 기본키 존재, 기본키 생성시 DBMS가 자동으로 인덱스 생성, NULL X
  • FOREIGN KEY : 참조하는 다른 테이블의 PK
  • ALTER TABLE 테이블명 ADD CONSTARINT 컬럼명 FOREIGN KEY 컬럼명 REFERENCES 테이블명(컬럼명);
  • UNIQUE KEY : 행(튜플|가로) 식별가능한 컬럼
  • DEFAULT : 디폴트 값 설정
  • NOT NULL
  • CHECK : 입력값의 종류 및 범위 제한
  • DESCRIBE 테이블명 : 테이블 구조 확인

3. ALTER TABLE

• 테이블 컬럼(속성|세로) 수정 명령어
• 컬럼 추가
  • ALTER TABLE 테이블명 ADD (칼럼명 데이터타입);
  • 마지막 컬럼에 추가됨 (위치 지정 불가)
• 컬럼 삭제
  • ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
  • 삭제 후 복구 불가
• 컬럼 설정 변경
  • ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입 제약조건);
  • null 값만 있거나, 튜플이 없는 경우에만 컬럼 크기 변경 가능
  • null 값만 있는 경우 데이터 타입 변경 가능
  • null 값이 없는 속성의 경우 not null 제약사항 추가 가능
  • default 설정해도 이전에 추가된 데이터에는 반영안되고 default 설정 이후에 들어온 데이터에만 기본값이 설정됨
• 컬럼명 변경
  • ALTER TABLE 테이블명 RENAME COLUMN 컬럼명;
• 제약조건 추가
  • ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건;
• 제약조건 삭제
  • ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건;

4. RENAME TABLE

• RENAME 테이블명 TO 새테이블명;
• == ALTER TABLE RENAME TO 테이블명;

5. DROP TABLE

• DROP TABLE 테이블명;
• 테이블 데이트, 구조 삭제 및 복구 불가
• CASECADE CONSTRAINT : 삭제하는 테이블의 제약조건도 모두 삭제 → 참조 무결성 준수

6. TRUNCATE TABLE

• TRUNCATE TABLE 테이블명;
• 테이블 내 데이터 모두 삭제, 구조는 그대로 둠
• ROLLBACK 불가
• 테이블 용량 초기화

3. DML (Data Manipulation Language) 데이터 조작어

1. INSERT

• INSERT INTO 테이블명 (컬럼명,....) VALUES (데이터, ...);
• INSERT INTO 테이블명 VALUES (데이터, .. 모든 컬럼의 데이터, 순서대로);

2. UPDATE

• UPDATE 테이블명 SET 컬렴명 = 데이터;

3. DELETE

• DELETE FROM 테이블명 [WHERE 조건절];
  • 테이블 용량 초기화 X

4. SELECT

• SELECT [DINSTINCT] 컬럼명 [AS 별명] FROM 테이블명 [별명]
  • distinct : 데이터 중복 X
• 문자열 합성 연산자
  •  
  • CONCAT
  • || (oracle)
• DUAL : 오라클의 기본 더미 테이블, 연산 수행을 위해 사용

4. TCL (Transaction Control Language) 트랜잭션 제어어

1. 트랜잭션

• 데이터베이스의 논리적 연산 단위
• 하나 이상의 sql 문을 포함

2. ACID

• Atomicity 원자성 : 전부 실행되거나 전혀 실행되지 않음. 모든 작업 중 하나라도 잘못되면 모두 롤백(작업 실행 이전 상태로 돌아감)
• Consistency 일관성 : 트랜잭션으로 인한 DB 상태의 모순이 없음, 즉 데이터가 미리 정의된 규칙(예를 들어 데이터타입)을 유지함
• Isolation 고립성 : Locking으로 보장, 서로 다른 트랜잭션의 결과에 서로 접근 불가
• Durability 영속성 : 한번 커밋된 트랜잭션 결과가 영구적으로 저장됨

3. TCL

• 데이터 무결성 보장을 목적으로 함
• 영구 변경 전 확인
• 연관 작업 동시 처리 가능하게 함
• DDL을 실행하면 자동 커밋
• DB를 정상적으로 종료하면 자동 커밋
• 비정상적으로 종료되면 자동 롤백

4. COMMIT

• 데이터를 DB에 영구적으로 반영하는 명령어
• commit → 트랜잭션 종료 + locking 해제
• commit 전
  • 복구 가능
  • 현재 사용자는 select로 결과 조회 가능, 다른 사용자는 조회 불가능
  • 조작된 행(튜플)에 locking 설정하여 다른 사용자는 변경 불가
• commit 후
  • 변경사항이 DB에 반영되어 이전으로 복구 불가
  • 모든 사용자가 결과 볼 수 있음
  • locking이 없어 조작한 행에 다른 사용자들이 접근 가능

5. ROLLBACK

• 트랜잭션 이전 상태로 되돌리는 명령어
• locking 해제 하면서 commit 이전 상태로 돌아감
• savepoint : 트랜잭션 일부만 롤백할 수 있는 중간 지점
  • rollback to savepointname
  • 중복 savepoint name 있는 경우 뒤에 나온 savepoint가 유효함

5. WHERE

1. 연산자

• 비교연산자 <, = ...
• 부정 비교 연산자 NOT, !=, ^=, <>
• SQL 연산자
  • [NOT] BETWEEN A AND B
  • [NOT] IN (리스트) : 리스트 내 값
  • LIKE ‘문자열’ : 문자열 형태와 일치하는 값
    • % : 0개 이상의 문자를 의미
    • _ : 1개의 단일 문자를 의미
  • [NOT] IS NULL : null은 비교 연산자로 절대! 비교 불가
• 논리연산자 and, or, not
• 우선순위 괄호 > 부정 > 비교 > 논리 (AND > OR)

2. 문자열 비교 방법

• char vs char : 같은 위치의 문자 비교해서 알파벳 순서가 뒤인 경우가 큰 값, 같으면 그 다음 위치 문자 비교, 길이가 다른 경우 짧은 문자열 뒤에 공백을 추가해서 길이를 맞춤, 공백 수만 다르면 같은 값이라고 판단
• char vs varchar : 위와 같은 방식으로 비교, 길이가 다르면 길이가 긴 값이 크다고 판단, varchar에서는 공백도 문자로 판단, TRIM 함수로 공백을 제거하고 판단할 수 있음
• char vs 상수 : 상수를 변수 타입으로 바꿔서 비교

3. 부분 범위 처리

• 오라클 : ROWNUM
  • SQL 처리 결과 집합의 각 행에 임시로 부여되는 번호
  • 조건절 내에서 행의 개수를 제한하는 목적으로 사용
• SQL Server : TOP
  • 출력 행의 수 제한을 위해 사용
  • TOP (n) : n개의 행출력, 개수 대신 비율로 제한 가능

6. 함수

1. 단일행함수

• select, where, order by 절에 사용 가능
• 각 행에 개별적으로 작용
• 여러 인자를 입력해도 하나의 결과만 출력
• 문자형 함수
  • LOWER : 소문자로 출력
  • UPPER : 대문자로 출력
  • LENGTH : 길이 출력
  • CONCAT : 문자열 결합
  • SUBSTR : 문자열 부분 출력
  • LTRIM : 왼쪽 공백 제거
  • RTRIM : 오른쪽 공백 제거
  • TRIM : 양쪽 공백 제거
  • ASCII : 아스키 코드 값 출력
• 숫자형 함수
  • ABS : 절대값 출력 ↔ SIGN : 1,0,-1 중 하나 출력
  • MOD : 나머지 (%으로 대체 가능)
  • ROUND : 반올림
  • CEIL : 올림
  • FLOOR : 버림
  • TRUNC : 숫자형 부분 추출
• 날짜형 함수
  • SYSDATE : 현재 시간 출력 (년, 월, 일, 시, 분, 초)
    • mysql → select sysdate();
  • EXTRACT : 날짜형 부분 출력
    • select extract(unit from sysdate) from dual;
  • +-숫자 : 하루 더하고 뺴기
  • +-숫자/24 : 시간 연산
  • NEXT_DAY : 지정된 요일의 첫번째 날짜 출력
• 변환형 함수 : 데이터 타입 변환, 명시적 형 변환
  • TO_NUMBER : char to num
  • TO_CHAR : num to char
  • TO_DATE : char to date
  • CAST, CONVERT
• NULL 관련 함수
  • NVL(칼럼,값) : NULL값 반환
  • NVL2(칼럼, null true일때 출력되는 값, null false일때 출력는 값)
  • NULLIF(값,값) : 같으면 null, 다르면 첫번째 인자 출력
  • COALESCE(값, 값, .. ): 왼→오 순서대로 탐색하다 NULL이 아닌 첫번째 값을 출력
  • ISNULL(컬럼, 값) : NULL이면 값으로 대치, NULL이 아니면 칼럼값을 그대로 출력

2. 데이터 변환

• 명시적 형 변환 : 변환형 함수를 이용해서 데이터 타입 변환
• 암시적 형 변환 : DBMS가 자동으로 변환

3. 조건문

• IF-THEN-ELSE
• CASE WHEN 조건절1 THEN 출력값1 .. ELSE 기본값 END
  • 조건절1 일때 출력값1을 출력
  • 조건절1이 아닐때 기본값 출력
  • else 구문 없는 경우 NULL 출력
  • ex) CASE WHEN 컬럼 IS NULL THEN 출력값
• DECODE(칼럼, 기준값1, 출력값1, ... , 기본값)
  • 오라클 함수, 표준 X
  • 칼럼이 기준값 1이면 출력값1 출력
  • 기준값과 출력값이 페어링되어 있음
  • 마지막값은 기준값들 중 어떤 것도 아닌 경우 마지막 기본값으로 출력
  • ex) DECODE(gender, ‘M’, ‘남자’, ‘F’, ‘여자’, ‘기타)

4. 집계 함수 (다중행함수)

• 그룹별 결과 출력
• GROUP BY와 항상 같이 사용해야 한다. 그렇지 않으면 에러 발생
• WHERE 절에 사용 불가
• 공집합에서도 연산 수행
• ALL : 중복돼도 그냥 전부 출력
• DISTINCT : 중복되는 것 제외하고 출력
• NULL 제외하고 연산
  • SUM : 합
  • AVG : 평균
  • MAX : 최대값
  • MIN : 최소값
  • VARIAN : 분산
  • STDDEV : 표준편차
• COUNT : 행 개수 출력
  • COUNT(*) : NULL 인 행도 포함해서 출력
  • COUNT(표현식) : null 제외하고 출력

7. GROUP BY, HAVING, ORDER BY

1. GROUP BY

• 그룹핑 기분 설정
• alias 사용 불가

2. HAVING

• GROUP BY 절에 의한 집계 데이터에 출력 조건을 건다
• select 절에 조건을 거는 where 절과 같은 역할 → 여기서 걸러지면 group by 대상에서도 제외된 것
• group by 뒤에 위치함

3. ORDER BY

• 특정 컬럼을 기준으로 조회 결과를 정렬해서 출력
• 기본은 오름차순
• 오라클은 null을 최대값으로 판단하고, sql server는 최소값으로 판단
• 문장의 제일 마지막에 사용!!
• sql 실행 순서
  • 테이블에 출력 대상이 아닌 것은 where절에서 걸러지고 남은걸 그룹핑하고 그 값이 조건에 맞는 데이터를 계산하고 정렬해서 출력함

    SELECT 칼럼명 AS '별명' 6) 출력 
    FROM 테이블명 1) 테이블에서 
    WHERE 조건식  2) 출력 대상을 조건식으로 거르고 
    GROUP BY 컬럼/표현식 3) 그룹핑해서
    HAVING 조건식 4) 그룹핑된 값들을 조건에 맞게 
    ORDER BY 컬럼/조건식 5) 정렬해서​

8. JOIN

1. 개념

• 조인 : 여러 테이블을 연결 또는 결합하여 데이터를 출력
• 등가조인 : 두 테이블의 컬럼값이 정확히 일치하는 경우 결합
  • ex) SELECT 칼럼 ... FROM 테이블1 A, 테이블2 B WHERE A.칼럼=B.칼럼;
  • select 대상 칼럼이 두 테이블 모두 가지고 있는 경우 반드시 alias 지정 필요!
• 비등가조인 : 두 테이블의 칼럼 값이 정확하게 일치하지 않는 경우 조인
  • 부등호나 BETWEEN 연산자로 조인