IT 개발입문 공부정리

Data Type을 사용할 때
우리가 DB서버를 가지고 있을 경우에는 보통 경로만 저장하기 때문에 BLOB, CLOB를  잘 안씀

SELECT 'ABCDE'
FROM DUAL --> DUAL은 더미테이블 없는 테이블인데 프린트문 처럼 쓴다

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

NULL 값
  - 한 행의 특정 열에 데이터 값이 없으면 널이라고 함
  - 널 값은 알 수 없는 값으로 0 또는 공백과 다름
  - 널 값을 포함하는 연산이 경우 결과도 널이다.
  - 모르는 데이터에 숫자를 더하거나 빼도 결과는 모르는 데이터인 것과 같다.
  - 숫자를 0으로 나누면 오류가 발생하지만 널로 나누면 결과는 널이다.

============================================================================
Literal 문자열
  - 열이름 열별칭이 아니면서 SELECT 목록에 포함된 문자, 숫자 또는 날짜이며
    각 행이 반환될 때마다 출력된다.
  - 텍스트 리터럴 문자열은 질의 결과에 포함되어 SELECT 목록의 열과 동일하게 취급
  - 날짜 및 문자 리터럴은 반드시 작은 따옴표(' ')로 묶어야 하지만 숫자 리터럴은 
    작은 따옴표로 묶지 않는다.

============================================================================Pseudo Column(의사 열) : query가 가능한 열
  - rownum을 이용하여 검색된 row의 수를 검색할 수 있다.
  - rownum은 각 검색된 행의 일련번호로써 order by에 의한 정렬 이전에 부여된다.
  - rowid는 테이블 내의 특정한 행을 유일하게 구별해주는 값으로 데이터 타입은 ROWID 이다.

============================================================================[4]
    SELECT 컬럼 [AS] 별칭,,,,
    FROM 테이블명 별칭,,,,
    WHERE condition(s)

  - 검색결과에 대한 제한을 둘 때 사용
  - WHERE은 FROM절 뒤에 온다
  - 문자열과 날짜 값은 작은 따옴표로 묶어서 표시
  - WHERE절은 생략가능하고 생략할 경우 모든 행이 검색됨
  - WHERE 조건식의 결과가 TRUE인 ROW만 선택한다.
  - WHERE 절은 별칭 사용 불가
  - JOIN 할 때 테이블들을 연결하는 조건으로 사용됨
  - 산술연산, 비교연산, 컬럼, 숫자, 문자상수, like, in, between, is null, is not null
  - 함수, 논리연산자, any, all

============================================================================Q1) 사원테이블에서 사원의 이름이 FORD인 정보를 출력하자
SELECT *
FROM EMP
WHERE ENAME = 'FORD';

Q2) 사원테이블에서 직업이 매너지인 사원의 번호, 이름, 직업, 급여를 리턴하자
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE JOB = 'MANAGER';

Q3) 사원테이블에서 급여가 3000 이상인 사원의 번호, 이름, 직업, 급여를 조회하자
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL >= 3000;

Q4) 사원테이블에서 급여가 1300에서 1700사이에 해당되는 사원의 이름, 직업, 급여, 부서번호를 출력
SELECT ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL BETWEEN 1300 AND 1700;  -->  BETWEEN 이상 이하 적힌 값 포함

EXPR: BETWEEN 하한값 A AND 상한값 B
  - 두 사이에 값을 출력할 때 사용되며 조건절에 명시된 범위의 값을 리턴
  - EXPR >= A AND EXPR <= B와 같다
  - NOT BETWEEN 하한값 A AND 상한값 B  ==> EXPR < A OR EXPR > B와 같다

============================================================================Q5) in (여러 값 중 하나와 일치하는 값)
  - 여러 값중에 어느 하나와 일치한다.
  - in은 =ANY 와 같다
  - NOT IN은 !=ALL 과 같다.
  - 형태는 IN(값1, 값2, 값3)이며 비교 대상이 괄호안의 값에 포함될 경우 조건을 만족하게 된다.
  - IN은 비교하는 값이 하나이더라도 ()와 같이 사용하며 여러형태의 데이터 타입 모두 비교 가능하다.

사원테이블에서 사원번호가 7902, 7788, 7566인 사원의 사번, 이름, 급여, 입사일을 출력하자
SELECT EMPNO, ENAME, SAL, HIREDATE
FROM EMP
WHERE EMPNO IN (7902, 7788, 7566);

