SQL 간만에 보니까 다 까먹었군요...기본 테이블인만큼 한줄씩 뜯어봅시다.
DDL: 데이터 정의어(Data Definition Language) : CREATE, ALTER, DROP, RENAME
DML : 데이터 조작어(Data Manipulation Language) : INSERT, SELECT, UPDATE, DELETE
DCL : 데이터 제어어(Data Control Language) : GRANT, REVOKE
▼ 전체보기
뭐 이렇게 구성되어 있었는데 그중에 일부만 쓰자.
SET SCHEMA PUBLIC
현재 사용하고자 하는 기본 스키마(Schema)를 설정.
"앞으로 사용할 테이블들은 전부 PUBLIC이라는 폴더에 저장할 거야!"
🔍 SCHEMA란?
스키마는 데이터베이스 안에서 객체들(테이블, 뷰, 인덱스, 시퀀스 등)을 논리적으로 구분하는 공간 또는 폴더.
예를 들어 PUBLIC이라는 스키마 안에 여러 테이블들이 들어있는 것.
🧩 왜 SET SCHEMA PUBLIC을 하나요?
PUBLIC 스키마를 현재 SQL 실행의 기본 스키마로 설정해서, 앞으로 만들거나 조회할 테이블들이 이 PUBLIC 스키마 안에 속하게 된다.
만약 이걸 하지 않으면, 다른 스키마를 사용하는 경우 SQL 문이 원하는 테이블을 찾지 못할 수 있다.
SET DATABASE SQL SYNTAX ORA TRUE
SQL 문법을 오라클(Oracle) 스타일로 허용. (현재 HSQL로 하고 있지만 오라클 방식도 허용하겠다는 뜻)
"이 데이터베이스에서는 오라클(Oracle) 방식의 SQL 문법도 허용할게!"
HSQLDB는 표준 SQL 문법, 오라클 스타일, MySQL 스타일 등을 지원하는데, 그 중 오라클 DB의 문법을 활성화하겠다는 듯.
ex. DUAL(임시용도의 가상 테이블), SYSDATE(현재 날짜와 시간 반환)
CREATE MEMORY TABLE PUBLIC.EMP (
EMPNO VARCHAR(4) NOT NULL PRIMARY KEY,
ENAME VARCHAR(30),
JOB VARCHAR(9),
JOBSTATUS VARCHAR(9),
MGR VARCHAR(4),
HIREDATE VARCHAR(8),
SAL DOUBLE,
COMM DOUBLE,
DEPTNO VARCHAR(2),
LV INTEGER,
ORDERNUM INTEGER
)
EMP 테이블.
직원번호(EMPNO) 를 PK로 설정한 직원 정보 테이블을 만든다.
- MEMORY TABLE: 메모리에만 존재하는 테이블로, DB를 끄면 사라짐.
- PUBLIC: 맨 처음 설정한 스키마(테이블이 들어있는 폴더)의 이름.
- EMP: 만든 테이블 이름.
- EMPNO VARCHAR(4) NOT NULL PRIMARY KEY : 직원번호(EMPNO) 는 기본키(PRIMARY KEY)로, 중복되거나 비워져서는 안된다(NOT NULL)
- SAL DOUBLE, ORDERNUM INTEGER : INTEGER 는 정수, DOUBLE은 실수(소수점 가능) SAL은 급여 컬럼으로 소수점이 가능하나, ORDERNUM 은 정렬 순서로 정수만 나올 수 있음.
CREATE SEQUENCE AUTO_INCREMENT
AS INTEGER
START WITH 1
INCREMENT BY 1;
AUTO_INCREMENT라는 이름의 시퀀스를 생성.
정수(INTEGER) 타입이고, 1부터 시작해서 1씩 증가한다.
이 시퀀스는 주로 자동으로 증가하는 숫자를 생성할 때 사용한다.
ex) 테이블에 새로운 행을 삽입할 때 고유한 ID 값을 자동으로 생성하고 싶을 때
이 이후 Insert 할 때
INSERT INTO EMP (회원번호, 이름)
VALUES (NEXT VALUE FOR AUTO_INCREMENT, '홍길동');
이렇게 하면 회원번호에 자동으로 1,2,3... 이렇게 들어간다.
시퀀스는 전체 테이블에 동작하는건 아니고,
원하는 테이블/컬럼에서 NEXT VALUE FOR 시퀀스명을 직접 사용해야만 동작한다.
-> 확인해보니 MOVIEINFO 테이블에 쓰려고 만든 것. 일반 EMP와 DEPT 테이블에서는 필요가 없다.
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
데이터베이스를 처음 열었을 때 사용할 기본 스키마를 PUBLIC으로 설정.
"앞으로 이 DB를 열면 자동으로 PUBLIC 스키마를 기본으로 사용하게 해!"
여러개 스키마를 쓸 수 있게 하는데 DB 열면 그냥 기본이 PUBLIC으로 하자.
스키마를 PUBLIC 한개만 만들어서 없어도 상관없긴하다.
하지만 스크립트를 이식할 때 다른 환경에서는 스키마가 여러개일 수 있으니 기본값을 정해놓은 것이다.
ex) 다른 스키마가 생길 수 있는 환경, 도구 연동, 이식성 대비
또한 HSQLDB에서 초기 접속 시점에 기본 스키마를 명확히 해주면, 접속한 유저나 설정에 따라 초기 스키마가 null, unknown일 경우 오류가 발생하지 않도록 해준다.
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
INFORMATION_SCHEMA라는 특수한 스키마에 있는 도메인들(domain)에 대한 권한을 부여.
"DB 내부 구조(테이블명, 날짜, 숫자, 텍스트 등)에 관련된 기본 데이터 타입들에 대해,
모든 사용자(PUBLIC)가 사용할 수 있도록 허용해!"
이것도 꼭 필요한 구문은 아님.
HSQLDB나 다른 RDB에서 내부적으로 자동으로 생성되는 초기 스크립트에 포함되어있는 경우로 그냥 자동 생성된거다.
DB 새로 만들 때
어떤 툴(DBeaver, HSQL Database Manager 등)을 통해 내보내기(export) 했을 때
시스템 테이블을 백업/초기화할 때
이런 경우 혹시 필요한 경우를 대비해 PUBLIC(모든 사용자)이 기본적인 메타 도메인 타입에 접근할 수 있도록 미리 권한을 주는 것이다.
식별자(SQL_IDENTIFIER), yes, no 같은 제한된 타입(YES_OR_NO), 타임스탬프(TIME_STAMP), 숫자(CARDINAL_NUMBER), 문자열(CHARACTER_DATA) 타입의 도메인을 모든 사용자가 사용할 수 있게 하용한다.
✅ 도메인(domain)이란?
도메인(Domain)은 데이터베이스 설계에서 데이터의 범위와 형식을 제한하는 방법으로, SQL에서는 컬럼에 대해 허용할 수 있는 값의 타입과 규칙을 정의하는 객체.
간단히 말하면, 특정 데이터 타입에 대해 제약 조건을 설정하는 것이다.
예를 들어, 날짜, 전화번호, 금액 등의 규격을 정의하고, 이를 여러 테이블에서 재사용할 수 있게 해준다.
도메인을 사용하면 중복 코드를 줄이고, 데이터의 일관성 및 무결성을 유지하는 데 유리하다.
일반적으로 SQL 데이터 타입(예: VARCHAR, INTEGER, DATE 등)과 유사하지만, 그것들을 특정 규칙과 함께 제한할 수 있다.
예를 들어, 날짜 데이터에서 생일은 미래일 수 없으니 과거 날짜만 가능하도록 하고 싶다면?
CREATE DOMAIN PastDate AS DATE
CHECK (VALUE <= CURRENT_DATE);
이렇게 PastDate 라는 이름의 도메인을 생성하고
CREATE TABLE EMPLOYEE (
EMP_ID INTEGER PRIMARY KEY,
NAME VARCHAR(100),
BIRTHDATE PastDate
);
이렇게 설정하면 생일 컬럼에 위 규칙을 추가할 수 있다.
예를 들어, 전화번호와 같은 특정 형식을 따라야하는 경우?
CREATE DOMAIN PhoneNumber AS VARCHAR(15)
CHECK (VALUE LIKE '(___)____-____' OR VALUE LIKE '(XXX)XXX-XXXX');
-을 포함한 11자리 숫자만 허용하는 도메인 생성.
INFORMATION_SCHEMA는 내가 생성한 스키마가 아니라 SQL 표준에 정의된 데이터 베이스의 메타데이터를 관리하는 스키마로, 그냥 기본 설정파일 같은거라고 보면 된다.
얘네의 기본 설정값을 최초에 정의한 스키마 PUBLIC에게 사용권한을 주는 명령어가 바로
GRANT USAGE ON DOMAIN 이다.
-- EMP 테이블에 한 행을 삽입
INSERT INTO EMP
VALUES
(
'3092', -- EMPNO: 직원 번호 (직원의 고유 번호, '3092'번 직원)
'JOHN', -- ENAME: 직원 이름 ('JOHN')
'CLERK', -- JOB: 직무 ('CLERK', 사무직)
'OD', -- JOBSTATUS: 직무 상태 ('OD', 직무 상태 코드, 예: "On Duty"일 수 있음)
'7782', -- MGR: 상사 직원 번호 (상사의 직원 번호, 이 직원의 상사는 '7782'번 직원)
'20160601', -- HIREDATE: 고용 날짜 ('20160601', 직원이 고용된 날짜, 'YYYYMMDD' 형식)
4300.0E0, -- SAL: 급여 (4300.0, 급여 금액, 'E0'는 지수 표기법으로 4300.0을 의미)
100.0E0, -- COMM: 커미션 (100.0, 커미션 금액, 'E0'는 지수 표기법으로 100.0을 의미)
'10', -- DEPTNO: 부서 번호 (부서 번호 '10'번 부서에 속한 직원)
3, -- LV: 직원 레벨 (3, 직원의 직급, 예를 들어, 3은 특정 직급을 나타냄)
14 -- ORDERNUM: 직원 순번 (부서 내에서 이 직원의 순번, 예를 들어, 14번째 직원)
);
EMP 테이블에 각각 해당하는 데이터를 넣는다.
-- DEPT 테이블에 한 행을 삽입
INSERT INTO DEPT
VALUES
(
'10', -- DEPTNO: 부서 번호 ('10', 부서 번호 10번)
'ACCOUNTING', -- DNAME: 부서 이름 ('ACCOUNTING', 회계 부서)
'NEWYORK' -- LOC: 부서 위치 ('NEWYORK', 부서의 위치)
);
DEPT 테이블에 각각 해당하는 데이터를 넣는다.
결론적으로 EMP/DEPT 테이블 기본 설정 끝!
SET SCHEMA PUBLIC
SET DATABASE SQL SYNTAX ORA TRUE
--테이블 생성
CREATE MEMORY TABLE PUBLIC.EMP(EMPNO VARCHAR(4) NOT NULL PRIMARY KEY,ENAME VARCHAR(30),JOB VARCHAR(9),JOBSTATUS VARCHAR(9),MGR VARCHAR(4),HIREDATE VARCHAR(8),SAL DOUBLE,COMM DOUBLE,DEPTNO VARCHAR(2), LV INTEGER, ORDERNUM INTEGER)
CREATE MEMORY TABLE PUBLIC.DEPT(DEPTNO VARCHAR(2) NOT NULL PRIMARY KEY,DNAME VARCHAR(14),LOC VARCHAR(13))
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
--데이터 삽입
INSERT INTO EMP VALUES('3092','JOHN','CLERK','OD','7782','20160601',4300.0E0,100.0E0,'10', 3, 14)
INSERT INTO EMP VALUES('7499','ALLEN','SALESMAN','OD','7698','20130619',1600.0E0,300.0E0,'30', 3, 11)
INSERT INTO EMP VALUES('7521','WARD','SALESMAN','LV','7698','19810222',1250.0E0,500.0E0,'30', 3, 7)
INSERT INTO EMP VALUES('7566','JONES','MANAGER','OD','7839','19810402',2975.0E0,0.0E0,'20', 2, 2)
INSERT INTO EMP VALUES('7654','MARTIN','SALESMAN','OD','7698','19810928',1250.0E0,1400.0E0,'10', 3, 9)
INSERT INTO EMP VALUES('7698','BLAKE','MANAGER','LV','7839','19810501',2850.0E0,0.0E0,'30', 2, 6)
INSERT INTO EMP VALUES('7782','CLARK','MANAGER','OD','7839','19810609',2450.0E0,0.0E0,'10', 2, 12)
INSERT INTO EMP VALUES('7788','SCOTT','ANALYST','OD','7566','19870713',99999.0E0,0.0E0,'10', 3, 4)
INSERT INTO EMP VALUES('7839','KING','PRESIDENT','OD','','19811117',5000.0E0,0.0E0,'10', 1, 1)
INSERT INTO EMP VALUES('7844','TURNER','SALESMAN','OD','7698','19810908',1500.0E0,0.0E0,'30', 3, 8)
INSERT INTO EMP VALUES('7876','ADAMS','CLERK','RS','7788','19870713',1100.0E0,0.0E0,'20', 4, 5)
INSERT INTO EMP VALUES('7900','JAMES','CLERK','SB','7698','19811203',950.0E0,0.0E0,'30', 3, 10)
INSERT INTO EMP VALUES('7902','FORD','ANALYST','SB','7566','19811203',3000.0E0,100.0E0,'20', 3, 3)
INSERT INTO EMP VALUES('7934','MILLER','CLERK','LV','7782','19820123',1300.0E0,200.0E0,'10', 3, 13)
INSERT INTO DEPT VALUES('10','ACCOUNTING','NEWYORK')
INSERT INTO DEPT VALUES('20','RESEARCH','DALLAS')
INSERT INTO DEPT VALUES('30','SALES','CHICAGO')
INSERT INTO DEPT VALUES('40','OPERATIONS','BOSTON')
INSERT INTO DEPT VALUES('50','R&D','NEWYORK')
INSERT INTO DEPT VALUES('60','TECH SUPPORT','DALLAS')
INSERT INTO DEPT VALUES('70','MARKETING','CHICAGO')
INSERT INTO DEPT VALUES('80','DESIGN','BOSTON')
INSERT INTO DEPT VALUES('90','FACTORY1','NEWYORK')
INSERT INTO DEPT VALUES('91','FACTORY2','CHICAGO')
'[ 정보처리기사 ]' 카테고리의 다른 글
| 정보처리기사 필기 후기 (0) | 2024.05.13 |
|---|---|
| 정보처리기사 1과목 정리 (0) | 2024.04.15 |
| 정보처리기사 실기 2020~2022 SQL 문제 모아보기 (14) | 2022.09.29 |
| 정보처리기사 실기(1) 2020~2022 문제유형 분석하기 (3) | 2022.09.29 |
