[MYAQL / Mariadb] 数据库学习-管理表记录2：匹配条件

Satushi-sho

已于 2023-05-17 16:21:00 修改

阅读量450

点赞数

分类专栏：数据库文章标签：数据库 mariadb 学习 mysql

于 2023-05-17 16:20:36 首次发布

本文链接：https://blog.csdn.net/HYFarashi/article/details/130712196

版权

数据库专栏收录该内容

17 篇文章 0 订阅

订阅专栏

管理表记录-匹配条件

匹配条件

匹配条件

基本条件.
数值匹配
逻辑匹配

.
高级条件
范围匹配
模糊查询

基本条件

请添加图片描述

查询

mysql> select id,name,uid,gid from user where uid != gid;
+----+------------------+------+------+
| id | name             | uid  | gid  |
+----+------------------+------+------+
|  4 | adm              |    3 |    4 |
|  5 | lp               |    4 |    7 |
|  6 | sync             |    5 |    0 |
|  7 | shutdown         |    6 |    0 |
|  8 | halt             |    7 |    0 |
|  9 | mail             |    8 |   12 |
| 10 | operator         |   11 |    0 |
| 11 | games            |   12 |  100 |
| 12 | ftp              |   14 |   50 |
| 15 | systemd-coredump |  999 |  997 |
| 18 | polkitd          |  998 |  996 |
| 19 | unbound          |  997 |  995 |
| 20 | sssd             |  996 |  993 |
| 22 | rngd             |  995 |  992 |
| 30 | bob              | 2023 | 2020 |
| 31 | tom              | 2023 | 2020 |
| 32 | bob              | 2023 | 2020 |
| 33 | lisi             | 2023 | 2020 |
| 34 | susan            | 2023 | 2020 |
+----+------------------+------+------+
19 rows in set (0.01 sec)

mysql> select id,name,uid,gid from user where name='bob';
+----+------+------+------+
| id | name | uid  | gid  |
+----+------+------+------+
| 30 | bob  | 2023 | 2020 |
| 32 | bob  | 2023 | 2020 |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql> select id,name,uid,gid from user where name is null;
Empty set (0.00 sec)

mysql> select id,name,uid,gid from user where gid is not null;
+----+------------------+-------+-------+
| id | name             | uid   | gid   |
+----+------------------+-------+-------+
|  1 | root             |     0 |     0 |
|  2 | bin              |     1 |     1 |
|  3 | daemon           |     2 |     2 |
|  4 | adm              |     3 |     4 |
|  5 | lp               |     4 |     7 |
|  6 | sync             |     5 |     0 |
|  7 | shutdown         |     6 |     0 |
|  8 | halt             |     7 |     0 |
|  9 | mail             |     8 |    12 |
| 10 | operator         |    11 |     0 |
| 11 | games            |    12 |   100 |
| 12 | ftp              |    14 |    50 |

逻辑匹配（多个条件判断）

逻辑与： and，多个同时成立
.
逻辑或： or,多个中有成立的即可
.
逻辑非：！， not ,取反

mysql> select id,name,uid from user  where id >= 20  and uid = 2023;
+----+-------+------+
| id | name  | uid  |
+----+-------+------+
| 30 | bob   | 2023 |
| 31 | tom   | 2023 |
| 32 | bob   | 2023 |
| 33 | lisi  | 2023 |
| 34 | susan | 2023 |
+----+-------+------+
5 rows in set (0.00 sec)

mysql> select id,name,uid from user  where id >= 20  and uid != 2023;
+----+--------+------+
| id | name   | uid  |
+----+--------+------+
| 20 | sssd   |  996 |
| 21 | sshd   |   74 |
| 22 | rngd   |  995 |
| 23 | alice  | 1000 |
| 24 | apache |   48 |
| 25 | mysql  | 1001 |
+----+--------+------+
6 rows in set (0.00 sec)



