1. 데이터 정의어
1) ALTER
--테이블 변경
--사원정보 테이블 복사
CREATE TABLE EMP_ALTER
AS SELECT * FROM EMP;
SELECT * FROM EMP_ALTER;
--테이블 열 추가
ALTER TABLE EMP_ALTER
ADD HP VARCHAR2(20);
SELECT * FROM EMP_ALTER;
--테이블 열 이름 변경
ALTER TABLE EMP_ALTER
RENAME COLUMN HP TO TEL;
SELECT * FROM EMP_ALTER;
--테이블 열의 데이터 타입 변경
ALTER TABLE EMP_ALTER
MODIFY EMPNO NUMBER(5);
DESC EMP_ALTER;
2) RENAME _ 이름 변경
--테이블 이름 변경
RENAME EMP_ALTER TO EMP_RENAME;
DESC EMP_ALTER;
SELECT * FROM EMP_ALTER;
DESC EMP_RENAME;
SELECT * FROM EMP_RENAME;
3) 테이블 비우기
--테이블 비우기(행 = 데이터 삭제) (DDL임 - 자동 커밋이 되는)
TRUNCATE TABLE EMP_RENAME;
SELECT * FROM EMP_RENAME;
--이 결과는 DELETE 문으로 가능하지만, 자동 COMMIT 이 되지 않음 (DML임 - 커밋을 해줘야 하는)
--테이블 삭제
SELECT * FROM EMP_RENAME;
DROP TABLE EMP_RENAME;
DESC EMP_RENAME;
SELECT * FROM EMP_RENAME;
2. 객체 종류 - 데이터 사전
0) 정의
- 데이터 사전
* 사용자 테이블 : 사용자가 데이터를 저장하기 위해 만든 테이블 (여태 봤던건 다 사용자 테이블)
* 시스템 테이블 : 테이블, 인덱스 및 저장 프로시저에 대한 정보와 같은 데이터베이스에 대한 메타데이터를 저장하기 위해 데이터베이스 관리 시스템(DBMS)에서 만들고 유지 관리하는 테이블
1) 사전 탐색 기능
--객체 종류
--데이터 사전
--현재 계정에서 사용 가능한 데이터 사전 보기(결과는 둘다 같음)
SELECT * FROM DICT;
SELECT * FROM DICTIONARY;
2) 현재 소유자의 테이블 보기
--현재 계정 사용자가 소유한 객체(테이블)보기 // scott이 소유하고 있는 테이블 (실습한 테이블)
SELECT TABLE_NAME FROM USER_TABLES;
3) 모든 사용자의 테이블 보기
--모든 사용자가 소유한 객체(테이블) 보기
SELECT OWNER, TABLE_NAME FROM ALL_TABLES;
4) 시스템 사용자의 테이블 보기
--데이터 베이스 관리 권한을 가진 사용자만 볼 수 있음
SELECT * FROM DBA_TABLES;
--이를 해결하기 위해 시스템 계정으로 들어가야 함
5) SCOTT 계정 사용자 정보 보기
--SCOTT 계정 사용자 정보 보기
SELECT * FROM DBA_USERS
WHERE USERNAME = 'SCOTT';
--존재하는데 존재하지 않는다고 뜨는 이유
--보안 문제 때문, 있는지 없는지 자체를 모르게 해야 안전함
--존재하지만 권한이 없다고 뜨면 존재한다는 추가적 정보를 주게 됨
--로그인 아이디, 비밀번호가 대표적 케이스
2-1. 객체 종류 - INDEX
1) INDEX 정보 보기
--인덱스
--SCOTT 계정으로 보기
--인덱스 정보 보기
SELECT * FROM USER_INDEXES;
--인덱스 열 정보 보기
SELECT * FROM USER_IND_COLUMNS;
2) INDEX 생성후 정보 보기
--인덱스 생성
CREATE INDEX IDX_EMP_SAL
ON EMP(SAL);
--생성된 인덱스 보기
SELECT * FROM USER_IND_COLUMNS;
--
3) INDEX 삭제
--인덱스 삭제
DROP INDEX IDX_EMP_SAL;
SELECT * FROM USER_IND_COLUMNS;
2-2. 객체 종류 - 뷰
0) 정의
* 뷰의 뜻 : SELECT 조회한 결과를 저장한 객체 (테이블처럼 데이터를 물리적으로 저장하지 않음)
* 사용목적
- 편리성 : 뷰로 저장해놓으면 메인 쿼리에 집중 가능 (실무 SELECT문은 복잡하고 길어서)
- 보안성 : 뷰 열람 권한을 제공하는 것이 불필요한 데이터 노출을 막음
1) 뷰 생성 권한 부여
--뷰
--테이블처럼 사용하는 뷰
--뷰 사용 목적 : 편리성, 보안성
--SCOTT계정에게 뷰 생성 권한 부여 (SQLPLUS에서 SYSTEM계정으로)
GRANT CREATE VIEW TO SCOTT;
2) TOAD에서 SCOTT 계정으로 뷰 생성
--토드에서 scott 계정으로 뷰 생성
CREATE VIEW VW_EMP20
AS (SELECT EMPNO,ENAME,JOB,DEPTNO
FROM EMP
WHERE DEPTNO = 20);
SELECT * FROM VW_EMP20;
3) 뷰 생성 확인
--뷰 생성 확인
SELECT * FROM USER_VIEWS;
* SQL PLUS에서 확인
--뷰 생성 확인 (SQLPLUS에서 SCOTT계정으로)
sqlplus scott/tiger
SELECT VIEW_NAME, TEXT_LENGTH, TEXT FROM USER_VIEWS;
* TOAD에서 뷰 확인
--생성한 뷰 보기
SELECT * FROM VW_EMP20;
4) 뷰 삭제
--뷰 삭제
DROP VIEW VW_EMP20;
--삭제된 뷰 확인
SELECT * FROM USER_VIEWS;
--삭제된 뷰 보기
SELECT * FROM VW_EMP20;
5) ROWNUM - 최상위 보기
--뷰를 이용하여 최상위 보기
--데이터 순으로 보여짐
SELECT ROWNUM, E.*
FROM EMP E;
--급여 내림차순으로 보여짐
SELECT ROWNUM,E.*
FROM EMP E
ORDER BY SAL DESC;
--ROWNUM은 테이블에 있는 열이 아닌 가상의 열
--아직 ROWNUM은 랭킹을 매기는 시스템이 없음
6) 인라인뷰
* 서브쿼리 이용
--급여 내림차순으로 정렬된 뷰
--인라인뷰(서브쿼리 이용)
SELECT ROWNUM, E.*
FROM ( SELECT *
FROM EMP E
ORDER BY SAL DESC) E;
* WITH절 이용
--인라인뷰(WITH절 이용)
WITH
E
AS
( SELECT *
FROM EMP E
ORDER BY SAL DESC)
SELECT ROWNUM, E.*
FROM E;
* 급여 1~3등 뽑기
-- 1~3등
SELECT ROWNUM, E.*
FROM ( SELECT *
FROM EMP E
WHERE ROWNUM <= 3
ORDER BY SAL DESC) E;
2-3. 객체 종류 - 시퀀스
1) 열만 복사한 테이블 만들기
--시퀀스 = 연속 숫자를 생성하는 객체
--부서 정보 테이블의 열만 복사한 테이블 만들기
CREATE TABLE DEPT_SEQUENCE
AS SELECT * FROM DEPT WHERE 1 !=1;
SELECT * FROM DEPT_SEQUENCE;
2) 시퀀스 생성 및 확인
--시퀀스 생성
CREATE SEQUENCE SEQ_DPET_SEQUENCE
INCREMENT BY 10
START WITH 10
MAXVALUE 90
MINVALUE 0
NOCYCLE
CACHE 2
ORDER;
--시퀀스 확인
SELECT * FROM USER_SEQUENCES;
3) 시퀀스 생성 번호 사용해 데이터 추가
--시퀀스에서 생성한 번호 사용하여 데이터 추가
--SEQ_DEPT_SEQUENCE.NEXTVAL : 다음 번호 생성
INSERT INTO DEPT_SEQUENCE (DEPTNO,DNAME,LOC)
VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE','SEOUL');
SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO;
--SEQ_DEPT_SEQUENCE.CURRVAL: 현재 번호 = 마지막으로 생성된 번호
SELECT SEQ_DEPT_SEQUENCE.CURRVAL
FROM DUAL;
4) 시퀀스 7번 실행하기
--실행만 7번
INSERT INTO DEPT_SEQUENCE (DEPTNO,DNAME,LOC)
VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE','SEOUL');
SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO;
--1번 더 실행하면 에러, 이미 최대값 도달했기 때문* while문 이용 시
--while문을 통한 실행 (AI)
DECLARE
i INTEGER := 1;
BEGIN
WHILE i <= 7 LOOP
INSERT INTO DEPT_SEQUENCE (DEPTNO, DNAME, LOC)
VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE', 'SEOUL');
i := i + 1;
END LOOP;
END;
/
SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO;
5) 시퀀스 수정하기
--시퀀스 수정
ALTER SEQUENCE SEQ_DEPT_SEQUENCE
INCREMENT BY 3
MAXVALUE 99
CYCLE;
--시퀀스 확인
SELECT * FROM USER_SEQUENCES;
--수정한 시퀀스 이용하여 데이터 추가
INSERT INTO DEPT_SEQUENCE (DEPTNO,DNAME,LOC)
VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE','SEOUL');
SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO;
--3번만 반복 실행
--최대값 도달, 다시 2번만 반복 실행
--CYCLE로 설정하였기 떄문에 시작번호(최소값)으로 돌아감
--
--시퀀스 삭제
DROP SEQUENCE SEQ_DEPT_SEQUENCE;
--삭제한 시퀀스 확인
SELECT * FROM USER_SEQUENCES;
2-3. 객체 종류 - 동의어
0) 정의
1) 권한부여
--동의어 SYNONYM
--동의어 = 공식적 별칭 = 일회성이 아님
--SELECT 나 FROM 절에서 사용하는 열이나 테이블의 별칭과 다름 = 일회성
--SQLPLUS 에서 SYSTEM/ORACLE로 로그인
--SCOTT계정에게 동의어 생성 권한 부여 (PUBLIC도 포함)
GRANT CREATE SYNONYM TO SCOTT ;
GRANT CREATE PUBLIC SYNONYM TO SCOTT;
2) 동의어 생성 및 확인 그리고 삭제
--토드에서 SCOTT 계정으로 실행
CREATE SYNONYM E FOR EMP;
--동의어 사용
SELECT * FROM E;
--동의어 삭제
DROP SYNONYM E;
--삭제된 동의어 확인
SELECT * FROM E;
SELECT * FROM EMP;
3. 제약 조건
0) 정의
* 무결성의 종류
- 영역 무결성 (열) : 값의 적절함
- 개체 무결성 (행) : 기본키
- 참조 무결성 : 외래키
* 제약조건
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT
1) 제약조건
--제약조건
--빈값 허용하지 않음 = NOT NULL
CREATE TABLE TABLE_NOTNULL(
LOGIN_ID VARCHAR2(20) NOT NULL,
LOGIN_PWD VARCHAR2(20) NOT NULL,
TEL VARCHAR2(20)
);
SELECT * FROM TABLE_NOTNULL;
DESC TABLE_NOTNULL;
2) NULL 값의 추가
--빈 값 추가 안됨 (제약조건에 어긋남)
INSERT INTO TABLE_NOTNULL (LOGIN_ID, LOGIN_PWD, TEL)
VALUES ('TEST_ID_01',NULL,'010-1234-5678');
SELECT * FROM TABLE_NOTNULL;
--빈 값 추가 됨 (제약 조건이 없음)
INSERT INTO TABLE_NOTNULL (LOGIN_ID, LOGIN_PWD)
VALUES ('TEST_ID_01', '1234');
SELECT * FROM TABLE_NOTNULL;
--빈 값으로 변경 안됨(제약조건에 어긋남)
UPDATE TABLE_NOTNULL
SET LOGIN_PWD = NULL
WHERE LOGIN_ID = 'TEST_ID_01';
3) 제약조건 확인
--제약조건 확인
--C: CHECK / NOT NULL, U: UNIQUE, P: PRIMARY, R: REFERENCE
SELECT * FROM USER_CONSTRAINTS;
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TALBE_NAME
FROM USER_CONSTRAINTS;
4) 제약조건 이름 지정
--제약조건 이름 지정
CREATE TABLE TABLE_NOTNULL2(
LOGIN_ID VARCHAR2(20) CONSTRAINT TBLNN2_LGNID_NN NOT NULL,
LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLNN2_LGNPW_NN NOT NULL,
TEL VARCHAR2(20));
--제약조건 확인
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;
5) 제약조건 추가 안 됨 (빈 값이 있음)
--제약조건 추가 안 됨 (빈 값이 있음)
ALTER TABLE TABLE_NOTNULL
MODIFY (TEL NOT NULL);
DESC TABLE_NOTNULL;
SELECT * FROM TABLE_NOTNULL;
6) 빈 값을 전화번호로 변경 // 전화번호도 NULL값 불가
--빈 값을 전화번호로 변경
UPDATE TABLE_NOTNULL
SET TEL = '010-1234-5678'
WHERE LOGIN_ID = 'TEST_ID_01';
SELECT * FROM TABLE_NOTNULL;
--
ALTER TABLE TABLE_NOTNULL
MODIFY (TEL NOT NULL);
DESC TABLE_NOTNULL;
SELECT * FROM TABLE_NOTNULL;
7) 제약조건 확인, 이름 지정, 확인 (정리)
--제약조건 확인
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;
--제약조건 이름 지정해서 추가
ALTER TABLE TABLE_NOTNULL2
MODIFY(TEL CONSTRAINT TBLNN_TEL_NN NOT NULL);
--제약조건 확인
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;
DESC TABLE_NOTNULL2;
팁
1. DDL은 자동 커밋, DML은 자동 커밋이 안 됨.
2. 데이터 테이블은 복수형이라는게 공통점임. (데이터 뷰 조회시 마지막에 S를 입력해 복수형으로 조회)
3. 기본키 : NULL값이 존재할 수 없음, 중복이 될 수 없음.
4. INDEX를 만들 때는 ON을 사용해서 열 값을 입력해야 함
5. VIEW에서 AS문으로 사용
핵심
1. 객체 종류 : 데이터 사전 뷰, 인덱스, 뷰, 시퀀스,시노님
'배운 책들 정리 > 오라클로 배우는 데이터베이스 입문' 카테고리의 다른 글
| 0209 오라클 DB 입문 - PL/SQL, 커서와 예외처리 (0) | 2023.02.09 |
|---|---|
| 0208 오라클 DB 입문 - 제약 조건 및 사용자, 권한, 롤 관리 (0) | 2023.02.08 |
| 0206 오라클 DB 입문 - 데이터 조작어, 트랜잭션 제어와 세션 (0) | 2023.02.06 |
| 0203 오라클 DB 입문 - 조인(JOIN), 서브쿼리, DML (0) | 2023.02.03 |
| 0202 오라클 DB 입문 - 오라클 함수 (1) | 2023.02.02 |
