서브쿼리 (SUBQUERY) / 뷰(VIEW)

1. 서브쿼리 (SUBQUERY)

하나의 SQL문에 또다른 SQL문이 있는 형태

 

서브쿼리를 포함하고 있는 쿼리 : 외부쿼리(outer query)

서브쿼리 : 내부쿼리(inner query)

 

서브쿼리는 반드시 괄호()로 감싸져있는 SELECT문이다.

 

서브쿼리는 SELECT 문으로 데이터들을 가지고 오는 것이고 그것을 외부커리에서 이용할 수있다.

 

예시1

SELECT 컬럼, 컬럼... FROM 테이블명
WHERE 컬럼 IN(SELECT 컬럼 FROM 테이블명 WHERE  조건);

()안에 SQL문이 삽입되어 있다.

 

 

 

예시2

INSERT INTO VALUES(값,값,(SELECT 컬럼 FROM 테이블명 WHERE 조건), 값);

값이 서브쿼리로 들어가있다.

 

 

EX. STUDENT 테이블의 학과번호를 통해 MAJOR 테이블의 학과명 조회

-- select sno, sname, (select major_name from major), score from student; --error!

STUDENT 테이블을 조회할 때 MAJOR 테이블의 MAJOR_NAME을 가지고 오려고 서브쿼리를 작성했다.

이렇게만 작성하면 하나의 데이터에 MAJOR_NAME 정보가 한번에 들어가는 것이기 때문에 single-row subquery returns more than one row 오류가 생긴다.

 

select sno, sname, 
(select major_name from major where major.major_no=student.major_no) as major, 
score from student;

WHERE 조건문을 추가해서 데이터에 들어올 학과명을 하나로 제한한다. 외부JOIN으로 학과명을 가져온 결과와 동일하다.

 

 

EX. 장학금테이블 목록에 있는 학생 정보, 장학금 조회

 

 

select 
sno, 
sname, 
(select major_name from major m where m.major_no=s.major_no), 
(select money from student_scholarship ss where ss.student_no=s.sno)
from student s;

1) 학생테이블의 SNO, SNAME 과 학과테이블의 MAJOR_NAME, 장학금테이블의 MONEY를 출력한 것이다. 학생테이블이 아닌 다른 테이블을 연결해야 하는 데이터는 서브쿼리를 이용했다.

여기까지 실행하면 학과를 포함한 학생정보를 모두 출력하면서 MONEY도 컬럼으로 확인된다.

그러나 여기까지는 장학금만 받는 학생목록이 아니다.

모든 학생 목록에서 장학금을 받는 학생은 MONEY에 값이 들어오고, 장학금테이블에 정보가 없는 학생은 MONEY 컬럼이 NULL로 보인다.

 

 

select 
sno, 
sname, 
(select major_name from major m where m.major_no=s.major_no), 
(select money from student_scholarship ss where ss.student_no=s.sno)
from student s
where s.major_no in(select m.major_no from major m)
and s.sno in(select ss.student_no from student_scholarship ss) ;

2) 장학금 테이블에 있는 학생 정보만 가져오도록 WHERE 조건을 추가했다.

원하는 데이터만 출력되었다.

 

 

 

 

EX. 학생 테이블에서 학생 전체 데이터 조회 / 행번호(순서대로), 학번, 이름, 학과번호, 평점(내림차순) 출력

 

SELECT ROWNUM, S.* FROM STUDENT S ORDER BY S.SCORE DESC;

1) SQL문을 작성하고 실행했더니, 위와 같이 ROWNUM이 섞여있다. ROWNUM이 부여된 후에 SCORE 내림차순으로 정렬을 다시 하였기 때문이다.

 

 

SELECT ROWNUM, S.* FROM (SELECT * FROM STUDENT ORDER BY SCORE DESC) S;

2) 이것을 해결하기 위해서 내림차순으로 정렬(ORDER BY)를 먼저 서브쿼리로 실행하도록 한다. 정렬해서 가져온 데이터를 S로 지칭하고 ROWNUM과 S 데이터를 가져온다.

원하는 데이터를 확인할 수 있다.

 

 

 

EX. 위 조회문 기준으로 ROWNUM 1부터 5번째까지만 조회

