Chapter 1 : 기본 SQL

---

🍀 Section 1 | DDL

1. DDL(데이터 정의어)의 개념

1) DDL 정의

• Data Definition Language
• 데이터를 정의하는 언어 = 데이터를 담는 그릇을 정의하는 언어
• 이러한 그릇을 DBMS에서는 오브젝트라고 한다.
• DDL 을 통해 정의할 수 있는 대상, 오브젝트의 유형은 다음과 같다.

DDL 대상	설명
스키마(Schema)	• DBMS 특성과 구현 환경을 감안한 데이터 구조
	• 직관적으로 하나의 DB로 이해 가능
도메인(Domain)	• 속성의 데이터 타입과 크기, 제약 조건 등을 지정한 정보
	• 속성이 가질 수 있는 값의 범위로 이해 가능
테이블(Table)	• 데이터 저장 공간
뷰(View)	• 하나 이상의 물리 테이블에서 유도되는 가상의 논리 테이블
인덱스(Index)	• 검색을 빠르게 하기 위한 데이터 구조

2) DDL 유형

구분	DDL 명령어	내용
생성	CREATE	• DB 오브젝트 생성
수정	ALTER	• DB 오브젝트 수정
삭제	DROP	• DB 오브젝트 삭제
	TRUNCATE	• DB 오브젝트 내용 삭제, 테이블 구조는 유지

💡 TRUNCATE

• DELETE와 같이 데이터를 삭제하는 것은 동일하다.
• 하지만, 테이블 구조를 유지하므로 DDL에 포함된다.

2. DDL(데이터 정의어)의 활용

1) 테이블 생성

-- 신규 생성
CREATE TABLE 테이블이름 (
  열이름 데이터 타입 [DEFAULT 값] [NOT NULL]
  [PRIMARY KEY (열 리스트)]
  {
    [
      FOREIGN KEY(열 리스트) REFERENCES 테이블이름 [(열이름)]
      [ON DELETE 옵션]
      [ON UPDATE 옵션]
    ]
  }
  [CHECK (조건식) | UNIQUE(열이름)]
);

-- 다른 테이블 정보를 이용한 테이블 생성
CREATE TABLE 테이블이름 AS SELECT 문;

2) 테이블 변경

-- 열 추가
ALTER TABLE 테이블이름 ADD 열이름 데이터 타입 [DEFAULT 값]

-- 열 데이터 타입 변경
ALTER TABLE 테이블이름 MODIFY 열이름 데이터 타입 [DEFAULT 값]

-- 열 삭제
ALTER TABLE 테이블이름 DROP 열이름

3) 테이블 삭제, 내용 삭제, 이름 변경

-- 테이블 삭제
DROP TABLE 테이블이름

-- 테이블 내용 삭제
TRUNCATE TABLE 테이블이름

-- 테이블 이름 변경
RENAME TABLE 이전 테이블이름 TO 새로운 테이블이름
ALTER TABLE 이전 테이블이름 RENAME 새로운 테이블이름

4) 데이터 타입

유형	정의
CHAR	고정 길이 문자열 데이터 타입
VARCHAR	가변 길이 문자열 데이터 타입
INT	숫자에 사용되는 데이터 타입
FLOAT	소수형 데이터 타입
DATE	날짜에 사용되는 데이터 타입

3. 제약조건 적용

1) 제약조건 유형

제약조건	설명
PRIMARY KEY	• 테이블의 기본키를 정의함
	• 기본으로 NOT NULL, UNIQUE 제약이 포함됨
FOREIGN KEY	• 외래키를 정의함
	• 참조 대상을 테이블 이름(열 이름)으로 명시해야 함
	• 참조 무결성 위배 상황 발생 시 처리 방법으로 옵션 지정 가능
	• NO ACTION, SET DEFAULT, SET NULL, CASCADE, RESTRICT
UNIQUE	• 테이블 내에서 열은 유일한 값을 가져야함
	• 테이블 내에서 동일한 값을 가져서는 안 되는 항목에 지정함
NOT NULL	• 테이블 내에서 관련 열의 값은 NULL일 수 없음
	• 필수 입력 항목에 대해 제약조건으로 설정함
CHECK	• 개발자가 정의하는 제약조건
	• 상황에 따라 다양한 조건 설정 가능

2) 제약조건 변경

