MySQL Optimization-query and mysql Optimization

Source: Internet
Author: User

MySQL Optimization-query and mysql Optimization

Reprinted please indicate the source: http://blog.csdn.net/l1028386804/article/details/46580589

Basic select syntax in MySQL

Select attribute list from Table Name and View list [where condition expression] [group by attribute name [having condition expression] [order by attribute name [asc | desc] [limit <offset>, row count]

Note:

Where clause: queries based on the conditions specified by the "conditional expression.

Group by clause: groups fields specified by "attribute name.

Having Clause: A group by clause can have a having clause. Only a group by clause that meets the conditions specified in the conditional expression can be output.

The group by clause is usually used together with Aggregate functions such as count () and sum.

Order by clause: sorts the fields specified by "attribute name. The sorting method is indicated by the "asc" and "desc" parameters, which are sorted by "asc" by default, that is, ascending order.

 

Create a test table

Create test table

CREATE TABLE fruits(  f_id CHAR(10)  NOT NULL,  s_id INT NOT NULL,  f_name  CHAR(255)  NOT NULL,  f_price DECIMAL(8,2) NOT NULL,  PRIMARY KEY(f_id))

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

Use the select statement to query data of the f_id Field

SELECT f_id,f_name FROM fruits

 

Note: SQL statements in MYSQL are case-insensitive, so the select and SELECT functions the same.

Common Query

SELECT * FROM fruitsSELECT f_id,f_name FROM fruits WHERE f_price >5.1SELECT f_id,f_name FROM fruits WHERE s_id IN(101,102)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 10SELECT f_id,f_name FROM fruits WHERE f_price NOT  BETWEEN 2 AND 10

Search for like characters

1. percent wildcard "%", matching any length of characters, or even including zero characters

SELECT f_id,f_name FROM fruits WHERE f_name LIKE 'b%y'


2. The underline wildcard "_" can only match any character at a time

The following statement has four underscores.

SELECT f_id,f_name FROM fruits WHERE f_name LIKE '____n'

 

Query null values

CREATE TABLE customers(  c_id INT  NOT NULL AUTO_INCREMENT,  c_name CHAR(25) 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 IS NOT NULL

 

AND, OR, DISTINCT keyword

SELECT f_id,f_name FROM fruits WHERE f_name LIKE '____n' AND f_id='bs2'SELECT f_id,f_name FROM fruits WHERE f_name LIKE '____n' OR f_id='bs2'SELECT DISTINCT s_id FROM fruits 

GROUP

SELECT s_id ,COUNT(1) AS total FROM fruits GROUP BY s_id

Insert two more records

INSERT INTO fruits(f_id,s_id,f_name,f_price)VALUES('a6',101,'cherry',6),('a8',102,'coconut',7) 

To view the name of the fruit type provided BY each vendor, MYSQL can use the GROUP_CONCAT () function in group,

Display the values of fields in each group

SELECT s_id,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id

SQLSERVER does not have the GROUP_CONCAT () function. To achieve the same effect, SQLSERVER must use the xml function. MYSQL is doing very well in this respect.

Having: filter groups

Groups data in the fruits table based on s_id and displays the group information with the fruit category greater than 1.

SELECT s_id ,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id HAVING COUNT(f_name)>1

Use with rollup in group

SELECT s_id ,COUNT(1) AS total FROM fruits GROUP BY s_id WITH ROLLUP

Adds the last row. 7 indicates the sum of all values in the total column.

Note: When ROLLUP is used, the order by clause cannot be used for result sorting at the same time, that is, ROLLUP and order by are mutually exclusive!

 

Limit the number of query results

In SQLSERVERTOPKeyword, which is used in MYSQLLIMITKeywords

LIMIT [location offset], number of rows

The first "location offset" parameter indicates the row from which MYSQL is displayed. It is an optional parameter. If "location offset" is not specified"

Starting from the first record in the table (the position offset of the first record is 0, and the position offset of the record on the next day is 1 ...... and so on)

The second parameter "number of rows" indicates the number of returned records

SELECT * FROM fruits

SELECT * FROM fruits LIMIT 4,3

The above results return three records starting from 5th record rows (because the number starts from 0 ).

Note: In MYSQL5.6, you can use LIMIT 4 OFFSET 3 to obtain the three records starting from the first row of the record. The results returned by LIMIT 4 and 3 are the same.

Subquery

SubqueryThis feature starts fromMYSQL4.1Start introduction.

Insert Test Data

CREATE TABLE tbl1(num1 INT NOT NULL);CREATE TABLE tbl2(num2 INT NOT NULL)INSERT INTO tbl1 VALUES(1),(4),(13),(27);INSERT INTO tbl2 VALUES(6),(14),(11),(20) 

The ANY keyword is followed by a comparison operator, indicating that TRUE is returned if it is compared with ANY value returned by the subquery.

Return all num2 columns in the tbl2 table, and compare the num1 value in tbl1 with the value. If it is greater than any value in num2, it is the result that meets the query conditions.

SELECT num1 FROM tbl1 WHERE num1>ANY(SELECT num2 FROM tbl2)


The ALL keyword is followed by a comparison operator, indicating that it is TRUE when compared with ALL values returned by the subquery. TRUE is returned.

SELECT num1 FROM tbl1 WHERE num1>ALL(SELECT num2 FROM tbl2)


Merge Query

When the UNION keyword is used, the columns and Data Types of the Two Queries must be the same.

Use the UNION or union all keywords to separate SELECT statements.

UNION: deletes duplicate records during execution. All returned rows are unique.

Union all: duplicate rows are not deleted, and results are not automatically sorted.

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 queries records with f_price less than 9.0, and the second query queries records with s_id 101 and 103.

Because the s_id of some records whose f_price is less than 9.0 is 102, 105, and 106, these results will not be removed and will be merged with the second query.

Therefore, the final result will contain records with s_id 102, 105, and 106.

 

Regular Expression Query

The regular expression is inSQLSERVERIt does not exist in it, but it is not only in MYSQL, but also has rich functions.

Use the REGEXP keyword in MYSQL to specify the character matching mode of the Regular Expression

 

1. query records starting with a specific character or string

The character "^" matches the text starting with a specific character or string

SELECT * FROM fruits WHERE f_name REGEXP '^b'

Returns records starting with B in the f_name field.


2. query records ending with a specific character or string

The character "$" matches the text ending with a specific character or string.

SELECT * FROM fruits WHERE f_name REGEXP 'y$'

Returns the records whose f_name field ends with y.

 

3. Use the symbol "." To replace any character in the string.

The character "." matches any character

SELECT * FROM fruits WHERE f_name REGEXP 'a.g'

A and g contain a single character. orange meets the requirements.

 

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

The asterisk "*" matches the preceding characters multiple times, including 0. The plus sign "+" matches the previous character at least once.

SELECT * FROM fruits WHERE f_name REGEXP '^ba*'

Blackberry and banana meet the requirements, starting with B, a matches any number of times, no matter where the order appears

SELECT * FROM fruits WHERE f_name REGEXP '^ba+'

"A +" matches the letter "a" at least once. Only banana meets the matching conditions.

5. Match the specified string

A regular expression can match a specified string, as long as the string is in the query text. To match multiple strings, use the separator "|" to separate multiple strings.

SELECT * FROM fruits WHERE f_name REGEXP 'on|ap'

The three values of apple, melon, and coconut contain strings "on" and "ap", meeting the matching conditions.

6. match any of the specified characters

The square brackets "[]" specify a character set that matches only one of the characters, that is, the searched text.

SELECT * FROM fruits WHERE f_name REGEXP '[ot]'

Square brackets [] can also specify a numerical set

SELECT * FROM fruits WHERE s_id REGEXP '[456]'

One of the three numbers in the s_id field is the matching record field.

[456] can also be written as [4-6] to specify the set interval

7. Match characters other than the specified character

"[^ Character Set combination]" matches any character that is not in the specified set

SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]'

 

Returns records starting with 1-2 letters a-e. For example, a1 and b1 do not meet the requirements.

 

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

"String {n ,}" indicates that the string matches the previous character at least n times;" string {n, m} "indicates that the string matches the previous character no less than n times, no more than m times.

 SELECT * FROM fruits WHERE f_name REGEXP 'b{1,}'

Match at least 1 letter B, and both blackberry and banana meet the requirements.

 SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}'

The "ba" string can appear at least once and at most three times. The banana string meets the requirements.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.