테이블 복사 / Script를 이용해 특정 계정 다시 초기상태 만들고 데이터 import 해오기

- TRANSACTION (MSSQL에서는 BEGIN TRANSACTION 이라는 문구를 써줘야 트랜잭션이 시작됨)
- COMMIT,ROLLBACK 으로 트랜잭션을 끝낼 수 있다.
- DML(I,U,D)문장 시작 시 자동으로 시작
- CRUD(SELECT,INSERT,UPDATE,DELETE)
- DML + S ( SELECT는 쿼리라고 함 )
- 자체적으로 COMMIT 되는것들은 DDL(CREATE,ALTER,DROP,RENAME,TRUNCATE(delete)) 이다. 즉 create table 해버리면, rollback으로 취소할 수 없음
- DCL(GRANT,REVOKE)
- EXIT(AUTO COMMIT) 되는 경우 - POWER OFF(AUTO ROLLBACK) 전원을 그냥 꺼버린다거나 CMD창 X표시로 끌 경우

---

--똑같은데이터를 누군가 access하면 lock이 걸림을 명심!

--DDL(CREATE,ALTER,DROP)
--ORACLE 에서의 OBJECT: TABLE, INDEX , SYNONYM, SEQUENCE, VIEW
--이걸 CREATE,ALTER,DROP 이용해서 작업할 수 있음

-- TABLE
-- CHAR: 문자(고정길이)
-- VARCHAR2:문자(가변길이)
-- NUMBER(P,S) :숫자(가변길이) NUMBER(6,2) 1235.24 이렇게 저장되는 것
-- 숫자길이 고정 안하고 싶으면 NUMBER(4) 이렇게만 쓰면 됨
-- DATE : 날짜, 시간(고정길이)
-- LONG : 문자(가변길이 2GB)

 

CREATE TABLE BUSE01
(DNO NUMBER(2), --2자리만 들어가게끔, 소수점 못들어감
DNAME VARCHAR2(14),
ZIPCODE CHAR(7));

 

INSERT INTO BUSE01 VALUES (10,'AAA','123'); -- 트랜잭션 시작되는 시점

 

SELECT * FROM BUSE01;

--테이블 이름
--영문자(30자)로 시작
--영문자,숫자 사용가능(공백허용안함)
--특수문자는 _,$,# 만 가능
--이름은 중복허용 안됨
--컬럼명과 중복 권장 안함
--예약어 사용 못함 (SELECT, INSERT, TABLE)

-- Sawon, P_NO, Division10, Name_Rule -- 사용가능
-- 10Division, $sal, p-no, Alter -- 사용불가

 

 

CREATE TABLE CUS(ID CHAR(10),
NAME CHAR(10),
JUMIN CHAR(14),
AGE NUMBER(3),
ADDR VARCHAR2(50),
BIRTH DATE); -- 이렇게 만든 CREATE는 ROLLBACK으로 취소할 수 없다 (AUTO-CUMMIT됨)

 

---

Script를 이용해 KIM 계정 다시 초기상태로 만들기

현재 'Example.sql' 파일에 KIM의 초기 데이터들이 들어가 있다.

1. 일단 쿼리박스에서 KIM 계정과의 접속을 해제한다.
2. cmd> KIM이 아닌 다른 계정으로 접속 한다.
sqlplus LEE/BOB
3. 가져오려는 script의 경로를 입력해준다
@C:\oraclexe\SampleDB\DB\Example.sql
참고) Example.sql 을 들어가보면
CONNECT SYSTEM/dba
DROP USER KIM CASCADE;
이런 코드가 있다. 즉, 상단에서 conn system/dba를 해주기 때문에 어떤 계정으로 접속해도 상관이 없던 것이다. dba권한으로 접속한 후 KIM 계정을 강제 삭제하는 과정이다

마지막에 exit로 나가주는거 잊지 말기

쿼리박스에서 새접속 KIM을 해보면 다시 초기 상태의 KIM 데이터들이 들어가있다.

 

