数据库和表的创建PPT课件

1、1,数据库运用技术,主讲：邵燕林,Geographical Information System,第二章 数据库和表的创建,第二大节(3学时) 教学目标,1. 理解SQL SERVER 2005的逻辑结构，能列举5个以上常用的数据库对象； 2. 了解各系统数据库的作用； 3. 理解数据库的物理结构，掌握主数据文件、辅助数据文件、日志文件、文件组的概念及管理方法； 4. 学习几种创建数据库的方法，并掌握设置数据库文件、属性的方法；,第二章 数据库和表的创建,2.1 数据库逻辑结构 一、数据库对象,从逻辑上看，SQL SERVER 2005数据库由存放数据的表以及支持这些数据的存储、检索、安全性和

2、完整性对象所组成。 SQL SERVER 2005主要包括以下一些对象： 表(Table)：由行列构成的集合，用来存储数据，是最重要的数据库对象； 视图(View)：由表或其他视图导出的虚拟表； 数据类型(Data Type)：定义列和变量的数据类型,第二章 数据库和表的创建,索引(Index)：为加快数据检索，定义的辅助数据结构； 约束（Constrain)：为表中的列定义完整性的规则； 默认值（Default)：为列提供的默认值 存储过程(Procedure)：存放于服务器的预先编译好的一组T-SQL语句； 触发器(Trigger)：是被系统自动执行，以确保数据完整性的一组特殊的存储过程

