190523_Day31 DB2

DB랑 DBMS 를 명확하게 구별 할 것

  • DB(DataBase) 는 체계화 된 데이터의 모임
  • 여러사람 공유목적, 데이터의 집합
  • 서버라고 생각하면 안됨, 저장소라고 생각해야 함!
  • DBMS(DataBase Management System)
  • 데이터베이스 관리 시스템
  • 효과적 이용 위해 정리, 보관 소프트웨어
  • 데이터 추가,조회,수정,삭제 등 기능을 집대성한 소프트웨어 패키지
  • 데이터베이스를 구축하는 틀을 제공
  • 효율적으로 데이터를 검색하고 저장하는 기능
  • 응용 프로그램들이 데이터베이스에 접근할 수 있는 인터페이스 제공
  • 장애에 대한 복구, 보안 유지 기능 제공

SQL*PLUS 명령어 : 접속 툴 내에서 출력형식을 지정하는 등 환경설정. (아주 중요한 것은 아니다)

<SQL.>

  • 관계형 데이터베이스의 ANSI 표준언어
  • 여러줄 실행
  • 종결문자필요(;)
  • 키워드 단축 불가
  • 버퍼에 마지막 명령문 저장
  • 대소문자 구분 없음 (TABLE명, COLUMN명을 SeLecT 이렇게 써도 됨. 단, 데이터를 표시할 때는 반드시 대소문자 구분! 'Gildong', 'gildong')

<SQL*PLUS>

  • 오라클 DB접속툴
  • 한줄실행
  • 종결문자 불필요
  • 키워드 단축 가능
  • 버퍼저장 안함

SQL*PLUS 명령어

  • L[IST] : 가장 마지막에 실행한 SQL문 출력

  • ED[IT] : 지정된 파일을 메모장을 통해 열어줌.

  • SAVE 파일명 : 명시된 파일명으로 버퍼에 있는 SQL문을 저장

    save hello.sql;
//맨 처음 들어온 경로 sqlplus 접근한 곳

  • GET 파일명: 명시된 파일 SQL문 얻어오기

  • SPOOL(갈무리)

    #시작
SPOOL 파일명
SQL문1;
SQL문2;
SQL문3;
#끝
SPOOL OFF  

===> SQL문1 부터  SQL문3 까지 지정된 파일명으로 저장
    • HOST: SQL접속을 유지한 상태에서 커맨드 상태로 이동 (sql프롬프트 ----> windows프롬프트)
    • EXIT: 커맨드상태에서 SQL프롬프트상태로 이동 (windows프롬프트 ----> sql프롬프트)

  • LINE, PAGESIZE(한 페이지에 출력되는 라인사이즈): 출력형식

      형식) SET 명령어 값

  • 행과 열이 짤린다면

    1. SQL>set linesize 300;
      1. SQL>set pagesize 100;
        위와 같이 출력 사이즈를 수정하자
    SQL> set pages17
SQL> select empno, ename, sal from emp;

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7369 SMITH                       800
      7499 ALLEN                      1600
      7521 WARD                       1250
      7566 JONES                      2975
      7654 MARTIN                     1250
      7698 BLAKE                      2850
      7782 CLARK                      2450
      7788 SCOTT                      3000
      7839 KING                       5000
      7844 TURNER                     1500
      7876 ADAMS                      1100
      7900 JAMES                       950
      7902 FORD                       3000
      7934 MILLER                     1300

  • 명령어 설명 참조)명령어 뭔지 모르겠으면 ? set 혹은 help set으로 도움말 보기 가능

    ​ 

    /          : 버퍼에 저장된 마지막 sql문  실행
R[UN]      : 버퍼에 저장된 마지막 sql문  출력과 실행
@test.sql  : 파일내의 sql문  실행
ed test.sql

show user

select tname from tab; // 모든테이블 보여준다

<WHERE절> - 조건절, 출력행을 제어하는 역할

형식)

SELECT 컬럼명 //전체 열중 원하는 열만 선택 : 열을 제어

FROM 테이블명

WHERE 조건식(conditions); // 전체 행중 원하는 행만 선택 : 행을 제어

FROM - WHERE SELECT 순서로 읽는다.

※ 조건식 구성

