SQL精进2_简单查询

/ Database / 没有评论 / 763浏览

取出一个表的行数

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)

多列排序

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)