MySQL Optimization--Query

Reprint Please specify source: http://blog.csdn.net/l1028386804/article/details/46580589

Basic syntax form for select in MySQL

Select Property List from table name and view list [where conditional expression][group by property name [having conditional expression]][order by property name [Asc|desc]][limit <offset>,row Count]

Description

WHERE clause: query according to the criteria specified by the conditional expression.

GROUP BY clause: GROUP by the field specified by the property name.

HAVING clause: A GROUP BY can have clause that can be output only if the conditions specified in the conditional expression are met.

The GROUP BY clause is typically used with aggregate functions such as count (), sum ().

ORDER BY clause: Sort by the field specified by the property name. The sort method is indicated by the "ASC" and "desc" two parameters, which are sorted by "ASC" by default, ascending.

Build a Test table

Create a test table

CREATE TABLE Fruits (  f_id char) is not NULL,  s_id INT is not null,  f_name  CHAR (255) is not  null,  f_price DECIMAL (8,2) is not NULL,  PRIMARY KEY (f_id))

Inserting test data

INSERT into Fruits (f_id,s_id,f_name,f_price) VALUES (' A1 ', 101, ' Apple ', 5.2), (' B1 ', 102, ' BlackBerry ', 5.2), (' BS1 ', 105, ' Orange ', 5.2), (' BS2 ', 103, ' melon ', 5.2), (' T1 ', 106, ' banana ', 5.2);

Querying the data for the F_ID field using the SELECT statement

SELECT f_id,f_name from Fruits

NOTE: SQL statements in MySQL are case-insensitive, so the select and select functions are the same

Common queries

