数据库实验1——第一部分

实验要求：

1）创建后面给出的这6个表（20分）。

1)用不同的方法创建约束；2）查看和删除约束；(3）创建、删除默认和规则 (3*5分)

1）掌握主键约束的特点和用法；2）掌握惟一性约束的用法；3）掌握默认约束和默认对象的用法；4）掌握CHECK约束和规则对象的用法；5）掌握利用主键与外键约束实现参照完整性的方法(5*5分)。

1)增加一个字段；2)删除一个字段; 3)增加一个约束; 4)修改字段的数据类型(4*5分)；

1）创建索引；2）重建索引(2*5分)。

1) 创建视图； 2）删除视图(2*5分)。

 

表格请查看具体实验指导书，具体SQL语句书写如下：

create table reader(
    id varchar(30) primary key, --第一个约束，主键
    rname varchar(30) not null, --第二个约束，不能为空
    sex varchar(10), --性别
    birthdate date, --出生日期
    books int not null, --借书量
    department varchar(40), --工作单位
    telephone varchar(30), --电话
    email varchar(30), --电子邮件
);

--向表中插入数据
insert into reader values('29307142', '张晓露', '女', '1989-02-01', 2, '管理信息系', '85860126', 'zxl@163.com');
insert into reader values('36405216', '李阳', '男', '1988-12-26', 1, '航海系', '85860729', 'ly@sina.com.cn');
insert into reader values('28308208', '王新全', '男', '1988-04-25', 1, '人文艺术系', '85860618', 'wxq@yahoo.cn');
insert into reader values('16406236', '张继刚', '男', '1989-08-18', 1, '轮机工程系', '85860913', 'zjg@163.com');
insert into reader values('16406247', '顾一帆', '男', '1981-12-30', 1, '轮机工程系', '85860916', 'gyf@yahoo.cn');

--查询表中的数据
select * from reader;

--更改books这一列的非空约束，改为可以为空
alter table reader alter column books int null;

--删除这一列数据的books的值，更改为Null
update reader set books = null where id='16406247';

--为电子邮件这一列添加默认约束
alter table reader add constraint DF_email default('电子邮件不详') for email;

 接上面：

--删除邮件的默认约束
alter table reader drop constraint DF_email;

 接上面：

--查看约束
exec sp_helpconstraint @objname=reader;

select * from reader;

--向email添加非空约束
alter table reader alter column email varchar(30) not null;

--向reader表中添加一个字段nickname
alter table reader add nickname varchar(40);

--删除表中的nickname字段
alter table reader drop column nickname;

创建第二张表：

create table borrow_return(
    borcode varchar(30), --借书证号
    bookcode varchar(30), --图书编号
    bookstate varchar(20) not null, --借/还
    borrow_date date, --借书日期
    return_date date, --还书日期
    number smallint, --数量
    work_code varchar(30), --工号
    primary key(work_code)
);

select * from borrow_return;

insert into borrow_return (borcode,bookcode,bookstate,borrow_date,return_date,number,work_code) 
values ('29307142','07108667','还','2008-03-28','2008-04-14',1,'002016');

--此时因为work_code是主键，所以报错:违反了 PRIMARY KEY 约束“PK__borrow_r__993FCB3B4C1DD36C”。不能在对象“dbo.borrow_return”中插入重复键。
insert into borrow_return (borcode,bookcode,bookstate,borrow_date,return_date,number,work_code) 
values ('36405216','00000746','还','2008-04-29','2008-05-09',1,'002016');

--于是我们剔除work_code是主键的约束
alter table borrow_return drop constraint PK__borrow_r__993FCB3B4C1DD36C;

--现在再来试一下上面那条插入语句，直接选中点击execute就可以了。显示成功
--接下来继续插入数据

insert into borrow_return (borcode,bookcode,bookstate,borrow_date,number,work_code) 
values ('29307142','99011818','借','2008-04-27',1,'002016');

insert into borrow_return (borcode,bookcode,bookstate,borrow_date,number,work_code) 
values ('36405216','07410802','借','2008-04-27',1,'002018');

insert into borrow_return (borcode,bookcode,bookstate,borrow_date,number,work_code) 
values ('16406236','07410139','借','2008-05-11',1,'002017');

--更改字段名称
exec sp_rename 'borrow_return.borcode','borrow_code';

 接上：

create table book_class(
    class_id varchar(20), --类别号
    class_name varchar(30), --图书类别
);

select * from book_class;
insert into book_class values ('H31', '英语');
insert into book_class values ('I267', '当代作品');
insert into book_class values ('TP312', '程序语言');
insert into book_class values ('TP393', '计算机网络');
insert into book_class values ('U66', '船舶工程');

