🐷전동준(Jeon DongJun)

Go with the flow

[MySQL]기초 복습

MySQL 복습!

  • RDBMS(관계형 데이터베이스 관리 시스템)인 MySQL에 대해서 간단하게 배워봤는데, 그 내용을 복습하려고 한다.

Database

  • 데이터 베이스란?
    • 많은 사람이 정보에 접근하기 위해서 만든 데이터의 집합이다.
    • DBMS은 데이터가 정형, 반정형, 비정형이냐에 따라 나뉜다.
  • RDMBS
    • Relational Database Management System
    • 관계형 데이터 베이스
    • 정형 데이터들의 관계를 정의해서 관리하는 방식이다.
  • NoSQL
    • Not Only SQL
    • 비정형 데이터를 저장한다.
    • 빅데이터를 분산 저장하는데에 용이하다.

RDBMS

  • Enity(table) : 데이터베이스에 저장하려는 현실상 객체인데, 이는 테이블로 저장된다.
  • Attribute(column) : Entity(객체)의 속성으로 열(column)으로 표현
  • Tuple(row) : Entity(객체)의 값으로 속성 값이라고도 부르며, 행(row)로 표현


SQL

  • SQL이란?
    • Structured Query Language
    • 구조화된 질의 언어
  • SQL은 3가지 명령어로 구성되어 있다.
    • DDL(Data Definition Language)
      • 데이터 정의 언어
      • DB의 스키마를 정의하고 조작한다.
      • CREATE, ALTER, DROP 등이 있다.
    • DML(Data Manipulation Language)
      • 데이터 조작 언어
      • 테이블 안에 있는 데이터들을 조작할 때 사용한다.
      • INSERT, DELETE, UPDATE, SELECT 등이 존재한다.(CRUD기능)
    • DCL(Data Control Language)
      • 데이터 제어 언어
      • 데이터 접근 권한을 부여/수거 하는 GRANT/REVOKE
      • TCL(트랜잭션 컨트롤 언어)
        • COMMIT : 트랜잭션이 일어나고 변화가 일어난 현재 상태를 데이터베이스 서버에 저장
        • ROLLBACK : 중간에 작업이 잘못되거나 원치 않은 방향으로 흘러 갔을 때 가장 마지막 COMMIT상태로 복구

MySQL

  • 1995년에 발표된 오픈 소스 DBMS
  • 실질적인 소유주는 오라클(Oracle)
  • 오픈소스이므로 무료로 사용할 수 있다.
  • 관계형 데이터베이스 관리 시스템(RDBMS)

설치 방법

  • MySQL 공식 홈페이지에 들어간다.
  • downlaods에 들어가서 MySQL Community Server를 클릭
  • https://dev.mysql.com/downloads/mysql/
  • 위의 링크를 누르면 각자의 컴퓨터에 맞는 운영체제를 고를 수 있다.
  • 그리고 사진의 빨간 박스를 클릭
  • image
  • 그러면 이러한 화면이 뜨는데 사실 두파일의 차이는 크게 차이가 없다.
  • image
  • 첫 번째는 용량이 적으나 인터넷에서 파일을 불러오며 다운을 받는 방법(?)이며
  • 두 번째는 이미 완전한 파일을 다 다운 받는 방식이다.
  • 나는 어차피 다 다운 받을 생각이라 밑에걸로 다운 받았다.
  • 다운이 완료되면 Installer를 실행시켜 쭉쭉 Ok를 체크하며 다운해준다.
  • 이때 기본 포트는 3306일 것이다. Root는 매우매우 중요하니 비밀번호를 정할 때는 까먹지 않게 주의해서 설정해주자.
  • 만약에 까먹는다면 재설치를하고 resmon.exe를 사용해서 포트사용을 중지해주고 다시 할 수 있지만 굉장히 번거롭다.

환경 설정

  • MySQL을 다운 받으면 이제 DB시스템을 만져볼 수 있다.
  • MySQL에서는 Workbench라는 프로그램을 통해서 수월하게 데이터를 만질 수 있다.
  • 하지만 지금은 명령 프롬프트를 사용해서 서버에 접속하고 데이베이스를 사용할 것이다.
  • MySQL.exe가 명령프롬프트의 경로에 상관없이 실행되기 위해서는 컴퓨터 속성 변경으로 path를 지정해줘야 한다.
  • 위에서 MySQL Server를 다운 받았고, 나처럼 윈도우 10을 사용한다면 MySQL.exe의 경로는 다음과 같을것이다.
  • C:\Program Files\MySQL\MySQL Server 8.0\bin
  • 다음은 [내 PC] -[마우스 우클릭] - [속성]
  • image
  • 여기서 고급 시스템 설정으로 들어가준다.
  • [고급] - [환경 변수] 클릭
  • 이러면 시스템 변수 안에 Path가 있을 것이다. 그걸 누르고 편집을 눌러준다.
  • image
  • 그리고 위의 경로(C:\Program Files\MySQL\MySQL Server 8.0\bin)를 추가해주면 완료다!
  • 이제 명령프롬프트로 MySQL server에 접속할 수 있다!

