Campanula

1.DUAL 테이블과 SQL 함수 분류

함수를 배우기 전에 한 행으로 결과를 출력하기 위한 테이블인 DUAL 테이블에 대해 살펴본다.

1)DAUL 테이블

SELECT 컬럼명, 컬럼명

FROM 테이블명

WHERE 조건식

ORDER BY 컬럼(별칭 OR 순번 작성 가능) ASC/DESC

SELECT 연산식 FROM 테이블명; (SELECT를 쓸려면 FROM 필요. 테이블이 가지고 있는 레코드 수 만큼 출력)

SELECT 연산식 FROM DUAL; 연산식 함수 확인하기 위한 테이블(한번만 나옴. DUAL: 임시 테이블, 산술 연산이나 가상  칼럼 등의 값을 하나의 레코드로 출력, DUMMY라는 한 개의 칼럼으로 구성)

테이블을 만들면 테이블을 만들때 소유주가 있어서 테이블 공통이 아님.(계정 체크 중요 HR(샘플용으로 제공)이면 HR에서만 사용 가능, 다른 사용자가 접근 못함. 권한을 주면 말이 다름. 테이블을 만들 때 사용자 체크)

2) 단일 행 함수와 그룹 함수로 SQL 함수 분류

함수를 이용하여 복잡한 질의를 간결하게 표현(함수를 주면 함수를 포함한게 컬럼이름=> 별칭을 줌)

SELECT DEPARTMENT_ID, LOWER(FIRST_NAME), SALARY --LOWER(FIRST_NAME)이 칼럼 이름이 된다.
FROM EMPLOYEES
WHERE DEPARTMENT_ID=30;

단일행 함수(행 마다 함수가 적용되어 결과를 반환)

그룹함수(하나 이상의 행을 그룹으로 묶어 연산)

2.문자함수

1) 소문자로 변환: LOWER

WHERE LOWER(JOB_ID)='it_prog';

2) 대문자로 변환: UPPER

WHERE EMAIL=UPPER('jking');

3) 첫 글자만 대문자 나머지 소문자: INICAP

SELECT EMPLOYEE_ID, FIRST_NAME, INITCAP(EMAIL)

4)두 문자를 연결: CONCAT

SELECT CONCAT(CONCAT(FIRST_NAME, ' '),LAST_NAME) "사원 이름"
--중복 사용 가능

5)문자 길이를 구함: LENGTH/LENGTHB

WHERE LENGTH(FIRST_NAME)=4;

6)문자열 일부만 추출: SUBSTR/SUBSTRB //DB시작은 1부터

- 시작위치 인자 값: 음수

뒤 쪽에서부터 세어서 시작위치를 잡음

WHERE SUBSTR(HIRE_DATE,1,2)=03;
WHERE SUBSTR(FIRST_NAME, -1,1)='k';

7)특정 문자의 위치를 구하는 INSTR/INSTRB

INSTR(FIRST_NAME, 'e')

8)특정 기호로 채우는 LPAD/RPAD =>자릿수를 맞출때 사용

SELECT LPAD('DATABASE',10, ' ') FROM DUAL;

SELECT CONCAT(RPAD(FIRST_NAME, 10,' '), CONCAT('($',CONCAT(SALARY,')'))) AS "사원 정보"

9)특정 문자를 잘라내는 TRIM 함수=>공백제거, 방향설정 가능, 앞뒤 공백 제거하고 데이터만 검색

SELECT TRIM(LEADING FROM '  ABCD  '), LENGTH(TRIM(LEADING FROM '  ABCD  ')) LT_LEN,
TRIM(TRAILING FROM '  ABCD  ') RT, LENGTH(TRIM(TRAILING FROM '  ABCD  ')) RT_LEN,
TRIM(BOTH FROM '  ABCD  ') BOTH1, LENGTH(TRIM(BOTH FROM '  ABCD  ')) BOTH1,
TRIM('  ABCD  ') BOTH2, LENGTH(TRIM('  ABCD  ')) BOTHLEN2
FROM DUAL;

3. 숫자함수

숫자형 데이터를 조작하여 변환된 숫자 값을 반환

1)ABS 함수/CEIL 함수/FLOOR 함수

ABS 함수는 절대값을 반환

CEIL(n) 무조건 소수점 아래를 올림, FLOOR(n) 함수 무조건 소수점 아래를 잘라냄

2) 특정 자릿수에서 반올림: ROUND 함수

ROUND(대상, 표시할 자릿수) //소수점 첫째자리가 1 기준

3)특정 자릿수에서 잘라내는 TRUNC 함수

지정한 자릿수 이하를 버린 것

4)나머지 값을 반환: MOD

4.날짜 함수

DATE형에 사용하는 함수 결과 값으로 날짜 또는 기간

날짜+숫자=>date

날짜-숫자=>date

날짜-날짜=>number(일수)

1)현재 날짜를 반환:SYSDATE

-날짜형 데이터는 더하기나 빼기 연산 가능(일자)

2)두 날짜 사이 간격을 계산: MONTHS_BETWEEN

