데이터베이스 SQL 기본 질의문, 연산자, 함수 및 단일행 함수

 

 

SQL

USE sakila;
select actor_id, last_update from actor 
limit 10;

 

limit 절

• limit 보여줄 데이터 수(행)
• limit는 변환되는 데이터의 개수를 지정할 때 사용
• select문의 가장 마지막에 추가

 

산술연산자

select 5 + 3 as 덧셈, 20 - 3 as 뺄셈, 14 * 3 as 곱셈, 50 / 5 as 나눗셈;

 

-> 하나의 행으로 출력됨

 

 

논리연산자

select actor_id as aid, last_update as l_d
from actor 
where not first_name = 'ED' or last_name = 'CHASE'
order by aid;

 

WHERE NOT : 특정 조건을 만족하지 않는 행을 선택

 

 

집합연산자

UNION : 집합 연산자 중에 합집합에 대한 연산자만 제

select actor_id, last_update 
from actor 
where last_update  > '2006-02-15'
union
select actor_id, last_update 
from actor 
where first_name = 'ZERO' and last_name = 'CAGE'
order by actor_id;

 

'2006-02-15' 날짜도 WHERE 조건에 들어갈 수 있음

 

---

 

IN 연산자

SELECT * FROM sakila.finance_data
where Mutual_Funds in (1,2);

 

 

BETWEEN 연산자

SELECT * FROM sakila.finance_data
where age between 20 and 30;

 

 

LIKE 연산자

SELECT gender, age FROM sakila.finance_data
where gender like 'F%';

 

-> % , _ : 와일드카드 문자

( _ : 한글자 , % : 글자수 상관없)

---

 

Fixed_Deposits 이 5와 10사이인 성별과 나이를 조회

 

SELECT gender, age FROM sakila.finance_data
where Fixed_Deposits between 5 and 10;

 

---

 

SQL 함수

: SQL 함수는 SELECT 뒤에 바로 사용

 

단일행 함수의 종류

- 문자열 함수

CHAR_LENGTH(문자열) : 문자의 개수

LENGTH(문자열) : 문자열에 할당된 바이트 수

CONCAT('문자열1', '문자열2') : 문자열을 연결

CONCAT_WS('-(구분자)', '2024', '08', '25') : 구분자와 함께 문자열 연결

 

LEFT(문자열, 길이) : 문자열의 왼쪽부터 길이만큼 문자열 반환

SUBSTR(문자열, 시작위치, 길이) : 지정한 위치로부터 길이만큼의 문자열 반환

SUBSTRING_INDEX(문자열, 구분자, 인덱스) : 지정한 구분자를 기준으로 문자열을 분리해서 가져

LPAD(문자열, 길이, 변환할 문자열) : 지정한 길이에서 문자열을 제외한 빈칸을 특정 문자로 채울 때 사용

-> LPAD 는 왼쪽에 RPAD는 오른쪽에 특정 문자열을 채움

 

LTRIM() : 왼쪽의 공백을 제거할 떄 사용

RTRIM() : 오른쪽 공백을 제거할 때 사용

---

 

숫자형 함수

ceiling(), floor(), round(값, 자릿수), truncate(값, 자릿수)

USE sakila;
# ceiling 올림
select ceiling(age) as ceil_age
from finance_data;

# floor 버림
select floor(age) as floor_age
from finance_data;

# round 지정한 위치 반올림
select round(age, 2) as round_age
from finanace_data;

# truncate 지정한 위치 버림
select truncate(age, 2) as round_age
from finance_data;

 

데이터 사용 예시

# 데이터 사용
select ceiling(123.567), 
floor(123.567), round(123.567),
 round(123.567, 2), truncate(123.567, 2);

 

---

 

mod 연산

: %, 피연산자 mod 피연산

select mod(301, 4), 301 % 4, 301 mod 4;

 

 

age가 홀수인지 짝수인지 나머지값 연산으로 출력

select mod(age, 2) as mod_age from finance_data;

 

---

 

power()

: n제곱승을 값을 반환하는 함수 (거듭제곱)

