Field Type in the database table

Source: Internet
Author: User
Tags how to use sql

All data in the database is stored in the table. Data tables include rows and columns. The column determines the data type in the table. The row contains the actual data.

For example, the table authors in the database pubs has nine fields. The field name is au_lname, which is used to store the author's name information. Each time a new author is added to this table, the author name is added to this field to generate a new record.

By defining fields, you can create a new table. Each field has a name and a specific data type (the data type is described in the "field type" section below). For example, the field au_lname stores structured data. A field can also store data of other types.

There are many ways to create a new table using SQL Server. You can execute an SQL statement or use SQL Transaction Manager to create a new table. In the next section, you will learn how to use SQL statements to create a new table.

1. Use the create statement to create a table

Note:

If you have not created your own database, go back to chapter 3 to create the database. You must not add data to the master, tempdb, or any other system database.

From SQL ServerProgramStart the iSQL/W program in the group (in the taskbar. When the query window appears, select the database you created in Chapter 3 from the drop-down list at the top of the window. Next, type the following SQL statement in the query window, and click Run query to execute the statement:

Create Table guestbook (visitor varchar (40), comments text, entrydate datetime)

If everything is normal, you will see the following text in the result window (if an exception occurs, see Chapter 3 ):

This command dit not return data, and it did not return any rows

Congratulations! You have created your first table!

The name of the table you created is guestbook. You can use this table to store the information of your site visitors. You created the table using the create table statement. This statement has two parts: the first part specifies the table name; the second part is the name and attribute of each field in brackets, separated by commas.

The table guestbook has three fields: visitor, comments, and entrydate.

The visitor field stores the name of the visitor,

The comments field stores comments from visitors on your site,

The entrydate field stores the date and time when visitors visit your site.

Note that each field name is followed by a special expression. For example, the field name comments is followed by the expression text. This expression specifies the field data type. The data type determines the data that a field can store. Because the field comments contains text information, its data type is defined as text type.

Ii. Field Type

Different field types are used to store different types of data. When creating and using tables, you should understand five common field types: numeric, text, numeric, logic, and date.

(1) Structured Data

Balanced data is very useful. When you need to store short string information, you always need to use string data. For example, you can put the information collected from the text box of HTML form in the character field.

To create a field to store variable-length string information, you can use the expression varchar. Consider the table guestbook you created earlier:

Create Table guestbook (visitor varchar (40), comments text, entrydate datetime)

In this example, the Data Type of the field visitor is varchar. Note the numbers in parentheses following the data type. This number specifies the maximum length of the string that this field can store. In this example, the visitor field can store a string of up to 40 characters. If the name is too long, the string is truncated and only 40 characters are retained.

A string of the varchar type can contain a maximum of 255 characters. To store longer string data, you can use text data (described in the next section ).

Another type of character data is used to store character data with a fixed length. The following is an example of using this data type:

Create Table guestbook (visitor char (40), comments text, entrydate datetime)

In this example, the field visitor is used to store fixed-length strings of 40 characters. The expression char specifies that this field should be a fixed-length string.

The differences between varchar and char data are subtle, but important. Assume that you enter the data Bill Gates in a 40-character varchar field. When you extract this data from this field, the length of the data you extract is 10 characters-the length of the string Bill Gates.

Now, if you input a string into a char field with a length of 40 characters, the length of the retrieved data will be 40 characters. Extra spaces will be appended to the string.

When you create your own site, you will find it easier to use varchar fields than char fields. When using varchar fields, you do not need to worry about cutting out unnecessary spaces in your data.

Another outstanding advantage of the varchar field is that it can occupy less memory and hard disk space than the char field. When your database is large, this memory and disk space savings will become very important.

(2) Text Data

Character data limits the length of a string to no more than 255 characters. Text data allows you to store strings with more than 2 billion characters. When you need to store large strings of characters, you should use text-type data.

Here is an example of using text data:

Create Table guestbook (visitor varchar (40), comments text, entrydate datetime)

In this example, the field comments is used to store users' opinions on your site. Note that text data has no length, but the plain data mentioned in the previous section has a length. Data in a text field is usually either empty or large.

When you collect data from the HTML form multi-line text editing box (textarea), you should store the collected information in text fields. However, whenever you avoid using a text field, you should not apply it. Text fields are both large and slow. Misuse of text fields slows down the server. Text fields also consume a large amount of disk space.

Warning:

Once you input any data (or even null values) to a text field, 2 K space is automatically allocated to the data. You cannot reclaim this bucket unless you delete this record.

(3) numeric data

SQL Sever supports many different types of numeric data. You can store integers, decimals, and money.

Generally, when you need to store numbers in a table, you need to use INTEGER (INT) data. The number of int Data Tables ranges from-2,147,483,647 to 2,147,483,647. The following is an example of how to use int data:

Create Table visitlog (visitor varchar (40), numvisits INT)

This table can be used to record the number of visits to your site. As long as no one visits your site more than 2,147,483,647 times, the nubvisits field can store the number of visits.

To save memory space, you can use smallint data. Smallint data can store integers from-32768 to 32768. The usage of this data type is exactly the same as that of int type.

Finally, if you really need to save space, you can use tinyint data. Similarly, the use of this type is the same as that of the int type. The difference is that this type of field can only store integers from 0 to 255. Tinyint fields cannot be used to store negative numbers.

Generally, the smallest integer data should be used as much as possible to save space. A tinyint data occupies only one byte, and an int data occupies four bytes. This seems to be a little different, but in a relatively large table, the number of bytes increases rapidly. On the other hand, once you have created a field, it is very difficult to modify it. Therefore, to ensure security, you should predict the maximum value that a field needs to store, and then select the appropriate data type.

In order to have more control over the data stored in a field, you can use numeric data to simultaneously represent the integer and decimal parts of a number. Numeric data enables you to represent a very large number-much larger than int data. A numeric field can store numbers from-10 ^ 38 to 10 ^ 38. Numeric data also enables you to represent the number of fractional parts. For example, you can store a decimal number of 3.14 in a numeric field.

When defining a numeric field, you must specify the size of the integer part and the decimal part at the same time. Here is an example of using this data type:

Create Table numeric_data (bignumber numeric (28, 0), fraction numeric (5, 4 ))

When this statement is executed, a table named numeric_data contains two fields will be created. The bignumber field can be a 28-digit integer. The fraction field can store decimals with five integers and four decimals.

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

You can use Int or numeric data to store money. However, there are two other data types dedicated for this purpose. If you want your outlets to earn a lot of money, you can use money-type data. If you are not ambitious, you can use smallmoney data. Money 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 numeric data.

Smallmoney data can only store the amount of money from-214,748.3648 to 214,748.3647. Similarly, if possible, you should use the smallmoney type to replace the money type data to save space. The following example shows how to use these two data types to represent money:

Create Table products (product varchar (40), price money,

Discount_price smallmoney)

This table can be used to store discounts and general prices of products. The data type of the field price is money, and the Data Type of the field discount_price is smallmoney.

(4) store logical values

If you use the check box to collect information from a webpage, you can store this information in the bit field. A bit field can have only two values: 0 or 1. Here is an example of how to use this field:

 

Create Table opinion (visitor varchar (40), good bit)

This table can be used to store information obtained from public opinion surveys on your website. Visitors can vote to indicate whether they like your website. If they cast Yes, 1 is saved to the bit field. Otherwise, if they cast no, they store 0 in the field (in the next chapter, you will learn how to calculate the vote ).

Note: after creating a table, you cannot add a bit field to the table. If you want to include a bit field in a table, you must complete it when creating the table.

(5) storage date and time

When you create a website, you may need to record the number of visitors within a period of time. To store date and time, you need to use datetime data, as shown in the following example:

Create tabl visitorlog (arrivaltime datetime, departuretime datetime)

This table can be used to record the time and date when visitors enter and leave your website. A datetime field can store the date range from the first millisecond of January 1, January 1, 1753 to the last millisecond of January 1, December 31, 9999.

If you do not 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 precise as datetime data. A smalldatetime field can be stored from January 1, January 1-20, 1900 to January 1, June 6. It can only be accurate to seconds.

Datetime fields do not contain actual data before you enter the date and time. It is important to understand this. In the next chapter, you will learn how to use a large number of SQL functions to read and operate on dates and times (see the "default" section below ). You can also use the date and time functions in VBScript and JScript to input a date and time to a datetime field.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.