SQL문법

<LIKE 연산자>
- 문자열에 대해서는 일부분만 일치하는 경우를 찾아야 할 때 사용
- '=' 연산자 대신에 'like 연산자'를 사용
  - '='는 정확히 일치하는 경우에만 사용

- 형식
  where<필드이름> like<문자열패턴>
  - <필드이름>에 지정된<문자열패턴>이 들어 있는 지를 판단

- 문자열 패턴 종류
  - _ : 임의의 한 개 문자를 의미
  - % : 임의의 여러 개 문자를 의미
    - ‘%서울%’ : ‘서울’이란 단어가 포함된 문자열
    - ‘%서울’ : ‘서울’이란 단어로 끝나는 문자열
    - ‘서울%’ : ‘서울’이란 단어로 시작하는 문자열
    - ‘_ _ _’ : 정확히 세 개의 문자로 구성된 문자열
    - ‘_ _ _%’ : 최소한 세 개의 문자로 구성된 문자열

- ex) student 테이블에서 김씨 성을 가진 학생들을 찾는 질의
select *
from  student
where  name like '김%'

- ex) student 테이블에서 여학생들만을 검색
select  *
from  student
where  resident_id like '%-2%'


<집합 연산>
- 관계대수의 집합 연신인 합집합, 교집합, 차집합에 해당하는 연산자 
  - union
  - intersect
  - minus

- 형식
<select문 1>  <집합연산자>  <select문 2>

- 조건
  - <select문 1>과 <select문 2>의 필드의 개수가 데이터타입이 서로 같아야 한다


<UNION>
- ex) student 테이블의 학생 이름과 professor 테이블의 교수 이름을 합쳐서 출력
select  name  from  student
union
select  name  from  professor


<UNION ALL>
- union 연산자를 연산 결과에 중복되는 값이 들어갈 경우 한 번만
- 중복을 제거하고 싶지 않다면 union 연산자 대신 union all 연산자를 사용한다



<INTERSECT>
- ex) 컴퓨터공학과 학생들 중에서 교과목에 상관없이 학점을 'A+'받은 학생들의 학번을 검색
select  s.stu_id
from  student s, department d, takes t
where  s.dept_id = d.dept_id and 
t.stu_id = s.stu_id and 
dept_name='컴퓨터공학과' and grade = 'A+'



<MINUS>
- ex) 산업공학과 학생들 중에서 한 번이라도 'A+'를 받지 못한 학생들의 학번을 검색
  select stu_id from student s, department d
  where s.dept_id = d.dept_id and dept_name = '산업공학과'
  minus
  select stu_id from takes
  where grade='A+'


<외부조인(Outer join)>
- ex) 모든  교과목들에 대해서 교과목명, 학점수, 개설년도, 개설 학기를 검색
  select title, credit, year, semester
  from course, class
  course.course_id = class.course_id

- ex) 모든  교과목들에 대해서 교과목명, 학점수, 개설년도, 개설 학기를 검
  select title, credit, year, semester
  from course, class
  where course.course_id = class.course_id(+)
- 강좌로 개설된 적 있는 교과목에 대해서만 검색된다
  - '이산수학', '객체지향언어' 교과목들은 class 테이블에 저장되어 있지 않기 때문에 검색 결과에 포함되지 못한다



<왼쪽 외부조인(Left outer join)>
- 연산자의 왼쪽에 위치한 테이블에 각 레코드에 대해서 오른쪽 테이블에 조인 조건에 부합되는 레코드가 없을 경우에도 검색 결과에 포함
- 생성되는 결과 레코드에서 오른쪽 테이블의 나머지 필드에는 NULL이 삽입

- 'course left outer join class'
  - course 테이블과 class 테이블에 대해 왼쪽 외부조인을 적용

- 'using(course_id);
  - 조인 조건이 'course.course_id=class.course_id'라는 것을 의미



<완전 외부조인(Full outer join)>
- 양쪽 테이블에서 서로 일치하는 레코드가 없을 경우, 해당 레코드들도 결과 테이블에 포함시키며 나머지 필드에 대해서는 모두 NULL을 삽입
  select title, credit, year, semester
  from course full outer join class
  using (course_id)



