본문 바로가기
자격증/정보처리기사

[정보처리기사 실기] DDL, DCL, DML 정리

by char_lie 2023. 4. 18.
반응형

자격증 준비하면서 내가 이해하기 편하게, 다시 보기 좋게 정리하는 정보처리기사의 내용 (자격증 상세 내용은 아래)

http://www.q-net.or.kr/crf005.do?id=crf00505&gSite=Q&gId=

 

http://www.q-net.or.kr/crf005.do?gId=&gSite=Q&id=crf00505

 

www.q-net.or.kr

DDL, DCL, DML 부분을 정리한 내용

DDL

  • DB를 구축하거나 수정할 목적으로 사용하는 언어
  • CREATE, ALTER, DROP으로 구성

CREATE SCHEMA

-- 스키마를 정의하는 명령어, []는 생략가능
CREATE SCHEMA 스키마명 AUTHORIZATION 사용_id;

CREATE DOMAIN

-- 도메인을 정의하는 명령문, []는 생략가능
CREATE DOMAIN 도메인명 [AS] 데이터타입
	[DEFAULT 기본값]
	[CONSTRAINT 제약조건명 CHECK (범위값)];

CREATE TABLE

-- 테이블을 정의하는 명령문, []는 생략가능
CREATE TABLE 테이블명
	(속석명 데이터타입 [DEFAULT 기본값] [NOT NULL],···
	[PRIMARY KEY(기본키속성명)]
	[UNIQUE(대체키속성명)]
	[FOREIGN KEY(외래키속성명)]
		REFERENCES 참조테이블(기본키속성명)
		[ON DELETE 옵션]
		[ON UPDATE 옵션]
	[CONSTRAINT 제약조건명] [CHECK(조건식)]

CREATE VIEW

-- 뷰(View)를 정의하는 명령문
CREATE VIEW 뷰명[(속성명)]
AS SELECT ···;

CREATE INDEX

-- 인덱스를 정의하는 명령문
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명[ASC|DESC])
[CLUSTER];
-- CLUSTER을 사용하면 인덱스가 클러스터드 인덱스로 설정

ALTER TABLE

--- 테이블에 대한 정의를 변경하는 명령문
ALTER TABLE 테이블명 ADD 속성명 데이터타입 [DEFAULT 기본값];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT 기본값];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

DROP

-- 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거하는 명령문
DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이블명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRAINT 제약조건명;

DCL

  • 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어
  • COMMIT, ROLLBACK, GRANT, REVOKE로 구성

GRANT / REVOKE

  • GRANT : 권한 부여를 위한 명령어
  • REVOKE : 권한 취소를 위한 명령어
GRANT 사용자등급 TO 사용자ID리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자ID리스트;

GRANT 권한리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한리스트 ON 개체 FROM 사용자[CASCADE];
-- WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한
-- GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
-- CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소

COMMIT

  • 트랜잭션 처리가 정상적으로 완료된 후 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령어
  • COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT 됨
  • DML이 실패 자동으로 ROLLBACK이 되도록 Auto commit 기능 설정 가능

ROLLBACK

  • 변경되었으나 아직 COMMIT 되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어
  • 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 데이터베이스에 반영되는 비일관성 상태가 될 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백되어야 함

DML

  • 저장된 데이터를 실질적으로 관리하는 데 사용되는 언어
  • SELECT, INSERT, DELETE, UPDATE로 구성

INSERT INTO

-- 기본 테이블에 새로운 튜플을 삽입할 때 사용
INSERT INTO 테이블명([속성명])
VALUES (데이터1, 데이터2, ···)

DELETE FROM

-- 기본 테이블에서 특정 튜플을 삭제할 때 사용
DELETE
FROM 테이블명
[WHERE 조건];

UPDATE~ SET~

-- 기본 테이블에 있는 튜플들 중 특정 튜플의 내용을 변경할 떄 사용
UPDATE 테이블명
SET 속성명 = 데이터
[WHERE 조건];

SELECT

SELECT [PREDICATE] [테이블명] 속성명 [AS 별칭] [테이블명]
[그룹함수(속성명) [AS 별칭]]
FROM 테이블명
[WHERE 조건]
[GROUP BY 속성명, 속성명, ···]
[HAVING 조건]
[ORDER BY 속성명 [ASC|DESC]];

-- PREDICATE : 검색할 튜플 수를 제한하는 명령어
-- DISTINCT : 중복된 튜플이 있으면 1개만 표시함

