데이터베이스 중간고사 내용 정리

시험 전 중간고사 내용을 정리하여 학습 및 암기를 하기 위해 끄적여 봅니다.

검색 용도와는 맞지 않는 페이지일 수 있습니다.

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

 

데이터:관찰의 결과로 나타난 정량적 혹은 정성적인 실제 값
정보:데이터에 의미를 부여한 것 // 지식 : 사물이나 현상에 대한 이해

DB:조직에 필요 정보를 얻기 위해 논리적으로 연관된 데이터를 모아 구조적으로 통합한 것

[DB의 개념] - DB는 운영 데이터를 통합하여 저장하며 공용으로 사용되는 데이터.
(이는 컴퓨터 내부의 하드디스크에 저장)

1. 통합된 데이터(integrated data)
데이터를 통합하는 개념으로, 각자 사용하던 데이터의 중복을 최소화하여 중복으로 인한 데이
터 불일치 현상을 제거
2. 저장된 데이터(stored data)
문서로 보관된 데이터가 아닌 디스크, 테이프 같은 컴퓨터 저장장치에 저장된 데이터를 의미
3. 운영 데이터(operational data)
조직의 목적을 위해 사용되는 데이터, 즉 업무를 위한 검색을 할 목적으로 저장된 데이터
4. 공용 데이터(shared data)
한 사람 또는 한 업무를 위해 사용되는 데이터가 아니라 공동으로 사용되는 데이터를 의미

 

[DB 특징]

1. 실시간 접근성(real time accessibility)
DB는 실시간으로 서비스된다. 사용자가 데이터를 요청하면 수 초 내에 결과를 서비스한다.

 2. 계속적인 변화(continuous change)
DB에 저장된 내용은 어느 한 순간의 상태를 나타내지만, 데이터 값은 시간에 따라 항상 바뀐
다. DB는 삽입, 삭제, 수정 등의 작업을 통하여 바뀐 데이터 값을 저장한다. 

3. 동시 공유(concurrent sharing)
DB는 서로 다른 업무 또는 여러 사용자에게 동시에 공유된다. 동시는 병행이라고도하며, 데이터베이스에 접근하는 프로그램이 여러 개 있다는 의미이다. 

4. 내용에 따른 참조(reference by content)
DB에 저장된 데이터는 데이터의 물리적인 위치가 아니라 데이터 값에 따라 참조된다.

 

DB 사용자 – 일반 사용자/응용 프로그래머/SQL 사용자/관리자
DB 시스템 – DBMS(사용자-DB를 연결해주는 S/W), DB(데이터 모아둔 토대), 데이터 모델
(데이터가 저장되는 기법에 관한 내용)

-정보기술의 발달-

✔ 파일 시스템

DBMS가 없는 시스템으로, 데이터를 파일 단위로 파일 서버에 저장함.

각 컴퓨터는 LAN으로 파일 서버 연결, 파일 서버에 저장된 데이터 사용을 위해 각 컴퓨터의 응용 프로그램에서 열기/닫기(open/close)를 요청함.
각 응용 프로그램이 독립적으로 파일을 다루기 때문에 데이터가 중복 저장될 가능성이 있음.
-> 데이터의 일관성 저하

 

✔ 데이터베이스 시스템(DBMS)

- DBMS를 도입하여 데이터를 통합 관리하는 시스템

- DBMS가 설치되어 데이터를 가진 쪽을 서버, 외부에서 데이터 요청하는 쪽을 클라이언트
- DBMS 서버가 파일을 다루며 데이터의 일관성 유지, 복구, 동시 접근 제어 등의 기능을 수행

-> 데이터의 중복을 줄이고 데이터를 표준화하며 무결성을 유지함

 

✔  웹 DB 시스템

-  데이터베이스를 웹 브라우저에서 사용할 수 있도록 서비스하는 시스템

- 불특정 다수 고객을 상대로 하는 온라인 상거래나 공공 민원 서비스 등에 사용됨

✔ 분산 DB 시스템 - 여러 곳에 분산된 DBMS 서버를 연결하여 운영하는 시스템

- 대규모의 응용 시스템에 사용됨

 

[데이터 저장 방법]

1. 데이터를 프로그램 내부에 저장하는 방법 - (도서 데이터는 프로그램 내 구조체 변수에 저장)
문제점 : 새로운 데이터가 생길 때마다 프로그램을 수정한 후 다시 컴파일해야 함

2. 파일 시스템을 사용하는 방법 (문제점 = 동시에 사용할 수 없다.)
새로운 데이터가 추가되어도 프로그램을 수정할 필요 없음
문제점 : 같은 파일을 두 개의 프로그램이 공유하는 것이 운영체제의 도움 없이 불가능

3. DBMS를 사용하는 방법 - 데이터 정의와 데이터 값을 DBMS가 관리
DBMS는 데이터 정의, 데이터 변경 등의 작업을 할 수 있는 별도의 프로그램을 갖고 있음
프로그램에 데이터 정의나 데이터 값을 포함 안해서 데이터 구조 바뀌어도 다시 컴파일 필요 X

 

