实验五：数据库的视图和图表的定义及使用(20210103123300)

**大学实验报告

2、提高操作实验

将自设计的数据库应用项目中子模式， 用SQL 语句描述其视图定义，并在SQLServer

企业管理器中，用视图创建向导创建这些视图。

1、 写出实现所有操作要求的 SQL 语句。

2、 创建、查看、修改和删除视图。

3、 创建、编辑和删除数据库图表。

--建库 use master go

if exists( select * from sysdatabases where name='ccb' ) drop database ccb go

create database ccb go

--切换数据库 use ccb

go --建图书表

if exists (select * from sysobjects drop table Books

go

create table Books (

BookId varchar (50) primary key , Type varchar ( 50), Press varchar ( 50) ,

Author varchar (50), BookName varchar (50), Price money ); go

--建读者表

实

验 内 容 where n ame ='Books' )

if exists (select drop table Reader go

create table Reader (

from sysobjects where n ame='Reader' )

ReaderId varchar (50) primary key ,

Name varchar ( 50)

Un it varchar ( 50)

Sgender char ( 10)

check ( Sgender ='男'

or Sgender

='女'),

Tel

varchar

(50)

); go

* from sysobjects

where n ame ='Borrow'

drop go

table Borrow

create table Borrow ( BookId

varchar ( 50),

ReaderId varchar ( 50), Date DateTime ,

key ( BookId , ReaderId )

alter table Borrow add constraint FK Borrow Books

foreig n key (BookId )

referen ces Books ( BookId ) alter table

Borrow add con stra int

FK Borrow Reader

foreig n

key

(ReaderId ) refere nces

Reader (ReaderId )

in sert select '200215121'

,'李勇'

,'CS',

'男’, ,'888888888'

union

select '200215122' ,'刘晨'

,'MA', '女'. ,'857988888'

union

select '200215123' ,'王明'

,'MA', '女'. ,'999999999'

union

select '200215124' ,'张力'

,'CS', '女'. ,'999999999'

union

select '200215125'

,'李小'

,'IS',

'男’, ,'999999999'

union

select '200215126' ,'杜绝’

,'IS',

'女'. ,'999999999'

建借阅表 if

exists

(select

primary

into Reader

in sert into Books

select'1001'，'计算机','机械工业岀版社,'张宏','C#编程',85

union

select'1002'，'计算机'，'清华大学出版社,'张宏',' 编程',90

union

select'1003'，'计算机'，'清华大学出版社,'立时','软件设计师’，100

union

select'1004','数学','清华大学出版社’，'张忠’,'高等数学’，50

union

select'1005','数学','北京大学出版社','李叶’,'概率统计’，80

union

select'1006','数学','北京大学出版社','李叶','高等逻辑’，55

union

select'1007','人文','北京大学出版社','李叶’,'马克思’，60

union

select'1008','人文','北京大学出版社','李叶’,'毛泽东思想’，30

union

select'1009','人文','北京大学出版社','好烦’，’邓小平理论',30

union

select'1010'，'地理','复旦大学出版社’，'好烦’,'中国地理’，81

union

select'1011','历史','复旦大学出版社’，'好烦’,'中国历史’，81

in sert into Books

select'1012'，'计算机','机械工业岀版社,'张宏’，’java 编程',88

in sert into Borrow

select'1001','200215124' , '2011.02.03'

union

select'1002','200215126' , '2011.02.03'

union

select'1003','200215122' , '2011.02.03'

union

select'1003','200215125' , '2011.02.03'

union

select'1006','200215126' , '2011.02.03'

创建视图并修改：

create view ghb as

SELECT

dbo . Books . Type , Books . Press , Books . Author , Books . BookName , Books . Price ,dbo . Borrow .* FROM dbo . Books INNER JOIN

dbo . Borrow ON dbo . Books . BookId = dbo . Borrow . BookId INNER JOIN