(SQL 첫걸음) 데이터 베이스 #4

jjinny_0609·2023년 3월 14일
0

데이터베이스

목록 보기
15/17
post-custom-banner

정렬과 연산

데이터를 오름차순 또는 내림차순 데이터를 재배치

오름차순 : a-z
내림차순 : z-a

정렬 - ORDER BY

Syntax

SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 [ASC(Default) | DESC]
ASC : 오름차순
DESC : 내림차순

ORDER BY로 검색 결과를 정렬하기

[원본 SAMPLE31]

SELECT *
FROM SAMPLE31
ORDER BY age -- 나이를 기준으로 오름차순
-- ORDER BY AGE ASC -- 나이를 기준으로 오름차순
-- ORDER BY AGE DESC -- 나이를 기준으로 내림차순
;

SELECT *
FROM SAMPLE31
ORDER BY ADDRESS; -- 주소를 기준으로 오름차순
-- ORDER BY ADDRESS ASC; -- 주소를 기준으로 오름차순
-- ORDER BY ADDRESS DESC; -- 주소를 기준으로 오름차순

대소관계

ORDER BY로 정렬할 때는 값의 대소관계가 중요하다. 수치형 데이터라면 대소관계는 숫자의 크기로 판별하므로 이해하기 쉽다.
문자열형 데이터는 알파벳, 한글 순이며 한글은 자음, 모음순이다.
예를 들어 '나비', '가방', '가족'을 정렬할 경우 다음과 같습니다.
① 가방
② 가족
③ 나비
<문자열형 데이터의 대소관계는 사전식 순서에 의해 결정됨>

[원본 SAMPLE311]

SELECT *
FROM SAMPLE311
ORDER BY a
;

원본에 정보를 확인해보면 VARCHAR(2)와 INT로 만들어진것을 확인할 수 있다

DESC SAMPLE311;


문자열의 비교라서 a의 1과 다음줄의 1을 비교해서 누가큰지 비교하는 것
그렇기 때문에 위와 같은 결과를 나오는 것임.

SELECT *
FROM SAMPLE311
ORDER BY b DESC
;


b로 비교해보면 수치형 열이라 정상적으로 정렬 되는 모습을 볼 수 있다.

복수의 열을 지정해 정렬하기

Syntax ORDER BY 구

SELECT 열명 FROM 테이블명 WHERE 조건식
ORDER BY 열명 [ASC | DESC], 열명2[ASC | DESC] ...

[원본테이블 SAMPLE32]

a열로 정렬하기

SELECT *
FROM SAMPLE32
ORDER BY a;

원본과 비교해봤을때 a를 기준으로만 정렬되었다.

a열과 b열 둘다 정렬하기

SELECT *
FROM SAMPLE32
ORDER BY a, b
;

b열과 a열로 정렬하기

SELECT *
FROM SAMPLE32
ORDER BY b, a
;

a열과 b열로 정렬 후 내림차순

SELECT *
FROM SAMPLE32
-- ORDER BY a, b	-- 1차로 a열을 기준으로 오름차순하고, 2차로 b열을 기준으로 오름차순
-- ORDER BY b, a	-- 1차로 b열을 기준으로 오름차순, 2차로 a열을 기준으로 오름차순
-- ORDER BY a, b DESC	-- 1차로 b열을 기준으로 오름차순하고, 2차로 b열을 기준으로 내림차순
ORDER BY a DESC, b DESC	-- 1차로 a열을 기준으로 내림차순하고, 2차로 b열을 기준으로 내림차순
;

결과 행 제한하기 - LIMIT

Syntax

SELECT 열명 FROM 테이블명 LIMIT 행수 [OFFSET 시작행]

쇼핑몰이나 커뮤니티 사이트의 게시판에 게시물을 페이지마다 다른것을 볼 수 있었을텐데 이런 경우 LIMIT구를 이용해 표시할 건(행) 수를 제한할 수 있습니다. 페이징 처리에는 LIMIT(표준SQL이 아님),와 rownum(표준SQL임)을 사용할 수 있다.