파일 시스템은 응용프로그램에 “종속적”이다. (데이터 일관성↓, 데이터 무결성↓)
DBMS는 응용프로그램에 “독립적”이다. (데이터 일관성/무결성 유지)

 

[파일 시스템, DBMS의 장단점 비교]

✔ 파일 시스템 장점

-OS설치 시 같이 설치되서 별도 구입비용 없이 사용 가능
-보통 소규모 프로그램에서 사용해 주기억장치 적게 사용, 속도 빠름

✔ 파일 시스템 단점

-프로그램-데이터 독립성X -> 유지,보수 비용↑
-데이터 공유가 어려움. -파일 단위 저장 ->중복 가능하여 데이터 불일치 발생,일관성↓
->사용자 권한에 따른 접근제어가 어려워 보안조치가 미흡

✔ DBMS 장점

- 데이터중복을 최소화한 통합DB 구축 -> 프로그램-데이터 독립적->유지,보수비용↓
- DB사용을 통해 데이터 공유에 용이
- 모든 데이터를 DB에 통합하므로 데이터 중복과 불일치 감소 ->데이터 일관성 유지
- DB를 통한 데이터 중앙집중화->보안조치 용이

- 데이터를 분리시킴으로써 독립성↑, 데이터 무결성 유지

✔DBMS  단점

- DBMS 구입 비용 및 추가적인 H/W 구입비용 등 초기 투자비용이 크다.

- 데이터 관리의 오버헤드와 위험이 존재.

- 응용프로그램이 단순하고, 데이터 변경이 자주 안 일어나고, 다수 사용자 접근이 필요하지 않을때는 부적합함. 

 

[DB 를 사용자의 분류]

1. 일반 사용자 - 데이터를 다루는 업무

프로그래머가 개발한 프로그램을 이용하여 데이터베이스에 접근 일반인

2. 응용 프로그래머

일반 사용자 위한 사인페와 데이터를 관리하는 응용 로직을 개발

3. SQL 사용자 - SQL 업무 담당자

응용 프로그램으로 구현되어 있지 않은 업무를 SQL을 사용하여 처리

4. DB 관리자(DBA, DataBase Administrator)

- DB 운영 조직의 데이터베이스 시스템을 총괄하는 사람
- 데이터 설계, 구현, 유지보수의 전 과정을 담당
- DB 사용자 통제/보안/성능 모니터링/데이터 전체 파악 및 관리/데이터 이동&복사 등 제반 업무를 함

[DBMS의 기능]

데이터 정의 : 데이터 구조 정의, 데이터 구조에 대한 삭제&변경 기능 수행
데이터 조작 : 데이터 조작 S/W가 요청하는 데이터 삽입/수정/삭제 작업 지원
데이터 추출 : 사용자가 조회하는 데이터 혹은 응용 프로그램의 데이터 추출
데이터 제어 : DB 사용자 생성, 모니터링하며 접근 제어. 백업/회복/동시성제어 기능 지원

 

[데이터 모델] : DB시스템에서 데이터를 저장하는 이론적 방법에 관한 것.

-> DB에 데이터가 어떻게 구조화되어 저장되는지 결정.

(1) 포인터 사용 : 계층 데이터 모델(트리 형식), 네트워크 데이터 모델(그래프 형식) (~1980)

-> 데이터 직접 찾아가서 프로그램 속도는 빠르지만 응용 프로그램 개발 속도 면에서는 느림.

(2) 속성 값 사용 : 관계 데이터 모델(1980~)

-> 포인터에 비해 속도는 느리지만 개념이 쉽고 프로그램 개발이 빠름.

 

(3) 객체 식별자 사용 : 객체 데이터 모델(1990~) – 상속/캡슐화 등 개념 도입

-> 테이블을 객체 개념으로 보고 객체의 고유 식별자를 다른 테이블에 저장하는 방법으로 구현.

 

[DB 구조]

스키마 : DB 구조와 제약 조건에 관한 전반적 명세를 기술한 메타데이터의 집합
-> DB 구성하는 데이터 객체, 속성,관계 및 데이터 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의.

 -> 외부, 개념, 내부 스키마

 

(1) 외부 단계(=외부 스키마) -> 여러 개 있을 수 있다. 외부 스키마는 개념 스키마의 일부만 접근/이용(개인의 견해) - 일반 사용자나 응용 프로그래머가 접근하는 계층으로 전체 데이터베이스 중에서 하나의 논리적인 부분을 의미
- 서브 스키마(sub schema)라고도 하며, 뷰(view)의 개념임
(2) 개념 단계(논리적 스키마, 범기관적 견해) -> 외부 스키마 통합 = 1개만 있다. 

