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

This article will explain in detail how to achieve single-table query operation in MySQL. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Create the fruits table:

CREATE TABLE fruits (f_id char (10) NOT NULL, s_id INT NOT NULL, f_name char (255) NOT NULL, f_price decimal (8) NOT NULL, PRIMARY KEY (f_id)) INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('a1century, 101 recordings applegoat page5. 2), (' b1ZWE 101 miniseries blackberries, 10.2), ('bs1',102,'orange', 11.2), (' bs2',105,'melon',8.2), ('t1meme 102meme bananas, 10.3), (' t2jewelry page102) grapestones, 5.3) (), ('c0), ('c0), ('a2), (()), ((), (()), () (), (()), (()) () ( 11.6), ('b5 recording, 107, recording xxxxx, 3.6)

Common queries:

SELECT * FROM fruits;select fancinamedirection fanciprice from fruits where faddish pricewise 10.2 * select fanciprice from fruits where favoprice5 name.select fanciprice from fruits where s_id in (101102) and f_price > = 5 and fancinamee = "apple"; select fancinamememe fanciprice from fruits where s_id=101 or s_id=102

The operation with in is more simple and clear.

Select fanciname favored pricefrom fruitswhere s_id in (101,102)

Fields are not duplicated

SELECT DISTINCT s_id FROM fruits;select f_name from fruits ORDER BY f_name

If there is no same value in the first column data, the second column will no longer be sorted.

SELECT f_name, f_priceFROM fruitsORDER BY f_name, f_price

Sort by price descending order, desc is descending order, default is ascending order.

SELECT f_name, f_price FROM fruits ORDER BY f_price desc;SELECT f_name, f_price FROM fruits ORDER BY f_price desc,f_name;SELECT s_id, COUNT (*) AS Total FROM fruits GROUP BY selected s_id, GROUP_CONCAT (f_name) AS Names FROM fruits GROUP BY s_id

Use having to filter packets

SELECT s_id, GROUP_CONCAT (f_name) AS NamesFROM fruitsGROUP BY s_id having count (f_name) > 1

Using with rollup in the group by clause

SELECT s_id, COUNT (*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP;SELECT * from fruits group by

Create the orderitems table:

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 Magazine 2) NOT NULL, PRIMARY KEY (ohmum numminomo item)) INSERT INTO orderitems (o_num, o_item, f_id, quantity, item_price) VALUES (30001, 1,'a _ 1, 10,5.2), (30001, 2,'b _ 2, 3,7.6), (30001, 3, 'bs1', 5,11.2), (30001, 4,' bs2', 15,9.2), (30002, 1,'b _ 3, 2,20.0), (30003, 1,'c _ 0, 100,10), (30004, 1) (30005, 1,'c0, 5, 10), (30005, 2,'b1, 10, 8.99), (30005, 3,'a2, 10, 2.2), (30005, 4,'M1, 5,14.99)

Common query statements:

SELECT o_num, SUM (quantity*item_price) AS orderTotalFROM orderitemsGROUP BY o_numHAVING SUM (quantity*item_price) > = 100th select o_num, SUM (quantity*item_price) AS orderTotalFROM orderitemsGROUP BY o_numHAVING SUM (quantity*item_price) > = 100order by ordertotal;SELECT * From fruits LIMIT 8

Starting with the fifth line, read three lines

SELECT * From fruits LIMIT 4

[example. 1] retrieve data for all fields from the fruits table

SELECT * FROM fruits;SELECT f_id, s_id, f_name, f_price FROM fruits

[example. 2] query the names of all fruits in the f_name column of the current table and enter the following statement:

SELECT f_name FROM fruits

[example .3] for example, get the f_name and f_price columns from the fruits table and enter the following statement:

SELECT f_name, f_price FROM fruits

[example. 4] query the name of the fruit with a price of 10.2 yuan and enter the following statement:

SELECT f_name, f_priceFROM fruitsWHERE f_price = 10.2

[example .5] find the price of the fruit named "apple" and enter the following statement:

SELECT f_name, f_priceFROM fruitsWHERE f_name = 'apple'

[example. 6] query the name of the fruit whose price is less than 10, and enter the following statement:

SELECT f_name, f_priceFROM fruitsWHERE f_price

