[SQL][DB] 데이터 준비 및 콘솔창에서 DB 간단히 다뤄보기

실습을 위한 샘플 데이터 준비하기

SQL을 통해 데이터를 다루는 연습을 위해선 우선 샘플 데이터가 필요할 것이다. 하지만 이 데이터를 어디서 어떻게 얻어야 할 것인지 고민이 될 것이다. 직접 데이터를 만들자니 수고롭다.

마침 여기 가짜 데이터들을 생성해주는 좋은 사이트가 있다. mockaroo라는 사이트이다.

Mockaroo - Random Data Generator and API Mocking Tool | JSON / CSV / SQL / Excel

해당 사이트에 들어가보면 처음에 다음과 같이 생겼다.

사진1-1. mockaroo 사이트 첫 화면

위 사진에서처럼, 각 필드명을 지어주고, 각 필드에 들어갈 데이터 종류까지 지정해줄 수 있다. 필드를 새로 추가할 수도, 삭제할 수도 있다.

해당 사이트는 다음의 특징을 가진다.

• 최대 1000개의 행까지는 무료이다. 그 이상은 유료이다.
• CSV, JSON, SQL 등의 여러 확장자 파일을 제공하여 원하는 확장자로 파일을 다운로드 받을 수 있다.
• 데이터는 영어로만 생성할 수 있다고 한다.
• 위 사진의 맨 아래 “PREVIEW” 버튼을 통해 가짜 데이터셋을 저장하기 전에 어떠한 모습인지 미리 파악할 수 있다.

여기서 위 사이트를 이용하여 간단하게 “사이트 유저” 데이터를 생성해보겠다. 먼저 다음의 사진과 같이 각 필드명과 데이터 타입을 정하였다.

사진 1-2. “사이트 유저 목록”이라는 가상 데이터셋을 생성하기 위해 필드들을 위와 같이 설정하였다.

• member_id : 사이트 회원의 고유 ID. 여기선 행 번호로 설정.
• sign_up_date : 사이트 최초 가입일. 날짜 형식.
• class_number : 클래스 번호. 전자상거래 사이트를 보면 VIP와 같은 여러 계급이 있는 걸 모방한 것임.
• mileage : 현재 보유 마일리지
• username: 유저 이름
• aver_purchase : 한 번 온라인 구매 시 평균 구매액.

참고로, 특정 필드에는 데이터 자체가 삽입되지 않을 수도 있다. 이러한 상황을 가정하고자 한다면 필드의 맨 오른쪽에 “blank” 속성에 NULL 값을 생성할 확률을 지정해주면 된다. 예를 들어 “blank : 50%”로 지정하면, 전체 레코드들 중 해당 필드값이 NULL일 확률이 50%가 된다는 뜻이다. 이렇게 특정 데이터에 NULL값을 줄 수도 있다.

위 사진의 맨 아래의 “# Rows” 칸에 최대 레코드 수를 지정할 수 있고, 어떤 확장자로 저장할 지 “Format” 에서 선택할 수 있다. 여기선 SQL 파일로 지정하였다.

만약 이 데이터셋을 담기 위한 테이블을 DB 내에 생성하지 않았다면 “include CREATE TABLE”에 체크하는 것이 좋다. 해당 데이터들을 담을 테이블을 생성해주는 쿼리를 자동 삽입해주기 때문이다.

“Table name”에서 테이블명을 직접 지정해줄 수 있다. 해당 데이터셋을 다운로드받을 때 파일명이 되기도 한다. 위 사진의 경우, 테이블명을 “site_users”라 하였는데, 다운로드 시 “site_users.sql” 파일명으로 다운받는다.

사이트 맨 아래의 “PREVIEW” 버튼을 누르면 랜덤 데이터를 생성해주는 쿼리문을 보여준다.

사진 1-3. 사진 2에서 설정한 필드대로 한 다음 “PREVIEW” 버튼을 누른 뒤의 화면. 자세히 보면 필드 속성에 맞는 데이터들이 랜덤으로 생성된 것을 확인할 수 있다.

위 사진에서 “GENERATE DATA”를 누르면 .sql 확장자를 가진 파일을 다운로드 받을 수 있다.

