[SQL] 관리 구문 (DCL, DDL, DML, TCL)

is Yoon·2023년 9월 3일
sql

SQL

목록 보기
3/4
구문역할문법커밋
DCL, 데이터 제어어 (Control)권한받기GRANT : 권한 부여
REVOKE : 권한 회수
DDL, 데이터 정의어 (Definition)데이터 구조 정의CREATE : 구조 생성
ALTER : 구조 변경
DROP : 구조 삭제
RENAME : 이름 변경
TRUNCATE : 테이블 초기화		AUTO
DML, 데이터 조작어 (Manipulation)데이터 조회SELECT : 조회
데이터 변형INSERT : 데이터 입력
UPDATE : 데이터 수정
DELETE : 데이터 삭제		SQL AUTO
TCL, 트랜잭션 제어어권한받기COMMIT : 반영
ROLLBACK : 취소
SAVEPOINT : 위치지정

기본 명령어

DESC DB명.테이블명   -- Describe 테이블 구조 확인(출력)
# 변수를 사용하는 방법
SET @변수명 = 변수값    -- 변수 선언 및 값 대입
SELECT @변수명;       -- 변수의 값 출력

# LIMIT에 변수 사용하는 방법 : PREPARE ~ EXECUTE
SET @count = 3
PREPARE mySQL FROM 'SELECT name, id FROM personal ORDER BY name LIMIT ?';
-- ? : 현재는 모르지만 나중에 채워짐
EXECUTE mySQL USING @count



DDL, 데이터 정의어

Data Definition Language

  • CREATE, ALTER, DROP, RENAME, TRUNCATE

CREATE (데이터베이스 생성)

CREATE DATABASE my_db;   -- 데이터베이스 생성
USE my_db;               -- 데이터베이스 선택
CREATE TABLE 테이블명 ()   -- 테이블 생성

CREATE (테이블 생성)

CREATE TABLE 테이블명 (컬럼명1 DATATYPE [DEFAULT 형식 NULL 여부]);

CREATE TABLE EXAMPLE (
	column1 NUMBER AUTO_INCREMENT NOT NULL,
    column2 VARCHAR2(20) NOT NULL,
    column3 VARCHAR2(15) 
    column4 VARCHAR2(20) FOREIGN KEY
    CONSTRAINT EXAMPLE_PK PRIMARY KEY (column1),
    CONSTRAINT EXAMPLE_FK FOREIGN KEY (column2) REFERENCES col2(column2),
);
# 기존 테이블 복제하여 생성하는 방법 : CTAS
# 컬럼별로 데이터 유형을 다시 명시해주지 않아도 되지만,
# 제약조건 중 NOT NULL만 복사되므로, (나머진 초기화)
# 나머지 제약조건은 필요하면 ALTER 명령어를 통해 정의해야 한다.
CREATE 테이블명 AS SELECT * FROM 복사할 테이블명;

DATATYPE : CHAR, VARCHAR, NUMBER, DATE

  • MySQL NUMBER : TINYINT, SMALLINT, INT, BIGINT
    - UNSIGNED : 범위가 0부터 시작되게 설정
  • MySQL DATA : LONGTEXT, LONGBLOB
    - BLOB : 글자가 아닌 이미지, 동영상 등의 데이터 (이진 Binary 데이터)
  • MySQL DATE : DATE(날짜만), TIME(시간만), DATETIME

AUTO_INCREMENT

  • 열을 정의할 때 1부터 증가하는 값을 자동 입력 (단, 반드시 PK로 지정)
  • 초기값 설정 가능
  • 증가값 설정 가능 : SET @@auto_increment_incement = 시스템 변수 이용

테이블명 생성 시 지켜야 할 규칙

  • 테이블명, 한 테이블 내 컬럼명은 고유해야 하며, 숫자로 시작될 수 없다.
  • 컬럼명 뒤에 데이터 유형, 데이터 크기가 명시되어야 한다.
  • 컬럼에 대한 정의는 괄호() 안에 기술한다.
  • 각 컬럼들은 콤마,로 구분된다.
  • 마지막은 세미콜론;으로 끝난다.

제약조건 CONSTRAINT

  • PRIMARY KEY (PK, 기본키) : 테이블에 저장된 각각의 row에 대한 고유성 보장, 한 테이블당 하나씩만 정의 가능, NULL 값 x, 자동으로 UNIQUE 인덱스로 생성됨
  • UNIQUE KEY (고유키) : 테이블에 저장된 각각의 row에 대한 고유성 보장, NULL 값 o
  • NOT NULL : 해당 컬럼의 NULL 값의 입력 금지
  • CHECK : 컬럼에 저장될 수 있는 값의 범위 제한
  • FOREIGN KEY (외래키) : 하나의 테이블이 다른 테이블을 참조하고자 할 때 정의, 참조 무결성 제약 옵션 별도로 선택 가능
    • CASCADE : Parent 값 삭제 시 Child 값 같이 삭제
    • SET NULL : Parent 값 삭제 시 Child의 해당 컬럼 NULL 처리
    • SET DEFAULT : Parent 값 삭제 시 Child의 해당 컬럼 DEFAULT 값으로 변경
    • RESTRICT : Child 테이블에 해당 데이터가 PK로 존재하는 경우에만 Parent 값 삭제 및 수정 가능
    • NO ACTION : 참조 무결성 제약이 걸려있는 경우 삭제 및 수정 불가

