말없제이™'

*.tablespace생성
create tablespace 테이블스페이스명
datafile '~~물리적인경로' size ~
ex)
create tablespace jp2
datafile 'D:\study\oracle\jp2-1.dbf' size 100m

생생 조회.
select tablespace_name, file_name, user_bytes
from dba_data_files
where tablespace_name='JP2'

*.temporary tablespace생성
create temporary tablespace 테이블스페이스명
tempfile '~~물리적인경로' size ~
ex)
create temporary tablespace jp2_temp
tempfile 'D:\study\oracle\jp2temp-1.dbf' size 50m

*.user생성
create user user명
identified by 비번
[default tablespace 테이블스페이스명 사이즈]
[temporary tablespace 테이블스페이스명 사이즈] (일시적인 작업)
ex)
create user super
identified by man
default tablespace jp2
temporary tablespace jp2_temp

*.롤(role:권한들의 집합)부여
grant 권한,권한 to user명.
-dba :
-connet :
-resource :
ex)
grant connect, resource to super

*.scott의 table. 기존sql스크립으로 생성.
@D:\oracle\ora92\sqlplus\demo\demobld.sql

*.table생성
create table table명(
컬럼명 테이터타입(크기)[,..]
)
ex)
create table mtest(
m1 char(10),
m2 varchar2(10)
)

*.데이터 타입(문자/숫자/..)
*.문자
-char : 고정길이 문자형,2000byte,
-varchar2 : 가변길이 문자형,4000byte,
-long : 2g
ex)
  1* select m1,m2,m1||m2,m2||m1 from mtest
SQL> /

M1         M2         M1||M2               M2||M1
---------- ---------- -------------------- -----------------
abcde      qqq        abcde     qqq        qqqabcde
oracle     hahahoho   oracle    hahahoho   hahahohooracle

*.숫자
-number :
-number(p) : 저살슈 5저라,
-number(p,s) : 소수점 자리포함하여 5자리
ex)
create table stest(
s1 number,
s2 number(5),
s3 number(5,2)
);

*.날짜
-date
-timestamp
-timestamp with time zone
-timestamp with local time zone
ex)
create table dtest(
d1 date,
d2 timestamp
)

- - - - - - -
*.제약조건.

*.LOCK(데이터 읽기 일관성과 락)
-- User1장소에서 접속
update dept
set deptno=70
where dname='부서추가1';

-- User2장소에서 접속
update dept
set deptno=70
where dname='부서추가1';

User1실행후.. User2를 실행하면..
User2에서는 lock현상이 일어남.

User1에서 commit/rollback실행시..
user2에서 실행됨 확인.

질문거리. User2.에서 해소방법 따로없는지?

*.Table 생성
create table table명
(
컬럼명 데이터 타입[(크기)],
...

)

create table dept2
(
deptno number(2),
dname varchar2(14),
loc varchar2(13)
)

*.table 내용 및 구조 복사.
-- dept의 모든 내용과 테이블 구조 복사
create table dept3 as
elect * from dept

drop table dept3;
create table dept3 as
--select * from dept d join emp e using(deptno);
--select * from dept d natural join emp e;
--자동을 사용하면 됨.
--select * from dept d, emp e where d.deptno=e.deptno;
--select * from dept d join emp e on d.deptno=e.deptno;
--안되는 이유 열명중복.

-- dept4만들기.. 원본 dept 부서번호 30이하의 내용만 복사
create table dept4 as
select * from dept where deptno<=30

-- dept5만들기.. 원본 dept 부서번호, 부서명만 복사, 10~50사이만.
create table dept5 as
select deptno, dname from dept where deptno between 10 and 50

-- dept6만들기.. 원본 dept 10,20의 dno, dname
create table dept6 as
select deptno as dno, dname from dept where deptno in (10,20)

-- dept66복사
create table dept66 as
select * from  dept6

-- dept6 데이터 삭제.
delete from dept6;

-- rollback으로 복구
rollback;

-- 데이터 삭제 : truncate table 테이블명;
truncate table dept66;

-- rollback으로 복구
rollback;

select * from dept66;
--선택된 레코드가 없습니다.

-->rollback : 복구 가능한 delete(DML문장) / 안되는 truncate(DML문장아님)

