数据库和表的创建(全).ppt

《数据库和表的创建(全).ppt》由会员分享，可在线阅读，更多相关《数据库和表的创建(全).ppt（113页珍藏版）》请在三一办公上搜索。

1、第8章 数据库和表的创建,【本章导读】,本章主要讲解SQL Server 2005数据库和表的结构、设计、创建和管理，介绍SQL Server 2005提供的数据类型以及数据的插入、修改和删除操作。,【本章要点】,数据库的基本结构数据库的创建和管理表的结构SQL Server 2005的系统数据类型表的创建和管理数据的插入、修改和删除,第8章 数据库和表的创建,8.1 数据库的创建8.2 数据表的创建8.3 数据更新本章小结习题五,8.1 数据库的创建,8.1 数据库的创建,8.1.1 数据库的结构8.1.2 系统数据库8.1.3 创建数据库8.1.4 查看数据库信息8.1.5 修改数据库8.

2、1.6 删除数据库,8.1.1 数据库的结构,SQL Server 2000用文件来存放数据库，即将数据库映射到操作系统文件上。数据库文件有三类：主数据文件。数据库文件将被存放在系统的默认存储路径上。SQL Server 2000的默认存储路径是“C:Program FilesMicrosoft SQL ServerMSSQLData”。,主数据文件,Primary Database File，也称主文件。主数据文件主要用来存储数据库的启动信息、部分或全部数据，是数据库的关键文件。主数据文件是数据库的起点，包含指向数据库中其他文件的指针。每个数据库都有一个主数据文件。主数据文件的推荐文件扩展名

3、是.mdf。,次要数据文件,Secondary Database File，也称辅助数据文件。次要数据文件用于存储主数据文件中未存储的剩余数据和数据库对象。一个数据库可以没有，也可以有多个次要数据文件。次要数据文件的推荐文件扩展名是.ndf。,事务日志文件,简称日志文件，存放用来恢复数据库所需的事务日志信息。每个数据库必须有一个或多个日志文件。事务日志的推荐文件扩展名是.ldf。,逻辑文件名和物理文件名,逻辑文件名是在所有 T-SQL 语句中引用物理文件时所使用的名称。逻辑文件名与物理文件名一一对应，其对应关系由SQL Server系统维护。逻辑文件名必须符合 SQL Server的 标识符命

4、名规则，而且在数据库中的逻辑文件名必须是惟一的。物理文件名是包括目录路径的物理文件名。它必须符合操作系统文件的命名规则。,文件组,SQL Server 2005中提供了两种类型的文件组：主文件组和用户定义文件组。主文件组包含主数据文件和任何没有明确分配给其他文件组的数据文件。,规则：,（1）一个文件和文件组只能被一个数据库使用。（2）一个文件只能属于一个文件组。（3）日志文件不能属于文件组。,数据库对象,表（Table）、视图（View）、存储过程（Stored procedures）、触发器（Triggers）、用户定义数据类型（User-defined data types）、用户自定义函

5、数（User-defined functions）、索引（Indexes）、规则（rules）、默认值（Defaults）等。,在SQL Server 2005中创建的每个对象都必须有一个惟一的完全限定对象名，即对象的全名。完全限定对象名由四个标识符组成：服务器名、数据库名、所有者名和对象名，各个部分之间由句点“.”连接。格式如下：服务器名.数据库名.所有者名.对象名,使用当前数据库内的对象可以省略完全限定对象名的某部分，省略的部分系统将使用默认值或当前值，如：server.database.object/*省略所有者名称*/server.owner.object/*省略数据库名称*/*省略服

6、务器名称*/serverobject/*省略数据库及所有者名称*/owner.object/*省略服务器及数据库名称*/object/*省略服务器、数据库及所有者名称*/,8.1.2 系统数据库,SQL Server 2005的系统数据库分别是：master数据库model数据库msdb数据库tempdb数据库,1master数据库,master 数据库记录 SQL Server 系统的所有系统级信息。如果 master 数据库不可用，则 SQL Server将无法启动。,2model 数据库,model 数据库是在SQL Server 实例上创建的所有数据库的模板。可以向model数据库中增

