Basics of SQL Data Operations (Elementary) 4

Create a new table with SQL

Note:

If you haven’t set up your own database yet, jump back to Chapter 3 to create this library now. You must not add data to master, tempdb or any other system database.

From SQL Start the ISQL/w program in the Sever program 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, click the Execute Query button to execute this statement:

CREATE TABLE guestbook (visitor VARCHAR(40),comments TEXT,entrydate

DATETIME)

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

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

Congratulations, you have created your first table!

The table you created is called guestbook. You can use this table to store information about visitors to your site. Are you using REEATE The table created by the TABLE statement has two parts: the first part specifies the name of the table; the second part is the name and attributes of each field enclosed in parentheses, separated by commas.

The guestbook table has three fields: visitor, comments and entrydate. The visitor field stores the visitor's name, the comments field stores the visitor's opinions on your site, and the entrydate field stores the date and time the visitor visited 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 data type of the field. The data type determines what kind of data a field can store. Because the field comments contains text information, its data type is defined as text.

Fields come in many different data types. The next section describes some important data types supported by SQL.

Field types

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

Character data

Character data is very useful. When you need to store short string information, you always use character data. For example, you could take the HTML The information collected in the text box of the form is placed in the character field.

To create a field to store variable length string information, you can use expressions VARCHAR. Consider the guestbook table 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 number in parentheses following the data type. This number specifies the maximum length of the string allowed to be stored in this field. In this example, the visitor field can store a string of up to forty characters. If the name is too long, the string will be truncated to forty characters.

The VARCHAR type can store a string of up to 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 fixed-length character data. Here is an example using this data type:

CREATE TABLE guestbook (visitor CHAR(40),comments TEXT,entrydate

DATETIME)

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

This difference between VARCHAR and CHAR data is subtle, but very important. Suppose you enter data Bill into a VARCHAR field with a length of forty characters. Gates. When you retrieve this data from this field later, the length of the data you retrieve is ten characters - the string Bill The length of 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. This memory and disk space saving becomes very important when your database is large.

Text data

Character data limits the length of the string to no more than 255 characters. With text data, you can store strings of more than two billion characters. Text data should be used when you need to store large strings of characters.

Here is an example of using text data:

CREATE TABLE guestbook (visitor VARCHAR(40),comments TEXT,entrydate

DATETIME)

In this example, the comments field is used to store visitors' opinions about your site. 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 go from HTML When collecting data in a form's multi-line text edit box (TEXTAREA), 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 overuse of text fields can slow down the server. Text fields also eat up a lot of disk space.

Warning:

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

Numerical data

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

Usually, when you need to store numbers in a table, you use integer (INT) data. The table number range of INT type data is an integer from -2,147,483,647 to 2,147,483,647. Here is an example of how to use INT type data:

CREATE TABLE visitlog (visitor VARCHAR(40),numvisits INT)

This table can be used to record the number of times your site has been visited. 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 type data. SMALLINT type 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 type data. Similarly, the usage of this type is the same as the INT type, except that this type of field can only store integers from 0 to 255. TINYINT type fields 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.

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. Here is an example 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 containing two fields will be created. The field bignumber can store integers up to 28 bits. The field fraction can store decimals with a five-digit integer part and a four-digit decimal part.

The integer part of a NUMERIC 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.

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 be stored from -214,748.3648 to 214,748.3647 amount of money. Likewise, if possible, you should use SMALLMONEY type instead of MONEY type data to save space. The following example shows how to use these two data types representing money:

CREATE TABLE PRoducts (product VARCHAR(40),price MONEY,

Discount_price SMALLMONEY)

This table can be used to store the discount and normal selling price of products. Field price The data type of is MONEY, and the data type of field discount_price is SMALLMONEY.

Storing logical values

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. Here is an example of how to use such a field:

CREATE TABLE opinion (visitor VARCHAR(40),good BIT)

