SQL Advanced Apps (data type)

Source: Internet
Author: User

Microsoft Access Data type
Data Type Description Storage
Text Used for text or a combination of text and numbers. A maximum of 255 characters.
Memo

Memo is used for a larger number of text. Stores a maximum of 65,536 characters.

Note: The memo field cannot be sorted. However, they are searchable.

Byte Allow numbers from 0 to 255. 1 bytes
Integer Allows a number between 32,768 and 32,767. 2 bytes
Long Allow all numbers between 2,147,483,648 and 2,147,483,647 4 bytes
Single Single-precision floating point. Handles most decimals. 4 bytes
Double Double-precision floating point. Handles most decimals. 8 bytes
Currency

Used for currency. Supports 15-bit, plus 4 decimal places.

Tip: You can choose which country's currency to use.

8 bytes
AutoNumber The AutoNumber field automatically assigns a number to each record, usually starting at 1. 4 bytes
Date/time For date and time 8 bytes
Yes/no

A logical field that can be displayed as yes/no, True/false, or on/off.

In code, use constant True and False (equivalent to 1 and 0)

Note: Null values are not allowed in the Yes/no field

1 bits
Ole Object can store pictures, audio, video, or other BLOBs (Binary Large OBjects) Up to 1GB
Hyperlink Contains links to other files, including Web pages.
Lookup Wizard Allows you to create a list of options that can be selected from the following list. 4 bytes
MySQL data type

In MySQL, there are three main types: text, numbers, and date/time types.

Text Type:
Data Type Description
CHAR (size) Holds a fixed-length string (which can contain letters, numbers, and special characters). Specifies the length of the string in parentheses. A maximum of 255 characters.
VARCHAR (size)

Saves variable-length strings (which can contain letters, numbers, and special characters). Specifies the maximum length of the string in parentheses. A maximum of 255 characters.

Note: If the value is longer than 255, it is converted to the TEXT type.

Tinytext Holds a string with a maximum length of 255 characters.
TEXT Holds a string with a maximum length of 65,535 characters.
Blob For BLOBs (Binary Large OBjects). Store up to 65,535 bytes of data.
Mediumtext Holds a string with a maximum length of 16,777,215 characters.
Mediumblob For BLOBs (Binary Large OBjects). Store up to 16,777,215 bytes of data.
Longtext Holds a string with a maximum length of 4,294,967,295 characters.
Longblob For BLOBs (Binary Large OBjects). Store up to 4,294,967,295 bytes of data.
ENUM (x,y,z,etc.)

Allows you to enter a list of possible values. The maximum of 65,535 values can be listed in the ENUM list. If the inserted value does not exist in the list, a null value is inserted.

Note: These values are stored in the order that you entered them.

Possible values can be entered in this format: ENUM (' X ', ' Y ', ' Z ')

SET Similar to ENUM, set can contain up to 64 list items, but set can store more than one value.
Number type: -128 to 127 conventional. 0 to 255 unsigned *. Specify the maximum number of digits in parentheses.
data type description
TINYINT (size)
SMALLINT (size) -32768 to 32767 general. 0 to 65535 unsigned *. Specify the maximum number of digits in parentheses.
mediumint (size) -8388608 to 8388607 normal. 0 to 16777215 unsigned *. Specify the maximum number of digits in parentheses.
INT (size) -2147483648 to 2147483647 general. 0 to 4294967295 unsigned *. Specify the maximum number of digits in parentheses.
BIGINT (size) -9223372036854775808 to 9223372036854775807 general. 0 to 18446744073709551615 unsigned *. Specify the maximum number of digits in parentheses.
float (size,d) A small number with a floating decimal point. Specify the maximum number of digits in parentheses. Specifies the maximum number of digits to the right of the decimal point in the D parameter.
double (size,d) A large number with a floating decimal point. Specify the maximum number of digits in parentheses. Specifies the maximum number of digits to the right of the decimal point in the D parameter.
Decimal (size,d) is a DOUBLE type stored as a string, allowing a fixed decimal point.

* These integer types have an additional option of UNSIGNED. Generally, integers can be negative or positive. If you add the UNSIGNED property, the range will start at 0 instead of a negative number.

Date Type:
Data Type Description
DATE ()

Date. Format: YYYY-MM-DD

Note: The scope of support is from ' 1000-01-01 ' to ' 9999-12-31 '

DATETIME ()

* Combination of date and time. Format: Yyyy-mm-dd HH:MM:SS

Note: The range of support is from ' 1000-01-01-00:00:00 ' to ' 9999-12-31 23:59:59 '

TIMESTAMP ()

