use studentManager2
select *from exam
-------连接查询
select
stu.stuName,les.lesName,exam.labExam,exam.writtenExam
from exam
inner join student stu
on(stu.stuId=exam.stuId)
inner join lesson les
on(les.lesId=exam.lesId)
--内连接 显示的是匹配的数据
--从语法上讲,连接的两张表可以没有主外键关系,但项目中一般都是主外键关系
select * from table1 t1 inner join table2 t2 on(t1.pk=t2.fk)
select
stu.stuName, exam.*
from exam
inner join student stu
on(stu.stuId = exam.stuId)
select stu.stuName, les.lesName,exam.labExam, exam.writtenExam
from student stu,exam,lesson les
where stu.stuId=exam.stuId and les.lesId = exam.lesId
select * from exam
--外连接
--左外连接 右外连接 完全外连接
select * from table1 t1 left join table2 t2 on(t1.pk=t2.fk)
--左外连接 左表的所有数据,右表匹配的数据
--1 显示内连接所有数据
--2 看左表是否还有没有匹配的,有的话原样列出,右表部分用null补齐
select *
from student stu
right join exam
on(stu.stuId = exam.stuId)
select *
from student stu
full join exam
on(stu.stuId = exam.stuId)
--交叉连接
select * from student, exam
use pas
select * from teacher
select * from classInfo
select * from student
select * from restPwd
select * from lesson
select * from coursearrangement
select * from examschedule
select * from score
use pas
select cls.clsName, ca.year,ca.semester, les.lesName,tea.teaName
from courseArrangement as ca
inner join lesson les on(les.lesId = ca.lesId)
inner join teacher tea on(tea.teaNum = ca.teaNum)
inner join classInfo cls on(cls.clsId = ca.clsId)
where tea.teaName like '李%'
--
select cls.clsName, ca.year, ca.semester, les.lesName
from coursearrangement ca
inner join lesson les on(les.lesId = ca.lesId)
inner join classInfo cls on(cls.clsId = ca.clsId)
where ca.teaNum =
(select teaNum from teacher where teaName='李兴辉')
--交叉连接
use studentManager2
--zhangsan
--子查询:一个查询的结果作为另一个查询的条件
select * from exam
where
stuId=(select stuId from student where stuName='张三')
--子查询只能返回一列
-- 条件 = 只能返回一行
select * from student
where stuId not in(
select stuId from exam
where lesId=(
select lesId from lesson where lesName='c'
)
)
select * from student where stuName not in('aa', 'bb', '张三', 'mm')