*.table 삭제
drop table table명.
drop table dept5;

-- dept5만들기, dept table 구조만 복사(조건 거짓으로만들어서)
create table dept5
as select * from dept where 10=200;

--dept원본빼고 삭제.
--일일이 삭제함.

*.다중테이블에 다중로우입력.
-- 다중 insert 문법
insert [all | first]
 [when 조건 then]
 into 테이블명 values (컬럼명)
 [when 조건 then]
 into 테이블명 values (컬럼명)
 SubQuery
:all(전체)
:frist(만약 같은 결과값이라면 처음쓰이는곳에서만 표시.다른결과값은 표시)
ex)1조건 MILLER, 2조건 MILLER,FORD라면. 2조건테이블에는 FORD만 들어감.
즉 1조건만족하면 2조건에는 포함되지 않음.

--emp 내용물없는 구조만 복사
--사원번호,사원명,입사일,부서번호있는 empdept테이블 생성

--emp 내용물없는 구조만 복사
--사원번호,사원명,입사일 empdate테이블 생성

create table empdept
as
select empno, ename, hiredate, deptno from emp where 10=20;

create table empdate
as
select empno, ename, hiredate from emp where 10=20;

--emp의 사원번호 7900초과하는
--사원번호,사원명,입사일,부서번호있는 empdept입력.

--emp의 사원번호가 7900초과하는
--사원번호,사원명,입사일 empdate테이블 생성

--다중 1번사용.
insert all
into empdept values (empno, ename, hiredate, deptno)
into empdate values (empno, ename, hiredate)
select ename, empno, hiredate, deptno --순서는 상관없이 컬럼명만상관.
from emp e
where empno>7900;

--연습테이블 데이터 삭제.
delete from empdept;
delete from empdate;

-- 승기사원 입사일 오늘로 수정.
update emp
set hiredate=sysdate
where ename='이승기'

--부서번호 20이하이면 empdept테이블에
--입사일이 82년도이전 이면 empdate테이블에 넣기.
insert all
when deptno<=20 then
into empdept values (empno, ename, hiredate, deptno)
when hiredate <= to_date('1982-12-31','yyyy-mm-dd') then
into empdate values (empno, ename, hiredate)
select ename, empno, hiredate, deptno --순서는 상관없이 컬럼명만상관.
from emp e
where empno>7900;

--부서번호 10이면 empdept테이블에
--입사일이 82년도이전이고 부서번호10인경우는 제외한 empdate테이블에 넣기.
insert first
when deptno=10 then
into empdept values (empno, ename, hiredate, deptno)
when hiredate <= to_date('1982-12-31','yyyy-mm-dd') then
into empdate values (empno, ename, hiredate)
select ename, empno, hiredate, deptno --순서는 상관없이 컬럼명만상관.
from emp e
where empno>7900;

사용중인 IP확인
request.getRemoteAddr();

ipaddress ... 0:0:0:0:0:0:0:0:1로나올경우..
IPV6 : IP 버전6프로토콜때문에 나옴.

*.SubQuery와 DML의 만남
DML :
insert into 테이블명 () values ()
update 테이블명 set 컬럼='' where 조건.
delete 테이블명 where 조건.

--마케팅부서를 제주도로 이전
update dept
set loc='제주도' where dname='마케팅부'

--60번 부서의 지역을 마케팅부의 지역으로 이전
update dept
set loc=(select loc from dept where dname='마케팅부')
where deptno = 60

--50번 부서의 이름과 지역을 40번부서의 것과 동일하게.
update dept
set loc=(select loc from dept where deptno=40),
dname=(select dname from dept where deptno=40)
where deptno=50
--=>> 복수컬럼이면 동일한 갯수/타입이여야 함
update dept
set (loc,dname)=(select loc,dname from dept where deptno=40)
where deptno=50

--8000,'이승기','사원',50번부서 사원등록
insert into emp
(empno, ename, job, deptno)
values (8000,'이승기','사원',50);

--이승기 사원이 근무하는 부서명은 오락부, 지역은 60번동일
update dept
set (dname,loc) = (select '오락부',loc from dept where deptno=60)
where deptno=(select deptno from emp where ename='이승기');

--이씨가 근무하는 부서 삭제
delete from dept
where deptno = (select deptno from emp where ename like '이%')

