<Database>DDL

이제내손을java...·2024년 3월 20일
DDLDatabaseoracle

Database 개념정리

목록 보기
6/11

이제는 몇번째인지도 헷갈리는 DATABASE 개념 정리!
오늘은 DDL에 대해 정리해보았습니다😲😲

DDL

테이블을 생성하는 것!

테이블 생성

  • CREATE TABLE 을 이용해서 생성함
    -- 테이블은 데이터들을 저장하는 저장소
    -- 테이블내 각컬럼에 데이터를 저장
    -- 컬럼(각 저장소)는 TYPE이 있음
    -- 오라클에서 제공하는 TYPE
    -- 숫자 : NUMBER 정수, 실수
    -- 문자 : CHAR, VARCHAR2, NVARCHAR2, LONG, CLOB
    -- 날짜 : DATE, TIMESTAMP

테이블 생성하는 방법

  • 표현법
CREATE TABLE 테이블명(
컬럼명 TYPE() [제약조건] ,
컬럼명 TYPE[()] [제약조건],......--)

문자열 타입 설정

  1. CHAR : 고정크기 문자열 저장소
  2. VARCHAR2 : 가변크기 문자열 저장소
  3. NVARCHAR2 : 가변크기 확장적 문자열들을 저장

EX)
테이블 TBL_STR을 A컬럼은 CHAR(6),B컬럼은 VARCHAR(6),C컬럼은 NVLCHAR2(6)으로 생성하고, 각 테이블안에 INSERT로 타입에 맞는 데이터를 넣어줌 ⤵️

  • 테이블 생성
CREATE TABLE TBL_STR(
    A CHAR(6),
    B VARCHAR2(6),
    C NVARCHAR2(6));
  • 데이터 삽입
SELECT * FROM TBL_STR;
INSERT INTO TBL_STR VALUES('ABC','ABC','ABC');
INSERT INTO TBL_STR VALUES('ABCDEFG','ABCDEF','ABCDEF');
INSERT INTO TBL_STR VALUES('가나','오나','자나');
INSERT INTO TBL_STR VALUES('가나다','오나다','자나다');
INSERT INTO TBL_STR VALUES('가나','오나','자나다라바마');
  • 조회
SELECT LENGTHB(A), LENGTHB(B), LENGTHB(C)
FROM TBL_STR;

숫자형 자료형

  1. NUMBER : 표현가능한 모든 숫자 저장함
  2. NUMBER(PRECISION, SCALE) : 숫자저장에 제한설정
  3. PRECISION : 저장가능한 숫자의 전체 자리수(1~38)
  4. SCALE : 소수점 자리수설정(-84~127)

EX) TBL_NUM에 숫자형 자료형들로 컬럼ABCD를 만들어서 각 타입에 맞는 데이터 넣어주고 조회 ⤵️

  • 테이블 생성
CREATE TABLE TBL_NUM(
    A NUMBER,
    B NUMBER(5),
    C NUMBER(5,1),
    D NUMBER(5,-2));
  • 데이터 삽입
SELECT * FROM TBL_NUM;
INSERT INTO TBL_NUM VALUES(1234.567,1234.567,1234.567,1234.567);
INSERT INTO TBL_NUM VALUES(1234.123,1234.123,1234.123,1234.123);
INSERT INTO TBL_NUM VALUES(123400.12300,12340.123,1234.123,12340000.123);

날짜형

  1. DATE : 년, 월, 일 저장하는 자료형
  2. TIMESTAMP : 년, 월, 일 시, 분 초 저장하는 자료형

EX)
날짜타입의 컬럼들이 있는 테이블을 생성해주고 날짜 데이터 삽입⤵️

  • 테이블 생성
CREATE TABLE TBL_DATE(
    BIRTHDAY DATE,
    RENT_START TIMESTAMP);
  • 데이터 삽입
INSERT INTO TBL_DATE VALUES(SYSDATE,SYSTIMESTAMP);

날짜데이터를 형변환해서 저장하기

  1. 문자형태로 저장
INSERT INTO TBL_DATE VALUES('24/02/13','24/02/13 09:00');
  1. 형변환해서 저장하기 
INSERT INTO TBL_DATE VALUES
(TO_DATE('24/02/13','YY/MM/DD'),TO_TIMESTAMP('24/02/13 09:00','RR/MM/DD HH24:MI'));

문자열저장시 데이터 크기가 엄청 클 때

  • VARCHAR2() -> 최대 4천바이트
    -- LONG, CLOB을 사용한다.
  • 테이블 생성
CREATE TABLE TBL_STR2(
    TESTSTR VARCHAR2(4000),
    TESTSTR2 CLOB
);
  • 데이터 삽입
SELECT * FROM TBL_STR2;
DESC TBL_STR2;
INSERT INTO TBL_STR2 VALUES('엄청긴문자열이있다고침');

