16일차 : 오라클
1. 오라클 시작하기
오라클은 java와 달리 대소문자 구분을 하지 않는다.
프로그램 설치 ==> password : java1234
SQL> conn system/java1234
SQL> alter user hr account unlock; //hr계정이 따로 만들어 놓은 것(연습용계정)
SQL> alter user hr identified by hgr;
--관리자 계정으로 접속하기
CMD> sqlplus system/java1234
-- 사용자 생성하기
SQL> create user scott identified by tiger; //tiger : 비밀번호
-- 사용자에 접속권한, 리소스 사용권한 주기
SQL> grant connect,resource to scott;
-- scott계정으로 로그인하기
SQL>conn scott/tiger
-- scott.sql 파일 실행하기
sqlplus scott/tiger --이것만 해도 접속!
--라인 사이즈 조절
SQL> SET LINESIZE 400
--현재 계정에 있는 모든 테이블 조회
SQL> SELECT * FROM TAB;
========================================================================
Database -> 하나 이상의 테이블이 존재해야 한다.
학번 이름 학과
1 홍길동 컴퓨터
2 김아무 전자
3 .
4
여기에서 학번을 '컬럼' 이라고 한다
1번 줄에 하나로 들어가는 것(하나로 묶이는 것)을 row, 행이라고 한다.
전체적으로 통틀어 '테이블'이라 한다.
관계형 데이터베이스
- 테이블을 하나로 처리해주는 것이 아니라 관계를 맺어주는 것
데이터베이스를 통해 검색할 수 있도록 한다.
오라클은 대부분 관계형 데이터 베이스를 사용.
사용이유: 중복데이터를 최소화 할 수 있기 때문
최소화 이유 - 테이블이 하나로만 이루어져야 한다고 하면,
ex) 이름, 학과, 전화번호, 성적...등등 있으면 성적 테이블 / 전화테이블 로 나누어 데이터를 관리
============================================================================
[2] SQL(Structured Query Language)
- 주요 용어
1) 테이블 : 관계형 DB에서 기본 데이터 저장구조로써 Entity(실체)의 집학 저장소
2) ROW : 테이블의 행(하나의 유효 데이터)
3) COLUMN : 테이블 열의 이름
4) PRIMARY KEY : 테이블에서 각 ROW를 유일하게 구분하는 컬럼
5) FORELGN-KEY : 다른 테이블의 컬럼을 참조하는 컬럼
6) FIELD : 테이블에서 ROW와 COLUMN이 교차하는 데이터
7) NULL : 데이터가 존재하지 않는 필드
(1) SQL의 종류
1) DQL (Data Query Language) - 데이터 질의어(select)
2) DML (Data Manipulate Language) : 데이터를 입력, 수정, 삭제할 때 사용(insert, update, delete)(★★★기억)
3) DOL (Data Definition Language) : 테이블 등의 객체를 생성, 변경, 삭제 할 때 사용(create, drop, alter,..)
4) TCL (Transaction Control Language) : 테이블 내의 DML문을 DB에 저장하거나 취소할 때 사용(commit.rollback,..)(★★★중요!)
5) DCL (Data Control Language) : DB사용자에게 권한을 부여하거나 취소할 때 사용(grant, revoke,..)
(2) DQL (SELECT) - 기본적으로 가장 많이 사용. 가장 중요!
1) 기본 구조
SELECT 컬럼명1.컬럼명2,... ┐이건 무조건 써야한다!!
FROM 테이블명 ┘
WHERE 조건절
ORDER BY 정렬기준컬럼 [ASC|DESC]
-- EMP 테이블의 구조
SQL> SELECT * FROM EMP;
EMPNO - 사원번호(PK)
ENAME - 사원이름
JOB - 직업
MGR - 매니저 번호
HIREDATE - 입사일
SAL - 급여
COMM - 커미션
DEPTNO - 부서번호(FK)
-- EMP테이블에서 사원번호, 사원이름, 부서번호, 급여를 조회하시오.
SELECT EMPNO,ENAME,DEPTNO,SAL
FROM EMP; (작업 실행 후 JAVA처럼 세미콜론을 무조건 찍어줘야 한다)
[결과]
SQL> select empno,ename,deptno,sal
2 from emp;
EMPNO ENAME DEPTNO SAL
---------- -------------------- ---------- ----------
7369 SMITH 20 800
7499 ALLEN 30 1600
7521 WARD 30 1250
7566 JONES 20 2975
7654 MARTIN 30 1250
7698 BLAKE 30 2850
7782 CLARK 10 2450
7839 KING 10 5000
7844 TURNER 30 1500
7900 JAMES 30 950
7902 FORD 20 3000
EMPNO ENAME DEPTNO SAL
---------- -------------------- ---------- ----------
7934 MILLER 10 1300
--EMP테이블에서 급여가 3000이상인 사원들의 모든 정보를 조회하시오.(급여가 높은순으로 출력)
SELECT * FROM EMP (모든 컬럼명을 주려면 * 사용)
WHERE SAL>=3000 //조건이 3000보다 크거나 작다
ORDER BY SAL DESC; //DESC내림차순정렬
*세미콜론을 찍으면 명령어를 끝내고 결과물 출력
--EMP테이블에서 부서번호가 30번이고 급여가 2000 이상인 사원들의 모든 정보를 조회하세요.
(급여순으로 오름차순, 급여순으로 내림차순해서 출력하시오)
SELECT * FROM EMP
WHERE DEPTNO=30 AND SAL>=2000 //부서번호 30번 그리고 급여 200이상
ORDER BY SAL DESC,EMPNO ASC; //급여 내림차순
2) DISTINCT / ALL (중복데이터 제거 / 중복데이터 포함)
--EMP테이블에서 부서번호를 출력하세요.(단 중복 부서번호는 출력하지 않는다.)
SELECT DISTINCT DEPTNO FROM EMP;
[결과]
SQL> SELECT DISTINCT DEPTNO FROM EMP;
DEPTNO
----------
30
20
10
3) ALIAS --> 컬럼명에 별칭을 부여
SELECT EMPNO "사원번호",ENAME"사원이름",JOB AS 직업
FROM EMP;
*따옴표는 줘도 되고 안줘도 되지만 특수문자가 포함되는 경우에는 따옴표로 무조건 묶어줘야 한다.
[결과]
SQL> SELECT EMPNO"사원번호",ENAME"사원이름",JOB AS 직업
2 FROM EMP;
사원번호 사원이름 직업
---------- -------------------- ------------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7839 KING PRESIDENT
7844 TURNER SALESMAN
7900 JAMES CLERK
7902 FORD ANALYST
4) 연산자
(1) 산술연산자 (+, -, *, /)
-- 부서번호가 10번인 사원들의 10% 인상된 급여를 출력해 보세요/
SELECT DEPTNO,SAL,SAL*1.1 "10%인상된급여" FROM EMP
WHERE DEPTNO=10;
(2) 비교연산자 (=, !=, >, >=, <, <=)
(3) 논리연산자 (NOT, AND, OR)
[Quiz]
--부서번호가 10번인 부서의 사람 중 사원번호, 이름, 급여를 출력하시오.
SQL> SELECT DEPTNO,EMPNO,ENAME,SAL FROM EMP
2 WHERE DEPTNO=10;
DEPTNO EMPNO ENAME SAL
---------- ---------- -------------------- ----------
10 7782 CLARK 2450
10 7839 KING 5000
10 7934 MILLER 1300
--사원번호가 7369인 사람 중 이름, 입사일, 부서번호를 출력하시오.
SQL> SELECT ENAME,HIREDATE,DEPTNO FROM EMP
2 WHERE EMPNO=7369;
ENAME HIREDATE DEPTNO
-------------------- -------- ----------
SMITH 80/12/17 20
--이름이 'ALLEN'인 사람의 모든 정보를 출력하시오.(문자의 경우 무조건 작은따옴표 필수)
SQL> SELECT * FROM EMP
2 WHERE ENAME='ALLEN';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
--급여가 800이상인 사원의 이름, 급여, 부서번호를 출력하시오.
SQL> SELECT ENAME,SAL,DEPTNO FROM EMP
2 WHERE SAL>=800;
ENAME SAL DEPTNO
-------------------- ---------- ----------
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
KING 5000 10
TURNER 1500 30
JAMES 950 30
FORD 3000 20
ENAME SAL DEPTNO
-------------------- ---------- ----------
MILLER 1300 10
--급여가 1600보다 크고 3000보다 작은 사원의 이름, 직업, 급여를 출력하시오.
SQL> SELECT ENAME,JOB,SAL FROM EMP
2 WHERE SAL>1600 AND SAL<3000;
ENAME JOB SAL
-------------------- ------------------ ----------
ALLEN SALESMAN 1600
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
FORD ANALYST 3000
--부서번호가 30인 사원들의 이름, 사원번호, 부서번호를 사원번호순으로 오름차순 정렬해서 출력하시오.
SQL> SELECT ENAME,EMPNO,DEPTNO FROM EMP
2 WHERE DEPTNO=30
3 ORDER BY EMPNO; --자동으로 오름차순으로 정렬하기 때문에 오름차순인 ASC는 생략 가능
ENAME EMPNO DEPTNO
-------------------- ---------- ----------
ALLEN 7499 30
WARD 7521 30
MARTIN 7654 30
BLAKE 7698 30
TURNER 7844 30
JAMES 7900 30
(4) SQL연산자(오라클에서만 볼 수 있는 연산자)
- IN, ANY, ALL, BETWEEN, LIKE, IS NULL, IS NOT NULL, EXISTS,...
--부서번호가 10번 또는 20번인 사원 중에서 부서번호, 급여를 출력하시오.(IN연산자)
SELECT DEPTNO,SAL
FROM EMP
WHERE DEPTNO IN(10,20); //10,20
*하나라도 값이 맞으면 참의 조건
같다라는 연산자만 쓸 수 있다.
--부서번호가 10번 또는 20번인 사원 중에서 부서번호, 급여를 출력하시오.(ANY연산자)
SELECT DEPTNO,SAL
FROM EMP
WHERE DEPTNO=ANY(10,20); --일반적으로 복수행에서(ANY는 하나라도 일치하는 값이 있으면 참)
*10하고 같거나 또는 20하고 같은 것
SELECT DEPTNO,SAL
FROM EMP
WHERE DEPTNO=ALL(10,20); --일반적으로 복수행에서(ALL은 모든 값이 일치해야 참)
*10과 20모두 같아야 하는 것
--BETWEEN A AND B : A와 B값 사이
--급여가 1000부터 3000사이인 사원들의 사원번호, 이름, 급여를 출력하시오.(BETWEEN연산자 사용)
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 3000;
--사원이름이 J로 시작하는 사원의 모든 정보를 조회하시오.
SELECT * FROM EMP
WHERE ENAME LIKE 'J%';
% = 와일드카드
--사원이름에 A를 포함하는 사원의 모든 정보를 조회하시오.
SELECT * FROM EMP
WHERE ENAME LIKE '%A%';
--사원이름 중 세번 째 단어가 R인 사원의 모든 정보를 조회하시오.
SELECT * FROM EMP
WHERE ENAME LIKE '__R%';
* __는 앞에 자리수에 뭐가 오든 상관이 없다
SELECT * FROM EMP
WHERE ENAME LIKE '__RT%';
--사원 중 커미션이 NULL인 사원의 모든 정보를 조회하시오.
SELECT * FROM EMP
WHERE COMM IS NULL;
--사원 중 커미션이 NULL이 아닌 사원의 모든 정보를 조회하시오.
SELECT * FROM EMP
WHERE COMM IS NOT NULL;
(5) 결합연산자(||)
SELECT ENAME || '의 급여는' || SAL || '입니다.'
FROM EMP;
|| : 문자열 연결
[Quiz]
- 직업이 'MANAGER' 이거나 또는 'SALESMAN'인 사원의 이름과 직업을 출력하시오.(IN사용)
SQL> SELECT ENAME,JOB
2 FROM EMP
3 WHERE JOB IN('MANAGER','SALESMAN');
ENAME JOB
-------------------- ------------------
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
TURNER SALESMAN
- 이름이 'S'로 시작하는 사원의 번호, 이름, 입사일, 부서번호를 출력하시오.
SQL> SELECT EMPNO,ENAME,HIREDATE,DEPTNO
2 FROM EMP
3 WHERE ENAME LIKE 'S%';
EMPNO ENAME HIREDATE DEPTNO
---------- -------------------- -------- ----------
7369 SMITH 80/12/17 20
- 입사일이 81년도인 사원의 입사일, 이름을 출력하시오.
SQL> SELECT HIREDATE,ENAME
2 FROM EMP
3 WHERE HIREDATE LIKE '81%';
HIREDATE ENAME
-------- --------------------
81/02/20 ALLEN
81/02/22 WARD
81/04/02 JONES
81/09/28 MARTIN
81/05/01 BLAKE
81/06/09 CLARK
81/11/17 KING
81/09/08 TURNER
81/12/03 JAMES
81/12/03 FORD
- 이름이 'S'로 시작하고 마지막 글자가 'H'인 사원의 모든 정보를 출력하시오.
SQL> SELECT * FROM EMP
2 WHERE ENAME LIKE 'S%H';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
- 이름의 두 번째 문자가 'A'인 사원의 이름을 출력하시오.
SQL> SELECT ENAME
2 FROM EMP
3 WHERE ENAME LIKE '_A%';
ENAME
--------------------
WARD
MARTIN
JAMES
- 이름의 첫글자가 'A'로 시작하거나 부서번호가 30인 사원의 사원번호, 이름, 부서번호를 출력하시오.
SQL> SELECT EMPNO,ENAME,DEPTNO
2 FROM EMP
3 WHERE ENAME LIKE'A%' OR DEPTNO=30;
EMPNO ENAME DEPTNO
---------- -------------------- ----------
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
7698 BLAKE 30
7844 TURNER 30
7900 JAMES 30
- 입사일이 81년도가 아닌 사원들의 입사일과 이름을 출력하시오.
SQL> SELECT HIREDATE,ENAME
2 FROM EMP
3 WHERE HIREDATE NOT LIKE '81%'; //WHERE NOT(HIREDATE LIKE '81%');
HIREDATE ENAME
-------- --------------------
80/12/17 SMITH
82/01/23 MILLER
- 급여가 2000에서 3000사이인 사원들의 사원번호, 이름, 급여를 출력하시오.(BETWEEN사용)
SQL> SELECT EMPNO,ENAME,SAL
2 FROM EMP
3 WHERE SAL BETWEEN 2000 AND 3000;
EMPNO ENAME SAL
---------- -------------------- ----------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7902 FORD 3000
5) 함수 (Function)
- 어떠한 일을 수행하는 기능으로써 주어진 인수(argument)를 재료로 처리를 하여 그 결과값을 반환함
1) 함수의 종류
- 단일행함수 : 하나의 행(row)당 하나의 결과 값을 반환하는 함수
- 복수행함수 : 여러 개의 행당 하나의 결과값을 반환하는 함수
2) 단일행함수 (오라클에서 지원하고 있는 함수)
<1> 문자함수
- CHR(아스키코드)
SELECT CHR(65) FROM DUAL;
CH
--
A
*DUAL은 SELECT의 구색을 맞추기 위해 쓰는 가상테이블
- CONCAT (컬럼명,'붙일문자')
SELECT CONCAT(ENAME,'IS A'),JOB FROM EMP;
- INITCAP('문자열') : 시작 문자를 대문자로 변환
SELECT INITCAP('the tiger') FROM DUAL;
- LOWER('문자열') : 소문자로 변환
SELECT LOWER('THE LION') FROM DUAL;
- LPAD('문자열',자릿수,'채울문자')
SELECT LPAD(SAL,10,'#') FROM EMP; //왼쪽에다가 문자#로 채우겠다. 공백을 주면 공백 출력
- RPAD('문자열',자릿수, '채울문자')
SELECT RPAD(SAL,10,'$') FROM EMP; //오른쪽에다가 문자$를 채우겠다.
- LTRIM('문자열1','없앨문자열')
SELECT LTRIM('THE LION','THE') FROM DUAL; //왼쪽을 기준으로 위치한 문자를 찾아서 오른쪽 문자를 없애줌
- REPLACE(문자열,'원래문자열','바꿀문자열')
SELECT REPLACE('my name','name','job') FROM DUAL;
- SUBSTR('문자열',위치,개수) //위치 만큼 개수 불러오기
SELECT SUBSTR('ABCDEFG',3,2) FROM DUAL;
- INSTR('문자열1','위치찾는문자열') : 문자의 위치 찾기 (없으면 0)
SELECT INSTR('HELLO','E') FROM DUAL;
- LENGTH('문자열') : 문자열 길이
- NUL(컬럼명,값) : 컬럼값이 NULL인 경우 값으로 채우기(★★★기억!)
SELECT EMPNO,ENAME,NVL(COMM,'-1') FROM EMP;
(COMM이 문자열이면 작은따옴표로 넣어주면 된다.)
<2> 날짜함수
- SYSDATE : 현재 날짜 얻어오기(★)
SELECT SYSDATE FROM DUAL;
- ADD_MONTHS(날짜, 더할개월 수)
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL; --오늘날짜에 3개월 더하기
*SELECT SYSDATE+40 FROM DUAL; //40일 더하기
- MONTHS_BETWEEN(날짜1,날짜2) : 날짜 1과 날짜 2의 개월 수
SELECT ENAME "사원이름",MONTHS_BETWEEN(SYSDATE,HIREDATE) "근무개월수"
FROM EMP;
<3> 날짜를 문자로 변환하는 함수(★★)
- 10 CHAR(날짜,'형식')
-- 오늘날짜를 2012-08-06 형식으로 출력하세요.
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL; //년도-월-날짜
-- 오늘날짜와 현재시각을 출력해 보세요.
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH:MI:SS') FROM DUAL; //년도/월/날짜 시:분:초
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL; // 24시간 단위 표시: HH24
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD AM HH:MI:SS') FROM DUAL; //AM:시간 앞에 오전 또는 오후 표시
SELECT TO_CHAR(SYSDATE,'YYYY"년"MM"월"DD"일"AM HH:MI:SS') FROM DUAL; //"년" "월" "일" - 이 때는 큰 따옴표
<4> 문자를 날짜로 변환하는 함수
- TO_DATE('날짜에 해당하는 문자열','해석할 서식')
SELECT TO_DATE('01-08-12','DD/MM/YYYY') FROM DUAL;
<5> 수학관련 함수
- ABS(숫자) : 절대값
SELECT ABS(-10) FROM DUAL;
- CEIL(숫자) : 올림값 구하기
SELECT CEIL(3.2) FROM DUAL;
- FLOOR(숫자) : 내림값 구하기
SELECT FLOOR(3.9) FROM DUAL;
- ROUND(숫자) : 반올림값 구하기
SELECT ROUND(3.6) FROM DUAL;
- MOD(숫자1,나눌값) : 나머지 구하기
SELECT MOD(5,3) FROM DUAL;
- TRUNC(숫자, 절삭할 소수점자릿수)
SELECT TRUNC(12.567,2) FROM DUAL;
2) 복수행 함수(★)
<1> COUNT(컬럼명) : 개수 얻어오기
-- EMP테이블에서 전체 사원수를 구하시오.
SELECT COUNT(*) FROM EMP;
(SELECT COUNT(EMPNO) FROM EMP;)
-- 커미션을 받는 사원수를 구하시오.
SELECT COUNT(COMM) FROM EMP;
<2> SUM(컬럼명) : 컬럼의 합 구하기
-- 전체 사원의 급여의 합을 구하여 출력하시오.
SELECT SUM(SAL) "사원급여합" FROM EMP;
<3> AVG(컬럼명) : 컬럼의 평균 구하기
-- 전체 사원의 급여의 형균을 구하시오.
SELECT AVG(SAL) FROM EMP;
<4> MIN(컬럼명),MAX(컬럼명) : 컬럼의 최소값, 컬럼의 최대값 구하기
SELECT MIN(SAL) "최소급여", MAX(SAL) "최대급여"
FROM EMP;
[ 과 제 ]