SQL & Database Part 2

정창민·2022년 11월 29일
sql코딩애플
0

1. 테이블과 컬럼 생성할 때 SQL 써야 약간 멋있음

DDL (Data Definition Language)

  • DB, 테이블, 컬럼 생성 / 수정 / 삭제문법

DML (Data Manipulation Language)

  • SELECT 등 데이터 다루는 문법
CREATE DATABASE 데이터베이스이름;  // db 생성
DROP DATABASE 데이터베이스이름;  // db 삭제
CREATE TABLE 테이블명 (
    컬럼1이름 datatype,
    컬럼2이름 datatype,
    컬럼3이름 datatype,
    ...등
); 


CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT
);
  • 마지막에 , (콤마) 넣으면 안됨!
CREATE TABLE new_table2 (
    id INT,
    이름 VARCHAR(100) DEFAULT '홍길동',
    나이 INT
)
  • default값 입력 가능
  • 원래 데이터 안들어오면 NULL을 채워주는데 , 위의 경우는 '홍길동'이 들어옴

DROP TABLE 테이블명  // table 삭제

ALTER TABLE 테이블명 ADD 컬럼명 VARCHAR(100);   // 컴럼 추가

ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 datatype; // 컬럼 데이터 타입 변경
  • datatype 란에 INT, VARCHAR() 등 아무거나 넣을 수 있다.
  • 하지만 이미 컬럼에 '가나다' 이런 문자가 있는 경우엔 INT 타입으로 변경하고 그런건 불가능

ALTER TABLE 테이블명
DROP COLUMN 컬럼명;
  • 특정 테이블에 있던 컬럼이 제거

문제

Q1.
유저 이름, 전화번호를 저장할 테이블을 하나 만들어옵시다. 

CREATE TABLE member (
    id INT,
    이름 VARCHAR(100),
    연락처 VARCHAR(100)
)
  • 연락처는 숫자가 아니라 왜 문자로 저장하냐고요?
    INT 컬럼에 01012341234 저장하면 1012341234만 남습니다.

Q2.
방금 만든 테이블에 '나이'를 저장할 컬럼을 추가해봅시다. 근데 기본값은 1로 설정해봅시다. 

ALTER TABLE member 
ADD 나이 INT DEFAULT 1;

Q3.
방금 만든 테이블의 '나이' 컬럼의 데이터타입을 변경해봅시다.
INT였던걸 BIGINT로 변경해봅시다.

ALTER TABLE member 
MODIFY COLUMN 나이 BIGINT;

Q4.
부끄러우니까 방금 만든 테이블을 삭제해봅시다.
이것저것 클릭질 하지말고 SQL 작성하고 실행시켜서 삭제하십시오.


2. 컬럼에 안전하게 제약 (Constraints) 주기

컬럼 만들 때 제약조건을 걸어둘 수 있다.

  • 이 컬럼은 NOT NULL 해야한다 (텅빈 데이터 집어넣지 말아라)

  • 이 컬럼에 있는 데이터들은 항상 UNIQUE 해야한다

  • 여기에 음수말고 양수만 들어오는지 CHECK 하라

  • 이 컬럼을 PRIMARY KEY로 설정하라

등 여러가지 설정이 가능하다. 

CREATE TABLE new_table (
    id INT NOT NULL,
    이름 VARCHAR(100) NOT NULL,
    나이 INT
)
  • null은 텅 비어있다는, 공백이라는 뜻
  • NOT NULL 입력해두면 나중에 이름 컬럼이나 id 컬럼에 까먹고 데이터를 안 넣으면 저장이 되지 않는다.
CREATE TABLE new_table (
    id INT UNIQUE,
    이름 VARCHAR(100),
    나이 INT
) 


CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT,
    UNIQUE(이름, 나이)
)
  • 유니크를 지정한 컴럼의 다른 행끼리의 중복을 막아 줌
  • 2개 이상의 컬럼을 넣게 될 경우 컬럼1과 컬럼2 값이 전부 같아야 저장을 막아준다.
DROP TABLE new_table; // *

CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT CHECK (나이 > 0),
)

  • DROP TABLE new_table; // (제약 설명과 관련 없는 내용)
    (테이블이 만들어져 있는데 이것저것 테스트한다고 생성하면 안되니까 시작 전에 테이블을 제거하고 다시 만드는 꿀팀)

  • 컬럼만들 때 우측에 CHECK () 넣으면 컬럼에 들어갈 값에 제약을 둘 수 있다.

  • 등호 부등호 AND OR 써서 조건식처럼 입력하면 됨, IN 연산자도 가능