실습 준비물

  • 데이터가 없으면 실습하기 번거롭다. 그래서 MySQL에서는 다양한 예시 데이터(Example Databases)를 제공하고 있다.
  • https://dev.mysql.com/doc/index-other.html
  • 그 중 employee data를 사용할 것이며, 이것은 Github 홈페이지에서 다운 받을 수 있다.
  • https://github.com/datacharmer/test_db
  • 거기서 Code를 눌러 ZIP파일을 다운해준다.
  • image
  • 원하는 곳에 압축을 풀어주면 실습 준비 완료다!

실습

  • 정말정말 간단하게 배웠기 때문에 했던 것들 위주로 다시 복습해보겠다.
  • 모르는 점들은 MySQL 공식 사이트에 DOCUMENTATION의 MySQL SERVER 탭을 눌러서 찾아보자.
  • https://dev.mysql.com/doc/refman/8.0/en/

서버 접속

  • [윈도우키] + R cmd를 쳐서 명령프롬프트를 켜준다.

  • MySQL 서버에 접속해보자

  • mysql -u root -p

  • root라는 포트로 서버에 접속하는 것이다. 설치 시에 정했던 비밀번호를 쳐주면 다음과같이 나올 것이다.

  • Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.28 MySQL Community Server - GPL
  
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
  
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
mysql>
  • mysql> 이 나오면서 mysql을 사용할 수 있게 되었다.

데이터베이스 확인 및 생성

  • show databases;를 적어주면 어떠한 데이터 베이스들이 존재하는지 확인할 수 있다.

  • mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

  • 확인해보면 6개의 데이터베이스가 있는데, 이는 설치할 때 기본적으로 깔려있는 데이터베이스들이다.

  • 데이터베이스를 생성해보자. create database 데이터베이스명; 을 입력해주면 데이터베이스가 생성된다.

  • 데이터를 다루기 위해 생성한 데이터베이스를 선택하자. use 데이터베이스명;을 입력해주면 된다.

  • 그리고 테이블이 있는지 확인해보자. 없다면 새로운 데이터베이스가 잘 생성된 것을 확인할 수 있다.

  • mysql> create database goodjeon
    -> ;
Query OK, 1 row affected (0.01 sec)
  
mysql> use goodjeon
Database changed
mysql> show tables;
Empty set (0.01 sec) # 데이터베이스가 비어있는 것을 확인

테이블 생성 및 구성요소 확인

  • 이제 만들어준 데이터에 테이블을 생성해보자.

  • create table 테이블명(속성들);

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

  • goodjeon 데이터베이스 안에 students라는 테이블을 생성했다.

  • id, name, phone, address 라는 속성(Attribute)를 만들어줬다.

  • 그 속성들의 특성은 각각 정수, 가변길이 100, 고정길이 13, 가변길이 1000으로 설정해줬다.

  • 이제 테이블이 생성되었는지, 그리고 속성이 잘 적용이 되었는지 확인해보자.

  • desc students; 을 입력하면 된다.(desc는 description의 약어)

  • 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    |       |
+---------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

데이터 삽입/읽기

  • students라는 테이블이 생성되었고 거기다가 이제 데이터를 삽입해보자!

  • 데이터를 삽입할 때는 insert into 테이블명 values(속성값1, 속성값2, ..., 속성값n);을 적어준다.

  • insert into students
values(1, 'hong-gd', '010-1111-1111', 'seoul');
Query OK, 1 row affected (0.00 sec)

  • select를 사용해서 데이터가 잘 입력되었는지 확인해보자.

  • select 컬럼명 from 테이블명;

  • mysql> select * from students; # 여기서 *는 모든 컬럼을 뜻함
+------+---------+---------------+---------+
| id   | name    | phone         | address |
+------+---------+---------------+---------+
|    1 | hong-gd | 010-1111-1111 | seoul   |
+------+---------+---------------+---------+
1 row in set (0.00 sec)

테이블 수정(컬럼 추가)

  • 현재 students 테이블의 속성(컬럼)은 id, name, phone, address다.

  • 여기서 새로운 컬럼을 추가해줄 수 있다. alter 명령어

  • alter table 테이블명 add 컬럼명 속성;

  • mysql> alter table students # students 테이블에
    -> add job varchar(100); # job(직업) 속성을 추가, 가변길이 100
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
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.00 sec)

  • 추가된 컬럼이 제대로 작동하는지 확인하기 위해 새로운 데이터를 삽입하고, 데이터를 조회해보자.

  • mysql> insert into students(id, name, address, job)
    -> values(2, 'jeon-dj', 'anyang', 'engineer');