3、函数(Function):是存放于服务器预编译好的数学函数、算法,5,第二章 数据库和表的创建,二、数据库对象的引用,在T-SQL语句中，数据库对象可使用两种对象名来引用：完全名和部分限定名。 完全限定名是对象的全名，在SQL SERVER 2005上创建的每个对象都有唯一的完全限定名。它包括四个部分：服务器名、数据库名、所有者名和对象名，其格式为： server.database.owner.object 如：NS001.XSBOOK.DBO.XS 即为一个完全限定名,7,第二章 数据库和表的创建,三、系统数据库,安装SQL SERVER 2005，系统将自动创建4个系统数据库(master

4、、 model、 tempdb、msdb）和2个示例数据库（Pubs、 Northwind）。 master：是SQL Server 中最重要的系统数据库，存储着系统表格和环境信息等等“元数据”。该数据库的破坏将导致数据库服务无法启动。 model：是系统创建新数据库时使用的模板数据库。 msdb：时SQL Server Agent用来安排警告、工作以及记录操作时使用的数据库,第二章 数据库和表的创建,tempdb：用来存放SQL SERVER 2005运行期间创建的临时表以及其他数据库对象。 pubs和NorthWind：是SQL SERVER 2005自带的示例数据库,第二章 数据库和表的

5、创建,2.2 数据库物理结构 一、文件,1. 主数据文件(Primary data file)： 它是数据库的关键文件，用于存储数据表、索引、视图、存储过程等数据对象及数据，并包含了数据库的启动信息。每个数据库必须有且仅能有一个主文件，其默认扩展名为.MDF。如：master数据库的主数据文件名为master.mdf,第二章 数据库和表的创建,2. 辅助数据文件(Secondary data file) 如果数据库中数据量非常大，除了存储在主数据文件中，可以将一部分数据存储在辅助数据文件中。辅助文件的默认扩展名 .NDF。根据具体情况，可以创建多个辅助文件，也可以不用辅助文件。 3. 日志文件

6、(log data file) 日志文件用于保存恢复数据库所需的事务日志信息。每个数据库至少有一个日志文件。日志文件的扩展名为.LDF,12,第二章 数据库和表的创建,二、文件组,为了方便管理，可以将多个数据文件组织成一组，称为数据库文件组。可以将文件组中的文件存放在不同的物理磁盘，当对数据进行操作时，SQL SERVER 2005会同时修改这些文件，这可以提高数据库的性能,第二章 数据库和表的创建,第二章 数据库和表的创建,在SQL SERVER 2005中，文件组由以下两种类型： 主文件组(Primary Filegroup)：包含主文件的文件组称为主文件组。在创建数据库时，如果我们没有定

7、义文件组，系统自动建立主文件组，所有的系统表都分配在主文件组中。 用户定义文件组(User-defined Filegroup)：用户定义文件组是指在创建或修改数据库时，用户明确创建的文件组,第二章 数据库和表的创建,TIP： 1. 每个数据库有且仅有一个文件组被指定为默认文件组，当创建数据库对象时，系统将这些对象指派到默认文件组中。 2. 文件或文件组不能被一个以上数据库使用； 3. 日志文件不属于任何文件组,文件组应用实例,主文件组,第二章 数据库和表的创建,2.3 创建和修改数据库,在SQL SERVER 2005中，创建数据库主要有三种方法： 使用向导创建数据库 使用企业管理器创建数据

8、库 使用T-SQL语句创建数据库 【例3.1】创建数据库XSBOOK，初始大小为5MB，最大大小20MB，数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长；所有者是Administrator。假设SQL Server服务已启动，并以Administrator身份登录计算机,第二章 数据库和表的创建,一、利用向导创建数据库,第二章 数据库和表的创建,在欢迎对话框中点击下一步，进入下一设置界面，依次完成每一步设置内容，最后点击【完成】，完成数据库的创建,第二章 数据库和表的创建,输入新建 数据库名称,输入数据库文件名,点击弹出数据文件路径设置对话框

9、,添加辅助数据文件,设置数据文件文件组,点击确定完成建立,二、利用企业管理器创建和修改数据库,1. 利用企业管理器创建数据库,第二章 数据库和表的创建,2.在企业管理器中修改数据库 启动企业管理器，选中要管理的数据库，打开属性对话框。 1）改变数据文件的大小和增长方式,第二章 数据库和表的创建,2）增加、删除数据文件,点击这里,第二章 数据库和表的创建,3）增加、删除文件组 有两种方法：创建新数据文件时直接输入新文件组名；在文件组选项卡中新建文件组。 TIP：删除文件组时，必须先将其中的所有数据文件删除,直接在这里输入新的文件组名,第二章 数据库和表的创建,1. 利用T-SQL语句创建数据库

10、基本语法 ： CREATE DATABASE database_name ON 子句 -指定数据库文件和文件组属性 LOG ON 子句 -指定日志文件属性 COLLATE -指定排序规则 FOR LOAD | FOR ATTACH -加载/附加数据库 1) ON子句 ON PRIMARY ,n | FILEGROUP 文件组名 ,n ,n,数据库名,三、利用T-SQL语句创建和修改数据库,第二章 数据库和表的创建,2) LOG ON子句 LOG ON ,n 3) 文件定义描述语法 := ( Name=逻辑文件名, FileName=操作系统文件名， ,SIZE=size ,MAXSIZE=ma

11、x_size|UNLIMITED ,FILEGROWTH=growth_increament,第二章 数据库和表的创建,TIP：在Transact-SQL语言的命令格式中，用 括起来的内容表示是可选的；，n表示重复前面的内容；用括起来表示在实际编写语句时，用相应的内容替代；用 括起来表示是必选的；类似A|B的格式，表示A和B只能选择一个，不能同时都选,第二章 数据库和表的创建,2. 在查询分析器中利用T-SQL语句创建数据库 【例3.2】使用T-SQL语句，创建一个名为xsbook1的数据库，其初始大小为5MB，最大大小20MB，允许数据库自动增长，增长方式是按10%比例增长；日志文件初始为2

12、MB，最大可增长到5MB，按1MB增长。假设SQL Server服务已启动，并以Administrator身份登录计算机,操作方法：启动查询分析器，并连接到对应的数据库服务器。登录后点击模板选卡，找到Creat Database Basic模板，用鼠标拖拽至T-SQL语句输入窗口，在模板的基础上，创建图中T-SQL语句，并点击分析、执行查询,28,分析、执行查询,第二章 数据库和表的创建,3. 数据库创建实训 【例3.3】 创建一个名为DB的数据库，它有两个数据文件，其中主数据文件为10MB，最大大小为100MB，按10MB增长；一个辅数据文件为10MB，最大大小不限，按10%增长；有2个日志

13、文件，大小均为5MB，最大大小均为50MB，按5MB增长。 【例3.4】在上例的基础上，增加一个用户定义的文件组，名称为testUserGroup，其中包含2个数据文件，都为10MB ，最大大小不限，按10%增长,30,CREATE DATABASE DB ON PRIMARY (NAME = DB_data1, FILENAME = e:Program filesMicrosoft SQL ServerMSSQLdatadb_data1.mdf, SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 10MB), (NAME = DB_data2, FILEN

14、AME = e:Program filesMicrosoft SQL ServerMSSQLdatadb_data2.ndf, SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) LOG ON (NAME = DB2_log1, FILENAME = e:Program filesMicrosoft SQL ServerMSSQLdatadb2_log1.ldf, SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 5MB), (NAME = DB2_log2, FILENAME = e:Program file

15、sMicrosoft SQL ServerMSSQLdatadb2_log2.ldf, SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 5MB) GO,注意，别掉了此处 “ , ” 号,插入下列语句： FILEGROUP testUserGroup (NAME = DB_data3, FILENAME = d:db_data3.ndf, SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%), (NAME = DB_data4, FILENAME = d:db_data4.ndf, SIZE = 10MB, MA

16、XSIZE = UNLIMITED, FILEGROWTH = 10,第二章 数据库和表的创建,4.利用T-SQL语句修改数据库 ALTER DATABASE database_name /*在文件组中增加数据文件*/ ADD FILE ,.n TO FILEGROUP filegroup_name | ADD LOG FILE ,.n -增加日志文件 | REMOVE FILE logical_file_name -删除文件 | ADD FILEGROUP filegroup_name -增加文件组 | REMOVE FILEGROUP filegroup_name -删除文件组 | MOD

17、IFY FILE -更改文件属性 | MODIFY NAME = new_dbname -更改数据库名 | SET ,.n WITH -设置数据库属性 | COLLATE -更改数据库排序规则,第二章 数据库和表的创建,5. T-SQL语句修改数据库文件实训： ALTER DATABASE DB MODIFY FILE (NAME=db_data, Maxsize=UNLIMITED, FILEGROWTH=5MB) GO -第一次修改主数据文件的大小限制 ALTER DATABASE DB ADD FILEGROUP Fgroup GO -第二次修改，为数据库添加文件组,第二章 数据库和表的

18、创建,ALTER DATABASE DB ADD FILE (NAME=db_data2, FILENAME=D:db_dat2, SIZE=10MB, MAXSIZE=30MB, FILEGROWTH=5MB) TO FILEGROUP Fgroup GO -第三次修改，为新文件组中添加数据文件 ALTER DATABASE DB REMOVE FILE db_dat2 GO -第四次修改，移除数据文件db_Dat2 ALTER DATABASE DB RMOVE FILEGROUP Fgroup GO -第五次修改，移除文件组FGroup,第二章 数据库和表的创建,ALTER DATABA

19、SE DB ADD LOG FILE (NAME=db_log2, FILENAME=D:db_Log2, SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=1MB) GO -第六次修改，为数据库添加日志文件 ALTER DATABASE DB MODIFY NAME=HEHE GO -第七次修改，更改数据库名称为HEHE DROP DATABASE HEHE GO -删除数据库HEHE,第二章 数据库和表的创建,第三大节 教学目标,1. 了解数据表的基本概念； 2. 了解E-R图到数据库逻辑结构的转换方法； 3. 了解SQL Server2000支持的数据类型； 4. 掌

20、握界面方式创建、修改表的方法，能按要求在SQL SERVER 2005企业管理器中创建、修改数据表； 5. 掌握命令方式创建、修改数据表的基本语法，能在查询分析器中按要求创建、修改数据表； 6. 掌握数据表浏览和操作的方法，能以界面的方式和命令的方式浏览操纵数据库中数据表的数据。,第二章 数据库和表的创建,2.4 创建和操作表 一、预备知识,1. 数据表基本概念 表是用来存储和操作数据的一组逻辑结构，它由行和列组成，是SQL Server中最主要的数据库对象。 关系数据库利用表来表示实体和实体之间的关系，所以关系数据库中包含了反映实体信息的表和反映实体之间联系的表,第二章 数据库和表的创建,1

21、)表结构：每个数据库包含了若干个表。每个表具有一定的结构，称之为表“型”，所谓表型是指组成表的各列的名称及数据类型，也就是日常表格的“栏目信息”。 2) 表名：每个表都有一个名字，以标识该表。例如上表的名字是XS。 3) 记录：每个表可包含若干行数据，表中的一行称为一个记录(Record)，因此，表是记录的有限集合,第二章 数据库和表的创建,4) 字段：每个记录由若干个数据项（列）构成，构成记录的每个数据项就称为字段(Field)，字段有其数据类型，是该字段的取值类型。 5)关键字：若表中记录的某一字段或字段组合能唯一标识记录，则称该字段或字段组合为候选关键字（Candidate key）。若