행수 제한

LIMIT 구는 표준 SQL이 아니다.
MySQL과 PostgreSQL에서 사용할 수 있는 문법이라는 점에 주의하여야 한다. LIMIT 구는 SELECT 명령의 마지막에 지정하는 것으로 WHERE 구나 ORDER BY 구의 뒤에 지정한다.

Syntax LIMIT 구

SELECT 열명 FROM 테이블명 WHERE 조건식 OREDER BY 열명 LIMIT 행수

[원본 SAMPLE33]

LIMIT로 행수 제한하기

SELECT * FROM sample33 LIMIT 3;


LIMIT로 지정하는 것은 '최대 행수'이다.

  • 정렬 후 제한하기
    앞의 예제처럼 LIMIT 3을 한 것과 동일한 결과를 얻기 위해 WHERE 구에 조건을 지정할 수도 있습니다. 예를 들면 WHERE no <= 3과 같은 조건을 붙인다면 동일한 결과를 얻을 수 있습니다.
    하지만 LIMIT와 WHERE은 기능과 내부 처리순서가 전혀 다르다. LIMIT는 반환할 행수를 제한하는 기능으로, WHERE 구로 검색한 후 ORDER BY로 정렬한 뒤 최종적으로 처리된다.

LIMIT 3은 데이터가 하나 삭제되도 3개의 행만 반환하지만, WHERE no <= 3과 같은 조건으로 하게되면 중간에 데이터가 바뀌면 동일한 결과를 볼 수 없게된다.

  • 홈페이지 페이징에서...
    rownum : 서브쿼리에서 가상의 테이블에 두고 하나씩 증가시키는 방식(지금은 간단하게 알고 넘어가기)
    사용자에게 no를 보여주지만 내부에서는 rownum으로 처리한다.

SAMPLE33을 정렬후 LIMIT3으로 상위3건만 취득하기

SELECT * 
FROM sample33 
ORDER BY NO DESC
LIMIT 3
;

LIMIT로 첫 번째 페이지에 표시할 데이터 취득하기

SELECT * 
FROM SAMPLE33
LIMIT 3 OFFSET 0; -- 1페이지 3개 출력

LIMIT로 두 번째 페이지에 표시할 데이터 취득하기

SELECT * 
FROM SAMPLE33
LIMIT 3 OFFSET 3; -- 2페이지 3개 출력
-- 자바의 배열의 index를 생각하면됨 012에 123이 들어있고 3번부터 456이 들어감.

수치 연산

Syntax 산술 연산

+ - * / % MOD
  1. 사칙 연산
    산술 연산자(표)
  • 연산자의 우선 순위
  • 우선순위가 같다면 왼쪽에서 오른쪽으로 계산

SELECT 구로 연산하기Syntax SELECT 구

SELECT * FROM sample34;

SELECT *, price*quantity
from sample34
;


따로 열을 추가 하지않았는데 추가된것을 확인 할 수 있음.

열의 별명붙이기

위의 SELECT 결과에서 price*quantity라고 명명된 열 이름을 변경하고 싶다면 아래와 같이 사용한다.
Syntax SELECT 구에서 식에 별명 붙이기

SELECT *, price*quantity AS amount 
FROM sample34
;

별명을 한글로 변경하고 싶으면 한글을 작성하면 된다. 다만 오류가 발생할 수 있기때문에 ""(더블 쿼트쌍따옴표)나 '' (싱글쿼트따옴표)를 사용하는 것을 권장한다.

WHERE 구에서 계산, 검색하기

SELECT *, price*quantity AS amount 
FROM sample34
-- 가격 * 수량(주문금액)이 2000이상인 행 검색
where price*quantity >= 2000
;

amount를 사용해서 계산, 검색하기

SELECT *, price*quantity AS amount 
FROM sample34
-- 가격 * 수량(주문금액)이 2000이상인 행 검색
where amount >= 2000
;


