KH 41 ~ 42일차 - SQL

1.    SQL

-      SQL = Structured Query Language ( 구조화된 질의 언어 )라는 의미이다.

-      SQL은 테이블에 저장된 데이터를 CRUD ( Create, Read, Update, Delete )할 때 사용

-      CREATE를 위해서는 INSERT문을 사용한다.

-      READ를 위해서는 SELECT문을 사용한다.

-      UPDATE를 위해서는 UPDATE문을 사용한다.

-      DELETE를 위해서는 DELETE문을 사용한다.

-      이때 개발자는 주로 Read(조회) 기능을 많이 사용하게 된다.

-      + SQL문을 통해 출력되는 테이블을 결과 set이라고 부른다.

---

2.    SQL문의 종류

-      1 ) DQL ( Data Query Language ) : 데이터 조회 언어로 SELECT문에 해당한다.

-      2 ) DML ( Data Manipulation Language ) : 데이터 조작언어로 CRUD 중 C / U / D할 때 사용하는, INSERT / UPDATE / DELETE문이 해당한다.

-      3 ) DDL ( Data Definition Language ) : DB 객체 정의 언어로 테이블 생성 등에 사용된다.

-      4 ) DCL ( Data Control Language ) : 데이터 제어 언어로 접근권한을 조정하는 문장들이 이에 해당된다.

-      5 ) TCL ( Transaction Control Language ) : 트랜잭션 제어 언어

---

-- ***************************************

-- 1. SELECT 문의 기본구조와 절의 실행순서 (*****)

-- ***************************************

-- Clauses (절)                 실행순서

-- SELECT 절                        (5)

-- FROM 절                          (1)

-- WHERE 절                         (2)

-- GROUP BY 절                      (3)

-- HAVING 절                        (4) // 그룹을 필터링

-- ORDER BY 절                      (6) // 정렬기능

-- ***************************************

-- 실행 순서 : FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

-- ***************************************

-- ***************************************

-- 2. SELECT 문의 기본 문법

-- ***************************************

--  SELECT [DISTINCT] { *, column [AS] [alias] , ... }

--  FROM <테이블명>

-- ***************************************

-- 대괄호 [] 안에 있는 것은 선택적이지만, 중괄호 {} 안에 있는 내용은 필수이다.

-- ***************************************

---

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

-- 1. To project all colums of table ( 모든 컬럼을 조회 )

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

-- 모든 사원의 모든 컬럼을 조회

SELECT *

FROM employees;

-- 모든 부서의 모든 컬럼을 조회

SELECT *

FROM departments;

-- 이때 *는 와일드카드라는 의미로 ALL이라는 의미이다.

-- VS code에서 실행될 때에는 HR.cloud로 접속한 후에 SQL문장을 실행해야 결과가 나온다.

 

---

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

-- 2. 보여주고 싶은 컬럼(colum)만 보여주기  (***)

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

--  SELECT column1[,column2, ...., column N]

--  FROM table;

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

SELECT

    employee_id,

    last_name,

    hire_date,

    salary

FROM employees;

 

---

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

-- 3. 산술 연산자 활용하기 ( + , - , * , / )  (***)

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

SELECT

    salary,

    salary + 100

FROM employees;

SELECT

    salary,

    salary - 100

FROM employees;

SELECT

    salary,

    salary * 100

FROM employees;

SELECT

    salary,

    salary / 100

FROM employees;

SELECT

    last_name,

    salary,

    salary * 12

FROM employees;

DESCRIBE employees; -- (*****)

-- DESCRIBE는 해당 테이블의 모든 속성과 이름을 출력해 준다.

 

---

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

-- 4. About SYS.DUAL table ( 더미테이블 - 테이블을 사용할 필요가 없을때 사용 )

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

SELECT 245 * 567

FROM dual;

-- FORM 절은 생략이 불가능하기에 더미 테이블을 사용해야 한다.

-- 참고로 MySQL / Mariadb / Postgresql에서는 FROM절이 생략 가능하다.

-- 더미 테이블의 구조를 확인해 보기 ( DESCRIBE = DESC )

DESCRIBE dual;

