2022-11-07 서브쿼리, 뷰, 데이터 조작어, 데이터 정의어, 제약 조건

서브쿼리 종류

 

 

3. 다중컬럼 서브쿼리

 

- 두 개 이상의 컬럼을 리턴하는 서브쿼리

 

- select절에 비교할 데이터를 여러 개 지정하는 반식

 

- 메인쿼리에 비교할 열을 괄호로 묶어 명시하고 서브쿼리에서는 괄호로 묶은 데이터와 같은 자료형 데이터를 select절에 명시

where (컬럼1, 컬럼2...) in (서브쿼리의 실행결과 값1, 값2...)

select empno, deptno, sal
from emp
where (deptno, sal) in (select deptno, min(sal)
	   from emp
	   group by deptno);

 

 

4. 상호연관 서브쿼리 (효율 안 좋음)

-서브쿼리를 실행할 때 메인쿼리 테이블의 컬럼을 참조해서 작업해야 하는 서브쿼리

- 서브쿼리의 비교 값이 계속 바뀌는 경우 메인쿼리의 값을 참조해서 사용
   ---------------------------------------------------
       ㄴ각각의 행에 입력된 값에 따라서 버크뤄리의 결과가 달라지는 경우

- 메인쿼리의 테이블에 별칭을 추가하고 참조해서 사용

- 메인쿼리의 한 row에 대해서 서브쿼리가 한 번씩 실행된다.

- 서브쿼리에서는 메인쿼리의 컬럼을 사용할 수 있으나 메인쿼리에서는 서브쿼리의 컬럼을 사용할 수 없다.

 

 

<실행흐름>

1) 메인쿼리에서 참조할 값을 이용해서 서브쿼리를 실행
2) 서브쿼리의 실행 결과를 이용해서 메인쿼리를 실행
3) 1번과 2번을 레코드 갯수만큼 반복해서 실행

Q. sal가 본인 부서의 평균보다 많은 사원의 사원번호, 사원명, 부서번호, sal 출력하기
select empno, deptno, sal
from emp main
where sal > (select avg(sal)
             from emp
             where deptno = main.deptno);

 

 

 

5. select절에 서브쿼리

- 서브쿼리의 실행결과 : 컬럼이 한 개, row도 한 개
- 메인쿼리의 컬럼을 사용하는 경우 성능저하

Q. 사원번호, 이름, 급여, 부서별 평균급여

 select empno, ename, sal, (select avg(sal)
                            from emp
 			    where deptno = main.deptno)
 from emp main;

 

 

 

6. from절에 서브쿼리

- 특정 테이블 전체 데이터가 아닌 select문을 통해 일부 데이터를 먼저 추출해 온 후 별칭을 주고 사용

select e.empno, e.ename, e.dpetno, e.sal, a.avgsal
from emp e, (select deptno, avg(sal) avgsal
             from emp
             group by deptno) a
 where e.deptno = a.deptno

 

 

 

7. top-N쿼리

- 순위 구해줌

select rownum ename, sal
from (select *
      from emp
      order by sal desc)
where rownum < 4 and sal is not null;

 ENAME         SAL
-------  ---------
     1        5000
     2        3000
     3        3000

=> ' rownum ename ' 

     ename이라는 이름을 가진 rownum. 숫자 1, 2, 3 즉, 순위를 출력한다.

 

 

 

<top-N쿼리 연습 블로그 과제>

1) 월 별 입사자 수를 조회하되 입사자수가 가장 많은 상위 3개의 달만 출력되록 하시오.

[출처] top-N쿼리 연습|작성자 heaves1

select mon 월, cnt 입사자수
from (select to_char(hire_date,'mm') mon, count(employee_id) cnt
      from employees
      group by to_char(hire_date,'mm')
      order by cnt desc) emp
where rownum <= 3
order by 월(= mon);

=> 서브 쿼리로 또 하나의 가상 테이블을 만들어서 from절에 넣어줌

