Prodigal son Tang Shao 2022-02-13 07:29:09 阅读数:504
scene : When we need to query the field values in the database , When converting to other values , You need to use CASE-WHEN function . similar :Java Medium switch-case sentence .
classification :Case There are two formats . Simple Case Functions and Case The search function .
(1) Grammar format
case Name
when Conditional value 1 then Options 1
when Conditional value 2 then Options 2.......
else The default value is
end
(2) Example
SELECT
CASE pay_way
WHEN 1 THEN ' Alipay pay '
WHEN 2 THEN ' Wechat payment '
WHEN 3 THEN ' UnionPay payment '
ELSE ' Other payments '
END payWay <!-- Convert the queried column names into :payWay -->
FROM order;
(1) Grammar format
case
when Name = Conditional value 1 then Options 1
when Name = Conditional value 2 then Options 2.......
else The default value is
end
(2) Example
UPDATE employee
SET salary =
CASE
WHEN level = '1' THEN salary * 1.8
WHEN level = '2' THEN salary * 1.5
WHEN level = '3' THEN salary * 1.2
ELSE salary * 1.1
END
sql1:SELECT user_name,COUNT(*) AS count FROM user_info GROUP BY user_name HAVING count > 1;
sql2:SELECT user_name FROM user_info GROUP BY user_name HAVING COUNT(1) > 1;
sql3:SELECT * FROM user_info WHERE user_name IN (SELECT user_name FROM user_info GROUP BY user_name HAVING COUNT(1) > 1);
PS:
1.HAVING It's grouping (GROUP BY) After the screening conditions , After grouping, the data group will be filtered again ;WHERE Filter before grouping .
2.WHERE Aggregate function... Cannot be used in clause ( similar count), and HAVING Clause can , So I added... To the set function HAVING To test whether the query results meet the conditions . namely HAVING The applicable scenario of clause is that aggregate functions can be used .
3. Used group by after , Just ask select The following fields are included in group by Or aggregate functions . Therefore, it is necessary to query all the data repeated in a certain field , use sql3
Definition : Judge that the first parameter is true still false, To decide whether to take the second parameter value or the third parameter value
1. grammar :IF(expr1,expr2,expr3)
If expr1 yes TRUE, be IF() The return value of is expr2; Otherwise, the return value is expr3.IF() The return value of is a numeric value or a string value
2. Example :
select *,if(sex=1," male "," Woman ") as sex from user where sex != ""
Definition : Determine whether the field is null, by null Returns a specific value , Not for null Also returns a specific value
1. grammar :IFNULL(expr1,expr2)
If expr1 Not for NULL, be IFNULL() The return value of is expr1; Otherwise, the return value is expr2.IFNULL() It's a number or a string
2. Example :
select IFNULL(sex,1) from user
Definition : Returns the number of days between two dates
1. grammar :DATEDIFF(date1,date2)
2. Example :SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate
PS:
1.date1 and date2 The parameter is a legal date or date / Time expression .
2. The calculation is date1 - date2
Definition : Used to display dates in different formats / Time data
1. grammar :DATE_FORMAT(date,format)
2. Example :select date_format(now(),'%Y-%m-%d %H:%i:%S') AS startDate Get the results :2021-12-20 20:02:18
PS:
1.date Parameter is a valid date .format Specified date / Time output format .
The available formats are :
Format | significance |
---|---|
%a | Abbreviated week name |
%b | Abbreviated month name |
%c | month , The number |
%D | Days of the month with English prefix |
%d | Day of the month , The number (00-31) |
%e | Day of the month , The number (0-31) |
%f | Microsecond |
%H | Hours (00-23) |
%h | Hours (01-12) |
%I | Hours (01-12) |
%i | minute , The number (00-59) |
%j | Days of (001-366) |
%k | Hours (0-23) |
%l | Hours (1-12) |
%M | Month name |
%m | month , The number (00-12) |
%p | AM or PM |
%r | Time ,12- Hours (hh:mm:ss AM or PM) |
%S | second (00-59) |
%s | second (00-59) |
%T | Time , 24- Hours (hh:mm:ss) |
%U | Zhou (00-53) Sunday is the first day of the week |
%u | Zhou (00-53) Monday is the first day of the week |
%V | Zhou (01-53) Sunday is the first day of the week , And %X Use |
%v | Zhou (01-53) Monday is the first day of the week , And %x Use |
%W | Week name |
%w | Days of the week (0= Sunday , 6= Saturday ) |
%X | year , Sunday is the first day of the week ,4 position , And %V Use |
%x | year , Monday is the first day of the week ,4 position , And %v Use |
%Y | year ,4 position |
%y | year ,2 position |
copyright:author[Prodigal son Tang Shao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130729073976.html