数据库3—数据库、表及数据操作-CSDN博客

本文链接：https://blog.csdn.net/m0_62927440/article/details/138671342

1、视图操作

1）在教师表中按工号降序建立聚簇索引Tno_index。

CREATE NONCLUSTERED INDEX Tno_index ON teacher(tno DESC)
2）在学生选课表中按学号升序和课程代码升序建立唯一索引SC_index。

CREATE UNIQUE INDEX SC_index ON SC(sno,cno)
3）删除教师表上的索引Tno_index.。

DROP INDEX Tno_index ON teacher
4)创建包含教师任课信息（工号，姓名，课程名称，上课地点，任课班级）的视图TC_view。

CREATE VIEW TC_view(工号,姓名,课程名称,上课地点,任课班级)

SELECT TC.tno,tname,cname,building,tdept

FROM TC,teacher,course,department

where teacher.tno=TC.tno AND TC.cno=course.cno AND tdept=dno
5)创建包含学生信息（学号，姓名，性别，年龄，专业）的视图Stu_view。

CREATE VIEW Stu_view(学号,姓名,性别,年龄,专业)

SELECT student.sno,sname,ssex,sbirthday,sdept

FROM student
6)创建包含学生成绩信息（学号，平均成绩）的视图SCAVG_view。

CREATE VIEW SCAVG_view(学号,平均成绩)

SELECT sno,avg(grade)

FROM SC

GROUP BY sno
7)利用视图SCAVG_view，创建平均成绩小于60分的学生成绩（姓名，平均成绩）视图SCLow_view。

CREATE VIEW SCLow_view(姓名,平均成绩)

SELECT sname,平均成绩

FROM student,SCAVG_view

WHERE student.sno=学号 AND 平均成绩<60
8）将视图TC_view中的任课班级列删除。

ALTER VIEW TC_view(工号,姓名,课程名称,上课地点)

SELECT TC.tno,tname,cname,building

FROM TC,teacher,course,department

WHERE teacher.tno=TC.tno AND TC.cno=course.cno AND tdept=dno
9）将视图SCLow_view删除。

DROP VIEW SCLow_view;
10）查询视图TC_view中周明老师的任课信息，包括课程名称和上课地点。

SELECT 课程名称,上课地点

FROM TC_view

WHERE 姓名='周明'
11）将视图Stu_view中学号为BX15236的学生的专业改为“电气工程”。

UPDATE Stu_view

SET 专业='电气工程'

WHERE 学号='BX15236'
12）删除学生成绩信息视图SCLow_view中平均成绩小于30分的学生信息。

DELETE

FROM SCLow_view

WHERE 平均成绩<30

2、创建一个BookDB数据库，

set nocount on

set dateformat ymd

use master

if exists (select * from sysdatabases where name='BookDB')

drop database BookDB

create database BookDB

on primary

(name='BookDB_data',

filename='D:\sqlWork\BookDB_data.mdf',

size=5,

maxsize=10,

filegrowth=1)

log on

(name='Booklog',

filename='D:\sqlWork\BookLog.ldf',

size=1,

maxsize=5,

filegrowth=1)

/*data mydatabase*/

use BookDB

/*图书分类表*/

print 'creat table BookClass'

CREATE TABLE BookClass(

classNo char(4) not null, /*图书分类号*/

className varchar(20) not null, /*图书分类名称*/

constraint BookClassPK primary key(classNo)

)

insert BookClass values('C001', '经济类')

insert BookClass values('C002', '外语类')

insert BookClass values('C003', '计算机类')

insert BookClass values('C004', '数学类')

insert BookClass values('C005', '文学类')

/*出版社表*/

print 'creat table Publisher'

CREATE TABLE Publisher(

publisherNo char(4) not null, /*出版社编号*/

publisherName varchar(20) not null, /*出版社名称*/

constraint PublisherPK primary key(publisherNo)

)

insert Publisher values('P001', '中国人民大学出版社')

insert Publisher values('P002', '清华大学出版社')

insert Publisher values('P003', '高等教育出版社')

insert Publisher values('P004', '外语教学与研究出版社')

insert Publisher values('P005', '机械工业出版社')

/*图书表*/

print 'create Book'

