04连接查询

（1）student表

			Sno		Sname  Ssex	Sage	Sdept	nativeplace
			200215121	李勇	男	20	CS	NULL
			200215123	王蓝	女	18	MA	NULL
			200215122	刘晨	女	19	CS	NULL

（2）course表

			Cno		Cname	  Cpno
			1		数据库	    5
			2		数学	
			3		信息系统	    1
			4		操作系统	    6
			5		数据结构	    7
			6		数据处理		
			7	        Pascal      6

（3）sc表

		Sno		  	 Cno			Grade
		200215121		  1			 92
		200215121		  2			 85
		200215121		  3			 88
		200215122		  2			 90
		200215122		  3			 80

3.4.3连接查询

一、等值与非等值连接

1、连接查询的where子句中用来连接两个表的条件称为连接谓词或者连接条件。

	格式1：[表名1.]<列名1><比较运算符>[]<表名2.><列名2>
		比较运算符：=,>,<,>=,<=,!=(<>)等。

	格式2：[表名1.]<列名1>between[表名2.]<列名2>and[表名2.]<列名3>

2、当连接运算符为=时称之为等值连接，其他为非等值连接。

   连接谓词中的列名称称为连接字段。

   连接条件中的各连接类型必须是可比的，但名字不必相同。

问题1：查询每个学生及其选修课程的情况？

	格式：
		select student.*,sc.*
		from student,sc
		where student.Sno=sc.Sno

		-----------------------------------------------------------------------------
		Sno		Sname	Ssex	Sage	Sdept	nativeplace	  Sno	   Cno  Grade
		200215121	李勇	男		20		CS		NULL		200215121	1	92
		200215121	李勇	男		20		CS		NULL		200215121	2	85
		200215121	李勇	男		20		CS		NULL		200215121	3	88
		200215122	刘晨	女		19		CS		NULL		200215122	2	90
		200215122	刘晨	女		19		CS		NULL		200215122	3	80

	说明:
		select/where子句属性名前都加了表名前缀，是为了区分以免混淆。
		若参加连接的各表中属性名唯一，可以省略前缀。

	过程：
		首先在student表中找到第一个元祖，然后从头开始扫描sc表，逐一查找一student第一个
		元组的Sno相等的sc元组，找到后就将两个元组连接起来。形成一个元祖。sc全部查找完后。
		再找student中第二个元祖，重复。找完为止。这就是嵌套循环的基本思想。

	补充：
		如果在sc表Sno上建立索引的话，就不用了每次扫面全表sc,而是根据索引找到相应的sc元组。
		用索引查询sc中满足条件的元组一般会比扫描全表快。
		若在等值连接中把目标列中重复的属性列去掉则成为自然连接。

问题2：自然连接（同上问题）

		格式:
			select student.Sno,Sname,Ssex,Sage,Sdept,nativeplace,sc.Cno,Grade
			from student,sc
			where student.Sno=sc.Sno

			----------------------------------------------------------------------------
				Sno		Sname	Ssex	Sage	Sdept	nativeplace	Cno	Grade
			200215121	李勇		男		20		CS			NULL	 1	 92
			200215121	李勇		男		20		CS			NULL	 2	 85
			200215121	李勇		男		20		CS			NULL	 3	 88
			200215122	刘晨		女		19		CS			NULL	 2	 90
			200215122	刘晨		女		19		CS			NULL	 3	 80

		说明：Sname,Ssex,Sage,Sdept,nativeplace这五个属性两个表都有所以不用前缀。

问题3：查询选修二号课程且成绩在85分以上的所有学生的学号和姓名

		格式：
			select student.Sno,Sname
			from student,sc
			where student.Sno=sc.Sno and Cno = '2' and Grade>85

二、自身连接

自身连接：连接操作不仅是两个表之间的连接，也可以是一个表与其自身连接。

	问题：找出course先修课的先修课

			select first.Cno,second.Cpno
			from course first,course second
			where first.Cpno=second.Cno

	说明：首先要为course表取两个别名，一个first一个second