7、加其他数据库对象，这些对象都能被以后创建的数据库所继承。,3msdb 数据库,msdb 数据库由SQL Server代理（SQL Server Agent）来计划警报和作业。,4tempdb 数据库,tempdb数据库是连接到 SQL Server 实例的所有用户都可用的全局资源，它保存了所有临时表和临时存储过程。每次启动 SQL Server 时，都要重新创建 tempdb，以便系统启动时，该数据库总是空的。在断开连接时，系统会自动删除临时表和存储过程，并且在系统关闭后没有活动连接。,8.1.3 创建数据库,使用企业管理器创建数据库：参见录像；使用T-SQL语句创建数据库。,使用T-SQL语

8、句创建数据库,CREATE DATABASE database_name ON,n,n LOG ON,n COLLATE FOR LOAD|FOR ATTACH,:=PRIMARY(NAME=逻辑文件名,FILENAME=存放数据库的物理路径和文件名,SIZE=数据文件的初始大小,MAXSIZE=指定文件的最大大小,FILEGROWTH=指出文件每次的增量）,项用以定义用户文件组及其文件。格式如下：:=FILEGROUP 文件组名,【例5-1】创建一个名为BookSys的数据库。CREATE DATABASE BookSys,命名主数据文件为BookSys.mdf，事务日志文件名为BookSy

9、s_log.log,【例5-2】创建一个名为KEJI_DB的数据库。要求有三个文件，其中，主数据文件为10MB，最大大小为50MB，每次增长20%；辅助数据文件属于文件组Fgroup，文件为10MB，大小不受限制，每次增长10%；事务日志文件大小为20MB，最大100MB，每次增长10MB。文件存储在“c:db”路径下。,CREATE DATABASE KEJI_DB/*数据库名*/ON PRIMARY/*主文件组*/(NAME=KEJI_DB_Data1,/*主文件逻辑名称*/FILENAME=c:dbKEJI_DB_Data1.mdf,/*主文件物理名称*/SIZE=10mb,MAXSIZ

10、E=50mb,FILEGROWTH=20%),FILEGROUP Fgroup/*文件组*/(NAME=KEJI_DB_Data2,/*主文件逻辑名称*/FILENAME=c:db KEJI_DB_Data2.ndf,/*主文件物理名称*/MAXSIZE=UNLIMITED,/*增长不受限制*/SIZE=10Mb,FILEGROWTH=10mb)LOG ON(NAME=KEJI_DB_Log,/*日志文件逻辑名称*/FILENAME=c:db KEJI_DB_Log.ldf,/*日志文件物理名称*/SIZE=20mb,MAXSIZE=100mb,FILEGROWTH=10mb),8.1.4 查

11、看数据库信息,sp_helpdb 数据库名使用系统存储过程sp_helpdb查看数据库信息。不指定数据库参数，将显示服务器中所有数据库的信息。指定具体数据库参数，将显示指定数据库的信息。,8.1.4 查看数据库信息,sp_databases此命令将显示服务器中所有可以使用的数据库的信息。,8.1.4 查看数据库信息,sp_helpfile 文件名查看数据库中文件的信息 不指定文件名参数，将显示当前数据库中所有文件的信息。指定具体文件名参数，将显示数据库中指定文件的信息。,8.1.4 查看数据库信息,sp_helpfilegroup 文件组名 不指定文件组名参数,将显示数据库中所有文件组的信息。

12、指定具体文件组名参数，将显示数据库中指定文件组的信息。,8.1.5 修改数据库,使用SQL Server Management Studio修改数据库：参见录像文件；使用T-SQL语句修改数据库,使用T-SQL语句修改数据库,ALTER DATABASE database_name ADD FILE,nTO FILEGROUP filegroup_name/*在文件组中增加数据文件*/|ADD LOG FILE,n/*增加事务日志文件*/|REMOVE FILE logical_file_name/*删除数据文件*/|ADD FILEGROUP filegroup_name/*增加文件组*/|

13、REMOVE FILEGROUP filegroup_name/*删除文件组*/|MODIFY FILE/*修改文件属性*/|MODIFY NAME=new_dbname/*更新数据库名称*/,【例5-3】为KEJI_DB数据库增加一个数据文件KEJI_DB_Data3，物理文件名为KEJI_DB_Data3.ndf，初始大小为5MB，最大大小为50MB，每次扩展1MB。ALTER DATABASE KEJI_DB ADD FILE(NAME=KEJI_DB_Data3,FILENAME=c:dbKEJI_DB_Data3.ndf,SIZE=5MB,MAXSIZE=50MB,FILEGROWT

14、H=1MB),【例5-4】将KEJI_DB数据库的第二个数据文件KEJI_DB_data2的初始大小修改为40MB。ALTER DATABASE KEJI_DB MODIFY FILE(NAME=KEJI_DB_data2,SIZE=40MB),【例5-5】删除KEJI_DB的数据文件KEJI_DB_Data3。ALTER DATABASE KEJI_DBREMOVE FILE KEJI_DB _Data3,【例5-6】使用ALTER DATABASE语句修改数据库选项，将Study数据库的自动缩减选项设置为TRUE。ALTER DATABASE Study SET AUTO_SHRINK O

15、N【例5-7】使用系统存储过程sp_dboption修改数据库选项，将Study数据库的自动缩减选项设置为TRUE。Sp_dboption Study,autoshrink,true,8.1.6 删除数据库,使用SQL Server Management Studio删除数据库：参见录像文件；使用T-SQL语句删除数据库 DROP DATABASE database,n,删除数据库时，请注意如下情况：（1）如果数据库涉及日志传送操作，请在删除数据库之前取消日志传送操作。（2）若要删除为事务复制发布的数据库，或删除为合并复制发布或订阅的数据库，必须首先从数据库中删除复制。（3）如果数据库已损坏，

16、不能删除复制，则可以首先使用 ALTER DATABASE 将数据库设置为脱机，然后再删除数据库。（4）不能删除系统数据库。（5）删除数据库后，应备份 master 数据库。,8.2 数据表的创建,8.2 数据表的创建,8.2.1 数据类型8.2.2 创建表结构8.2.3 查看表结构8.2.4 修改表结构8.2.5 删除表结构,8.2.1 数据类型,表是用来存储数据和操作数据的逻辑结构。关系数据库中的所有数据都存储在表中，因此表是SQL Server 数据库最重要的组成部分。,1.整数数据类型,数字数据类型,decimal/numeric可以指定精度小数点两边的总位数 P 刻度小数点右面的位数

17、 S,例1 如指定某列的精度为6，小数位数为3，即decimal(6,3)，则向某记录的该例附值86.436876，那么该列实际存储的是：86.4368。如指定某列的精度为8，小数位数为3，即decimal(8,3)，则向某记录的该例附值12685.564986，那么该列实际存储的是：12685.564。,注意：数值型数据，其小数位数必须小于精度；,浮点数据类型,real/float可以存储范围非常大的数字但是不能保证精度,real型数据范围从-3.40E+381.79E+38，存储时使用4个字节，精度可以达到7位。float型数据范围从-1.79E+381.79E+38。利用float 来表

18、明变量和表列时可以指定用来存储按科学记数法记录的数据尾数的bit数。如float(n)，n的范围是153。当n的取值为124时，float型数据可以达到的精度是7位，用4个字节来存储。当n的取值范围时2553时，float型数据可以达到的精度是15位，用8个字节来存储。,2.字符数据类型,SQL Server提供了3类字符数据类型：charvarchartext,8000个英文或者4000个汉字,最大长度可以达到231-1个字符，约2GB,字符数据类型,char定长字符串，应给定最大长度给定字符串短于最大长度，补以空格给定字符串长于最大长度，截掉多余字符,字符数据类型,varchar变长字符串

19、，应给定最大长度给定字符串短于最大长度，不补以空格给定字符串长于最大长度，截掉多余字符,字符数据类型,text存储数量庞大的变长字符数据,3.日期/时间数据类型,datetimesmalldatetime用户可以使用GETDATE()函数来得到系统时间使用SET DATEFORMAT命令设置日期格式,【例5-10】设置日期格式为“月-日-年”。SET DATEFORMAT MDY本例设置的日期格式为“月-日-年”。M表示月，D表示日，Y表示年。常用的日期格式如下：年年月日月-日-年月/日/年年-月-日系统默认的日期格式是：年-月-日。,4.货币数据类型,向表中添加数据的时候，可在最高位前放$或

20、其他货币单位的记号，但是没有严格要求。,5.二进制数据类型,一些用十六进制来表示的数据。插入数据时在数据前加0 x标识。Binary:固定长度，最长8K字节。Varbinary：其定义形式为varbinary(n)，数据的存储长度是变化的，它为实际所输入数据的长度加上4字节。其它含义同binary。Image：用于存储照片、目录图片或者图画，其理论容量为231-1（2，147，483，647）个字节。,6.双字节数据类型,Nchar：其定义形式为nchar（n），最大长度为4000。Nvarchar：其定义形式为nvarchar（n），最大长度为4000。Ntext：与text数据类型类似，存

21、储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。,7图像、文本数据的使用,字符用两个字节存储，不论英文还是汉字ncharnvarcharntext,最多4000个字符,在SQL Server7.0以前的版本中，文本和图像数据都是这样与表中的其他数据分开存储的。SQL Server 2000提供了将小型的文本和图像数据在行中存储的功能。,为了指定某个表的文本和图像数据在行中存储，需要使用系统存储过程sp_tableoption设置该表的“text in row”选项为TRUE。当指定“text in row”选项时，还可以指定一个文本和图像数据大小的上限值

22、，这个上限值应在24字节到7000字节之间。,当同时满足以下两个条件时，文本和图像数据可以直接存储在行中：（1）文本和图像数据的大小不超过指定的上限值。（2）数据行有足够的空间存放这些数据。,现建立表text_example：create table text_example(bin_1 text,bin_2 ntext)sp_tableoption text_example,text in row,truesp_tableoption text_example,text in row,1000sp_tableoption text_example,text in row,false,8用户定

23、义数据类型及使用,sp_addtype type_name,system_type,NULL|NOT NULL|NONULLsp_droptype type_name,凡是包含了诸如“（）”或“，”等分隔符的系统数据类型，必须使用单引号括起来。,【例5-13】下面的例子创建了两个用户定义数据类型：use modelgoexec sp_addtype telelephone,varchar(24),not nullexec sp_addtype fax,varchar(24),null,8.2.2 创建表结构,在创建表及其对象之前，最好先规划并确定表的下列特征：（1）表要包含的数据的类型；（2）

24、表中的列数，每一列中数据的类型和长度（如果必要）；（3）哪些列允许空值。,Student,Course,Score,表的创建也有两种方式,利用SQL Server Management Studio提供的图形界面创建表：参见录像；使用T-SQL命令创建。,使用T-SQL命令创建,CREATE TABLE table_name（column_name datatype NOT NULL|NULL）,说明：（1）table_name是所创建的表的名称，表名在一个数据库内必须惟一。（2）column_name是列名，列名在一个表内必须惟一。（3）datatype是该列的数据类型，可以使用系统数据类型

25、，也可以使用用户定义数据类型。对于需要给定数据最大长度的类型，在定义时要给出长度。（4）NOT NULL|NULL指示该列可否输入空值，默认可以为空。,【例5-14】在Study数据库上创建课程表Course，成绩表Score。use Studygocreate table Course/*定义课程表Course*/(cno char(10)not null,/*课程号列cno，不能输入空值*/came varchar(40)not null,/*课程名列cname，不能输入空值*/credit char(4),/*学分列Credit*/notes varchar(200)/*备注列Notes

26、*/gocreate table Score/*定义成绩表Score*/(sno char(10)not null,/*学号列sno，不能输入空值*/cno char(10)not null,/*课程号列cno，不能输入空值*/degree tinyint)/*成绩列degree*/go,8.2.3 查看表结构,sp_help 查看表结构，包括表的所有者、类型（系统表还是用户表）、创建时间、表上每一列的名称、数据类型、表上定义的索引以及约束等。【例5-15】查看表Course的基本结构。use Studygoexec sp_help Course,8.2.4 修改表结构,使用SQL Serve

27、r Management Studio修改表使用T-SQL语句实现。,（1）添加列,向表中添加列的语句格式如下：ALTER TABLE 表名ADD 列名 列的描述,（1）添加列,【例5-17】向Student表中添加两个新的列：邮箱Email和电话phone。use Studygoalter table Studentadd Email varchar(20)null,phone char(20)null,可以一次向表中添加多列，各列之间用逗号分开即可。,（2）删除列,删除一列的语句格式为：ALTER TABLE 表名DROP COLUMN 列名,（2）删除列,【例5-18】删除 Studen

28、t表的Email列。use Studygoalter table Studentdrop column email,（3）删除列约束,删除列约束的语句格式为：ALTER TABLE 表名DROP CONSTRAINT 约束名例题：Create table ce(tt int constraint NQ_ce_tt uniqueGoAlter table ceDrop constraint NQ_ce_tt,（4）修改列定义,修改列定义的语句格式为：ALTER TABLE 表名ALTER COLUMN 列名 列的描述,（4）修改列定义,【例5-19】将 Student表的姓名列sname改为最大

29、长度为20的varchar型数据，且不允许空值。alter table Student alter column sname varchar(20)not null,列中没有存放是空值的记录；在列上没有创建索引。,8.2.5 删除表结构,使用SQL Server Management Studio删除表 DROP TABLE 表名需要注意的是DROP TABLE语句不能用来删除系统表。【例5-20】删除Study数据库中的表table1。use Studygodrop table table1,8.3 数据更新,8.3 数据更新,8.3.1 向表中插入数据8.3.2 修改表中数据8.3.3 删

30、除表中数据8.3.4 使用SQL Server Management Studio实现数据操作,8.3.1 向表中插入数据,INSERT语句，可以实现数据的插入操作。INSERT语句的基本语法为：INSERT INTO 表名（列名）VALUES（表达式）,1添加数据到一行中的所有列,当将数据添加到一行中的所有列时，使用VALUES关键字来给出要添加的数据。INSERT语句中无需给出表中的列名，只要在VALUES中给出的数据与用CREATE TABLE定义表时给定的列名顺序、类型和数量均相同即可。,1添加数据到一行中的所有列,【例5-21】向Course表中添加一条记录。use Studygoi

31、nsert into Coursevalues(080110H,数据库原理与应用,3,必修),2添加数据到一行中的部分列,要将数据添加到一行中的部分列时，则必须同时给出要使用的列名以及要赋给这些列的数据。【例5-22】向Student表中添加一条记录。use Studygoinsert into Student(sno,sname,sage,ssex,sbirthday)values(05091101,李明,19,男,1989-1-10),2添加数据到一行中的部分列,注意：（1）输入数据的顺序和数据类型必须与表中列的顺序和数据类型一致。（2）列名与数据必须一一对应，当每列都有数据输入时，列名可

32、以省略，但输入数据的顺序必须与表中列的定义顺序相一致。（3）可以不给全部列赋值，但没有赋值的列必须是可以为空的列。（4）插入字符型和日期型数据时要用单引号引起来。,3添加多行数据,通过在INSERT语句中嵌套子查询，可以将子查询的结果作为批量数据，一次向表中添加多行数据。查询语句将在第六章中讲解，在此只给出一个简单例子。,【例5-23】添加批量数据。创建一个新的数据表stu_temp：create table stu_temp(sno char(10)not null,sname char(20)not null,sage tinyint)go假设Student中已有一批数据，可以从Stude

33、nt表中选择部分数据插入到新表stu_temp中，此处将所有女生的信息插入到新表stu_temp中：insert into stu_tempselect sno,sname,sagefrom Studentwhere ssex=女,8.3.2 修改表中数据,当数据添加到表中后，会经常需要修改，如客户的地址发生了变化，货品库存量的增减等。使用UPDATE语句可以实现数据的修改，其语法格式为：UPDATE 表名 SET 列名=表达式 WHERE 条件,【例5-24】将所有课程的备注信息都改为“必修”。use Studygoupdate Courseset notes=必修,【例5-25】将课程号为

34、“080110H”的课程改为“必修”。use Studygoupdate Courseset notes=必修where cno=080110H,8.3.3 删除表中数据,当数据的添加工作完成以后，随着使用和对数据的修改，表中可能存在着一些无用的数据，这些无用数据不仅会占用空间，还会影响修改和查询数据的速度，所以应及时将它们删除。,1使用DELETE语句删除数据,使用T-SQL中的DELETE语句可以删除数据表中的一个或多个记录。DELETE语句最简单的形式如下：DELETE 表名WHERE 条件,1使用DELETE语句删除数据,【例5-26】删除学号为“05091101”的学生的基本信息。u

35、se Studygodelete Studentwhere sno=05091101,2使用TRUNCATE TABLE语句删除数据,TRUNCATE TABLE语句提供了一种删除表中所有记录的快速方法，因为TRUNCATE TABLE语句不记录日志，只记录整个数据页的释放操作，而DELETE语句对每一行修改都记录日志，所以TRUNCATE TABLE语句总比没有指定条件的DELETE语句快。,2使用TRUNCATE TABLE语句删除数据,【例5-27】删除所有学生的成绩记录。TRUNCATE TABLE Score,8.3.4 使用SQL Server Management Studio实

36、现数据操作,参见录像文件。,本章小结,本章主要讲述了数据库的结构、数据库和表的创建与管理、SQL Server 2005提供的数据类型以及表中数据的添加、修改、删除。通过对本章的学习，读者应能建立和管理数据库和表。熟悉对表中数据的操作，包括插入、修改和删除，并向表中添加一定量的数据，为后面的数据查询做准备。,本章小结,SQL Server 2005用文件来存放数据库，即将数据库映射到操作系统文件上。数据库文件有3类：主数据文件、次要数据文件和事务日志文件。,本章小结,SQL Sever 2005数据库中的数据在逻辑上被组织成一系列对象，当一个用户连接到数据库后，他所看到的是这些逻辑对象，而不是

37、物理的数据库文件。SQL Server 2005中的对象有：表（Table）、视图（View）、存储过程（Stored Procedures）、触发器（Triggers）、用户定义数据类型（User-defined Data Types）、用户自定义函数（User-defined Functions）、索引（Indexes）、规则（Rules）、默认值（Defaults）等。,本章小结,SQL Server 2005有master数据库、tempdb数据库、model数据库和msdb数据库4个系统库，在创建任何数据库之前，依次打开SQL Server Management Studio中“对象

38、资源管理器”的“服务器”/“数据库”/“系统数据库”目录，我们就可以看到这4个系统数据库。,本章小结,我们可以使用SQL Server Management Studio和T-SQL语句两种方式创建、查看、修改和删除数据库。T-SQL提供了数据库创建语句CREATE DATABASE、数据库查看语句sp_helpdb和sp_databases、文件查看语句sp_helpfile、数据库修改语句ALTER DATABASE、数据库删除语句DROP DATABASE。,本章小结,SQL Server为了实现T-SQL的良好性能，提供了丰富的数据类型，包括数值型、字符型、日期/时间型、货币型、二进制

39、型、双字节型、图像、文本型以及用户自定义型数据。我们同样可以使用SQL Server Management Studio和T-SQL语句两种方式创建、显示、修改和删除数据表结构。T-SQL提供了数据表创建语句CREATE TABLE、数据表显示语句sp_help、数据表修改语句ALTER TABLE、数据表删除语句DROP TABLE。,本章小结,新创建的表中没有任何数据，可以通过数据更新操作向表中插入新数据，修改表中的数据和删除表中的数据。我们可以使用INSERT语句插入数据、UPDATE语句修改数据、DELETE和TRUNCATE语句删除数据。,习题五,一、填空题1SQL Server 2

40、005提供的系统数据类型有_、_、_、_、_和货币数据，也可以使用用户定义的数据类型。2SQL Server 2005的数据库包含3类文件：_、_和_。包含4个系统数据库：_、_、_和msdb数据库。3可以使用系统存储过程_来查看表的定义，后面加上要查看的_作为参数。,习题五,二、操作题1创建用户定义的数据类型：编号（非空，长度为8的字符型）。2创建图书数据库（BookSys），并在数据库中建立如下表，要求图书编号、读者编号使用用户定义类型：编号。,习题五,图书信息（tsxx）,说明：图书编号、书名不能为空。读者信息（dzxx）,说明：读者编号、姓名不能为空。,习题五,借阅信息（jyxx）,说明：图书编号、读者编号不能为空。,习题五,3完成如下操作。（1）向读者信息表中添加列：联系方式，可以为空。（2）修改列“出版社”的定义，长度修改为200。（3）删除“联系方式”一列。4完成如下数据操作。（1）向各表插入若干数据。（2）修改读者信息表中编号为00001001的读者的级别为2级。（3）删除借阅信息表中读者编号为00001001借阅10010001图书的记录。,