24년 10월 17일

DB 로 넘어갔다.

근데 mysql 아니고 오라클을 사용한다.

외부평가에서는 오라클을 사용한다고 한다.

---

 

오라클 시퀀스란?

 

유일한 값을 생성해주는 오라클 객체

기본키와 같이 순차적으로 증가하는 컬럼을 생성할 수 있다.

주로 기본키(PRIMARY KEY)를 생성하기 위해 사용.

 

constraint (제약조건)

조건에 맞지않는 데이터를 입력시키지 않기 위한 제약조건

외부평가에서 사용하는 dbms 오라클

 

SELECT * FROM USERTBL WHERE HEIGHT > ANY 
(SELECT HEIGHT FROM USERTBL WHERE ADDR='경남');

SELECT * FROM USERTBL WHERE HEIGHT > ALL
(SELECT HEIGHT FROM USERTBL WHERE ADDR='경남');

SELECT * FROM BUYTBL WHERE PRICE > (SELECT PRICE FROM BUYTBL WHERE AMOUNT =10);
SELECT * FROM BUYTBL;
SELECT * FROM BUYTBL WHERE AMOUNT > ALL (SELECT AMOUNT FROM BUYTBL WHERE USERID LIKE 'K%');

SELECT AMOUNT , PRICE FROM BUYTBL;
SELECT * FROM BUYTBL WHERE PRICE > ALL 
(SELECT PRICE FROM BUYTBL WHERE AMOUNT =5);

--구조복사 + 값 같이 복사

CREATE TABLE TBL_BUY AS SELECT * FROM BUYTBL;  //테이블을 복사

SELECT * FROM all_constraints WHERE TABLE_NAME='BUYTBL'; //테이블의 제약조건을 확인한다

CREATE TABLE TBL_BUY2 AS SELECT * FROM BUYTBL WHERE 1=2; 
1=2 는 FALSE 이므로 항상 거짓을 만들어서 데이터는 복사하지 않고
테이블 구조만 복사한다.

DESC TBL_BUY2;

SELECT * FROM all_constraints WHERE TABLE_NAME='TBL_BUY2';
SELECT * FROM TBL_BUY2;

INSERT INTO TBL_BUY2 SELECT * FROM BUYTBL;

SELECT NAME,mDATE FROM USERTBL
ORDER BY MDATE DESC;   -- 데이터가 많을 때 정렬을 하면 부하가 많이 든다고 한다. 인덱스처리를 해주는게 좋다고함

SELECT NAME,MDATE,HEIGHT FROM USERTBL ORDER BY HEIGHT DESC,NAME ASC;

SELECT DISTINCT HEIGHT FROM USERTBL ORDER BY HEIGHT DESC;

SELECT * FROM USERTBL;
SELECT * FROM    
(SELECT ROWNUM AS RN,USERTBL.* FROM USERTBL) WHERE RN >= 3 AND RN<7;   --ROWNUM : 행 번호 , 오라클에만 있는 기능이라고함 , 인덱스 처리..

SELECT * FROM BUYTBL;
SELECT * FROM BUYTBL
ORDER BY USERID ASC;
SELECT * FROM BUYTBL 
ORDER BY PRICE DESC;
SELECT * FROM BUYTBL
ORDER BY AMONUT ASC,PRODNAME DESC;
SELECT DISTINCT PRODNAME FROM BUYTBL;
SELECT DISTINCT USERID FROM BUYTBL;
SELECT * FROM BUYTBL WHERE AMOUNT > 3
ORDER BY PRODNAME DESC;
CREATE TABLE CUsertbl AS (SELECT * FROM USERTBL WHERE ADDR IN('서울','경기'));
SELECT * FROM USERTBL WHERE ADDR IN('서울','경기');
SELECT * FROM CUsertbl;
CREATE TABLE CUsertbl2 AS SELECT * FROM USERTBL WHERE ADDR IN('서울','경기');

SELECT USERID , AMOUNT FROM BUYTBL
ORDER BY USERID;

SELECT * FROM BUYTBL;
SELECT USERID,AMOUNT FROM BUYTBL GROUP BY USERID;

