[SQL]Structured Query Language : Syntax 정리

다시 정리해보는 SQL 문법

database에서 데이터 조회 / 처리

SELECT * FROM hrdb2024.employee;

 

hrdb : human resource 관련 데이터베이스

7개의 테이블로 이루어져 있음

1. employee : 직원 정보 (id, 이름, 나이, 성별, 연봉 등)
2. vacation : 휴가 정보 (직원 id, 시작 날짜, 종료 날짜, 이유, 기간 등)
3. toeic : 직원 별 토익 점수 (직원 id, 점수)
4. department : 부서 정보 (부서 id, 부서 이름, 설립일 등)
5. club : 동호회 (동호회 id, 종류 등)
6. club join : 동호회 가입 정보 (직원 id, 동호회 id 등)
7. unit 

 

마스터 테이블 : 변경이 잘 되지 않음

• (상품, 고객 정보)  여기서는 employee table
• 주문 테이블의 경우 => 변경이 계속 되기 때문에 마스터 테이블이 아님

 

주석달기

/*
practicing annotation
*/

-- can do this too
SELECT * FROM employee; -- can also put annot. here but be cautious!

 

query 에서의 quotation 은 'single quotation' 을 사용함

SELECT 'Hello SQL World' AS Start;

 

1. database 연결 및 확인

- oracle의 경우 db가 하나여서 연결 필요 없음

- mysql mssql의 경우는 여러개의 db가 있기 때문에 db 연결

USE hrdb2024;
SELECT DATABASE();

 

2. 원하는 열을 원하는 순서로 나열 가능 (SELECT 시 정함)

-- 원하는 순서로
SELECT emp_id, emp_name, 
	FROM employee;

SELECT emp_name, gender, emp_id, dept_id 
	FROM employee;

SELECT emp_name, emp_id, gener, dept_id, phone, salary 
	FROM employee;

 

 

3. 조회 조건 지정 WHERE 사용 

1) 모든 열 선택

SELECT * -- 모든 열 선택
	FROM employee  -- employee 테이블에서
	WHERE emp_id = 'S0001';  -- 조건 : emp_id 가 S0001인 것만

 

2) 특정 열만 선택

SELECT emp_name, emp_id, dept_id, gender, hire_date, salary
	FROM employee
	WHERE salary > 7000;

 

3) 조건문 날짜  -> 문자열로

SELECT emp_name, emp_id, dept_id, gender, hire_date, email
		FROM employee
        WHERE hire_date < '2020-06-01';

 

 

4) LIKE 

-- 김씨인 직원 찾기
SELECT * 
    FROM employee
    WHERE emp_name LIKE '김%';
    
-- 김씨가 아닌 직원 찾기    
SELECT * 
    FROM employee
    WHERE emp_name NOT LIKE '김%';
    
-- 이름에 국 이 들어간 직원 찾기    
SELECT * 
    FROM employee
    WHERE emp_name LIKE '%국%'; -- 이름 어디던 '국'자가 들어가는 직원
    
SELECT * 
    FROM employee
    WHERE emp_name LIKE '%국'; -- 이름이 ~ 국 으로 끝나는 직원

 

4) AND / OR 로 다중 조건

-- 기간검색
 SELECT * 
    FROM employee
    WHERE (hire_date >= '2020-01-01') AND (hire_date <= '2020-12-31');
    
    
 -- 다양한 조건
 SELECT *
	FROM employee
	WHERE gender = 'M' AND dept_id = 'SYS'; -- 남자이면서 부서가 system인 직원
 
 
  SELECT *
	FROM employee
	WHERE dept_id = 'MKT' OR dept_id = 'SYS'; -- 마케팅 부서이거나 시스템 부서인 직원

 

5) BETWEEN 

-- 연봉이 5000 과 8000 사이인 직원
SELECT *
    FROM employee
    WHERE salary BETWEEN 5000 AND 8000;

-- 연봉이 5000 과 8000 사이가 아닌 직원
SELECT *
    FROM employee
    WHERE salary NOT BETWEEN 5000 AND 8000;

 

6) IN (a, b, c) : 1 대 다수 

-- 부서가 sys, mrk, gen 인 직원
SELECT *
    FROM employee
    WHERE dept_in IN ('SYS', 'MKT', 'GEN');

-- 부서가 sys, mrk, gen이 아닌 직원
SELECT *
    FROM employee
    WHERE dept_in NOT IN ('SYS', 'MKT', 'GEN');

 

 

▶ 다양하게 적용시켜보기

-- 직원 id S0001, S0003, S0005의 2022년 휴가 정보 조회
SELECT *
	FROM vacation
	WHERE emp_id IN ('S0001', 'S0003', 'S0005') 
        AND begin_date BETWEEN '2022-01-01' AND '2022-12-31';


-- 시스템 혹은 마케팅 부서 직원 중 2022년도에 입사한 직원 정보 조회
SELECT *
	FROM employee
	WHERE dept_id IN ('SYS' , 'MRK')
        AND hire_date BETWEEN '2022-01-01' AND '2022-12-31';

 

 

 

4.  NULL 값 

