Select-sql JoJo talk-First week

Objective

This article discusses the application of Select-sql in Visual FoxPro and SQL Server in several instances. We will use the SQL Server 2000 English version of sample database Northwind as the blueprint, the author has converted this database to Visual FoxPro data, you can download it. The experimental environment used by the author is: SQL Server (e), Visual FoxPro 7.0 (e). Of course, you can also get results with SQL Server 7 and Visual FoxPro 6, after all, Select-sql is one of the most basic database statements ...

First week of July 28, 2001

How to experiment

In Visual FoxPro we enter SQL statements directly in the Command window, in SQL Server we use SQL Query Analyzer. SQL Query Analyzer's Startup method is: Start-Program-"Microsoft SQL server-" Query Analyzer. The following figure:

After opening the program as follows:

Note: Select the Northwind database in the DB column.

Now let's get to the official start!

A. In the sale of products

The database has a product table-products, which stores all the products sold in the history of the enterprise, including, of course, the products now available for sale. The reason that you do not remove products that have been sold, but not now, from the datasheet is that the data is still associated with other data, such as historical sales records. Products table use Discontinued field to indicate a product distribution status, 0-in sales, 1-do not sell. So for SQL Server, the select should write this:

SELECT products. Productid,products. PRODUCTNAME from the products WHERE products. Discontinued=0

This statement is basically consistent with SQL Server in Visual FoxPro, except that the Discontinued field is bit-type in SQL Server, the logical type in Visual FoxPro. Because I use SQL Server DST to convert Northwind data to Visual FoxPro data, it automatically converts bit types to logical types, so this select should be written in Visual FoxPro:

SELECT products. Productid,products. PRODUCTNAME from the products WHERE products. Discontinued=. F.

B. Sell the product and display its category name

This issue continues on the previous issue, just to get the name of each product category. There are only CategoryID fields in the Products table and CategoryName in the Categories table. For this multiple-table problem, I'm going to connect the table to the following SQL Server implementation:

SELECT products.*,categories. CategoryName from the products INNER JOIN CATEGORIES in products. Categoryid=categories. CategoryID
WHERE products. Discontinued=0

Products INNER JOIN CATEGORIES in products. Categoryid=categories. CategoryID means that a set is generated, provided that the CategoryID of the two source tables is equal.

So how does SQL Server generate this collection?

In fact, the computer did a very silly thing: each row of the table is connected with each row of the second table, which forms the N*m row data (n represents the number of rows in the first table, and M represents the number of rows in the second table, in this case 77*8).

For example: the first table is as follows:

Col_a	Col_b
1	A
2	B

The second table is as follows:

Col_c	Col_d
1	1
2	2
3	1
4	2
5	2

TABLE1 INNER JOIN TABLE2 on TABLE1. Col_a=table2. Col_d

The middle set is as follows: (2*5=10)

Col_a	Col_b	Col_c	Col_d
1	A	1	1
1	A	2	2
1	A	3	1
1	A	4	2
1	A	5	2
2	B	1	1
2	B	2	2
2	B	3	1
2	B	4	2
2	B	5	2

The second thing a computer does is to run the data of a result set with a join condition. In this case, on TABLE1. Col_a=table2. Col_d, in the huge set of the connection field cut off the required records, the following table does not highlight the data is cut off data.

Col_a	Col_b	Col_c	Col_d
1	A	1	1
1	A	2	2
1	A	3	1
1	A	4	2
1	A	5	2
2	B	1	1
2	B	2	2
2	B	3	1
2	B	4	2
2	B	5	2

The following middle set is formed:

Col_a	Col_b	Col_c	Col_d
1	A	1	1
1	A	3	1
2	B	2	2
2	B	4	2
2	B	5	2

This simple example then complements the full SQL statement: SELECT TABLE2. Col_c,table1. Col_b from TABLE1 INNER JOIN TABLE2 on TABLE1. Col_a=table2. Col_d WHERE TABLE2. Col_c>3

The computer filters out the unqualified data in the middle set based on the contents of the WHERE clause, and the result is:

Col_a	Col_b	Col_c	Col_d
1	A	1	1
1	A	3	1
2	B	2	2
2	B	4	2
2	B	5	2

To do this, the computer chooses the appropriate columns to list the final results to the user:

Col_c	Col_b
4	B
5	B

The idea of Sql,sql server processing for this example is basically the same as our simple example, which we will not elaborate on. The select in Visual FoxPro is written as:

