6월24일 - SW활용

저번주 과제 

문제 ) '이강인'의 국영수 점수와 평균 점수를 수정하시오

public static void main(String[] args) {
	// 문제) '이강인'의 국영수 점수와 평균 점수를 수정하시오
    String url     ="jdbc:oracle:thin:@localhost:1521:xe";
    String user    ="java0514";
    String password="1234";
    String driver  ="oracle.jdbc.driver.OracleDriver";

    Connection con=null;
    PreparedStatement pstmt=null;
    ResultSet rs=null;
   
	try{
        Class.forName(driver);
        con=DriverManager.getConnection(url, user, password);
        System.out.println("오라클 DB 서버 연결 성공!!");

		String uname="이강인";
		int kor=95, eng=40, mat=60;
		int aver=(kor+eng+mat)/3;
				
		StringBuilder sql=new StringBuilder();
		//전부 변수로 해주었기 때문에 이름부터 점수까지 다 ?로 해주어야한다.
		sql.append(" UPDATE sungjuk ");
		sql.append(" SET kor=?,eng=?,mat=?,aver=? ");
		sql.append(" WHERE uname=? "); 
			
		pstmt=con.prepareStatement(sql.toString());
		pstmt.setInt(1, kor);
		pstmt.setInt(2, eng);
		pstmt.setInt(3, mat);
		pstmt.setInt(4, aver);
		pstmt.setString(5, uname);
			
		int result=pstmt.executeUpdate();
		if(result==0){
			System.out.println("이강인 수정 실패!");
		}else{
			System.out.println("이강인 수정 성공~");
		}//if end
					
		}catch(Exception e){
			System.out.println("실패!"+e);
		}finally{
			//자원반납(순서주의~!)
			try{
				if(pstmt!=null){pstmt.close();}
			}catch(Exception e){}
			
			try{
				if(con!=null){con.close();}
			}catch(Exception e){}
		}//try end
		
	}//main() end
}//class end

 

DB 생성 및 삭제

*DB명은 한글로 적으면 절대 안된다.

- 데이터베이스 생성 : create database DB명

- 데이터베이스 삭제 : drop database DB명

- 테이블 생성 : create table 테이블명

- 테이블 삭제 : drop table 테이블명

 

시퀀스

참고교재 오라클 chap4 / chap12

 

: 오라클에서 자동으로 일련번호를 부여

 

1) 시퀀스 생성

crate sequence 시퀀스명

increment by 증가값

start with 시작값;

2) 시퀀스에서 일련번호 발생

시퀀스명.nestval

 

3) 시퀀스 삭제

drop sequence 시퀀스명;

 

4) sungjuk테이블에서 사용할 시퀀스 생성

create sequence sungjuk_seq;

-삭제

drop sequence sungjuk_seq;

 

DB의 자료형 p.70

1) 숫자형

   int               표준

   number        정수형

   number (5,2)  실수형 (999.99) ex) 5은 자릿수(소숫점 제외), 2는 점의 위치

2) 문자형

   char             표준. 고정형

    -> char(5) 'SKY  ' (길이가 정해져있다, 공백으로 표시)

    -> 우편번호  

   varchar         표준. 가변형

    -> varchar(5) 'SKY'

    -> 아이디, 비번, 이름, 주소~~

   varchar2       가변형

3) 날짜형 (년월일시분초)

   date

   sysdate : 현재 시스템 날짜 함수

 

 

-sungjuk 테이블 생성

create table sungjuk(
sno      number             not null--일련번호
,uname varchar2(255)      not null--이름
,kor      number             not null--국어
,eng     number             not null--영어
,mat     number             not null--수학
,aver     number                       --평균
,addr     varchar2(255)               --주소
,wdate   date                           --작성일
);

-행추가 

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)

values(sungjuk_seq.nextval,'무궁화',100,95,80,'Seoul',sysdate); --시퀀스에서 하나씩 빼기

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'진달래',90,50,90,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'개나리',20,50,20,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'봉선화',90,90,90,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'나팔꽃',50,50,90,'Suwon',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'선인장',70,50,20,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'소나무',90,60,90,'Busan',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'참나무',20,20,20,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'홍길동',90,90,90,'Suwon',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'무궁화',80,80,90,'Suwon',sysdate);

 

문제)
1) 이름 '무궁화'를 조회하시오
select uname from sungjuk where uname='무궁화';

2) 국어점수 50이하 조회하시오
select kor from sungjuk where kor<=50;

