MySQL query data table data record (including multi-table query)

MySQL query data table data record (including multi-table query) MySQL query data table data record (including multi-table query)

Transferred from: http://www.baike369.com/content/?id=5355

The purpose of creating a database in MySQL is to use the data in it.

Use the SELECT query statement to query the data from the database.

The syntax format for the SELECT statement is as follows:

Select Selection_list                  //To query the content, choose which columns from table_list//                        What table to query from, where to select the row where Primary_constraint               // Conditions to be met when querying, rows must meet the criteria group by Grouping_columns              //How to group the results order by Sorting_columns               //How to sort the results having Secondary_constraint            //query results for the second condition that is met by the limit count                            //qualified output

The following is a detailed explanation of the parameters of the SELECT query statement.

First, connect the MySQL server, and second, select the specified database:

Use Db_xuesheng;

Make sure that the database is already established.

Also make sure that the data table Tb_chengji has been established and that the data has been added before you can better understand the content below.

1. Selection_list

Sets the contents of the query.

1) If you want to query all the columns in a table, you can set it to "*".

For example, query all columns in the Tb_chengji data table. The code is as follows:

SELECT * from Tb_chengji;   Querying all data in a data table

2) If you are querying only one or more columns in a table, you can enter the column names directly and separate them with ",". The code is as follows:

Select Xuesheng,yuwen from Tb_chengji;   Querying data in Xuesheng and Yuwen columns in a data table

2. table_list--Multi-Table query

Specifies the data table for the query.

You can query from a single data table, query from multiple data tables, separate multiple data tables with ",", and use join operations to determine the connection between tables through a WHERE clause.

The SQL statement format for a multi-table query is as follows:

Select Table name. field name from Table 1, table 2 ... where table 1. field = Table 2. Fields and other query conditions

For example, first look at the data records in the Tb_chengji data table. There is a display effect.

Then take a look at the data records in the Tb_info data table. The code is as follows:

SELECT * from Tb_info;

The following query from the Tb_chengji and Tb_info data tables Yuwen data records that are greater than or equal to 100. The code is as follows:

Select Tb_chengji.id,tb_chengji.xuesheng,xingbie,dizhi,yuwen,shuxue,yingyu fromtb_chengji,tb_info where tb_ Chengji.xuesheng=tb_info.xuesheng and tb_chengji.yuwen>=100;

In the SQL statement above, because 2 tables have an ID field and a xuesheng field, prefix is added to tell the server which table to display the field information in. The syntax format is as follows:

Table name. Field Name

The Tb_chengji.xuesheng=tb_info.xuesheng joins the table Tb_chengji and Tb_info, called the equivalent connection. If you do not use Tb_chengji.xuesheng=tb_info.xuesheng, then the result will be a Cartesian product of two tables, called the full connection.

3. Where Condition statement

When you use a query statement, such as to query the desired records from a number of records, you need a query condition. Only by setting the conditions of the query, the query has practical significance.

A WHERE clause is applied to the set query criteria.

With the WHERE clause, many complex conditional queries can be implemented. When you use the WHERE clause, you need to use some comparison operators. Common comparison operators are shown in the following table:

operator	name	Example
=	Equals	id=10
>	Greater than	Id>10
<	Less than	Id<10
>=	Greater than or equal	id>=10
<=	Less than or equal	id<=10
! = or <>	Not equal to	id!=10
Is null	N/A	ID is null
is NOT NULL	N/A	ID is not NULL
Between	N/A	ID between 1 and 15
Inch	N/A	ID in (3,4,5)
Not in	N/A	Xuesheng not in (Shi,li)
Like	Pattern matching	Xuesheng like (' shi% ')
Not-like	Pattern matching	Xuesheng not like (' shi% ')
Regexp	Regular expressions	Xuesheng Regular Expressions

The comparison operators commonly used in the WHERE clause are listed in the table above. The ID in the example is the number of the record, and Xuesheng is the student name in the table.

For example, use the WHERE clause to query the Tb_chengji table, with the condition that the data record is Xuesheng for Lily. The code is as follows:

SELECT * from Tb_chengji where xuesheng= ' Lily ';

Tip: The WHERE clause works by removing rows that do not conform to the where condition before grouping the results of the query, that is, filtering the data before grouping. Therefore, when you use the WHERE clause, the condition cannot contain an aggregate function to display a particular row.

4. GROUP BY results

With the GROUP BY clause, row data can be divided into smaller groups to enable group queries on records. The GROUP by clause works best when used with aggregate functions such as the AVG () function or the sum () function.

The GROUP BY clause can group query results and return summary information for the rows.

In a query statement with a GROUP BY clause, the column specified in the select list is either the column specified in the GROUP BY clause, or it is included in the aggregate function.

The Select and group BY, and having clauses of a query statement are the only places where aggregate functions occur, so you cannot use aggregate functions in the WHERE clause.

