01. DB의 기초

1. DB (MariaDB)

 

 

-- 주석
/* 1. 유저 생성 */
-- CREATE USR [유저이름]@[접근 가능한 IP] IDENTIFIED BY [비밀번호];

-- web_user 는 모든 IP 에서 접근 가능하고, 비밀번호는 pass 로 만들어라.
create user 'web_user'@'%' identified by 'pass';

-- dba_user 는 192.168 로 시작되는 IP 에서 접근 가능하고, 비밀번호는 pass 로 만들어라.
-- 특정 IP 대역만 접근 가능하게 할 경우
create user 'dba_user'@'192.168.%' identified by 'pass';

-- user002 는 192.168.38.70 IP 에서만 접근 가능하고, 비밀번호는 pass3 로 만들어라.
create user 'user002'@'192.168.38.70' identified by 'pass3';

-- 유저 생성 확인
select * from mysql.user;

/* 2. 권한 부여 및 회수 */
-- 권한 부여 (GRANT)
-- GRANT [권한 종류] ON [데이터베이스.테이블] TO [유저명]
-- INSERT(추가), UPDATE(수정), SELECT(조회), DELETE(삭제)
-- ALL PRIVILEGES : GRANT OPTION 을 제외한 모든 권한 부여
-- GRANT OPTION : 권한을 주는 권한

-- dba_user 에게 모든 데이터베이스의 모든 테이블의 데이터를 볼 수 있는 권한을 준다.
-- user 명시할 때 IP 도 함께 명시해야 한다.
grant select on *.* to 'dba_user'@'192.168.%';

-- user002 에게 모든 데이터베이스의 모든 테이블의 데이터를 보고, 추가하고, 수정하고, 삭제할 수 있는 권한을 준다.
grant insert, update, select, delete on *.* to 'user002'@'192.168.38.70';

-- web_user 에게 모든 데이터베이스의 모든 테이블의 데이터를 뭐든지 할 수 있는(권한 주는거 제외) 권한을 준다.
-- IP 가 % 인 경우 생략 가능
grant all privileges on *.* to 'web_user'@'%';
grant all privileges on *.* to 'web_user';

-- 유저 권한은 user 확인에서 가능하다.
select * from mysql.user;

/* 권한 회수 */
-- REVOKE [권한] ON [데이터베이스.테이블] FROM [유저명];
-- 'user002'@'192.168.38.70' 에게 insert, update, delete 권한 회수하기
revoke insert, update, delete on *.* from 'user002'@'192.168.38.70';

/* 비밀번호 변경 */
-- SET PASSWORD FOR [사용자] = PASSWORD([패스워드]);
-- 'user002'@'192.168.38.70' 의 비밀번호를 pass 로 바꿔보자
set password for 'user002'@'192.168.38.70' = password('pass');

/* 유저 삭제 */
-- DROP USER [유저이름]@[IP];
drop user 'dba_user'@'192.168.%';
drop user 'user002'@'192.168.38.70'

/* D.C.L (Data Control Language) */
-- 데이터 사용 권한이나 유저를 다루는 언어

 

 

 

 

 

 

 

2. DDL (Data Define Language)

 

 

 

/* D.D.L (Data Define Language) : 데이터베이스나 테이블 등을 생성하는 언어 */
-- 데이터베이스 목록 보기
show databases;

-- 데이터베이스 생성
-- CREATE DATABASE [데이터베이스 이름];
create database mydb;

-- 데이터베이스 삭제
-- DROP DATABASE [데이터베이스 이름];

-- 데이터베이스 사용
-- USE [데이터베이스 이름];
use mydb;

-- 테이블 목록 확인
show tables;

-- 테이블 생성
-- CREATE TABLE [테이블명](
-- [컬럼명] [데이터타입](크기)
-- ...
-- );