SELECT USERID,SUM(AMOUNT) AS "구매총량" FROM BUYTBL GROUP BY USERID;

SELECT * FROM BUYTBL;

SELECT PRODNAME,SUM(AMOUNT) AS 구매총량 FROM BUYTBL GROUP BY PRODNAME;

SELECT USERID,AVG(AMOUNT*PRICE) AS 구매평균 FROM BUYTBL GROUP BY USERID;

SELECT COUNT(*) FROM BUYTBL;
SELECT AVG(SUM(AMOUNT)) FROM BUYTBL;

SELECT MIN(AMOUNT) FROM BUYTBL;
 ------ 서브쿼리 -------
 
SELECT * FROM USERTBL WHERE HEIGHT =(SELECT MIN(HEIGHT) FROM USERTBL )
OR
HEIGHT = (SELECT MAX(HEIGHT) FROM USERTBL);

SELECT * FROM BUYTBL WHERE AMOUNT > (SELECT *,AVG(AMOUNT) AVG FROM BUYTBL);

SELECT TRUNC(AVG(AMOUNT),5) AVG FROM BUYTBL;

SELECT USERID,SUM(AMOUNT) FROM BUYTBL GROUP BY USERID;

SELECT (SELECT AVG(HEIGHT) FROM USERTBL) FROM USERTBL;

SELECT  DISTINCT AMOUNT,GROUPNAME FROM BUYTBL
WHERE AMOUNT =(SELECT MAX(AMOUNT) FROM BUYTBL)
OR
AMOUNT = (SELECT MIN(AMOUNT) FROM BUYTBL);

SELECT COUNT(GROUPNAME) FROM BUYTBL ;


SELECT USERID,SUM(PRICE*AMOUNT) AS A FROM BUYTBL GROUP BY USERID HAVING A = 100;

SELECT GROUPNAME,SUM(PRICE*AMOUNT) AS 비용 FROM BUYTBL
GROUP BY ROLLUP(GROUPNAME); 

SELECT * FROM BUYTBL;
--1
SELECT (SELECT SUM(PRICE*AMOUNT) FROM BUYTBL WHERE PRODNAME='모니터') FROM BUYTBL GROUP BY PRODNAME;
SELECT USERID,PRODNAME,SUM(PRICE*AMOUNT) FROM BUYTBL GROUP BY PRODNAME,USERID;
--2
SELECT USERID,PRODNAME,SUM(PRICE*AMOUNT) FROM BUYTBL GROUP BY PRODNAME,USERID HAVING SUM(PRICE*AMOUNT) >= 100;
--3
SELECT USERID,PRODNAME,PRICE FROM BUYTBL 
WHERE PRICE = (SELECT MIN(PRICE) FROM BUYTBL)
OR PRICE = (SELECT MAX(PRICE) FROM BUYTBL);
--4
SELECT * FROM BUYTBL WHERE GROUPNAME IS NOT NULL;
--5
SELECT PRODNAME,SUM(PRICE*AMOUNT) AS 총합 FROM BUYTBL GROUP BY ROLLUP(PRODNAME,USERID);


create table tmp(idx number(10),name varchar(1000));
desc tmp;

drop table tmp_seq;
drop table sequence tmp;

create sequence tmp_seq start with 2 //2 부터 시작하는 시퀸스를 생성하겠다.
increment by 1  //1씩 증가함
maxvalue 100   //최대값이 100이다.
cycle nocache;   //값을 미리 캐시하지 않겠다.

select * from tmp;
insert into tmp values(tmp_seq.nextval,'a1');
insert into tmp values(tmp_seq.nextval,'a2');
insert into tmp values(tmp_seq.nextval,'a3');  

select * from user_sequences where sequence_name='TMP_SEQ';

