SQL | 데이터베이스와 SQL 맛보기

2023-01-04 Comments

SQL
Designed by Freepic

데이터베이스^{DATABASE, DB}

데이터베이스는 데이터들의 집합을 의미한다.
DBMS^{Database Management System}는 데이터베이스를 관리/운영하는 소프트웨어이다.
- 종류
  - 계층형^Hierarchical DBMS
  - 망형^Network DBMS
  - 관계형^Relational DBMS, RDBMS⭐️
- 도구
  - MySQL, MariaDB, PostgreSQL, Oracle, SQL server, DB2, Access, SQLite 등
SQL^{Structured Query Language}은 RDBMS에서 사용되는 언어이다.
- 국제표준화기구에서 지정한 표준 SQL가 기반이 되므로 여러 DBMS의 공통적인 부분이다.
  (소프트웨어 별로 특성을 고려해서 다른점도 있다)
- SQL 혹은 시퀄 이라고 발음한다.
  초기에는 SEQUEL^{Structured English Query Language}라는 이름이었고 이를 "시퀄"이라고 발음해서, 현재도 시퀄로 줄여 말하는 경우가 많다.
  SEQUEL이 SQL로 바뀐 이유는 이미 사용하는 상표가 있었기 때문이라고…

데이터베이스 모델링^{Database modeling}

Waterfall Method Concept
Designed by Freepic

폭포수 모델^{Water Fall}
- 프로젝트^Project 진행에는 대표적으로 폭포수 모델을 사용한다.
- 데이터베이스 모델링은 요구사항 분석^Requirements과 시스템 설계^Design 단계에 해당된다.
데이터베이스 모델링은 데이터 특성을 테이블 형태로 정리해 놓은 것. 예를 들어, 사람의 특징은 이름, 키, 성별, 주소 등으로 만들 수 있다.
데이터베이스 모델링에 정답은 없지만, 좋은 모델링과 나쁜 모델링은 존재한다.
- 모델링 자체로도 몇 권의 책으로 낼만큼 광범위하다.
- 데이터의 특성과 실무적인 노하우도 필요하며, 이에 대해서는 별도 공부가 필요하다.
데이터베이스 구성
- DBMS
- 데이터베이스
- 테이블
  - 행^row
  - 열^column
  - 열 이름^{column name}
  - 기본 키^{Primary Key}
- SQL

데이터베이스 맛보기

데이터베이스를 구축하는 절차는 다음과 같다.

데이터베이스 생성
테이블 생성
데이터 입력/수정/삭제
데이터 조회/활용

각 단계를 간단히 살펴보자.

(MySQL과 MySQL workbench를 이용하였다.)

1. 데이터베이스 만들기

스키마^Schema는 MySQL 안의 데이터베이스를 말하고, 데이터베이스와 동일한 용어라고 생각하면 된다.

MySQL workbench에서 좌측 SCHEMAS 패널의 빈공간을 우클릭하여 Create Schema를 선택한다.
Name에 shop_db를 입력 후 Apply를 클릭하면 Apply SQL Script to Database 창에 SQL 스크립트가 생성된다.
Apply와 Finish를 진행하면 shop_db 데이터베이스가 생성된다.

여기서는 GUI를 기준으로 설명했는데, 앞으로는 코드 기반으로 설명하겠다.

Query

1	CREATE DATABASE shop_db;

2. 테이블 만들기

테이블은 행^row과 열^column로 되어있는데, 처음에는 설계를 해야한다.

열에 대한 이름, 데이터 타입, 최대 길이, Null 허용 여부^{Not Null, NN}을 지정해야한다.

쇼핑몰의 회원목록을 데이터베이스에 테이블로 저장한다고 하자. 아래에 예시 테이블이 있다.

member_id	member_name	member_addr
hero	임영웅	서울 은평구 증산동
iyou	아이유	인천 남구 주안동
jyp	박진영	경기 고양시 장항동
tess	나훈아	경기 부천시 중동

쇼핑몰 회원 목록

member_id, member_name, member_addr 의 데이터 타입, 최대 길이, Not Null 여부 등을 지정해야한다. 아래처럼 설계를 했다고 해보자.

열 이름 (한글)	영문 이름	데이터 형식	최대 길이	Not Null
아이디 (기본 키)	member_id	CHAR	8	Yes
회원 이름	member_name	CHAR	5	Yes
주소	member_addr	CHAR	20	No

회원 목록의 테이블 설계

참고

데이터 타입은 여러 형태가 있다. CHAR는 문자^character를 의미한다. 새로운 타입이 나오면 처음에 한번만 설명을 작성하겠다.

member 테이블 생성

GUI

Query

