数据库应用技术系列第一讲 创建数据库和表

2020-10-09 10:11:13 浏览数 (1)

数据库应用技术系列第一讲

1 创建数据库快照

代码语言：javascript复制
CREATE DATABASE数据库快照名
    ON(
        NAME = 逻辑文件名,
        FILENAME = '操作系统文件名'
    ) 
    AS SNAPSHOT OF源数据库名

例：创建test1数据库的快照test1_s1

代码语言：javascript复制
CREATE DATABASE test1_s1
    ON
    (
        NAME=test1,
        FILENAME='d:SQL Server2012test1_s1.mdf'
    )
    AS SNAPSHOT OF test1

命令执行成功后，“对象资源管理器”中，展开数据库，会出现“数据库快照”。

2 删除数据库快照

删除数据库快照的方法和删除数据库的方法完全相同，可以使用界面方式删除，也可以使用命令方式删除

例如：

代码语言：javascript复制
DROP DATABASE test1_s1;

3 SQL Server数据类型

  • 字符型

SQL Server字符型包括两类：固定长度（char）或可变长度（varchar）字符数据类型。

（1）char[(n)]：定长字符数据类型，其中n定义字符型数据的长度，n在1～8 000，默认为1。当表中的列定义为char(n)类型时，若实际存储的串长度不足n时，则在串的尾部添加空格以达到长度n，所以char(n)的长度为n。

例如，某列的数据类型为char(20)，而输入的字符串为"ahjm1922"，则存储的是字符ahjm1922和12个空格。若输入的字符个数超出了n，则超出的部分被截断。

（2）varchar[(n)]：变长字符数据类型，其中，n的规定与定长字符型char中的n完全相同，但这里n表示的是字符串可达到的最大长度。

  • unicode字符型

unicode字符型包括nchar[(n)]和nvarchar[(n)]两类。nchar是固定长度Unicode数据的数据类型，nvarchar是可变长度unicode数据的数据类型，二者均使用UNICODE UCS-2字符集。

（1）nchar[(n)]：nchar[(n)]为包含n个字符的固定长度Unicode字符型数据，n的值在1～4 000，长度为2n字节。若输入的字符串长度不足n，将以空白字符补足。

（2）nvarchar[(n)]：nvarchar[(n)]为最多包含n个字符的可变长度Unicode字符型数据，n的值在1～4 000，默认为1。长度是所输入字符个数的两倍。

  • 文本型

文本型包括text和ntext两类，分别对应ASCII字符和Unicode字符。

（1）text类型：可以表示最大长度为231−1个字符，其数据的存储长度为实际字符数个字节。

（2）ntext类型：可表示最大长度为230-1个Unicode字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。

  • 二进制型

（1）binary [(n) ]：固定长度的n个字节二进制数据。n的取值范围为1～8 000，默认为1。binary(n)数据的存储长度为n 4个字节。若输入的数据长度小于n，则不足部分用0填充；若输入的数据长度大于n，则多余部分被截断。

（2）varbinary [(n)]：n个字节变长二进制数据。n取值范围为1～8 000，默认为1。varbinary(n)数据的存储长度为实际输入数据长度 4个字节。

  • 日期时间类型

日期时间类型数据用于存储日期和时间信息，日期时间数据类型包括date、time、datetime2和datetimeoffset。

（1）datetime：可表示的日期范围从1753年1月1日到9999年12月31日，精确度为0.03s（3.33ms或0.00333s）。例如，1～3ms的值都表示为0ms，4～6ms的值都表示为4ms。

用户给出datetime类型数据值时，日期部分和时间部分分别给出。

日期部分的表示形式常用的格式如下：

年 月 日

2001 Jan 20、2001 January 20

年 日 月

2001 20 Jan

月 日[,]年

Jan 20 2001、Jan 20,2001、Jan 20,01

月 年 日

Jan 2001 20

日 月[,]年

20 Jan 2001、20 Jan,2001

日 年 月

20 2001 Jan

年（4位数）

2001表示2001年1月1日

年月日

20010120、010120

月/日/年

