220419~20 데이터베이스 데이터타입, DDL, DML

1. 복습 문제

-- 1. employees 테이블에서 100번 부서의 최소 급여보다 
-- 최소 급여가 많은 다른 모든 부서, 최소급여를 출력하라. 
SELECT 	MIN(SALARY)
FROM 	EMPLOYEES e
WHERE 	DEPARTMENT_ID = 100
;

SELECT	department_id, min(salary)
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID 
HAVING min(SALARY) > (SELECT min(SALARY)	FROM EMPLOYEES e2 	WHERE DEPARTMENT_ID = 100)
;


-- 2. employees 테이블에서 사원 번호가 123인 사원의 직업과 같고,
-- 사원 번호가 192인 사원의 급여보다 많은 사원의 사원번호, 이름, 직업, 급여를 출력하라
SELECT 	JOB_ID 
FROM 	EMPLOYEES
WHERE 	EMPLOYEE_ID = 123
;

SELECT 	SALARY 
FROM 	EMPLOYEES e 
WHERE 	EMPLOYEE_ID = 192
;

SELECT 	EMPLOYEE_ID , FIRST_NAME , JOB_ID , SALARY 
FROM 	EMPLOYEES e 
WHERE 	JOB_ID = (SELECT JOB_ID	FROM EMPLOYEES e	WHERE EMPLOYEE_ID = 123)
AND 	SALARY > (SELECT SALARY	FROM EMPLOYEES e	WHERE EMPLOYEE_ID = 192)
;


-- 3. employees 테이블에서 50번 부서의 최소 급여를 받는 사원보다 많은 급여를 받는 사원의
-- 사원번호, 이름, 업무(job_id), 입사일자, 급여, 부서번호를 출력하라. 단, 50번 부서는 제외
SELECT 	MIN(SALARY) 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID = 50
;

SELECT 	EMPLOYEE_ID , FIRST_NAME , JOB_ID , HIRE_DATE , SALARY , DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	SALARY > (SELECT MIN(SALARY)	FROM EMPLOYEES e	WHERE DEPARTMENT_ID = 50)
AND 	DEPARTMENT_ID <> 50
;


-- 4. employees 테이블에서 50번 부서의 최대 급여를 받는 사원보다 많은 급여를 받는 사원의
-- 사원번호, 이름, 업무(job_id), 입사일자, 급여, 부서번호를 출력하라. 단, 50번 부서는 제외
SELECT 	EMPLOYEE_ID , FIRST_NAME , JOB_ID , HIRE_DATE , SALARY , DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	SALARY > (SELECT MAX(SALARY)	FROM EMPLOYEES e	WHERE DEPARTMENT_ID = 50)
AND 	DEPARTMENT_ID <> 50
;


-- 5. employees 테이블에서 가장 많은 사원이 속해 있는 부서 번호와 사원수를 출력하라.
SELECT 	MAX(COUNT(EMPLOYEE_ID)) 
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID
;

SELECT 	DEPARTMENT_ID , count(EMPLOYEE_ID)	-- 강사님은 COUNT(*)로 풀이
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID 
HAVING 	count(EMPLOYEE_ID) = (SELECT 	MAX(COUNT(EMPLOYEE_ID)) 
				FROM 	EMPLOYEES e 
				GROUP BY DEPARTMENT_ID)
;

 

 

 

2. Oracle data type

데이터 타입이란 컬럼이 저장되는 데이터 유형을 말한다. 기본 데이터 타입은 문자형, 실수, 소수, 자료형 등의 여러 데이터를 식별하는 타입이다.

 

2-1. 문자 데이터 타입

• CHAR(10) : 고정형
• VARCHAR2(10) : 가변형
  실제 입력된 데이터 길이에 따라 크기가 변한다.

CHAR와 VARCHAR2의 차이

 

2-2. 숫자형 데이터 타입

숫자 타입은 4가지 타입이 있다. 대부분 NUMBER형을 사용한다.

NUMBER(P,S) : 가변형

• P : 소수점을 포함한 전체 자리 수
• S : 소수점 자리수를 의미

