SQL查询的艺术学习笔记--子查询

use seldata
--子查询返回单个值的情况
select sno,sname,cname,mark
from student as s
 join course as c
on s.cno=c.cno
where cname='计算机入门'
--采用内连接表等值连接的形式实现
--采用子查询的方式来实现上面功能
select sno,sname,student.cno,mark
from student
where student.cno=(select cno from course where cname='计算机入门')
--这条语句有问题 因为from后带两表先做交叉连接，后面的条件再进行筛选
--注意了。
select sno,sname,student.cno,mark,course.cname,course.cno
from student,course--交叉连接笛卡尔乘积
where student.cno=(select cno from course where cname='计算机入门')
--这条语句可以用
select sno,sname,cname,mark
from student as s ,course as c
where s.cno=c.cno and cname='计算机入门'
--在子查询中使用聚合函数
select * from teacher
select tno,tname,dname,sex,age
from teacher
where age>(select AVG(age) from teacher)
order by age
--子查询的比较运算符两边均采用聚合函数
select tno,tname,dname,cno,age
from teacher as t
where 
(select AVG(age) from teacher where dname=t.dname)>
(select AVG(age) from teacher)
--在select 语句中使用子查询
select tno,tname,dname,cno,
(select COUNT(*) from student
where cno=teacher.cno) as stotal 
--teacher.cno需要指定表名，而此值为查询from teacher值
from teacher 
order by stotal
--创建和使用返回多行的子查询
--语法：select column_name from table_name
--where test expression [not] in (subquery)
use seldata
--In 实现自连接查询
select s1.sname,s1.dname,s1.cno,s1.mark
from student as s1,student as s2
where s1.sno=s2.sno
and s2.mark <60
--学号相同，且另一表内有不及格的信息
order by  s1.sname
select * from student order by mark
--用子查询来实现
select sno,sname,dname,cno,mark
from student
where sno in (select sno from student where mark<60)
--查询学号有存在不及格成绩的信息
order by sno
--IN子查询实现集合交和集合差运算
--集合交运算
select sno,sname,dname,cno
from student where cno =1 and cno=10
--这个不能实现同时对一个列赋值两次
select sno,sname,dname
from student where cno=1 and sno in (select sno from student where cno=10)
--IN子查询实现集合差运算
select sno, sname, dname
from student
where cno=1
and sno not in (select sno from student where cno=10)
--exists子查询 存在即值为真，不存在为假 
--语法:
select  COLUMN_name
from table_name
where [not] exists (subquery)


select  tno,tname,dname,cno
from teacher as t
where  exists(select * from student where cno=t.cno)
order by tno


select * from teacher


