Hello, World?

mssql 테이블 복사 쿼리 입니다.

1. 테이블을 생성하면서 테이블의 데이타 복사

select * into 생성될테이블명 from 원본테이블명

테이블 구조만 복사하겠다면

select * into 생성될테이블명 from 원본테이블명 where 1=2

2. 테이블이 이미 생성되어 있는경우 데이타만 복사

insert into 카피될테이블명 select * from 원본테이블명

특정 데이타만 복사 하겠다면

insert into 카피될테이블명 select * from 원본테이블명 where 검색조건

약 천 육백만개의 데이터를 지워야 하는데..

디비 부하때문에 천개씩 지워야 하는상황 :(

천개씩 몇번 하다보니 이건 아니다 싶어서.... 

그래서 저장 프로시저를 만들었다. 

사실 프로시저 안에서 반복문이 돌아가는건 안좋은 거랜다. 

밖의 실행문에서 반복하여 프로시저를 실행해야 옳다고 하니 참고 할 것.

2차출처: http://ash84.tistory.com/335

[펌] MS-SQL : 저장 프로시저 만들기

일반 실행 시 작업 방법

1. 구문변환 작업(오류여부 검사.)

2. 쿼리문의 테이블이나 컬럼명등이 정확한지 확인

3. 해당 개체 소유자의 권한을 점검

4. 최적화

5. 컴파일

저장프로시저 실행 시 작업 방법

일반쿼리문과 같은 과정을 거친다. 

그러나 두번 째 실행 부터는 최초에 실행시 만들어 놓았던 실행 계획을 남겨 놓기 때문에 

쿼리문과 같은 과정을 거치지 않아에 수행 속도가 훨씬 빠르다.

프로시저?

많이 쓰는 SQL 쿼리들을 미리 정의해서 컴파일 해 놓은 SQL 집합 혹은 그룹이다. 

성능, 보안, 유지관리가 용이하다는 장점이 있다. 

DB파일이 예전 백업본이라서 SP가 없다.. 우짜노........

디비를 확 내려서 백업받을 수도 없고... ㅠㅠㅠㅠㅠ

참고 :

http://msdn.microsoft.com/ko-kr/library/ms187926(v=SQL.100).aspx

http://gomdolinara.com/dokuwiki/doku.php/dev/dbms/mssql/procedure

------------------------------------------------------------------

VPN 접속해보니까 프로시저 파일이 있당 ㅎㅎㅎㅎ

근데 쿼리 날리니까 이미 존재하는 프로시저라는데.. 

MS SQL 안에 프로그래밍 기능 - 저장 프로시저 - 시스템 저장 프로시저 가니까 다 있넿ㅎㅎㅎ

삽질 왕 b

org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 호스트 192.168.0.78, 포트 1433에 대한 TCP/IP 연결에 실패했습니다. 오류: "Connection refused: connect. 연결 속성을 확인하고 SQL Server의 인스턴스가 호스트에서 실행되고 있고 포트에서 TCP/IP 연결을 허용하고 있는지 확인하십시오. 또한 포트에서 TCP 연결을 차단하고 있는 방화벽이 없는지 확인하십시오.".
### The error may exist in com/maeil/cvo/sqlMap/Login.xml
### The error may involve Login.getLogin
### The error occurred while executing a query
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 호스트 192.168.0.78, 포트 1433에 대한 TCP/IP 연결에 실패했습니다. 오류: "Connection refused: connect. 연결 속성을 확인하고 SQL Server의 인스턴스가 호스트에서 실행되고 있고 포트에서 TCP/IP 연결을 허용하고 있는지 확인하십시오. 또한 포트에서 TCP 연결을 차단하고 있는 방화벽이 없는지 확인하십시오.".
예외 발생 : null====java.lang.NullPointerException
예외 발생 :
예외 발생 : /login.do

하............... 이거 고치려고 TCP/IP 포트 열고 서비스 실행 중인지 확인하고 MS SQL 설정 백번하고...

결론은

 sql server configuration Namager 의 TCP/IP 속성의 IP ALL 값의 TCP 포트값을 1433으로 변경하면 된다!!!!

함 니ㅡ란 ㅇ허 ㅠㅠㅠㅠ 똥멍청이 ㅠㅠㅠ

평소에 이렇게 바보는 아닌데.. 똑소리 난다는 얘기 많이 듣는데.. 아닌가 .. ㅋㅋㅋㅋ

아무튼 신입사원은 똥멍청이고 일년 지나야 멍청이 된다는 얘기를 몸소 체험중인 입사 삼주째 신입 ㅠㅠ 슬프다 ㅠㅠㅠ

MS SQL은 한번도 안해본건데.. 하니까 되긴되네 ㅠㅠㅠㅠㅠ

http://www.golinuxhub.com/2013/07/the-tcpip-connection-to-host-127001.html

데이터베이스의 CRUD 중 가장 중요한 것은 무엇일까?

중요도에 따른 상중하를 나눠보자. 

나는 처음에 

Select    下

Insert     中

Update    上

Delete    上

이라고 생각했다. 

PK 에 FK 엮이고 하면 아무래도 업데이트나 삭제가 복잡해지니까..?

근데 여기에는 함정이 있다. 

중요도에 따른 상중하를 나눠보자고 했지만, 그 기준을 제시하지 않았다. 

위의 상중하는 만들때 얼마나 쿼리가 귀찮은가를 기준으로 중요도를 결정한거같다.

데이터 정합성을 기준으로 CRUD의 중요도를 다시 매긴다면 어떨까?

Select    下/中

Insert     上

Update    中/上

Delete    上

으로 매길 수 있다고 본다. 

select 의 경우 쿼리를 잘못 뽑는다고 해서 DB에 있는 값이 변하지 않기 때문에下,

中일때는 통계성 쿼리일 때이다. 이 통계가 맞는지 틀린지를 검증하기 위해서는 일정 시간이 소요되기 때문.

insert 나 delete 는 잘못 집어넣거나 지운 데이터가 있다면 select고 뭐고.. 일단 아 망했어요 라고 보면 된다 ^^!

잘못 된 데이터를 가지고 쿼리를 아무리 작성해봤자, 정확한 결과값이 나오지 않기 때문

업데이트의 경우에도 중~상의 중요도를 차지하는 이유는 역시 데이터 정합성때문 . 

쿼리는 어렵다 ㅠㅠㅠㅠㅠㅠ 열심히 하자 

DECODE

DECODE(테이블.컬럼, 비교대상, true일때, false일때 출력)

DECODE({colum|expression,search1,result1[,search2,result2]...[,default]})

if문 같은 느낌...? 첫번쨰 값이 비교대상과 같으면 세번째꺼, 틀리면 네번째거 출력

RANK()OVER()

특정 컬럼으로 순위를 부여할 때 사용

옵션

PARTITION BY

ORDER BY DESC (ASE)

NULLS FIRST

NULLS LAST

UNION/UNION ALL

UNION: 두 table 집합의 합집합을 보여주며 중복 row 제거 (sort 기능을 수행함)

UNION ALL: 두 table의 합집합을 보여주며 중복 된 row도 모두 다 보여준다 (sorting하지 않음)

회사 가니까 쿼리문이 제일 어렵다...

공부 할 때는 기껏해야 열줄 스무줄인데 

실전에선 테이블 정의서 하나만 해도 A4용지 세네장 분량은 되는거 같다. 

흐흐허하흥힇.... 난 쿼리를 제일 못하는데, ㅋㅋㅋㅋㅋ

열심히 해야겠다. 쿼리 잘하는 사람들 부럽다.. 뀨뀨

SELECT * FROM TAB;
DESC DEPT;
SELECT * FROM DEPT;
DESC EMP;

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP;
DESC EMP;

SELECT EMPNO AS 사원번호,ENAME 사원이름,SAL 본봉,SAL*1.2 AS 총급여 FROM EMP;

SELECT EMPNO,ENAME,COMM,COMM+100, SAL,SAL+COMM AS 총급여  FROM EMP;

SELECT ENAME ||' 의 직업은 '||JOB || '입니다.' AS "이름 직업" FROM EMP;
-- 중복행제거
/*
중복행제거
여러줄주석
*/
SELECT DISTINCT JOB FROM EMP;

SELECT EMPNO,ENAME,JOB,DEPTNO
FROM EMP
WHERE DEPTNO=30;

SELECT * 
FROM EMP
WHERE JOB='CLERK';
SELECT * FROM EMP;
SELECT * FROM EMP WHERE HIREDATE='81/11/17';

SELECT *
FROM EMP
WHERE SAL<=1000;

SELECT *
FROM EMP
WHERE SAL>=100 AND SAL <=2000;

SELECT EMPNO,ENAME,SAL 
FROM EMP 
WHERE SAL BETWEEN 1000 AND 2000;

SELECT EMPNO,ENAME,COMM 
FROM EMP
WHERE COMM IS NULL;

SELECT EMPNO,ENAME,COMM
FROM EMP
WHERE EMPNO IN(7369,7654,7934);

SELECT EMPNO,ENAME,JOB,COMM
FROM EMP
WHERE JOB IN('CLERK','SALESMAN');


SELECT *
FROM EMP
WHERE ENAME LIKE 'A%';

SELECT *
FROM EMP
WHERE ENAME LIKE '%T%';

SELECT *
FROM EMP
WHERE ENAME LIKE '_L%';

SELECT *
FROM EMP
WHERE ENAME LIKE '%\_%' ESCAPE '\' ;

SELECT * 
FROM EMP
WHERE COMM IS NULL;

SELECT * 
FROM EMP
WHERE COMM IS NOT NULL;

/*
INSERT INTO EMP VALUES
(9000,'M_BEER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DELETE FROM EMP WHERE EMPNO=9000;
commit;
*/
SELECT *
FROM EMP
WHERE ENAME LIKE '%\_%' ESCAPE '\';

SELECT * FROM EMP;

SELECT EMPNO,ENAME,HIREDATE FROM EMP
ORDER BY HIREDATE ASC;

SELECT EMPNO,ENAME,SAL*12 FROM EMP
ORDER BY SAL*12 ASC;

SELECT EMPNO,ENAME,SAL*12 FROM EMP
ORDER BY 3 ASC;

SELECT EMPNO,ENAME FROM EMP
ORDER BY ENAME DESC;

SELECT EMPNO,ENAME,SAL FROM EMP
ORDER BY SAL DESC ,EMPNO ASC;
--단일행함수

DESC DUAL;
SELECT DUMMY FROM DUAL;

SELECT ROUND(45.528,2),ROUND(45.582,0),ROUND(45.582,-1) FROM EMP;
SELECT ROUND(45.528,2),ROUND(45.582,0),ROUND(45.582,-1) FROM DUAL;

SELECT TRUNC(45.528,2) ,TRUNC(45.528,0),TRUNC(45.528,-1),TRUNC(45.528,-2) FROM DUAL;

SELECT MOD(42342,7) FROM DUAL;

SELECT EMPNO,ENAME,SAL,MOD(SAL,10) FROM EMP;

SELECT CEIL(15.3),CEIL(-15.3) FROM DUAL;
SELECT FLOOR(15.3),FLOOR(-15.3) FROM DUAL;

SELECT HIREDATE FROM EMP;

SELECT SYSDATE FROM DUAL;
SELECT SYSTIMESTAMP FROM DUAL;


SELECT EMPNO,ENAME,SYSDATE,HIREDATE,TRUNC((SYSDATE - HIREDATE)/7,0) FROM EMP;

SELECT EMPNO,ENAME,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE),0) FROM EMP;
SELECT EMPNO,ENAME,HIREDATE,ADD_MONTHS(HIREDATE,6) FROM EMP;
SELECT SYSDATE,ADD_MONTHS(SYSDATE,6) FROM DUAL;
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
SELECT SYSDATE,NEXT_DAY(SYSDATE,'SUN') FROM DUAL;
ALTER SESSION SET NLS_LANGUAGE='KOREAN';

