Briefly describe five data types in SQL: character type, text, numerical, logical, and date type
Character pattern
Varchar vs char
This difference in VARCHAR type and CHAR type data is subtle, but it is very important. They are all characters used to store string lengths less than 255.
If you enter data Bill Gates in a varchar field that is length of forty characters. When you remove this data from this field, you take the length of the data in the length of ten characters - string Bill Gates. Now, if you enter a string into a CHAR field with a length of forty characters, then when you remove the data, the extracted data length will be forty characters. The back of the string will be attached to extra spaces.
When you build your own site, you will find that using the varchar type is more convenient than the char type field. When using a varchar field, you don't need to worry about the extra spaces in your data.
Another prominent benefit of a VARCHAR field is that it can take less memory and hard disk space than the CHAR type field. This memory and disk space savings will become very important when your database is large.
Text type
TEXT
With textual data, you can store more than 2 billion characters. Text-type data should be used when you need to store large string characters.
Note that textual data has no length, and the characteristic data in the previous section is long. Data in a text field is usually empty, or it is either.
When you collect data from HTML Form, you should store the collected information in a text field. However, whenever you can avoid using text type fields, you should not apply it. Text-type fields are both large and slow, and the abuse text field will slow the server speed. Text fields also eat a lot of disk space.
Once you enter any data (even null value) to the text field, there will be 2K spaces to be automatically assigned to the data. You cannot recover this part of the storage space unless you delete this record.
Numerical type
SQL supports many different numerical data. You can store integer int, decimal numeric, and money number Money.
INT VS Smallint vs tinyint The difference is just the character length:
INT data The number range range from -2, 147, 483, 647 to 2, 147, 483, 647 can store the integer Tinyint type field from -32768 to 32768 can only be stored from 0 to 255. Integer, can not be used to store negative numbers
Typically, in order to save space, you should use the minimum integer data as much as possible. A tinyint type data takes only one byte; an int type data takes up four bytes. This seems difference, but in a relatively large table, the increase in the number of bytes is very fast. On the other hand, once you have created a field, it is difficult to modify it. Therefore, for safety, you should predict the following, the value you need to store is mostly possible, then select the appropriate data type.
Muneric
In order to be able to control the data stored in the field, you can use NuMeric data to simultaneously represent an integer part and the fractional portion. Numeric type data makes you a very large number - much more than INT data. A Numeric type field can store the number from -1038 to 1038. Numeric type data also allows you to indicate the number of decimal parts. For example, you can store score 3.14 in the Numeric type.
When defining a Numeric type, you need to specify the size of the integer portion and the size of the fractional portion. Such as: muneric (23, 0)
The integer portion of a NUMERIC type data can only have 28 bits. The number of digits must be less than or equal to the number of digits of the integer portion, and the fractional portion can be zero. Money VS Smallmoney
You can use INT or Numeric type data to store money. However, there are other two types of data for this purpose. If you want your outlet to earn a lot of money, you can use Money type data. If your ambition is not large, you can use SmallMoney data. Money type data can be stored from -922, 337, 203, 685, 477.5808 to 922, 337, 203, 685, 477.5807. If you need to store a big amount than this, you can use NUMERIC type data.
SmallMoney data can only be stored from -214, 748.3648 to 214, 748.3647. Similarly, if you can, you should use the SmallMoney type to save Money type data to save space.
Logical type
Bit
If you use the check box (Checkbox) to collect information from the web page, you can store this information in the Bit field. Bit type fields can only take two values: 0 or 1.
Beware, after you create a table, you can't add a bit type field to the table. If you intend to include a Bit type field in a table, you must do it when you create a table.
Date type
DateTime VS SmallDateTime
A datetime type field can store the date range from January 1, 1753 to last milliseconds, 9999.
If you don't need to override such a wide range of dates and times, you can use SmallDateTime type data. It is also used as DateTime type data, but it can be indicated by the date and time range than the datetime type data, and it is not as accurate as DateTime type data. A SmallDateTime type field can store the date from January 1, 1900 to June 6, 2079, which can only be accurate to second.
The DateTime field does not contain actual data before you enter the date and time, and it is important to understand this.
This is introduced here about the data type commonly used by SQL. If you have any questions and suggestions, you can leave a message, or email: xiaoboe-mail@emil.jlu.edu.cn