SELECT ROWNUM, S.* FROM (SELECT * FROM STUDENT ORDER BY SCORE DESC) S 
WHERE ROWNUM<=5;

WHERE 조건에 ROWNUM<=5를 추가하였고 정상적으로

결과가 확인된다.

 

 

 

EX. 위 조회문 기준으로 ROWNUM 5번째부터 10번째까지만 조회

-- SELECT ROWNUM, S.* FROM (SELECT * FROM STUDENT ORDER BY SCORE DESC) S 
-- WHERE ROWNUM>=5 AND ROWNUM<=10;

에러가 뜨지는 않지만, 아무런 데이터가 출력되지 않는다.

ROWNUM은 1부터 시작한다. ROWNUM 1으로 데이터가 출력되면 번호가 증가 되고, 증가된 번호가 다음으로 출력되는 것이다.
여기서도 처음 ROWNUM은 1로 시작하는데, WHERE절의 5 이상 10 이하 조건에 맞지 않기 때문에 출력이 될 수가 없다. 조건거짓으로 데이터가 출력되지 못하니 ROWNUM은 2로 넘어가지 못하고 이후의 모든 데이터가 나올 수 없는 것이다.

 

SELECT * FROM (SELECT ROWNUM AS RN, S.* FROM 
(SELECT * FROM STUDENT ORDER BY SCORE DESC) S )
WHERE RN>=5 AND RN<=10;

서브쿼리를 이용해서 ROWNUM을 먼저 전부 생성하였다. 생성한 ROWNUM을 RN으로 지칭하고 RN에 대한 조건을 주면서 해결할 수 있다. 

ROWNUM 5번부터 10번까지만 출력된다.

 

 

불일치 쿼리 EX. 장학금 받지 못하는 학생목록 조회

SELECT * FROM STUDENT WHERE SNO NOT IN(SELECT STUDENT_NO FROM STUDENT_SCHOLARSHIP);

장학금테이블에 저장된 학번을 가져오고 스튜던트 학번과 NOT IN으로 비교하였다.

 

 

 

EX. 장학금 받지 못하는 학생들을 학과별로 몇명인지 서브쿼리 이용해서 출력 (학과명 인원수)

 

 

--1 학번,학과명 가져오기 (학번은 학생 비교하기 위해서 가져온것)
select s.sno,(select major_name from major m where m.major_no=s.major_no) as major_name
from student s;

1) 학생테이블(s)로 부터 학번은 바로 가져올 수 있다. 학과명을 가지고 오기 위해서 major_no과 동일한 조건으로 major_name을 major 테이블에서 가지고 왔다.

여기까지 하면 학생테이블에 있는 학생 정보의 학번과 학과명을 출력하였다.

학번을 가져온 이유는 학번으로 장학금테이블과 비교하기 위해서이다.

 

 

--2 장학금 받지 못한 학생들의 학과명만 가져온다.
select major_name from 
(select s.sno,(select m.major_name from major m where s.major_no = m.major_no) as major_name from student s)
where sno not in(select ss.student_no from student_scholarship ss);

2) 위의 1)에서 만든것은 학생의 학번,학과명 정보이다. 그것에서 학과명이 필요하기 때문에 학과명을 가지고 온다. 조건은 where로 걸어주는데 장학금테이블에 학번이 없는 학생의 정보만 가져올 수 있도록 한다.

 

 

--3 그룹화, 그룹함수 추가
select major_name, count(*) from 
(select s.sno,(select m.major_name from major m where s.major_no = m.major_no) as major_name from student s)
where sno not in(select ss.student_no from student_scholarship ss)
group by major_name;

3)  이제 학과명으로 그룹화하고 count로 인원을 출력할 수 있다.

 

 

 

EX. 위의 예시를 조인을 이용해서 출력

select m.major_name, count(*) from student s, major m, student_scholarship ss
where s.major_no = m.major_no(+) and ss.student_no(+) = s.sno and ss.student_no is null
group by m.major_name;

학생, 학과, 장학금 3개의 테이블을 조인한다. 장학금 테이블에 학번이 NULL인 학생을 찾아야하기때문에 외부조인을 이용한다. 조인으로 묶은뒤에 그룹화하고 출력하면 된다.

 

 

 

EX. 학생 데이터 중 최고 점수를 가지고 있는 학생과 최저 점수를 가지고 있는 학생 정보를 조회
(학번 이름 학과명 점수)

 

 

