SHU

select Emp_name,Dep_id
from Employees
where Emp_name='张三'

--对xsh和ryfg交叉连接
select xsh.编号 as 编号1,姓名,ryfg.编号 as 编号2,职务
from xsh cross join ryfg　

select *
from xsh cross join ryfg
/*【练习】使用交叉连接从从表Departments和表Employees中
同时获取数据，查询所有员工的姓名和所在部门名称。*/
select Emp_name,Dep_name
from Employees cross join Departments

select Employees.Emp_name,Departments.Dep_name
from Employees cross join Departments

select t1.Emp_name,t2.Dep_name
from Employees t1 cross join Departments t2

/*【例】由xsh和ryfg表得到有姓名并且有职务的
每个人的职务情况*/
select xsh.编号 as 编号1,姓名,ryfg.编号 as 编号2,职务
from xsh inner join ryfg on xsh.编号=ryfg.编号

select a.编号 as 编号1,姓名,b.编号 as 编号2,职务
from xsh as a,ryfg as b
where a.编号=b.编号

/*【练习】使用内连接从表Departments和表Employees中
同时获取数据，查询所有员工的姓名和所在部门名称。*/
select t1.Emp_name,t2.Dep_name
from Employees t1 inner join Departments t2
                  on t1.Dep_id=t2.Dep_id
                 
select t1.Emp_name,t2.Dep_name
from Employees t1,Departments t2
where t1.Dep_id=t2.Dep_id

/*【例】列出所有xsh表中学生姓名并对已有职务的同学
给出其职务*/
select xsh.编号 as 编号1,姓名,ryfg.编号 as 编号2,职务
from xsh left join ryfg on xsh.编号=ryfg.编号

/*[例]列出所有有职务的学生，给出其编号及姓名*/
select xsh.编号 as 编号1,姓名,ryfg.编号 as 编号2,职务
from xsh right join ryfg on xsh.编号=ryfg.编号

select xsh.编号 as 编号1,姓名,ryfg.编号 as 编号2,职务
from xsh full join ryfg on xsh.编号=ryfg.编号\

/*【例】在cj表中查询选修了两门或两门以上课程的
学生的学号和课程号*/
select distinct a.学号,a.课程号
from cj a join cj b on a.学号=b.学号 and a.课程号!=b.课程号
/*【例】在xs表中查询和“程明”在同一专业的所有男同学的信息*/
select b.*
from xs as a,xs as b
where a.姓名='程明' and b.专业=a.专业 and b.性别='男'
      and b.姓名<>'程明'
/*【例】在xsgl数据库中创建一个名称为“StuInfo”的存储过程，
要求完成以下功能：在xs表中查询20080301班学生的
学号、姓名、性别和出生时间四个字段的内容*/
CREATE PROCEDURE StuInfo
AS
BEGIN 
 SET NOCOUNT ON;   
 SELECT 学号,姓名,性别,出生时间
 from xs
 where LEFT(学号,8)='20080301'
END
GO

execute StuInfo

/*【例】在xsgl数据库中创建存储过程stu_cj，
返回学生学号、姓名、课程名、成绩。*/
create procedure stu_cj
as
select xs.学号,xs.姓名,kc.课程名,cj.成绩
from xs inner join cj on xs.学号=cj.学号
     inner join kc on cj.课程号=kc.课程号
    
create procedure stu_cj
as
select xs.学号,xs.姓名,kc.课程名,cj.成绩
from xs,cj,kc
where xs.学号=cj.学号 and cj.课程号=kc.课程号

execute stu_cj

--【例】根据输入的学号查询学生信息
create proc showstu
@sno char(10)
as
select *
from xs
where 学号=@sno

exec showstu '2008030101'
exec showstu @sno='2008030101'

/*创建存储过程IncreaseWage，
功能是将表Employees中所有员工的工资数据增加10%
为了进一步增强存储过程的可用性，
在此题原来的基础上添加参数信息。
由用户通过参数动态地设置工资增长的比例。
（设置输入参数@IncRate SMALLINT）
*/

/*【例】根据输入的姓名、性别查询学生信息，
如果没有输入性别则查询女生的学生信息*/
create proc showstu1
@sname char(8),@sex char(2)='女'
as
select *
from xs
where 姓名=@sname and 性别=@sex

exec showstu1 '王林','男'
exec showstu1 @sname='王林',@sex='男'
exec showstu1 @sex='男',@sname='王林'
exec showstu1 @sname='王林'
exec showstu1 '王林',DEFAULT
/*创建存储过程add_proc，
实现计算两个参数@num1和@num2(默认值为0)之和
并将其输出*/
create proc add_proc
@num1 int=0,@num2 int=0
as
declare @num3 int
set @num3=@num1+@num2
print @num3

exec add_proc 1,2
CREATE TRIGGER TR_xs_Insert
   ON xs
   AFTER INSERT
AS
BEGIN
 SET NOCOUNT ON;
    print '你插入了一条新记录！'
END
GO

insert into xs(学号,姓名)
values('2009030101','张三')
/*【例】建立一个触发器tr_cj_insert ，
当向cj表中添加数据时，
如果添加的数据与xs表中的数据不匹配（没有对应的学号），
则将此数据删除，同时输出“插入的记录不符合学生表中的记录！”
*/
create trigger tr_cj_insert
on cj
after insert
as
begin
  declare @sno char(10)
  select @sno=inserted.学号 from inserted
  if not exists(select 学号
                from xs
                where xs.学号=@sno)
     delete cj where 学号=@sno
  print '插入的记录不符合学生表中的记录！'