-전체 데이터베이스의 정의를 의미
-통합 조직별로 하나만 존재하며 DBA가 관리함
-하나의 데이터베이스에는 하나의 개념 스키마(conceptual schema)가 있음
(3) 내부 스키마(물리적 스키마, 데이터 크기/타입 정의) -> 1개만 있다. 

-물리적 저장 장치에 데이터베이스가 실제로 저장되는 방법의 표현
-인덱스, 데이터 레코드의 배치 방법, 데이터 압축 등에 관한 사항이 포함됨

 

● 외부/개념 매핑 - 사용자의 외부 스키마와 개념 스키마 간의 매핑(사상)
외부 스키마의 데이터가 개념 스키마의 어느 부분에 해당되는지 대응시킴
● 개념/내부 매핑 
개념 스키마의 데이터가 내부 스키마의 물리적 장치 어디에 어떤 방법으로 저장되는지 대응

 

[데이터 독립성] 

- 하위 단계 내용을 추상화해 상위 단계에서 그 세부사항 숨김으로써 하위 단계 내의 변경에 대해 상위 단계와 상호간 간섭이 없도록 하는 것
->(결과)독립성 수행함으로써 DB 시스템 운영과 관련 응용프로그램의 유지보수가 용이해진다. 

 

● 논리적 데이터 독립성

->개념 스키마가 변경돼도 외부 스키마는 변경X
• 외부 단계(외부 스키마)와 개념 단계(개념 스키마) 사이의 독립성
• 개념 스키마가 변경되어도 외부 스키마에는 영향을 미치지 않도록 지원
• 논리적 구조가 변경되어도 응용 프로그램에는 영향이 없도록 하는 개념
• 개념 스키마 테이블을 생성/변경해도 외부 스키마가 직접 다루는 테이블이 아니면 영향X

● 물리적 데이터 독립성

->내부 스키마가 변경돼도 개념스키마는 변경X
개념 단계(개념 스키마)와 내부 단계(내부 스키마) 사이의 독립성
저장장치 구조 변경과 같이 내부 스키마 변경돼도 개념 스키마에 영향을 미치지 않도록 지원
• 성능 개선을 위해 물리적 저장 장치 재구성할 경우 
개념 스키마나 응용 프로그램 같은 외부 스키마에 영향X
• 물리적 독립성은 논리적 독립성보다 구현하기 쉬움

 

[ANSI 3단계 데이터베이스 구조]

 

-릴레이션 

관계 데이터 모델에서 사용하는 2차원 테이블 형태의 데이터 구조
관계 :

 (1) 릴레이션 내에서 생성되는 관계 : 릴레이션 내 데이터들의 관계
 (2) 릴레이션 간에 생성되는 관계 : 릴레이션 간의 관계
 -> 관계 데이터 모델을 다른 데이터 모델과 구분하는 특징이 됨.

 

스키마(정적) : 관계 DB의 릴레이션이 어떻게 구성되는지 어떤 정보를 담고 있는지에 대한 기본적인 구조를 정의(테이블의 첫 행인 헤더에 나타나며 각 데이터의 특징을 나타내는 속성, 자료 타입 등의 정보를 담고 있음.)
인스턴스(동적) : 정의된 스키마에 따라 테이블에 실제로 저장되는 데이터의 집합(투플 집단)

속성 : 각각의 이름이 있으며 우리는 그 이름을 보고 어떤 정보가 담기는지 알 수 있다. 

도메인 : 속성이 가질 수 있는 값의 집합
차수 : 속성의 개수
투플 : 릴레이션이 나타내는 엔티티의 한 인스턴스

 

● 투플이 가지는 속성의 개수는 릴레이션 스키마의 차수와 동일하다.

● 릴레이션 내의 모든 투플들은 서로 중복되지 않아야 한다. 

● 릴레이션에 저장된 튜플의 수를 카다날리티라고 한다.(삽입,수정,삭제에 따라 변함)

 

- 릴레이션과 테이블-

- 릴레이션은 수학 집합이론에 기초하여 속성이나 인스턴스 중복 허용 X
- 릴레이션은 주로 구조를 강조하는 의미로 이론 설명 시 많이 사용, 테이블은 데이터를 강조하는 의미로 실무에서 많이 씀

 

- 릴레이션 특징-

⓵ “속성의 원자성” - 속성은 단일 값을 가진다. (여러 개 값 가질 수 없음.->투플 하나 더 생성해야 함)
⓶ “속성의 유일성” - 속성은 서로 다른 이름을 가짐. 

⓷ 한 속성의 값은 모두 같은 도메인 
⓸ “속성의 무순서성” - 속성의 순서는 상관없다
⓹ “투플의 유일성” - 릴레이션 내의 중복된 투플은 허용하지 않는다.
(값이 모두 달라야 한다. -> 중복되면 데이터 불일치 발생!)
⓺ “튜플의 무순서성” - 투플의 순서는 상관없다

 

* 관계 데이터 모델 – 데이터를 2차원 테이블 형태인 릴레이션으로 표현
-릴레이션에 대한 제약조건과 관계 연산을 위한 관계대수를 정의함.

 

