mysql1-Basic Knowledge point 2-Query

Some examples in this article are the assumption that the DB is the test table named user.

1. Grammar
(1) Select Column name from table name [query condition]: column names can contain multiple, separated by commas, you can use wildcards, such as * to query all the contents of the table, the order of the columns is generally in the order defined by the table (unless you really need each column, otherwise try not to use, may affect performance). Regardless of the columns, the order of the retrieved rows is indeterminate.
(2) Select DISTINCT column name from ... : Only different columns are returned, and if multiple columns are queried, the data will be hidden only if the columns are all the same, that is, the data will be displayed only if there is a different column.
(3) Select xx from XX limit 5: Return only the first 5 rows (not enough 5 rows to return all rows); if limit 3, 5, return 3 rows from line 5 (line 4th) "can implement paged query"
(4) Select User.Name from Test.user: The table name or column name can be fully qualified, or both can be fully qualified (as shown in the example).
(5) Add: Although select is often used to retrieve data from a table, you can omit the FROM clause to simply access and manipulate the expression, such as Select 3*3 returns 9,select now () to return the current date and time, so select is often used for testing.

2. Search by condition: Where condition
(1) Support: operator (=/!=/<>/</<=/>/>=), extension operator (is [not] null,in,like,between), and/or combination query, not
(2) A in (' A ', ' B ', ' C '); A is ' a ', ' B ', ' C ' one of them; a between and 20.
(3) and/or Calculation Order: first calculate and, then calculate or; You can use parentheses. If where Con1 or Con2 and Con3, returns a column that satisfies Con1, or both Con2 and Con3, while where (Con1 or con2) and Con3 can change this behavior.
(4) Not: The following in, between, exists clauses can be reversed, not all clauses, such as where LastName not in (' Li ', ' Wang ')
(5) Like: Wildcard characters can be used. The% wildcard can match any character any number of times, such as a like '%a% ' indicates that A is contained ' a ', and '% ' can match any value, but cannot match null. _ wildcard characters can only match any single character. "Wildcard characters should be used as sparingly as possible, because the search is slow; try to avoid placing wildcards at the beginning of the search, which is the slowest" "like ' a ' that matches only ' a '"
(6) Note that when querying for NULL The judge uses is null and is not NULL, but when modifying the database, the use of SET column_name = null. Null represents an unknown, so a null value is not returned when a query uses a match filter (=) or a mismatch filter (! =).

3. Select COUNT () from table_name [where ...];
(1) The number of criteria records is satisfied in the query data table.
(2) The contents of select COUNT () brackets are arbitrary (but cannot be empty), and the principle is to determine whether the corresponding value is null in the queried record, and if not null, the record count, so count (1), COUNT (*), COUNT ("anything ") can return all of the required records (even if all columns of the record are null and counted), and count (column name) returns the number of records in the corresponding column that are not NULL in the record that satisfies the requirement, and the count (null) return value is 0.
(3) Use SELECT COUNT (distinct+ column name) to return the number of records in the record that satisfy the requirement that the corresponding column is not null and does not duplicate.

