Operating data: SQL N SQL foundation n intermediate SQL N advanced SQL ===================================== ==================================================================================================================================================================================================================== # Take the data ¨ ¨ 创 Create a new table ¨ 字 Field Properties ¨ ¨ ¨ 向 ¨ Delete and modification Table In order to establish an interactive site, you need to use the database to store information from the visitors. 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 Using SQL language. So, master SQL is very important to program the ASP programming. Note: You can read "SQL" as "sequel", or read S-Q-L in a single letter. Two pronunciations It is correct, each pronunciation has a lot of supporters. In this book, "SQL" is considered "SQL" to read "Sequel". By learning this chapter, you will understand how to use SQL to realize database query, you will learn how to learn how Use this query to remove information from the data table, in the end, you will learn how to design and build your own database. Note: Introduce SQL through the following chapter, you will have enough understanding of SQL, so you can effectively use ACTIVE SEVER PAGES. However, SQL is a complex language. This book cannot include all of its details. To fully master SQL language, you need to learn to use SQL in Microsoft SQL Sever. You can go to a nearby bookstore to buy a book Microsoft SQL Sever 6.5. 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 the operational database. (In fact, About SQL language A special ANSI standard: Note: Don't try to use Microsoft Access on your site to replace Microsoft SQL Sever.SQL Sever to serve many users at the same time, if you want your site to have a high access rate, MS Access is unable to compete 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 database is stored in the table. A table is Rows and columns.
For example, the following simple table includes Name and E-mail address: Name Email Address .............................. .................................. Bill Gates Billg@microsoft.com President Clinton President@whitehouse.com Stephen Walther Swalther @ 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 is called a record together. Whenever you Add new data to the table, you have added a new record. A data sheet can have dozens of records, or there are several thousand or even billion records. Although you may never need to store billion Email addresses, I know that you can do this, maybe you have this need for one day. Your database is likely to contain dozens of tables, all stores in your database are stored in these tables. When you Considering how to store information in the database, you should consider how to store them in the table. SQL's second feature is somewhat difficult to master. This language is designed to not allow you to take a record in a specific order. Because this will reduce the efficiency of SQL Sever to take records. Use SQL, you can only read records according to query conditions. When you consider how to remove records from the table, you will naturally read them according to the recorded position. For example, perhaps You will try to scan by a loop, to select a specific record. When using SQL, you must 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 record is feasible, but the efficiency is not high. Use SQL, As long as you say, "Choose all the names of the name equal to BILL GATES", SQL will choose all the eligible records. SQL will determine the best way to implement the query. Building you want to take the top ten records in the table . Using the traditional programming language, you can make a loop, take out the previous ten records, end the loop after the previous record. But use standard SQL queries, this is impossible to implement. From SQL's perspective, there is no existence in a table Ten records this concept. At the beginning, when you know that you can't achieve some of the features you feel able to implement, you will be frustrated. You may have a designer for SQL with the head and even want to write vicious letters. But later you will know At this feature of SQL, this feature 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 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 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 President, you can use the following query statement: select email from email_table where name = "Bill Gates" or name = "PRESIDENT CLINTON" in this case in the previous one It's complicated. 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 the 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 results should be displayed in the display window: phone ................. 801 826_0752 801 826_0752 (2 row (s) an affected) Figure 10.3 The SELECT statement you execute From Table Authors to the author of the author of the Ringer number. 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, remove all phone numbers in Table Authors (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 plurality of fields in a SELECT statement, such as: select au_fname, au_lname, the Phone from Authors executes, will take out all the values of the three columns. Below is an example of this result of this query (in order to save paper, only part of the query result is displayed, the rest of the record is replaced by omna number): 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 This SELECT statement is executed, the value of all fields in the table is taken out. You will find that you will frequently use the asterisk in the SQL query. Tip: 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 to now, you only try to remove data from a table with a SQL query. You can also use a SELECT statement to remove data from multiple tables at the same time, just list the table names to remove data from the SELECT statement: SELECT AU_LNAME, TIM AUTHHORS, TITLES This SELECT statement is executed The data is removed from the 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 results: SELECT AU_NAME, TIM AUTHORS, TITLES, TIM = = TitHors.au_id = title_id = title = TitleR.Title_ID When this SELECT statement is executed, each Persons will match the correct book name. 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 books and their publishers, you can use the following statement: select title, pub_name from titles, public = publics where titles.pub_id = publicishers.pub_id is of course, if a book is jointly published by two publishers So 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. The operation field is usually, 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 a field name AU_LNAME: SELECT AU_LNAME "Author Last Name" from autom, when this SELECT statement is executed, the value from the field AU_LNAME is connected to "Author Last Name" . The result of the query may be like this: author last name .......................................................................................................................................................................... Affected) Note 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 data is removed from the table titles, the original price shows In the title Original Price, the doubled price is shown below the title new price. The result may be like this: Original Price New price .................................................................. 19.99 19.99 39.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 ( ), subtract (-), 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 the price and sales. The result of this SELECT statement will be like this: Total Revenue ........................................................................ 81, 859, 05 46, 318, 205, 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 Field au_fname and field au_lname are pasted together, separated by a comma in the middle, and specify the title of the query result as Author Name. The result of this statement will be like this: author name .............................................................................. Johnson White Marjorie Green Cherel 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 have emphasized in this chapter, and the SQL table has no internal 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 the field au_lname and field au_fname at the same time, you need to sort two fields: select au_lname, au_fname from authors order by au_lname, AU_FNAME This query first sorts the results Press the AU_LNAME field, then press Field AU_FNAME Sort. 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 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. Keyword DESC puts 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 names of all names Ringer in Table Authors. Author record. 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 removes the price of all books from the table, show the result, low price Displayed, after the high price is displayed. WARNING: Do not sort the query results when not needed, because the server completes this job. This means that the SELECT statement with the Order By clause performs a long time than the general SELECT statement. Retrieving a table with each other is a table that 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 repetitive value, you may want each value to be only selected once, you can use the keyword distinct to do this: SELCET DISTINCT AU_LNAME FROM AUTHORS WHERE AU_LNAME = "Ringer" When this SELECT statement is executed, only Returns a record. 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. WARNING: Like the Order By clause, the forced server returns to each other will increase the operation overhead. Fuqiao has to spend some time to complete this work. Therefore, do not use the keyword distinct when you must be. Creating a new table says, 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 execution query button, execute this statement: Create Table Guestbook (visitor varcha (40), comments text, entrydate datetime) If everything is normal, you will look at the results window. To the following text (if 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 types 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. Characteristic 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: Create Table Guestbook (visitor varchar (40), comments text, entrydate datetime) in this example, the data type of 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, field Visitor is used to store fixed length strings for 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-type data character type 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. WARNING: Once you enter any data (or even null) to the text type field, there will be 2K spaces to be automatically assigned to the data. You cannot recover this part of the storage space unless you delete this record. Numerical 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 type of data: create Table Numeric_Data (Bignumber Numeric (28, 0)) When this statement is executed, a table containing two fields 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, Price Money, this table can be used to store discounts and ordinary prices. The data type of the field Price is Money, the data type of field discount_price is SmallMoney. Storage logic values 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 is an example of how to use this field: CREATE TABLE OPINION (Visitor Varchar (40), Good bit) This table can be used to store information on your outlets to make public opinions. 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. Storage Date and Time When you create an outlet, you may need to record the number of visitors in a period of time. In order to be able 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) This table can be used to record visitors to enter and leave your outlet And date. A datetime type field can store the date range from January 1, 1753 to last milliseconds, 9999. If you don't need to override such a wide range of dates and times, you can use SmallDateTime type data. It is also used as DateTime type data, but it can be indicated by the date and time range than the datetime type data, and it is not as accurate as DateTime type data. A SmallDateTime type field can store the date from January 1, 1900 to June 6, 2079, which can only be accurate to second. The DateTime field does not contain actual data before you enter the date and time, and it is important to understand this. 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 values can be accepted for null values. 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 type data cannot be null. One type of field must be 0 or 1. Sometimes you need to disable a field to use null value. For example, it is assumed that there is a table stores the credit card number and the credit card valid date, you will not want someone to enter a credit card number but do 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 the back of the field definition with expressions Not null. By containing expression not null, you can disable Anyone is only inserted into data in a field without entering the data of another field. You will find that this banned null's ability is very useful during your network. If you specify a field You can't accept null values, then you will have an error warning when you try to enter an null value. These error warnings can provide valuable clues for program debugging. The default value is a table that stores address information, this table is included Street, city, state, postal code and country. If you expect the address of the address in the United States, you can use this value as the default value of the Country field. In order to specify the default value when you create a table, you can use the expression DEFAULT. Please see the following use of the default value when creating a table: Create Table address (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. Note that the single quotes are used, the quotation marks indicate that this is a characteristic data. To specify the default value for the non-characters Do not extend this value in quotation: Create Table ORDERS (Price Money Default 50, EntryDate DEFAULT 50, EntryDate DateTime DEFAULT GETDATE ()) In this CREATE TABLE statement, each field specifies a default. Note DateTime field ENT The default value specified by RyDate is the return value of the function getdate (), which returns the current date and time. Identifying a field each table can have only one identification field. A identification field is a special field of each record in the unique identity table. For example, the table jobs in the database PUBS contains 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 type or int type field to the identity field, here there is an example: CREATE TABLE VITETORID (THEID NUBERIC (18) Identity, Name VARCHAR (40)) The table created by this statement contains an identification called theid Field. 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. Tip: When establishing a marked field, pay attention to use a large enough 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. Creating a new table using the 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 yet, return to Chapter 3 Creating 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 at least enter a 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 a data to the table Next chapter to discuss how to insert data into one table using SQL. 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 select 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 to the last record will be saved. If you need, you can use Microsoft Query to enter hundreds of records in the table. Deleting and modifying tables You should carefully design them before establishing a table, because you will be greatly restricted 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 Warning: 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 the 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. Below is an example of how to use this statement: ALTER TABLE MyTable Add MyneWColumn Int Null adds a new field MyNewColumn in 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. Summary 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 process Chapter 10, "SQL Basis", to you preliminate 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 assume that 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. 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 assume that you have found the page number where a sentence is found 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. The index properties These two types of indexes have two important properties: You can use any types in both simultaneously establish an index (composite index); 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 establishes an index named 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 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 an index type, 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 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 repetitive record in the table, an error will occur when you try to establish an index with this statement.
However, there are tables with repeated records to establish an index; you only need to use keyword allow_dup_row to tell SQL Sever: 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 COUSTERED INDEX MyClumn_cindex on myTable (MyColumn) This is the index creation statement 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 has established a single index for both fields. In a composite index, you can index up to 16 fields. Establishing a Social Manager with a Transaction Manager Establishing an Index is much easier than using SQL statements. 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 build indexes 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 many 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 can be established. In Chapter 10, you learned how to use the SQL SELECT statement 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 Table Add a new record, you want to use the SQL INSERT statement.
Here 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 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 full sentence of the INSERT statement is as follows: INSERT [INTO] {Table_name | View_name}] {Default Values | VALUES_LIST | SELECT_STATEMENT} If a table has multiple fields, by separating the field name and field value, you can Insert data into all fields. Assume that the table MyTable has three field first_column, second_column, and third_column. The following INSERT statement adds a complete record of three fields: INSERT MyTable (first_column, second_column, third_column) Values ('Some Data', 'Some More Data', 'Yet More Data') Note: You can use INSERT statement inserts data into the text field. 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, 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 the 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 in a table with identity field, you can use SQL variable @@ identity to access the value of the new recorded identity field. Consider the following SQL statement: Insert MyTable (First_Column) Values ('Some Value') Insert Anothertable (Another_first, Another_Sec ") Values (@@ iDentity, 'some value') If the table MyTable has a identification field, the value of this field is Insert 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, this DELETE statement only deletes the value of field first_column is equal to 'delete me' record: delete myTable where first_column = 'deltet me' delete statement The full sentence 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 removes only the value of the first_column field is the value of 'goodbye' or the second_column field: delete myTable where first_column = 'goodby' or second_column = 'so long' If you don't give The DELETE statement provides a WHERE clause, and 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 do you need to replace the DELETE statement with a TRUNCATE TABLE 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 'Update Me!'. For all selected records, the value of field first_column is set to 'updated!'. Here is the complete syntax of the UPDATE statement: UPDATE {table_name | view_name} SET [{table_name | view_name}] {column_list | variable_list | variable_and_column_list} [, {column_list2 | variable_list2 | variable_and_column_list2} ... [, {column_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 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 while updating first_column, second_column, and third_column these three fields: UPDATE mytable SET first_column = Second_column = 'Updated!' Third_column = 'Updated!' WHERE first_column = 'Update Me1' skills 'Updated!': SQL Ignore excess space in the statement. You can write the SQL statement into any format you easier to read.
Creating a record 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 achieve this, you need to INSERT statement with a SELECT statement together, like this: INSERT mytable (first_column, second_column) SELECT another_first, another_second FROM anothertable WHERE another_first = 'Copy Me!' This statement from anothertable record copy to mytable. Only the value of the field another_first in the table anothertable 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 named newTable, which contains all the data of 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 modification. 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. The collection function is now, 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 this 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 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 example calculation table AUTHORS Number (Last Name). 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 author 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 returned to 'ringer' The number of authors. 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. Below is an example of how to do this: select count (*) from authors Note function count () does not specify any field. 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 statement: select count (vote) from opinion_table where vote = 1 Calculate the average of 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 This SELECT statement return value 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. Calculating a field value and assuming your site is used to sell cards, it has been running for two months, is the time when the calculation makes it. Assume that there is a table named ORDERS to record all visitors' order information.
To calculate the sum of all ordered quantities, you can use the function sum (): select sum (purchase_amount) from ORDERS function SUM () return value 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. Returns the maximum or minimum value once again assumes that you have a table to save 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 to your site's highest evaluation, 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 visitor's minimum evaluation of your site, you can use the function min (), as shown in the following example: SELECT MIN (VOTE) from Opinion function min () Returns all values of a field Minimum. If the field is empty, the function min () returns a null value. Other common SQL expressions, functions, and process 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 to assume that you have a table to save your spouse to make a public opinion survey. 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 implement your requirements. You can get the same result using the SELECT statement: 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, no reason not to use it. However, there is a way equivalent. Use the following SELECT to get the same result: SELECT UserName From Opinion Where Vote In (1, 10) Note 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 ('Bill Gates', 'PRESIDENT CLINTON') Finally, you can use Expression Not while using BetWeen or IN.
For example, to take out the names of those who are not in 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 Record, 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 Make your query closer to the natural language, these two expressions are helpful. The conversion data SQL Sever is strong enough, and most of the values can be converted from one 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 a function convert (), 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, the SELECT statement returns a value of a bit type: SELECT 'The vote is' Convert (1), vote) from FROM Opinion The following is the result of this statement example: 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 DataType 'Varchar' to 'Bit' Is Not ALOWEC. Use The Convert Function To Run This Query. Actions SQL Sever has many functions and expressions that make you fun for strings, including a variety of Mode matching and character conversion. In this section, you will learn how to use the most important character functions and expressions. Matching wildcard assumes that 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 remove the correct site list, you may try to use this 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 rewrites the above query using wildcard and keyword Like to return the name of all correct sites: select site_name from site_directory where site_desc like '% TRADING CARK%' In this example, all its description contains expressions Trading. Card's site is 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 '[am]%' uses expressions [AM] in this example, only those first letters are in a The site between M. 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 '[NZ]%' 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 the site starting with 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 specified A range, but give some characters. 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 following query, you can take out between those letters between c to f, or start with the letter Y: select site_name from site_directory where site_name like '[c-fy]%' In this example, the name Sites for collegescape and yahoo are 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 site that does not start with Y, you can use the following query: select site_name from site_directory where site_name like '[^ y]%' uses a dazzle to 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 letter: select site_name from site_directory where site_name like 'm_crosoft' This example returns to Microsoft sites 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 underscore character itself, you need to include 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: select site_name from site_directory where site_desc like '% [%]%' Match pronunciation Microsoft SQL has two allows you to match the string 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 the function defference () to get the pronunciation of its name and the very similar site very similar to Microsoft. Function Difference () Returns a 0 to 4 between Number. If the function returns 4, it means 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 the site name Microsoft and Macrosoft. These two names Pronunciation and Microsoft are very similar. If you change the greater 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 rating greater than 1, The site for PicoSoft and MiniSoft will also be matched. To learn how to learn DIFCERENCE () work, you can use the function SoundEx () to return the soundtale code used by the function Difference (). An example: select site_name ' Site name ', SoundEx (Site_name)' Sounds like 'This statement selection all the data of the field site_name and its audio code.
Here is the result of this inquiry: 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 sound 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. Deleting spaces There are two functions, TTRIM (), and Ltrim (), which can be used to cut off space from the string. Function Ltrim () removes all spaces in front of the string; function RTRIM () removes all spaces of a string tail. Here, there is any example of using a function RTRIM (): select RTRIM (Site_Name) from site_directory In this example, if the name of the name of any site has extra space, excess space will be deleted from the query results. You can use these two functions to use these two functions, and delete the space before and after a string: Select Ltrim (rtRim (slter (site_name) from site_directory you will find that these two The function is very useful. Remember, if you save a string in the CHAR type field, the string will be added extra spaces to match the length of the field. Use these two functions, you can remove the useless spaces, Thereby solving this problem. The date and time date and time function are very useful for establishing a site. The owner of the site often is interested in the data in a table. With the date and time function, you can at millisecond Track a change. Returns the current date and time through 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, you get the date later than this time, or the infarction. Function getDate () can be used as a Datedime () type The default value of the field is useful for saving the time when inserting records. For example, suppose having a table to save the activity log on your site. Whenever there is a visit to your site, Add a new record, write down the number of visitor's name, activity, and access to access.
To create a table, the record contains the current date and time, you can add a DateTime type field, specify the return value of its 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 displayed time will include milliseconds: SELECT Convert (VARCHAR (30), GetDate (), 9) Number 9. This figure indicates which date and time format that uses time when displaying the date and time. When this statement is executed, the date and time will be displayed: ...................................... Nov 30 1997 3: 29: 55: 170am (1 row (s) affected) in the function Convert () You can use many different styles of date and time format. Table 11.1 shows all formats. Table 11.1 Date and Time Type 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) Type 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. Type 0, 7, and 13 The month returned with three characters (represents November by NOV). For each format listed in Table 11.1, you can add a type value plus 100 to show an annual year (for example Years will be shown in 2000). For example, you should use the date of the Japanese standard, including the century, you should use the following statement: SELECT Convert (VARCHAR (30), getDate (), 111) In this example, the function control converts the date format, displayed as 1997/11/30 Extracting date and time In many cases, you may only want to get 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.
To extract the 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 function DatePart () parameters are 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 Section of the Date and Its Shredded Date Some Story of the Date of 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 functions when you need to perform date and time comparison DATEPART () returns an integer is useful. 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 function DateName () and functions DatePart () receives the same parameters. However, its return value is a string instead of an integer. Below is the result of the above example: Site Name Month Postec .......................................................................................... Yahoo February Microsoft June Magicw3 June 3 Row (s) Affected You can also use the function datenae () to extract one 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 this When the example is executed, it will return the following results: Site name day and month posted ....................................................................... Yahoo Friday - February Microsoft Tuesday - February Microsoft Tuesday - February Microsoft Tuesday - February Microsoft Tuesday - February Microsoft Tuesday - February Microsoft Tuesday - February Microsoft Tuesday - February Microsoft Tuesday - June Magicw3 Monday - June (3 Row (s) Affected) Returns the date and time range When you analyze the data in the table, you may want to take out the data of a certain time.
You may be interested in a certain day - for example, December 25, 2000 - visitor is interested in your site. To take out this type of data, you may try to use this SELECT statement: select * from Weblog where entdate = "12/25/20000" Don't 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. 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 do not 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 The date and time in the table are greater than or equal to 12/25/2000 12: 00: 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). Comparison 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 2000 11 At 6:15 in the 30th, I 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) function Dadediff () parameters are three variables. The first variable specifies some part of the date. In this example, it is compared to the date on hours (to understand the details of each part of the date, please refer to Table 11.2) 689 between the specified time of November 1, 2000 and November 30, 2000 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 registration_table function dateadd () parameter 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. Suppose the current date is June 30, 2000, this statement will return as follows: user name registration Expires ........................ ....................................................... Bill Gates Jul 30 2000 4:09 PM President Clinton Jul 30 2000 4:13 PM William Shakespeare Jul 1 2000 4:09 PM (3 row ) Affected) Note: In contrast to your expectation, use the function dateadd () plus a date for 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 Mail 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.