DESC dual;

SELECT *

FROM dual;

DESC sys.dual;

SELECT *

FROM sys.dual;

---

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

-- 5. 특정 컬럼에 별칭(alias) 정하기 (***)

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

SELECT

    last_name AS 이름,

    salary 월급,

    salary * 12 AS 연봉

FROM employees;

-- AS는 생략이 가능하다.

 

---

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

-- 6. 특정 컬럼에 별칭(alias) 정하기 - 공백 / 특수문자를 포함하는 별칭 (***)

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

SELECT

    last_name AS "사원 이름",

    salary "사원월급",

    salary AS "Salary",

    salary * 12 AS "연 봉"

FROM employees;

-- 대소문자 유지를 하고 싶을 때에도 ""를 붙여야 한다.

-- 기본적으로 결과set에서는 모든 컬럼이 대문자로 출력이 된다.

-- AS는 생략이 가능하다.

---

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

-- 7. NULL값 이해하기

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

-- 분석입장에서는 결측치(Missing Value)라고 한다.

-- 이러한 결측치가 발생하는 경우는

-- 1 ) 실험측정시, 측정이 안되는 경우

-- 2 ) 실험측정장치의 오동작

-- 3 ) 관찰로 얻어지는 경우라면, 관찰이 안된 경우

-- 4 ) 관찰자(사람)의 실수 등 다양하다.

-- + 자바언어에서의 NULL은 객체의 주소가 아직 없다는 의미이다.

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

SELECT

    employee_id 사원번호,

    last_name 이름,

    job_id 직책,

    commission_pct 수수료율

FROM employees;

DESC hr.employees;

-- hr.을 작성하게 되면, hr에 있는 테이블이 보이게 된다.

SELECT

    last_name 이름,

    salary 월급,

    commission_pct 수수료율,

    salary * 12 + commission_pct AS 연봉

FROM employees;

-- 이렇게 작성하면, 수수료가 NULL값인 사원의 경우 계산에 NULL값이 포함되어 연봉이 NULL로 나온다.

-- 수수료가 있는 직원의 경우도 연봉이 잘못 계산되어 나온다.

SELECT

    last_name 이름,

    salary 월급,

    commission_pct 수수료,

    ( salary * 12 ) * ( 1- commission_pct ) AS 연봉

FROM employees;

-- 연봉을 구하기 위해서는 월급 * 12 * 수수료율을 제외한 퍼센트를 해야 한다.

 

---

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

-- 8. NULL값의 기본값 처리 : NVL() 함수 사용하기

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

-- NVL(column, defaultValue)

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

SELECT

    last_name 이름,

    salary 월급,

    commission_pct 수수료율,

    salary * 12 + nvl(commission_pct, 0) AS 연봉

FROM employees;

-- NULL이라면 0으로 표기하라는 의미이다.

 

---

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

-- 9. 연결 연산자 : ||

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

-- SELECT column1 || column2

-- FROM table;

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

-- 1 ) 문자열 + 숫자

SELECT

    last_name || salary AS "이름 월급"

FROM employees;

-- 2 ) 문자열 + 날짜

SELECT

    last_name || hire_date AS "이름 날짜"

FROM employees;

 

---

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

-- 10. 연결 연산자 : ||

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

-- SELECT column1 || literal

-- FROM table;

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

-- 3 ) 문자열 + 리터럴(문자열)

SELECT

    last_name || '사원'

FROM employees;

-- SQL문에서는 문자열 리터럴의 경우에는 ''로 작성한다. (****)

-- 4 ) 복합 연결

SELECT

    last_name || '의 직업은 ' || job_id || '입니다.' AS "사원별 직급"

FROM employees;

-- 5 ) where 절 사용 (**)

SELECT

    last_name || '의 직책은 ' || job_id AS "사원별 직책"

FROM                            -- 실행순서 1

    employees

WHERE                           -- 1차 필터링 ( 실행순서 : 2 )

    last_name = 'Smith';        -- = : 동등비교연산자

 

---

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

-- 11. DISTINCT 중복 제거

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

-- SELECT DISTINCT columns1 [ , columns2, ... , columnsN ]

