[풀스택] Database : SQL

[DDL]

0. 데이터베이스 보기, 테이블 보기

show databases;
show tables;

1. 데이터 베이스 생성

create database multi;

2. 데이터 베이스 사용하기

use multi;

3. 테이블 생성

create table students(
 id int,
 name varchar(100),
 phone char(13),
 address varchar(1000)
);

4. 테이블 미리보기

desc students;

+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | int           | YES  |     | NULL    |       |
| name    | varchar(100)  | YES  |     | NULL    |       |
| phone   | char(13)      | YES  |     | NULL    |       |
| address | varchar(1000) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

5. 값 넣기

insert into students
 values(1, 'hong-gd', '010-1111-1111', 'seoul');

6. 테이블 보기

select *
from students;

7. 컬럼(속성) 추가

alter table students
add job varchar(100);

8. 원하는 열에만 데이터 추가

insert into students(id, name, address, job)
values (2, 'kim-sd', 'suwon', 'engineer');

9. 데이터의 속성 수정하기

alter table students
modify job varchar(1000);

10. 테이블 삭제 / 데이터베이스 삭제

-- 테이블 삭제하기
drop table students;

-- 데이터 베이스 삭제하기
drop databases multi;

 

---

 

[DML : sql 파일을 가져와서 데이터 베이스 저장 후 조회하는 법]

(데이터 파일 출처)

MySQL :: Employees Sample Database

0. 준비

source employees.sql;
use employees;

 

mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

1. 사원수 count

select count(*) as 사원수
from employees;

2. 원하는 칼럼에서 5명 보기

select emp_no, first_name, last_name
from employees
limit 5;

3. 조건 : where

-- 고용일이 2000년 1월 1일 이후 employees 전체 출력
select *
from employees
where hire_date >= "2000-01-01";

-- 급여가 100,000보다 크고 150,000보다 작거나 같은 salaries 전체 출력
select *
from salaries
where salary > 100000 and salary <= 150000;

-- 1960년대 태어난 employees
select *
from employees
where birth_date between('1960-01-01' and '1970-01-01');

4. 정렬 : order by

-- 늦게 취업한 사람 순서, 나이순으로
select hire_date, birth_date
from employees
order by hire_date desc, birth_date
limit 50;

-- 나이 순서로, 늦게 취업한 순서대로
select *
from employees
order by birth_date, hire_date desc
limit 50;

5. 그룹화 : group by

mysql> select title, count(*)
from titles
group by title;

+--------------------+----------+
| title              | count(*) |
+--------------------+----------+
| Senior Engineer    |    97750 |
| Staff              |   107391 |
| Engineer           |   115003 |
| Senior Staff       |    92853 |
| Assistant Engineer |    15128 |
| Technique Leader   |    15159 |
| Manager            |       24 |
+--------------------+----------+
7 rows in set (0.19 sec)

+ 그룹화에 조건 추가 : having

-- 부서 별 사원 수가 5만명 이상인 부서 출력
select dept_no, count(dept_no)
from dept_emp
group by dept_no
having count(dept_no) >= 50000;

 

---

[DCL 실습 : commit과 rollback] 

rollback 실습을 하기 전에 autocommit이 설정되어 있는지를 확인해야 함.

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)

0. 준비 

create database multi;
use multi;

create table students(
 id int,
 name varchar(100),
 phone char(13),
 address varchar(1000),
 job varchar(100)
);

mysql> desc students;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | int           | YES  |     | NULL    |       |
| name    | varchar(100)  | YES  |     | NULL    |       |
| phone   | char(13)      | YES  |     | NULL    |       |
| address | varchar(1000) | YES  |     | NULL    |       |
| job     | varchar(100)  | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

 

1. 데이터 추가 후 커밋

insert into students
values (1, 'hong-gd', '010-1111-1111', 'seoul', 'ai');

insert into students(id, name, phone)
values (2, 'kim-sd', '02-222-2222');

select *
from students;
+------+---------+---------------+---------+------+
| id   | name    | phone         | address | job  |
+------+---------+---------------+---------+------+
|    1 | hong-gd | 010-1111-1111 | seoul   | ai   |
|    2 | kim-sd  | 02-222-2222   | NULL    | NULL |
+------+---------+---------------+---------+------+

commit;

 

2. 내용 update

update students 
set phone = '010-2222-2222',
    address = 'suwon',
    job = 'engineer'
where id = 2;

