[MySQL] 코딩 테스트 기본 문법 정리

 

* 코딩테스트 준비를 위해 앞서 문법 정리와 더불어 실습에 쓰일 ddl 스크립트를 만들었습니다. 제공된 스크립트를 통해 직접 query를 작성해보시면 실력 향상에 큰 도움이 될 것입니다. 

 

https://sqltest.net/#

SQL Test

해당 사이트에 방문하시면 따로 mysql 설치 없이도 ddl 스크립트를 이용해 보실 수 있습니다. 

 

1. ddl scipt

CREATE TABLE customer
   (
      customer_id  INT UNSIGNED PRIMARY KEY,
      acnumber VARCHAR(20),
      bank_id VARCHAR(20),
      balance INT(7),
      aod DATE,
      atype VARCHAR(10)
    );



INSERT INTO customer VALUES(1, 'A00001','KB',659100000,'2022-12-15','Saving');
INSERT INTO customer VALUES(2, 'A00001','SHINHAN',588000000,'2022-04-13','Saving');
INSERT INTO customer VALUES(3, 'A00001','KB',155400000,'2022-04-09','Saving');
INSERT INTO customer VALUES(4, 'A00001','KAKAO',854400000,'2022-06-17','Saving');
INSERT INTO customer VALUES(5, 'A00001','NH',9103000,'2022-12-15','Saving');
INSERT INTO customer VALUES(6, 'A00001','KAKAO',1005140000,'2022-07-11','Saving');
INSERT INTO customer VALUES(7, 'A00001','WOORI',NULL,'2022-12-31','Saving');
INSERT INTO customer VALUES(8, 'A00001','NH',344004500,'2022-10-15','Saving');
INSERT INTO customer VALUES(9, 'A00001','IBK',454800000,'2022-05-21','Saving');
INSERT INTO customer VALUES(10, 'A00001','WOORI',5771110,'2022-11-08','Saving');

 

(1) 상위 n 개만 조회 (LIMIT)

SELECT * FROM 테이블명 LIMIT n;

ex) customer 테이블 내에서 상위 3개의 튜플을 조회하시오.

SELECT *
FROM customer
LIMIT 3;

 

(2) 컬럼명 순으로 조회 (ORDER BY) 

SELECT * FROM 테이블명 ORDER BY 컬럼명 ASC|DESC;

ex) 모든 은행을 통틀어 보유 금액이 가장 많은 3명의 고객 id와 잔고를 조회하시오. 단, 보유 금액이 많은 순으로 보여주세요.

SELECT customer_id, balance
FROM customer
ORDER BY balance DESC
LIMIT 3;

* ORDER BY 절에는 반드시 SELECT 절에서 나열한 컬럼만 등장할 수 있다. 

* ORDER BY 절에는 컬럼명 대신 SELECT 절에서 해당 컬럼이 등장한 순서 번호가 들어올 수 있다.

SELECT customer_id, balance
FROM customer
ORDER BY 2 DESC
LIMIT 3;

다음과 같이 작성하여도 같은 결과값이 출력된다.

 

(3) 가장 작은/큰(MIN( ), MAX( ))

SELECT MIN|MAX(컬럼명) FROM 테이블명;

ex) 모든 은행을 통틀어 보유 금액이 가장 적은 고객의 잔고를 조회하시오. 

SELECT MIN(balance)
FROM customer;

 

(4) 중복 제거하여 컬럼 (DISTINCT( ))

SELECT DISTINCT 컬럼명 FROM 테이블명;

ex) 고객들이 계좌를 개설한 은행명를 조회하시오. (단 중복을 제거해야 합니다.) 

SELECT DISTINCT(bank_id)
FROM customer;

 

(5) 특정 문자열을 포함하는 레코드 조회 (LIKE)

-- 특정 문자열로 시작하는 경우 
SELECT * FROM 테이블명 WHERE 컬럼명 LIKE '특정문자열%';

-- 특정 문자열로 제외하는 경우 
SELECT * FROM 테이블명 WHERE 컬럼명 NOT LIKE '특정문자열%';

-- 특정 문자열로 끝나는 경우
SELECT * FROM 테이블명 WHERE 컬럼명 LIKE '%특정문자열';

-- 특정 문자열이 중간에 있는 경우 
SELECT * FROM 테이블명 WHERE 컬럼명 LIKE '%특정문자열%';

-- 한으로 시작하여 민으로 끝나는 세글자일 경우 (_ 사용)
SELECT * FROM 테이블명 WHERE 컬럼명 LIKE '한_민';

ex) '블랙 원피스'를 포함하는 레코드를 싶은 경우

SELECT * FROM 테이블명 WHERE 컬럼명 LIKE '블랙%'

SELECT * FROM 테이블명 WHERE 컬럼명 LIKE '블랙____' ( 띄어쓰기 포함하여 '_' 4개)

 

