7.1The ISO SQL Data Type 国际标准组织数据类型

In this section we introduce the data types defined in the SQL standard.

在这一节中，我们介绍数据类型（在ISO标准中定义的）。 

We start by defining what constitutes a vailed identifer in SQL.

identifer /ai‘dentifaiə/ n. 标识符,  识别码；

我们开始通过使用SQL定义什么组成一个有效标识符。

 

7.1.1  SQL Identifiers  

SQL 标识符

SQL identifiers are used  to  identify objects in the database, such as table names, views names, and columns.

identify /ai’dentifai/ vt.  If you can identify someone or something,

you are able to recongnize them or distinguish them from other.  识别；

SQL 标识符是在数据库中用来识别对象， 例如：表名，视窗名，和列。

The characters that can be used in a user-defined SQL identifier must appear in a characteəur set.

user-defined  用户定义的

在用户定义的SQL 标识符中被用来使用的字符必须出现在字符集中。 

The ISO standard provides a default character set, which consists of the uppercase letters A...Z,

the lowercase letters a...z, the digits 0...9, and the underscore (_) character.

ISO 标准提供了一个默认的字符集，包括大写字母A...Z，小写字母a...z，数字0...9, 和下划线字符。

The following restrictions are imposed on an identifier:

impose /'impəuz/ vt. If you impose  something on people, you use your authority to force them to accept it.  强制实行

下列条件被强加于每个标识符。 

@ an identifier can be no longer than 128 characters(most dialets have a much lower limit than this).

一个标识符不能长于128个字符（绝大多数方言比这个长度有一个更短的限制）。

@ an identifier must start with a letter.

一个标识符必须用一个字母作为开始。 

@ an identifier cannot contain spaces.

一个标识符不能含有空格。 

TABLE 7.1  ISO SQL data types（国际标准化组织SQL数据类开型）

 

DATA TYPE	DECLARATION（声明）
boolean	BOOLEAN
Character	CHAR	VARCHAR
Bit	BIT	BIT VARYING
Exact	Numeric	Decimal		Integer	Small-int
Approximate	Float	Real		Double precision
Datetime	DATE	REAL		TIMESTAMP
Interval	INTERVAL
Large object	Character large object		Binary large object

7.1.2 SQL Scalar 

scalar /skeilə/ n.   a quantity,  such as time or temperature, that has magnitude but not  direction.  标量

Table 7.1 shows the SQL scalar data type defined in the ISO standard.

表7.1 显示SQL标量数据类型（ISO标准所定义）

Sometimes, for manipulation and conversion purpose,

有时，为了检索和转换的目的， 

the data types character and bit are collectively referred to as a string data types,

数据类型（字符和比特）集合起来被参考作为数据串类型，

and exact numeric and approximate numeric are referred to as numeric data types, as they share similar properties.

精确数据和大约数据被参考为数值类数据类型， 因为他们共享相似的性能。

conversion/.../ n. 转换，转变; an event that results in a transformation  

The SQL standard now also defines both character large objects and binary large objects,although we defer discussion of these data type until Section 29.4.

defer/di‘fæ/ vt. If you defer an event or action,you arrange for it to happen at a later date, rather than immediately or at the previously planned time.推迟

SQL 标准也定义了大字符对象和大的二进制对象，尽管我们推迟讨论这些数据类型（直到章节29.4 ）。

 

Boolean data

布尔数据

 

Boolean data consists of the distinct truth value TRUE and FALSE.

布尔数据由严格真值“真”和“假”组成。

Unless prohibited by a NOT NULL constraint, boolean data also supports the UNKNOWN truth values as NULL value. 

prohibit/prə’hibit/ vt. If a low or someone in authority prohibits something, they forbid it or make it illegae. 禁止

除非“非空”约束被禁用，布尔代数也支持“未知值”作为空值。

All boolean data type values and SQL truth values are mutually comparable and assignable.

 mutually /'mju:tʃuəli/ vt. 互助的；

comparable /'kɔmɔpərəbl/ adj. 可比较的；

assignable/ə'sainəbl/adj. 可过户的；可转化的；

