codemon

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

관계형 데이터베이스에서 SQL을 이용해 관계를 맺는 것을 '조인'이라고 합니다. 테이블 간의 연결 고리로 관계를 맺고 데이터를 추출하는 방법입니다. 테이블의 기본키와 외래키가 연결고리가 됩니다.

조인의 종류에 대해 알아보겠습니다. 조인은 크게 내부 조인과 외부 조인으로 구분이 됩니다. 

내부 조인에는 동등 조인, 세미 조인, 안티 조인, 셀프 조인이 있습니다. 

하나 하나 알아보겠습니다. 

동등 조인 

WHERE절에서 등호 연산자를 사용해 2개 이상의 테이블이나 뷰를 연결하는 조인입니다. 

세미 조인

서브 쿼리를 사용해 서브 쿼리에 존재하는 데이터만 메인 쿼리에서 추출하는 조인입니다. 

세미 조인은 최종 반환되는 메인 쿼리 데이터에 중복되는 건이 없습니다.

안티 조인

서브 쿼리의 B 테이블에는 없는 메인 쿼리의 A 테이블의 데이터만 추출하는 조인입니다.

셀프 조인

동일한 한 테이블을 사용해 조인하는 방법입니다. 

외부 조인

조인 조건에 만족하는 데이터 뿐만 아니라, 어느 한 쪽 테이블에 조인 조건에 명시된 컬럼에 값이 없거나 (NULL이라도) 해당 로우가 아예 없더라도 데이터를 모두 추출하는 것입니다.  데이터가 없는 테이블의 컬럼에 (+) 표시를 붙이는 방식이고 조인 조건이 여러 개일 때 모든 조건에 (+) 표시를 붙여야 합니다.

카타시안 조인

FROM절에는 2개 이상의 테이블이 명시되어 있지만 WHERE절에는 조인 조건이 없는 조인입니다.  결과는 두 테이블의 건수의 곱만큼 나오게 됩니다.

ANSI 조인

ANSI SQL 문법을 사용한 조인으로 조인 조건이 WHERE절이 아닌 FROM절에 위치하는 것입니다.

ANSI 내부 조인

ANSI 외부조인 

다음 코드를 보면 데이터가 더 많은 쪽이 왼쪽에 쓰여진 테이블(employees)이기 때문에 LEFT [OUTER] JOIN이라고 합니다.

CROSS 조인은 기존 카타시안 조인을 ANSI 조인에서 쓴 것입니다.

FULL OUTER 조인은 외부 조인의 하나로 ANSI 조인에서만 제공하는 것입니다. 두 테이블 모두 기준이 되어 부족한 것들까지 다 나오도록 하는 방식입니다. 

서브 쿼리

SQL 문장 안에서 보조로 사용되는 또 다른 SELECT문을 의미합니다. 서브 쿼리는 여러 개를 사용할 수 있습니다. 서브 쿼리를 구분해보면 다음과 같이 나눌 수 있습니다.

- 연관성이 없는 서브쿼리

  : 메인 쿼리와 조인 조건이 걸리지 않는 서브쿼리로 단지 순차적으로 처리 되는 경우

- 연관성이 있는 서브쿼리

  : 메인 테이블과 조인 조건이 걸린 서브쿼리

- 일반 서브쿼리(SELECT절)

- 인라인 뷰(FROM절)

- 중첩쿼리(WHERE절)

[출처] DB - 조인과 서브 쿼리|작성자 DeveloperBee

SQL을 작성하다 보면 테이블에 저장된 데이터를 원하는 목적에 맞게 집계해서 데이터를 추출해야 할 때가 많은데 이때 사용하는 것이 그룹 쿼리입니다.

그룹 쿼리란, 특정 항목별로 그룹을 지어 정보를 추출할 때 사용하는 SQL 구문으로 집계 함수와 GROUP BY절의 결합이라고 할 수 있습니다.

