[DATABASE] ALTER - 테이블 속성 설정 (+ 제약조건, view)

< 이전 포스팅 정리 >

[DATBASE] DML(SELECT 서브쿼리) + DDL(CREATE/ALTER/DROP)

 

데이터베이스화
쇼핑몰
고객, 상품, 주문
- 저장
ERP:offline => online(프로그램)
- 검색
=> KM
=>경영/ 경제(이론) => 구현(IT)
Extended ERP
CRM
SCM
SEM

=>Big DATA

 

DDL

create database

drop database

create table(*)

열이름 자료형(크기)

           옵션 not null / default

alter table

컬럼 정의 

        옵션 => constraint

drop table

 

DML

select(*)

insert

update

delete

---

제약조건(contraint)

=> 무결성 검사

=> 입력 데이터 검사

 

1. 필수 입력 -> not null

2. 중복 방지 -> unique

3. 필수입력 + 중복방지 -> primary key (주키)

     테이블 당 1개

      => 사원번호

    + auto_increment(sequence)

      => 자동 증가

      => 정수 타입 데이터 형태

4. primary key를 참조 -> foreign key (참조키, 외래키)

5. 값에 대한 검사 -> check  (mariadb는 사용하지 않음)

 

기술 방법 (효과 같음)

컬럼 단위 제약조건 

    create table 테이블명(

           열이름 자료형(크기) 제약조건기술, 

           열이름 자료형(크기) 제약조건기술, 

            ..

    )

 

 테이블 단위 제약조건

    create table 테이블명(

           열이름 자료형(크기),

           열이름 자료형(크기)

           .. ,

            제약조건기술, 

            제약조건기술

    )

 

 

* 테이블의 제약조건을 확인

제약조건 테이블 실행 명령어

> source c:\mariadb\(sql파일이름 적기)           //내 파일경로에 있는 sql파일을 source로 실행시키면 된다.

select constraint_name, table_schema, table_name, constraint_type

from information_schema.table_constraints

where constraint_schema='sample';      //sample은 데이터베이스 이름

 

테이블의 제약조건을 확인할 수 있도록 표시

 

 

* null과 공백의 차이 (테이블을 만들 때 null로 설정해주어야 null이 들어감. 공백과는 다름)

 

 

[unique]

* 테이블 생성 시 unique 지정 (컬럼단위)

> create table dept_u1 (
    -> deptno int(2) unique,
    -> dname varchar(14),
    -> loc varchar(13));

unique로 컬럼 지정

같은 값의 데이터를 넣으려고 하면 에러가 뜸

 

mariadb는 null에 대해서 unique라도 중복값이 가능하다.

 

* 테이블 생성 시 unique 지정 (테이블 단위)

> create table dept_u2(
    -> deptno int(2),
    -> dname varchar(14),
    -> loc varchar(13),
    -> constraint unique(deptno));

결과

 

[primary key] - 주 키

* 테이블 생성 시 primart key 지정 (컬럼단위)

> create table dept_p1(
    -> deptno int(2) primary key,
    -> dname varchar(14),
    -> loc varchar(13));

테이블 속성 확인과 타입 관계성확인

 

* 테이블 생성 시 primart key 지정 (테이블단위)

> create table dept_p2(
    -> deptno int(2),
    -> dname varchar(14),
    -> loc varchar(13),
    -> constraint primary key(deptno));

관계성 확인

 

insert시 null과 중복값이 삽입이 안되는 것을 확인가능

 

[auto_increment]  - 자동증가

primary key를 넣지 않고 auto_increment를 주었을 때 에러

 

> create table dept_a1(
    -> deptno int(2) primary key auto_increment,
    -> dname varchar(14),
    -> loc varchar(13));

auto_increment추가 확인

auto_increment되는 타입을 unsigned로 지정하면 더 많은 범위를 삽입 가능하다

 

자동증가 결과

중간에 다른 데이터 넣었을 때의 자동증가 변화

=> auto_increment는 제일 큰 숫자를 기준으로 + 된다.

 

 

