SQL / DML(트랜잭션, INSERT... 등), DDL(CREATE)

Cheol·2023년 5월 17일

SQL

목록 보기

6/7

edu day 6

DML

: 앞서 배운 SELECT문은 테이블에 저장된 테이터를 검색할 때 사용되는 SLQ문으로 일반적으로 쿼리(QUERY)문 이라고 부른다. DML(Data Manipulation Language)은 데이터베이스의 테이블에 새로운 데이터를 저장(INSERT) 하거나 삭제(DELETE) 또는 수정(UPDATE) 및 병합(MERGE)할 때 사용하는 데이터 조작어를 의미한다.

DML 및 DCL 종류

EMP - 사원번호: 기본키 / 부서번호: 참조키

기본키(Primary key, pk)의 특징

1. 중복 불가
2. NULL 불가(NOT NULL)

외래키(foreign key)(참조키(reference key)의 특징)

1. 참조되는 테이블의 컬럼데이터만 사용 가능

트랜잭션(TX): 데이터 조작어와 관련

1. 커밋, 롤백을 할 논리 단위 (작업단위)
2. DML(추가, 수정, 삭제, 병합)이 실행되는 순간 트랜잭션(TX)이 만들어진다.
3. commit(반영), rollback(취소) 명령으로 TX종료

단일행 INSERT 문

: INSERT 문은 테이블에 데이터를 저장하기 위한 데이터 조작어이다. 한 번에 하나의 행을 테이블에 저장하는 단일 행 INSERT 방법과 서브쿼리를 이용하여 한꺼번에 여러 행을 동시에 저장하는 다중 행 INSERT 방법이 있다.

NSERT INTO 테이블명 [(컬럼명,컬럼명2,...)]
VALUES ( 값, 값2, ... );

INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (50, '개발', '서울');    --TX가 시작
INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (60, '개발', '서울');
INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (70, '개발', '서울');
COMMIT; -- 반영 후 TX종료

50번 추가  --TX시작
10번 수정
20부서 삭제
ROLLBACK; --TX종료

컬럼명 생략(컬럼 순서에 맞게 전체 데이터를 다 넣을때만 가능)

