DB_2 SQL로 데이터 처리하기

SQL의 기본 개념

SQL

• Structured Query Language
• 현업에서 쓰이는 relational DBMS의 표준 언어
• 종합적인 database 언어 : DDL + DML + VDL

SQL 주요 용어

relational data model	SQL
relation	table
attribute	column
tuple	row
domain	domain

SQL에서 relation : multiset(= bag) of tuples @ SQL, 중복된 tuple을 허용한다

SQL & RDBMS → SQL은 RDBMS의 표준 언어이지만 실제 구현에 강제가 없기 때문에 RDBMS마다 제공하는 SQL의 스펙이 조금씩 다름

• database와 schema
  • MySQL에서는 database와 schema가 같은 뜻을 의미
  • 다른 RDBMS에서는 의미가 다르게 쓰임

attribute data type

1. 숫자

• 정수 - 정수를 저장할 때 사용 (tinyint(1), smallint(2), mediumint(3), int(=integer)(4), bigint(8))
• 부동 소수점 방식 - 실수를 저장할 때 사용, 고정 소수점 방식에 비해 정확하지 않음 (float(4), double(=double precision)(8))
• 고정 소수점 방식 - 실수를 정확하게 저장할 때 사용 (decimal(=numeric)(variable))

2. 문자열

• 고정 크기 문자열 - 최대 몇 개의 문자를 가지는 문자열을 저장할지 지정. 저장될 문자열의 길이가 최대 길이보다 작으면 나머지를 space로 채워서 저장 (char(n), 0≤ n≤ 255)
• 가변 크기 문자열 - 최대 몇 개의 문자를 가지는 문자열을 저장할지 지정. 저장될 문자열의 길이 만큼만 저장 (varchar(n), 0≤ n≤ 65535)
• 사이즈가 큰 문자열 - 사이즈가 큰 문자열을 저장할 때 사용 (tinytext, text, mediumtext, longtext)

3. 날짜와 시간

• 날짜 - 년, 월, 일을 저장. YYYY-MM-DD (date)
• 시간 - 시, 분, 초를 저장. hh:mm:ss or hhh:mm:ss (time)
• 날짜와 시간 - 날짜와 시간을 같이 표현. YYYY-MM-DD hh:mm:ss (datetime, timestamp(time-zone이 반영됨))

4. 그 외

• byte-string - 문자열이 아니라 byte string을 저장 (binary, varbinary, blob type)
• boolean - true, false를 저장. MySQL에는 따로 없음 (tinyint로 대체하여 사용)
• 위치 관련 - 위치 관련 정보를 저장 (geometry, etc)
• JSON - json 형태의 데이터를 저장 (json)

예제 - IT 회사 관련 RDB 만들기

• 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들 예정
• 사용항 RDBMS는 MySQL (InnoDB)

1. database 정의

   show databases; → 어떤 데이터베이스가 있는지 확인

   create database [database_name]; → 새로운 데이터베이스 생성

   select database(); → 지금 선택된 데이터베이스가 뭔지 알려줌

   use [database_name]; → 해당 데이터베이스를 사용한다고 명시

   drop database [database_name]; → 해당 데이터베이스 삭제

2. table 정의

   department

   id	name	leader_id

   → create table department ( id int primary key, name varchar(20) not null unique, leader_in int ) ;

   • primary key 선언 방법
     • attribute 하나로 구성 → id int primary key
     • attribute 하나 이상으로 구성 → primary key(team_id, back_number)
   • unique key
     • unique로 지정된 attribute(s)는 중복된 값을 가질 수 없음
     • null은 중복으로 허용
     • unique 선언 방법 : primary key 선언 방법과 같음
   • not null constraint
     • attribute가 not null로 지정되면 해당 attribute는 null 값을 가질 수 없음

   employee

   id	name	birth_date	sex	position	salary	dept_id

   → 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)

   );

   • attribute default : attribute의 default 값을 정의할 때 사용. 새로운 tuple을 저장할 때 해당 attribute에 대한 값이 없다면 default 값으로 저장
   • check constraint : attribute의 값을 제한하고 싶을 때 사용
     • 선언 방법
       1. attribute 하나로 구성 → age int check (age ≥ 20)
       2. attribute 하나 이상으로 구성 → check(start_date ≤ end_date)
   • referential integrity constraint : foreign key - attributes가 다른 table의 primary key나 unique key를 참조할 때 사용
     • foreign key reference_option
       1. cascade : 참조값의 삭제/변경을 그대로 반영
       2. set null : 참조값이 삭제/변경 시 null로 변경
       3. restrict : 참조값이 삭제/변경되는 것을 금지
       4. no action : restict와 유사
       5. set default : 참조값이 삭제/변경 시 default 값으로 변경
   • constraint 이름 명시하기

     • 이름을 붙이면 어떤 constraint 위반했는지 쉽게 파악할 수 있음

     • constraint를 삭제하고 싶을 때 해당 이름으로 삭제 가능

       → age int constraint age_over_20 check (age > 20)

   project

   id	name	leader_id	start_date	end_date

   works_on

   empl_id	proj_id

