[DB][SQL][국비교육] Day 27

Ga02·2023년 2월 3일
dbsql국비교육

국비교육

목록 보기
26/82

🔍 DDL, Data Definition Language, 데이터 정의어

DB 객체(테이블, 사용자계정, 뷰, 인덱스, 시퀀스 등)를 생성, 변경, 삭제하는 SQL구문

  • DB구조(스키마, Schema)에 대한 관리 작업을 수행한다
  • DDL구문의 결과는 자료 사전(Data Dictionary)에 기록된다 👉🏻 자료사전을 SELECT해서 확인한다
  • DB컬럼은 대소문자 구분이 없어 낙타식표기가 소용이 없음 👉🏻 언더바로 구분(뱀 표기법)

➰ 테이블 DDL 구문종류

  • CREATE : 테이블 생성 SQL
  • ALTER : 테이블에 적용된 설정사항을 변경하는 SQL
  • DROP : 테이블 삭제 SQL

🔍 CREATE TABLE 구문

테이블의 구조인 컬럼을 같이 지정해서 생성해야 함
👉🏻 컬럼마다 컬럼명, 자료형, 제약사항을 같이 작성

  • 제약사항은 데이터의 무결성을 지켜주는 PK, FK같은 것 👉🏻 필수작성 아님
CREATE TABLE 테이블명 (

	컬럼명1 자료형1 [컬럼 제약사항],
	컬럼명2 자료형2 [컬럼 제약사항],
	컬럼명3 자료형3 [컬럼 제약사항],
	...
	, [테이블 제약사항]

);


-- 게시글 테이블 생성
--  게시글 번호  (숫자)
--  게시글 제목  (문자)
--  게시글 본문  (문자)
--  작성일     (날짜)
Drop TABLE board;
CREATE TABLE board (
    boardno NUMBER
    , title VARCHAR2(500)
    , content VARCHAR2(4000)	--실제 만들 때는 이 타입으로는 부족 / 추후에 배울 예정
    , write_date DATE
);

➰ 코멘트, Comment

테이블이나 컬럼에 설명을 적어주는 객체
👉🏻 작성된 코멘트는 자료사전을 이용하여 확인할 수 있다

--  테이블 코멘트 추가하기
COMMENT ON TABLE board IS '게시판';

--  테이블 코멘트 제거하기
COMMENT ON TABLE board IS '';	--제거 키워드는 별도로 없음

--  컬럼에 코멘트 추가하기
COMMENT ON COLUMN board.boardno IS '게시글 번호';
COMMENT ON COLUMN board.title IS '게시글 제목';
COMMENT ON COLUMN board.content IS '게시글 본문';
COMMENT ON COLUMN board.write_date IS '작성일';

--	컬럼에서 코멘트 제거하기
COMMENT ON COLUMN board.boardno IS '';
  • 코멘트 자료사전 확인하기
    • USER_TAB_COMMENTS : 테이블 코멘트
    • USER_COL_COMMENTS : 컬럼 코멘트
--  테이블 코멘트 자료사전 확인
SELECT * FROM user_tab_comments
WHERE table_name = 'BOARD';

--  컬럼 코멘트 자료사전 확인
SELECT * FROM user_col_comments
WHERE table_name = 'BOARD';

➰ CONCAT('str1', 'str2')

문자열 1, 2를 합친 결과를 반환

  • concat을 늘릴 때는 괄호로 감싸서 앞에서 늘려줌
SELECT
    concat(concat(concat(table_name, '['), table_type), ']') AS table_name
    , null AS column_name, comments FROM user_tab_comments
WHERE table_name = 'BOARD'
UNION
SELECT table_name, column_name, comments FROM user_col_comments
WHERE table_name = 'BOARD'
ORDER BY column_name NULLS FIRST;

🔍 NUMBER - 자료형

