Mysql database 2 - complex SQL commands

Prodigal son Tang Shao 2022-02-13 07:29:09 阅读数:504

mysql database 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
copyright:author[Prodigal son Tang Shao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130729073976.html