/* 데이터 타입 종류 (자주 쓰는 일부)
 *  1) 문자 타입
 * 고정형 - 한번 정해진 크기가 변하지 않음 (2글자로 선언했으나 1들자로 오면 남은 한 자리는 공백 처리됨)
 * char(바이트 수)
 * 가변형 - 정해진 크기보다 적게 들어오면 알아서 줄여준다.
 * varchar(바이트 수)
 * text		: 65,535 byte
 * longtext	: 4,294,967,295 byte
 * 
 *  2) 숫자 타입 - int, long, float, double
 * 
 *  3) boolean
 * 
 *  4) 날짜 타입
 * DATE				: 0000-00-00
 * DATETIME		: 0000-00-00 00:00:00
 * TIMESTAMP	: DATETIME 과 같지만 time zone 에 따라 시간이 변경된다.
 * */

create table test_table(
	user_name varchar(50),
	age int(3),
	mobile varchar(11),
	reg_date date default current_date -- 기본으로 현재 날짜를 넣는다.
);

-- 테이블 구조 보기
desc test_table;

-- 테이블을 아래와 같이 만들어보자
-- 테이블 명 : employees
-- emp_no 숫자형 3자리
-- first_name 문자열 8자
-- last_name 문자열 2자
-- email 문자열 50자
-- mobile 문자열 11자
-- salary 숫자형 8자
-- reg_date 날짜 기본으로 입력한다.
create table employees(
	emp_no int(3),
	first_name varchar(8),
	last_name varchar(4),
	email varchar(50),
	mobile varchar(13),
	salary int(8),
	reg_date date default current_date
);

desc employees;

show tables;
-- 테이블 삭제
-- DROP TABLE [테이블 명];
drop table test_table;

-- CREATE 로 생성한 녀석은 DROP 으로 삭제하고 ALTER 로 수정한다.
-- 테이블 수정 (컬럼에 대한 수정)
-- 1) 컬럼의 이름 변경 (기존 데이터가 삭제된다.)
-- ALTER TABLE [테이블 명] RENAME COLUMN [변경 전 이름] TO [변경 후 이름];
ALTER TABLE employees RENAME COLUMN last_name TO family_name;
desc employees;

-- 2) 컬럼 추가
-- ALTER TABLE [테이블 명] ADD ([컬럼 명] [데이터 타입](크기));
ALTER TABLE employees ADD (depart_no varchar(10)); -- depart_no 문자열 10바이트
ALTER TABLE employees ADD (commision varchar(10)); -- commision 문자열 10바이트
ALTER TABLE employees ADD (etc varchar(100)); -- etc 문자열 100바이트
desc employees;

-- 3) 컬럼 속성 변경 (해당 컬럼이 비어 있어야 한다.)
-- ALTER TABLE [테이블 명] MODIFY COLUMN [컬럼 명] [데이터 타입](크기);
ALTER TABLE employees MODIFY COLUMN commision float(4,2); -- float(전체 자릿수, 소숫점 자릿수) -> 00.00
desc employees;

-- 4) 컬럼 삭제
-- ALTER TABLE [테이블 명] DROP COLUMN [컬럼 명];
ALTER TABLE employees DROP COLUMN etc;
desc employees;

 

 

 

 

 

3. DML (Data Manipulation Language)

 

 

 

 

Statement	Description
SELECT	특정 조건의 데이터를 조회한다.
INSERT	특정 데이터를 테이블에 삽입한다.
UPDATE	특정 조건의 데이터를 수정한다.
DELETE	특정 조건의 데이터를 삭제한다.
UPSERT	입력되는 key 가 중복일 경우 UPDATE 하고, 아닐 경우 INSERT 한다.

 

 

 

 

/* D.M.L (Data Manipulation Language)
 * 데이터를 다루는 언어 (SELECT, INSERT, UPDATE, DELETE, UPSERT)
 */

select * from employees;
desc employees;
-- 1. 데이터 삽입
-- INSERT INTO [테이블 명](컬럼 명, ...)VALUES(컬럼 명에 넣을 값, ...);
INSERT INTO employees(emp_no, first_name, family_name, email, mobile, salary, depart_no, commision)
	VALUES(111, '지훈', '김', 'zer0box@naver.com', '01012341234', 99999999, 'dev01', 90);

insert into employees(emp_no, first_name, family_name, email, mobile, salary)
	values(112,'태근','김','email@naver.com','01020343054',99999999);
	
insert into employees(emp_no, family_name, email, mobile, salary)
	values(113,'김','email@naver.com','01020343054',99999999);
	
