Oracle SQL 요약정리 - DDL 계정생성, DDL 개념,

안녕하세요

여니입니다 :)

 

 

 

-- 계정명 DDL, 비밀번호 DDL
CREATE USER DDL IDENTIFIED BY DDL;
-- 부여할 권한 : 사용자가 DB에 접속 가능하기 위한 CREATE SESSION이 담겨있는 롤권한
--             CREATE구문을 사용할 수 있는 권한들이 뭉쳐져있는 롤권한

GRANT RESOURCE, CONNECT TO DDL;

---

 

*  DDL(DATA DEFINITION LANGUAGE) : 데이터 정의 언어
    
    오라클에서 제공하는 객체(OBJECT)를
    새로이 만들고(CREATE) 구조를 변경하고(ALTER) 구조를 삭제(DROP)하는 명령문
    즉, 구조자체를 정의하는 언어는 DB관리자, 설계자가 사용함.
    
    오라클에서 객체(DB를 이루는 구조물들)
    테이블(TABLE), 사용자(USER), 함수(FUNCTION), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스 등등...

 

---

 

<CREATE TABLE>
    
    테이블 : 행(ROW) , 열(COLUMN)로 구성되는 가장 기본적인 데이터베이스 객체 종류중 하나
            모든 데이터는 테이블을 통해서 저장됨(데이터를 조작하고자하려면 무조건 테이블을 만들어야한다)
            
    [표현법]
    CREATE TABLE 테이블명(
    컬럼명 자료형,
    컬럼명 자료형,
    컬럼명 자료형,
    ...
    )
    <자료형>
    - 문자  (CHAR(크기)/VARCHAR2(크기)) : 크기는 BYTE 수
                                    (숫자,영문자,특수문자 => 1글자당 1BYTE)
                                    (한글 => 1글자단 2/3BYTE)

                 CHAR(바이트수) : 최대 2000BYTE까지 지정가능
                                              ★고정길이 (아무리 적은값이 들어와도 공백으로 채워서 처음 할당한 크기를 유지하겠다.)
                                              주로 들어올 값의 글자수가 정해져 있을 경우 사용
                                              EX) 성별 : 남/여, M/F
                                                     주민번호 : 6-7 => 14글자 => 14BYTE

 ★많이사용  VARCHAR2(바이트수) : 최대 4000BYTE까지 지정가능
                                                            가변길이(적은 값이 들어올 경우 그 담긴 값에 맞춰서 크기가 줄어든다)
                                                            주로 들어올 값의 글자수가 정해져있지 않은경우 사용
                                                            예) 이름, 아이디, 비밀번호, 이메일...
                 숫자 (NUMBER) : 정수/실수 상관없이 NUMBER
                 날짜 (DATE)   : 년/월/일/시/분/초 형식으로 시간을 지정

 

 

 

ex) 테이블 생성

CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20), -- 대소문자 구분 X, 낙타등표현법을 쓸수없음 -> 언더바로 구분함.
    MEMER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR2(20),
    MEMBER_BDATE DATE
);

 

ex) 테이블 확인방법1

SELECT * FROM MEMBER;

ex) 테이블 확인방법2 : 데이터 딕셔너리 이용
-- 데이터 딕셔너리  : 다양한 객체들의 정보를 저장하고 있는 시스템 테이블

SELECT *
FROM USER_TABLES;
-- USER_TABLES : 현재 이 사용자 계정이 가지고 있는 테이블들의 전반적인 구조를  확인할수 있는 데이터 딕셔너리.

SELECT *
FROM USER_TAB_COLUMNS; -- 컬럼들 확인하는 방법

--; 직접 계정목록에서 계정누르고 테이블 들어가서 확인할 수도 있음

---

* 칼럼에 코멘트 달기(칼럼에 대한 설명)
    [표현법]
    COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

ex) COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원가입';

 

 