create table Book(

bookNo char(10) not null primary key, /*图书编号*/

check(bookNo like '[B][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

classNo char(4) not null, /*分类号*/

bookName varchar(40) not null, /*图书名称*/

authorName varchar(8) not null, /*作者姓名*/

publisherNo char(4) not null, /*出版社号*/

price numeric(7,2) null

check(price between 10 and 80), /*单价*/

publishingDate datetime null, /*出版日期*/

shopDate datetime null, /*入库时间*/

shopNum numeric(3) null, /*入库数量*/

constraint BookFK1 foreign key(classNo) references BookClass(classNo),

constraint BookFK2 foreign key(publisherNo) references Publisher(publisherNo)

)

insert Book values('B201501001', 'C001', '政治经济学', '宋涛', 'P001', 23.50, '20141001', '20150111', 10)

insert Book values('B201601004', 'C001', '微观经济学', '张蕊', 'P001', 30.30, '20150102', '20160112', 8)

insert Book values('B201501003', 'C001', '宏观经济学', '王冰', 'P001', 27.80, '20141001', '20150111', 20)

insert Book values('B201501002', 'C002', '大学英语', '郑树棠', 'P001', 25.20, '20140701', '20160111', 30)

insert Book values('B201601002', 'C002', '大学英语读写实践', '张丽莉', 'P004', 20.80, '20140102', '20150112', 30)

insert Book values('B201506001', 'C002', '大学英语听说', '郑树棠', 'P004', 16.60, '20150601', '20160111', 25)

insert Book values('B201503001', 'C003', '数据库系统原理', '万慧红', 'P002', 22.20, '20140101', '20150111', 8)

insert Book values('B201601001', 'C003', '操作系统原理', '左万力', 'P003', 31.50, '20140102', '20150112', 10)

insert Book values('B201605002', 'C003', '现代操作系统', '陈向群', 'P002', 41.00, '20150202', '20150812', 10)

insert Book values('B201601003', 'C003', '数据结构', '章新雨', 'P002', 18.80, '20150302', '20151212', 5)

insert Book values('B201503002', 'C003', '数据库系统概念', '王珊', 'P005', 28.20, '20140501', '20150111', 5)

insert Book values('B201603001', 'C003', '操作系统教程', '孟静', 'P003', 15.60, '20160102', '20160712', 5)

insert Book values('B201603003', 'C003', '数据结构与算法', '郭树琴', 'P005', 22.00, '20161002', '20161112', 20)

insert Book values('B201603004', 'C005', '古典文学作品欣赏', '刘欣红', 'P001', 21.00, '20141102', '20150112', 20)

insert Book values('B201605001', 'C005', '现代诗歌欣赏', '吴红', 'P001', 18.20, '20150902', '20160412', 30)

/*读者表*/

print 'create Reader'

create table Reader(

readerNo char(8) not null primary key, /*读者编号*/

check(readerNo like '[R][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

readerName varchar(8) not null, /*姓名*/

sex char(2) null /*性别*/

check(sex in('女','男')),

identifycard char(18) unique null, /*身份证号*/

workUnit varchar(50) null, /*工作单位*/

borrowCount tinyint default 0 null,

check(borrowcount<=8) /*可借书数量*/

)

insert Reader values('R2015001','张小娟','女','412723199001014321','统一股份有限公司',null)

insert Reader values('R2015002','刘凤','女','412723199003014321','联合股份有限公司',null)

insert Reader values('R2015003','高代鹏','男','412723199005014321','洪都股份有限公司',null)

insert Reader values('R2014001','陈辉','男','412723199111014321','南昌市电脑研制公司',null)

insert Reader values('R2014002','李虹冰','女','412723199208014321','富士康科技集团',null)

insert Reader values('R2016001','张露','女','412723199002014321','兴隆股份有限公司',null)

insert Reader values('R2016002','喻自强','男','412723199004014321','万事达股份有限公司',null)

insert Reader values('R2016003','张晓梅','女','412723199112014321','世界技术开发公司',null)

insert Reader values('R2016004','张良','男','412723199110014321','上海生物研究室',null)

/*借阅表*/

print 'create Borrow'

create table Borrow(

readerNo char(8) not null, /*读者编号*/

bookNo char(10) not null, /*图书编号*/

borrowDate datetime default getdate() not null, /*借阅日期*/

shouldDate datetime not null, /*应归还日期*/

returnDate datetime null, /*归还日期*/

constraint BorrowPK primary key clustered(readerNo,bookNo,borrowDate),

constraint BorrowFk1 foreign key(readerNo) references Reader(readerNo),

constraint Borrowck check(returndate>borrowdate),

constraint BorrowFk2 foreign key(bookNo) references Book(bookNo)

)

insert Borrow values('R2016001', 'B201501001','20160301','20160401','20161011')

insert Borrow values('R2016001', 'B201601004','20160907','20161007',null)

insert Borrow values('R2016001', 'B201601002','20160308','20160408','20161111')

insert Borrow values('R2016001', 'B201601001','20160507','20160607',null)

insert Borrow values('R2016001', 'B201501001','20160901','20161201',null)

insert Borrow values('R2015001', 'B201503002','20150501','20150701','20151011')

insert Borrow values('R2015001', 'B201601004','20160501','20160601','20161011')

insert Borrow values('R2015001', 'B201501001','20151001','20151101',null)

insert Borrow values('R2015002', 'B201601001','20160301','20160401','20161011')

insert Borrow values('R2015002', 'B201603004','20160907','20161007','20161105')

insert Borrow values('R2015002', 'B201601004','20160307','20160407','20160505')

insert Borrow values('R2015002', 'B201501001','20160407','20160607','20160705')

insert Borrow values('R2015002', 'B201601004','20161107','20161207',null)

insert Borrow values('R2015002', 'B201501003','20150307','20150607','20151005')

insert Borrow values('R2015002', 'B201506001','20150807','20151007','20151105')

insert Borrow values('R2015002', 'B201506001','20160907','20161007','20161205')

insert Borrow values('R2015003', 'B201503002','20160301','20160401','20161011')

insert Borrow values('R2016002', 'B201601002','20160301','20160401','20161011')

insert Borrow values('R2016002', 'B201603004','20160501','20160601','20161011')

insert Borrow values('R2016002', 'B201601004','20160311','20160411','20160811')

insert Borrow values('R2016002', 'B201501001','20160311','20160411','20161011')

insert Borrow values('R2016002', 'B201601001','20160917','20161017','20161105')

insert Borrow values('R2016003', 'B201601001','20160907','20161007',null)

insert Borrow values('R2016003', 'B201503001','20160107','20160207',null)

insert Borrow values('R2016004', 'B201501001','20160907','20161007','20161005')

insert Borrow values('R2016004', 'B201603003','20160507','20160607','20160605')

insert Borrow values('R2016004', 'B201601002','20160207','20160307','20160305')

insert Borrow values('R2016004', 'B201603004','20160520','20160720','20160715')

insert Borrow values('R2016004', 'B201601004','20160307','20160407','20160405')

insert Borrow values('R2016004', 'B201501001','20151007','20151107','20151103')

insert Borrow values('R2016004', 'B201603001','20160407','20160517','20161005')

insert Borrow values('R2016004', 'B201603001','20160920','20161020','20161025')

--insert Borrow values('R2016004', 'B201601001','20161207','20170107',null)

--insert Borrow values('R2016004', 'B201601003','20161207','20170107',null)

--insert Borrow values('R2016004', 'B201503002','20161112','20161212','20161225')

--insert Borrow values('R2016004', 'B201603001','20160417','20160517',null)

--insert Borrow values('R2016004', 'B201603003','20160420','20160520','20160505')

--insert Borrow values('R2016004', 'B201601001','20160317','20160417','20160415')

创建一个BookDB数据库。

创建图书借阅管理相关5张关系表。

（1）根据基本表创建以下索引：

① 在图书表中按出版社号建立一个非聚集索引PublishernoIdx。

CREATE INDEX PublishernoIdx ON Book(bookNo)

② 在读者表中按身份证号建立一个非聚集索引IdentifycardIdx。

CREATE INDEX IdentifycardIdx ON Reader(identifycard)

③ 在读者表中，首先按工作单位的升序，然后按借书数量降序建立一个非聚集索引WorkunitCountIdx。

CREATE INDEX WorkunitCountIdx ON Reader(workUnit,borrowCount DESC)

(2) 创建一个图书名包含“数据”的图书视图BookView。

CREATE VIEW BookView(图书名称,图书编号,图书分类编号,作者,出版编号,价格,出版日期,出售日期,出售数量)
AS
SELECT bookName,bookNo,classNo,authorName,publisherNo,price,publishingDate,shopDate,shopNum

FROM Book

(3) 创建一个包含读者编号、读者姓名、图书编号、图书名称、借阅日期、归还日期的视图BorrowView。

CREATE VIEW BorrowView(读者编号,读者姓名,图书编号,图书名称,借阅日期,归还日期)
AS
SELECT Borrow.readerNo,readerName,Borrow.bookNo,bookName,borrowDate,returnDate
FROM Borrow,Reader,Book
WHERE Borrow.bookNo=Book.bookNo AND Borrow.readerNo=Reader.readerN

(4) 创建一个视图，要求显示至少借阅了3本书的读者信息ReaderView。

CREATE VIEW ReaderView(读者编号,读者姓名,借阅次数)
AS
SELECT Borrow.readerNo,readerName,COUNT(*) 借阅次数
FROM Borrow,Reader
WHERE Borrow.readerNo=Reader.readerNo
GROUP BY Borrow.readerNo,readerName
HAVING COUNT(*)>=3

(5) 在视图BorrowView中查询2016年3月1日以前借阅的图书。

SELECT *
FROM BorrowView
WHERE 借阅日期<='2016-3-1'

(6) 在视图ReaderView中查询姓张的读者信息。

SELECT *
FROM ReaderView
WHERE 读者姓名 like '张%'

(7) 在视图BorrowView基础上再建一个包含合生元有限公司的读者所借图书信息的视图BorrowView1。

CREATE VIEW BorrowView1
AS
SELECT *
FROM BorrowView
WHERE 读者编号 in (
SELECT readerNo
FROM Reader
WHERE workUnit LIKE '合生元%')

(8) 删除视图BorrowView。

DROP VIEW BorrowView