This table can be used to store information from polls of your outlets. Visitors can vote to indicate whether they like your site. If they vote YES, a 1 is stored in the BIT field. Otherwise, if they vote NO, store 0 in the field (you'll learn how to count votes in the next chapter).

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.

Storage date and time

When you set up a website, you may need to record the number of visitors over a period of time. In order to be able to store date and time, you need to use DATETIME type data, as shown in the following example:

CREATE TABL visitorlog( visitor VARCHAR (40), arrivaltime DATETIME ,

departuretime DATETIME)

This table can be used to record the time and date when visitors enter and leave your website. A DATETIME type field can store the 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 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 the second.

DATETIME type fields do not contain actual data until you enter the date and time. It is important to realize this. In the next chapter, you'll learn how to use a variety of SQL functions to read and manipulate dates and times (see the "Default Values" section below). You can also use VBScript and JScript Use the date and time functions to enter a date and time into a DATETIME field.

Field attributes

The previous section introduced how to create a table containing different types of fields. In this section, you will learn how to use the three properties of fields. These properties allow you to control null, default and identity values.

Allow and prohibit null values

Most fields can accept null values ​​(NULL). When a field accepts a null value, it will remain null if you don't change it. NULL is different from zero. Strictly speaking, NULL means no value.

To allow a field to accept NULL values, you use the expression NULL after the field definition. For example, both fields in the table below allow null values:

CREATE TABLE empty (empty1 CHAR (40) NULL,empty2 INT NULL(

Note:

BIT type data cannot be null. A field of this type must be 0 or 1.

Sometimes you need to prohibit a field from using null values. For example, suppose there is a table that stores credit card numbers and credit card expiration date, you don't want someone to enter a credit card number but not the expiration date. To force data into both fields, you can create the table using:

CREATE. TABLE creditcards (creditcard_number CHAR(20) NOT NULL,

Creditcard_expire DATETIME NOT NULL)

Note that the field definition is followed by the expression NOT NULL. By including the expression NOT NULL, you can prevent anyone from inserting data into only one field without entering data from another field.

You will find that this ability to prohibit null values ​​is very useful in the process of building your own network. If you specify that a field cannot accept null values, an error message will appear when you try to enter a null value. These error warnings can provide valuable clues for program debugging.

Default value

Suppose there is a table that stores address information. The fields of this table include street, city, state, zip code, and country. If you expect the majority of addresses to be in the United States, you can use this value as the default value for the country field.

To specify a default value when creating a table, you can use the expression DEFAULT. Consider the following example of using default values ​​when creating a table:

CREATE TABLE addresses (street VARCHAR(60) NULL,

city VARCHAR(40) NULL,

state VARCHAR(20) NULL

zip VARCHAR(20) NULL,

country VARCHAR(30) DEFAULT ‘USA’)

In this example, the default value of the field country is specified as the United States. Note the use of single quotes, which indicate that this is character data. To specify a default value for a non-character field, do not enclose the value in quotes:

CREATE TABLE orders(price MONEY DEFAULT $38.00,

quantity INT DEFAULT 50,

entrydate DATETIME DEFAULT GETDATE())

in this CREATE In the TABLE statement, each field is assigned a default value. Note that the default value specified by the DATETIME type field entrydate is the return value of the function Getdate(), which returns the current date and time.

Identification field

Each table can have one or only one identification field. An identification field is a special field that uniquely identifies each record in a table. For example, the jobs table in the pubs database contains a field that uniquely identifies each job:

job_id job_desc

………………………………………………………………………….

New Hire Job not specified
Chief Executive officer
Bushness Operations Manager
Chief Financial Officer
Publisher
The field job_id provides a unique number for each job. If you decide to add a new job, the job_id field of the new record will be automatically assigned a new unique value.

In order to create an identification field, you only need to add the expression IDENTITY after the field definition. You can only set NUMERIC or INT type fields as identification fields. Here is an example:

CREATE TABLE visitorID (theID NUBERIC(18) IDENTITY,name VARCHAR(40))

The table created by this statement contains an identification field named theid. Whenever a new visitor name is added to the table, this field is automatically assigned a new value. You can use this table to provide a unique identifier for each user of your site.

Tips:

When creating a label field, pay attention to using a large enough data type. For example, if you use TINYINT type data, you can only add 255 records to the table. If you anticipate that a table may become very large, you should use NUMERIC data.

The existence of identification fields will make you want to try many impossible things. For example, you might want to use an identification field to perform operations on records based on their position in the table. You should abandon this intention. The value of the identification field of each record is different from each other, but this does not prohibit the existence of gaps between the identification numbers of an identification field. For example, you should never try to use a table's identification field to retrieve the first ten records in the table. This operation will cause failure, for example, record No. 6 and record No. 7 do not exist at all.

The above is the content of SQL Data Operation Basics (Elementary) 4. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!