[MySQL] LIKE (% / _) / NOT LIKE + ฮฑ


LIKE / NOT LIKE + WILDCARD (%, _)


  • LIKE %

      SELECT column_name1 FROM table_name 
      WHERE column_name2 LIKE '๋ฌธ์ž์—ด';
    
      SELECT column_name1 FROM table_name 
      WHERE column_name2 LIKE '๋ฌธ์ž์—ด%';
    
      SELECT column_name1 FROM table_name 
      WHERE column_name2 LIKE '%๋ฌธ์ž์—ด%';
    
      SELECT column_name1 FROM table_name 
      WHERE column_name2 LIKE '%๋ฌธ์ž์—ด';
    
  • LIKE _

      SELECT column_name1 FROM table_name 
      WHERE column_name2 LIKE '___';
    
    • ์–ธ๋”์Šค์ฝ”์–ด n๊ฐœ > ์ปฌ๋Ÿผ์˜ ๊ฐ’์ด 3์ž๋ฆฟ์ˆ˜์ธ๊ฒƒ๋งŒ ์ถœ๋ ฅ

    • ๋ฌธ์ž์—ด ์ค‘๊ฐ„์— ์‚ฌ์šฉ ๊ฐ€๋Šฅ

  • NOT LIKE

    • ํŠน์ • ๋ฌธ์ž์—ด์„ ์ œ์™ธํ•œ ๊ฒ€์ƒ‰
      SELECT column_name1 FROM table_name WHERE column_name2 NOT LIKE '๋ฌธ์ž์—ด';
    
  • โ€™%โ€™๋‚˜ โ€˜_โ€˜์ด ๋“ค์–ด๊ฐ„ ๋ฌธ์ž์—ด์„ ์ฐพ๊ณ  ์‹ถ๋‹ค๋ฉด ๋ฐฑ์Šฌ๋ž˜์‹œ ์‚ฌ์šฉ (โ€˜%\%%โ€™ / โ€˜%\_%โ€™)



SOURCE


  • ์™ธ๋ถ€ sql ํŒŒ์ผ์— ์ž‘์„ฑํ•œ ์ฟผ๋ฆฌ ์‹คํ–‰ํ•˜๊ธฐ

      SOURCE ํŒŒ์ผ๊ฒฝ๋กœ/ํŒŒ์ผ์ด๋ฆ„.sql
    



COUNT


  • ๋ฐ์ดํ„ฐ ๊ฐœ์ˆ˜ ๊ตฌํ•˜๊ธฐ

      SELECT COUNT(*) FROM table_name;
    
      SELECT COUNT(column_name) FROM table_name;
    
      SELECT COUNT(DISTINCT column_name) FROM table_name;
    



UPPER / LOWER / REVERSE


  • ๋Œ€๋ฌธ์ž๋กœ ์ถœ๋ ฅํ•˜๊ธฐ

      SELECT UPPER();
    
  • ์†Œ๋ฌธ์ž๋กœ ์ถœ๋ ฅํ•˜๊ธฐ

      SELECT LOWER();
    
  • ๋ฌธ์ž์—ด ๋ฐ˜๋Œ€๋กœ ์ถœ๋ ฅํ•˜๊ธฐ

      SELECT REVERSE('๋ฌธ์ž์—ด');
    



CHAR_LENGTH


  • ๋ฌธ์ž์—ด์˜ ๊ธธ์ด ๊ตฌํ•˜๊ธฐ

      SELECT CHAR_LENGTH('๋ฌธ์ž์—ด');
    
      SELECT CHAR_LENGTH(column_name);
    



MIN / MAX


  • ์ตœ๋Œ€/์ตœ์†Ÿ๊ฐ’ ์ถœ๋ ฅํ•˜๊ธฐ

      SELECT MIN(column_name) FROM table_name;
    
      SELECT MAX(column_name) FROM table_name;
    
      SELECT column_name1, column_name2 
      MIN(column_name3) 
      FROM table_name 
      GROUP BY column_name1, column_name2;
    
      SELECT * FROM table_name 
      WHERE column_name = (SELECT MIN(column_name) FROM table_name);
    



SUM / AVG


  • ํ•ฉ๊ณ„ ์ถœ๋ ฅ

      SELECT SUM(column_name) FROM table_name;
    
  • ํ‰๊ท  ์ถœ๋ ฅ

      SELECT AVG(column_name) FROM table_name;
    



ROUND / FLOOR / CEIL


  • ROUND() : ๋ฐ˜์˜ฌ๋ฆผ

  • FLOOR() : ๋‚ด๋ฆผ

  • CEIL() : ์˜ฌ๋ฆผ

  • ex)

      create table member(
          ...
          age     int,
          ...
      );
    
      select concat(round(avg(age)), '์„ธ') ํ‰๊ท ๋‚˜์ด from member;
    

Categories:

MySQL