CREATE TABLE `shop_db`.`member` (
  `member_id` CHAR(8) NOT NULL,
  `member_name` CHAR(5) NOT NULL,
  `member_addr` CHAR(20) NULL,
  PRIMARY KEY (`member_id`));

Product 테이블 생성

나중에 사용할 쇼핑몰 상품 테이블도 생성하자. 표와 테이블 설계는 아래와 같다.

product_name	cost	make_date	company	amount
바나나	1500	2021-07-01	델몬트	17
삼각김밥	800	2023-09-01	CJ	22
카스	2500	2022-03-01	OB	3

제품 목록

열 이름 (한글)	영문 이름	데이터 형식	최대 길이	Not Null
제품 이름 (기본 키)	product_name	CHAR	4	TRUE
가격	cost	INT		TRUE
제조일자	make_date	DATE		FALSE
제조회사	company	CHAR	5	FALSE
남은 소량	amount	INT		TRUE

제품 목록의 테이블 설계

INT는 정수형, DATE는 날짜를 의미한다.

GUI

Query

SQL로 작성하면 아래와 같은 쿼리가 된다. 다음 부터는 GUI가 아닌 쿼리로 진행하겠다.

CREATE TABLE `shop_db`.`product` (
  `product_name` CHAR(4) NOT NULL,
  `cost` INT NOT NULL,
  `make_date` DATE NULL,
  `company` CHAR(5) NULL,
  `amount` INT NOT NULL,
  PRIMARY KEY (`product_name`));

이로써 회원 목록 (member)와 제품 목록 (product) 테이블을 생성하였였다.

3. 데이터 입력하기

위 쇼핑몰 회원 목록 데이터를 입력해보자.

SQL에서 데이터 입력에는 INSERT 예약어를 사용한다. 데이터는 행^row 단위로 입력한다.

INSERT INTO `shop_db`.`member` (`member_id`, `member_name`, `member_addr`) VALUES ('tess', '나훈아', '경기 부천시 중동');
INSERT INTO `shop_db`.`member` (`member_id`, `member_name`, `member_addr`) VALUES ('hero', '임영웅', '서울 은평구 증산동');
INSERT INTO `shop_db`.`member` (`member_id`, `member_name`, `member_addr`) VALUES ('iyou', '아이유', '인천 남구 주안동');
INSERT INTO `shop_db`.`member` (`member_id`, `member_name`, `member_addr`) VALUES ('jyp', '박진영', '경기 고양시 장항동');

제품 테이블도 데이터를 입력하자.

1
2
3

INSERT INTO `shop_db`.`product` (`product_name`, `cost`, `make_date`, `company`, `amount`) VALUES ('바나나', '1500', '2021-07-01', '델몬트', '17');
INSERT INTO `shop_db`.`product` (`product_name`, `cost`, `make_date`, `company`, `amount`) VALUES ('삼각김밥', '800', '2023-09-01', 'CJ', '22');
INSERT INTO `shop_db`.`product` (`product_name`, `cost`, `make_date`, `company`, `amount`) VALUES ('카스', '2500', '2022-03-01', 'OB', '3');

생성된 테이블을 확인해보자. 테이블을 조회할 때는 SELECT 예약어를 사용한다.

1 2	SELECT * FROM shop_db.member; SELECT * FROM shop_db.product;

참고

지금은 SELECT * FROM shop_db.member; 으로 조회하였는데,
MySQL에서는 schema에 있는 데이터베이스를 더블 클릭하면 데이터베이스 이름이 굵게 변하는데, 이는 기본 데이터베이스를 선택한 것이다. 이 때부터는 데이터베이스 명을 따로 입력하지 않고 테이블 명만 입력해도 테이블을 조회할 수 있다.
코드로는 USE shop_db;로 선택할 수 있다. 선택한 후에는 다음처럼 사용하면 된다.
SELECT * FROM member;

몇 가지 더 해보기

추가

생성된 테이블에 새로 추가가 가능하다.

1	INSERT INTO `shop_db`.`member` (`member_id`, `member_name`, `member_addr`) VALUES ('carry', '머라이어', '미국 텍사스 사막');

수정

존재하는 row를 수정할 수 있다. 수정에는 UPDATE 예약어를 사용한다.

1	UPDATE `shop_db`.`member` SET `member_addr` = '영국 런던 먹자골목' WHERE (`member_id` = 'carry');

삭제

데이터 삭제도 가능하다. WHERE 예약어를 사용하면 조건에 해당하는 row를 삭제할 수 있다.

1	DELETE FROM `shop_db`.`member` WHERE (`member_id` = 'carry');

4. 데이터 조회하기