The value TRUE is greater than the value FALSE,

所有的布尔数据类型和SQL 真值都是可以互相比较和可转化的。 

and  any comparison involving the NULL value or an UNKNOWN truth value returns an UNKNOWN result.

comparision /kəm'pæris(ə)n/ n. when you make a comparison,

you consider two or more things and discover the differences between them. 比较；比喻；

和任何涉及“空值”或者“未知值”的比较，返回一个未知结果。

Character data

字符数据

 

Character data consists of a sequence of characters from an implementation-defined character set,

that is, it is defined by the vender of the particular SQL dialet.

implementation-defined 执行定义；

字符数据同由一个执行定义字符集（被特别的SQL方言发布商所定义的）组成一个有序的字符。

Thus, the exact characters that can appear as data values in a character type column will vary.

ASCII and EBCDIC are two sets in common use today.

EBCDIC: Extended Binary Coded Decimal Interchange code. 广义二进制编码的十进制交换码；

ASCII 和EBCDIC 是今天通用的两个集。

The format for specifying a character data type is :

CHARACTER [VARYING][length]

　　CHARACTER can be abbreviated to CHAR and

　　CHARACTER VARYING to VACHAR

说明一个字符数据类型的格式为：

CHARACTER [VARYING][length]

　　字符串被缩写为CHAR和可变字符串被缩写为VACHAR。

When a character string column is defined,

当列被字符串定义时，

a length can be specified to indicate the maximum number of characters that the column can hold (default length is 1) .

列被说明的长度暗示了列所能贮储最大字符数目（默认长度为1）。

 

A character string may be defined as having a fixed or varying length.

一个字符串可能被定义为固定长度或者可变长度。

If the string is defined to be a fixed length and we enter a string with fewer characters than this length,

the strings is padded with blanks on the right to make up the required size.

 pad//t.if you pad or pad out a piece of writing or a speech with unnecessary words or pieces of information, 