● Key(키) - 특정 투플을 식별할 때 사용하는 속성 혹은 속성의 집합

키가 되는 속성(or 속성의 집합)은 반드시 값이 달라서 투플들을 구별할 수 있어야 함. 키는 릴레이션 간 관계를 맺는데도 사용됨.

(1) 슈퍼키 : “유일성”을 가진 속성들의 집합

-투플을 유일하게 식별할 수 있는 하나의 속성/속성의 집합
-투플을 유일하게 식별할 수 있는 값이면 모두 슈퍼키가 될 수 있음.

-슈퍼키는 포함하지 않아도 되는 속성을 모두 포함할 수 있음.

(2) 후보키 : “유일성” AND “최소성” -투플을 유일하게 식별할 수 있는 속성의 최소 집합

-두 속성을 합쳐서 후보키로 만들 수 있다. - 두 개 이상의 속성으로 이루어진 키를 복합키라고 함.

(3) 기본키 : 여러 후보키 중 하나를 선정하여 대표로 삼는 키

- 후보키가 하나뿐이라면 이를 기본키로 사용하면 되고, 여러 개라면 릴레이션의 특성을 반영하여 하나를 선택하면 됨

* 고려사항
- 릴레이션 내 튜플을 식별할 수 있는 고유한 값을 가져야 함. NULL값 허용 X
- 키 값의 변동이 일어나지 않아야 함. 최대한 적은 수의 속성을 가진 것이야 함.(복합키 많아지면 효율성↓)
-> 향후 키를 사용하는 데 있어서 문제 발생 소지가 없어야 함

- 릴레이션 스키마 표현 시 기본키는 밑줄을 그어 표시함

(4) 대리키(=인조키) : 기본키가 보안을 요하거나, 여러 개 속성으로 구성되어 복잡하거나, 마땅한 기본키가 없을 때 일련번호 같은 가상의 속성을 만들어 기본키로 삼는 경우. -DBMS나 관련 S/W에서 임의로 생성하는 값으로 사용자가 직관적으로 그 값의 의미를 알 수 없음.

(5) 대체키 : 기본키로 선정되지 않은 후보키

(6) 외래키 : 다른 릴레이션의 기본키를 참조하는 속성

-다른 릴레이션의 기본키를 참조하여 관계 데이터 모델의 특징인 릴레이션 간의 관계를 표현.

 

- 외래키의 특징 -

관계 데이터 모델의 릴레이션 간의 관계를 표현함. 

다른 릴레이션의 기본키를 참조하는 속성임. 

참조하고(외래키) 참조되는(기본키) 양쪽 릴레이션의 도메인은 서로 같아야 함. 

참조되는(기본키) 값이 변경되면 참조하는(외래키) 값도 변경됨. NULL 값, 중복값 등 허용
자기 자신의 기본키를 참조하는 외래키도 가능함. 외래키가 기본키의 일부가 될 수 있음.

 

[제약 조건 정리 -> 무결성 제약조건 3가지]

● 데이터 무결성 : DB에 저장된 데이터의 일관성과 정확성을 지키는 것

 

(1) 도메인 무결성 제약조건(=도메인 제약)

릴레이션 내 투플들이 각 속성의 도메인에 지정된 값만을 가져야 함. (SQL 문에서 데이터 형식(type), 널(NULL, 
NOT NULL), 기본 값(default), 체크(check) 등을 사용하여 지정 가능)

(2) 개체 무결성 제약조건(=기본키 제약)

기본키는 NULL값을 가져서는 안 되며 릴레이션 내에 오직 하나의 값만 존재해야 함.

(3) 참조 무결성 제약조건(=외래키 제약) 

자식 릴레이션의 외래키는 부모 릴레이션의 기본키와 도메인이 동일해야 하며, 자식 릴레이션 값이 변경될 때 부모 릴레이션의 제약을 받음.

 

[ 데이터 변경이 일어났을 때]

● 개체 무결성 제약조건

-삽입 : 기본키 값이 같으면 삽입이 금지됨.

-수정 : 기본키 값이 같거나 NULL로도 수정이 금지됨
-식제 : 특별한 확인이 필요하지 않으며 즉시 수행함

● 참조 무결성 제약조건

-삽입 : 부모 – 투플 삽입한 후 수행 시 정상적으로 진행됨. 자식 

– 참조받는 테이블에 외래키 값 없으면 삽입 불가
->투플에 기본키 넣도록 수정하거나, 부모 릴레이션 기본키 옵션에 NULL값 허용 시 삽입 가능

 

-삭제 : 부모 – 참조하는 테이블을 같이 삭제할 수 있어서 금지하거나 다른 추가 작업이 필요
=> 거부(RESTRICTED, 즉시 작업 중지)/연쇄 삭제(CASCADE, 자식 릴레이션의 관련 투플 
함께 삭제)/자식 릴레이션 관련 투플을 미리 설정해둔 값으로 변경(DEFAULT)/자식 릴레이션 
관련 투플 값을 NULL 값으로 설정(NULL)