-날짜와 날짜 사이의 개월 수를 구함.

TO_DATE: 문자를 날짜 데이터 타입으로 변화해 주는 함수

3)특정 날짜에 개월수를 더함: ADD_MONTHS

4) 해당 요일의 가까운 날짜: NEXT_DAY

5) 해당 달의 마지막 날짜를 반환:LAST_DAY

6)ROUND 함수의 다양한 적용

7)TRUNC 함수의 다양한 적용

5. 변환 함수

자료형을 변환시키고자 할때 사용

TO_CHAR: 날짜형 혹은 숫자형을 문자형으로 변환

TO_DATE: 문자형을 날짜형으로 변환

TO_NUMBER: 문자형을 숫자형으로 변환

1)TO_CHAR

DDD: 01~365일

DY: 요일의 약어(월, 화,..)

DL: 현재 일을 요일까지 표시

AM,PM / A.M, P.M / HH, HH12 (시간1~12)/ HH24 (24시간)

숫자형을 문자형으로 변환

TO_CHAR(숫자, '출력형식')

--숫자형을 문자형으로 변환하기
SELECT TO_CHAR(123467, 'FM999,999') --123,467
     , TO_CHAR(123467890, 'FM999,999,999') --123,467,890
     , TO_CHAR(123467, 'FML999,999')  --￦123,467
FROM DUAL;

TO_DATE: 날짜형 변환 함수

6.일반 함수

1)NULL을 다른 값으로 변환: NVL, NVL2

NVL(컬럼 또는 표현식, 대체값)

SELECT FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*NVL(COMMISSION_PCT,0) AS COMMISSION,
SALARY+(SALARY*NVL(COMMISSION_PCT,0)) AS TOTAL, JOB_ID
FROM EMPLOYEES
ORDER BY JOB_ID;

NVL2 함수

NVL2(컬럼 또는 표현식, NULL값이 아니면 처리할 구문, NULL 값이면 처리할 구문)

SELECT FIRST_NAME, SALARY, COMMISSION_PCT,
NVL2(COMMISSION_PCT, SALARY+(SALARY*COMMISSION_PCT), SALARY)TOTAL_SAL
FROM EMPLOYEES;

--<문제> 모든 직원은 자신의 상관(MANAGER_ID)이 있자.
--하지만 EMPLOYEES 테이블에 우일하게 상관이 없는 로우가 있는데 그 사원의 MANAGER_ID 칼럼 값이 NULL이다.
--상관이 없는 대표이사만 출력하되 MANAGER_ID 칼럼 값 NULL 대신 CEO로 출력
---01722: 수치가 부적합합니다
SELECT EMPLOYEE_ID, FIRST_NAME, NVL(MANAGER_ID, 'CEO')--MANAGER_ID=숫자타입
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;

SELECT EMPLOYEE_ID, FIRST_NAME, NVL(TO_CHAR(MANAGER_ID),'CEO')
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;

2) 선택을 위한 DECODE 함수

SWITCH CASE 문과 같이 여러가지 경우에 대해서 선택할 수 있는 함수

DECODE(표현식, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3, ... 조건절외 나머지 결과n )

3)조건에 따라 서로 다른 처리가 가능한 case 함수

CASE WHEN 조건1 THEN 결과1

           WHEN 조건2 THEN 결과2 

           WHEN 조건3 THEN 결과3

           ELSE 결과n

END( AS 별칭) 

NULL ->NOT NULL

유일 해야하는 고유 키를 지정: UNIQUE (UK)

칼럼값은 반드시 존재, 유일: PRIMARY KEY (PK) //NULL 허용X, 중복 허용X, 기본키

해당 칼럼값은 참조되는 테이블의 칼럼에 하나 이상과 일치하도록 하려면 FOREIGN KEY (FK)

1) 무결성 제약 조건의 개념과 종류

데이터 무결성: 데이터베이스 내의 데이터에 대한 정확성, 일돤성, 유효성, 신뢰성을 보장하기 위해 데이터 변경 혹은 수정시 여러가지 제한을 두어 데이터의 정확성을 보증하는 것

1)NOT NULL 제약조건

2)UNIQUE 제약조건

UNIQUE: 특정 칼럼에 대해 자료가 중복되지 않게 하는 것, 유일값

--NULL은 값에서 제외되므로 유일한 조건인지를 체크 하는 값에서 제외 //중복해서 들어갈 수 있다.
INSERT INTO EMP02(EMPNO, ENAME, JOB, DEPTNO)
VALUES(NULL, 'JONES', 'MANAGER', 20);

3)데이터 딕셔너리(시스템 테이블)

데이터 베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블.

4)제약조건 확인하기

제약조건(CONSTRAINTS)의 에러 메시지에 대한 정확한 원인을 알기 위해 

5)데이터의 구분을 위한 PRIMARY KEY 제약 조건

식별 기능을 갖는 칼럼은 유일하면서도 NULL 값을 허용하지 말아야 한다.

UNIQUE 제약 조건과 NOT NULL 제약 조건을 모두 갖고 있어야 하는데 두가지 제약을 모두 갖는 것이 PRIMARY KEY.

