이 글은 이기적 정보처리기능사 실기 기본서를 기반으로 제작되었습니다.

소개

저번 데이터베이스 챕터에 이어서, 이번엔 Part.4 SQL 활용에 대해 배워보도록 합시다!

1. DDL

1. DDL의 개념

1) DDL 정의

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

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

2) DDL 유형

구분	DDL 명령어	내용
생성	CREATE	데이터베이스 오브젝트 생성
변경	ALTER	데이터베이스 오브젝트 변경
삭제	DROP	데이터베이스 오브젝트 삭제
삭제	TRUNCATE	데이트베이스 오브젝트 내용 삭제, 테이블 구조는 유지

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 열이름 데이터 타입 [DEFAILT 깂]
열 데이터 타입 변경	ALTER TABLE 테이블이름 MODIFY 열이름 데이터 타입 [DEFAILT 깂]
열 삭제	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을 사용하여 테이블에 제약조건을 추가하거나 삭제할 수는 있으나 수정은 불가능하다.

  내용	SQL 명령문
  제약조건 추가	ALTER TABLE 테이블이름 ADD [CONSTRAINT 제약조건이름] 제약조건(열이름)
  제약조건 삭제	ALTER TABLE 테이블이름 DROP CONSTRAINT 제약조건이름
  제약조건 활성화	ALTER TABLE 테이블 이름 ENABLE CONSTRAINT 제약조건이름
  제약조건 비활성화	ALTER TABLE 테이블 이름 DISABLE CONSTRAINT 제약조건이름

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

• 실제로 SQL중 DDL 부분을 실습해보자!
• 앞서 데이터베이스 부분에서 다뤘던 보험사 ERD를 토대로 한번 만들어보자!

1) 테이블 생성 : CREATE

• 먼저 테이블을 생성해보자.
• 여기서 꿀팁은 받은 설계도를 메모장을 켜서 이름과 데이터타입으로 정리하는 것이다.
  
• 그리고, 기본키와 제약 조건을 정리해보자.
  
• 이것을 토대로 테이블을 만들어보자.
• Mysql에서는 Varchar2 데이터 타입이 없다. 그래서 Varchar로 만들어줬다.
  
• 아래에 결과값이 나와있다. Client, Address, Account 순이다.
  

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

• Address의 Address1을 Address_Main으로 변경하시오.

  ALTER TABLE Address rename column Address1 to Address_Main;

• Client에 Social_S_No를 가변 문자형으로 표현하고, Null값을 허용하지 못하도록 변경하시오,

  ALTER TABLE Client modify Social_S_No Varchar(20) NOT NULL;

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

• Client 테이블을 삭제하시오.

  DROP TABLE Client CASCADE;

• Client 테이블에서 Phone 컬럼만 삭제하시오.

  ALTER TABLE Client DROP COLUMN Phone

  DROP 문에서 쓰이는 기타 SQL 명령어들
  RESTRICT : 삭제할 테이블이 참조 중리면 삭제하지 않는다.
  CASCADE : 삭제할 테이블이 참조 중이여도 삭제한다.
  CASCADE로 삭제를 하게 되면, 삭제할 요소와 참조된 모든 요소에 대해서 연쇄적으로 같이 삭제된다.

2. DML

1. DML의 개념

1) DML 정의

• 데이터를 조작하는 명령어를 DML(Data Manipulation Language)라고 한다.
• 여기에서 조작이란, 데이터 관점에서 생명 주기를 제어하는 것을 의미한다.
• 우리가 흔히 알고있는 CRUD도 여기에 속한다.

  CRUD : Create, Read, Update, Delete의 약자이다.
  Create = INSERT, Read = SELECT. Update = UPDATE, Delete = DELETE

2) DML 유형

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

2. DML 활용

1) 데이터 조회

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

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

• SELECT 문에서 사용되는 요소
  

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

2) 데이터 삭제

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

  DELETE FROM table [WHERE 절]

3) 테이터 변경

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

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