3. table schema 변경

→ alter table department add foreign key (leader_id) references employee(id) on update cascade on delete set null;

• 이미 서비스 중인 table의 schema 변경하는 것이라면 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후 변경하는 것이 중요

4. table 삭제

→ drop table table_name;

database 구조를 정의할 때 중요한 점 : 만들려는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등을 종합적으로 고려하여 DB 스키마를 적절하게 정의하는 것이 중요

SQL로 DB에 데이터 추가, 수정, 삭제하기

1. 데이터 추가하기

→ insert into employee values ( 1, ‘Messi’, 1987-02-01’, ‘M’, ‘DEV_BACK’, 100000000, null );

• 데이터의 순서는 table을 만들 때 attribute 순서에 맞춰서 입력
• constraint 이름 확인하는 방법 → show create table table_name;

→ insert into employee (name, birth_date, sex, position, id) values (’Jenny’, ‘2000-10-12’, ‘F’, ‘DEV_BACK’, 3);

• 원하는 데이터만 넣을 수 있음
• attribute 순서를 지정하여 데이터를 입력

INSERT statement 정리

• INSERT INTO table_name VALUES (comma-separated all values);
• INSERT INTO table_name (attributes list) VALUES (attributes list 순서와 동일하게 comma-separated values);
• INSERT INTO table_name VALUES (…, …), (…, …), (…, …);

2. 데이터 수정하기

→ update employee set dept_id = 1003 where id = 1;

→ update employee, works_on set salary = salary*2 where id=empl_id and proj_id = 2003;

→ update employee, works_on set salary = salary*2 where employee.id=empl_id and works_on.proj_id = 2003;

3. 데이터 삭제하기

→ delete from employee where id=8;

→ delete from works_on where impl_id=2;

→ delete from works_on where impl_id=5 and proj_id=2002;

---

데이터 조회하기

1. select로 데이터 조회하기

→ select name, position from employee where id=9;

• name, position → projection attributes : 내가 알고 싶은 데이터
• id=9 → selection condition : 조건 → 조건에 해당하는 튜플에서 내가 알고 싶은 데이터에 대응하는 것만 가져옴
  

→select employee.id, employee.name, position from project, employee where project.id=2002 and project.leader_id=employee.id;

• join condition : 두 테이블을 연결

2. AS 사용하기

AS : 테이블이나 attribute에 별칭을 붙일 때 사용. 생략 가능

→select E.id, E.name, position from project AS P, employee AS E where P.id=2002 and P.leader_id=E.id;

• 테이블 별칭 붙이기

→select E.id AS leader_id, E.name AS leader_name, position from project AS P, employee AS E where P.id=2002 and P.leader_id=E.id;

• attribute 별칭 붙이기 → 실행 결과 테이블에서 attribute 이름이 별칭으로 나옴

3. DISTINCT 사용하기

• distinct : 중복된 튜플을 하나만 나타내는 키워드

→ select DISTINCT E.id AS leader_id, E.name AS leader_name, position from project AS P, employee AS E where P.id=2002 and P.leader_id=E.id;

4. LIKE 사용하기

→ SELECT name FROM employee WHERE name LIKE ‘N%’ or name LIKE ‘%N’;

• % : 0개 이상의 임의의 개수를 가지는 문자를 의미

→ SELECT name FROM employee WHERE name LIKE ‘J_ _ _’ ;

• J로 시작하면서 4글자의 이름을 가진 임직원을 의미
• %, *가 포함된 문자를 찾고 싶을 때 : \%, *
• LIKE : 문자열 패턴 매칭에 사용
• % : 0개 이상의 임의의 개수를 가지는 문자들
• _ : 하나의 문자를 의미
• (escape character) : 예약 문자를 escape 시켜서 문자 본연의 문자로 사용하고 싶을 때 사용

5. asterisk(*) 사용하기

→ select * from employee where id=9;

• 선택된 tuples의 모든 attributes를 보여주고 싶을 때 사용

6. where 절이 없는 select 문

