SQL-Server-2012数据库技术及应用2.4

1、教学单元2.4第6章 表的创建与操作（ T-SQL ）SQL Server 2012数据库技术及应用案例2-6-1 图书管理表的创建与管理案例2-6-2 图书管理表的操作表的创建与操作 学习导航23使用T-SQL命令方式表的创建与操作 知识框架单元2.4 表的创建与操作（ T-SQL ）能力目标能够根据数据库逻辑设计使用T-SQL创建表能够根据数据库逻辑设计中完整性规则，使用T-SQL定义表的主键、外键和其他约束能够根据数据库应用系统的功能需求使用T-SQL插入、删除数据行以及更新数据能够根据数据库应用系统的功能需求使用T-SQL创建合适的索引能够准确地编辑、清晰地分析T-SQL能够阅读并熟练

2、书写创建和操作表的T-SQL命令和有关参数（英文）4单元2.4 表的创建与操作（ T-SQL ）知识目标使用T-SQL语句创建、修改和删除表的语法与用法使用T-SQL语句插入、删除数据行以及更新数据的语法与用法使用T-SQL创建和删除索引的语法与用法T-SQL表定义与操作有关的英文词汇素质目标通过规范的表定义与操作，培养严谨的科学态度通过以组为单位组织数据，培养良好的完成工作任务、团队合作和良好的沟通能力培养较强的质量意识、经济意识和时间意识，能够按时完成工作任务5案例2 图书管理系统案例2-6-1 图书管理表的创建与管理（T-SQL）案例2-6-2 图书管理表的操作（T-SQL）工作任务6单

3、元2.4 表的创建与操作（ T-SQL ）7使用T-SQL创建与管理表一使用T-SQL操作表二使用T-SQL创建与管理索引三单元2.4 表的创建与操作（ T-SQL ）案例2-6-1 图书管理表的创建与管理根据案例2-3图书管理数据库逻辑设计得到的关系模型，在所创建的数据库“Library”中创建相应的表。工作任务8一、使用T-SQL创建与管理表一、使用T-SQL创建与管理表9使用T-SQL创建表1使用T-SQL修改表2使用T-SQL删除表3（一）使用T-SQL创建表ReaderType(TypeID,Typename,LimitNum,LimitDays) PK：TypeIDReader(R

4、ID,Rname,TypeID,Lendnum) PK：RID FK：TypeIDBook(BID,Bname,Author,Publisher,Price,LentOut) PK：BIDBorrow(RID,BID,LendDate,ReturnDate,SReturnDate)PK：RID+BID+LendDate FK：RID，BID案例2-3 图书管理4个关系模式10功能：在数据库中创建新表，为表定义各列的名称、数据类型和完整性约束等。CREATE TABLE 表名 ( | -见以下进一步的语法说明 ,.n -见以下进一步的语法说明 ,.n )CREATE TABLE11（一）使用T-

5、SQL创建表 的语法列名 数据类型 -列名及其数据类型NULL|NOT NULL-非空值约束，默认为NULLCONSTRAINT 约束名 DEFAULT 常量表达式 -默认值定义，可以为其定义一个约束名称|IDENTITY(标识种子,标识增量) -标识列定义 -见以下进一步的语法说明列定义语法说明12（一）使用T-SQL创建表CONSTRAINT 约束名 -可选项，表示以下约束定义的开始，并给约束一个名称PRIMARY KEY -主键约束|UNIQUE -唯一键约束|FOREIGN KEY REFERENCES 主键表(主键) -外键约束|CHECK(逻辑表达式) -检查约束列级完整性约束13

6、（一）使用T-SQL创建表CONSTRAINT 约束名-表示以下为表约束定义的开始，并可给约束一个名称PRIMARY KEY(列名,.n) -主键约束(一个或多个列组合)|UNIQUE(列名,.n) -唯一键约束(一个或多个列组合)|FOREIGN KEY(列名,.n)REFERENCES 主键表(列名,.n)-外键约束(一个或多个列组合)|CHECK(逻辑表达式) -检查约束(逻辑表达式含有一个或多个列)表级完整性约束14（一）使用T-SQL创建表USE LibraryGOCREATE TABLE ReaderType (TypeID int NOT NULL PRIMARY KEY, -读

7、者类型编号，主键 Typename char(8) NULL, -读者类型名称 LimitNum int NULL, -限借数量 LimitDays int NULL -限借天数 )1.创建读者类型表“ReaderType”15（一）使用T-SQL创建表CREATE TABLE Reader ( /*列定义*/ RID char(10) NOT NULL PRIMARY KEY, -读者编号，主键 Rname char(8) NULL, -读者姓名 TypeID int NULL, -读者类型 Lendnum int NULL, -已借数量 /*表级完整性约束*/ FOREIGN KEY(Ty

8、peID) REFERENCES ReaderType(TypeID) ON DELETE NO ACTION -外键，不级联删除 )2.创建读者表“Reader”16（一）使用T-SQL创建表CREATE TABLE Book (BID char(13) PRIMARY KEY, -图书编号，主键 Bname varchar(42) NULL, -书名 Author varchar(20) NULL, -作者 Publisher varchar(28) NULL, -出版社 Price decimal(7,2) NULL CHECK (Price0), -定价，检查约束 LentOut bi

9、t -是否借出 ) 3.创建图书表“Book”17（一）使用T-SQL创建表CREATE TABLE Borrow(/*列定义*/-更新读者主键表Reader的RID列值时,级联更新外键表（本表）的RID值-删除读者主键表Reader的数据行时,级联删除外键表(本表)关联(RID值相等)的数据行RID char(10) NOT NULL-读者编号FOREIGN KEY REFERENCES Reader(RID) -外键约束ON UPDATE CASCADE -级联更新ON DELETE CASCADE, ) -级联删除4.创建图书借阅表“Borrow”18（一）使用T-SQL创建表-更新图书

