2013년 1월 31일 목요일

[DB] 제약조건

제약조건(데이터를 추가, 수정, 삭제하는 과정에서 DB의 무결성 유지)

CREATE TABLE emp04(
 empno number(4) NOT NULL, //반드시 입력해야하는 제약조건
 empname varchar2(10),
 job varchar2(10) PRIMARY KEY,//식별자로써 주키이다. 유일한 키값이고 반드시 입력
 deptno number(4) UNIQUE //중복되는 값을 배제(유일한 값)
);

테이블의 제약조건을 읽어냄
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
 FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP04';

CONSTRAINT_TYPE
P - PRIMARY KEY
R - FOREIGN KEY(REFERENCES)
U - UNIQUE
C - NOT NULL, CHECK

테이블 선언에서 주키와 외래키 설정하기
CREATE TABLE emp05(
 empno number(4),
 empname varchar2(10),
 job varchar2(10),
 department_id number(4)

 CONSTRAINT emp05_empno_PK PRIMARY KEY(empno),
 CONSTRAINT emp05_department_id_ FK FOREIGN KEY(department_id)
 REFERENCES departments(department_id)
);

테이블을 만든 뒤 나중에 제약을 설정

CREATE table emp06(//테이블 수정 방식
empno number(4),
empname varchar2(10),
job varchar2(10),
department_id number(4)
);

ALTER TABLE emp06 ADD CONSTRAINT emp06_empno_PK PRIMARY KEY(empno);
ALTER TABLE emp06 ADD CONSTRAINT emp06_department_id_FK FOREIGN KEY(department_id) REFERENCES departments(department_id);

제약조건 삭제
ALTER TABLE emp06 DROP CONSTRAINT emp06_empno_PK

CHECK (의도한 데이터만 입력받기 위해 설정)
CREATE TABLE emp07(
 empno number(4),
 empname varchar2(10),
 job varchar2(10),
 gender varchar2(1) CHECK(gender IN('M', 'F'))//M, F 둘중 하나의 값만 허용
);
INSERT INTO emp07 values(100, '홍길동', 'IT', 'M');

DEFAULT 제약조건
CREATE TABLE emp08(
 deptno number,
 dname varchar2(20),
 loc varchar2(20) DEFAULT 'seoul', //값을 입력하지 않으면 디폴트로 seoul입력
);
INSERT INTO emp08(deptno, dname) values(10, '홍길동');

[DB] 테이블 생성

CREATE TABLE 테이블이름(
 컬럼이름1 데이터타입,
 컬럼이름2 데이터타입,
 컬럼이름3 데이터타입,
 제약조건
);

데이터타입의 종류
- 문자형 : char(size), varchar2(size) <- 가변형
- 숫자형 : number, number(w), number(w, d)
- 날짜형 : date
- 대용량(이미지, 파일) : LOB

테이블 복사
CREATE TABLE emp01 AS SELECT * FROM employees;

테이블 구조만 복사
CREATE TABLE emp02 AS SELECT * FROM employees WHERE 1 = 0;

테이블 구조 수정
- 새로운 컬럼 추가
ALTER TABLE emp02 ADD(job varchar2(30));

- 기존 컬럼 수정
ALTER TABLE emp02 MODIFY(job varchar2(50));

- 기존 컬럼 삭제
ALTER TABLE emp02 DROP(job);
또는
ALTER TABLE emp02 DROP column job;

테이블 삭제
DROP TABLE emp02 PURGE;//쓰레기 데이터 값까지 제거

테이블 로우(row) 삭제
TRUNCATE table emp01;
DELETE FROM emp01;
두 명령어의 차이는 DELETE는 ROLLBACK이 가능해서 데이터를 되돌릴 수 있는 반면 TRUNCATE는 ROLLBACK이 불가능하다는 것

테이블 쓰레기값 삭제
PURGE recyclebin;

테이블 이름 변경
RENAME emp01 to emp02;

테이블 정의
CREATE TABLE emp03(
 empno number(4),
 empname varchar2(10),
 job varchar2(10),
 deptno number(4)
);

ROW 추가
INSERT INTO emp03 values(1, '홍길동', 'IT', 30);

ROW 추가시 명시적 컬럼 추가
INSERT INTO emp03(empno, empname, job) values(1, '박찬호', 'IT');

ROW 추가시 null문자로 추가
INSERT INTO emp03 values(1, null, null, null);

2013년 1월 30일 수요일

[DB] 함수

1. 그룹함수(SUM, AVG, MAX, MIN, COUNT...)
- SUM() 인자값으로 컬럼을 지정. 해당컬럼의 합계 산출
SELECT SUM(salary) AS 인건비 FROM employees;

- AVG() 해당컬럼의 평균 산출
SELECT AVG(salary) AS 평균급여 FROM employees;

- MAX() 컬럼의 최대값
SELECT MAX(salary) AS 최대급여 FROM employees;

- MIN() 컬럼의 최소값
SELECT MIN(salary) AS 최소급여 FROM employees;