1) IS NULL / IS NOT NULL : null 이 아닌 값 / null 인 값 조회 

SELECT *
	FROM employee
    WHERE retire_date IS NOT NULL;


-- 영어 이름이 없는 근무중인 직원 정보
SELECT *
	FROM employee
    WHERE eng_name IS NULL AND retire_date IS NULL;

 

2) IFNULL

SELECT emp_id, emp_name,
        IFNULL(eng_name, ''), gender, dept_id, hire_date, phone --영어이름이 null이면 공백
        FROM employee
        WHERE retire_date IS NULL;

        
-- 별칭 사용 : eng_name 이라는 칼럼 이름으로 출력됨
SELECT emp_name, emp_id, 
	IFNULL(eng_name, '') AS eng_name, gender, dept_id, hire_date
	FROM employee
	WHERE retire_date IS NULL;

 

3) COALESCE (a,b,c,d)

SELECT emp_name, emp_id, COALESCE(eng_name, '') AS eng_name, gender, dept_id, hire_date
	FROM employee
	WHERE retire_date IS NULL;

 

coalesce 와 ifnull 의 차이점은?

https://stackoverflow.com/questions/18528468/what-is-the-difference-between-ifnull-and-coalesce-in-mysql

What is the difference between IFNULL and COALESCE in MySQL?

SELECT IFNULL('some value', 'some other value');
-> returns 'some value'

SELECT IFNULL(NULL,'some other value');
-> returns 'some other value'

SELECT COALESCE(NULL, 'some other value');
-> returns 'some other value' - equivalent of the IFNULL function

SELECT COALESCE(NULL, 'some value', 'some other value');
-> returns 'some value'

SELECT COALESCE(NULL, NULL, NULL, NULL, 'first non-null value');
-> returns 'first non-null value'

 

 

연습

SELECT emp_name, emp_id, gender, dept_id, retire_date, hire_date, coalesce(salary, 0) AS salary
	FROM employee
    WHERE gender = 'F' AND retire_date IS NULL;

 

 

5. 합치기 

1) CONCAT : 문자열 결합 

SELECT CONCAT('10', '20'); -- 1020
SELECT CONCAT(10, '20'); -- 1020
SELECT CONCAT(10, 20); -- 1020
SELECT CONCAT(10, NULL); -- NULL

 

→ employee name (employee id) 형식으로 emp_name 칼럼을 출력 : CONCAT( ) 으로 가능

SELECT CONCAT(emp_name. '(', emp_id, ')') AS emp_name, 
	dept_id, gender, hire_date, email
    FROM empolyee
    WHERE retire_date IS NULL;

 

2) CONCAT_WS -> seperator 로 여러 값을 concat

-- concat with seperator **
SELECT emp_id, CONCAT_WS(',',score01, score02, score03, score04, score05) AS score
	FROM toeic;
    
 /*
   emp_id  score
   s0001   940,950,930,980
   s0002   870,880,860,890
   */

 

 

6. CASE WHEN THEN

SELECT customer_name AS name, 
	customer_id AS id, 
    CASE WHEN gender='M' THEN 1
		 WHEN gender='F' THEN 0
		 ELSE NULL END,
    phone, email, city, register_date,
	COALESCE(point, 10000) AS point
	FROM customer
	WHERE city in ('서울', '부산') AND register_date BETWEEN '2022-01-01' AND '2022-12-31'
    ORDER BY point DESC;

 

 

7. IF

SELECT customer_name AS name, 
	customer_id AS id, 
    IF ('gender'='M',  '남자', '여자') AS gender,
    phone, email, city, register_date,
	COALESCE(point, 10000) AS point
	FROM customer
	WHERE city in ('서울', '부산') AND register_date BETWEEN '2022-01-01' AND '2022-12-31'
    ORDER BY point DESC;
    
    
  SELECT customer_name AS name, 
	customer_id AS id, 
    IF(gender = 'M', '남자', IF(gender = 'F', '여자', '')) AS gender,
    phone, email, city, register_date,
	COALESCE(point, 10000) AS point
	FROM customer
	WHERE city in ('서울', '부산') AND register_date BETWEEN '2022-01-01' AND '2022-12-31'
    ORDER BY point DESC;

 

 

--  부서 코드가 'SYS'이면 전화번호를 공백으로 해서 근무 중인 직원 조회하기

SELECT emp_name, emp_id, dept_id, gender,
    CASE WHEN dept_id = 'sys' THEN ''
    	ELSE phone END
    FROM employee
    WHEN retire_date IS NULL


-- 급여 크기를 상, 중, 하로 구분해서 근무 중인 직원 조회
SELECT emp_name, emp_id, dept_id, gender, phone, hire_date,
    CASE WHEN salary >= 8000 THEN '상'
    	 WHEN salary >= 7000 THEN '중'
         ELSE '하' END AS salary
    FROM employee
    
-- 근무 상태를 재직, 퇴사로
SELECT emp_name, emp_id, dept_id, gender, phone, hire_date,
    IF (retire_date = IS NULL, '재직', '퇴사') AS status
    FROM employee