SQL

1. CONCAT():  문자열을 결합하여 하나의 문자열로 만드는 함수

SELECT CONCAT(first_name, ' ', last_name, ' (', email, ')') AS user_info FROM users;

 

2. SUBSTR() : 문자열에서 일부분을 추출하여 반환하는 함수 

SUBSTR(문자열, 시작 위치, 길이) 
/* 시작위치는 1부터 시작한다. 길이를 생략하는 경우 마지막까지 찾는다*/

SELECT SUBSTR(name, 1, 3) FROM users;

3.INSTR() : 문자열 내에서 특정 문자열이 나타나는 위치를 찾아주는 함수 ( 없는 경우 0이 반환된다)

INSTR(원본 문자열, 찾을 문자열 , 시작 위치 , 발견 위치)
/* 원본 문자열 : 찾을 문자열
시작 위치: 검색을 시작할 위치를 지정한다. (생략시 1부터 시작) 
발견위치 : 찾은 문자열의 발견 위치. (생략시에는 제일 처음 발견된 위치가 출력된다.)
*/

SELECT INSTR('Hello, World!', 'l', 3) AS position; -- 3번째 위치부터 찾음
SELECT INSTR('Hello, World!', 'l', 3, 2) AS position; -- 3번째 위치부터 두 번째로 발견한 위치를 찾음

 

 

4. sql에 조건문을 사용하는 방법

SELECT IF(조건, 참값, 거짓값) AS 결과;

--학생의 수업이 5개 이상인 경우 10% 할인율을, 3개 이상인 경우 5% 할인율을, 그 외의 경우는 할인율을 0으로 설정하는 경우.
SELECT 
    name, 
    num_classes, 
    IF(num_classes >= 5, 0.1, IF(num_classes >= 3, 0.05, 0)) AS discount_rate 
FROM students;


-- 미성년과 성년을 구분하는 
SELECT name, age, 
       IF(age >= 18, '성인', '미성년자') AS status
FROM users;

SELECT
    column1,
    column2,
    ...,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE result /* 생략 가능, 생략시에는 else인 경우 null반환)  */
    END
FROM
    table_name;

--학생의 수업이 5개 이상인 경우 10% 할인율을, 3개 이상인 경우 5% 할인율을, 그 외의 경우는 할인율을 0으로 설정하는 경우.
SELECT 
    name, 
    num_classes, 
    CASE 
        WHEN num_classes >= 5 THEN 0.1
        WHEN num_classes >= 3 THEN 0.05
        ELSE 0
    END AS discount_rate
FROM students;


-- 미성년과 성년을 구분하는 
SELECT name, age, 
       CASE 
           WHEN age >= 18 THEN '성인' 
           ELSE '미성년자' 
       END AS status
FROM users;

5. REGEXP(), RLIKE() :  정규식을 활용해서 확인하는 경우

-- 특정 단어를 포함하는 컬럼을 찾기
SELECT *
FROM my_table
WHERE column_name REGEXP 'specific_word';

-- 특정 패턴과 일치하는 행 찾기
SELECT *
FROM my_table
WHERE column_name RLIKE '^pattern.*$';

-- 특정 패턴과 일치하는 행 (주민번호)
SELECT * 
FROM table_name 
WHERE column_name REGEXP '^[0-9]{6}-?[0-9]{7}$'; 
/* ^시작, [0-9] 0~9안의 숫자가 {6} 6번 반복하고 -? -가 0번이나 1번 나올수 있다. $는 문자열의 끝을 의미한다.  */

-- 특정 패턴과 일치하는 행 (전화번호)
SELECT * 
FROM table_name 
WHERE column_name REGEXP '^[0-9]{2,3}-?[0-9]{3,4}-?[0-9]{4}$';
/*^시작, [0-9] 0~9안의 숫자가 {2,3} 2~3번 반복하고 -? -가 0번이나 1번 나올수 있다. $는 문자열의 끝을 의미한다.*/

6. SQL조인 시에 일치하지 않는 데이터도 0으로 출력하고 싶은 경우가 있다 이경우에는 left outer join을 이용하여 해결할 수 있다.

SELECT o.id, o.customer_name, o.order_date, COUNT(oi.id)
FROM orders AS o
LEFT OUTER JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY o.id, o.customer_name, o.order_date;

 

 

LEFT JOIN (or LEFT OUTER JOIN)

왼쪽 테이블의 모든 레코드와 오른쪽 테이블에서 일치하는 레코드를 선택하여 조인한다.

만약 오른쪽 테이블에서 일치하는 레코드가 없다면 NULL 값을 반화한다. 

 

RIGHT JOIN (or RIGHT OUTER JOIN)

오른쪽 테이블의 모든 레코드와 왼쪽 테이블에서 일치하는 레코드를 선택하여 조인한다.

만약 왼쪽 테이블에서 일치하는 레코드가 없다면 NULL 값을 반화한다. 

 

FULL OUTER JOIN (or FULL JOIN, or OUTER JOIN)

왼쪽 테이블과 오른쪽 테이블에서 일치하는 모든 레코드를 선택하여 조인한다.

만약 왼쪽 테이블이나 오른쪽 테이블에서 일치하는 레코드가 없다면  NULL 값을 반화한다. 

 

+)  만약 null일때 0으로 출력하고 싶은 경우

IFNULL(확인할 값, value): 확인할 값이 null이면 value를, 확인할 값이 null이 아니면 해당 값이 출력된다.

COALESCE(value1, value2, value3):  value1, value2, value3 중에서 NULL이 아닌 값이 있는 첫 번째 값을 반환하며, 만약 모든 값이 NULL인 경우에는 NULL을 반환한다.

-- COALESCE
SELECT COALESCE(NULL, 0, 1); -- 0

-- IFNULL
SELECT IFNULL(NULL, 0); -- 0

 

참고 ,출처 : 

https://blogger.pe.kr/231

https://leejinseop.tistory.com/19