< 10; 【例.7】s_id为101和102的记录，输入如下语句： SELECT s_id,f_name, f_priceFROM fruitsWHERE s_id IN (101,102)ORDER BY f_name; 【例.8】查询所有s_id不等于101也不等于102的记录，输入如下语句： SELECT s_id,f_name, f_priceFROM fruitsWHERE s_id NOT IN (101,102)ORDER BY f_name; 【例.9】查询价格在2.00元到10.5元之间水果名称和价格 SELECT f_name, f_priceFROM fruitsWHERE f_price BETWEEN 2.00 AND 10.20; 【例.10】查询价格在2.00元到10.5元之外的水果名称和价格 SELECT f_name, f_priceFROM fruitsWHERE f_price NOT BETWEEN 2.00 AND 10.20; 【例.11】查找所有以'b'字母开头的水果，输入如下语句： SELECT f_id, f_nameFROM fruitsWHERE f_name LIKE 'b%'; 【例.12】在fruits表中，查询f_name中包含字母'g'的记录 SELECT f_id, f_nameFROM fruitsWHERE f_name LIKE '%g%'; 【例.13】查询以'b'开头，并以'y'结尾的水果的名称 SELECT f_nameFROM fruitsWHERE f_name LIKE 'b%y'; 【例7.14】在fruits表中，查询以字母'y'结尾，且'y'前面只有4个字母的记录 SELECT f_id, f_nameFROM fruitsWHERE f_name LIKE '____y'; 【例.15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值： SELECT c_id, c_name,c_emailFROM customersWHERE c_email IS NULL; 【例.16】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值 SELECT c_id, c_name,c_emailFROM customersWHERE c_email IS NOT NULL; 【例.17】在fruits表中查询s_id = '101'，并且f_price大于5的记录价格和名称 SELECT f_id, f_price, f_nameFROM fruitsWHERE s_id = '101' AND f_price >

= 5

[example. 18] query the fruits table for s_id = '101' or' 102', and the f_price is greater than 5, and the recorded price and name of the fancinameplate apple`

SELECT f_id, f_price, f_nameFROM fruitsWHERE s_id IN ('101,' 102') AND f_price > = 5 AND f_name = 'apple'

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

SELECT OR s_id fanciname, f_priceFROM fruitsWHERE s_id = 101and

[example. 20] query the f_price and f_name of fruit suppliers of s_id=101 or s_id=102

SELECT sprints, f_priceFROM fruitsWHERE s_id IN (101102)

[example .21] query the value of s_id field in fruits table, and return the value of s_id field must not be repeated.

SELECT DISTINCT s_id FROM fruits

[example .22] query and sort the f_name field values of the fruits table

Select f_name from fruits ORDER BY f_name

[example .23] query the f_name and f_price fields in the fruits table, sort by f_name first, and then by f_price

SELECT f_name, f_priceFROM fruitsORDER BY f_name, f_price

[example. 24] query the f_name and f_price fields in the fruits table, and sort the results in f_price descending order

SELECT f_name, f_priceFROM fruitsORDER BY f_price DESC

[example .25] 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:

SELECT f_price, f_nameFROM fruitsORDER BY f_price DESC, f_name

[example .26] Group the data in the fruits table according to s_id

SELECT s_id, COUNT (*) AS TotalFROM fruitsGROUP BY s_id

[example .27] grouping the data in the fruits table according to s_id to display the fruit name of each supplier

SELECT s_id, GROUP_CONCAT (f_name) AS NamesFROM fruitsGROUP BY s_id

[example .28] grouping the data in the fruits table according to s_id, and displaying grouping information with fruit species greater than 1

SELECT s_id, GROUP_CONCAT (f_name) AS NamesFROM fruitsGROUP BY s_id HAVING COUNT (f_name) > 1

[example .29] groups the data in the fruits table according to s_id and displays the number of records

SELECT s_id, COUNT (*) AS TotalFROM fruitsGROUP BY s_id WITH ROLLUP

[example .30] groups the data in the fruits table according to the s_id and f_name fields, and the SQL statement is as follows

SELECT * from fruits group by scuttlehead fanciname

[example. 31] query the order number and the total order price for which the order price is greater than 100

SELECT o_num, SUM (quantity*item_price) AS orderTotalFROM orderitemsGROUP BY o_numHAVING SUM (quantity*item_price) > = 100

[example .32] display the first four rows of the query result of the fruits table, enter the following statement:

SELECT * From fruits LIMIT 4

[example. 33] in the fruits table, use the LIMIT clause to return a record with a row length of 3 starting from the fifth record

SELECT * From fruits LIMIT 4,3; about how to implement single-table query operation in MySQL is shared here. I hope the above content can be helpful to you and 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.