10、主键表Book的BID列值时，级联更新外键表（本表）的BID值BID char(13) NOT NULL-图书编号FOREIGN KEY REFERENCES Book(BID) -外键约束ON UPDATE CASCADE, -级联更新4.创建图书借阅表“Borrow”19（一）使用T-SQL创建表-定义借期的默认值为当前日期LendDate date NOT NULL DEFAULT(GETDATE(), -借书日期，默认值定义ReturnDate date NULL,-还书日期SReturnDate date NULL,-应还日期/*表级完整性约束*/PRIMARY KEY(RID,BI

11、D,LendDate) -定义RID+BID+LendDate为主键约束4.创建图书借阅表“Borrow”20（一）使用T-SQL创建表在“Library”数据库中创建的表21（一）使用T-SQL创建表一、使用T-SQL创建与管理表22使用T-SQL创建表1使用T-SQL修改表2使用T-SQL删除表3（二）使用T-SQL修改表修改列属性添加列或约束删除列或约束图书管理表的修改23（二）使用T-SQL修改表ALTER TABLE 表名 (ALTER COLUMN 列名-指定一个要修改的列 数据类型 NULL|NOT NULL -指定修改的数据类型和非空值约束 |ADD |,.n -指定添加一个或

12、多个列定义或表级约束 |DROP CONSTRAINT 约束名,.n-指定删除一个或多个约束 |COLUMN 列名,.n-指定删除一个或多个列 )ALTER TABLE24案例：修改读者表“Book”中“Publisher”的类型varchar(28)”改为“varchar(30)”。代码：USE LibraryGOALTER TABLE Book ALTER COLUMN Publisher varchar(30) NOT NULL -修改列定义1.修改数据类型（与空值约束）25（二）使用T-SQL修改表案例：为表“Reader”添加邮件地址列和检查约束。代码：ALTER TABLE Rea

13、der ADD Email varchar(20) NULL -添加列与其约束 CONSTRAINT Reader_Email CHECK(Email LIKE %)2.添加列或约束26（二）使用T-SQL修改表案例：为表“Book”的主键图书编号“BID”添加（BID LIKE %/%）的检查约束。代码：ALTER TABLE Book ADD CONSTRAINT CK_Book_BID CHECK(BID LIKE %/%) -添加约束2.添加列或约束27（二）使用T-SQL修改表案例： 删除“Reader”中的“Email”的约束和列。代码：ALTER TABLE Reader DRO

14、P CONSTRAINT Reader_Email, -删除约束COLUMN Email -删除列说明：如果列上有约束，必须先删除其上的约束。3.删除列或约束28（二）使用T-SQL修改表一、使用T-SQL创建与管理表29使用T-SQL创建表1使用T-SQL修改表2使用T-SQL删除表3（三）使用T-SQL删除表语法：DROP TABLE 表名案例：先在数据库“Library”中创建一个表“Table1”，然后删除。代码：USE LibraryGODROP TABLE Table1DROP TABLE30单元2.4 表的创建与操作（ T-SQL ）31使用T-SQL创建与管理表一使用T-SQL

15、操作表二使用T-SQL创建与管理索引三二、使用T-SQL操作表案例2-6-2 图书管理表的操作根据图书管理系统的功能需求，对数据库“Library”中所创建的表进行数据操作。工作任务32使用T-SQL插入数据行1使用T-SQL更新数据2使用T-SQL删除数据行333二、使用T-SQL操作表（一）使用T-SQL插入数据行语法：INSERT INTO 表名|视图名 (列名表)VALUES(常量表)1.INSERTVALUES34说明：列名表用于指定该数据表的一列或多列（逗号分隔），顺序可以与表的列顺序不同。在数据表中未被指定的列必须支持空值或者默认值，如果默认值存在，该列使用默认值，否则SQL S

16、erver会尝试为该列补上一个空值。常量表提供向表中插入的数据，如果提供的数据是多个，也必须用逗号分隔。常量表中提供的数据与列名表指定的列名个数、数据类型和顺序必须一致。当常量表中提供的数据与表定义的列在个数和顺序上完全一致时，列名表可以省略。1.INSERTVALUES35（一）使用T-SQL插入数据行案例：为表“ReaderType”插入数据行。代码：INSERT INTO ReaderType VALUES(1,教师,6,90)INSERT INTO ReaderType VALUES(2,职员,4,60)INSERT INTO ReaderType VALUES(3,学生,3,30)（

17、1）插入一行所有列的值。36（一）使用T-SQL插入数据行案例：为表“Reader”插入读者“张子建”的部分信息，用同样的语句添加其他数据行。INSERT Reader(RID,Rname,TypeID) VALUES(2000186010,张子建,1)（2）插入一行部分列的值。37（一）使用T-SQL插入数据行（3）插入表“Book”的数据行38（一）使用T-SQL插入数据行（4）插入表“Borrow”的数据行39（一）使用T-SQL插入数据行语法：INSERT 表名 SELECT子查询说明：SELECT查询子句用于指定源表及其数据，通过SELECT查询生成结果集，并将其插入到INSERT后

18、指定的表中。此语句可以将其他数据源的多行数据插入到现有的表中。必须保证接收新值的表中列的数据类型与源表中相应列的数据类型一致。2.INSERT SELECT40（一）使用T-SQL插入数据行案例：将图书表“Book”中人民邮电出版社出版的图书插入到新建的表“BookPostTel”中。（1）在数据库“Library”中新建表“BookPostTel”。CREATE TABLE BookPostTel (BID char(13) PRIMARY KEY, -图书编号，主键约束 Bname varchar(42) NULL, -书名 Author varchar(20) NULL, -作者 Pub

19、lisher varchar(30) NULL, -出版社 Price decimal(7,2) NULL CHECK (Price0) -定价,检查约束2.INSERT SELECT41（一）使用T-SQL插入数据行（2）将图书表“Book”中人民邮电出版社出版的图书插入到新建的“BookPostTel”表中。INSERT BookPostTelSELECT BID,Bname,Author,Publisher,Price FROM Book WHERE Publisher =人民邮电出版社2.INSERT SELECT42（一）使用T-SQL插入数据行 二、使用T-SQL操作表使用T-SQ

20、L插入数据行1使用T-SQL更新数据2使用T-SQL删除数据行343（二）使用T-SQL更新数据语法：UPDATE 表名SET ,.n WHERE 逻辑表达式功能：对于UPDATE所指定的表，当满足WHERE子句后的条件（逻辑表达式为真）时，SET子句为指定的列名赋予“=”号后表达式的值。UPDATESET44说明： 用WHERE子句指定需要更新的行，如果没有选用WHERE子句，则更新表中所有的行。如果行的更新违反了约束或者更新值是不兼容的数据类型，则取消执行该语句，同时返回错误提示。表达式可以是SELECT子查询语句，将把查询到的结果赋值给相应列名。有关查询语句将在第7章中详细介绍。UPDA

21、TE.SET45（二）使用T-SQL更新数据案例：将读者类型表“ReaderType”中学生的原限借数量3册增加2册，原限借天数30天增加5天。USE LibraryGOUPDATE ReaderTypeSET LimitNum=LimitNum+2,LimitDays=LimitDays+5WHERE Typename=学生1.更新指定行的列值46（二）使用T-SQL更新数据案例：将读者类型表“ReaderType”中所有数据行的限借天数都增加10天。UPDATE ReaderTypeSET LimitDays= LimitDays+102.更新所有行的列值47（二）使用T-SQL更新数据案

22、例：计算读者表“Reader”中已借数量列“Lendnum”值UPDATE ReaderSET Lendnum=-从表“Borrow”中统计出每个读者借书的册数(SELECT COUNT(*) FROM Borrow WHERE ReturnDate IS NULL AND Reader.RID=Borrow.RID)3.更新来自另一个表的查询48（二）使用T-SQL更新数据说明：读者表“Reader”的每位读者的已借数量列“Lendnum”，将得到从借阅表“Borrow”中统计出该读者“Reader. RID =Borrow. RID”借书且尚未归还“ReturnDate IS NULL”的

23、册数“COUNT(*)”。可在下一章学习之后再理解。3.更新来自另一个表的查询49（二）使用T-SQL更新数据 二、使用T-SQL操作表使用T-SQL插入数据行1使用T-SQL更新数据2使用T-SQL删除数据行350（三）使用T-SQL删除数据行语法：DELETE 表名WHERE 逻辑表达式功能：删除表中符合WHERE子句指定条件的数据行。DELETE51案例：删除表“BookPostTel”中作者周慧的图书信息。USE LibraryDELETE BookPostTel WHERE Author=周慧DELETE52（三）使用T-SQL删除数据行案例：删除表“BookPostTel”中所有图书信息。USE LibraryDELETE BookPostTelDELETE53