[ 데이터베이스 이론 ] 공부 정리

[ 데이터베이스 이론 ] 공부 정리 - 4강. SQL SELECT문

호크테마 2023. 5. 19. 16:49

* 공부 정리일자 (2022년 8월 ~ 12월)
- 책 : 데이터베이스 배움터 (ORACLE을 기반으로 하는), 데이터베이스 개론 (2판), 데이터베이스 시스템 (해외서적 6판)
- 개인적으로 공부 및 복습하면서 정리한 내용입니다. (사진 제외)

* 목차
0) 주요 개요
1) 데이터 정의어 (DDL)와 무결성 제약조건
2) SELECT문
(1) SELECT 문 개요
(1.1) 별칭(alias) - 재명명
(1.2) 릴레이션의 모든 애트리뷰트나 일부 애트리뷰트들을 검색
(1.3) 상이한 값들 검색 (=중복 제거)
(1.4) 특정한 투플들의 검색
(1.5) 문자열 비교
(1.6) 다수의 검색 조건
(1.7) 부정 검색 조건
(1.8) 범위를 사용한 검색
(1.9) 리스트를 사용한 검색
(1.10) SELECT절에서 산술 연산자(+, -, *, /) 사용
(1.11) 널 값
(1.12) ORDER BY절
(1.13) 집단 함수
(1.14) 그룹화
(1.15) HAVING절
(1.16) 집합 연산

3) 조인
(1) 조인
(2) 자체 조인(self join)

4) 중첩 질의 (nested query)
(0) 중첩 질의 (nested query)
(1) 한 개의 스칼라값이 반환되는 경우 (하나의 튜풀)
(2) 한 개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우 (다수의 튜풀)

1. 복습 (중요 키워드 위주)
* 복습 파트 : 4강. SQL

0) 주요 개요
(1) SQL 분류, 릴레이션 예시

* 데이터 정의언어 DDL
(Data Definition Language)
- Create, Alter, Drop

* 데이터 조작언어 DML
(Data Manipulation Language)
- 일반 질의어 (Querying) - select
- 데이터베이스 수정(modification)
-- insert, delete, update

* 기타 기능 (DCL, Embedded SQL)

(2) SQL 개요
* SQL은 비절차적 언어(선언적 언어)이므로
사용자는 자신이 원하는 바(what)만 명시하며, 원하는 것을 처리하는 방법(how)은 명시할 수 없음

* 관계 DBMS는 사용자가 입력한 SQL문을 번역하여 사용자가 요구한 데이터를 찾는데 필요한 모든 과정을 담당
* 자연어에 가까운 구문을 사용하여 질의를 표현할 수 있음

* 두 가지 인터페이스
- 대화식 SQL (interactive SQL)
- 내포된 SQ L(embedded SQL)

(3) 오라클 SQL의 구성요소
* 데이터 검색
* 데이터 조작어
* 데이터 정의어
* 트랜잭션 제어
* 데이터 제어어

(4) SQL 인터페이스의 구성 요소

1) 데이터 정의어 (DDL)와 무결성 제약조건

(1) 데이터 정의어 (DDL)
- 스키마의 생성과 제거
- SQL2에서는 동일한 데이터베이스 응용에 속하는 릴레이션, 도메인, 제약조건, 뷰, 권한 등을 그룹화하기 위해서 스키마 개념을 지원

* 릴레이션 정의
CREATE TABLE  DEPARTMENT
        (DEPTNO NUMBER NOT NULL,
        DEPTNAME CHAR(10),
        FLOOR   NUMBER,
        PRIMARY KEY(DEPTNO));

* 릴레이션 제거
DROP TABLE DEPARTMENT;

* ALTER TABLE
ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);

* 인덱스 생성
CREATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO);

(2) 제약조건

2) SELECT문
(1) SELECT 문 개요
* 관계 데이터베이스에서 정보를 검색하는 SQL문
- 관계 대수의 실렉션과 의미가 완전히 다름
- 관계 대수의 실렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 것
- 관계 데이터베이스에서 가장 자주 사용됨(질의)
- 여러 가지 질의들의 결과를 보이기 위해서 그림 4.8의 관계 데이터베이스 상태를 사용함

* 기본적인 SELECT SQL 질의- SELECT절은 관계형 대수의 추출연산과 대응. 질의의 결과로 바라는 애트리뷰트 나열.
- SQL에서는 릴레이션이나 질의의 결과에 중복을 허용. (SQL은 수학이 아님)
- 중복 제거를 위하여 distinct 사용.
- from 절은 관계형 대수의 카티전 곱 연산에 대응한다. 표현식의 계산에서 검색될 릴레이션들을 나열한다.

- Where 절은 선택연산의 조건 표시
-- From 절에 나열된 릴레이션의 애트리뷰트만 사용.

(1.1) 별칭(alias) - 재명명
- 서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법

SELECT E.DNO
FROM EMPLOYEE AS E, DEPARTMENT AS D;

(1.2) 릴레이션의 모든 애트리뷰트나 일부 애트리뷰트들을 검색
* * 를 사용하여 모든 애트리뷰트들 검색
- 전체 부서의 모든 애트리뷰트들 검색하라.

