07 SQL 실습

1. SELECT

SELECT 구문은 SQL에서 가장 중요한 명령어 중 하나로, 데이터베이스에서 데이터를 조회하는 데 사용됨
SELECT는 테이블에서 원하는 데이터를 선택하여 반환하며, 이를 통해 데이터베이스에 저장된 정보를 분석하거나 필요한 데이터를 추출할 수 있음
실무에서 데이터베이스 작업의 핵심 요소로, 데이터를 효율적으로 검색, 분석, 가공하는 데 사용됨, 대부분의 비즈니스 시스템에서 데이터는 저장될 뿐만 아니라 조회되고 분석되어야 하므로, SELECT는 거의 모든 데이터 관련 작업에 필수적

기본구조

SELECT 열1, 열2, ...
FROM 테이블명
WHERE 조건;

SELECT 구문 활용

1. SELECT 구문으로 테이블 전체 선택

* (애스터리스크) : 모든 열(컬럼)을 선택함
Members 테이블의 모든 컬럼 조회

SELECT * FROM members;

2. SELECT 구문으로 일부 컬럼 선택

조회하고자 하는 컬럼 검색
Members 테이블의 name, mobile, age 컬럼만 조회

SELECT name, mobile, age FROM members;

3. ALL / DISTINCT

ALL과 DISTINCT는 SQL에서 데이터 조회 시 중복된 데이터 처리 방식을 지정하는 옵션
주로 SELECT 구문에서 사용되며, 각 키워드는 중복된 값을 어떻게 처리할지를 결정
DISTINCT
- 결과 집합에서 중복된 값을 제거하고 고유한 값만 반환함
- SELECT 구문에서 특정 열에 대해 중복을 제거할 때 사용

SELECT distinct department_id FROM new_schema.employees;

ALL
- Default 옵션이므로 별도로 표시하지 않아도 됨
- 중복된 데이터가 있어도 모두 출력함
- 명시적으로 중복을 유지할 때 사용되지만, 대부분의 경우 생략됨
- 아래 두 결과는 동일 함

SELECT ALL department_id FROM employees;
SELECT department_id FROM employees;

4. AS

AS는 SQL에서 별칭(Alias)을 지정할 때 사용함
테이블이나 열의 이름을 임시로 다른 이름으로 바꾸어 사용하고자 할 때 AS를 사용하여 가독성을 높이거나 결과의 의미를 명확히 할 수 있음
주로 조회 결과의 열 이름이나 테이블 이름에 새로운 이름을 지정하는 데 사용되며, 이 별칭은 쿼리의 결과에만 영향을 미치고, 실제 데이터베이스 구조에는 영향을 주지 않음

SELECT gender, count(*) as gender_count from members group by gender;
-- Members 테이블에서 gender에 따른 합계를 gender_count란 이름으로 출력

산술연산자

SQL의 산술 연산자는 숫자 데이터를 다룰 때 사용되며, SELECT 구문에서 값을 계산하거나 결과를 조작하는 데 매우 유용함
산술연산자의 종류

산술 연산자

설명

()

연산자 우선순위 변경

더하기

빼기

곱하기

나누기

나머지

산술연산자 예시

SELECT 10 + 5 AS result;
SELECT 10 - 5 AS result;
SELECT 10 * 5 AS result;
SELECT 10 / 5 AS result;
SELECT 10 % 5 AS result;

실제 테이블에서 산술연산자 예시

SELECT name, salary, (salary + salary * 0.15) AS total_salary_with_bonus
FROM employees;

합성연산자

문자열 합성 연산자는 여러 문자열을 하나로 결합하는 데 사용되는 연산자임
문자열을 동적으로 조합하거나 결과를 사용자에게 더 읽기 쉽게 표현할 수 있음
MySQL에서는 문자열을 결합하는 방법으로 CONCAT() 함수가 주로 사용됨

CONCAT() 함수

CONCAT() 함수는 여러 개의 문자열을 하나로 결합하는 함수
원하는 만큼의 문자열 인수를 전달할 수 있으며, 그 인수들을 차례대로 연결하여 하나의 문자열로 반환함

SELECT CONCAT('Hello', ' ', 'World') AS greeting;

SELECT CONCAT(name, '의 월급은 ', salary, '원 입니다.') AS salary_info
FROM employees;

