SQL查询的艺术学习笔记--多表查询

USE seldata
SELECT * FROM sys.tables
select * from teacher order by tno
insert into teacher values(1,'王军',4,800,'数学','男',32)
create table student
(
sno char(4) not null,
sname char(20) not null,
dname char(20) not null,
sex char(2) not null default '男',
cno int ,
mark decimal(3,1) ,
type char(4) 
)
select * from student
insert into student values('9701','刘建国','管理工程','男',4,82.5,'必修')
insert into student values('9701','刘建国','管理工程','男',10,70,'选修')
insert into student values('9701','刘建国','管理工程','男',1,78.5,'选修')
insert into student values('9702','李春','环境工程','女',5,63,'必修')
insert into student values('9702','李春','环境工程','女',10,58,'选修')
insert into student values('9703','王天','生物','男',5,48.5,'必修')
insert into student values('9703','王天','生物','男',2,86,'选修')
insert into student values('9704','李华','计算机','女',4,76,'必修')
insert into student values('9704','李华','计算机','女',1,92,'必修')
insert into student values('9704','李华','计算机','女',2,89,'必修')
insert into student values('9704','李华','计算机','女',9,80,'必修')
insert into student values('9704','李华','计算机','女',8,70,'选修')
insert into student values('9705','孙庆','电子工程','男',8,79,'必修')
insert into student values('9705','孙庆','电子工程','男',1,59,'必修')
insert into student values('9705','孙庆','电子工程','男',11,52,'必修')
insert into student values('9705','孙庆','电子工程','男',8,68,'必修')
insert into student values('9706','高伟','机械工程','男',13,93,'必修')
insert into student values('9706','高伟','机械工程','男',12,88.5,'必修')
insert into student values('9706','高伟','机械工程','男',1,78,'选修')
insert into student values('9706','高伟','机械工程','男',10,76,'选修')
--简单的二表连接查询
select * from teacher
select * from course
select tname,dname,cname,ctest from teacher,course 
where teacher.cno=course.cno
--别名查询
select a.tname,a.dname,b.cname,cast(b.ctest as CHAR(11)) from teacher a,course b
where a.cno=b.cno order by a.dname
--Where进行条件搜索
select tname,dname,cname,ctest from teacher,course order by cname
delete from teacher where tno=1
insert into teacher select * from seldata2.dbo.teacher where tno=1
--表中记录错误，从另一数据库表复制一条记录至现有表中。
select tname,dname from teacher
select cname,ctest from course
select * from student
select * from course
select * from teacher
select * from course
select sname as '学生',student.dname as '系别',cname as '学科',
       ctest as '考试时间', mark as '成绩',tname as '教师' 
       from teacher,course,student
       where student.cno=course.cno
       and teacher.cno=student.cno
       order by sname