22、一个表有多个候选关键字，则选定其中一个为主关键字（Primary key），也称为主键。当一个表仅有唯一的一个候选关键字时，该候选关键字就是主关键字,第二章 数据库和表的创建,2. E-R图的转换 1)实体联系类型,第二章 数据库和表的创建,2) 11联系的E-R图到关系模式的转换,第二章 数据库和表的创建,A. 如果联系不单独对应关系模式，可设计如下关系模式： class（班级编号，院系，专业，人数） monitor（学号，姓名，班级编号） B. 如果联系单独对应关系模式，可设计如下关系模式： class（班级编号，院系，专业，人数） monitor（学号，姓名） belongto（学号，班

23、级编号） TIP: 下横线表示该字段为主码,第二章 数据库和表的创建,3) 1n 联系的E-R图到关系模式的转换,第二章 数据库和表的创建,A. 如果联系不单独对应关系模式，可设计如下关系模式： class（班级编号，院系，专业，人数） student(学号，姓名，年龄，性别，班级编号) B. 如果联系单独对应一关系模式，可设计如下关系模式： class（班级编号，院系，专业，人数） student(学号，姓名，年龄，性别) belongto（学号，班级编号,第二章 数据库和表的创建,4) mn联系的E-R图到关系模式的转换,第二章 数据库和表的创建,每个实体集各对应一个关系模式。 对于(m

24、: n)的联系，单独对应以关系模式，该模式包括联系的属性、参与联系的各实体集的主码属性，该关系模式的主码由各实体集的主码属性共同组成。 根据以上方法步骤，可设计如下关系模式： student(学号，姓名，专业，班级，性别，) course（课程编号，课程名称，课程类别，学分） selection(学号，课程编号，成绩,第二章 数据库和表的创建,5) E-R图转换实训,第二章 数据库和表的创建,3. SQL SERVER 2005数据类型简介,第二章 数据库和表的创建,2.4 创建和操作表 一、创建表,数据表的创建和修改有两种方式： 界面方式（通过企业管理器）； 命令方式（直接通过T-SQL语句

