select (sysdate)
from dual;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--오늘날짜 조회
select sysdate, systimestamp
from dual;
select add_months(sysdate, -6), add_months(sysdate, 18)
from dual;
select months_between(sysdate, add_months(sysdate, -6))
from dual;
select last_day(sysdate)
from dual;
--입사한 달의 근무일수(토, 일, 공휴일에도 근무가정)
--날짜 -날짜의 데이터 타입은 숫자
select ename, hiredate, last_day(hiredate), last_day(hiredate)-hiredate
from emp
where deptno=10
order by hiredate asc ;
--비교 : 날짜데이터대상의 trunc(날짜데이터, FMT), round(날짜데이터, FMT)
--trunc함수는 FMT에 지정된 단위로 절삭
--round함수는 FMT가 년도라면 년도단위로 반온림
--FMT가 생략되면 날짜를 가장 가까운 날짜로 반올림
--FMT: YEAR,MONTH,DD 등=>P141 날짜변환형식 참고)
select trunc(123.456), trunc(456.78) from dual;
select round(123.456, -2), round(123.456, -1),
round(123.456, 0), round(123.456, 1)
from dual;
--trunc 와 lastday는 반대 개념
select sysdate, trunc(sysdate, 'month'), trunc(sysdate, 'day'),
trunc(systimestamp, 'month'), trunc(systimestamp, 'day'),
to_char(trunc(systimestamp, 'month'), 'YYYY.MM.DD / HH24:MI:SS'), to_char(trunc(systimestamp, 'day'), 'YYYY.MM.DD / HH24:MI:SS')
from dual;
select sysdate, round(sysdate, 'month'), round(sysdate, 'day'),
round(systimestamp, 'month'), round(systimestamp, 'day'),
to_char(round(systimestamp, 'month'), 'YYYY.MM.DD / HH24:MI:SS'), to_char(round(systimestamp, 'day'), 'YYYY.MM.DD / HH24:MI:SS')
from dual;
-- * TABLE삭제 => auto commit되므로 주의.
문법> Drop table 테이블명;
--scott의 bonus, emp, dept, salgrade 제외하고 전부 삭제
Drop table ctest;
Drop table dtest;
Drop table emp1;
Drop table emp10;
Drop table emp2;
Drop table emp20;
Drop table empetc;
Drop table istest;
Drop table ntest;
Drop table test;
p176
--join
select 컬럼명 [AS] 별칭, 컬럼명 AS 별칭
from 테이블명 별칭, 테이블명 별칭
[where 조건];
--사원번호, 사원명, 업무, 부서번호 조회 (부서 번호로 오름차순 정렬)
select empno, ename, job, deptno
from emp
order by deptno asc;
--부서번호, 부서명 조회 (부서 번호로 오름차순 정렬)
select deptno, dname
from dept
order by deptno asc;
--deptno 양쪽 테이블 모두에 있어서 애매한 상황
-- 1 select empno, ename, job, deptno, dname
-- 2* from emp, dept
--SQL> /
--select empno, ename, job, deptno, dname;
-- *
/*
ERROR at line 1:
ORA-00918: column ambiguously defined
*/
--deptno를 지워버리면 결과는 나온다.그런데 ... 정확하지 않다...크로스조인 되어 데이터가 이상하다.
--CROSS JOIN, CATASIAN PRODUCT(p184)
--join조건을 명시하지 않는 조인
select empno, ename, job, dname
from emp, dept; --사원 .12개행*부서.5개의행 => 60개의 행 이것이 크로스 조인
--equi join(동등조인 p176)
--where절에 =(동등기호)를 이용하여 join 조건을 명시
--where 사원 테이블의 부서번호 부서테이블의 부서번호 컬럼의 값이 같다.
--이 구절을 써줘야 제대로 출력된다.
select empno, ename, job, dname
from emp, dept
where emp.deptno = dept.deptno; --12개의 행이 리턴.
--equi조인은 조인조건에 사용되는 값들이 서로 일치=하는 데이터만 select
--즉 여기에서는 사원테이블에 부서번호가 null인 사원은 select 되지 않는다.
--즉 소속부서가 확정되지 않은 사원은 제회 => 소속부서가 명확한 사원만 select
select empno, ename, job, dname
from emp, dept
where emp.deptno = dept.deptno
and
emp.deptno is not null;
--사원번호가 9000이상인사원 삭제후 commit;
delete from emp
where empno like '9%';
--where empno>=9000;
commit;
--부서테이블에서 부서번호가 91이상인 부서삭제후 commit;
delete from dept
where deptno>=91;
commit;
/*
select 테이블명.empno, 테이블명.ename, 테이블명.job,
테이블명.부서번호, 테이블명.dname
from emp, dept
where emp.deptno = dept.deptno;
*/
--에러 : column ambiguously defined 발생
--해결방법 : 조회하는 컬럼의 소속테이블명을 명시하면 된다.
select emp.empno, emp.ename, emp.job,
dept.deptno, dept.dname
from emp, dept;
where emp.deptno = dept.deptno;
--테이블에 별칭을 부여하여 쿼리는 간단, 소속분명, 이해도 향상
select e.empno, e.ename, e.job,
d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno;
*p198 인라인뷰(inline view)
-->오랫동안 또는 영구적으로 사용할거면 뷰를 생성하면 되고 일시적으로 처리 할 거면 인라인 뷰형태인 서브쿼리로 쓰면 된다.
--from절에 서브쿼리(subquery) 가 들어가면 inline view
--select e.empno, e.ename, e.job, 테이블명.컬럼명, 별칭.컬럼명
-- d.deptno, d.dname
--from emp e, dept d (select 컬럼명, 컬럼명 from 테이블명) 별칭
--where e.deptno = d.deptno;
-- 10번 또는 20번 부서에 근무하는 사원번호, 사우너명, 급여, 부서번호, 부서명을
-- 부서명 오름차순 정렬하여 조회
select e.empno, e.ename, e.sal, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno and d.deptno in (10, 20) --조인문 조건 써줘야 한다 ! 무조건!! 카타시안 프로덕트 및 크로스조인 방지!
order by d.dname asc;
--where (e.deptno=10 or e.deptno=20) and e.deptno = d.deptno --> 이렇게 써도 무관. 문법은 무궁무진
/*
급여가 1600이상인 사원번호, 사원명, 급여,부서번호, 부서명을
급여를 많이 받는 사원부터 출력 -->내림차순
이때, 급여가 확정된 사원에 한함
*/
select e.empno, e.ename, e.sal, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno and e.sal>=1600 and e.sal is not null
order by e.sal desc;
--사번, 사원명, 부서번호, 부서명을 부서번호, 오름차순
select e.empno, e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
order by d.deptno asc;
--natural join
--문법> from 테이블명1 natural join 테이블명2
--주의> natural join에 사용된 컬럼의 소속테이블은 명시하면 안된다.
--해결방법> 컬럼명만 명시하자
--예시> d.deptno가 아닌 deptno;
--예시> order by d.deptno가 아닌 order by deptno;
--공통된 컬럼에 대해서 소속을 넣으면 안된다.
select e.empno, e.ename, deptno, d.dname
from emp e natural join dept d
order by deptno;
--join using()
--문법> from 테이블명1 join 테이블명2 using(join사용컬럼)
--column part of USING clause cannot have qualifier
--주의> join에 사용된 컬럼의 소속테이블은 명시하면 안된다.
--해결방법> 컬럼명만 명시하자
--예시> d.deptno가 아닌 deptno;
--예시> order by d.deptno가 아닌 order by deptno;
--이 것 또한 사용된 컬럼의 소속테이블은 명시하면 안된다.
select e.empno, e.ename, deptno, d.dname
from emp e join dept d using(deptno)
order by deptno;
--join on
--column ambiguously defined
-->얘는 사용 컬럼 명시를 따로 안하기 때문에 소속을 적어줘야하는듯?e.d.다 된다.
-- order by 절은 소속 명시 안해도 된다.
-- 주의. 테이블명(소속) 명시하자!
select e.empno, e.ename, d.deptno, d.dname
from emp e join dept d on e.deptno = d.deptno
order by d.deptno;
--ANSI 조인(p184)
--join on(p184)
--문법>테이블명 [INNER] JOIN 테이블명 ON 조인조건
--column ambiguously defined
-- 주의. 테이블명(소속) 명시하자!
select e.empno, e.ename, d.deptno, d.dname
from emp e INNER join dept d on e.deptno = d.deptno
order by d.deptno;
--9000, 이순신, DBA, 오늘입사, 급여 7000, 부서 미확정
insert into emp(empno, ename, job, hiredate, sal)
values(9000, '이순신', 'DBA', sysdate, 7000);
commit;
--where절 추가!!(+)왼쪽
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno; -->부서쪽이 다 출력된다.부서번호가 다 나와야 하니까 나머지 컬럼에 널을 추가해가면서까지 출력된다.
--안시외부조인
--where e.deptno(+) = d.deptno; 과 동일한 결과 (외부조인!!OUTER 생략가능)
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e RIGHT OUTER join dept d on e.deptno = d.deptno; --> 오른쪽에 있는 테이블이 다 출력 (위와 동일)
--where절 추가!!(+)오른쪽
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno(+); -->사원쪽이 다 출력된다.사원쪽정보가 다 나와야 하니까 부서쪽 나머지 컬럼에 널을 추가해가면서까지 출력된다
--안시외부조인
--where e.deptno = d.deptno(+); 과 동일한 결과 (외부조인!!OUTER 생략가능)
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e LEFT OUTER join dept d on e.deptno = d.deptno; --> 왼쪽쪽에 있는 테이블이 다 출력 (위와 동일).
--에러발생
--a predicate may reference only one outer-joined table
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno(+); -->+는 아우터 조인인데 이것은 오직 한쪽에만 조인 가능(+)쓰는 문법은 무조건 이렇게.
--안시 아우터 조인만 가능.
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e FULL OUTER join dept d on e.deptno = d.deptno; --> 양쪽 다 + 하는것은 FULL로 명시해야한다.여전히 OUTER 생략가능
-- NON-EQUI JOIN 조인
--사번, 사원명, 급여, 급여등급 조회
select e.empno, e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal BETWEEN s.losal AND s.hisal;
--where e.sal >= s.losal AND e.sal <= s.hisal; --같은결과
--self join(p180)
--동일한 테이블을 사용해 조인하는 방법
select e1.empno, e1.ename, e1.mgr, e2.empno, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;
--이것도 된다.
--select e1.empno, e1.ename, e1.mgr, e2.empno, e2.ename
--from emp e1 join emp e2
--on e1.mgr = e2.empno;
/*
subquery(서브쿼리 p191)
:서브쿼리란 SQL문장안에서 보조로 사용되는 또 다른 select문을 의미
*/
/
select 컬럼명1,...컬럼명N [AS alias]
from 테이블명 별칭, 뷰명,
~
[where 조건]
[group by 그룹기준]
[having 그룹기준 조건]
[order by 정렬기준 방법]
*/
--20번 부서에 근무하는 사번, 사원명, 급여, 부서번호 조회
select empno, ename, sal, deptno
from emp
where deptno = 20;
--SMITH가 근무하는 부서번호는?
select ename, deptno
from emp
where ename = 'SMITH';
--SMITH와 동일부서에 근무하는 부서번호, 사번, 사원조회(서브쿼리 사용)
select deptno, empno, ename
from emp
--where ename = 'SMITH' or deptno = 20; -->이건 결과는 같은데 ... 서브쿼리 사용 의도에 맞지 않는 구문
where deptno = (select deptno from emp where ename = 'SMITH');
--FORD와 같은 일을 하는 사번, 사원명, 업무 조회-> 1명밖에 없다.
select empno, ename, job
from emp
where job = (select job from emp where ename = 'FORD');
--TURNER와 같은 일을 하는 사번, 사원명, 업무 조회
select empno, ename, job
from emp
where job = (select job from emp where ename = 'TURNER');
--TURNER가 받는 급여보다 많이 받는 사번, 사원명, 급여 조회
--> 해석하면 1500보다 많이 받는 사람 조회
select empno, ename, sal
from emp
where sal > (select sal from emp where ename = 'TURNER')
order by sal asc;
--전체사원들의 평균급여 이상을 받는 사번, 사원명, 업무 조회
--사번, 사원명, 업무조회
--1. 전체사원들의 평균급여를 조회
select avg(sal)
from emp;
select empno, ename, job, sal
from emp
where sal >= (select avg(sal) from emp);
--TURNER와 같은 일을 하는 사원들의 평균급여 이상을 받는 사번, 사원명, 업무 조회
--TURNER가 하는일 조회
--사원명이 TURNER인 업무 조회
select job
from emp
where ename='TURNER';
--TURNER와 같은일을 하는 사원들의 평균급여
-- =>job이 salesman 사원들의 평균급여
select avg(sal)
from emp
where job = 'SALESMAN'; --1400
--사원들의 평균급여 조회
select empno, ename, job, sal
from emp
where 급여가 (평균급여)이상
where 급여가 (1400) 이상
where sal>=(select AVG(sal)
from emp
where job ='SALESMAN');
select empno, ename, job, sal
from emp
where sal >= (select avg(sal) from emp where job = (select job from emp where ename = 'TURNER'))
order by sal asc;
--위의 결과에 부서명을 추가하여 출력하시오
select e.empno, e.ename, e.job, e.sal, d.dname
from emp e, dept d
where sal >= (select avg(sal) from emp where job = (select job from emp where ename = 'TURNER')) and e.deptno = d.deptno
order by sal asc;
select e.empno, e.ename, e.job, e.sal, d.dname
from emp e, dept d
where sal >= (select avg(sal) from emp e1 where e1.job = (select e2.job from emp e2 where e2.ename = 'TURNER')) and e.deptno = d.deptno
order by sal asc;
select e.empno, e.ename, e.job, e.sal, d.dname
from emp e, dept d
where sal >= (select avg(sal) from emp where job = (select job from emp where ename = 'TURNER')) and e.deptno = d.deptno(+)
order by sal asc;
select e.empno, e.ename, e.job, e.sal, d.dname
from emp e LEFT OUTER join dept d on e.deptno = d.deptno
where sal >= (select avg(sal) from emp where job = (select job from emp where ename = 'TURNER'))
order by sal asc;
-- new york에 근무하는 부서번호, 부서명, 소재지, 사번, 사원명,급여 조회
-- 급여를 많이 받는 사원부터 출력
--INNER 생략 가능
--INNER join on
select d.deptno, d.dname, d.loc, e.empno, e.ename, e.sal
from dept d INNER join emp e on d.deptno = e.deptno
where loc = 'NEW YORK'
order by sal desc;
--INNER join using
--deptno 소속 명시 금지
select deptno, d.dname, d.loc, e.empno, e.ename, e.sal
from dept d INNER join emp e using(deptno)
where loc = 'NEW YORK'
order by sal desc;
--natural join
--deptno 소속 명시 금지
select deptno, d.dname, d.loc, e.empno, e.ename, e.sal
from dept d natural join emp e
where loc = 'NEW YORK'
order by sal desc;
--일반 조인!!
select d.deptno, d.dname, d.loc, e.empno, e.ename, e.sal
from dept d, emp e
--where loc = 'NEW YORK' AND d.deptno = e.deptno
where loc = UPPER('new york') AND d.deptno = e.deptno
order by sal desc;
--allen이 근무하는 부서의 인원수보다 많은 사원들이 근무하는 부서명, 근무하는 사원수를 구하시오.
select d.dname
from dept d;
--not a single-group group function --->count 함수 쓰려면 그룹바이 절을 써야한다.그리고 조건절 왼쪽에 카운트나 avg같은 함수는 그룹바이 다음 having절에 써야한다.
select d.dname, count(e1.deptno)
from emp e1, dept d
where e1.deptno = (select deptno from emp e where e.ename='ALLEN');
--ssj
select d.dname, count(e.deptno)
from emp e join dept d on d.deptno = e.deptno
group by d.dname
having count(e.deptno) > (select count(deptno)
from emp
where deptno =
(select deptno from emp where ename = 'ALLEN'));
--jjb
select e.deptno, d.dname, count(e.deptno)
from emp e join dept d on e.deptno=d.deptno
where e.deptno is not null
group by e.deptno, d.dname
having count(e.deptno)>=(select count(deptno)
from emp
where deptno=(select deptno
from emp
where ename=upper('allen')))
order by deptno;
--p177~180, p194
*다중행 서브쿼리 => 서브쿼리의 결과로 리턴받은 rows수가 여러개일때
연산자(서브쿼리) : IN, ANY, ALL, EXISTS
sal=2450 or sal=5000 or sal=1300 => sal in(2450, 5000, 1300) OR
select empno, ename, sal, deptno
from emp
where sal in(select sal from emp where deptno=10) --or연산 이지만 부등호를 쓸 수 없다.
order by sal desc;
select empno, ename, sal, deptno
from emp
where sal >=any(select sal from emp where deptno=10) --or연산
order by sal desc;
select empno, ename, sal, deptno
from emp
where sal >=all(select sal from emp where deptno=10) --and연산
order by sal desc;
where 컬럼명 >=(서브쿼리);
where sal>=all(2450, 5000, 1300); AND sal>=5000
where sal>=any(2450, 5000, 1300); OR sal>=1300
where sal>=(2450, 5000, 1300);
where sal>=(select sal from emp where deptno=10);
*EXISTS (p194)
--=>서브쿼리의 실행결과가 존재하면 메인쿼리 실행
--=>서브쿼리의 실행결과가 존재하지않으면 메인쿼리 실행결과 없다.
select empno, ename
from emp
where exists(select deptno from dept where dname='SALES'); -->where문 무시~ 있는 값이면 다 보여준다.
select empno, ename
from emp
where exists(select deptno from dept where dname='SALES123'); --> where문 무시~ 없는 값이면 아예 안보여준다.
/*
view 객체(p.73)
view : 하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터베이스 객체이다.
*View 생성
CREATE [OR REPLACE] 객체타입 객체명
CREATE [OR REPLACE] View View명
AS
Subquery;
*View 삭제
DROP View View명
*/
--사번, 사원명, 부서명 조회 --> 권한 없다고 하면 system 계정으로 접속해서 grant create view to scott; commit;
CREATE View emp_dept
AS
select e.empno, e.ename, d.dname
from emp e join dept d
on e.deptno = d.deptno;
CREATE or REPLACE View emp_dept
AS
select e.empno, e.ename, e.sal, d.dname
from emp e join dept d
on e.deptno = d.deptno
where e.sal>=2450;
drop View emp_dept;
select * from emp_dept;
--수정
--7698사원의 급여를 3000으로 수정 (2850 -> 3000)
/*
UPDATE 뷰명
set 컬럼명=새값
where 테이블명
*/
UPDATE emp_dept set sal = 3000 where empno=7698;
select * from emp_dept where empno=7698;
select * from emp where empno=7698; --> 뷰를 수정해도 원본 테이블 데이터도 수정된다.
--원본 EMP 테이블의 7698사원의 급여를 다시 2850으로 수정 (3000->2850)-->원본이 수정되면 자동으로 뷰도 수정된다.
update emp set sal = 2850 where empno = 7698;
--뷰에서 7698사원의 급여를 100으로 수정
update emp_dept set sal=100 where empno=7698;
--원본 emp에서 확인
select * from emp where empno=7698; -->100으로 수정하면 원본 테이블 데이터 수정 된다.
--뷰 emp_dept에서 확인 --> 얘는 100으로 수정된 것이 안보인다애초에 뷰를 생성할때 서브쿼리 조건이 2450이상이기 때문에 안보이는것.
select * from emp_dept;
--생성된 뷰 구조 확인
desc user_views;
select view_name, text, read_only
from user_views;
--삭제
--delete from emp_dept where empno=7698;
--system 계정 접속
conn system/asdf123
--계정정보 및 PW 및 계정잠금상태 변경
문법>ALTER USER 유저명
IDENTIFIED BY 비번
ACCOUNT UNLOCK | LOCK ;
--hr계정 pw hr, 잠금 풀기
ALTER USER hr
IDENTIFIED BY hr
ACCOUNT UNLOCK;
*p198 인라인뷰(inline view)
-->오랫동안 또는 영구적으로 사용할거면 뷰를 생성하면 되고 일시적으로 처리 할 거면 인라인 뷰형태인 서브쿼리로 쓰면 된다.
--from절에 서브쿼리(subquery) 가 들어가면 inline view
--부서별, 부서명, 평균급여, 총급여를 부서번호 오름차순 정렬하여 출력
--이때 평균급여는 소수점 첫째자리에서 반올림
--내가 한 것.
select d.deptno, d.dname, sum(e.sal), round(avg(e.sal),0) -->이경우 e.없어도 실행된다.
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno, d.dname
order by deptno asc;
--답안-->인라인뷰 이용한것이 맞음.인라인 뷰테이블 자체를e로 지정!
select d.dname, e.avg_sal, e.sum_sal
from dept d,
(select deptno, round(avg(sal), 0) avg_sal, sum(sal) sum_sal
from emp
where deptno is not null
group by deptno) e
where e.deptno = d.deptno
order by d.deptno asc;
--join on으로 변경 결과는 위와 같다.
select d.dname, e.avg_sal, e.sum_sal
from dept d join
(select deptno, round(avg(sal), 0) avg_sal, sum(sal) sum_sal
from emp
where deptno is not null
group by deptno) e on e.deptno = d.deptno
order by d.deptno asc;
--*SEQUENCE(시퀀스)
/*
SEQUENCE(시퀀스)(p.83) : 자동순번을 반환하는 데이터베이스 객체
사용 예) 사원번호, 부서번호, 회원번호, 주문번호, 대여번호, 예약번호, 글번호
/
--시퀀스 조회
select *
from user_sequences;
/*
- 생성
CREATE SEQUENCE 시퀀스명
[START WITH 시작번호] --시작번호(수정불가)
[INCREMENT BY 값] --증감규칙
[MINVALUE 최저값]
[MAXVALUE 최고값]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE];
*수정
ALTER SEQUENCE [스키마명.] 시퀀스명
increment by 값 --증값규칙
minvalue 값 --시작번호
maxvalue 값 --끝번호
--*수정
ALTER SEQUENCE 스키마명.시퀀스명
--[START WITH 시작번호] --시작번호(수정불가)
[INCREMENT BY 값] --증감규칙
[MINVALUE 최저값]
[MAXVALUE 최고값]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE];
- 삭제
문법>DROP SEQUENCE 시퀀스명;
DROP SEQUENCE dept_deptno_seq;
SELECT * FROM USER_SEQUENCES; - /
- /
--부서dept테이블의 deptno컬럼의 값으로 사용하고자하는 시퀀스생성
--71부터 81까지 1씩 증가
CREATE SEQUENCE scott.dept_deptno_seq
START WITH 71 --시작번호 (수정불가
increment by 1 --증감규칙
minvalue 71 --시작번호
maxvalue 81; --끝번호
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept1'); --71
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept2'); --72
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept3'); --73
select * from dept;
--사원번호(시퀀스 이용), 'emp1' 입력
insert into emp(empno, ename)
values(dept_deptno_seq.nextval,'emp1'); --74가 들어간다. 위에 73까지 들어갔으니...
insert into emp(empno, ename)
values(dept_deptno_seq.nextval,'emp2'); --75
select * from emp;
--부서의 부서번호 INSERT ? 몇 ?
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept4'); --76
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept5'); --77
select * from dept;
select dept_deptno_seq.nextval from dual; --78
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept6'); --79
rollback;
select * from dept; -- 71, 72, 73, 76, 77, 79
select * from emp; -- 74, 75,
--롤백 후에도 그 다음 숫자부터 이어서 입력 된다.
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept7'); --80
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept8'); --81
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept9'); --오류발생
--sequence DEPT_DEPTNO_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
--시퀀스를 다시 만들어서 새로 써도 되지만... 수정하는 방식으로 !!
--71부터 89까지 2씩 증가!
--cannot alter starting sequence number
--시작번호는 수정불가하다
ALTER SEQUENCE scott.dept_deptno_seq
--START WITH 71--시작번호 (수정불가)
increment by 2 --증감규칙
minvalue 1 --시작번호
maxvalue 89; --끝번호
--81에서 2씩 증가
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'AFTER--'); --83
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept10'); --85
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept11'); --87
--*삭제
DROP SEQUENCE scott.dept_deptno_seq;
select * from USER_SEQUENCES;
delete from dept where loc is null;
delete from dept where not in(10, 20, 30, 40, 90);
select * from emp;
select * from dept;
commit;
--emp의 식별키 empno에 사용될 시퀀스 생성
CREATE SEQUENCE scott.emp_empno_seq
START WITH 9100
INCREMENT BY 1 ;
select * from user_sequences;
--시퀀스명.NEXTVAL
--시퀀스명.CURRVAL
--위에서 생성한 시퀀스를 이용하여 emp에 데이터 입력
insert into emp(empno, ename, job, hiredate, sal, comm)
values(emp_empno_seq.nextval, '조규성', '개발', sysdate, 5000, 2500);
select * from emp
order by empno desc;
rollback;
--자바로 넣은 데이터는 오토커밋된다. 롤백해도 그대로
--테이블 변경, 삭제, 추가 등의 작업은 상당히 주의해야한다.
=> 모델링을 잘 해야한다. 모델링의 중요!!!
--복사생성
create table 테이블명
AS
SUBQUERY;(select문)
--*생성
create table 테이블명(
컬럼명 데이터타입(크기),
컬럼명 데이터타입(크기) default 기본값,
컬럼명 데이터타입(크기) [constraint 제약조건명] 제약조건
);
--*수정
alter table 테이블명
-추가 ADD
-변경 MODIFY
-삭제 DROP
--*삭제
drop table 테이블명;
--*제약조건 조회
select *
from user_constraints;
--*제약조건 추가
--*제약조건 삭제
--@emp table 복사생성
--부서번호 10번인 사원들의 사원번호, 사우너명, 업무, 입사일, 부서번호 출력(입사일 기준으로 오름차순)
--데이터 타입, 크기, 데이터복사
create table emp10
as
select empno, ename, job, hiredate, deptno
from emp
where deptno = 10
order by hiredate asc;
--*수정
--emp10테이블에 etc 숫자(10) 컬럼 추가
--추가 ADD
문법>alter table 테이블명
ADD 컬럼명 데이터 타입(크기);
--컬럼추가
alter table emp10
ADD etc number(10);
select * from emp10;
--변경 MODIFY (p.68)
문법>alter table 테이블명
MODIFY 컬럼명 데이터 타입(크기);
--앞에서 추가한 etc컬럼의 데이터타입과 크기를
--varchar2(100)으로 변경
--주의.컬럼의 데이터타입, 크기변경시에는 데이터가 있는지 확인해야한다.
--이미 데이터가 들어가 있는데 타입을 변경하면 안된다.
--예) 날짜데이터가 들어가 있는데 숫자타입으로 바꾸면 안된다는 뜻.
alter table emp10
modify etc varchar2(100);
select * from emp10;
--삭제 DROP
문법>alter table 테이블명
DROP column 컬럼명; or DROP constraint 제약조건명;
--앞에서 추가한 etc컬럼삭제
alter table emp10
drop column etc;
select * from emp10;
--테이블 복사 생성
create table empsales
as
select empno no, ename name, job
from emp
where job='SALESMAN';
desc empsales; -->컬럼명 alias로 출력 된다.
--empsales삭제
drop table empsales;
--테이블 복사 생성시 조건이 false 이면 데이터 복사X 구조만 가져온다.(조건문 이상할때)
create table empsales
as
select empno no, ename name, job
from emp
where 1=2;
select * from empsales;
--*emp테이블을 복사하여 emp2생성(테이블 구조만 복사, 데이터복사 X)
--사원번호의 컬럼명은 eno
--ename
--job
--mgr컬럼명은 mgrid
--hiredate
--sal
create table emp2
as
select empno eno, ename, job, mgr mgrid, hiredate, sal
from emp
where 1=2;
desc emp2;
select * from emp2;
--*제약조건(constraint)
-- 제약조회
SELECT constraint_name, constraint_type, table_name
FROM USER_CONSTRAINTS;
-- 제약조건 종류
1)not null : null허용x=>값을 넣어라. 필수입력
2)unique : 유일한 값=>중복허용x
3)check : 조건만족하는지 체크. check(조건)
4)Primary key:pk.식별키. nn+unique이면서 테이블대표, 각 레코드를 구분
-- 주의 : 컬럼타입(문자&날짜는 '')
컬럼크기(숫자&영문 1글자는 1byte,
여기에서는 한글1글자는 3byte(한글은 encoding설정에 따라 달라짐))
5)Foreign key : FK.외래키
PK(혹은 UK)컬럼의 값을 참조하는 키(references key)
--*emp테이블을 복사하여 emp2생성(테이블 구조만 복사, 데이터복사 X)
--사원번호의 컬럼명은 eno => pk
--ename ==> nn
--job
--mgr컬럼명은 mgrid => uk
--hiredate
--sal => check sal between 700 and 9999
참고문법>
alter table 테이블명 ADD 컬럼명 데이터타입(크기);--컬럼추가
*수정
--테이블의 제약조건 추가(변경)-p71
--문법>
ALTER table 테이블명
ADD [constraint 제약조건명] 제약조건(컬럼명);
--emp2테이블의 eno에 PK 제약조건 추가(변경)-p71
--문법>
ALTER table emp2
ADD constraint pk_emp2_eno primary key(eno);
--emp2테이블의 mgrid에 UK 제약조건 추가
--문법>
ALTER table emp2
ADD constraint uk_emp2_eno unique(mgrid);
--emp2테이블의 sal에 check 제약조건 추가
--check sal between 700 and 9999
--문법>
ALTER table emp2
ADD constraint ck_emp2_sal check(sal between 700 and 9999);
--emp2테이블의 ename에 nn 제약조건 변경
--문법> add가 아니라 modify로 해야한다.--컬럼명이 앞에 들어간다.modify문법이라서 이미 있는 컬럼이라서 데이터타입과 값은 입력하지 않는다.
ALTER table emp2
MODIFY ename constraint nn_emp2_ename not null;
--emp2테이블의 hiredate에 기본값 오늘
--테이블 특정컬럼에 기본값 적용(아래에는 이형식 사용)
--문법>
ALTER table 테이블명
MODIFY 컬럼명 default 값;
--문법> add가 아니라 modify로 해야한다
ALTER table emp2
MODIFY hiredate default SYSDATE;
--제약조건 있는 테이블에 DML
select * from emp2;
--*****입력
1, NULL, NULL, NULL, NULL, NULL
insert into emp2
--values(1, NULL, NULL, NULL, NULL, NULL);
--cannot insert NULL into ("SCOTT"."EMP2"."ENAME") ename이 not null이기 때문에 에러발생
values(1, '홍길동', NULL, NULL, NULL, NULL);
select * from emp2;
--eno컬럼의 값을 null입력
insert into emp2
--values(null, '홍길동', NULL, NULL, NULL, NULL); --PK는 NN이므로 오류발생
--cannot insert NULL into ("SCOTT"."EMP2"."ENO")
values(1, '홍길동', NULL, NULL, NULL, NULL); --PK는 유일한 값이어야 하는데
--unique constraint (SCOTT.PK_EMP2_ENO) violated
select * from emp2;
-- 2, 이순신, mgrid는 1입력 이순신이 상사
insert into emp2(eno, ename, mgrid)
values(2, '이순신', 1);
-- 기본값은 묵시적 NULL입력시 들어간다. hiredate는 오늘날짜가 입력된다.
select * from emp2;
delete from emp2 where ename='이순신';
delete from emp2 where ename='강감찬'; -->그냥 내가 필요해서 넣은것
--3, '강감찬', 1입력
insert into emp2(eno, ename, mgrid)
values(3, '강감찬', 1); --MGROD는 unique한 값을 넣어야 하므로 오류발생
--unique constraint (SCOTT.UK_EMP2_ENO) violated
--기본값을 명시적으로 NULL해보자
--3, '강감찬', 2입력
insert into emp2
values(3, '강감찬', null, 2, null, null);
select * from emp2;
--4, 김구, 3, 입사일 기본값 적용, 급여는 700
insert into emp2(eno, ename, mgrid, sal)
values(4, '김구', 3, 700);
select * from emp2;
--*****수정
update 테이블명
set 컬럼명=새값, 컬럼명=새값
where 조건
---강감찬의 급여를 7770으로 수정
update emp2
set sal=7770
where ename = '강감찬';
select * from emp2;
---이순신의 급여를 9999으로 수정
update emp2
set sal=9999
where ename = '이순신';
select * from emp2;
---홍길동의 급여를 119999으로 수정
UPDATE emp2
SET sal = 119999
where ename = '홍길동'; --급여는 700~9999사이어야만 한다. 그래서 오류가 난다.
--value larger than specified precision allowed for this column
select * from emp2;
--emp2테이블에 deptno number(2)컬럼을 추가
--foreign key
--fk인 컬럼이 참조한다. 부모테이블의( PK인) 컬럼을
--(fk)인 컬럼이 참조한다. 부모테이블의( PK인)컬럼명
--(fk)인 컬럼명 references 부모테이블의( PK인)컬럼명
--emp2테이블에 deptno number(2)컬럼을 추가
alter table emp2
add deptno number(2);
desc emp2;
select * from emp2;
--기존테이블에 fk제약조건 추가
--문법>
alter table 테이블명
add constraint 제약조건명
제약조건(컬럼명) references 부모테이블의( PK인)컬럼;
--emp2에 fk제약조건 추가
alter table emp2
add constraint fk_emp2_deptno
foreign key(deptno) references dept(deptno);
select * from emp2;
--현재기준 dept.deptno(부모)컬럼의 값에는 10, 20, 30, 40, 90 존재
--fk는 null허용. 중복허용
--2번, 이순신의 부서번호를 90으로 변경
update emp2
set deptno=90
where eno=2 and ename='이순신';
--3번, 강감찬의 부서번호를 90으로 변경
update emp2
set deptno=90
where eno=3 and ename='강감찬'; -->중복허용!!
--4번, 김구의 부서번호를 99으로 변경
update emp2
set deptno=99
where eno=4 and ename='김구'; -->99부서는 존재하지 않는 부서이기 때문에 안된다!!!--참조하는 컬럼에 존재하는 값만 가능!!
--integrity constraint (SCOTT.FK_EMP2_DEPTNO) violated - parent key not found -->무결성 제약에 위반!!
select * from emp2;
--부모와 자식관계 pk와 fk인 컬럼에서 입력, 수정, 삭제
부서(dept.deptno) 사원(emp2.deptno)입력 [※항상가능※] [※부분가능※]
부모 테이블인 dept에서 입력을 하는것은 언제든지 가능!!! 존재하는 부모키에 한해 가능
(부모한테 없는값은 업데이트 불가)(단, null가능)
수정 [※부분가능※] [※부분가능※]
참조하는 자식레코드가 없으면 언제든 가능!!! 존재하는 부모키에 한해 가능
(부모한테 없는값은 업데이트 불가)(단, null가능)
삭제 [※부분가능※] [※항상가능※]
참조하는 자식레코드가 없으면 언제든 가능!!! 자식 테이블인 emp2에서 삭제를 하는것은 언제든지 가능!!!
--dept테이블에 부서입력(91, dept91)
insert into dept(deptno, dname)
values(91, 'dept91');
--dept테이블의 91번 부서번호를 99로 수정
update dept set deptno = 99 where deptno=91;
--dept테이블의 90번 부서번호를 1로 수정
update dept set deptno = 1 where deptno=90; --무결성 제약조건 위반
--integrity constraint (SCOTT.FK_EMP2_DEPTNO) violated - child record found
--업데이트 하고자 하는 데이터는 emp2에서 참조하고 있으므로 수정 불가
select * from dept;
--dept테이블에서 deptno=99 삭제
delete from dept where deptno=99;
--dept테이블에서 deptno=90 삭제
delete from dept where deptno=90;--무결성 제약조건 위반
--integrity constraint (SCOTT.FK_EMP2_DEPTNO) violated - child record found
--삭제 하고자 하는 데이터는 emp2에서 참조하고 있으므로 삭제 불가
select * from dept;
-- emp2테이블에 사원입사 (사번은 5번, 나머지는 임의내용)
insert into emp2(eno, ename, deptno)
values(5, '이이', null);
insert into emp2(eno, ename, deptno)
values(6, '이도', 99); --dept.deptno 컬럼에 99 값은 없어서 오류!!
--integrity constraint (SCOTT.FK_EMP2_DEPTNO) violated - parent key not found
insert into emp2(eno, ename, deptno)
values(6, '이도', 90);
select * from emp2;
-- 5, 이이 삭제
delete emp2 where eno=5;
-- 6, 이도 삭제
delete emp2 where eno=6;
select * from emp2;
/*
★★입력은 부모먼저하고 자식!★★
*테이블생성 : 부모테이블 ->자식테이블 순으로 생성
*부모테이블의 pk컬럼의 데이터입력 ->-> 자식테이블의 fk컬럼의 데이터입력 순
*/
/*
★★삭제는 자식먼저하고 부모!★★
*테이블삭제 : 자식테이블 ->부모테이블 순으로 삭제
*참조하는 child record가 존재해도 부모테이블의 데이터를 삭제하고 싶다면
방법1. fk제약조건삭제 후 부모테이블의 데이터 삭제
방법2. 참조하는 child record도 같이 삭제
*/
*fk제약조건 설정시 on delete cascade
constraint 제약조건명
foreign key(컬럼명) references 참조테이블명(컬럼명)
[on delete cascade];
외래키 쪽 제약조건 생성시 on delete cascade 조건 적용하면 참조테이블 삭제시 관련되는 child record 데이터도 같이 삭제된다.!
원래 참조되는 child record가 있으면 에러뜨면서 삭제가 안되지만 해당 조건을 명시해서 외래키를 생성해놓으면 참조테이블삭제시 같이 삭제 된다.
*제약조건이 있는 테이블 생성
--형태1
문법>CREATE TABLE 테이블명(
컬럼명 데이터타입(크기) [DEFAULT 기본값] [CONSTRAINT 제약조건명 제약조건],
컬럼명 데이터타입(크기) CONSTRAINT 제약조건명 PRIMARY KEY,
컬럼명 데이터타입(크기) CONSTRAINT 제약조건명 UNIQUE,
컬럼명 데이터타입(크기) CONSTRAINT 제약조건명 NOT NULL,
컬럼명 데이터타입(크기) CONSTRAINT 제약조건명 REFERENCES 부모테이블(컬럼명)
);
--형태2
문법> create table 테이블명(
컬럼명 데이터타입(크기) [default 기본값],
컬럼명 데이터타입(크기) [default 기본값] CONSTRAINT 제약조건명 NOT NULL,
컬럼명 데이터타입(크기) [default 기본값],
constraint 제약조건명 primary key(컬럼명)
constraint 제약조건명 UNIQUE(컬럼명)
constraint 제약조건명 CHECK(조건)
constraint 제약조건명 FOREIGN KEY(컬럼명)
references 부모테이블명(컬럼명)
);
--명시 안하면 기본값이 NULL인데 밑에 NOT NULL선언하는것이 문법상 맞지 않아 NOT NULL만 기본 컬럼명 구문 뒤에 바로 제약조건을 써준다.
--*****삭제
*테이블 삭제
문법>DROP table 테이블명 [PURGE]
--EMP10삭제
drop table EMP10;
*삭제한 테이블 복원
문법>flashback table "테이블명" to before drop;
flashback table "CTEST" to before drop;
flashback table "EMP10" to before drop;
*완전하게 테이블제거(flashback 불가)
문법>drop table 테이블명 purge;
--CTEST테이블 제거
drop table CTEST purge;
*휴지통 비우기
문법>PURGE RECYCLEBIN;
--휴지통비우기
PURGE RECYCLEBIN;
SELECT constraint_name, constraint_type, table_name
FROM USER_CONSTRAINTS;
================================================================연습구문================================================================ ===============================================================================================================================
/* 조회문법
SELECT { * | 컬럼명 [AS] alias}
FROM 테이블명
[WHERE 조건]
[GROUP BY 그룹기준1,..그룹기준N]
[HAVING 그룹조건]
[ORDER BY 정렬기준1 정렬방법,.. 정렬기준N 정렬방법];
-- 입력문법
INSERT INTO 테이블[(컬럼명,..컬럼명)]
VALUES(값,..값);*/
SELECT * FROM dept;
-- 제약조건사전의 구조확인
DESC USER_CONSTRAINTS;
-- 제약조회
SELECT constraint_name, constraint_type, table_name
FROM USER_CONSTRAINTS;
-제약조건
1)not null : null허용x=>값을 넣어라. 필수입력
2)unique : 유일한 값=>중복허용x
Primary key:pk.식별키. nn+unique이면서 테이블대표, 각 레코드를 구분
-- 주의 : 컬럼타입(문자&날짜는 '')
컬럼크기(숫자&영문 1글자는 1byte,
여기에서는 한글1글자는 3byte(한글은 encoding설정에 따라 달라짐))
INSERT INTO dept(deptno, dname, loc)
VALUES(95,'부서96','경주'); --unique constraint(SCOTT.PK_DEPT) violated
INSERT INTO dept(deptno, dname, loc)
VALUES(196,'부서96','경주');
-- value larger than specified precision allowed for this column
INSERT INTO dept(deptno, dname, loc)
VALUES(96,'최고로잘나가는개발부96','경주');
--value too large for column "SCOTT"."DEPT"."DNAME"(actual:32,maximum:14)
INSERT INTO dept(deptno, dname, loc)
VALUES(96,'최강개발96','경주');
--null입력 => 제약조건에 주의
1)명시적 null입력
2)묵시적 null입력
INSERT INTO dept(deptno, dname, loc)
VALUES(null,null,'');
--cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO")
--1)명시적 null입력
INSERT INTO dept(deptno, dname, loc)
VALUES(97,null,'');
-- commit하여 db에 DML작업내용을 반영
commit;
2)묵시적 null입력 : 명시한 컬럼명에 값을 넣어준다.명시하지않은 컬럼에는 null입력
INSERT INTO dept(deptno)
VALUES(98);
-- 테이블의 모든 컬럼에 value를 넣을 때에는 컬럼명생략 가능
INSERT INTO dept
VALUES(99,'디자인부','인제');
commit;
SELECT * FROM DEPT;
desc emp
-- 9000,이순신,보안,7839
INSERT INTO EMP(empno,ename,job,mgr)
VALUES(9000,'이순신','보안',7839);
-- 문자&날짜는 ''작은따옴표
-- 9001,김구,기획,오늘입사,6000,3000
INSERT INTO EMP(empno,ename,job,hiredate,sal,comm)
VALUES(9001,'김구','기획','2022/11/23',6000,3000);
-- 9002,장보고,어제입사,상사는이순신
INSERT INTO EMP(empno,ename,hiredate,mgr)
VALUES(9002,'장보고','2022/11/21',9000);
-- 9003,단군,2일전 입사
INSERT INTO EMP(empno,ename,hiredate)
VALUES(9003,'단군',sysdate-2);
select * from emp order by empno desc;
-- 현재 날짜 조회
-- 날짜+-숫자 => 날짜타입 리턴
select sysdate-1, sysdate, sysdate+(24/24)
from dual;
*형변환함수(p140)★★★★★
to_number(문자) : 문자->숫자
to_char(숫자,'숫자format'): 숫자->문자,
to_char(날짜,'날짜시간format'): 날짜->문자
to_date(문자,'날짜시간format') : 문자->날짜
to_timestamp(문자,'날짜시간format') : 문자->날짜
*to_char( 날짜,'format' )
날짜시간FORMAT(P141)
YY YYYY:년도
MM MON MONTH:월
D DD DDD:일
HH HH24:시
MI:분
SS:초
select sysdate as 현재, TO_CHAR(SYSDATE,'YYYY-MONTH-DD')
from dual;
select sysdate as 현재,
TO_CHAR(sysdate+(1/24),'YYYY.MON.DD HH24:MI:SS') as "1시간뒤",
TO_CHAR(sysdate+(1/(24*60)),'YYYY.MON.DD HH24:MI:SS') as "1분뒤"
from dual;
select sysdate as 현재,
TO_CHAR(sysdate,'YYYY MM DD DDD HH24 AM')
FROM DUAL;
select sysdate as 현재,
TO_CHAR(sysdate,'YYYY"년" MONTH DD"일"')
FROM DUAL;
select ename,sal,comm,nvl(comm,300),nvl2(comm,sal*2.5,300)
from emp;
select ename,
sal,
comm,
nvl(comm,300),
to_char(sal13+nvl2(comm,sal2.5,300),'999,999.99') as "연봉1",
to_char(sal13+nvl2(comm,sal2.5,300),'000,000.00') as "연봉2"
from emp;
연봉 = sal13+커미션
커미션=기존급여의 250% sal2.5
이때 커미션이 미확정되었으면 300
-- to_number(문자) : 문자->숫자
SELECT TO_NUMBER('100')*99
FROM DUAL;
SELECT empno,ename,sal from emp
where sal=800; --0초
SELECT empno,ename,sal from emp
where sal='800'; --0.002초
SELECT empno,ename,sal from emp
where sal=to_number('800'); --0.001초
SELECT empno,ename,sal from emp
where to_char(sal)='800';--0.001초
to_date(문자,'날짜시간format') : 문자->날짜
to_timestamp(문자,'날짜시간format') : 문자->날짜
-- 오라클에 이미 설정된 형식에 따라 날짜가 출력된다
-- 여기에서는 YY/MM/DD => 80/11/23
select to_date('801123','YYMMDD') from dual;
=> 80.11.23 오후 형식으로 출력하시오
SELECT to_char(to_date('801123','YYMMDD'),'YY.MM.DD PM') from dual;
SELECT to_char(sysdate,'YY.MM.DD') from dual;
SELECT to_char(hiredate,'YY.MM.DD HH24:MI:SS') from emp;
-- 밀리세컨드는 FF자리수로 표현
select to_timestamp('801123 01:30:59:12345',
'YYMMDD HH24:MI:SS:FF6') from dual;
++++++++++++++++++++++++++++++++++++
*수정
UPDATE 테이블명
SET 컬럼명=새값, 컬럼명=새값
[where 조건];
COMMIT;
-- 99번 부서를 정동진으로 변경
UPDATE DEPT
SET LOC='정동진'
WHERE DEPTNO=99;
-- 광주소재의 부서명을 인사부로 변경
UPDATE DEPT
SET DNAME='인사부'
where LOC='광주';
-- 부서명에 92가 포함된 부서의 이름을 회계부로 변경하고 울릉도로 이사
UPDATE DEPT
SET DNAME='회계부', LOC='울릉도'
where DNAME LIKE '%92%';
-- 부서번호가 90이상이면서
부서명에 %가 포함된 부서의 이름을 영업부로, 독도로 변경
UPDATE DEPT
SET DNAME='영업부', LOC='독도'
where DEPTNO>=90 AND DNAME LIKE '%@%%' ESCAPE '@';
-- 부서명이 확정되지 않은 부서는 개발부이고 대전에 위치하시오
UPDATE DEPT
SET DNAME='개발부', LOC='대전'
where DNAME IS NULL;
98인 부서는 소재지가 미확정으로 다시 조정되었다
UPDATE DEPT
SET LOC=''
where DEPTNO=98;
--94번 부서 BYE~~
DELETE FROM DEPT
where DEPTNO=94;
SELECT * FROM DEPT ORDER BY DEPTNO DESC;
/삭제
DELETE [FROM] 테이블명
[where 조건]/
COMMIT;
*table생성
CREATE TABLE 테이블명(
컬럼명 데이터타입,
컬럼명 데이터타입(크기) [CONSTRAINT 제약조건명][제약조건],
컬럼명 데이터타입(크기) [DEFAULT 값] [CONSTRAINT 제약조건명][제약조건]
)[TABLESPACE 테이블스페이스명];
*문자데이터타입
CREATE TABLE ctest(
c1 CHAR(15),
vc1 VARCHAR2(15)
);
INSERT INTO ctest(c1,vc1) values('abc123','abc123');
INSERT INTO ctest(c1,vc1) values('가나','가나');
INSERT INTO ctest(c1,vc1) values('char타입','varchar2타입');
commit;
select c1||vc1 from ctest;
select vc1||c1 from ctest;
*숫자데이터타입
create table ntest(
n1 number,
n2 number(7),
n3 number(7,2)
);
select * from ntest;
insert into ntest values(50/3,50/3,50/3);
insert into ntest values(500/3,500/3,500/3);
insert into ntest values(5000/3,5000/3,5000/3);
insert into ntest values(50000/3,50000/3,50000/3);
insert into ntest values(500000/3,500000/3,500000/3); --에러
*날짜데이터
create table dtest(
d1 date,
d2 timestamp
);
insert into dtest values(sysdate, sysdate);
select * from dtest;
++++++++++++++++++++++++++++++++
p98
INSERT~ SELECT문
INSERT INTO [스키마.]테이블명 (컬럼1, 컬럼2, …)
SELECT 컬럼명,컬럼명
from 테이블명;
● VALUES 절과 함께 값을 일일이 명시하는 대신 SELECT 문을 사용
● 테이블명 다음 컬럼 순서와 SELECT 다음의 컬럼 순서, 타입이 맞아야 함
● 테이블명 다음 컬럼 리스트를 생략할 경우 이 테이블의 모든 컬럼에 값을 넣는다는 의미
create table istest(
eno number(6),
ename varchar2(15),
salary number(7,2)
);
-- 급여가 3000이상되는 사번,사원명,급여 조회
INSERT INTO scott.istest(eno,ename,salary)
select empno,ename,sal
from emp
where sal>=3000;
select eno,ename,salary from scott.istest;
++++++++++++++++++++++++++++++++
p113
*CASE 표현식
CASE WHEN 조건1 THEN 값1
WHEN 조건2 THEN 값2
…
ELSE 기타값
END;
select ename,empno,sal,
CASE WHEN SAL>=6000 THEN 'A등급'
WHEN sal>=3000 THEN 'B등급'
WHEN sal>=2000 THEN 'C등급'
ELSE '기타등급'
END AS salgrade
from emp
order by sal desc;
-- 자바의 조건여러개있는 else if else문
if(조건1 score>=90){
조건1만족실행코드 "A학접"
}else if(조건2 score>=80){
조건2만족실행코드 "B학점"
}else if(조건n SCORE>=70){
조건n만족실행코드 "C학점"
}else{
모든조건만족x실행코드 "F"
}
++++++++++++++++++++++++++++++++
p114
*조건식 ANY,(SOME은 ANY와 동일하게 사용 및 동작), ALL
SELECT 컬럼명
FROM 테이블명
WHERE 조건;
-- 급여가 2000이거나 3000이거나 5000인 사원명,급여 조회
select ename,sal
from emp
where sal=2000 or sal=3000 or sal=5000;
select ename,sal
from emp
where sal in(2000, 3000, 5000);
-- 아래에서는 2000이상이거나 3000이상 혹은 5000이상 조건
-- => 최저 2000이상이 되는 의미 조건
select ename,sal
from emp
where sal >=any(2000, 3000, 5000);
select ename,sal
from emp
where sal >=some(2000, 3000, 5000);
--급여가 2000이상 and 급여가 3000이상 and 급여가 5000이상
=> 급여가 가장 큰 5000이상
select ename,sal
from emp
where sal >=all(2000, 3000, 5000);
-- 급여 2000 미만 조건
select ename,sal
from emp
where sal <all(2000, 3000, 5000);
-- 급여가 5000미만
select ename,sal
from emp
where sal <any(2000, 3000, 5000);
-- 입사일이 81/02/20 이거나
입사일이 81/04/20 혹은
입사일이 81/09/28인 사원명,입사일을
입사일이 빠른 사원부터 조회
select ename,hiredate
from emp
where hiredate in('81/02/20','81/04/20','81/09/28')
order by hiredate asc;
-- 입사일이 81/09/28(포함)이전에 입사한 사원조회
select ename,hiredate
from emp
where hiredate <=any('81/02/20','81/04/20','81/09/28')
order by hiredate asc;
++++++++++++++++++++++++++++++++
p247
*다중테이블 insert
*조건이 없는 다중테이블 insert all=> unconditional INSERT ALL
INSERT ALL
INTO 테이블 VALUES(컬럼명,..,컬럼명)
INTO 테이블 VALUES(컬럼명,..,컬럼명)
서브쿼리;
create table emp1(
eno number(6), --사원번호
ename varchar2(30), --사원명
pay number(10) --급여
);
create table emp2(
eno number(6), --사원번호
task varchar2(30), --업무
dno number(6) --부서번호
);
select * from emp1;
select * from emp2;
INSERT ALL
INTO emp1 VALUES(EMPNO,ENAME, SAL)
INTO emp2 VALUES(EMPNO,job,deptno)
SELECT EMPNO,ENAME, SAL, job, deptno
FROM EMP;
SELECT * FROM EMP1;
create table emp10(
eno number(6), --사원번호
ename varchar2(30), --사원명
payM number(10), --월급여
payY number(20), --년봉
dno number(2) --부서번호
);
create table emp20(
eno number(6), --사원번호
ename varchar2(30), --사원명
payM number(10), --월급여
payY number(20), --년봉
dno number(2) --부서번호
);
create table empetc(
eno number(6), --사원번호
ename varchar2(30), --사원명
payM number(10), --월급여
payY number(20), --년봉
dno number(2) --부서번호
);
select * from emp10; --3행입력
select * from emp20; --3행입력
select * from empetc;--6행입력
조건이 있는 다중테이블입력=> conditional INSERT ALL
INSERT ALL
WHEN deptno=10 THEN
INTO emp10 VALUES(empno, ename, sal, sal13,deptno)
WHEN deptno=20 THEN
INTO emp20 VALUES(empno, ename, sal, sal13,deptno)
ELSE
INTO empetc VALUES(empno, ename, sal, sal13,deptno)
SELECT empno, ename, sal, sal*13, job, deptno
FROM EMP
WHERE deptno=10 or deptno=20 or deptno=30;
--EMP10의 모든 데이터를 삭제
DELETE FROM EMP10;
--EMP20의 모든 데이터를 삭제
DELETE FROM EMP20;
--EMPETC의 모든 데이터를 삭제
DELETE FROM EMPETC;
SELECT * FROM EMP10; --1행입력
SELECT * FROM EMP20; --9행입력
SELECT * FROM EMPETC;--2행입력
INSERT ALL
WHEN sal>=3001 THEN
INTO emp10 VALUES(empno, ename, sal, sal13,deptno)
WHEN sal BETWEEN 1000 AND 3000 THEN
INTO emp20 VALUES(empno, ename, sal, sal13,deptno)
ELSE
INTO empetc VALUES(empno, ename, sal, sal13,deptno)
SELECT empno, ename, sal, sal13, job, deptno
FROM EMP
WHERE deptno=10 or deptno=20 or deptno=30;
conditional INSERT FIRST
INSERT FIRST
WHEN sal>=3000 THEN
INTO emp10 VALUES(empno, ename, sal, sal13,deptno)
WHEN sal>=2000 THEN
INTO emp20 VALUES(empno, ename, sal, sal13,deptno)
ELSE
INTO empetc VALUES(empno, ename, sal, sal13,deptno)
SELECT empno, ename, sal, sal*13, job, deptno
FROM EMP
WHERE deptno=10 or deptno=20 or deptno=30;
SELECT * FROM EMP10; --2행입력
SELECT * FROM EMP20; --3행입력
SELECT * FROM EMPETC;--7행입력
INSERT ALL
WHEN sal>=3000 THEN
INTO emp10 VALUES(empno, ename, sal, sal13,deptno)
WHEN sal>=2000 THEN
INTO emp20 VALUES(empno, ename, sal, sal13,deptno)
ELSE
INTO empetc VALUES(empno, ename, sal, sal13,deptno)
SELECT empno, ename, sal, sal13, job, deptno
FROM EMP
WHERE deptno=10 or deptno=20 or deptno=30;
SELECT * FROM EMP10; --2행입력 3000 5000
SELECT * FROM EMP20; --5행입력 3000 5000
SELECT * FROM EMPETC;--7행입력
-- 테이블삭제
drop table 테이블명;
++++++++++++++++++++++++++++++++++++++++++++++++
*함수(4장)
-내장함수
-사용자정의 함수=>PL/SQL
-암묵적함수
-명시적함수
-문자함수(P130)
-숫자함수
-날짜함수
*데이터타입
-문자
-char
-varchar2
-숫자
-number
-날짜
-date
-문자함수(P130)
select initcap('apple banana'),
lower('apple baNANa'),upper('apple banana')
from dual;
*p131
SUBSTR(char, pos, len) : ★★★★★
char의 pos번째 문자부터 len 길이만큼 잘라낸 결과를 반환
pos이 0이 오면 디폴트값인 1, 즉 첫번째 문자를 가르킨다
posdl 음수이면 뒤에서부터 시작,즉 -1이면 가장 마지막 글자의 위치를 의미.
len을 생략하면 문자열의 끝까지 추출한다는 의미
SELECT SUBSTR('I have Big DREAM',0,1), --I
SUBSTR('I have Big DREAM',1,1), --I
SUBSTR('I have Big DREAM',0,5), --I hav
SUBSTR('I have Big DREAM',1,5), --I hav
SUBSTR('I have Big DREAM',3), --have Big DREAM
SUBSTR('I have Big DREAM',-1),--M
SUBSTR('I have Big DREAM',-3) --EAM
FROM DUAL;
*p137
INSTR(str, substr, pos, occur) :
str에서 substr과 일치하는 위치를 반환, pos는 시작위치,
pos는 생략가능. 생략하면 1적용=>첫번째 문자부터 찾겠다
occur은 몇 번째 일치하는지를 명시.
생략가능.생략하면 1적용=>첫번째 등장하는 문자의 위치를 리턴.
--아래는'apple'에서 'a'를 1번째 문자부터 1번째 등장하는 문자의 위치를 리턴.
select INSTR('apple','a',1,1) as "ex1",--1
INSTR('apple','a',1) as "ex2",--1
INSTR('apple','a') as "ex3" --1
from dual;
--'apple'에서 2부터 1번째 등장하는 'a'를 찾아라=>'pple'에서 1번째 등장하는 'a'를 찾아라
select INSTR('apple','a',2,1) as "ex1" --0
from dual;
select INSTR('apple','ap' ,1,1) as "ex1",--1
INSTR('apple','app',1,1) as "ex2",--1
INSTR('apple','apk',1,1) as "ex3",--0
INSTR('apple','afk',1,1) as "ex4",--0
INSTR('apple','A' ,1,1) as "ex5",--0 대소문자까지 일치
--'apple'에서 1번째위치부터 1번째등장하는 'p'의 위치를 리턴
INSTR('apple','p' ,1,1) as "ex6",--2
--'apple'에서 2번째위치부터 1번째등장하는 'p'의 위치를 리턴
INSTR('apple','p' ,2,1) as "ex7",--2
--'apple'에서 1번째위치부터 2번째등장하는 'p'의 위치를 리턴
INSTR('apple','p' ,1,2) as "ex8", --3
--'apple'에서 2번째위치부터 2번째등장하는 'p'의 위치를 리턴
INSTR('apple','p' ,2,2) as "ex9" --3from dual;
-- '801123-1234567'에서 생년월일 부분 추출=>801123
-- '801123-1234567'에서 성별부분 추출 =>1
select SUBSTR('801123-1234567', 1, 6), --801123
SUBSTR('801123-1234567', 8, 1) --1
from dual;
-- '02)123-4567' 에서 지역번호 추출=>02
-- '031)123-4567 에서 지역번호 추출=>031
create table test(
tel varchar2(15)
);
INSERT INTO test values('02)123-4567');
INSERT INTO test values('031)123-4567');
select * from test;
-- tel에서 지역번호 추출=> 02 031
select SUBSTR(tel,1, 2),--02 03
SUBSTR(tel,1,3)--02) 031
from test;
-- 지역번호는 첫번째부터 ')'앞까지
-- 추출은 SUBSTR(문자열, 위치, 글자수)
-- 추출은 SUBSTR(전번컬럼명, 첫번째, 글자수)
-- 추출은 SUBSTR(tel, 1, 글자수)
-- 글자수 02이면 2, 031이면 3이 되어야 한다=>유동적
-- ')'를 찾아서 그 앞까지 =>')'를 찾아서-1
-- 특정문자의 위치를 찾아라=>INSTR(대상문자,찾는문자,시작위치,몇번째)
-- INSTR(전번컬럼,')',처음부터,1번째등장)
-- INSTR(tel, ')',1 ,1)=>')'의 위치를 리턴받았다
-- => 리턴받은 ')'의 위치-1하면 전번컬럼에서 ')'앞까지를 의미하게 된다
select INSTR(tel,')',1,1) from test; --3 --4
select SUBSTR(tel, 1, INSTR(tel,')',1,1)-1 )
from test;
/*오라클 9i R2부터 WITH절 사용.
임시테이블(가상테이블)로서 사용.
WITH 테이블명 AS (
SELECT쿼리문
)
SELECT쿼리문;
*/
SELECT '02)123-4567' AS t1 FROM DUAL
UNION ALL
SELECT '031)123-4567' AS t1 FROM DUAL;
JDK (1.8) =>1.8
TOMCAT (9) =>9
Oracle SQL Developer (19.2.1) =>19.2.1
JDK (1.8)버전 =>1.8
TOMCAT (9) version =>9
Oracle SQL Developer (19.2.1)을 사용=>
WITH table1 AS (
SELECT '02)123-4567' AS t1 FROM DUAL
UNION ALL
SELECT '031)123-4567' AS t1 FROM DUAL
)
select INSTR(t1,')',1,1) as "1",
SUBSTR(t1, 1, INSTR(t1,')',1,1)-1 ) as "2"
from table1;
--3 --4
select SUBSTR(tel2컬럼명, 1, INSTR(tel2컬럼명,')',1,1)-1 ) from test??;
*P132
SELECT LTRIM('NA IS NA','NA'), RTRIM('NA IS NA','NA')
FROM DUAL;
SELECT length(' I AM.. '),
length(TRIM(' I AM.. ')) FROM DUAL;
SELECT TRIM(' I AM.. ') FROM DUAL;
select Lpad(ename,10,''),Lpad(ename,10,' ') ,Rpad(ename,10,'')
from emp;
p134
REPLACE(char, search_str, replace_str) :
char에서 search_str을 찾아 이를 replace_str로 대체한 결과를 반환
TRANSLATE(expr, from_str, to_str) :
expr에서 from_str에 해당하는 문자를 찾아 to_str로
한 글자씩 바꾼 결과 반환
select replace('나는 너를 모르는데', '나는','iam'),
translate('나는 너를 모르는데','나는','iam')
from dual;
P137
length:글자길이
SELECT length('APPLE'),length('APPLE 12!'),
length('김구'),length('김구 12!')
FROM DUAL;
lengthB:Byte수를 리턴=> 여기에서는 한글1글자당 3byte
SELECT lengthB('APPLE'),lengthB('APPLE 12!'),
lengthB('김구'), lengthB('김구 12!')
FROM DUAL;
++++++++++++++++++++++++++++++++++++++++++
*날짜함수(p138)
-- 오늘날짜 조회
SELECT SYSDATE, systimestamp
FROM DUAL;
select add_months(sysdate,-6), add_months(sysdate,18)
from dual;
select months_between(sysdate, add_months(sysdate,-6))
from dual;
--입사한 달의 근무일수(토,일,공휴일에도 근무가정)
--날짜-날짜의 데이터타입은 숫자
select ename,hiredate, last_day(hiredate), last_day(hiredate)-hiredate
from emp
where deptno=10
order by hiredate asc;
--비교 : 숫자데이터대상의 trunc(), round(값,자릿수기준0)
select trunc(123.456), trunc(456.78) from dual;
select round(123.456,-2), round(123.456,-1),
round(123.456,0), round(123.456,1)
from dual;
--비교 : 날짜데이터대상의 trunc(날짜데이터, FMT), round(날짜데이터, FMT)
--trunc함수는 FMT에 지정된 단위로 절삭
--round함수는 FMT가 년도라면 년도단위로 반온림
FMT가 생략되면 날짜를 가장 가까운 날짜로 반올림
FMT: YEAR,MONTH,DD 등=>P141 날짜변환형식 참고
select sysdate,
trunc(systimestamp,'month'),
to_char(trunc(systimestamp,'month'), 'YYYY.MM.DD HH24:MI:SS'),
trunc(systimestamp,'day'),
to_char( trunc(systimestamp,'day'), 'YYYY.MM.DD HH24:MI:SS')
from dual;
select sysdate,
ROUND(systimestamp,'month'),
to_char(ROUND(systimestamp,'month'),'YYYY.MM.DD HH24:MI:SS'),
ROUND(systimestamp,'day'),
to_char(ROUND(systimestamp,'day'),'YYYY.MM.DD HH24:MI:SS')
from dual;
*TABLE삭제 => AUTO COMMIT되므로 주의.
문법> DROP TABLE 테이블명;
-- scott의 bonus,emp,dept,salgrade 제외하고 전부 삭제
DROP TABLE dtest;
-- 사원번호9000이상인 사원삭제 후 commit;
delete from emp where empno>=9000;
commit;
-- 부서테이블에서 부서번호91이상인 부서삭제 후 commit;
delete from dept where deptno>=91;
commit;
++++++++++++++++++++++++++++++++++++++++++++++
P176
JOIN★★★★★
SELECT 컬럼명 [AS] 별칭, 컬럼명 AS 별칭
FROM 테이블명 별칭, 테이블명 별칭
[WHERE 조건];
select empno,ename,job,deptno
from emp
order by deptno asc;
-- cross join, catasian product(p184)
-- join조건을 명시하지 않는 조인
select empno,ename,job,dname
from emp, dept; --사원.12개행*부서.5개의행=> 60의 행
--equi join(동등조인.p176)
--where절에 =(동등기호)를 이용하여 join조건을 명시
select empno,ename,job,dname
from emp, dept
where emp.deptno=dept.deptno; --12개의 행
--where 사원테이블의 부서번호 부서테이블의 부서번호컬럼의 값이 같다;
--equin조인은 조인조건에 사용되는 값들이 서로 일치=하는 데이터만 select된다
--즉 여기에서는 사원테이블에 부서번호가 null인 사원은 select되지x
--즉 소속부서가 확정되지않는 사원은 제외=>소속부서가 명확한 사원만select
select empno,ename,job,dname
from emp, dept
where emp.deptno=dept.deptno
and
emp.deptno is not null; --12개의 행
-- 에러: column ambiguously defined 발생
-- 해결방법: 조회하는 컬럼의 소속테이블명을 명시하면 된다. 테이블명.컬럼명
select emp.empno, emp.ename, emp.job,
dept.deptno, dept.dname
from emp, dept
where emp.deptno=dept.deptno;
-- 테이블에 별칭을 부여하여 쿼리는 간단,소속분명,이해도향상
select e.empno, e.ename, e.job, 별칭.컬럼명,별칭.컬럼명
d.deptno, d.dname
from emp e, dept d
where e.deptno=d.deptno;
*p198 인라인뷰(inline view)
-- from절에 서브쿼리(subquery)가 들어가면 inline view
select e.empno, e.ename, e.job, 별칭.컬럼명,별칭.컬럼명
d.deptno, d.dname
from emp e, dept d, (select 컬럼명,컬럼명 from 테이블명) 별칭
where e.deptno=d.deptno;
-- 10번 또는 20번부서에 근무하는
사원번호,사원명,급여, 부서번호,(부서명)을
부서명 오름차순 정렬하여 조회
select e.empno, e.ename,e.sal,e.deptno, d.dname
from emp e, dept d
where (e.deptno=10 or e.deptno=20)
and
e.deptno=d.deptno
order by d.dname asc;
select e.empno, e.ename,e.sal,e.deptno, d.dname
from emp e, dept d
where e.deptno IN(10,20)
and
e.deptno=d.deptno
order by d.dname asc;
/* 급여가 1600이상인 사원번호,사원명,급여,부서번호,부서명을
급여를 많이 받는 사원부터 출력
(이때, 급여가 확정된 사원에 한함)*/
select e.empno, e.ename,e.sal,e.deptno, d.dname
from emp e, dept d
WHERE e.deptno=d.deptno and e.sal>=1600 and e.sal is not null
order by e.sal desc;
-- 사번,사원명,부서번호,부서명을 부서번호 오름차순 조회
select e.empno, e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno=d.deptno
order by d.deptno asc;
-- natural join조인
-- 문법> from 테이블명1 NATURAL JOIN 테이블명2
-- 주의>natural join에 사용된 컬럼의 소속테이블은 명시하면 안된다
-- 해결방법> 컬럼명만 명시하자
-- 예시> d.deptno가 아닌 deptno;
-- 예시> order by d.deptno가 아닌 order by deptno;
select e.empno, e.ename, deptno, d.dname
from emp e NATURAL JOIN dept d
order by deptno;
-- JOIN USING()
-- 문법> from 테이블명1 JOIN 테이블명2 USING(join사용컬럼)
-- column part of USING clause cannot have qualifier
-- 주의>JOIN에 사용된 컬럼의 소속테이블은 명시하면 안된다
-- 해결방법> 컬럼명만 명시하자
-- 예시> d.deptno가 아닌 deptno;
-- 예시> order by d.deptno가 아닌 order by deptno;
select e.empno, e.ename,deptno, d.dname
from emp e JOIN dept d USING(deptno)
order by deptno;
--ANSI조인(P184)
--참고 ANSI:미국 국립 표준 협회(American National Standards Institute, ANSI)
--ANSI가 제정한 표준 중 가장 유명한 것이 ASCII 코드다.
-- JOIN ON(P184)
-- 문법> 테이블명 [INNER] JOIN 테이블명 ON 조인조건
-- column ambiguously defined
-- 주의. 테이블명을 명시하자
select e.empno, e.ename,d.deptno, d.dname
from emp e INNER JOIN dept d ON e.deptno=d.deptno
order by d.deptno;
-- 9000,이순신,DBA,오늘,7000,부서미확정
INSERT INTO EMP
VALUES(9000,'이순신','DBA',NULL,SYSDATE,7000,NULL,NULL);
COMMIT;
SELECT e.empno, e.ename,e.job, d.deptno, d.dname
from emp e, dept d
WHERE e.deptno(+)=d.deptno;
-- WHERE e.deptno(+)=d.deptno;와 동일한 결과
SELECT e.empno, e.ename,e.job, d.deptno, d.dname
from emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;
SELECT e.empno, e.ename,e.job, d.deptno, d.dname
from emp e, dept d
WHERE e.deptno=d.deptno(+);
-- WHERE e.deptno=d.deptno(+);와 동일결과
SELECT e.empno, e.ename,e.job, d.deptno, d.dname
from emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;
--a predicate may reference only one outer-joined table
SELECT e.empno, e.ename,e.job, d.deptno, d.dname
from emp e, dept d
WHERE e.deptno(+)=d.deptno(+);
SELECT e.empno, e.ename,e.job, d.deptno, d.dname
from emp e FULL OUTER JOIN dept d
ON e.deptno=d.deptno;
-- NON-EQUI JOIN조인
-- 사번,사원명, 급여, 급여등급 조회
SELECT e.empno,e.ename,e.sal, sg.grade
FROM EMP e,salgrade sg
WHERE e.sal BETWEEN sg.losal AND sg.hisal;
SELECT e.empno,e.ename,e.sal, sg.grade
FROM EMP e,salgrade sg
WHERE e.sal>=sg.losal AND e.sal<=sg.hisal;
-- SELF JOIN(P180)
-- 동일한 테이블을 사용해 조인하는 방법
SELECT e.empno,e.ename,e.mgr,
ta.empno, ta.ename
from emp e, emp ta
where e.mgr=ta.empno;
++++++++++++++++++++++++++++++++
*subquery(서브쿼리 p191)
: 서브쿼리란 SQL문장안에서 보조로 사용되는 또 다른 SELECT문을 의미.
SELECT 컬럼명 [AS alias], 컬럼명,
~
FROM 테이블명 별칭, 뷰명, ~
[WHERE 조건 ~~]
[GROUP BY 그룹기준]
[HAVING 그룹조건]
[ORDER BY 정렬기준 방법]
-- 20번부서에 근무하는 사번,사원명,급여,부서번호 조회
SELECT empno,ename,sal,deptno
FROM EMP
WHERE deptno=20;
-- SMITH가 근무하는 부서번호는?
SELECT deptno
FROM emp
WHERE ename='SMITH';
-- (SMITH와 동일부서)에 근무하는 부서번호,사번,사원명조회
SELECT deptno,empno,ename
FROM emp
WHERE deptno = (SELECT deptno
FROM emp
WHERE ename='SMITH');
-- TURNER와 같은 일은 하는 사번,사원명,업무 조회
SELECT empno,ename,job
FROM EMP
WHERE JOB=(SELECT JOB
FROM EMP
WHERE ENAME='TURNER');
-- TURNER가 받는 급여보다 많이 받는 사번,사원명,급여 조회
-- 급여가 1500보다 많이 받는 사번,사원명,급여 조회
select sal
from emp
where ename='TURNER';
SELECT EMPNO,ENAME,SAL
from emp
where SAL>(select sal
from emp
where ename='TURNER');
-- 전체 사원들의 평균급여 이상을 받는
-- 사번,사원명,업무 조회
--1.전체 사원들의 평균급여를 조회?
SELECT avg(SAL)
FROM EMP; --2455.7692
--2.조회된 평균급여 이상의 사번,사원명,업무 조회
--급여가 2455.7692 이상의 사번,사원명,업무 조회
select empno,ename,job, sal
from emp
where sal>=2455.7692;
select empno,ename,job, sal
from emp
where sal>=(SELECT avg(SAL)
FROM EMP);
-- TURNER와 같은 일은 하는 사원들의 평균급여 이상을 받는
-- 사번,사원명,업무 조회
-- TURNER가 하는 일 조회
-- 사원명이 TURNER인 업무 조회
select job
from emp
where ename='TURNER'; -- SALESMAN
-- TURNER와 같은 일은 하는 사원들의 평균급여
-- => job이 SALESMAN 사원들의 평균급여
select AVG(sal)
from emp
where job='SALESMAN'; --평균급여가 1400
select empno,ename,job, sal
from emp
where 급여가 (평균급여) 이상
where 급여가 (1400) 이상
where sal>=(1400);
where sal>=(select AVG(sal)
from emp
where job=(TURNER가 하는 업무));
where sal>=(select AVG(sal)
from emp
where job=(select job
from emp
where ename='TURNER'));
정리하면
select empno,ename,job, sal
from emp
where sal>=(select AVG(sal)
from emp
where job=(select job
from emp
where ename='TURNER'));
-- 위의 결과에 부서명을 추가하여 출력하시오
select e.empno,e.ename,e.job, e.sal, d.dname
from emp e, dept d
where e.sal>=(select AVG(sal)
from emp
where job=(select job
from emp
where ename='TURNER'))
AND
e.deptno=d.deptno;
select e.empno,e.ename,e.job, e.sal, d.dname
from emp e, dept d
where e.sal>=(select AVG(sal)
from emp e1
where e1.job=(select job
from emp
where ename='TURNER'))
AND
e.deptno=d.deptno;
select e.empno,e.ename,e.job, e.sal, d.dname
from emp e, dept d
where e.sal>=(select AVG(sal)
from emp e1
where e1.job=(select e2.job
from emp e2
where e2.ename='TURNER'))
AND
e.deptno=d.deptno;
--new york에 근무하는 부서번호,부서명,소재지,사번,사원명,급여
급여를 많이 받는 사원부터 출력
SELECT d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal
FROM DEPT d, emp e
WHERE loc=upper('new york') AND
d.deptno=e.deptno
ORDER BY e.sal DESC;
-- NATURAL JOIN이용시
SELECT deptno,d.dname,d.loc,e.empno,e.ename,e.sal
FROM DEPT d NATURAL JOIN emp e
WHERE loc=upper('new york')
ORDER BY e.sal DESC;
--JOIN USING() 이용시
SELECT deptno,d.dname,d.loc,e.empno,e.ename,e.sal
FROM DEPT d JOIN emp e USING(deptno)
WHERE loc=upper('new york')
ORDER BY e.sal DESC;
--JOIN ON이용시
SELECT d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal
FROM DEPT d JOIN emp e ON d.deptno=e.deptno
WHERE loc=upper('new york')
ORDER BY e.sal DESC;
-- allen이 근무하는 부서의 인원수이상 사원들이 근무하는
부서명를 구하시오
select deptno,dname
from dept
where deptno=(select e.deptno
from emp e
group by e.deptno
having count(e.deptno)>=(select count(deptno)
from emp
where deptno=(select deptno
from emp
where ename='ALLEN')));
-- allen이 근무하는 부서의 인원수이상 사원들이 근무하는
부서명,사원수를 구하시오
SELECT count(d.deptno), d.dname
FROM emp e JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.deptno, d.dname
HAVING COUNT(d.deptno)>=(SELECT COUNT(deptno)
FROM emp
WHERE deptno = (SELECT deptno
FROM emp
WHERE ename = 'ALLEN'));
select e.deptno, d.dname, count(e.deptno)
from emp e join dept d on e.deptno=d.deptno
where e.deptno is not null
group by e.deptno, d.dname
having count(e.deptno)>=(select count(deptno)
from emp
where deptno=(select deptno
from emp
where ename=upper('allen')))
order by deptno;
+++++++++++++++++++++++++++++++++++++++++
p177~180, 194
*다중행 서브쿼리 =>서브쿼리의 결과로 리턴받은 rows수가 여러개일때
연산자(서브쿼리) : IN, ANY, ALL, EXISTS
sal=2450 or sal=5000 or sal=1300 => sal IN(2450,5000,1300)
select empno,ename,sal,deptno
from emp
where sal in(select sal from emp where deptno=10)
order by sal desc;
select empno,ename,sal,deptno
from emp --(2450,5000,1300)
where sal >=any(select sal from emp where deptno=10)
order by sal desc;
select empno,ename,sal,deptno
from emp --(2450,5000,1300)
where sal >=all(select sal from emp where deptno=10)
order by sal desc;
p194
*EXISTS : EXISTS 사전적의미로 존재하다라는 뜻.
=>서브쿼리의 실행결과가 존재하면 메인쿼리를 실행
=>서브쿼리의 실행결과가 없으면하면 메인쿼리 실행결과 없다
select empno,ename,deptno
from emp
where EXISTS(select deptno
from dept
where dname='SALES');
select empno,ename
from emp
where exists(select deptno
from dept
where dname='SALES123');
+++++++++++++++++++++++++++++++++
*VIEW객체(P73)
VIEW : 하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는
데이터베이스 객체이다.
*View생성문법
CREATE [OR REPLACE] VIEW 뷰명
AS
Subquery;
*View삭제문법
DROP VIEW 뷰명;
/*만약insufficient privileges 오류발생하면
권한있는 유저에게 접속후 scott에게 view생성권한 부여한다
conn system/시스템비번
grant create view
to scott;
*/
drop view emp_dept;
-- 사번,사원명,부서명 조회
CREATE OR REPLACE VIEW emp_dept
AS
SELECT e.empno, e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
and
e.sal>=2450;
select * from emp_dept;
--수정
-- 7698사원의 급여를 3000으로 수정(2850->3000)
UPDATE emp_dept
SET SAL=3000
WHERE EMPNO=7698;
SELECT * FROM emp_dept WHERE EMPNO=7698;
-- 원본 EMP테이블의 7698사원의 급여를 2850으로 수정(3000->2850)
UPDATE emp
SET SAL=2850
WHERE EMPNO=7698;
SELECT * FROM emp WHERE EMPNO=7698; --급여가 2850으로 변경완료.
SELECT * FROM emp_dept WHERE EMPNO=7698;
-- 뷰에서 7698사원의 급여를 100으로 수정
UPDATE emp_dept SET SAL=100 WHERE EMPNO=7698;
-- 원본 EMP에서 확인
select * from emp WHERE EMPNO=7698;
-- 뷰 emp_dept에서 확인
select * from emp_dept;
--삭제
--delete from emp_dept whre empno=7698;
desc user_views;
select view_name, text, read_only
from user_views;
*생성
CREATE [OR REPLACE] 객체타입 객체명
~~
*수정
ALTER 객체타입 객체명
~
*삭제
DROP 객체타입 객체명;
++++++++++++++++++++++++++++++++++++
-- system 계정에 접속
conn system/비번
-- 계정정보 비번 및 계정잠금상태 변경
문법>ALTER USER 유저명
IDENTIFIED BY 비번
ACCOUNT UNLOCK | LOCK;
-- hr계정의 비번hr, 계정풀기
ALTER USER hr
IDENTIFIED BY hr
ACCOUNT UNLOCK;
++++++++++++++++++++++++++++++++++++
*p198 인라인뷰(inline view)
-- from절에 서브쿼리(subquery)가 들어가면 inline view
-- 부서별 부서명,평균급여,총급여를 부서번호오름차순정렬하여 출력
-- 이때 평균급여는 소수 첫째자리에서 반올림
select deptno, round(avg(sal),0) avg_sal ,sum(sal) sum_sal
from emp
where deptno is not null
group by deptno;
select d.dname, e.avg_sal, e.sum_sal
from dept d,
(select deptno, round(avg(sal),0) avg_sal, sum(sal) sum_sal
from emp
where deptno is not null
group by deptno) e
where d.deptno=e.deptno
order by d.deptno asc;
++++++++++++++++++++++++++++++++++++++
P83
*SEQUENCE(시퀀스) : 자동순번을 반환하는 데이터베이스 객체
사용예)사원번호,부서번호,회원번호,글번호,주문번호,대여번호,예약번호
*시퀀스조회
select *
from user_sequences;
-- 부서dept테이블의 deptno컬럼의 값으로 사용하고자하는 시퀀스생성
-- 71부터 89까지 2씩 증가
*생성
CREATE SEQUENCE 시퀀스명
[START WITH 시작번호] --시작번호(수정불가)
[INCREMENT BY 값] --증감규칙
[MINVALUE 최저값]
[MAXVALUE 최고값]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE];
CREATE SEQUENCE scott.dept_deptno_seq
START WITH 71 --시작번호(수정불가)
INCREMENT BY 2 --증감규칙
MINVALUE 1
MAXVALUE 89; --끝번호
*수정
ALTER SEQUENCE 스키마명.시퀀스명
--[START WITH 시작번호] --시작번호(수정불가)
[INCREMENT BY 값] --증감규칙
[MINVALUE 최저값]
[MAXVALUE 최고값]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE];
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept1'); --71
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept2'); --72
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept3'); --73
select * from dept;
--사원번호(시퀀스이용),'emp1'입력
INSERT INTO EMP(empno,ename)
VALUES( dept_deptno_seq.NEXTVAL ,'emp1'); --74
INSERT INTO EMP(empno,ename)
VALUES( dept_deptno_seq.NEXTVAL ,'emp2'); --75
select * from emp;
--부서의 부서번호 INSERT? 몇?
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept4'); --76
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept5'); --77
select * from dept;
select dept_deptno_seq.nextval from DUAL;--78
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept6'); --79
ROLLBACK;
select * from dept; -- 71,72,73,76,77,79 삭제
select * from emp; -- 74,75삭제
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept7'); --80
select * from dept; --80
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept7'); --81
select * from dept; --81
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept8'); --오류발생
--sequence DEPT_DEPTNO_SEQ.NEXTVAL exceeds MAXVALUE
-- and cannot be instantiated
-- cannot alter starting sequence number
-- 시작번호는 수정불가하다
ALTER SEQUENCE scott.dept_deptno_seq
--START WITH 71 --시작번호(수정불가)
INCREMENT BY 2 --증감규칙
MINVALUE 1
MAXVALUE 89;
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'AFTER--'); --83
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept10'); --85
INSERT INTO DEPT(deptno,dname)
VALUES(dept_deptno_seq.nextval, 'dept11'); --87
SELECT * FROM DEPT;
*삭제
문법>DROP SEQUENCE 시퀀스명;
DROP SEQUENCE dept_deptno_seq;
SELECT * FROM USER_SEQUENCES;
--dept테이블에서 부서번호가10,20,30,40,90제외한 부서정보 삭제
DELETE FROM dept
where deptno not in(10,20,30,40,90);
SELECT * FROM DEPT;
SELECT * FROM EMP;
COMMIT;
-- EMP의 식별키 EMPNO에 사용될 시퀀스생성
CREATE SEQUENCE EMP_EMPNO_SEQ
START WITH 9100 --시작번호(수정불가)
INCREMENT BY 1; --증감규칙
SELECT * FROM USER_SEQUENCES;
-- 시퀀스명.NEXTVAL
-- 시퀀스명.CURRVAL
-- 위에서 생성한 시퀀스를 이용하여 EMP에 데이터입력
INSERT INTO emp(empno,ename,job,hiredate,sal,comm)
VALUES(EMP_EMPNO_SEQ.NEXTVAL,'조규성','개발',SYSDATE,5000,2500);
SELECT * FROM EMP ORDER BY EMPNO DESC;
ROLLBACK;
======================================================================================