Shulou(Shulou.com)05/31 Report--

This article is about how MySQL implements single-table queries. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

A single table query refers to querying the required data from a table.

(1) query all fields

(2) query specified fields

(3) query specified records

(4) query with in keyword

(5) query with between and scope

(6) character matching query with like

(7) query null value

(8) Multi-conditional query with and

(9) Multi-conditional query with or

(10) query results are not duplicated.

(11) sort query results

(12) grouping query

(13) use limit to limit the number of query results

(1) query all fields

1.select * from fruits

2.select f_id,s_id,f_name,f_price from fruits

The result of the execution of the above two statements is to query all fields:

Mysql > select * from fruits +-+ | f_id | s_id | f_name | f_price | +-+ | 12 | 104 | lemon | 6.40 | | A1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | bs1 | 102 | orange | bs2 | melon | 8.20 | c0 | 101 | cherry | 3.20 | M1 | 106 | mango | 15.70 | m2 | 105 | xbabay | 2 | . 60 | | m3 | 105 | xxtt | 11.60 | | O2 | 103 | coconut | 9.20 | T1 | 102 | banana | 10.30 | | T2 | 102 | grape | 5.30 | t4 | xbabay | 3.60 | +-+ 16 rows in set (0.00 sec) (2) query the specified field

1. Query a single field: select column name from table name

[example] query all fruit names in f_name column in fruits table. The SQL statement is as follows:

Mysql > select f_name from fruits;+-+ | f_name | +-+ | lemon | | apple | | apricot | | blackberry | | berry | | xxxx | | orange | | melon | cherry | | mango | | xbabay | | xxtt | | banana | | grape | xbabay | +-+ 16 rows in set (0.00 sec) |

two。 Query multiple fields: select field name 1, field name 2. Field name n from table name

[example] get two columns named f_name and f_price from the fruits table, and the SQL statement is as follows:

Mysql > select fanciname favored price from fruits +-+-+ | f_name | f_price | +-+-+ | lemon | 6.40 | apple | 5.20 | apricot | 2.20 | blackberry | 10.20 | berry | 7.60 | xxxx | 3.60 | orange | 11.20 | | melon | | 8.20 | | cherry | 3.20 | mango | 15.70 | xbabay | 2.60 | xxtt | 11.60 | coconut | 9.20 | banana | 10.30 | grape | 5.30 | xbabay | 3.60 | +-+ 16 rows in set (0.00 sec) (3) query the specified record select field name 1 | Field name 2,. , field name nfrom table name where query condition

[example 1] to query the name of a fruit with a price of 10.2 yuan, the SQL statement is as follows:

Mysql > select fanciname row in set-> from fruits-> where f_price = 10.2 sec + | f_name | f_price | +-+-+ | blackberry | 10.20 | +-+-+ 1 sec)

[example 2] find the price of the fruit named "apple". The SQL statement is as follows:

Mysql > select fanciname row in set-> from fruits-> where f_name = 'apple';+-+-+ | f_name | f_price | +-+-+ | apple | 5.20 | +-+-+ 1 sec

[example 3] to query the name of a fruit whose price is less than 10, the SQL statement is as follows:

Mysql > select fanciname where s_id in-> from fruits-> where f_price select scuttledline fanciname journal fallible price-> from fruits-> where s_id in (101102)-> order by f_name +-+ | s_id | f_name | f_price | +-+ | 101 | apple | 5.20 | 102 | banana | 10.30 | | 101 | blackberry | 10.20 | | 101 | cherry | 3.20 | 102 | grape | | 5.30 | | 102 | orange | 11.20 | +-+ 6 rows in set (0.00 sec) |

[example 2] query all records whose s_id is neither 101nor 102.The SQL statement is as follows:

Mysql > select swatches, from fruits, fame, order by f_name-> price-> where s_id not in (101102)-> +-+ | s_id | f_name | f_price | +-+ | 103 | apricot | 2.20 | 104 | berry | 7.60 | 103 | coconut | 9.20 | 104 | lemon | 6.40 | mango | 15.70 | 105 | melon | | 8.20 | xbabay | 2.60 | | xbabay | 3.60 | xxtt | 11.60 | 107 | xxxx | 3.60 | +-+ 10 rows in set (0.00 sec) (5) query with between and

