注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Mr.Right

不顾一切的去想,于是我们有了梦想。脚踏实地的去做,于是梦想成了现实。

 
 
 

日志

 
 
关于我

人生一年又一年,只要每年都有所积累,有所成长,都有那么一次自己认为满意的花开时刻就好。即使一时不顺,也要敞开胸怀。生命的荣枯并不是简单的重复,一时的得失不是成败的尺度。花开不是荣耀,而是一个美丽的结束,花谢也不是耻辱,而是一个低调的开始。

网易考拉推荐

ACCESS SQL Create Table 及 字段数据类型Data Types  

2016-06-14 21:22:27|  分类: 编程 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

Creates a new table.

NOTE: The Microsoft Access database engine does not support the use of CREATE TABLE, or any of the DDL statements, with non-Microsoft Access database engine databases. Use the DAO Create methods instead.

Syntax

CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2type[(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

The CREATE TABLE statement has these parts:

Part

Description

table

The name of the table to be created.

field1field2

The name of field or fields to be created in the new table. You must create at least one field.

type

The data type of field in the new table.

size

The field size in characters (Text and Binary fields only).

index1index2

A CONSTRAINT clause defining a single-field index.

multifieldindex

A CONSTRAINT clause defining a multiple-field index.


Remarks

Use the CREATE TABLE statement to define a new table and its fields and field constraints. If NOT NULL is specified for a field, then new records are required to have valid data in that field.

A CONSTRAINT clause establishes various restrictions on a field, and can be used to establish the primary key. You can also use the CREATE INDEX statement to create a primary key or additional indexes on existing tables.

You can use NOT NULL on a single field or within a named CONSTRAINT clause that applies to either a single field or to a multiple-field named CONSTRAINT. However, you can apply the NOT NULL restriction only once to a field. Attempting to apply this restriction more than once results in a run-time error.

When a TEMPORARY table is created it is visible only within the session in which it was created. It is automatically deleted when the session is terminated. Temporary tables can be accessed by more than one user.

The WITH COMPRESSION attribute can be used only with the CHARACTER and MEMO (also known as TEXT) data types and their synonyms.

The WITH COMPRESSION attribute was added for CHARACTER columns because of the change to the Unicode character representation format. Unicode characters uniformly require two bytes for each character. For existing Microsoft Access databases that contain predominately character data, this could mean that the database file would nearly double in size when converted to the Microsoft Access format. However, Unicode representation of many character sets, those formerly denoted as Single-Byte Character Sets (SBCS) can easily be compressed to a single byte. If you define a CHARACTER column with this attribute, data will automatically be compressed as it is stored and uncompressed when retrieved from the column.

MEMO columns can also be defined to store data in a compressed format. However, there is a limitation. Only instances of MEMO columns that, when compressed, will fit within 4096 bytes or less, will be compressed. All other instances of MEMO columns will remain uncompressed. This means that within a given table, for a given MEMO column, some data may be compressed and some data may not be compressed.


------------------------------------------

Microsoft Access Data Types

 

The following table shows the Microsoft Access data types, data types used to create tables, and ODBC SQL data types.

Microsoft Access data type

Data type (CREATETABLE)

ODBC SQL data type

BIGBINARY[1]

LONGBINARY

SQL_LONGVARBINARY

BINARY

BINARY

SQL_BINARY

BIT

BIT

SQL_BIT

COUNTER

COUNTER

SQL_INTEGER

CURRENCY

CURRENCY

SQL_NUMERIC

DATE/TIME

DATETIME

SQL_TIMESTAMP

GUID

GUID

SQL_GUID

LONG BINARY

LONGBINARY

SQL_LONGVARBINARY

LONG TEXT

LONGTEXT

SQL_LONGVARCHAR[2] SQL_WLONGVARCHAR[3]

MEMO

LONGTEXT

SQL_LONGVARCHAR[2] SQL_WLONGVARCHAR[3]

NUMBER (FieldSize= SINGLE)

SINGLE

SQL_REAL

NUMBER (FieldSize= DOUBLE)

DOUBLE

SQL_DOUBLE

NUMBER (FieldSize= BYTE)

UNSIGNED BYTE

SQL_TINYINT

NUMBER (FieldSize= INTEGER)

SHORT

SQL_SMALLINT

NUMBER (FieldSize= LONG INTEGER)

LONG

SQL_INTEGER

NUMERIC

NUMERIC

SQL_NUMERIC

OLE

LONGBINARY

SQL_LONGVARBINARY

TEXT

VARCHAR

SQL_VARCHAR[1] SQL_WVARCHAR[2]

VARBINARY

VARBINARY

SQL_VARBINARY

The following table shows limitations on Microsoft Access data types.

Data type

Description

BINARY, VARBINARY, and VARCHAR

Creating a BINARY, VARBINARY, or VARCHAR column of zero or unspecified length actually returns a 510-byte column.

BYTE

Even though a Microsoft Access NUMBER field with a FieldSize equal to BYTE is unsigned, a negative number can be inserted into the field when using the Microsoft Access driver.

CHAR, LONGVARCHAR, and VARCHAR

A character string literal can contain any ANSI character (1-255 decimal). Use two consecutive single quotation marks ('') to represent one single quotation mark (').

Procedures should be used to pass character data when using any special character in a character data type column.

DATE

Date values must be either delimited according to the ODBC canonical date format or delimited by the datetime delimiter ("#"). Otherwise, Microsoft Access will treat the value as an arithmetic expression and will not raise a warning or error.

For example, the date "March 5, 1996" must be represented as {d '1996-03-05'} or #03/05/1996#; otherwise, if only 03/05/1993 is submitted, Microsoft Access will evaluate this as 3 divided by 5 divided by 1996. This value rounds up to the integer 0, and since the zero day maps to 1899-12-31, this is the date used.

A pipe character (|) cannot be used in a date value, even if enclosed in back quotes.

GUID

Data type limited to Microsoft Access 4.0.

NUMERIC

Data type limited to Microsoft Access 4.0.

  评论这张
 
阅读(208)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016