Day35 Database

22/12/13

📌 9. 데이터정의어(DDL) - Table

• <SQL구문의 유형>
  데이터질의어(DQL) : select
  데이터조작어(DML) : insert, update, delete
  데이터정의어(DDL) : create, alter, drop, truncate
  데이터제어어(DCL) : grant, revoke
  트랜잭션제어어(TCL) : commit, rollback, savepoint

---

📂 테이블 수정 (Alter table)

✅ alter table 테이블명 add ---;     : 컬럼 추가, 제약조건 추가
✅ alter table 테이블명 modify ---; : 컬럼 수정, 제약조건 추가
✅ alter table 테이블명 drop ---;    : 컬럼 삭제, 제약조건 삭제
✅ alter table 테이블명 rename ---; : 컬럼명 수정

✅ 컬럼 추가 : alter table 테이블명 add ---;

use hr;
desc dept80;
select*
from dept80;

🔸 (예제1) dept80 테이블에 job_id 컬럼 추가

alter table dept80 
add job_id varchar(9);

• 새롭게 추가된 컬럼은 기본적으로 마지막 컬럼으로 추가됨
  

• 새롭게 추가된 컬럼에는 기본적으로 null값이 저장됨
  

🔸 (예제2) dept80 테이블에 default 값이 있는 email 컬럼 추가

alter table dept80
add email varchar(30) default '미입력';

desc dept80;

• 새롭게 추가된 컬럼에 defaul값 저장됨.
  

🔸 (예제3) 첫번째 컬럼으로 추가하기

alter table dept80
add emp_number int first;

🔸 (예제4) 특정 컬럼 다음 컬럼으로 추가하기

alter table dept80
add salary int default 300 not null after last_name;

🔸 (예제5) not null 제약조건과 함께 컬럼 추가하기

alter table dept80
add salary1 int not null after last_name;
-- last_name 컬럼 뒤에 컬럼 추가됨.
desc dept80;

• 자동으로 null 값 대신 0이 적용됨.

select*
from dept80;

---

✅ 칼럼 수정 : alter table 테이블명 modify ---;
: 데이터타입, 컬럼 사이즈, default값, not null 제약조건 등 수정 가능함.

🔸 (예제1) salary 컬럼의 데이터타입(int->bigint), default값 삭제, not null삭제로 변경하는 작업

alter table dept80 
modify salary bigint;

desc dept80;

🔸 (예제2) last_name 컬럼의 컬럼사이즈(25->30) 변경, not null 유지하는 작업

alter table dept80
modify last_name varchar(30) not null;

🔸 (예제3) salary 컬럼에 default값, not null 추가하기

alter table dept80
modify salary bigint default 500 not null; 

---

✅ 컬럼명 변경 : alter table 테이블명 rename column 기존컬럼명 to 새컬럼명;

alter table dept80
rename column hire_date to start_date;

desc dept80;

---

✅ 컬럼 삭제 : alter table 테이블명 drop ---;

alter table dept80
drop emp_number;

✅ 제약조건 추가

• pk, uk, ck, fk 제약조건 추가 : alter table 테이블명 add ---;
• not null 제약조건 추가 : alter table 테이블명 modify ---;

🔹 primary key 제약조건 추가하기

alter table dept80
add primary key(employee_id);

🔹 unique 제약조건 추가하기

alter table dept80
add unique(job_id);

🔹 check 제약조건 추가하기

alter table dept80
add check (salary>100);

🔹 foreign key 제약조건 추가하기

alter table dept80
add mgr_id int default 150;

alter table dept80
add foreign key(mgr_id) references dept80(employee_id);

🔹 not null 제약조건 추가

alter table dept80
modify annsal double(22,0) not null; 

desc dept80;

✅ 제약조건 삭제

• pk, uk, ck, fk 제약조건 : alter table 테이블명 drop ---;
• not null 제약조건 : alter table 테이블명 modify ---;

< fk, ck, uk 제약조건 삭제시 제약조건명 알아야함 >

use information_schema;

select*
from table_constraints
where table_name = 'dept80';

🔸 foreign key 제약조건 삭제하기

use hr;

alter table dept80
drop foreign key dept80_ibfk_1;

🔸 primary key 제약조건 삭제하기

