[大数据]SQL问题复盘解析-2：

10-29?每日测试复盘：

1、检索所有比“王华”年龄大的学生姓名、年龄和性别。正确的SELECT语句是()

解析：SELECT sn,age,sex,FROM s where age>(selece age from s where sn="王华")

子查询必须是完整语句

2、开发人员决定需永久删除数据表waterinfo001表。选出符合要求的语句。

解析：DROP TABLE waterinfo001

drop是完全删除表，包括表结构；delete是删除表数据，保留表的结构，而且可以加where,只删除一行或者多行；truncate 只能删除表数据，会保留表结构，而且不能加where

1、处理效率：drop>trustcate>delete，2、drop删除整个表；trustcate删除全部记录，但不删除表；delete删除部分记录，3、delete不影响所用extent，高水线保持原位置不动；trustcate会将高水线复位。

3、运动会比赛信息的数据库，有如下三个表：运动员ATHLETE（运动员编号 Ano，姓名Aname，性别Asex，所属系名 Adep）， 项目 ITEM （项目编号Ino，名称Iname，比赛地点Ilocation）， 成绩SCORE （运动员编号Ano，项目编号Ino，积分Score）。写出目前总积分最高的系名及其积分，SQL语句实现正确的是：（? ? ? ）

解析：SELECT Adep,SUM(Score)FROM ATHLETE,SCORE? WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep? HAVING SUM(Score)>=ALL (SELECT SUM(Score) FROM ATHLETE,SCORE? WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep)

All,any都是用于子查询的，All：对所有数据都满足条件，整个条件才成立；Any：只要有一条数据满足条件，整个条件成立；Some的作用和Any一样 .

4、有订单表orders，包含字段用户信息userid，字段产品信息productid，以下语句能够返回至少被订购过两次的productid？（）

解析：SELECT productid FROM orders GROUP BY productid HAVING COUNT(productid)>1

5、在SQL中语法规范中，having子句的使用下面描述正确的是：（????）

解析：having子句即可包含聚合函数作用的字段也可包括普通的标量字段

having子句必须于group by 子句同时使用，不能单独使用

where在分组前过滤，having在分组后过滤，两者之间不冲突

having的作用是对分组查询的结果进行过滤，没有聚合函数的使用也可以用having过滤。

6、用一条SQL 语句查询出每门课都大于80 分的学生姓名，SQL语句实现正确的是：（? ? ? ）

解析：Select distinct name from score where name not in(Select name from score where grade <= 80);

?7、 有一张学生成绩表sc（sno 学号，class 课程，score 成绩）请问哪个语句可以查询出每个学生的英语、数学的成绩（行转列，一个学生输出一行记录，比如输出[1, 89, 90]）？

解析：select sno,
sum(if(class='english',score,0)) as english,
sum( if(class='math',score,0) ) as math
from sc
where class in('english','math')
group by sno

8、有两张表，问：用SQL查询 购买过goods_id 为1001的用户user_id（）
解析：select user_id from A where order_id in (select order_id from B where goods_id = '1001')
在子表元素大于1时， 不能用= 要用in in（子表）

9、表结构如下：

CREATE TABLE `score` (

???`id`?int(11) NOT NULL AUTO_INCREMENT,

???`sno`?int(11) NOT NULL,

???`cno` tinyint(4) NOT NULL,

???`score` tinyint(4) DEFAULT NULL,

???PRIMARY KEY (`id`)

?) ;

1. SELECT sum(score) / count(*) FROM score WHERE cno = 2;
2. B.SELECT sum(score) / count(id) FROM score WHERE cno = 2;
3. C.SELECT sum(score) / count(sno) FROM score WHERE cno = 2;
4. D.SELECT sum(score) / count(score) FROM score WHERE cno = 2;
5. E.SELECT sum(score) / count(1) FROM score WHERE cno = 2;
6. F.SELECT avg(score) FROM score WHERE cno = 2;

所有的统计函数都会忽略空值(null)。

A :统计所有学生的平均分，就算成绩为空的学生，最后计算count(*)时也作为分母基数，计算得到所有学生的平均分。

B :与A一样，因为id主键非空，count(id)所得分母基数是所有学生。

C : 与B一样，非空属性sno。

D :由于score字段的值可能是空，空值在统计时忽略，所以count(score)和sum(score)统计的只是score不为空的学生，计算得到的平均分也只是有成绩的学生的平均分，无法计算所有学生的平均分。

E: count(1)与count(*)一样。

F:avg(score)会忽略空值,故计算结果为有成绩的学生的平均分。

A,B,C,E返回sum(score)除以行数；D,F返回sum(score)除以score不为null的行数

1、count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL。

2、count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL。

3、count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空

10、select语句完整语法如下：

select? 目标表的列名或列表达式序列

from 基本表名和（或）视图序列

[where 行条件表达式]

[group by? 列名序列 [having? 组条件表达式]]

[order by 列名 [asc | desc]]

则sql语句的执行顺序是：

解析：执行顺序：from—join--on-where--group by—avg/sum--having--select—distinct--order by

就是select要放后面，如果有order by，则order by放最后，因为order by 是对结果进行排序