Shulou(Shulou.com)06/01 Report--

This article uses the example code to show the use of the single table query of the select statement, the code is very detailed, which can be used as a reference for interested friends. I hope it will be helpful to you.

The basic syntax of the SELECT statement is as follows:

Select selection_list / / the content to be queried, select which columns from data table name / / specify the conditions to be met when querying the data table where primary_constraint / / Conditions that rows must meet group by grouping_columns / / how to group results order by sorting_cloumns / / how to sort results limit count single table query meets the second condition limit count / / query

A single table query refers to querying the required data from a table. All query operations are relatively simple.

1. Query all fields

Querying all fields refers to the data of all fields in the query table. In this way, the data of all the fields in the table can be queried. In MySQL, you can use "*" to represent all the columns, and all the fields can be found.

The syntax format is as follows:

SELECT * FROM table name

Example:

Create database test; # create a library

Use test; # enter the library

Create table info (id int not null,name char (6), score decimal (5jue 2), age int (4); # create tables

Insert test data

Insert into info (id,name,score,age) values

Insert into info (id,name,score,age) values (2 records48 and 31)

Insert into info (id,name,score,age) values

Insert into info (id,name,score,age) values (4 recordings, pwelling pwagons, 98, 25)

Insert into info (id,name,score,age) values (5 recorders, wlkstores, 19,537)

Insert into info (id,name,score,age) values (3 recordsLihuaZhi 58 Magi 23)

SELECT * FROM info

two。 Query the specified field

A single table query refers to querying the required data from a table. All query operations are relatively simple.

Query specified fields can use the following syntax format:

SELECT field name FROM table name

Example:

SELECT name FROM info

Query results:

3. Query specified data

If you want to query a specified record from many records, you need a query condition. The WHERE clause is applied to set the query condition. Many complex conditional queries can be realized through it. When using the WHERE clause, you need to use some comparison operators to determine the conditions of the query.

Example:

SELECT * FROM info WHERE name = 'lisi'

Query results:

4. Query with IN keyword

The IN keyword determines whether the value of a field is in the specified collection. If the value of the field is in the collection, the query condition is met and the record will be queried; if it is not in the collection, the query condition is not met.

The syntax format is as follows:

SELECT * FROM table name WHERE condition [NOT] IN (element 1, element 2, … , element n)

Example:

SELECT * FROM info WHERE name in ('lisi','pw')

Query results:

SELECT * FROM info WHERE name not in ('lisi','kk')

Query results:

5. Range query with BETWEEN AND

The BETWEEN AND keyword determines whether the value of a field is within a specified range. If the value of the field is within the specified range, the query condition is met and the record will be queried. If it is not within the specified range, the query criteria are not met.

The syntax is as follows:

SELECT * FROM table name WHERE condition [NOT] BETWEEN value 1 AND value 2

Example:

SELECT * FROM info WHERE age BETWEEN 25 AND 35

Query results:

Friendly reminder: including the marginal value, 25 and 35 are eligible.

6. Character matching query with LIKE

LIKE is a commonly used comparison operator, through which fuzzy query can be realized.

It has two wildcards: "%" and underscore "_"

Example:

SELECT * FROM info WHERE name LIKE'% w%'

Query results:

7. Query null values with the IS NULL keyword

The IS NULL keyword can be used to determine whether the value of a field is null (NULL). If the value of the field is null, the query condition is met and the record is queried. If the value of the field is not null, the query condition is not met.

The grammatical style is as follows:

IS [NOT] NULL

Example:

SELECT * FROM info WHERE age is not NULL

Query results:

8. Multi-conditional query with AND

The AND keyword can be used to combine multiple conditions for query. When using the AND keyword, only records that meet all the query criteria at the same time are queried. If one of these query conditions is not met, such records will be excluded.

The syntax format of the AND keyword is as follows:

Select * from data table name where condition 1 and condition 2 [… AND conditional expression n]

Example:

SELECT * FROM info WHERE age = 33 and name = 'san'

Query results:

9. Multi-conditional query with OR

The OR keyword can also be used to combine multiple conditions for a query, but unlike the AND keyword, the record will be queried as long as one of the query conditions is met by the OR keyword; if any of these query conditions are not met, such records will be excluded.

The syntax format of the OR keyword is as follows:

Select * from data table name where condition 1 OR condition 2 [… OR conditional expression n]

Example:

SELECT * FROM info WHERE age = 23 or name = 'san'

Query results:

10. Use the DISTINCT keyword to remove duplicate lines in the result

Use the DISTINCT keyword to remove duplicate records from query results

The syntax format is as follows:

Select distinct field name from table name

Example:

Select distinct score from info

Query results:

Before going to the heavy duty:

11. Sort the query results with the ORDER BY keyword

Using ORDER BY, you can sort the results of a query in ascending (ASC) and descending (DESC) order, and by default, ORDER BY outputs the results in ascending order. If you want to sort in descending order, you can use DESC to do so.

The syntax format is as follows:

ORDER BY field name [ASC | DESC]

Example:

Select * from info ORDER BY score

Query results:

twelve。 Group query with GROUP BY keyword

Through the GROUP BY clause, the data can be divided into different groups, and the records can be queried in groups. When querying, the queried columns must be included in the grouped columns so that there is no contradiction in the queried data.

Example:

Select id,age from info GROUP BY age

Query results:

13. Limit the number of query results with LIMIT

When querying data, many records may be queried. The records that users need may be only a small part of them. This requires limiting the number of query results. LIMIT is a special keyword in MySQL. The LIMIT clause can limit the number of records of a query result and control the number of rows it outputs.

Example:

Select * from info ORDER BY score LIMIT 3

Query results:

After reading the above, have you learned how to use select statements to query single tables? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.