2월에 있을 자격시험을 대비하여, 하루 2~3문제는 꼭 풀기로 했다.
매일매일 푼 내용은 github에 정리하여 넣고, 문제풀다가 기억이 안나는 개념 몰랐던 개념, 헤깔리던 개념관련해서 나의 생각과 같이 블로그에 기록해보고자 한다🥕
🔍 CONCAT 함수
SQL를 사용하면 정말 많이 사용하게되는 함수이다.
- 둘 이상의 문자열을 입력한 순서대로 합쳐서 반환해주는 함수
- 여러 문자열 혹은 컬럼 값을 합쳐서 가져와야하는 경우에 자주 사용
📌 참고 문제
-
풀이
SELECT concat("/home/grep/src/" ,BOARD_ID, "/",FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH FROM USED_GOODS_FILE
WHERE USED_GOODS_FILE.BOARD_ID = (SELECT USED_GOODS_BOARD.BOARD_ID FROM USED_GOODS_BOARD ORDER BY VIEWS DESC LIMIT 1)
ORDER BY FILE_ID DESC✂️ 사용법
- 생각보다 간단하다. 문자열을 쭈욱~ 나열하면 합쳐진 문자열 값을 반환한다.
CONCAT(문자열1, 문자열2 , 문자열3 ...)- 말보단 코드 예시를 보면 단번에 이해가 된다.
-- BOARD_ID,FILE_ID , FILE_NAME, FILE_EXT : 문자열로 이루어진 칼럼의 이름
select concat("/home" , "/grep/src/" ,BOARD_ID , "/" , FILE_ID , FILE_NAME , FILE_EXT )- 위와 같이 합칠 컬럼을 같이 써주면 된다.
- 문자열 “ “ 빈공간이 있다면 띄어씌기로 합쳐진다.
간단한 사용법을 알았으니, 이제 유의사항에 대해알아보자
⚠️ Concat 유의사항
1. 데이터 유형 호환성
- concat 함수는 문자열을 결합하는 함수 라서, 모든 인수가 문자열로 변환될 수 있는 유형이여야한다.
- 자동으로 데이터가 문자열로 변환되기 때문에 명확히 인지하고 사용하는게 중요!
2.NULL 값 처리
들어가는 인수중에 하나라도 NULL이 있다면, concat함수는 NULL를 반환한다.
-
null값이랑 합친 경우
-
null값이 아닌 경우
3. 문자열길이
- 데이터베이스 시스템에 따라 concat 함수로 결합할 수 있는 문자열의 길이에 제한이 있을 수 있다.
- 길이를 초과하는 경우 에러가 발생
- 물론 최대길이를 바꾸는 방법도 가능 MySQL기준
group_concat_max_len시스템 변수에 의해 길이가 제한되기에,(기본값 1024) 이 변수를 변경하면 된다.-
변경 쿼리
SET GLOBAL group_concat_max_len = <원하는 값>; -
변경된점 확인 쿼리
SELECT @@group_concat_max_len;
-
4. 성능이슈
- concat 함수는 문자열을 결합하기 위해 내부적으로 작업을 수행,
- 큰 규모의 데이터나 반복적인 작업에서는 성능에 영향을 줌
- 이것은 보다 고민하여 해결이 필요
- 인덱스를 사용하거나,
- 쿼리를 최적화하거나
- 캐싱을 만들어 사용하거나 등등으로 해결가능
🔍 서브쿼리
문제를 풀다보니, Subquery returns more than 1 row 라는 에러문구를 마주했다.
찾아보니 보통 서브쿼리가 단일값만 반환해야되는데 여러 개의 행을 반환하는 경우 발생하는 오류!
처음 알게된 것이기에 이번에 서브쿼리가 뭔지!, 왜 이런 에러가 나는지!, 그리고 해결책은 뭔지 알아보고자 한다!
서브쿼리란?
하나의 SQL 문 안에 포함된 또 다른 SQL 문!
SQL의 강력한 기능 중 하나이며, 데이터 검색과 조작을 더욱 유연하고 효과적으로 수행할 수 있도록 도와준다.
- 주로 사용할 때
- 데이터 필터링
- 데이터 검색
- 데이터 연산
🧐 그럼 서브쿼리가 단일값만 반환해야되는 이유??
1. 비교 연산
- 서브쿼리의 결과를 비교 연산자와 함께 사용하는 경우가 있는데, 이경우 1:1 대응으로 되야되기때문에 단일값을 반환해야한다.
- 예
WHERE table1.column1 = table2.column1;- 보통 이런식으로 칼럼과 칼럼 1:1 대응으로 이루어진다.(약간 변수와 변수 느낌)
WHERE column1 = (SELECT MAX(column1) FROM table2);- 서브쿼리의 경우 어떠한 값으로 고정이 되기때문에 단일값으로 만 반환되야 1:1 대응이 된다.(약간 변수와 상수 느낌)
2. EXISTS 절
-
개념
- EXISTS 절은 서브쿼리의 결과가 있으면 참 없으면 거짓을 반환하는 함수이다. -> 애초에 단일값만 나옴.
- 정말 심플하게 반환된 행이 있는지 없는지만 판단한다.
-
사용하는 상황
- 일반적으로 SELECT까지 가지 않기에 IN에 비해 속도나 성능은 좋다.
- 약간 사용자의 주문내역 확인하고 싶을때 빠르게 가능
- 보통 WHERE이랑 같이 사용하며, 조건이 만족하면 그때 메인쿼리를 실행시킬수 있기에 좋다. (EXISTS 자체가 거의 서브쿼리라는 말)
-
예
SELECT column1, column2 FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);SELECT column1, column2 FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.column3 > 100);
3. 칼럼 값으로 사용
-
서브쿼리의 결과를 SELECT 문의 칼럼값으로 사용할 때, 단일값 반환해야됨!(내가 맞닿들였던 문제!)
-
WHERE절의 서브쿼리 말고도, SELECT에 들어갈 하나의 값도 가능하다.
-
예시
SELECT column1, column2, (SELECT COUNT(*) FROM table2 WHERE table2.column1 = table1.column1) AS count_value FROM table1;결론적으로, 서브쿼리가 WHERE에 종속적인 경우, WHERE은 결국 결과를 내야되기에 대부분의 서브쿼리가 단일값으로 해야되는 것, 그렇지않으면
Subquery returns more than 1 row에러가 남을 도출했다.
(여기서, 무조건 서브쿼리는 단일값을 반환해야된다! 가 아니라 위와 같은 상황에서는 단일값을 반환해야된다는 말이다.)
🔑 여러개의 행 해결책
1. 집계함수 사용 (Group By)
- Count, Sum등 단일값을 반환해주는 집계함수를 사용해주면 해결하기 쉽다.
- 여기서 유의사항으로는 보통 집계함수는 집계를 낼 리스트가 필요하기 때문에, Group By가 사용된다.
- 물론 집계 함수가 group by 와 사용되지 않고, 테이블 전체에 적용할 수도 있습니다.
- 하지만 이 경우에는 테이블 전체를 묵시적으로 group by 대상으로 생각하고 집계 함수를 적용함으로 Select 절에는 집계 함수 외에는 다른 컬럼이 올 수가 없습니다.
SELECT column1, column2, (SELECT AVG(column3) FROM table2) AS avg_value FROM table1;
2. IN 연산자 사용
-
여러 값이 반환되더라도 IN은 그 값들이 포함된 것으로 처리해주기때문에, 단일 값이 아닌 경우 처리가능하다.
- OR연산으로 취급하는 것!
-
나도 문제는 IN을 사용하여 풀었다.
-
예시
- 참고링크 : w3school
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
📌 참고 문제
문제 풀이
-- 코드를 입력하세요
-- 조건: 게시물 3건 이상
-- 조회: 사용자 ID, 닉네임, 전체주소(시 ,도로명, 상세주소), 전화번호 (xxx-xxxx-xxxx)
-- 정렬 : USER_ID 내림차순
SELECT USER_ID , NICKNAME, CONCAT(CITY, " ",STREET_ADDRESS1," ", STREET_ADDRESS2) AS 전체주소 , CONCAT(SUBSTRING(TLNO,1,3) ,'-', SUBSTRING(TLNO,4,4) ,'-',SUBSTRING(TLNO,8)) AS 전화번호
FROM USED_GOODS_USER
WHERE USED_GOODS_USER.USER_ID IN
(
SELECT WRITER_ID
FROM USED_GOODS_BOARD
GROUP BY WRITER_ID
HAVING COUNT(USED_GOODS_BOARD.WRITER_ID) >= 3
)
ORDER BY USER_ID DESC작가 chandlervid85 이미지 출처 Freepik
