MySQL优化之-查询语句详解

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

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

SELECT f_id,f_name FROM fruits

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

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

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

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

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

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

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

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

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

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

LIMIT[位置偏移量]，行数

SELECT * FROM fruits

SELECT * FROM fruits LIMIT 4,3

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)

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

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

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)

SELECT * FROM fruits WHERE f_name REGEXP '^b'

SELECT * FROM fruits WHERE f_name REGEXP 'y$'

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

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

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

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

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

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

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

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

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