4-SQL 기본문법 정리

5/19

 

1)View

: 물리적으로 연결되어 있는 가상의 테이블 (뷰끼리도 조인가능)
1. 편리성 : 조인을 이용하여 view테이블을 만들 수 있다. 

               -select를 목적으로 많이 쓴다
2. 보안성 : 원본테이블의 부분을 이용하여 view테이블을 만들 수 있다.

               -테이블 하나를 부분적인 내용만 보여주고 싶을 때 주로 사용

 

create view v_student1
as
select sno, sname from student;
-- v_student1는 sno랑 sname만 보여주는 뷰이다

select * from v_student1
where sno=300 or sno=400;

 

조인시 동일한 컬럼명이 있다면 반드시 명칭으로 새로운 컬럼명을 만들어야 한다

 

create view v_join2
as
select s.sno as s_sno, e.sno as e_sno
from student s
join enrol e
on s.sno=e.sno;

 

조인이 되어있으면 수정,삭제,추가를 할 수 없다

view는 join되어 있으면 select만 하자!

 

 

2) join

예제1) 300번 학생의 중간성적, 기말성적의 합과 평균을 나타내시오.

 

1.한명의 중간과 기말을 합쳐서 낸 일년의 합과 평균

select sno, midterm, final, (midterm+final) 합계, (midterm+final)/2 평균
from enrol
where sno='300';

2.한명의 중간과 기말을 합쳐서 낸 일년의 합과 평균

select sno, sum(midterm), sum(final), round(avg(midterm),1),round(avg(final),1)
from enrol
group by sno
having sno='300';

 

(300번이라는 sno를 나타내고 싶은데 sum, avg를 썼으니 그룹화 해줘야함)

 

 

예제2) 전기과,산공과,컴퓨터과 학생이 수강한 과목의 개수를 출력하시오 

 

select dept, count(dept)
from student s, enrol e
where s.sno=e.sno  
group by dept
having dept in('전기','산공','컴퓨터');

 

3) 서브쿼리

 

1. 컴퓨터과 학생들의 점수를 중간, 기말 점수에서 -1점씩 진행하시오

 update enrol set midterm=midterm-1 , final=final-1
 where sno in( 
 select sno from student
 where dept='컴퓨터'
 );

 
2. 서브쿼리를 이용하여 화일처리 과목의 중간고사 평균과 기말고사 평균을 나타내시오

select avg(midterm) "중간고사 평균", avg(final)"기말고사 평균" 
from enrol
where cno in(
select cno from course
where cname='화일처리'
);

 

4) 정규화

정규화(모델링) : 테이블 모델링할 때 중복되는 것들을 제거하는 것 (예.sno)

 

select * from student s 
join enrol e
on s.sno=e.sno
order by s.sno asc;
--이너조인 / 두 테이블에 다 있는 값만

select * from student s 
left join enrol e
on s.sno=e.sno
order by s.sno asc;
--student테이블을 기준으로 enrol에 없는 것은 null

select * from student s 
right join enrol e
on s.sno=e.sno
order by s.sno asc;
--enrol테이블을 기준으로 student에 없는 것은 null

 

select * from student s 
full join enrol e
on s.sno=e.sno
full join course c
on e.cno=c.cno
order by s.sno asc;

 

5) update

 예) 중간고사 성적이 80점 이하인 학생에 대해서 5점을 더해 주세요!!
  
  update enrol set midterm=midterm+5
  where midterm<=80;