Post

[SQL] DDL, DML, DCL, TCL 기본 쿼리

Posted
By Ennik 7 min read

SQL 기본 명령어 4가지 - DDL, DML, DCL, TCL

데이터 정의어(DDL)

테이블 생성, 열 추가, 열 데이터 타입 변경, 테이블명 변경, 테이블 삭제
CREATE, ALTER, DROP

  • Practice 데이터베이스 생성, 사용하기

    1
2

    CREATE DATABASE Practice;
USE Practice;

  • 회원 테이블 생성

    • 열 이름을 먼저 쓰고 뒤에 데이터 타입 작성
    • 테이블은 각 열마다 반드시 1가지 데이터 타입으로 정의
      • 대표적인 데이터 형식: 숫자형, 문자형, 날짜형
    • 테이블에서 각 열마다 제약 조건 정의 가능
    • PK(Primary Key): 중복되어 나타날 수 없는 단일 값, NOT NULL
    1
2
3
4
5
6

    CREATE TABLE 회원 (
회원번호 INT PRIMARY KEY,     -- 제약조건: PRIMARY KEY
이름 VARCHAR(20),
가입일자 DATE NOT NULL,       -- 제약조건: NOT NULL
수신동의 BIT
);

  • 회원 테이블 조회하기

    1
2
3
4

    SELECT *
FROM 회원;

SELECT * FROM 회원; --한 줄도 가능

  • 테이블에 열 추가하기

    1

    ALTER TABLE 회원 ADD 성별 VARCHAR(2); -- 성별 열 추가

  • 열 데이터 타입 변경하기

    1

    ALTER TABLE 회원 MODIFY 성별 VARCHAR(20);

  • 열 이름 변경하기

    1

    ALTER TABLE 회원 CHANGE 성별 성 VARCHAR(2); -- 성별 -> 성 으로 열 이름 변경

  • 테이블 이름 변경하기

    1

    ALTER TABLE 회원 RENAME 회원정보; -- 회원 -> 회원정보로 테이블 이름 변경

  • 테이블 삭제하기

    1

    DROP TABLE 회원정보;

데이터 조작어(DML)

테이블 삽입, 조회, 수정, 삭제
SELECT, INSERT, UPDATE, DELETE

회원번호이름가입일자수신동의
INTVARCHAR(20)DATEBIT

  • 데이터 삽입하기

    1
2
3
4

    INSERT INTO 회원정보 VALUES (1001, '이익준', '2023-09-01', 1);
INSERT INTO 회원정보 VALUES (1002, '채송화', '2023-09-02', 0);
INSERT INTO 회원정보 VALUES (1003, '김준완', '2023-09-03', 1);
INSERT INTO 회원정보 VALUES (1004, '안정원', '2023-09-04', 0);

  • PRIMARY KEY 제약 조건을 위반하는 경우

    1

    INSERT INTO 회원정보 VALUES (1004, '양석형', '2023-09-05', 1); --에러, 중복 저장 불가

  • NOT NULL 제약 조건을 위반하는 경우

    1

    INSERT INTO 회원정보 VALUES (1005, '양석형', NULL, 1); -- 에러, 저장 불가

  • 데이터 타입 제약 조건을 위반하는 경우

    1

    INSERT INTO 회원정보 VALUES (1005, '양석형', 1, 1); --문자열이 아닌 숫자라서 저장 불가

  • 데이터 조회하기

    1
2
3

    SELECT * FROM 회원정보; -- 테이블의 모든 열 조회
