[MySQL] Constraint / DML / DDL / DCL / TCL

[ 제약 조건 / Constraints ]

Constraint

 - 제약 조건은 테이블의 데이터 유형에 적용 가능한 규칙

 - 제약 조건을 사용하면 테이블의 특정 열에 저장 가능한 데이터 타입 및 데이터에 대한 제한 가능

 

01. NOT NULL

       - 해당 제약 조건은 열에 null 값을 저장할 수 없음을 나타냄

       - 해당 열이 NOT NULL로 지정 시 데이터 타입 및 데이터 또한 null 값이 저장될 수 없음

       - 아래 이미지와 같이 NOT NULL이 지정될 경우 새 행 삽입 시 마다 ID, NAME 필드에는 반드시 값이 필요함

NOT NULL 예시

 

02. UNIQUE [유니크 제약조건]

       - 테이블의 각 행을 고유하게 식별하는데 도움을 주는 제약 조건

       - 한 개의 테이블에 두 개 이상의 유니크 열 존재 가능

       - 즉 유니크 제약 조건이 지정 될 경우 해당 열의 값은 중복 될 수 없음

유니크 제약 조건 예시

 

03. PRIMARY KEY / PK [기본 키]

       - 프라이머리 키(기본 키)는 테이블의 각 행을 고유하게 식별할 수 있는 필드

       - 기본 키는 유일한 값이기 때문에 중복 / 공백 불가

       - 관계형 데이터베이스에서 식별이 가능한 기본키를 의미

 

04. FOREIGN KEY / FK [외부 키]

       - 두 테이블의 데이터 간 연결을 설정하고 강제 적용

       - 외부 키 필드는 다른 테이블의 기본 키를 가리키며 테이블들 사이에 일종의 링크 생성 역할을  함

       - 외부 키가 있는 테이블을 하위 테이블이라 하며, 기본 키가 있는 테이블을 참조 테이블 또는 상위 테이블이라 함

Users 테이블의 User_id를 외부키로 사용하는 Orders 테이블

 

05. CHECK [검사 제약조건]

       - 특정 조건을 충족하도록 열의 값을 검증할 때 사용

       - 열에 배치될 수 있는 값 범위를 제한하는데 사용

       - CHECK 제약 조건은 일반적으로 SQL의 CREATE TABLE 명령과 함께 사용

pets 테이블의 Gender 데이터 열에 'Male', 'Female', 'Unknown' 세 유형만 허용하도록 CHECK 제약 조건 설정

 

06. DEFAULT [기본값 제약조건]

       - 해당 제약 조건은 사용자가 값을 지정하지 않을 때 열의 기본값을 지정

       - 특정 값을 지정하지 않을 경우 기본값은 모든 새 레코드에 자동 추가

pets 테이블의 Gender 데이터의 기본값을 'Female'로 설정

 

 

SQL 명령어 구조도 / 이미지 출처 : https://www.geeksforgeeks.org/sql-ddl-dml-tcl-dcl/

 

[ 데이터 조작 언어 / DML ]

Data Manipulation Language / DML

 - 데이터 검색, 등록, 삭제, 갱신 등 데이터 조작을 위해 사용되는 명령어

 

01. SELECT FROM [데이터 조회]

       - 데이터 조회 시 사용되는 명렁어로 DQL(Data Query Language)로 분류되기도 함

# Syntax
SELECT col_1, col_2 FROM table_name; 
# col_1, col_2 : 테이블 내 조회할 컬럼

 

02. INSERT INTO [데이터 삽입]

       - 테이블 내 데이터 삽입 시 사용되는 명령문

# Syntax
INSERT INTO table_name(col_1, col_2, col_3)
  VALUES(val_1, val_2, val_3, val_4);

 

03. UPDATE SET [데이터 갱신]

       - 기존 테이블의 데이터를 갱신할 때 사용

       - 요구사항에 따라 단일 컬럼뿐만 아니라 여러 컬럼 갱신 가능

# Syntax
# col_2 의 값이 val_2인 데이터의 col_1 값을 val_1으로 갱신
UPDATE table_name 
  SET col_1 = val_1
  WHERE col_2 = val_2;

 

04. DELETE FROM [데이터 삭제]

       - 테이블에서 기존 레코드를 삭제할 때 사용

       - WHERE 절에서 지정한 조건에 따라 단일 레코드 또는 여러 레코드 삭제 가능

# Syntax
DELETE FROM table_name
  WHERE 조건;

 

 

[ 데이터 정의 언어 / DDL ]

Data Definition Language / DDL

 - 테이블, 인덱스, 뷰 등과 같은 관계형 데이터베이스의 구조를 정의

 - 테이블의 속성 생성, 변경 및 삭제 등 테이블 수정에 사용 가능한 명령어

 

01. CREATE TABLE [테이블 생성]

      - 해당 명령문은 SQL에서 데이터베이스 내 테이블을 생성할 때 사용

mydata > smp01 테이블 생성

 

02. DROP TABLE [테이블 삭제]

       - DROP 명령문은 전체 데이터베이스 또는 테이블 삭제 시 사용

DB 또는 테이블 전체 삭제 명령문

 