-- 2. 데이터 삭제
-- DELETE FROM [테이블 명]
-- commision 이 null 인 값을 지워보자.
delete from employees where commision is null;

-- 3. 데이터 수정
-- UPDATE [테이블 명] SET [컬럼] = [값] WHERE [조건];
-- depart_no 가 dev005 인 row 에 대해서 commision 을 30으로 변경
update employees set commision = 30 where depart_no = 'dev005';

 

 

-- 데이터 조회
-- 1) 특정 컬럼 조회
-- SELECT [조회할 컬럼] FROM [테이블 명];
select * from employees;
select first_name, family_name, mobile from employees;

-- 산술 표현
-- 숫자로 된 컬럼에 대해서는 계산해서 보여줄 수 있다. (실제로 계산)
select first_name, family_name, salary/10000 as 급여 from employees;
-- 컬럼명을 임시로 변경해서 보여줄 수도 있다.

-- 보여주는 순서 변경 가능
select family_name, first_name, salary/10000 as 급여 from employees;

-- 문자열을 합칠 수도 있다.
select  
	concat(family_name, first_name) as 이름, 
	concat(truncate(salary/10000, 0), '만원') as 급여 
from employees;

-- 이 모든건 실제 데이터를 가공해서 보여줄 뿐이지 조작되진 않는다.

-- 2) 특정 조건의 데이터를 조회
-- SELECT [조회할 컬럼] FROM [테이블] WHERE [조건];
select * from employees where family_name = '김';
select  * from  employees where salary > 3000000;

-- family_name == '김' && salary > 2000000 AND
-- family_name = '김' AND salary > 2000000
-- salary >= 1000000 && salary <= 3000000
select * from employees where salary >= 1000000 and salary <= 3000000;

-- || OR
-- family_name == '김' || salary == 2000000;
select * from employees where family_name = '김' or salary = 2000000;

-- BETWEEN AND (특정 값 사이를 구할 때 사용)
-- AND 조건을 이용해서 salary 가 50만원보다 크거나 같고, 400만원 보다 작거나 같은 사람의 first_name, family_name, salary 를 구하시오.
select first_name, family_name, salary from employees where salary >= 500000 and salary <= 4000000;
-- 부등호가 특수문자로 인식되는 경우가 있어서 피하고자 할 때 종종 사용된다.
select first_name, family_name, salary from employees where salary between 500000 and 4000000;

-- 3) 중복 제거
-- SELECT DISTINCT [출력할 컬럼] FROM [테이블]
select distinct family_name from employees where salary = 2000000;
-- distinct 는 하나의 컬럼만 사용할 때 가장 정확한 효과가 나타난다.
select distinct family_name, first_name from employees where salary = 2000000;

-- 4) IN (OR 하고 똑같다.)
-- family_name 이 '김' 또는 '이' 또는 '박' 인 사람의 모든 정보를 가져와라.
select * from employees where family_name = '김' or family_name = '이' or family_name  = '박';
-- OR 로 비교하는 컬럼이 모두 같을 경우 사용한다. (속도도 훨씬 빠르다.)
select * from employees where family_name in ('김', '이', '박');

-- 5) IS NULL || IS NOT NULL
select * from employees;
-- commision 이 10 인 녀석을 찾아서 null 로 만들어라.
update employees set commision = null where commision = 10;

select * from employees where commision is null;
select * from employees where commision is not null;

-- 6) LIKE
-- 일부 비슷한 문자열을 검색 - 검색 기능을 사용할 때 가장 많이 사용
-- WHERE [컬럼 명] LIKE '%[문자열]%'
-- 남발할 경우 속도 저하가 심하다.

-- ze%		: ze 로 시작하지만 뒤에는 아무거나 와도 된다. -> ze 로 시작하는...
-- %com	: 앞에 아무거나 와도 되지만 뒤에는 com 으로 끝나야 한다.	-> com 으로 끝나는...
-- %se%	: se 앞 뒤에 아무거나 와도 된다. -> se 를 포함하는...
-- %s%e%	: s 와 e 사이에 아무거나 와도 된다. -> s 또는 e 를 포함하는...

