SQL
Designed by Freepic
데이터베이스DATABASE, DB
- 데이터베이스는 데이터들의 집합을 의미한다.
- DBMSDatabase Management System는 데이터베이스를 관리/운영하는 소프트웨어이다.
- 종류
- 계층형Hierarchical DBMS
- 망형Network DBMS
- 관계형Relational DBMS, RDBMS⭐️
- 도구
- MySQL, MariaDB, PostgreSQL, Oracle, SQL server, DB2, Access, SQLite 등
- MySQL, MariaDB, PostgreSQL, Oracle, SQL server, DB2, Access, SQLite 등
- 종류
- SQLStructured Query Language은 RDBMS에서 사용되는 언어이다.
- 국제표준화기구에서 지정한 표준 SQL가 기반이 되므로 여러 DBMS의 공통적인 부분이다.
(소프트웨어 별로 특성을 고려해서 다른점도 있다) - SQL 혹은 시퀄 이라고 발음한다.
초기에는 SEQUELStructured English Query Language라는 이름이었고 이를 "시퀄"이라고 발음해서, 현재도 시퀄로 줄여 말하는 경우가 많다.
SEQUEL이 SQL로 바뀐 이유는 이미 사용하는 상표가 있었기 때문이라고…
- 국제표준화기구에서 지정한 표준 SQL가 기반이 되므로 여러 DBMS의 공통적인 부분이다.
데이터베이스 모델링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
1 | CREATE TABLE `shop_db`.`member` ( |
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가 아닌 쿼리로 진행하겠다.
1 | CREATE TABLE `shop_db`.`product` ( |
이로써 회원 목록 (member)와 제품 목록 (product) 테이블을 생성하였였다.
3. 데이터 입력하기
위 쇼핑몰 회원 목록 데이터를 입력해보자.
SQL에서 데이터 입력에는 INSERT
예약어를 사용한다. 데이터는 행row 단위로 입력한다.
1 | INSERT INTO `shop_db`.`member` (`member_id`, `member_name`, `member_addr`) VALUES ('tess', '나훈아', '경기 부천시 중동'); |
제품 테이블도 데이터를 입력하자.
1 | INSERT INTO `shop_db`.`product` (`product_name`, `cost`, `make_date`, `company`, `amount`) VALUES ('바나나', '1500', '2021-07-01', '델몬트', '17'); |
생성된 테이블을 확인해보자. 테이블을 조회할 때는 SELECT
예약어를 사용한다.
1 | SELECT * FROM shop_db.member; |
참고
지금은
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 | CREATE INDEX idx_member_name ON member(member_name); |
해당 쿼리는 Non-Unique Key Lookup
을 사용하였고 Cost는 0.35로 감소한 것을 확인할 수 있다.
이런 조회 방법을 인덱스 스캔Index scan 이라고 한다.
뷰
뷰
는 테이블을 직접 보여주는 것이 아닌 바로가기 같은 개체이다.
다음의 쿼리는 VIEW
를 member_view
라는 이름으로 생성하였다.
이는 member
테이블을 가리킨다. 그 다음, member_view
를 조회해보았다.
1 | CREATE VIEW member_view |
결과
아무리 살펴봐도 똑같다. 왜냐하면 바로가기니까.
뷰
를 사용해도 눈으로 보는 사용자는 사용하기 전과 차이가 없다.
그렇다면 왜 사용하는걸까? 다음의 장점이 존재한다.
- 보안에 도움이 된다.
- 긴 SQL을 간략하게 해준다.
스토어드 프로시져
Stored procedure는 SQL 쿼리를 묶어주는 역할을 한다. 스크립트 처럼 사용할 수 있고, 연산식, 조건문, 반복문도 포함시킬 수 있다.
예시
다음의 쿼리는 2개의 테이블에서 각각 조회를 하는 쿼리이다.
1 | SELECT * FROM member WHERE member_name = "나훈아"; |
다음은 PROCEDURE
를 생성하는 쿼리이다. DELIMITER
구분자가 있는데 이는 PROCEDURE
를 묶는 개념이고, BEGIN
, END
안에 SQL 쿼리를 넣어줘야 한다는 것만 알고 넘어가자.
1 | -- DROP PROCEDURE myProc; 생성한 PROCEDURE를 지울 떄에는 DROP을 사용하면된다. |
Procedure 실행. 실행에는 CALL
예약어가 사용된다
1 | CALL myProc(); |
결과는 처음의 2개 쿼리와 같다.
데이터베이스, DBMS, 데이터베이스 살짝 맛보기, 데이터베이스 객체에 대해 정리하였고 여기서 마치겠습니다.
읽어주셔서 감사드립니다.