2. 입력값 확인 기능 끔
3. 입력창 메세지 바꿈
4. 구조화 구문
5. 오류 --문자열 크기 작음
2016년 6월 30일 목요일
06day DB Oracle
1. 다중 테이블 INSERT
--서브쿼리의 결과를 1개 이상의 테이블에 입력 할 수 있기 때문에 기존의 데이터베이스에 구축된 많은 데이터를 데이터웨어 하우스로 구축하는 업무에 매우 유용하다
**무조건 INSERT
//빈 테이블 생성
SQL> create table t1
2 as select empno, ename, hiredate
3 from emp
4 where 1 != 1;
SQL> create table t2
2 as select empno, ename, sal
3 from emp
4 where 0=1;
--다중 테이블 insert
SQL> insert all
2 into t1 values (empno, ename, hiredate)
3 into t2 values (empno, ename, sal)
4 select empno, ename, hiredate,sal
5 from emp
6 where deptno=10;
**조건부 INSERT ALL
SQL> insert all
2 when hiredate >= '81/01/01' then
3 into t1 values (empno, ename, hiredate)
4 when sal>3000 then
5 into t2 values (empno, ename, sal)
6 select empno, ename, hiredate, sal
7 from emp;
**조건부 INSERT FIRST
--분류작업에 용의함, 테이블이 상호 배타적임
SQL> insert first
2 when sal <= 1000 then
3 into sal1000 values (empno, ename, sal)
4 when sal <= 3000 then
5 into sal3000 values (empno, ename, sal)
6 else
7 into salmax values (empno, ename, sal)
8 select empno, ename, sal
9 from emp;
**피벗팅(Pivoting) INSERT
---행데이터를 열데이터로 변환
--테이블 생성
CREATE TABLE SALES_DATA
(EMPNO NUMBER(4),
SPRING_SALE NUMBER(9),
SUMMER_SALE NUMBER(9),
AUTUMN_SALE NUMBER(9),
WINTER_SALE NUMBER(9));
INSERT INTO SALES_DATA VALUES (100, 3000, 5000, 6000, 7000);
INSERT INTO SALES_DATA VALUES (200, 4000, 2000, 3000, 5000);
INSERT INTO SALES_DATA VALUES (300, 6000, 3000, 4000, 4000);
INSERT INTO SALES_DATA VALUES (400, 3000, 1000, 5000, 2000);
CREATE TABLE SALES_SEASON
(EMPNO NUMBER(4),
SEASON_CODE CHAR,
SALES NUMBER(9));
--피벗팅 INSERT
SQL> insert all
2 into sales_season values ( empno, '1', spring_sale)
3 into sales_season values ( empno, '2', summer_sale)
4 into sales_season values ( empno, '3', autumn_sale)
5 into sales_season values ( empno, '4', winter_sale)
6 select empno, spring_sale, summer_sale, autumn_sale, winter_sale
7 from sales_data;
2. EXTERNAL TABLE
--데이터베이스에는 테이블에 대한 정의만 저장되고 실제 데이터는 데이터베이스 외부에 저장되는 읽기 전용 테이블. DML을 사용할 수 없다.
--권한부여
SQL> conn system/123456
SQL> grant create any directory to scott;
--외부파일 읽어드릴 폴더이름 지정
SQL> create directory emp_dir as 'c:\oracle'; --디렉토리명을 emp_dir로 설정
--외부 테이블 파일 읽기
SQL> create table extemp (
2 empno number, ename varchar2(10), job varchar2(10), mgr number, hiredate date)
3 organization external
4 (type oracle_loader
5 default directory emp_dir
6 access parameters
7 (records delimited by newline
8 badfile 'BAD_EMP'
9 logfile 'LOG_EMP'
10 fields terminated by ','
11 (empno char,
12 ename char,
13 job char,
14 mgr char,
15 hiredate char date_format date mask "YY/MM/DD")
16 )
17 location('EMP.TXT')
18 )
19 parallel 5
20 reject limit 200;
--예제 주소 파일 읽기
SQL> create table zipcode(
2 zipcode char(7),
3 sido varchar2(6),
4 gugun varchar2(27),
5 dong varchar2(39),
6 ri varchar(67),
7 bunji varchar2(18),
8 seq number(5))
9 organization external(
10 type oracle_loader
11 default directory emp_dir
12 access parameters(
13 records delimited by newline
14 badfile 'BAD_EMP'
15 logfile 'LOG_EMP'
16 fields terminated by ','(
17 zipcode char,
18 sido char,
19 gugun char,
20 dong char,
21 ri char,
22 bunji char,
23 seq char)
24 )
25 location('zipcode.csv')
26 )
27 parallel 7
28 reject limit 200;
--출력
SQL> select * from zipcode
2 where rownum<10; ---불러올 행의 갯수 제한하기
3. 분석 함수
**RANK
--ORDER BY 뒤에 지정된 컬럼을 기준으로 정렬한 뒤, 해당 행에 대한 순위를 계산한다.
SQL> select empno, ename, hiredate, rank() over (order by hiredate) rank
2 from emp;
///PARTITION BY 그룹별 랭킹
SQL> select empno, ename, deptno, sal,
2 rank() over (partition by deptno order by sal desc) rank
3 from emp;
//DENSE_RANK
--동 순위가 있어도 그 다음 순위를 건너띄지 않음
SQL> select empno, ename, deptno, sal,
2 rank() over (order by sal desc) rank,
3 dense_rank() over (order by sal desc) dense_rank
4 from emp;
//CUME_DIST
--최대값 1을 기준으로 순위를 0~1사이의 값으로 표시
SQL> select empno, ename, deptno, sal,
2 rank() over (order by sal desc) rank,
3 cume_dist() over (order by sal desc) cume_dist
4 from emp;
//PERCENT_RANK
--CUME_DIST와 유사하지만, 계산 방법은 약간 다르다
--PERCENT_RANK = (파티션 내 자신의 RANK-1)/(파티션 내의 행 개수-1)
SQL> select empno, ename, deptno, sal,
2 rank() over (order by sal desc) rank,
3 percent_rank() over (order by sal desc) percent_rank
4 from emp;
//NTILE(N)
--파티션 내의 행들을 N개로 분류하여 행의 위치를 표시한다
SQL> select empno, ename, sal,
2 ntile(7) over (order by sal desc) ntile
3 from emp;
//ROW_NUMBER
--파티션 내에 행들에 대하여 차례로 순번을 표시한다
SQL> select empno, ename, sal,
2 row_number() over (order by sal desc) row_number
3 from emp;
4. 향상된 MERGE 문장
--MERGE 문장의 UPDATE 및 INSERT 구문에 WHERE 절을 추가하여, 특정 조건에 만족하지 않는 행들은 INSERT 및 UPDATE가 수행되지 않도록 할 수 있다.
--테이블 생성
SQL> create table emp20
2 (empno number(4),
3 ename varchar2(10),
4 job varchar2(10),
5 sal number(7,2));
--데이터 입력
SQL> insert into emp20(empno, ename, job, sal)
2 select empno, ename, job, 0
3 from emp
4 where deptno = 20;
--데이터 확인
SQL> select * from emp20;
--향상된 merge
SQL> merge into emp20 n
2 using emp o
3 on (n.empno = o.empno)
4 when matched then
5 update set
6 n.ename = o.ename,n.job = o.job, n.sal = o.sal
7 where o.job='CLERK'
8 when not matched then
9 insert (n.empno, n.ename, n.job, n.sal)
10 values (o.empno, o.ename, o.job, o.sal)
11 where o.job='CLERK';
5. 정규 표현식
--정규 표현식은 문자열의 검색 및 조작에 있어서 단순 및 복잡한 패턴을 모두 사용 할 수 있는 방법을 제공해준다.
․ REGEXP_LIKE : 주어진 패턴에 일치하는 문자열을 검색한다.
․ REGEXP_REPLACE : 일치하는 문자를 검색하고 지정된 패턴으로 교체한다.
․ REGEXP_INSTR : 문자열을 검색하고, 해당문자의 위치를 정수값으로 리턴한다.
․ REGEXP_SUBSTR : 정규 표현식에 일치하는 일부 문자열을 리턴한다.
--REGEXP_LIKE
SQL> select * from reg_test
2 where regexp_like(name, '^ste(v|ph)en$');
--REGEXP_INSTR
SQL> select name,
2 regexp_instr(name, '[^[:alpha:]]')
3 from reg_test
4 where regexp_instr(name, '[^[:alpha:]]')>1;
//[ : 표현식의 시작
//^ : NOT
// [:alpha:] : 알파벳
// ] : 표현식의 끝
--REGEXP_SUBSTR
SQL> select regexp_substr(name, '[^ ]+')
2 from reg_test;
--첫 번째 공백이 나타나기 전의 문자열을 리턴
--REGEXP_REPLACE
SQL> select regexp_replace(name, '(.)', '₩1 ')
2 from reg_test;
--각 문자 뒤에 공백을 추가
6
2016년 6월 29일 수요일
05day DB Oracle
1. 뷰
**뷰 만들 권한 부여
**뷰 내용확인
**뷰 테이블처럼 사용
**뷰 생성 ---컬럼명을 지정해야함
**뷰 생성 ---다른방법
**뷰 생성 ---집계함수, 조인문장 포함된 복합뷰
**뷰 삭제
**인라인 뷰 --이름이 없는 뷰 //select문을 괄호안에 씀
**TOP-N 쿼리
//인라인 뷰를 사용함
//컬럼 값 중에서 가장 큰 값 또는 가장 작은 값 n 개를 질의하는 경우
//조인문 포함 top-n쿼리
2. 데이터베이스 객체
**시퀀스 --여러 사용자들이 공유하는 데이터베이스 객체로서 호출 될 때마다 중복되지 않은 고유한 숫자를 리턴하는 객체
//생성
**NEXTVAL, CURRVAL 가상 컬럼 --가상 컬럼을 이용하여 시퀀스 번호를
검색할 수 있다.
//시퀀스의 사용
---시퀀스에 의해 추출되는 번호는 순차적이지만, 시퀀스 번호는 COMMIT 또는
ROLLBACK 명령에 의해 지워지면 재사용 불가함, 공유번호이기 때문에 혼란방지
//시퀀스 변경
--해당 시퀀스의 소유자이거나 ALTER SEQUENCE 권한을 부여받아야 한다.
3. INDEX
---행을 검색할 때 속도를 높이기 위해 Oracle 서버가 사용하는 스키마 객체
--경우에 따라서 데이터베이스가 인덱스를 검색하지 않을 수도 있음
**인덱스 생성
--PRIMARY KEY 또는 UNIQUE 제약조건을 지정하면 해당 컬럼에 고유 인덱스가 자동생성
--사용자가 필요에 따라 특정 컬럼에 별도의 인덱스를 생성
**인덱스 확인
**인덱스 삭제
**primary key로 인덱스 자동생성
**함수 기반 인덱스
--인덱스가 생성된 컬럼이 함수에 입력되어 사용되면 절대 인덱스를 사용할 수 없다. 그래서 함수 기반 인덱스는 WHERE 조건의 함수 또는 연산식 자체를 인덱스로 생성한 것이다.
//권한부여
4. 동의어 --객체에 대한 별칭
//권한 부여
//동의어 사용
//조회
//삭제
5. 사용자 접근 제어
--데이터베이스 보안은 시스템 보안과 데이터 보안으로 분류 할 수 있다. 시스템 보안은 사용자 계정 생성, 암호 변경, 디스크 공간 할당, 시스템 작업 등과 같이 시스템 수준에서의 데
이터베이스 접근 및 사용을 관리하는 것이며 데이터베이스 보안은 데이터베이스 객체에 대
한 사용자들의 접근 및 사용을 관리하는 것이다.
**사용자 생성
**접속권한 부여
**비밀번호 바꿈
**계정 잠금
**유저 조회
6. 객체 권한
--테이블 만든 사람이 권한을 줌
//상대방이 만든 객체에 접근
//권한부여
//재접근
//컬럼권한 부여
//변경
//권한 확인 --주는 쪽
//권한 확인 --받는 쪽
//권한 회수
7. 롤 ---권한을 하나하나 부여하는 것이 아니라 집합적으로 부여함
//롤 생성
//사용자 생성
//사용자 접속
C:\Users\user>sqlplus newuser1/123456
8. 데이터베이스 링크
---데이터베이스 링크를 사용함으로서 얻을 수 있는 가장 큰 장점은 로컬데이터베이스의 사용자들이 원격 데이터베이스내 객체에 접근하면 모든 권한은 해당 객체의 소유자 권한으로 한정된다는 점
//링크시킬 데이터생성
//원격접속 확인
C:\Users\user>sqlplus scott@remote
//링크생성
9. ROLLUP과 CUBE
--부분 집계에 사용되는 GROUP BY 절에 ROLLUP과 CUBE를 사용하면 다양한 통계 자료를
출력 할 수 있다
**ROLLUP
--ROLLUP은 GROUP BY에 의해서 출력되는 부분집계 결과에 누적된 부분 집계를 추가
**CUBE
---CUBE는 뒤에 기술된 컬럼들에 대한 모든 조합을 그룹화하고, 각각의 그룹에 대하여
GROUP BY에 의해 부분집계를 수행
10. GROUPING 함수
--GROUPING 함수는 ROLLUP과 CUBE를 사용했을 때, 각각의 행들이 부분집계에 참여했는
지를 표시해준다
11. GROUPING SETS
--GROUPING SETS는 GROUP BY를 확장한 것으로서 GROUPING SETS을 이용하면 사용자가 원하는 컬럼들로 구성된 그룹을 만들 수 있다
12. 복수 컬럼 서브쿼리
**Pairwise 비교
**Nonpairwise 비교
13. 상관관계 서브쿼리
---서브쿼리가 메인쿼리의 컬럼을 참조하도록 되어 있는 서브쿼리
**EXISTS
--서브쿼리의 결과가 한 개의 행이라도 존재하면 조건은 참이 되며 반대로 한 개
의 행도 존재하지 않으면 조건은 거짓
14. WITH
-- 서브쿼리를 블럭으로 선언하여 문장의 다양한 위치에서 활용
15. 계층형 쿼리
---위 그림과 같은 트리 구조의 데이터를 검색하는 방법이 계층형 쿼리
**LEVEL
--계층형 쿼리에서 사용 할 수 있는 가상 컬럼 LEVEL은 트리 구조에서 계층을 나타낸다
16. 트리 구조의 노드(Node) 및 브랜치(Branch) 제거
//하향식 전개된 계층형 쿼리 결과에서 'FORD'를 제외하고 출력하려면
WHERE 절에서 제거할 노드를 제외 할 수 있는 조건을 기술해주면 된다
//트리 구조에서 'FORD'의 브랜치를 제거하고 계층형 쿼리를 진행하고자 하는 경우에는 CONNECT BY씀
17
SQL> conn system/(비밀번호)
SQL> grant create view to scott;
**뷰 생성
SQL> conn scott/tiger
SQL> create or replace view empvu10
2 as select empno, ename, sal from emp where deptno=10;
**뷰 사용
SQL> select * from empvu10;
SQL> desc user_views;
SQL> select view_name, text from user_views;
SQL> select empno from empvu10;
SQL> select empno from empvu10 where empno=7782;
SQL> create view salvu10
2 as select empno, ename, sal*12 //에러남
3 from emp where deptno=10;
SQL> create view salvu10
2 as select empno, ename, sal*12 year_sal //컬럼명 지정함
3 from emp where deptno=10;
SQL> create view salvu20 (id, name, year_sal)
2 as select empno, ename, sal*12
3 from emp where deptno=10;
SQL> create view dept_avg_vu (name, avg_sal)
2 as select d.dname, avg(e.sal)
3 from dept d, emp e
4 where d.deptno = e.deptno
5 group by d.dname;
SQL> select * from dept_avg_vu;
SQL> drop view salvu20;
SQL> select * from (select empno, ename, sal from emp where deptno=10);
SQL> select d.dname, e.maxsal
2 from dept d, (select deptno, max(sal) maxsal
3 from emp
4 group by deptno) e
5 where d.deptno = e.deptno; //인라인 뷰를 조인에 활용함
SQL> select rownum, ename, sal
2 from (select ename, sal
3 from emp
4 order by sal desc)
5 where rownum <=5;
SQL> select rownum, e.ename, e.sal, d.loc
2 from dept d, (select ename, sal,deptno
3 from emp
4 order by sal desc) e
5 where e.deptno= d.deptno and rownum<=5;
SQL> create sequence emp_empno_seq
2 increment by 10
3 start with 10
4 maxvalue 100
5 nocache
6 nocycle;
//시퀀스 확인
SQL> desc user_sequences;
SQL> select sequence_name, min_value, max_value, increment_by, last_number
2 from user_sequences;
SQL> select emp_empno_seq.nextval from dual;
SQL> select emp_empno_seq.currval from dual; //nextval로 불러와야지 사용가능
SQL> ALTER SEQUENCE EMP_EMPNO_SEQ
2 INCREMENT BY 2
3 MAXVALUE 9000
4 NOCACHE
5 NOCYCLE;
//시퀀스 삭제
SQL> drop sequence emp_empno_seq;
SQL> create index emp2_ename_idx on emp2(ename);
SQL> select ic.index_name, ic.column_name,
2 ic.column_position, ix.uniqueness
3 from user_indexes ix, user_ind_columns ic
4 where ic.index_name = ix.index_name
5 and ic.table_name = 'EMP2';
SQL> drop index emp2_ename_idx;
SQL> alter table emp2
2 add constraint emp2_empno_pk primary key(empno);
//primary key를 삭제하면 인덱스도 사라짐
SQL> conn system/123456
SQL> grant query rewrite to scott;
//인덱스 생성
SQL> create index lower_job_idx
2 on emp2(lower(job));
SQL> grant create synonym to scott;
//생성
SQL> create synonym e for emp2;
SQL> select * from e where lower(ename)= 'smith';
SQL> desc user_synonyms;
SQL> select synonym_name, table_name from user_synonyms;
SQL> drop synonym e;
//공용동의어 생성
SQL> create public synonym hr_emp for hr.employees;
//다른사용자 권한 부여
SQL> grant select on hr.employees to scott;
//다른사용자 동의어 접속
SQL> select * from hr_emp where first_name='Steven';
SQL> create user tom
2 identified by jerry; //생성후 해당 사용자 바로 접속 불가함
SQL> grant create session to tom;
**테이블생성 권한 부여
SQL> grant create table to tom;
**테이블공간 사용 권한 부여
SQL> grant unlimited tablespace to tom;
SQL> alter user tom
2 identified by jerry; //관리자와 계정본인 둘다 가능함
SQL> alter user tom account lock;
**계정 잠금 해제
SQL> alter user tom account unlock;
SQL> desc all_users;
SQL> select username, user_id created from all_users;
SQL> select * from scott.emp;
select * from scott.emp
SQL> grant select on emp to tom;
SQL> grant update (loc) on dept to tom;
SQL> update scott.dept set loc='뉴욕' where deptno=10;
SQL> update scott.dept set dname='회계' where deptno=10; //에러남
SQL> select grantee, table_name, grantor, privilege
2 from user_tab_privs_made;
SQL> select owner, table_name, grantor, privilege
2 from user_tab_privs_recd;
SQL> revoke select on dept
2 from tom;
SQL> revoke select on emp
2 from tom;
SQL> create role newuser;
//롤에 권한 부여
SQL> grant create session, create table to newuser;
SQL> create user newuser1
2 identified by 123456;
//사용자에 롤 부여
SQL> grant newuser to newuser1;
SQL> create table emp2 as select * from emp;
SQL> insert into emp2
2 values(8000,'RJU','MANAGER', 7934, sysdate, 2000, null, 40);
SQL> commit;
//접속경로 설정
SQL> conn system/123456
SQL> create public database link link3
2 connect to scott identified by tiger
3 using 'REMOTE';
//링크 연결
SQL> select * from emp2@link2 where empno=8000;
SQL> select deptno, job, sum(sal)
2 from emp
3 group by rollup(deptno, job);
SQL> select deptno, job, sum(sal)
2 from emp
3 group by cube(deptno, job);
SQL> select deptno, job, sum(sal),
2 grouping(deptno),
3 grouping(job)
4 from emp
5 group by rollup(deptno, job);
//0을 리턴하는 경우 --해당 행이 집계 연산에 포함되었음.
SQL> select deptno, job, mgr, sum(sal)
2 from emp
3 group by grouping sets
4 ((deptno,job,mgr),
5 (deptno,mgr),
6 (job,mgr));
SQL> select empno, job, deptno
2 from emp
3 where (job, deptno) in
4 (select job, deptno
5 from emp
6 where empno in (7369,7499))
7 and empno not in (7369,7499);
//사번이 7369 또는 7499번인 직원과 직급 및 부서코드가 동일한 사원 + 본인을 제외
SQL> select empno, job, deptno
2 from emp
3 where job in
4 (select job
5 from emp
6 where empno in (7369,7499))
7 and deptno in
8 (select deptno
9 from emp
10 where empno in (7369,7499))
11 and empno not in (7369,7499);
//사번이 7369 또는 7499번인 직원과 관리자사번이 같거나 직급이 같은 사원 +본인 제외
SQL> select ename, sal
2 from emp e
3 where sal>(select avg(sal)
4 from emp
5 where e.deptno=deptno); //서브쿼리 안에 메인쿼리의 열이 들어가있음
SQL> select empno, ename
2 from emp e
3 where exists (select 'X'
4 from emp
5 where e.empno = mgr);
SQL> with
2 dept_sal as (
3 select d.dname, sum(e.sal) as sal_sum
4 from dept d, emp e
5 where d.deptno = e.deptno
6 group by d.dname),
7 dept_avg as (
8 select avg(sal_sum) as sal_avg
9 from dept_sal)
10 select *
11 from dept_sal
12 where sal_sum>(select sal_avg
13 from dept_avg)
14 order by dname;
SQL> select empno, ename, mgr
2 from emp
3 start with ename= 'KING'
4 connect by prior empno=MGR;
SQL> select lpad(ename, length(ename) + (level*2)-2, ' ') name
2 from emp
3 start with ename = 'KING'
4 connect by prior empno = mgr;
SQL> select lpad(ename, length(ename) + (level*2)-2, ' ') name
2 from emp
3 where ename != 'FORD'
4 start with ename='KING'
5 connect by prior empno = mgr;
SQL> select lpad(ename, length(ename) + (level*2)-2, ' ') name
2 from emp
3 start with ename = 'KING'
4 connect by prior empno = mgr
5 and ename != 'FORD';
피드 구독하기:
글 (Atom)