개발/sql

[mysql] basic functions

방푸린 2024. 9. 9. 14:23
반응형

basics

1. String functions

  • CONCAT(str1, str2, ...): Concatenates two or more strings into one.
SELECT CONCAT('Hello', ' ', 'World');
-- Output: Hello World
  • LENGTH(str): Returns the length of a string in bytes.
SELECT LENGTH('MySQL');
-- Output: 5
  • LOWER(str) / UPPER(str): Converts the string to lowercase or uppercase.
SELECT LOWER('HELLO');
-- Output: hello
  • SUBSTRING(str, pos, len): Extracts a substring from a string starting at a specified position and for a specified length.
SELECT SUBSTRING('MySQL Functions', 1, 5); //1부터 시작
-- Output: MySQL
  • TRIM(str): Removes leading and trailing spaces from a string.
SELECT TRIM('  Hello  ');
-- Output: Hello

2. Numeric Functions

  • ABS(x): Returns the absolute value of x.
SELECT ABS(-5);
-- Output: 5
  • CEIL(x) / FLOOR(x): Rounds up or down to the nearest integer. 올림/버림
SELECT CEIL(4.3), FLOOR(4.3);
-- Output: 5, 4
  • ROUND(x, d): Rounds the number x to d decimal places. 소수점x개만 보이게
SELECT ROUND(5.567, 2);
-- Output: 5.57
  • POWER(x, y): Returns x raised to the power of y.
SELECT POWER(2, 3);
-- Output: 8
  • MOD(x, y): Returns the remainder of x divided by y.
SELECT MOD(10, 3);
-- Output: 1

3. Date and Time Functions

  • NOW(): Returns the current date and time.
SELECT NOW();
-- Output: 2024-09-09 12:34:56
  • CURDATE(): Returns the current date.
SELECT CURDATE();
-- Output: 2024-09-09
  • CURTIME(): Returns the current time in HH:MM:SS format.
SELECT CURTIME();
-- Output: 12:34:56
  • DATE_ADD(date, INTERVAL expr unit): Adds a time interval to a date.
SELECT DATE_ADD('2024-09-09', INTERVAL 5 DAY);
-- Output: 2024-09-14
  • DATE_SUB(date, INTERVAL expr unit): Subtracts a time interval from a date.
SELECT DATE_SUB('2024-09-09', INTERVAL 1 MONTH);
-- Output: 2024-08-09
  • ADDDATE(date, INTERVAL expr unit) / SUBDATE(date, INTERVAL expr unit): Synonyms for DATE_ADD and DATE_SUB, respectively.
SELECT ADDDATE('2024-09-09', INTERVAL 10 DAY);
-- Output: 2024-09-19
  • TIMESTAMPADD(unit, interval, datetime): Adds an interval to a timestamp.
SELECT TIMESTAMPADD(DAY, 5, '2024-09-09 12:00:00');
-- Output: 2024-09-14 12:00:00
  • DATEDIFF(date1, date2): Returns the difference in days between two dates. (앞 - 뒤) 일자
    • =timestampdiff(day, date2, date1) 과 같음
SELECT DATEDIFF('2024-09-15', '2024-09-09');
-- Output: 6
  •  TIMESTAMPDIFF(unit, datetime1, datetime2): Returns the difference between two dates in the specified unit (second, minute, hour, day, week, month, year). (뒤 - 앞)로 계산하며 -도 나옴
SELECT TIMESTAMPDIFF(HOUR, '2024-09-09 10:00:00', '2024-09-09 15:00:00');
-- Output: 5
  • 주의해야 할 사항!
    • timestampdiff는 시간으로 계산하고 24시간으로 날짜일수를 계산
    • datediff는 시간은 버리고 순수 날짜로만 계산 
select timestampdiff(day, last_login, last_logout) as timestampdifff
, datediff(last_logout, last_login) as datedifff
, last_login , last_logout 
from user
having timestampdifff !=  datedifff
;