먼저 기본 집계 함수에 대해 알아보겠습니다.

우선 집계 함수란, 대상 데이터를 특정 그룹으로 묶은 다음 이 그룹에 대해 총합, 평균, 최댓값, 최솟값 등을 구하는 함수를 말합니다.

COUNT(expr)

쿼리 결과 건수, 즉 전체 로우 수를 반환하는 함수입니다. expr에 * 또는 컬럼명이 올 수 있고 NULL이 아닌 것만을 체크하게 됩니다.

DISTINCT

중복을 제거할 때 사용하여 유일한 값만을 조회하게 됩니다. 중복된 값을 제거하지만 NULL인 것도 하나로 체크하게 됩니다.

SUM(expr)

expr의 전체 합계를 반환합니다. expr은 숫자형만 올 수 있습니다.

AVG(expr)

expr의 평균 값을 반환합니다.

MIN(expr) / MAX(expr)

최솟값과 최댓값을 반환합니다.

VARIANCE(expr) / STDDEV(expr)

분산과 표준편차를 반환합니다. 분산이란 개별 값과 평균 값의 차이인 편차를 구해 이를 제곱하여 평균한 값을 의미합니다. 표준 편차는 이 분산 값의 제곱근을 의미합니다.

다음으로 GROUP BY절과 HAVING절에 대해 알아보겠습니다.

GROUP BY절이란, 

특정 그룹을 묶어 데이터를 집계하는 것을 말합니다. WHERE와 ORDER BY절 사이에 위치하며 그룹으로 묶을 컬럼명이나 표현식을 명시하게 됩니다. 집계함수가 아닌 컬럼과 집계함수를 같이 조회하는 경우 반드시 일반 컬럼은 GROUP BY절에 명시가 되어야 합니다.

HAVING절은 GROUP BY절 다음에 위치해서 GROUP BY한 결과를 대상으로 다시 필터를 거는 역할을 합니다.

GROUP BY절에서 그룹별 소계를 추가로 보여주는 역할을 하는 ROLLUP과 CUBE절이 있습니다. 이 절들에는 집계 함수를 제외한 컬럼 등의 표현식이 올 수 있습니다.

ROLLUP(expr1, expr2,...)은 명시한 표현식 수와 순서(오른쪽 -> 왼쪽)에 따라 레벨별로 집계한 결과를 반환합니다.

CUBE(expr1, expr2,...)는 명시한 표현식 개수에 따라 가능한 모든 조합별로 집계한 결과를 반환합니다.

추가적으로 분할(partial) ROLLUP과 분할 CUBE도 있습니다.

마지막으로 집합 연산자에 대해 알아보겠습니다.

집합 연산자란,

데이터 집합(쿼리 조건에 맞아 조회된 일련의 데이터)을 대상으로 연산을 수행하는 연산자로 여러 SELECT문을 연결해 또 다른 하나의 쿼리를 만드는 역할을 합니다.

UNION

합집합과 같은 의미로 각 집합 원소 (SELECT 결과)를 모두 포함한 결과를 반환합니다. 여기서 중복되는 값은 한번만 조회됩니다.

UNION ALL

UNION과 비슷하지만 중복되는 값을 각각 하나로 조회한 결과를 반환합니다.

INTERSECT

교집합과 같은 의미로 데이터 집합에서 공통된 항목만 추출한 결과를 반환합니다.

MINUS

차집합과 같은 의미로 한 데이터 집합을 기준으로 다른 데이터 집합과 공통된 항목을 제외한 결과만 추출하여 반환합니다.

집합 연산자를 사용할 때 제한 사항은 다음과 같습니다.

  - 집합 연산자로 연결되는 각 SELECT문의 SELECT 리스트의 개수와 데이터 타입 일치

  - 집합 연산자로 SELECT문을 연결할 때 ORDER BY절은 맨 마지막 문장에서만 사용 가능

  - BLOB, CLOB, BFILE 타입의 컬럼에 대해서는 집합 연산자를 사용할 수 없음

  - UNION, INTERSECT, MINUS 연산자는 LONG형 컬럼에는 사용할 수 없음

