Oracle SQL DDL - accidentlywoo/sec GitHub Wiki

[DML] 계정 만들기

Data Dictionary -> user_tables

sqlplus

system 계정 접속

계정 정보확인

show user

성공 실행결과

USER is "SYSTEM"

test 계정 만들기

CREATE USER test IDENTIFIED BY test;

성공 실행결과

User created.

생성된 'test'계정에 접속 시도

conn test/test

실패 결과

user TEST lacks CREATE SESSION privilege; logon denied

사용자들의 연결은 세션이 필요하다 -> Session

system 계정으로 다시 접속

test계정에 권한 주기

GRANT CREATE SESSION to test;

성공 결과

Grant succeeded.

test 계정 접속 시도

conn test/test

성공 결과

Connected.

세션이 주어지고, 접속이 성공한다. 테이블을 생성해 보자.

CREATE TABLE test(a number);

실패 결과

CREATE TABLE test(a number)
*
ERROR at line 1:
ORA-~~: insufficient privileges;

생성권한을 줘야 한다.

system 계정으로 다시 접속

GRANT CONNECT, RESOURCE to test;

GRANT는 ROLE을 줄 수도 있고, 개별적인 권한을 줄 수도 있다.

다시 test 계정으로 접속 테이블을 생성해 보자.

CREATE TABLE test(a number);

성공 결과

Table created.

테이블 생성

CREATE TABLE 테이블명(
a CHAR,
a1 CHAR(3),
a2 VARCHAR2(3)
)

CHAR타입은 문자열 길이를 지정하지 않아도 테이블 생성이 가능, (CHAR는 MAXIMUM 1자리로 생성됨 / NUMBER 문자길이를 지정하지 않으면 최댓값으로 생성)

But VARCHAR는 문자길이를 꼭 지정해줘야 한다.(생성 에러)

• CHAR은 고정 문자길이
• VARCHAR (오라클은 VARCHAR2사용) 문자 길이만큼 저장

오라클에서 제공하는 함수를 이용해서 컬럼의 문자열 길이를 알 수 있다.

SELECT LENGTH(a), LENGTH(a1), LENGTH(a2) FROM 테이블명;

CREATE TABLE 테이블명(
dt DATEm
dt1 TIMESTAMP
);

-DATE : DATE에도 년월일 시분초 정보가 들어있지만, 출력할때는 년월일만 보여준다. -TIMESTAMP : 밀리 세컨즈 단위의 자세한 시간정보를 보고싶을 땐, TIMESTAMP를 사용하자.

날짜타입의 데이터는 자릿수 지정 절대하면 안된다.

DATE타입으로할까? VARCHAR타입으로할까?

• 자동으로 날짜발급 -> DATE / 입력을 받아야 되는 경우 -> 문자타입

INSERT INTO 테이블명 VALUES(SYSDATE, SYSTIMESTAMP);

SELECT * FROM 테이블명;

성공 결과

DT                DT1
------------------------------------------
20/06/08          20/06/08 10:45:52.109000

SELECT TO_CHAR(dt,'YY/MM/DD HH24:MI:SS'), dt1 FROM 테이블명;
DT                        DT1
------------------------------------------
20/06/08 10:45:52         20/06/08 10:45:52.109000

아주 정확한 표현은 TIMESTAMP를 사용한다.

테이블 만들기 + 제약조건

Data Dictionary -> User Constraints

제약 조건 정의

구문

CREATE TABLE [schema] table
    (column datatype [DEFAULT expr]
    [column_constraint].
    ...
    [table_constraint][,...]);

열 레벨 제약 조건 구문

column [CONSTRAINT constraint_name] constraint_type,

테이블 레벨 제약 조건 구문

column,...
   [CONSTRAINT constraint_name] constraint_type
   (column, ...),

-> 테이블 레벨 제약조건 사용을 권장한다.

제약 조건 유형 1.NOT NULL 2. UNIQUE 3. PRIMARY KEY 4. FORIEGN KEY 5. CHECK

예시를 보자

CREATE TABLE 사원(
    사번 VARCHAR2(10) CONSTRAINT 사원_사번_pk PRIMARY KEY,
    이름 VARCHAR2(20) NOT NULL,
    직무 ID VARCHAR2(6),
    주민번호 CHAR(13),
    급여 NUMBER(7),
    CONSTRAINT 사원_직무ID_fk FOREIGN KEY (직무ID) REFERENCES 직무(직무ID),
    CONSTRAINT 사원_주민번호_uq UNIQUE (주민번호),
    CONSTRAINT 사원_급여_ck CHECK (급여 >=0)
);

INSERT INTO 직무(직무ID, 직무명) VALUES ('A', '인사업무');
INSERT INTO 사원(사번, 이름, 직무ID, 급여) VALUES ('s1','n1','A', 1000);

DELETE FROM 직무 WHERE 직무ID = 'A'; -- ERROR

부모 테이블에서 참조되고있는 자식테이블이 있을때 에러 발생

사원 테이블 생성시

~, CONSTRAINT 사원_직무ID_fk FOREIGN KEY (직무ID) REFERENCES 직무(직무ID) ON DELETE CASCADE

자식 테이블을 생성할 때, 참조하는 부모 테이블의 정보가 삭제되면 같이 삭제되겠다는 제약조건! 업무에선 잘 사용하지 않는다.