* INSERT (데이터를 추가할 수 있는 구문) => DML문
-- 한 행으로 추가(행을 기준으로 추가), 추가할 값을 기술(값의 순서 중요!)

    [표현법]
    INSERT INTO 테이블명 VALUES(첫번째 칼럼의 값, 두번째 칼럼의 값, ... )

ex) INSERT INTO MEMBER VALUES('user01','pass01','홍길동','99/05/10');
INSERT INTO MEMBER VALUES('user02','pass02','김갑생','1980-10-06');
INSERT INTO MEMBER VALUES('user03','pass03','박말똥',SYSDATE);

INSERT INTO MEMBER VALUES(NULL,NULL,NULL,SYSDATE); -- 아이디, 비밀번호, 이름에 NULL값이 존재해도 되나?
INSERT INTO MEMBER VALUES('user03','pass03','박말똥',SYSDATE); -- 중복된 아이디가 존재해도 되나?

-- 위의 NULL 값이나 중복된 아이디 값은 유효하지 않은 값들이다.
-- 유효한 데이터값을 유지하기 위해서 제약조건을 걸어줘야한다.

---

<제약조건 CONSTRAINTS>
    
    - 원하는 데이터값만 유지하기 위해서 (보관하기 위해서) 특정 컬럼마다 설정하는 제약
      (데이터 무결성 보장을 목적으로)
    - 제약조건이 부여된 컬럼에 들어올 데이터에 문제가 있는지 없는지 자동으로 검사할 목적
    
    - 종류 : NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY
    
    - 컬럼에 제약조건을 부여하는 방식 : 컬럼레벨방식 / 테이블레벨방식

---

1. NOT NULL 제약조건
        해당 컬럼에 반드시 값이 존재해야만 할 경우 사용
        => 즉 NULL값이 절대 들어와서는 안되는 컬럼에 부여하는 제약조건
           삽입/수정시 NULL값을 허용하지 않도록 제한하는 제약조건
           
        + 컬럼레벨 방식으로만 등록 가능.

 

ex)

-- NOT NULL 제약조건을 설정한 테이블 만들기
-- 컬럼레벨방식 : 컬럼명 자료형 제약조건 => 제약조건을 부여하고자하는 컬럼 뒤에 곧바로 기술하는 방법
CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

ex)

INSERT INTO MEM_NOTNULL
VALUES (1, 'user01', 'pass01', '소연', '여', '010-1111-1111', 'thdusthdus@naver.com');

+ DDL 계정에 MEM_NOTNULL 테이블에 NOTNULL 제약조건이 부여된 위의 칼럼들에 NULL값이 들어가면 오류발생

 

INSERT INTO MEM_NOTNULL
VALUES (1, 'user01', 'pass01', '소연',NULL,NULL,NULL);

+ DDL 계정에 MEM_NOTNULL 테이블에 NOTNULL 제약조건이 부여되지 않은 칼럼들에 NULL값이 들어가도 오류 안생김

 

 

2. UNIQUE 제약조건
        칼럼에 중복값을 제한하는 제약조건
        삽입/수정시 기존에 
        추가, 수정이 되지 않게 제약
        
     + 칼럼/ 테이블 레벨방식 둘다 가능

 

ex)CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, --컬럼레벨방식
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

 

* DROP TABLE MEM_UNIQUE;  -- 테이블 삭제

 

-- 테이블 레벨방식 : 모든 칼럼을 다 기술하고, 그 이후에 제약조건을 나열
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
    UNIQUE(MEM_ID) -- 테이블 레벨 방식
);

 

ex)

INSERT INTO MEM_UNIQUE
VALUES(1,'user02','pass01','thdus','ksy','010-1111-1111','thdusthdus@naver.com');

 

UNIQUE 제약조건에 위배되었으므로 INSERT실패
    어느컬럼에 어느 문제가 있는지 구체적으로 알려주지 않음
    DDL.SYS_C007062 : 제약조건의 이름으로만 제약조건 위배를 알려준다.
    제약조건 부여시 직접 제약조건명(☆)을 지정해주지 않으면 시스템에서 임의의 제약조건명을 부여해줌.