6월20일 - SW활용 Oracle

교재 chap 4,5

 

데이터타입과 데이터 무결성

 

1. 오라클 DB Server 서비스

 1) 제어판

     : 시작 -> 서비스검색 -> OracleServiceXE -> 시작 및 정지

 2) 오라클 자체 

     : 시작 -> 모든프로그램 -> Oracle Database 11g -> Start DB or Stop DB

 

2. 오라클 명령어 입력

 1) sqlplus 

    : 시작 -> cmd -> sqlplus java0514/1234

 2) Run Command Line  

    : SQL>connect

             아이디 : java0514

             비번 : 1234

             connected <- 뜨는지 확인할 것 

 3) SQL Developer         

     : 별도설치 (오라클 로그인 필요)

 4) 웹브라우저

     : 시작-> OracleServiceXE -> get Started 

       자기 자신의 pc를 지칭하는 IP와 도메인

       - 127.0.0.1 / localhost

       http://127.0.0.1:8080/apex -> http:// : 프로토콜 127.0.0.1 : ip 8080 : 포트번호

       http://localhost:8080/apex

 5) Eclipse                   

     : DataSource 연결

 

 

데이터명령어

DCL명령어   (Data Control Language 제어어)	사용자가 별로 쓸일이 없음.   호스팅업체가 주로 사용	- grant 사용자 접근 권한부여   - revoke 사용자 접근 권한 취소   - deny 특정사용자만 접근차단
DDL명령어   (Data Definition Language 정의어)	DB 및 TABLE에 대한 정의	- create 생성   - Drop 삭제   - Alter 수정
DML명령어   (Data Manipulation Language 제어어)	레코드 작업	- Select 조회 및 검색   - Insert 삽입   - Update 수정

 

SQL문 - DDL 명령어

 

1. sungjuk 테이블 삭제 

drop table sungjuk;

 

2. sungjuk 테이블 생성

create table sungjuk(

uname varchar(10) not null --빈값허용안함

,kor int not null

,eng int not null

,mat int not null

,aver int           --안적으면 빈값허용

);

 

3. sungjuk 테이블 구조 확인

desc sungjuk;

 

4. sungjuk 테이블 구조 수정

--칼럼명 수정

alter table 테이블명 rename column 원래컬럼명 to 바꿀컬럼명;

 

alter table sungjuk rename column kor to korea;

칼럼명 수정하기 

--칼럼명 삭제

alter table 테이블명 drop(칼럼명);

 

alter table sungjuk drop(korea);

칼럼명 삭제

--칼럼 추가

alter table 테이블명 add (컬럼명 데이터타입)

 

alter table sungjuk add (total int);

칼럼 추가하기

 

5. commit : 명령완료 

   -> commit;

    rollback : 명령취소

   -> rollback;

--sqlplus는 자동커밋된다. 

 

DDL명령어 연습문제) sungjuk 테이블을 아래와 같이 수정하시오 

1) uname 칼럼의 글자수를 50개로 수정

alter table sungjuk modify(uname varchar(50));

 

2) uname 칼럼을 null 조건으로 수정

alter table sungjuk modify(uname null); 

 

 

SQL문 - DML 명령어

 

*uname 칼럼을 20칸으로 변경

alter table sungjuk modify(uname varchar(20));

 

[sungjuk테이블 샘플데이터]

insert into sungjuk(uname,kor,eng,mat)
values ('홍길동',50,60,30);

insert into sungjuk(uname,kor,eng,mat)
values ('무궁화',30,30,40);

insert into sungjuk(uname,kor,eng,mat)
values ('진달래',90,90,20);

insert into sungjuk(uname,kor,eng,mat)
values ('개나리',100,60,30);

insert into sungjuk(uname,kor,eng,mat)
values ('라일락',30,80,40);

insert into sungjuk(uname,kor,eng,mat)
values ('봉선화',80,80,20);

insert into sungjuk(uname,kor,eng,mat)
values ('대한민국',10,65,35);