제약조건 더 자세히 알아보기


ALTER (테이블 변경)

ALTER TABLE 테이블명 ADD 컬럼명 데이터유형;   # 컬럼 추가
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;   # 컬럼 삭제

# 컬럼 수정 (Oracle = MODIFY, SQL Server = ALTER)
ALTER TABLE 테이블명 MODIFY (컬럼명 데이터유형 [DEFAULT value] [NOT NULL], ..);
ALTER TABLE 테이블명 ALTER (컬럼명 데이터유형 [DEFAULT value] [NOT NULL], ..);

ALTER TABLE 테이블명 RENAME COLUMN 변경할_컬럼명 TO 새_컬럼명;   # 컬럼 이름 변경
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);   # 제약조건 추가
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;   # 제약조건 삭제

DROP (테이블 삭제)

DROP TABLE 테이블명 [CASCADE CONSTRAINT];
  • 만약 해당 테이블을 참조하고 있는 테이블이 존재하는 경우 CASCADE 옵션을 명시해야 삭제된다.
  • CASCADE CONSTRAINT : 참조 제약 조건도 함께 삭제한다는 의미

RENAME (테이블 이름 변경)

RENAME 변경전_테이블명 TO 변경후_테이블명;

TRUNCATE (테이블 초기화)

TRUNCATE TABLE 테이블명;
  • 테이블 구조는 유지, 데이터만 전부 삭제
  • ROLLBACK 불가능






DML, 데이터 조작어

Data MAnipulation Language

  • SELECT & INSERT, UPDATE, DELETE

DDL에서 정의한 대로 데이터를 입력하고, 입력된 데이터를 수정, 삭제, 조회하는 명령어
SQL Server에서는 AUTO COMMIT 된다.

INSERT (데이터 입력)

INSERT INTO 테이블명 (컬럼 리스트) VALUES (데이터 리스트);
  • 명시되지 않은 컬럼에는 NULL 값 입력
    PK나 NOT NULL 제약조건이 걸린 컬럼에는 NULL이 있으면 안되므로 주의
  • (컬럼 리스트) 생략 가능
    데이터 리스트가 전체 컬럼에 입력됨

다른 테이블의 데이터를 한 번에 입력하는 INSERT INTO ~ SELECT

INSERT INTO 테이블명 (열이름1, 열이름2, ...)
	SELECT ~ ;

UPDATE (데이터 수정)

UPDATE 테이블명 SET 수정할 컬럼명 = 새로운 데이터 [WHERE 수정할 데이터에 대한 조건]

UPDATE 테이블명 SET 컬럼1 = 데이터, 컬럼2 = 데이터 .. [WHERE __]
  • WHERE절이 없으면 테이블의 모든 Row가 변경되므로 주의

DELETE (데이터 삭제)

DELETE FROM 삭제할 테이블명 [WHERE 수정할 데이터에 대한 조건]
  • WHERE절이 없으면 테이블의 모든 Row가 삭제되므로 주의
  • 데이터 전체 삭제 : TRUNCATE (별도의 로그를 쌓지 않아 시스템 부하 측면에서 유리)

MERGE (데이터 입력 + 변경)

MERGE INTO 타겟 테이블명   # 테이블의 데이터를 변경 또는 생성
	USING 비교 테이블명    # 변경/생성 시 이 테이블을 이용
	ON 조건
WHEN MATCHED THEN       # 조건에 맞는 데이터가 있으면 데이터 변경
	UPDATE SET 컬럼1 = 새로운 데이터, ..
WHEN NOT MATCHED THEN   # 조건에 맞는 데이터가 없으면 데이터 생성
	INSERT [(컬럼1, ..)] VALUES (데이터, ..);






TCL, 트랜잭션 제어어

Transaction Control Language

  • COMMIT, ROLLBACK, SAVEPOINT

트랜잭션(쪼개질 수 없는 논리적인 업무 단위)을 제어하는 명령어

  • 원자성 - 트랜잭션에 정의된 연산들은 모두 성공적으로 실행되던지 전혀 실행되지 않던지 (all or nothing)
  • 일관성 - 트랜잭션 실행 전 DB 내용이 잘못되지 않았다면 실행 이후에도 DB 내용에 잘못이 있으면 안 된다.
  • 고립성 - 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어선 안 된다.
  • 지속성 - 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장된다.

잠금 (LOCKING) : 트랜잭션이 수행되는 동안 특정 데이터에 대해 다른 트랜잭션이 동시에 접근하지 못하도록 제한이 걸린다.

