**오라클이 제공하는 함수
1. 숫자 관련 함수
round( 값 이나 컬럼 , 자릿 수 ) : 값이 컬럼의 값을 자릴 수 아래에서 반올림
자릿수를 지정하지 않으면 소수 첫째자리에서 반올림해서 정수로 표현
2) trunc는 round와 동일한 데 소수를 버림
3) mod는 나머지 구해주는 함수
2) 문자 함수
1) upper, lower : 대문자나 소문자로 변경해주는 함수
영문 검색을 할 때 대소문자 구분없이 검색을 하는 경우에 사용
2) TRIM,LTRIM, RTRIM : 좌우 공백 제거
3) substr : 문자열을 잘라주는 함수
게시판에서 제목을 일정 부분만 출력하고 상세보기
4) LPAD, RPAD : 왼쪽이나 오른쪽에 특정한 문자를 채우는 함수
5) length : 문자의 길이를 리턴해주는 함수
--문자 관련 함수
select length('오라클'), upper('Oracle'), lpad('Oracle', 20,'*'),
substr('Oracle',2),substr('Oracle',2,3),trim(' Oracle ')
from dual
3. 형변환 함수
숫자나 날짜를 문자열로 변환 : to_char
문자열을 숫자로 변환 : to_number
문자열을 날짜로 변환 : to_date
=> 년도는 yy, yyyy,로 나타내고 월은 mm 일은 dd 시간은 h24, h12 분은 MI 초는 ss
4. NVL
=> null 인 데이터가 있을 때 변환해주는 함수
=> 데이터베이스에서는 NULL은 아직 알려지지 않은 값
=> NULL은 다른 데이터와 연산할 수 없어서 NULL을 가지고 연살을 하면 결과도 NULL이됩니다.
=> 집계 함수 Sum, AVG,COUNT, Min,MAX) 는 NULL인 경우 연산에서 제외합니다.
=> 집계함수 연산이나 다른데이터와 연산할 때 NULL을 다른 데이터로 치환해서 수행해야합니다.
=> NVL( 컬럼이름이나 연산식, 치환할 값)
5. 그룹함수
sum, avg, max, min, count : null을 제외하고 연산
=> 평균을 구할 때 null 포함여부를 고려해야 합니다.
=> count 를 사용할 때는 컬럼이름을 직접 사용하는 것보다는 *을 이용하는 것이 좋습니다,.
=> emp 테이블에는 comm이 null인 데이터가 포함되어 있습니다
-- emp 테이블에서 comm의 평균 구하기
--null인 데이터를 제외하고 평균을 구하기
select avg (comm)
from emp ;
-- comm이 null인 데이터는 0으로 간주하고 평균을 구하기
select avg(nvl(comm, 0))
from emp;
--null인 데이터를 포함한 평균
select sum(comm)/count(*)
from emp;
**group by
=> 그룹화를 위한 절
select 조회할 컬럼이름이나 연산식을 나열하거나 *
from 조회할 테이블이름을 기재하거나 나열
where 조회할 조건
group by 그룹화 할 컬럼이름이나 연산식
having 그룹화 한 이후의 조건
order by 정렬할 컬럼이름이나 연산식
수행 순서 from -> where -> group by -> having -> select -> order by 순으로 실행 됩니다 .
=> emp 테이블에서 job 별로 sal의 평균을 조회
select job, round(avg(sal))
from emp
group by job ;
=> group by를 사용할 때 주의할 점은 select 절에 그룹화 한 컬럼이름 또는 계산을 수행한 연산식을 제외한 컬럼을 기재하면 에러가 발생하거나 이상한 결과가
나올 수 있습니다.
-- emp 테이블에서 job 별로 sal의 평균을 조회
select job, round(avg(sal))
from emp
group by job ;
-- job 별로 그룹화하면 select 절에는 job과 집계함수를 이용하는 컬럼만 작성해야합니다.
-- 그 이외의 컬럼을 출력하려고 하면 에러가 발생
select job ename round(avg(sal))
from emp
group by job;
=> group by 이후의 조건
집계함수는 group by 이후에 사용할 수 있는 함수여서 where절에서 집계 함수를 이용한
조건은 작성할 수 없습니다.
집계함수를 이용한 조건은 having에 작성합니다.
--emp태이블에서 인원수 , 최대급여(sal), 최소급여 , 급여의 합을
--계산하여 출력하는 SELECT문자을 작성
select count(*)"인원수", max(sal)"최대급여", min(sal)"최소급여",sum(sal)"급여의 합"
from emp
--EMP 테이블에서 각 업무별(job)로 최대 급여(sal), 최소 급여, 급여의 합을 출력하는 SELECT 문장을 작성
--EMP 테이블에서 업무별(job) 인원수를 구하여 출력하는 SELECT 문장을 작성하여라.
--EMP 테이블에서 최고 급여(sal)와 최소 급여의 차이는 얼마인가 출력하는 SELECT 문장을 작성하여라.
**join - 2 개 이상의 테이블을 합쳐서 하나의 테이블을 만드는 것을 Join이라고 한다.
1. Cross Join - > Cartesian product
=> 2개의 테이블의 모든 데이터 조합
=> from 절에 테이블을 2개 이상 기재하면 Cross Join이 수행됩니다.
=> 앞에 기재된 테이블의 모든 데이터와 뒤에 기재된 테이블의 모든 데이터를 결합
=> 컬럼의 개수는 양쪽 테이블의 컬럼 개수를 합친것과 같고, 행의 개수는 양쪽 테이블의
행 개수를 곱한 것과 같습니다.
select *
from emp, dept ;
=위 처럼 작성하면 catesian product입니다.
emp 테이블은 14개의 행과 9개의 열로 이루어져있습니다.
(empno, ename, job, sal, comm, deptno, mgr, hiredate )
dept 테이블은 4개의 행과 3개의 열로 구성 되어있습니다.
(deptno, dname, loc)
2. join의 종류
1) cross join : catesian product, 2개 테이블의 모든 조합
2) equi join : 동일한 의미를 갖는 컬럼끼리 비교해서 일하는 경우에만 join하는 것
3) non-equi join: 일치하는 경우가 아닌 !=, >,>=.<=와 같은 연산자를 이용해서 비교한 후 join
4) outer join : 한쪽 테이블에만 존재하는 데이터도 join
5) self join : 하나의 테이블을 가지고 join
6) semi join : 서브쿼리를 가지고 join
3. equi join - where 절에 양쪽 테이블에서 동일한 의미를 갖는 컬럼의 값이 일치하는 경우에만 조회하도록 설정하면 됩니다.
=> emp 테이블과 dept 테이블은 deptno 라는 동일한 의미를 갖는 컬럼을 소유하고 있습니다.
=> emp 테이블과 dept 테이블을 join 할 때는 deptno의 값이 같은 경우메나 join 하도록 합니다.
ex1)-- emp 테이블과 dept 테이블을 equi join
=> 2개의 테이블에서 데이터를 조회할 때는 where절에 join 조건을 만들어 주는 것이 좋습니다.
select *
from emp, dept
where emp.deptno=dept.deptno;
ex2) emp 테이블의 ename과 dept 테이블의 dname과 loc를 조회 사원 이름과 부서이름과 지역을 조회
=> 조건이 있는 데이터를 조회할 때는 보통 조건은 Join 조건 뒤에 기술합니다.
select ename,loc
from emp, dept
where emp.deptno =dept.deptno
order by ename, loc
ex3 ) emp의 sal 값이 1500 이상인 데이터의 ename과 sal 그리고 deptno와 dname을 출력
=> join을 했을 때 양쪽 테이블에 공통으로 존재하는 컬럼을 출력하고자 하면 이 때
컬럼 이름 앞에 테이블이름. 을 추가해야 합니다
select ename sal, dept.deptno dname
from emp, dept
where emp.deptno = dept.deptno and sal >=1500;
4.non-equi join
=> 2개 테이블의 조인 조건이 =가 아닌 경우
=> emp 테이블에서는 sal이라는 급여 컬럼이 있습니다.
salgrade라는 테이블에는 급여 등급과 losal(급여의 최소값), hisal(급여의 최대값) 이라는 컬럼이 있습니다.
=>emp 테이블에서 ename과 sal을 조회한 후 sal의 salgreade에 있는 등급이 얼마인지 조회
select ename, sal, salgrade
from emp, salgrade
-- emp 테이블에서 ename과 sal을 조회한 후 sal의
--salgrade에 있는 등급이 얼마인지 조회
-- 중복2번 넣어서 distinct
select distinct ename, sal, grade
from emp, salgrade
where emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal;
5. Outer join
=> 한쪽 테이블에만 존재하는 데이터도 join에 참여하는 것
=> left outer join, right outer join, full outer join 이 있습니다.
=> 표준 SQL에서는 full outer join을 지원하지 않습니다.
=> 추가 시켜주고자 하는 join 조건에 (+) 를 기재 하면 됩니다.
--emp 테이블의 demptno를 확인
-- 10, 20, 30
select distinct deptno
from emp;
--dept 테이블의 deptno를 확인
--10,20,30,40
select deptno
from dept
group by deptno
--equi join 을 하게되면 dept테이블에만 있는 40번은 제외 됩니다.
select *
from emp, dept
where emp.demptno = dept.deptno;
--40번추가
--dept 테이블에만 존재하는 40을 추가해서 join
--대신 없는 값은 전부 null로 채워집니다.
-- 양쪽에 (+)를 붙일 수 없습니다.
select *
from emp, dept
where emp.deptno(+) = dept.deptno;
6. selfjoin
=> 자신의 테이블과 join
=> 하나의 테이블에 동일한 의미를 갖는 컬럼이 2개 이상 존재하는 경우에 사용하는 join입니다.
=> emp 테이블에는 empno라고 하는 자신의 사원번호 컬럼이 있고 mgr이라고 하는 자신의 상관 사원번호 컬럼이 있습니다.
=> selfjoin은 from절에서 자신의 테이블이름을 2번 기재해야 하기 때문에 구별하기 위해서 테이블 이름에 별명을 부여해서 작업합니다.
=> 테이블 이름에 별명을 부여할 때는 테이블 이름 다음에 공백을 추가하고 별명을 기재하면 됩니다.
=> 테이블 이름이 길 때도 별명을 붙여서 짧게 사용하는 경우가 있습니다.
=> emp 테이블의 자신의 이름(ename) 과 자신의 상관이름을 같이 조회
--emp 테이블에서 사원이름과 관리자의 이름을 같이 출력
--emp 테이블의 mgr 컬럼이 관리자의 사원번호(empno)입니다.
--
select s1.ename 사원이름, e2.ename 관리자 이름
-- 테이블 이름 두개적기
-- e1에가서 뭘 가져와
from emp e1, emp e2
where e1.mgr=e2.empno
**ANSI join
ANSI ( 미국 표준 협회 ) 에서 재정한 Join 문법
=> 관계형 데이터 베이스 종류에 따라 지원하는 경우도 있고 그렇지 않은 경우도 있습니다.
1. cross join - cartesian product - 2개 테이블의 모든 조합이 전부 조회되는 것
=> from 절에
테이블 이름 cross join 테이블 이름
--emp 테이블과 dept 테이블의 cross join
-- 양쪽 테이블의 행의 개수를 곱한 결과인 56개의 행이 출력되고
-- 양쪽 테이블의 컬럼의 개수를 더한 결과인 11개의 커럶이 출력됩니다.
select *
from emp cross join detp
2. inner join
=> from 절에
2개 테이블을 나열 할 때 , 대신에 inner join 이라는 예약어 추가하고 on절에 join조건을 추가합니다.
select
from 테이블 이름 inner join 테이블이름2
on 테이블이름1.컬럼이름 = 테이블이름2.컬럼이름
-- ex)emp 테이블에 ename에 L이 2자 이상 포함 되어 있는 사원의 ename과 dept 테이블의 dname을 조회
=> 2개 테이블의 공통 컬럼 :deptno
3.using 과 natural join
=> join 하고자 하는 컬럼의 이름이 동일 할 때 사용할 수 있는 Join
=> 컬럼의 이름이 동일할 때는 on 대신에 using이라고 적고 공통 컬럼이름을 기재하면 됩니다.
=> 컬럼의 이름이 같을 때는 inner join이라는 예약어 대신에 natural join 이라고 적고 join조건을 생략해도 됩니다.
--
select ename, dname
from emp inner join dept
using (deptno)
where ename like '%L%L%';
--
select ename, dname
from emp natural dept
where ename like '%L%L%';
6.조인.ppt