→ select name, birth_date from employee;

• 테이블에 있는 모든 tuples 반환

---

쿼리 안의 쿼리

subquery

• select, insert, update, delete에 포함된 query
  • outer query : subquery를 포함하는 query
  • subquery는 () 안에 기술

1. select 안에 subquery

→ select id, name, birth_date from employee where birth_date < ( select birth_date from employee where id=14);

• where 절에서 or 조건을 하나로 쓰는 방법 : proj_id=2001 or proj_id=2002 → proj_id IN (2001, 2002)
• IN
  • IN(v1, v2, v3, …) → v가 v1, v2, v3,… 중에 하나와 값이 같으면 TRUE를 반환
  • v1, v2, v3, … → 명시적인 값들의 집합 혹은 subquery의 결과(set, multiset)
  • v NOT IN (v1, v2, v3, …) → v가 v1, v2, v3, … 의 모든 값과 다를 때 TURE 반환
• EXISTS

  • outer 쿼리 먼저 보는게 편함

  • correlated query : subquery가 바깥쪽 query의 attribute를 참조할 때

  • subquery의 결과가 최소 하나의 행이라도 있으면 TURE를 반환

  • NOT EXISTS : subquery의 결과가 단 하나의 행이라도 없을 때 TRUE를 반환

    → select p.id, p.name from project p where exists ( select * from works_on w where w.proj_id=p.id and w.empl_id in (7, 12) );

  • exists는 in으로 바꿀 수 있음

• ANY

  • v comparison_operator ANY (subquery) : subquery가 반환한 결과들 중에 단 하나라도 v와의 비교 연산이 TRUE라면 TRUE 반환

  • SOME과 같은 역할

    → select e.id, e.name, e.salary from department d, employee e where d.leader_id=e.id and e.salary < ANY ( select salary from employee where id <> d.leader_id and dept_id=e.dept_id );

• ALL

→ select distinct e.id, e.name e.position from employee e, works_on w where e.id=w.empl_id and w.proj_id <> ALL (select proj_id from works_on where empl_id=13);

• v comparison_operator ALL (subquery) : subquery가 반환한 결과들과 v와의 비교 연산이 모두 TRUE일 경우 TRUE 반환

---

Three-Valued_Logic

1. NULL

• NULL의 의미
  1. unknown
  2. unavailable or withheld
  3. not applocable
• IS NULL → select id from employee where birth_date IS NULL;
  • null은 = 기호를 쓰지 않고 IS 키워드를 사용

2. NULL과 Three-Valued_Logic

• SQL에서 NULL과 비교 연산을 하게 되면 그 결과는 UNKNOWN
• UNKNOWN은 TRUE일수도 FALSE일 수도 있다는 의미
• Three-Valued_Logic : 비교, 논리 연산의 결과로 TRUE, FALSE, UNKNOWN을 가짐
• NULL과 비교 연산 → 항상 UNKNOWN

3. where 절의 condition(s)

• where절에 있는 condition(s)의 결과가 TRUE인 tuple(s)만 선택됨
• 결과가 FALSE거나 UNKNOWN이면 tuple은 선택되지 않음

→ 3 not in (1, 2, unknown) → unknown (false 아님)

• 해결 : IS NOT NULL 혹은 NOT EXISTS 혹은 table 생성 시 NOT NULL 조건 걸어두기

---

테이블 조인(Join)

join

• 두 개 이상의 table들에 있는 데이터를 한 번에 조회하는 것
• 종류가 다양

1. implicit join

   • implicit join : from 절에는 table만 나열하고 where 절에 join condition을 명시하는 방식
   • old-style join syntax
   • where 절에 selection condition과 join condition이 같이 있기 때문에 가독성이 떨어짐
   • 복잡한 join 쿼리를 작성하면 실수할 가능성이 커짐

   → select d.name from employee as e, department as d where e.id = 1 and e.dept_id=d.id;

2. explicit join

   • from 절에 JOIN 키워드와 함께 joined table을 명시하는 방식
   • from 절에서 ON 뒤에 join condition이 명시됨
   • 가독성 좋음
   • 복잡한 조인 쿼리 작성해도 실수할 가능성 적음

   → select d.name from employee as e join department as d on e.dept_id=d.id where e.id = 1 ;

3. inner join

   → select * from employee as e inner join department as d on e.dept_id=d.id ;

   • 두 table에서 join condition을 만족하는 tuple들로 result table을 만드는 join
   • from table1 [INNER] JOIN table2 ON join_condition
   • join condition : =, <, >, <> 등 비교연산자. NULL 값을 가지는 tuple은 result table에 포함 안 됨

