[SQL] 오라클문법

1.DataBase:관련된 자료들을 정리해서 모아둔 것.

Table:자료의 묶음.

Oracle DB:oracle SQL은 무한대 .DB는 유일하게 하나 cf>access

tablespace로 분류될 뿐.

field = attribute = col --> 세로줄:col명

record = entity = row --> 가로줄:row명

schema : table의 구조



2.관계형 DB

DB의 모델

HDB:계층DB

NDB:네트워크 DB

RDB:관계형 DB ex>oracle RDBMS(관계형db엔진), MS-SQL



* ORACLE제품

- db제품 : ORACLE ENTERPRISE SERVER제품(RDBMS): 기업용 NT

ORACLE WORKGROUP SERVER제품:부서용 NT

PERSONAL ORACLE WINDOW 95:교육용 WIN95

- 미들웨어 제품 : DB와 CLIENT를 연결시켜주는 제품 SQL*NET 2.2

- CLIENT TOOL 제품 : POWER OBJECT - 그룹단위의 프로그램 개발 TOOL

DEVELOPER/2000-전사단위의 프로그램 개발 TOOL(FORMS,REPORT,GRAPICS)

* DATA BROWSE:검색TOOL

* SQL*PLUS:SQL명령을 구현할 수 있는 TOOL



3.SQL(STRUCTURED QUERY LANGUAGE)

관계DB를 처리하기 위해 고안된 언어로 , 독자적인 문법을 갖는 DB표준언어이다(ISO에서 지정)



4.SQL의 세가지 기능

DATA DEFINETION LANGUAGE (DDL):데이타 정의 기능, TABLE,VIEW등을 정의하는 기능

DATA MANIPULATION LANGUAGE(DML):데이타 조작기능,COL과 ROW에 대한 INSERT,DELETE,UPDATE하는 기능

DATA CONTROL LANGUAGE(DCL):데이타제어기능 유저관리기능



5.SQL*PLUS란?

SQL언어를 구현하여 오라믈 RDBMS를 관리할 수 있는 오라클사의 CLIENT TOOL 제품명이다.

6.명령

SQL명령: 모든 명령의 끝은 ;으로 끝나야 한다.

여러줄에 입력할 수 있다.

가장 최근의 SQL명령은 SQL버퍼에 남아있다.

실행하려면 RUN이나 /를 누른다.



SQL*PLUS명령: 모든 명령의 끝은 ENTERKEY에 의해 끝난다.

명령의 끝에 ;을 붙여도 되고 생략해도 된다.

LIST(L):버퍼의 내용을 보여준다.

EX> TTITLE CENTER '안녕하세요' ----> 머리말

TTITLE OFF ----->버퍼에 남아있는 머리말내용을 없애준다.





* drop user:user삭제 ex>drop user 유저명;

drop user 유저명 cascade; //table 존재할 경우 유저가 object를 소유하고 있으면 유저를 삭제할 수 없다.이때 cascade를 사용하면 유저와 그 유저가 소유하고 있는 모든 object를 삭제할 수 있다.



* create user:user생성

ex> Create user seo identified by smh default tablespace user_data;



* create table: table생성

ex> create table samp(name varchar2(10),age number(3),birthday date);

col명 datatype[참조제약조건],....



* resource의 권한을 갖는 사람이면 누구나 생성할 수 있다.



* datatype의 종류

문자열: char(n);n byte 고정길이 문자data(최대 255자)

varchar2(n); n byte가변길이 문자 data(최대 2000자)

숫자 : number ;38자까지의 정수,실수,숫자

number(n);n byte까지의 정수 숫자 기억

number(p,s); p전체자리수(소수점을 제외한) s소수점이하 자리수

정수나 실수 숫자 기억

ex> 125.46-> number(10,2) 125.479->number(10,2); 125.48(반올림이 된다)125.479->number(10,-1); 120

날짜 : date ; bc 4712 1,1-ad 4712 12,31까지 표현 가능

한글 : 년-월-일, 영어: 일-월(영어로)-년