6)참조 무결성을 위한 FOREIGN KEY 제약 조건

참조의 무결성은 두 테이블 사이의 주종 관계에서 설정

먼저 존재해야하는 테이블이 주체가 되는 테이블: 부모 테이블, 참조하는 테이블: 자식 테이블

=부모 키가 되기 위한 칼럼은 반드시 부모 테이블의 기본 키나 유일 키로 설정되어 있어야 한다.

7)CHECK 제약 조건

입력되는 값을 체크하여 설정된 값 이외의 값이 들어오면 오류 메시지와 함께 명령이 수행되지 못하게 하는 것

GENDER VARCHAR2(1) CHECK (GENDER IN('M', 'F')),

2>제약 조건명 지정하기

사용자가 의미 있게 제약 조건명을 명시하여 제약 조건명을 명시하여 제약 조건명만으로도 어떤 제약 조건을 위배 했는지 알 수 있제 지정하는 방법

[테이블명]_[칼럼명]_[제약 조건 유형]

3>테이블 레벨 방식으로 제약 조건 지정하기

복합키로 기본키를 지정

ALTER TABLE로 제약 조건을 추가

4>제약조건 변경하기

1)제약조건 추가하기

테이블 생성이 끝난 후에 제약 조건을 추가하기 위해서는 ALTER TABLE로 추가

2)제약조건 제거하기

5>외래키가 설정된 데이터 삭제

PRIMARY KEY는 무조건 NOT NULL

고객 테이블 구조

테이블명: TB_CUSTOMER

기본키 설정방법: 컬럼명 자료형 PRIMARY KEY

TABLE 생성시 

컬럼명 자료형 NULL/NOT NULL PRIMARY KEY,

컬럼명 자료형 NULL/NOT NULL

nVARCHAR2(n)

없으면 고정길이, 있으면 가변길이

없으면 200BYTE, 있으면 400BYTE

크기

없으면 영문 1BYTE 한글 3BYTE 크기를 BYTE 수 단위로 받음, 있으면 유니코드 문자형 모든 문자 2BYTE 크기를 글자의 단위로 받음

문자니까 CHAR필수

1) 테이블에 새로운 행을 추가하는 INSERT문

새로운 행을 추가하기 위해 insert문을 사용하면 한 번에 하나의 행만 삽입된다. 작성한 칼럼 목록 순서대로 VALUES에 지정된 값이 삽입된다.

날짜->' '

숫자-> 그대로

문자-> 자바 CHAR(' ')/String(" ")

           오라클 CHAR/VARCHAR2->' ', 별칭->" "

작업단위를 짧게 잡아 COMMIT

INSERT 구문에서 오류 발생의 예 

• 칼럼명에 기술된 목록의 수보다 VALUES 다음에 나오는 괄호 안에 기술한 값의 개수가 적으면 에러가 발생.
• 칼럼명에 기술된 목록의 수보다 VALUES 다음에 나오는 괄호에 기술한 값의 개수가 많으면 에러가 발생.
• 칼럼명이 잘못 입력되었을 때
• 칼럼과 입력할 값의 데이터 타입이 서로 맞지 않은 경우에도 에러가 발생한다.

1>칼럼명을 생략한 INSERT 구문

모든 칼럼에 자료를 입력한 경우에는 굳이 칼럼 목록을 기술하지 않아도 됨. 

단, 디버그 시 용이하지 않으므로 칼럼명을 써 주는것이 좋다.

INSERT INTO DEPT
VALUES(20, 'RESEARCH', 'DALLAS');

NULL은 암시적으로 들어가게 하거나 컬럼 생략시 명시적으로 입력해 줘야 한다.

NULL 값을 갖는 칼럼을 추가하기 위해 NULL 대신 ''를 사용 가능=> NOT NULL일 때 '' 불가능(가장 많이 만나는 오류)

--암시적으로 NULL값 삽입
INSERT INTO DEPT(DEPTNO, DNAME)
VALUES(30, 'SALES');

--명시적
INSERT INTO DEPT
VALUES(40, 'OPERATIONS',NULL);

--DEPT 테이블의 전체 레코드 삭제
DELETE FROM DEPT;

--DEPT 테이블 삭제
DROP 사용

1. 레코드는 가져오지 않고 기존 테이블의 컬럼만 가져오게 구조 복사

CREATE TABLE 테이블

AS

SELECT 컬럼명, 컬럼명 FROM 테이블 WHERE 1=0;

2. 기존 테이블에 존재하는 데이터를 다른 테이블에 입력할 때 

INSERT INTO table_name(column1, column2,...)

SELECT column1, column2,...FROM table_name WHERE 조건;

2>테이블의 내용을 수정하기 위한 UPDATE 문

UPDATE table_name

SET column_name1 = value1, column_name2 = value2,...

WHERE conditions; //where절을 빼먹으면 다 업데이트 된다. 주의

1. 테이블의 모든 행 변경

--입사일을 오늘로 수정
UPDATE EMP
SET HIRE_DATE=SYSDATE;

--SYSDATE: 현재 날짜

