Mysql Study Notes (single table data record query) and mysql Study Notes

Mysql Study Notes (single table data record query) and mysql Study Notes

Querying data records refers to obtaining required data records from database objects. Mysql provides various data query methods.


1. Simple data record Query

Select field1, field2,..., fieldn from t_name

* Query all field data

Select * from t_name;

* Query data of a specified field

Select field1,..., fieldn from t_name;

If field1,... and fieldn are specified as all columns, all fields are queried.

* Avoid duplicate data query-distinct

Select distinct field1,..., fieldn from t_name

* Query mathematical arithmetic data

+-*/%

Select price, price * 10 from t_product

* Set display format Data Query

Select concat ('price per kilogram ', product, 'price', price * 2) per from t_product

Ii. Query of conditional data records

Select field1, field2,..., fieldn from t_name where condition
Function:

* Conditional data query with relational and logical operators

><=>=! = (<>)

& (AND) | (OR )! (NOT) XOR (logical exclusive or)

Select product from t_product where price <4.0 & price> 3.3

Select price from t_product where product = 'pear ';

Select price from t_product where PRODUCT = 'pear ';

The query results of the two statements are the same, that is, the case is ignored here.

Alter table t_product add PRODUCT float (8, 2 );

ERROR: Duplicate column name 'product'

* Conditional data query with the between and keyword

Select field1, field2,..., fieldn from t_name where field between value1 and value2

Select field1, field2,..., fieldn from t_name where field not between value1 and value2

Select product from t_product where price between 3.3 and 6

Select product from t_product where price not between 3.3 and 6

* Conditional data query with the is null keyword

Select field1,..., fieldn from t_name where field is null;

Select field1,..., fieldn from t_name where field is not null;

* Conditional data query with the in keyword

Determines whether the value of a field is queried in a specified set.

Select field1,..., fieldn from t_name where field in (value1,..., valuen );

Select product from t_product where price in (43.40, 6.5, 8.9 );

Select product from t_product where price not in (43.40, 6.5, 8.9 );

Note:

When the keyword in is used, if null exists in the query set, the query is not affected. If the keyword not in is used, if null exists in the query set, no query results

* Fuzzy Data Query with the like keyword

Select field1,..., fieldn from t_name where field like value;

Select filed1,..., fieldn from t_name where field not like value;

Select field1,..., fieldn from t_name where not field like value;

Wildcard characters supported by the like Keyword:

-: The wildcard value can match a single character.

%: The wildcard value can match strings of any length.

Mysql is not only case-insensitive for keywords, but also case-insensitive for field data records.

Select * from t_product where product like 'a %'

Select * from t_product where product like 'a %'

The query results of the two statements are the same.

Select * from t_product where product like '_ d % ';

Select * from t_product where! (Product like '_ d % ');

Iii. sorting data record Query

Select field1, field2,..., fieldn

From t_name

Where condition

Order by fieldm1 [asc | desc] [, fieldm2 [asc | desc],]

Function:

* Sort by single Field

The keyword "order by" is followed by only one field. The query results are sorted by this field when displayed.

In mysql, the null value is the minimum value, so it is first displayed in ascending order.

1. Sort in ascending order

Select * from t_product order by price [asc]

The default value is ascending.

2. Sort in descending order

Select * from t_product order by price desc;

* Sort by multiple fields

Select * from t_product order by price asc, id desc;

Iv. Limit the number of data records to be queried

Select field1,..., fieldn

From t_name

Where condition

Limit offser_start, row_count

Function:
* The initial position mode is not specified.

No initial position is specified. The default value is 0, indicating that the first record is displayed.

Limit row_count

Select product from t_product limit 5;

NOTE: If row_count is greater than the number of records queried, the number of all query records is displayed. If row_count is smaller than the number of query records, the number of row_count records is displayed.

* Specify the initial position

The limit keyword is often applied in the paging system. For the first page of data records, you can do this by not specifying the initial position, however, for other pages such as the second page, the initial position (offset_start) must be specified. Otherwise, the paging function cannot be implemented. In addition, the limit keyword is often used with order by, that is, the query results are sorted first, and some data records are displayed.

Select product from t_product limit 4, 5;

Five records are displayed starting from the fifth record.

V. Query of statistical functions and group data records

Count (), avg (), sum (), max (), min ()
Statistical functions are often used with grouping in specific applications.
Note: although data values can be grouped without duplicates, it is not recommended because a data record can also be grouped, but it does not have any practical significance.
Select function (field)

From t_name

[Where condition]

1. Number of statistical data records

Count (*): counts the data records in the table, regardless of whether the table field contains null or non-null

Count (field): collects statistics on records of specified fields. The null value is ignored in specific statistics.

2. Calculate the average value

Avg (field): calculates the average value of a specified field. The null value is ignored in specific statistics.

3. Sum of statistical calculations

Sum (field): calculates the sum of specified field values and ignores null values during statistics.

4. Calculate the maximum and minimum values

Max (field) and min (field)

This function is used to calculate the maximum and minimum values of statistical data. These functions can be used to calculate the maximum and minimum values in a specified field or the maximum and minimum values in a specified field value that meets the specified conditions.

Note:

For the statistics supported by mysql, if no data records exist in the operated table, the count () function returns 0, while other functions return null.

Vi. Grouping data Functions

1. Simple grouping Query
When a statistical function is used, statistics are calculated based on the number of records in the table or the data records with the specified condition (where clause. In practical applications, all data records are often grouped before statistical calculation is performed on these grouped data records.
Select function ()

From t_name

Where condition

Group by field;

Note: When querying a specific group, the value of the field on which the group is based must have a repeated value; otherwise, it will have no practical significance.

2. Grouping query of statistical functions
Mysql does not have any practical significance if it only implements simple grouping query, because when the keyword group by is used independently, it queries a random record in each group by default, which has great uncertainty.
Group_concat (): displays the specified field values in each group.

Select group_concat (field)

From t_name

Where condition

Group by field

Example:

Select price, count (price) as count, group_concat (product)

From t_product

Group by price;

3. Implement grouping query of Multiple Fields

Select group_concat (field), function (field)

From t_name

Where condition

Group by field1,..., fieldn;

In the preceding statement, field1 is first grouped, then field2 is used for each group, and so on.

4. Implement having clause to limit group queries

If you want to restrict the group conditions, you cannot use the where keyword. This keyword is mainly used to restrict data records by conditions. As a result, having is used to restrict grouped data records.

Select function (field)

From t_name

Where condition

Group by field1,..., fieldn

Having condition;