이제 상황에 따라 JOIN을 적용해보자.

사용될 테이블은 총 4가지로, employees, departments, locations, jobs이다.

 

1. employees

2. departments

3. locations

4. jobs

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
use mydb;
 
select * from employees;
select * from departments;
select * from locations;
 
-- 문제1) 사원들의 이름, 부서번호, 부서명을 출력하라
-- employees 테이블의 department_id는 Null값도 있고, 10부터 110까지만 존재
-- but, departments 테이블의 department_id는 10부터 270까지 존재.
-- inner join을 하게되면 department_id는 10부터 110까지 출력.
select first_name, e.department_id, department_name
from employees e inner join departments d on e.department_id = d.department_id;
 
-- left join을 하게되면 employees의 테이블의 department_id NULL 행 하나와 department_id는 10부터 110까지 출력.
select first_name, e.department_id, department_name
from employees e left join departments d on e.department_id = d.department_id;
 
-- right join을 하게되면 departments의 테이블의 department_name 행들과 department_id는 10부터 110까지 출력
select first_name, e.department_id, department_name
from employees e right join departments d on e.department_id = d.department_id;
 
-- left join, right join 모두 department_id 컬럼이 두 테이블이 공통으로 가지고 있는 값에 해당하므로 공통 부분만큼만 출력(10 ~ 110) 
 
select e.first_name, e.department_id, d.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;
 
-- 문제2) 30번 부서의 사원들의 이름,직업(id),부서명을 출력하라
select first_name, job_id, department_name, e.department_id
from employees e inner join departments d on e.department_id = d.department_id
where e.department_id = 30;
 
-- 문제2-1) 30번 부서의 사원들의 이름,직업명,부서명을 출력하라
select first_name, job_title, department_name
from employees e inner join departments d on e.department_id = d.department_id
    inner join jobs j on e.job_id = j.job_id
where e.department_id = 30;
 
select e.department_id, first_name, e.job_id, j.job_title, department_name
from employees e, departments d, jobs j
where e.department_id = d.department_id
    and e.department_id = 30
        and e.job_id = j.job_id;
 
 
-- 문제3) 커미션을 받는 사원의 이름, 직업, 부서번호, 부서명을 출력하라
select first_name, job_id, e.department_id, department_name, commission_pct
from employees e inner join departments d on e.department_id = d.department_id
where commission_pct is not null;
 
select first_name, job_id, e.department_id, department_name, commission_pct
from employees e, departments d
where e.department_id = d.department_id
    and commission_pct is not null;
 
-- 문제4) 지역번호 2500 에서 근무하는 사원의 이름, 직업, 부서번호, 부서명을 출력하라
select first_name, job_id, d.department_id, department_name, d.location_id
from employees e join departments d on e.department_id = d.department_id
    join locations l on d.location_id = l.location_id
where d.location_id = 2500;
 
select first_name, job_id, e.department_id, department_name, d.location_id, l.city
from employees e, departments d, locations l
where e.department_id = d.department_id
    and l.location_id = 2500
        and d.location_id = l.location_id;
 
-- 문제5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라
select first_name, department_name
from employees e join departments d on e.department_id = d.department_id
where first_name like "%A%";
 
select first_name, department_name
from employees e, departments d
where e.department_id = d.department_id
    and first_name like '%A%';
 
-- 문제6) 사원이름과 그 사원의 관리자 이름을 출력하라
select emp.first_name as 사원, mgr.first_name as 상사
from employees emp, employees mgr
where emp.manager_id = mgr.employee_id;
 
-- 문제7) 사원이름과 부서명과 월급을 출력하는데 월급이 6000 이상인 사원을 출력하라
select first_name, department_name, salary
from employees e join departments d on e.department_id = d.department_id
where salary >= 6000;
 
select first_name, department_name, salary
from employees e, departments d
where e.department_id = d.department_id
    and salary >= 6000;
 
-- 문제8) first_name 이 TJ 이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라
select tj.first_name, tj.hire_date, e.first_name, e.hire_date
from employees e, employees tj
where tj.first_name = "TJ" and tj.hire_date < e.hire_date;
 
-- 문제9) 급여가 3000에서 5000사이인 사원의 이름과 소속부서명 출력하라
select first_name, department_name, salary
from employees e join departments d on e.department_id = d.department_id
where salary between 3000 and 5000;
 
select e.first_name, e.salary, d.department_name
from employees e, departments d
where e.department_id = d.department_id
-- and e.salary >= 3000 and e.salary <= 5000
    and e.salary between 3000 and 5000;
 
-- 문제10) ACCOUNTING 부서 소속 사원의 이름과 입사일 출력하라
select first_name, hire_date, department_name
from employees e join departments d on e.department_id = d.department_id
where department_name = "ACCOUNTING";
 
select first_name, department_name, hire_date
from employees e, departments d
where e.department_id = d.department_id
    and d.department_name = 'Accounting';
 