SELECT * FROM DEPTARTMENT;

* 원하는 애트리뷰트들의 이름 열거
- 모든 부서의 부서번호와 부서 이름을 검색하라.

SELECT DEPTNO, DEPTNAME FROM DEPTARTMENT;

(1.3) 상이한 값들 검색 (=중복 제거)
* DISTINCT 절 사용
- 모든 사원들의 직급 검색하라.

SELECT DISTINCT , DEPTNAME FROM DEPTARTMENT;

(1.4) 특정한 투플들의 검색
* WHERE 절을 사용하여 검색 조건을 명시
- 2번 부서에 근무하는 사원들에 관한 모든 정보 검색하라.

SELECT * FROM EMPLOYEE WHERE DNO = 2;

(1.5) 문자열 비교
* %를 사용하는 문자열 비교
- 이씨 성을 가진 사원들의 이름, 직급, 소속 부서번호를 검색하라.

SELECT EMPNAME, TITLE, DNO, FROM EMPLOYEE WHERE EMPNAME LIKE '이%';

(1.6) 다수의 검색 조건
* AND 검색 조건
- 직급이 과장이면서 1번 부서에서 근무하는 사원들의 이름과 급여 검색하라.

SELECT EMPNAME, SALARY FROM EMPLOYEE WHERE TITLE = '과장' AND DNO = 1;

(1.7) 부정 검색 조건
* 부정 <> 검색 조건
- 직급이 과장이면서 1번 부서에 속하지 않은 사원들의 이름과 급여 검색하라.

SELECT EMPNAME, SALARY FROM EMPLOYEE WHERE TITLE = '과장' AND DNO <> 1;

(1.8) 범위를 사용한 검색
- 범위 조건 AND, <>, >=, <=, >, < 등 사용하면 됨.

(1.9) 리스트를 사용한 검색
* in 검색 조건
- 1번 부서나 3번 부서에 소속된 사원들에 관한 모든 정보 검색하라.

SELECT * FROM EMPLOYEE WHERE DNO IN (1, 3);

(1.10) SELECT절에서 산술 연산자(+, -, *, /) 사용
- 직급이 과장인 사원들에 대하여 이름과 현재의 급여, 급여가 10% 인상되었을 떄의 값을 검색하라.

SELECT EMPNAME, SALARY, SALARY * 1.1 AS NEWSALARY // AS 이름 재명명!
FROM EMPLOYEE WHERE TITLE = '과장';

(1.11) 널 값
* 널 값
- 널값을 포함한 다른 값과 널값을 +, - 등을 사용하여 연산하면 결과는 널
- COUNT(*)를 제외한 집단 함수들은 널값을 무시함
- 어떤 애트리뷰트에 들어 있는 값이 널인가 비교하기 위해서 ‘DNO=NULL’처럼 나타내면 안됨

* 널값 올바른 표현
SELECT EMPNO, EMPNAME FROM EMPLOYEE
WHERE DNO IS NULL;

(1.12) ORDER BY절
- 사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 릴레이션에 투플들이 삽입된 순서대로 사용자에게 제시됨
- ORDER BY절에서 하나 이상의 애트리뷰트를 사용하여 검색 결과를 정렬할 수 있음
- SELECT문에서 가장 마지막에 사용되는 절

- 디폴트 정렬 순서는 오름차순(ASC)
- DESC를 지정하여 정렬 순서를 내림차순으로 지정할 수 있음

- 널값은 오름차순에서는 가장 마지막에 나타나고, 내림차순에서는 가장 앞에 나타남
- SELECT절에 명시한 애트리뷰트들을 사용해서 정렬해야 함

- 2번 부서에 근무하는 사원들의 급여, 집급, 이름을 검색하여 급여의 오름차순으로 정렬하라.

SELECT SALARY, TITLE, EMPNAME
FROM EMPLOYEE WHERE DNO = 2
ORDER BY SALARY;

(1.13) 집단 함수
- 데이터베이스에서 검색된 여러 투플들의 집단에 적용되는 함수
- 한 릴레이션의 한 개의 애트리뷰트에 적용되어 단일 값을 반환함
- SELECT절과 HAVING절에만 나타날 수 있음

- COUNT(*)를 제외하고는 널값을 제거한 후 남아 있는 값들에 대해서 집단 함수의 값을 구함
- COUNT(*)는 결과 릴레이션의 모든 행들의 총 개수를 구하는 반면에
   COUNT(애트리뷰트)는 해당 애트리뷰트에서 널값이 아닌 값들의 개수를 구함

- 키워드 DISTINCT가 집단 함수 앞에 사용되면 집단 함수가 적용되기 전에 먼저 중복을 제거함

* 주요 집단함수와 기능
- COUNT : 투플이나 값들의 개수 / SUM : 값들의 합 / AVG : 값들의 평균값
- MAX : 값들의 최대값 / MIN : 값들의 최소값

- 모든 사원들의 평균 급여와 최대 급여를 검색하라

SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE;

