[DB] ch7 SQL III

박소미·2023년 11월 25일
db
0

DB

목록 보기
7/14

7.1 데이터 정의어와 조작어

어떤 테이블에 대해 SELECT문을 사용하기 위해서는 테이블을 생성하고 데이터를 입력해야 한다.

데이터베이스 생성 -> 테이블 생성 -> 테이블에 데이터 입력/수정/삭제)

오라클 데이터베이스의 구조

7.2 CREATE

CREATE 명령문은 테이블을 포함해서 데이터베이스 안에 저장되는 모든 객체들의 구조를 정의한다.

데이터베이스 자체도 CREATE문을 이용하여 생성

CREATE DATABASE (오라클)
단순히 데이터베이스만 생성하면 되는 것이 아니고 데이터베이스가 사용될 수 있는 상태를 만들기 위해 3가지 절차가 필요하다.

1) 데이터베이스(pdb)를 생성한다.
2) 생성된 데이터 베이스를 활성화한다.
3) 데이터가 저장될 테이블스페이스를 생성한다.


데이터베이스 생성 문법

CREATE PLUGGABLE DATABASE 데이터베이스 이름
ADMIN USER 관리자계정명 IDENTIFIED BY 비밀번호 [ROLES = (역할)]
FILE_NAME_CONVERT = (PDB템플릿 폴더명, 생성될 데이터베이스 폴더명);

--ADMIN USER : 생성할 데이터베이스의 관리자 계정을 함께 생성
--FILE_NAME_CONVERT = 생성할 데이터베이스가 저장될 위치를 지정
  1. testdb를 생성하는 명령문 실행
CREATE PLUGGABLE DATABASE testpdb
ADMIN USER testmgr IDENTIFIED BY 4321
FILE_NAME_CONVERT = ('pdbseed' , 'TESTPDB' );
  1. 원활한 관리 작업을 위해 testmgr에게 권한을 추가한다.
alter session set container = testpdb;
GRANT resource, dba TO testmgr;
-- alter session .. : 현재 세션에 연결된 데이터베이스를 testdb로 변경
-- GRANT TO : testmgr에게 resource 권한과 dba 권한을 부여
  1. 오라클에서 생성한 PDB를 사용 가능하게 하려면 데이터베이스를 활성화시켜야 한다.
ALTER PLUGGABLE DATABASE testpdb open read write;
-- 읽기 쓰기가 가능한 상태로 활성화시키는 역할
ALTER PLUGGABLE DATABASE testpdb save state;
-- 활성화된 상태를 저장하여 다음에 다시 활성화할 필요가 없게 만드는 역할
  1. 데이터 저장을 위한 테이블스페이스를 생성한다.
CREATE tablespace 테이블스페이스 이름
DATAFILE 테이블스페이스가 저장될 물리적 파일 SIZE 파일사이즈 AUTOEXTEND ON;
-- SIZE 항목 : 물리적 파일의 초기 사이즈를 설정하는 역할
-- AUTOEXTEND ON 항목 : 데이터가 축적되어 초기에 지정한 사이즈를 초과하는 
-- 상황이 되면 자동적으로 파일 사이즈를 늘려주는 옵션

CREATE TABLESPACE testtbls
DATAFILE 'testtbls' SIZE 64M AUTOEXTEND ON ;

테이블 생성 기본 문법

CREATE TABLE 테이블명 (
컬럼명 자료형 [NOT NULL],
컬럼명 자료형 [NOT NULL],
.....,
[CONSTRAINT 제약조건명 PRIMARY KEY (컬럼목록)],
[CONSTRAINT 제약조건명 FOREIGN KEY (컬럼목록) REFERENCES 테이블명(컬럼목록)]
ON DELETE 처리방법
ON UPDATE 처리방법]
) TABLESPACE "테이블스페이스명" 


ex)
CREATE TABLE my_dept (
deptno number(2) not null,
dname varchar2(14) not null,
CONSTRAINT pk_deptno PRIMARY KEY(deptno)
); -- TABLESPACE "TESTTBLS";

CREATE TABLE my_emp (
empno number(5) not null,
ename varchar2(10) not null,
hiredate date not null,
deptno number(2),
CONSTRAINT pk_empno PRIMARY KEY(empno),
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES my_dept(deptno)
ON DELETE SET NULL
); -- TABLESPACE "TESTTBLS";

컬럼의 자료형
char 타입은 주민번호, 국가코드와 같이 저장되는 값의 길이가 일정한 경우 사용하고 주소처럼 값의 길이가 일정하지 않은 경우에 varchar2 타입을 사용한다.

컬럼에대한 추가사항 지정

초기값(default value) 지정

gender char(1) DEFAULT 'M',
-- 해당 컬럼에 사용자가 아무 값도 입력하지 않으면 자동으로 저장되도록 지정해 놓은 값

UNIQUE 지정

cell_phone char(11), 
..
CONSTRAINT uk_cell_phone UNIQUE(cell_phone),
-- 중복된 값이 저장되면 안되는 컬럼에 대해 지정 (기본키와는 다름)

✅ 기본키와 UNIQUE
공통점 : 특정 컬럼에 중복된 값이 저장되는 것을 방지
차이점 : 기본키 컬럼에는 널값이 저장 X, UNIQUE로 지정된 컬럼에는 널값이 저장 O

키 컬럼에 대한 정의
테이블 구조 정의시 기본키와 외래키도 함께 정의할 수 있다.


기본키 문법

CONSTRAINT 제약조건명 PRIMARY KEY (컬럼목록)
CONSTRAINT pk_deptno PRIMARY KEY(deptno) 
-- 기본키 컬럼이 여러 개일 경우 컬럼과 컬럼을 콤마로 구분하여 기술