그 외 GROUPING SETS절이 있는데 이는 GROUP BY절에서 사용되고 UNION ALL 개념이 셨여 있다고 할 수 있습니다. 간단히 설명하면 GROUPING SETS(expr1, expr2, expr3)이면 세 표현식 별로 각각 집계가 이루어진다는 것입니다.

이유는 Statement 객체에서 사용한 createStatement()라는 메소드 때문입니다. 이것을 사용할 경우 사용자의 입력 값을 미리 만들어 놓은 sql문에 적용한 후 컴파일을 하기 때문에 사용자의 입력 값에 따라 쿼리문의 형태가 바뀔 수 있어 보안에 취약합니다. 사용자가 입력 값과 함께 'OR 1=1'과 같은 코드를 함께 전달할 경우 모든 사용자의 정보 등이 노출될 수 있기 때문입니다.

따라서 해결책으로 Statement 객체의 preparedStatement(query) 메소드를 사용하였습니다. 이것은 미리 개발자가 작성한 쿼리문을 컴파일 하고 ?로 처리한 부분에 사용자의 입력 값을 넣기 때문에 쿼리문의 형태가 바뀌지 않아 보안성을 높일 수 있습니다.

PreparedStatement 와 Statement

* PreparedStatement 와 Statement의 가장 큰 차이점은 캐시(cache) 사용여부이다.

1) 쿼리 문장 분석

2) 컴파일

3) 실행

Statement를 사용하면 매번 쿼리를 수행할 때마다 1) ~ 3) 단계를 거치게 되고, PreparedStatement는 처음 한 번만 세 단계를 거친 후 캐시에 담아 재사용을 한다는 것이다. 만약 동일한 쿼리를 반복적으로 수행한다면 PreparedStatment가 DB에 훨씬 적은 부하를 주며, 성능도 좋다.

1. Statement 

String sqlstr = "SELECT name, memo FROM TABLE WHERE num = " + num 

Statement stmt = conn.credateStatement(); 

ResultSet rst = stmt.executeQuerey(sqlstr); 

sqlstr를 실행시 결과값을 생성

Statement  executeQuery() 나 executeUpdate() 를 실행하는 시점에 파라미터로 SQL문을 전달하는데, 이 때 전달되는 SQL 문은 완성된 형태로 한눈에 무슨 SQL 문인지 파악하기 쉽다. 하지만, 이 녀석은 SQL문을 수행하는 과정에서 매번 컴파일을 하기 때문에 성능상 이슈가 있다. 

2. PreparedStatement 

String sqlstr = "SELECT name, memo FROM TABLE WHERE num = ? " 

PreparedStatement stmt = conn.prepareStatement(sqlstr); 

pstmt.setInt(1, num);

ResultSet rst = pstmt.executeQuerey(); 

sqlstr 은 생성시에 실행

PreparedStatement 은 이름에서부터 알 수 있듯이 준비된 Statement 이다. 이 준비는 컴파일(Parsing) 을 이야기하며, 컴파일이 미리 되어있는 녀석이기에 Statement 에 비해 성능상 이점이 있다. 요 녀석은 보통 조건절과 함께 사용되며 재사용이 되는데, ? 부분에만 변화를 주어 지속적으로 SQL을 수행하기 때문에 한눈에 무슨 SQL 문인지 파악하기는 어렵다.

2.와 같이 이용할 경우 해당 인자만 받아서 처리하는 구조로 갈 수 있는것입니다.내부적으로 상세하게 뜯어 보지는 않았지만, 2.는 생성시 메모리에 올라가게 되므로 동일한 쿼리의 경우 인자만 달라지게 되므로, 매번 컴파일 되지 않아도 된다는 결론이 날듯 합니다. 