select power(age, 2.5) as pow_age
from finance_data;

 

 

sqrt() 

: 제곱근 값을 반환하는 함

select sqrt(age) as pow_age
from finance_data;

 

rand()

: 0과 1사이 임의이 실수값을 반환하는 함

 

select rand() as rand_value
from finance_data;

 

---

문제 예제

문제1 : age의 2.5제곱을 계산하고 소수점 둘째 자리까지 반올림하여 rounded_power_age로 출력하시오.

select round(power(age, 2.5), 2) as rud_pow_age
from finance_data;

 

문제2 : Mutual_Funds의 제곱근이 3 이상인 레코드만 조회하여 sqrt_mutual_funds로 출력하시오.

select sqrt(age) as sqrt_mutual_funds from finance_data
where sqrt(age) >= 3;

 

문제3 : Debentures의 네제곱을 계산한 후, 그 값의 제곱근을 구하여 sqrt_power_debentures로 출력하시오.

select sqrt(power(Debentures, 4)) as sqrt_power_debentures
from finance_data;

 

문제4 : 레코드를 임의의 순서로 정렬하여 random_order로 출력하시오.

select * from finance_data
order by RAND();

 

---

 

현재 날짜/ 시간 반환 함수

현재 날짜 / 시간 반환 함수

 

NOW() : 현재 서버 시간 확인

 

• NOW()는 쿼리가 실행될 때의 날짜와 시간을 반환
• 쿼리 실행 시간에 고정되어 있고, 동일 쿼리 내에서 여러 번 호출되더라도 동일한 값을 반환

 

select now() as now_time;

 

 

현재 날짜와 시간을 inv_now라는 별칭으로 표시

select Investment_Avenues, NOW() as inv_now
from finance_data;

 

 

SYSDATE()

 

• SYSDATE()는 함수가 호출될 때의 시스템 날짜와 시간을 반환
• 쿼리 실행 시간보다는 함수 호출 시의 정확한 시간을 반환하며,
• 동일 쿼리 내에서 여러 번 호출되면 서로 다른 값을 반환할 수 있음

select sysdate() as sysdate;

 

 

 

CURDATE() : 시스템의 현재 날짜를 반환

CURTIME() : 시스템의 현재 시간을 반환

select curdate() as curdate, curtime() as curtime;

 

date_format() : 

날짜 및 시간 값을 지정된 형식으로 반환

• %Y: 4자리 연도 (예: 2024) %y: 2자리 연도 (예: 24)
  %m: 2자리 월 (01-12) %c: 월 (1-12)
  %d: 2자리 일 (01-31) %e: 일 (1-31)
  %H: 2자리 시 (00-23, 24시간 형식) %h: 2자리 시 (01-12, 12시간 형식)
  %i: 2자리 분 (00-59) %s: 2자리 초 (00-59) %p: AM 또는 PM
  %a: 요일의 축약형 이름 (예: Sun, Mon) %W: 요일의 전체 이름 (예: Sunday, Monday)

select date_format(now(), '%Y-%m-%d %H-%i-%s') as col1;

 

---

 

연도, 분기, 월, 일, 시, 분, 초 반환 함수

