[Database] 비전공자도 쉽게 활용하는 MYSQL

2023. 1. 20. 08:32

728x90

들어가며

요즈음 개발자 뿐 아니라 비개발자인 분들도 SQL을 공부하고 계시는 분들이 많은데요. 사내에서 SQL 세미나를 진행했고 실제로 서비스팀 멤버분들은 QUERY를 날려 필요한 데이터를 직접 export하고 있습니다. 이번 포스팅을 통해 개발자 뿐 아니라 비개발자분들도 DBMS(DataBase Management System)를 이해하고 기본적인 QUERY문을 작성하여 사내의 데이터 베이스가 있다면 실제로 사용해보시면 좋을 것 같습니다. DBMS는 데이터 저장소로 여러 사용자들이 접근하여 데이터를 저장 및 관리할 수 있는 소프트웨어 프로그램을 의미합니다.

MySQL-Server와 MySQL-WorkBench를 설치하셨다는 가정하에 들어가도록 하겠습니다. 계정과 권한 부여 등을 수행하시면 다음과 같은 기본적인 쿼리를 따라하실 수 있습니다.

기본 쿼리

CRUD는 기본적인 데이터 처리 기능인 Create(생성), Read(읽기), Update(갱신), Delete(삭제)를 묶어서 일컫는 말입니다. 순서대로 기본 쿼리에 대해서 살펴보도록 하겠습니다.

데이터베이스 생성

# CREATE DATABASE 데이터베이스 이름;
CREATE DATABASE nuvilab;

Local에서 MySQL-Server를 실행하면 당연히 사용할 데이터베이스를 생성해주어야 합니다. 저는 nuvilab이라는 이름을 가진 데이터베이스를 생성하겠습니다. WorkBench에서 명령어를 실행하고자 한다면 Ctrl + Enter 단축키를 사용합니다.

참고로 #은 주석을 의미해요! 주석을 해둔 쿼리는 WorkBench가 실행하지 않기 때문에 보통 실행하고 싶지 않은 쿼리나 설명을 주석으로 써둡니다. 또한 세미콜론(;)은 쿼리의 끝을 의미하고 보통은 적어주어야 프로그램이 어디가 끝인지를 인식하고 해당 쿼리를 실행합니다.

Default Schema

# USE 데이터베이스 이름;
USE nuvilab;

데이터베이스를 생성했으면 해당 데이터베이스를 사용하기 위하여 Default Schema 로 설정해줍니다.

테이블 생성

# CREATE TABLE `테이블 이름` (
# `컬럼1` 자료형 PRIMARY KEY AUTO_INCREMENT, 
# `컬럼2` 자료형 
# );

CREATE TABLE `users` (
  `id` int PRIMARY KEY AUTO_INCREMENT, # 따옴표가(') 아니라 backtick(`)이에요!
  `full_name` varchar(255),
  `created_at` timestamp,
  `country_code` varchar(255),
);

CREATE TABLE `countries` (
  `code` int PRIMARY KEY AUTO_INCREMENT,
  `country_name` varchar(255),
  `continent_name` varchar(255)
);

nuvilab 데이터베이스 안에 2개의 테이블을 생성하는 쿼리입니다. 테이블에 사용될 컬럼과 그 자료형을 표시해줍니다. id 오른쪽에 쓰여있는 PRIMARY KEY는 아래에서 알아보도록 하겠습니다. ERD(Entity Relationship Diagram)는 테이블간의 관계를 설명해주는 다이어그램입니다. 예를 들어, 생성한 2개의 테이블을 ERD로 표현하면 아래와 같은 그림일 것입니다.

데이터 생성(Create)

데이터를 새로 생성하는 명령어입니다. 어떤 어플리케이션의 사용자가 새로 가입했을 경우에 데이터베이스에 데이터를 추가해주어야 합니다. users 테이블의 각 컬럼에 데이터를 삽입합니다.

