MySQL must know before 15-20

15. Join table

One of the most powerful functions of SQL is to join tables during data retrieval and query execution. Join is the most important operation that can be executed using SQL SELECT.

A relational table is designed to ensure that information is divided into multiple tables. One type of data is a table, and each table is correlated through a certain relationship.

Foreign key: A foreign key is a column of a table. It contains the primary key value of another table and defines the relationship between two tables.

Scalability: it can adapt to the increasing workload without failure.

15.1 connections

Join is a mechanism used to join a table in a select statement. Therefore, join is called join. With special syntax, you can join multiple tables to return a set of outputs, and join the correct rows in the joined table at run time.

15.2 create a connection

SELECT vend_name, prod_name, prod_price FROMvendors, products WHERE vendo. vend_id = products. vend_id ORDER BYvend_name, prod_name;

+ ------------- + ---------------- + ------------ +

| Vend_name | prod_name | prod_price |

+ ------------- + ---------------- + ------------ +

| ACME | birds seed | 10.00 |

| ACME | Carrots | 2.50 |

| Accesskey | Detonator | 13.00 |

| ACME | Safe | 50.00 |

| ACME | log service | 4.49 |

| ACME | TNT (1 stick) | 2.50 |

| ACME | TNT (5 sticks) | 10.00 |

| Anvils R Us |. 5 ton andevil | 5.99 |

| Anvils R Us | 1 ton anevil | 9.99 |

| Anvils R Us | 2 ton andevil | 14.99 |

| Jet Set | JetPack 1000 | 35.00 |

| Jet Set | JetPack 2000 | 55.00 |

| LT Supplies | Fuses | 3.42 |

| LT Supplies | Oil can | 8.99 |

+ ------------- + ---------------- + ------------ +

14 rows in set (0.05 sec)

Ensure that all joins have a WHERE clause; otherwise, MySQL returns much more data than the desired data. When the referenced column may be ambiguous, you must use a fully qualified column name (Table Name and column name separated by a single vertex ).

15.3 internal connections

The join based on the equality test between two tables is called an equivalent join or an internal join. You can use a slightly different syntax to implement this type of join.

SELECT vend_name, prod_name, prod_price FROMvendors inner join products ON vendors. vend_id = products. vend_id;

The components of the FROM clause between the two tables are specified in inner join. When using this syntax, the join condition is given by a specific ON clause instead of the WHERE clause. The actual conditions passed to ON are the same as those passed to WHERE.

15.4 join multiple tables

SELECT cust_name, cust_contact FROMcustomers, orders, orderitems WHERE customers. cust_id = orders. cust_id ANDorderitems. order_num = orders. order_num AND prod_id = 'tnt2 ';

+ ---------------- + -------------- +

| Cust_name | cust_contact |

+ ---------------- + -------------- +

| Coyote Inc. | Y Lee |

| Yosemite Place | Y Sam |

+ ---------------- + -------------- +

2 rows in set (0.02 sec)

Here we implement the subquery function in chapter 14.

16. Create an advanced join

16.1 use table alias

SELECT cust_name, cust_contact FROM customersAS c, orders AS o, orderitemsAS oi WHERE c. cust_id = o. cust_id AND oi. order_num = o. order_numAND prod_id = 'tnt2 ';

The table alias layout can be used for the WHERE clause, the SELECT list, the order by clause, and other parts of the statement.

16.2 use different types of connections

Self-connection, spontaneous combustion connection and external connection

16.2.1 auto join

SELECT p1.prod _ id, p1.prod _ name FROMproducts AS p1, products AS p2 WHERE p1.vend _ id = p2.vend _ id AND p2.prod _ id = 'dtntr ';

16.2.2 natural connections

Natural joins exclude multiple occurrences so that each column value is returned once. In fact, every internal link we establish is a natural link.

16.2.3 external connections

Mysql> SELECTcustomers. cust_id, orders. order_num FROM customers LEFT OUTER JOIN

Orders ON customers. cust_id = orders. cust_id;

+ --------- + ----------- +

| Cust_id | order_num |

+ --------- + ----------- +

| 10001/20005 |

| 10001/20009 |

| 1, 10002 | NULL |

| 10003/20006 |

| 10004/20007 |

| 10005/20008 |

+ --------- + ----------- +

6 rows in set (0.00 sec)

Unlike the rows in the two tables associated with the internal join, the external join does not include the rows that are not associated. When using outer join, you must use the RIGHT or LEFT keyword to specify the table containing all its rows (RIGHT indicates the table on the RIGHT of outer join and LEFT indicates the table on the LEFT of outer join ).

16.3 use a join with aggregate Functions

Mysql> SELECTcustomers. cust_name, customers. cust_id, COUNT (orders. order_num) AS N

Num_ord FROM customers inner join orders ONcustomers. cust_id = orders. cust_id G

Roup by MERs. cust_id;

+ ---------------- + --------- + ---------- +

| Cust_name | cust_id | Nnum_ord |

+ ---------------- + --------- + ---------- +

| Coyote Inc. | 10001 | 2 |

| Wascalss | 10003 | 1 |

| Yosemite Place | 10004 | 1 |