PRIMATY KEY

CREATE TABLE new_table (
    id INT PRIMARY KEY,
    이름 VARCHAR(100),
    나이 INT CHECK (나이 > 0),
)
  • 이 컬럼은 primary key 역활을 하는 컬럼입니다. 일종의 명시, 데코레이션
  • NOT NULL, UNIQUE 제약조건도 자동으로 부여
CREATE TABLE new_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    이름 VARCHAR(100),
    나이 INT,
)
  • 넣어두면 테이블에 행이 추가될 때 자동으로 알아서 1씩 증가된 값이 부여
  • PRIMARY KEY 조건 넣을 때 자주 함께 사용
CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT,
    PRIMARY KEY (id),  // 제약조건 줄 때 밑에 이렇게 써도 됨
    CHECK(나이 > 10)
) 


CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT,
    CONSTRAINT 제약조건작명 PRIMARY KEY (id),
    CONSTRAINT 제약조건작명2 CHECK(나이 > 10)
)
  • 근데 제약조건마다 작명을 해서 귀엽게 이름붙여주고 싶으면 CONSTRAINT 문법을 쓴다.
  • 이렇게 해두면 나중에 에러가 날 때 "제약조건작명2 때문에 에러가 납니다" 이런 식으로 표기
  • SQL 코드가 동작하지 않을 때 버그찾는 과정이 좀 더 편리


3. 정규형 알면 DB 설계도 가능 (1NF, 2NF)

제1정규형 (1st normal form)

여러분은 지금부터 구민 체육센터 전산시스템 만드는 개발자입니다.

일단 체육센터에 수강 등록한 사람들을 테이블로 정리하고 싶은겁니다.

그래서 테이블 하나 만들어서 이쁘게 잘 저장을 해놨습니다.

근데 '김민수' 라는 사람이 다음 날 '골프초급'을 또 수강신청한겁니다.

그럼 어떻게 데이터를 추가하죠?

▲ 하나의 셀에 데이터를 더 추가하면 어떨까요.

그래도 되긴 합니다.



근데 보통은 이렇게 해놓으면 나중에 성능이슈가 생길 수 있습니다.

저렇게 문자로 길게 저장해두면 상품목록이 길어질 수록 찾기 / 수정 / 삭제 작업이 느려집니다.

엑셀에서도 저렇게 해놓으면 사수에게 빠따맞음



실은 JSON 데이터타입 아니면 array 데이터타입 (Postgres에서 가능) 사용하면

['헬스', '골프초급']

이런 식으로 여러개의 문자나 숫자를 한 칸에 저장할 수 있는데

그거 써도 마찬가지로 문제될 수 있습니다.

왜냐면 그냥 array, JSON은 문자와 취급이 비슷해서 나중에 array 자료 안의 일부 항목만 수정하는게 매우 어렵기 때문입니다.

(그래서 수정할 때는 새 값으로 아예 갈아치우거나 그런 식으로 많이 합니다)

▲ 그래서 이렇게 만드는게 낫습니다.

하나의 셀 안에는 하나의 데이터만 보관하는 겁니다.

이 작업이 완료된 테이블을 제1정규형 (1st normal form) 이라고 합니다.

이렇게 해두면 성능문제도 없어지고 나중에 다른 컬럼을 추가할 때도 문제가 안생길 수 있습니다.



(참고)

예전에 배운 비관계형 데이터베이스들은 제1 정규화를 안하는 경우가 있습니다.

하지만 관계형 데이터베이스는 제1 정규화 안해놓으면 단점이 많아서 데이터 저장할 때 항상 하도록 합시다.

제2정규형 (2nd normal form)

아까 제1정규화 해놓은 테이블이 있습니다.

심심해서 각 프로그램마다 '가격'이랑 '납부여부'를 기록하는 컬럼도 만들어놨습니다.

잘 돌아갈 것 같은데 근데 이런 문제가 생길 수도 있습니다.

Q. '헬스' 프로그램의 가격에 오류가 있어서 6000에서 7000으로 변경되어야하면 어쩌죠?

  • 2개 각각 수정하면 됩니다.

Q. 근데 장사가 잘되어서 '헬스'를 신청한 사람이 100명이면 어쩌죠?

  • 100개 행을 전부 6000 -> 7000 이렇게 바꿔야지 꼼수 그런거 없습니다.

100개 행을 고치는 불상사가 생기는게 싫다면

현재 테이블의 주제와 관련없는 컬럼은 다른 테이블로 옮기십시오.

