쿼리 및 집합의 이해-2009/02/16

-----------------------------
-- hr
-----------------------------

-- 테이블 생성
create table temp_emp (
id number not null,
name varchar2(12),
primary key (id)
);

-- 테이블 검색
select * from tab where tname='TEMP_EMP';

-- 테이블 구조보기
desc temp_emp;

-- commit/rollback
insert into temp_emp(id, name) values(1,'홍길동');
select name from temp_emp where id=1;
commit;
update temp_emp set name='홍인영' where id=1;
insert into temp_emp(id, name) values(2,'홍판서');
select * from temp_emp;
rollback;
commit;
select name from temp_emp where id=1;
delete temp_emp;
rollback;
select name from temp_emp where id=1;

select * from temp_emp;
update temp_emp set name='홍만석' where id=1;
savepoint a;
insert into temp_emp(id,name) values(3,'강감찬');
savepoint b;
delete from temp_emp;
rollback to savepoint a;

----------------------------
-- sys
----------------------------
-- 예약어
select keyword from v$reserved_words order by keyword;

----------------------------
-- hr
----------------------------
select * from departments;

----------------------------
-- scott
----------------------------

-- *** select

-- scott의 모든 테이블
select * from tab;
-- emp테이블의 모든 컬럼 조회
select * from emp;
-- emp테이블의 구조
desc emp;
-- emp테이블의 empno, ename 컬럼 조회
select empno, ename from emp;
-- emp테이블에 있는 job의 종류 조회
select distinct job from emp;
select job from emp;
-- emp테이블에 있는 모든 job
select all job from emp;
-- sal에 대해 정렬
select * from emp order by sal asc;
select * from emp order by sal;    
select * from emp order by sal desc;
-- 정렬시 null인 값은 마지막에
select * from emp order by comm;
-- 2차 정렬 (1차정렬이 완료된 후 2차정렬함)
select deptno, ename from emp order by deptno, ename;
-- alias (별칭)
select empno, sal 봉급, sal*0.1 보너스, sal+sal*0.1 총수령액 from emp;
-- emp테이블에서 deptno가 10
select empno, ename, deptno from emp
where deptno=10;
-- 현재 오라클이 인식하는 날짜 형식 알아내기
select value from nls_session_parameters
where parameter='NLS_DATE_FORMAT';
-- 사원들의 입사일
select hiredate from emp;
-- 1983년 이전에 입사한 사원들의 데이터
select * from emp where hiredate<'83/01/01';
-- 조건연산
select * from emp where deptno=10 and sal<3000;
-- not in 연산 (job이 SALESMAN이 아닌 데이터)
select * from emp where job not in 'SALESMAN';
-- in 연산 (job이 CLERK, MANAGER인 데이터)
select * from emp where job in ('CLERK','MANAGER');
select * from emp where job='CLERK' or job='MANAGER';
-- like 연산
-- ename에 M이 포함된 모든 데이터
select * from emp where ename like '%M%';
-- ename이 M으로 시작하는 모든 데이터
select * from emp where ename like 'M%';
-- ename이 M으로 끝나는 모든 데이터
select * from emp where ename like '%M';
-- 한글자는 _ (_자체를 표시하려면 \_)
select * from emp where ename like '_____';
-- between a and b (a, b를 포함해서 그 사이)
select empno, deptno from emp where deptno between 10 and 20;
select empno, deptno from emp where deptno>=10 and deptno<=20;
select * from emp where ename between 'A' AND 'K';
select * from emp where hiredate between '81/01/01' and '83/05/01';
-- null : 데이터 없음
select * from emp where comm is null;
select * from emp where comm is not null;
-- (X) select * from emp where comm=null;
-- 문자열 접합
select ename||'의 1년 연봉은'||sal||' 입니다!' as 연봉 from emp;
-- union (합집합)
select empno, ename from emp
union
select deptno, dname from dept;
-- union all (합집합 + 교집합)
select empno, ename from emp
union all
select deptno, dname from dept;
-- intersect (교집합)
select empno, ename from emp
intersect
select deptno, dname from dept;
-- minus (차집합)
select empno, ename from emp
minus
select deptno, dname from dept;
select deptno, dname from dept
minus
select empno, ename from emp;
-- projection : 열(컬럼) 추출
-- selection : 행(로) 추출

-- *** DML (Data Manipulation Language) - insert, update, delete
create table emp_dml(
sid number primary key,
name varchar2(12)
);
create table emp_dml2(
sid number primary key,
name varchar2(12)
);
-- insert
insert into emp_dml values (1111,'홍길동');
select * from emp_dml;
insert into emp_dml(sid, name) values(2222,'이순신');
-- null허용컬럼은 insert하지 않으면 null값 insert됨
insert into emp_dml(sid) values(3333);
-- subquery를 이용한 데이터 insert
insert into emp_dml2(sid, name) values(4444,'장화');
insert into emp_dml2(sid, name) values(5555,'홍련');
insert into emp_dml select * from emp_dml2;
-- update
update emp_dml set name='이순삼' where sid=2222;
-- subquery를 이용한 update
update emp_dml set name='박순삼'
where sid=(select sid from emp_dml2 where name='장화');
-- delete
delete from emp_dml where sid=1111;
delete from emp_dml where sid=(select sid from emp_dml2 where name='장화');
commit;

20090216-lsj403.sql

20090216_home-lsj403.sql