Q6) LIKE 연산자 (문자의 패턴이 일치하는 값)
  - % : 임의의 길이 문자열 (공백문자가능), 0개 또는 1개 이상의 문자와 ㄷ응
  - _ : 한글자
  - ESCAPE : 검색할 문자에 % _ 문자를 포함할 때

사원의 이름 중 D자로 끝나는 사원을 출력해보자
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%D';

사원의 이름 중 3번째에 A가 들어가는 사원의 이름을 출력
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '__A%';


CREATE TABLE TEST(
NAME VARCHAR2(15)
);

DROP TABLE TEST;

INSERT INTO TEST VALUES('HONG_GILDONG');
INSERT INTO TEST VALUES('A_GILDONG');
INSERT INTO TEST VALUES('BB%GILDONG');

이름에 _문자가 들어있는 이름을 출력하고 싶다.
SELECT * FROM TEST WHERE NAME LIKE '%#_%' ESCAPE '#';  
--> ESCAPE로 정한 문자 뒤에 오는 기호를 문자열로 인식하겠다.


Q7) 사원테이블에서 커미션이 NULL인 사원을 조회하자
SELECT *
FROM EMP
WHERE COMM IS NULL;

============================================================================함수 : 단일행(문자함수, 숫자함수, 날짜함수, 변환함수, 기타함수), 다중행
문자함수
UPPER, LOWER, 
INITCAP : 단어의 첫문자는 대문자 나머지 소문자, 
CONCAT, SUBSTR, 
INSTR : 특정 문자열의 위치를 숫자로 리턴, 
LENGTH, CHR, ASCII

Q8) 사원테이블에서 SCOTT의 사원번호, 성명, 직업, 부서번호를 출력하되 모두 소문자로 출력
SELECT EMPNO, LOWER(ENAME), LOWER(JOB), DEPTNO
FROM EMP
WHERE ENAME = 'FORD';

Q9) DEPT 테이블에서 컬럼의 첫글자만 대문자로 변환하여 모든 정보를 출력
SELECT DEPTNO, INITCAP(DNAME), INITCAP(LOC)
FROM DEPT;

Q10) SELECT EMPNO, ENAME, CONCAT(EMPNO, ENAME) "별칭"
FROM EMP;

Q11) 사원테이블에서 이름의 첫글자가 K보다 크고 Y보다 작은 사원의 정보를 출력하되
이름순으로 정렬해보자 SUBSTR()
SELECT *
FROM EMP
WHERE SUBSTR(ENAME, 1, 1) > 'K' AND SUBSTR(ENAME, 1, 1) < 'Y';

Q12) 아래해보기
SELECT ENAME, LPAD(ENAME, 15, '*'), SAL, LPAD(SAL, 10, '*')
FROM EMP
WHERE DEPTNO = 10;

Q13) EMP 테이블에서 10번 부서의 사원에 대하여 담당 업무 중 좌측에 'A'를 삭제하고
급여 중 좌측의 1을 삭제하여 출력해라. LTRIM
SELECT LTRIM(JOB, 'A'), LTRIM(SAL, 1)
FROM EMP
WHERE DEPTNO = 10;

Q14 EMP 테이블에서 10번 부서의 사원에 대하여 담당 업무 중 우측에 'T'를 삭제하고
급여 중 우측의 0을 삭제하여 출력해라 . RTRIM
SELECT RTRIM(JOB, 'T'), RTRIM(SAL, 0)
FROM EMP
WHERE DEPTNO = 10;

Q15) REPLACE 함수를 이용해서 사원 이름에 SC문자열을 *?로 변경해서 조회
SELECT ENAME, REPLACE(ENAME, 'SC', '*?') 결과
FROM EMP;

Q16) TRANSLATE 함수를 이용해서 사원이름에 SC문자열을 *?로 변경해서 조회
SELECT ENAME, TRANSLATE(ENAME, 'SC', '*?') 결과
FROM EMP;

============================================================================숫자함수
ROUND : 반올림
TRUNK : 버림
MOD(M, N) : M을 N으로 나눈 나머지
ABS : 숫자값을 절대값으로
FLOOR : 해당 수보다 작거나 같은 정수 중 큰 정수 값을 리턴 -> 가우스함수
CEIL : 해당 수보다 크거나 같은 정수 중 가장 작은 정수 값을 리턴
SIGN : 1, -1, 0
POWER(M, N) : M의 N승