기준컬럼명 연산자 비교데이터
(비교연산자)
> , < , >= , <=
= (같다, 등가연산자)
!=(다르다), ^=, <>

문제) 전체 사원의 사원번호, 사원명, 부서번호를 출력.
select empno, ename, deptno
from emp;

   EMPNO ENAME                    DEPTNO
  7369 SMITH                        20
  7499 ALLEN                        30
  7521 WARD                         30
  7566 JONES                        20
  7654 MARTIN                       30
  7698 BLAKE                        30
  7782 CLARK                        10
  7788 SCOTT                        20
  ....
  ===> 14행의 출력

문제) 30번 부서에 근무하는 사원의 사원번호, 사원명, 부서번호를 출력.
select empno, ename, deptno
from emp
where deptno = 30;

문제) 30번 부서에 근무하지않은 사원의 사원번호, 사원명, 부서번호를 출력.

select empno, ename, deptno
from emp
where deptno != 30; //혹은 (deptno=10 OR deptno=20)

문제) SMITH사원의 사원번호, 사원명, 입사일, 부서번호를 출력하시오.

​ SELECT empno, ename, hiredate, deptno

​ from emp

​ where upper(ename) = upper('sMiTh');

문제1 ) 20번 부서에 근무하는 사원의 사원번호, 사원명, 직책, 급여를 출력하시오

select empno, ename,job, sal
from emp
where deptno = 20;

문제2 ) 직책이 manager인 사원의 사원번호, 사원명, 직책, 입사일을 출력하시오

SELECT empno, ename,job, hiredate
FROM emp
WHERE  job = 'MANAGER';

문제3) 급여가 2000 이상인 사원의 사원번호, 사원명, 급여를 출력하시오.

SELECT empno, ename, sal
FROM emp
WHERE sal >= 2000;

문제4) FORD사원의 사원번호, 사원명, 부서번호를 출력하시오.

SELECT empno, ename, deptno
FROM emp
WHERE ename = 'FORD';

문제5) 1981년 6월 1일 이후에 입사한 사원의 사원번호, 사원명, 부서번호, 입사일을 출력하시오.

SQL> SELECT empno, ename, deptno, hiredate
  2  FROM emp
  3  WHERE hiredate >= '810601';

       EMPNO ENAME                    DEPTNO HIREDATE
---------- -------------------- ---------- --------
      7654 MARTIN                       30 81/09/28
      7782 CLARK                        10 81/06/09
      7788 SCOTT                        20 87/04/19
      7839 KING                         10 81/11/17
      7844 TURNER                       30 81/09/08
      7876 ADAMS                        20 87/05/23
      7900 JAMES                        30 81/12/03
      7902 FORD                         20 81/12/03
      7934 MILLER                       10 82/01/23

문제6) 10번 부서에 근무하는 사원의 사원번호, 사원명, 매니저의 사원번호를 출력하시오.

select empno, ename, mgr
from emp
where deptno = 10;

문제7) 20번과 30번 부서에 근무하는 사원의 사원번호, 사원명, 매니저의 사원번호를 출력하시오.

select empno, ename, mgr
from emp
where deptno = 20 OR deptno = 30;

     EMPNO ENAME                       MGR
---------- -------------------- ----------
      7369 SMITH                      7902
      7499 ALLEN                      7698
      7521 WARD                       7698
      7566 JONES                      7839
      7654 MARTIN                     7698
      7698 BLAKE                      7839
      7788 SCOTT                      7566
      7844 TURNER                     7698
      7876 ADAMS                      7788
      7900 JAMES                      7698
      7902 FORD                       7566

문제8) 10,20번 부서의 부서번호, 부서명, 부서위치를 조회하시오.

select deptno, dname, loc
from dept
where deptno = 10 OR deptno = 20;

문제9) 3등급 급여의 최소급여와 최대급여를 출력하시오.

SELECT losal, hisal
from salgrade
where grade = 3;

===> 급여 3등급은 1401이상 ~ 2000이하 !!

where 조건식1 OR|AND 조건식2
where 조건식1 OR 조건식2 AND 조건식3

  2
          1
    ------------

A || B && C 순차적 실제