-> 자식 – 바로 삭제 가능 

 

-수정 : 삭제와 삽임 명령이 연속해서 수행됨. = 삭제 후 삽입과 같음

 

● 관계대수 

-  어떤 데이터를 찾는지에 대한 처리 절차를 명시하는 절차적인 언어

- 릴레이션에서 원하는 결과를 얻기 위해 수학의 대수와 같은 연산을 이용하여 질의하는 방법을 기술하는 언어. 

- 결과 릴레이션이 나오기까지의 절차를 확인할 수 있는 방법 제공

● 관계해석

 - 어떤 데이터를 찾는지만 명시하는 선언적인 언어 -> 관계대수와 함께 DBMS 표준 언어인 SQL의 이론적 기반을 제공

● 관계대수 연산자

(1) 순수 관계 연산 : 셀렉션, 프로젝션, 조인, 디비전, 개명
(2) 일반 집합 연산 : 합집합, 차집합, 교집합, 카티전 프로덕트

 

● 관계대수식

(1) 셀렉션(단항 연산자) -릴레이션 투플 추출 연산. -찾고자 하는 투플의 조건을 명시하고 그에 맞는 투플 반환. -조건이 여러개라면 ∧(and), ∨(or),ㄱ(not)을 이용하여 복합조건 표시 가능.
(2) 프로젝션(단항 연산자) -릴레이션 속성 추출을 위한 연산, 중복 튜플은 삭제함
(3) 합집합 -두 개 릴레이션은 서로 같은 속성 순서와 도메인을 가져야 함.
(4) 교집합, 차집합
(6) 카티전 프로덕트 : 두 렐리이션을 하나로 합칠 때 사용
-결과 릴레이션은 첫 번째 릴레이션의 오른쪽에 두 버째 릴레이션의 모든 투플 순서대로 배열하여 반환. 결과 릴레이션의 차수는 두 릴레이션의 차수의 합이며, 카디날리티는 두 릴레이션의 카디날리티의 곱

 

● 조인

- 두 릴레이션의 공통 속성 기준으로 속성 값 같은 투플을 수평으로 결합하는 연산.

- 두 릴레이션의 조인에 참여하는 속성이 서로 동일한 도메인으로 구성되어야 함.

- 조인 연산의 결과는 공통 속성의 속성 값이 동일한 투플만을 반환함.

 

(1) 기본 연산 : 세타조인, 동등조인, 자연조인
(2) 확장된 조인 연산 : 세미조인, 외부조인

⓵ 세타조인 : 조인에 참여하는 두 릴레이션 속성 값 비교하여 조건 만족하는 투플만 반환. 세타조인 조건은 (=,≠,≤,≥,<,>)

중 하나.

⓶ 동등조인(중복O) : 세타조인에서 =연산자를 사용한 조인. -결과 릴레이션 차수 합 

=1번째 릴레이션 차수 + 2번째 릴레이션 차수
-속성 이름은 그대로 사용. 같은 경우 1번째 릴레이션의 속성부터 시작하여 순서대로 나타냄. 

⓷ 자연조인 : 동등조인에서 조인에 참여한 속성이 두 번 나오지 않도록 두 번째 속성을 제거
한 결과를 반환. 결과 차수는 두 릴레이션 차수의 합 –1 

 