end

--课堂练习：
/*1、创建存储过程insert_kc，根据输入参数，
向kc表中的字段“课程号”、“课程名”插入记录*/
create proc insert_kc
@kno char(4),@kname char(16)
as
insert into kc(课程号,课程名)
values(@kno,@kname)

  --调用存储过程
execute insert_kc '1006','体育'

select * from kc

/*2、创建存储过程delete_kc，
根据输入参数“课程号”，从kc表中删除记录。*/
create proc delete_kc
@kno char(4)
as
delete from kc
where 课程号=@kno

execute delete_kc '1006'

/*【例】显示有一个输入参数和一个输出参数的
存储过程s_stu。*/
if exists(select name from sysobjects
          where name='s_stu' and type='P')
   drop procedure s_stu
go
create procedure s_stu
@sno char(10),@snm char(8) output
as
select @snm=姓名
from xs
where 学号=@sno

declare @snm char(8)
exec s_stu '2008030101',@snm output
select '2008030101',@snm

/*创建存储过程add_proc，
它的功能是计算两个参数@num1与@num2之和，
并将结果使用输出参数@num3返回*/
CREATE PROCEDURE add_proc1
@num1 INT = 0,
@num2 INT = 0,
@num3 INT OUTPUT
AS
SET @num3 = @num1 + @num2

DECLARE @num3 INT
EXEC add_proc 12,23,@num3 OUTPUT
PRINT @num3
/*【例】在xs表中查询和“程明”在同一专业
的所有男同学的信息*/
select b.*
from xs as a,xs as b
where a.姓名='程明' and b.专业=a.专业 and b.性别='男' and b.姓名<>'程明'

select *
from xs
where 专业=(
   select 专业
   from xs
   where 姓名='程明')and 性别='男'
   
/*查询cj表中课程号为“1001”的分数中
成绩比学号为“2008030103”成绩低的信息*/
select *
from cj
where 成绩<(
  select 成绩
  from cj
  where 学号='2008030103' and 课程号='1001')
 and 课程号='1001'

/*查询财务处所有员工的信息*/
select *
from Employees
where Dep_id=(
             select Dep_id
             from Departments
             where Dep_name='财务部')

/*查询学生表中“高数”分数在80分以上的
学生的“学号”、“姓名”和 “专业”。*/
select 学号,姓名,专业
from xs
where 学号 in (
               select cj.学号
               from cj inner join kc
                    on cj.课程号=kc.课程号
                    and kc.课程号='高数' and cj.成绩>80)
                  
select 学号,姓名,专业
from xs
where 学号 in (
               select cj.学号
               from cj
               where cj.成绩>80 and cj.课程号=(
                              select kc.课程号
                              from kc
                              where kc.课程名='高数'
                              )                             
              )

select xs.学号,姓名,专业,课程名,成绩
from xs,cj,kc
where xs.学号=cj.学号 and cj.课程号=kc.课程号
      and kc.课程号='高数' and cj.成绩>60
*【例】在xsgl数据库中创建一个名称为“StuInfo”的存储过程，
要求完成以下功能：在xs表中查询20080301班学生的
学号、姓名、性别和出生时间四个字段的内容*/
CREATE PROCEDURE StuInfo
AS
BEGIN 
 SET NOCOUNT ON;   
 SELECT 学号,姓名,性别,出生时间
 from xs
 where LEFT(学号,8)='20080301'
END
GO

execute StuInfo

/*【例】在xsgl数据库中创建存储过程stu_cj，
返回学生学号、姓名、课程名、成绩。*/
create procedure stu_cj
as
select xs.学号,xs.姓名,kc.课程名,cj.成绩
from xs inner join cj on xs.学号=cj.学号
     inner join kc on cj.课程号=kc.课程号
    
create procedure stu_cj
as
select xs.学号,xs.姓名,kc.课程名,cj.成绩
from xs,cj,kc
where xs.学号=cj.学号 and cj.课程号=kc.课程号

execute stu_cj

--【例】根据输入的学号查询学生信息
create proc showstu
@sno char(10)
as
select *
from xs
where 学号=@sno

exec showstu '2008030101'
exec showstu @sno='2008030101'

/*创建存储过程IncreaseWage，
功能是将表Employees中所有员工的工资数据增加10%
为了进一步增强存储过程的可用性，
在此题原来的基础上添加参数信息。
由用户通过参数动态地设置工资增长的比例。
（设置输入参数@IncRate SMALLINT）
*/

/*【例】根据输入的姓名、性别查询学生信息，
如果没有输入性别则查询女生的学生信息*/
create proc showstu1
@sname char(8),@sex char(2)='女'
as
select *
from xs
where 姓名=@sname and 性别=@sex

exec showstu1 '王林','男'
exec showstu1 @sname='王林',@sex='男'
exec showstu1 @sex='男',@sname='王林'
exec showstu1 @sname='王林'
exec showstu1 '王林',DEFAULT
/*创建存储过程add_proc，
实现计算两个参数@num1和@num2(默认值为0)之和
并将其输出*/
create proc add_proc
@num1 int=0,@num2 int=0
as
declare @num3 int
set @num3=@num1+@num2
print @num3

exec add_proc 1,2