'가격' 컬럼은 수강등록현황과 별로 상관이 없고 '프로그램' 종류에 따라서 결정될 뿐입니다.

그래서 '가격' 컬럼은 현재 테이블의 주제와 맞지 않기 때문에

그걸 잘라내서 새로운 테이블 만들어서 거기 넣어봤습니다.

이 작업이 완료된 테이블을 제2정규형 (2nd normal form) 이라고 합니다.

장점은 아까처럼 '헬스'의 가격이 수정되어야할 때 100곳을 수정할 필요가 없어짐




물론 이러면 단점도 있는데 나중에 김민수라는 사람이 얼마를 내야할지 조회하는게 귀찮아질 수 있는데

그건 나중에 SQL JOIN 문법 쓰면 됩니다.

제2정규형 - 어려운버전

제2정규형의 교과서적 정의는 partial dependency를 제거한 테이블입니다.

쉽게 설명해보자면



  1. composite primary key 라는게 있습니다.

    primary key는 행을 서로 구분할 수 있는 유니크한 데이터를 담고있는 컬럼입니다.

    근데 가끔은 하나의 컬럼만으로 primary key를 정할 수 없는 경우가 있습니다.

▲ 위의 테이블에선 (회원번호 + 프로그램) 이렇게 조합해야 primary key 역할을 수행할 수 있습니다.

'회원번호' 만으로 primary key 역할은 불가능합니다.

'프로그램' 만으로 primary key 역할은 불가능합니다.

근데 두개 합치면 primary key 역할 가능

두개 컬럼을 합친 primary key를 composite primary key 라고 합니다.




  1. composite primary key 중에 하나의 컬럼에만 종속되어 있는 따까리 컬럼을

    partial dependency가 있다고 표현합니다.

▲ 예를 들어 위의 테이블에서 보면 '가격' 컬럼은 '프로그램'에 따라서 결정될 뿐 '회원번호'과는 상관이 없습니다.

그 경우 가격 컬럼은 partial dependency가 있다고 표현합니다.

  1. partial dependency가 있는 컬럼을 다른 테이블로 빼면 제2정규형 테이블

    완성입니다.

    그래서 위의 예시의 '가격' 컬럼을 다른 테이블로 빼는 것입니다.

문제

Q1. 위 테이블에서 책가격 컬럼은 다른 테이블로 옮기는게 좋을까요?

위 테이블에서 composite primary key는 (회원명 + 책이름 + 날짜) 같군요.

근데 이 중에서 '책이름 컬럼'에만 종속되어있으니

제2정규형 테이블 만들고 싶으면 옮기는게 좋겠군요.

Q2. 위 테이블에서 회원등급 컬럼은 다른 테이블로 옮기는게 좋을까요?

위 테이블에서 composite primary key는 (회원명 + 책이름 + 날짜) 같군요.

근데 이 중에서 '회원명 컬럼'에만 종속되어있으니

제2정규형 테이블 만들고 싶으면 옮기는게 좋겠군요.

Q3. 위 테이블에서 반납여부 컬럼은 다른 테이블로 옮기는게 좋을까요?

composite primary key는 (회원명 + 책이름 + 날짜) 3개 중에

하나의 컬럼에만 달랑 종속되진 않습니다.

(회원명 + 책이름 + 날짜) 조합마다 종속되어야하기 때문에 옮길 필요는 없습니다.



그냥 빡통식으로 쉽게 생각하면 반납여부는 [책대여내역 table] 이라는 주제와 매우 관련있어서 옮기면 안됩니다.

4. 제3정규형 (3NF) & Foreign Key

제3정규형 (3rd normal form)

제2정규형을 만족하는 테이블에서 'primary key' 또는 'composite primary key' 컬럼에 종속된게 아닌

하찮은 잔챙이 컬럼에 종속된 컬럼을 다른 테이블로 빼면

그게 제3정규형 테이블

  • 제2정규형에서 '이상구'의 출신대학이 바껴서 수정해야할 경우, 데이터가 적다면

    작업이 어렵지 않겠지만 수정해야할 항목이 많은 경우 제3정규화 작업을 하여

    원만한 db관리가 필요

  • 데이터를 제3정규화하여 1곳만 수정하면 되는 모습

제3정규형 - 어려운버전

제2정규형 테이블에서 transitive dependency도 제거해버리면 그게 제3정규형입니다.