INSERT INTO dept
VALUES ( 60 ,'인사‘,’경기‘);

INSERT INTO DEPT VALUES(60, '인사', '경기');

널(NULL) 값 저장

묵시적 방법 : INTO절에서 해당 컬럼을 생략하면 자동으로 컬럼값에 널값을 저장한다. 생략된 컬럼에는 자동으로 널값이 저장되기 때문에 NOT NULL 제약조건(PRIMARY KEY)이 설정된 컬럼인 경우에는 사용이 불가능하다.

INSERT INTO DEPT(deptno,dname) VALUES(70,'인사');

명시적 방법: VALUES 절의 컬럼 값에 널(NULL)값 또는 ''(빈문자열)을 직접 지정하는 것이다.

INSERT INTO DEPT(deptno,dname,loc) VALUES(80, '인사', NULL);
INSERT INTO DEPT(deptno,dname,loc) VALUES(81, '인사', '');

-- 이는 널이 아닌 공백 값이다.
INSERT INTO DEPT(deptno,dname,loc) VALUES(82, '인사', ' '); -- 널이 아닌 공백

INSERT- 특정 데이터 입력

특수값 입력(SYSDATE, USER)

INSERT INTO EMP VALUES(9000,USER, '연구원',7839,SYSDATE, 5000,NULL,90);

--> USER는 SCOTT계정 이름이, SYSDATE는 현재 날짜가 적용된다.

특정 데이터 타입으로 입력(RR/MM/DD 형식, TO_DATE() 사용)

INSERT INTO EMP VALUES (9001,'홍길동','MANAGER',7839,'2000/01/01',2000,NULL,30);
COMMIT;

INSERT INTO EMP VALUES (9002,'임꺽정','MANAGER',7839,TO_DATE('1999-12-13','YYYY/MM/DD'),2000,NULL,30);
COMMIT;

INSERT문 사용시 에러 발생 예

INTO절에 명시된 컬럼 수와 VALUES절에 명시된 컬럼 값의 개수가 일치하지 않는 경우

INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (11, '인사');

INTO절에 컬럼명을 생략하는 경우 VALUES절에서 컬럼값이 누락되어 에러 발생

INSERT INTO DEPT VALUES(12, '인사');

INTO절에 컬럼명의 데이터 타입과 VALUES절의 컬럼값의 데이터 타입이 일치하지 않으면 에러 발생

INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES ('개발', 13, '인사');

VALUE절의 컬럼값 지정 시 반드시 리터럴(literal)형식에 맞춰서 설정해야 된다. 문자와 날짜 리터럴을 지정할 때는 반드시 ''로 묶어야 되고 수치 리터럴은 ''없이 사용한다.

INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES('개발', 14, 인사);

복수행 INSERT문

: VALUES 절을 사용하는 대신에 Subquery를 이용하여 하나의 INSERT문을 사용하여 한꺼번에 여러 행을 생성할 수 있다. 즉 기존의 테이블에 저장된 데이터를 서브쿼리를 이용하여 복사한 후에 INSERT문으로 새로운 행을 생성하는 것이다.

주의할 점은 INTO절에서 지정한 컬럼의 개수와 데이터 타입이 서브쿼리를 수행한 결과와 반드시 동일해야 된다.

INSERT INTO 테이블명 [(컬럼명,컬럼명2,...)]
Subquery

테이블 구조 복사(CTAS=CREATE AS)

CREATE TABLE MYDEPT AS SELECT * FROM DEPT WHERE 1=2;
-- 구조만 복사, 데이터 없음, 기본키 조건 또한 초기화

데이터 구조만 복사한 데이터가 없는 MYDEPT테이블에 복수형 INSERT문 사용

INSERT INTO MYDEPT SELECT * FROM DEPT;
--'n개의 행이 삽입되었습니다.' 출력되면 성공

다중 테이블 다중행 INSERT문

: 한 번의 INSERT시 여러 테이블에 복수 데이터를 저장하는 방법을 INSERT ALL이라고 부른다.

조건 INSERT ALL문 구조

INSERT ALL
[WHEN 조건식 THEN]
INTO 테이블1 VALUES ( 컬럼명,컬럼명2,...,컬럼명n)
[WHEN 조건식2 THEN]
INTO 테이블2 VALUES ( 컬럼명,컬럼명2,...,컬럼명n)
Subquery;

테이블 구조만 복사

CREATE TABLE MYEMP_HIRE AS SELECT EMPNO, ENAME, HIREDATE, SAL FROM EMP WHERE 1=2;
--MYEMP_HIRE TABLE 생성

CREATE TABLE MYEMP_MGR AS SELECT EMPNO, ENAME, MGR FROM EMP WHERE 1=2;
--MYEMP_MGR TABLE 생성

테이블 구조를 복사한 다중 테이블에 다중 데이터 저장

INSERT ALL 
INTO MYEMP_HIRE VALUES (EMPNO,ENAME,HIREDATE,SAL)
INTO MYEMP_MGR VALUES (EMPNO, ENAME, MGR)
SELECT EMPNO, ENAME, HIREDATE, SAL, MGR
FROM EMP;

무조건 INSERT ALL문 구조

INSERT ALL
INTO (TABLE 1) VALUES (col1, col2, ...)
INTO (TABLE 2) VALUES (col1, col2, ...)
Subquery;
조건이 있을 때

--테이블 생성
CREATE TABLE MYEMP_HIRE2 AS SELECT EMPNO, ENAME, HIREDATE, SAL FROM EMP WHERE 1=2;
CREATE TABLE MYEMP_MGR2 AS SELECT EMPNO, ENAME, MGR FROM EMP WHERE 1=2;

--조건 INSERT ALL
INSERT ALL 
WHEN SAL > 3000 THEN INTO MYEMP_HIRE2 VALUES (EMPNO,ENAME,HIREDATE,SAL)
WHEN MGR = 7698 THEN INTO MYEMP_MGR2 VALUES (EMPNO,ENAME,MGR)
SELECT EMPNO, ENAME, HIREDATE,SAL,MGR
FROM EMP;

INSERT FRIST문은 WHEN절에 지정된 조건이 중복되는 경우에 처음 조건에 일치하는 테이블에만 저장되고 이후 조건이 일치해도 테이블에 저장되지 않는 방법이다.

UPDATE 문

: 테이블에 저장된 데이터를 수정

UPDATE 테이블명
SET 컬럼명=변경할값[, 컬럼명1=변경할값][WHERE 조건식];

50번 부서의 부서명을 영업, 지역을 서울로 변경

UPDATE MYDEPT SET loc = '서울', dname = '영업' WHERE DEPTNO = 50;

mydept테이블의 부서번호가 60인 사원의 부서명을 dept테이블의 부서번호가 10인 부서의 부서명으로 수정하고 부서위치는 dept테이블의 부서번호가 20인 부서위치로 수정하는 SQL문

UPDATE MYDEPT SET DNAME = (SELECT DNAME FROM MYDEPT WHERE DEPTNO=10), LOC = (SELECT LOC FROM MYDEPT WHERE DEPTNO = 20) WHERE DEPTNO = 60;

DELETE 문

DELETE FROM 테이블명
[WHERE 조건식];

부서 번호가 50인 사원만 MYDEPT 테이블에서 삭제

DELETE FROM MYDEPT WHERE DEPTNO =50;

부서번호가 20인 부서위치와 동일한 위치에 해당하는 행을 MYDEPT 테이블에서 삭제

DELETE FROM MYDEPT WHERE LOC = (SELECT LOC FROM MYDEPT WHERE DEPTNO = 20);

DML 응용문제

CAPACITY를 10%늘려서 변경하시오

UPDATE TB_DEPARTMENT SET capacity = ROUND(CAPACITY * 1.1);

학번 A413042 박건우 학생의 주소가 '서울시 종로구 숭인동 181-21'로 변경됨

UPDATE TB_STUDENT SET student_address = '서울시 종로구 숭인동 181-21' WHERE student_no = 'A413042';

SELECT * FROM TB_STUDENT WHERE STUDENT_NO = 'A413042';

주민번호 뒷자리를 저장하지 않기로 했다. 내용을 변경시켜라

UPDATE TB_STUDENT SET student_ssn = SUBSTR(STUDENT_SSN,1,6);

휴학중인 학생들과 관련한 TB_GRADE 테이블의 데이터를 모두 삭제

DELETE FROM TB_GRADE WHERE STUDENT_NO IN (SELECT STUDENT_NO FROM TB_STUDENT WHERE absence_yn = 'Y');

의학과 김명훈 학생의 2005년도 1학기 '피부생리학' 점수가 잘못되어 정정해야 한다. 해당 과목의 학점을 3.5로 변경 시키시오

UPDATE TB_GRADE SET POINT = 3.5 WHERE (STUDENT_NO,CLASS_NO) IN (SELECT STUDENT_NO, TB_GRADE.CLASS_NO FROM TB_STUDENT JOIN TB_GRADE USING(STUDENT_NO) JOIN TB_DEPARTMENT USING(DEPARTMENT_NO) JOIN TB_CLASS ON TB_CLASS.CLASS_NO = TB_GRADE.CLASS_NO WHERE STUDENT_NAME = '김명훈' AND DEPARTMENT_NAME = '의학과' AND CLASS_NAME = '피부생리학' AND TERM_NO = 200501);


SELECT STUDENT_NAME, TB_CLASS.CLASS_NAME, POINT FROM TB_STUDENT JOIN TB_GRADE USING(STUDENT_NO) JOIN TB_DEPARTMENT USING(DEPARTMENT_NO) JOIN TB_CLASS ON TB_CLASS.CLASS_NO = TB_GRADE.CLASS_NO WHERE STUDENT_NAME = '김명훈' AND DEPARTMENT_NAME = '의학과' AND CLASS_NAME = '피부생리학';

MERGE 문

트랜잭션

관리자의 특징

SELECT * FROM MYDEPT;	--바로 접근 불가능
SELECT * FROM SCOTT.MYDEPT;	-- MYDEPT TABLE을 보유하는 SCOTT를 참조하여 접근 가능

CREATE, ALTER, DROP 하는 명령어인 DDL은 자동으로 COMMIT된다.

1) 트랜잭션내의 모든 데이터 변경 사항은 트랜잭션이 종료되기 전까지는 모두 임시적 이다. 따라서 데이터를 변경 전 상태로 복구 할 수 있다.
2) 변경된 행은 내부적으로 잠금(Lock)이 걸리게 되어 해당 사용자를 제외한 나머지 사용자는 해당 행들을 변경할 수 없다. 트랜잭션을 COMMIT 또는 ROLLBACK으로 종료시켜야 잠금(Lock)이 해제된다.

