Operation Data: SQL basics _ MySQL

In this chapter, SQL introduces how to use the SELECT statement to retrieve data from a table to create new table field attributes, add data to the table, delete the table, and modify the table. in order to create an interactive site, you need to use a database to store information from visitors. For example, if you want to set up a website for professional services, you need to store information such as your resume and work you are interested in. Create this chapter

SQL introduction
Use the SELECT statement to retrieve data from the table
Create a new table
Field attribute
Add data to a table
Delete and modify a table


To create an interactive site, you need to use a database to store information from visitors. For example, if you want to set up a website for professional services, you need to store information such as your resume and work you are interested in. You also need to use a database to create a dynamic leaf. if you want to display the best job that meets the visitor's requirements, you need to retrieve the job information from the database. You will find that you need to use the database in many cases.
In this chapter, you will learn how to use the "Structured Query Language" (SQL) to operate databases. SQL is the standard language for databases. In Active Sever Pages, you need to use the SQL language whenever you want to access a database. Therefore, mastering SQL is very important for ASP Programming.

Note:
You can read "SQL" as "sequel" or "S-Q-L" as a single letter. Both are correct, and each pronunciation has a large number of supporters. In this book, I think "SQL" is read as "sequel ".

Through this chapter, you will understand how to use SQL to implement database queries, how to use this query to retrieve information from the data table, and finally, you will learn how to design and build your own database.

Note:
Through the introduction of SQL in the following chapters, you will have enough knowledge about SQL to effectively use Active Sever Pages. However, SQL is a complex language, and this book cannot cover all its details. To fully understand the SQL language, you need to learn how to use SQL in Microsoft SQL Server. You can buy a Microsoft SQL Sever 6.5 at a bookstore nearby.

