[MySQL] CONCAT / SUBSTRING / REPLACE / DINSTINCT / LIMIT / ORDER BY

17 Nov 2021 - 1 minute read

CONCAT

---

• 문자열 합치기

    CONCAT(x, y);
  
    CONCAT(column_name, another_column_name);
  
    CONCAT(column_name, 'text', another_column_name, 'another text');
  
    SELECT CONCAT (column_name1, column_name2) 
    AS alias FROM table_name; 
  
    SELECT column_name1 AS alias1, column_name2 AS alias2, 
    CONCAT(column_name1, column_name2) AS alias3 FROM table_name;
  

• CONCAT_WS()

  ws : with separator

  
    SELECT CONCAT_WS('separator', expression1, expression2, expression3);
  
    SELECT CONCAT_WS('separator', column_name1, column_name2, column_name3) FROM table_name;
  
  

  • ex)

      create table concatTest(
          first	char(10),
          second	char(10),
          third	char(10)
      );
    
      insert into concatTest
      values
      ('a', 'b', 'c'),
      ('1', '2', '3');
    
      select concat_ws(' /', first, second, third) from concatTest;
    

  • 실행결과

      # concat_ws(' /', first, second, third)
      'a /b /c'
      '1 /2 /3'
    

SUBSTRING

---

• 문자열 자르기

    SELECT SUBSTRING('문자열', num1, num2);
  
    SELECT SUBSTR('문자열', num1, num2);
  

  • ex)

      SELECT SUBSTRING('Hello World', 3, 8); # llo Worl
      SELECT SUBSTRING('Hello World', 7); # World
      SELECT SUBSTRING('Hello World', -3); # rld
    

• Substring + Concat

  • SELECT CONCAT(
        SUBSTRING(column_name, num1, num2),
        'concat문자열'
    )
    FROM table_name;
    

REPLACE

---

• 문자열 바꾸기

    SELECT REPLACE ('문자열', '바꾸고 싶은 부분', '바꿀 내용');
  

  • ex)

      SELECT REPLACE ('Hello World', 'Hello', '!@#$'); # !@#$ World
      SELECT REPLACE ('Hello World', 'o', '*'); # Hell* W*rld
      SELECT REPLACE ('Hello World', ' ', ' ! '); # Hello ! World
    

• Substring + Replace

    SELECT SUBSTRING(REPLACE(('문자열', '바꾸고 싶은 부분', '바꿀 내용'), num1, num2) FROM table_name;
  

DISTINCT

---

• 중복된 결과를 제거하고 하나씩만 보여줌

    SELECT DISTINC column_name FROM table_name;
  

LIMIT

---

• 출력 개수 제한하기

    SELECT column_name FROM table_name LIMIT num;
  

  • num개의 값만 출력

    SELECT column_name1, column_name2, .. FROM table_name ORDER BY column_name dESC LIMIT num1n num2;
  

  • num1 ~ num2번째 값 출력

  • num : 인덱스, 0부터 시작

ORDER BY

---

• 오름차순 정렬

    SELECT column_name FROM table_name 
    ORDER BY column_name; 
    #기본값 : 오름차순
  
    SELECT column_name FROM table_name 
    ORDER BY column_name ASC;
  

• 내림차순 정렬

    SELECT column_name FROM table_name 
    ORDER BY column_name DESC;
  

• 2차 정렬

    SELECT column_name FROM table_name 
    ORDER BY colum_name1 ASC/DESC, colum_name2 ASC/DESC;
  

• 컬럼 지정

    SELECT colum_name1, colum_name2, .. FROM table_name ORDER BY num;
  

  • num : num번째 컬럼

• ORDER BY가 없다면 자동으로 PK컬럼의 오름차순으로 정렬