select floor(3.1), ceil(3.1), round(3.1), round(3.5) from dual;
2. 문자열 함수
concat(string1, string2, ...) : 여러 문자열을 연결
left(string, length) : 문자열의 왼쪽에서 지정한 개수만큼 추출
right(string, length) : 문자열의 오른쪽에서 지정한 개수만큼 추출
substring(string, start, length) or substr(string, start, length) : 지정한 위치부터 지정한 개수만큼 추출
repeat(string, count) : 지정한 문자열을 주어진 횟수만큼 반복
length(string) : 문자열 길이 반환
reverse(string) : 문자열을 거꾸로 리턴
upper(string) : 문자열 내 문자를 대문자로 변환
lower(string) : 문자열 내 문자를 소문자로 변환
rtrim(string) : 오른쪽의 공백을 삭제
ltrim(string) : 왼쪽의 공백 삭제
trim(string) : 좌우 공백 삭제
3. 논리 관련 함수
case when ~ then : switch-case문과 유사한 역할
if(condition, true_value, false_value) : 조건식이 참이면 참일 때 값을 출력하고, 그렇지 않으면 거짓일 때 값을 출력함
예제
select gno, brand, price,
case
when price < 10000 then price * 1.15
when price >= 10000 then price * 1.1
end as INC_price
from goods
order by INC_price asc;
select gno, brand , price,
if(price >= 30000, price * 1.15, price * 1.1) as IncPrice
from goods;
4. 날짜 관련 함수
curdate(): 현재 날짜를 반환
curtime(): 현재 시간을 반환
now(): 현재 날짜와 시간을 반환
sysdate(): 현재 날짜와 시간을 반환 (DB 서버 시간)
current_timestamp(): 현재 타임스탬프를 반환
DATE_ADD(date, INTERVAL value unit): 지정된 날짜에 시간이 더해짐
DATE_SUB(date, INTERVAL value unit): 지정된 날짜에서 시간이 빼짐
DAYOFWEEK(date): 날짜의 주별 일자를 반환 (일요일(1) ~ 토요일(7))
WEEKDAY(date): 월요일(0) ~ 일요일(6)로 요일을 숫자로 반환
DAYOFYEAR(date): 1월 1일부터 해당 날짜까지의 날수를 반환
YEAR(date), MONTH(date), DAYOFMONTH(date), HOUR(date), MINUTE(date), SECOND(date) : 각각 년, 월, 일, 시, 분, 초를 반환
MONTHNAME(date) : 해당 날짜의 월 이름을 반환
DAYNAME(date) : 해당 날짜의 요일 이름을 반환
QUARTER(date) : 해당 날짜의 분기를 반환
date_format(date, format) : 날짜 형식 임의 지정 가능
예제
select curdate(), curtime(), now(), sysdate(), current_timestamp() from dual;
select date_sub(now(), interval 1 day) from dual; -- 어제
select date_add(now(), interval 1 day) from dual; -- 내일
select dayofweek(now()) from dual;
select year(sysdate()), month(sysdate()), dayofmonth(sysdate()), hour(sysdate()), minute(sysdate()), second(sysdate()) from dual;
select date_format(sysdate(), '%y년 %m월 %d일 - %p %h시 %i분 %s초') as today from dual;
1. SQL SELECT 문
형식
SELECT [DISTINCT] * | 컬럼명 [AS alias] | 간단한 연산 | 함수 호출
FROM 테이블명 [AS alias], ...
[WHERE 조건]
[GROUP BY 컬럼명, ... [HAVING 조건]]
[ORDER BY 컬럼명 [ASC | DESC], ...]
예제
-- 모든 행, 모든 열 조회
SELECT * FROM members;
-- 특정 열 조회
SELECT id, name, email FROM members;
-- 상품에 대한 정보 조회
SELECT gno, brand, price, price * 1.1 AS IncPrice FROM goods;
2. WHERE 절
조건 연산자: = (같음), !=, <> (다름), >, >=, <, <=
AND / OR: 논리 게이트
IS NULL / IS NOT NULL: NULL 값 비교
사용 예시
-- 조건을 통한 행 추출
SELECT gno, brand, price FROM goods WHERE price >= 5000;
-- 특정 ID 제외
SELECT * FROM members WHERE id != 'kdg';
-- NULL 값 조회
SELECT * FROM goods WHERE cno IS NULL;
SELECT * FROM goods WHERE cno IS NOT NULL;
3. DISTINCT (중복 제거)
DISTINCT: 중복된 값을 제거하여 조회
사용 예시
-- 중복 없는 분류 번호 조회
SELECT DISTINCT cno FROM goods;
4. BETWEEN과 IN 연산자
BETWEEN: 범위 조건 조회
IN: 열거형 데이터 조회
사용 예시
-- 가격 범위 조회
SELECT * FROM goods WHERE price BETWEEN 5000 AND 15000;
-- 특정 분류 번호 제외
SELECT * FROM goods WHERE cno NOT IN (10, 20);
5. LIKE 연산자
LIKE: 특정 문자열 패턴 포함 조회
와일드카드:특정 문자를 대신할 수 있는 기호를 뜻함.
_: 한 문자를 대체
%: 문자열 길이 상관없이 대체
사용 예시
-- 특정 제조사가 포함된 상품 조회
SELECT * FROM goods WHERE maker LIKE '%LG%';
SELECT * FROM goods WHERE brand LIKE '%마우스%';
-- 두 쿼리문 모두 앞 뒤 다른 문자 뭐가 나오든 상관없음. 그냥 해당 단어만 포함되어 있으면 됨
6. CASE 문
CASE: 조건에 따른 값 반환
사용 예시
SELECT gno, brand, price, cno,
CASE cno
WHEN '10' THEN '음식'
WHEN '20' THEN '전자제품'
WHEN '30' THEN '책'
WHEN '40' THEN '가구'
ELSE '미분류'
END AS 분류명
FROM goods;
7. ORDER BY 절
ORDER BY: 정렬
ASC: 오름 차순 (=> 생략 가능)
DESC: 내림 차순 ( => 생략 불가!)
사용 예시
-- 가격 오름 차순 정렬
SELECT * FROM goods ORDER BY price;
-- 가격 내림 차순 정렬
SELECT * FROM goods ORDER BY price DESC;
-- 복수 기준 정렬
SELECT * FROM goods ORDER BY cno DESC, brand;
8. Alias 사용
Alias: SQL 문에서 쉽게 참조하기 위해 컬럼에 별칭을 정의하는 것, as로 사용합니다!
사용 예시
SELECT empno AS employNo, ename AS employName, sal AS salary
FROM emp
WHERE empno > 7500
ORDER BY employNo; -- ORDER BY 절에서는 alias 사용 가능
SQL 쿼리 실행 순서 😍
FROM: 데이터 소스를 정의합니다. 테이블과 조인 조건이 결정됩니다.
WHERE: 데이터를 필터링합니다. FROM 절에서 정의된 데이터 중 조건을 만족하는 행만 선택됩니다.
GROUP BY: 데이터를 그룹화합니다. 같은 값을 가진 행들을 묶어줍니다.
HAVING: 그룹화된 데이터에 대해 조건을 적용합니다. GROUP BY로 묶인 데이터에서 조건을 만족하는 그룹만 선택됩니다.
SELECT: 최종적으로 반환할 열을 지정합니다. 필요한 열, 별칭, 함수 등을 정의합니다.
ORDER BY: 정렬 기준에 따라 결과를 오름차순 또는 내림차순으로 정렬합니다.
예시
SELECT brand, COUNT(*) AS product_count
FROM goods
WHERE price > 5000
GROUP BY brand
HAVING COUNT(*) > 1
ORDER BY product_count DESC;
수행 순서
FROM goods: goods 테이블에서 데이터를 선택.
WHERE price > 5000: 가격이 5000보다 큰 행만 필터링.
GROUP BY brand: 브랜드별로 데이터를 그룹화.
HAVING COUNT(*) > 1: 그룹화된 결과 중 상품 수가 1보다 큰 그룹만 선택.
SELECT brand, COUNT(*) AS product_count: 필요한 열과 계산된 열을 선택.
ORDER BY product_count DESC: product_count를 기준으로 내림차순 정렬.
SQL GROUP BY 문
1. 집계 함수 (Aggregation Functions)
정의: 데이터 그룹에 대해 집계된 결과를 반환해주는 함수
주요 집계 함수:
COUNT(*): 전체 행 수
COUNT(column): NULL이 아닌 행 수
SUM(column): 합계
AVG(column): 평균
MIN(column): 최소값
MAX(column): 최대값
사용 예시
-- 전체 직원 수 조회
SELECT COUNT(*) FROM emp;
-- comm이 있는 사원 수 조회
SELECT COUNT(comm) FROM emp;
-- 급여 관련 통계 조회
SELECT MIN(sal), MAX(sal), AVG(sal) FROM emp;
2. GROUP BY 절
정의: 데이터를 특정 열을 기준으로 그룹화하여 집계 작업을 수행해주는 절
쿼리 구조:
SELECT 컬럼, 집계함수
FROM 테이블
GROUP BY 컬럼
[ORDER BY 컬럼];
예제
-- 부서별 인원수와 평균 급여 조회
SELECT deptno, COUNT(*) AS 인원수, AVG(sal) AS 평균급여
FROM emp
GROUP BY deptno
ORDER BY deptno;
-- 업무별 인원수와 평균 급여
SELECT job, COUNT(*) AS 인원, ROUND(AVG(sal), 2) AS 평균급여
FROM emp
GROUP BY job
ORDER BY job;
3. HAVING 절
정의: GROUP BY로 그룹화된 결과에 조건을 적용해주는 절
쿼리 구조:
SELECT 집계함수
FROM 테이블
GROUP BY 열
HAVING 조건;
예제
-- 부서별 평균 급여가 2000 이상인 부서 조회
SELECT deptno, AVG(sal) AS avgSal
FROM emp
GROUP BY deptno
HAVING avgSal >= 2000;
4. ROLLUP
정의: 그룹별 통계와 전체 통계를 추가로 조회합니다. GROUP BY 결과에 전체 합계를 추가할 수 있게 해준다.
쿼리 구조:
SELECT 컬럼, 집계함수
FROM 테이블
GROUP BY 컬럼 WITH ROLLUP;
5. GROUPING 함수
정의: ROLLUP에 의해 생성된 그룹을 식별합니다. 결과가 집계된 행인지 여부를 확인합니다.
쿼리 예시:
SELECT IF(GROUPING(job) = 1, 'total', IFNULL(job, '프리랜서')) AS job,
COUNT(*) AS 근무인원, ROUND(AVG(sal), 2) AS avgSal, MIN(sal), MAX(sal)
FROM emp
GROUP BY job WITH ROLLUP;
주의사항
GROUP BY를 사용할 때, SELECT 문의 나머지 열은 GROUP BY에 포함되거나 집계 함수로 감싸져야 합니다.
[MySQL] ONLY_FULL_GROUP_BY 설정이 기본으로 활성화되지 않아서, GROUP BY와 관련하여 다른 열을 같이 사용하는 경우, 버전에 따라 의미 없는 결과가 나올 수 있음 => 이를 해결하기 위해 settings에서 다음과 같이 설정하면 됨.
SET @@session.sql_mode = 'STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY';
SQL JOIN 문
1. JOIN이란?
JOIN: 두 개 이상의 테이블을 결합하여 데이터를 조회하는 방법
종류 간단 정리
Inner Join: 조인 조건에 맞는 데이터만 조회.
Outer Join: 조인 조건에 맞지 않는 데이터도 포함하여 조회.
Left Outer Join: 왼쪽 테이블의 모든 데이터와 조인 조건을 만족하는 오른쪽 테이블의 데이터 조회.
Right Outer Join: 오른쪽 테이블의 모든 데이터와 조인 조건을 만족하는 왼쪽 테이블의 데이터 조회
Full Outer Join: 두 테이블의 모든 데이터를 포함하여 조회. (일반적으로 사용되지 않음)
Self Join: 동일한 테이블 내에서 조인
Natural Join: 동일한 열 이름을 가진 컬럼을 기준으로 자동으로 조인
Cross Join (Cartesian Product): 조인 조건을 생략하면 두 테이블의 모든 조합을 조회
2. 카티시안 곱 (Cross Join)
정의: 조인 조건을 생략했을 때 두 테이블의 모든 행을 연결한 결과를 조회.
SELECT gno, brand, price, goods.cno AS cno, category.cno AS 분류번호, name
FROM goods, category;
3. Inner Join
형식:
SELECT *
FROM 테이블명1
JOIN 테이블명2 ON 조건;
사용 예시:
-- Inner Join 예시
SELECT gno, brand, price, name
FROM goods INNER JOIN category ON goods.cno = category.cno
ORDER BY gno;
4. Outer Join
4.1 Left Outer Join
정의: 왼쪽 테이블의 모든 행과, 조인 조건을 만족하는 오른쪽 테이블의 행을 조회. 오른쪽 테이블에 조인 조건을 만족하는 행이 없으면, NULL이 반환됩니다.
형식
SELECT 컬럼명1, 컬럼명2, ...
FROM 왼쪽_테이블 LEFT JOIN 오른쪽_테이블
ON 조인_조건;
사용 예시
SELECT e.empno, e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;
4.2 Right Outer Join
정의: 오른쪽 테이블의 모든 행과 조인 조건을 만족하는 왼쪽 테이블의 행을 조회. 왼쪽 테이블에 조인 조건을 만족하는 행이 없으면, NULL이 반환됩니다.
형식
SELECT 컬럼명1, 컬럼명2, ...
FROM 왼쪽_테이블 RIGHT JOIN 오른쪽_테이블
ON 조인_조건;
사용 예시
SELECT d.dname, e.ename
FROM dept d
RIGHT JOIN emp e ON d.deptno = e.deptno;
4.3 Full Outer Join
정의: 두 테이블에 대한 모든 행을 조회하고, 조인 조건을 만족하지 않는 행은 NULL로 표시됨
형식
SELECT 컬럼명1, 컬럼명2, ...
FROM 왼쪽_테이블 FULL OUTER JOIN 오른쪽_테이블
ON 조인_조건;
예제
SELECT e.empno, e.ename, d.dname
FROM emp e
FULL OUTER JOIN dept d ON e.deptno = d.deptno;
5. Self Join
정의: 동일한 테이블을 조인해야 할때 사용, 같은 테이블에 대해 서로 다른 별칭을 붙여주면 됨
예제
SELECT e.empno, e.ename, e.job, e.sal, e.mgr, m.ename
FROM emp e
JOIN emp m ON e.mgr = m.empno;
6. Natural Join
정의: 동일한 컬럼명을 가진 컬럼을 기준으로 자동으로 조인
예제
SELECT empno, ename, deptno, dname
FROM emp NATURAL JOIN dept;
JOIN 쿼리문 작성 시 DB 벤더 전용 방식 vs ANSI 표준 방식 비교
DB 벤더 방식
ANSI 표준 방식
조인 표현
쉼표(,) + WHERE 절
JOIN 키워드 사용
지원 여부
일부 DBMS에서 지원 안 함
거의 모든 DBMS에서 지원
다양한 조인
INNER JOIN만 명확하게 표현 가능
INNER, LEFT, RIGHT, FULL JOIN 모두 지원
DB 벤더
SELECT *
FROM employees e, departments d
WHERE e.department_id = d.department_id;
ANSI 방식 (ON)
SELECT *
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
USING vs ON (ANSI 조인)
USING
ON
사용 조건
조인할 컬럼명이 동일할 때
조인할 컬럼명이 다르거나, 비교 연산자가 =가 아닐 때
Alias 사용
불가능 (사용 시 오류 발생)
가능 (같은 컬럼도 테이블을 구분해야 함)
기본 비교 연산자
=
자유롭게 지정 가능 (>, <, LIKE 등)
ANSI 방식 (USING)
SELECT *
FROM employees
JOIN departments USING(department_id);