테이블 생성시에 컬럼에 제약조건 설정하기

  1. NOT NULL
  2. UNIQUE
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. CHECK

제약조건을 설정하는 방식

컬럼레벨에서 설정 : 컬럼한개에 제약조건을 설정할때
테이블레벨에서 설정 : 여러컬럼을 묶어서 제약조건을 설정할때

  • 표현법
CREATE TABLE 테이블명(
 컬럼명 타입명 [제약조건] 컬럼레벨,
 컬럼명 타입명 [제약조건],
 [제약조건] 테이블레벨
)

NOT NULL (C)

설정된 컬럼에는 NULL값을 저장하지 않게 하는 제약조건 , 컬럼은 별다른 제약조건이 없으면 NULLABLE(DEFUALT)이다.

  • 컬럼레벨에서만 설정이 가능함👀
  • NOTNULL제약조건 설정하기 -> MEMEBER_PWD,MEMBER_ID는 컬럼값이 NULL을 갖지 못함 기본적으로 아이디와 패스워드는 비어있으면 안되는 값
CREATE TABLE NN_MEMBER(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(50) NOT NULL,
    MEMBER_PWD VARCHAR2(50) NOT NULL,
    MEMBER_NAME VARCHAR2(20),
    MEMBER_AGE NUMBER
--    NOT NULL(MEMBER_ID)
);
INSERT INTO NN_MEMBER VALUES(NULL, 'ADMIN',NULL,NULL,NULL);
INSERT INTO NN_MEMBER VALUES(NULL, 'ADMIN','1234',NULL,NULL);

UNIQUE (U)

설정된 컬럼에 중복데이터를 저장할 수 없게 하는 기능이다.

  • 컬럼레벨, 테이블레벨에서 설정👀
  • 다수컬럼에 UNIQUE제약조건을 설정할 수 있다.
  • 다수의 테이블은 테이블 레벨에서만 가능
CREATE TABLE UQ_MEMBER2(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(50),
    MEMBER_PWD VARCHAR2(50) NOT NULL,
    MEMBER_NAME VARCHAR2(20),
    MEMBER_AGE NUMBER,
    UNIQUE(MEMBER_ID,MEMBER_NAME)
);

PRIMARY KEY(P)

설정된 컬럼에 중복, NULL 둘다 안되게하는 기능이다.

  • 일반적으로 모든 테이블에 한개 컬럼에 설정👀
  • 실제 사용하는 컬럼 / 사용하지않는 컬럼을 생성해서 설정
  • 컬럼레벨, 테이블레벨 모두 가능
  • 기준이 되는 컬럼
CREATE TABLE PK_MEMBER(
    MEMBER_NO NUMBER PRIMARY KEY,
    MEMBER_ID VARCHAR2(50) UNIQUE NOT NULL,
    MEMBER_PWD VARCHAR2(50) NOT NULL,
    MEMBER_NAME VARCHAR2(20),
    MEMBER_AGE NUMBER
    --첫번째 줄 없애고 PRIMARY KEY(MEMBER_NO)를 써줘도 됨, PRIMARY도 다중으로 묶어서 사용할 수 있음-> PRIMARY KEY(MEMBER_NO,MEMBER_ID)
);

FORENIN KEY(R)

컬럼에 저장되는 데이터가 다른테이블에 있는 값만 저장하게 하는 것

  • 참조관계를 설정 -> 참조되는 테이블(부모) 참조하는 테이블(자식)
  • 참조되는 테이블에서 선택된 컬럼은 PK거나 UNIQUE제약조건이 설정이 되어있어야 한다.
  • 테이블 생성
CREATE TABLE FK_BOARD(
    BOARD_NO NUMBER PRIMARY KEY,
    BOARD_TITLE VARCHAR2(200) NOT NULL,
    BOARD_CONTENT VARCHAR2(2000),
    BOARD_WRITER VARCHAR2(20), --NOT NULL REFERENCES UQ_MEMBER(MEMBER_ID), --REFERENCES 테이블명(컬럼명),
    BOARD_DATE DATE,
    FOREIGN KEY(BOARD_WRITER) REFERENCES UQ_MEMBER(MEMBER_ID)
);
  • UQ_MEMBER 데이터 삽입
SELECT * FROM UQ_MEMBER;
INSERT INTO UQ_MEMBER VALUES(4,'ADMIN','1234','관리자',44);
INSERT INTO UQ_MEMBER VALUES(5,'USER01','1111','유저일',11);
INSERT INTO UQ_MEMBER VALUES(6,'BS','1111','유저일',11);
  • FK_BOARD 데이터 삽입
