CODO Day32_DB(Table/Data/Commit/Transaction)

Table

• 테이블은 특정 데이터베이스 안에 존재
• 데이터 베이스에서 테이블은 여러 데이터의 집합체
• JAVA의 Class와 비슷함(객체로 봄)

Written By zer0box@naver.com

 

 

show databases; -- 현재 존재하는 데이터베이스

create database mydb; -- mydb라는 데이터베이스 생성

use mydb; -- mydb라는 데이터베이스를 사용

show tables; -- 현재 데이터베이스 내 테이블 목록 보기

 

테이블 생성

CREATE TABLE [테이블 명](
    [컬럼명] [데이터 타입] (사이즈),
    [컬럼명] [데이터 타입] (사이즈)
);

 

데이터타입 종류(자주 쓰는 일부) 

• 문자타입
  • 고정형  :  char(바이트 수)
  • 가변형  :  varchar(바이트 수)
  • text  :  65,535 byte
  • longtext  :  4,294,967,295 byte
• 숫자타입
  • int, bigint, float, double

• boolean
• 날짜타입 
  • date           >>  0000-00-00
  • datetime    >>  0000-00-00 00:00:00
  • timestamp  >>  datetime과 같지만 서버의 timezone에 따라 시간이 변경됨

 

• 테이블 만들기

-- 카멜기법 사용x(대소문자 구분 못하는 경우도 있음)
create table test_table(
	user_name varchar(20),
	age int(3),  -- int(자릿수)
	mobile varchar(13),
	reg_date date default current_date
);  
-- 테이블 구조 확인
desc test_table;

create table employees(
	emp_no int(3),
	first_name varchar(8),
	last_name varchar(2),
	email varchar(50),
	mobile varchar(13),
	salary int(8),
	reg_date date default current_date
);
desc employees;  -- 테이블 상세보기

 

• 테이블 삭제

-- DROP TABLE [테이블 이름] - 완전 삭제 복구X
drop table test_table;

show tables;

-- 테이블 내 데이터를 모두 삭제
-- 테이블 자체를 삭제 >> 재생성해줌
-- TRUNCATE TABLE [테이블 명]
truncate table test_table;

 

• 테이블 수정

-- 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));
alter table employees add (commission varchar(10)); 
alter table employees add (etc varchar(10)); 
desc employees;

-- 3) 컬럼 속성 변경 : 컬럼이 비어있어야 함
-- ALTER TABLE [테이블명] MODIFY COLUMN [컬럼명] [데이터타입](사이즈)
-- float(전체자리, 그 중 소숫점 자리) >> 00.00일 경우(4,2)
alter table employees modify column commission float(4,2);
desc employees;

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

 

 

 

DML(SELECT/INSERT/UPDATE/DELETE/UPSERT)

• 데이터를 취급하는 구문

SELECT	특정 조건의 데이터를 조회
INSERT	특정 데이터를 테이블에 삽입
UPDATE	특정 조건의 데이터를 수정
DELETE	특정 조건의 데이터를 삭제
UPSERT(정식명칭X)	입력되는 key가 중복일 경우 UPDATE, 없을 경우 INSERT

 

 

• 데이터 삽입(INSERT)
  • INSERT INTO [테이블명]([컬럼명, 컬럼명, ...])VALUES([값,값,...])

use mydb;

insert into employees(emp_no,first_name,family_name,email,mobile,salary,depart_no,commission)
	values(111,'길동','홍','admin@naver.com','01012345678',99999999,'dev_001',90);

insert into employees(emp_no,first_name,family_name,email,mobile,salary)
	values(112,'길동','고','admin2@naver.com','01012345672',99999999);

insert into employees(emp_no,family_name,email,mobile,salary)
	values(111,'홍','admin@naver.com','01012345678',99999999);

select * from employees;

 

• 데이터 수정(UPDATE)
  • UPDATE [테이블명] SET [컬럼] = [이름] WHERE [조건] : 조건 없으면 전체 수정*

update employees set depart_no = 'dev002' where depart_no is null;
update employees set commission = '10' where commission is null;

 

• 데이터 삭제(DELETE)
  • DELETE FROM [테이블명] WHERE [조건]

delete from employees where first_name is null;

 

• 값이 없으면 INSERT, 있으면 UPDATE 하기
  • UPSERT라고 부름(명령어 아님 - 통용이름 )
  • 다만 제약조건이 있어야 하므로 제약조건 이후에 다시 다룸

 

 

데이터 조회(SELECT)

• 특정한 데이터를 선택(SELECT)하여 볼 수 있음
• 세밀한 선택을 위해 조건문 사용(WHERE)

 

• 특정 컬럼 조회
  • SELECT [컬럼] FROM [테이블];

select * from employees;
select first_name, family_name, salary from employees;

-- 산술표현
-- 숫자컬럼의 경우 산술표현(연산)이 됨
select first_name, family_name, salary/10000 from employees;
-- as : 컬럼에 별칭을 부여
select first_name, family_name, salary*12 as 연봉 from employees;
select concat(family_name, first_name) as name, salary*12 as 연봉 from employees;

-- name, salary 출력
-- salary는 450만원 형태로 출력
-- concat : 여러개의 컬럼을 하나로 합쳐줌
-- truncate(숫자,버릴 자릿수) : 숫자를 버릴 자릿수 아래로 버림
select concat(family_name,first_name) as name, 
		concat(truncate(salary/10000,0),'만원') as 연봉 
from employees;

 

• 특정 조건을 만족하는 데이터 조회
  • SELECT [컬럼] FROM [테이블] WHERE [조건];

select * from employees where family_name = '김';
-- 급여가 300만원 이상인 사람 찾기
select * from employees where salary >= 3000000;

