连接查询：同时涉及多个表查询，连接两个表的条件称为连接条件或连接谓词
连接查询主要类型
交叉连接查询（广义笛卡尔积）
格式
select student.*,sc.*
from student cross join sc;
select student.*,sc.*
from student ,sc;
内连接（等值连接）只连接两表中字段值满足比较条件的记录。
格式
select student.sno,sname,cno,grade
from student,sc
where student.sno=sc.sno;
自然连接，两表必须有同名列，且结果会自动去掉重复的列。
自然连接
select * from student NATURAL JOIN sc;
隐含条件：student.sno=sc.sno
等价于：
select student.*,cno,grade from student,sc
where student.sno=sc.sno;
select * from student join sc USING(sno);
--using指定连接的列
自身连接
SELECT FIRST.Cno,SECOND.Cpno
FROM Course AS FIRST，Course AS SECOND
where FIRST.Cpno = SECOND.Cno；
外连接：
左外连接:
SELECT Student.*,cno,grade
FROM Student LEFT JOIN SC on Student.Sno = SC.Sno;
SELECT Student.*,SC.*
FROM Student,SC where Student.Sno = SC.Sno（+）;
右外连接:
SELECT sname,sage,ssex,sdept,SC.*
FROM Student RIGHT JOIN SC on Student.Sno = SC.Sno;
SELECT Student.*,SC.*
FROM Student ,SC where Student.Sno(+) = SC.Sno;
完全外连接：
SELECT sname,sage,ssex,sdept，SC.*
FROM Student FULL JOIN SC on Student.Sno = SC.Sno；
复合条件连接：
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno
AND Cno= ' 2 '
AND Grade > 60;
SELECT Student.Sno,Sname，Grade
FROM Student,SC,COURSE
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno
AND CNMAE=‘数据库’;
子查询
SELECT Sname
FROM Student
WHERE Sno IN (SELECT Sno
FROM SC
WHERE Cno= ‘ 2 ’);
子查询中不能使用order by
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN (‘信息学院’)；
SELECT Sno，Sname
FROM Student
WHERE Sno IN (SELECT Sno --如果在in前面加NOT，就是查询不符合条件的
FROM SC
WHERE Cno IN (SELECT Cno
FROM Course
WHERE Cname= ‘数据库’) );
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN (SELECT Sdept
FROM Student
WHERE Sname= ‘ 张三’ );
SELECT Sno,Sname,Sdept,Sage
FROM Student
WHERE （Sdept,Sage) IN (SELECT sdept,Sage
FROM Student
WHERE Sname= ‘ 张三’ );
SELECT Sno,Sname
FROM Student
WHERE Sno IN (SELECT Sno
FROM SC
WHERE Cno =‘1’);
SELECT Sno，Sname
FROM STUDENT
WHERE （Sdept,Sage) IN (SELECT Sdept,Sage
FROM student
WHERE ssex=‘女’);
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN (SELECT Sdept
FROM Student
WHERE Sname= ‘ 张三’ );
any && all
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sage > ANY (SELECT SAGE
FROM Student
WHERE SDEPT= ‘ 信息学院’);
SELECT Sname，Sage
FROM Student
WHERE Sage >ANY (SELECT Sage
FROM Student
WHERE Sdept= ‘ 信息学院 ')
AND Sdept <> ‘ 信息学院;
SELECT Sname，Sage
FROM Student
WHERE Sage >ALL (SELECT Sage
FROM Student
WHERE Sdept= ‘ 信息学院 ')
AND Sdept <> ‘ 信息学院;
SELECT Sname
FROM Student
WHERE EXISTS （SELECT *
FROM SC
WHERE SC.SNO=Student.SNO
AND Cno= ' 1 '）;
SELECT Sname
FROM Student
WHERE EXISTS （SELECT *
FROM SC
WHERE SC.SNO=STUDENT.SNO
AND Cno= ' 1 '）;
EXISTS谓词查询
EXISTS谓词查询
SELECT Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno
AND SC.Cno= '1';
SELECT Sname
FROM Student
WHERE sno IN (SELECT sno
FROM SC
WHERE Cno= ' 1 ');
SELECT Sname
FROM Student
WHERE EXISTS (SELECT *
FROM SC
WHERE Sno=Student.Sno
AND Cno=‘1’);
SELECT Sname
FROM Student
WHERE NOT EXISTS (SELECT *
FROM SC
WHERE Sno=Student.Sno
AND Cno='1');
SELECT SNO,CNO
FROM SC X
WHRE GRADE>=(SELECT AVG(GRADE)
FROM SC Y
WHERE Y.SNO=X.SNO);
SELECT Sname
FROM Student
WHERE EXISTS (SELECT *
FROM SC
WHERE Sno=Student.Sno
AND Cno=‘1’);
SELECT Sname
FROM Student
WHERE NOT EXISTS （SELECT *
FROM Course
WHERE NOT EXISTS (SELECT *
FROM SC
WHERE Sno=Student.Sno
AND Cno=Course.Cno));
SELECT SNO,SNAME, (SELECT COUNT(*) FROM SC WHERE SNO=‘001’) 课程数
FROM STUDENT
WHERE SNO=‘001’;
SELECT *
FROM (SELECT SNO,AVG(GRADE)
FROM SC
GROUP BY SNO ) TMP;
SELECT t1.sno
FROM (SELECT *
FROM SC
where cno='1') t1 , (SELECT *
FROM SC
where cno='2' ) t2
WHERE t1.sno=t2.sno;
SELECT distinct student.sno,sname, sum(grade)
over(partition by sc.sno), avg(grade)
over(partition by sc.sno)
from student join sc on student.sno=sc.sno;
SELECT sno,cno,grade, rank() over (partition by sno order by grade DESC ) from sc;
SELECT sno,cno,grade, DENSE_RANK() over (partition by sno order by grade DESC )
from sc;
SELECT sno,cno,grade,ntile(2) over (partition by sno order by grade DESC )
from sc ORDER BY SNO;
SELECT sno,cno,grade, ROW_NUMBER() over (partition by sno order by grade DESC)
FROM SC ORDER BY SNO;