[foreign key] - 외래키

=> 외래키를 생성할 때는 references로 항상 참조할 테이블의 primary key를 지정해야한다 (= 기본 테이블에는 primary key가 있어야 한다.)

 

> create table dept_p(
    -> deptno int(2) primary key,
    -> dname varchar(14),
    -> loc varchar(13));

 

> create table emp_f(
    -> empno int(4),
    -> ename varchar(10),
    -> job varchar(9),
    -> deptno int(2),
    -> constraint foreign key(deptno) references dept_p(deptno));

foreign key를 이용하여 참조하기

emp_f(참조 테이블)의 속성확인 (Key에 MUL이 찍힘)

 

테이블 type확인

 

* 테이블에 데이터 삽입하기

=> 기본 테이블(primary key가 있는)에 데이터를 삽입 후 해당 데이터와 연결할 수 있는 데이터를 참조 테이블에 삽입하는 순서로 진행해야 에러가 나지 않는다.

참조테이블에 그냥 데이터를 넣으면 에러가 뜸

기본 테이블에 insert후 primary key값과 연결하여 삽입하면 에러가 뜨지 않음

 

=> 참조 중일때는 기본 테이블 값의 변경/삭제를 할 수 없다.

 

[check] - 값에 대한 결과

> create table dept_c1(
    -> deptno int(2) not null,
    -> dname varchar(14),
    -> loc varchar(13),
    -> check(deptno >=30));

30이상만 insert 될 수 있도록 함

 

 

복합키 -집합

=> 두 개 이상의 컬럼을 묶어서 키를 생성

=> primary key가 2개가 아닌 2개 묶어서 하나의 primary key로 인식해야한다.

 

> create table order_p(
    -> pcode int(4),
    -> ccode int(4),
    -> orderdate datetime,
    -> etc varchar(20),
    -> constraint primary key(pcode,ccode));

 

primary key가 복합적으로 쌍을 이룸

 

1000,1000값을 중복으로 넣었을 때 1000-1000의 쌍을 이루어 중복에러가 뜨게 된다.

 

테이블을 만들  때 무조건 부여해야하는 것.

1. not null

2. pk

 

---

 

 

null / not null 속성 추가/삭제

> alter table dept modify deptno int(2) not null;     =>not null

> alter table dept modify deptno int(2);                   => null

위 ( not null) / 아래 (null)

null값 insert 후 not null변경 불가

 

unique 추가

 > alter table dept add constraint unique(deptno);

 

unique 속성 삭제

 > alter table 테이블이름 drop constraint 속성이름;

 

제약조건 변경 시 constraint_name과 table_name의 위치

 

primary key 추가

> alter table dept add constraint primary key(deptno);

 

primary ket 속성 삭제

> alter table dept drop constraint primary key;

 

 

참조 테이블 확인 (FOREIGN KEY 확인)

삭제 전 속성 테이블

 

참조테이블을 삭제할 때,

> alter table emp drop foreign key emp_ibfk_1;           //속성 명

삭제 후 속성 테이블

참조를 삭제했지만 인덱스가 남아있기 때문에 alter 명령어로 인덱스 완전 삭제

 

 

check 제약조건 추가

> alter table dept add constraint check(deptno);

dept테이블 deptno에 check 속성 추가

 

check 삭제

> alter table dept drop constraint CONSTRAINT_1;

dept테이블의 check제약조건을 삭제 후 확인

 

 

---

 

테이블 간의 관계에 의해서 데이터 조회

- 관계형 데이터베이스

pk, fk

모델링을 통해서 디자인 => ERD(Entity-relationship diagram)

디자인      코딩

엔터티      테이블

속성         컬럼

관계         키

 

Diagram

            CA ERWin

MySQL Workbench

수동

https://ko.exerd.com/index.do#intro

eXERD

https://www.erdcloud.com/ 

ERDCloud

 

고객/주문정보 ERD

- 주문과 주문상세정보는 일대일 관계