insert into sungjuk(uname,kor,eng,mat)
values ('해바라기',30,80,40);

 

insert into sungjuk(uname,kor,eng,mat) 
values ('장미',70,90,50);

 

insert into sungjuk(uname,kor,eng,mat) 
values ('코알라',60,90,30);

 

--칼럼명이 생략되면 values()값은 테이블 설계순으로 입력

insert into sungjuk

values('손흥민',90,60,60,70);

 

****

물리적 스키마(테이블)

 -> create, insert등을 직접한 값들 , 실제 존재하는 테이블 

논리적 스키마(테이블)

 -> 조인, 뷰등을 통해 만들어진 값들

****

 

1. 행 갯수                                                                                                                                             1) 목록조회

select * from sungjuk;

select kor, eng, mat from sungjuk;

select uname, aver from sungjuk;

 

 2) as (생략가능)

특정 칼럼명, 테이블명을 일시적으로 리네임

select kor as korea

,eng english

,mat

from sungjuk;

as

 3) count() 함수

 : null값은 카운트안한다

레코드 갯수(행갯수)

select count(uname) from sungjuk;

 

select count(uname) as cnt from sungjuk;

select count(uname) cnt from sungjuk;

 

select count(kor) as korea from sungjuk;

  ->null은 적용대상이 아니다

select count(aver) as average from sungjuk;

->average의 null 값은 카운트 안한다. 

 

 4) 전체 레코드(행) 갯수 

--만능문자 '*' '?' 같은 것 

select count(*) from sungjuk;

select count(*) as cnt from sungjuk; 

 5) 집계함수 

select sum(kor) ,max(kor), min(kor) from sungjuk;

select sum(kor) as hap, max(kor) as maximum, min(kor) as minimum, avg(kor) as average from sungjuk;

**문제 ) 국, 영, 수 각 과목의 평균점수를 조회하시오 

select avg(kor) avg_kor, avg(eng) avg_eng, avg(mat) avg_mat from sungjuk;

 6) 정렬(sort)

순서대로 재배치

오름차순 ASCending  ASE -> 기본값

내림차순 DESCending  DESC

형식) order by 칼럼1, 칼럼2, 칼럼3, ~~~

 

ex) 칼럼1을 기준으로 내림차순으로 정렬하고 

     칼럼1값이 동일하다면 칼럼2를 기준으로 오름차순 정렬하고

     칼럼2값도 동일하다면 칼럼3을 기준으로 오름차순으로 정렬

order by 칼럼1 desc,

칼럼2 asc,

칼럼3, ~~

 

 -1 오름차순

select uname

from sungjuk

order by uname;

 

select uname 
from sungjuk 
order by uname asc; --asc생략가능 기본값

오름차순

 -2 내림차순

select uname
from sungjuk
order by uname desc;

내림차순

select uname, kor

from sungjuk

order by kor desc; 

 ->kor점수 내림차순으로 정리하라

 

 -3 2차정렬(국어점수가 동일하다면 이름으로 정리하라)

select kor, uname

from sungjuk

order by kor,uname;

같은 점수 이름으로 정렬하기

****

칼럼은 select할 때 순서 중요하다.

****

 

 -4 3차정렬(kor점수-> eng점수-> mat점수 같은 순으로 정렬 )

select kor,eng,mat,uname

from sungjuk

order by kor,eng,mat;

 -5 3차정렬(kor점수-> eng점수-> mat점수-> uname 점수 같은 순으로 정렬 )

select kor,eng,mat,uname

from sungjuk

order by kor,eng,mat,uname;

 

 7) 조건절

   1) where 조건절 - 조건에 만족하는 레코드만을 대상

     -1 산술연산자 :  +  -  *  /

     -2 비교연산자 :  <  <=  >  >= 

                       같지않다 != (<>)

                       같다 = 

     -3 논리연산자 :  and  or  not  

   2) having 조건절 - gruop by절과 같이 사용

   3) on 조건절 - 테이블 조인할 때 사용

 

 8) where 조건절 

   1) 산술연산자

    -1 dual 테이블

       : 결과값을 일시적으로 출력할 때 유용한 임시 테이블 

 