SQL introduction:
This book assumes that you are operating a Microsoft SQL Sever database in SQL. You can also use SQL to operate many other types of databases. SQL is the standard language for operating databases. (In fact, there is a special ANSI standard for the SQL language 〕

Note:
Do not try to replace Microsoft SQL Server with Microsoft Access on your site. SQL Sever can serve many users at the same time, if you want your site to have a high renewal rate, MS Access is not competent.

Before learning the details of SQL, you need to understand its two main features. One feature is easy to grasp, and the other is a little difficult to grasp.
The first feature is that data in all SQL databases is stored in tables. A table consists of rows and columns. For example, the following simple table includes name and email address:
Name Email Address
........................................ ........................
Bill Gates billg@microsoft.com
President Clinton president@whitehouse.com
Stephen Walther swalther@somewhere.com
This table has two columns (the column is also called a field, field): Name and Email Address. There are three rows, each containing a group of data. The combination of data in a row is called a record.
Whenever you add new data to the table, you add a new record. A data table can have dozens of records or thousands or even billions of records. Although you may never need to store billions of Email addresses, it is always good to know that you can do this. Maybe one day you will need this.
Your database may contain dozens of tables. all the information stored in your database is stored in these tables. When you consider how to store information in the database, you should consider how to store them in the table.
The second feature of SQL is difficult to grasp. This language is designed to not allow you to retrieve records in a specific order, because this will reduce the efficiency of SQL Server to retrieve records. With SQL, you can only read records based on query conditions.
When you consider how to retrieve records from a table, you will naturally think of reading them at the record location. For example, you may try to scan records one by one in a loop to select a specific record. When using SQL, you must train yourself.
If you want to select all the records named "Bill Gates", if you use a traditional programming language, you may construct a loop to view the records in the table one by one, check whether the name field is "Bill Gates ".
This method is feasible but inefficient. When using SQL, you just need to say, "select all records whose names are equal to Bill Gates", SQL will select all qualified records for you. SQL will determine the best way to implement the query.
Create the first 10 records you want to retrieve from the table. With traditional programming languages, you can create a loop, retrieve the first 10 records, and end the loop. However, using standard SQL queries is impossible. From the SQL perspective, there is no such concept as the first 10 records in a table.
At the beginning, when you know that you cannot use SQL to implement certain functions that you feel should be able to implement, you will be frustrated. You may hit the wall with your head and even want to write vicious letters to SQL designers. However, you will realize that SQL is not only a limitation, but also its strength. Because SQL does not read records by location, it can read records quickly.
To sum up, SQL has two features: All data is stored in the table. from the SQL perspective, there is no sequence of records in the table. In the next section, you will learn how to use SQL to select special records from the table.

Use SQL to retrieve records from the table.
One of the main functions of SQL is to implement database query. If you are familiar with the Internet engine, you are familiar with the query. You can use queries to obtain information that meets specific conditions. For example, if you want to find all sites with ASP information, you can connect to Yahoo! And execute a search for Active Sever Pages. After you enter this query, you will receive a list containing all the sites whose descriptions contain search expressions.
Most Internet engines allow logical queries. In logical queries, you can include special operators such as AND, OR, and not. you can use these operators to select specific records. For example, you can use AND to restrict query results. If you perform a search for Active Sever Pages and SQL. You will get a description that contains both Active Sever Pages and SQL records. You can use AND to restrict query results.
If you need to extend the query results, you can use the logical operator OR. For example, if you perform a search to search all sites whose descriptions contain Active Sever Pages or SQL, the list you receive contains all the sites whose descriptions contain both expressions or any of them.
If you want to exclude a specific site from the search results, you can use NOT. For example, querying "Active Sever Pages" and not "SQL" will return a list. The site in the list contains Active Sever Pages, but does NOT contain SQL. When a specific record must be excluded, you can use NOT.
Queries executed using SQL are very similar to searches executed using Internet search engines. When you execute an SQL query, you can obtain a record list by using the query conditions that contain logical operators. The query result is from one or more tables.
SQL query syntax is very simple. Suppose there is a table named email_table, which contains two fields: name and address. to get the e_mail address of Bill Gates, you can use the following query:

SELECT email from email_table WHERE name = "Bill Gates"

When this query is executed, the e_mail address of Bill Gates is read from the table named email_table. This simple statement consists of three parts:
■ The first part of the SELECT statement indicates the column to be selected. In this example, only the email column is selected. When executed, only the billg@microsoft.com of the values in the e-mail column is displayed.
■ The second part of the SELECTT statement specifies the table from which data is to be queried. In this example, the name of the table to be queried is email_table.
■ FINALLY, the WHERE clause of the SELECT statement specifies the records that meet the conditions to be selected. In this example, the query condition is that only records with the name column value as Bill Gates are selected.
Bill Gates may have more than one email address. If the table contains multiple email addresses of Bill Gates. The preceding SELECT statement can be used to read all of its email addresses. The SELECT statement extracts the values of all the email fields whose name field value is Bill Gates from the table.
As mentioned earlier, a query can contain logical operators in a query condition. If you want to read all the email addresses of Bill Gates or President Clinton, you can use the following query statement:

SELECT email FROM email_table WHERE name = "Bill Gates" OR
Name = "president Clinton"

The query conditions in this example are a little more complex than the previous one. This statement selects all records whose name columns are Bill Gates or president Clinton from the email_table table. If the table contains multiple addresses of Bill Gates or president Clinton, all addresses are read.
The structure of the SELECT statement looks intuitive. If you ask a friend to select a set of records from a table, you may make your request in a very similar way. In the SQL SELECT statement, you "SELECT a specific column FROM a table WHERE some columns meet a specific condition ".
The next section describes how to execute SQL queries to select records. This will help you familiarize yourself with the various methods for getting data from a table using the SELECT statement.

Execute SELECT query using ISQL
When you install SQL Sever, you also install an application called ISQL/w. ISQL/w allows you to execute interactive SQL queries. Before you include the query in your ASP webpage, it is very useful to test it with ISQL/w.

Note:
In the first part of this book, you learned how to install and configure Microsoft SQL Sever. If SQL Sever is not installed or cannot be run, see Chapter 3 "install and use SQL Sever ".

Select ISQL_w in the SQL Sever program group on the task to start the program. When the program starts, a dialog box is displayed, asking you to enter the server information and logon information (see 0.1 ). In the Sever box, enter the name of your SQL Server. If the server is running on a local computer, the server name is your computer name. In the logon information box, enter a logon account and password, or select "trusted connection", and then click Connect.

0. 1



Note:
If you configure SQL Sever to use full or hybrid security, you can use trusted connections. If you use standard security, you need to provide the user account and password. For more information, see Chapter 3.

If everything works, a query window is displayed after you click the connect button, as shown in Figure 10.2. (If any exception occurs, see Chapter 3)


0.2


You need to select a database before executing the query. When installing SQL Sever, you have created a database for yourself. SQL Sever also has many system databases, such as master, model, msdb, and tempdb.
Conveniently, SQL Server has a special example database named pubs. The database pubs contains tables for a virtual publisher. All the examples in this document are designed for this library. Many examples in this book also use this database.
Select database pubs in the DB drop-down box at the top of the query window, so that you can select a database. All your queries will be executed for each table in this database. Now you can execute your first query. This is really exciting!
Your first query will target a table named autrors. The table contains the relevant data of all the authors working for a virtual publisher. Click the query window and enter the following statements:

SELECT phone FROM authors WHERE au_name = "Ringer"

After entering the information, click the execute query button (a green triangle that looks like the VCR primary key ). After you click this button, any statements that appear in the query window are executed. The query window is automatically displayed as a result display window. you can see the query results (see 0.3 ).
The query result may be different from that shown in 0.3. In different SQL Server versions, the data in the database pubs is different. For SQL Sever 6.5, two records are found. The following content should be displayed in the result display window:

Phone
...................
801 826_0752
801 826_0752
(2 row (s) affected)



0.3



The SELECT statement you run extracts all the telephone numbers of the authors named Ringer from the table authors. You can use special selection conditions in the WHERE clause to limit the query results. You can also ignore the selection conditions and retrieve the phone numbers of all authors from the table. To do this, click the Query tag and return to the Query window. enter the following SELECT statement:

SELECT Phone FROM authors
After this query is executed, all phone numbers in the authors table are taken out (no specific order is available ). If the table authors contains one hundred phone numbers, one hundred records will be taken out. if the table contains one billion phone numbers, these billion records will be taken out (this may take some time ).
Table authrs fields include the last name, name, phone number, address, city, state, and zip code. By specifying them in the first part of the SELECT statement, you can retrieve any field from the table. You can retrieve multiple fields at a time in a SELECT statement, for example:

SELECT au_fname, au_lname, phone FROM authors

After the SELECT statement is executed, all values of the three columns are taken out. The following is an example of the query result (to save paper, only a portion of the query result is displayed, and other records are replaced by ellipsis ):

Au_fname au_lname phone
.....................................................................................
Johnson whitelist 408 496_7223
Marjorie Green 415 986_7020
Cheryl Carson 415 548_7723
Michael O 'Leary 408 286_2428
...
(23 row (s) affected)

In the SELECT statement, you can list the number of fields. Do not forget to separate field names with commas. You can also use the asterisk (*) to retrieve all fields from a table. Here is an example using an asterisk:

SELECT * FROM authors

After this SELECT statement is executed, the values of all fields in the table are taken out. You will find that you frequently use asterisks in SQL queries.

Tips:
You can use asterisks to view the names of all columns in a table. To do this, you only need to check the column title of the query result after executing the SELECT statement.

Operate on multiple tables
So far, you have only tried to use an SQL statement to retrieve data from a table. You can also use a SELECT statement to retrieve data FROM multiple tables at the same time. you only need to list the names of the tables FROM which data is to be retrieved in the FROM clause of the SELECT statement:

SELECT au_lname, title FROM authors, titles

When this SELECT statement is executed, data is retrieved from both the table authors and table titles. Extract all the author names from the table authors and all the names of the authors from the table titles. Run this query in the ISQL/w program and check the query result. You may find some strange unexpected situations: The author's name does not match their book, but all possible combinations of the author's name and title appear, this may not be what you want to see.
What went wrong? The problem is that you did not specify the relationship between the two tables. You didn't tell SQL in any way how to associate a table with a table. Because you do not know how to associate two tables, the server can only simply return all possible combinations of records from the two tables.
To select a meaningful record combination from the two tables, you need to establish the relationship between the fields in the two tables to associate the two tables. One way to do this is to create a third table to describe the relationship between fields in the other two tables.
Table authors has a field named au_id, which contains the unique identifier of each author. Table titles has a field named title_id that contains the unique identifier of each title. If you can establish a relationship between the au_id field and the title_id field, you can associate the two tables. The database pubs has a table named titleauthor, which is used to complete this task. Each record in a table contains two fields used to associate table titles with table authors. The following SELECT statement uses the three tables to obtain the correct results:

SELECT au_name, title FROM authors, titles, titleauthor
WHERE authors. au_id = titleauthor. au_id
AND titles. title_id = titleauthor. title_id

When this SELECT statement is executed, each author will match the correct title. Table titleauthor specifies the relationship between table authors and table titles, which is achieved by including each field from the two tables. The unique purpose of the third table is to establish a relationship between the fields of the other two tables. It does not contain any additional data.
Note how the field name is written in this example. To distinguish the same field name au_id in table authors and table titles, a table name prefix and a full stop are added before each field name. The field named author. au_id belongs to the table authors and the field named titleauthor. au_id belongs to the table titleauthor.
By using the third table, you can establish various types of relationships between two table fields. For example, an author may write many different books or a book may be completed by many different authors. When the fields in two tables have this "many-to-many" relationship, you need to use the third table to specify this relationship.
However, in many cases, the relationship between two tables is not complex. For example, you need to specify the relationship between table titles and table publishers. Because a title cannot match with multiple publishers, you do not need to specify the relationship between the two tables through the third table. To specify the relationship between the table titles and the table publishers, you only need to make the two tables have a public field. In the database pubs, the table titles and the table publishers both have a field named pub_id. If you want to get a list of titles and their publishers, you can use the following statement:
SELECT title, pub_name FROM titles, publishers
WHERE titles. pub_id = publishers. pub_id

Of course, if a book is jointly published by two publishers, you need a third table to represent this relationship.
Generally, when you first know that there is a "many-to-many" relationship between the fields in the two tables, use the third table to join the two tables. If the fields in two tables have only one-to-one or one-to-many relationships, you can use public fields to associate them.

Operation field
Generally, when you extract a field value from a table, the value is associated with the field name defined during table creation. If you select the names of all authors from the table authors, all values are associated with the field name au_lname. However, in some cases, you need to perform operations on the field name. In the SELECT statement, you can replace the default field name with a new name. For example, you can replace the field Name au_lname with a more intuitive and easy-to-read Name Author Last Name:

SELECT au_lname "Author Last Name" FROM authors

When this SELECT statement is executed, the value from the au_lname field will be associated with "Author Last Name. The query result may be as follows:

Author Last Name
................................................................................
White
Green
Carson
O 'Leary
Straight
...
(23 row (s) affected)

Note that the field title is replaced by Author Last Name instead of au_lname.
You can also perform operations to operate the field values returned from a table. For example, if you want to double the price of all books in table titles, you can use the following SELECT statement:

SELECT price * 2 FROM titles

When this query is executed, the price of each book is doubled from the table. However, this method does not change the book price stored in the table. Field operations only affect the output of SELECT statements, but not the data in the table. To display both the original price and the new price after the price increase, you can use the following query:

SELECT price "Original price", price * 2 "New price" FROM titles

When the data is extracted from the table titles, the Original price is displayed under the title Original price, and the doubled price is displayed under the title New price. The result may be as follows:

Original price new price
.........................................................................
19.99 39.98
11.95 23.90
2.99 5.98
19.99 39.98
...
(18 row (s) affected)

You can use most standard mathematical operators to operate field values, such as addition (), subtraction (-), multiplication (*), and division (/). You can also perform operations on multiple fields at a time, for example:

SELECT price * ytd_sales "total revenue" FROM titles

In this example, the total sales of each book is calculated by multiplying the price and the sales volume. The result of this SELECT statement is as follows:

Total revenue
.....................................................
81,859, 05
46,318, 20
55,978, 78
81,859, 05
40,619, 68
...
(18 row (s) affected)

Finally, you can use the concatenation operator (which looks like a plus sign) to connect two struct fields:

SELECT au_fname "" au_lname "author name" FROM authors

In this example, you paste the au_fname field and the au_lname field together, separate them with a comma, and specify the title of the query result as author name. The execution result of this statement is as follows:

Author names
..................................................................
Johnson White
Marjorie Green
Cheryl Carson
Michael O 'Leary
Dean Straight
...
(23 row (s) affected)

As you can see, SQL provides you with many control over the query results. You should make full use of these advantages in ASP Programming. Using SQL to operate on query results is almost always more efficient than using scripts with the same effect.

Sort query results
The introduction in this chapter emphasizes that there is no internal order in the SQL table. For example, it is meaningless to retrieve the second record from a table. From the SQL perspective, there is no record before any other record.
However, you can manipulate the order of the results of an SQL query. By default, when a record is retrieved from a table, the record does not appear in a specific order. For example, when the au_lname field is retrieved from the authors table, the query result is as follows:

Au_lname
...........................................
White
Green
Carson
O 'Leary
Straight
...
(23 row (s) affected)

It is inconvenient to see the names of a column without a specific sequence. It is much easier to read these names in alphabetical order. BY using the order by clause, you can force a query result to be sorted in ascending ORDER, as shown in the following figure:

SELECT au_lname FROM authors order by au_lname

When the SELECT statement is executed, the names of the authors are displayed alphabetically. The order by clause lists the authors in ascending ORDER.
You can also use the order by clause for multiple columns at the same time. For example, if you want to display both the au_lname and au_fname fields in ascending order, you need to sort both fields:

SELECT au_lname, au_fname FROM authors order by au_lname, au_fname

This query first sorts the results by the au_lname field and then by the au_fname field. Records are retrieved in the following order:

Au_lname au_fname
...............................................................................
Bennet Abraham
Ringer Albert
Ringer Anne
Smith Meander
...
(23 row (s) affected)

Note that two authors have the same name Ringer. The author Albert Ringer appeared before Anne Ringer, because Albert was listed before Anne in alphabetical order.
If you want to sort the query results in reverse order, you can use the keyword DESC. The keyword DESC sorts the query results in descending order, as shown in the following example:

SELECT au_lname, au_fname FROM authors
WHERE au_lname = "Ringer" order by au_lname, au_fname DESC

This query retrieves all the author Records named Ringer from the table authors. The order by clause lists the query results in descending ORDER based on the author's name and surname. The result is as follows:

Au_lname au_fname
....................................................................................................
Ringer Anne
Ringer Albert
(2 row (s) affectec)

Note that Anne appears before Albert in this table. The author's name is displayed in descending order.
You can also sort the query results by numeric fields. For example, if you want to retrieve the prices of all books in descending order, you can use the following SQL query:

SELECT price FROM titles order by price DESC

This SELECT statement extracts the prices of all books from the table. when the result is displayed, the prices of low-priced books are displayed first, and those of high-priced books are displayed later.

Warning:
Do not sort the query results unless necessary, because it takes some effort to complete this task on the server. This means that the SELECT statement with the order by clause takes longer to execute than the normal SELECT statement.

Retrieve records of different types
A table may have duplicate values in the same column. For example, the database pubs table authors has two authors named Ringer. If you retrieve all the names from this table, the name Ringer is displayed twice.
Under certain circumstances, you may only be interested in extracting different values from a table. If a field has repeated values, you may want each value to be selected only once. you can use the DISTINCT keyword to achieve this:

Selcet distinct au_lname FROM authors WHERE au_lname = "Ringer"

When this SELECT statement is executed, only one record is returned. By including the DISTINCT keyword in the SELECT statement, you can delete all repeated values. For example, if you want to retrieve the names of all people who have posted information in this newsgroup, you can use the keyword DISTINCT. Each user name is retrieved only once-although some users publish more than one piece of information.
Warning:
Like the order by clause, forcing the server to return different values also increases the running overhead. FUQI had to spend some time to complete the job. Therefore, do not use the DISTINCT keyword when it is not necessary.

Create a new table
As mentioned above, 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.

Use SQL to create a new 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.

Start the ISQL/w program from the SQL 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, 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 this TABLE using the reeate table statement. this statement has two parts: the first part specifies the name of the TABLE, and 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 the comments of the visitor on your site, and the entrydate field stores the date and time when the visitor accesses 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.
Fields have many different data types. The next section describes some important data types supported by SQL.

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.

Balanced 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.

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.

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-1038 to 1038. 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.

Storage logic value
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 sites. Visitors can vote to indicate whether they like your outlets. 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.

Storage date and time
When creating a network, 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 (visitor VARCHAR (40), arrivaltime DATETIME,
Departuretime DATETIME)

This table can be used to record the time and date when visitors enter and exit your site. 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.

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

Allow and disable null values
Most fields can accept NULL values ). When a field accepts a null value, it remains null if you do not change it. NULL is different from zero. Strictly speaking, NULL indicates that no value exists.
To allow a field to accept NULL values, you must use the expression NULL after the field definition. For example, the following table allows null values for both fields:

Create table empty (empty1 CHAR (40) NULL, empty2 int null (

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

Sometimes you need to disable a field from using null values. For example, if a table contains a credit card number and a valid date, you do not want someone to enter a credit card number but do not enter a valid date. To force both fields to input data, you can use the following method to create the table:

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 using the include expression not null, you can prohibit anyone from inserting data in only one field without entering data in another field.
You will find that this capability of prohibiting null values is very useful when you build your own outlets. If you specify a field that cannot accept null values, an error warning is triggered 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 in this table include Street, city, state, zip code, and country. If you expect most of the addresses to be in the United States, you can use this value as the default value of the country field.
To specify the DEFAULT value when creating a table, you can use the DEFAULT expression. See the following example of using the default value 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 country field is specified as the United States. Note the use of single quotes. the quotation marks indicate that this is character-type data. To specify a default value for a non-linear field, do not extend the value in quotation marks:

Create table orders (price money default $38.00,