DB (4) MySQL 튜토리얼 (2) UPDATE, DELETE, 테이블 생성 및 데이터 삽입

(https://www.udemy.com/course/100-2022-web-development)

 

---

 

How SQL gets executed in PostgreSQL, "Seven Databases in Seven Weeks" 32p, E. Redmond

 

- Find online POSIX regex documentation

 

 

---

 

 

저번시간까지

MySQL workbench에서

로컬DB에 연결해 스키마와 테이블을 만든 뒤

Insert와 Select 명령어를 사용해보는 시간을 가졌다.

 

데이터베이스상에서 기본적으로 요구하는 기능은

CRUD, Create Read, Update, Delete이고

 

SQL류의 문법에서는 Data Control Language에 해당하는 네가지 문법이 존재한다.

C : INSERT INTO tablename VALUES (id, name, ...)

R : SELECT * FROM tablename WHERE id >= 2

U : UPDATE tablename SET name = 'new name' WHERE id = 1

D : DELETE FROM tablename WHERE id = 1

 

조건문인 WHERE가 많이 쓰이는 것을 볼 수 있다.

 

이러한 기능들을 이용해서

SQL스크립트를 작성하고, 저장하여 

필요할 때 마다 실행을 한다면 MySQL을 통한 DB를 성공적으로 제어할 수 있을 것이다.

 

---

 

지금까지 SQL이 어떻게 작동하는지 알아보았다.

이제 이를 통해 실제 스키마와 테이블을 작성하여

더 본격적으로 SQL를 사용해보자.

 

---

 

SQL에 저장할 때에는 데이터를 정규화된 형식(Normalized Form)으로 저장한다.

하나만 저장해도 되는 데이터를.. 

이쪽 테이블에도 저장하고 저쪽 테이블에도 저장해 놨다가

나중에 수정이 필요할 때 두번 수정하는 것보다

한 테이블에 저장해놓고 다른 테이블에서 참조하도록 만드는 것이

유지보수적으로, 보안적으로 우수하기 때문이다.

 

그렇기 때문에 일반적인 DB를 설계하고

SQL을 작성할 때에는

상당히 많은 수의 테이블과 심플한 데이터를 저장한 뒤

Multiple Table을 쿼리를 이용해서 참조하도록 만들 것이다. 

 

---

 

레스토랑의 주소에 관한 정보를 담는 테이블을 만들어보자.

하나의 튜플은 id, street name, street number, postal code, country 의 속성을 가져야 한다

 

1.

우선 새로운 테이블을 추가하자

SQL쿼리를 실행해서 테이블을 만들 수 있지만,

워크벤치에서 좀 편하게 만들어주자.

 

Tables에서 Create Table을 해주자.

 

 

2.

addresses라는 테이블이름을 설정해 준뒤,

id 칼럼을 추가해서, Primary Key, Not Null, Auto Increment 옵션을 추가해주자.

id는 identification을 위해 각 튜플의 유일성을 만족시켜야 하며

Null값이 아님, 자동증가 기능은 달려있는게 편하기 때문이다.

 

 

 그 이후에도 계속해서 속성들을 추가해주자.

Not Null옵션을 달고

문자열이 적절한 속성들은 문자열로 데이터타입을 지정해주고

숫자값만 있는 속성은 정수형으로 데이터타입을 지정해주자.

속성을 잘못만들면 Delete로 지워주자.

 

이러한 속성의 이름을 네이밍하는 컨벤션(관례)로서

postalCode와 같은 파스칼케이스가 아니라

postal_code와 같은 스네이크케이스를 사용하는 것이 좋다.

 

이러한 설정을 마치고 Apply를 누르면

다음과 같이 DDL로 깔끔하게 번역된 SQL문을 확인할 수 있다.

 

---

 

 

비슷한 과정을 거쳐 type테이블도 만들어주자.

 

---

 

3.

테이블을 잘못만들었다고 치자.

 

테이블을 지우고 새로 만들고 싶다면, DROP 기능을 사용하면 된다.

가령, 이전의 restaurant 테이블에서 country 속성을 잘못 작성하였다면..

DROP을 하고 새로 테이블을 작성해줄 수 있다.

 

참고로 한번 수행하면 되돌릴 수 없는 작업이니 신중하게 수행해야한다.

 

 

그러면 워크벤치에서 "진짜 삭제할 거에요? SQL Code Review 해봐"

라고 하는데, 리뷰하기를 선택하면

 

위와 같이 DROP TABLE이라는 DDL 문구가 보이게 된다.

사실 삭제하지 않아도 ALTER로 수정하면 된다.

근데 강의에서 삭제하라고 하니 삭제해주자.

 

 

---

 

4.

새로 restaurants table을 만들어주자.

name 속성과 address_id 속성을 만들어줬다.

 

창의 아랫부분에 

Columns, Indexes, Foreign keys 등등

아직까지 다뤄보지 못한 기능들이 많이 보인다.

 

그 중에 Foreign Key를 클릭해보자.

 

 

Foreign Key, 외래키 탭에서는

외래키 릴레이션에 대해 설정해줄 수 있다.

외래키로 연관된 특정한 레코드가 삭제되었을때, 이 레코드는 어떻게 작동되는지를

설정해 줄 수 있는데,

 

중요한 내용이나 따로 개설된 심화강의를 통해서 배우도록하고

그냥 넘어가고 Column탭으로 다시 돌아오도록 하자.

 

---

 

5.

address_id와 type_id 속성을 추가하고,

이후에 다른 테이블에서 addresses와 types와의 릴레이션을 생성하기 위한 초석을 깔아주도록 하자.

 

앞에서 우리는 addresses와 types라는 테이블을 생성해주었고,

이 restaurant table의 속성인 address_id와 type_id를 외래키로 사용하여

두 테이블의 레코드들을 식별해주고 싶다.

 

그러기 위해서, 두 속성에 NotNull 옵션을 넣고 테이블을 만들어주자.

 

 

그래서 새로만든 restaurant table은 다음과 같은 DDL로 만들어진다.

 

 

---

 

마지막으로 review 테이블을 작성해주자.

 

리뷰에 들어갈 내용에 해당하는 text속성에는 datatype을 TEXT로 설정해주자.

사실 블로그 리뷰글에 가까울 정도로 varchar와는 다른 긴 텍스트를 저장하기 위한 타입이지만.. 그냥 써보자.

 

date 속성에는 DATETIME 데이터타입을 선택하고, 

디폴트 값에 CURRENT_TIMESTAMP, 즉 현재 시스템 시간을 기입하도록 하자.

 

 

올바르게 기입했으면 아래와 같은 DDL문이 만들어진다.

 

---

 

6.

 

이제 스키마에서 테이블과 테이블의 속성을 다 정의해줬다. 

원하는 데이터를 넣어보도록 하자.

 

위와 같은 테이블들에 대해서..

먼저 types테이블에 아래와 같이 레코드를 입력하자.

INSERT INTO types (name) VALUES ('Italian');
INSERT INTO types (name) VALUES ('American');
INSERT INTO types (name) VALUES ('German');
INSERT INTO types (name) VALUES ('Austrian');

 

그후 SELECT * FROM types를 해보면..

 

레코드가 잘 들어간 것을 볼 수 있다.

 

---

 

addresses에도 데이터를 넣어주고...

INSERT INTO addresses (street, street_number, city, postal_code, country) VALUES (
	'Teststreet',
    '23a',
    'Munich',
    81541,
    'Germany'
)

 

 

---

 

restaurant 테이블에도 데이터를 넣는다.

 

INSERT INTO restaurants (name, address_id, type_id) VALUES ('Schnitzelhaus', 1, 3)

 

types 테이블에서 id 3은 German을 의미한다.

addresses 테이블에서 id 1은 teststreet을 의미한다.

 

restaurants 테이블에서 types_id와 addresses_id에 

외부 테이블 id에 해당하는 정수를 가지게 한채로

레스토랑에 관한 레코드들을 저장하는 셈이다.

 

---

 

잘못된 레코드를 삽입했을 때에는..

DELETE FROM을 이용해서 튜플을 지워주도록 하자.

 

 

---

 

특정 튜플의 데이터를 수정하고 싶을 때에는..

UPDATE SET을 이용하자.

 

 

 

 

 

--

 

마지막으로 review 테이블에 레코드를 삽입해주자.

 

INSERT INTO reviews (reviewer_name, rating, text, restaurant_id) VALUES(
	'Maximilian Schwarzmuller',
    4,
    'This was awesome!',
    2
)

 

restaurant_id 가 2번인 식당에 대해서, 위와 같은 평가를 남겼다는 의미이다.

 

 

 

---

 

 

 

SELECT * FROM reviews WHERE rating>3

과 같이 조건을 걸어서 검색할 수도 있다.

 

---

 

이제 더미 데이터 세팅을 끝냈다.

이제 이 데이터들을 이용해서

데이터를 쿼리하는 방법을 알아보도록 하자.