[SQL] DDL, DML, DCL, TCL 예제 (Oracle 기준)

DDL

-- 테이블 생성 1 제약조건까지 한번에
CREATE TABLE EMP (
	NO VARCHAR2(100) NOT NULL
	,NAME VARCHAR2(100) DEFAULT '홍길동' NOT NULL
	,ADDR VARCHAR2(100) NULL
	,CONSTRAINT EMP_PK PRIMARY KEY (NO)
);

-- 테이블 생성 2 제약조건은 ALTER문으로 
CREATE TABLE EMP (
	NO VARCHAR2(100) NOT NULL
	,NAME VARCHAR2(100) DEFAULT '홍길동' NOT NULL
	,ADDR VARCHAR2(100) NULL
);
ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (NO);

-- 테이블 생성 3 PK를 칼럼 선언할때 한번에
CREATE TABLE EMP (
	NO VARCHAR2(100) PRIMARY KEY
	,NAME VARCHAR2(100) DEFAULT '홍길동' NOT NULL
	,ADDR VARCHAR2(100) NULL)
);

-- 다른 테이블에서 데이터까지 복사해서 만들기
CREATE TABLE EMP AS SELECT * FROM EMP2; -- 모든 컬럼
CREATE TABLE EMP AS SELECT NAME FROM EMP2; -- 특정 컬럼
CREATE TABLE EMP AS SELECT * FROM EMP2 WHERE 1=2; -- 데이터를 제외한 모든 컬럼

-- 참조관계 정의
-- 1. 부모
CREATE TABLE 부서 (부서번호 CHAR(10), 부서명 CHAR(10), PRIMARY KEY(부서번호));
-- 2. 자식 (CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION)
CREATE TABLE 직원 (직원번호, CHAR(10), 부서명 CHAR(10), PRIMARY KEY(직원번호),
FOREIGN KEY(부서명) REFERENCES 부서(부서명) ON DELETE CASCADE);

-- 인덱스 생성
CREATE INDEX IDX_EMP ON EMP (NAME);

-- 제약조건 생성
ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (NO);
ALTER TABLE EMP ADD CONSTRAINT EMP_CK CHECK(LENGTH(NAME) > 2);

-- 인덱스 생성

-- 칼럼 추가
ALTER TABLE EMP ADD (PHONE VARCHAR2(100));

-- 칼럼 명 변경
ALTER TABLE EMP RENAME COLUMN PHONE TO PHONE_NUM;

-- 칼럼 타입 변경
ALTER TABLE EMP MODIFY (PHONE_NUM VARCHAR2(200));

-- 칼럼 삭제
ALTER TABLE EMP DROP COLUMN PHONE_NUM; 


-- 테이블 전체 삭제
DROP TABLE EMP;

-- 테이블은 삭제하지 않음. 데이터 전체 삭제. 롤백 불가능, 자동 커밋, 데이터 저장공간 모두 Release
TRUNCATE FROM EMP;

-- 테이블 이름 변경
RENAME EMP TO EMP_AFTER;

 

 

DML

-- 데이터 삽입 : 전체 컬럼 데이터 입력시
INSERT INTO EMP VALUES ('1', '홍길동', '영등포');

-- 데이터 삽입 : 특정 컬럼 데이터 입력시
INSERT INTO EMP (NO, NAME) VALUES ('1', '홍길동');

-- 여러 행을 한번에 삽입
INSERT INTO EMP
	SELECT NO, NAME, ADDR FROM EMP2 WHERE ADDR = '영등포';

-- 데이터 수정 
UPDATE EMP SET ADDR = '강남' WHERE NAME = '홍길동';

-- 데이터 일부 삭제
DELETE FROM EMP WHERE NO = '1';

-- 데이터 전체 삭제. 롤백가능, 직접 커밋, 데이터 저장공간은 그대로 살아있음
DELETE FROM EMP;

-- 여러 테이블의 데이터를 한 테이블에 모으기
MERGE INTO 타겟테이블
	USING 소스테이블
	ON (타겟테이블.컬럼1 = 소스테이블.컬럼1)
	WHEN MATCHED THEN
		... -- ON절 조건이 성립한다면 해당 쿼리 수행
	WHEN NOT MATCHED THEN
		... -- ON절 조건이 성립하지 않는다면 해당 쿼리 수행

 

DCL

-- 계정 생성
CREATE USER 사용자 IDENTIFIED BY 패스워드;

-- 사용자 생성 권한 부여
GRANT CREATE USER TO 사용자;

-- 로그인 권한 부여
GRANT CREATE SESSION TO 사용자;

-- 테이블 생성 권한 부여
GRANT CREATE TABLE TO 사용자;

-- 다른 사용자에게 테이블 SELECT 권한 부여
GRANT SELECT ON 나의테이블 TO 상대방

-- 여러 개의 권한을 뭉친 하나의 ROLE 생성
CREATE ROLE NEW_ROLE;
GRANT CREATE SESSION, CREATE TABLE TO NEW_ROLE;

-- 생성한 ROLE을 사용자에게 부여
GRANT NEW_ROLE TO 사용자;


-- 릴레이션 권한
-- 1. 사용자1이 다른 유저에게 테이블에 대해서 권한을 줄 수 있도록 설정
GRANT SELECT, INSERT, DELETE ON 테이블명 TO 사용자1 WITH GRANT OPTION;
-- 2. 사용자1이 사용자2에게 권한 부여
GRANT SELECT, INSERT, DELETE ON R TO 사용자2;
-- 3. 사용자1의 테이블명의 DELETE 권한을 제거
REVOKE DELETE ON 테이블명 FROM 사용자1;
-- 4. 사용자1의 테이블명의 INSERT 권한 제거 및 사용자2도 자동으로 제거됨
REVOKE INSERT ON 테이블명 FROM 사용자1 CASCADE;