가변길이 숫자 타입 👉🏻 정수, 실수, 음수, 0, 양수 모두 표현 가능

  • NUMBER : 최대 38자리까지 표현 가능한 숫자타입
  • NUMBER(p) : 최대 p자리까지 표현 가능한 정수타입
  • NUMBER(p, s) : 전체 p자리, 소수점 이하 s자리까지 가능한 실수타입 👉🏻 정수는 전체자리 - 소수자리 에서 남은 부분에서만 활용가능
CREATE TABLE type_test_01 (
    data1 NUMBER,
    data2 NUMBER(10),
    data3 NUMBER(5, 2),
    data4 NUMBER(4, 5)
);


--  data1 : NUMBER
INSERT INTO type_test_01 ( data1 )
VALUES ( 123456789012345678901234567890123456789012345678901234567890 ); --숫자 60자리
INSERT INTO type_test_01 ( data1 )
VALUES ( 123.456 );

--  data2 : NUMBER(10)
--  소수부분 표현 불가 / 반올림 처리
INSERT INTO type_test_01 (data2) VALUES (123.456);  --123만 저장
INSERT INTO type_test_01 (data2) VALUES (678.987);  --679 저장(반올림)

--  data3 : NUMBER(5, 2)
INSERT INTO type_test_01 (data3) VALUES (34.56);
INSERT INTO type_test_01 (data3) VALUES (45.6789);  --45.68 저장(소수 2자리 넘으면 알아서 반올림)
--INSERT INTO type_test_01 (data3) VALUES (7656.78);  --에러, 정수 3자리를 넘는 데이터

--  data4 : NUMBER(4, 5)
--  전체 4자리, 소수 5자리 -> 0.0xxxx
--INSERT INTO type_test_01 ( data4 ) VALUES ( 12345 );    --에러, 정수표현 불가
--INSERT INTO type_test_01 ( data4 ) VALUES ( 0.123 );    --에러, 소수 첫째자리 표현 불가
INSERT INTO type_test_01 ( data4 ) VALUES ( 0.07894 );

🔍 VARCHAR2(n)

VARiable CHARacter 2, 바캐릭터

  • 가변길이 문자타입 👉🏻 최대 n길이의 문자 표현가능
    • n : 최소 1 , 최대 4000 / 생략 불가
  • 문자데이터는 ''로 감싸서 표현
  • 접미어
    • byte : 바이트 단위로 길이 판단 👉🏻 기본값
    • char : 문자단위로 길이 판단
CREATE TABLE type_test_02 (
    data VARCHAR2(10)
);

INSERT INTO type_test_02 VALUES ('1234567890');
INSERT INTO type_test_02 VALUES ('123456789012345');    --에러, 길이 초과
INSERT INTO type_test_02 VALUES ('일이삼사오육칠팔구십');     --에러, 길이 초과


CREATE TABLE type_test_03 (
    data1 VARCHAR2(10 BYTE),
    data2 VARCHAR2(10 CHAR)
);

INSERT INTO type_test_03 VALUES ('일이삼', '일이삼사오육칠팔구십');

🔍 CHAR(n)

고정 길이 문자 데이터 👉🏻 n으로 지정한 길이만큼 저장공간 확보

  • n : 최소크기 1 , 최대크기 2000 / 생략가능
  • VARCHAR2와 CHAR는 문자 표현하는 방식이 같아서 같은 데이터타입으로 취급 👉🏻 저장공간을 처리(사용)하는 방식 달라 조회할 때 표현방식에 주의해야 함
    ✔ CHAR타입은 저장공간 크기보다 작은 데이터를 저장할 때 남는공간을 ' '빈칸으로 채워둠
CREATE TABLE type_test_04 (
    data1 CHAR,
    data2 CHAR(10),
    data3 CHAR(10 CHAR)
);

INSERT INTO type_test_04 VALUES ('1', '1234567890', '12345육칠팔구십');
INSERT INTO type_test_04 VALUES ('2', '12345', '12육칠팔');

🔍 DATE, TIMESTAMP

날짜와 시간을 모두 표현할 수 있는 날짜시간타입

  • sysdate는 초단위까지 / systimestamp는 밀리초단위까지 표현 가능
  • systimestamp는 TIMEZONE을 지정하거나 표현 가능