01/20/01、1/20/01、01/20/2001、1/20/2001

月-日-年

01-20-01、1-20-01、01-20-2001、1-20-2001

月.日.年

01.20.01、1.20.01、01.20.2001、1.20.2001

时间部分常用的表示格式如下：

时:分

10:20、08:05

时:分:秒

20:15:18、20:15:18.2

时:分:秒:毫秒

20:15:18:200

时:分AM|PM

10:10AM、10:10PM

（2）smalldatetime：可表示从1900年1月1日到2079年6月6日的日期和时间，数据精确到分钟。即29.998s或更低的值向下舍入为最接近的分钟，29.999s或更高的值向上舍入为最接近的分钟。

（3）date：可以表示从公元元年1月1日到9999年12月31日的日期，date类型只存储日期数据，不存储时间数据，存储长度为3字节，表示形式与datetime数据类型的日期部分相同。

（4）time：只存储时间数据，表示格式为“hh:mm:ss[.nnnnnnn]”。hh表示小时，范围为0～23。mm表示分钟，范围为0～59。

（5）datetime2：也用于存储日期和时间信息。但是datetime2类型取值范围更广，日期部分取值范围从公元元年1月1日到9999年12月31日，时间部分的取值范围为00:00:00.000 000 0～23:59:59.999 999。

（6）datetimeoffset：也用于存储日期和时间信息，取值范围与datetime2类型相同。但datetimeoffset类型具有时区偏移量，此偏移量指定时间相对于协调世界时（UTC）偏移的小时和分钟数。

  • 时间戳型

标识符是timestamp。若创建表时定义一个列的数据类型为时间戳类型，那么每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。

  • 图像数据类型

标识符是image，它用于存储图片、照片等。实际存储的是可变长度二进制数据，介于0与231−1 (2 147 483 647)字节。该类型是为了向下兼容而保留的数据类型，微软推荐用户使用varbinary(MAX)数据类型来替代image类型。

  • 其他数据类型

（1）cursor：游标数据类型，用于创建游标变量或定义存储过程的输出参数。

（2）sql_variant：一种存储SQL Server支持的各种数据类型（除text、ntext、image、timestamp和sql_variant外）值的数据类型。sql_variant的最大长度可达8 016字节。

（3）table：用于存储结果集的数据类型，结果集可以供后续处理。

（4）uniqueidentifier：唯一标识符类型。系统将为这种类型的数据产生唯一标识值，它是一个16字节长的二进制数据。

（5）xml：用来在数据库中保存xml文档和片段的一种类型，但是此种类型的文件大小不能超过2 GB。

（6）hierarchyid：可表示层次结构中的位置。

4 计算列

计算列由可以使用同一表中的其它列的表达式计算得来。

  1. 该表达式可以是非计算列的列名、常量、函数、变量，
  2. 也可以是用一个或多个运算符连接的这些元素的任意组合。
  3. 表达式不能为子查询。

例：在AdventureWorks示例数据库中，Sales.SalesOrderHeader表的TotalDue列有以下定义：TotalDue AS Subtotal TaxAmt Freight。

5 完整性约束

实体完整性

Primary Key, Unique Key, Unique Index, Identity Column(同一表的元组之间)

值域完整性

Default, Check, Foreign Key, Data Type, Rule（列本身）

引用完整性

Foreign Key, Check, Triggers, Procedure （不同表的元组之间）

用户定义完整性

Rule, Triggers, Procedures, CREATE TABLE中的全部列级和表级约束

6 创建域完整性

（1）CHECK约束的定义与删除

  • 界面方式创建与删除CHECK约束

例如，在pxscj数据库的cjb表中，学生每门功课的成绩的范围为0～100，现对用户的输入数据要施加这一限制。

① 在“对象资源管理器”中展开“数据库”→“pxscj”→“表”，选择“dbo.cjb”，展开后选择“约束”，右键单击鼠标，在出现的快捷菜单中选择“新建约束”菜单项，如图所示 。

