java 고급과정 9일차

SQL


JDBC(Java DataBse connectivity) - Java에서 db에 접근
SQL(structured query language) - DMBS를 사용하는 표준 명령어
DBMS(database management system)


SQL data type

예)

열 이름	설명	예	적합한 데이터 타입
가격(price)	물건의 판매가	5678.39	DEC(6,2)
우편번호(zip_code)	5~10개의 문자	90210-0010	VARCHAR(10)
원자량(atomic_weight)	원소의 원자량  (소수점 6자리까지)	4.002602	DEC(10,6)
주석(comments)	용량이 큰 텍스트,  255이상의 문자열	조, 나 주주총회에 있어. 시연을 보였는데 화면에 장난감 오리가 막 날라다니..	BLOB
갯수(quantity)	남아 있는 물건의 개수	239	INT
세율(tax_rate)	퍼센트(%)	3.755	DEC(5, 3)
책 제목(book_title)	문자열	Head First SQL	VARCHAR(50)
성별(gender)	M이나 F 중 하나의 문자	M	CHAR(1)
전화번호(phone_number)	열 자리 숫자, 구두점없이	2105552367	CHAR(10)
state	주를 나타내는 두 개의  문자	TX, CA	CHAR(2)
기념일(anniversary)	월, 일, 년	11/22/2006	DATE
이긴 경기(game_won)	이긴 경기 수를 나타내는  정수	15	INT
미팅 시간  (meeting_time)	시간과 날짜	10:30 a.m. 4/12/2020	DATETIME

 * DEC(6, 2) : 소수점 앞의 숫자는 최대 6개, 소수점 뒤의 숫자는 최대 2개를 의미
 * VARCHAR과 CHAR의 차이
   - VARCHAR은 길이가 변할 수 있으므로(최대 길이는 유효) 길이가 변할 수 있는 데이터의 타입으로
     사용하면 유용하다(공간절약). 만약 길이가 변하지 않고 항상 정해진 길이라면 CHAR을 사용할 수도 있다.


작은 따옴표 사용하는 데이터 타입
             - char / varchar / date / datetime / time / timestamp / blob
           작은 따옴표를 사용하지 않는 데이터 타입
             - dec, int

 

 

==========================================================

테이블 생성

 

CREATE TABLE emp_test(

           id                   NUMBER(30),

           name               VARCHAR2(15),

           salary              NUMBER(7,2),

           title                 VARCHAR2(15)    DEFAULT '사원',

           in_date            DATE               DEFAULT SYSDATE,

           dept_name        VARCHAR2(15)

);

 

 

===========================================================

TABLE 삭제 ==> DROP의 사용

DROP TABLE test_table;

 

 

 

CREATE TABLE test_table(

           no NUMBER(10),

           contends VARCHAR2(10)

);

 

==>테이블 구조 확인

DESC test_table;

 

============================================================

 

==>SYSDATE : current time 을 나타내는 SQL keyword

SELECT SYSDATE FROM DUAL;

(YEAR/MONTH/DATE) 형식으로 출력됨

EX) 12/03/15

 

 

 

============================================================

 

DUAL==>출력을 위한 가상의 테이블

SELECT 9*9 FROM DUAL;

       9*9

----------

        81

 

SELECT USER FROM DUAL;

       9*9

----------

        81

 

 

============================================================

INSERT

INTO emp_test(id, name, salary, title, in_date, dept_name)

VALUES(1, '김사장', 100, '사장', '04-07-19', '개발부');

 

SELECT*FROM emp_test;

 

 

결과 :

 

        ID NAME                               SALARY TITLE                          IN_DATE  DEPT_NAME

---------- ------------------------------ ---------- ------------------------------ -------- -------------

         1 김사장                                100 사장                           04/07/19 개발부

 

 

============================================================

 

INSERT

INTO emp_test

VALUES(2, '홍길동', 1000, '부사장', '2004/07/19', '영업부');

 

============================================================

 

INSERT

INTO emp_test(id, name, salary)

VALUES(3, '홍길순', 1000);

 

자동으로 IN_DATE, TITLE 이 채워짐

왜?

           title                 VARCHAR2(15)    DEFAULT '사원',

           in_date            DATE               DEFAULT SYSDATE,

