数据库的匹配条件及应用

匹配条件:目录

一丶基本匹配条件
二丶高级匹配条件
三丶操作查询结果

基本匹配条件

1.数值比较

= 等于 >大于 <小于
<= 小于等于 >= 大于等于 !=不等于

mysql> select * from db3.user where  uid=3;
mysql> select * from db3.user where  uid<=3;
mysql> select * from db3.user where  uid!=3;

2.字符比较/匹配空/匹配非空

= 相等 !=不相等 is null 空 is not null 非空

mysql> select * from db3.user where  name is null ;
Empty set (0.00 sec)

mysql> select * from db3.user where  shell is null ;
Empty set (0.00 sec)

mysql> select * from db3.user where  shell is not null ;

3.逻辑匹配

or 逻辑或
and 逻辑与
!或not 逻辑非

mysql> select * from db3.user where  name='root' and uid=0 ;
+----+------+------+----------+------+------+---------+---------+-----------+
| id | name | age  | passwdrd | uid  | gid  | comment | hostdir | shell     |
+----+------+------+----------+------+------+---------+---------+-----------+
|  1 | root |   20 | x        |    0 |    0 | root    | /root   | /bin/bash |
+----+------+------+----------+------+------+---------+---------+-----------+
1 row in set (0.00 sec)

mysql> select * from db3.user where  name='root' or uid=2 ;
+----+--------+------+----------+------+------+---------+---------+---------------+
| id | name   | age  | passwdrd | uid  | gid  | comment | hostdir | shell         |
+----+--------+------+----------+------+------+---------+---------+---------------+
|  1 | root   |   20 | x        |    0 |    0 | root    | /root   | /bin/bash     |
|  3 | daemon |   20 | x        |    2 |    2 | daemon  | /sbin   | /sbin/nologin |
+----+--------+------+----------+------+------+---------+---------+---------------+
2 rows in set (0.01 sec)

4.范围匹配/去重显示

in(值列表) 在…里
not in 不在…里
between数字and数字 在…之间
distinct 字段名 去重显示

//查询uid等于1,3,5的信息
mysql> select * from db3.user where  uid  in (1,3,5)  ;
//查询除uid等于1,3,5之外的信息
mysql> select * from db3.user where  uid not in (1,3,5)  ;
//查询uid在1-5之间的信息 包括1 5 
mysql> select * from db3.user where  uid  between 1 and 5  ;

高级匹配条件

1.模糊查询

用法
where 字段名 like ‘通配符’

_ 表示一个字符
% 表示0~n个字符

//查询所有name
mysql> select name from db3.user ;
//查询四个字的name
mysql> select name from db3.user where name like "____";
//查询以a开头后面两位的name
mysql> select name from db3.user where name like "a__";
//查询以a开头的name
mysql> select name from db3.user where name like "a%";
//查询最少四个字符的name
mysql> select name from db3.user where name like "__%__";

2.正则表达式

用法:
where 字段名 regexp ‘正则表达式’
^ $ . [] * |

//以a开头 或者 以t结尾的name
mysql> select name,uid from db3.user  where name  regexp '^a|t$'
    -> ;
//以数字开头的name
mysql> select name,uid from db3.user  where name  regexp '^[0-9]'
    -> ;

//以字母开头的name
mysql> select name,uid from db3.user  where name  regexp '^[a-z]';
//name包含字母的
mysql> select name,uid from db3.user  where name  regexp '[a-z]';
//名字包含r
mysql> select name,uid from db3.user  where name  regexp '[r]';
//名字至少两位
mysql> select name,uid from db3.user  where name  regexp '..';
//uid至少两位
mysql> select name,uid from db3.user  where uid  regexp '..';
//uid至少一位的
mysql> select name,uid from db3.user  where uid  regexp '.';
//uid是两位的
mysql> select name,uid from db3.user  where uid  regexp '^..$';
//uid是三位的
mysql> select name,uid from db3.user  where uid  regexp '^...$';

3.四则运算

+加法
-减法
*乘法
/除法
% 取余数/求模
() 提取优先级