| E Fudd | 10005 | 1 |

+ ---------------- + --------- + ---------- +

4 rows in set (0.00 sec)

17. Combined Query

A Combined Query is also called a union or composite query.

There are two basic cases:
1) return data with similar structures from different tables in a single query

2) execute multiple queries on a single table and return data based on a single query

17.1 create a Combined Query

SELECT vend_id, prod_id, prod_price FROM productsWHERE prod_price <= 5 union select vend_id, prod_id, prod_price FROM productsWHERE vend_id IN (1001,1002 );

Union all can contain duplicate columns selected by different SELECT clauses.

17.2 UNION rules

1) UNION must be composed of two or more SELECT statements, which are separated by the keyword UNION.

2) each query in UNION must contain the same columns, expressions, or aggregate functions.

3) The column data types can be different, but must be compatible

17.3 sort the combined query results

Order by must appear after the last SELECT statement

18. Full text search

Before full text search, set one or more columns in the table to FULLTEXT. Then, use Match () and Against () to perform full-text search. Match () indicates the column to be searched and Against () indicates the search expression to be used.

18.1 perform full text search

SELECT note_text FROM productnotes WHERE Match (note_text) Against ('rabbit ');

+ -------------------------------------------------------------------------------

---------------------------------------- +

| Note_text

|

+ -------------------------------------------------------------------------------

---------------------------------------- +

| Customer complaint: rabbit has been ableto detect trap, food apparently less

Valid now. |

| Quantity varies, sold by the sack load.

All guaranteed to be bright and orange, andsuitable for use as rabbit bait. |

+ -------------------------------------------------------------------------------

---------------------------------------- +

2 rows in set (0.05 sec)

The output order of the above two rows is obtained after sorting, that is, the rank of the 3rd words containing the word rabbit is higher than that of the 20th words. The following shows how to sort the entire text search.

Mysql> SELECT note_text, Match (note_text) Against ('rabbit ') AS rank FROM productn

Otes;

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- + --

---------------- +

| Note_text

| R

Ank |

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- + --

---------------- +

| Customer complaint:

Sticks not individually wrapped, too easyto mistakenly detonate all at once.

Recommend individual wrapping. | 0 |

| Can shipped full, refills not available.

Need to order new can if refill needed.

| 0 |

| Safe is combination locked, combinationnot provided with safe.

This is rarely a problem as safes aretypically blown up or dropped by MERs

. | 0 |

| Quantity varies, sold by the sack load.

All guaranteed to be bright and orange, andsuitable for use as rabbit bait.

| 1, 1.59055435657501 |

| Included fuses are short and have beenknown to detonate too quickly for some

MERs.

Longer fuses are available (item FU1) andshoshould be recommended. |

0 |

| Matches not included, recommend purchaseof matches or detonator (item DTNTR ).

|

0 |

| Please note that no returns will beaccepted if safe opened using explosives.

|

0 |

| Multiple customer returns, anvils failingto drop fast enough or falling backw

Ards on purchaser. Recommend that customerconsiders using heavier anvils. |

0 |

| Item is extremely heavy. Designed for dropping, not recommended for use with s

Lings, ropes, pulleys, or tightropes. |

0 |

| Customer complaint: rabbit has been ableto detect trap, food apparently less

Valid tive now. | 1

. 64080536365509. |

| Shipped unassembled, requires commontools (including oversized hammer ).

|

0 |

| Customer complaint:

Circular hole in safe floor can apparentlybe easily cut with handsaw.

| 0 |

| Customer complaint:

Not heavy enough to generate flying starsaround head of victim. If being purcha

Sed for dropping, recommend ANV02 or ANV03instead. | 0 |

| Call from individual trapped in safeplummeting to the ground, suggests an esc

Ape hatch be added.

Comment forwarded to vendor. | 0 |

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- + --

---------------- +

14 rows in set (0.03 sec)

Here, Match () and Against () are used in SELECT rather than WHERE clauses (). This causes all rows to be returned. Match () and Against () are used to create a computing column. This column contains the level value calculated by full text search. The level is calculated by MySQL based on the number of words in the row, the number of unique words, the total number of words in the entire index, and the number of rows containing the word.

18.2 use query Extension

The query extension is used to manage the range of the full text search results returned by the house fee. When using the query extension, MySQL scans data and indexes on both sides to complete the search:

1) First, perform a basic full text search to find all rows that match the rough search conditions.

2) Secondly, MySQL checks these matching rows and selects all the useful words (we will briefly explain how MySQL judges what is useful and what is useless)

3) Next, MySQL searches the entire text again. This time, not only the original conditions, but also all useful words are used.

