[DB][SQLD][2024] 2-3 관리 구문 정리

https://megamaker.tistory.com/374

[DB][SQLD][2024] 2-2 SQL 활용 정리

ㄴ 이전 게시글

 

관리 구문 - 세부 항목

출처 - 한국데이터산업진흥원

 

---

 

DML

DML(Data Manipulation Language) 

데이터를 조작하는 언어

입력, 수정, 삭제하는 명령어이다.

 

COMMIT이나 ROLLBACK을 수행해야 반영됨

 

SELECT를 DML로 넣는 경우도 있지만 DML은 데이터의 상태가 변하는 명령어만 해당

SELECT는 DQL로 따로 분류

하지만 시험에 "SELECT가 DML"이다 말고 다른 틀린 것 같은 선택지가 있으면 확실히 틀린 것으로 선택할 것

 

 

INSERT 

테이블에 행 삽입

INSERT INTO 테이블명(컬럼1, 컬럼2, ...) VALUES(값1, 값2, ...)
INSERT INTO 테이블명 VALUES(값1, 값2, ...); -- 모든 컬럼의 값을 입력해야 함

 

• 한 번에 한 행만 삽입 가능 (SQL Server는 여러 행 가능)
• 각 컬럼별 도메인에 맞게 삽입해야 함
• 명시하지 않은 컬럼에 NULL 입력되는데 NOT NULL인 컬럼이면 오류 발생
• 문자 컬럼에 숫자 입력 가능
• 숫자 컬럼에 '001'과 같은 숫자 입력 가능

 

 

UPDATE 

데이터 수정 시 사용

 

-- 단일 컬럼 수정
UPDATE 테이블명
SET 수정할컬럼명 = 값
WHERE 조건;

-- 다중 컬럼 수정
UPDATE 테이블명
SET 수정할컬럼명1 = 값1, 수정할컬럼명2 = 값2, ...
WHERE 조건;

 

 

DELETE 

데이터 삭제 시 사용

 

DELETE [FROM] 테이블명 [WHERE 조건];

 

 

MERGE 

데이터 병합 시 사용

한 테이블을 기준으로 다른 테이블에 삽입, 수정, 삭제를 하는 작업

 

MERGE INTO 테이블명
USING 참조테이블 ON (연결조건)  -- 괄호 필수로 사용
	WHEN MATCHED THEN
    	UPDATE
        	SET 수정할내용
        DELETE 조건
    WHEN NOT MATCHED THEN
    	INSERT VALUES(값1, 값2, ...)

 

• UPDATE에서 수정할 테이블이 왼쪽에 와야 함 ex) 수정할 테이블이 T1이면 UPDATE SET T1.NAME = T2.NAME

 

---

 

TCL

TCL(Transaction Control Language) 

트랜잭션을 제어하기 위한 언어

 

트랜잭션

데이터베이스의 논리적인 연산 단위

ACID를 만족함으로써 트랜잭션이 제대로 동작한다는 것을 보장

 

COMMIT 

입력한 내용들을 저장하고 반영하는 명령어

 

 

ROLLBACK 

COMMIT되지 않은 입력한 내용들을 취소하는 명령어

최종 COMMIT 전 또는 특정 SAVEPOINT로 돌아감

 

 

SAVEPOINT 

특정 상황에서 돌아가기 위해 특정 지점을 지정하는 명령어

ROLLBACK TO 세이브포인트명 으로 돌아갈 수 있다.

 

---

 

DDL

DDL(Data Definition Language) 

데이터 구조를 정의하는데 사용하는 언어

• 기본적으로 AUTO COMMIT으로 자동 반영되지만 설정을 통해 직접 COMMIT을 수행하도록 변경할 수 있음

 

 

CREATE 

테이블, 인덱스와 같은 객체를 생성

 

CREATE TABLE [소유자.]테이블명 (
	컬럼명1 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류],
    컬럼명2 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류],
    ...
);

 

소유자 생략 시 명령어를 수행하는 계정 기준으로 적용

 

CREATE TABLE [소유자.]테이블명 (
	컬럼명1 데이터타입 [DEFAULT 기본값],
    컬럼명2 데이터타입 [DEFAULT 기본값],
    ...
    CONSTRAINT MY_PK PRIMARY KEY(컬럼명1, 컬럼명2)  -- 여러 컬럼 묶어서 제약 조건 생성
    CONSTRAINT MY_FK FOREIGN KEY(컬럼명) REFERENCES 다른테이블(다른테이블의 주식별자)
    -- 외래키 설정, 마지막에 ON DELETE CASCADE 이렇게 옵션 추가 가능
);

 

여러 컬럼을 묶어서 제약 조건을 설정하고 싶으면 위와 같이 마지막에 CONSTRAINT로 설정해준다.

 

 

자주 나오는 제약 조건

• PRIMARY KEY - 기본키
• FOREIGN KEY - 외래키
  • CASCADE - 부모 데이터 삭제 시 자식 데이터도 삭제
  • SET NULL - 부모 데이터 삭제 시 자식 데이터의 참조값을 NULL로 수정
  • SET DEFAULT - 부모 데이터 삭제 시 자식 데이터 칼럼의 DEFAULT값으로 변경
  • RESTRICT - 자식 테이블에 해당 데이터가 없을 때만 부모 데이터 삭제 가능
• NOT NULL - NULL 허용 X
• UNIQUE - 중복 허용 X
• CHECK - 데이터의 값을 제한 ex) ADD CONSTRAINT AGE_CHK CHECK (AGE > 20)

 

 

자주 나오는 데이터 타입