3. API

(1) Preparedstatement

public interface PreparedStatement extends Statement 

프리컴파일 된 SQL 문을 나타내는 오브젝트입니다. PreparedStatement 는 Statement를 상속받고 있습니다. 

SQL 문은 프리컴파일 되어 PreparedStatement 오브젝트에 저장됩니다. 거기서, 이 오브젝트는 이 문장을 여러 차례 효율적으로 실행하는 목적으로 사용할 수 있습니다. 

(2) Statement

public interface Statement 

정적 SQL 문을 실행해, 작성된 결과를 돌려주기 위해서(때문에) 사용되는 오브젝트입니다. 

디폴트에서는 Statement 오브젝트 마다 1 개의 ResultSet 오브젝트만이 동시에 오픈할 수 있습니다. 따라서, 1 개의 ResultSet 오브젝트의 read가, 다른 read에 의해 끼어들어지면(자), 각각은 다른 Statement 오브젝트에 의해 생성된 것이 됩니다. Statement 인터페이스의 모든 execution 메소드는 문장의 현재의 ResultSet 오브젝트로 오픈되고 있는 것이 존재하면, 그것을 암묵에 클로우즈 합니다. 

그리고 FOR 문등을 통하여 동일한 SELECT 를 여러번 실행해야 하는 경우에는, 그 사용성에 볼때 2번이 훨씬 효과적이라고 볼 수 있습니다. 

(3) 예제

1) Statement 

String sqlstr = null; 

Statement stmt = null; 

ResultSet rst = null; 

FOR(int i=0; i< 100 ; i++){ 

sqlstr = "SELECT name, memo FROM TABLE WHERE num = " + i 

stmt = conn.credateStatement(); 

rst = stmt.executeQuerey(sqlstr); 

} 

2) PreparedStatement

String sqlstr = null; 

PreparedStatement pstmt = null; 

ResultSet rst = null; 

sqlstr = "SELECT name, memo FROM TABLE WHERE num = ? " 

pstmt = conn.prepareStatement(sqlstr); 

FOR(int i=0; i< 100 ; i++){ 

pstmt.setInt(1, i); 

rst = pstmt.executeQuerey(); 

}

4. PreparedStatement를 사용해야 하는 경우

(1) 사용자 입력값으로 쿼리를 생성하는 경우 

사용자에의해 입력되는 값을 가지고 SQL 작업을 할 경우 statement를 사용한다면 다음과 같이 될 것이다.

String content = request.getParameter("content");

stmt= conn.createStatement();

stmt.executeUpdate("INSERT INTO TEST_TABLE (CONTENT) VALUES('"+content+"');

사용자가 제대로 입력 하였다면 상관 없지만 content값에 "AA'AA"를 입력하였다면?

stmt.executeUpdate("INSERT INTO TEST_TABLE (CONTENT) VALUES('"+content+"'); 에서 에러가 발생할 것이다.

즉 SQL문은 다음과 같이 되는 것이다. INSERT INTO TEST_TABLE (CONTENT) VALUES('AA'AA');

이를 다음과 같이 수정한다면 위와같은 에러나 장애를 원천적으로 봉쇄할 수 있다

pstmt = conn.preapreStatement("SELECT * FROM TEST_TABLE WHERE CONTENT = :content");

pstmt.setString(1, content);

pstmt.executeUpdate();

이는 content값이 "'"가 들어왔다 하더라도 알아서 파싱 해주기 때문이다.

고로 사용자 입력 값으로 쿼리를 바인딩 할 경우에는 필히 pstmt를 사용하도록 하자! :)

(2) 쿼리 반복수행 작업일 경우

일반적으로 반복 수행 작업을 할 경우 아래와 같이 코딩 하게 된다.

1) Statement 사용

for (int i = 0; i < 100000; i++) {

stmt.executeUpdate("INSERT INTO TEST_TABLE VALUES('"+content+"');

}

2) PreparedStatement 사용

pstmt = conn.preapreStatement("INSERT INTO TEST_TABLE VALUES(?)"); <--- ⓐ

for (int i = 0; i < 10000; i++) {

pstmt.setString(1, content+i);

pstmt.executeUpdate();

}

종종 실수로 ⓐ번 문장이 for문으로 들어가는것을 보게된다! 주의!

cf.) DB의 종류에 따라 상황이 달라진다.

