MySQL data retrieval + query + Full Text Search

"0" README 0.1) This part of the text described in the "MySQL must Know", designed to review "MySQL data retrieval + query + Full-text Search" basic knowledge;
"1" Using subqueries 1) query definition: Any SQL statement is a query. However, this term generally refers to the SELECT statement; SQL also allows you to create subqueries, which are queries nested within other queries; 2) Filter by sub-query (WHERE clause, in clause)

2.1) can use the result returned by a SELECT statement for the WHERE clause of another SELECT statement;
3) Use subqueries as calculated fields 3.1) Another way to use a subquery is to create a calculated field: The total number of orders for an explicit vendor and the vendor's ID and name;

Attention) Obviously, select Id,name, (select COUNT (*) from product p where p.vendor=v.id Group by vendor) as My_co Unt from vendor V; unlike other SQL because where P.vendor=v.id group by vendor) as My_count the from Vendor v clause uses a fully qualified name to compare the IDs of internal and external tables for equality ;
"2" junction table 1) Primary key + foreign key: 1.1) Primary key: ability to uniquely identify each row of data; 1.2) foreign key: The foreign key is a column in a table that contains the primary key value of another table and defines the relationship between the two tables; 2) Create a junction:
3) Cartesian product: The result of a table relationship without a junction condition is a Cartesian product. Number of rows retrieved is the number of rows in the first table multiplied by the number of rows in the second table;
Attention) do not forget the WHERE clause: you should ensure that all junctions have a WHERE clause, otherwise MySQL will return much more data than desired data;
4) Internal coupling (= equivalent coupling) 4.1) Definition: The connection used so far is called an equivalent junction, which is based on an equality test between two tables. This connection is also known as internal coupling;

Select V.id,v.name from product p, vendor v where P.vendor = V.id;select v.id,v.name from product p inner joins vendor V on P.vendor = v.id;

5) joining multiple tables
5.1) We need to find out the customer ID of the product purchased by Wang supplier;

Select customer_id from T_order o,product P,vendor v where o.prod_id=p.id and P.vendor = v.id and V.name= ' Wang ';

"3" Create an advanced junctionfor spec info,please visit MySQL natural coupling + external junction (left outer connection, right outer connection) + internal Junction

"4" combination query 1)MySQL allows multiple queries (multiple Select) to be executed and returns the result as a single query result set. These combination queries are often called and (union) or compound queries; 2) There are two kinds of case, need to use combinatorial query;

case1) returns similar structure data from different tables in a single query , CASE2) executes multiple queries against a single table and returns data as a single query;

3) Create a combined query: The union operator can be used to combine several SQL queries, and with union, multiple SELECT statements are given, and their results are composed into a single result set; 4) Use Union
5) Using the Union Rule (rules)

R1)Union must consist of two or more than two SELECT statements separated by a keyword union; R2)each query in the Union must contain the columns of the system. An expression or aggregation function (though the columns do not need to be given in the same order); R3) The column quantity type must be compatible: the type does not have to be identical, but must be a type that the DBMS can implicitly convert to;

6) rows that contain or cancel duplicates 6.1) Problem+solution:

problem) The first SELECT statement returns 4 rows, the second SELECT statement returns 3 rows, and after combining two select statements with union, only 6 rows are returned instead of 7 rows; Union automatically removes duplicate rows from the query result set (duplicate rows are automatically canceled when using union); solution) If you want to return all matching rows, you can use union all instead of union;

7) Sort the results of the combined query:When you use union to combine queries, you can use only one order BY statement, which must appear after the last SELECT statement.
Attention) When using union, you can also use different tables to combine; "5" full-text Search 1) only MyISAM engine supports full text search; 2) For full-text search, the searched columns must be indexed and continually re-indexed as the data changes;

2.1) Enable full-text search support: generally enables full-text search when creating tables; The CREATE TABLE statement receives a FULLTEXT clause that gives a comma-delimited list of indexed columns;

See a lychee) using the Create demo fulltext clause:

analysis of the above code:

A1) MySQL indexes it according to the instructions of clause fulltext (NOTE_TEXT). Here the fulltext indexes a single column, and multiple columns can be indexed if needed; A2) After the definition, MySQL automatically maintains the index. the index automatically updates when rows are added, updated, or deleted;

Attention)do not use fulltext when importing data: Updating an index takes time, though not many, but takes time after all. If you are importing data to a new table, you should not enable the Fulltext index at this time. You should import all the data first, and then modify the table to define Fulltext, which will help you to import the data more quickly;

< BR style= "Background-color:inherit" > 2.2) Full-Text search: use the match () function and the against () function centroid full-text search, the match () function specifies the column being searched, and against () specifies the search expression to use; Attention) The following lychee turns from: http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/ Functions.html#fulltext-search
2.3) Full-text search with query extension

Full-Text Search supports query extension capabilities ( especially its changeable "blind query extension" ). If the length of the search phrase is too short , then users need to rely on the general lack of the full-text search engineimplicit knowledge for querying,&NBSP; a search " Span style= "Background-color:inherit" >database "the user of the term, may think" mysql "," oracle "," DB2< Span style= "Background-color:inherit" > " &NBSP; and&NBSP; "rdbms " are in accordance with " Span style= the item "Background-color:inherit" >databases "and therefore should be returned. This is both implicit knowledge.

After the following search phrase, add WITH QUERY EXPANSION , activating the blind query extension function ( that is what is commonly said automatic dependency feedback ) . It performs two searches, where the search phrase for the second search is the original search phrase for the few top-level file connections found with the first search. This if one of these files contains the word " Databases "and the word" mysql " ,&NBSP; The second search will look for files that contain the word "mysql " even if the files do not contain the word "database ". The following example shows this difference:

2.4) Boolean full-text index: using in Boolean MODE to modify the program, MySQL You can also perform a Boolean full-text search to provide details about the following (details)

D1) to match the word;

D2) the words to be excluded;

D3) Arranging hints (some words are more important than others, and more important words are more hierarchical);

D4) expression grouping;

instructions for using full-Text Search (specification)

< STRONG>S1) When indexing full-text data, short words are ignored and excluded from the index. The short term is defined as those words with 3 or 3 characters (this number can be modified if needed); S2) MySQL comes with a built-in list of non-used words that are always ignored when indexing full-text data. If necessary, you can override this list; S3) Many words appear very frequently, and searching for them is useless. Therefore, MySQL specifies a 50% rule, if a word appears in more than 50% of the line, it is ignored as a non-word, 50% rule is not used in Boolean mode; S4) If the number of rows in the table is less than 3 rows, the full-text search does not return results (because each time or not, or at least 50% of the rows); S5) ignores single quotes in the word, such as" t = = dont; S6) languages that do not have word separators, including Japanese and Chinese, do not properly return full-text search results; S7) supports full-text search only in the MyISAM database engine;

MySQL data retrieval + query + Full Text Search