MySQL基本查询示例（2）

ゝ一纸荒年。 2023-06-20 03:59 30阅读 0赞

注：本博文基于上一篇博文中的表进行查询，上篇博文：[MySQL基本查询示例（1）][MySQL_1]。

### 1、查询fruits表中每个s\_id对应的所有f\_name值 ###

mysql> select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1;

返回结果如下：

![MySQL基本查询示例（2）][MySQL_2]

### 2、统计相同s\_id值的行有多少？ ###

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

![MySQL基本查询示例（2）][MySQL_2 1]

注：with rollup的作用是将s\_id分组后的和再进行相加，统计出来的总数，也就是16。

### 3、创建一个新表并插入数据 ###

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)
        -> );
    
    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');

查看表中的数据如下：

![MySQL基本查询示例（2）][MySQL_2 2]

### 4、查询同一个o\_num列的quantity（数量）和item\_price（价格）相乘结果大于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——限制返回的行数 ###

#### 限制1： ####

mysql> select * from fruits limit 4;

返回结果如下：

![MySQL基本查询示例（2）][MySQL_2 3]

#### 限制2： ####

mysql> select * from fruits limit 4,3;

返回结果如下：

![MySQL基本查询示例（2）][MySQL_2 4]

### 6、查询每个o\_num对应的f\_id有几个 ###

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

返回的结果如下：  
![MySQL基本查询示例（2）][MySQL_2 5]

### 7、查询o\_num为30005的quantity（数量）有多少 ###

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

返回的结果如下：

![MySQL基本查询示例（2）][MySQL_2 6]

### 8、查询s\_id为103的f\_price的平均数是多少（s\_id的平均价格是多少） ###

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

返回的结果如下：

![MySQL基本查询示例（2）][MySQL_2 7]

### 9、查询每个s\_id对应的平均价格（f\_price）是多少? ###

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

返回的结果如下：

![MySQL基本查询示例（2）][MySQL_2 8]

### 10、查询每个s\_id中f\_price值最大的行是哪个？ ###

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

返回的结果如下：

![MySQL基本查询示例（2）][MySQL_2 9]

同理，若要查看最小的行，只需要将max换为min即可。

### 11、查询每个f\_price值最大的值及其所对应的s\_id、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);

返回的结果如下：

![MySQL基本查询示例（2）][MySQL_2 10]

### 12、再次创建所需表并插入数据 ###

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(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、表联接类型的概念 ###

在进行接下来的查询，这里有必要说一下多表查询的相关概念。

#### 1）内联接 ####

内联接（inner join）是最常见的一种联接方式，只返回两个数据集合之间匹配关系的行，将位于两个互相交叉的数据集合中重叠部分以内的数据行联接起来。

内联接使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与联接相匹配的数据行。

#### 2）外联接 ####

外联接（outer join）是对内联接的扩充，除了将两个数据集合中重复部分以内的数据行联接起来之外，还可以根据要求返回左侧或右侧表中非匹配的数据或全部的数据。

外联接还可以分为以下几种：

**左外联接（left join或left outer join）**的结果包括左表的所有行，如果左表的某一行在右表中没有匹配行，则右表返回空值，否则返回相应值。

**右外联接（right join或right outer join）**是左外联接的反向联接，将返回右表的所有行，如果右表的某一行在左表中没有匹配行，则左表返回空值，否则返回相应值。

**全联接（full join 或full outer join）**将返回左表和右表中的所有行，当某一行在另一个表中没有匹配行时，另一个表返回空值，否则返回相应值。

### 14、内联接查询，将两个表的指定列生成一个新表 ###

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

返回的结果如下：

![MySQL基本查询示例（2）][MySQL_2 11]

### 15、左外联接查询示例 ###

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

返回结果如下：

![MySQL基本查询示例（2）][MySQL_2 12]

### 16、内联接查询时指定其他条件 ###

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

返回结果如下：  
![MySQL基本查询示例（2）][MySQL_2 13]

———————— 本文至此结束，感谢阅读 ————————

[MySQL_1]: https://blog.51cto.com/14154700/2456603
[MySQL_2]: https://s1.51cto.com/images/blog/201912/06/1e025db27ec0167fa51ade454551e91c.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 1]: https://s1.51cto.com/images/blog/201912/06/f01953e72ab6c24fa16b2d2db941a0f9.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 2]: https://s1.51cto.com/images/blog/201912/06/55b411e993c8fb595e7c6e32d56931b3.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 3]: https://s1.51cto.com/images/blog/201912/06/01160a3abc0ef9b8f7d3cb314a97249f.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 4]: https://s1.51cto.com/images/blog/201912/06/144b2757d38669fad89f86a4c799401f.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 5]: https://s1.51cto.com/images/blog/201912/06/a1c566b3251760e7f0085377176e79a4.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 6]: https://s1.51cto.com/images/blog/201912/06/19043a666271463194acb58d197cfbe8.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 7]: https://s1.51cto.com/images/blog/201912/06/84a1f6f2d507bc861d53f906793d5ca5.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 8]: https://s1.51cto.com/images/blog/201912/06/b64fba640f4c04745914d17e0be20895.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 9]: https://s1.51cto.com/images/blog/201912/06/7bc791b735563d09f28e2272bd5f0704.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 10]: https://s1.51cto.com/images/blog/201912/06/fa14f3d3576eb63cdae11ed04c3f7f3f.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 11]: https://s1.51cto.com/images/blog/201912/06/d3742253fa478d5ab5f68ddc9345b5c5.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 12]: https://s1.51cto.com/images/blog/201912/06/1ce9249d85d8b0f0113dbf0aa8da3a17.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_2 13]: https://s1.51cto.com/images/blog/201912/06/07483fbfbfff002b910f1ea6fcf8b70a.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=