SQL_1일차

23.03.09 목 1일차

국비지원 내일배움 카드를 이용해서 학원을 등록했다.
오늘은 첫 수업날!
오늘 배운 내용을 복습하며 기록하기로한다.

SQL

오늘 배운 내용은 SQL이다.
기본적인 내용들 중 Oracle을 사용하여 사용한 SQL 내용을 알아보자.

Drop

drop  table enrol;  
-- 기존 테이블 내역 삭제(혹시 모를 잔여데이터 충돌 예방)

drop  table student;

drop  table course;

앞서 테이블에서 기존에 남아있을 데이터를 삭제하기 위해 Drop문을 사용하여 데이터를
삭제 후 진행 하였다.
Drop + 테이블명 + 칼럼명을 입력하여 사용한다.

Create

create table student(
 sno varchar2(3) , 
 sname nvarchar2(10),
 -- n + 함수 > 한글 포함언어 입력 시 필요
 -- 예외) 오라클 내 한글 한글자 입력시 3자리수 단위로 입력 필요 EX) 가 > (3) 
 -- 산술연산의 여부 존재로 문자와 숫자 구분 
   EX) 001은 숫자인가 문자인가 ? 숫자 표현시 1 문자 표현시 001
 year varchar2(10),
 dept nvarchar2(10) ,
 constraint   student_sno_pk   primary  key(sno));
 -- P.K 제약조건 = primary key
 -- P.K 제약조건 2가지 충족 필요 = 값(not null), 중복불가 
 -- 제약조건을 사용하는 이유 ? = 정확한 값을 얻기 위함
 -- UI - S(Java) - DB 각각 제약조건 필수
 -- 하나의 테이블에 하나의 P.K 사용 




create table course(
 cno varchar2(5) primary key,
 -- 제약조건을 바로 걸수 있음 유형은 자유롭게 사용
 cname nvarchar2(10),
 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),
  -- 참조 값이 같으면 참조 키 입력하여 연결 가능
 constraint enrol_cno_fk foreign key(cno) references course(cno),
 constraint enrol_combo_pk primary key(sno, cno));
  -- 2개의 칼럼을 하나의P.K로 묶는 것은 가능 
     EX) 100 A, 100 B, 200 A >> 100A, 100B, 200B 







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);

create를 사용하여 student, course, enrol 의 테이블을 작성하였고
insert into를 사용하여 해당 테이블의 칼럼명과 값을 작성하였다.

간단한 값을 구문을 이용해 간단히 만들고 값을 부여할 수 있다.

select

select * from student;
-- 같은 표현 == select sno, sname, year, dept from student;
-- 커서 위치에 따라 테이블 내부 입력 위치가 달라진다.
select * from course;
select * from enrol;
-- select > 신입들이 주로 하는 부분 사고가 별로 없음

select sno as "학 번", sname, year, dept from student;
-- as 사용시 별칭으로 사용 가능
-- 공백이 있을시 따옴표 사용으로 띄어쓰기 표현 가능

select 문을 사용하여 테이블에 작성된 내용을 사용할 수 있다.
*은 테이블의 있는 모든 값을 사용할 수 있게 한다.
또한 테이블의 칼럼명을 as +"별명" 을 이용해
별칭으로 선언하여 변경할 수 있었다.

where

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

select + 칼럼명 + from + 테이블명 이 기본 적인 구문이며,
where 문을 사용하여 해당하는 칼럼의 이름, 값을 출력값으로 보일 수 있다.

select * from student
where year='3' or year = '4';

select * from student
where year in ('2','4');
-- in = or 와 같은 동작 (또는) 으로 중복 검색

select * from student
where year between '2' and '4';
-- between = 2에서 4사이 = 3이상 4이하

또한 or와 같은 연산자와 in +('a','b'), between + 'a' + 연산자 +'b' 와 같이
중간 값과 복수 값 또한 출력값으로 보일 수 있다.

join

select * from student;

select * from course;

select * from enrol;

elect * from student join enrol
on student.sno = enrol. SNO;

select  s.sno, sname, cno, MIDTERM, FINAL, MIDTERM + FINAL as "총점" 
from student s join enrol e
on s.sno = e.SNO;

select * from course join enrol
on course.cno = enrol.cno;

select *
from course join enrol
on course.cno = enrol.cno
where sno='100';

select sno,c.CNO, cname, credit, dept, MIDTERM, FINAL
from course c join enrol e
on c.cno = e.cno
where sno='100';

select * from student s left join enrol e
on s.sno = e.sno left join course c
on c.cno = e.cno;

join을 사용하여 두개의 테이블을 묶어서 출력할 수 있다.
또한 칼럼명을 별칭을 두어 간단하게 표현이 가능했다.
EX) Student = S, course = C
별칭을 사용할 시에는 Select 구문에 사용하는 칼럼명 또한
동일하게 변경해 주어야 에러가 발생하지 않는다.

--학생 테이블 출력하시오
select * from student;

-- 학생테이블에서 학번과 이름만 출력하시오
select sno, sname from student ;

--학생 테이블에서 컴퓨터과 학생만 출력하시오
select * from student
where dept='컴퓨터';

-- 학생테이블에서 이름에 '이' 라는 글자를 가지고 있는 학생을 출력하시오
select * from student
where sname like '%이%';
-- like
-- 학생테이블에서 이름에 '이'와 과가 '전기과' 인글자를 가지고 있는 학생을 출력하시오
select * from student
where sname like '%이%'and dept like '%전기%';

-- 오름차순 정렬 (미 기입시도 오름차순)
select * from student order by sno asc; 

-- 내림차순 정렬
select * from student order by sno desc; 

--학과를 기준으로 내림차순 정렬 하시오. 
 동일한 경우 학번을 이용해 오름차순으로 정렬 하시오
