SQL——SQL的数据类型与模式

2018-04-05 本文已影响0人薛定谔与猫的故事

在前面的《数据定义》中我们有提到过几种固有数据类型，除此之外，SQL还有支持一些其他的固有数据类型，甚至自定义类型。

SQL中的日期和时间类型

date：日历日期，包括年（四位）、月、日。

time：一天中的时间，包括小时、分和秒。可以用变量time(p)来表示秒的小数点后的数字位数（默认值为0）。通过指定time with timezone，还可以把时区信息连同时间一起存储。

timestamp（时间戳）：date和time的结合，可用变量timestamp(p)来表示秒的小数点后的数字位数（默认值为6）。如果指定with time zone，则时区信息也会被存储。

为了更为直观的展现三者，它们各自形式是这样的

date ‘2018-04-05’
time ‘08:34:00’
timestamp ‘2018-04-05 08:34:00’

类型的使用跟其他类型无差别
例如：

time_slot TIMESTAMP WITH TIME ZONE

而mysql是不支持这样子写的，那么MySQL要如何使用with time zone呢？
mysql time zone 1
mysql time zone 2

我们可以利用cast e as t（即所谓的强制转换）形式的表达式将一个字符串（或者字符串表达式）e转换为类型t，其中t是date，time、timestamp之一。字符串必须符合正确的格式。

另外我们也可以使用extract（filed from d），从date或time值d中提取出单独的域，这里的域可以是year，month，day，hour，minute或者second中的一种。时区信息可以用timezone_hour和timezone_minute来提取。

#在表中提取出年份为2018 的日期和时间
SELECT time_slot
FROM temp
WHERE extract(YEAR FROM temp.time_slot)=2018;

原表

结果表

SQL定义了一些函数来获取当前日期或者时间。例如，current_date返回当前日期，current_time返回当前时间（带有时区），还有localtime返回当前的本地时间（不带时区）。时间戳(日期加上时间）由current_timestamp（带有时区）和local_timestamp（本地日期和时间，不带时区）返回。

#例如我们向temp表中插入一个当前时间
INSERT INTO temp VALUES (current_timestamp);

SQL还支持interval(间隔)类型，它允许在日期、时间和时间间隔上进行计算，例如假设x和y都是date类型，那么x-y就是时间间隔类型，其值是从日期x到日期y间隔的天数。类似的，也可以加得到新的日期。

默认值

SQL允许使用default为属性指定默认值，例如，下面的create table 语句：

CREATE TABLE temp_t(
  tol_budget NUMERIC(3,0) DEFAULT 0
);

这个关系中，属性tol_budget的默认值为0，这样当一个元组被插入表中是，即使插入的元素并没有tol_budget属性，元组的tol_budget的属性值也不为null，而是0；

创建索引

在关系上所创建的索引是一种数据结构，它允许数据库系统高效地找到关系中的那些在索引属性上取给定值得元组，而不用扫描关系中的元组。

创建索引：索引是建立在一个属性列表上的

create index stu_id_index on student(id);

我们给student的id添加索引。一般情况，但我们要找到id为stu-0001的学生，我们需要遍历整个关系，尽管，期间已经匹配到了。而在创建索引的情况下，如果用户提交的SQL查询可以从索引中获益时（即查询的时间更短），那么SQL查询处理器就会自动使用索引，一步到位找到stu-0001的学生元组，而不用遍历关系的所有元组。

大对象类型

许多数据库应用需要存储可能很大（KB级）的属性，例如一张照片；或者非常大的属性（MB或者GB），例如高清图片或者视频片段。因此SQL提供了字符数据的大对象数据类型（clob）和二进制数据的大对象数据类型（blob）。lob是代表“large object”的意思。
例如我们可以声明属性：

book_review clob(10KB),
image blob(10MB),
movie blob(2GB)

对于大对象的结果元组而言，把整个大对象放入内存是非常低效的。相反，一个应用通常用一个SQL查询来检索出一个大对象的“定位器”，然后在宿主语言中用这个定位器来操作对象，应用本身也是用宿主语言写的。例如，在jdbc允许获取一个定位器而不是整个大对象，然后用这个定位器一点一点地取出这个大对象，而不是一次取出全部，这很像用一个read函数调用从操作系统文件中读取数据。

用户定义的类型

SQL支持两种形式的用户自定义数据类型：独特类型（distinct type），结构化数据类型（structured data type）。

独特类型

对100人民币和100美元，直接进行比较，显然是程序上的错误。正确的化，应该是先进行转换，化为同种类型，这里人民币和美元是两种币种，是两种不同的数据类型，是无法直接比较的。为了支持这种情况，SQL提供了独特类型的概念，可用下列形式定义：

create type dollors as numeric(12,2) final;
create type pounds as numeric(12,2) final;

使用用户定义类型：
create table department（
dept_name varchar(20),
building varchar(15),
budget dollors
）;

这样的话，当你讲一个pounds赋值给budget是错误的，或者类型numeric也是，表达式department.budget+20是错误。如果要操作，应先进行类型的强制转换，例如：

cast(department.budget to numberic(12,2));

利用drop或alter来删除或者修改类型的属性。

与用户定义类型相似的概念：域（domain），它可以在基本类型上添加约束。定义一个域，如下：

create domain dollors as numeric(12,2) not null

域与用户定义类型相比，除了具备添加约束特性，还具备非强类型特性，即一个域的类型可以被赋值给另一个域，只要基本类型是相容的。

当把check子句应用到域上的时候，允许指定一个谓词，被声明为来自该域的任何变量都必须满足这个谓词。例如check子句可以保证教师工资域中只允许出现大于给定值的值：

CREATE DOMAIN salary_year NUMERIC(12,2)
  CONSTRAINT salary_value_test CHECK (VALUE >=29000.00);

constraint salary_value_test子句是可选的，它用来将该约束命名为salary_value_test。系统用这个名字来指定一个更新违反了哪个约束。