第3章 数据库定义与更新

图3-4 借阅表Borrow
9



(3) 表结构的修改，要求： ① 修改图书表结构，要求出版社名称和入库时间不允许 为空。 ② 修改读者表结构，要求读者身份证号不允许为空。
10
3.3 实验五 索引与视图定义

1. 创建索引




[例3.7] 在员工表中按生日建立一个非聚簇索引birthdayIdx。 CREATE NONCLUSTERED INDEX birthdayIdx ON Employee(birthday) [例3.8] 在订单主表中，首先按订单金额的降序，然后按客户编 号的升序建立一个非聚簇索引sumcustIdx。 CREATE INDEX sumcustidx ON OrderMaster(orderSum DESC, customerNo) [例3.9] 在订单主表中按发票号码创建一个唯一性索引 uniqincoiceIdx。 CREATE UNIQUE INDEX uniqincoiceIdx ON OrderMaster(invoiceno) [例3.10] 删除birthdayIdx索引 DROP INDEX birthdayIdx
图3-1 图书分类表BookClass
7
图3-2 图书表Book
属性名 bookNo classNo bookName authorName publishingName publishingNo price publishingDate shopDate shopNum
类型 char(10) char(3) varchar(40) varchar(8) varchar(20) char(17) numeric(7, 2) datetime datetime numeric(3)
18
3.3.3 实验六实验内容




根据BookDB中4张关系表，完成以下更新操作： (1) 分别给这4张表添加信息，要求图书分类表、图书表、读者表各 插入5个元组，借阅表插入20个元组。 (2) 将联合股份有限公司的读者工作单位修改为联合立华股份有限公 司。 (3) 将入库数量最多的图书单价下调5%。 (4) 将“经济类”的图书单价提高10%。 (5) 将借阅次数高于2次的图书数量增加50%。 (6) 将富士康科技集团读者的借书期限延长至3个月。 (7) 根据借阅表修改每个读者的借书数量。 (8) 删除价格超过50元的图书借阅信息。 (9) 删除借阅了大学英语的借阅记录。 (10) 删除从未借过书的读者。
3

创建索引

语法：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON {TABLE | VIEW} (column [ASC | DESC] [, ...n]) [ON filegroup]



[例3.14] 删除视图emp_view

14
3.2.3 实验五实验内容
ຫໍສະໝຸດ (1) 根据基本表创建以下索引： ① 在图书表中按出版社号建立一个非聚集索引PublishingnoIdx。 ② 在读者表中按身份证号建立一个非聚集索引IdentifycardIdx。 ③ 在读者表中，首先按工作单位的升序，然后按借书数量降序建立一 个非聚集索引WorkunitCountIdx。 (2) 创建一个只含清华大学出版社信息的图书视图BookView。 (3) 创建一个包含读者编号、读者姓名、图书编号、图书名称、借阅日期、 归还日期的视图BorrowView。 (4) 创建一个视图，要求显示至少借阅了3本书的读者信息ReaderView。 (5) 在视图BorrowView中查询2010年4月22日以前借阅的图书。 (6) 在视图ReaderView中查询姓张的读者信息。 (7) 在视图BorrowView基础上再建一个只包含合生元有限公司读者所借图书 信息的视图BorrowView1。 (8) 删除视图BorrowView。
4
SQL Server中的DML

1. 插入数据

INSERT [INTO] table_name/view_name [(column_list)] VALUES {DEFAULT | NULL | expression} UPDATE table_name/view_name SET column_name = expression | DEFAULT | NULL [ FROM <table_source> [, ...n] ] [ WHERE <search_condition> ] DELETE FROM <table_name/view_name> [WHERE <search_condition>]

2. 修改数据



3. 删除数据

5
3.2 实验四 数据库与数据表定义

3.2.1 实验目的与要求

掌握数据库的建立、删除和修改操作。 理解基本表之间的关系，掌握表结构的建立、修改和删除操作， 创建模式导航图。
6
图3-1 图书分类表BookClass
3.2.3

实验四实验内容
null null null null
性 别 身份证号 工作单位 借书数量
图3-3 读者表Reader 属性名 readerNo bookNo borrowDate shouldDate returnDate 类型 char(8) char(10) datetime datetime datetime 空值约束 not null not null not null not null null 属性含义 读者编号 图书编号 借阅日期 应归还日期 归还日期
• UNIQUE：为表或视图创建唯一索引，聚集索引必须是UNIQUE索引。 • CLUSTERED：创建聚集索引，如果没有指定CLUSTERED，则创建非聚集索引 。 • NONCLUSTERED：创建非聚集索引。 • index_name：索引名，索引名必须遵循标识符规则。 • TABLE：要创建索引的表。 • VIEW：要建立索引的视图的名称。 • column：应用索引的列。 • ON filegrou：在给定的filegroup上创建指定的索引。该文件组必须已经通过 执行 CREATE DATABASE或ALTER DATABASE创建。

INSERT Customer VALUES('C20050001', '统一股份有限公司', '022-3566021', '天津市', '220012') DELETE FROM Employee WHERE year(Birthday)<1950 DELETE FROM OrderDetail WHERE orderNo IN ( SELECT orderNo FROM OrderMaster WHERE salerNo='E2005001' )
17

[例3.16] 删除1950年以前出生的员工记录。


[例3.17] 删除E2005001业务员的订单明细信息。

3.3.2 实验案例

[例3.18] 将客户表中C20050004客户的客户名称改为西 湖商厦，电话改为021-6800000。

UPDATE Customer SET customerName='西湖商厦',Telephone='021-6800000' WHERE customerNo='C20050004'



CREATE TABLE table_name ( column_name1 datetype [CHECK search_condition], column_name2 datetype ...... ∶ ) ON groupfilename
2

其中： • table_name：新表的名称，表名必须符合标识符规则。 • column_name：表中的列名，列名必须符合标识符规则， 并且在表内唯一。 • datetype：列的数据类型。 • ON group filename：指定该表属于哪个文件组

(1) 创建一个BookDB数据库，要求至少一个数据文件和一 个日志文件。 (2) 创建图书管理相关4张关系表，表结构如图3-1至图3-4 所示。
属性名 classNo className 类型 char(3) varchar(20) 空值约束 not null not null 属性含义 图书分类号 图书分类名称
19
空值约束 not null not null not null not null null null null null null null
属性含义 图书编号 分类号 图书名称 作者姓名 出版社名称 出版社编号 单 价 出版日期 入库时间 入库数量
图3-2 图书表Book
8
图3-3 读者表Reader

[例3.19] 在OrderMaster表中找出E2005002业务员的订 单，将这些订单对应的每一项销售商品的单价打8折。


UPDATE OrderDetail SET price=price*0.8 WHERE orderNo IN ( SELECT orderNo FROM OrderMaster WHERE salerNo='E2005002' )

[例3.12] 创建一个视图，要求查询每个员工的订单号、员工编号、员工姓名 、订单金额、发票号码等信息。