三、外连接

	背景：通常连接操作中，只有满足连接条件的元组才被连接展现出来。
		如student200215123学生没有选课连接时没有相对应选课记录，
		故将其舍弃。
		要想把那个学生信息显示在结果中，需要用到外连接。
		空信息仍为null。

	问题：((**左外连接**))（完全连接）

		select student.*,sc.*
		from student left outer join sc on(student.Sno=sc.Sno)

		-------------------------------------------------------------------------------------------
		Sno			Sname	Ssex	Sage	Sdept	nativeplace		Sno			Cno		Grade
		200215121	李勇		男		20		CS		NULL		200215121		1		92
		200215121	李勇		男		20		CS		NULL		200215121		2		85
		200215121	李勇		男		20		CS		NULL		200215121		3		88
		200215122	刘晨		女		19		CS		NULL		200215122		2		90
		200215122	刘晨		女		19		CS		NULL		200215122		3		80
		200215123	王敏		女		18		MA		NULL		   NULL			NULL	NULL

	说明：即使sc表中不存在student的Sno=200215123属性，也可以连接。

四、多表查询

	查询每个学生的学号，姓名，课程名，成绩。

	问题：
		select student.Sno ,Sname,course.Cname,sc.Grade 
		from student,course,sc
		where student.Sno =sc.Sno and sc.Cno =course.Cno 


		----------------------------------------------
		  Sno	   Sname	Cname	  Grade
		200215121	李勇		数据库		92
		200215121	李勇		数学		 85
		200215122	刘晨		数学		 90
		200215121	李勇		信息系统	88
		200215122	刘晨		信息系统	80

	说明：依次用and连接。

五、嵌套查询

嵌套查询：使用户多个可以用多个简单查询构成复杂查询，通过层层嵌套，体现出SQL的结构化查询语言。

问题1：查询选择二号课程的学生信息？

		select *
		from student
		where Sno in(
					select Sno
					from sc
					where Cno='2');

		----------------------------------------------------------------------
				Sno		Sname		Ssex		Sage	Sdept	nativeplace
				200215121	李勇		男		20	  CS	NULL
				200215122	刘晨		女		19	  CS	NULL

		说明：SQL语句允许多层嵌套。子查询结果不能用order by，order by只对最终查询结果排序。

 @@、带有in谓词的子查询
	在嵌套查询中，子查询结果往往是一个集合。所以嵌套中常用谓词in。

问题2：查询刘晨所在系的学生信息？

		select *
		from student
		where Sdept in
				(select Sdept
				from student
				where Sname='刘晨');

	说明：子查询的查询条件不依赖于父查询，称为不相关子查询。

		(1)间接结果：
		
				select s1.*,s2.*
				from student s1,student s2
				where s1.Sdept=s2.Sdept 		--and s2.Sname='刘晨'

		Sage	Sdept	nativeplace		Sno		Sname	Ssex	Sage	Sdept	
		李勇		男		20		CS	NULL			李勇	男	20	CS	NULL	
		刘晨		女		19		CS	NULL			李勇	男	20	CS	NULL	
		李勇		男		20		CS	NULL			刘晨	女	19	CS	NULL	
		刘晨		女		19		CS	NULL			刘晨	女	19	CS	NULL	
		王敏		女		18		MA	NULL			王敏	女	18	MA	NULL	
		小呆		女		21		MA	陕西西安		 王敏	 女	 18	 MA  NULL	
		王敏		女		18		MA	NULL			小呆	女	21	MA 	陕西西安	
		小呆		女		21		MA	陕西西安		 小呆	 女	 21	 MA  陕西西安	

		(2)最终结果：
				select s1.*
				from student s1,student s2
				where s1.Sdept=s2.Sdept and s2.Sname='刘晨'
		
				200215121	李勇	男	20	CS	NULL	软件工程
				200215122	刘晨	女	19	CS	NULL	大数据

问题3：查询选修课程名为数学的学生信息和学号？

		select *
		from sc,student,course
		where sc.Cno = course.Cno  and student.Sno = sc.Sno 

		Sno			Cno	Grade		Sno		Sname	Ssex	Sage	Sdept	nativeplace	 xibie	    Cno	Cname	   Cpno
		200215121	1	92		200215121	李勇		男		20		CS		NULL		软件工程	1	数据库	     5
		200215121	2	85		200215121	李勇		男		20		CS		NULL		软件工程    2	数学	
		200215122	2	90		200215122	刘晨		女		19		CS		NULL		大数据      2	 数学	
		200215121	3	88		200215121	李勇		男		20		CS		NULL		软件工程	3	信息系统	1
		200215122	3	80		200215122	刘晨		女		19		CS		NULL		大数据	     3	 信息系统    1
		-----------------------------
		select *
		from student
		where Sno in
			(select Sno
			from sc
			 where Cno in
				(select Cno
				 from course
				 where Cname='数学'));

				Sno		   Sname	Ssex	Sage	Sdept	nativeplace	xibie
				200215121	    李勇		男	20	CS	NULL		软件工程
				200215122	    刘晨		女	19	CS	NULL		大数据