【SQL server速成之路】数据库和表（二）

🎉个人主页：这个昵称我想了20分钟
✨往期专栏：【速成之路】jQuery

---

🎖️专栏：【速成之路】SQL server
🔓往期回顾：
【SQL server速成之路】数据库基础
【SQL server速成之路】数据库和表（一）

🎆一、创建表

1.数据类型

  创建表的字段时，必须为其指定数据类型。字段的数据类型决定了数据的取值、范围和存储格式。字段的数据类型可以是SQL Server提供的系统数据类型，也可以是用户定义数据类型。SQL Server 2012提供了丰富的系统数据类型，将其列于下表中。

1．整数型
  整数包括bigint、int、smallint和tinyint这4类，从标识符的含义就可以看出，它 们的表示数范围逐渐缩小。下表列出了这4类整数的精度、长度和取值范围。

2．精确数值型
  精确数值型数据由整数部分和小数部分构成，其所有的数字都是有效位，能 够以完整的精度存储十进制数。精确数值型包括decimal 和 numeric两类。在SQLServer 2012中，这两种数据类型在功能上完全等价。
   声明精确数值型数据的格式是numeric | decimal(p[,s])，其中p为精度，s为小数位数，s的缺省值为0。例如指定某列为精确数值型，精度为6，小数位数为3， 即decimal(6,3)，那么若向某记录的该列赋值56.342689时，该列实际存储的是56.3427。decimal和numeric可存储从 -1038 +1 到 1038 –1 的固定精度和小数位的数字数据，它们的存储长度随精度变化而变化，最少为5字节，最多为17字节。
3．浮点型
  浮点型也称近似数值型，这种类型不能提供精确表示数据的精度。当使用这种类型来存储某些数值时，有可能会损失一些精度，所以它可用于处理取值范围非常大且对精确度要求不是十分高的数值量，如一些统计量。有两种近似数值数据类型：float[(n)]和real，两者通常都使用科学记数法表示数据。科学记数法的格式为：
尾数E阶数
其中，阶数必须为整数。
例如，9.8431E10，-8.932E8，3.68963E-6等都是浮点型数据。
real和float类型数据的精度、长度和表数范围列于下表中。

4. 货币型
  SQL Server提供了两个专门用于处理货币的数据类型：money和smallmoney，它们用十进制数表示货币值。这两种类型数据的精度、长度和表数范围列于下表中。

5．位型
  SQL Server 2012中的位（bit）型只存储0和1，长度为一个字节。当为bit类 型数据赋0时，其值为0，而赋非0（如100）时，其值为1。
  字符串值TRUE和FALSE可以转换为以下bit 值：TRUE转换为1，FALSE转换为0。
6．字符型
  字符型数据用于存储字符串，字符串中可包括字母、数字和其它特殊符号（如#、@、&等等）。在输入字符串时，需将串中的符号用单引号或双引号括起 来，如’abc’、“Abc<Cde”。
  SQL Server字符型包括两类：固定长度(char)、可变长度(varchar)字符数据类型。
7．Unicode字符型
  Unicode是“统一字符编码标准”，用于支持国际上非英语语种的字符数据的存储和处理。SQL Server的Unicode字符型可以存储Unicode标准字符集定义的各种字符。Unicode字符型包括nchar[(n)]和nvarchar[(n)]两类。nchar是固定长度Unicode数据的数据类型，nvarchar是可变长度Unicode数据的数据类型，二者均使用UNICODE UCS-2字符集。
8．文本型
  当需要存储大量的字符数据，如较长的备注、日志信息等等，字符型数据的最长8000个字符的限制可能使它们不能满足这种应用需求，此时可使用文本型数据。
  文本型包括text和ntext两类，分别对应ASCII字符和Unicode字符，其可以表示的最大长度和存储字节数列于下表中。

9．二进制型

（1）binary[(n)]：固定长度的n个字节二进制数据。N的取值范围为1~8000，默认值为1。binary(n)数据的存储长度为n+4字节。若输入的数据长度小于n，则不足部分用0填充；若输入的数据长度大于n，则多余部分被截断。
输入二进制型数据时，实际上用十六进制表示，如0xFF,0x12A0。
（2）varbinary[(n)]：n个字节变长二进制数据。n取值范围为1~8000，默认值为1。varbinary(n)数据的存储长度为实际输入数据长度+4个字节。