employees테이블에서 내가 뽑아올 데이터의 열 이름을 각각 'mon', 'cnt'라고 주었고,  'mon'의 데이터를 월 별로 그룹화시켰다. 그리고 "입사자 수가 가장 많은 상위의 달"을 출력해야 하니까 입사자 수의 데이터를 가진 'cnt'를 내림차순 정렬하여 상위 3개의 달(1월, 2월, 3월)만 출력되도록 했다.

"3개의 달"만 출력해야 하기 때문에 메인쿼리의 where절에서 rownum함수를 이용해 "<=3" 조건을 주었다. 

마지막으로 오름차순으로 출력될 수 있도록 월의 정보를 가지고 있는 열인 'mon'을 정렬해 주었다.

 

2) 'IT' 부서에 속한 직원들을 대상으로 급여(Salary)가 가장 많은 직원 상위 3명의 이름(first_name), 급여(salary), 부서명(department_name)을 조회하시오.

[출처] top-N쿼리 연습|작성자 heaves1

select first_name, salary, dname
from (select e.*, d.DEPARTMENT_name dname
      from employees e, DEPARTMENTs d
      where e.DEPARTMENT_ID = d.DEPARTMENT_ID
      order by salary desc)
where dname = 'IT' and rownum <= 3;

 

 

<rank() over 함수>

select ename, sal, rank() over(order by sal desc) as 순위
from emp
where sal is not null;

ENAME                       SAL       순위
-------------------- ---------- ----------
KING                       5000          1
FORD                       3000          2
SCOTT                      3000          2
JONES                      2975          4
BLAKE                      2850          5
CLARK                      2450          6
ALLEN                      1600          7
TURNER                     1500          8

 

 

 

 

 

 

 

 

뷰

 

---

- 자주 쓰는 테이블의 내용을 뷰로 만들어서 저장해두기(가상테이블을 만들기 위한 SQL문=> 뷰)

- 미리 등록해둔 뷰를 펼처놓고 작업함

1. 관리자 계정으로 이동
conn system/manager
2. 권한주기
grant create view to scott;
3. 뷰 만들기
create view avgempview   // avgempview라는 이름의 뷰를
as
select deptno, avg(sal) avg  // 이런 내용으로 만들것이다.
from emp
group by deptno;
4. 사용
select * from avgempview;

 

 

 

 

 

 

 

 

데이터 조작어 DML

 

---

- select문으로 조회한 테이블에  데이터를 추가, 변경, 삭제할 때 사용하는 명령어로 이루어져 있다.

 

- CLRUD

 

 

1) 테이블 만들기

create table 테이블이름(컬럼1, 컬럼2 ...) values(조건1, 조건2 ...)

create table customer (id varchar2(20) primary key,
                 	   pass varchar2(20),
                 	   name varchar2(20));

 

2) 데이터 추가

insert into 테이블이름(컬럼1, 컬럼2 ...) values(값1, 값2 ...)

insert into customer(id, pass, name, addr) values('LostArk3',null,'바스티안','아르데타인');

 

3) 데이터 수정하기

update 변경할 테이블

set 변경할 열 1 = '데이터1',  변경할 열 2 = '데이터2' ...

- 데이터 전체 수정
update customer
set id 'kim';

- 데이터 일부만 수정 => where절에 조건 주기
update customer
set id 'kim';
where pass = '1234';

 

4) 데이터 삭제하기

- 전체 삭제하기

: delete from 테이블명

- 일부만 삭제하기

: delete from 테이블명

  where 조건

DELETE from customer
where pass = '1107';   // pass가 1107인 것만 지움

 

 

 

 

 

 

 

 

데이터 정의어 DDL

 

---

- 데이터 베이스 데이터를 보관하고 관리하기 위해 제공되는 여러 객체의 생성, 변경, 삭제 관련 기능을 수행한다.

- DML과 다르게 자동 commit된다.

- 데이터 정의어