P와 S에 값을 입력하지 않으면, 저장 데이터의 크기에 맞게 자동으로 조절된다.정수는 NUMBER(P) 또는 NUMBER(P, 0)으로 사용한다.

 

2-3. 날짜 데이터 타입

가장 일반적으로 쓰는 데이터타입은 DATA 타입이다.

 

2-4. LOB 데이터 타입

LOB이란 Large Object의 약자로, 대용량 데이터를 저장할 수 있는 데이터 타입

일반적으로 그래픽, 이미지, 사운드 등 비정형 데이터를 저장할 때 LOB 타입 사용

문자형 대용량 데이터는 CLOB이나 NCLOB을 사용. 그래픽, 이미지, 동영상의 데이터는 BLOB을 주로 사용한다.

 

 

 

3. 데이터정의어 DDL(Data Definition Language)

데이터의 구조를 정의하기 위한 테이블 생성, 삭제 같은 명령어

• CREATE : 테이블 생성
• DROP : 테이블 삭제
• ALTER : 테이블 수정
• TRUNCATE : 테이블에 있는 모든 데이터 삭제

 

 

 

4. 데이터조작어 DML(Data Manipulation Language)

데이터의 구조를 정의하기 위한 테이블 생성, 삭제 같은 명령어

• SELECT : 데이터 조회
• INSERT : 데이터 입력
• UPDATE : 데이터 수정
• DELETE : 데이터 삭제

 

4-1. INSERT

• 전체 컬럼에 데이터 넣기
  INSERT INTO 테이블명 VALUES (값1, 값2, ...) ;
• 특정 컬럼에 데이터 넣기
  INSERT INTO 테이블명(컬럼1, 컬럼2, ...) VALUES (값1, 값2, ...) ;
  

 

4-2. UPDATE

• UPDATE 테이블명 SET 컬럼1=값, 컬럼2=값, ... 
  ★WHERE 조건 ;

 

4-3. DELETE

• DELETE 테이블명
  ★WHERE 조건 ;

 

 

 

5. 예제

-- TABLE 생성
CREATE TABLE EMPLOYEES2(
	EMPLOYEE_ID	NUMBER(10),
	NAME		VARCHAR2(20),
	SALARY		NUMBER(7,2)
);					

-- 기존 테이블과 동일하게 작성
CREATE TABLE EMPLOYEES3
AS
SELECT * FROM EMPLOYEES e ;

-- 컬럼 추가
ALTER TABLE EMPLOYEES2 ADD(
	MANAGER_ID	VARCHAR2(20)
);

-- 컬럼 수정
ALTER TABLE EMPLOYEES2 MODIFY(
	MANAGER_ID	VARCHAR2(30)
);

-- 컬럼 삭제
ALTER TABLE EMPLOYEES2 DROP(
	MANAGER_ID
);

-- 테이블 삭제
DROP TABLE EMPLOYEES3 ;

SELECT * FROM EMPLOYEES2 e ;

-- 데이터 입력
INSERT INTO EMPLOYEES2 VALUES (1, '직원1', 30000);
INSERT INTO EMPLOYEES2 VALUES (2, '직원2', 3000);
INSERT INTO EMPLOYEES2 VALUES (3, '직원3', 50000);
INSERT INTO EMPLOYEES2 VALUES (4, '직원4', 39000);
INSERT INTO EMPLOYEES2 VALUES (5, '직원5', 2000);
								
-- TRUNCATE 
TRUNCATE TABLE EMPLOYEES2 ;

 

 

 

6. DROP vs TRUNCATE vs DELETE

6-1. DROP

DROP TABLE 테이블명 ;

존재 자체를 삭제한다.

 

6-2. TRUNCATE

TRUCATE TABLE 테이블명 ;

데이터만 통으로 삭제

테이블을 DROP 했다가 CREATE하기 때문에, 모든 행을 삭제하는 데에는 가장 빠르고 효율적인 방법이다.

데이터 정의어에 속한다.

 

6-3. DELETE

DELETE FROM 테이블명 (WHERE ~) ;

데이터를 골라서 삭제가 가능

데이터 조작어에 속한다(TRUNCATE와는 내부방식이 다름)

DELETE는 한 줄 한 줄 삭제가 된다.로그가 남는다.