[Oracle] create, alter 등

 

 

CREATE

• 테이블 생성

-- 테이블 생성
CREATE TABLE EMP_DDL (
	EMPNO NUMBER(4),
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2)
);

SELECT * FROM emp_ddl




-- 기존 테이블 열 구조와 데이터를 복사해,
-- 새 테이블 생성하기
CREATE TABLE DEPT_DDL
	AS SELECT * FROM DEPT;

SELECT * FROM DEPT_DDL;




-- EMP 테이블에서 DEPTNO = 30 인 애들만 넣은 테이블 생성
CREATE TABLE EMP_DDL_30
	AS SELECT *
		FROM EMP
		WHERE DEPTNO = 30;




-- WHERE 1 != 1 식이 성립되지 않기 때문에
-- 아무것도 출력이 안되고 리스트 형식만 뜸
-- 양식만 갖고와야 할때 씀
CREATE TABLE EMPDEPT_DDL
	AS SELECT E.EMPNO, E.ENAME, E.JOB, E.HIREDATE,
	E.SAL, E.COMM, D.DEPTNO, D.DNAME, D.LOC
	FROM EMP E, DEPT D
	WHERE 1 <> 1;

SELECT * FROM EMPDEPT_DDL;

 

 

 

ALTER

• 테이블 변경

MODIFY

• 열의 자료형을 변경

DROP

• 열을 삭제

-- 테이블 복사
CREATE TABLE EMP_ALTER
	AS SELECT * FROM EMP;

SELECT * FROM EMP_ALTER;

-- 사진 1-1
-- 컬럼 추가 (ALTER TABLE + ADD 키워드)
ALTER TABLE EMP_ALTER ADD HP VARCHAR2(20);




-- 열 이름을 변경하는 RENAME COLUMNN ~ TO ~
ALTER TABLE EMP_ALTER RENAME COLUMN HP TO HEL;



-- 열의 자료형을 변경하는 MODIFY
-- 파일의 크기를 줄이지 못함 (늘리는 것만 가능)
ALTER TABLE EMP_ALTER
MODIFY EMPNO NUMBER(5);



-- 열을 삭제할때 사용하는 DROP
ALTER TABLE EMP_ALTER
DROP COLUMN HEL;

SELECT * FROM EMP_ALTER;

 

 

 

 

테이블 변경

• RENAME, TRUNCATE, DROP

-- 테이블 이름 변경
RENAME EMP_ALTER TO EMP_RENAME;



-- 테이블의 데이터 삭제
TRUNCATE TABLE EMP_RENAME;

SELECT * FROM EMP_RENAME;


-- 테이블 삭제하기
DROP TABLE EMP_RENAME;

 

 

 

CRUD

• create(insert, create table), read(select), update(update, alter table), delete(delete, drop table)

 

트랜잭션

• 더이상 분할할 수 없는 최소 수행 단위

 

 

CREATE, INSERT, UPDATE, DELETE

-- 테이블 복사하기
CREATE TABLE DEPT_TEMP
AS SELECT * FROM DEPT

SELECT * FROM DEPT_TEMP

-- DEPT_TEMP 테이블에 데이터(VALUES) 컬럼별로 순서대로 추가하기
-- 컬럼 갯수에 맞게 지정해야함
INSERT INTO DEPT_TEMP
	VALUES(50,'DATABASE','SEOUL');

INSERT INTO DEPT_TEMP
	VALUES(60, 'NETWORKS', 'BUSAN');

-- NULL 추가 가능
-- '' 보다 이 방식으로 NULL 넣는 것을 선호함
INSERT INTO DEPT_TEMP
	VALUES(70,'WEB',NULL);
	

-- 공백은 NULL 으로 인식함
INSERT INTO DEPT_TEMP 
	VALUES(80, 'MOBILE', '');
	

-- 부분적으로 데이터 삽입하기
-- 나머지는 NULL 으로 들어감
INSERT INTO DEPT_TEMP (DEPTNO, LOC)
	VALUES (90, 'INCHEON');
	

-- 테이블 양식만 가져오기
CREATE TABLE EMP_TEMP
	AS SELECT * FROM EMP WHERE 1 != 1

-- 테이블에 데이터 추가하기 INSERT
-- 날짜 입력하기. / 혹은 - 로 끊어 입력하면 됨
INSERT INTO EMP_TEMP (HIREDATE)
	VALUES ('2001/01/01')
	
	
INSERT INTO EMP_TEMP (HIREDATE)
	VALUES (TO_DATE('2001/01/01', 'YYYY-MM-DD'))	