//查看name,uid 行
mysql> select name,uid from db3.user ;
//在表中加入age 默认为20 设置在name字段后
mysql> alter table db3.user add age tinyint unsigned  default 20 after name;
//把uid,gid行相加 结构显示为zh字段
mysql> select name,uid,gid,  uid+gid zh  from db3.user;
// 2019减root行的age字段 并把结果输出为csnf字段
mysql> select name,age,  2019-age csnf from db3.user  where name = 'root';
//查看uid小于10的name,uid
mysql> select name,uid  from db3.user  where  uid<=10;
//查看uid为双数的字段
mysql> select name,uid  from db3.user  where  uid%2=0;
//把uid小鱼等于10的 加1
mysql> update db3.user  set uid=uid+1  where uid<=10;
Query OK, 9 rows affected (0.37 sec)
Rows matched: 9  Changed: 9  Warnings: 0

//把uid小于等于10的 减一
mysql> update db3.user set uid=uid-1 where uid <= 10;
Query OK, 9 rows affected (0.38 sec)
Rows matched: 9  Changed: 9  Warnings: 0

//查看name为root的行的uid和gid的平均值
mysql> select name,uid,gid,  (uid+gid)/2 pjz from db3.user where name='bin';

操作查询结果

1.聚集函数

用法:select 函数(字段) from 库名.表名 ;
avg (字段名) 统计字段平均值
sum(字段名) 统计字段之和
min(字段名) 统计字段最少值
max(字段值) 统计字段最大值
count(字段值) 统计字段值个数

mysql> select avg(gid) from db3.user;
+----------+
| avg(gid) |
+----------+
| 132.0952 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(gid) from db3.user;
+----------+
| sum(gid) |
+----------+
|     2774 |
+----------+
1 row in set (0.00 sec)

mysql> select min(gid) from db3.user;
+----------+
| min(gid) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select max(gid) from db3.user;
+----------+
| max(gid) |
+----------+
|      998 |
+----------+
1 row in set (0.00 sec)

mysql> select count(gid) from db3.user;
+------------+
| count(gid) |
+------------+
|         21 |
+------------+
1 row in set (0.00 sec)

//查看登录shell不是/bin/bash的人数和

mysql> select count(name) from db3.user  where shell != "/bin/bash"
    -> ;
+-------------+
| count(name) |
+-------------+
|          20 |
+-------------+
1 row in set (0.01 sec)

2.查询结果排序

用法:sql语句 order by 字段名 [asc|desc];
orber by 默认升序
asc/desc 升序/降序

mysql> select name,uid  from db3.user where uid<=100  ;
+----------+------+
| name     | uid  |
+----------+------+
| root     |    0 |
| bin      |    1 |
| daemon   |    2 |
| adm      |    3 |
| lp       |    4 |
| sync     |    5 |
| shutdown |    6 |
| halt     |    7 |
| mail     |    8 |
| operator |   11 |
| games    |   12 |
| ftp      |   14 |
| nobody   |   99 |
| dbus     |   81 |
| tss      |   59 |
| sshd     |   74 |
| tcpdump  |   72 |
| mysql    |   27 |
+----------+------+
18 rows in set (0.00 sec)

mysql> select name,uid  from db3.user where uid<=100  order by  uid;
+----------+------+
| name     | uid  |
+----------+------+
| root     |    0 |
| bin      |    1 |
| daemon   |    2 |
| adm      |    3 |
| lp       |    4 |
| sync     |    5 |
| shutdown |    6 |
| halt     |    7 |
| mail     |    8 |
| operator |   11 |
| games    |   12 |
| ftp      |   14 |
| mysql    |   27 |
| tss      |   59 |
| tcpdump  |   72 |
| sshd     |   74 |
| dbus     |   81 |
| nobody   |   99 |
+----------+------+
18 rows in set (0.00 sec)


mysql> select name,uid  from db3.user where uid<=100  order by  uid desc;
+----------+------+
| name     | uid  |
+----------+------+
| nobody   |   99 |
| dbus     |   81 |
| sshd     |   74 |
| tcpdump  |   72 |
| tss      |   59 |
| mysql    |   27 |
| ftp      |   14 |
| games    |   12 |
| operator |   11 |
| mail     |    8 |
| halt     |    7 |
| shutdown |    6 |
| sync     |    5 |
| lp       |    4 |
| adm      |    3 |
| daemon   |    2 |
| bin      |    1 |
| root     |    0 |
+----------+------+
18 rows in set (0.00 sec)