CREATE TABLE TEST_01(
    USERID CHAR(10) PRIMARY KEY,
    NAME CHAR(10) NOT NULL);
    
    DESC TEST_01;
    SELECT * FROM all_constraints WHERE TABLE_NAME='TEST_01' AND CONSTRAINT_TYPE='P';
    SELECT * FROM all_cons_columns WHERE TABLE_NAME='TEST_01';
    
    SELECT TMP_SEQ.CURRVAL FROM DUAL;
    INSERT INTO EMP ;SELECT * FROM all_constraints WHERE TABLE_NAME='TEST_01' AND CONSTRAINT_TYPE='P';
    
    CREATE TABLE TEST_02(
    USERID CHAR(10) , 
    NAME CHAR(10) NOT NULL
    PRIMARY KEY(USERID);

 SELECT * FROM all_constraints WHERE TABLE_NAME='TEST_01' AND CONSTRAINT_TYPE='P';

SELECT TMP_SEQ.CURRVAL FROM DUAL;
    INSERT INTO EMP ;SELECT * FROM all_constraints WHERE TABLE_NAME='TEST_02' AND CONSTRAINT_TYPE='P';
    
SELECT * FROM ALL_CONSTRA WHERE TABLE_NAME-'TEST'01' AND BETWWEN ='9;

ALTER TABLE TEST_01 ADD CONSTRAINT PK_USED='TEST01' AND CONSTRAINT_TYPE4;

CREATE TABLE TEST_03
(
    PRODcODE CHAR(20) NOT NULL,
    PRODNAME CHAR(100) NOT NULL,
    pRODCHAR CHAR(20) NULL);
    
    SELECT * FROM TEST_03;
    
 ALTER TABLE TEST_03 ADD CONSTRAINT PK_PCODE_PID PRIMARY KEY(PRODCODE);
 
 SELECT * FROM all_constraints WHERE TABLE_NAME = 'TEST_03' AND CONSTRAINT_TYPE='P';
 
 create table prod_tbl
 (
    prod_id number primary key,
    prod_name varchar(40));
    
desc prod_tbl;          

CREATE TABLE BUY_TBL
(
    BUY_ID NUMBER,
    PROD_ID NUMBER,
    ORDER_DATE DATE,
    CONSTRAINT FK_PRODTBL_BUYTBL FOREIGN KEY(PROD_ID) REFERENCES PROD_TBL(PROD_ID));
    
    DESC BUY_TBL;
    COMMIT;
--제약 조건 확인 C : CHECK,NOT NULL , P:PRIMARY KEY , R: FOREIGN KEY , U: UNIQUE    
SELECT * FROM all_constraints WHERE TABLE_NAME='BUY_TBL';
--컬럼별 제약조건을 조회하는 쿼리
SELECT * FROM ALL_CONS_COLUMNS WHERE CONSTRAINT_NAME = 'FK_PRODTBL_BUYTBL';

--외래키 삭제
ALTER TABLE BUY_TBL DROP CONSTRAINT FK_PRODTBL_BUYTBL;

--제약조건 확인
SELECT * FROM all_constraints WHERE TABLE_NAME='BUY_TBL';

-- 외래키 추가
ALTER TABLE BUY_TBL ADD CONSTRAINT FK_PRODTBL_BUYTBL_RE FOREIGN KEY (PROD_ID) REFERENCES PROD_TBL (PROD_ID);
-- 오라클은 딜리트 캐스케이드는 되는데 업데이트 캐스케이드는 안되서 트리거라는걸 쓴다고함

-- 옵션 추가
ALTER TABLE BUY_TBL DROP CONSTRAINT FK_PRODTBL_BUYTBL_RE;

ALTER TABLE BUY_TBL
ADD CONSTRAINT FK_PRODTBL_BUYTBL_RE_OP
FOREIGN KEY (PROD_ID)
REFERENCES PROD_TBL(PROD_ID)
ON DELETE CASCADE;  -- ON UPDATE CASCADE 는 오라클에서 지원안함 트리거 설정을 통해서 처리해야함

SELECT * FROM all_constraints WHERE TABLE_NAME='BUY_TBL';

/* SELECT , WHERE , GROUP BY ORDER BY 연산자들 PRIMARY KEY , FORIEGN KEY 지정 , 시퀀스= INDEX 자동증가 ,