정보처리기사 - 8. SQL응용

1.SQL - DDL

DDL

• DB를 구축하거나 수정할 목적으로 사용하는 언어
• CREATE : SHCEMA, DOMAIN, TABLE, VIEW, INDEX를 정의
• ATLER : TABLE에 대한 정의를 변경하는데 사용
• DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제

 

CREATE DOMAIN

• 도메인을 정의하는 명령문
• 표기형식

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

• '성별'을 '남' 또는 '여'와 같이 정해진 1개의 문자로 표현되는 도메인 SEX를 정의하는 SQL문 작성
•  

CREATE DOMAIN SEX CHAR(1)
DEFAULT '남'
CONSTRAINT VALID-SEX CHECK(VALUE IN('남', '여'));

 

CREATE TABLE

• 테이블을 정의하는 명령문
• ex)  '이름', '학번', '전공', '성별', '생년월일'로 구성된 <학생>테이블을 정의하는 SQL문 
• 제약 조건
  • 이름은 NULL이 올 수 없고, '학번'이 기본키 
  • 전공은 <학과> 테이블의 '학콰코드'를 참조하는 외래키로 사용
  • <학과> 테이블에서 삭제가 일어나면, 관련된 튜플들의 전공 값을 NULL로 만든다
  • <학과>테이블에서 '학과코드'가 변경되면, 전공값도 같은 값으로 변경한다 
  • '생년월일'은 1980-01-01 이후의 데이터만 저장 할 수 있다.
  • 제약 조건의 이름은 '생년월일제약'으로 한다.
  • 각 속성의 데이터 타입은 적당하게 지정한다
    • 단 성별은 도메인 'SEX'를 사용한다.

CREATE TABLE 학생
	이름 VARCHAR(15) NOT NULL,
    학번 CHAR(8) PRIMARY KEY,
    전공 CHAR(5),
    성별 SEX,
    생년월일 DATE,
    FOREIGN KEY(전공)
    	REFERENCES 학과(학과코드)
        ON DELETE SET NULL
        ON UPDATE CASCADE
    CONSTRAINT 생년월일제약
    	CHECK(생년월일) >= '1980-01-01'));

 

CREATE VIEW

• 뷰(View)를 정의하는 명령문
• 표기형식

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

• ex) <고객>테이블에서 '주소'가 '안산시'인 고객들의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의 하시오

CREATE VIEW 안산고객(성명,전화번호)
AS SELECT 성명, 전화번호
   FROM 고객
   WHERE 주소='안산시';

 

CREAET INDEX

• 인덱스를 정의하는 명령문
• 표기형식

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

• ex) 고객 테이블에서 unique한 특성을 갖는 '고객번호' 속성에 대해 내림차순으로 정렬하여 '고객번호_idx'라는 이름으로 인덱스를 정의하시오

CREATE UNIQUE INDEX 고객번호_idx
ON 고객 (고객번호 DESC);

 

ALTER TABLE

• 테이블에 대한 정의를 변경하는 명령문

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

• ex) <학생> 테이블에 20 자의 가변길이를 가진 '주소'속성을 추가하는 SQL문을 작성해라

ALTER TABLE 학생 ADD 주소 VARCHAR(20);

 

DROP

• 스키마, 도메인, 기본테이블, 뷰테이블, 인덱스, 제약조건 등을 제거하는 명령문

DROP SCHEMA 스키마명 [CASCADE|RESTRICT];
DROP DOMAIN 도메인명 [CASCADE|RESTRICT];
DROP TABLE 테이블명 [CASCADE|RESTRICT];
DROP VIEW 뷰명 [CASCADE|RESTRICT];
DROP INDEX 인덱스명 [CASCADE|RESTRICT];
DROP CONSTRAIN 제약조건명;

• ex) 학생 테이블을 제거하되, 학생 테이블을 참조하는 모든 데이터를 함께 제거하시오.

DROP TABLE 학생 CASCADE;

 

 

2. SQL - DCL

DCL

• 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용하는 언어 
• 종류
  • COMMIT : 명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고 작업 완료를 관리자에게 알려줌
  • ROLLBACK : DB조작 작업이 비정상적으로 종료되었을 때 원래 상태로 복구
  • GRANT : DB사용자에게 사용 권한을 부여
  • REVOKE : DB사용자의 사용 권한을 취소
