Table

DDL문

SUBQUERY를 이용한 UPDATE

수정시 바로 값을 바꾸는 것이 아니라 다른 컬럼의 값으로 바꾸고 싶을때, 다른 컬럼의 확인 없이 SUBQUERY를 이용해서 한번에 값을 바꿀 수 있다.

UPDATE DEPT_COPY
SET 
    LOC = (SELECT LOC 
            FROM DEPT_COPY
            WHERE DNO = 20)
WHERE DNO = 10;

이렇게 하면 괄호안에서 조회된 값이 DNO가 10일때 LOC의 값이 된다.
20과 10의 지역이 같아졌다.

업데이트 할 컬럼이 여러개면 SUBQUERY를 여러개 만들지 말고 하나에 여러 컬럼을 넣어서 만들 수 있다.

UPDATE DEPT_COPY
SET 
    (DNAME, LOC) = (SELECT DNAME, LOC
            FROM DEPT_COPY
            WHERE DNO = 30)
WHERE DNO = 10;

이렇게 하나의 SUBQUERY만 만드는 것이 더 좋은 성능을 가진다.

DELETE 삭제

TRUNCATE보다는 느리지만 대신 부분삭제와 취소가 가능하다.

• 질의 : DELETE FROM 테이블
  WHERE 컬럼 = 값;

DELETE FROM DEPT_COPY
WHERE DNO = 10;

SELECT * FROM DEPT_COPY;

DNO가 10인 행이 모두 사라졌다.

삭제할때 주의할 점은 자식 테이블에서 삭제에 관련된 행을 먼저 삭제하고 부모의 행을 삭제해야한다.

ORACLE 툴에서 데이터 편집

데이터 탭에서 수정을 할 수 있다. +모양이 추가, X가 삭제 체크표시가 COMMIT이다.

컬럼 편집

ALTER ADD 컬럼 추가하기

테이블에 새로운 컬럼을 만들때 사용한다.

• 질의 : ALTER TABLE 테이블
  ADD(컬럼명 자료형);

ALTER TABLE DEPT20
ADD(BIRTH DATE);

새로운 날짜형식의 컬럼이 생성되었다.

ALTER MODIFY 컬럼 크기 변경

컬럼에 들어갈 데이터의 최대 크기를 변경할 수 있다.

• 질의 : ALTER TABLE 테이블
  MODIFY 컬럼 자료형(크기);

ALTER TABLE DEPT20 
MODIFY DNAME VARCHAR2(30);

크기가 30으로 바뀐 것을 확인 할 수 있다.

ALTER DROP 컬럼 삭제하기

컬럼을 삭제해보자.

• 질의 : ALTER TABLE 테이블
  DROP COLUMN 컬럼명;

ALTER TABLE DEPT20
DROP COLUMN BIRTH;

BIRTH컬럼이 삭제되었다.

테이블 명 변경

테이블 명을 바꿔보자.

• 질의 : RENAME 테이블명 TO 새 테이블명

RENAME DEPT20 TO DEPT30;

20에서 30으로 바꼈다

제약조건

테이블에 입력될 값으 제한하는 조건을 말한다.

UNIQUE 제약조건

유일한 값만 들어갈 수 있고 중복이 금지 된다. 단 NULL값은 들어갈 수 있다.

CREATE TABLE CUSTOMER( 
ID       VARCHAR2(20) UNIQUE 
PWD     VARCHAR2(20)  NOT NULL
NAME    VARCHAR2(20)  NOT NULL
PHONE   VARCHAR2(30)
ADDRESS VARCHAR2(1000)
);

테이블 생성시 자료형 뒤에 공백 후 넣어주면된다.
SQL탭에서 확인이 가능하다.

NOT NULL 제약 조건

NULL은 들어올 수 없게 조건을 거는 것이다.

CREATE TABLE CUSTOMER( 
ID       VARCHAR2(20) UNIQUE 
PWD     VARCHAR2(20)  NOT NULL
NAME    VARCHAR2(20)  NOT NULL
PHONE   VARCHAR2(30)
ADDRESS VARCHAR2(1000)
);

테이블 생성시 자료형 뒤에 공백 후 넣어주면된다.
NOT NULL은 여기서도 확인이 가능하다. 기본적으로 NOT NULL을 하지 않으면 NULLABLE은 YES이다.

