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

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,Microsoft,SQL Server 2012,SQL Server 2012,数据库技术及应用,人民邮电出版社,*,教学单元,2.4,第,6,章 表的创建与操作（,T-SQL,）,SQL Server 2012,数据库技术及应用,案例,2-6-1,图书管理表的创建与管理,案例,2-6-2,图书管理表的操作,表的创建与操作 学习,导航,2,3,使用,T-SQL,命令方式,表的创建与操作 知识框架,单元,2.4,表,的创建与操作（,T-SQL,）,能力目标,能够,根据数据库逻辑设计使用,T-SQL,创建,表,能够,根据数据库逻辑设计中完整性规则，使用,T-SQL,定义表的主键、外键和其他,约束,能够,根据数据库应用系统的功能需求使用,T-SQL,插入、删除数据行以及更新,数据,能够,根据数据库应用系统的功能需求使用,T-SQL,创建合适的,索引,能够,准确地编辑、清晰地分析,T-SQL,能够,阅读,并熟练,书写创建和操作表的,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,单元,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,：,TypeID,Reader(RID,Rname,TypeID,Lendnum),PK,：,RID FK,：,TypeID,Book(BID,Bname,Author,Publisher,Price,LentOut),PK,：,BID,Borrow(RID,BID,LendDate,ReturnDate,SReturnDate),PK,：,RID,+,BID+,LendDate FK,：,RID,，,BID,案例,2-3,图书管理,4,个关系模式,10,功能：在数据库中创建新表，为表定义各列的名称、数据类型和完整性约束等。,CREATE TABLE,表名,(,|,-,见以下进一步的语法说明,.,n,-,见以下进一步的语法说明,.,n,),CREATE TABLE,11,（一）使用,T-SQL,创建表,的语法,列名 数据类型,-,列名及其数据类型,NULL|NOT NULL,-,非空值约束，默认为,NULL,CONSTRAINT,约束名,DEFAULT,常量表达式,-,默认值定义，可以为其定义一个约束名,称,|IDENTITY(,标识种子,标识增量,),-,标识列定义,-,见以下进一步的语法说明,列定义语法说明,12,（,一）使用,T-SQL,创建表,CONSTRAINT,约束名,-,可选项，表示以下约束定义的开始，并给约束一个名称,PRIMARY KEY,-,主键约束,|,UNIQUE,-,唯一键约束,|,FOREIGN KEY REFERENCES,主键表,(,主键,),-,外键约束,|,CHECK(,逻辑表达式,),-,检查约束,列级完整性约束,13,（一）使用,T-SQL,创建表,CONSTRAINT,约束名,-,表示以下为表约束定义的开始，并可给约束一个名称,PRIMARY KEY(,列名,.,n,),-,主键约束,(,一个或多个列组合,),|UNIQUE(,列名,.,n,),-,唯一键约束,(,一个或多个列组合,),|FOREIGN KEY(,列名,.,n,)REFERENCES,主键表,(,列名,.,n,),-,外键约束,(,一个或多个列组合,),|CHECK(,逻辑表达式,),-,检查约束,(,逻辑表达式含有一个或多个列,),表级完整性约束,14,（一）使用,T-SQL,创建表,USE Library,GO,CREATE TABLE ReaderType,(TypeID int NOT NULL PRIMARY KEY,-,读者类型编号，主键,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(TypeID)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 bit,-,是否借出,),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,创建表,-,更新图书主键表,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,BID,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,-,指定添加一个或多个列定义或表级约束,|DROP,CONSTRAINT,约束名,.n,-,指定删除一个或多个约束,|COLUMN,列名,.n,-,指定删除一个或多个列,),ALTER TABLE,24,案例：,修改读者表“,Book”,中“,Publisher,”的类型,varchar(28),”改为“,varchar(30),”。,代码：,USE Library,GO,ALTER TABLE Book,ALTER COLUMN,Publisher varchar(30)NOT NULL -,修改列定义,1.,修改数据类型（与空值约束）,25,（二）使用,T-SQL,修改表,案例：,为表“,Reader,”添加邮件地址列和检查约束。,代码：,ALTER TABLE Reader,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,DROP 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 Library,GO,DROP TABLE Table1,DROP TABLE,30,单元,2.4,表的创建与操作,（,T-SQL,）,31,使用,T-SQL,创建与管理表,一,使用,T-SQL,操作表,二,使用,T-SQL,创建与管理索引,三,二、使用,T-SQL,操作表,案例,2-6-2,图书管理表的操作,根据图书管理系统的功能需求，对数据库“,Library”,中所创建的表进行数据操作。,工作任务,32,使用,T-SQL,插入数据行,1,使用,T-SQL,更新数据,2,使用,T-SQL,删除数据行,3,33,二、使用,T-SQL,操作表,（一）使用,T-SQL,插入数据行,语法：,INSERT INTO,表名,|,视图名,(,列名表,),VALUES(,常量表,),1.INSERT,VALUES,34,说明：,列名表用于指定该数据表的一列或多列（逗号分隔），顺序可以与表的列顺序不同。在数据表中未被指定的列必须支持空值或者默认值，如果默认值存在，该列使用默认值，否则,SQL Server,会尝试为该列补上一个空值。,常量表提供向表中插入的数据，如果提供的数据是多个，也必须用逗号分隔。,常量表中提供的数据与列名表指定的列名个数、数据类型和顺序必须一致。,当常量表中提供的数据与表定义的列在个数和顺序上完全一致时，列名表可以省略。,1.INSERT,VALUES,35,（一）使用,T-SQL,插入数据行,案例：,为表“,ReaderType,”插入数据行。,代码：,INSERT INTO ReaderType VALUES(1,教师,6,90),INSERT INTO ReaderType VALUES(2,职员,4,60),INSERT INTO ReaderType VALUES(3,学生,3,30),（,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,后指定的表中。,此语句可以将其他数据源的多行数据插入到现有的表中。必须保证接收新值的表中列的数据类型与源表中相应列的数据类型一致。,2.INSERT,SELECT,40,（一）使用,T-SQL,插入数据行,案例：,将图书表“,Book,”中人民邮电出版社出版的图书插入到新建的表“,BookPostTel,”中。,（,1,）在数据库“,Library,”中新建表“,BookPostTel,”。,CREATE TABLE BookPostTel,(BID char(13)PRIMARY KEY,-,图书编号，主键约束,Bname varchar(42)NULL,-,书名,Author varchar(20)NULL,-,作者,Publisher varchar(30)NULL,-,出版社,Price decimal(7,2)NULL CHECK(Price0),-,定价,检查约束,2.INSERT,SELECT,41,（一）使用,T-SQL,插入数据行,（,2,）将图书表“,Book,”中人民邮电出版社出版的图书插入到新建的“,BookPostTel,”表中。,INSERT BookPostTel,SELECT BID,Bname,Author,Publisher,Price,FROM Book,WHERE Publisher=,人民邮电出版社,2.INSERT,SELECT,42,（一）使用,T-SQL,插入数据行,二、,使用,T-SQL,操作表,使用,T-SQL,插入数据行,1,使用,T-SQL,更新数据,2,使用,T-SQL,删除数据行,3,43,（二）使用,T-SQL,更新数据,语法：,UPDATE,表名,SET,.,n,WHERE,逻辑表达式,功能：,对于,UPDATE,所指定的表，当满足,WHERE,子句后的条件（逻辑表达式为真）时，,SET,子句为指定的列名赋予“,=,”号后表达式的值。,UPDATE,SET,44,说明：,用,WHERE,子句指定需要更新的行，如果没有选用,WHERE,子句，则更新表中所有的行。,如果行的更新违反了约束或者更新值是不兼容的数据类型，则取消执行该语句，同时返回错误提示。,表达式可以是,SELECT,子查询语句，将把查询到的结果赋值给相应列名。有关查询语句将在第,7,章中详细介绍。,UPDATE.SET,45,（二）使用,T-SQL,更新数据,案例：,将读者类型表“,ReaderType,”中学生的原限借数量,3,册增加,2,册，原限借天数,30,天增加,5,天。,USE Library,GO,UPDATE ReaderType,SET LimitNum=LimitNum+2,LimitDays=LimitDays+5,WHERE Typename=,学生,1.,更新指定行的列值,46,（二）使用,T-SQL,更新数据,案例：,将读者类型表“,ReaderType,”中所有数据行的限借天数都增加,10,天。,UPDATE ReaderType,SET LimitDays=LimitDays+10,2.,更新所有行的列值,47,（二）使用,T-SQL,更新数据,案例：,计算读者表“,Reader,”中已借数量列“,Lendnum,”值,UPDATE Reader,SET,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,”的册数“,COUNT(*),”。,可在下一章学习之后再理解。,3.,更新来自另一个表的查询,49,（二）使用,T-SQL,更新数据,二、,使用,T-SQL,操作表,使用,T-SQL,插入数据行,1,使用,T-SQL,更新数据,2,使用,T-SQL,删除数据行,3,50,（三）使用,T-SQL,删除数据行,语法：,DELETE,表名,WHERE,逻辑表达式,功能：,删除表中符合,WHERE,子句指定条件的数据行。,DELETE,51,案例：,删除表“,BookPostTel,”中作者周慧的图书信息。,USE Library,DELETE BookPostTel WHERE Author=,周慧,DELETE,52,（三）使用,T-SQL,删除数据行,案例：,删除表“,BookPostTel,”中所有图书信息。,USE Library,DELETE BookPostTel,DELETE,53,（三）使用,T-SQL,删除数据行,单元,2.4,表的创建与操作,（,T-SQL,）,54,使用,T-SQL,创建与管理表,一,使用,T-SQL,操作表,二,使用,T-SQL,创建与管理索引,三,三、使用,T-SQL,创建与管理索引,使用,T-SQL,创建索引,1,使用,T-SQL,删除索引,2,55,（一）使用,T-SQL,创建索引,语法：,CREATE UNIQUECLUSTERED|NONCLUSTERED,INDEX,索引名,ON,（列名,ASC|DESC,n,）,功能：,为指定的表或视图按照指定的列（索引键）、升序（,ASC,）、降序（,DESC,）创建唯一、聚集或非聚集索引。,CREATE INDEX ON,56,说明：,UNIQUE,：指定唯一索引。,CLUSTERED|NONCLUSTERED,：指定聚集索引或非聚集索引。,ASC|DESC,：确定索引列升序或降序，默认为升序（,ASC,）。,CREATE INDEX ON,57,（一）使用,T-SQL,创建索引,案例：,为了提高依据图书名查找图书的速度，在数据库“,Library,”中为图书表“,Book,”创建一个不唯一、非聚集索引“,Bookindex,”，索引键为“,Bname,”，升序排列。,代码：,USE Library,GO,CREATE NONCLUSTERED,INDEX Bookindex,ON Book(Bname ASC),CREATE INDEX ON,58,（一）使用,T-SQL,创,建索引,CREATE INDEX ON,59,（一）使用,T-SQL,创建索引,（二）使用,T-SQL,删除索引,语法：,DROP INDEX,表名,.,索引名,案例：,删除表“,Book,”的索引“,Bookindex,”。,USE Library,GO,DROP INDEX Book.Bookindex,DROP INDEX,60,表的创建与操作（,T-SQL,）小结,61,Thank You!,SQL Server 2012,数据库技术及应用,