What are the data types in SQL?

The five data types in SQL: character type, text type, numeric type, logical type and date type

Briefly describe the five data types in SQL: character type, text type, numeric type , logical type and date type

Character type

VARCHAR and CHAR

The difference between VARCHAR type and CHAR type data is subtle, but very important. They are used to store strings whose length is less than 255 characters.

Suppose you enter data Bill Gates into a VARCHAR field with a length of forty characters. When you later retrieve this data from this field, the data you retrieve will be ten characters long—the length of the string Bill Gates. Now if you enter a string into a CHAR field with a length of forty characters, then when you retrieve the data, the length of the retrieved data will be forty characters. Extra spaces will be appended to the end of the string.

When you build your own site, you will find that it is much more convenient to use VARCHAR fields than CHAR fields. When using VARCHAR fields, you don't need to worry about trimming extra spaces in your data.

Another outstanding benefit of the VARCHAR field is that it takes up less memory and hard disk space than the CHAR field. When your database is large, this memory and disk space saving becomes very important

Text

TEXT

Using text data, you can store A string of over two billion characters. Text data should be used when you need to store large strings of characters.

Note that text data has no length, while the character data mentioned in the previous section has length. The data in a text field is usually either empty or very large.

When you collect data from the multi-line text edit box (TEXTAREA) of an HTML form, you should store the collected information in a text field. However, whenever you can avoid using text fields, you should not use it. Text fields are large and slow, and overusing text fields can slow down your server. Text fields also eat up a lot of disk space.

Once you enter any data (even a null value) into the text field, 2K space will be automatically allocated to the data. You can't get this storage back unless you delete the record.

Number type

SQL supports many different types of numeric data. You can store integers as INT, decimals as NUMERIC, and money as MONEY.

INT VS SMALLINT VS TINYINT
Their difference is only the character length: The table number range of INT type data is from -2, 147, 483, 647 to 2, 147, 483, 647 integer SMALLINT type Data can store integers from -32768 to 32768. TINYINT type fields can only store integers from 0 to 255, and cannot be used to store negative numbers.
Generally, in order to save space, the smallest integer data should be used as much as possible. A TINYINT type data occupies only one byte; an INT type data occupies four bytes. This may not seem like a big difference, but in larger tables, the number of bytes increases very quickly. On the other hand, once you have created a field, it is difficult to modify it. Therefore, to be on the safe side, you should predict the maximum possible value that a field needs to store, and then choose the appropriate data type.

MUNERIC

In order to have more control over the data stored in the field, you can use NUMERIC data to represent both the integer part and the decimal part of a number. NUMERIC data allows you to represent very large numbers - much larger than INT data. A NUMERIC field can store numbers in the range from -1038 to 1038. NUMERIC data also enables you to represent numbers with decimal parts. For example, you can store the decimal 3.14 in a NUMERIC field.

When defining a NUMERIC field, you need to specify both the size of the integer part and the size of the decimal part. For example: MUNERIC(23,0)

The integer part of a NUMERIC type data can only have a maximum of 28 digits. The number of digits in the decimal part must be less than or equal to the number of digits in the integer part. The decimal part can be zero.

MONEY VS SMALLMONEY

You can use INT type or NUMERIC type data to store the amount of money. However, there are two other data types used specifically for this purpose. If you want your outlet to make a lot of money, you can use MONEY type data. If you are less ambitious, you can use SMALLMONEY type data. MONEY type data can store money from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. If you need to store amounts larger than this, you can use NUMERIC data.

SMALLMONEY type data can only store money from -214,748.3648 to 214,748.3647. Likewise, if possible, you should use SMALLMONEY type instead of MONEY type data to save space.

Logical type

BIT

If you use checkboxes (CHECKBOX) to collect information from web pages, you can store this information in BIT-type fields. BIT type fields can only take on two values: 0 or 1.

Be careful, after you create a table, you cannot add BIT fields to the table. If you plan to include BIT fields in a table, you must do so when you create the table.

Date type

DATETIME VS SMALLDATETIME

A DATETIME type field can store the date range from the first millisecond of January 1, 1753 to December 31, 9999 The last millisecond of the day.

If you don't need to cover such a wide range of dates and times, you can use SMALLDATETIME type data. It is used in the same way as DATETIME type data, except that the date and time range it can represent is smaller than DATETIME type data, and it is not as accurate as DATETIME type data. A SMALLDATETIME type field can store dates from January 1, 1900 to June 6, 2079, and it can only be accurate to seconds.

DATETIME type fields do not contain actual data until you enter the date and time

The above is the detailed content of What are the data types in SQL?. For more information, please follow other related articles on the PHP Chinese website!