D:\query\KIM

--KIM

select * from tab;

select * from PERSONNEL;

SELECT * FROM DIVISION;

SELECT * FROM BONUS;

SELECT * FROM PAYGRADE;

/*

1.QUERY(DATA검색어)

SELECT

2.DML(DATA MAIPULATION LANGUAGE : 데이터 조작어)

INSERT , UPDATE, DELETE

--QUERY,DML 를 통틀어 CRUD라고 함 

3.DDL(DATA DEFINITION LANGUAGE : 데이터 정의어)

CREATE,  ALTER, DROP,RENAME --CASCADE 는 계정이 삭제되어도 TABLE은 삭제 되지 않음 

    --그래서 내가 만든 데이터 모든것을 지우게 하는게 CASCADE

4.TCL(트랜젝션 정의어)

COMMIT(DML 문장을 쓸때 사용함,SAVE기능,X버튼 누르지 말기!INSERT,UPDATE,DELETE 한게 다 없어진다)

ROLLBACK (데이터 베이스의 취소 명령어) ,

SAVEPOINT(실패했을때 맨처음부터 시작하지않 고,,LIKE..CHECKPOINT역할!)

5.DCL(권한 정의어)

GRANT(권한을 줄때), REVOKE(권한을 뺏어올때!)

*/

SELECT * FROM PERSONNEL; --*은 ALL

SELECT PNO,PNAME,PAY,JOB FROM PERSONNEL; --STRING으로 처리되기때문에 단어만 안짤리면 엔터 상관무!!!

SELECT DISTINCT JOB FROM PERSONNEL; --중복제거는 DISTINCT

--정렬

SELECT * FROM PERSONNEL;

SELECT * FROM PERSONNEL ORDER BY PAY ASC; --오름차순

SELECT * FROM PERSONNEL ORDER BY PNAME;         --ASC 생략가능 

SELECT * FROM PERSONNEL ORDER BY PAY DESC; --내림차순

--사원번호가 높은순으로 정렬

SELECT PNO,PNAME,JOB,PAY FROM PERSONNEL ORDER BY PNO;

--별칭

SELECT PNO,PNAME,PAY FROM PERSONNEL;     --PNO,PNAME,PAY 열제한 

--파생열(SELECT 하는 순간에만 PAY가 보여주는거 )

SELECT PNO,PNAME,PAY+1000 AS 보너스 FROM PERSONNEL; --PAY+1000 을 보너스라는 이름으로 변경                                                                                 --AS생략가능 --띄어쓰기는 안됨 

SELECT PNO,PNAME,PAY+1000 AS 보_너_스 FROM PERSONNEL; --띄어쓰기 하고싶으면 언더바

--파생컬럼으로 정렬

SELECT PNO,PNAME,PAY+1000 AS 보너스 FROM PERSONNEL ORDER BY 보너스;

SELECT PNO,PNAME,PAY+1000 AS 보너스 FROM PERSONNEL ORDER BY PAY+1000;

--이방법이 더 주로 사용(PAY+1000)

SELECT PNO,PNAME,PAY,PAY+1000 AS 보너스 FROM PERSONNEL ORDER BY 4;

--4번째(PAY+1000)으로 정렬

SELECT * FROM PERSONNEL ORDER BY 4;          --모든컬럼에서 네번째 정렬 

--DNO는 부서번호 

SELECT * FROM DIVISION;

SELECT PNO,PNAME,DNO FROM PERSONNEL ORDER BY DNO;

SELECT DNO,PNO,PNAME FROM PERSONNEL ORDER BY DNO,PNO;

SELECT DNO,PNO,PNAME FROM PERSONNEL ORDER BY DNO,PNO DESC;

--DNO뒤엔 ASC이 생략되는거 

--NULL

SELECT * FROM PERSONNEL; --MANAGE컬럼에 NULL하나 있음(0과NULL은 다른거!)

SELECT MANAGER FROM PERSONNEL ORDER BY MANAGER ASC;

SELECT MANAGER FROM PERSONNEL ORDER BY MANAGER DESC;

--NULL이 가장위로 올라가게 되어있음 (큰값처럼 인식함)

--사원의 급여와 일년치 연봉을 구하시오 

SELECT PNO,PNAME,PAY,PAY*12 연봉 FROM PERSONNEL ORDER BY PAY*12 ASC;

--연산가능

SELECT PAY,PAY-500 FROM PERSONNEL;

SELECT PAY,PAY*5 FROM PERSONNEL;

SELECT PAY,PAY/5 FROM PERSONNEL;

--연결연산자

SELECT PNAME || PNO FROM PERSONNEL; --컬럼과 컬럼의 값을 붙어서 나타내줌

SELECT PNAME || ' ' || PNO AS PNAME_PNO FROM PERSONNEL;

SELECT * FROM PERSONNEL;

SELECT PNO,PNAME,PAY,BONUS,BONUS*1.1  AS 인상분 FROM PERSONNEL; --없는건 NULL*1.1

SELECT PNO,PNAME,PAY,BONUS,NVL(BONUS*1.1,0)  AS 인상분 FROM PERSONNEL;

--NVL=값이 나오면 값을 써주고 NULL이면 0

--행제한(조건식)

--SELECT * FROM PERSONNEL WHERE 컬럼명 = 값;

SELECT * FROM PERSONNEL WHERE JOB='SALESMAN';

--ORACLE에서 저장되어있는 데이타는 무조건 대문자 /MSSQL은 소문자 

--연산자

--=,>,<,<=,>=,<> --<>같지않다 

--급여가 1600인 사원을 검색

SELECT * FROM PERSONNEL WHERE PAY=1600;

--사번이 1116인 사원을 검색

SELECT * FROM PERSONNEL WHERE PNO=1116;

--급여가 2000이하인 사원을 검색

SELECT * FROM PERSONNEL WHERE PAY <= 2000;

--90년12월17일에 입사한 사원

SELECT * FROM PERSONNEL WHERE STARTDATE='1990-12-17';

--업무가 CLERK인 사원을 찾으시오

SELECT * FROM PERSONNEL WHERE JOB='CLERK';

--이름이 JAMES인 사원을 찾으시오

SELECT * FROM PERSONNEL WHERE PNAME='JAMES';

--논리연산자

--AND,OR,NOT

--부서번호가 10번이고 급여가 3000이상인 사원

SELECT * FROM PERSONNEL WHERE DNO=10 AND PAY>=3000;

--직업이 SALESMAN이고 90년 이후에 입사한 직원을 찾으시오 

SELECT * FROM PERSONNEL WHERE JOB='SALESMAN' AND STARTDATE > '1990-12-31';

--91년 9월에 입사한 직원을 찾으시오

SELECT * FROM PERSONNEL WHERE STARTDATE >='1991-09-01'  AND STARTDATE<='1991-09-30';

--OR

--부서번호가 10번 이거나 급여가 3000이상인 사원

SELECT * FROM PERSONNEL WHERE DNO=10 OR PAY>=3000;

--직업이 SALESMAN이거나 90년 이후에 입사한 직원을 찾으시오 

SELECT * FROM PERSONNEL WHERE JOB='SALESMAN' OR STARTDATE > '1990-12-31';

--NOT

--업무가 SALESMAN,CLERK인 사원

SELECT * FROM PERSONNEL WHERE JOB='SALESMAN' OR JOB='CLERK';

--업무가 SALESMAN,CLERK인 사원

SELECT * FROM PERSONNEL WHERE JOB IN('SALESMAN','CLERK');

--업무가 SALESMAN,CLERK이 아닌 사원

SELECT * FROM PERSONNEL WHERE JOB<>'SALESMAN' AND JOB<>'CLERK';

--업무가 SALESMAN,CLERK이 아닌  사원

SELECT * FROM PERSONNEL WHERE JOB NOT IN ('SALESMAN','CLERK');

SELECT * FROM PERSONNEL WHERE NOT JOB IN('SALESMAN','CLERK');

--문자열 연산자

--BETWEEN A AND B

--급여가 1000에서 2000사이인 직원

SELECT * FROM PERSONNEL WHERE PAY>=1000 AND PAY<=2000; --처리속도가 더 빠름!

SELECT * FROM PERSONNEL WHERE PAY BETWEEN 1000 AND 2000; --WHERE뒤엔 항상 컬럼명!!명심!!

SELECT * FROM PERSONNEL WHERE PAY BETWEEN 2000 AND 1000; --ERROR!!무조건 작은값이 앞으로!!

--91년 9월 입사한 사원

SELECT *FROM PERSONNEL WHERE STARTDATE BETWEEN '1991-09-01' AND '1991-09-30';

--부서번호가 20과 30사이인 직원 

SELECT * FROM PERSONNEL WHERE DNO IN (20,30); --20과 30만!찾음

SELECT * FROM PERSONNEL WHERE DNO BETWEEN 20 AND 30; --20~30사이를 찾음 

--91년 9월에 입사하지 않은 사원

SELECT * FROM PERSONNEL WHERE STARTDATE NOT BETWEEN '1991-09-01' AND '1991-09-30';

--LIKE 

--이름이 A로 시작되는 사원

SELECT * FROM PERSONNEL WHERE PNAME LIKE 'A%';

--사원번호가 111_인 사원

SELECT * FROM PERSONNEL WHERE PNO LIKE '111_';   --_는 자릿수 4자리! 

SELECT * FROM PERSONNEL WHERE PNO LIKE '1__8'; --1AA8,1998가운데에는 어떠한 문자가 와도 상관없다 

--91년9월에 입사한 직원

SELECT * FROM PERSONNEL WHERE STARTDATE LIKE '91/09/%';

SELECT * FROM PERSONNEL WHERE STARTDATE LIKE '1991-09-%';

SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT'; 

--SEVER에서 CLIENT에서 접속할때 그 시점에서만 날짜 타입

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'; --타입이 RR/MM/DD에서 바뀜 

--PNO가 11로 시작되고 다음에 2가 오면 안됨 

SELECT * FROM PERSONNEL WHERE REGEXP_LIKE(PNO,'11[^2]');

--정규화 표현식 , 일단 ^나오면 그다음 문자가 나오면 안됨

--PNO가 11로 시작되고 다음에 1또는 2가 오면 됨 

SELECT * FROM PERSONNEL WHERE REGEXP_LIKE(PNO,'1[1,2]');

--이메일에서 아이디 추출

