数据库（SQL）学习-基础篇二：基础查询与排序

数据库（SQL）学习——基础篇⼆：基础查询与排序准备⼯作：建表#使⽤数据库shopuseshop;#创建表productCREATETABLEproduct(product_idCHAR(4)NOTNULL,product_nameVARCHAR(100)NOTNULL,product_typeVARCHAR(32)NOTNULL,sale_priceINTEGERDEFAULT0,purchase_priceINTEGER,regist_dateDATE,PRIMARYKEY(product_id));#向表中插⼊数据INSERTINTOproductVALUES('0001','T恤衫','⾐服',1000,500,'2021-12-20'),('0002','打孔器','办公⽤品',500,320,'2021-12-19'),('0003','运动T恤','⾐服',4000,2800,NULL),('0004','菜⼑','厨房⽤具',3000,2800,'2021-12-18'),('0005','⾼压锅','厨房⽤具',6800,5000,'2021-12-17'),('0006','叉⼦','厨房⽤具',500,NULL,'2021-12-16'),('0007','擦菜板','厨房⽤具',880,790,'2021-12-12'),('0008','圆珠笔','办公⽤品',100,NULL,'2021-12-11');⼀.SELECT语句基础通过SELECT语句查询并选取出必要数据的过程称为匹配查询或查询（query）1.基本SELECT语句基本SELECT语句包含了SELECT和FROM两个⼦句，SELECT⼦句中列举了希望从表中查询出的列的名称，FROM⼦句则指定了选取出数据的表的名称SELECT<列名1,列名2,...,列名n>FROM<表名>;例：注意最后⼀个查询项product_name后⾯没有符号SELECTproduct_id,product_nameFROMproduct;2.使⽤WHERE语句从表中选取符合条件的数据WHERE⼦句⽤来指定查询数据的条件，查询出只符合该条件的记录SELECT<列名1,列名2,...,列名n>FROM<表名>WHERE<条件表达式>;例⼦：SELECTproduct_id,product_nameFROMproductWHEREproduct_type='⾐服';3.相关法则（1）星号*代表全部列#查询出全部列SELECT*FROMproduct;（2）可以使⽤AS关键字为列设定别名，设定中⽂别名时需要使⽤双引号""括起来SELECTproduct_idASid,product_nameASname,purchase_priceAS"进货单价"FROMproduct;（3）使⽤DISTINCT可以删除重复⾏。只是查询结果不重复，原表是没有变化的（没有删除）#使⽤DISTINCT删除product_type列中重复的数据SELECTDISTINCTproduct_typeFROMproduct;（4）SELECT⼦句和WHERE⼦句中可以使⽤运算表达式SELECTproduct_id,product_nameASname,#SQL语句使⽤运算表达式sale_price*2AS"sale_price*2"FROMproduct#WHERE⼦句中使⽤运算表达式WHEREsale_price-purchase_price>=500;（5）选取NULL的记录时，WHERE语句中是ISNULL；选取不是NULL的记录时，WHERE语句中是ISNOTNULLSELECTproduct_id,product_nameFROMproduct#选取purchase_price是NULL的记录#WHEREpurchase_priceISNULL;#选取purchase_price不是NULL的记录WHEREpurchase_priceISNOTNULL;（6）表⽰"不是..."的含义，可⽤不等号<>，也可⽤NOT运算符（在查询条件中添加NOT运算符）SELECTproduct_id,product_name,sale_priceFROMproduct#在查询条件中使⽤不等号，并添加NOT运算符WHERENOTsale_price<>500;（7）希望同时使⽤多个查询条件时，可以使⽤AND（且，取交集）、OR（或，取并集）运算符。AND运算符优先于OR运算符，如想优先执⾏OR运算，可以使⽤括号优先处理例：想查询商品种类为“办公⽤品”且登记⽇期是2021-12-11或2021-12-19的商品SELECTproduct_name,product_type,regist_dateFROMproductWHEREproduct_type='办公⽤品'AND(regist_date='2021-12-11'ORregist_date='2021-12-19');（8）含有NULL时的真值SQL之外的语⾔基本上只使⽤真和假这两种真值SQL中还存在NULL，因为不知道这样⼀个值，所以真值的结果既不为真，也不为假，是除真假之外的第三种值——不确定。三值逻辑下的AND真值表：真AND不确定=不确定不确定AND真=不确定假AND不确定=假不确定AND假=假不确定AND不确定=不确定三值逻辑下的OR真值表真OR不确定=真不确定OR真=真假OR不确定=不确定不确定OR假=不确定不确定OR不确定=不确定⼆.聚合查询1.聚合函数COUNT：计算表中的记录数（⾏数）聚合函数会将NULL排除在外。但COUNT(*)例外，它不排除NULL，会得到包含NULL的数据⾏数，⽽COUNT(<列名>)会得到NULL之外的数据⾏数#计算全部数据的⾏数，共8⾏SELECTCOUNT(*)FROMproduct;#计算regist_date不为NULL的数据的⾏数，共7⾏SELECTCOUNT(regist_date)FROMproduct;SUM：计算表中数值列中数据的合计值#计算销售单价和进货单价的合计值SELECTSUM(sale_price),SUM(purchase_price)FROMproduct;AVG：计算表中数值列中数据的平均值#计算销售单价和进货单价的平均值SELECTAVG(sale_price),AVG(purchase_price)FROMproduct;MAX：求出表中任意列中数据的最⼤值MIN：求出表中任意列中数据的最⼩值MAX和MIN也可⽤于⾮数值型数据#进货单价的最⼤值和登记⽇期的最⼩值SELECTMAX(purchase_price),MIN(regist_date)FROMproduct;注：MAX/MIN函数⼏乎适⽤于所有数据类型的列。SUM/AVG函数只适⽤于数值类型的列2.使⽤聚合函数删除重复值（原表不变）：聚合函数的参数中使⽤DISTINCT想要计算值的种类时，可以在COUNT函数的参数中使⽤DISTINCT。#计算去除重复数据后的数据⾏数，结果为3，表⽰表中有3种不同的商品SELECTCOUNT(DISTINCTproduct_type)FROMproduct;在SUM函数中使⽤DISTINCT，遇到相同的数值时不会再加SELECTSUM(sale_price),SUM(DISTINCTsale_price)FROMproduct;三.对表分组1.GROUPBY语句使⽤聚合函数会将整个表的数据进⾏统计处理，当想将数据按照某列来统计处理时，可以使⽤GROUPBY进⾏分组在GROUPBY⼦句中指定的列称为聚合键或分组列SELECT<列名1,列名2,...,列名n>FROM<表名>GROUPBY<列名1,列名2,...,列名n>;例⼦：使⽤GROUPBY语句按照商品种类统计数据⾏数SELECTproduct_type,COUNT(*)FROMproductGROUPBYproduct_type;不使⽤GROUPBY语句，就是统计全表数据SELECTproduct_type,COUNT(*)FROMproduct;2.GROUPBY语句使⽤的⼀些说明（1）当聚合键中包含NULL时，会将NULL作为⼀组特殊数据进⾏处理SELECTpurchase_price,COUNT(*)FROMproductGROUPBYpurchase_price;（2）GROUPBY的书写位置1SELECT→2.FROM→3.WHERE→4.GROUPBY其中前三项⽤于筛选数据，GROUPBY对筛选出的数据进⾏处理SELECTpurchase_price,COUNT(*)FROMproductWHEREproduct_type='⾐服'GROUPBYpurchase_price;四.为聚合结果指定条件在GROUPBY后使⽤HAVING⼦句，对分组结果进⾏过滤。可以使⽤数字、聚合函数和GROUPBY中指定的列名（聚合键）1.使⽤数字找到分组后，数据⾏数为2的分组SELECTproduct_type,COUNT(*)FROMproductGROUPBYproduct_typeHAVINGCOUNT(*)=2;2.使⽤聚合函数找到分组后，销售额总和⼤于5000元的分组SELECTproduct_type,COUNT(*)FROMproductGROUPBYproduct_typeHAVINGSUM(SALE_PRICE)>5000;3.使⽤GROUPBY中指定的列名找到分组后，商品类型为厨房⽤具的分组SELECTproduct_type,COUNT(*)FROMproductGROUPBYproduct_typeHAVINGproduct_type='厨房⽤具';注：因为product_name不包含在GROUPBY聚合键中，所以会报错SELECTproduct_type,COUNT(*)FROMproductGROUPBYproduct_typeHAVINGproduct_name='圆珠笔';五.练习1.编写⼀条SQL语句，从product表中选取出登记⽇期regist_date在2009年4⽉28⽇之后的商品，查询结果要包含product_name和regist_date两列SELECTproduct_name,regist_dateFROMproductWHEREregist_date>'2009-04-28';2.说出对product表执⾏如下3条SELECT语句时的返回结果（1）从product表中返回所有purchase_price为NULL的商品的全部列SELECT*FROMproductWHEREpurchase_price=NULL;（2）从product表中返回所有purchase_price不为NULL的商品的全部列SELECT*FROMproductWHEREpurchase_price<>NULL;（3）从product表中返回所有purchase_name不为NULL的商品的全部列SELECT*FROMproductWHEREproduct_name>NULL;3.写出两条执⾏结果如下的语句语句1SELECTproduct_name,sale_price,purchase_priceFROMproductWHEREproduct_name='T恤衫'ORproduct_name='运动T恤'ORproduct_name='⾼压锅';语句2SELECTproduct_name,sale_price,purchase_priceFROMproductWHEREproduct_type='⾐服'ORsale_price='6800';4.请写出⼀条SELECT语句，从product表中选取出满⾜“销售单价打九折之后利