本文代码均已在 S Q L S e r v e r 2012 测试通过，欢迎指正任何错误 本文代码均已在 SQL Server2012 测试通过，欢迎指正任何错误 本文代码均已在SQLServer2012测试通过，欢迎指正任何错误?
目录
前言
大二上刚开学的时候转专业后，发现信工大一下学的这门课 (数据库技术与应用,2学分) 不能抵消计科大二下要学的课 (数据库原理,3学分)?
课本第四章P85-86的33道题
--利用第3章定义的 student、 course和sc表结构实现如下操作
--1.查询学生选课表中的全部数据
select * from sc
--2.查询计算机系的学生的姓名、年龄。
select sname 姓名,sage 年龄
from student
where sdept = '计算机系'
--3.査询成绩在70~80分之间的学生的学号、课程号和成绩。
select student.sno 学号, course.cno 课程号, sc.grade 成绩
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno
and grade between 70 and 80
--4.査询计算机系年龄在18~20之间且性别为“男”的学生的姓名、年龄。
select sname 姓名,sage 年龄
from student
where sage between 18 and 20 and ssex='男' and sdept='计算机系'
--5.查询课程号为“c001”的课程的最高的分数
select max(grade) as最高分
from sc
where cno='c001'
--6.查询计算机系学生的最大年龄和最小年龄。
select max(sage)as最大年龄,min(sage)as最小年龄
from student
where sdept='计算机系'
--7.统计每个系的学生人数。
select count(sdept) 学生人数, sdept 对应系
from student
where sdept is not null
group by sdept
--8.统计每门课程的选课人数和考试最高分
select cno 课程号,count(*)选课人数,max(grade)考试最高分
from sc
group by cno
--9.统计每个学生的选课门数和考试总成绩,并按选课门数升序显示结果
select sno 学号,sum(grade)as 考试总成绩, count(cno) 选课门数
from sc
group by sno
order by count(cno)
--10.查询总成绩超过200分的学生,要求列出学号、总成绩。
select sno 学号,sum(grade) 总成绩
from sc
group by sno
having sum(grade)>200
--11.查询选修了“c02”号课程的学生的姓名和所在系。
select sname 姓名, sdept 所在系
from student s1,sc s2
where s1.sno=s2.sno and s2.cno='c02'
--12.査询成绩80分以上的学生的姓名、课程号和成绩,并按成绩降序排列结果。
select s1.sname, s2.cno, s2.grade
from student s1, sc s2
where s1.sno=s2.sno and grade >80
order by grade desc --降序
--13.查询哪些学生没有选课,要求列出学号、姓名和所在系
--左(外)连接，左表的记录将会全部表示出来，
--而右表只会显示符合搜索条件的记录。右表记录不足的地方均为null
--select student.sno,sname,sdept 这样写不行
select a.sno,sname,sdept
from student a left join sc b
on a.sno=b.sno where b.sno is null
--14.查询与java在同一学期开设的课程的课程名和开课学期
select c2.cname, c1.semester
from course c1 join course c2 on c1.semester=c2. semester
where c1.cname='java'and c2.cname!= 'java'
--15.查询与李勇年龄相同的学生的姓名、所在系和年龄。
select s2.sname, s2.sdept, s2.sage
from student s1 join student s2 on s1.sage=s2. sage
where s1.sname='李勇'and s2.sname='李勇'
--16.用子査询实现如下査询:
--(1)查询选修了“c001”号课程的学生的姓名和所在系。
select sname, sdept from student
where sno in(select sno from sc where cno= 'c001')
--(2)查询数学系成绩80分以上的学生的学号、姓名、课程号和成绩。
select student.sno, sname, cno, grade
from student join sc on student.sno=sc. sno
where sdept='数学系'
and student.sno in (select sno from sc where grade>80)
--(3)查询计算机系考试成绩最高的学生的姓名。
select sname
from student s join sc on s.sno=sc.sno
where sdept='计算机系'and
grade=(select max(grade)from sc join student s on s.sno=sc. sno where sdept='计算机系')
--(4)査询数据结构考试成绩最高的学生的姓名、所在系、性别和成绩
select sname, sdept, ssex, grade
from student s join sc on s.sno=sc.sno join course on sc.cno=course.cno
where cname='数据结构' and grade=( select max( grade) from sc
join course on sc.cno= course.cno where cname='数据结构')
--17.查询没有选修java课程的学生的姓名和所在系
select sname,sdept
from student s join sc on s.sno=sc.sno join course c on c.cno=sc.cno
where cname!='java'
--18.查询计算机系没有选课的学生的姓名和性别
--左(外)连接，左表的记录将会全部表示出来，
--而右表只会显示符合搜索条件的记录,右表记录不足的地方均为null
select sname,ssex
from student s left join sc on s.sno=sc.sno
where sdept='计算机系' and sc.cno is null
--19.创建一个新表,表名为 test_t,其结构为(col1,col2,col3),其中:
--col1:整型,允许空值
--col2:字符型,长度为10,不允许空值
--col3:字符型,长度为10,允许空值
--试写出按行插入如下数据的语句(空白处表示空值)
create table test_t(
col1 int,
col2 char(10)not null,
col3 char(10)
)
insert into test_t(col2)values('b1')
insert into test_t(col1, col2, col3)values(1,'b2','c2')
insert into test_t(col1, col2)values(2, 'b3')
--20.删除考试成绩低于50分的学生的选课记录
delete from sc where grade<50
--21.删除没有人选的课程记录
delete from course where cno not in (select cno from sc)
--22.删除计算机系java成绩不及格学生的java选课记录
delete from sc
where sno in( select sno from student where sdept='计算机系')
and cno=( select cno from course where cname='java')
and grade<60
--23.将第2学期开设的所有课程的学分增加2分。
update course set credit = credit+2
where semester =2
--24.将java课程的学分改为3分。
update course set credit =3
where cname='java'
--25.将计算机系学生的年龄增加1岁。
update student set sage= sage+1
where sdept='计算机系'
--26.将信息系学生的“计算机文化学”课程的考试成绩加5分
update sc set grade= grade +5
where sno in (select sno from student where sdept='信息系')
and cno =(select cno from course where cname='计算机文化学')
--27.查询每个系年龄大于等于20的学生人数,并将结果保存到一个新永久表 dept_age中。
select sdept, count(sno)人数 into dept_age
from student
where sno in(select sno from student where sage<20)
group by sdept
--28.查询计算机系每个学生的java考试情况,列出学号、姓名、成绩和成绩情况,其中成绩情况的显
--示规则为:如果成绩大于等于90,则成绩情况为“好”;如果成绩在80~89,则成绩情
--况为“较好”;如果成绩在70~79,则成绩情况为“一般”;如果成绩在60~69,则成
--绩情况为“较差”;如果成绩小于60,则成绩情况为“差”。
select s.sno, s.sname, grade,
case when grade >=90 then '好'
when grade between 80 and 89 then '较好'
when grade between 70 and 79 then '一般'
when grade between 60 and 69 then '较差'
when grade <60 then'差' end as 考试情况
from student s join sc on s.sno=sc.sno join
course c on c.cno=sc.cno where cname='java'
--29.统计每个学生的选课门数(包括没有选课的学生),列出学号、选课门数和选课情况,其中选课情
--况显示规则为:如果选课门数大于等于6门,则选课情况为“多”;如果选课门数超过在3~5门
--则选课情况为“一般”;如果选课门数在1~2门,则选课情况为“偏少”;如果没有选课,则选课
--情况为“未选课”。
select s.sno, count(cno)选课门数,
case when count(cno)>=6 then '多'
when count(cno) between 3 and 5 then'一般'
when count(cno) between 1 and 2 then '偏少'
when count(cno)=0 then '未选课'
end as 选课情况
from student s left join sc on s.sno=sc.sno
group by s.sno
--30.修改全部课程的学分,修改规则如下:如果是第1~2学期开设的课程,则学分増加5分;如果是
--第3~4学期开设的课程,则学分增加3分;如果是第5~6学期开设的课程,则学分增加1分;
--对其他学期开设的课程,学分不变。
update sc set grade= grade+case
when semester between 1 and 2 then 5
when semester between 3 and 4 then 3
when semester between 5 and 6 then 1
else 0 end from sc join course c on
sc.cno=c.cno
--31.査询“李勇”和“王大力”所选的全部课程,列出课程名、开课学期和学分,不包括重复的结果
select cname,semester,credit
from course
where cno in
(
select cno from sc where sno in
(
select sno from student where sname='李勇' or sname='王大力'
)
)
--32.查询在第3学期开设的课程中,“李勇”选了但“王大力”没选的课程,列出课程名和学分
select cname,credit
from course
where semester= 3
and cno in
(select cno from sc where sno =(select sno from student where sname='李勇'))
and cno not in
(select cno from sc where sno=(select sno from student where sname='王大力'))
--33.查询在学分大于3分的课程中,“李勇”和“王大力”所选的相同课程,列出课程名和学分
select cname,credit
from course
where credit>3
and cno in
(select cno from sc where sno =(select sno from student where sname='李勇'))
and cno in
(select cno from sc where sno =(select sno from student where sname='王大力'))
d a y 1 day1 day1 熟悉SQL Server 的SSMS 集成环境
一、实验目的
1.熟悉SQL Server Management Studio 集成环境的使用
2.掌握界面方式，创建数据库和数据表
3.掌握界面方式建立数据完整性约束
二、实验内容
1.启动MicroSoft SQL Server
2.用界面方式，按下面的要求创建“练习”数据库
在SSMS 的“对象资源管理器”中，右击数据库，在“新建数据库”对话框中，输入数据库名称练习，设置数据库文件初始大小为5M，限制文件大小为100M，自动增长时的递增量为2M。日志文件初始大小设为2M。限制文件大小为10M，自动增长大小为1M。更改文件存储路径（请将练习数据库保存到自己的文件夹下），由此完成了名为“练习”数据库的创建
3.用界面方式创建如下要求的三个数据表
在“练习”数据库下，分别创建stu1、course1、sc1 三个数据表，操作步骤可以参考教材175 页
（1）按下面要求建立表stu1：
列名 数据类型 允许空 主键 说明
1 sno Char(8) 否 是 学号
2 sname Varchar(20) 是 否 姓名
3 ssex Char(2) 是 否 性别
4 sage tinyint 是 否 年龄
5 sdept Varchar(20) 是 否 所在系
（2）按下面要求建立表course1：
列名 数据类型 允许空 主键 说明
1 cno Char(6) 否 是 课程号
2 cname Varchar(20) 是 否 课程名
3 credit tinyint 是 否 学分
（3）按下面要求建立表sc1：（注：sno 和cno 共同组成主键）
列名 数据类型 允许空 主键 说明
1 sno Char(8) 否 是 学号
2 cno Char(6) 否 是 课程号
3 grade tinyint 是 否 成绩
4.定义好上述三个表后，继续在sc1 表中定义外键约束（参考教材179 页）
（1）sc1 表的外键sno 与主键表stu1 的sno 建立关系；
（2）sc1 表的外键cno 与主键表course1 的cno 建立关系；
5.界面方式，在stu1 表中添加身份证号数据列，列名stuID，字符型char（18），不允许空值
6.界面方式，为stu1 表中的stuID 列定义unique 约束。（参考教材181 页）
7.界面方式，为stu1 表中的性别列定义default 约束，默认值为男，定义Check 约束，性别取值为男或女
8.为sc1 表的grade 列定义check 约束：0≤grade≤100
总结:
难度不大,就是照着步骤来,手动建三个表,搞搞约束,创新程度不高,入门
d a y 2 day2 day2 用SQL 数据定义功能创建数据库、数据表
一、实验目的：
掌握使用SQL 的数据定义功能创建数据库和数据表，以及数据完整性约束
二、实验内容：
1.启动MicroSoft SQL Server→SQL Server Management Studio（即SSMS），在“连接到服务器”对话框中，选择“Windows 身份验证”，点击“连接”，进入SSMS 操作界面。
2.在SSMS 界面中，单击工具栏上的“新建查询”按钮，打开SQL 查询编辑器窗口。在窗口中输入：create database 练习2
3.请使用“use 练习2”命令，将“练习2”数据库设置为当前数据库。
4.在“练习2”数据库下，请使用SQL 的Create table 数据定义命令，按下面的要求完成student、course、sc 三个数据表的创建。
（1）建立表student：
列名 数据类型 允许空 主键 默认值 说明
1 sno Char(8) 否 是 无 学号
2 sname char(10) 是 否 无 姓名
3 ssex Char(2) 是 否 男 性别
4 sdept char(20) 是 否 无 所在系
（2）建立表course：
列名 数据类型 允许空 主键 说明
1 cno Char(6) 否 是 课程号
2 cname char(20) 否 否 课程名
3 credit tinyint 是 否 学分
4 semester tinyint 是 否 学期
要求：cname 的值唯一
（3）建立表sc：（注：包括两个外键，sno 和cno 共同组成主键）
列名 数据类型 允许空 主键 外键 说明
1 sno Char(8) 是 是 是 students(sno) 学号
2 cno Char(6) 是 是 是 course(sno) 课程号
3 grade tinyint 是 否 否 成绩
要求：0≤grade≤100
请将创建3 个表的Create table 命令写入实验报告中。
5.用alter table 命令完成下列要求
- 在student 表中，添加年龄sage 列，微整型，允许空。
- 在student 表中，为性别列添加check 约束，性别只能是男或女。
- 在student 表中，添加身份证号列，定长普通编码，宽度为18，Unique 约束
请将实现上述功能的alter table 命令写入实验报告中。
6.创建“学生信息”数据库，将下列数据用Excel 创建好之后，导入到“学生信息”数据库中
（1）表student 的数据:（请参考第4 版教材第45 页表4-1，下面红色的数据是我们修改和补充的）
sno sname ssex sage sdept
1712101 李勇 男 19 计算机系
1712102 刘晨 男 20 计算机系
1712103 王敏 女 18 计算机系
1712104 李小玲 女 19 计算机系
1721101 张立 男 22 信息系
1721102 吴宾 女 21 信息系
1721103 张海 男 20 信息系
1731101 钱小平 女 18 数学系
1731102 王大力 男 19 数学系
1731103 王敏 男 17 数学系
1721104 刘晨 女 18 信息系
1712105 吴宾 男 18 计算机系
（2）表course 的数据:（请参考第4 版教材第46 页表4-2，下面红色的数据项与教材上不同）
Cno Cname Credit Semester
c001 计算机文化学 3 1
c002 高等数学 6 1
c003 高等数学 3 2
c004 大学英语 6 2
c005 java 2 3
c006 程序设计 3 3
c007 数据结构 5 4
c008 操作系统 4 4
c009 数据库基础 4 5
c010 计算机网络 5 6
（3）表sc 的数据：（请参考第4 版教材第46 页表4-3，下面红色的数据是我们修改的）
sno cno grade
1712101 c001 90
1712101 c002 86
1712101 c003 92
1712101 c005 88
1712101 c006 Null
1712102 c001 76
1712102 c002 78
1712102 c005 66
1712104 c002 66
1712104 c005 78
1712104 c008 66
1721102 c001 82
1721102 c005 75
1721102 c007 92
1721102 c009 50
1721103 c002 68
1721103 c006 Null
1721103 c007 Null
1721103 c008 78
1731101 c001 80
1731101 c005 50
1731101 c007 45
1731102 c001 80
1731102 c002 75
1731102 c005 85
1731102 c009 88
注意：在导入数据过程中，请检查导入后的三个表的表名是否正确，以及各表中每个字段的数据类型是否符合本实验内容4 创建的表中个字段的数据类型。如果不符合请按本实验内容4 的定义进行修改。
7.根据上面创建的数据表完成下列查询：
- 查询student 表中全体学生的信息
- 查询course 表全部信息
- 查询选修课sc 表中选课的学生的学号（请去掉重复值）
请将完成上述查询功能的select 语句及相应的查询结果写入实验报告中
创建 3 个表的 Create table 命令：
--使用SQL语句创建表
create table student
(
sno char(8)primary key not null,/*定义主码，列级完整性约束*/
sname char(10),/*允许空，列级完整性约束*/
ssex char(2)default'男',
sdept char(20),
)
create table course
(
cno char(6)not null,
cname char(20)not null unique,
credit tinyint,
semester tinyint,
primary key(cno)/*定义主码，表级完整性约束*/
)
create table sc
(
sno char(8),
cno char(6),
grade tinyint,
primary key(sno,cno),/*定义主码，表级完整性约束*/
foreign key(sno)references student(sno),
foreign key(cno)references course(cno),/*定义外码，表级完整性约束*/
check(grade between 0 and 100)
)
alter table 命令：
alter table student add sage tinyint
--check约束格式：ALTER TABLE 表名 ADD [ CONSTRAINT 约束名 ] CHECK （逻辑表达式）
alter table student add constraint student_sex check(ssex='女'or ssex='男')
alter table student add s_id char(18) unique
select 语句:
select * from student
select * from course
select distinct sno from sc
d a y 3 day3 day3 用SQL 的数据查询语句实现分组查询和多表连接查询
一、实验目的：
掌握SQL 的数据查询功能，实现分组查询、多表连接查询和嵌套查询
二、实验要求：
用实验2 创建的学生信息数据库以及三个关系表student，course，sc 练习分组查询、多表的连接查询以及嵌套查询。
三、实验内容：
1.启动MicroSoft SQL Server→SQL Server Management Studio（即SSMS），在“连接到服务器”对话框中，在服务器名称文本框中输入Local，选择“Windows 身份验证”，点击“连接”，进入SSMS 操作界面
2.在SSMS 界面中，快速的创建一个学生信息数据库，并将Excel 中的student、course、sc 三个表的数据快速的导入数据库中
3.基于student、sc、course 三个表，完成下列查询：
(1)查询选修了课程的学生学号、姓名、性别、所在院系和课程名称。
(2)没有选修课程的学生的学号、姓名、性别、所在院系。
(3)查询student 表中同名同姓的学生的学号、姓名、性别、所在院系。
(4)查询和王大力同系的同学信息。
(5)查询每个系的男、女生人数和最大年龄、平均年龄。
(6)查询每门课程的选课人数和平均成绩。
(7)查询没有学生选修的课程信息。
(8)查询选课人数最多的那门课的课程信息。
(9)查询选课成绩在前15%的学生的学号、姓名、性别、年龄和选修的课程名称、成绩。
(10)查询每个学生的学号、姓名、性别、所在系、选修的课程号、课程名和考试成绩，并将查询的结果保存在stu 数据表中。
请将完成上述各题的SQL 语句和执行界面截图写入上机实验报告中。
--(1)查询选修了课程的学生学号、姓名、性别、所在院系和课程名称。
select student.sno,sname,ssex,sdept,cname from course
join sc on course.cno=sc.cno
join student on sc.sno=student.sno
where course.cno in ( select cno from sc)
--(2)没有选修课程的学生的学号、姓名、性别、所在院系。
select student.sno,sname,ssex,sdept from student
where student.sno not in (select sno from sc)
--(3)查询 student 表中同名同姓的学生的学号、姓名、性别、所在院系。
select student.sno,sname,ssex,sdept from student
where sname in(select sname from student group by sname having(count(*))>1)
--(4)查询和王大力同系的同学信息。
select * from student
where sdept in (select sdept from student where sname='王大力')
--(5)查询每个系的男、女生人数和最大年龄、平均年龄。
select sdept,ssex,count(*)人数,max(sage)最大年龄,avg(sage)平均年龄 from student
group by sdept,ssex
--(6)查询每门课程的选课人数和平均成绩。
select cno,count(*)选课人数,avg(grade)平均成绩 from sc
group by cno
--(7)查询没有学生选修的课程信息。
select cno,cname
from course
where course.cno not in ( select cno from sc)
--(8)查询选课人数最多的那门课的课程信息。
select cno,cname
from course
where course.cno in ( select max(cno) from sc)
--(9)查询选课成绩在前15%的学生的学号、姓名、性别、年龄和选修的课程名称、成绩。
select student.sno,sname,ssex,sage,cname,grade from sc a
join student on student.sno=a.sno
join course on course.cno=a.cno and a.grade is not null
where (select count(*) from sc b where a.grade<b.grade)<(select COUNT(*)*0.15 FROM sc)--有并列第四
order by grade desc --降序
--(10)查询每个学生的学号、姓名、性别、所在系、选修的课程号、课程名和考试成绩，并将查询的结 果保存在 stu 数据表中。
select student.sno,sname,ssex,sdept,sc.cno,cname,grade into stu from student
join sc on student.sno=sc.sno
join course on course.cno=sc.cno
d a y 4 day4 day4 用SQL 语句实现嵌套查询、数据操作和创建视图
一、实验目的：
1.掌握多表连接查询和嵌套查询。
2.掌握SQL 的数据操作功能，实现数据查询和数据更改。
二、实验要求：
在现有的数据库上进行各种查询操作，以及数据插入、更新、删除和创建视图等操作。
三、实验内容：
1.启动MicroSoft SQL Server→SQL Server Management Studio（即SSMS），在“连接到服务器”对话框中，在服务器名称文本框中输入Local，选择“Windows 身份验证”，点击“连接”，进入SSMS 操作界面
2.在SSMS 界面中，快速的创建一个学生信息数据库，并将Excel 中的student、course、sc 三个表的数据快速的导入数据库中
3.基于student、sc、course 三个表，完成下列查询：
(1)查询年龄大于全体女生平均年龄的女生信息。
(2)查询选修“c001”课程并且考试成绩高于本门课平均成绩的学生学号、姓名、课程名、考试成绩。
(3)查询计算机系没有选修课程的学生的学号、姓名、性别。
(4)统计计算机系和信息系每名学生的选课门数、参加考试的门数和考试的平均成绩，要求查询的结果按所在系升序、系相同的按选课门数降序排列，显示所在系、学号、选课门数、参加考试的门数、考试的平均成绩（包括没有选课的学生）。
(5)统计每个系每名学生的选课门数、选修课的平均成绩和选课情况。其中对选课情况的处理为：如果选课门数等于或超过4 门，则选课情况为“多”；如果选课门数在2-3 门，则选课情况为“一般”；如果选课门数只有1 门，则选课情况为“少”；如果学生没有选课，则选课情况为“未选”，请将查询结果按系名升序、选课门数降序排列。（请同学们参考教材p81 例77）
4.基于student、sc、course 三个表，完成下列数据插入、更新和删除：
(1)将student 表复制到stu1 表中，在stu1 表中插入学号为“1731105”、姓名为“陈思进”、性别为“男”的学生记录。
(2)将course 表复制到kc 表中，在kc 表中插入课程号为“c020”、课程名为“嵌入式系统”、学分为3、开课学期为7 的记录。
(3)将sc 表复制到xsxk 表中，给xsxk 表所有参加了考试，且考试课程门数大于3 门的学生的选修课成绩加5 分
(4)在xsxk 表中，给非计算机系的学生的数据结构课程考试成绩加10%分。
(5)在kc 表中，修改全部课程的学分，修改规则是：如果是第1、2 学期开设的课程，则学分增加5分；如果是第3、4 学期开设的课程，则学分增加3 分；如果是第5、6 学期开设的课程，则学分增加1 分；其他学期开设的课程，学分不变。
(6)在stu1 表中，删除没有选修课程的学生记录。
(7)在stu1 表中，删除选修课成绩低于60 分、信息系的学生记录。
5.基于student、sc、course 三个表，创建三表连接的视图stu_sc_c，该视图有学生的学号、姓名、性别、所在系、课程号、课程名、课程学分、选课成绩。
6.基于上题的三表连接视图stu_sc_c，创建视图stu_xk 满足下列要求：统计每个学生的修课总学分，要求列出学生的学号和总学分（说明：考试成绩≥60 才可获得此门课程的学分）
7.根据第6 题创建的视图stu_xk，显示学生的学号和总学分
8.修改第6 题创建的视图stu_xk，列出学生的学号、选课门数和总学分（说明：考试成绩≥60 才算选修了该课程，并可获得此门课程的学分）
9.根据第8 题的stu_xk 视图，显示视图内容
10.请保留student、sc、course 三个基本表，删除本实验复制的stu1、kc、xsxk 三个表以及stu_sc_c视图和stu_xk 视图。
请将完成上述各题的SQL 语句和执行结果界面截图写入上机实验报告中。
--(1)查询年龄大于全体女生平均年龄的女生信息
select * from student
where ssex='女' and sage>(select avg(sage) from student where ssex ='女')
--(2)查询选修“c001”课程并且考试成绩高于本门课平均成绩的学生学号、姓名、课程名、考试成绩
select student.sno,sname,cname,grade from student
join sc on student.sno=sc.sno
join course on course.cno=sc.cno
where sc.cno='c001' and grade>(select avg(grade) from sc where sc.cno='c001')
--(3)查询计算机系没有选修课程的学生的学号、姓名、性别
select student.sno,sname,ssex from student
where student.sno not in (select sno from sc)and sdept='计算机系'
--(4)统计计算机系和信息系每名学生的选课门数、参加考试的门数和考试的平均成绩，要求查询的结
--果按所在系升序、系相同的按选课门数降序排列，显示所在系、学号、选课门数、参加考试的门数、考试
--的平均成绩（包括没有选课的学生）
select sdept,sc.sno 学号,count(cno)选课门数,count(grade)参加考试的门数,sum(grade)/count(grade)考试的平均成绩 from sc
join student s on s.sno=sc.sno and (sdept='计算机系' or sdept='信息系')
group by sc.sno,sdept
order by sdept,count(grade) desc
--(5)统计每个系每名学生的选课门数、选修课的平均成绩和选课情况。其中对选课情况的处理为：如
--果选课门数等于或超过 4 门，则选课情况为“多”；如果选课门数在 2-3 门，则选课情况为“一般”；如果
--选课门数只有 1 门，则选课情况为“少”；如果学生没有选课，则选课情况为“未选” ，请将查询结果按系
--名升序、选课门数降序排列
select sdept,sc.sno 学号,count(cno)选课门数,sum(grade)/count(grade)考试的平均成绩,
case
when count(cno)>=4 then '多'
when count(cno)between 2 and 3 then '一般'
when count(cno)=1 then '少'
when count(cno)=null then '未选'
end as 选课情况
from sc
join student s on s.sno=sc.sno
group by sc.sno,sdept
order by sdept,count(grade) desc
--(1)将 student 表复制到 stu1 表中，在 stu1 表中插入学号为“1731105”、姓名为“陈思进”、性别为“男”的学生记录。
select * into stu1 from student
insert into stu1(sno,sname,ssex)values('1731105','陈思进','男')
--(2)将 course 表复制到 kc 表中，在 kc 表中插入课程号为“c020”、课程名为“嵌入式系统”、学分为3、开课学期为 7 的记录。
select * into kc from course
insert into kc(cno,cname,credit,semester)values('c020','嵌入式系统',3,7)
--(3)将 sc 表复制到 xsxk 表中，给 xsxk 表所有参加了考试，且考试课程门数大于 3 门的学生的选修课成绩加 5 分
select * into xsxk from sc
update xsxk set grade=grade+5
where sno in(select sno from xsxk group by sno having count(*)>3)
--(4)在 xsxk 表中，给非计算机系的学生的数据结构课程考试成绩加 10%分。
update xsxk set grade=grade*1.1
where sno not in (select sno from student where sdept = '计算机系' )
and cno in(select cno from course where cname='数据结构')
--(5)在 kc 表中，修改全部课程的学分，修改规则是：如果是第 1、2 学期开设的课程，则学分增加 5
--分；如果是第 3、4 学期开设的课程，则学分增加 3 分；如果是第 5、6 学期开设的课程，则学分增加 1 分；
--其他学期开设的课程，学分不变。
update kc set credit = credit +
case
when semester in(1,2) then 5
when semester in(3,4) then 3
when semester in(5,6) then 1
end from kc
--(6)在 stu1 表中，删除没有选修课程的学生记录。
delete from stu1 where sno not in(select sno from sc)
--(7)在 stu1 表中，删除选修课成绩低于 60 分、信息系的学生记录。
delete from stu1 where sno in(select sno from sc where grade<60) and sdept='信息系'
--5.基于 student、sc、course 三个表，创建三表连接的视图 stu_sc_c，该视图有学生的学号、姓名、性别、 所在系、课程号、课程
名、课程学分、选课成绩。
--select * from stu_sc_c
go
create view stu_sc_c(学号,姓名,性别,所在系,课程号,课程名,课程学分,选课成绩)
as
select student.sno,sname,ssex,sdept,course.cno,cname,credit,grade
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno
go
--6.基于上题的三表连接视图 stu_sc_c，创建视图 stu_xk 满足下列要求：统计每个学生的修课总学分， 要求列出学生的学号和总学分
（说明：考试成绩≥60 才可获得此门课程的学分）。
--drop view stu_sc_c
--select * from stu_xk
go
create view stu_xk (学号,总学分)as
select 学号, sum(课程学分)from stu_sc_c v
where 选课成绩>=60 group by 学号
go
--7.根据第 6 题创建的视图 stu_xk，显示学生的学号和总学分。
select * from stu_xk
--8.修改第 6 题创建的视图 stu_xk，列出学生的学号、选课门数和总学分（说明：考试成绩≥60 才算选修了该课程，并可获得此门课
程的学分）。
--drop view stu_xk
go
alter view stu_xk(学号,选课门数,总学分) as
select 学号, count(课程号) ,sum(课程学分)from stu_sc_c v
where 选课成绩>=60 group by 学号
go
--9.根据第 8 题的 stu_xk 视图，显示视图内容。
select * from stu_xk
--10.请保留 student、 sc、 course 三个基本表，删除本实验复制的 stu1、 kc、 xsxk 三个表以及 stu_sc_c
--视图和 stu_xk 视图。
drop table stu1
drop table kc
drop table xsxk
drop view stu_sc_c
drop view stu_xk
d a y 5 day5 day5 数据库的备份和恢复
一、实验目的：
熟悉并掌握数据库备份和恢复的原理和操作。
二、实验要求：
1.掌握备份设备的创建、使用。
2.掌握数据库的备份和恢复操作。
3.掌握数据库完整备份、差异备份、事务日志备份的含义。
三、实验内容：
1、利用如下sql 语句，创建stu_lx 数据库。
create database stu_lx
use stu_lx
2、利用如下sql 语句在stu_lx 数据库中创建表（具体含义见第四版课本P45 页）
create table student
(
sno char ( 7 ) primary key,
sname char ( 10 ) not null,
ssex char (2),
sage tinyint ,
sdept char (20)
)
create table course
(
cno char(10) not null,
cname char(20) not null,
ccredit tinyint ,
semester tinyint,
primary key(cno)
)
create table sc
(
sno char(7) not null,
cno char(10) not null,
grade tinyint,
primary key ( sno, cno ) ,
foreign key (sno) references student (sno),
foreign key (cno) references course (cno)
)
3、认真阅读教材第12 章备份和恢复数据库或何玉洁老师的第12 章课件，利用SSMS 工具按顺序完成如下操作：
（1）创建永久备份设备：backup1，backup2。
（2）对stu_lx 数据库进行一次完整备份，并以追加的方式备份到backup1 设备上。
（3）执行下述语句删除stu_lx 数据库中sc 表:`Drop table sc`
（4）利用backup1 设备上对stu_lx 数据库进行的完整备份，还原stu_lx 数据库。
（5）查看sc 表是否被恢复回来了。（答案：恢复了）
注意：不能对正在使用的数据库进行恢复。
4、利用SSMS 工具按顺序完成下列操作：
（1）利用SSMS 工具对stu_lx 数据库进行一次完整备份，并以覆盖的方式备份到backup1 设备上。
（2）执行下述语句，完成在course 表中插入两行新记录：
Insert into course values('c201', '离散数学', 3,2)
Insert into course values('c101', '数据结构', 6,3)
（3）将stu_lx 数据库以覆盖的方式差异备份到backup2 设备上。
（4）执行下述语句，删除新插入的记录：`Delete from course where cno like 'c_01'`
（5）利用backup1 和backup2 备份设备对stu_lx 数据库的备份，还原stu_lx 数据库。
（6）完全恢复完成后，查看course 中是否有第（2）步插入的记录？为什么？（答案：有）
5、利用SSMS 工具按顺序完成后下列操作：
（1）将stu_lx 数据库的恢复模式改为“完整”的。（设置方法，参看教材p223）
（2）对stu_lx 数据库进行一次完整备份，并以覆盖的方式备份到backup1 设备上。
（3）执行下述语句，完成在course 表中插入两行新记录：
Insert into course values('c202', '编译原理', 4,4)
Insert into course values('c203', '操作系统', 6,4)
（4）将stu_lx 数据库以追加的方式差异备份到backup1 设备上。
（5）执行下述语句，删除新插入的记录：`Delete from course where cno like 'c20_'`
（6）对stu_lx 数据库进行一次日志备份，并以覆盖的方式备份到backup2 设备上。
（7）利用backup1 和backup2 备份设备，恢复stu_lx 数据库。
（8）恢复完成后，查看course 中是否有第（3）步插入的记录？为什么？（答案：没有了）
6、利用SSMS 工具按顺序完成后下列操作：
（1）利用SSMS 工具对stu_lx 数据库进行一次完整备份，并以覆盖的方式备份到backup1 设备上。
（2）删除stu_lx 数据库中的SC 表。
（3）对stu_lx 数据库进行一次差异备份，以追加的方式差异备份到backup1 设备上。
（4）删除stu_lx 数据库。
（5）利用backup1 备份设备恢复stu_lx 数据库的完整备份。在恢复完成后使数据库成为可用状态。
（6）在SSMS 工具的对象资源管理器中查看是否有stu_lx 数据库？如果有，查看stu_lx 数据库中是否有sc 表？为什么？（答案：没有了）
请记录每一步的结果和完成情况，撰写在实验报告中。
总结:
疯狂备份+还原,截了一堆图片
d a y 6 day6 day6 创建存储过程和触发器
一、实验目的：
1.了解存储过程的功能特点
2.掌握SQL Server 创建和管理存储过程的方法，存储过程的参数定义和存储过程的执行
2.掌握SQLServer 触发器的创建语句
二、实验要求：
本实验基于前面实验创建的“学生信息”数据库，以及student、course、sc 三个数据表完成以下实验内容
三、实验内容：
1.启动MicroSoft SQL Server→SQL Server Management Studio（即SSMS），在“连接到服务器”对话框中，在服务器名称文本框中输入Local，选择“Windows 身份验证”，点击“连接”，进入SSMS 操作界面。
2.检查对象资源管理器中是否有“学生信息”数据库以及student、course、sc 三个数据表。若没有，请在SSMS 界面中，快速的创建一个“学生信息”数据库，并将实验二创建的Excel 工作簿文件中的student、course、sc 三个表的数据快速的导入该数据库中。
3.请按下列要求创建存储过程并执行：
(1)在学生信息数据库中，创建一个名为MyProc1 的存储过程，实现查询student 数据表中计算机系的学生姓名、性别和年龄，显示为汉字标题。
(2)在学生信息数据库中，创建带两个输入参数、名为MyProc2 的存储过程，实现指定系名和课程名，查询出该系、该门课程的选课人数，显示系名、课程名和选课人数（显示汉字标题）。
请用如下带输入参数的存储过程执行语句执行：Exec Myproc2 ‘信息系’，’数据结构’
(3)在学生信息数据库中，创建带一个输入参数、两个输出参数（输入与输出参数的名称自行定义）、名为MyProc3 的存储过程，实现查询指定学生姓名，并以输出参数的形式返回学生选修课门数和选修课平均分。
请用如下语句执行Myproc3 存储过程：
declare @x int, @y float
exec myproc3 '李勇' ,@x output, @y output
select '李勇' 姓名, @x 选课门数, @y 平均成绩
(4)在学生信息数据库中，创建一个名为Myproc4 的存储过程，实现将指定课程的学分改为指定值。要求指定值在1-8 之间，否则不予修改。请将完成上述各题的SQL 语句和执行结果界面截图写入上机实验报告中。
--(1)在学生信息数据库中，创建一个名为 MyProc1 的存储过程，实现查询 student 数据表 中计算机系的学生姓名、性别和年龄，显示为汉字标题。
use 学生信息
go
create procedure myproc1 as
select sname 学生姓名,ssex 性别,sage 年龄 from student
where sdept='计算机系'
go
exec myproc1--查看执行结果
--(2)在学生信息数据库中，创建带两个输入参数、名为 MyProc2 的存储过程，实现指定系名和课程名，
--查询出该系、该门课程的选课人数，显示系名、课程名和选课人数（显示汉字 标题）。
go
create procedure myproc2
@sdept varchar(20), @cname varchar(30)--系名和课程名
as
select sdept 系名, cname 课程名, count(*)选课人数
from sc
join course on sc.cno=course.cno
join student on sc.sno=student.sno
where sdept =@sdept and cname =@cname
group by sc.cno,sdept,cname--注意有坑
go
exec myproc2'信息系','数据结构'
--(3)在学生信息数据库中，创建带一个输入参数、两个输出参数（输入与输出参数的名称自行定义）、
--名为 MyProc3 的存储过程，实现查询指定学生姓名，并以输出参数的形式返回学生选修课门数和选修课平均分。
go
create procedure myproc3
@sname char(20), @选修课门数 char(20)output, @选修课平均分 char(20)output
as
select @选修课门数=count(*),@选修课平均分=avg(grade)
from sc
join student on sc.sno=student.sno
where sname = @sname
group by sc.sno
go
declare @x int,@y float
exec myproc3 '李勇',@x output,@y output
select '李勇' 姓名,@x 选课门数,@y 平均成绩
--(4)在学生信息数据库中，创建一个名为 Myproc4 的存储过程，实现将指定课程的学分改 为指定值。
--要求指定值在 1-8 之间，否则不予修改。
go
create procedure myproc4
@courseno char(10),@credit tinyint--指定的课程号和学分
as
if @credit between 1 and 8
update course--表名
set credit= @credit
from course
where cno= @courseno
go
附录
我整理了一些SQL Server 的基本代码
三个基本表信息
基础
course表信息：
cno char(6)--课程号
cname char(20)--课程名
credit tinyint--学分
semester tinyint--学期
sc表信息：
sno char(8)--学号
cno char(8)--课程号
grade tinyint--成绩
ps：
sc表的外键 sno 与主键表student的 sno 建立关系
sc表的外键 cno 与主键表course的 cno 建立关系
student表信息：
sno char(8)--学号
sname char(10)
ssex char(2)
sage tinyint--年龄
sdept char(20)--所在系
--Checked：允许NULL值
select例题
--1.查找年龄不在20-23岁之间的女生的学号，姓名，出生年份
select sno,sname,2020-sage 出生年份 from student
where sage not between 20 and 23 and ssex='女'
--2.查询没有学生选修的课程(输出课程号和课程名)(下面为子查询)
select cno,cname
from course
where course.cno not in ( select cno from sc)
--3.用一条sql语句统计信息系和数学系各自男，女生的平均年龄
SELECT sdept,ssex,AVG(sage) AS 平均年龄
FROM student
where sdept in('数学系','信息系')
GROUP BY sdept ,ssex
--4.填充“奖学金等级”字段内容，条件是：若选修课成绩的平均值≥90，荣获“一等”奖学金；
update student set 奖学金等级 = '一等'
where sno in(select sno from sc group by sno having avg(grade)>=90)
--(1)查询选修了课程的学生学号、姓名、性别、所在院系和课程名称。
select student.sno,sname,ssex,sdept,cname from course
join sc on course.cno=sc.cno
join student on sc.sno=student.sno
where course.cno in ( select cno from sc)
--(2)没有选修课程的学生的学号、姓名、性别、所在院系。
select student.sno,sname,ssex,sdept from student
where student.sno not in (select sno from sc)
--(3)查询 student 表中同名同姓的学生的学号、姓名、性别、所在院系。
select student.sno,sname,ssex,sdept from student
where sname in(select sname from student group by sname having(count(*))>1)
--(4)查询和王大力同系的同学信息。
select * from student
where sdept in (select sdept from student where sname='王大力')
--(5)查询每个系的男、女生人数和最大年龄、平均年龄。
select sdept,ssex,count(*)人数,max(sage)最大年龄,avg(sage)平均年龄 from student
where sdept is not null
group by sdept,ssex
--(6)查询每门课程的选课人数和平均成绩。
select cno,count(*)选课人数,avg(grade)平均成绩 from sc
group by cno
--(7)查询没有学生选修的课程信息。
select cno,cname
from course
where course.cno not in ( select cno from sc)
--(8)查询选课人数最多的那门课的课程信息。
select cno,cname
from course
where course.cno in ( select max(cno) from sc)
--(9)查询选课成绩在前15%的学生的学号、姓名、性别、年龄和选修的课程名称、成绩。
select student.sno,sname,ssex,sage,cname,grade from sc a
join student on student.sno=a.sno
join course on course.cno=a.cno and a.grade is not null
where (select count(*) from sc b where a.grade<b.grade)<(select COUNT(*)*0.15 FROM sc)--有并列第四
order by grade desc --降序
--(10)查询每个学生的学号、姓名、性别、所在系、选修的课程号、课程名和考试成绩，并将查询的结 果保存在 stu 数据表中。
drop table stu
select student.sno,sname,ssex,sdept,sc.cno,cname,grade into stu from student
join sc on student.sno=sc.sno
join course on course.cno=sc.cno
--创建和调用存储过程：查询Stu数据库中某个同学的选修课程的信息，包括学号，姓名，课程名称，成绩
go
create procedure proc_select--建立存储过程
@sno char(10) output,--输入输出参数
@sname varchar(20) out,--输出参数
@cno char(4) out,--输出参数
@grade tinyint out--输出参数
as
select @sname=sname,@sno=student.sno,@cno=cno,@grade=grade --select里面写输出参数
from student,sc --从学生表，选修表中查询
where @sno=student.sno--where里面写输入参数
go
数据查询1
--1. 选择表中若干列
select sno, sname from student
select * from student
/*等价于: */
select sno,sname,ssex, sage, sdept from student
select sname,sage,2010 - sage 出生年份 from student--(注意sage和出生年份之间要有空格)
--常量列
select sname,'出生年份:' as csp, 2010-sage from student --as可写可不写
--2.选择表中若干元组
--去重
select distinct sno from sc
select sname from student where sdept = '计算机系'
select sname, sage from student where sage < 20
select distinct sno from sc where grade < 60 --查询考试成绩有不及格的学生的学号
--确定范围用用between…and和not between…and
select sname, sdept, sage,ssex from student where sage between 20 and 23 and ssex='女'
select sname, sdept, sage from student where sage not between 20 and 23
--确定集合（格式为：列名 [ not ] in (常量1, 常量2, … 常量n) ）
select sname, ssex from student where sdept in ('信息系', '数学系', '计算 机系')
select sname, ssex from student where sdept not in ('信息系', '数学系', '计算机系')
--3.字符匹配（一般形式为： 列名 [not ] like <匹配串> ）
--_：匹配任意一个字符；
--%：匹配0个或多个字符；
--[ ]：匹配[ ]中的任意一个字符；对于连续字母 的匹配，例如匹配[abcd]，可简写为[a-d]
--[ ^ ]：不匹配[ ]中的任意一个字符
--查询姓‘张’的学生的详细信息。
select * from student where sname like '张%'
--查询学生表中姓‘张’、‘李’和‘刘’的学生的 情况。
select * from student where sname like '[张李刘]%'
--查询名字中第2个字为‘小’或‘大’的学生的姓 名和学号。
select sname, sno from student where sname like '_[小大]%'
--查询所有不姓“王”也不姓“张”的学生 姓名
select sname from student where sname not like '[王张]%'
select sname from student where sname like '[^王张]%'
select sname from student where sname not like '王%' and sname not like '张%'
--查询姓“王”且名字是2个字的学生 姓名。
select sname from student where sname like '王_'
--查询姓王且名字是3个字的学生姓名
select sname from student where sname like '王__'
--4.涉及空值的查询
select sno, cno from sc where grade is null
select sno, cno from sc where grade is not null
--5.多重条件查询
select sname from student where sdept = '计算机系' and sage < 20
select sname,sdept, sage from student where (sdept = '计算机系' or sdept = '信息系') and sage >= 20
select sname,sdept, sage from student where sdept in ('计算机系', '信息系') and sage >= 20 --与上一个等价
---------------------------------------------------------
--1对查询结果进行排序
--order by <列名> [asc | desc ] [,<列名> … ](升序asc或降序desc)
--将学生按年龄的升序排序。
select * from student order by sage
--查询选修了‘c02’号课程的学生的学号及其成绩，查询结果按成绩降序排列。
select sno, grade from sc where cno='c02' order by grade desc
--查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。
select * from student order by sdept, sage desc
--2使用计算函数汇总数据
/*
sql提供的计算函数有：
count（ * ）：统计表中元组个数；
count（[distinct] <列名> ）：统计本列列值个数；
sum（[distinct] <列名> ）：计算列值总和；
avg（[distinct] <列名> ）：计算列值平均值；
max（[distinct] <列名> ）：求列值最大值；
min（[distinct] <列名> ）：求列值最小值。
上述函数中除count（*）外，其他函数在计算过 程中均忽略null值。
注意：计算函数不能出现在where子句中
*/
--统计学生总人数
select count(*) from student
--统计选修了课程的学生的人数。
select count (distinct sno)from sc
--计算9512101号学生的考试总成绩之和。
select sum(grade) from sc where sno = '9512101'
--计算’c01’号课程学生的考试平均成绩。
select avg(grade) from sc where cno='c01'
--查询选修了’c01’号课程的学生的最高分 和最低分。
select max(grade) , min(grade) from sc where cno='c01'
--3对查询结果进行分组计算
--[group by <分组条件>] [having <组过滤条件>]
--having用于对分组自身进行限制，它有点像where子句，但它用于组而不是对单个记录。
--统计每门课程的选课人数，列出课程号和人数。
/*
思路：
对查询结果按cno的值分组，所有具有相同 cno值的元组为一组，
然后再对每一组使用 count计算，求得每组的学生人数。
*/
select cno as 课程号,count(sno) as 选课人数 from sc group by cno
--查询每名学生的选课门数和平均成绩。
select sno as 学号,count(*) as 选课门数, avg(grade) as 平均成绩 from sc group by sno
--查询修了3门以上课程的学生的学号
select sno from sc group by sno having count(*) > 3
--查询修课门数等于或大于4门的学生的平均成绩和选课门数。
select sno, avg(grade)平均成绩, count(*) 修课门数 from sc group by sno having count(*) >= 4
--查询没有学生选修的课程(输出课程号和课程名)
select cno,cname
from course
where course.cno not in ( select cno from sc)
数据查询2
--多表连接查询
--查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。
select sname, cno, grade from student join sc on student.sno = sc.sno where sdept = '计算机系'
--查询计算机系修了高等数学课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。
--思路：student的学号，sc的学号和课程号，course的课程号
select sname, cname, grade from student s
join sc on s.sno = sc. sno
join course c on c.cno = sc.cno
where sdept = '计算机系' and cname = '高等数学'
--或者
select sname, cname, grade from sc
join student on student.sno = sc. sno
join course on course.cno = sc.cno
where sdept = '计算机系' and cname = '高等数学'
嵌套查询和数据操作和创建视图
--(1)查询年龄大于全体女生平均年龄的女生信息。
select * from student
where ssex='女' and sage>(SELECT AVG(sage) FROM student WHERE ssex ='女')
--(2)查询选修“c001”课程并且考试成绩高于本门课平均成绩的学生学号、姓名、课程名、考试成绩。
select student.sno,sname,cname,grade from student
join sc on student.sno=sc.sno
join course on course.cno=sc.cno
where sc.cno='c001' and grade>(SELECT AVG(grade) FROM sc where sc.cno='c001')
--(3)查询计算机系没有选修课程的学生的学号、姓名、性别。
select student.sno,sname,ssex from student
where student.sno not in (select sno from sc)and sdept='计算机系'
--(4)统计计算机系和信息系每名学生的选课门数、参加考试的门数和考试的平均成绩，要求查询的结
--果按所在系升序、系相同的按选课门数降序排列，显示所在系、学号、选课门数、参加考试的门数、考试
--的平均成绩（包括没有选课的学生）。
select sdept,sc.sno 学号,count(cno)选课门数,count(grade)参加考试的门数,sum(grade)/count(grade)考试的平均成绩 from sc
join student s on s.sno=sc.sno and (sdept='计算机系' or sdept='信息系')
group by sc.sno,sdept
order by sdept,count(grade) desc
--(5)统计每个系每名学生的选课门数、选修课的平均成绩和选课情况。其中对选课情况的处理为：如
--果选课门数等于或超过 4 门，则选课情况为“多”；如果选课门数在 2-3 门，则选课情况为“一般”；如果
--选课门数只有 1 门，则选课情况为“少”；如果学生没有选课，则选课情况为“未选” ，请将查询结果按系
--名升序、选课门数降序排列。
select sdept,sc.sno 学号,count(cno)选课门数,sum(grade)/count(grade)考试的平均成绩,
case
when count(cno)>=4 then '多'
when count(cno)between 2 and 3 then '一般'
when count(cno)=1 then '少'
when count(cno)=null then '未选'
end as 选课情况
from sc
join student s on s.sno=sc.sno
group by sc.sno,sdept
order by sdept,count(grade) desc
--(1)将 student 表复制到 stu1 表中，在 stu1 表中插入学号为“1731105”、姓名为“陈思进”、性别为“男”的学生记录。
select * into stu1 from student
insert into stu1(sno,sname,ssex)values('1731105','陈思进','男')
--(2)将 course 表复制到 kc 表中，在 kc 表中插入课程号为“c020”、课程名为“嵌入式系统”、学分为3、开课学期为 7 的记录。
select * into kc from course
insert into kc(cno,cname,credit,semester)values('c020','嵌入式系统',3,7)
--(3)将 sc 表复制到 xsxk 表中，给 xsxk 表所有参加了考试，且考试课程门数大于 3 门的学生的选修课成绩加 5 分
select * into xsxk from sc
update xsxk set grade=grade+5
where sno in(select sno from xsxk group by sno having count(*)>3)
--(4)在 xsxk 表中，给非计算机系的学生的数据结构课程考试成绩加 10%分。
update xsxk set grade=grade*1.1
WHERE Sno not in (SELECT Sno FROM Student WHERE Sdept = '计算机系' )
and cno in(select cno from course where cname='数据结构')
--(5)在 kc 表中，修改全部课程的学分，修改规则是：如果是第 1、2 学期开设的课程，则学分增加 5
--分；如果是第 3、4 学期开设的课程，则学分增加 3 分；如果是第 5、6 学期开设的课程，则学分增加 1 分；
--其他学期开设的课程，学分不变。
UPDATE kc SET credit = credit +
case
when semester in(1,2) then 5
when semester in(3,4) then 3
when semester in(5,6) then 1
end from kc
--(6)在 stu1 表中，删除没有选修课程的学生记录。
DELETE FROM stu1 WHERE sno not in(select sno from sc)
--(7)在 stu1 表中，删除选修课成绩低于 60 分、信息系的学生记录。
DELETE FROM stu1 WHERE sno in(select sno from sc where grade<60) and sdept='信息系'
--5.基于 student、sc、course 三个表，创建三表连接的视图 stu_sc_c，该视图有学生的学号、姓名、性别、 所在系、课程号、课程名、课程学分、选课成绩。
--SELECT * FROM stu_sc_c
go
create view stu_sc_c(学号,姓名,性别,所在系,课程号,课程名,课程学分,选课成绩)
as
select student.sno,sname,ssex,sdept,course.cno,cname,credit,grade
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno
go
--6.基于上题的三表连接视图 stu_sc_c，创建视图 stu_xk 满足下列要求：统计每个学生的修课总学分， 要求列出学生的学号和总学分（说明：考试成绩≥60 才可获得此门课程的学分）。
--drop view stu_sc_c
--SELECT * FROM stu_xk
go
create view stu_xk (学号,总学分)AS
SELECT 学号, SUM(课程学分)FROM stu_sc_c V
WHERE 选课成绩>=60 GROUP BY 学号
go
--7.根据第 6 题创建的视图 stu_xk，显示学生的学号和总学分。
SELECT * FROM stu_xk
--8.修改第 6 题创建的视图 stu_xk，列出学生的学号、选课门数和总学分（说明：考试成绩≥60 才算选修了该课程，并可获得此门课程的学分）。
--drop view stu_xk
go
alter view stu_xk(学号,选课门数,总学分) as
SELECT 学号, count(课程号) ,SUM(课程学分)FROM stu_sc_c V
WHERE 选课成绩>=60 GROUP BY 学号
go
--9.根据第 8 题的 stu_xk 视图，显示视图内容。
SELECT * FROM stu_xk
--10.请保留 student、 sc、 course 三个基本表，删除本实验复制的 stu1、 kc、 xsxk 三个表以及 stu_sc_c
--视图和 stu_xk 视图。
drop table stu1
drop table kc
drop table xsxk
drop view stu_sc_c
drop view stu_xk
内连接
select sdept 院系名称,ssex 性别,count(*)人数,max(sage)年龄最大值
from student group by ssex,sdept order by 院系名称 desc
select top 10 percent * from student order by sage--top和order by一起使用更有意义
--内连接
select student.sno,sname,sdept,course.cno,cname,credit,grade
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno
select student.sno,sname,sdept,course.cno,cname,credit,grade
from student join sc on student.sno=sc.sno join course on sc.cno=course.cno
select cname 课程名称,count(*) 人数
from sc,course --顺序无所谓
where sc.cno=course.cno
group by cname
--查找同名的人
select * from student x,student y
where x.sname=y.sname and x.sno!=y.sno
order by x.sname
--找和王大力同系的人的信息
select*from student
select *from student x,student y
where x.sname='王大力'and y.sname!='王大力'and x.sdept=y.sdept
视图临时作业
--在学生表中使用SQL的数据定义语句增加“奖学金等级,char（4）”字段。
alter table student add 奖学金等级 char (4) NULL
--用SQL的UPDATE命令填充“奖学金等级”字段内容，
--条件是：若选修课门数≥3，并且选修课成绩的平均值≥85，荣获“一等”奖学金；
--若选修课门数≥3，选修课成绩平均值≥80，小于85，则荣获“二等”奖学金。
update student set 奖学金等级 = '一等'
where sno in(select sno from sc group by sno having count(*)>=3 and avg(grade)>=85)
update student set 奖学金等级 = '二等'
where sno in(select sno from sc group by sno having count(*)>=3 and avg(grade)>=80 and avg(grade)<85)
--1.建立信息系学生的视图。
go CREATE VIEW 信息系 AS SELECT * FROM Student WHERE Sdept='信息系'; go
go CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept ='信息' go
--2.建立信息系选了“c001”号课程的学生视图，列出学生的学号、姓名、年龄和成绩。
go
CREATE VIEW 信息系2 (Sno,Sname,sage,Grade)
AS
SELECT Student.Sno,Sname,Sage,grade FROM Student
INNER JOIN SC ON Student.Sno=SC.Sno WHERE Sdept ='信息系' AND SC.Sno='c001';
go
--法二：
go
CREATE VIEW V_IS_S1(Sno, Sname,sage, Grade)
AS
SELECT Student.Sno, Sname, Sage,Grade FROM Student, SC
WHERE Sdept = '信息系' AND Student.Sno = SC.Sno AND SC.Cno = 'c001'
go
--3.建立信息系选修了“c001”号课程且成绩90分以上的学生视图。
go
CREATE VIEW 信息系3 AS
SELECT Student.Sno,Sname,Sage,grade FROM Student
INNER JOIN SC ON Student.Sno=SC.Sno
INNER JOIN Course ON Course .Cno=SC.cno
WHERE SC.Sno='c001' and Grade>90;
go
--4.建立一个学生学号、姓名和出生年份的视图。
go
CREATE VIEW 信息系4(Sno,Sname,age) AS SELECT Sno,Sname,2020-Sage FROM Student WHERE Sdept='信息系';
go
--5.定义一个名称为STU1的视图，该视图的SELECT语句完成查询：全体学生的学号、姓名、性别、年龄、所在院系、选修课程名称、选课成绩。
--Drop view STU1
go
CREATE VIEW STU1 AS
SELECT Student.Sno,Sname,Sage,grade,ssex,sdept,cname FROM Student
INNER JOIN SC ON Student.Sno=SC.Sno
INNER JOIN Course ON Course .Cno=SC.cno
go
SELECT Sno,Sname,ssex,Sage,sdept,cname,grade FROM STU1
草稿纸(自己无聊写的)
--(1)在学生信息数据库中，创建一个名为 MyProc1 的存储过程，实现查询 student 数据表 中计算机系的学生姓名、性别和年龄，显示为汉字标题。
USE 学生信息
GO
CREATE PROCEDURE MyProc1 AS
SELECT sname 学生姓名,ssex 性别,sage 年龄 FROM student
WHERE sdept='计算机系'
GO
exec MyProc1--查看执行结果
--(2)在学生信息数据库中，创建带两个输入参数、名为 MyProc2 的存储过程，实现指定系名和课程名，
--查询出该系、该门课程的选课人数，显示系名、课程名和选课人数（显示汉字 标题）。
go
CREATE PROCEDURE MyProc2
@sdept varchar(20), @cname varchar(30)--系名和课程名
AS
SELECT sdept 系名, cname 课程名, count(*)选课人数
FROM sc
JOIN course ON sc.cno=course.cno
JOIN student ON sc.sno=student.sno
WHERE sdept =@sdept AND cname =@cname
group by sc.cno,sdept,cname--注意有坑
go
Exec Myproc2'信息系','数据结构'
--(3)在学生信息数据库中，创建带一个输入参数、两个输出参数（输入与输出参数的名称自行定义）、
--名为 MyProc3 的存储过程，实现查询指定学生姓名，并以输出参数的形式返回学生选修课门数和选修课平均分。
go
CREATE procedure MyProc3
@sname char(20), @选修课门数 char(20)OUTPUT, @选修课平均分 char(20)OUTPUT
AS
SELECT @选修课门数=COUNT(*),@选修课平均分=AVG(Grade)
FROM sc
JOIN student ON sc.sno=student.sno
WHERE sname = @sname
GROUP BY sc.sno
go
declare @x int,@y float
exec myproc3 '李勇',@x output,@y output
select '李勇' 姓名,@x 选课门数,@y 平均成绩
--(4)在学生信息数据库中，创建一个名为 Myproc4 的存储过程，实现将指定课程的学分改 为指定值。
--要求指定值在 1-8 之间，否则不予修改。
go
CREATE PROCEDURE Myproc4
@courseno char(10),@credit tinyint--指定的课程号和学分
AS
IF @credit BETWEEN 1 AND 8
UPDATE course--表名
SET credit= @credit
FROM course
WHERE cno= @courseno
go