정보처리기능사 준비하는 겸 SQL 문법 정리해서 올려봅니다

데이터베이스 개념은 이전 글을 참조하세요

SQL 개념

SQL(Structured Query Language)은 현재 널리 사용되는 데이터베이스를 조작하는 데 사용할 수 있는 언어이다.

SQL의 분류

• DDL(Data Define Language): SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 조작할 때 사용하는 언어로, 이에 해당하는 명령어로는 CREATE(정의), ALTER(정의 변경), DROP(삭제)이 있다.
• DML(Data Manipulation Language): 테이블에서 튜플을 실질적으로 처리하는 데 사용되는 언어로, 해당하는 명령어로는 SELECT(검색), INSERT(삽입), DELETE(삭제), UPDATE(내용 변경)이 있다.
• DCL(Data Control Language): 데이터의 보안, 무결성, 회복, 권한 제어 등을 정의하는 데 사용되는 언어로, 해당하는 명령어로는 COMMIT, ROLLBACK, GRANT(사용자에게 사용 권한 부여), REVOKE(권한 취소) 등이 있다.

DDL(Data Define Language)

DDL은 DB의 전체적인 구조를 구축하거나 수정하는 데 사용하는 언어이다. 대표적 명령문의 사용법은 다음과 같다.

CREATE

CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_id;

CREATE DOMAIN 도메인명 [AS] 데이터타입
       [DEFAULT 기본값]
       [CONSTRAINT 제약조건명 CHECK (조건)]

CREATE TABLE 테이블명[AS]
       (속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL], ...
       [, PRIMARY KEY(기본키_속성명, ...)]
       [, UNIQUE(대체키_속성명, ...)]
       [, FOREIGN KEY(외래키_속성명, ...) REFERENCES 참조테이블(기본키_속성명, ...)]
       [, CONSTRAINT 제약조건명 CHECK (조건식)];

CREATE VIEW 뷰명[(속성명, ...)]
AS SELECT ... ;

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명[ASC/DESC], ...)
[CLUSTER];

ALTER

ALTER TABLE 테이블명 ADD 속성명 데이터_타입;
ALTER TABLE 테이블명 ALTER 속성명 데이터_타입;
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 제약조건명;

*CASCADE: 제거할 요소를 참조하는 다른 모든 개체를 함께 제거.

*RESTRICT: 다른 개체가 제거할 요소를 참조 중일 때는 제거를 취소.

DCL(Data Control Language)

DCL은 데이터베이스 관리자(DBA)가 데이터 및 권한 관리를 목적으로 사용하는 언어이다.

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: 부여받은 권한을 다른 사용자에게 다시 부여할 권한을 부여함

*CASCADE: 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소함

*권한 종류에는 ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등이 있음

COMMIT / ROLLBACK / SAVEPOINT

COMMIT;

ROLLBACK;

SAVEPOINT [세이브포인트명];
ROLLBACK TO [세이브포인트명];

DML(Data Manipulation Language)

DML은 데이터베이스 사용자가 저장된 데이터를 실질적으로 관리하는 데 사용하는 언어이다.

INSERT INTO~

INSERT INTO 테이블명([속성명1, 속성명2, ...])
VALUES (데이터1, 데이터2, ...);

DELETE FROM~

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

UPDATE~ SET~

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

SELECT

SELECT [PREDICATE] [테이블명.]속성명1 [AS 별칭], ...
FROM 테이블명1, 테이블명2, ...
[WHERE 조건]
[GROUP BY 속성명1, 속성명2, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC/DESC]];

*PREDICATE: 검색할 튜플의 제한 조건. ALL(기본), DISTINCT, DISTINCTROW가 있음.

주요 연산자 및 함수

• = 연산자: 같다
• NOT, AND, OR 연산자: 논리 연산 수행
• LIKE 연산자: '대표 문자'를 이용해 문자열의 문자 패턴이 일치하는 튜플을 검색하기 위해 사용함. %(모든 문자 대표), _(문자 하나를 대표), #(숫자 하나를 대표)
• NOW(), DATE(): 각각 현재 날짜와 시간, 현재 날짜를 표시
• LEFT(문자열, 자릿수), MID(문자열, 시작값, 자릿수), RIGHT(문자열, 자릿수): 각각 시작 위치에서 주어진 자릿수만큼 추출
• TRIM(문자열): 문자열의 좌우 공백 제거
• LEN(문자열): 문자열의 길이 반환
• UPPER(문자열), LOWER(문자열): 각각 대문자, 소문자로 변환해 반환
• AVG(필드명), SUM(필드명), COUNT(필드명), MIN(필드명), MAX(필드명), 각각 평균, 합, 레코드 수, 최소값, 최대값 구함
• UNION, UNION ALL, INTERSECT, EXCEPT 연산자: 두 SELECT문의 통합 질의에서 사용되는 집합 연산자로 각각 합집합, 중복을 포함한 합집합, 교집합, 차집합을 구함

(SELECT문 예시)

SELECT 기본급+10 AS 월급
FROM 사원
WHERE 부서='기획' aND 기본급 > 150;

SELECT TOP 2 *
FROM 사원
WHERE 이름 LIKE '김%'
ORDER BY 주소 DESC;

SELECT 부서, COUNT(*) AS 사원수
FROM 사원
GROUP BY 부서;

SELECT *
FROM 사원1
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동 WHERE 취미='테니스')
UNION
SELECT *
FROM 사원2
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동 WHERE 취미='테니스');

SELECT - JOIN

Join이란 2개에 테이블에 대해 연관된 튜플들을 결합하여 하나의 새로운 릴레이션을 얻는 것을 말한다.

INNER JOIN - 조인된 필드가 일치하는 행들만 표시

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

OUTER JOIN - 조인된 필드가 일치하지 않는 행들도 필드 값 대신 NULL을 넣어 결과로 출력

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

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

Reference

• 2024 시나공 정보처리기능사 실기 기본서. 길벗