transitive dependency는

  1. composite primary key 또는 primary key 역할을 하는 컬럼이 있고

  2. 거기에 직접 종속된 컬럼A가 있고

  3. 또 거기에 직접 종속된 하찮은 컬럼B가 있으면

컬럼B가 primary key 컬럼에 transitive dependency 가 있다고 표현합니다.

그래서 그 컬럼B를 다른 테이블로 옮기라는 소리입니다.

옮기면 제3정규형을 만족하는 테이블이 완성됩니다.

primary key, foreign key 항상 넣으면 좋음

정규화하려고 테이블을 쪼갤 때가 많습니다. 그 경우 주의점은

  1. 테이블마다 항상 primary key 역할을 하는 컬럼을 넣어두는게 좋습니다. 'id' 컬럼 이런거요

  1. 다른 테이블에 있는 컬럼내용을 가져다쓸 때도 primary key를 사용하는게 좋습니다.

    ▲ 예를 들어서 아까처럼 제3정규화 하느라 이렇게 테이블을 분리해놨다고 칩시다.
     이 테이블을 쓸만하게 고쳐보면

  • 테이블마다 primary key 역할을 할 수 있는 id 컬럼같은거 하나 만들어두면 좋습니다.

  • 다른 테이블에 있는 컬럼내용을 가져다쓸 때도 primary key를 사용하는게 좋습니다.


지금 왼쪽 테이블보면 '강사' 컬럼이 있습니다.

거기에 강사 이름이 들어가야하는데 강사 정보는 오른쪽 테이블에 들어있군요.

그럴 경우 강사 이름을 그대로 적는것 보다는 강사의 id를 적는게 좋습니다.

이러면 나중에 동명이인 강사가 생겨도 명확히 구분할 수 있으니까요.



저기서 강사id 컬럼을 전문용어로 foreign key 라고 합니다.

어려운건 아니고 다른 테이블에 있던 primary key를

다른 테이블에 사용할 때는 foreign key라고 부릅니다.

참고로 알아둡시다.

Foreign key 등록하는 법

  • foreign key가 등록된 모습


5. 테이블 2개 합쳐서 출력은 INNER JOIN

▲ 위 사진처럼 프로그램, 가격, 강사, 출신대학까지 이렇게 4개의 컬럼을
 한 번에 출력하고 싶으면

SELECT *
FROM program, teacher

  • 이상하게 출력됨...

  • 위와 같은 원리로 (이중 for문과 비슷한듯?) 15줄이 출력됨

SELECT 프로그램, 가격, 강사, 출신대학
FROM program, teacher
WHERE program.강사id = teacher.id

INNER JOIN문으로 구현

SELECT 출력할컬럼들
FROM 테이블1 INNER JOIN 테이블2
ON 조건문 
  
SELECT 프로그램, 가격, 강사, 출신대학
FROM program INNER JOIN teacher
ON program.강사id = teacher.id
  • INNER JOIN 결과를 조건에 맞는 행만 필터링하고 싶은 경우 ON 조건문을 사용

테이블 3개 이상 합쳐서 출력하고 싶은 경우

SELECT *
FROM 테이블1, 테이블2, 테이블3
WHERE 조건1 AND 조건2 ...
  
SELECT *
FROM 테이블1 
INNER JOIN 테이블2 ON 조건1
INNER JOIN 테이블3 ON 조건2

문제

Q1.
sales 테이블을 출력하는데 위 사진처럼 product 테이블에 있던 상품명, 가격도 함께 출력해봅시다. 

SELECT sales.id, 고객번호, 구매날짜, 상품명, 가격
FROM sales 
  INNER JOIN product 
    ON sales.상품id = product.id

Q2.
sales 테이블을 출력하는데 위 사진처럼 상품명, 가격, 고객명도 함께 출력해봅시다.

  
// 내가 짠 코드
SELECT sales.id, 고객번호, 구매날짜, 상품명, 가격, 고객명 FROM sales
INNER JOIN product
	ON sales.상품id = product.id
INNER JOIN user_table
	ON sales.고객번호 = user_table.id
ORDER BY sales.id ASC; // id순 오름차순 정렬
  
//선생님이 짠 코드
SELECT sales.id, 고객번호, 구매날짜, 상품명, 가격, 고객명
FROM sales 
  INNER JOIN product 
    ON sales.상품id = product.id 
  INNER JOIN user_table 
    ON sales.고객번호 = user_table.id

Q3.
날짜별 매출 합계를 출력하도록 2번에서 작성한 코드를 업그레이드 해봅시다. 

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SELECT sales.id, 고객번호, 구매날짜, 상품명, SUM(가격), 고객명 FROM sales
INNER JOIN product
	ON sales.상품id = product.id