you include them to make it longer and hide the fact you dont have very much to say. (添加不必要的词句或信息）拉长（文章或演讲）

如果一个串被 定义社固定长度，输入这个串的应该小于该长度的字符数，串被用空隔向右填充已达到所需尺寸。

If the string is defined to be of a varying length and we enter a string with fewer characters than this lenth, only those characters entered are stored, thereby using less space.

如果串被定义为可变长度，我们输入一个小于该长度的字符，仅仅那些被输入的字符被储存，从而使用更少的空间。

For example, The number column branchNo of the Branch table,which has a fixed length of four characters, is declared as:

BranchNo CHAR(4)

例如： Branch 表中branchNo列的数目，（有四个字符的固定长度），被申明为：BranchNo CHAR(4)

The column address of the PrivateOwner table, which has a variable number of characters up to a maximum of 30, is declared as:

address VARCHAR = (30)

 PrivateOwner 表的列地址（有一个可变字符数可达到最大值30），被申明：

address VARCHAR = (30)

 

Bit data 

比特数据

The bit data type is used to define bit strings, that is, a sequence of binary digits (bits), each having either the value 0 or 1. 

比特数据类型被用来定义比特串，那是， 一个有序的二进制(位)，每一个有值0或1。

The format for specifying the bit data type is similar to that of the character data type:

BIT [VARYING][length]

For example, to hold the fixed length string "0011", we declare a column bitstring, as:

bitstring BIT (4)

 

7.1.3 Exact Numeric Data 

 

 精确的数值类数据

The exact numeric data type is used to define numbers with an exact representation.

精确的数值类数据被用来定义伴随精确表述的数目。

The number consists of digits, an optional decimal point, and an optional sign.

数字由数字，可选的点分十进制，可选符号组成。

An exact numeric data type consists of  a precision and a scale.

precision //  n.精度

scale//  N-COUNT.  A scale is a set of levels or numbers which are used in a particular system of measuring things  or are used when comparing things.  等 级。

一个精确的数值型数据由一个精度一个进位制组成。

The precision gives the total number of significant decimal digits,

that is,

the total number of digits, including decimal places but excluding the point itself. 

精度指示所有非常重要的十进制数字个数，那就是说，所有的数字个数，包括小数位，但不包括小数点本身。

The scale gives the total number of decimal places.

等级指示所有小数位的个数。

For example, the exact numeric  value -12.345 has precision 5 and scale 3. 

例如，精确数值型值-12.345 有5个精度和3个等级。

A special case of exact numeric occurs with integers.

一个特别的精确数值的例子发生是整型。 

There are several ways of specifying an exact numeric data type: 

NUMERIC [precision [, scale] ]

DECIMAl  [precision [, scale] ]

INTEGER

SMALLINT 

　　INTEGER can be abbreviated to INT and DECIMAL to DEC 

 

NUMERIC and DECIMAL store numbers in decimal notation.

 

decimal // adj. a decimal system involves counting in units of ten. 十进制；

　　　　N-COUNT  a decimal is a fraction that is written in the form of a dot followed by one or more numbers in which represent tenths, hundredths, and so on,  小数

notation //  n-var. a system of notation is a set of written symbols that are used to represent something such as music or mathematics。符号

The default precision is implement-defined.

默认谓词是由现实定义。

INTEGER is used for large positive or negative whole numbers.

positive//  adj. a positive number is greater than 0. 正的（数字）；

negative// adj. a negative number, quantity, or measurement is less than zero. 负的（数字）；

整型被使用为大的正整数和负整数。

SMALLINT is used for small positive or negative whole numbers. 

短整型被使用为小的正整数和负整数。

 

By specifying this data type, less storage space can be reserved for the data.

 通过说明这些数据类型，比较少的空间能够为数据预留。

For example, the maximum absolute value that can be stored with SMALLINT might be 32767.

absolute // adj.  absolute rules and principles are believed to be true, right, or relevant in all situation. 绝对；

例如，短整型能够贮储的最大绝对值应该是32767。

 

The column rooms of the propertyForRent Table, which represents the number of rooms in a property, is obviously a small integer and can be declared as:

 rooms SMALLINT

PropertyForRent 表的室数列，（代表在房子里有室的数目），它明显是一个短整型，能够被申明名：

rooms SMALLINT

The column salary of the staff table can be declared as:

salary DECIMAL(7,2)

which can handle a value up to 99,999.99.

员工薪资列能够被申明为：

salary DECIMAL(7,2)

它能够处理数据的上限值为99,999.99

 

Approximate numeric data

大约数值类数据

 

The approximate numeric data type is used for defining numbers that do not have an exact representation, such as real numbers.

real numbers.  n. a number expressible as a limit of rational numbers.一个数字表现作为有限的有理数。 实数。

大约数值类数据被用来定义数字（它们不拥有精确的表示）， 例如：实数。

Approximate numeric, or floating point, notation is similar to scientific notation,

in which a number is written as a mantissa times some power of ten (the exponent).

 mantissa /mæn‘tisə/ n. 尾数。

 exponent /ik’pəunənt/ n. 指数

 

大约数，浮点数，记法与科学记数法相似，（一个数字被写为尾数乘以10的级数（指数））。

 

For example, 10E3, +5.2E6,-0.2E4.

There are several ways of specifying an approximate numeric data type:

FLOAT [precision]

REAL 

DOUBLE PRECISION 

 

The precision controls the precision of the mantissa.

The precision of REAL and DOUBLE PRECISION is implementation-defined.

Datetime data   

 

The datetime data type is used to define points in time to a certain degree of accuracy.

accuracy // 精确度， 准确性；

日期类数据类型数据被使用来定义（以某一精确度为精度）时间点。

Examples are dates, times, and times of day. 例如日期，时间，和一天中的时间段。 

The ISO standard subdivides the datetime data type  into YEAR, MONTH, DAY, HOUR, MINITE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE.

The latter fields specify the hour and minute part of time zone offset from Universal Coordinated Time (which used to be called GreenWich Mean Time).

Three types of datime data type are supported:

DATE

TIME[ time precision] [with time zone]

TIMESTAMP[timeprecision][WITH TIME ZONE]

 

DATE is used to store calendar dates using YEAR, MONTH, and DAY field.