서브쿼리, ROWNUM, 인라인뷰, DDL
[Oracle] 오라클 ANY, SOME, ALL 연산자 사용법 완벽 정리
오라클의 ANY, SOME, ALL 연산자는 실무에서 자주 사용은 안하지만, 사용법이 궁금해서 찾아보면 사용법이 생각보다 어렵고 어떤 상황의 쿼리문에서 사용해야 할지 머릿속에 그려지지 않는다. SOME
gent.tistory.com
스칼라 서브 쿼리
SELECT *,
(SELECT deptno FROM dept WHERE dept.NO = emp.deptno)
FROM emp;
ROWNUM
행에 번호를 매겨 준다. 데이터가 INSERT된 순서대로 번호가 정해 진다.
LIMIT처럼 사용할 순 있으나 행 번호를 먼저 정한 뒤 정렬을 진행하므로 다르다.
정렬을 진행한 SELECT문을 FROM절에 불러와 주면 LIMIT처럼 사용하는 것도 가능하다.
SELECT name, salary, job, rownum
FROM emp WHERE rownum<=5;
인라인 뷰(FROM 서브 쿼리)
SELECT name, salary, job
FROM (SELECT name, salary, job
FROM emp
ORDER BY salary DESC);
임시 테이블을 생성하여 거기서 데이터를 추출한다.
직접 테이블을 참조하지 않기 때문에 보안에 유리하다.
DDL
Table : 데이터를 저장하는 공간
View : 가상 테이블(보안, SQL 문장을 최소화)
→ 반복되는 SQL 문장이 많거나, 여러 데이터를 조합하여 편하게 검색하고 싶을 때 사용함
Sequence : 자동으로 증가하는 일련번호
Function : 리턴형을 가지고 있는 함수
***Procedure : 리턴형이 없는 함수
Trigger : 다른 테이블에 영향을 줌
테이블 만들기
문자 데이터형(String@java) | |
CHAR(1~2000byte) | 크기가 고정된 문자열 |
*VARCHAR2(1~4000byte) | 가변형 문자열(=VARCHAR@MySQL) |
*CLOB(4GB) | 줄거리나 회사 소개 등 엄청 긴 내용(=TEXT@MySQL) |
숫자 데이터형(int, double@java) | |
NUMBER | 8자리까지 숫자를 저장 |
NUMBER(4) -정수형 | 4자리까지 저장(=int@MySQL) |
NUMBER(7,2) -실수형 | 7자리까지 저장 or 5자리+소수점 2자리 (=double@MySQL) |
기타형 | |
DATE(@java.util.Date) | 날짜형 저장 -> YY/MM/DD |
BFILE | file형태로 저장 (이미지, 동영상) |
BLOB*** | 바이너리 형식 |
제약조건
정형화된 데이터, 즉 필요한 데이터만 저장해야 함.
***모든 테이블은 기본 키(Primary Key)가 존재해야 한다.(권장)
그리고 그 키는 중복이 없어야 하며, 각 데이터의 id(식별자)로 쓰인다.
- PRIMARY KEY : 중복이 없는 데이터
- NOT NULL : 반드시 입력값이 추가되어 있어야 함
- UNIQUE : 중복이 없는 데이터이지만 NULL이 허용됨. (전화번호, 이메일 등)
→ PRIMARY KEY = NOT NULL + UNIQUE
- FOREIGN KEY : 외래키, 참조키, 다른 테이블과 연결하는 매개가 되는 데이터
FOREIGN KEY (column_name1) REFERENCES table_name(column_name2)
- CHECK : 지정된 데이터만 첨부(성별, 부서명, 근무지 등)
- DEFAULT : 입력이 되지 않은 경우 자동 지정 (날짜 같은 거)
- (ex) regdate DATE DEFAULT SYSDATE
FOREIGN KEY : 외래 키, 참조 키. PRIMARY KEY에 있는 데이터만 참조한다.
→ FOREIGN KEY는 제약 조건이 아니다.
명령어 (TABLE 제작)
1) 기존의 테이블을 복사
--emp테이블의 데이터와 형태를 복사
CREATE TABLE table_name
AS (SELECT * FROM emp);
--테이블의 형태만 복사
CREATE TABLE table_name
AS (SELECT * FROM emp WHERE 1=2); --WHERE FALSE조건을 주면 값은 하나도 가져오지 않는다.
2) 새로운 테이블을 제작
CREATE TABLE table_name(
--CONSTRAINT : NOT NULL, PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, DEFAULT 등
column_name1 dataType CONSTRAINT,
column_name2 dataType CONSTRAINT,
column_name3 dataType CONSTRAINT);
테이블명은 알파벳으로 시작하고, 대소문자 구분을 하지 않으며 숫자 사용이 가능하다.
키워드(SELECT, FROM 등)는 사용할 수 없다.
특수문자(_, $, #)는 사용할 수 있으나 대체로 언더바(_)만 사용한다.
테이블명 길이는 30byte로 제한된다.
*table명과 컬럼명은 동일해도 되나 다른 테이블과 테이블명이 중복되어선 안된다.
*한 테이블 안에서 컬럼명이 중복되어선 안된다.
--이게 정석이지만
CREATE TABLE member(
id varchar2(20) PRIMARY KEY,
pwd varchar2(10) NOT NULL,
name varchar2(34) NOT NULL,
sex varchar2(10),
regdate DATE DEFAULT SYSDATE,
tel varchar2(20),
CHECK(sex IN ('남자','여자')),
UNIQUE(tel));
--실무에서는 테이블 변경을 용이하게 하기 위해...
--이런식으로 제약조건을 따로 분리한다. 이름도 지어주고~ (테이블명_컬럼명_조건(pk, fk, ck, nn, uk...))
CREATE TABLE member(
id varchar2(20),
pwd varchar2(10) NOT NULL,
name varchar2(34) NOT NULL,
sex varchar2(10),
regdate DATE,
tel varchar2(20),
CONSTRAINT member_id_pk PRIMARY KEY(id),
CONSTRAINT member_sex_ck CHECK(sex IN('남자','여자'))
);
명령어 (컬럼 제어)
ALTER : 컬럼, 제약 조건 등 수정
ADD : 추가
MODIFY : 수정
DROP : 삭제
CREATE TABLE student(
name varchar2(50),
hakbun NUMBER(5),
kor NUMBER(3),
eng NUMBER(3),
math NUMBER(3),
address varchar2(200) CONSTRAINT std_adr_nn NOT NULL,
CONSTRAINT std_hak_pk PRIMARY KEY(hakbun),
CONSTRAINT std_kor_ck CHECK(kor>=0),
CONSTRAINT std_eng_ck CHECK(eng>=0),
CONSTRAINT std_math_ck CHECK(math>=0)
);
ALTER TABLE student ADD tel varchar2(15) CONSTRAINT std_tel_nn NOT NULL; --tel 컬럼 추가
ALTER TABLE student MODIFY name varchar2(34); --name 컬럼 데이터형 변경
ALTER TABLE student DROP tel; --tel 컬럼 삭제
DROP TABLE student; --student 테이블 완전삭제
--이미 있는 컬럼에 primary key 제약조건 추가
ALTER TABLE books MODIFY no CONSTRAINT books_no_pk PRIMARY KEY;
TRUNCATE : 데이터 잘라내기(테이블의 형태는 유지하고 데이터만 모두 삭제한다)
RENAME : 테이블명 변경
INSERT INTO student values(
'kim',1111,78,65,87,'seoul'); --예시 데이터 삽입
SELECT * FROM STUDENT s; --데이터 1개 들어가 있음
TRUNCATE TABLE student; --데이터 잘라내기
SELECT * FROM STUDENT s; --조회되는 데이터 없음
RENAME student TO students; --student테이블의 이름 students로 변경
DDL(create, drop, alter, truncate) → commit, rollback이 없다.
오류가 없는 한... 무조건 실행 후 저장된다. (신중해야 함)
'부트캠프(END) > Oracle' 카테고리의 다른 글
DDL 예제 (0) | 2022.06.28 |
---|---|
Oracle JOIN, ANSI JOIN (0) | 2022.06.22 |
Oracle 내장 함수 (0) | 2022.06.21 |