初め丸

DML

모든 테이블 목록 보기

SQL> select * from tab;

유저리스트 목록 보기

SQL> select username from dba_users;

sql 파일 이름을 설정하고 create버튼을 누른다

사용하는 DB를 고른다.

마커의 버튼을 누른다

오라클의 사용하는 버전을 고른다

ojdbc14.jar을 선택하고 Edit JAR/ZIP 버튼을 눌러 ojdbc14.jar 파일이 있는 경로를 지정한다.

1) SID : 오라클 사용버전을 설정 (xe 버전)

2) 호스트와 사용자 아이디/ 비번을 넣는다.

3) testConnection 버튼을 눌러 성공했는지 팝업이 뜬다.

SQL문을 작성하고 실행하고자하는 SQL문에 블럭을 씌우고 

Alt + x를 누르면 SQL문이 실행한다.

SQL 강좌

http://blog.naver.com/makand123/30174508916

IN 구문 

 IN을 사용하면 여러값을 지정하여 검색할 수 있다.

예)

//  user_id컬럼의 test1의 레코드를 가져와라.

select * from member where user_id = 'test1';

// user_id컬럼의 test1과 test4의 모든 레코드를 가져와라.

select * from member where user_id IN('test1', 'test4') ;

//반대로 test1과 test4의 값을 제외하고 모든 레코드를 가져와라.

select * from member where user_id  NOT('test1', 'test4') ;

AS구문

as 구문은 테이블 또는 컬럼에 대해서 임시로 이름을 변경할 수 있다.

예)

select user_id AS uid, user_pwd AS pwd from member;

// 구문을 살펴보면 user_id AS uid 라고 변경했고 user_pwd AS pwd 라고 변경했다.

// 참고로 AS구문은 생략이 가능한다. 

// select user_id uid, user_pwd pwd from member;

테이블 열 합치기

예)

select user_id, name+', '+age+', '+phone as info from member;

// 위 예제는 여러가지의 테이블 열을 합쳐 하나의 내용으로 묶어서 가져오게 하는 구문이다.

// Join 정리_______________________________________________________________________//

- join은 초급단계에서는 inner join , left-outer-join 이 두가지만 알면 된다.

- 조인이란 테이블을 합치는 것

select * from sawon;

select * from sawon, dept;

-- inner join --

select * from sawon,dept where sawon.deptno=dept.deptno

select * from sawon inner join dept on sawon.deptno=dept.deptno

--left outer join --

--(+)는 null값까지 출력한다는 의미 (오라클 전용 문법이다)

-- (+)는 left outer join을 의미한다.

-- 문장의 의미 : s1.samgr과 s2.sabun(+)이 같은 것을 출력해라. 

select * from sawon s1,sawon s2 where s1.samgr=s2.sabun(+)

order by s1.sabun

select * from sawon,gogek where sawon.sabun = gogek.godam(+)

order by sabun

// 강의 내용________________________________________________________________________//

JOIN: 테입블 결합

: 정규화(테이블분리)된 테이블 사용

[조건체크 형태]

EQUI-JOIN

: 필드 = 필드

NON-EQUI-JOIN

: All ,Some,Any,...

  필드 some(값1,값2,값3)

  필드 ALL (값1,값2,값3)

[조인방식 따른]

--cross join : 모든행을 결합

select * from sawon,dept

[ANSI-92]

select * from sawon cross join dept

--inner join : 두테이블간 만족하는 행만 결합

select * from sawon,dept where sawon.deptno=dept.deptno

[ANSI-92]

select * from sawon inner join dept on sawon.deptno=dept.deptno

--outer join : 조건이 null인 데이터를 결합하지 않는다...

 1) left  outer join :  A left outer join B on 조건 => A행은 모두추출 / B행은 만족하는행만

 2) right outer join :  B right outer join A on 조건

--self join : 자신의 테이블과 결합

-- left outer join(oracle (+))   cf) ms-sql : s1.samgr *= s2.sabun

-- oracle에서만 사용되는 문버

select * from sawon s1,sawon s2 where s1.samgr=s2.sabun(+)