* Time stamp. The TIMESTAMP value is stored using the description of the Unix era (' 1970-01-01 00:00:00 ' UTC) to date. Format: Yyyy-mm-dd HH:MM:SS

Note: The supported range is from ' 1970-01-01 00:00:01 ' UTC to ' 2038-01-09 03:14:07 ' UTC

Time () Time. Format: HH:MM:SS Comment: The supported range is from ' -838:59:59 ' to ' 838:59:59 '
Year ()

Year of 2-bit or 4-bit format.

Note: The 4-bit format allows values from 1901 to 2155. The 2-bit format allows values from 70 to 69, representing 1970 to 2069.

* Even if DATETIME and TIMESTAMP return the same format, they work in a different way. In an INSERT or UPDATE query, TIMESTAMP automatically sets itself to the current date and time. TIMESTAMP also accepts different formats, such as YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.

SQL Server data type character string:
Data Type Description Storage
CHAR (n) A fixed-length string. A maximum of 8,000 characters. N
VARCHAR (n) A variable-length string. A maximum of 8,000 characters.
varchar (max) A variable-length string. A maximum of 1,073,741,824 characters.
Text A variable-length string. Up to 2GB character data.
Unicode string:
Data Type Description Storage
NCHAR (n) Fixed-length Unicode data. A maximum of 4,000 characters.
nvarchar (n) Variable-length Unicode data. A maximum of 4,000 characters.
nvarchar (max) Variable-length Unicode data. A maximum of 536,870,912 characters.
ntext Variable-length Unicode data. Up to 2GB character data.
Binary Type:
Data Type Description Storage
Bit Allow 0, 1, or NULL
Binary (n) Fixed-length binary data. Up to 8,000 bytes.
varbinary (n) Variable-length binary data. Up to 8,000 bytes.
varbinary (max) Variable-length binary data. Up to 2GB bytes.
Image Variable-length binary data. Up to 2GB.
Number type:
Data Type Description Storage
tinyint All numbers from 0 to 255 are allowed. 1 bytes
smallint All numbers from 32,768 to 32,767 are allowed. 2 bytes
Int All numbers from 2,147,483,648 to 2,147,483,647 are allowed. 4 bytes
bigint Allows all numbers between 9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. 8 bytes
Decimal (P,s)

Fixed-precision and proportional numbers. Allows a number from -10^38 +1 to 10^38-1.

The P parameter indicates the maximum number of digits that can be stored (left and right of the decimal point). P must be a value between 1 and 38. The default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value between 0 and P. The default is 0.

5-17 bytes
Numeric (P,s)

Fixed-precision and proportional numbers. Allows a number from -10^38 +1 to 10^38-1.

The P parameter indicates the maximum number of digits that can be stored (left and right of the decimal point). P must be a value between 1 and 38. The default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value between 0 and P. The default is 0.

5-17 bytes
SmallMoney Currency data between -214,748.3648 and 214,748.3647. 4 bytes
Money Currency data between -922,337,203,685,477.5808 and 922,337,203,685,477.5807. 8 bytes
Float (n) Floating precision digital data from -1.79e + 308 to 1.79E + 308. The parameter n indicates whether the field holds 4 bytes or 8 bytes. Float (24) holds 4 bytes, while float (53) holds 8 bytes. The default value for N is 53. 4 or 8 bytes
Real Floating precision digital data from -3.40e + 38 to 3.40E + 38. 4 bytes
Date Type:
Data Type Description Storage
Datetime From January 1, 1753 to December 31, 9999, the accuracy is 3.33 milliseconds. 8 bytes
DateTime2 From January 1, 1753 to December 31, 9999, the accuracy is 100 nanoseconds. 6-8 bytes
smalldatetime From January 1, 1900 to June 6, 2079, the accuracy is 1 minutes. 4 bytes
Date Only dates are stored. From January 1, 01 to December 31, 9999. 3 bytes
Time Storage time only. The accuracy is 100 nanoseconds. 3-5 bytes
DateTimeOffset Same as datetime2, plus time zone offset. 8-10 bytes
Timestamp Stores a unique number that is updated whenever a row is created or modified. The timestamp is based on the internal clock and does not correspond to real time. There can be only one timestamp variable per table.
Other data types:
Data Type Description
sql_variant Stores data of up to 8,000 bytes of different data types, except text, ntext, and timestamp.
uniqueidentifier Stores the global identifier (GUID).
Xml Stores XML formatted data. Up to 2GB.
Cursor Stores a reference to a pointer used for a database operation.
Table Stores the result set for later processing.

SQL Advanced Apps (data type)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.