MySQL基本查詢示例（2）

注：本博文基于上一篇博文中的表進(jìn)行查詢，上篇博文：MySQL基本查詢示例（1）。

1、查詢fruits表中每個s_id對應(yīng)的所有f_name值

<!--以組來進(jìn)行緊湊-->
mysql> select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1;

返回結(jié)果如下：

2、統(tǒng)計(jì)相同s_id值的行有多少？

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

注：with rollup的作用是將s_id分組后的和再進(jìn)行相加，統(tǒng)計(jì)出來的總數(shù)，也就是16。

3、創(chuàng)建一個新表并插入數(shù)據(jù)

<!--創(chuàng)建新表-->
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)
    -> );
<!--插入數(shù)據(jù)-->
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');

查看表中的數(shù)據(jù)如下：

4、查詢同一個o_num列的quantity（數(shù)量）和item_price（價(jià)格）相乘結(jié)果大于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——限制返回的行數(shù)

限制1：

<!--只顯示表中的前四行-->
mysql> select * from fruits limit 4;

返回結(jié)果如下：

限制2：

<!--從第四行開始，顯示后面3行-->
mysql> select * from fruits limit 4,3;

返回結(jié)果如下：

6、查詢每個o_num對應(yīng)的f_id有幾個

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

返回的結(jié)果如下：

7、查詢o_num為30005的quantity（數(shù)量）有多少

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

返回的結(jié)果如下：

8、查詢s_id為103的f_price的平均數(shù)是多少（s_id的平均價(jià)格是多少）

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

返回的結(jié)果如下：

9、查詢每個s_id對應(yīng)的平均價(jià)格（f_price）是多少?

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

返回的結(jié)果如下：

10、查詢每個s_id中f_price值最大的行是哪個？

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

返回的結(jié)果如下：

同理，若要查看最小的行，只需要將max換為min即可。

11、查詢每個f_price值最大的值及其所對應(yīng)的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);

返回的結(jié)果如下：

12、再次創(chuàng)建所需表并插入數(shù)據(jù)

<!--創(chuàng)建表-->
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)
    -> );
<!--插入數(shù)據(jù)-->
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、表聯(lián)接類型的概念

在進(jìn)行接下來的查詢，這里有必要說一下多表查詢的相關(guān)概念。

1）內(nèi)聯(lián)接

內(nèi)聯(lián)接（inner join）是最常見的一種聯(lián)接方式，只返回兩個數(shù)據(jù)集合之間匹配關(guān)系的行，將位于兩個互相交叉的數(shù)據(jù)集合中重疊部分以內(nèi)的數(shù)據(jù)行聯(lián)接起來。

內(nèi)聯(lián)接使用比較運(yùn)算符進(jìn)行表間某些列數(shù)據(jù)的比較操作，并列出這些表中與聯(lián)接相匹配的數(shù)據(jù)行。

2）外聯(lián)接

外聯(lián)接（outer join）是對內(nèi)聯(lián)接的擴(kuò)充，除了將兩個數(shù)據(jù)集合中重復(fù)部分以內(nèi)的數(shù)據(jù)行聯(lián)接起來之外，還可以根據(jù)要求返回左側(cè)或右側(cè)表中非匹配的數(shù)據(jù)或全部的數(shù)據(jù)。

外聯(lián)接還可以分為以下幾種：

左外聯(lián)接（left join或left outer join）的結(jié)果包括左表的所有行，如果左表的某一行在右表中沒有匹配行，則右表返回空值，否則返回相應(yīng)值。

右外聯(lián)接（right join或right outer join）是左外聯(lián)接的反向聯(lián)接，將返回右表的所有行，如果右表的某一行在左表中沒有匹配行，則左表返回空值，否則返回相應(yīng)值。

全聯(lián)接（full join 或full outer join）將返回左表和右表中的所有行，當(dāng)某一行在另一個表中沒有匹配行時，另一個表返回空值，否則返回相應(yīng)值。

14、內(nèi)聯(lián)接查詢，將兩個表的指定列生成一個新表

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

返回的結(jié)果如下：

15、左外聯(lián)接查詢示例

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

返回結(jié)果如下：

16、內(nèi)聯(lián)接查詢時指定其他條件

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

返回結(jié)果如下：

———————— 本文至此結(jié)束，感謝閱讀 ————————