[조인 사용 연산] 
예 : π주문번호, 이름, 판매가격 (σ이름=‘박지성’ (고객 고객.고객번호=주문.고객번호 주문))
[카티전 프로덕트 연산]
예 : π주문.주문번호, 고객.이름, 주문.판매가격 (σ고객.고객번호=주문.고객번호 AND 고객.이름=‘박지성’ (고객×주문)

 

⓸ 외부조인 : 자연조인 시 조인에 실패한 투플을 모두 보여주되,
 값이 없는 대응 속성에는 NULL값을 채워서 반환
-모든 속성을 보여주는 기준 릴레이션 위치에 따라 왼쪽/오른쪽/완전/외부 조인으로 나뉨.

⓹ 세미조인 : 자연조인을 한 후 두 릴레이션 중 한쪽 릴레이션의 결과만 반환. -기호에서 닫힌 쪽 릴레이션의 투플만 반환.

 

● 디비전 : 릴레이션의 속성 값의 집합으로 연산을 수행 -특정 값들을 모두 갖고 있는 투플을 찾을 때 사용

 

[SQL과 일반 프로그래밍 언어의 차이]

-데이터 정의어(DDL) : 테이블이나 관계의 구조를 생성하는데 사용
(예 : CREATE, ALTER, DROP 등) 

-데이터 조작어(DML) : 테이블에 데이터를 검색, 삽입, 수정, 삭제하는데 사용
(예 : SELECT(질의어), INSERT, DELETE, UPDATE 등)

 -데이터 제어어(DCL) : 데이터 사용 권한을 관리하는 데 사용
(예 : GRANT, REVOKE 문 등)

 

(1) SELECT - 질의 결과 추출되는 속성 리스트 열거 (속성 이름)
(2) FROM-질의에 어느 테이블이 사용되는지 열거 (테이블 이름)
(3) WHERE-질의의 조건 작성 (검색 조건) -> 중복을 제거하고 싶다면 SELECT에 DISTINCT를 넣어주면 된다.

 

● WHERE 조건

LIKE ‘%축구%’ : ‘축구’ 가 포함된 것을 찾는다. 

LIKE ‘_구%’ : 왼쪽 두 번째 위치에 ‘구’ 문자열 찾기

 

● 문자열 검색 시 LIKE와 같이 사용하는 와일드 문자

- ORDER BY (오름차순 : ASC, 내림차순 : DESC)

 

● 집계 함수

-의미 있는 열 이름을 출력하고 싶다면 속성 이름의 별칭을 지칭하는 AS 키워드를 이용하여 열 이름을 부여한다.

예 : SELECT SUM(saleprice) AS 총매출
 FROM Orders; - GROUP BY를 쓸 때, GROUP BY의 속성 값은 SELECT 속성 값에 꼭 존재해야 한다. 

-> SELECT 문에서 GROUP BY의 속성 값 외에는 모두 집계 함수여야 한다.

- HAVING 절은 GROUP BY 절의 결과 나타나는 그룹을 제한하는 역할.

● 집계 함수 종류

● GROUP BY와 HAVING 절의 문법, 주의사항

● 두 개 이상 테이블에서 SQL 질의

[조인] - 조인을 사용하면 두 개 이상의 테이블에서 일부 데이터만 얻을 수 있다. 

기본 형식 : SELECT *
                  FROM Customer, Orders
                  WHERE Customer.custid = Orders.custid;
-> 동등 조인한 것과 같다!! 

 

예 : 세 개 테이블 조인 -> 질의 : 질의 : 고객의 이름과 고객이 주문한 도서의 이름을 구하시오.

SELECT Customer. name, Book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid = Orders.custid
AND Orders.bookid = Book.bookid;

(해설 : 고객 이름은 Customer에 있고, 주문내역은 Orders에 있으며, 도서 이름은 Book에 있다. Customer과 Orders는 Customer의 기본키인 custid와 Orders의 외래키인 custid로 연결한다. Orders 테이블과 Book 테이블은 Orders의 외래
키인 bookid와 Book의 기본키인 bookid로 연결한다.)

 

● 외부조인 : 결과에 포함되지 않는 조건 값도 포함시켜 출력하게 하고 싶을 때

질의 : 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구해라.

SELECT Customer.name, saleprice
FROM Customer LEFT OUTER JOIN Orders
 ON Customer.custid=Orders.custid;

 

● 조인 문법

● 부속 질의(=중첩질의)

- SELECT 문의 WHERE 절에 또 다른 테이블 결과를 이용하기 위해 다시 SELECT문을 괄호로 묶는 것

- 결과가 단일행-단일열(1x1)이 아닌 다중행-단일열(nx1)이라면 IN 키워드를 사용한다. - 부속질의 간에는 상하 관계가 있으며, 실행 순서는 하위 부속질의를 먼저 실행하고 그 결과를 이용하여 상위 부속질의를 실행한다.

● 상관 부속질의

- 상위 부속질의의 투플을 이용하여 하위 부속질의를 계산

- 상위 부속질의와 하위 부속질의가 독립적이지 않고 서로 관련을 맺고 있음.

- 부속질의와 조인의 차이점- 

⓵ 부속질의는 SELECT문에 나오는 결과 속성을 FROM절의 테이블에서만 얻을 수 있고, 조인은 조인한 모든 테이블에서 결과 속성을 얻을 수 있다. 

⓶ 조인은 부속질의가 할 수 있는 모든 것을 할 수 있다. 그러나 부속질의를 조인을 이용해서 작성해보면 부속질의 만의 편리함을 알 수 있다. ->한 개의 테이블에서만 결과를 얻는 여러 테이블 질의는 조인보다 부속질의로 작성하는 것이 훨씬 편하다. (= 여러 테이블에서 결과를 얻을 때는 조인이 훨씬 낫다!)

 

● 집합 연산

합집합 : UNION(UNION ALL은 중복을 포함한 모든 결과)
차집합 : WHERE절에 NOT IN을 사용한다. 

교집합 : WHERE절에 IN을 사용한다.

● EXISTS : 상관 부속질의문 형식(WHERE문에 쓰임) 

- 조건에 맞는 튜플이 존재하면 결과에 포함시킴 = 부속질의문의 어떤 행이 조건에 만족하면 참
(NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참이다.)

 

● CREATE 문 [데이터 정의어] 

- 테이블 구성, 속성과 속성에 관한 제약 정의, 기본키 및 외래키 정의하는 명령
- table 생성하기 위해 필요한 문장들을 모두 CREATE로 표현 가능. 

- PRIMARY KEY : 기본키 지정, FOREIGN KEY : 외래키 지정
- ON UPDATE & ON DELETE : 외래키 속성의 수정/투플 삭제 시 동작을 나타냄. 

- NULL/NOT NULL, UNIQUE(중복값X), 기본값(DEFAULT xxx), 조건문(CHECK() )

 - 외래키 참조 시 FOREIGN KEY(bookid) REFERNCES Orders(orderid) ON DELETE CASCADE ); 와 같이 적는다.

 