4. Aggregation function/aggregation function
(1) sum ()/max ()/min ()/avg (), and some standard deviation, slightly "count () also calculate aggregate function, not in this section of the scope" "When there is no group BY, the aggregate function for all check travel calculation; The aggregation function calculates each grouping "
(2) Syntax: select SUM (Salary) from table_name where ...
(3) () the corresponding expression (not necessarily the column name), but only one, cannot be empty or more, four functions will ignore the null line.
(4) Distinct: for five functions (including count), there are two modes, all or distinct, the former is calculated for all rows, distinct only the rows with different values, and when using DISTINCT, the column name must be followed, not the expression; max ()/min ( It's good to use distinct, but it doesn't make sense.
(5) Max ()/min () is generally used for numbers and dates, and is not good for text results.

5, Gourp by
(1) groups the records that meet the criteria, and counts the results within each grouping; The GROUP BY clause can contain any number of columns, each of which is exactly the same group, called nested groupings, and group by can be followed by a column name, a normal function, It cannot be an aggregate function, and if there is a null value in the grouping column, all null rows in the column (in which the column is grouped) are divided into a group, and the expression of the Select, including the column name, *, function (normal function or aggregate function), is not affected by the group by, and is not identical to the old version.
(2) Note that if the column name used in the SELECT clause is not the column name used by the group by, it is still the return of only one result, not all. In the following statement, only one name is returned for each dept.
SELECT name from the Staff Group by dept;
(3) Having and where:having support all the where operators; but the where filter is the whole table before the Group group, and having is after the grouping; the Where condition cannot use an aggregate function. Having can use aggregate functions (aggregate calculations for all rows within a group).
(4) with rollup: When using multiple columns for grouping, add [with rollup] to give statistics that are grouped by a single column. Examples are as follows:

It is important to note that when using a GROUP by statement with the WITH ROLLUP clause, the result set can no longer be sorted by using the order BY statement, and if the order of the returned results is not satisfactory, it needs to be sorted in the program after the application obtains the results. See URL http://blog.csdn.net/id19870510/article/details/6254358
(5) Although the order of the group by output is generally grouped by group, it is not necessarily necessary to add an order by if a clear order of output is required.

6. ORDER BY
(1) The query results are sorted, the row sequence can be different from the query column, you can have more than one column, separated by commas, first by the previous sort, if the previous same, followed by the next, ORDER BY clause must be after the FROM clause.
(2) Desc: Specifies the sort direction; The default is ASC, which does not need to be specified; Each DESC only works on one of the columns in front of it, and for multiple columns in descending order, each column specifies a desc
(3) Case-sensitive: MySQL default A and a are the same, cannot be differentiated by the ORDER BY clause, and if you need to change this behavior, you need to change the database settings.
(4) Order BY and limit: logically, the first order is given (can be used to find the maximum minimum), and the order by must precede the limit.
(5) Group by is used with order by: syntactically, group by must precede order by, logically, first grouped, then sorted by group.
(6) If there is no order BY, how are the results of the search sorted? Officially, InnoDB is sorted by default with primary key, but this is not guaranteed and should not be relied upon as a sort order. For MyISAM, online parlance is sorted by default in insert order, but if update is not guaranteed.
(7) Execution sequence Summary: Where>group by>having>order by>limit "distinct??? 】
Syntax: Select>from>where>group by>having>order by>limit "aggregate function can either do the result in the SELECT clause or in Having/order by (group ) clause to do the condition "

7, the number of query results (number of lines) summary
(1) When there is no GROUP by: If the SELECT clause has only a column name/normal function, it is all displayed, and if the SELECT clause has only an aggregate function, only one is displayed, and if there are both column names and aggregate functions, only one is displayed.
(2) When a group by exists: regardless of the SELECT clause, the number of displayed results is consistent with the number of groups that satisfy the query criteria (including where and having).

8. Join/Multi-table query
(1) Background/meaning: the design of the relational table guarantees that the information is decomposed into multiple tables, a table of data, and the tables are interrelated through certain relationships (columns). A join is a mechanism that can be associated with a table in a SELECT statement and is therefore called a junction. It should be noted that the junction is not a physical entity, that it does not exist in the actual database table, and that MySQL is built on demand and exists in the execution of the query.
(2) Create a junction
Form: You can use either the WHERE clause or the form of Join+on (Join+on can also mate with the WHERE clause, not conflict), as in the following example; Different methods may affect performance.
Select Vend_name, Prod_name, Prod_price
From Vendors,products
where vendors.vend_id=products.vend_id
Select Vend_name, Prod_name, Prod_price
From vendors joins products
On vendors.vend_id=products.vend_id
Junction conditions: Whether using where or using Join+on, the condition of the junction can be not only =, but also other conditions.
Number of junction tables: There is no limit to the number of junction tables, but the more tables you join, the more performance degradation you should use sparingly.
(3) Internal coupling and external coupling: the definition of an internal/external junction is not related to the use of where or join+on, regardless of the condition of the junction (although generally =, but not necessarily), only with respect to the inclusion of columns in the underlying table that do not meet the criteria. See "inner and outer connections" for details.
(4) Self-coupling
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 '
(5) Natural coupling: Exclude multiple occurrences, so that each column is returned only once, and non-natural connections are seldom encountered, temporarily do not understand
(6) using a junction with aggregation function "If there is no GROUP BY clause, because there is a count () function in Select, only one row of data is returned, where cust_id is the first cust_id of customers, and count () is the total number of orders"
Select customers.cust_id, COUNT (orders.order_num)
From customers join orders
On customers.cust_id = orders.cust_id
GROUP BY customers.cust_id
(7) Performance considerations: For the same query function, can be implemented in a variety of ways, such as sub-query (sub-query location can also be different), junction (including the use of where or join+on), union query, the where statement of the bombing days, etc., different query performance is different, even may be large differences. However, different ways of performance may be affected by the type of operation, the amount of data in the table, the presence of indexes, and other conditions, and different ways are affected by different parameters differently. Therefore, when the required query may take a long time, we should compare the different implementation methods by experiment, and choose the one with the fastest query speed. For example, to query the list of customers who have ordered TNT2, you can use the following two ways:

Select Cust_name,cust_contactfrom customerswhere cust_id in (select cust_id from   orders  where Order_num in ( Select Order_num from  orderitems  where prod_id = ' TNT2 '); Select Cust_name,cust_contactfrom customers, Orders, Orderitemswhere customers.cust_id = Orders.cust_idand Orderitems.order_num = Orders.order_numand orderitems.prod_id = ' TNT2 ';

(8) It should be noted that the link emphasizes the linkage between the tables, and there is no where/join+on of the deadlock, that is, multiple table queries can contain no junctions, that is, the condition of a multi-table query (Where/join+on) can only involve a single table, not necessarily involving two tables.

Number of rows: Depending on the condition, if the query condition is a query that involves more than one table, the total number of bars (two table combinations) that satisfy the result is returned, and if the query condition is a single table or no query criteria, the number of results is two tables that satisfy the multiply of the criteria.
Number of columns: Depends on the SELECT clause and, if *, the column of a A, a, a, or, otherwise, the arrangement as specified by the SELECT clause.

9, sub-query
(1) Query results as a new table: Try not to use this, the efficiency is low; if possible, use and instead of
Error usage
SELECT * FROM (SELECT * from ' user ' where ' name ' = ' 465465456 ') where id< ' 20 '
Error: Every derived table must has its own alias (alias)
Correct usage
SELECT * FROM (SELECT * from ' user ' where ' name ' = ' 465465456 ') as T WHERE id< ' ' #方法1
SELECT * FROM (SELECT * from ' user ' where ' name ' = ' 465465456 ') t WHERE id< ' #省略AS
(2) query results in the WHERE clause, the number of columns in the WHERE clause should be the same as the number of columns in the SELECT clause in the subquery (can be greater than 1)
SELECT * from ' user ' where id= (select id from ' user ' where name= ' 465465457 '); //=
SELECT * from ' user ' where ID in (the Select ID from ' user ' where name!= ' 465465457 ');//in
SELECT * from the user where id= (select Max (id) from ' user ');
SELECT * from user where Id=max (ID); Error usage
(3) Use a subquery as a calculated field: For each row of data (which should be a group of data at the time of grouping), perform a subquery once "can understand that each row/group of data as a constant/parameter for each subquery" The second way can also realize the ability to query the number of orders per customer. However, the ID and name information of the customer cannot be queried at the same time.
Select cust_id,
Cust_name,
(SELECT COUNT (*) from orders where order.cust_id = customers.cust_id) as Order_count
From customers;
Select COUNT (*) from the orders where cust_id in (select cust_id from Customers)

10. Regular Expressions
(1) MySQL only supports a very small subset of the implementation of most regular expressions; the regular expressions in MySQL are case-insensitive, in order to be case sensitive, use the binary keyword.
(2) The syntax is similar to the general regular expression, noting several points: unless the | is enclosed in a collection, it is applied to the entire string, and the series (? +*, etc.) that represent the number of characters applies to the previous character. [[: <:]] and [[:;:]] match the beginning and end of the word, respectively.
(3) Example (the following example matches the name contains 1000 of the data, and like ' 1000 ' can only match ' 1000 ')
where name REGEXP ' 1000 '
Where name REGEXP BINARY ' Jack '
Select ' Hello ' REGEXP ' [0-9] ' #返回0
Select ' Hello ' REGEXP ' [A-z] ' #返回1

11. calculated Field/field
(1) Overview: The function is to retrieve the converted, computed or formatted data directly in the DB (which is much faster than on the client, because the DBMS is optimized); The calculated field does not exist in the database table, but is created within the SELECT statement at the runtime; The data for the calculated field is returned in exactly the same way as the data for the other columns, and only the DB knows the difference.
(2) Alias: The replacement name of the field (which can also be used as a replacement for ordinary columns), if the AS clause is not added, the caption of the returned result that the client sees is Concat (last_name, "", first_name), plus, the caption is Full_name.
Select Concat (last_name, "", first_name) as full_name from user where ...
(3) Examples: concat (), Trim (), LTrim (), RTrim (), +-*/"More functions are described in the relevant chapters of the function"

12. Combination Query/union
(1) Definition: Use Union to link multiple SELECT statements and return the result as a single query result.
(2) Usage restrictions: Each query must contain the same column, expression, or aggregate function, but the order of the given columns does not require the same (in the name of the corresponding), the same type of column does not require the same types, but must be compatible with each other; You can query from the same or different tables.
(3) UNION ALL: when querying through the same table, duplicate data is removed by default, but if you use union ALL, duplicate data is preserved.
(4) Union and where: in general, the function of Union can be implemented by where or, but some complex cases cannot be implemented by where or, such as union all. In addition, the query performance is not the same.
(5) Union and ORDER by: can only be added after the last query (personal guess, when the query uses a table, the non-query column is used to sort; when querying multiple tables, you can only sort by using query columns)

13. Full Text Search
Why do you need a full-text search for like and regular expressions?
1, fast: Full-Text search uses the index, and the index is for the word, the search can also be targeted at words, you can quickly determine whether to match, match the frequency and so on.
So more quickly; personally disagree with this, because like and regular expressions can also use an index
2. More accurate match: Use Boolean full text match, more accurate
3, the results show more intelligent: Query extension can check related rows, can be sorted by the degree of matching and so on

How to use full-text search
1. Specify a column when creating a table: Multiple columns can be specified, and can be created without specifying later. After the definition, MySQL automatically maintains the index, adding, deleting, updating rows, and updating the index. Tip: If you import large amounts of data, it is recommended that you turn off the index first and then re-index it after the import is complete;
CREATE TABLE Productnotes (
note_id int,
Note_text text,
PRIMARY KEY (note_id),
Fulltext (Note_text)
) Engine=myisam;
2, general full-text search
(1) match () specifies the column being indexed, and if Fulltext specifies more than one column, match () must also specify multiple columns, and the order must be the same.
(2) against () specifies the matching expression.
(3) It is not case-insensitive unless binary is used (unlike Boolean text search).
(4) compared with like, the difference search will be sorted according to the good degree of matching, the sorting is related to many factors, such as match frequency, occurrence position and so on.
#全文本搜索在where子句中
SELECT Note_text from Productnotes WHERE MATCH (note_text) against (' World ')
#全文本搜索在select子句中
SELECT Note_text, MATCH (note_text) against (' world ') as rank from productnotes
3, Query extension: Scan two times, the steps are: (1) First basic full-text search, find the matching line (2) in the matching row to find useful words (3) Again full-text search, not only match the original condition, but also match the found useful words
SELECT Note_text from Productnotes WHERE MATCH (note_text) against ("World" with QUERY expansion)
4. Boolean Text Search
(1) More accurate, you can specify the words to be criticized, the words to be excluded, the order of sorts, the expression grouping, and so on; The following example is part of.
(2) No Fulltext can also use Boolean but too slow
(3) The returned rows are not sorted in descending order of rank (but can see the score values)
(4) 50% rule is invalid (the rule is shown later)
#含world不含apple
SELECT Note_text from Productnotes WHERE MATCH (note_text) against (' World-apple ' in Boolean MODE)
#world, hello with one can
SELECT Note_text from Productnotes WHERE MATCH (note_text) against ("World Hello" in Boolean MODE)
#同时含world和hello
SELECT Note_text from Productnotes WHERE MATCH (note_text) against (' +world +hello ' in Boolean MODE)
#world对排序影响更大, Hello has less effect on sorting
SELECT Note_text from Productnotes WHERE MATCH (note_text) against (' >world #hello World as a phrase match
SELECT Note_text from Productnotes WHERE MATCH (note_text) against (' Hello World ' ' in Boolean MODE)
#以hello开头的单词
SELECT Note_text from Productnotes WHERE MATCH (note_text) against (' hello* ' in Boolean MODE)

Precautions
1, MyISAM support, InnoDB temporarily do not support (the future version is not necessarily)
2, the full text search is to index the word, not the whole line of data: Languages that do not have word separators (such as Japanese and Chinese) are very strange, and the short words are ignored and not indexed (the short words are defined by default as <=3 words).
3. MySQL has a built-in list of unused words that are ignored at index time. Note that some of the most commonly used words are ignored, such as Hello, this, and so on.
4, 50% rule: If a word appears in more than 50% of the line, as a non-word ignored; not for Boolean mode.
5. Single quotes in the word are ignored, such as the "T" index is dont.
6, MySQL temporarily does not support near search, later version may support.

14. Views "Usually related to multiple table queries"
What is a view
The view is a virtual table
A view is a facility for viewing data stored elsewhere; The view looks like a table, which is actually the wrapper for the MySQL SELECT statement. The view does not contain any columns and data that should be in the table, and contains an SQL query.
Note: In select, the view is used as a table, not a special description, similar in usage.
Why use views
1. Simplify complex SQL operations: Filters, formatting, calculated fields, etc. can be implemented in a view (table)
2. Protect data: You can grant users access to specific parts of a table rather than the entire table
Example: Productcustomers is like a table with 2 columns (cust_name,prod_id)

#不使用视图select Cust_namefrom customers, orders, orderitemswhere customers.cust_id = Orders.cust_idand orderitems.order_ num = Orders.order_numand orderitems.prod_id = ' $ '; #创建视图CREATE VIEW productcustomers asselect cust_name, Prod_idfrom C Ustomers, orders, orderitemswhere customers.cust_id = Orders.cust_idand Orderitems.order_num = orders.order_num# Use view query Select Cust_name from Productcustomers WHERE prod_id= ' $ ' #查看视图SHOW CREATE view productcustomers# Delete views Drop view productcustomers; #修改视图CREATE or REPLACE VIEW ...

Views can be reused

When you create a view, you should not write the query criteria too dead, so it is not convenient to reuse, such as the above written, can not be reused.
CREATE VIEW Productcustomers as
Select Cust_name, prod_id
From customers, orders, OrderItems
where customers.cust_id = orders.cust_id
and Orderitems.order_num = Orders.order_num
and orderitems.prod_id = ' 2000 '
Update view
In general, the view can be updated; updates include Insert/update/delete. Updates are made against the data of the base table, and cannot be updated if MySQL cannot determine which base data to update.
You cannot update a view if it contains the following actions: Grouping, joins, subqueries, and, aggregate functions, DISTINCT, export (computed) columns.
Views are typically used for queries and are less useful for updating.
Precautions
1. The view may contain complex SQL statements, and the view looks simple; therefore, for applications that contain more views, pay attention to test performance.
2. The view name is unique and cannot be associated with another view or table.
3. Views can be nested.
4. The order by can be used in the view, but if the SELECT statement that retrieves data from the view also contains an order by, the order by in the view is overwritten.
5. Views cannot be indexed, and cannot have associated triggers and default values.
6. If there is a WHERE clause in the Select and view, the two sets of clauses are automatically combined.

15. Cursors
What is a cursor
A cursor is similar to an iterator that acts on the result of a select query.
With the SELECT statement, you can only get all the rows of the result set, while using a cursor to locate the first row, the next row, or the first n rows, or you can process the data one row at a time.
Typical examples

drop procedure if exists processorders; CREATE PROCEDURE processorders () begindeclare i int;declare o INT; #定义/declaration cursor DECLARE ordernumbers Cursorforselect order_num from orders; #打开游标OPEN ordernumbers; SET I=1; While I<3 Dofetch ordernumbers to o;insert into Ordercopy VALUES (o); SET i = i + 1; END while; #关闭游标CLOSE ordernumbers; END;

Description

1, the cursor must be declared before use, the declaration does not retrieve data, but only defines the SELECT statement to use. Note that the DECLARE statement has strict order requirements: The local variable precedes the cursor, and the cursor precedes the handle.
2, each use, must first open; But the statement once is enough. The cursor needs to be closed to free internal resources, but encountering end automatically shuts down.
3. The FETCH statement specifies which cursor (which column) to use, and where the data is stored, and moves the inner row pointer in the cursor forward.
Precautions
1. The MySQL cursor is currently (5.6) only available for stored procedures/functions.
2. Can cursors be used for multiple columns? Of course.

16, Sql_no_cache
(1) The real function of Sql_no_cache is to disallow caching of query results, but does not imply that the cache is not returned to query as a result.
(2) Reset query cache can clean up the cache, or modify the table to clean up the relevant cache.

mysql1-Basic Knowledge point 2-Query