당분간은 이 데이터를 가지고 sql 연습을 해볼 예정이다. 만약 여기서 다루는 데이터셋을 똑같이 사용하고자 한다면 다음의 파일을 다운로드하여 사용하면 되겠다.

[다운로드 받기] site_users.sql

이제 이 파일을 MariaDB에서 가져와 사용할 것이다. 그래서 우선 해당 파일을 적절한 곳에 위치시킨다. 필자의 경우, C:\sql_practice 경로에 해당 파일을 위치시켰다.

SQL 쿼리문을 작성하는 방법은 크게 두 가지이다. 하나는 위와 같이 .sql 확장자 파일을 생성하고 그 안에 쿼리문을 작성하는 방식이다. 위의 site_user.sql이 대표적 예이다. 쿼리문을 저장하고 나중에 재사용하고자 할 때 유용한 방법이다.
또 하나는, 직접 실시간으로 입력하는 방법이다. 아래에 소개할 콘솔창에서 한 줄의 쿼리문을 입력하는 것이 그 예이다. 바로바로 결과를 보고자 할 때 유용하지만, 입력한 쿼리문이 어딘가에 저장되지 않기에 재사용할 수 없다.

MariaDB에 새 데이터셋 등록하기

MariaDB에서 해당 파일을 읽어와 해당 테이블이 구축되도록 해야할 차례이다. 이를 위해 콘솔창(cmd)에서 진행할 것이다.

그 전에 미리 해야할 일이 있다. 앞서 다운로드 받은 site_users.sql에 몇 가지 쿼리문을 추가해야한다. 해당 파일을 vscode로 열면 여러 데이터들을 삽입하는 쿼리문을 볼 수 있다.