2. 테이블의 특정 행만 변경

SUBSTR(SUBSTRING): 잘라와라

SELECT * FROM EMP WHERE SUBSTR(HIRE_DATE, 1, 2)='08'; --첫번째부터 2개를 잘라와라, 0번째부터X

3.테이블에서 2개 이상의 칼럼 값 변경

테이블에서 하나의 컬럼이 아닌 복수 개의 칼럼 값을 변경하려면 기존 SET절에 콤마를 추가하고 칼럼=값을 추가 하면 된다.

--LAST_NAME 이 Russell인 사원의 급여를 17000로, 커미션 비율이 0.45로 인상된다.
SELECT*
FROM EMP
WHERE LAST_NAME='Russell';

UPDATE EMP
SET SALARY=17000, COMMISSION_PCT=0.45
WHERE LAST_NAME='Russell';

3>테이블에 불필요한 행(레코드)를 삭제하기 위한 DELETE문

테이블에 특정 로우(행)의 데이터를 삭제

--새로운 테이블 생성한 후 30번 부서를 삭제
DELETE FROM DEPT WHERE DEPTNO=30;

DML

INSERT INTO 테이블명(컬럼, 컬럼,...)

VALUES(값, 값,...)

--EMP01테이블에 EMPLOYEES 테이블에서 부서코드가 30인 직원의 
--사번, 이름, 소속부서, 입사일을 삽입. 
--EMP_MANAGER테이블에 EMPLOYEES 테이블의 부서코드가 30인 직원의 
--사번, 이름, 관리자 사번을 조회하여 삽입.
--두개 이상의 테이블에 INSERT ALL(마지막에 SELECT가 있어야함)을 이용하여 한번에 삽입 가능
--단, 각 서브쿼리의 조건절이 같아야함

INSERT ALL

INTO EMP01
VALUES(EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, HIRE_DATE)

INTO EMP_MANAGER
VALUES(EMPLOYEE_ID, FIRST_NAME, MANAGER_ID)

SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, HIRE_DATE, MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=30;

SELECT * FROM EMP01;
SELECT * FROM EMP_MANAGER;

-- EMPLOYEES 테이블의 구조를 복사하여 사번, 이름, 입사일, 급여를 저장할 수 있는
-- 테이블 EMP_OLD와 EMP_NEW 생성

CREATE TABLE EMP_OLD
AS
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE 1=0;

CREATE TABLE EMP_NEW
AS
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE 1=0;

-- EMPLOYEES 테이블의 입사일 기준으로 2006년 1월 1일 이전에 입사한 사원의 사번, 이름,
-- 입사일, 급여를 조회해서 EMP_OLD 테이블에 삽입하고 그 후에 입사한 사원의 정보는 EMP_NEW 테이블에 삽입

--INSERT ALL INTO EMP_OLD
--VALUES (EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY)
--SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
--FROM EMPLOYEES WHERE HIRE_DATE <='06.01.01';

INSERT ALL
WHEN HIRE_DATE< '2006/01/01' THEN
    INTO EMP_OLD
    VALUES(EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY)
WHEN HIRE_DATE >= '2006/01/01' THEN
    INTO EMP_NEW
    VALUES(EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY)
    
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES;

4>MERGE문

조건을 비교해서 테이블에 해당 조건에 맞는 데이터가 없으면 INSERT문, 있으면 UPDATE를 수행하는 문장

MERGE INTO table_name

       USING(update나 insert 될 데이터 원천)

        ON(update될 조건)

WHEN MATCHED

MERGE INTO TB_CUSTOMER CU --CU별칭
    USING TB_ADD_CUSTOMER NC  --NC 별칭
    ON (CU.CUSTOMER_CD = NC.CUSTOMER_CD)
    WHEN MATCHED THEN 
        UPDATE SET  CU.CUSTOMER_NM = NC.CUSTOMER_NM,
                    CU.MW_FLG= NC.MW_FLG,
                    CU.BIRTH_DAY = NC.BIRTH_DAY,
                    CU.PHONE_NUMBER=NC.PHONE_NUMBER
    WHEN NOT MATCHED THEN
        INSERT (CU.CUSTOMER_CD, CU.CUSTOMER_NM, CU.MW_FLG, CU.BIRTH_DAY,
                CU.PHONE_NUMBER, CU.EMAIL, CU.TOTAL_POINT, CU.REG_DTTM)
        VALUES (NC.CUSTOMER_CD, NC.CUSTOMER_NM, NC.MW_FLG, NC.BIRTH_DAY,
                NC.PHONE_NUMBER, '', 0, TO_CHAR(SYSDATE, 'YYYYMMDDHHMISS'));

SELECT * FROM TB_CUSTOMER;

이제 실기만 남았다!ㅎㅎ

1.데이터를 조회하기 위한 SELECT

SELECT [DISTINCT]{*, column[Alias],...}

FROM 테이블명;

1) SELECT 컬럼, 컬럼 FROM 테이블 => 테이블로부터 컬럼값을 조회해라.

--: 쿼리문 주석