기본키와 참조키

기본키 (Primary Key):

• 기본키는 테이블의 각 행을 식별하기 위한 고유한 식별자입니다.
• 각 행은 기본키 값을 가져야 하며, 이 값은 NULL이 아니어야 합니다.
• 테이블 내에 중복된 값을 가질 수 없습니다.
• 주로 고유 식별자(ID) 또는 조합된 필드들로 이루어집니다.
• 테이블 간의 관계를 설정할 때 사용됩니다.

참조키 (Foreign Key):

• 참조키는 다른 테이블의 기본키를 가리키는 필드입니다.
• 부모 테이블의 기본키와 동일한 데이터 형식과 크기를 가집니다.
• 외래 키로도 불립니다.
• 참조 무결성 제약 조건을 적용하여 부모 테이블의 데이터가 변경되었을 때 자식 테이블의 데이터가 일관성을 유지하도록 합니다.
• 테이블 간의 관계를 설정하여 데이터를 연결하고 정규화를 유지하는 데 사용됩니다.

PRIMARY KEY(PK)기본키 제약조건

유일한 값만 입력되면서 동시에 NOT NULL이 되는 조건이다.
추가적으로 인텍스도 자동생성 되어 조회속도를 올릴 수 있다.

CREATE TABLE CUSTOMER( 
ID       VARCHAR2(20) , 
PWD     VARCHAR2(20)  NOT NULL,
NAME    VARCHAR2(20)  NOT NULL,
PHONE   VARCHAR2(30),
ADDRESS VARCHAR2(1000),
CONSTRAINT PK_CUSTOMER2_ID PRIMARY KEY(ID)
);

"CONSTRAINT 제약조건이름 PRIMARY KEY(컬럼명)"을 테이블을 만들때 마지막에 넣어주면 된다. 주로 제약조건에는 "제약조건테이블명컬럼"으로 짓는다.

FOREIGN KEY 외래키 제약조건

참조키는 두 테이블 간에 부모와 자식관계를 형성시켜주어서, 부모의 값 범위 안에서만 자식에도 값을 줄수있게 제약을 줄 수 있다.

예를 들어 부모 테이블에 부서번호가 10~40의 수만 올 수 있다고 하면 자식도 그 사이의 값만 들어올수 있게 하는 것이다.

참조키 특징

1. 부모테이블의 컬럼은 유일한 조건이어야한다.(기본키나 UNIQUE를 걸어주어야한다.)
2. 삭제시 자식 테이블 데이터를 먼저 삭제하고 부모 테이블의 데이터를 삭제해야한다.

사용법

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
);

가장 밑이나 참조키 사용할 컬럼 옆에 "CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼) REFERENCES 부모테이블"을 해주면된다.

데이터 무결성 제약조건

기본키 / 외래키 제약조건을 이용해 데이터에 결점이 없게 할 수 있다.

CHECK 제약조건

컬럼에서 허용 가능한 데이터 범위 또는 조건을 지정할 수 있음

CREATE TABLE EMP_SECOND(
    ENO NUMBER(4) CONSTRAINT PK_EMP_SECOND_ENO PRIMARY KEY
    ,SALARY NUMBER(7,2) CONSTRAINT CK_EMP_SECOND_SALARY CHECK(SALARY>0) -- 양수값만 넣기
);

"CONSTRAINT 제약조건이름 CHECK(조건식)"을 해주면된다.

DEFAULT 제약조건

NULL이 들어오면 자동으로 특정값으로 값을 생성한다.

CREATE TABLE EMP_SECOND(
    ENO NUMBER(4) CONSTRAINT PK_EMP_SECOND_ENO PRIMARY KEY
    ,SALARY NUMBER(7,2) DEFAULT 1000
);

컬럼 뒤에 DEFAULT 와 값만 넣어주면된다.
SALARY에 아무 값도 넣지않았는데 1000이 들어갔다.

제약조건 확인

어떤 테이블에 어떤 제약조건을 걸었는디 명령어로 확인 할 수 있다.

SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS -- ORACLE에서 제공하는 테이블로 여기에 모든 제약조건이 저장된다.
WHERE TABLE_NAME IN ('EMPLOYEE', 'DEPARTMENT')
ORDER BY TABLE_NAME;

