2017.11.14
二:最基础的连接查询----多表查询
1.使用内部连接
内部连接也叫自然连接,最常见的连接形式
语法:
select 选择列表 from 表名1 ,表名2 where 表名1.列名1=表名2.列名2
或select 选择列表 from 表名1 join 表名2 on 表名1.列名1=表名2.列名2
例1:查询每个学生及其选修课程情况:
select student.*,sc.* from student,sc where student.sno=sc.sno
select student from student,sc where studnet.sno=sc.sno
select student.sno,sname,cno from student,sc where studnet.sno=sc.sno(唯一列可以不写来源)
例2:查询所有学生的学号,姓名和成绩:
select x.sno,x.sname,y.grade from student as x, sc as y where x.sno=y.sno
或select x.sno,sname,grade
自身连接(一个表和自己进行连接):
例3:查询所有比'刘晨'大的学生的姓名和年龄:
select X.sname,X.sage from student as X,student as Y where X.sage>Y.sage and Y.sname='刘晨'
三:复合条件连接(where子句中含多个连接条件)
例4:查选修2号课程且成绩在90分以上的所有学生的学号,姓名:
select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno='2' and sc.grade>90
例5:查询2号课程的成绩在前3名学生的姓名:
select top 3 sname as 前3名学生 from student,sc where student.sno=sc.sno and sc.cno=2 order by sc.grade desc
例6:查询每个学生的学号,姓名,选修的课程名及成绩:
select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
五:以join关键字指定的连接:
从多个表合并数据,并涉及多个表之间的连接,多表间的连接可分为内连接,外连接和交叉连接.被连接的表可以来自同一或不同的数据库
1.内连接:
select 列名 from <数据源> join <数据源> on 连表条件
说明:(1)内连接按照on指定的连接条件合并两个表,返回满足条件的行
(2)作用等价利用where子句进行连表查询
1.在学生数据库中,查询学号,姓名,成绩
select student.sno,sname,grade from student join sc on student.sno=sc.sno
select x.sno,sname,grade from
多表连接:
from(表1 join 表2 on 表1.字段号=标. 字段号 join 表3 on 表1.字段号=表3.字段号)
例6:查询每个学生的学号,姓名,选修的课程名及成绩:
select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
select studnet.sno,sname,cname,grade from (student join sc on student.sno=sc.sno) join course on sc.cno=course.cno
2.外连接:
3种类型:
1.左外连接 left out join 返回所有匹配的行并从关键值join左边的表中返回
2.右外连接
3.完全外连接
语法:
select 列名 from <数据源> [left][right][full]oueter join <数据源> on 连表条件
1.显示所有学生的学号,姓名,考试成绩(使用左连接):
select x.sno,sname,grade from student x left outer join sc y on x.sno=y.sno
2.显示所有学生的学号,姓名,考试成绩(使用右连接):
select y.sno,sname,grade from sc x right outer join studnet y on x.sno=y.sno
3.显示所有学生的学号,姓名,考试成绩(使用完全连接):
select x.sno,sname,grade from student x full outer join sc y on x.sno=y.sno
3.交叉连接(非限制连接,将两个表不加任何限制的组合起来,没有where子句的交叉连接将产生连接所涉及的表的笛卡儿积):
select 列名 from <数据源> cross join <数据源>
查询所有学生选课的可能情况:
select sno,sname,cno,cname from student cross join course
或 select sno,sname,cno,cname from student,course
(一般不加条件,非加的话用where)加条件:select sno,sname,cno,cname from student cross join course where sdept='计算机'and student.sno=sc,sno
4.联合查询(把多个select语句返回结果集组合到一个结果集):
select 语句1 union [all] select 语句2
注意:
1.union运算符连接的查询必须有类似的数据类型,相同的字段数目,并且在字段列表中字段顺序相同
2.默认情况下,SQL server2008将自动删除结果集中重复的记录.如果想要返回所有的数据,可以在union后增加all选项
3.第一个select语句的字段名决定了整个结果集的字段名,如果想要改变结果集里显示
4.
查询学校所有师生的信息:
select sno 编号,sname 姓名 from studnet union select tno,tname from teacher
练习:
use students
go
--1、查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。
select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno='2' and sc.grade>90
--2、查询所有学生的学号、姓名、选修的课程名及成绩。
select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
--、用自身连接查询所有比“张立”小的学生的姓名和年龄。
select X.sname,x.sage from student as X ,student as Y where X.sage<Y.sage and Y.sname='张立'
--4、查询1号课程的成绩在前2名学生的姓名 。
select top 2 sname as 前2名学生 from student,sc where student.sno=sc.sno and sc.cno=1 order by sc.grade desc
--5、查询所有计算机专业的学生的选课情况（包括姓名，课程号及成绩）。
select student.sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and sdept like '计算机'
--6、查询选修课成绩在80分以上的学生姓名。
select student.sname from student,sc where student.sno=sc.sno and sc.grade>80
--7、统计男女同学选修课的平均分和最高分。
select ssex,AVG(grade) as 平均分,MAX(grade) as 最高分 from sc,student.sno=sc.sno group by ssex
或:select ssex,AVG(grade) as 平均分,MAX(grade) as 最高分 from student join sc on student.sno=sc.sno group by ssex
--8、用自然连接查询电子专业女生的姓名、选修的课程名及成绩。
select sname,sdept,grade from student a join sc b on a.sno=b.sno join course c on b.cno =c.cno where a.sdept like '电子' and a.ssex ='女'
或:select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and ssex='女'and sdept='电子'
或:select student.sno,sname,cname,grade from(student join sc on student,sno=sc.sno)join course......
--9、用内连接查询电子专业男生的学号、姓名、选修的课程名及成绩。
select a.sno,a. sname,a.sdept,b.grade from student a join sc b on a.sno=b.sno join course c on b.cno=c.cno where a.sdept like '电子' and a.ssex='男'
--10、用外连接查询所有学生的选课情况，包括没有选课的情况。
select x.sno,sname,cno from student x full outer join sc y on x.sno=y.sno
--11、使用交叉连接查询所有学生选课的可能情况。
select sno,sname,cno,cname from student cross join course
--12、使用UNION联合查询查询学校所有师生的信息。（在数据库中创建“teacher表”,字段有tno,tname,ssex。数据类型参考student表。“teacher表”数据自由添加5行）。
select sno 编号,sname 姓名,ssex 性别 from student union select tno,tname,tsex from teacher