<집계 함수(aggregate function)> 
- 통계 연산 기능 제공
- ex)
  - 컴퓨터공학과 학생들을 모두 몇 명인가?
  - 교수들의 평균 재직연수는 몇 년인가?

- 종류
  - count : 데이터의 개수를 구한다
  - sum : 데이터의 합을 구한다
  - avg : 데이터의 평균 값을 구한다
  - max : 데이터의 최대 값을 구한다(character type도 가능)
  - min : 데이터의 최소 값을 구한다(character type도 가능)

- SELECT 절과 HAVING절에서만 사용 가능
- sum, avg는 숫자형 데이터 타입을 갖는 필드에만 적용 가능


<COUNT>
- 형식 
  count(distinct<필드이름>)
  - 해당 필드에 값이 몇 개인지 출력 - 단일 값으로 보여준다
  - distinct : 서로 구별되는 값의 개수가 필요한 경우에만 사용
  - NULL은 계산에서 제외
  - 단, <필드이름>에는 필드 이름 대신 '*'가 사용된 경우에는 레코드의 개수를 계산

- ex) student 테이블에서 3학년 학생이 몇 명인지 출력
   select  count(*)
from  student
where  year = 3

- ex) student 테이블에서 dept_id 필드에 값이 몇 개인지를 출력
  select count(dept_id)
  from  student


- ex) distince 키워드를 사용하면 중복되는 데이터를 제외한 개수를 리턴
  ex) count(dept_id)대신 count(distinct dep_id)를 사용
  select count(distinct dept_id)
  from  student


- ex) 컴퓨터공학과의 학생 수를 출력
  select count(*)
  from student s, department d
  where s.dept_id = d.dept_id and d.dept_name = '컴퓨터 공학과'


<SUM>
- 형식
  sum(<필드이름>)

- ex) 전체 교수들의 재직연수 합
  select sum(2012 - year_emp)
  from  professor

- emp 테이블에 저장된 모든 직원들의 급여 합을 출력
  select sum(sal)
  from emp

- 업무(job 필드)가 'ANALYSY'인 직원들의 급여의 합을 출력
  select sum(sal)
  from emp
  where job='ANALYST'

- 부서 이름이 'RESEARCH'인 직원들의 급여의 합을 출력
  select sum(sal)
  from emp e, dept d
  where e.deptno = d.deptno and dname='RESEARCH'



<AVG>
- 형식
  avg(<필드이름>)

- ex) 전체 교수의 평균 재직연수를 출력
  select avg(2012 - year_emp)
  from  professor



<MIN, MAX>
- 형식
  max(<필드이름>)
  min(<필드이름>)

- ex) 부서 이름이 'ACCOUNTING' 인 직원들 중에서 최대 급여가 얼마인지 출력
  select max(sal)
  from emp e, dept d
  whter e.deptno = d.deptno and dname= = "ACCOUNTING"



<GROUP BY>
- select 절에 집계 함수가 사용될 경우 다른 필드는 select절에 사용할 수가 없다
- 지금까지의 SQL은 전체 레코드들을 대상으로 평균, 합, 최대값/최소값만을 출력
- GROUP BY를 이용하면 그룹별로 집계함수 적용 가능
  - ex) '학과별 학생 수', '부서별 최대 급여'

- 형식
  group by<필드리스트>
  - group by절은 select문에서 where절 다음에 위치
  - group by에 지정된 필드의 값이 같은 레코드들끼리 그룹을 지어 각 그룹별로 집계 함수를 적용한 결과를 출력

- ex) student 테이블에서 학과번호(dept_id필드)별로 레코드의 개수를 출력
  select dept_id, count(*)
  from   student
  group by dept_id
  - group by 절에 사용된 필드를 select절에 추가하여 사용할 수 있다

- ex) 학과번호 대신 department 테이블과 조인하여 학과 이름이 출력되도록 수정
  select dept_name, count(*)
  from student s, department d 
  where s.dept_id = d.dept_id
  group by dept_name


- ex) emp, dept 테이블에서 부서별 직원수, 평균급여, 최대급여, 최소급여를 출력
  select dname, count(*), avg(sal), max(sal), min(sal)
  from emp e, dept d
  where e.deptno = d.deptno
  group by dname