SELECT REGEXP_SUBSTR('SUZI@NAVER.COM' , '[^@]+') 아이디 FROM DUAL;

--뒤에 @가 오면안되고 뒤에 몇자가 되던 상관없음

--실제 존재하지 않는 테이블을 출력

SELECT * FROM DUAL;

SELECT 123+123 FROM DUAL;

SELECT 123+123 FROM DIVISION;         --DIVISION엔 데이터 4개 있어서 4개 나옴

--O_K를 포함한 이름을 가진 사원검색

SELECT * FROM PERSONNEL;

INSERT INTO PERSONNEL VALUES (2000, 'HELLO_KIM','SALESMAN',1116,'2019-1-1',3000,0,20);

INSERT INTO PERSONNEL VALUES (3000, 'HELLOAKIM','SALESMAN',1116,'2019-1-1',3000,0,20);

--INSERT하고 저장하고 끌때 롤백 OR 커밋 인지 뜸 

--롤백하면 INSERT한거 빠짐!

SELECT * FROM PERSONNEL WHERE PNAME LIKE '%O\_K%' ESCAPE '\'; 

--\이용 _가 문자임을 인식시켜주고 , 뒤에서 \를 이용 \제거해줌

--NULL

SELECT MANAGER FROM PERSONNEL;

SELECT MANAGER FROM PERSONNEL WHERE MANAGER = NULL;--X

SELECT MANAGER FROM PERSONNEL WHERE MANAGER IS NULL;

SELECT MANAGER FROM PERSONNEL WHERE MANAGER IS NOT NULL;

--우선순위 

--업무가 MANAGER이고 급여가 1500 이상인 사원 또는 업무가 SALESMAN인 사원의 정보를 검색

