Getting Started with MySQL (ii)

This study note refers to "MySQL must know" and the official manual MySQL 5.6 Reference Manual

Getting started with MySQL (i)

The content of this article:
-MySQL junction table
-MySQL View

Iv. MySQL junction table

Suppose you want to store information about the goods, create a Products table, a line of information for each shipment, including the name of the product, the origin and the supplier's information (supplier name, phone number, etc.). The same supplier may have a variety of products, then different products belonging to the same supplier may want to re-store the information of the vendor, which will result in storage space and time wasted, inconvenient changes and so on. The solution to this problem is to build two tables, products and vendors, the vendors table with the Supplier ID (VEND_ID) as the primary key (primary key), the Products table with vend_id as the foreign key (foreign key), This establishes a connection between the two tables.
If the data is stored in more than one table, it can be queried using a junction, which is a mechanism used to correlate tables in a SELECT statement.

An example of a junction (with WHERE implementation):

SELECT vend_name, prod_name, prod_price    -> FROM vendors, products    -> WHERE vendors.vend_id = products.vend_id    -> ORDER BY vend_name, prod_name;

The result returned by a table relationship without a join condition is a Cartesian product, and the number of rows retrieved is the number of rows in the first table multiplied by the rows in the second table.

4.1 Internal coupling

The junction used so far is called the equivalent Junction (Equijoin), which is based on an equality test between two tables, which is also an internal junction. The following more canonical and preferred method and the above notation return the same result.

SELECT vend_name, prod_name, prod_price    -> FROM vendors INNER JOIN products    -> ON vendors.vend_id = products.vend_id;

4.2 Joining multiple tables

SELECT prod_name, vend_name, prod_price, quantity    -> FROM orderitems, products, vendors    -> WHERE products.vend_id = vendors.vend_id    -> AND orderitems.prod_id = products.prod_id    -> AND order_num = 20005;

For simplicity, you can also use table aliases to query, table aliases and column aliases are different, table aliases are not returned to the client. The following statement uses a table alias, and returns the same result as the above statement.

SELECT prod_name, vend_name, prod_price, quantity    -> FROM orderitems AS o, products AS p, vendors AS v    -> WHERE p.vend_id = v.vend_id    -> AND o.prod_id = p.prod_id    -> AND o.order_num = 20005;

4.3 Self-coupling

If you find a problem with an item (whose ID is dtntr), you want to know if any of the other items produced by the supplier that produced the item also have these problems. This query requires that the vendor of the item with the production ID dtntr be found first, and then the other items produced by this supplier are identified.

How to use subqueries:

mysql>SELECT vend_id, prod_id, prod_name    -> FROM products    -> WHEREINSELECT vend_id                       -> FROM products                       -> WHERE=‘DTNTR‘ );

In a self-coupling way:

SELECT p1.vend_id, p1.prod_id, p1.prod_name    FROMASAS p2    WHERE p1.vend_id = p2.vend_id    AND‘DTNTR‘;

4.4 External coupling

The Customers table stores information for all customers, each customer has a unique ID (cust_id), and cust_id is the primary key for customers. The Orders table stores the customer order (but not the order details), each order has a unique order number (Order_num), and the order is associated with the Customers table with the cust_id column.

Query for the customer with the order and their order number (can be used within the junction):

mysql>SELECT customers.cust_id, cust_name, orders.order_num    -> JOIN orders    -> ON customers.= orders.cust_id;

Query all customers and their orders (including customers without orders):

mysql>SELECT customers.cust_id, cust_name, orders.order_num    -> JOIN orders    -> ON customers.= orders.cust_id;

When you use the OUTER JOIN syntax, you must use the left or right keyword to specify the table that includes all of its rows (that is, the table on the OUTER join to the right of the OUTER join).

4.5 using a junction with aggregation functions

mysql>SELECT customers.cust_id, cust_name, orders.order_num,    -> COUNT(orders.order_num) AS num_ord    -> JOIN orders    -> ON customers.= orders.cust_id    -> GROUPBY customers.cust_id;

An article about the link

V. MySQL View 5.1 What is a view

The

     view is a virtual table. Unlike a table that contains data, a view contains only queries that retrieve data dynamically when used.
example, the following statement completes retrieving information for a customer who has ordered a specific product.

mysql>SELECT cust_name, cust_contact    -> FROM customers, orders, orderitems    -> WHERE customers.= orders.cust_id    -> AND orders.= orderitems.order_num    -> AND orderitems.=‘TNT2‘;

To write such a statement must know the structure of the table and the connection relationship, now if the query is wrapped into a virtual table named Productcustomers, you can easily retrieve the same data as follows:

mysql>SELECT cust_name, cust_contact     -> FROM productcustomers     -> WHERE=‘TNT2‘;

5.2 Why Use views

Reuse SQL statements, simplify complex SQL operations, and protect data. However, because the view does not contain data, a retrieval is done each time it is used, which can affect performance.

5.3 Using views

Use CREATE VIEW statements to create views;
Use SHOW CREATE VIEW viewname; the statement to view the statement that created the view;
Use DROP VIEW viewname; to delete a view;
When you update a view, you can first use the DROP in CREATE. It can also be used directly CREATE OR REPLACE VIEW if the view does not exist and is created if it exists, replacing the original view.

(1) Using views to simplify complex joins

mysql> CREATE VIEW productcustomers AS    -> SELECT cust_name, cust_contact, prod_id    -> FROM customers, orders, orderitems    -> WHERE customer.cust_id = orders.cust_id    -> AND orders.order_num = orderitems.order_num;

The above statement creates a view named Productcustomers that joins three tables to return a list of all customers that have ordered any product.

SELECTFROM productcustomers;

(2) Reformat the retrieved data with a view

CREATEAS    SELECTConcat‘ (‘‘)‘)    AS vend_title    FROMORDERBYSELECTFROM vendorlocations;

(3) filter unwanted data with a view

CREATEAS    SELECT cust_id, cust_name, cust_email    FROM customers    WHEREISNOT NULL;

New View customeremaillist filter users who do not have a mailbox address.

SELECTFROM customeremaillist;

(4) Working with views and calculated fields

mysql> CREATE VIEW orderitemsexpanded AS    -> SELECT order_num, prod_id, quantity, item_price,    -> quantity * item_price AS expanded_price    -> FROM orderitems;

Create a new view orderitemsexpanded to calculate the total price of the products in all orders.

SELECTFROM orderitemsexpanded    WHERE20005;

5.4 Viewing information for a view

Use the DESCRIBE statement to view basic information about the view:

DESC orderitemsexpanded;

CREATE VIEW orderitemsexpanded;

To view information about all created views:

SELECTFROM views;            // 该语句输出信息比较多SELECTFROM views;

Getting Started with MySQL (ii)