*.Transaction
트랜잭션 개념 및 효과 정리해야 :
TCL : commit/rollbakck/savepoint

-- commit 트랜잭션 종료/시작
-- 50, '부서추가1' 추가  -----1
insert into dept
values(50, '부서추가1',null)

-- commit 트랜잭션 종료/시작
-- 60번 부서 지역을 서울 ----2
update dept
set loc='서울'
where deptno=60;
savepoint a1;
-- 50번부서 추가.  -----3
insert into dept
values (70,'부서추가2',null);
savepoint a2;
-- 50번부서지역을 서울로 ------ 4
delete from dept where deptno=50;
savepoint a3;

========
1.DML문법 숙지
insert into 테이블명 () values ()
update 테이블명 set 컬럼='' where 조건.
delete 테이블명 where 조건.

2.트랜잭션 개념정리
 트랜잭션의 일반적인 의미는 정보의 교환이나 데이터베이스 갱신 등
연관되는 작업들에 대한 일련의 연속을 의미하는데,
데이터베이스의 무결성이 보장되는 상태에서
요청된 작업을 완수하기 위한 작업의 기본 단위

 트랜잭션이 성공적으로 끝나기 위해서는
각 작업 단계들이 모두 완성되어야만 하며,
그랬을경우 비로소 이 새로운 내용이
데이터베이스에 실제로 반영된다.
 만약 그렇지 못했을 경우,
즉 어떤 한 작업 단계에서라도 오류가 발생하면
데이터베이스에는 아무런 수정이 이루어지지 않으며,
트랜잭션이 시작되기 이전 상태로 유지된다.

트랜잭션이 성공리에 끝났을때 이루어지는 데이터베이스의 갱신을
"commit" 이라고 부르며,
트랜잭션이 실패되었을때 데이터베이스의 수정 내용이 취소되는 것을
"rollback"이라고 부른다.
트랜잭션의 각 사건들을 관리 감독하는 프로그램을
트랜잭션 모니터라고 하며, 트랜잭션은 SQL에 의해 제공된다.
(ORACLE 에서는 savepoint라고 특정위치 정해줄수 있다.)

-- martin이 근무하는 부서의 부서명과 지역조회
select dname, loc
from dept
where deptno=
(
select deptno
from emp
where ename='MARTIN'
)

--3000이상의 급여를 받는 사람의 근무하는 부서의 부서번호,사원명,급여조회
select deptno, ename, sal
from emp
where deptno in
(
select distinct(deptno)
from emp
where sal>=3000
)
order by deptno, sal

1.scott의 급여 이상을 받은 사원의 이름,급여,커미션
select ename, sal, comm
from emp
where sal>=
(
select sal from emp where ename='SCOTT'
) and ename<>'SCOTT'

2.clerk업무에 종사하는 사원이 근무하는 부서명?
select dname
from dept
where deptno in
(
select distinct(deptno) from emp where job='CLERK'
)

3.이름에 L을 포함하는 사원과 동일한 부서에 근무하는 사원 모든정보
select *
from emp
where deptno in
(
select distinct(deptno) from emp where ename like '%L%'
)
order by deptno asc, ename asc

4.DALLAS소재부서에 근무하는 사원 모든정보
select *
from emp
where empno in
(
select distinct(empno) from dept where loc='DALLAS'
)

5.SALES부서에 근무하는사원들의 모든정보
select *
from emp
where deptno in
(
select distinct(deptno) from dept where dname='SALES'
)

6.KING에게 보고하느 모든 사원들의 이름과 급여조회
select ename, sal
from emp
where mgr in
(
select distinct(empno) from emp where ename='KING'
)

7.자신의 급여가 전체사원의 평균급여보다 많고,
이름에 S가 들어가는 사원과 동일한 부서에 근무한 사원들의 모든정보
select *
from emp
where empno in
(
select distinct(empno)
from emp
where ename like '%S%'
and sal>=(select avg(sal) from emp)
)

DML(조작어)
insert into 테이블명
[(컬럼)] values (내용)

update 테이블명
set 컬럼=내용
[where 조건]

delete [from] 테이블명 where 조건.
(실제 DB에서삭제는 commit을 통해완료됨.)

