SQL 2주차

SQL 2주차

데이터 처리

• Data Definition Language(DDL)
  • DDL는 스키마를 정의
  • 하나의 테이블을 만드는데 생성(Create)/조작(alter)/삭제(delete)
• Data Manipulation Language (DML)
  • DDL을 통해 만들어진 데이터를 삽입/삭제/수정을 할 수 있다
  • 하나의 테이블 뿐만 아니라 여러개의 테이블에서 정보를 가져오는 동작 역시 가능
  • select, insert, update, delete
• Data Control Language(DCL)
  • 객체 권한 부여 등의 제어어
  • 데이터의 보안, 무결성, 데이터 회복, 병행 수행 제어 등을 정의하는데 사용
  • DB관리자가 데이터 관리를 목적으로 사용
  • commit, rollback, grant, revoke 등
• 테이블 = 관계 = Relation 
• 테이블에는 투플이라고 하는 많은 데이터가 있으며 attributes(속성)의 형태는 스키마로 정의 되어 있음
• 예시로 Product(PName, Price, ...) 등이 Product의 스키마
• 스키마 = 데이터베이스를 구성하는 개체(Entity), 속성(Attribute), 관계(Relationship) 및 데이터 조작시 데이터들이 값들이 가지는 제약 조건 등에 관해 전반적으로 정의
• Multiset은 List와 Set을 합친 것이다. List는 중복을 허용하며 Set은 중복을 허용하지 않는 대신 순서가 없다. 즉 멀티셋은 순서가 없으며 중복을 허용한다
• 투플tuple = 행 = 레코드record = 로우row 
• Attribute = 컬럼(column) = 속성 
• 컬럼에는 하나의 정보만 담을 수 있다
• 카디날리티 = 헤더(열 제목)을 제외한 투플의 개수
• arity는 속성의 개수

MySQL Data Type

데이터 타입은 유일한 값이어야만 한다.

• Atomic types (하나의 칸 안에 정보는 유일해야한다 예시로 이름과 나이를 한칸에 넣는 경우 절대 XX)
  • characters : CHAR(20) = 20자 고정, VARCHAR(50) 0~50자
  • Numbers : INT, BIGINT, FLOAT
  • Others : Money, DATETIME

Table Schemas

• 스키마는 테이블 이름, 속성, 그들의 타입을 정의해야한다

Product(Pname: String, Price: float, Category:string, Manufacturer:string)

• 속성 중에 유일한 값들로 구성되는 속성들을 키(key)라고 표현한다. 보통 밑줄을 쳐서 표현함

 

Key constraints(키 제약조건)

• 유일하게 구분할 수 있는 것이 키
• 모든 줄 들을 유일하게 구별할 수 있는 방법
• 가령 Students(sid: string, name: string, gpa: float)라는 스키마가 있다. 학번 이름 성적중에 유일하게 구별가능한 것은 뭘까? 이름의 경우 동명이인이 존재할 수 있으며 성적 역시 겹칠 수 있다. 단 학번의 경우는 사람마다 다르기 때문에 학번을 키로 지정해야하는 것이다.
• 키가 항상 존재해야하냐? 항상 존재할 수 있도록 하는 것이 DB관리자의 몫이다. 없게 만들 수 있지만 가능하면 만들어줘야함
• 하나의 키 이상이 있는가? 유일하게 투플들을 구별할 수 있으면 된다. 예시로 학번과 이름을 한쌍으로 키를 만들 수 있지만 독립적으로 학번과 이름을 키로 만들어선 안된다

 

NULL and NOT NULL

• NULL = 값이 정해지지 않았다 (0이라는 뜻이 아님)
• 예시로 컬럼에 NOT NULL 제약 조건을 학번 컬럼에 명시한다면 키인 학번은 반드시 입력해야 하는 것이다.(기본키 제약조건에 의해 NOT NULL이 디폴트 값으로 지정되어 있긴하다)

 

Chapter 1 Summary

• 스키마와 제약조건(Constraints)
• 이 데이터를 어떻게 DB가 구조적으로 관리하는가

 

Chapter 2 Single Table Queries

• SFW(Select, From, Where)
• Select는 어떤 속성을 보일 것인지 From은 어떤 릴레이션(테이블)인지 Where은 조건문

select *
from product
where category = 'Gadgets'
-- Product 릴레이션(테이블)에서 카테고리명이 'Gadgets'인 투플의 모든 정보를 출력

select PName
from product
where category = 'Gadgets'
-- Product 릴레이션(테이블)에서 카테고리명이 'Gadgets'인 투플의 PName을 출력

만일 select * 을 통해 PName, Price, Manufacturer이 출력된다고 가정하자
select Price, PName을 할 경우 Price, PName 순으로 출력됨

 

• SQL의 명령어들은 대소문자를 구분하지 않음
• 값은 대소문자 구분함
• 문자열 표현시 ""이 아닌 ' '을 사용해야함
• LIKE 구문

