SQLsever数据库第三章创建和操作表.ppt

《SQLsever数据库第三章创建和操作表.ppt》由会员分享，可在线阅读，更多相关《SQLsever数据库第三章创建和操作表.ppt（52页珍藏版）》请在三一办公上搜索。

1、第三章 创建和操作表,数据库的建立和修改数据表的建立加强数据完整性数据表修改与删除SQL视图创建索引掌握内容,数据库的建立和修改,数据库界面直接创建数据库文件关联数据库建立语法：CREATE DATABASE Create Database AppDtaOn Primary(Name=AppDta_data,Filename=d:appdtal.mdf,Size=10MB,MaxSize=100MB,FileGrowth=10MB)Log On(Name=AppDta_Log,Filename=d:appdtalog1.ldf,Size=10MB,MaxSize=100MB,FileGrowt

2、h=10MB),数据库的建立和修改,数据库扩展名说明mdf 数据库主文件ndf 数据库从属文件ldf 事务日志文件数据库说明每个数据库至少有两个文件，一个主文件和一个事务日志文件。从属文件保存主文件容纳不下的数据，可将数据分布在多个磁盘上。事务日志文件保存用来恢复数据库的日志信息。,数据库的建立和修改,数据库对象的标识符标识符包含的字符数必须在1128之间；标识符的第一个字符必须是字母、下划线、符号或#；某些处于标识符开始位置的符号具有特殊意义。标识符的后续字符可以为字母、数字或符号，$符号、数字符号或下划线；如果标识符是保留字或包含空格，则需要使用分割标识符进行处理。SELECT*FROM

3、my table WHERE order=10,数据库的建立和修改,修改数据库语法：ALTER DATABASE Add File|Remove File|Modify File 示例：Alter Database AppDtaAdd File(Name=AppDta2,Fileanme=d:appdta2.ndf,Size=10MB,FileGrowth=10MB)Alter Database AppDtaRemove File AppDta2Alter Database AppDtaModify File(Name=AppDta_data,FileGrowth=50MB)删除数据库DROP

4、 DATABASE,设计数据库表,设计表各列和行的数据类型，达到三级范式要求。决定哪些列允许空值决定是否要在表中使用及何时使用约束、默认设置或规则所需索引的类型，哪里需要索引、哪些列是主键，哪些是外键,设计数据库表,建立数据表用于存放学生信息，包含以下列：学号（ID）、姓名（Name）、性别（Sex）、出生年月日（Bir_day）、出生地（Bir _place）、班级(Class)等。定义数据类型ID（char 10位）、Name（nchar 10位）、Sex（nchar 2位）、Bir_day（datatime 8位）、Bir _place（nvarchar 50位）、Class（nchar

