Oracle(SQL Developer) & JAVA 상품 주문 관리 프로그램 리뷰 - 1. SQL문

SQL

* 시작하기 전에 서비스(제어판)에서 oracle 실행여부 확인 -> system에서 유저 생성 -> 해당 서버에서 작업

[ 테이블 생성 ]

1. 부모 테이블 product 생성 후 자식 테이블 orders를 생성한다

(삭제할 때는 자식 테이블 먼저 해야 오류가 나지 않는다.)

** order같은 sql의 예약어는 테이블 명으로 사용할 수 없다.

create table product(
  pno int primary key not null,
  name varchar(100) not null,
  price int default 0
);

create table orders(
  order_no int primary key not null,
  product_no int not null,
  order_date date default sysdate,
  order_name varchar(20),
  order_qnt int default 1,
  foreign key (product_no) references product(pno)
);

- primary key는 기본키로 중복되지 않는 고유한 데이터이다.

- not null : 값을 무조건 입력하도록 설정

- default : 기본 값을 지정 (날짜의 default를 sysdate로 설정하면 오늘 날짜가 기본으로 들어간다)

- foreign key는 왜래키로 각 테이블의 동일한 데이터를 참조한다. 

foreign key (product_no) references product(pno)

▶ orders테이블의 product_no열이 product테이블의 pno열을 참조하도록 외래키 설정

** 같은 데이터를 참조하기 때문에 없는 데이터를 삽입하면 오류 난다. (참조무결성)

(ex. 부모테이블의 pno는 100~104까지 있는데 product_no에 105번을 insert 하면 참조하는 데이터가 없어 오류)

 

[ 시퀀스 생성 ]

create sequence pnum
start with 100
increment by 1;

create sequence onum
start with 1
increment by 1;

- 상품번호(pnum)과 주문번호(onum)가 자동으로 증가하도록 각각의 시퀀스를 생성한다

- 시퀀스를 생성하면 max num을 따로 구하지 않아도 돼서 편리하다.

- start with는 시작번호, increment by는 증감하는 숫자

- 삭제할 때는 drop sequence 시퀀스명;

 

//product
insert into product(pno,name,price)
values(pnum.nextval,'세탁기',1500000);
insert into product(pno,name,price)
values(pnum.nextval,'냉장고',4500000);

//orders
insert into orders(order_no,product_no,order_name,order_qnt)
values(onum.nextval,100,'심청이',2);
insert into orders(order_no,product_no,order_name,order_qnt)
values(onum.nextval,101,'홍길동',3);

- insert문으로 각각의 테이블에 데이터를 삽입한다.

insert into 테이블명(열1,열2,열3) values(데이터,데이터,데이터)

- 시퀀스로 생성한 번호를 사용하려면 시퀀스명.nextval로 넣어주어야 값이 정상으로 들어간다.

- 데이터를 삽입할 때는 열 번호만큼 데이터를 넣어주어야 하며 각각의 타입에 맞춰 작성해야 한다. (문자는 '' 작은따옴표 안에)

select * from product;로 실행한 결과

select * from orders;로 실행한 결과

[ 뷰 생성 ]

- 뷰는 테이블의 일부 또는 여러 개의 칼럼(열)을 결합한 결과를 담는 가상의 테이블이다.

- 뷰는 select쿼리를 실행할 때 원본 테이블에서 데이터를 가져오는 것처럼 사용할 수 있다.

create view view_order as
select o.*,p.* from orders o, product p
where p.pno = o.product_no;

- view_order라는 뷰를 생성해 orders 테이블은 o, product 테이블은 p라는 별칭을 부여

- product테이블의 pno와 orders테이블의 no가 같은 경우 두 테이블의 모든 정보를 보여준다.

create view 뷰이름 as slect문

** select문의 from절에서 테이블 별칭을 부여할 때 as 없이 작성한다.

select * from view_order;로 실행한 결과

[ 커밋 ]

- 커밋은 현재 트랜잭션에서 수행한 모든 DB 변경 작업을 저장하는 것을 의미한다.- insert, update, delete 등 쿼리를 실행한 후 commit 하면 실제 DB에 적용되어 영구적으로 저장된다.

commit;

- 모든 작업이 끝나면 최종적으로 commit 해준다.