[DB] Unique, not null, check 조건, default, union, join

* Unique

- 컬럼에 중복값을 허용하지 않는다.

- null을 허용한다. (null은 중복체크 하지 않는다.)

- 인덱스가 자동생성 된다.

- 문법

+ 컬럼 단위

create table 테이블명(

컬럼명 데이터형(크기) constraint 제약사항명 unique,

...

);

+ 테이블 단위

create table 테이블명(

컬럼명 데이터형(크기),

...,

constraint 제약사항명 unique(적용컬럼),

...

);

[참고] 제약사항명은 'uq_컬럼명'으로 지정한다.

----------------------------------------

-- unique

----------------------------------------

 

----------------------------------------

-- *컬럼 단위

create table column_unique(

name varchar2(15byte),

age number(3),

phone varchar2(13byte) constraint uq_phone unique,

address varchar2(100byte)

);

 

select * from user_constraints;

 

-- *위 테이블에 데이터 추가 성공 예제

-- 1. 최초로 데이터 입력

insert into column_unique(name, age, phone, address) values ('이진기', 26, '010-1989-1214', '광명');

select * from column_unique;

-- 2. 전화번호가 다른 경우(unique 컬럼이 중복 되지 않을 때)

insert into column_unique(name, age, phone, address) values ('이진기', 26, '011-1989-1214', '광명');

-- 3. 전화번호가 없는 경우(null값이 입력되는 경우 : ''or 컬럼 생략)

insert into column_unique(name, age, address) values ('김종현', 25, '동대문구');

insert into column_unique(name, age, phone, address) values ('김기범', 24, '', '대구');

 

 

-- * 위 테이블에 데이터 추가 실패 예제

-- 유니크로 설정한 컬럼에 동일한 값이 추가 될 때

insert into column_unique(name, age, phone, address) values ('최민호', 24, '011-1989-1214', '인천');

-- └error msg : 무결성 제약 조건(SCOTT.UQ_PHONE)에 위배됩니다

 

commit;

select * from user_indexes;

----------------------------------------

 

 

* not null

- 컬럼에 null을 허용하지 않을 때 사용한다.

- 문법

+ 컬럼 단위

create table 테이블명(

컬럼명 데이터형(크기) not null,

...

);

 

* check 조건

- 컬럼의 값을 원하는 범위로만 입력 받을 때 체크 조건을 쓰게 된다.

- 조건이 정의된 컬럼만 검사할 수 있다.

- 문법

+ 컬럼 단위

create table 테이블명(

컬럼명 데이터형(크기) check (검사할 컬럼명 연산자 조건)

...

);

 

* default 

- 컬럼에 null이 들어가면 설정된 값으로 추가하는 제약사항이다.

- 컬럼 단위로만 설정이 가능하다.

- 제약사항명을 설정할 수 있다.

- 문법

 + 컬럼 단위

create table 테이블명(

컬럼명 데이터형(크기) default 들어갈 값,

...

);

----------------------------------------

-- not null, check, default

----------------------------------------

 

create table other_constraint(

name varchar2(15 byte) not null,

age number(3) check (age between 20 and 40),

address varchar2(100 byte) null,

gender char(1 byte) check (gender='M' or gender='F'),

hiredate date default sysdate

);

 

select * from user_constraints;

 

-- *위 테이블에 데이터 추가 성공 예제

-- 1. 문) 이름이 반드시 입력되고, 나이는 20~40대 사이이며, 성별은 M 또는 F, 입력일은 추가하지 않아도 된다.

insert into other_constraint(name, age, address, gender, hiredate) values ('이태민', 22, '서울시', 'M', sysdate);

select * from other_constraint;

 

-- 2. default가 설정되어 있기 때문에 입력일을 넣지 않아도 추가 되는 시점의 날짜가 들어간다.

insert into other_constraint(name, age, address, gender) values ('최민호', 24, '인천', 'M');

 

 

-- *위 테이블에 데이터 추가 실패 예제

-- 1. not null 조건(이름에 null 입력시)

insert into other_constraint(name, age, address, gender, hiredate) values ('', 22, '서울시', 'M', sysdate);

-- └error msg : NULL을 ("SCOTT"."OTHER_CONSTRAINT"."NAME") 안에 삽입할 수 없습니다

-- not null조건은 제약사항명을 따로 명시하지 않았음에도 불구하고 명확하게 나오기 때문에 굳이 제약사항명을 명명하지 않는다.

 

-- 2. 나이가 20~40 사이가 아닐 때

insert into other_constraint(name, age, address, gender, hiredate) values ('이태민', 19, '서울시', 'M', sysdate);

-- └error msg : 체크 제약조건(SCOTT.SYS_C0011210)이 위배되었습니다

 