LOCK 경합
A사용자가 DML문을 실행한 후 커밋하지 않으면 B사용자가 DML문을 실행할 때 실행되지 않고 대기상태가 된다.(무한대기)

--> DML문을 사용한 경우 에는 반드시 COMMIT이나 ROLLBACK으로 트랜잭션을 종료시켜야 다른 사용자가 무한 대기하는 상황을 피할 수 있다

DDL(Data Definition Language)

: 데이터베이스의 구조를 생성하거나 수정 및 삭제하는데 사용되는 SQL문이다. 자동으로 COMMIT되기 때문에 데이터베이스에 즉각 영향을 미치며 데이터베이스 사전(DATA DICTIONARY)에 정보가 저장된다.

오라클 객체

DDL문 종류

테이블 생성

CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [DEFAULT 값 | 제약조건][,...] );

스키마 : 계정

SELECT deptno,dname,loc
FROM SCOTT.dept;

일반적으로 자기 스키마일 때는 생략 가능. 다른 스키마의 객체를 접근하기 위해서는 '스키마.객체'형식으로 한다.

DB객체 이름 지정 방법

테이블 및 컬럼명은 문자로 시작하고 길이는 30문자 이내로 작성한다.
테이블 및 컬럼명은 A~Z, a~z, 0~9, _, $, #를 사용할 수 있고 한글 사용도 가능하지만 권장하지 않는다.
동일한 스키마 내에서는 다른 객체와 이름이 중복되면 식별이 안되기 때문에 중복되지 않도록 이름을 지정해야 된다. 하지만 스키마가 다른 경우에는 스키마를 통하여 식별이 가능하기 때문에 객체 이름의 중복이 가능하다.
오라클 데이터베이스가 예약어로 사용하는 이름은 사용이 불가능하며 대소문자는 구별하지 않는다.
소문자로 테이블 명을 설정해도 데이터 사전(Data Dictionary)에는 자동으로 대문자로 저장이 된다. 따라서 테이블 명을 검색할 경우에는 반드시 대문자로 지정해야 된다.