order by s1.sabun

[ANSI-92]

select * from  sawon s1 left outer join sawon s2 on s1.samgr=s2.sabun

--사원이 관리하는 고객정보 출력(사원정보는 모두보겠다)

select * from sawon,gogek where sawon.sabun = gogek.godam(+)

order by sabun

[ANSI-92]

 오라클에 JDBC 접근 가장 기초 틀 예문

Main Class

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.util.ArrayList;

import java.util.List;

import GogekVO;

public class yMain {

// STEP1. DB driver 초기화___________//

// 정형화된 공식

//_______________________________________//

static {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void main(String[] args) throws Exception{

// STEP2. 연결______________________//

Connection conn = null;

//____________________________________//

// STEP3. 접근______________________//

//_____________________________________//

// DB에 접근하기 위한 정보가 필요하다.

String url = "jdbc:oracle:thin:@localhost:1521:xe";

conn = DriverManager.getConnection(url, "test", "test");

System.out.println("--connect success!!---");

// STEP4. 명령처리 객체 (기본적으로 AutoCommit으로 설정되어있다)______//

//___________________________________________________________________//

Statement stmt = null;

stmt  = conn.createStatement();

/*

* 설명

* 모든 SQL 명령을 처리(stmt) :DDL, DCL, DML .

* select : executeQuery(SQL)

* insert, delete, update : exexcuteUpadate(SQL);

* stmt.executeQuery("create table tsb3(no int, name varchar2(12))");

* stmt.executeQuery("drop table tsb1");

*/

stmt.executeQuery("insert into tsb3 values(1, '민우')");

// 결과행 처리객체 //

ResultSet rs = null;

rs = stmt.executeQuery("select * from tsb1");

// 데이터 그릇 준비

//<> 은 제네릭으로서, 지정한 객체만 받겠다는 의미이다.

List<GogekVO> list = new ArrayList<GogekVO>();

//List list = new ArrayList();

while(rs.next()){

int rsNo = rs.getInt("NO"); // 필드명은 가급적 대문자로 쓴다.

String rsName = rs.getString("NAME");

/*

// 임시로 데이터를 출력

System.out.print(rsNo + "   ");

System.out.print(rsName);

System.out.println();

*/

// 모든행을 읽어와서 읽기

GogekVO vo = new GogekVO(rsNo, rsName);

list.add(vo);

}

// 출력

for (int i = 0; i < list.size(); i++) {

GogekVO vo = list.get(i);

System.out.print(vo.getNo()+"  ");

System.out.print(vo.getName());

System.out.println();

}

// STEP5. 닫기______________________//

//_____________________________________//

stmt.close();

conn.close();

}

}

GogekVO Class

// VO 객체 : 값을 저장관리하는 객체(Value Object)

// VO는 가급적 getter / setter를 만들도록한다.

public class GogekVO {

// DB 필드명과 동일하게 변수명을 부여하면 편리하다.

int no;

String name;

public GogekVO(int no, String name){

this.no = no;

this.name = name;

}

// GET / SETTER_____________________//

public int getNo() {

return no;

}

public void setNo(int no) {

this.no = no;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

}

select_연산자_함수

select  필드1,필드2,..      <=컬럼(필드)선택

from    테이블(뷰)

where   조건절              <=행(레코드)선택 

order by 필드 [asc | desc]  <=정렬방식

--[연산자]-------

산술 : + - * /

관계 : > >=  <  <=  =(같냐?)  !=  <>(같지않냐?)  not(부정)

논리 :  A and B   

        A or  B

기타 : 필드 between A and B    <= A ~ B사이냐?

       필드 in(A,B,C)          <=  필드=A ro 필드=B or 필드=C

문자열 결합 : ||

문자열 유사검색 : 필드 like '%_'

                  _ : 문자 1자리

                  % : 모든

문자열 함수  : substr(문자열,시작,갯수)

         1 2 3 4 5 6 7 8

select '우리나라대한민국' from dual;