- ex) 학사 테이터베이스에서 학과별 교수 숫자와 평균 재직연수, 최대 재직연수를 출력
  select dept_name, count(*), avg(2012 - year_emp), max(2012 - year_emp)
  from professor p, department d
  where p.dept_id = d.dept_id
  group by dept_name



<HAVING>
- 그룹에 대한 조건을 명시할 때 사용
- ex) 평균 재직연수가 10년 이상인 학과에 대해서만 교수 숫자와 평균 재직연수, 최대 재직연수를 출력
  select dept_name, count(*), avg(2012 - year_emp), max(2012 - year_emp)
  from professor p, department d
  where p.dept_id = d.dept_id and avg(2012 - year_emp) >= 10 -> 오류
  group by dept_name

- 다음은 오류
  - Group에 대한 조건은 where절에 사용하지 못한다
  - Having절을 이용해야 한다

- 형식
  having<집계함수 조건>

- ex) having 절을 이용하여 다시 작성
  select dept_name, count(*), avg(2012 - year_emp), max(2012 - year_emp)
  from professor p, department d
  where p.dept_id = d.dept_id
  group by dept_name
  having avg(2012 - year_emp) >= 10

- ex) 직원 숫자가 5명 이상인 부서에 대해서 부서별 직원수, 평균급여, 최대급여, 최소급여를 출력
  select dname, count(*), avg(sal), max(sal), min(sal)
  from emp e, dept d
  where e.deptno = d.deptno
  group by dname
  having count(*) >= 5

- where절과 having절, group by절을 모두 함께 사용할 경우
  1. where절에 명시된 조건을 만족하는 레코드들을 검색
  2. group by절에 명시된 필드의 값이 서로 일치하는 레코드들끼리 그룹을 지어 집계 함수를 적용
  3. 마지막으로 그 집게 함수를 적용한 결과들 중에서 having 절을 만족하는 결과만 골라서 출력



<NULL의 처리>
- 널을 검색하는 방법
- 형식 
  <필드이름> is null
  <필드이름> is not null

- ex) takes 테이블에서 아직 학점이 부여되지 않은 학생의 학번을 검색
  select stu_id
  from takes
  where grade is null

- ex) takes 테이블에서 학점이 'A+'가 아닌 학생들의 학번을 검색
  select stu_id
  from takes
  where grade <> 'A+'
- grade 필드의 값이 널인 레코드에 대해서는 질의 결과에 포함되지 않는다
- 하지만 count(*)는 특정 필드가 아닌 레코드 전체에 대한 연산이므로 널의 존재여부와는 무관하다. 즉 null값이 포함된다



<중첩 질의(nested query)>
- SQL문을 다른 SQL문 안에 중첩하여 사용하는 질의
- 복잡한 질의를 쉽게 표현할 수 있는 수단을 제공

- 내부질의(inner query), 부질의(subquery)
  - 내부에 포함된 SQL문

- 외부질의(outer query)
  - 부 질의를 내부적으로 갖는 SQL 문

- 부 질의는 외부 질의의 from 절이나 where절에 위치
- 종류
  - in, not in
  - =some, <=some, <some, >some, >=some, <>some (some대신 any를 사용해도 된다)
  - =all, <=all, <all, >all, >=all, <>all
  - exists, not exists



<IN, NOT IN>
- ex) '301호' 강의실에서 개설된 강좌의 과목명을 출력
  select title //과목명
  from course
  where course_id in (select distinct couse_id
                           from class 
                           where classromm='301호')
- 부 질의
  - 키워드 in뒤에 나오는 SQL문으로서 class 테이블에서 강의실이 '301호'인 교과목 번호를 검색

- 외부질의
  - course 테이블에서 course_id 필드의 값이 부 질의의 검색 결과에 포함되는 경우(in)에만 과목명을 출력

- ex) (동일한 표현) '301호' 강의실에서 개설된 강좌의 과목명을 출력
  select distinct title
  from course c1, class c2
  where c1.course_id = c2.course_id and
           classroom='301호'

- ex) 2012년 2학기에 개설되지 않은 과목명을 검색
  select title
  from course 
  where cours_id not in
                     (select distinct course_id
                      from class
                      where year = 2012 and semester=2) 