CHAR(n)	고정 길이 문자형 빈자리는 공백으로 채워짐
VARCHAR2(n)	가변 길이 문자형 길이보다 작아도 입력된 길이대로 저장
NUMBER(p, s)	ㅇ p는 총 자리수, s는 소수점 자리 ex) p=7, s=2일 경우 소수점 자리 2자리인 최대 만 자릿수인 숫자 -> 12345.12 ㅇ 데이터 입력 시 소수점이 2자리 넘어가도 자동으로 2자리만 들어감(정수부는 해당 X)
DATE	날짜 타입

 

 

테이블 복제

CREATE TABLE 테이블명
AS
SELECT * FROM 복제할테이블명;

 

인덱스나 제약조건은 복사되지 않음

 

 

 

ALTER 

테이블의 구조 변경 시 사용

 

• 컬럼 추가

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류];
ALTER TABLE 테이블명 ADD (
	컬럼명1 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류],
    컬럼명2 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류]
);

 

• 다중 컬럼 추가 시 괄호 필수!
• 새로 추가된 컬럼에는 값이 없는 상태이므로 NOT NULL 지정 불가능
• DEFAULT를 지정한 것과 같은 상황에서는 NOT NULL 지정 가능

 

 

• 컬럼 변경

ALTER TABLE 테이블명 MODIFY 컬럼명 [데이터타입] [DEFAULT 기본값];
ALTER TABLE 테이블명 MODIFY(컬럼명1 [데이터타입] [DEFAULT 기본값], 컬럼명2 [데이터타입] [DEFAULT 기본값], ...);

 

• 데이터 타입과 DEFAULT값은 선택이긴 하지만 둘 중 하나는 무조건 작성해야 함!
• 하나의 컬럼만 변경 시 괄호는 선택
• 여러 컬럼명 변경 시 괄호 필수
• 비어있는 컬럼이면 데이터 타입 변경 가능
• CHAR와 VARCHAR의 경우 데이터 있어도 서로 변경 가능
• DEFAULT 값 삭제하고 싶으면 DEFAULT를 NULL로 변경

        ex) ALTER TABLE STUDENT MODIFY NAME DEFAULT NULL;

 

 

• 컬럼명 변경

ALTER TABLE TABLE_NAME RENAME COLUMN 기존컬럼명 TO 새컬럼명;

 

• 동시에 여러 컬럼명 변경 불가능
• COLUMN이라는 키워드가 있으면 단일 컬럼만 허용한다고 생각하면 됨

 

 

• 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

 

• 데이터 존재와 상관없이 삭제 가능
• 동시에 여러 컬럼 삭제 불가
• 바로 삭제되기 때문에 복구 불가

 

DROP 

인덱스나 테이블과 같은 객체를 삭제

DROP TABLE 테이블명 [PURGE];

 

PURGE를 작성하면 바로 삭제하기 때문에 복구 불가

 

 

TRUNCATE 

테이블 구조만 남기고 데이터를 모두 삭제, 복구 불가

TRUNCATE TABLE 테이블명;

 

 

뷰(VIEW) 

저장공간을 가지진 않지만 특정 쿼리의 결과를 가상 테이블로 표현한 것

 

CREATE [OR REPLACE] VIEW 뷰이름 AS 조회쿼리;  -- 생성
DROP VIEW 뷰이름;  -- 삭제

 

뷰의 특징

• 보안성 - 보여주고 싶은 데이터만 출력할 수 있어서 데이터 보호 가능
• 독립성 - 테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도 됨
• 편리성 - 복잡한 쿼리를 뷰로 생성하여 관련 쿼리를 단순하게 작성 가능
• 테이블을 삭제하면 해당 테이블을 참조하여 만든 뷰도 같이 삭제됨

 

---

 

DCL

DCL(Data Control Language) 

객체에 대한 권한을 부여 또는 회수

• 테이블 소유자는 타인에게 자신의 테이블에 대해나 권한 부여가 가능하다.
• 권한 부여 시에는 테이블 소유자 또는 관리자계정이어야 함

 

권한 종류

• 오브젝트 권한 - 테이블에 대한 권한
• 시스템 권한 - 테이블 생성 등 시스템 작업에 대한 권한

 

GRANT 

권한 부여

GRANT 권한1, 권한2, ... ON 테이블명 TO 유저1, 유저2, ...;

 

 

 

REVOKE 

권한 회수

• 동시에 여러 권한, 여러 유저에 대한 권한 회수 가능

REVOKE 권한1, 권한2, ... ON 테이블명 FROM 유저1, 유저2, ...;

 

 

 

롤(ROLE) 

여러 권한을 가지고 있는 객체

 

롤 생성

CREATE ROLE 롤이름;

 

 

롤에 권한 부여

GRANT 권한1, 권한2, ... ON 테이블명 TO 롤이름;

 

 

유저에게 롤 부여

GRANT 롤이름 TO 유저명;

 

 

롤에서 권한 회수

REVOKE 권한1, 권한2, ... ON 테이블명 FROM 롤이름;

 

 

 

권한 부여 옵션 

• WITH GRANT OPTION
  • WITH GRANT OPTION으로 받은 오브젝트 권한을 다른 사용자에게 부여 가능
  • WITH GRANT OPTION으로 권한을 부여받으면 중간 관리자가 됨
  • 중간 관리자가 부여한 권한은 중간 관리자만 회수 가능
  • 중간 관리자의 권한을 회수하면 중간 관리자가 다른 사용자에게 부여한 권한도 같이 회수
• WITH ADMIN OPTION
  • WITH ADMIN OPTION으로 받은 시스템 권한을 다른 사용자에게 부여 가능
  • 중간 관리자를 거치지 않아도 회수 가능
  • 중간 관리자의 권한 회수 시 중간 관리자가 다른 사용자에게 부여한 권한은 회수 X