timestampdifff|datedifff|last_login         |last_logout        |
--------------+---------+-------------------+-------------------+
           -10|      -11|2024-06-10 16:52:12|2024-05-30 21:04:17|
             3|        4|2024-07-25 17:49:34|2024-07-29 08:16:30|
             0|        1|2024-03-15 17:49:32|2024-03-16 02:01:10|
             0|        1|2024-07-31 08:34:44|2024-08-01 07:01:18|

 

  • YEAR(date): Extracts the year from a date.
SELECT YEAR('2024-09-09');
-- Output: 2024
  • MONTH(date): Extracts the month from a date (1-12).
SELECT MONTH('2024-09-09');
-- Output: 9
  • DAY(date): Extracts the day of the month from a date (1-31).
SELECT DAY('2024-09-09');
-- Output: 9
  • HOUR(time), MINUTE(time), SECOND(time): Extracts the hour, minute, or second from a time value.
SELECT HOUR('12:34:56'), MINUTE('12:34:56'), SECOND('12:34:56');
-- Output: 12, 34, 56
  • DAYOFWEEK(date): Returns the day of the week for a date (1 for Sunday, 7 for Saturday).
    • 일월화수목금토
    •  1 2 3 4 5 6 7
SELECT DAYOFWEEK('2024-09-09');
-- Output: 2 (Monday)
  • DAYOFYEAR(date): Returns the day of the year (1-366).
SELECT DAYOFYEAR('2024-09-09');
-- Output: 253
  • WEEK(date): Returns the week number (1-53) of the year for the date.
SELECT WEEK('2024-09-09');
-- Output: 36
  • DATE_FORMAT(date, format): Formats the date based on the specified format string.Common format specifiers:
    • %Y: Year (4 digits)
    • %m: Month (2 digits)
    • %d: Day of the month (2 digits)
    • %H: Hour (24-hour format)
      • %h :12-hour format
    • %i: Minute
    • %s: Second
SELECT DATE_FORMAT('2024-09-09', '%W, %M %d, %Y');
-- Output: Monday, September 09, 2024
  • STR_TO_DATE(str, format): Parses a string into a date based on the format specified.
SELECT STR_TO_DATE('09-09-2024', '%d-%m-%Y');
-- Output: 2024-09-09

  • LAST_DAY(date): Returns the last day of the month for the given date.
SELECT LAST_DAY('2024-09-09');
-- Output: 2024-09-30
  • EXTRACT(unit FROM date): Extracts a part of the date based on the specified unit (e.g., year, month, day).
SELECT EXTRACT(YEAR FROM '2024-09-09');
-- Output: 2024

 

4. Aggregate Functions

  • COUNT(column): Returns the number of non-NULL rows in a column.
SELECT COUNT(*) FROM employees;
  • SUM(column): Returns the sum of values in a numeric column.
SELECT SUM(salary) FROM employees;
  • AVG(column): Returns the average value of a numeric column.
SELECT AVG(salary) FROM employees;
  • MAX(column) / MIN(column): Returns the maximum or minimum value in a column.
SELECT MAX(salary) FROM employees;

5. Control Flow Functions

  • IF(expr, true_value, false_value): Returns one value if a condition is true and another if it's false.
SELECT IF(salary > 5000, 'High', 'Low') FROM employees;
  • CASE: Evaluates a list of conditions and returns one of several possible results.
SELECT 
  CASE 
    WHEN salary > 5000 THEN 'High'
    WHEN salary BETWEEN 3000 AND 5000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_range
FROM employees;

 

window functions

1. ROW_NUMBER()

  • Description: Assigns a unique, sequential integer to rows within a result set, starting at 1. Rows with the same values in the ORDER BY clause receive different ranks.
SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;
 

Example Output:

name salary rank
Alice 8000 1
Bob 7000 2
Charlie 7000 3
David 6000 4