2) 칼럼 이름을 명시해서 특정 칼럼만 보기

DEPARTMENTS  테이블에서 부서번호와 부서명만 출력 (역순)

SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS;

--사원의 이름과 급여와 입사일자만을 출력하는 SQL문을 작성
SELECT FIRST_NAME, LAST_NAME, SALARY, HIRE_DATE FROM EMPLOYEES;

3) 칼럼 이름에 별칭 지정하기

AS로 컬럼에 별칭 부여하기: 칼럼을 기술한 바로 뒤에 AS라는 키워드를 쓴 후 별칭을 기술

별칭에 공백문자나 $,_,# 등 특수 문자를 표현하고 싶거나 대소문자를 구별하고 싶으면 ""을 사용한다. AS를 생략하고 ""를 사용하여 별칭부여가 가능. 한글이라서 묶는게 아니라 공백이 있으면 묶는다.

자바

-문자형: ''

-문자열:""

오라클

문자:''(대소문자 구분)//작은 따옴표

숫자: 그대로

4)Concatenation 연산자의 정의와 사용(연결 연산자)

-오라클에서는 여러 개의 컬럼을 연결할 때 사용하기 위해서 Concatenation 연산자를 제공해 준다. 컬럼과 쿤자열 사이에 연산자를 기술

--EMPLOYEES 테이블에서 여러 컬럼을 하나의 문자열로 출력

SELECT FIRST_NAME||'의 직급은'||JOB_ID||'입니다.' AS직급FROM EMPLOYEES;
SELECT FIRST_NAME||'의 직급은'||JOB_ID||'입니다.' "직급"FROM EMPLOYEES;

SELECT FIRST_NAME||'의 직급은'||JOB_ID||'입니다.' 직급 FROM EMPLOYEES;

SELECT FIRST_NAME||'의 입사일은'||HIRE_DATE||'입니다.' 입사일 FROM EMPLOYEES;
SELECT FIRST_NAME||' '||LAST_NAME 이름, SALARY 급여, HIRE_DATE 입사일 FROM EMPLOYEES;

5)중복된 데이터를 한번씩만 출력하게 하는 DISTINCT

EMPOLOYESS 테이블에서 칼럼 JOB_ID를  표시하되 중복된 값은 한번만 표시

SELECT JOB_ID FROM EMPLOYEES;

=>

SELECT DISTINCT JOB_ID FROM EMPLOYEES;

--직원들이 어떤 부서에 소속되에 있는지 소속 부서번호 출력하되 중복되지 않고 한번씩 출력
SELECT DISTINCT DEPARTMENT_ID "소속 부서번호" FROM EMPLOYEES;

6)WHERE 조건과 비교 연산자

--급여를 10000 이상 받는 직원을 대상
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY>=10000;

--급여를 3000 미만 받는 작원을 대상
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY<3000;

--사원에 급여를 1000 인상하여 사원번호, 사원명, 급여, 인상급여, 입사일을 출력하시오
SELECT EMPLOYEE_ID 사원번호, FIRST_NAME||' '|| LAST_NAME 사원명, SALARY 급여, SALARY+1000 인상급여, HIRE_DATE 입사일
FROM EMPLOYEES;

--EMPLOYEES 테이블에서 부서 번호가 110번인 직원에 관한 모든 정보만 출력하라
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=110;

--EMPLOYEES 테이블에서 급여가 5000미만이 되는 직원의 정보 중에서 사번과 이름, 급여를 출력
SELECT EMPLOYEE_ID 사번, FIRST_NAME 이름, SALARY 급여 
FROM EMPLOYEES 
WHERE SALARY<5000;

문자 데이터 조회

문자 데이터는 반드시 단일 따옴표 안에 표시. 대소문자를 구분 쿼리문은 구분 안하지만 데이터는 구분

날짜 데이터 조회

반드시 단일 따옴표 안에 표시 년/월/일 형식으로 기술

--문자 데이터 조회, 날짜 데이터 조회
--이름이 JOHN인 사람의 사원번호와 직원명과 업무ID을 출력, 문자 데이터는 대소문자를 구분하므로
--따옴표 안에 대소문자 구분
SELECT EMPLOYEE_ID 사원번호, FIRST_NAME 직원명, JOB_ID 업무ID 
FROM EMPLOYEES
WHERE FIRST_NAME='John';

--2008년 이후에 입사한 직원, 2008만 입력하면 에러 발생. 리터럴이 형식 문자열과 일치하지 않음
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE>='2008/01/01';

7)논리 연산자

• AND 연산자: 조건 모두 만족
• OR 연산자: 두 가지 조견 중에서 한가지만 만족하더라도 검색할 수 있도록 하기 위해 사용
• NOT 연산자: 반대되는 논리값

--급여가 5000에서 10000이하 직원 정보 출력
SELECT *
FROM EMPLOYEES 
WHERE SALARY>=5000 AND SALARY<=10000;

--부서번호가 100번이거나 직급이 FI_MGR인 직원
SELECT EMPLOYEE_ID, FIRST_NAME, PHONE_NUMBER, DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100 OR JOB_ID='FI_MGR';
--사원 번호가 134이거나 201이거나 107인 직원 정보 출력
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID=134 OR EMPLOYEE_ID=201 OR EMPLOYEE_ID=107;