조건 연산자

  • 비교 연산자 : =, <>, >, <, >=, <=
  • 논리 연산자 : AND, NOT, OR
  • LIKE 연산자 : %(모든 문자), _ (문자 1개), #(숫자 1개)

SELECT문 기본 검색 방법

-- 사원 테이블의 전체를 출력
SELECT * FROM 사원;
SELECT 사원, * FROM 사람;
SELECT 이름, 부서, 생일, 주소, 기본급 FROM 사원;
SELECT 사원.이름, 사원.부서, 사원.생일, 사원.주소, 사원.기본급 FROM 사원

-- 사원 테이블에서 중복된 주소를 제외하고 출력
SELECT DISTINCT 주소 FROM 사원;

-- 기본급에 특별수당 10을 더한 월급을 OO부서의 OOO의 월급 OOO 형태로 출력
SELECT 부서 + '부서의' AS 부서2, 이름 + '의 월급' AS 이름2, 기본급+10 AS 기본급2 FROM 사원;

-- 사원 테이블에서 기획부의 모든 튜플 검색
SELECT * FROM 사원 WHERE 부서 = '기획'

-- 사원 테이블에서 기획부에 근무하면서 서울에 사는 사람의 튜플 검색
SELECT * FROM 사원 WHERE 부서 = '기획' AND 주소 = '서울';

-- 사원 테이블에서 부서가 기획이거나 인터넷인 튜플 검색
SELECT * FROM 사원 WHERE 부서 = '기획' OR 부서 = '인터넷';

-- 사원 테이블에서 성이 김인 사람의 튜플 검색
SELECT * FROM 사원 WHERE 이름 LIKE '김%';

-- 사원 테이블에서 생일이 01/01/60에서 12/31/70 사이인 튜플 검색
SELECT * FROM 사원 WHERE 생일 BETWEEN #01/01/60# AND #12/31/70#

-- 사원 테이블에서 주소가 NULL인 튜플 검색
SELECT * FROM 사원 WHERE 주소 IS NULL;

-- 사원 테이블에서 주소를 기준으로 내림차순 정렬시켜 상위 2개 튜플 검색
SELECT TOP 2 * FROM 사원 ORDER BY 주소 DESC;

-- 사원 테이블에서 부서를 기준으로 오름차순, 이름을 기준으로 내림차순 정렬하여 검색
SELECT * FROM 사원 ORDER BY 부서 ASC, 이름 DESC;

-- 취미가 게임인 사원의 이름과 주소를 검색
SELECT 이름, 주소 FROM 사원 WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미 = '게임');

-- 취미활동을 하지 않는 사원 검색
SELECT * FROM 사원 WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);

-- 취미활동을 하는 사원들의 부서 검색
SELECT 부서 FROM 사원 WHERE EXISTS (SELECT 이름 FROM 여가활동 WHERE 여가활동.이름 = 사원.이름);

-- 경력이 10년 이상인 사원의 이름, 부서, 취미, 경력 검색
SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원.여가활동 WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;

그룹 함수

  • COUNT(속성명) : 그룹별 튜플 수를 구하는 함수
  • SUM(속성명) : 그룹별 합계를 구하는 함수
  • AVG(속성명) : 그룹별 평균을 구하는 함수
  • MAX(속성명) : 그룹별 최댓값을 구하는 함수
  • MIN(속성명) : 그룹별 최솟값을 구하는 함수
  • STDDEV(속성명) : 그룹별 표준편차를 구하는 함수
  • VARIANCE(속성명) : 그룹별 분산을 구하는 함수
  • ROLLUP(속성명, 속성명, …) : 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수
  • CUBE (속성명, 속성명, …) : ROLLUP과 유사한 형태지만 CUBE는 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함

WINDOW 함수

  • GROUB BY 절을 사용하지 않고 함수의 인수로 지정한 속성의 값을 집계
  • ROW_NIUMBER() : 윈도우별로 각 레코드에 대한 일련번호를 반환
  • RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 반영
  • DENSE_RANK() : 윈도별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여
-- 상여금 테이블에서 상여내역별로 상여금에 대한 일련 번호 검색
SELECT 상여내역, 상여금 ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO FROM 상여금;
-
-- 상여금 테이블에서 상여내역별로 상여금에 대한 순위 검색
SELECT 상여내역, 상여금 RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 상여금순위 FROM 상여금;

-- 상여금 테이블에서 부서별 상여금의 평균
SELECT 부서, AVG(상여금) AS 평균 FROM 상여금 GROUP BY 부서;

-- 상여금 테이블에서 부서별 튜플 수
SELECT 부서, COUNT(*) AS 사원수 FROM 상여금 GROUP BY 부서;