Query OK, 1 row affected (0.00 sec)
  
mysql> select id, name, phone, address, job from students;
+------+---------+---------------+---------+----------+
| id   | name    | phone         | address | job      |
+------+---------+---------------+---------+----------+
|    1 | hong-gd | 010-1111-1111 | seoul   | NULL     |
|    2 | jeon-dj | NULL          | anyang  | engineer |
+------+---------+---------------+---------+----------+
2 rows in set (0.00 sec)
  • 새로 삽입한 데이터가 잘 들어간 것을 확인할 수 있다. 이 때 phone 속성의 속성값 char(13)을 지켜서 삽입하지 않았기 때문에 NULL 값이 자동으로 적용되는 것도 확인할 수 있다.

테이블 수정(컬럼 타입 변경)

  • 속성(컬럼)의 타입도 변경할 수 있다.

  • 위에 만들었던 job 속성을 varchar(100) -> varchar(1000)으로 변경해보자.

  • mysql> alter table students
    -> modify job varchar(1000);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
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(1000) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
  • vachar(1000)으로 변경된 것을 확인할 수 있다.

테이블 삭제

  • 테이블을 생성(create)하고 변경(alter)해줬으면 삭제(drop)도 할 수 있다.

  • drop table 테이블명;

  • mysql> drop table students; # students 테이블을 삭제
Query OK, 0 rows affected (0.03 sec)
  
mysql> show tables; 
Empty set (0.00 sec) # 테이블이 비어있는 것을 확인할 수 있다.

데이터베이스 삭제

  • 테이블을 삭제한 것처럼 데이터베이스도 삭제할 수 있다.

  • 명령어는 동일하다.

  • drop database 데이터베이스명;

  • mysql> drop database goodjeon; # goodjeon 데이터베이스 삭제
Query OK, 0 rows affected (0.01 sec)
  
mysql> show databases; # 데이터베이스 목록 확인, goodjeon이 사라진걸 확인할 수 있음
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

서버 종료

  • 프롬프트에 exit;을 입력하면 MySQL 서버가 종료된다.

실습 2

  • 이번 실습은 위에서 MySQL에서 제공한 employee 데이터를 이용할 것이다.

  • 데이터를 다운 받고 압축을 풀어주자. 나는 c드라이브에 압축을 풀어줬다.

  • 폴더명은 test_db-master로 cmd를 켜서 경로를 지정해주자.

  • cd test_db-master

  • 그리고 그곳에서 MySQL 서버에 접속해보자.

  • C:\>cd test_db-master
  
C:\test_db-master>mysql -u root -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.28 MySQL Community Server - GPL
  
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
  
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
mysql>

예제 데이터 소스 불러오기

  • employees.sql 소스를 불러올 것이다.

  • source employees.sql;

  • 그러면 뭔가 적용되는 것이 보이면서 로딩이 완료될 것이다.

  • +---------------------+
| data_load_time_diff |
+---------------------+
| 00:01:36            |
+---------------------+
1 row in set (0.01 sec)

  • 완료가 되었다면 employees 데이터베이스를 선택해주자.

  • use employees;

  • show, desc를 사용해 테이블들과 테이블들의 구성요소 등을 확인해보자.

  • image

    사진 출처 : https://dev.mysql.com/doc/employee/en/sakila-structure.html

  • employees 예제 데이터베이스는 이러한 구조의 데이터베이스다.

limit(개수 제한걸기)

  • 이제 DCL 명령어를 사용해 볼 것이다.

  • 하지만, 이 데이터는 상당히 많은 수의 데이터를 갖고 있으므로.. 자칫 데이터를 잘못 조회하면 무한 로딩지옥에 걸릴 것이다.(데이터 수가 너무너무너무 많아서)

  • 그러므로 일정 샘플만 가져와서 기다림을 최소화해주자.

  • 우선 select문을 사용해 employees 테이블의 데이터수가 몇개인지 확인해보자.

  • mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.21 sec)

  • 30만 24 건에 달하는 데이터가 조회되었다.(ㄷㄷ)

  • count(*) 집계함수를 사용해서 오래걸리지 않았지 만약 count를 빼고 select * from employees;를 했다면 내 컴퓨터로는 무한정 기다려야했을 것이다.

  • 다만, 이 같은 현상은 커맨드 창을 이용해서 그런 것이고, workbench나 기타 툴을 사용하면 안그런다고한다.

  • 데이터들을 limit을 사용해 조회해보자.

  • 사원번호(emp_no), first_name(이름), last_name(성)을 조회해볼 것이다.

  • mysql> select emp_no, first_name, last_name
    -> from employees
    -> limit 10; # 10개 행만 출력
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|  10001 | Georgi     | Facello   |
|  10002 | Bezalel    | Simmel    |
|  10003 | Parto      | Bamford   |
|  10004 | Chirstian  | Koblick   |
|  10005 | Kyoichi    | Maliniak  |
|  10006 | Anneke     | Preusig   |
|  10007 | Tzvetan    | Zielinski |
|  10008 | Saniya     | Kalloufi  |
|  10009 | Sumant     | Peac      |
|  10010 | Duangkaew  | Piveteau  |
+--------+------------+-----------+
10 rows in set (0.00 sec)

  • 다음으로는 employees 테이블의 전체 컬럼, 1000개 행을 출력해보자.

  • select * from employees limit 1000;