INSERT INTO FK_BOARD VALUES(1,'나는 오늘 db를 즐기고있다.',NULL,'GDJ79',SYSDATE);
INSERT INTO FK_BOARD VALUES(2,'참조관계에 NULL값은?.',NULL,NULL,SYSDATE);
INSERT INTO FK_BOARD VALUES(3,'참조관계에 NULL값은?.',NULL,'BS',SYSDATE);
SELECT * FROM FK_BOARD;

🖐️문제풀이

  • 댓글테이블 구성하기
  • 댓글번호, 내용, 작성자(FK), 작성일, 게시글번호(FK)
CREATE TABLE FK_BOARD_COMMENT(
    COMMENT_NO NUMBER PRIMARY KEY,
    COMMENT_CONTENT VARCHAR2(1000) NOT NULL,
    COMMENT_WRITER VARCHAR2(20) REFERENCES UQ_MEMBER(MEMBER_ID),
    BOARD_NO NUMBER REFERENCES FK_BOARD(BOARD_NO),
    COMMENT_DATE DATE
);

INSERT INTO FK_BOARD_COMMENT VALUES(1, 'MJ 두끼 힘내요! 아랫배?','GDJ79',1,SYSDATE);
INSERT INTO FK_BOARD_COMMENT VALUES(2, '헌수 MJ 그만 눌러! 아랫배?','USER01',1,SYSDATE);
SELECT * FROM FK_BOARD_COMMENT;

FK제약조건이 설정된 부모테이블의 데이터는 함부로 삭제 할 수 없다.

FK 설정시 삭제에 대한 옵션을 설정할 수 있음
1. ON DELETE SET NULL : 참조값이 삭제되면 NULL로 설정 / 컬럼에 NOT NULL 제약조건을 설정하면 안됨!
2. ON DELETE CASCADE : 참조값이 삭제되면 같이 삭제됨

CREATE TABLE FK_BOARD_COMMENT(
    COMMENT_NO NUMBER PRIMARY KEY,
    COMMENT_CONTENT VARCHAR2(1000) NOT NULL,
    COMMENT_WRITER VARCHAR2(20) REFERENCES UQ_MEMBER(MEMBER_ID),
    --1. BOARD_NO NUMBER REFERENCES FK_BOARD(BOARD_NO) ON DELETE SET NULL,
    --2.BOARD_NO NUMBER REFERENCES FK_BOARD(BOARD_NO) ON DELETE CASCADE,
    COMMENT_DATE DATE
);

CHECK DEFAULT COMMENT(제약조건조회 제약조건 이름 설정)

CHECK(C)

특정 조건에 맞는 값만 저장할 수 있게 해주는 것이다.

  • 컬럼레벨에서 설정가능
    ✓ PERSON테이블 생성시 GENDER컬럼의 값을 'M'/'F'로 지정함
CREATE TABLE PERSON(
NAME VARCAHR2(20),
AGE NUMBER CHECK(AGE>0),
GENDER VARCHAR(5) CHECK(GENDER IN('M','F'))
);

DEFAULT 값 설정하기

테이블 생성 시 컬럼명, 자료타입 뒤에 DEFAULT '기본값'을 써준다.

데이터의 기본값을 '기본값'으로 지정함 

CREATE TABLE DEFAULT_TEST(
TEST_NO NUMBER PRIMARY KEY,
TEST_DATE DATE DEFAULT SYSDATE NOT NULL,
TEST_DATA VARCHAR2(20) DEFAULT '기본값'
);

CONSTRAINT

제약조건을 설정할 때 제약 조건에 대한 이름을 설정해준다.
(CONSTRAING 뒤에 있는것이 이름)

CREATE TABLE MEMBER_TEST(
MEMBER_NO NUMBER CONSTRAINT MEMBER_NO_PK PRIMARY KEY,
MEMBER_ID VARCHAR(20) CONSTRAINT MEMBER_ID_UQ UNIQUE CONSTRAINT NN_MEMBER_ID NOT NULL,
MEMBER_AGE NUMBER CONSTRAINT CK_AGE CHECK(MEMBER_AGE>0),
BOARD_NO NUMBER,
CONSTRAINT FK_BOARD FOREIGN KEY(BOARD_NO) REFERENCES FK_BOARD(BOARD_NO)
);
INSERT INTO MEMBER_TEST VALUES(1,'ADMIN',10,2); -- 에러 메시지에서 이름이 나오기때문에 찾기 더 편함

설정된 제약 조건 조회

오라클에서 USER_CONSTRAINTS,USER_CONS_COLUMNS 테이블을 제공한다. 

SELECT *
FROM USER_CONSTRAINTS; --컬럼 안나옴
-- 컬럼 확인하기(타입과 내용 안나옴)
SELECT *
FROM USER_CONS_COLUMNS;
-- > 합쳐서 사용(유일한 값을 갖는 CONSTRAINT_NAME)이 기준

다 나오게 하기⤵️