<SOME, ALL>
- =some
  - 지정된 필드의 값이 부 질의 검색 결과에 존재하는 임의의 값과 같은지를 나타낼 때 사용
  - in과 같은 의미

- <=some
  - 부 질의의 검색 결과에 존재하는 임의의 값보다 작거나 같은지를 나타낼 때 사용

- =all
  - 지정된 필드의 값이 부 질의 검색 결과에 포함된 모든 값과 같은지를 판단
 
- <=all
  - 지정된 필드의 값이 부 질의 검색 결과에 포함된 모든 값보다 작거나 같은지를 판단

- ex) 가장 많은 수강 인원을 가진 강좌를 검색
  select c1.couse_id, title, year, semester, prof_id
  from class c1, course c2
  where c1.course_id = c2.course)id and enroll >= all (selexr enroll from class)



<EXISTS, NOT EXIST>
- 부 질의 검색 결과에 최소한 하나 이상의 레코드가 존재하는지의 여부를 표현

- exists
  - 최소한 한 개의 레코드가 존재하면 참이 되고 그렇지 않으면 거짓

- not exists
  - 부 질의의 결과에 레코드가 하나도 없으면 참이 되고 하나라도 존재하면 거짓

- ex) '301호' 강의실에서 개설된 강좌의 과목명을 출력
  select title
  from course
  where exists
          (select *
           from class
           where classroom = '301호' and 
                    course.course_id = class.course_id)

- ex) '301호' 강의실에서 개설된 강좌의 과목명을 출력(not exists로 표현)
  select title
  from course
  where not exists
          (select *
           from class
           where year = 2012 and
                   semester = 2 and
                  course.course_id = class.course_id)



<뷰(VIEW)>
- 기존 테이블들로부터 생성되는 가상의 테이블
- 테이블처럼 물리적으로 생성되는 것이 아니라 기존의 테이블들을 조합하여 사용자에게 실제로 존재하는 테이블인 것처럼 보이게 한다
  
- 기능
  - 특정 사용자에게 테이블의 내용 중 일부를 숨길 수 있기 때문에 보안의 효과
  - 복잡한 질의의 결과를 뷰로 만들어서 사용하게 되면 질의를 간단히 표현할 수 있다



<뷰 생성>
- 생성된 뷰는 테이블과 동등하게 사용
- 형식
  create or replace view <뷰이름> as
                               <select문>
- or replace 키워드를 추가하면 <뷰이름>과 같은 뷰가 이미 존재하는 경우 기존의 뷰를 지우고 새로 생성
- <select문>
  - 뷰 생성에 사용될 select문

- 대부분의 DBMS에서는 사용자 계정에는 뷰 생성 권한이 부여되지 않는다
- 관리자 계정이 아닌 사용자 계정으로 로그인하여 뷰를 생성하려면 뷰 생성과 관련된 권한이 부여되야 한다
- 오라클에서 뷰 생성 권한을 부여하기 위한 형식
  grant create view to <사용자 계정>

- ex) takes 테이블에서 grade 필드를 제외한 나머지 필드만으로 구성된 뷰를 생성
  create or replace view v_takes as select stu_id, class_id from takes

- ex) student 테이블에서 컴퓨터공학과 학생들 레코드만 추출하여 뷰를 생성
  create or replace cs_student as
         select s.stu_id, s.resident_id, s.name, s.year, s.address, s.dept_id
         from student s, department d
         where s.dept_id = d.dept_id and
                 d.dept_name = '컴퓨터공학과' 



<뷰 사용>
- ex) v_takes 뷰에 대해 select 문을 실행

- 뷰에 대해서 insert, update, delete 문을 실행
- ex) v_takes 뷰에 대해 레코드를 삽입
  insert into v_takes
  values('1292502', 'C101-01')
  - v_takes 뷰에 포함되지 않은 grade 필드에는 null이 삽입

- 형식
  drop view<뷰이름>



<뷰 삭제>
- 읽기 전용 뷰
  - 뷰를 생성할 때 insert, update, delete문과 같은 데이터 조작 언어의 사용을 불가능하게 하려면, with read only 키워드 추가