CONCAT_WS() 함수

CONCAT_WS()는 "With Separator"의 약자로, 인수들 사이에 지정한 구분자를 넣어 문자열을 결합함
첫 번째 인수로 구분자를 제공하고, 그 뒤에 결합할 문자열들을 나열

SELECT CONCAT_WS('-', '2024', '10', '23') AS formatted_date;

SELECT CONCAT_WS(' ', name, salary, department_id) AS employee_summary
FROM employees;

2. MySQL 내장 함수

Concat 함수처럼 MySQL 내부에 정의되어 있는 함수를 내장함수라고 함
SQL 내장 함수는 다양한 작업을 수행하는 데 사용되며, 크게 집계 함수(Aggregate Functions), 문자열 함수(String Functions), 수학 함수(Mathematical Functions), 날짜 및 시간 함수(Date and Time Functions), 형 변환 함수(Type Conversion Functions) 등으로 나뉨
내장 함수의 종류

2-1. 집계 함수 (Aggregate Functions)

집계 함수는 여러 행에 걸친 데이터를 하나의 값으로 집계하는 데 사용됨

COUNT()

주어진 조건에 맞는 행의 개수를 반환
COUNT() 함수는 NULL 값을 제외하고 계산됨

SELECT COUNT(*) AS total_employees, COUNT(manager_id) AS total_manager
FROM employees;

SUM()

특정 컬럼의 합계를 반환함
주로 숫자 데이터를 다룰 때 사용되며, NULL 값은 무시됨

SELECT SUM(salary) AS total_salary
FROM employees;

AVG()

특정 컬럼의 평균 값을 반환함
NULL 값은 계산에서 제외됨

SELECT AVG(salary) AS average_salary
FROM employees;

MIN()

특정 컬럼에서 가장 작은 값을 반환
숫자, 날짜, 문자열 데이터 타입 모두에서 사용 가능

SELECT MIN(salary) AS min_salary
FROM employees;

MAX()

특정 컬럼에서 가장 큰 값을 반환함
숫자, 날짜, 문자열 데이터 타입 모두에서 사용 가능

SELECT MAX(salary) AS max_salary
FROM employees;

2-2. 날짜 및 시간 함수 (Date and Time Functions)

SQL에서 날짜 및 시간 함수는 데이터베이스에서 날짜 기반의 조회나 분석을 할 때 자주 사용됨

NOW()

현재 날짜와 시간을 반환함
DATETIME 형식으로 현재 시스템의 날짜와 시간을 반환

SELECT NOW() AS current_datetime;

CURDATE()

현재 날짜만 반환
시간은 포함되지 않고, YYYY-MM-DD 형식의 DATE 타입 값이 반환됨

SELECT CURDATE() AS currentdate;

CURTIME()

현재 시간만 반환
날짜는 포함되지 않고, HH:MM:SS 형식의 TIME 타입 값이 반환됨

SELECT CURTIME() AS currenttime;

YEAR(), MONTH(), DAY()

날짜에서 각각 연도, 월, 일을 추출하는 함수
주로 날짜 필드를 분석하거나 필터링할 때 사용됨

SELECT name, YEAR(hire_date) AS hire_year, MONTH(hire_date) AS hire_month, DAY(hire_date) AS hire_day FROM employees;

DATE()

DATETIME이나 TIMESTAMP 형식의 값에서 날짜 부분만 추출
시간 정보는 무시됨

SELECT name, DATE(hire_date) AS hire_date_only FROM employees;

DATE_ADD()

날짜에 특정 기간을 더할 때 사용됨

SELECT NOW() AS today, DATE_ADD(NOW(), INTERVAL 7 DAY) AS future_date;

SELECT NOW() AS today, DATE_ADD(NOW(), INTERVAL 1 YEAR) AS one_year_later;

DATEDIFF()

두 날짜 간의 차이를 일 단위로 반환함
첫 번째 인수에서 두 번째 인수를 뺀 차이를 반환

SELECT name, DATE(hire_date) AS hire_date, CURDATE() AS today, DATEDIFF(NOW(), hire_date) AS days_since_hire FROM employees;

DATE_FORMAT()

날짜의 형식을 지정하여 반환할 때 사용됨
날짜와 시간을 특정 형식으로 출력하거나 데이터를 정리할 때 유용함
hire_date를 '년-월-일' 형식으로 출력