-- FROM table;

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

-- 직책의 목록 출력하기

SELECT

    job_id

FROM

    employees;

SELECT

    DISTINCT job_id

FROM

    employees;

-- DISTINCT를 사용하자 중복값이 사라졌다.

SELECT

    DISTINCT *

FROM employees;

SELECT

    DISTINCT last_name, first_name

    -- SELECT DISTINCT

FROM employees;

-- 성과 이름이 모두 같아야 중복값으로 인식하게 된다.

 

---

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

-- 12. 비교 연산자

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

-- SQL문에서는 같다라는 의미로 ==이 아니라 =만 작성해야 한다.

-- 같지 않다라는 비교연산자는 !=이 있지만, 동일한 의미로 <>도 있다.

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

 

SELECT

    employee_id,

    last_name,

    job_id,

    salary

FROM

    employees

WHERE

    salary >= 10000;

-- FROM -> WHERE -> SELECT 순으로 실행된다.

SELECT

    employee_id,

    last_name,

    job_id,

    salary

FROM

    employees

WHERE

    last_name = 'King';

-- EMPLOYEES테이블에서 이름이 King이라는 사원의 사원번호, 이름, 직책, 연봉을 출력한다.

SELECT

    employee_id,

    last_name,

    job_id,

    salary

FROM

    employees

WHERE

    last_name = 'KING';

-- 비교하는 문자는 대소문자를 따져가면서 같은지 확인한다.

 

---

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

-- 13. 날짜 출력

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

 

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees;

-- 개발도구에 따라서 날짜형식이 다르게 출력된다.

DESC employees;

 

---

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

-- 14. 자동형변환 (***)

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

-- <NUMBER> <-> <CHARACTER> <-> <DATE>간에는 자동형변환이 된다. (***)

-- 단, DATE와 NUMBER의 경우 중간에 CHARACTER이 사이에 있어야 가능하지, 바로 둘이 자동형변환은 불가능하다.

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

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees

WHERE

    hire_date > '31-DEC-07';

-- '07/12/31' 또는 '31-12-07'에는 각 개발도구의 출력 형태에 맞는 형태로 날짜로 입력해야 비교가 가능하다.

-- '07/12/31'는 디벨로퍼의 형태이고, '31-DEC-07'는 VScode의 형태이다.

 

---

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

-- 15. 강제형변환 (***)

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

-- SQL에서는 강제형변환하는 것이 가독성 확보에 좋기에 사용하는 것이 좋다. (**)

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees

WHERE

    hire_date > to_date('07/12/31', 'RR/MM/DD');

-- to_date는 디벨로퍼의 형태인 07/12/31의 날짜를 RR/MM/DD 형태임을 알려주어, 비교가능한 형태로 강제형변환하여 비교가 가능하게 바꾸라는 의미이다.

-- 주의해야 할 점은 테이블에 있는 날짜의 형태를 강제형변환하는 것이 아니라 문자열인 07/12/31를 DATE로 강제형변환하는 것이다.

-- to_가 붙은 경우에는 강제형변환이 일어날 가능성이 매우 높다. (***)

 

---

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

-- 16. BETWEEN ( A <= x <= B )

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

-- 1 ) 숫자 형태

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees

WHERE

    salary BETWEEN 7000 AND 8000;

-- 2 ) DATE 형태 ( 자동형변환 )

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees

WHERE

    hire_date BETWEEN '01-JAN-07' AND '31-DEC-08';

    -- hire_date BETWEEN to_date('07/01/01','RR/MM/DD') AND to_date('08/12/31','RR/MM/DD');

-- 디벨로퍼 형식으로는 hire_date BETWEEN '07/01/01' AND '08/12/31';로 작성해야 한다.

-- 3 ) DATE 형태 ( 강제형변환 )

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees

WHERE

    hire_date

        BETWEEN to_date('07/12/31','RR/MM/DD')

        AND to_date('08/12/31','RR/MM/DD');

-- SQL문에서는 강제형변환을 사용하는 것이 좋다.

 

---

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

-- 17. IN OPERATORS ( 집합 연산자 ) (*****) - 숫자

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

