[SQLD] SQL 기본

 SQL 기본

• DML (Data Manipulation Language: 데이터 조작어)
  • SELECT

  SELECT (ALL) : 중복된 데이터가 있어도 모두 출력
  SELECT DISTINCT : 중복된 데이터가 있는 경우 중복제거 출력
  
  SELECT FROM GROUP BY 칼럼명1, 칼럼명2 : 중복제거 출력

  데이터베이스 테이블에 들어있는 데이터에 변형을 가하는 종류의 명령어
  • INSERT
  • UPDATE
  • DELETE
  설명 방식에 따른 분류
  • 절차적 데이터 조작어(procedual DML)
    What + How
    사용가자 어떤 데이터를 원하는지 + 어떻게 데이터를 접근해야 하는지 명세
  • 비절차적 데이터 조작어(nonprocedual DML)
    What
    사용자가 어떤 데이터를 원하는지만 명세. How는 DBMS에 맡김
  호스트 프로그램에 속에 삽입되어 사용되는 DML 명령어 -> 데이터 부속어(Data Sub Language)
• 데이터베이스에 들어있는 데이터를 조회하거나 검색하기 위한 명령어

 

• DDL (Data Definition Language: 데이터 정의어)
  테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어
  • CREATE
  • ALTER
  • DROP
  • RENAME

 

• DCL (Data Control Language: 데이터 제어어)
  데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어
  • GRANT
  • REVOKE

 

• TCL (Transaction Control Language: 트랜잭션 제어어)
  논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 트랜잭션 별로 제어하는 명령어
  • COMMIT
  • ROLLBACK

 

• 테이블 칼럼에 대한 정의 변경
  • Oracle
    • 추가

    ALTER TABLE 테이블명 ADD (칼럼명 데이터형식);

    • 이름 수정

     ALTER TABLE 테이블명 RENAME COLUMN 기존칼럼명 TO 새칼럼명;
     // ANSI도 동일

    • 속성 수정

    ALTER TABLE 테이블명
    MODIFY 
    (칼럼명1 데이터형식, [NOT NULL],
    칼럼명2 데이터형식 ...);

    • 칼럼 삭제

     ALTER TABLE 테이블명 DROP COLUMN (삭제할 칼럼명);

  • SQL Server
    • 추가

     ALTER TABLE 테이블명 ADD 칼럼명 데이터형식

    • 이름 수정

     
      EXEC SP_RENAME '테이블명.[변경전 칼럼이름]'.'새로운 칼럼이름'.'COLUMN'
     

    • 속성 수정

     
     ALTER TABLE 테이블명 ALTER 칼럼명 데이터형식 [NOT NULL]
     //여러개의 칼럼 동시 수정 불가
    

    • 칼럼 삭제

     
      ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명

 

• 제약 조건
  • Oracle
    • PK
    • CREATE TABLE 테이블명( 칼럼명 데이터형식 PRIMARY KEY ); OR CREATE TABLE 테이블명( 칼럼명 데이터형식 CONSTRAINT PK이름 PRIMARY KEY(칼럼명) ); //UNIQUE도 이와 유사
    • FK
    • CREATE TABLE 테이블명( 칼럼명 데이터형식, CONSTRAINT FK명 FOREIGN KEY (적용 칼럼명) REFERENCES 참조테이블명 (참조테이블 내 참조할 칼럼명) ON [DELETE/UPDATE] CASCADE ( <-- 선택 사항) );

  • SQL Server
    • PK
    • CREATE TABLE 테이블명 ( 칼럼명 데이터형식 PRIMARY KEY (칼럼명), ... ) ALTER TABLE 테이블명 ADD CONSTRAINT PK명 PRIMARY KEY (칼럼명)
    • FK
    • CREATE TABLE 테이블명( 칼럼명 데이터형식 FOREIGN KEY, REFERENCES 테이블명 (칼럼명) ... ) ALTER TABLE 테이블명 ADD CONSTRAINT FK명 FOREIGN KEY (적용 칼럼명), REFERENCES 참조테이블명 (참조테이블 내 참조할 칼럼명)

• 데이터 수정 및 삭제
  • Oracle
    • 데이터 삽입

    INSERT INTO 테이블명 [칼럼, 칼럼, ...] VALUES (값1, 값2, ...);

    • 데이터 수정

    UPDATE 테이블명
    SET 대상 칼럼명 = '수정할 데이터' 
    WHERE 칼럼명 = 조건;

    • 데이터 삭제

    DROP FROM 테이블명
    WHERE 칼럼명 = 조건;

  • SQL Server
    • 데이터 삽입

     INSERT INTO 테이블명 [칼럼, 칼럼, ...] VALUES (값1, 값2, ...);

    • 데이터 수정

      UPDATE 테이블명
      SET 대상 칼럼명 = '수정할 데이터' 
      WHERE 칼럼명 = 조건;

    • 데이터 삭제

      DELETE
      (FROM) 테이블명
      WHERE 조건;

