[DB&SQL] DCL & 제약조건 & TOP-N & 시퀀스

1.Constraint (제약조건)

 

Constraint 제약조건이름 제약조건 유형(column)

Create table test

( a number(5),

 b number(5),

 c number(5),

Constraint test_a_pk primary key(a)

);

======================================================================

Create table test

( a number(5),

 b number(5) Constraint test_b_uk unique,  또는 그냥 unique   (둘다 잘 안씀)   <- 컬럼 레벨

 c number(5),

Constraint test_a_pk primary key(a)   <- 테이블 레벨( 가독성이 컬럼 레벨보다 더좋고 많이씀)

);

pk, uk, fk,  ck  => t.레벨  c.레벨

nn => c.레벨

 

제약조건의 외래키를 사용하는경우 서로 같은 데이터 타입과 길이여야 한다.

[생성된 테이블에 제약조건을 추가하는 경우]

 

 

alter table employees add constraint emp_manager_fk   foreign key(manager_id)  references employees(employee_id);

 

 

 

modify -> nn    ex) aa contsraint test_aa_nn not null

ex)alter table 테이블명(컬럼명 데이터타입);   

add -> pk fk ck uk 

 

cascade => fk emp 같이 삭제

cascade constraints=> fk 만 삭제

 

save test.sql    << 해당 sql 스크립트 파일 만들기

메모장으로 test.sql 찾은후 메모장으로 명령어 치기 가능

@test.sql           <<스크립트 실행!

 

 

2.view 

---

뷰 생성

create view empvu80
as
select employee_id, last_name, salary
from employees
where department_id=80

 

테이블 같은경우

테이블 구조=> 데이터 딕셔너리에 저장

데이터 => 저장공간 할당되어 저장됨

뷰 같은경우

뷰의 서브쿼리문의 텍스트(select문장)를 데이터 딕셔너리에 저장

 

뷰 제거

drop view 뷰이름;

 

 

3.top-n 분석(rownum+인라인뷰)

---

rownum   =행의 번호 출력 select rownum,employee_id,salary from employees;

 

select rownum,employee _id,salary from employees; where rownum<5;

 

rownum은 큰 번호는(ex rownum>5) 찾을수 없음

애초에 먼저 출력이 안되기 때문에

 

[문제]가장하위 3명 돈을 출력하시오

select rownum,last_name,salary
from employees
where rownum<4
order by salary desc

[틀림]

 

select rownum,employee_id,salary
from (select employee_id, salary
        from employees
        order by salary )
where rownum<4

[정답]

why? 실행순서를 보면 알수있음

 

[문제]:상위 3명의 급여를 받는 사람들의 평균값을 구하시오

select rownum,department_id,  avgsal
from (select department_id,avg(salary) as avgsal
      from employees
      group by department_id
      order by avg(salary) desc) 
where rownum<4 

 

4.시퀀스

---

create sequence dept_deptid_seq
         increment by 10           <증가값
          start with 300             < 시작위치
         maxvalue 9999;

no cache   < 둘다 디폴트값이라 생략 가능

no cycle

 

NEXTVAL   =값을 추출   CURRVAL =추출된 값을 저장

 

 

 

 

! ROLLBACK 해도 증가된 값이 남아있음 

 

시퀀스는 동시성 제어에서 공유가 가능하다. 테이블은 공유X

START WITH 를 바꾸고 싶을시 새롭게 생성해야한다. 이미 써버런 스타트 위치는 바꿀수없음

 

5.DCL (권한제어)

---

 

유저생성

create user test     << id
identified by test;   << 비번

 

접속권한 부여

grant create session to test;

 

테이블 생성부여

grant create table to test;

 

생성된 테이블 저장 권한 부여

grant connect, resource to test;

 

권한 회수

revoke connect, resource from test;

[connect,resource ->hr과 같은 롤]

 

role 생성

1. create role manager;

2. grant a,b to manager;          [ab의 롤을 매니저에게 부여] 

3. grant manager to test, test1       [매니저를 test,test1에게 부여]                         ->test(a,b)  ,test1(a,b)

4. grant c,d to manager;             [cd의 롤을 매니저에게 부여]        test,test1(c,d) => test(a,b,c)  ,test1(a,b,c)

5. revoke d from manager;      [d를 제거하면 매니저권한 부여받은곳에서 다제거]               test(a,b,c),  test1(a,b,c)

 

DB구축 TABLE 구축 ->INSERT DB운영 INSERT,UPDATE,DELETE 서비스 개발 DATA 검색=>SELECT