0 0 0 0 0
0 0 1 0 0
0 1 0 0 0
0 1 1 1 1
1 0 0 0 1 ==> check
1 0 1 1 1
1 1 0 0 1 ==> check

1 1 1 1 1

System.out.println( 1<2 || 1<3 && 300>500); //true
---- --- -------
조건식1 조건식2 조건식3

select dname
from dept
where 조건식3 AND 조건식2 OR 조건식1

select dname
from dept
where 300>500 AND 1<3 OR 1<2;
==> 4행 출력 System.out.println( 1<2 || 1<3 && 300>500); //true

select dname
from dept
where 300>500 AND (1<3 OR 1<2);
==> 0행 출력 System.out.println( (1<2 || 1<3) && 300>500); //false

====================================================================
<조건식의 구성>
컬럼명 연산자 비교데이터

<LIKE연산자>
형식) 컬럼명 LIKE 'pattern'
----> 패턴과 닮은 컬럼이 속한 행을 조회
----> 패턴내의 예약문자(횟수를 표현하는 문자)
% : 0~n개의 문자를 대체
_ : 단 1개의 문자를 대체

DB에 저장된 문자데이터 예) 'a', 'ab', 'ad', 'abc', 'abbbbb', 'bababa'

like 'a' -----> 'a' : % 또는 _ 문자를 사용하지 않는다면 등호와 같은 결과!! = 'a'

like 'a%' -----> a문자로 시작하는 데이터!! -----> 'a', 'ab', 'ad', 'abc', 'abbbbb'

like 'a__' -----> 'abc'

like '%a' -----> a문자로 끝나는 데이터!! -----> 'a', 'bababa'

like '__c' -----> 'abc'

like '%a%' -----> a문자를 포함하는 데이터!! -----> 'a', 'ab', 'ad', 'abc', 'abbbbb', 'bababa'

문제) 사원명이 'A'로 시작되는 사원의 정보(사원번호,사원명,급여)를 출력하시오
select empno, ename, sal
from emp
where ename like 'A%'; 

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7499 ALLEN                      1600
      7876 ADAMS                      1100

문제) 사원명이 'S'로 끝나는 사원의 정보(사원번호,사원명,급여)를 출력하시오
select empno, ename, sal
from emp
where ename like '%S'; 

 EMPNO ENAME                       SAL
  7566 JONES                      2975
  7876 ADAMS                      1100
  7900 JAMES                       950

문제) 사원명에 'A'문자가 포함된 사원의 정보(사원번호,사원명,급여)를 출력하시오
select empno, ename, sal
from emp
where ename like '%A%'; 

  EMPNO ENAME                       SAL
  7499 ALLEN                      1600
  7521 WARD                       1250
  7654 MARTIN                     1250
  7698 BLAKE                      2850
  7782 CLARK                      2450
  7876 ADAMS                      1100
  7900 JAMES                       950

문제) 사원명의 세번째 문자에 'A'문자가 포함된 사원의 정보(사원번호,사원명,급여)를 출력하시오
select empno, ename, sal
from emp
where ename like 'A';

문제) 사원명에 '%'문자가 포함된 사원의 정보(사원번호,사원명,급여)를 출력하시오
==> 테스트를 위해 잠시 '%'포함된 사원정보 입력!! ==> insert into emp (empno, ename, sal)
values (8000, '홍%길동' , 2500); 

SELECT empno, ename, sal
from emp
where ename like '%%%';

※참고)

ESCAPE옵션 : '%' 또는 '_'를 와일드 카드가 아닌 문자의 의미를 가지고자 할 때 사용

​ -> 컬럼명 LIKE '패턴' ESCAPE '부호';

​ 이스케이프 구분자!!

select empno, ename, sal
from emp
where ename like '%%%' escape '';

rollback; -----------> 작업취소(insert 이전의 상태로 되돌리기!!)

<IN 연산자>

콤마로 구분하는 배열데이터

형식) 컬럼명 IN (value_list)

​ => 컬럼의 값이 value_list에 데이터와 하나라도 일치한다면!!

​ (OR연산자 유사)

문제) 커미션을 300 , 500, 1400 받는 사원들의 정보 ( 사원번호, 사원명, 부서번호, 커미션)를 출력하시오

