1. DDL (CREATE TABLE)
DDL (DATA DEFINITION LANGUAGE) : 데이터 정의 언어
객체(OBJECT)를 만들고(CREATE), 수정(ALTER)하고, 삭제(DROP)하는 구문
1-1. CREATE
테이블 만들기
[표현식]
CREATE TABLE 테이블명 (컬럼명 자료형(크기), 컬럼명 자료형(크기),...)
CREATE TABLE MEMBER (
MEMBER_ID VARCHAR(20),
MEMBER_PWD VARCHAR(20),
MEMBER_NAME VARCHAR(20)
);
1-2. COMMENT
컬럼에 주석달기
[표현식]
COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';
COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원명';
2. 제약조건 (CONSTRAINT)
테이블 작성 시 각 컬럼에 대해 값 기록에 대한 제약조건을 설정할 수 있다.
데이터 무결성 보장을 목적으로 함
입력/수정하는 데이터에 문제가 없는지 자동으로 검사하는 목적
PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY
USER_CONSTRAINTS
: 유저가 소유한 모든 제약 조건을 불 수 있다.
SELECT
UC.*
FROM USER_CONSTRAINTS UC;
USER_CONS_COLUMNS
: 컬럼에 할당된 제약 조건을 볼 수 있다.
SELECT
UCC.*
FROM USER_CONS_COLUMNS UCC;
제약조건에 이름 설정
CREATE TABLE CONS_NAME(
TEST_DATA1 VARCHAR2(20) CONSTRAINT NN_TEST_DATA1 NOT NULL,
TEST_DATA2 VARCHAR2(20) CONSTRAINT UN_TEST_DATA2 UNIQUE,
TEST_DATA3 VARCHAR2(30),
CONSTRAINT UN_TEST_DATA3 UNIQUE(TEST_DATA3)
);
-- NN_TEST_DATA1 / UN_TEST_DATA2 / UN_TEST_DATA3이 설정한 제약조건의 이름이 된다.
2-1. NOT NULL
해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용
삽입/수정 시 NULL 값을 허용하지 않도록 컬럼 레벨에서 제한
CREATE TABLE USER_NOTNULL(
USER_NO NUMBER NOT NULL,
USER_ID VARCHAR2(20) NOT NULL,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30) NOT NULL,
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);
INSERT로 USER_NOTNULL 테이블에 모든 컬럼에 값을 추가해보자
INSERT INTO USER_NOTNULL
VALUES ( 1, NULL, 'PASS01', '홍길동'
, '남', '010-1234-5678', 'hong123@greedy.com' );
NOT NULL조건인 컬럼에 NULL값을 입력하면
"NULL을 ("C##EMPLOYEE"."USER_NOTNULL"."USER_ID") 안에 삽입할 수 없습니다" 라고 오류가 난다.
2-2. UNIQUE 제약조건
컬럼의 입력 값에 대해 중복을 제한하는 제약조건
컬럼 레벨에서 설정 가능, 테이블 레벨에서 설정 가능
- UNIQUE 제약조건 컬럼 레벨 설정
CREATE TABLE USER_UNIQUE(
USER_NO NUMBER,
USER_ID VARCHAR2(20) UNIQUE NOT NULL,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);
- UNIQUE 제약조건 테이블 레벨 설정
CREATE TABLE USER_UNIQUE2 (
USER_NO NUMBER,
USER_ID VARCHAR2(20) NOT NULL,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
UNIQUE(USER_ID)
);
두 개의 컬럼을 묶어서 하나의 UNIQUE 제약 조건 설정
CREATE TABLE USER_UNIQUE3 (
USER_NO NUMBER,
USER_ID VARCHAR2(20) NOT NULL,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
UNIQUE(USER_NO, USER_ID)
);
INSERT로 값을 추가해보자.
INSERT INTO USER_UNIQUE3(
USER_NO, USER_ID, USER_PWD, USER_NAME
, GENDER, PHONE, EMAIL)
VALUES(
1, 'USER01', 'PASS01', '홍길동'
, '남', '010-1234-5678', 'hong123@greedy.com');
INSERT INTO USER_UNIQUE3(
USER_NO, USER_ID, USER_PWD, USER_NAME
, GENDER, PHONE, EMAIL)
VALUES(
1, 'USER02', 'PASS01', '홍길동'
, '남', '010-1234-5678', 'hong123@greedy.com'
);
두 개의 컬럼 ( USER_NO, USER_ID )이 UNIQUE 제약 조건으로 묶여있기 때문에
(1, 'USER01') 과 (1,'USER02') 는 UNIQUE 조건을 만족할 수 있다. 서로 고유한 값이 됨.
2-3. CHECK 제약조건
컬럼에 기록되는 값에 조건설정을 할 수 있음
CHECK (컬럼명 비교연산자 비교값)
주의: 비교값은 리터럴만 사용할 수 있음, 변하는 값이나 함수 사용 못함
CREATE TABLE USER_CHECK(
USER_NO NUMBER,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10) CHECK(GENDER IN ('남', '여')),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);
GENDER 에 CHECK 제약조건을 설정했기 때문에 INSERT시 '남'과 '여'로만 입력이 가능하다.
'남자'로 입력 시 "체크 제약조건(C##EMPLOYEE.SYS_C007732)이 위배되었습니다"라고 오류가 난다.
연습 문제1
-- 회원 가입용 테이블 생성 (USER_TEST)
-- 컬럼명 : USER_NO(회원번호)
-- USER_ID(회원아이디) -- 중복금지, NULL값 허용 안함
-- USER_PWD(비밀번호) -- NULL값 허용안함
-- PNO(주민번호) -- 중복금지, NULL값 허용안함
-- GENDER(성별) -- '여' 또는 '남'으로 입력
-- PHONE(연락처)
-- ADDRESS(주소)
-- STATUS(탈퇴여부) -- NOT NULL, 'Y' 혹은 'N'으로 입력
-- 제약조건 이름 부여
-- 5명 이상의 회원 정보 INSERT
-- 컬럼별로 코맨트 생성
작성한 코드)
CREATE TABLE USER_TEST (
USER_NO NUMBER,
USER_ID VARCHAR2(20) CONSTRAINT UN_USER_ID UNIQUE NOT NULL,
USER_PWD VARCHAR2(20) CONSTRAINT NN_USER_PWD NOT NULL,
PNO VARCHAR2(20) CONSTRAINT UN_PNO UNIQUE NOT NULL,
GENDER VARCHAR2(10) CONSTRAINT CK_GENDER CHECK (GENDER IN ('남','여')),
PHONE VARCHAR2(20),
ADDRESS VARCHAR2(50),
STATUS VARCHAR2(10) CONSTRAINT CK_NN_STATUS NOT NULL CHECK (STATUS IN ('Y','N'))
);
COMMENT ON COLUMN USER_TEST.USER_NO IS '회원번호';
COMMENT ON COLUMN USER_TEST.USER_ID IS '회원아이디';
COMMENT ON COLUMN USER_TEST.USER_PWD IS '회원비밀번호';
COMMENT ON COLUMN USER_TEST.PNO IS '주민등록번호';
COMMENT ON COLUMN USER_TEST.GENDER IS '성별';
COMMENT ON COLUMN USER_TEST.PHONE IS '연락처';
COMMENT ON COLUMN USER_TEST.ADDRESS IS '주소';
COMMENT ON COLUMN USER_TEST.STATUS IS '탈퇴여부';
INSERT INTO USER_TEST(
USER_NO, USER_ID, USER_PWD, PNO,
GENDER, PHONE, ADDRESS, STATUS)
VALUES(
1, 'USER01', 'PASS01', '901010-1010100',
'남', '010-1234-5678', '서울시 강남구 역삼동', 'Y');
-- 이하 4명의 회원 정보 추가 생략
테이블 레벨에서 제약조건을 명시한 코드 )
CREATE TABLE USER_TEST(
USER_NO NUMBER,
USER_ID VARCHAR2(20) CONSTRAINT NN_USER_ID NOT NULL,
USER_PWD VARCHAR2(20) CONSTRAINT NN_USER_PWD NOT NULL,
PNO VARCHAR2(20) CONSTRAINT NN_PNO NOT NULL,
GENDER VARCHAR2(3),
PHONE VARCHAR2(20),
ADDRESS VARCHAR2(100),
STATUS VARCHAR2(3) CONSTRAINT NN_STATUS NOT NULL,
CONSTRAINT UK_USER_ID UNIQUE(USER_ID),
CONSTRAINT UK_PNO UNIQUE (PNO),
CONSTRAINT CK_GENDER CHECK(GENDER IN ('남', '여')),
CONSTRAINT CK_STATUS CHECK(STATUS IN ('Y', 'N'))
);
2-4. PRIMARY KEY
(기본키) 제약조건
테이블에서 한 행의 정보를 찾기 위해 사용할 컬럼을 의미한다.
테이블에 대한 식별자 역할을 한다.
NOT NULL + UNIQUE 제약조건의 의미
한 테이블당 한 개만 설정할 수 있음
컬럼 레벨, 테이블 테벨 둘 다에서 설정 가능함
한 개의 컬럼에 설정할 수 도 있고, 여러 개의 컬럼을 묶어서 설정할 수 있음
- 컬럼 레벨에서 PK 생성
CREATE TABLE USER_PRIMARYKEY(
USER_NO NUMBER CONSTRAINT PK_USER_NO PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(10),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);
PK는 NULL 값이 존재할 수 없다.
- 테이블 레벨에서 PK 설정 (복합키로 설정)
CREATE TABLE USER_PRIMARYKEY2(
USER_NO NUMBER,
USER_ID VARCHAR2(20),
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(10),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
CONSTRAINT PK_USER_NO2 PRIMARY KEY (USER_NO, USER_ID)
);
2-5. FOREIGN KEY
(외부키 / 외래키) 제약 조건
참조(REPERENCES) 된 다른 테이블에서 젝공하는 값만 사용할 수 있음
참조 무결성을 위배하지 않게 하기 위해서 사용
FOREIGN KEY 제약 조건에 의해서 테이블 간의 관계가 형성 됨
제공되는 값 외에는 NULL을 사용할 수 있음
1) 컬럼 레벨인 경우
- 컬럼명 자료형 (크기) [CONSTRAINT 이름] REFERENCE 참조할 테이블명 [(참조할 컬럼)] [삭제룰]
2) 테이블 레벨인 경우
- [CONSTRAINT 이름] FOREING KEY (적용할컬럼명) REFERENCE 참조할 테이블명 [(참조할 컬럼)] [삭제룰]
각 회원의 등급 코드 테이블을 먼저 생성한다.
CREATE TABLE USER_GRADE (
GRADE_CODE NUMBER PRIMARY KEY,
GRADE_NAME VARCHAR2(30) NOT NULL
);
--> GRADE_CODE가 10이면 '일반회원', 20이면 '우수회원', 30이면 '특별회원' 이다.
CREATE TABLE USER_FOREIGNKEY(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER,
CONSTRAINT FK_GRADE_CODE FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE (GRADE_CODE)
);
-> GRADE_CODE에 50 이라는 값은 올 수 없지만, NULL은 가능하다.
참조할 테이블의 참조할 컬럼명이 생략 되면 PRIMARY KEY로 설정된 컬럼이 자동 참조할 컬럼이 됨
참조될 수 있는 컬럼은 PRIMARY KEY 컬럼과 UNIQUE로 지정 된 컬럼만 외래키로 참조할 수 있음
-- USER_FOREIGNKEY 테이블에서 회원 아이디, 이름, 성별, 연락처, 회원 등급명 조회
SELECT
UF.USER_ID
, UF.USER_NAME
, UF.GENDER
, UF.PHONE
, UG.GRADE_CODE
FROM USER_FOREIGNKEY UF
LEFT JOIN USER_GRADE UG ON (UF.GRADE_CODE = UG.GRADE_CODE);
삭제 옵션
부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를 어떤 식으로 처리할 것인지에 대한 내용을 설정할 수 있다.
ON DELETE RESTRICT
위 코드처럼 USER_FOREIGNKEY 테이블의 GRADE_CODE 컬럼에 대한
삭제 옵션이 없는 경우
DELETE
FROM USER_GRADE
WHERE GRADE_CODE = 10;
무결성 제약조건(C##EMPLOYEE.FK_GRADE_COD)이 위배되었습니다- 자식 레코드가 발견되었습니다.
ON DELETE RESTRICT 로 지정되어 있음
FOREIGN KEY로 지정 된 컬럼에서 사용 되고 있는 값일 경우
제공하는 컬럼의 값은 삭제하지 못한다.
ON DELETE SET NULL
부모 키를 삭제 시 자식 키를 NULL로 변경하는 옵션
CREATE TABLE USER_FOREIGNKEY2(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER,
CONSTRAINT FK_GRADE_CODE2 FOREIGN KEY(GRADE_CODE)
REFERENCES USER_GRADE2(GRADE_CODE) ON DELETE SET NULL
);
다시 USER_GRADE2 , USER_FOREIGNKEY2 테이블을 만들고, GRADE_CODE를 외래키로 설정한다.
그리고 임의의 회원 4명을 추가했다.
이번엔 삭제옵션이 있기 때문에
USER_GRADE2 테이블의 GRADE_CODE의 '10'을 지울 수 있다.
DELETE
FROM USER_GRADE2
WHERE GRADE_CODE = 10;
USER_FOREIGNKEY2 테이블을 조회해보면 GRADE_CODE가 10이었던 회원의
행의 값이 NULL이 된 걸 확인 할 수 있다.
1 USER01 PASS01 홍길동 남 010-1234-5678 hong123@greedy.com NULL -- 10이었음
2 USER02 PASS02 유관순 여 010-2345-5678 YOU123@greedy.com MULL -- 10이었음
3 USER03 PASS03 신사임당 여 010-8888-5678 SINS123@greedy.com 30
4 USER04 PASS04 윤봉길 남 010-3456-1234 yoon1@greedy.comㅜ MULL --원래 NULL값을 입력함
ON DELETE CASCADE
부모 키를 삭제 시 자식 키를 가진 행도 함께 삭제
CREATE TABLE USER_FOREIGNKEY3(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER,
CONSTRAINT FK_GRADE_CODE3 FOREIGN KEY(GRADE_CODE)
REFERENCES USER_GRADE3(GRADE_CODE) ON DELETE CASCADE
);
다시 USER_GRADE3 , USER_FOREIGNKEY3 테이블을 만들고, GRADE_CODE를 외래키로 설정한다.
그리고 임의의 회원 4명을 추가했다.
위와 동일하게 USER_GRADE3 테이블의 GRADE_CODE가 10인 행을 지운 뒤
참조하고 있는 USER_FOREIGNKEY3 테이블의 회원 정보를 출력해보면
3 USER03 PASS03 신사임당 여 010-8888-5678 SINS123@greedy.com 30
4 USER04 PASS04 윤봉길 남 010-3456-1234 yoon1@greedy.com NULL --원래 NULL값 입력함
참조하는 자식 키를 가진 행도 같이 지워진 것을 확인 할 수 있다.
서브쿼리를 이용한 테이블 생성
CREATE TABLE EMPLOYEE_COPY AS 로 EMPLOYEE 테이블의 전체 행을 복사할 수 있다.
CREATE TABLE EMPLOYEE_COPY
AS
SELECT
E.*
FROM EMPLOYEE E;
EMPLOYEE의 부분 컬럼과 JOIN 컬럼으로 테이블 생성도 가능하다.
CREATE TABLE EMPLOYEE_COPY2
AS
SELECT
E.EMP_ID
,E.EMP_NAME
,E.SALARY
,D.DEPT_TITLE
,J.JOB_NAME
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON(E.DEPT_CODE= D.DEPT_ID)
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE);
3. 제약조건 추가
1. ALTER TABLE 테이블명 ADD PRIMARY KEY (컬럼명);
2. ALTER TABLE 테이블명 ADD FPREKGN KEY (컬럼명) REFERENCES 테이블명 (컬럼명);
3. ALTER TABLE 테이블명 ADD UNIQUE (컬럼명);
4. ALTER TABLE 테이블명 ADD CHECK (컬럼명 비교연산자 비교값);
5. ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;
연습문제 2
-- EMPLOYEE 테이블의 DEPT_CODE에 외래키 제약조건 추가
-- 참조 테이블은 DEPARTMENT, 참조컬럼은 DEPARTMENT의 기본키
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT (DEPT_ID);
-- DEPARTMENT 테이블의 LOCATION_ID에 외래키 제약조건 추가
-- 참조 테이블은 LOCATION, 참조 컬럼은 LOCATION의 기본키
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (LOCATION_ID) REFERENCES LOCATION (LOCAL_CODE);
-- EMPLOYEE 테이블의 JOB_CODE에 외래키 제약조건 추가
-- 참조 테이블은 JOB 테이블, 참조 컬럼은 JOB테이블의 기본키
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (JOB_CODE) REFERENCES JOB (JOB_CODE);
-- EMPLOYEE 테이블의 SAL_LEVEL에 외래키 제약조건 추가
-- 참조테이블은 SAL_GRADE테이블, 참조 컬럼은 SAL_GRADE테이블 기본키
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (SAL_LEVEL) REFERENCES SAL_GRADE (SAL_LEVEL);
-- EMPLOYEE테이블의 ENT_YN컬럼에 CHECK제약조건 추가('Y','N')
-- 단, 대 소문자를 구분하기 때문에 대문자로 설정
ALTER TABLE EMPLOYEE ADD CHECK (ENT_YN IN('Y','N'));
-- EMPLOYEE테이블의 SALARY 컬럼에 CHECK제약조건 추가(양수)
ALTER TABLE EMPLOYEE ADD CHECK (SALARY >0);
-- EMPLOYEE테이블의 EMP_NO컬럼에 UNIQUE 제약조건 추가
ALTER TABLE EMPLOYEE ADD UNIQUE (EMP_NO);
'백엔드 과정 > Oracle(SQL)' 카테고리의 다른 글
[Oracle] Chap08 DDL (0) | 2022.01.25 |
---|---|
[Oracle] Chap07 DML (0) | 2022.01.25 |
[Oracle] SQL문제풀이 / SUBQUERY (0) | 2022.01.22 |
[Oracle] Chap05 SUBQUERY (0) | 2022.01.21 |
[Oracle] SQL문제풀이 / JOIN 연습문제 (1) | 2022.01.19 |