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

1. Query all f ﹣ name values corresponding to each s ﹣ ID in the fruits table

<!--Compact in groups-->
mysql> select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1;

The results are as follows:

2. What are the rows with the same s ﹣ u ID value?

mysql> select s_id,count(*) as total
    -> from fruits
    -> group by s_id with rollup;

Note: the function of with roll up is to add the sum of the grouped s ﹣ IDs and then count the total number, that is, 16.

3. Create a new table and insert data

<!--Create new table-->
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(o_num,o_item)
    -> );
<!--insert data-->
mysql> insert into orderitems(o_num,o_item,f_id,quantity,item_price)
    -> values(30001,1,'a1',10,'5.2'),
    -> (30001,2,'b2',3,'7.6'),
    -> (30001,3,'bs1',5,'11.2'),
    -> (30001,4,'bs2',15,'9.2'),
    -> (30002,1,'b3',2,'20.0'),
    -> (30003,1,'c0',100,10),
    -> (30004,1,'o2',50,'2.50'),
    -> (30005,1,'c0',5,'10'),
    -> (30005,2,'b1',10,'8.99'),
    -> (30005,3,'a2',10,'2.2'),
    -> (30005,4,'m1',5,'14.99');

The data in the view table is as follows:

4. Query the row whose multiplication result of quantity and item price of the same o'num column is greater than 100

mysql> select o_num,SUM(quantity*item_price) as total from orderitems
    -> group by o_num having total > 100 order by total;

5. Limit -- limit the number of rows returned

Limit 1:

<!--Show only the first four rows in the table-->
mysql> select * from fruits limit 4;

The results are as follows:

Limit 2:

<!--Starting from the fourth line, display the next three lines-->
mysql> select * from fruits limit 4,3;

The results are as follows:

6. Query how many f ﹣ IDS correspond to each o ﹣ num

mysql> select o_num,count(f_id) as items_total 
    -> from orderitems
    -> group by o_num;

The results returned are as follows:

7. Query the quantity of 30005

mysql> select sum(quantity) as items_total
    -> from orderitems
    -> where o_num = 30005;

The results returned are as follows:

8. Query the average number of F price with s UU ID 103 (what is the average price of s UU ID)

mysql> select avg(f_price) as avg_price from fruitss where s_id = 103;

The results returned are as follows:

9. What is the average price corresponding to each s ﹣ ID?

mysql> select s_id,avg(f_price) as avg_price from fruits group by s_id;

The results returned are as follows:

10. Query the row with the largest value of F ﹣ price in each s ﹣ ID?

mysql> select s_id, max(f_price) as max_price from fruits group by s_id;

The results returned are as follows:

Similarly, to see the smallest row, simply change max to min.

11. Query the maximum value of each f ﹣ price and its corresponding s ﹣ ID and f ﹣ name.

mysql> select s_id,f_price,f_name from fruits
    -> where f_price in(select max(f_price) from fruits group by s_id);

The results returned are as follows:

12. Create the required table again and insert the data

<!--Create table-->
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)
    -> );
<!--insert data-->
mysql> insert into suppliers(s_id,s_name,s_city,s_zip,s_call) 
    -> values(101,'FastFruit Inc.','tianjin','300000','48075'),
    -> (102,'LT Supplies','chongqing','400000','44333'),
    -> (103,'acme','shanghai','200000','90046'),
    -> (104,'fnk inc.','zhongshan','528437','11111'),
    -> (105,'good set','taivuang','030000','22222'),
    -> (106,'just eat ours','beijing','010','45678'),
    -> (107,'dk inc.','zhengzhou','450000','33332');
mysql> insert into orders(o_num,o_date,c_id)
    -> values(30001,'2008-09-01',10001),
    -> (30002,'2008-09-12',10003),
    -> (30003,'2008-09-30',10004),
    -> (30004,'2008-10-03',10005),
    -> (30005,'2008-10-08',10001);

13. Concept of table join type

In the next query, it is necessary to talk about the related concepts of multi table query.

1) internal connection

inner join is the most common join method. It only returns the rows of matching relationship between two data sets, and joins the data rows within the overlapping part of two cross data sets.

Inner joins use comparison operators to compare some column data between tables and list the data rows in these tables that match the join.

2) external connection

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

The external connection can also be divided into the following types:

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

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

Full join (full join or full outer join) will return all the rows in the left table and the right table. When one row does not match in another table, the other table will return a null value. Otherwise, the corresponding value will be returned.

14. Inner join query, generate a new table from the specified columns of two tables

mysql> select suppliers.s_id,s_name,f_name,f_price from fruits inner join suppliers on fruits.s_id = suppliers.s_id;

The results returned are 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 results are as follows:

16. Specify other conditions during inner join query

mysql> select customers.c_id,orders.o_num from customers 
    -> left outer join orders on customers.c_id = orders.c_id
    -> ;

The results are as follows:

————————Thank you for reading————————