글자가 저장되는 데이터 타입 = varchar2()
날짜가 저장돼있는 데이터 타입 = date()
숫자가 저장되어 있는 데이터 타입 = number()
뒤에 괄호는 바이트수를 나타낸다.
ddl
create table로 만들면 alter table로 지운다.
• DDL(Data Definition Language)
‐ CREATE → create로 만든 거만 무조건 alter로 구조를 고칠 수 있다. 지우려면 drop으로 지운다.
‐ ALTER
‐ DROP
‐ RENAME → 오브젝트 이름을 바꿈.(사용할 일 없다.)
‐ TRUNCATE → 테이블 안을 비워라는 말임.
세그먼트가 아닌 애들은 존재 안 함.
공간을 할당받으면 안 써도 가지고 있는다. delete로 지워도 공간을 가지고 있는다.
truncate는 테이블 지웠다 새로 만들어라는 뜻. 그래서 테이블을 새로 만들었을 때랑 똑같다.
테이블 생성과 삭제
CREATE TABLE 테이블 (
컬럼 데이터_타입 [DEFAULT 값] [컬럼 레벨 제약조건],
......
[테이블 레벨 제약조건],
.....
);
DROP TABLE 테이블 [CASCADE CONSTRAINT];
ㄴ 테이블을 지우는 것 사실상 거의 불가능하다.(사용 중인 데이터베이스에서.)
PURGE RECYCLEBIN;
ㄴ 휴지통 비우기라고 생각하면 된다.(drop table 사용하면 여기로 온다.)
테이블생성규칙
• 문자로 시작한다.
• 30자 이내로 한다.
• 영문, 숫자, _, $, # 을 사용한다.
‐ 한글사용은 가능하지만 되도록 사용하지 않는 것이 좋다.
• 테이블의 이름은 동일한 유저(스키마) 안에서 유일해야 한다.
• 예약어는 사용이 불가능하다.
• 대소문자를 구별하지 않는다.
‐ 생성할 때사용한 문자와는 관계없이 모든 이름은 대문자로 정의된다.
‐ 테이블이름은 딕셔너리에 저장되는데, 모두 대문자로 저장된다.
데이터타입
VARCHAR2(n)
‐ 가변길이문자타입(1 < n < 4000 byte)
CHAR(n)
‐ 고정길이문자타입(1 < n < 2000 byte)
NUMBER(n,p)
‐ 숫자타입, n은 전체자릿수이고 p는 소수점이하자릿수이다.
‐ 전체자릿수를 초과할 경우 입력거부되지만 소수점이하자릿수가 초과되면반올림되어 입력된다.
DATE
‐ 날짜타입 출력이 나입력 형식과 무관 하게 YYYY/MM/DD:HH24:MI:SS 형태 조저장된다. (기원전 4712년 1월 1일~ 서기 9999년 12월 31일
LONG
‐ 2GB까지 저장가능한 가변길이문자타입, 조건검색할 수 없으며 테이블에는 하나의 LONG 컬럼만정의 할 수 있다.
CLOB
‐ LONG를 개선한 타입, 최대 4G까지 저장가능하고 한 테이블의 여러 컬럼에 정의할 수 있다.
BLOB
‐ 4G까지 저장가능한가 변길이이진타입
ROW(n)
‐ 가변길이이진타입
BFILE
‐ 4G 이내의외부파일저장을 위한 이진타입 → 동영상, 미디어 타입을 처리하는 건 좀 안 좋다.
ROWID
‐ ROWID를 저장하기 위한 데이터타입, 각문자는 64진 수로엔코딩되어있다
테이블생성 관련명령어 → 꼭 외우자.
SQL> SELECT * FROM tab; → tab은 가상 테이블 SQL> SELECT table_name FROM user_tables;
‐ 스키마에 테이블목록을 검색한다.
‐ 스키마는 유저와동일하게 쓰인다.
SQL> DESC 테이블;
SQL> SELECT table_name, column_name, data_type, data_length
2 FROM user_tab_columns
3 [WHERE table_name = '테이블'];
‐ 테이블의 컬럼구성을 확인한다.
정의 정보는 봐도 된다.
통계정보는 봐도 의미가 없다.
테이블생성과 확인
테이블명: board(게시판)
구성컬럼: no(게시물번호), name(작성자), sub(제목), content(내용), hdate(입력일시)
SQL> CREATE TABLE board (
2 no NUMBER,
3 name VARCHAR2(50),
4 sub VARCHAR2(100),
5 content VARCHAR2(4000),
6 hdate DATE DEFAULT SYSDATE
7 );
SQL> SELECT * FROM tab;
SQL> SELECT table_name FROM user_tables
2 WHERE table_name = 'BOARD';
• 생성된 테이블에 행을 입력하고 default 입력을 확인한다.
SQL> DESC board;
SQL> SELECT table_name, column_name, data_type, data_length
2 FROM user_tab_columns
3 WHERE table_name = 'BOARD';
SQL> INSERT INTO board (no) VALUES (1);
SQL> COMMIT;
SQL> SELECT * FROM board;
문자타입데이터
- 문자와 숫자
‐ 주민번호, 군번, 숫자만으로 구성된 학번 : 문자
． 개별자리가 의미가 있는 경우 항상 문자형 데이터
． 연산가능성이 없다.
‐ 금액: 숫자
． 연산가능성이 있으면 숫자
． NULL에 주의한다. - VARCHAR, VARCHAR2
‐ 같은 데이터타입
‐ 앞으로 뭔가 변경될 것이다.??? - CHAR, VARCHAR2
‐ CHAR의 장점은 없다.
‐ VARCHAR2를 쓴다.
CHAR, VARCHAR2의 비교
• 동일한 값이 입력된 두 컬럼이데이 터 타입에 따라 다르게 인식됨을 확인한다.
SQL> CREATE TABLE comp(
2 co1 CHAR(4),
3 co2 VARCHAR2(4)
4 );
SQL> INSERT INTO comp VALUES ('AA', 'AA');
SQL> SELECT LENGTHB(co1), LENGTHB(co2) FROM comp;
ㄴ co1은 4, co2는 2가 나온다.
SQL> SELECT * FROM comp WHERE co1='AA';
SQL> SELECT * FROM comp WHERE co2='AA';
ㄴ 둘 다 AA가 나온다.
SQL> SELECT * FROM comp WHERE co1=co2;
ㄴ no rows selected 나온다.
날짜타입 컬럼검색
날짜데이터입력 시에 sysdate 등의 자동으로 입력되는 기능을 이용할 때 주의사 항
SQL> CREATE TABLE hd (
2no NUMBER,
3 hdate DATE 4 );
SQL> INSERT INTO hd VALUES (1, sysdate); SQL> SELECT * FROM hd;
ㄴ 오늘 날짜가 나온다.
SQL> SELECT * FROM hd WHERE hdate = '2021/10/02';
ㄴ no rows selected
SQL> SELECT no, TO_CHAR(hdate,'YYYY/MM/DD:HH24:MI:SS') FROM hd;
ㄴ 1 2025/09/23:10:56:08
SQL> SELECT * FROM hd
2 WHERE hdate BETWEEN '2021/10/02' AND '2021/10/03';
ㄴ 1 2025/09/23
‐ 더 좋지 못한 조건문: WHERE hdatelike '2021/10/02%';
테이블삭제
• board, comp, hd 테이블을 정말 삭제한다.
SQL> SELECT * FROM tab;
SQL> DROP TABLE board;
SQL> DROP TABLE comp;
SQL> DROP TABLE hd;
SQL> SELECT * FROM tab;
SQL> PURGE RECYCLEBIN;
SQL> SELECT * FROM tab;
테이블관리
서브쿼리를 이용한 테이블생성
CREATE TABLE 테이블 [(
컬럼 [DEFAULT 값],
...
)]
AS (SELECT 문장 : Sub query문);
‐ 서브쿼리실행결과를 테이블로 생성한다.
‐ 컬럼목록이 생략되면 서브쿼리의 열이름이 생성될 테이블의 컬럼명이 된다.
‐ 컬럼에 DEFAULT항목을 지정할 수 있다.
‐ 테이블을 복사하거나 일부를 별도로저장하는 용도로 사용한다.
• 사번, 이름, 업무, 부서 정보만을 갖는 테이블을 생성한다.
SQL> CREATE TABLE emp2
2 AS
3 SELECT eno 사번, ename 이름, job 업무, d.dno 부서번호, dname 부서
4 FROM emp e, dept d
5 WHERE d.dno=e.dno;
SQL> DESC emp2;
SQL> SELECT * FROM emp2;
또는
SQL> CREATE TABLE emp2 (사번, 이름, 업무, 부서번호, 부서)
2 AS
3 SELECT eno, ename, job, d.dno, dname
4 FROM emp e, dept d
5 WHERE d.dno=e.dno;
하지만 이행종속이 발생해서 잘못 만들었습니다.
테이블관리(ALTER TABLE)
ALTER TABLE 테이블
ADD (컬럼 데이터_타입 [DEFAULT 값], ...);
ALTER TABLE 테이블
MODIFY (컬럼 데이터_타입 [DEFAULT 값], ...);
ALTER TABLE 테이블
DROP COLUMN 컬럼;
• ALTER TABLE 명령
‐ 컬럼 추가
‐ 컬럼 수정
‐ 컬럼 삭제
컬럼추가 → 컬럼을 추가하는 일은 애초에 없어야 한다.
• emp2 테이블에 입사일 컬럼을 추가한다.
SQL> ALTER TABLE emp2
2 ADD (hdate DATE);
SQL> DESC emp2;
SQL> SELECT * FROM emp2;
‐ 추가되는 열은 마지막에 위치한다.
‐ 열의위치를 지정할 수 없다.
‐ 추가된 열에는 NULL이 저장된다.
- db의 행은 순서대로 안 들어간다.
컬럼변경,컬럼삭제
ㄴ 그런 작업을 할 필요가 없는 것이 정상이다.
테이블내용완전삭제
TRUNCATE TABLE 테이블;
‐ 테이블의 내용(행, 공간)을완전히삭제한다.
‐ DDL 작업으로 ROLLBACK 할 수 없다.
‐ 'DELETE FROM 테이블'과 비슷하지만 공간까지 해제하는 차이가 있다.
． DELETE에 비해 속도가 빠르다.
• emp2 테이블의 내용과 구조를 완전히 삭제한다.
SQL> TRUNCATE TABLE emp2;
테이블이름변경
RENAME OLD_NAME TO NEW_NAME;
‐ 테이블, 뷰(VIEW), 시퀀스등다양한 대상의 이름을 변경할 수 있다.
• emp2 테이블의 이름을 eemp로 수정한다.
SQL> RENAME emp2 TO eemp;
SQL> SELECT * FROM tab;
PK, FK
제약 조건의 이해
• 제약 조건
‐ 테이블 단위에서 정의되고 적용된다.
‐ 종속성이 존재하는 경우 테이블의 삭제를 막아준다.
‐ 자료가 삽입, 갱신, 삭제될 때마다 규칙이 적용된다.
‐ 일시적으로 활성화하거나 비활성화하는 것이 가능하다.
‐ 제약조건은 개체처럼 관리되므로 반드시 이름이 필요하다.
• 오라클에서 제공되는 제약조건
‐ PRIMARY KEY
‐ FOREIGN KEY
‐ UNIQUE KEY
‐ NOT NULL
‐ CHEC
- PK → 결정 인자. 이걸 알면 나머지를 찾거나 구별할 수 있다.
‐ Primary Key, 주 키, 주 식별자
‐ 테이블마다 한 개만 정의할 수 있다.
‐ 테이블 내에 모든 행을 유일하도록 식별해주는 컬럼
‐ 모든 컬럼은 PK 컬럼에 **함수적 종속관계(functional dependency)**를 갖는다.
． 학번→ (이름, 학년, 학과,...)
‐ 중복될 수 없고 NULL을 허용하지 않는다.
‐ 고유 인덱스가 자동으로 생성된다.
- FK → 참조하는 놈.(항상 자식테이블에 있다. 부모테이블에는 존재 불가능.)
‐ Foreign Key, 외부키, 외부식별자
‐ 테이블간관계(Relationship)를 의미한다.
‐ 항상 부모자식 관계이다.
‐ 자식 테이블의 참조 컬럼에 지정한다.
‐ 두 컬럼에 데이터타입이 일치해야 한다.
‐ PK나 UK만 참조 가능하다.
ㄴ 일반적으로 pk만 참조한다.
PK, FK 설정
SQL> CREATE TABLE 테이블 (
2 .....
3 CONSTRAINT 제약_조건 PRIMARY KEY (컬럼));
ㄴ 주 키가 2개면 컬럼부분에 2개 넣으면 됩니다.
SQL> CREATE TABLE 테이블 (
2 .....
3 CONSTRAINT 제약_조건 FOREIGN KEY (컬럼)
4 REFERENCES 참조할_테이블 (참조할_컬럼) [ON DELETE CASCADE]);
제약조건검색
SQL> SELECT c.table_name, c.constraint_name, c.constraint_type,
2 c.status, s.column_name
3 FROM user_constraints c, user_cons_columns s
4 WHERE c.constraint_name = s.constraint_name
5 AND c.table_name in (검색_대상_테이블_목록)
6 ORDER BY c.table_name;
SQL> SELECT p.table_name 상위테이블, p.constraint_name 상위제약조건,
2 c.table_name 하위테이블, c.constraint_name 참조제약조건
3 FROM user_constraints p, user_constraints c
4 WHERE c.r_constraint_name=p.constraint_name
5 AND p.table_name in (검색_대상_테이블_목록)
6 ORDER BY p.table_name;
‐ ucon.sql, ref.sql 스크립트로 저장해서 사용
PK와 FK 설정테이블생성
dept부터 만들어준다.
SQL> CREATE TABLE dept (
2 dno VARCHAR2(2),
3 dname VARCHAR2(15),
4 loc VARCHAR2(9),
5 CONSTRAINT dept_dno_pk PRIMARY KEY(dno)
6 );
SQL> CREATE TABLE emp (
2 eno VARCHAR2(4),
3 ename VARCHAR2(15),
4 sex VARCHAR2(4),
5 job VARCHAR2(12),
6 mgr VARCHAR2(4),
7 hdate DATE,
8 sal NUMBER,
9 comm NUMBER,
10 dno VARCHAR2(2),
11 CONSTRAINT emp_eno_pk PRIMARY KEY (eno),
12 CONSTRAINT emp_mgr_fk FOREIGN KEY (mgr) REFERENCES emp (eno),
13 CONSTRAINT emp_dno_fk FOREIGN KEY (dno) REFERENCES dept (dno)
14 );
오류
SQL> INSERT INTO dept (dno, dname, loc) VALUES ('10', '총무', '부산');
SQL> DELETE FROM dept WHERE dno = '10';
ㄴ 10번 안에 사람이 있는데 지워버리면 없는 부서사람이 되기 때문.
SQL> INSERT INTO emp (eno,ename, dno) VALUES ('2001','손하늘','20');
ㄴ 20번 부서가 없기 때문에
SQL> UPDATE emp SET dno = '20' WHERE ename = '문시현';
ㄴ 20번 부서가 없는데 20번 부서를 바꾸려 해서
‐ 조건은 입력(수정, 삭제) 작업 시에 발생한다.
‐ 오라클에서 제약조건은 지연가능하다.
논리모델링부터 만들고 물리 모델링을 한다.