SQL-DDL, DML, Constraints
DDL
- Data Definition Language : (객체)데이터 정의어
- create(생성), drop(삭제), alter, rename, truncate
- AUTO-COMMIT기능이 있음.
테이블 생성-CREATE
- 테이블을 생성하기위해서는 최소 1개 이상의 컬럼이 정의되어야함.
- 이미 존재하는 테이블명(객체명)과 같은 이름은 생성불가
- 부적절한 테이블명 : 첫글자 숫자, 예약어, 특수문자 전부 불가
- 만약 2개 이상의 컬럼을 정의할 시, 그 구분을 위해 콤마를 반드시 사용해아함.
- 형식
CREATE TABLE EM2P(EMPNO NUMBER(4)); Table created. CREATE TABLE EMP3(EMPNO NUMBER(4), ENAME VARCHAR2(15), SAL NUMBER(7,2)); Table created.
서브쿼리를 이용한 테이블 생성
- 기존 테이블의 구조(컬럼,자료형)와 데이터를 복사
-
제약(constraint)은 복사에서 제외!!
- 형식
CREATE TABLE EMP4 AS SELECT * FROM EMP; Table created. DESC EMP4; Name Null? Type ----------------------------------------- -------- ------------ EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SELECT EMPNO, ENAME FROM EMP4 WHERE DEPTNO=10; EMPNO ENAME ---------- -------------------- 7782 CLARK 7839 KING 7934 MILLER SELECT COUNT(*) FROM EMP4; COUNT(*) ---------- 14
서브쿼리를 통한 테이블 생성: 원하는 컬럼과 행만 복사
CREATE TABLE EMP5 AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO=30;
Table created.
SELECT * FROM EMP5;
EMPNO ENAME SAL
---------- -------------------- ----------
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7698 BLAKE 2850
7844 TURNER 1500
7900 JAMES 950
서브쿼리를 통한 테이블 생성: 구조(컬럼,자료형)만 복사, 데이터 복사X
CREATE TABLE EMP6 AS SELECT * FROM EMP WHERE 1=0;
Table created.
SELECT * FROM EMP6;
no rows selected
DESC EMP6;
Name Null? Type
----------------------------------------- -------- ------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
테이블의 구조 변경하기 - ALTER
- 컬럼추가, 컬럼삭제, 컬럼의 자료형, 바이트 수를 변경.
- EMP6테이블에 속성컬럼 EMAIL을 추가!!
ALTER TABLE EMP6 ADD (EMAIL VARCHAR2(20)); Table altered. DESC EMP6; Name Null? Type ----------------------------------------- -------- ---------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) EMAIL VARCHAR2(20) - 행 추가
INSERT INTO EMP6 (EMPNO, ENAME, EMAIL) VALUES(8000,'박명수','QQDDDDDD@NAVER.COM'); 1 row created. - 컬럼속성 변경
ALTER TABLE EMP6 MODIFY (EMAIL VARCHAR2(30)); Table altered.
테이블 객체 삭제-DROP
select tname, tabtype from tab;
TNAME TABTYPE
------------------------------------------------------------ ---------
DEPT TABLE
EM2P TABLE
EMP TABLE
EMP3 TABLE
EMP4 TABLE
EMP5 TABLE
EMP6 TABLE
SALGRADE TABLE
DROP TABLE EM2P;
Table dropped.
DROP TABLE EMP3;
Table dropped.
DROP TABLE EMP4;
Table dropped.
DROP TABLE EMP5;
Table dropped.
DROP TABLE EMP6;
Table dropped.
select tname, tabtype from tab;
TNAME TABTYPE
------------------------------------------------------------ ---------
DEPT TABLE
EMP TABLE
SALGRADE TABLE
테이블객체 이름 변경-RENAME
- 형식) RENAME old_객체명 TO new_객체명; RENAME EMP2 TO EMPLOYEE; Table renamed.
테이블의 전체 데이터 지우기-TRUNCATE
- 형식) TRUNCATE TABLE 테이블명;
CREATE TABLE EMP3 AS SELECT*FROM EMP; Table created. SELECT COUNT(*) FROM EMP3; COUNT(*) ---------- 14 TRUNCATE TABLE EMP3; Table truncated. SELECT COUNT(*) FROM EMP3; COUNT(*) ---------- 0 - TRUNCATE → 삭제데이터 복구 불가능
- COMMIT → 데이터베이스의 실제적, 물리적인 반영을 해주는 애
- DDL은 AUTO-COMMIT이 들어가있음.
DML
- Data Manipulation Language: 데이터 조작어
- INSERT, DELETE, UPDATE
INSERT
- 테이블에 (행)데이터를 추가(최초입력)!!
- 만약, 테이블명 뒤에 컬럼명을 생략하는 경우 테이블 구조에 있는 순서에 따라 전체 컬럼명이 명시된 것으로 간주.
- 명시된 컬럼의 갯수와 데이터 갯수는 일치.
- 컬럼의 순서에 맞는 자료형 데이터가 와야 함.
- 형식) INSERT INTO 테이블명 (컬럼명1, 컬럼명2, 컬럼명3) VALUES (데이터1, 데이터2, 데이터3);
CREATE TABLE DEPT2 AS SELECT *FROM DEPT;
Table created.
SELECT* FROM DEPT2;
DEPTNO DNAME LOC
---------- ---------------------------- ---------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
INSERT INTO DEPT2 (DEPTNO, DNAME, LOC) VALUES (50, 'DEVELOP', 'SEOUL');
SELECT* FROM DEPT2;
DEPTNO DNAME LOC
---------- ---------------------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DEVELOP SEOUL
NULL값 입력방법
- 컬럼명을 명시하지않는다.
INSERT INTO DEPT3 (DEPTNO, DNAME) VALUES (50, '개발부'); - 데이터가 들어가는 자리에 직접 NULL을 명시.
INSERT INTO DEPT3 VALUES (50, '개발부', NULL); - 데이터가 들어가는 자리에 ‘ ‘를 입력. ‘ ‘는 SQL에서 NULL로 인식한다.
INSERT INTO DEPT3 (DEPTNO, DNAME, LOC) VALUES (70, '총무부', ' ');
서브쿼리로 행 추가
- 형식 : INSERT INTO 테이블명 (컬럼명1, 컬럼명2, 컬럼명3) 서브쿼리;
- VALUES 들어갈 자리에 서브쿼리가 들어간 형식.
INSERT INTO DEPT5 SELECT* FROM DEPT3; - 문제) DEPT6테이블을 생성하고 구조만 따온다. 그 후 DEPT테이블의 20, 40번 부서의 정보(번호,이름)을 서브쿼리로 추가
CREATE TABLE DEPT6 AS SELECT * FROM DEPT WHERE 1=0; Table created. INSERT INTO DEPT6 (DEPTNO, DNAME) SELECT DEPTNO, DNAME FROM DEPT WHERE DEPTNO IN (20,40); 2 rows created. SELECT * FROM DEPT6; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 20 RESEARCH 40 OPERATIONS - 여기까지 가장 중요한건 CREATE, INSERT. 이거 두 가지 구문만은 꼭 암기!! ★★★
- 각각을 서브쿼리 이용해서 선언하는 것도 매우 중요★★
UPDATE
- 기존 데이터에 대한 수정,갱신, 재입력
-
형식) UPDATE 테이블명 SET 컬럼명1=(변경할)데이터, 컬럼명2=(변경할)데이터 [WHERE 조건식];
- ※ 주의: UPDATE와 DELETE문을 실행시에는 반드시 WHERE절 사용을 고려하자!!
-
왜? WHERE절을 사용하지 않는 다면 전체행에 대한 수정 또는 삭제가 실행되기 때문!!
- 문제) 10번 부서의 위치를 ‘서울’로 변경하시오.
CREATE TABLE DEPT2 AS SELECT * FROM DEPT; Table created. UPDATE DEPT2 SET LOC = 'SEOUL' WHERE DEPTNO=10; 7 rows selected. SELECT * FROM DEPT2; DEPTNO DNAME LOC ---------- ---------------------------- ------------ 10 ACCOUNTING SEOUL 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON - 문제) 30번 부서의 급여를 10% 인상해라!!
CREATE TABLE EMP2 AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP; Table created. UPDATE EMP2 SET SAL = SAL*1.1 WHERE DEPTNO=30; 6 rows updated. SELECT * FROM EMP2; EMPNO ENAME SAL DEPTNO ---------- -------------------- ---------- ---------- 7369 SMITH 800 20 7499 ALLEN 1760 30 7521 WARD 1375 30 7566 JONES 2975 20 7654 MARTIN 1375 30 7698 BLAKE 3135 30 7782 CLARK 2450 10 7788 SCOTT 3000 20 7839 KING 5000 10 7844 TURNER 1650 30 7876 ADAMS 1100 20 7900 JAMES 1045 30 7902 FORD 3000 20 7934 MILLER 1300 10 -
서브쿼리를 이용한 UPDATE
- 문제) 부서번호 20번인 부서의 이름과 지역을 40번 부서와 동일하게 변경하시오!!
CREATE TABLE DEPT3 AS SELECT * FROM DEPT; Table created. UPDATE DEPT3 SET DNAME = (SELECT DNAME FROM DEPT3 WHERE DEPTNO=40), LOC = (SELECT LOC FROM DEPT3 WHERE DEPTNO=40) WHERE DEPTNO = 20; UPDATE DEPT3 SET (DNAME, LOC) = (SELECT DNAME, LOC FROM DEPT3 WHERE DEPTNO=40) WHERE DEPTNO = 20; --이렇게 둘 다 가능 1 row updated. SELECT * FROM DEPT3; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 OPERATIONS BOSTON 30 SALES CHICAGO 40 OPERATIONS BOSTON
DELETE
- (특정)행 삭제 명령어
-
형식) DELETE FROM 테이블명 – 전체 행 삭제 [WHERE 조건식]; – 특정 행 삭제
- 문제) 전체 부서를 삭제하시오.
DELETE FROM DEPT4; - 문제) dept5테이블을 유지한 상태에서 dept테이블의 데이터들을 복사(입력)하시오.
INSERT INTO DEPT3 SELECT * FROM DEPT; - 문제) 20번 부서를 삭제하시오.
DELETE FROM DEPT3 WHERE DEPTNO=20; 1 row deleted. SELECT * FROM DEPT3; DEPTNO DNAME LOC ---------- ---------------------------- ----------- 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 40 OPERATIONS BOSTON - 문제) SALES부서에 속한 사원들을 사원테이블(emp4)에서 삭제하시오.
DELETE FROM EMP4 WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES'); 6 rows deleted. SELECT * FROM EMP4; EMPNO ENAME DEPTNO ---------- -------------------- ---------- 7369 SMITH 20 7566 JONES 20 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7876 ADAMS 20 7902 FORD 20 7934 MILLER 10
MERGE
- 합병(병합): 구조가 같은 두 개의 테이블을 하나의 테이블로 합치는 기능.
- 기존에 존재하는 행이 있다면 새로운 값으로 갱신(update)되고
-
존재하지 않는 행이 있다면 새로운 행으로 추가(insert)해라!!
- 형식)
MERGE INTO 기준테이블명 별명
USING 참조테이블명 별명
ON (매칭 조건식)
WHEN MATCHED -- 매칭되는 행이 있다면
THEN 업데이트 문
WHEN NOT MATCHED -- 매칭되는 행이 아니라면
THEN 추가문;
create table emp11 -- 기준 테이블 (병합된 결과를 반영)
as select * from emp;
create table emp12 -- 참조 테이블
as select * from emp
where job='MANAGER';
update emp12 set job='사원';
insert into emp12 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (8000,'나길동','사원',7788, sysdate, 2000, null, 30);
SQL> select empno, ename, job, deptno from emp12;
EMPNO ENAME JOB DEPTNO
---------- -------------------- ------------------ ----------
7566 JONES 사원 20
7698 BLAKE 사원 30
7782 CLARK 사원 10
8000 나길동 사원 30
MERGE INTO emp11 e1 -- 기준테이블(병합결과 반영)
USING emp12 e2 -- 참조테이블
ON (e1.empno = e2.empno)
WHEN MATCHED -- 매칭되는 행이 있다면
THEN -- 변경문
update set --이미 e1 기준테이블이 정의 되어있으므로 테이블명을 생략
e1.ename=e2.ename,
e1.job=e2.job,
e1.mgr=e2.mgr,
e1.sal=e2.sal,
e1.comm=e2.comm,
e1.deptno=e2.deptno
WHEN NOT MATCHED -- 매칭되는 행이 아니라면
THEN --추가문
insert values (e2.empno, e2.ename, e2.job, e2.mgr,
e2.hiredate, e2.sal, e2.comm, e2.deptno);
===> 4 rows merged.(4행 병합되었습니다!!)
트랜잭션
- 데이터 처리의 한 단위
- 오라클에서 발생하는 여러개의 SQL 명령문(DML)들을 하나의 논리적인 작업단위로 처리하는데 이를 트랜잭션이라 함.
- 하나의 트랜잭션은 All-or-Nothing방식으로 처리.
- 목적 : 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구시키기 위해서.
- 트랜잭션 제어 명령어 : commit, rollback, savepoint
- COMMIT
- 저장되지 않은 모든 데이터를 데이터베이스에 저장하고 현재의 트랜잭션을 종료하라는 명령어.
- 커밋하면 트랜잭션의 처리과정이 모두 반영되며 하나의 트랜잭션 과정이 끝남.
- 트랜잭션이 발생하는 경우 새롭게 생성되거나 갱신된 데이터들이 물리적으로 영구히 저장됨.
- DDL(create,drop,alter,rename,truncate)은 AutoCommit!!
- 정상적인 종료시에도 commit작업을 수행 예) exit종료
2, ROLLBACK [TO SAVEPOINT명]
- 저장되지 않은 모든 데이터 변경사항(DML)을 취소하고 현재의 트랜잭션을 끝내라는 명령.
- 트랜잭션으로 인한 하나의 묶음처리가 시작되기 이전의 상태로 되돌려지는 것.
- 이전상태로 되돌아가 지금까지 수행했던 데이터베이스의 변경을 모두 무효화.
- 비정상적인 종료시 rollback작업을 수행. 예) 우측 상단 X버튼 클릭시
Enter user-name: scott
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
=================================================================> 트랜젝션 시작
DROP TABLE DEPT3;
Table dropped.
=================================================================> 트랜젝션 끝, 시작
CREATE TABLE DEPT3 AS SELECT * FROM DEPT;
Table created.
=================================================================> 트랜젝션 끝, 시작
DELETE FROM DEPT3 WHERE DEPTNO IN (20,40);
2 rows deleted.
SELECT * FROM DEPT3;
DEPTNO DNAME LOC
---------- ---------------------------- ---------------
10 ACCOUNTING NEW YORK
30 SALES CHICAGO
UPDATE DEPT3 SET DNAME = '영업부' WHERE DEPTNO=30;
1 row updated.
SELECT * FROM DEPT3;
DEPTNO DNAME LOC
---------- ---------------------------- ---------------
10 ACCOUNTING NEW YORK
30 영업부 CHICAGO
ROLLBACK; -- 트랜젝션 범위 내의 전체 작업(DML) 취소 (13~30번줄)
Rollback complete.
SELECT * FROM DEPT3;
DEPTNO DNAME LOC
---------- ---------------------------- ---------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
무결성 제약조건
- 테이블에 부적절한 자료가 입력(insert,update)되는 것을 방지하기 위해서 테이블을 생성할 때 각 컬럼에 대해서 정의 하는 여러가지 규칙.
- NOT NULL
- 해당 컬럼값으로 NULL을 허용하지 않음
- 입력시 데이터를 무조건 받겠다!!
- INSURT, UPDATE 시에 NULL입력 또는 NULL수정을 허용하지 않겠음.
- 중복된 데이터를 입력방지하는건 할 수가 없다. 그래서 나온 게 UNIQUE
- UNIQUE
- 테이블 내에서 해당 컬럼값은 항상 유일무이한 값을 가질 것.
- 중복허용하지 않겠다!!
- PRIMARY KEY(기본키)
- not null+unique 동시에 만족
- 해당컬럼값은 반드시 존재해야 하고 딱 하나만 존재해야함. 2개부터는 에러남.
- 테이블내에서 서로 다른 행을 구분하기 위해서 사용!!
- FOREIGN KEY (외래키,참조키)
- 해당컬럼의 값이 타컬럼의 값을 참조해야만 함
- 즉, 참조되는 컬럼에 없는 값은 입력 불가. - 참조하고자 하는 테이블에서 설정(예: 사원테이블의 deptno에 참조키 설정
- 참조되는 쪽부서테이블)의 컬럼은 반드시 unique하거나 또는 primary키 여야함!!
- CHECK
- 해당 컬럼에 저장 가능한 데이터 값의 범위나 사용자 조건을 지정.
- age컬럼: (1~100)
- gender컬럼: (남성,여성)
제약설정하기 - 3가지 방법
- 컬럼레벨 설정
create table 테이블명 ( 컬럼명 자료형 [constraint 제약명] constraint_type , ); - 테이블레벨 설정
create table 테이블명 ( 컬럼명1 자료형, 컬럼명2 자료형, 컬럼명3 자료형, [constraint 제약명] constraint_type (적용할 컬럼명) ); - 테이블생성후 제약추가 (없는 제약을 있게 만들어 줌.) 제일 깔끔
alter table 테이블명 ADD [constraint 제약명] constraint_type (적용할 컬럼명)
- 제약삭제
alter table 테이블명 DROP constraint 제약명; alter table emp4 DROP primary key; - 제약수정 (null <—-> not null)
alter table 테이블명 modify 컬럼명 [constraint 제약명] NOT NULL | NULL;foreign key 제약
- 존재하지 않는 데이터 입력 또는 수정 방지!! 3가지 방법
- 컬럼레벨
create table 테이블명emp ( 컬럼명 자료형 [constraint 제약명] REFERENCES 참조테이블명(참조컬럼명) deptno number(2) REFERENCES dept(deptno) ); - 테이블레벨
create table 테이블명emp ( 컬럼명1 자료형, 컬럼명2 자료형, 컬럼명3 자료형, [constraint 제약명] FOREIGN KEY (컬럼명2) REFERENCES 참조테이블명(참조컬럼명) ----> foreign key (deptno) references dept(deptno) ); - 테이블 변경
alter table 테이블명 ADD [constraint 제약명] foreign key (deptno) references dept(deptno);
데이터 딕셔너리 - DD 시스템테이블
- 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블.
- 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할때 데이터베이스서버에 의해 자동으로 갱신되는 테이블.
제약조건 확인하기
- user_constraints
- desc user_constraints
OWNER : 제약조건을 소유한 사용자명
CONSTRAINT_NAME : 제약조건명
CONSTRAINT_TYPE : 제약조건타입
TABLE_NAME : 제약이 속한 테이블명
SEARCH_CONDITION : check제약일 경우 조건으로 사용되어지는 설명.
R_OWNER
R_CONSTRAINT_NAME : foreign key경우 어떤 primary key를 참조했는지 정보.
<CONSTRAINT_TYPE>
P : primary key
R : foreign key
U : unique
C : check, not null
CHECK제약
- 특정 범위내의 데이터만 입력
-
정해진 데이터만 입력 형식) CHECK (조건식)
- 문제) emp3테이블 생성
- 사원번호: primary key
- 사원명 : 기본값 ‘무명’ 지정 - [default 데이터] 사용
- 급여: 최소 500 , 최대 1000 - [check (조건식)] 사용
- 매니저 : 반드시 입력
- 부서번호: 부서테이블의 부서번호 참조 , foreign key사용
- 성별: ‘M’ 또는 ‘F’ 입력 체크 - [check (조건식)] 사용
최종정리!!!!!
CREATE TABLE EMP3 (
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(20) DEFAULT 'NONAME',
SAL NUMBER (7,2) CHECK (SAL BETWEEN 500 AND 1000),
MGR NUMBER(4) NOT NULL,
DEPTNO NUMBER(2) REFERENCES DEPT3(DEPTNO),
GENDER CHAR(1) CHECK (GENDER IN ('M','F'))
);
댓글남기기