4일차 내용 정리
ANSI = American National Standards Institute (미국 국가 표준 협회)
ROWNUM의 이해
SELECT *
FROM (SELECT 컬럼1, 컬럼2, 컬럼3, ROWNUM
FROM (SELECT 컬럼1, 컬럼2, 컬럼3
FROM 테이블명
ORDER BY 컬럼1 ASC)
WHERE ROWNUM BETWEEN 1 AND 5;
ROWNUM은 SELECT할 때마다 새롭게 부여
또 다른 ROWNUM의 개념이 발생
SELECT *
FROM (SELECT 컬럼1, 컬럼2, 컬럼3, ROWNUM
FROM (SELECT 컬럼1, 컬럼2, 컬럼3
FROM 테이블명
ORDER BY 컬럼1 ASC))
WHERE ROWNUM BETWEEN 6 AND 10;
ROWNUM에 별칭을 지정할 경우 가능
SELECT *
FROM (SELECT 컬럼1, 컬럼2, 컬럼3, ROWNUM AS 별칭1
FROM (SELECT 컬럼1, 컬럼2, 컬럼3
FROM 테이블명
ORDER BY 컬럼1 ASC))
WHERE 별칭1 BETWEEN 6 AND 10;
*의 사용 주의점
*을 하면 뒤에 또 다른 칼럼명을 명시할 수 없다 (* , 가 불가능)
SELECT *
FROM (SELECT *, ROWNUM AS 별칭1
FROM (SELECT 컬럼1, 컬럼2, 컬럼3
FROM 테이블명
ORDER BY 컬럼1 ASC))
WHERE rnum BETWEEN 6 AND 10;
-> ERROR 발생
별칭명.* 으로는 가능
SELECT *
FROM (SELECT 별칭2.*, ROWNUM AS 별칭1
FROM (SELECT 컬럼1, 컬럼2, 컬럼3
FROM emp
ORDER BY 컬럼1 ASC) 별칭2)
WHERE rnum BETWEEN 6 AND 10;JOIN 대신 서브쿼리(SELECT문)를 사용해 별칭 지정 후 다른 테이블 데이터를 연결
SUBQEURY (서브쿼리)
단일 행 SUBQEURY
서브쿼리의 결과가 오직 하나의 행으로 반환
=, >, <, >=, <=, <> 등 비교 연산자를 사용
스칼라 서브쿼리 (Scalar Subquery)
딱 하나의 값만 결과값으로 반환 = 컬럼 1개, 행 1개
다중 행 SUBQEURY
서브쿼리의 결과가 여러 개의 행으로 반환
IN, ANY, ALL, EXISTS 같은 다중 비교 연산자를 사용
1. IN
값이 테이블 안에 포함하면
-> "내 값이 테이블 데이터 안에 포함돼 있으면 돼"
ex) 내 이름이 수학 경시대회 명단에 있으면 참가 가능
2. ANY
여러 값 중 하나라도 비교 조건을 만족하면
-> "여러 개의 값 중에서 하나라도 조건을 만족하면 돼"
ex) 반 친구 중 한 명보다라도 성적이 높으면 한격
3. ALL
모든 값과 비교해 전부 만족하면
-> "모든 값과 비교해서 전부 조건을 만족해야 돼"
ex) 반 친구 전부보다 성적이 높아야 합격
4. EXISTS
조건을 만족하는 행이 존재한다면
-> "조건을 만족하는 데이터가 하나라도 존재하면 돼'
ex) 그 반에 학생이 하나라도 있으면 수업 시작Data Manipulation Language = DML (insert, update, delete)
Data Query Language = DQL (select)
Data Definition Language = DDL (create, alter, drop)
DDL (데이터 정의어)
CREATE (객체 생성)
-> TABLE, SEQUENCE, VIEW, USER
CREATE TABLE 테이블명 (
컬럼1 데이터타입 PRIMARY KEY,
컬럼2 데이터타입 NOT NULL,
컬럼3 데이터타입,
컬럼4 데이터타입 REFERENCES 테이블2명(컬럼2_1)
);제약조건(CONSTRAINT)
NOT NULL
NULL로 입력이 되어서는 안되는 컬럼에 부여하는 조건으로 컬럼 레벨에서 만 부여할 수 있는 제약조건
UNIQUE KEY (유일키)
저장된 값이 중복되지 않고 오직 유일하게 유지되어야할 떄 사용하는 제약조건 (NULL은 허용)
PRIMARY KEY (기본키)
NOT NULL 조건과 UNIQUE KEY 를 합친 조건
CHECK
조건에 맞는 데이터만 입력되도록 조건을 부여하는 제약 조건
FOREIGN KEY (외래키)
부모 테이블이 PRIMARY KEY를 참조하는 컬럼에 붙이는 제약조건
ALTER (객체 변경)
-> TABLE, USER
ADD (추가)
ALTER TABLE 테이블명 ADD(컬럼1 데이터타입, 컬럼2 데이터타입);
하나만 삭제한다면
ALTER TABLE 테이블명 ADD 컬럼 데이터타입;MODIFY (데이터 타입 수정)
ALTER TABLE 테이블명 MODIFY(컬럼1 데이터타입, 컬럼2 데이터타입);
하나만 삭제한다면
ALTER TABLE 테이블명 MODIFY 컬럼 데이터타입;RENAME (컬럼명 수정)
ALTER TABLE 테이블명 RENAME COLUMN 수정할 컬럼 TO 컬럼명;DROP (컬럼 삭제)
ALTER TABLE 테이블명 DROP COLUMN 컬럼;DROP (객체 삭제)
-> TABLE, USER
DROP TABLE 테이블명;Oracle에서 제공해주는 VIEW
DESC USER_TABLES;해당 계정에 있는 모든 테이블들 조회
SELECT TABLE_NAME FROM USER_TABLES;
테이블들의 제약조건 구조 확인
DESC USER_CONSTRAINTS;
USER_CONSTRAINTS
-------------------
CONSTRAINT_NAME -> 제약조건명
CONSTRAINT_TYPE -> 제약조건 타입
TABLE_NAME -> 제약조건이 적용된 테이블명
.
.
.init.sql 파일에 추가
COLUMN CONSTRAINT_NAME FORMAT A16
COLUMN TABLE_NAME FORMAT A10테이블에 설정된 제약 조건 정보 조회하기
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS;나만의 TABLE 생성
CREATE TABLE dept2(
COLUMN을 정의할 떄 작성하는 제약조건
COLUMN LEVEL의 제약 조건이라고 한다
deptno NUMBER(2) CONSTRAINT dept2_deptno_pk PRIMARY KEY,
dname VARCHAR2(12) CONSTARINT dept2_dname_nn NOT NULL,
loc VARCHAR2(13) CONSTRAINT dept2_loc_ck CHECK (loc IN('SEOUL', 'BUSAL'))
해당 조건이 TRUE일 때만 ROW가 생성되거나 UPDATE
);NOT NULL은 테이블 수준의 제약조건이 불가능하며 컬럼 수준만 가능
컬럼을 정의할 때 밑에 따로 제약 조건 추가 가능
CREATE TABLE dept3(
deptno NUMBER(2), COLUMN LEVEL의 제약조건
dname VARCHAR2(12) CONSTRAINT dept3_dname_nn NOT NULL,
loc VARChAR2(13),
CONSTRAINT dept3_deptno_pk PRIMARY KEY(deptno)
CONSTRAINT dept3_loc_ck CHECK(loc IN('SEOUL', "BUSAN'))
TABLE LEVEL의 제약조건
정의 시에 () 안에 제약조건을 부여할 컬럼을 작성
);테이블 생성 후 제약 조건 추가
CREATE TABLE dept4(
deptno NUMBER(2),
dname VARCHAR2(12),
loc VARCHAR2(10)
);
ALTER TABLE dept4 ADD CONSTRAINT dept4_deptno_pk PRIMARY KEY(deptno);
ALTER TABLE dept4 MODIFY dname CONSTRAINT dept4_dname_nn NOT NULl;
ATLER TABLE dept4 ADD CONSTRIAINT dept4_loc_ck CHECK(loc IN('SEOUL', 'BUSAN'));
CREATE TABLE emp4(
empno NUMBER(4),
ename VARCHAR2(12),
deptno NUMBER(2)
);
ALTER TABLE emp4 ADD CONSTRAINT emp4_empno_pk PRIMARY KEY(empno);
ALTER TABLE emp4 MODIFY ename CONSTRAINT emp4_ename_nn NOT NULL;
ALTER TABLE emp4 ADD CONSTRAINT emp4_deptno_fk FOREIGN KEY(deptno) REFERENCES dept4(deptno);해당 테이블의 제약조건 적용 여부를 확인
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'DEPT3'
|
TABLE_NAME CONSTRAINT_NAME CO
---------- ---------------- --
DEPT3 DEPT3_DNAME_NN C
DEPT3 DEPT3_LOC_CK C
DEPT3 DEPT3_DEPTNO_PK P
---------- ---------------- --
SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'DEPT3'
|
CONSTRAINT_NAME
----------------
DEPT3_DNAME_NN
DEPT3_DEPTNO_PK
----------------제약조건 활성화
1. ENABLED
제약 조건이 활성화된 상태로 데이터를 입력/수정 시 제약 조건이 검사됨
2. DISABLED
제약 조건이 비활성화된 상태
SELECT CONSTRAINT_NAME, STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME ='DEPT3'
|
CONSTRAINT_NAME STATUS
---------------- ----------------
DEPT3_DNAME_NN ENABLED
DEPT3_DEPTNO_PK ENABLED
---------------- ----------------
ALTER TABLE dept3 DISABLE CONSTRAINT dept3_dname_nn;
SELECT CONSTRAINT_NAME, STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'DEPT3';
|
CONSTRAINT_NAME STATUS
---------------- ----------------
DEPT3_DNAME_NN DISABLED
DEPT3_DEPTNO_PK ENABLED
---------------- ----------------Oracle에서는 테이블 생성, 제약 조건 추가 시 기본값은 'ENABLED'
VIEW
사용자에게 편리한 정보를 보여주기 위한 읽기 전용 TABLE
이때까지 수업으로 배운 점
1. TABLE, SEQUENCE를 적절히 생성 가능
2. 만들어진 TABLE에 DATA를 INSERT, DELETE, UPDATE 가능
3. 적절한 SELECT (JOIN, SUBQUERY, GROUP BY, 여러가지 함수 활용) 가능
4. TABLE 수정, 삭제, 조작, 제약조건 부여 및 관리..