SELECT EMPNO,ENAME,HIREDATE,ROUND(HIREDATE,'MONTH'),SYSDATE,ROUND(SYSDATE,'MONTH') FROM EMP;
SELECT EMPNO,ENAME,HIREDATE,ROUND(HIREDATE,'YEAR'),SYSDATE,ROUND(SYSDATE,'YEAR') FROM EMP;

SELECT EMPNO,ENAME,HIREDATE,TRUNC(HIREDATE,'MONTH'),SYSDATE,TRUNC(SYSDATE,'MONTH') FROM EMP;
SELECT EMPNO,ENAME,HIREDATE,TRUNC(HIREDATE,'YEAR'),SYSDATE,TRUNC(SYSDATE,'YEAR') FROM EMP;
SELECT * FROM EMP;
--변환함수
SELECT EMPNO,ENAME FROM EMP WHERE EMPNO='7839';
SELECT EMPNO,ENAME FROM EMP WHERE EMPNO=TO_NUMBER('7839');

SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE hiredate='82/01/23';
-- 1982-01-23 , 1982 01 23 
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE hiredate=to_date('82/01/23','RR/MM/DD');
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
SELECT EMPNO,ENAME,HIREDATE 
FROM EMP 
WHERE hiredate=to_date('1982 JANUARY 23','YYYY MONTH DD');