select empno, ename, deptno, comm
from emp
where comm = 300 or comm = 500 or comm = 1400;
select empno, ename, deptno, comm
from emp
where comm in (300, 500, 1400);

<BETWEEN AND 연산자> : 범위 조건시 사용

형식) 컬럼명 BETWEEN a AND b => a:최소값, b:최대값 => a이상 b이하

​ 컬럼명 BETWEEN 최소값 AND 최대값

​ BETWEEN 1 and 100 => 1이상 100이하

주의) BETWEEN AND 연산자는 초과와 미만을 표현을 못함!!

문제) 급여가 1600 이상이고 3000 이하인 사원의 정보(사원번호, 사원명, 급여)를 출력하시오.

SELECT empno, ename, sal
from emp
where 1600 <= sal <= 3000;
    => 에러 : 한 컬럼을 두개 이상의 값과 동시에 비교할 수 없음
SELECT empno, ename, sal
from emp
where sal >= 1600
    AND
    sal <= 3000;
SELECT empno, ename, sal
from emp
where sal between 1600 AND 3000;        //거꾸로 하면 3000 AND 1600 하면 조회 불가

문제) 커미션을 받는 사원의 사원번호, 사원명, 부서번호, 커미션을 출력하시오

SELECT empno, ename, deptno, comm
from emp
where comm > 0;
SELECT empno, ename, deptno, comm
from emp
where comm is not null;

문제) 커미션을 받지 않는(커미션이 null인, 커미션을 받지 않은 조건 ) 사원의 사원번호, 사원명, 부서번호, 커미션을 출력하시오

SELECT empno, ename, deptno, comm
from emp
where comm is null;

☆★데이터 값이 null인 것을 체크(비교) : 컬럼명 IS NULL

​ null이 아닌것은

​ null이 아닌것을 체크

<NULL데이터> ★

  • 미확정 데이터 (같은 행에서 입력되지 않은 데이터)

  • 연산, 할당, 비교가 불가능!! (결과행에서 배제)

    comm=null

    3000+null ==> null 

   select empno, ename, deptno, comm
   from   emp
   where  comm is null;

null 연산, 비교, 할당 불가 -> 가능은 하지만 결과도 null

문제) 커미션을 받을 조건이 되는 사원의 사원번호, 사원명, 부서번호, 커미션을 출력하시오

   select empno, ename, deptno, comm
   from   emp
   where  comm is not null;

문제) 전체사원의 사원번호, 사원명, 급여, 연봉(comm포함)을 출력하시오.

select empno, ename, sal, sal*12+comm "연봉"
   from emp;                              

     EMPNO ENAME                       SAL       연봉
---------- -------------------- ---------- ----------
      7369 SMITH                       800
      7499 ALLEN                      1600      19500
      7521 WARD                       1250      15500
      7566 JONES                      2975
      7654 MARTIN                     1250      16400
      7698 BLAKE                      2850
      7782 CLARK                      2450
      7788 SCOTT                      3000
      7839 KING                       5000
      7844 TURNER                     1500      18000
      7876 ADAMS                      1100                                 
 select empno, ename, sal, sal*12+  NVL(comm,0) "연봉"
   from emp;  

     EMPNO ENAME                       SAL       연봉
---------- -------------------- ---------- ----------
      7369 SMITH                       800       9600
      7499 ALLEN                      1600      19500
      7521 WARD                       1250      15500
      7566 JONES                      2975      35700
      7654 MARTIN                     1250      16400

<NVL함수>

  • null변환 함수
    형식)
    NVL( null값이 예상되는 컬럼명 , 대체할 데이터 )
    ===> ※주의: 첫번째인자와 두번째인자는 서로 자료형이 같아야 함!!

문제) 사원들의 사원번호, 사원명, 커미션을 출력하되
커미션을 받지않는(커미션의 값이 null) 사원들은 0으로 출력하시오.

  select empno, ename, comm
  from emp;

     EMPNO ENAME                      COMM
---------- -------------------- ----------
      7369 SMITH
      7499 ALLEN                       300
      7521 WARD                        500
      7566 JONES
      7654 MARTIN                     1400
      7698 BLAKE
      7782 CLARK
  select empno, ename, nvl(comm, 0) comm 
  from emp;

     EMPNO ENAME                       COMM
