오늘 정리
우리가 사용하고 있는 오라클은? 관계형 데이터베이스 관리 시스템 중 하나이다. SQL은 데이터베이스 구축, 활용에 사용되는 언어를 말한다. 본 교육과정에서는 오라클 툴로 Toad를 사용하였다.
쿼리 속도 향상? 데이터를 조회할 때 드는 비용을 줄이고, 속도를 높이기 위한 쿼리문 작성이 필요하다. 그 대표적인 예로는 조회하는 양을 조건절로 줄이거나 자주 사용해야 하는 데이터를 PK로 지정한다.
복습
-
쿼리속도 향상 원리
- index - PK는 제공 받음 → 테이블 전체 조회 전 인덱스부터 확인 → 속도 빠름
- 인라인뷰(서브쿼리) : from절에 들어가기 때문에 조회 대상값이 줄어들기 때문에 속도향상
- index - PK는 제공 받음 → 테이블 전체 조회 전 인덱스부터 확인 → 속도 빠름
-
실행 순서 : 파싱(문법 체크) → 실행(who?? RDBMS, 옵티마이저 전달, open cursor) → 인출 → 종료
-
그룹함수 : max, min, avg 등 -> 전체 범위 처리
SELECT
to_char(max(sal), '999,999,999')||'만원' AS "max"
, to_char(min(sal), '999,999,999')||'만원' AS "min"
, to_char(round(avg(sal)), '999,999,999')||'만원' AS "avg"
FROM emp;
--5,000만원 800만원 2,073만원- 테이블 추가
- row 증감 : unioin, union all, intersect, minus 등
- colurm 증가 : 열거형으로 증가
강의 내용
[💡Toad tip]
- Describe 보기(F4)
- 화면 클리어
- 쿼리실행(Ctrl + enter)
- 사용자 전환(Session → New connection)
- explain plan 확인하기(Ctrl + E)
- why? 성능이슈 파악(일의 양 계산), 조인방식과 순서, 인덱스 사용여부 등 확인 가능
- 주석처리(Ctrl + B) / 해제(Ctrl + Shift + B)
- 오라클 관리자 계정 비번 찾기/변경 (https://m.blog.naver.com/baekmg1988/221298569322)
SELECT sql_text,sharable_mem, executions FROM v$sqlarea WHERE sql_text LIKE 'select ename%'
[서브쿼리]
- 쿼리 문 안에 포함되어 있는 또 다른 쿼리 문을 말함.
- 주의사항
- 서브쿼리를 괄호로 감싸서 사용
- 서브쿼리는 단일행(=, <, <=, >, >=, <>) or 복수행 비교연산자(IN, ALL, ANY, SOME)와 함께 사용 가능
- 서브쿼리에서는 order by를 사용하지 못 한다.
- 인라인뷰(from 절 쿼리)
- 서브쿼리문에서 사용된 컬럼명으로 주쿼리문에서도 작성이 가능
- 동적으로 생성된 테이블인 것 처럼 사용할 수 있음.
-6.TEMP의 자료 중 EMP_ID와 EMP_NAME을 각각 ‘사번’,’성명’으로 표시되어
--DISPLAY되도록 COLUMN ALLIAS를 부여하여 SELECT 하시오.
SELECT to_char(emp_id)||'번' AS "사번", emp_name AS "성명"
FROM temp;
SELECT 사번, 성명
FROM(
SELECT
emp_id AS "사번", emp_name AS "성명"
FROM temp
);- 서브쿼리(where 절, having 절 쿼리) ****
-
그룹함수와 함께 사용될 때 그룹핑 결과에 부가적 조건을 줄 수 있음.
--2.김유신씨가 보유하고 있는 마일리지 포인트로 얻을 수 있는 상품 중 --가장 포인트가 높은 것은 무엇인가? --김유신 포인트 가져오기 SELECT mem.NAME_vc AS "이름", mem.point_nu AS "포인트" FROM t_giftmem mem WHERE mem.Name_vc = '김유신'; --가장 포인트 높은 상품 SELECT max(poi.point_nu) FROM t_giftpoint poi WHERE point_nu<=50012; --두 개 같이 SELECT poi.name_vc AS "상품" FROM t_giftpoint poi WHERE point_nu = ( SELECT max(poi.point_nu) FROM t_giftpoint poi WHERE point_nu<=50012 );
-
[구문 관련]
- SELECT문
- commit, rollback 대상 아닌 only read 구문
- Group by
- Having과 같이 사용함.
--8. 부서별 급여 평균을 구하시오
SELECT
deptno AS "부서번호",
to_char(round(avg(sal)),'9,999')||'만원' AS "평균급여"
FROM emp
GROUP BY deptno
HAVING avg(sal)>2000
ORDER BY 평균급여 desc;
--부서번호 평균급여
--10 2,917만원
--20 2,175만원- 데이터타입(자주 사용한 것만)
- 가변형 : 빈 값 채워줌.
- 고정형 : 빈 값 그대로 둠
| 데이터 유형 | 정의 |
|---|---|
| CHAR(n) | 고정 길이 문자열(최대 2000 BYTE) - 지정된 길이 보다 짧게 데이터 입력될 시 나머지 공간은 공백으로 채운다. |
| VARCHAR2(n) | 가변 길이 문자열 (최대 4000BYTE) - 지정된 길이보다 짧게 데이터가 입력될 시 나머지 공간은 채우지 않는다. |
| NUMBER(P,S) | P: 1~38자리,디폴트 값은 38 (22BYTE) S: 소수점 이하 자리 (디폴트 : 0) |
| DATE | 고정 길이 날짜 데이터 타입. (날짜 연산 수행시 많이 쓰인다.) |
| TIMPSTAMP_WITH TIME ZONE | 날짜 및 시간대 형태의 타입 |
[Giftmem & GiftPoint 실습]
- ERwin은 프로젝트 설계 과정에서 효과적인 데이터모델링 을 생성 할 수 있도록 도와주는 윈도우 기반 도구이다.
- ERwin Data Modeler 사용(데이터 많이 볼 경우)
-
집합과 집합의 관계 도식화
-
[lecture 데이터 실습]
- Decode : 같으면??? 일반적인 if문을 sql에서 사용하기 위해 만들어진 오라클 함수
SELECT decode(1,1,'t'), --t
decode(1,2,'t','f') --f
FROM dual;
--학점, 시수가 같은 경우, 일반과목을 돌려받기
SELECT lec_id, lec_time, lec_point, decode(lec_time,lec_point,'일반과목','다름')
FROM lecture;
- decode 부등호 쓸 수 없을 때 sign 같이 사용
- orderby에 select에 쓰인 decode 부분이 입력될 수 있음. (정렬은 기본값이 ASC임)
--3.강의시간과 학점이 같거나 강의시간<학점 '일반과목',
--강의시간>학점 '실험과목',
SELECT decode(lec_time-lec_point,1,'실험과목','일반과목') AS "구분"
FROM lecture;
--3-1.lec_time이 크면 '실험과목, lec_point가 크면 '기타과목',
--같으면 '일반과목'으로 돌려받기
SELECT decode(sign(lec_time-lec_point),1,'기타과목','일반과목') AS "구분"
FROM lecture;
--3-2. 강의시간 = 학점, '일반과목' & 정렬
SELECT
lec_id,decode(lec_time,lec_point,'일반과목', NULL) AS "과목"
FROM lecture
ORDER BY decode(lec_time,lec_point,'일반과목', NULL) ASC;- dual : 오라클에서 제공되는 가상테이블(1*1) → 함수 테스트 시 사용
- 형전환 함수
- to_char : 문자열로 전환, 문자열 더할 때는|| (concat)
- to_number : 숫자로 전환
강의 마무리
- 자바복습(배열, 자료구조 List, 인스턴스, 클래스, 메소드(파라미터, 리턴타입) 등)