For example, in the TB_CHENGJI02 data table. As shown in the following:

You can use the GROUP BY clause to group by Xuesheng to find out the average score for each student. The code is as follows:

Select Xuesheng,avg (Fenshu) from tb_chengji02 Group by Xuesheng;

Group by Xuesheng are grouped by students, and in the Xuesheng column, students of the same name are grouped together and then their average scores are calculated.

5. HAVING clause

The HAVING clause is the function of filtering a group that satisfies a condition, that is, filtering the data after grouping. Aggregate functions are often included in the HAVING clause. You can use the having condition to display specific groups, or you can use multiple grouping criteria for grouping.

When a HAVING clause is used in a GROUP BY clause, only the groups that satisfy the having condition are returned in the query results.

For example, in a tb_chengji02 table, a record with a total of 280 or greater is queried. The code is as follows:

Select Xuesheng,sum (Fenshu) from TB_CHENGJI02 GROUP by Xuesheng have sum (Fenshu) >=280;

We can also query the TB_CHENGJI02 table to see if there is a record with a total of more than or equal to 270, and if so, it is displayed. The code is as follows:

Select Xuesheng,sum (Fenshu) from tb_chengji02 where xuesheng= ' winter snow ' or xuesheng= ' Lily ' GROUP by Xuesheng have sum (Fenshu) &G t;=270;

6. Distinct to remove duplicate rows in the results

You can use the DISTINCT keyword to remove duplicate rows from the result.

The field selected in the distinct statement can only be a field specified by distinct, and other fields cannot appear.

For example, query the TB_CHENGJI02 table and remove duplicate data from the Xuesheng field in the results. The code is as follows:

Select distinct Xuesheng from tb_chengji02;

7. Order BY to sort the results

The order by can be used to arrange the results of the query in ascending (ASC) or descending (DESC) order by default, and the order by is output as ascending. If you want to sort in descending order, you can use Desc.

When sorting columns that contain null values, the null value appears first if sorted in ascending order, and null values appear in the last face if they are arranged in descending sequence.

For example, query all the information in the Tb_chengji table, in descending order by ID. The code is as follows:

SELECT * from Tb_chengji ORDER by id DESC;

8. Like fuzzy query

Like belongs to the more commonly used comparison operator, which can implement fuzzy query. It has two wildcard characters: "%" and the underscore "_".

"%" can match one or more characters, while "_" matches only one character.

For example, in the Tb_chengji table, find all the second characters that are "winter" students. The code is as follows:

SELECT * from Tb_chengji where Xuesheng like (' _ Winter% ');

9. Concat Union Multi-column

Use the CONCAT function to combine multiple fields to form a total string.

For example, the Yuwen, Shuxue, and Yingyu fields in the Tb_chengji table are merged together to form a new string. The code is as follows:

Select Id,xuesheng,concat (Yuwen, ":", Shuxue, ":", Yingyu) as Fenshu from Tb_chengji;

Number of limit qualifying results rows

The limit clause allows you to limit the number of records in the query result and control the number of rows it outputs.

For example, querying the Tb_chengji table, in descending order of the ID field, shows 3 records. The code is as follows:

SELECT * from Tb_chengji ORDER BY id desc limit 3;

You can also use limit to take a value from the middle part of the query result. To first define two parameters, parameter 1 is the number of the first record to start reading (in the query results, the first result has a record number of 0, not 1), and parameter 2 is the number of records to query.

SELECT * from Tb_chengji ORDER BY id DESC limit 1, 2;

11. Using Functions and expressions

In MySQL, you can also use an expression to calculate the values of each column as the result of the output. An expression can also contain functions.

For example, calculate the total number of students in the Tb_chengji table. The code is as follows:

Or:

Calculate the average score of the students in the Tb_chengji table. The code is as follows:

Select *,format (AVG (Yuwen+shuxue+yingyu)/3), 1) as Pingjun Feng from Tb_chengji Group by ID;

When working with MySQL databases, it is sometimes necessary to count the records in the database, such as averaging, minimum or maximum, and then use the statistical functions in MySQL.

The usual statistical functions are shown in the following table:

name	Description
AVG (field name)	Gets the average of the specified column.
Count (field name)	If a field is specified, the non-empty record in the field is counted. If you add distinct earlier, a record of different values is counted, and the same value is used as a record. If Count (*) is used, counts all records that contain null values.
Min (field name)	Gets the minimum value for the specified field.
Max (field name)	Gets the maximum value of the specified field.
STD (field name)	Specifies the standard deviation value for the field.
Stdtev (field name)	Same as Std.
sum (field name)	Specifies the sum of all records for the field.

In addition to using functions, you can use arithmetic operators, string operators, and logical operators to make an expression.

For example, calculate the score after 80 percent for math scores. The code is as follows:

SELECT *, (Shuxue * 0.8) as ' 80% ' from Tb_chengji;

MySQL query data table data record (including multi-table query)