long : 2Gbyte까지의 자료저장, 단 하나의 테이블에 하나만 만들 수 있다.

index,연산,무결성제약조건등에 사용. 함수나, select시, where조건, order by, group by절에는 사용불가.



- 참조 제약 조건 : primary key모든 key에 존재(기본키),table에서 행과행을 구별할 수 있는 field값NotNull속성 unique속성,index값으로 이용된다.(널값허용X, 중복 불가능, 데이타검색시 이용) unique;중복 불가능

NOT NULL : 반드시 자료를 입력해야 한다.

defalut : 기본값 지정

check : 입력된 자료를 검증 ex>check(in('남자','여자'))

foreign key : 외래 키,종속 키(관계형 데이타베이스주역할)



** table간의 관계 설정 키 **

- table 삭제 : drop table 테이블명

- table에 내용 삽입 : ex> insert into samp values('홍길동',20,'78-12-11');

- 암호 변경 : alter user sys identified by sys;

단 user 명은 바꿀 수 없다. 방법은 아예 user를 삭제시키고 다시 만드는 수밖에 없다. 내 암호를 바꾸고 싶을 때는 굳이 system/manager로 connect 할 필요는 없다. 내 환경에서도 가능.하지만 타인의 암호를 바꿀경우에는 반드시 위에 접속을 해야 됨.

* 모든 user관람 : select * from all_users;

* db에 data 넣기 : commit;

* exit : 자동으로 commit을 실행시켜 준다.



* SQL*PLUS의 편집:

LIST(L) : SQL버퍼의 내용을 보여준다. ex> L : 모든내용

L3 : 세번째 줄.

L2 4 : 두번째 줄에서 4번째

3 : 세번째 줄만

APPEND(A) : 현재 작업라인의 끝에 문자열 추가 EX> 2; 두번째라인으로 POINT를 옮긴 후

A FROM : FROM을 APPEND시킨다.

CHANGE(C) : OLD문자를 새로운 문자로 바꾼다.