create table site_users (
	member_id INT,
	sign_up_date DATE,
	class_number INT,
	mileage INT,
	username VARCHAR(50),
	aver_purchase INT
);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (1, '2023-10-23', 1, 1549, 'pcopozio0', 616);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (2, '2024-01-09', 4, 2712, 'eszanto1', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (3, '2023-08-15', 5, 1129, 'jmatuszkiewicz2', 855);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (4, '2024-06-04', 2, 4238, 'egudgion3', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (5, '2023-12-06', 3, 4632, 'gfittes4', 592);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (6, '2023-09-23', 4, 2235, 'dbuttrum5', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (7, '2024-07-21', 5, 1891, 'lfree6', 547);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (8, '2023-07-03', 3, 1396, 'bleggis7', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (9, '2024-05-04', 2, 4058, 'kmardle8', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (10, '2023-05-06', 5, 4042, 'dsummerside9', 249);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (11, '2023-01-08', 2, 1282, 'fwillimonta', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (12, '2024-02-21', 1, 1546, 'atailourb', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (13, '2024-07-09', 2, 3492, 'dmulgrewc', 807);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (14, '2024-06-14', 4, 4908, 'ksimeolid', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (15, '2024-05-16', 1, 4640, 'lscaddinge', 656);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (16, '2023-10-10', 5, 3486, 'srenonf', 322);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (17, '2024-03-27', 4, 2954, 'aledesg', 850);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (18, '2023-01-06', 1, 2632, 'hshickleh', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (19, '2022-09-11', 5, 3712, 'lglandersi', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (20, '2024-03-13', 1, 2596, 'dionsj', 761);

site_users.sql

위 파일 내용의 문제점은, 새 데이터를 테이블에 삽입하는 쿼리문과, 데이터를 저장할 테이블 생성 쿼리문은 있어도, 해당 테이블을 어느 데이터베이스에 넣을지에 대한 언급이 없다. 이 상태에서 DBMS에 해당 파일을 등록하려고 하면 에러가 난다. 그래서 위 쿼리문에 데이터베이스 생성 및 해당 데이터베이스를 선택하는 쿼리문을 추가할 것이다. 그래야 해당 데이터베이스 내에 위와 같은 데이터들이 테이블로 삽입될 수 있기 때문이다.

위 파일의 맨 위에 다음의 쿼리문을 추가한다.

-- SQL문에서는 '--' 대시 두 문자를 통해 주석을 작성할 수 있다.

-- "sql_practice"라는 데이터베이스가 이미 존재한다면 
-- 해당 DB를 삭제한다. 그 후, 똑같은 이름의 새 데이터베이스를 생성한다. 
-- 이렇게 하는 이유는, 실습 도중 데이터가 의도치 않게 변형되어 
-- 초기화하여 처음부터 다시 사용하고자 하기 위함이다. 
-- "sql_practice"라는 이름의 데이터베이스 생성 시도.
DROP DATABASE IF EXISTS sql_practice;
CREATE DATABASE sql_practice;

-- 사용할 데이터베이스 선택. 
-- 사용할 데이터베이스를 선택해야 그 안에 테이블 및 
-- 새 데이터를 삽입할 수 있다. 
-- 여기서는 위에서 생성한 데이터베이스를 선택.
USE sql_practice;

예제 1-1

여기에, 해당 테이블에 기본키도 설정해보겠다. 기본 키에 대해선 나중에 살펴보겠다. 기본키는 회원 고유 ID인 member_id 필드에 지정하겠다. 다음과 같이 지정하면 된다.

create table site_users (
	member_id INT PRIMARY KEY,  -- member_id 필드에 기본 키 설정.
	sign_up_date DATE,
	class_number INT,
	mileage INT,
	username VARCHAR(50),
	aver_purchase INT
);

예제 1-2

위와 같이 변경한 후의 최종 전체 파일 내용은 다음과 같다.

-- SQL문에서는 '--' 대시 두 문자를 통해 주석을 작성할 수 있다.

-- "sql_practice"라는 데이터베이스가 이미 존재한다면 
-- 해당 DB를 삭제한다. 그 후, 똑같은 이름의 새 데이터베이스를 생성한다. 
-- 이렇게 하는 이유는, 실습 도중 데이터가 의도치 않게 변형되어 
-- 초기화하여 처음부터 다시 사용하고자 하기 위함이다. 
-- "sql_practice"라는 이름의 데이터베이스 생성 시도.
DROP DATABASE IF EXISTS sql_practice;
CREATE DATABASE sql_practice;

-- 사용할 데이터베이스 선택. 
-- 사용할 데이터베이스를 선택해야 그 안에 테이블 및 
-- 새 데이터를 삽입할 수 있다. 
-- 여기서는 위에서 생성한 데이터베이스를 선택.
USE sql_practice;

create table site_users (
	member_id INT PRIMARY KEY,  -- member_id 필드에 기본 키 설정.
	sign_up_date DATE,
	class_number INT,
	mileage INT,
	username VARCHAR(50),
	aver_purchase INT
);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (1, '2023-10-23', 1, 1549, 'pcopozio0', 616);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (2, '2024-01-09', 4, 2712, 'eszanto1', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (3, '2023-08-15', 5, 1129, 'jmatuszkiewicz2', 855);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (4, '2024-06-04', 2, 4238, 'egudgion3', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (5, '2023-12-06', 3, 4632, 'gfittes4', 592);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (6, '2023-09-23', 4, 2235, 'dbuttrum5', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (7, '2024-07-21', 5, 1891, 'lfree6', 547);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (8, '2023-07-03', 3, 1396, 'bleggis7', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (9, '2024-05-04', 2, 4058, 'kmardle8', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (10, '2023-05-06', 5, 4042, 'dsummerside9', 249);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (11, '2023-01-08', 2, 1282, 'fwillimonta', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (12, '2024-02-21', 1, 1546, 'atailourb', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (13, '2024-07-09', 2, 3492, 'dmulgrewc', 807);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (14, '2024-06-14', 4, 4908, 'ksimeolid', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (15, '2024-05-16', 1, 4640, 'lscaddinge', 656);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (16, '2023-10-10', 5, 3486, 'srenonf', 322);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (17, '2024-03-27', 4, 2954, 'aledesg', 850);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (18, '2023-01-06', 1, 2632, 'hshickleh', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (19, '2022-09-11', 5, 3712, 'lglandersi', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (20, '2024-03-13', 1, 2596, 'dionsj', 761);

site_users.sql (수정본)

이제 변경사항을 저장하기만 하면 된다.

콘솔창에서 .sql 확장자 파일을 MariaDB에 적용하는 방법은 두 가지 방법이 있다. 하나는 아직 MariaDB에 접속하지 않았을 때 사용하는 방법이고, 또 하나는 MariaDB에 이미 접속했을 때 사용하는 방법이다.

• DB에 접속하지 않은 경우, 콘솔창에서 다음의 명령어 입력.

mysql -uid명 -p > 파일명.sql

여기서 < 기호는 그 뒤에 올 경로로 리다이렉션(redirection)하겠다는 뜻이다. 해당 기호를 window powershell에서 사용하면 에러가 날 수 있으므로 cmd 창에서 하는 걸 추천한다.

• 이미 MariaDB에 접속한 경우.

source 경로(C:\...\파일명.sql);

여기서는 아직 DB에 접속하지 않은 상황에서 해당 파일을 MariaDB에 등록시켜보겠다.

1. 우선 sql 파일이 저장된 경로로 이동한다. cd 명령어와 그 뒤에 해당 경로를 다음과 같이 입력하면 된다.

   

   사진 2-1

2. 해당 경로에서, 앞서 언급한 명령어를 입력하여 site_users.sql 파일을 MariaDB에서 실행시키도록 한다.

   

   사진 2-2

   위와 같이 엔터키 입력 후 아무런 메시지도 뜨지 않는다면 해당 sql 파일 실행에 성공한 것이다.

이제 해당 파일이 성공적으로 실행되어 새 DB와 테이블, 그리고 새 데이터들도 등록되었는지 확인할 차례이다.

DB 정보 확인하기

이제 콘솔창에서 MariaDB를 실행시켜보자.

사진 3-1

모든 데이터베이스들을 보여주는 쿼리문은 다음과 같다.

show databases;

예제 2-1

사진 3-2

앞서 site_users.sql 파일에 새 쿼리문을 삽입할 때 새 DB명으로 지정한 “sql_practice”가 존재하는 것을 볼 수 있다.

“information_schema”, “mysql”, “performance_schema”, “sys” 이 데이터베이스들은 MariaDB를 설치할 때 같이 설치된 DB들로, DB관련 설정들이 들어있기에 되도록 건드리지 않는 것이 좋다. ”testmaria”는 필자가 테스트하기 위해 만든 DB이다.

특정 데이터베이스에 접속하고자 한다. 그래야 해당 데이터베이스 내에 어떤 테이블과 데이터들이 있는지 확인할 수 있다. 그러면 다음의 쿼리문을 이용한다.

use db명;

예제 2-2

사진 3-3

여기서는 “sql_practice”를 선택하였다. 그러자 원래는 [(none)] 이었던 곳이 해당 DB명으로 바뀌었다. 이제는 모든 작업이 해당 DB 내에서 이뤄진다는 뜻이다.

특정 DB는 선택하였는데, 해당 DB내에 존재하는 모든 테이블을 보고자 한다. 그러면 다음의 쿼리문을 사용한다.

show tables;

예제 2-3

사진 3-4

지금은 “site_users”라는 테이블만 생성했기에 테이블이 1개만 존재하는 상황이다.

해당 테이블에 대한 정보를 알고 싶다. 그러면 다음의 쿼리문을 이용한다.

desc 테이블명;

예제 2-4

사진 3-5

해당 테이블의 필드명, 데이터 타입, Null 키 허용 여부(데이터를 입력하지 않아도 되는지 여부, No면 반드시 입력, Yes면 입력하지 않아도 됨. 입력하지 않을 경우, 해당 데이터는 Null로 인식됨), 키 등의 여러 정보들을 볼 수 있다.

지금까지 설명한 데이터베이스 및 테이블 정보 조회 관련 쿼리문들을 정리하면 다음과 같다.

show databases; -- 모든 DB 보기

-- 특정 DB 선택. 아래에 나오는 쿼리문들은 모두 이 쿼리문이 선행되어야 함.
use db명;
  
show tables;  -- 현재 DB내 모든 테이블명 확인.
desc 테이블명;  -- 특정 테이블의 정보 확인.

예제 2-5

---

References

[1] 에이콘아카데미(강남) 강의

[2] 우재남, “혼자 공부하는 SQL”, (한빛미디어, 2021)

This content is licensed under CC BY-NC 4.0