                               시작,갯수 

select substr('우리나라대한민국',5,2) from dual;

////////////////////////////////////////////////////////////////

--연산자 <=가상의 임시 테이블(dual)

--         heading(연산결과에 대한 필드명 부여)

--     필드 as alias(별명) 

--     필드    alias(별명) 

--     필드    "escape문자포함"   

select 1+1 as plus ,(2-1) minus1 from dual;

--사원테이블

select * from sawon;

select sabun,saname,deptno,sajob , sapay, sapay*0.1 bonus from sawon;

select saname || '님의 년봉은 '  || sapay || '입니다' as "년봉 설명" from sawon

select sabun 사번,saname 이름,deptno 부서번호,sajob 직위,

       sahire 입사일자,sapay 년봉,samgr 부서장

from sawon

order by deptno ;

--사원테이블 10 부서 직원을 모두 추출

select * from sawon where deptno=10 ;

--사원테이블 남자 직원을 모두 추출

select * from sawon where sasex='남자';

--사원테이블 2000년이후 입사자를 추출 : 2000.1.1 00:00:00

--날짜 표현방법

select * from sawon where sahire >= '2000-1-1';

--입사일자 2000 ~ 2003년인 직원 추출

--         2000.1.1 00:00:00 ~ 2003.12.31 23:59:59

--                             '2003-12-31 00:00:00'

select * from sawon

where sahire>='2000-1-1' and sahire<'2004-1-1';

--날짜에서는 between사용하지 말것(왜? : date타입은 시간까지 고려)

select * from sawon

where sahire between '2000-1-1' and '2004-1-1';

--급여가  2000 ~ 3000사이인 직원 추출

select * from sawon

where sapay>=2000 and sapay<=3000 ;

select * from sawon

where sapay between 2000 and 3000 ;

--10번 부서에서 남자 직원중 직급이 사원을 추출

select * from sawon

where deptno=10 and sasex='남자'  and  sajob='사원';

--10,30번 부서직원 추출

select * from sawon

where deptno=10 or deptno=30 ;

select * from sawon

where deptno in(10,30) ;

--직급이 과장,대리,사원인 직원을 모두 추출

select * from sawon

where sajob='과장' or sajob='대리' or sajob='사원' ;

select * from sawon

where sajob in('과장','대리','사원') ;

--직급이 과장,대리,사원인 직원을 빼고 모두 추출

select * from sawon

where sajob not in('과장','대리','사원') ;

select * from sawon

where not( sajob='과장' or sajob='대리' or sajob='사원') ;

------[문자열 유사검색]---------------------------

-- 성씨가 '최'씨인 직원을 모두 추출

select * from sawon 

where  saname like '최%';

--이름중에 2번째 글자가 '미'인 직원을 추출

select * from sawon

where saname like '_미%'; 

--고객테이블 '서울'사는 고객을 추출

select * from gogek

where goaddr like '서울%';

--고객테이블에서 강서구 거주자 추출

select * from gogek

where goaddr like '%강서구%';

--고객테이블에서 여자만 추출

select * from gogek

where gojumin like '______-2%' 

      or

      gojumin like '______-4%' ;

select * from gogek

where substr(gojumin,8,1) in('2','4');

--고객테이블에서 80년대 출생자   추출   

select * from gogek

where gojumin like '8%';

select * from gogek

where substr(gojumin,1,1)='8' ;

--고객테이블에서 계울에 출생한 고객 추출

select * from gogek

where gojumin like '__01%' or 

      gojumin like '__02%' or

      gojumin like '__12%'    ;

select * from gogek

where substr(gojumin,3,2)='01' or

      substr(gojumin,3,2)='02' or 