SELECT UC.CONSTRAINT_NAME,UC.TABLE_NAME,CONSTRAINT_TYPE,COLUMN_NAME
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME=UCC.CONSTRAINT_NAME;

컬럼에 설명 작성하기

  • 다른 사람이 못알아보는 코드는 좋은 코드가 아니기 때문에 설명을 작성하는게 좋음
COMMENT ON COLUMN MEMBER_TEST.MEMBER_NO IS'회원번호 PRIMARY KEY';
COMMENT ON COLUMN MEMBER_TEST.MEMBER_ID IS '회원아이디 8글자이상 영문';

TABLE에 설명 작성하기

  • 표현법 
COMMENT ON TABLE MEMBER_TEST IS '제약조건 연습테이블';
  • 확인하기
SELECT*
FROM USER_TAB_COMMENTS
WHERE TABLE_NAME='MEMBER_TEST';

테이블 생성할 때 기존테이블의 내용을 복사해서 생성하기

  • 복사해서 생성한 테이블은 제약조건이 NOT NULL제약조건만 복사됨
    ✔️ ENPLOYEE 테이블 복사해서 테이블 생성하고 확인하기
CREATE TABLE COPY_EMP
AS SELECT *FROM EMPLOYEE;
SELECT*FROM COPY_EMP;

🖐️문제풀이

  • EMPLOYEE 에서 아이디, 이름, 연봉만 복사해서 테이블 생성하고 확인하기
CREATE TABLE COPY_EMP
AS SELECT  EMP_ID,EMP_NAME,SALARY*12 AS YEAR_SAL FROM EMPLOYEE;
SELECT*FROM COPY_EMP;
  • EMPLOYEE 테이블의 구조만 복사하기
CREATE TABLE COPY_EMP2
AS SELECT*FROM EMPLOYEE WHERE 1=2; --컬럼들만 복사해서 가져오게됨(NOT NULL제약조건만 복사됨)
  • TEST_MEMBER 테이블 만들기
    --MEMBER_CODE(NUMBER) - 기본키 -- 회원전용코드
    --MEMBER_ID (varchar2(20) ) - 중복금지, NULL값 허용금지 -- 회원 아이디
    --MEMBER_PWD (char(20)) - NULL 값 허용금지 -- 회원 비밀번호
    --MEMBER_NAME(nchar(10)) - 기본값 '아무개' -- 회원 이름
    --MEMBER_ADDR (char(50)) - NULL값 허용금지 -- 회원 거주지
    --GENDER (varchar2(5)) - '남' 혹은 '여'로만 입력 가능 -- 성별
    --PHONE(varchar2(20)) - NULL 값 허용금지 -- 회원 연락처
    --HEIGHT(NUMBER(5,2) - 130이상의 값만 입력가능 -- 회원키
    --TYPE_NO NUMBER - BOARD_TYPE NO값을 참조 -- 보드타입참조
CREATE TABLE BOARD_TYPE(
    TYPE_NO NUMBER PRIMARY KEY,
    TYPE_NAME VARCHAR2(10)
);

테이블 생성하기

CREATE TABLE TEST_MEMBER(
 MEMBER_CODE NUMBER PRIMARY KEY,
 MEMBER_ID VARCHAR2(20) UNIQUE NOT NULL ,
 MEMBER_PWD CHAR(20) NOT NULL,
 MEMBER_NAME NCHAR(10) DEFAULT '아무개',
 MEMBER_ADDR CHAR(50) NOT NULL,
 GENDER VARCHAR(5) NOT NULL CHECK(GENDER IN('남','여')),
 PHONE VARCHAR(20) NOT NULL,
 HEIGHT NUMBER(5,2) CHECK(HEIGHT>=130),
 TYPE_NO NUMBER REFERENCES BOARD_TYPE(TYPE_NO)
 );

코멘트 달기

COMMENT ON COLUMN TABLE_MEMNBER.MEMBER_CODE IS'회원전용코드';
COMMENT ON COLUMN TABLE_MEMNBER.MEMBER_ID IS'회원아이디';
COMMENT ON COLUMN TABLE_MEMNBER.MEMBER_PWD IS'회원비밀번호';
COMMENT ON COLUMN TABLE_MEMNBER.MEMBER_ADDR IS'회원 거주지';
COMMENT ON COLUMN TABLE_MEMNBER.GENDER IS'성별';
COMMENT ON COLUMN TABLE_MEMNBER.PHONE IS'회원 연락처';
COMMENT ON COLUMN TABLE_MEMNBER.HEIGHT IS'회원키';
COMMENT ON COLUMN TABLE_MEMNBER.TYPE_TO_NUM IS'보드타입 참조';

이상으로 DDL끝입니당!

profile
이제내손을java...
히죽코딩
이전 포스트

<Database>SUBQUERY

다음 포스트

<Database> DML

0개의 댓글