select hiredate,to_char(hiredate,'YYYY/MM/DD') from emp;
select sysdate,to_char(sysdate,'YEAR MONTH DD DAY HH:MI:SS') from dual;

select empno,ename,sal,to_char(sal,'L99,999.99') from emp;
select empno,ename,sal,to_char(sal,'$99,999.90') from emp;

select empno,ename,sal,comm,sal*12+comm, sal*12+NVL(comm,0) 총급여 from emp;
select empno,ename,sal,comm,sal*12+comm, sal*12+NVL2(comm,comm,0) 총급여 from emp;
select empno,ename,sal,comm,sal*12+comm, NVL2(comm,sal*12+comm,sal*12) 총급여 from emp;

select empno,ename,sal,
  case job when 'ANALYST' then sal*1.1
           when 'CLERK' then sal*1.2
           when 'MANAGER' then sal*1.3
           when 'PRESIDENT' then sal*1.4
           when 'SALESMAN' then sal*1.5
           else sal
  end "급여"
from emp;  

select empno,ename,sal,
  case     when job='ANALYST' then sal*1.1
           when job='CLERK' then sal*1.2
           when job='MANAGER' then sal*1.3
           when job='PRESIDENT' then sal*1.4
           when job='SALESMAN' then sal*1.5
           else sal
  end "급여"