25、）。 1. 界面方式 启动企业管理器，进入要创建新表的数据库，选种表对象，鼠标右击弹出菜单，选取【新建表】，进入表设计器界面,直接输入新字段名称,选择字段新建数据类型,可以设置默认值或绑定默认值对象,设置主键,设计完成后点击保存，保存新建的表,第二章 数据库和表的创建,2. 命令方式 1) 创建表 创建表的基本语法如下： CREATE TABLE table_name column_name datatype |identity|NOT NULL|NULL 创建表的完整语法如下： CREATE TABLE database_name. owner . table_name -表名 ( -列的定

26、义 | column_name AS computed_column_expression -定义计算列 | PRIMARY KEY | UNIQUE -指定主键/唯一键 ,.n ) ON filegroup | DEFAULT -指定存储表的文件组 TEXTIMAGE_ON filegroup | DEFAULT -指定存储长文本、二进制数据的文件组(,第二章 数据库和表的创建,1) column_definition：列定义，其简化构成为 := -列名、类型 column_name datatype -默认值 DEFAULT constant_expression -列为标识列 | IDE

27、NTITY seed，increment -列为全局标识符列() ROWGUIDCOL -指定列的约束() n,第二章 数据库和表的创建,:= () CONSTRAINT constraint_name NULL | NOT NULL | PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY REFERENCES ref_table ( ref_column ) ON DELETE CASCADE | NO ACTION ON

