안녕하세요. CS지식 정리도 할 겸, 학교에서 배운 데이터베이스 개념 + "데이터베이스 개론-IT COOKBOOK" 의 책을 공부하며 알게 된 내용을 정리하려고 합니다. 지난 포스트 "관계 데이터 연산 개념 정리"에 이어 이번에는 Database language SQL 관한 개념, SQL에서 DDL(Create, Alter, Drop), DML(Select, Insert, Update, Delete) 개념을 총 정리해보려고 합니다 +_+ 주관적으로 작성된 글이기에 틀린 내용이 있을 수 있습니다.(댓글로 알려주시면 감사합니다:)
1. SQL이란?
이전 장에서 배운 관계 연산으로 실제 database에서 query를 할 수는 없습니다. database에서 insert, select, update, delete 등의 query연산을 하기 위해서는 SQL(Structured Query Language)를 사용해야 합니다. ORACLE, MySQL, SQLServer등 표준 SQL포함해서 사용됩니다. == 대부분의 DBMS에 적용 가능합니다.
C언어의 int main(void) { ... return 0; } 같이 entry point로부터 시작해서 main thread가 한 줄 한 줄 코드를 수행하는 serial 한 작업을 수행하는 게 아닙니다. 그래서 SQL은 비 절차적 언어의 특성을 지녔습니다.
즉 DBMS에 명령어를 통해 명령을 하는데 "원하는 데이터를 어떻게?" CURD 등의 작업을 수행하도록 기술하는데 초점이 맞춰져 있습니다.
SQL의 기능 | DDL, DML, DCL
이전 포스트 3편에서 DDL, DML, DCL에 관해 설명했지만 다시 간략하게 설명을 하자면,,
- DDL: 데이터 정의어. Db's table, view, index 등 db 객체에 관해 CREATE, ALTER, DROP etc... 가 가능합니다.
- DML: 데이터 조작어. Db's table, view에서 INSERT, UPDATE, DELETE, SELECT etc... 가 가능합니다.
- DCL: 데이터 제어어. GRANT, REVOKE 등의 명령어로 사용자에게 테이블 권한을 부여하거나 취소할 수 있고 보안에 사용됩니다.
이제부터 실제 database의 생성은 어떻게 하고 query문은 어떻게 작성하는지 탐구하겠습니다.
2. DDL | Create, Alter, Drop
2-1. CREATE TABLE
테이블 생성은 CREATE TABLE 입니다. 속성명, 데이터 type, constraint를 정의할 수 있습니다.
CREATE TABLE 테이블_이름(
속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값] // 1
[PRIMARY KEY (속성_리스트)] // 2
[UNIQUE (속성_리스트)] // 3
[FOREIGN KEY (속성_리스트) REFERENCES 테이블 이름(속성_리스트)] // 4
[ON DELETE OPTION] [ON UPDATE OPTION] // 5
[CONSTRAINT 이름] [CHECK(조건)] // 6
);
테이블의 정의는 CREATE TABLE( ... ); 이 형식입니다.
교수님이 알려주셨는데 [NOT NULL] 등에 사용된 '[' 와 ']' 는 optional!! 선택사항입니다.
참고로 SQL의 query문에 사용되는 명령어들은 대소문자 상관없이 입력할 수 있습니다.
1. 테이블의 속성은 이름, 데이터타입, [NN] 여부, 기본 값을 통해 테이블의 속성을 정할 수 있습니다.
2. 여러 속성 중 테이블을 식별할 수 있는 속성에 PRIMARY KEY를 부여합니다. 이때 속성 리스트는 1... N개의 속성이 올 수 있습니다.
3. UNIQUE를 통해 대체키 지정 가능합니다.
4. Join이 필요한 경우 FK를 잘 지정해야 합니다. FK(Foreign Key)는 타 릴레이션의 PK(Primary Key)와 도메인이 같아야 합니다. 참조할 테이블이름(속성리스트)으로 FK가 어느 릴레이션과 join 할지 지정할 수 있습니다. ( 키 관련 개념 정리 포스트 )
5. 결국 join query를 한 결과 또한 릴레이션입니다. 이때 여러 옵션을 통해 참조되는 릴레이션에 대한 튜플의 데이터 변경에 관한 옵션을 지정할 수 있습니다.
- ON <UPDATE | DELETE> NO ACTION: 튜플 변경 불가 // default
- ON <UPDATE | DELETE> CASCADE: 참조되는 릴레이션이 변경될 경우 해당 동작을 수행한 튜플들을 참조한느 모든 튜플도 업데이트 or 삭제
- ON <UPDATE | DELETE> SET NULL: 참조되는 릴레이션에서 튜플이 삭제 or 업데이트될 경우 해당 튜플을 참조하는 튜플의 외래 키 값은 NULL로 설정
- ON <UPDATE | DELETE> DEFUALT: 참조되는 릴레이션에서 튜플이 삭제 or 업데이트 될 경우 해당 튜플 참조하는 튜플의 외래키 값은 default value로 설정
6. 특정 속성의 제약 조건을 부여할 수 있습니다. 예를 들어 회원가입에서 아이디 속성은 최소 한 3글자에서 최대 15글자로 제한! 요런 느낌으로 말입니다. 이런 제약 조건은 무결성 보장 + 데이터를 원하는 형식에 맞게 constraint 할 수 있습니다.
예시를 통해 살펴보는 게 빨리 익힐 수 있는 것 같습니다.
1. 고객 테이블 생성
고객 테이블은 아이디, 이름, 나이, 등급, 직업, 적립금 속성으로 구성된다. 아이디 속성은 기본키다. 아이디 속성은 최소 3~ 최대 15 이하의 값을 입력받아야 한다. 이름과 등급 속성은 값을 반드시 입력해야 한다. 적립금 속성은 값을 입력하지 않으면 0이 기본적으로 입력되도록 고객 테이블 생성해보자.
Create Table CUSTOMERS(
id varchar(15) Not Null,
name varchar(10) Not Null,
age int,
rating varchar(10), Not Null,
profession varchar(30),
rewardPoints int Default 0,
Primary Key(id),
Constraint CHK_CUSTOM_ID Check (Length(id) >= 3 AND Length(id) <= 15)
);2. 주문 테이블 생성
주문테이블은 주문번호, 주문 고객, 주문 제품, 수량 속성이 있다. 주문번호는 PK이다. 주문고객 속성은 고객 테이블의 id 속성을 참조한다. 주문 제품 속성은 제품 테이블의 제품 번호 속성을 참조한다. 테이블을 생송해보자.
Create Table ORDERS(
number char(3) Not Null,
customer varchar(20),
product varchar(3),
count int,
Primary Key(number),
Foreign Key(customer) References Customers(id),
Foreign Key(product) References Products(number)
);2-2. Alter Table
테이블의 속성을 추가하거나 기존 속성을 삭제할 때 또는 테이블의 제약조건을 변경할 때는 Alter 명령어를 사용합니다.
Alter Table CUSTOMERS Add 가입날짜 date; // 1
Alter Table CUSTOMERS Drop Column 가입날짜; // 2
Alter Table CUSTOMERS Add Constraint CHK_AGE Check(age >= 20); // 3
Alter Table CUSTOMERS Drop Constraint CHK_AGE; //4
기본적으로 Alter Table 테이블 명 <Add, Drop Column, Add Constraint> 속성 ... ; 으로 이루어져 있습니다.
1. CUSTOMERS table에 가입날짜 속성을 추가합니다.
2. CUSTOMERS table 가입날짜 column을 삭제합니다.
3. CUSTOMERS table의 특정 속성 age에 Check를 통해 제약사항을 추가하고 Constraint를 통해 제약 사항의 네이밍을 CHK_AGE로 부여합니다. ( Constraint 제약조건 이름을 설정했기에 추후 Check 속성 조건 삭제 가능합니다. )
4. 3에서 설명했듯 특정 속성의 Check 조건을 부여 후 조건에 대한 네이밍을 했기에 해당 속성의 조건을 삭제할 수 있습니다.
2-3. Drop Table
Drop Table CUSTOMERS;3. DML | Select, Insert, Update, Delete
3-1. Select ... From ...
Select From ...은 database의 테이블에 특정 데이터를 검색할 때 사용되는 명령어입니다.
Select [All | Distinct] 속성_리스트 // 1
From 테이블_리스트 // 2
[Where 조건] // 3
[Group By 속성_리스트 [Having 조건]] // 4
[Order By 속성_리스트 [Asc | Desc]]; // 5
참고로 여기서 속성_리스트는 (1...n) 개의 속성이 올 수 있다는 것을 의미합니다.
1. Select ...
Select로 특정 db의 table들에 대한 속성들을 query 할 수 있습니다. 결과로 테이블이 반환됩니다. 이때 All or Distinct가 있는데 디폴트는 All입니다. Distinct의 경우 query 결과 테이블이 중복된 튜플 데이터를 허용하지 않습니다.
속성 리스트 작성 시 각각의 속성에 대해 산술 연산(+, -, *, /)도 가능합니다. 이때 결과 테이블은 속성 + 속성 or 데이터로 표현됩니다. 속성에 연산자와 피연산자들이 들어있기에 이 네이밍을 리네이밍할 수 있습니다. AS키워드를 사용하면 됩니다.
// MARK: - Case1
Select All column1 + column2
From TABLE // res : 결과 테이블의 속성이름 column1 + column2
// MAKR: - Case2
Select All column1 + column2 As sum
From TABLE // res : 결과 테이블의 속성 이름은 sum
이 경우 결과 테이블의 데이터는 column1 + column2를 한 값으로 결과 테이블의 sum 속성의 데이터가 반영됩니다.
집계 함수 Count, Max, Min 사용이 가능합니다. Sum, Avg도 가능한데 숫자에 한해서 가능합니다.
Select 주문 제품, Count(Distinct 제조업체) As "제조업체 수"
From ...2. From...
From을 통해 특정 테이블_ 리스트를 작성할 수 있습니다. where 키워드에서 join을 할 때 fk와 pk를 작성할 때 특정 pk의 table이 필요하기 때문입니다.
From CSUTOMERS, ORDERS, PRODUCTS
3. Where ... | Join, Like, InnerJoin, Outer Join, Subquery
Where 조건을 통해 Select query의 특정 속성에 대한 비교, 논리 연산자를 사용한 조건을 걸어 조건에 부합하는 데이터, 튜플들만 결과 테이블을 구성할 수 있습니다. 또한 join 연산도 가능합니다. not null인 데이터만 가져올 수도 있습니다. 조건에서 문자나 날짜 값은 single quoting ' '을 사용해야 합니다. Like를 통해 부분적으로 문자열이 일치하는 데이터를 검색할 수 도 있습니다.
위 사진 1을 바탕으로 ,,
문제 1. 주문 테이블에서 apple 고객이 15개 이상 주문한 주문 제품, 수량, 주문일자를 검색해보자!
Select 주문제품, 수량, 주문일자
From 주문
Where 주문고객='apple' And 수량 >= 15;
| 결과 테이블 | |||
| 주문제품 | 수량 | 주문일자 | |
| 1 | p03 | 22 | 22/03/15 |
문제 2. 고객 테이블에서 고객 아이디가 5자 이거나, 성이 김 씨인 고객의 고객아이디, 고객이름, 나이를 검색해보자!!
Select 고객아이디, 고객이름, 나이
From 고객
Where 고객이름 Like '김%' Or 고객아이디 Like '_ _ _ _ _';
| 결과 테이블 | |||
| 고객아이디 | 고객이름 | 나이 | |
| 1 | apple | 정소화 | 20 |
| 2 | banana | 김선우 | 25 |
| 3 | orange | 김용욱 | 22 |
| 4 | melon | 성원용 | 35 |
| 5 | peach | 오형준 | NULL |
문제 3. 판매 데이터베이스에서 고명석 고객이 주문한 제품의 제품명을 검색해보자.
여기서 고명석 고객은 고객테이블에, 제품명은 제품 테이블에 존재합니다. 그리고 주문 테이블은 고객.고객아이디 pk를 참조할 수 있는 fk가 있습니다(주문.주문고객). 또한 주문 테이블은 제품.제품번호pk를 참조할 수 있는 fk가 있습니다(주문.주문제품).
Select 제품.제품명
From 고객, 제품, 주문
Where 고객.고객이름='고명석' And 고객.고객아이디=주문.주문아이디 And 제품.제품번호=주문.주문제품;
| 결과 테이블 | |
| 제품명 | |
| 1 | 매운쫄면 |
- Join은 where 키워드로 할 수도 있지만 Inner Join, Outer Join으로도 가능합니다.
// MARK: - Case1
Select ...
From table1 Inner Join table21 On join조건
[Where Select 조건]
// MAKR: - Case2
Select ...
From table1 Left | Right | Full Outer Join table21 On join조건
[Where Select 조건]
Outer Join의 경우
Left는 table2는 일치하지만 table1에 있는 튜플 + table1에 일치하지 않는 데이터 포함해서 결과 테이블로 나옵니다. ( table1 튜플 전부 나옵니다. )
Right는 table1는 일치하지만 table2에 있는 튜플 + table2에 일치하지 않는 데이터 포함해서 결과 테이블로 나옵니다. ( table2 튜플 전부 나옵니다. )
Full은 양측 테이블 전부 다 나옵니다.
Where 키워드에 Subquery도 할 수 있습니다. subquery의 결과가 단일행인지, 다중행인지의 여부에 따라 앞에 붙는 연산자 키워드가 다릅니다.
- 단일행의 경우
문제 4. 판매 데이터베이스에서 달콤비스킷을 생산한 제조업체가 만든 제품들의 제품명과 단가를 검색해보자.
이 경우 직접 에서 달콤 비스킷을 생산한 제조업체명 데이터를 찾아볼 수 있지만 지금처럼 문제만 봤을 때는 달콤비스킷을 생산한 제조업체가 어디인지 모릅니다. 이럴 경우 subquery를 사용합니다.
Select 제품명, 단가
From 제품
Where 제조업체=(Select 제조업체
From 제품
Where 제품명='달콤비스킷');
// 위의 query와 아래의 query가 같습니다.
// Select 제품명, 단가
// From 제품
// Where 제조업체='한빛제과';
(Select 속성 리스트
From 테이블명
Where 조건);
이 결과로 '한빛제과' 제조업체가 subquery의 Select query 결과로 반환됩니다.
- 다중 행의 경우
다중행의 경우 단일행과 거의 똑같지만 앞에 연산자가 '='가 아니라 IN, NOT IN, EXISTS, NOT EXISTS, ALL, ANY or SOME 중 하나를 사용해야 합니다.
- IN: 특정 값들의 집합 안에 속하는지 여부를 확인합니다.
- NOT IN: 특정 값들의 집합 안에 속하지 않는지 여부를 확인합니다.
- EXISTS: 하위 쿼리의 결과가 적어도 하나의 행을 반환하는지 여부를 확인합니다.
- NOT EXISTS: 하위 쿼리의 결과가 어떠한 행도 반환하지 않는지 여부를 확인합니다.
- ALL: 모든 행에 대해 조건을 만족하는지 여부를 확인합니다.
- ANY or SOME: 하나 이상의 행에 대해 조건을 만족하는지 여부를 확인합니다.
문제 5. 판매 데이터베이스에서 banana 고객이 주문한 제품의 제품명과 제조업체를 검색해보자
여기서 특정 고객이 주문한 제품은 여러 개일 수 있습니다. 그래서 In을..
Select 제품명, 제조업체
From 제품
Where 제품번호 In(Select 주문제품
From 주문
Where 주문고객='banana');
// 위 query는 아래와 같습니다.
// Select 제품명, 제조업체
// From 제품
// Where 제품번호 In('p01', 'p04', 'p06');
4. Group By ...
group by는 특정 속성에 대한 조건이 부합되는 튜플을 하나의 그룹으로 묶어서 반환하는 것입니다. 이렇게 나뉜 그룹에 대한 조건은 Where을 사용하는 게 아닌 Having을 통해 다뤄져야 합니다.
바로 예제를 살펴보겠습니다.
문제 1. 고객 테이블에서 적립금 평균이 1000원 이상인 등급에 대해 등급별 고객수와 적립금 평균을 검색해보자.
Select 등급, Count(고객아이디) As 고객수, Avg(적립금) As 평균 적립금
From 고객
Group By 등급 Having Avg(적립금) >= 1000;
1. Group By에 의해 고객 테이블이 등급에 의해 각각의 튜플들이 분류됩니다. 사진에서 등급은 silver, gold, vip 세 종류가 있습니다.
| vip 등급 그룹 튜플 | |||||
| 고객아이디 | 고객이름 | 나이 | 등급 | 직업 | 적립금 |
| banana | 김선우 | 25 | vip | 간호사 | 2500 |
| gold 등급 그룹 튜플 | |||||
| 고객아이디 | 고객이름 | 나이 | 등급 | 직업 | 적립금 |
| apple | 정소화 | 20 | gold | 학생 | 1000 |
| carrot | 고명석 | 28 | gold | 교사 | 4500 |
| melon | 성원용 | 35 | gold | 회사원 | 5000 |
| silver 등급 그룹 튜플 | |||||
| 고객아이디 | 고객이름 | 나이 | 등급 | 직업 | 적립금 |
| orange | 김용욱 | 22 | silver | 학생 | 0 |
| peach | 오형준 | NULL | silver | 의사 | 300 |
| pear | 채광주 | 31 | silver | 회사원 | 500 |
이렇게 등급에 의해 그룹이 분류되고 Having 조건이 적용됩니다.
| vip 등급 그룹 튜플 | |||||
| 고객아이디 | 고객이름 | 나이 | 등급 | 직업 | 적립금 |
| banana | 김선우 | 25 | vip | 간호사 | 2500 |
| gold 등급 그룹 튜플 | |||||
| 고객아이디 | 고객이름 | 나이 | 등급 | 직업 | 적립금 |
| apple | 정소화 | 20 | gold | 학생 | 1000 |
| carrot | 고명석 | 28 | gold | 교사 | 4500 |
| melon | 성원용 | 35 | gold | 회사원 | 5000 |
이제 이 그룹에 대해서 Select query가 수행됩니다. 등급, 튜플의 개수, 적립금의 평균을 속성으로 하는 결과 table이 생성됩니다.
| 결과 table | |||
| 등급 | 고객수 | 평균적립금 | |
| 1 | gold | 3 | 3500 |
| 2 | vip | 1 | 2500 |
문제 2. 주문 테이블에서 각 주문고객이 주문한 제품의 총주문수량을 주문제품별로 검색해보자!!!!!!!
Select 주문제품, 주문고객, Sum(수량) As 총주문수량
From 주문
Group By 주문제품, 주문고객;
1. 주문 제품별로 그룹을 나눠야 합니다. ( 속성이 많아 주문제품, 주문고객, 수량으로 한정하겠습니다.)
| p01 주문 제품 그룹 | ||
| 주문제품 | 주문고객 | 수량 |
| p01 | melon | 5 |
| p01 | banana | 19 |
| p02 주문 제품 그룹 | ||
| 주문제품 | 주문고객 | 수량 |
| p02 | carrot | 8 |
| p02 | pear | 50 |
| p03 주문 제품 그룹 | ||
| 주문제품 | 주문고객 | 수량 |
| p03 | apple | 10 |
| p03 | apple | 22 |
| p03 | carrot | 20 |
| p04 주문 제품 그룹 | ||
| 주문제품 | 주문고객 | 수량 |
| p04 | banana | 15 |
| p06 주문 제품 그룹 | ||
| 주문제품 | 주문고객 | 수량 |
| p06 | banana | 45 |
| p06 | melon | 36 |
2. 주문 제품 그룹 안에서 주문 고객 별로 그룹을 나눕니다.
| p01 주문 제품, melon 주문고객 그룹 | ||
| 주문제품 | 주문고객 | 수량 |
| p01 | melon | 5 |
| p01 주문 제품, banana 주문고객 그룹 | ||
| 주문제품 | 주문고객 | 수량 |
| p01 | banana | 19 |
...
| p03 주문 제품, apple 주문 고객 그룹 | ||
| 주문제품 | 주문고객 | 수량 |
| p03 | apple | 32 |
| p03 주문 제품, carrot 주문 고객 그룹 그룹 | ||
| 주문제품 | 주문고객 | 수량 |
| p03 | carrot | 20 |
...
| 결과 테이블 | |||
| 주문제품 | 주문고객 | 수량 | |
| 1 | p02 | carrot | 8 |
| 2 | p01 | banana | 19 |
| 3 | p06 | melon | 36 |
| 4 | p03 | apple | 32 |
| 5 | p01 | melon | 5 |
| 6 | p02 | pear | 50 |
| 7 | p03 | carrot | 20 |
| 8 | p06 | banana | 45 |
| 9 | p94 | banana | 15 |
5. Order By ...
위 결과 테이블의 경우 주문제품의 차순이 무작위입니다. 그러나 때로 정렬된 결과를 얻고 싶을 때가 있습니다.
[Order By 속성리스트 [ASC | DESC]] 사용할 수 있습니다. ASC: 오름차순, DESC내림차순
Order By는 sorting입니다. 행들을 오름차순 또는 내림차순으로 정렬할 때 비교 연산자를 통해서 쉽게 정렬할 수 있습니다. 그러나 특정 행의 비교 속성이 일치하다면? 이럴 때는 해당 행의 다른 속성을 비교해주어야 합니다. 이 또한 오름차순 또는 내림차순으로 정렬할 수 있습니다.
바로 위의 결과 테이블에서 주문제품은 이미 특정 그룹별로 묶었기 때문에 유일해서 주문고객의 이름순으로 a부터 z까지 ASC로 정렬할 것입니다. 이때 튜플의 주문제품은 유일하지만 주문 고객이 같은 이름이 있습니다. 이 경우 이름이 같기에 문자열 대소비교가 불가능함으로 다른 속성을 이용해서 정렬 기준을 삼아야 합니다. 만약 주문고객이 같다면 수량을 통해서 ASC나 DESC로 새로운 정렬기준을 세울 수 있습니다.
Select 주문제품, 주문고객, 수량
From 위의결과table
Order By 주문고객 ASC, 수량 DESC;
길고 긴 Select query를 요약했습니다... 이제 남은 건 Insert, Update, Delete입니다.
3-2. Insert Into
기존 테이블에 새로운 데이터를 삽입할 때 사용되는 명령어입니다.
Insert Into 테이블명[(속성리스트)]
Values (속성값리스트);
만약 Insert Into에 테이블 명만 입력할 경우 속성 값 리스트는 테이블의 속성들에 대한 값을 전부 입력해야 합니다. 특정 속성들만 삽입하고 싶은 경우 예를 들어
Insert Into 주문(주문번호, 주문고객)
Values ('o011', 'kiwi');
이렇게 입력할 수 있습니다.
3-3. Update ... Set ...
특정 튜플의 속성값들 또는 속성값을 업데이트해야 할 경우
Update table_name
Set 속성이름1=값1, 속성이름2=값2...
[Where 조건];
이 명령어를 사용하면 됩니다. 테이블명의 속성 값 변경할 때 Where키워드 지정하지 않으면 특정 column에 해당하는 데이터 전부 변경됩니다. 따라서 요구사항에 맞는 적절한 조건을 부여해야 합니다.
3-4. Delete From ...
DML의 마지막.. Delete입니다. 이 경우 특정 튜플을 삭제할 때 사용됩니다. Update 명령어와 비슷하지만 이번엔 행 기준.. Where 키워드를 부여하지 않을 경우 테이블의 모든 튜플이 삭제됩니다. 그러기에 어느 행을 삭제할 것인지 Where 키워드를 통해 특정 조건을 부여할 수 있습니다. 당연히 Where키워드를 사용하기 때문에 Subquery, Inner Join, Outer Left| Right| Full Join 등이 가능합니다.
틀린 부분 발견 시 댓글로 남겨주시면 정말 감사합니다.
'ComputerScience > Database 이론' 카테고리의 다른 글
| [Database] 8. 데이터베이스 설계 | 요구사항분석, 개념적, 논리적, 물리적 설계 개념 정리 (0) | 2023.06.22 |
|---|---|
| [Database] 7-2. View 개념 정리 (0) | 2023.06.22 |
| [Database] 6. 관계 데이터 연산 개념 정리 (0) | 2023.06.21 |
| [Database] 5. 관계형 데이터 모델(Relational data model) 개념 정리 (0) | 2023.06.21 |
