SQL Data Manipulation Basics (primary) 2

Data uses isql to execute a select query

When you install SQL Sever, you also install an application called isql/w. ISQL/W allows you to perform interactive SQL queries. It is useful to test your query with ISQL/W before you include it in your ASP page.

Attention:

In the first part of the book, you learned how to install and configure Microsoft SQL Sever. If you do not have SQL Sever installed or SQL Sever cannot be run, see chapter III, "Installing and Using SQL Sever."

Select Isql_w in the SQL Sever program group on the task to start the program. When the program starts, a dialog box appears, requiring you to enter server information and login information (see Figure 10.1). In the Sever box, enter the name of your SQL Server. If the server is running on the local computer, the server name is your computer's name.
In the Login information box, enter a login account and password or choose to use trusted connections, and then click the Connect button.

Figure 10. 1

　

　

Attention:

If you configure SQL Sever to use full security or mixed security, you can use a trusted connection. If you use standard security, you will need to provide a user account and
Password. For more information, see Chapter Iii.

If everything works, a query window appears when you click the Connection button, as shown in Figure 10.2. (If there are exceptions, please refer to chapter III)

　

Figure 10.2

　

You need to select a database before executing the query. When you install SQL Sever you have created a database for yourself, and SQL Sever also has many system databases, such as MASTER,MODEL,MSDB, and tempdb.

Conveniently, SQL Sever has a special example database named pubs. The library pubs contain individual tables for use by a virtual publisher. All the example programs in the document are designed for this library. Many of the 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 can select the database. All of your queries will be executed against each of the tables in the library. Now you can execute your first query. It's so exciting!

Your first query will be for a table named Autrors that contains all the relevant data for the author who works for a virtual publisher. Click the Query window and enter the following statement:

SELECT phone from authors WHERE au_name= "Ringer"

When the input is complete, click the Execute Query button (a green triangle that looks like a VCR playback key). When you click this button, any statements that appear in the query window will be
Yes. The query window automatically becomes the result display window, and you can see the results of the query (see Figure 10.3).

The query results you see may be different from the one shown in Figure 10.3. In different versions of SQL Sever, the data in the pubs in the library is different. To SQL Sever 6.5来 said that
Two records will be found. The result display window should display the following:

Phone

...................

801 826_0752

801 826_0752

(2 row (s) affected)

Figure 10.3

　

　

The SELECT statement you execute takes out the phone number of all authors whose names are ringer from the table authors. You restrict by using special selection criteria in the WHERE clause
The results of the query system. You can also omit the selection criteria and remove all the author's phone numbers from the table. To do this, click the Query tab, return to the Queries window, and lose
Into the following SELECT statement:

SELECT Phone from Authors

After this query is executed, all phone numbers in the table authors are removed (no specific order). If the table authors contains 100 phone numbers, there will be 100
The record is taken out and if there are 1 billion phone numbers in the table, the 1 billion records will be taken out (this may take some time).

Table authrs fields include surname, first name, phone number, address, city, state, and zip code. By specifying them in the first part of the SELECT statement, you can take the
Out of any one field. You can remove more than one field at a time in a SELECT statement, such as:

SELECT au_fname, au_lname, phone from authors

After this SELECT statement is executed, all values for these three columns are taken out. Here is an example of the results of this query (to save paper, show only one of the query results
The remaining records are replaced with ellipses):

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

...

(Affected row (s))

In the SELECT statement, you need to list how many fields you want to list. Don't forget to separate the field names with commas. You can also use an asterisk (*) from a table
Remove all the fields. Here is an example of using an asterisk:

SELECT * FROM Authors

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

Skills:

You can use the asterisk to view the names of all the columns in a table. To do this, just look at the column headings for the query results after you finish executing the SELECT statement.

Manipulating multiple tables

So far, you've only tried to fetch data from a table with a SQL query. You can also use a SELECT statement to retrieve data from multiple tables at the same time, just
The FROM clause of the SELECT statement lists the name of the table from which you want to remove data:

SELECT au_lname, title from authors, titles

When this SELECT statement executes, it extracts data from both table authors and table titles. Remove all the author names from the table authors, and remove all from the table titles
's title. Execute the query in the ISQL/W program and look at the results of the query. You will find some strange surprises: the author's name does not and their
The book matches, but there are all possible combinations of the author's name and title, which may not be what you would like to see.

What's wrong? The problem is that you don't specify the relationship between the two tables. You didn't tell SQL in any way how to associate a table with a table. Because I don't know how to
With two tables associated, the server can simply return all possible combinations of records from two tables.

To choose a meaningful combination of records from two tables, you need to correlate two tables by creating a relationship between the fields in the two tables. One way to do this is to create a third
Table, which is designed to describe the relationship between the fields of the other two tables.

The table authors has a field named au_id that contains a unique identifier for each author. Table titles have a field named title_id that contains the unique label for each title
General If you can establish a relationship between the field au_id and the field title_id, you can associate the two tables. There's a database in pubs named titleauthor.
Table, which is used to complete this work. Each record in the table includes two fields that are used to associate the table titles with the table authors. The following SELECT statement uses the
These three tables to get the correct results:

SELECT Au_name,title from Authors,titles,titleauthor

WHERE authors.au_id=titleauthor.au_id

and titles.title_id=titleauthor.title_id

When this SELECT statement is executed, each author will match the correct title. Table titleauthor indicates the relationship between table authors and table titles, which is done by including
One field from each of the two tables implements this. The only purpose of the third table is to establish a relationship between the fields of the other two tables. It does not itself contain any additional numbers
According

Notice how the field name is written in this example. To distinguish the same field name au_id in table authors and table titles, precede each field name with the table name before
Suffix and a period. Fields named author.au_id belong to table authors, and fields named titleauthor.au_id belong to table titleauthor and they don't mix
Xiao

By using the third table, you can establish various types of relationships between the two-table fields. For example, an author may have written many different books, or a book may be by
Many different authors have done it together. When there is this "many-to-many" relationship between fields in two tables, you need to use a third table to indicate the relationship.

However, in many cases, the relationship between the two tables is not complex. For example, you need to specify the relationship between table titles and table publishers. Because a title cannot be associated with
Multiple publishers, you don't need a third table to indicate the relationship between the two tables. To indicate the relationship between table titles and table publishers, you just let this
Two tables have a common field on it. In the database pubs, both table titles and table publishers have a field named pub_id. If you want the title
And a list of its publishers, you can use the following statement:

SELECT Title,pub_name from Titles,publishers

WHERE titles.pub_id=publishers.pub_id

Of course, if a book is published jointly by two publishers, then you need a third table to represent the relationship.

Typically, when you have a "many-to-many" relationship between fields that first knows the two tables, you use the third table to associate the two tables. Conversely, if there are only "one-to-one" or "one-to-many" relationships between fields in two tables, you can use public fields to associate them with the door.