[Relational database] 06. SQL ***

Chapter 1: SQL 개요

• SQL 개요
• SQL 구성

Chapter 2: DDL - Data Definition Language

• DDL: Data Definition Language
• SQL에서 DDL 구문
• 테이블 생성, 수정, 삭제 DDL문

실습: 테이블 생성 (MYSQL)

Chapter 3: DML - Data Manipulation Language, SQL 질의 기본 구조

• SQL 질의 형식
• SELECT 절
• SELECT 명령 내의 수식과 문자열
• FROM절
• WHERE절

Chapter 4: 집합 연산

• UNION
• INTERSECT
• EXCEPT

Chapter 5: 서브 쿼리

• 서브 쿼리 개요
• 포함된 서브쿼리(Nested subquery)
• 인라인 뷰(Inline view)
• 스칼라 서브쿼리(Scalar subquery)
• 상호 연관 서브쿼리(Correlated Subquery)

Chapter 6: 집단 연산

• 집계 함수
• GROUPT BY절과 HAVING절

Chapter 7: 정렬

• ORDER BY를 이용한 정렬
• INDEX를 이용한 정렬

Chapter 8: 삽입

• INSERT 문
• 하나의 INSERT 문으로 여러 행 삽입

Chapter 9: 갱신

• UPDATE 문

Chapter 10: 삭제

• DELETE 문

---

Chapter 1: SQL 개요

SQL 개요

• SQL(Structured Query Language)은 가장 널리 사용되는 상용 관계 데이터베이스 언어.
• 비 절차적(non-procedural) 언어: SQL은 어떻게 작업 수행할 것인가 보다는 어떤 데이터를 구하고, 삭제하고 ,삽입할지를 서술한다.
• 플랫폼과 프로그램에 구애 받지 않는 언어.

SQL 구성

• DML (Data Manipulation Language): DB의 데이터를 조회하거나 검색하기 위한 명령 및 DB 테이블의 데이터에 변형을 가함.
• DDL (Data Definition Language): 테이블, 무결성 등의 데이터 구조를 정의하는데 사용되는 명령어들로 데이터 구조와 관련됨.
• DCL (Data Control Language): DB에 접근하고 개체들을 사용하도록 권한을 주고 회수.
• TCL (Transaction Control Language): DML에 의해 조작된 결과를 트랜잭션별로 제어.

2024.10.01 - [NHN Java 백엔드 8기/Relational database] - [Relational database] DDL, DML, DCL, TCL

[Relational database] DDL, DML, DCL, TCL

 

---

Chapter 2: DDL - Data Definition Language

DDL: Data Definition Language

• SQL에서 DDL은 Relational DB의 구조와 객체를 정의하는 SQL 언어.

 

SQL에서 DDL 구문

• CREATE: DB 객체 생성 ex) Relation(Table), View, Index, Stored procedure 등
• DROP: 존재하는 DB 객체 삭제.
• ALTER: 존재하는 DB 객체 수정.
• TRUNCATE: 테이블 내 데이터 완전 삭제.

 

테이블 생성, 수정, 삭제 DDL문

• 생성

CREATE TABLE {Table Name} (
	[Column Name] [Data Type] {NULL | NOT NULL} {Column Option}
	{Constraint List} [Constraint definition]
)

 

• 수정

ALTER TABLE [TableName]
	{ALTER COLUMN} [COLUMN NAME] {Column Option}
	{ADD} (Column | Constraints} {ADD Option}
	{DROP} (Column | Constraints} {DROP Option}

 

• 삭제

DROP TABLE [TableName]

 

예제(MYSQL)

* 릴레이션 스키마

Category(CategoryNo: Integer, CategoryName: String)
Product(ProductNo: Integer, SerialKey: String, ProductName: String, Price: Integer, State: String, Detail: String, Stock: Integer, TaxRate: Integer, CategoryNo: Integer)

 

* 테이블 생성 

# 기본키가 없는 테이블 생성.
CREATE TABLE Category (
    CategoryNo      int,
    CategoryName    nvarchar(20)
);

