-- 이글에서 언급하지 않은 UK, PK 부여하는 방법
-- 가 주석으로 처리되는 편집기에서 붙여넣기 해서 찾아 써도 됨
---DDL---------------------------------
--데이터 정의 언어---------------------
---------------------------------------
-- create, alter, drop
--개발자 입장에서 table 중심으로---
--본인 계정이 가지고 있는 모든 테이블의 정보 보기
SELECT * FROM user_tables;
--테이블의 정보를 볼 수 있는 시스템 테이블 조회하기
SELECT table_name, tablespace_name FROM user_tables;
--table 만들기
CREATE TABLE newTable(
memberid number(10),
NAME VARCHAR2(20),
addr NVARCHAR2(30),
reg_date DATE DEFAULT SYSDATE,--sysdate가 가지는 날자 형식
jumin CHAR(6)
);
SELECT * FROM newTable;
INSERT INTO newTable(memberid, NAME, addr,jumin)
VALUES(1,'김유신','서울시 강남구','123456');
COMMIT;
-- 날자 포멧을 강제 하기
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
--강제한 포멧으로 내용 인서트 하기(날자포멧이 안맞으면 입력시 에러 날수 있음)
INSERT INTO newTable(memberid, NAME, addr,reg_date,jumin)
VALUES(1,'김유신','서울시 강남구','2012-8-9','123456');
--테이블 생성 추가 옵션 --------------------------------------
--테이블 구조(schema)와 데이터 까지 복사
CREATE TABLE copyTable
AS SELECT * FROM newTable;
select * FROM copyTable;
CREATE TABLE copyEmp
AS
SELECT * FROM EMP WHERE empno=7788;
select * FROM copyemp;
CREATE TABLE copyEmp2
AS
SELECT empno, ename, sal FROM EMP;
SELECT * FROM copyemp2
--데이터는 복사하지 않고 구조만 복사하는 방법
--where 절에 결과가 안들어오게 하면 되는데
--관용적으로 where 1=2 를 많이 쓴다고 함
CREATE TABLE copyemp3
AS
SELECT * FROM EMP WHERE 1=2;
SELECT * FROM copyemp3;
------------------------------------------------------------------------------------
------11g에 추가된 내용(가상 컬럼) or (조합 컬럼), 슈드 컬럼 (11g 이상에서만 지원됨)
------ 가상으로 컬럼 내용을 채워주는 기능
------------------------------------------------------------------------------------
CREATE TABLE vtable
(
no1 NUMBER,
no2 number,
no3 NUMBER GENERATED always AS (no1 + no2) virtual
);
--1. insert no1,no2값으로 no3값을 채워넣어줌
INSERT INTO vtable(no1, no2)
VALUES(100,200);
COMMIT;
SELECT * FROM vtable;
--단 가상커럼에 직접 데이터를 넣을수 없음 아래와 다음 문장을 실행 안됨
INSERT INTO vtable(no1, no2,no3)
VALUES(100,200,500);
UPDATE vtable SET no1 =10;
COMMIT;
--테이블이 이미 생성된 상황에서 가상 컬럼을 추가할 경우
--조합컬럼은 조합컬럼을 참조해서는 안된다.
ALTER TABLE vTable
ADD (no4 NUMBER GENERATED always AS ((no1*12)+no2));
SELECT * FROM user_tab_columns WHERE table_name='VTABLE';
--조건절을 이용한 가상컬럼 생성하기
CREATE TABLE vtable2(
NO NUMBER,
vdate CHAR(8),
month NUMBER(1)
GENERATED always AS (
CASE
WHEN SUBSTR(vdate,5,2) IN ('01','02','03') THEN 1
WHEN SUBSTR(vdate,5,2) IN ('04','05','06') THEN 2
WHEN SUBSTR(vdate,5,2) IN ('07','08','09') THEN 3
ELSE 4
end)virtual);
INSERT INTO vtable2(NO, vdate) VALUES(1,'20130101');
INSERT INTO vtable2(NO, vdate) VALUES(1,'20130501');
INSERT INTO vtable2(NO, vdate) VALUES(1,'20131212');
COMMIT;
SELECT * FROM vtable2
--가상 컬럼 작성하는 방법은 11g 이상에서 지원됨
------------------------------------------------------------------------------------
--필수사항
--1. Table 생성
CREATE TABLE NewTable3(
id number);
--2. Table 생성후 추가적인 컬럼 생성
ALTER TABLE NewTable3
ADD NAME VARCHAR2(20)
--3. 기존 컬럼에 대한 이름 변환
ALTER TABLE NewTable3
RENAME COLUMN NAME TO ename;
--4. 기존 컬럼에 대한 스키마(구조) 변환
ALTER TABLE NEWTable3
modify (ename VARCHAR2(50));
SELECT * FROM newTable3
--5. 기존 컬럼 삭제하기
ALTER TABLE newTable3
DROP column id;
------------------------------------------------------------------------------------
--table 삭제
--범위에 따른 구분
--DELETE < TRUNCATE < DROP
--1. DELETE : (table 유지, date 삭제 데이터가 가지고 있는
메모리 공간은 oracle 내부적으로 남아있음)
--2. TRUNCATE : (table 유지, data 삭제 및 데이터가 가지고 있는 공간도 삭제
, 최초 table 생성 시점으로 이동)
--truncate의 단점 : 조건을 가지고 데이터를 삭제하지 못함
--3. DRPO : (table 과 데이터 모두 삭제)
------------------------------------------------------------------------------------
-- partition 개념 이해
------------------------------------------------------------------------------------
--개발자가 접근 하기에는 난해하다(보통 DBA가 처리하는 사항)
--개발자가 알고 있다면 plus한 사항
--대용량 데이터(1억건 이상)에서 사용하는 방법
--파티션 테이블 (ADMIN)
--기본상식
--데이터 조회 나 수정시 물리적인 데이터저장소에 접근하여 데이터를 복사한 후
--메모리에 올려 놓게 작업(그 메모리 공간을 Buffer)라 합니다.
--오라클에서 사용되는 Buffer => DataBase Buffer Cache
--오라클은 DataBase Buffer Cache 라는 메모리 공간을 TableSpace라는 논리적
--공간으로 나누어 사용
--EX)
--물건이 전시되어 있는 공간 (TABLESPACE)
--물건창고 (데이터 파일)
--손님이 찾는 물건 : 테이블...
--그런데 물건창고에 물건이 적은 경우는 문제가 되지 않지만
--물건창고에 물건이 엄청나게 많은 경우에는?
--물건을 정리(나름의 기준을 가지고) --물리적인 작업
--ORACLE 에서 파티션 partition 확인하는 방법
--SYSTEM 계정으로 접속 하고나서 다음 명령 실행
SELECT * FROM DBA_DATA_FILES;
SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER ='HR';
SELECT * FROM USER_TABLES;
--물리적인 데이터 저장 공간
--C:\APP\KOSTA\ORADATA\ORCL\USERS01.DBF
--판매용 테이블
--범위를 기준으로 한 Range partition(범위 파티션)
--데이터를 날자를 기준으로 물리적인 공간에 배치하는 작업
--작업순서
--1. 물리적인 데이터 공간 확인 작업
---- SYSTEM 계저응로 접근하여 조회 확인 SELECT * FROM DBA_DATA_FILES;
-------C:\APP\KOSTA\ORADATA\ORCL\USERS01.DBF
---- SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER ='HR';
-------없음
--2. 분할 기준을 가지고 저장용 테이블 스페이스 확보
-- 별도의 공간을 만드는 작업 (System 계정에서 실행)
CREATE TABLESPACE ts_q1
DATAFILE 'C:\APP\KOSTA\ORADATA\ORCL\ts_q101.DBF' SIZE 2M;
-- 여기도 system 계정에서 지정
CREATE TABLE panmae (
pdate VARCHAR2(8),
pcode NUMBER(3),
pqty NUMBER(5))
PARTITION BY RANGE(pdate) --무엇을 기준으로 정렬(파티션 할 것인가?)
(PARTITION q1 VALUES less than ('20100401') TABLESPACE ts_q1,
PARTITION q2 VALUES less than ('20100701') TABLESPACE ts_q2,
PARTITION q3 VALUES less than ('20101001') TABLESPACE ts_q3,
PARTITION q4 VALUES less than ('20110101') TABLESPACE ts_q4,
PARTITION q5 VALUES less than (MAXVALUE) TABLESPACE ts_q5
);
SELECT * FROM panmae;
INSERT INTO panmae VALUES ('20100404',100,50);
INSERT INTO panmae VALUES ('20100929',200,30);
COMMIT;
SELECT * FROM panmae PARTITION(q2);
SELECT * FROM panmae PARTITION(q3);
----------------------------------------------
--partition end-------------------------------
----------------------------------------------
--제약 조건 (Table 생성시 데이터의 무결정(intergrity) 보장)
--전화번호
--주민번호 : primary key(무조건 있고(null 미허용), 중복이 안되야 됨)
--이름
--핸드폰번호
--제약의 종류
--1. not null : null 값을 넣지 못하게 하는 제약
--2. unique : 중복 데이터를 넣지 못하게 하는 제약
--3. primary key : not null + unique(중복, null 허락안함)
--4. foreign key : 관계를 설정(관계제약) 참조 무결성 확보
--5. check : 조건을 생성해서 조건에 맞는 데이터만 입력, 수정하게 하는 제약
--6.참고 : 제약 정보를 확인하는 방법 : SELECT * FROM user_constraints where table_name='EMP';
--1
CREATE TABLE C_emp(
id NUMBER CONSTRAINT C_emp_id_pk PRIMARY KEY ,
--제약을 거는 방법중에서 full name 작성함
--제약의 이름은 (관용적인 표기) 테이블 이름_컬럼명_제약의 약어 : 제약의 이름만 봐도 알수 있도록
NAME VARCHAR(20) NOT NULL,
addr VARCHAR2(50)
);
SELECT * FROM user_constraints WHERE table_name =UPPER('c_emp');
--C_emp 테이블 test(제약)
--정상적인 데이터를 넣어봄
INSERT INTO C_emp(id, name, addr)
VALUES(100,'홍길동','서울시 강남구');
INSERT INTO C_emp(id, name)
VALUES(200,'김유신');
COMMIT;
SELECT * FROM C_emp;
--문제 발생
--INSERT INTO C_emp(id, NAME, addr) VALUES(200,'김만중','한양');
--id 컬럼에 중복 데이터 삽입
--INSERT INTO C_emp(NAME, addr) VALUES('김만충','벌레');
--ID 컬럼에 NULL 값 삽입
--INSERT INTO E_EMP(ID,ADDR) VALUES (300,'서울시 강남구');
--NAME 칼럼에 NOT NULL 제약
CREATE TABLE C_emp2 (
ID NUMBER PRIMARY KEY,
--Contraint C_emp2_id_PK primary key 이지만 윗줄처럼 줄여 쓸수 있음
ENAME VARCHAR2(20) NOT NULL,
jumin CHAR(6) CONSTRAINT C_emp2_jumin_uk UNIQUE
);
SELECT * FROM user_constraints WHERE table_name = UPPER('C_emp2');
INSERT INTO C_emp2(id,ename,jumin)
VALUES (100,'아무개','123456');
SELECT * FROM c_emp2;
--INSERT INTO C_emp2(id, ename, jumin) VALUES (200,'이이','123456');
--주민번호 컬럼에 중복 데이터 insert : QNIQUE
--quiz
INSERT INTO C_emp2(id,ename, jumin) VALUES(200,'누구',null);
--Unique 되는데 해당 컬럼에 null 값 허용
INSERT INTO C_emp2(id,ename, jumin) VALUES(300,'슈퍼맨',null);
--Unique에 null 두번재 집어넣으려고 하면 됨!!!
--위 작업은 Oracle은 되나 MS-SQL에서는 안됨 : null도 중복 체크를 함
--Check
CREATE table C_emp3(
id NUMBER PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
age NUMBER CONSTRAINT C_emp3_age_ck CHECK(age>19));
INSERT INTO C_emp3(id, NAME, age) VALUES(100,'김좌진', 20);
COMMIT;
--INSERT INTO C_emp3(id, NAME, age) VALUES (200,'철이',18);
--check age 컬럼에 19이상만 입력되도록 check 조건을 걸어두었기 때문에 실행되지 않음
--foreign key : 참조제약, 관계제약
CREATE TABLE C_emp4
AS SELECT * FROM EMP WHERE 1=2;
CREATE TABLE C_dept
AS
SELECT * FROM dept WHERE 1=2;
COMMIT;
SELECT * FROM C_emp4;
SELECT * from user_constraints WHERE table_name = 'C_EMP4';
SELECT * FROM C_dept;
SELECT * from user_constraints WHERE table_name = 'C_DEPT';
--empt table 에있는 deptno(부서번호) 데이터는
--dept table에 있는 deptno의 데이터중에 하나이어야만 한다
--두 테이블간에 제약 조건을 성립 => foreign key
--원칙적으로 테이블 생성시 제약을 만들지만
--추후(테이블 이후)에 제약을 테이블에 추가할 수 있다.
ALTER TABLE C_emp4
ADD CONSTRAINT C_emp4_deptno_fk FOREIGN KEY (deptno)
REFERENCES c_dept(deptno);
--위 처럼 처리되기 위해서는 dept 의 deptno는 최소한 unique 혹은 primary key
-- 설정이 되어있어야 참조당할수 있다.
--만약을 위해 제약을 날리는 방법
ALTER TABLE C_emp4
DROP CONSTRAINT sys_c00111147;
------------------------제약 날리는 방법
ALTER TABLE C_dept ADD CONSTRAINT C_dept_deptno_pk PRIMARY KEY(deptno);
------------------이걸 create 구문에 넣으려면 다른 제약조건과 달리
-- , 를 해줘야함
SELECT * FROM user_constraints WHERE table_name ='C_DEPT';
SELECT * FROM user_constraints WHERE table_name ='C_EMP4';
SELECT * FROM c_emp4
SELECT * FROM c_dept
INSERT INTO c_dept(deptno, dname, loc) VALUES(10,'인사팀','서울');
INSERT INTO c_dept(deptno, dname, loc) VALUES(20,'회계팀','서울');
INSERT INTO c_dept(deptno, dname, loc) VALUES(30,'IT팀','서울');
COMMIT;
INSERT INTO c_emp4(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(1000,'아마게','it',2000,'2013-10-09',500,10,10);
INSERT INTO c_emp4(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(2000,'누구게','it',2000,'2013-10-09',500,10,40);
--제약 에러 발생 : deptno 가 c_dept 테이블의 deptno가 40번을 가지고 있지 않아서임
INSERT INTO c_emp4(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(2000,'누구게','it',2000,'2013-10-09',500,10,null);
--null은 됨? --> 첨 왔을때 부서가 없을수 있다는 가능성은 열어둠
-- 이걸 허용하지 않으려면 deptno에 not null 제약을 주면 됨
SELECT * FROM c_emp4
-------------------------------------------
--학생 성적테이블
-- 학번은 중복되거나 null을 허용하지 않음
-- 이름은 null을 허용하지 않음
-- 총점과 평균은 가상컬럼으로 구현
-- 학과 코드는 학과 테이블에 있는 학과 코드를 참조함
-- type은 임의 부여 가능
--학번, 이름, 국어, 수학, 총점, 평균, 학과코드
SELECT * FROM studentTable;
CREATE TABLE studentTable (
studentNumber NUMBER(8) CONSTRAINT studenTable_number_pk PRIMARY KEY,
studentName VARCHAR2(12)CONSTRAINT studentTable_Name_Null NOT null,
kor number DEFAULT 0,
eng NUMBER DEFAULT 0,
math NUMBER DEFAULT 0,
STsum NUMBER GENERATED always AS (kor+eng+math) virtual,
STavg NUMBER GENERATED always AS ((kor+eng+math)/3) virtual,
study_code VARCHAR2(4), CONSTRAINT studentTable_studycode_fk FOREIGN KEY (study_code) REFERENCES study_table(study_code)
);
DROP TABLE studentTable;
COMMIT;
ALTER TABLE studentTable ADD CONSTRAINT studentTable_studycode_fk FOREIGN KEY (study_code)
REFERENCES study_table(study_code);
INSERT INTO studentTable(studentnumber,studentname, kor,eng,math,study_code)
VALUES(10000000,'삼강아잇',70,60,50,1000);
INSERT INTO studentTable(studentnumber,studentname, kor,eng,math,study_code)
VALUES(10000001,'셀트리온',86,65,89,2000);
INSERT INTO studentTable(studentnumber,studentname, kor,eng,math,study_code)
VALUES(10000002,'동양강철',50,74,94,3000);
INSERT INTO studentTable(studentnumber,studentname, kor,math,study_code)
VALUES(10000003,'샴숑',50,94,3000);
COMMIT;
--학과 테이블
-- 학과 코드는 중복, null 허용하지 않음
--학과 코드, 학과명
CREATE TABLE study_table(
study_code VARCHAR2(4) CONSTRAINT studyTable_studycode_pk PRIMARY KEY,
study_name VARCHAR2(20)
);
SELECT *FROM study_table;
INSERT INTO study_table(study_code, study_name)
VALUES(1000,'코끼리수염연구');
INSERT INTO study_table(study_code, study_name)
VALUES(2000,'파리발가락연구');
INSERT INTO study_table(study_code, study_name)
VALUES(3000,'피부생물학');
COMMIT;
SELECT s.STUDENTNUMBER, s.STUDENTNAME, s.KOR, s.ENG, s.MATH, s.STSUM, ROUND(s.STAVG,2),s.STUDENTNAME FROM studentTable s, study_table t WHERE s.STUDY_CODE = t.STUDY_CODE
----------------------------------------------
--VIEW----------------------------------------
--VIEW 란 가상 테이블 (SUBQUERY : IN LINE VIEW :FROM 절 뒤에 서버쿼리 사용가능)
--VIEW 는 실제 물리적인 테이블이 아니고 SQL 문장이 들어있는 객체
--그래서 마치 TABLE 처럼 사용 할 수 있음
--그래서 뷰를 사용하는 사용자는 실제 사용하는 것이 뷰인지 테이블인지 구별할 수 없다.
--형식 create or replace view 뷰 명칭 as (원하는 셀렉트 구문)
--종류
----단순뷰 : 테이블 하나로 생성, DML 100% 지원
CREATE OR REPLACE VIEW vemp
AS SELECT empno, ename, job, sal FROM EMP;
SELECT *FROM vemp;
----복합뷰 : 테이블 하나이상 으로 생성, DML 사용 말것을 권장함
CREATE OR REPLACE VIEW vemp2
AS
SELECT e.empno, e.ename, e.sal, d.deptno, d.dname FROM EMP e inner join dept d ON e.deptno = d.deptno
--뷰 확인방법
DESC user_views
SELECT * FROM user_views
--key : view 를통한 insert, update, delete 작업이 가능합니다.
--단 !! view 가 볼수 있는 자원에 대해서만 가능함
--단순뷰의 경우 DML 100% 지원함
----단순뷰에 대한 DML 작업
CREATE TABLE VTEST AS SELECT * FROM EMP;
SELECT * FROM vtest;
COMMIT;
CREATE OR REPLACE VIEW vt AS
SELECT empno, ename, sal FROM vtest;
SELECT *FROM vt;
--view 를 통한 insert
INSERT INTO VT(empno, ename, sal) VALUES(3000, '홍길동', '10');
DELETE FROM VT WHERE empno =7369;
--이것도 가능함, 볼수있는걸 통해서 나머지도 지워버림
SELECT * FROM vtest;
--vt가 볼수 없는 vtest 자원인 경우 vt를 통해서 입력할수는 없다.
INSERT INTO VT(job) VALUES('clack');
--복합뷰 : 테이블 하나이상으로 생성된 복합뷰는 DML을 쓰지 말라고 권장하고 있음
댓글 없음:
댓글 쓰기