数据库学习（以数据库系统概述课本为基础）

实验一 新建一个数据库（学生-课程数据库）

1.创建数据库**

事先要在I盘建立名为“My234”的文件夹，准备用来存放My234.mdf和My234.ldf文件

create database My234
on
(name=My234_data,
 filename='I:\My234\My234.mdf',
 size=10,
 maxsize=30,
 filegrowth=5)
 log on
 (name=My234_log,
  filename='I:\My234\My234.ldf',
  size=3,
  maxsize=12,
  filegrowth=2)

2.创建数据表

(1) 学生表Student,其中，Sno为主码

create table Student(
   Sno char(9) primary key,
   Sname char(20) unique,
   Ssex char(2),
   Sage char(3),
   Sdept char(20));

(2) 课程表Course

create table Course(
   Cno char(4) primary key,
   Cname char(40),
   Cpno char(4),
   Ccredit smallint,
   foreign key(Cpno) references Course(Cno));
 

(3) 学生选课表SC

create table SC(
   Sno char(9),
   Cno char(4),
   Grade smallint,
   primary key(Sno,Cno),
   foreign key(Sno) references Student(Sno),
   foreign key(Cno) references Course(Cno)
   );

3.修改基本表

(1) 在表中增加“入学时间”列，数据类型为日期型

alter table Student add S_entrance datetime;

(2)将年龄的数据类型由字符型改为整型，因SQL SERVER2000没有MODIFY功能，只能先删除再添加，即

     alter table Student drop column Sage;
     alter table Student add Sage int;

(3)删除Student表中的“入学时间”列

    alter table Student drop column S_entrance;
    或者这样写：
    alter table Student drop S_entrance;

(4)在Student表中增加一个完整性约束定义，使年龄的取值只能在15到40之间

    alter table Student add check (Sage between 15 and 40);

4.建立索引

为学生-课程数据库中的 Student、Course、SC 三个表建立索引。其中 Student 表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

   create unique index Stusno on Student(Sno);
   create unique index Coucno on Course(Cno);
   create unique index SCno on SC(Sno ASC,Cno DESC);

5.取消索引

取消按步骤4建立的索引

  drop index Student.Stusno;
  drop index Course.Coucno;
  drop index SC.SCno;

报错的话就用这句：

  drop index index_name on table_name ;

实验二 数据更新语言的使用

1.插入数据

（1）向学生表Student插入数据

 insert into Student values(‘200215121’,’李勇’,’男’,19,’CS’);
  insert into Student values(‘200215122’,’刘晨’,’男’,20,’CS’);
  insert into Student values(‘200215123’,’王敏’,’女’,20,’MA’);
  insert into Student values(‘200215124’,’霸天’,’女’,20,’MA’);
  insert into Student values(‘200215125’,’张立’,’男’,22,’IS’);

(2)向课程表Course插入数据

insert into Course(Cno,Cname,Ccredit) values(‘2’,’数学’,2);
insert into Course(Cno,Cname,Ccredit) values(‘6’,’数据处理’,2);
insert into Course values(‘4’,’操作系统’,’6’,’3’);
insert into Course values(‘7’,’PASCAL语言’,’6’,’4’);
insert into Course values(‘5’,’数据结构’,’7’,’4’);
insert into Course values(‘1’,’数据库’,’5’,’4’);
insert into Course values(‘3’,’信息系统’,’1’,’4’);

（3）向学生课程表SC插入数据

insert into SC(Sno,Cno) values('200215121','1');
insert into SC(Sno,Cno,Grade) values('200215121','2',85);
insert into SC values('200215121','3',88);
insert into SC values('200215122','2',90);
insert into SC values('200215122','3',80);

2.修改数据

给学号为 200215121 学生录入课程号为 1（数据库）的成绩。

update SC set Grade=92 where Sno='200215121' and Cno='1';

3.删除数据

给学号为 200215121 学生录入课程号为 1（数据库）的成绩。

delete from Student where Sno='200215124';

实验三 数据查询语言的使用

1.单表查询
（1）查询全体学生的学号与姓名

select Sno,Sname from Student;

（2）查询全体学生的详细记录

   select * from Student;

(3)查询全体学生的姓名及其出生年份

  select Sname,2020-Sage from Student;

(4)将学生按年龄的升序排序

     select * from Student order by Sage;

(5)查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列

 select * from Student order by Sdept,Sage desc;

2.连接查询
(1)查询每个学生及其修课的情况

select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno;

（2）查询与刘晨在同一个系学习的学生的姓名和所在的系

select S2.Sname,S2.Sdept from Student S1,Student S2 where S1.Sdept=S2.Sdept and S1.Sname='刘晨' and S2.Sname!='刘晨';

