MySQL单表查询常见操作实例总结
本文实例总结了MySQL单表查询常见操作。分享给大家供大家参考,具体如下:
创建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);
常用查询:
SELECT*FROMfruits; selectf_name,f_pricefromfruits; selectf_name,f_pricefromfruitswheref_price=10.2; selectf_name,f_pricefromfruitswheref_price<10; select*fromfruitswheres_idin(101,102)orderbyf_name; select*fromfruitswheres_idnotin(101,102)orderbyf_name; selectf_name,f_pricefromfruitswheref_pricebetween2.00and10.20; selectf_name,f_pricefromfruitswheref_pricenotbetween2.00and10.20; selectf_name,f_pricefromfruitswheref_namelike"b%"; selectf_name,f_pricefromfruitswheref_namelike"%g%"; selectf_name,f_pricefromfruitswheref_namelike"b%y"; selectf_name,f_pricefromfruitswheref_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','LMing@163.com'), (10002,'Stars','333FromageLane','Dalian','116000','Zhangbo','Jerry@hotmail.com'), (10003,'Netbhood','1SunnyPlace','Qingdao','266000','LuoCong',NULL), (10004,'JOTO','829RiversideDrive','Haikou','570000','YangShan','sam@hotmail.com');
常用查询语句:
selectc_id,c_name,c_emailfromcustomerswherec_emailisnull; selectc_id,c_name,c_emailfromcustomerswherec_emailisnotnull; selectf_name,f_pricefromfruitswheres_id=101andf_price>=5; selectf_name,f_pricefromfruitswheres_idin(101,102)andf_price>=5andf_name="apple"; selectf_name,f_pricefromfruitswheres_id=101ors_id=102;
使用in操作更加简洁明了
selectf_name,f_price fromfruits wheres_idin(101,102);
字段不重复
SELECTDISTINCTs_idFROMfruits; selectf_namefromfruitsORDERBYf_name;
如果第一列数据中没有相同值,将不再对第二列进行排序。
SELECTf_name,f_price FROMfruits ORDERBYf_name,f_price;
按价格降序排列,desc为降序,默认为升序。
SELECTf_name,f_priceFROMfruitsORDERBYf_pricedesc; SELECTf_name,f_priceFROMfruitsORDERBYf_pricedesc,f_name; SELECTs_id,COUNT(*)ASTotalFROMfruitsGROUPBYs_id; SELECTs_id,GROUP_CONCAT(f_name)ASNamesFROMfruitsGROUPBYs_id;
使用having过滤分组
SELECTs_id,GROUP_CONCAT(f_name)ASNames FROMfruits GROUPBYs_idhavingcount(f_name)>1;
在groupby子句中使用withrollup
SELECTs_id,COUNT(*)ASTotalFROMfruitsGROUPBYs_idWITHROLLUP; SELECT*fromfruitsgroupbys_id,f_name;
创建orderitems表:
CREATETABLEorderitems ( o_numintNOTNULL, o_itemintNOTNULL, f_idchar(10)NOTNULL, quantityintNOTNULL, item_pricedecimal(8,2)NOTNULL, PRIMARYKEY(o_num,o_item) ); 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);
常用查询语句:
SELECTo_num,SUM(quantity*item_price)ASorderTotal FROMorderitems GROUPBYo_num HAVINGSUM(quantity*item_price)>=100; SELECTo_num,SUM(quantity*item_price)ASorderTotal FROMorderitems GROUPBYo_num HAVINGSUM(quantity*item_price)>=100 orderbyordertotal; SELECT*FromfruitsLIMIT8;
从第五行开始,读取3行
SELECT*FromfruitsLIMIT4,3;
【例.1】从fruits表中检索所有字段的数据
SELECT*FROMfruits; SELECTf_id,s_id,f_name,f_priceFROMfruits;
【例.2】查询当前表中f_name列所有水果名称,输入如下语句:
SELECTf_nameFROMfruits;
【例.3】例如,从fruits表中获取f_name和f_price两列,输入如下语句:
SELECTf_name,f_priceFROMfruits;
【例.4】查询价格为10.2元的水果的名称,输入如下语句:
SELECTf_name,f_price FROMfruits WHEREf_price=10.2;
【例.5】查找名称为“apple”的水果的价格,输入如下语句:
SELECTf_name,f_price FROMfruits WHEREf_name='apple';
【例.6】查询价格小于10的水果的名称,输入如下语句:
SELECTf_name,f_price FROMfruits WHEREf_price<10;
【例.7】s_id为101和102的记录,输入如下语句:
SELECTs_id,f_name,f_price FROMfruits WHEREs_idIN(101,102) ORDERBYf_name;
【例.8】查询所有s_id不等于101也不等于102的记录,输入如下语句:
SELECTs_id,f_name,f_price FROMfruits WHEREs_idNOTIN(101,102) ORDERBYf_name;
【例.9】查询价格在2.00元到10.5元之间水果名称和价格
SELECTf_name,f_price FROMfruits WHEREf_priceBETWEEN2.00AND10.20;
【例.10】查询价格在2.00元到10.5元之外的水果名称和价格
SELECTf_name,f_price FROMfruits WHEREf_priceNOTBETWEEN2.00AND10.20;
【例.11】查找所有以‘b'字母开头的水果,输入如下语句:
SELECTf_id,f_name FROMfruits WHEREf_nameLIKE'b%';
【例.12】在fruits表中,查询f_name中包含字母‘g'的记录
SELECTf_id,f_name FROMfruits WHEREf_nameLIKE'%g%';
【例.13】查询以‘b'开头,并以‘y'结尾的水果的名称
SELECTf_name FROMfruits WHEREf_nameLIKE'b%y';
【例7.14】在fruits表中,查询以字母‘y'结尾,且‘y'前面只有4个字母的记录
SELECTf_id,f_name FROMfruits WHEREf_nameLIKE'____y';
【例.15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值:
SELECTc_id,c_name,c_email FROMcustomers WHEREc_emailISNULL;
【例.16】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值
SELECTc_id,c_name,c_email FROMcustomers WHEREc_emailISNOTNULL;
【例.17】在fruits表中查询s_id=‘101',并且f_price大于5的记录价格和名称
SELECTf_id,f_price,f_name FROMfruits WHEREs_id='101'ANDf_price>=5;
【例.18】在fruits表中查询s_id=‘101'或者'102',并且f_price大于5,并且f_name='apple'的记录价格和名称
SELECTf_id,f_price,f_name FROMfruits 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_price FROMfruits WHEREs_id=101ORs_id=102;
【例.20】查询s_id=101或者s_id=102的水果供应商的f_price和f_name
SELECTs_id,f_name,f_price FROMfruits WHEREs_idIN(101,102);
【例.21】查询fruits表中s_id字段的值,并返回s_id字段值不得重复
SELECTDISTINCTs_idFROMfruits;
【例.22】查询fruits表的f_name字段值,并对其进行排序
selectf_namefromfruitsORDERBYf_name;
【例.23】查询fruits表中的f_name和f_price字段,先按f_name排序,再按f_price排序
SELECTf_name,f_price FROMfruits ORDERBYf_name,f_price;
【例.24】查询fruits表中的f_name和f_price字段,对结果按f_price降序方式排序
SELECTf_name,f_price FROMfruits ORDERBYf_priceDESC;
【例.25】查询fruits表,先按f_price降序排序,再按f_name字段升序排序,SQL语句如下:
SELECTf_price,f_name FROMfruits ORDERBYf_priceDESC,f_name;
【例.26】根据s_id对fruits表中的数据进行分组
SELECTs_id,COUNT(*)ASTotal FROMfruits GROUPBYs_id;
【例.27】根据s_id对fruits表中的数据进行分组,将每个供应商的水果名称显示出来
SELECTs_id,GROUP_CONCAT(f_name)ASNames FROMfruits GROUPBYs_id;
【例.28】根据s_id对fruits表中的数据进行分组,并显示水果种类大于1的分组信息
SELECTs_id,GROUP_CONCAT(f_name)ASNames FROMfruits GROUPBYs_idHAVINGCOUNT(f_name)>1;
【例.29】根据s_id对fruits表中的数据进行分组,并显示记录数量
SELECTs_id,COUNT(*)ASTotal FROMfruits GROUPBYs_idWITHROLLUP;
【例.30】根据s_id和f_name字段对fruits表中的数据进行分组,SQL语句如下,
SELECT*fromfruitsgroupbys_id,f_name;
【例.31】查询订单价格大于100的订单号和总订单价格
SELECTo_num,SUM(quantity*item_price)ASorderTotal FROMorderitems GROUPBYo_num HAVINGSUM(quantity*item_price)>=100;
【例.32】显示fruits表查询结果的前4行,输入如下语句:
SELECT*FromfruitsLIMIT4;
【例.33】在fruits表中,使用LIMIT子句,返回从第5个记录开始的,行数长度为3的记录
SELECT*FromfruitsLIMIT4,3;
更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。