SQL 정리

민찬홍·2023년 8월 10일
mysqlsql

DB

목록 보기
1/4

이번에 보는 시험중에 SQL코테문제가 2문제정도 나와서 정보처리기사 이후에 제대로 본 적 없는 SQL문을 정리해본다.

IF

  • select, where 절에서 사용 가능
SELECT IF(10>5, '크다','작다') AS result;

Order By

  • Order by 뒤에 우선순위가 있는 열을 순서대로 적는다.
  • ORDER BY DESC (역순정렬 = 내림차순)
  • ORDER BY ASC(오름차순)

LIKE

  • where 절과 함께 특정 패턴을 검색할 때 사용
SELECT *
FROM Student
WHERE Student_ID like 'a%';

LIKE 'a%'   --a로 시작되는 모든 것
LIKE 'a_%_%' -- a로 시작되고 최소 3이상의 길이를 가진 것
LIKE '_a%' -- 두번째 자리에 a가 들어가는 모든 것

<>

-sql 문에서 <>는 같지않음을 나타낸다. 

WHERE INTAKE_CONDITION <> 'Aged'

위의 sql문의 경우 INTAKE_CONDITION중에서 'Aged'와 같지 않은 것들을 반환한다. 따라서 'Aged'를 제외하고 반환하는 것과 같다.

IN

  • where절 내 여러 값을 설정하고자 할 때 사용
  • 연산 속도가 상대적으로 빠름
  • or 연산과 유사한 효과
  • in 연산자에 입력된 값 중에서 하나라도 일치하는 것이 있으면 리스트에 조회된다.
SELECT * 
FROM Customers
WHERE country IN ('UK','Korea') -- customer중 country가 uk이거나 korea인것을 다 뽑아냄
  • 문제: 동물 보호소에 들어온 동물 중 이름이 'Lucy,Ella,Pickle,Rogan,Sabrina,Mitty'인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL문을 작성하시오.
SELECT ANIMAL_ID,NAME,SEX_UPON_INTAKE
FROM ANIMAL_INS ani
WHERE NAME IN ('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty');

Between

  • where 절 내 검색 조건으로 범위를 지정하고자 할 때 사용
  • between과 and 사이에 들어가는 값은 ~이상 ~이하이다.
SELECT * 
FROM products
WHERE price BETWEEN 10 and 20;

Null의 처리

  • ifnull의 사용
