2019. 4. 11. 19:02ㆍDateBase
----------------------------------오라클의 데이터베이스 객체------------------------------------
테이블 객체의 종류
테이블 : 가장 기본적인 데이터 저장소, 행과 열로 구분되어 있다.
뷰 : 하나 이상의 테이블에 있는 데이터의 부분집합으로 구성된 가상의 테이블이다.
(주로 Select 하는용도로 쓴다.)
시퀀스 : 일련번호 생성기 (중복된 데이터값을 가지지 않기위해 사용된다.)
인덱스 : 쿼리의 실행 속도를 향상시키기 위해서 테이블의 데이터에 대한 색인을 갖고 있는 객체이다.
================DDL 데이터 베이스 정의어================
DDL
- 데이터베이스 정의어
- 데이터베이스 객체의 생성, 삭제, 수정과 관련한 SQL
- create구문, drop구문, alter구문
- commit, rollback의 적용대상이 아니다.
======테이블 정의하기=====
- 태이블의 데이터를 저장하는 객체
- 테이블의 이름, 컬럼의 이름은 30자 까지만 가능하다.
- A-Z, a-z, 0-9, _, $, # 를 사용할 수 있다 다만 $, #은 특별한 기능이있어 사용을 안한다.
////////////////////////////////////////테이블 생성하기 create table
테이블 생성하기
- create table 테이블명 (
컬럼이름 데이터타입(크기)
컬럼이름 데이터타입(크기)
컬럼이름 데이터타입(크기) default 값, // 필요하다면 기본값을 지정할 수 있다.
... // 값을 넣으면 넣은값, 안넣으면 기본값이 들어간다.
};
<오라클의 데이터 타입>
- VARCHAR2(size) 가변길이 문자 데이터 ,자바의 String (최대 4000바이트)
(이름, 주소, 블로그의 내용, 책의 제목, ...)
- CHAR(size) 고정길이 문자 데이터 ,자바의 String (최대 2000바이트)
(우편번호, 군번, 주민등록번호, 사업자등록번호, ...)
- NUMVER(p, s) 가변길이 숫자 데이터(p는 전체 숫자의 자리수, s는 소수점이이하 자리수)
예시) number(8,1) - 1,000,000.1 - 이렇게 표기가능 앞에 7자리 소숫점 1자리
- DATE 날짜 및 시간
- LONG 최대 2GB의 가변길이 문자 데이터 (이제 많이 사용안함)
- 테이블당 하나만 정의할 수 있다, 제약조건을 정의할 수 없다.
- group by, order by 절에 포함시킬수 없다
- CLOB 최대 4GB의 가변길이 문자 데이터 (이제 LONG대신 사용 많이함)
- BLOB 최대 4GB의 이진(바이너리) 데이터 (사진 및 동영상등 파일은 BLOB을 쓴다.)
- ROWID 테이블에서 행의 고유 주소를 나타내는 64진수
예시) 테이블 생성 예시
create table sample_categories (
category_code char(2) primary key, -- 고정길이 문자 데이터
category_name varchar2(200) -- 가변길이 문자 데이터
);
예시2) 테이블 생성 및 데이터 타입
create table sample_products (
product_no NUMBER(4) primary key, -- 숫자 데이터
product_name VARCHAR2(200), -- 가변길이 문자데이터
product_maker VARCHAR2(200), -- 가변길이 문자데이터
product_price NUMBER(8, 0), -- 숫자 데이터
product_stock NUMBER(4, 0), -- 숫자 데이터
product_discount_rate NUMBER(3,2) DEFAULT 0.0, -- 소수점이 포함된 숫자 데이터
product_pubdate date, -- 날짜 및 시간 데이터
category_code char(2), -- 고정길이 문자 데이터
product_create_date date DEFAULT SYSDATE -- 날짜 및 시간 데이터
);
무결성 제약 조건
- 테이블에 유효하지 않은 데이터가 저장되는 것을 방지한다.
- 테이블에 행이 추가, 갱신, 삭제될 때마다 제약조건 규칟이 적용된다.
- 제약조건의 종류 (중요!)
not null 제약조건
해당 컬럼이 null값을 포함하지 못하도록 제약한다.
unique 제약조건 (고유키 제약조건)
해당 컬럼의 값은 테이블의 모든 행에서 고유한 값을 가지는 컬럼
혹은 컬럼의 조합이어야 한다.
(이메일, 주민번호, 아이디, ....)
primary key 제약조건 (기본키 제약조건)
해당 컬럼이 행을 대표하는 컬럼이 되도록 한다.
* null값을 허용하지 않는다.
* 모든 행에 대해서 값이 고유해야 된다.
* not null + unique 같은 느낌
foreign key 제약조건 (외래키 제약조건)
해당 컬럼의 값이 동일한 테이블이나 다른 테이블의 기본키 혹은 고유키로
지정된 컬럼의 값을 참조하도록 한다.
(A라는 테이블의 컬럼의 값을 B라는 테이블의 값에서만 고를수있게 하는것)
* 참조하는 컬럼의 값들만 저장되게 한다.
* not null 제약조건이 지정되어 있지 않으면 null값도 허용된다.
references 로 사용된다.
check 제약조건
해당 컬럼의 값이 제시된 조건을 만족하는 값만 저장되도록 지정한다.
예시) 제약조건을 사용한 테이블 만들기
create table sample_products (
product_no NUMBER(4) primary key, -- 기본키 제약조건
product_name VARCHAR2(200) not null, -- not null (고유키 제약조건)제약조건
product_maker VARCHAR2(200),
product_price NUMBER(8, 0) check (product_price > 0), -- check 제약조건
product_stock NUMBER(4, 0) check (product_stock > 0), -- check 제약조건
product_discount_rate NUMBER(3,2) DEFAULT 0.0 check (product_discount_rate >= 0 and product_discount_rate <1), -- check 제약조건
product_pubdate date,
category_code char(2) references sample_categories (category_code), -- 외래키 제약조건
product_create_date date DEFAULT SYSDATE -- 날짜 및 시간 데이터
);
---------------제약조건을 사용하고 제약조건 이름을 지정하고 테이블 생성하기------------------
예시) 컬럼레벨 제약 조건 설정
※. 제약조건명은 다른테이블이라도 중복이 되어서는 안된다.
-- 컬럼레벨 제약조건
create table sample_products(
product_no number(4) constraint products_no_pk primary key,
product_name varchar2(500) constraint products_name_nn not null,
product_maker varchar2(200),
product_price number(8) constraint products_price_ck check (product_price > 0),
product_stock number(3) constraint products_stock_ck check (product_stock > 0),
product_sell char(1) constraint product_sell_ck check (product_sell in ('Y', 'N')),
product_category char(2) constraint product_category_fk references sample_categories(category_code),
product_create_date date default sysdate
);
insert into sample_products
(product_no, product_name, product_price, product_stock, product_sell, product_category)
VALUES
(product_seq.nextval, '양문형 냉장고', -1000000, null, null, null);
※. 위와같이 제약조건의 이름이 에러에 나오면서 뭐가 에러가 났는지 확인이 가능하다.
예시2) 테이블레벨 제약 조건 설정
※. 테이블레벨 제약조건을 하면 장점은 제약조건을 2개 이상을 걸수있다.
product_price 보면 not null과 check가 되어있는것을 확인할 수 있다.
-- 테이블레벨 제약 조건 설정
create table sample_products(
product_no number(4),
product_name varchar2(500) constraint products_name_nn not null, -- not null 제약조건으 아래로 못내림
product_maker varchar2(200),
product_price number(8) constraint products_price_nn not null,
product_stock number(3) constraint products_stock_nn not null,
product_sell char(1) default 'Y' constraint products_sell_nn not null,
product_category char(2) constraint products_category_nn not null,
product_create_date date default sysdate,
constraint products_no_pk primary key (product_no),
constraint products_price_ck check (product_price > 0),
constraint products_stock_ck check (product_stock > 0),
constraint product_sell_ck check (product_sell in ('Y', 'N')),
constraint product_category_fk foreign key (product_category) -- 테이블레벨에서는 foreign key가 필요
references sample_categories(category_code)
);
==================시퀀스(중요!)===================
시퀀스 특징
- 일련번호 생성한다.
- 여러 사용자가 공유할 수 있다.
- 행의 고유한 기본키 값을 생성하는데 주로 사용된다.
- 시퀀스는 테이블과 별도로 저장, 관리되기 때문에 여러 테이블에서 동일한 시퀀스를 사용할 수 있다.
/////////////////////////////////////시퀀스 생성하기
- create sequence 시퀀스명
만들고 오브젝트 탐색기 -> 시퀀스에서 확인 가능
예시) create sequence my_seq;
세부 명령어
create sequence 시퀀스명
increment by 숫자값 : 번호사이의 간격을 지정 (시퀀스 증가값) - 기본값 1
start with 숫자값 : 첫번째 일련번호 지정 - 기본값 1
maxvalue 숫자값 : 시퀀스의 최대값을 지정 - 기본값은 10^27
minvalue 숫자값 : 시퀀스의 최솟값을 지정 - 기본값은 1
cycle | nocycle : 최대값 도달 이후에도 기퀀스를 계속 생성할지 여부 - 기본값 nocycle
cache 숫자값 | nocache : 일련번호를 미리 생성해서 메모리에 저장할 갯수를 지정
- 기본값 cache 20
(캐쉬 20은 20번까지 미리 만들어두는것이다. 그래서 Last Value가 캐쉬포함값이다.)
////////////////////////////////////////새로운 일련번호 발급받기
- nextval을 사용하면 새로운 일련번호를 발급받을 수 있다.
예시) nextval을 이용해서 book_no입력
insert into books
(book_no, book_title, book_author, book_publicher, book_price, book_pubdate, book_create_date)
values
(my_seq.nextval, '이것이 자바다', '신용권', '한빛미디어', 3000, '2015-01-01', sysdate);
// 10번 실행
// book_no가 하나씩 저절로 증가한다.
//////////////////////////////////////////////최근 생성 일련번호 조회
- currval을 사용하면 현재 사용자가 방금 생성한 일련번호를 조회할 수 있다.
(nextval이 한번이상은 실행되야 사용가는 키자마자는 불가능)
예시) currval을 이용하여 일련번호 조회하기
select my_seq.nextval from dual;
select my_seq.currval from dual;
-------------------------------------
next.val을 하면 일련번호가 1씩늘어나지만
currval을 하면 마지막 생성된 일련번호만 조회된다.
사용 예시) 유저를 만들면 유저에게 카드번호(카드번호 + 유저번호)에 추가로 등록해주는것
insert into users (user_no, user_name, ......, user_card_no)
values (user_seq.nextval, ?, ....., 101-111||user_seq.vurrval);
// 이런식으로 사용
시퀀스의 nextval, currval 사용 위치
1. 서브쿼리에 속하지 않은 select문의 select절
2. insert문의 values절
3. update문의 set절
////////////////////////////////////////////////////시퀀스 삭제하기
- drop sequence 시퀀스명
예시) drop sequence my_seq;