--使用表别名进行查询
select sname,s.dname,cname,ctest,mark,tname
from teacher t,course c,student s
where s.cno=c.cno
and t.cno=c.cno
order by sname 
--采用JOIN关键字建立连接     
--JOIN语法：select column from join_table join_type join_table on join_condition)
--自然连接
--查询有不及格学科学生：
select distinct sno from student where mark<60
--distinct 去除重复学号
select sno,sname,dname,cno,mark 
from student
where sno in('9702','9703','9705')
order by sname
--采用自连接完成上述功能
select distinct 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 sname
select s1.*
from student as s1,student as s2 order by sname
select * from course order by cno
select * from teacher order by cno
select * from course,teacher  order by course.cno 
--采用子查询来实现
select a.* from student as a
where sno in(select DIstinct sno from student 
where mark<60)
--自然连接（natural join)
use seldata
select sname,dname,cno,tname from student natural join teacher--SQL不支持自然连接
--注：具有相同列自动进行匹配 自然连接的相同项在结果只出现一次
--等同于如下语句：
select sname,s.dname,s.cno,tname 
from student as s,teacher as t
where s.dname=t.dname
and s.cno=t.cno
select * from student
select * from teacher
--使用where 子句进行等值连接
select sname,s.dname,s.cno,tname 
from student as s,teacher as t
where s.cno=t.cno
--内连接：内连接可按过滤条件分为：等值连接  不等值连接
--内连接等值连接
select  distinct sname,s.dname,s.cno,tname 
from student as s,teacher as t
where s.cno=t.cno 
--distinct 区别的是两行完全相同的值去重
--说明：等值内连接相同行是同时出现的
select s.sname,s.dname,s.cno,t.cno,t.tname
from student as s,teacher as t
where s.cno=t.cno
--内连接的不等值连接
select  s.sno,sname,s.dname,t.dname,s.cno,t.cno, mark,tname
from student as s
inner join teacher as t
on s.dname<>t.dname
and s.cno =t.cno 
order by sname
use seldata
--使用where语句来实现join语句功能
select s.sno,sname,student.dname,t.dname,s.cno,t.cno,mark,tname
from student as s,teacher  as t
where s.dname<>t.dname and s.cno=t.cno
order by sname
--使用inner join 实现多表的内连接
select sname,s.dname,cname,ctest,mark,tname
from teacher as t
inner join  course as c
on t.cno=c.cno
inner join student as s
on t.cno=s.cno
order by ctest
select * from student where 0=1
--外连接：out join 分为三种类型
--左外连接 left outer  join
--右外连接 right outer  join 
--全外连接 full outerjoin


--左连接  显示左边表不配匹行
select s.sno,sname,s.cno,cname,ctest,mark
from student as s
left join course as c
on s.cno=c.cno
order by sname
--左连接包括了左表的所有行，而连接表则只有所有匹配左表所有行出现
--使用where *= 语句来实现左外连接 即左表的所有行 ‘ * ’ 和相关表的匹配行
select s.sno,sname,s.cno,mark, cname,ctest
from student as s,course as c
where s.cno *= c.cno
order by sname
--会提示兼容问题，可能过以下语句修改数据库安全级别
alter database seldata
set compatibility_level=80


--右外连接 显示右边表所有不配匹行
select s.sno,sname,s.cno,mark,cname,ctest
from student as s
right join course as c
on s.cno=c.cno
order by sname
--同样使用where '=*‘语句来实现右外连接，’=*‘即表示右边所有行
select * from student where 1=2
select * from course 
select sno,sname,dname,sex,mark,type,cname,ctime,scount,ctest
from student as s,course as c
where s.cno=*c.cno
order by sname
--通过查询结果创建表
--语法：select * into <表名> from （查询语句） as 任意别名  where 条件语句
--一定要加别名，因为查询的结果本就是一个系统表
--加别名后直接从别名复制表，同样还能加条件语句进一步过滤
select * into  sc from (select sno,sname,dname,sex,mark,type,cname,ctime,scount,ctest
from student as s,course as c where s.cno=*c.cno ) as qq
select * from (select sno,sname,dname,sex,mark,type,cname,ctime,scount,ctest
from student as s,course as c where s.cno=*c.cno)  as qq where qq.sname='孙庆'
select * from qq
select * from sc
drop table sc,#sc
select * from sys.tables 
--通过查询结果创建视图
create view aqq as select sno,sname,dname,sex,mark,type,cname,ctime,scount,ctest
from student as s,course as c where s.cno=*c.cno 
select * from aqq


--全外连接full inner join on(包括左右两表所有不匹配的行）
select  s.sno,sname,s.cno,cname,ctest,mark
from student as s
full join course as c
on s.cno=c.cno
order by sname


--交叉连接查询（cross ineer join)
select * from student,course
select * from student cross  join   course


select sno,sname,mark,dname,sex,type,cname 
from student as a cross join course as b  
where a.cno=b.cno  and mark>=60 --查询分数在60分以上的人


