MySQL Advanced 3456

Sort queries

First, the grammar

Select query table

From table

Where filter criteria

Order by sorted list [Asc/desc]

Characteristics:

1. ASC: Ascending

Desc: Descending

2. Support single field, multiple fields, functions, expressions, aliases in sorted list

3. The position of order by is generally placed at the end of the query statement (except limit)

Common functions:

Features: Improved reusability

Hides the implementation details

Call function Syntax:

Select function name (argument list);

Second, single-line function

1. Character functions

Concat: Connect

SUBSTR: Intercepting strings

Upper/lower: Uppercase, lowercase

Repalce: Replace

Length: Gets the byte length

Trim: Go space before and after

Lpad/rpad: padding left and right

InStr: Gets the index of the first occurrence of a string

2. Mathematical functions

Ceil: Rounding up

Round: Rounding

MoD: Rounding

Floor: Rounding Down

Truncat: Stage

Rand: Get random number

3. Date function

Date

Now

Year

Month

Day

Date_format:

Curdate:

Str_to_date:

Curtime:

Hour

Minute

Second

DateDiff: Two days of difference in time

MonthName

　　

　　

4. Other functions

Wersion: Database server version

Database: currently open databases

Password: Returns the password form for this character

MD5: Returns the MD5 encryption form of the character

5. Process Control function

if (conditional expression, expression 1, expression 2): Returns 1 if the conditional expression is true, 2 otherwise

Case Scenario 1

Case variable or expression or field

When constant 1 then value 1

When ....

else ...

End

Case Scenario 2

　　

Case

When condition 1 then value 1

When ....

else ...

End

Third, grouping functions

1. Classification

Max: Max value

Min: Minimum value

Avg: Average

Sum: Sum

Count: Count

2. Features

1. Grammar

Select Max (field) from table name

2. Supported types

Sum and AVG are generally used to handle numeric types

Max, Min, Cuount can handle any data type

All of the above grouping functions ignore null

Can be used with distinct, to achieve de-re-statistics

Count function

Conut (field): Counts the number of non-null values in the field

COUNT (*): Number of rows in the statistic result set

Case: Query the number of employees in each department

MySQL Advanced 3456