SELECT name, DATE_FORMAT(hire_date, '%Y-%m-%d') AS formatted_hire_date FROM employees;

2-3. 문자열 함수(String Functions)

SQL에서 문자열 데이터를 처리하고 변환하는 데 사용됨
문자열을 조작하여 데이터를 더 효과적으로 관리하고 조회할 수 있게 도와줌
MySQL을 포함한 대부분의 데이터베이스 시스템에서 문자열을 다루는 다양한 함수들이 제공되고 있음

CONCAT()

두 개 이상의 문자열을 하나로 결합
원하는 만큼의 문자열을 결합할 수 있음

SELECT CONCAT(name, ' 연봉: ', salary) AS employee_info FROM employees;

SUBSTRING() 또는 SUBSTR()

문자열의 일부분을 추출함
시작 위치와 추출할 길이를 지정하여 특정 부분을 추출할 수 있음

SELECT name, SUBSTRING(name, 1, 3) AS name_part, hire_date, SUBSTRING(hire_date, 1, 4) AS hire_year FROM employees;

UPPER()

문자열을 모두 대문자로 변환함

SELECT customer_name, UPPER(customer_name) AS upper_name FROM customers;

LOWER()

문자열을 모두 소문자로 변환함

SELECT customer_name, LOWER(customer_name) AS lower_name FROM customers;

LENGTH()

문자열의 길이를 반환함
길이는 문자열의 문자 수를 나타냄

SELECT customer_name, LENGTH(customer_name) AS name_length FROM customers;

TRIM()

문자열의 양쪽 끝에 있는 공백을 제거함
LEADING, TRAILING, 또는 BOTH 옵션을 통해 앞쪽, 뒤쪽, 또는 양쪽 모두에서 공백을 제거할 수 있음

SELECT product_id, product_name, TRIM(product_name) AS trimmed_product_name FROM trim_practice;

SELECT product_id, product_name, TRIM('*' FROM product_name) AS trimmed_product_name FROM trim_practice;

SELECT product_id, product_name, TRIM(LEADING '*' FROM product_name) AS trimmed_product_name
FROM trim_practice;

SELECT product_id, product_name, TRIM(TRAILING '*' FROM product_name) AS trimmed_product_name
FROM trim_practice;

SELECT product_id, product_name, TRIM(BOTH '*' FROM product_name) AS trimmed_product_name
FROM trim_practice;

REPLACE()

문자열 내에서 특정 부분을 다른 문자열로 대체

SELECT name, REPLACE(name, '김', '이') AS replaced_name FROM employees;

INSTR()

문자열 내에서 특정 문자열이 처음으로 등장하는 위치를 반환함
위치는 1부터 시작하고 문자열이 없으면 0을 반환함

SELECT name, INSTR(name, '수') AS position FROM employees;

LEFT()

문자열의 왼쪽부터 지정한 길이만큼의 문자열을 반환함

SELECT name, LEFT(name, 2) AS left_part FROM employees;

RIGHT()

문자열의 오른쪽부터 지정한 길이만큼의 문자열을 반환함

SELECT name, RIGHT(name, 2) AS right_part FROM employees;

LPAD() 및 RPAD()

문자열의 왼쪽 또는 오른쪽에 지정한 길이만큼 특정 문자를 추가하여 길이를 맞춤
이름을 길이 10으로 만들고, 왼쪽에 * 문자를 채움

SELECT name, LPAD(name, 10, '*') AS padded_name FROM employees;

REVERSE()

문자열을 역순으로 변환

SELECT name, REVERSE(name) AS reversed_name FROM employees;

2-3. 수학 함수(Mathematical Functions)

SQL에서 수치 데이터를 처리하고 변환하는 데 사용됨
이 함수들은 숫자 값의 연산, 절대값 계산, 반올림, 제곱근 계산 등 다양한 수학적 작업을 쉽게 처리할 수 있게 도와줌

ABS()

숫자의 절댓값을 반환함
음수 값이 주어지면 양수로 변환하여 반환

SELECT ABS(-10) AS absolute_value;

CEIL() 또는 CEILING()

소수점이 있는 숫자를 올림하여 가장 가까운 정수 값을 반환

SELECT CEIL(4.3) AS ceil_value;

FLOOR()