--1 학생-학번 조인
select s.sno, s.sname, m.major_name, s.score
from student s, major m
where s.major_no = m.major_no(+);

1) 학과 테이블에서 학과명을 가져오기 위해 조인한다.

select max(score) from student;

그룹함수 MAX를 사용하기 위해서 그룹화를 해야한다고 생각할 수 있지만, 여기서는 그룹화를 해서 그룹별로 MAX, MIN값이 궁금한 것이 아니다. 전체학생중의 max, min 한건씩 출력하는 경우는 그룹으로 묶지 않고 위 처럼 바로 값을 구할 수 있다.

 

 

--2 서브쿼리 추가
select s.sno, s.sname, m.major_name, s.score
from student s, major m
where s.major_no = m.major_no(+) 
and s.score in((select max(s.score) from student s),(select min(s.score) from student s));

2) WHERE에 점수가 MAX와 동일하거나 MIN과 동일한 점수를 가지는 정보만 출력하도록 IN을 이용해서 조건문을 추가한다. 

 

 

 

EX. 학생 데이터 중 평점이 전체 평균 이하인 학생 조회

select * from student s where score<(select avg(s.score) from student s);

 

 

EX. 평균 연봉 이상 받는 사원들을 조회 (사번 이름 부서 직급명 연봉)

select 
e.eno, e.name, e.department, p.position_name, s.salary 
from employee e, salary s, position_list p 
where p.position_no(+) = e.position_no and s.eno(+) = e.eno 
and s.salary >= (select avg(salary) from salary);

 

 

EX. 파견을 갔다온 횟수*연봉의1% = 성과급으로 하고 성과급 출력 

--1 파견 다녀온 직원
select *
from employee e
where e.eno in(select d.eno from dispatch d);
--2 
select e.eno, e.name,
(select count(*) from dispatch d where e.eno = d.eno) 
* (select s.salary * 0.01 from salary s where s.eno = e.eno)
as bonus
from employee e
where e.eno in(select d.eno from dispatch d);

1) 파견 다녀온 직원은 파견테이블과 직원테이블의 사번을 비교하여 출력한다.

2) 성과급을 출력하기 위해 파견테이블에 입력된 사번의 수를 COUNT로 구하고, 연봉테이블에서 사번으로 연봉을 찾아서 1%를 계산해준다. 이것 자체를 컬럼으로 넣었고 AS BONUS를 사용해서 컬럼명을 간단히 할 수도 있다.

원하는 데이터를 출력했다.

 

select e.eno, e.name,
(select count(*) from dispatch d where e.eno = d.eno) 
* salary * 0.01 as bonus
from employee e, salary s
where s.eno = e.eno and e.eno in(select d.eno from dispatch d);

위 예시를 조인을 이용하여 만들 수도 있다.

 

 

EX. 최다 판매 자동차 조회

 

 

SELECT MAX(CC) FROM (SELECT CAR_NO, COUNT(*) AS CC FROM CAR_SELL_LIST GROUP BY CAR_NO);

1) 판매테이블에서 최대 판매수부터 구한다.

 

 

SELECT CAR_NAME, COUNT(*) 
FROM CAR C , CAR_SELL_LIST S
WHERE C.CAR_NO = S.CAR_NO
GROUP BY CAR_NAME
HAVING COUNT(*)=(SELECT MAX(CC) FROM (SELECT COUNT(*) AS CC FROM CAR_SELL_LIST GROUP BY CAR_NO));

2) 필요한 컬럼을 가져오기 위해 조인하고, GROUP을 묶어 COUNT를 구한다. HAVING으로 COUNT의 값이 위에서 구한 최대값과 같을 때로 조건을 넣어준다. 

 

 

 

 

 

2. SQL INJECTION

SQL 인젝션(SQL 삽입, SQL 주입)

코드 인젝션의 한 기법으로 클라이언트의 입력값을 조작하여 서버의 데이터베이스를 공격할 수 있는 공격방식

임의의 SQL 문을 주입하여 데이터베이스가 비정상적인 동작을 하도록 조작하는 행위

 

 

 

3. 뷰 VIEW

사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 가상 테이블