오라클의 데이터 형

CHAR(size) : 고정 길이의 문자 데이터 저장.
1바이트 ~ 2000바이트
VARCHAR2(size) : 가변 길이의 문자 데이터 저장.
1바이트 ~ 4000 바이트

업로드중..
-> 대부분 VARCHAR2 사용

ROWID는 테이블에서 실제 데이터 행이 저장되어 있는 논리적인 주소값이다.
데이터베이스 전체에서 중복되지 않는 유일한 값이다.
검색 속도를 향상시키기 위해 사용한다.
테이블내 행의 고유주소를 가지는 64비트 문자

제약 조건 (Constraints Rule)

업로드중..

컬럼 레벨 제약조건 : 한 개의 컬럼에 한 개의 제약조건만 정의가 가능하고 위의 5가지 모든 제약조건에 대해서 사용이 가능하다. (단점: 컬럼 하나당 하나의 제약조건밖에 못쓴다.)
테이블 레벨 제약조건 : 테이블의 컬럼 정의와 분리하여 정의한다. 한 개 이상의 컬럼에 한 개의 제약조건을 정의할 수 있으며 NOT NULL제약조건을 제외한 나머지 4개의 제약조건에 사용이 가능하다.(하나의 컬럼에 여러 개의 제약조건 부여 가능)

Cheol

이전 포스트