SELECT * from Fruitsselect f_id,f_name to fruits where f_price >5.1select f_id,f_name from Fruits where s_id in (101,1 () Select F_id,f_name from fruits where s_id not in (101,102) Select F_id,f_name from fruits where f_price between 2 and 10S Elect f_id,f_name from Fruits WHERE f_price not  between 2 and 10

Character matching query with like

1, percent semicolon wildcard "%", matching any length of characters, even including 0 characters

SELECT f_id,f_name from Fruits WHERE f_name like ' b%y '

2, underline the wildcard character "_", can only match any one character at a time

The following statement has four underscores

SELECT f_id,f_name from Fruits WHERE f_name like ' ____n '

Querying null values

CREATE TABLE Customers (  c_id INT not  null auto_increment,  c_name char (+) NOT NULL,  c_city  char (50 )   NULL,  PRIMARY KEY (c_id)) INSERT into Customers (c_name,c_city) VALUES (' liming ', ' China '), (' Hongfang ', NULL) SELECT * FROM Customers WHERE c_city is NULL

SELECT * FROM Customers WHERE c_city are not NULL

And, or, distinct keywords

GROUP by

SELECT s_id, COUNT (1) as total from Fruits GROUP by s_id

Insert two more records

If you want to see the names of the kinds of fruits each vendor provides, you can use the Group_concat () function in the group by in MySQL.

Displays the values of each field in each group

SELECT S_id,group_concat (f_name) as NAMES from Fruits GROUP by s_id

SQL Server does not have the Group_concat () function, and SQL Server needs to use the XML function to achieve the same effect, MySQL is doing very well

Having: Filtering grouping

Group the data in the fruits table according to s_id, and display group information with a fruit type greater than 1

SELECT s_id, Group_concat (f_name) as NAMES from Fruits GROUP by s_id have COUNT (f_name) >1

Using with rollup in GROUP by

SELECT s_id, COUNT (1) as total from Fruits GROUP by s_id with ROLLUP

Adds the last line, 7 represents the sum of all the values of the total column

Note: When using rollup, you cannot use the ORDER BY clause to sort results at the same time, that is, rollup and order by are mutually exclusive!

Limit limits the number of query results

The TOP keyword is used in SQL Server, and the LIMIT keyword is used in MySQL

LIMIT[ position offset ], number of rows

The first position offset parameter indicates the line from which MySQL is displayed, and is an optional parameter if the position offset is not specified

Will start with the first record in the table (the position offset of the first record is 0, the position offset for the second day of the record is 1 ...). And so on

The second parameter, number of rows, indicates the number of record bars returned

SELECT * from Fruits

SELECT *  from 4,3

The above results return 3 records from the 5th record line (since 0 starts)

Note: You can use the limit 4 OFFSET 3 in MYSQL5.6, which means to get 3 records starting from line 5th, and limit4,3 The returned result is the same

Sub-query

The subquery feature is introduced from MYSQL4.1 .

Inserting test data

The Any keyword is followed by a comparison operator that returns true if any value returned from the subquery is more true

Returns all num2 columns of the TBL2 table, and then compares the values of NUM1 in tbl1 with them, as long as any value greater than num2 is the result of matching the query criteria

Select Num1 from Tbl1 WHERE num1>any (select num2 from TBL2)

The all keyword is followed by a comparison operator that returns true if all values returned from the subquery are true

Select Num1 from Tbl1 WHERE num1>all (select num2 from TBL2)

Merging queries

With the Union keyword, two queries must have the same number of columns and data types when merging results.

Separate SELECT statements using the union or UNION ALL keyword

UNION: Delete duplicate records when executing, all returned rows are unique

UNION All: Do not delete duplicate rows and do not automatically sort results

Select S_id,f_name,f_price from    fruitswhere f_price<9.0union SELECT s_id,f_name,f_pricefrom fruitswhere s_id in (101,103)

The first query F_price less than 9.0 records query, the second query s_id 101 and 103 records query processing

Because some records in F_price less than 9.0 record s_id are 102, 105, 106, these results will not be removed will be merged with the second query

So the final result will be a record of s_id 102, 105, 106.

Regular expression Query

Regular expressions are not available in SQL Server , but not only in MySQL, but also in a richer and more functional way.

Use the RegExp keyword in mysql to specify a character-matching pattern for regular expressions

1. Query for records that begin with a specific character or string

The character "^" matches text that begins with a specific character or string

SELECT * from Fruits WHERE f_name REGEXP ' ^b '

Returns a record of the F_name field beginning with B

2. Query for records ending with a specific character or string

The character "$" matches text that ends with a specific character or string

SELECT * from Fruits WHERE f_name REGEXP ' y$ '

Returns the record of the F_name field ending with Y

3, with the symbol "." To replace any one of the characters in the string

Character "." Match any one character

SELECT * from Fruits WHERE f_name REGEXP ' A.G '

A and G two letters contain a single character, Orange meets the requirements

4. Use "*" and "+" to match multiple characters

The asterisk "*" matches the preceding character any number of times, including 0 times. The plus "+" matches the preceding character at least once

SELECT * from Fruits WHERE f_name REGEXP ' ^ba* '

BlackBerry and Banana Meet the requirements, B begins, a matches any number of times, regardless of the order in which they appear

SELECT * from Fruits WHERE f_name REGEXP ' ^ba+ '

"A +" matches the letter "a" at least once, only banana satisfies the match condition

5. Match the specified string

A regular expression can match a specified string, as long as the string is in the query text, such as to match multiple strings, using the delimiter "|" Between multiple strings. Separated

SELECT * from Fruits WHERE f_name REGEXP ' On|ap '

You can see that the apple, melon, coconut 3 values contain the string "on" and the "AP", which meet the matching criteria

6. Match any one of the specified characters

Square brackets "[]" specify a character set that matches only one of these characters, which is the text you are looking for

SELECT * from Fruits WHERE f_name REGEXP ' [ot] '

square brackets [] can also specify a value collection

SELECT * from Fruits WHERE s_id REGEXP ' [456] '

The s_id field value has 1 of 3 numbers that are match record fields

[456] can also be written as [4-6] the specified set of intervals

7. Match characters other than the specified character

"[^ charset]" matches any character not in the specified collection

SELECT * from Fruits WHERE f_id REGEXP ' [^a-e1-2] '

Returns records that are not at the beginning of the A-E 1-2 letter, for example, A1,B1 These records do not meet the requirements

8. Use {N,} or {n,m} to specify the number of consecutive occurrences of a string

"String {N,}", which represents at least n times the preceding character; "String {n,m}" means that the preceding string matches not less than n times, not more than m times.

SELECT * from Fruits WHERE f_name REGEXP ' b{1,} '

Match at least 1 letters b,blackberry and banana meet the requirements

SELECT * from Fruits WHERE f_name REGEXP ' ba{1,3} '

The "BA" string appears at least once, up to three times, banana this string meets the requirements

MySQL optimization--Query