YEAR(), QUARTER(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()

 

 

문제1 : last_update에서 연도를 추출하여 actor_id와 함께 출력

select YEAR(last_update) as '연도', actor_id  from actor;

 

 

---

 

기간 반환 함수

 

DATEDIFF() : 기간을 일자 기준으로 반환

TIMESTAMPDIFF() : 기간을 지정한 단위 기준으로 보여줌

 

현재 날짜(NOW())와 last_update 열의 날짜 사이의 일수를 계산

select datediff(now(), last_update) as day_sin_upd from actor;

 

 

actor 테이블에서 현재 시간, 마지막 업데이트 시간, 그리고 현재 시간과 마지막 업데이트 시간 사이의 일수를 조회

select now() as time, last_update, datediff(now(), last_update) as day_sin_pid
from actor;

 

 

actor 테이블에서 각 레코드의 last_update 열과

현재 시간 (NOW()) 간의 차이를 개월 단위로 계산하여

mon_sin_up라는 열에 반환

select timestampdiff(MONTH, last_update, now()) as mon_sin_up from actor;

 

 

---

 

문제 예제

문제1: last_update와 현재 날짜 사이의 일 수가 30일 이상인 레코드만 조회하여 days_since_update로 반환하시오.

select datediff(now(), last_update) as days_since_update from actor
where datediff(now(), last_update) >= 30;

 

문제2:현재 시간과 last_update 사이의 달 수가 6개월 이상인 레코드만 조회하여

investment_duration_months로 반환하시오.

select timestampdiff(MONTH, last_update, now()) as mon_sin_up from actor
where timestampdiff(MONTH, last_update, now()) >=6;

-> TIMESTAMPDIFF 함수를 사용하여 달 수 차이를 계산

 

문제 3 : last_update와 현재 날짜 사이의 분 수를 계산하여 minutes_since_last_update으로 반환하시오.

select timestampdiff(MINUTE, last_update, NOW()) as minutes_since_last_update from actor;

 

---

 

ADDDATE() : 지정한 날짜를 기준으로 그 기간만큼 더한 날짜를 반환하는 함수

SUBDATE() :기간만큼 뺀 날짜를 반환함

 

select adddate(now(), -30),  adddate(now(), interval 50 day);

 

select subdate(now(), interval 50 month);

 

 

문제1 : last_update에 30일을 더한 날짜가 현재 날짜보다 이전인 레코드만 조회하여 new_update_date로 반환하시오.

select adddate(last_update, interval 30 day) as new_update_date;

 

 

문제2 : last_update에 45일을 더한 날짜와

last_update에서 1년을 뺀 날짜를

각각 new_update_date와 earlier_update_date로 반환하고,

두 날짜를 'YYYY-MM-DD' 형식으로

formatted_new_update_date와 formatted_earlier_update_date로 출력하시오.

select 
adddate(last_update, INTERVAL 45 DAY) as new_update_date,
subdate(last_update, INTERVAL 1 YEAR) as earlier_update_date,
date_format(adddate(last_update, INTERVAL 45 DAY), '%Y-%m-%d') as formatted_new_update_date,
date_format(subdate(last_update, INTERVAL 1 YEAR), '%Y-%m-%d') as formatted_earlier_update_date
FROM actor;

---

 

기타 날짜 반환 함수

last_day() : 해당 월의 마지막 일자 반환

dayofyear() : 현재 연도에서 며칠이 지났는지 반환

monthname() : 월을 영문으로 weekday()는 요일을 정수로

 

select last_day(now()), dayofyear(now()), monthname(now()), weekday(now());

 

해당 월의 마지막 날짜

 

 

문제 1 : last_update가 속한 주의 요일을 weekday_update로 반환하시오.

SELECT LAST_DAY(last_update) AS last_day_update, 
DAYOFYEAR(last_update) AS day_of_year_update, 
MONTHNAME(last_update) AS month_name_update
FROM actor;

문제 2. last_update의 달의 마지막 날이 해당 연도의 365일 중 300일 이후인 레코드만 조회하여 last_day_update와 day_of_year_update로 반환하시오.

SELECT LAST_DAY(last_update) AS last_day_update,
DAYOFYEAR(LAST_DAY(last_update)) AS day_of_year_update
FROM actor
WHERE DAYOFYEAR(LAST_DAY(last_update)) > 300;

 

 

문제 3. last_update가 속한 요일이 월요일(0)이면서 해당 달의 이름이 'January'인 레코드만 조회하여 weekday_evaluation과 month_name_evaluation로 반환하시오.

SELECT WEEKDAY(last_update) AS weekday_last_update, 
MONTHNAME(last_update) AS month_name_last_update
FROM actor
WHERE WEEKDAY(last_update) = 0 AND MONTHNAME(last_update) = 'January';

 

 

문제 4. last_update의 달의 이름이 'March'이면서 해당 연도의 100일 이전인 레코드만 조회하여 month_name_equity_evaluation과 day_of_year_equity_evaluation로 반환하시오.

SELECT MONTHNAME(payment_date) AS month_name_equity_evaluation,
 DAYOFYEAR(payment_date) AS day_of_year_equity_evaluation
FROM payment
WHERE MONTHNAME(payment_date) = 'August' AND DAYOFYEAR(payment_date) < 5000;

 

 

---

 

형 변환 함수

 

CAST() 함수

: 데이터 형식을 변환하는 데 사용되는 함수

주어진 값이나 열을 다른 데이터 타입으로 변환 가능

select cast(age as char) as age_as_string
from finance_data;

 

 

signed, unsigned

: 숫자 데이터 타입의 속성을 정의

• signed : 부호비트가 포함되어 있어서 부호가 있는 자료형
• unsigned : 부호비트가 없어서 부호가 없는 자료형이에요 그래서 양수만 표현 가능!

-> unsigned에서 더 큰 범위를 표현할 수 있다는게

signed의 부호비트를 unsigned는 다 숫자 표현하는데 쓰니까 수 표현 범위가 커지는 것

 

 

CONVERT() 함수

: MySQL에서 데이터 타입을 변환할 때 사용

예를 들어 숫자를 문자열로 변환하거나, 날짜를 다른 형식으로 변환하는 경우에 주로 사용

select convert(age, char) as age_as_string
from finance_data;

 

---

 

제어흐름함수

제어문

IF(조건, 수식1, 수식2)

select age, If(age >= 30, '30대 이상', '30대 미만') as col1
from finance_data;

 

 

문제1. age가 40 이상인 경우 'Very High', 30 이상 40 미만인 경우 'High', 

그렇지 않으면 'Low'를 반환하여 funds_level로 출력하시오.

select If(age >= 40, 'Very High', IF(age >= 30, 'High', 'Low')) as funds_level
from finance_data;

 

 

문제2. payment_date가 2005년 이후인 경우 'Recent', 

그렇지 않으면 'Old'를 반환하여 update_status로 출력하시오.

select payment_date, If(payment_date >= 2005, 'Recent', 'Old') as update_status
from payment;

 

 

IFNULL() 

:  첫 번째 매개변수로 전달된 값이 NULL인 경우 두 번째 매개변수로 전달된 값을 반환

select ifnull(age, 0) as age_isnull from finance_data;

-> IFNULL(age, 0)은 age 컬럼의 값이 NULL이면 0을 반환하고, 그렇지 않으면 age 컬럼의 값을 그대로 반환

 

 

NULLIF()

: 첫 번째 매개변수와 두 번째 매개변수가 같으면 NULL을 반환하고, 다르면 첫 번째 매개변수 값을 반환

select nullif(age, 34) as age_isnull;

-> finance_data 테이블의 각 행에서 age 값이 34와 같으면 NULL을 출력하고,

그렇지 않으면 해당 age 값을 출력

 

 

 

문제1. age가 30인 경우 NULL을 반환하고, 각 레코드의 age 값을 2배로 하여 age_value로 출력하시오.

select 2 * nullif(age, 30) as age_value from finance_data;

 

 

문제2. last_update가 '2023-01-01'인 경우 NULL을 반환하여 update_date로 출력하시오.

select nullif(last_update, '2005-01-01') as update_date from actor;

 

 

---

 

CASE문

: 함수는 아니지만 조건 비교가 어러 개일 때 사용

 

select case when 조건 then 값

                   when 조건 then 값

                   else 값

                   end;

 

 

select age, case when age < 18 then '미성년자'
when age between 18 and 30 then '미성년자 아님'
else '시니어'
end as age_group
from finance_data;

 

 

---

 

집계함수 

 : 여러 행에 걸쳐 있는 값을 묶어서 계산을 수행하여 단일값을 반환하는 함수

 

GROUP BY절

 

각 age_group별로 총 gold를 계산하여 totalfund로 반환하시오.

select
case
when age < 18 then '미성년자'
when age between 18 and 30 then '청장년층'
else '중장년층'
end as age_group,
sum(gold) as total_gold
from finance_data
group by age_group;

 

 

 

문제1. 각 성별과 나이 그룹별 총 gold을 계산하고 10 이상인 그룹만 반환

select gender, age, sum(gold) as total_fund
from finance_data
group by gender, age
having sum(gold) >= 10;

 

 

문제2. 각 성별별 gold의 평균을 계산하고 10 이상인 그룹만 반환하여 avg_golds로 출력하시오.

select gender, avg(gold) as avg_golds
from finance_data
group by gender
having avg(gold) >= 6;

 

 

문제3. 각 성별과 나이 그룹별 총 gold을 계산하고 10 이상인 그룹만 반환
각 성별별 gold의 평균을 계산하고 6 이상인 그룹만 반환하여 avg_golds로 출력하시오.

select Dept_no, count(*) as 직원수
from employee
group by Dept_no
having count(*) >= 5;

 

 

문제4. 고객 주문 수와 고객의 주문 수가 10개 이상인 주문 조회

select customer_no, count(order_no) as 주문수
from orders
group by customer_no
having count(order_no) >= 10;

 

 

문제5. 제품별 총 판매 수량과 총 판매 수량이 100이상인 제품 조회

select product_no, sum(order_quantity) as '총판매수량'
from order_details
group by product_no
having sum(order_quantity) >= 100;

 

 

문제6. 각 도시별 고객 수와 고객 수가 10명 이상인 도시 조회

select city, count(customer_no) as 고객
from customer
group by city
having count(customer_no) >= 10;

 

 

문제7. 각 고객의 마일리지 합계와 마일리지가 10000이상인 고객 조회

select customer_no, sum(mileage) as 마일리지합계
from customer
group by customer_no
having sum(mileage) >= 10000;

 

 

---

 

WITH ROLLUP

: 부분과 전체 집합 같이 보고싶은 경우

 

문제1. 각 부서별 및 전체 직원 수 조회

select dept_no, count(*) as 직원수
from employee
group by dept_no with rollup;

 

 

문제2. 각 도시별 및 전체 고객수 조회

select city, count(*) as 고객수
from customer
group by city with rollup;

 

 

문제3. 각 제품별 및 전체 총 판매 수량과 총 판매 금액 조회

select product_no ,sum(order_quantity) as 총판매수량,
 sum(order_quantity * unit_price * (1 - discount_rate)) as 총판매금액
 from order_details
 group by product_no with rollup;

 

 

---

 

복습 문제

 

문제 1. 각 직원별 및 전체 주문 처리 수와 평균 주문 처리 시간 조회 (order_detail 테이블)

select emp_no, count(order_no) as 전체주문처리수, 
avg(request_date - order_date) as 평균주문처리시간
from orders
group by emp_no;

 

문제 2: 각 부서별 및 전체 직원 수와 평균 입사 날짜 조회(employee 테이블)

select Dept_no, count(emp_no) as 전체직원수, 
avg(date_of_emp) as 평균입사날짜
from employee
group by Dept_no with rollup;

문제 3: 각 부서별 및 전체 직원 수와 평균 나이 조회 (employee 테이블)

select Dept_no, count(Emp_no) as 전체직원수, avg(year(now()) - year(birthday)) as 평균나이
from employee
group by Dept_no;

 

select ifnull(dept_no, '전체') as 부서번호,
count(emp_no) as 직원수,
avg(year(curdate()) - year(birthday)) as 평균나이
from employee
group by dept_no with rollup;

 

 

 

각 직원별 총 주문 수와 전체 총 주문 수 조회

(총 주문 수가 5개 이상인 직원만, 평균 처리 시간이 2일 이상인 경우 '지연'으로 표시)

select ifnull(product_no, '전체') as 제품번호,
sum(order_quantity) as 총판매수량,
case when avg(discount_rate) = 0 then '할인없음'
else avg(discount_rate)
end as 평균할인률, 
sum(order_quantity * unit_price * (1 - discount_rate)) as 총판매금액
from order_details
where order_quantity > 0
group by product_no with rollup
having sum(order_quantity) >= 100 or product_no is null;