- 고객정보 없이 주문이 들어갈 수 없으니 일대다 관계

- 상품정보 없이 주문이 있을 수 없으니 일대다 관계

 

 

E-R 다이어그램

• 개체(Entity) - 사각형으로 표현, 사각형의 상단에 개체의 이름을 기술.

 

• 속성(Attribute) - 사각형 내부에 기술, 사각형의 위 칸에는 기본키 속성, 아래 칸에는 일반 속성 기술

 

• 관계(Relationship) - 개체 간에 실선 또는 점선 표시

1. 실선인 경우 -> 식별 관계 (Identifying) : 카디널리티가 1쪽인 개체의 기본키 속성이 M쪽인 사원 개체의 외래키로 구현되는 과정에서 사원의 기본키 속성으로 추가.
2. 점선인 경우 -> 비식별 관계(Non-Identifying) : 카디널리티가 1쪽인 개체의 기본키 속성이 M쪽인 사원 개체의 외래키로 구현되는 과정에서 일반 속성으로 추가.

• 까마귀 발(Crow's foot) - 한 개의 부서에 여러 명의 사원이 소속되는 관계
• O기호 - 선택도. O이 붙어있는 경우 (선택) / O이 붙어있지 않은 경우 (필수)  
• 사원 개체 쪽에 O이 있다면 각 부서에 사원이 한 명도 배정되지 않을 수도 있다는 의미
• 부서 개체 쪽에 O이 있다면 사원 중에서 어떠한 부서에도 소속되지 않은 사원이 있을 수 있음을 의미

 

---

 

VIEW

- select문을 저장한 이름

 

create view 뷰이름 as select문

 

뷰이름 - 가상 테이블처럼 활용 가능

           - view에는 데이터가 실제로는 없음

 

*뷰 생성

> create view emp_vu1
    -> as
    -> select*from emp;

생성한 view확인

* table 타입으로 view 확인

> show full tables where table_type='view';

 

> show full tables;    

 

 ==  (같은 명령어)

 

  > select table_name, table_type
    -> from information_schema.tables
    -> where table_schema='sample';

 

table name과 table type을 확인

> show create view emp_vy1 \G           //view 확인

 

*뷰 삭제

> drop view emp_vu1;

 

 

 

* 부서번호가 10인 데이터에 대한 view만들기

 > create view emp_vu_10
    -> as
    -> select empno,ename,mgr,job
    -> from emp where deptno=10;

 

view 속성 확인

view 내용 확인

 

* 필드의 이름을 arias로 변경

> create view emp_vu_20
    -> as
    -> select empno no, ename name,mgr,job
    -> from emp
    -> where deptno=20;

view의 속성확인

view의 내용 확인

- 이름과 내용의 범위를 변경해서 view를 만들었을 때, 보안의 효과가 증대될 수 있다.

 

* 사원번호,사원이름, 급여 , 연봉정보를 조회할 수 있는 emp_sal이라는 view생성

 > create view emp_sal
    -> as
    -> select empno,ename,sal,sal*12+ifnull(comm,0) ansal
    -> from emp;

 

view(emp_sal)의 속성

view(emp_sal)의 내용

 

 

* 사원번호, 사원이름, 부서번호, 부서이름, 부서위치의 정보를 조회할 수 있는 emp_dept이라는 view생성

> create view emp_dept
    -> as
    -> select e.empno, e.ename,d.deptno,d.dname,d.loc
    -> from emp e inner join dept d
    -> on(e.deptno=d.deptno);

view(emp_dept)의 속성

view(emp_dept)의 내용

---

> show create view emp_vy1 \G     해당 명령어 결과물에 대한 분석이 필요

View: emp_vu_10
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`

SQL SECURITY DEFINER VIEW `emp_vu_10`

AS select `emp`.`empno`

AS `empno`,`emp`.`ename`

AS `ename`,`emp`.`mgr`

AS `mgr`,`emp`.`job`

AS `job` from `emp` where `emp`.`deptno` = 20
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci