常用的sql语句的总结（连接查询）

select * from TblClass
select * from TblStudent

--查询：显示学生姓名及对应的班级名称
select tClassName,tSName
from TblClass
inner join tblstudent on TblClass.tClassId=TblStudent.tSClassId
--查询：学生的姓名及英语数学分数
select tSName,tEnglish,tMath
from TblStudent
inner join tblscore on TblStudent.tSId=TblScore.tSId

———————————————内连接————————————————
create table groups(
	gid int identity(1,1) primary key not null,
	gname nvarchar(10),
	gparent int
)

select * from groups

insert into groups values('人事部门',3)
--当前表的含义：第一级分类：总部，第二级分类：分公司，第三级分类：部门
--查询部门名称
select gname from groups as department
--查询公司名称
select gname from groups as company
--查询部门名称及对应的公司名称
--需要从部门表和公司表连接查询
select department.gname,company.gname
from groups as department
inner join groups as company
on department.gparent=company.gid
———————————————外连接————————————————
select * from Department
select * from Employee

insert into Employee(eName,eCode,edid) values('yzk',12,100)
update Employee set edid=5 where edid=1

select * from Department left join Employee on department.DId=employee.edid

select * from Department right join Employee on department.DId=employee.edid

select * from Department
full join
Employee on department.DId=Employee.edid
--查询所有学生（参加及未参加考试）的学生姓名、年龄、成绩，如果没有参加考试显示“缺考”，如果小于60分显示不及格
use ItCastCn

select TblStudent.tSName,
isnull(cast(TblScore.tEnglish as varchar(10)),'缺考'),
isnull(cast(TblScore.tMath as varchar(10)),'缺考')
from tblstudent
left join TblScore on TblStudent.tSId=TblScore.tSId

select * from TblStudent
insert into TblStudent(tSName,tSGender,tSClassId) values('yzk','男',3)