Note: The blog summarizes the knowledge points in MySQL, the core content of Chapter 13, 14, 15, 16, 17 and 18.

Operators involved: GROUP BY, HAVING, ORDER BY, INNER JOIN, OUT JOIN, AS, UNION;

Introduction to the tables used in the book and their script files: Introduction to the Table in Mysql

Packet data

1. Data grouping

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

2. Filtration grouping

SELECT cust_id,COUNT(*) AS prders
FROM orders
WHERE prod_price >=10
GROUP BY cust_id
HAVING COUNT(*) >= 2;

3. Grouping and Sorting

SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderiterms
GROUP BY order_num
HAVING COUNT(quantity*item_price) >= 50
ORDER BY ordertotal;

4.SELECT clause order

The order is SELECT - > FROM - > WHERE - > GROUP BY - > HAVING - > ORDER BY - > LIMIT.

Using subqueries

Multi-use sub-queries in IN of WHERE clause

1. Using sub-queries to filter

SELECT cust_id
FROM orders
WHERE order_num IN(SELECT order_num
                   FROM orderitems
                   WHERE prod_id='TNT2');
-- It can also be nested in multiple layers, but it's slow.

2. Using computational fields to use subqueries

SELECT cust_name,cust_state,
      (SELECT COUNT(*)
      FROM orders
      WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
-- orders To compute fields

Consolidated table

1. Creating Connections

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

-- The following is the result of Cartesian product, the number of rows is the product of the number of rows in two tables.
SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name,prod_name;

2. Internal Connections

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

3. Join multiple tables

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

Creating Advanced Connections

1. Use table aliases

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_county),')') AS vend_title
FROM vendors
ORDER BY vend_name;

2. Self-connection

-- Retrieval of other items produced by the supplier
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id='DTNTR')

-- Aliases can also be used to self-join
SELECT 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';

3. Natural Connection

SELECT c.*, o.order_num, o.order_date,
       oi.prod_id, oi.quantity, oi.item_price
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 = 'FB';

4. External linkages

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id;

5. Use join with aggregate function

SELECT customers.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;

Combination query

1. Use UNION

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);

2. Include or cancel duplicate rows

-- UNION Duplicate rows are automatically removed; if you want to return all rows, you can use UNION ALL；
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);

3. Sorting the results of combined queries

-- ORDER BY It ranks the results after combination.
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_price;

Full text search

Two commonly used engines are MyISAM and InnoDB, which support full-text search, while the latter does not.

1. Enable full text search support

CREATE TABLE productnotes
(
 note_id int        NOT NULL AUTO_INCREMENT,
 prod_id char(10)   NOT NULL,
 note_date datetime NOT NULL,
 note_text text     NULL,
 PRIMARY KEY(note_id),
 FULLTEXT(note_text) --Here is to enable full text;
)ENGING=MyISAM;

2. Full text search

-- It's mainly used for two functions. Match()and Against()，Match()Specify the column to be searched, Against()Specifies the search expression to use.
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

-- The above search can also be used simply. LIKE Completion;
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

-- Difference: Full text search ranks the results;

3. Using Query Extension

-- Find all other rows that may be relevant to the search;
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit' WITH QUERY EXANSION);

4. Boolean Text Search

-- No 50%Rule (When the return value is too much, only 50 is returned%Speed is very slow.
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

-- Full text Boolean operation can also be used with many Boolean operators.
-- Example 1, Matching Inclusive Words rabbit and bait Line;
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+heavy +bait' IN BOOLEAN MODE);

-- Example 2, Matching Inclusive Words rabbit and bait At least one of the rows;
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy bait' IN BOOLEAN MODE);

Full text Boolean operator

+ Inclusion, words must exist;
- Exclusion, words must not exist;
> Include, and increase the level value;
<Include and reduce the rank value;
(2) Form words into sub-expressions;
~ Cancel the ranking value of a word;
* The wildcard at the end of the word;
"Defines a phrase;