# 기본키가 있는 테이블 생성 1.
CREATE TABLE Category (
    CategoryNo      int    PRIMARY KEY,
    CategoryName    nvarchar(20)
);

# 기본키가 있는 테이블 생성 2.
CREATE TABLE Category (
    CategoryNo    int,
    CategoryName  nvarchar(20),

    CONSTRAINT pk_Category PRIMARY KEY(CategoryNo)
);

# Product Table 생성.
CREATE TABLE Product (
    ProductNo 	int,
    SerialKey	char(12)		NOT NULL,
    ProductName	nvarchar(30)	NOT NULL,
    Price		int		NOT NULL,
    State		nvarchar(4)	NOT NULL,
    Detail		text,
    Stock		int		DEFAULT 0,
    TaxRate		int		NOT NULL CHECK(TaxRate >= 10 AND TaxRate < 20),
    CategoryNo	int,

    CONSTRAINT pk_Product PRIMARY KEY(ProductNo), # PK 제약조건
    CONSTRAINT uq_ProductSerial UNIQUE(SerialKey), # UNIQUE 제약조건
    CONSTRAINT ch_State CHECK (State IN ('신상','중고','전시')), # State column에 들어갈 수 있는 데이터 제한
    CONSTRAINT fk_Product_Category FOREIGN KEY(CategoryNo) REFERENCES Category(CategoryNo) # FK 제약조건
);

 

 

* 테이블 수정

# Product Table의 ProductName의 데이터 타입을 nvarchar(30) -> nvarchar(40)으로 변경.
ALTER TABLE Product MODIFY COLUMN ProductName nvarchar(40);

# Product Table에 ModelNo 필드 추가.
ALTER TABLE Product ADD COLUMN ModelNo nvarchar(20);

# Product Table에 추가한 ModelNo 필드의 순서를 SerialKey 다음으로 변경.
ALTER TABLE Product MODIFY ModelNo varchar(20) AFTER SerialKey;

# Product Table의 TaxRate column의 default 값을 10으로 변경.
ALTER TABLE Product ALTER COLUMN TaxRate SET DEFAULT 10;

# Product Table의 Detail 필드를 NOT NULL로 설정.
ALTER TABLE Product MODIFY COLUMN Detail text NOT NULL;

# Product Table의 ModelNo 필드의 이름을 ModelNumber로 변경.
ALTER TABLE Product CHANGE ModelNo ModelNumber nvarchar(20);

# Product Talbe의 이름을 Products로 변경.
ALTER TABLE Product RENAME Products;

# Product Table의 이름을 Product로 변경.
RENAME TABLE Products TO Product;

 

 

* 테이블 삭제

# Product Table의 SerialKey column에 설정된 UNIQUE 제약 조건 삭제.
ALTER TABLE Product DROP CONSTRAINT uq_ProductSerial;

# Product Table의 ModelNumber column 삭제.
ALTER TABLE Product DROP COLUMN ModelNumber;

# Product Table 삭제.
DROP TABLE Product;

 

실습: 테이블 생성 (MYSQL)

https://github.com/gikpreet/class-relational_database/blob/main/Module%2006%20SQL/contents/09-1_lab6-1a.adoc

class-relational_database/Module 06 SQL/contents/09-1_lab6-1a.adoc at main · gikpreet/class-relational_database

 

---

 

Chapter 3: DML - Data Manipulation Language, SQL 질의 기본 구조

스키마: https://github.com/gikpreet/class-relational_database/blob/main/Module%2006%20SQL/contents/11_note.adoc

SQL 질의 형식

• SQL 표현의 기존 구조 - SELECT, FROM, WHERE 절

SELECT 절

• 관계 대수에서 프로젝션(Projection - π) 연산과 일치, 결과에 나타날 속성을 나열하는데 사용.
• 중복을 허용하고, 중복이 없어야 하는 쿼리의 경우 DISTINCT 키워드를 사용한다.
• SELECT [DISTINCT] A1, A2 … An

SELECT 명령 내의 수식과 문자열

• 집계함수: SUM, COUNT, AVG 
• 집계함수는 NULL을 제외하고 계산한다.

FROM절

• Query에서 사용되는 Relation 목록

WHERE절

• 관계대수에서 셀렉션(Selection - σ) 연산과 일치.

---

Chapter 4: 집합 연산

UNION

• 관계 대수식의 합집합에 대응.
• 기본적으로 중복을 제거함.
• UNION ALL: 중복 허용.

INTERSECT

• 관계 대수식의 교집합에 대응.
• 기본적으로 중복을 제거함.
• INTERSECT ALL: 중복 허용.

EXCEPT

• 관계 대수식의 차집합에 대응.
• 기본적으로 중복을 제거함.
• EXCEPT ALL: 중복 허용.
• MySQL은 지원 X

---

Chapter 5: 서브 쿼리

서브 쿼리 개요

• 서브 쿼리(Subquery)
  • 다른 Query를 포함하는 Query.
  • 관계 대수 질의 결과는 relation을 반환한다.
  • 어떤 realtion을 계산하고, 그 relation에 query를 할 경우가 필요할 때.
• SELECT 절에 사용: Scala Subquery
• FROM 절에 사용: Inline View
• WHERE 절에 사용: Subquery

 

포함된 서브쿼리(Nested subquery)

• 단일 Query 안에 Query가 포함된 형태로, Subquery의 결과를 변수처럼 사용.
• Single Row Subquery(단일행 서브 쿼리): 쿼리의 결과가 하나의 Row만을 산출.
• Multi Row Subquery(다중행 서브 쿼리): 쿼리 결과가 여러 Row를 산출.
• Multi Column Subquery(다중 컬럼 서브 쿼리): 일부 테이블만을 불러와서 그 중에서 selection.
• Relational DB가 제공하는 집합 내 다중 값을 비교할 수 있는 연산자.
  • IN, ANY, ALL, EXISTS

* 예시

# 대한항공에서 운항하는 항공편의 항공편 번호, 비행기 번호, 출발지, 도착지, 가격을 구하라.

SELECT
	flight.flightNo, flight.AircraftNo, Depareture, Arrival, Price
FROM
	flight INNER JOIN aircraft ON flight.AircraftNo = aircraft.AircraftNo
WHERE
	Airline = '대한항공';
    
# 서브 쿼리로 작성
SELECT flight.flightNo, flight.AircraftNo, Depareture, Arrival, Price
FROM flight
WHERE aircraftNo IN (SELECT AircraftNo
FROM aircraft
WHERE airline = '대한항공');

 

스칼라 서브쿼리(Scalar subquery)

• Subquery가 SELECT 절 안에서 사용되는 경우.
• 스칼라 서브쿼리의 반환 값은 하나의 레코드에서 단일 column 이어야 한다.
• 테이블의 Join이 많아 실행 계획의 제어가 어려울 경우 사용 - 대량의 데이터 조회가 아닐 때
• 매우 느리다 - 중복 for문을 돌리는 것과 같으므로 사용 자제.

# 항공 편명의 출발지와 도착지, 운영 항공사를 구하시오
SELECT flightNo, depareture, arrival, airline
FROM flight as f INNER JOIN aircraft as a ON f.aircraftNo = a.aircraftNo;

# Scala Subquery 사용
select flightno, depareture, arrival, (SELECT airline FROM aircraft as a where a.AircraftNo = f.AircraftNo) as airline
FROM flight as f;

 

인라인 뷰(Inline view)

• Subquery가 FROM 절 안에서 사용되는 경우.
• FROM 절에서 사용된 Subquery의 결과가 하나의 테이블에 대한 뷰처럼 사용.

# 인천에서 출발하고 샌프란시스코에 도착하는 항공기의 종류
SELECT DISTINCT KindOfAircraft
FROM Aircraft AS A INNER JOIN Flight AS F ON A.AircraftNo = F.AircraftNo
WHERE Depareture = '인천' AND Arrival = '샌프란시스코';

# Inline View
SELECT DISTINCT KindOfAircraft
FROM Aircraft AS A,
	(SELECT * FROM Flight WHERE Depareture = '인천' AND Arrival = '샌프란시스코') AS F
WHERE
	A.AircraftNo = F.AircraftNo;

 

상호 연관 서브쿼리(Correlated Subquery)

• Subquery가 Main Query의 필드 값과 연관되어 사용하는 query.
• Main Query의 한 row에 대해 Subquery가 한 번씩 실행됨.

# 같은 등급의 평균 나이보다 많는 나이의 승객을 이름과 나이를 구하시오

# 각 등급별 나이 평균
SELECT avg(age), grade
FROM Passenger
GROUP BY grade;

# 상호 연관 서브쿼리 사용.
SELECT passengerName, Age
FROM passenger AS p1
WHERE age > (SELECT avg(age)
	FROM passenger AS p2
    WHERE p1.grade = p2.grade);

 

---

Chapter 6: 집단 연산

집계 함수

• SELECT절에서 산출되는 column의 값에 대한 집계를 위해 사용됨.
• COUNT([DISTINCT] A): A 필드에 있는 [유일한] 값들의 수
• SUM([DISTINCT] A): A 필드에 있는 [유일한] 값들의 합
• AVG(DISTINCT] A): A 필드에 있는 [유일한] 값들의 평균
• MAX (A): A 필드에 있는 최대값
• MIN (A): A 필드에 있는 최소값

GROUPT BY절과 HAVING절

• Relation의 Tuple들을 여러 그룹으로 나누어서 각 그룹별로 집단 연산을 수행.
• 그룹의 수는 릴레이션의 인스턴스에 따라 좌우됨.
• GROUP BY: Column의 값을 기준으로 Tuple을 그룹화함.
• HAVING: group에 대한 조건식을 지정.

# 각 등급별로 가장 적은 나이를 구하라
SELECT MIN(Age)
FROM Passenger
GROUP BY Grade;

 

---

Chapter 7: 정렬

ORDER BY를 이용한 정렬

• ORDER BY 절에 사용되는 column을 기준으로 데이터를 정렬.
  • DESC(오름차순), ASC(내림차순, default)

# 가장 나이가 많은 승객의 이름과 등급, 나이를 구하시오
SELECT PassengerName, Grade, Age
FROM Passenger
WHERE Age = (SELECT MAX(age) FROM Passenger);

# ORDER BY 사용.
SELECT PassengerName, Grade, Age
FROM Passenger
ORDER BY age DESC
LIMIT 1;

 

INDEX를 이용한 정렬

• INDEX: 데이터의 검색 속도를 높이기 위한 보조적인 자료구조.
• INDEX를 지원하는 자료구조에 생성되며, 테이블에 지정된 column의 데이터를 기존으로 정렬된 데이터를 저장함.
• Query에서 Index를 사용하면, Index에 포함된(정렬된) 데이터를 기준으로 데이터를 정렬함.

# 승객의 이름, 등급, 나이를 등급순으로 내림차순 정렬하여 구하시오
SELECT PassengerName, Grade, Age
FROM Passenger
ORDER BY Grade;

# Passenger 테이블의 Grade column에 대한 INDEX 생성
CREATE INDEX idx_passenger_grade ON Passenger(grade);

# Query Optimizer가 사용하도록 query를 지정.
SELECT PassengerName, Grade, Age
FROM Passenger
WHERE Grade > 0;

 

---

Chapter 8: 삽입

INSERT 문

• Table에 Tuple을 삽입하기 위해 사용.

INSERT INTO <Table Name>
[(Column Name 1, Column Name2, … Column Name n)]
VALUES (Value 1, Value 2 … Value 3)

# 테이블의 컬럼 목록 명시.
INSERT INTO Passenger
(PassengerNo, PassengerName, Grade, Age)
VALUES (8, ‘조지훈’, 9, 43);

# 테이블의 컬럼 목록 명시 X
INSERT INTO Passenger
VALUES (10, '박목월', 63);

 

하나의 INSERT 문으로 여러 행 삽입

# 삽입될 데이터의 형식을 지정한 후, 여러 데이터를 삽입.
INSERT INTO <Table Name>
[(Column Name 1, Column Name2, … Column Name n)]
VALUES (Value 1, Value 2 … Value n),
(Value 1, Value 2 … Value n),
(Value 1, Value 2 … Value n)

