常用SQL查询语句考试答案及解释
%代表任意多个字符_代表一个字符
如果我就真的要查%或者_，怎么办呢？使用escape，转义字符后面的%或_就不作为通配符了，注意前面没有转义字符的%和_仍然起通配符作用
select username from gg_user where username like'%xiao_%'escape'';
select username from gg_user where username like'%xiao%%'escape'';
A as B，就是给A起个别名叫B
select a.* from table_1 as a就是给table_1起个别名叫a，因此前面就可以使用a.*了
比如name as 姓名这样的话，查询出来的列就是写姓名
一、单表查询练习
1、查询<学生信息表>，查询学生"张三"的全部基本信息
Select *
from A_studentinfo
where sname='张三'
2、查询<学生信息表>，查询学生"张三"和”李四”的基本信息
Select *
from A_studentinfo
where sname='张三'
or sname='李四'
3、查询<学生信息表>，查询姓"张"学生的基本信息
Select *
from A_studentinfo
where sname like '张%'
4、查询<学生信息表>，查询姓名中含有"四"字的学生的基本信息
Select *
from A_studentinfo
where sname like '%四%'
5、查询<学生信息表>，查询姓名长度为三个字，姓“李”，且最后一个字是“强”的全部学生信息。
select *
from A_studentinfo
where sname like '李_强'
6、查询<学生信息表>，查询姓"张"或者姓”李”的学生的基本信息。
Select *
from A_studentinfo
where sname like '张%'
or sname like '李%'
7、查询<学生信息表>，查询姓"张"并且"所属省份"是"北京"的学生信息
Select *
from A_studentinfo
where sname like '张%'
and province='北京'
8、查询<学生信息表>，查询"所属省份"是"北京"、”新疆”、”山东”或者"上海"的学生的信息
Select *
from A_studentinfo
where province in ('北京','上海','新疆','山东')
9、查询<学生信息表>，查询姓"张"，但是"所属省份"不是"北京"的学生信息
Select *
from A_studentinfo
where sname like '张%'
and province !='北京'
10、查询<学生信息表>，查询全部学生信息，并按照“性别”排序，性别相同的情况下按照“所属省份”排序，所属省份相同的情况下再按照“班级”排序
select *
from A_studentinfo
order by sex,province,class
11、查询<学生信息表>，查询现有学生都来自于哪些不同的省份
select distinct province as 省份
from A_studentinfo
12、查询<学生选修信息表>，查询没有填写成绩的学生的学号、课程号和成绩
Select *
from A_studentcourse
where score is null
13、查询<学生选修信息表>，查询全部填写了成绩的学生的选修信息，并按照“成绩”从高到低进行排序
Select *
from A_studentcourse
where score is not null
order by score desc
二、聚合函数练习
1、统计<学生信息表>，统计共有多少个学生
Select count (*) as 学生数量
from A_studentinfo
2、统计<学生信息表>，统计年龄大于20岁的学生有多少个
Select count(*) as 学生数量
from A_studentinfo
where (2008-yearofbirth)>20
3、统计<学生信息表>，统计入学时间在1980年至1982年的学生人数
select count(*) as 学生数量
from A_studentinfo
where enrollment between '1998-01-01' and '2003-12-30'
对比以下查询方式，看看有何不同，为什么？
select count(*) as 学生数量
from A_studentinfo
where enrollment between '1998' and '2003'
4、统计<学生选修信息表>，统计学号为"S001"的学生的平均成绩
Select avg(score) as 平均成绩
from A_studentcourse
where sno='S001'
5、统计<学生选修信息表>，统计学号为"S001"的学生的总成绩
select sum(score) as 总成绩
from A_studentcourse
where sno ='S001'
6、统计<学生选修信息表>，查询课程号为”C001”的课程的最高成绩
select max(score) as 最高成绩
from A_studentcourse
where cno='C001'
7、统计<学生信息表>，查询所有学生中的最大年龄是多少
select 2008-min(yearofbirth) as 最大年龄
from A_studentinfo
三、分组查询练习
1、统计<学生选修信息表>，统计每个课程的选修人数
select cno,count(*) as 学生数量
from A_studentcourse
group by cno
2、统计<学生选修信息表>，统计每个同学的总成绩
select sno,sum(score) as 总成绩
from A_studentcourse
group by sno
3、统计<学生信息表>，统计每个班级中每种性别的学生人数，并按照班级排序select class as 班级,sex as 性别, count(*) as 人数
from A_studentinfo
group by class,sex
order by class
4、统计<学生选修信息表>，统计每门课程的平均成绩,并按照成绩降序排序
Select cno,avg(score) as 平均成绩
from A_studentcourse
group by cno
order by avg(score) desc
5、统计<学生选修信息表>，显示有两门以上课程不及格的学生的学号
Select sno as 不及格学生学号
from A_studentcourse
where score<60
group by sno
having count(*)>1
（1. Group By 语句简介：
Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。
2.Group By 的使用：
2.1 Group By [Expressions]:
这个恐怕是Group By语句最常见的用法了，Group By + [分组字段](可以有多个)。在执行了这个操作以后，数据集将根据分组字段的值将一个数据集划分成各个不同的小组。比如有如下数据集，其中水果名称(FruitName)和出产国家(ProductPlace)为联合主键：
如果我们想知道每个国家有多少种水果，那么我们可以通过如下SQL语句来完成：
SELECT COUNT(*) AS水果种类,ProductPlace AS出产国
FROM T_TEST_FRUITINFO
GROUP BY ProductPlace
这个SQL语句就是使用了Group By + 分组字段的方式，那么这句SQL语句就可以解释成“我按照出产国家(ProductPlace)将数据集进行分组，然后分别按照各个组来统计各自的记录数量。”
2.2 Group By All [expressions] ：
其中有这么一句话“如果使用ALL关键字，那么查询结果将包含由Group By子句产生的所有组...没有ALL关键字，那么不显示不符合条件的行组。”这句话听起来好像挺耳熟的，对了，好像和LEFT JOIN 和RIGHT JOIN 有点像。其实这里是类比LEFT JOIN来进行理解的。还是基于如下这样一个数据集：
FruitName ProductPlace Price
Apple China$1.1
Apple Japan$2.1
Apple USA$2.5
Orange China$0.8
Banana China$3.1
Peach USA$3.0
首先我们不使用带ALL关键字的Group By语句：
SELECT COUNT(*)AS水果种类,ProductPlace AS出产国
FROM T_TEST_FRUITINFO
WHERE(ProductPlace<>'Japan')
GROUPBY ProductPlace
那么在最后结果中由于Japan不符合where语句，所以分组结果中将不会出现Japan。
现在我们加入ALL关键字：
SELECT COUNT(*)AS水果种类,ProductPlace AS出产国
FROM T_TEST_FRUITINFO
WHERE(ProductPlace<>'Japan')
GROUPBY ALL ProductPlace
重新运行后，我们可以看到Japan的分组，但是对应的“水果种类”不会进行真正的统计，聚合函数会根据返回值的类型用默认值0或者NULL来代替聚合函数的返回值。
。。。。。。
having 子句
假如元组在分组前需要按照某种方式加以限制，使不需要的分组为空，可以在group by子句后加一个having子句。
having子句与where有相似之处但也有区别,都是设定条件的语句。
在查询过程中聚合语句(sum,min,max,avg,count)要比having子句优先执行.而where子句在查询过程中执行优先级别优先于聚合语句。
having条件表达示为聚合语句。肯定的说having子句查询过程执行优先级别低于聚合语句。
再换句话说把上面的having换成where则会出错。统计分组数据时用到聚合语句。
对分组数据再次判断时要用having。如果不用这些关系就不存在使用having。直接使用where就行了。
having就是来弥补where在分组数据判断时的不足。因为where执行优先级别要快于聚合语句。
聚合函数，这是必需先讲的一种特殊的函数：
例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。
SELECT SUM(population) FROM tablename
这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即所有国家的总人口数。通过使用GROUP BY 子句，可以让SUM 和COUNT 这些函数对属于一组的数据起作用。
当你指定GROUP BY region 时，属于同一个region（地区）的一组数据将只能返回一行值．
也就是说，表中所有除region（地区）外的字段，只能通过SUM, COUNT等聚合函数运算后返回一个值．
HAVING子句可以让我们筛选成分组后的各组数据．
HAVING子句在聚合后对组记录进行筛选
而WHERE子句在聚合前先筛选记录．也就是说作用在GROUP BY 子句和HAVING子句前
一、显示每个地区的总人口数和总面积．
SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段（一或多条记录）作运算。
二、显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。
SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000
在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。
相反，HAVING子句可以让我们筛选成组后的各组数据．
）
6、统计<学生信息表>，统计每个班级中的最大年龄是多少
select class as 班级, 2008-min(yearofbirth) as 最大年龄
from A_studentinfo
group by class
四、嵌套查询练习
1、用子查询实现，查询选修“高等数学”课的全部学生的总成绩
select sum(score) as 高等数学总成绩
from A_studentcourse
where cno=
(
select cno
from A_courseinfo
where subject='高等数学'
)
2、用子查询实现，统计<学生选修信息表>，显示学号为"S001"的学生在其各科成绩中，最高分成绩所对应的课程号和成绩
select score,cno
from A_studentcourse
where sno='S001'
and score =
(
select max(score)
from A_studentcourse
where sno ='S001'
)
思考：如果该学号学生有两个课程分数都为最高的100分，查询会有什么结果
3、用子查询实现，查询2班选修"数据库技术"课的所有学生的成绩之和
select sum(score) as 数据库技术总成绩
from A_studentcourse
where cno =
(
select cno
from A_courseinfo
where subject='数据库技术')
and sno in
(
select sno
from A_studentinfo
where class='2'
)
4、用子查询实现，查询3班"张三"同学的"测试管理"成绩
select score
from A_studentcourse
where cno=
(
select cno
from A_courseinfo
where subject='测试管理'
)
and sno in
(
select sno
from A_studentinfo
where class='3'
and sname='张三'
)
五、联接查询练习
1、查询"张三"的各科考试成绩，要求显示姓名、课程号和成绩
select sname as 姓名,cno as 课程号,score as 成绩
from A_studentinfo,A_studentcourse
where A_studentinfo.sno=A_studentcourse.sno
and sname='张三'
2、查询"张三"的各科考试成绩中，哪科没有记录考试成绩，要求显示姓名、课程号和成绩select sname as 姓名,cno as 课程号,score as 成绩
from A_studentinfo,A_studentcourse
where A_studentinfo.sno=A_studentcourse.sno
and sname='张三'
and score is null
3、查询"张三"的各门课程成绩，要求显示姓名、课程名称和成绩
select sname as 姓名,subject as 课程名称,score as 成绩
from A_studentinfo,A_courseinfo,A_studentcourse
where A_studentcourse.sno=A_studentinfo.sno
and A_https://www.wendangku.net/doc/8617503842.html,o=A_https://www.wendangku.net/doc/8617503842.html,o
and A_studentinfo.sname='张三'
4、查询3班"张三"的"测试管理"成绩，要求显示姓名、成绩
select sname as 姓名,score as 成绩
from A_studentcourse,A_courseinfo,A_studentinfo
where A_https://www.wendangku.net/doc/8617503842.html,o=A_https://www.wendangku.net/doc/8617503842.html,o
and A_studentcourse.sno=A_studentinfo.sno
and subject='测试管理'
and class='3'
and sname='张三'
5、查询所有2000年以前入学的，各班男生的各科考试平均成绩
select class as 班级,avg(score) as 男生平均成绩
from A_studentcourse,A_courseinfo,A_studentinfo
where A_https://www.wendangku.net/doc/8617503842.html,o=A_https://www.wendangku.net/doc/8617503842.html,o
and A_studentcourse.sno=A_studentinfo.sno
and sex='男'
and enrollment<'2000-01-01'
group by class
六、外联接查询
查询李坚强所有课程的成绩，并显示学号、姓名、课程号和成绩，没有成绩记录的学号包括：('S009','S010','S011') 1、使用左联接
select A_studentinfo.sno as 学生表学号,sname as 姓名,A_studentcourse.sno as 成绩表学号, cno as 课程号,score as 成绩
from A_studentinfo
left join A_studentcourse
on A_studentinfo.sno=A_studentcourse.sno
where sname='李坚强'
2、使用右联接
select A_studentinfo.sno as 学生表学号,sname as 姓名,A_studentcourse.sno as 成绩表学号, cno as 课程号,score as 成绩
from A_studentcourse
right join A_studentinfo
on A_studentinfo.sno=A_studentcourse.sno
where sname='李坚强'
3、对比等值连接
select sname,score
from A_studentinfo,A_studentcourse
where A_studentinfo.sno=A_studentcourse.sno
and sname='李坚强'
七、补充提高
1、查询“张三”比“王三”入学早几年
select A.sname as 姓名,year(A.enrollment) as 入学时间,
B.sname as 姓名,year(B.enrollment) as 入学时间,
datediff(year,A.enrollment,B.enrollment) as 年差
from A_studentinfo A,A_studentinfo B
where A.sname='张三'
and B.sname='王三'
2、查询所在班级和该班内学生的年龄之和，其中每个人的年龄都大于20岁，每个班的年龄之和大于60岁select class as 班级,sum(2008-yearofbirth) as 年龄和
from A_studentinfo
where (2008-yearofbirth) >20
group by class
having sum(2008-yearofbirth)>60
order by class
3、计算每种产品的剩余库存量
表1，为产品进货表，产品名称name[char(10)]，产品数量amount[int]
表2，为产品出货表，产品名称name[char(10)]，产品数量amount[int]
业务逻辑：表1存储产品的总进货量，表2存储每种产品每次的出货量，如产品A进货为100，出货了3次，每次分别为10、20、30，那么A产品的库存就为40
表A1 | 表A2
---------------------------------|---------------------------
name amount | name amount
A 100 | A 10
B 100 | A 20
| A 30
| B 10
| B 40
--方法1
--建立视图
create view A2Sum
as
select name,sum(amount) as thesum
from A2
group by name
--关联查询
select https://www.wendangku.net/doc/8617503842.html,,A1.amount-A2Sum.thesum
from A1,A2Sum
where https://www.wendangku.net/doc/8617503842.html,=https://www.wendangku.net/doc/8617503842.html,
--方法2
select https://www.wendangku.net/doc/8617503842.html,,A1.amount-A2Table.A2amount as stocks
from A1,(select name,sum(amount) as A2amount from A2 group by name) as A2Table
where https://www.wendangku.net/doc/8617503842.html,=https://www.wendangku.net/doc/8617503842.html,
--方法3
--定义单个产品变量
declare @aa int
set @aa=
(
select sum(amount)
from A2
where name='A'
group by name
)
--计算单个产品剩余
select name,amount-@aa as leave from A1
where name='A'