4. outer join

   • 두 table에서 join condition을 만족하지 않는 tuple들도 result table에 포함하는 join
   • FROM table1 LEFT [OUTER] JOIN table2 ON join_condition
   • FROM table1 RIGHT [OUTER] JOIN table2 ON join_condition
   • FROM table1 FULL [OUTER] JOIN table2 ON join_condition → MySQL에서 지원 안 함
   • join condition : =, <, >, <> 등 비교연산자

5. equi join

   • join condition에서 = (equality comparator)를 사용하는 join
   • equi join에 대한 시각
     • inner join, outer join 상관없이 =을 사용한 join → equi join
     • inner join으로 한정해서 =을 사용한 join → equi join

USING

• equi join에서 중복된 attribute를 하나만 표시하기 위해 사용
• 두 테이블이 equi join을 할 때 join하는 attribute의 이름이 같다면 USING으로 간단하게 작성할 수 있음
• 이 때 같은 이름의 attribute는 result table에서 한 번만 표시됨
• FROM table1 [INNER] JOIN table2 USING (attribute(s))
• FROM table1 LEFT [OUTER] JOIN table2 USING (attribute(s))
• FROM table1 RIGHT [OUTER] JOIN table2 USING (attribute(s))
• FROM table1 FULL [OUTER] JOIN table2 USING (attribute(s))

→ select * from employee e inner join department d using (dept_id);

1. natural join

• 두 table에서 같은 이름을 가지는 모든 attribute pair에 대해서 equi join을 수행
• join condition을 따로 명시하지 않음
• FROM TABLE1 NATURAL [INNER] JOIN TABLE2
• FROM TABLE1 NATURAL LEFT [OUTER] JOIN TABLE2
• FROM TABLE1 NATURAL RIGHT [OUTER] JOIN TABLE2
• FROM TABLE1 NATURAL FULL [OUTER] JOIN TABLE2

2. cross join

• 두 table의 tuple pair로 만들 수 있는 모든 조합(=Cartesian product)를 result table로 반환
• join condition 없음
• implicit cross join : FROM table1, table2
• explicit cross join : FROM table2 CROSS JOIN table2
• MySQL에서 cross join = inner join = join
  • ON, USING 키워드 쓰면 inner join으로 동작
  • ON, USING 키워드 안 쓰면 cross join으로 동작

3. self join

• table이 자기 자신에게 join하는 경우

---

Grouping, Aggregate function Ordering

ORDER BY

• 조회 결과를 특정 attribute(s) 기준으로 정렬하여 가져오고 싶을 때 사용
• default 정렬 방식 → 오름차순
• 오름차순 : ASC
• 내림차순 : DESC

→ select * from employee ORDER BY salary DESC;

→ select * from employee ORDER BY dept_id ASC, salary DESC;

aggregate funtion

• 여러 tuple들의 정보를 요약해서 하나의 값으로 추출하는 함수
• 대표적으로 COUNT, SUM, MIN, AVG 함수가 있음
• 주로 관심있는 attribute에 사용
• NULL 값들은 제외하고 요약 값을 추출

→ select COUNT(*) FROM employee; (중복도 포함)

→ select count(*), max(salary), min(salary), avg(salary) from works_on w join employee e on empl_id=e.id where w.prof_id=2002;

GROUP BY

→ select count(*), max(salary), min(salary), avg(salary) from works_on w join employee e on empl_id=e.id GROUP BY w.prof_id=2002;

• 프로젝트 별로 통계를 알려줌
• 관심있는 attribute 기준으로 그룹을 나눠서 그룹별로 aggregate function을 적용하고 싶을 때 사용
• 그룹을 나누는 기준이 되는 attribute → grouping attribute
• grouping attribute에 NULL 값이 있을 때는 NULL 값을 가지는 tuple끼리 묶임

HAVING

→ select [count(*), max(salary), min(salary), avg(salary)] from works_on w join employee e on empl_id=e.id GROUP BY w.prof_id=2002 HAVING COUNT() ≥ 7 ;

• GROUP BY와 함께 사용
• aggregate function의 결과값을 바탕으로 그룹을 필터리하고 싶을 때 사용
• HAVING절에 명시된 조건을 만족하는 그룹만 결과에 포함됨

SELECT 요약

SELECT attribute(s) or aggregate function(s) → 6

FROM table(s) →1

[WHERE conditions] → 2

[GROUP BY group attributes] → 3

[HAVING group conditions] → 4

[ORDER BY attributes] → 5