MaiaDB 查询表(六)

第一部分 基本SELECT

1.1 检索单列

> SELECT 列名 FROMM 表名;

1.2 检索多列

> SELECT 列名1, 列名2, 列名3...
    FROM 表名;

1.3 检索所有的列

> SELECT * FROM 表名;

1.4 去除重复列

> SELECT DISTINCT 列名 FROM 表名;

**DISTINCT: **去除重复行

1.5 限制检索数

> SELECT 列名 FROM 表 LIMIT 4;

刷选所用数据中的前4行;

1.6 基本排序

升序: 默认 <ASC>(ascending order);
降序:**DESC**(descending order);

    > SELECT * FROM books ORDER BY book_id;
    +---------+------------------+------------------+------------------+-------------+----------+------+
| book_id | book_name        | book_author_name | publication_date | description | classify | c    |
+---------+------------------+------------------+------------------+-------------+----------+------+
|       1 | 跟我学MariaDB    | NULL             | 2016-12-03       | NULL        | 18       | NULL |
|       2 | NodeJS           | NULL             | 2016-12-02       | NULL        | 18       | NULL |
|       3 | HTTP权威指南     | NULL             | 2002-01-01       | NULL        | 18       | NULL |

> SELECT * FROM books ORDER BY book_id DESC;
+---------+------------------+------------------+------------------+-------------+----------+------+
| book_id | book_name        | book_author_name | publication_date | description | classify | c    |
+---------+------------------+------------------+------------------+-------------+----------+------+
|      22 | 巨人的陨落       | NULL             | 2016-05-01       | NULL        | 11       | NULL |
|      21 | 史记             | NULL             | 1982-11-01       | NULL        | 11       | NULL |
|      20 | 国史大纲         | NULL             | 2013-08-01       | NULL        | 11       | NULL |
|      19 | 人类简史         | NULL             | 2014-11-01       | NULL        | 11       | NULL |

1.7 按列的位置排序

SELECT book_id, book_name, description FROM books ORDER BY 2, 3; 表示先按book_name排序, 然后按照description排序;

第二部分 过滤数据 WHERE

2.1 WHERE 支持条件判断

1. 大于: >;
2. 小于: <;
3. 等于: =;
4. 大于等于: >=;
5. 小于等于: <=;
6. 不等于: !=;

实例: 检测单个值, 可以将WHERE支持的条件判断语句替换;

> SELECT * FROM books WHERE book_id = 1;

2.2 范围检测BETWEEN AND

> SELECT book_id, book_name FROM books WHERE book_id BETWEEN 3 AND 10;
+---------+------------------+
| book_id | book_name        |
+---------+------------------+
|       3 | HTTP权威指南     |
|       4 | 追风筝的人       |
|       5 | 小王子           |
|       6 | 围城             |
|       7 | 活着             |
|       8 | 解忧杂货店       |
|       9 | 简爱             |
|      10 | 平凡的世界       |
+---------+------------------+

2.3 空值检测 NULL: 列 IS NULL;

> SELECT book_id, book_name FROM books WHERE description IS NULL;

2.4 筛选 AND 前后共同满足条件

+---------+------------------+------------------+------------------+-------------+----------+------+
| book_id | book_name        | book_author_name | publication_date | description | classify | c    |
+---------+------------------+------------------+------------------+-------------+----------+------+
      16 | 国富论           | NULL             | 2005-01-01       | NULL        | 6        | NULL |
|      17 | 伟大的博弈       | NULL             | 2005-01-01       | NULL        | 6        | NULL |

> SELECT book_id, book_name FROM books WHERE book_id = 16 AND publication_date = '2005-01-01'; 
+---------+-----------+
| book_id | book_name |
+---------+-----------+
|      16 | 国富论    |
+---------+-----------+
1 row in set (0.01 sec)

2.5 筛选 OR 前后 满足一个条件即可

> SELECT book_id, book_name FROM books WHERE book_id = 16 OR publication_date = '2005-01-01'; 
+---------+-----------------+
| book_id | book_name       |
+---------+-----------------+
|      10 | 平凡的世界      |
|      16 | 国富论          |
|      17 | 伟大的博弈      |
+---------+-----------------+
3 rows in set (0.00 sec)

2.6 AND OR 结合使用

筛选book_id等于** 3 或 10** ,并且在一月出版的书;默认从前往后执行, 当然可以用(..)

其中用到日期函数, 后续完善;

> SELECT * FROM books WHERE (book_id = 3 OR book_id = 10) AND  month(publication_date) = 1;
+---------+------------------+------------------+------------------+-------------+----------+------+
| book_id | book_name        | book_author_name | publication_date | description | classify | c    |
+---------+------------------+------------------+------------------+-------------+----------+------+
|       3 | HTTP权威指南     | NULL             | 2002-01-01       | NULL        | 18       | NULL |
|      10 | 平凡的世界       | NULL             | 2005-01-01       | NULL        | 9        | NULL |
+---------+------------------+------------------+------------------+-------------+----------+------+
2 rows in set (0.00 sec)

2.7 IN字句使用
IN 与 OR 非常类似, 不是指的范围;与BETWEEN区别开;

> SELECT * FROM books WHERE book_id IN (3, 10);
+---------+------------------+------------------+------------------+-------------+----------+------+
| book_id | book_name        | book_author_name | publication_date | description | classify | c    |
+---------+------------------+------------------+------------------+-------------+----------+------+
|       3 | HTTP权威指南     | NULL             | 2002-01-01       | NULL        | 18       | NULL |
|      10 | 平凡的世界       | NULL             | 2005-01-01       | NULL        | 9        | NULL |
+---------+------------------+------------------+------------------+-------------+----------+------+
2 rows in set (0.01 sec)

2.8 NOT字句使用
非, 就是除了括号中的其余的所有;

> SELECT * FROM books WHERE book_id NOT IN (3, 10);

使用IN / NOT, 总的来说

1. 更清楚且更直观;
2. 效率也更高;
2. IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

参考:

MySQL：日期函数、时间函数总结（MySQL 5.X）

更多精彩内容请关注“IT实战联盟”哦~~~

IT实战联盟.jpg