mysql< Forms & Collection Queries >

form Query Simple Query

SELECT statement

Querying all fields

Specify all fields: Select Field Name 1, field name 2,...from table name;

SELECT * from table name;

Querying a specified field

Select Field Name 1, field name 2,...from table name;

Query by Criteria

Queries with relational operators

SELECT Field name 1, field name 2,......

From table name

WHERE conditional expression;

Query with in keyword

SELECT *| Field name 1, field name 2,......

From table name

WHERE field name [not] in (element 1, Element 2,......);

Queries with between and keywords

SELECT *| {Field name 1, field name 2,......}

From table name

WHERE field name [not] between value 1 and value 2;

Null value query

SELECT *| Field name 1, field name 2,......

From table name

The WHERE field name is [not] NULL;

Query with DISTINCT keyword

Filter out duplicate values: SELECT DISTINCT field name from table name;

function multiple fields: SELECT DISTINCT field name 1, field name 2,... from table name;

Queries with the LIKE keyword

SELECT *| {Field name 1, field name 2,......}

From table name

WHERE field name [not] like ' match string ';

Percent percent (%) wildcard character

Can match any length of string, including an empty string

Underline (_) wildcard characters

The underscore wildcard matches only a single character, and if you want to match more than one character,

You need to use multiple underscore wildcards.

Multi-criteria query with and keywords

SELECT *| {Field name 1, field name 2,......}

From table name

WHERE condition expression 1 [... An and conditional expression n];

Multi-criteria query with or keyword

SELECT *| {Field name 1, field name 2,......}

From table name

A WHERE condition expression 1 OR [... An OR conditional expression n];

cases where the OR and and keywords are used together

and has precedence over or, so when the two are used together, the conditional expressions on and on both sides should be first calculated, and then the conditional expressions on or both sides are calculated.

Advanced Query Aggregation Functions

Total number of records: SELECT COUNT (*) from table name;

Sum of all values for a field: Select Sum (field name) from table name;

Average number of all values in a field: Select AVG (field name) from student;

Maximum value for a field: select Max (field name) from student;

Minimum value for a field: select min (field name) from student;

sort the results of a query

SELECT Field name 1, field name 2,......

From table name

ORDER by field name 1 [ASC | DESC], field name 2 [ASC | DESC] ...;

It is important to note that if the field value of a record is null in ascending order by the specified field, this record is shown in the first bar, because the null value can be considered as the minimum value

group queries on field values

SELECT Field name 1, field name 2,......

From table name

GROUP by Field name 1, field Name 2, ... [having conditional expression];

Three types of usage

Use GROUP by group alone: SELECT * from student group by gender;

The query is one record in each group.

With aggregate functions: SELECT (*), gender from student group by gender;

You can count the maximum, minimum, average, and so on for one or some of the fields in a group.

With Having:select sum (grade), gender from student group by gender have sum (grade) <300;

Having and where are used to set conditions to filter the query results. The difference is that you can follow the aggregation function after having a, where cannot.

limit the number of query results by using limit

SELECT Field name 1, field name 2,......

From table name

Limit [OFFSET,] number of records;

Top four: SELECT * from student limit 4;

5th to 8th grade from high to Low: SELECT * from Student order BY grade DESC limit 4, 4;

functions (list)

Include mathematical functions, string functions, date and time functions, conditional judgment functions, cryptographic functions, and so on. Simplifies user operations on data

alias a table

SELECT * from table name [as] alias;

Alias a field

Select field name [as] alias [, field name [as] alias,...] from table name;

Collection Query

Set functions include count, MIN, MAX, SUM, and Avg.

COUNT calculates the number of non-null values in an expression, and removes duplicate values if the Distice keyword is used. If Count (*) is used, all rows count

MIN calculates the minimum value of an expression. Ignore null values in an expression

MAX calculates the maximum value of an expression. Ignore null values in an expression

SUM evaluates the and of all values of an expression. Ignore null values in an expression

AVG calculates the average of an expression. Ignore expression Hollow value

There are three conversion functions: To_char, To_date, and To_number.

Character functions mainly include lower (all lowercase), UPPER (all uppercase), Initcap (uppercase), CONCAT (connection string), SUBSTR (substring), length (Getting string lengths), and so on.

mysql< Forms & Collection Queries >