암시적 트랜잭션 : 시작은 DBMS가 처리, 끝은 명시적 처리하는 방식 (Oracle)
명시적 트랜잭션 : 시작과 끝 모두 사용자가 명시적으로 지정하는 방식


COMMIT

INSERT, DELETE, UPDATE 후 변경된 내용을 확정/반영하는 명령어
올바르게 반영된 데이터를 데이터베이스에 반영시키는 것

  • SQL Server : AUTO COMMIT(자동 커밋, 오류 시 롤백), DML/DDL 수행 후 DBMS가 트랜잭션 컨트롤

ROLLBACK

INSERT, DELETE, UPDATE 후 변경된 내용을 취소하는 명령어
트랜잭션 시작 이전의 상태로 되돌리는 것

  • 데이터 무결성 보장
  • 영구 변경 전 데이터의 변경 사항 확인
  • 논리적으로 연관된 작업을 그룹핑하여 처리 가능

SAVEPOINT (저장점)

ROLLBACK을 수행할 때 전체 작업을 되돌리지 않고 일부만 되돌릴 수 있게 하는 기능을 가진 명령어






DCL, 데이터 제어어

Data Control Language

  • USER 관련 명령어 : CREATE USER, ALTER USER, DROP USER
  • 권한 관련 명령어 : GRANT, REVOKE
  • Role 관련 명령어 :

유저를 생성하고, 데이터를 컨트롤 할 수 있는 권한을 부여하거나 회수하는 명령어

CREATE/ALTER/DROP USER (사용자 생성/변경/삭제)

CREATE USER 사용자명 IDENTIFIED BY 패스워드;
ALTER USER 사용자명 IDENTIFIED BY 패스워드;
DROP USER 사용자명;

GRANT, REVOKE (권한 부여, 회수)

# GRANT 권한 TO 사용자명;
GRANT CREATE SESSION TO 사용자명;
GRANT CREATE USER TO 사용자명;
GRANT CREATE TABLE TO 사용자명;

# REVOKE 권한 FROM 사용자명;
REVOKE CREATE TABLE FROM 사용자명;

ROLE

롤 Role이란, 특정 권한들을 하나의 세트처럼 묶는 것으로, 유저와 권한 사이에서 중개 역할을 한다.

# ROLE 생성
CREATE ROLE 롤명;

# ROLE에 권한 부여
GRANT CREATE SESSION, CREATE USER, CREATE TABLE TO 롤명;

# ROLE을 사용자에게 부여
GRANT 롤명 TO 사용자명;





요약

DDL 데이터 정의어 : CREATE, ALTER, DROP, RENAME, TRUNCATE

# 1. 테이블(구조) 생성
CREATE TABLE 테이블명 (칼럼명1 DATETYPE [DEFAULT 형식], ..) ;

# 2. 테이블 변경
ALTER TABLE 테이블명 ADD 추가할_칼럼명 데이터유형;   # 칼럼 추가
ALTER TABLE 테이블명 DROP COLUMN 삭제할_칼럼명;   # 칼럼 삭제
ALTER TABLE 테이블명 MODIFY (칼럼명, 데이터유형);   # Oracle 칼럼 수정
ALTER TABLE 테이블명 ALTER (칼럼명, 데이터유형);   # SQL Server 칼럼 수정
ALTER TABLE 테이블명 RENAME COLUMN 변경될_칼럼명 TO 새칼럼명;   # 칼럼 이름 변경
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;   # 제약조건 삭제
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명;   # 제약조건 추가

# 3. 테이블명 변경
RENAME 변경전_테이블명 TO 변경후_테이블명;

# 4. 테이블/데이터 삭제
DROP TABLE 테이블명 [CASCADE CONSTRAINT];   # 테이블 구조/데이터, [관계 있던 참조 제약조건] 삭제
TRUNCATE TABLE 테이블명;   # 테이블 구조는 유지, 데이터만 전부 삭제

DML 데이터 조작어 : SELECT, INSERT, UPDATE, DELETE

# 1. 데이터 입력
INSERT INTO 테이블명 (컬럼 리스트) VALUES (데이터 리스트);

# 2. 데이터 변경
UPDATE 테이블명 SET 수정할 컬럼명 = 새로운 데이터 [WHERE 수정할 데이터에 대한 조건];

# 3. 데이터 삭제
DELETE FROM 테이블명;

# 4. 데이터 조회
SELECT * FROM [WHERE __ GROUP BY __ HAVING __ ORDER BY];

DCL 데이터 제어어 : GRANT, REVOKE


TCL 트랜잭션 제어어 : COMMIT, ROLLBACK, SAVEPOINT




정리

DELETE, DROP, TRUNCATE 차이

DELETE - 테이블 안 데이터 삭제 (오래 걸림)
DROP - 테이블 완전 삭제
TRUNCATE - 테이블 구조 유지, 데이터 전체 삭제 (비교적 빠름)

profile
is Yoon
planning design development with data
이전 포스트

데이터베이스 기본

0개의 댓글