10．日期时间类型

（1）datetime：datetime类型可表示的日期范围从1753年1月1日到9999年12月31日的日期和时间数据，精确度为1/300s（3.33毫秒或0.00333秒），例如1 到3毫秒的值都表示为0毫秒，4到6毫秒的值都表示为4毫秒。 （2）smalldatetime：smalldatetime类型数据可表示从1900年1月1日到2079年6月6日的日期和时间，数据精确到分钟。
（3）date：date类型数据可以表示从公元元年1月1日到9999年12月31日的 日期，date类型只存储日期数据，不存储时间数据，存储长度为3个字节，表示形式与datetime数据类型的日期部分相同。
（ 4 ） time ： time 数据类 型 只存储时间数据 ， 表 示 格式为
“hh:mm:ss[.nnnnnnn]”。hh表示小时，范围为0到23。mm表示分钟，范围为0 到59。ss表示秒数，范围为0到59。n是0 到7位数字，范围为0到9999999，表示 秒的小数部分，即微秒数。
（5）datetime2：datetime2数据类型和datetime类型一样，也用于存储日期和时间信息。
（6）datetimeoffset：datetimeoffset数据类型也用于存储日期和时间信息， 取值范围与datetime2类型相同。

11．时间戳型
  标识符是timestamp。若创建表时定义一个列的数据类型为时间戳类型，那 么每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。
12．图像数据类型
  标识符是image，它用于存储图片、照片等。实际存储的是可变长度二进制数据，介于 0 与 231-1 (2,147,483,647) 字节之间。在SQL Server 2012中该类型是为了向下兼容而保留的数据类型。微软推荐用户使用varbinary(MAX)数据类型 来替代image类型。
13．其它数据类型
  除了上面所介绍的常用数据类型外，SQL Server 2012还提供了其它几种数据类型：cursor、sql_variant、table、uniqueidentifier、xml和hierarchyid。

• cursor：是游标数据类型，用于创建游标变量或定义存储过程的输出参数。
• sql_variant：是一种存储SQL Server支持的各种数据类型（除text、ntext、
  image、timestamp 和 sql_variant 外）值的数据类型。sql_variant的最大长度可 达8016字节。
• table：是用于存储结果集的数据类型，结果集可以供后续处理。
• uniqueidentifier：是唯一标识符类型。系统将为这种类型的数据产生唯一标识值，它是一个16字节长的二进制数据。
• xml：是用来在数据库中保存xml文档和片段的一种类型，但是此种类型的文件大小不能超过2GB。

  学生（xs）表为例，包含借书证号、姓名、性别、出生时间、专业和借书量等字段。

（1）借书证号。存放8个符号，用作主键，不能为空。确定：char(8)，NOT NULL，PRIMARY KEY。
（2）姓名。考虑姓名最多可能4个汉字（超过4个汉字存放需要缩减），一个汉字占用2个字节，不能为空。确定：char(8)，NOT NULL。
（3）性别。考虑男和女两种情况，可以确定以bit类型存放，用1表示男，0表示女。默认为1（因为男性占多数），这样是男性就不需要输入。本书是为了让读者通过它熟悉bit类型的操作。实际使用时也可把该字段确定为char(2)。
（4）出生时间。因为可能需要据该字段计算如年龄等，所以应该确定为Date类型。
（5）专业。考虑最多可能6个汉字，确定：char(12)。
（6）借书量。考虑该字段内容需要数值增减运算，可确定为int类型。当然也可为smallint类型和tinyint类型，因为借书量一般不超过255本。
（7）照片。为了在借书时核对照片，也可在xs表中增加该字段。因为照片数据量大而且不同人差别较大，为了节省存储空间，确定：varbinary(MAX)，NULL。NULL表示有人可以没有照片。

  这样图书管理数据库xsbook中学生表（xs）如下表所示，其余类似。