---

 

CREATE TABLE PAY3000
AS
SELECT * FROM PERSONNEL WHERE PAY>=3000;

SELECT * FROM PAY3000;

 

CREATE TABLE MON12
AS
SELECT * FROM PERSONNEL
WHERE TO_CHAR(STARTDATE,'MM')=12;
--WHERE STARTDATE LIKE '%/12/%'; 같은말

 

 

 

주의! GROUP BY

SELECT DNO,SUM(PAY) SUM_PAY FROM PERSONNEL
WHERE DNO = 10;
-- 에러) 단일 그룹의 그룹 함수가 아닙니다

SELECT DNO,SUM(PAY) SUM_PAY FROM PERSONNEL
WHERE DNO = 10
GROUP BY DNO;
-- SUM(PAY)는 단일 값이므로 반드시 DNO를 GROUP BY로 묶어줘야 에러가 안남

 

 

---

테이블 복사 하기

• 컬럼명 다르게 지정해서 복사하기
• 실제 PNO,PNAME,JOB,DNO 컬럼이 NUM,NAME,JOB,DNUM으로 바뀌어서 저장됨

SELECT* FROM MON12;

CREATE TABLE ANALYST (NUM,NAME,JOB,DNUM)
AS
SELECT PNO,PNAME,JOB,DNO FROM PERSONNEL
WHERE JOB='ANALYST'; 

 

 

• 컬럼명 (틀) 만 복사해서 새 테이블 생성하기

SELECT * FROM ANALYST;

CREATE TABLE SAWON1
AS
SELECT * FROM PERSONNEL
WHERE 0=1; --만약 이 코드가 없다면, PERSONNEL의 모든데이터가 SAWON1으로 복사돼버림

SELECT * FROM SAWON1;
-- 테이블은 복사되지만 데이터는 복사되지 않는다. (컬럼명, 즉 틀만 복사됨)

 

 

• 기존 테이블을 복사해서 새로 생성한 테이블도 제약조건은 복사되지 않는다.

CREATE TABLE SALESMAN1
AS
SELECT PNO,PNAME,JOB,PAY FROM PERSONNEL
WHERE JOB = 'SALESMAN';

 

SELECT * FROM SALESMAN1;

 

DESC PERSONNEL;

DESC SALESMAN; -- 제약조건은 복사되지 않는다

 

---

--08/08(목) START

--만들어 놓은 테이블 수정 (실무에서는 수정명령어를 잘 쓰진 않는다)
-- 데이터가 이미 들어가 있는 상태에서 컬럼을 추가하는건 좋지 않다!!

--컬럼 추가
ALTER TABLE SALESMAN1
ADD DNO NUMBER(2);

 

INSERT INTO SALESMAN1 VALUES (1111, 'SUZI', 'SALESMAN', 3000, 10);

SELECT * FROM SALESMAN1;

-- L테이블 : 최악의 테이블
-- 테이블이 이미 만들어진 후에 컬럼을 추가한 모양

 

 

• ALTER로 컬럼 추가하기

ALTER TABLE SALESMAN1
ADD (JUMIN CHAR(14), ZIP CHAR(7));
-- 컬럼 2개 이상을 추가할 때는 반드시 괄호로 묶어줘야 한다

 

 

ALTER TABLE SALESMAN1
ADD BIGI VARCHAR2(10) DEFAULT('AA');
-- 기본값이 AA로 전부 들어가 있다

 

 

-- 컬럼의 DEFAULT값은 테이블을 만들 때도 줄 수 있다
CREATE TABLE ABC
(ID NUMBER,
NAME CHAR(10) DEFAULT('이름없음'));

 

SELECT * FROM ABC;

INSERT INTO ABC VALUES (111, '수지');
INSERT INTO ABC (ID) VALUES (222);
--값이 없으면 '이름없음'으로 들어가고 값이 있으면 입력값으로 들어간다