--对于借还明细表，我们设置图书编号、借书证号和借还状态合在一起是主键
--在这之前先把将要设置为主键的这些值全部修改为不可为空的约束
alter table borrow_return alter column borrow_code varchar(30) not null;
alter table borrow_return alter column bookcode varchar(30) not null;
alter table borrow_return add primary key(borrow_code, bookcode, bookstate);

create table book_operating_detail(
    book_code varchar(30) not null, --图书编号
    book_name varchar(40), --图书名称
    borrow_id varchar(30) not null, --借书证号
    borrow_date date, --借出日期
    return_date date, --归还日期
    store_number int not null, --库存数
    --foreign key (borrow_id) references borrow_return (borrow_code)
);
--显示失败，外键 'book_code' 引用了位于引用表 'borrow_return' 中的无效列 'book_code'。我们先改下
--book_operating_detail中book_code这个字段的名字
alter table borrow_return add foreign key (d_book_code) references book_operating_detail(book_code);
--更改名字
exec sp_rename 'book_operating_detail.book_code','d_book_code';

--这个时候再试一下，直接选中添加外键的那个语句执行。

insert into book_operating_detail (d_book_code, book_name, borrow_id, borrow_date, store_number) 
values ('99011818', '文化苦旅', '29307142', '2008-04-27', 14);

以上代码还存在问题，说：外键 'd_book_code' 引用了位于引用表 'borrow_return' 中的无效列 'd_book_code'。我决定重启再试一下。

终于解决问题，代码如下：

alter table borrow_return add foreign key (bookcode) references book_operating_detail(d_book_code);

select * from borrow_return;
select * from book_operating_detail;

--仍然不行，说明必须把d_book_code设为主键
alter table book_operating_detail add primary key(d_book_code);

insert into book_operating_detail(d_book_code, book_name, borrow_id, borrow_date, store_number)
values ('07410802', '航海英语', '36405216', '2008-04-27', 24);

insert into book_operating_detail(d_book_code, book_name, borrow_id, borrow_date, store_number)
values ('07410298', 'C++程序设计语言', '29307142', '2008-04-28', 14);

insert into book_operating_detail(d_book_code, book_name, borrow_id, borrow_date, store_number)
values ('07410139', '艺海潮音', '28308208', '2008-05-10', 18);

insert into book_operating_detail(d_book_code, book_name, borrow_id, borrow_date, store_number)
values ('07108667', '说话的艺术', '28209203', '2008-05-13', 21);

insert into book_operating_detail(d_book_code, book_name, borrow_id, borrow_date, store_number)
values ('00000746', '计算机网络', '31359236', '2008-04-12', 12);

我们创建外键约束，你必须要清楚这么几个点，它们全是你犯过的错误：

1.要搞清楚是哪个表参照哪个表；

2.表中的字段名不要搞错，不要搞反了；

3.参照表的那个字段必须是主键；

4.以你创建的表为例，你希望在borrow_return这个表中向图书编号添加外键约束，而参照的表是book_operating_detail表的d_book_code，这两个表所拥有的不同数据的个数必须一致！例如，你按照实验指导书上的向book_operating_detail表中添加完数据后，添加外键约束的操作仍然失败的原因是，book_operating_detail表中还缺少两个数据，缺少的这两个数据是已经在borrow_return表中存在的。所以，新增两条数据就可以了。

一鼓作气，把剩下的表创建完毕！

create table staff(
    working_code varchar(30) not null, --工号
    staff_name varchar(30) not null, --姓名
    staff_sex varchar(30), --性别
    birthdate date, --出生日期
    phone varchar(35) not null, --联系电话
    staff_email varchar(30) not null --电子邮件
);

insert into staff values('002016', '周学飞', '男', '1971-05-03', '85860715', 'zxf@163.com');
insert into staff values('002017', '李晓静', '女', '1979-09-15', '85860716', 'lj@163.com');
insert into staff values('002018', '顾彬', '男', '1972-04-25', '85860717', 'gb@yahoo.cn');
insert into staff values('002019', '陈欣', '女', '1968-11-03', '85860718', 'cx@sina.com.cn');

create table book_detail(
    class_number varchar(30) not null, --类别号
    book_code varchar(30) not null, --图书编号
    book_name varchar(30) not null, --图书名称
    author varchar(40) not null, --作者
    publishing_company varchar(60), --出版社
    price int, --定价
    purchase_date date, --购进日期
    purchase_number int, --购入数
    copies int, --复本数
    store int, --库存数
);