• 권한 리스트 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등
• WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여함
• GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소함 
• CASCADE : 권한 취소시 권한을 부여 받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적 취소

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

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

• ex) 사용자 park에게 테이블 student의 데이터를 갱신할 수 있는 시스템 권한을 부여하는 sql 문 작성

GRANT UPDATE ON STUDENT TO PARK;

• SAVE POINT : 트랜잭션 내의 ROLLBACK 할 위치인 저장점을 지정하는 명령어

 

3. SQL-DML

DML

• 저장된 데이터를 실질적으로 관리하는데 사용되는 언어 
• DML의 유형(SELECT, INSERT, DELETE, UPDATE)

삽입문(INSERT INTO~)

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

• ex) 사원 테이블에서 이름,부서,생일,주소 기본급 속성이 있을 때 사원 테이블에 있는 ㅇ편집부의 모든 튜플을 편집부원(이름,생일,주소,기본급)테이블에 삽입하는 sql문 작성

INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
SELECT 이름, 생일, 주소, 기본급
FROM 사원
WHERE 부서='편집';

 

삭제문(DELETE FROM~)

• DELETE문은 튜플만 삭제한다, 테이블을 완전히 삭제하기 위해서는 DROP문을 사용 

DELETE FROM 테이블명 [WHERE 조건]

 

• ex) <사원> 테이블에서 부서가 "인터넷"인 모든 튜플을 삭제하는 sql문 작성

DELETE 
FROM 사원
WHERE 부서='인터넷';

 

갱신문(UPDATE ~SET~)

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

ex) 사원 테이블에서 황진이의 부서를 기획부로 변경하고 기본급을 5만원 인상시키는 sql문 작성

UPDATE 사원
SET 부서='기획', 기본급=기본급+5
WEHRE 이름='황진이';

 

SELECT

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

• PREDICATE : 검색할 튜플 수를 제한하는 명령어를 기술(=DISTINCT : 중복된 튜플을 첫번째 한개만 표시)
• 사원 테이블에서 주소 만 검색하되 같은 주소는 한번만 출력하는 SQL문 작성

SELECT DISTINCT 주소
FROM 사원;

 

조건 지정 검색

• 비교 연산자 : = , <>(같지 않다), >, <, >=, <=
• 논리 연산자 : NOT, AND, OR
• LIKE 연산자 : %(모든문자대표), _(문자하나대표), #(숫자하나를대표)
• ex) 사원 테이블에서 성이 '김'인 사람과 생일이 01/01/69에서 12/31/73 사이인 튜플을 검색하는 sql문을 작성

SELECT 
FROM 사원
WHERE (이름 LIKE'%김') AND (생일 BETWEEN #01/01/69# AND #12/31/73#)

 

정렬 검색

• ORDER BY 절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬해 검색
• ex) 사원 테이블에서 부서 를 기준으로 오름차순, 이름을 기준으로 내림차순 정렬 시켜 검색하는 sql문 작성

SELECT
FROM 사원 
ORDER BY 부서 ASC, 이름 DESC;

 

하위 질위

• 조건절에 주어진 질의를 먼저 수행하여 그 검색결과를 피연산자로 사용
• ex) 취미가 나이트댄스인 사원이 이름과 주소를 검색하는 sql문 작성

SELECT 
FROM 사원
WHERE 이름 (SELECT 이름
	    FROM 여가활동
            WHERE 취미='나이트댄스');

 

그룹 함수 

• COUNT(속성명) : 그룹별 튜플 수 구하는 함수
• SUM(속성명) : 그룹별 합계 구하는 함수
• AVG(속성명) : 그룹별 평균을 구하는 함수
• MAX(속성명) : 그룹별 최대값을 구하는 함수
• MIN(속성명) : 그룹별 최소값을 구하는 함수
• STDDEV(속성명) : 그룹별 표준편차를 구하는 함수
• VARIANCE(속성명) : 그룹별 분산을 구하는 함수 

 

그룹 지정 검색

• GROUP BY 절에 지정한 속성을 기준으로 자료를 그룹화하여 검색한다.
• ex) <상여금> 테이블에서 '상여금'이 100이상인 사원이 2명이상인 '부서'의 튜플수를 구하는 sql문 작성

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금>=100 
GROUP BY 부서
HAVING COUNT(*)>=2;

 

