sql查询having条件

limit 限制查询结果显示的行数

sql查询limit 数字  ； 显示查询结果的前几行

mysql> select name, shell from user where  uid <=500 limit 5;

+--------+---------------+

| name   | shell         |

+--------+---------------+

| root   | /bin/bash     |

| bin    | /sbin/nologin |

| daemon | /sbin/nologin |

| adm    | /sbin/nologin |

| lp     | /sbin/nologin |

+--------+---------------+

5 rows in set (0.00 sec)

sql查询limit 数字 1，数字2； 显示查询结果的指定范围内的几行

mysql> select name, shell from user where  uid <=500 limit 2,8;

+----------+----------------+

| name     | shell          |

+----------+----------------+

| daemon   | /sbin/nologin  |

| adm      | /sbin/nologin  |

| lp       | /sbin/nologin  |

| sync     | /bin/sync      |

| shutdown | /sbin/shutdown |

| halt     | /sbin/halt     |

| mail     | /sbin/nologin  |

| operator | /sbin/nologin  |

+----------+----------------+

8 rows in set (0.00 sec)

mysql> select name, shell from user where  uid <=500 limit 1,1;

+------+---------------+

| name | shell         | 从第二列查找。显示一列

+------+---------------+

| bin  | /sbin/nologin |

+------+---------------+

1 row in set (0.00 sec)

 

sql 查询 having 条件

mysql> select  name,uid from user where uid>=1000 having name is null;

Empty set (0.00 sec)

 

mysql> select  name,uid from user where uid>=1000 having uid=65534;

+-----------+-------+

| name      | uid   |

+-----------+-------+

| nfsnobody | 65534 |

+-----------+-------+

1 row in set (0.00 sec)

 

mysql> select  name,uid from user where uid>=1000 having name="bob";

+------+------+

| name | uid  |

+------+------+

| bob  | 2000 |

| bob  | 2000 |

| bob  | 2000 |

+------+------+

3 rows in set (0.00 sec)

 

mysql> select  name,uid from user where uid>=1000 and name="bob";  ——这两种的查询结果皆然不同

+------+------+

| name | uid  |

+------+------+

| bob  | 2000 |

| bob  | 2000 |

| bob  | 2000 |

+------+------+

3 rows in set (0.00 sec)

 

sql 查询 group by 字段名；查询分组

mysql> select shell from user group by shell;

+----------------+

| shell          |

+----------------+

| NULL           |

| /bin/          |

| /bin/bash      |

| /bin/false     |

| /bin/kill      |

| /bin/sync      |

| /sbin/halt     |

| /sbin/nologin  |

| /sbin/shutdown |

| hon            |

+----------------+

10 rows in set (0.00 sec)

 

mysql> select distinct shell from user;

+----------------+

| shell          |

+----------------+

| /bin/bash      |

| /sbin/nologin  |

| /bin/sync      |

| /sbin/shutdown |

| /sbin/halt     |

| /bin/false     |

| /bin/          |

| NULL           |

| hon            |

| /bin/kill      |

+----------------+

10 rows in set (0.00 sec)

 

sql 查型 order by  字段名;  排序 排序方式  desc asc 

mysql> select name,uid from user where uid>=10 and uid <=500;

mysql> select name,uid from user where uid>=10 and uid <=500 order by uid desc; ——升序排列

mysql> select name,uid from user where uid>=10 and uid <=500 order by uid asc;——降讯排列