mysql> select id,name,uid from user  where id >= 20  or uid <=5  or name='lisi';
+----+--------+------+
| id | name   | uid  |
+----+--------+------+
|  1 | root   |    0 |
|  2 | bin    |    1 |
|  3 | daemon |    2 |
|  4 | adm    |    3 |
|  5 | lp     |    4 |
|  6 | sync   |    5 |
| 20 | sssd   |  996 |
| 21 | sshd   |   74 |
| 22 | rngd   |  995 |
| 23 | alice  | 1000 |
| 24 | apache |   48 |
| 25 | mysql  | 1001 |
| 30 | bob    | 2023 |
| 31 | tom    | 2023 |
| 32 | bob    | 2023 |
| 33 | lisi   | 2023 |
| 34 | susan  | 2023 |
+----+--------+------+
17 rows in set (0.00 sec)

高级条件

范围匹配

请添加图片描述

mysql> select id,name,uid from user where uid in (1,20,50,100) ;                               +----+------+------+
| id | name | uid  |
+----+------+------+
|  2 | bin  |    1 |
+----+------+------+
1 row in set (0.01 sec)



mysql> select id,name,shell from user where shell not in ('/bin/bash','/sbin/nologin');
+----+----------+----------------+
| id | name     | shell          |
+----+----------+----------------+
|  6 | sync     | /bin/sync      |
|  7 | shutdown | /sbin/shutdown |
|  8 | halt     | /sbin/halt     |
+----+----------+----------------+
3 rows in set (0.00 sec)


mysql> select uid,name,shell from user where uid between 2000 and 2023;
+------+-------+-----------+
| uid  | name  | shell     |
+------+-------+-----------+
| 2023 | bob   | /bin/bash |
| 2023 | tom   | /bin/bash |
| 2023 | bob   | /bin/bash |
| 2023 | lisi  | NULL      |
| 2023 | susan | NULL      |
+------+-------+-----------+
5 rows in set (0.00 sec)

模糊查询

请添加图片描述

mysql> select uid,name,shell from user where name like '__%___';
+-------+------------------+----------------+
| uid   | name             | shell          |
+-------+------------------+----------------+
|     2 | daemon           | /sbin/nologin  |
|     6 | shutdown         | /sbin/shutdown |
|    11 | operator         | /sbin/nologin  |
|    12 | games            | /sbin/nologin  |
| 65534 | nobody           | /sbin/nologin  |
|   999 | systemd-coredump | /sbin/nologin  |
|   193 | systemd-resolve  | /sbin/nologin  |
|   998 | polkitd          | /sbin/nologin  |
|   997 | unbound          | /sbin/nologin  |
|  1000 | alice            | /bin/bash      |
|    48 | apache           | /sbin/nologin  |
|  1001 | mysql            | /bin/bash      |
|  2023 | susan            | NULL           |
+-------+------------------+----------------+
13 rows in set (0.00 sec)



mysql> select uid,name,shell from user where name like '___';
+------+------+---------------+
| uid  | name | shell         |
+------+------+---------------+
|    1 | bin  | /sbin/nologin |
|    3 | adm  | /sbin/nologin |
|   14 | ftp  | /sbin/nologin |
|   59 | tss  | /sbin/nologin |
| 2023 | bob  | /bin/bash     |
| 2023 | tom  | /bin/bash     |
| 2023 | bob  | /bin/bash     |
+------+------+---------------+
7 rows in set (0.00 sec)


mysql> select uid,name,shell from user where name like '%b%';
+-------+---------+---------------+
| uid   | name    | shell         |
+-------+---------+---------------+
|     1 | bin     | /sbin/nologin |
| 65534 | nobody  | /sbin/nologin |
|    81 | dbus    | /sbin/nologin |
|   997 | unbound | /sbin/nologin |
|  2023 | bob     | /bin/bash     |
|  2023 | bob     | /bin/bash     |
+-------+---------+---------------+
6 rows in set (0.00 sec)

正则表达式： regexp

^ ：匹配开头
$ ：匹配结尾
. ：匹配任意单个
[ ] ：匹配范围内的

：匹配前面一个的内容，任意多个
| ：或者

请添加图片描述