Mysql> select note_text FROMproductnotes WHERE Match (note_text) Against ('anvils

'With query expansion );

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- +

| Note_text

|

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- +

| Multiple customer returns, anvils failingto drop fast enough or falling backw

Ards on purchaser. Recommend that customerconsiders using heavier anvils. |

| Customer complaint:

Sticks not individually wrapped, too easyto mistakenly detonate all at once.

Recommend individual wrapping. |

| Customer complaint:

Not heavy enough to generate flying starsaround head of victim. If being purcha

Sed for dropping, recommend ANV02 or ANV03instead. |

| Please note that no returns will beaccepted if safe opened using explosives.

|

| Customer complaint: rabbit has been ableto detect trap, food apparently less

Valid now. |

| Customer complaint:

Circular hole in safe floor can apparentlybe easily cut with handsaw.

|

| Matches not included, recommend purchaseof matches or detonator (item DTNTR ).

|

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- +

7 rows in set (0.00 sec)

The first line contains the word anvils, so the level is the highest. The second line has nothing to do with anvils, but because it contains two words (customer and recommend) In the first line, it is also retrieved. This will also happen in the next few lines.

18.3 Boolean text search

MySQL supports another form of full text search, Boolean search, which provides the following details:

1) words to be matched

2) The word to be excluded (if a line contains the word, no change is returned, even if it contains the word to be matched)

3) arrange the prompt (specify some words that are more important than others, and the level of words that are heavier is higher)

4) expression grouping

5) additional content

The Boolean method is different from the general full text search method in that it can be used even if the FULLTEXT index is not defined.

Boolean operators and descriptions are as follows:

+ Contain, the word must exist

-Exclude: The word must not appear.

> Include and add a level value

<Include, and reduce the level value

() Phrase into a subexpression

~ Cancels the sorting value of a word.

* Wildcard at the end of the word

"" Defines a phrase

SELECT note_text FROM productnotes WHERE Match (note_text) Against ('heive' in boolean mode); // The output result is as follows:

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- +

| Note_text

|

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- +

| Item is extremely heavy. Designed fordropping, not recommended for use with s

Lings, ropes, pulleys, or tightropes. |

| Customer complaint:

Not heavy enough to generate flying starsaround head of victim. If being purcha

Sed for dropping, recommend ANV02 or ANV03instead. |

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- +

2 rows in set (0.00 sec)

The keyword in boolean moden is used here.

Mysql> SELECT note_text FROMproductnotes WHERE Match (note_text) Against ('heavy

-Rope * 'in boolean mode );

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- +

| Note_text

|

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- +

| Customer complaint:

Not heavy enough to generate flying starsaround head of victim. If being purcha

Sed for dropping, recommend ANV02 or ANV03instead. |

+ -------------------------------------------------------------------------------

--------------------------------------------------------------------------- +

1 row in set (0.00 sec)

Here-rope * explicitly instructs MySQL to exclude including rope *.

The following are examples:

SELECT note_text FROM productnotes WHEREMatch (note_text) Against ('+ rabbit + bait' in boolean mode); // This search matches the rows containing the words rabbit and bait

SELECT note_text FROM productnotes WHEREMatch (note_text) Against ('rabbit bait' in boolean mode); // No operator is specified. This search matches the rows of at least one word IN rabbit and bait.

SELECT note_text FROM productnotes WHEREMatch (note_text) Against ('"rabbit + bait"' in boolean mode); // match the phrase rabbitbait instead of matching the two words rabbit and bait

SELECT note_text FROM productnotes WHEREMatch (note_text) Against ('> rabbit <bait' in boolean mode); // increase the rabbit level to lower the bait level.

SELECT note_text FROM productnotes WHEREMatch (note_text) Against ('+ safe + (<cobination)' in boolean mode); // This search matches the word safe and combination, reducing the level of the latter

19. insert data

19.1 Insert the complete row

Mysql> INSERT into MERs (cust_name, cust_address, cust_city, cust_state, cust_zi

P, cust_country, cust_contact, cust_email) VALUES ('pep E. Lapew ', '2017 main Street ',

'Losangeles', 'CA', '123', 'usa', 'null', 'null ');

Query OK, 1 row affected (0.06 sec)

19.2 insert multiple rows

Mysql> INSERT into MERs (cust_name, cust_address, cust_city, cust_state, cust_zi

P, cust_country) VALUES ('pep E. Lapew ', '100main Street ',

'Los Angeles', 'CA', '123', 'usa'), ('m. martian ', '42 Galaxy Way', 'New York ', 'ny', '123', 'USA ');

19.3 Insert the Retrieved Data

INSERT into MERs (cust_name, cust_address, cust_city, cust_state, cust_zi

P, cust_country) SELECTcust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_countryFROM custnew;

20. Update and delete data

20.1 with new data

Two methods

1) update all rows in the table without the WHERE clause

2) update a specific row in the table and add WHERE

The UPDATE statement consists of three parts:

1) The table to be updated

2) column names and their new values

3) determine the filter conditions to be updated

Mysql> UPDATE MERs SET cust_name = 'the Fudds ', cust_email = 'elmer @ fudd.com

'Where cust_id = 10005;

Query OK, 1 row affected (0.08 sec)

Rows matched: 1 Changed: 1 Warnings: 0

20.2 delete data

Two methods:

1) delete a specific row from the table without omitting the WHERE clause

2) Delete all rows from the table and omit the WHERE clause

Delete from MERs WHERE cust_id = 10006;

DELETE deletes the table content rather than the table itself.

To DELETE all rows, do not use DELETE. Use TRUNCATETABLE;

= MySQL required