▶ 테이블 생성과 삭제
CREATE TABLE 테이블 (
컬럼 데이터_타입 [DEFAULT default값] [컬럼 레벨 제약조건],
컬럼 데이터_타입 [DEFAULT default값] [컬럼 레벨 제약조건],
......
[테이블 레벨 제약조건],
.....
);
- 데이터_타입
: 컬럼에 입력될 데이터의 종류와 크기를 결정한다.
- DEFAULT
: 입력이 누락됐을 때 기본 입력 값을 정의한다.
: Default 값을 지정하지 않으면 널 값이 저장된다.
- 컬럼 레벨 제약 조건
: PK, FK, UK, CHECK, NOT NULL등을 지정한다.
- 테이블 레벨 제약 조건
: PK, FK, UK, CHECK만 지정한다.
: NOT NULL은 정의할 수 없다.
DROP TABLE 테이블
[CASCADE CONSTRAINT];
PURGE RECYCLEBIN; //휴지통 비우기
SELECT table_name
FROM user_tables; //내가 소유하고 있는 table만 보고 싶을 경우
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
[WHERE table_name = '테이블']; //'테이블' 여기서 따옴표 안 테이블 명은 무조건 대문자로 적기!
▶ 테이블 생성에서 이름 규칙
- 문자로 시작한다.
- 30자 이내로 한다.
- 영문, 숫자, _, $, #만을 사용한다.
(한글 사용은 가능하지만 되도록 사용하지 않는 것이 좋다.)
- 테이블의 이름은 동일한 유저(스키마) 안에서 유일해야 한다.
- 예약어는 사용이 불가능하다.
- 대소문자를 구별하지 않는다.
(생성할 때 사용한 문자와는 관계없이 모든 이름은 대문자로 정의된다.)
(테이블 이름은 딕셔너리에 저장되는데, 모두 대문자로 저장된다.)
(따옴표 안에 있는 대소문자는 구별한다.)
▶ 데이터 타입
- 오라클은 다양한 데이터 타입을 제공한다. 다음은 그 중 많이 사용되는 데이터 타입들이다.
- 문자 타입
: VARCHAR2, CHAR, LONG, CLOB (CHAR 타입은 안 써도 된다.)
- 숫자 타입
: NUMBER
- 날짜 타입
: DATE
- 이진 타입
: RAW, LONG RAW, BLOB, BFILE
- ROWID 타입
: ROWID
ROWID : 행이 저장되어 있는 물리적인 위치
SELECT ROWID, eno, ename FROM emp;
ROWID ENO ENAME
------------------ ------- ----------
AAASO6AAFAAAAIOAAA 0001 안영희
AAASO6AAFAAAAIOAAB 0201 안영숙
AAASO6AAFAAAAIOAAC 0202 손하늘
AAASO6AAFAAAAIOAAD 0301 이승철
AAASO6AAFAAAAIOAAE 0302 박선경
AAASO6AAFAAAAIOAAF 1001 문시현
AAASO6AAFAAAAIOAAG 1002 김주란
예제 1. 다음과 같은 구조의 테이블을 생성한다.(데이터 타입은 입력될 내용에 따라 결정한다.)
* 테이블명 : board
- 컬럼 구성 : no(게시물번호), name(작성자), sub(제목), content(내용), hdate(입력일시)
CREATE TABLE board (
no NUMBER,
name VARCHAR2(50),
sub VARCHAR2(100),
content VARCHAR2(4000),
hdate DATE DEFAULT SYSDATE
);
테이블이 생성되었습니다.
SELECT table_name FROM user_tables
WHERE table_name = 'BOARD';
TABLE_NAME
-------------
BOARD
DESC board;
이름 널? 유형
---------------- -------- ----------------
NO NUMBER
NAME VARCHAR2(50)
SUB VARCHAR2(100)
CONTENT VARCHAR2(4000)
HDATE DATE
COL table_name FORMAT A10
COL column_name FORMAT A10
COL data_type FORMAT A10
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'BOARD';
TABLE_NAME COLUMN_NAM DATA_TYPE DATA_LENGTH
---------- ---------- ---------- -----------
BOARD NO NUMBER 22
BOARD NAME VARCHAR2 50
BOARD SUB VARCHAR2 100
BOARD CONTENT VARCHAR2 4000
BOARD HDATE DATE 7
INSERT INTO board (no) VALUES (1);
1 개의 행이 만들어졌습니다.
COMMIT;
커밋이 완료되었습니다.
SELECT * FROM board;
NO NAME SUB CONTENT HDATE
---------- ---------- ---------- ---------- ----------
1 2021/11/18
▶ 데이터 딕셔너리(Data Dictionary)
데이터 딕셔너리는 데이터베이스의 상태나 내부 운영과 관련된 정보를 저장해주는 테이블이다.
▶ 딕셔너리 이름의 접두어
- DBA_ : 관리자만 검색 가능한 딕셔너리에 붙는 접두어
(DBA_TABLES, DBA_INDEXES, DBA_TABLESPACES, DBA_DATA_FILES ...)
- ALL_ : 일반 사용자가 검색 가능한 딕셔너리, 접근 가능한 개체에 대한 정보를 담고 있다.
(ALL_TABLES, ALL_CONSTRAINTS, ALL_INDEXES, ALL_VIEWS, ...)
- USER_ : 일반 사용자가 검색 가능한 딕셔너리, 소유하고 있는 개체에 대한 정보를 담고 있다.
(USER_TABLES, USER_CONSTRAINTS, USER_INDEXES, USER_VIEWS, ...)
예제 2. 테이블을 생성하고 데이터를 입력 확인한다.
SQL> CREATE TABLE t1 (no NUMBER(4,2));
테이블이 생성되었습니다.
SQL> INSERT INTO t1 VALUES (12.12);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO t1 VALUES (1.789);
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM t1;
NO
----------
12.12
1.79
SQL> INSERT INTO t1 VALUES (123.1);
INSERT INTO t1 VALUES (123.1)
*
1행에 오류:
ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.
SQL> CREATE TABLE t2 (name VARCHAR2(3));
테이블이 생성되었습니다.
SQL> INSERT INTO t2 VALUES ('AAA');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO t2 VALUES ('장강');
INSERT INTO t2 VALUES ('장강')
*
1행에 오류:
ORA-12899: "ST"."T2"."NAME" 열에 대한 값이 너무 큼
SQL> INSERT INTO t2 VALUES ('장');
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM t2;
NAME
----
AAA
장
예제 3. 다음 예제를 통해 CHAR와 VARCHAR2의 차이를 이해해 보자
SQL> CREATE TABLE comp(
2 co1 CHAR(4),
3 co2 VARCHAR2(4)
4 );
테이블이 생성되었습니다.
SQL> INSERT INTO comp VALUES ('AA','AA');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO comp VALUES ('AAAA','AAAA');
1 개의 행이 만들어졌습니다.
SQL> SELECT LENGTHB(co1), LENGTHB(co2) FROM comp;
LENGTHB(CO1) LENGTHB(CO2)
------------ ------------
4 2
4 4
SQL> SELECT * FROM comp;
CO1 CO2
-------- --------
AA AA
AAAA AAAA
SQL> SELECT * FROM comp WHERE co1='AA';
CO1 CO2
-------- --------
AA AA
SQL> SELECT * FROM comp WHERE co2='AA';
CO1 CO2
-------- --------
AA AA
SQL> SELECT * FROM comp WHERE co1=co2;
CO1 CO2
-------- --------
AAAA AAAA
예제 4. 다음 실습을 통해 DATE 타입에 대해서 이해해 보자
CREATE TABLE hd (
no NUMBER,
hdate DATE
);
테이블이 생성되었습니다.
SQL> INSERT INTO hd VALUES (1, sysdate);
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM hd;
NO HDATE
---------- ----------
1 2021/11/19
SQL> SELECT * FROM hd WHERE hdate = '2021/11/19';
선택된 레코드가 없습니다.
SQL> SELECT no, TO_CHAR(hdate,'YYYY/MM/DD:HH24:MI:SS') FROM hd;
NO TO_CHAR(HDATE,'YYYY/MM/DD:HH24:MI:SS')
---------- --------------------------------------
1 2021/11/19:13:21:42
SQL> SELECT * FROM hd
2 WHERE hdate BETWEEN '2021/11/19' AND '2021/11/20';
NO HDATE
---------- ----------
1 2021/11/19
SQL> SELECT * FROM hd
2 WHERE TRUNC(hdate) = '2021/11/19';
NO HDATE
---------- ----------
1 2021/11/19
SQL> SELECT * FROM hd
2 WHERE hdate like '2021/11/19%'; // 답은 나오지만 사용하지 말기
NO HDATE
---------- ----------
1 2021/11/19
테이블 생성시 아래와 같이 만든다.
예제 5. 다음에 제시된 테이블을 생성한다. 테이블명이나 컬럼의 이름, 컬럼의 길이는 임의로 정한다.
- 테이블명 : 고객
- 컬럼 : 고객관리번호, 고객명, 주소, 전화
* C:\Users\st04\sql\client.sql 파일 생성
client.sql
DROP TABLE client;
PURGE RECYCLEBIN;
CREATE TABLE client(
cnum NUMBER,
cname VARCHAR2(50),
addr VARCHAR2(200),
tel VARCHAR2(20)
);
@C:\Users\st04\sql\client
SQL> @client
DROP TABLE client
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
휴지통이 지워졌습니다.
테이블이 생성되었습니다.
실습
1. 예제를 통해 만들어진 테이블을 확인하고 모두 삭제한다.
SQL> DESC client;
이름 널? 유형
-------------------------- -------- -------------------------
CNUM NUMBER
CNAME VARCHAR2(50)
ADDR VARCHAR2(200)
TEL VARCHAR2(20)
SQL> DROP TABLE client;
테이블이 삭제되었습니다.
SQL> DESC client;
ERROR:
ORA-04043: client 객체는 존재하지 않습니다.
2. 다음 구조와 같은 테이블을 생성하는 스크립트를 작성하고 실행해본다.
- 테이블명이나 컬럼명, 데이터 타입 등은 입력될 데이터의 성격에 따라 임의로 정할 수 있다.
dept.sql
DROP TABLE dept;
PURGE RECYCLEBIN;
CREATE TABLE dept (
dno VARCHAR2(2),
dname VARCHAR2(15),
loc VARCHAR2(9)
);
SQL> @dept
테이블이 삭제되었습니다.
휴지통이 지워졌습니다.
테이블이 생성되었습니다.
SQL> SELECT table_name, column_name, data_type, data_length
2 FROM user_tab_columns
3 WHERE table_name='DEPT';
TABLE_N COLUMN_ DATA_TYPE DATA_LENGTH
------- ------- ---------- -----------
DEPT DNO VARCHAR2 2
DEPT DNAME VARCHAR2 15
DEPT LOC VARCHAR2 9emp.sql
DROP TABLE emp;
PURGE RECYCLEBIN;
CREATE TABLE emp (
eno VARCHAR2(4),
ename VARCHAR2(15),
sex VARCHAR2(4),
job VARCHAR2(12),
mgr VARCHAR2(4),
hdate DATE,
sal NUMBER,
comm NUMBER,
dno VARCHAR2(2)
);
SQL> @emp
테이블이 삭제되었습니다.
휴지통이 지워졌습니다.
테이블이 생성되었습니다.
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name='emp'; //대문자로 작성 필수!!
선택된 레코드가 없습니다.
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name='EMP';
TABLE_N COLUMN_ DATA_TYPE DATA_LENGTH
------- ------- ---------- -----------
EMP ENO VARCHAR2 4
EMP ENAME VARCHAR2 15
EMP SEX VARCHAR2 4
EMP JOB VARCHAR2 12
EMP MGR VARCHAR2 4
EMP HDATE DATE 7
EMP SAL NUMBER 22
EMP COMM NUMBER 22
EMP DNO VARCHAR2 2salgrade.sql
DROP TABLE salgrade;
PURGE RECYCLEBIN;
CREATE TABLE salgrade (
salgrade NUMBER,
losal NUMBER,
hisal NUMBER
);
SQL> @salgrade
테이블이 삭제되었습니다.
휴지통이 지워졌습니다.
테이블이 생성되었습니다.
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name='DEPT';
TABLE_N COLUMN_ DATA_TYPE DATA_LENGTH
------- ------- ---------- -----------
DEPT DNO VARCHAR2 2
DEPT DNAME VARCHAR2 15
DEPT LOC VARCHAR2 9
3. 다음 표에 제시한 테이블을 생성한다.
student.sql
DROP TABLE student;
PURGE RECYCLEBIN;
CREATE TABLE student (
sno VARCHAR2(10),
sname VARCHAR2(15),
sex VARCHAR2(4),
syear NUMBER,
major VARCHAR2(15),
avr NUMBER
);
SQL> @student
테이블이 삭제되었습니다.
휴지통이 지워졌습니다.
테이블이 생성되었습니다.
SQL> SELECT table_name, column_name, data_type, data_length
2 FROM user_tab_columns
3 WHERE table_name='STUDENT';
TABLE_N COLUMN_ DATA_TYPE DATA_LENGTH
------- ------- ---------- -----------
STUDENT SNO VARCHAR2 10
STUDENT SNAME VARCHAR2 15
STUDENT SEX VARCHAR2 4
STUDENT SYEAR NUMBER 22
STUDENT MAJOR VARCHAR2 15
STUDENT AVR NUMBER 22professor.sql
DROP TABLE professor;
PURGE RECYCLEBIN;
CREATE TABLE professor (
pno VARCHAR2(10),
pname VARCHAR2(15),
section VARCHAR2(15),
orders VARCHAR2(15),
hiredate DATE
);
SQL> @professor
테이블이 삭제되었습니다.
휴지통이 지워졌습니다.
테이블이 생성되었습니다.
SQL> SELECT table_name, column_name, data_type, data_length
2 FROM user_tab_columns
3 WHERE table_name='PROFESSOR';
TABLE_N COLUMN_ DATA_TYPE DATA_LENGTH
------- ------- ---------- -----------
PROFESS PNO VARCHAR2 10
OR
PROFESS PNAME VARCHAR2 15
OR
PROFESS SECTION VARCHAR2 15
OR
PROFESS ORDERS VARCHAR2 15
OR
PROFESS HIREDAT DATE 7
OR Ecourse.sql
DROP TABLE course;
PURGE RECYCLEBIN;
CREATE TABLE course (
cno VARCHAR2(10),
cname VARCHAR2(15),
st_num NUMBER,
pno VARCHAR2(10)
);
SQL> @course
테이블이 삭제되었습니다.
휴지통이 지워졌습니다.
테이블이 생성되었습니다.
SQL> SELECT table_name, column_name, data_type, data_length
2 FROM user_tab_columns
3 WHERE table_name='COURSE';
TABLE_N COLUMN_ DATA_TYPE DATA_LENGTH
------- ------- ---------- -----------
COURSE CNO VARCHAR2 10
COURSE CNAME VARCHAR2 15
COURSE ST_NUM NUMBER 22
COURSE PNO VARCHAR2 10score.sql
DROP TABLE score;
PURGE RECYCLEBIN;
CREATE TABLE score (
sno VARCHAR2(10),
cno VARCHAR2(10),
result NUMBER
);
SQL> @score
테이블이 삭제되었습니다.
휴지통이 지워졌습니다.
테이블이 생성되었습니다.
SQL> SELECT table_name, column_name, data_type, data_length
2 FROM user_tab_columns
3 WHERE table_name='SCORE';
TABLE_N COLUMN_ DATA_TYPE DATA_LENGTH
------- ------- ---------- -----------
SCORE SNO VARCHAR2 10
SCORE CNO VARCHAR2 10
SCORE RESULT NUMBER 22
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name='emp';
선택된 레코드가 없습니다.
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name='EMP';
TABLE_N COLUMN_ DATA_TYPE DATA_LENGTH
------- ------- ---------- -----------
EMP ENO VARCHAR2 4
EMP ENAME VARCHAR2 15
EMP SEX VARCHAR2 4
EMP JOB VARCHAR2 12
EMP MGR VARCHAR2 4
EMP HDATE DATE 7
EMP SAL NUMBER 22
EMP COMM NUMBER 22
EMP DNO VARCHAR2 2
'SQL 공부 기록' 카테고리의 다른 글
| SQL 19 - 트랜잭션(Transaction)과 잠금(Lock)의 이해 (0) | 2021.11.19 |
|---|---|
| SQL 18 - DML(INSERT, UPDATE, DELETE) 문의 이해 (0) | 2021.11.19 |
| (수정중) SQL 17 - 그룹 함수와 HAVING (0) | 2021.11.18 |
| SQL 16 - 그룹 함수와 GROUP BY (0) | 2021.11.18 |
| DB 2 - 개체, 관계, 속성, 식별자 (1) | 2021.11.17 |