【9.0】MySQL之过滤条件
【一】查询语法
【1】语法
select */字段名 from 表名 where 筛选条件;
【2】执行顺序
from
where
select
【3】模版
- 虽然执行顺序和书写顺序不一致，但是可以按照书写顺序写SQL语句
# 先用 * 占位，再去补全完整的 SQL 语句
select * from * where *
# * 替换成想要的字段
【二】数据准备
【1】创建数据库
drop table if exists emp_data;
create database emp_data;
【2】创建表
use emp_data;
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum("male","female") not null default "male",
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
- 查看表结构
desc emp;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)
【3】插入测试数据
insert into emp(name, sex, age, hire_date, post, salary, office, depart_id) values
("dream", "male", 78, '20220306', "陌夜痴梦久生情", 730.33, 401, 1), # 以下是教学部
("mengmeng", "female", 25, '20220102', "teacher", 12000.50, 401, 1),
("xiaomeng", "male", 35, '20190607', "teacher", 15000.99, 401, 1),
("xiaona", "female", 29, '20180906', "teacher", 11000.80, 401, 1),
("xiaoqi", "female", 27, '20220806', "teacher", 13000.70, 401, 1),
("suimeng", "male", 33, '20230306', "teacher", 14000.62, 401, 1), # 以下是销售部
("娜娜", "female", 69, '20100307', "sale", 300.13, 402, 2),
("芳芳", "male", 45, '20140518', "sale", 400.45, 402, 2),
("小明", "male", 34, '20160103', "sale", 350.80, 402, 2),
("亚洲", "female", 42, '20170227', "sale", 320.99, 402, 2),
("华华", "female", 55, '20180319', "sale", 380.75, 402, 2),
("田七", "male", 44, '20230808', "sale", 420.33, 402, 2), # 以下是运行部
("大古", "female", 66, '20180509', "operation", 630.33, 403, 3),
("张三", "male", 51, '20191001', "operation", 410.25, 403, 3),
("李四", "male", 47, '20200512', "operation", 330.62, 403, 3),
("王五", "female", 39, '20210203', "operation", 370.98, 403, 3),
("赵六", "female", 36, '20220724', "operation", 390.15, 403, 3);
# Query OK, 17 rows affected (0.17 sec)
# Records: 17 Duplicates: 0 Warnings: 0
- 查看数据
select * from emp;
- 格式化美化数据
select * from emp\G;
【三】筛选条件之where
【1】作用
- 对整体数据的筛选
【2】查询3<=id<=6的数据
- 查询数据方式一
select id,name,age from emp where id >=3 and id <=6;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 3 | xiaomeng | 35 |
| 4 | xiaona | 29 |
| 5 | xiaoqi | 27 |
| 6 | suimeng | 33 |
+----+----------+-----+
4 rows in set (0.00 sec)
- 查询数据方式二
select id,name,age from emp where id between 3 and 6;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 3 | xiaomeng | 35 |
| 4 | xiaona | 29 |
| 5 | xiaoqi | 27 |
| 6 | suimeng | 33 |
+----+----------+-----+
4 rows in set (0.00 sec)
【3】查询 薪资是1w2或者1w3或者7300 的数据
- 查询数据方式一
select * from emp where salary=12000.50 or salary = 13000.70 or salary = 7300.33;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+5 rows in set (0.00 sec)
- 查询方式二
select * from emp where salary in (12000.50,13000.70,7300.33);
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+5 rows in set (0.00 sec)
【3】查询 员工姓名中包含字母o的姓名和薪资
模糊查询：like
% 任意
- 任意单个字符
- 查询数据
select name,salary from emp where name like "%o%";
+----------+----------+
| name | salary |
+----------+----------+
| xiaomeng | 15000.99 |
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
| xiaomeng | 15000.99 |
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
+----------+----------+
6 rows in set (0.00 sec)
【4】查询员工姓名是由六个字符组成的姓名和薪资
- 查询数据方式一
select name,salary from emp where name like "______";
+--------+----------+
| name | salary |
+--------+----------+
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
+--------+----------+
4 rows in set (0.00 sec)
- 查询数据方式二
select name,salary from emp where char_length(name) = 6;
+--------+----------+
| name | salary |
+--------+----------+
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
+--------+----------+
4 rows in set (0.00 sec)
【5】查询 id<3 或者 id>6 的数据
select * from emp where id not between 3 and 6;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+19 rows in set (0.00 sec)
【6】查询岗位描述为空的员工姓名和岗位名
针对 null 不能用 = ，而是要用 is
- 查询数据
select * from emp where post_comment is null;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+23 rows in set (0.00 sec)
【四】筛选条件之group by(分组)
【】0什么时候需要分组？
- 参考关键字：每个、平均、最高、最低
【1】按照部门分组
（1）查询数据
select * from emp group by post;
# ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'day03.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
（2）严格模式
-
关闭这个严格模式
-
模糊查询所有严格模式
show variables like "%mode";
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value
|
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| block_encryption_mode | aes-128-ecb
|
| gtid_mode | OFF
|
| innodb_autoinc_lock_mode | 1
|
| innodb_strict_mode | ON
|
| offline_mode | OFF
|
| pseudo_slave_mode | OFF
|
| rbr_exec_mode | STRICT
|
| slave_exec_mode | STRICT
|
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set, 1 warning (0.00 sec)
（3）替换严格模式
- 删除了 ONLY_FULL_GROUP_BY
set global sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER';
- 查询数据
select * from emp group by post;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+4 rows in set (0.00 sec)
拿到每一个部门的第一行数据
最小的操作单位应该是组，而不是组内的单个数据
这条命令在没有设置严格模式的时候是可以执行的，返回的数据是每组的第一条数据
但是分组不应该以单条数据为参考，而是要以组为操作单位
如果设置了严格模式，上述命令会直接报错
也就是上面的那个错误
（4）其他办法
- 设置严格模式
set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
# Query OK, 0 rows affected (0.00 sec)
设置严格模式后，按照什么分组就只能拿到什么
其他字段不能直接获取，获取其他数据需要借助其他方法
- 查询数据
select post from emp group by post;
+-----------------------+
| post |
+-----------------------+
| operation |
| sale |
| teacher |
| 陌夜痴梦久生情 |
+-----------------------+
4 rows in set (0.00 sec)
【2】获取每个部门的最高薪资(max)
（1）聚合函数 - max
-
聚合函数：max - 取最大值
-
可以利用as关键字给字段起别名，或者默认不写
-
但是不推荐，如果忽略语义不明确，容易错乱
（2）查询数据
select post,max(salary) from emp group by post;
+-----------------------+-------------+
| post | max(salary) |
+-----------------------+-------------+
| operation | 630.33 |
| sale | 420.33 |
| teacher | 15000.99 |
| 陌夜痴梦久生情 | 7300.33 |
+-----------------------+-------------+
4 rows in set (0.00 sec)
（3）查询数据指定别名
select post as "部门" ,max(salary) as "最高薪资" from emp group by post;
+-----------------------+--------------+
| 部门 | 最高薪资 |
+-----------------------+--------------+
| operation | 630.33 |
| sale | 420.33 |
| teacher | 15000.99 |
| 陌夜痴梦久生情 | 7300.33 |
+-----------------------+--------------+
4 rows in set (0.00 sec)
【3】获取每个部门的最低薪资(min)
（1）聚合函数 - min
- 聚合函数：min- 取最大值
（2）查询数据
select post as "部门" ,min(salary) as "最低薪资" from emp group by post;
+-----------------------+--------------+
| 部门 | 最低薪资 |
+-----------------------+--------------+
| operation | 330.62 |
| sale | 300.13 |
| teacher | 11000.80 |
| 陌夜痴梦久生情 | 730.33 |
+-----------------------+--------------+
4 rows in set (0.00 sec)
【4】获取每个部门的平均薪资(avg)
（1）聚合函数 - avg
- 聚合函数：avg- 取最大值
（2）查询数据
select post as "部门" ,avg(salary) as "平均薪资" from emp group by post;
+-----------------------+--------------+
| 部门 | 平均薪资 |
+-----------------------+--------------+
| operation | 426.466000 |
| sale | 362.241667 |
| teacher | 13000.722000 |
| 陌夜痴梦久生情 | 4015.330000 |
+-----------------------+--------------+
4 rows in set (0.00 sec)
【5】获取每个部门的薪资总和(sum)
（1）聚合函数 - sum
- 聚合函数：sum- 取最大值
（2）查询数据
select post as "部门" ,sum(salary) as "薪资总和" from emp group by post;
+-----------------------+--------------+
| 部门 | 薪资总和 |
+-----------------------+--------------+
| operation | 2132.33 |
| sale | 2173.45 |
| teacher | 130007.22 |
| 陌夜痴梦久生情 | 8030.66 |
+-----------------------+--------------+
4 rows in set (0.00 sec)
【6】获取每个部门的人数(count)
（1）聚合函数 - count
- 聚合函数：count- 取最大值
（2）查询数据
select post as "部门" ,count(salary) as "部门的人数" from emp group by post;
+-----------------------+-----------------+
| 部门 | 部门的人数 |
+-----------------------+-----------------+
| operation | 5 |
| sale | 6 |
| teacher | 10 |
| 陌夜痴梦久生情 | 2 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)
select post as "部门" ,count(id) as "部门的人数" from emp group by post;
+-----------------------+-----------------+
| 部门 | 部门的人数 |
+-----------------------+-----------------+
| operation | 5 |
| sale | 6 |
| teacher | 10 |
| 陌夜痴梦久生情 | 2 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)
select post as "部门" ,count(age) as "部门的人数" from emp group by post;
+-----------------------+-----------------+
| 部门 | 部门的人数 |
+-----------------------+-----------------+
| operation | 5 |
| sale | 6 |
| teacher | 10 |
| 陌夜痴梦久生情 | 2 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)
（3）不能对null计数
select post as "部门" ,count(post_comment) as "部门的人数" from emp group by post;
+-----------------------+-----------------+
| 部门 | 部门的人数 |
+-----------------------+-----------------+
| operation | 0 |
| sale | 0 |
| teacher | 0 |
| 陌夜痴梦久生情 | 0 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)
【7】查询分组之后的部门名称和每个部门下所有的员工姓名(group_concat)
（1）聚合函数 - group_concat
- 聚合函数：group_concat- 获得分组之后的具体的值
- 不单单支持获取分组之后的其他字段值，还支持拼接操作
（2）查询数据
select post,group_concat(name) from emp group by post;
+-----------------------+---------------------------------------------------------------------------------+
| post | group_concat(name) |
+-----------------------+---------------------------------------------------------------------------------+
| operation | 大古,张三,李四,王五,赵六 |
| sale | 娜娜,芳芳,小明,亚洲,华华,田七 |
| teacher | mengmeng,xiaomeng,xiaona,xiaoqi,suimeng,mengmeng,xiaomeng,xiaona,xiaoqi,suimeng |
| 陌夜痴梦久生情 | dream,dream |
+-----------------------+---------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
（3）拼接数据
select post,group_concat(name,'_drm') from emp group by post;
+-----------------------+-------------------------------------------------------------------------------------------------------------------------+
| post | group_concat(name,'_drm')
|
+-----------------------+-------------------------------------------------------------------------------------------------------------------------+
| operation | 大古_drm,张三_drm,李四_drm,王五_drm,赵六_drm
|
| sale | 娜娜_drm,芳芳_drm,小明_drm,亚洲_drm,华华_drm,田七_drm
|
| teacher | mengmeng_drm,xiaomeng_drm,xiaona_drm,xiaoqi_drm,suimeng_drm,mengmeng_drm,xiaomeng_drm,xiaona_drm,xiaoqi_drm,suimeng_drm |
| 陌夜痴梦久生情 | dream_drm,dream_drm
|
+-----------------------+-------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
（4）查询多条数据
select post,group_concat(name,':',salary) from emp group by post;
+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| post | group_concat(name,':',salary)
|
+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| operation | 大古:630.33,张三:410.25,李四:330.62,王五:370.98,赵六:390.15
|
| sale | 娜娜:300.13,芳芳:400.45,小明:350.80,亚洲:320.99,华华:380.75,田七:420.33
|
| teacher | mengmeng:12000.50,xiaomeng:15000.99,xiaona:11000.80,xiaoqi:13000.70,suimeng:14000.62,mengmeng:12000.50,xiaomeng:15000.99,xiaona:11000.80,xiaoqi:13000.70,suimeng:14000.62 |
| 陌夜痴梦久生情 | dream:7300.33,dream:730.33
|
+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
（5）查询数据(不分组之前用concat)
select concat("NAME:",name),concat("SALARY:",salary) from emp;
+----------------------+--------------------------+
| concat("NAME:",name) | concat("SALARY:",salary) |
+----------------------+--------------------------+
| NAME:dream | SALARY:7300.33 |
| NAME:mengmeng | SALARY:12000.50 |
| NAME:xiaomeng | SALARY:15000.99 |
| NAME:xiaona | SALARY:11000.80 |
| NAME:xiaoqi | SALARY:13000.70 |
| NAME:suimeng | SALARY:14000.62 |
| NAME:dream | SALARY:730.33 |
| NAME:mengmeng | SALARY:12000.50 |
| NAME:xiaomeng | SALARY:15000.99 |
| NAME:xiaona | SALARY:11000.80 |
| NAME:xiaoqi | SALARY:13000.70 |
| NAME:suimeng | SALARY:14000.62 |
| NAME:娜娜 | SALARY:300.13 |
| NAME:芳芳 | SALARY:400.45 |
| NAME:小明 | SALARY:350.80 |
| NAME:亚洲 | SALARY:320.99 |
| NAME:华华 | SALARY:380.75 |
| NAME:田七 | SALARY:420.33 |
| NAME:大古 | SALARY:630.33 |
| NAME:张三 | SALARY:410.25 |
| NAME:李四 | SALARY:330.62 |
| NAME:王五 | SALARY:370.98 |
| NAME:赵六 | SALARY:390.15 |
+----------------------+--------------------------+
23 rows in set (0.00 sec)
（6）as语法
as 语法不单单可以给字段起别名，还可以给表取别名
只能临时起别名
- 查数据
select emp.id,emp.name from emp;
+----+----------+
| id | name |
+----+----------+
| 1 | dream |
| 2 | mengmeng |
| 3 | xiaomeng |
| 4 | xiaona |
| 5 | xiaoqi |
| 6 | suimeng |
| 7 | dream |
| 8 | mengmeng |
| 9 | xiaomeng |
| 10 | xiaona |
| 11 | xiaoqi |
| 12 | suimeng |
| 13 | 娜娜 |
| 14 | 芳芳 |
| 15 | 小明 |
| 16 | 亚洲 |
| 17 | 华华 |
| 18 | 田七 |
| 19 | 大古 |
| 20 | 张三 |
| 21 | 李四 |
| 22 | 王五 |
| 23 | 赵六 |
+----+----------+
23 rows in set (0.00 sec)
- 查数据起别名
select emp.id,emp.name from emp as ti;
# ERROR 1054 (42S22): Unknown column 'emp.id' in 'field list'
select ti.id,ti.name from emp as ti;
+----+----------+
| id | name |
+----+----------+
| 1 | dream |
| 2 | mengmeng |
| 3 | xiaomeng |
| 4 | xiaona |
| 5 | xiaoqi |
| 6 | suimeng |
| 7 | dream |
| 8 | mengmeng |
| 9 | xiaomeng |
| 10 | xiaona |
| 11 | xiaoqi |
| 12 | suimeng |
| 13 | 娜娜 |
| 14 | 芳芳 |
| 15 | 小明 |
| 16 | 亚洲 |
| 17 | 华华 |
| 18 | 田七 |
| 19 | 大古 |
| 20 | 张三 |
| 21 | 李四 |
| 22 | 王五 |
| 23 | 赵六 |
+----+----------+
23 rows in set (0.00 sec)
【8】查询每个人的年薪(12)
直接对查询到的数据进行运算
- 查询数据
select name,salary*12 from emp;
+----------+-----------+
| name | salary*12 |
+----------+-----------+
| dream | 87603.96 |
| mengmeng | 144006.00 |
| xiaomeng | 180011.88 |
| xiaona | 132009.60 |
| xiaoqi | 156008.40 |
| suimeng | 168007.44 |
| dream | 8763.96 |
| mengmeng | 144006.00 |
| xiaomeng | 180011.88 |
| xiaona | 132009.60 |
| xiaoqi | 156008.40 |
| suimeng | 168007.44 |
| 娜娜 | 3601.56 |
| 芳芳 | 4805.40 |
| 小明 | 4209.60 |
| 亚洲 | 3851.88 |
| 华华 | 4569.00 |
| 田七 | 5043.96 |
| 大古 | 7563.96 |
| 张三 | 4923.00 |
| 李四 | 3967.44 |
| 王五 | 4451.76 |
| 赵六 | 4681.80 |
+----------+-----------+
23 rows in set (0.00 sec)
【五】筛选条件之 group by(分组) 注意事项
【0】引入
（1）关键字 where 和 group by 同时出现
-
关键字 where 和 group by 同时出现的时候，group by 必须在 where 后面
- where 先对整体数据进行过滤
- group by 再对数据进行分组
（2）where 筛选条件不能使用聚合函数
-
where 筛选条件不能使用聚合函数
- 不分组，默认整张表就是一组
-
聚合函数只能在分组之后使用
- 查询数据
select id,name,age from emp where max(salary) > 3000; # ERROR 1111 (HY000): Invalid use of group function- 查询数据
select max(salary) from emp;+-------------+ | max(salary) | +-------------+ | 15000.99 | +-------------+ 1 row in set (0.00 sec)
【1】统计各部门年龄在 30 岁以上的员工的平均薪资
（1）先求所有年龄大于30岁的员工
select * from emp where age >30;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+17 rows in set (0.00 sec)
（2）再对结果进行分组
select * from emp where age >30 group by post;
（3）语法总结
select post,avg(salary) from emp where age >30 group by post;
+-----------------------+--------------+
| post | avg(salary) |
+-----------------------+--------------+
| operation | 426.466000 |
| sale | 362.241667 |
| teacher | 14500.805000 |
| 陌夜痴梦久生情 | 4015.330000 |
+-----------------------+--------------+
4 rows in set (0.00 sec)
【六】筛选条件之having(分组之后筛选)
【0】引入
- having与where的功能是一模一样的 都是对数据进行筛选
- where用在分组之前的筛选
- havng用在分组之后的筛选
- 只不过having是在分组之后进行的过滤操作
- 即having是可以直接使用聚合函数的
【1】统计各部门年龄在 30 岁以上的员工的工资，并且保留平均薪资大于1w的部门
-- 先筛选出30岁以上的员工数据 然后再对数据进行分组
select post,avg(salary) from emp where age>30 group by post;
-- 在过滤出平均薪资大于10000的数据
select post,avg(salary) from emp
where age >30
group by post
having avg(salary) > 10000
;
+---------+--------------+
| post | avg(salary) |
+---------+--------------+
| teacher | 14500.805000 |
+---------+--------------+
1 row in set (0.00 sec)
-- 针对聚合函数 如果还需要在其他地方作为条件使用 可以先起别名
select post,avg(salary) as avg_salary from emp
where age>30
group by post
having avg_salary > 10000
;
【七】筛选条件之distinct(去重)
【0】引入
- 必须是完全一样的数据才可以去重
- 一定要注意主键的问题
- 在主键存在的情况下是一定不可能去重的
等我们学到Django ORM之后 数据会被封装成对象
那个时候主键很容易被我们忽略 从而导致去重没有效果!!!
【2】对emp表中的age和id去重
select distinct id,age from emp;
+----+-----+
| id | age |
+----+-----+
| 1 | 78 |
| 2 | 25 |
| 3 | 35 |
| 4 | 29 |
| 5 | 27 |
| 6 | 33 |
| 7 | 78 |
| 8 | 25 |
| 9 | 35 |
| 10 | 29 |
| 11 | 27 |
| 12 | 33 |
| 13 | 69 |
| 14 | 45 |
| 15 | 34 |
| 16 | 42 |
| 17 | 55 |
| 18 | 44 |
| 19 | 66 |
| 20 | 51 |
| 21 | 47 |
| 22 | 39 |
| 23 | 36 |
+----+-----+
23 rows in set (0.00 sec)
【3】只对emp表中的age去重
select distinct age from emp;
+-----+
| age |
+-----+
| 78 |
| 25 |
| 35 |
| 29 |
| 27 |
| 33 |
| 69 |
| 45 |
| 34 |
| 42 |
| 55 |
| 44 |
| 66 |
| 51 |
| 47 |
| 39 |
| 36 |
+-----+
17 rows in set (0.00 sec)
【八】筛选条件之order by(排序)
【0】引入
- order by : 默认是升序
- asc 默认可以省略不写 ---> 修改降序
- desc : 降序
【1】将emp表中的数据按照薪资排序(升序)
select * from emp order by salary;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+23 rows in set (0.00 sec)
【2】将emp表中的数据按照薪资排序(降序)
select * from emp order by salary desc;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+23 rows in set (0.00 sec)
【3】将emp表中的数据按照薪资(升序)和年龄(降序)排序
- order by 后面可以跟多个参数
select * from emp order by age desc,salary asc;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+23 rows in set (0.00 sec)
先按照age降序排
如果碰到 age 相同 ，再按照salary 升序排
【4】混合排序
- 统计各部门年龄在 10 岁以上的员工的工资，并且保留平均薪资大于1000的部门，对平均工资进行排序
select post,avg(salary) from emp
where age >10
group by post
having avg(salary) > 1000
order by avg(salary) desc
;
+-----------------------+--------------+
| post | avg(salary) |
+-----------------------+--------------+
| teacher | 13000.722000 |
| 陌夜痴梦久生情 | 4015.330000 |
+-----------------------+--------------+
2 rows in set (0.00 sec)
【九】筛选条件之 limit(限制展示条数)
【0】引入
- 针对数据太多的情况，我们大都是做分页处理
- limit x,y : 第一个参数是起始位置，第二个是条数
【1】查询数据方式一：单数字限制
- 限制只展示五条数据
select * from emp limit 10;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+10 rows in set (0.00 sec)
【2】查询数据：多限制
- 分页效果
select * from emp limit 0,6;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+6 rows in set (0.00 sec)
从 0 后面 取六条
第一个参数是起始位置，第二个是条数
【3】案例：查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
当数据特别多的时候 经常使用limit来限制展示条数
节省资源 防止系统崩溃
【十】筛选条件之正则
【1】语法
属性名 REGEXP '匹配方式'
- 其中，“属性名”表示需要查询的字段名称；
- “匹配方式”表示以哪种方式来匹配查询。
【2】匹配方式
- “匹配方式”中有很多的模式匹配字符，它们分别表示不同的意思。
- 下表列出了 REGEXP 操作符中常用的匹配方式。
| 选项 | 说明 | 例子 | 匹配值示例 |
|---|---|---|---|
| ^ | 匹配文本的开始字符 | ‘^b’ 匹配以字母 b 开头的字符串 | book、big、banana、bike |
| $ | 匹配文本的结束字符 | ‘st$’ 匹配以 st 结尾的字符串 | test、resist、persist |
| . | 匹配任何单个字符 | ‘b.t’ 匹配任何 b 和 t 之间有一个字符 | bit、bat、but、bite |
| * | 匹配前面的字符 0 次或多次 | ‘f*n’ 匹配字符 n 前面有任意个字符 f | fn、fan、faan、abcn |
| + | 匹配前面的字符 1 次或多次 | ‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a | ba、bay、bare、battle |
| ? | 匹配前面的字符 0 次或1次 | ‘sa?’ 匹配0个或1个a字符 | sa、s |
| 字符串 | 匹配包含指定字符的文本 | ‘fa’ 匹配包含‘fa’的文本 | fan、afa、faad |
| [字符集合] | 匹配字符集合中的任何一个字符 | ‘[xz]’ 匹配 x 或者 z | dizzy、zebra、x-ray、extra |
| [^] | 匹配不在括号中的任何字符 | ‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串 | desk、fox、f8ke |
| 字符串 | 匹配前面的字符串至少 n 次 | ‘b{2}’ 匹配 2 个或更多的 b | bbb、bbbb、bbbbbbb |
| 字符串 | 匹配前面的字符串至少 n 次， 至多 m 次 | ‘b{2,4}’ 匹配最少 2 个，最多 4 个 b | bbb、bbbb |
【3】案例
（0）准备数据
- 创建表
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(40) NULL DEFAULT NULL,
`heigh` int(40) NULL DEFAULT NULL,
`sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
- 插入数据
INSERT INTO `person` VALUES ('Thomas ', 25, 168, '男');
INSERT INTO `person` VALUES ('Tom ', 20, 172, '男');
INSERT INTO `person` VALUES ('Dany', 29, 175, '男');
INSERT INTO `person` VALUES ('Jane', 27, 171, '男');
INSERT INTO `person` VALUES ('Susan', 24, 173, '女');
INSERT INTO `person` VALUES ('Green', 25, 168, '女');
INSERT INTO `person` VALUES ('Henry', 21, 160, '女');
INSERT INTO `person` VALUES ('Lily', 18, 190, '男');
INSERT INTO `person` VALUES ('LiMing', 19, 187, '男');
- （1）查询 name 字段以j开头的记录
select * from person where name REGEXP '^j';
+------+------+-------+------+
| name | age | heigh | sex |
+------+------+-------+------+
| Jane | 27 | 171 | |
+------+------+-------+------+
1 row in set (0.00 sec)
- （2）查询 name 字段以“y”结尾的记录
select * from person where name REGEXP 'y$';
+-------+------+-------+------+
| name | age | heigh | sex |
+-------+------+-------+------+
| Dany | 29 | 175 | |
| Henry | 21 | 160 | |
| Lily | 18 | 190 | |
+-------+------+-------+------+
3 rows in set (0.01 sec)
- （3）查询 name 字段值包含“a”和“y”，且两个字母之间只有一个字母的记录
select * from person where name REGEXP 'a.y';
+------+------+-------+------+
| name | age | heigh | sex |
+------+------+-------+------+
| Dany | 29 | 175 | |
+------+------+-------+------+
1 row in set (0.00 sec)
- （4）查询 name 字段值包含字母“T”，且“T”后面出现字母“h”的记录
select * from person where name REGEXP 'Th*';
+---------+------+-------+------+
| name | age | heigh | sex |
+---------+------+-------+------+
| Thomas | 25 | 168 | |
| Tom | 20 | 172 | |
+---------+------+-------+------+
2 rows in set (0.00 sec)
- （5）查询 name 字段值包含字母“T”，且“T”后面至少出现“h”一次的记录
select * from person where name REGEXP 'Th+';
+---------+------+-------+------+
| name | age | heigh | sex |
+---------+------+-------+------+
| Thomas | 25 | 168 | |
+---------+------+-------+------+
1 row in set (0.00 sec)
- （6）查询 name 字段值包含字母“S”，且“S”后面出现“a”一次或零次的记录
select * from person where name REGEXP 'sa?';
+---------+------+-------+------+
| name | age | heigh | sex |
+---------+------+-------+------+
| Thomas | 25 | 168 | |
| Susan | 24 | 173 | |
+---------+------+-------+------+
2 rows in set (0.00 sec)
- （7）查询 name 字段值包含字符串“an”的记录
select * from person where name REGEXP 'an';
+-------+------+-------+------+
| name | age | heigh | sex |
+-------+------+-------+------+
| Dany | 29 | 175 | |
| Jane | 27 | 171 | |
| Susan | 24 | 173 | |
+-------+------+-------+------+
3 rows in set (0.00 sec)
- （8）查询 name 字段值包含字符串“an”或“en”的记录
- 指定多个字符串时，需要用|隔开。只要匹配这些字符串中的任意一个即可。
select * from person where name REGEXP 'an|en';
+-------+------+-------+------+
| name | age | heigh | sex |
+-------+------+-------+------+
| Dany | 29 | 175 | |
| Jane | 27 | 171 | |
| Susan | 24 | 173 | |
| Green | 25 | 168 | |
| Henry | 21 | 160 | |
+-------+------+-------+------+
5 rows in set (0.01 sec)
- （9）查询 name 字段值包含字母“i”或“o”的记录
select * from person where name REGEXP '[io]';
+---------+------+-------+------+
| name | age | heigh | sex |
+---------+------+-------+------+
| Thomas | 25 | 168 | |
| Tom | 20 | 172 | |
| Lily | 18 | 190 | |
| LiMing | 19 | 187 | |
+---------+------+-------+------+
4 rows in set (0.00 sec)
- （10）方括号[ ]还可以指定集合的区间。例如，“[a-z]”表示从 a~z 的所有字母；“[0-9]”表示从 0~9 的所有数字；“[a-z0-9]”表示包含所有的小写字母和数字；“[a-zA-Z]”表示匹配所有字符。MySQL中的正则表达式匹配不区分大小写。为区分大小写，可使用BINARY关键字。
select * from person where name REGEXP BINARY '^[a-z]';
Empty set (0.00 sec)
- （11）查询 name 字段值包含字母 a~t 以外的字符的记录
select * from person where name REGEXP '[^a-t]';
+---------+------+-------+------+
| name | age | heigh | sex |
+---------+------+-------+------+
| Thomas | 25 | 168 | |
| Tom | 20 | 172 | |
| Dany | 29 | 175 | |
| Susan | 24 | 173 | |
| Henry | 21 | 160 | |
| Lily | 18 | 190 | |
+---------+------+-------+------+
6 rows in set (0.00 sec)
- （12）查询 name 字段值出现字母‘e’ 至少 2 次的记录
select * from person where name REGEXP 'e{2,}';
+-------+------+-------+------+
| name | age | heigh | sex |
+-------+------+-------+------+
| Green | 25 | 168 | |
+-------+------+-------+------+
1 row in set (0.00 sec)
- （13）查询 name 字段值出现字符串“i” 最少 1 次，最多 3 次的记录
select * from person where name REGEXP 'i{1,3}';
+--------+------+-------+------+
| name | age | heigh | sex |
+--------+------+-------+------+
| Lily | 18 | 190 | |
| LiMing | 19 | 187 | |
+--------+------+-------+------+
2 rows in set (0.00 sec)
本文来自博客园，作者：Chimengmeng，转载请注明原文链接：https://www.cnblogs.com/dream-ze/p/17995409