03. TRUNCATE TABLE [테이블 내 모든 행(데이터) 삭제]

       - TRUNCATE 명령문은 할당 해제를 위해 테이블의 범위를 표시하는데 사용되는 DDL 작업임

       - TRUNCATE 명령의 경우 테이블 내부의 데이터를 삭제하지만 테이블 자체는 삭제하지 않음

       - 일반적으로 매우 빠르게 수행되며 DROP 명령어와 달리 테이블 내 데이터는 전부 사라지지만

          테이블의 구조는 남아 있음.

TRUNCATE 명령 사용 후 테이블 구조는 남아 있는 것을 테이블 조회를 통해 확인 가능

 

04. ALTER TABLE [테이블 수정]

       - ALTER TABLE 명령문은 기존 테이블을 수정, 다양한 제약 조건 추가 혹은 삭제하는데 사용

 

      a. ADD [열 추가]

           - 기존 테이블에 열을 추가할 때 사용

           - 테이블에 열 추가 시 데이터 타입 정의 필요

# Syntax
ALTER TABLE <table_name>
  ADD (Columnname_1 datatype,
       Columnname_2 datatype,
       ...
       Columnname_n datatype );

 

      b. DROP [열 삭제]

            - DROP COLUMN 명령문은 테이블 내 열을 삭제하는데 사용

# Syntax
ALTER TABLE table_name
  DROP COLUMN column_name;

 

      c. MODIFY [열 속성 변경]

            - 테이블 내 존재하는 기존 열의 데이터 타입을 수정하는데 사용되며 여러 열을 동시에 수정 가능

            - 구문은 데이터베이스에 따라 약간씩 다를 수 있음

# Syntax
ALTER TABLE table_name
  MODIFY column_name column_type;

 

      d. CHANGE [열 이름 수정]

            - 테이블 내 존재하는 기존 열(컬럼)의 이름을 수정하는 명령문

            - 명령을 통해 컬럼명 수정 시 단순하게 컬럼명만 변경하는 것이 아닌 컬럼을

              새로 덮어 쓰는 개념이기에 컬럼 재정의가 필요함

# Syntax
ALTER TABLE table_name
  CHANGE table_name new_table_name VARCHAR(10);

 

      e. RENAME [테이블 명 수정]

            - 해당 명령문을 통해 테이블 명 수정 가능

#Syntax
ALTER TABLE table_name      # 기존 table 명
  RENAME TO new_table_name; # 수정할 table 명

 

 

[ 데이터 제어 언어 / DCL ]

Data Control Language (DCL)

 - 데이터베이스에서 데이터에 대한 엑세스를 제어하는데 사용되는 명령어

 - 데이터 객체들에 접근 가능하도록 권한을 부여 및 박탈

 

01. GRANT [권한 부여]

        - MySQL의 CREATE USER 문을 사용하여 계정을 만들 수 있으나 계정에 권한 부여는 하지 않음

        - 사용자 계정에 권한을 부여할 때 GRANT 명령문 사용

# Syntax
GRANT 권한명 ON 개체/테이블 TO 사용자명

# 01.사용자명이 Admin인 사용자에게 'Users'라는 테이블의 SELECT 권한을 부여하는 경우
GRANT SELECT ON Users TO 'Admin'@'localhost;

# 02. 사용자에게 둘 이상의 권한을 부여할 경우
GRANT SELECT, INSERT, DELETE ON Users TO 'Admin'@'localhost;

# 03. 사용자에게 모든 권한을 부여하는 경우
GRANT ALL ON Users TO 'Admin'@'localhost;

# 04. 'Users' 테이블의 모든 사용자에게 특정 권한을 부여하는 경우
GRANT SELECT ON Users TO '*'@'localhost;

 

02. REVOKE [권한 박탈]

        - REVOKE 명령문은 사용자에게 부여된 권한의 일부 혹은 전부를 박탈하는데 사용됨

        - 명령문 사용 형식이 GRANT문과 거의 유사함

# Syntax
REVOKE 권한명 ON 개체/테이블 FROM 사용자명

# 01. 사용자명이 'Admin'인 사용자의 Users 테이블에 대한 특정 권한 취소
REVOKE SELECT ON Users FROM 'Admin'@'localhost;

 

DCL 에서 부여 가능한 권한 / 이미지 출처 : https://www.geeksforgeeks.org/mysql-grant-revoke-privileges/?ref=gcse

 

 

[ 트랜잭션 제어 언어 / TCL ]

Transaction Control Language (TCL)

 - 데이터베이스의 트랜잭션을 관리하는 사용되는 명령어

 - 논리적인 작업의 단위를 묶어 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별 제어

 

01. COMMIT [트랜잭션 영구 저장]

        - COMMIT 명령문은 트랜잭션에서 변경한 내용을 테이블/데이터베이스에 영구 저장할 때 사용

        - 트랜잭션(INSERT, UPDATE, DELETE) 작업 내용을 실제 데이터베이스 저장

        - 데이터베이스 커밋 실행 후에는 이전 상태로 복구 불가능

 

02. ROLLBACK [데이터베이스 복원]

       - 데이터베이스를 마지막 커밋 상태로 복원

       - 세이브포인트 명령과 함께 사용시 세이브포인트로 점프 가능

 

03. SAVEPOINT [트랜잭션 일시 저장]

       - 롤백 명령과 함께 사용 시 필요할 때마다 세이브포인트 지점으로 롤백 가능

# Syntax
# 01. 세이브 포인트 생성
SAVEPOINT 세이브포인트 명;

# 02. 세이브 포인트로의 ROLLBACK(롤백)
ROLLBACK TO 세이브포인트 명;