(1.14) 그룹화
- GROUP BY절에 사용된 애트리뷰트에 동일한 값을 갖는 투플들이 각각 하나의 그룹으로 묶임
- 이 애트리뷰트를 그룹화 애트리뷰트 (grouping attribute)라고 함

* 각 그룹에 대하여 결과 릴레이션에 하나의 투플이 생성됨
- SELECT절에는
   각 그룹마다 하나의 값을 갖는 애트리뷰트, 집단 함수, 그룹화에 사용된 애트리뷰트들만 나타날 수 있음

- 모든 사원들에 대해서 사원들이 속한 부서번호별로 그룹화하고, 각 부서마다 부서번호, 평균 급여, 최대 급여를 검색하라.

SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO;

(1.15) HAVING절
- 어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용할 수 있음
- 각 그룹마다 하나의 값을 갖는 애트리뷰트를 사용하여 각 그룹이 만족해야 하는 조건을 명시함

- 그룹화 애트리뷰트에 같은 값을 갖는 투플들의 그룹에 대한 조건을 나타내고, 이 조건을 만족하는 그룹들만 질의 결과에 나타남
- HAVING절에 나타나는 애트리뷰트는 반드시 GROUP BY절에 나타나거나 집단 함수에 포함되어야 함

- 모든 사원들에 대해서 사원들이 속한 부서번호별로 그룹화하고, 평균 급여가 2500000원 이상인 부서에 대해서 부서번호, 평균 급여, 최대 급여를 검색하라.

SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG(SALARY) >= 25000000;

(1.16) 집합 연산
- 집합 연산을 적용하려면 두 릴레이션이 합집합 호환성을 가져야 함
- UNION (합집합), EXCEPT (차집합), INTERSECT (교집합),   UNION ALL (합집합), EXCEPT ALL (차집합), INTERSECT ALL (교집합)

3) 조인
(1) 조인
- 두 개 이상의 릴레이션으로부터 연관된 투플들을 결합
- 일반적인 형식은
아래의 SELECT문과 같이 FROM절에 두 개 이상의 릴레이션들이 열거되고,
두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 WHERE절에 포함됨

- 조인 조건은 두 릴레이션 사이에 속하는 애트리뷰트 값들을 비교 연산자로 연결한 것
- 가장 흔히 사용되는 비교 연산자는 =

- 조인 조건이 명확해지도록 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용하는 것이 바람직
- 두 릴레이션의 조인 애트리뷰트 이름이 동일하다면 반드시 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용해야 함

- 모든 사원의 이름과 이 사원이 속한 부서 이름을 검색하라.
SELECT EMPNAME, DEPTNAME
FROM EMPLYEE AS E, EMPARTMENT AS D
WHERE E.DNO = D.DEPTNO;

(2) 자체 조인(self join)
- 한 릴레이션에 속하는 투플을 동일한 릴레이션에 속하는 투플들과 조인하는 것
- 실제로는 한 릴레이션이 접근되지만
FROM절에 두 릴레이션이 참조되는 것처럼 나타내기 위해서 그 릴레이션에 대한 별칭을 두 개 지정해야 함

- 모든 사원에 대해서 사원의 이름과 직속 상사의 이름을 검색하라.
SELECT E.EMPNAME, M.EMPNAME
FROM EMPLYEE E, EMPNAME M
WHERE E.MANAGER = M.EMPNO;

4) 중첩 질의 (nested query)
(0) 중첩 질의 (nested query)
- 외부 질의의 WHERE절에
   다시 SELECT ... FROM ... WHERE 형태로 포함된 SELECT문
- 부질의 (subquery)라고 함

- INSERT, DELETE, UPDATE문에도 사용될 수 있음
- 중첩 질의의 결과로
    한 개의 스칼라값(단일 값), 한 개의 애트리뷰트로 이루어진 릴레이션,
    여러 애트리뷰트로 이루어진 릴레이션이 반환될 수 있음

(1) 한 개의 스칼라값이 반환되는 경우 (하나의 튜풀)

(2) 한 개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우 (다수의 튜풀)
- 중첩 질의의 결과로 한 개의 애트리뷰트로 이루어진 다수의 투플들이 반환될 수 있음
- 외부 질의의 WHERE절에서 IN, ANY(SOME), ALL, EXISTS와 같은 연산자를 사용해야 함

- 키워드 IN은 한 애트리뷰트가 값들의 집합에 속하는가를 테스트할 때 사용됨
- ANY : 한 애트리뷰트가 값들의 집합에 속하는 하나 이상의 값들과 어떤 관계를 갖는가를 테스트하는 경우에는 ANY를 사용
- ALL : 한 애트리뷰트가 값들의 집합에 속하는 모든 값들과 어떤 관계를 갖는가를 테스트하는 경우에는 ALL을 사용

(3) 여러 애트리뷰트들로 이루어진 릴레이션이 반환되는 경우
(4) 상관 중첩 질의 (correlated nested query)
- 생략

다음은 SQL의 중요한 INSERT, DELETE, UPDATE문 정리입니다.

저작자표시 비영리 (새창열림)