BETWEEN AND 연산자

특정 범위 내에 속하는 데이터를 알아보려고 할 때 BETWEEN AND 연산자를 사용

column_name BETWEEN A AND B

컬럼>=값 AND 컬럼<=값

SELECT EMPLOYEE_ID 직원번호, FIRST_NAME 이름, SALARY 급여
FROM EMPLOYEES
WHERE SALARY BETWEEN 2500 AND 4500;

IN 연산자

동일한 칼럼이 여러 개의 값 중에 하나인지를 살펴보기 위해서 간단하게 표현할 수 있는 IN연산자 사용

column_name IN(A, B, C);

WHERE EMPLOYEE_ID=177 OR EMPLOYEE_ID=101 OR EMPLOYEE_ID=184

WHERE EMPLOYEE_ID IN(177, 101, 184)

--부서 번호가 10,20,30 중 하나에 소속된 직원의 직원번호, 이름, 급여를 출력
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID=10 OR DEPARTMENT_ID=20 OR DEPARTMENT_ID=30;

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20, 30);

--사원 테이블에서 JOB_ID가 'SA_MAN', 'ST_MAN', 'PU_MAN', 'AC_MGR'인 
--사원번호, 사원명, 직무번호를 출력
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE JOB_ID IN ('SA_MAN', 'ST_MAN', 'PU_MAN', 'AC_MGR');

8)LIKE 연산자

검색하고자 하는 값을 정확히 모를 경우 와일드카드와 함께 사용하여 원하는 내용을 검색하는 연산자

column_name LIKE pattern

%: 검색하고자 하는 값을 정확히 모를 경우 사용. %는 몇 개의 문자가 오든 상관없다는 의미

%자바: 자바로만 끝나면

자바%: 자바로만 시작하면

%자바%: 무조건 자바가 포함되면

_ _ _:3글자

_D%: 이름의 두번째 글자D

LOWER(FIRST_NAME): 소문자 변환해서 검색

--사원테이블(EMPLOYEES)에서 직무ID에 3번째 _를 포함하고 4번째 자리의 값이 P인 레코드를 조회
SELECT *
FROM EMPLOYEES
WHERE JOB_ID LIKE '___P%';
--ESCAPE
--LIKE 연산으로'%'나 '_'가 포함된 문자를 검색하고자 할때 사용
--'%'나 '_'앞에 ESCAPE로 특수문자를 지정하면 검색할 수 있다.
--특수문자는 아무거나 상관없이 사용가능
--구문 마지막에 ESCAPE에 사용할 문자열만 지정해주면 '_'나'%'를 검색에 사용할 수 있게 도와준다.

SELECT * FROM EMPLOYEES
WHERE JOB_ID LIKE '__\_P%' ESCAPE '\';

SELECT * FROM EMPLOYEES
WHERE JOB_ID LIKE '__$_P%' ESCAPE '$';

9)NULL을 위한 연산자

오라클에서는 컬럼에 NULL값이 저장되는 것을 허용한다.

NULL은 미확정, 알 수없는 값을 의미한다. 0도 빈 공간도 아닌 어떤값이 존재하기는 하지만 어떤 값인지를 알아낼 수 없는 것을 의미. NULL은 연산, 할당, 비교가 불가능

100+NULL=NULL

NULL이 저장되어 있는 경우에는 = 연산자로 판단할 수 없다.

IS NULL, IS NOT NULL을 사용

--이름에 a를 포함하지 않은 직원의 직원번호, 이름 출력
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE LOWER(FIRST_NAME) NOT LIKE '%a%';

--커미션을 받지 않는 사원
SELECT EMPLOYEE_ID, FIRST_NAME, COMMISSION_PCT, JOB_ID
FROM EMPLOYEES
WHERE COMMISSION_PCT = NULL;

--커미션을 받지 않는 사원
SELECT EMPLOYEE_ID, FIRST_NAME, COMMISSION_PCT, JOB_ID
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL;

--커미션을 받는 사원
SELECT EMPLOYEE_ID, FIRST_NAME, COMMISSION_PCT, JOB_ID
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

--자신의 직속상관이 없는 직원의 전체 이름과 직원번호, 업무ID를 출력하라
SELECT FIRST_NAME||' '||LAST_NAME 이름, EMPLOYEE_ID 직원번호, JOB_ID 업무ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;

--커미션을 받는 사원만 출력하되 사원번호, 이름, 급여, 수당율, 
--수당금액(계산식 - 급여*수당율)을 출력하라
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*COMMISSION_PCT 수당금액
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

SELECT 컬럼, 컬럼

FROM 테이블

WHERE 조건(별칭X)

ORDER BY 컬럼명 ASC(DESC)

ASC: 오름차순//작은값부터 디폴트

DESC: 내림차순//큰값부터

ORDER BY 컬럼