3) 국영수 모두 90점 이상 조회하시오
select kor,eng,mat from sungjuk where kor>=90 and eng>=90 and mat>=90;

4) 이름 개나리, 진달래, 라일락 조회하시오
//select uname from sungjuk where uname='개나리' and '진달래' and '라일락';
select uname from sungjuk where uname in ('개나리','진달래','라일락');

5) 주소가 서울인 레코드의 평균을 구하시오
update sungjuk set aver=(kor+eng+mat)/3 where addr='Seoul';

select addr,aver from sungjuk where addr='Seoul';

6) 수학점수 50~59사이 레코드를 조회하시오
select mat from sungjuk where mat between 50 and 59;

7) 비어있는 평균값을 모두 구하시오
update sungjuk set aver=(kor+eng+mat)/3 where aver is null;
select aver, uname from sungjuk;

8) 평균 70점이상 레코드를 이름순으로 정렬해서 조회하시오

select sno,uname,aver from sungjuk where aver>=70; 

select sno,uname,aver from sungjuk where aver>=70 order by uname asc; --asc생략가능

 

ex) 게시판테이블에서 가장 최근에 작성한 글 순서로 출력

order by wdate desc; --내림차순

 

9) aver칼럼의 데이터타입 number(6,2)으로 수정하시오

alter table sungjuk modify(aver number(6,2));

에러떴음 ! 왜냐면 modify를 하려면 empty상태를 만들어주고 고쳐야하는데 그게 안되니 

구조수정(다 비우고 하면 된다)

 

*재실행

update sungjuk set aver=null; ->null이 아닌 0으로 한다면 안됨. 

altet table sungjuk modify(aver number(6,2));

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

select sno,sname,aver from sungjuk;

 

 

**cmd창에서 출력칸수 조절하기

col uname for a8;  ->uname 칼럼을 8칸내에서 출력

col addr for a6;     ->addr 칼럼을 6칸내에서 출력

select * from sungjuk;

 

10) 수학점수 50점 미만 학생들에게 수학점수 5점씩 추가하시오
update sungjuk set mat=mat+5 where mat<50;

11) 이름에 '화' 문자가 들어가 있는 레코드만 검색
select sno,uname from sungjuk where uname like '%화';

12) 주소가 'Seoul','Jeju'이면서
    이름에 '나'글자가 포함되어 있는 레코드 조회
select sno,uname,addr from sungjuk where uname like '%나%' and (addr='Seoul' or addr='Jeju'); 

13) 국영수 과목의 각 평균을 구하시오
select avg(kor) as avg_kor,
avg(eng) as avg_eng,
avg(mat) as avg_mat from sungjuk;

 

14) 국영수 과목의 각 평균을 반올림해서 소수점 두자리까지 출력하시오

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

 

create table emp (
empno int not null
,ename varchar(10) not null
,job varchar(10) not null
,mrg char(4) not null
,hiredate date not null
,sal int not null
,comm int 
,deptno int not null
);

insert into emp(empno,ename,job,mrg,hiredate,sal,comm,deptno)
values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);

insert into emp(empno,ename,job,mrg,hiredate,sal,comm,deptno)
values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);

insert into emp(empno,ename,job,mrg,hiredate,sal,comm,deptno)
values(7521,'ward','salesman',7698,'1981-02-22',1200,500,30);

 

DB 

연결문자 ||

 : MySQL -> concat() 함수

select '#' || addr || '#'

from sungjuk;

 

오라클함수

select lower('Hello World') from dual;
select upper('Hello World') from dual;
select concat('Helllo', 'World') from dual;
select substr('HelloWorld', 6) from dual;
select substr('HelloWorld', 1, 5) from dual;
select instr('HelloWorld', 'W') from dual;
select trim(' SQLPLUS') from dual;
select ltrim('*SQLPLUS', '*') from dual;
select rtrim('SQLPLUS*', '*') from dual;
select replace('SEVLTL', 'L', 'EN') from dual;

select abs(-7) from dual; 

select mod(1500,200) from dual; 

select ceil(1.123) from dual; 

select ceil(-1.623) from dual; 

select floor(1.123) from dual; 

select round(17.825,2) from dual; 

select round(17.825,0) from dual; 

select round(17.825,-1) from dual; 

select trunc(17.825,2) from dual; 

select trunc(17.825,0) from dual; 

select trunc(17.825,-1) from dual;