学习记录之--SQL Server 数据库学习笔记（二）

--------链接查询---视图的使用---------------------------
--查询tbEmployee表的全部和tbDepart里的Dname
select tbEmployee.*,tbDepart.Dname from tbEmployee,tbDepart 
where tbEmployee.Did = tbDepart.Did
--链接（inner join）简写（join）
select tbEmployee.*,tbDepart.Dname,tbWork.Wname from tbEmployee join tbDepart
on tbEmployee.Did = tbDepart.Did
join tbWork on tbEmployee.wid = tbwork.wid

--创建视图，也就是虚表（他不是真正的表，是SQL指令的结果集合）方便查询
create view VEmpInfo
as
select tbEmployee.*,tbDepart.Dname,tbWork.Wname from tbEmployee join tbDepart
on tbEmployee.Did = tbDepart.Did
join tbWork on tbEmployee.wid = tbwork.wid
--查询视图
select * from VEmpInfo order by Eid

--增加-----------------------增、删、查、改-------------------------
insert into tbEmployee(Eid,Ename,Gender,Age,Did,Wid,Tel,Address)
values('E09','阿虎','男',34,'D01','W05','123423','中国')
--删除
delete from tbEmployee where Eid = 'E004'
--查询
select * from tbEmployee where Eid = 'E005'
--修改
update tbEmployee set Tel = '77778888000',Address = '上海'
where Eid = 'E003'
---------------------------------------------------------------
------------------------------存储过程--------------------------
---------------------------------------------------------------
create procedure upGetEmpInfo
as
begin
	select tbEmployee.*,tbDepart.Dname,tbWork.Wname from tbEmployee  inner join tbDepart
on tbEmployee.Did = tbDepart.Did
join tbWork on tbEmployee.Wid = tbWork.Wid
end

--执行
exec upGetEmpInfo

--带参数的存储过程
create procedure upDeleteEmp
@Eid  varchar(50) = ''
as
begin
	delete from tbEmployee where Eid = @Eid
end

--执行
exec upDeleteEmp  'E003'
--------已知Wname和Dname查Wid和Did
select Did,Wid from tbDepart,tbWork
where Dname = '测试部' and Wname = '测试员'