use studentcourse go
--在student数据库中，创建三张表 create table students (
Sno char(7) Primary Key,
Sname varchar(8) NOT NULL, Ssex char(2),
Sbirthday datetime, Sdept char(10), Smemo char(30) ) go
create table courses (
Cno char(4),
Cname varchar(12), CpreCno char(4), Ccredit tinyint,
constraint PK_course primary key(cno) ) go
create table sc (
Sno char(7) Foreign key references students(sno), Cno char(4) Foreign key references courses(cno), grade tinyint,
primary key(sno,cno) ) go
--分别向三张表中添加数据库
insert into students values('0602001','钟振华','男','1987-05-01','计算机','优秀毕业生') insert into students values('0602002','吴家硕','女','1987-03-24','计算机','爱好：音乐') insert into students values('0602003','吴春斌','男','1988-07-01','计算机',NULL) insert into students values('0701001','王腾飞','男','1988-05-04','机电','爱好：音乐') insert into students values('0701002','林世伟','女','1987-04-03','机电','爱好：体育') insert into students values('0701003','李乐仪','女','1986-03-03','机电',null) insert into students values('0703001','李奇','男','1988-09-17','工商管理',null) go
insert courses values('c1','数据结构',null,4) insert courses values('c2','数据库原理','c1',4) insert courses values('c3','大型数据库','c2',3)
1
insert courses values('c4','高尔夫',null,1) go
insert sc values('0602001','c1',61) insert sc values('0602001','c2',72) insert sc values('0602001','c3',88) insert sc values('0602002','c1',null) insert sc values('0602002','c2',61) insert sc values('0701001','c1',50) insert sc values('0701001','c2',null) insert sc values('0701002','c3',78) insert sc values('0701003','c1',52) insert sc values('0701003','c3',87) go
--查询表中数据
select * from students go
--删除表中数据 delete sc go
--删除表
drop table students go
--3① 查询计算机系全体学生的信息
select *from Students
--3② 查询姓“李”的学生的学号和姓名。
select Sno,Sname from Students where Sname like '李%'
--3③ 查询课程表中先行课为空的课程名。
select Cname from Courses where PreCno is null
--3④ 查询考试成绩有不及格的学生的学号。
select Sno from SC where Grade<60 or Grade is null
--3⑤ 求选修了C1 课程或C2 课程的学生的学号及成绩。
select Sno,Grade from SC where Cno='C1' or Cno='C2'
--3⑥ 查询全体计算机系学生的姓名及其年龄。
2
select Sname,year(getdate())-year(Sbirthday) from Students where Sdept='计算机'
--3⑦ 查询计算机系在1986－1987 年之间出生的学生的姓名。
select Sname from Students where Sdept='计算机' and year(Sbirthday)>=1986 and year(Sbirthday)<=1987
--3⑧ 查询姓“李”的前两个学生(按姓名升序排列)的学号和姓名。
select top 2 Sno,Sname from Students where Sname like '李%'order by Sname asc
--3⑨ 查询选修了两门以上课程的学生学号与课程数。
select Sno,count(Sno) as 课程数 from SC group by Sno having count(Sno)>2
--3⑩ 查询选修课程数大于等于2 的学生的学号、平均成绩和选课门数，并按平均成绩降序排列。
select sno,avg(grade),count(*) from sc group by sno having count(*)<=2 order by avg(grade)desc
--(1) 查询选修了【数据库原理】的计算机系的学生学号和姓名。 select Students.Sno,Sname from Students,SC,Courses
where Cname='数据库原理' and SC.Cno=Courses.Cno and SC.Sno=Students.Sno
--(2) 查询每一门课的间接先行课(即先行课的先行课)。 select a.Cno,b.PreCno from Courses a,Courses b where a.PreCno=b.Cno
--(3) 查询学生的学号、姓名、选修课程的名称和成绩。 select Students.Sno,Sname,Cname,Grade
from Students,SC,Students.Sno=SC.Sno and SC.Cno=Courses.Cno
--(4) 查询选修了课程的学生姓名。 select distinct Sname from Students,SC
where SC.Sno=Students.Sno
--(5) 查询所有学生的信息和所选修的课程,没有选课的同学对应的选课信息为空。 select students.*,courses.*,sc.*
from students left join (sc INNER JOIN courses on (courses.cno=sc.cno )) on(students.sno=sc.sno)
3
--(6) 查询所有课程的课程编号、课程名称及选课人数，没有被选的课程选课人数显示为0。 select Courses.Cno,Cname,count(Sno) as 选课人数 from Courses left join SC on Courses.Cno=SC.Cno group by Courses.Cno,Courses.Cname
--(7) 列出学生所有可能的选修情况。 select Students.Sno,Courses.Cno from Students,Courses order by Sno
--(8) 查找计算机系的学生选修课程数大于2 的学生的姓名、 平均成绩和选课门数，并按平均成绩降序排列。
select Sname,avg(Grade) as 平均成绩,count(Cno) as 选课门数 from Students left join SC on SC.Sno=Students.Sno group by Students.Sname,Students.Sdept
having count(Cno)>2 and Students.Sdept='计算机' order by avg(Grade) desc
--2、嵌套查询和组合查询操作。
--(1) 统计选修了【数据库原理】课程的学生人数。 select count(Sno) as 选修数据库原理的学生人数 from SC
where Cno=(select Cno from Courses where Cname='数据库原理')
--(2) 查询没有选修【数据库原理】课程的学生信息。 --可以用not exists select count(Students.Sno) as 没有选修数据库原理的学生人数 from Students
where Sno not in (select Sno from SC where Cno=(select Cno from Courses where Cname='数据库原理'))
--(3) 查询其他系中比计算机系学生年龄都小的学生。 select *
from Students
where Sdept!='计算机' and year(Sbirthday) < (select top 1 year(Sbirthday) from Students where Sdept='计算机' order by year(Sbirthday) desc)
--(4) 查询被0602001 学生或0602002 学生所选修的课程的课程号 --(用UNION 组合查询与IN 条件查询两种方法实现)。 select Cno from SC
where Sno='0602001' UNION select Cno from SC
4
where Sno='0602002'
select distinct Cno from SC
where Sno in (select Sno from SC Where Sno='0602001' or Sno='0602002')
--(5) 查询0602001 学生和0602002 学生同时选修的课程的课程号 --(用IN 组合查询与EXISTS 嵌套子查询两种方法实现)。 select distinct Cno from SC
where Sno='0602001' and Cno in (
select Cno from SC
where Sno='0602002' )
select Cno from SC where exists( select * from SC
where Sno='0602001' ) and Sno='0602002'
--(6) 查询被学号0602001 学生选修，但没有被0602002 学生所选修的课程的课程号。 select Cno from SC
where Cno not in (select Cno from SC where Sno='0602002')and Sno='0602001'
--① 向表Students 中插入(0601001,赵林, 男,1985-09-08,计算机)的记录。
insert Students values('0601001','赵林', '男','1985-09-08','计算机',NULL)
--② 向SC 表中添加一个学生的选课记录，学号为0601001，所选的课程号为C2。 --SC表中有Sno、Cno、Grade 这3 个列。这里只知道学号和课程号，不知道成绩值。 insert SC values('0601001','C2',NULL)
--③ 向表Students 中插入(0601002，张修雨，default)记录，该记录的数据中default 表示默认值‘男’，其他数据为空值。
insert Students(Sno,Sname,Ssex) values('0601002','张修雨',default)
--④ 用CREATE 语句建立表StudentBAK1，包含(与Students 的Sno、Sname、Sdept 相同)3
5
个字段，
-- 然后用INSERT SELECT 语句实现向StudentBAK1 添加Students 表中的计算机系学生的学号、姓名、所在系的信息。 create table StudentBAK1( Sno char(9) primary key, Sname char(20), Sdept char(20) )
insert into StudentBAK1 select Sno,Sname,Sdept from Students where sdept='计算机'
select * from StudentBAK1
--⑤ 用 SELECT… INTO 语句实现把Students 表中1986 年后(包含1986 年)出生的学生的学号、姓名存储到一个新表StudentBAK2。 create table StudentBAK2( Sno char(9), Sname char(20) )
select Sno,Sname into StudentBAK2 from Students where year(Sbirthday)>=1986
select * from StudentBAK2
--⑥ 将Students表中姓名为【赵林】的同学所在系改为【机电】，爱好改为【足球】。 update Students set Sdept='机电',Memo='足球' where Sname='赵林'
--⑦ 将选修了课程名为【数据库原理】并且有成绩的学生成绩加5 分。
update SC set Grade=Grade+5 where Cno = (select Cno from Courses where Cname='数据库原理')
--⑧ 将StudentCourse 数据库的StudentBAK1 表中所有姓赵的同学删除。 delete from StudentBAK1 where Sname like '赵%'
--⑨ 删除机电系课程成绩不及格或者没有登记成绩的学生选课记录。 delete from SC where
exists(select * from SC,Students where Sdept='机电' and Grade<60 and SC.Sno=Students.Sno) or Grade is NULL
--⑩ 将StudentCourse 数据库的StudentBAK2 表中的所有行删除。 delete StudentBAK2
--实验(6)、第四章 数据库安全性
--1.在数据库student中创建7个用户U1,U2,U3,U4,U5,U6,U7
6
--<记录下创建用户的过程，需要自己完成>记录你的实际操作过程 exec sp_addlogin 'U1' exec sp_addlogin 'U2' exec sp_addlogin 'U3' exec sp_addlogin 'U4' exec sp_addlogin 'U5' exec sp_addlogin 'U6' exec sp_addlogin 'U7'
exec sp_grantdbaccess 'U1' exec sp_grantdbaccess 'U2' exec sp_grantdbaccess 'U3' exec sp_grantdbaccess 'U4' exec sp_grantdbaccess 'U5' exec sp_grantdbaccess 'U6' exec sp_grantdbaccess 'U7' go
--2.完成教材4.2.4小节[例1]～[例7](P138~139)的授权语句。 --<记录下授权的过程，需要自己完成> grant select
on student to U1
grant all privileges on student to U2,U3
grant all privileges on course to U2,U3
grant select on sc to public
grant select,update(sno) on student to U4
grant insert
on sc to U5 with grant option
grant insert
on sc to U6 with grant option
grant insert on sc to U7
--3.在授权完成以后，验证用户是否拥有相应的权限
--注意：验证时需要分别以不同的用户(U1～U7)身份登录到数据库，进行相关的操作，
7
--检查他们是否具有教材表4.4(P139)中的相应权限。 --<记录下验证过程，需要自己完成>
--4.完成教材4.2.4小节[例8]～[例10] (P140)的权限回收语句，并验证在权限回收以后， --该用户是否真正丧失了对数据的相应权限。 --<记录下权限回收及验证过程，需要自己完成> revoke update(sno) on student from u4
revoke select on sc from public
revoke insert on sc from U5 cascade
--5.数据库角色的使用
--包括: 创建角色,为角色授权，将用户添加到角色中，从角色移除用户等操作，角 --色概念介绍参考教材4.2.5小节数据库角色的内容( P142)，
--而在SQL Server上机操作需要参考教学博客上提供的示例代码自行完成。 --<记录下角色的使用过程，需要自己完成> exec sp_addrole R1 go
grant select,insert,update on student to R1 go
exec sp_addrolemember R1,u5 go
revoke select on student from R1 go
--实验(七)、第五章 数据库完整性
服务器: 消息 547，级别 16，状态 1，行 1
INSERT 语句与 COLUMN FOREIGN KEY 约束 'FK__sc__sno__00551192' 冲突。该冲突发生于数据库 'Practice7'，表 'student', column 'sno'。 语句已终止。
服务器: 消息 2627，级别 14，状态 1，行 1
违反了 PRIMARY KEY 约束 'PK__student__79A81403'。不能在对象 'student' 中插入重复键。
语句已终止。
所影响的行数为 1 行）
分数不能大于100
（所影响的行数为 1 行）
8
--在开始实验之前，先创建一个名为Practice7的数据库， --下面所有的SQL语句都是在Practice7这个数据库内执行的 create database Practice7 go
use Practice7 go
--1.在数据库Practice7中创建教材P82的student, course, sc三张表，先不设置主键， --检查能否向表中插入重复值。
--<记录下验证的过程，需要自己完成> create table student (
sno char(9),
sname varchar(10), ssex char(2), sage tinyint, sdept char(20) ) go
create table course (
cno char(4),
cname varchar(16), cpno char(4), ccredit tinyint ) go
create table sc (
sno char(9), cno char(4), grade tinyint ) go
insert into student values('200215121','李勇','男',20,'CS') insert into student values('200215121','李勇','男',20,'CS') go
select * from student
--2.实体完整性：删除上一步创建的3张表，重新创建，对每张表都设置主键，先
--不为SC 表设置外键。再次检查能否向表中插入重复值，能否在SC表中插入不存在的学号(Sno)或者课程号(Cno)
9
drop table student drop table course drop table sc go
create table student (
sno char(9) primary key, sname varchar(10), ssex char(2), sage tinyint, sdept char(20) ) go
create table course (
cno char(4) primary key, cname varchar(16), cpno char(4), ccredit tinyint ) go
create table sc (
sno char(9), cno char(4), grade tinyint,
primary key(sno,cno) ) go
insert into student values('200215121','李勇','男',20,'CS') insert into student values('200215121','李勇','男',20,'CS') go
insert into sc values('200215121','0001',98) insert into sc values('200215122','0002',99) go
select * from sc
--3. 参照完整性：删除上一步创建的SC表，重新创建SC表并为其设置主键和外键。--再次验证能否在SC表中插入不存在的学号(Sno)或者课程号(Cno) drop table sc go
create table sc (
sno char(9) foreign key references student(sno),
10
cno char(4) foreign key references course(cno), grade tinyint,
primary key(sno,cno) ) go
insert into sc values('200215123','0001',98) go
--4. 用户定义的完整性：完成教材 P158[例10]，并通过插入数据进行验证。 drop table sc
drop table student go
create table student (
sno numeric(5),
sname varchar(20) not null, ssex char(2), sage tinyint, sdept char(20),
constraint c1 check(sno between 90000 and 99999), constraint c3 check(sage<30),
constraint c4 check(ssex in ('男','女')), constraint PK_student primary key(sno) ) go
--5.触发器的使用
--创建一个名为Tab的表，它仅包含一整数列(列名为col)。在表Tab上定义触发器Tri, --在对Tab表插入或者更新前进行检查，如果插入或更新的值在60~100之间，将值设置为60；
--如果大于100，则给出新值不允许大于100的提示信息。 create table tab(a int primary key,b int) go
insert into tab values(1,50) insert into tab values(2,70) insert into tab values(3,102) go
select * from tab go
create trigger tri on tab instead of insert as
declare @a int, @b int
11
select @a=a,@b=b from inserted if @b>100 print'分数不能大于100' else begin
if @b>=60
insert tab values(@a,60) else
insert tab select * from inserted end go
insert into tab values(4,40) insert into tab values(5,80) insert into tab values(6,105) go
drop trigger tri
12
因篇幅问题不能全部显示，请点此查看更多更全内容