查询结果过滤

最新推荐文章于 2023-07-31 14:39:40 发布

diebaoyan1530

最新推荐文章于 2023-07-31 14:39:40 发布

阅读量224

点赞数

文章标签： shell 数据库

原文链接：http://www.cnblogs.com/summer2/p/10798313.html

版权

查询结果过滤
sql查询 having 条件;

mysql> select name from user where shell!="/bin/bash";
mysql> select name from user where shell!="/bin/bash" having name="mysql"; 从缓存中读取
+-------+
| name |
+-------+
| mysql |
+-------+

mysql> select name from user
-> where shell!="/bin/bash" and name="mysql"; 处理过程不同，有多少行就得匹配多少次，浪费资源!!!还要再理解一下。
+-------+
| name |
+-------+
| mysql |
+-------+
########################################################################################################
mysql> select avg(uid) from user;
+-----------+
| avg(uid) |
+-----------+
| 1863.9524 |
+-----------+

mysql> select sum(uid) from user;
+----------+
| sum(uid) |
+----------+
| 78286 |
+----------+

mysql> select max(uid) from user;
+----------+
| max(uid) |
+----------+
| 65534 |
+----------+

mysql> select min(uid) from user;
+----------+
| min(uid) |
+----------+
| 0 |
+----------+

mysql> select count(uid) from user;
+------------+
| count(uid) |
+------------+
| 42 |
+------------+

mysql> select count(name) from user where shell!="/bin/bash";
+-------------+
| count(name) |
+-------------+
| 39 |
+-------------+

mysql> select count(name) from user;
+-------------+
| count(name) |
+-------------+
| 42 |
+-------------+

mysql> select min(uid) from user;
mysql> select min(uid) from user where uid<=1000;
+----------+
| min(uid) |
+----------+
| 1 |
+----------+

mysql> select name,uid from user order by uid;
+---------------------+-------+
| name | uid |
+---------------------+-------+
| root | 1 |
| bin | 2 |
| daemon | 3 |
... ...

mysql> select name,uid from user order by uid desc;
+---------------------+-------+
| name | uid |
+---------------------+-------+
| nfsnobody | 65535 |
| bob | 3002 |
| lisi | 1001 |
| polkitd | 1000 |
| libstoragemgmt | 999 |
... ...

mysql> select gid from user;
+-------+
| gid |
+-------+
| -1 |
| 0 |
| 1 |
| 3 |
... ...

mysql> select gid from user group by gid; 分类
+-------+
| gid |
+-------+
| -1 |
| 0 |
| 1 |
| 3 |
| 6 |
... ...

mysql> select shell from user where id>10 and id<=100;
+---------------+
| shell |
+---------------+
| /sbin/nologin |
... ...

mysql> select count(shell) from user where uid>200 and uid<5000; 200<uid<5000的shell的个数
+--------------+
| count(shell) |
+--------------+
| 10 |
+--------------+

mysql> select count(shell),shell from user where uid>200 and uid<5000 group by shell;
+--------------+---------------+
| count(shell) | shell |
+--------------+---------------+
| 2 | /bin/bash |
| 8 | /sbin/nologin |
+--------------+---------------+

mysql> select name from user where id <=20 limit 5;
+--------+
| name |
+--------+
| root |
| bin |
| daemon |
| adm |
| lp |
+--------+
5 rows in set (0.00 sec)

mysql> select name from user where id <=20 limit 1;
+------+
| name |
+------+
| root |
+------+
1 row in set (0.00 sec)

mysql> select name from user where id <=20 limit 3;
+--------+
| name |
+--------+
| root |
| bin |
| daemon |
+--------+

mysql> select name from user where id <=8;
+----------+
| name |
+----------+
| root |
| bin |
| daemon |
| adm |
| lp |
| sync |
| shutdown |
| halt |
+----------+
8 rows in set (0.00 sec)

mysql> select name from user where id <=8 limit 1,3; 第一行默认都是从0开始,id是从0开始算的。N代表从查询结果的第几行开始显示，M代表一共显示几行。从第2行开始，一共显示3行
+--------+
| name |
+--------+
| bin |
| daemon |
| adm |
+--------+

mysql> select name from user where id <=8 limit 1,1; 只看查询结果的第2行。从第2行开始显示，一共显示1行。
+------+
| name |
+------+
| bin |
+------+

mysql> select name from user where id <=8 limit 0,1; 只看查询结果的第1行。从第1行开始显示，一共显示1行。
+------+
| name |
+------+
| root |
+------+

mysql> select name from user where id <=8 limit 1; 如果limit之后只有一个数字的话，代表从第1行开始，显示M行
+------+
| name |
+------+
| root |
+------+

mysql> select name from user where id <=8 limit 0,10;
+----------+
| name |
+----------+
| root |
| bin |
| daemon |
| adm |
| lp |
| sync |
| shutdown |
| halt |
+----------+

mysql> select name from user where id <=8 limit 10;
+----------+
| name |
+----------+
| root |
| bin |
| daemon |
| adm |
| lp |
| sync |
| shutdown |
| halt |
+----------+

mysql> select * from user limit 1,3; 第一行默认都是从0开始
+----+--------+------+----------+------+------+---------+----------+---------------+
| id | name | age | password | uid | gid | comment | homedir | shell |
+----+--------+------+----------+------+------+---------+----------+---------------+
| 2 | bin | 18 | A | 2 | 0 | student | /bin | /sbin/nologin |
| 3 | daemon | 18 | A | 3 | 1 | student | /sbin | /sbin/nologin |
| 4 | adm | 18 | A | 4 | 3 | student | /var/adm | /sbin/nologin |
+----+--------+------+----------+------+------+---------+----------+---------------+

限制查询结果显示行数

mysql> select name,uid from user;
+---------------------+-------+
| name | uid |
+---------------------+-------+
| root | 1 |
| bin | 2 |
| daemon | 3 |
| adm | 4 |
| lp | 5 |
| sync | 6 |
| shutdown | 7 |
| halt | 8 |
| mail | 9 |
| operator | 12 |
| games | 13 |
| ftp | 15 |
| nobody | 100 |
| systemd-network | 193 |
| dbus | 82 |
| polkitd | 1000 |
| libstoragemgmt | 999 |
| rpc | 33 |
| colord | 998 |
| saslauth | 997 |
| abrt | 174 |
| rtkit | 173 |
| radvd | 76 |
| chrony | 996 |
| tss | 60 |
| usbmuxd | 114 |
| geoclue | 995 |
| qemu | 108 |
| rpcuser | 30 |
| nfsnobody | 65535 |
| setroubleshoot | 994 |
| pulse | 172 |
| gdm | 43 |
| gnome-initial-setup | 993 |
| sshd | 75 |
| avahi | 71 |
| postfix | 90 |
| ntp | 39 |
| tcpdump | 73 |
| lisi | 1001 |
| mysql | 28 |
| bob | 3002 |
+---------------------+-------+
42 rows in set (0.00 sec)

mysql> select name,uid from user limit 3;
+--------+------+
| name | uid |
+--------+------+
| root | 1 |
| bin | 2 |
| daemon | 3 |
+--------+------+
3 rows in set (0.00 sec)

mysql> select name,uid from user limit 1,3;
+--------+------+
| name | uid |
+--------+------+
| bin | 2 |
| daemon | 3 |
| adm | 4 |
+--------+------+
3 rows in set (0.00 sec)