-- 사진 1-1
INSERT INTO EMP_TEMP (
	EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
	SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
	FROM EMP E, SALGRADE s
	WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
	AND S.GRADE = 1;

-- 테이블에 있는 데이터 수정하기 - 새 테이블 생성
CREATE TABLE DEPT_TEMP2
	AS SELECT * FROM DEPT;

-- 데이터 전체 수정하기 (LOC 열 값을 모두 'SEOUL'로 업데이트)
-- "" 안됨 '' 이어야함
UPDATE DEPT_TEMP2
	SET LOC= 'SEOUL';

-- 데이터 일부분 수정
-- DEPTNO = 40 인 줄에서, DNAME 과 LOC 을 수정
UPDATE DEPT_TEMP2
SET DNAME = 'DATABASE', LOC = 'SEOUL'
WHERE DEPTNO = 40;

SELECT * FROM EMP_TEMP

UPDATE EMP_TEMP
	SET COMM = 50
	WHERE SAL <= 2500;

-- 테이블 삭제하기 (JOB = 'MANAGER') 인 곳 전부 지우기
DELETE FROM EMP_TEMP2
WHERE JOB = 'MANAGER';

-- 테이블에 있는 전체 데이터 삭제하기
DELETE FROM DEPT_TEMP2;

CREATE TABLE DEPT_tcl
	AS SELECT *
	FROM DEPT;

INSERT INTO DEPT_tcl VALUES (50, 'DATABASE', 'SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH';

 

사진 1-1

 

 

 

SEQUENCE

-- 테이블에 데이터 추가
CREATE TABLE DEPT_tcl
	AS SELECT *
	FROM DEPT;

INSERT INTO DEPT_tcl VALUES (50, 'DATABASE', 'SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH';

-- 트랜잭션 취소
-- 직전 실행 상태로 돌아가기
-- 단, COMMIT 하기 이전으로는 못 돌아감
ROLLBACK

SELECT * FROM DEPT_tcl

-- EMP 테이블의 SAL 열에 인덱스 생성
CREATE INDEX IDX_EMP_SAL
ON EMP(SAL)

-- 시퀀스 : 특정 규칙에 맞는 연속 숫자를 생성하는 객체
CREATE SEQUENCE 생성할 시퀀스이름
INCREMENT BY N --시퀀스에서 생성할 번호의 증가값
START WITH N --시퀀스에서 생성할 번호의 시작
MAXVALUE N --시퀀스에서 생성할 번호의 최대값
MINVALUE N --시퀀스에서 생성할 번호의 최소값
CYCLE / NOCYCLE --시퀀스에서 생성한 번호가 최댓값에 도달했을때
--CYCLE 이면 시작값에서 다시 시작
--NOCYCLE 이면 번호 생성이 중단됨
CACHE N / NOCACHE
-- CACHE : 캐시를 사용하여 시퀀스가 생성할 번호를 메모리에 미리 할당해 놓은 수를 지정
-- 속도 빠름, 동시사용자 많으면 유리. 기본값 20
-- NOCACHE : 캐시사용안함

-- 양식 가져오기
CREATE TABLE DEPT_SEQUENCE
AS SELECT *
FROM DEPT
WHERE 1 <> 1;

-- 10씩 증가, 생성할 번호의 시작값 10, 50까지가 최댓값
CREATE SEQUENCE SEQ_DEPT_SEQUENCE
INCREMENT BY 10
START WITH 10
MAXVALUE 50
NOCYCLE

-- 최댓값(50)을 넘으면 에러 발생
-- 사진 1-2
INSERT INTO DEPT_SEQUENCE
VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE', 'SEOUL')

SELECT * FROM DEPT_SEQUENCE

-- 시퀀스 삭제
DROP SEQUENCE SEQ_DEPT_SEQUENCE

 

사진 1-2

 

 

 

 

 

 

제약조건

• 컬럼에 지정함
• NOT NULL
  • 지정한 열에 NULL 을 허용하지 않음
• UNIQUE
  • 지정한 열이 NOT NULL 이면서 중복되지않는 유일한 값을 가져야 함
• PRIMARY KEY
  • 지정한 열이 유일한 값이면서, NULL을 허용하지 않음
  • 테이블에 하나만 지정 가능
• FOREIGN KEY
  • 다른 테이블의 열을 참조하여 존재하는 값만 입력 가능

 

-- 제약조건
-- NOT NULL
CREATE TABLE TABLE_NOTNULL (
	LOGIN_ID VARCHAR2(20) NOT NULL,
	LOGIN_PWD VARCHAR2(20) NOT NULL,
	TEL VARCHAR2(20)
);

-- NULL 이면 오류가 걸림
INSERT INTO TABLE_NOTNULL (LOGIN_ID, LOGIN_PWD, TEL)
VALUES ('ADMIN', NULL, '010')

-- PRIMARY KEY
CREATE TABLE TABLE_PK(
	LOGIN_ID VARCHAR2(20) PRIMARY KEY,
	LOGIN_PWD VARCHAR2(20)NOT NULL,
	TEL VARCHAR2(20)
);

-- REFRENCES : 열을 참고함
-- ON DELETE CASCADE : 열데이터를 삭제할 때, 데이터를 참조하고있는 데이터도 함께 삭제
CONSTRAINT 제약조건이름 REFERENCES 참조테이블(참조할 열) ON DELETE CASCADE

CREATE TABLE EMP_FK (
	EMPNO NUMBER(4) PRIMARY KEY,
	ENAME VARCHAR2(10),
	DEPTNO NUMBER(2) REFERENCES DEPT_FK(DEPTNO)
)

 

 

 

스키마

• DB에서 데이터간 관계, 구조, 제약조건 등
  • 테이블, 뷰, 인덱스와 같은 구조 포함
  • 데이터를 저장 및 관리하기 위해 정의한 논리적인 데이터베이스 구조의 범위
• 임의의 사용자가 생성한 모든 데이터베이스 객체를 의미
• = 사용자 이름 (사용자가 생성한 여러객체의 소유자)