-- 제약조건 추가
ALTER TABLE 테이블이름
ADD [CONSTRAINT 제약조건이름] 제약조건(열이름)

-- 제약조건 삭제
ALTER TABLE 테이블이름 `주문테이블` DROP FOREIGN KEY [제약조건이름];

-- 제약조건 활성화
ALTER TABLE 테이블이름
ENABLE CONSTRAINT 제약조건이름

-- 제약조건 비황설화
ALTER TABLE 테이블이름
DISABLE CONSTRAINT 제약조건이름

💡 ALTER TABLE

• 테이블에 제약조건을 추가하거나 삭제할 수 있다.
• 테이블에 제약조건을 수정 불가능하다

4. 예제로 알아보는 SQL - DDL

1) 테이블 생성 : CREATE

• 급여라는 테이블을 생성하며 길이에 상관 없는 문자열로 ID와 고객명 컬럼을 설정하고, 나이는 숫자, 입사연도는 날짜로 정의하시오.
• 기본키는 ID로 설정하고, ID와 입사연도는 공백을 허용하지 않으며, 입사연도가 입력되지 않을 시 2020으로 입력되도록 기본값을 설정하시오.

CREATE TABLE 급여 (
  ID VARCHAR(20) NOT NULL
  고객명 VARCHAR(20)
  나이 INT
  입사연도 DATE NOT NULL DEFAULT 2020
  PRIMARY KEY(ID)
)

2) 컬럼 이름 및 타입 변경 : ALTER

-- 주문 테이블의 배송지를 배송지역으로 변경하시오.
ALTER TABLE `주문` CHANGE `배송지` `배송지역`;

-- 주문 테이블의 상품 데이터 형식을 가변 문자형으로 표현하고 null값을 허용하지 않도록 하시오
ALTER TABLE `주문` ALTER COLUMN `상품` VARCHAR(20) NOT NULL;

3) 테이블 또는 컬럼 삭제 : DROP

-- 급여 테이블을 삭제하시오.
DROP TABLE `급여`;

-- 급여 테이블에서 부서 컬럼만 삭제하시오.
ALTER TABLE `급여` DROP COLUMN `부서`;

💡 DROP 문에서 쓰이는 기타 SQL 명령어들

• RESTRICT
  • 삭제할 테이블(데이터)가 참조 중이면삭제하지 않는다.
• CASCADE
  • 삭제할 테이블(데이터)가 참조 중이라도 삭제를 하게 되면, 삭제할 요소와 참조된 모든 요소에 대해서 연쇄적으로 같이 삭제된다.

🚀 예상문제

1) 데이터 정의 언어(DDL, Data Define Language)에서 테이블 구조는 유지하며 테이블 내용을 제고하는 명령어를 작성하시오.

• 답 : TRUNCATE

2) 아래 보기에는 영진 테이블의 기본키를 삭제하는 SQL문이다. 빈칸에 들어갈 알맞은 SQL을 모두 작성하시오.

( ① ) TABLE 영진 ( ② ) PRIMARY KEY

• 답 :
  • ① : ALTER
  • ② : DROP

---

🍀 Section 2 | DML

1. DML(데이터 조작어)의 개념

1) DML 정의

• Data Manipulation Language
• 여기서 조작이란, 데이터 관점에서 생명 주기를 제어하는 것

2) DML 유형

구분	DML 명령어	내용
데이터 조회	SELECT	• 테이블의 내용을 조회
데이터 변경	UPDATE	• 테이블의 내용을 변경
데이터 추가	INSERT	• 테이블의 내용을 추가
데이터 삭제	DELETE	• 테이블의 내용을 삭제

2. DML(데이터 조작어)의 활용

1) 데이터 조회

• 데이터 내용을 조회할 때 사용하는 명령어
• 가장 많이 사용되는 SQL명령어
• 다른 DML 명령어와 같이 사용되어 SQL의 활용을 풍부하게 한다.

💡SELECT 명령어의 기본 형식

SELECT [OPTION] columns FROM table [WHERE 절];

[SELECT 문에서 사용되는 요소]

요소	요소값	내용
OPTION	ALL	• 중복을 포함한 조회 결과 출력
	DISTINCT	• 중복을 제외한 조회 결과 출력