+------+---------+---------------+---------+----------+
| id   | name    | phone         | address | job      |
+------+---------+---------------+---------+----------+
|    1 | hong-gd | 010-1111-1111 | seoul   | ai       |
|    2 | kim-sd  | 010-2222-2222 | suwon   | engineer |
+------+---------+---------------+---------+----------+

delete from students
where id = 1;

+------+--------+---------------+---------+----------+
| id   | name   | phone         | address | job      |
+------+--------+---------------+---------+----------+
|    2 | kim-sd | 010-2222-2222 | suwon   | engineer |
+------+--------+---------------+---------+----------+

 

3. 되돌리고 싶다면 rollback 

rollback;

mysql> select * from students;
+------+---------+---------------+---------+------+
| id   | name    | phone         | address | job  |
+------+---------+---------------+---------+------+
|    1 | hong-gd | 010-1111-1111 | seoul   | ai   |
|    2 | kim-sd  | 02-222-2222   | NULL    | NULL |
+------+---------+---------------+---------+------+
2 rows in set (0.00 sec)

 

4. 권한 변경

mysql로 데이터 베이스를 바꾼 후에 root 권한 user 생성

grant all privileges on DB명.테이블명(or *) to '계정이름'@'호스트명' identified by '암호';
grant all privileges on *.* to '계정이름'@'호스트명' identified by '암호';
grant all privileges on *.* to 'admin'@'localhost' identified by 'admin';
grant all privileges on *.* to 'admin'@'127.0.0.1' identified by 'admin';
-- 저장
flush privileges;

 

---

[join]

use employees;

1. inner join

mysql> select * from employees limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)

mysql> select * from dept_emp limit 5;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d005    | 1986-06-26 | 9999-01-01 |
|  10002 | d007    | 1996-08-03 | 9999-01-01 |
|  10003 | d004    | 1995-12-03 | 9999-01-01 |
|  10004 | d004    | 1986-12-01 | 9999-01-01 |
|  10005 | d003    | 1989-09-12 | 9999-01-01 |
+--------+---------+------------+------------+
5 rows in set (0.00 sec)

- inner join 적용 후 쿼리문 작성

mysql> select *
    -> from employees emp inner join dept_emp de on emp.emp_no = de.emp_no
    -> limit 5;
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | emp_no | dept_no | from_date  | to_date    |
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |  10001 | d005    | 1986-06-26 | 9999-01-01 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |  10002 | d007    | 1996-08-03 | 9999-01-01 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |  10003 | d004    | 1995-12-03 | 9999-01-01 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |  10004 | d004    | 1986-12-01 | 9999-01-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |  10005 | d003    | 1989-09-12 | 9999-01-01 |
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
5 rows in set (0.00 sec)

mysql> select *
    -> from employees join dept_emp using(emp_no)
    -> limit 5;
+--------+------------+------------+-----------+--------+------------+---------+------------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | from_date  | to_date    |
+--------+------------+------------+-----------+--------+------------+---------+------------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 | d005    | 1986-06-26 | 9999-01-01 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 | d007    | 1996-08-03 | 9999-01-01 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 | d004    | 1995-12-03 | 9999-01-01 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 | d004    | 1986-12-01 | 9999-01-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 | d003    | 1989-09-12 | 9999-01-01 |
+--------+------------+------------+-----------+--------+------------+---------+------------+------------+
5 rows in set (0.00 sec)

mysql> select first_name, dept_no
    -> from employees emp inner join dept_emp de on emp.emp_no = de.emp_no
    -> where de.to_date = "9999-01-01"
    -> limit 10;
+------------+---------+
| first_name | dept_no |
+------------+---------+
| Georgi     | d005    |
| Bezalel    | d007    |
| Parto      | d004    |
| Chirstian  | d004    |
| Kyoichi    | d003    |
| Anneke     | d005    |
| Tzvetan    | d008    |
| Sumant     | d006    |
| Duangkaew  | d006    |
| Patricio   | d005    |
+------------+---------+
10 rows in set (0.00 sec)

 

2. natural join

mysql> select *
    -> from employees emp natural join titles tt
    -> limit 50;