C/OLD문자 /NEW문자 ex> C/*/ENAME

DEL : 현재 작업라인을 삭제한다.

INPUT(I) : 현재 작업위치아래에 새로운 라인을 삽입

I 문자열 : 한라인 추가

I : 여러라인 추가.종료시는 CTRL+C.

CLEAR SCREEN : CTRL+DEL

SPOOL : 화면에서 사용한 기록들을 하나의 화일로 저장한다.

@화일명(START화일명) : 메모장의 명령어 이용 -> EDIT명령어로 메모장을 불러낸다.



** ORACLE RDBMS의 제한:

데이타베이스가 포함할 수 있는 테이블수 : 제한없다.

1개의 테이블에 포함되는 ROW수 : 제한없다.

1개의 테이블에 포함할 수 있는 COL수 : 254

* ROW한행에 들어갈 수 있는 최대문자수 : 130,306 BYTE(단,LONG형 FIELD는 제외)

문자열의 최대길이 :255

숫자의 최대길이 : 38

날짜의 최대길이 :BC 4712년1월1일 - AD 4712년 12월 31일

테이블에 붙일수 있는 INDEX수 :제한없다.

테이블과 테이블의 결합수 : 제한없다.

서브쿼리의 하위 단계:255 (EX> SELECT안의 SELECT문)

명칭의 문자수 :30 BYTE



*user의 생성 삭제 수정-user에 대한 관리는 DBA권한을 갖은 사람만이 할 수 있다.

(생성형식)

create user 유저명 identified by 패스워드 default tablespace 테이블스페이스명

[temporary tablespace 테이블 스페이스명];



** tablespace종류 **

- system : sys 나 system이 관리하는 table이나 view등이 저장되는 tablespace

- rollback_data : backup

- temporary_data : 임시 작업 장소

- user_data : 임의로 유저가 만드는 tablespace

- 기타_data



** 제약조건지정 **

1.필드명지정시 직접지정: table생성시

deptno number(2) references dept(deptno);//foreign key 인 경우

2.constraint를 이용한 지정방법:table생성시

3.alter를 이용한 지정방법:table생성후에 제약조건 추가



constraint 이름 제약조건의 종류(필드명) [references table명(필드명)[on delete cascade]]:

(primary key,check,unique,foreign key) cf.default 허용 안된다. not null허용 안된다. 두개는 반드시 table생성시 지정.

constraint emp_empno_pk primary key(empno);

constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete cascade;

constraint emp_sal_ck check(sal > 0 );



* desc : 만들어 놓은 테이블의 구조를 알아보고 싶을 때 ex> desc emp;



* foreign key의 영향으로 data의 입력이 이루어지지 않을 경우 foreign key 를 잠시 숨겼다가 살리는 방법.

key숨기기 -----> alter table emp disable constraint emp_deptno_fk;

다시 살리기 ---> alter table emp enable constraint emp_deptno_fk;



* table에 있는 내용을 삭제시키고자 할 경우

1. 모든 내용을 삭제할 경우 ex) delete from emp;

2. 조건에 해당하는 내용만 삭제할 경우 ex) delete from emp where deptno = 10;

* table잘못 만들었을 경우 수정방법 (예를 들어 check key를 잘못 만들었을 경우)

1.먼저 check key를 삭제시킨다.

alter table emp drop constraint emp_deptno_ck;

2.다시 새롭게 만들어 추가시킨다.

alter table emp add constraint emp_deptno_ck check(deptno in('10','20','30','40'));

alter table emp add constraint emp_sal_uq unique(sal);

alter table emp add constraint emp_ename_pk primary key(ename);(record나 필드의 자료가 없는 경우)

# col의 datatype을 수정할 수 있다 .

# col의 폭을 줄이거나 늘릴 수 있다.(record나 필드의 자료가 들어 있는 경우)

# col의 datatype을 수정할 수 없다 .

# col의 폭은 늘릴 수는 있으나 줄일 수는 없다.

(1.col의 이름은 변경할 수 없다. 2.col을 삭제할 수 없다. 3.col의 추가는 가능하다.단 NOT NULL인 col은 안된다.)

형식> alter table 테이블명 add

modify

drop

enable-- constriant에서만 사용

disable

ex> alter table emp add (ename varchar2(14) not null);

ex> alter table emp modify(deptno varchar2(10));



* 파라미터를 이용한 query

&1 - &9를 이용한 매개변수 사용.

ex> select empno, ename, sal from emp where deptno = 10 : 부서코드가 10인것만 출력.

'10'대신 edit박스에 '&1'이라고 써주면 실행시 1의 값을 물어본다. 혹은 start 화일명 10 이라고 즉석에서 부여할 수 도 있다. 또는 1 이라는 값대신 user가 알아볼 수 있는 값을 accept를 이용해서 나타낼 수도 있다.

ex> accept va prompt '부서코드를 입력하세요'

select empno, ename, sal from emp where deptno = &dept;



* 함수

1.산술함수

sin() cos() tan() sinH() cosH() tanH() : radian값을 이용

ex> 30' 일 경우 sin(30* (3.14.../180));

exp(n) : 지수값 e를 밑으로 하는 e의 n승 값;

LN(n):자연 log값 밑수 e 지수 n

SQRT(n): 루트n

power(m,n): m의 n승

floor(n): n보다 작은 정수값들중에서 최대값

ceil(n): n보다 큰 정수값들중에서 최소값

greatest(m,n): 두 수중에서 큰것

least(m,n);두 수중에서 작은 것

TO_NUMBER('숫자형 문자열'):문자를 숫자로 변환

round(m,n):m값을 n+1의 자리에서 반올림하여 n자리까지 표시

trunc(m,n):절삭한다 즉 반올림을 허용하지 않는다.

NVL(col명,값): col의 값이 null를 가질때 값을 가진다.

ex> 100+NULL은 NULL이 되기 때문에 이러한 문제점을 해결할 때 사용한다.

select ename,sal,comm,sal+nvl(comm,0) from emp;-->sal값과 comm값을 더하는데comm값이 null값이면 0값을 취해라.



2.문자열 함수

lower():소문자로 변환시켜주는 함수

upper():대문자로 변환시켜주는 함수

Initcap():단어의 첫자만 대문자로 나머지는 소문자로

length():문자열의 길이

substr(문자열,위치,갯수): 해당위치에서 갯수만큼의 문자열을 추출한다.

instr(문자열,찾을 문자열[,위치,찾을위치]):문자열에서 이 문자를 찾아라. return값은 찾은 위치

ex> Instr("abcdabkdoerabjdlfjdg","ab") ; return 1

Instr("abcdabkdoerabjdlfjdg","ab",7,2) ;7번째에서 시작해서 2번째로 나오는 것을 찾아라. k부터시작

user:현재 접속된 사용자를 보여준다.

decode(col명,비교값,취할값,비교값,취할값,....기본값):col값이 비교값이면 취할값으로 대체.

ex> emp테이블에서 부서코드를 이용하여 부서명을 출력하시오?

select decode(deptno,10,'총무부',20,'영업부',30,'전산부','관리부') from dept; -> 나머지는 관리부

ex> 특정한 문자를 포함하고 있는 문자열을 찾아 그 문자열을 출력하고 또한 특정한 그 문자도 출력해보자.

select ename, substr(ename, instr(ename,'s'),1) from emp where ename like '%s%';



3.group함수 : NULL값은 계산되지 않는다.

단일 필드와 함께 사용할 수 없다.

만약 단일 필드와 사용되면 group by절에 반드시 나와야 한다.

sum():합

avg():평균

count():갯수 -> count(*): 리턴되는 레코드의 수를 계산

max():최대값

min():최소값

stddev: 표준편차

variance: 분산

ex> select deptno,sum(sal) from emp; 틀린내용 -> 단위 필드는 group by 필드,필드;라고 지정해야 한다.

ex> select deptno,sum(sal) from emp group by deptno; 맞는내용

* 조건 : where cf>그룹지어진 상태를 조건지울때는 having순서: group by, order by,

* rownum : record번호를 보여준다.



* 날짜함수

TO_CHAR(날짜,'포맷') : 날짜를 문자열로 바꾼다. ex> select to_char(sysdate) from dual;

TO_DATE(문자열,'포맷') : 문자열을 날짜로 ex> select to_date('98-05-07') from dual;

ADD_MONTHS(날짜,n) : 현재 날짜에 n개월수를 더한다.

MONTHS_BETWEEN(날짜1, 날짜2) : 두 날짜의 개월수의 차를 구한다.

LAST_DAY(날짜) : 그 달의 마지막 날짜를 구한다.

NEXT_DAY(날짜, '요일') : 날짜로 부터 다음에 나오는 요일은 며칠인가

ex> next_day(97-10-14, '금요일')

날짜 + n : 날짜에다 n 일을 더한다.



* 입출력 포맷형식

yy : 년도를 2자리로

yyyy :년도를 4자리로

mm : 월을 표시(01-12)

mon : 월을 표시(DES....):약자로 나온다.

month : 월을 표시(1월,2월,....혹은 ,DESEMBER)

d : 일을 표시(주에 대한 일)

dd : 일을 표시 (월에 대한 일)

ddd : 일을 표시 (년에 대한 일)

Q : 분기를 구한다.

DAY : 요일 (월요일)

DY : 요일(월)

HH & HH12 : 시간을 12시간제로

HH24 : 24시간제로

MI : 분

SS : 초

AM & PM & A.M & P.M : 12시간제 일때 오전 오후를 표시

ex) select to_char(sysdate, 'yyyy/mm/dd hh24:mm:dd') from dual;

select to_char(sysdate, 'yyyy/mm/dd day dy q') from dual;



* 접미사로 사용하는 포맷

TH:서수로 표시 ex> 4 -> DD ->4

4-> DDTH -> 4TH

SP:철자로 표시 ex> 4 ->DDSP -> FOUR

SPTH & THSP ex> 4 -> DDSPTH -> FOURTH



* Join

join: table과table간의 결합(많이 사용한다.)

left outer join: 왼쪽 table의 자료는 모두 추출되고 오른쪽 table의 자료는 연결되는 것들만 추출 필드명 = 필드명(+)

right outer join:위와 반대 필드명 (+) = 필드명

inner join:두 table에서 같은 자료가 있는 것들만 join되어 추출 필드명 = 필드명

self join:자신의 table에 결합하는 join alias를 사용한다.

ex> deptno를 Key해서 부서테이블에서 부서명을 가져온는 join예제

select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno;

(Table명옆의 a는 alias기호임).



* Sub Query : 255단계 까지 가능 query(select) 안에 query문이 포함되어 있는 query

ex> 부서코드가 10인 사원만 보고 싶을때.

select * from emp where deptno = (select deptno from dept where deptno = 10);

ex> 회사의 평균급여보다 많은 사원들만 보고 싶을때.

select empno, ename from emp where sal > (select avg(sal) from emp);

ex> 10번부서에서 회사의 평균급여보다 급여가 적은사원만 보고 싶을때.

select empno, ename from emp where deptno = 10 and sal < (select avg(sal) from emp);



* 상관 Query 서로 상관 참조작용하는 Query이다.(table두개를 두고 서로 연속적으로 비교해가는 과정과 유사)

ex> select * from emp a where sal = (select max(sal) from emp where deptno = a.deptno);

ex> select * from emp a where sal > (select avg(sal) from emp where deptno = a.deptno);

ex> 전체직원들 중에서 급여서열 5위까지 출력

select * from emp a where 5 > (select count(*) from emp where sal > a.sal) order by sal desc;



* Union

- union : 합집합

- union all : 공통원소 두번씩 다 포함한 합집합

- intersect : 교집합

- minus : 찻집합

* 쿼리된 결과를 처리

ex> select * from emp where job in (select job from emp where ename = '이순신' union select job from emp where ename = '이미라');

ex> 홍길동과 부서가 같고 직책이 같은 사람은?

- select * from emp where deptno = (select deptno from emp where ename = '강철') and job = (select job from emp where ename = '강철');

- select * from emp where (job, deptno) = (select job, deptno from emp where ename = '홍길동');

- 괄호를 이용할 시는 연속적인 필드 나열 가능

ex> select * from emp where sal > (select max(sal) from emp where deptno = 20);

- select * from emp where sal > all(select sal from emp where deptno = 20);

혹은 any -> all(모든 것중), any(가장 작은 것보다 크기만 하면 된다.)



* query된 결과를 이용한 table의 생성

형식) create table 테이블명 [(col명,col명....)] as select 문장;

ex> create table emp1 as select ename, job, sal from emp;

방법 1> create table emp2(name,pay,bonus) as select ename, pay, pay*1.5 from emp;

즉, 새로운 필드명 다 만들어 준다. 단,필드의 형태를 바꿀 수 있는 것은 아니다. 단지 이름만.

ex> create table emp3 as select * from emp where deptno = 10;



* 두 테이블 합치기

create table emp4 as select deptno from emp union select dname from dept ; ---> union사용



* record 삭제 (delete)

형식) 1.delete from table명[where 조건]

2.truncate table table명 :모든 row를 삭제하여 다시 복구할 수 없다.

* 복구

rollback;



* record삽입(insert)

형식) 1. insert into 테이블명 values(값,.....) 모든 컬럼의 자료를 입력

2. insert into 테이블명(컬럼명,...) values(값,....) 해당 컬럼의 자료만 입력 단 Not null인 컬럼은 반드시 포함해야 한다.

ex> insert into emp1(ename, sal, job) values ('김현주', 200000, '탤런트');

3. insert into 테이블명 select 문장; 쿼리된 결과를 insert 한다.

ex> insert into emp1 select ename, job, salfrom emp where job = '과장';

반드시 기존에 있던(emp1) 테이블과 컬럼이 동일한 것만 입력시킬 수 있다.



* 자료 수정(update)

형식) update 테이블명 set 컬럼명 = 값, 컬럼 = 값,...[where 조건]

ex> update emp set sal = sal * 2 where empno = 7800;