2. RANK()

  • Description: Assigns a rank to rows within a result set based on the ORDER BY clause. Rows with equal values in the ORDER BY clause receive the same rank, and the next rank will be skipped (i.e., if two rows share rank 1, the next rank will be 3).
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Example Output:

name salary rank
Alice 8000 1
Bob 7000 2
Charlie 7000 2
David 6000 4

3. DENSE_RANK()

  • Description: Similar to RANK(), but it doesn’t skip any ranks. Rows with equal values in the ORDER BY clause receive the same rank, but the next rank is consecutive (i.e., no ranks are skipped).
SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Example Output:

name salary rank
Alice 8000 1
Bob 7000 2
Charlie 7000 2
David 6000 3

Syntax Breakdown:

ROW_NUMBER() OVER (ORDER BY column_name)
RANK() OVER (ORDER BY column_name)
DENSE_RANK() OVER (ORDER BY column_name)
 
  • ORDER BY: Specifies the column or columns to determine the order of the ranking.
  • You can also partition the data using the PARTITION BY clause to restart the ranking for each partition.

4. Using PARTITION BY:

  • You can use the PARTITION BY clause to restart the ranking for each subset of data, such as departments.
SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS department_rank
FROM employees;
  • This query ranks employees by salary within each department.

Example Output:

name department salary department_rank
Alice HR 8000 1
Bob HR 7000 2
Charlie IT 9000 1
David IT 8500 2

Summary of Ranking Functions:

  • ROW_NUMBER(): Assigns a unique rank to each row.
  • RANK(): Assigns the same rank to ties, but skips the next rank(s).
  • DENSE_RANK(): Assigns the same rank to ties, but does not skip ranks.

 

ALIAS

SQL에서는 WHERE 절에서 select 절에 선언한 컬럼 별칭(alias)를 바로 사용할 수 없음. 왜냐면 SQL 쿼리에서 데이터 필터링을 위한 조건이 먼저 실행되기 때문에, 이 시점에서는 아직 SELECT 절에서 생성된 별칭이 존재하지 않기 때문

해결책: 서브쿼리 또는 HAVING 절을 사용

1. 서브쿼리를 사용한 해결 방법:

SELECT timestampdiff(day, last_login, last_logout) AS timestampdifff, 
       datediff(last_logout, last_login) AS datedifff, 
       last_login, 
       last_logout 
FROM (
    SELECT last_login, 
           last_logout, 
           timestampdiff(day, last_login, last_logout) AS timestampdifff,   ---
           datediff(last_logout, last_login) AS datedifff   ---
    FROM user
) AS subquery
WHERE timestampdifff != datedifff;

여기서 subquery는 먼저 timestampdifff와 datedifff 값을 계산한 후, 외부 쿼리에서 이 값을 필터링함

2. HAVING 절을 사용하는 방법:

만약 GROUP BY가 사용된다면 HAVING 절을 이용할 수 있으나, 이 경우에는 GROUP BY가 필요 없을 때도 활용할 수 있음. HAVING 절은 SELECT 절이 실행된 후에 필터링을 적용하기 때문에 별칭을 사용할 수 있음

 
SELECT timestampdiff(day, last_login, last_logout) AS timestampdifff, 
       datediff(last_logout, last_login) AS datedifff, 
       last_login, 
       last_logout 
FROM user
HAVING timestampdifff != datedifff;

 

union vs union all

  • union : 결과에서 중복 삭제(더 느림)
  • union all: 중복 포함

 

left join, right join, inner join

  • INNER JOIN: Returns only matching rows from both tables. 교집합
  • LEFT JOIN(left outer join): Returns all rows from the left table, and matching rows from the right table. If there’s no match, NULL is returned for columns from the right table. (from A left join B A에서 모두 보여주고 B에서 없으면 null)
  • RIGHT JOIN(right outer join): Returns all rows from the right table, along with matching rows from the left table. If there is no match, NULL is returned for the left table's columns.
  • JOIN (without a prefix) defaults to INNER JOIN.

 

728x90
반응형