数据库连接查询语句
- 格式:doc
- 大小:24.50 KB
- 文档页数:2
查询与“张军”工资相等的教师姓名和工资
select t1.tname,t1.sal
from teacher t1,teacher t2
where t1.sal=t2.sal and t2.tname='张军'
查询同时选修了和两门课的学生学号
select sc1.sno
from sc sc1,sc sc2
where o='19980201'and o='19990103'
and sc1.sno=sc2.sno
通过等值连接获取每个员工的所有详细信息
select emp.*,dept.*
from emp inner join dept
on emp.deptno=dept.deptno
通过不等值连接查找号员工可以去非本人出生地参加工作的地方select emp.empno,emp.ename,dept.loc
from emp inner join dept on emp.deptno <> dept.deptno
where empno=7788
order by empno
()自然连接student和sc表
select student.sno,sname,sex,cno,grade
from student,sc
where student.sno=sc.sno
左外连接
查询所有学生的选课情况
select student.sno,sname,cno,grade
from student left outer join sc
on student.sno=sc.sno
右外连接
()查询每个学生及选修课程的情况
select student.sno,sname,cno,grade
from student,sc
where student.sno=sc.sno
()查询学生选课情况
select student.sno,sname,cno,grade
from student right outer join sc on student.sno=sc.sno
全外连接
查询dept表中所有系所包含的教研室select dept.*,room.*
from dept full outer join room
on dept.dno=room.dno
交叉连接
交叉连接dept表和room表
select dept.*
from dept cross join room
order by dno