23일차 2023-04-07
어제 내용에 이어서 3-3). 테이블 생성 시 제약 조건 추가+)
not null: null값을 절대 허용 안 하는 칼럼(column)을 뜻함 (데이터가 있어야 됨)
col1, col2, col3는 값이 무조건 들어가야 된다 , col4는 없어도 됨
unique: 값의 중복 허용 x , 하지만 null은 허용(여러 번 가능)
col1은 숫자에 pk, col2는 문자에 UQ_COL2_UQTBL이름으로 unique(중복 x), col3는 unique에 not null까지 , col4는 제약조건 없음.
-데이터 insert into로 입력
-결과
default: 데이터 입력 시 해당 칼럼에 입력하지 않았을 때 자동적으로 입력되는 기본 값 (제약명을 지정할 수 없다)
foreign key: 외래키로 참조 무결성을 유지하지 위한 제약 조건
check: domain무결성을 유지하기 위한 제약 조건 (내가 정해놓은 제약조건에 벗어나는 데이터 값을 입력되지 못하게 하는 것, 오라클이 막음)
id에 pk설정됨 (주석 설명 무시)
member테이블 id에 문자 10자리 pk,
bbs는 id를 fk로 설정 두 가지 방법
id varchar2(10) references member(id)
혹은 id varchar2(10) foreign key(id) references member(id)
이렇게 설정
(on delete set null)은 아래 설명으로 foreign key생성 시 제약을 푸는 조건으로
id가 kim행들을 삭제가 가능하게 됨 null로 변환 혹은 on delete cascade로 자식을 생성 설정하면 부모인 member를 삭제하면 자식도 삭제됨
*FOREIGN KEY 생성 시
ON DELETE CASCADE 혹은 =>(부모가 삭제되면 이 명령어로 자식이 참조하는 칼럼을 삭제)
ON DELETE SET NULL조건을 추가할 수 있다. => 이건 위에서 NULL값으로 변경
단, ON UPDATE CASCADE는 없음(오라클에서)
VARCHAR2(size):가변 길이 문자 데이터
필드 생성 시에는 size 최댓값이 4000 임.
단위는 byte
영문 및 숫자는 최대 4000자까지
한글(유니코드)은 최대 2000자까지 저장
NVARCHAR2(size):가변 길이 문자 데이터
필드 생성 시에는 size 최댓값이 2000 임.
단위는 글자
영문 및 숫자는 최대 2000자까지
한글(유니코드)은 최대 2000자까지 저장
'A@B.kr' 말고 'A@B.k'는 입력이 안됨 2개 이상이어서
recyclebin은 휴지통 내용 보기인데 11gx버전인데 안 먹힘
purge는 윈도우에서 영구삭제랑 같음 (shift+delete)
fk가 참조해서 부모인 member가 삭제 안됨
하지만 cascade constraints는 관계가 형성되어 있어도 삭제 가능
2. 시퀀스
-테이블의 필드에 일련번호 부여
-테이블 생성 후 시퀀스(일련번호)를 따로 만들어야 한다. (되도록 1테이블 생성에 1시퀀스 생성해라.)
※시퀀스를 모른다고 가정할 때 일련번호의 데이터를 입력하는 방법
INSERT INTO 테이블명(일련번호칼럼) VALUES((SELECT MAX(일련번호칼럼)+1 FROM BOARD));
2-1). 시퀀스구문
시퀀스 생성 SQL 구문 []는 생략 가능
CREATE SEQUENCE 시퀀스명
[INCREMENT BY 증가값]
[START WITH SEED값]
[MAXVALUE n| MINVALUE n] //시퀀스 최대/최솟값 지정
minvalue의 디폴트 값 1
maxvalue의 디폴트값 1.0000E+28 (E는 10^28)
[CYCLE|NOCYCLE] //최대 또는 최솟값에 도달한 후 계속 값을
생성할지 여부 지정(디폴트는 NOCYCLE)
[CACHE/NOCACHE] //CACHE메모리에 오라클 서버가 SEQUENCE값을
할당하는가 여부 지정(디폴트로 CACHE]
※START WITH에 MINVALUE 보다 작은 값은 지정할 수 없다.
즉 START WITH값은 MINVALUE와 같거나 MINVALUE보다 커야 한다
※NOCYCLE로 설정하고 시퀀스를 계속 얻어 올 때
MAXVALUE에 지정한 값을 초과하면 아래와 같은 에러발생
시퀀스명.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다.
*PRIMARY KEY에 CYCLE옵션지정하면 안 됨.
-NEXTVAL:다음의 일련번호 값을 반환
-CURRVAL: 현재의 일련번호 값을 반환
-NEXTVAL과 CURRVAL을 사용할 수 있는 구문
SUBQUERY가 아닌 SELECT문
INSERT문의 VALUES절
UPDATE문의 SET절
※같은 절 안에서 NEXTVAL을 여러 번 사용해도 값은 같다
-NEXTVAL과 CURRVAL을 사용할 수 없는 구문
-VIEW문의 SELECT문
-DISTINCT를 사용한 SELECT문
-GROUP BY, HAVING, ORDER BY를 이용한 SELECT문
사용 ex)
-시퀀스 생성
CREATE SEQUENCE dept_deptno
INCREMENT BY 1
START WITH 50
MAXVALUE 99
NOCACHE
NOCYCLE
-해당 칼럼에 값 입력
INSERT INTO dept VALUES(DEPT_DEPTNO.NEXTVAL, '영업부', '서울');
-현재 시퀀스 값 얻기
SELECT DEPT_DEPTNO.CURRVAL FROM DUAL;
-시퀀스 수정
ALTER SEQUENCE 시퀀스명
[INCREMENT BY 증가값]
[MAXVALUE n| MINVALUE n]
[CYCLE|NOCYCLE] 디폴트는 nocycle
[CACHE/NOCACHE] 디폴트는 cache (설정 건들지 않으면 cache_size 20)
기본 시퀀스 생성
seq2의 숫자를 2씩 늘리는데 처음에는 10에서 시작 40까지 나오고, 42이 안되고 1이 된다 최솟값(여기서 2 증가여서 2가 될 거 같아도 1이 됨)
*START WITH N은 수정 못함
-시퀀스 삭제
DROP SEQUENCE 시퀀스명
*DUAL :출력을 위한 임시 테이블로 모든 사용자 계정이 가질 수 있다. (select문은 꼭 from을 써야 됨 여기서 dual로 임시테이블 설정 가능)
※CREATE SEQUENCE 시퀀스명
MAXVALUE 10
CYCLE;
실행 시에
"CACHE 에는 1 사이클보다 작은 값을 지정해야 합니다" 에러발생
왜냐하면 할당 가능한 값이 1에서 10까지 10개인데 CACHE의
기본값은 20개임으로 생성 실패한다.
해결책:
1. CACHE를 10 미만값으로 설정하거나
CREATE SEQUENCE 시퀀스명
MAXVALUE 10
CACHE 9
CYCLE;
2. NOCACHE로 설정하거나 혹은 NOCYCLE로 설정한다
CREATE SEQUENCE 시퀀스명
MAXVALUE 10
[NOCACHE]
NOCYCLE;
3. 데이터 입력 (DML)
[데이터 입력]
-데이터 입력을 위한 SQL구문 작성 시 문자형(CHAR 혹은 VARCHAR2등)은 반드시 '(작은따옴표)로 값을 감싸야한다.
숫자형은 작은따옴표가 필요 없다.
1. 입력방법 첫 번째 SQL구문
INSERT INTO 테이블명(컬럼명1,컬러명2,---------,컬러명N)
VALUES(값1,값2,---------------,값N)
2. 입력방법 두 번째 SQL구문
INSERT INTO 테이블명
VALUES(값1,값2,---------------,값N)
※ 컬럼에 NULL삽입시: '' 혹은 NULL
디폴트 제약조건을 준 컬럼에 DEFALUT값 삽입시:DEFAULT
예)
INSERT INTO 테이블명
VALUES(값1,값2,NULL,DEFAULT)
[데이터 수정]
-SQL구문
UPDATE 기존테이블명
SET 컬럼명 = 새로운 값
[WHERE 업데이트 조건]
[데이터 삭제]
-SQL구문
DELETE [FROM] 기존테이블명
[WHERE 삭제 조건]
*TRUNCATE TABLE 테이블명:기능은 DELETE FROM 테이블명과 같다
즉 테이블 안에 잇는 모든 데이터를 삭제한다.
차이점:DELETE FROM은 ROLLBAKC을 위해 ROLLBAKC로깅정보를 남기지만
TRUNCATE TABLE은 ROLLBACK을 위한 로깅정보를 남기지 않는다.
고로 대용량의 데이터(테이블에 백만 개의 레코드)를 한 번에 삭제 시에
TRUNCATE가 속도면에서 빠르다.
DELETE FROM을 사용하면 Oracle서버가 다운될 수도 있다.
단, TRUNCATE TABLE은 ROLLBACK이 안되기 때문에 삭제하면
복원이 불가능함으로 항상 조심해라
TRUNCATE는 테이블 삭제 후 다시 삭제된 테이블과
같은 구조로 다시 생성하는 것임.
ex)
4. view
[VIEW]
- VIEW는 하나 또는 그 이상의 테이블로부터 생성된 가상의 테이블
- 물리적으로 존재하지 않는 테이블
- 데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 액세스 제한 가능
- 복잡한 질의어를 통해 얻을 수 있는 결과를 간단할 질의어를 써 구할 수 있다.
- 하나의 테이블로 만든 VIEW에서는 DML(INSERT, DELETE, UPDATE) 문장을 수행할 수 있지만
여러 테이블로 만든 VIEW(PK와 FK로 조인한)에서는
DML문(INSERT)을 수행할 수 없다, 단, UPDATE와 DELETE는 가능.
- VIEW생성 시 ORDER BY를 포함할 수 없다(ORACLE 8i이전버전까지)
-VIEW의 장점
(1) 보안성 - 중요한 칼럼을 숨 길수 있어 보안을 유지할 수 있다.
(2) 편의성 - 여러 개의 테이블 사용을 위해 복잡한 조인이 사용되었다 하더라도 사용자는 이를
몰라도 된다.
(3) 간결성 - 뷰를 사용하면 APPLICATION을 개발할 때 복잡한 쿼리문을 숨길 수 있어 소스가
간결해진다.
-VIEW생성 구문 형식
CREATE [ OR REPLACE] VIEW VIEW명[(컬럼별칭1,컬럼별칭2...)]
//별칭 부여시 "" 로 감싸줘라
AS
SELECT 구문
[WITH READ ONLY] --뷰를 읽기 전용으로 만들 때
-VIEW의 수정
OR REPLACE 사용:이미 존재한다면 다시 생성
-VIEW의 삭제
DROP VIEW VIEW명
* 생성한 VIEW 정보 보기
SQL> SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
view를 하나의 객체로 본다 table도 객체로 본 것처럼 똑같이 보면 됨
ex)
5. 인덱스
[인덱스(INDEX)]
- 행의 검색 속도를 향상 시킬 수 있는 개체
- 인덱스를 명시적(CREATE INDEX) 또는
자동적으로(PRIMARY KEY, UNIQUE KEY)로 생성할 수 있다.
- 칼럼에 대하 인덱스가 없으면 한 테이블 전 체를 검색.
즉 인덱스는 쿼리의 성능을 향상 시키는 것이 목적
- INSERT/UPDATE/DELETE 가 많은 컬럼에 대해서
INDEX를 되도록이면 설정하지 말아라
- 인덱스가 많은 것이 항상 좋은 것은 아니다
왜냐하면 인덱스를 가진 테이블에 대한 DML작업 은 인덱스도
갱신되어야 함을 의미하기 때문
- 어느 칼럼에 인덱스를 설정하는가?
1.WHERE조건이나 조인 조건에서 자주 사용되는 칼럼
2. 광범위한 값을 포함하는 칼럼
3. 많은 NULL값을 포함하는 칼럼
*테이블에 자료의 양이 적거나 자주 갱신되는
테이블은 오히려 인덱스를 걸지 말아라
인덱스 구문 형식]
-인덱스 생성
CREATE INDEX 인덱스명 ON 테이블명(컬럼1[,컬럼2,.....]);
-인덱스 삭제
DROP INDEX 인덱스명
*인덱스는 수정 불가 수정 시에는 삭제 후 다시 생성
※칼럼에 설정된 인덱스 보기
select * from user_ind_columns
ex)
