数据库查询练习152含参考答案.docx
《数据库查询练习152含参考答案.docx》由会员分享，可在线阅读，更多相关《数据库查询练习152含参考答案.docx（13页珍藏版）》请在冰豆网上搜索。
数据库查询练习152含参考答案
查询练习题
第一部分：
#创建fruits表
CREATETABLEfruits（
f_idCHAR（10）NOTNULL,
s_idINTNOTNULL,
f_nameCHAR（255）NOTNULL,
f_priceDECIMAL（8,2）NOTNULL,
PRIMARYKEY（f_id）
）;
#插入数据
INSERTINTOfruits（f_id,s_id,f_name,f_price）
VALUES
（'a1',101,'apple',5.2）,（'b1',101,'blackberry',10.2）,（'bs1',102,'orange',11.2）,
（'bs2',105,'melon',8.2）,（'t1',102,'banana',10.3）,（'t2',102,'grape',5.3）,
（'o2',103,'coconut',9.2）,（'c0',101,'cherry',3.2）,（'a2',103,'apricot',2.2）,
（'l2',104,'lemon',6.4）,（'b2',104,'berry',7.6）,（'m1',106,'mango',15.6）,
（'m2',105,'xbabay',2.6）,（'t4',107,'xbababa',3.6）,（'m3',105,'xxtt',11.6）,
（'b5',107,'xxxx',3.6）;
【1】从fruits表中检索所有字段的数据
Select*fromfruits;
【2】查询fruits表中f_name列所有水果名称
Selectf_namefromfruits;
【3】从fruits表中获取f_name和f_price两列
Selectf_name,f_pricefromfruits;
【4】查询价格为10.2元的水果的名称
Selectf_namefromfruitswheref_price=10.2;
【5】查找名称为“apple”的水果的价格
Selectf_pricefromfruitswheref_name=’apple’;
【6】查询价格小于10的水果的名称
Selectf_namefromfruitswheref_peice<10;
【7】s_id为101和102的记录
Select*fromfruitswheres_id=101ors_id=102;
【8】查询所有s_id不等于101也不等于102的记录
Select*fromfruitswheres_id!
=101ands_id!
=102;
【9】查询价格在2.00元到10.20元之间的水果名称和价格
Selectf_name,f_pricefromfruitswheref_price>2.00andf_price<10.20;
【10】查询价格在2.00元到10.20元之外的水果名称和价格
Selectf_name,f_pricefromfruitswheref_price<2.00orf_price>10.20;
【11】查找所有以’b’字母开头的水果
Selectf_namefromfruitswheref_namelike‘b%’;
【12】在fruits表中，查询f_name中包含字母’g’的记录
Select*fromfruitswheref_namelike“%g%”;
【13】查询以’b’开头，并以’y’结尾的水果的名称
Select*fromfruitswheref_namelike“b%y”;
【14】在fruits表中，查询以字母’y’结尾，且’y’前面只有4个字母的记录
Select*fromfruitswheref_namelike“____y”;
第二部分：
#创建数据表customers
CREATETABLEcustomers（
c_idINTNOTNULLAUTO_INCREMENT,
c_nameCHAR（50）NOTNULL,
c_addressCHAR（50）NULL,
c_cityCHAR（50）NULL,
c_zipCHAR（10）NULL,
c_contactCHAR（50）NULL,
c_emailCHAR（255）NULL,
PRIMARYKEY（c_id）
）;
#插入数据，请执行以下语句。
INSERTINTOcustomers（c_id,c_name,c_address,c_city,c_zip,c_contact,c_email）
VALUES（10001,'RedHook','200Street','Tianjin','300000','LiMing',''）,
（10002,'Stars','333FromageLane','Dalian','116000','Zhangbo',''）,
（10003,'Netbhood','1SunnyPlace','Qingdao','266000','LuoCong',NULL）,
（10004,'JOTO','829RiversideDrive','Haikou','570000','YangShan',''）;
查询表中的记录数目：
SELECTCOUNT（*）AScust_numFROMcustomers;
【15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值
Selectc_id,c_name,c_emailfromcustomerswherec_emailisnull;
【16】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值
Selectc_id,c_name,c_emailfromcustomerswherec_emailisnotnull;
【17】在fruits表中查询s_id=101，并且f_price大于等于5的水果价格和名称
Selectf_name,f_pricefromfruitswheres_id=101andf_price>=5;
【18】在fruits表中查询s_id=101或者102，且f_price大于5，并且f_name=‘apple’的水果价格和名称
Selectf_name,f_pricefromfruitswheres_idin（101,102）andf_price>5andf_name=’apple’;
【19】查询s_id=101或者s_id=102的水果供应商的f_price和f_name
Selectf_price,f_namefromfruitswheres_id=101ors_id=102;
【20】查询s_id=101或者s_id=102的水果供应商的f_price和f_name（用不同的方法）
Selectf_price,f_namefromfruitswheres_idin（101,102）;
【21】查询fruits表中s_id字段的值，返回s_id字段值且不得重复（使用distinct去重复）
SELECTDISTINCTs_idFROMfruits;
【22】查询fruits表的f_name字段值，并对其进行排序
Selectf_namefromfruitsorderbyf_name;
【23】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序
Selectf_name,f_pricefromfruitsorderbyf_name,f_price;
【24】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序
Selectf_name,f_pricefromfruitsorderbyf_pricedesc;
【25】查询fruits表，先按f_price降序排序，再按f_name字段升序排序
Select*fromfruitsorderbyf_pricedesc,f_name;
【26】根据s_id对fruits表中的数据进行分组，并显示每个分组的总记录数。
Selects_id,count（*）fromfruitsgroupbys_id;
【27】根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来（使用GROUP_CONCAT拼接字段内容）
SELECTs_id,GROUP_CONCAT（f_name）ASNAMESFROMfruitsGROUPBYs_id;
【28】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息
Selects_idasnemesfromfruitsgroupbys_idhavingcount（f_name）>1;
【29】根据s_id对fruits表中的数据进行分组，并显示记录数量
SELECTs_id,COUNT（*）ASTotalFROMfruits
GROUPBYs_idWITHROLLUP;
WITHROLLUP对所有记录进行统计
【30】根据s_id和f_name字段对fruits表中的数据进行分组
Select*fromfruitsgroupbys_id,f_name;
第三部分：
创建数据表，SQL语句如下：
CREATETABLEorderitems（
o_numINTNOTNULL,
o_itemINTNOTNULL,
f_idCHAR（10）NOTNULL,
quantityINTNOTNULL,
item_priceDECIMAL（8,2）NOTNULL,
PRIMARYKEY（o_num,o_item）
）;
插入数据，SQL语句如下：
INSERTINTOorderitems（o_num,o_item,f_id,quantity,item_price）
VALUES（30001,1,'a1',10,5.2）,（30001,2,'b2',3,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）;
【31】查询总订单价格大于100的订单号和总订单价格
【32】显示fruits表查询结果的前4行
【33】在fruits表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录
【34】查询customers表中总的行数
【35】查询customers表中有电子邮箱的顾客的总数
【36】在orderitems表中，使用COUNT（）函数统计不同订单号中订购的水果种类
【37】在orderitems表中查询30005号订单一共购买的水果总量
【38】在orderitems表中，使用SUM（）函数统计不同订单号中订购的水果总量
【39】在fruits表中，查询s_id=103的供应商的水果价格的平均值
【40】在fruits表中，查询每一个供应商的水果价格的平均值
【41】在fruits表中查找市场上价格最高的水果
【42】在fruits表中查找不同供应商提供的价格最高的水果
【43】在fruits表中查找f_name的最大值
【44】在fruits表中查找市场上价格最低的水果
【45】在fruits表中查找不同供应商提供的价格最低的水果
第四部分：
#创建数据表suppliers，SQL语句如下：
CREATETABLEsuppliers（
s_idINTNOTNULLAUTO_INCREMENT,
s_nameCHAR（50）NOTNULL,
s_cityCHAR（50）NULL,
s_zipCHAR（10）NULL,
s_callCHAR（50）NOTNULL,
PRIMARYKEY（s_id）
）;
插入数据，SQL语句如下：
INSERTINTOsuppliers（s_id,s_name,s_city,s_zip,s_call）
VALUES（101,'FastFruitInc.','Tianjin','300000','48075'）,
（102,'LTSupplies','Chongqing','400000','44333'）,
（103,'ACME','Shanghai','200000','90046'）,
（104,'FNKInc.','Zhongshan','528437','11111'）,
（105,'GoodSet','Taiyuang','030000','22222'）,
（106,'JustEatOurs','Beijing','010','45678'）,
（107,'DKInc.','Zhengzhou','450000','33332'）;
【46】在fruits表和suppliers表之间使用内连接查询。
查询之前，查看两个表的结构：
DESCfruits;
DESCsuppliers;
由结果可以看到，fruits表和suppliers表中都有相同数据类型的字段s_id，两个表通过s_id字段建立联系。
接下来从fruits表中查询f_name、f_price字段，从suppliers表中查询s_id、s_name
【47】在fruits表和suppliers表之间，使用INNERJOIN语法进行内连接查询
【48】查询供应f_id=‘a1’的水果供应商提供的其他水果种类【较难】
1．LEFTJOIN左连接
左连接的结果包括LEFTOUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。
如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。
创建表orders，SQL语句如下：
CREATETABLEorders（
o_numINTNOTNULLAUTO_INCREMENT,
o_dateDATETIMENOTNULL,
c_idINTNOTNULL,
PRIMARYKEY（o_num）
）;
插入数据，SQL语句如下：
INSERTINTOorders（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）;
【49】在customers表和orders表中，查询所有客户，包括没有订单的客户
2．RIGHTJOIN右连接
右连接是左连接的反向连接，将返回右表的所有行。
如果右表的某行在左表中没有匹配行，左表将返回空值。
【50】在customers表和orders表中，查询所有订单，包括没有客户的订单
【51】在customers表和orders表中，使用INNERJOIN语法查询customers表中ID为10001的客户的订单信息
【52】在fruits表和suppliers表之间，使用INNERJOIN语法进行内连接查询，并对查询结果排序
参考答案
【1】从fruits表中检索所有字段的数据，SQL语句如下：
SELECT*FROMfruits;
【2】查询fruits表中f_name列所有水果名称，SQL语句如下：
SELECTf_nameFROMfruits;
【3】从fruits表中获取f_name和f_price两列，SQL语句如下：
SELECTf_name,f_priceFROMfruits;
【4】查询价格为10.2元的水果的名称，SQL语句如下：
SELECTf_name,f_price
FROMfruits
WHEREf_price=10.2;
【5】查找名称为“apple”的水果的价格，SQL语句如下：
SELECTf_name,f_price
FROMfruits
WHEREf_name='apple';
【6】查询价格小于10的水果的名称，SQL语句如下：
SELECTf_name,f_price
FROMfruits
WHEREf_price<10;
【7】s_id为101和102的记录，SQL语句如下：
SELECTs_id,f_name,f_price
FROMfruits
WHEREs_idIN（101,102）
ORDERBYf_name;
【8】查询所有s_id不等于101也不等于102的记录，SQL语句如下：
SELECTs_id,f_name,f_price
FROMfruits
WHEREs_idNOTIN（101,102）
ORDERBYf_name;
【9】查询价格在2.00元到10.20元之间的水果名称和价格，SQL语句如下：
SELECTf_name,f_priceFROMfruitsWHEREf_priceBETWEEN2.00AND10.20;
【10】查询价格在2.00元到10.20元之外的水果名称和价格，SQL语句如下：
SELECTf_name,f_price
FROMfruits
WHEREf_priceNOTBETWEEN2.00AND10.20;
【11】查找所有以’b’字母开头的水果，SQL语句如下：
SELECTf_id,f_name
FROMfruits
WHEREf_nameLIKE'b%';
【12】在fruits表中，查询f_name中包含字母’g’的记录，SQL语句如下：
SELECTf_id,f_name
FROMfruits
WHEREf_nameLIKE'%g%';
【13】查询以’b’开头，并以’y’结尾的水果的名称，SQL语句如下：
SELECTf_name
FROMfruits
WHEREf_nameLIKE'b%y';
【14】在fruits表中，查询以字母’y’结尾，且’y’前面只有4个字母的记录，SQL语句如下：
SELECTf_id,f_nameFROMfruitsWHEREf_nameLIKE'----y';
查询表中的记录数目：
SELECTCOUNT（*）AScust_numFROMcustomers;
【15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值，SQL语句如下：
SELECTc_id,c_name,c_emailFROMcustomersWHEREc_emailISNULL;
【16】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值，SQL语句如下：
SELECTc_id,c_name,c_emailFROMcustomersWHEREc_emailISNOTNULL;
【17】在fruits表中查询s_id=101，并且f_price大于等于5的水果价格和名称，SQL语句如下：
SELECTf_id,f_price,f_nameFROMfruitsWHEREs_id='101'ANDf_price>=5;
【18】在fruits表中查询s_id=101或者102，且f_price大于5，并且f_name=‘apple’的水果价格和名称，SQL语句如下：
SELECTf_id,f_price,f_nameFROMfruits
WHEREs_idIN（'101','102'）ANDf_price>=5ANDf_name='apple';
【19】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：
SELECTs_id,f_name,f_priceFROMfruitsWHEREs_id=101ORs_id=102;
【20】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：
SELECTs_id,f_name,f_priceFROMfruitsWHEREs_idIN（101,102）;
【21】查询fruits表中s_id字段的值，返回s_id字段值且不得重复，SQL语句如下：
SELECTDISTINCTs_idFROMfruits;
【22】查询fruits表的f_name字段值，并对其进行排序，SQL语句如下：
SELECTf_nameFROMfruitsORDERBYf_name;
【23】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下：
SELECTf_name,f_priceFROMfruitsORDERBYf_name,f_price;
【24】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序，SQL语句如下：
SELECTf_name,f_priceFROMfruitsORDERBYf_priceDESC;
【25】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：
SELECTf_price,f_nameFROMfruitsORDERBYf_priceDESC,f_name;
【26】根据s_id对fruits表中的数据进行分组，并显示每个分组的总记录数，SQL语句如下：
SELECTs_id,COUNT（*）ASTotalFROMfruitsGROUPBYs_id;
【27】根据s_id对f
升级会员 