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

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

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

3、，$符号、数字符号或下划线； 如果标识符是保留字或包含空格，则需要使用分割标识符进行处理。 SELECT * FROM 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 A

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

5、ar 10位）、Name（nchar 10位）、Sex（nchar 2位）、Bir_day（datatime 8位）、Bir _place（nvarchar 50位）、 Class（nchar 10位) 空值？ 主健？ 范围？,数据表的建立,基本表的定义 create table (列名 数据类型 可选项 ,列名 数据类型 可选项, , constraint ,constraint ) 可选项包括：not null , default 缺省值 , identity(种子,增量), unique, primary key , check(表达式) , references 表名(列名) Cons

6、traint 包括：constraint name unique(列名,)constraint name primary key(列名,)constraint name foreign key(列名,) references 引用表名(列名,)constraint name check(表达式),数据表的建立,示例 Create table teach.dbo.student(num int,name char(10),score float); 说明 指定1 到1024 个列 主键约束(可选) 1 到250 个Unique 约束(可选) 1 到253 个外键约束(可选) 1 个或者多个Che

7、ck 约束限制插入表中的数据(可选) 如果使用系统保留字作为表名、列名或其他SQL 对象名称，必须用双引号括起这些名称。Create table student(“create int,name char(10);,加强数据完整性(约束）,NOT NULL（非空） UNIQUE（唯一性） PRIMARY KEY（主键） FOREIGN KEY（外键） CHECK（检查） DEFAULT （默认值） IDENTITY（标识）,加强数据完整性,使用 NOT NULL 约束 NULL表示未定义或未知的值。空不等同于零、空白或默认值，空表示值缺少。 可以把空看作是一个标识符，表示一种特定状态。 在空情

8、况下，如果没有为列提供值，那么其值是未知的，即空的。 通过使用一个标明列不接收空值的 NOT NULL约束，可以重写默认空能力的特性。,加强数据完整性,NOT NULL 语法 列名 数据类型 NOT NULL 示例 Create table student( num int not null, name char(10) not null, score float ); 违反NOT NULL出现的问题 数据记录不能添加到表中,加强数据完整性,添加UNIQUE约束 UNIQUE可以使一列或一组列只包含唯一值。 列添加UNIQUE约束后，此列就不能具有相同的两行或多行记录。 语法： 对一列添加UN

9、IQUE约束列名 数据类型 UNIQUE 对多列添加UNIQUE约束CONSTRAINT UNIQUE (列名,),加强数据完整性,UNIQUE 示例 Create table student( num int unique, name char(10) not null, score float); Create table student( name char(10), lesson char(20), score float, constraint name_lesson unique (num,name) -,constraint name_lesson unique (name) -

10、,constraint name_lesson unique (lesson);,加强数据完整性,UNIQUE 与 NULL (SQL:99) UNIQUE是防止相同的值输入定义有该约束的列 通过NOT NULL 和 UNIQUE 一起使用可以使UNIQUE约束列不存在空值。 Create table student( name char(10) not null unique,); Create table student( name char(10) not null, contraint un_name unique(name);,加强数据完整性,添加PRIMARY KEY约束 和UNI

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

12、( 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 约束，而只能定义一个 PRIMARY KEY 约束。 允许空值的列上可以定义 UNIQUE 约束，而不能定义 PRIMARY KEY 约束。 当为表指定 PRIMARY KEY 约束时，SQL Server

13、通过为主键列创建唯一索引强制数据的唯一性。当在查询中使用主键时，该索引还可用来对数据进行快速访问。,加强数据完整性,添加FOREIGN KEY约束 涉及一个表中数据如何与另一个表中数据相联系。 有关系的表有目的的联系在一起可以保证数据的完整性。 参照完整性防止一个表中的数据操作对另一个表中的数据产生不利影响。,加强数据完整性,在表2中没有列出的课程，就不能被插入到表1中。 如果修改表2中的内容，如果在表1中存在相同的值，应该可以预测到这样作的结果。 FOREIGN KEY约束可以增强两个表之间的参照完整性。,表1,表2,加强数据完整性,使用 FOREIGN KEY 原则 被引用的列必须是被引用

14、表中的候选键。被引用表最常用的是主键。 FOREIGN KEY约束可以包括一列或多列。 使用FOREIGN KEY约束时，两个表的引用的列数、列的数据类型必须相同，但列名称可以不同。 如果在定义FOREIGN KEY约束时没有指定被引用列，那么在被引用表中的主键中定义的列就被当作被引用列。 在一表建立外键之前，被引用表必须存在，并且被引用表对应列必须定义UNIQUE或PRIMARY KEY约束。,加强数据完整性,FOREIGN KEY约束语法 列名 类型 REFERENCES (引用列)MATCH FULL | PARTIAL | SIMPLE ON DELETE CASCADE | NO A

15、CTION 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(课程编号); Create table 表1( 学号 char(5) primary key, 课程编号 int, CONSTRAINT

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

17、OREIGN KEY 约束的默认值。,加强数据完整性,被引用表,当前表,(SQL Server 缺省),加强数据完整性,ON DELETE, ON UPDATE CASCADE：当被引用列的数据更新或删除时，引用列的数据也将被更新和删除。 NO ACTION：当更新或删除被引用列的数据将引起外键违反时，该动作将被禁止执行。 建立外键如果未指定 CASCADE和NO ACTION时，SQL Server 缺省使用NO ACTION 示例： create table 当前表 (编号 int primary key,姓名 char(10),年龄 int, constraint fk foreign

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

19、龄=5 AND 年龄=30 AND 年龄!=20 BETWEEN 年龄 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）约束，用户在插入新行时没有显示为列提

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

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

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

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

24、视图基表。 对视图中的计算列不允许使用INSERT语句插入数据。 UPDATE语句不能够修改视图的计算列数据，也不允许它修改包含集合的函数和内置函数的视图列。 在视图的Text和Image数据类型的列上不允许使用READTEXT和WRITETEXT语句。,SQL视图,视图创建语法 CREATE VIEW ()AS WITH CHECK OPTION 视图列,必须使用的情况 通过某种计算从表中得到，而不是直接从表中复制的列。 多个表的列名相同，连接在一起建立在一个视图中需提供列名。 不能只给一些列提供名称，而不给其它列提供名称。 Query expression 包含查询语句的复杂结构，可以执行

25、大量操作，包括从表中检索数据、计算数据、限制返回的数据类型，或者执行查询表达式支持的大部分其它操作。 由 SELECT 语句组成,SQL视图,基本 SELECT 语法 SELECT 字段 FROM 表 WHERE 条件 示例 CREATE VIEW stu AS select 学号,姓名,班级名称 from student CREATE VIEW 工资库(姓名,工资,工资改变)AS select 姓名,工资,工资*1.15 from 工资库 CREATE VIEW lesson_viewAS select 选课.学号,课程.课程名称 from 选课,课程where 选课.课程编号=课程.课程编

26、号,SQL视图,可更新视图 视图中无汇总、聚合等函数。 视图中的每一列必须与表中的一源列对应。 视图中的每一行必须和表中的一源行对应。 在视图中进行更新或插入数据，实际上修改的是内在表中的数据，不能违反表中设置的约束。 WITH CHECK OPTION 对WHERE条件加限制，防止对视图更新数据后得到的结果不满足更新前的条件。,SQL视图,CREATE VIEW myviewAS select 姓名,工资 from 工资库where 工资800 如果对视图进行更新，使工资小于800，那么这些小于800的数据行就不会出现在视图中，没法对其进行进一步更新了。 CREATE VIEW myview

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

28、删除数据 DELETE FROM WHERE 通过视图更改数据 UPDATE SET 列1=列值1 WHERE ,SQL视图,删除视图 DROP VIEW 示例： Drop view lesson_view 删除视图，对所引用表的数据没有任何影响。 如果视图已被另外的视图所引用，则尽量不要删除该视图，否则另外的视图将无法正常被检索。,创建索引,当指定一个主键或唯一性约束时SQL Server 为基表创建一个内部索引。 索引对数据库性能有正面影响和负面影响。简单地说索引可以加速数据检索，但是由于SQL Server 需要花费一些时间修改基表上索引列的内容，减慢了修改索引列的值。 由 ntext、

29、text 或 image 数据类型组成的列不能指定为索引列。,创建索引,创建索引的目的 加速数据检索 加速连接、ORDER BY 和GROUP BY 查询优化器依赖于索引起作用 强制实施行的唯一性 为什么不在每一列创建索引 创建索引要花费时间和占用存储空间 减慢了数据修改速度,创建索引,考虑创建索引的列 主键上创建索引 连接中频繁使用的列（外键） 在某一范围内频繁搜索的列和按排序次序频繁检索的列 不考虑创建索引的列 很少或从来不在查询中引用的列 只有两个或若干个值的列 小的表,索引的分类,唯一性索引 在表中建立唯一性索引时，组成该索引的字段或字段组合在表中具有唯一值，对于表中的任何两条记录来说

30、，索引键的值都是各不相同的。 主键索引 在数据库关系图中为表定义一个主键时，将自动创建主键索引，主键索引是唯一索引的特殊类型。 聚集索引 在聚集索引中，表中各记录的物理顺序与键值的逻辑（索引）顺序相同。,创建索引,语法 CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX name ON table | view ( column ASC | DESC ,.n ) 说明 UNIQUE：为表或视图创建唯一索引，不允许存在索引值相同的两行。 CLUSTERED：聚簇(聚集)索引，行的物理顺序与索引排序相同，一个表或视图只允许同时有一个聚集索引。 SQL Server