DDL
SQL 구문은 DCL, DDL, DML로 구분하며, 아래와 같은 종류가 있다
SQL 종류
- DDL: 데이터 정의어
- 데이터베이스 객체(table, view, index)의 구조를 정의
- 테이블 생성, 컬럼 추가, 타입변경, 제약조건 지정, 수정 등
데이터베이스 생성
create database 데이터베이스명;
or
create database 데이터베이스명
default charcter set 값
collate 값;-
Charcter set은 각 문자가 컴퓨터에 저장될 때 어떠한 '코드'로 저장될지에 대한 규칙의 집합을 의미한다
-
Collation은 특정 문자 셋에 의해 데이터베이스에 저장된 값들을 비교 검색하거나 정렬 등의 작업을 위해 문자들을 서로 '비교'할 때 사용하는 규칙들의 집합을 의미한다.
데이터베이스 변경
alter database 데이터베이스명
default charcter set 값 collate 값;
데이터베이스 삭제
- 이름이 'dbtest'인 데이터베이스 삭제
drop database dbtest;- 이름이 'dbtest'인 데이터베이스 사용
use dbtest;Data Type - 문자형
Data Type - 날짜형
Create Table
-
table 생성
-
optional attributes
- NOT NULLL: 각 행은 해당 열의 값을 포함해야 하며 null값은 허용되지 않음
- DEFAULT value: 값이 전달되지 않을 때 추가되는 기본값 설정
- UNSIGNED: Type이 숫자인 경우만 해당하며 숫자가 0 또는 양수로 제한됨
- AUTO INCREMENT: 새 레코드가 추가 될 때마다 필드 값을 자동으로 1 증가 시침
- PRIMARY KEY: 테이블에서 행을 고유하게 식별하기 위해 사용. PRIMARY KEY 설정이 있는 열은 일반적으로 ID번호이며 AUTO INCREMENT와 같이 사용되는 경우가 많음
-
제약 조건
- 컬럼에 저장될 데이터의 조건을 설정하는 것
- 제약조건을 설정하면 조건에 위배되는 데이터는 저장 불가
- 테이블 생성시 컬럼에 직접 지정하거나 contraint로 지정, 또는 ALTER를 이용하여 설정 가능
- table 생성 1
- 스키마: 데이터베이스의 테이블에 저장될 데이터의 구조와 형식을 정의
- table 생성 2
- ER Diagram(ERD): 개체 타입과 관계 타입을 기본 개념으로 현실 세계를 개념적으로 표현하는 방법
- table 생성 3
- 스키마를 참조하여 테이블 생성 SQL 작성
use userdb;
CREATE TABLE user_member(
idx INT NOT NULL AUTO_INCREMENT,
userid VARCHAR(16) NOT NULL
username VARCHAR(16)
userpwd VARCHAR(16)
emailid VARCHAR(16)
emaildomain VARCHAR(16)
joindate TIMESTAMP NOT NULL DEFAULT current_timestamp,
PRIMARY KEY(idx)
)INDEX
Index
-
책의 찾아보기(index)와 같이 원하는 내용을 바로 찾을 수 있도록 지원
-
테이블의 데이터 조회 시 동작속도를 높여주는 자료구조
-
데이터의 위치를 빠르게 찾아주는 역할
-
컬럼의 값과 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 둠
-
MYI(MySQL Index) 파일에 인덱스 저장
Index의 문제점
-
책의 모든 페이지에 나오는 단어를 찾아보기에 표시하게 되면 찾아보기의 분량이 엄청나게 많아져 오히려 본문보다 두꺼워지는 상황이 발생
-
필요 없는 Index를 만들면 데이터베이스가 차지하는 공간만 늘어나고, index를 이용하여 데이터를 찾는 것이 전체 테이블을 찾는 것 보다 느려짐
-
데이터베이스의 공간을 차지하므로 추가적인 공간 필요(DB 크기의 10% 정도 추가 공간 필요)
-
처음 index를 생성하는데 많은 시간이 소요
-
데이터의 변경 작업(insert, update, delete)이 자주 일어나는 경우 오히려 성능 저하가 일어날 수 있음
Index의 종류
- 클러스터형 인덱스 (clustered index)
-
특정 나열된 데이터들을 일정 기준으로 정렬해주는 인덱스
-
클러스터형 인덱스 생성 시 데이터 페이지 전체가 다시 정렬 >> 이미 대용량의 데이터가 입력된 상태라면 클러스터형 인덱스 생성시 심각한 부하가 발생
-
테이블당 하나만 생성가능. 어느 열에 클러스터형 인덱스를 생성하는 지에 따라 시스템의 성능이 달라짐
-
보조 인덱스 보다 검색 속도는 더 빠르다 단 입력/수정/삭제는 더 느림
-
MySQL의 경우 Primary Key가 있다면 PK를 클러스터형 인덱스로, 없다면 unique하면서 Not Null인 컬럼을, 그것도 없으면 임의로 보이지 않는 컬럼을 만들어 클러스터형 인덱스로 지정
- 보조 인덱스 (Secondary index)
-
개념적으로 후보키에만 부여 가능한 index (후보키: 주민번호와 같이 각 데이터를 인식할 수 있는 최소한의 고유 식별 속성 집합)
-
보조 인덱스 생성시 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성 (자동 정렬되지 않음)
-
데이터가 위치하는 주소값(RID)
-
클러스터형 인덱스보다 검색 속도는 느리지만 데이터의 입력/수정/삭제 시 성능부하가 적음
-
보조 인덱스는 테이블당 여러 개 생성 가능 (너무 많이 생성시 오히려 성능저하)
Index 생성 전략
-
인덱스는 열 단위에 생성
-
Where 절에서 사용되는 열에 생성
-
Where 절에 사용되는 열이라도 자주 사용해야 가치가 있음
-
데이터 중복도가 높은 열에는 인덱스를 만들어도 효과가 없음(중복도가 낮은 열에 생성)
-
왜래키를 설정한 열에는 자동으로 왜래키 인덱스가 생성됨
-
조인에 자주 사용되는 열에는 인덱스를 생성하는 것이 좋음
-
데이터 변경(삽입,수정,삭제) 작업이 얼마나 자주 일어나는지를 고려해야함
-
클러스터형 인덱스는 테이블당 하나만 생성할 수 있음
-
사용하지 않는 인덱스는 제거
Index 활용
Index 생성
-
create index 문으로 인덱스를 만들면 보조 인덱스가 생성
-
create index 문으로는 클러스터형 인덱스를 만들 수 없으며, 클러스터형 인덱스를 만들려면 alter table을 사용해야함
-
create index 문의 unique 옵션은 고유한 인덱스를 만들 때 사용
-
ASC, DESC로 정렬 방식 지정
-
index_type은 생략 가능하며, 생략할 경우 기본 값인 B-Tree 형식 사용
View
View
-
데이터베이스에 존재하는 '가상의 테이블'
-
실제 행과 열을 가지고 있지만 데이터를 저장하고 있지는 않음
-
테이블처럼 물리적으로 저장되는 것은 아님
-
join이나 subquery와 같이 여러 개의 테이블을 참조하여 데이터를 조회할 때 번거로움을 줄일 수 있음
-
단 MySQL에서 View는 단지 다른 테이블이나 View에 있는 데이터를 보여주는 역할만 수행
-
View와 Table 차이점은, Table은 실질적인 데이터가 있지만 View는 데이터가 없고 SQL만 저장한다
View 장점
-
특정 사용자에게 테이블 전체가 아닌 필요한 필드만 보여줄 수 있음
- DBMS의 사용자별 권한 관리 기능을 통해 사용자가 테이블에 직접적인 접근을 하지 못하도록 막을 수 있음
-
복잡한 쿼리를 단순화해서 사용할 수 있음
-
쿼리를 재사용할 수 있음
-
여러 방법의 데이터 조회에 알맞은 다양한 구조에 데이터 분석 기반을 구축할 구 있음
- 기존 테이블 구조를 변경하지 않음
View 단점
-
삽입, 삭제, 갱신 작업에 많은 제한 사항을 가짐
-
View는 자신만의 인덱스를 가질 수 없음
View data 변경
-
View를 생성한 기존 테이블의 data가 업데이트되면 View의 내용도 update 되는가 -> update 된다!
-
View를 조회하게 되면, 옵티마이저에서 View를 생성할 때 저장해 놓은 Select문이 실행되는 것이기 때문에 View의 data 또한 Update가 된 것 처럼 보임
View 종류
- 단순 뷰(Simple View)
- 하나의 테이블로 생성
- 그룹 함수의 사용이 불가능
- distinct 사용 불가능
- DML 사용 가능
- 복합 뷰(Complex View)
- 여러개의 테이블로 생성(join)
- 그룹 함수의 사용이 가능
- distinct 사용 가능
- DML 사용 불가능
- 인라인 뷰(Inline View)
- 일반적으로 가장 많이 사용
- from 절 안에 SQL문장이 들어가는 것을 인라인 뷰라 할 수 있음
Simple View
- 단순 뷰에서 DML 명령어 사용이 불가능한 경우
- View 정의 시 포함되지 않은 컬럼 중 not null 제약조건이 지정되어 있는 경우
- 산술 표현식을 포함한 컬럼이 포함되어 있는 경우
- distinct를 포함한 경우
- 그룹 함수나 gruop by 절을 포함한 경우
View 생성
- create문 사용
-
select 문에서 선택된 필드를 이용하여 새로운 View를 생성
-
View는 원본 테이블의 이름과 같은 이름을 사용할 수 없음
View 대체
- 설정한 필드를 대체하기 위해서는 새로운 View로 대체해야함
- 뷰이름에 해당하는 View가 존재하면 replace, 존재하지 않으면 Create가 일어 남
View 수정
View 삭제
- drop 사용
View 실습
-- ex1) 사원테이블에서 부서가 90인 사원들을 v_view1으로 뷰테이블을 만드시오
-- (사원ID,사원이름,급여,부서ID만 추가)
create or replace view v_view1
as
select employee_id, last_name, salary,department_id
from employees
where department_id = 90;
select * from v_view1;-- ex2) 사원테이블에서 급여가 5000이상 10000이하인 사원들만 v_view2으로 뷰를 만드시오 --43건
-- (사원ID , 사원이름, 급여, 부서ID)
-- ex3) v_view2 테이블에서 103사원의 급여를 9000.00에서 12000.00으로 수정하시오.
create or replace view v_view2 (사원ID , 사원이름, 급여, 부서ID)
as
select employee_id, last_name, salary, department_id
from employees
where salary between 5000 and 10000;
update v_view2 set 급여 = 12000 where 사원ID = 103;
select * from v_view2; -- ex4)사원테이블과 부서테이블에서 사원번호,사원명,부서명을 v_view3로 뷰테이블을만드시오
-- 조건1) 부서가 10,90인 사원만 표시하시오
-- 조건2) 타이틀은 사원번호, 이름, 부서이름으로 출력하시오
-- 조건3) 사원번호로 오름차순정렬하시오
create or replace view v_view3 (사원번호,이름,부서이름)
as
select employee_id, last_name, department_name
from employees
left join departments using(department_id)
where department_id in (10,90)
order by 1 asc;
select * from v_view3;
show tables;-- ex5) 부서ID가 10,90번 부서인 사원들의 부서 위치를 표시하시오
-- 조건1) v_view4로 뷰테이블을 만드시오
-- 조건2) 타이틀을 사원번호,사원명,급여,입사일,부서명,부서위치(city)로 표시하시오
-- 조건3) 사원번호순으로 오름차순정렬하시오
-- 조건4) 급여는 천단위절삭하고,세자리마다 콤마와 '달러'을 표시하시오
-- 조건5) 입사일은 '2004년 10월 02일' 형식으로 표시하시오
create or replace view v_view4 (사원번호,사원명,급여,입사일,부서명,부서위치)
as
select employee_id, last_name,
concat(format(truncate(salary,-3),0),'달러'),
date_format(hire_date,'%Y년%m월%d일'),
department_name, city
from employees
left join departments using(department_id)
left join locations using (location_id)
where department_id in (10,90)
order by 1 asc;
select * from v_view4;-- ex6)
-- 사원테이블을 가지고 부서별 평균급여를 뷰(v_view5)로 작성하시오
-- 조건1) 반올림해서 1000단위까지 구하시오
-- 조건2) 타이틀은 부서ID,부서평균
-- 조건3) 부서별로 오름차순정렬하시오
-- 조건4) 부서ID가 없는 경우 5000으로 표시하시오
create or replace view v_view5
as
select ifnull(department_id,5000) as '부서ID', round(avg(salary),-3) as '부서평균'
from employees
group by department_id
order by 1 asc;
select * from v_view5;select * from v_view5;
-- -----------------------------------------------------------------
-- ex9)뷰 - 인라인
-- 부서별 최대급여를 받는 사원의 부서명,최대급여를 출력하시오(단, null은 제외)
select department_name, max(salary)
from (select department_id ,department_name
from departments) d
left join employees using(department_id)
group by department_id
having max(salary) is not null;-- ex10) Top N분석
-- 급여를 가장많이 받는 사원3명의 이름,급여를 표시하시오
select last_name, salary
from (select last_name, salary from employees order by salary desc) e
limit 0,3;-- ex12) 급여의 순위를 내림차순정렬했을때, 3개씩 묶어서 2번째 그룹을 출력하시오
-- (4,5,6 순위의 사원출력 ==> 페이징처리기법)
-- employee_id last_name salary
-- --------------------------------------
-- 145 John 14000.00
-- 146 Karen 13500.00
-- 201 Michael 13000.00
-- 방법1)
select a.*
from (select employee_id, first_name, salary
from employees e
order by salary desc
)a limit 3, 3;
-- 방법2)
select e.employee_id,e.first_name,salary, e.page
from(select tt.*, ceil(@rownum := @rownum + 1/3) as page
from (select * from employees, (select @rownum := 0) tmp order by salary desc) tt) e
where page=2; -- ex13) 사원들의 연봉을 구한후 최하위 연봉자 5명을 추출하시오
-- 조건1) 연봉 = 급여*12+(급여*12*커미션)
-- 조건2) 타이틀은 사원이름 , 부서명, 연봉
-- 조건3) 연봉은 $25,000 형식으로 하시오
-- 사원이름 부서명 연봉
-- -------------------------------
-- Olson Shipping $25,200
-- Markle Shipping $26,400
-- Philtanker Shipping $26,400
-- Gee Shipping $28,800
-- Landry Shipping $28,800
select d.last_name as "사원이름", d.department_name as "부서명", d.totsal as "연봉"
from (select last_name, department_name,
concat('$',format((salary*12+(salary*12* ifnull(commission_pct, 0))),0))
as totsal
from employees
left join departments using(department_id)
order by salary asc
)d limit 0,5;