where 절로 조건 걸기

  • where절을 사용하면 컬럼들의 속성값을 활용하여 원하는 조건의 데이터들을 조회할 수 있다.

  • 2000년 1월 1일부터 입사한 사람들을 조회해보자.

  • select *
from employees
where hire_date >= '2000-01-01'
limit 10;
+--------+------------+-------------+------------+--------+------------+
| emp_no | birth_date | first_name  | last_name  | gender | hire_date  |
+--------+------------+-------------+------------+--------+------------+
|  47291 | 1960-09-09 | Ulf         | Flexer     | M      | 2000-01-12 |
|  60134 | 1964-04-21 | Seshu       | Rathonyi   | F      | 2000-01-02 |
|  72329 | 1953-02-09 | Randi       | Luit       | F      | 2000-01-02 |
| 108201 | 1955-04-14 | Mariangiola | Boreale    | M      | 2000-01-01 |
| 205048 | 1960-09-12 | Ennio       | Alblas     | F      | 2000-01-06 |
| 222965 | 1959-08-07 | Volkmar     | Perko      | F      | 2000-01-13 |
| 226633 | 1958-06-10 | Xuejun      | Benzmuller | F      | 2000-01-04 |
| 227544 | 1954-11-17 | Shahab      | Demeyer    | M      | 2000-01-08 |
| 422990 | 1953-04-09 | Jaana       | Verspoor   | F      | 2000-01-11 |
| 424445 | 1953-04-27 | Jeong       | Boreale    | M      | 2000-01-03 |
+--------+------------+-------------+------------+--------+------------+
10 rows in set (0.13 sec)

  • 다음은 급여(salaries) 테이블을 사용해보자.

  • 급여가 150000달러 이상인 경우를 조회

  • mysql> select *
    -> from salaries
    -> where salary >= 150000
    -> limit 10;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  43624 | 151115 | 1998-03-23 | 1999-03-23 |
|  43624 | 153166 | 1999-03-23 | 2000-03-22 |
|  43624 | 153458 | 2000-03-22 | 2001-03-22 |
|  43624 | 157821 | 2001-03-22 | 2002-03-22 |
|  43624 | 158220 | 2002-03-22 | 9999-01-01 |
|  46439 | 150345 | 2002-05-15 | 9999-01-01 |
|  47978 | 151929 | 2001-07-14 | 2002-07-14 |
|  47978 | 155709 | 2002-07-14 | 9999-01-01 |
|  66793 | 150052 | 2002-06-16 | 9999-01-01 |
|  80823 | 151768 | 2001-02-22 | 2002-02-22 |
+--------+--------+------------+------------+
10 rows in set (0.21 sec)

  • 파이썬과 같이 and(이면서), or(혹은)을 사용해서 조건을 더 상세히할 수도 있다.

  • 월급이 100000보다 크고 150000보다 작거나 같은 데이터 10개 출력

  • select * 
from salaries
where salary > 100000 and salary <= 150000
limit 10;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10066 | 102425 | 2000-02-23 | 2001-02-22 |
|  10066 | 102674 | 2001-02-22 | 2002-02-22 |
|  10066 | 103672 | 2002-02-22 | 9999-01-01 |
|  10068 | 100005 | 1993-08-05 | 1994-08-05 |
|  10068 | 101829 | 1994-08-05 | 1995-08-05 |
|  10068 | 101630 | 1995-08-05 | 1996-08-04 |
|  10068 | 105533 | 1996-08-04 | 1997-08-04 |
|  10068 | 106204 | 1997-08-04 | 1998-08-04 |
|  10068 | 108345 | 1998-08-04 | 1999-08-04 |
|  10068 | 111623 | 1999-08-04 | 2000-08-03 |
+--------+--------+------------+------------+
10 rows in set (0.00 sec)

  • 1965년에 태어난 사원의 이름(first_name)과 생일을 10개만 출력

  • mysql> select first_name, birth_date
    -> from employees
    -> where birth_date >= '1965-01-01' and birth_date <= '1965-12-31'
    -> limit 10;
