[ 3일차 ] select ~from where구문, order by, count, group by~having, distinct
**복습하기
[1일차] 오라클 기본 테이블
'커밋'을 눌러주는 게 좋다 : 나중에 웹에서 작업할 때 커밋을 안누르면 웹에서 보이지 않는다
제일 처음 등록 (enrol) 테이블을 먼저 지워줘야 한다
create table student(
sno varchar2(3) ,
sname nvarchar2(10),
year number(1), --수정한 부분 원래는 varchar2(10) / 학년 (거의 한자릿수)을 나타내는데 용량을 10으로 크게 잡을 필요 없음
dept nvarchar2(10) ,
constraint student_sno_pk primary key(sno)
);
create table student(
sno varchar2(3) not null primaty key , --하단에 제약조건 거는 대신 이렇게 써줘도 된다, 가장 기본적임
sname nvarchar2(10),
year varchar2(10),
dept nvarchar2(10) ,
);
insert into student(sno,sname,year,dept)
values('100','나연묵','4','컴퓨터');
☞ 이 구문을 보고 테이블을 만들어 보기 ↓↓↓
create table student(
sno varchar2(3),
sname nvarchar2(5),
year number(1), -- int 도 쓸 수 는 있지만 오라클 표준은 아니다, 오라클 자체에 int를 number로 변형하는 프로그램이 들어가있음
dept nvarchar2(10)
);
DDL (데이터 정의어) : creat, drop,alter(수정),rename(이름변경)
DML (데이터 조작어) : select / update, delete, insert - select만 성격이 좀 다름
DCL (데이터 제어어) : grant, revoke
TCL ( 트랜잭션 제어어) : COMMIT(확정), ROLLBACK, SAVEPOINT
트랜잭션은 작업처리의 단위이다
정확한 값을 받기 위해서 제약조건(참조조건)을 걸어두는 것이다 !
cname nvarchar2(10) not null; 이라고 정의하면 null값이 들어오면 오류가 나는 것
- 제약조건과 유사한 유효성검사 컨트롤이 있는데 이는 UI에서 사용한다
constraint enrol_sno_fk foreign key(sno) references student(sno) -- student 테이블의 기본키를 참조하는 키가 외래키(fk)이다
student 테이블의 sno를 참조하는 것
기본키 (primary key) : 중복 불가, not null
외래키 (foreign key) : 다른 테이블의 기본키를 참조
constraint enrol_combo_pk primary key(sno, cno)); -- student, course 테이블의 sno,cno를 pk로 지정한 것
따라서 같은 학번의 학생이 같은 과목의 시험을 볼 수 없다는 의미이다
【교재 p224. 용어 알아두기】
【p179.~185 select 문】
【p194.~196 특정조건 데이터만 조회하는 select from where 구문】
【p199. order by절】
【p209. count】
select sno, sname, year, dept
from student
where year>='4';
select sno, sname, year, dept
from student
where year<='2';
select sno, sname, year, dept
from student
where year<='2' or year>='4';
select sno, sname, year, dept
from student
order by year asc;
select sno, sname, year, dept
from student
where year>='2' and year<='3'; -- 를 between A and B 라고 나타낼 수 있다. a가 작은수 b가 큰수여야함
select sno, sname, year, dept
from student
where year between '2' and '3';
select sno, sname, year, dept
from student
order by year asc, sno asc; --두번 정렬 걸어주기
select count(*)
from student; --레코드 카운트하기, NULL값은 카운팅이 안됨 따라서 * 를 써서 카운트를 해줌
select count(sno)
from student; -- 라고 써줘도 되지만 null값 때문에 *로 써주는것
select count(*) as "학생수" -- 쌍 따옴표 써주기 !
from student; -- 별칭 달기 !!
insert into student (sno, sname, year, dept) -- year엔 null dept엔 공백 넣어줌
values ('600', '둘리','',' ');
select count(dept) as "학생수"
from student; --6명 , 공백도 읽음
select count (year) as "학생수"
from student; --5명
select count (nvl(year,0)) as "학생수" --null값도 카운팅을 해주게 함
from student;
select sno, sname, nvl(year,0), dept -- 이렇게 적어도 같음
from student;
select sno, count(dept)
from student; -- 이렇게 쓰면 그룹바이 오류가 난다
select sno, count(dept)
from student
group by sno; -- sno 를 기준으로 그룹화가 된 것 / 각 열에 대한 카운트가 행추가되어 나타남
100 1
200 1
300 1
400 1
500 1
600 1
select dept, count(dept)
from student
group by dept; -- 학과를 기준으로 그룹화를 시킨 것
전기 1
컴퓨터 3
산공 1
select dept,year, count(dept)
from student
group by dept, year -- 그룹화도 같이 해줘야함
order by dept desc;
select dept
from student
group by dept; -- 그룹으로 묶여서 전기, 컴퓨터, 산공 으로 나옴
select dept as "학과이름", count(dept) as"학생수"
from student
group by dept;
교재 p200. 중복제거 DISTINCT
select count (distinct dept) as "학과수"
from student;
select distinct dept , year
from student;
【교재 p205. GROUP BY절】
select dept "학과명", count(dept) as"학생수" -- 해당학과의 학생수를 카운팅 하고싶을 때
from student
group by dept -- 중복되는 학과를 합쳐서 표현
having count(dept)>=2 --count 값이 2보다 큰 값만 나타내고 싶을 때 having절을 쓴다
order by count(dept) desc; -- count값 내림차순으로 정렬
☞ 여기에서 전기과 학생은 빼고 컴퓨터과 학생만 나타내고 싶을때는
where 절을 사용해서 전기과를 빼고 출력하면 된다 ↓↓↓↓
select dept "학과명", count(dept) as"학생수"
from student
where dept != '전기'
group by dept
having count(dept)>=2
order by count(dept) desc;
-> 해당 순서는 정해져 있는 것 !! select->from->where->group by->having->order by
【교재 p207. 집계함수】
select avg(year)
from student; -- 학년의 평균을 구하기
select avg(year), sum(year)
from student; --학년의 합을 구하기
select sum(year), count(year), avg(year) "평균1", round(avg(nvl(year,0)),1)as"평균",max(year) "최고학년", min(year) "최저학년"
from student; --round는 소수점 처리하기
【ppt 자료】
【교재 p274. 조인 】
inner join (내부조인) : 일반적인 join을 의미, inner join을 할 수록 레코드 수가 감소할 수 있다. 양쪽에 있는 것만 출력되니까
select * from student inner join enrol -- 여기에서 inner는 생략이 가능하다
on student.sno = enrol.SNO;
select * from student inner join enrol
on student.sno = enrol.SNO
where sname='나연묵'; -- 조건절
select * from student inner join enrol
on student.sno = enrol.SNO
join course on enrol.cno = course.cno; -- 세개의 테이블을 조인한 것
조인하는 속성에 대해서 일반적으로 fk를 설정해줌
-enrol 테이블과 course테이블 join하기
select * from enrol inner join course
on enrol.cno = course.cno;
-student 테이블까지 join 하기
select * from enrol inner join course
on enrol.cno = course.cno
join student on student.sno = enrol.sno;
select student.sno,sname,year,dept from student inner join enrol -- * 대신에 컬럼명 명시해 주기/ 각각의 테이블에 on student.sno = enrol.SNO; 중복되는 컬럼은 앞에 테이블명 같이 써줘야함
select student.sno,sname,year,dept from student s inner join enrol e -- 줄여서 별칭으로 명시해줘도 됨
on s.sno = e.SNO;
select s.sno, sname, year, s.dept, c.cno, cname, credit, c.dept, prname, grade, midterm, final
from student s inner join enrol e
on s.sno = e.sno
join course c on e.cno = c.cno;
select s.sno, sname, year, s.dept, c.cno, cname, credit, c.dept, prname, grade, midterm, final
from student s inner join enrol e
on s.sno = e.sno
join course c on e.cno = c.cno
where s.dept='컴퓨터'; -- 컴퓨터 시험본 학생만 출력됨