select *
from products
where PName LIKE '%gizmo%'
-- products 테이블에 PName의 데이터에 gizmo 글씨가 포함된 투플의 모든 정보 출력

where PName LIKE '%_izmo%
-- izmo가 포함된 모든 정보 출력

DISTINCT 중복 제거(unique)
select DISTINCT Category
from product
-- product 테이블에 Category를 중복 제거하여 출력


Order By 순서 정렬
where 절 다음에 
order by price, PName
-- price, Pname 컬럼의 값에 따라 정렬
-- default = 오름차순
-- DESC 
order by price, DESC PName
-- price 오름차순 -> PName 내림차순 정렬

Group By

 

 

Chapter 3 Multi table query

• Foreign Key (외래키)
• 의존성을 나타냄

Students(sid: String, name: String, gpa: float)
Enrolled(Student id: String, cid: string, grade: string)

누가 수강했는지 알기 위해 Students sid를 참조하는 Enrolled 릴레이션(student id, cid)

create table enrolled(
	student_id CHAR(20),
    cid CHAR(20),
    grade CHAR(10),
    Primary Key(student_id, cid)
    FOREIGN Key(student_id) REFERENCES Students(sid)
);
 student_id와 cid를 하나의 기본키로 본다
 students의 sid 속성을 외래키로 참조함

• 외래키는 NULL 불가
  1. 참조 무결성(Referential integrity) 보장 외래키 제약조건은 참조된 테이블의 기본키와 일치하는 값만을 참조할 수 있도록 보장합니다. 즉, 데이터베이스에서 데이터 무결성을 유지하면서 테이블 간의 관계를 정의할 수 있습니다.
  2. 데이터 무결성 유지 외래키 제약조건은 데이터 무결성을 유지하는 데 중요한 역할을 합니다. 예를 들어, 학생 테이블에서 삭제된 학생의 학번 정보가 과목 테이블에 남아 있을 경우 데이터 무결성이 깨질 수 있습니다. 하지만 외래키 제약조건을 설정하면 학생 테이블에서 학생 정보를 삭제할 때, 해당 학생 번호를 참조하는 과목 정보도 함께 삭제됩니다.
  3. 성능 저하 가능성 외래키 제약조건은 데이터베이스에서 데이터 무결성을 보장하기 위해 많은 작업을 수행해야 하므로, 성능 저하가 발생할 수 있습니다. 하지만 이는 데이터 무결성을 유지하기 위한 필수적인 작업이기 때문에, 적절하게 설계되어 있다면 성능 저하를 최소화할 수 있습니다.
  4. 외래키 값이 NULL일 수 있음 외래키 값은 참조된 테이블의 기본키 값과 일치해야 하지만, NULL 값을 가질 수도 있습니다.

최인호 학생 테이블과 학사정보시스템 테이블이 있다.

최인호 학생 테이블의 학번은 학사정보 시스템의 학번을 참조하고 있는 외래키이다.

 

위에 테이블을 보면 

arity = 9

cardinality = 4

누적성적(년도학기 : String, 신청학점 : int, 취득학점 : int, 평점평균 : float, 산술평균 ..)

 

학사정보에서 최인호 학생의 학번을 삭제할 경우 최인호 학생의 테이블의 정보도 삭제된다.

그렇다면 학사정보를 참조하고 있는 최인호 학생의 테이블에서 학번을 삭제한다면?

 

최인호 학생 테이블에서 학번을 삭제하는 경우, 해당 학생의 정보와 함께 외래키 제약조건에 따라 학사정보 시스템 테이블에서 해당 학번을 참조하는 모든 레코드도 자동으로 삭제됩니다. 따라서, 최인호 학생 테이블에서 학번을 삭제하는 것은 학사정보 시스템 테이블에서 해당 학생의 학적 정보를 모두 삭제하는 것과 같습니다.

하지만 이 경우, 학사정보 시스템에서 다른 테이블에서 해당 학번을 참조하는 경우가 있을 수 있으므로, 이러한 외래키 제약조건을 고려하여 학번을 삭제해야 합니다. 예를 들어, 학사정보 시스템에서 성적 정보를 저장하는 성적 테이블에서 해당 학번을 참조하는 경우, 해당 레코드도 함께 삭제해야 데이터의 일관성과 무결성을 유지할 수 있습니다

 

 

 

Chapter 4 Join

• Join은 둘 이상의 테이블에서 데이터를 검색하고 조합하는 프로세스다.
• 두개 이상의 테이블에서 데이터를 검색하는 경우 사용하며 관계를 이용해 두개 이상의 테이블 간 공통된 값을 가지고 있는 레코드들을 연결하고 새로운 집합을 만든다.
• Inner Join : 두 테이블에서 공통된 값을 가지고 있는 레코드만 선택한다
• Outer Join : 왼쪽 테이블, 오른쪽 테이블 중 하나 이상의 일치하는 레코드 또는 둘 다 공통된 값이 없는 레코드를 선택한다.
• Left Join : 왼쪽 테이블의 모든 레코드와 오른쪽 테이블에서 일치하는 레코드를 선택한다
• Right Join : 오른쪽 테이블의 모든 레코드와 왼쪽 테이블에서 일치하는 레코드를 선택한다
•  Outer Join Case

