[计算机软件及应用]第4章sql serer.ppt

,SQL Server2005数据类型,本章学习要点,1,数据库表的类型,2,数据库表的创建,3,数据库表的管理,4,第4章 数据库表,本章学习内容导航,Diagram 2,Diagram 2,第4章 数据库表,4.1 数据表的概念与数据类型,4.2 创建数据表,4.3 数据的添加与查看,4.4 数据表的修改与删除,4.5案例应用,4.1 数据表的概念与数据类型,4.1.1 SQL Server表的概念,1表的的概念,表在关系数据库中是用来表示实体的，用来存储和组织数据、具有行、列结构的数据库对象。在SQL Server 数据库中，表定义为列的集合，数据在表中是按行、列的格式组织结构的。行，也称为记录，代表一个唯一的实体。列，也称为字段，代表对实体的属性。,由上可知：表有以下特点：代表实体，有唯一的名称，由行和列组成，行和列的顺序可以是任意的且列名不允许重复。,2表的分类,在Microsoft SQL Server 2005系统中，表大致分为4类，即普通表、已分区表、临时表和系统表，其中每一种表都有其自身的作用和特点。,2表的分类,普通表：也称为标准表或基本表，即通常说的在数据库中存储数据的表，是经常使用的表对象，也是最重要、最基本的表，简称表。,已分区表：是为提高系统使用效率而派生的表，将数据水平划分成多单元，这些单元可以分散到数据库的多个文件组里，来实现对单元数据的并行访问。若表中的数据量非常庞大，且数据经常以不同的方式来访问，建立分区是一个最好的选择。其优点是：方便管理大型表，来提高对数据的使用效率。,临时表：即临时创建的，不可以永久存在的表。临时分为本地临时表和全局临时表。一旦临时表创建后，可以一直存储到SQL Server实例断来链接为止。其中本地临时表只对创建者可见，而全局临时表在创建后对所有的用户和链接都可见。,系统表：主要存储有关SQL Server服务器配置、数据库设置、用户和表对象的描述等系统信息。通常只有DBA才能使用系统表。,3表的设计,3表的设计,由于数据表是数据库对象中最重要的一个对象，所以数据表的设计好坏直接关系到数据库的维护性和可用性。因此在设计表时，要根据数据库逻辑结构设计的要求确定需要什么样的表、什么样的数据、列的名称、所包含的数据类型，以及是否允许空值、索引、主键以及外键的约束等情况。创建一个表最有效的方法是将表中所需要的信息一次定义完成。然后在使用的过程中再逐一完善。,在Microsoft SQL Server 2005系统中，创建表有如下限制：, 每个数据库中最多可创建20亿个表； 每个表中最多可创建1个聚集索引和249个非聚集索引； 每个表最多可创建1024列； 每条记录最多可以占8060个字节。,4.1.2 SQL Server2005数据类型,4.1.2 SQL Server2005数据类型,数据类型是用来表现数据特征的，决定了数据在计算机中的存储格式、存储长度以及数据精度和小数位数等属性。在创建数据表时，表中的每一个列、局部变量、表达式等都离不开数据类型的支持。确定了数据类型也就确定了该数据的取值范围。在Microsoft SQL Server 2005系统中，需要使用数据类型的对象包括：表中的列、视图中的列、定义的局部变量、存储过程中的参数、Transact-SQL函数、存储过程的返回值等。,Microsoft SQL Server 2005系统提供了28种数据类型。这些数据类型可以分为数字数据类型、字符数据类型、日期和时间数据类型、二进制数据类型以及其他数据类型。,1数字数据类型,使用数字数据的数据类型称为数字数据类型。该数据类型可以参加各种数学运算。SQL Server2005系统提供了11种数字数据类型，即bigint、int、smallint、tinyint、decimal、numeric、money、smallmoney、float、real、bit种。实际上还可以把这些数据类型继续分类，如整数类型和小数类型，其中整数类型有bigint、int、smallint、tinyint。还可以分为精确数字类型和近似数字类型，其中float和real都是近似数字类型。也可以按是否能表示货币金额，分为货币类型和非货币类型等。,4.1.2 SQL Server2005数据类型,（1）整数数据类型,整数数据类型表示可以存储整数精确数据，SQL Server2005系统提供了4种整数数据类型：,bigint ：长度是8个字节，且可以存储正负数，取值范围是-263至263-1。 int ：长度是4个字节，且可以存储正负数，取值范围是-231至231-1，int是最常 用的数据类型，当int数据类型表示的数据长度不足时，才考虑使用bigint数据类型。 smallint ：长度是2个字节，也可以存储正负数，取值范围是-215至215-1 tinyint ：长度是1个字节，且都是正数，取值范围是0到255。,（2）decimal和numeric数据类型,1）decimal和numeric也称为精确数据类型，用于存储带小数点且小数点后位数确定的数据。,2）decimal （p,s）：取值范围是-1038+1至1038-1,3）numeric（p,s）：用法同decimal （p,s）,上面的语法中，p表示数字的精度，s表示数字的小数位数。精度p的取值范围是1至38，默认值是18。小数位s的取值范围必须是0到p之间的值，默认值是0。,4.1.2 SQL Server2005数据类型,（3）money和smallmoney数据类型,money和smallmoney也称为货币数据类型，这两种数据类型的差别在于存储字节的大小和取值范围不同。,1）money：需要8个存储字节，取值范围是-263至263-1，精确到货币单位的千分之十。,2）smallmoney：需要4个存储字节，取值范围是-215至215-1,money和smallmoney是一种确定性数值的数据类型，因为它们的精度和小数位都是确定的。但是这两种数据类型也有一些与其他数字数据类型不同的地方。第一，当表示货币时一定要加上$符号。第二，它们的小数位最多是4位，也就是精确到货币单位的万分之一。第三，当小数位位数超过4位时，系统自动按照四舍五入进行处理。,（4）float和real数据类型,若要进行科学计算，并且希望存储更大的数值，但对数据的精度要求不是绝对严格，可以考虑使用float和real数据类型，这两种数据类型用于表示指定数据的大致数据值的数据类型。,4.1.2 SQL Server2005数据类型,1）float（n）：存储8个字节，取值范围是-1.79E+3081.79E+308的浮点数，其中n为精度，n是从1到53的整数。,2）real：存储从-3.04E+38到3.04E+38之间的浮点数，存储大小为4个字节。,需要注意的是，如果某些列中的数据或变量将会参与科学计算，建议考虑为这些数据对象指定float或real数据对象，否则运算过程中会形成比较大的误差。,（5）bit数据类型,bit可以存储1、0、null数据的数据类型，这些数据主要用于一些条件逻辑判断。也可以把TRUE和FALSE数据存储到bit数据类型中，这时需要按照字符格式存储TRUE和FALSE数据。,2字符数据类型,SQL Server系统提供了6种字符数据类型，即char、varchar、text、nchar、nvarchar、ntext,其中char、varchar、text是非Unicode字符数据，nchar、nvarchar、ntext是Unicode字符数据。,4.1.2 SQL Server2005数据类型,（1）非Unicode字符数据类型,非Unicode字符数据用于存储汉字、英文字母、数字、标点和各种符号，输入时必须用半角单引号括起来，是定长的字符数据类型：,1）char（n）：为固定长度存储字符串的数据类型，n取值范围是18000，存储空间大小为n个字节。,2）varchar(n|max) ：变长存储字符串的数据类型，n 可以是1和8000之间的数，max表示最大存储大小为231-1,存储大小为所输入数据的实际长度+2个字节。,3）text ：可以存储最大长度为231-1个字节的字符数据，微软公司建议尽量避免使用text数据类型，而应该使用varchar(max)存储大文本数据。,在选择使用char（n）和varchar(n|max)数据类型时，可以考虑使用下面的原则：,（1）如果该列存储的数据长度都相同，这时应该使用char（n）数据类型。如果该列存储的数据长度相差比较大，由应该考虑使用varchar（n）。,4.1.2 SQL Server2005数据类型,（2）如果存储的数据的长度虽然不是完全相同，但长度差别不大，且希望提高查询的执行效率，可以考虑char（n）数据类型，如果希望降低数据存储的成本，则可以考虑使用varchar（n）数据类型。,（2）Unicode字符数据类型,Unicode标准为全球商业领域中广泛使用的大部分字符定义了一个单一的编码方案。所有的计算机都采用单一的Unicode标准，Unicode数据中的位模式一致翻译成字符。这确保了同一个位模式在所有的计算机上总是转换成同一个字符。因此当数据库中存储的数据有可能涉及到多种语言时，应该使用Unicode数据类型。用于存储Unicode字符数据的数据类型包括nchar、nvarchar、ntext。,3日期和时间数据类型,日期和时间数据用于存储日期和时间类型的数据，两者区别在于表示的日期和时间范围、精度不同。,1）datetime：可以存储从1753年1月1日到9999年12月31日的日期和时间，精确度为3.33毫秒。,4.1.2 SQL Server2005数据类型,2）smalldatetime ：可以存储从1900年1月1日到2079年6月6日的日期和时间，精确度为分。,在向表中输入时间日期数据时，可以使用SET DATEFORMAT语句改变时间的格式。,4二进制数据类型,SQL Server系统提供了种二进制数据类型，即存储固定长度的binary，存储可变长度的varbinary以及存储图像信息的image。,binary(n)和varbinary(n)的数据长度由n 来确定，n的取值范围是到8000，默认值是，如果存储二进制数据大小一致，则使用binary(n)数据类型，如果要存储的数据类型之间大小差别较大，则使用varbinary(n)数据类型，如果存储数据大于8000字节，就必须使用varbinary(max)数据类型。微软公司建议使用varbinary(max)来代替image数据类型。,5其他数据类型,除上面介绍的数据类型外，SQL Server 2005系统还提供了cursor、sql_variant、table、timestamp、uniqueidentifier和xml数据类型，使用这些数据类型可以完成对特殊数据对象的定义、存储和使用。,4.1.2 SQL Server2005数据类型,cursor：是变量或存储过程 OUTPUT 参数的一种数据类型，这些参数包含对游标的引用。使用 cursor 数据类型创建的变量可以为空。该变量只能用于与定义游标和使用游标的有关语句中，不能在诸如CREATE TABLE之类的语句中使用。,sql_variant：用于存储 SQL Server 支持的各种数据类型（不包括 text、ntext、image、timestamp 和 sql_variant）的值。sql_variant 可以用在列、参数、变量和用户定义函数的返回值中。sql_variant 使这些数据库对象能够支持其他数据类型的值。类型为 sql_variant 的列可能包含不同数据类型的行。例如，定义为 sql_variant 的列可以存储 int、binary 和 char 值。sql_variant最大长度是8016个字节，其中包括基类型信息和基类型值，基类型值的最大长度是8000个字节。,table：一种特殊的数据类型，用于存储结果集以进行后续处理。table 主要用于临时存储一组作为表值函数的结果集返回的行。,timestamp：公开数据库中自动生成的唯一二进制数字的数据类型。rowversion 通常用作给表行加版本戳的机制。存储大小为 8 个字节。rowversion 数据类型只是递增的数字，不保留日期或时间。若要记录日期或时间，请使用 datetime2 数据类型。,4.1.2 SQL Server2005数据类型,uniqueidentifier：是一个特殊的数据类型，这是一个具有16 个字节的全局唯一性标志符，用来确保对象的唯一性，可以通过使用NEWID函数得到该数据类型的初始值。,xml：是SQL Server 2005系统中新增的数据类型，用于存储XML的数据类型。可以像使用int数据类型一样使用XML数据类型。需要注意的是，存储XML数据类型表示实例最大值是2GB。,4.2 创建数据表,在Microsoft SQL Server 2005系统中，既可以使用CREATE TABLE语句创建表，也可以使用可视化的Microsoft SQL Server Management Studio工具来创建。下面将分别介绍这两种方法。,4.2.1 使用SQL Server Management Studio创建数据表,数据表主要包括表结构、约束和数据三部分。因此创建表也就包括这三个部分：定义表结构、设置约束和添加数据三步，其中设置约束可以在定义表结构时创建，也可以在定义表完成后再创建，步骤如下：,（1）定义表结构：定义列的列名、数据类型、数据长度以及是否为空等属性。,（2）设置约束：设置约束来限制输入值的取值范围，以保证输入数据的正确性和一致性。,（3）输入数据：表建立完成后，就可以向表中输入数据。,在SQL Server 2005系统中，可以使用SQL Server Management Studio中的表设计器完成。,4.2.1 使用SQL Server Management Studio创建数据表,【案例4-1】在“stu“数据库中,创建系部信息表“ Department “，表结构如表4-1所示：,表4-1 系部信息表结构,利用表设计器创建表操作步骤如下：,（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，依次展开【数据库】【stu】【表】节点，右击【表】节点，单击右键，在弹出的快捷菜单中选择【新建表】命令，打开【表设计器】对话框。,（2）在【表设计器】对话框中，窗口上部分，其中每一行描述了表中的字段，每一行有三列，包括：列名、数据类型、长度以及是否允许为空的属性，当每选中一行时，在窗口下部分，就会显示相应的该列的详细属性：包括名称、长度、约束、规则等。,4.2.1 使用SQL Server Management Studio创建数据表,（3）插入、删除列，在定义表结构时，可以在选中的某一字段的上边插入一个新字段，也可以删除一个字段。操作如下：在表设计器窗口的上部分中右击该字段，在弹出的快捷菜单中选择【插入列】或【删除列】即可。,（4）保存表。完成以上操作后，单击工具栏上的“保存”按钮，打开保存对话框，输入“Department”，单击“确定”按钮，完成了表的定义。,4.2.2 使用Transact-SQL创建数据表,4.2.2 使用Transact-SQL创建数据表,在SQL Server 2005中，除使用SQL Server Management Studio以图形界面创建数据表外,还可以在查询编辑器中用T-SQL语句中的CREATE TABLE语句创建数据表,下面介绍如何使用 CREATE TABLE语句创建数据表。,CREATE TABLE语句是一种最灵活、最强大的创建表的方式，为了更好的理解CREATE TABLE语句，下面我们通过实例来介绍它的使用方法：,【案例4-1】 在“stu“数据库中，建立一个“ speciality “（专业）表，表结构如下表4.2所示：,表4-2 “ speciality “（专业）表结构,4.2.2 使用Transact-SQL创建数据表,利用T-SQL语句创建该表的步骤如下：,（1）启动SQL Server Management Studio，单击工具栏上的“新建查询”按钮，打开查询编辑窗口，输入如下SQL语句：,USE stu GO CREATE TABLE speciality ( sp_id char (4) NOT NULL, sp_name varchar (20) NOT NULL, d_id char(2) NOT NULL) GO,（2） 输入上面代码后，单击工具栏上的“分析”按钮或用“CTRL+F5”键，对输入代码进行分析查询，通过检查后，单击工具栏上的“执行”按钮或用“F5”键，消息框中返回“命令成功完成”表示完成数据表的创建。,（3）在“对象资源管理器”窗口中，右击表节点，在弹出的快捷菜单中选择“刷新”命令，就可看到新创建的“speciality”数据表。,4.2.2 使用Transact-SQL创建数据表,通过是例可以看到，创建表的关键字是“CREATE TABLE”；表名是“speciality”，在括号内给出的是该表的三个字段，如：“sp_id char (4) NOT NULL”的含义是，字段名“sp_id”，数据类型是char，长度为4个字节，不允许出现NULL值，字段与字段定义之间用西文的逗号分开。,实际上，在使用CREATE TABLE语句创建表时，可以同时指定列的主健、外键以及其他约束、规则等属性，,【案例4-2】 在“stu“数据库中，建立一个“class“（班级）表，表结构如下表4.3所示：,表4-3 “class“（班级）表结构,4.2.2 使用Transact-SQL创建数据表,利用T-SQL语句创建该表：,USE stu GO CREATE TABLE class ( c_id char (9) CONSTRAINT pk_bjdm PRIMARY KEY, c_name varchar (20) NOT NULL, sp_id char (4) CONSTRAINT fk_zydm REFERENCES speciality(sp_id) , d_id char (2) CONSTRAINT fk_xbdm REFERENCES department (d_id), c_memo varchar(50) GO,在创建“class”表时，sp_id char (4) CONSTRAINT fk_zydm REFERENCES speciality(sp_id)的含义是指定speciality 表的“sp_id”字段为“class”表的外键。有关主键、外键的详细介绍详见第5章中5.2节约束的相关内容。,4.3 数据的添加与查看,4.3.1 数据的添加,数据表定义完成后，此新表中并不包含任何数据记录，在SQL Server 2005中，可以通过SQL Server Management Studio中对象资源管理器向表中添加数据，也可以使用T-SQL语句向表中添加数据，下面分别介绍这两种方法。,下面我们以stu数据库为例，介绍如何使用SQL Server Management Studio向表中添加数据的方法及步骤：,（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，选择相应的【stu】数据库。,（2）展开选中的数据库节点，再展开【表】节点，右键单击要添加数据的【表】，如【class】，在弹出的快捷菜单中选择【打开表】命令，就会打开【查询设计器】的结果窗口。,（3）在【查询设计器】的表中可以输入新记录，也可以修改和删除已经输入的数据。,下面我们以stu数据库为例，介绍如何使用T-SQL 语句向表中添加数据：,4.3.1 数据的添加,（1）最简单的INSERT语句：,【案例4-3】 在“stu“数据库中，向“ department“（系部）表中添加一条记录。T-SQL语句如下：,USE stu GO INSERT department(d_id,d_name, d_director) VALUES(07,公共教学部,陈平) GO,此种方法要求VALUES列表中的表达式的个数、顺序必须与列表中的个数、顺序一致，表达式的数据类型应与对应列的数据类型兼容。,（2）省略字段清单的INSERT语句：,【案例4-4】 在“stu“数据库中，向“ department“（系部）表再添加一条记录。T-SQL语句如下：,4.3.1 数据的添加,USE stu GO INSERT department VALUES(08,艺术传媒系,王小慧) GO,此种方法省略了字段清单，必须按照这些列在表中定义的顺序提供每一个列的值。,（2）省略字段清单的INSERT语句：,【案例4-5】 在“stu“数据库中，创建一个系部表的副本“ department1“（系部1），并向“ department1“（系部1）表再添加一条记录。T-SQL语句如下：,USE stu GO CREATE TABLE department1 (d_id char(2) not null primary key, d_name varchar(30) not null, d_director varchar(8) ) GO,4.3.1 数据的添加,USE stu GO INSERT department1 SELECT d_id,d_name,d_director FROM department GO,此种方法是一种简单的插入多行记录的方法，这种方法使用SELECT语句查询出结果来代替VALUES子句，其中要求：INSERT表和SELECT表的结果集的列数、列序、数据类型必须一致。,4.3.2 数据的查看,4.3.2 数据的查看,在SQL Server 2005中，可以通过SQL Server Management Studio中对象资源管理器查看表中的相关数据，也可以使用T-SQL语句完成（详细用法将在第6章中详细介绍），下面主要介绍第一种方法。,下面我们以stu数据为例，介绍如何使用SQL Server Management Studio查看表中数据的方法及步骤：,（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，选择相应的【stu】数据库。,（2）展开选中的数据库节点，再展开【表】节点，右键单击要添加数据的【表】，如【class】，在弹出的快捷菜单中选择【打开表】命令，就会打开【查询设计器】的结果窗口，来查看详细的数据信息。,4.4 数据表的修改与删除,4.4.1 数据表的修改,当表创建好后，由于实际需要可能会对表的结构进行一些调整、修改操作，这些操作包括：修改表中列的名称、数据类型，添加列、调整列的次序、删除列等操作。,1使用SQL Server Management Studio中表设计器修改表结构,使用表设计器修改表结构的操作方法及步骤如下：,（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，选择相应的数据库，如【stu】。,（2）展开【stu】节点，再展开【表】节点，右键单击要修改的【class】表，在弹出的快捷菜单中选择【修改】命令，启动表设计器。,（3）若要修改字段名，选中要修改的字段，即可修改字段名、字段类型、字段长度、是否为空等属性。,4.4.1 数据表的修改,（4）若要添加,将光标移动到最后一个字段的下边，输入新字段的定义即可。 若要在某一字段前插入一个新字段，右键单击该字段，在弹出的快捷菜单中选择【插入列】命令； 若要删除某字段，右键单击该字段,在弹出的快捷菜单中选择【删除列】命令。,修改完成后，点击快捷菜单栏中【保存】图标，所进行的修改操作立即生效。,2使用T-SQL语句修改表结构,使用T-SQL语句修改表结构包括：修改指定列的属性、增加列、删除列等操作，语法格式如下：,修改指定列的属性：,ALTER TABLE 表名 ALTER COLUMN 列名 更改后新的数据类型 ( 精度 , 小数位 ) NULL | NOT NULL 增加列 ALTER TABLE 表名 ADD 列定义 ,n 删除列 ALTER TABLE 表名 DROP COLUMN 列名 ,n ,4.4.1 数据表的修改,【案例4-6】 在“stu“数据库中，修改“class“（班级）表中“c_name“(班级名称)字段的数据类型为varchar(30),不允许为空。T-SQL语句如下：,USE stu GO ALTER TABLE class ALTER COLUMN c_name varchar(30) NOT NULL GO,【案例4-7】 在“stu“数据库中，为“department “（系部）表增加“d_memo“(系部描述) 列，字段的数据类型为varchar(50),允许为空。T-SQL语句如下：,USE stu GO ALTER TABLE department ADD d_memo varchar(50) NULL GO,4.4.1 数据表的修改,【案例4-8】 在“stu“数据库中，删除“teacher “（教师）表中“t_memo“(教师信息备注)。T-SQL语句如下：,USE stu GO ALTER TABLE teacher DROP COLUMN t_memo GO,4.4.2 数据表的删除,4.4.2 数据表的删除,在实际数据库的管理应用过程中，由于各种原因，有时对一些不需要的表，要进行删除操作，一旦执行删除表的操作，该表的数据结构、记录、约束、索引等相关信息将全部删除。,使用SQL Server Management Studio删除表,使用表设计器删除表的操作方法及步骤如下：,（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，选择相应的数据库，如【stu】。,（2）展开【stu】节点，再展开【表】节点，右键单击要删除的表，在弹出的快捷菜单中选择【删除】命令，在弹出的对话框中选择【确定】按钮即可完成该表的删除。,删除操作完成后，该表的所有信息将被永久删除。,4.4.2 数据表的删除,2使用T-SQL语句删除表,T-SQL中的DROP TABLE语句来实现，详细语法如下：,DROP TABLE 表名,.表名,【