+--------+------------+------------+-------------+--------+------------+--------------------+------------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  | title              | from_date  | to_date    |
+--------+------------+------------+-------------+--------+------------+--------------------+------------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello     | M      | 1986-06-26 | Senior Engineer    | 1986-06-26 | 9999-01-01 |
|  10002 | 1964-06-02 | Bezalel    | Simmel      | F      | 1985-11-21 | Staff              | 1996-08-03 | 9999-01-01 |
|  10003 | 1959-12-03 | Parto      | Bamford     | M      | 1986-08-28 | Senior Engineer    | 1995-12-03 | 9999-01-01 |
|  10004 | 1954-05-01 | Chirstian  | Koblick     | M      | 1986-12-01 | Engineer           | 1986-12-01 | 1995-12-01 |
|  10004 | 1954-05-01 | Chirstian  | Koblick     | M      | 1986-12-01 | Senior Engineer    | 1995-12-01 | 9999-01-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak    | M      | 1989-09-12 | Senior Staff       | 1996-09-12 | 9999-01-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak    | M      | 1989-09-12 | Staff              | 1989-09-12 | 1996-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig     | F      | 1989-06-02 | Senior Engineer    | 1990-08-05 | 9999-01-01 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski   | F      | 1989-02-10 | Senior Staff       | 1996-02-11 | 9999-01-01 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski   | F      | 1989-02-10 | Staff              | 1989-02-10 | 1996-02-11 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi    | M      | 1994-09-15 | Assistant Engineer | 1998-03-11 | 2000-07-31 |
|  10009 | 1952-04-19 | Sumant     | Peac        | F      | 1985-02-18 | Assistant Engineer | 1985-02-18 | 1990-02-18 |
|  10009 | 1952-04-19 | Sumant     | Peac        | F      | 1985-02-18 | Engineer           | 1990-02-18 | 1995-02-18 |
|  10009 | 1952-04-19 | Sumant     | Peac        | F      | 1985-02-18 | Senior Engineer    | 1995-02-18 | 9999-01-01 |

 

* 문제

-- 부서 이름과 관리자 이름을 출력
select dept_name, first_name
from (select * from departments natural join dept_manager) dept inner join employees emp on dept.emp_no = emp.emp_no
where to_date = '9999-01-01';

select dept_name, first_name
from departments d join dept_manager dm on d.dept_no = dm.dept_no
join employees e on dm.emp_no = e.emp_no
where dm.to_date = '9999-01-01';

select dept_name, first_name
from departments join dept_manager using(dept_no)
join employees using(emp_no)
where to_date = '9999-01-01';

select dept_name, first_name
from departments natural join dept_manager
natural join employees
where to_date = '9999-01-01';

 

3. outer join

mysql> select * from join_a;
+------+------+
| aa   | ab   |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    3 | ccc  |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from join_b;
+------+------+
| bb   | ab   |
+------+------+
|    4 | aaa  |
|    5 | bbb  |
|    6 | ccc  |
+------+------+
3 rows in set (0.00 sec)

- left join

mysql> select * from join_a left join join_b on join_a.ab = join_b.ab;
+------+------+------+------+
| aa   | ab   | bb   | ab   |
+------+------+------+------+
|    1 | aaa  |    4 | aaa  |
|    2 | bbb  |    5 | bbb  |
|    3 | ccc  |    6 | ccc  |
|    7 | ddd  | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)

mysql> select *
    -> from join_a left join join_b using(ab);
+------+------+------+
| ab   | aa   | bb   |
+------+------+------+
| aaa  |    1 |    4 |
| bbb  |    2 |    5 |
| ccc  |    3 |    6 |
| ddd  |    7 | NULL |
+------+------+------+
4 rows in set (0.01 sec)

- right join

mysql> select * from join_a right join join_b on join_a.ab = join_b.ab;
+------+------+------+------+
| aa   | ab   | bb   | ab   |
+------+------+------+------+
|    1 | aaa  |    4 | aaa  |
|    2 | bbb  |    5 | bbb  |
|    3 | ccc  |    6 | ccc  |
| NULL | NULL |    8 | eee  |
+------+------+------+------+
4 rows in set (0.00 sec)

mysql> select *
    -> from join_a right join join_b using(ab);
+------+------+------+
| ab   | bb   | aa   |
+------+------+------+
| aaa  |    4 |    1 |
| bbb  |    5 |    2 |
| ccc  |    6 |    3 |
| eee  |    8 | NULL |
+------+------+------+
4 rows in set (0.00 sec)

 

4. 서브쿼리

mysql> select salary
    -> from salaries
    -> where emp_no in
    ->     (select emp_no
    ->     from employees
    ->     where last_name = 'Haraldson')
    -> limit 50;
+--------+
| salary |
+--------+
|  54398 |
|  55586 |
|  57195 |
|  58209 |
|  57770 |
|  59188 |
|  60763 |
|  64797 |
|  68037 |
|  69404 |
|  70575 |