      substr(gojumin,3,2)='12' ;

select * from gogek

where substr(gojumin,3,2) in('01','02','12');

SubQuery(하위쿼리)

: 하위쿼리의 결과값을 이용해서 주쿼리를 수행

-- 최불암과 동일한 부서 직원을 추출

select * from sawon 

where deptno = 10;

select deptno from sawon where saname='최불암';

--2개의 쿼리를 결합

select * from sawon 

where deptno=(select deptno from sawon where saname='최불암') ;

--장동건과 연봉이 동일한 직원 추출

select * from sawon 

where sapay = (select sapay from sawon where saname='장동건');

--이미자보다 연봉을 많이 받는 직원을 추출

select * from sawon 

where sapay > (select sapay from sawon where saname='이미자');

--주의) 하위쿼리의 결과가 여러개 발생시 => in연산자 사용

--최불암 ,이미자의 소속부서 직원을 추출

select * from sawon

where deptno in (select deptno from sawon where saname='최불암' or saname='이미자') ;

--where deptno in (10,20) ; <=해석

select * from sawon

where deptno = (select deptno from sawon where saname='최불암')

      or

      deptno = (select deptno from sawon where saname='이미자') ;

--고객테이블 

select * from gogek;

select length(goname) from gogek;

--마징가의 이름과 동일한 길이의 이름을 갖는 고객 추출

select * from gogek 

where length(goname)=(select length(goname) from gogek where goname='마징가');

select length(goname) from gogek where goname='마징가';

--류민과 거주지가 동일지역(광역시)의 고객 추출      

select * from gogek 

where substr(goaddr,1,2)=(select substr(goaddr,1,2) from gogek where goname='류민');

select substr(goaddr,1,2) from gogek where goname='류민'      

-- 몰리와 동일한 출생월을 갖는 고객을 추출

select * from gogek

where substr(gojumin,3,2) = 

                (select substr(gojumin,3,2) from gogek where goname='몰리');

[제약조건]: Constraint

1.데이터의 무결성을 위해서 사용(Data Integrity)

2.제약조건은 테이블정의시 필드에 설정

3.종류

  1) not null  : 필수입력

  2) unique    : 고유해야된다.(도메인내에 동일한 값을 저장할 수 없다)

  3) check     : 유효성(범위,값...) <=테이블내에서 검색조건을 부여

  4) foreign key   : 다른테이블에서 검색조건 검사

  5) primary key   : not null + unique + index 결합형태 

  6) default   : 기본값이 입력

--1] not null 연습

--drop table tb1; --테이블 삭제

create table TB1

(

   no int not null ,

   name varchar2(20) not null

)

insert into tb1 values(1,'일길동');

--insert into tb1(no) values(2);

--insert into tb1(name) values('이길동');

select * from tb1;

--2] unique 연습

create table tb2

(

  no int not null unique,

  name varchar2(20) not null

)

insert into tb2 values(1,'일길동');

--insert into tb2 values(1,'이길동'); -- X

insert into tb2 values(2,'일길동');

select * from tb2;

--테이블 구조확인

desc tb2;

desc user_constraints;

--제약조건 확인

select constraint_type,constraint_name from user_constraints

where table_name='TB2';

select * from user_constraints where table_name='TB2';

--3[check제약]

-- primary key : 테이블당 1개만 존재(기본키)

create table tb3

(

   no int primary key, -- (not null + unique + index)

   name varchar2(20) not null,

   kor  number(3) check( kor>=0 and kor<=100),

   eng  number(3),

   mat  number(3)

)

--필드 수정                                eng>=0 and eng<=100동일 

alter table tb3 modify eng number(3);

--제약조건 추가

alter table tb3 

      add constraint ck_eng  check(eng between 0 and 100);

alter table tb3 

      add constraint ck_mat  check(mat between 0 and 100);

insert into tb3 values(1,'일길동',55,77,88);

--insert into tb3 values(2,'이길동',200,55,77); --check제약 위반

insert into tb3 values(2,'이길동',88,30,500);

delete from tb3 where no=2;

select * from tb3;

create table tb4

(

   no int primary key,

   name varchar2(20) not null,

   sex  varchar2(20) check(sex='남자' or sex='여자'),

   job  varchar2(30)

)

--제약추가                    -- job='부장' or job='차장' or job='과장' .....

alter table tb4

      add constraint ck_job check( job in('부장','차장','과장','대리','주임','사원') );

insert into tb4 values(1,'일길동','남자','대리');

--insert into tb4 values(2,'이길동','남','대리');

--insert into tb4 values(2,'이길동','여자','대 리');