예 CREATE TABLE new (
bookid INTEGER PRIMARY KEY,
publisher VARCHAR(20),
bookname VARCHAR(20)); - bookid 속성이 없어서 두 개의 속성 bookname, publisher가 기본키가 된다면 괄호를 사
용하여 복합키를 지정한다.

 

 예 CREATE TABLE new (
bookid INTEGER,
publisher VARCHAR(20),
bookname VARCHAR(20),
PRIMARY KEY (bookname, publisher));

 

● 외래키 제약조건 명시할 때 주의할 점 (참조 무결성 제약조건을 지킴.)

- 반드시 참조되는 테이블(부모 릴레이션)이 존재해야 하며 참조되는 테이블의 기본키여야 한다.

- 외래키 지정 시 ON DELETE 또는 ON UPDATE 옵션은 참조되는 테이블의 튜플이 삭제/수정 시 취할 수 있는 동작을 지정한다.

- NO ACTION은 어떠한 동작도 취하지 않는다.

 

● MYSQL에서 사용하는 데이터 타입의 종류

● ALTER 문 [데이터 정의어]

- 생성된 테이블의 속성과 속성에 관한 제약을 변경하며, 기본키 및 외래키를 변경함.

 - ADD, DROP은 속성을 추가/수정/삭제할 때 사용
- MODIFY는 속성의 기본값을 설정하거나 삭제할 때 사용
- ADD <제약이름>, DROP <제약이름>은 제약사항 추가/삭제 시 사용

● DROP 문 [데이터 정의어] : 테이블 삭제하는 명령 (DROP TABLE 테이블이름) 

- DROP문은 테이블 구조&데이터 모두 삭제한다.
(데이터만 삭제하려면 DELETE 문 사용)

- 삭제하려는 테이블의 기본키를 다른 테이블에서 참조하고 있다면 삭제가 거절됨. 

-> 참조하고 있는 테이블부터 삭제 후 삭제하려던 테이블 삭제

● INSERT 문 [데이터 조작어] : 테이블에 새로운 투플 삽입하는 명령

-문법 : INSERT INTO 테이블이름[(속성리스트)]

           VALUES (값리스트);

- 대량 삽입(bulk insert)이란 한꺼번에 여러 개의 투플을 삽입하는 방법이다. 

● UPDATE 문 [데이터 조작어] : 특정 속성 값 수정하는 명령어