SELECT 다음에 나오는 *^asterisk는 모든 Column을 의미하는 wild card이다. 여기에 column을 특정해서 조회할 수도 있다.

1	SELECT member_id, member_addr FROM member;

이번에는 특정 row만 추출하는 방법을 알아보자. 회원 명단에서 아이유 회원의 정보만 표시하려면 어떻게 하면 될까?

위에서 간략하게 표시했지만, WHERE 예약어는 특정 조건을 입력하여 원하는 row만 추출할 수 있게 해준다. 다음처럼 작성하면 된다.

1	SELECT * FROM member WHERE member_name = "아이유";

아래처럼 아이유 회원의 row만 조회된다.

데이터베이스 개체

데이터베이스에는 핵심 개체인 테이블 외에 다음 개체도 중요하다.

인덱스^INDEX : 조회 결과를 빠르게 해준다.
뷰^VIEW : 테이블 일부를 제한적으로 보여준다.
스토어드 프로시져^{Stored Procedure, SP} : SQL 쿼리문의 집합으로, Python의 함수 정의와 비슷하다.
트리거^Trigger: 잘못된 데이터 입력 방지.

인덱스

여러 분야에서 사용되는 용어이고, 빠르게 데이터를 찾는데 사용된다.

예시를 위해 위에서 사용한 쿼리를 가져왔다.

1	SELECT * FROM member WHERE member_name = "아이유";

이 쿼리가 어떻게 실행되었는 지는 Execution Plan을 보면 알 수 있다.

해당 쿼리는 Full Table Scan 을 사용했다. cost는 0.65이고 처음부터 끝까지 일일히 찾은 결과이다.

INDEX를 생성하고 다시 조회를 해보자.

1 2	CREATE INDEX idx_member_name ON member(member_name); SELECT * FROM member WHERE member_name = "아이유";

해당 쿼리는 Non-Unique Key Lookup을 사용하였고 Cost는 0.35로 감소한 것을 확인할 수 있다.

이런 조회 방법을 인덱스 스캔^{Index scan} 이라고 한다.

뷰

뷰는 테이블을 직접 보여주는 것이 아닌 바로가기 같은 개체이다.

다음의 쿼리는 VIEW를 member_view라는 이름으로 생성하였다.
이는 member 테이블을 가리킨다. 그 다음, member_view를 조회해보았다.

CREATE VIEW member_view
AS
	SELECT * FROM member;
    
SELECT * FROM member_view;

결과

아무리 살펴봐도 똑같다. 왜냐하면 바로가기니까.

뷰를 사용해도 눈으로 보는 사용자는 사용하기 전과 차이가 없다.

그렇다면 왜 사용하는걸까? 다음의 장점이 존재한다.

보안에 도움이 된다.
긴 SQL을 간략하게 해준다.

스토어드 프로시져

Stored procedure는 SQL 쿼리를 묶어주는 역할을 한다. 스크립트 처럼 사용할 수 있고, 연산식, 조건문, 반복문도 포함시킬 수 있다.

예시

다음의 쿼리는 2개의 테이블에서 각각 조회를 하는 쿼리이다.

1 2	SELECT * FROM member WHERE member_name = "나훈아"; SELECT * FROM product WHERE product_name = "삼각김밥";

다음은 PROCEDURE를 생성하는 쿼리이다. DELIMITER 구분자가 있는데 이는 PROCEDURE를 묶는 개념이고, BEGIN, END안에 SQL 쿼리를 넣어줘야 한다는 것만 알고 넘어가자.

-- DROP PROCEDURE myProc; 생성한 PROCEDURE를 지울 떄에는 DROP을 사용하면된다.
DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
	SELECT * FROM member WHERE member_name = "나훈아";
	SELECT * FROM product WHERE product_name = "삼각김밥";
END //
DELIMITER ;

Procedure 실행. 실행에는 CALL 예약어가 사용된다

1	CALL myProc();

결과는 처음의 2개 쿼리와 같다.

데이터베이스, DBMS, 데이터베이스 살짝 맛보기, 데이터베이스 객체에 대해 정리하였고 여기서 마치겠습니다.

읽어주셔서 감사드립니다.

SQL | 데이터베이스와 SQL 맛보기

데이터베이스DATABASE, DB

데이터베이스 모델링Database modeling

데이터베이스 맛보기

1. 데이터베이스 만들기

2. 테이블 만들기

member 테이블 생성

Product 테이블 생성

3. 데이터 입력하기

몇 가지 더 해보기

4. 데이터 조회하기

데이터베이스 개체

인덱스

뷰

스토어드 프로시져

데이터베이스^{DATABASE, DB}

데이터베이스 모델링^{Database modeling}