ORACLESQL테이블변경
--(참고) 테이블 설계(구조)를 변경하기 : 컬럼명, 데이터형
-- 실습 테이블 : DEPT20
-- DEPARTMENT 복사
CREATE TABLE DEPT20
AS
SELECT * FROM DEPARTMENT
WHERE 1=2;
--예제 1) 부서 테이블에(DEPT20) 날짜 자료형을(DATE) 가지는 BIRTH 컬럼 추가하기
-- 사용법) ALTER TABLE 테이블명 ADD(컬럼명 자료형);
ALTER TABLE DEPT20
ADD(BIRTH DATE);
--구조 보기
DESC DEPT20;
-- 예제 2) 부서테이블에(DEPT20) 부서명(NAME)의 크기를 변경
-- 사용법) ALTER TABLE 테이블명 MODIFY 컬럼명 자료형(자리수)
-- 예제 3) 부서테이블(DEPT20) BIRTH 컬럼 삭제하기
-- 사용법) ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
ALTER TABLE DEPT20 DROP COLUMN BIRTH;
-- 구조 보기
DESC DEPT20;
-- 예제 4) 테이블 명 변경 : DEPT20 -> DEPT30
-- 사용법) RENAME 원본테이블명 TO 바꿀테이블명;
ORACLESQL테이블 제약조건
-- 제약조건은 컬럼별로 각각 지정할 수 있음
-- 1) UNIQUE(유일한) 제약조건 : 유일한 값만 추가될 수 있음(중복 금지), NULL 은 허용
-- 2) NOT NULL 제약조건(*) : 입력될 값으로 NULL 은 허용 않함
-- 사용법 :
-- CREATE TABLE 테이블명 (
-- 컬럼명 자료형(자리수) 제약조건,
-- ...
-- );
-- 예제 1) CUSTOMER 테이블을 정의하고 ID 컬럼에 UNIQUE 제약조건을 지정하세요
-- 그리고, PWD, NAME 에는 NOT NULL 제약조건을 추가하세요
-- (ID VARCHAR2(20) -> UNIQUE 제약조건 지정
-- PWD VARCHAR2(20)
-- NAME VARCHAR2(20)
-- PHONE VARCHAR2(30)
-- ADDRESS VARCHAR2(1000))
CREATE TABLE CUSTOMER (
ID VARCHAR2(20) UNIQUE,
PWD VARCHAR2(20) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
PHONE VARCHAR2(30),
ADDRESS VARCHAR2(1000)
);
-- 간단연습 : CUSTOMER2 테이블 만들때 PHONE, ADDRESS 에
-- NOT NULL 제약조건을 추가하세요
-- (ID VARCHAR2(20)
-- PWD VARCHAR2(20)
-- NAME VARCHAR2(20)
-- PHONE VARCHAR2(30)
-- ADDRESS VARCHAR2(1000))
CREATE TABLE CUSTOMER2 (
ID VARCHAR2(20),
PWD VARCHAR2(20),
NAME VARCHAR2(20),
PHONE VARCHAR2(30)NOT NULL,
ADDRESS VARCHAR2(1000)NOT NULL
);
-- 3) PRIMARY KEY : 기본키 제약조건, (약어: PK)
-- 특징 : 1) UNIQUE(유일, 중복금지) + 2) NOT NULL(NULL금지)
-- 2) INDEX (조회속도 향상) 도 자동 생성
-- 실무) 주로 테이블 설계시 PK 1개는 거의 생성함
-- 예제 2) CUSTOMER3 테이블을 정의하고 ID 컬럼에 PK 제약조건을 지정하세요
-- (ID VARCHAR2(20)
-- PWD VARCHAR2(20)
-- NAME VARCHAR2(20)
-- PHONE VARCHAR2(30)
-- ADDRESS VARCHAR2(1000))
--CONSTRAINT 제약조건이름 PRIMARY KEY(컬럼명)
CREATE TABLE CUSTOMER3 (
ID VARCHAR2(20),
PWD VARCHAR2(20),
NAME VARCHAR2(20),
PHONE VARCHAR2(30),
ADDRESS VARCHAR2(1000),
CONSTRAINT PK_CUSTOMER3_ID PRIMARY KEY(ID)
);
--간단연습 : CUSTOMER4 테이블을 정의하고 ID컬럼에 PK제약조건을 지정하세요
--제약조건이름(중복금지) : PK_CUSTOMER4_ID
CREATE TABLE CUSTOMER4 (
ID VARCHAR2(20),
PWD VARCHAR2(20),
NAME VARCHAR2(20),
PHONE VARCHAR2(30),
ADDRESS VARCHAR2(1000),
CONSTRAINT PK_CUSTOMER4_ID PRIMARY KEY(ID)
);
-- PK 테스트 : 1) UNIQUE(유일) 2) NOT NULL
-- CUSTOMER3 : INSERT (ID) 컬럼에 NULL 값을 넣기
INSERT INTO CUSTOMER3(ID, PWD, NAME,PHONE,ADDRESS)
VALUES(NULL,NULL,NULL, '010-123-4567', '부산시');
--PK 2: PK만드는 2번째 방법
CREATE TABLE CUSTOMER5 (
ID VARCHAR2(20)CONSTRAINT PK_CUSTOMER5_ID PRIMARY KEY,
PWD VARCHAR2(20),
NAME VARCHAR2(20),
PHONE VARCHAR2(30),
ADDRESS VARCHAR2(1000),
);
--4) FOREIGN KEY(*) : 외래키(참조키) 제약조건, (약어:FK)
--참조키(외래키) : 부서테이블 (10~40: DNO(부서번호))
-- 사원테이블(자식테이블, DNO(10~40) 까지만 INSERT 할수 있게하는 것)
-- 관계 : 1) 회사에서는 사원들은 무조건 1개의 부서에 소속됨
-- 2) 회사에 있는 부서만 소속될 수 있음
-- 특징 : 1) 부모테이블의 컬럼의 데이터만 자식테이블의 데이터로 입력될 수 있게 제약조건을 거는 것이 참조키
-- 2) 그 때 부모테이블의 컬럼은 반드시 유일한 값이어야함(기본키(PK), UNIQUE)
-- 3) 데이터 삭제 시 순서가 중요함 : 자식테이블을 먼저 삭제하고, 부모테이블을 삭제해야함
--(실무) : 1) 반은 사용하고(데이터 중요), 반은 사용안함(성능 저하,테스트시 어려움)
-- 예제 3) EMP_SECOND 테이블을 만들고,
-- ENO NUMBER(4) (기본키 #2)
-- ENAME VARCHAR2(10),
-- JOB VARCHAR2(9)
-- DNO NUMBER(2) (참조키 : 부모(DEPARTMENT)) 지정하세요
-- CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼명) REFERENCES 부모테이블
CREATE TABLE EMP_SECOND (
ENO NUMBER(4) CONSTRAINT PK_EMP_SECOND_ENO PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DNO NUMBER(2),
CONSTRAINT FK_EMP_SECOND_DNO FOREIGN KEY(DNO) REFERENCES DEPARTMENT
);
--간단연습 : EMP_SECOND2 부모테이블은 DEPARTMENT 이고
-- DNO NUMBER(2) (참조키 : 부모(DEPARTMENT)) 지정하세요
-- 단 FK 이름은 : FK_EMP_SECOND2_DNO
CREATE TABLE EMP_SECOND2 (
ENO NUMBER(4) CONSTRAINT PK_EMP_SECOND2_ENO PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DNO NUMBER(2),
CONSTRAINT FK_EMP_SECOND2_DNO FOREIGN KEY(DNO) REFERENCES DEPARTMENT
);
-- FK 2: 2번째 생성방법
CREATE TABLE EMP_SECOND4 (
ENO NUMBER(4) CONSTRAINT PK_EMP_SECOND4_ENO PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DNO NUMBER(2) CONSTRAINT FK_EMP_SECOND4_DNO FOREIGN KEY(DNO) REFERENCES DEPARTMENT
);
--5) DEFAULT(기본) 제약조건 :
-- 정의 : INSERT 할때 NULL 값이 추가되면 대체해서 다른값으로 생성하는 제약조건
-- 예제) EMP_SECOND3 SALARY 칼럼에 DEFAULT(기본) 제약조건 추가하세요 (NULL -> 1000)
-- ENO NUMBER(4) (기본키 #2)
-- SALARY NUMBER(7,2) 디폴트 (1000)
CREATE TABLE EMP_SECOND3 (
ENO NUMBER(4),
SALARY NUMBER(7,2) DEFAULT 1000
);
--- INSERT 테스트
INSERT INTO EMP_SECOND3 (ENO)
VALUES(8000);
COMMIT;
--6) CHECK 제약 조건 : 컬럼에 조건식을 지정가능
-- -- 예제) EMP_SECOND6 SALARY 칼럼에 CHECK 제약조건 추가하세요 (SALARY >0)
-- ENO NUMBER(4) (기본키 #2)
-- SALARY NUMBER(7,2) CHECK (SALARY>0)
CREATE TABLE EMP_SECOND6 (
ENO NUMBER(4),
SALARY NUMBER(7,2) CONSTRAINT CK_EMP_SECOND6_SALARY CHECK (SALARY>0)
);
--INSERT
INSERT INTO EMP_SECOND6(ENO, SALARY)
VALUES(8000, -200);
--참고 PK / FK 를 테이블 생성후에 지정하기
-- 실습 : DEPARTMENT 테이블 복사 : DEPT_COPY
DROP TABLE DEPT_COPY;
CREATE TABLE DEPT_COPY
AS
SELECT * FROM DEPARTMENT;
DROP TABLE EMP_COPY;
CREATE TABLE EMP_COPY
AS
SELECT * FROM EMPLOYEE;
-- 테이블 복사 후에는 제약조건이 복사되지 않음.
-- 그래서 제약조건만 따로 추가해보세요
-- 예제1) 사원복사본 테이블에(EMP_COPY) 기본키(PRIMARY KEY) 추가하기
-- 사용법) ALTER TABLE 테이블명
-- ADD CONSTRAINT 제약조건명 PRIMARY KEY(컬럼)
ALTER TABLE EMP_COPY
ADD CONSTRAINT PK_EMP_COPY_ENO PRIMARY KEY(ENO);
-- 간단연습 : 부서복사본(DEPT_COPY) 테이블에 기본키(PK) 추가하기
-- 기본키 : DNO(부서번호),(테이블의 데이터 중에서 유일한 값을 가져야 되는것)
-- 제약조건명 : PK_DEPT_COPY_DNO
-- 사용법) ALTER TABLE 테이블명
-- ADD CONSTRAINT 제약조건명 PRIMARY KEY(컬럼)
ALTER TABLE DEPT_COPY
ADD CONSTRAINT PK_EMP_COPY_DNO PRIMARY KEY(DNO);
-- 예제2) 테이블 생성후에 외래키(참조키:FK) 추가하기 : EMP_COPY
-- 사용법) ALTER TABLE 테이블명
-- ADD CONSTRAINT 제약조건명
-- FOREIGN KEY(컬럼명) REFERENCES 부모테이블명(부모컬럼명);
ALTER TABLE EMP_COPY
ADD CONSTRAINT FK_EMP_COPY_DNO
FOREIGN KEY(DNO) REFERENCES DEPT_COPY(DNO);
29oracle시퀀스AND인덱스.sql
-- 시퀀스(***) : 실행할때마다 1씩 또는 몇개씩 숫자를 증가시키는 것(객체)
-- 예) 1++, 2++ 등
-- 용도) 자유게시판의 No(1~n), 중복없음, null 아님(기본키:PK)
-- 예제 1) SAMPLE_SEQ 시퀀스를 1부터 시작해서 10씩 증가시키는 시퀀스 생성
-- 사용법) CREATE SEQUENCE 시퀀스명
-- INCREMENT BY 증가값
-- START WITH 시작값
-- MINVALUE 최소값 [생략가능]
-- MAXVALUE 최대값 [생략가능]
CREATE SEQUENCE SAMPLE_SEQ
INCREMENT BY 10
START WITH 1;
-- 최초 1번은 아래와 같이 증가시켜야함
-- 실행 방법) 시퀀스명.NEXTVAL : 시퀀스가 증가함
SELECT SAMPLE_SEQ.NEXTVAL FROM DUAL;
--간단연습 : SMAPLE_SEQ2 시퀀스를 1부터 1씩 증가시키시퀀스 생성
CREATE SEQUENCE SAMPLE_SEQ2
INCREMENT BY 1
START WITH 1;
SELECT SAMPLE_SEQ2.NEXTVAL FROM DUAL;
-- (참고) 현재 시퀀스의 값을 보기 : 시퀀스명.CURRVAL
SELECT SAMPLE_SEQ.CURRVAL FROM DUAL;
-- 예제 2) 테이블에 insert 할때 시퀀스 사용하기
CREATE TABLE DEPT_TEMP
AS
SELECT * FROM DEPARTMENT
WHERE 1=2;
-- INSERT : 시퀀스
INSERT INTO DEPT_TEMP(DNO, DNAME, LOC)
VALUES(SAMPLE_SEQ2.NEXTVAL,'개발부','대전');
COMMIT;
-- 예제 3) 시퀀스 삭제
-- 사용법) DROP SEQUENCE 시퀀스명;
DROP SEQUENCE SAMPLE_SEQ;
DROP SEQUENCE SAMPLE_SEQ2;
-- 인덱스 : INDEX
-- 용도 : 컬럼에 생성해서 조회속도를 높임
-- 예제 3) 어떤 사이트에서 조회시 이름검색이(ENAME) 많다고 합니다.
-- 그런데 속도가 많이 느리다고 합니다. 인덱스를 생성해주세요
-- 사용법) CREATE INDEX 인덱스명 ON 테이블명(컬럼명);
-- 이름 조회 :
-- SELECT FROM EMPLOYEE
-- WHERE ENAME LIKE 'SOCTT%'; -- 속도느림
CREATE INDEX IX_EMPLOYEE_ENAME ON EMPLOYEE(ENAME);
-- 기타 : 결합 인덱스 : 컬럼 여러개에 인덱스 생성하기
--사용법) CREATE INDEX 인덱스명ON 테이블명(컬럼, 컬럼2....);
-- 예제 ) 아래의 sql 문이 자바에서 실행되는데 느리다고 합니다.
-- 개선해주세요
SELECT * FROM DEPARTMENT
WHERE DNAME = 'SALES'
AND LOC = 'NEW YORK';
CREATE INDEX IX_DEPARTMENT_DNAME_LOC ON DEPARTMENT(DNAME, LOC);
-- INDEX 삭제하기
-- 사용법) DROP INDEX 인덱스명;
DROP INDEX IX_DEPARTMENT_DNAME_LOC;