(6) 컬럼 개수 조회 (COUNT( ))

SELECT COUNT(컬럼명) FROM 테이블명;

 

(7) 그룹별 개수 조회 (GROUP BY, COUNT( ))

COUNT(*) : NULL 값을 '포함한' 전체 행의 개수 

COUNT(컬럼명) : NULL 값을 포함하지 않는 행의 개수 

SELECT COUNT(컬럼명) FROM 테이블명 GROUP BY 컬럼명;

ex) 은행별 전체 계좌 금액 합계를 조회하시오 

SELECT bank_id, sum(balance) as 'total_balance'
FROM customer
GROUP BY bank_id;

* SELECT에서 나열되는 컬럼들은 집계함수를 적용하지 않았다면, 반드시 GROUP BY 절에 나열한다.

SELECT 컬럼1, 컬럼2, COUNT(컬럼3)

FROM 테이블명

GROUP BY 컬럼1, 컬럼2; 

 

* GROUP BY 절에는 컬럼명 대신 SELECT 문에서의 컬럼 순서 번호가 들어올 수 있다.

아래 쿼리에서 bank_id가 SELECT 문에서 등장하는 순서는 첫번째다. 따라서 GROUP BY 절에서bank_id 대신 1을 사용할 수 있다. (테이블에서의 순서 번호가 아니라 쿼리문에서의 순서대로 번호를 가진다.) 

SELECT bank_id, sum(balance) as 'total_balance'
FROM customer
GROUP BY 1;

* where절과 having절의 차이

where : 집계되기 전에 필터링  => 집계 함수를 적용한 컬럼이 WHERE 절에 올 수 없음

having : 집계된 후에 필터링  => 집계함수를 적용한 컬럼만 HAVING 절에 올 수 있음 

SELECT 컬럼1, 컬럼2, SUM(컬럼3) FROM 테이블명

WHERE 조건 1

GROUP BY 컬럼1, 컬럼2 HAVING조건 2;

 

(8) 문자열 길이 조회 (LENTH( ), CHAR_LENGTH( ))

-- byte 단위로 가져오므로 '한글'로 된 문자열 길이 인식 불가능
SELECT LENTH(컬럼명) FROM 테이블명;
-- 한글 길이 인식 가능
SELECT CHAR_LENGTH(컬럼명) FROM 테이블명;

틀린 결과

정답인 결과

(9) A와 B 사이 결과 조회 (BETWEEN A AND B)

SELECT * FROM 테이블명 WHERE 컬럼명 BETWEEN A AND B;

 

(10) 두 쿼리 결과를 하나로 합칠 때 (UNION)

* UNION ALL: UNION 시 중복된 ROW들은 삭제되지만 UNION ALL은 중복된 ROW를 포함한다. 

SELECT 컬럼명1, 컬럼명2 FROM 테이블명_1 
UNION 
SELECT 컬럼명1, 컬럼명2 FROM 테이블명_2;

SELECT 컬럼명1, 컬럼명2 FROM 테이블명_1 
UNION ALL
SELECT 컬럼명1, 컬럼명2 FROM 테이블명_2;

(11) 년도, 시간, 달, 일 계산 (YEAR, HOUR, MONTH, DAY)

-- 시간 조회
SELECT HOUR(DATETIME) FROM 테이블명;
-- 연도 조회
SELECT YEAR(DATETIME) FROM 테이블명;

 

(12) 컬럼값이 NULL인 경우 0 처리

-- 컬럼명이 NULL 값인 경우 0 출력
SELECT IFNULL(컬럼명, 0) 
FROM 테이블명;

 

(13) 절대값 처리 (ABS( ))

SELECT ABS(컬럼명) FROM 테이블명;

 

(14) 올림/버림/반올림 (CEIL( ), FLOOR( ), ROUND( ))

-- 소수점 첫째 자리에서 올림
-- 2
SELECT CEIL(1.5, 1);
-- 소수점 첫째 자리에서 내림
-- 1
SELECT FLOOR(1.5, 1);
-- 소수점 첫째 자리에서 반올림
-- 2
SELECT ROUND(1.5, 1);

 

(15) 제곱 (POW( ))

-- 10**2 = 100
SELECT POW(10, 2);

 

(16) 날짜, 시간을 지정 형식으로 출력 (DATE_FORMAT( ))

* 대소문자 구별

SELECT DATE_FORMAT(DATETIME, '%y-%m-%d');

 

(17) NULL이 아닌 첫번째 값 반환 (COALESCE ( ))

SELECT COALESCE(컬럼명1, 컬럼명1이 NULL인 경우 대체할 값)
FROM 테이블명;

 

* 컬럼명을 alias 로 명명하는 경우, 특수 문자나 띄어 쓰기를 포함된 단어는 올 수 없다.

* alias는 where 절에서 사용할 수 없다. group by, having, order by 절에서만 사용 가능