（3）查询选修2号课程，且成绩在90分以上的所有学生

select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and SC.Cno='2' AND SC.Grade>90;

（4）查询每个学生的学号、姓名、选修的课程及成绩

select Student.Sno,Sname,Cname,Grade from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno;

3.嵌套查询

(1) 查询与”刘晨”在同一个系学习的学生

select Sno,Sname,Sdept
from Student
where Sdept in(select Sdept from Student where Sname='刘晨');

（2）查询选修了“数据库”课程的学生的学号、姓名

select Sno,Sname 
from Student 
where Sno in
(select Sno 
 from SC
where Cno in
(select Cno 
from Course 
where Cname='数据库'));

4.集合查询
（1）查询选修了课程 1 或者选修了课程2 的学生。

select Sno from SC 
where Cno='1' 
union 
select Sno from SC 
where Cno='2';

5.统计查询
（1）统计学生总人数

select count(*) from Student;

(2) 统计选修了课程的学生的人数

select count(distinct Sno) from SC;

(3)计算200215121 号学生的考试成绩之和。

select SUM(Grade)  
 from SC  
 where Sno = '200215121';

（4）计算 1 号课程学生的考试平均成绩。

select AVG(Grade) from SC where Cno='1'； 

(5)查询选修了 1 号课程的学生的最高分和最低分。

select MAX(Grade),MIN(Grade) from SC where Cno='1';

(6)查询修了 3 门以上课程的学生的学号。

select Sno from SC group by Sno having COUNT(*)>3;

(7)统计每门课程的选课人数，列出课程号和人数。

select Cno as 课程号,COUNT(Sno) as 选课人数 from SC group by Cno;

实验四 视图的定义与使用

1.建立视图
（1）建立信息系学生的视图

create view IS_Student as 
select Sno,Sname,Sage from Student 
where Sdept='IS';

（2）建立信息系学生的视图，并要求进行插入、修改操作时还需保证该视图中只有信息
系学生。

create view IS_Student1 as 
select Sno,Sname,Sage from Student 
where Sdept='IS' with check option;

（3）建立信息系选修了‘c01’号课程的学生的视图。

create view V_IS_S1(Sno,Sname,Grade) as 
select Student.Sno,Sname,Sage from Student,SC 
where Student.Sno=SC.Sno and Sdept='IS' and SC.Cno='c01';

（4）建立信息系选修了‘c01’号课程且成绩在 90 分以上的学生的视图。

create view V_IS_S2 as
select Sno,Sname,Grade from V_IS_S1
where Grade>=90;

（5）定义一个反映学生出生年份的视图。

create view BT_S(Sno,Sname,Sbirth) as
select Sno,Sname,2020-Sage from Student;

（6）定义一个存放每个学生的学号及平均成绩的视图。

create view S_G(Sno,AverageGrade) as
select Sno,AVG(Grade) from SC group by Sno;

2.查询视图
（1）根据视图 IS_Student，查询信息系学生的信息。

select * from IS_Student;

（2）根据视图 V_IS_S2，查询信息系选修了‘c01’号课程且成绩在 90 分以上的学生。

select * from V_IS_S2;

(3)在信息系学生的视图中找出年龄小于 20 岁的学生。

select Sno,Sage from IS_Student where Sage<20;

3.更新视图
（1） 将信息系学生视图 IS_Student 中学号为 200215122 的学生姓名改为‘刘辰’。

update IS_Student set Sname='刘辰' where Sno='200215122';

4.删除视图
（1）删除IS_Student视图

  drop view IS_Student;

实验五 数据控制语言的定义与使用

1.给用户授权
（1） 把查询 Student 表的权限授予用户 USER1

grant select on Student to USER1;

（2）把对 Student 表和 Course 表的全部操作权限授予用户 USER2 和 USER3。

     grant all priviliges on table Student,Course to USER2,USER3;

(3) 把对表 SC 的查询权限授予所有用户。

  grant select on SC to public;

（4）把查询 Student 表和修改学生学号的权限授予用户 USER4。

    grant update(Sno),select on Student to USER4;
    

（5） 把对表 SC 的 INSERT 权限授予用户 USER5，并允许将此权限授予其他用户。

  grant insert on SC to USER5 with grant option;

（6） 收回用户 USER4 修改学生学号的权限。

 revoke update(Sno) on Student from USER4;

（7） 收回所有用户对表 SC 的查询权限。

revoke select on SC from public;

（8）收回用户 USER5 对 SC 表的 INSETT 权限。

revoke insert on table SC from USER5 cascade;