==>DEFAULT 시에 사원, SYSDATE를 입력하라고 설정해놨기 때문에

==>설정하지 않았을 때는 null 이 입력됨

 

============================================================

INSERT

INTO emp_test(id, name, title)

VALUES(4, '이순신', '장군');

============================================================

 

INSERT

INTO emp_test

VALUES(2, USER, 1000, '부사장', SYSDATE, '영업부');

 

============================================================

INSERT

INTO emp_test(id, name)

VALUES(5, '장나라');

 

INSERT

INTO emp_test

VALUES(5, '장나라', NULL, null, '','');

 

==>SQL은 대소문자 구분하지 않음 NULL,null 같음

==>싱글포테이션 2개를 입력->null

 

============================================================

COMMIT; 변경 사항 저장

ROLLBACK; 변경 사항 취소

============================================================

==>변경

 

UPDATE emp_test

SET id=7

WHERE name ='SCOTT';

 

UPDATE emp_test

SET DEPT_NAME='관리부'

WHERE DEPT_NAME ='영업부';

 

============================================================

==>홍길동을 사장/2000/영업팀으로 변경;

 

UPDATE emp_test

SET NAME='홍길동', SALARY='2000', TITLE='사장', DEPT_NAME='영업팀'

WHERE NAME='홍길동';

 

============================================================

==> salary가 1500넘는 사람 dept_name 를 '대기발령' 변경

 

UPDATE emp_test

SET dept_name='대기발령'

WHERE salary>=1500;

 

==> 전직원 '평사원'으로 변경

==> WHERE 절이 없으면 전체 적용

UPDATE emp_test

SET title='평사원';

 

==> 전직원 임금 인상 500

UPDATE emp_test

SET SALARY=SALARY+500;

==>salary가 null인 직원은 연산에서 제외됨

 

 

dept_name이 null 인 row를 대기발령 변경

UPDATE emp_test

SET dept_name='대기발령'

WHERE dept_name=null;

null은 연산에서 제외되기 때문에, 실행되지 않는다.

 

UPDATE emp_test

SET dept_name='대기발령'

WHERE dept_name IS NULL;

 

 

==>NULL이 아닌 것을 찾을 때

UPDATE emp_test

SET dept_name='대기발령'

WHERE dept_name IS NOT NULL;

==> id 가 3보다 같거나 높은 ROW 삭제

============================================================

==>id가 5같거나 높고 6같거나 낮은 row 삭제

 

DELETE FROM emp_test

WHERE id>=5 AND id<=6;

 

DELETE FROM emp_test

WHERE id>=5 or id<=6;

ALIAS
다른 두 테이블에 동일한 column명이 존재한다면 어느 테이블의 column인지 판단이 불가능하다. 이 때문에 테이블에 별칭을 주어 어느 테이블의 column인지 명확히 하여 에러를 피할 수 있다.

SELECT
 cust_name AS cn
FROM emp_table;

 NVL(exp1, exp2)
; exp1이 NULL이면 exp2로 변경, (exp1, exp2는 반드시 같은 데이터타입)

NVL2(exp1, exp2, exp3)
; exp1이 NULL이 아니면 exp2,
NULL 이면 exp3

DECODE(exp, 조건1, 결과값1,조건2, 결과값2,, 결과값3)
; 오라클에서만 제공
exp가 조건1이면 결과값1 출력,
조건2면 결과값2 출력
조건에 맞지 않는 값이면 결과값3 출력(이 값이 없으면 조건에 맞지 않는 값은 NULL로 처리)

(, 의 사용에 주의하세요~)

 

 