소수점이 있는 숫자를 내림하여 가장 가까운 정수 값을 반환

SELECT FLOOR(4.9) AS floor_value;

ROUND()

숫자를 소수점 아래에서 반올림함
두 번째 인수로 소수점 자릿수를 지정할 수 있으며, 생략하면 소수점 첫 번째 자리에서 반올림함

SELECT ROUND(123.456, 2) AS rounded_value;

MOD()

두 숫자를 나누었을 때의 나머지 값을 반환함

SELECT MOD(10, 3) AS remainder;

POWER()

주어진 숫자의 거듭제곱 값을 반환함
첫 번째 인수는 밑(base)이고, 두 번째 인수는 지수(exponent)

SELECT POWER(2, 3) AS power_value;

SQRT()

숫자의 제곱근 반환

SELECT SQRT(16) AS sqrt_value;

RAND()

0과 1 사이의 난수를 반환함
주로 무작위 수를 생성하는 데 사용됨

SELECT RAND() AS random_value;

SELECT * 
FROM products
ORDER BY RAND()
LIMIT 3;

SELECT FLOOR(100000 + RAND() * 900000) AS temp_password;

TRUNCATE()

숫자를 소수점 이하에서 버림하여 특정 자릿수까지 표현함
반올림 없이 단순히 잘라냄

SELECT TRUNCATE(123.456, 1) AS truncated_value;

2-4. 형 변환 함수(Type Conversion Functions)

데이터 타입으로 변환하는 데 사용됨
데이터베이스에서 데이터를 저장할 때, 숫자, 문자열, 날짜 등 다양한 데이터 타입이 사용되는데, 이를 필요에 따라 변환해야 할 때가 있음, SQL은 이러한 형 변환을 위한 여러 함수를 제공함

CAST()

값을 특정 데이터 타입으로 변환함
형 변환 시 타입을 명시적으로 지정하며, 정확한 형식으로 데이터를 변환할 수 있음
숫자 형태로 변경되지 않는 것은 고정값으로 0이 들어감

SELECT 
    id,
    original_value,
    CAST(original_value AS DECIMAL(10, 2)) AS converted_value,
    CAST(original_value AS DECIMAL(10, 2)) + 100 AS value_plus_100
FROM type_data;

CONVERT()

CAST()와 비슷하게 데이터 타입을 변환하지만 일부 DBMS(MySQL, SQL Server)에서 사용 가능
데이터 타입 변환 뿐 아니라 문자 인코딩 변환도 가능함

SELECT 
    id,
    original_value,
    CONVERT(original_value, DECIMAL(10, 2)) AS converted_value,
    CONVERT(original_value, DECIMAL(10, 2)) + 100 AS value_plus_100
FROM type_data;

SELECT 
    id,
    text_data,
    CONVERT(text_data USING latin1) AS latin1_text,
    CONVERT(text_data USING utf8) AS utf8_text
FROM encoding_test;

2-5. 흐름 제어 함수(Flow Control Functions)

조건을 평가하여 논리적인 흐름을 제어하거나 특정 조건에 따라 다른 값을 반환할 수 있게 해주는 함수
주로 조건에 따른 값 선택이나 조건에 따른 분기 처리를 위해 사용됨

IF()

조건이 참(true)인지 거짓(false)인지를 평가한 후, 조건이 참일 경우와 거짓일 경우에 각각 다른 값을 반환
IF(condition, true_value, false_value)
condition: 참 또는 거짓을 평가할 조건식
true_value: 조건이 참일 때 반환할 값
false_value: 조건이 거짓일 때 반환할 값

SELECT 
    name, 
    salary, 
    IF(salary > 7000, 'High', 'Low') AS salary_level
FROM employees;

CASE()

여러 조건을 평가하여 각각의 조건에 맞는 값을 반환함
CASE는 두 가지 방식으로 사용할 수 있음: 단순 CASE / 검색 CASE
단순 CASE

SELECT name, department_id, 
CASE department_id
    WHEN 1 THEN '인사'
    WHEN 2 THEN '구매'
    ELSE '기타'
END AS department_name
FROM employees;

검색 CASE

SELECT name, salary, 
CASE 
    WHEN salary >= 9000 THEN 'Very High'
    WHEN salary >= 7000 THEN 'High'
    ELSE 'Low'
END AS salary_grade
FROM employees;