SQL is used for various database data types
The type and scope of data used by Microsoft Access, MySQL, and SQL Server.
Microsoft Access data type
type of data | describe | storage |
---|---|---|
Text | For text or text and numbers.Up to 255 characters. | |
Memo | MEMO is used for a larger number of text.Store up to 65,536 characters. Note: The MEMO field cannot be sorted.However, they are searchable. | |
Byte | Allow numbers of 0 to 255. | 1 byte |
Integer | Allows all numbers between -32, 768 and 32, 767. | 2 bytes |
Long | Allows all numbers between -2, 147, 483, 648 and 2, 147, 483, 647. | 4-byte |
Single | Single precision floating point.Handling most of the decimal. | 4-byte |
Double | Double precision floating point.Handling most of the decimal. | 8-byte |
Currency | Used for currency.Supports 15 yuan, plus 4 decimals. hint: You can choose which country's currency. | 8-byte |
AutoNumber | Autonumber fields automatically assign numbers to each record, usually starting with 1. | 4-byte |
Date/Time | Date and time | 8-byte |
Yes/No | The logical field can be displayed as Yes / NO, True / False or ON / OFF.In the code, use constants true and false (equivalent to 1 and 0). Note: NULL value is not allowed in the YES / NO field | 1 bit |
Ole Object | You 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 drop-down list. | 4-byte |
MySQL data type
In MySQL, there are three main types: Text, Number, and Date/Time.
Text type:
type of data | describe |
---|---|
CHAR(size) | Save the fixed length string (which can contain letters, numbers, and special characters).Specify the length of the string in parentheses.Up to 255 characters. |
VARCHAR(size) | Save the variable length string (which can contain letters, numbers, and special characters).Specify the maximum length of the string in parentheses.Up to 255 characters. Note: If the length is greater than 255, it is converted to a TEXT type. |
TINYTEXT | Sticker strings with a maximum length of 255 characters. |
TEXT | Store a string of the maximum length of 65,535 characters. |
BLOB | Used in Blobs (Binary Large Objects).Store up to 65,535 bytes of data. |
MEDIUMTEXT | Store a string of the maximum length of 16,777,215 characters. |
MEDIUMBLOB | Used in Blobs (Binary Large Objects).Store up to 16,777,215 bytes of data. |
LONGTEXT | Store strings with maximum lengths of 4,294,967,295 characters. |
LONGBLOB | Used in Blobs (Binary Large Objects).Store up to 4,294,967,295 bytes. |
ENUM(x,y,z,etc.) |
Allows you to enter a list of possible values.The maximum 65535 values can be listed in the enum list.If there is no inserted value in the list, you can insert a null value.
Note: These values are sorted in the order you entered. You can enter the possible value according to this format: Enum ('x', 'Y', 'Z') |
SET | Similar to ENUM, in different, SET can only contain 64 list items and SET can store more than one choice. |
Number type:
type of data | describe |
---|---|
TINYINT(size) | -128 to 127 routine.0 to 255 no symbol *.The maximum number of digits is specified in parentheses. |
SMALLINT(size) | -32768 to 32767 routine.0 to 65535 No symbol *.The maximum number of digits is specified in parentheses. |
MEDIUMINT(size) | -8388608 to 8388607 ordinary.0 TO 16777215 No symbol *.The maximum number of digits is specified in parentheses. |
INT(size) | -2147483648 to 2147483647 routine.0 to 4294967295 No symbol *.The maximum number of digits is specified in parentheses. |
BIGINT(size) | --9223372036854775808 to 9223372036854775807 Conventional.0 to 18446744073709551615 No symbol *.The maximum number of digits is specified in parentheses. |
FLOAT(size,d) | A small number with floating decimal points.The maximum number of digits is specified in the Size parameter.The maximum number of digits on the right side of the decimal point is specified in the D parameter. |
DOUBLE(size,d) | Big numbers with floating decimal points.The maximum number of digits is specified in the Size parameter.The maximum number of digits on the right side of the decimal point is specified in the D parameter. |
DECIMAL(size,d) | The Double type stored as a string is stored, allowing the fixed decimal point.The maximum number of digits is specified in the Size parameter.The maximum number of digits on the right side of the decimal point is specified in the D parameter. |
Note: These integer types have additional options FORSIGNED. T ypically, an integer can be negative or positive. If you add a UNSIGNED property, the range starts at 0, not a negative number.
Date type:
type of data | describe |
---|---|
DATE() |
date.Format: YYYY-MM-DD
Note: Supported range from '1000-01-01' to '9999-12-31' |
DATETIME() |
* Date and time combination.Format: YYYY-MM-DD HH: mm: SS
Note: Supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
TIMESTAMP() |
* Timestamp.TimeSTAMP value is stored using UNIX Era ('1970-01-01 00:00' UTC).Format: YYYY-MM-DD HH: mm: SS
Note: Supported range from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07 UTC |
TIME() |
time.Format: hh: mm: ss
Note: The support range is from '-838: 59: 59' to '838: 59: 59' |
YEAR() |
The year of 2 or 4-bit formats.
Note: The values allowed in the 4-bit format: 1901 to 2155.2 The values allowed by: 70 to 69, indicating from 1970 to 2069. |
Note: Even if DATETIME and TIMESTAMP return the same format, they work very differently. I n insert or UPDATE queries, TIMESTAMP automatically sets itself to the current date and time. TIMESTAMP also accepts different formats, such as YYYYMMDDHMMSS, YYMMDDHMMSS, YYYYMMDD or YYMMDD.
SQL Server data type
String type:
type of data | describe | storage |
---|---|---|
char(n) | Fixed strings.Up to 8,000 characters. | Defined width |
varchar(n) | Variable length string.Up to 8,000 characters. | 2 bytes + number of chars |
varchar(max) | Variable length string.Up to 1,073,741,824 characters. | 2 bytes + number of chars |
text | Variable length string.Up to 2GB text data. | 4 bytes + number of chars |
nchar | Unicode string of fixed length.Up to 4,000 characters. | Defined width x 2 |
nvarchar | A variable length Unicode string.Up to 4,000 characters. | |
nvarchar(max) | A variable length Unicode string.Up to 536,870,912 characters. | |
ntext | A variable length Unicode string.Up to 2GB text data. | |
bit | Allow 0, 1 or NULL | |
binary(n) | Secondary binary string of fixed length.Up to 8,000 bytes. | |
varbinary | Binary string of variable length.Up to 8,000 bytes. | |
varbinary(max) | Binary string of variable length.Up to 2GB. | |
image | Binary string of variable length.Up to 2GB. |
Number type:
type of data | describe | storage |
---|---|---|
tinyint | Allows all numbers from 0 to 255. | 1 byte |
smallint | Allows all numbers to be between -32, 768 and 32, 767. | 2 bytes |
int | Allows every number of -2, 147, 483, 648 and 2, 147, 483, 647. | 4-byte |
bigint | Allows all numbers between -9, 223, 372, 036, 854, 775, 808, 9, 223, 372, 036, 854, 775, 807. | 8-byte |
decimal(p,s) |
Fixed precision and ratio of numbers.
Allow numbers between -10 ^ 38 +1 to 10 ^ 38 -1. The P parameter indicates the maximum number of digits (decimal point left and right) that can be stored.P must be a value between 1 and 38.The default is 18. The S parameter indicates the maximum number of bits stored on the right side of the decimal point.S must be a value between 0 to P.The default is 0. |
5-17 bytes |
numeric(p,s) |
Fixed precision and ratio of numbers.
Allow numbers between -10 ^ 38 +1 to 10 ^ 38 -1. The P parameter indicates the maximum number of digits (decimal point left and right) that can be stored.P must be a value between 1 and 38.The default is 18. The S parameter indicates the maximum number of bits stored on the right side of the decimal point.S must be a value between 0 to P.The default is 0. |
5-17 bytes |
smallmoney | Money data between -214, 748.3648 and 214, 748.3647. | 4-byte |
money | Money data between -922, 337, 203, 685, 477.5808 and 922, 337, 203, 685, 477.5807. | 8-byte |
float(n) |
Floating precision digital data from -1.79e + 308 to 1.79e + 308.
n parameters indicate that the field saves 4 bytes or 8 bytes.FLOAT (24) saves 4 bytes, while Float (53) saves 8 bytes.N The default value is 53. |
4 or 8 bytes |
real | Floating precision digital data from -3.40e + 38 to 3.40e + 38. | 4-byte |
Date type:
type of data | describe | storage |
---|---|---|
datetime | From January 1, 1753 to December 31, 9999, the accuracy was 3.33 milliseconds. | 8-byte |
datetime2 | From January 1 to 9999, from January 1, 999, the accuracy was 100 nanoseconds. | 6-8 bytes |
smalldatetime | From January 1, 1900, June 6, 2079, precision is 1 minute. | 4-byte |
date | Only store the date.From January 1, 1999 to December 31, 9999. | 3 bytes |
time | Store time only.Accuracy is 100 nanoseconds. | 3-5 bytes |
datetimeoffset | The same as DateTime2, an additional time zone offset. | 8-10 bytes |
timestamp | Store unique numbers, which update is updated whenever you create or modify a row.The timestamp value is based on the internal clock and does not correspond to real time.Each table can only have a TIMESTAMP variable. |
Other data types:
type of data | describe |
---|---|
sql_variant | Store up to 8,000 bytes of different data types, except for Text, NText, and TimeStamp. |
uniqueidentifier | Store the global unique identifier (GUID). |
xml | Store XML formatted data.Up to 2GB. |
cursor | Store references to pointers for database operations. |
table | Storage result set for later processing. |