1>返回指定表的所有数据；
SELECT * FROM tbl_name;
2>返回指定表的特定字段的所有数据；
SELECT col_name1, col_name2, … FROM tbl_name;
显示时，字段可以显示为别名（字段别名）；
“col_name AS col_alias”，别名”col_alias”无需事先定义，只是作为临时显示效果而已；
3>指定字段，显示符合条件的数据；
SELECT col_name1, col_name2, … FROM tbl_name WHERE clause;
WHERE clause：指明条件；
SELECT语法结构：
=======================
SELECT
select_expr [, select_expr …]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], … [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], …]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
>>WHERE子句：指明过滤条件以实现“选择”的功能；
——————————————
算术操作符：+, -, *, /, %
比较操作符：=, !=, <>, <=>, >, >=, <, <=
BETWEEN min_num AND max_num 前面指定字段名称，查找介于最小数值与最大数值之间的数据；
IN (element1, element2, …)
IS NULL 前面指定字段名称，查找空值的数据；
IS NOT NULL 前面指定字段名称，查找非空值的数据；
LIKE: 前面指定字段名称，模糊匹配查找数据；
%: 任意长度的任意字符
_： 任意单个字符
RLIKE 前面指定字段名称，支持正则表达式；
逻辑操作符：NOT,AND,OR
>> GROUP BY：根据指定的条件把查询结果进行”分组”，以用于做“聚合”运算：avg(), max(), min(), count(), sum()；
>> HAVING： 对分组聚合运算后的结果指定过滤条件；
>> ORDER BY：根据指定的字段对查询结果进行排序，升序为ASC，降序为DESC；默认是升序排列；
>> LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制；
图示SELECT执行条件的次序
=============================
select语句的从句分析顺序：from(过滤表)–>where(过滤行)–>group by(分组)–>having(分组过滤)–>order by(排序)–
>select(选取字段)–>limit(查询限制)–>最终结果；
举例：
MariaDB [firstdb]> select * from shediao; +-----------+-----------------+--------+-------------+-----------------+------------------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+-----------------+--------+-------------+-----------------+------------------+-------+ | 1 | Huang YaoSHi | M | Tao Hua Dao | Tao Hua Dao | Dong Xue | 8820 | | 2 | ou yang feng | M | xi yu | bai tuo shan | xi du | 8850 | | 3 | duan zhi xin | M | da li | di huang | nan di | 8810 | | 4 | hong qi | M | zhong yuan | gai bang | bei gai | 8855 | | 5 | wang chong yang | M | zhong yuan | quan zheng jiao | zhong sheng tong | 9500 | | 6 | Guo Jing | M | NULL | NULL | NULL | 9800 | | 7 | Huang Rong | F | NULL | NULL | NULL | 8688 | +-----------+-----------------+--------+-------------+-----------------+------------------+-------+ 7 rows in set (0.00 sec)
WHERE字句举例：
—————————————
MariaDB [firstdb]> select * from shediao where level<9000; +-----------+--------------+--------+-------------+--------------+----------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+--------------+--------+-------------+--------------+----------+-------+ | 1 | Huang YaoSHi | M | Tao Hua Dao | Tao Hua Dao | Dong Xue | 8820 | | 2 | ou yang feng | M | xi yu | bai tuo shan | xi du | 8850 | | 3 | duan zhi xin | M | da li | di huang | nan di | 8810 | | 4 | hong qi | M | zhong yuan | gai bang | bei gai | 8855 | | 7 | Huang Rong | F | NULL | NULL | NULL | 8688 | +-----------+--------------+--------+-------------+--------------+----------+-------+ 5 rows in set (0.00 sec) MariaDB [firstdb]> select * from shediao where level>=8855; +-----------+-----------------+--------+------------+-----------------+------------------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+-----------------+--------+------------+-----------------+------------------+-------+ | 4 | hong qi | M | zhong yuan | gai bang | bei gai | 8855 | | 5 | wang chong yang | M | zhong yuan | quan zheng jiao | zhong sheng tong | 9500 | | 6 | Guo Jing | M | NULL | NULL | NULL | 9800 | +-----------+-----------------+--------+------------+-----------------+------------------+-------+ 3 rows in set (0.00 sec) MariaDB [firstdb]> select * from shediao where name='guo jing'; +-----------+----------+--------+------+-------+-------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+----------+--------+------+-------+-------+-------+ | 6 | Guo Jing | M | NULL | NULL | NULL | 9800 | +-----------+----------+--------+------+-------+-------+-------+ 1 row in set (0.00 sec) MariaDB [firstdb]> select * from shediao where level between 7000 and 9000; +-----------+--------------+--------+-------------+--------------+----------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+--------------+--------+-------------+--------------+----------+-------+ | 1 | Huang YaoSHi | M | Tao Hua Dao | Tao Hua Dao | Dong Xue | 8820 | | 2 | ou yang feng | M | xi yu | bai tuo shan | xi du | 8850 | | 3 | duan zhi xin | M | da li | di huang | nan di | 8810 | | 4 | hong qi | M | zhong yuan | gai bang | bei gai | 8855 | | 7 | Huang Rong | F | NULL | NULL | NULL | 8688 | +-----------+--------------+--------+-------------+--------------+----------+-------+ 5 rows in set (0.00 sec)
GROUP BY分组举例：
—————————
MariaDB [firstdb]> SELECT * FROM shediao WHERE level BETWEEN 7000 AND 9000 GROUP BY level DESC; +-----------+--------------+--------+-------------+--------------+----------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+--------------+--------+-------------+--------------+----------+-------+ | 4 | hong qi | M | zhong yuan | gai bang | bei gai | 8855 | | 2 | ou yang feng | M | xi yu | bai tuo shan | xi du | 8850 | | 1 | Huang YaoSHi | M | Tao Hua Dao | Tao Hua Dao | Dong Xue | 8820 | | 3 | duan zhi xin | M | da li | di huang | nan di | 8810 | | 7 | Huang Rong | F | NULL | NULL | NULL | 8688 | +-----------+--------------+--------+-------------+--------------+----------+-------+ 5 rows in set (0.00 sec)
ORDER BY排序举例：
—————————–
MariaDB [firstdb]> SELECT * FROM shediao WHERE level BETWEEN 7000 AND 9000 ORDER BY level DESC; +-----------+--------------+--------+-------------+--------------+----------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+--------------+--------+-------------+--------------+----------+-------+ | 4 | hong qi | M | zhong yuan | gai bang | bei gai | 8855 | | 2 | ou yang feng | M | xi yu | bai tuo shan | xi du | 8850 | | 1 | Huang YaoSHi | M | Tao Hua Dao | Tao Hua Dao | Dong Xue | 8820 | | 3 | duan zhi xin | M | da li | di huang | nan di | 8810 | | 7 | Huang Rong | F | NULL | NULL | NULL | 8688 | +-----------+--------------+--------+-------------+--------------+----------+-------+ 5 rows in set (0.00 sec)
LIMIT限制行显示举例：
——————————
MariaDB [firstdb]> SELECT * FROM shediao WHERE level BETWEEN 7000 AND 9000 ORDER BY level DESC LIMIT 3; +-----------+--------------+--------+-------------+--------------+----------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+--------------+--------+-------------+--------------+----------+-------+ | 4 | hong qi | M | zhong yuan | gai bang | bei gai | 8855 | | 2 | ou yang feng | M | xi yu | bai tuo shan | xi du | 8850 | | 1 | Huang YaoSHi | M | Tao Hua Dao | Tao Hua Dao | Dong Xue | 8820 | +-----------+--------------+--------+-------------+--------------+----------+-------+ 3 rows in set (0.00 sec) LIMIT m,n ： 'm'表示偏移量，第一行偏移量为'0',表示显示'm+1'所在的行，以及'm+1'往下'n'行； MariaDB [firstdb]> SELECT * FROM shediao ORDER BY name ; +-----------+-----------------+--------+-------------+-----------------+------------------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+-----------------+--------+-------------+-----------------+------------------+-------+ | 3 | duan zhi xin | M | da li | di huang | nan di | 8810 | | 6 | Guo Jing | M | NULL | NULL | NULL | 9800 | | 4 | hong qi | M | zhong yuan | gai bang | bei gai | 8855 | | 7 | Huang Rong | F | NULL | NULL | NULL | 8688 | | 1 | Huang YaoSHi | M | Tao Hua Dao | Tao Hua Dao | Dong Xue | 8820 | | 2 | ou yang feng | M | xi yu | bai tuo shan | xi du | 8850 | | 5 | wang chong yang | M | zhong yuan | quan zheng jiao | zhong sheng tong | 9500 | +-----------+-----------------+--------+-------------+-----------------+------------------+-------+ 7 rows in set (0.00 sec) MariaDB [firstdb]> SELECT * FROM shediao ORDER BY name LIMIT 3,2; +-----------+--------------+--------+-------------+-------------+----------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+--------------+--------+-------------+-------------+----------+-------+ | 7 | Huang Rong | F | NULL | NULL | NULL | 8688 | | 1 | Huang YaoSHi | M | Tao Hua Dao | Tao Hua Dao | Dong Xue | 8820 | +-----------+--------------+--------+-------------+-------------+----------+-------+ 2 rows in set (0.00 sec) MariaDB [firstdb]> SELECT * FROM shediao GROUP BY level DESC LIMIT 1; +-----------+----------+--------+------+-------+-------+-------+ | jianghuid | name | gender | area | arena | title | level | +-----------+----------+--------+------+-------+-------+-------+ | 6 | Guo Jing | M | NULL | NULL | NULL | 9800 | +-----------+----------+--------+------+-------+-------+-------+ 1 row in set (0.00 sec)
From MariaDB 10.3.2, ORDER BY and LIMIT can be used in a multi-table update:
CREATE TABLE warehouse (product_id INT, qty INT);
INSERT INTO warehouse VALUES (1,100),(2,100),(3,100),(4,100);
CREATE TABLE store (product_id INT, qty INT);
INSERT INTO store VALUES (1,5),(2,5),(3,5),(4,5);
UPDATE warehouse,store SET warehouse.qty = warehouse.qty-2, store.qty = store.qty+2
WHERE (warehouse.product_id = store.product_id AND store.product_id >= 1)
ORDER BY store.product_id DESC LIMIT 2;
SELECT * FROM warehouse;
+------------+------+
| product_id | qty |
+------------+------+
| 1 | 100 |
| 2 | 100 |
| 3 | 98 |
| 4 | 98 |
+------------+------+
SELECT * FROM store;
+------------+------+
| product_id | qty |
+------------+------+
| 1 | 5 |
| 2 | 5 |
| 3 | 7 |
| 4 | 7 |
+------------+------+
CREATE TABLE plays (name VARCHAR(16), plays INT, wins INT);
INSERT INTO plays VALUES
("John", 20, 5),
("Robert", 22, 8),
("Wanda", 32, 8),
("Susan", 17, 3);
SELECT (wins / plays) AS winavg, COUNT(*) FROM plays GROUP BY winavg;
+--------+----------+
| winavg | COUNT(*) |
+--------+----------+
| 0.1765 | 1 |
| 0.2500 | 2 |
| 0.3636 | 1 |
+--------+----------+
3 rows in set (0.00 sec)
SELECT (wins / plays) AS winavg, AVG(plays) FROM plays GROUP BY winavg;
+--------+------------+
| winavg | AVG(plays) |
+--------+------------+
| 0.1765 | 17.0000 |
| 0.2500 | 26.0000 |
| 0.3636 | 22.0000 |
+--------+------------+
3 rows in set (0.00 sec)
From MariaDB 10.3.3, LIMIT can be used with GROUP_CONCAT, so, for example, given the following table:
CREATE TABLE d (dd DATE, cc INT);
INSERT INTO d VALUES ('2017-01-01',1);
INSERT INTO d VALUES ('2017-01-02',2);
INSERT INTO d VALUES ('2017-01-04',3);
SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) FROM d;
+-------------------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |
+-------------------------------------------------------------+
| 2017-01-04:3 |
+-------------------------------------------------------------+