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