--직원번호, 이름, 급여, 부서번호를 급여가 높은 순으로 출력, EMPLOYEE_ID가 높은 순으로
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
ORDER BY SALARY DESC, EMPLOYEE_ID DESC;

--입사일이 가장 최근인 직원 순으로 직원번호, 이름, 입사일을 출력하라ㅏ
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE DESC;

--부서번호가 20, 50번 부서에서 근무하는 모든 사원들의 이름(FIRST_NAME), 부서 번호, 급여를 
--사원의 이름순(알파벳순)으로 출력하라
SELECT FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(20,50)
ORDER BY FIRST_NAME;

CREATE TABLE 테이블명

컬럼 자료형 NULL(DEFAULT)/NOT NULL 제약조건,

컬럼 자료형 NULL(DEFAULT)/NOT NULL 제약조건,

컬럼 자료형 NULL(DEFAULT)/NOT NULL 제약조건,...

기본 BYTE단위

• 테이블 명과 칼럼명을 부여하기 위한 규칙

• 반드시 문자(A-Z,a-z)로 시작해야 하며, 컬럼명은 최대 30바이트
• A~Z까지의 대소문자와 0~9까지의 숫자, 특수기호는 (_, $, #)만 가능하다
• 오라클에서 사용되는 예약어나 다른 객체명과 중복하여 생성할 수 없다.
• 공백 허용하지 않는다
• 대소문자 구별이 없다.
• 소문자로 저장하려면 ''로 묶어 주어야 한다.
• 한 테이블에 사용 가능한 컬럼은 최대 255개까지이다.

기존 테이블 복사

기존 테미블과 동일한 구조와 내용을 갖는 테이블을 생성 //기존 테이블의 구조+ 데이터를 그대로 복사하여 새로운 테이블 생성

CREATE TABLE EMPLOYEES02

AS

SELECT * FROM EMPLOYEES;

복사본으로 연습 후 반영

ALTER TABLE로 테이블 구조 변경

-ALTER TABLE 명령어는 테이블에서 칼럼의 추가, 삭제 , 칼럼의 타입이나 길이를 변경할 때 사용

• ADD(컬럼명 자료형)절을 사용하여 새로운 칼럼을 추가한다.
• MODIFY(컬럼명 자료형)절을 사용하여 기존 칼럼을 수정
• DROP COLUMN 컬럼명절을 사용하여 기존 칼럼을 삭제

ALTER TABLE table_name

ADD(column_name data_type)

ALTER TABLE로 기존 칼럼 수정

ALTER TABLE 명령어에 MODIFY절을 사용하여 칼럼을 수정. (데이터 타입, 크기를 변경가능)

ALTER TABLE table_name

MODIFY(column_name data_type expr,...);

-해당 칼럼에 자료가 없는 경우

칼럼의 데이터 타입을 변경할 수 있다.

칼럼의 크기를 변경할 수 있다.

-해당 칼럼에 자료가 있는 경우

칼럼의 데이터 타입을 변경할 수 없다.

크기를 늘릴 수는 있지만 현재 가지고 있는 데이터 크기보다 작은 크기로 변경할 수 없다.

--이미 존재하는 EMP01 테이블에 입사일 칼럼(CREDATE)을 날짜형으로 추가
ALTER TABLE EMP01
ADD(CREDATE DATE);

DESC EMP01;

--직급을 최대 30자까지 입력할 수 있도록 크기 수정
ALTER TABLE EMP01
MODIFY(JOB VARCHAR2(30));

DESC EMP01;

ALTER TABLE로 기존 칼럼명 변경

ALTER TABLE table_name

RENAME COLUMN old_name TO new_name;

ALTER TABLE로 기존 칼럼 삭제

ALTER TABLE table_name

DROP COLUMN column_name;

DROP TABLE로 테이블 구조 삭제

DROP TABLE table_name;

휴지통(recyclebin)

--휴지통 비우기
PURGE RECYCLEBIN;

--실수로 지운 테이블이라 삭제를 취소하려면 다음과 같은 명령으로 다시 복구하면 된다.
--flashback table table_name to before drop;

--새로운 이름으로 복원하는 방법
FLASHBACK TABLE EMP01 TO BEFORE DROP
RENAME TO EMP02;

6) 테이블의 모든 로우를 제거해 TRUNCATE 문

TRUNCATE table table_name; //이 작업은 절대 복원 불가능 완전삭제

• 테이블을 Truncate하면 테이블의 모든 행이 삭제 되고 사용된 공간이 해제된다.
• TRUNCATE TABLE은 DDL명령이므로 롤백 데이터가 생성되지 않는다.
• DELETE명령으로 데이터를 지우면 롤백 명령어로 복구 할 수 있지만 TRUNCATE로 데이터를 삭제하면 롤백을 할 수 없다
• 외래키가 참조중인 데이블은 TRUNCATE 할 수 없다.
• TRUNCATE 명령을 사용하면 삭제 트리거가 실행되지 않는다.

데이터는 관찰의 결과로 나타난 정량적(양을 헤아리는 것) 혹은 정양적.