앗! 위에서 amount를 별명으로 만들었었는데 왜 안될까?

  • WHERE구와 SELECT 구의 내부 처리순서를 알면 알 수있다.
    데이터베이스 서버 내부에서 WHERE → SELECT 구의 순서로 처리된다.
    별명은 SELECT 구문을 내부 처리할때 붙여지는데, WHERE 구의 처리에는 SELECCT 구보다 선행되므로 WHERE 구에서 사용한 별칭은 아직 내부에 지정되지 않은 상태가 되어 에러가 발생하는 것이다.

    읽어도 잘 모르겠다면 이것만 알고 넘어가자. SELECT구에서 지정한 별명은 WHERE 구 안에서 사용할 수 없다.

NULL 값의 연산

NULL값을 이용해 NULL + 1 과 같은 연산을 하면 어떻게 될까?
결과는 NULL이 나온다.
다른결과들도 마찬가지 NULL이 들어간 연산은 어떤값을 더하고 빼고, 나누고, 곱해도 NULL값이 나온다.

NULL + 1
1 + NULL
1 + 2 * NULL
1 / NULL
전부 NULL이 나옴.

NULL이 들어간 연산은 어떤값을 넣더라도 더하고 빼고, 나누고, 곱해도 NULL값이 나온다.

원본

값 + NULL 했을때 NULL값이 나오는것을 볼 수 있다.

ORDER BY 구에서 연산하기

SELECT *, price*quantity AS amount 
FROM sample34
-- 가격 * 수량(주문금액)이 2000이상인 행 검색
-- where amount >= 2000
-- order by price*quantity
order by amount
;


ORDER BY는 서버에서 내부적으로 가장 나중에 처리되므로 SELECT 구에서 지정한 별명을 ORDER BY에서도 사용할 수 있습니다. (아래의 SQL 쿼리 실행순서 참고)

SELECT SQL 문법 작성 순서(사용자 작성)

SELECT 열명
FROM 테이블명
WHERE 조건식
GROUP BY 그룹화할 열명
HAVING 그룹하고 난 후의 조건식
ORDER BY 정렬하고자 하는 열명

SELECT SQL 쿼리 실행 순서(DBMS 내부 처리)

FROM 조회 테이블 확인
WHERE 데이터 추출 조건 확인
GROUP BY 그룹핑
HAVING 그룹한 후 조건
SELECT 데이터 추출
ORDER BY 데이터 순서 정렬

금액의 별명을 사용해 내림차순으로 정렬하기

SELECT *, price*quantity AS amount FROM sample34 ORDER BY amount DESC;

함수

연산자 외에 함수를 사용해 연살할 수도 있습니다. 함수는 다음과 같은 문법으로 표기합니다.
Syntax 함수

함수명(인수1, 인수2...)

함수도 연산자도 표기 방법이 다를 뿐, 같은 것이다!

ROUND 함수

ROUND로 반올림하기

Syntax ROUND

SELECT*FROM sample341;

  • ROUND로 반올림
SELECT amount, ROUND(amount) FROM sample341;

  • 반올림 자릿수 지정
    SELECT amount, ROUND(amount,1) FROM sample341;
    (ROUND 함수의 두 번째 인수를 저장해, 소수점 둘째자리에서 반올림)

  • 10 단위를 반올림하기
    SELECT amount, ROUND(amount, -2) FROM sample341;

문자열 결합

문자열 결합이란 다음과 같이 문자열 데이터를 결합하는 연산입니다.

  • 문자열 결합사례
    'ABC' || '1234' → 'ABC1234'

    '+' 연산자, || 연산자, CONCAT 함수로 문자열을 결합할 수 있다.

-- 				      quantity + unit : SQL Server
-- 				      quantity || unit : Oracle, DB2, PostgreSQL
SELECT quantity, unit, concat(quantity, unit)	-- mysql
FROM SAMPLE35
;

SUBSTRING 함수

[원본]

SELECT substring(job,2,4)	--2번째 부터 4개를 출력
FROM EMP
;

TRIM 함수

TRIM으로 스페이스 제거하기

SELECT trim('		ABC');