from emp;  


select empno,ename,sal,
  case     when sal>=1000 then sal*1.1
           else sal
  end "급여"
from emp;  

--GROUP FUNCTION
select sum(sal),avg(sal),min(sal),max(sal) from emp;
select count(*),count(comm),count(nvl(comm,0)),sum(comm),round(avg(nvl(comm,0)),2) from emp;

select deptno,avg(sal) 
from emp
group by deptno;

select deptno,job,avg(sal)
from emp
group by deptno,job
order by deptno asc;

select deptno ,sum(sal)
from emp
where sal>800
group by deptno
having sum(sal)>9000
order by deptno asc;

 SELECT DEPTNO ,
        DECODE( JOB , 'CLERK', SAL) CLERK,
        DECODE( JOB , 'MANAGER', SAL) MANAGER FROM EMP;
--ANSI JOIN

SELECT EMPNO,ENAME,DNAME
FROM DEPT,EMP;

SELECT EMP.EMPNO,EMP.ENAME,DEPT.DNAME,DEPT.LOC
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO; 

SELECT e.EMPNO,e.ENAME,e.DEPTNO,d.DEPTNO,d.DNAME,d.LOC
FROM EMP e,DEPT d
WHERE e.DEPTNO=d.DEPTNO AND e.EMPNO = 7788;

SELECT e.EMPNO,e.ENAME,e.SAL,s.GRADE
FROM EMP e,SALGRADE s
WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL;


SELECT e.EMPNO,e.ENAME,e.SAL,s.GRADE
FROM EMP e,SALGRADE s
WHERE e.SAL>=s.LOSAL AND e.SAL<=s.HISAL;




SELECT e.EMPNO,e.ENAME,e.DEPTNO,d.DEPTNO,d.DNAME,d.LOC
FROM EMP e,DEPT d
WHERE e.DEPTNO(+)=d.DEPTNO;

INSERT INTO EMP VALUES(9999,'홍길동','MANAGER',7839,sysdate,2450,NULL,NULL);

SELECT * FROM EMP;

SELECT e.EMPNO,e.ENAME,e.DEPTNO,d.DEPTNO,d.DNAME,d.LOC
FROM EMP e,DEPT d
WHERE e.DEPTNO = d.DEPTNO(+);

