SLQ实战语句总结二.docx

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()