- COUNT() 컴럼의 수
SELECT COUNT(salary) AS 글로자 수 FROM employees;

GROUP BY (특정 컬럼의 값들을 각 값별로 묶어서 결과를 출력, 그룹함수를 적용할 때 사용한다. 단순히 group by에 기술된 컬럼만 출력한다면 distinct와 같은 결과.)
- 사원테이블에 부서별로 각 그룹의 부서 아이디와 평균 연봉 출력
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

잘못된 출력명령
SELECT department_id, last_name, AVG(salary) FROM employees GROUP BY department_id;(부서별로 그룹을 묶었으나 출력하는 컬럼에 last_name이 존재한다. last_name을 어떠한 기준으로 출력할 방법이 없다.)

HAVING (특정 그룹의 조건을 지정)
- 그룹별로 부서 아이디와 연봉이 10000이상인 부서 출력
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id
HAVING AVG(salary) >= 10000;

- 부서별로 사원의 수와 커미션을 받는 사원의 수를 카운트하여 출력
SELECT department_id, COUNT(department_id), COUNT(commission_pct)
FROM employees GROUP BY department_id HAVING COUNT(commission_pct) IS NOT NULL

2. 숫자함수
- MOD() 나머지값 리턴
SELECT MOD(1, 3) "나머지 값" FROM dual;(dual은 가상의 테이블)

- ROUND() 반올림
SELECT ROUND(899.2356, -2) "반올림" FROM dual;
(두 번쨰 인자가 -2변 소수점 위 2번째 자리 반올림, 2라면 소수점 아래 2번째 자리 반올림)

- TRUNC() 반올림 버림
SELECT TRUNC(899.2356, 2) "반올림버림" FROM dual;

3. 문자열함수
- LOWER() 대문자를 소문자로
SELECT LOWER('DataBase') FROM dual;

- UPPER() 소문자를 대문자로
SELECT UPPER('DataBase') FROM dual;

- SUBSTR() 부분문자열 추출
SELECT SUBSTR('Korea Economy', 3, 8) FROM dual;
3->추출문자 시작, 8->추출문자로부터 출력할 문자갯수

- LENGTH() 문자열길이 추출
SELECT LENGTH('DataBase') FROM dual;

4. 날짜함수
- SYSDATE
SELECT SYSDATE-1 AS 어제, SYSDATE AS 오늘, SYSDATE+1 AS 내일 FROM dual;

- 근무일수 구하기
SELECT last_name, SYSDATE - hire_date FROM employees;

5. 변환함수 (데이터형변환 + 포멧변경)
- TO_CHAR() 해당하는 숫자, 날짜 형태의 데이터를 문자열로 변환
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DY') FROM dual;

숫자를 문자로
SELECT TO_CHAR(2000, '&999,999') FROM dual;

입사년도 출력
SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYY') = '1997';

- TO_DATE() 숫자, 문자형태의 데이터를 날짜형으로 변환
SELECT last_name, hire_date FROM employees
WHERE hire_date = TO_DATE(19970625, 'YYYY/MM/DD');

-TO_NUMBER() 정수형으로 변환
SELECT TO_NUMBER('10,000', '999,999') + TO_NUMBER('20,000', '999,999') FROM dual;

6. 기타함수
- DECODE() 케이스문처럼 사용
SELECT job_id, DECODE(job_id, 'ST_MAN', 'Salse DEPT', 'SH_CLERK', 'SALES DEPT', 'Another') FROM employees WHERE job_id LIKE 'S%';

문제) department_id가 80일 경우 Accounting, 50일 경우 Sales, 30일 경우 Invocation
SELECT department_id, DECODE(department_id, 80, 'Accounting', 50, 'Sales', 30, 'Invocation') FROM employees;

- NVL() 데이터값이 NULL인 경우 0 또는 다른 값으로 변환
SELECT employee_id, salary, NVL(commission_pct, 0) FROM employees;

문제) job_id순서로 사원의 연봉을 출력. 연봉 = (salary * 12) + commission_pct
SELECT last_name, salary * 12 + NVL(commission_pct, 0) AS 연봉 FROM employees
ORDER BY job_id ASC;

이상 몇가지 함수를 정리해 보았다. 더많은 함수들은 따로 더 찾아보고 공부하자

2013년 1월 28일 월요일

[DB] SELECT

기본적인 SELECT 구문 (문장 마지막에 세미콜론은 안붙여도 된다.)

- table 정보 출력
SELECT * FROM TAB;

- 해당 테이블의 구조(정보)를 알고자 할때
DESC (해당테이블);

- 사원테이블의 전체 컬럼내용에 대한 데이터 출력
SELECT * FROM employees;

- 사원테이블 중 원하는 컬럼내용(employee_id, last_name, salsry)에 대한 데이터 출력
SELECT employee_id, last_name, salary FROM employees;

- 컬럼명 대신 별칭으로 출력
첫 번째 방법
SELECT employee_id "사원번호", last_name "사원명", salary "연봉" FROM employees;