• 테이블 생성 주의사항
  • 테이블명은 객체를 의미할 수 있는 적절한 이름 + 가능한 단수형
  • 테이블명은 다른 테이블의 이름과 중복 X
  • 한 테이블 내에서는 칼럼명 중복 X
  • 테이블 이름을 지정하고 각 칼럼들을 괄호 () 로 묶어 지정
  • 각 칼럼들은 콤마 , 로 구분
  • 생성문의 끝은 항상 세미콜론 ;
  • 칼럼은 다른 테이블까지 고려하여 DB 내에서는 일관성 있게 사용
    (데이터 표준화 관점)
  • 칼럼 뒤에 데이터 유형은 꼭 지정되어야
  • 테이블명과 칼럼명은 반드시 문자로 시작, 벤더별로 길이에 대한 한계 O
  • 벤더에서 사전에 정의한 예약어는 사용 불가
  • A-Z, a-z, 0-9, _, $, # 문자만 사용 가능
• 참조 동작
  • [Delete/Modify] Action
    • Cascade : Master 삭제 시 Child 같이 삭제
    • Set Null : Master 삭제 시 Child 해당 필드 Null
    • Set Default : Master 삭제 시 Child 해당 필드 Default 값으로 설정
    • Restrict : Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용
    • No Action : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음
  • Insert Action 
    • Automatic : Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child 입력
    • Set Null : Master 테이블에 PK가 없는 경우 Child 외부키를 Null 값으로 처리
    • Set Default : Master 테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 입력
    • Dependent : Master 테이블에 PK가 존재할 때만 Child 입력 허용
    • No Action : 참조무결성을 위반하는 입력 액션을 취하지 않음
• 테이블 삭제
  • TRUNCATE
    • 전체 데이터를 한번에 삭제
    • 최초 생성되었을 당시의 Storage 남김, 데이터 담겨있던 Storage Release
      (CREATE TABLE 직후의 상태와 같음)
    • 자동 COMMIT, 로그가 남지 않음
    • DDL
  • DELETE
    • WHERE절 사용하여 데이터를 하나하나 선택하여 삭제
    • 데이터를 삭제하더라도 데이터가 담겨있던 Storrage는 Release X
      -> 로그가 남음, COMMIT 전에 ROLLBACK 가능
    • DML
  • DROP
    • 테이블 자체를 완전히 삭제
    • 자동 COMMIT, 로그가 남지 않음
    • DDL
• 트랜잭션 특성
  • 원자성
    트랜잭션에 정의된 연산들은 모두 성공적으로 실행 or 전혀 실행되지 않은상태로
  • 일관성
    트랜잭션이 실행되기 전의 DB 내용이 잘못되어 있지 않다면 트랜잭션 실행 이후에도 DB 내용에 잘못이 있으면 안됨
  • 고립성
    트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들면 안됨
  • 지속성
    트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장
• 트랜잭션에 대한 격리성 낮은 경우 발생할 수 있는 문제점
  • Dirty Read
    다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것
  • Non-Repeatable Read
    한 트랜잭션 내에서같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
  • Phantom Read
    한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상
  • isolation
    트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
• Oracle <-> SQL Server
  • Oracle
    DDL 문장 수행 후 자동 COMMIT
    DDL 문장의 수행은 내부적으로 트랜잭션 종료시킴
  • NULL 데이터 조회 -> WHERE 칼럼명 IS NULL (= NULL X)
  • SQL Server
    DDL 문장 수행 후에 자동 COMMIT X
    CREATE TABLE도 트랜잭션 범주에 포함
  • NULL 데이터 조회 -> WHERE 칼럼명 ''
• DISTINCT
  = GROUP BY 역할, 중복 제외
• 연산자 우선순위
  1. 괄호
  2. 비교 연산자
  3. 논리 연산자 (AND -> OR)
• 내장 함수
  • 단일행 함수
  • 다중행 함수
    • 집계 함수
    • 그룹 함수
    • 윈도우 함수
  다중행 함수, 단일행 함수 모두 단일 값만 반환
• Oracle 1/24/60 : 1분
• SEARCHED_CASE_EXPRESSION, SIMPLE_CASE_EXPRESSION
  CASE WHEN LOC = 'SEOUL' THEN 'GANGNAM'
  <=> CASE LOC WHEN 'SEOUL' TEHN 'GANGNAM'
• 단일행 NULL 관련 함수
  • NVL(A,B) / ISNULL(A,B)
    A==NULL 이면 B 출력 (A, B 데이터 타입 같아야 함)
  • NULLIF(A,B)
    A==B 이면 NULL 다르면 A 리턴
  • COALESCE(A,B, ...)
    최초로 NULL이 아닌 표현식 반환, 모두 NULL이면 NULL반환
    (각 칼럼에서)
• SELECT 문 실행 순서
  • FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY
• JOIN
  • 일반적으로 JOIN은 PK와 FK 값의 연관성에 의해 성립
  • DBMS 옵티마이저는 FROM 절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝을 지어 JOIN 수행
  • EQUI JOIN은 JOIN에 관여하는 테이블 간의 칼럼 값들이 정확하게 일치하는 경우에 사용되는 방법
  • EQUI JOIN은 '=' 연산자에 의해서만 수행, 그 이외의 비교 연산자를 사용하는 경우에는 모두 Non EQUI JOIN
  • 대부분 Non EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있음
• REPLACE(칼럼, 바뀔 문자, 바꿀 문자)