Five types of data in SQL

Data | data type
Briefly describe five types of data in sql: character, text, numeric, logical, and date


Character type

VARCHAR VS CHAR

The difference between varchar and char data is subtle, but very important. They are all used to store characters with a string length less than 255.

If you enter data into a 40-character varchar field, Bill Gates. When you remove this data from this field later, the data you take out is 10 characters long-the length of the string, Bill Gates. Now if you type a string into a char field of 40 characters, the length of the data you take out will be 40 characters. The string will be appended with extra spaces.

When you set up your own site, you'll find that using varchar fields is much more convenient than char-type fields. When using varchar fields, you don't have to worry about cutting out the extra space in your data.

Another notable benefit of the varchar field is that it takes up less memory and hard disk space than the Char type field. This saving of memory and disk space becomes very important when your database is large

Text type

TEXT

With text-type data, you can store strings of more than 2 billion characters. When you need to store large strings of characters, you should use text-type data.

Note that the literal data has no length, and the character data described in the previous section is of length. The data in a text field is usually either empty or large.

When you collect data from the HTML form's multiple-text edit box (TEXTAREA), you should store the information you collect in a literal field. However, whenever you can avoid using text-type fields, you should not be able to use them. Text-type fields are large, and misuse of text fields slows the server down. Text-type fields also consume a large amount of disk space.

Once you have entered any data (even a null value) into a text field, 2K of space is automatically assigned to that data. You will not be able to reclaim this portion of the storage space unless you delete the record.



Numeric type

SQL supports many different numeric data types. You can store integers int, decimal NUMERIC, and money.

INT vs SMALLINT vs TINYINT
Their difference is only character length: The number of tables in int data is from 2,147,483,647 to 2,147,483,647 integer smallint 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
In general, in order to save space, you should use the smallest integer data as much as possible. One tinyint data occupies only one byte; an int data occupies four bytes. This may seem a little different, but in a larger table, the number of bytes grows fast. On the other hand, once you have created a field, it is difficult to modify it. Therefore, for security reasons, you should anticipate the maximum number of values a field needs to store, and then select 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 and the decimal parts of a number. Numeric data allows you to represent a very large number-much larger than int-type data. A numeric field can store numbers from 1038 to 1038. Numeric data also allows you to represent numbers with a small number of parts. For example, you can store decimal 3.14 in the Numeric Type field.

When defining a numeric type field, you need to specify both the size of the integer part and the size of the decimal part. such as: Muneric (23,0)

The integer portion of a numeric data can have a maximum of only 28 digits, and a fractional number must be less than or equal to the number of digits in the integer part, and the decimal part can be zero.

Money VS SmallMoney

You can use INT-or numeric-type data to store money. However, there are two other types of data that are specifically used for this purpose. If you want your network to earn a lot of money, you can use the cash type data. If you are not ambitious, you can use smallmoney 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 a larger amount than this, you can use the numeric type data.

SmallMoney data can only store money from -214,748.3648 to 214,748.3647. Also, if you can, you should use the SmallMoney type instead of the money type data to save space.

Logical type

BIT

If you use a checkbox to collect information from a Web page, you can store this information in a bit type field. The bit Type field can take only two values: 0 or 1.

Be careful, after you create a table, you can't add bit fields to the table. If you plan to include bit fields in a table, you must do so when creating a table.

Date type

DATETIME VS smalldatetime

A datetime field can be stored with a date range from the first millisecond of January 1, 1753 to the last millisecond of December 31, 9999.

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

It is important to recognize that DateTime fields do not contain actual data until you enter a date and time.

The data types commonly used for SQL are introduced here first.
If you have any questions and suggestions, you can leave a message to the small skin, or email:xiaoboe-mail@email.jlu.edu.cn