Between and is used to query values within a range, and the operator requires two parameters, the start and end values of the range.

[example 1] to query the names and prices of fruits with prices ranging from 2.00 yuan to 10.20 yuan, the SQL statement is as follows:

Mysql > select fanciname and price from fruits where f_price between 2.00 +-+-+ | f_name | f_price | +-+-+ | lemon | 6.40 | apple | 5.20 | apricot | 2.20 | blackberry | 10.20 | berry | 7.60 | xxxx | 3.60 | melon | 8.20 | | cherry | | 3.20 | | xbabay | 2.60 | | coconut | 9.20 | grape | 5.30 | | xbabay | 3.60 | +-+-+ 12 rows in set (0.00 sec) |

[example 2] to query the names and prices of fruits with prices ranging from 2.00 yuan to 10.20 yuan, the SQL statement is as follows

Mysql > select fanciname and and 10.20-> from fruits-> where f_price not between 2.00 +-+-+ | f_name | f_price | +-+-+ | orange | 11.20 | mango | 15.70 | xxtt | 11.60 | banana | 10.30 | +-+ 4 rows in set (0.00 sec) (6) character matching query with like

1.% wildcard characters that match characters of any length, even zero characters.

[example 1] look for all fruits that begin with the letter "b". The SQL statement is as follows:

Mysql > select flip name-> from fruits-> where f_name like'b% conversation + | f_id | f_name | +-+-+ | B1 | blackberry | | b2 | berry | | T1 | banana | +-+-+ 3 rows in set (0.00 sec)

[example 2] in the fruits table, query the records that contain the letter "g" in f_name. The SQL statement is as follows:

Mysql > select fanciidgravity fanciname-> from fruits-> where f_name like'% g% talk about rows in set + | f_id | f_name | +-+-+ | bs1 | orange | | M1 | mango | | T2 | grape | +-+-+ 3 rows in set (0.00 sec)

[example 3] query the name of the fruit that begins with "b" and ends with "y". The SQL statement is as follows:

Mysql > select fanciidthecompany name-> from fruits-> where f_name like'b% yawning% rows in set + | f_id | f_name | +-+-+ | B1 | blackberry | | b2 | berry | +-+-+ 2 rows in set (0.00 sec)

2. _ wildcard, which can only match any one character at a time.

[example] in the fruits table, the query ends with the letter'y', and there is only a 4-letter record before'y'. The SQL statement is as follows:

Mysql > select _ to query null values. | f_id | f_name | +-+-+ | b2 | berry | +-+-+ 1 row in set (0.00 sec) (7) query null values

When the datasheet is created, you can specify whether a column can contain a null value of null. A null value is different from a 0 or an empty string. Null values generally indicate the location of the data, are not used, or data will be added later. Using the is null clause in the select statement, you can query that a field has an empty record.

The following data table customers is created to demonstrate:

Create table customers (c_id int not null auto_increment, c_name char 50) not null, c_address char 50 null, c_city char 50 null, c_zip char 10 null, c_contact char 50 null, c_email char 255 null, primary key (c_id))

Insert the following statement:

Mysql > insert into customers-> values-> (200Street, 'Tianjin','300000','LiMing','LMing@163.com'),-> (10002 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotmail.com', 333 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotmail.com'),-> (10003 Sunny Place' 'Qingdao','266000','LuoCong',null),-> (10004 Riverside Drive','Haikou','570000','YangShan','sam@hotmail.com') Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0

[example 1] query the c_id, c_name, and c_email field values of records with empty c_email in the customers table. The SQL statement is as follows:

Mysql > select clockwork cymbal email from customers where c_email is null +-+ | c_id | c_name | c_email | +-+ | 10003 | Netbhood | NULL | +-+ 1 row in set (10003 sec)

[example 2] query the c_id, c_name, and c_email field values of records whose c_email is not empty in the customers table. The SQL statement is as follows:

Mysql > select clockwork, centername, where c_email is not null, email-> from customers-> where c_email is not null +-+ | c_id | c_name | c_email | +-+ | 10001 | redhool | LMing@163.com | | 10002 | Stars | Jerry@hotmail.com | | 10004 | JOTO | | sam@hotmail.com | +-+ 3 rows in set (0.00 sec) (8) Multi-conditional query with and |

