SQL의 기본 개념과 문법

  보통, 현업에서 데이터베이스를 구축할 때 가장 먼저 프로젝트에 맞는 스키마를 정의하는 작업을 진행한다. 이렇게 전체적인 구조를 잡은 후에 실제로 SQL을 사용해서 테이블을 생성한다. 데이터베이스의 구조를 정의할 때 만들려는 서비스의 스펙과 데이터 일관성, 편의성, 확장성등을 종합적으로 고려해서 DB 스키마를 정의하는 것이 중요하다. 시니어 개발자와 주니어 개발자의 중요한 차이중 하나가 이렇게 데이터베이스를 설계하는 능력에 있다.

• 데이터베이스 생성: create database dbname;
• 데이터베이스 목록보기: show databases;
• 데이터베이스 목록 중에 어떤 데이터베이스를 사용하는지 확인: select database();
• 데이터베이스 사용: use dbname;
• 데이터베이스 삭제: drop database [if exist] dbname;

1. SQL의 기본 개념

  먼저 SQL의 약자는 Structured Query Language로 현업에서 쓰이는 relational DBMS의 표준언어입니다. 그래서 우리가 relational database를 만든다고 할때, SQL을 통해서 데이터베이스를 정의하고 이제 이 데이터베이스에 데이터를 CRUD 합니다. 예전에서 말했지만 이 SQL은 종합적인 데이터베이스 언어로 DDL + DML + VDL을 모두 아우르는 언어입니다. 그러면 SQL에는 주요 용어가 있는데 저번 시간에 배운 relational data model과 대칭하는 언어를 살펴보겠습니다.

 

출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

위 사진에서 처럼 relation 은 SQL에서 table을 의하고 다른 용어들도 서로 대칭되는 것을 확인할 수 있습니다. 하지만 이렇게 딱딱 구분지어서 사용하지는 않고 섞어서 쓰는 경우가 많다. ❗️relational data model에서의 relation과 SQL에서의 relation의 의미가 약간 차이가 있는데, SQL에서 relaiton은 multiset of tuples로 중복된 튜플(레코드)를 허용한다는 점입니다.

SQL & RDBMS

  SQL은 RDBMS의 표준언어지만 실제 구현에 강제가 없으며 RDBMS(oracle, mySQL 등)마다 제공하는 SQL의 스펙이 조금씩 다릅니다. 💡여기서 다시한번 집고 넘어가자면 우리는 RDBMS라는 소프트웨어가 제공하는 SQL이라는 통합된 언어를 통해 데이터베이스에 실제 데이터를 CRUD 할 수 있습니다.

2. 예제를 통해서 SQL로 데이터베이스 정의하기

  데이터베이스를 정의하고 데이터를 CRUD 하기 위해서는 '터미널'을 사용할 수 있고 workbench와 같은 툴을 사용할 수 있습니다. 여기서는 워크벤치를 활용하는 예제를 보겠습니다. 예를들어, IT관련 RDB를 만들어 보겠습니다. 즉, 부서, 사원, 프로젝트 관련 정보들(데이터들)을 저장할 수 있는 관계형 데이터베이스를 만들어보자. 사용할 RDBMS는 mySQL

• 데이터베이스 정의하기: mySQL에 company라는 데이터베이스를 정의한다.

create database company;

company DB 생성된다.

show databases; 명령어 실행 -> mySQL에 company라는 데이터베이스가 생겼다.

• : 여러 데이터베이스 중에서 company라는 데이터베이스를 사용하겠다고 말하는 것. USE 사용하기. 지금 선택된 데이터베이스를 확인하기 위해서는 select database(); 라는 명령어를 입력하면 된다.

select database();

  아직 어떤데이터 베이스를 사용할 것인지 정해주지 않았기 때문에 NULL 값이 출력된다. 그래서 데이터베이스를 지정해주기 위해서는 USE라는 키워드 사용 즉, USE company;

USE company;

USE company;명령어를 통해 mySQL에 있는 데이터베이스 중에서 company라는 데이터베이스를 선택해주고 다시 select database(); 명령어를 입력하면 NULL에서 company가 선택된 것을 확인할 수 있다.

만약 내가 정의한 데이터베이스를 삭제하기 위해서는 drop database company; 명령어를 입력해주면 된다.

drop database company;

  우리는 이제 이 company라는 데이터베이스 위에 관계형 데이터베이스를 구축할 것이다. 추가로 알아둘 것은, mySQL에서 database와 schema가 같은 뜻을 의미한다. 즉, create database company == create schema company 라는 뜻이다. 하지만 이 문법은 mySQL에서 적용되는 것이고 다른 RDBMS에서는 다르게 쓰인다는 것을 알아두자. 예를들어, postgreSQL에서는 schema가 database의 namespace를 의미한다.