: CREATE(객체 생성), ALTER(이미 생성된 객체 변경), DROP(객체 삭제)

 

1) 테이블 생성하기

create table 소유계정.테이블 이름 (
열1 이름, 열1 자료형,
열2 이름, 열2 자료형,
... );

 

2) 테이블  변경하기

 

- 테이블에 열 추가 ADD

alter table customer(테이블 이름)
add (regdate date, email varchar2(20)); (추가할 열)
=>Table altered.
 desc customer;
=>결과

 Name
 ----------------------------
 ID
 PASS
 NAME
 ADDR
 REGDATE 추가됨
 EMAIL 추가됨

 

- 데이터 추가하기

insert into customer(id,name,addr) values('kim', '김하늘', '경기도');
         테이블 이름(컬럼1, 컬럼2 ...) values (값1, 값2 ...);

 

- 열 이름 변경하기 RENAME

alter table customer(테이블 이름)
rename column regdate to reg_date;
          수정 전 이름 to 바꿀 이름

 

- 열 삭제하기 DROP

alter table customer(테이블 이름)
drop column id(삭제할 열 이름)

 

- 열 수정하기 MODIFY

열1이름, 열1자료형, customer
modify (addr varchar2(50), reg_date varchar2(20));

 

3) 테이블 이름 변경하기 RENAME

rename 수정 전 이름 to 바꿀 이름;

 

4) 테이블 삭제하기 DROP

- 테이블에 저장된 데이터도 모두 삭제됨

drop table 삭제할 테이블 이름

 

 

 

 

 

 

 

 

제약 조건

 

---

- 특정 테이블의 특정 열에 지정함

 

- 제약 조건을 저장한 열에 제약 조건에 부합하지 않는 데이터를 저장할 수 없다.

 

- 제약 조건 지정 방식에 따라 기존 데이터의 수정이나 삭제 가능 여부도 영향을 받는다.

• NOT NULL : NULL값 허용 안 함. NULL을 제되한 데이터의 중복은 허용
• UNIQUE : 중복값 입력 금지 (NULL값은 중복입력 가능)
• PRIMARY KEY : NOT NULL + UNIQUE.
• CHECK : 조건식을 만족하는 데이터만 입력 가능

 

- 제약 조건 조회 

desc user_constraints;

 

- 제약 조건 살펴보기

select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
from user_constraints;

OWNER				 	                              타입
---------------------------------------------------------------------------
CONSTRAINT_NAME                                        CO TABLE_NAME
------------------------------------------------------------ -- -----------
SCOTT
SYS_C006997  //이름                                     P  EMP

SCOTT
SYS_C007020                                             P  CUSTOMER

=> 이름을 지정하지 않으면 알아서 이름을 지어줌

 

 

<유니크 제약 조건>

- 값이 중복되지 않음

alter table customer
add (sal number, nickname varchar2(20));

- 닉네임 중복x 제약조건 주기
alter table customer
add constraint uk_nickname unique(nickname);
                제약 명           컬럼1, 컬럼2...

- 닉네임 만들어주기
update customer
set nickname = 'kimmmmmm'
where id='kim';

 

 

<check제약조건>

- 데이터의 형태과 범위를 정함

alter table customer
add constraint sal_chk check(sal between 1000 and 5000);  // sal의 범위를 1000~5000으로 제한두기

alter  table customer
add constraint addr_ck check(addr in ('경기도','서울','페이튼','베른','루테란','아르데타인','전주'));
ㄴ> 여기에 있는 주소만 추가할 수 있음

 

 

<PRIMARY KEY & FOREIGN KEY>

- 기본키(pk) = parent key / parent table
- 외래키(fk) = child key / child key

alter table order_product
add constraint fk_ordno foreign key(order_no) references order_info(ORDERNO);

=> "fk_ordno"라는 이름의 제약조건을 추가하는데, order_info 테이블의 'ORDERNO'을 참조하겠다.