Oracle 6일차(1)-UPDATE, DELETE, CONSTRAINTS, TRANSACTION, DDL

1.DML(INSERT,UPDATE,DELETE)

UPDATE

UPDATE 테이블 SET 변경내용

WHERE 조건;

UPDATE PERSONNEL SET DNO=30

WHERE PNO=1111;

→ PNO=1111인 사원의 DNO를 30으로 변경

UPDATE SAWON SET DNO=20;

→ 조건이 없으면 다 변경

주소가 제주도인 고객의 월급을 500원 인상

▶SUBQUERY

UPDATE EXCOMPANY SET PAY=PAY+500

WHERE USERID IN

(SELECT USERID FROM EXCUSTOM WHERE ADDR1='제주도'); → SUBQUERY

▶JOIN : ORACLE에서는 사용 불가능.

  MS SQL에서만 가능

UPDATE EXCOMPANY SET PAY=PAY+500

FROM EXCOMPANY A, EXCUSTOM B         → JOIN

WHERE A.USERID=B.USERID AND ADDR1='제주도';

DELETE

DELETE FROM PERSONNEL → FROM은 생략 가능

WHERE PNO=1112;

DELETE BUSEO;                 → 조건이 없으면 다 변경

EXSALES에서 판매 내역이 없는 고객정보를 삭제

▶SUBQUERY

DELETE EXCUSTOM

WHERE USERID NOT IN

(SELECT USERID FROM EXSALES);

2.에러가 발생하는 경우

▶CONSTRAINTS:제약

▶제약조건

  Primary Key : 중복X, null X

  Unique Key  : 중복X, null허용 (MS SQL은 한 번 허용

  Check Key   : 제약조건의 기준( 미성년자는 가입 불가. 여성만 가입 가능 …)

  Not null Key :

  Foreign Key : 외부참조키. 참조하는 key값과 데이터타입이 똑같아야 함.

           참조하는 key는 PK 또는 UK

  Default Key : 값이 없으면 기본적인 값을 넣어줌 → MS SQL에서 Not null Key대신

▶INSERT 에러

INSERT INTO PERSONNEL (PNO,PNAME,DNO)

VALUES (7711,'SONG',99);

→무결성 제약 조건(KIM.PERSONNEL_PNO_PK)에 위배됩니다

▶UPDATE 에러

UPDATE PERSONNEL SET DNO=55

WHERE DNO=20;

→무결성 제약조건(KIM.PERSONNEL_DNO_FK)이 위배되었습니다- 부모 키가 없습니다

▶DELETE 에러

DELETE DIVISION WHERE DNO=50;

→무결성 제약조건(KIM.PERSONNEL_DNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다

3.TRANSACTION

▶DML문장(INSERT, UPDATE, DELETE) 시작시 자동으로 시작

▶COMMIT,ROLLBACK 해야함

▶오토커밋인 경우

   CREATE, ALTER, DROP

   EXIT

▶기능①

A계좌에서 B계좌로 500원 송금

A계좌 1000원, B계좌 500원

1. A계좌 확인

2. A계좌 잔액 확인(1000원)

3. A계좌에서 500원 차감(500원)

4. B계좌 확인

5. 500원 송금

6. B계좌 잔액 확인(500원)

7. B계좌에 500원 증감(1000원)

8. 완료

→ 이 중에 하나라도 실행이 안되면 에러발생

→ LOG파일에 저장 (선기록 후조치)

→ 취소(ROLLBACK)

▶기능②

Lock

서울 ▶ LA 2019-1-20 09:00 IC KAL302 A1

A가 접속을 하면 다른 사람들은 접속 불가

4.DDL

▶CREATE,ALTER,DROP

▶OBJECT(TABLE,INDEX,SYNONYM,SEQUENCE,VIEW)

TABLE

▶DATA TYPE

▶CHAR : 문자(고정길이)

  메모리나 하드디스크의 낭비가 심하다

  속도가 빠르다

  WHERE절에는 대부분 CHAR

▶VARCHAR2 : 문자(가변길이)

  공간이 남으면 OS에 맡기고, 다시 사용할때 가져옴

  메모리나 하드디스크의 낭비가 없다

  속도가 느리다

 단편화(CHAINING)   

▶NUMBER(P,S) : 숫자(가변길이). 전체P자리, 소숫점S자리

             NUMBER 괄호가 없으면 : 내가 원하는 숫자 다 가능

▶DATE : 날짜(고정길이,7BYTE)

▶LONG : 문자(가변길이,2GB)

테이블 이름

1.영문자로 시작

 2.영문자,숫자 사용가능

 3.특수문자는 _,$,#만 가능

4.이름은 중복 불가

5.컬럼명은 테이블명과 달라야함

6.예약어(INSERT,VALUES 같은) 사용불가

CREATE

CREATE TABLE 테이블명

(컬럼1 조건,

컬럼2 조건,

……

컬럼M 조건);

CREATE TABLE BUSEO1

(DNO NUMBER(2),

DNAME VARCHAR(10),

ZIPCODE CHAR(7));

CREATE TABLE SALESMAN1

AS

SELECT PNO,PNAME,JOB FROM PERSONNEL

WHERE JOB='SALESMAN';

▶테이블 복사

12월에 입사한 사원들의 데이터를 MON12로 저장

CREATE TABLE MON12

AS

SELECT * FROM PERSONNEL

WHERE TO_CHAR(STARTDATE,'MM')=12; ▶WHERE STARTDATE LIKE '%/12/%'

▶제약조건은 복사X

CREATE TABLE DIVISION1

AS

SELECT * FROM DIVISION;

▶테이블의 구조 복사

CREATE TABLE SAWON1

AS

SELECT * FROM PERSONNEL

WHERE 0=1;

정규형

제1정규형 : 테이블은 Primary Key가 있어야 한다.

제2정규형