일반적으로 위와같은 코딩을 할 경우 2)경우가 1)보다 더 나은 성능을 보인다고 알려져 있다. 즉 자바의 PreparedStatement의 사용은 오라클 DB에서 bind변수를 사용하도록 함으로 해서 DB서버에 미리 준비된 SQL을 사용하게 되고 파싱과정을 생략하기 때문에 결국 DB리소스를 효율적으로 사용하도록 하는 방법이 된다.

하지만 이것이 DB서버에 따라 다르다. MySql같은 경우는 1)과2)의 성능차이가 거의 나지 않는다.

--> 적당한 PreparedStatement의 사용

위와같은 이유로 PreparedStatement가 좋다! 모든 쿼리를 PreparedStatement로 하자! 만약 이와 같이 된다면 또다른 문제가 생긴다. 각 DB마다 SQL캐싱할 수 있는 한계가 있기 때문에 정작 성능상 캐싱되어야 할 쿼리가 그렇지 않은 쿼리 때문에 캐싱이 안 될 수 있기때문에 꼭 필요한 문장만 PreparedStatement를 쓰는것을 권고한다.

5. Statement를 받드시 사용해야 하는 경우

(1) Dynamic SQL을 사용할 경우

Dynamic SQL을 사용한다면 매번 조건절이 틀려지게 됨으로 statement가 낫다. 즉 캐싱의 장점을 잃어버립니다. 또한 Dynamic SQL일 경우 코딩도 Statement가 훨신 편하다.

출처: http://devbox.tistory.com/entry/Comporison?category=577782 [장인개발자를 꿈꾸는 :: 기록하는 공간]

DB를 설계할 때는 다음과 같이 5가지 방법으로 크게 나눌 수 있다.

1. 최종 목표 잡기

2.문서화된 요구사항

3. 프로그램의 메뉴 및 기능

4.E-R Diagram

5.데이터베이스 스키마

1. 프로젝트의 최종 목표

- 저희 프로젝트의 목표는 컴퓨터 주문 및 A/S시스템으로써 현재 상용화 되어있는 컴퓨터 판매 프로그램과 유사하게 만들어 내는 것과 자바와 오라클간의 연동 오라클 DBMS의 쿼리문 사용을 숙달 하는 것을 목표로 합니다.

2. 문서화된 요구사항

3. 프로그램의 메뉴 및 기능

회원가입을 제외한 모든 기능은 로그인을 하여야 사용 가능합니다. 직원아이디로 로그인 할 시 직원의 기능을 사용가능하고, 회원아이디로 로그인 할 시 회원의 기능을 사용가능합니다.

첫 번째 기능은 회원 가입입니다. 회원 가입 시 고객은 회원아이디, 나이, 주소, 연락처, 비밀번호, 이름를 입력해야 합니다. 회원가입시 자동으로 등급과 관리 직원의 아이디가 할당 됩니다. 직원은 회원관리를 할 수 있습니다. 세부적으로 회원 검색, 수정, 삭제기능이 있습니다. 고객은 회원가입과 로그인을 한 후 상품을 주문 할 수 있습니다. 회원 검색에서 이름을 검색 할 시 이름의 일부분만 적어도 검색이 됩니다.