--insert into tb4 values(2,'이길동','여자','대리 ');

delete from tb4 where no=2;

select * from tb4;

commit;

--4 [default] :기본값

create table tb5

(

   no int primary key,

   name varchar2(20) not null,

   sex  varchar2(10) default '남자'  --입력되지 않으면 기본값

)

insert into tb5 values(1,'일길동','여자');

insert into tb5(no,name) values(2,'이길동');--입력하지 말던지

insert into tb5 values(3,'삼길동',default); --default

insert into tb5 values(4,'사길동','남자',20);

--필드추가

alter table tb5  add age int default 0;

select * from tb5;

--5 [foregin key]

--부서테이블

create table dept

(

   dno   int primary key,

   dname varchar2(30),

   loc   varchar2(30)

)

insert into dept values(10,'인사부','205');

insert into dept values(20,'영업부','305');

insert into dept values(30,'경리부','505');

select * from dept;

--직위테이블

create table job

(

   job_no int primary key, --직위번호

   job_name varchar2(20),  --직위명

   job_pay  int            --급여 

)

insert into job values(100,'부장',3000000);

insert into job values(101,'차장',2500000);

insert into job values(102,'과장',2000000);

insert into job values(103,'대리',1800000);

insert into job values(104,'주임',1500000);

insert into job values(105,'사원',1000000);

select * from job;

create table emp

(

  emp_no    int primary key, --사번

  emp_name  varchar2(30),

  dno       int   references dept(dno)

)

insert into emp values(1,'일길동',10);

insert into emp values(2,'이길동',40);

alter table emp add job_no int;

--외래키 설정

alter table emp

   add constraint fk_job_no foreign key(job_no) references job(job_no);

--desc emp;

update emp set job_no=105 where emp_no=1;

insert into emp values(3,'삼길동',20,103);

--insert into emp values(4,'사길동',20,303);

select * from emp;

테이블생성 및 DML 기초

--오라클에서 사용되는 자료형

문자형: char(크기)     <= 고정크기  char(20)     'abc' =>전체공간 20개 잡힌다.

        varchar2(크기) <= 가변크기  varchar2(20) 'abc' =>전체공간 3개 잡힌다.

                       <= 최대크기  4000  한글=>3byte

        clob           <= character large object binary(문자 대용량) 

                       <= 용량제한 없다(저장공간이 허용한는 한:2G~4G)

숫자형: number(길이)                       

        number(전체크기,소숫점)

        int  == number(38) 와 동일

날짜형: date   <= 년월일시분초     

--테이블 생성 : 데이터를 저장할 수 있는 물리적구조(DDL)

create table sungjuk

(

    no     int,  --필드명  자료형순으로 선언

    name   varchar2(100),

    kor    number(3), --  0 ~ 999까지 저장

    eng    number(3),

    mat    number(3) 

)

--데이블 구조 확인하기...

desc sungjuk;

--[데이터 추가(DML)]

insert into sungjuk values( 1,'일길동' , 88 , 99, 77 );

--입력되는 필드를 나열

insert into sungjuk(name,no,kor,eng,mat) values('이길동',2,99,44,66);

--null

insert into sungjuk(no,kor,eng,mat) values(3,88,44,55);

commit; -- insert된 내용을 DB에 적용(저장) <= insert,update,delete만 Transaction에 저장

rollback; -- 현재까지 DML명령을 취소

--[데이터 수정(DML)]

--      테이블    필드=수정값      조건절

update sungjuk set kor = 100     where no=1;

--이런경우 이름이 이길동인 레코드가 모두 수정된다(주의)

update sungjuk set mat = 100     where name='이길동'; 

--[데이터 삭제(DML)]

--          테이블        조건절

delete from sungjuk where no = 2;

--조회

-- * : 모든필드를 추출

select  추출필드목록  <=필드선택

from    테이블(뷰)

where   조건절        <=레코드선택 (생략되면 모든레코드)

select * from sungjuk;

select no,name,kor,eng,mat ,

       kor+eng+mat tot,         --계산(연산)필드

       (kor+eng+mat)/3 as ave

from sungjuk;