SQL Detailed syntax Introduction--the most basic knowledge of learning database two

Data | database | syntax aggregate function count
Use:

Returns the number of rows in the selected result set.

Grammar:

SELECT COUNT (column_name) from table_name

Cases:

The original data in the "Persons" table is as follows:

Name

Age

Hansen, Ola

34

Svendson, Tove

45

Pettersen, Kari

19

Select Total Records:

SELECT COUNT (Name) from Persons

Execution results:

3


Sum
Use:

Returns the sum of all the values in an expression, or only the DISTINCT value. SUM is available only for numeric data rows. Null value has been ignored.

Grammar:

SELECT SUM (column_name) from table_name

Cases:

The original data in the "Persons" table is as follows:

Name

Age

Hansen, Ola

34

Svendson, Tove

45

Pettersen, Kari

19

Select the sum of everyone's age in the Persons table:

SELECT SUM (age) from Persons

Execution results:

98

Select the sum of the ages of persons over 20 years of age in the Persons table:

SELECT SUM (age) from Persons WHERE age>20

Execution results:

79


Avg
Use:

Returns the average of the values in the selected result set. Null value has been ignored.

Grammar:

SELECT AVG (column_name) from table_name

Cases:

The original data in the "Persons" table is as follows:

Name

Age

Hansen, Ola

34

Svendson, Tove

45

Pettersen, Kari

19

Select the average age of everyone in the "Persons" table:

SELECT AVG (age) from Persons

Execution results:

32.67

Select the average age of persons over 20 years of age in the "Persons" table:

SELECT AVG (age) from Persons WHERE age>20

Execution results:

39.5


Max
Use:

Returns the maximum value for the selected result set. Null value has been ignored.

Grammar:

SELECT MAX (column_name) from table_name

Cases:

The original data in the "Persons" table is as follows:

Name

Age

Hansen, Ola

34

Svendson, Tove

45

Pettersen, Kari

19

Select the maximum age in the Persons table:

SELECT MAX (age) from Persons

Execution results:

45


Min
Use:

Returns the minimum value for the value in the selected result set. Null value has been ignored.

Grammar:

SELECT MIN (column_name) from table_name

Cases:

The original data in the "Persons" table is as follows:

Name

Age

Hansen, Ola

34

Svendson, Tove

45

Pettersen, Kari

19

Select the minimum age in the Persons table:

SELECT MIN (age) from Persons

Execution results:

19






Arithmetic function abs
Use:

Returns the absolute positive value of the specified numeric expression (Numeric Expression).

Grammar:

ABS (numeric_expression)

Cases:

ABS ( -1.0) ABS (0.0) ABS (1.0)

Execution results:

1.0 0.0 1.0


Ceil
Use:

Returns the smallest integer greater than or equal to the given numeric expression.

Grammar:

Ceil (numeric_expression)

Cases:

Ceil (123.45) ceil (-123.45)

Execution results:

124.00-123.00


Floor
Use:

Returns the largest integer less than or equal to the given numeric expression.

Grammar:

FLOOR (numeric_expression)

Cases:

FLOOR (123.45) FLOOR (-123.45)

Execution results:

123.00-124.00


Cos
Use:

Returns the mathematical function of the trigonometric cosine of the specified angle (in 弪) in the specified expression.

Grammar:

COS (numeric_expression)

Cases:

COS (14.78)

Execution results:

-0.599465


Cosh
Use:

Returns the angle value in radians, and the rest of the string is the specified float expression, also known as the inverse cosine.

Grammar:

COSH (numeric_expression)

Cases:

COSH (-1)

Execution results:

3.14159


Sin
Use:

Returns the trigonometric sine function (trigonometric Sine) of the given angle (in radians) with an approximate value (float) expression.

Grammar:

SIN (numeric_expression)

Cases:

SIN (45.175643)

Execution results:

0.929607


Sinh
Use:

Returns the angle in 弪, whose sine is the specified float expression (also known as the inverse chord).

Grammar:

SINH (numeric_expression)

Cases:

SINH (-1.00)

Execution results:

-1.5708


Tan
Use:

Returns the tangent function of an input expression.

Grammar:

TAN (numeric_expression)

Cases:

TAN (3.14159265358979/2)

Execution results:

1.6331778728383844E+16


Tanh
Use:

Returns the angle in 弪, whose tangent is the specified float expression (also known as tangent).

Grammar:

TANH (numeric_expression)

Cases:

TANH (-45.01)

Execution results:

-1.54858


Exp
Use:

Returns the exponent (exponential) value of the given float expression.

Grammar:

EXP (numeric_expression)

Cases:

EXP (378.615345498)

Execution results:

2.69498e+164


Log
Use:

Returns the natural logarithm of the given float expression.

Grammar:

LOG (numeric_expression)

Cases:

LOG (5.175643)

Execution results:

1.64396


Power
Use:

Returns a given expression specifying the value of the power.

Grammar:

Power (NUMERIC_EXPRESSION,V)

Cases:

Power (2,6)

Execution results:

64


Sign
Use:

Returns the positive (+1), 0 (0), or minus (-1) Number of the given expression.

Grammar:

SIGN (numeric_expression)

Cases:

SIGN (123) SIGN (0) SIGN (-456)

Execution results:

1 0-1


sqrt
Use:

Returns the square of the given expression.

Grammar:

SQRT (numeric_expression)

Cases:

SQRT (10)

Execution results:

100