SQL

김현송·2023년 3월 8일
0

SQL

명령어의 종류명령어설명
데이터 조작어 (DML)SELECT데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE 라고도 한다.
INSERT
UPDATE
DELETE
데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어들을 말한다. 예를 들어 데이터를 테이블에 새로운 행을 집어넣거나, 원하지 않는 데이터를 삭제하거나 수정하는 것들의 명령어들을 DML이라고 부른다.
데이터 정의어(DDL)CREATE
ALTER
DROP
RENAME
테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 그러한 구조를 생성하거나 변경하거나 삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어들을 DDL이라고 부른다.
데이터 제어어(DCL)GRANT
REVOKE
데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어를 DCL이라고 부른다
주로 현업에서는 개발 DB와 운영 DB가 나뉘어 있으므로 DBA 권한을 부여할 때 사용한다.
트랜잭션 레이어COMMIT
ROLLBACK
DML에 의해 조작된 결과를 트랜잭션별로 제어하는 명령어를 말한다.

TABLE

데이터를 저장한느 객체로서 관계형 데이터베이스의 기본 단위이다.

모든 데이터를 칼럼과 행의 2차원 구조로 나타나고, 세로 방향을 칼럼 가로 방향을 행(Row)이라 하고, 칼럼과 행이 겹치는 하나의 공간을 필드라 한다.

  • 정규화 : 테이블을 분할하여 데이터의 불필요한 중복을 줄이는 것을 정규화라고 한다.
  • 기본키 : 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 컬럼을 말한다.
  • 외부키 : 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 컬럼을 말한다.

ERD

테이블간의 관계를 직관적으로 표현하는 도식이다.

DDL

데이터 유형

CHARACTER(s) : 고정 길이 문자열 정보

VARCHAR(s) : 가변 길이 문자열 정보

CREATE TABLE

테이블은 일정한 형식에 의해 생성된다. 테이블 생성을 위해서는 해당 테이블에 입력될 테이터를 정의하고, 정의한 데이터를 어떻나 데이터 유형으로 선언할 지를 결정해야한다.

CREATE TABLE 테이블이름 (칼럼명1 DATATYPE[DEFAULT 형식], 칼럼명2 DATATYPE[DEFAULT 형식])

테이블 생성 규칙

  • 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다.
  • 가능한 단수형을 권고한다.
  • 테이블명은 다른 테이블의 이름과 중복되지 않아야 한다.
  • 한 테이블 내에서는 칼럼명이 중복되게 지저오딜 수 없다.
  • 테이블 이름을 지정하고 각 칼럼들은 괄호 "()"로 지정한다.
  • 각 칼럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";"으로 끝난다
  • 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.
  • 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
  • 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.
  • 벤더에서 사전에 정의한 예약어는 쓸 수 없다.
  • 문자 타입이 가질 수 있는 최대길이를 반드시 표시해야 한다.

제약 조건

  • PRIMARY KEY : 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다. 하나의 테이블에 하나의 기본키 제약만 정의할 수 있다.

  • UNIQUE KEY : 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 지정한다. 단 NULL은 고유키 제약의 대상이 아니므로, NULL 값을 가진 행이 여러개가 있더라도 고유키 제약의 위반이 되지 않는다.

  • NOT NULL : NULL 값의 입력을 금지한다.

  • CHECK : 입력할 수 있는 값의 범위 등을 제한한다. TRUE | FALSE 로 평가할 수 있는 논리식을 지정한다.

  • FOREIGN KEY : 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성된다.

생성된 테이블 구조 확인

# oracle
DESCRIBE 테이블명; , DESC 테이블명;
# sql
sp_help 'dbo.테이블명'

CTAS 방법

SELECT 문장을 활용해서 테이블을 생성할 수 있는 방법

  • 주의 사항 : 기존 테이블의 제약조건 중 NOTNULL 만 복제되고 기본키, 고유키, 외래키, CHECK 등의 다른 제약 조건은 없어진다는 점이다.
  • 제약 조건을 추가하기 위해서는 ALTER TABLE 기능을 사용해야 한다.
#Oracle
CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM
#SQL
SELECT * INTO TEMP_TEAM FROM TEAM

ALTER TABLE

업무상 테이블 구조가 변경이 필요할 때, 칼럼을 추가/삭제하거나 제약조건을 추가/삭제하는 작업을 할 때

  • ADD COLUMN

    ALTER TABLE 테이블명 ADD 추가할칼럼명 데이터 유형
  • DROP COLUMN

    ALTER TABLE 테이블명 DROP COLUMN 삭제할컬럼명
  • MODIFY COLUMN

    #Oracle
    ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터유형[DEFAULT][NOT NULL])
    #SQL
    ALTER TABLE 테이블명 ALTER (칼럼명1 데이터유형[DEFAULT][NOT NULL])
  • DROP CONSTRAINT : 테이블 생성시 부여했던 제약 조건을 삭제하는 명령어

  • ADD CONSTRAINT : 테이블 생성 이후 필요에 의해 제약 조건을 추가

  • RENAME TABLE : 테이블 이름 변경

  • DROP TABLE : 테이블 삭제

  • TRUNCATE TABLE : 테이블 자체가 삭제되는 것이 아닌, 해당 테이블에 들어있던 모든 행 삭제

TCL

트랜잭션 개요

COMMIT : 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것

ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌리는 것

SAVEPOINT : 저장점

트랜잭션의 대상이 되는 SQL 문은 DML 문이다. (UPDATE, INSERT, DELETE)

특성

  1. 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.
  2. 일관성 : 트랜잭션이 실행되기 전의 데이터베이스의 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.
  3. 고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
  4. 지속성 : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저자오딘다.

COMMIT

  • 단지 메모리 BUFFER 에만 영향을 받았기 떄문에 데이터의 변경 이전 상태로 복구 가능하다.
  • 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.
  • 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
  • 변경된 행은 잠금이 설정되어서 다른 사용자가 변경할 수 없다.

SQL 트랜잭션 방식

  1. AUTO COMMIT : DML DDL을 수행할 때마다 DBMS가 트랜잭션을 컨트롤하는 방식이다. 명령어가 성공적으로 수행되면 자동으로 COMMIT을 수행하고 오류가 발생하면 자동으로 ROLLBACK을 수행한다.
  2. 암시적 트랜잭션: 오라클과 같은 방식으로 처리된다. 트랜잭션의 시작은 DBMS가 처리하고, 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리한다. 인스턴스 단위 또는 세션 단위로 설정할 수 있다. 인스턴스 단위로 설정하려면 서버 속성 창의 연결화면에서 기본 연결 옵션 중 암시적 트랜잭션에 체크해주어야 한다.
  3. 명시적 트랜잭션 : 트랜잭션의 시작과 끝을 사용자가 모두 지정하는 방식으로 BEGIN TRANSACTION으로 시작하고 COMMIT 또는 ROLLBACK TRANSACTION으로 종료한다.

SAVEPOINT

SAVEPOINT를 정의하면 롤백할 떄 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.

profile
안녕하세요

0개의 댓글