외래키 문법

CONSTRAINT 제약조건명 FOREIGN KEY (컬럼목록) REFERENCES 테이블명(컬럼목록)
ON DELETE 처리방법
ON UPDATE 처리방법

CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES my_dept(deptno)
ON DELETE CASCADE

오라클에서 테이블 구조 확인

  • 테이블 구조 확인하는 명령어 : DESC
  • 테이블을 생성할 때 사용된 CREATE문을 알고 싶을 때 :
    SELECT DBMS_METADATA.GET_DDL('TABLE', '테이블명') FROM DUAL ;
    --테이블 명은 반드시 대문자로 써야함

테이블 컬럼 이름에 대한 주석 추가

COMMENT ON TABLE my_dept IS '부서 테이블' ;
COMMENT ON COLUMN my_dept.deptno IS '부서번호' ;

질의 결과로부터 테이블 생성하기💥
SELECT문으로 확인한 질의 결과를 테이블로 저장하는 것이 가능

CREATE TABLE my_emp_sal_high
AS
SELECT * FROM my_emp WHERE sal >= 2000 ;
-- 원 테이블의 기본키, 외래키등 컬럼 설정사항은 복사되지 않음

7.3 ALTER, DROP

CREATE : 데이터베이스 및 데이터베이스 안에 저장되는 객체들의 구조를 정의

ALTER : 객체의 구조를 변경할 때

DROP : 생성된 객체를 삭제

테이블에 새로운 컬럼 추가 

ALTER TABLE my_dept
ADD budget number(10,2) default (0) ;
-- my_dept 테이블에 새로운 컬럼 budget(예산)을 추가

컬럼 / 테이블 이름 변경 

ALTER TABLE my_dept
RENAME COLUMN budget TO dept_budget ;
--my_dept 테이블의 budget을 dept_budget로 변경


ALTER TABLE my_emp_sal_high
RENAME TO my_sal_high ;
--emp_sal_high 테이블의 이름을 sal_high로 변경

컬럼의 지정사항 변경 

ALTER TABLE my_dept
MODIFY dept_budget number(12,2) ;
--my_dept 테이블의 dept_budget에서 자료형의 자릿수를 number(10,2)에서
number(12,2)로 변경

컬럼 / 테이블 삭제 

ALTER TABLE my_dept
DROP COLUMN dept_budget ;
--my_dept 테이블의 dept_budget 컬럼을 삭제


CREATE TABLE tmp (
tmpid number(2)) ;
DROP TABLE tmp ;

기본키, 외래키의 추가와 삭제 

ALTER TABLE my_emp
ADD CONSTRAINT pk_empno PRIMARY KEY(empno),
ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES my_dept(deptno)
ON UPDATE CASCADE ON DELETE RESTRICT ;
-- emp 테이블에 pk,fk가 지정되지 않은 상태로 생성했을 때 추가 가능

7.4 INSERT, UPDATE, DELETE

INSERT : 테이블에 새로운 튜플(행)을 추가한다.
UPDATE : 테이블에 저장된 데이터를 수정한다.
DELETE : 테이블에 저장된 튜플(행)을 삭제한다.

위의 명령어를 실행시키면 변경 결과를 즉시 데이터베이스에 반영하지 않고 COMMIT 명령을 내리면 그 때 반영된다.

COMMIT 명령을 내리기 전에 잘못 실행한 것이 있으면 ROLLBACK 명령어로 취소 가능하다.

INSERT

  • 튜플의 값들을 테이블에 있는 컬럼의 순서대로 하는 경우 컬럼 이름 생략 가능
  • 테이블에 있는 순서대로 안 써도 됨
  • 모든 컬럼을 지정할 필요 x , 지정하지 않은 컬럼에는 자동으로 NULL값 입력
  -- 기본 문법
INSERT INTO 테이블명 (컬럼1, 컬럼2, ..컬럼n)
VALUES (값1, 값2, .., 값n);
  
  -- 예제
INSERT INTO my_dept (deptno, dname, loc)
VALUES (10, 'accounting', 'New York');
COMMIT ;

INSERT .. SELECT
다른 테이블에 있는 튜플들을 선택하여 다른 테이블에 추가할 때 사용

INSERT INTO my_emp_sal_high
SELECT * FROM my_emp WHERE sal >= 1500 AND sal < 2000 ;

UPDATE
테이블에 저장된 데이터를 수정하는 명령어

-- 기본 문법
UPDATE 테이블명
SET 컬럼1 = 수정값1,
    컬럼2 = 수정값2,
    ..
    컬럼n = 수정값n,
WHERE 조건문 ;

-- 예제
UPDATE my_dept
SET loc = 'JEJU'
WHERE deptno = 10 ;
COMMIT ;

UPDATE와 서브쿼리 


-- 연봉을 가장 적게 받는 사원의 급여를 10% 올리시오                       
UPDATE my_emp
SET sal = sal * 1.1
WHERE sal = ( SELECT MIN(sal)
FROM my_emp ) ;
COMMIT ;

DELETE
테이블에 저장된 튜플을 삭제하는데 사용되는 명령어 

-- 문법
DELETE FROM 테이블명
WHERE 조건문;

DROP과 DELETE 차이점
DELETE는 테이블 안에 있는 튜플들을 삭제하지만 테이블의 구조는 남아 있어서 새로운 튜플 입력이 가능하다.

DROP은 테이블 자체를 삭제하는데 사용하기 때문에 DROP에 의해 삭제되면 더 이상 새로운 튜플을 입력할 수 없다.

profile
박소미
이전 포스트

[DB] ch6 SQL II

다음 포스트

[DB] ch8 뷰,인덱스, PL/SQL

0개의 댓글