SQL common data type
The data type defines the type of value stored in the column.
SQL common data type
Each column in the database table needs to have a name and data type.
SQL developers must decide when creating SQL tables what type of data each column in the table will store. A data type is a label that is a guide for SQL to understand what type of data each column expects to store, and it also identifies how SQL interacts with stored data.
The following table lists the common data types in SQL:
| type of data | describe |
|---|---|
| CHARACTER(n) | Character / string.Fixed length n. |
|
VARCHAR (N) or
CHARACTER VARYING(n) |
Character / string.Variable length.Maximum length n. |
| BINARY(n) | Two-in-strings.Fixed length n. |
| BOOLEAN | Store true or false value |
|
Varbinary (N) or
BINARY VARYING(n) |
Two-in-strings.Variable length.Maximum length n. |
| INTEGER(p) | Integer value (no decimal point).Precision P. |
| SMALLINT | Integer value (no decimal point).Accuracy 5. |
| INTEGER | Integer value (no decimal point).Accuracy 10. |
| BIGINT | Integer value (no decimal point).Accuracy 19. |
| DECIMAL(p,s) | Accurate value, accuracy P, rear number of decimal points s.For example: Decimal (5, 2) is a number of 2 digits after a small number of decimal points before the decimal point. |
| NUMERIC(p,s) | Accurate value, accuracy P, rear number of decimal points s.(Same as Decimal) |
| FLOAT(p) | Approximate value, mandate accuracy P.A floating point number using an exponential count of 10 is based.This type of Size parameter consists of a single number of specified minimum accuracy. |
| REAL | Approximate value, mantissa accuracy 7. |
| FLOAT | Approximate value, mandate accuracy 16. |
| DOUBLE PRECISION | Approximate value, mandate accuracy 16. |
| DATE | Store the value of the year, month, and day. |
| TIME | Store hours, points, and seconds. |
| TIMESTAMP | Storage year, month, day, hour, minute, second value. |
| INTERVAL | Composed of some integer fields, representing a period of time, depending on the type of interval. |
| ARRAY | Order collection of the fixed length of the element |
| MULTISET | Disorder collection of variable lengths of elements |
| XML | Store XML data |
SQL data type quick reference manual
However, different databases provide different choices for data type definitions.
The following table shows the generic names of certain data types on different database platforms:
| type of data | Access | SQLServer | Oracle | MySQL | PostgreSQL |
|---|---|---|---|---|---|
| boolean | Yes/No | Bit | Byte | N/A | Boolean |
| integer | Number (integer) | Int | Number |
Int
Integer |
Int
Integer |
| float | Number (single) |
Float
Real |
Number | Float | Numeric |
| currency | Currency | Money | N/A | N/A | Money |
| string (fixed) | N/A | Char | Char | Char | Char |
| string (variable) |
Text (<256)
Memo (65k+) |
Varchar |
Varchar
Varchar2 |
Varchar | Varchar |
| binary object | OLE Object Memo |
Binary (fixed up to 8K)
Varbinary (<8K) Image (<2GB) |
Long
Raw |
Blob
Text |
Binary
Varbinary |
|
Note: The same data type may have different names in different databases. E ven if the name is the same, the size and other details can be different! Please feel free to check the files! |
|---|