** 자바 테스트 용을 ORACLE 11G XE를 설치해 사용함.
[1] 오라클 시작하기
- 프로그램 설치하기(경로명에 한글이 있으면 안됨: password=>java1234)
- 관리자계정으로 로그인하기
또는
C:\>sqlplus system/java1234
- 연습용 사용자 계정만들기
- 생성된 사용자에게 접속권한과 리소스 사용권한 부여하기
- 생성된 계정으로 접속하기
- 연습용 테이블 생성하기
< 테스트 관련 테이블>
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/TIGER
DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
[2] SQL(Structured Query Language)
- 주요용어
1) 테이블:관계형 DB에서 기본 데이터 저장구조로써 엔티티(실체)의 집합 저장소
2) ROW : 테이블의 행(하나의 유효데이터)
3) COLUMN : 테이블 열의 이름
4) PRIMARY KEY:테이블에서 각 ROW를 유일하게 구분하는 컬럼
5) FOREIGN KEY:다른 테이블의 컬럼을 참조하는 컬럼
6) FIELD : 테이블에서 ROW와 COLUMN이 교차하는 데이터
7) NULL : 데이터가 존재하는 않는 필드
- SQL의 종류
1) DQL(Data Query Language):데이터 질의어(select)
2) DML(Data Manipulate Langueage):데이터를 입력,수정,삭제할때사용(insert,update,delete)
3) DDL(Data Definition Language): 테이블내의 객체를 생성,변경,삭제할때 사용(create,drop,alter,..)
4) TCL(Transaction Control Language):테이블내의 DML문을 저장하거나 취소할때사용(commit,rollback)
5) DCL(Data Control Language):DB사용자에게 권한을 부여하거나 취소할때 사용(grant,revoke)
[3] DQL(SELECT)- 데이터 조회
1) 기본구조
SELECT 컬럼명1,컬럼명2,..
FROM 테이블명
[WHERE 조건절]
[ORDER BY 정렬기준컬럼 [ASC|DESC]]
-- 테이블 정보 조회하기
-- 테이블구조 조회하기
Name
---------------
EMPNO ==> 사원번호
ENAME ==> 사원이름
JOB ==> 직업
MGR ==> 매니져번호
HIREDATE ==> 입사일
SAL ==> 급여
COMM ==> 커미션
DEPTNO ==> 부서번호
-- EMP테이블에서 사원번호,사원이름,입사일,급여를 조회하시오.
SELECT EMPNO,ENAME,HIREDATE,SAL
FROM EMP;
[결과]
EMPNO ENAME HIREDATE SAL
---------- -------------------- -------- ----------
7369 SMITH 80/12/17 800
7499 ALLEN 81/02/20 1600
7521 WARD 81/02/22 1250
7566 JONES 81/04/02 2975
7654 MARTIN 81/09/28 1250
...
-- EMP테이블에서 급여가 3000이상인 사원들의 모든 정보를 조회하시오. (결과는 급여가 낮은 순으로 출력)
SQL> SELECT * FROM EMP
2 WHERE SAL>=3000
3 ORDER BY SAL ASC;
-- EMP테이블에서 부서번호가 30번 이고 급여가 1000이상인 사원들의 모든 정보를 조회하시오.(급여순으로 내림차순하고,사원번호순으로 오름차순해서 출력)
SQL> SELECT * FROM EMP
2 WHERE DEPTNO=30 AND SAL>=1000
3 ORDER BY SAL DESC,EMPNO ASC;
Q1) 부서번호가 10번인 부서의 사원중 사원번호,이름,급여를 출력하시오.
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE DEPTNO=10;
Q2) 사원번호가 7369인 사람의 이름,입사일,부서번호를 출력하시오.
SELECT ENAME,HIREDATE,DEPTNO
FROM EMP
WHERE EMPNO=7369;
Q3) 급여가 800이상인 사원의 이름,급여,부서번호를 출력하시오.
SELECT ENAME,SAL,DEPTNO
FROM EMP
WHERE SAL>=800;
Q4) 급여가 1600보다 크고 3000보다 작은 사원의 이름,직업,급여를 출력하시오.
SELECT EMPNO,JOB,SAL
FROM EMP
WHERE SAL>=1600 AND SAL<=3000;
Q5) 부서번호가 30인 사원들의 이름,사원번호,부서번호를 사원번호순으로 오름차순 정렬해서 출력하시오.
SELECT ENAME,EMPNO,DEPTNO
FROM EMP
WHERE DEPTNO=30
ORDER BY EMPNO ; -- ASC는 생략가능
2) DISTINCT / ALL (중복데이터제거/중복데이터포함)
-- EMP테이블의 모든 사원의 직업을 출력하시오.
SQL>SELECT DISTINCT JOB FROM EMP;
3) ALIAS : 컬럼명에 별칭을 붙여 사용
SELECT ENAME AS "사원이름",JOB AS "직업"
FROM EMP;
-- AS는 생략가능,ALIAS에 특수문자가 존재하면 반드시 ""로 묶어야 함
SELECT ENAME 사원이름,JOB "직 업"
FROM EMP;
SELECT EMPNO,SAL*12 "연봉"
FROM EMP;
4) 연산자
(1) 산술연산자 +,-,*,/
(2) 비교연산자 =, != , >,>=,<,<=
(3) 논리연산자 NOT,AND,OR
Q1) 부서번호가 10번인 부서의 사원들의 10%인상된 급여를 출력해 보세요.
SELECT DEPTNO,SAL,SAL*1.1 "10%인상된급여"
FROM EMP
WHERE DEPTNO=10;
Q2) EMP테이블의 사원중 근무하는 부서번호를 중복되지 않도록 출력해 보세요.
SELECT DISTINCT DEPTNO
FROM EMP;
5) SQL연산자
- IN,ANY,ALL,BETWEEN,LIKE,IS NULL,IS NOT NULL,EXISTS
(1) IN 연산자
-- 부서번호가 10번 또는 30번인 사원중에서 부서번호와 급여를 출력하시오.
SELECT DEPTNO,SAL
FROM EMP
WHERE DEPTNO=10 OR DEPTNO=30;
==>IN 연산자로 바꿔보면
SELECT DEPTNO,SAL
FROM EMP
WHERE DEPTNO IN(10,30); -- DEPTNO가 10번또는 30번인 데이터
(2) ANY 연산자
FROM EMP
WHERE DEPTNO=ANY(10,30); -- ANY는 10번또는 30번 둘중 하나만 일치하면 됨
(3) ALL 연산자
SELECT DEPTNO,SAL
FROM EMP
WHERE DEPTNO=ALL(10,30); -- ALL는 10번과 30번 모두 일치해야 됨
(4) BETWEEN A AND B : A와 B사이의 값
-- 급여가 1000부터 3000사이인 사원들의 사원번호,이름,급여를 출력하시오.
FROM EMP
WHERE SAL>=1000 AND SAL<=3000;
==> BETWEEN연산자로 바꿔보면
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 3000;
-- 사원번호가 7300부터 7500사이인 사원들의 모든 정보를 출력해 보세요.(BETWEEN사용)
WHERE EMPNO BETWEEN 7300 AND 7500;
-- 사원이름이 'J'로 시작하는 사원의 모든 정보를 조회하시오.
WHERE ENAME LIKE 'J%'; -- ENAME이 'J'로 시작하는 모든 데이터
-- 사원이름에 'E'를 포함하는 사원의 모든 정보를 조회하시오.
WHERE ENAME LIKE '%E%';
-- 사원이름이 'N'으로 끝나는 사원의 모든 정보를 조회하시오.
WHERE ENAME LIKE '%N';
-- 사원이름중 두번째 문자가 'L'인 사원의 모든 정보를 조회하시오.
WHERE ENAME LIKE '_L%';
-- 사원이름중 두번째 문자가 'L'이 아닌 사원의 모든 정보를 조회하시오.
WHERE ENAME NOT LIKE '_L%';
-- 커미션이 NULL인 사원들의 모든 정보를 조회하시오.
WHERE COMM IS NULL;
-- 커미션이 NULL이 아닌 사원들의 모든 정보를 조회하시오.
WHERE COMM IS NOT NULL;
(5) 결합연산자 (||) - 문자열연결
FROM EMP;
-- XXX의 직업은 XXX입니다.
FROM EMP;
Q1) 직업이 'MANAGER'이거나 또는 'SALESMAN'인 사원의 이름과 직업을 출력(IN사용)
SELECT ENAME,JOB
FROM EMP
WHERE JOB IN('MANAGER','SALESMAN');
Q2) 이름이 'S'로 시작하는 사원의 번호,이름,입사일,부서번호 출력
SELECT EMPNO,ENAME,HIREDATE,DEPTNO
FROM EMP
WHERE ENAME LIKE 'S%';
Q3) 입사일이 81년도인 사원의 입사일,이름 출력
SELECT HIREDATE,ENAME
FROM EMP
WHERE HIREDATE LIKE '81%';
Q4) 이름이 'A'로 시작하고 마지막글자가 'N'인 사원의 모든 정보를 출력
WHERE ENAME LIKE 'A%N';
Q5) 이름의 두번째 문자가 'A'인 사원의 이름을 출력하시오.
SELECT ENAME FROM EMP
WHERE ENAME LIKE '_A%';
Q6) 이름의 첫글자가 'A'로 시작하거나 부서번호가 30인 사원의 사원번호,이름,부서번호를 출력
SELECT EMPNO,ENAME,DEPTNO
FROM EMP
WHERE ENAME LIKE 'A%' OR DEPTNO=30;
Q7) 입사일이 81년도가 아닌 사원들의 입사일과 이름을 출력
SELECT HIREDATE,ENAME
FROM EMP
WHERE HIREDATE NOT LIKE '81%';
Q8) 급여가 2000에서 3000사이인 사원들의 사원번호,이름,급여출력(BETWEEN사용)
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;
'DBMS > 오라클기초' 카테고리의 다른 글
| 오라클 기초#6(Transaction Control Language) (0) | 2013.01.27 |
|---|---|
| 오라클 기초#5(Data Manipulation Language) (0) | 2013.01.27 |
| 오라클 기초#4 ( JOIN, ROWID, ROWNUM ) (0) | 2013.01.27 |
| 오라클 기초#3 (subquery) (0) | 2013.01.27 |
| 오라클 기초#2(함수) (0) | 2013.01.27 |