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

Note: this blog post is based on the table in the previous post, the previous post: MySQL basic query example (1).

1. Query all the f_name values corresponding to each s_id in the fruits table mysql > select sdistribuidPowergrouproomconcat (f_name) as name from fruits group by s_id having count (f_name) > 1

The returned result is as follows:

2. Count the number of rows with the same Sid value? Mysql > select as total count (*) from fruits-> group by s_id with rollup

Note: the function of with rollup is to add the grouped s_id and the total number, that is, 16.

3. Create a new table and insert the data 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 (ohemagrinominominal item)->) Mysql > insert into orderitems,-> values (30001meme, 1meme, pyrrine, 5pyrus, 5.2'),-> (30001meme, 2meme, 2pyrum, 3pyrum, 3pyrum, 7.6'),-> (30001pr, 3mlt, bs1pl, 5pr, 11.2'),-> (30001mr4, ms2pl, 15pr, 9.2'),-> (30002pm, 1pyrum, b3pyrm, 2pas, 20.0'),-> (30003ref. ),-> (30004),-> (30004),-> (30005),-> (30005),-> (30005),-> (30005),-> (30005),-> (30005),-> (30005),-> (30005),-> (30004),-> (30005),-> (30005),-> (30004),-> (30005),-> (30005),-> (30004)-> (30004)-> (30004)-> (30004)-> (30004)-> (30004)-> (30004)-> (30004)-> (30004)-> (30005)-> (30004)-> (30004)-> (30005)-> (30005)-> (30004)-> (30004)-> (30004)-> (30005)-> (30004)-> (30004)-> (30005)-> (30005)-> (30004)-> (

View the data in the table as follows:

4. Query the rows of quantity (quantity) and item_price (price) in the same o_num column whose multiplication result is greater than 100. mysql > select ohlnum order by total;5 as total from orderitems-> group by o_num having total > 100order by total;5, limit-- limit the number of rows returned: 1:mysql > select * from fruits limit 4

The returned result is as follows:

Restrict 2:mysql > select * from fruits limit 4

The returned result is as follows:

6. Query the f_id corresponding to each o_num. There are several mysql > select ointnumCool count (f_id) as items_total-> from orderitems-> group by o_num.

The result returned is as follows:

7. Query the number of quantity (quantity) with o_num of 30005 mysql > select sum (quantity) as items_total-> from orderitems-> where o_num = 30005

The result returned is as follows:

8. Query the average number of f_price whose s_id is 103 (what is the average price of s_id) mysql > select avg (f_price) as avg_price from fruitss where s_id = 103

The result returned is as follows:

9. What is the average price (f_price) corresponding to each s_id? mysql > select select f_price avg (f_price) price

The result returned is as follows:

10. Which row in each s_id has the highest fprice value? Mysql > select s_id, max (f_price) as max_price from fruits group by s_id

The result returned is as follows:

Similarly, to see the smallest row, simply replace max with min.

11. Query the maximum value of each fprice and its corresponding s_id and f_name. Mysql > select select max (f_price) from fruits group by s_id-> where f_price in

The result returned is as follows:

12. Create the required table again and insert data mysql > create table suppliers-> (- > s_id int not null auto_increment,-> s_name char (50) not null,-> s_city char (50) null,-> s_zip char (10) null,-> s_call char (50) not null,-> primary key (s_id)->) Mysql > create table orders-> (- > o_num int not null auto_increment,-> o_date datetime not null,-> c_id int not null,-> primary key (o_num)->) Mysql > insert into suppliers-> values (101 values FastFruit Inc.','tianjin','300000','48075'),-> (102 Supplies','chongqing','400000','44333' Lt call),-> (103 inc.','zhongshan','528437','11111' acmetical inc.','zhongshan','528437','11111' 90046'),-> (104 inc.','zhongshan','528437','11111' FNK) -> (105 perfect good set','taivuang','030000','22222'),-> (106 recollection just eat ours','beijing','010','45678'),-> (107 recorder dk inc.','zhengzhou','450000','33332') Mysql > insert into orders-> values (30001, 2008-09-01),-> (30002, 2008-09-12) 10003),-> (30003, 2008-09-30),-> (30004, 2008-10-03),-> (30005, 2008-10-03),-> (30005, 2008-10-08)

Before proceeding to the next query, it is necessary to talk about the concepts of multi-table query.

1) Inner connection

Inner join (inner join) is the most common form of join, which only returns rows that match the relationship between two data sets and joins data rows that are within the overlap of two intersecting data sets.

The inner join uses the comparison operator to compare the data of certain columns between tables and lists the rows of data in those tables that match the join.

2) external connection

The outer join (outer join) is an extension of the inner join. In addition to joining the data rows within the repeating parts of the two data sets, it can also return unmatched data or all the data in the left or right side table as required.

Outer joins can also be divided into the following categories:

The result of the left outer join (left join or left outer join) includes all rows of the left table. If a row of the left table does not match in the right table, the right table returns a null value, otherwise it returns the corresponding value.

The right outer join (right join or right outer join) is the reverse join of the left outer join, which returns all rows of the right table. If a row of the right table does not match in the left table, the left table returns a null value, otherwise it returns the corresponding value.

A full join (full join or full outer join) returns all rows in the left and right tables, and when one row has no matching row in another table, the other table returns a null value, otherwise it returns the corresponding value.

14. Inner join query to generate a new table mysql > select suppliers.s_id,s_name,f_name,f_price from fruits inner join suppliers on fruits.s_id = suppliers.s_id from the specified columns of the two tables

The result returned is as follows:

15. Example of left outer join query mysql > select customers.c_id,orders.o_num from customers-> left outer join orders on customers.c_id = orders.c_id->

The returned result is as follows:

16. Specify other conditions mysql > select customers.c_id,orders.o_num from customers-> left outer join orders on customers.c_id = orders.c_id-> when inner join query

The returned result is as follows:

-this is the end of this article. 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.