--DATE : 날짜시간
CREATE TABLE type_test_05 (
    data DATE
);

INSERT INTO type_test_05 VALUES (sysdate);
INSERT INTO type_test_05 VALUES ('2026/03/31');
INSERT INTO type_test_05 VALUES ('23/07/6');
--INSERT INTO type_test_05 VALUES ('12/05/23 13:22:34');  --시간 서식은 자동인식x to_date() 필요
INSERT INTO type_test_05 VALUES ( to_date('12/05/23 13:22:34', 'YY/MM/DD HH24:MI:SS') );

➰ RR, YY 서식문자의 차이

to_date() 함수에서 2자리로 년도를 인식하도록 작성했을 때 차이가 발생

  • YY : 현재 년도의 앞 두자리가 붙은걸로 인식
  • RR : 현재 년도에 따라 입력받은 년도를 다르게 생각
to_date( '25/2/7', 'yy/mm/dd' )		-> 2025년
to_date( '99/12/29', 'yy/mm/dd' )	-> 2099년


INSERT INTO type_test_05 VALUES ( to_date( '13/9/7', 'yy/mm/dd' ) );
INSERT INTO type_test_05 VALUES ( to_date( '91/3/24', 'yy/mm/dd' ) );
INSERT INTO type_test_05 VALUES ( to_date( '13/9/7', 'rr/mm/dd' ) );
INSERT INTO type_test_05 VALUES ( to_date( '91/3/24', 'rr/mm/dd' ) );

SELECT to_char(data, 'YY/MM/DD HH24:MI:SS') data FROM type_test_05;
SELECT to_char(data, 'RR/MM/DD HH24:MI:SS') data FROM type_test_05;

🔍 LONG

가변 길이 문자 타입

  • 최대 길이가 2GB며 테이블당 한 개의 컬럼에만 지정할 수 있음
  • 테이블 내에 데이터가 직접 저장됨

➰ 로렘 입숨, Lorem Ipsum

텍스트 더미(dummy) 데이터를 생성해주는 사이트

🔍 RAW, LONG RAW

이미지나 사운드 같은 바이너리 데이터를 저장할 때 사용하는 데이터 타입 👉🏻 저장은 이진 데이터로, 조회는 16진수로 출력됨

  • 문자로 변환 불가
  • RAW : 최대 2000B
  • LONG RAW : 최대 2GB

🔍 LOB, Large OBject

최대 4GB까지 저장 가능한 가변길이 타입 👉🏻 최대 4GB까지 저장 가능

  • 테이블의 여러 컬럼에 지정해서 사용 가능
  • 참조형으로 동작 👉🏻 테이블에는 데이터의 저장위치(참조값)을 저장 / 실제 데이터는 따로 저장

➰ BLOB, Binary LOB

대용량 이진(Binary) 데이터 👉🏻 LONG RAW 타입 대체

➰ CLOB, Character LOB

대용량 문자 데이터 👉🏻 LONG타입을 대체

--  CLOB데이터를 조회할 때 사용하는 함수
SELECT DBMS_LOB.SUBSTR(data1, 100, 1)	--(colname, length, offset)
FROM type_test_07;


--CLOB컬럼 데이터 전체 조회
SELECT DBMS_LOB.SUBSTR(data2, length(data2), 1)
FROM type_test_07;

🔍 제약사항, 제약조건, Constraint

  • 테이블에 부적절한 데이터가 입력되지 않도록 설정하는 필터조건 👉🏻 데이터 무결성 유지
    데이터 무결성 : 프로그램이 사용되면서 데이터를 일관성있고 정확성있게 유지하는 성격 / 의도했던 대로 데이터가 유지되도록 하는 것
  • 제약조건에 이름을 붙여서 설정하도록 👉🏻 이름을 설정하지 않고 설정시 시스템이 자동으로 이름을 붙여줌 ➡ 가독성 안좋음
  • 테이블 레벨이나 컬럼 레벨로 컬럼에 제약조건 설정 가능
    ❗ NOT NULL은 테이블 레벨로 설정 불가
  • 한 컬럼에 제약조건 여러개 설정 가능
