뉴비를 위한 SQL
프로그래밍 입문을 할 때 거치는 관문 중 하나인 "SQL"에 대하여 얕게 소개를 하려고 한다.
해당 포스트는 심도 깊게 정리를 하기보다,
이제 막 SQL을 배우는 "뉴비"를 위해 작성한 점을 참고하도록..
참고로 작성자는 MariaDB(10.5.8)을 사용하고 있다.
SQL?
Structured Query Language
구조화 질의어 : RDBMS의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어.
좀 더 친근하게 말하면, Oracle, MySQL, MariaDB와 같은 "DB"에서 원하는 데이터를 출력할 때, 작성하는 "쿼리" 이다.
SQL 활용 예시 #1
스프링 프레임워크 기반 게시판을 개발할 때 보통 html, css, js를 사용하여 화면의 각 컴포넌트들을 구현,
게시글 작성 화면에서 제목, 본문을 입력 후 저장 버튼을 클릭하면 입력한 게시물의 내용을 자바로 작성한 서버로 전송(보통 json 타입을 사용..),
클라이언트(화면)에서 전달 받은 json 데이터를 Map 혹은 board DTO 타입으로으로 변환 후,
서버에서 구현한 게시물 작성 관련 로직을 수행 후, 게시글을 영구적으로 저장하기 위해 미리 연동한 Database에 저장을 한다.
이 때, 서버에서 게시물 관련 데이터를 Database가 알아 먹을 수 있는 형태로 만들어서 전달해줘야한다.
여기서 알아 먹을 수 있는 형태가 "SQL"이다.
SQL 활용 예시 #2
이 부장 "김 사원~ 이번 달에 A서비스에 가입한 회원 목록 좀 DB에서 뽑아줘~"
오 차장 "김 사원~ 올해 입사 3년차 되는 직원들 직급을 대리로 그룹웨어 DB쪽에 업데이트 좀 쳐줘~"
김 사원 "네 알겠습니다!"
....
김 사원 '왜 나만 시키냐 ㅠ_ㅠ'
위와 같이 SQL은 개발자 뿐만 아니라, 비 개발자도 업무에서 사용할 수 있다.
요즘 누가 개발할 때 쿼리를 써요? Hibernate 쓰면 되는데요?
이렇게 생각하면... 뭐... 할 말이 없다...
요즘 많이 사용하는 ORM 프레임워크인 Hibernate(JPA)도 개발자가 Hibernate에서 제공하는 API를 코드를 통해 호출하는 것 뿐이지,
Hibernate 내부에서는 개발자가 호출한 API의 파라미터를 기반으로 SQL을 만들어서 연동한 DB에 날린다.
혹시라도 오해를 할까봐 말하는데, 개발자가 직접 쿼리를 작성해서 xml 파일로 관리하거나 (Mybatis),
쿼리가 아닌 프레임워크(Hibernate)에서 제공하는 API를 호출하는 차이일 뿐,
Hibernate를 사용한다고 해서 SQL에 대한 학습 자체를 건너뛰어도 된다? 그건 아니다.
본인의 프로젝트에서 사용하는 프레임워크에 대해 이해 없이 사용하는 경우가 많다. 한 번 쯤은 겉 핥기 수준이라도 찾아보고 쓰는 습관을 들이자 -_-;;
쓸데 없는 말이 너무 길어졌다..! 이제 SQL을 공부하러 가자...
SQL의 종류 및 문법 (MySQL & MariaDB 기준)
DDL : Data Definition Language
"데이터 정의어"
데이터 베이스의 테이블과 같은 데이터 구조 등을 정의 하는 언어를 말한다.
데이터베이스 ,테이블의 추가, 삭제 혹은 컬럼을 추가, 컬럼의 속성을 수정 등..
종류 | 역할 |
CREATE | 데이터베이스, 테이블을 생성할 때 사용 |
ALTER | 테이블의 속성을 수정할 때 사용 |
DROP | 데이터베이스, 테이블을 삭제할 때 사용 |
TRUNCATE | 테이블의 초기화 할 때 사용 (row삭제, index 삭제 등...) |
CREATE
별도 설정 없이 데이터 베이스를 생성하는 경우
CREATE DATABASE 데이터베이스이름;
SQL
복사
데이터 베이스를 생성 시, 문자셋 설정을 하는 경우
CREATE DATABASE 데이터베이스이름 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SQL
복사
테이블 생성 시, 아~무 설정도 안하는 경우
CREATE TABLE 테이블이름 (
컬럼1 INT(5),
컬럼2 varchar(255),
컬럼3 bigint(20)
)
SQL
복사
테이블 생성 시, 기본키, 자동 증감 여부 및 NULL 허용 여부 옵션을 설정하는 경우
CREATE TABLE 테이블이름 (
컬럼1 INT(5) NOT NULL AUTO_INCREMENT,
컬럼2 varchar(255)NOT NULL,
컬럼3 bigint(20) NULL,
PRIMARY KEY (`컬럼1`)
)
SQL
복사
테이블 생성 시, 기본키, 자동 증감 여부, NULL 허용 여부, 테이블 엔진 옵션 및 기본 문자 셋을 설정하는 경우
CREATE TABLE 테이블이름 (
컬럼1 INT(5) NOT NULL AUTO_INCREMENT,
컬럼2 varchar(255)NOT NULL,
컬럼3 bigint(20) NULL,
PRIMARY KEY (`컬럼1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
SQL
복사
테이블 생성 시, 기본키, 자동 증감 여부, 자동 증감 값 지정, NULL 허용 여부, 음수 미 허용, 기본 값, 테이블 엔진 옵션 및 기본 문자 셋을 설정하는 경우
CREATE TABLE 테이블이름 (
컬럼1 INT(5) NOT NULL AUTO_INCREMENT,
컬럼2 varchar(255)NOT NULL,
컬럼3 bigint(20) unsigned DEFAULT 0,
PRIMARY KEY (`컬럼1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
SQL
복사
ALTER
테이블에 컬럼을 추가하는 경우
ALTER TABLE 테이블이름 ADD COLUMN 컬럼1 컬럼속성1;
SQL
복사
테이블의 컬럼 속성을 변경하는 경우
ALTER TABLE 테이블이름 MODIFY COLUMN 컬럼이름1 컬럼속성1 컬럼속성2;
SQL
복사
테이블의 컬럼을 삭제하는 경우
ALTER TABLE 테이블이름 DROP COLUMN 컬럼2;
SQL
복사
테이블의 이름을 변경하는 경우
ALTER TABLE 원래테이블이름 RENAME 바꿀테이블이름;
SQL
복사
DROP
데이터베이스를 DROP 하는 경우
DROP DATABASE 데이터베이스이름;
SQL
복사
테이블을 DROP 하는 경우
DROP TABLE 테이블이름;
SQL
복사
TRUNCATE
TRUNCATE TABLE 테이블이름;
SQL
복사
DML : Data Manipulation Language
"데이터 조작어"
데이터베이스 혹은 테이블의 row를 조회, 삽입, 수정, 삭제 등 데이터를 조작하는 언어를 말한다.
SELECT는 데이터베이스의 정보(환경변수, 버전 등...), 테이블의 데이터를 조회(검색)을 하는 질의어이며,
나머지 INSERT, UPDATE, DELETE는 테이블에 있는 row(record, tuple)를 변형 (삽입, 수정, 삭제)를 하는 질의어이다.
주로 웹 어플리케이션에서 CRUD 기능을 구현할 때 사용한다.
종류 | 역할 |
SELECT | 데이터베이스, 테이블의 정보를 조회할 때 사용 |
INSERT | 테이블에 row를 삽입 할 때 사용 |
UPDATE | 테이블의 row를 수정 할 때 사용 |
DELETE | 테이블의 row를 삭제할 때 사용 |
SELECT
조건이 없는 경우 : 모든 컬럼의 조회 할 시, 컬럼 명 대신 *(asterisk)을 입력
SELECT * FROM 테이블;
SELECT 컬럼1 FROM 테이블;
SELECT 컬럼1, 컬럼2, FROM 테이블;
SQL
복사
조건이 하나인 경우
SELECT 컬럼1, 컬럼2, FROM 테이블 WHERE 컬럼1 = '조건1';
SQL
복사
조건이 여러개인 경우
SELECT 컬럼1, 컬럼2, FROM 테이블 WHERE 컬럼1 = '조건1' AND 컬럼2 = '조건2';
SELECT 컬럼1, 컬럼2, FROM 테이블 WHERE 컬럼1 = '조건1' AND 컬럼2 = '조건2' AND 컬럼3 = '조건3';
SQL
복사
INSERT
하나의 row를 삽입하는 경우
INSERT INTO 테이블 (컬럼1, 컬럼2) VALUES (데이터1, 데이터2);
SQL
복사
다수의 row를 삽입하는 경우
INSERT INTO 테이블 (컬럼1, 컬럼2)
VALUES
(데이터1, 데이터2),
(데이터1, 데이터2),
(데이터1, 데이터2),
(데이터1, 데이터2);
SQL
복사
UPDATE
조건 없이 테이블의 모든 row의 컬럼을 변경하는 경우
UPDATE 테이블 SET 컬럼3='수정3', 컬럼4='수정4';
SQL
복사
조건에 부합하는 row의 컬럼을 변경하는 경우
UPDATE 테이블 SET 컬럼3='수정3', 컬럼4='수정4' WHERE 컬럼1 = '조건1';
UPDATE 테이블 SET 컬럼3='수정3', 컬럼4='수정4' WHERE 컬럼1 = '조건1' AND 컬럼2 = '조건2';
UPDATE 테이블 SET 컬럼3='수정3', 컬럼4='수정4' WHERE 컬럼1 = '조건1' AND 컬럼2 = '조건2' AND 컬럼3 = '조건3';
SQL
복사
DELETE
DELETE FROM 테이블 WHERE 컬럼1 = '조건1' AND 컬럼2 = '조건2';
SQL
복사
DCL : Data Control Language
"데이터 제어어"
데이터베이스에 대한 접근 및 객체(테이블 등) 사용 권한을 부여 및 회수를 하는 언어를 말한다.
종류 | 역할 |
GRANT | 데이터베이스의 권한을 사용자에게 부여 |
REVOKE | 데이터베이스의 권한을 사용자에게 회수 |
권한의 종류는 꽤 많다... 자주 쓰는 권한 몇 가지 정도만 작성했다.
ALL(전체), ALTER, CREATE, INSERT, UPDATE, DELETE, DROP, INDEX...
GRANT
mysql.user의 계정 중, Username이 'username'이면서 Host가 'myhost'인 사용자에게 권한 부여
GRANT 권한 ON DATABASENAME.TABLENAME TO 'username@myhost';
SQL
복사
mysql.user의 계정 중, Username이 'username'이면서 Host가 'myhost'이면서 비밀번호가 'password'인 사용자에게 권한 부여
GRANT 권한 ON DATABASENAME.TABLENAME TO 'username@myhost' IDENTIFIED BY 'password';
SQL
복사
REVOKE
mysql.user의 계정 중, Username이 'username'이면서 Host가 'myhost'인 사용자에게서 권한 회수
REVOKE 권한 ON DATABASENAME.TABLENAME TO 'username@host';
SQL
복사
권한을 부여/회수하였을 때 아래 쿼리를 통해 반영해야함.
FLUSH PRIVILEGES;
SQL
복사
TCL : Transaction Control Language
"트랜잭션 제어어"
DML에 의해 조작된 결과를 작업 단위(트랜잭션) 별로 제어하는 언어를 말한다.
종류 | 역할 |
COMMIT | 트랜잭션 처리가 정상적으로 종료 되었을 때, 변경 내용을 데이터베이스에 반영하는 연산 |
SAVEPOINT | 현재 트랜잭션 상태를 저장한다. 해당 시점으로 트랜잭션으로 롤백 할 수 있다. |
ROLLBACK | SAVEPOINT 혹은 COMMIT하기 전 트랜잭션으로 롤백 |
참고로 MySQL & MariaDB는 기본으로 자동 커밋 모드이다. 롤백 기능을 사용하고 싶으면 자동 커밋 모드를 비활성화 해야한다.
자동 커밋 모드 확인 : ON 활성, OFF 비활성
SHOW VARIABLES LIKE 'autocommit%';
SQL
복사
자동 커밋 활성
SET AUTOCOMMIT = TRUE;
SQL
복사
자동 커밋 비활성
SET AUTOCOMMIT = FALSE;
SQL
복사
COMMIT
COMMIT;
SQL
복사
SAVEPOINT (InnoDB 지원)
SAVEPOINT A (포인터명);
SQL
복사
ROLLBACK
COMMIT하기 전 트랜잭션으로 ROLLBACK
ROLLBACK;
SQL
복사
SAVEPOINT A 트랜잭션으로 ROLLBACK
ROLLBACK TO SAVEPOINT A (포인터명);
SQL
복사
.......
.......
.......
갑자기 여러 문법을 많이 보게 되어서 정신이 없을 것이다.
약간이나마, SQL을 통해 데이터를 조회 및 조작을 어떻게 할 지 감이 잡혔을 것이다.
하지만 슬프게도, 기본 중의 기본 문법만 언급한 것이다...
RDBMS : Relational Data Base Management System
"관계형 데이터 베이스 관리 시스템"
우리가 주로 접하는, 속칭 DB라고 부르는 시스템의 정식 명칭이다.
MySQL, MariaDB, Oracle, PostgreSQL, SQL Server(MS-SQL)이 여기에 속한다.
사전적인 의미 말고, 예시를 들어 좀 더 쉽게 설명을 하겠다.
예를 들어, 우리가 객체 지향 관점으로 게시판을 개발 할 때, 전체 적인 게시판을 구성하는 객체로는 다음과 같을 것이다.
게시물
> 게시물 ID (고유 값)
> 제목
> 본문
> 작성자
> 작성일
Plain Text
복사
게시물 추천
> 게시물 추천 ID (고유 값)
> 게시물 ID
> 추천한 사용자 ID
Plain Text
복사
댓글
> 댓글 ID(고유 값)
> 게시물 ID
> 댓글 내용
> 댓글 작성자 ID
> 댓글 비밀번호
> 댓글 작성일자
Plain Text
복사
댓글 추천
> 댓글 추천 ID (고유 값)
> 댓글 ID
> 추천한 사용자 ID
Plain Text
복사
사용자
> 사용자 ID (고유 값)
> 비밀번호
> 이름
> 이메일 주소
> 닉네임
Plain Text
복사
Board, Comment, User 등... 각 객체별로 사용자 ID 혹은 게시물의 ID 값으로 얽혀 있고, 연관(혹은 관계)을 지을 수 있다.
마찬가지로 위에서 배웠던 DDL문의 "CREATE TABLE ~" 구문을 통하여 각 객체들을 테이블로 정의 할 수 있다.
각 테이블(스키마)의 관계는 서로를 참조하는 외래키를 통해 보다 관계 지향적으로 정의 할 수 있다.
여기서 나오는 개념이 기본키, 외래키, 제약 조건들이 있다. 이 부분은 이번 포스팅에서 설명 하지 않고 다음 포스팅에 정리를 하겠다.
SQL 공부를 위한 샘플 데이터 파일 사용하기
Oracle XE 에디션을 사용하는 경우, 학습용도로 사용할 수 있는 샘플 데이터베이스를 제공한다. (HR 계정)
아쉽게도 MariaDB를 설치할 경우 샘플 데이터베이스를 제공하지 않는다.
본인이 직접 더미데이터를 만들어도 되지만, 다행히도 깃허브에 샘플 데이터 베이스가 공개 되어있다.
깃허브에 접속하여 샘플 데이터베이스를 내려 받은 후, 본인 로컬 DB에 Import 해서 사용하는 방법을 소개하겠다.
본인이 편한 방법으로 (git clone이나 zip 파일 다운로드) 샘플 파일을 내려 받자.
압축을 풀면 아래와 같다
pwd
/Users/pjw/Downloads/test_db-master
ls -trl
total 336680
-rwxr-xr-x@ 1 pjw staff 2.0K 9 7 13:24 test_versions.sh
-rw-rw-r--@ 1 pjw staff 4.6K 9 7 13:24 test_employees_sha.sql
-rw-rw-r--@ 1 pjw staff 4.6K 9 7 13:24 test_employees_md5.sql
-rwxr-xr-x@ 1 pjw staff 1.8K 9 7 13:24 sql_test.sh
-rw-rw-r--@ 1 pjw staff 272B 9 7 13:24 show_elapsed.sql
drwxrwxr-x@ 5 pjw staff 160B 9 7 13:24 sakila
-rw-rw-r--@ 1 pjw staff 4.5K 9 7 13:24 objects.sql
-rw-rw-r--@ 1 pjw staff 21M 9 7 13:24 load_titles.dump
-rw-rw-r--@ 1 pjw staff 37M 9 7 13:24 load_salaries3.dump
-rw-rw-r--@ 1 pjw staff 38M 9 7 13:24 load_salaries2.dump
-rw-rw-r--@ 1 pjw staff 38M 9 7 13:24 load_salaries1.dump
-rw-rw-r--@ 1 pjw staff 17M 9 7 13:24 load_employees.dump
-rw-rw-r--@ 1 pjw staff 1.1K 9 7 13:24 load_dept_manager.dump
-rw-rw-r--@ 1 pjw staff 14M 9 7 13:24 load_dept_emp.dump
-rw-rw-r--@ 1 pjw staff 250B 9 7 13:24 load_departments.dump
drwxrwxr-x@ 5 pjw staff 160B 9 7 13:24 images
-rw-rw-r--@ 1 pjw staff 7.8K 9 7 13:24 employees_partitioned_5.1.sql
-rw-rw-r--@ 1 pjw staff 6.1K 9 7 13:24 employees_partitioned.sql
-rw-rw-r--@ 1 pjw staff 4.1K 9 7 13:24 employees.sql
-rw-rw-r--@ 1 pjw staff 4.2K 9 7 13:24 README.md
-rw-rw-r--@ 1 pjw staff 964B 9 7 13:24 Changelog
Bash
복사
sql 파일을 import 하기 앞서, 본인 로컬 DB에 접속 후 Database를 하나 생성하자.
create database employees default character set utf8mb4 collate utf8mb4_unicode_ci;
SQL
복사
sql 파일을 employees 데이터 베이스에 import
pwd
/Users/pjw/Downloads/test_db-master
mysql -h127.0.0.1 -u데이터베이스계정 -p비밀번호 employees < employees.sql
Bash
복사
작성자는 로컬 DB를 도커 엔진에 MariaDB 컨테이너를 올려서 사용해서 아래와 같이 작업 했다.
docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
1c19b0ef3306 mariadb "docker-entrypoint.s…" 2 months ago Up 6 seconds 0.0.0.0:3306->3306/tcp mariadb
docker exec -i 1c19b0ef3306 mysql -h127.0.0.1 -u데이터베이스계정 -p비밀번호 employees < employees.sql
ERROR at line 113: Failed to open file 'load_departments.dump', error: 2
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
Bash
복사
위와 같이 오류가 나면서 테이블 스키마만 생성 되고, 실제 row들은 들어가지 않았다.
vi employees.sql
.....
.....
SELECT 'LOADING departments' as 'INFO';
source load_departments.dump ;
SELECT 'LOADING employees' as '
Bash
복사
source 명령어가 문제가 있나보다.. 도커에 해당 sql을 import 하였을 때 외부 dump 파일을 못 읽어들이는 것 같아 보였다.
docker exec -i 1c19b0ef3306 mysql -h127.0.0.1 -u데이터베이스계정 -p비밀번호 employees < load_employees.dump
docker exec -i 1c19b0ef3306 mysql -h127.0.0.1 -u데이터베이스계정 -p비밀번호 employees < load_dept_emp.dump
docker exec -i 1c19b0ef3306 mysql -h127.0.0.1 -u데이터베이스계정 -p비밀번호 employees < load_dept_manager.dump
docker exec -i 1c19b0ef3306 mysql -h127.0.0.1 -u데이터베이스계정 -p비밀번호 employees < load_titles.dump
docker exec -i 1c19b0ef3306 mysql -h127.0.0.1 -u데이터베이스계정 -p비밀번호 employees < load_salaries1.dump
docker exec -i 1c19b0ef3306 mysql -h127.0.0.1 -u데이터베이스계정 -p비밀번호 employees < load_salaries2.dump
docker exec -i 1c19b0ef3306 mysql -h127.0.0.1 -u데이터베이스계정 -p비밀번호 employees < load_salaries3.dump
docker exec -i 1c19b0ef3306 mysql -h127.0.0.1 -u데이터베이스계정 -p비밀번호 employees < show_elapsed.sql
Bash
복사
노가다를 하니 잘 들어갔다.
use employees;
show tables;
SQL
복사
select * from employees;
SQL
복사
다음 포스팅에서는 이 샘플 데이터로 실습을 하면서 공부를 하도록 하겠다.