< div> lpad left fill
< div> join table 2 alias
< div> Outer connection:
Common function:
Character function:
Length field character length
Concat connection substring
< p> substr intercept substring
instr insert substring
trim both ends to empty
upper to uppercase
lower to lowercase< /p>
Lpad left padding
Rpad right padding
Replace
Numerical function:
< p> round to the nearest integer
ceil to round up
floor to round down
mod modulus
truncate truncated
rand returns 0 Random decimal between -1
Date function:
now return current date+time
curdate return current date
curtime returns the current time
year
month
monthname returns the month in English
day
< p> hour
minute
second
date_format converts the date into characters
other functions:< /p>
version current data server version
database currently open database
user current user
md5(‘character’) Return the character md5 encryption form
Control function:
1)if
if (conditional expression, expression 1, expression 2 ) If the conditional expression is true, return expression 1, otherwise return 2
select dept_id,
sum(if (sex=’男’,1,0)) male count,
sum(if (sex=’女’,1,0)) female count,
from emp_sex
group by dept_id;
< p>
2)case
select dept_id,
sum(case sex when’male’ then 1 else 0 end) male count,
sum(case sex when’女’ then 1 else 0 end) female count,
from emp_sex
group by dept_id;
Grouping function:
sum,avg,max,min,count
1.sum,avg are generally used to process numeric types, max min count can handle any Type
2. The above grouping functions ignore null values
3. Can be combined with distinct to achieve deduplication operation
group by Group query:
1. The filter conditions in the group query are divided into two categories:
Filter before grouping: Operation data source
Filter after grouping: Operation group After the result set
1). The grouping function must be placed in the having clause as a condition
2). The filtering before the grouping can be used, and the filtering before the grouping is preferred.
2. group by can write multiple field groupings, there is no order relationship between the front and back
3. If there is a sort, it will be placed at the end of the entire group query
< p> SELECT * FROM sc GROUP BY sid; After grouping, no aggregation function is specified, and the system selects to display the first data of sid by default
mysql basic syntax:
< p> Select the grouping function, the field after grouping step5
From Table 1 Alias 1, Table 2 Alias 2 step1
Where filter conditions step2
Group by group Field step3
having filtering after grouping step4
order by sorting list step6
sql92 syntax:
< p>(Non) equivalence connection:
1. The result of multi-table equivalence connection is the intersection of multiple tables
2. n-table connection, at least n-1 Connection conditions
3. The order of multiple tables is not required 4. Generally, it is necessary to alias the table (the original name of the table cannot be written in the select after the alias is created)
5. It can be combined with the front All the clauses introduced
Self-connection:
1. Look at a table as two tables, and use different aliases to query the same table. Data
Syntax:
select alias 1. field, alias 2. field
from table A alias 1, table A alias 2
Where alias 1. field= alias 2. field
sql99 syntax:
Syntax:
select query list
from table 1 alias [connection type]
join table 2 alias
on connection Conditions
[where filter conditions]
[group by grouping]
[having filter conditions]
[order by [order list] ]
Connection type classification:
Inner connection: inner
Outer connection:
Left outer: left
Right outer: right
All outer: full
Cross connection: cross