2.界面方式操作表

1．创建表
  以下是通过“对象资源管理器”创建表xs的操作步骤：

  （1）启动“SQL Server Management Studio”在→“对象资源管理器”中，
展开“数据库”→右击“xsbook”数据库菜单下的“表”选项，在弹出的快捷菜
单中，选择“新建表(N)…”菜单项，打开“表设计器”窗口。
   （2）在“表设计器”窗口中，根据已经设计好的xs的表结构分别输入或选择各列的名称、数据类型、是否允许为空等属性。根据需要，可以在列属性表格填入相应内容。如图所示。

  （3）在“借书证号”列上右击鼠标，选择“设置主键”菜单项，选择“设为
主键”选项，如图所示。

  （4）在列属性窗口中的“默认值和绑定”和“说明”项中分别填写各列的默认值和说明。
  （5）在表的各列的属性均编辑完成后，单击工具栏中的“ ”按钮，出现 “选择表名”对话框。在“选择表名”对话框中输入表名“xs”，单击“确定”按钮即可创建xs表。在“对象资源管理器”窗口中可以找到新创建的xs表，如图所示。

2．修改表结构
  在创建了一个表之后，使用过程中可能需要对表结构进行修改。对一个已存在的表可以进行的修改操作包括：更改表名、增加列、删除列、修改已有列的属性（列名、数据类型、是否为空值等）。
  在SQL Server 2012中，当用户使用界面方式修改表的结构（如添加列、修 改列的数据类型等）时，必须要删除原来的表，再重新创建新表才能完成表的更 改。如果强行更改会弹出如图所示的对话框。

（1）更改表名
  SQL Server中允许改变一个表的名字，但当表名改变后，与此相关的某些对象如视图，以及通过表名与表相关的存储过程将无效。因此，一般不要更改一个已有的表名，特别是在其上定义了视图或建立了相关的表。
  例如，将前述所创建的xs表的表名改为student，更改表名的操作步骤如下：在“对象资源管理器”中选择需要更名的表xs，右击鼠标，在弹出的快捷菜单上选择“重命名”菜单项，输入新的表名student，按下回车键即可更改表名。

（2）增加列
  当原来所创建的表中需要增加项目时，就要向表中增加字段。例如若在表中需要登记学生是否有逾期未还的书籍等，就要用到增加字段的操作。以向表xs中添加一个“逾期未还书数”字段为例说明向表中添加新字段的操作过程，“逾期未还书数”字段为微整型tinyint，允许为空值。

（3）删除列
  在表设计器窗口中需删除的列（例如xs表中删除“逾期未还书数”列），右击鼠标，在弹出的快捷菜单上选择“删除列”菜单项，该列即被删除。

（4）修改列
  表中尚未有记录值时，可以修改表结构，如更改列名、列的数据类型、长度和是否允许空值等属性。但当表中有了记录后，建议不要轻易改变表结构，特别不要改变数据类型，以免产生错误。

① 具有以下特性的列不能被修改：

• 数据类型为timestamp的列
• 计算列
• 全局标识符
• 用于索引的列（但若用于索引的列为varchar、nvarchar或varbinary数据类- 型时，可以增加列的长度）
• 用于主键或外键约束的列
• 用于CHECK或UNIQUE约束的列
• 关联有默认值的列

② 当改变列的数据类型时，要求满足下列条件：

• 原数据类型必须能够转换为新数据类型。
• 新类型不能为timestamp类型。

3.删除表
  当一个数据表不再需要时，可将它删除。删除一个表时，表的定义、表中的所有数据以及表的索引、触发器、约束等均被删除掉，因此执行删除表操作时一定要格外小心。注意，不能删除系统表和有外键约束所参照的表。
设需将xsbook数据库中的表xs删除，操作过程为：
  在“对象资源管理器”中展开“数据库”→“xsbook”→“表”→选择要删除的表dbo.xs，右击鼠标，在弹出的快捷菜单上选择“删除”菜单项。系统弹出“删 除对象”窗口。单击“确定”按钮，即可删除选xs表。

3.命令方式操作表

1