4) 데이터 추가

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

  INSERT INTO table_name (column1, column2, ...) VALUES (Value1, Value2, ...)

  ---

  INSERT INTO table_name VALUES (Value1, Value2, ...)

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

1) 데이터 추가 : INSERT

• 전에 만들었던 Client 테이블에 임의에 유저 데이터를 넣어보자.
• ( Address_Code는 필요가 없는 칼럼이라 제외했다. )
• ( phone와 Social_S_No는 int에서 Varchar(20)으로 바꿔주었다.)
• ID는 1, 이름은 홍길동, phone은 010-1234-5678, Social_S_No는 751204-1234567로 넣어보자.
• ID는 2, 이름은 도제후, phone은 010-2345-6789, Social_S_No는 041230-7654321로 넣어보자.
  

2) 데이터 조회 : SELECT

• 아무 조건 없이 Client 테이블의 모든 컬럼을 조회하시오.
  

• Client에서 Client_ID가 1인 유저를 조회하시오.
  

추가로 알아둬야 하는 SELECT 명령어들!
- 학생 테이블에서 중복을 제외한 동아리명 컬럼을 조회하시오.
 SELECT DISTINCT 동아리명 FROM 학생;
- 학생 테이블에서 학년이 3학년, 과목이 영어인 학생의 성명과 연락처를 조회하시오.
 SELECT 성명, 연락처 FROM 학생 WHERE 학년 = 3 AND 과목 = '영어';

3) 데이터 변경 : UPDATE

• Client_ID가 1인 사람에 전화번호를 010-9999-8988로 변경하시오.
  

4) 데이터 삭제 : DELETE

• Client 테이블에서 전체 행을 제거하시오.
  

추가로 알아줘야 하는 DELETE 명령어
- 학생 테이블에서 학년이 3학년인 데이터를 삭제하시오.
  DELETE FROM '학생' WHERE 학년 = 3;

3. DCL

1. DCL의 개념

1) DCL 정의

• 데이터베이스에서 데이터 이외의 오브젝트에 대해 조작할 필요가 있다.
• 이때 사용하는 SQL 명령을 DCL(Data Control Language)이라고 한다.
• DCL의 조작 대상, 오브젝트 유형은 다음과 같다.

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

2) DCL 유형

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

2. DCL의 활용

1) 사용자 권한 부여

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

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

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

2) 사용자 권한 회수

• GRANT에 대응하는 권한 회수 명령은 REVOKE이다.

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

3. 접근 통제

1) 접근 통제 개념

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

2) 접근 통제 유형

3) 접근 통제와 DCL의 관계

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

4. DCL 활용 방법

1) 트랜잭션 개념

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

2) 트랜잭션 제어

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

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

4. 데이터 사전 검색

1. 데이터 사전의 개념

• 데이터 사전?
  • 데이터베이스의 데이터를 제외한 모든 정보가 있다.
  • 이 데이터 사전은 메타 데이터(Meta Data)로 구성되어 있다.

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

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

2. 데이터 사전의 내용

• 데이터 사전 안에 존재하는 메타 데이터의 유형은 다음과 같다.
  • 사용자 정보( ID, PW 및 권한 등 )
  • DB 객체 정보( 테이블, 뷰, 인덱스 등 )
  • 무결성 제약 정보
  • 함수, 프로시저 및 트리거 등
• 데이터 사전 내용이 메타 데이터라는 것은 모든 DBMS 제품에 공통이다.
• 하지만 데이터 사전을 구현하고 관리하는 방법 등의 차이로 메타데이터의 구체적인 내용은 제품마다 다르다.

3. 데이터 사전의 용도

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

마치며

네! SQL 활용 중 기본 SQL에 대해 배워 보았습니다!
다음 시간에는 뷰, 인덱스등, SQL에 필요한 고오급 기술에 대해 배워보도록 합시다!

기본 SQL은 분식집이고, 고급 SQL은 고오급 레스토랑이죠 ㅎ
그럼 다음 시간에 다시 뵙도록 하죠!