Q17) SELECT ROUND(0.5) 기, ROUND(-0.5) 니, ROUND(456.678, 0) 디, ROUND(456.678, 2) 리,
ROUND(456.678, -1) 미, ROUND(-456.678, -1) 비, ROUND(-456.678, 2) 시
FROM DUAL;

============================================================================날짜 데이터
  - century, year, month, day, hours, minutes, secods = 7byte
  - 기본 date format : 'RR/MM/DD', 'DD - MON - RR'
          RR = Y2K 고려해서 년도를 표기한 것( 00~49 : 2000년대, / 50~99 : 1900년대)

  - 2011년 6월 7일 오전 3시 15분 47초 -> 07-JUN-11(DD-MON-RR)
  century, year, month, day, hours, minutes, seconds
     20      11      06     07     3        15         47

DATE + NUMBER : 날짜에 해당 일수를 더한 결과를 리턴, 날짜형태로 표시
DATE - NUMBER : 날짜에 해당 일수를 뺀 결과를 리턴, 날짜형태로 표시
DATE1 - DATE2 : 두 날짜 사이의 경과 일수를 나타냄, 숫자로 표시
DATE + NUMBER/24 : 날짜에 해당 시간을 더해서 리턴, 날짜 형태로 리턴

SYSDATE : 7BYTE를 가지고 있지만 YY/MM/DD형식만 리턴
    SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
SYSTIMESTAMP : TIMESTAMP WITH TIMEZONE 형식의 시스템 날짜 리턴
CURRENT_DATE : 현재 SESSION의 날짜 정보를 일/월/년도 24시:분:초 리턴
CURRENT_TIMESTAMP : TIMESTAMP WITH TIMEZONE 형식으로 
        CURRENT_DATE SESSION TIMEZONE 형식으로 리턴
EXTRACT : DATETIME 또는 INTERVAL의 값 표현 형식에 지정된 DATETIME 필드의 값을
              추출해서 리턴한다.
    EX) 오늘날짜에서 연도만 출력하고싶다.
         SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;
         사원테이블에서 사원의 이름, 입사일자에서 입사한 월 정보만 조회하자
         SELECT ENAME, EXTRACT(MONTH FROM HIREDATE) FROM EMP;

MONTHS_BETWEEN(D1, D2) : 두 DATE 사이의 기간을 개월 수로 리턴
ADD_MONTH(D1, N) : D1 날짜에 N개월을 더함
NEXT_DAY(D1, 'CHAR') : D1보다 이후 날짜이고 'CHAR'에 지정한 요일에 해당하는 날짜
LAST_DAY : 해당 월의 마지막 날짜를 리턴


Q18) 입사일이 81년도 9월 9일 이후에 입사한 사원을 출력해보자
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE >= '81/09/09';    -> 81-09-09도 되고 1981/09/09도 된다.

Q19) 아래 결과와 같이 사원 테이블에서 현재까지 근무 일수가 몇주 몇일인지 조회한다.
SELECT ENAME, HIREDATE, SYSDATE, SYSDATE-HIREDATE "TOTAL DAYS", 
TRUNC((SYSDATE-HIREDATE) / 7) "WEEKS",
ROUND(MOD((SYSDATE-HIREDATE), 7)) "DAYS"
FROM EMP
ORDER BY 4 DESC;

Q20) 사원테이블에서 10번 부서의 사원들의 현재까지의 근무 개월 수를 계산해서 리턴받자
SELECT ENAME, HIREDATE, SYSDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) "M",
TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) "T"
FROM EMP
WHERE DEPTNO = 10;

Q21) 사원테이블에서 10번 부서 사원의 입사일로부터 5개월이 지난 날짜를 계산하여 출력
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 5) A_MONTH
FROM EMP
WHERE DEPTNO = 10
ORDER BY 2 DESC;

Q22) 사원테이블에서 10번 부서사원의 입사일로부터 돌아오는 금요일을 계산해보자
SELECT ENAME, HIREDATE, NEXT_DAY(HIREDATE, 6) "금"
FROM EMP
WHERE DEPTNO = 10;