-- 3. 성별이 m 또는 f가 아닌 값일 때

insert into other_constraint(name, age, address, gender, hiredate) values ('홍길동', 18, '한양', 'T', sysdate);

-- └error msg : 체크 제약조건(SCOTT.SYS_C0011211)이 위배되었습니다  

----------------------------------------

 

 

* 테이블을 합쳐서 데이터 조회 (union, join)

union : 테이블은 다르지만 컬럼의 정보가 같을 때

- 중복데이터를 모두 가져오는 union all과 중복데이터를 가져오지 않는 union 두가지가 있다.

- 문법

select 컬럼명,,,

from 테이블명

...

union [all]

select 컬럼명,,,

from 테이블명

...

- 컬럼의 이름은 달라도 된다. 하지만 이때 컬럼의 개수, 데이터형은 반드시 일치하여야 한다.

----------------------------------------

-- union

----------------------------------------

 

create table union_a as(

select empno, ename, job, hiredate, deptno

from emp

where deptno=10

);

 

create table union_b as(

select empno, ename, mgr, job, hiredate, deptno

from emp

where deptno=30

);

 

insert into union_b(empno, ename, mgr, job, hiredate, deptno) (

select empno, ename, mgr, job, hiredate, deptno

from emp

where deptno=10

);

 

select * from union_a;

select * from union_b;

 

 

-- union은 중복 데이터를 줄력하지 않는다.

select empno, ename, job, hiredate, deptno

from union_a

union

select empno, ename, job, hiredate, deptno

from union_b;

 

-- union all은 모든 데이터를 출력한다.

select empno, ename, job, hiredate, deptno

from union_a

union all

select empno, ename, job, hiredate, deptno

from union_b;

 

 

-- 컬럼의 개수가 일치하지 않으면 에러

select empno, ename, job, hiredate

from union_a

union

select empno, ename, job, hiredate, deptno

from union_b;

-- └error msg : 질의 블록은 부정확한 수의 결과 열을 가지고 있습니다.

 

-- 데이터형이 일치하지 않으면 에러

select ename, empno, job, hiredate, deptno

from union_a

union all

select empno, ename, job, hiredate, deptno

from union_b;

-- └error msg : 대응하는 식과 같은 데이터 유형이어야 합니다

 

-- 컬럼의 이름이 달라도 데이터형이 갇다면 출력한다.(주의할 것!!! 현재 job과 ename이 바뀌어서 출력됨)

-- view ex) job - 영희, 철수, 길동... / ename - 사원, 대리, 부장, 과장...

select  empno, job, ename, hiredate, deptno

from union_a

union all

select empno, ename, job, hiredate, deptno

from union_b;

 

 

join : 테이블과 컬럼의 정보가 다를 때 데이터를 가져오는 쿼리

- inner join, outer join, self join, cross join으로 나뉘어 진다.

- 서로 다른 테이블에 값이 같을 때 사용하는 eque-join과 서로 다른 테이블에 값이 다른 것을 가져오는 non-eque-join이 있다.

- 오라클은 ANSI와 oracle join 두가지를 사용할 수 있다.

 

+ inner join

- 컬럼의 값이 양쪽 테이블에 있는 것만 조회한다.

- 한쪽 테이블에만 데이터가 있다면 해당 레코드는 조회하지 않으므로 모든 레코드를 볼 수 없다.

- driving table의 선정이 조회 속도에 영향을 끼친다.

└driving table : 조회시 기준값으로 사용될 테이블을 이야기한다. 기본키가 존재하거나 레코드가 적은 테이블이 되는 것이 좋다.

- 문법

-ANSI

     select 컬럼명,,,테이블명.컬럼명,,,

from테이블명

...

inner join 조인할 테이블명

on 조인조건(driving table설정)

select

...

- ex) [emp 사원번호, 사원명, [ (부서번호) ] 부서명, 위치 dept] 를 조회하라

select empno, ename, dept.deptno, dname, loc

from dept alias

inner join emp alias

on emp.deptno = dept.deptno

----------------------------------------

-- join

----------------------------------------

 

----------------------------------------

-- inner join

 

-- 문) 사원번호, 사원명, 입사일, 부서번호, 부서명, 위치를 조회하라

select empno, ename, hiredate, ee depteno, dname, loc

from dept d

inner join emp e

on dept.deptno= emp.deptno;

 

-- 문) 입사년도가 1981년인 사원의 사원번호, 사원명, 입사일, 직무, 부서번호, 부서명, 위치를 조회하라

select e.empno, e.ename, e.hiredate, e.job, d.deptno, d.dname, d.loc

from emp e

inner join dept d

on d.deptno = e.deptno

where to_char(e.hiredate, 'yyyy') = '1981';