INSERT INTO Aircraft
(AircraftNo, KindOfAircraft, Airline)
VALUES
(106, '에어버스 A330', '대한항공'),
(107, '에어버스 A321', '아시아나 항공'),
(108, '보잉 737', '제주항공');


# SELECT 질의의 결과로 산출되는 결과를 테이블에 삽입.
INSERT INTO <Table Name>
[(Column Name 1, Column Name2, … Column Name n)]
SELECT <Column 1, Column 2, Column n>
FROM <Table Name>
[WHERE clause]

CREATE TABLE tempPassenger
SELECT P.PassengerNo, PassengerName Grade, Age, FlightNo, ReservedDate
FROM Passenger AS P INNER JOIN Reservation AS R ON P.PassengerNo = R.PassengerNo;

 

---

Chapter 9: 갱신

UPDATE 문

• 테이블의 데이터를 갱신하기 위해 사용.
• Tuple 전체가 아닌, 데이터 단위(cell)로 데이터 갱신.
• 테이블 내에서 조건에 해당하는 모든 Tuple의 지정된 데이터를 모두 갱신.

UPDATE <Table Name> SET
[(Column Name 1 = VALUE,
Column Name2 = VALUE, …
Column Name n = VALUE)]
[WHERE Clause]

# 안중근 승객의 등급을 9로 갱신하시오
UPDATE Passenger SET
Grade = 9
WHERE PassengerNo = 3;

# 이외수 승객의 등급을 10으로, 나이를 60으로 갱신하시오”
UPDATE Passenger SET
Grade = 10,
AGE = 60
WHERE PassengerNo = 9;

# 항공편을 한 번이라도 이용한 적 있는 고객의 등급을 1씩 증가하여 갱신하시오
# MySQL은 업데이트 하려는 테이블은 자신 테이블의 SELECT 결과로 업데이트 할 수 없다는 제약이 있어서
# 임시 테이블을 만들어서 업데이트하는 방법을 사용해야함.
SET SQL_SAFE_UPDATES = 0;
UPDATE Passenger SET
Grade = Grade + 1
WHERE PassengerNo IN
(SELECT DISTINCT PassengerNo FROM
(SELECT p.PassengerNo
FROM Passenger AS p INNER JOIN Reservation AS r ON p.PassengerNo = r.PassengerNo) tmp);

UPDATE Passenger SET
Grade = Grade + 1
WHERE PassengerNo IN
(SELECT DISTINCT p.PassengerNo
FROM Passenger AS p INNER JOIN Reservation AS r ON p.PassengerNo = r.PassengerNo)

 

---

Chapter 10: 삭제

DELETE 문

• 테이블의 데이터를 삭제하기 위해 사용.
• Tuple 단위로 데이터가 삭제됨.
• WHERE 조건이 명시되지 않은 경우, 테이블 내 모든 Tuple 삭제.

DELETE FROM <Table Name>
[WHERE Clause]

# 나이가 60세 이상인 승객을 삭제하시오
DELETE FROM Passenger
WHERE Age >= 60;

# 한번도 운항한 적 없는 비행기를 삭제하시오
# MySQL은 삭제하려는 테이블은 자신 테이블의 SELECT 결과로 삭제할 수 없다는 제약을 가지고 있어서
# 임시 테이블을 만들어 업데이트 하는 방법을 사용해야 한다.
DELETE FROM Aircraft
WHERE AircraftNo NOT IN (
SELECT AircraftNo FROM (
	SELECT DISTINCT a.AircraftNo
	FROM Aircraft AS a INNER JOIN Flight as f ON a.aircraftNo = f.aircraftNo) tmp);
    
DELETE FROM Aircraft
WHERE AircraftNo NOT IN (
SELECT DISTINCT a.AircraftNo
FROM Aircraft AS a INNER JOIN Flight as f ON a.aircraftNo = f.aircraftNo);

 

https://github.com/Joo-v7/NHN-Academy-RDB

GitHub - Joo-v7/NHN-Academy-RDB

 

---

 

 

출처: https://github.com/gikpreet/class-relational_database

GitHub - gikpreet/class-relational_database