[MariaDB] 6. 샘플데이터 다운로드 및 DB 스키마 조회하기

Maria DB 를 이용하여 DDL, DML, DCL sql 실습을 하기 전에 먼저 샘플데이터를 다운받아 데이터베이스와 테이블 구조를 조회하는 방법을 먼저 알아보도록 합시다.

1. 샘플데이터 다운로드
2. 샘플 데이터 추가
   1. source
   2. redirection
3. 데이터베이스 및 테이블 구조 조회
   1. 데이터베이스 목록 조회
   2. 데이터베이스 접근
   3. 테이블 목록 조회
   4. 테이블 스키마 조회

1. 샘플데이터 다운로드

https://dev.mysql.com/doc/index-other.html

MySQL 공식 홈페이지에는 SQL 실행 실습을 위한 샘플데이터 몇가지를 제공하고 있습니다.
전 시간에 말했듯이 MariaDB는 MySQL과 문법이 거의 유사하기 때문에 이 샘플데이터를 활용하여 SQL 실습이 가능합니다.

위의 데이터베이스 중 employees DB를 다운받아봅시다.
GitHub 링크를 누르면 아래와 같은 GitHub 사이트가 열립니다.

git clone 명령어를 이용하여 sql덤프파일이 포함된 디렉토리가 다운로드 됩니다.

git clone https://github.com/datacharmer/test_db.git

다운받은 디렉토리는 아래와 같이 다양한 덤프파일이 있습니다.

---

2. 샘플 데이터 추가

git 을 통해 가져온 데이터들 중, employees.sql 덤프파일을 이용하여 스키마를 가져올 것입니다.

employees.sql 내에는 table schema와 데이터가 들어있기 때문에, DB서버에 데이터를 부으면 됩니다.

덤프파일을 DB에 넣는 방법에는 2가지가 있습니다.

• source 를 이용
• redirection 을 이용

둘 중 편한 방법을 이용해서 데이터를 추가하면 됩니다.

2.1. source

source 방식은 MariaDB Client 를 이용하여 DB에 접속한 후 Client 콘솔에서 source명령어를 이용하여 덤프파일을 실행하는 방법입니다.

먼저, DB 서버에 접속한 후

sudo mysql

unix_socket 플러그인을 이용하여 root사용자로 로그인했습니다.

root 사용자가 mysql_native_password 플러그인으로 설정되어있다면 username과 password를 설정해서 실행시키면 됩니다. (sudo mysql -u root -p)

source 명령어를 실행합니다.

MariaDB [(none)]> source employees.sql

Query OK, 0 rows affected, 1 warning (0.002 sec)

Query OK, 1 row affected (0.000 sec)

Database changed
+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
1 row in set (0.000 sec)

...

Query OK, 7671 rows affected (0.139 sec)
Records: 7671  Duplicates: 0  Warnings: 0

+---------------------+
| data_load_time_diff |
+---------------------+
| 00:01:37            |
+---------------------+
1 row in set (0.050 sec)

2.2. redirection

redirection을 이용하여 employees.sql 덤프파일을 실행시킵니다.

sudo mysql < employees.sql

unix_socket 플러그인을 이용하여 root사용자로 로그인했습니다.

root 사용자가 mysql_native_password 플러그인으로 설정되어있다면 username과 password를 설정해서 실행시키면 됩니다. (sudo mysql -u root -p < employees.sql)

아래와 같은 로그가 터미널에 출력됩니다.

INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:01:35

---

3. 데이터베이스 및 테이블 구조 조회

3.1. 데이터베이스 목록 조회

show database 명령어로 데이터베이스 목록을 출력해봅니다.
employees 데이터베이스가 잘 조회되네요.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| book               |
| employees          |
| information_schema |
| jiniworld_demo     |
| jiniworld_test     |
| mysql              |
| performance_schema |
| test               |
| test_db            |
| tete               |
+--------------------+

이 명령어는 현재 로그인한 사용자(current_user())에게 접근 권한이 있는 database 목록을 조회합니다.

현재 로그인한 계정인 root@localhost는 모든 database에 대한 모든 권한이 부여된 계정이기 때문에 MariaDB에 생성된 모든 데이터베이스 목록이 출력된 것입니다.

※ 권한에 대한 자세한 설명은 추후 추가될 권한과 관련된 포스팅에서 더 상세히 다루도록 하겠습니다.

3.2. 데이터베이스 접근

use 데이터베이스명

MariaDB [(none)]> use employees;

use 명령어를 이용하여 employees database에 접근합니다.
특정 데이터베이스를 이용하게 되면, SELECT나 INSERT와 같은 SQL을 실행할 때 prefix로 데이터베이스명을 설정하는 것을 생략할 수 있습니다.

select * from employees.departments limit 5;

이렇게 앞에 employees.를 붙이던 것에서

select * from departments limit 5;

위와 같이 생략할 수 있습니다.

3.3. 테이블 목록 조회

show 테이블명;

MariaDB [employees]> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+

액세스한 database에 들어있는 table과 view 목록을 조회합니다.
단, 로그인한 사용자에게 조회 권한이 있는 table 및 view만 조회됩니다.

MariaDB [employees]> show full tables;
+----------------------+------------+
| Tables_in_employees  | Table_type |
+----------------------+------------+
| current_dept_emp     | VIEW       |
| departments          | BASE TABLE |
| dept_emp             | BASE TABLE |
| dept_emp_latest_date | VIEW       |
| dept_manager         | BASE TABLE |
| employees            | BASE TABLE |
| salaries             | BASE TABLE |
| titles               | BASE TABLE |
+----------------------+------------+

show tables명령어는 테이블 뿐만 아니라 view 도 함께 출력합니다.
만약 테이블만 출력하고 싶다면 WHERE 절을 이용하여 BASE TABLE만 출력해야 합니다.

MariaDB [employees]> show full tables where Table_type='BASE TABLE';
+---------------------+------------+
| Tables_in_employees | Table_type |
+---------------------+------------+
| departments         | BASE TABLE |
| dept_emp            | BASE TABLE |
| dept_manager        | BASE TABLE |
| employees           | BASE TABLE |
| salaries            | BASE TABLE |
| titles              | BASE TABLE |
+---------------------+------------+

3.4. 테이블 스키마 조회

테이블 구조(스키마)를 조회하는 방법은 두가지가 있습니다.

• desc 를 이용하여 표의 형태로 컬럼명과 자료형, 주요 제약조건 등을 표현하는 방법
• show 를 이용하여 테이블의 생성 DDL을 그대로 출력하는 방법

먼저 desc 를 이용하는 방법을 살펴봅시다.

desc 테이블명;

MariaDB [employees]> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

show create table 테이블명;

MariaDB [employees]> show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

구조를 한눈에 확인하고 싶다면 desc를 사용하면 편리할 것이고, create 실행문을 그대로 출력하고 싶다면 show를 사용하면 됩니다.

참고로 show create sql문은 database에도 이용할 수 있습니다.

MariaDB [employees]> show create database employees;
+-----------+-----------------------------------------------------------------------+
| Database  | Create Database                                                       |
+-----------+-----------------------------------------------------------------------+
| employees | CREATE DATABASE `employees` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+-----------+-----------------------------------------------------------------------+

show를 이용하여 employees 데이터베이스의 create DDL도 조회할 수 있습니다.