-- 개발부이거나 경리부이거나 지역이 미확정된 부서 삭ㄱ제
delete dept where dname in ('개발부','연구원') or loc is null

*.subquery(서브쿼리, 이너쿼리)
mainquery~~ ~(subqurey) ~~ ;
from 안에 들어가는 서버쿼리를 인라인뷰라고 부름.

-단일행부속질의
-복수행부속질의 : in, any, all, exists

--miller가 근무하는 부서의 사원명,부서번호 조회
select ename, deptno
from emp
where deptno in
(
select deptno
from emp
where ename='MILLER'
);

ENAME          DEPTNO
---------- ----------
CLARK              10
KING               10
MILLER             10

--7782 사원이 받는 급여이상을 받는 사원번호,사원명,급여,커미션 조회
 1  select empno, ename, sal, comm
 2  from emp
 3  where sal >= (
 4  select sal
 5  from emp
 6  where empno=7782
 7* )
 8  /

    EMPNO ENAME             SAL       COMM
--------- ---------- ---------- ----------
     7566 JONES            2975
     7698 BLAKE            2850
     7782 CLARK            2450
     7788 SCOTT            3000
     7839 KING             5000
     7902 FORD             3000

-- MANAGER일을 하는 사원들의 평균급여보다 많이받는 모든정보조회

  1  select *
  2  from emp
  3  where sal>=
  4  (
  5  select avg(sal)
  6  from emp
  7  group by job
  8  having job='MANAGER'
  9* )
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 81/04/02       2975                    20
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7788 SCOTT      ANALYST         7566 82/12/09       3000                    20
      7839 KING       PRESIDENT            81/11/17       5000                    10
      7902 FORD       ANALYST         7566 81/12/03       3000                    20

*.서브쿼리 연산자
- in : 나열된 조건과 같은것.
- all : 조건의 모든결과일치
- any/some : 조건 하나이상 조건 만족.
- exists : 하나라도 값이 있는지.

-- MANAGER일을 하는 사원들의 급여보다 많이받는 모든정보조회
 select *
 from emp
 where sal>any
 (
 select sal
 from emp
 where job='MANAGER'
 )
 /

 EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
------ ---------- --------- ---------- -------- ---------- ---------- ----------
  7566 JONES      MANAGER         7839 81/04/02       2975                    20
  7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  7788 SCOTT      ANALYST         7566 82/12/09       3000                    20
  7839 KING       PRESIDENT            81/11/17       5000                    10
  7902 FORD       ANALYST         7566 81/12/03       3000                    20

-- 7698사원 밑에서 일하는 사원번호,사원명,부서번호,부서명 조회

  select e.empno, e.ename, e.deptno, d.dname
  from emp e join dept d on e.deptno=d.deptno and e.mgr=7698
> /

  EMPNO ENAME          DEPTNO DNAME
------- ---------- ---------- --------------
   7499 ALLEN              30 SALES
   7521 WARD               30 SALES
   7654 MARTIN             30 SALES
   7844 TURNER             30 SALES
   7900 JAMES              30 SALES

select e.empno, e.ename, e.deptno, d.dname
from emp e, dept d
where e.empno in
(
select empno
from emp
where mgr=7698
)
and e.deptno=d.deptno

--------------
research 부서의 근무하는 모든 사원정보 조회
select e.*
from emp e join dept d on e.deptno=d.deptno and d.dname='RESEARCH'

select e.*
from emp e
where deptno in
(
select deptno
from dept d
where d.dname='RESEARCH'
)/

-DML : 조작어(insert, delete, update)
-DDL :
*.객체생성
create 객체타입 객체명( ... )

*.객체수정
alter 객체타입 객체명
 -추가/변경/삭제
-사용시 주의점 : 컬럼데이터 타입, 컬럼크기, 제약조건 확인해서 수정해아함.

alter user user명
[idenfified by 비밀번호]
[account lock]

*.객체삭제
drop 객체타입 객체명( ... )

*.데이터 입력
insert into 테이블명 [(컬럼명[,..])] values (컬럼명[,..])
- '~' : 문자
- 컬럼타입,컬럼크기[,제약조건(not null,unique,pk,fk,ck)]도 확인
- null : 묵시적/명시적(null,'')

