MariaDB（MySQL）的常用命令2 【对检索的数据进行排序】【过滤数据】【高级数据过滤】

第5章 对检索的数据进行排序

数据排列

mysql> SELECT * FROM students;
+----+----------+--------+-----+-------------+
| id | name     | sex    | age | tel         |
+----+----------+--------+-----+-------------+
|  2 | ??       | mela   |  27 | 15766699975 |
|  3 | ??       | femela |  18 | 15766699914 |
|  5 | xiaoming | femela |  18 | 15766699912 |
|  6 | xiaoming | femela |  18 | 15766699901 |
|  7 | xiaoming | femela |  19 | 15766699900 |
+----+----------+--------+-----+-------------+
5 rows in set (0.00 sec)

对students 表中的age字段，进行排序(从小到大)(只显示age字段)

mysql> SELECT age FROM students ORDER BY age;
+-----+
| age |
+-----+
|  18 |
|  18 |
|  18 |
|  19 |
|  27 |
+-----+
5 rows in set (0.00 sec)
 

当然你也可以

SELECT * FROM students ORDER BY age;

多行为 标准排列(下面是 先以name排序，若name一样再用age )

SELECT id,name,age FROM students ORDER BY name,age;
 

指定排序方向 从小到大(ASC 默认可以省略) OR 从大到小 (DESC）

SELECT * FROM students ORDER BY age DESC;
 
 
# 先按照age从大到小排序，若age相同则用name排序。
SELECT * FROM students ORDER BY age DESC, name;

提示：区分大小写， 和排序次序。
字典排序中， 默认大小写一样

---

第6章 过滤数据

where子句

#显示students中 age=18的人的所有信息
SELECT * FROM students WHERE age = 18;
 
 

where子句操作符

=====================================================
操作符                 说明
 
=                      相等
<>                     不等于
!=                     不等于
<                      小于
<=                     小于等于
>                      大于
>=                     大于等于
BETWEEN                在2个特定值之间
====================================================

 
# age 小于20
SELECT * FROM students WHERE age<20;
 
 
# <> 和 != 都是不等于 的意思
SELECT * FROM students WHERE age <> 18;
SELECT * FROM students WHERE age != 18;
 
# 查找区间值
SELECT * FROM students WHERE age BETWEEN 19 AND 27;
 
# 查找   >18  <27的值
SELECT * FROM students WHERE age > 18 AND age < 27;
 
# 查找出为空的值
SELECT * FROM students WHERE age IS NULL;
 

---

第7章 高级数据过滤

• 组合where子句

  • AND操作符
    SELECT * FROM students WHERE age > 18 AND age < 27;

  • OR操作符
    SELECT * FROM students WHERE age = 18 OR age = 27;

  -理解优先级顺序

      //AND > OR
      `SELECT * FROM students WHERE id = 1 OR id = 2  AND age >= 27;`
   
      //为了更加清晰，用括号表示
      `SELECT * FROM students WHERE (id = 1 OR id = 2)  AND age >= 27;`

• IN操作符(用来指定一组条件，只要匹配任意 一个条件即可。 )

  //tel（电话号码）为15711111111 和15700000000 的数据全都合格
  SELECT * FROM students WHERE tel IN (15711111111, 15700000000) ORDER BY age;

  //与上面结果一样
  SELECT * FROM students WHERE tel=15711111111 OR tel=15700000000 ORDER BY age;

• NOT操作符

  //显示 age 不是18 ，不是27 。 的数据（按照id默认（从小到大）排序）
  SELECT * FROM students WHERE age NOT IN (18, 27) ORDER BY id;

---

第17章 创建联合查询

• UNION 不同table 中，重读的数据不会累加统计

SELECT COUNT(*) FROM entry_record WHERE entry_record.time_stamp BETWEEN 1530432000 AND 1530858872  
UNION 
SELECT COUNT(*) FROM entry_record_upload WHERE entry_record_upload.time_stamp BETWEEN 1530432000 AND 1530858872

• UNION ALL 不同table 中，重读的数据，累加统计

SELECT COUNT(*) FROM entry_record WHERE entry_record.time_stamp BETWEEN 1530432000 AND 1530858872  
UNION ALL
SELECT COUNT(*) FROM entry_record_upload WHERE entry_record_upload.time_stamp BETWEEN 1530432000 AND 1530858872

返回2个值， 再Poco 中返回vector 类型对象，

仔细想想 好像还真的没有比这更好的，数量可以不限制，因为Poco DB 中返回数据集，都是vector<*>