---------- -------------------- -----------
      7369 SMITH                          0
      7499 ALLEN                        300
      7521 WARD                         500
      7566 JONES                          0
      7654 MARTIN                      1400
      7698 BLAKE                          0
      7782 CLARK                          0
      7788 SCOTT                          0

문제) 사원들의 사원번호, 사우너명, 커미션을 출력하되 커미션을 받지않는 (커미션의 값이 null) 사원들은 '없음'으로 출력하시오

    select empno, ename, nvl(comm,'없음') comm 
  from emp;                    //에러발생 인자가 서로 다른 자료형

  select empno, ename, nvl(TO_CHAR(comm),'없음') comm 
  from emp;                                                    

  EMPNO ENAME                COMM
---------- -------------------- --------------------------------------------------------------------------------
      7369 SMITH                없음
      7499 ALLEN                300
      7521 WARD                 500
      7566 JONES                없음
      7654 MARTIN               1400
      7698 BLAKE                없음
      7782 CLARK                없음
      7788 SCOTT                없음
      7839 KING                 없음
      7844 TURNER               0
      7876 ADAMS                없음
      7900 JAMES                없음
      7902 FORD                 없음
      7934 MILLER               없음   

           to_number                 to_date              
      <------------------    ----------------------->
 NUMBER                  CHAR                        DATE
      ------------------>    <-----------------------
           to_char                   to_char

문제) 10번 부서에 근무하는 사원들의 사원번호, 사원명, 직책, 매니저(상사)를 출력하시오.
(단, 매니저가 없는 경우 'CEO'를 출력하시오.)

select empno, ename, job, nvl(to_char(mgr),'CEO') mgr
from emp
where deptno = 10;

<ORDER BY절>

  • 정렬하여 출력

형식) 

SELECT 컬럼명
FROM 테이블명
WHERE 조건식
ORDER BY (정렬하고자 하는)컬럼명 [ASC|DESC];

---> ASC (ascending) : 오름차순 정렬(기본값!!) abcde 012345 가나다라
---> DESC(descending) : 내림차순 정렬 edcba 543210 라다나가

문제) 30번 부서에 근무하는 사원의 사원번호, 사원명, 급여를 출력하되
사원번호에 대해 내림차순으로 정렬하시오.

SELECT empno, ename, sal
FROM emp
WHERE deptno = 30
ORDER BY EMPNO DESC;

문제) 30번 부서에 근무하는 사원의 사원번호, 사원명, 급여를 출력하되
사원명에 대해 오름차순으로 정렬하시오.

SELECT empno, ename, sal
FROM emp
WHERE deptno = 30
ORDER BY EMPNO ASC;

문제) 30번 부서에 근무하는 사원의 사원번호, 사원명, 급여를 출력하되
급여를 많이 받는 순으로 출력하시오.

SELECT empno, ename, sal
FROM emp
WHERE deptno = 30
ORDER BY sal DESC;

문제) 가장 최근에 입사한 사원부터 사원번호, 사원명, 급여, 입사일을 출력하시오. 

SELECT empno, ename, sal, hiredate
FROM emp
ORDER BY hiredate DESC;

문제) 급여가 많은 순으로 사원번호, 사원명, 급여를 출력하시오.
(단, 급여가 같을 경우 사원명을 기준으로 오름차순 출력하시오.)

ORDER BY 1차정렬 컬럼명 [오름/내림] , 2차정렬 컬럼명 [오름/내림]

SELECT empno, ename, sal
FROM emp
ORDER BY sal DESC, ename ASC;

'클라우드 기반 웹 개발자 과정 공부 > DB' 카테고리의 다른 글

190529_DAY35, <복습>, <DDL>, <DML>  (0) 2019.05.29
190528_DAY ANSI JOIN 복습, 서브쿼리, DDL시작  (0) 2019.05.28
190527_DAY33 decode, case , join  (0) 2019.05.27
190524_Day32 DB3 복습 및 진도  (0) 2019.05.24
190522_Day30 DB(Oracle)-SQL  (0) 2019.05.22

+ Recent posts

티스토리툴바