insert into book_detail values ('I267', '99011818', '文化苦旅', '余秋雨', '知识出版社', 16, '2000-03-19', 8, 15, 14);
insert into book_detail values ('TP312', '00000476', 'Delphi高级开发指南', '坎图', '电子工业出版社', 80, '2000-03-19', 15, 15, 15);
insert into book_detail values ('U66', '01058589', '船舶制造基础', '杨敏', '国防工业出版社', 19, '2001-07-15', 20, 20, 20);
insert into book_detail values ('I267', '07410139', '艺海潮音', '李叔', '江苏文艺出版社', 19, '2007-04-12', 15, 20, 18);
insert into book_detail values ('TP312', '07410298', 'C++程序设计', '成颖', '东南大学出版社', 38, '2007-05-08', 10, 15, 14);
insert into book_detail values ('H31', '07410802', '航海英语', '陈宏权', '武汉工业大学出版社', 42, '2007-10-20', 25, 25, 24);
insert into book_detail values ('H31', '07108667', '大学英语学习辅导', '姜丽蓉', '北京理工大学出版社', 23.5, '2008-02-06', 25, 25, 25);
insert into book_detail values ('TP393', '07410810', '网络工程实用教程', '汪新民', '北京大学出版社', 34.8, '2008-08-21', 10, 15, 15);

select * from book_detail;

--修改字段的数据类型
alter table book_detail alter column price float;

--修改表中存储的数据
update book_detail set price=23.5 where book_code='07108667';
update book_detail set price=34.8 where book_code='07410810';

 

接上面，我们创建了一张姓的表，在这张表里添加了非聚集性索引，添加了一个新字段：age，也添加了check索引，分别限制年龄和性别。同时还有向新创建的字段里面插入数据，可以用insert吗？让我们拭目以待吧！

create table student (
    id int not null, --学号
    student_name varchar(30), --姓名
    sex varchar(30), --性别
    birthday date, --生日
    class varchar(30) --班级
);

insert into student values(108, '曾华', '男', '1977-09-01', '95033');
insert into student values(105, '匡明', '男', '1975-10-02', '95031');
insert into student values(107, '王丽', '女', '1976-01-23', '95033');
insert into student values(101, '李军', '男', '1976-02-20', '95033');
insert into student values(109, '王芳', '男', '1975-02-10', '95031');
insert into student values(103, '陆军', '男', '1974-06-03', '95031');

select * from student;

--基于姓名创建非聚集索引
create index aa on student(student_name);

--查询这个索引的表
select * from student with (index=aa);

--添加一个字段：年龄
alter table student add age int;

--向新添加的字段age添加年龄索引
alter table student add constraint CK_student check (age >= 0 and age <= 200);

--这个索引的添加失败，原因是因为该表中已经存在CK_student的对象，故无法创建索引，我们试试别的方法
alter table student add constraint CK_student check (sex in ('男', '女'));

--成功了，改个名字就可以了
alter table student add constraint check_student check (sex in ('男', '女'));

--注意这里不能使用insert了，要给age添加值的话必须用update。insert和where是不能搭配使用的！
update student set age=45 where id=108;

显而易见，不能使用insert语句！这种情况下，创建了一个新的字段，如果想向里面添加数据的话，必须要用update语句。而且记住，insert和where是不能连用的！接下来我们继续按照实验指导书上的要求创建完剩下的所有表并添加所有的数据，然后正式开始这一个实验。

接上，重建索引+创建一个视图：

--重建索引
alter index all on student rebuild;

--重建索引后，继续根据索引进行查询，看看能不能正常工作
select * from student with (index=aa);

--创建一个视图
create view test_view 
as
select id from student;

select * from test_view;

--删除视图
drop view test_view;

 

--创建规则
create rule sex_constraint as @sex in ('男','女');
--将规则绑定到student表的sex字段
exec sp_bindrule 'sex_constraint', 'student.sex';

--先解除绑定，再删除规则
exec sp_unbindrule 'student.sex';
--删除规则
drop rule sex_constraint;


--创建默认对象
create default reason as 'Not clear';
--将其与reader表的email字段绑定
exec sp_bindefault 'reason', 'reader.email';

--解除绑定，再删除
exec sp_unbindefault 'reader.email';
--删除默认对象
drop default reason;

 

 接上

--创建唯一性约束
alter table teacher_info add constraint unique_name unique(teacher_id);
--删除唯一性约束
alter table teacher_info drop constraint unique_name;

 

--创建索引
create index aa on student(student_name);
--基于索引查询
select * from student with (index = aa);
--删除索引
drop index aa on student;