-- 상여금이 100 이상인 사원이 2명이상인 부서의 튜플의 수
SELECT 부서, COUNT(*) AS 사원수 FROM 상여금 WHERE 상여금 >= 100 GROUP BY 부서 HAVING COUNT(*) >= 2

-- 상여금 테이블의 부서, 상여내역, 상여금에 대해 부서별 상여내역별 소계와 전체 합계 검색 (ROLLUP)
-SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계 FROM 상여금 GROUP BY ROLLUP(부서, 상여내역);

-- 상여금 테이블의 부서, 상여내역, 상여금에 대해 부서별 상여내역별 소계와 전체 합계 검색 (CUBE)
-SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계 FROM 상여금 GROUP BY CUBE(부서, 상여내역);

집합연산자

SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ...
FROM 테이블명
[ORDER BY 속성명 [ASC|DESC];

-- UNION : 두 SELECT문의 조회 결과를 통합하여 모두 출력(중복된 행은 한번만 출력, 합집합)
-- UNION ALL: 두 SELECT문의 조회 결과를 통합하여 모두 출력(중복된 행도 그대로 출력, 합집합)
-- INTERSECT : 두 SELECT 문의 조회 결과 중 공통된 행만 출력 (교집합)
-- EXCEPT : 첫번째 SELECT문의 조회 결과에서 두 번쨰 SELECT 문의 조회 결과를 제외한 행 출력(차집합)

JOIN

  • 연관된 튜플들을 결합하여 하나의 새로운 릴레이션을 반환
  • 크게 INNER JOIN과 OUTER JOIN으로 구분

INNER JOIN

  • 조건 없는 INNER JOIN을 하면 CROSS JOIN과 동일한 결과
  • EQUI JOIN
-- WHERE절을 사용한 경우
SELECT[테이블명1]속성명, [테이블명2]속성2, ...
FROM 테이블명1, 테이블명2 ...
WHERE 테이블명1.속성명 = 테이블명2.속성명;

-- NAUTRAL JOIN을 사용한 경우
SELECT [테이블명1]속성명, [테이블2]속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2;

-- JOIN ~ USING 절을 이용한 경우
SELECT [테이블명1]속성명, [테이블명2]속성명, ...
FROM 테이블명1 JOIN 테이블명2 USING(속성명);
  • NON-EQUI JOIN
-- = 조건이 아닌 >, <, <>, >=, <- 등의 연산자를 사용하는 JOIN
SELECT [테이블명1]속성명, [테이블명2]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE (NON-EQUI JOIN 조건);

-- 학생 테이블과 성적 테이블을 JOIN하여 각 학생의 학번, 이름, 성적, 등급 출력
SELECT 학번, 이름, 성적, 등급
FROM 학생, 성적등급
WHERE 학생.성적 BETWEEN 성적등급.최저 AND 성적등급.최

OUTER JOIN

  • JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN
  • LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
--LEFT OUTER JOIN
SELECT [테이블명1]속성명, [테이블명2]속성명,...
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

SELECT [테이블명1]속성명, [테이블명2]속성명,...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명(+);

--RIGHT OUTER JOIN
SELECT [테이블명1]속성명, [테이블명2]속성명,...
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

SELECT [테이블명1]속성명, [테이블명2]속성명,...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명(+);

--FULL OUTER JOIN
SELECT [테이블명1]속성명, [테이블명2]속성명,...
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

 뒤로 이어지는 내용

https://edder773.tistory.com/202

 

[정보처리기사 실기] 프로시저, 트리거 및 ORM 정리

자격증 준비하면서 내가 이해하기 편하게, 다시 보기 좋게 정리하는 정보처리기사의 내용 (자격증 상세 내용은 아래) http://www.q-net.or.kr/crf005.do?id=crf00505&gSite=Q&gId= http://www.q-net.or.kr/crf005.do?gId=&gSit

edder773.tistory.com

 

반응형
저작자표시

'자격증 > 정보처리기사' 카테고리의 다른 글

[정보처리기사 실기] 소프트웨어 보안과 공격 종류 정리  (0) 2023.04.19
[정보처리기사 실기] 프로시저, 트리거 및 ORM 정리  (0) 2023.04.18
[정보처리기사 실기] 테스트 종류 및 품질 정리  (0) 2023.04.17
[정보처리기사 실기] 사용자 인터페이스(UI) 정리  (0) 2023.04.17
[정보처리기사 실기] 인터페이스 구현 정리  (0) 2023.04.17

관련글

댓글

티스토리툴바