rollback;

select * from emp;

select a.ename  "사원" ,b.ename "관리자"
from emp a,emp b 
where a.mgr = b.empno(+);

--SQL 1999 JOIN
select empno,ename,dname,loc 
from emp cross join dept;

select empno,ename,dname,loc 
from emp natural join dept;

select empno,ename,dname,loc 
from emp join dept using(deptno);

select empno,ename,dname,loc
from emp join dept
on emp.deptno=dept.deptno and dept.deptno=10;

select empno,ename,sal,dname,loc
from emp join dept
on emp.deptno=dept.deptno and sal in(800,3000,5000);

select e.empno,e.ename,d.dname,e.sal,s.grade
from emp e join dept d
           on e.deptno=d.deptno
           join salgrade s
           on e.sal between s.losal and s.hisal;

select a.ename "사원", b.ename "관리자"
from emp a join emp b
     on a.mgr=b.empno;     
     
INSERT INTO EMP VALUES(9999,'홍길동','MANAGER',7839,sysdate,2450,NULL,NULL);
     
select empno,ename,dname,loc
from emp right outer join dept
on emp.deptno=dept.deptno;

select empno,ename,dname,loc
from emp left outer join dept
on emp.deptno=dept.deptno;     
     
select empno,ename,dname,loc
from emp full outer join dept
on emp.deptno=dept.deptno;  

select sal from emp where ename='SCOTT';

select empno,ename,sal from emp where sal > 3000;


select empno,ename,sal 
from emp 
where sal >(select sal 
            from emp 
            where ename='SCOTT');
--DML
insert into dept(deptno,dname,loc) values(90,'인사과','서울');
insert into dept values(80,'인사과','서울');
insert into dept(loc,dname,deptno) values('서울','인사과',70);
select * from dept;
desc dept;
/*
desc dept
이름     널        유형           
------ -------- ------------ 
DEPTNO NOT NULL NUMBER(2)    
DNAME           VARCHAR2(14) 
LOC             VARCHAR2(13) 
*/
insert into dept(deptno,dname) values(91,'인사과');
insert into dept values(92,'인사과',null);

desc emp;
/*
desc emp
이름       널        유형           
-------- -------- ------------ 
EMPNO    NOT NULL NUMBER(4)    
ENAME             VARCHAR2(10) 
JOB               VARCHAR2(9)  
MGR               NUMBER(4)    
HIREDATE          DATE         
SAL               NUMBER(7,2)  
COMM              NUMBER(7,2)  
DEPTNO            NUMBER(2)  
*/
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
       values(9000,user,'연구원',7839,sysdate,5000,null,90);

select * from emp where empno >=9000;

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
       values(9001,'홍길동','MANAGER',7839,'2012/03/22',2000,null,90);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
       values(9002,'임꺽정','MANAGER',7839,
       to_date('2012/03/20','YYYY/MM/DD'),2000,null,30);

create table dept_1(deptno number(2),dname varchar2(14),loc varchar2(13));
desc dept_1;

insert into dept_1(deptno,dname,loc) select deptno,dname,loc from dept;
select * from dept_1;

select * from dept;

update dept 
set dname='경리과',loc='부산'
where deptno=90;

select * from emp where empno=9001;

update emp
  set job=(select job 
           from emp 
           where empno=7900 
          ),
          sal=(select sal
              from emp 
              where empno=7844
              )
where empno=9001; 

update dept_1
set dname=(
            select dname 
            from dept 
            where deptno=30)
where deptno=(
              select deptno 
              from dept 
              where dname='SALES');

select *  from dept_1 where deptno=30;
select * from dept_1;

delete from emp 
where deptno=91;

delete emp 
where deptno=(select deptno 
              from dept 
              where dname='경리과');

select deptno,dname from dept;
select * from emp;

rollback;       

select * from emp;
select * from dept;
delete from dept where deptno=91;
delete from dept where deptno=70;
delete from dept where deptno=80;


delete from dept where deptno=92;
select * from dept;
commit;
       
delete from emp; 
select * from emp;
rollback;
--DDL

