本次实验内容让我放在资源里了。
实验二
使用 SQL 语句创建数据库 EDUC。
数据库的参数要求:
数据库名称:EDUC
数据库存储路径:将数据库文件存储在 D:\jxgl\目录中。
其他参数要求:
数据文件的初始大小:10M 最大:60M 按 5%的比例自动增长。
日志文件的初始大小:4M 最大:10M 增量为 1M。
create database Mytest
on
(name = Mytest_data,
filename = 'F:\jxgl\Mytest.mdf',
size = 10,
maxsize = 60,
filegrowth = 5%)
log on
(
name = Mytest_log,
filename = 'F:\jxgl\Mytest.ldf',
size = 4,
maxsize = 10,
filegrowth = 1
)
实验三
在数据库 EDUC 中,创建如下四个表:
class、student、course、sc
- 根据如上表结构用向导创建各表。
create table class
(
ClsNO char(6) primary key,
ClsName Varchar(16) not NULL,
Director Varchar(10),
Specialty Varchar(30)
)
create table student
(
Sno char(8) primary key,
Sname varchar(10) not NULL,
Sex char(2) check(Sex in ('男','女')),
ClsNO char(6),
Saddr varchar(20),
Sage numeric(3,0)check(Sage > 10 and Sage < 30),
Height Decimal(4,2),
Sdept varchar(30),
foreign key(ClsNO) references class(ClsNO)
on delete cascade
on update cascade
)
create table course
(
Cno char(4) primary key,
Cname varchar(16) not NULL,
Cpno char(4),
Ccredit Tinyint,
foreign key (Cpno) references course(Cno)
)
create table sc
(
Sno char(8),
Cno char(4),
Score Numeric(4,1)
primary key(Sno,CNO),
foreign key (Sno) references student(Sno)
on delete cascade
on update cascade,
foreign key (Cno) references course(Cno)
on delete cascade
on update cascade
)
2.用 SSMS 删除所建立的表 Student,Sc,和 Course
3.在查询分析器中用 sql 语句删除表 class
Drop table class
根据如上表结构用 SQL 语句创建各表。
实验四
1. 数据插入操作
insert into class
values('CS01','计算机一班','张宁','计算机应用');
insert into class
values('CS02','计算机二班','王宁','计算机应用');
insert into class
values('MT04','数学四班','陈晨','数学');
insert into class
values('PH08','物理八班','葛格','物理');
insert into class
values('GL01','地理一班','张四','应用地理');
select * from class;
insert into student
values('20170101','王军','男','CS01','下关40#',20,1.76,NULL);
insert into student
values('20170102','李杰','男','CS01','江边路96#',22,1.72,NULL);
insert into student
values('20170306','王彤','男','MT04','中央路94#',19,1.65,NULL);
insert into student
values('20170107','吴雨','女','PH08','莲花小区74#',18,1.60,NULL);
insert into student
values('20190001','唐三','男','GL01','斗罗大陆01#',27,1.83,NULL);
insert into student
values('20190002','霍雨浩','男','CS01','斗罗大陆02#',23,1.79,NULL);
insert into student
values('20190003','唐舞麟','男','CS02','斗罗大陆03#',19,1.81,NULL);
insert into student
values('20190004','蓝轩宇','男','MT04','斗罗大陆04#',17,1.80,NULL);
insert into student
values('20190005','叶凡','男','PH08','遮天#',22,1.78,NULL);
insert into student
values('20190006','石昊','男','PH08','完美世界#',20,1.76,NULL);
insert into student
values('20190007','楚风','男','CS01','圣墟#',21,1.81,NULL);
insert into student
values('20190008','萧炎','男','CS02','斗破苍穹#',16,1.75,NULL);
insert into student
values('20190009','林动','男','MT04','武动乾坤#',18,1.79,NULL);
insert into student
values('20190010','牧尘','男','GL01','大主宰#',22,1.82,NULL);
insert into student
values('20190011','周元','男','PH08','元尊#',21,1.78,NULL);
insert into student
values('20190012','张若尘','男','CS01','万古神帝#',24,1.83,NULL);
insert into student
values('20190013','冯宝宝','女','MT04','一人之下#',18,1.73,NULL);
insert into student
values('20190014','萧黛儿','女','CS02','斗破苍穹#',23,1.75,NULL);
insert into student
values('20190015','楚瑜','女','PH08','扬州路01#',22,1.70,NULL);
insert into student
values('20190016','木婉','女','MT04','南山路03#',18,1.72,NULL);
insert into student
values('20190017','苏洛','女','CS02','合肥路20#',17,1.65,NULL);
select * from student;
insert into course
values('0001','高等数学',NULL,6);
insert into course
values('0002','操作系统',NULL,3);
insert into course
values('0003','计算机基础','0001',3);
insert into course
values('0004','数据结构','0001',4);
insert into course
values('0005','数据库','0004',3);
insert into course
values('0006','离散数学',NULL,4);
insert into course
values('0007','汇编语言',NULL,3);
insert into course
values('0008','线性代数',NULL,3);
insert into course
values('0009','概率论',NULL,3);
insert into course
values('0010','单片机原理',NULL,2);
select * from course
insert into sc
values('20170101','0001',90);
insert into sc
values('20170101','0002',86);
insert into sc
values('20170102','0001',87);
insert into sc
values('20170102','0003',76);
insert into sc
values('20170306','0001',97);
insert into sc
values('20170306','0003',93);
insert into sc
values('20170107','0005',85);
insert into sc
values('20170107','0002',70);
insert into sc
values('20190001','0002',92);
insert into sc
values('20190001','0003',82);
insert into sc
values('20190002','0002',62);
insert into sc
values('20190002','0004',74);
insert into sc
values('20190003','0002',78);
insert into sc
values('20190003','0005',57);
insert into sc
values('20190004','0002',76);
insert into sc
values('20190004','0006',65);
insert into sc
values('20190005','0010',58);
insert into sc
values('20190005','0009',73);
insert into sc
values('20190006','0009',89);
insert into sc
values('20190006','0008',78);
insert into sc
values('20190007','0006',75);
insert into sc
values('20190007','0005',83);
insert into sc
values('20190008','0004',95);
insert into sc
values('20190008','0009',86);
insert into sc
values('20190009','0006',88);
insert into sc
values('20190009','0005',79);
insert into sc
values('20190010','0003',78);
insert into sc
values('20190010','0007',93);
insert into sc
values('20190011','0006',73);
insert into sc
values('20190011','0008',87);
insert into sc
values('20190012','0005',65);
insert into sc
values('20190012','0007',78);
insert into sc
values('20190013','0002',98);
insert into sc
values('20190013','0009',83);
insert into sc
values('20190014','0001',68);
insert into sc
values('20190014','0010',53);
insert into sc
values('20190015','0003',58);
insert into sc
values('20190015','0009',63);
insert into sc
values('20190016','0002',88);
insert into sc
values('20190016','0005',73);
insert into sc
values('20190017','0003',48);
insert into sc
values('20190017','0007',53);
2.数据修改与删除操作
首先在 student 表中插入一条新记录:学号:20171101、姓名:张三、性别:男、年龄:
19、班级编号:‘CS01’。
insert into student(Sno,Sname,Sex,Sage,ClsNo)
values('20171101','张三','男',19,'CS01');
对于 student 表,将所有班级号为‘CS01’的,并且年龄小于 20 岁的学生的班级号改
为‘CS02’。
update student
set ClsNo = 'CS02' where ClsNo = 'Cs01' and Sage < 20
对于 student 表,删掉所有年龄小于 20 岁,并且专业号为‘CS02’的学生的记录。
在查询分析器中完成实验内容。
delete from student where Sage < 20 and ClsNo = 'CS02';
实验五
1)数据库的分离
2)数据库的附加
实验六
- 用 SQL 语句分别建立以下索引
(1) 在 student 表的 Sname 列上建立普通降序索引 Stusname。
(2) 在 course 表的 Cname 列上建立唯一索引 Coucname。
(3) 在 sc 表的 Sno(升序), Cno(升序)和 grade (降序)三列上建立一个普通索引
SCno。
create index Stusname on student(Sname desc);
create unique index Coucname on course(Cname);
create index SCno on sc(Sno asc,Cno asc,score desc);
- 检查创建索引的完成情况。
- 用 SQL 语句删除索引
删除 Student 表的 Stusname 索引。
drop index Stusname on student;