columns	컬럼명 목록	• SELECT를 통해 조회할 컬럼명 지정
	와일드카드	• 모두 또는 전체를 의미하는 '*'

💡 SELECT 문에서 쓰이는 기타 SQL 명령어들

• DISTINCT
  • 중복된 값을 한 번만 검색되도록 한다.
• BETWEEN
  • A AND B, 즉 A값과 B값 사이를 만족하는 부분을 검색한다.
• IN
  • IN(A, B), OR과 동일하게 참조하는 부분 중 하나라도 만족하는 부분을 검색한다.
• ORDER BY
  • 오름차순은 ASC, 내림차순은 DESC를 사용하여 정렬한다.
• HAVING
  • GROUP BY 에 의해 그룹으로 분류된 부분에서 WHERE 대신 조건절을 대신한다.

2) 데이터 변경

• 데이터를 변경 및 수정할 때 다음과 같은 형태의 UPDATE 명령문을 사용한다.
• UPDATE 명령문은 보통 WHERE 절을 통해 어떤 조건을 만족할 경우에만 특정 컬럼의 값을 수정하는 용도로 많이 사용된다.

UPDATE table SET column1 = value1, column2 = value2, ... [WHERE 절];

3) 데이터 추가

• 데이터를 삽입하기 위한 명령어로 다음과 같이 두 가지 형태의 명령문 형식을 제공한다.
• 이때 데이터 삽입 결과로 하나의 레코드가 추가된다.
• 따라서 삽입에 사용되는 정보는 하나의 레코드를 충분히 묘사해야 한다.

INSERT INTO table_name (column1, column2, ..) VALUES (value1, value2, ...);

INSERT INTO table_name VALUES (value1, value2, ...);

4) 데이터 삭제

• 레코드를 삭제할 때 다음과 같은 형태의 DELETE 명령문을 사용한다.
• 조건절 없이 DELETE를 사용하는 경우, 테이블 전체가 한 번에 삭제된다.

DELTE FROM table [WHERE 절];

3. 예제로 알아보는 SQL - DML

1) 데이터 조회 : SELECT

-- 아무 조건 없이 학생 테이블의 모든 컬럼을 조회하시오.
SELECT * FROM 학생;

-- 학생 테이블에서 학년이 2학년인 모든 컬럼을 조회하시오.
SELECT * FROM 학생 WHERE 학년 = 2;

-- 학생 테이블에서 중복을 제외한 동아리명 컬럼을 조회하시오.
SELECT DISTINCT 동아리명 FROM 학생

-- 학생 테이블에서 학년이 3학년이고, 과목이 영어인 학생의 성명과 연락처를 조회하시오.
SELECT 성명, 연락처 FROM 학생 WHERE 학년 = 3 AND 과목 = 영어;

2) 데이터 수정 : UPDATE

-- 판매내역 테이블에 재고가 없으면 상태를 '판매불가'라고 수정하시오.
UPDATE '판매내역' SET 상태 = '판매불가' FROM 재고 IS NULL;

3) 데이터 추가 : INSERT

-- 회원 테이블에서 회원번호 1112, 성명 '윤지영', 지역 '인천', 연락처 '486-1112'인 회원을 추가하시오.
INSERT INTO 회원(회원번호, 성명, 지역, 연락처)
VALUES (1112, '윤지영', '인천', '486-1112');

4) 데이터 삭제 : DELETE

-- 학생 테이블에서 전체 행을 제거하시오
DELETE FROM '학생';

-- 학생 테이블에서 학년이 3학년인 데이터를 삭제하시오.
DELETE FROM '학생' WHERE 학년 = 3;

🚀 예상문제

1) 다음 학생 테이블에서 수학이 80점 이하인 학생이 3명 이상인 반의 튜플 수를 구하는 SQL문이다. 빈칸에 들어갈 명령어를 작성하시오.

SELECT 반, COUNT(*) AS 학생수
FROM 학생
WHERE 수학 <= 80
GROUP BY 반
(      ) COUNT(*) >= 3;

• 답 : HAVING

2) PATH 테이블에서 점수 열을 내림차순으로 검색하려고 한다. 빈칸에 들어갈 알맞은 SQL을 작성하시오.

SELECT * FROM ORDER BY 점수 (      )

• 답 : DESC

---

🍀 Section 3 | DCL

1. DML(데이터 조작어)의 개념

1) DML 정의