SELECT 회원번호, 이름 FROM 회원정보; -- 특정 열 조회
SELECT 회원번호, 이름 AS 성명 FROM 회원정보; -- 특정 열 이름을 변경하여 조회

  • 데이터 수정하기

    • 수정 전 데이터 수정, 삭제 제한 설정 해제

      • MySQL_Workbench (Edit → Preferences → SQL Editor → Other → Safe updates 체크박스 해제)

    • 열의 모든 데이터 수정

      1

      UPDATE 회원정보 SET 수신동의 = 0;

    • 특정 조건 데이터 수정

      1

      UPDATE 회원정보 SET 수신동의 = 1 WHERE 이름 = '채송화';

  • 데이터 삭제

    • 특정 데이터 삭제

      1

      DELETE FROM 회원정보 WHERE 이름 = '이익준';

    • 모든 데이터 삭제

      1

      DELETE FROM 회원정보;

데이터 제어어(DCL)

사용자 확인, 사용자 추가 및 삭제, 권한 부여 및 삭제
데이터베이스 관리자(DBA)가 특정 사용자에게 데이터 접근 권한 부여, 제거할 때 사용
GRANT, REVOKE

  • 사용자 목록 확인하기

    1

    SELECT * FROM USER;

  • 사용자 추가하기

    1

    CREATE USER 'TEST1'@LOCALHOST IDENTIFIED BY 'TEST2'; # 'TEST1'은 ID, 'TEST2'는 비밀번호

  • 사용자 비밀번호 변경하기

    1

    SET PASSWORD FOR 'TEST2' @LOCALHOST = '1234';

  • 권한 부여 및 제거

    • 권한 종류: CREATE, ALTER, DROP, INSERT, DELETE, UPDATE, SELECT 등

    • 특정 권한 부여

      1

      GRANT SELECT, DELETE ON PRACTICE.회원정보 TO 'TEST1'@LOCALHOST;

    • 특정 권한 제거

      1

      REVOKE DELETE ON PRACTICE.회원정보 FROM 'TEST1'@LOCALHOST;

    • 모든 권한 부여

      1

      GRANT ALL ON PRACTICE.회원정보 TO 'TEST1'@LOCALHOST;

    • 모든 권한 제거

      1

      REVOKE ALL ON PRACTICE.회원정보 FROM 'TEST1'@LOCALHOST;

  • 사용자 삭제

    1

    DROP USER 'TEST1'@LOCALHOST;

트랜잭선 제어어(TCL)

데이터 조작어 명령어 실행, 취소, 임시저장
데이터의 일관성을 유지하기 위해 사용되며, 명령어의 실행을 관리한다.
BEGIN, COMMIT, ROLLBACK

  • 트랜잭션 시작

    1

    BEGIN;

  • 데이터 삽입하고 확인하기

    1
2
3

    INSERT INTO 회원정보 VALUES (1001, '이익준', '2023-09-01', 1);

SELECT * FROM 회원정보; -- 삽입 확인

  • 취소하기

    1

    ROLLBACK;

  • 데이터 삽입 후 트랜잭션 취소 확인하기

    1
2
3
4

    INSERT INTO 회원정보 (회원번호, 이름, 가입일자, 수신동의) VALUES (1001, '이익준', '2023-09-01', 1);
SELECT * FROM 회원정보; -- 삽입된 데이터 확인
ROLLBACK; -- 트랜잭션 취소
SELECT * FROM 회원정보; -- 취소 후 데이터 확인

  • 실행하기

    1

    COMMIT;

  • SAVEPOINT와 ROLLBACK 사용하기

    • 트랜잭션 중에 여러 작업을 수행한 후, 특정 지점으로 돌아가고 싶을 때 SAVEPOINT를 사용한다.
    1
2
3
4
5
6
7

    BEGIN;
INSERT INTO 회원정보 VALUES (1002, '채송화', '2023-09-02', 0);
SAVEPOINT SP1; -- 첫 번째 저장 지점 설정
UPDATE 회원정보 SET 이름 = '김준완' WHERE 회원번호 = 1002;
SAVEPOINT SP2; -- 두 번째 저장 지점 설정
ROLLBACK TO SP1; -- 첫 번째 저장 지점으로 롤백
COMMIT; -- 변경사항 확정
데이터베이스
Data Database SQL
This post is licensed under CC BY 4.0 by the author.