[오라클복습 Day12] DDL, DML, DCL, TCL, 테이블 생성, FK 2개로 PK 복합키 만들기, ROWNUM, 문자열, 숫자 관련 함수

객체 

: 테이블, 시퀀스(일련번호 발생), 뷰, User, 동의어(시노님), 인덱스 

 

DDL -Create, Alter, Drop

DML- Insert, Delete, Update, Selete 

DCL(관리자가 부여하는 명령문)- Grant, Revoke  일반적으로 권한들을 묶어서 한꺼번에 부여, 이렇게 묶인 권한을 Role이라고 한다.   

TCL

- Transaction :  commit, rollback, savepoint

원본은 하나고 복사본에 작업을 하는데 commit을 하면 복사본의 내용이 원본에 덮어쓰기 하는 것. 

 

 

desc 내림차순 

asc 오름차순 

 

 

<Select절>

select [distinct] 컬럼명(*)

from 테이블명

where 조건절

order by 컬럼명 [desc]; 

 

1) 비교연산자 

: > < >= <= = !=

 

2) and, or, not 

 

3) 컬럼명 between A and B(컬럼명이 같은 경우에만 사용할 수 있다. A <= 컬럼명 <=  B)

 

4) 컬럼명 in (A, B, C) : 특정 컬럼의 값이 A,B,C이냐 물어보는 것이기 때문에 범위연산자가 아니다. 

 GRADE VARCHAR2(1) CONSTRAINT MEMBERS_GRADE_CK CHECK (GRADE IN ('1', '2','3')),

 

5) 와일드카드 (% _)

컬럼명 Like '%문자%';

 

6) 컬럼명 is null;

컬럼명 is not null; 

 

7) 산술 연산자 + - * /

 

 

데이터 모델링 

테이블을 만들기 위해 모델링 작업 => 어떤 컬럼 , 중복되는 데이터가 발생하는지 확인 

- 테이블에 저장될 데이터들을 수집(어떤 데이터가 필요한지 확인), 컬럼 정의, 타입 정의

테이블을 분할했을 때 잘 되었는지 확인하는 작업을 정규화작업, 너무 정규화되었을 때 다시 반대대는 작업을 하는 것이 역정규화 업무를 진행! 후 테이블 생성 create 

 

 

테이블 생성

CREATE TABLE 테이블명 

(

    컬럼명1 타입 [제약조건1],

    컬럼명2 타입 [제약조건2],

    컬럼명3 타입 [제약조건3]

);

 

 

*제약조건 

: PK, FK, NN, CHECK, UNIQUE, DEFAULT 

: 동시에 주고 싶으면 UNIQUE NOT NULL이라고 연달아 적으면 된다. 

CREATE TABLE CATEGORY
(
    C_ID VARCHAR2(20) PRIMARY KEY,
   GENRE VARCHAR(50) CONSTRAINT CATEGORY_GENRE_UKNN UNIQUE NOT NULL
);

 

SELECT * FROM USER_CONSTRAINTS;

메타테이블(테이블을 관리하는 테이블, 실재 존재하는 테이블이 아니다. USER_CONSTRAINTS)

==> 제약조건을 관리해주는 테이블에 그 값이 등록 

 

 

개발자가 제약조건 이름을 줄 때 

USERID 타입 constraints 테이블명_PRIMARY KEY 

 

Oracle이 제약조건의 이름을 알아서 짓는다. 

USERID 타입 PRIMARY KEY 

 

 

▶오라클 타입의 종류 

문자 : char(문자 길이, 고정길이), varchar2(길이, 가변길이), Nvarchar는 유니코드를 저장할 때 

숫자 : number, number(크기), number(크기, 소수점크기)

날짜 : date, timestamp(시분초까지 필요한 경우)  

크기가 큰데이터 : CLOB, BLOB (Large of Binary) 

 

 

테이블이든 컬럼이든 키워드인 KEYWORD를 굳이 꼭 쓰고 싶을 경우 " "을 이용하면 된다. 

 "CONTENT" VARCHAR2(100),

 

 

ON DELETE CASCADE

: 부모가 죽으면 자식도 사라짐 

: CASCADE 는 부모 테이블의 row 에 DELETE 또는 UPDATE 명령어를 적용할 때, 자동적으로 자식 테이블의 매치되는 row 에도 똑같이 DELETE 또는 UPDATE 를 반영하는 것을 의미

