*Insert******************************************************************************
insert into dept (deptno, dname, loc)
values(50, 'SYSTEM', 'SEOUL')
insert into dept (deptno, dname, loc)
values(60, 'SYSTEM2', null) => 빼도 상관 없음 NULL 로 채워 진다.
insert into dept
values(80, 'SYSTEM2', null)
alter table mydb
add USER_RE varchar2(10)
/***********************************************************************************
1.
select owner, table_name, tablespace_name
from user_tables;
2.
select owner, table_name, tablespace_name
from all_tables;
3.
select owner, table_name, tablespace_name
from dba_tables;
// dba view는 dba권한을 가지고 있는, 스키마에서만 보는것이 가능하다.
================================================================
//테이블당 제약조건등을 뽑아내보자!
select constraint_name, constraint_type, table_name, r_constraint_name
from user_Constraints
where table_name in ('EMP', 'DEPT')
// 이건....뭐더라?
select c1.constraint_name, c1.constraint_type, c1.table_name, c1.r_constraint_name, c2.column_name
from user_Constraints c1, user_Cons_columns c2
where c1.table_name in('EMP', 'DEPT') and c1.constrain_name = c2.constrain_name
================================================================
1.
insert into dept(deptno, dname, loc) <-- 테이블 명과 컬럼순서
values(50, 'SYSTEM', 'SEOUL') <-- 컬럼순서에 맞춘 데이터내용
// 기본형
2.
insert into dept(deptno, dname, loc)
values(50, 'SYSTEM', null) <-- 마지막 컬럼에 데이터를 넣지 않을 경우
↓↓↓↓ 아래것도 똑같다. ↓↓↓↓
insert into dept(deptno, dname) <-- 데이터를 넣지 않을 컬럼명을 생략
values(50, 'SYSTEM')
3.
insert into dept <-- 테이블의 컬럼의 기본순서대로 넣을땐, 컬럼순서 생략가능
values(50, 'SYSTEM', 'SEOUL')
4.
insert into emp10
select empno, ename, job, sal, deptno
from emp
where deptno = 10
================================================================
1.
update dept
set loc = 'DAEGU', dname = 'BIT
where deptno = 90
2.
update emp
set sal = sal*0.8
where ename = 'KING'
3.
update emp
set sal = sal*1.2
where sal = (select max(sal) from emp) <-- 서브쿼리도 사용 가능
4.
update (select * from emp)
set sal = (select avg(sal) from emp)
where sal = (select max(sal) from emp)
// 이런것도 가능 >_<
=================================================================
//MERGE는 A<=B... 한쪽에서 흡수하는 느낌?
=================================================================
1.
delete from dept
2.
delete from dept
where dept = 90
3.
delete from (select * from dept)
where dname in (select max(dname) from dept)
=================================================================
/*Oracle Main DB Objects***************************************************************
VIEW :: Logical Table :: Table 처럼 사용한다. :: Base Table 은 중요해서 보여지지 않고.. VIEW 사용
=> 보안의 목적으로 사용, 간단하게 사용
/-사용예-----------------------------------------------------------------------------------------------
1. 간단하게 사용
create view v_emp_info
as
select e.empno, e.ename, e.sal, e.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
select * from v_emp_infor;
자주 실행되는 쿼리 => View로 만들어 놓는다. => View에서 나오는 Data 는 최신의 Data 이다.
2. 보안의 목적으로 사용
create view v_emp10
as
select *
from emp
where deptno = 10
/------------------------------------------------------------------------------------------------------
or REPLACE :: 기존에 있던 View 에 내용 덮어 쓰기
FORCE | NO FORCE :: Base Table 이 없어도 View 를 만드는 옵션 NO FORCE 가 기본예
aliase :: View Table 의 column 이름을 재정의 해준다.
/---사용예---------------------------------------------------------------------
create or replace view v_emp10(eno, ename, newsal, job,deptno)
as
select empno, ename, sal*12, job, deptno
from emp
where deptno = 10
View에서 DML 은 가능 하다.
Simple View에서 DML 은 가능 하지만 Complex View 는 DML 이 가능하지 않다.(부분적으로 가능)
/----사용예------------------------------------
=> DML 이 가능한 Column 을 보여주는 쿼리
select column_name, updatable, insertable, deletable
from user_updatable_columns
where lower(table_name) = 'v_emp_info'
/----------------------------------------------
select *
from(select * from emp) -> inline View 라 한다. 재사용 하지 않는다.
with read only => View에서 읽기만 가능하도록 설정한다.
with check option => where 절에 벗어나는 범위의 작업을 하지 못하게 한다.
drop View => View Table 삭제 (Data는 삭제 되지 않는다.)
INDEX :: 훨씬 적은 횟수의 비교로 정확한 자리를 찾기 때문에 index 를 사용한다. (트리구조)
: 별도의 저장공간을 차지해서 저장 된다.
: 인덱스를 지워도 테이블 data 가 변환되지 않지만 table 을 지웠을때 index 는 변한다.
: table data 와 index 를 같이 변환해야 하기 때문에 DML 작업속도는 저하 된다.
: 값을 쌍으로도 만들 수 있다. (Single Column) <=> (Composite)
: 인덱스에서 중복값을 받아 들일 지 설정. ( Unique Index) <-> NonUnique Index
: Index 값을 만들때 함수와 같이 만들거나, 컬럼을 같이 만든다.
: - 비교가 자주 이루어지는 방법으로 Index 를 만든다. Column data <=> Function-Based
: Primary Key 를만들면 Index 가 자동생성 ( Automatic created)
: 사용자가 Index를 만듦 (User Create)
/---사용예------------------------------------------------------------------------------------------
create [unique] index ndx_emp_ename // emp table 에 ename 을 index로 만들어 준다.
on emp (ename)
/----------------------------------------------------------------------------------------------------
/-Index 가 생성되어있는지 확인 하는 쿼리-------------------------------------
select index_name, table_name
from user_indexes
where table_name = 'EMP'
/--------------------------------------------------------------------------------
Index 삭제
= drop index index_name;
USER_INDEXES
USER_IND_COLUMNS :: INDEX 가 어느 COLUMN 에 걸려 있는지 알고 싶을 때 사용
INDEX 를 모든 COLUMN 에 만드는 것은 좋지 않고 where 절에 자주 나오는 COLUMN 에 대해서 INDEX를 만드는 것이 좋다. 또 넓은 범위에 대해 조건을 찾는 쿼리에는 INDEX 를 만드는 것은 좋지 않다. 반대로 적은 범위에, 한행을 찾아 오는 쿼리는 INDEX 를 만드는 것이 좋다.
BIG MAP INDEX :: 성별 같은 범위가 크지만 자주 쓰이는 쿼리에 만드는 INDEX
SEQUENCE :: 어떤 고유한 NUMBER 을 넘겨주는 객체
: 한번 만들어 주면 규칙에 맞게 값을 증가 한다.
/----------사용예----------------------------------------------------------------
create sequence ord_no_seq
start with 10// sequence 를 통해 나오는 첫 번째 숫자가 뭐가 될지 결정 default 1
increment by 2 // 숫자를 하나 더 줄때 얼마나 더해서 줄까 결정
maxvalue // 얼마 까지 증가 하는지 결정
minvalue // 얼마 까지 줄어 들지 결정
cycle // 상한선 까지 갔을 때 그 다음 사이클을 결정할
cache // 미리 계산해서 cache 에 저장해 놓는다.
select ord_no_seq.nextval (증가되는 숫자가 나오는 스톡홀럼)
from dual
select ord_no_seq.currval (증가되는 숫자가 나온 마지막이 나오는 스톡홀럼)
from dual
insert into order(ordeno, ........)
values(ord_no_seq.nextval, .....)
/---------------------------------------------------------------------------------
SYNONYM :: 객체에 별명을 붙여 주는데 객체처럼 저장 시켜 준다.
/--------사용예------------------------------------------------------------
create [public] synonym employee for emp;
- public 는 전체 사용자가 이름을 쓸 수 있게 해준다.
- Coding 의 편의 를 위해서 많이 사용한다.
/***********************************************************************************
/*Data Control************************************************************************
Database User
cretate user bit identified by bitbit
grant 권한이름 to 사용자
grant create sseion to bit => system 이 줄 수 있음
alter user bit identified by bit12; // 암호 변경
System Privileges :: 이미 만들어진 객체와 연결된 권한 : 객체권한
:: 객체와 관계 되지 않는 권한 : system 권한
- CREATE SESSION // system 권한
- CREATE TABLE // system 권한
- SELECT ANY TABLE // system 권한
- EXECUTE ANY PROCEDURE
- DROP ANY TABLE
/--------사용예-------------------------------------
grant create session, select any table, crate table
to bit, …
부여했던 권한 제거
revoke create session from bit;
----------------------------------------------------
Object Privileges
grant select ON emp to bit
grant all on emp to bit
rovoke all on emp from bit
Role :: 권한의 묶음.
-사용자를 그룹으로 묶어서 관리 한다.
select * from user_role_privs // 사용자가 가지고 있는 권한 보기
/***********************************************************************************
*정리 #1 **************************************************************************
select empno, ename
from emp
where deptno = 10
order by => 순서만 다시 지정
/**********************************************************************************
/*스키마 안에 있는 테이블이 뭐가 있는지 찾는 방법********************************************
select *
from tab => (tap) 테이블 구조 목록을 가지고 옮(sys 의 정보)
oracle 모든 구조적 정보를 가지고 있는 계정 sys
/**********************************************************************************
/* Functions (단위 프로그램) = 빠르고 편리하게 사용******************************************
built-in functions - oracle 에서 미리 정의한 함수
- 실행방식에 따라 분류
+ single-row functions - 한 행당 한번씩 실행되는 함수
- 파라미터에 따라서 분류(데이터 타입, 리턴 타입)
- number functions
- character functions
- date functions
- conversion functions - 데이터 변환 함수 ( num -> char , char -> num)
- etc functions
- group functions - 한 그룹당 한번이 적용 되는 함수(한 그룹은 여러개의 행으로 구성) - 통계정보
user-defined function
/**********************************************************************************
/* Data Type 사용 예******************************************************************
SQL> desc emp;
이름 널? 유형
------------------- ------------ ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2) =>99999.99
DEPTNO NUMBER(2)
/**********************************************************************************
/char 와 varchar2의 차이***************************************************************
char(8) => 고정 길이 자릿수 (fixed length character)
'aaa' => 파일에 적을 때 Space 로 채워서 최대 자릿수 까지 data file 에 저장
varchar2(8) 변형 길이 자릿수(variable length)
'aaa' => 파일에 적을 때에도 자릿수 만큼만 저장공간을 사용해여 저장
>> varchar2 가 문제를 일으킬 수 있는 환경
data 를 수정 할 때 저장공간에 있는 data 보다 큰값이 수정 되는 경우 다른 공간에 기록 되는 데 다른
블록에 저장 해야 하는 경우 select 시 수정되기 전에 있는 블록의 data 를 찾아 가서 수정 값이 저장 되어
있는 블록으로 이동 한다.
/**********************************************************************************
/*날짜와 관계된 데이터 타입*************************************************************
1 select hiredate
2* from emp
3 /
HIREDATE
-----------
80/12/17 => default 날짜 표시
81/02/20 (날짜와 관계된 모든 data 를 가지고 있지만 default 만 표시 되고 필요시 더 보여준다.)
81/02/22
81/04/02
/**********************************************************************************
/*Number Functions******************************************************************
abs(n) => 절대값
ceil(n) => 올림 / 내림
floor(n) => 버림 / 소수점 여부
mod(m, n) => 나머지
power(m,n) => 승수
round(m,n) => 반올림
trunc(m,n) => 버림(자리수 지정 버림)
sign(n) => 주어진 숫자가 음수 인지 양수 인지 0 인지 판별
select round(42.195, 1)
from dual =>(함수를 쓸 수 있는 table = 누구나가 접근 가능한 특별 table)
/**********************************************************************************
/*Character Functions*****************************************************************
concat(s1, s2) => 연결 연산자 (s1, s2) 문자열을 연결 시켜 준다.
initcap(s) => 첫 번째 글자만 대문자로 바꾸어 준다.
lower(s) => 문자열 소문자로 바꿈
/사용예-----------------------------
select *
from emp
where lower(job) = 'analyst'
/-----------------------------------
upper(s) => 문자열 대문자로 바꿈
lpad(s1, n, s2) => 문자열을 특별한 문자로 자릿수 만큼 왼쪽으로 채워줌
rpad(s1, n, s2) => 문자열을 특별한 문자로 자릿수 만큼 오른쪽으로 채워줌
ltrim(s, c) => 특별한 쓰레기 문자를 왼쪽부터 검색 해서 지워줌(처음으로 아닌 문자 이후 멈춤)
rtrim(s, c) => 특별한 쓰레기 문자를 오른쪽부터 검색해서 지움
chr(n) => 아스키 값이라 생각 하고 문자로 바꿔줌
replace(s, p, r) => s에서 p를 찾아서 r로 바꾸어 준다.
substr(s,m,n) => s 문자열에서 m 번째에서 n 개의 문자를 가져와라
translte(s,from,to) => s 문자열에서 from 에 있는 캐릭터를 to 에 있는 캐릭터로 바꾸어 준다.
ascii(s) => 문자열을 아스키 코드값으로 변환해줌
instr(s1,s2,m,n) => s1에서 s2 문자열을 찾아서 몇 번째 있는지 포지션 넘버 리턴
=> 부분 문자열이 여러개 있을 경우 n 값에 있는 문자열 리턴
length(s) => 길이를 나타내 주는 함수
/**********************************************************************************
/*Date Functions ********************************************************************
sysdate => 현재 시스템의 날짜와 시간을 알려줌
/---사용예-------------------------------------------------
select to_char(sysdate, 'YYY-MM-DD day HH:MI:SS')
from dual
/-----------------------------------------------------------
add_month(d,n) => 어떤 날짜에 달(month) 로 환산 해서 n만큼 더해 준다.
/-사용예--------------------------
select add_months(sysdate, 5)
from dual
/---------------------------------
last_day(d) => 오늘 날짜의 마지막 일을 알려준다.
month_betwen(d1, d2) => 두 날짜의 차이를 알려준다. (단위는 월(month) 이다.)
new_time(d, z1, z2) => 날짜를 주면서 z1에 소속된 날자를 z2의 날짜로 환산해서 알려줌
next_day(d, day) => 오늘이 지나고 나서 처음으로 똑같은 요일이 오는 day 를 알려준다.(요일 정보로)
round(d, fmt) => 날짜를 버림( 1일이 된다.)
trunc(d, fmt) => 날짜를 반올림
/**********************************************************************************
/*Conversion Functions ***************************************************************
to_char(d, fmt) fmt = format model
/---사용예------------------------
select to_char(sal, '9,999') => 9는 숫자가 들어가는 자리(다른 문자는 들어오지 못한다.) or 0(앞자리채움)
from emp
/---------------------------------
to_char(n, fmt)
to_date(s, fmt)
to_number(s, fmt)
/--사용예-------------------------------------
select sal, to_number('1,234', '9,999')
from emp
/----------------------------------------------
/**********************************************************************************
/*Format Elements for Date************************************************************
/----------사용예--------------------------------------------------
1 select to_char(hiredate, 'YYYY-MM-DD'), hiredate
2* from emp
SQL> /
TO_CHAR(HI HIREDATE
---------- ---------------
1980-12-17 80/12/17
1981-02-20 81/02/20
1981-02-22 81/02/22
1981-04-02 81/04/02
1 select to_char(hiredate, 'day'), hiredate
2* from emp
SQL> /
TO_CHA HIREDATE
------ --------
수요일 80/12/17
금요일 81/02/20
/--------------------------------------------------------------------
/**********************************************************************************
/*RR Format ***********************************************************************
RR => 현실에 가까운 날짜로 해석하기 위해서 생겨남 (기본으로 RR 을 사용하는 것이 나음)
/**********************************************************************************
/*Miscellaneous Functions**************************************************************
/---DECODE 사용예-------------------------------------------
select ename, sal, deptno,
decode(deptno, 10, sal*1.1, 20, sal*1.2, sal)
from emp
=>>조건 하나, 활용 범위 적음 case 가 사용범위는 많음
/---------------------------------------------------------------
/**********************************************************************************
/*JOIN *****************************************************************************
select empno, ename, job, emp.deptno, dname
from emp, dept
where emp.deptno = dept.deptno (JOIN 조건) => 없으면 Cartesian Product 발생
order by 1
//소속을 밝힘
select emp.empno, emp.ename, emp.job, emp.deptno, dept.dname
from emp, dept
where emp.deptno = dept.deptno
order by 1
//별칭을 줌
select e.empno, e.ename, e.job, e.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
order by 1
//Equijoin
select e.empno, e.ename, e.job, e.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno // Equijoin
order by 1
//ANSI 용법 - natural join
select *
from emp e natural join dept d => natural :: 두 table 에 공통된 컬럼이 있다고 가정한다.
select *
from emp e join dept d using(deptno) => using 에 있는 table 의 똑같은 칼럼이 있다고 가정.
/--같은 이름의 컬럼이 있어야만 가능---------------------------------------------------------------------
/--같은 이름이 없어도 가능 :: 모든 종류의 것을 다 표현 할 수 있다.---
select *
from emp join dept d on(e.deptno = d.deptno)
/**********************************************************************************
/*Non-equijoin***********************************************************************
select empno, ename, sal, grade
from emp, salgrade
where sal between losal and hisa
/---ANSI 용법----------------------------------------------------------------
select empno, ename, sal, grade
from emp join salgrade on(where sal >= losal and sal <= hisal )
/------------------------------------------------------------------------------
모든 부서의 부서번호, 이름, 위치 그리고 혹시 해당 부서에 소속된 사원이 있다면 사원의 이름까지 보여주기
select d.deptno, d.dname, d.loc, e.ename
from dept d, emp e
where d.deptno = e.deptno(+) => Outer Join - left Outer Join = 비중을 두어 비중을 둔 쪽 data 출력
order by 1
/--ANSI 용법---------------------------------------------------------
select d.deptno, d.dname, d.loc, e.ename
from dept d left outer join emp e on(d.deptno = e.deptno)
order by 1
/----------------------------------------------------------------------
/**********************************************************************************
/*Self Join***************************************************************************
select e.empno 사원번호, e.ename 사원이름, m.empno 매니저번호, m.ename 매니저이름
from emp e, emp m
where e.mgr = m.empno(+)
사원번호, 사원이름, 급여, 급여등급, 부서번호, 부서이름 보여주기
select e.empno, e.ename, e.sal, s.grade, e.deptno, d.dname
from emp e, salgrade s, dept d
where e.sal between s.losal and s.hisal and e.deptno = d.deptno
/--ANSI---------------------------------------------------------------------------
select e.empno, e.ename, e.sal, s.grade, e.deptno, d.dname
from emp e join salgrade s on(e.sal between s.losal and s.hisal)
join dept d on(e.deptno = d.deptno)
/-----------------------------------------------------------------------------------
/**********************************************************************************
/*Aggregate Function******************************************************************
count() => 각 그룹에 속하는 행의 개수, 값의 개수를 찾을 때 사용
sum() => 숫자 값을 합할 때 사용 (합계)
avg => 평균값을 구할 때 사용 (평균)
max() => 특정 컬럼의 큰값을 찾음
min() => 특정 컬럼의 작은 값을 찾음
stddev() => 표준편차에 해당함
variance() => 분산에 해당함
/--사용예-----------------------------------------------------------------
1 select min(ename), max(ename), sum(sal), avg(sal)
2* from emp
SQL> /
MIN(ENAME) MAX(ENAME) SUM(SAL) AVG(SAL)
---------- ---------- ---------- ----------
ADAMS WARD 29025 2073.21429
1 select sum(sal), avg(sal), sum(distinct sal), avg(distinct sal)
2* from emp
3 /
SUM(SAL) AVG(SAL) SUM(DISTINCTSAL) AVG(DISTINCTSAL)
-------- ---------- ---------------------- ----------------------
29025 2073.21429 24775 2064.58333
/-------------------------------------------------------------------------
/**********************************************************************************
/*GROUP BY Clause*******************************************************************
/--------사용예---------------
1 select deptno, sum(sal), avg(sal)
2 from emp
3* group by deptno => 그룹 생성
SQL> /
DEPTNO SUM(SAL) AVG(SAL)
-------- ---------- ----------
10 8750 2916.66667
20 10875 2175
30 9400 1566.66667
/---------------------------------
/**********************************************************************************
/*Incorrect Example for GROUP BY******************************************************
select jop, ename, sum(sal)
from emp
group by jop;
=> 대표값을 찾을 수 없기 때문에 Error Message
/**********************************************************************************
/*HAVING Clause********************************************************************
1 select deptno, job, sum(sal), avg(sal)
2 from emp
3 group by deptno, job
4* having sum(sal) > 5000 => where 절에서는 arregate function 을 사용할 수 없다.
DEPTNO JOB SUM(SAL) AVG(SAL)
--------- --------- ---------- ----------
20 ANALYST 6000 3000
30 SALESMAN 5600 1400
/**********************************************************************************
/*ROLLUP, CUBE Operators*************************************************************
/-----사용예-------------------------------------------
1 select deptno, job, sum(sal), avg(sal)
2 from emp
3* group by rollup (deptno, job)
4 /
DEPTNO JOB SUM(SAL) AVG(SAL)
--------- --------- ---------- ----------
10 CLERK 1300 1300
10 MANAGER 2450 2450
10 PRESIDENT 5000 5000
10 8750 2916.66667
20 CLERK 1900 950
20 ANALYST 6000 3000
20 MANAGER 2975 2975
20 10875 2175
30 CLERK 950 950
30 MANAGER 2850 2850
30 SALESMAN 5600 1400
30 9400 1566.66667
29025 2073.21429
1 select deptno, job, sum(sal), avg(sal)
2 from emp
3* group by cube (deptno, job)
QL> /
DEPTNO JOB SUM(SAL) AVG(SAL)
--------- --------- ---------- ----------
29025 2073.21429
CLERK 4150 1037.5
ANALYST 6000 3000
MANAGER 8275 2758.33333
SALESMAN 5600 1400
PRESIDENT 5000 5000
10 8750 2916.66667
10 CLERK 1300 1300
10 MANAGER 2450 2450
10 PRESIDENT 5000 5000
20 10875 2175
20 CLERK 1900 950
20 ANALYST 6000 3000
20 MANAGER 2975 2975
30 9400 1566.66667
30 CLERK 950 950
30 MANAGER 2850 2850
30 SALESMAN 5600 1400
/--------------------------------------------------------
/**********************************************************************************
/*Subquery**************************************************************************
select *
from emp
where deptno=(select deptno from emp where ENAME = 'SMITH') => Subquery, innerquery
Select 문장만 Subquery 가 될 수 있다.
/***********************************************************************************
/*Single Row Subquery*****************************************************************
Subquery 문장을 실행 시켜서 결과가 하나 일 때
/***********************************************************************************
/*Multiple Row Subquery***************************************************************
Subquery 문장의 실행 결과가 둘 이상일 때
select *
from emp
where deptno in (10, 20)
select *
from emp
where sal >all(any) (select avg(sal) from emp group by deptno)
/***********************************************************************************
/*Subquery in the From Clause**********************************************************
select *
from (select * from emp)
where sal >all(any) (select avg(sal) from emp group by deptno)
select *
from (select * from emp where deptno = 10)
select *
from (select * from emp where deptno = 10)
/***********************************************************************************
/*Subquery in the Having Clause*********************************************************
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > (select avg(sal) from emp where deptno =20)
select empno, ename, sal - (select avg(sal) from emp)
from emp
/-----부서별 최고 금액 출력------------------------------------------------------------
select e.deptno,d.dname, e.ename, e.sal
from emp e, (select deptno, max(sal) msal
from emp
group by deptno) s, dept d
where e.sal = s.msal and e.deptno = s.deptno and e.deptno=d.deptno
/
DEPTNO DNAME ENAME SAL
------ -------------- ---------- ----------
10 ACCOUNTING KING 5000
20 RESEARCH SCOTT 3000
20 RESEARCH FORD 3000
30 SALES BLAKE 2850
/-----------------------------------------------------------------------------------------
/***********************************************************************************
/*Set Operator***********************************************************************
select ename form emp
union
select dname from detp
select ename, sal from emp
union
select dname, null from dept
select deptno, job, avg(sal), sum(sal)
from emp
group by deptno, job
union
select deptno,null, avg(sal), sum(sal)
from emp
group by deptno
* order by 1
> /
DEPTNO JOB AVG(SAL) SUM(SAL)
------- --------- ---------- ----------
10 CLERK 1300 1300
10 MANAGER 2450 2450
10 PRESIDENT 5000 5000
10 2916.66667 8750
20 ANALYST 3000 6000
20 CLERK 950 1900
20 MANAGER 2975 2975
20 2175 10875
30 CLERK 950 950
30 MANAGER 2850 2850
30 SALESMAN 1400 5600
30 1566.66667 9400
/***********************************************************************************
/*Hierarchical Query*******************************************************************
조직도를 쿼리로 그리는 방식
select level, ename => 모든 table 이 접근 가능한 Recode
from emp
start with mgr is null =>root 를 찾을 조건
connect by prior empno = mgr => level 연결 조건
order by level
LEVEL ENAME
------ ----------
1 KING
2 JONES
2 CLARK
2 BLAKE
3 SCOTT
3 ALLEN
3 MARTIN
3 MILLER
3 JAMES
3 TURNER
3 WARD
3 FORD
4 ADAMS
4 SMITH
1 select rpad(' ',level*3, ' ') || ename
2 from emp
3 start with mgr is null
4* connect by prior empno = mgr
5 /
RPAD('',LEVEL*3,'')||ENAME
-------------------------------------------
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
/***********************************************************************************
/*Substitution Variables****************************************************************
select *
from emp
where upper(job) = upper('&job')
select &select
from emp
select *
from emp
where deptno = &&deptno // 어딘가에 기억을 했다가 다시 실행 시 물어 보지 않음
&deptno, &&deptno
1. deptno 라는 치환 변수 값이 저장 되어 있는지 확인
2. 있다면, 물어보지 않고 그냥 사용한다.
3. 없다면 물어본다.
4. & : 사용자가 입력한 값을 사용하고 그냥 버린다.
&&: 사용자가 입력한 값을 사용하고 값을 저장한다.
define deptno => 치환변수를 보여준다.
/***********************************************************************************
/*Create Table************************************************************************
DB 관리 단위
小 Table Space
Block :: I/O 단위 (데이터를 읽어 올 수 있는 단위)
Extent :: 저장 공간 할당의 단위(table 같은 곳으로 저장 공간을 줄때 사용하는 단위)
Segment :: object => talble = segment :: 저장 공간이 필요한 객체
ex) emp segment, dept segment (View 는 객체지만 Segment 는 아님)
반드시 하나의 table은 하나의 segment 에 소속 되어 있어야 한다.
大 Table Space : 테이블이 만들어 지는 공간 => file 의 모임
기본적으로 만들어 지는 Table Space => System TS (모든 구조적인 정보, DB Server 정보 가지고 있음)
(Data Dictionary + User Data)
User Data 는 새로운 Table 을 만들어서 활용
Undo TS
Temporary TS
/***********************************************************************************
/*Create Table************************************************************************
create table myemp
( empno number(4),
ename varchar2(12)
)
or
create table scott.myemp
create table bit.myemp(특정 스키마에 객체를 만들 경우 소속을 명시적으로 표시 한다.)
rowid : 행의 주소 값 ( 객체번호:6, 상대 파일번호:3, 블록넘버:6 행의주소:3)
rownum : 행의 개수
select rownum, empno, ename
from emp
where rownum < 6
create table myemp
(
empno number(4) constraint myemp_name_nn not_null,
no number(4) constraint myemp_name_nk unique
SSN1 char(6),
SSN2 char(7),
deptno number(2) constraint myemp_deptno_fk
references dept(deptno), //dept = referenced table, parent table
constraint myemp_SSN1_SSN2_uk unique(SSN1, SSN2)
constraint myemp_empno_pk primarykey(empno)
constraint myemp_deptno_fk
foreign key(deptno)
references dept(deptno)
on delete set null
comm number(4,2) constraint myemp_comm_ck
check(comm in ( 10, 12.5, 15, 17.5, 20)) // 비교식
or
comm number(4,2) constraint myemp_comm_ck
check(comm between 10.25 and 30.00)) // 비교식
)
create table emp10
as
select empno, ename, job, sal, deptno
from emp
where deptno = 10
create table emp10
as
select empno, ename, job, sal*12 newsal, deptno
from emp
where deptno = 10
create table emp10 ( empno, empname, job, sal, deptno)
as
select empno, ename, job, sal*12, deptno
from emp
where 0 = 10 //컬럼 구조만 복사 data 는 복사 하지 않음
/***********************************************************************************
"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."