-- 2-1) AND 조건
-- 급여가 100만원 이상이고, 500만원 이하인 사원 찾기
select * from employees where salary >= 1000000 and salary <= 5000000;

-- 2-2) OR 조건
-- family_name이 김이거나 급여가 2000000인 사원 찾기
select * from employees where family_name = '김' or salary = 2000000;

-- 2-3) BETWEEN AND(태그와 함께 사용 시 헷갈림 방지, 속도빠름)
-- salary가 50만원 이상 400만원 이하인 사람의 first_name과 family_name 구하기
select first_name, family_name from employees where salary between 500000 and 4000000;

 

• 중복제거
  • SELECT DISTINCT [중복제거 컬럼,...] FROM [테이블]
    - 메뉴얼에는 1개 이상 가능하다고 하지만 1개 이상 넣는 것 지양
    - 중복제거는 1개 컬럼을 대상으로 하는 것이 가장 정확
    - 한번에 실행하면 각각 중복값 제거 후 합치는 기능(합치면서 중복발생)

select distinct family_name from employees;
select distinct family_name,salary from employees;

 

• IN : 공통컬럼으로 여러 값을 or 조건을 사용할 때 간략화시킴(속도빠름)

-- family_name이 '김' or '박' or '이'인사람의 모든 컬럼 가져오기
select * from employees where family_name in ('김','박','이');

 

• IS NULL / IS NOT NULL

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

 

• LIKE 검색
  • 일부가 비슷한 내용을 검색(속도가 느림)
    - WHERE [컬럼명] LIKE '%[문자열]%'
    - ze%        :  ze로 시작하는
    - %com     :  com으로 끝나는
    - %se%     :  se를 포함하는(가장많이사용)
    - %s%e%  :  s or 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%';

 

• ORDER BY
  • 특정 컬럼을 기준으로 정렬 ASC(오름차순) - 기본값 | DESC(내림차순)
  • order by의 위치(가장 뒤에 위치*)
    데이터를 뽑은 후 정렬하는 것이 정렬 후 데이터를 뽑는 것보다 효율적
  • 다중정렬(1차 정렬 후 동률의 데이터에서 2차 정렬 진행)

-- salary가 많은 순
select * from employees order by salary desc;
-- family_name을 가나다순으로
select * from employees order by family_name;
-- 연봉을 높은 순으로 정렬
select emp_no, first_name, family_name, salary*12 as annsal from employees order by annsal desc; 
-- 다중정렬(1차 정렬 후 동률의 데이터에서 2차 정렬 진행)
select * from employees order by family_name, salary desc;
-- 급여(salary)가 200만원 이상인 사람을 family_name 순으로 오름차순
select * from employees where salary >= 2000000 order by family_name;

 

• GROUP BY 
  • 특정한 컬럼을 중심으로 데이터를 묶어서 가져오는 것
  • SELECT [컬럼,...] FROM [테이블] GROUP BY [묶어줄 기준 컬럼]

-- depart_no을 기준으로 묶어서 가져오기
-- 묶인 대상 컬럼, 연산(합계,평균,집계,...)된 내용
-- 부서번호, 부서 급여합계
select depart_no, sum(salary) from employees group by depart_no;
-- 부서번호, 부서 급여평균
select depart_no, avg(salary) from employees group by depart_no;
-- 부서번호, 급여 합계
select depart_no, sum(salary) as 급여합계, count(depart_no) as 사원수 
from employees group by depart_no;

# 컬럼연산  :  SUM, AVG, COUNT

 

• HAVING
  • GROUP BY로 부터 얻어온 결과를 특정조건으로 추출
  • HAVING에는 별칭을 사용하지 말 것(특정 DB에서는 먹히지 않음)

-- group by 해온 데이터중에서 sum(salary)가 5000만원 넘는 데이터만 가져오기
select depart_no, sum(salary) as sal_sum from employees group by depart_no 
having sum(salary) > 50000000;

-- 급여합계가 1000만원 이상인 팀들만 조회(합계 급여가 큰 순으로)
select depart_no, sum(salary) as sal_sum from employees group by depart_no 
having sum(salary) > 10000000 order by sal_sum desc;

 

 

 

Transaction

• 쪼갤 수 없는 업무처리의 단위(한번에 수행되야하는 연산)
• All or Nothing  :  모든 작업 수행  |  모든 작업 수행X
• 분리할 수 없는 작업단위이므로 작업결과가 둘 중 하나여야함
• 하나의 작업 내부의 과정이 잘못되면 전부 취소해야함
• ex) 송금 기능 : 입금+출금이 한번에 이루어져야 함
• COMMIT(작업완료시)  |  ROLLBACK(중간에 오류발생 시 모든 작업 취소 - 원래의 상태로 돌아감)

 

-- 1) COMMIT : (데이터를 변경한) 실행한 쿼리를 확정
SELECT @@AUTOCOMMIT;  -- 가장 먼저 auto commit 여부 확인(1:true, 0:false)

set autocommit = 0;  -- 수정 전 반드시 auto commit 기능 OFF
-- auto commit을 OFF해도 CREAT, ALTER, DROP, TRUNCATE는 취소 안됨

commit;  -- 실행한 쿼리를 확정

-- 2) ROLLBACK : (데이터를 변경한) 실행한 쿼리 취소
-- emp_no 117 ~ 130까지 삭제
delete from employees where emp_no between 117 and 130;
rollback;  -- 지난 쿼리 되돌리기(삭제취소)
-- 1이 나올경우 되돌릴 수 있으나 0이 나오면 되돌릴 수 없음

-- 신규작업(insert, update, delete)이 확정되면 반드시 commit을 해줄것!!
-- 이후에 직접 서비스(웹)에서 확인할 것