+------------+------------+
| first_name | birth_date |
+------------+------------+
| Hilari     | 1965-01-03 |
| Ohad       | 1965-01-19 |
| Dipayan    | 1965-01-23 |
| Takahito   | 1965-01-19 |
| Kokou      | 1965-01-01 |
| Make       | 1965-01-25 |
| Teunis     | 1965-01-09 |
| Lech       | 1965-01-19 |
| Juyoung    | 1965-01-24 |
| Jeanna     | 1965-01-24 |
+------------+------------+
10 rows in set (0.00 sec)

order by(정렬)

  • 어느 컬럼을 기준으로 데이터를 정렬해서 조회할 수 있다.

  • 이때 order by 컬럼명;을 적어주면 된다.

  • 오름차순(asc)가 디폴트 값이며, 내림차순은(desc)를 적어주면 된다.

  • 급여가 제일 많은 순서대로 10개만 출력해보자.

  • mysql> select *
    -> from salaries
    -> order by salary desc
    -> limit 10;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  43624 | 158220 | 2002-03-22 | 9999-01-01 |
|  43624 | 157821 | 2001-03-22 | 2002-03-22 |
| 254466 | 156286 | 2001-08-04 | 9999-01-01 |
|  47978 | 155709 | 2002-07-14 | 9999-01-01 |
| 253939 | 155513 | 2002-04-11 | 9999-01-01 |
| 109334 | 155377 | 2000-02-12 | 2001-02-11 |
| 109334 | 155190 | 2002-02-11 | 9999-01-01 |
| 109334 | 154888 | 2001-02-11 | 2002-02-11 |
| 109334 | 154885 | 1999-02-12 | 2000-02-12 |
|  80823 | 154459 | 2002-02-22 | 9999-01-01 |
+--------+--------+------------+------------+
10 rows in set (1.23 sec)

  • 컬럼 여러 개를 기준으로 조회를 중복시킬 수도 있다.

  • order by 컬럼명1, 컬럼명2..; : 컬럼명 1 기준으로 정렬 후, 그 상태에서 컬럼명 2 기준으로 정렬

  • 나이가 많은 순서대로, 늦게 취업한 순서대로 조회

  • mysql> select *
    -> from employees
    -> order by birth_date, hire_date desc
    -> limit 10;
+--------+------------+------------+--------------+--------+------------+
| emp_no | birth_date | first_name | last_name    | gender | hire_date  |
+--------+------------+------------+--------------+--------+------------+
|  91374 | 1952-02-01 | Eishiro    | Kuzuoka      | M      | 1992-02-12 |
| 406121 | 1952-02-01 | Supot      | Remmele      | M      | 1989-01-27 |
| 207658 | 1952-02-01 | Kiyokazu   | Whitcomb     | M      | 1988-07-26 |
| 237571 | 1952-02-01 | Ronghao    | Schaad       | M      | 1988-07-10 |
|  87461 | 1952-02-01 | Moni       | Decaestecker | M      | 1986-10-06 |
|  65308 | 1952-02-01 | Jouni      | Pocchiola    | M      | 1985-03-10 |
|  48910 | 1952-02-02 | Zhongwei   | DuBourdieux  | M      | 1999-12-19 |
|  33131 | 1952-02-02 | Reinhold   | Savasere     | M      | 1998-01-30 |
| 462774 | 1952-02-02 | Moie       | Chinal       | F      | 1997-09-20 |
| 436596 | 1952-02-02 | Gou        | Pearson      | F      | 1997-08-14 |
+--------+------------+------------+--------------+--------+------------+
10 rows in set (0.27 sec)

group by(그룹화)

  • 속성을 기준으로 데이터를 그룹화 시킬 수 있다.

  • titles라는 테이블의 속성은 이렇다.

  • mysql> desc titles;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no    | int         | NO   | PRI | NULL    |       |
| title     | varchar(50) | NO   | PRI | NULL    |       |
| from_date | date        | NO   | PRI | NULL    |       |
| to_date   | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

  • 여기서 title은 담당직무을 뜻한다. emp_no는 사원번호, from_date는 일 시작날짜, to_date는 일 끝날짜.

  • 여기서 title을 기준으로 묶어서 데이터를 조회해보자.

  • mysql> select title from titles group by title;
+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+
7 rows in set (0.28 sec)

  • title기준으로 묶여 title을 조회했을 때, 총 7개의 담당직무가 있는 것을 확인할 수 있다.

  • 그리고 담당직무별로 몇명이 존재하는지도 확인할 수도 있다.

  • 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.52 sec)

having

  • 그룹화를 했다면 집계함수를 사용할 수 있다.

  • 집계 함수에 조건을 넣고 싶을 때는, where절 대신에 having절을 이용하면 된다.

  • dept_emp는 사원번호, 부서번호 등의 속성을 갖고 있다. 이 테이블을 이용해 having절을 사용해보자.

  • 우선 부서 별 사원수를 출력해보자.

  • mysql> select dept_no, count(dept_no)
    -> from dept_emp
    -> group by dept_no;