NVL2 예제  학생의 이름, 지도교수 번호를 출력하여라. 단, 지도교수가 배정되지 않은 학생은 지도 교수 번호를 0000 으로 출력하여라.   SQL> select name,   2 nvl2( to_char(profno), to_char(profno), '0000' ) "지도교수"   3  from student;   NAME       지도교수번호 ---------- ---------------------------------------- 전인하     9903 이동훈     0000 박미경     0000 .... 류민정     9907   16 rows selected. (nvl2안에 to_char를 써준이유는 숫자타입에서는 '0000'을 출력하면  '0'만 출력되기때문에 문자타입으로 변경해서 '0000'을 출력한 것입니다.)	NVL 예제   1  select name, sal,   2  nvl(comm,10), comm   3* from professor SQL> /   NAME              SAL NVL(COMM,10) COMM ---------- ---------- ------------ ---------- 김도훈            500           20               20 이재우            320           10 성연희            360           15               15 염일웅            240           10 권혁일            450           25               25 이만식            420           10 전은지            210           10 남은혁            400           17               17   8 rows selected.
DECODE 예제  학생 전화번호의 지역번호가 ’02’이면 ‘서울’, ‘051’이면 ‘부산’,’052’이면 ‘울산’,' 053’이면 ‘대구’, 나머지는 ‘Etc’ 로 하여 학생이름,전화번호,지역명(Loc)을 출력하여라.   SQL> select name, tel,   2  decode( substr(tel,1, instr(tel,')',1,1)-1),   3  '02', '서울',   4  '051', '부산',   5  '052', '울산',   6  '053', '대구',   7  '기타') 지역명   8  from student;   NAME       TEL           지역 ---------- ------------- ---- 전인하     051)781-2158  부산 이동훈     055)426-1752  기타 박미경     055)261-8947  기타 .... 윤진욱     053)487-2698  대구 이광훈     055)736-4981  기타 김진경     052)175-3941  울산 조명훈     02)785-6984   서울 류민정     055)248-3679  기타   16 rows selected.
CASE 예제  교수 테이블에서 입사일이 1~4월인 모든 교수의 급여를 15% 인상하여 정수로 출력하되 절삭된 값을 출력하여라.     1  select name, hiredate, sal,   2  CASE WHEN to_char(hiredate,'mm') BETWEEN 1 and 4   3     THEN to_char( trunc(sal*1.15) )   4     ELSE '-'   5  END "인상 후"   6* from professor SQL> /   NAME       HIREDATE         SAL            인상 후 ---------- ------------ ---------- ---------------------------------------- 김도훈     24-JUN-82           500                  - 이재우     12-APR-95           320                 368 성연희     17-MAY-93           360                 - 염일웅     02-DEC-98           240                  - 권혁일     08-JAN-86           450                 517 이만식     13-SEP-88           420                 - 전은지     01-JUN-01           210                  - 남은혁     18-NOV-90           400                 -   8 rows selected.

 

 

DECODE

DECODE(칼럼1, NULL, 'X', 'O') 를 하면 됨

칼럼1 내용이 없으면 'X'를 있으면 'O'를.. 표시하게 됨

 

SELECT

name 이름, NVL2(title, '직급있음', '<직급없음>') 직급유무, title 직급

From emp_test;

 

 

SELECT

name, DECODE(title, NULL, '<직급없음>'/*if(title= null) 직급없음 출력*/,'직급있음'), title  /*아니면 직급있음*/

From emp_test;

 

 

SELECT

name, DECODE(title, NULL, '<직급없음>','사장','회사의 아버지', '부사장', '회사의 어머니', '직급있음'), title

From emp_test;

 

===========================================================

=> ||(concatenation) 문자 이어 붙이기

 

SELECT

name||title

FROM emp_test;

==>홍길동과장

 

SELECT

name || ' earns ' || title

FROM emp_test;

 

SELECT

Name||' 님 '|| title ||'이며 연봉은 '|| NVL(salary,0)*12 ||  '원' AS "이름 직책 연봉 ?"

FROM emp_test;

 

======================================================

=>DISTINCT 중복되는 행 제거

SELECT

DISTINCT dept_name 부서명

FROM emp_test;

 

INSERT

INTO emp_test(name, dept_name)

