SQL连接查询

连接查询

1 表示连接的格式

等值连接，自然连接，自身连接（自身的笛卡尔积连接），全外连接，左外连接，右外连接等待

连接条件(l连接谓词）写在where子句中

where   <表名1>.<列名1> <比较运算符> <表名2>.<列名2>

 <表名1>.<列名1>BETWEEN   <表名2>.<列名2> AND  <表名2>.<列名3>

比较运算符：大于小于，等于不等于之类的啦。

2 外连接

select Student.sno,Sname,Ssex,Sage,Sdept,Cno,Grade

from Student LEFT OUTER  JOIN SC ON  sc (Student.Sno=SC.Sno);
/使用USING 来去掉结果中的重复值，FROM Student LEFT OUTER JOIN SC USING(Sno);/using

注意: from Student LEFT OUTER JOIN SC ON sc (Student.Sno=SC.Sno) 的写法

3 多表连接

多表连接，连接是通过主键、外键建立的

比如学生、课程、选课

学生表中的一个学号，可能对应SC表中的多条选课记录

一条选课记录，唯一对应一个学号，一个课程号，大概就是找这种唯一对应的关系吧

4嵌套查询

一个select from where 语句称为一个查询块

查询块嵌套在where子句或者having短语的条件中

注意：子查询的select不能使用order by子句，order by子句只能对最终查询结果排序

不相关子查询

定义：子查询的查询条件不依赖于父查询

查询过程：先进行子查询，找到子查询的结果，where in （子查询的结果）作为父查询的条件

特点：查询条件各写各的，父查询处也会有查询条件

相关子查询

定义：子查询的查询条件依赖于父查询

查询过程：从外层查询中取出一个元组，将元组的值传递给内层查询，执行内层查询，用该值代替内存查询，得到外层查询。感觉就是把外层里的元组一个一个拿出来，往子查询里套，看是否满足条件

特点：条件是写在最后的子查询里的。

5 带有ANY ALL 谓词的子查询

子查询返回单值时可以用比较运算符，但返回多值时要用ANY或ALL谓词修饰符。

sqlite中没有关键字ANY

all是满足所有返回结果的交，即返回的每一条记录都要满足

any是满足所有返回结果的并，即存在一条记录满足即可

可以用max,min进行转换，值返回一条记录，此时就可以用比较运算符代替ANY,ALL

存在，>min,<max

全部 ，>max,<min

聚合函数的查询效率要比ANY | ALL的查询效率要高

6 带有EXIST 的谓词查询

存在量词，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑值“true”| “false”

查询过程：如果内层查询结果非空，则外层的where子句返回真值。

exists 是有一个满足条件即可

not exists要验证返回的每一个条件

由于EXISTS量词的子查询只关心是否有返回值，并不需要查具体值，因此比较高效

例题：1

注意在EXISTS中，from sc,只选择了一张表，student表来自于上层查询，sno=student.sno,依赖于父查询，因此为相关子查询。

查询过层为，从student表中拿出第一条记录，然后在exists中验证真值，存在符合一条的记录student的这条记录值可以输出。如此往复，验证完student的所有记录

例题2

查询选修了全部课程的学生姓名

等价于不存在一门课程，这个学生没有学过

select   stu_name
from student
where not exists      -- 不存在一门课程
		 (select * 
		 from course
		 where not exists     -- 该生没有学过
				 (select * 
				 from sc
				 where  stu_no = student.stu_no AND cou_no = course_no));

例题3

查询至少选修了学生2018001选修的所有课程的学生号码

====》不存在这样的课程，学生2018001学过，而学生x没有学过

select  distinct stu_no
from  sc sc1
where not exists 
			(select *
			from  sc sc2
			where sc2.sno = '2018001'
			AND not exists
						 (select *
						 from sc sc3
						 where sc3.sno=sc1.sno
						 AND sc3.cno= sc2.cno));

其实还没参透其中真谛

7 集合查询

UNION 并 ，并操作系统会自动去重，如果要保留重复元组，则用UNION ALL

INTERSECT 交

EXCEPT 差

UNION INTERSECT EXCEPT是在两个完整的select —from —where 句子中使用。

在集合单位上使用

8 基于派生表的查询

子查询可以在

where子句中

还有from子句中，生成临时的派生表

举例：

--子查询生成的派生表成为主查询的查询对象
--找出每个学生超过他自己选修课程平均成绩的课程号
--临时生成一张含有平均成绩的表
select Sno,Cno
from sc,(select Sno,Avg(grade) from sc group by Sno)
		AS Avg_sc(avg_sno,avg_grade)
where  SC.Sno=Avg_sc.avg_sno  and   SC.Grade>=Avg_sc.avg_grade

--如果子查询中没有聚集函数，派生表可以不指定属性列，子查询select子句后面的列名为其默认属性

做题总结

1 日期函数

取当前日期：

SQL server ：getdate()

mysql : now()

sqlite : datetime(‘now’);

两个日期相差天数：

SQL SERVER : DATEDIFF(dd , Due_time , getdate())

mysql: DATEDIFF(NOW(), Due_time )

sqlite : round(julianday()- julianday(‘2018-02-08’),0 )

2 cast ,类型转换

比如grade项，成绩为空时，显示缺考，其他显示成绩，列具有同质性，即相同的数据类型和取值范围，

把int全部转换为字符型，cast（grade as char)

3 做题步骤：

• 查询的结果包含在几张表中，需要进行几次表的连接
• 共有多少个查询条件，把条件分块，找到这个条件对应的集合
• 至少，全部，每学全等等问题可使用谓词ALL ANY EXISTS