Query syntax

# When querying all data in the table
select 

#When querying by criteria
select
	Field list
from
	Table name list
where
	Condition list
group by
	Grouping field
having
	Post grouping condition
order by
	sort
limit
	paging

sql execution order

from < join<where<group by <count(*)<having<select<order by <limit

Condition query

grammar

select 
	Query list
from 
    Table name
where 
    Screening conditions;

classification

1. Filter by conditional expression
Conditional operator: > < =! = < > < = >=

2. Filter by logical expression
Action: used to connect conditional expressions
Logical operator: & & |! and or not
&&And and: as long as two conditions are true, the result is true, and vice versa
||And or: if one of the two conditions is true, the result will be true, otherwise it will be false
! Reverse and: not

3. Fuzzy query
like
between and
in
is null
is not null

Sort query

grammar

select 
	Query list
from
	surface
[where Screening conditions]
order by Sort list [asc|desc]

sort order

desc: descending order
asc: ascending (default)
be careful:
If there are multiple sorting conditions, the second condition will be judged only when the condition values of the current edge are the same

Common functions

Function concept

A method similar to java encapsulates a group of logical statements and exposes the method name

benefit

Hidden implementation details
Improve code reusability

classification

Single line function (multiple in and one out)

1. Character function

length Get the number of bytes of the parameter value (the character set needs to be considered)
	select length('john');
concat Splice string
	select concat (name,'_',age) from student;
upper((to capital) ,lower((to lowercase)
	select upper('john');
	select lower('JOHN');
	Case: capitalize the last name and lowercase the first name, and then splice them
	select concat(upper('nihao'),lower('zaijian'));
substr Intercept string in sql Index in starts at 1
	select substr('2020-08-08',1,4);
	select substr('Li Mochou fell in love with Lu Zhanyuan',7,3);
instr (The starting index of the first occurrence of a substring in a large string. If it does not return 0)
	select instr('Yang Buhui fell in love with Yin Liuxia','Yin Liuxia');
trim Forward and backward character
	select trim('       Zhang Cuishan      ');
	select trim('a' from 'aaaaaa Zhang aaaa Cuishan aaaaaa');
lpad，rpad((complete left or right)
	select lpad('nihao',10,'a');
	select rpad('nihao',10,'a');
	replace replace
	select replace('Zhang Wuji fell in love with Zhou Zhiruo','Zhou Zhiruo','Zhao Min');

2. Mathematical function

round rounding
	select round(1.5);
	select round(1.567,2);
ceil  Round up and return>=The minimum integer for this parameter
	select ceil(1.1); 
floor Round down and return<=The maximum integer for this parameter
	select floor(1.9);
truncate truncation
	select truncate(1.69999,2);
mod Surplus   a-a/b*b
	select mod(10,3);
	select 10%3;

3. Date function

now Returns the current system date+time
	select now();
currdate Returns the current system date
    select curdate();
Gets the specified part ,year(year),month(month),day(day),hour(hour),minute(minute),second(second)
    select year(now());
    select month('1999-09-09');
str_to_date:Converts characters in date format to a date in the specified format
    select str_to_date('9-13-2004','%m-%d-%Y')
date_format:Convert date to string
    select date_format('2020-01-01','%Y year/%m month/%d day');

4. Other functions

# View version
select VERSION();
# View database in use
select database();
# View user
select user();

5. Process control function

if Functions are similar to if else
    if(Conditional expression,true Execute this, false Execute this)
    if(10>5,'greater than','less than')
    if(english is null ,'I didn't take the English test','I took the English test') from student;
case Functions are similar to switch case
    case Field or expression to judge
    when Constant 1 then Displayed value 1
    when Constant 2 then Displayed value 2
    else Value to display n
    end

Grouping function (aggregation function, used for Statistics)

count: Calculate(Number of rows)number
	 Generally, non empty columns are selected: primary key
	count(*)
	select count(math) from student;	
max: Calculate maximum
	select max(math) from student;
min: Calculate minimum
	select min(math) from student;
sum: Calculation and
	select max(math) from student;
avg: Calculate average
	select avg(math) from student;
What types of parameters are supported
	sum(),avg() Numerical type
	min(),max(),count() Both numeric values and characters are OK
	-- Note: the calculation of all aggregate functions is excluded null Value.
	-- contain null Solutions:
		-- 1. Select columns that do not contain non empty columns for calculation
		-- 2. IFNULL function
 Can and distinct Use with
	select sum(distinct math),sum(math) from student;
	select count(distinct math),count(math) from student;
There are restrictions on the fields queried with the grouping function,Generally speaking group by Fields after
	select sum(math),name from student; -- meaningless

Grouping query

grammar

select Grouping function,column(Request appears in group by behind)
from surface
[where Screening conditions]
group by Group list
[having Screening conditions]
[order by Sort list]

be careful:

1. Fields queried after grouping: grouping fields and aggregation functions
   1. What's the difference between where and having?
      1. where is limited before grouping. If the conditions are not met, it will not participate in grouping. having is limited after grouping. If the result is not satisfied, it will not be queried
      2. where cannot be followed by the aggregate function. having can judge the aggregate function

Paging query

grammar

select
	Field list
from
	Table name list
[where
	Condition list]s
[group by
	Grouping field]
[having
	Conditions after grouping]
[order by
	sort]
limit
	Paging limit

formula

Start index = (current page number - 1) * number of entries displayed per page

Multi table joint query

Inner connection

1. Implicit connection: use the where condition to eliminate useless data (sql92)
Syntax: select field list from table name 1, table name 2 on condition
2. Explicit inner connection (sql99):
Syntax: select field list from table name 1 [inner] join table name 2 on condition
3. Key points of connection query:
From which tables do you query data
What are the conditions
Which fields to query

External connection

1. Left outer connection:
   Syntax: select field list from table 1 left [outer] join table 2 on condition;
   All data in the left table and its intersection are queried.

2. Right outer connection:
   Syntax: select field list from table 1 right [outer] join table 2 on condition;
   All the data in the right table and its intersection are queried.

Subquery (nested query)

select
	Field list
from
(
	select
		Field list
	from
		Table name list
)