select count(*) as 及格人数,AVG(mark) as 平均分数,
MAX(mark) as 合格最高分数,MIN(mark) as 合格最低分数
from (select sno,sname,mark,dname,sex,type,cname 
from student as a cross join course as b  
where a.cno=b.cno  and mark>=60)  as bb --统计分数级格的人数和平均分
--同样，我们都会将查询结果作为一个别名表来处理。
 
select sno,sname,mark,dname,sex,type,cname 
from student as a cross join course as b  
where a.cno=b.cno  and mark<60


--综合处理（自已加的）
--通过对两张表的查询得到视图，并对视图进行统计
select top 3 * from student 
select top 3 * from course
select top 3 * from teacher


create view t_view as 
select sno,sname,a.dname,a.sex,mark,cname,tname,c.dname  as dtname
from student as a
join course as b
on a.cno=b.cno
join teacher as c
on a.cno=c.cno--注意：视图不能进行排序？
--order by sname 


select * into ttable from 
(select sno,sname,a.dname,a.sex,mark,cname,tname,c.dname  as dtname
from student as a
join course as b
on a.cno=b.cno
join teacher as c
on a.cno=c.cno) as qq
--order by sname 注意：同样不能加排序
use seldata
select * from t_view
select sname,count(sname),AVG(mark) as 平均分 from t_view group by sname
select * from ttable
--Union和Union join 多表的并运算 多表的并连接
--补充：晚上补习集合的运算
select sno,sname,dname
from student where cno=1
union
select sno,sname,dname
from student where cno=10
--union 去两个子集并运算会除掉重复值
select distinct sno,sname,dname from student where cno=1 or cno=10
以where OR 语句实现上述功能
select sno,sname,dname
from student
where cno=1
union all   
select sno,sname,dname
from student where cno=10
--加入all表示所有数据进行连接，不做去除重复处理
--等同于以下or语句
select sno,sname,dname from student where cno=1 or cno=10
--union 对不同字段和不同表进行连接
select sno,sname,dname from student where cno=5 or cno=13
union
select tno,tname,dname from teacher where cno=5 or cno=13


select top 3 * from student 
union
select * from teacher
--使用order by 对union运算结果进行排序
select sname,dname,cno from student where cno in(5,13)
union 
select tname,dname,cno from teacher where cno in(5,13)
order by 3 desc
--注意order by 可以对sname,dname,cno进行排序处理
--order by 序号 同样也可以进行排序
select sname,dname,cno from student order by 3 desc
--union多表并运算 A U B U C 操作
select cno from student where sex='男'
union all
select cno from teacher where sex='女'
union all
select cno from course where ctime >40
order by cno
use seldata
--连接表进行聚合运算
--在内连接中使用聚合函数
select t.cno,COUNT(s.cno) as nums
from teacher as t
inner join student as s
on t.cno=s.cno
group by t.cno
order by nums 


select * from teacher
join student
on teacher.cno=student.cno
order by teacher.cno 
--查询数据


--在外连接中使用聚合函数
select t.cno,COUNT(s.cno) as nums
from teacher as t
left join  student as s
on t.cno=s.cno
group by t.cno
order by nums
select * from teacher order by tno
update teacher set cno=1 where tno=3
select * from teacher as t
left join student as a
on t.cno=a.cno
order by t.cno
--多表连接的综合运用<join 内连接和left外连接的使用>
 select t.tname,t.dname,cname,sname,mark
 from teacher as t
 left join course as c
 on t.cno=c.cno
 join student as s
 on t.cno=s.cno
 order by t.tname
 --注意：使用join连接时我们需要的数据是完全根据等值匹配的
 --      使用left join 或者 right join则是按情况匹配的
 --       实际的使用看取值情况进行决定
 --多表连接综合运用（union)
 select sno,sname,cname,mark
 from student as s
 join course as c
 on s.cno=c.cno
 where cname='计算机入门'
 union 
 select sno,sname,cname,mark
 from student as s
 join course as c
 on s.cno=c.cno
 where cname='生物工程概论'
 order by sno