SELECT products.*,categories. CategoryName from the products INNER JOIN;
CATEGORIES on products. Categoryid=categories. CategoryID WHERE Products. Discontinued=. T.

C. Listing suppliers and customers

Supplier information from the Suppliers table, customer information from the Customers table, as long as the two tables are connected together with the answer. You can write this in SQL Server:

SELECT city,companyname,contactname, ' CUSTOMERS ' as relationship from CUSTOMERS
UNION All
SELECT city,companyname,contactname, ' suppliers ' from suppliers

This statement can be copied almost in Visual FoxPro, but in fact you can't execute it here. This is because the related field is not the same length, even if the field type is consistent, that is, C (40) and C (41) are not connected. There is no such harsh limit in SQL Server. The author has adjusted the length of the related fields in the Suppliers table, so you can easily use the following statement:

SELECT city,companyname,contactname, ' CUSTOMERS ' as relationship from CUSTOMERS;
UNION all SELECT city,companyname,contactname, ' suppliers ' from suppliers

D. Preparing for the printing of detailed sales

This is a more complex SELECT statement, but it's not that high, it's just a few more tables participating in the connection, a total of 6. As a complete sales document must have: Sales basic conditions (Orders), Sales details (Order details), product conditions (products), sales staff (Employees), customer situation (Customers), Transportation (shippers )。 The idea of connecting these tables is consistent with the ideas in the previous example: first, connect two tables to get the intermediate result set, which is then connected to the third table to get a data set ... Thus, a large collection of data is obtained, and several fields are submitted according to the requirements of the SELECT statement. From this we can see: The patchwork of datasets is the most important work, the first work!

The implementation of SQL Server is as follows, and it is noteworthy that the table Order details are expanded in [] in the SELECT statement for SQL Server recognition.

The

SELECT ORDERS. Shipname,orders. Shipaddress,orders. Shipcity,orders. Shipregion,orders. Shippostalcode,
Orders.shipcountry,orders.customerid,customers.companyname as Customernam,
EMPLOYEES. Firstname+ ' +employees. LASTNAME as salesperson,
ORDERS. Orderid,orders. Orderdate,orders. RequiredDate,
Orders.shippeddate,shippers.companyname as Shippername,
[order DETAILS]. Productid,products. Productname,[order DETAILS]. Unitprice,[order DETAILS]. Quantity,[order DETAILS]. DISCOUNT,
[order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT) as EXTENDEDPRICE, ORDERS. FREIGHT
from ((EMPLOYEES INNER JOIN ORDERS on EMPLOYEES. Employeeid=orders. EMPLOYEEID)
INNER JOIN CUSTOMERS on ORDERS. Customerid=customers. CUSTOMERID)
INNER JOIN shippers on ORDERS. Shipvia=shippers. ShipperID)
INNER JOIN [order DETAILS] on ORDERS. Orderid=[order DETAILS]. ORDERID)
INNER JOIN products in [order DETAILS]. Productid=products. PRODUCTID

This statement can be executed almost without modification in Visual FoxPro. To be aware of is still the order details of the table name problem, for convenience, the author has given details to Order_Details, so the statement is as follows:

The

SELECT ORDERS. Shipname,orders. Shipaddress,orders. Shipcity,orders. Shipregion,orders. Shippostalcode,;
Orders.shipcountry,orders.customerid,customers.companyname as Customernam,;
EMPLOYEES. Firstname+ ' +employees. LASTNAME as salesperson,;
ORDERS. Orderid,orders. Orderdate,orders. RequiredDate,;
Orders.shippeddate,shippers.companyname as Shippername,;
Order_Details. Productid,products. Productname,order_details. Unitprice,order_details. Quantity,order_details. DISCOUNT,;
Order_Details. Unitprice*order_details. quantity* (1-order_details. DISCOUNT) as EXTENDEDPRICE, ORDERS. FREIGHT;
From ((() EMPLOYEES INNER JOIN ORDERS on EMPLOYEES. Employeeid=orders. EMPLOYEEID); The
INNER JOIN CUSTOMERS on ORDERS. Customerid=customers. CUSTOMERID); The
INNER JOIN shippers on ORDERS. Shipvia=shippers. ShipperID); The
INNER JOIN order_details on ORDERS. Orderid=order_details. ORDERID); The
INNER JOIN products on order_details. Productid=products. PRODUCTID

This week, to summarize: We found that Visual FoxPro's SELECT statement is very similar to SQL Server's SELECT statement and can sometimes be used without modification. However, each language has its own norms, in the comparison should pay attention to their characteristics, to hold their own characteristics.