SELECT * FROM PERSONNEL (WHERE JOB=MANAGER' AND PAY>=1500) OR JOB='SALESMAN';   

--AND가 우선순위 ,BUT 큰 의미가 없기 때문에 괄호로 묶어줌!

--업무가 'PRESIDENT'이고 또는 'SALESMAN'이고 급여가 1500이상인 사원

SELECT * FROM PERSONNEL WHERE JOB='PRESIDENT' OR JOB='SALESMAN' AND PAY >= 1500;

SELECT * FROM PERSONNEL WHERE JOB IN('PRESIDENT' , 'SALESMAN' ) AND PAY >= 1500;

------------------------------------------------------------------------------20190108

--함수 

--숫자함수 

--ROUND (반올림)

SELECT ROUND(45.275,1) FROM DUAL;  --45.3

SELECT ROUND(45.275,-1)FROM DUAL;  --50

/*

 1  2  3  4 . 5 6    --수숫점에서 1은 2번째 자리에서 반올림하는것

-4 -3 -2 -1 0 1 2    --정수에서 -1은 -1번째자리에서 반올림하는것

*/

SELECT ROUND (45.225,1) FROM DUAL;  --45.2

SELECT ROUND (43.275,-1) FROM DUAL; --40

SELECT ROUND (43.275,0) FROM DUAL;  --43

SELECT ROUND (43.575,0) FROM DUAL;  --44

SELECT ROUND (-43.275,2) FROM DUAL;   -- -43.28

SELECT ROUND (-43.275,-1) FROM DUAL;  -- -40

--마이너스는 반대임 

SELECT * FROM PERSONNEL;

SELECT PNO,PNAME,JOB,PAY,ROUND(PAY,-2) FROM PERSONNEL; --10의 자리는 반올림!

--TRUNC(절삭)

SELECT TRUNC (45.245,2) FROM DUAL; --45.24

SELECT TRUNC (45.545,0) FROM DUAL; --45

SELECT TRUNC (45.245,-1) FROM DUAL; --40

SELECT PNO,PNAME,JOB,PAY,TRUNC(PAY,-2) FROM PERSONNEL;

--CEIL/FLOOR (올림/내림) --자릿값이 없음

SELECT CEIL(461.21) FROM DUAL;             --462

SELECT FLOOR(461.91) FROM DUAL;          --461

--MOD(나머지)

SELECT MOD(10,3) FROM DUAL;  --1

--ABS(절대값)

SELECT ABS(-23) FROM DUAL; --23

--SIGN(양수:1 , 음수:-1 , 영:0)

SELECT SIGN(100+200),SIGN(100-200),SIGN(0) FROM DUAL; --1 -1 0

--지수 

SELECT POWER(2,4) FROM DUAL;  --16

--제곱근

SELECT SQRT(9) FROM DUAL;

--문자함수

--UPPER(소문자->대문자)

SELECT UPPER('Oracle') FROM DUAL ;

--LOWER(대문자->소문자)

SELECT LOWER('ORACLE') FROM DUAL;

SELECT * FROM DIVISION WHERE DNNAME='sales'; --X 왜냐면 입력값은 대문자로 이루어져 있기때문에 

SELECT * FROM DIVISION WHERE DNAME=UPPER('sales');  --이게더빠름!

SELECT * FROM DIVISION WHERE LOWER(DNAME)='sales';

--INITCAP (첫글자만 대문자로 ...)

SELECT INITCAP('KOREA FIGHTING') FROM DUAL;

--CONCAT

SELECT PNO || PNAME FROM PERSONNEL; --이거를더많이 씀

SELECT CONCAT(PNO,PNAME) FROM PERSONNEL;

--LENGTH(글자의 길이)

SELECT LENGTH('KOREA') FROM DUAL;                 --5

SELECT LENGTH('KOREA FIGHTING') FROM DUAL;   --14(공백포함)

--SUBSTR

SELECT SUBSTR('ABCDEFG',1,3) FROM DUAL;  --1번째부터 3번째까지 --ABC

SELECT SUBSTR('ABCDEFG',3,2) FROM DUAL;  --CD

SELECT SUBSTR('ABCDEFG',-3,2) FROM DUAL; --EF  --뒤에서 3번째에서 2자리 

SELECT SUBSTR('ABCDEFG',-4) FROM DUAL;   --DEFG --뒤에서 4번째 자리에서 끝까지!

--INSTR

SELECT INSTR('ABCDEFG','C') FROM DUAL;      --3    --3번째자리 

SELECT INSTR('AAAAAAA','A') FROM DUAL; --1 --A를 찾기 

SELECT INSTR('AAAAAAA','a') FROM DUAL; --0

--RPAD/LPAD

SELECT RPAD('ABC',5,'*') FROM DUAL;     --ABC**  --ABC는 세자리이지만 5자리를 나타낼때 뒤에 **로 나타냄

SELECT LPAD('ABC',5,'*') FROM DUAL;     --**ABC

--JUMIN

--123456-1*******

--RTRIM/LTRIM

SELECT RTRIM('ABBBBBBB','B') FROM DUAL;         --A

SELECT RTRIM('A       ',' ') FROM DUAL;              --A

SELECT LTRIM('BBBABBB','B') FROM DUAL; --ABBB

--날짜함수

--SYSDATE

--GETDATE()-MS-SQL

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE-1,SYSDATE,SYSDATE+1 FROM DUAL; --하루전,오늘,하루후

--KIM 사원이 현재까지 근무한 날수,년수를 검색

SELECT * FROM PERSONNEL;

SELECT PNO,PNAME,JOB,STARTDATE ,SYSDATE,TRUNC(SYSDATE-STARTDATE) || '일' NALSU,

ROUND((SYSDATE-STARTDATE)/365) || '년'Y_SU

FROM PERSONNEL WHERE PNAME='KIM';

--ROUND

SELECT STARTDATE,ROUND(STARTDATE, 'YEAR')FROM PERSONNEL;     --년도 반올림 

SELECT STARTDATE,ROUND(STARTDATE, 'MONTH')FROM PERSONNEL; --월 반올림

--EXTRACT

SELECT STARTDATE,

EXTRACT(YEAR FROM STARTDATE) 년, 

EXTRACT(MONTH FROM STARTDATE) 월, 

EXTRACT(DAY FROM STARTDATE) 일 

FROM PERSONNEL;

--TRUNC

SELECT STARTDATE,TRUNC(STARTDATE,'YEAR') FROM PERSONNEL; --해당년도의 첫날

SELECT STARTDATE,TRUNC(STARTDATE,'MONTH') FROM PERSONNEL; --해당월의 첫날

--MONTH_BETWEEN(달수)

SELECT ROUND((MONTHS_BETWEEN(SYSDATE,'2002-6-1'))/12) FROM DUAL;

--LAST_DAY(달의 마지막날)

SELECT STARTDATE,LAST_DAY(STARTDATE) FROM PERSONNEL; --매달의 마지막날 

--NEXT_DAY

SELECT NEXT_DAY(SYSDATE,'토요일')FROM DUAL;

SELECT NEXT_DAY('2019-10-10','일요일')FROM DUAL;

--ADD_MONTHS

SELECT ADD_MONTHS(SYSDATE,7) FROM DUAL; --7개월뒤 

    현재 DATE = 2019-01-28

--TO_CHAR

--숫자나 날짜를 문자로 변환

/*

MM: 달수(10)

MON: 3문자 달이름(MAR),,3월일시

MONTH:달의 이름(MARCH)

DD: 달의 날짜 수(22일)

D: 주의 일수(2)

DY: 3문자의 요일이름(MON,TUE,,,)

DAY: 일 이름 (MONDAY)

YYYY: 년도 

YY: 2문자연도

RM: 로마식 달수(IX)

*/

SELECT SYSDATE,TO_CHAR(SYSDATE,'D')FROM DUAL;             --3

SELECT SYSDATE,TO_CHAR(SYSDATE,'DY')FROM DUAL;             --화

SELECT SYSDATE,TO_CHAR(SYSDATE,'DAY')FROM DUAL;     --화요일

SELECT SYSDATE,TO_CHAR(SYSDATE,'RM')FROM DUAL;             --I

SELECT SYSDATE,TO_CHAR(SYSDATE,'YYYY')FROM DUAL;           --2019

SELECT SYSDATE,TO_CHAR(SYSDATE,'DD')FROM DUAL;             --08

SELECT SYSDATE,TO_CHAR(SYSDATE,'MON')FROM DUAL;     --1월

SELECT * FROM PERSONNEL;

INSERT INTO PERSONNEL VALUES (7777,'SUZI','SINGER',2222,SYSDATE,5000,100,10);

SELECT STARTDATE,TO_CHAR(STARTDATE,'YYYY-MM-DD') 가입일 FROM PERSONNEL WHERE PNAME='SUZI';

SELECT PNAME,TO_CHAR(STARTDATE,'FMDD-MON-YY') STARTDATE1,

TO_CHAR(STARTDATE,'DD-MON-YY') STARTDATE2 FROM PERSONNEL;

--시간

/*

HH,HH12 : 12시간

HH24 : 24시간

MI : 분

SS : 초

AM,PM,A.M.,P.M. : 오전,오후

*/

SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL;        --14:45:26

SELECT TO_CHAR(SYSDATE,'AMHH:MI') FROM DUAL;        --오후02:45

SELECT TO_CHAR(SYSDATE,'AM HH:MI:SS') FROM DUAL;     --오후 02:45:38

SELECT TO_CHAR(SYSDATE,'P.M. HH:MI') FROM DUAL;        --오후 02:45

--숫자 

SELECT TO_CHAR(12506,'99,999.99') FROM DUAL; -- 12,506.00

SELECT TO_CHAR(12506,'99,999') FROM DUAL; --  12,506

SELECT TO_CHAR(12506,'9,999') FROM DUAL; --######

--적은건 5자리 표시는 6자리일때

SELECT TO_CHAR(12506,'099,999') FROM DUAL; -- 012,506

SELECT TO_CHAR(12506,'$99,999') FROM DUAL; -- $12,506

SELECT TO_CHAR(-12506,'99,999') FROM DUAL; ---12,506

SELECT TO_CHAR(-12506,'99,999PR') FROM DUAL; --<12,506>

--서수

--SP

--TH

SELECT SYSDATE,TO_CHAR(SYSDATE,'DD')FROM DUAL; --08

SELECT SYSDATE,TO_CHAR(SYSDATE,'DDSP')FROM DUAL; --EIGHT

SELECT SYSDATE,TO_CHAR(SYSDATE,'DDTH')FROM DUAL; --08TH

SELECT SYSDATE,TO_CHAR(SYSDATE,'DDSPTH')FROM DUAL; --EIGHTH

--TO_DATE

SELECT TO_DATE('19-1-8','YY-MM-DD') FROM DUAL; --2019-01-08 00:00:00 -- 문자가 날짜로 바뀐거임

SELECT TO_DATE('02:54','HH:MI') FROM DUAL; --2019-01-01 02:54:00

SELECT TO_DATE('19-1-8 02:54','YY-MM-DD HH:MI') FROM DUAL; --2019-01-08 02:54:00

SELECT TO_DATE('19-1-8 02:54','YY-MM-DD HH:MI')+3 FROM DUAL;--2019-01-11 02:54:00

--TO_NUMBER

SELECT TO_NUMBER('123') +100 FROM DUAL; --223

SELECT TO_NUMBER('ABC')  FROM DUAL;         --X--문자를 숫자로 바꿀순 없음

--NVL

SELECT BONUS,NVL(BONUS,0) FROM PERSONNEL;

SELECT MANAGER FROM PERSONNEL;

SELECT MANAGER,NVL(MANAGER,'NO-MANAGER')FROM PERSONNEL;

--X, --MANAGER가 없으면 NO-MANAGER로 넣어라 BUT 데이터형식은 숫자이기때문에 안됨

SELECT MANAGER,NVL(TO_CHAR(MANAGER),'NO-MANAGER')FROM PERSONNEL;

DESC PERSONNEL;

--DECODE(IF)

--각 사원의 급여를 부서가 10인경우 10%,부서가 20인경우 20%

--나머지는 30%를 더해서 출력

SELECT * FROM PERSONNEL;

SELECT PNAME,BONUS,DNO,PAY, DECODE(DNO,10,PAY*1.2,20,PAY,1.2,PAY*1.3) "인상분" FROM PERSONNEL;

--급여가 3500이상인 경우 GRADE 컬럼에 'GOOD'를 출력하고 미만인 경우 'POOR'출력

--SIGN(양수:1 , 음수:-1 , 영:0)

SELECT SIGN(100+200),SIGN(100-200),SIGN(0) FROM DUAL;         --1 -1 0

SELECT PNAME,DNO,PAY,DECODE(SIGN(PAY-3500),1,'GOOD','POOR') GRADE FROM PERSONNEL;

--그룹함수

--한개의 값만 출력

--COUNT

SELECT COUNT(*) FROM PERSONNEL;

SELECT COUNT(MANAGER) FROM PERSONNEL; --NULL은 못셈

--급여가 3000이상인 직원의 수

SELECT COUNT(*) FROM PERSONNEL WHERE PAY>=3000 ;

--AVG

SELECT PAY FROM PERSONNEL;         --다중값을 반환하는쿼리 

SELECT TRUNC(AVG(PAY)) FROM PERSONNEL;   --단일값을 반환하는 쿼리

SELECT PAY, AVG(PAY) FROM PERSONNEL;       --X, 다중과 단일은 같이 반환할수 없기 때문에 같이 쓸수없음

SELECT JOB FROM PERSONNEL WHERE JOB='PRESIDENT'; --다중

--BONUS의 평균

SELECT ROUND(AVG(NVL(BONUS,0))) 보너스평균 FROM PERSONNEL;

--SUM

SELECT SUM(PAY) FROM PERSONNEL;

--MAX/MIN

SELECT MIN(PAY) FROM PERSONNEL;

SELECT MAX(PAY) FROM PERSONNEL;

--입사한지 가장 오래된 직원의 입사일

SELECT MIN(STARTDATE) FROM PERSONNEL;

SELECT MAX(STARTDATE) FROM PERSONNEL;

--입사한지 가장 오래된 직원과 신입사원과의 연수

SELECT ROUND(((MAX(STARTDATE)-MIN(STARTDATE))/365)) 년수 FROM PERSONNEL;

SELECT MAX(PAY)-MIN(PAY) FROM PERSONNEL;

SELECT

(SELECT MAX(PAY) FROM PERSONNEL)-

(SELECT MIN(PAY)FROM PERSONNEL)

FROM DUAL;

--분석함수

--부서별 평균 구하기**외우기**

SELECT DISTINCT DNO,AVG(PAY) OVER(PARTITION BY DNO) --부서별평균

FROM PERSONNEL;

--월급 1200을 받는 사람 순위

SELECT * FROM PERSONNEL ORDER BY PAY DESC;

--랭크! 그룹안에(PAY로 정렬한)

SELECT (RANK(1200) WITHIN GROUP(ORDER BY PAY DESC) || '등') 등수 FROM PERSONNEL; --10등

SELECT PNAME, PAY, RANK() OVER (ORDER BY PAY DESC) 순위 FROM PERSONNEL; 

--OVER란? OVER를 쓰게 되면 컬럼이 하나 만들어지는데 그위에 RANK로 덮어씌운것임

--전체사원의 급여와 함께 부서별 최고급여

SELECT DNO,PAY,FIRST_VALUE(PAY)

OVER(PARTITION BY DNO ORDER BY PAY DESC) AA FROM PERSONNEL;

--------------------------------------------------------------------------------------------------------20190109

--GROUP BY

SELECT PNAME FROM PERSONNEL;

SELECT PAY FROM PERCENT WHERE PAY=1600; --다중값

SELECT COUNT(*) FROM PERSONNEL;  --단일값 

SELECT AVG(PAY) FROM PERSONNEL;  --단일값

SELECT PNAME,MIN(STARTDATE) FROM PERSONNEL; 

--X,     MIN은 단일값,PNAME은 다중값이 나오기 때문에 출력이 되지않음!

--입사한지 가장 오래된 사원 

SELECT PNAME,MIN(STARTDATE) FROM PERSONNEL;

--GROUP BY PNAME;   --만약 이름이 중복이면 그중에서 STARTDATE가 가장 작은것 하나만 출력됨

--SUBQUERY

SELECT PNAME, STARTDATE FROM PERSONNEL 

WHERE STARTDATE=(SELECT MIN(STARTDATE) FROM PERSONNEL);  --BILL 1989-01-10 00:00:00  

  --값을 비교할수있음 

--부서*별* 평균 급여를 구하시오 --'별'자가 나오면 GROUP BY 써야됨

SELECT DNO,ROUND(AVG(PAY)) FROM PERSONNEL GROUP BY DNO;

--직업별 평균급여, 인원수 구하기

SELECT * FROM PERSONNEL;

SELECT JOB,ROUND(AVG(PAY)),COUNT(*) FROM PERSONNEL 

GROUP BY JOB;

--각 부서의 평균 급여가 전체평균 급여보다 크면 'GOOD'

--작으면 'POOR'로 표시 

SELECT ROUND(AVG(PAY)) FROM PERSONNEL;

SELECT DNO,ROUND(AVG(PAY)) 부서평균,

DECODE(SIGN(AVG(PAY) -(SELECT AVG(PAY) FROM PERSONNEL)),1,'GOOD','POOR') 상태

FROM PERSONNEL

GROUP BY DNO         --GROUP BY의 조건문은 HAVING

HAVING 부서평균 >= 3000 ; --X --결과가 나온다음에 조건문이 들어감 

SELECT * FROM PERSONNEL

WHERE DNO=20           --DNO로 결과가 나온다음에 ORDER BY 로 정렬

ORDER BY JOB;  --SELECT 의 조건문은 WHERE

SELECT ROUND(AVG(PAY)) FROM PERSONNEL;

SELECT DNO,ROUND(AVG(PAY)) 부서평균,

DECODE(SIGN(AVG(PAY) -(SELECT AVG(PAY) FROM PERSONNEL)),1,'GOOD','POOR') 상태

FROM PERSONNEL

GROUP BY DNO --GROUP BY의 조건문은 HAVING

HAVING ROUND(AVG(PAY)) >= 3000 ; --결과가 나온다음에 조건문이 들어감 

SELECT DNO,PNO,PNAME 이름 FROM PERSONNEL 

ORDER BY 이름;  --O ,ORDER BY만 별칭사용가능

--부서인원이 2명보다 많은/조건/ 부서의 부서 번호 ,급여의 합 출력/별/

SELECT * FROM PERSONNEL;

SELECT DNO,COUNT(*),SUM(PAY) 합계

FROM PERSONNEL

GROUP BY DNO

HAVING COUNT(*)>2; --결과가 모두 나온값에 조건을 주는거임!!!!!!!!!!!

--ORDER BY는 결과가 모두 나오고 정렬이니까 마지막에 들어가야함

--SELF JOIN

SELECT * FROM PERSONNEL;

--1112 ALLY의 매니저 1116 은 PNO1116은 JOHNSON임 

SELECT A.PNO ,A.PNAME 직원 ,A.MANAGER, B.PNO,B.PNAME 관리자 

FROM PERSONNEL A,PERSONNEL B 

WHERE A.MANAGER =B.PNO;

--NON-EQUI JOIN(ORACLE에만 있음)

SELECT * FROM PERSONNEL;

SELECT * FROM PAYGRADE; --범위가 들어가있음 

SELECT PNAME,PAY, GRADE

FROM PERSONNEL , PAYGRADE

WHERE PAY BETWEEN LOWPAY AND HIGHPAY;

--OPERATOR

--A=[1,2,5,6]

--B=[1,2,4]

--UNION 을 주로 사용함

--A UNION ALL B=[1,2,5,6,1,2,4]

--A UNION B =[1,2,5,6,4]

--A MINUS B =[5,6] --A가기준

--A INTERSECT B = [1,2] 교집합

--UNION/ALL

SELECT * FROM PERSONNEL

UNION ALL 

SELECT * FROM PERSONNEL;

--UNION

SELECT * FROM PERSONNEL

UNION  

SELECT * FROM PERSONNEL; --1번출력

--MINUS

SELECT DNO FROM DIVISION     --10,20,30,40

MINUS;

SELECT DNO FROM PERSONNEL; --40/PERSONNEL엔 10,20,30까지만 있음 

--INTERSECT

SELECT DNO FROM DIVISION     --10,20,30,40

INTERSECT

SELECT DNO FROM PERSONNEL; --10,20,30 --교집합 

SELECT * FROM PERSONNEL;

--------------------------------------------------------------------------20190111

--SUBQUERY

--PAIRWISE

SELECT PNAME,DNO,PAY,NVL(BONUS,-1)

FROM PERSONNEL

WHERE (PAY , NVL(BONUS,-1)) IN (SELECT PAY,NVL(BONUS,-1) FROM PERSONNEL WHERE DNO=30); --두개를 비교할때는 반드시 괄호를 묶어줘야?p..

--두개의 조건이 맞는경우의 결과값이 검색됨 

SELECT PAY,BONUS FROM PERSONNEL WHERE DNO=30; --조건이 4개가 나옴 

--NON-PAIRWISE (PAY와 BONUS를 나눈것)

SELECT PNAME,DNO,PAY,NVL(BONUS,-1)

FROM PERSONNEL

WHERE PAY IN

(SELECT PAY FROM PERSONNEL WHERE DNO=30)

AND NVL(BONUS,-1) IN

(SELECT NVL(BONUS,-1) FROM PERSONNEL WHERE DNO=30);

--DML(INSERT,UPDATE,DELETE)

SELECT * FROM DIVISION ;

DESC DIVISION;

--PRIMARY KEY 

--중복값 허용안함 (DNO 중복값 안됨 ),NULL값 안됨 (NOT NULL로 출력)

--TABLE당 하나만 만들수있음 

SELECT * FROM USER_CONSTRAINTS;

INSERT INTO DIVISION VALUES (50,'OPERATION','031-123-1234','DAEGU');

--INTO는 ORACLE에선 생략하면 안됨 (MSSQL에선 생략가능)

INSERT INTO DIVISION VALUES(60);

--안들어감 / WHY? (DNO값만 NULL을 허용하지 않음 ) 60을 어디에 넣는지 모르기때문에 에러남

INSERT INTO DIVISION (DNO) VALUES(60);

INSERT INTO DIVISION (DNO,DNAME,PHONE,PROSITION) VALUES (50,'OPERATION','031-123-1234','DAEGU');

--다시 생성하면 에러남 

--어디 컬럼에 들어가는지 써줘야함 

--무결성(결함이없다)

INSERT INTO DIVISION(DNAME,POSITION) VALUES ('ACCOUNT', 'DAEJEON'); --X / DNO가 없어서 

--암시적 NULL삽입

INSERT INTO PERSONNEL (PNO,PNAME,PAY,DNO)

VALUES (7711,'YOUNG',4000,20);

--암시적 NULL삽입(내가 넣고자 하는 데이터만 넣고 나머지는 자동으로 NULL)

SELECT * FROM PERSONNEL;

--명시적 NULL삽입

INSERT INTO DIVISION VALUES (70,'AAA','123',NULL); --특정컬럼에 NULL삽입

INSERT INTO DIVISION VALUES (80,'DDD','123',''); --띄어쓰기는 공백이 들어가니까 작은 따옴표 두개만!!

SELECT * FROM DIVISION;

INSERT INTO PERSONNEL (PNO,PNAME,JOB,STARTDATE,DNO)

VALUES (1204,'YOU','SALESMAN',SYSDATE,10);

SELECT PNO,PNAME,JOB,TO_CHAR(STARTDATE,'YYYY-MM-DD'),DNO FROM PERSONNEL;

--치환변수 --내가 DNO값을 계속 바꿔줘야할때 

SELECT PNO,PNAME,JOB,STARTDATE,DNO

FROM PERSONNEL

WHERE DNO=&DIV_DNO;

CREATE TABLE MANAGER

AS 

SELECT * FROM PERSONNEL

WHERE 1=2;

--조건식만 거짓으로 만들면됨 

CREATE TABLE SALESMAN

AS 

SELECT * FROM PERSONNEL

WHERE 1=2;

CREATE TABLE BUSEO   

AS SELECT * FROM DIVISION;

CREATE TABLE SAWON

AS SELECT * FROM PERSONNEL;

CREATE TABLE EXAM1

AS SELECT * FROM PERSONNEL;

CREATE TABLE EXAM2

AS SELECT * FROM DIVISION;

SELECT * FROM TAB;

SELECT * FROM DIVISION;

SELECT * FROM MANAGER;

SELECT * FROM SALESMAN; --데이터는 없지만 기본틀만있음

SELECT * FROM BUSEO;

INSERT INTO BUSEO(DNO,DNAME,PHONE,POSITION)

VALUES (&B_NO,'&B_NAME','&B_PHONE','&B_POSIT');   --이미 테이블이 있기때문에 INSERT로 넣어줌 

--&&

SELECT PNO,PNAME,JOB,&&COL_NAME   --고정임 (&&=고정,&=항상 물어봄)

FROM PERSONNEL

ORDER BY &ORDER_COL; --&&두개를 쓴 값을 먼저 물어봄

--PAY-JOB / PAY먼저 정렬후 그다음 JOB

UNDEFINE COL_NAME; --변수 초기화

--ACCEPT

ACCEPT B_NO PROMPT '부서 번호를 입력하세요: '; --10입력하면 B_NO 로 들어감 

--SUBQUERY를 사용해서 INSERT

DESC MANAGER;

SELECT * FROM MANAGER;

SELECT * FROM PERSONNEL

WHERE JOB='MANAGER';

INSERT INTO MANAGER (PNO,PNAME,PAY,STARTDATE)

SELECT PNO,PNAME,PAY,STARTDATE FROM PERSONNEL --AS는CREATETABLE쓸때만 

WHERE JOB='MANAGER';

INSERT INTO SALESMAN

SELECT * FROM PERSONNEL WHERE JOB='SALESMAN';

SELECT * FROM SALESMAN;   

--UPDATE

SELECT * FROM PERSONNEL

WHERE PNO=1111;

UPDATE PERSONNEL SET DNO=30   --SET->찾아라 

WHERE PNO=1111;   --DNO를 찾아서 1111로 변경 

SELECT *FROM SAWON;

UPDATE SAWON SET DNO=20;   --조건을 주지 않으면 다 20으로 변경됨.. 주의!!!!!!!!!!

SELECT * FROM PERSONNEL;

UPDATE PERSONNEL SET DNO=10,PNAME='SUZI',JOB='SINGER' ,MANAGER=0000,PAY=5000,STARTDATE=SYSDATE

WHERE PNO=1111;

 

--DELETE 

SELECT * FROM PERSONNEL --FROM 생략가능 

WHERE PNO=1112;

 

DELETE FROM PERSONNEL

WHERE PNO=1112;

 

SELECT * FROM BUSEO;

DELETE BUSEO; --조건없으면 다 삭제!

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

--에러가 발생하는 경우 (CONSTARAINRS: 제약)

--INSERT에러 

SELECT * FROM PERSONNEL;

INSERT INTO PERSONNEL (PNO,PNAME,DNO)

VALUES (7711,'SONG',99); --에러뜸 (무결성 , 똑같은 ID가 두번들어가니까)

SELECT  * FROM USER_CONSTRAINTS;

--PERSONNEL_PNO_PK (PRIMARY KEY는 똑같은 값이 들어갈수없음 )

--UPDATE 에러 

SELECT * FROM DIVISION;

SELECT * FROM PERSONNEL;

UPDATE PERSONNEL SET DNO=50

WHERE DNO=20;

--부모키가 없음 (무결성 위배)//DIVISION TABLE에서 DNO컬럼을 PERSONNEL 에 DNO가 참조하는것

--FORIGN KEY,.. 20이들어가 있는데 55로 변경 할려고 하지만 부모가 55인 값을 가지고 있지 않기 때문에 INSERT시킬수 없음 

--50으로 변경하면 다됨!

--TABLE엔 제약조건을 무조건 만들어줘야함

SELECT * FROM USER_CONSTRAINTS;

--R:REFERENCE 참조 PSERSONNEL DNO FK 가 DIVISION DNO PK를 R(참조)함 

DESC DIVISION; --NUMBER(2,0)동일함 

DESC PERSONNEL; --NUMBER(2,0)

/*

PRIMARY KEY

UNIQUE KEY

CHECK KEY 

NOT NULL KEY 

FOREIGN KEY 

NOT NULL 은 MS-SQL

PK는 TABLE에서 절대 중복으로 가질수 없는 것에 PK를 줌 (NULL은 안됨) 처음부터 존재하지 않음 DBA가 넣어줘야함 

PK는 무조건 하나 !! 무조건 고정인 값인 경우엔 PK를 안줘야함 왜냐면 INSERT ,UPDATE 하지 않을테니까(LIKE USERID)

일반적으로 첫번째 컬럼은 고유값을 가지는 컬럼을 만들어줌 

UK는 두번째로 중복될수없는 값을 정할때 

BUT, NULL허용가능 / NULL은 MS-SQL은 한번가능(NULL을 데이터로 인식)/ORACLE 은 무한,.. (NULL을 데이터로 인식 안함)LIKE 주민번호 

CK 는 제약조건,, 20살 이하는 가입 불가일때 !

NOT NULL은 별도로 줄수있음 ,.LIKE) 수강과목에 NULL값을 허용안할때 

DEFAULT KEY 란 등록일이 있을때 날짜는 자동으로 들어감 데이터를 INSERT할때 내가 값을 넣지 않아도 SYSDATE가 DEFAULT로 들어갈때 

FOREIGN KEY 란 (외부참조키) 아래 기본으로 만들어놓은 테이블에서 학생정보 입력할때 해당 데이터가 없으면 데이터가 안들어감,

테이블과 테이블사이에 연결고리를 만들어주는것

과목명 컬럼은 학생테이블에서 참조하는 컬럼 (참조 되는 컬럼과, 참조하는 컬림이 데이터타입이 똑같아야함, 참조되는 컬럼은 PK/UK중 하나 ! 무조건!

번호      과목명

1 VB

2 SQL

3 C 

*/

--DELETE 에러 

SELECT * FROM DIVISION;

SELECT * FROM PERSONNEL;

--부모의 DNO 가 없으면 FK의 DNO는 고아 데이터가 됨 

--그래서 참조 하고 있으면 DNO는 지울수 없음 BUT 데이터가 없으면 삭제 가능

DELETE DIVISION WHERE DNO=50; --자식레코드가 발견되었습니다 

DELETE DIVISION WHERE DNO=60; --O/참조하는 데이터가 없기 때문에 삭제가능 

--TRANSACTION 

--DML문장 시작시 자동을 시작

--INSERT,UPDATE, DELETE 

--COMMIT,ROLLBACK 무조건 결정해야됨 

--AUTO COMMIT인 경우 

--CREATE, ALTER, DROP 

--EXIT 

/*

TRANSACTION 이란 

EX)

A에는 1000원

B에는 500원

A계좌에서 B계좌로 500원 송금하는 작업을 할때 

-내가 하고싶은 작업은 하나지만 실제 실행할땐 여러작업이 필요함

1. A계좌가 있는지 확인 

2. A계좌에 잔액이 있는지 확인(1000)

3. A계좌에서 500원 차감(500)

4. B계좌가 있는지 확인

5. A계좌에서 B계좌로 500원 송금 

6. B계좌에 잔액확인(500)

7. B계좌에 500원 증감(1000)

8. 완료 

내부 작업중에 하나라도 실행이 안되면 모든게 실행되지 않는게 TRANSACTION

은행에서 500송금을 하는중 문제가 생겨서 500이 사라진경우 관리자가 확인후 5번째에서 오류가 난걸 확인했을경우

ORACLE은 무조건 취소시킴 /송금은 하루에 많이 일어나기때문에 LOGFILE 로 저장해서 다시 ROLLBACK시킴  

COMMIT 은 사람 시키고 예기치 않은 오류는 자동으로 ROLLBACK시켜버림 

LOG FILE은 무조건 선기록하고 실행!!!!!!!!!!

*LOCK

EX) 비행기표예약

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

서울->LA

2019-1-20 09:00

IN KAL302

A1  

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

->결제 ->B라는 사용자도 접속해서 A1자리를 먼저 결제까지 해버림->A가 돌아왔지만 이미 발권된 티켓 

(A라는 사용자가 카드를 가지러 잠시 딴데 들린상태)

=>다른사용자들이 같이 접속은 가능해도 A가 선택한 상태면 COMMIT 을하거나 ROLLBACK을 하기 전까지 LOCK을 걸어줌 (ORACLE이 걸어줌,TRANSACTION 의 일부이기 때문에)

LOCK을 걸어주지 않으면...EX)수강신청

A에서 C데이터를 입력하고 COMMIT 전,, B에서 C데이터를 입력하게되면 B에서 LOCK이 걸려버림 

WEB,언어에서 사용하는 INSERT,UPDATE,DELETE는 AUTO COMMIT임

*/

COMMIT;

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

--DDL (CREATE , ALTER,DROP)

--OBJECT(*TABLE*,*INDEX*,SYNONYM,SEQUENCE,*VIEW*)

--TABLE 

--DATE TYPE 

--*CHAR: 문자 (고정길이)

--*VARCHAR2: 문자(가변길이)

--NUMBER(P,S) : 숫자(가변길이)  (6,2) 전체 6자리 소숫점 2자리 

--DATE: 날짜(고정길이,7BYTE)

--LONG: 문자(가변길이, 2GB)

/*

CHAR(5) : 5는 길이 /4자만 쳤어도 5자리를 가지고 있음/ 추가하고싶을땐 공간을 가지고 있기때문에 입력하면됨

VARCHAR2(5) : 5는 길이/ 4자리를 치면 남은 한자리를 OS에 반납함/ 추가하고 싶을땐 OS가 남은공간을 줌 

2는 생략해도 무조건 붙음

CHAR은 공간 낭비가 심하다 

CHAR은 속도가 굉장히 빠르다 

CHAR은 읽어야하는것만 다 읽어버리면 되니까 

HEADER에서 5BYTE라고 들어있음

VARCHAR 은 5BYTE에서 3BYTE쓰고 2BYTE반납하고 다음 데이터는 4BYTE 1BYTE반환.. 

HEADER는 물음표임.. 그때그때마다 헤더를 읽어야하니까 속도가 느려짐 

 

속도, 공간을 고려해서 VARCHAR 과 CHAR을 사용함

 

CHAR 은 이름, 학번(학번의 크기는 똑같), 주민번호, ZIP

VARCHAR 은 주소.. 주소는 각자 길이가 다르기때문에 

WHERE뒤에 자주쓰는건 CHAR..

빠르기때문에 CHAR을 주로 사용함 

VARCHAR은 AAA--/AA 이런식이 아닌 AAAAA 붙어서 쓰게 됨 

만약 중간에 B를 입력할때는 중간에 들어가지 않고 맨 끝에 B가 입력됨 

A와 B를 찾게 되면 A찾고 B찾고..  이게 단편화 현상(CHAIN)-관리자의 관리가 필요함 

*/

CREATE TABLE BUSEO1

(DNO NUMBER(2),

DNAME VARCHAR(10),

ZIPCODE CHAR(7));

DESC BUSEO;

INSERT INTO BUSEO1 VALUES (11,'AAA','12345');

SELECT * FROM BUSEO1;

--테이블 이름 

--1.영문자로 시작 

--2.영문자,숫자 사용가능 

--3.특수문자는 _,$,# 만가능

--4.이름은 중복 허용안함

--5.칼럼명이 틀려야함

--6.예약어 사용불가 (TABLE이름을 INSERT 로 만들고 그런것들..)

--Sawon,p_no,Div10 --O

--10Div, $sal, p-no ,alter --X

CREATE TABLE ABC

(ID NUMBER(2) PRIMARY KEY,

NAME CHAR(10),

JUMIN CHAR(14),

ZIP CHAR(7),

ADDR VARCHAR(50));

/*

제1정규형 = PK를 만듦 (학번은 다르니까 ,,)

제2정규형 = FK TABLE 만들어준다 

제3정규형 = 컬럼과 컬럼과의 관계(단장 만원,단원 오천원 ,신입 천원 으로 정한TABLE을 따로 만들어놓는다)

-정규화가 완료된 테이블

테이블을 쪼개서 만드는것을 정규화 한다고 함

FK값만 갖고있는 TABLE은 선호하지 않음

회원-아이디(PK)이름암호성별

주문-주문번호(PK)아이디(FK)주문상태발신인발신인연락처

제품-제품번호(PK)제품명대분류

장바구니-제품번호(FK)주문번호(FK)주문개수 주문가격 

*/

DESC ABC;

SELECT * FROM ABC;

INSERT INTO ABC VALUES(11,'AAA','123','123','서울');

SELECT * FROM ABC;

SELECT * FROM USER_CONSTRAINTS; --SYS_C0011077 --항상 복수 

--SYS_C까지 뒤에 숫자는 의미없음 --지울때는 가져다 써야함 

SELECT * FROM TAB;

SELECT PNO,PNAME,JOB FROM PERSONNEL

WHERE JOB='SALESMAN';

CREATE TABLE SALESMAN1

AS 

SELECT PNO,PNAME,JOB FROM PERSONNEL

WHERE JOB='SALESMAN';

SELECT * FROM SALESMAN1;

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

CREATE TABLE MANAGER1

AS

SELECT * FROM PERSONNEL WHERE JOB='MANAGER';

SELECT * FROM MANAGER1;

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

CREATE TABLE PAY3000

AS

SELECT * FROM PERSONNEL

WHERE PAY>=3000;

SELECT * FROM PAY3000;

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

--12월에 입사한 직원들의 데이터를 MON12로 저장

SELECT * FROM PERSONNEL;

CREATE TABLE MON12

AS

SELECT * FROM PERSONNEL

WHERE TO_CHAR(STARTDATE,'MM')=12;

--WHERE STARTDATE LIKE '%/12/%';

SELECT * FROM MON12;

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

CREATE TABLE ANALYST ( NO,NAME,JOB,DNO) --컬럼명을 내가 원하는 이름으로 만들수있음

AS

SELECT PNO,PNAME,JOB,DNO FROM PERSONNEL

WHERE JOB='ANALYST';

SELECT * FROM ANALYST;

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

CREATE TABLE DIV10

AS

SELECT DNO,SUM(PAY) SUM_PAY FROM PERSONNEL

WHERE DNO=10

GROUP BY DNO;

SELECT * FROM DIV10;

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

--PK, 제약조건은 복사되지않는다

SELECT * FROM DIVISION;

DESC DIVISION;

CREATE TABLE DIVISION1

AS

SELECT * FROM DIVISION;

DESC DIVISION;

DESC DIVISION1;

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

--테이블의 구조 복사 

SELECT * FROM PERSONNEL;

CREATE TABLE SAWON1

AS

SELECT * FROM PERSONNEL

WHERE 0=1;

SELECT * FROM SAWON1; --틀만 만들어짐

DESC SAWON1;

--------------------------------------------------------------------------20190115

--테이블 수정 

SELECT * FROM SALESMAN1;

DESC SALESMAN1;

ALTER TABLE SALESMAN1

ADD DNO NUMBER(2); --컬럼은 미리 다 만들어져있어야함 

DESC SALESMAN1;

INSERT INTO SALESMAN1 VALUES (1222,'KIM','SALESMAN',10);

ALTER TABLE SALESMAN1 

ADD (JUMIN CHAR(14),ZIP CHAR(7)); --괄호

ALTER TABLE SALESMAN1

ADD BIGO VARCHAR(10) DEFAULT ('AA'); --DEFAULT 값을 주면 모든 레코드에 AA가 들어가있음 

SELECT * FROM SALESMAN1;

INSERT INTO SALESMAN1 VALUES (2222,'JO','SALESMAN',10,'123','123'); --컬럼의 수가 맞지 않아서 에러남 

INSERT INTO SALESMAN1 (PNO,PNAME,JOB,DNO,JUMIN,ZIP)VALUES (3333,'CHO','SALESMAN',10,'123','123'); --자동으로 AA들어감 

ALTER TABLE SALESMAN1

ADD(MARRIAGE CHAR(8) CONSTRAINT SALESMAN1_MARRIAGE_CK 

--제약조건의 이름은 사용자가 만들어줌 

CHECK (MARRIAGE IN('SINGLE','MARRIED')));

--PRIMARY면 PRIMARY 

SELECT *FROM USER_CONSTRAINTS;

INSERT INTO SALESMAN1 (PNO,PNAME,JOB,DNO,JUMIN,ZIP,MARRIAGE)

VALUES (3334,'CHO2','SALESMAN',10,'123','123','SINGLE');

SELECT * FROM SALESMAN1;

INSERT INTO SALESMAN1 (PNO,PNAME,JOB,DNO,JUMIN,ZIP,MARRIAGE)

VALUES (3334,'CHO2','SALESMAN',10,'123','123','SOLO'); --ORA-02290: 체크 제약조건(KIM.SALESMAN1_MARRIAGE_CK)이 위배되었습니다

--PRIMARY KEY 

SELECT * FROM SALESMAN1;

DESC SALESMAN1;

ALTER TABLE SALESMAN1

ADD CONSTRAINT SALESMAN1_PNO_PK PRIMARY KEY (PNO); --PK설정

SELECT * FROM SALESMAN1;

DESC SALESMAN1;

SELECT * FROM USER_CONSTRAINTS;

INSERT INTO SALESMAN1 (PNO,PNAME,JOB,DNO,JUMIN,ZIP,MARRIAGE)

VALUES (3335,'CHO3','SALESMAN',10,'123','123','MARRIED'); --두번실행했을시 ORA-00001: 무결성 제약 조건(KIM.SALESMAN1_PNO_PK)에 위배됩니다

SELECT * FROM MANAGER1;

ALTER TABLE MANAGER1 

ADD COMM NUMBER(5);

ALTER TABLE MANAGER1

ADD CONSTRAINT MANAGER1_PNO_PK PRIMARY KEY (PNO);

SELECT * FROM USER_CONSTRAINTS;

SELECT * FROM MANAGER1;

--컬럼 수정

--MODIFY 

SELECT * FROM MANAGER1;

DESC MANAGER1; --PNAME 9

ALTER TABLE MANAGER1

MODIFY PNAME VARCHAR2(16); --PNAME 16

ALTER TABLE MANAGER1

MODIFY PNAME VARCHAR2(5); --X , 이미 들어가있는 데이터가 5의 길이보다 크기때문에 

ALTER TABLE MANAGER1

MODIFY PNAME VARCHAR2(7);

--데이터가 없을때 

--문자<->숫자 <->날짜

--확대,축소 가능

--데이터가 있을때

--CHAR <-> VARCHAR2 

--확대만 가능 

--DROP (컬럼,제약조건 삭제)

--제약조건 삭제 -> 컬럼삭제 

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'MANAGER1';   --SELECT * FROM CON~ 도가능 

ALTER TABLE MANAGER1 

DROP CONSTRAINT MANAGER1_PNO_PK ;

--테이블은 그대로 있는상태에서 안에들어가있는 컬럼이나 제약조건은 삭제시키는거 /테이블은 그대로기때문에 ALTER로 .. 

ALTER TABLE MANAGER1

DROP PRIMARY KEY;

SELECT * FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'DIVISION';

ALTER TABLE DIVISION 

DROP CONSTRAINT DIVISION_PNO_PK ;

ALTER TABLE DIVISION

DROP PRIMARY KEY;

SELECT * FROM USER_CONSTRAINTS

WHERE TABLE_NAME ='DIVISION' OR TABLE_NAME='PERSONNEL';

--1 FK먼저 지우고

ALTER TABLE PERSONNEL

DROP CONSTRAINT PERSONNEL_DNO_FK;

--2

ALTER TABLE DIVISION

DROP CONSTRAINT DIVISION_DNO_PK;

--1과2를 합친거

ALTER TABLE DIVISION

DROP PRIMARY KEY CASCADE;

--컬럼삭제

SELECT * FROM SALESMAN1;

DESC SALESMAN1;

ALTER TABLE SALESMAN1

DROP COLUMN MARRIAGE;

--SET UNUSED - 삭제하진 않지만 일반 사용자에겐 보여지진않음

ALTER TABLE SALESMAN1

SET UNUSED COLUMN DNO;

SELECT * FROM SALESMAN1;

DESC SALESMAN1;

ALTER TABLE SALESMAN1

DROP UNUSED COLUMNS; --UNUSED 한 컬럼들 다 지우기 

--DROP(테이블삭제)

SELECT * FROM TAB; --VIEW(가상테이블) 도 보임

SELECT * FROM TABS;

--USERS01.DBF 는 OS에서 관리 

DROP TABLE MANAGER1;

--BIN$8Rh7EXtrQnOqW75wVqbY+g==$0 ->쓰레기통(OS)에 들어감 / 그래서 복구가 가능함 

DROP TABLE ABC;

SELECT * FROM TAB;

--휴지통확인

SELECT * FROM RECYCLEBIN;

SELECT * FROM "BIN$eOsRhWJbRbGUhVr4D4i1uw==$0";

--휴지통 복구 

FLASHBACK TABLE MANAGER1 TO BEFORE DROP;

FLASHBACK TABLE "BIN$eOsRhWJbRbGUhVr4D4i1uw==$0" TO BEFORE DROP;

SELECT * FROM TAB;

--휴지통 비우기

PURGE RECYCLEBIN;

--휴지통으로 넣지 않고 바로 지워버림 (바로삭제)

DROP TABLE MANAGER1 PURGE;

SELECT * FROM RECYCLEBIN;

--TRUNCATE / DELETE 의 차이(둘다 레코드를 삭제하는 명령어)

--DELETE

SELECT * FROM DIVISION;

DELETE DIVISION; --FROM 생략가능 

SELECT * FROM DIVISION;

ROLLBACK;

--LOG파일에선 행을 삭제할때마다 LOG파일엔 1행씩 삭제하는 과정이 저장되어있음 

--TRUNCATE--RECOVERY 작업을 할수없음 

TRUNCATE TABLE DIVISION; --레코드를 지우는거!!!!!!!

ROLLBACK;

SELECT * FROM DIVISION; --안나옴/WHY?TRUNCATE는 LOG파일에 기록하지 않음/그래서 속도가 빠름 

--RENAME

SELECT * FROM TAB;

SELECT * FROM PAY3000;

RENAME PAY3000 TO HIGHPAY;

SELECT * FROM PAY3000; --X /이름을 바꿔주었기 때문에 SELECT 안됨

SELECT * FROM HIGHPAY;

--COMMENT 

SELECT * FROM USER_TAB_COMMENTS;

SELECT COUNT(*) FROM DICTIONARY;

SELECT * FROM DICTIONARY

WHERE TABLE_NAME='USER_CONSTRAINTS';

COMMENT ON TABLE HIGHPAY 

IS '연봉많이 받는 사람 개부럽';

--지우는방법

COMMENT ON TABLE HIGHPAY

IS '';

--컬럼 주석

SELECT * FROM BUSEO;

COMMENT  ON COLUMN BUSEO.POSITION

IS '부서 위치';

SELECT * FROM USER_COL_COMMENTS;

--DCL(GRANT/REVOKE) --관리자 명령어

--GRANT: USER나 OBJECT에 권한을 부여할때 사용함

--REVOKE: USER나 OBJECT에 권한을 회수할때 사용

--USER 생성

CREATE USER TEST 

IDENTIFIED BY TEST 

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP; --ORA-01031: 권한이 불충분합니다 

CREATE USER TEST 

IDENTIFIED BY TEST 

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP; --권한을 받은후 / ORA-01920: 사용자명 'TEST'(이)가 다른 사용자나 롤 이름과 상충됩니다

CREATE USER TEST1

IDENTIFIED BY TEST1

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;  --O

--OBJECT 권한 

--SELECT * FROM KIM.PERSONNEL; KIM 의 계정에서 PERSONNEL을 열겠다 !

GRANT SELECT ON PERSONNEL TO TEST;

GRANT SELECT,INSERT,DELETE ON DIVISION1 TO PARK;

--권한확인

SELECT * FROM USER_TAB_PRIVS_MADE;

--모든사용자에게 권한주기

SELECT * FROM BUSEO;

SELECT * FROM TAB;

GRANT SELECT ON KIM.BUSEO TO PUBLIC; --OBJECT 권한은 SYS가쓸수있음 

REVOKE SELECT ON BUSEO FROM PUBLIC;

REVOKE SELECT ON PERSONNEL FROM TEST;

REVOKE SELECT,DELETE,INSERT  ON DIVISION1 FROM TEST;

REVOKE SELECT PERSONNEL TO TEST;

--ROLE에 OBJECT권한 사용

CREATE ROLE KIMOBJ;

GRANT SELECT,INSERT,DELETE ON DIVISION1 TO KIMOBJ;

SELECT * FROM USER_TAB_PRIVS_MADE;

--------------------------------------------------------------------------------20190116

--제약조건

--PRIMARY KEY(PK)

--FOREIGN KEY(FK)

--UNIQUE KEY(UK)

--NOT NULL (NN)

--CHECK KEY(CK)

--PRIMARY KEY

--COLUMN LEVEL

CREATE TABLE CUSTOMER

(ID NUMBER(4) CONSTRAINT CUSTOMER_ID_PK PRIMARY KEY,   --내가 지정해주는 컬럼의 제약조건 

NAME VARCHAR2(10),

NO NUMBER(4));

SELECT * FROM USER_CONSTRAINTS;

--TABLE LEVEL (2개 이상의 기본 키를 지정해야 할 경우)

CREATE TABLE ORDERS 

(NO NUMBER(4),

SNO NUMBER(4),

NAME VARCHAR2(10),

COUNT NUMBER(7),

CONSTRAINT ORDERS_NO_PK PRIMARY KEY(NO));   --NO 이라는 컬럼에 PRIMARY KEY 를 지정/ 많이씀 

--설정하는 방법은 칼럼을 모두 정의하고 마지막에 정의된 칼럼들의 제약 조건을 한 번에 지정R

--두 개 이상의 기본 키를 지정하고자 할 때는 ','로 나열해야 한다. 

--외래 키를 지정할 때는 칼럼 레벨 방식과 다르게 FOREIGN KEY(칼럼명)을 먼저 정의해야 한다.

SELECT * FROM USER_CONSTRAINTS;

CREATE TABLE STUDENT 

(NO NUMBER(4) CONSTRAINT PK_STUDENT_NO PRIMARY KEY,

NAME VARCHAR2(10),

MAJOR VARCHAR2(10),

D_NUM NUMBER(4));

--FOREIGN KEY

SELECT * FROM CUSTOMER;

SELECT * FROM ORDERS;

DESC CUSTOMER;

DESC ORDERS;

ALTER TABLE CUSTOMER

ADD CONSTRAINT CUSTOMER_NO_FK FOREIGN KEY(NO)

REFERENCES ORDERS(NO);

SELECT * FROM USER_CONSTRAINTS;

CREATE TABLE CATALOG1

(CATALOGNO NUMBER(4) CONSTRAINT CATALOG1_CATALOGNO_PK PRIMARY KEY,

NAME VARCHAR2(10),

NO NUMBER(4) CONSTRAINT CATALOG1_NO_FK REFERENCES ORDERS(NO));     

--CATALOG_NO_FK ->ORDERS(NO)를 참조 

SELECT * FROM CATALOG1;

DESC CATALOG1;

SELECT * FROM USER_CONSTRAINTS;

SELECT * FROM ORDERS;

DESC ORDERS;

INSERT INTO ORDERS VALUES(10,20,'AA',3);

INSERT INTO ORDERS VALUES(20,30,'BB',4);

DESC COSTOMER;

INSERT INTO CUSTOMER VALUES(11,'A',10); --O

INSERT INTO CUSTOMER VALUES(22,'B',30); --X 

--ORA-02291: 무결성 제약조건(KIM.CUSTOMER_NO_FK)이 위배되었습니다

-- 부모 키가 없습니다

SELECT * FROM CATALOG1;

DESC CATALOG1;

INSERT INTO CATALOG1 VALUES(22,'A',20); --O

INSERT INTO CATALOG1 VALUES(33,'B',30); --X 

--ORA-00001: 무결성 제약 조건(KIM.CATALOG1_CATALOGNO_PK)

에 위배됩니다

--UNIQUE KEY

--중복값 허용안함

--NULL 허용

SELECT * FROM ORDERS;

DESC ORDERS;

ALTER TABLE ORDERS 

ADD CONSTRAINT ORDERS_SNO_UK UNIQUE (SNO);

SELECT * FROM USER_CONSTRAINTS;

SELECT * FROM ORDERS;  --SNO에 UK

INSERT INTO ORDERS VALUES (30,30,'CC',5); --X 

--ORA-00001: 무결성 제약 조건(KIM.ORDERS_SNO_UK)에 위배됩니다

INSERT INTO ORDERS VALUES (30,NULL,'CC',5); --O

INSERT INTO ORDERS VALUES (40,NULL,'DD',6); --O

CREATE TABLE TEST 

(ID NUMBER,

NAME VARCHAR2(10),

JUMIN VARCHAR2(14) CONSTRAINT TEST_JUMIN_UK UNIQUE);

SELECT * FROM USER_CONSTRAINTS;

--CHECK

SELECT * FROM ORDERS;

DESC ORDERS;

ALTER TABLE ORDERS

ADD CONSTRAINT ORDERS_SNO_CK CHECK(SNO BETWEEN 10 AND 50);

INSERT INTO ORDERS VALUES(50,11,'EE',5); --O

INSERT INTO ORDERS VALUES(60,51,'EE',5); --X

 --ORA-02290: 체크 제약조건(KIM.ORDERS_SNO_CK)이 위배되었습니다

INSERT INTO ORDERS VALUES(60,NULL,'EE',5); --O --NULL값 허용

--NOT NULL

CREATE TABLE TEST1

(NO NUMBER(4) CONSTRAINT TEST1_NO_NN NOT NULL);        --"NO"라는 컬럼에 제약조건

SELECT * FROM USER_CONSTRAINTS;

ALTER TABLE ORDERS

ADD CONSTRAINT ORDERS_NAME_NN CHECK(NAME IS NOT NULL); --NAME 자체가 조건!!!

--체크제약조건이라 ORDERS_NAME_CK라 적어줘야함

ALTER TABLE ORDERS

MODIFY NAME NOT NULL;

SELECT * FROM USER_CONSTRAINTS;

--SYS_C0011096     C      "NAME" IS NOT NULL

--제약조건 추가,삭제(수정은 없다)

SELECT * FROM USER_CONSTRAINTS

WHERE TABLE_NAME='STUDENT';

ALTER TABLE STUDENT

DROP CONSTRAINT PK_STUDENT_NO;

ALTER TABLE STUDENT

DROP PRIMARY KEY;

SELECT * FROM USER_CONSTRAINTS

WHERE TABLE_NAME='CATALOG1';

ALTER TABLE CATALOG1

DROP PRIMARY KEY;

SELECT * FROM USER_CONSTRAINTS

WHERE TABLE_NAME IN('ORDERS','CUSTOMER','CATALOG');

ALTER TABLE ORDERS

DROP PRIMARY KEY CASCADE;      --ORDERS_NO_PK 삭제하면 참조키인 CATALOG_NO_FK와 ORDERS_SNO_UK가 삭제됨

ALTER TABLE ORDERS

DROP CONSTRAINTS ORDERS_SNO_UK;

--제약조건 비활성화(잠깐 사용안하는거)

SELECT * FROM USER_CONSTRAINTS

WHERE TABLE_NAME='PERSONNEL' OR TABLE_NAME='DIVISION';

SELECT * FROM PERSONNEL;

INSERT INTO PERSONNEL (PNO) VALUES(111);

SELECT * FROM TAB;

SELECT * FROM PERSONNEL;

SELECT * FROM DIVISION;

SELECT * FROM USER_CONSTRAINTS

ALTER TABLE DIVISION

DISABLE PRIMARY KEY CASCADE;   --ORA-02297: 제약 조건(KIM.DIVISION_DNO_PK)을 사용 안함으로 설정 불가 - 종속성이 존재합니다.

--CASCADE로 DIVISION_DNO_PK,PERSONNEL_DNO_FK DICABLE 시켜줌

INSERT INTO DIVISION (DNO)VALUES (40);

SELECT * FROM DIVISION;

--활성화

ALTER TABLE DIVISION

ENABLE PRIMARY KEY; --X ORA-02437: (KIM.DIVISION_DNO_PK)을 검증할 수 없습니다 - 잘못된 기본 키입니다 / 왜냐면 이미 같은 KEY가 들어가있기때문에

DELETE DIVISION WHERE DNAME IS NULL;

SELECT * FROM USER_CONSTRAINTS

WHERE TABLE_NAME='PERSONNEL' OR TABLE_NAME='DIVISION';

ALTER TABLE PERSONNEL

ENABLE CONSTRAINT PERSONNEL_DNO_FK;

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

--DATA DICTIONARY

SELECT * FROM DICTIONARY;

SELECT * FROM USER_CONSTRAINTS;   --DICTIONARY

--USER_ : USER소유의 OBJECT정보

--ALL_  : USER에게 ACCESS가 허용된 OBJECT 정보

--DBA_  : DBA권한을 가진 USER소유의 OBJECT정보

--V$    : SERVER의 성능에 관련된 정보 

--1번테이블을 SELECT할수있게 A에게 권한을 주는건 OBJECT권한

--그러면 A테이블  + B의 테이블 ->ALL_

SELECT * FROM V$VERSION;

SELECT * FROM V$

SELECT * FROM USER_CONS_COLUMNS;

SELECT * FROM ALL_CONS_COLUMNS;   --내가 ACCESS할수 있는 컬럼

SELECT * FROM DBA_CONSTRAINTS;

 

SELECT * FROM SYSTEM_PRIVILEGE_MAP;

SELECT * FROM USER_TABLES;

SELECT * FROM USER_VIEWS;

--VIEW

--가상테이블

--보안을 위해사용 

SELECT * FROM PERSONNEL;

--SIMPLE VIEW 

CREATE VIEW PER10_V

AS

SELECT * FROM PERSONNEL WHERE DNO=10;

SELECT * FROM PER10_V;

--PER10_V는 테이블을 가지고있는게 아니라 (SELECT * FROM PERSONNEL WEHRE DNO=10; )을 가지고있음

CREATE VIEW PER20_V

AS

SELECT PNO,PNAME,MANAGER,PAY,DNO FROM PERSONNEL;

SELECT * FROM PER20_V;

SELECT * FROM TAB; --VIEW도 나오게됨 

CREATE VIEW PER_AVG

AS 

SELECT DNO,AVG(PAY) 평균 , SUM(PAY) 합계

FROM PERSONNEL

GROUP BY DNO;

SELECT * FROM PER_AVG;

SELECT * FROM PER_AVG WHERE DNO=10; --VIEW는 조건문도 사용가능

SELECT * FROM PER_AVG WHERE 합계>=8000;

SELECT DNO,합계 FROM PER_AVG WHERE 합계>=8000;

--INSERT ,UPDATE,DELETE 가 가능 

INSERT INTO PER20_V VALUES ( 1234,'HHHH',1001,2000,10);

SELECT * FROM PER20_V;

UPDATE PER20_V SET PNAME='AAAA' WHERE PNO=1234;

DELETE PER20_V WHERE PNO='1234';

--INSERT 에러

SELECT * FROM PERSONNEL;

CREATE VIEW PER 

AS 

SELECT PNAME, JOB, PAY FROM PERSONNEL;

SELECT * FROM PER;

INSERT INTO PER VALUES('SUZI','SALESMAN',5000); --ORA-01400: NULL을 ("KIM"."PERSONNEL"."PNO") 안에 삽입할 수 없습니다

--PK인  PNO 데이터값이 안들어갔기때문에 에러가남/PK값 필수!

--VIEW수정

--CREATE -> ALTER

SELECT * FROM USER_VIEWS;

CREATE OR REPLACE VIEW PER20_V 

(번호,이름,직업,부서번호)

AS 

SELECT PNO,PNAME,JOB,DNO FROM PERSONNEL

WHERE DNO=20;

SELECT * FROM PER20_V;

CREATE OR REPLACE VIEW AAA

AS

SELECT * FROM PERSONNEL WHERE DNO=10;

SELECT * FROM AAA;

--VIEW 삭제

SELECT * FROM USER_VIEWS;

DROP VIEW AAA;

DROP VIEW PER_AVG;

--COMPLEX VIEW (JOIN) --여러개의 TABLE

--SELECT는 잘되는데 INSERT ,DELETE ,UPDATE 불가

CREATE TABLE 고객정보 

(고객번호 CHAR(10),

이름 CHAR(10));

CREATE TABLE 회사정보

(고객번호 CHAR(10),

회사명 CHAR(10));

DESC 고개정보;

DESC 회사정보;

INSERT INTO 고객정보 VALUES('A001','배수지');

INSERT INTO 고객정보 VALUES('A002','홍길동');

SELECT * FROM 고객정보;

INSERT INTO 회사정보 VALUES('A001','LG');

INSERT INTO 회사정보 VALUES('A002','HYUNDAI');

SELECT * FROM 회사정보;

CREATE VIEW 정보

AS 

SELECT A.고객번호,이름,회사명

FROM 고객정보 A , 회사정보 B 

WHERE A.고객번호=B.고객번호;

SELECT * FROM 정보; --

INSERT INTO 정보 VALUES ('A003','전지현','SAMSUNG'); 

--ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

INSERT INTO 정보 (고객번호,이름) VALUES('A003','전지현'); --X

INSERT INTO 정보 (회사명) VALUES('SAMSUNG');--X

UPDATE 정보 SET 이름 ='이수지' WHERE 고객번호='A001'; 

--ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

DELETE 정보 WHERE 고객번호='A001'; 

--ORA-01752: 뷰으로 부터 정확하게 하나의 키-보전된 테이블 없이 삭제할 수 없습니다

--WITH CHECK OPTION

SELECT * FROM PER10_V;

UPDATE PER10_V SET DNO=20 WHERE PNO=1111;

SELECT * FROM PERSONNEL;

CREATE OR REPLACE VIEW PER10_V

AS

SELECT * FROM PERSONNEL WHERE DNO=10

WITH CHECK OPTION CONSTRAINT PER10_VU_CH;

SELECT * FROM PER10_V;

UPDATE PER10_V SET DNO=20 WHERE PNO=1001;  --ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

DELETE PER10_V WHERE PNO=1001; --삭제는 가능함 DNO를 수정불가

--WITH READ ONLY 

--SELECT 만 가능

CREATE OR REPLACE VIEW PER10_V

AS

SELECT PNO , PNAME,JOB FROM PERSONNEL

WHERE DNO=10

WITH READ ONLY;

SELECT * FROM PER10_V;

DELETE PER10_V WHERE PNO=1118; --ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

UPDATE PER10_V SET JOB='AAA' WHERE PNO=1118; 

--ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

INSERT INTO PER10_V VALUES (1111,'KIM','SALESMAN'); --X

--TOP-N

--가장 최근에 입사한 5명의 사원의 정보

SELECT ROWNUM AS FASTDATE ,PNAME,STARTDATE

FROM (SELECT PNAME,STARTDATE FROM PERSONNEL

ORDER BY STARTDATE DESC)  --()로 나온거로 부터 

WHERE ROWNUM <= 5;

SELECT FASTDATE,PNAME,STARTDATE FROM

(SELECT ROWNUM FASTDATE , PNAME, STARTDATE FROM 

(SELECT PNAME,STARTDATE FROM PERSONNEL

ORDER BY STARTDATE DESC))

WHERE FASTDATE>=2 AND FASTDATE<=4;

--MS-SQL

--SELECT TOP 5 PNAME,STARTDATE FROM PERSONNEL ORDER BY STARTDATE DESC ;

--SELECT TOP 5 PERCENT PNAME,STARTDATE FROM PERSONNEL ORDER BY STARTDATE DESC ;

SELECT ROWNUM FROM PERSONNEL;

SELECT ROWID FROM PERSONNEL;

--AAASRRAAEAAAAIdAAA --순서대로 

--AAASRRAAEAAAAIdAAB

--SYNONYM동의어

CREATE SYNONYM SALARY

FOR PAYGRADE;

SELECT * FROM SALARY;

CREATE SYNONYM INSA

FOR PERSONNEL;

SELECT * FROM INSA;

--동의어 삭제 

SELECT * FROM USER_SYNONYMS;

DROP SYNONYM SALARY;

SELECT * FROM SALARY;  --X

--INDEX(SQL 명령문의 처리 속도를 향상시키기 위하여 컬럼에 생성하는 오라클 객체)

CREATE INDEX PER_PAY_IDX

ON PERSONNEL(PAY);

SELECT * FROM USER_INDEXES;

--PERSONNEL_PNO_PKPER_PAY_IDXDIVISION_DNO_PK

CREATE TABLE AAA

(ID NUMBER(4) CONSTRAINT AAA_ID_IDX PRIMARY KEY,

NAME CHAR(10));

SELECT * FROM USER_CONSTRAINTS;     --AAA_ID_IDX(PRIMARY KEY를 만들면 INDEX자동생성)

SELECT * FROM USER_INDEXES;      --AAA_ID_IDX

--NON-CLUSTERED

INSERT INTO AAA VALUES(3,'K');

INSERT INTO AAA VALUES(2,'A');

INSERT INTO AAA VALUES(1,'B');

INSERT INTO AAA VALUES(4,'C');

SELECT * FROM AAA;

ALTER TABLE AAA

DROP PRIMARY KEY;

SELECT * FROM USER_INDEXES; --PRIMARY KEY지우면 INDEX도 지워짐 

SELECT * FROM USER_IND_COLUMNS;--INDEX는 컬럼마다 들어감

--SEQUENCE(일렬번호) --실제 DB에 넣는것 (시작과 끝값을 지정해주고 증가값을 넣어주면 자동으로 들어감 /BUT ,중간에 삭제되면 (1,2,3->1,3) 일렬번호의 역할은 제대로X

--하지만 고유의 값은 가지고 있음

--MAX값에 +1하는 방법도 있음

--ROWNUM -DB에 저장되어있지 않음

SELECT ROWNUM,PNAME FROM PERSONNEL;

--CREATE SEQUENCE DIV_DNO;

--START WITH 1 

--INCREMENT BY 1

--MAXVALUE 100 | NOMAXVALUE

--CYCLE | NOCYCLE --1부터 시작해서 1씩 증가해서 100까지 반복/ 노반복 

--CACHE 20| NOCACHE --메모리에 일정 갯수만큼 로딩시켜놓는거 (SEQUENCE에 받아서)

CREATE SEQUENCE DIV_DNO

START WITH 90

INCREMENT BY 1

MAXVALUE 99

NOCYCLE

NOCACHE;

SELECT * FROM USER_SEQUENCES;

CREATE SEQUENCE PER_PNO

INCREMENT BY 1

START WITH 10

MAXVALUE 100 

NOCYCLE 

NOCACHE;

--NEXTVAL : SEQUENCE 의 다음 번호 (SEQUENCE 이름.NEXTVAL)

--CURRVAL : SEQUENCE 의 현재 번호 (SEQUENCE 이름.CURRVAL)

SELECT DIV_DNO.NEXTVAL FROM DUAL; --90 --그럼 90은 이제 못쓰는거 / 왜냐면 한번 썼기때문에 

SELECT DIV_DNO.CURRVAL FROM DUAL; --방금 쏜 총알 몇번!!!90번!!!

SELECT * FROM DIVISION;

INSERT INTO DIVISION VALUES

(DIV_DNO.NEXTVAL,'AA','123','AA'); --DNO : 91

INSERT INTO DIVISION VALUES

(DIV_DNO.NEXTVAL,'BB','123','BB'); --DNO : 92

--수정

ALTER SEQUENCE DIV_DNO

INCREMENT BY 1

MAXVALUE 999

NOCYCLE 

NOCACHE;

SELECT * FROM USER_SEQUENCES;

ALTER SEQUENCE DIV_DNO

INCREMENT BY 10; --증가값만 10으로 바뀜

INSERT INTO DIVISION VALUES

(DIV_DNO.NEXTVAL,'CC','123','CC');   --X / WHY? DNO 자리값은 2자리 , 92다음엔 102가 들어와야함 

그래서 안됨../ 사용했으니 102는 날라감 

    --그래서 LAST_NUMBER 가 112 됨 

SELECT * FROM DIVISION;

DESC DIVISION;

INSERT INTO PERSONNEL(PNO) VALUES (PER_PNO.NEXTVAL);

SELECT * FROM PERSONNEL; --PNO에 10이 들어감

INSERT INTO DIVISION (DNO) VALUES (PER_PNO.NEXTVAL);

SELECT * FROM DIVISION; --PNO에 11이 들어감

INSERT INTO PERSONNEL(PNO) VALUES (PER_PNO.NEXTVAL);

SELECT * FROM PERSONNEL;   --PNO에 12이 들어감

--따라서 각 TABLE에 PNO값에 일렬번호에 갭이생김 

ALTER SEQUENCE DIV_DNO

INCREMENT BY 1

MAXVALUE 999

NOCYCLE 

CACHE 20;

SELECT * FROM USER_SEQUENCES;

DROP SEQUENCE PER_PNO;

--MS-SQL

--CREATE TABLE AA

--(ID INT IDENTIFY(1,1), --TABLE을 만들면서 1부터 시작해서 1씩 증가하는

--NAME CHAR(10));

--MY-SQL

--CREATE TABLE AA

--(ID INT AUTOINCREMENT, --무조건 자동으로 들어감 / 한번사용한거는 다시 사용X

--NAME CHAR(10));