• Table 정의하기: it 회사 RDB 만들기 -> 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스! 다음은 it회사 데이터베이스의 스키마를 정의 한 것이다. {id}와 {empl id, proj id}는 primary key를 의미한다. 테이블은 총 4개이고 각각의 필드값이 나타나있습니다. ❗️프라이머리 키는 그 테이블의 튜플을 식별하기 위해서 사용.

출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

• DEPARTMENT 테이블 만들기

create table DEPARTMENT(
    id INT PRIMARY KEY,
    name varchar(20) NOT NULL unique,
    leader_id int
);

  body에서 각 필드값인 id, name, leader_id를 정의하고 있다. 위에서 int, varchar(20)는 데이터 타입을 의미한다.❗️가변크기문자열(VARCHAR(n))에서 0 <= n <= 65.535이고 저장될 문자열의 길이 만큼만 저장한다. 고정크기문자열(CHAR(n))은 0 <= n <= 255이고 저장될 문자열의 길이가 지정 길이보다 작으면 나머지는 space로 채워서 저장된다. 각각의 장단점이 있으니깐 상황에 맞게 사용하면 좋다. 그리고 mySQL에서 boolean을 사용하기 위해서는 TINYINT로 대체해서 사용하면된다.

 

primary key는 중복된 값을 가질 수 없으며, NULL도 값으로 가질 수 없다. 

// 필드가 하나로 구성될 때
create table PLAYER(
	id int primary key
);

// 필드가 하나 이상으로 구성될 때,
create table PLAYER(
	team_id varchar(12),
    back_number int,
    ...
    primary key (team_id, back_number)
);

출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

  unique로 지정된 필드는 중복된 값을 가질 수 없다, 하지만 NULL은 중복을 허용할 수 있다. 이것도 사실 RDBMS마다 다르지만 mySQL과 postgreSQL 같은 경우에는 널 값의 중복을 허용한다.

출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

보통은 NOT NULL and UNIQUE를 함께 사용한다.&nbsp;출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

create table EMPLOYEE(
    id int primary key,
    name varchar(30) not null,
    birth_date date,
    sex char(1) check(sex in ('m', 'f'))
    position varchar(10),
    salary int default 50000000,
    dept_id int,
    foreign key (dept_id) references DEPARTMENT(id)
    	on delete SET NULL on update CASCADE,
    check (salary >= 50000000)
);

default는 필드의 기본값을 정의 할 때 사용한다. 따라서 새로운 레코드를 저장할 때 해당 필드에 값이 없다면 기본값으로 저장된다.

create table Orders(
	...
    menu varchar(15) DEFAULT '자장면',
    ...
);

출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

check는 필드의 값을 제한하고 싶을 때 사용한다.  아래 사진에서 salary가 제한에 걸리기 때문에 10000000을 사용할 수 없다.

출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

제약 조건에 만족해야지만 가능하다. 필드가 하나뿐이라도 위 두가지 방법 모두 가능하다.&nbsp;출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

foregin 키는 필드가 다른 테이블의 프라이머리 키나 유니크 키를 참조할 때 사용된다.

출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

primary key의 값이 삭제 변경시 foregin key의 참조값이 그대로 반영되는 것이 CASCADE이며 참조값이 삭제/변경시에 NULL값을 반영하는 것이 SET NULL이고 참조값이 삭제/변경되는 것을 금지하는 것을 RESTRICT이다. 보통은 mySQL에서이 3가지를 가장 많이 사용하게 된다. *postgreSQL에서는 5가지를 모두 지원한다.

출처:https://www.youtube.com/watch?v=c8WNbcxkRhY&list=RDCMUCReNwSTQ1RqDZDnG9Qz_gyg&index=2

추가적으로 제약 이름을 명시하면 에러가 발생했을 때, 좀 더 직관적으로 에러메시지를 알려주기 때문에 파악하기 쉽다. 나머지 테이블도 위와 비슷한 방법으로 작성해주면 된다. 그리고 department 테이블을 작성할 때는 employ 테이블이 없었기 때문에 foregin key를 사용하지 못했는데, 만약 employ 테이블을 작성 했다면 ALTER 을 사용해서 테이블을 수정하면 된다.

ALTER TABLE department ADD FOREIGN KEY (leader_id)
    references employee(id)
    on update CASCADE
    on delete SET NULL;

ALTER TABLE

❗️이 명령어는 이미 서비스 중인 table의 스키마를 변경하는 것이라 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요하다. 자짓 서비스에 악영향을 미칠 수 있기 때문이다.

DROP TABLE

만약 테이블을 삭제하고 싶을 경우, DROP TABLE 테이블_이름; 을 사용하면 된다.

 

이번 시간에는 SQL을 통해 데이터베이스를 정의하고 그것을 구조화 하는 법에대해서 알아보았다. 감사합니다.