집합 연산자를 이용한 통합 질의

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

 

JOIN

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

 

INNER JOIN

• 일반적으로 EQUI JOIN과 NON-EQUI JOIN으로 구분 
• 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻을 수 있다.
  • CROSS JOIN(교차조인) : 두 테이블의 행 수를 곱한 것 과 같다. 

EQUI JOIN

• 대상 테이블에서 공통 속성을 기준으로 =(equal) 비교에 의해 같은 값을 가지는 행을 연결해 결과를 생성하는 INNER JOIN 방법 
• WHERE 절을 이용한 EQUI JOIN 표기

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

• NATURAL JOIN 절을 이용한 EQUI JOIN 표기 형식

SELECT [테이블명1] 속성명, [테이블명2] 속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2

• JOIN~USING 절을 이용한 EQUI JOIN 표기형식

SELECT [테이블명1]속성명, [테이블명2]속성명...
FROM 테이블명1 JOIN 테이블명2 USING(속성명);

 

트리거

• 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 절차형 SQL

 

커서

• 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터
• 묵시적 커서 : 자동으로 생성되는 커서(DBMS에 의해)
• 명시적 커서 : 사용자가 직접 정의해서 사용하는 커서
• 속성의 종류
  • SQL %FOUND : 쿼리 수행 결과로 패치된 튜플 수가 1개이상이면 TRUE
  • SQL %NOTFOUND : 쿼리 수행 결과로 패치된 튜플 수가 0개이면 TRUE
  • SQL %ROWCOUNT : 쿼리 수행 결과로 패치 된 튜플수를 반환
  • SQL %ISOPEN : 커서가 열린 상태면 TRUE, 묵시적 커서는 자동생성후 자동닫히므로 항상 FALSE

DBMS 접속 기술의 종류

• DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크
• JDBC : Java언어로 DB 접속시 사용하는 표준 API
• ODBC : 언어 관계 없이 DB접근을 위한 표준 개방형 API(MS사에서 개발)
• MYBatis : JDBC 코드를 단순화 하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크, SQL문장을 분리해 XML 파일을 만들고 Mapping을 통해 sql을 실행

 

동적sql

• sql 구문을 동적으로 변경하여 처리할 수 있는 sql 처리 방식

 

ORM

• 객체와 관계형 데이터베이스의 데이터를 연결하는 기술
• 종류
  • JAVA : JPA, Hibernate, EsplishLink 등
  • c++ : ODB, QxOrm
  • Python : Django, Storm
  • .NET : NHibernate, DtabaseObjects, Dapper
  • PHP : Doctrine, Propel

쿼리 성능 관련 용어

• APM : 성능 관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역 등 다양한 모니터링 기능을 제공하는 도구
• 옵티마이저(Optimizer) : 작성된 sql 이 가장 효율적으로 수행되도록 최적의 경로를 찾아주는 모듈
  • RBO : DBA가 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
  • CBO : 입출력 속도, CPU 사용량, 블록개수 등을 종합하여 산출되는 비용으로 최적의 경로를 찾는 비용기반 옵티마이저
• 실행계획 : DBMS 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법
• 힌트 : SQL 문에 추가되어 테이블 접근 순서를 변경하거나 ,인덱스 사용을 강제하는 등 실행 계획에 영향을 줄 수 있는 문장
• IOT : 인덱스 안에 테이블 데이터를 직접 삽입하여 저장함으로써 주소를 얻는 과정을 생략하여 빠른 조회가 가능하도록 구성한 테이블

 

 

출처 : https://starrykss.tistory.com/1640

[정보처리기사 실기] 08. SQL 응용

https://velog.io/@jaik_lee/%EC%A0%95%EB%B3%B4%EC%B2%98%EB%A6%AC%EA%B8%B0%EC%82%AC-%EC%8B%A4%EA%B8%B0-%EC%A0%95%EB%A6%AC%EB%B3%B8#9%EB%8B%A8%EC%9B%90--%EC%86%8C%ED%94%84%ED%8A%B8%EC%9B%A8%EC%96%B4-%EA%B0%9C%EB%B0%9C-%EB%B3%B4%EC%95%88-%EA%B5%AC%EC%B6%95

[정보처리기사 실기] 정리본

https://zero-zae.tistory.com/91

[정보처리기사] 실기 - SQL 응용