두 번째 기능은 재고 관리입니다. 직원으로 로그인 한 뒤 직원은 재고관리를 할 수 있습니다. 납품될 부품들을 받을 납품회사들을 먼저 등록합니다. 납품회사를 등록하면 납품회사명, 전화번호, 담당자의 정보가 저장됩니다. 직원은 납품회사에서 납품된 재고들을 프로그램에 입력합니다. 그 재고들은 부품으로 구별됩니다. 부품을 받으면 부품번호, 최종공급일자, 공급량, 종류, 납품회사명, 부품명, 가격을 할당 합니다. 직원은 재고 관리를 통해 재고 정보를 수정합니다. (여기서 재고라 함은 CPU,그래픽카드 등 상품으로 합쳐지지 않은 부품이다.)

세 번째 기능은 상품 주문과 상품 관리입니다. 직원이 재고들을 합쳐 상품을 만들면 상품에 대한 상품번호, 상품명, 가격, CPU 그래픽카드, 메인보드, 메모리, 파워, SSD, HDD, 수량이 저장됩니다. 직원은 상품을 등록할 수 있습니다. 상품은 재고들을 결합하여 만들어진 PC여야 합니다. 회원은 원하는 상품을 주문 할 수 있습니다.

네 번째 기능은 주문기능입니다. 회원은 프로그램에 등록된 가격과 성능을 보고 상품을 주문합니다. 회원이 주문할 상품을 선택 후 받는 이의 주소 및 전화번호 등을 입력하고 주문을 버튼을 누르면 주문완료 됩니다. 주문 생성 시 주문에 대한 주문번호, 주문아이디, 주문한상품번호, 주문수량, 주문일자, 배송지, 판매완료정보, 연락처가 저장됩니다. 직원은 회원이 주문을 하면 해당 주문정보를 조회 가능합니다.

다섯 번째 기능은 수리접수 신청기능입니다. 고객은 자신이 이 프로그램을 통해 주문한 상품만 수리접수신청이 가능합니다. 자신의 상품구매 정보 및 주소지 연락처 등을 입력하고 수리접수신청을 누르면 수리접수신청이 완료됩니다. 수리접수 신청은 직원이 확인하여 관리 할 수 있습니다. 수리접수 신청시 수리접수번호, 회원아이디, 수리접수일자, 주문번호, 주소, 연락처, 수리상태의 정보가 남습니다.

여섯 번째 기능은 주문 신청관리와 A/S 신청관리입니다. 회원이 상품을 주문하면 직원은 이 주문 신청관리 기능으로 어떤 상품이 주문되었는지 확인 할 수 있고 상품이 배송완료 되었다는 가정하에 완료된 주문을 판매완료 상태로 변경할 수 있습니다. A/S 신청관리는 상품을 배송받은 회원이 수리접수를 신청한뒤 직원이 수리접수를 신청한 회원의 명단을 볼 수 있는 기능으로서 앞의 주문 신청관리와 마찬가지로 수리접수가 완료되었다는 가정하에 수리접수 상태를 완료상태로 바꿀 수 있습니다.

일곱 번째 기능은 판매량조회기능입니다. 이 기능으로 어느 회원이 몇 개의 상품을 샀는지 알 수 있으며 직원이 원하는 날짜를 입력하면 입력한 날짜부터 날짜까지의 그 날 해당 판매총액이 나오게 됩니다. 판매액은 테이블로 보여지며 판매량이 많은 순, 판매량이 적은 순으로 볼 수도 있습니다.

4. E-R Diagram

5. 데이터베이스 스키마

http://keep-cool.tistory.com/23

1.http://www.oracle.com/technetwork/developer-tools/sql-developer/learnmore/index.html

2.SQL Developer Release History click!

3.원하는 버전의 Release Notes 클릭

4.FAQ 클릭

5.Download 클릭

6.컴퓨터 사양에 맞는 다운로드 클릭해서 진행~

http://junil-hwang.com/blog/jsp-oracle-crud/

아주 자세히 나와있다.