1. SQL 연습
-- SQL에서 주석은 이렇게 씁니다.
- 설명
- SQL에서 주석은
-- 를 앞에 붙여서 작성한다.
select lec_id, tutor_id ,lec_name, CONCAT(DATE_FORMAT(c_st, '%Y.%m.%d'),'~', DATE_FORMAT(c_end, '%Y.%m.%d')) as cdate
from tb_lec_info;
- 설명
CONCAT(column, ...) - 이어 붙이기 가능DATE_FORMAT(column, 'format 양식') - 날짜 데이터 변환
select li.lec_id
, li.tutor_id
, ui.name
, li.lec_name
, li.tutor_name
, CONCAT(DATE_FORMAT(li.c_st, '%Y.%m.%d'),'~', DATE_FORMAT(li.c_end, '%Y.%m.%d')) as cdate
from tb_lec_info li
inner join tb_userinfo ui on ui.loginID = li.tutor_id and ui.user_type = 'B';
- 설명
INNER JOIN 할 때 AND 연산자를 활용해서 테이블 데이터 수를 줄일 수 있다.
select li.lec_id
, li.tutor_id
, ui.name
, li.lec_name
, li.tutor_name
, li.pre_pnum
, (select count(*) from tb_lecstd_info ls where ls.lec_id = li.lec_id ) as cnt
, CONCAT(DATE_FORMAT(li.c_st, '%Y.%m.%d'),'~', DATE_FORMAT(li.c_end, '%Y.%m.%d')) as cdate
from tb_lec_info li
inner join tb_userinfo ui on ui.loginID = li.tutor_id and ui.user_type = 'B';
- 설명
- Subquery를 활용해서 다른 테이블의 조건에 맞는 개수를 바로 가져와서 사용할 수 있다.
select ui.name, ui.loginID, ui.tel, ui.mail, ifnull(em.wp_state,'미취업') as wp_state
from tb_lecstd_info li
inner join tb_userinfo ui on ui.loginID = li.std_id
left outer join tb_employ em on li.std_id = em.std_id
where li.lec_id = '1007';
- 설명
IFNULL(column, 'value') - IFNULL 함수를 쓰면 해당 column의 데이터 값이 null 일 때, 어떤 값 으로 바꾸도록 할 수 있다.
-- 학생명 연락처 입사일 퇴사일 업체명 재직여부
select ui.name
, ui.tel
, em.employ_day
, em.resign_day
, em.comp_name
, ifnull(em.resign_day,'Y') as emp_type
from tb_employ em
inner join tb_userinfo ui on ui.loginID = em.std_id;
-- 학번 학생명 연락처 강의명 가입일자
select ui.std_num
,ui.name
,ui.tel
,ll.lec_name
,ui.join_date
from tb_userinfo ui
left outer join (
select li.lec_id
,li.lec_name
,le.std_id
from tb_lecstd_info le
inner join tb_lec_info li on li.lec_id = le.lec_id
) ll on ui.loginID = ll.std_id
where ui.user_type = 'A'
and ui.loginID not in (
select em.std_id
from tb_employ em
)
- 설명
JOIN 절에 subquery를 한 번 사용하였고, WHERE 절에서도 subquery를 사용한 예- JOIN subquery 부분 :
tb_userinfo 테이블에 없는 강의명 을 가져오려면 학생-강의 테이블과 강의정보 테이블 두 개의 테이블을 JOIN 해야 하는데 이를 피하고자 두 테이블을 미리 JOIN 한 subquery를 사용하였다.
- WHERE subquery 부분 : 학생들 중에서 취업한 학생들을 제외하기 위해서
tb_employ 테이블에 있는 학생 아이디, em.std_id 로 이루어진 테이블을 subquery로 만든 후에 여기에 id가 속하지 않는 loginID 를 걸러내도록 만들었다.
-- 강의실 명 크기 자리수 비고
select lm.lecrm_name
,lm.lecrm_size
,lm.lecrm_snum
,lm.lecrm_note
,lm.lecrm_id
from tb_lecrm lm;
-- 번호 장비명 갯수 비고
select eq.equ_id
,eq.equ_name
,eq.equ_num
,eq.equ_note
from tb_equ eq
where eq.lecrm_id = '1';
- 설명
SELECT에서 지정한 Alias를 그다음 SELECT 명령어에서 사용할 수 없다. (예를 들어, SELECT 문에서 정의한 두 개의 Alias 변수 명의 차이를 못 씀)- 작성한 Alias 변수 이름을 같은 레벨단에서는 사용할 수 없지만, 밖에 있는 다른 레벨의 명령어에서는 사용 가능
-- 강의명 시험명 시험기간 상태 대상자 수 응시자 수 미응시자 수
select li.lec_id
,li.lec_name
,te.test_name
,concat(DATE_FORMAT(te.test_start, '%Y.%m.%d'),'-',DATE_FORMAT(te.test_end, '%Y.%m.%d')) as dduedate
,case when te.test_end < now() then '시험종료'
else '시험중'
end state
,ifnull(llc.cnt,0) as cnt
,ifnull(llt.cnt,0) as lltcnt
,(ifnull(llc.cnt,0) - ifnull(llt.cnt,0)) as nocnt
-- ,(cnt - lltcnt) as nocnt
from tb_test te
inner join tb_lec_info li on te.lec_id = li.lec_id
left outer join (
select lc.lec_id, count(*) as cnt
from tb_lecstd_info lc
group by lc.lec_id
) llc on li.lec_id = llc.lec_id
left outer join (
select tu.test_id, count(*) as cnt
from tb_test_user tu
group by tu.test_id
) llt on te.test_id = llt.test_id
- 설명
AS 로 새로 설정한 column 이름은 같은 레벨(예를 들어 select 내부)에서 사용할 수 없다.- 즉,
cnt 와 lltcnt 의 차이를 구하고 싶다고 cnt - lltcnt 로 작성할 수 없다는 말이다.
2. SQL BASIC QUERY
SELECT - 선택한 COLUMNFROM - 특정 TABLEWHERE - 조건 에 해당하는ORDER BY - COLUMN을 기준으로 정렬AND - 동시에 조건을 만족OR - 여러 조건들 중 적어도 하나를 만족IN - OR를 여러번 사용해야 할 때 쓰면 유용LIKE - WHERE 절에서 패턴을 찾을 때DISTINCT - 중복 제거AS - COLUMN의 별칭 지정IS NULL - NULL 값 찾기COUNT - 행의 갯수SUM - 행데이터의 합AVG - 행데이터의 평균MIN - 최소값 구하기MAX - 최대값 구하기GROUP BY - 특정 COLUMN을 기준으로 결과를 그룹화해서 보고 싶을 때 사용, 자동 정렬됨.HAVING - GROUP BY 와 함께 조건을 지정해줄 때CASE - 특정한 조건에 따라 값을 지정해줄 때
- 예시 1 : Customer 테이블에서 active 칼럼에 속한 데이터의 값에 따라 customer_id, first_name, last_name의 활성/비활성 여부를 나누어 표시하기 (active 값이 1일 경우 active, 0일 경우 inactive, Status로 별칭) 출처
SELECT customer_id, first_name, last_name, active,
CASE
WHEN active=1 THEN 'active'
ELSE 'inactive'
END AS Status
FROM customer;- 예시 2 : Film 테이블에서 대여 기간(rental_duration)에 속한 데이터의 값에 따라 title의 길이를 나누어 표시하기 (rental_duration이 3 이하일 경우 ‘SHORT’, 4 이상일 경우 ‘MEDIUM’, 6 이상일 경우 'LONG', Duration으로 별칭) 출처JOIN
SELECT title, rental_duration,
CASE
WHEN rental_duration <= 3 THEN 'SHORT'
WHEN rental_duration > 3 AND rental_duration < 6 THEN 'MEDIUM'
WHEN rental_duration >= 6 THEN 'LONG'
ELSE NULL
END AS 'Duration'
FROM film;
INNER JOIN - SELECT ~ FROM ~ INNER JOIN ~ ON => 공통된 칼럼의 데이터를 기준으로 테이블 합치기
- 어느 한쪽에라도
NULL값이 있는 경우 해당 ROW 는 사라진다.
LEFT/RIGHT OUTER JOIN - SELECT ~ FROM ~ LEFT/RIGHT OUTER JOIN ~ ON => 결합되는 기준이 되는 테이블을 선택해서 결합하기
INNER JOIN과는 다르게 어느 한쪽의 데이터가 NULL이라도 지정한 LEFT OR RIGHT에 따라 데이터가 사라지지 않고 남아 있을 수 있다.
- SUBQUERY -
IN,FROM, JOIN을 활용해서 쿼리 안에 또 다른 쿼리를 사용해서 데이터를 한 번에 확인하기
- 예시 1 : 9.99 달러짜리 영화를 빌린 이력이 있는 손님의 번호, 이름, 이메일 목록과 활성 여부를 뽑아보자 (서브 쿼리 사용-WHERE/IN) 출처
SELECT customer_id, first_name, last_name, active, email
FROM customer
WHERE customer_id IN (
SELECT customer_id
FROM payment
WHERE amount=9.99
)
AND active=1;- 예시 2 : 1번 가게에서 2.99 달러 미만의 영화를 빌린 고객 중에서 총 5달러 이상 빌린 고객의 리스트를 구해보자 (서브 쿼리 사용-FROM) 출처
SELECT customer_id, SUM(amount)
FROM (
SELECT payment_id, customer_id, amount
FROM payment
WHERE staff_id=1
) sub
WHERE sub.amount < 2.99
GROUP BY sub.customer_id
HAVING SUM(sub.amount) >= 5;- 예시 3 : customer, payment 테이블을 활용해서 고객 목록 중에 대여 금액이 가장 높은 10명의 고객 번호, 이름, 총 대여 금액을 뽑아보자! (서브 쿼리를 활용해서 JOIN하기) 출처날짜형 함수
SELECT a.customer_id, a.first_name, a.last_name, b.total
FROM customer a
INNER JOIN (
SELECT custoer_id, SUM(amount) as total
FROM payment
GROUP BY customer_id
ORDER BY total desc
LIMIT 10
) b ON a.customer_id=b.customer_id;
DATE_FORMATDATEDIFF
- 예시 1 : 영화 대여 기간을 확인하기 위해 사용 가능, 반납일자 - 대여 일자 = 대여 기간
SELECT rental_id,
customer_id,
rental_date,
return_date,
DATEDIFF(retun_date, rental_date) AS rental_duration
FROM rental;
문자형 함수
CONCATLENGTHSUBSTRLEFT(column, number) - ex) LEFT('Hello', 1) = 'H', LEFT('Hello', 3) = 'Hel'RIGHT(column, number) - ex) RIGHT('Hello', 1) = 'o', RIGHT('Hello', 3) = 'llo'- 숫자형 함수
TRUNCATE(SUM(column), 0) - 소수점 아래 절삭ROUND(AVG(column), n) - 소수점 n째 자리에서 반올림MOD(column, n) - n으로 나눈 후 나머지를 반환- 정규식 표현법
- 정규식 함수 종류에 따라 사용할 수 있는 함수가 정해져 있다.