select * from teacher
where exists (select sal from teacher)
--null 也做为真值处理，只是一个不确定的值
order by sex
--使用NOT EXISTS 子查询
select  * from course
select cno,cname,ctime,ctest
from course 
where cno not in(select cno from student)
order by cno
--in语句实现 in为实际存在的值
--使用exists语句实现
select cno,cname,ctime,ctest
from course as c
where not exists(select * from student where cno=c.cno)
--not exists为不存在的值为真
--exists实现两表交集
use seldata
select * from teacher
select * from course
select cno,tname,dname
from teacher
where sex='女'
and exists (select * from course where MONTH(ctest)=7
and cno=teacher.cno)
order by cno
--用连接表的形式实现
select t.cno,sex,tname,dname,MONTH(ctest) as ttime
from teacher as t
inner join course as c
on c.cno=t.cno
and sex='女'
and MONTH(ctest)=7
order by c.cno
--some / all 子查询
--语法结构如下：
--select  column_name
--from table_name
--where test expression some/all (subquery)
select * from student
select sno,AVG(mark) as avg_score
from student
group by sno
having AVG(mark)>=all(select AVG(mark) 
from student group by sno) 
--查询平均成绩最高的学生学号和平均成绩
select  tno,tname,dname,age 
from teacher
where age<all (select age from teacher where dname='计算机')
and dname<>'计算机'
order by age
--查询比计算机系年龄都小的教师
--使用集合函数实现
select  tno,tname,dname,age
from teacher 
where age<(select MIN(age) from teacher where dname='计算机')
--使用min(*)聚合函数
and dname<>'计算机'
order by age
--unique子查询 unique： 单一存在不重复值的查询
--语法：
select  column_name
from table_name
where [not] unique (subquery)
--sql并不支持unique
--以聚合函数来实现
select cno,cname,ctime,ctest
from course
where (select count(*) from student where cno=course.cno)=1
order by cno
--相关子查询
  select  cno,cname,ctime,ctest
  from course
  where '李华' in (select sname from student where cno=course.cno) 
  order by cno
  --使用连接查询来实现
  select c.cno,cname,ctime,ctest
  from course as c
  inner join student as s
  on c.cno=s.cno
  where sname='李华'
  order by c.cno
  --使用where 语句来实现
  select a.cno,cname,ctime,ctest
  from course as a,student as b
  where a.cno=b.cno
  and sname='李华'
  order by a.cno
  --使用比较运算符引入相关子查询
  --以where 语句实现
  select sname,dname,cno,mark
  from  student as s
  where (select ctest from course  where cno=s.cno)
  <cast('2006-7-2' as smalldatetime)
  --查询考试时间在2006.7.2之前考生姓名，系别，课程号，学习成绩
    order by sname
  --where 连接多表实现
  select sname,dname,c.cno,mark
  from course as c ,student as s
  where s.cno=c.cno
  and ctest<CAST('2006-7-2' as smalldatetime)
  order by sname
 --以inner join 实现
 select sname,dname,c.cno,mark
 from student as s
 inner join course as c
 on s.cno=c.cno and ctest<CAST('2006-7-2' as smalldatetime)
 order by sname
 --多表连接的方式实现子查询功能
 select * from course
select sname,dname,c.cno,cname,ctest,mark
from student as s,course as c
where c.cno=s.cno
and ctest<CAST('2006-7-2' as smalldatetime)
order by c.cno
 --以join on 实现
 select sname,dname,s.cno,cname,convert(char(11),ctest,20) as cctest,mark
 from student as s
 inner join course as c
 on s.cno=c.cno
 and ctest<CAST('2006-7-2' as smalldatetime) 
 order by sname
 --在HAVING子句中使用相关子查询
 select top 3 * from teacher
 select top 3 * from student
 select t.dname,COUNT(*) as tdname
 from teacher as t 
 group by t.dname
 having COUNT(*)
 <(select COUNT(*) from student where cno in 
  (select cno from teacher as t2 where t2.dname=t.dname))
 --查询统计出那些系中教师少于选修学生人数的系
 --嵌套子查询
 select * from course
 where cno in(select cno from teacher where dname='计算机' 
 and not exists (select * from student where cno=teacher.cno
 and mark < 60))
 order by cno
 --查询教师所开计算机系课程，所开课程的学生均合格
 --通过两个子查询来实现
 select * from course
 where cno in (select cno from teacher where dname='计算机')
 and 
 not exists(select  * from student where cno=course.cno and mark<60)
 order by cno
select * from student
--使用子查询创建视图
--语法
create view <view_name> [(column1,coulumn2...)]
as 
select  <column_name>
from    <table_naem>
use seldata
create view view_avgmark(cno,avgmark)
as 
select cno,AVG(mark)
from student
group by cno
select * from view_avgmark
exec sp_rename 'view_avgmark', 'avgmark'
--上面语句为修改视图表名，同样也可以用于修改表名
语法：exec sp_rename '原表名','重命名表名'
select * from avgmark
select cno,cname,ctest
from course
where cno in (select cno from avgmark where avgmark>70)
order by cno
--使用视图：通过查询视图取得平均成线大于70课程号信息
--不使用视图,用子查询带聚合函数
select cno,cname,ctest
from course as c
where cno in (select cno from student
group by cno
having AVG(mark)>70)
order by cno
select cno from student order by cno
--树查询晚上完成