DBMS
DBMS: "DB 관리 도구" - oracla, mysql, ms-sql, mariadb
DBMS가 DB를 관리하기 위한 용도로 사용하는 언어: SQL -> 특정 벤더에 의존적이지 않다
SQL + 자체 기술 -> 벤더에 의존적인 SQL(대표적으로 오라클 -> PL/SQL)
DB
Database: 데이터를 담는 큰 그릇
database는 data를 table에 담아 정리한다.
생성된 테이블은 관계를 형성하여 유의미한 데이터를 추출해 낼 수 있게 된다.
이러한 관계형 database는 여전히 많이 사용되고 있지만, 최근의 경향과 같이 소규모 데이터의 잦은 입출력에 대해서는 관계형 보다 NoSQL 등을 활용하는 경향도 늘어나고 있다.
PK(기본키), FK(외래키)
기본키(PK): 각 행을 구분하는 유일한 열
회원가입 시 각 행의 유일성을 보장 받기 위하여 ID를 필수 입력 사항으로 받는다. 이 때 기존 DB에 동일한 ID가 있는지 여부를 미리 확인하고, 없을 경우에 DB에 행을 작성하게 된다. 우리는 ID를 통해 특정 유저를 확인할 수 있는데 이러한 ID와 같은 열을 기본키로 지정할 수 있다. -> 기본키로 지정되면 데이터 입력이 필수이므로 별도로 체크하지 않더라도 NOT NULL로 지정된다. 비슷하게 데이터 작성 시 유일성을 보장 받기 위한 것으로 UNIQUE를 사용할 수 있다. 이는 회원가입 시 휴대폰 번호와 같은 것으로 유일성을 보장 받으나, 반드시 입력해야 하는 사항은 아니다.
FK(외래키) -> 외래키를 따라가서 부모 테이블을 만나면 유일한 데이터를 만날 수 있다.
데이터베이스 구축 절차
데이터베이스 생성 -> 테이블 생성 -> 데티업 입력 -> 활용(select 정렬, 구매 전체 금액 -> 포인트 지급): 웹이나 CLI와 연결하여 사용자에게 정보를 제공
데이터베이스 실습
실습 데이터 다운로드
weget http://dw.hanbit.co.kr/mariadb/10.3/employees.zip
mkdir dbtest
mv employees.zip dbtest/
cd dbtest/
unzip employees.zip
실습 데이터 적용
mysql -u root -p1234
source employees.sql
데이터 확인
show tables;
desc employees;
select * from employees limit 100;
select * from employees where first_name = 'Jayson';
인덱스(Index)
- 검색 속도를 높이기 위한 튜닝 기법 중 하나
- 인덱스는 테이블의 열 단위에 생성한다.
- 무조건 인덱스를 사용한다고 해서 속도가 높아지는 것 만은 아니다. 보통은 PK 열에 지정하는 경우가 많다.
테이블 생성
CREATE TABLE indextbl (first_name varchar(14), last_name varchar(16), hire_date date);
show tables;
desc indextbl;
데이터 추가
insert into indextbl
select first_name, last_name, hire_date
from employees limit 500;
select * from indextbl;
데이터 조회
select * from indextbl where first_name = 'Mary';
explain select * from indextbl where first_name = 'Mary';
-> type 부분을 보면 ALL이라고 되어있음
-> 전체 데이터를 다 보고 검색했다는 뜻
인덱스 만들기
CREATE INDEX idx_test ON indextbl(first_name);
explain select * from indextbl where first_name = 'Mary';
-> type를 보면 ref라고 되어있음
-> key: idx_test
-> Extra: Using index condition
-> index를 적용하여 데이터를 검색헀음
뷰(View)
- select가 실체이다
- 실제 테이블이 아니라 원본 테이블에서 필요한 열만 추출하여 별도의 '가상 테이블'을 만드는 것
- 원본 테이블에 연결된 일종의 링크 개념
- 하나의 테이블에서 별도의 view를 만들 수도 있지만 여러 테이블에서 필요한 열을 조합하여 하나의 가상 테이블로 만드는 것도 가능하다.
- 보안 상 중요 열에 접근하는 것이 꺼려지는 경우 별도의 view를 만들고 이에 접근할 수 있는 사용자를 지정한다.
- 별도의 저장 공간을 차지하지 않음
https://learnsql.com/blog/sql-view/
VIEW 생성
CREATE VIEW v_emp
AS
SELECT first_name, hire_date
FROM employees LIMIT 50;
show tables;
select * from v_emp;
[HeidiSQL]
v_emp 생성되었음
스토어드 프로시져(Stored Procedure)
- mariaDB에서 제공하는 프로그래밍 기능
- 함수와 비슷하게 프로그램을 작성한 뒤, 필요할 때마다 호출하여 사용할 수 있다.
- 일반적인 인터랙티브한 명령 실행이 아닌, 전체 내용을 한꺼번에 실행하는 형식으로 동작한다.
[HeidiSQL]
DELIMITER //
CREATE PROCEDURE myproc()
BEGIN
SELECT FROM indextbl
WHERE first_name = 'Maris';
SELECT FROM indextbl
WHERE first_name = 'Lobel';
END //
DELIMITER ;
CALL myproc();
트리거(trigger)
BEFORE/AFTER :
- 테이블에만 부착
- 대상 테이블의 insert, delete, update 도작 직전/직후에 미리 지정한 동작을 수행
INSTEAD OF :
- 대상 테이블의 insert, delete, update 동작대신 지정한 동작이 대신 수행됨
- 이벤트 발생전 동작
- 테이블에는 부착되지 않고 뷰에만 부착됨
OLD.컬럼명 : DELETE 시 임시 저장
NEW.컬럼명 : insert,update,delete 작업시 변경할 새로운 데이터를 임시저장. 따라서 NEW 를 조작하면 입력되는 값을 변경시킬 수 있음.
백업 테이블 생성
CREATE TABLE deletedUserTBL (
id CHAR(8),
username CHAR(20),
email CHAR(30),
deletedDate DATE -- 삭제한 날짜
);
트리거 생성
DELITIMER //
CREATE TRIGGER trg_deletedUserTBL -- 트리거 이름
AFTER DELETE -- 삭제 후에 작동하게 지정
ON usertbl -- 트리거를 부착할 테이블
FOR EACH ROW -- 각 행마다 적용시킴
BEGIN
-- OLD 테이블의 내용을 백업 테이블에 삽입
INSERT INTO deletedUserTBL
VALUES (OLD.id, OLD.username, OLD.email, CURDATE());
END //
결과 확인
SELECT * FROM usertbl; -- 원본 테이블
DELETE FROM usertbl WHERE id = 'user1'; -- 사용자 삭제
SELECT FROM usertbl; -- 원본테이블(user1 없는 것 확인)
SELECT FROM deletedUserTBL; -- 삭제된 사용자 저장된 테이블(user1 정보 확인 가능)
SQL 내보내기
SQL 파일로 내보내기
불러오기 확인
usertbl 삭제해보기
내보냈던 SQL 파일 불러오기
삭제했던 usertbl 정상적으로 불러와졌음
모델링
dbForge v8.0.124 설치
http://dw.hanbit.co.kr/mariadb/10.3/dbforgemysql80exp.exe
centos와 연결 설정
모델링에서의
실선: 부오 테이블의 PK가 자식 테이블의 Fk가 되는 경우
점선: 부모 테이블의 PK가 자식 테이블의 일반 속성이 되는 경우
(부모가 없어도 자식이 생기는 경우)
새 모눈종이
file -> new -> database diagram
데이터베이스 불러오기 -> employees 데이터베이스 선택
새 데이터베이스 생성
db name: modelDB
테이블 생성
usertbl 생성
buytbl 생성
FK 설정
각 테이블의 username 연결
usertbl에 행 삽입
buytbl에 행 삽입
usertbl에 입력했던 username을 drop down list에서 선택
테이블 조회
SELECT 구문
아래의 순서 지켜야 함
select
from
where
group by
having
order by
ANY, ALL, IN
ex) userTbl에서 경남 사람의 키가 170, 173일 때
다음과 같은 서브쿼리 오류 발생
subquery returns more than 1 row
1) ANY(SOME) -> 여러 조건 중 하나라도 만족
-> 170 이상인 사람
2) ALL -> 여러 조건 모두 만족해야 함
-> 173 이상인 사람
3) = ANY -> IN 과 같은 기능
4) IN
참고: 이것이 MariaDB다, 우영우, 한빛미디어
