[OracleDB] SQL 종류 (DDL.DML.DCL)

 

 

DDL	- Data Definition Language - 데이터베이스 개체를 생성/삭제/변경하는 역할 - CREATE, DROP, ALTER...
DML	- Data Manipulation Language - 데이터를 조작(선택, 삽입, 수정, 삭제) 하는데 사용되는 언어 - SELECT, INSERT, UPDATE, DELETE ...
DCL	- Data Control Language - 권한부여/철회에 사용되는 구문 - GRANT/REVOKE/DENY

 

 

 

DDL

1. 테이블 컬럼 추가 (ALTER TABLE ADD)

 

//ALTER TABLE 테이블명 ADD(컬럼명 데이터타입(사이즈));

//USER 테이블에 USER_NAME 컬럼을 VARCHAR 타입으로 추가

ALTER TABLE USER ADD(USER_NAME VARCHAR(13));

 

2. 테이블 컬럼 수정 ( ALTER TABLE MODIFY)

 

//ALTER TABLE 테이블명 MODIFY(컬럼명 데이터타입(사이즈));

//USER테이블에 USER_AGE 컬럼을 NUMBER(3) 타입으로 수정

ALTER TABLE USER MODIFY(USER_AGE NUMBER(3));

 

3. 테이블 컬럼 삭제 ( ALTER TABLE DROP)

 

//ALTER TABLE 테이블명 DROP COLUMN 컬럼명

//USER테이블에 USER_NAME 컬럼 삭제

ALTER TABLE USER DROP COLUMN USER_NAME;

 

4. 테이블 컬럼 이름 변경 ( ALTER TABLE RENAME)

 

//ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명;

//USER테이블에 USER_NAME 컬럼을 USER_FIRST_NAME 으로 변경

ALTER TABLE USER RENAME COLUMN USER_NAME TO USER_FIRST_NAME;

 

DML

 

USER_TABLE 생성

 

CREATE TABLE userTbl( // 회원 테이블
userID CHAR(10) NOT NULL PRIMARY KEY, // 사용자 아이디
name NVARCHAR(10) NOT NULL,           // 이름
birthYear INT NOT NULL,               // 출생년도
addr NCHAR(2) NOT NULL,               // 지역(2글자 입력.. 경남, 서울, 경기...)
mobile1 CHAR(3)                       // 휴대폰의 국번
mobile2 CHAR(8)                       // 휴대폰의 나머지 전화번호
height SMALLINT                       // 키
mDate DATE                            // 회원가입 일
);

 

 

 

BUY_TABLE 생성

 

CREATE TABLE buyTbl(              // 회원 구매 테이블
num INT NOT NULL PRIMARY KEY,     // 순번 (Primary key)
userID CHAR(8) NOT NULL,       
prodName CHAR(6) NOT NULL,        // 물품명
groupName CHAR(4)                 // 분류
price INT NOT NULL,               // 단가
amount SMALLINT NOT NULL,         // 수량
FOREIGN KEY (userID) REFERENCES userTbl(userID) 
);

 

 

USERTABLE 값 삽입

INSERT INTO userTbl VALUES('LSG','이승기',1987,'서울','011','1111111',182.'2008-8-8');
INSERT INTO userTbl VALUES('KBS','김범수',1979,'경남','011','2222222',173.'2012-4-4');
INSERT INTO userTbl VALUES('KKH','김경호',1971,'전남','019','3333333',177.'2007-7-7');
INSERT INTO userTbl VALUES('JYP','조용필',1950,'경기','011','4444444',166.'2009-4-4');
INSERT INTO userTbl VALUES('SSK','성시경',1979,'서울',NULL,NULL,182,'2009-9-9');

 

BUYTABLE 값 삽입

 

