[MariaDB] 샘플데이터 다운로드

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

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

1. employees 샘플데이터 다운로드

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. 샘플 데이터 추가

mysql --user=jini < employee.sql

mysql --user=jini를 이용하여 jini@localhost로 로그인하여 redirection을 이용하여 employees.sql 덤프파일을 실행시킵니다.

database 생성과 데이터 추가를 완료했다면 로그인하여 데이터가 정상적으로 추가되었는지 확인해봅시다.

mysql --user=jini;

unix_socket 을 이용하여 jini 계정으로 로그인 하고

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| jiniworld          |
| jiniworld_log      |
| jiniworld_test     |
| mysql              |
| performance_schema |
| test               |
+--------------------+

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

---

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

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

show databases;

조금 전에 employees 데이터베이스가 생성되었는지 확인해보기위해 위의 명령어를 실했습니다.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| jiniworld          |
| jiniworld_log      |
| jiniworld_test     |
| mysql              |
| performance_schema |
| test               |
+--------------------+

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

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

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

3.2. 데이터베이스 접근

use 데이터베이스명

mysql> 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도 조회할 수 있습니다.