-- 문제11) 급여가 3000이하인 사원의 이름과 급여, 근무지를 출력하라
select first_name, salary, city
from employees e join departments d on e.department_id = d.department_id
    join locations l on d.location_id = l.location_id
where salary <= 3000;
 
select first_name, salary, department_name, city
from employees e, departments d, locations l
where e.department_id = d.department_id
    and d.location_id = l.location_id
        and salary <= 3000;
 
-- 문제12) 지역별로 근무하는 사원의 수가 5명 이하인 경우, 사원이 적은 도시순으로 정렬하고 
-- 사원수와 city를 출력하시오.
select d.location_id, count(employee_id), city
from employees e join departments d on e.department_id = d.department_id
    join locations l on d.location_id = l.location_id
group by l.location_id
having count(employee_id) <= 5
order by count(employee_id) asc;
 
select d.location_id, count(e.employee_id) as emp_count, l.city
from employees e left outer join departments d 
    on e.department_id = d.department_id
        inner join locations l 
            on d.location_id = l.location_id
group by d.location_id
having count(e.employee_id) <= 5
order by emp_count asc;
 
 
-- 문제13) 지정한 부서번호, 사원이름 및 지정한 사원과 동일한 부서에서 근무하는 모든 사원을 표시하도록 하고 
-- 부서번호는 deptno, 사원이름은 사원, 동일한 부서에서 근무하는 사원은 동료로 표시하시오.
-- (부서번호, 사원이름, 동료 순으로 오름차순 정렬)
select e.department_id as deptno, e.first_name as 사원, c.first_name as 동료
from employees e, employees c
where e.department_id = c.department_id and e.first_name != c.first_name
order by deptno asc, 사원 asc, 동료 asc;
 
select  e.first_name as "사원", 
    e.department_id as deptno, c.department_id as deptno, 
    c.first_name as "동료" 
from employees e join employees c
    on e.department_id = c.department_id
where e.employee_id != c.employee_id
-- and e.first_name = 'TJ'
order by e.department_id, e.first_name, c.first_name;
 cs

'DB > MySQL' 카테고리의 다른 글

MySQL - JOIN(1)  (0) 2023.01.05
MySQL - 문법정리(2) - where, order, group, having...  (0) 2023.01.03
MySQL 기초 정리  (0) 2023.01.03
MySQL 문법(1) - select * from, insert into values 등등  (0) 2023.01.02

여러 개의 표(테이블)로 분산된 정보를 결합해서 하나의 단일한 표로 만드는 기술이 바로 JOIN

관계형 데이터베이스에서 가장 중요한 기능은 JOIN이다.