[테이블 1: 학생 정보 (students)]
id   name      major
----------------------
1    John      Math
2    Sarah     English
3    Tom       History
4    Mary      Math

[테이블 2: 성적 정보 (scores)]
id   subject   score
----------------------
1    Math      80
1    English   75
2    History   90
3    English   85
5    Math      95

예를 들어, 학생 정보 테이블(students)과 성적 정보 테이블(scores)을 Outer Join으로 조인한다고 가정해보겠습니다. 이때, 학생 정보 테이블(students)을 왼쪽 테이블로, 성적 정보 테이블(scores)을 오른쪽 테이블로 사용합니다. 이렇게 Outer Join을 실행하면, 결과는 아래와 같습니다.

[Outer Join 결과]
id   name      major     subject   score
----------------------------------------
1    John      Math      Math      80
1    John      Math      English   75
2    Sarah     English   History   90
3    Tom       History   English   85
4    Mary      Math      NULL      NULL
5    NULL      NULL      Math      95

위 결과에서, Outer Join으로 조인된 결과에는 각각의 테이블에서 일치하지 않는 정보도 포함되어 있습니다. 예를 들어, 학생 정보 테이블에서는 id가 4인 Mary 학생의 정보가 존재하지만, 성적 정보 테이블에서는 해당 학생의 성적 정보가 존재하지 않습니다. 따라서, Outer Join 결과에서는 id가 4인 Mary 학생의 정보에 대해서는 subject와 score 컬럼에 NULL 값이 포함됩니다. 마찬가지로, 성적 정보 테이블에서는 id가 5인 학생의 정보가 존재하지만, 학생 정보 테이블에서는 해당 학생의 정보가 존재하지 않습니다. 따라서, Outer Join 결과에서는 id가 5인 학생의 정보에 대해서는 name과 major 컬럼에 NULL 값이 포함됩니다.

 

• Product와 Company 테이블들을 조인한다면?
• 예시로 어떤 회사의 200달러 이하의 제품의 이름과 가격을 뽑아낸다면?

<Schema>
Product(PName, Price, Category, Manufacturer)
Company(CName, StockPrice, Country)

select PName, Price
from Product, Company
where Manufacturer = CName  -- 두 테이블을 연결해주는 기준 값 
AND Country = 'Japan'
AND Price <= 200

• 조인시에 두개의 테이블에 공통이 되도록 묶어줄 수 있는 연결고리 필요

select PName, Price
from Product
JOIN Company ON Manufacturer = CName
		AND Country = 'Japan'
where Price <= 200

1. Product 테이블을 기준으로 잡아서 Company 테이블과 조인할 것이다
2. Manufacturer와 CName을 합치는데 나라이름이 'Japan'인 경우만
3. 가격이 200 이하인 투플들만

 

• 만약 이러한 경우는?

<Schema>
Person(name, address, wroksfor)
Company(name, address)

select distinct name, address
from Person, Company
Where worksfor=name

은 불가능
단 이것을 명시적으로 해결할 수 있음

select distinct Person.name, Person.address
from person, company
where Person.worksfor = Company.name

혹은 

select distinct p.name, p.address
from Person p, Company c
where p.worksfor = c.name

 

Chapter 5  Join semantic

Join semantic(조인 세맨틱)은 관계형 데이터베이스에서 조인 작업을 수행하는 방법에 대한 규칙들의 모음을 말합니다. Join semantic은 조인의 결과가 어떻게 생성되는지에 대한 명확한 규칙을 제공하여, 조인에 대한 예측 가능성을 높이고 조인 결과의 일관성을 유지합니다.

Join semantic에는 아래와 같은 세 가지 유형이 있습니다.

1. Inner Join Semantic: Inner Join Semantic은 조인하는 두 테이블에서 매치되는 레코드를 찾아서 결합합니다. 이때, 결과에는 매칭되는 레코드만 포함되고, 매칭되지 않는 레코드는 결과에 포함되지 않습니다.
2. Outer Join Semantic: Outer Join Semantic은 조인하는 두 테이블에서 매치되는 레코드와 매치되지 않는 레코드를 모두 결과에 포함합니다. 이때, 매치되는 레코드는 Inner Join Semantic과 동일하게 결합되고, 매치되지 않는 레코드는 NULL 값을 가지는 결과가 생성됩니다.
3. Semi-Join Semantic: Semi-Join Semantic은 조인하는 두 테이블 중 하나의 테이블에서 매치되는 레코드만 결과에 포함합니다. 이때, 매칭되는 레코드만을 결합하고, 매칭되지 않는 레코드는 결과에서 제외됩니다. Semi-Join Semantic은 EXISTS 나 IN 연산자를 사용하는 쿼리에서 자주 사용됩니다.