[정보처리기사]3.데이터베이스 구축/SQL응용/SQL :DDL, DCL, DML

SQL : DDL, DCL, DML 

[...] 생략가능을 의미
<...> 객체명 

1. SQL(Structured Query Language)

- 1974년 IBM 연구소에서 개발한 SEQUEL에서 유래함

- 관계대수와 관계해석을 기초로 한 혼합 데이터 언어

1) SQL의 분류  20년 1, 2회 기출문제

1.1) DDL(Data Define Language, 데이터 정의어) #ACD(Alter, Create, Drop)

- 정의:  논리적 & 물리적 구조 정의 ,수정, 제약조건, 데이터간의 관계, 데이터 연 산 명세

- DOMAIN, SCHEMA, TABLE, VIEW, INDEX를 정의(CREATE),변경(ALTER), 삭제(DROP)할 때 사용하는 언어

- DDL 결과 정보는 데이터 사전(DD)에 저장

(1) 정의

CREATE DOMAIN, SCHEMA, TABLE, VIEW, INDEX 생성

CREATE DOMAIN <도메인 이름> ( 데이터 타입 제한 조건 ) ;
CREATE SCHEMA <스키마 이름> [ AUTHORIZATION <스키마 소유자명> ];
CREATE TABLE <테이블 이름> (컬럼1 데이터 타입, 컬럼2 데이터 타입, ..., 컬럼N 데이터 타입);
CREATE VIEW <뷰 이름> SELECT .. FROM ,,, [WHERE ...]; 
CREATE [UNIQUE] INDEX <인덱스 이름> ON <테이블 이름> (컬럼1 데이터 타입, ..., 컬럼N 데이터 타입);

-- Person이라는 테이블 생성하는데 PerSonID는 정수혀 타입, 나머지 컬럼은 varchar(255)타입으로 생성
CREATE TABLE Person (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

(2) 삭제

DROP DOMAIN, SCHEMA, TABLE, VIEW, INDEX 삭제

DROP DOMAIN <이름>;
DROP SCHEMA <이름>;
DROP TABLE <이름>;
DROP VIEW <이름>; 
DROP INDEX <이름>;

 

(3) 테이블 변경

테이블의 컬럼을 추가, 제거, 변경에 사용

ALTER TABLE <테이블명>
ADD <컬럼명> <데이터 타입>;

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

ALTER TABLE <테이블명>
MODIFY COLUMN <컬럼명> <데이터 타입>;

-- e.g. Person 테이블에 DateOfBirth 컬럼명을 갖고 데이터 타입은 date 인 컬럼을 추가하여 변경한다.
ALTER TABLE Person
ADD DateOfBirth date;

(4) FK 제한 옵션

* CASCADE: 변경/제거 시  참조하는 모든 개체 함께 제거/업데이트

*RESTRICTED: 다른 개체가 제거할 요소를 참조 중이면 제거/업데이트 취소

-- 출처:MySQL https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT   //참조 중인 경우 업데이트 시 함께 업데이트, 제거시 제거 취소

1.2) DML(Data Manipulation Language, 데이터 조작어) 기출 1, 2회 #SELECT, INSERT, DELTE, UPDATE

실기에서 대부분 DML에 관해서 출제된다.
DML의 Update와 DDL의 Alter 를 섞어 자주 출제된다.

- 조작: 삽입, 검색(요청), 갱신(변경) , 삭제의 연산을 위한 사용자와 DB 사이의 인터페이스 수단 제공

- DB 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 처리하는 데 사용하는 언어

(1) SELECT 테이블에서 조건에 맞는 튜플 조회

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

(2) INSERT 테이블에 새로운 튜플 삽입

INSERT INTO 테이블명 VALUES (값1, 값2 ..., 값);

(3) DELETE 테이블에서 조건에 맞는 튜플 삭제 

DROP 과 헷갈리지 않도록 함

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

(4) UPDATE  테이블에서 조건에 맞는 튜플의 내용 갱신(변경)  21년 2회 실기

UPDATE ~SET

UPDATE 테이블명 SET 속성명=데이터 [WHERE 조건];

 

1.3) DCL(Data Control Language, 데이터 제어어) 20년 1, 2, 3회 기출문제

-데이터의 무결성, 보안, 회복, 권한, 병행수행 제어 정의하는 데 사용되는 언어 -> 공용

-데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용

(1.3.1) 트랜잭션

(a) COMMIT

명령 수행된 결과를 실제 물리적 디스크로 저장, 데이터베이스 조작 작업이 정상적으로 완료됐음을 알려주는 명령어

CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
INSERT INTO customer VALUES (10, 'Heikki');
COMMIT;

 

(b)ROLLBACK

아직 COMMIT되지 않은 변경된 모든 내용들을 취소, 데이터베이스를 이전 상태로 되돌리는 명령어

-- 위에 이어서
INSERT INTO customer VALUES (15, 'John');
INSERT INTO customer VALUES (20, 'Paul');
DELETE FROM customer WHERE b = 'Heikki';

ROLLBACK;

SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Heikki |
+------+--------+
-- 출처:https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html

(c) SAVEPOINT

트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier

(1.3.2) 권한 21년 3회 실기 기출

(a)GRANT 데이터베이스 사용자에게 사용 권한 부여

GRANT ~ ON ~ TO

GRANT 권한 리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];

GRANT UPDATE ON 고객(테이블) TO 존 WITH GRANT OPTION;

*WITH GRANT OPTION: 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한

 

(b) REVOKE 데이터베이스 사용자의 사용 권한 취소

REVOKE ~ ON ~

REVOKE [GRANT OPTION FOR] 권한 리스트 ON 개체 FROM 사용자 [CASCADE];

REVOKE GRANT OPTION FOR UPDATE ON 고객(테이블) FROM 존 CASCADE;

*GRANT OPTION FOR: 다른 사용자에게 권한을 부여할 수 있는 권한을 취소

 

 

[정보처리기사] 정보처리기사 필기 목차

[정보처리기사] 개정된 정보처리기사 필기 목차