CHARACTER_LENGTH 함수

문자열의 길이를 계산해 돌려주는 함수이다.

SELECT job,
	substring(job,2,4),
	character_length(job)
from emp;

날짜 연산

Syntax 날짜연산
CURRENT_TIMESTAMP CURRENT_DATE INTERVAL

-- CURRENT_TIMESTAMP로 시스템 날짜 확인
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;

날짜 서식

기존날짜에서 date_format을 이용해 형식을 변경할 수 있다.

select hiredate,
 date_format(hiredate,"%y/%m/%d"),
 date_format(hiredate,"%Y/%M/%D"),
 date_format(hiredate,"%Y.%M.%D")
from emp;

날짜의 덧셈과 뺄셈

날짜의 덧셈

CURRENT_DATE는 시스템의 날짜만 확인하는 함수 INTERVAL 1 DAY 라고 하면 '1일 후'라는 의미의 기간형 상수이다.

select hiredate,
 date_format(hiredate,"%y/%m/%d"),
 date_format(hiredate,"%Y/%M/%D"),
 hiredate + interval 1 day,
 CURRENT_DATE + INTERVAL 1 DAY
from emp;

날짜의 뺄셈

날짜시간형 데이터 간에 뺄셈도 가능하다.
Oracle에서는 '2014-02-25' - '2014-01-01'이라고 한다면 두 날짜 사이에 차이가 얼마나 발생하는지 계산할 수 있다.
한편 MySQL에서는 DATEDIFF('2014-02-28', '2014-01-01')로 계산할 수 있다.

CASE문으로 데이터 변환하기

CASE문

Syntax CASE문

CASE WHEN 조건식1 THEN 식1
[ WHEN 조건시2 THEN 식2 ...]
[ ELSE 식3 ]
END
select a,
	case 
		WHEN a = 1 THEN '남자'
		WHEN a = 2 THEN '여자'
        ELSE '미지정'
	END
from sample37;


case WHEN ···(중략) 너무 길어서 보기 싫으니까 AS로 별명을 지정해주자

select a,
	case 
		WHEN a = 1 THEN '남자'
		WHEN a = 2 THEN '여자'
        ELSE '미지정'
	END AS '성별'
from sample37;

다른방법으로 작성한 case

select a,
	-- case 
		-- WHEN a = 1 THEN '남자'
		-- WHEN a = 2 THEN '여자'
        -- ELSE '미지정'
	-- END AS '성별'
    case a
		when 1 then '남자'
        when 2 then '남자'
        when null than '데이터 없음' -- NULL인데 이게 출력안됨.
        else '미지정'
	end as '성별'
from sample37;


이 방법은 null값이 있다면 문제가 발생할수 있기 때문에 처음 방식을 사용하는 것이 좋다.

다시 처음 방식으로 확인해보면

select a,
	case 
		WHEN a = 1 THEN '남자'
		WHEN a = 2 THEN '여자'
        WHEN a is null THEN '데이터 없음'
        ELSE '미지정'
	END AS '성별'
from sample37;

  • COALESCE
    여러개의 인수를 지정할 수 있다.
    주어진 인수 가운데 NULL이 아닌 값에 대해서는 가장 먼저 지정된 인수의 값을 반환한다. 아래 예문은 a가 NULL이면 a가 NULL이 아니면 a값을 그대로 출력하고, 그렇지 않으면 (a가 NULL이면) 0을 출력한다.
SELECT a, COALESCE(a,0) FROM sample37;
  • ifnull(변경할 위치, 대체할 숫자)

    comm의 null값을 0으로 대체함

아래와 같이 null값을 제거하여 계산할 수 있다.

select *, ifnull(comm,0), sal+comm, sal+ifnull(comm,0)
from emp;

profile
jjinny_0609
뉴비 개발자 입니다. velog 주소 : https://velog.io/@jjinny_0609 Github 주소 :
이전 포스트

SQL_SELECT 연습문제

다음 포스트

(SQL 첫걸음) 데이터 베이스 #6

post-custom-banner

0개의 댓글