[스터디12] 01. SQL 시작하기 - MySQL

목차

 

1장. 준비하기

1.1 샘플 데이터베이스에 대해

온라인 서점 '북원스' 운영 시나리오

• 고객이 웹 사이트를 방문한다.
• 고객은 자신의 세부 정보를 등록하기도 한다.
• 그다음 장바구니에 한 권 이상의 책을 추가한다.
• 이후 결제까지 넘어가 책을 구매한다.
• '북웍스'는 책을 확보한 후 고객에게 배송한다.

1.2 설정하기

1.2.1 데이터베이스 관리 소프트웨어

DBMS

• PostgreSQL
• MariaDB/MySQL
• Microsoft SQL Server(MSSQL)
• SQLite
• Oracle

1.2.3 샘플 데이터베이스

1. 샘플 데이터베이스를 만든다. 그리고 데이터베이스에 연결한다.
   
   • CREATE DATABASE bookworks;
2. DBMS 옵션을 설정한다. 파일을 다운로드하고 압축을 풀어준다. 
3. 데이터베이스를 사용해 새로운 데이터베이스에 연결한다. 스크립트 파일을 실행한다.

1. 데이터베이스 생성하기

2. 파일 다운로드하기

3. 다운로드한 파일 실행하기

스크립트 파일 분석

1. 데이터베이스 및 테이블 생성: 고객(customers), 저자(authors), 도서(books), 장르(genres), 판매(sales), 판매품목(saleitems), VIP 등 핵심 엔터티를 정의하고 기본 키, 외래 키, 제약 조건(UNIQUE, CHECK 등)을 설정한다.
2. 보조 데이터 삽입: 국가(countries)와 도시/마을(towns) 데이터를 별도 테이블에 저장해 고객 주소와 연결하고, 대규모 INSERT 구문으로 실제 데이터를 채운다.
3. 관계 및 무결성 관리: ALTER TABLE을 이용해 외래 키, NOT NULL, DEFAULT, CHECK 조건 등을 점진적으로 추가하고, NULL 값을 보정(coalesce, update)하며, JOIN을 통해 연관 데이터를 조회한다.
4. 뷰(View)와 인덱스 생성: customerdetails, catalogue 같은 뷰를 정의해 통합 조회를 지원하고, 성능 향상을 위해 CREATE INDEX로 고객 이름, 도서 제목, 저자명 등에 인덱스를 부여한다.

# 1. 데이터베이스 및 테이블 생성
CREATE TABLE authors (id INT AUTO_INCREMENT PRIMARY KEY, givenname VARCHAR(40), familyname VARCHAR(40));
CREATE TABLE books (id INT AUTO_INCREMENT PRIMARY KEY, authorid INT REFERENCES authors(id), title VARCHAR(255), published INT, price NUMERIC(4,2));
ALTER TABLE customers ADD CONSTRAINT ck_customers_postcode CHECK (postcode RLIKE '[0-9]{4}');

# 2. 보조 데이터 삽입
CREATE TABLE countries (id CHAR(2) PRIMARY KEY, name VARCHAR(64), alpha3 CHAR(3));
INSERT INTO countries(id, name, alpha3) VALUES ('au','Australia','AUS');

CREATE TABLE towns (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40), state VARCHAR(3), postcode CHAR(4), UNIQUE(name,state,postcode));
INSERT INTO towns(id, name, state, postcode) VALUES (13,'Canberra','ACT','2600');

# 3. 관계 및 무결성 관리
ALTER TABLE saleitems MODIFY quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0);
UPDATE saleitems SET quantity = 1 WHERE quantity IS NULL;
SELECT s.id, c.email FROM sales s JOIN customers c ON s.customerid = c.id;

# 4. 뷰(View)와 인덱스 생성
CREATE VIEW customerdetails AS
SELECT c.id, c.email, t.name AS town, t.state, t.postcode
FROM customers c LEFT JOIN towns t ON c.townid = t.id;

CREATE VIEW catalogue AS
SELECT b.id, b.title, CONCAT(a.givenname,' ',a.familyname) AS author, FORMAT(b.price,2) AS price
FROM books b LEFT JOIN authors a ON b.authorid = a.id
WHERE b.price IS NOT NULL;

CREATE INDEX ix_authors_name ON authors(familyname, givenname, othernames);

1.3 알 수도 있는 내용

1.3.1 몇 가지 철학적 개념

• 데이터베이스: 데이터의 집합
• 데이터는 '질문'이고 값은 '답'이라고 생각한다.

1.3.2 SQL 작성하기

• SQL에서는 공백을 충분히 사용하는 것이 좋다.
• SQL 문은 세미콜론(;)으로 끝난다.
• SQL 언어는 대소문자를 구분하지 않는다.

1.3.3 SQL 기초

SELECT 열
FROM 테이블
WHERE 조건식;

1.3.4 데이터 유형

• 숫자
• 문자열
• 날짜 및 시간

1.3.5 SQL 절

• 일반적은 SELECT 문에서는 최대 6개의 절을 사용한다.
  • 작성 순서: SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY
  • 처리 순서: FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

1.3.6 열 계산하기

• 계산에 NULL이 포함되면 결과는 보통 NULL이 된다. coalesce()를 사용해 NULL을 다른 값으로 대체할 수 있다.
• AS를 사용해 별칭을 적용할 수 있다. 별칭과 이름은 고유해야 한다.
• 서브쿼리는 부가적인 SELECT 문으로 메인쿼리의 일부로 사용된다.

1.3.7 JOIN

SELECT 열
FROM 테이블 JOIN 테이블;

1.3.8 집계

• count(): 열의 행 또는 값의 개수를 계산한다.
• min() 또는 max(): 정렬을 했을 때 첫 번째 또는 마지막 순서의 값을 가져온다.
• save(), avg(), stdev() 또는 stddev(): 숫자 열의 합계, 평균, 표준 편차를 계산한다.

1.3.9 테이블로 작업하기

• CREATE TABLE 문으로 생성한다.
  • 열 이름
  • 데이터 유형: 숫자, 문자열, 날짜
  • 기타 테이블 및 열 속성: 제약 조건(NOT NULL, UNIQUE, DEFAULT, 외래키(REFERENCES)

1.3.10 데이터 조작하기

• INSERT
• UPDATE
• DELETE

1.3.11 집합 연산

• UNION
• INTERSECT
• EXCEPT(Oracle에서는 MINUS)

1.4 앞으로 다룰 내용

• 2장: 데이터베이스의 테이블의 안정성과 효율성을 개선하는 방법
• 3장: 테이블이 서로 연관되는 방식과 여러 테이블로 작업하는 방법 
• 4장: 값을 조작해 값에서 더 많은 가치를 얻어내는 방법
• 5장: 데이터 요약과 분석 방법
• 6장: 쿼리 및 중간 결과를 저장하는 방법
• 7장: 여러 테이블의 데이터와 집계를 혼합하는 방법
• 6, 7, 9장: 다른 쿼리 위에 빌드해 더 복잡한 쿼리를 처리하는 방법
• 8장: 실행 중인 집계 및 순위 데이터를 데이터세트에 추가하는 방법 

 

이 글은 『실무에서 SQL을 다루는 기술』 책을 학습한 내용을 정리한 것입니다.