28、 UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( logical_expression ),第二章 数据库和表的创建,2) PRIMARY KEY：即主键，是通过唯一索引对给定的一列或多列强制实体完整性的约束。对于每个表只能创建一个 PRIMARY KEY约束。 3) UNIQUE：是通过唯一索引为给定的一列或多列提供实体完整性的约束。一个表可以有多个UNIQUE约束。 4) ON filegroup | DEFAULT：指定存储表的文件组。如果指定了filegroup，则表将存储在指定的

29、文件组中。数据库中必须存在该文件组。如果指定DEFAULT，或者未指定ON参数，则表存储在默认文件组中,第二章 数据库和表的创建,实例】在当前数据库文件组Secondary上创建表studentInfo（studentID，Name，性别，出生时间，专业名，照片）。要求studentID非空，且为主键；性别非空，默认值为0。 USE testDB CREATE TABLE studentInfo ( studentID int NOT NULL PRIMARY KEY, Name char(8) NOT NULL, 性别 bit NOT NULL DEFAULT(0), 出生时间 dateti

30、me NOT NULL, 专业名 char(24), 照片 image NULL ) ON Secondary GO,55,第二章 数据库和表的创建,2）修改表结构 修改表的语法如下： ALTER TABLE table ALTER COLUMN column_name -修改列 new_data_type ( precision , scale ) COLLATE NULL | NOT NULL | ADD -添加列 ,.n | WITH CHECK | WITH NOCHECK ADD -添加约束 ,.n | DROP -删除列、约束 CONSTRAINT constraint_name

31、| COLUMN column ,.n,第二章 数据库和表的创建,实例】在前面实例创建的表的基础上进行表结构的修改： A. 将字符型（char)字段“专业名”长度更改为36，并且设置该字段为非空； B. 删除字段“照片”； C.添加字段“自传”，设置字段类型为Unicode长文本类型（ntext)，可以为空。 ALTER TABLE studentInfo ALTER COLUMN 专业名 char(36) NOT NULL GO -更改字段 ALTER TABLE studentInfo DROP COLUMN 照片 GO -删除字段 ALTER TABLE studentInfo ADD

32、自传 ntext NULL GO -添加字段,58,第二章 数据库和表的创建,TIP:当表中未有记录时，可以修改表结构；但当表中已经有了记录，建议不要轻易更改表结构，且具有以下特性的字段不能被修改： 具有text、ntext、image或timestamp数据类型的列； 计算列； 全局标识列； 复制列； 用于索引的列 用于生成统计的列 主、外键约束的列 CHECK或UNIQUE约束的列 关联有默认值的列,第二章 数据库和表的创建,3）删除表 删除表的语法如下： DROP TABLE table_name 【实例】删除前面创建的studentInfo表 USE testDB DROP TABLE studentInfo GO,第二章 数据库和表的创建,三、浏览/操作表数据,同样的道理，浏览/操作表数据也有界面方式和命令方式。 1. 企业管理器中浏览/操作表数据（界面方式） 启动企业管理器，进入要修改的表对象，鼠标右击弹出菜单，选取【打开表|返回所有行】