-- WHERE cloumn IN ( Value1, Value2, ... )

-- column 중에 ( Value1, Value2, ... ) 안에 있는 것과 같은 것이 있다면, 출력한다.

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

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees

WHERE

    employee_id IN ( 100, 200, 300 );

-- employee_id 중에 100이나 200이나, 300 중 하나에라도 해당하면 출력한다.

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees

WHERE

    employee_id IN ( 100, 100, 200, 200, 300 );

-- 집합연산자는 집합이기에 중복되는 값일 경우에는 자동으로 중복값을 무시하게 된다.

 

---

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

-- 18. 논리 연산자 ( AND, OR, NOT ) (*****)

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

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees

WHERE

    employee_id = 100

    OR employee_id = 200

    OR employee_id = 300;

-- SQL에서 AND, OR, NOT은 그대로 AND, OR, NOT으로 작성한다.

-- 이 경우에는 집합 연산자를 사용하는 결과와 같은 결과를 출력하기에 집합 연산자를 사용하는 것이 좋다.

 

---

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

-- 19. IN OPERATORS ( 집합 연산자 ) - 문자열

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

SELECT

    employee_id,

    first_name,

    last_name,

    job_id,

    salary,

    hire_date

FROM

    employees

WHERE

    last_name IN ('King', 'Abel', 'Jones');

 

---

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

-- 20. IN OPERATORS ( 집합 연산자 ) (*****) - 날짜

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

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees

WHERE

    hire_date IN (

        to_date('01/01/13', 'RR/MM/DD'),

        to_date('07/02/07','RR/MM/DD'));

-- 디벨로퍼 형태이기에 비교하기 위해서는 강제형변환이 필요하다.

 

---

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

-- 21. LIKE ( 패턴매칭 연산자 ) (*****)

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

-- WHERE column LIKE <패턴>

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

-- <패턴>에 사용가능한 wildcard 문자들 : (***)

-- (1) % ( 0개 이상 )

-- (2) _ ( 1개 )

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

-- 1 ) % ( 문자의 개수를 제한하지 않는다. )

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    last_name LIKE 'J%';

-- J로 시작하는 칼럼을 모두 출력한다. (***)

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    last_name LIKE '%ai%';

-- 이름에 ai가 포함된 사원의 경우 모두 출력한다.

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    last_name LIKE '%in';

-- 이름의 끝이 in으로 끝나는 모든 사원을 출력한다.

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    last_name LIKE '%d';

-- 직원의 이름 중 d로 끝나는 직원을 모두 출력한다.

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    last_name LIKE '%';

-- 이름이 무엇이 되었든 작성되어있는 모든 직원을 출력한다.

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    last_name LIKE '%%';

-- 직원 중 이름이 최소 0개 이상인 모든 직원을 출력한다.

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

-- 2 ) _ ( 문자의 개수를 한정짓는다. )

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    last_name LIKE '_b%';

-- 직원 중에 이름의 2번째 문자가 b인 직원을 모두 출력한다.

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    last_name LIKE '_____d';

-- 직원의 이름이 6글자이고 d로 끝나는 모든 직원을 출력한다.

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    last_name LIKE '%_%';

-- 이름의 길이가 최소 1문자 이상인 직원을 모두 출력한다.

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    last_name LIKE '_';

-- 이름이 1문자로 작성되어 있는 모든 직원을 출력한다.

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

-- 3 ) ESCAPE (****)

-- 탈출문자(Escape Character) : (***)

-- 특수한 의미를 가지는 기호의 기능을 없애는 문자로

-- ESCAPE를 '탈출문자'라고 한다.

-- 탈출문자는 $가 아니어도 가능하다.

SELECT

    employee_id,

    last_name,

    salary,

    job_id

FROM

    employees

WHERE

    job_id LIKE '%$_%' ESCAPE '$';

-- 이때는 탈출문자 뒤의 '_'가 기능을 잃었기에 평범한 문자_로 인식한다.

-- 그렇게 되어서 job_id 안에 _가 포함되어 있는 모든 직원들을 출력하게 된다.