SQL-sever自学教程-管理表PPT课件.ppt

2020 3 21 1 管理表 概述数据类型管理表查看表属性习题 2020 3 21 2 一 概述 表是数据库中的主要对象 用来存储各种各样的信息 1 按照数据存储的时间分类表可以分为永久性表和临时性表 数据库中的数据一般存储在永久性的表中 通常所说的数据库表 就是永久性的表 在永久性的表创建之后 这些表就存储在数据库文件中 并且一直存在 直到它们被删除为止 数据库中的表可以被数据库中的用户使用 当然 这些用户应该具有使用表的相应的权限 用户还可以创建临时表 临时表的使用与永久性的表类似 只是临时表存储在内存中 当它们不再被使用时 会被自动删除 2020 3 21 3 2 按照表的用途分类可以分为系统表和用户表两类 用户表是用户创建的 用于开发各种数据库应用系统的表 通常用户创建的表就是永久的用户表 系统表是维护SQLServer2000服务器和数据库正常工作的数据表 每个数据库都会建立很多系统表 系统表不允许用户进行更改 只能由DBMS系统自行进行维护 SQLServer2000中几个重要的系统表如下表所示 2020 3 21 4 2020 3 21 5 一般而言 表具有下列特点 1 代表实体 2 由行和列组成 3 行和列的顺序是不重要的 注意 每个数据库最多可存储20亿个表 每个表至多可定义1024列 每行最多可存储8060字节 表和列的命名要遵守标识符的规定 在特定表中必须是唯一的 但同一数据库中的不同表可使用相同的列名 必须为每列指定数据类型 尽管对每一个所有者在一个数据库内表的名称必须是唯一的 但如果为每一张表指定不同的所有者 则可以创建多个具有相同名称的表 当使用此表时 可以通过指定表的使用者以及表名称来区分表 2020 3 21 6 二 数据类型 在MicrosoftSQLServer2000系统中 有两种数据类型 一种是系统提供的数据类型 另外一种是用户基于系统数据类型而定义的用户数据类型 1 系统数据类型2 用户定义的数据类型 2020 3 21 7 在创建表时 要对表中的各字段定义数据类型 在讨论数据类型之前 先介绍在数据类型中经常使用的三个术语 精度 小数位数和长度 精度 指数值型数据可以存储的十进制数字的总位数 包括小数点左侧的整数部分和小数点右侧的小数部分 比如 1230 456的精度为7 小数位数 指数值型数据小数点右边的数字个数 比如 543 15的精度是5 小数位数是2 长度 指存储数据时所占用的字节数 数据类型不同 所占用的字节数就有所不同 有些数据类型拥有固定的长度 而有些数据类型则根据用户的要求来决定长度 比如 real类型的数据存储时不管数值多大均占用4个字节长度 而字符型数据则可根据用户的要求来决定存储数据的长度 精度和小数位数是针对数值型数据的 但不是所有的数值型数据都能设置精度和小数位 某些数值类型的精度与小数位数是固定的 对这样的数据类型的字段不能设置精度与小数位 2020 3 21 8 1 系统数据类型 在MicrosoftSQLServer2000系统中 提供了27种数据类型 常用的数据类型如表所示 2020 3 21 9 整数型在MicrosoftSQLServer中 整数存储的数据类型是bigint int smallint tinyint和bit bigint 从 2 63到2 63 1的整型数据 长度为8字节 int 从 2 31到2 31 1的整型数据 长度为4字节 Smallint 从 2 15到2 15 1的整型数据 长度为2字节 tinyint 数据的范围为0 255 长度为1字节 bit 此类型数据相当于其他语言中的逻辑型数据 它只存储0 1或NULL 空值 长度为1字节 当希望表示真或假 是或非 合格或不合格 有或无 ON或OFF时 建议使用bit数据类型 2020 3 21 10 精确数值型精确数值型包括decimal和numeric两类 由整数部分和小数部分构成 其所有的数字都是有效位 能够以完整的精度存储十进制数 这种数据所占的存储空间根据该数据的位数和小数点后的位数来确定 使用时 需指明精度 即全部有效位数 与小数位数 在SQLServer中这两种类型完全相同 当用命令创建表并声明此类型的数据时 在类型名的后面标明精度和小数位数 如 numeric p s 或decimal p s 其中p为精度 不仅表示数值精度 也隐含指定了数据占用空间的大小 s为小数位数 例如 numeric 8 3 表示精度为8 即总共有8位数 其中有5位整数和3位小数 若不指定 则默认为numeric 18 0 精度与长度的关系 当精度为1 9时 长度为5字节 当精度为10 19时 长度为9字节 当精度为20 28时 长度为13字节 当精度为29 38时 长度为17字节 2020 3 21 11 近似浮点数值型在MicrosoftSQLServer中 浮点数值数据类型是float和real float类型在定义变量或字段时可以定义精度 即表示为 float n 用于存储 1 79E 308 1 79E 308之间的浮点数值 用于存储科学计数法浮点数尾数的位数 同时指示其精度和存储大小 必须为介于1到53之间的值 当 介于1到24之间 精度为7位有效数字 占用4个字节 当 介于25到53之间时 精度为15位有效数字 占用8个字节存储空间 real类型也是浮点数 其值范围为 3 40E 38 3 40E 38 占用4个字节存储空间 在SQLServer中 real类型相当于float 24 float和real的区别在于float数据类型可以表示的数据范围远远大于real数据类型可以表示的数据范围 2020 3 21 12 货币数据类型在MicrosoftSQLServer中 货币数据的数据类型是money和smallmoney 用于表示与货币有关的数据 money 货币数值介于 2 63到2 63 1之间 长度为8字节 精确到货币单位的万分之一 smallmoney 货币数值介于 214748 3648到214748 3647之间 长度为4字节 精确到货币单位的万分之一 实际上 这种数据类型也是一种小数 但是在这种类型的数据中 小数点后的数据有四位 并且会自动地四舍五入 2020 3 21 13 日期和时间数据在MicrosoftSQLServer中 日期和时间数据类型包括datetime和smalldatetime两种类型 日期和时间数据类型由有效的日期和时间组成 两种数据类型的区别在于两者存储时间和日期的范围不同 datetime 能够存放1753年1月1日 9999年12月31日的日期和时间数据 精确到百分之三秒 或3 33毫秒 长度为8字节 smalldatetime 能够存放1900年1月1日 2079年6月6日的日期和时间数据 精确到分钟 长度为4字节 输入日期部分时可以采用英文数字格式 数字加分隔符格式或纯数字格式 采用英文数字格式时 月份可用英文全名或缩写形式 不分大小写 例如 2003年8月16日这个日期就可以有下列几种输入格式 Aug162003 英文数字格式2003 8 16 数字加分隔符格式20030816 纯数字形式输入时间部分时可以使用12小时格式或24小时格式 使用12小时格式时加上AM或PM说明上午还是下午 在秒与毫秒之间用半角冒号 作为分隔符 例如 要表示2003年8月16日下午2点30分45秒20毫秒 可用以下两种形式来输入 2003 8 162 30 45 20PM 12小时格式2003 8 1614 30 45 20 24小时格式 2020 3 21 14 字符数据字符型数据是由包含汉字 英文字母 数字和各种符号任意组合而成的数据 char 定长字符型 用来存放固定长度的字符数据 其长度最多为8KB 对于像身份证号码 邮政编码等为固定长度的数据 使用char类型比较合适 varchar 变长字符型 其长度最多为8KB 当一个字段中包含的字符个数是变化的 且不超过8000个字符时 可以使用varchar类型 例如 图书名称 字段比较适合使用varchar类型 因为书名长短不一 所以可以将最长的书名长度作为n的值 不同长度的书名 占用的字节数也不同 text 文本型 超过8KB的数据可以使用text数据类型存储 比如 个人简历 日志信息等有较长的内容 适合于用此类型 其数据的长度为实际字符个数 如果存储的数据的长度是不变化的 那么最好采用定长数据类型 如果存储的数据的长度是变化的 那么建议采用变长度数据类型 2020 3 21 15 unicode数据unicode数据类型也被称为国际数据类型 包括nchar nvarchar和ntext 可以用来存储世界上所有的字符 使用unicode数据类型 所占用的空间是使用非unicode数据类型所占用的空间大小的两倍 在MicrosoftSQLServer系统中 unicode数据以nchar nvarchar和ntext数据类型存储 当列的长度变化时 应该使用nvarchar字符类型 这时最多可以存储4000个字符 当列的长度固定不变时 应该使用nchar字符类型 同样 这时最多可以存储4000个字符 当使用ntext数据类型时 该列可以存储多于4000个字符的数据 注 unicode为全球使用的大部分字符定义的一个统一编码方案 数据可以从一个数据库或计算机传送到另一个数据库或计算机 接收系统不会错误的解释编码 一个字节只能表示256个不同的字符 unicode采用两个字节编码每个字符 可编码65536个字符 2020 3 21 16 二进制数据二进制数据包括binary varbinary和image 二进制数据类型既可以是固定长度的 也可以是变长度的 binary n 是n位固定长度的二进制数据 其中 n的取值范围是从1到8000 varbinary n 是位变长度的二进制数据 其中 n的取值范围也是从1到8000 在image数据类型中存储的数据是以位字符串存储的 这种数据不是由SQLServer解释的 必须由应用程序来解释 2020 3 21 17 特殊数据类型特殊数据类型包括前面没有提到过的数据类型 特殊的数据类型有5种 即cursor timestamp sql variant table和uniqueidentifier cursor数据类型用于游标定义 timestamp用于表示MicrosoftSQLServer活动的先后顺序 以二进制的格式表示 sql variant是一种允许存储不同数据类型的数据值的数据存储 table数据类型主要在定义函数时使用 uniqueidentifier由16个字节的十六进制数字表示 表示一个全局惟一的标识号 GlobalUniqueIdentifier GUID 当表中的记录要求惟一时 GUID是非常有用的 2020 3 21 18 2 用户定义的数据类型 当在几个表中 必须存储同一种数据类型 并且为了保证这些列有相同的数据类型 长度和空值性 那么最好使用用户定义的数据类型 当创建用户定义的数据类型时 必须提供3个参数 数据类型的名称 所基于的系统数据类型和数据类型的可空性 创建用户定义的数据类型可以使用系统存储过程sp addtype 其语法形式如下 sp addtypetype system data type null type其中 type是用户定义的数据类型的名称 system data type是系统提供的数据类型 例decimal int char等 null type表示该数据类型是如何处理空值的 必须使用单引号引起来 例如 NULL NOTNULL 2020 3 21 19 示例1 创建用户定义的数据类型USEpubsEXECsp addtypessn varchar 15 NOTNULL EXECsp addtypebirthday datetime NULL EXECsp addtypetelephone varchar 24 NOTNULL EXECsp addtypefax varchar 24 NULL 2020 3 21 20 当不需要用户定义的数据类型时 可以删除它们 删除用户定义的数据类型的命令是sp droptype 注 当用户定义的数据类型正在被某个表的定义引用时 这些数据类型不能被删除 示例2 删除用户定义的birthday数据类型USEpubsEXECsp droptypebirthday 2020 3 21 21 三 管理表 管理表技术包括创建 修改 删除表的结构 只有创建了表 才可以在表中插入和使用数据 1 创建表2 修改表的结构3 删除表的定义 2020 3 21 22 1 创建表 表的主要设计内容如下 表所包含的数据类型 表的各列及每一列的数据类型 如果必要 还应注意列宽 哪些列允许空值 是否要使用以及何时使用约束 默认设置或规则 所需索引的类型 哪里需要索引 哪些列是主键 哪些是外键 2020 3 21 23 作为一种数据库对象 表既可以使用CREATETABLE语句创建 可以使用SQLServerEnterpriseManager创建 一 使用CREATETABLE语句创建表CREATETABLE语句是一种经常使用的创建表的方法 也是一种柔性的创建表的方式 下面通过示例说明CREATETABLE语句的用法 CREATETABLE 数据库名 所有者 table name column namedatatype NOTNULL NULL IDENTITY seed increment n 其中 table name为表名 column name为字段名 datatype为字段类型 seed为标识种子 increment为标识递增量 IDENTITY子句用于指定对字段进行自动编号 NULL表示字段可取空值 NOTNULL表示字段不可取空值 2020 3 21 24 例1 创建student表CREATETABLEstudents numberintnotnull namevarchar 10 notnull genderchar 2 null birthdaydatetimenull hometownvarchar 30 null telphone novarchar 12 null addressvarchar 30 null othersvarchar 50 null 创建表student 该表包含了学生的信息 这些信息是学号 姓名 性别 出生日期 籍贯 联系电话 住址和备注等 2020 3 21 25 例2 创建orders表CREATETABLEorders order nochar 10 notnull customer nochar 10 notnull product novarchar 20 notnull amountmoneynotnull order datedatetimenotnull 创建表orders 该表是某公司的订单表 存储了该公司的订单信息 即订单代号 客户代号 产品代号 订单金额和订单日期等 2020 3 21 26 例3 创建mytable表CREATETABLEmytable lowint highint avgAS low high 2 在创建表的过程中 除了在列中指定数据类型和属性外 还可以对某些列进行计算 也就是说 某些列的值不是直接输入得到的 而是通过计算得到的 2020 3 21 27 例4 创建一个包含主键约束的orders2表CREATETABLEorders2 order nochar 10 notnullPRIMARYKEY customer nochar 10 notnull product novarchar 20 notnull amountmoneynotnull order datedatetimenotnull 在定义表结构时 还可以定义主键约束 主键约束是惟一地确定表中每一行数据的逻辑 主键约束的关键字是PRIMARYKEY 2020 3 21 28 结果显示如下 2020 3 21 29 2020 3 21 30 例5 创建一个数据库lx 在其中创建表card card表包含3个字段 卡号 字段为decimal类型 精度为6 小数位为0 并且此字段能自动编号 标识种子为100000 标识增量为1 书名 字段为char类型 字段最大长度为20 并且不能为空 类别 字段为char类型 字段最大长度为10 允许为空 操作步骤如下 1 启动企业管理器 创建lx数据库 2 在企业管理器的 工具 菜单中选择 SQL查询分析器 命令 进入 查询分析器 输入如下代码 CREATETABLEcard 卡号decimal 6 0 identity 100000 1 书名char 20 NOTNULL 类别char 10 NULL 3 单击 执行查询 按钮 执行此T SQL语句 即完成了card表的创建 2020 3 21 31 例6 在lx数据库中创建一个表lx1 字段同上题的stud new表 要求将 学号 设置为主键 1 在查询分析器的文本编辑区输入如下代码 CREATETABLElx1 学号char 8 NOTNULLPRIMARYKEY 姓名char 8 NOTNULL 入学日期datetime 2020 3 21 32 2 单击 执行查询 按钮运行后 再单击 对象浏览器 窗格中的 lx 节点 单击其前面的 号展开lx 然后展开 用户表 及 dbo lx1 再展开其中的 列 及 约束 节点 则出现如图所示的界面 2020 3 21 33 例7 在lx数据库中创建一个名为 成绩 的表 其包括如下字段 学号 课程编号 成绩 并将学号和课程编号两个字段的组合设置为主键 试写出能够完成相应功能的CREATETABLE语句 在查询分析器的文本编辑区中输入如下代码并运行 CREATETABLE成绩 学号char 8 NOTNULL 课程编号char 3 NOTNULL 成绩real CONSTRAINTpk x kPRIMARYKEY 学号 课程编号 2020 3 21 34 二 使用SQLServerEnterpriseManager创建表 1 启动企业管理器 在数据库上单击鼠标右键 选择 新建 表 或点击数据库前面的 号展开数据库 然后在 表 项上单击鼠标右键 在出现的快捷菜单中选择 新建表 系统将弹出表设计器窗口 2 表设计器窗口的上半部分有一个表格 在这个表格中输入字段的属性 每一行对应一字段 对每一字段都需要进行以下设置 其中前三项是必须输入的 3 表设计器窗口的下半部分是特定字段的详细属性 包括是否使用默认值 是否是标识列 设置精度及小数位数等 2020 3 21 35 在 长度 栏中选择数据类型的长度 如果选择的数据类型需要用户指定长度或精度 则在 长度 或表格下半部分的 精度 或 小数位数 栏中指定 对于字符型数据 包括Unicode字符型数据 在 长度 栏中输入一个数字 用以指定该字段的长度 对于decimal或numeric数据类型的字段 则在 精度 和 小数位数 栏中各输入一个数字 用以指定该字段的精度和小数位数 此精度值决定了该类型数据存储时所占用的长度 注意 对于像decimal和numeric等类型的数据 需要指明精度和小数位数 因为精度决定了数据存储时的长度 所以不允许在 长度 栏中改变长度值 默认值 在新增记录时 如果没有把值赋予该字段 则此默认值为字段值 默认值其实就是一个常量 如果字段设置了默认值 则在插入记录时 如果未指定该字段的值 则默认值将成为该字段的内容 例如 对于 图书信息 表的 状态 字段 类型为bit型 其值为0时 表示未借出 为1则表示已借出 若设置其默认值为0 则将光标定位在 状态 字段行上 在下面的属性框的 默认值 栏中输入0即可 插入记录时 用户可以不输入 状态 字段信息 保存时这些记录的值将都为0 2020 3 21 36 允许空 指定是否可以为空 NULL 打勾说明允许为空 空白说明不允许为空 默认状态下是允许为空 用户可以通过单击鼠标或按空格键来进行设置 NULL值不等于数值0 也不等于字符空白或长度为零的字符串 所谓字段能否允许NULL值 是指该字段中的数据是否可以是未知的 如果字段允许NULL 则代表该字段中的数据可以是未知的 如果字段不允许为NULL 则代表该字段中的数据不可以是未知的 那么在插入或输入记录时不给此字段输入数据 保存时就会出现错误信息 而且拒绝接收数据 2020 3 21 37 标识 表示对应字段是表中的一个标识列 即新增的字段值为等差数列 其类型必须是tinyint smallint int decimal p 0 或者numeric p 0 p为精度 0表示小数位数为0 使其具有自动编号功能 有此属性的字段会自动产生字段值 不需要用户输入 用户也不能输入 设置的方法是 先设置该字段不允许为NULL 然后在 标识 栏下拉列表中选 是 接着在 标识种子 标识递增量 栏中分别输入数字 用以指定初始编号和编号的增量 假如新建一个表 其中有一个字段名为 编号 其他字段自定 数据类型为decimal 精度 栏输入5 小数位数 栏输入0 标识 栏选 是 标识种子 栏输入100000 标识递增量 栏输入1 则输入记录时 此列不允许手工输入 而自动在第1条记录填入100000 第2条记录填入100001 第3条记录填入100002 以此类推 标识种子 等差数列的开始数字 标识递增量 等差数列的公差 是RowGuid 可以让SQLServer产生一个全局唯一的字段值 字段的类型必须是uniqueidentifier 有此属性的字段会自动产生字段值不需要用户输入 用户也不能输入 2020 3 21 38 4 设置主键约束 选中要作为主键的列 并单击工具栏上的 设置主键 按钮 主键列的前方将显示钥匙标记 设置主键 是为了保证每条记录的惟一性 如果要求表中的一个字段 或多个字段的组合 具有不重复的值 并且不允许为NULL 则应将这个字段 或字段组合 设置为表的主键 5 在表的各字段属性均编辑完后 单击工具栏上的保存按钮 出现 选择名称 对话框 输入表名 单击 确定 按钮 表就创建好了 创建好的 图书信息 表结构如图所示 2020 3 21 39 按照上面介绍的方法 可以创建 学生信息 和 租借信息 表 结构如图所示 学生信息 表结构 租借信息 表结构 2020 3 21 40 在通过右键菜单 可以执行下列操作 2020 3 21 41 2 修改表的结构 表创建之后 用户可以修改表的结构 改变表中原先定义的选项 这些修改包括增加列 删除列 改变表名和改变表的所有者等 一 使用ALTERTABLE语句修改表借助ALTERTABLE命令 能够以程序控制方式更改表的结构 其包括更改现有字段的属性 加入新字段 删除现有字段以及添加和删除约束等任务 但是同时只能做一种操作 比如 用户想修改一个字段的属性 又想删除另一个字段 则至少要使用两条ALTERTABLE命令才能完成 2020 3 21 42 1 修改字段属性ALTERTABLE的语法格式如下 ALTERTABLEtable nameALTERCOLUMNcolumn name new data type precision scale NULL NOTNULL 使用ALTERCOLUMN子句修改字段属性 要修改的字段名由column name给出 修改列的新的数据类型由new data type给出 例8 将lx数据库中的 教材 表中的字段 出版社 的数据类型由char 30 改为char 40 属性NULL改为NOTNULL 在查询分析器中的文本编辑区中输入如下代码并运行 USElxALTERTABLE教材ALTERCOLUMN出版社char 40 NOTNULL注意 一次只能更改一个字段的属性 如果要更改多个字段的属性 必须执行多条ALTERTABLE命令 2020 3 21 43 2 使用ADD子句增加字段通过ALTERTABLE语句中使用ADD子句 可以向表中增加新字段 新字段的定义方法与CREATETABLE语句中定义字段的方法相同 此时 ALTERTABLE语句格式如下 ALTERTABLEtable nameADD n 例9 为 图书信息 表增加两个字段 书籍页码 类型为smallint型 备注 类型为varchar 100 代码如下 USE学生图书借阅管理ALTERTABLE图书信息ADD书籍页码smallint 备注varchar 100 2020 3 21 44 3 使用DROP子句删除约束或字段 例10 将 图书信息 表中 书籍页码 和 备注 字段删除 代码如下 ALTERTABLE图书信息DROPCOLUMN书籍页码 备注 2020 3 21 45 例11 首先创建了一个doc exa表 然后使用sp help系统存储过程查看该表的结构信息 接下来 在该表中增加一个column b字段 该字段的数据类型是varchar 长度是20 这个新字段没有默认值 允许空 最后 再使用sp help系统存储过程查看修改过的该表的结构 在表中增加列 CREATETABLEdoc exa column aint GO 使用sp help系统存储过程查看该表的结构信息 EXECsp helpdoc exaGO 在该表中增加一个column b字段 ALTERTABLEdoc exaADDcolumn bvarchar 20 nullGO 使用sp help系统存储过程查看修改过的该表的结构 EXECsp helpdoc exaGO 2020 3 21 46 二 使用SQLServerEnterpriseManager修改表启动企业管理器 在数据库中的指定表上单击鼠标右键 在出现的快捷菜单中选择 设计表 进入表设计器窗口对表进行修改 在通过右键菜单 可以执行下列操作 2020 3 21 47 3 删除表的定义 删除表就是将表中数据和表的结构从数据库中永久性地去除 表删除之后 就不能再恢复该表的定义 删除表可使用DROPTABLE语句完成 它可同时删除多个表 其语法格式为 DROPTABLEtable name 例如 要删除当前数据库中的表 lx1 假设当前数据库中有表lx1 使用的T SQL语句为 USElx1DROPTABLElx1若要删除另外一个数据库中包含的表 应当在表名前面冠以数据库和所有者的名称 2020 3 21 48 示例 A 除去当前数据库内的表下例从当前数据库中删除titles1表 DRO