Shulou(Shulou.com)06/01 Report--

1. Use a table alias

In addition to being used for column names and calculated fields, SQL also allows aliases for table names. There are two main reasons for doing so:

Shorten SQL statement

Allows the same table to be used multiple times in a single SELECT statement.

Mysql > SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id='TNT2' +-+ | cust_name | cust_contact | +-+-+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +-+-+

Analysis: you can see that all three tables in the FROM clause have aliases. Customers AS c establishes c as an alias for customers, and so on. This makes it possible to use the abbreviated c instead of the full name customers. In this example, the table alias is used only for the WHERE clause. However, table aliases can be used not only for WHERE clauses, but also for lists of SELECT, ORDER BY clauses, and other parts of the statement.

two。 Use different types of joins

2.1 self-connection

Mysql > SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.ProdSecretidshipping DTNTR'- > +-+-+ | prod_id | prod_name | +-+-+ | DTNTR | Detonator | | FB | Bird seed | | FC | Carrots | | SAFE | Safe | | SLING | Sling | | TNT1 | TNT (1 stick) | | TNT2 | | TNT (5 sticks) | +-+ + |

Analysis: the two tables needed in this query are actually the same tables, so the products table appears twice in the FROM clause. Although this is perfectly legal, the reference to products is ambiguous because MySQL does not know which instance of the products table you are referring to.

Use self-join instead of subquery: self-join is often used as an external statement instead of a subquery statement used to retrieve data from the same table. Although the end result is the same, sometimes dealing with joins is much faster than subqueries

2.2 Natural connection

Natural joins exclude multiple occurrences so that each column is returned only once.

Mysql > SELECT c. Items, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price FROM customers AS c, orders AS ocharge orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB' +- -+-+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | order_num | order_ Date | prod_id | quantity | item_price | +-+- -- + | 10001 | Coyote Inc. | 200Maple Lane | Detroit | MI | 44444 | USA | Y Lee | | ylee@coyote.com | 20005 | 2005-09-01 00:00:00 | FB | 1 | 10.00 | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | 20009 | 2005-10-08 00:00:00 | FB | 1 | 10.00 | +-| + -+-

+

Analysis: in this example, wildcards are used only for the first table. All other columns are explicitly listed, so no duplicate columns are retrieved.

2.3 external connections

Many joins associate rows in one table with rows in another table. Sometimes, however, you need to contain rows that are not associated with them.

In order to retrieve all customers, including those without orders, you can do the following:

Mysql > SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON omers.cust_id = orders.cust_id +-+-+ | cust_id | order_num | +-+-+ | 10001 | 20005 | 10001 | 20009 | 10002 | NULL | 10003 | 20006 | 10004 | 20007 | | 10005 | 20008 | +-+-+

Analysis: similar to the internal join seen in the previous chapter, this SELECT statement uses the keyword OUTER JOIN to specify the type of join (rather than in the WHERE clause). However, unlike the rows in the two tables associated with the internal join, the external join also includes rows that do not have associated rows. When using OUTER JOIN syntax, you must use the RIGHT or LEFT keyword to specify a table that includes all its rows (RIGHT indicates the table to the right of OUTER JOIN, and LEFT indicates the table to the left of OUTER JOIN). The above example uses LEFT OUTER JOIN to select all rows from the left table (the customers table) of the FROM clause.

3 using joins with aggregate functions

If you want to retrieve all customers and the number of orders placed by each customer, the following code uses the COUNT () function to do this:

Mysql > SELECT customers.cust_name, customers.cust_id, COUNT (orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id +-+ | cust_name | cust_id | num_ord | +-+ | Coyote Inc. | 10001 | 2 | Wascals | 10003 | 1 | | Yosemite Place | 10004 | 1 | | E Fudd | 10005 | 1 | +-+

Analysis: this SELECT statement uses INNER JOIN to correlate customers and orders tables. The GROUP BY clause is grouped by guest, so the function calls COUNT (orders.order_num) to count the order for each customer and returns it as a num_ord.

Mysql > SELECT customers.cust_name, customers.cust_id, COUNT (orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id +-+ | cust_name | cust_id | num_ord | +-+ | Coyote Inc. | 10001 | 2 | Mouse House | 10002 | 0 | Wascals | 10003 | 1 | Yosemite Place | 10004 | 1 | | E Fudd | 10005 | 1 | +-+

Analysis: this example uses the left outer link to include all customers, even those who do not place any orders. The results show that the customer Mouse House is also included, which has 0 orders.

These are the details of MySQL must know: create advanced links, please follow other related articles for more!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.