# INSERT INTO 테이블 이름 (컬럼1, 컬럼2) values (값1, 값2);
INSERT INTO users (id, full_name, country_code) value (1, ‘홍길동’, 81);

데이터 읽기(Read)

위에서 추가한 데이터가 잘 들어갔는지 확인해봐야겠죠? 테이블의 데이터를 조회하는 명령어입니다.

# SELECT * FROM 테이블 이름;
SELECT * FROM users

데이터 변경(Update)

데이터베이스에 들어간 데이터가 잘못 입력됐거나, 변경사항이 있을 때 데이터를 업데이트하는 명령어입니다.

# UPDATE 테이블 이름 set 컬럼1=값1
UPDATE users set country_code=82 where full_name='홍길동'

UPDATE 문은 보통 조건문과 같이 사용되는데요. 위에서 where~ 이후의 조건문이 없다면 users 테이블에 있는 모든 country_code 컬럼의 값이 82로 변경이 됩니다. 그러면 사용자의 이름이 “홍길동”인 사람 뿐 아니라 다른 모든 사람의 country_code가 82로 변경이 되어 상상하기 싫은 순간이 오게 됩니다. 따라서 데이터를 변경하거나 삭제할 때는 해당 쿼리가 맞는지 dev 데이터베이스에서 테스트 해보는 등 재차 점검하고, 제약조건을 걸어두는 등의 조치를 취해야 합니다.

데이터 삭제(Delete)

사용자가 회원탈퇴를 하는 경우에 데이터베이스에서 데이터를 삭제하는 명령어입니다.

# DELETE FROM 테이블 이름
DELETE FROM users where full_name='홍길동'

마찬가지로 조건문 없이 DELETE 쿼리를 사용한다면 users 테이블의 모든 데이터가 삭제가 되버리는데요. 사용자의 이름이 “홍길동”인 것만 데이터를 제거한다는 조건문을 걸어두도록 합니다.

KEY

Primary Key

정의 : Primary Key는 테이블의 기본키를 의미합니다. null을 허용하지 않고 unique 한 옵션을 포함하며 테이블에서 한개의 필드에만 설정할 수 있습니다.
예를 들어, 위 users 테이블에서 ‘full_name’, ‘country_code’가 중복될 때 각 데이터를 구별해주는 것이 바로 Primary Key인 ‘id’입니다.

Foreign Key

정의 : 두 개 이상의 테이블을 연결해주는 값입니다.
필요한 이유 : 한 테이블에 너무 많은 정보가 들어가게 되면 가독성이 떨어지고, 필요없는 정보가 많이 삽입될 수 있습니다. 이를 방지하기 위해 사용합니다.
예를 들면 users 테이블과 countries 테이블은 각각 ‘country_code’와 ‘code’로 연결이 되어있는데요. 만약에 users 테이블에 countries 테이블 컬럼인 ‘name’, ‘continent’까지 들어가게 되면 users 테이블에 2개의 컬럼이 늘어나 데이터가 쌓일 때마다 저장 공간을 더 빠르게 잡아먹을 수 있습니다. 하지만 countries 테이블을 두어 FK를 사용한다면 users 테이블에 데이터가 쌓인다 하더라도 저장 공간을 덜 차지하게 됩니다.

Constraint

부모 테이블이 PK이거나 UNIQUE 때만 사용할 수 있습니다.

Restrict