INNER JOIN user_table
	ON sales.고객번호 = user_table.id
GROUP BY 구매날짜
ORDER BY 구매날짜 ASC;  // 내가 짠 코드
  

SELECT sales.id, 고객번호, 구매날짜, 상품명, SUM(가격), 고객명
FROM sales 
  INNER JOIN product 
    ON sales.상품id = product.id 
  INNER JOIN user_table 
    ON sales.고객번호 = user_table.id
GROUP BY 구매날짜   // 선생님이 짠 코드

6. 저번시간 숙제와 LEFT, RIGHT JOIN

왼쪽 테이블 전체 + 공통된 행 출력하고 싶으면 LEFT JOIN

SELECT * 
FROM program 
  LEFT JOIN teacher
    ON program.강사id = teacher.id

오른쪽 테이블 전체 + 공통된 행 출력하고 싶으면 RIGHT JOIN

SELECT * 
FROM program 
  RIGHT JOIN teacher
    ON program.강사id = teacher.id


7. 데이터 넣거나 복사하려면 INSERT

데이터 집어넣고 싶으면 INSERT

INSERT INTO 테이블명 (컬럼명1, 컬럼명2 ...) VALUES (값1, 값2 ...) 
  
INSERT INTO product (id, 상품명, 가격) VALUES (1, '김치', 500)

출력

INSERT INTO product (상품명, 가격) VALUES ('두부', 1000) 

INSERT INTO product VALUES (3, '수박', 1500) 

INSERT INTO product VALUES (4, '참외', 2000), (5, '배추', 2500); // 여러행 동시 삽입

  • 모든 컬럼말고 일부 컬럼에만 데이터 저장할 수도 있다.

  • 모든 컬럼에 빠짐없이 데이터를 넣는 경우 컬럼명은 생략할 수 있다.
    다만 나중에 새로운 컬럼을 누가 만들거나 그러면 문제될 수 있으므로
    컬럼명도 쓰는게 낫다.


다른 테이블에 있던 데이터 복사도 가능

INSERT INTO product SELECT 컬럼명 FROM 테이블명 

INSERT INTO product (id, 상품명, 가격)
SELECT id, 상품명, 가격 FROM product2;
  • product2 테이블 안에 컬럼들을 product로 삽입

테이블 복사문법 2

CREATE TABLE 새로운테이블명 SELECT * FROM 기존테이블명

CREATE TEMPORARY TABLE 새로운테이블명 SELECT * FROM 기존테이블명
  • temporary 사용 시, 임시 테이블을 생성
  • 세션이 끝나면 자동으로 테이블이 삭제

8. 수정 삭제는 UPDATE / DELETE

UPDATE / SET

특정 행의 데이터를 수정하고 싶으면 UPDATE 문법

UPDATE 테이블명 
SET 컬럼1 = 값, 컬럼2 = 값
WHERE 조건식  

UPDATE product
SET 가격 = 5000, 상품명 = '단무지'
WHERE id = 1;


UPDATE product
SET 가격 = 가격 + 100 // set 옆에 등호는 대입한다는 뜻
WHERE id = 1
  • 1행의 상품명을 '단무지', 가격을 5000으로 바꾼다.

삭제하려면 DELETE FROM

특정 row를 아예 삭제하고 싶으면 DELETE 사용

DELETE 
FROM 테이블명 
WHERE 조건식
  • 다른 테이블에서 Foreign key로 사용중인 데이터는 삭제가 안됨
  • WHERE 조건식 생략하면 모든 행이 수정되는 불상사가 일어남

응용 : JOIN & UPDATE

UPDATE A INNER JOIN B 
	ON 쪼인조건

SET 수정할내용
WHERE 조건식

응용 : JOIN & DELETE

DELETE A 
FROM A INNER JOIN B 
	ON 쪼인조건
  
WHERE 조건식
  • A 테이블과 B 테이블 중 어떤 테이블의 행을 지울지는 DELETE 우측 기입

UPDATE user_sales 
SET email = IF(first_name = Solly, 'admin@test.com', 'test@test.com') 
WHERE email = '';  // 공백

Q4. 방금 테이블에서 sales 부분이 NULL인 행을 일괄 삭제하려면 어떻게할까요?

DELETE 
FROM user_sales 
WHERE sales IS NULL
profile
정창민
안녕하세요~!
이전 포스트

SQL & Database Part 1

다음 포스트

SQL & Database Part 3

0개의 댓글