目录：
1.用T.SQL创建数据库表
2.为数据库表添加约束
3.简单查询
4.聚集函数及其应用
5.高级查询
6.视图操作
7.流程控制语句的应用
8.存储过程
9.触发器
1.用T.SQL创建数据库表
1.给出一个名为xxgl的数据库，并将其附加到当前实例中。
2.为xxgl数据库创建学生表、课程表和选课表。
3.分别为student表、course表和sc表输入数据。
1首先创建一个XXGL的数据库
create database XXGL
on
(
name=XXGL,
filename='F:\sql\XXGL.mdf',
size=6,
maxsize=20,
filegrowth=1
)
2创建一个student表。
use XXGL
go
create table student
(
sno char(12),
sname char(10),
ssex char(2),
sage tinyint,
snat char(8),
sdept nchar(20)
)
Go
3创建一个course表
use XXGL
go
create table course
(
cno char(3),
cname nchar(20),
credit tinyint
)
Go
4创建一个选课表 sc。
use XXGL
go
create table sc
(
sno char(12),
cno char(3),
grade tinyint
)
Go
1 course表的结果
2 student表的结果
3选课表sc的结果
2.为数据库表添加约束
1.使用表设计器为student表、course表和sc表添加主键约束。
2.使用“数据库关系图”为sc表添加外键约束。
1给student表添加主键约束
go
alter table student
alter column sno varchar(12) not null
go
alter table student
add constraint pk_sno primary key(sno)
Go
给course添加主键约束
alter table course
alter column cno varchar(3) not null
go
alter table course
add constraint pk_cno primary key(cno)
Go
2为选课表sc添加外键约束
alter table
alter column sno varchar(12)
alter table sc
add constraint fk_son foreign key(sno) references student(sno)
go
go
alter table sc
alter column cno varchar(3)
alter table sc
add constraint fk_cno foreign key(cno) references course(cno)
Go
1课程表course的主键
2 学生表student的主键
3选课表sc的外键
3.简单查询
1.学生表、课程表和选课表表结构如下：
student(sno char(10),sname char(10),ssex char(4),sage tinyint, spol char(8),snat char(12), sdept char(10))
course(cno char(6),cname char(20),credit tinyint)
sc(sno char(10),cno char(6),grade tinyint)
2.根据表结构完成简单查询。
1创建一个student表。
use XXGL
go
create table student
(
sno char10),
sname char(10),
ssex char(4),
sage tinyint,
snat char(12),
sdept char(10)
)
Go
创建一个course表
use XXGL
go
create table course
(
cno char(6),
cname nchar(20),
credit tinyint
)
Go
创建一个选课表 sc。
use XXGL
go
create table sc
(
sno char(10),
cno char(6),
grade tinyint
)
Go
2.根据表结构完成简单查询。
- 查询政治面貌为团员的学生姓名和年龄，并加入一个新表t1中
use XXGL
go
alter table student
add pc nchar(10)
go
select sname,sage
into t1
from student
where pc='团员'
Go
2.查询年龄在18.20岁（包括18岁和20岁）之间的学生的姓名和年龄，便加入新表t2中
select sname,sage
into t2
from student
where sage between 18 and 20
Go
3.查询通信系和计算机系学生的姓名和性别，并加入新表t3中
select sname,ssex
into t3
from student
where sdept in('通信系','计算机系')
Go
4.查询所有姓张的学生的姓名、学号和性别，并加入新表t4中
select sname,sno,ssex
into t4
from student
where sname like '张%'
Go
5.查询选修了课程的学生的学号并去除重复行，并加入新表t5中
go
select distinct sno
into t5
from sc
go
1 简单查询的结果
2 简单查询二的结果
3 简单查询三的结果
4 简单查询四的结果
5 简单查询五的结果
4.聚集函数及其应用
1.学生表、课程表和选课表表结构如下：
student(sno char(10),sname char(10),ssex char(4),sage tinyint, spol char(8),snat char(12), sdept char(10))
course(cno char(6),cname char(20),credit tinyint)
sc(sno char(10),cno char(6),grade tinyint)
2.根据表结构使用聚集函数完成相关查询。
（1）统计学生人数；查询开设的课程数。
（2）查询选修了课程的学生人数；查询003号课程的最高分，最低分和平均成绩。
（3）统计每门课程的选课人数，列出课程号和选课人数。
（4）统计每门课程的选课人数，列出选课人数大于5人的课程号和选课人数。
（5）分别统计男女生人数；分别统计男女生人数，且只显示人数超过3人的一组。
（6）按学号查询学生选修课程门数；查询选修了2门以上课程的学生的学号和课程门数。
（7）统计每个学生选修课程的平均分，列出学号和平均分。
（8）统计每个学生选修课程的平均分，列出平均分大于70分的学号和平均分。
（9）查询20岁以下学生的姓名，查询结果按学号降序排列。
（10）查询全体学生信息，查询结果按年龄升序排列，相同年龄按性别降序排列。
新建查询，在查询页面中输入查询语句。
语句如下：
（1）统计学生人数
go
select COUNT(*)
from student
go
查询开设的课程数。
go
select COUNT(*)
from course
go
（2）查询选修了课程的学生人数。
go
select count(distinct sno) as '人数'
from sc
go
查询003号课程的最高分，最低分和平均成绩。
go
select MAX(grade) as '最高分' ,MIN(grade) as '最低分',AVG(grade) as '平均分'
from sc
where cno='003'
go
（3）统计每门课程的选课人数，列出课程号和选课人数。
go
select cno,count(sno) as '人数'
from sc
group by cno
go
（4）统计每门课程的选课人数，列出选课人数大于5人的课程号和选课人数。
go
select cno,count(sno) as '人数'
from sc
group by cno
having COUNT(sno)>5
go
（5）分别统计男女生人数
go
select ssex as '性别' , count(*) as '人数'
from student
group by ssex
go
分别统计男女生人数，且只显示人数超过3人的一组。
go
select ssex as '性别' , count(*) as '人数'
from student
group by ssex
having COUNT(*)>3
go
（6）按学号查询学生选修课程门数。
go
select sno, COUNT (cno) as '课程人数'
from sc
group by sno
go
查询选修了2门以上课程的学生的学号和课程门数。
go
select sno, COUNT(cno) as '门数'
from sc
group by sno
having COUNT(cno)>2
go
（7）统计每个学生选修课程的平均分，列出学号和平均分。
go
select sno,AVG(grade) as '平均分'
from sc
group by sno
go
（8）统计每个学生选修课程的平均分，列出平均分大于70分的学号和平均分。
go
select sno,AVG(grade) as '平均分'
from sc
group by sno
having AVG(grade)>70
go
（9）查询20岁以下学生的姓名，查询结果按学号降序排列。
go
select sno,sname
from student
where sage<20
order by sno desc
go
（10）查询全体学生信息，查询结果按年龄升序排列，相同年龄按性别降序排列。
go
select *
from student
order by sage , ssex desc
go
结果
5高级查询
1.学生表、课程表和选课表表结构如下：
Student(sno char(10),sname char(10),ssex char(4),sage tinyint,snat char(8),spol char(8),sdept char(10))
Course(cno char(6),cname char(20),credit tinyint)
Sc(sno char(10),cno char(6),grade tinyint)
2.根据表结构完成以下查询。
（1）查询学生的学号、姓名、课程号和成绩；
（2）查询选修003号课程且成绩在60分以上的所有学生的学号和姓名。
（3）查询选修了课程名为“高数”的学生的学号和姓名。
（4）查询与“高数”学分相同的课程的课程名和学分，用自身连接查询完成。
（5）查询与孙瑶年龄相同的学生的信息，用自身连接查询完成。
（6）查询与“蒋路”在同一个系学习的学生的学号、姓名和系别。
（7）查询选课成绩大于60分以上的学生的学号和姓名。
（8）查询成绩至少比学号为09110101的学生选修的某一门课成绩要高的学生的学号、课程号和成绩。
（9）查询成绩比学号为09110101的学生选修的任一门课成绩都要高的学生的学号、课程号和成绩。
（10）找出超过所有学生选修课程平均成绩的学号、课程号和成绩。
（11）查询已修课程门数高于09110101号的所有学生的学号和已修门数。
（1）查询学生的学号、姓名、课程号和成绩
use XXGL
go
select student.sno,student.sname,sc.cno,sc.grade
from student join sc
on sc.sno=sc.sno
Go
(2)查询选修003号课程且成绩在60分以上的所有学生的学号和姓名
go
select student.sno,student.sname
from student join sc
on student.sno=sc.sno and grade>60
where cno='003'
Go
(3)查询选修了课程名为“高数”的学生的学号和姓名
go
select student.sno,student.sname
from student inner join course
on student.sno=sc.sno join course
on sc.cno=course.cno
where cname='高数'
（4）查询与“高数”学分相同的课程的课程名和学分，用自身连接查询完成
go
select a.cname,a.credit
from course a inner join course b
on a.credit=b.credit and b.cname='高数'
Go
（5）查询与孙瑶年龄相同的学生的信息，用自身连接查询完成
go
select a.*
from student a inner join student b
on a.sage=b.sage and b.sname='孙瑶'
Go
（6）查询与“蒋路”在同一个系学习的学生的学号、姓名和系别
go
select a.sno,a.sname,a.sdept
from student a inner join student b
on a.sdept=b.sdept and b.sname='蒋路'
go
select sno,sname,sdept
from student
where sdept in
(select sdept
from student
where sname='蒋路'
)
（7）查询选课成绩大于60分以上的学生的学号和姓名
go
select student.sno,student.sname
from student inner join sc
on student.sno=sc.sno and grade>60
Go
（8）查询成绩至少比学号为09110101的学生选修的某一门课成绩要高的学生的学号、课程号和成绩
go
select sno,cno,grade
from sc
where grade>any(select grade
from sc
where sno='09110101'
)
Go
（9）查询成绩比学号为09110101的学生选修的任一门课成绩都要高的学生的学号、课程号和成绩
go
select sno,cno,grade
from sc
where grade>all(select grade
from sc
where sno='09110101'
)
Go
（10）找出超过所有学生选修课程平均成绩的学号、课程号和成绩
go
select sno,cno,grade
from sc
where grade>all(select AVG(grade)
from sc
)
Go
（11）查询已修课程门数高于09110101号的所有学生的学号和已修门数
go
select sno,COUNT(cno)as'门数'
from sc
group by sno
having COUNT(cno)>=
(select COUNT(cno)
from sc
where sno='09110101')
go
（12）找出每个学生超过自己选修课程平均成绩的课程号。(相关子查询)
go
select cno
from sc as a
where grade>=
(select AVG(grade)
from sc as b
where a.sno=b.sno)
go
（13）查询学生的学号、姓名、课程名和成绩
go
select student.sno,student.sname,course.cname,sc.grade
from student join sc
on student.sno=sc.sno
join course
on course.cno=sc.cno
6.视图操作
1.学生表、课程表和选课表表结构如下：
Student(sno char(10),sname char(10),ssex char(4),sage tinyint,snat char(8),spol char(8),sdept char(10))
Course(cno char(6),cname char(20),credit tinyint)
Sc(sno char(10),cno char(6),grade tinyint)
2.根据表结构，完成下面的操作。
（1）建立计算机系学生的视图jsj_student，列出学号、姓名和年龄。
（2）建立选修了003号课程的学生的视图s_sc_003，列出学号、姓名、年龄和系别。
（3）定义一个查询学生学号、姓名、出生年份的视图s_birthday。
（4）按政治面貌统计学生人数定义为一个视图s_p。
（5）在s_p视图中找出政治面貌是团员的学生人数。
（6）将计算机系学生视图jsj_student中学号为09110106的学生年龄改为19。
（7）创建查询每个学生的选课门数，要求列出学生的学号和选课门数的视图s_m。
（8）删除jsj_student视图。
（9）将学生09110101的政治面貌改为群众。
（10）将所有学生的年龄增加1岁。
（11）删除学号为09110104的学生的记录。
（12）删除所有学生的选课记录。
（13）将计算机系学生的“高数”成绩加10分。
（1）建立计算机系学生的视图jsj_student，列出学号、姓名和年龄
use xxgl
create view jsj_student
as
select sno,sname,sage
from s
where sdept='计算机系'
Go
查询视图
go
select *
from jsj_student
go
（2）建立选修了003号课程的学生的视图s_sc_003，列出学号、姓名、年龄和系别
go
create view s_sc_003
as
select s.sno,sname,sage,sdept
from s inner join sc
on s.sno=sc.sno
where cno='003'
Go
查询视图
go
select *
from s_sc_003
go
（3）定义一个查询学生学号、姓名、出生年份的视图s_birthday
go
create view s_birthday
as
select s.sno,sname,year(GETDATE())-sage as '出生年月'
from s
Go
查询视图
go
select *
from s_birthday
Go
（4）按政治面貌统计学生人数定义为一个视图s_p
go
create view s_p
as
select pc,count(sno) as '人数'
from s
group by pc
Go
查询视图
go
select *
from s_p
Go
（5）在s_p视图中找出政治面貌是团员的学生人数
go
select pc,人数
from s_p
where pc='团员'
Go
（6）将计算机系学生视图jsj_student中学号为09110106的学生年龄改为19
go
update jsj_student
set sage='19'
where sno='09110106'
go
（7）创建查询每个学生的选课门数，要求列出学生的学号和选课门数的视图s_m
go
create view s_m(sno,c_num)
as
select sno,COUNT(cno)
from sc
group by sno
Go
查询视图
go
select *
from s_m
Go
（8）删除jsj_student视图
go
drop view jsj_student
go
（9）将学生09110101的政治面貌改为群众
go
update s
set pc='群众'
where sno='09110101'
go
（10）将所有学生的年龄增加1岁
go
update s
set sage=sage+1
go
（11）删除学号为09110104的学生的记录
go
delete s
where sno='09110104'
go
（12）删除所有学生的选课记录
go
delete
from sc
go
（13）将计算机系学生的“高数”成绩加10分
go
update sc
set grade=grade+10
where '计算机系'=
(select sdept
from s
where s.sno=sc.sno
) and cno='002'
Go
7.流程控制语句的应用
1.学生表、课程表和选课表表结构如下：
Student(sno char(10),sname char(10),ssex char(4),sage tinyint,snat char(8),spol char(8),sdept char(10))
Course(cno char(6),cname char(20),credit tinyint)
Sc(sno char(10),cno char(6),grade tinyint)
2.根据表结构完成以下编程。
（1）创建整型局部变量@myint1和@myint2，然后分别为其赋值10和20，并在计算机屏幕上显示出来。
（2）定义两个局部变量，分别存放常量16和student表中蒋路的学号，并在计算机屏幕上显示出来。
（3）统计并显示“C语言程序设计”课程的平均成绩，如果在80分以上，则显示“成绩优秀”，否则显示“成绩一般”。
（4）统计并显示“C语言程序设计”课程的平均成绩，如果在80分以上，则显示“成绩优秀”，如果在60—79之间，则显示“成绩一般”否则显示“成绩不理想”。
（5）将“C语言程序设计”课程的成绩，按A、B、C、D、E五个等级显示成绩。
A级：90分以上；B级：80.89分；C级：70.79分
D级：60.69分；E级：60分以下
（6）求1～100之间的所有数之和，并输出结果。
（7）为确保选修002号课程的学生都能通过考试，制定了如下加分规则：
每人都加5分，看是否都通过，如果没有，则每人再加5分，再看是否都能通过，如此反复加分，直到所有人都能通过为止。
语句如下：
（1）创建整型局部变量@myint1和@myint2，然后分别为其赋值10和20，并在计算机屏幕上显示出来
use xxgl
go
declare @myint1 int,@myint2 int
set @myint1=10
set @myint2=20
print @myint1
print @myint2
Go
（2）定义两个局部变量，分别存放常量16和student表中蒋路的学号，并在计算机屏幕上显示出来
go
declare @num int,@sid char(9)
set @num='16'
select @sid=sno
from s
where sname='蒋璐'
print @num
print @sid
go
（3）统计并显示“C语言程序设计”课程的平均成绩，如果在80分以上，则显示“成绩优秀”，否则显示“成绩一般”
go
select avg(grade)
from sc
where cno='001'
go
if(select avg(grade) from sc where cno='001')>80
print 'c语言程序设计成绩优秀'
else
print 'c语言程序设计成绩一般'
go
（4）统计并显示“C语言程序设计”课程的平均成绩，如果在80分以上，则显示“成绩优秀”，如果在60—79之间，则显示“成绩一般”否则显示“成绩不理想”
go
select AVG(grade) as '平均成绩',
case
when AVG(grade)>=80 then '成绩优秀'
when AVG(grade)between 60 and 79 then '成绩一般'
when AVG(grade)<60 then '成绩不理想'
end as '成绩等级'
from sc
where cno='001'
go
（5）将“C语言程序设计”课程的成绩，按A、B、C、D、E五个等级显示成绩。
A级：90分以上；B级：80.89分；C级：70.79分
D级：60.69分；E级：60分以下
go
select sname as '姓名',grade,
case
when grade>=90 then 'A级'
when grade between 80 and 89 then 'B级'
when grade between 70 and 79 then 'C级'
when grade between 60 and 69 then 'D级'
else 'E级'
end as '成绩等级'
from s join sc on s.sno=sc.sno and cno='001'
go
（6）求1～100之间的所有数之和，并输出结果
go
declare @s int,@i int
set @s=0
set @i=1
while (@i<=100)
begin
set @s=@s+@i
set @i=@i+1
end
print @s
go
（7）为确保选修002号课程的学生都能通过考试，制定了如下加分规则：
每人都加5分，看是否都通过，如果没有，则每人再加5分，再看是否都能通过，如此反复加分，直到所有人都能通过为止
go
select * from sc where cno='002'
declare @n int
while(1=1)
begin
select @n=COUNT(*) from sc where grade<60 and cno='002'
if(@n>0)
update sc set grade=grade+5 where cno='002'
else break
end
update sc set grade=100 where grade>100 and cno='002'
select '加分后课程号为的成绩如下：'
select * from sc where cno='002'
go
8.存储过程：
1、将选修001号课程的学生的不及格率控制在20%范围内，制定了如下加分规则：
每人都加5分，看是否满足不及格率在20%以下，如果没有，则每人再加5分，再看是否满足，如此反复加分，直到满足条件为止。
Use xxgl
declare @n int,@num int
select @num=COUNT(sno) from sc where cno='001'
select'加分前的成绩如下：'
select * from sc where cno='001'
set @num=@num*0.2
while(1=1)
begin
select @n=count(sno) from sc where cno='001' and grade<60
if(@n>@num)
update sc set grade=grade+5 where cno='001'
else break
end
update sc set grade=100 where grade>100 and cno='001'
select'加分后的成绩如下：'
select * from sc where cno='001'
go
create proc grade_info
as
select s.sno,sname,cname,grade
from s join sc on s.sno=sc.sno
join c on sc.cno=c.cno
go
2、在JXGL数据库中创建一个存储过程grade_info，该存储过程可以查看所有选课学生的成绩信息（sno,sname,cname,grade)。
go
create proc grade_info
as
select s.sno,sname,cname,grade
from s join sc on s.sno=sc.sno
join c on sc.cno=c.cno
go
3、利用教学管理数据库的三个基本表，创建一个存储过程ps_grade，输出指定学生的姓名及课程名称、成绩信息。
go
create proc ps_grade
@s_name char(8)
as
select sname ,cname,grade
from s join sc on s.sno=sc.sno and sname like @s_name
join c on sc.cno=c.cno
go
4、利用教学管理数据库的三个基本表，创建一个存储过程pv_grade ，输入一个学生姓名，输出该学生所有选修课程的平均成绩。
go
create proc pv_grade
@s_name char(8)=null,@s_avg real output
as
select @s_avg=avg(grade)
from s join sc on s.sno=sc.sno and sname like @s_name
go
5、创建存储过程insert_sc，通过该存储过程可以加入一条记录到表SC，并查询此表中该学生的总成绩
go
create proc insert_sc
@para_sno char(5),@para_cno char(4),@para_grade int
as
insert sc(sno,cno,grade)
values(@para_sno,@para_cno,@para_grade)
select sum(grade)
from sc
where sno=@para_sno
go
6、分别调用grade_info、ps_grade、pv_grade、 insert_sc四个存储过程，查看所有学生的成绩信息、“王”姓学生的成绩和“王”姓学生的平均成绩，将一条记录插入到SC表中，并查询此表中该学生的总成绩
go
exec grade_info
go
exec ps_grade '王%'
go
declare @pij real
exec pv_grade '王%',@pij output
print @pij
go
exec insert_sc 's7','c3','78'
go
9.触发器：
1、为xxgl数据库中的c表创建一个名为tri_d_s的触发器，当删除c表中的记录时，激活该触发器，拒绝删除操作
use xxgl
go
create trigger tri_d_s
on c
instead of
delete
as
print'拒绝删除操作'
go
delete from c where cname='高数'
go
2、为xxgl数据库中的sc表创建一个名为tri_d_sc的触发器，当删除sc表中的记录时，激活该触发器，不执行删除操作，执行触发器中的操作。
create trigger tri_d_sc
on sc
instead of
delete
as
print'不执行删除操作，执行触发器中的操作'
go
delete from sc where cno='001'
go
3、为s创建一个名为test_tr的触发器，当执行添加、修改和删除时，激活该触发器,显示两个临时表中的记录。
create trigger test_tr
on s
for insert,update,delete
as
select *
from inserted
select *
from deleted
go
delete from s where sno='09110103'
go
insert into s values('09110109','刘红','女','23','汉族','ms')
go
update s set sdept='自动化' where sno='09110104'
go
4、在xxgl数据库中创建一个触发器，当在s表中删除一个学生的信息，同时将该学生的成绩删除。
create trigger database1
on s
after delete
as
delete from sc where sno=(select sno from deleted)
go
delete from s where sno='09110103'
go
5、在xxgl数据库中，创建一个insert触发器tr_c_insert，当在C表中插入一条新记录时，触发该触发器，并给出“你插入了一门新的课程！”的提示信息。
create trigger tr_c_insert
on c
instead of
insert
as
print'你插入了一门新的课程！'
go
insert into c values('006','数值计算方法','3')
go
6、创建xxgl数据库作用域的DDL触发器，当删除一个表时，提示禁止该操作，然后回滚删除表的操作。
create trigger shanchu
on database
after drop_table
as
print'禁止删除'
rollback transaction
go
drop table s
go
7、创建触发器，功能是在s表中修改某个学生的学号，同时将该学号更新到成绩表sc中。
create trigger gengxin
on s
after update
as
declare @oldson char(12),@newson char(13)
select @oldson=sno from deleted
select @newson=sno from inserted
update sc set sno=@newson
where sno=@oldson
go
update s set sno='09110109' where sname='赵平'
go
create trigger after
on database
after