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;
2、統計相同s_id值的行有多少？ mysql> select s_id,count(*) as total -> from fruits -> group by s_id with rollup; MySQL基本查詢示例（2）
注：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、查詢同一個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： <!--只顯示表中的前四行-->
限制2： <!--從第四行開始，顯示后面3行--> mysql> select * from fruits limit 4,3;
6、查詢每個o_num對應的f_id有幾個 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、查詢s_id為103的f_price的平均數是多少（s_id的平均價格是多少） mysql> select avg(f_price) as avg_price from fruitss where s_id = 103; 返回的結果如下：
9、查詢每個s_id對應的平均價格（f_price）是多少? mysql> select s_id,avg(f_price) as avg_price from fruits group by s_id; 返回的結果如下：
10、查詢每個s_id中f_price值最大的行是哪個？ mysql> select s_id, max(f_price) as max_price from fruits group by s_id; 返回的結果如下：
同理，若要查看最小的行，只需要將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);
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、表聯接類型的概念 在進行接下來的查詢，這里有必要說一下多表查詢的相關概念。