select * from student order by dept desc, sno asc;
-- 중간번호 삭제 시 재정렬 하면 안됨 EX 100, 200, 300,400 중 
   300이 없어졌다고 그자리에 다시 300을 만들면 안됨 500으로 신규생성

Where문을 사용하여 테이블 내부의 일부 칼럼명의 값을 출력할 수 있으며,
테이블 내부 값을 오름차순 내림 차순 정렬또한 가능하다.
작성한 테이블 값 중 중간 테이블 값이 없어졌을 시에는
중간에 값을 다시 넣게되면 참조해야할 사항이 많아지므로
테이블의 신규 값으로 추가하는것이 바람직하다.

update

-- 학생 테이블의 학번이 '500'인 학생의 학년을 '7' 로 변경
update student set year = '7'
where sno='500';

select * from student;

-- 학번이 100인 학생의 학년을 '9' 학과를 '만화과'로 변경
update student set year = '9', dept='만화과'
where sno='100';

select * from student;

-- 값에 공백 문자 삽입
update student set year=' '
where sno='100';

select * from student;

-- 값에 Null 값 삽입
update student set year=''
where sno='100';

작성된 테이블에서 칼럼의 값을 변경하기 위해선 update문을 사용하였다.
update + 테이블명 + set + 칼럼명 = 값의 구문으로 사용된다.

delete

select * from student;
-- 학번이 701인 학생을 테이블에서 삭제
delete from student where sno=701;

select * from student;
delete from student where sno='700';

-- 삭제의 중요성 = 하나라도 삭제시 한줄의 레코드가 전부 삭제
delete from v_student 
where sno='600';

테이블의값을 삭제하는 delete문은
delete + from + 테이블명을 사용한다.
sql의 삭제문은 굉장히 조심스럽다.
삭제를 진행시에 하나의 칼럼을 삭제하려고 해도 테이블의 한레코드가
전부 삭제되기 때문에 삭제를 진행할때는 Select 문으로 확인 후
삭제할 내용을 다시한번 점검하는것이 바람직하다.

view

Create view v_student
as
select sno, year, dept from student;

select * from v_student;

create view v_join3
as
select s.sno,sname,year, s.dept, c.cno, cname, midterm, final 
from student s join enrol e
on s.sno = e.sno join course c
on c.cno = e.cno;

create view v_v_join3
as
select * from v_join3 where sno=100;

select * from v_v_join3;

create + view + (사용할)테이블명 + as + select + 칼럼명 + from + (기존)테이블명의
구문으로 작성하여 가상의 테이블을 작성할 수 있다.
기존 테이블의 내용을 동일하게 변경된 테이블 명으로 사용할 수 있기에 코딩의 길이를
간략하게 하여 시안성에 좋고 개발자의 편리함을 가져올 수 있다.
하지만 join이 된 view를 삭제 및 수정은 불가능 하다는 점이 있기에
꼭 생각을 해두어야 한다.

1일차 연습문제

오늘 작성한 테이블을 사용하여 간단한 문제를 작성해보았고,
문제에 대한 값을 구하며 숙지해보았다.
문제 및 정답을 알아보자.

1) 테이블 student 의 모든 레코드를 출력하시오.

 select * from student;

2) student 테이블에서 sno 와 sname 을 나타내시오.

 select sno, sname from student;

3) sno 가 100 인 학생을 출력하시오.

 select * from student
 where sno='100'; 

4) sno가 300인 학생의 sno 와 year 을 출력하시오.

 select sno,year from student
 where sno='300';

5) sno가 300 또는 400인 학생의 레코드를 출력하시오.

 select * from student
 where sno='300' or sno='400';
 
 select * from student
 where sno in ('300','400');

6) dept가 '컴퓨터' 이고 year 이 '4' 학년인 학생을 출력하시오.

 select * from student
 where dept='컴퓨터' and year='4';

7) 이름의 첫글자가 '이' 또는 '나' 또는 '영' 인 학생을 출력하시오.

 select * from student
 where sname like '%이%' or sname like '%나%' or sname like '%영%';
 
 select * from student
 where regexp_like(sname,'이|나|영');
 -- regexp_like = 정기 표현식에 포함된 단어 검색 

8) 학년을 기준으로 내림차순 정렬하시오.

 select * from student order by year desc;

9) 학번이 400번 이상인 학생의 이름을 출력하시오.

 select sname from student
 where sno >= 400;

10) 학번이 400번 이상이고 학년이 5학년 이상인 학생의 학번, 이름, 학년을 출력하시오.

 select sno,sname,year 
 from student
 where sno >= 400 and year >= 5;

11) 컴퓨터과가 아닌 학생을 출력하시오

select * from student
where dept!='컴퓨터';

select * from student
where dept<>'컴퓨터';

12) 컴퓨터과도 아니고 만화과도 아닌 학생을 출력하시오

select * from student
where dept != '컴퓨터' and dept != '만화과';

select * from student
where dept <> '컴퓨터' and dept <> '만화과';

select * from student
where dept not in ('컴퓨터', '만화과');

느낀점

이렇게 동일한 내용으로도 다양한 표현의 구문으로 작성이 가능하단 것을 알게되었다.
새로운 SQL의 구문들을 배우며 새로운 것을 배우는 것에 대한 흥미를 다시한번 느꼈고,
백엔드 개발자를 준비하며 가장 기초석이 되는 이런 내용들을 완전하게 다룰 수 있는
기본기를 다져야 겠다고 생각이 들었다.

앞으로 899시간의 기간동안 하루하루 배운 내용을 정리하며 다시 숙지하도록 하려한다.
꾸준하고 끈기있게 성장하는 개발자가 되도록 하자. 화이팅!