SELECT ANIMAL_TYPE, IFNULL(NAME,'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS

-- ANIMAL_INS 테이블의 각 행에서 동물의 유형, 이름(이름이 없는 경우 'No name'으로 표시), 그리고 수용 시의 성별 정보를 가져오는 역할
  • coalesce사용
    coalesce는 정의된 열 중 null이 아닌 첫번째 값을 화면에 출력.
    coalesce('a','b')인 경우 가장 왼쪽이 null이 아니다. 그러므로 a가 'null',출력된다.
    coalesce('null','b','c')인 경우 가장 왼쪽이 null이다. 이 경우 b가 출력된다.
SELECT ANIMAL_TYPE, coalesce(NAME,'No name') AS NAME, SEX_UPON_INTAKE FROM ANIMAL_INS

Case 문

CASE
WHEN 조건 1 THEN '조건1 반환값'
WHEN 조건 2 THEN '조건2 반환값'
ELSE '충족되는 조건 없을때 반환값'
END

WHEN.. THEN은 쌍으로 같이 다녀야한다.

SELECT,WHERE,ORDER BY 절에서 사용 가능하고 보통 SELECT절에서 쓰인다. ELSE를 생략하면 결과 값이 NULL이 나오므로 주의

SELECT
	seq,
    CASE
    	WHEN(u.seq BETWEEN 1 AND 3) THEN
        	CASE
            	WHEN(u.enabled IS TRUE) THEN 'A+'
                ELSE 'A0'
                
            END
        WHEN(u.seq BETWEEN 4 AND 6) THEN
        	CASE
            	WHEN(u.enabled IS TRUE) THEN 'B+'
                ELSE 'B0'
            END
            
        ELSE 'C+'
    END AS case_result
FROM 'user' u
SELECT(
    CASE
        WHEN PRICE < 10000 THEN 0
        ELSE TRUNCATE(PRICE,-4)
        END)  AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

Limit

  • 상위 데이터 몇개만 출력할 때
SELECT NAME 
FROM ANIMAL_INS 
ORDER BY DATETIME ASC LIMIT 1; -- 상위 한개만 출력

SELECT NAME 
FROM ANIMAL_INS 
ORDER BY DATETIME ASC LIMIT 1,10; -- 두번째 데이터부터 10개의 데이터를 가져옴(인덱스 0부터 시작)

두번째 데이터부터 시작한다 해도 사실상 처음데이터부터 10개를 출력하는 것이다. 따라서 범위의 마지막인 10은 11번째 데이터가 출력될 것이다.

-- 조건식이 있는 경우
SELECT 열명
FROM 테이블명
WHERE 조건식
LIMIT 행수

-- 조건식이 없는 경우
SELECT 열명
FROM 테이블명
LIMIT 행수

-- 지정한 숫자 개수 리턴
SELECT * 
FROM 
LIMIT 0,3; -- 0번부터 3개

Group By

  • 집계함수와 함께 사용 되어, 결과를 지정한 컬럼에 따라 그룹으로 묶고자 할때 사용
SELECT COUNT(id)
FROM CUSTOMERS
GROUP BY country -- 각 도시에 사는 사람이 몇명인지

MIN(), MAX(), AVG(), SUM(), ROUND()

  • 집계함수
  • SELECT 문에서 사용
SELECT AVG(price) FROM products;
SELECT SUM(price) FROM products;
  • 문제: 평균 잔고가 700 이상인 지점의 이름과 평균 잔고를 구하라
SELECT branch_name, avg(balance)
FROM account
GROUP BY branch_name
HAVING AVG(balance) >= 700;
  • ROUND는 반올림에 사용
  • ROUND(컬럼,자릿수): 해당 컬럼 값의 자리수 번째 값을 기준으로 반올림

Union

  • SELECT의 칼럼 리스트를 기준으로 두 개 이상의 질의 결과를 하나의 테이블로 합치고자 할 때 사용
  • 기본적으로 중복값을 제거한다.
  • 중복값을 포함하고 싶은 경우 UNION ALL을 사용한다.
SELECT * 
FROM customers
UNION
SELECT city FROM orders
ORDER BY city;

COUNT

count만 따로 다루어보도록 하겠다.

  • COUNT : null을 포함하는경우, 그렇지않은 경우가 있다.

  • 하지만 중복은 포함해서 계산한다. 따라서 DISTINCT를 통해 중복을 제거해주어야 한다.

  • 전체 행의 개수

SELECT COUNT(*) FROM TABLE_NAME

: 이 결과에는 NULL이 포함되어서 세어진다.

  • NULL을 포함하지 않는 행의 개수
SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME

: 컬럼의 이름을 사용하면 그 컬럼의 NULL인 값을 제외한 개수가 반환된다.

  • 중복 값을 제외한 행의 개수
SELECT COUNT(DISTINCT COLUMN_NAME) FROM TABLE_NAME
  • 값을 치환하여 구하기
SELECT COUNT(IFNULL(COLUMN_NAME,0)) FROM TABLE_NAME

:NULL 값을 0으로 계산 -> 집계 함수에 반영됨

날짜 관련

  • DATE_FORMAT()
    : DATE_FORMAT(날짜,형식): 날짜를 지정한 형식으로 출력
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS 컬럼명 FROM 테이블명
-- 2022-12-29 출력

%y를 쓰면 22 이렇게 출력되고, 대문자로 %Y를 써야 2022 이렇게 출력된다.

  • YEAR()
    : 날짜,시간과 관련된 함수 중 하나이다.
SELECT YEAR(NOW()) FROM 테이블명
-- 2022 출력

NOW()의 데이터 형식
2022-12-29 오전 8:29:11

  • HOUR()
    : 시간만 출력한다.
SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
HAVING HOUR >=9 AND HOUR<=19
ORDER BY HOUR;
  • SUBSTR
    : 지정된 위치와 길이의 부분 문자열을 추출하는 함수
WHERE SUBSTR(A.CREATED_DATE,1,7) = '2022-10'

-- CREATED_DATE의 데이터에서 첫번째부터 7번째자리를 추출해 연도와 월 정보를 가져옴

YEAR()이외에도 MONTH(),DAYOFMONTH()등 날짜, 시간과 관련된 여러 함수가 존재한다.

앞으로 모르는 부분들이 나올때마다 계속해서 정리해 갈 것이다.

profile
민찬홍
백엔드 개발자를 꿈꿉니다
다음 포스트

SQL 정리(2)

0개의 댓글