取出一个表的行数
select count(*) from table；
查询限制结果
取最开始的5行
select id from table limit 5;
取从第5行起的3行数据
mysql> select id from zt_user limit 5 offset 3;
+----+
| id |
+----+
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
5 rows in set (0.00 sec)
第一个数字是检索的行数，第二个数字是指从哪开始，行数从0开始
排序
mysql> select last from zt_user ORDER By last;
+------------+
| last |
+------------+
| 1338863860 |
| 1338864116 |
| 1338864187 |
| 1338865125 |
| 1338865450 |
| 1338865739 |
| 1338865842 |
| 1338865876 |
| 1338866083 |
| 1520176448 |
+------------+
10 rows in set (0.00 sec)
- 注意： order by 必须是最后一条子句，但是有例外，比如在和 limit 等结果限制在一起使用的时候 limit就必须在最后。 order by 要排序的字段和要检索的字段并不一定要相同，并不一定那么严格
多列排序
mysql> select sex,name from zyd1_user order by uid,sex limit 6 ;
+-----+--------+
| sex | name |
+-----+--------+
| 0 | admin |
| 0 | 东泽 |
| 0 | 王凯 |
| 0 | 东泽 |
| 0 | 冯璐 |
| 0 | 东泽 |
+-----+--------+
6 rows in set (0.02 sec)
按列位置排序
mysql> select sex,user_id from zyd1_user order by 1,2 limit 6 ;
+-----+---------+
| sex | user_id |
+-----+---------+
| 0 | 1 |
| 0 | 629 |
| 0 | 663 |
| 0 | 908 |
| 0 | 923 |
| 0 | 1570 |
+-----+---------+
6 rows in set (0.01 sec)
order by 1，2 中，1指的是sex字段，2指的是user_id字段，指的是相对位置 此写法有好有坏，个人拿捏
降序排列
之前的都是升序
mysql> select sex,user_id from zyd1_user order by 1,2 desc limit 6 ;
+-----+----------+
| sex | user_id |
+-----+----------+
| 0 | 10015635 |
| 0 | 10015634 |
| 0 | 10015459 |
| 0 | 10015458 |
| 0 | 10015457 |
| 0 | 10015456 |
+-----+----------+
6 rows in set (0.01 sec)
单个字段降序
mysql> select reg_time,sex from zyd1_user order by reg_time desc,2 limit 10 ;
+------------+-----+
| reg_time | sex |
+------------+-----+
| 1515548091 | 2 |
| 1515500354 | 2 |
| 1515500161 | 1 |
| 1515500140 | 1 |
| 1515499971 | 2 |
| 1515499806 | 2 |
| 1515499767 | 1 |
| 1515499523 | 2 |
| 1515497228 | 1 |
| 1515496413 | 1 |
+------------+-----+
10 rows in set (0.00 sec)
一个简单where查询
mysql> select reg_time from zyd1_user where 1 = sex limit 4;
+------------+
| reg_time |
+------------+
| 1460822400 |
| 1468425600 |
| 1445943912 |
| 1445944562 |
+------------+
4 rows in set (0.00 sec)
注意 where 和limit 一起实用的话，where需在limit前面
where操作符
mysql> select reg_time from zyd1_user where role_id != 900 limit 5;
+------------+
| reg_time |
+------------+
| 0 |
| 1460822400 |
| 1460822400 |
| 1468425600 |
| 1445943912 |
+------------+
5 rows in set (0.00 sec)
操作符总结在这里
!> 不大于
< 小于
BETWEEN 在指定的两个值之间
= 等于
<= 小于等于
NULL 为NULL值
!< 不小于
<> 不等于
> 大于
>= 大于等于
大小示范
<> 和 != 在大多数情况下是通用的，但是也存在某些不兼容的情况
mysql> select reg_time from zyd1_user where sex != 1 limit 5;
+------------+
| reg_time |
+------------+
| 0 |
| 1460822400 |
| 1451031088 |
| 1451031247 |
| 1451036638 |
+------------+
5 rows in set (0.00 sec)
mysql> select reg_time from zyd1_user where sex <> 1 limit 5;
+------------+
| reg_time |
+------------+
| 0 |
| 1460822400 |
| 1451031088 |
| 1451031247 |
| 1451036638 |
+------------+
5 rows in set (0.00 sec)
between示范
mysql> select reg_time from zyd1_user where role_id between 1500 and 1600 limit 5 ;
+------------+
| reg_time |
+------------+
| 1470585600 |
| 1470585600 |
| 1470585600 |
| 1470585600 |
| 1470585600 |
+------------+
5 rows in set (0.00 sec)