[22.12.13] 36일차 [데이터베이스] 테이블 수정, 테이블 삭제, 테이블 절단, DDL(뷰)

W·2022년 12월 13일
데이터베이스
0

국비

목록 보기
52/119

테이블 수정 : alter table

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

컬럼 추가

alter table 테이블명 add ---;

  • dept80 테이블에 job_id 컬럼 추가
alter table dept80
add job_id varchar(9);

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

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

  • dept80 테이블에 default값이 있는 email 컬럼 추가

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

desc dept80;

select *
from dept80;

  • 가장 앞에 또는 특정 컬럼 뒤에 컬럼 추가하는 방법
alter table dept80
add emp_number int first;

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

  • not null 제약조건과 함께 컬럼 추가하기
alter table dept80
add salary1 int not null after last_name;

desc dept80;

자동으로 null값 대신 0이 저장됨

select *
from dept80;

컬럼 수정

alter table 테이블명 modify ---;

데이터타입, 컬럼 사이즈, default값, not null 제약조건

  • salary 컬럼의 데이터 타입(int -> bigint), default값 삭제, not null 삭제로 변경하는 작업
alter table dept80
modify salary bigint;

desc dept80;


- bigint로 바뀌면서 default값도 사라짐

  • last_name 컬럼의 컬럼 사이즈(25->30) 변경하는 작업
alter table dept80
modify last_name varchar(30) not null;

desc dept80;

alter table dept80
modify salary bigint default 500 not null;

desc dept80;


- 저장된 default값이 변경되는게 아니라 변경 이후 데이터 삽입 시부터 적용됨.

컬럼명 변경

alter table 테이블명 rename column 기존컬럼명 to 새컬럼명;

alter table dept80
rename column hire_date to start_date;

컬럼 삭제

alter table 테이블명 drop---;

alter table dept80
drop emp_number;

desc dept80;

제약조건 추가

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

  • not null 제약조건 추가 : alter table 테이블명 modify ---;

  • primary key 제약조건 추가하기

alter table dept80
add primary key(employee_id);

desc dept80;

  • unique 제약조건 추가하기
alter table dept80
add unique(job_id);

desc dept80;

  • 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);

desc dept80;

  • 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;

use information_schema;

select *
from table_constraints
where table_name = 'dept80';

foreign key 제약조건 삭제됨

  • primary key 제약조건 삭제하기
use hr;

alter table dept80
drop primary key;
-- 자식이 있는 pk는 삭제 안됨.
-- fk부터 삭제
desc dept80;

  • check 제약조건 삭제 문법
    alter table 테이블명
    drop check check제약조건명;

  • unique 제약조건 삭제 문법
    alter table 테이블명
    drop index unique제약조건명;

  • not null 제약조건 삭제 문법

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

[정리] 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;

truncate table copy_dept;
(==)
delete from copy_dept;

select *
from copy_dept;

delete 구문은 where절 작성 가능

데이터정의어(DDL)

View

  • Database 객체(object) 종류 : table, view, index, function 등...
  • 뷰(view)를 생성/수정/삭제하는 명령어(DDL) : create view, alter view, drop view ...
  • 뷰(view)란?
  • 하나 이상의 테이블을 기반으로 생성은 되었으나 물리적으로 존재하지 않고 DB 사전에 정의만 되어 있는 가상의 논리적인 테이블
  • 뷰 사용 목적 : 보안성, 공간 효율성, 편의성

뷰 생성

  • create view

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

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

desc empvu80;

select *
from empvu80;

create view deptvu
as select *
from departments
where department_id > 200; 

desc deptvu;

select *
from empvu80;

연계된 employees 테이블에서 데이터가 검색됨.

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

select *
from deptvu;

select *
from departments;


- 뷰에 삽입해도 departments 테이블에도 같이 삽입됨

보안성 예제

use shopdb;

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

desc member_vu;

select *
from member_vu;

단순성 예제

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;


- 계속 긴 구문 써서 조회하기 어렵기 때문에 view 테이블 만들어 놓음

select *
from dept_sal_vu;

  • 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;

  • view 삭제
drop view empvu80;

select *
from empvu80;

select *
from employees;


- 뷰를 삭제해도 base table에 미치는 영향은 없다

  • View 정보 조회
use information_schema;
show tables; 

select *
from views
where table_schema = 'hr';

profile
W
이전 포스트

[22.12.13] 36일차 [프론트엔드] 자바스크립트

다음 포스트

[22.12.14] 37일차 [리눅스] 오라클 DBCA 생성, DB 접속하기, EM

0개의 댓글