select * from employees where email like 'ze%';
select * from employees where email like '%com';
select * from employees where email like '%se%';
select * from employees where email like '%s%e%';

-- 7) ORDER BY (정렬)
-- 특정 컬럼을 기준으로 정렬
-- 오름차순 (▲) ASC | 내림차순 (▼) DESC
-- SELECT * FROM [테이블 명] ORDER BY [컬럼 명] [ASC | DESC];
select * from employees order by salary desc;
select * from employees order by family_name asc;
select * from employees order by family_name; -- asc 는 생략이 가능하다.

-- 김 씨 중에서 급여가 높은 순으로 정렬하시오.
select * from employees where family_name = '김' order by salary desc;
-- 연봉이 높은 순으로 정렬하시오.
select first_name, family_name, email, salary * 12 as ann_sal from employees order by ann_sal desc;
-- 다중정렬 (1차 정렬 후 동률의 데이터에서 2차 정렬을 진행)
select * from employees order by family_name, salary desc;

-- 8) GROUP BY
-- 데이터를 특정 컬럼을 기준으로 묶어서 가져오는 경우 사용 (통계)
-- SELECT [컬럼 1], [컬럼 2], ... FROM [테이블 명] GROUP BY [묶어줄 컬럼];
-- 조회되는 컬럼들은 묶어주는 기준 컬럼이거나, 집계되는 컬럼이어야 한다.

-- 부서별로 급여 합산
select * from employees;
select family_name, first_name, salary from employees where depart_no = 'dev001';
select family_name, first_name, salary from employees where depart_no = 'dev002';
select family_name, first_name, salary from employees where depart_no = 'dev003';
select family_name, first_name, salary from employees where depart_no = 'dev004';
select family_name, first_name, salary from employees where depart_no = 'dev005';

-- 위 내용을 GROUP BY 로 변환
select depart_no, sum(salary) from employees group by depart_no;

-- 그룹의 기준이 되는 컬럼과 집계 컬럼 외의 다른 컬럼이 들어간다면?
-- 나오긴 하지만 그 의미를 알 수 없다. -> 각 그룹의 가장 첫 값을 보여준다.
-- 다른 DB 에서는 이 경우 에러를 발생시킬 수 있다.
select depart_no, sum(salary), family_name from employees group by depart_no;

-- 부서 별 급여 평균 - AVG(column)
select depart_no, truncate(avg(salary), 0) as sal_avg from employees group by depart_no;

-- HAVING
-- GROUP BY 에서 WHERE 처럼 조건을 주고 싶을 때 사용
-- 합계 급여가 500만원 이상인 팀을 추출
-- having 은 별칭은 사용하면 안된다. (total 대신 sum(salary) 를 써야한다.
-- 마리아에서는 허용 해 주지만 몇몇 DB에서는 에러가 발생한다.
select depart_no, sum(salary) as total, family_name 
	from employees group by depart_no having sum(salary) > 5000000;

-- 급여 합계가 500만원 이상인 부서들을 합계 급여가 높은 순으로 정렬하시오.
select depart_no, sum(salary) as total from employees group by depart_no having sum(salary) > 5000000 order by total desc;

 

 

 

 

 

 

 

 

 

4. Transaction

 

 

 

Transaction 은 Data Base 에서 사용되는 쪼갤 수 없는 업무처리의 단위이다.

 

ALL or NOTHING, 하나의 작업 내부의 과정이 잘못되면 전부 취소 되어야 한다.

 

-- transaction -> TX -> tranx

-- 1) AUTO COMMIT 여부
select @@autocommit; -- 1 : 설정, 0 : 미설정

-- 설정 | 미설정
set autocommit = 1; -- autocommit 설정 on
set autocommit = 0; -- autocommit 설정 off

-- commit 과 rollback 은 데이터(DML) 에 한정된다.
-- DCL, DDL 은 되돌릴 수 없다.

-- 현재 상태 저장 하는 것이 commit;
select  * from employees;
update employees set commision = 10 where emp_no = 129;
commit;

delete from employees;
-- rollback : commit 하지 않은 내용에 대해서 되돌릴 수 있다.
rollback;