FROM USER_CONSTRAINTS 는 ORACLE에서 제공하는 테이블로 여기에 모든 제약조건이 저장된다.

이미 만들어진 테이블에 제약조건 주기

복사된 테이블에 제약조건

테이블을 복사할때 제약조건은 따로 걸어주어야한다.

CREATE TABLE DEPT_COPY
AS
SELECT * FROM DEPARTMENT;

CREATE TABLE EMP_COPY
AS
SELECT * FROM EMPLOYEE;

두 테이블 다 제약조건은 복사되지 않았다.

만들어진 테이블에 제약조건 주기

• 질의 : ALTER TABLE 테이블
  ADD CONSTRAINT 제약조건이름 PRIMARY KEY(컬럼);

ALTER TABLE EMP_COPY -- 기본키 제약조건 부여
ADD CONSTRAINT PK_EMP_COPY_ENO PRIMARY KEY(ENO);

ALTER TABLE EMP_COPY -- 외래키 제약조건 부여
ADD CONSTRAINT FK_EMP_COPY_DNO
FOREIGN KEY(DNO) REFERENCES DEPT_COPY(DNO);

이렇게 하면 제약조건이 추가가 된다.
잘 추가 되었다.

테이블 만든 뒤 제약조건 삭제

-- 1) 외래키(참조키:FK) 삭제하기
-- 사용법) ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
ALTER TABLE EMP_COPY DROP CONSTRAINT FK_EMP_COPY_DNO;

-- 2) 기본키 삭제하기
ALTER TABLE DEPT_COPY DROP PRIMARY KEY;

DROP CONSTRAINT로 삭제해준다. PRIMARY는 하나밖에 없으니 굳이 이름을 안 적어 주어도 된다.

Sequence와 Index

sequence

실행될 때마다 자동적으로 숫자를 증가시켜주어서 이걸 인덱스에 주면 자동으로 index를 형성할 수 있게 해준다.

주로 기본키 용도로 사용한다. 기본키는 not null에다가 중복도 안되야 하므로 1씩 증가하는 sequence를 사용하면 null도 아닌데다가, 수가 증가하니 중복도 되지 않는다.

SEQUENCE 생성법

• 질의 : CREATE SEQUENCE 시퀀스명
  INCREMENT BY 증가값
  START WITH 시작값
  MINVALUE 최솟값
  MAXVALUE 최댓값;

CREATE SEQUENCE SAMPLE_SEQ
INCREMENT BY 10
START WITH 1;

1부터 10씩 증가하는 시퀀스를 만들었다.
여기서 만든 시퀀스를 확인할 수 있고, 최대 최소값을 지정하지 않으면 다음과 같이 설정된다.

실행 하기

• 질의 :SELECT 시퀀스명.NEXTVAL FROM 테이블;

SELECT SAMPLE_SEQ.NEXTVAL FROM DUAL;

1부터 시작해서 실행할때마다 10씩 증가한다.

조회하기

현재 시퀀스 값을 보는 코드이다.

• 질의 : SELECT 시퀀스명.CURRVAL FROM 테이블;

SELECT SAMPLE_SEQ.CURRVAL FROM DUAL;

값을 확인 할 수 있다.

테이블에 INSERT할때 SEQUENCE 사용하기

테이블에 값을 넣을때 자동으로 특정 컬럼의 값이 증가하게 만들 수 있다.

1. 시퀀스 만들기
2. INSERT문에 시퀀스 사용하기

-- 1) 시퀀스 생성 : 초기값 1, 증가값 1
CREATE SEQUENCE DEPT_TEMP_SEQ
INCREMENT BY 1
START WITH 1;

--2) INSERT문에 사용하기
INSERT INTO DEPT_TEMP(DNO, DNAME, LOC)
VALUES(DEPT_TEMP_SEQ.NEXTVAL, 'ACCOUNTING', 'NEW YOURK');

1씩 증가하는 시퀀스를 만들었고 INSERT문에서 DNO의 값에 "시퀀스명.NEXTVAL"을 해주었다.

행이 추가될 때마다 값이 증가하는 것을 확인 할 수 있다.