SQL Data Operation Basics (Elementary) 2

Execute SELECT queries using ISQL

When you install SQL Sever, you also installed an application called ISQL/w. ISQL/w allows you to perform interactive SQL queries. Before including the query in your ASP web page, it is very useful to test it with ISQL/w.

Note:

In the first part of this book, you learned how to install and configure Microsoft SQL Server. If SQL Sever is not installed or SQL Sever cannot run, please refer to Chapter 3 "Installing and Using SQL Sever".

Select Task on SQL ISQL_w in the Sever program group to start the program. When the program starts, a dialog box will first appear, asking for 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 your local computer, 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", and then click the Connect button.

Figure 10.1

　

　

Note:

If you use SQL Sever is configured to use full security or mixed security, then you can use trusted connections. If you use standard security, you will need to provide your username and
password. For more information, see Chapter 3.

If everything goes well, a query window will appear after you click the connect button, as shown in Figure 10.2. (If there is any abnormality, please refer to Chapter 3)

　

Figure 10.2

　

Before executing the query, you need to select the database. When installing SQL Sever you have created a database for yourself, SQL Sever also has many system databases, such as master, model, msdb, and tempdb.

Conveniently, SQL Sever comes with a special example database called pubs. Library pubs contains tables for use by a virtual publisher. All example programs in the document are designed for this library. Many of the examples in this book also use this database.

Select database pubs in the DB drop-down box at the top of the query window, so that you select the database. All your queries will be executed against various tables in this library. Now you can execute your first query. This is so exciting!

Your first query will be against a table called autrors, which contains data about all authors who work for a virtual publisher. Click on the query window and enter the following statement:

SELECT phone FROM authors WHERE au_name="Ringer"

After the input is completed, click the Execute Query button (a green triangle that looks like a VCR play button). After clicking this button, any statement that appears in the query window will be executed. The query window will automatically turn into a results display window, and you can see the results of the query (see Figure 10.3).

The query results you see may be different from those shown in Figure 10.3. in SQL In different versions of Sever, the data in library pubs will be different. SQL Server For 6.5,
will find two records. 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 executed retrieves the phone numbers of all authors named Ringer from the authors table. You limit the results of a query by using special selection conditions in the WHERE clause. You can also ignore the selection criteria and get all the authors' phone numbers from the table. To do this, click the Query tab, return to the query window, and enter the following SELECT statement:

SELECT Phone FROM authors

After this query is executed, all phone numbers in the authors table will be retrieved (in no particular order). If the authors table contains one hundred phone numbers, one hundred records will be fetched. If there are one billion phone numbers in the table, all one billion records will be fetched (this may take some time).

The fields of table authrs include last name, first name, phone number, address, city, state and zip code. You can retrieve any field from the table by specifying them in the first part of the SELECT statement. You can retrieve multiple fields at once in a SELECT statement, such as:

SELECT au_fname,au_lname, phone FROM authors

After this SELECT statement is executed, all the values ​​of these three columns will be retrieved. Here is an example of the results of this query (to save paper, only a part of the query results are shown, and the remaining records are replaced by 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

…

(23 row(s) affected)

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

SELECT * FROM authors

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

Tip:

You can use asterisks to see the names of all columns in a table. To do this, just look at the column headers of the query results after executing the SELECT statement.

Operate multiple tables

Up to now, you have only tried to use a SQL query to retrieve data from one table. You can also use a SELECT statement to fetch data from multiple tables at the same time. Just list the name of the table from which you want to fetch data in the FROM clause of the
SELECT statement:

SELECT au_lname ,title FROM authors, titles

When this SELECT statement is executed, data is retrieved from the authors table and titles table at the same time. Get all the author names from the authors table, and get all
book titles from the titles table. Execute this query in the ISQL/w program and take a look at the query results. You will find some strange and unexpected situations: the names of the authors do not match the books they are written by, but all possible combinations of the names of the authors and the titles of the books appear, which may not be what you expected to see. of.

What went wrong? The problem is that you didn't specify the relationship between the two tables. You don't tell SQL in any way how to relate tables to each other. Not knowing how to relate the two tables, the server simply returns all possible combinations of records taken from both tables.

To select meaningful record combinations from two tables, you need to associate the two tables by establishing a relationship between the fields in the two tables. One way to do this is to create a third
table specifically used to describe the relationship between the fields of the other two tables.

The authors table has a field named au_id, which contains the unique identification of each author. The table titles has a field called title_id, which contains a unique identifier for each book title. If you can have in field au_id and field title_id By establishing a relationship between them, you can relate the two tables. There is a
table named titleauthor in the database pubs, which is used to complete this work. Each record in the table contains two fields that are used to associate the titles table with the authors table. The following SELECT statement uses
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 be matched with the correct book title. The titleauthor table specifies the relationship between the authors table and the titles table. It does this by including one field from each table. The only purpose of the third table is to establish relationships between the fields of the other two tables. It does not contain any additional data itself.

Note how the field names are written in this example. In order to distinguish the same field name au_id in the authors table and titles table, each field name is preceded by the table name prefix
and a period. Named author.au_id The field belongs to the table authors, and the field named titleauthor.au_id belongs to the table titleauthor. The two will not be confused.

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

However, in many cases, the relationship between two tables is not complicated. For example, you need to specify the relationship between the table titles and the table publishers. Because it is impossible for a book title to match multiple publishers, you do not need a third table to indicate the relationship between the two tables. To specify the relationship between the titles table and the publishers table, you only need to have a common field between the two tables. In the database pubs, the tables titles and publishers have a field named pub_id. If you want to get a list of book titles
and their publishers, you can use the following statement:

SELECT title,pub_name FROM titles,publishers

WHERE titles.pub_id=publishers.pub_id

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

Usually, when you know in advance that there is a "many-to-many" relationship between the fields of two tables, you use a third table to relate the two tables. On the other hand, if there is only a "one-to-one" or "one-to-many" relationship between the fields of two tables, you can use a common field to relate them.


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