create table dept_2(deptno number(2),dname varchar2(10),loc varchar2(10));
desc dept_2;
create table scott.dept_3(deptno number(2),dname varchar2(10),loc varchar2(10));
desc dept_3;

create table def_table(num number(2),writeday date);

create table def_table2(num number(2),writeday date default sysdate);

insert into def_table(num) values(1);
insert into def_table(num,writeday) values(2,sysdate);

insert into def_table2(num) values(1);
insert into def_table2(num,writeday) values(2,default);

select * from def_table;
select * from def_table2;

create table new_tmp(tmp_id number(2),w_day  date,w_day2 timestamp);
desc new_tmp;
insert into new_tmp values(1,sysdate,sysdate);
select * from new_tmp;

create table new_tmp2(tmp_id number ,w_day timestamp  with time zone);
desc new_tmp2;
insert into new_tmp2 values(1,sysdate);
insert into new_tmp2 values(2,sysdate);
insert into new_tmp2 values(3,sysdate);
insert into new_tmp2 values(4,sysdate);
insert into new_tmp2 values(5,sysdate);
select * from new_tmp2;

create table new_tmp3(tmp_id number ,w_day timestamp  with local time zone);
insert into new_tmp3 values(1,sysdate);
insert into new_tmp3 values(2,sysdate);
insert into new_tmp3 values(3,sysdate);
insert into new_tmp3 values(4,sysdate);
insert into new_tmp3 values(5,sysdate);
insert into new_tmp3 values(6,sysdate);
insert into new_tmp3 values(7,sysdate);
insert into new_tmp3 values(8,sysdate);
insert into new_tmp3 values(9,sysdate);
insert into new_tmp3 values(10,sysdate);
select * from new_tmp3;

SELECT SESSIONTIMEZONE, DBTIMEZONE, localtimestamp FROM dual;
alter session set time_zone = '+09:00';
commit;

create table new_tmp4
( time1  interval year to month,
  time2 interval day to second);
desc new_tmp4;  

insert into new_tmp4 values( 
interval '10-1' year to month,
interval '10 10:10:10' day to second);

select  to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'),
        to_char(sysdate+time1,'YYYY/MM/DD HH24:MI:SS'),
        to_char(sysdate+time1+time2,'YYYY/MM/DD HH24:MI:SS')
from new_tmp4;

create table deptA 
as
select * from dept;

desc deptA;
select * from deptA;

create table deptB(no,name)
as
select deptno,dname from dept;

desc deptB;
select * from deptB;

create table deptC
as
select * from dept where 1=2;

select count(*) from deptC;
--constraint

create table sawon(
  s_no number(4),
  s_name varchar2(10) not null,
  s_hiredate date constraint sawon_s_hiredate_nn not null
);
desc sawon;
select * from user_constraints where table_name='SAWON3';

insert into sawon values(1,'길동',sysdate);
insert into sawon values(2,'춘향',sysdate);

select * from sawon;
--column level
create table sawon2 (
  s_no number(2),
  s_name varchar2(10),
  s_email varchar2(20) constraint sawon2_s_email_uk unique
);
insert into sawon2 values(1,'홍길동','adb@abc.com');
insert into sawon2 values(2,'유관순','adb@abc.co.kr');
--insert into sawon2 values(3,'강감찬','adb@abc.co.kr');
-- table level
create table sawon3 (
  s_no number(2),
  s_name varchar2(10),
  s_email varchar2(20),
  constraint sawon3_s_email_uk unique(s_email)
);

create table sawon4 (
  s_no number(2),
  s_name varchar2(10),
  s_email varchar2(20),
  constraint sawon4_s_email_s_name_uk unique(s_name, s_email)
);

INSERT INTO SAWON4 VALUES (1, '홍길동', 'ABC@ABC.CO.KR');
INSERT INTO SAWON4 VALUES (2, '유관순', 'ABC@ABC.CO.KR');
-- INSERT INTO SAWON4 VALUES (3, '유관순', 'ABC@ABC.CO.KR'); --unique constraint (%s.%s) violated
INSERT INTO SAWON4 VALUES (3, '이순신', NULL);
INSERT INTO SAWON4 VALUES (4, NULL, NULL);
INSERT INTO SAWON4 VALUES (5, NULL, NULL); --유니크에 안걸려서 중복삽입 가능.
SELECT * FROM SAWON4;

