지난 포스팅에서 SQL(SELECT)과 SQL 연산자
그리고 DML(데이터 조작어)에 대해서 다뤄봤죠.
https://healthdevelop.tistory.com/entry/db2
[Database] SQL | SQL 정의 | DML(SELECT,INSERT,DELETE,UPDATE) | SQL 연산자 | SQL 예제 (java로 웹개발하기)(day20)
지난 포스팅에서 Oracle 클라우드를 이용해 개인용 데이터베이스를 생성했죠. 이번 시간에는 SQL과 SQL 연산자 그리고 DML(데이터 조작어)에 대해서 다뤄보고자 합니다. ● SQL SQL이란,, Structured Query L
healthdevelop.tistory.com
이번 시간에는 SQL(INSERT, DELETE,UPDATE)과
DDL(CREATE, ALTER, DROP)에 대해서 다뤄보고자 합니다.
● DDL
DDL 이란 Data Definition Language의 약어로,
데이터 정의 언어로 객체(OBJECT)를
만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말합니다.
오라클에서 객체의 종류는 다음과 같습니다.
● CREATE
CREATE란,,
테이블이나 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문입니다.
표현식은 아래와 같습니다.
● 오라클 데이터형
● 컬럼 주석
컬럼 주석이란,,
테이블의 컬럼에 주석을 다는 구문입니다.
표현식은 아래와 같습니다.
● 제약 조건(CONSTRAINTS)
테이블 작성 시 각 컬럼에 기록될 데이터에 대해 제약 조건을 설정할 수 있는데
이는 데이터 무결성 보장을 주목적으로 합니다.
제약 조건은,
입력 데이터에 문제가 없는지에 대한 검사와
데이터 수정/삭제 가능 여부 검사 등을 위해 사용됩니다.
제약 조건의 종류는 아래와 같습니다.
제약 조건의 종류를 하나씩 알아봅시다.
※ 제약 조건의 종류
○ NOT NULL
NOT NULL은 해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용되고,
특정 컬럼에 값을 저장/수정할 때는 NULL값을 허용하지 않도록
컬럼 레벨에서 제한합니다.
아래는 NOT NULL의 사용 예시입니다.
○ UNIQUE
UNIQUE란,
컬럼 입력 값에 대해 중복을 제한하는 제약조건으로
컬럼 레벨과 테이블 레벨에 설정이 가능합니다.
아래는 UNIQUE의 사용 예시입니다.
○ PRIMARY KEY
PRIMARY KEY은 테이블에서 한 행의 정보를 구분하기 위한
고유 식별자 역할을 합니다.
NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고 있으며,
한 테이블 당 하나만 설정 가능하고,
컬럼 레벨과 테이블 레벨 둘 다 지정이 가능합니다.
아래는 PRIMARY KEY의 사용 예시입니다.
○ FOREIGN KEY
FOREIGN KEY은 참조 무결성을 위한 제약조건으로,
참조된 다른 테이블이 제공한 값만 사용하도록 제한을 거는 것입니다.
참조되는 컬럼과 참조된 컬럼을 통해 테이블 간에 관계가 형성되는데,
참조되는 값은 제공되는 값 외에 NULL을 사용 가능하며,
참조할 테이블의 참조할 컬럼 명을 생략할 경우
PRIMARY KEY로 설정된 컬럼이 자동으로 참조할 컬럼이 됩니다.
아래는 FOREIGN KEY의 사용 예시입니다.
○ CHECK
CHECK는 해당 컬럼에 입력되거나 수정되는 값을 체크하여
설정된 값 이외의 값이면 에러를 발생시킵니다.
비교 연산자를 이용하여 조건을 설정하며
비교 값을 리터럴만 사용 가능하고, 변하는 값이나 함수 사용은 불가능합니다.
아래는 CHECK의 사용 예시입니다.
지금까지 DDL, DML, 제약조건을 다뤄봤습니다.
이제 DDL, DML, 제약조건의 예제를 풀어봅시다.
DML(SELECT) 예제
난이도 ★★☆
예제 1
Q : 대학교의 교육시스템을 관리하기 위한 테이블을 생성하라.
TABLE LAYOUT
풀이 :
-- 학과 테이블
DROP TABLE tb_department;
CREATE TABLE tb_department(
department_no VARCHAR2(10),
department_name VARCHAR2(20),
category VARCHAR2(20),
open_yn CHAR(1),
capacity NUMBER
);
ALTER TABLE tb_department ADD CONSTRAINT TB_DEPARTMENT_NO_PK PRIMARY KEY(department_no);
ALTER TABLE tb_department MODIFY department_name CONSTRAINT TB_DEPARTMENT_NAME_NN NOT NULL;
ALTER TABLE tb_department ADD CONSTRAINT TB_DEPARTMENT_CATEGORY CHECK(category IN ('y', 'n'));
COMMENT ON COLUMN tb_department.department_no IS '학과 번호';
COMMENT ON COLUMN tb_department.department_name IS '학과 이름';
COMMENT ON COLUMN tb_department.category IS '계열';
COMMENT ON COLUMN tb_department.open_yn IS '개설 여부';
COMMENT ON COLUMN tb_department.capacity IS '정원';
SELECT * FROM ALL_ALL_TABLES WHERE table_name = 'TB_DEPARTMENT';
SELECT * FROM ALL_TAB_COLUMNS WHERE table_name = 'TB_DEPARTMENT';
SELECT * FROM ALL_COL_COMMENTS WHERE table_name = 'TB_DEPARTMENT';
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'TB_DEPARTMENT';
-- 학생 테이블
DROP TABLE tb_student;
CREATE TABLE tb_student(
student_no VARCHAR2(10),
department_no VARCHAR2(10),
student_name VARCHAR2(30),
student_ssn VARCHAR2(14),
student_address VARCHAR2(100),
entrance_date DATE,
absence_yn CHAR(1),
coach_professor_no VARCHAR2(10)
);
ALTER TABLE tb_student ADD CONSTRAINT TB_STUDENT_NO_PK PRIMARY KEY(student_no);
ALTER TABLE tb_student ADD CONSTRAINT TB_STUDENT_DEPARTMENT_NO_FK FOREIGN KEY(department_no) REFERENCES tb_department(department_no);
ALTER TABLE tb_student MODIFY student_name CONSTRAINT TB_STUDENT_NAME_NN NOT NULL;
ALTER TABLE tb_student ADD CONSTRAINT TB_STUDENT_ABSENCE_YN CHECK(absence_yn IN ('y', 'n'));
ALTER TABLE tb_student ADD CONSTRAINT TB_STUDENT_COACH_PROFESSOR_NO_FK FOREIGN KEY(coach_professor_no) REFERENCES tb_professor(professor_no);
COMMENT ON COLUMN tb_student.student_no IS '학생 번호';
COMMENT ON COLUMN tb_student.department_no IS '학과 번호';
COMMENT ON COLUMN tb_student.student_name IS '학생 이름';
COMMENT ON COLUMN tb_student.student_ssn IS '학생 주민번호 ';
COMMENT ON COLUMN tb_student.student_address IS '학생 주소';
COMMENT ON COLUMN tb_student.entrance_date IS '입학 일자';
COMMENT ON COLUMN tb_student.absence_yn IS '휴학 여부';
COMMENT ON COLUMN tb_student.coach_professor_no IS '지도 교수 번호';
SELECT * FROM ALL_ALL_TABLES WHERE table_name = 'TB_STUDENT';
SELECT * FROM ALL_TAB_COLUMNS WHERE table_name = 'TB_STUDENT';
SELECT * FROM ALL_COL_COMMENTS WHERE table_name = 'TB_STUDENT';
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'TB_STUDENT';
-- 과목 테이블
DROP TABLE tb_class;
CREATE TABLE tb_class(
class_no VARCHAR2(10),
department_no VARCHAR2(10),
preattending_class_no VARCHAR2(10),
class_name VARCHAR2(30),
class_type VARCHAR2(10)
);
ALTER TABLE tb_class ADD CONSTRAINT TB_CLASS_NO_PK PRIMARY KEY(class_no);
ALTER TABLE tb_class MODIFY DEPARTMENT_NO CONSTRAINT TB_CLASS_DEPARTMENT_NO_NN NOT NULL;
ALTER TABLE tb_class ADD CONSTRAINT TB_CLASS_DEPARTMENT_NO_FK FOREIGN KEY(department_no) REFERENCES tb_department(department_no);
ALTER TABLE tb_class MODIFY CLASS_NAME CONSTRAINT TB_CLASS_NAME_NN NOT NULL;
COMMENT ON COLUMN tb_class.class_no IS '과목 번호';
COMMENT ON COLUMN tb_class.department_no IS '학과 번호';
COMMENT ON COLUMN tb_class.preattending_class_no IS '선수 과목 번호';
COMMENT ON COLUMN tb_class.class_name IS '과목 이름';
COMMENT ON COLUMN tb_class.class_type IS '과목 구분';
SELECT * FROM ALL_ALL_TABLES WHERE table_name = 'TB_CLASS';
SELECT * FROM ALL_TAB_COLUMNS WHERE table_name = 'TB_CLASS';
SELECT * FROM ALL_COL_COMMENTS WHERE table_name = 'TB_CLASS';
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'TB_CLASS';
-- 과목 교수 테이블
DROP TABLE tb_class_professor;
CREATE TABLE tb_class_professor(
class_no VARCHAR2(10),
professor_no VARCHAR2(10)
);
ALTER TABLE tb_class_professor ADD CONSTRAINT TB_CLASS_PROFESSOR_PK PRIMARY KEY(class_no, professor_no);
ALTER TABLE tb_class_professor ADD CONSTRAINT TB_CLASS_PROFESSOR_CLASS_NO_FK FOREIGN KEY(class_no) REFERENCES tb_class(class_no);
ALTER TABLE tb_class_professor ADD CONSTRAINT TB_CLASS_PROFESSOR_PROFESSOR_NO_FK FOREIGN KEY(professor_no) REFERENCES tb_professor(professor_no);
COMMENT ON COLUMN tb_class_professor.class_no IS '과목 번호';
COMMENT ON COLUMN tb_class_professor.professor_no IS '교수 번호';
SELECT * FROM ALL_ALL_TABLES WHERE table_name = 'TB_CLASS_PROFESSOR';
SELECT * FROM ALL_TAB_COLUMNS WHERE table_name = 'TB_CLASS_PROFESSOR';
SELECT * FROM ALL_COL_COMMENTS WHERE table_name = 'TB_CLASS_PROFESSOR';
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'TB_CLASS_PROFESSOR';
-- 교수 테이블
DROP TABLE tb_professor;
CREATE TABLE tb_professor(
professor_no VARCHAR2(10),
professor_name VARCHAR2(30),
professor_ssn VARCHAR2(14),
professor_address VARCHAR2(100),
department_no VARCHAR2(10)
);
ALTER TABLE tb_professor ADD CONSTRAINT TB_PROFESSOR_NO_PK PRIMARY KEY(professor_no);
ALTER TABLE tb_professor MODIFY professor_name CONSTRAINT TB_PROFESSOR_NAME_NN NOT NULL;
ALTER TABLE tb_professor ADD CONSTRAINT TB_PROFESSOR_DEPARTMENT_NO_FK FOREIGN KEY(department_no) REFERENCES tb_department(department_no);
COMMENT ON COLUMN tb_professor.professor_no IS '교수 번호';
COMMENT ON COLUMN tb_professor.professor_name IS '교수 이름';
COMMENT ON COLUMN tb_professor.professor_ssn IS '교수 주민번호';
COMMENT ON COLUMN tb_professor.professor_address IS '교수 주소';
COMMENT ON COLUMN tb_professor.department_no IS '학과 번호';
SELECT * FROM ALL_ALL_TABLES WHERE table_name = 'TB_PROFESSOR';
SELECT * FROM ALL_TAB_COLUMNS WHERE table_name = 'TB_PROFESSOR';
SELECT * FROM ALL_COL_COMMENTS WHERE table_name = 'TB_PROFESSOR';
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'TB_PROFESSOR';
-- 성적 테이블
DROP TABLE tb_grade;
CREATE TABLE tb_grade(
term_no VARCHAR2(10),
class_no VARCHAR2(30),
student_no VARCHAR2(14),
point NUMBER
);
ALTER TABLE tb_grade ADD CONSTRAINT TB_GRADE_TERM_NO_PK PRIMARY KEY(term_no);
ALTER TABLE tb_grade ADD CONSTRAINT TB_GRADE_CLASS_NO_FK FOREIGN KEY(class_no) REFERENCES tb_class(class_no);
ALTER TABLE tb_grade ADD CONSTRAINT TB_GRADE_STUDNET_NO_FK FOREIGN KEY(student_no) REFERENCES tb_student(student_no);
ALTER TABLE tb_grade ADD CONSTRAINT TB_GRADE_POINT CHECK(point IN (3,2));
COMMENT ON COLUMN tb_grade.term_no IS '학기 번호';
COMMENT ON COLUMN tb_grade.class_no IS '과목 번호';
COMMENT ON COLUMN tb_grade.student_no IS '학생 번호';
COMMENT ON COLUMN tb_grade.point IS '학점';
SELECT * FROM ALL_ALL_TABLES WHERE table_name = 'TB_GRADE';
SELECT * FROM ALL_TAB_COLUMNS WHERE table_name = 'TB_GRADE';
SELECT * FROM ALL_COL_COMMENTS WHERE table_name = 'TB_GRADE';
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'TB_GRADE';
SELECT * FROM ALL_ALL_TABLES WHERE table_name LIKE 'TB%';
이상 DML(INSERT,DELETE,UPDATE) , DDL(CREATE)를 마치겠습니다.
