[정보처리기사] 실기 7단원 'SQL 응용' 정리

스키마

외부 스키마 (External Schema)	사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조 - 사용자 뷰를 나타냄 - 서브 스키마로 불림
개념 스키마 (Conceptual Schema)	- 데이터베이스의 전체적인 논리적 구조 - 전체적인 뷰를 나타냄 - 개체 간의 관계, 제약 조건, 접근 권한, 무결성, 보안에 대해 정의
내부 스키마 (Internal Schema)	- 물리적 저장장치의 관점에서 보는 데이터베이스 구조 - 실제로 데이터베이스에 저장될 레코드의 형식을 정의하고 저장 데이터 항목의 표현 방법, 내부 레코드의 물리적 순서 등을 표현

 

 

CREATE TABLE

CREATE TABLE 테이블명 

( 컬럼명 데이터 타입 CHECK(조건식) : 제약 조건 설정

);

 

 

 

테이블에 컬럼 추가

ALTER TABLE 테이블명 ADD 칼럼명 데이터타입 [제약 조건];

ex) 사원 테이블에 VARCHAR(11) 타입으로 전화번호라는 컬럼을 추가하는 쿼리

ALTER TABLE 사원
ADD 전화번호 VARCHAR(11);

 

테이블에서 컬럼 삭제 

ALTER TABLE 테이블명 DROP 컬럼명;

ex) 사원 테이블에 생년월일이라는 컬럼을 삭제하는 쿼리

ALTER TABLE 사원
DROP 생년월일;

 

테이블에서 데이터 삭제

DELETE FROM 테이블명 WHERE 조건;

ex) 학생 테이블에서 학번이 3000인 데이터를 삭제하는 쿼리

DELETE FROM 학생
WHERE 학번 = 3000;

 

 

테이블에 뷰 생성

CREATE VIEW 뷰이름 AS 조회쿼리;

ex) 사원 테이블에 사번, 업무, 이름, 생년월일, 성별, 입사일이라는 컬럼이 있을 때 사원 테이블에서 성별 값이 'M'을 가진 사번, 이름으로 생성된 '사원뷰'라는 이름의 뷰 생성하는 쿼리

CREATE VIEW 사원
AS
SELECT 사번, 이름 FROM 사원 WHERE 성별 = 'M';

 

 

테이블에 데이터 추가 

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

ex) 사원명이 홍길동, 나이가 24, 급여가 300인 직원을 직원 테이블에 삽입하는 쿼리

INSERT INTO 직원 (사원명, 나이, 급여) VALUES('홍길동', 24, 300);

 

 

권한을 부여하는 쿼리

GRANT 권한 ON 테이블 TO 사용자 (그온투)

REVOKE 권한 ON 테이블 FROM 사용자 (리온프)

ex) 권리자가 사용자 장길산에게 '학생' 테이블에 대해 UPDATE할 수 있는 권한을 부여하는 쿼리

GRANT UPDATE ON 학생 TO 장길산;

ex) 권리자가 사용자 장길산에거 '학생'테이블에 대해 조회할 수 있는 권한을 부여하는 쿼리

GRANT SELECT ON 학생 TO 장길산;

 

 

ROLLUP 함수

- ROLLUP에 의해 지정된 컬럼은 소계(소그룹의 합계) 등 중간 집계 값을 산출하기 위한 그룹 함수

- 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과가 바뀐다.

ex) 학교명, 학년, 인원 컬럼이 존재하는 학교 테이블에서 학교별 인원수와 전체 인원수를 알려주는 쿼리

SELECT 학교명, 학년, SUM(인원) AS 인원 FROM 학교
GROUP BY ROLLUP(학교명, 학년)

 

 

순위 함수

RANK	동일 순위의 레코드 존재 시 후순위는 넘어감 ex) 2위가 3개인 레코드: 2위, 2위, 2위, 5위 ...
DENSE_RANK	동일 순위의 레코드 존재 시 후순위는 넘어가지 않음 ex) 2위가 3개인 레코드 : 2위, 2위, 2위, 3위, ...
ROW_NUMBER	동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여 ex) 2위가 3개인 경우 : 2위, 3위, 4위, 5위 ...

ex)

SELECT 이름, 점수, DENSE_RANK() OVER(ORDER BY 점수 DESC) AS 등수
FROM 학생;

ex) RANK 함수를 이용하며 연봉을 높은 순에서 낮은 순으로 정렬

SELECT 이름, 연봉, RANK() OVER(ORDER BY 연봉 DESC) AS 순위
FROM 사원;

 

 

UPDATE 명령어

UPDATE 테이블명 SET 속성명 = 데이터 , ... 

WHERE 조건;

 

 

 

 

프로시저

: 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

 

사용자 정의 함수

: 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 변환할 수 있는 절차형 SQL

 

트리거

: 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

 

- 운영체제에서 프로시저/사용자 정의 함수로 값을 전달하는 모드는 IN이고, 프로시저/사용자 정의 함수에서 처리된 결과를 운영체제로 전달하는 모드는 OUT, IN/ OUT의 두 가지 기능을 동시에 수행하는 모드는 INOUT

 

 

롤백(ROLLBACK)

: 오류가 발생했을 때, 오류 이전의 특정 시점(SAVEPOINT, CHECKPOINT) 상태로 되돌려주는 제어어

 

커밋(COMMIT)

: 트랜잭션을 메모리에 영구적으로 저장하는 제어어

 

체크포인트(CHECKPOINT)

: ROLLBACK을 위한 시점을 지정하는 제어어

 

 

 

인덱스 생성 쿼리

CREATE INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);

ex) 학생 테이블의 학번 컬럼에 대해 학번 인덱스라는 인덱스 명으로 인덱스를 생성

CREATE INDEX 학번 ON 학생(학번);

 

 

SELECT -> FROM -> GROUP BY -> HAVING

 

 

내부 조인 SQL 

SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2
FROM 테이블1 A (INNER) JOIN 테이블2 B
ON 조인조건
(WHERE 검색조건)

 

 

WHERE 절에서 IN 연산자를 사용한 서브쿼리

ex) 책명이 자료구조인 가격 중에 가장 비싼 값을 도서가격 테이블에서 서브쿼리

SELECT MAX(가격)
FROM 도서가격
WHERE 책번호 IN (SELECT 책번호 FROM 도서 WHERE 책명 = '자료구조');

 

 

옵티마이저 

: SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진

 

규칙기반 옵티마이저 (RBO)	통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행 계획을 선택하는 옵티마이저
비용기반 옵티마이저 (CBO)	통계 정보로부터 모든 접근 경로를 고려한 질의실행 계획을 선택하는 옵티마이저

 

 

 

~ 해당 포스팅은 업데이트 중 ~