② 在打开的 “CHECK约束”对话框中，单击“添加”按钮，添加一个“CHECK约束”。在“常规”属性区域中的“表达式”栏后面单击 按钮（或直接在文本框中输入内容），打开“CHECK约束表达式”窗口，并编辑相应的CHECK约束表达式为“成绩>=0 AND成绩<=100”。

③ 在“CHECK约束”对话框中选择“关闭”按钮，并保存修改，完成“CHECK约束”的创建。此时若输入数据，如果成绩不是在0～100的范围内，系统将报告错误。

如果要删除上述约束，只需进入如图6.7所示的“CHECK约束”对话框，选中要删除的约束，单击“删除”按钮删除约束，然后单击“关闭”按钮即可。

  • 命令方式在创建表时创建CHECK约束

在创建表时可以使用CHECK约束表达式来定义CHECK约束，CHECK约束表达式语法格式如下：

代码语言：javascript复制
CHECK [ NOT FOR REPLICATION ] ( 逻辑表达式 )

关键字CHECK表示定义CHECK约束，如果指定NOT FOR REPLICATION选项，则当复制代理执行插入、更新或删除操作时，将不会强制执行此约束。

【例6.9】 创建一个表student，只考虑“学号”和“性别”两列，性别只能包含“男”或“女”。

代码语言：javascript复制
USE pxscj
GO
CREATE  TABLE  student
 (    
     学号char(6) 	NOT NULL,
     性别char(1) 	NOT NULL CHECK(性别IN ('男', '女'))
 )

【例6.10】 创建一个表student1，只考虑“学号”和“出生日期”两列，出生日期必须大于1980年1月1日，并命名CHECK约束。

代码语言：javascript复制
CREATE TABLE student1
 (    
     学号 	   char(6) 	NOT NULL,
     出生时间 datetime  	NOT NULL,
     CONSTRAINT  DF_student1_cjsj  CHECK(出生时间>'1980-01-01')
 )

如果指定的一个CHECK约束中，要相互比较一个表的两个或多个列，那么该约束必须定义为表的约束。

【例6.11】 创建表student2，有“学号”、“最好成绩”和“平均成绩”三列，要求最好成绩必须大于平均成绩。

代码语言：javascript复制
CREATE  TABLE  student2
 (    
    学号 	char(6)     NOT NULL,
    最好成绩 	int  	NOT NULL,
    平均成绩 	int  	NOT NULL,
          CHECK(最好成绩>平均成绩)
)

也可以同时定义多个CHECK约束，中间用逗号隔开。

  • 命令方式在修改表时创建CHECK约束

在使用ALTER TABLE语句修改表时也能定义CHECK约束。

定义CHECK约束的语法格式为：

代码语言：javascript复制
ALTER TABLE表名
      [ WITH { CHECK | NOCHECK } ] ADD
      [<列定义>]
      [CONSTRAINT约束名] CHECK (逻辑表达式)

【例6.12】 通过修改pxscj数据库的cjb表，增加“成绩”字段的CHECK约束。

代码语言：javascript复制
USE pxscj
GO
ALTER TABLE cjb     
    ADD CONSTRAINT cj_constraint  CHECK (成绩>=0 AND成绩<=100)
  • 利用SQL语句删除CHECK约束

使用ALTER TABLE语句的DROP子句可以删除CHECK约束。

语法格式：

代码语言：javascript复制
ALTER TABLE表名
    DROP CONSTRAINT约束名

【例6.13】 删除cjb表“成绩”字段的CHECK约束。

代码语言：javascript复制
ALTER TABLE cjb     
    DROP CONSTRAINT cj_constraint

int CHECK (成绩>=0 AND成绩<=100)

代码语言：javascript复制
- 利用SQL语句删除CHECK约束

使用ALTER TABLE语句的DROP子句可以删除CHECK约束。

语法格式：

```sql
ALTER TABLE表名
    DROP CONSTRAINT约束名

【例6.13】 删除cjb表“成绩”字段的CHECK约束。

代码语言：javascript复制
ALTER TABLE cjb     
    DROP CONSTRAINT cj_constraint
存储 编程算法 数据库 sql unicode

0 人点赞