MySQL optimization-detailed explanation of query statements

The basic syntax form of select in MySQL

select 属性列表
from 表名和视图列表
[where 条件表达式]
[group by 属性名[having 条件表达式]]
[order by 属性名[asc|desc]]
[limit <offset>,row count]

Description:

where clause: Query according to the conditions specified by "conditional expression".

group by clause: Group by the field specified by "attribute name".

Having clause: The having clause can only be used with group by. Only those that meet 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: Sort by the field specified by "attribute name". The sorting method is indicated by the two parameters "asc" and "desc". The default is to sort according to "asc", that is, ascending order.

---

Create 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 the data of the f_id field

SELECT f_id,f_name FROM fruits

Note: SQL statements in MYSQL are not case-sensitive, so select and SELECT have the same effect

Common queries

SELECT * FROM fruits

SELECT f_id,f_name FROM fruits WHERE f_price >5.1

SELECT 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 10

SELECT f_id,f_name FROM fruits WHERE f_price NOT  BETWEEN 2 AND 10

---

Character matching query with like

1. The percent wildcard character "%" matches any length of characters, even zero characters

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

2. The underscore wildcard character "_", once Can only match any character

The following statement has four underscores

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

---

##Query for empty 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 keywords

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 BY

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)

If you want to view the names of the types of fruits provided by each supplier, you can use the GROUP_CONCAT() function in GROUP BY in MYSQL,

Display the values ​​of each field 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 needs to use xml Function, MYSQL does a very good job in this regard

having: filter grouping

Group the data in the fruits table according to s_id, and display the grouping information of fruit types 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 by

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

Added the last row, 7 represents the sum of all values ​​in the total column

Note: When using ROLLUP, the ORDER BY clause cannot be used at the same time to sort results, that is, ROLLUP and ORDER BY are mutually exclusive!

---

limit limits the number of query results

is used in SQLSERVER TOP keyword, while in MYSQL, the LIMIT keyword is used

LIMIT[位置偏移量]，行数

The first "position offset" parameter indicates which row MYSQL starts displaying, is an optional parameter. If you do not specify the "position offset"

will start from the first record in the table (the position offset of the first record is 0, and the position of the next day's record The offset is 1...and so on)

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

SELECT * FROM fruits

SELECT * FROM fruits LIMIT 4,3

The above result returns 3 records starting from the 5th record row (because counting starts from 0)

Note: It can be used in MYSQL5.6

LIMIT 4 OFFSET 3, which means to get 3 records starting from the 5th row, and LIMIT4,3 The returned result is the same

---

Sub query

Sub query QueryThis feature was introduced from MYSQL4.1.

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 if TRUE is compared with any value returned by the subquery, TRUE

返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何一个值，即为符合查询条件的结果

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

ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE

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

---

合并查询

使用UNION关键字，合并结果时，两个查询对应的列数和数据类型必须相同。

各个SELECT语句之间使用UNION或UNION ALL关键字分隔

UNION：执行的时候删除重复的记录，所有返回的行都是唯一的

UNION ALL：不删除重复行也不对结果进行自动排序

SELECT s_id,f_name,f_price    
FROM fruits
WHERE f_price<9.0
UNION 
SELECT s_id,f_name,f_price
FROM fruits
WHERE s_id IN (101,103)

第一个查询把f_price小于9.0的记录查询出来，第二个查询把s_id为101和103的记录查询处理

因为f_price小于9.0的记录里有些记录的s_id是102、105、106，这些结果不会被去掉会跟第二个查询进行合并

所以最终的结果会有s_id为102、105、106的记录

---

正则表达式查询

正则表达式在SQLSERVER里面是没有的，但是在MYSQL里不单只有，而且功能也比较丰富

MYSQL中使用REGEXP关键字指定正则表达式的字符匹配模式

1、查询以特定字符或字符串开头的记录

字符“^”匹配以特定字符或者字符串开头的文本

SELECT * FROM fruits WHERE f_name REGEXP '^b'

返回f_name字段以b开头的记录

2、查询以特定字符或字符串结尾的记录

字符“$”匹配以特定字符或者字符串结尾的文本

SELECT * FROM fruits WHERE f_name REGEXP 'y$'

返回f_name字段以y结尾的记录

3、用符号“.”来代替字符串中的任意一个字符

字符“.”匹配任意一个字符

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

a和g两个字母之间包含单个字符，orange符合要求

4、使用“*”和“+”来匹配多个字符

星号“*”匹配前面的字符任意多次，包括0次。加号“+”匹配前面的字符至少一次

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

blackberry和banana符合要求，b开头，a匹配任意多次，不管出现的顺序在哪里

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

 “a+”匹配字母“a”至少一次，只有banana满足匹配条件

5、匹配指定字符串

正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，如要匹配多个字符串，多个字符串之间使用分隔符“|”隔开

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

可以看到apple 、melon 、coconut 3个值中都包含有字符串“on”和“ap”，满足匹配条件

6、匹配指定字符中的任意一个

方括号“[]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本

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

方括号[]还可以指定数值集合

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

s_id字段值中有3个数字中的1个即为匹配记录字段

[456]也可以写成[4-6]即指定集合区间

7、匹配指定字符以外的字符

“[^字符集合]”匹配不在指定集合中的任何字符

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

 

返回开头不在a-e  1-2字母的记录，例如a1，b1这些记录就不符合要求

8、使用{n,} 或者{n,m}来指定字符串连续出现的次数

“字符串{n,}”，表示至少匹配n次前面的字符；“字符串{n,m}”表示匹配前面的字符串不少于n次，

不多于m次。

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

至少匹配1次字母b，blackberry和banana都符合要求

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

“ba”字符串最少出现一次，最多三次，banana这个字符串符合要求

The above is the detailed content of MySQL optimization-detailed explanation of query statements. For more information, please follow other related articles on the PHP Chinese website!