MySQL entry (2), mysql entry

MySQL entry (2), mysql entry

For more information about this study, see MySQL required and the official Manual MySQL 5.6 Reference Manual.

MySQL (1)
MySQL (3)
Content:
-MySQL join table
-MySQL View

Iv. MySQL join table

Assume that you want to store the goods information and create a products table. Each item occupies one row, including the product name, origin, and supplier information (supplier name, telephone number, etc ). The same supplier may have multiple products, so different products belonging to the same supplier may need to repeat the information of the supplier, which may cause a waste of storage space and time, and the inconvenience of modification. To solve this problem, create two tables, products and vendors. In the vendors table, the supplier ID (vend_id) is used as the primary key ), in the products table, vend_id is used as the foreign key (foreign key), so that the two tables are connected.
If data is stored in multiple tables, join can be used for query. Join is a mechanism used to associate a table in a SELECT statement.

Example of a join (implemented using WHERE ):

mysql> 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 relation without join conditions is a Cartesian product. The number of rows retrieved is the number of rows in the first table multiplied by the number of rows in the second table.

4.1 internal connections

The join used so far is called equijoin. It is based on the equality test between two tables. This join also becomes an internal join. The following more standard and preferred method returns the same result as the preceding method.

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

4.2 join multiple tables

mysql> 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 for queries. Table aliases are different from column aliases and are not returned to the client. The following statement uses the table alias and returns the same result as the preceding statement.

mysql> 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 auto join

If you find that an item (whose ID is DTNTR) has problems, you want to know if other items produced by the supplier that produces the item also have these problems. This query requires that you first find the supplier that produces the item with the ID of DTNTR, and then find other items produced by this supplier.

Subquery:

mysql> SELECT vend_id, prod_id, prod_name    -> FROM products    -> WHERE vend_id IN ( SELECT vend_id                       -> FROM products                       -> WHERE prod_id = 'DTNTR' );

Self-join mode:

mysql> SELECT p1.vend_id, p1.prod_id, p1.prod_name    -> FROM products AS p1, products AS p2    -> WHERE p1.vend_id = p2.vend_id    -> AND p2.prod_id = 'DTNTR';

4.4 external connections

The MERs table stores information about all customers. Each customer has a unique ID (cust_id) and the cust_id is the primary key of customers. The orders table stores customer orders (but not order details). Each order has a unique order number (order_num). orders are associated with the MERs table using the cust_id column.

Query customers with existing orders and their order numbers (you can use internal connections ):

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

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

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

When using the outer join syntax, you must use the LEFT or RIGHT keyword to specify the table containing all its rows (RIGHT refers to the table on the RIGHT of the outer join, LEFT refers to the table on the LEFT of outer join ).

4.5 Use a join with aggregate Functions

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

An article about connection

V. What is MySQL view 5.1?

A view is a virtual table. Unlike tables that contain data, a view only contains queries that Dynamically Retrieve data when used.
For example, the following statement retrieves the information of the customer who ordered a specific product.

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

To write such a statement, you must know the table structure and join relationship. Now, If You encapsulate this query into a virtual table named productcustomers, you can easily retrieve the same data as follows:

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

5.2 why use a view

Reuse SQL statements, simplify complex SQL operations, and protect data. However, because a view does not contain data, each time a query is performed, the performance is affected.

5.3 use View

UseCREATE VIEWStatement to create a view;
UseSHOW CREATE VIEW viewname;Statement to view the statements used to create the view;
UseDROP VIEW viewname;To delete a view;
When updating a view, you can use 'drop 'to use 'create. You can also directly useCREATE OR REPLACE VIEWIf the view does not exist, it is created. If yes, it replaces the original view.

(1) simplify complex connections using views

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 preceding statement creates a view named productcustomers, which joins three tables to return the list of all customers who have subscribed to any product.

mysql> SELECT * FROM productcustomers;

(2) reformat the retrieved data with the view

mysql> CREATE VIEW vendorlocations AS    -> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')    -> AS vend_title    -> FROM vendors ORDER BY vend_name;mysql> SELECT * FROM vendorlocations;

(3) filter unwanted data with views

mysql> CREATE VIEW customeremaillist AS    -> SELECT cust_id, cust_name, cust_email    -> FROM customers    -> WHERE cust_email IS NOT NULL;

Create a view customeremaillist to filter users without email addresses.

mysql> SELECT * FROM customeremaillist;

(4) use view 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 an orderitemsexpanded view and calculate the total prices of all products in the order.

mysql> SELECT * FROM orderitemsexpanded    -> WHERE order_num = 20005;

5.4 view information

View basic information using the DESCRIBE statement:

mysql> DESC orderitemsexpanded;

mysql> SHOW CREATE VIEW orderitemsexpanded;

View information about all created views:

Mysql> USE information_schema; mysql> SELECT * FROM views; // The statement outputs more information. mysql> SELECT TABLE_NAME, definer from views;