select 5+3 from dual;

select 5-3 from dual;

select 5*3 from dual;

select 6/3 from dual;

select mod(5,3) from dual; --나머지 함수 

 

ex1) 국어점수 90이상 조회

select kor, uname

from sungjuk

where kor>=90;

ex2) 수학점수 50미만 조회

select mat, uname

from sungjuk

where mat<50;

ex3) 이름이 '무궁화' 조회

select uname

from sungjuk

where uname='무궁화';

ex4) 이름이 '무궁화' 행의 평균을 구하시오

update sungjuk

set aver=(kor+eng+mat)/3

where uname='대한민국';

 

select aver,uname from sungjuk;

select count(aver) from sungjuk;

 

**null값 주의!!

ex5) 비어있는 aver칼럼을 조회하시오 

select uname, aver

from sungjuk

//where aver=null; =로 표시하면 틀리다.

where aver is null; -> is 연산자 활용

비어있는 칼럼 조회 

ex6) 비어있는 aver칼럼의 갯수를 구하시오 

select count(*) 
from sungjuk

where aver is null;

//where aver is not null; -> 비어있지 않은 aver칼럼 갯수 구할 때엔 부정연산자 쓰면 된다.

비어있는 칼럼 갯수 

비어있지 않은 칼럼 갯수

 

**문제 ) 국영수 모두 50점미만인 행의 평균을 구하시오 

update sungjuk

set aver=(kor+eng+mat)/3

where kor<50 and eng<50 and mat<50;

 

select aver,kor,eng,mat,uname
from sungjuk;

 

**문제 ) 비어있는 평균값을 모두 채우시오 

update sungjuk
set aver=(kor+eng+mat)/3
where aver is null;

 

select aver,kor,eng,mat,uname
from sungjuk;

 

**문제 ) 평균 60~69점 사이의 평균, 이름 조회 

select aver, uname
from sungjuk
where aver>=60 and aver<=69;

 

select count(*)

from sungjuk

where aver>=60 and aver<=69;

 

--between A and B

select aver, uname

from sungjuk

where aver between 60 and 69;

and 연산자 사용한 것 & between and 연산자 사용한 것

**문제 ) 국영수 중에서 한과목이라도 30점 미만 조회 (과락) 

select kor,eng,mat
from sungjuk
where kor<30 or eng<30 or mat<30;

 -> 데이터의 갯수가 너무 많으면 분석이 힘드므로 , 적당히 나누어 확인하기.

 

 

**문제 ) 이름이 '진달래' , '개나리' , '라일락' 조회

select aver,uname
from sungjuk
where uname='진달래' or uname='개나리' or uname='라일락';

--in 연산자 (일치하는 값)

select aver, uname

from sungjuk

where uname in ('진달래' , '개나리' , '라일락');

 

 9) LIKE 연산자 

  - 문자열에서 만능문자(% _)를 이용해 비슷한 유형을 조회 

 

ex1) 홍씨성을 조회하시오 

select uname
from sungjuk
where uname like '홍%'; --홍으로 시작하는 문자열

select uname 
from sungjuk 
where uname like '%화';

select uname 
from sungjuk 
where uname like '%나%';

--두글자중에서 나로 끝나는 문자열 조회 

select uname
from sungjuk
where uname like '_나';

 

select uname 
from sungjuk 
where uname like '__나';  --세글자중에서 나로 끝나는 문자열 조회

 

select uname 
from sungjuk 
where uname like '_나'_; 

 

**문제 ) 자유게시판 검색시 제목+내용을 선택하고 검색어 happy를 입력했을 때 레코드를 조회하시오.

/내가 한 답 

select subjuct, content

from sungjuk

where subject in ('happy');

 

/선생님 답 

where subject like '%happy%' or content like '%happy%';