두 번째 방법
SELECT employee_id as 사원번호, last_name as 사원명, salary as 연봉 FROM employees;

WHERE 조건 

- 사원테이블에 성이 King인 사람 찾기
SELECT * FROM employees WHERE last_name = 'King';(대소문자 구문하고 홀따옴표 중요)

- AND연산자를 이용해 봉급이 10000 이상 20000 이하인 사람의 사원번호와 성, 봉급 출력
SELECT employee_id, last_name, salary FROM employees
WHERE salary >= 10000 AND salary <= 20000;

- BETWEEN연산자를 이용하여 위에 예제와 같은 데이터 출력
SELECT employee_id, last_name, salary FROM employees
WHERE salary BETEWEEN 10000 AND 20000;

- OR연산자를 이용하여 직업 아이디가 FI_MGR 이거나 FL_ACCOUNT인 사람의 사원번호, 고용일, 봉급, 직업 아이디 출력
SELECT employee_id, hire_date, salary, job_id FROM  employees
WHERE job_id = 'FI_MGR' OR job_id = 'FL_ACCOUNT';

- IN연산자(해당하는 컬럼 모두 출력)를 이용하여 직업 아이디가 'FI_MGR', 'FL_ACCOUNT', 'SA_MGR' 인 사람의 사원번호, 고용일, 봉급, 직업 아이디를 출력
SELECT employee_id, hire_date, salary, job_id FROM employees
WHERE job IN('FI_MGR', 'FL_ACCOUNT', 'SA_MGR');

- DISTINCT (중복 제거)를 사용하여 직업 아이디를 출력
SELECT DISTINCT job_id FROM employees;

- 비교연산자를 사용하여 97년도에 입사한 사원의 모든 정보를 출력
SELECT * FROM employees WHERE hire_date >= '1997/01/01' AND hire_date <= '1997/12/01';

- 부서테이블에서 부서번호가 10인 데이터 모두 출력
SELECT * FROM departments WHERE department_id =10;

- NOT연산자를 이용하여 위의 예제가 10이 아닌 데이터 모두 출력
SELECT * FROM departments WHERE NOT department_id = 10;

- 위의 예제에서 또다른 not 사용해 출력
SELECT * FROM departments WHERE department_id <> 10;

- LIKE연산자(검색과 관련해 자주 쓰임)를 사용하여 97년에 입사한 사원의 아이디, 성, 고용일을 출력
SELECT employee_id, last_name, hire_date FROM employees
WHERE hire_date LIKE '97%';

- LIKE 또다른 예제-
1. last_name이 K로 시작하는 row를 출력
SELECT employee_id, last_name, hire_date FROM employees
WHERE last_name LIKE 'K%';
2. last_name이 o로 끝나는 row를 출력
SELECT employee_id, last_name, hire_date FROM employees
WHERE last_name LIKE '%o';
3. last_name 중간에 a를 포함하지 않는 사원의 모든 정보를 출력
SELECT * FROM employees WHERE last_name NOT LIKE '%a%';

-ORDER BY(정렬)을 이용하여 고용일을 오름차순으로 정렬하고 사원 아이디, 성, 고용일을 출력
SELECT employee_id, last_name, hire_date FROM employees
ORDER BY hire_date ASC;

[DB] 데이터베이스

유용하고 의미있는 정보를 관리하고 저장하기 위해 데이터베이스를 사용한다.
데이터의 수가 십만개 또는 몇백만개는 크지도 않지만 오늘날 빅데이터가 이슈화되고 있는 시점에서 실시간으로 생성되는 데이터의 수는 어마어마하다.

따라서 Oracle, MS SQL, MY SQL과 같은 DBMS, 즉 데이터베이스를 관리해 주는 소프트웨어가 필요하다.

앞으로 Oracle을 위주로 공부한다. 그 이유는 가장 널리 사용되어지고 성능과 비용이 좋기 때문이다. 하지만 데이터베이스의 구축보다는 어떻게 활용을 할것인가에 초점을 맞출것이고  데이터베이스 설계의 측면에서는 프로그래머가 되어 많은 역략을 쌓으면서 차츰 데이터를 보눈 눈을 높여가면서 계속적으로 공부해나가야할 분야일 것 같다.

앞으로의 순서는 대략 다음과 같다.

1. SELECT 문을 활용한 데이터 선택
2. 함수
3. TABLE 생성과 제약조건 설정
4. JOIN
5. VIEW, SQUENCE, INDEX
6. DB모델링

일단 간단한 SELECT문에 대해 알아보자

SELECT : 원하는 Column을 선택
FROM : 해당 테이블 지정
WHERE : 조건 설정
DISTINCT : 중복제거
ORDER BY : 정렬(ASC오름차순, DESC내림차순)

예를 들어 다음과 같다.
SELECT Column FROM Table WHERE 조건 ORDER BY 정렬필드 [ASC, DESC]


DB 시작!!!

다시 한번 고고고!!!