(학습에 도움이 되는 사이트: https://sql-joins.leopard.in.ua/)

출처: 생활코딩

먼저, 아래의 두 가지 경우를 살펴보자.

1. 테이블을 나누기 전(중복된 행을 노란색으로 표시)

 

2. 테이블을 나눈 후(중복된 행 제거 목적)

1번의 표들을 2번의 표로 나눈 것이다. 중복을 제거하여 과도하게 데이터 용량을 사용하던 것을 막고, 데이터의 수정이 용이해졌지만, 한편으로는 표를 읽는 것이 불편해졌다. 

우리는 데이터를 읽는 목적에 따라 JOIN을 사용할 필요성이 생긴 것이다.

 

이제, MySQL Workbench에서 2번의 표를 만들고 JOIN에 대해 알아보도록 하자.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
use test;
 
DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
  `aid` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  `profile_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `author` VALUES (1,'egoing','seoul',1),(2,'leezche','jeju',2),(3,'blackdew','namhae',3);
 
DROP TABLE IF EXISTS `profile`;
CREATE TABLE `profile` (
  `pid` int(11) NOT NULL,
  `title` varchar(10) DEFAULT NULL,
  `description` tinytext,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `profile` VALUES (1,'developer','developer is ...'),(2,'designer','designer is ..'),(3,'DBA','DBA is ...');
 
DROP TABLE IF EXISTS `topic`;
CREATE TABLE `topic` (
  `tid` int(11) NOT NULL,
  `title` varchar(45) DEFAULT NULL,
  `description` tinytext,
  `author_id` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `topic` VALUES (1,'HTML','HTML is ...','1'),(2,'CSS','CSS is ...','2'),(3,'JavaScript','JavaScript is ..','1'),(4,'Database','Database is ...',NULL);
 cs

Theme. LEFT JOIN

위 그림처럼 LEFT JOIN은 테이블A만 가지고 있는 값(행이라고 생각하는 것이 좋다) + 테이블 A와 B 모두 가지고 있는 값을 나타내게 된다. 이게 도대체 무슨 말인가?

직접 표를 LEFT JOIN해보면서 이해해보자. MySQL에서 미리 준비한 테이블들을 이용하자.

앞으로의 모든 과정에서 topic table을 중심으로 JOIN을 하는 것에 초점을 두도록 하겠다.

select * from topic; 을 해보면,

select * from topic;

총 4개의 행이 있는데, tid = 4일 때의 author_id 값이 NULL임을 알 수 있다.

select * from author;을 해보면,

select * from author;

총 3개의 행이 있고, NULL값이 없다.

 

이제, topic 테이블과 author테이블을 LEFT JOIN해보려 하는데, JOIN을 할 때 topic 테이블의 author_id와 author 테이블의 aid가 같은 값을 가지는 것을 참고한다.

select * from topic left join author on topic.author_id = author_aid;

(위에 코드는 select * from topic t left join author a on t.author_id = a.aid; 라고 작성할 수 있다.)

테이블의 왼쪽에는 topic 테이블이, 오른쪽에는 author 테이블이 JOIN되었고,

topic 테이블에는 존재하는 행이 author 테이블에는 존재하지 않지만, JOIN 결과 author 테이블 영역 밑에 NULL 값으로 채워진 행이 생성된 것을 확인할 수 있다.

즉, 왼쪽에 있는 테이블에는 값이 있는데, 그 값에 해당하는 오른쪽 테이블에는 행이 없다면, 왼쪽의 테이블의 행을 기준으로 출력되기 전에 NULL이 생긴다.

 

이 결과에 profile 테이블을 추가적으로 LEFT JOIN 해보자.

select * from profile;

select * from profile;

profile 테이블은 3개의 행이 존재한다.

 

author 테이블의 profile_id 값과 profile 테이블의 pid 값을 참고하여 JOIN해보면, 

select * from topic left join author on topic.author_id = author.aid left join profile on author.profile_id = profile.pid;

( select * from topic t left join author a on t.author_id = a.aid left join profile p on a.profile_id = p.pid;)

profile 테이블 또한 author 테이블과 같이 행이 3개이기 때문에 JOIN 결과 NULL 값으로 구성된 행이 추가됨을 알 수 있다.

 

이제 필요한 컬럼명들만 선택하여 출력해보자.

1
2
3
4
5
6
7
8
SELECT tid, t.title, author_id, name, p.title AS job_title 
FROM topic t LEFT JOIN author a ON t.author_id = a.aid 
    LEFT JOIN profile p ON a.profile_id = p.pid;
/* 
    t.title, p.title로 구분해주는 이유는 title이라는 컬럼명이 topic 테이블에도
    profile 테이블에도 존재하기 때문에 어떤 테이블의 컬럼명인지를 구분하기 위함이다.
*/
 
 cs

결과는,

위의 코드에 WHERE을 통해 조건(aid = 1이도록)도 추가해보면,

1
2
3
4
5
6
SELECT tid, t.title, author_id, name, p.title AS job_title 
FROM topic t LEFT JOIN author a ON t.author_id = a.aid 
    LEFT JOIN profile p ON a.profile_id = p.pid
where aid = 1;
 
 
 cs

결과는,

 

Theme. INNER JOIN

위 그림을 보면, 교집합이 떠오를 것이다. 

즉, 테이블 A와 B가 모두 가지고 있는 값(행)만 가지고 새로운 표를 만드는 것이다. 따라서, NULL행은 존재할 수 없다.

INNER를 생략하고 JOIN이라고만 코드를 작성해도 INNER JOIN으로 인식한다.

위의 테이블 결과들을 이용해서 topic 테이블과 author 테이블을 INNER JOIN 해보자.

topic 테이블은 4행, author 테이블은 3행을 가지고 있으므로 JOIN 결과 3행의 값들만 나타날 것이다.

select * from topic t inner join author a on t.author_id = a.aid;

여기에 profile 테이블도 INNER JOIN해보면,

select * from topic t inner join author a on t.author_id = a.aid inner join profile p on a.profile_id = p.pid;

마찬가지로 3행의 결과만 출력되는 것을 확인할 수 있다. (profile 테이블도 3행이다)

 

Theme. FULL OUTER JOIN

위 그림을 보면, 합집합이 떠오를 것이다.

즉, 테이블 A와 B가 가지고 있는 값(행) 모두를 가지고 새로운 표를 만드는 것이다. 

그런데, MySQL에서는 FULL OUTER JOIN을 통해 JOIN할 수 없다. 

이때 사용하는 것이 "union"이다. 앞서 설명은 하지 않았지만, RIGHT JOIN은 LEFT JOIN의 반대로 생각해주면 된다.

 

어떻게 union을 사용하는지 살펴보자.

topic 테이블과 author 테이블을 FULL OUTER JOIN한다면,

1
2
3
4
5
select *
from topic t left join author a on t.author_id = a.aid 
union
select *
from topic t right join author a on t.author_id = a.aid;
 cs

결과를 해석해보자.

먼저, topic 테이블과 author 테이블은 3행에 대한 값을 모두 가진다. 그래서 위 3개의 행이 나타났고,

4번째 행은 topci 테이블에만 존재하는 행이므로 author 테이블의 영역에서는 NULL값이 나타났다.

마지막으로, author 테이블에는 존재하지만, topic 테이블에 존재하지 않는 행이 맨 마지막에 나타났다.

 

Theme. SELF JOIN

 

한 마디로 테이블 자기 자신을 자신에 조인하는 것(?)이라고 말할 수 있을 것이다.

이는 예제로 이해하는 것이 가장 이해하기 좋다.

위와는 다른 테이블을 이용해보도록 한다.

employees 라는 테이블이 있다고 가정해보자. 그 테이블의 데이터의 일부는 아래와 같다.

컬럼명 중에서 employee_id와 manager_id를 찾아보자.

간단하게 설명하면, employee_id는 직원, manager_id는 각 직원에 대한 사수이다.

그런데, manager_id는 결국 직원 중 한명이므로 employee_id를 가진다.

이때, 각 직원에 대해 사수는 누구인지를 알고 싶다고 가정해보자. 이를 알아보기 위해서는

직원의 manager_id가 결국 사수의 employee_id와 같다는 것을 이용해야 한다.

즉,

1
2
3
select emp.first_name, emp.manager_id, mgr.employee_id, mgr.first_name
from employees emp join employees mgr    -- emp:직원  mgr:사수
    on emp.manager_id = mgr.employee_id;
 cs

첫 번째 column은 직원을, 4번째 column은 직원에 대한 사수를 의미한다.

 

 

이외에도 다양한 종류의 JOIN이 있으나, 정리할 필요가 있을 시 내용을 추가하도록 한다. 끝.

 

 

 

 

 

 

 

'DB > MySQL' 카테고리의 다른 글

MySQL - JOIN(2)  (0) 2023.01.05
MySQL - 문법정리(2) - where, order, group, having...  (0) 2023.01.03
MySQL 기초 정리  (0) 2023.01.03
MySQL 문법(1) - select * from, insert into values 등등  (0) 2023.01.02

전체적인 형식만 간단하게 정리해보자. 아래 코드들의 내용을 종합했을 때, 

 

SELECT 컬럼명1, 컬럼명2...

FROM 컬럼명에 속해 있는 테이블명

WHERE 그룹으로 묶기 전, 출력 전에 다는 조건

GROUP BY 그룹으로 묶기

HAVING 그룹으로 묶은 후, 출력 전에 다는 조건

ORDER BY 정렬

정도의 틀이 형성된다. 

 

이후 문제를 포스팅하겠지만,  형식은 직관적이므로 순서를 크게 외울 것은 없으나

WHERE과 HAVING에서 GROUP 전/후의 관계에서 조건을 부여하는 것이기 때문에

내가 부여하는 조건이 그룹을 묶는데 영향을 미치는지 아닌지를 잘 구분하는 것이 중요할 것이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
use mydb;
 
/*
    where: 조건절
    
    대소비교, 판정
    > < >= <= 
    =(같다) 
    != <>(같지 않다)
 
    is null( == null)
    is not null( != null)
    
    &&(AND) -> and
    ||(OR) -> or
*/
 
-- first_name 컬럼의 값이 John인 다른 컬럼명에 따른 데이터를 출력
select employee_id, first_name, salary
from employees
where first_name = 'John'; -- Java에서는 == "John"
 
-- first_name 컬럼의 값이 John이 아닌 다른 컬럼명에 따른 데이터를 출력
select employee_id, first_name, salary
from employees
where first_name != 'John';
-- where first_name <> 'John'; 와 같다.
 
-- salary 컬럼의 값이 10000이상인 다른 컬럼명에 따른 데이터를 출력 
select first_name, salary
from employees
where salary >= 10000;
 
-- ASCII 코드를 통해 문자도 대소비교가 가능하다 -> 정렬도 같은 논의
select first_name
from employees
where first_name >= 'John';
 
-- hire_date가 1990년 1월 1일보다 이전의 값인 다른 컬럼명에 따른 데이터를 출력
-- 날짜 및 시간 컬럼명 < 어떤 날짜 혹은 시간 값 이면, 정해진 날짜 혹은 시간보다 이전을, 반대로 > 이면 이후를 의미
select employee_id, first_name, hire_date
from employees
-- where hire_date < '1990-01-01'; 
where hire_date < date('1990-01-01'); -- date != String 완전히 다른 데이터 타입이다.
 
-- is null (== null)
-- manager_id의 값이 null인 다른 컬럼명들의 데이터를 출력
select first_name, last_name, manager_id
from employees
-- where manager_id = null;  -- 안된다
where manager_id is null;
 
-- manager_id의 값이 null이 아닌 다른 컬럼명들의 데이터를 출력
select first_name, last_name, manager_id
from employees
where manager_id is not null;
 
select first_name, commission_pct
from employees
where commission_pct is not null;
 
-- 조건1 그리고 조건2
select first_name, commission_pct, salary
from employees
where commission_pct is null
    and salary >= 10000;
 
-- 조건1 또는 조건2
select employee_id, first_name
from employees
where first_name = 'John'
    or first_name = 'Den';
 
-- 전체 문장 중에서 부분만 실행시키고 싶으면 그 부분을 범위 지정하고
-- ctrl + shift + enter
select employee_id, first_name, salary
from employees
where job_id = 'IT_PROG'
    and salary > 8000;
    
-- 조건 사이에 우선순위를 부여하려면 괄호()로 묶는다.
select first_name, salary
from employees
where (first_name = 'John'
    or first_name = 'Den')
    and salary < 6000;
 
/*
    all, any, in, exists, between
    and     or   or          
*/
 
select first_name, salary
from employees
where salary = all(select salary from employees where first_name = 'John');
-- first_name이 John인 사람들의 salary값들은 2700, 8200, 14000인데, 
-- 위와 같이 쓰면 where salary = 8200 and salary 2700 and salary = 14000가 되므로 불가능한 조건이 된다.
 
select first_name, salary
from employees
where salary = any(select salary from employees where first_name = 'John');
-- 위와 같이 쓰면 where salary = 8200 or salary 2700 or salary = 14000의 의미가 되어 성립할 수 있다.
 
select first_name, salary
from employees
where salary in(8200, 2700, 14000);
-- where salary = 8200 or salary 2700 or salary = 14000의 의미
 
-- job_id가 IT_PROG인 다른 컬럼명들에 대한 데이터 출력
select first_name, salary, job_id
from employees a
where exists(    select 1 from dual
                where a.job_id = 'IT_PROG'  );
 
-- 아래 두 코드는 동일한 결과를 나타낸다.////////
select first_name, salary
from employees
where salary >= 6000 
    and salary <= 10000;
    
select first_name, salary
from employees
where salary between 6000 and 10000;
-- /////////////////////////////////////
 
-- 아래 두 코드는 동일한 결과를 나타낸다.////////
select first_name, salary
from employees
where salary > 6000 
    or salary < 10000;
    
select first_name, salary
from employees
where salary not between 6000 and 10000;
-- /////////////////////////////////////
 
/*
    like: 포함하는 문자열 
    
*/
-- _는 한글자이고 _ 부분에 어떤 글자가 와도 상관없다.
select first_name
from employees
where first_name like 'G_ra_d'; 
 
-- %는 글자의 개수와 상관없이, 어떤 글자가 와도 상관 없다.
-- K로 시작하고 y로 끝나기만 하면 된다.
select first_name
from employees
where first_name like 'K%y'; 
 
select first_name
from employees
where first_name like 'M%';
 
select first_name
from employees
where first_name like '%y';
 
-- 중간에 b가 존재하기만 하면 된다.
select first_name
from employees
where first_name like '%b%';
 
-- 날짜에서도 활용가능
-- 2000년대를 나타낼 때, 
select first_name, hire_date
from employees
where hire_date like '2000%';
 
select first_name, hire_date
from employees
where hire_date like '2000-04%';
 
 
 cs

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
use mydb;
 
/*
    order by == sorting(정렬) 오름차순/내림차순
 
*/
 
select first_name, salary
from employees
order by salary asc; -- 오름차순
-- order by salary; (생략해도 오름차순이 기본값)
 
select first_name, salary
from employees
order by salary desc; -- 내림차순
 
select job_id, first_name, salary
from employees
where job_id = 'IT_PROG'
order by salary desc;
 
select first_name, manager_id
from employees
order by manager_id asc; -- null값이 오름차순일 때는 제일 먼저 나옴(즉, 제일 작은 값으로 취급)
-- order by manager_id desc; null값이 제일 마지막에 나옴
 
select first_name, commission_pct
from employees
order by commission_pct asc;
 
select first_name, job_id, salary
from employees
order by job_id asc, salary desc; -- 일단 job을 기준으로 오름차순 정렬하고 job의 동일한 값들 내에서 salary기준으로 내림차순
 
select first_name, salary * 12 as "연봉"
from employees
order by "연봉" desc;
 
-- 그룹으로 묶는 기능
select distinct department_id -- distinct를 통해 중복된 값을 안보여줌
from employees
order by department_id asc;
 
-- group by절
select department_id
from employees
group by department_id
order by department_id asc;
 
-- 통계
/*
    그룹함수
    count, sum, avg, max, min
*/
 
select count(employee_id), count(*), sum(salary), avg(salary), max(salary), min(salary),
    sum(salary) / count(*)
from employees
where job_id = 'IT_PROG';
 
-- truncate: 버림 -> truncate(column명, 2): 컬럼명에 해당되는 값들은 소수 둘째자리까지 나타내고 아래는 버림 
select department_id, sum(salary), max(salary), truncate(avg(salary), 0) 
from employees
where department_id is not null
group by department_id
order by department_id asc;
 
select department_id, job_id
from employees
group by department_id, job_id
order by department_id asc, job_id desc; -- 부서별 오름차순 정렬먼저, 그다음에 직업별로 내림차순
 
-- having: group 묶은 다음의 조건. group by와 함께 써야 한다.
select job_id, sum(salary)
from employees
group by job_id
having sum(salary) >= 15000
order by sum(salary) desc;
 
-- 급여가 5000이상 받는 사원으로 합계를 내서 업무로 그룹화하여 급여의 합계가 20000을 초과하는 업무명과 사원수, 합계, 평균을 구하시오
select job_id, count(employee_id), sum(salary), round(avg(salary), 0) -- round는 반올림
from employees
where salary >= 5000
group by job_id
having sum(salary) > 20000;
 
 
 cs

 

 

 

 

 

 

 

 

 

 

'DB > MySQL' 카테고리의 다른 글

MySQL - JOIN(2)  (0) 2023.01.05
MySQL - JOIN(1)  (0) 2023.01.05
MySQL 기초 정리  (0) 2023.01.03
MySQL 문법(1) - select * from, insert into values 등등  (0) 2023.01.02

연관된 표들을 그룹화 한 것을 데이터베이스라고 표현할 수도 있지만, 이를 Schema라고 표현할 수 있다.

MySQL을 설치한 것은 곧 데이터베이스 서버를 설치한 것이다.

간단한 용어들

Theme. 테이블 만들기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
show databases;
drop database opentutorials;
create database opentutorials;
use opentutorials;
 
CREATE TABLE topic (
id INT(11) NOT NULL AUTO_INCREMENT,  
title VARCHAR(100) NOT NULL, -- 
description TEXT NULL,
created DATETIME NOT NULL,
author VARCHAR(30) NULL,
profile VARCHAR(100) NULL,
PRIMARY KEY(id)
);
/*
"topic"이라는 이름의 table을 만들었다. 위 코드에 대한 설명을 하자면, 
Not Null: 값이 없는 것을 허용하지 않겠다.
auto_increment: 기본적으로 시작값은 1이며, 각 새 레코드가 추가 될때마다 1씩 자동증가 한다.
int(11)에서, int는 Integer이고 11은 table에서 몇 개까지 노출시킬 지를 정한 것(11개만 저장할 수 있다는 것이 아니다)
varchar(100)에서 varchar는 문자열의 데이터 타입을 의미하고, 100은 100"글자"를 말한다.
text: varchar보다 긴 문자열의 데이터 타입
datetime: 날짜와 시간을 모두 표현할 수 있는 데이터 타입
primary key(column명): 괄호 안에 있는 column명이 가장 중요한 컬럼. 예컨대 이를 기준으로 다른 값들과 중복되지 않게 구분할 수 있다.
*/
 
 cs

DESC topic;

위 코드에서 나온 용어들에 대해 정리해보면,

NULL, NOT NULL

NULL: 값이 없다

NOT NULL: NULL 값을 가지지 못하도록 지정한다.

 

cf) SQL의 주요 datatypes

1) 정수

 INT

2) 수치

 DECIMAL: 전체 자릿수, 소수 자릿수가 고정된 숫자 데이터 형식. 예를들어, DECIMAL(5,2)의 형식인 경우 234.5678은 234.58이 된다. (전체 자릿수 5개, 소수 둘째짜리까지 반올림)

3) 실수

 FLOAT

4) 날짜 및 시간

 DATE: 날짜

 TIME: 시간

 DATETIME: 날짜와 시간

5) 문자열

 VARCHAR, TEXT(이때, TEXT가 VARCHAR보다 최대 문자열 길이가 더 길다)

 

Theme. CRUD

Create: 생성한다

Read: 읽는다

Update: 수정한다.

Delete: 삭제한다

어떤 데이터베이스든 반드시 가지고 있는 4가지의 작업.

각각의 작업이 SQL에서 어떻게 이루어지고 있는지 하나씩 살펴보도록 하자.

 

1. Create

INSERT문을 이용한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
desc topic; -- desc: 테이블의 어떤 column이 있는지와 구조는 어떤지 나타냄
 
-- Create
INSERT INTO    topic (title, description, created, author, profile)
VALUES('MySQL', 'MySQL is..', now(), 'egoing', 'developer');
-- INSERT문을 통해 데이터를 생성(Create)할 수 있다.
-- id는 자동으로 증가하니까 생성해줄 필요가 없다.
 
INSERT INTO    topic (title, description, created, author, profile)
VALUES('Oracle', 'Oracle is..', now(), 'egoing', 'developer');
INSERT INTO    topic (title, description, created, author, profile)
VALUES('SQL Server', 'SQL Server is..', now(), 'duru', 'data administrator');
INSERT INTO    topic (title, description, created, author, profile)
VALUES('PostgreSQL', 'PostgreSQL is..', now(), 'taeho', 'data scientist, developer');
INSERT INTO    topic (title, description, created, author, profile)
VALUES('MongoDB', 'MongoDB is..', now(), 'egoing', 'developer');
SELECT * FROM topic; -- 모든 행 조회
 cs

 

Create

2. Read

SELECT문을 이용한다.

1
2
3
-- 선택한 column에 대한 모든 행을 조회
SELECT id, title, created, author 
FROM topic;
 cs

1
2
3
-- 선택한 column에 대한 모든 행을 조회 + 일정한 값을 가진 행만을 조회
SELECT id, title, created, author 
FROM topic WHERE author = 'egoing';
 cs

1
2
3
-- 선택한 column에 대한 모든 행을 조회 + 일정한 값을 가진 행만을 조회 + 정렬(내림차순)
SELECT id, title, created, author 
FROM topic WHERE author = 'egoing' ORDER BY id DESC;
 cs

1
2
3
-- 선택한 column에 대한 모든 행을 조회 + 일정한 값을 가진 행만을 조회 + 정렬(내림차순) + 출력되는 행의 개수(2개) 설정
SELECT id, title, created, author 
FROM topic WHERE author = 'egoing' ORDER BY id DESC LIMIT 2;
 cs

3. Update

위 결과에서 id  = 2일 때의 title의 Oracle은 'ORACLE'로, description의 Oracle is는 'ORACLE is'로 수정해보자.

이때, WHERE와 같은 제어문을 반드시 주의해서 써줘야한다. 자칫 원하는 행뿐만 아니라 앞의 조건들을 만족한 행들의 값들이 한번에 수정되어 버리는 참사가 벌어질 수 있기 때문이다.

1
2
-- id가 2인 행의 title, description 값을 수정해주는 방법
UPDATE topic SET title = 'ORACLE', description = 'ORACLE is..' WHERE id = 2;
 cs

그 결과 다시 SELECT * FROM topic;을 하면,

id = 2인 행의 값들이 수정된 것을 확인할 수 있다.

 

4. DELETE

위의 전체 행에 대한 결과에서 MongoDB에 대한 행을 지워보자.

1
2
3
-- MongoDB를 삭제해보자.
DELETE FROM topic WHERE id = 5;
SELECT * FROM topic;
 cs

 

Theme. 관계형 데이터베이스의 이해(Relational Database)와 JOIN을 위한 테이블 나누기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- Relational Database(관계형 데이터베이스)의 이해
-- 먼저, topic 테이블을 쪼개보자.
RENAME TABLE topic TO topic_backup;
SELECT * FROM topic_backup;
DESC topic_backup;
--
-- Table structure for table `author`
--
 
CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `profile` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 
--
-- Dumping data for table `author`
--
 
INSERT INTO `author` VALUES (1,'egoing','developer');
INSERT INTO `author` VALUES (2,'duru','database administrator');
INSERT INTO `author` VALUES (3,'taeho','data scientist, developer');
SELECT * FROM author;
DESC author;
--
-- Table structure for table `topic`
--
 
CREATE TABLE `topic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `description` text,
  `created` datetime NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 
--
-- Dumping data for table `topic`
--
 
INSERT INTO `topic` VALUES (1,'MySQL','MySQL is...','2018-01-01 12:10:11',1);
INSERT INTO `topic` VALUES (2,'Oracle','Oracle is ...','2018-01-03 13:01:10',1);
INSERT INTO `topic` VALUES (3,'SQL Server','SQL Server is ...','2018-01-20 11:01:10',2);
INSERT INTO `topic` VALUES (4,'PostgreSQL','PostgreSQL is ...','2018-01-23 01:03:03',3);
INSERT INTO `topic` VALUES (5,'MongoDB','MongoDB is ...','2018-01-30 12:31:03',1);
SELECT * FROM topic;
DESC topic;
 cs

그 결과, 3가지 테이블에 대한 정보는 다음과 같다.

1. topic_backup(기존의 topic)

DESC topic_backup;

 

SELECT * FROM topic_backup;
 
2. author
 

DECS author;

 

SELECT * FROM author;

3. topic(새로운 topic)

DESC topic;

SELECT * FROM topic;
 

Theme. 관계형 데이터베이스의 이해(Relational Database)와 JOIN

JOIN을 통해 독립적인 테이블들을 읽을 때, 마치 하나인 것처럼 읽을 수 있게 된다.

위 topic과 author 테이블을 JOIN 해보자.

두 테이블이 결합될 수 있는 지점은 topic 테이블에서는 author_id, author 테이블에서는 id이다.

그래서 두 개의 테이블을 그 지점을 이용해서 합성해보면,

1
2
3
-- JOIN
SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;
-- topic 테이블과 author 테이블을 author_id, id의 지점에서 JOIN한다.
 cs

 

JOIN 결과

이를 중간의 author_id와 id column은 제외하고 보기 좋게 바꾸면,

(이때, id라는 column명이 겹치는데, 맨 앞의 id를 topic.id라고 명시해줘야 한다.)

즉, SELECT topic.id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;

라고 적는다. 그 결과,

이렇게 JOIN을 해놓으면 각각의 테이블을 독립적으로 관리하면서 수정하더라도, 다른 테이블을 다시 각각 고쳐줄 필요가 없는 장점이 있다. 

즉, 테이블 A,B,C가 있을 때, A와 B, A와 C를 각각 JOIN해줬다면,

A의 데이터를 수정하게 되었을 때, B와 C도 별도의 수정없이 자동으로 동시에 수정되게 된다.

 

 

'DB > MySQL' 카테고리의 다른 글

MySQL - JOIN(2)  (0) 2023.01.05
MySQL - JOIN(1)  (0) 2023.01.05
MySQL - 문법정리(2) - where, order, group, having...  (0) 2023.01.03
MySQL 문법(1) - select * from, insert into values 등등  (0) 2023.01.02
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
use mydb;
 
-- 한줄 주석문
/*
    범위 주석문
 
    SQL - Structured Query Language
          구조적           질의어
    
    Data를 다루는 방법으로써,
    CRUD
    insert, select, update, delete
    select가 전체 90%를 차지할 만큼 비중이 크다.
          
    employees: 사원 
    departments: 부서
    job: 업무
    
*/
 
select * from employees; -- employees 테이블의 모든 데이터 검색
 
select employee_id, first_name, salary -- 원하는 column만 가져옴
from employees;  -- employees 테이블에서        
 
desc employees; -- employees 테이블의 column에 대한 정보
 
/*
    자료형
    <java            MySQL>
    int                int, decimal 예를들면, decimal(5)
    double            double, decimal 예를들면, decimal(5,1) 소수 첫째자리까지 표현
    String            varchar
    Date            date
*/
 
-- table 생성
/*
create table 테이블명(
    컬럼명1 자료형,
    컬럼명2 자료형
);
 
*/
 
-- 테이블정보 조회
select * 
from information_schema.tables
where table_schema = 'mydb'; 
 
-- varchar (== String) 
create table tb_varchar(
    col1 varchar(10), -- 10byte라는 뜻 (영문자: 1byte, 한글은 한글자에 3byte)
    col2 varchar(20)
);
    
select * from tb_varchar;
 
insert into tb_varchar(col1, col2)
values('abc', 'ABC');    -- 문자열 집어넣을 때, 작은 따옴표! col1에 abc, col2에 ABC 들어감
 
-- int, double
create table tb_decimal(
 col1 decimal, -- 소수점 아래는 반올림해서 다 없앰
 col2 decimal(5), -- 총 다섯자리만 남고 소수점 아래 버림
 col3 decimal(5,2) -- 소수 둘째짜리까지 남기고, 그 아래는 버림. 총 다섯
);
 
insert into tb_decimal(col1, col2, col3)
values(1234.5678, 12345.12, 123.456);
 
select * from tb_decimal;
 
-- 날짜
create table tb_date(
    col1 date,
    col2 date
);
 
-- 현재 날짜
insert into tb_date(col1, col2)
values(now(), '2022-12-25');  -- now()는 현재날짜
 
select * from tb_date;
 
-- 현재 날짜, 시간
create table board(
    col1 timestamp,
    col2 timestamp default now()
);
 
insert into board(col1, col2)
values(now(), default);
 
insert into board(col1, col2)
values(now(), now());
 
select * from board;
 
drop table tb_varchar;
drop table tb_decimal;
drop table tb_date;
drop table board;
 
 cs

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
use mydb;
 
/*
 형식:     select절 -> 검색
        select (값, 컬럼명, 함수, sub query)
        from (테이블명, sub query)
        
*/
 
select 1 from dual; -- dual: 임시테이블
select '한글' from dual;
 
-- 특정 테이블에 대해서 모든 데이터를 취득
select * 
from employees;
 
select * 
from departments;
 
select employee_id, last_name, hire_date
from employees;
 
select '이름', employee_id, last_name, hire_date
from employees;
 
-- 컬럼의 별명(alias) 큰 따옴표 붙여야 함
select employee_id AS "사원번호", last_name as "성", salary "월급"  
from employees;
 
select employee_id AS 사원번호, last_name as 성, salary 월급  
from employees;
 
/* 띄어쓰기 할때는 큰 따옴표 반드시 붙여야 함 -> "사원 번호" 이렇게!
select employee_id AS "사원 번호", last_name as 성, salary 월급  
from employees;도 가능하다.
*/
 
-- 산술연산자( +, -,  *, /)
select first_name, last_name, salary * 12 as 연봉
from employees;
 
-- 문자열 합치기
select concat('이름: ', last_name, ' ', first_name) as 이름
from employees;
 
 
 cs

 

'DB > MySQL' 카테고리의 다른 글

MySQL - JOIN(2)  (0) 2023.01.05
MySQL - JOIN(1)  (0) 2023.01.05
MySQL - 문법정리(2) - where, order, group, having...  (0) 2023.01.03
MySQL 기초 정리  (0) 2023.01.03

+ Recent posts

티스토리툴바