============================================================================ROUND
일을 반올림할 때 정오를 넘으면 다음날 자정을 출력하고 넘지 않으면 그날 자정을 출력
월을 반올림할 때 15일 이상이면 다음달 1일을 출력하고 넘지 않으면 현재 달 1일을 출력
년도를 반올림할 때 6월을 넘으면 다음해 1월 1일을 출력하고 넘지 않으면 그 해 1월 1일을 출력

TRUNC
일을 절삭할 때 그 날 자정을 출력
월을 절삭할 때 그 달 1일을 출력
년을 절삭할 때 그 해 1월 1일을 출력
단, 주의할 점은 시간 정보가 중요하지 않을 경우에 시간 정보를 생략하고 입력하거나
연산할 때 편리하다.

============================================================================컬럼폭 조절 숫자 : COLUMN COMM FORMAT 9999
컬럼폭 조절 문자 : COLUMN ENAME FORMAT A10

             ←                  →
      TO_NUMBER       TO_DATE
NUMBER ↔ CHARACTER ↔ DATE         NUMBER를 DATE로 갈면 이렇게 복잡
         TO_CHAR        TO_CHAR
             →                  ←
============================================================================TO_CHAR
    숫자 FORMAT : 숫자(9, 0), 그룹 구분자(,), G, 소수점, 통화 $ L, 공백제거 FM,
                         표기법 EEEE(지수), RN(로마표기자), rn, X(16진수표기), 
                         기호표시 MI : 숫자가 음수일경우 마지막에 -
                                     PR : 음수인경우 <>안에 숫자표시
                                       S : 음수인경우 숫자 앞에 - 

    날짜 FORMAT : 기원 : AD/BC, A.D./B.C.
                         년도 : YYYY, YYY, YY, Y, YEAR, SYYYY(BC인경우 음수로 표시), RR
                         분기 : Q
                         월 : MM(1~12), MON(JAN), MONTH(JANUARY), RM(로마식)
                         주 : W(1,2,3,4,5), WW(1년 54주, 1월 1일부터 1월 7일까지가 1주차), 
                                               IW(IOS 주표시 1월 1일부터 첫번째 일요일까지가 1주차)
                         일자 : D, DD, DDD, J(BC4712 이후의 줄리안력 일자)
                         요일 : DY(MON), DAY(MONDAY)

    시간 FORMAT : 오전/오후 : AM / PM,, A.M. / P.M.
                         시간 : HH, HH12, HH24
                         분 : MI
                         초 : SS, SSSS(자정을 기준으로 하루 단위의 초 표시)
                         시간대 : TZD(서머타임), TZH, TZM, TZR
                         기타 : .. / ;

강의목표
1. db설치 후 속성파일을 확인할 수 있다.
2. sid의 개념을 이해할 수 있다.
3. select 구문을 이해하고 활용할 수 있다.

뭔가 말하는 내용을 받아 적은 것이라 문장이 이상하다....

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

oracle11(XE)를 다운받아서 설치한다. oracle12부터는 c가 붙으며 cloud 연동이되서 무겁다.

오라클을 설치하면 데이터를 저장하는 서비스를 XE로 접근하게 해놨다. tomcat과 카타리나 같은 느낌.

XE = SID = Service ID

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

Window의 서비스 앱을 실행해서 oracle을 확인해보자.

OracleServiceXE : 오라클 서비스를 XE라는 서비스 아이디로 실행한다.
OracleXETNSListener : 클라이언트가 서버(XE)에 접속하게 해준다.

오라클 포트
1521: Oracle database listener
2030: Oracle Services for Microsoft Transaction Server
8080: HTTP port for the Oracle Database XE graphical user interface

port number :8080은 톰캣이랑 겹친다. WAS랑 서버(오라클)랑 있으면 오라클이 우선순위이므로
우선순위가 낮은 톰캣의 포트를 바꿔야한다.

C:\tomcat\apache-tomcat-9.0.45\conf에서 servel.xml을 실행한 후

69번째 라인의 포트를 8080에서 8787로 바꾼다.

    <Connector port="8787" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="8443" />

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

C:\oraclexe\app\oracle\product\11.2.0\server\log\diag
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN에서 
listner.ora, tnsnames.ora는 확인해야한다.
얘네 2개가 서비스에 OracleServiceXE, OracleXETNSListener 두 개의 속성을 확인한다.