mysql> select uid,name from user where name regexp '^b|m$';
+------+------+
| uid  | name |
+------+------+
|    1 | bin  |
|    3 | adm  |
| 2023 | bob  |
| 2023 | tom  |
| 2023 | bob  |
+------+------+
5 rows in set (0.00 sec)


mysql> select uid,name from user where name regexp 'd$';
+------+---------+
| uid  | name    |
+------+---------+
|  998 | polkitd |
|  997 | unbound |
|  996 | sssd    |
|   74 | sshd    |
|  995 | rngd    |
+------+---------+
5 rows in set (0.00 sec)


开头在ablt中的名字
mysql> select uid,name from user where name regexp '^[ablt]';
+------+--------+
| uid  | name   |
+------+--------+
|    1 | bin    |
|    3 | adm    |
|    4 | lp     |
|   59 | tss    |
| 1000 | alice  |
|   48 | apache |
| 2023 | bob    |
| 2023 | tom    |
| 2023 | bob    |
| 2023 | lisi   |
+------+--------+
10 rows in set (0.00 sec)


b开头b结尾，匹配多个字符
mysql> select uid,name from user where name regexp '^b.*b$';
+------+---------+
| uid  | name    |
+------+---------+
| 2023 | bob     |
| 2023 | bob     |
| 1999 | baobob  |
| 2021 | bilib   |
| 2021 | baibaib |
+------+---------+
5 rows in set (0.00 sec)


b开头b结尾，匹配多一个字符
mysql> select uid,name from user where name regexp '^b.b$';
+------+------+
| uid  | name |
+------+------+
| 2023 | bob  |
| 2023 | bob  |
+------+------+
2 rows in set (0.00 sec)

起码名字字长为5个字符及以上
mysql> select name from user where name regexp '.....';
+------------------+
| name             |
+------------------+
| daemon           |
| shutdown         |
| operator         |
| games            |
| nobody           |
| systemd-coredump |
| systemd-resolve  |
| polkitd          |
| unbound          |
| alice            |
| apache           |
| mysql            |
| susan            |
| baobob           |
| bilib            |
| baibaib          |
+------------------+
16 rows in set (0.00 sec)


mysql> select name,uid from user where uid regexp '....';
+---------+-------+
| name    | uid   |
+---------+-------+
| nobody  | 65534 |
| alice   |  1000 |
| mysql   |  1001 |
| bob     |  2023 |
| tom     |  2023 |
| bob     |  2023 |
| lisi    |  2023 |
| susan   |  2023 |
| baobob  |  1999 |
| bilib   |  2021 |
| baibaib |  2021 |
+---------+-------+
11 rows in set (0.00 sec)


限制字符长度为4
mysql> select name,uid from user where uid regexp '^....$';
+---------+------+
| name    | uid  |
+---------+------+
| alice   | 1000 |
| mysql   | 1001 |
| bob     | 2023 |
| tom     | 2023 |
| bob     | 2023 |
| lisi    | 2023 |
| susan   | 2023 |
| baobob  | 1999 |
| bilib   | 2021 |
| baibaib | 2021 |
+---------+------+
10 rows in set (0.00 sec)

四则运算

请添加图片描述

mysql> select id,name,uid from user where uid>=2000;
+----+---------+-------+
| id | name    | uid   |
+----+---------+-------+
| 13 | nobody  | 65534 |
| 30 | bob     |  2024 |
| 31 | tom     |  2024 |
| 32 | bob     |  2024 |
| 33 | lisi    |  2024 |
| 34 | susan   |  2024 |
| 37 | baobob  |  2000 |
| 38 | bilib   |  2022 |
| 39 | baibaib |  2022 |
+----+---------+-------+
9 rows in set (0.00 sec)