INSERT INTO buyTbl VALUES(1,'KBS','운동화',NULL,30,2);
INSERT INTO buyTbl VALUES(2,'KBS','노트북','전자',1000,1);
INSERT INTO buyTbl VALUES(3,'JYP','모니터','전자',200,1);
INSERT INTO buyTbl VALUES(4,'BBK','모니터','전자',200,5);
INSERT INTO buyTbl VALUES(5,'KBS','청바지','의류',50,3);
INSERT INTO buyTbl VALUES(6,'BBK','메모리','전자',80,10);
INSERT INTO buyTbl VALUES(7,'SSK','책','서적',15,5);
INSERT INTO buyTbl VALUES(8,'EJW','책','서적',15,2);

 

USE / SELECT

테이블 조회하기

 

OracleDB에서 데이블 목록을 조회할 때는 오라클에서 제공하는 3가지 시스템 뷰를 사용한다.

3가지 시스템 뷰는 all_tables,all_tab_comments,all_objects이며, 접근 권한에 따라서 접두어(all)를 바꿔서 사용할 수 있다.

 

테이블 목록 조회 3가지 방법

SELECT * 
  FROM all_tables
 
// all_tables는 권한을 가진 모든 테이블이 조회되며 통계 정보를 쉽게 확인할 수 있다.




SELECT * 
  FROM all_tab_comments
 WHERE table_type = 'TABLE'
 
// all_tab_comments는 권한을 가진 모든 테이블 목록과 테이블 설명(COMMENT)을 조회할 때 사용한다




SELECT * 
  FROM all_objects
 WHERE object_type = 'TABLE'

// all_objects는 권한을 가진 모든 테이블의 최초 생성 일시와 마지막 변경 일시를 확인할 수 있다.

 

 

시스템 뷰의 접두어를 변경해서 사용 가능

 

all	all_tables all_tab_comments all_objects	사용자 계정이 접근 가능한 모든 테이블 또는 객체 조회
user	user_tables user_tab_comments user_objects	사용자 계정에 생성된 모든 테이블 또는 객체 조회
dba	dba_tables dba_tab_comments dba_objects	관리자(DBA) 권한으로 모든 테이블 또는 객체 조회

 

 

일반적으로는 all 접두어 뷰를 많이 사용하며, 관리자 권한이 있으면 dba 접두어 뷰를 사용할 수 있다.

현재 로그인한 게정에 생성된 테이블 목록만 필요하다면 user 접두어 뷰를 사용하면 된다.

 

 

all_tables 사용법

 

SELECT * FROM all_tables

 

 

 

 

all_tables 대신에 all_all_tables를 사용할 수 있다. all_all_tables 시스템 뷰를 사용하면 객체 테이블도 추가할 수 있다.

일반적인 테이블을 조회하면 all_tables를 사용하면 된다. all_tables 조회하면 table_type 칼럼 항목은 없으며

all_all_tables를 조회할 때만 table_type 칼럼이 존재하며 값이 NULL이면 일반 테이블, 값이 있으면 객체 테이블이다

 

SELECT * FROM all_all_tables

 

all_tables과 all_all_tables의 차이는 조회 시 객체 테이블의 포함 여부이다.

 

 

 

 

SELECT * FROM all_tab_comments WHERE table_type = 'TABLE'

 

 

 

all_tab_comments 시스템 뷰는 TABLE,VIEW 목록을 조회한다.

테이블만 조회 할 경우 table_type 조건을 부여해야 테이블 목록만 조회된다.

 

 

SELECT *
  FROM all_tab_comments
 WHERE table_type = 'TABLE'
   AND owner IN ('SYSTEM', 'HR')
   
   
 SELECT * 
   FROM all_tab_comments
  WHERE table_name = 'EMP'
  
// 특정 테이블의 설명(COMMENT)을 확인할 때는 table_name 조건을 부여하면 된다

 

all_objects 사용법

 

SELECT * 
  FROM all_objects
 WHERE object_type='TABLE'
 
 
 //all_objects를 조회하면 테이블의 최초 생성 일시와 마지막 수정 일시를 확인할 수 있다
 //all_objects는 테이블 외에 뷰, 프로시저, 함수, 시노님, 시퀀스 등 모든 객체를 조회할 수 있다

 

 

 

SELECT *
  FROM all_objects
  WHERE object_name LIKE '%DEPT%'
  
  
  // all_objects 뷰는 테이블 조회보다는 전체적인 객체 검색이 필요할 때 많이 사용한다

 

 

 