[example 1] query the s_id=101 in the fruits table and the price and name of the fruit with f_price greater than 5. The SQL statement is as follows:

Mysql > select faddish and f_price-> from fruits-> where s_id = '101' and f_price > = 5 +-+ | f_id | f_price | f_name | +-+ | A1 | 5.20 | apple | | b1 | 10.20 | blackberry | +- -+ 2 rows in set (0.00 sec)

[example 2] query s_id=101 or 102in the fruits table, and the f_price is greater than 5, and the fruit price and name of the fancinamefruit apple` are as follows:

Mysql > select f_id, f_price, f_name-> from fruits-> where s_id in ('101') and f_price > = 5 and f_name = 'apple' +-+ | f_id | f_price | f_name | +-+ | A1 | 5.20 | apple | +-+ 1 row in set (0.05sec) (9) Multi-conditional query with or

[example 1] the following SQL statements are used to query the fruit suppliers of s_id=101 or s_id=102:

Mysql > select where s_id=101 or s_id=102-> from fruits-> where s_id=101 or s_id=102 +-+ | s_id | f_name | f_price | +-+ | 101 | apple | 5.20 | 101 | blackberry | 10.20 | 102 | orange | 11.20 | | 101 | cherry | 3.20 | 102 | banana | | 10.30 | | 102 | grape | 5.30 | +-+ 6 rows in set (0.00 sec) |

[example 2] the following SQL statements are used to query the fruit suppliers of s_id=101 or s_id=102:

Mysql > select sprints, from fruits, fame, where s_id in (101102) +-+ | s_id | f_name | f_price | +-+ | 101 | apple | 5.20 | 101 | blackberry | 10.20 | 102 | orange | 11.20 | | 101 | cherry | 3.20 | 102 | banana | | 10.30 | | 102 | grape | 5.30 | +-+ 6 rows in set (0.00 sec) |

Be careful

Or can be used with and, but pay attention to the priority of both. Because and takes precedence over or, you first operate on the operands on both sides of and, and then combine them with the operands in or.

(10) query results are not duplicated.

Syntax format: select distinct field name from table name

[example] query the value of the s_id field in the fruits table and return the value of the s_id field and must not repeat it. The SQL statement is as follows

Mysql > select distinct s_id from fruits;+-+ | s_id | +-+ | 104To sort the query results.

1. Single column sort order by

[example] query and sort the f_name field values of the fruits table. The SQL statement is as follows:

Mysql > select f_name from fruits order by fancinamebomacher + | f_name | +-+ | apple | | apricot | | banana | | berry | | blackberry | | cherry | | coconut | | grape | | lemon | | mango | | orange | | xbabay | | xbabay | xxtt | | xxxx | +-+ 16 rows in set (0.00 sec) |

two。 Multi-column sorting

Multi-column sorting the first column sorted first must have the same column value before the second column is sorted. If all values in the first column of data are unique, the second column is no longer sorted.

[example] query the f_name and f_price fields in the fruits table. Sort by f_name first, and then by f_price. The SQL statement is as follows:

Mysql > select fanciname _ +-+-+ | f_name | f_price | +-+-+ | apple | 5.20 | apricot | 2.20 | banana | 10.30 | berry | berry | blackberry | 10.20 | cherry | 3.20 | coconut | 9.20 | | grape | 5.30 | | lemon | 6.40 | | mango | 15.70 | melon | 8.20 | orange | xbabay | 2.60 | xbabay | 3.60 | xxtt | 11.60 | xxxx | 3.60 | +-+ 16 rows in set (0.00 sec)

3. Specify sort direction desc

Desc is in descending order, as opposed to asc ascending order, but asc is the default and can be left out.

[example 1] query the f_name and f_price fields in the fruits table, and sort the results in descending order of f_price. The SQL statement is as follows:

Mysql > select fanciname, farewell price-> from fruits-> order by f_price desc +-+-+ | f_name | f_price | +-+-+ | mango | 15.70 | xxtt | 11.60 | orange | 11.20 | banana | 10.30 | blackberry | blackberry | coconut | 9.20 | melon | 8.20 | | berry | | 7.60 | | lemon | 6.40 | | grape | 5.30 | apple | 5.20 | xxxx | 3.60 | xbabay | 3.60 | cherry | 3.20 | xbabay | 2.60 | apricot | 2.20 | +-+ 16 rows in set (0.00 sec) |

[example 2] query the fruits table, first sort by f_price descending order, and then sort by f_name field ascending order. The SQL statement is as follows:

Mysql > select fond name-> from fruits-> order by f_price desc,f_name +-+-+ | f_price | f_name | +-+-+ | 15.70 | mango | 11.60 | xxtt | 11.20 | orange | | 10.30 | banana | | 10.20 | blackberry | | 9.20 | coconut | | 8.20 | melon | | 7 .60 | berry | | 6.40 | lemon | | 5.30 | grape | | 5.20 | apple | xbabay | | 3.60 | xxxx | | 3.20 | cherry | | 2.60 | xbabay | 2.20 | apricot | +-+-+ 16 rows in set (0.00 sec) (12) packet query

A grouping query is to group data according to one or more fields. In MySQL, the data is grouped using the group by keyword. The basic syntax form is: [group by field] [having]

The field value is the column name by which the grouping is made, and "having" specifies that the result that meets the qualification of the expression will be displayed.

1. Create grouping

[example 1] the data in the fruits table is grouped according to s_id, and the SQL statement is as follows

Mysql > select account (*) as total-> from fruits-> group by sec + | s_id | total | +-+-+ | 104 | 2 | 101 | 3 | 103 | 2 | 107 | 2 | 102 | 3 | 105 | 3 | 106 | 1 | +-+-+ 7 sec)

You can see that the group by clause is sorted by s_id and grouped the data.

If you need to see the name of the fruit category provided by each vendor, you can use the group_concat () function in the group by clause to display the values of each field in each group.

[example 2] Group the data in the fruits table according to s_id, and display the fruit name of each supplier. The SQL statement is as follows:

Mysql > select sprints, f_name, groupconcat (f_name) as Names-> from fruits-> group by s_id +-+-+ | s_id | Names | +-+-+ | 101 | apple,blackberry,cherry | 102 | orange,banana,grape | | 103 | apricot,coconut | 104 | lemon,berry | | 105 | melon,xbabay Xxtt | | 106 | mango | | 107 | xxxx,xbabay | +-+-+ 7 rows in set (0.05sec)

two。 Use having to filter packets

Groub by can work with having to define the conditions required to display records, and only groups that meet the conditions will be displayed.

Both having and where are used to filter data. Having filters after data packets to select packets, while where is used to select records before grouping. Records excluded by where are no longer included in the grouping.

[example] the data in the fruits table are grouped according to s_id, and the grouping information with fruit category greater than 1 is displayed. The SQL statement is as follows:

Mysql > select sprints, as Names, groupconcat (f_name)-> from fruits-> group by s_id having count (f_name) > 1 +-+-+ | s_id | Names | +-+-+ | 101 | apple,blackberry,cherry | 102 | orange,banana,grape | | 103 | apricot,coconut | 104 | lemon,berry | | 105 | melon,xbabay Xxtt | | 107 | xxxx,xbabay | +-+-+ 6 rows in set (0.00 sec)

3. Using with rollup in the group by clause

After using the with rolluo keyword, a record is added after all the queried grouped records, which calculates the sum of all the queried records, that is, the number of records.

[example] the data in fruits table is grouped according to s_id, and the number of records is displayed. The SQL statement is as follows:

Mysql > select as Total count (*) from fruits-> group by s_id with rollup +-+-+ | s_id | Total | +-+-+ | 101 | 3 | 102 | 3 | 103 | 2 | 104 | 2 | 105 | 3 | 106 | 1 | 107 | 2 | NULL | 16 | +-+-+ 8 rows in set (0.05sec)

4. Multi-field grouping

Multiple fields can be grouped using group by, the group by keyword is followed by the fields that need to be grouped, MySQL is grouped hierarchically according to the values of multiple fields, and the grouping hierarchy is from left to right, that is, first grouped by the first field, and then grouped according to the value of the second field in the record with the same value of the first field, and so on.

[example] the data in the fruits table is grouped according to the s_id and f_name fields. The SQL statement is as follows:

Mysql > select * from fruits group by sprints favored name +-+ | f_id | s_id | f_name | f_price | +-+ | 12 | 104 | lemon | 6.40 | | A1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | bs1 | 102 | orange | bs2 | melon | 8.20 | c0 | 101 | cherry | 3.20 | M1 | 106 | mango | 15.70 | m2 | 105 | xbabay | 2 | .60 | | m3 | 105 | xxtt | 11.60 | | O2 | 103 | coconut | 9.20 | T1 | 102 | banana | 10.30 | | T2 | 102 | grape | 5.30 | | T4 | xbabay | 3.60 | +-+ 16 rows in set (0.00 sec)

5.group by is used with order by

In some cases, you need to sort the grouping. Order by is used to sort the records of the query, and you can sort the grouping if you use it with group by.

Create a datasheet demo:

Mysql > create table orderitems-> (- > o_num int not null,-> o_item int not null,-> f_id char (10) not null,-> quantity int not null,-> item_price decimal (8 ~ 2) not null,-> primary key Query OK, 0 rows affected (0.13 sec) mysql > insert into orderitems-> values (30001)-> values (30001)-> (30001)-> (30001)-> (30001)-> (30001)-> (30001)-> (30001)-> (30001)-> (30001)-> (30001)-> (30001)-> (30001)-> (30001)-> (30002) (30004) > (30004)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (30005)-> (3000 Query OK, 11 rows affected (0.06 sec) Records: 11 Duplicates: 0 Warnings: 0

[example] query the order number and the total order price for which the order price is greater than 100. the SQL statement is as follows

Mysql > select ohnum sum (quantity * item_price) as orderTotal-> from orderitems-> group by o_num-> having sum (quantity * item_price) > = 100 +-+-+ | o_num | orderTotal | +-+-+ | 30001 | 268.80 | | 30003 | 1000.00 | 30004 | 125.00 | | 30005 | 236.85 | +-+-+ 4 rows in set (sec)

You can see that the total order price of the orderTotal column is not displayed in a certain order. Next, use the order by keyword to display the results by the total order price. The SQL statement is as follows:

Mysql > select ohnum sum (quantity * item_price) as orderTotal-> from orderitems-> group by o_num-> having sum (quantity * item_price) > = 100-> order by orderTotal +-+-+ | o_num | orderTotal | +-+-+ | 30004 | 125.00 | | 30005 | 236.85 | 30001 | 268.80 | | 30003 | 1000.00 | +-+-+ 4 rows in set (sec)

As you can see, the group by clause groups the data by order number, and the sum () function returns the total order price. The having clause filters the grouped data so that only orders with a total price greater than 100 are returned, and the output is sorted using the order by clause.

(13) use limit to limit the number of query results

Select returns all matching rows, possibly all rows in the table. If you only need to return the first row or the first few rows, use the limit keyword. The basic syntax is as follows:

Limit [position offset,] number of rows

The first "position offset" parameter is only the row from which the MySQL is displayed and is an optional parameter. If you do not specify the "position offset", it will start from the first record in the table (the position offset of the first record is 0, the position offset of the second record is 1, and so on); the second parameter "the number of rows indicates the number of records returned".

[example 1] displays the first four rows of the query result of the fruits table, with the following SQL statement:

Mysql > select * from fruits limit 4 +-+ | f_id | s_id | f_name | f_price | +-+ | 12 | 104 | lemon | 6.40 | | A1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | B1 | 101 | blackberry | 10.20 | +-+ 4 rows in set (0.00 sec) |

[example 2] in the fruits table, use the limit clause to return a record with a length of 3 rows starting from the fifth record, as shown in the SQL statement:

Mysql > select * from fruits limit 4 +-+ | f_id | s_id | f_name | f_price | +-+ | b2 | 104 | berry | 7.60 | b5 | 107 | xxxx | 3.60 | | bs1 | 102 | orange | 11.20 | +- -+ 3 rows in set (0.00 sec)

You can also use "limit 4 offset 3" to get the next three records starting with the fifth record.

Thank you for reading! This is the end of the article on "how to achieve single-table query in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.