给id大于等于30的，id加10
mysql> update user set id=id+10 where id>=30;
Query OK, 8 rows affected (0.04 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> select id,name,uid from user where id>=30;
+----+---------+------+
| id | name    | uid  |
+----+---------+------+
| 40 | bob     | 2024 |
| 41 | tom     | 2024 |
| 42 | bob     | 2024 |
| 43 | lisi    | 2024 |
| 44 | susan   | 2024 |
| 47 | baobob  | 2000 |
| 48 | bilib   | 2022 |
| 49 | baibaib | 2022 |
+----+---------+------+
8 rows in set (0.00 sec)

把id大于等于30的，用户和组id*2，把操作命名为yxj（优先级）

mysql> select name,uid,gid,(uid+gid)*2 yxj from user where id >=30;
+---------+------+------+------+
| name    | uid  | gid  | yxj  |
+---------+------+------+------+
| bob     | 2024 | 2020 | 8088 |
| tom     | 2024 | 2020 | 8088 |
| bob     | 2024 | 2020 | 8088 |
| lisi    | 2024 | 2020 | 8088 |
| susan   | 2024 | 2020 | 8088 |
| baobob  | 2000 | NULL | NULL |
| bilib   | 2022 | NULL | NULL |
| baibaib | 2022 | NULL | NULL |
+---------+------+------+------+
8 rows in set (0.00 sec)



不加括号，按顺序算
mysql> select name,uid,gid,uid+gid*2 yxj from user where id >=30;
+---------+------+------+------+
| name    | uid  | gid  | yxj  |
+---------+------+------+------+
| bob     | 2024 | 2020 | 6064 |
| tom     | 2024 | 2020 | 6064 |
| bob     | 2024 | 2020 | 6064 |
| lisi    | 2024 | 2020 | 6064 |
| susan   | 2024 | 2020 | 6064 |
| baobob  | 2000 | NULL | NULL |
| bilib   | 2022 | NULL | NULL |
| baibaib | 2022 | NULL | NULL |
+---------+------+------+------+
8 rows in set (0.00 sec)

操作查询结果（对查找到的数据再做处理）

请添加图片描述

排序

mysql> select name,id,uid from user where uid>=1700 ;
+---------+----+-------+
| name    | id | uid   |
+---------+----+-------+
| nobody  | 13 | 65534 |
| bob     | 30 |  2024 |
| tom     | 31 |  2024 |
| bob     | 32 |  2024 |
| lisi    | 33 |  2024 |
| susan   | 34 |  2024 |
| baobob  | 37 |  2000 |
| bilib   | 38 |  2022 |
| baibaib | 39 |  2022 |
+---------+----+-------+
9 rows in set (0.00 sec)


排序默认是升序：
mysql> select name,id,uid from user where uid>=1700 order by uid;
+---------+----+-------+
| name    | id | uid   |
+---------+----+-------+
| baobob  | 37 |  2000 |
| bilib   | 38 |  2022 |
| baibaib | 39 |  2022 |
| bob     | 30 |  2024 |
| tom     | 31 |  2024 |
| bob     | 32 |  2024 |
| lisi    | 33 |  2024 |
| susan   | 34 |  2024 |
| nobody  | 13 | 65534 |
+---------+----+-------+
9 rows in set (0.00 sec)



降序排：
mysql> select name,id,uid from user where uid>=1700 order by uid desc;
+---------+----+-------+
| name    | id | uid   |
+---------+----+-------+
| nobody  | 13 | 65534 |
| bob     | 30 |  2024 |
| tom     | 31 |  2024 |
| bob     | 32 |  2024 |
| lisi    | 33 |  2024 |
| susan   | 34 |  2024 |
| bilib   | 38 |  2022 |
| baibaib | 39 |  2022 |
| baobob  | 37 |  2000 |
+---------+----+-------+
9 rows in set (0.00 sec)

分组（一样的显示一次）：查询语句 group by 字段；

字段类太多，用shell分组，逻辑错误
mysql> select name,id,shell from user group by shell;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db1.user.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


一般查什么按什么分：
mysql> select shell from user group by shell;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /sbin/nologin  |
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
| NULL           |
+----------------+
6 rows in set (0.00 sec)

去重 distinct（部分实现无法由group完成）

mysql> select  name,id from user where id>=30;
+---------+----+
| name    | id |
+---------+----+
| bob     | 30 |
| tom     | 31 |
| bob     | 32 |
| lisi    | 33 |
| susan   | 34 |
| baobob  | 37 |
| bilib   | 38 |
| baibaib | 39 |
+---------+----+
8 rows in set (0.00 sec)

mysql> select distinct  name from user where id>=30;
+---------+
| name    |
+---------+
| bob     |
| tom     |
| lisi    |
| susan   |
| baobob  |
| bilib   |
| baibaib |
+---------+
7 rows in set (0.01 sec)

mysql> select group  name from user where id>=30;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group  name from user where id>=30' at line 1

聚集函数（MySQL自带的对数据统计的命令）

请添加图片描述

mysql> select uid from user where id>=30  ;
+------+
| uid  |
+------+
| 2024 |
| 2024 |
| 2024 |
| 2024 |
| 2024 |
| 2000 |
| 2022 |
| 2022 |
+------+
8 rows in set (0.00 sec)


最小值、最大值、平均值、和、个数
mysql> select min(uid),max(uid),avg(uid),sum(uid),count(uid) from user where id>=30  ;
+----------+----------+-----------+----------+------------+
| min(uid) | max(uid) | avg(uid)  | sum(uid) | count(uid) |
+----------+----------+-----------+----------+------------+
|     2000 |     2024 | 2020.5000 |    16164 |          8 |
+----------+----------+-----------+----------+------------+
1 row in set (0.00 sec)

null 空，在统计个数时，算无数据不会计入，所以这里gid才会是5.

mysql> select *  from user where id>=30  ;
+----+---------+----------+------+------+-----------+-----------+-----------+
| id | name    | password | uid  | gid  | comment   | homedir   | shell     |
+----+---------+----------+------+------+-----------+-----------+-----------+
| 30 | bob     | x        | 2024 | 2020 | test user | /home/bob | /bin/bash |
| 31 | tom     | x        | 2024 | 2020 | test user | /home/tom | /bin/bash |
| 32 | bob     | x        | 2024 | 2020 | test user | /home/bob | /bin/bash |
| 33 | lisi    | NULL     | 2024 | 2020 | NULL      | NULL      | NULL      |
| 34 | susan   | NULL     | 2024 | 2020 | NULL      | NULL      | NULL      |
| 37 | baobob  | NULL     | 2000 | NULL | NULL      | NULL      | NULL      |
| 38 | bilib   | NULL     | 2022 | NULL | NULL      | NULL      | NULL      |
| 39 | baibaib | NULL     | 2022 | NULL | NULL      | NULL      | NULL      |
+----+---------+----------+------+------+-----------+-----------+-----------+
8 rows in set (0.00 sec)

mysql> select count(gid)  from user where id>=30  ;
+------------+
| count(gid) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

查询结果过滤

请添加图片描述

mysql> select  name from user where id>=30  ;
+---------+
| name    |
+---------+
| bob     |
| tom     |
| bob     |
| lisi    |
| susan   |
| baobob  |
| bilib   |
| baibaib |
+---------+
8 rows in set (0.00 sec)

mysql> select  name from user where id>=30  having name='bob';
+------+
| name |
+------+
| bob  |
| bob  |
+------+
2 rows in set (0.01 sec)

限制查询结果显示行数

请添加图片描述

mysql> select  name,id from user where id<=10 ;
+----------+----+
| name     | id |
+----------+----+
| root     |  1 |
| bin      |  2 |
| daemon   |  3 |
| adm      |  4 |
| lp       |  5 |
| sync     |  6 |
| shutdown |  7 |
| halt     |  8 |
| mail     |  9 |
| operator | 10 |
+----------+----+
10 rows in set (0.00 sec)

显示前5行：
mysql> select  name,id from user where id<=10  limit 5;
+--------+----+
| name   | id |
+--------+----+
| root   |  1 |
| bin    |  2 |
| daemon |  3 |
| adm    |  4 |
| lp     |  5 |
+--------+----+
5 rows in set (0.01 sec)


从第5行以后显示，显示2行
mysql> select  name,id from user where id<=10  limit 5,2;
+----------+----+
| name     | id |
+----------+----+
| sync     |  6 |
| shutdown |  7 |
+----------+----+
2 rows in set (0.00 sec)