VALUES(‘홍길동', '대기발령');

 

=======================================================

 

ORDER BY(DATA를 정렬하여 조회) ASC(오름차순) DESC(내림차순)

 

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY salary*12 ASC;

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY salary*12 DESC;

 

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY salary*12;

 

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY salary*12 NULLS FIRST;

==>NULL이 먼저 출력

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY salary*12 NULLS LAST;

==>NULL이 마지막에 출력

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY 연봉;

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY 3 DESC;

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY dept_name;

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY 연봉;

==>order by는 alias 사용 가능

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY 3 DESC;

==> order by는 는 coulumn의 순서(index)로도 정렬 가능

 

SELECT

name 이름, title 직책, salary*12 연봉

FROM emp_test

ORDER BY dept_name;

==>order by는 select로 추출하지 않는 column 으로 정렬가능

 

//==============================================//
//==================== 실   습 ====================//
//==============================================//
==> 아래의 조건에 맞는 QUERY문작성하세요
==> 실습한후 ROLLBACK 할것

ㅇ  salary가 null 이고 title가 null인 직원 검색  :: SELECT

SELECT name from emp_test
WHERE salary is null and title is null;
장나라, 꺽다리991

ㅇ  title이 null인 직원 title 계약직 변경 :: UPDATE
UPDATE emp_test
SET title='계약직'
WHERE title IS NULL;


ㅇ 월급 2000 이상인  직원 '특판팀' 변경 및
     현 월급에서 무조건 1000 차감 변경        :: UPDATE

UPDATE emp_test
SET dept_name='특판팀', salary=salary-1000
WHERE salary>=2000;

ㅇ  2005년 01월01일 이후 입사자 검색       :: SELECT
select
name
from emp_test
where in_date >= '05/01/01';

ㅇ  2005년 01월01일 이후 입사자 이고
      월급이 6000원 이하인 직원 검색            :: SELECT
select
name
from emp_test
where in_date>'2005-01-01' and salary<=6000;



===========================
=======> 실습 합시다. <=======
===========================
ㅇ ALIAS 사용 이름,부서,직책,입사일를 조회  :: SELECT

select
name 이름, dept_name 부서, title 직책, in_date 입사일
from emp_test;

ㅇ ALIAS을 사용
     이름,직책,연봉,특별 상여금을 조회   :: SELECT
     (상여금은 연봉의 15%로 계산)
    
 >> QUERY 하나
 NULL은 연산에서 제외됨 NULL은 어떻게 직책,연봉,상여금이 어떻게 추출되는지 확인
    
 >> QUERY 둘
  직책 null 인 경우 ==> 인사발령전
  연봉 null 인경우 ==> 0
  상여금 null 인경우 ==> 0
  으로 출력되도록 Query 변경


select
name 이름, NVL(title,'인사발령전') 직책, NVL(salary,0)*12 연봉, round(NVL(salary,0)*12*0.15) "특별 상여금"
from emp_test;


ㅇ ALIAS를 사용   
     이름,월급,일당,시급을 조회     :: SELECT
     (일당 :30일, 시급 : 8시간 기준)

select
name 이름, round(NVL(salary,0)/30) 일당, ROUND(NVL(salary,0)/30/8) 시급
from emp_test;

ㅇ위의 QUERY문을 아래의 함수(?)를 사용하여 정리하자
    ==> 반올림함수 ROUND(VALUE, 반올림자리수)
    ==> 버림    함수 TRUNC (VALUE,  버림자리수)


 ㅇ 위의 QUERY문(ROUND,TRUNC 사용한 것) 월급,일당,시급 NULL은 0 추출
select

name 이름, round(NVL(salary,0)/12) 월급, round(NVL(salary,0)/12/30) 일당, round(NVL(salary,0)/12/30/8) 일당
from emp_test;





==================================
===> 실습합시다.
==================================
ㅇ ALIAS , ||를 사용하고        개발부서의 김사장입니다       형식으로 조회
     오름차순으로 정렬 조회
SELECT
DEPT_NAME ||'서의 '|| NAME ||'입니다'
FROM emp_test
order by dept_name;

ㅇ 부서별/연봉이 높은 순서로 이름/부서/연봉순으로 조회

SELECT
NAME 이름, DEPT_NAME 부서, SALARY 연봉
FROM emp_test
ORDER BY 부서, 연봉 ASC;

ㅇ 연봉이 높은순서,  입사연도가 빠른순서 로 연봉/입사년도/이름를 조회
SELECT
SALARY 연봉, IN_DATE 입사년도, NAME 이름
FROM emp_test
ORDER BY 연봉, 입사년도 ASC;