Operating data: SQL
N SQL foundation N intermediate SQL N advanced SQL ========================================= ================================
N SQL foundation
¨ ¨ SQL introduction
¨ ¨ Use the SELECT statement to take data from the table
¨ ¨ Create a new table
¨ ¨ field properties
¨ ¨ Add data to the table
¨ ¨ ¨ Delete and modify the table
To establish an interactive site, you need to use the database to store information from the visitor. For example, you have to build a professional introduction service, you need to store such information such as personal resume, interested work, etc. Creating a dynamic network also requires the database if you want to display the best work that meets the requirements of the visitor, you need to take out this job from the database. You will find that in many cases you need to use the database.
In this chapter, you will learn how to use the Structured Query Language (SQL] to operate the database. SQL language is the standard language of the database. In Active Sever Pages, whenever you want to access a database, you will Use SQL language. So, master SQL programming is very important to ASP.
note:
You can read "SQL" as "sequel" or read S-Q-L in a single letter. Both pronunities are correct, each pronunciation has a large number of supporters. In this book, it is considered "SQL" to read "Sequel".
Through this chapter, you will understand how to use SQL to implement database queries, you will learn how to use this query from taking information from the data table, in the end, you will learn how to design and build your own database.
note:
By introducing SQL by the following chapters, you will have enough understandings to SQL, so that you can effectively use Active Sever Pages. However, SQL is a complex language that does not have to include all of it. To fully master SQL languages, you need to learn to use SQL in Microsoft SQL Sever. You can buy a Microsoft SQL Sever 6.5 to a nearby bookstore.
SQL introduction:
This book assumes that you are a database of Microsoft SQL Sever in SQL. You can also use SQL to operate many other types of databases. SQL is a standard language for operating a database. (In fact, there is a special ANSI standard for SQL language]
note:
Don't try to use Microsoft Access to replace Microsoft SQL Sever on your site. SQL Sever can serve many users at the same time, if you want your site to have a high access rate, MS Access is uncomfortable.
Before learning SQL details, you need to understand its two characteristics. One feature is easy to master, and the other is a bit difficult. The first feature is that the data in all SQL databases are stored in the table. A table consists of rows and columns. For example, the simple table below includes Name and E-mail address:
Name Email Address
................................................ ............
Bill gates billg@microsoft.com
President CLINTON PRESIDENT@whitehouse.com
Stephen walther swampher@somewhere.com
This table has two columns (columns are also known as fields, domains): Name and Email address. There are three rows, each line contains a set of data. The data in one line is called a record.
Whenever you add new data to the table, you add a new record. A data sheet can have dozens of records, or several thousand or even billion records. Although you may never need to store billion email addresses, you know that you can do this, maybe one day you will have this need.
Your database is likely to contain dozens of tables, all 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 somewhat difficult to master. This language is designed to remove records in a specific order, as this will reduce the efficiency of SQL Sever takes the record. With SQL, you can only read records according to query conditions.
When considering how to remove records from the table, you will naturally think that they read them according to the recorded location. For example, maybe you will try to scan one by a loop, and select a specific record. When using SQL, you have to train yourself, don't have this idea.
If you want to select all the names "Bill Gates" record, if you use a traditional programming language, you may construct a loop, view the records in the table one by one, see if the name domain is "Bill Gates".
This method of selecting records is feasible, but the efficiency is not high. Using SQL, you just say, "Select all the names of the name or equal to Bill Gates", SQL will select all the qualified records. SQL determines the best way to implement queries.
Building you want to take out the top ten records in the table. Using traditional programming languages, you can do a loop, take out the previous ten records, end the loop. But using standard SQL queries, this is impossible to implement. From the perspective of SQL, there is no top ten recordings in a table.
At the beginning, when you know that you can't use SQL to implement some features you feel able to implement, you will be frustrated. You may be able to write vicious letters to SQL designers with head hitting the wall. But later you will realize that this feature of SQL is not only a restricted, but it is its strength. Because SQL does not read records according to position, it can read records quickly.
In summary, SQL has two features: all data is stored in the table, from the perspective of SQL, the records in the table have no order. In the next section, you will learn how to use SQL from the table to choose a special record.
Use SQL from the table to take a record.
One of the main features of SQL is to implement database queries. If you are familiar with the Internet Engine, then you are already familiar with query. You use queries to get information that meets specific conditions. For example, if you want to find all sites with ASP information, you can connect to Yahoo! and perform a search for Active Sever Pages. Once you enter this query, you will receive a list, including all its descriptions that include search expressions. Most Internet Engines allow logical queries. In a logical query, you can include special operators such as and NOT, you can use these operators to select specific records. For example, you can use and and to limit query results. If you perform a search for Active SEVER PAGES AND SQL. You will get a record of Active Sever Pages and SQL in its description. When you need to limit the results of the query, you can use and.
If you need to extend the result of the query, you can use the logical operator or. For example, if you perform a search, search all its descriptions containing the Active SEVER PAGES OR SQL, you receive all its descriptions, including two expressions or any of the expressions, or any of its expressions.
If you want to exclude specific sites from your search results, you can use NOT. For example, Query "Active Sever Pages" and not "SQL" will return a list, and 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.
The query executed with SQL is very similar to the search executed with the Internet Search Engine. When you perform a SQL query, you can get a log list by using query conditions including logical operators. At this point the query is from one or more tables.
The syntax of the SQL query is very simple. Suppose there is a table named email_table, contains two fields of the name and address, to get the Bill Gates's E_MAIL address, you can use the following query:
SELECT Email from email_table where name = "Bill Gates"
When this query is executed, read the E_MAIL address of Bill Gates from a table named email_table. This simple statement includes three parts:
■ The first part of the SELECT statement names the column you want to select. In this case, only the email column is selected. When executed, only the value of the Email column is displayed Billg@microsoft.com.
■ The second part of the SELECT statement indicates which (some) tables you want to query data. In this case, the table name is email_table.
■ Finally, the WHERE clause of the SELECT statement indicates a record of what condition to be selected. In this case, the query condition is selected to be a record that only the value of the Name column is Bill Gates.
Bill Gates is likely to have more than an email address. If the table contains a plurality of Email addresses of Bill Gates. Use the above SELECT statement to read all the email addresses. SELECT statement Removes the value of all Name field values of all Name field values to Bill Gates from the table.
As mentioned earlier, the query can include a logical operator in the query condition. If you want to read all the Email addresses of Bill Gates or 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 more complicated than the previous one. This statement is selected from the table email_table, all Name listed as Bill Gates or President Clinton records. If the table contains the plurality of addresses of Bill Gates or President Clint, all addresses are read. The structure of the SELECT statement looks intuitive. If you invite a friend from a table to choose a group of records, you may make your request in a very similar way. In the SQL SELECT statement, you "Select-specific column from a table where some columns meet a specific condition".
The next section describes how to perform SQL queries to select records. This will help you familiarize yourself with the various methods of using the SELECT statement from the table.
Execute a SELECT query using ISQL
When you install SQL Sever, you install a application called ISQL / W at the same time. ISQL / W allows you to perform an interactive SQL query. It is very useful to test it with ISQL / W before the query is included in your ASP page.
note:
In the first part of this book, you learned how to install and configure Microsoft SQL Sever. If you do not have SQL Sever or SQL Sever can't run, see Chapter 3, "Install and use SQL Sever".
Select the ISQL_W in the SQL Sever program group on the task to start the program. When the program starts, a dialog box will appear to enter server information and login information (see Figure 10.1). In the Sever box, enter your name of your SQL server. If the server is running on the local machine, the server name is the name of your computer. In the login information box, enter a login account and password or choose to use "trusted connection", then click the Connect button.
Figure 10.1
note:
If you configure SQL Sever to use full security or hybrid security, you can use trusted connections. If you use standard security, you need to provide user accounts and passwords. For more information, see Chapter 3.
If everything is normal, a query window will appear after you click the Connection button, as shown in Figure 10.2. (If there is an abnormality, please refer to Chapter 3)
Figure 10.2
Before performing the query, you need to select the database. When you install SQL Sever, you have created a database for yourself, and SQL Sever has many system databases such as Master, Model, MSDB, and Tempdb.
Convenient is that SQL Sever has a special example database called PUBS. The library PUBS contains various tables used by a virtual publisher. All examples in the document are designed for this library. Many examples in this book also use this database.
Select the database PUBS in the DB drop-down box at the top of the query window, so you have chosen the database. All your queries will be performed for each table in this library. Now you can perform your first query. This is really exciting!
Your first query will be a table named Autrors, which contains all the author's authors. Click the Query window and enter the following statement:
SELECT Phone from authors where au_name = "ringer"
After the input is complete, click the Execute Query button (a green triangle, look like a VCR play button). After clicking this button, any statements that appear in the query window are executed. The query window will automatically turn to the result display window, you can see the result of the query (see Figure 10.3).
The result you see may be different from Figure 10.3. In the different versions of SQL Sever, the data in the library PUBS will vary. Two records will be found for SQL SEVER 6.5. The result display window should be displayed: Phone
.................
801 826_0752
801 826_0752
(2 row (s) affected)
Figure 10.3
The SELECT statement you perform will take all the names of the author of Ringer from the table Authors. You limit the results of the query by using special selection conditions in the WHERE clause. You can also ignore the selection criteria, remove all the phone numbers from all authors. To do this, click the Query tab, return to the query window, enter the following SELECT statement:
SELECT Phone from authors
After this query is executed, all phone numbers in Table Authors are removed (there is no specific order). If the table authors contains one hundred phone numbers, there will be hundreds of records that are removed, and if there are billions of phone numbers in the table, this billion record will be taken out (this may take some time).
Table Authrs includes surname, name, phone number, address, city, state, and postal code. You can take any fields from the table by specifying them through the first part of the SELECT statement. You can take a variety of fields at a SELECT statement, such as:
SELECT AU_FNAME, AU_LNAME, Phone from Authors
After this SELECT statement is executed, all values of these three columns will be removed. Below is an example of this result of this query (in order to save paper, only part of the query result is displayed, and the rest of the record is replaced by omnidity):
AU_FNAME AU_LNAME Phone
.........................................................................
Johnson White 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, how many fields do you need to list, you can list how much. Don't forget to separate the field names. You can also use an asterisk (*) to remove all fields from a table. Here is an example of 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 will frequently use the asterisk in the SQL query.
skill:
You can use an asterisk to view the name of all columns of a table. To do this, just take a look at the column header of the query result after executing the SELECT statement.
Operate multiple tables
Up to now, you only try to remove data from a table with a SQL query. You can also remove data from multiple tables simultaneously with a SELECT statement, just list the table name to remove data from it in the FROM clause of the SELECT statement:
SELECT AU_LNAME, TITLE from Authors, Titles
When this SELECT statement is executed, the data is removed from Table Authors and Table Titles. Take all the author names from the table authors, remove all books from the table titles. Execute this query in the ISQL / W program to see the results of the query. You will find some strange unexpected situations: the author's name does not match the books they have, but there are all possible combinations of author name and book name, which may not be what you want to see. . What error did you? The problem is that you did not specify the relationship between these two tables. You didn't tell SQL to associate tables and tables in any way. Since I don't know how to associate two tables, the server can only simply return all possible combinations of records from the records in two tables.
To select a meaningful record combination from two tables, you need to associate two tables by building a relationship between the two tables. One of the ways to do this is to create a third table, specifically used to describe the relationship between the fields of the other two tables.
Table Authors has a field named AU_ID that contains unique identifiers for each author. Table Titles has a field called title_id, contains unique identifiers for each book name. If you can establish a relationship between field au_id and field title_id, you can associate these two tables. There is a table named Titleauthor in the database PUBS, which is used to complete this work. Each record in the table includes two fields to associate table titles and table authors. The following SELECT statement uses these three tables to get the correct result:
SELECT AU_NAME, TITLE from Authors, Titles, Titleauthor
Where authors.au_id = titleauthor.au_id
And titles.title_id = Titleauthor.title_ID
Each author will match the correct book name when this SELECT statement is executed. Titleauthor indicates the relationship between table authors and table titles, which implements this by including a field from two tables. The sole 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. In order to distinguish between the same field name AU_ID in the table authors and table titles, each field name is added with a table name prefix and one sentence. The field named author.au_id belongs to Table Authors, which is a field named titleauthor.au_ID belongs to Titleauthor, and both will not be confused.
By using the third table, you can build a variety of types of relationships between the fields of the two tables. For example, a authors may have written many different books, or a book may be done by many different authors. When there is such a "multi-to-many" relationship between the fields of the two tables, you need to use the third table to indicate this relationship.
However, in many cases, the relationship between the two tables is not complicated. For example, you need to specify the relationship between table titles and tables Publishers. Because a book name is impossible to match multiple publishers, you don't need to indicate the relationship between these two tables through the third table. To refer to the relationship between Titles and Table Publishers, you only need to have a public field in these two tables. In the database PUBS, table titles and tables Publishers have a field called Pub_ID. If you want to get a list of book names and its publishers, you can use the following statement:
Select Title, Pub_Name from Titles, Publishers
Where titles.pub_id = publicishers.pub_id
Of course, if a book is published by two publishers, then you need a third table to represent this relationship.
Typically, when you give a "multi-to-many" relationship between the fields of the two tables, use the third table to associate these two tables. Conversely, if there is only a "one-to-one" or "one-to-one" relationship between the two tables, you can use the public field to associate it. Action field
Typically, when you remove a field value from a table, this value is associated with the field name defined when you create the table. If you select all the author names from the table authors, all values will be connected to the field name AU_LNAME. But in some cases, you need to operate the field name. In the SELECT statement, you can replace it with a new name after the default field name. For example, you can use a more intuitive and easy-to-read name author last name instead of the field name AU_LNAME:
SELECT AU_LNAME "Author Last Name" from authors
When this SELECT statement is executed, the value from the field AU_LNAME is connected to the "Author Last Name". The result of the query may be like this:
Author Last Name
........................................................................ ...
WHITE
Green
Carson
O'Leary
Straight
...
(23 row (s) affected)
Note that the field title is no longer AU_LNAME, but is replaced by Author Last Name.
You can also operate the field value returned from a table by performing operations. For example, if you want to double the price of all the books in Titles, you can use the following SELECT statement:
SELECT price * 2 from Titles
When this query is executed, the price per book will be doubled from the table. However, the field of operation is not changed to the book price stored in the table by this way. The calculation of the field will only affect the output of the SELECT statement without affecting the data in the table. In order to display the original price and price increase of the book, you can use the following query:
Select Price "Original Price", Price * 2 "New Price" from Titles
When the data is removed from the table titles, the original price is displayed under the title Original Price, the doubled price is displayed under the title new price. The result may be like this:
Original Price New Price
.................................................................
19.99 19.99 39.98
11.95 23.90
2.99 2.99 5.98
19.99 19.99 39.98
...
(18 row (s) affected)
You can use most standard math operators to operate field values, such as plus ( ), minus (-), multiply (*) and remove (/). You can also operate multiple fields at a time, for example: select price * ytd_sales "Total Revenue" from Titles
In this example, the total sales of each book are calculated by multiplying prices and sales. The result of this SELECT statement will be like this:
Total Revenue
..................................................
81,859,05
46, 318, 20
55, 978, 78
81,859,05
40, 619, 68
...
(18 row (s) affected)
Finally, you can also use the connection operator (it looks like a plus sign) to connect two characters field:
SELECT AU_FNAME "" au_lname "Author Name" from authors
In this example, you paste the field au_fname and field AU_LNAME together, separated by a comma, and specify the title of the query result as Author Name. The result of this statement will be like this:
Author names
..............................................................
Johnson White
Marjorie Green
CHERYL CARSON
Michael O'Leary
Dean Straight
...
(23 row (s) affected)
It can be seen that SQL provides you with a number of controls for query results. You should take advantage of these advantages during the ASP programming process. Use SQL to operate the query result is almost always more efficient than the same role than the use.
Sort query results
In the introduction of this chapter, it has been emphasized that the SQL table has no inherent order. For example, it is meaningless to take a second record from a table. From the perspective of SQL, there is no record before any other record.
However, you can manipulate the order of SQL query results. By default, when the recording is removed from the table, the record does not appear in a specific order. For example, when the field AU_LNAME is removed from the table authors, the query results are shown in this:
AU_LNAME
........................................
WHITE
Green
Carson
O'Leary
Straight
...
(23 row (s) affected)
It is very inconvenient to see a column name without a specific order. If these names are arranged in alphabetical order, they will be much easier. By using the ORDER BY clause, you can force a query result to arrange in ascending order, just like this:
SELECT AU_LNAME FROM AUTHORS ORDER BY AU_LNAME
When this SELECT statement is executed, the display of the author name will be arranged in alphabetical order. The ORDER BY clause is arranged in ascending the author name.
You can also use the ORDER BY clause to multiple columns at the same time. For example, if you want to display field AU_LNAME and field au_fname at the same time, you need to sort two fields:
SELECT AU_LNAME, AU_FNAME AUTHORS ORDER BY AU_LNAME, AU_FNAME
This query first sorts the results in the AU_LNAME field, and then sequenctions by field au_fname. The record will be taken in the following order:
AU_LNAME AU_FNAME
........................................................................ .bennet Abraham
Ringer Albert
Ringer Anne
Smith meander
...
(23 row (s) affected)
Note that there are two authors have the same name Ringer. The author named Albert Ringer appeared before the authors of Anne Ringer, because the surname Albert should be in the case of the name ANNE in alphabetical order.
If you want to arrange the query results in the opposite order, you can use the keyword DESC. Keywords DESC arrange 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 takes out all the author records for all names Ringer from Table Authors. According to the name and last name of the author, the ORDER BY clause is arranged in descending order. The result is this:
AU_LNAME AU_FNAME
..................................................................... "
Ringer Anne
Ringer Albert
(2 row (s) affectec)
Note In this table, the surname Anne appears before the surname Albert. Author name is displayed in descending order.
You can also sort a query result in numeric fields. For example, if you want to remove all the prices of all books in descending, you can use the following SQL query:
Select Price from Titles Order by Price DESC
This SELECT statement takes out all the prices of all books from the table, when the results show the results, the low price is first displayed, and the price is high after the price is displayed.
caveat:
Do not sort the query results when it is required, because the server completes this work. This means that the SELECT statement with the Order By clause performs a long time than the general SELECT statement.
Take out the same record
A table may have a repetition value in the same column. For example, the name of two authors in the database PUBS is Ringer. If you remove all your names from this table, the name ringer will display twice.
In a particular case, you may only be interested in taking out the same value from one table. If a field has a duplicate value, you may wish that each value is only selected once, you can use the keyword Distin to do this:
SELCET DISTINCT AU_LNAME FROM AUTHORS WHERE AU_LNAME = "Ringer"
When this SELECT statement is executed, only one record is returned. By containing keyword DISTINCT in the SELECT statement, you can delete all duplicate values. For example, suppose there is a table about the newsgroup information, you want to take out the name of the person who has published information in this newsgroup, then you can use the keyword Distinct. Each user's name is only one time - although some users have released more than one information.
caveat:
As with the Order By clause, the forced server returns to each other will also increase operation overhead. Fuqiao has to spend some time to complete this work. Therefore, do not use the keyword distinct when you must be.
Create a new table
As mentioned earlier, all data in the database is stored in the table. Data tables include rows and columns. The column determines the type of data in the table. The line contains actual data. For example, table authors in the database PUBS have nine fields. One of the fields is called Au_lname, which is used to store the author's name information. Each time you add a new author to this table, the author name is added to this field and generate a new record.
By defining a field, you can create a new table. Each field has a name and a specific data type (data type, in the "Field Type" section in the later "section), such as the field AU_LNAME stores characteristic data. A field can also store other types of data.
Using SQL Sever, the method of creating a new table is much. You can perform a SQL statement or use the SQL Transaction Manager (SQL Enterprise Manager) to create a new table. In the next section, you will learn how to create a new table with a SQL statement.
Create a new table with SQL
note:
If you haven't built your own database yet, you will now jump back to the third chapter to create this library. You must never add data to Master, Tempdb, or any other system database.
Start the ISQL / W program from the SQL Sever program group (in the taskbar). After 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 SQL statement below in the query window, click the Execute Query button, perform this 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 you have an exception, please refer to Chapter 3):
THIS Command Dit Not Return Data, And It Did Not Return Any Rows
Congratulations, you have established your first table!
The table name you created is GUESTBOOK, you can use this table to store the information of your site visitor. Are you created with a reeate table statement, this statement has two parts: the first part of the name of the specified table; the second part is the name and attribute of each field in parentheses, with a comma with each other open.
Table GuestBook has three fields: Visitor, Comments, and Entrydate. Visitor field Stores the visitor's name, Comments field Stores the visitor to your site, and the entryDate field stores the visitor to access your site date and time.
Note that each field name is followed by a specialized expression. For example, the field name comments followed by expression text. This expression specifies the data type of the field. The data type determines what kind of data can be stored. Because the field comments contains text information, its data type is defined as text.
Fields have many different data types. The next section tells some important data types supported by SQL.
Field Type
Different field types are used to store different types of data. When you create and use a table, you should understand the five common field types: characters, text, numerical, logical, and date types.
Character data
Character data is very useful. When you need to store short string information, you always use characters. For example, you can put the information collected from the HTML Form text box in the character field.
To create a string information that is used to store variable lengths, you can use expression varchar. Consider the table GuestBook created in front:
Create Table GuestBook (Visitor Varchar (40), Comments Text, EntrydateTime)
In this example, the data type of the field Visitor is VARCHAR. Pay attention to the numbers in parentheses behind the data type. This figure specifies the maximum length of the string allowed to be stored in this field. In this example, the string of the field Visitor can be stored for a maximum of forty characters. If the name is too long, the string will be truncated and only forty characters are kept.
The VARCHAR type can store the string of up to 255 characters. To store longer string data, you can use text data (described in the next section).
Another characteristic data is used to store character data of the fixed length. Below 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 a fixed length string of forty characters. Expression char Specifies this field to be a fixed length string.
This difference in VARCHAR type and CHAR type data is subtle, but it is very important. 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 very large.
Text data
Characteristic data limits the length of the string that cannot exceed 255 characters. 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.
Here is an example of using textual data:
Create Table GuestBook (Visitor Varchar (40), Comments Text, Entrydate
Datetime)
In this example, the field Comments is used to store the visitor to your site. 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.
caveat:
Once you enter any data (even null value) to the text field, there will be 2K spaces to be automatically assigned to this data. You cannot recover this part of the storage space unless you delete this record.
Numerical data
SQL Sever supports many different numerical data. You can store integer, decimal, and money.
Usually, when you need to store numbers in the table, you want to use integer (int) data. The number of INT data is an integer from -2, 147, 483, 647 to 2, 147, 483, 647. Below 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 times your site is accessed. As long as no one visits your site exceeds 2,147,483,647 times, the NubVisits field can store access times.
In order to save memory space, you can use Smallint type data. Smallint data can store an integer from -32768 to 32768. This method of use of this data type is identical to the INT type.
Finally, if you really need to save space, you can use Tinyint type data. Similarly, this type of use is also the same as the INT type, and the difference is that this type of field can only store an integer from 0 to 255. Tinyint type field cannot 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.
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. 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 will be created. Field Bignumber can store until 28-bit integers. Field FRACTION can store a decimal number of five integer parts and four fractional parts.
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.
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. The following example shows how to use these two data types that represent money:
Create Table Products (Product VARCHAR (40), Price Money,
Discount_price smallmoney)
This table can be used to store discounts and ordinary selling prices. The data type of the field Price is Money, the data type of field discount_price is SmallMoney. Storage logic value
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. Here has an example of how to use this field:
Create Table Opinion (Visitor Varchar (40), Good Bit)
This table can be used to store information about your outlets for public opinion investigations. Visitors can vote whether they like your outlets. If they invest yes, they are deposited in the Bit field. Conversely, if they invest NO, deposit 0 in the field (in the next chapter, you will learn how to calculate vote).
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.
Store dates and time
When you create an outlet, you may need to record the number of visitors in a period of time. In order to store the 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 of the visitor to enter and leave your outlet. A DateTime type field can store the date range from January 1, 1753 to
9999
December
The last milliseconds 31.
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. In the next chapter, you will learn how to use a large number of SQL functions to read and operate dates and times (see "Default" section below). You can also use the date and time function in VBScript and JScript to enter a date and time in a DateTime field.
Field properties
The previous section describes how to build a table containing different types of fields. In this section, you will learn how to use the three properties of the field. These attributes allow you to control null, default, and identifier.
Allow and disable null value
Most types can be accepted for null. When a field accepts a null value, if you don't change it, it will keep a null value. Null values (null) and zero are different, strictly said that null value indicates no value.
To allow a field to accept null values, you have to use the expression null after the field definition. For example, two fields in the table are allowed to accept null values:
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 ban a field to use null values. For example, suppose there is a table stores a credit card number and a credit card valid date, you will not want someone to enter a credit card number but not enter the valid date. To force two fields to enter data, you can build this table with the following method: Create Table Creditcards (CreditCard_Number Char (20) Not Null,
CreditCard_expire datetime not null)
Note that the field definition is followed by express NOT NULL. By including expression not null, you can disable anyone from inserting data in a field without entering the data of another field.
You will find that this prohibited null value is very useful during you to build your own outlet. If you specify a field that cannot be accepted null, then when you try to enter an empty value, there will be error warnings. These false warnings can provide programs to provide a valuable clue.
Default
Suppose there is a table that stores address information, the fields of this table include streets, cities, states, postal codes and countries. If you expect most of the address in the United States, you can use this value as the default value of the Country field.
In order to specify a default value when creating a table, you can use the expression default. Please see the 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 field country is designated as the United States. Pay attention to the use of single quotes, indicate that this is characteristic data. In order to specify the default value for the field of non-character types, do not extend this value in quotation marks:
Create Table Orders (Price Money Default $ 38.00,
Quantity Int Default 50,
EntryDate DateTime Default getdate ())
In this CREATE TABLE statement, each field specifies a default. Note that the default value specified by the DateTime field entrydate, which is the return value of the function getDate (), which returns the current date and time.
Logo field
Each table can have one that can only have one identification field. A identification field is a special field of each record in the unique identity table. For example, table jobs in the database PUBS contain a unique identifier each work identification field:
JOB_ID JOB_DESC
................................................
1 1 New Hire Job NOT Specified
2 2 Chief Executive Offer
3 3 Bushness Operations Manager
4 4 Chief Financial OFFICIER
5 5 Publisher
Field JOB_ID provides a unique number for each job. If you decide to add a new job, the newly recorded JOB_ID field will be automatically assigned a new unique value.
In order to establish a identification field, you only need to add an expression Identity after the field is defined. You can only set the numeric or int type field to the identity field, here there is an example: Create Table Visitorid (Theid Nuberic (18) Identity, Name Varchar (40))
The table created by this statement contains an identification field called theid. Whenever a new visitor name is added to this table, this field is automatically assigned to a new value. You can use this table to provide unique identifiers for each user of your site.
skill:
When establishing a marked field, pay attention to use a sufficiently large data type. For example, you use Tinyint type data, then you can only add 255 records to the table. If you expect a table that may become big, you should use Numeric type data.
The presence of identification fields will make you try a lot of impossible things. For example, you may want to use identification fields to make the records based on their location in the table. You should abandon this intention. The value of the identification field of each record is not the same, but this does not disable intervals between the identification numbers of the identity field. For example, you will never try to take out the top ten records in the table with a table identity field. This operation will result in failure, such as the No. 6 records and records of the No. 7 not exist.
Create a new table using SQL Transaction Manager
You can create a new table using the methods mentioned in the previous section. However, it is easier to create a new table using the transaction manager. This section describes how to create a new table using this program.
Select SQL Enterprise Manager from the SQL Sever program group of the taskbar, start the program, you will see the window shown in Figure 10.4. Browse the tree structure in the Service Manager window, select the folder named Database. After opening the folder Database, select the database you established in Chapter 3.
note:
If you haven't created your own database, return to Chapter 3 Create it. You must never add data to Master, Tempdb, or any other system database.
After selecting the database, you will see a folder called Group / Users and a folder named Objects. Open the folder Objects, you will see a lot of folders, one of which is named Tables. Use the right-click Folder Tables and select New Table, the window shown in Figure 10.5 will appear.
You can use the Manager Tables window to create a new table. The Manager Tables window has 7 columns: Key, Column, Name, DataType, Size, Nulls, and Default. The Manager Tables window is marked with information on a field in the table.
Figure 10.4
10.5
To create a new table, you should enter at least one line of information. Type MyColumn in columns named Column Name. Next, select the DataType column and select CHAR from the drop-down list. When you enter the information in these two columns, the window will appear as shown in Figure 10.6.
Figure 10.6
You have established a simple table with only one field. Click Save button to save this new table. When you ask you to enter the name of the new table, enter MyTable and click OK. Now this table has been saved in your database.
If you open the folder tables in the Service Manager window, you will see the new table you build is listed. You can double-click the chart of the table to edit it, then the Manager Tables window will appear, you can add new fields and save it.
Work with SQL Transaction Manager can do it with SQL statements. However, the transaction manager makes it easy to build a table.
Add data to the table
The next chapter will discuss how to use SQL to insert data into a table. However, if you need to add a number of records to a table, you are very inconvenient to use SQL statement. Fortunately, Microsoft SQL Sever has a client application called Microsoft Query, which makes it easy to add data to the table. Start the Microsoft Query program located in the taskbar SQL Sever program group. Select File | New Query from the menu at the top of the window. A SELECT DATA SOURCE dialog is displayed (see Figure 10.7). Select your data source name and click Use.
Figure 10.7
Enter your login account and password, the program requires you to choose a table and a database. Select the table (MyTable) created in the previous section, click the Button Add, and then click the Button Close to close the dialog.
A dialog box appears in the upper left corner of the window, and a column name of the table MyTable is taken. You can double-click any field to add it to the main window. If you double-click the asterisk (*) character, all fields are added to the main window.
If there is a record in your table, they have now appearing below the field header of the main window. However, because you have just built this table, the table is empty. To add a new record, select Records | Allow Editing, a new record will appear in the main window. Enter a line of data to complete this record, add a new record to the table.
Figure 10.8
When you go to the next new record, the values you entered in the previous record are automatically saved. If you need, you can use Microsoft Query to enter hundreds of records in the table.
Delete and modify the table
You should carefully design them before building a table, because you will be greatly limited when you change an existing table. For example, once a table has been established, you cannot delete the fields in the table or change the data type of the field. In this case, you can do it to delete this table, then start (see "Creating Records and Table" section in Chapter 11 "Intermediate SQL").
To delete a table, you can use the SQL statement Drop Table. For example, you should completely delete the table myTable from the database, you want to use the following statement:
Drop Table MyTable
caveat:
Be careful when using the Drop table command. Once a table is deleted, you will not be able to restore it.
When you build a site, you are likely to need to enter test data to your database. And when you are ready to provide your outlets to the world, you will want to empty these test information in the table. If you want to clear all the data in the table but don't delete this table, you can use the TRUNCATE TABLE statement. For example, the following SQL statement removes all data from the table myTable:
Truncate Table MyTable
Although you can't delete and modify the existing fields, you can add new fields. The easiest implementation method is to use the Manager Tables window in the SQL Transaction Manager. You can also use the SQL statement Alter Table. Here is an example of how to use this statement:
ALTER TABLE MyTable Add MyneWColumn Int Null
This statement adds a new field MyNewColumn to the table myTable. When you add new fields, you must allow it to accept null values because there may already have many records in the table.
to sum up
This chapter introduces you SQL. With SQL, you can operate the Microsoft SQL Sever database. You have learned to use the SELECT statement from the database from the database, you have learned how to use the CREATE TABLE statement and SQL Transaction Manager to create a new table. Finally, you learned how to indicate a series of important field properties. The next chapter will show how to use an index to enhance the operation of the SQL query. It will also be further expanded by many other SQL statements and functions.
■ Intermediate SQL
¨ ¨ Create an index
¨ ¨ SQL core statement
¨ ¨ set function
¨ ¨ ¨ Other common SQL expressions, functions, and processes
Chapter 10 "SQL Basics" introduces you to SQL. Do you learn how to use the SELECT statement to query, you have learned how to build your own table. In this chapter, you will deepen your SQL knowledge. You will learn how to build an index to speed up the query. You will also learn to operate the data in the table if you use more SQL statements and functions.
Establish an index
Suppose you want to find a sentence in this book. You can search on a page on a page, but this will spend a lot of time. By using the index of this book, you can quickly find the topics you want to search.
The index of the table is very similar to the index attached behind a book. It can greatly improve the speed of the query. For a larger table, by adding, a query that is usually taken for a few hours will be completed as long as a few minutes. Therefore, there is no reason to increase the index for tables that need frequent queries.
note:
When your memory capacity or hard disk space is insufficient, you may not want to add an index to a table. For a database containing an index, SQL Sever requires a considerable extra space. For example, to establish a clustered index, it takes about 1.2 times the space of the data size. Take a look at the index of a table to occupy the space size, you can use the system stored procedure sp_spaceused, the object name is specified as the named name of the index.
Cluster index and non-clustered index
Suppose you have found the page number where a sentence is located through the index of this book. Once you already know the page number, you are likely to look up this book until you find the correct page number. Through random flips, you can eventually reach the correct page number. However, there is a more effective way to find the page number.
First, turn the book to half of the place. If the page number to be found is smaller than the page number of the half book, the book is turned to a quarter, otherwise, turn the book to a quarter place. With this method, you can continue to divide the book into a smaller part until you find the correct page number. This is a very effective way to find a book page.
The table index of SQL Sever works in a similar manner. A table index consists of a set of pages, which constitute a tree structure. The root page is logically divided into and two parts by pointing to the other two pages. The two pages pointed to by the root page separately divide the records into smaller parts. Each page is divided into smaller splits until the leaf-level page is reached.
There are two types of indexes: clustered indexes and non-clustered indexes. In the clustered index, the leaf-level page of the index tree contains actual data: the index order of the record is the same as the physical order. In the non-clustered index, the leaf-level page points to the record in the table: the physical order of the record is not necessarily associated with the logical order.
Cluster index is very like a catalog table, the order in which the catalog table is consistent with the actual page number. Non-clustered indexes are more like a standard index table, the order in the index table is usually inconsistent with the actual page number order. A book may have multiple indexes. For example, it may have a topic index and authors index. Similarly, a table can have multiple non-clustered indexes.
Typically, you are using clustered indexes, but you should understand the advantages and disadvantages of the two types of indexes.
Each table can only have a clustered index because the records in a table can only be stored in a physical order. Usually you want to establish a clustered index according to the identity field. However, you can also establish clustered indexes, such as characters, numerical, and datetime fields for other types of fields. Removing data from the table established a clustered index, it is more fast than the establishment of a non-clustered index. When you need to take out some range of data, use clustered indexes to be compared with non-clustered indexes. For example, suppose you use a table to record the activity of the visitor on your outlet. If you want to take out login information within a certain period of time, you should establish a clustered index on this table's DateTime field.
The main limitation of the clustered index is that each table can only establish a clustered index. However, a table can have more than one non-clustered index. In fact, you can build up to 249 non-clustered indexes for each table. You can also establish clustered indexes and non-clustered indexes at the same time.
If you not only want to get data from your dot activity log according to the date, but want to get data from your outlet activity. In this case, it is effective to establish a clustered index and the non-clustered index. You can establish a clustered index on the datetime field, and establish a non-clustered index of the username field. If you find that you need more indexing methods, you can add more non-clustered indexes.
Non-clustered indexes require a lot of hard disk space and memory. In addition, although the non-clustered index can improve the speed of data from the table, it also reduces the speed of inserting and updating data into the table. Whenever you have changed a data in a table in which the non-clustered index must be established, the index must be updated. So you should carefully consider when you build a non-clustered index. If you expect a table to update the data frequently, do not build too many non-clustered indexes. In addition, if the hard disk and the memory space are limited, the number of non-clustered indexes should also be restricted.
Index properties
Both types of indexes have two important properties: You can use any types in the two to establish an index (composite index) for multiple fields; two types of indexes can be specified as unique indexes.
You can create a composite index for multiple fields, or even composite clustered indexes. If there is a table to record your online visitor's last name and name. If you want to get data from the table from the table based on the full name, you need to build an index that simultaneously on the name field and the name field. This is different from the index of two fields to establish separate indexes. When you want to query more than one field, you should establish an index of multiple fields. If you want to query each field, you should establish an independent index for each field.
Both types of indexes can be designated as unique indexes. If a unique index is established for a field, you will not enter the repeated value to this field. A identification field will automatically become a unique value field, but you can also establish a unique index for other types of fields. Suppose you use a table to save your network user password, you certainly don't want the two users to have the same password. By enforcing a field into a unique value field, you can prevent this happening.
Establish an index with SQL
In order to establish an index to a table, start the ISQL / W program in the taskbar SQL Sever program group. After entering the query window, enter the following statement:
Create index mycolumn_index on myTable (Myclumn)
This statement has established an index called MyColumn_index. You can cause any name to a claim, but you should include the field name index in the index name, which is helpful to make you clear the intention of building the index.
note:
In this book you perform any SQL statement, you will receive the following information:
THIS Command Did Not Return Data, And It Did Not Return Any Rows
This shows that the statement is successful.
Index MyColumn_index is performed on the Mycolumn field of the table myTable. This is a non-clustered index and a non-unique index. (This is an index default attribute)
If you need to change the type of index, you must delete the original index and rebuild one. After establishing an index, you can delete it with the following SQL statement: Drop Index MyTable.MyColumn_index
Note that you have the name of the table in the Drop Index statement. In this example, the index you delete is MyColumn_index, which is the index of the table MyTable.
To create a clustered index, you can use the keyword clustered. Remember that a table can only have a clustered index. (Here is an example of how to build cluster indexes for a table:
Create Clustered Index MyColumn_Clust_index on MyTable (MyColumn)
If there is a duplicate record in the table, an error will occur when you try to establish an index with this statement. But there are tables with repeated records can also establish an index; you just tell SQL Sever using keyword allow_dup_row:
Create Clustered Index MyColumn_cindex on MyTable (MyColumn)
WITH ALLOW_DUP_ROW
This statement establishes a clustered index that allows repeated records. You should try to avoid repeated records in a table, but if you have already appeared, you can use this method.
To establish a unique index for a table, you can use the keyword unique. This keyword can be used for clustered indexes and non-clustered indexes. Here is an example:
Create Unique Counstered Index Myclumn_cindex on MyTable (MyColumn)
This is the index that you will use. Whenever, as long as you can, you should try to enhance the query operation to build a single cluster index to a table.
Finally, build an index for multiple fields - composite index - contains multiple field names while indexing establishment statements. The following example establishes an index for both fields of FirstName and lastname:
Create Index Name_index ON Username (Firstname, Lastname)
This example establishes a single index for two fields. In a composite index, you can index up to 16 fields.
Establish an index with a transaction manager
Establishing an index with a transaction manager than more than using the SQL statement. Using Transaction Manager, you can see a list of indexes that have been established and you can select an index option via a graphical interface.
Using the Transaction Manager You can build an index in two ways: Use the Manage Tables window or use the Manage Indexes window.
To create a new index with the Manage Tables window, click the button Advanced Options (it looks like a table with a plus number in front). This opens the Advanced Options dialog. This dialog is part of the Named Primary Key (see Figure 11.1).
Figure 11.1
To create a new index, select the field name that you want to establish an index from the drop-down list. If you want to build an index of multi-field, you can choose multiple field names. You can also choose an index whether it is clustered or non-clustered. After saving the table information, the index will be automatically established. Next to the field name in the Manage Tables window, a key will appear.
You have established a "main index" for your table. The main index must be established for fields that do not contain null values. In addition, the main index is enforceding a field to become a unique value field.
To establish an index without these restrictions, you need to use the Manage Indexes window. Select Manage | Indexes from the menu to open the Manage INDEXES window. In the Manage Indexes window, you can select a table and a specific index through the drop-down box. (See Figure 11.2). To create a new index, select New Index from the Index drop-down box., Then select the field you want to establish indexes. Click the button Add to add the field to the index. Figure 11.2
You can choose a lot of different options for your index. For example, you can choose whether the index is clustered or non-clustered. You can also specify that the index is a unique index. After designing the index, click Button Build to create this index.
note:
The only index refers to this field that cannot be repeated, not only this index.
SQL core statement
In Chapter 10, you learned how to use the SQL SELECT statement from one table from a table. However, until now, it has not discussed how to add, modify, or delete the data in the table. In this section, you will learn these content.
Insert data
Add a new record to the table, you want to use the SQL INSERT statement. There is an example of how to use this statement:
INSERT MyTable (MyColumn) Values ('Some Data')
This statement puts the string 'Some Data' into the MyTable myTable mycolumn field. The name of the field to be inserted into the data is specified in the first parentheses, and the actual data is given in the second parentheses.
The complete sentence of the INSERT statement is as follows:
INSERT [INTO] {Table_Name | View_name} [(Column_List)] {Default Values |
VALUES_LIST | SELECT_STATEMENT}
If a table has multiple fields, you can insert data into all fields by using a comma with a comma with a comma. Assume that the table MyTable has three field first_column, second_column, and third_column. The following INSERT statement adds a full record of three fields:
INSERT MyTable (first_column, second_column, third_column)
VALUES ('Some Data', 'Some More Data', 'Yet More Data')
note:
You can insert data into the text field using the INSERT statement. However, if you need to enter a long string, you should use the WRITETEXT statement. This part of the content is too advanced to this book, so it will not discuss it. For more information, please refer to the documentation of Microsoft SQL Sever.
What if you only specify two fields and data in the insert statement? In other words, you insert a new record to a table, but there is no data to provide data. In this case, there are four possibilities:
■ ■ If this field has a default value, this value will be used. For example, suppose you do not provide data to the field third_column when you insert a new record, and this field has a default value 'some value'. In this case, the value 'some value' is inserted when the new record is established.
■ ■ If this field can be accepted, there is no default, it is inserted.
■ ■ If this field cannot be accepted, there is no error in the default value. You will receive an error message:
The Column In Table MyTable May Not Be Null.
■ ■ Finally, if this field is a identification field, it will automatically generate a new value. When you insert a new record to a table with identity fields, as long as you ignore this field, the identification field will give yourself a new value. note:
After inserting a new record into a table with identification field, you can use SQL variable @@ identity to access new records
The value of the identity field. Consider the following SQL statement:
INSERT MyTable (First_Column) Values ('Some Value')
INSERT Anothertable (Another_first, Another_Second)
VALUES (@@ iDentity, 'some value')
If the table MyTable has a identification field, the value of this field is inserted into the another_first field of the table anothertable. This is because the variable @@ identity always saves the value of the last inserted identity field.
Field Another_First should have the same data type as the field first_column. However, field another_first cannot be identified by the field. The Another_First field is used to save the value of the field first_column.
Delete Record
To remove one or more records from the table, you need to use the SQL DELETE statement. You can give the DELETE statement with a WHERE clause. The WHERE clause is used to select the record you want to delete. For example, the DELETE statement below deletes the value of the field first_column is equal to the record of 'Delete ME':
Delete mytable where first_column = 'deltet me'
The complete sentence of the delete statement is as follows:
Delete [from] {TABLE_NAME | View_name} [Where clause]
Any conditions that can be used in the SQL SELECT statement can be used in the WHERE clause of the delect statement. For example, the DELETE statement below deletes the value of the value of the first_column field is 'goodbye' or the second_column field of 'So long':
Delete mytable where first_column = 'goodby' or second_column = 'so long'
If you don't give the DELETE statement, all records in the table will be deleted. You should not have this idea. If you want to delete all the records in the table, you should use the Truncate Table statement told by Chapter 10.
note:
Why use the TRUNCATE TABLE statement instead of the DELETE statement? When you use the TRUNCATE TABLE statement, the recorded deletion is not recorded. That is, this means that Truncate Table is much better than Delete.
update record
To modify one or more records already existing in the table, you should use the SQL UPDATE statement. Like the Delete statement, the UPDATE statement can use the WHERE clause to select a specific record. Please see this example:
Update myTable set first_column = 'updated!' Where second_column = 'update me!'
This update statement updates the value of all second_column fields to 'update me!'. For all selected records, the value of field first_column is set to 'updated!'.
Below is the full syntax of the UPDATE statement:
Update {Table_name | View_name} set [{table_name | View_name}]
{color_list | variable_list | variable_and_column_list} [, {color2_list2 | variable_list2 | variable_and_column_list2} ...
[, {colorn_listn | variable_listn | variable_and_column_listn}]]]
[WHERE CLAUSE]
note:
You can use the UPDATE statement for text type fields. However, if you need to update a long string, use the UpdateText statement. This part of the content is too advanced to this book, so it will not discuss it. For more information, please refer to the documentation of Microsoft SQL Sever.
If you don't provide a WHERE clause, all records in the table will be updated. Sometimes this is useful. For example, if you want to double the price of all the books in Titles, you can use the UPDATE statement:
You can also update multiple fields at the same time. For example, the following UPDATE statement is updated at the same time updating first_column, second_column, and third_column these three fields:
Update myTable set first_column = 'updated!'
SECOND_COLUMN = 'Updated!'
Third_column = 'updated!'
Where first_column = 'Update ME
1
'
skill:
SQL ignores excess space in the statement. You can write the SQL statement into any format you easier to read.
Create records and tables with SELECT
You may have noticed that the INSERT statement is different from the DELETE statement and the Update statement. It only operates a record at a time. However, there is a way to make the INSERT statement to add multiple records at a time. To do this, you need to combine the INSERT statement with the SELECT statement, like this:
INSERT MyTable (first_column, second_column)
Select Another_First, Another_Second
From anothertable
Where another_first = 'Copy ME!'
This statement records from anothertable to myTable. Only the value of the field another_first in the table another_first is' Copy ME! 'Records were copied.
This form of INSERT statement is very useful when establishing a backup for recording in a table. Before deleting records in a table, you can copy them to another table with this method.
If you need to copy the entire table, you can use the SELECT INTO statement. For example, the following statement creates a new table called NewTable, which contains all the data of the table MyTable:
Select * INTO NewTable from MyTable
You can also specify that only specific fields are used to create this new table. To do this, just specify the field you want to copy in the list of fields. In addition, you can use the WHERE clause to limit the records copied to the new table. The following example only copies the value of the field second_columnd is equal to the first_column field of the record of 'Copy ME!'.
SELECT First_Column InTo NewTable
From myTable
Where second_column = 'copy me!'
It is difficult to use SQL to modify the table that has been established. For example, if you add a field to a table, there is no easy way to remove it. Also, if you accidentally give the data type of a field, you will have no way to change it. However, using the SQL statement described in this section, you can bypass these two questions. For example, suppose you want to delete a field from a table. Using the Select INTO statement, you can create a copy of the table, but do not include the fields to be deleted. This allows you to delete this field and retain data that you don't want to delete.
If you want to change the data type of a field, you can create a new table that contains the correct data type field. After you create the table, you can use the UPDATE statement and the SELECT statement to copy all the data in the original table to the new table. In this way, you can modify the structure of the table and save the original data.
Collection function
So far, you only learn how to remove one or more records from the table based on a particular condition. However, if you want to count the records in a table. For example, if you want to count the voting result of a public opinion stored in the table. Or do you want to know how much time a visitors spend on your site. To count any type of data in the table, you need to use a set function.
Microsoft SQL supports five types of collection functions. You can count the number, average, minimum, maximum, or sum. When you use a collection function, it only returns a number, which represents one of these statistics.
note:
To use the return value of the collection function in your ASP page, you need to give this value to a name. To do this, you can follow a field name in the SELECT statement, as shown in the following example:
SELECT AVG (Vote) 'The_average' from Opinion
In this example, the average of Vote is named the_average. Now you can use this name in the database method of your ASP page.
Number of statistical field values
Function count () may be the most useful collection function. You can use this function to count how many records in a table. Here is an example:
SELECT Count (au_lname) from authors
This example calculates the number of names (Last Name) in Authors. If the same name has appeared more than once, the name will be calculated multiple times. If you want to know how many of the names of a particular value, you can use the WHERE clause, as shown in the following example:
SELECT Count (au_lname) from authors where au_lname = 'ringer'
This example returns the number of authors of 'ringer'. If this name appears twice in Table Authors, the return value of the subunies is 2.
If you want to know the number of authors with different names. You can get this number by using keyword Distinct. As shown in the following example:
Select Count (Distinct au_lname) from authors
If the name 'ringer' is more than once, it will only be calculated once. The keyword Distinct determines that only a value that is different from each other is calculated.
Typically, when you use count (), the null value in the field will be ignored. In general, this is exactly what you want. However, if you just want to know the number of records in the table, you need to calculate all records in the table - whether it contains null values. Here is an example of how to do this:
SELECT Count (*) from authors
Note that the function count () does not specify any fields. This statement calculates all records in the table, including a null value record. Therefore, you don't need to specify specific fields to be calculated. Functions count () are useful in many different situations. For example, suppose there is a table saved the results of the quality of your site for public opinion. This table has a field named Vote, the value of this field is 0.0, or 1.0 means opposition, 1 means it as a ticket. To determine the number of votes, you can all the following SELECT statements:
SELECT Count (vote) from opinion_table where vote = 1
Calculate the average of the field
Use the function count (), you can count how many values in a field. But sometimes you need to calculate the average of these values. Using a function avg (), you can return the average of all values in a field.
If you make a more complicated public opinion on your site. Visitors can vote between 1 and 10, indicating that they like your site. You save the votes in the int type field named Vote. To calculate the average of your users vote, you need to use the function avg ():
SELECT AVG (Vote) from Opinion
The return value of this SELECT statement represents the average version of the user to your site. Functions AVG () can only be used for numeric fields. This function is also ignored when the average is calculated.
Calculate the field value and
Suppose your site is used to sell cards, it has been running for two months, it is the time when it makes it. Assume that there is a table named ORDERS to record all visitors' order information. To calculate the sum of all orders, you can use the function SUM ():
Select SUM (Purchase_Amount) from Orders
The return value of the function SUM () represents the average of all values in the field purchase_amount. The data type of the field purchase_amount may be MONEY type, but you can also use the function sum () to other numeric fields.
Return maximum or minimum
Once again, you have a menu for saving your site for public opinion. Visitors can choose from 1 to 10 to represent their evaluation of your site. If you want to know the visitor's highest evaluation of your site, you can use the following statement:
SELECT MAX (Vote) from Opinion
You may want someone to give your site very high evaluation. With the function max (), you can know the maximum value in all values of a value field. If someone casts a number 10 on your site, the function max () will return this value.
On the other hand, if you want to know the minimum evaluation of the visitors to your site, you can use the function min (), as shown in the following example:
SELECT MIN (VOTE) from Op Opinion
The functionmin () returns the minimum of all the values of a field. If the field is empty, the function min () returns a null value.
Other common SQL expressions, functions, and processes
This section will introduce some other SQL technology. You will learn how to remove data from the table, where a certain field is at a certain range, you will also learn how to convert field values from one type to another, how to operate strings and date time data. Finally, you will learn a simple way to send an email.
Remove data by matching a range of values
Suppose you have a table for saving your site for public opinion. Now you want to send a written letter from the visitors of 7 to 10 to your site. To get the names of these people, you can use the following SELECT statement: Select UserName from Opinion Where Vote> 6 and vote <11
This SELECT statement will realize your requirements. You can get the same result using the SELECT statement below:
Select Username from Opinion WHERE Vote Between 7 and 10
This SELECT statement is equivalent to the previous statement. Which statement to use is a programming problem, but you will find that the statement that uses expression BetWeen reads easier.
Now suppose you just want to take out the name of the visitors of 1 or 10 for your site. To take out these names from the table Opinion, you can use the following SELECT statement:
Select username from opinion where vote = 1 or vote
This SELECT statement will return the correct result and there is no reason not to use it. However, there is a way equivalent. Use SELECT to get the same result:
Select UserName from Opinion WHERE Vote In (1, 10)
Pay attention to the use of expressions in. This SELECT statement takes only the value of the Vote's value equal to one of the values in parentheses.
You can also use IN to match the character data. For example, suppose you just want to take out the vote value of Bill Gates or President Clinton. You can use the following SELECT statement:
Select Vote from Opinion WHERE UserName In ('PRESIDENT CLINTON')
Finally, you can use Expression Not while using BetWeen or IN. For example, to take out the names of those who have not on 7 to 10, you can use the following SELECT statement:
Select Username from Opinion WHERE VOTE NOT BETWEEN 7 and 10
To select the value of a field is not in a column value, you can use NOT and IN simultaneously, as shown in the following example:
Select Vote from Opinion
Where username not in ('bill Gates', 'President Clinton')
You don't have to use BetWeen or in in the SQL statement, but you have to make your query closer to the natural language, these two expressions are helpful.
Conversion data
SQL Sever is strong enough to convert most values from one type of type to another when needed. For example, to compare the size of the SmallINT type and INT data, you do not need to perform explicit type conversion. SQL Sever will do this for you. However, when you want to convert between characters and other types of data, you do need your conversion operation. For example, suppose you want to remove all values from a Money field and add string "US Dollars" after the results. You need to use the function control (), as shown in the following example:
Select Convert (Char (8), Price) 'US DOLLARS' from Orders
Function Convert () has two variables. The first variable specifies the data type and length. The second variable specifies the field to be converted. In this example, the field price is converted to a CHAR type field that has a length of 8 characters. Field Price To convert the character string 'US Dollars' after it is connected to it. When you add a string to the Bit type, a DateTime type, an INT, or a Numeric type, you need to perform the same conversion operation. For example, the following statement adds a string 'the vote is' in the query result of a SELECT statement, which returns a value of a bit type:
SELECT 'The Vote Is' Convert (Char (1), Vote) from Opinion
Below is the result of this statement:
The vote IS 1
The vote IS 1
THE VOTE IS 0
(3 row (s) affected)
If you don't make an explicit conversion, you will receive the following error message:
Implicit conversion from dattype 'varchar' to 'bit' is not allows.
Use the control function to run this query.
Operation string data
SQL Sever has many functions and expressions that allow you to make interesting operations, including a wide variety of pattern matching and character transformation. In this section, you will learn how to use the most important character functions and expressions.
Match wildcard
Suppose you want to build an Internet directory similar to the Yahoo functionality. You can create a table to save a range of site names, unified resource locator (URLs), description, and categories, and allow accessers to retrieve these contents by entering keywords in HTML Form.
If an visitor wants to get a list of sites containing the keyword trading card from this directory. To take a list of the right site, you may try to use such a query:
SELECT Site_name from site_directory where site_desc = 'TRADING CARD'
This query can work. However, it can only return sites where there are only TRADING CARD strings in their description. For example, a site description as We Have The Greatest Collection of Trading Cards In The World! Is not returned.
To match a string with a part of another string, you need to use wildcard. You use wildcards and keyword Like to implement mode matching. The following statement has written the above query using wildcard and keyword Like to return all the correct sites:
Select Site_name from site_directory
WHERE Site_Desc Like '% Trading Cars%'
In this example, all sites containing an expression trading card are returned. The site described as We Have The Greatest Collection of Trading Cards in The World! Is also returned. Of course, if a site is included in the description of the I am Trading Cardboard Boxes Online, the name of the site is also returned.
Note the use of percentage in this example. The percent sign is one of the examples of wildcards. It represents 0 or more characters. By enclosing the trading card in a percent sign, all strings in which string TRADING CARD are embedded.
Now, suppose your site directory becomes too big and cannot be fully displayed in a page. You decided to divide the directory into two parts. On the first page, you want to show the site between all letters between A to M. On the second page, you want to display all the sites between N to Z to Z. To get a list of site on the first page, you can use the following SQL statement: select site_name from site_directory where site_name like '[A-M]%'
In this example, expressions [A-M] were used, and only those sites between the first letters between A to M were taken. Brand brackets ([]) are used to match a single character in the specified range. To get the site displayed in the second page, use this statement:
Select Site_name from site_directory
Where site_name like '[n-z]%'
In this example, the expression in parentheses represents any single character in N to Z.
Suppose your site directory becomes bigger, you need to divide the directory into more pages. If you want to show sitting on A, B or C, you can use the following query:
SELECT Site_name from site_directory where site_name like '[abc]%'
In this example, the expression in parentheses no longer specifies a range, but some characters are given. Any site that its name starts in any of these characters will be returned.
By including a range and some specified characters in the expression in parentheses, you can combine these two methods. For example, with the query below, you can take out those initiators between c to f, or at the beginning of the letter Y:
SELECT Site_name from site_directory where site_name like '[c-fy]%'
In this example, the name of the name of Collegescape and Yahoo will be selected, and the name of the name MAGICW3 will not be selected.
You can also use the hair character (^) to exclude a specific character. For example, you have to get the sites that are not starting with Y, you can use the following query:
SELECT Site_name from site_directory where site_name like '[^ y]%'
Dramatics can be used for a given character or character range.
Finally, you can match any single characters by using the underline character (_). For example, the following query returns the second character of each of its names for any letters:
SELECT Site_name from site_directory where site_name like 'm_crosoft'
This example returns both the site named Microsoft, and returns a site named Macrosoft. However, the name of the Moocrosoft is not returned. Unlike the wildcard '%', the underscore represents only a single character.
note:
If you want to match the 100-segment or underline character itself, you need to enclose them in square brackets. If you want to match the hockey (-), you should specify it as the first character in square brackets. If you want to match square brackets, you should also include them in square brackets. For example, the following statement returns a site containing a percent sign in all its description:
SELECT Site_name from site_directory where site_desc like '% [%]%'
Match pronunciation
Microsoft SQL has two functions that allow you to match strings according to pronunciation. The function SOUNDEX () assigns a sound coding code for a string, and the function DIFCERENCE () compares two strings according to the pronunciation. When you don't know the exact spelling of a name, how much knows how it pronunses, uses these two functions will help you take this record. For example, if you create an Internet directory, you may want to add an option to search for the site according to the pronunciation of the site name, not by the spelling of the name. Consider the following statement:
Select Site_name from site_directory
Where Difference (Site_name, 'Microsoft'> 3
This statement uses a function defference () to get the pronunciation of its name and a very similar site very similar to Microsoft. Function DIFFERENCE () Returns a number between 0 and 4. If the function returns 4, it indicates that the pronunciation is very similar; if the function returns 0, the pronunciation of the two strings is large.
For example, the above statement will return to the site name Microsoft and Macrosoft. The pronunciation of these two names is similar to Microsoft. If you change the upper body than 3 in the previous statement to greater than 2, the site named Zicrosoft and Megasoft will also be returned. Finally, if you only need the difference of differences to be greater than 1, the site named PicoSoft and MiniSoft will also be matched.
To understand how the function Difference () works, you can use the function snoundex () to return the soundtale code used by the function DIFCERENCE (). Here is an example:
Select Site_name 'Site Name', Soundex (Site_name) 'Sounds Like'
This statement selects all the data of the field site_name and its audio code. Here is the result of this query:
Site Name Sounds Like
.................................................................
Yahoo Y000
Mahoo M000
Microsoft M262
Macrosoft M262
MiniSoft M521
Microshoft M262
Zicrosoft Z262
Zaposoft Z121
Millisoft M421
NanoSoft N521
Megasoft M221
PicoSoft P221
(12 row (s) affected)
If you take a closer look, you will notice that the first letter of the audio code is the same as the first letter of the field value. For example, Yahoo and Mahoo's audio code only differ in the first letter. You can also find that Microsoft and Macrosoft's phonetic code is identical.
The function Difference () compares the first letter of the two strings and all consonant letters. This function ignores any vowel letters (including Y) unless a vowel letter is the first letter of a string.
Unfortunately, there is a lack with SoundEx () and Difference (). The WHERE clause contains the query that the two functions is not good. Therefore, you should be careful to use these two functions. Delete space
There are two functions, TTRIM (), and Ltrim (), which can be used to cut off spaces from the string. Function Ltrim () removes all spaces in front of the string; function RTRIM () removes all spaces of a string tail. Here is an example of any use function RTRIM ():
SELECT RTRIM (Site_name) from site_directory
In this example, if the name of the name of any site has excess space, excess space will be deleted from the query results.
You can nested these two functions, delete the space before and after a string:
SELECT LTRIM (RTRIM (Site_name) from site_directory
You will find that both functions are useful when cutting over the extra spaces from the CHAR field. Remember, if you save a string in the Char field, the string will be added extra spaces to match the length of the field. With these two functions, you can remove useless spaces to solve this problem.
Operation date and time
Date and time functions are very useful for establishing a site. The owner of the site is often interested in the data in a table. With the date and time function, you can track a change in milliseconds.
Return the current date and time
With the function getdate (), you can get the current date and time. For example, statement select getdate () returns the following results:
................................
NOV 30 1997 3:29 AM
(1 row (s) affected)
Obviously, if you use this function in the future, the date you get will be late than this time, or early.
Function getdate () can be used as a default value of a Datedime () field. This is useful for saving the time when the record is inserted. For example, assume that there is a table to save the activity log on your site. Whenever there is a visit to your site, add a new record in the table, write down the visitor's name, activity, and access time. To create a table, the record contains the current date and time, you can add a DateTime field, specify the return value of the default value getDate (), like this:
Create Table Site_log
Username varchar (40),
UserActivity varchar (100),
EntryDate DateTime Default getdate ())
Conversion date and time
You may have noticed that in the example of the previous section, the return value of the function getdate () is displayed only in seconds. In fact, the internal time of SQL Sever can be accurate to milliseconds (exactly, it can be accurate to 3.33 ms).
To get the date and time of different formats, you need to use the function control (). For example, when the statement below is executed, the time displayed will include milliseconds:
Select Convert (VARCHAR (30), Getdate (), 9)
Note The use of numbers 9 in the example. This figure indicates which date and time format that uses time when displaying the date and time. When this statement is executed, the following date and time will be displayed: ......................................
NOV 30 1997 3: 29: 55: 170AM
(1 row (s) affected)
In the function control (), you can use many different styles of date and time format. Table 11.1 shows all formats.
Table 11.1 Types of Date and Time
Type value standard output
0 Default Mon DD YYYY HH: MIAM
1 USA MM / DD / YY
2 ANSI YY.MM.DD
3 BRITISH / FRENCH DD / MM / YY
4 german dd.mm.yy
5 Italian DD-MM-YY
6 - DD MON YY
7 - MON DD, YY
8 - HH: MI: SS
9 9 Default Milliseconds - Mon DD YYYY
HH: MI: SS: Mmmam (OR)
10 USA MM-DD-YY
11 japan yy / mm / dd
12 ISO YYMMDD
13 Europe Default Milliseconds - DD MON YYYY
HH: MI: SS: MMM (24h)
14 - HH: MI: SS: MMM (24h)
Types 0, 9, and 13 always return four years. For other types, to display the century, add the style value to 100. Types 13 and 14 returns a 24-hour clock time. The moon returned by type 0, 7, and 13 is represented by three characters (represents November by NOV).
For each format listed in Table 11.1, you can add a type value to 100 to display an angelic year (for example, 2000). For example, you should display the date according to Japanese standard, including the century, you should use the following statement:
Select Convert (VARCHAR (30), Getdate (), 111)
In this example, the function convert () converts the date format and is displayed as
1997/11/30
Extract date and time
In many cases, you may only want to get a part of the date and time, not a complete date and time. For example, suppose you want to list the months that each site is queried in your site directory. At this time, you don't want the full date and time to mess up the web. In order to extract a specific part of the date, you can use the function datepart (), like this: select site_name 'site name',
DatePart (mm, site_entrydate) 'Month Posted' from site_directory
The parameter of the function datepart () is two variables. The first variable specifies which part to extract the date; the second variable is actual data. In this example, the function datepart () extracts the month because the MM represents the month. Here is the output of this SELECT statement:
Site name Month Posted
....................................................................
Yahoo 2
Microsoft 5
Magicw3 5
(3 row (s) affected)
The Month Posted column shows the month that each site is queried. The return value of the function datepart () is an integer. You can use this function to extract the different parts of the date, as shown in Table 11.2.
Table 11.2 Date of the Date and Its Shop
Date partial shorthand value
Year yy 1753--9999
Quarter QQ 1--4
Month mm 1--12
Day of year dy 1--366
Day DD 1--31
Week WK 1--53
Weekday DW 1--7 (Sunday - Saturday)
Hour hh 0--23
Minute mi 0--59
Second ss 0--59
Milisecond MS 0--999
Use function datepart () returns an integer when you need to perform a date and time comparison. However, the query results (2, 5) in the above example are not very easy to read. To get partial dates and time in more readable format, you can use the function datename (), as shown in the following example:
SELECT Site_name 'Site Name'
Datename (mm, site_entrydate) 'Month Posted'
From site_directory
Functions datename () and function datepart () receive the same parameters. However, its return value is a string instead of an integer. Here is the result of the above example to use DateName ():
Site Name Month Postec ................................................................
Yahoo February
Microsoft June
Magicw3 June
(3 row (s) affected)
You can also use the function datenae () to draw a day in a week. The following example simultaneously extracts one day and date in a week:
Select site_name 'site name',
Datename (DW, Site_Entrydate) '-' Datename (mm, site_entrydate)
'Day and Month Posted' Form Site_Directory
When this example is executed, the following result is returned:
Site Name Day and Month Posted
...........................................................................
Yahoo Friday - February
Microsoft Tuesday - June
MAGICW3 MONDAY - JUNE
(3 row (s) affected)
Return date and time range
When you analyze the data in the table, you might want to take out the data of a certain time. You may have a certain day in a certain day.
2000
December
25th - visitor is interested in the event of your site. To take out this type of data, you may try to use this.
SELECT statement:
Select * from Weblog where entrydate = "12/25 /
20000
"
Do not do this. This SELECT statement does not return the correct record - it will return only the date and time of 12/25/2000 12: 00: 00: 000AM. In other words, only records just entered at midnight zero are returned.
note:
In this section, assuming field entrydate is a DateTime type, not a SmallDateTime type. The discussion of this section is also applicable to the SmallDateTime field, but the SmallDateTime field can only be accurate to second.
The problem is that SQL Sever will replace part and time with a complete date and time. For example, when you enter a date, but not entering time, SQL Sever will add the default time "12: 00: 00: 000AM". When you enter a time, but if you don't enter a date, SQL Sever will add the default date "JAN 1
1900
"
.
To return to the correct record, you need to apply the date and time range. There is more than one way to do this. For example, the following SELECT statement will be able to return the correct record:
Select * from Weblog
WHERE EntryDate> = "
12/25/2000
"
And entrydate <
"
12/26 /
2000
"
This statement can complete the task because it is selected for the date and time in the table, which is greater than or equal to 12/25/2000 12: 00: 000AM and less than 12/26/2000 12: 00: 00: 000AM record. In other words, it will correctly return to each record entered by Christmas 2000 Christmas.
Another way is that you can use LIKE to return the correct record. By including wildcard "%" in the date expression, you can match all times of a specific date. Here is an example: select * from Weblog Where entrydate Like 'DEC 25 2000%
This statement can match the correct record. Because the wildcard "%" represents any time.
With these two functions of these two matches, you can choose a month, one day, one year, an hour, a minute, one second, or even entered within a millisecond. However, if you use Like to match the second or milliseconds, you first need to use the function convert () to convert the date and time to a more accurate format (see the "Conversion Date and Time" section in front).
Compare date and time
Finally, there are two dates and time functions to remove records based on date and time. Use the function dateadd () and dateDiff (), you can compare the day before. For example, how many hours have been entered in the following SELECT statement to display each record in the table:
SELECT Entrydate 'Time Entered'
Datediff (hh, entrydate, getdate ()) 'Hours ago' from Weblog
If the current time is from 6:15 on November 30, 2000, it will return the following results:
Time entered Hours Ago
....................................................
Dec 30 2000 4:09 PM 2
DEC 30 2000 4:13 PM 2
DEC 1 2000 4:09 PM 698
(3 row (s) affected)
The parameter of the function Dadediff () is three variables. The first variable specifies some part of the date. In this example, the date is compared to the date in hours, (To understand the details of the date, please refer to Table 11.2) at the date
2000
November
1st and
2000
November
There is between the specified time of the 30th
689 hours. The other two parameters are time to compare. In order to return a positive number, the earlier time should be given first.
The function dateAdd () adds two dates. This function is useful when you need to calculate the data of the deadline. For example, assume that visitors must first register to use your site. After registration, they can use your site for a month. To determine when their free time will be used, you can use the following SELECT statement:
Select Username 'User Name',
Dateadd (mm, 1, firstvisit_date) 'registration expires'
From seriousration_table
The parameter of the function dateAdd () has three variables. The first variable represents a certain part of the date (see Table 11.2), this example uses the MM of the menu. The second variable specifies the interval of time - in this example is one month. The last variable is a date, in this example, the date is taken from the datetime field firstvisit_date. Assume that the current date is June 30, 2000, this statement will return the following:
User Name Registration Expires
...................................................................................
Bill Gates Jul 30 2000 4:09 Pmpresident CLINTON JUL 30 2000 4:13 PM
William Shakespeare Jul 1 2000 4:09 PM
(3 row (s) affected)
note:
In contrast to your expected, use the function dateadd () plus a date with a month, it does not add 30 days. This function simply adds the month value to 1. This means that people registered in November will get more than 2 days or 3 days than those registered in February. To avoid this problem, you can use the function dateAdd () to add the number of days directly instead of the month.
send email
You can send simple E_mail information with SQL Sever. To do this, you need to install a mail server in your system, such as Microsoft Exchange Sever (see Chapter 4 "Exchange Active Sever, Index Sever, and Netshow"). You also need to configure SQL SEVER to identify mail servers.
To let SQL Sever identify the mail server, start the Transaction Manager and select Sever | SQL Mail | Configue from the menu, a dialog shown in Figure 11.3 will appear. Enter your username and password you registered in the mail server, and then click OK.
note:
If you use Microsoft Exchange Sever, the process of configuring SQL Sever will be greatly different. You need to run Microsoft SQL SEVER and Exchange Sever under the same (domain) user account. You also need to install Exchange Cliect on the machine installed in SQL Sever and create a configuration file to this account. Once you do, you can enter the name of the configuration file in the SQL Mail Configuration dialog.
Figure 11.3
Before sending an email, you have to start SQL Mail first. Select Sever | SQL Mail | Start from the menu. If your mail server is configured correctly, and you enter the correct username and password, SQL Mail will start successfully.
note:
You can configure SQL SEVER to automatically start email services. To do this, select the Auto Start Mail Client in the Set Sever Optons dialog (select Sever | SQL Sever | Configure from the menu).
To send an email, you can use the extended stored procedure named XP_sendmail. Here has an example of how to use this process:
Master..xp_sendmail "President@whitehouse.gov", "Hello Mr. PRESIDENT"
This process call sends a simple Email information to the E_MAIL address President@whitehouse.gov: "Hello Mr. PResident". You can replace the corresponding content in the above example with any other email address and information, however, the information you sent cannot exceed 255 characters.
When you want to know the status of your site database, stored procedure XP_sendmail is useful. For example, you can send information to a page manager. If your site has, you can know it right away. The next chapter will tell more about the content of the stored procedure.
to sum up
This chapter has deepened your SQL knowledge. You have learned how to build an index, making your query speed faster. You also learned how to insert, delete and update data in a table, how to use the collection function to get statistics for data in a table. Finally, you have learned a lot of valuable expressions, functions, and processes to operate strings, date, and time and email. The next chapter will further deepen your master of Microsoft SQL Sever. You will learn how to use SQL for programming, how to create stored procedures, triggers, and execution plans. What more people are more excited is that you will learn to let SQL Sever automatically create a simple way to create a web page.