• Data Control Language
• 데이터 이외의 오브젝트에 대해 조작할 때

오브젝트	목적	내용
사용자 권한	접근 통제	• 사용자를 등록하고, 사용자에게 특정 DB를 사용할 수 있는 권리를 부여하는 작업
트랜잭션	안전한 거래 보장	• 동시에 다수의 작업을 독립적으로 안전하게 처리하기 위한 상호작용 단위

2) DCL 유형

구분	DML 명령어	내용
DCL	GRANT	• DB 사용자 권한 부여
	REVOKE	• DB 사용자 권한 회수
TCL	COMMIT	• 트랜잭션 확정
	ROLLBACK	• 트랜잭션 취소
	CHECKPOINT	• 트랜잭션 설정

💡 TCL

• Transaction Control Language
• 트랜잭션 제어를 위한 명령어
• TCL과 DCL은 대상이 달라 서로 별개의 개념으로 분류할 수 있으나, 제어 기능이라는 공통점으로 DCL의 일부로 분류하기도 한다.

2. DCL의 활용

1) 사용자 권한 부여

• 권한은 시스템 권한과 객체 권한으로 분류한다.
• 각 권한을 부여하기 위한 명령어 사용법은 다음과 같다.

권한	명령어 문법
시스템 권한 부여	GRANT 권한1, 권한2 TO 사용자계정
객체 권한 부여	GRANT 권한1, 권한2 ON 객체명 TO 사용자계정

• 시스템 권한과 객체 권한의 종류는 다음과 같다.

-- 시스템 권한

CREATE USER       // 계정 생성 권한
DROP USER         // 계정 삭제 권한

CREATE TABLE      // 테이블 생성 권한
DROP ANY TABLE    // 테이블 삭제 권한

CREATE SESSION    // DB 접속 권한
CREATE VIEW       // 뷰 생성 권한
CREATE SEQUENCE   // 시퀀스 생성 권한
CREATE PROCEDURE  // 함수 생성 권한

-- 객체 권한

ALTER    // 테이블 변경 권한

INSERT   // 데이터 조작 권한
DELETE   // 데이터 조작 권한
SELECT   // 데이터 조작 권한
UPDATE   // 데이터 조작 권한

EXECUTE ON PROCEDURE  //  실행 권한

2) 사용자 권한 회수

• GRANT에 대응하는 권한 회수 명령은 REVOKE이다.
• 권한 유형별 대응하는 명령어 구조는 다음과 같다.

권한	명령어 문법
시스템 권한 회수	REVOKE 권한1, 권한2 TO 사용자계정
객체 권한 회수	REVOKE 권한1, 권한2 ON 객체명 FROM 사용자계정

3. 접근 통제

1) 접근 통제 개념

• DB의 보안을 구현하는 방법으로 접근 통제 방법을 사용한다.

💡 접근 통제

• 보안 정책에 따라 접근 객체에 접근 주체의 접근 권한을 확인
  • 접근 객체 : 시스템 자원, 통신자원 등
  • 접근 권한 : 사용자, 프로세스 등
• 이를 기반으로 한 접근 제어를 통해 자원에 대한 비인가된 사용을 방지하는 정보 보호 기능

2) 접근 통제 유형

💡 DAC

• Discretionary Access Control, 임의 접근 통제
• 시스템 객체에 대한 접근을 사용자 개인 또는 그룹의 식별자를 기반으로 제한하는 방법
• 임의적이라는 말은,
  • 어떤 종류의 접근 권한을 갖는 사용자는 다른 사용자에게
  • 자신의 판단에 의해서 권한을 줄 수 있다는 것
• 주체와 객체의 신분 및 임의적 접근 통제 규칙에 기초하여 객체에 대한 주체의 접근을 통제하는 기능
• 통제 권한이 주체에 있음
• 주체가 임의적으로 접근 통제 권한을 배분하며 제어할 수 있음

💡 MAC

• Mandatory Access Control, 강제 접근 통제
• 정보 시스템 내에서 어떤 주체가 특정 객체에 접근하려 할 때,
• 양쪽의 보안 레이블(Secuity Label)에 기초하여,
• 높은 보안 수준을 요구하는 정보(객체)가,
• 낮은 보안 수준의 주체에게 노출되지 않도록
• 접근을 제한하는 통제 방법