+---------+----------------+
| dept_no | count(dept_no) |
+---------+----------------+
| d001    |          20211 |
| d002    |          17346 |
| d003    |          17786 |
| d004    |          73485 |
| d005    |          85707 |
| d006    |          20117 |
| d007    |          52245 |
| d008    |          21126 |
| d009    |          23580 |
+---------+----------------+
9 rows in set (0.26 sec)

  • count 집계 함수를 이용해 부서 별 직원의 수를 확인할 수 있다.

  • 다음으로는 having절을 이용해볼 차례다.

  • 사원수가 5만명 이상인 부서만 출력해보자.

  • mysql> select dept_no, count(dept_no)
    -> from dept_emp
    -> group by dept_no
    -> having count(dept_no) >= 50000;
+---------+----------------+
| dept_no | count(dept_no) |
+---------+----------------+
| d004    |          73485 |
| d005    |          85707 |
| d007    |          52245 |
+---------+----------------+
3 rows in set (0.10 sec)
  • 집계함수를 having절에 넣어 조건을 부여하는 것을 확인할 수 있다.

실습 3

  • 이번엔 DCL(데이터 제어언어)를 실습할 것이다.

  • 실습을 위해서 DB를 생성하고 테이블을 만들어 값을 넣어주자.

  • mysql> create database goodjeon; # goodjeon DB 생성
Query OK, 1 row affected (0.01 sec)
  
mysql> use goodjeon; # goodjeon DB 선택
Database changed
mysql> create table students(id int, name varchar(100), phone char(13), address varchar(1000), job varchar(100)); 
# id, name phone, address, job 속성을 가진 students 테이블 생성
Query OK, 0 rows affected (0.03 sec)
  
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)

commit/rollback

  • 아마 mysql을 다운받으면 autocommit 이 적용되어 있을 것이다. 그거 먼저 풀어주자.

  • set autocommit = 0;

  • students 테이블에 데이터를 삽입해주자.

  • mysql> insert into students
    -> values (1, 'jeon-dj', '010-1234-5678', 'anyang', 'ai');
Query OK, 1 row affected (0.00 sec)
mysql> insert into students(id, name, phone)
    -> values (2, 'kim-sd', '02-222-2222');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+------+---------+---------------+---------+------+
| id   | name    | phone         | address | job  |
+------+---------+---------------+---------+------+
|    1 | jeon-dj | 010-1234-5678 | anyang  | ai   |
|    2 | kim-sd  | 02-222-2222   | NULL    | NULL |
+------+---------+---------------+---------+------+
2 rows in set (0.00 sec)

  • 여기서 데이터 제어 명령어인 commit을 입력해주면 트랜잭션이 완료되고 서버에 변경사항이 저장된다.

  • mysql> commit;
Query OK, 0 rows affected (0.00 sec)

  • 삽입된 데이터를 수정해줘보자.

  • update students # students 테이블의
set phone = '010-2222-2222', # phone 속성의 값을 010-2222-2222로
address = 'suwon', # address 속성의 값을 suwon으로
job = 'engineer' # job 속성의 값을 engineer로  변경할 것이다.
where id = 2;  # 무엇을? id가 2인 데이터를
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

  • 데이터를 삭제도 해줘보자.

  • mysql> delete from students
    -> where id = 1;
Query OK, 1 row affected (0.00 sec)

  • students 테이블에 변경사항이 적용되었는지 확인해보자.

  • mysql> select * from students;
+------+--------+---------------+---------+----------+
| id   | name   | phone         | address | job      |
+------+--------+---------------+---------+----------+
|    2 | kim-sd | 010-2222-2222 | suwon   | engineer |
+------+--------+---------------+---------+----------+
1 row in set (0.00 sec)

  • 여기서 rollback하고 다시 확인해보면

  • mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
  
mysql> select * from students;
+------+---------+---------------+---------+------+
| id   | name    | phone         | address | job  |
+------+---------+---------------+---------+------+
|    1 | jeon-dj | 010-1234-5678 | anyang  | ai   |
|    2 | kim-sd  | 02-222-2222   | NULL    | NULL |
+------+---------+---------------+---------+------+
2 rows in set (0.00 sec)
  • 가장 마지막 commit 상태로 돌아가는 것을 확인할 수 있다.

grant/revoke

  • 이 부분은 빠르게 지나가서 다시 공부해야한다.
  • use mysql : mysql 데이터베이스 선택
  • grant all privileges on DB명 to '유저아이디'@'접속서버' identified by '패스워드' with grant option;
    • 일반 사용자를 추가한다.
  • grant all privileges on *.* to 'root'@'%' with grant option;
    • 전체 DB에 대해 권한을 가진 사용
  • flush privileges;
    • grant 테이블을 reload해서 변경사항을 바로 적용