--12.16 PRIMARY KEY 제약조건 
/*
: 테이블에 기본키를 의미하며, 한 개의 테이블에는 오직 한 개의 기본키를 만들 수 있다.
: UNIQUE + NOT NULL 제약조건 특성을 갖는다.
: 테이블 수준 및 컬럼 수준 , 2 가지 방법 모두 사용 가능하다.
: UNIQUE 와 마찬가지로 INDEX 가 자동 생성된다.
 CONSTRAINT 이름 UNIQUE,NOTNULL,PRIMARY 해주면 된다.
*/
CREATE TABLE SAWON5 (
  S_NO NUMBER(2) CONSTRAINT SAWON5_S_NO_PK PRIMARY KEY,
  S_NAME VARCHAR2(10),
  S_SAL NUMBER(10)
); --방법1
INSERT INTO SAWON5 VALUES(1, '이순신', 2000);
-- INSERT INTO SAWON5 VALUES(1, '김순신', 2000); --"unique constraint (%s.%s) violated"

CREATE TABLE SAWON6 (
  S_NO NUMBER(2),
  S_NAME VARCHAR2(10),
  S_SAL NUMBER(10),
  CONSTRAINT SAWON6_S_NO_PK PRIMARY KEY (S_NO)); -- 방법2
select * FROM user_constraints WHERE TABLE_NAME = 'SAWON6';

--12.17FOREIGN KEY 제약조건
/*
: 참조 무결성 제약조건이라고도 한다.
: 반드시 다른 테이블의 기본키나 또는 UNIQUE 컬럼의 값을 참조하도록 제한한다. ( NULL허용)
*/
CREATE TABLE DAPT2
( DEPTNO NUMBER(2) CONSTRAINT DEPT2_DEPTNO_PK PRIMARY KEY,
  DNAME VARCHAR2(10),
  LIC VARCHAR2(10)
  );
RENAME DAPT2 TO DEPT2;
  
INSERT INTO DEPT2 VALUES (10, '인사', '서울');
-- INSERT INTO DAPT2 VALUES (10, '총무', '서울'); --unique constraint (SCOTT.DEPT2_DEPTNO_PK)
INSERT INTO DEPT2 VALUES (20, '인사', '부산');
SELECT * FROM DEPT2;

-- 외래키참조
CREATE TABLE EMP2
( EMPNO NUMBER (4) CONSTRAINT EMP2_EMPNO_KO PRIMARY KEY,
  ENAME VARCHAR2(10),
  SAL NUMBER(10),
  DEPTNO NUMBER(2) CONSTRAINT EMP2_DEPTNO_FK REFERENCES DAPT2(DEPTNO) );
SELECT * FROM user_constraints WHERE TABLE_NAME = 'EMP2'; 
INSERT INTO EMP2 VALUES (1111,'이효리',5600, 10);
INSERT INTO EMP2 VALUES (2222,'강수지',5600, 20);
-- INSERT INTO EMP2 VALUES (2222,'강수지',5600, 14); --unique constraint (SCOTT.EMP2_EMPNO_KO) violated
SELECT * FROM EMP2;


CREATE TABLE EMP3 
( EMPNO NUMBER (4) CONSTRAINT EMP3_EMPNO_PK PRIMARY KEY,
  ENAME VARCHAR2 (10),
  SAL NUMBER(10),
  DEPTNO NUMBER(2),
  CONSTRAINT EMP3_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT2(DEPTNO));
  
  
INSERT INTO EMP3 VALUES (1111,'이효리',5600, 10);
INSERT INTO EMP3 VALUES (2222,'강수지',5600, 20);
SELECT * FROM EMP3;

/*
FOREIGN KEY 추가옵션
- 부모 테이블의 행 삭제 시 문제될 수 있는 자식테이블 행 설정법.

: ON DELETE CASCADE
  - FK 제약조건에 의해 참조되는 테이블( 부모 테이블)의 행이 삭제되면, 
   해당 행 을 참조하는 테이블(자식 테이블)의 행도 같이 삭제되도록 한다.
: ON DELETE SET NULL
  - FK 제약조건에 의해 참조되는 테이블( 부모 테이블)의 행이 삭제되면, 
  해당 행 을 참조하는 테이블(자식 테이블)의 행을 NULL 로 설정한다.
*/
--CHECK CONSTRAINT
CREATE TABLE SAWON7
( S_NO NUMBER(4),
  S_NAME VARCHAR2(10),
  S_SAL NUMBER (10) CONSTRAINT SAWON7_S_SAL_CHK CHECK(S_SAL > 500) ); --행 레벨
  

