Day 23 - SQL 문법 pt.2 (제약 조건) 그리고 JAVA web 페이지 작성

SQL Developer

• SQL Developer 20.2
  https://www.oracle.com/tools/downloads/sqldev-downloads-202.html

SQL 문법

무결성 제약 조건

• NOT NULL : null을 허용하지 않는다.

create table lec02( 
    num number not null,
    name varchar(2) not null, 
    nalja date not null 
);

• UNIQUE : 중복된 값을 허용하지 않는다. (null은 중복적을 적용 가능)

create table lec03( 
    idx number, 
    num number unique, 
    name varchar2(5) unique 
);

• PRIMARY KEY : null을 허용하지 않고 중복된 값을 허용하지 않는다.
  • 테이블에는 primary key를 1개만 포함시킬 수 있다.

  create table lec04(
      idx number primary key,
      num number unique not null,
      name varchar2(5)
  );

• FORIEGN KEY : 참조되는 테이블의 칼럼의 값이 존재하면 허용한다.

create table loc02( 
    idx number primary key, 
    loc varchar2(6) not null 
); 

create table emp02( 
    sabun number primary key, 
    name varchar2(6) not null, 
    idx number references loc02(idx) 
); 

insert into loc02 values (1, '부산'); 
insert into emp02 values (1111, 'test', 1);

• CHECK : 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용한다.

create table lec06( 
    num number primary key, 
    name varchar2(6) not null, 
    age number(2) check (age between 1 and 80) 
);
create table lec05( 
    num number primary key, 
    name varchar2(6) not null, 
    gender char(1) check (gender in ('m', 'w')) 
);

create table lec07(
    num number primary key,
    sub varchar2(12) default '제목없음',
    nalja date default sysdate,
    su number default 100
);

제약조건명 지정하기

• 명명규척 : [테이블명]_[칼럼명]_[제약조건명]

create table emp05(
    empno number(4) constraint EMP05_EMPNO_PK primary key,
    ename varchar2(10) constraint EMP05_ENAME_NN not null,
    job varchar2(9) constraint EMP05_JOB_UK unique,
    deptno number(4) constraint EMP05_DEPTNO_FK references dept(deptno)
);

create table emp04(
    empno number(4),
    ename varchar2(10) not null,
    job varchar2(9),
    deptno number(4),
    primary key(empno),
    unique(job),
    foriegn key(deptno) references dept(deptno)
);

create table emp03(
    empno number(4),
    ename varchar2(10) constraint EMP03_ENAME_NN not null,
    job varchar2(9),
    deptno number(4),
    constraint EMP03_EMPNO_PK primary key(empno),
    constraint EMP03_JOB_UK unique(job),
    constraint EMP03_DEPTNO_FK foreign key(deptno) references dept(deptno)
);

제약 조건 변경하기

제약 조건 비활성화 하기

alter table emp02 disable constraint \[제약조건이름\];
alter table emp02 enable constraint \[제약조건이름\];

트랜잭션 관리

commit

수행한 작업들을 메모리에 저장한다.

rollback

commit 이전 시점으로 돌아간다.

Join

한 개 이상의 테이블에서 언하는 결과를 얻기 위한 기능

Oracle Join

Cross Join

두 개의 테이블을 무작위적으로 join 한다.

select * from emp11, dept11;

Equi Join

select * from emp11, dept11 where emp11.deptno = dept11.deptno;

select empno, name, loc from emp11, dept11 where emp11.deptno = dept11.deptno;

Non-Equi Join

select empno, name, loc from emp11, dept11 where emp11.deptno != dept11.deptno;

Outer Join

select empno, name, loc from emp11, dept11 where emp11.deptno = dept11.deptno(+);

select empno, name, loc from emp11, dept11 where emp11.deptno(+) = dept11.deptno;

Self Join

select a.empno, a.ename, b.empno, b.ename from emp a, emp b where a.mgr=b.empno

ANSI Join

Cross Join

select * from emp11 CROSS JOIN dept11;

Inner Join

select * from emp11 INNER JOIN dept11 on emp11.deptno=dept11.deptno where emp11.deptno < 2;

select * from emp11 INNER JOIN dept11 on emp11.deptno=dept11.deptno;
select * from emp11 INNER JOIN dept11 using(deptno);

Natural Join

select * from emp11 NATURAL JOIN dept11;

Outer Join

select * from emp11 RIGHT OUTER JOIN dept11 using(deptno);
select * from emp11 LEFT OUTER JOIN dept11 using(deptno);
select * from emp11 OUTER JOIN dept11 using(deptno);
select * from emp11 FULL OUTER JOIN dept11 using(deptno);

select * from emp11 FULL OUTER JOIN dept11 using(deptno) where name is null;
select * from emp11 FULL OUTER JOIN dept11 using(deptno) where loc is null;

Sub Query

• 필드와 테이블과 조건 자리에 sub query가 사용될 수 있다.
• Join 보다 SubQuery를 사용하는게 자원 소모가 더 적다.

select * from (select empno, ename from emp);

select empno, ename, (select sysdate from dual)-hiredate from emp;

select empno, (select avg(sal) from emp) from emp;

select * from emp where sal>(select avg(sal) from emp);

 select * from emp where empno in (select empno from emp where sal <2000);

 select (select (select 'test' from dual) from dual) from dual;

rownum

select rownum, ename from emp;

select rownum, ename from emp order by rownum desc;

select * from (select rownum as rn, ename from emp) where rn>5;

select * from (select rownum as rn, ename from emp) where rn>5 order by rn desc;

SubQuery로 테이블 작성하기

create table emp10 as (select empno, ename, sal+nvl(comm, 0) as pay from emp);

테이블의 구조만 복사하기

create table emp12 as (select empno, ename, sal+nvl(comm, 0) as pay from emp where 1=0);

sub query로 값을 한번에 집어넣기

insert into emp12 (select empno, ename, sal from emp);

• sub query를 이용한 두개 이상의 칼럼에 대한 값 변경/ 삭제가 가능하다.

Sequence

insert into bbs01 values ((select max(num)+1 from bbs01), 'max', 'tester', sysdate, 'test');

create sequence seq02 start with 17 increment by 10;
insert into bbs01 values (seq01.nextval, 'test', 'test', sysdate, 'test');

create sequence seq03 maxvalue 10 minvalue 5 cycle(nocycle) nocache;