MySQL数据查询.docx
《MySQL数据查询.docx》由会员分享，可在线阅读，更多相关《MySQL数据查询.docx（13页珍藏版）》请在冰豆网上搜索。
MySQL数据查询
.0.Mysql查询数据
本章所用到的数据表
/*顾客表*/
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）
）;
/*水果表*/
/*供货方表*/CREATETABLEfruits
（
f_idchar（10）NOTNULL,
s_idINTNOTNULL,
f_namechar（255）NOTNULL,
f_pricedecimal（8,2）NOTNULL,
PRIMARYKEY（f_id）
）;
CREATETABLEsuppliers
（
s_idintNOTNULLAUTO_INCREMENT,
s_namechar（50）NOTNULL,
s_citychar（50）NULL,
s_zipchar（10）NULL,
s_callCHAR（50）NOTNULL,
PRIMARYKEY（s_id）
）;
/*订单明细表*/
CREATETABLEorderitems
（
o_numintNOTNULL,
o_itemintNOTNULL,
f_idchar（10）NOTNULL,
quantityintNOTNULL,
item_pricedecimal（8,2）NOTNULL,
PRIMARYKEY（o_num,o_item）
）;
/*订单表*/
CREATETABLEorders
（
o_numintNOTNULLAUTO_INCREMENT,
o_datedatetimeNOTNULL,
c_idintNOTNULL,
PRIMARYKEY（o_num）
）;
/*--------------------------插入数据--------------------------*/
INSERTINTOcustomers（c_id,c_name,c_address,c_city,c_zip,c_contact,c_email）
VALUES（10001,'RedHook','200Street','Tianjin','300000','LiMing','LMing@'）,
（10002,'Stars','333FromageLane','Dalian','116000','Zhangbo','Jerry@'）,
（10003,'Netbhood','1SunnyPlace','Qingdao','266000','LuoCong',NULL）,
（10004,'JOTO','829RiversideDrive','Haikou','570000','YangShan','sam@'）;
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）;
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'）;
INSERTINTOorderitems（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）;
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）;
单表查询
查询所有字段查询指定字段
【例7.1】从fruits表中检索所有字段的数据
SELECT*FROMfruits;
SELECTf_id,s_id,f_name,f_priceFROMfruits;
查询单个字段
【例7.2】查询当前表中f_name列所有水果名称，输入如下语句：
SELECTf_nameFROMfruits;
查询多个字段
【例7.3】例如，从fruits表中获取f_name和f_price两列，输入如下语句：
SELECTf_name,f_priceFROMfruits;
查询指定记录
【例7.4】查询价格为10.2元的水果的名称，输入如下语句：
SELECTf_name,f_priceFROMfruitsWHEREf_price=10.2;
1
【例7.5】查找名称为“apple”的水果的价格,输入如下语句：
SELECTf_name,f_priceFROMfruitsWHEREf_name='apple';
【例7.6】查询价格小于10的水果的名称，输入如下语句：
SELECTf_name,f_priceFROMfruitsWHEREf_price<10;
带in关键字的查询in（m,n）等于m和等于nnotin（m,n）
【例7.7】s_id为101和102的记录，输入如下语句：
SELECTs_id,f_name,f_priceFROMfruitsWHEREs_idIN（101,102）ORDERBYf_name;
【例7.8】查询所有s_id不等于101也不等于102的记录，输入如下语句：
SELECTs_id,f_name,f_price
FROMfruits
WHEREs_idNOTIN（101,102）
ORDERBYf_name;
带betweenand的范围查询betweenmandn在M和N之间
【例7.9】查询价格在2.00元到10.5元之间水果名称和价格
SELECTf_name,f_priceFROMfruitsWHEREf_priceBETWEEN2.00AND10.20;
【例7.10】查询价格在2.00元到10.5元之外的水果名称和价格
SELECTf_name,f_priceFROMfruitsWHEREf_priceNOTBETWEEN2.00AND10.20;
带like的字符匹配查询，通配符---百分号'%',匹配任意长度的字符
M%N以M开头且以N结尾
M%以M开头
%N以N结尾
%M%含有M
【例7.11】查找所有以‘b’字母开头的水果，输入如下语句：
SELECTf_id,f_nameFROMfruitsWHEREf_nameLIKE'b%';
【例7.12】在fruits表中，查询f_name中包含字母‘g’的记录
SELECTf_id,f_nameFROMfruitsWHEREf_nameLIKE'%g%';
【例7.13】查询以‘b’开头，并以‘y’结尾的水果的名称
SELECTf_nameFROMfruitsWHEREf_nameLIKE'b%y';
下划线通配符'_'一次只能匹配任意一个字符
【例7.14】在fruits表中，查询以字母‘y’结尾，且‘y’前面只有4个字母的记录
SELECTf_id,f_nameFROMfruitsWHEREf_nameLIKE'____y';
查询空值在子句中不能使用’=‘使用isnull表示空值
【例7.15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值：
SELECTc_id,c_name,c_emailFROMcustomersWHEREc_emailISNULL;
【例7.16】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值
SELECTc_id,c_name,c_emailFROMcustomersWHEREc_emailISNOTNULL;
带and/&&的多条件查询
【例7.17】在fruits表中查询s_id=‘101’，并且f_price大于5价格和名称的记录
SELECTf_id,f_price,f_nameFROMfruitsWHEREs_id='101'ANDf_price>=5;
【例7.18】在fruits表中查询s_id=‘101’或者’102’，并且f_price大于5，并且f_name=’apple’的记录价格和名称
SELECTf_id,f_price,f_nameFROMfruits
WHEREs_idIN（'101','102'）ANDf_price>=5ANDf_name='apple';
带OR的多条件查询
In（M,N）M和N
And和，且
【例7.19】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：
SELECTs_id,f_name,f_priceFROMfruitsWHEREs_id=101ORs_id=102;（不能写为=101,102或者=101or102（只求了101））
【例7.20】查询s_id=101或者s_id=102的水果供应商的f_price和f_name
SELECTs_id,f_name,f_priceFROMfruitsWHEREs_idIN（101,102）;
查询结果不重复使用distinct语法：
selectdistinct字段名from表名
【例7.21】查询fruits表中s_id字段的值，并返回s_id字段值不得重复
SELECTDISTINCTs_idFROMfruits;
对查询结果进行排序orderby默认按升序来排序即ASC
Orderby子节段（ASC）/DESC
Orderby子节段1，字节段2.。
。
。
。
。
单列排序
【例7.22】查询fruits表的f_name字段值，并对其进行排序
selectf_namefromfruitsORDERBYf_name;
多列排序
【例7.23】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序
SELECTf_name,f_priceFROMfruitsORDERBYf_name,f_price;
备注：
先按照字段1进行排序,如果字段1中的值有相等,那么相等的这些数据将按照字段2,在进行排序.
如果第一列数据中所有的值都是唯一的（值均不相等），将不再对第二列进行排序.
【例7.24】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序
SELECTf_name,f_priceFROMfruitsORDERBYf_priceDESC;
指定排序的方向ASC升序，DESC降序
【例7.25】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：
SELECTf_price,f_nameFROMfruitsORDERBYf_priceDESC,f_name;
分组查询：
groupby关键字通常和集合函数一起使用例如：
max（），min（）,count（）,sun（）,avg（）
每一个水果供应商提供多种水果，根据水果供应商分组使用count可以查看水果供应商供应的水果种类数
【例7.26】根据s_id对fruits表中的数据进行分组
SELECTs_id,COUNT（*）ASTotalFROMfruitsGROUPBYs_id;
group_concat（）可以将分组的子元素查看出来
【例7.27】根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来
SELECTs_id,GROUP_CONCAT（f_name）ASNamesFROMfruitsGROUPBYs_id;
having条件过滤
【例7.28】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息
SELECTs_id,GROUP_CONCAT（f_name）ASNames
FROMfruits
GROUPBYs_idHAVINGCOUNT（f_name）>1;
多字段分组,与多字段排序相同,先按照字段1分组,组中再按照字段2分组.
【例7.30】根据s_id和f_name字段对fruits表中的数据进行分组，SQL语句如下，
SELECT*fromfruitsgroupbys_id,f_name;
groupby和orderby一起使用o_num订单号，订单价格
【例7.31】查询总订单价格大于100的订单号和总订单价格
SELECTo_num,SUM（quantity*item_price）ASorderTotal
FROMorderitems
GROUPBYo_num
HAVINGSUM（quantity*item_price）>=100;
使用limit限制查询结果的数量
limit[位置偏移量]，行数位置偏移量即从哪行开始显示
【例7.32】显示fruits表查询结果的前4行，输入如下语句：
SELECT*FromfruitsLIMIT4;
记录从0开始
【例7.33】在fruits表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录
SELECT*FromfruitsLIMIT4,3;
使用集合函数查询
Select集合函数（目的）（as新名称）from表明
Count（）求某列的总行数
Sum（）求和
Avg（）求平均数
Max（）求最大值
Min（）求最小值
count（）函数返回某一列的行总数
【例7.34】查询customers表中总的行数
SELECTCOUNT（*）AScust_numfromcustomers;
【例7.35】查询customers表中有电子邮箱的顾客的总数，输入如下语句：
SELECTCOUNT（c_email）ASemail_num
FROMcustomers;
【例7.36】在orderitems表中，使用COUNT（）函数统计不同订单号中订购的水果种类
SELECTo_num,COUNT（f_id）FROMorderitemsGROUPBYo_num;
sum（）求和函数
【例7.37】在orderitems表中查询30005号订单一共购买的水果总量，输入如下语句：
SELECTSUM（quantity）ASitems_total
FROMorderitems
WHEREo_num=30005;
【例7.38】在orderitems表中，使用SUM（）函数统计不同订单号中订购的水果总量
SELECTo_num,SUM（quantity）ASitems_total
FROMorderitems
GROUPBYo_num;
avg（）平均值函数
【例7.39】在fruits表中，查询s_id=103的供应商的水果价格的平均值，SQL语句如下：
SELECTAVG（f_price）ASavg_price
FROMfruits
WHEREs_id=103;
【例7.40】在fruits表中，查询每一个供应商的水果价格的平均值，SQL语句如下：
SELECTs_id,AVG（f_price）ASavg_price
FROMfruits
GROUPBYs_id;
max（）最大值
【例7.41】在fruits表中查找市场上价格最高的水果，SQL语句如下：
mysql>SELECTMAX（f_price）ASmax_priceFROMfruits;
【例7.42】在fruits表中查找不同供应商提供的价格最高的水果
SELECTs_id,MAX（f_price）ASmax_price
FROMfruits
GROUPBYs_id;
【例7.43】在fruits表中查找f_name的最大值，SQL语句如下
SELECTMAX（f_name）fromfruits;
min（）最小值
【例7.44】在fruits表中查找市场上价格最低的水果，SQL语句如下：
mysql>SELECTMIN（f_price）ASmin_priceFROMfruits;
【例7.45】在fruits表中查找不同供应商提供的价格最低的水果
SELECTs_id,MIN（f_price）ASmin_price
FROMfruits
GROUPBYs_id;
子查询
定义两个表tb11和tb12
CREATEtabletb11（num1INTNOTNULL）;
CREATEtabletb12（num2INTNOTNULL）;
向两个表中插入数据，
INSERTINTOtb11values
（1）,（5）,（13）,（27）;
INSERTINTOtb12values（6）,（14）,（11）,（20）;
使用any，some关键字的子查询同义词
Any（只要有，就可以）all（满足所有要求）
【例7.53】返回tb12表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何值为符合查询条件的结果。
SELECTnum1FROMtb11WHEREnum1>ANY（SELECTnum2FROMtb12）;
带All关键字的子查询
【例7.54】返回tbl1表的中比tbl2表num2列所有值都大的值
SELECTnum1FROMtb11WHEREnum1>ALL（SELECTnum2FROMtb12）;
带exists关键字的子查询:
exists后的sql语句的结果不为空时,执行exists前的查询
【例7.55】查询表suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的记录
SELECT*fromfruits
WHEREEXISTS
（SELECTs_nameFROMsuppliersWHEREs_id=107）;
【例7.56】查询表suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的f_price大于10.20的记录
SELECT*fromfruits
WHEREf_price>10.20ANDEXISTS
（SELECTs_nameFROMsuppliersWHEREs_id=107）;
【例7.57】查询表suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录
SELECT*fromfruits
WHERENOTEXISTS
（SELECTs_nameFROMsuppliersWHEREs_id=107）;
带in关键字的子查询
【例7.58】在orderitems表中查询订购f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id
SELECTc_idFROMordersWHEREo_numIN
（SELECTo_numFROMorderitemsWHEREf_id='c0'）;
【例7.59】与前一个例子语句类似，但是在SELECT语句中使用NOTIN操作符
SELECTc_idFROMordersWHEREo_n
升级会员 