3-1

5/18

 

1) 커밋

: 커밋을 눌러야 확정이 돼서 웹에서 보임 (외부에서 들어오는 데이터는 자동으로 커밋됨)

 

2) 데이터 정의어

DDL - Create, Drop, Alter(수정), 
DML- 조작어,  select, update, insert, delete 

DCL -데이터 제어어 , Grant, Revoke 권한을 부여하고 회수하는 것

TCL - 트렌젝션 제어어 , Commit(확정), Rollback(원위치)

*224p

 

3) 5/17복습

drop  table enrol;
drop  table student;
drop  table course;


create table student(
 sno varchar2(3) ,
 sname nvarchar2(10),
 year varchar2(10), --char(1) / number(1)을 쓰는게 더 적합
 dept nvarchar2(10) ,
 constraint   student_sno_pk   primary  key(sno)); --sno에 pk제약조건을 걸고 이름은 student_sno_pk 로 할게요

create table student(
 sno varchar2(3) not null primary key,
 sname nvarchar2(10),
 year varchar2(10), --char(1) / number(1)
 dept nvarchar2(10) ,
 );

create table student1(
sno varchar2(3),
sname nvarchar2(10),
year number(1),
dept nvarchar2(10),
primary key(sno)
);

create table course(
 cno varchar2(5) primary key, --pk 개념 자체에 not null이 포함되어있지만, 작성하는게 좋음
 cname nvarchar2(10) not null, -- 사용해도됨
 credit varchar2(3),
 dept nvarchar2(10),
 prname nvarchar2(10));



create table enrol(
 sno varchar2(3) ,
 cno varchar2(5) ,
 grade varchar2(3),
 midterm int,
 final int ,
 constraint enrol_sno_fk foreign key(sno) references student(sno), --student의 pk를 참조
 constraint enrol_cno_fk foreign key(cno) references course(cno),
 constraint enrol_combo_pk primary key(sno, cno)); --예)100번이 C123 과목 시험쳤으면 또 못친다고 정의하는 것/ 재시험 불가 제약조건 (없애면 재시험 칠 수 있음)


insert into student(sno,sname,year,dept)
values('100','나연묵','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('200','이찬영','3','전기');
insert into student(sno,sname,year,dept)
values('300','정기태','1','컴퓨터');
insert into student(sno,sname,year,dept)
values('400','송병호','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('500','박종화','2','산공');


insert into course(cno,cname,credit,dept,prname)
values('C123','프로그래밍','3','컴퓨터','김성기');
insert into course(cno,cname,credit,dept,prname)
values('C312','자료구조','3','컴퓨터','황수찬');
insert into course(cno,cname,credit,dept,prname)
values('C324','화일처리','3','컴퓨터','이규철');
insert into course(cno,cname,credit,dept,prname)
values('C413','데이터베이스','3','컴퓨터','이성호');
insert into course(cno,cname,credit,dept,prname)
values('E412','반도체','3','전자','홍봉희');


insert into enrol(sno,cno,grade,midterm,final)
values('100','C413','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('100','E412','A',95,95);
insert into enrol(sno,cno,grade,midterm,final)
values('200','C123','B',85,80);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C312','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C324','C',75,75);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C413','A',95,90);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C312','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C324','A',95,90);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C413','B',80,85);
insert into enrol(sno,cno,grade,midterm,final)
values('400','E412','C',65,75);
insert into enrol(sno,cno,grade,midterm,final)
values('500','C312','B',85,80);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C123','A',90,90);


select * from student;
select * from course;
select * from enrol;


select * from student 
order by sno desc;

 

4)select 연습

--책 179p 연습
select sno,sname from student;

select sno, sname, year, dept
from student
where sno='300'or sno='500';

select sno, sname, year, dept
from student
where sno in ('300','500');

select sno, sname, year, dept
from student
where sno='300'or sname='나연묵';

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
where year<=2 or year>=4
order by year asc;

--책 195p 연습
select sno, sname, year , dept
from student
where year>=2 and year<=3;

select sno, sname, year , dept
from student
where year between 2 and 3; --2이상 3이하 / 앞에 작은 수 뒤에 큰 수

 

--책 199p 연습
select sno, sname, year , dept
from student
order by year desc, sno asc; --학년부터 내림차순 정렬하고 동일한 값에 학번 오름차순 정렬

 

5) Count 

select count(*)
from student; --레코드를 카운트 

select count(*) as "학생 수"
from student; --별칭

 

select count(sno) as "학생 수"
from student; --sno를 카운트

 

insert into student(sno, sname, year, dept)
values ('600','둘리','', ' ');

select count(year) as "학생 수"
from student; --5명 null

select count(dept) as "학생 수"
from student; --6명 공백문자

 

6) NVL 

: null을 원하는 값으로 치환시켜줌

 

select sno, sname, nvl(year,0),dept
from student;

select count(nvl(year,0)) as "학생 수"
from student; --year가 null이면 0처럼 보임

7) group 205p

update student set dept='전기'where sno='600';

select sno, count(dept) as "학생 수"
from student; --에러/ sno와 dept가 안맞음

select sno, count(dept) as "학생 수"
from student
group by sno; --sno를 기반으로 그룹화

select dept, count(dept) as "학생 수"
from student
group by dept; --dept를 기반으로 그룹화

select dept, year, count(dept) as "학생 수"
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; 

8) distinct 중복제거 (컬럼의 가장 앞에 씀) 200p
select distinct dept
from student;

select count (distinct dept) as "학과 수"
from student;

select distinct dept, year
from student;

 

9) group by, having 205p

- having 절 : 조건 where 말고 group by (그룹화) 후에 조건을 다는 것 

 

select dept 
from student
group by dept;

select dept "학과명", count(dept) as "학생 수" --as 생략가능
from student
group by dept
having count(dept)>=2
order by count(dept) desc;

select dept "학과명", count(dept) as "학생 수"
from student
where not dept='전기'
group by dept;

select dept "학과명", count(dept) as "학생 수"
from student
where dept!='전기'
group by dept
having count(dept)>=2
order by count(dept) desc;

--이 순서를 바꾸면 안됨

 

10)집계함수 SUM, AVG, MAX, MIN 207p
select avg(year) 
from student;

select sum(year), avg(year) 
from student;

select sum(year),count(year),avg(year) 
from student; --600번의 year이 null이라 5로 카운트됐음

select sum(year),count(year),avg(year), avg(nvl(year,0)) 
from student; --600번의 year를 NVL로 null을 0으로 치환시켜줌

select sum(year),count(year),avg(year)평균1, round(avg(nvl(year,0)),1) as "평균2" 
from student; --평균값의 소숫점을 round로 정리해줌

select sum(year),count(year),avg(year)평균1, round(avg(nvl(year,0)),1) as "평균2" 
,max(year)최고학년, min(year)최저학년
from student;

 

--round(값,1) : 소수 첫째자리 반올림

  round(값,0) : 1의 자리까지 표시

  round(값,-1) : 10의 1승 자리까지 표시 

  round(값,-2) : 10의 2승 자리까지 표시 

예) round(123.456,-1)  / 10

     round(123.456,-2) / 100