MaiaDB 查询表(六)
2016-12-13 本文已影响20人
simuty
第一部分 基本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子句。
参考:
更多精彩内容请关注“IT实战联盟”哦~~~
IT实战联盟.jpg