-문법 : UPDATE 테이블이름
           SET 속성이름1=값1[, 속성이름2=값2, ...
           [WHERE <검색조건>];

● DELETE 문 [데이터 조작어] : 테이블에 있는 기존 투플을 삭제

-문법 : DELETE FROM 테이블이름
           [WHERE 검색조건];

예 : 모든 고객 삭제 = DELETE FROM Customer; 
이때, 다른 테이블에서 외래키로 참조하고 있으면 데이터 삭제가 되지 않는다.

 

[SQL 내장 함수]

- SQL에서는 함수의 개념을 사용
- SQL의 함수는 DBMS가 제공하는 내장 함수(build-in function),

사용자 필요에 따라 직접 만드는 사용자 정의 함수(user-defined function)으로 나뉜다.

사용자가 만든 함수에 대비되는 용어, DBMS에 제공하는 함수. 

- 상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환함
- 모든 내장 함수는 최초에 선언될 때 유효한 입력 값을 받아야 함

 

● NULL 값 처리

NULL : 아직 지정되지 않은 값

- NULL 값은 ‘0’, ‘’ (빈 문자), ‘ ’ (공백) 등과 다른 특별한 값
- NULL 값은 비교 연산자로 비교가 불가능함
- NULL 값의 연산을 수행하면 결과 역시 NULL 값으로 반환됨

 

* 집계 함수를 사용할 때 주의점

- ‘NULL+숫자’ 연산의 결과는 NULL
- 집계 함수 계산 시 NULL이 포함된 행은 집계에서 빠짐 (Count에서 개수 빠짐) 

- 해당되는 행이 하나도 없을경우 SUM, AVG 함수의 결과는 NULL이 되며, COUNT 함수의 결과는 0.

- NULL값 확인하는 방법 – IS NULL, IS NOT NULL
- 공백은 들어갈 수 있음!!(NULL이랑 다름!!) 

- IFNULL : NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력
- 형식 : IFNULL(속성,값) // 속성 값이 NULL이면 ‘값’으로 대치함. 

 

* 행번호 출력

- MySQL에서 변수는 이름 앞에 @ 기호를 붙이며 치환문에는 SET과 := 기호를 사용함
- 자료를 일부분만 확인하여 처리할 때 유용함.

 

● 부속질의

- 하나의 SQL 문 안에 다른 SQL 문이 중첩된(nested) 질의
- 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용
- 보통 데이터가 대량일 때 데이터를 모두 합쳐서 연산하는 조인보다 필요한 데이터만 찾아서 공급해주는 부속질의가 성능이 더 좋음
- 주질의(main query, 외부질의)와 부속질의(sub query, 내부질의)로 구성

 

※ 조인을 사용 : Customer 테이블과 Orders 테이블의 고객번호로 조인한 후 필요한 데이터를 추출
※ 부속질의 사용 : Customer 테이블에서 박지성 고객의 고객번호를 찾고, 찾은 고객번호를 바탕으로 Orders 테이블에서 확인 

 

< 부속질의 종류>

★ 동작 방식

부속질의가 주질의의 값을 참조하는가에 따라 상관 부속질의/비상관 부속질의로 나뉨. 

- 상관 부속질의 : 주질의의 특정 열 값을 부속질의가 상속받아 부속질의의 질의에 사용하는 형태
- 비상관 부속질의(일반 부속질의) : 독립된 질의를 수행해서 결과 값을 가져오는 형태

● 스칼라 부속질의(SELECT 부속질의)

- 부속질의의 결과 값을 단일 행, 단일 열의 스칼라 값으로 반환
- 원칙적으로 스칼라 값이 들어갈 수 있는 모든 곳에 사용 가능
- 일반적으로 SELECT 문과  UPDATE SET 절에 사용됨
- 주질의와 부속질의와의 관계는 상관/비상관 모두 가능함.

● 인라인 뷰(FROM 부속질의)

- 테이블 이름 대신 인라인 뷰 부속질의를 사용하면 보통의 테이블과 같은 형태로 사용할 수 있음
- 부속질의 결과 반환되는 데이터는 다중 행, 다중 열이어도 상관없음
- 다만 가상의 테이블인 뷰 형태로 제공되어 상관 부속질의로 사용될 수는 없음
- 조인에 참여하기 직전 필요한 데이터만 뽑아 조인할 수 있으므로 처리 성능↑

● 중첩질의(WHERE 부속질의)

- WHERE 절은 보통 데이터를 선택하는 조건 혹은 술어(predicate)와 같이 사용됨
→ 중첩질의를 술어 부속질의(predicate subquery)라고도 함

 

※ 비교 연산자 : 부속질의가 반드시 단일 
행, 단일 열을 반환해야 하며, 아닐 경우 질의를 처리할 수 없음.

※ IN, NOT IN -IN 연산자는 주질의 속성 값이 부속질의에서 제공한 결과 집합에 있는지 확인하는 역할을 함
-IN 연산자는 부속질의의 결과 다중 행을 가질 수 있음
-주질의는 WHERE 절에 사용되는 속성 값을 부속질의의 결과 집합과 비교해 하나라도 있으면 참이 됨
-NOT IN은 이와 반대로 값이 존재하지 않으면 참이 됨

※ ALL, SOME(ANY) - ALL은 모두, SOME(ANY)은 어떠한(최소한 하나라도)이라는 의미

※ EXISTS, NOT EXISTS
- 데이터의 존재 유무를 확인하는 연산자
- 주질의에서 부속질의로 제공된 속성의 값을 가지고 부속질의에 조건을 만족하여 값이 존재하면 참이 되고, 주질의는 해당 행의 데이터를 출력함
- NOT EXIST의 경우 이와 반대로 동작함

 

● EXISTS와 IN의 차이

(1) EXISTS : 조건에 해당하는 ROW의 존재 유무와 체크 후 더이상 수행하지 않음.
 -> SELECT 절을 평가하지 않으므로 일반적으로 IN에 비해 성능이 좋다.
 IN : 조건에 해당하는 ROW의 컬럼을 비교하여 체크한다. 

-> SELECT 절에서 조회한 컬럼 값으로 비교하므로 EXISTS에 비해 성능이 떨어진다.

 

(2) 실행순서
EXISTS : 메인 쿼리의 결과값을 서브 쿼리에 대입하여 조건 비교 후 결과를 출력한다.
( 메인쿼리 -> EXISTS 쿼리 )
IN : 서브 쿼리의 결과값을 메인 쿼리에 대입하여 조건 비교 후 결과를 출력한다. ( IN쿼리 -> 메인 쿼리 )

 

(3) NOT IN과 NOT EXISTS의 NULL값에 대한 처리
NOT EXISTS : NULL 데이터에 대해 TRUE를 리턴한다. 

NOT IN : NULL 데이터에 대해 FALSE를 리턴한다. -> NULL 처리를 하지 않을 경우 누락이 발생한다.