C:\oraclexe\app\oracle\product\11.2.0\server에서 다른 프로그램이랑 연결하는 객체(드라이버)를 확인할 수 있다.
ASP.net : C#연동 객체
jdbc : java 
odbc, ode.net, odp.net : 기타언어
oledb : 윈도우 연결객체

겸사겸사 java의 jdk와 jre에 jdbc를 복사해놓는다.

C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib에서 

ojdbc5.jar, ojdbc6.jar, ojdbc6_g.jar 파일을 복사해서
C:\Program Files\Java\jdk1.8.0_291\jre\lib\ext,
C:\Program Files\Java\jre1.8.0_291\lib\ext 두 곳에 붙여넣는다.

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

cmd에서 오라클을 실행하는 방법

1. sqlplus 입력 -> id 입력 -> pw 입력

2. sqlplus id 입력 -> pw 입력

3. sqlplus id/pw 입력

미리만들어진 sql문을 실행하려면
@"c:\test\scott.sql"

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

[1] 단일 select
    SELECT 컬럼리스트,,,,,[*]
    FROM 테이블명;

Q1) 생성된 모든 테이블을 확인
SELECT *
FROM TAB; 

Q2) 접속된 계정을 확인
SHOW USER;

Q3) 생성된 테이블 중 사원테이블(EMP)의 구조 확인
DESC EMP

Q4) 사원테이블에서 사원의 이름, 사번을 출력 해보자.
SELECT ENAME, EMPNO
FROM EMP;

Q5) 사원테이블과 부서테이블의 전체 내용을 출력하자
SELECT *
FROM EMP, DEPT; 

--> (EMP1 - DEPT1, EMP2 - DEPT1 ... EMP11 - DEPT4, EMP12 - DEPT4) 해서 12 * 4 = 48개 나옴

Q6) 사원의 이름과 부서번호, 부서이름을 출력하자
SELECT ENAME, DEPTNO, DNAME
FROM EMP, DEPT; --> 오류

SELECT EMP.ENAME, EMP.DEPTNO, EMP.DNAME
FROM EMP, DEPT;

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

[2]
    SELECT 컬럼 [AS] 별칭,,,,
    FROM 테이블명 별칭,,,,;

Q7) 사원의 이름고 부서번호, 부서이름을 테이블에 각각 사원, 부서로 별칭을 주어 출력하자
SELECT 사원.ENAME, 부서.DEPTNO, 부서.DNAME
FROM EMP 사원, DEPT 부서;

Q8) 사원테이블에서 사원의 이름, 부서번호, 봉급이라고 별칭을 주고 출력하자
SELECT ENAME "사원의 이름", DEPTNO "부서번호", SAL AS 봉급
FROM EMP;      --> 띄어쓰기가 있으면 "", AS는 생략 가능

Q9) 문자열 연결 연산자 || : SELECT 위치에 지정 이름 님이라고 출력하자
SELECT ENAME||'님'
FROM EMP;

Q10) 00님은 00일에 입사했다. 를 출력해보자
SELECT ENAME||'님은 '||HIREDATE||'일에 입사했다.'
FROM EMP;

Q11) 중복 데이터를 삭제하는 DISTINCT(열중복제거)
SELECT DISTINCT 컬럼
FROM 테이블;

SELECT DISTINCT JOB
FROM EMP;

Q12) 사칙연산 (컬럼)
사원테이블에서 연봉을 구하자
봉급 * 12 + 커미션
SELECT ENAME, SAL*12 + COMM
FROM EMP;  --> NULL은 더해지지 않는다

SELECT COMM, NVL(COMM, NULL을 대체할 값)
FROM EMP;

SELECT ENAME, SAL*12 + NVL(COMM, 0) AS 연봉
FROM EMP;

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

[3]
    SELECT 컬럼 [AS] 별칭,,,,
    FROM 테이블명 별칭,,,,;
    ORDER BY 정렬할 컬럼 [ASC(DEFAULT), DESC]  --> 컬럼명 대신 SELECT 순서에 따라 1234로 가능

Q13) 사원테이블에서 사원의 이름, 사번을 사번의 역순으로 출력해보자
SELECT ENAME, EMPNO
FROM EMP

ORDER BY 2 DESC;