5、 10位)空值？主健？范围？,数据表的建立,基本表的定义create table(列名 数据类型 可选项,列名 数据类型 可选项,constraint,constraint)可选项包括：not null,default 缺省值,identity(种子,增量),unique,primary key,check(表达式),references 表名(列名)Constraint 包括：constraint name unique(列名,)constraint name primary key(列名,)constraint name foreign key(列名,)references 引用表名(列名

6、,)constraint name check(表达式),数据表的建立,示例Create table teach.dbo.student(num int,name char(10),score float);说明指定1 到1024 个列主键约束(可选)1 到250 个Unique 约束(可选)1 到253 个外键约束(可选)1 个或者多个Check 约束限制插入表中的数据(可选)如果使用系统保留字作为表名、列名或其他SQL 对象名称，必须用双引号括起这些名称。Create table student(“create int,name char(10);,加强数据完整性(约束）,NOT NULL

7、（非空）UNIQUE（唯一性）PRIMARY KEY（主键）FOREIGN KEY（外键）CHECK（检查）DEFAULT（默认值）IDENTITY（标识）,加强数据完整性,使用 NOT NULL 约束NULL表示未定义或未知的值。空不等同于零、空白或默认值，空表示值缺少。可以把空看作是一个标识符，表示一种特定状态。在空情况下，如果没有为列提供值，那么其值是未知的，即空的。通过使用一个标明列不接收空值的 NOT NULL约束，可以重写默认空能力的特性。,加强数据完整性,NOT NULL 语法列名 数据类型 NOT NULL示例Create table student(num int not n

8、ull,name char(10)not null,score float);违反NOT NULL出现的问题数据记录不能添加到表中,加强数据完整性,添加UNIQUE约束UNIQUE可以使一列或一组列只包含唯一值。列添加UNIQUE约束后，此列就不能具有相同的两行或多行记录。语法：对一列添加UNIQUE约束列名 数据类型 UNIQUE对多列添加UNIQUE约束CONSTRAINT UNIQUE(列名,),加强数据完整性,UNIQUE 示例Create table student(num int unique,name char(10)not null,score float);Create ta

9、ble student(name char(10),lesson char(20),score float,constraint name_lesson unique(num,name)-,constraint name_lesson unique(name)-,constraint name_lesson unique(lesson);,加强数据完整性,UNIQUE 与 NULL(SQL:99)UNIQUE是防止相同的值输入定义有该约束的列通过NOT NULL 和 UNIQUE 一起使用可以使UNIQUE约束列不存在空值。Create table student(name char(10)n

10、ot null unique,);Create table student(name char(10)not null,contraint un_name unique(name);,加强数据完整性,添加PRIMARY KEY约束和UNIQUE约束一样，是一种SQL唯一约束。PRIMARY KEY约束定义的列不能包含空值，即使没有NOT NULL，该列也必须包含一个数据值。对于每一个表只能定义一个PRIMARY KEY约束，经常使用作列标识。一个表中，即使没有定义UNIQUE约束，也应该包含主键，来确保行的唯一性。,加强数据完整性,PRIMARY KEY语法列名 数据类型 PRIMARY KE

11、YCONSTRAINT PRIMARY KEY(列名,)示例Create table student(num int primary key,name char(10),);Create table student(num int,name char(10),constraint pk_num primary key(num,name);Create table student(num int,name char(10)unique,constraint pk_num primary key(num);,加强数据完整性,PRIMARY KEY约束说明一个表可以定义多个 UNIQUE 约束，而只

12、能定义一个 PRIMARY KEY 约束。允许空值的列上可以定义 UNIQUE 约束，而不能定义 PRIMARY KEY 约束。当为表指定 PRIMARY KEY 约束时，SQL Server通过为主键列创建唯一索引强制数据的唯一性。当在查询中使用主键时，该索引还可用来对数据进行快速访问。,加强数据完整性,添加FOREIGN KEY约束涉及一个表中数据如何与另一个表中数据相联系。有关系的表有目的的联系在一起可以保证数据的完整性。参照完整性防止一个表中的数据操作对另一个表中的数据产生不利影响。,加强数据完整性,在表2中没有列出的课程，就不能被插入到表1中。如果修改表2中的内容，如果在表1中存在相

13、同的值，应该可以预测到这样作的结果。FOREIGN KEY约束可以增强两个表之间的参照完整性。,表1,表2,加强数据完整性,使用 FOREIGN KEY 原则被引用的列必须是被引用表中的候选键。被引用表最常用的是主键。FOREIGN KEY约束可以包括一列或多列。使用FOREIGN KEY约束时，两个表的引用的列数、列的数据类型必须相同，但列名称可以不同。如果在定义FOREIGN KEY约束时没有指定被引用列，那么在被引用表中的主键中定义的列就被当作被引用列。在一表建立外键之前，被引用表必须存在，并且被引用表对应列必须定义UNIQUE或PRIMARY KEY约束。,加强数据完整性,FOREIG

14、N KEY约束语法列名 类型 REFERENCES(引用列)MATCH FULL|PARTIAL|SIMPLE ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION CONSTRAINT FROEIGN KEY(,)REFERENCES(,),加强数据完整性,FOREIGN KEY约束示例Create table 表1(学号 char(5)primary key,课程编号 int REFERENCES 表2);Create table 表1(学号 char(5)primary key,课程编号 int REFERENCES 表2(课程编

15、号);Create table 表1(学号 char(5)primary key,课程编号 int,CONSTRAINT fk_1 FOREIGN KEY(课程编号)REFERENCES 表2(课程编号);,加强数据完整性,FOREIGN KEY约束的MATCH字句(SQL:99)MATCH FULL|PARTIAL|SIMPLE 该字句用于决定如何处理外键列中的空值。如外键列不许可空值，MATCH字句就不能应用。MATCH FULL：所有的引用列要么为空值，要么都不是空值(为被引用列的值)。MATCH PARTIAL：一个或多个引用列可以有空值，剩下的引用列和对应的被引用列相同即可。MATC

16、H SIMPLE：一个或多个引用列如果有空值，那么剩下的引用列可以不包含在对应的被引用列中。是FOREIGN KEY 约束的默认值。,加强数据完整性,被引用表,当前表,(SQL Server 缺省),加强数据完整性,ON DELETE,ON UPDATECASCADE：当被引用列的数据更新或删除时，引用列的数据也将被更新和删除。NO ACTION：当更新或删除被引用列的数据将引起外键违反时，该动作将被禁止执行。建立外键如果未指定 CASCADE和NO ACTION时，SQL Server 缺省使用NO ACTION示例：create table 当前表(编号 int primary key,姓

17、名 char(10),年龄 int,constraint fk foreign key(姓名,年龄)references 被引用表(姓名,年龄)on delete cascade on update cascade);,加强数据完整性,添加CHECK约束CHECK约束限制表中列的取值范围。可以通过任何基于逻辑运算符返回结果 TRUE 或 FALSE 的逻辑（布尔）表达式来创建 CHECK 约束。语法：列约束：列名 数据类型 CHECK(表达式)表约束：CONSTRAINT CHECK(表达式)可对多列进行限制,加强数据完整性,CHECK约束的表达式写法字段是否为空IS NULL 或 IS NO

18、T NULL数字的取值范围：关系、逻辑运算符年龄=5 AND 年龄=30 AND 年龄!=20BETWEEN年龄 BETWEEN 5 AND 30字符取值范围关系、逻辑预算符性别=男 OR 性别=女IN性别 IN(男,女)LIKE,加强数据完整性,CHECK示例Create Table student(num int,name char(10)CHECK(name=张三 or name=李四),score float CHECK(score=0 and score=0);,加强数据完整性,Default约束为数据列定义一个默认值；使用默认值（Default）约束，用户在插入新行时没有显示为列提

19、供数据，系统将默认值赋给该列；默认值（Default）约束所提供的默认值可以为常量、函数、系统函数、空值等；DEFAULT 定义可适用于除定义为 timestamp 或带 IDENTITY 属性的列以外的任何列；每列只能定义一个DEFAULT约束。,加强数据完整性,语法：列约束：列名 数据类型 default 默认值表约束：CONSTRAINT DEFAULT(表达式)示例Create table student(num int default 1,name char(10)default abc,score float,curTime datetime default getdate();,

20、加强数据完整性,IDENTITY 属性在表中创建一个标识列 语法：IDENTITY(seed,increment)Seed：装载到表中的第一个行所使用的值。Increment：增量值.必须同时指定种子和增量，或者二者都不指定。如果二者都未指定，则取默认值(1,1)。示例Create table student(num int identity(1,2),name char(10),score float);,数据表修改与删除,ALTER TABLEALTER TABLE ALTER COLUMN 类型 NULL|NOT NULLALTER TABLE ADD 列名 类型 NULL|NOT NU

21、LL CONSTRAINTALTER TABLE ADD CONSTRAINT ALTER TABLE DROP COLUMN 列名ALTER TABLE DROP CONSTRAINT DROP TALBE,SQL视图,视图是一种类似表的对象，定义在一个或多表上。视图可以看成虚拟表，在其中不包含数据，只是一些对视图的定义语句。使用视图可以定义复杂的查询，并可以把这些查询存储在视图的定义中，每当用到这些查询时，就可以调用视图，不必重新创建查询，提供简化和限制用户访问数据的方法。向用户隐藏数据库的内部结构。一旦创建了该视图就可以像在其他SQL 语句中使用表一样使用该视图。,SQL视图,试图的优点

22、为用户简化查询为用户定制数据简化用户权限的管理导出数据视图的缺点降低系统性能修改限制,SQL视图,过滤数据,方便数据查看,SQL视图,视图的限制在创建或使用视图时，应遵守以下规定：不能在视图上建立触发器和索引。一个视图最多只能有250列。不能基于临时表建立视图，由SELECT INTO语句可建立临时表，在CREATE VIEW语句中不能使用INTO关键字。在CREATE VIEW语句中不能使用联合操作符UNION。在插入或修改数据时，每个INSERT语句和UPDATE语句不能影响一个以上的视图基表。对视图中的计算列不允许使用INSERT语句插入数据。UPDATE语句不能够修改视图的计算列数据，

23、也不允许它修改包含集合的函数和内置函数的视图列。在视图的Text和Image数据类型的列上不允许使用READTEXT和WRITETEXT语句。,SQL视图,视图创建语法CREATE VIEW()AS WITH CHECK OPTION 视图列,必须使用的情况通过某种计算从表中得到，而不是直接从表中复制的列。多个表的列名相同，连接在一起建立在一个视图中需提供列名。不能只给一些列提供名称，而不给其它列提供名称。Query expression 包含查询语句的复杂结构，可以执行大量操作，包括从表中检索数据、计算数据、限制返回的数据类型，或者执行查询表达式支持的大部分其它操作。由 SELECT 语句组

24、成,SQL视图,基本 SELECT 语法SELECT 字段 FROM 表 WHERE 条件示例CREATE VIEW stu AS select 学号,姓名,班级名称 from studentCREATE VIEW 工资库(姓名,工资,工资改变)AS select 姓名,工资,工资*1.15 from 工资库CREATE VIEW lesson_viewAS select 选课.学号,课程.课程名称 from 选课,课程where 选课.课程编号=课程.课程编号,SQL视图,可更新视图视图中无汇总、聚合等函数。视图中的每一列必须与表中的一源列对应。视图中的每一行必须和表中的一源行对应。在视图中

25、进行更新或插入数据，实际上修改的是内在表中的数据，不能违反表中设置的约束。WITH CHECK OPTION 对WHERE条件加限制，防止对视图更新数据后得到的结果不满足更新前的条件。,SQL视图,CREATE VIEW myviewAS select 姓名,工资 from 工资库where 工资800如果对视图进行更新，使工资小于800，那么这些小于800的数据行就不会出现在视图中，没法对其进行进一步更新了。CREATE VIEW myviewAS select 姓名,工资 from 工资库where 工资800 WITH CHECK OPTIONWITH CHECK OPTION 选项将会

26、阻止将工资更新到800以下。出现出错信息，告诉你不能进行这样的修改。,SQL视图,管理试图sp_helptext 视图名修改视图ALTER VIEW()AS WITH CHECK OPTION 示例：ALTER VIEW student_view AS select 学号,姓名 from student,SQL视图,通过视图检索数据SELECT*FROM 通过视图插入数据INSERT INTO VALUES(列值)通过视图删除数据DELETE FROM WHERE 通过视图更改数据UPDATE SET 列1=列值1 WHERE,SQL视图,删除视图DROP VIEW 示例：Drop view

27、lesson_view删除视图，对所引用表的数据没有任何影响。如果视图已被另外的视图所引用，则尽量不要删除该视图，否则另外的视图将无法正常被检索。,创建索引,当指定一个主键或唯一性约束时SQL Server 为基表创建一个内部索引。索引对数据库性能有正面影响和负面影响。简单地说索引可以加速数据检索，但是由于SQL Server 需要花费一些时间修改基表上索引列的内容，减慢了修改索引列的值。由 ntext、text 或 image 数据类型组成的列不能指定为索引列。,创建索引,创建索引的目的加速数据检索加速连接、ORDER BY 和GROUP BY查询优化器依赖于索引起作用强制实施行的唯一性为什

28、么不在每一列创建索引 创建索引要花费时间和占用存储空间减慢了数据修改速度,创建索引,考虑创建索引的列主键上创建索引连接中频繁使用的列（外键）在某一范围内频繁搜索的列和按排序次序频繁检索的列不考虑创建索引的列 很少或从来不在查询中引用的列只有两个或若干个值的列小的表,索引的分类,唯一性索引在表中建立唯一性索引时，组成该索引的字段或字段组合在表中具有唯一值，对于表中的任何两条记录来说，索引键的值都是各不相同的。主键索引在数据库关系图中为表定义一个主键时，将自动创建主键索引，主键索引是唯一索引的特殊类型。聚集索引在聚集索引中，表中各记录的物理顺序与键值的逻辑（索引）顺序相同。,创建索引,语法CREA

29、TE UNIQUE CLUSTERED|NONCLUSTERED INDEX name ON table|view(column ASC|DESC,.n)说明UNIQUE：为表或视图创建唯一索引，不允许存在索引值相同的两行。CLUSTERED：聚簇(聚集)索引，行的物理顺序与索引排序相同，一个表或视图只允许同时有一个聚集索引。SQL Server 建立索引的缺省值为 nonclustered，行的物理排序独立于索引排序。ASC、DESC为索引的升序、降序排列。,创建索引,示例CREATE INDEX idx_name ON student(姓名 ASC);CREATE UNIQUE INDEX

30、 idx_nameON student(姓名 ASC);CREATE CLUSTERED INDEX idx_nameON student(姓名 ASC);CREATE UNIQUE CLUSTERED INDEX idx_nameON student(姓名 ASC);删除索引DROP INDEX 表名.索引名DROP INDEX student.idx_name,掌握内容,数据库操作CREATE DATABASE、ALTER DATABASE、DROP DATABASE表定义与完整性约束create table(列名 数据类型 可选项,列名 数据类型 可选项,constraint,const

31、raint)可选项包括：not null,default 缺省值,identity(种子,增量),unique,primary key,check(表达式),references 表名(列名)Constraint 包括：constraint name unique(列名,)constraint name primary key(列名,)constraint name foreign key(列名,)references 引用表名(列名,)constraint name check(表达式)表修改与删除ALTER TABLE、DROP TABLE视图创建、修改与删除CREATE VIEW、ALTER VIEW、DROP VIEW索引创建与删除CREATE INDEX、DROP INDEX,练习题,建立一表 student，要求：编号，主键，值由数据库维护，10,20,30,40姓名，非空且唯一年龄，范围1060班级，范围 成型081，成型082，成型083，成型084课程名称授课教师录入日期，缺省值为系统日期课程名称和授课教师为外键，对应 lesson建立一表 Lesson,要求：课程名称授课教师课程信息课程名称和授课教师为主键,