3.查询结果分组

group by 字段名;

//uid大于等于1000的shell分组
mysql> select shell from db3.user where uid<=1000 group by shell;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /bin/false     |
| /bin/sync      |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/shutdown |
+----------------+
6 rows in set (0.00 sec)
//所有的shell分组
mysql> select shell from db3.user  group by shell;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /bin/false     |
| /bin/sync      |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/shutdown |
+----------------+
6 rows in set (0.00 sec)

//所有shell
mysql> select shell from db3.user  ;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /bin/false     |
+----------------+
21 rows in set (0.00 sec)

4.查询结果过滤

having 条件表达式;

//查询uid小于等于10 的行  
mysql> select * from db3.user where uid<=10;
+----+----------+------+----------+------+------+----------+-----------------+----------------+
| id | name     | age  | passwdrd | uid  | gid  | comment  | hostdir         | shell          |
+----+----------+------+----------+------+------+----------+-----------------+----------------+
|  1 | root     |   20 | x        |    0 |    0 | root     | /root           | /bin/bash      |
|  2 | bin      |   20 | x        |    1 |    1 | bin      | /bin            | /sbin/nologin  |
|  3 | daemon   |   20 | x        |    2 |    2 | daemon   | /sbin           | /sbin/nologin  |
|  4 | adm      |   20 | x        |    3 |    4 | adm      | /var/adm        | /sbin/nologin  |
|  5 | lp       |   20 | x        |    4 |    7 | lp       | /var/spool/lpd  | /sbin/nologin  |
|  6 | sync     |   20 | x        |    5 |    0 | sync     | /sbin           | /bin/sync      |
|  7 | shutdown |   20 | x        |    6 |    0 | shutdown | /sbin           | /sbin/shutdown |
|  8 | halt     |   20 | x        |    7 |    0 | halt     | /sbin           | /sbin/halt     |
|  9 | mail     |   20 | x        |    8 |   12 | mail     | /var/spool/mail | /sbin/nologin  |
+----+----------+------+----------+------+------+----------+-----------------+----------------+
9 rows in set (0.00 sec)
//查询uid小于等于10 的行   并且只输出 name等于sync的行
mysql> select * from db3.user where uid<=10 having name='sync';
+----+------+------+----------+------+------+---------+---------+-----------+
| id | name | age  | passwdrd | uid  | gid  | comment | hostdir | shell     |
+----+------+------+----------+------+------+---------+---------+-----------+
|  6 | sync |   20 | x        |    5 |    0 | sync    | /sbin   | /bin/sync |
+----+------+------+----------+------+------+---------+---------+-----------+

5.限制查询结果显示行数

limit
sql语句 limit 数字 ; //显示查询结果的前多少行
sql语句 limit 数字1(起始行),数字2(显示之后多少行); //显示指定范围内的行

//显示前两行
mysql> select * from db3.user where id <=10  limit 2;
+----+------+------+----------+------+------+---------+---------+---------------+
| id | name | age  | passwdrd | uid  | gid  | comment | hostdir | shell         |
+----+------+------+----------+------+------+---------+---------+---------------+
|  1 | root |   20 | x        |    0 |    0 | root    | /root   | /bin/bash     |
|  2 | bin  |   20 | x        |    1 |    1 | bin     | /bin    | /sbin/nologin |
+----+------+------+----------+------+------+---------+---------+---------------+
2 rows in set (0.00 sec)
//显示第二行后面的三行
mysql> mysql> select * from db3.user where id <=10  limit 2,3;
+----+--------+------+----------+------+------+---------+----------------+---------------+
| id | name   | age  | passwdrd | uid  | gid  | comment | hostdir        | shell         |
+----+--------+------+----------+------+------+---------+----------------+---------------+
|  3 | daemon |   20 | x        |    2 |    2 | daemon  | /sbin          | /sbin/nologin |
|  4 | adm    |   20 | x        |    3 |    4 | adm     | /var/adm       | /sbin/nologin |
|  5 | lp     |   20 | x        |    4 |    7 | lp      | /var/spool/lpd | /sbin/nologin |
+----+--------+------+----------+------+------+---------+----------------+---------------+
3 rows in set (0.00 sec)