[ SQL ] Oracle sql문 #5 _ 데이터 조작 & 처리 & 제어
SQL 문의 형태
[데이터 조작어 ]
- DML(Data Manipulation Language)은 데이터 조작어를 의미한다.
- 데이터 조작어는 데이터를 조작할 때 사용한다.
- 데이터 조작어는 응용 프로그램이 데이터베이스에 대해 데이터 등록, 데이터 삭제, 데이터 갱신을 위한 데이터베이스 언어이다.
- 데이터 조작어는 관계형 데이터베이스에 대해 업데이트 등의 데이터 조작을 위해 사용된다.
- 데이터 조작어는 데이터베이스의 운영에 관련해 많이 사용되는 데이터 질의어와 함께 데이터를 처리한다.
- 데이터 조작어의 넓은 범위에서는 데이터 질의어를 포함하며 기본적인 데이터 처리 기능인 CRUD 이다.
- CRUD 는 Create(생성), Read(읽기), Update(갱신), Delete(삭제)를 묶어서 일컫는 말이다.
- 데이터 조작어는 트랜잭션과 관련이 있으므로 SQL 문이 실행되었다고 실제 데이터베이스에 반영되지 않고 언제든지 실행 결과를 취소시킬 수 있다.
* 데이터 조작어의 주요 오류
- 같은 값을 입력할 때
ORA-00001: 무결성 제약조건(SCOTT.PK_DEPT)에 위배됩니다 - 컬럼을 생략하고 모든 컬럼을 저장하지 않을 때
ORA-00947: 값의 수가 충분하지 않습니다.. - 컬럼을 순서대로 값을 지정하지 않을 때
ORA-01722: 수치가 부적합합니다.
1. insert 문
- insert 문은 데이터를 입력한다.
- insert 문은 테이블에 새로운 데이터를 입력할 때 사용한다.
insert into 테이블명(column1, column2, … columnN)
values(값 1, 값 2, … 값 N);
insert into 테이블명(column1, column2, … columnN)
- insert into 테이블명(column1, column2, … columnN) 문
지정한 테이블명으로 테이블과 데이터를 저장할 컬럼을 지정한다.
컬럼은 생략할 수 있지만, 명시적으로 컬럼을 지정하는 것을 권장한다.
컬럼을 생략하는 경우에는 반드시 테이블의 모든 컬럼에 데이터를 저장한다. - values(값 1, 값 2, … 값 N);
values 문에는 컬럼과 일대일 대응이 되도록 값들을 지정하고 ;(세미콜론)으로 종료한다.
테이블의 컬럼 순서대로 값을 지정해야 한다.
컬럼값을 명시하지 않으면 null 값이 자동으로 저장된다..
ex 1 ) board 테이블에 새로운 컬럼값을 저장한다.
▼SELECT* FROM BOARD;
| INSERT INTO BOARD(NUM, TITLE, AUTHOR, CONTENT) VALUES(1,'테스트','홍길동','테스트입니다'); SELECT* FROM BOARD; |
VALUES ( , , ,....) : 컬럼과 일대일 대응이 되도록 값들을 지정
1 => NUM
'테스트' => TITLE
'홍길동' => AUTHOR
'테스트입니다' => CONTENT
컬럼값을 명시하지 않으면 null 값이 자동으로 저장
ex 2 ) dept 테이블에 새로운 부서 정보를 저장한다
▼ SELECT*FROM DEPT;
| INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(45,'개발부','서울'); -- 1 행 이(가) 삽입되었습니다. SELECT*FROM DEPT; |
ex 3 ) 부모 키가 존재하지 않는 데이터를 입력하면 오류가 발생한다.
| INSERT INTO EMP(EMPNO, ENAME, DEPTNO) VALUES(6789,'홍길동',70); |
| ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found |
2. update 문
- iupdate 문은 데이터를 수정한다.
- update 문은 테이블에 저장된 데이터를 수정하기 위해서 사용한다.
update 테이블명
set column = 새로운 값
where 조건식;
- update 테이블명
update 테이블명 문은 지정한 테이블명으로 수정할 테이블을 지정한다. - set column = 새로운 값
set column = 새로운 값 문은 column 에 새로운 값을 입력한다. - where 조건식
where 조건식 문은 수정할 조건을 지정하며 조건을 충족하는 컬럼값으로 SQL 문을 제한한다.
where 문을 생략하는 경우에는 지정한 컬럼의 모든 컬럼값이 수정되므로 주의해야 한다.
where 문을 지정하면 조건식과 일치하는 컬럼값만 수정된다.
조건식은 수정할 조건에 대한 조건이나 표현식으로 ;(세미콜론)으로 종료한다.
ex ) dept 테이블의 부서번호가 50 인 부서명을 기획실로 변경한다.
▼ SELECT*FROM DEPT;
| UPDATE DEPT SET DNAME='기획실' WHERE DEPTNO = 50; -- 1 행 이(가) 삽입되었습니다. SELECT*FROM DEPT; |
3. delete문
- delete 문은 데이터를 삭제한다.
- delete 문은 테이블에 저장된 데이터를 삭제하기 위해서 사용한다.
delete from 테이블명
where 조건식;
- delete from 테이블명
delete from 테이블명 문은 지정한 테이블명으로 삭제할 테이블을 지정한다. - where 조건식
where 조건식 문은 삭제할 조건을 지정하며 조건을 충족하는 컬럼값으로 SQL 문을 제한한다.
where 문으로 조건을 주어 조건에 해당하는 컬럼만 삭제할 수 있다.
where 문을 생략하는 경우에는 지정한 컬럼의 모든 컬럼값이 삭제되므로 주의해야 한다.
where 문을 지정하면 조건식과 일치하는 컬럼값만 삭제된다.
조건식은 삭제할 조건에 대한 조건이나 표현식으로 ;(세미콜론)으로 종료한다.
ex 1 ) dept 테이블의 사원번호가 50 인 컬럼값을 삭제한다.
▼ SELECT*FROM DEPT;
| DELETE FROM DEPT WHERE DEPTNO = 50; --1 행 이(가) 삭제되었습니다. SELECT * FROM DEPT; |
ex 2 ) 외래키가 존재하는 부모 테이블을 삭제하면 오류가 발생한다
| DELETE FROM DEPT WHERE DEPTNO = 30; |
| ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found |
SQL 문의 형태
[데이터 처리어]
- TCL(Transaction Control Language)은 데이터 처리어를 의미한다.
- 데이터 처리어는 데이터의 작업처리인 트랜잭션에 적용한다.
- 트랜잭션(transaction)은 데이터베이스 시스템에서 사용되는 작업처리의 최소 단위이다.
- 트랜잭션은 데이터베이스 작업처리를 모두 일관되게 하려고 존재한다.
- 트랜잭션은 데이터 일관성을 제공하므로 만약 두 가지의 데이터베이스에 명령을 주었을 때 첫 번째 명령의 처리는 올바르게 되었지만 두 번째 명령의 처리가 올바르지 못하다면 일관성이 유지되지 않으므로 두 가지 명령 중 하나라도 실패하면 원점으로 되돌린다.
- 하나에서 여러 개의 데이터 조작어를 실행해야 한다면 반드시 트랜잭션 처리가 되어야 한다.
- 트랜잭션은 여러 개의 데이터 조작어가 하나의 논리적인 실행 단위로 묶어서 단위로 묶인 데이터 조작어 중에 하나라도 정상적으로 수행되지 않고 실패하면 전체를 취소하는 기능이다.
- 트랜잭션 이용하면 하나의 트랜잭션으로 묶인 작업을 전부 실행되든지 전부 취소되게 처리할 수 있다.
- 데이터베이스 작업은 초기 상태에서 프로세스에 의해서 트랜잭션으로 각 데이터베이스 작업을 처리하게 된다.
- 데이터베이스 작업이 오류 없이 모두 잘 작동된다면 커밋을 수행하고 트랜잭션 처리가 마무리되지만, 여러 데이터베이스 작업 중 하나의 문제라도 발생하게 되면 롤백으로 돌아가고 초기 상태 이후에 처리한 데이터베이스 작업은 모두 무효 처리된다.
- 프로세스 단계에서 처리되는 작업은 커밋이 실행되지 않는 한 데이터베이스에 업데이트 작업이 이루어지지 않는다.
- 트랜잭션의 명령에는 크게 처리한 작업을 모두 반영하도록 하는 커밋(commit)과 처리한 작업을 모두 되돌리는 롤백(rollback)이 있다.
- 묵시적인 트랜잭션은 비정상적인 방법으로 종료되면 자동으로 롤백으로 트랜잭션이 종료되고 정상적인 방법으로 종료하면 커밋으로 트랜잭션이 종료된다.
1. commit 문
commit;
- commit 문은 트랜잭션을 저장하며 ;(세미콜론)으로 종료한다.
- commit 문으로 트랜잭션 작업을 완료 시키면 실제적인 물리적 디스크에 저장한다.
- 외부 응용 프로그램에서 데이터를 입력할 때 commit 문으로 저장하지 않으면 실제 데이터가 저장되지 않는다.
ex ) 입력 작업처리를 완료한다.
▼ SELECT*FROM DEPT;
| INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(50,'인사과','부산'); --1 행 이(가) 삽입되었습니다. COMMIT; --커밋 완료. SELECT*FROM DEPT; |
2. rollback문
rollback;
- rollback 문은 트랜잭션을 취소한다.
- rollback 문으로 트랜잭션 작업을 취소시키면 실제적인 물리적 디스크에서 정보를 취소한다.
ex ) 삭제 작업처리를 취소한다.
| DELETE FROM EMP; --12개 행 이(가) 삭제되었습니다. SELECT*FROM EMP; ▼ |
| ROLLBACK; --롤백 완료. SELECT*FROM EMP; ▼ |
3. rollback문
savepoint 세이브포인트명;
- savepoint 문은 트랜잭션 내의 책갈피 기능을 하고 ;(세미콜론)으로 종료한다.
- savepoint 문으로 현재 트랜잭션 내에 저장 지점을 만든다.
ex ) 저장 지점만 작업처리를 취소한다
▼ SELECT*FROM DEPT;
| INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(60, '총무과', '광주'); --1 행 이(가) 삽입되었습니다. ▼ SELECT*FROM DEPT; |
| SAVEPOINT A; --Savepoint이(가) 생성되었습니다. INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(70, '개발과', '대구'); --1 행 이(가) 삽입되었습니다. ▼ SELECT*FROM DEPT; |
| SAVEPOINT B; --Savepoint이(가) 생성되었습니다. DELETE FROM DEPT WHERE DEPTNO = 60 OR DEPTNO = 70; --2개 행 이(가) 삭제되었습니다. SELECT*FROM DEPT; |
| ROLLBACK TO SAVEPOINT A; --롤백 완료. SELECT * FROM DEPT; |
SQL 문의 형태
[데이터 제어어]
- DCL(Data Control Language)은 데이터 제어어를 의미한다.
- 데이터 제어어는 최고관리자 계정인 sys 계정에서 권한을 부여하고 삭제하는 역할을 하면 DBA 가 담당한다.
- 데이터베이스를 접속하기 위해서는 계정과 암호가 필요하다.
- 계정에 접속한 후에 테이블을 생성하고 데이터를 조회, 입력, 수정, 삭제 등을 하기 위해서는 특별한 권한이 필요하다.
- 권한의 종류도 시스템 권한과 객체 권한으로 나누어진다.
- 여러 개의 권한을 묶어서 롤(role)이라는 개념으로 사용한다.
- 개별적인 권한보다 롤을 사용하는 것이 더 효율적이며 새로운 계정과 권한 부여는 sys 계정에서만 가능하다.
1. 계정의 관리
(1) 계정생성
최고관리자 sys 계정에서 create user 문으로 새로운 계정을 생성한다.
create user 계정 identified by 비밀번호;
- create user 계정
create user 문으로 계정을 생성한다. - identified by 비밀번호;
identified by 문으로 비밀번호 생성하고 ;(세미콜론)으로 종료한다.
ex ) 최고관리자 SYS 계정으로 접속하여 test 계정과 생성한 test 계정의 비밀번호를 생성한다.
| CREATE USER TEST IDENTIFIED BY 12345; --User TEST이(가) 생성되었습니다. |
(2) 계정변경
최고관리자 sys 계정에서 alter user 문으로 계정을 변경한다.
alter user 계정 identified by 새로운 비밀번호;
- alter user 계정\
alter user 문으로 계정을 변경한다 - identified by 새로운 비밀번호;
identified by 문으로 비밀번호 생성하고 ;(세미콜론)으로 종료한다.
ex ) 최고관리자 SYS 계정으로 접속하여 test 계정의 비밀번호를 변경한다.
| ALTER USER TEST IDENTIFIED BY 1234; --User TEST이(가) 변경되었습니다. |
identified by 새로운 비밀번호
(3) 계정삭제
최고관리자 sys 계정에서 drop user 문으로 계정을 삭제한다.
drop user 계정 cascade;
- drop user 계정
drop user 문으로 계정을 삭제한다. - cascade 명령어
cascade 명령어로 기존 계정을 완전히 삭제하고 ;(세미콜론)으로 종료한다
ex ) 최고관리자 SYS 계정으로 접속하여 test 계정을 삭제한다.
| DROP USER TEST CASCADE; --User TEST이(가) 삭제되었습니다. |
2. 계정의 권한
(1) 권한 부여
- 최고관리자 sys 계정에서 grant create 문으로 계정에 권한을 부여한다.
- grant create 문으로 권한과 롤을 설정하고 to 명령어로 계정에 부여한다.
grant create 권한 및 롤 to 계정;
- grant create 권한 및 롤
grant create 문으로 권한 및 롤을 부여한다.
롤은 사용자에게 허가할 수 있는 권한들의 집합이다. - to 계정;
to 명령어로 권한을 부여할 계정을 지정하고 ;(세미콜론)으로 종료한다.
ex 1 ) 최고관리자 SYS 계정으로 접속하여 test 계정에 접속 권한을 부여한다.
#1 삭제했던 TEST 계정 생성하기
| CREATE USER TEST IDENTIFIED BY 12345; --User TEST이(가) 생성되었습니다. |
#2 TEST 계정에 접속 권한 부여
| GRANT CREATE SESSION TO TEST; --Grant을(를) 성공했습니다. |
ex 2 ) 최고관리자 SYS 계정으로 접속하여 test 계정에 테이블 관련 권한을 부여한다.
| GRANT CREATE TABLE TO TEST; --Grant을(를) 성공했습니다. |
ex 3 ) 최고관리자 SYS 계정으로 접속하여 test 계정에 시퀀스 권한을 부여한다.
| GRANT CREATE SEQUENCE TO TEST; --Grant을(를) 성공했습니다. |
ex 4 ) 최고관리자 SYS 계정으로 접속하여 test 계정에 뷰 생성 권한을 부여한다.
| GRANT CREATE VIEW TO TEST; --Grant을(를) 성공했습니다. |
ex 5 ) 최고관리자 SYS 계정으로 접속하여 test 계정에 접속과 자원에 관한 롤을 부여한다.
| GRANT CONNECT, RESOURCE TO TEST; --Grant을(를) 성공했습니다. |
(2) 권한 삭제
- 최고관리자 sys 계정에서 revoke create 문으로 계정에 권한을 삭제한다.
- revoke create 문으로 권한과 롤을 설정하고 from 명령어로 계정에 부여한다.
revoke create 권한 및 롤 from 계정;
- revoke create 권한 및 롤
revoke create 문으로 권한 및 롤을 삭제한다.
롤은 사용자에게 허가할 수 있는 권한들의 집합이다. - from계정;
from명령어로 권한을 삭제할 계정을 지정하고 ;(세미콜론)으로 종료한다.
ex ) 최고관리자 SYS 계정으로 접속하여 시퀀스 권한을 삭제한다.
| REVOKE CREATE SEQUENCE FROM TEST; --Revoke을(를) 성공했습니다. |