登录
登录 注册新账号
注册
已有账号登录
MySQL基本查询示例(2)
Bluetom 阅读 46 次
12月7日发布

注:本博文基于上一篇博文中的表进行查询,上篇博文:MySQL基本查询示例(1)

1、查询fruits表中每个sid对应的所有fname值

<!--以组来进行紧凑-->
mysql> select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1;

返回结果如下:

图片描述

2、统计相同s_id值的行有多少?

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

图片描述

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

查看表中的数据如下:

图片描述

4、查询同一个onum列的quantity(数量)和itemprice(价格)相乘结果大于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;

返回结果如下:

图片描述

限制2:

<!--从第四行开始,显示后面3行-->
mysql> select * from fruits limit 4,3;

返回结果如下:

图片描述

6、查询每个onum对应的fid有几个

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

返回的结果如下:
图片描述

7、查询o_num为30005的quantity(数量)有多少

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

返回的结果如下:

图片描述

8、查询sid为103的fprice的平均数是多少(s_id的平均价格是多少)

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

返回的结果如下:

图片描述

9、查询每个sid对应的平均价格(fprice)是多少?

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

返回的结果如下:

图片描述

10、查询每个sid中fprice值最大的行是哪个?

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

返回的结果如下:

图片描述

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

11、查询每个fprice值最大的值及其所对应的sid、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);

返回的结果如下:

图片描述

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;

返回的结果如下:

图片描述

15、左外联接查询示例

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

返回结果如下:

图片描述

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

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

返回结果如下:
图片描述

------------------------ 本文至此结束,感谢阅读 ------------------------

©著作权归作者所有:来自51CTO博客作者warrent的原创作品,如需转载,请注明出处,否则将追究法律责任