MySQL Must Know must Know – Concepts Section

MySQL must know must – Install application section

MySQL must know must – retrieve data sections

The database files used below are available in themysql_scriptsTo find it.

Using subqueries

Suppose you want to list all the customers who ordered the item TNT2. We can break down the following three steps.

  1. Retrieves the numbers of all orders containing the item TNT2.
  2. Retrieves the ids of all customers with the order numbers listed in the previous step.
  3. Retrieves customer information for all customer ids returned in the previous step.
SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN (SELECT cust_id FROM orders
WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
Copy the code

Assuming we need to display the total number of orders for each customer in the Customers table, we can identify the following two steps.

  1. Retrieves the customer list from the Customers table.
  2. For each customer retrieved, count the number of orders in the Orders table.
SELECT cust_name, cust_contact,
(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;
+----------------+--------------+--------+
| cust_name      | cust_contact | orders |
+----------------+--------------+--------+
| Coyote Inc.    | Y Lee        |      2 |
| E Fudd         | E Fudd       |      1 |
| Mouse House    | Jerry Mouse  |      0 |
| Wascals        | Jim Jones    |      1 |
| Yosemite Place | Y Sam        |      1 |
+----------------+--------------+--------+
Copy the code


Join table

Equijoin, which is based on an equality test between two tables. This join is also called an inner join. SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name; SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name; The above mentioned subquery is used to return the list of customers who ordered TNT. Now it is changed to join table, which can be compared with the subquery. SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num AND prod_id = 'TNT2';Copy the code

Pay attention to

  • You should ensure that all joins have a WHERE clause, otherwise MySQL will return much more data than it wants.


Creating high-level joins

Table aliases and self-joins

## use table aliases, SELECT CUST_name, CUST_contact FROM customers AS C, Orders AS O, orderitems AS oi WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num AND prod_id = 'TNT2'; ### use self-join, SELECT * FROM products where ID = 1 SELECT * FROM products where ID = 1; SELECT * FROM products where ID = 1; products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';Copy the code

External connection

Joins contain rows that have no associated rows in related tables. There are two basic forms of OUTER joins: LEFT OUTER JOIN and right OUTER JOIN. The only difference between them is the order of the associated tables. For more details, see JOIN.

### list orders placed by each customer, Including SELECT Customers. Cust_id, orders.order_num FROM customers LEFT JOIN orders ON customers.cust_id = orders.cust_id; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10002 | NULL | | 10003 | 20006 | | 10004 | 20007 | | 10005 | | 20008 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + # # # # for each user order count, SELECT c.cust_name, c.cust_id, COUNT(o.order_num) AS order_count FROM customers AS c LEFT JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id;  +----------------+---------+-------------+ | cust_name | cust_id | order_count | +----------------+---------+-------------+ | Coyote Inc. | 10001 | 2 | | Mouse House | 10002 | 0 | | Wascals | 10003 | 1  | | Yosemite Place | 10004 | 1 | | E Fudd | 10005 | 1 | +----------------+---------+-------------+Copy the code


Combination query

MySQL allows you to execute multiple queries (multiple SELECT statements) and return the results as a single query result set. These combined queries are called union or compound Queries.

There are two basic cases where you need to use a composite query:

  • Return similarly structured data from different tables in a single query;
  • Perform multiple queries on a single table, returning data as a single query.
### select all items whose price is less than or equal to 5 and find all items made by suppliers 1001 and 1002 (regardless of price) SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001,1002); ### use the combined query implementation, SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002); SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION ALL SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002); SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002) ORDER BY vend_id, prod_id;Copy the code

Pay attention to

  • A UNION must consist of two or more SELECT statements separated by the keyword UNION.
  • Each query in the UNION must contain the same column, expression, or aggregate function (although the columns need not be listed in the same order).
  • When sorting composite query results, only one ORDER BY clause can be used, which must appear after the last SELECT statement.

Front- end-Basics: Watch, Star, Front-End-Basics: Front-End-Basics

MySql must know must know this article