실습 4

  • 테이블을 합치는 join을 사용해보자.
  • 여기서는 employees DB를 사용하겠다.
  • use employees;
  • 위에 예제데이터 소스를 불러오는 설명을 적은 부분에 employees 데이터베이스 구조가 시각화 되어있다.

inner join

  • 현재 근무하고 있는 직원들의 신상을 조회하고 싶다.

  • employees와 dept_emp 두 테이블을 연결해야한다.

  • select * from employees;와 select * from dept_emp;가 같이 나오게 하고 싶은 것이다.

  • 이럴땐 inner join을 사용하면 된다.

  • select * from 테이블A inner join 테이블B on 테이블A.컬럼 = 테이블B.컬럼;

  • 두 테이블 모두 emp_no이라는 속성을 가지고 있어서 그것을 기준으로 합칠 수 있다.

  • to_date가 ‘9999-01-01’이면 퇴사를 하지 않고 현재까지 다니고 있다는 것을 말함

  • mysql> select *
    -> from employees emp inner join dept_emp de on emp.emp_no = de.emp_no
    -> where de.to_date = '9999-01-01'
    -> limit 10;
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
| 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 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |  10006 | d005    | 1990-08-05 | 9999-01-01 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |  10007 | d008    | 1989-02-10 | 9999-01-01 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |  10009 | d006    | 1985-02-18 | 9999-01-01 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |  10010 | d006    | 2000-06-26 | 9999-01-01 |
|  10012 | 1960-10-04 | Patricio   | Bridgland | M      | 1992-12-18 |  10012 | d005    | 1992-12-18 | 9999-01-01 |
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
10 rows in set (0.00 sec)

  • 확인해보면 테이블이 잘 합쳐졌고, 연결 컬럼 emp_no가 두개나 출력된 것을 확인할 수 있다.

  • join을 사용해도 동일한 결과가 나온다.

using()

  • using을 사용하면 on 테이블A.컬럼 = 테이블B.컬럼 이렇게 적지 않아도 된다.

  • mysql> select * from employees join dept_emp using(emp_no) where dept_emp.to_date = '9999-01-01' limit 10;
+--------+------------+------------+-----------+--------+------------+---------+------------+------------+
| 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 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 | d005    | 1990-08-05 | 9999-01-01 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 | d008    | 1989-02-10 | 9999-01-01 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 | d006    | 1985-02-18 | 9999-01-01 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 | d006    | 2000-06-26 | 9999-01-01 |
|  10012 | 1960-10-04 | Patricio   | Bridgland | M      | 1992-12-18 | d005    | 1992-12-18 | 9999-01-01 |
+--------+------------+------------+-----------+--------+------------+---------+------------+------------+
10 rows in set (0.00 sec)
  • using을 사용했을 때는 연결 컬럼인 emp_no 컬럼이 2개가 아닌 1개만 출력 되는 것을 확인할 수 있다.

natural join

  • natural join을 사용하면 join에 using을 사용했을 때 처럼 연결 컬럼이 하나만 출력되는 것을 확인할 수 있다.

  • natural join은 자동으로 연결되는 컬럼이 1개가 존재 했을때 연결해주는 join이다.

  • mysql> select * from employees natural join dept_emp where dept_emp.to_date = '9999-01-01' limit 10;
+--------+------------+------------+-----------+--------+------------+---------+------------+------------+
| 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 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 | d005    | 1990-08-05 | 9999-01-01 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 | d008    | 1989-02-10 | 9999-01-01 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 | d006    | 1985-02-18 | 9999-01-01 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 | d006    | 2000-06-26 | 9999-01-01 |
|  10012 | 1960-10-04 | Patricio   | Bridgland | M      | 1992-12-18 | d005    | 1992-12-18 | 9999-01-01 |
+--------+------------+------------+-----------+--------+------------+---------+------------+------------+
10 rows in set (0.00 sec)

카테시안(cartesian) 곱

  • 테이블 끼리 연결 할 때 join을 사용하면서 on으로 연결되는 같은 컬럼이 있어야한다.

  • 그게아니라면 속성들끼리 얽히고 설켜 수많은 데이터가 생겨버린다.

  • employees 테이블과 titles 테이블을 emp_no로 연결해보자.

  • mysql> select count(*)
    -> from employees join titles using(emp_no);
+----------+
| count(*) |
+----------+
|   443308 |
+----------+
1 row in set (1.27 sec)

  • 연결 컬럼이 있어 443308개의 데이터를 확인할 수 있다.

  • 하지만 on 조건을 빼고 실행한다면?

  • mysql> select count(*)
    -> from employees join titles;
