Mysql database 2 - complex SQL commands

mysql database 2— complex sql grammar

1.case when usage

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. Simple Case 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;

2.Case The search function

（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

2.group by … having usage

1. Inquire about SQL Duplicate data of a field in the table

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

3.if Sentence usage

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 != ""

4.ifnull Sentence usage

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

5.datediff function

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

6.date_format function

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