掌握用SSMS和T-SQL创建表

第3章表的创建和使用本章的学习目标：掌握用SSMS和T-SQL创建表熟悉各种列数据类型掌握列的各种属性掌握用SSMS和T-SQL修改、删除表熟悉保持数据完整性的各种约束掌握根据完整性规则的要求使用SSMS和T-SQL设置表的主键、外键和约束等3.1表概述在使用数据库的过程中，接触最多的莫过于表。表是存储数据的地方，是一种结构化的文件，可用来存储一些特定数据类型的数据，是数据库中最重要的部分。3.1.1什么是表在关系数据库中，每一个关系都表现为一张表。表是用来存储数据和操作数据的逻辑结构，关系数据库中的所有数据都表现为表的形式，由列和行组成，如图3-1所示。关系数据库由表、具体查询等对象组成，而查询等对象又是通过表来呈现的。表的数据类型种类数据类型数字整数int,bigint,smallint,tinyint精确数值decimal,numeric近似数值float,real货币money,smallmoney日期和时间datetime,smalldatetime字符Non-Unicodechar,varchar,varchar(max),textUnicodenchar,nvarchar,nvarchar(max),ntext二进制binary,varbinary,varbinary(max)图像image全局标识符uniqueidentifierXMLxml特殊bit,cursor,timestamp,sysname,table,sql_variant使用SQLServerManagementStudio创建表使用T-SQL语句创建表CREATETABLE[database_name.[owner].|owner.]table_name（{column_definition|column_nameAScomputed_column_expression|table_constraint}[，…n]）[ON{filegroup|DEFAULT}][TEXTIMAGE_ON{filegroup|DEFAULT}]column_definition::={column_namedata_type}[[DEFAULTconstant_expression]|[IDENTITY[（seed,increment）]]][column_constraint][...n]3.2列数据类型在计算机中，按照数据描述信息的含义，将数据分为不同的种类，称之为数据类型。3.2.1数据类型的分类SQLServer提供两类数据类型：一类是用户定义数据类型，用户可以根据需要创建自己的数据类型，会对数据组织带来一定的好处。一类是基本数据类型，是SQLServer系统提供的数据类型。本书只介绍基本数据类型。种类数据类型数字数据类型int,bigint,smallint,tinyint,decimal,numeric,float,real,money,smallmoney,bit字符数据类型char,varchar,nchar,nvarchar,ntexttext日期和时间datetime,smalldatetime,date,time,datetime2,datetimeoffset二进制数据类型binary,varbinary,image其他数据类型uniqueidentifier,xml,timestamp,sql_variantSQLServer中常用的数据类型3.2.2数字数据类型数字数据类型表示符号用途精确数字类型整数数据类型int、bigint、smallint、tinyint存储数字数据，直接进行数据运算位数据类型bit用于一些条件逻辑判断货币数据类型money、smallmoney存储货币数据近似数字类型十进制小数型decimal、numeric存储带固定精度和位数的小数浮点数据类型float、real存储范围非常大的数字，用于表示指定数的大致数据值3.2.3字符数据类型字符类型表示符号用法ANSI字符char存储指定长度的字符串varchar存储可变长度字符text存储大量非Unicode字符Unicode字符nchar用于存储Unicode字符nvarchar与varchar类似，存储可变长度Unicode字符ntext与text类似，存储大量Unicode字符数据3.2.4日期时间数据类型日期时间数据类型用于存储日期和时间信息。在SQLServer2008之前的版本中，日期时间类型只包括datetime和smalldatetime两种类型。它们的差别在于其表示的日期和时间范围不同、时间精确度也不同。其中最常用的是datetime型数据。3.2.5二进制数据类型二进制数据类型用于表示位数据流，包括binary（固定长度）、varbinary（可变长度）和image三种。–binary用于存储固定长度的二进制数据；–varbinary用于存储可变长度的二进制数据，若存储的二进制大于8000字节，就必须使用varbinary（max）数据类型；–image数据类型用于存储图像信息，在SQLServer2008中，只有在数据的字节数超过了8KB的情况下，才使用image数据类型，其他情况应使用varbinary（max）代替，其中max最大可以达到231-1字节；3.2.6其他数据类型除了上述数据类型外，SQLServer还提供了其他几类常用数据类型，使用这些数据类型可以完成特殊数据对象的定义、存储和使用：–timestamp–sql_variant类型–uniqueidentifier唯一标识符类型3.2.7数据类型的选择数据的使用情况：数据是用于数学运算？表示日期或者时间？存储文本信息？不同的使用情况决定了不同数据类型的选择；数据的大小：选择的数据类型能否存放期望存储的最大值。例如，选择整型数据，要根据数据的取值决定使用INT型数据还是BIGINT型数据；选择字符型数据，需要考虑实际需要的最大字符长度；正确的存储信息：例如使用integer数据类型存储货币值，将造成小数部分丢失，这将导致不正确的值；非英语字符：在SQLServer2008系统中，如果某些列需要存储非英语字符（如中文字符），建议最好使用NCHAR、NVARCHAR数据类型。3.3列的其他属性3.3.1默认约束–默认约束指用户在进行插入操作时，没有显示地为列提供数据，那么系统将把默认值赋给该列。默认值约束所提供的默认值可以为常量、函数、系统函数、空值等，表中的每一列只能定义一个默认约束，对于具有IDENTITY属性和timestamp数据类型的字段，不能使用默认约束，同时，定义的默认值长度不允许大于对应字段所允许的最大长度。DEFAULT约束使用SSMS图形化界面创建默认约束用Transact-SQL语句创建默认约束。其语法形式如下：CONSTRAINTconstraint_nameDEFAULTconstraint_expression[FORcolumn_name]3.3.2空值约束空值约束即是否允许该字段的值为NULL，即空值。主键列不允许为空值，否则就失去了唯一标识的意义。NULL约束使用SSMS图形化界面设置空值约束。用Transact-SQL语句创建空值约束。其语法形式如下：直接在相应的语句后书写[NULL|NOTNULL]3.3.3IDENTITY的应用identity表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值，是系统定义的全局变量。比如有个表A，它的自增列是id，当向A表插入一行数据后，如果插入数据后自增列的值自动增加至101，则通过select@@identity得到的值就是101。3.4向表中添加数据首先在需要添加数据的表上单击鼠标右键，从弹出的快捷菜单中选择“打开表”命令，出现如图3-8所示的“表”对话框，在其中可向打开的表添加数据。本节向各个表中添加的数据如表3-5~3-7所示。3.5查看表3.5.1查看表中的有关信息–打开指定的数据库，在需要查看的表上单击鼠标右键，从弹出的快捷菜单中选择“属性”命令，将打开“表属性”对话框，如图3-10所示。“常规”选项页中显示了该表格的定义，包括存储结构、当前的连接及名称等属性，该选项页中显示的属性不能修改。3.5.2查看表中存储的数据在表“课程表”上单击鼠标右键，在弹出的快捷菜单中选择“打开表”命令，显示“课程表”中的数据，如图3-11所示。3.5.3查看表与其他数据对象的依赖关系在要查看的表上单击鼠标右键，从弹出的快捷菜单中选择“查看依赖关系”命令，打开“对象依赖关系”对话框，该对话框显示了该表依赖的其他数据对象和依赖于此表的依赖对象。例如，查看“选课表”的依赖对象，如图3-12所示的对话框显示了选课表依赖于课程表与学生表。3.6修改表3.6.1使用SSMS图形化界面修改表–在“对象资源管理器”窗口中，展开“数据库”节点，再展开具体选择的数据库，然后展开“表”节点，用右键单击要修改的表，从弹出的快捷菜单中选择“设计”命令，打开“表设计器”，即可对表的定义进行修改，方法同创建表。3.6.2使用T-SQL语句修改表ALTERTABLE表名(ALTERCOLUMN列名列定义，ADD列名数据类型约束，……DROP列名，……ADDCONSTRAINT约束名约束，……)例【3-2】：在AdventureWorks数据库中，在Sales下创建名为CustomerOrders的表；创建OrderID、OrderDate、CustomerID、Notes列，并定义其数据类型为int、datetime、int、nvarchar(200))和可空性(Notes列为空)，指定OrderID列为标志列。T-SQL语句如下：CREATETABLESales.CustomerOrders(OrderIDintidentity(1,1),OrderDatedatetime,CustomerIDint,Notesnvarchar(200)NULL)；例【3-3】：基于例3-2定义的Sales.CustomerOrders表，添加列和更改列的可空性。--修改Sales.CustomerOrders表的定义--在表中添加名为SalesPersonID的列，并定义其数据类型为int，指其不可空ALTERTABLESales.CustomerOrdersADDSalesPersonIDintNOTNULLGO--修改Sales.CustomerOrders表的定义--修改Notes列，并定义其数据类型为nvarchar(200)，指定其不可空ALTERTABLESales.CustomerOrdersALTERCOLUMNNotesnvarchar(200)NOTNULLGO3.6.3使用INSERTSELECT语句INSERTSELECT语句可以将其他数据源中的行添加到数据库中现有的表当中。这种方法用于插入的数据是不确定(一般都多于一条)。具体方法是：通过SELECT语句生成的结果集，再结合INSERT语句，就可以把结果集插入到指定的表中。例【3-4】：求每个学生的平均成绩，并按学号、姓名、平均成绩存入学生信息数据库。首先创建一个表：CREATETABLEAG(学号CHAR(8)PRIMARYKEY,姓名CHAR(8),平均成绩SMALLINT)GO;3.6.4使用SELECTINTO语句SELECTINTO语句与INSERTSELECT语句不同的是：它可以将其他数据源中的任何查询结果或导入的数据都添加到数据库的新表当中。例【3-5】：统计每个学生未通过课程的门数，将其保存到临时表#stuexam表。显示系名、学号、姓名、未通过门数，并按系名排序。T-SQL语句如下：SELECTMIN(系名),SC.学号,MIN(姓名),COUNT(*)未通过门数INTO#stuexamFROMStudent,SCWHEREStudent.学号=SC.学号AND成绩60GROUPBYSC.学号O