+--------------+
| count(*)     |
+--------------+
| 133003039392 |
+--------------+
1 row in set (0.36 sec)

  • 엄청난 수의 데이터가 나온다..ㅋㅋㅋㅋ

  • cross join을 해도 카테시안 곱이 나온다.

  • 카테시안 곱은 발생 가능한 모든 경우의 수의 행이 출력되는 현상이다.

outer join

  • mysql은 full outer join은 없다.

  • 저런 효과를 내고 싶다면 left outer, right outer를 순서대로 하면 된다.

  • left outer join : 왼쪽 테이블의 기준에 맞춰 병합

  • right outer join : 오른쪽 테이블의 기준에 맞춰 병합

  • 실습으로 이해해보자.

  • mysql> use goodjeon
Database changed
mysql> create table join_a(aa int, ab char(3));
Query OK, 0 rows affected (0.04 sec)
  
mysql> create table join_b(bb int, ab char(3));
Query OK, 0 rows affected (0.02 sec)
  
mysql> insert into join_a
    -> values(1, 'aaa');
Query OK, 1 row affected (0.01 sec)
  
mysql> insert into join_a
    -> values(2, 'bbb');
Query OK, 1 row affected (0.00 sec)
  
mysql> insert into join_a
    -> values(3, 'ccc');
Query OK, 1 row affected (0.00 sec)
  
mysql> insert into join_b
    -> values(4, 'aaa');
Query OK, 1 row affected (0.00 sec)
  
mysql> insert into join_b
    -> values(5, 'bbb');
Query OK, 1 row affected (0.00 sec)
  
mysql>
mysql> insert into join_b
    -> values(6, 'ccc');
Query OK, 1 row affected (0.00 sec)
  
mysql> insert into join_a
    -> values(7, 'ddd');
Query OK, 1 row affected (0.00 sec)
  
mysql> insert into join_b
    -> values(8, 'eee');
Query OK, 1 row affected (0.00 sec)
  
mysql> select * from join_a;
+------+------+
| aa   | ab   |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    3 | ccc  |
|    7 | ddd  |
+------+------+
4 rows in set (0.00 sec)
  
mysql> select * from join_b;
+------+------+
| bb   | ab   |
+------+------+
|    4 | aaa  |
|    5 | bbb  |
|    6 | ccc  |
|    8 | eee  |
+------+------+
4 rows in set (0.00 sec)

  • join_a, join_b 테이블을 만들어 데이터를 삽입해준 결과다.

  • 이 테이블들로 left/right outer join을 해보자.

  • 먼저 left outer join

  • 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.00 sec)

  • 연결 컬럼 ab를 기준으로 병합했는데 왼쪽 테이블 join_a의 값을 모두 반영했고, join_b에는 ab 속성 값이 ddd가 존재 하지 않아 NULL이 채워졌다. 그리고 가지고 있던 ab 속성 값 eee가 사라졌다.

  • 다음은 right outer join

  • 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)
  • 오른쪽 테이블 join_b의 값을 모두 반영했다. join_a에는 ab 속성 값이 eee가 존재하지 않아 NULL이 채워졌다. 그리고 가지고 있던 ab 속성 값 ddd 데이터가 사라졌다.

실습 5

서브쿼리(Sub Query)

  • 서브쿼리는 Query 내부에서 사용되는 select문이다.

  • 중첩된 SELECT (Nested SELECT) 라고도 부른다.

  • 종류

    • single row subquery : 결과가 1개 값
    • multi row subquery : 결과가 여러 개의 값
    • multi column subquery : where 조건 절에서 여러 개의 컬럼 값 비교
    • inline view : from 절에서 사용(가상 테이블)

  • join(테이블 합치기)로 얻기 힘든 결과들을 얻고자 할 때 사용한다고 한다.

  • 예제

    • employees DB를 사용

    • last_name이 Haraldson인 사원의 월급을 조회하라.

    • select salary from salaries where emp_no in (select emp_no from employees where last_name = 'Haraldson') limit 10;
+--------+
| salary |
+--------+
|  54398 |
|  55586 |
|  57195 |
|  58209 |
|  57770 |
|  59188 |
|  60763 |
|  64797 |
|  68037 |
|  69404 |
+--------+
10 rows in set (0.00 sec)
    • 이런식으로 from, where 등 select문에서 쿼리문을 적용시켜서 조회를 할 수 있다.

2022

[web]jQuery 복습 3

1 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]jQuery 복습 2

13 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]jQuery 복습 1

14 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리4

5 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리3

10 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리2

7 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리1

8 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]CSS 기초 정리

11 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]HTML 기초 정리

8 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[Pandas]pandas 연습

3 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

맨 위로 이동 ↑

2021

[Python기초]module

1 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

맨 위로 이동 ↑