데이터 베이스: 조직에 필요한 정보를 얻기 위해 논리적으로 연관된 데이터를 모아 통합, 검색에 용이하게 데이터를 저장하고 수정, 삭제 용이

데이터 베이스 관리 시스템: 지속적으로 유지 관리해야하는 데이터의 집합을 데이터베이스

방대한 양의 데이터를 편리하게 저장하고 효율적으로 관리하고 검색할 수 있는 환경을 제공

행: 레코드(로우) 열: 칼럼(구분",")

DBMS 장점

• 데이터의 공유가 가능하다
• 데이터 중복성이 감소
• 데이터 불일치를 피할 수 있다
• 데이터의 무결성을 유지
• 데이터 보안을 유지
• 표준화가 가능

공통사용자는 c##<사용자명>의 형태로 이루어지며 PDB사용자는 각 PDB에서 별도로 생성하여 PDB에 로그인

접근이 안되거나 ip가 뜰 때 service들어가서 서버 재구동

oracleServiceXE, oracleListener 

1.오라클 서버의 IP주소 필요

2.포트 (기본 포트: 1521)

3.서비스 이름

SQL: 구조화된 질의 언어

쿼리문: 대소문자 구분X, 값: 대소문자 구분

DB 상에서 데이터를 읽고 , 쓰고 삭제하는 등 데이터 관리를 위한 프로그램 언어

DDL(Data Definition Language, 데이터 정의어)

-데이터베이스 객체를 생성 또는 변경, 제거 할 때 사용(자동 commit)

DML(Data Manipulation Language, 데이터 조작어)

-데이터를 조작하는 역할

TCL(Transaction Control Language, 트랜잭션 처리어)

-트랜젝션 관련 문장으로 데이터 조작어에 의해 변경된 내용을 관리

데이터를 추가, 변경, 삭제한 내용은 파일에 즉각 저장되는 것이 아니다. 따라서 사용자의 실수로 잘못 입력한 명령어라면 언제든지 이전 상태로 되돌릴 수 있다. 실수 없이 완벽하게 입력된 명령어라는 것이 확인될 때에만 영구히 저장하도록 TCL을 사용(all or nothing)

DCL(Data Control Language, 데이터 제어어)

-사용자마다 데이터를 사용할 수 있는 권한이 달라야 하는데 이렇게 특정 사용자에게 권한을 부여하거나 제거하기 위해서 사용하는 명령어가 DCL

SQL*Plus(보기가 어렵다.)

-오라클에서 제공하는 기본 개발 도구, 오라클을 설치할 때 함께 설치

hr 계정은 오라클에서 실습을 위하여 제공하는 기본 사용자 계정 및 샘플 데이터베이스(실제 오라클 관련 서적 및 명령 예에서 가장 많이 사용되는 계정)

SQL Plus 실행: 명령 프롬프트에 sqlplus sys/비밀번호@localhost:1521/xe as sysdba(CDB_SYS)

SQL>alter pluggable database all open; 

SQL>alter pulggable database all save state; 

SQL>alter user hr account unlock; (잠금 해제)(PDB_SYS)
SQL>alter user hr identified by hr1234; (비밀 번호 설정)

SQL> select * from tab; (모든 테이블)

SQL Developer

1.오라클 서버 IP주소 - localhost(127.0.0.1/192.168.0.1)

2.오라클 서버 port 번호-1521

3.11g->SID//18c->서비스 이름

v 시작 SystemTable

F9 실행

CDB=xe

PDB=xepdb1

SYSDBA=최고권한자

[HR 사용자에 저장된 테이블 정보]

[부서 테이블 (DEPARTMENTS)의 컬럼]

1. SQL은 관계 DB를 처리하기 위해 고안된 언어로, 독자적인 문법을 갖는 DB 표준 언어

2. SQLPlus는 SQL 언어를 구현하여 오라클 RDBMS를 관리할 수 있는 오라클 사의 클라이언트 틀 제품면

3.SELECT 문은 데이터베이스로부터 필요한 데이터를 가져온다.

4.DML문은 INSERT, UPDATE, DELETE 문 등으로 구성되며, 행을 삽입하고 변경하고 삭제하는 역할

정적 요소의 객체화: $("셀렉터")는 이미<body> 안에 존재하는 요소에 대한 객체화 방식이다.

동적 요소의 객체화: let 객체이름=$("<태그명>")

생성한 요소를 화면에 적용하기

• A.html(B): A요소의 시작태그와 종료 태그 사이의 내용을 B로 대체한다.
• remove(): 해당 요소를 삭제한다.
• A.append(B): A에 B를 추가하다. 태그 사이의 내용을 그대로 유지하면서 B의 내용을 뒤에 추가한다.
• B.appendTo(A): B를 A에 추가하다.

css("속성"): 속성값 읽기

css("속성","값"): 속성값 변경/ 추가

css({"속성1":"값1","속성2":"값2","속성3":"값3"): 속성값 읽기

addClass("클래스명"): 특정요소에게 CSS클래스를 적용할 겅우에 사용

removeClass("클래스명"):HTML 태그 요소에 적용된 특정 CSS클래스를 제거

ToggleClass("클래스명"):