07_데이터베이스(기타)

시퀀스

   · create sequence 시퀀스이름 [옵션]; create sequence student_seq;
   · max value 표기 가능한 최대 수
   · increment_by 증가하는 숫자
   · cycle_flag   max 넘어갈 시 1로 돌아갈지(디폴트 no)
   · last_number 캐시사이즈

      - create sequence item_seq no cache;  캐시사이즈 사용 x
      - create sequence item_seq cache 100; 캐시사이즈 100
   · 사용

시퀀스이름.nextval values(student_seq.nextval, ?);

   · 삭제

drop sequence 시퀀스이름;

   · 조회

SELECT board_seq.currval AS seq_num(표시할이름) FROM dual;  --쓰인 번호 조회

단일행 함수

   · 결과집합의 행마다 연산하는 함수

SELECT member_id, upper(member_id) 대문자 FROM MEMBER;
SELECT member_id, lower(member_id) 소문자 FROM MEMBER;
SELECT * FROM MEMBER WHERE instr(member_id, 'kh')>0;

집계 함수

   · 데이터를 종합해서 하나의 결과를 만드는 함수(합계, 평균, 개수, 최대, 최소)

SELECT AVG(price) 평균 FROM subject;
SELECT max(price) 최대 FROM subject;
SELECT min(price) 최소 FROM subject;
SELECT count(price) 개수 FROM SUBJECT; --가격이 몇 개 있는가?(null 제외)
SELECT count(*) 개수 FROM product; --데이터가 몇 개 있는가(NULL 포함)
SELECT sum(price) 합계, AVG(price) 평균 FROM SUBJECT;

서브쿼리(sub query)

SELECT name FROM product WHERE price = (SELECT max(price) FROM product);

rownum

SELECT tmp.*, rownum FROM (SELECT * FROM MEMBER ORDER BY member_join asc) tmp 
WHERE rownum BETWEEN 1 AND 10;

Top n query

SELECT * FROM (
SELECT tmp.*, rownum rn FROM (
SELECT * FROM MEMBER ORDER BY member_join ASC
) tmp
) WHERE rn BETWEEN 2 AND 10;

그룹

SELECT TYPE FROM product GROUP BY TYPE;
SELECT TYPE, avg(price) FROM product GROUP BY TYPE;
SELECT TYPE, count(*) FROM product GROUP BY TYPE;
SELECT TYPE, sum(price) 합계 FROM product 
	GROUP BY TYPE ORDER BY 합계 DESC;
SELECT to_char(made, 'yyyy'), count(*) FROM product 
	GROUP BY to_char(made, 'yyyy'); 	--연도별 개수 출력
SELECT extract(YEAR FROM made) 연도, count(*) FROM product 
	GROUP BY extract(YEAR FROM made); 	--연도별 개수 출력
SELECT TYPE, count(*) FROM product 
	GROUP BY TYPE HAVING count(*)>=3; 	--having으로 그룹 조건 활용
select count(*) from board_like 
	group by board_like_no having board_like_no=20
SELECT extract(YEAR FROM made), TYPE, count(*) FROM product 
	GROUP BY extract(YEAR FROM made), TYPE; --그룹이 여러항목

중복제거(항목만 뽑기)

SELECT DISTINCT TYPE FROM product;

뷰

   · 권한부여  GRANT CREATE VIEW TO kh11;
   · 생성  

CREATE VIEW pocketmon_stat as 
	SELECT TYPE, count(*) cnt FROM pocketmon 
	GROUP BY TYPE ORDER BY cnt DESC, TYPE asc;

   · 조회 

SELECT * FROM pocketmon_stat;

   · 덮어쓰기 

CREATE OR REPLACE VIEW subject_stat AS 
	SELECT TYPE, count(*) cnt, avg(price) avg 
    FROM subject GROUP BY TYPE;

dual

   · 한번만 찍고 싶을 때 쓰는 정해지지 않는 임시 테이블

select sysdate, systimestamp, board_seq.nextval from dual;

집합

   · union all - 합집합(중복제거 x)
   · union distinct - 합집합(중복제거)
   · intersect - 교집합
   · minus - 차집합

SELECT *  FROM board_notice UNION ALL SELECT * FROM board_ohter;

테이블 조인(Table Join)

  · 내부조인(Inner Join) : 매칭되는(연결된) 데이터만 조인(매칭되는거 없으면 빠져버림)

select 항목 from A테이블 inner join B테이블 on 연결조건
SELECT A.*, R.room_no, R.room_name, R.room_size FROM academy A 
		INNER JOIN room R ON A.academy_no = R.academy_no;

  · 외부조인(Outer Join) : 특정 테이블은 전부 조회(기준(방향)이 필요)
      - A ← B 면 A left outer join B
      - A → B 면 A right outer join B
      - A ↔ B면 A full outer join B

SELECT A.academy_name, count(R.room_no) FROM academy A 
	LEFT OUTER JOIN room R ON A.academy_no=R.academy_no 
	GROUP BY A.academy_name;