3) 접근 통제와 DCL의 관계

• 강제 접근 통제의 경우, 제 3자의 종류에 따라, 보다 세분화 된 정책이 존재한다.
• 접근 통제 정책의 두 가지 가운데 DBMS에서 채택한 접근 통제 정책은 DAC 방식이다.
• DB 관리, 특히 접근 용도로 SQL에서 사용하는 명령어가 바로 DCL이다.

4. TCL 활용 방법

1) 트랜잭션 개념

• Transaction = 일 처리 단위
• 보다 다양한 관점에서 트랜잭션은 다음과 같은 모습을 가진다.
  • Transaction = 논리적 연산 단위
  • Transaction = 한 개 이상의 DB 조작
    • 즉, 하나 이상의 SQL 문장이 포함된다.
  • Transaction = 분할할 수 없는 최소 단위
  • Transaction = 거래
    • 이때 거래 결과가 모두 반영되거나 모두 취소되어야 한다.
    • DB에서의 트랜잭션은 특별한, 엄격한 거래를 의미한다.

2) 트랜잭션 제어

• 트랜잭션을 제어한다는 것
  • 흐름의 구조를 바꾼다는 것이 아님
  • 트랜잭션의 결과를 수용하거나 취소하는 것
• 이러한 작업을 수행하는 TCL 관련 명령어는 다음과 같다.

명령어	내용	비고
COMMIT	트랜잭션을 확정함
ROLLBACK	트랜잭션을 취소함
CHECKPOINT	저장점 설정	ROLLBACK할 위치를 지정함

🚀 예상문제

1) 사용자 PATH에게 계정 생성 권한(CREATE USER)을 부여하는 SQL문을 작성하시오.

• 답 : GRANT CREATE USER TO PATH

2) 사용자 PATH에게 점수 테이블의 갱신(UPDATE)과 삭제(DELETE) 권한을 해제하는 SQL문을 작성하시오.

• 답 : REVOKE UPDATE, DELETE ON 점수 FROM PATH

---

🍀 Section 4 | 데이터 사전 검색

1. 데이터 사전의 개념

• Data Dictionary
• DB의 데이터를 제외한 모든 정보가 있다.

💡 데이터를 제외한 모든 정보?

• 데이터 구조와 정의 정보를 의미한다.

  -- EX)
  -- 데이터 : 실제 값
  학생 테이블
  이름 = "재재", 나이 = 200
  -- 메타 데이터
  테이블 이름 :학생
  컬럼 : 이름(VARCHAR), 나이(INT)
  기본키 : 학번

• 데이터 사전은 메타 데이터(Meta Data)로 구성되어 있다고 할 수 있다.

💡 Meta Data

• 데이터의 데이터, 즉 어떠한 목적을 가지고 만들어진 데이터
• 데이터에 관해 구조화 된 데이터로, 다른 데이터를 설명해주는 데이터라고 볼 수 있다.

• 데이터 사전의 내용을 변경하는 권한은 시스템이 가진다.
• 사용자에게는 읽기 전용 테이블 형태로 제공되므로 단순 조회만 가능하다.

2. 데이터 사전의 내용

💡 데이터 사전 안에 존재하는 메타 데이터의 유형

• 사용자 정보
  • 아이디, 패스워드 및 권한 등
• DB 객체 정보
  • Table, View, Index 등
• 무결성 제약 정보
• 함수, 프로시저 및 트리거 등

• 데이터 사전 내용이 메타 데이터라는 것은 모든 DBMS 제품에 공통이다.
• 데이터 사전을 구현하고 관리하는 방법 등의 차이로 메타 데이터의 구체적인 내용은 제품마다 다르다.

3. 데이터 사전의 용도

• 사용자에게 데이터 사전은 단순 조회의 대상일 뿐이다.
• DB 엔진을 이루는 컴파일러, 옵티마이저 등과 같은 구성요소에게 데이터 사전은 작업을 수행하는데 필요한 참조 정보일 뿐만 아니라 작업의 대상이기도 한다.

🚀 예상문제

1) 다음에서 설명하는 정의에 대한 용어를 작성하시오

데이터를 제외한 데이터를 구성하는 정보 또는 '데이터의 데이터'를 말하며 어떠한 목적을 가지고 만들어진 데이터

• 답 : 메타 데이터(Meta Data)