CREATE TABLE SAWON8
( S_NO NUMBER(4),
  S_NAME VARCHAR2(10),
  S_SAL NUMBER (10), CONSTRAINT SAWON8_S_SAL_CHK CHECK(S_SAL > 500) ); --테이블레벨
  
--INSERT INTO SAWON7 VALUES (1, '김미은', 400); --check constraint (SCOTT.SAWON7_S_SAL_CHK) violated
INSERT INTO SAWON7 VALUES (2, '김미숙', 600);

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME ='SAWON7';

--DROP
/*
데이터베이스에서 해당 테이블을 제거하는 것이다.
: 테이블에 저장된 모든 데이터와 관련 INDEX가 삭제된다.
*/
DROP TABLE SAWON8;
DESC SAWON8;
-- TRUNCATE: 
/*테이블잘라내기
: 테이블의 모든 행들을 삭제할수있다.
: 테이블이 사용하고 있던 저장 공간을 해제하여 다른 테이블들이 사용 할 수 있도록 한다.
: DELETE 명령은 저장공간을 해제하지 않는다.
: ROLLBACK 정보를 발생시키지 않아서 DELETE 보다 수행속도가 빠르다
단, DELETE와 달리 ROLLBACk 은 불가능하다.
*/
TRUNCATE SAWON8;


----------------- 2013.03.26 화요일
--CH13. 기타스키마 객체
-- 13.3 VIEW
CREATE VIEW EMP_VIEW 
AS
SELECT EMPNO, ENAME, SAL, HIREDATE
FROM EMP
WHERE DEPTNO= 10; -- "insufficient privileges" -> 권한 줘야 함. sys에서 grant create view to scott;
desc emp_view ;
select * from emp_view;

create view dept_view
as
select deptno as no, dname name
from dept;
desc dept_view;
select * from dept_view;

-- 13.4 view 수정
create or replace view dept_view
as 
select deptno no, dname name, loc location
from dept;
desc dept_view;
select * from dept_view;

-- 13.4 복합 뷰 생성
create view emp_dept_view
as
select empno, ename, dname
from emp, dept
where emp.deptno = dept.deptno
and dept.deptno = 30;
desc emp_dept_view;
select * from dept_view;


select * from emp_view;
delete from emp_view where empno = 7782;

--13.6 제약조건
create or replace view emp_view
as
select * from emp
where deptno = 10
with check option constraint empview10_check;

select * from emp;

update emp_view
set deptno = 10 where ename = 'KING';
select * from emp;

-- 13.6 WITH READ ONLY
create or replace view dept_view
as
select * from dept
where deptno = 10
with read only;
delete from dept_view; -- cannot perform a DML operation on a read-only view
select * from emp;

--13.9 시퀀스 사용
create sequence emp_empno_seq
increment by 1
start with 100;

desc user_sequences;
select * from user_sequences;
insert into emp values (emp_empno_seq.nextval, '김수미', '인사', null, sysdate, 2500, 300, 40);
select * from emp;

select emp_empno_seq.currval from dual; -- 현재 값 보기
select emp_empno_seq.nextval from dual; -- nextval 할 때 마다 값이 증가

alter sequence emp_empno_seq
increment by 2; -- 다시 되돌려줌
select emp_empno_seq.nextval from dual;
select emp_empno_seq.currval from dual;

drop sequence emp_empno_seq; -- sequence EMP_EMPNO_SEQ이(가) 삭제되었습니다.

-- 13.11 인덱스 -> 넘어갈게요. 회사가서 하시면됩니다.

--01. 사용자관리
-- 1.5 사용자에게 권한 부여 
-- grant create session, create table to suin; --권한주기(suin 이 conn 생성가능)
select * from session_privs;

 -- 1.7 객체권한 부여
 grant select on dept to  OSI;
 grant select on emp to OSI;
 
 grant update(dname) on dept to OSI;