SELECT *
  FROM all_tables
  
// all_tables는 권한을 가진 모든 테이블이 조회되며 정보를 쉽게 확인할 수 있다.


SELECT * 
  FROM all_tab_comments
 WHERE table_type = 'TABLE'
 
// all_tab_comments는 권한을 가진 모든 테이블 목록과 테이블 설명(COMMENT)을 조회 할 때 사용한다.


SELECT *
  FROM all_objects
 WHERE object_type = 'TABLE'
 
 
//all_objects는 권한을 가진 모든 테이블의 최초 생성 일시와 마지막 변경 일시를 확인할 수 있다.

 

 

 

SELECT 옵션

 

where 조건

 

// where 조건
// SELECT * 열이름 FROM 테이블이름 WHERE 조건

select * from usertbl;
select * from usertbl where name='김경호';


// where 조건 + 관계연산자
// SELECT 열이름 FROM 테이블이름 WHERE 조건

select * from usertbl where birthyear>=1970 and height>=182;
select * from usertbl where birthyear>=1970 or height>=182;

 

 

BETWEEN...AND / IN() / LIKE

 

//BETWEEN..AND -(-에서 - 사이)
SELECT Name,height FROM userTbl WHERE height BETWEEN 180 AND 183;


//IN() - (비연속적 집합 - 문자열)
SELECT Name,height FROM userTbl WHERE addr IN('경남','전남','경북');


//LIKE - (문자열 내용 검색)
SELECT Name,height FROM userTbl WHERE name LIKE '김%';
SELECT Name,height FROM userTbl WHERE name LIKE '_재범';

 

서브쿼리

// 서브쿼리 - 쿼리 안에 쿼리
SELECT Name,height FROM userTbl
WHERE height < (SELECT height FROM userTbl WHERE Name='김경호');





// 서브쿼리 - 쿼리 안에 쿼리 - 하나 이상 값 반환 시 오류
SELECT Name, height FROM userTbl
WHERE height < (SELECT height FROM userTbl WHERE addr='경남');

 

ANY / ALL

 

// ANY - 여러 결과 중 한 가지만 만족해도 OK

SELECT Name, height FROM userTbl
WHERE height>=ANY (SELECT height FROM userTbl WHERE addr='경남');

// ALL - 모두 만족해야 OK

SELECT Name, height FROM userTbl
WHERE height >= ALL (SELECT height FROM userTbl WHERE addr='경남');

 

 

ORDER BY

 

//ORDER BY - 정렬(기본 오름차순)
SELECT Name, mDate FROM userTbl ORDER BY mDate;



//ORDER BY DESC - 내림파순 정렬
SELECT Name, mDate FROM userTbl ORDER BY mDate DESC;


//ORDER BY
SELECT Name, height FROM userTbl ORDER BY height DESC, name ASC;

 

 

테이블 복사

 

// 테이블 복사하기 스키마 & 데이터
//CREATE TABLE 새로만들테이블명 AS SELECT * FROM 복사할테이블명 [WHERE 절]

// 테이블 구조만 복사하기
//CREATE TABLE 새로만들테이블명 AS SELECT * FROM 복사할테이블명 WHERE 1=2 
//[where절에 '참'이 아닌 조건을 넣어줌]


//테이블은 이미 생성되어 있고 데이터만 복사 (테이블 구조가 동일할 때)
//INSERT INTO 복사할테이블명 SELECT * FROM 테이블명 [WHERE 절]

INSERT INTO TB_BOARD_TEMP SELECT * FROM TB_BOARD;


// 테이블은 이미 생성되어 있고 데이터만 복사 (테이블 구조가 다를 때)
// INSERT INTO 복사할테이블명 (NUM, TITLE, CONTENTS) SELECT NUM, TITLE, CONTENTS FROM 테이블명

INSERT INTO TB_BOARD_TEMP (NUM, TITLE, CONTENTS) SELECT NUM, TITLE, CONTENTS FROM TB_BOARD;