일반적으로 FK를 설정하면 Restrict 제약이 걸리게 됩니다. users의 ‘country_code’ 컬럼에 FK를 설정하면 부모 테이블인 countries 테이블의 PK인 ‘code’ 컬럼을 참조하게 되므로 INSERT, UPDATE, DELETE 할 때 제약이 걸리게 됩니다.
users 테이블에 insert를 하게 될 경우 ‘coutry_code’는 null이 될 수 없으며 countries 테이블의 ‘code’ 컬럼을 참조하여 데이터 값이 있는 것만 집어넣을 수 있습니다. 예를 들면 ‘code’ 컬럼에 81,82,83이라는 값만 있다면 users 테이블에 ‘country_code’에도 81, 82, 83만 들어갈 수 있는 식입니다.
FK 제약이 걸린 경우에 별도의 옵션이 없다면 users 테이블에서는 ‘country_code’ 컬럼을 UPDATE, DELETE를 할 수 없습니다. 반면, countries 테이블에서는 users 테이블에서 참조하지 않는 값이 있다면 그 값에 대해서는 UPDATE, DELETE가 가능합니다. 예를 들면, countries 테이블에서 ‘code’ 컬럼이 5,6,7 인 row는 users 테이블에서 참조하고 있지 않을 때, UPDATE, DELETE가 가능합니다.

Cascade

UPDATE와 DELETE를 할 때 각각 Cascade를 설정할 수 있습니다.
users 테이블에 FK Cascade 제약을 UPDATE, DELETE 할 때 모두 설정했다고 가정해봅시다. 부모 테이블인 countries 테이블에서 ‘code’가 81인 데이터를 82로 update를 수행하게 되면 users 테이블의 ‘country_code’도 81에서 82로 부모 테이블과 똑같이 변경됩니다. countries 테이블에서 ‘code’가 82인 데이터에 대해 DELETE를 수행한다면 users 테이블에서 ‘country_code’가 82인 데이터 또한 마찬가지로 삭제가 되는것이죠.

프로시저

장점

여러 쿼리를 한번에 실행할 수 있는 명령어입니다.
미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어듭니다.
파이썬 등의 호스트 언어에서 사용할 경우 쿼리를 여러번 호출하기보다 프로시저 한번만 호출할 수 있어 코드 관리에 유용합니다.

단점

업무 변경으로 인해 저장된 프로시저를 변경해야할 때 변경 실수로 서비스 장애가 있을 수 있습니다.
디버깅이 어려워 에러가 발생했을 때 대처하기가 까다롭습니다.

프로시저 옵션

본인이 생성한 프로시저를 다른 유저가(해당 유저의 권한으로) 실행할 수 있도록 하는 옵션

SQL SECURITY INVOKER

예시 : 회원가입을 했을 경우 full_name, country_code를 받아 users 테이블에 데이터를 입력하는 프로시저

CREATE DEFINER=`jinyoung.kim`@`%` PROCEDURE `signup`(f_name VARCHAR,                                                     c_code INT)       
    SQL SECURITY INVOKER  
BEGIN  
INSERT INTO users (full_name, country_code) value (f_name, c_code); select * from users where full_name=f_name and country_code=c_code;  
END

실무에 사용되는 유용한 꿀팁

누비랩에서 실제로 많이 사용하는 쿼리를 정리해보았는데요. 알고 계시면 두고두고 쓸 수 있을 것이라 생각합니다.

Foreign Key를 활용하여 2개 이상 테이블 Join하기

SELECT * FROM users as u
INNER JOIN coutry as c
on c.code = u.country_code

‘k’로 시작하는 나라 검색

SELECT * FROM countries where name like 'k%'

중복 데이터가 있는지 확인

SELECT 
    full_name, 
    country_code,
    COUNT(id) c
FROM
    users
GROUP BY 
    full_name,
    country_code;
having
c>1;

중복 데이터 없애는 쿼리

create table users_temp
LIKE users;

insert into users_temp
select * from users
group by 
full_name, country_code;

drop table users;

alter table users_temp
rename to users;

참고)

https://www.mysqltutorial.org/mysql-delete-duplicate-rows/

https://ko.wikipedia.org/wiki/저장_프로시저

728x90

저작자표시

'DevOps > Database' 카테고리의 다른 글

[Database] 계층적인 데이터 구조 관계형 DB에서 관리하는 방법 (0)	2023.01.05

디너코딩