alter table dept80
drop primary key;

• 작업하고 information_schema 다시 열어보니까 fk,pk없어짐

use information_schema;

select*
from table_constraints
where table_name = 'dept80';

🔸 not null 제약조건 삭제하기

use hr;

alter table dept80
modify annsal double(22,0) null;	-- null을 안적어도 삭제하는것

desc dept80;

---

📍 [정리] alter table 구문

✅ alter table 테이블명 add ---;     : 컬럼 추가, 제약조건 추가(pk, fk, uk, ck)
✅ alter table 테이블명 modify ---; : 컬럼 수정, 제약조건 추가/삭제(nn)
✅ alter table 테이블명 drop ---;    : 컬럼 삭제, 제약조건 삭제(pk, fk, uk, ck)
✅ alter table 테이블명 rename ---; : 컬럼명 수정

---

📂 테이블 삭제 (Drop table)

: DB에서 테이블 삭제(테이블구조, 데이터, 제약조건 등)하는 명령어

drop table dept80;
desc dept80;

---

📂테이블 절단 (Truncate table)

: 테이블의 구조만 남고 데이터가 모두 삭제되는 명령어. 즉, 테이블의 데이터가 비워지는 명령어.

select *
from copy_dept;   -- 32개의 행이 저장된 테이블

truncate table copy_dept; -- 모든 행 삭제됨.
-- (==) delete from copy_dept;

select *
from copy_dept;   -- 0개의 행이 조회됨.

---

📌 10. 데이터정의어(DDL) - View

: 뷰(view)를 생성/수정/삭제하는 명령어(DDL) : create view, alter view, drop view

• 뷰(view)란?
  : 하나 이상의 테이블을 기반으로 생성은 되었으나 물리적으로 존재하지 않고, DB 사전에 정의만 되어있는 가상의 논리적인 테이블
• 뷰 사용 목적 : 보안성, 공간 효율성, 편의성

📂 뷰 생성 : create view

[문법] create view 뷰명
        as select 컬럼명1, 컬럼명2
        from 테이블명 ---> 뷰의 base table
        where 조건문;

사용방법 : 테이블과 동일하게 사용된다.

🔸 (예제1) empvu80뷰 데이터 생성

create view empvu80
as select employee_id, last_name, salary, department_id
    from employees
    where department_id = 80;
    
desc empvu80;

select*
from empvu80;

🔸 (예제2) deptvu뷰 생성 및 데이터 조작

create view deptvu
as select*
	from departments
    where department_id > 200;
    
-- 뷰 구조 조회
desc deptvu;

-- 뷰 데이터 검색(연계된 departments 테이블에서 데이터 검색됨)
select*
from deptvu;

• 뷰를 통한 데이터 조작(연계된 departments 테이블에 데이터 삽입됨)

insert into deptvu
values(340,'AAA',null,1700);

• 뷰에서 삽입 결과 확인(연계된 departments 테이블에서 데이터 검색됨)

select*
from deptvu;

• base table(departments)에서 삽입 결과 확인

select*
from departments;

🔸 (예제3) 보안성 예제

use shopdb;

create view member_vu
as select member_id, member_name, birth, job
   from members;
   
desc member_vu;

select *
from member_vu;

🔸 (예제4) 단순성 예제

use hr;

create view dept_sal_vu
as select d.department_name, sum(e.salary) as "급여 합계", 
		 avg(e.salary) as "급여 평균", 
         min(e.salary) as "최소 급여", max(e.salary) as "최대 급여"
 from employees e join departments d
 on e.department_id = d.department_id
 group by d.department_name
 order by d.department_name;
 
desc dept_sal_vu;

select *
from dept_sal_vu;

---

📂 뷰 수정 : alter view

alter view empvu80
as select employee_id, last_name, salary, email, department_id
	from employees
    where department_id = 80;

desc empvu80;

select*
from empvu80;

---

📂 뷰 삭제 : drop view

drop view empvu80;

desc empvu80;	-- 작업안됨!

select*
from empvu80;	-- 작업안됨!

💥 뷰를 삭제하더라도 base table에 미치는 영향은 없음!

---

📂 DB 사전으로부터 view 정보 조회

use information_schema;

show tables;

select *
from views
where table_schema = 'hr';