:  USERID VARCHAR2(50)REFERENCES MEMBERS(USERID) ON DELETE CASCADE

 

ON UPDATE CASCADE

: 수정시 둘 다 수정되지만 그러나 오라클에는 존재하지 않음. 

 

 

default는 제약조건이 아니기 때문에 먼저 쓰고 constraint를 나중에 적어야 한다. 

:  P_DATE DATE DEFAULT SYSDATE CONSTRAINT PRODUCT_DATE_NN  NOT NULL,

 

 

PRIMARY 복합키 만드는 방법

: FOREIGN KEY 두개를 합쳐서 하나의 유일한 키 PRIMARY KEY 만들기  

CREATE TABLE WISHLIST
(
    CUST_ID VARCHAR2(100) CONSTRAINT WISHLIST_ID_FK REFERENCES CUSTOMER(CUST_ID),
    PRODUCT_ID CHAR(10) CONSTRAINT WISHLIST_PID_FK REFERENCES PRODUCT(PRODUCT_ID),
    LIST_DATE DATE DEFAULT SYSDATE, 
     CONSTRAINT WISHLIST_PK PRIMARY KEY(CUST_ID, PRODUCT_ID)
);

 

SEQUENCE 생성 및 몇 개있는지 확인 

: CREATE SEQUENCE ORDER_SEQ; 

: SELECT ORDER_SEQ.NEXTVAL FROM DUAL;
: SELECT ORDER_SEQ.CURRVAL FROM DUAL;

 

DUAL은 존재하지 않는 가상테이블

: SELECT * FROM DUAL;라고 출력을 해도 DUMMY 라고 출력됨, 존재하지 않기 때문   

 

ROWNUM

: 오라클에서 제공하는 가상의 데이터, 동적인 일련번호, 데이터베이스에서 관리하기 위한 용도

: SELECT ROWNUM, REPLY. * FROM REPLY; 

: SELECT ROWNUM "일련번호", REPLY. * FROM REPLY; 

 

UPDATE

: 자식이 있는 경우엔 수정이 불가하다. 자식이 없는 경우에는 수정 가능! 

 

 

<SELECT문 중 가장 중요하고 많이 사용하는 기능>  
1) 함수 이용

: 특정 기능을 지정해서 처리(단일행 함수, 그룹 함수)

▶단일행 함수

:  각각의 ROW를 개별적으로 처리 

▶그룹행 함수

: 그룹화시켜 처리하는 함수 (SUM, AVG, COUNT, MIN, MAX)  

▶사용자 정의 함수 

 

 

<문자 관련 함수>UPPER, LOWER, SUBSTR

: SELECT FIRST_NAME, UPPER(FIRST_NAME), LOWER(FIRST_NAME), SUBSTR(FIRST_NAME, 3) FROM EMPLOYEES;

: SUBSTR(FIRST_NAME, 3) 3번째부터 쭉

: SUBSTR(FIRST_NAME, 1, 3) 첫번째부터 3번째까지

 

▶ || 

: SELECT FIRST_NAME || '님' FROM EMPLOYEES;

: 출력값 

Ellen님
Sundar님
Mozhe님

 

<숫자 관련 함수>

: COS(), TRUNC(), CEIL(), FLOOR(), POWER() 

: SELECT ABS(15.3) FROM DUAL; 출력값 15.3

: SELECT CEIL(15.3) FROM DUAL; 출력값 16 

: SELECT FLOOR(15.3) FROM DUAL; 출력값 15

: SELECT TRUNC(15.3) FROM DUAL; 출력값 15 소수점을 그냥 버리는 역할을 한다. 

: SELECT MOD(10, 3), POWER(10, 3) FROM DUAL; MOD는 나머지 구하는 함수, POWER는 제곱근 출력값 1 1000

: SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL; ADD_MONTHS 현재 날짜에서 한달을 더한 값 출력

: SELECT LAST_DAY(SYSDATE), LAST_DAY('20/02/01') FROM DUAL;  출력값 22/01/31 20/02/29

: SELECT MONTHS_BETWEEN(SYSDATE, '1919/03/01') FROM DUAL; 날짜간의 차이를 개월 수로 리턴해주는 함수 출력값 1234.409693100358422939068100358422939068

 

2) 조인

3) 서브쿼리