저장장치 내에 물리적으로 존재하지 않지만 사용자에게 있는 것처럼 간주된다.

필요한 내용만 보여주도록 만드는 것이며, 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해진다.

뷰는 원본데이터가 바뀌면 바뀐 데이터 기준으로 뷰에도 적용된다.

뷰를 통해서만 데이터에 접근하게 하면 뷰를 구성한 원래의 테이블 정보를 알 수 없기 때문에 보안에 용이하다.

 

1) 뷰 생성 권한 부여

뷰를 생성하기 위해서는 CREATE VIEW 권한이 있어야 한다.

GRANT CREATE VIEW TO 사용자;

 

2) 뷰 생성

뷰 생성 권한이 있는 유저는 뷰를 만들 수있다.

 

뷰 생성

CREATE OR REPLACE VIEW 뷰 NAME [ (컬럼1,컬럼2..)]

AS

SELECT문;

 

SELECT문에 입력한 내용이 VIEW로 저장된다.

뷰 생성을 위해서는 CREATE VIEW, 뷰 수정을 위해서는 REPLACE VIEW로 사용하는데

CREATE OR REPLACE VIEW를 사용하면 같은 이름의 뷰가 있을 경우에는 뷰가 수정되고, 없으면 생성된다.

CREATE OR REPLACE VIEW STUDENT_VIEW --VIEW 생성OR수정 : 뷰가 있으면 수정, 없으면 생성
AS --AS 문 다음 SELECT 했던 구문 사용
SELECT S.SNO, S.SNAME, M.MAJOR_NAME, S.SCORE
FROM STUDENT S, MAJOR M 
WHERE S.MAJOR_NO = M.MAJOR_NO;

 

 

EX. 서브쿼리 예제를 뷰를 이용하기 - 평균 연봉 이상 받는 사원들을 조회 (사번 이름 부서 직급명 연봉)

--1 뷰 생성
create view employee_view as
select e.eno, e.name, e.department, p.position_name, s.salary from employee e, salary s, position_list p 
where p.position_no(+) = e.position_no and s.eno(+) = e.eno;
--2 뷰 이용
select * from employee_view where salary >= (select avg(salary) from salary);

테이블 3개를 조인하여 사번, 이름, 부서, 직급명, 연봉 컬럼을 가진 뷰를 생성한다. 생성된 뷰를 이용하면 뷰에 조건을 주어서 select로 간단히 출력할 수 있다.

 

 

 

EX. 평균 연봉 이상 받는 사원들 중 직급별 인원수 조회

select position_name, count(*) from employee_view 
where salary >= (select avg(salary) from salary) 
group by position_name;

위에서 생성한 뷰를 이용한다. 뷰에는 position_name 컬럼이 있기 때문에 그룹화도 할 수 있다. 직급별로 그룹화하여 count로 인원수를 구할 수 있다.

 

select p.position_name, count(*)
from employee e, position_list p, salary s
where s.eno = e.eno and p.position_no = e.position_no
and s.salary >= (select avg(salary) from salary)
group by p.position_name;

동일한 내용을 뷰를 사용하지 않고 하려면 이처럼 작성한다. 3개 테이블을 조인하고 그룹화한다.

 

 

4) 뷰 조회

SELECT * FROM 뷰이름;

select * from student_view;

 

5) 뷰 전체 목록 조회

SELECT * FROM USER_VIEWS;

사용자가 만들었던 모든 뷰를 확인할 수 있다.

 

5) 뷰 구조 확인

DESC 뷰이름;

DESC STUDENT_VIEW;

이름                   널?               유형           
----------          --------         ------------ 
SNO                 NOT NULL      CHAR(8)      
SNAME             NOT NULL    VARCHAR2(15) 
MAJOR_NAME    NOT NULL    VARCHAR2(30) 
SCORE              NUMBER(4,3)  

 

실행하면 이러한 뷰 이름, NULL 정보, 데이터 유형을 확인할 수 있다.

 

6) 뷰 삭제

DROP VIEW 뷰이름 RESTRICT or CASCADE

DROP VIEW STUDENT_VIEW RESTRICT;

RESTRICT : 뷰를 다른곳에서 참조하고 있으면 삭제하지 않는다.

CASCADE : 해당 뷰를 참조하는 다른 뷰나 제약 조건까지 모두 삭제된다.