insert into dept (deptno, dname, loc)
values (50,'It부','서울')

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values(8000,'원빈','홍보모델',7934,'2009/07/01',3500,350,30)

insert into emp
values(8100,'송해교','인사부',7902,'2009/01/01',3500,300,20)

70,신속정확한경리부,광주
insert into dept values (70,'정확한경리부','광주')

*.데이터 삭제
delete [from] 테이블명 [where 조건]

*.데이터 수정
update 테이블명
set 컬렴명=새값
[where 조건]

--50번 부서의 부서명은 개발부, 인천으로 이전

*.변수명/치환변수(&,&&사용)
&변수명 = 변수값.
&&변수명 = 변수값 : 기존사용하던 변수인경우 묻지않고 치환됨.

emp table에
8200,이승기,개발자
8300,김동률, 연구원
8400,임의명,임의업무
8500,임의명,임의업무

insert into emp (empno, ename, job)
values (&no, '&na', '&job')

*.join
*-self join : 자기자신의 테이블에서 특정값호출위해 조인.

-- 사원번호, 사원명, 상사의 사원번호, 상사이름
ex)
select a.empno as "사원번호", a.ename as "사원명", b.mgr as "상사사번", b.ename as "상사이름"
from emp a, emp b
where a.empno = b.mgr

-- 사원명, 부서번호, 부서이름 조회
select e.ename, e.deptno, d.dname
from emp e join dept d on e.deptno=d.deptno

40번 부서 사람이 없으니, 조회 안됨.

*-outer join : (+) 연산자로 추가해서 반대쪽에 있는 데이터 null값도 보여줌(반대편 컬럼전체보여줌).
ex)
select e.ename, e.deptno, d.dname
from emp e, dept d
where e.deptno(+)=d.deptno
ex)
select e.ename, e.deptno, d.dname
from emp e full outer join dept d
on e.deptno=d.deptno(+)
ex)
select e.ename, e.deptno, d.dname
from emp e left outer join dept d
on e.deptno=d.deptno(+)
ex)
select e.ename, e.deptno, d.dname
from emp e right outer join dept d
on e.deptno(+)=d.deptno

--
--1.EQUI
--EMPNO, ENAME, DNAME, LOC
select e.empno, e.ename, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno;

--2.NATURAL
--EMPNO, ENAME, DNAME
select e.empno, e.ename, d.dname
from emp e natural join dept d;

--3.JON USING
--EMPNO, ENAME, DNAME, LOC
select e.empno, e.ename, d.dname, d.loc
from emp e join dept d using(deptno);

--4.JOIN ON
--EMPNO, JOB, SAL, DNAME, LOC
select e.empno, e.job, e.sal, d.dname, d.loc
from emp e join dept d on e.deptno=d.deptno;

--1.사원들의 이름, 부서번호, 부서이름
select e.ename, d.deptno, d.dname
from emp e join dept d on e.deptno=d.deptno;

--2.부서번호가 30인 사람들의 이름, 직급, 부서번호, 부서위치
select e.ename, e.job, e.deptno, d.loc
from emp e join dept d on e.deptno=d.deptno
where e.deptno=30;

--3.커미션 받는 사람이름, 커미션, 부서이름, 부서위치
select e.ename, e.comm, d.deptno, d.loc
from emp e join dept d on e.deptno=d.deptno
where comm is not null;

--4.DALLAS에서 근무하는 사원 이름, 직급, 부서번호, 부서이름
select e.ename, e.job, e.deptno, d.dname
from emp e join dept d on e.deptno=d.deptno
where d.loc='DALLAS';

--5.이름에 A가 들어가는 사원들의 이름과 부서이름
select e.ename, d.dname
from emp e join dept d on e.deptno=d.deptno
where e.ename like '%A%';

--6.사원이름과 직급, 급여, 급여등급
select e.ename, e.job, e.sal, s.grade
from emp e join salgrade s on e.sal between s.losal and s.hisal;

--7.사원이름, 부서번호, 해당사원과 같은부서에 근무하는 사원을 출력(self join)
select e.ename as "나의 이름", e.deptno, e1.ename as "그대의 이름"
from emp e join emp e1 on e.deptno=e1.deptno and e.ename<>e1.ename
order by e.ename asc;