1、SLQ实战语句总结二=Oracle的分页查询: 没有limit,只有改用类似子查询; =select语句的逻辑 和 数学处理能力: : select 12; /-计算表达式结果,1-true; 1=2; / 0-false; select birth FROM pets ;/业务要求:请列出所有宠物的出生日期 age的SQL表达式就是:(当前年-生日年) - (当前月日 生日月日); SQL实现:当前日期-curdate(); 当前日期年份-YEAR(curdate(); 月日-RIGHT(日期值,5);/从日期值参数的右边向左,取5位,即是月日值; /业务要求:请列出所有宠物(如果存活至今)
2、的 年龄;业务要求:请列出所有宠物(如果存活至今)的 年龄; select name ,birth , CURDATE() ,(YEAR(CURDATE()-YEAR(birth)-(right(curdate(),5)right(birth,5) from pets where death is null; /业务要求:请列出所有宠物(如果存活至今)的 年龄;业务要求:请列出所有宠物(如果存活至今)的 年龄; select name ,birth , CURDATE() as 当前日期 ,(YEAR(CURDATE()-YEAR(birth)-(right(curdate(),5)right
3、(birth,5) as age from pets where death is null; /业务要求:请列出所有宠物的 年龄(存活至今的 + 已经死亡的); select name ,birth ,death, CURDATE() as 当前日期 ,(YEAR(CURDATE()-YEAR(birth)-(right(curdate(),5)right(birth,5) as age from pets where death is null UNION all select name ,birth , death,CURDATE() as 当前日期 ,(YEAR(death)-YEAR
4、(birth)-(right(death ,5)right(birth,5) as age from pets where death is not null; 答案II)将上两句结果表合并为一个结果表,输出;/记录合并 Union:可以简单地组合两个查询结果集;/要求;字段数必需一致,字段名必需一致; Union all: 两表重复部分,不抵消,都列出; Union distinct: 重复部分,仅列出一次; Union:默认为 Union distinct; /请列出所有宠物的年龄(存活至今的),并按年龄排序; select name ,birth ,death, CURDATE() as
5、 当前日期 ,(YEAR(CURDATE()-YEAR(birth)-(right(curdate(),5)right(birth,5) as age from pets where death is null UNION all select name ,birth , death,CURDATE() as 当前日期 ,(YEAR(death)-YEAR(birth)-(right(death ,5)right(birth,5) as age from pets where death is not null order by age desc; /挑出活着的宠物中的老寿星; select
6、name ,birth ,death, CURDATE() as 当前日期 ,(YEAR(CURDATE()-YEAR(birth)-(right(curdate(),5)right(birth,5) as age from pets where death is null UNION all select name ,birth , death,CURDATE() as 当前日期 ,(YEAR(death)-YEAR(birth)-(right(death ,5)=0; _-任意单个字符; =1;=区间查找: 类型I):范围查找 select * from student where id
7、= and id =3; . | | | | 字段名 的 别名 表名 的 别名 /显示时,以别名为临时表字段名。 /查询ID号=3的宠物; 要求:英文字段名-中文字段名; select p.id AS 宠物ID号, p.name AS 宠物名 from pet1 AS p where p.id=3; - 如此别名易于读懂业务意义; -如此别名简化表名书写; =计算记录行数;count(); 业务意义:BI(Business Intelligence)、OA等大量商务软件,都需要对DB中大量业务数据进行统计、分析; -商业软件的意义所在。 函数COUNT(某个字段名)-统计临时结果表中,在该名字
8、段登记有值的记录个数; *-任意字段;自动挑一个字段值最多的;/比如,主键字段,肯定有值; /COUNT(*)-近似为(记录)总数; /注意:null字段值不被count()统计。 select count(*) as 宠物总数 from pets;/统计宠物总数; select * from pets; select count(name) from pets;/统计name字段/业务意义:有名字的宠物的数量; select count(sex) from pets;/统计sex字段 select count(*) from pets where species=dog; /-业务意义:统计
9、狗的数量; select count(*) from pets where species =dogor species =snakeor species =cat; select count(owner) owner from pets ; /宠物主人字段有几条记录? select count(death) from pets ;/宠物death字段有几条记录? /业务意义:统计宠物主人有几位?/重复的主人登记,会重复计算;还需要分组合并重复; =记录分组(归类): I)给某个字段分组: select 字段名 from pet group by 字段名 ; select species fr
10、om pets group by species ;/技术意义:对species字段进行分组查询/业务意义;列出宠物的种类 II)统计每组内的数量: COUNT(字段名)+ Group By select species,count(species) from pets group by species ;/技术意义:对species字段进行分组数量统计;/业务意义:每种宠物的数量; /?如果想列出每种宠物的数量,同时想列出宠物的种类名称; select species,count(species) from pets where species IN(cat,dog) group by sp
11、ecies ;/业务意义:统计出猫和狗的各自数量; select species,COUNT(*) from orders GROUP BY species;/每种家电产品卖了多少件; select species,COUNT(*) from sales GROUP BY species; select species,COUNT(*) from orders GROUP BY species;/每种家电产品卖了多少件; select species,COUNT(*) from sales GROUP BY species; select sex ,COUNT(*) from pets gro
12、up by sex;/-业务意义:宠物的性别统计情况; SELECT owner, COUNT(*) FROM pets GROUP BY owner; /业务意义:每个主人各自拥有的宠物数量 select owner,count(*) as 宠物个数 from pets group by owner;/同上; SELECT DISTINCT owner FROM pets;/业务意义:列出所有养宠物的人; select COUNT( distinct owner) from pets ;/列出养宠物的人的个数 其余业务意义: III)统计组数量: COUNT()+Distinct实现 /se
13、lect COUNT( distinct 字段名) from pet ; =Distinct /纯字段值种类-用Distinct。 SELECT DISTINCT owner FROM pet; /Distinct 与 Group By 的相同: 针对同一个字段归组时,二者相同; /Distinct 与 Group By 的不同: 组合使用count()、min()、max()等时,distinct的使用 与 Group by的使用方式不同; 具体:组间统计-COUNT(distinct 字段名); 组内统计-Count(*) from . group by 字段名; select COUNT
14、(distinct species) from pets;/几种宠物 select species ,count(species) as 宠物数量 from pets group by species;/-宠物共有几种; =最值问题。 Max()、Min() order by + limit 1 =求某个字段中的最值: select max(birth) as 最晚的生日 from pets ;/最晚的生日 select max(month(birth) as 最晚的生日 from pets ;/生日中月份最晚的 =Count()、Max()、Min()等,与其它字段组合使用时,两个字段以上必
15、须和Group by合用。 select NAME, max(birth) as 最晚的生日 from pets;-会报错; /解决方案1:-ORDER BY + LIMIT 技术 select NAME, birth as 最晚的生日 from pets order by birth desc limit 1;/-limit n; 从结果集中,取出最顶部的n个记录; /解决方案2:-子查询技术。 select name,birth from pet where birth=(select Max(birth) as 最晚生日 from pet); select NAME, max(birth
16、) as 最晚的生日 from pets group by birth;/列出 生日最晚的宠物,及其名字; select max(b.petcount) AS 单人拥有最多宠物数量 from (select owner,count(*) as petcount from pets group by owner) as b;/单人拥有的最多的宠物数量: select owner ,count(*) as petcount from pet group by owner order by petcount desc limit 1;/哪个主人拥有的宠物最多? -order by可以对表or临时表的
17、字段进行排序;=联合字段分组 + 统计。 特点:分级别的分组依次统计; select species ,sex, COUNT(*) as 数量 from pets group by species,sex;/ -业务意义:每个种类的每种性别的宠物 数量; select species ,sex, COUNT(*) as 数量 from pets where sex is not null group by species,sex;/每个种类的每种性别的宠物数量;(所有知道性别的) select species ,sex, COUNT(*) as 数量from pets where specie
18、s IN(cat,dog) group by species,sex;/猫或狗的每种性别的数量 技术:1)多字段GROUP BY-实现联合分组 2)再统计最小组内的数量; COUNT(*) + GROUP BY 字段1, 字段2,.; 注意:大小组的级别顺序; GROUP BY species, sex; /先按species,再按sex,联合分组; GROUP BY sex,species; /先按sex,再按species,联合分组; 练习: /网上书店项目中的count() 分析:哪张表-哪些过滤条件-是否分组-是否排序-哪些字段-是否统计。 -某个用户下的订单数 count(*) ,O
19、rder表,where 用户号条件; -某个用户购买的每种商品的订单数 count(*),Order表,where 用户号条件,group by 商品名; -某个用户购买的每种商品的全年每个月的订单数 。 -某种货物卖出的件数; count(*) ,Order表,where 货号条件 + 已交费条件; -本月下订单的总用户数; count(用户字段) ,Order表,where 时间条件; /where month(订单日期字段)=month(curdate() -如果一个用户多次只算作一个用户,则需 group by 用户名字段; -本月每种被订购商品的总用户数; 按商品种类字段分组统计;
20、count(用户字段) ,Order表,where 时间字段月份值; -每个月每种被订购商品的用户数; -每个用户购买的每种商品的全年每个月的订单数;/用户名、商品名、订单日期月份 三字段的联合分组; -本月销售的总商品数;/sum(货物数量) ,Order表,where 时间条件; -本月销售的每种商品的数量; -全年销售的每种商品的数量; 当年-where Year(订单日期)=Year(curdate() 每种-group by 商品名字段 数量-sum(货物数量)/加法和; -另一张表:accounts select accountName ,accountCreatedAt, acc
21、ountBalance from accounts ; select accountCreatedAt from accounts group by accountCreatedAt ;/列出好、各个账户的地点名称; select accountCreatedAt,count(*) from accounts group by accountCreatedAt ;/每个地点的账户总数;=平均值问题 =“平均收入”、“平均分数” select AVG(accountBalance) as 平均余额 from accounts ;/所有(记录的)帐户余额平均值 =分组 平均值:技术:GROUP B
22、Y + AVG(); select accountCreatedAt, AVG(accountBalance) as 平均余额 from accounts group by accountCreatedAt ; /“每个地点(地域组)的帐户的平均余额” =分组最大值:技术:GROUP BY + max(); select accountCreatedAt,max(accountBalance) from accounts group by accountCreatedAt ;/每个地点的,账户余额最大值; =分组总和:技术:GROUP BY + SUM(); select accountCre
23、atedAt,sum(accountBalance) as 总和 from accounts group by accountCreatedAt ;/每个地点的,账户总和 【总结】一组一统计 分组+函数 =having 子句常和GROUP BY子句,联合; 对select操作之后的结果集临时表中记录进行条件筛选。 /where是对原表记录,作过滤; /“ny这个地方的帐户的平均余额” 算法I):先按地点分组,统计出每组的余额,得临时表; 再用Having对临时结果表过滤,过滤出CA这一条临时结果记录; select accountCreatedAt,AVG(accountBalance) as 平均余额 from accounts group by accountCreatedAt HAVING accountCreatedAt=ny; 算法II):where 原表过滤条件 +avg()
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1