CREATE TABLE cons_02 (
    data VARCHAR2(10) CONSTRAINT nn_data NOT NULL
);
➡ 제약조건 이름 : nn_data / 제약조건 : NOT NULL


CREATE TABLE cons_04 (
	--컬럼 레벨로 설정
    data1 NUMBER CONSTRAINT ukdata1 UNIQUE
    , data2 number UNIQUE
    , data3 NUMBER
    , data4 NUMBER

    --테이블 레벨로 설정
    , CONSTRAINT ukdata2 UNIQUE(data3)
	, UNIQUE (data4)
);


CREATE TABLE cons_05 (
    --컬럼 레벨로 제약조건 여러개 나열하기
--    data NUMBER NOT NULL UNIQUE
----------------------------------------------
    --컬럼레벨로 NOT NULL, 테이블 레벨로 UNIQUE
    --	-> 비추
--    data NUMBER NOT NULL
--    , UNIQUE (data)
----------------------------------------------
    --컬럼레벨로 이름 붙여서 제약조건 여러개 설정
    data NUMBER
        CONSTRAINT nntest1 NOT NULL
        CONSTRAINT uktest1 UNIQUE
);

➰ 제약사항의 종류

  • NOT NULL : 컬럼의 데이터로 NULL을 허용하지 않음
    컬럼의 데이터타입과 함께 부여되는 제약사항
CREATE TABLE cons_01 (
    data VARCHAR2(10) NOT NULL
    
    --NOT NULL 제약조건은 테이블 레벨로 설정할 수 없음
--    , CONSTRAINT nn NOT NULL (data)
--    , NOT NULL(data)
);

INSERT INTO cons_01 VALUES (NULL);  --에러, NULL값 입력 거부
  • UNIQUE : 컬럼에 중복데이터를 허용하지 않음 👉🏻 데이터의 유일성 부여
    • 테이블의 UNIQUE KEY(유일키, UK)가 됨 ➡ 자동으로 인덱스 생성
CREATE TABLE cons_03 (
--    data VARCHAR2(10) UNIQUE
     data VARCHAR2(10) CONSTRAINT ukdata UNIQUE
);

INSERT INTO cons_03 VALUES ('BANANA');
INSERT INTO cons_03 VALUES ('BANANA');  --같은 데이터 삽입 불가
  • CHECK :
  • DEFAULT :
  • PRIMARY KEY :
  • FOREIGN KEY :

➰ 제약조건 자료사전

  • user_constraints : 테이블 제약조건
  • user_cons_columns : 컬럼 제약조건
--제약조건 자료사전 : user_constraints
SELECT * FROM user_constraints
WHERE table_name = upper('&aaaa');


--제약조건 컬럼 자료사전 : user_cons_columns
SELECT * FROM user_cons_columns
--WHERE table_name = upper('cons_01');
WHERE constraint_name = upper('SYS_C007064');

➰ 제약조건 조회 쿼리

SELECT
    A.TABLE_NAME,
	SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,
	DECODE(B.CONSTRAINT_TYPE,
		'P','PRIMARY KEY',
		'U','UNIQUE KEY',
		'C','CHECK OR NOT NULL',
		'R','FOREIGN KEY'
	) CONSTRAINT_TYPE,
	A.CONSTRAINT_NAME,
    SEARCH_CONDITION
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = UPPER('&table_name')
	AND A.TABLE_NAME = B.TABLE_NAME
	AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY 1, 2;

SELECT * FROM USER_CONSTRAINTS; -- 사용자의 모든 제약사항
SELECT * FROM USER_CONS_COLUMNS; -- 컬럼에 해당하는 제약사항 조회
profile
Ga02
IT꿈나무 댓츠미
이전 포스트

[DB][SQL][국비교육] Day 25

다음 포스트

[DB][SQL][국비교육] Day 28

0개의 댓글