首页 > 数据库 >MySQL之过滤条件

MySQL之过滤条件

时间：2024-02-01 20:14:33浏览次数：23  
标签：male MySQL sale 过滤 female 条件 post NULL teacher

【一】筛选过滤条件

【1】查询语句

-- 查询当前表中的指定字段的数据
select id,name from emp where id > 3;

【2】创建数据表

create database emp_data;
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_data date not null,
    post varchar(50),
    post_comment varchar(100),
    salary double(15,2),
    office int,
    depart_id int
    
);

insert into emp(name, sex, age, hire_data, 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);
select * from emp;
select * from emp\G;

【3】筛选条件之where

  • 查询3<=id<=6的数据
mysql> 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)

mysql> 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)
  • 查询 薪资是 12000.50 或者 13000.70 或者730.33 的数据
mysql> select * from emp where salary in (12000.50,13000.70,730.33);
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_data  | post                  | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |   730.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 |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)
  • 查询 员工姓名中包含字母o的姓名和薪资
mysql> select * from emp where name like "%o%";
+----+----------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_data  | post    | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  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 |
+----+----------+--------+-----+------------+---------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)
  • 查询员工姓名是由六个字符组成的姓名和薪资
mysql> select name,salary from emp where name like "______";
+--------+----------+
| name   | salary   |
+--------+----------+
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
+--------+----------+
2 rows in set (0.00 sec)

mysql> select name,salary from emp where char_length(name) = 6;
+--------+----------+
| name   | salary   |
+--------+----------+
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
+--------+----------+
2 rows in set (0.00 sec)
  • 查询岗位描述为空的员工姓名和岗位名
mysql> select * from emp where post_comment is null;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_data  | post                  | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |   730.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 | 娜娜     | female |  69 | 2010-03-07 | sale                  | NULL         |   300.13 |    402 |         2 |
|  8 | 芳芳     | male   |  45 | 2014-05-18 | sale                  | NULL         |   400.45 |    402 |         2 |
|  9 | 小明     | male   |  34 | 2016-01-03 | sale                  | NULL         |   350.80 |    402 |         2 |
| 10 | 亚洲     | female |  42 | 2017-02-27 | sale                  | NULL         |   320.99 |    402 |         2 |
| 11 | 华华     | female |  55 | 2018-03-19 | sale                  | NULL         |   380.75 |    402 |         2 |
| 12 | 田七     | male   |  44 | 2023-08-08 | sale                  | NULL         |   420.33 |    402 |         2 |
| 13 | 大古     | female |  66 | 2018-05-09 | operation             | NULL         |   630.33 |    403 |         3 |
| 14 | 张三     | male   |  51 | 2019-10-01 | operation             | NULL         |   410.25 |    403 |         3 |
| 15 | 李四     | male   |  47 | 2020-05-12 | operation             | NULL         |   330.62 |    403 |         3 |
| 16 | 王五     | female |  39 | 2021-02-03 | operation             | NULL         |   370.98 |    403 |         3 |
| 17 | 赵六     | female |  36 | 2022-07-24 | operation             | NULL         |   390.15 |    403 |         3 |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
17 rows in set (0.00 sec)

【二】筛选条件之group by(分组)

mysql> select * from emp group by post;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'emp_data.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

  • 模糊查询所有严格模式

  • 关闭这个严格模式

  • 删除了 ONLY_FULL_GROUP_BY

  • 拿到每一个部门的第一行数据

    最小的操作单位应该是组，而不是组内的单个数据

    这条命令在没有设置严格模式的时候是可以执行的，返回的数据是每组的第一条数据

    但是分组不应该以单条数据为参考，而是要以组为操作单位

    如果设置了严格模式，上述命令会直接报错

    也就是上面的那个错误

mysql> 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)

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';

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql> select * from emp group by post;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_data  | post                  | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| 13 | 大古     | female |  66 | 2018-05-09 | operation             | NULL         |   630.33 |    403 |         3 |
|  7 | 娜娜     | 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         |   730.33 |    401 |         1 |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)

【1】获取每个部门的最高薪资(max)

  • 聚合函数：max - 取最大值
  • 可以利用as关键字给字段起别名，或者默认不写
  • 但是不推荐，如果忽略语义不明确，容易错乱
select post,max(salary) from emp group by post;
-- as别名
select post,min(salary) as "最低薪资" from emp group by post;

【2】获取每个部门的最低薪资(min)

  • 聚合函数：min- 取最大值
mysql> select post,min(salary) from emp group by post;
+-----------------------+-------------+
| post                  | min(salary) |
+-----------------------+-------------+
| operation             |      330.62 |
| sale                  |      300.13 |
| teacher               |    11000.80 |
| 陌夜痴梦久生情        |      730.33 |
+-----------------------+-------------+
4 rows in set (0.00 sec)

mysql>
mysql> select post,min(salary) as "最低薪资" from emp group by post;
+-----------------------+--------------+
| post                  | 最低薪资     |
+-----------------------+--------------+
| operation             |       330.62 |
| sale                  |       300.13 |
| teacher               |     11000.80 |
| 陌夜痴梦久生情        |       730.33 |
+-----------------------+--------------+
4 rows in set (0.00 sec)

【3】获取每个部门的平均薪资(avg)

  • 聚合函数：avg- 取最大值
mysql> select post,avg(salary) from emp group by post;
+-----------------------+--------------+
| post                  | avg(salary)  |
+-----------------------+--------------+
| operation             |   426.466000 |
| sale                  |   362.241667 |
| teacher               | 13000.722000 |
| 陌夜痴梦久生情        |   730.330000 |
+-----------------------+--------------+
4 rows in set (0.00 sec)

mysql> select post,avg(salary) as "平均薪资" from emp group by post;
+-----------------------+--------------+
| post                  | 平均薪资     |
+-----------------------+--------------+
| operation             |   426.466000 |
| sale                  |   362.241667 |
| teacher               | 13000.722000 |
| 陌夜痴梦久生情        |   730.330000 |
+-----------------------+--------------+
4 rows in set (0.00 sec)

【4】获取每个部门的薪资总和(sum)

  • 聚合函数：sum- 取最大值
mysql> select post,sum(salary) from emp group by post;
+-----------------------+-------------+
| post                  | sum(salary) |
+-----------------------+-------------+
| operation             |     2132.33 |
| sale                  |     2173.45 |
| teacher               |    65003.61 |
| 陌夜痴梦久生情        |      730.33 |
+-----------------------+-------------+
4 rows in set (0.00 sec)

mysql>
mysql> select post,sum(salary) as "薪资综合" from emp group by post;
+-----------------------+--------------+
| post                  | 薪资综合     |
+-----------------------+--------------+
| operation             |      2132.33 |
| sale                  |      2173.45 |
| teacher               |     65003.61 |
| 陌夜痴梦久生情        |       730.33 |
+-----------------------+--------------+
4 rows in set (0.00 sec)

【5】获取每个部门的人数(count)

  • 聚合函数：count- 取最大值
mysql> select post,count(salary) from emp group by post;
+-----------------------+---------------+
| post                  | count(salary) |
+-----------------------+---------------+
| operation             |             5 |
| sale                  |             6 |
| teacher               |             5 |
| 陌夜痴梦久生情        |             1 |
+-----------------------+---------------+
4 rows in set (0.00 sec)

mysql>
mysql> select post,count(id) as "部门总人数" from emp group by post;
+-----------------------+-----------------+
| post                  | 部门总人数      |
+-----------------------+-----------------+
| operation             |               5 |
| sale                  |               6 |
| teacher               |               5 |
| 陌夜痴梦久生情        |               1 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)

【6】查询分组之后的部门名称和每个部门下所有的员工姓名(group_concat)

  • 聚合函数：group_concat- 获得分组之后的具体的值
  • 不单单支持获取分组之后的其他字段值，还支持拼接操作
mysql> select post,group_concat(name) from emp group by post;
+-----------------------+-------------------------------------------+
| post                  | group_concat(name)                        |
+-----------------------+-------------------------------------------+
| operation             | 大古,张三,李四,王五,赵六                  |
| sale                  | 娜娜,芳芳,小明,亚洲,华华,田七             |
| teacher               | mengmeng,xiaomeng,xiaona,xiaoqi,suimeng   |
| 陌夜痴梦久生情        | dream                                     |
+-----------------------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql>
mysql> select post,name from emp group by post;
+-----------------------+----------+
| post                  | name     |
+-----------------------+----------+
| operation             | 大古     |
| sale                  | 娜娜     |
| teacher               | mengmeng |
| 陌夜痴梦久生情        | dream    |
+-----------------------+----------+
4 rows in set (0.00 sec)
  • 拼接数据
mysql> 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       |
| 陌夜痴梦久生情        | dream_drm                                                         |
+-----------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

【7】分组注意事项

  • where和group by可以同时使用，但是要注意顺序
-- 同时出现要有先后顺序 
-- where 先对整体过滤 group by 再对局部过滤
select * from * where * group by *;

【8】统计各部门年龄在 30 岁以上的员工的平均薪资

mysql> select name,age,post,salary from emp where age>30;
+----------+-----+-----------------------+----------+
| name     | age | post                  | salary   |
+----------+-----+-----------------------+----------+
| dream    |  78 | 陌夜痴梦久生情        |   730.33 |
| xiaomeng |  35 | teacher               | 15000.99 |
| suimeng  |  33 | teacher               | 14000.62 |
| 娜娜     |  69 | sale                  |   300.13 |
| 芳芳     |  45 | sale                  |   400.45 |
| 小明     |  34 | sale                  |   350.80 |
| 亚洲     |  42 | sale                  |   320.99 |
| 华华     |  55 | sale                  |   380.75 |
| 田七     |  44 | sale                  |   420.33 |
| 大古     |  66 | operation             |   630.33 |
| 张三     |  51 | operation             |   410.25 |
| 李四     |  47 | operation             |   330.62 |
| 王五     |  39 | operation             |   370.98 |
| 赵六     |  36 | operation             |   390.15 |
+----------+-----+-----------------------+----------+
14 rows in set (0.00 sec)

mysql> select group_concat(name,":",age),post,salary from emp where age>30 group by post;
+-------------------------------------------------------------+-----------------------+----------+
| group_concat(name,":",age)                                  | post                  | salary   |
+-------------------------------------------------------------+-----------------------+----------+
| 大古:66,张三:51,李四:47,王五:39,赵六:36                     | operation             |   630.33 |
| 娜娜:69,芳芳:45,小明:34,亚洲:42,华华:55,田七:44             | sale                  |   300.13 |
| xiaomeng:35,suimeng:33                                      | teacher               | 15000.99 |
| dream:78                                                    | 陌夜痴梦久生情        |   730.33 |
+-------------------------------------------------------------+-----------------------+----------+
4 rows in set (0.00 sec)

mysql> select post,avg(salary) from emp where age>30 group by post;
+-----------------------+--------------+
| post                  | avg(salary)  |
+-----------------------+--------------+
| operation             |   426.466000 |
| sale                  |   362.241667 |
| teacher               | 14500.805000 |
| 陌夜痴梦久生情        |   730.330000 |
+-----------------------+--------------+
4 rows in set (0.00 sec)

【三】筛选条件之having(分组之后筛选)

  • having的语法和where是一致的
  • 只不过having是在分组之后进行的过滤操作
  • 即having是可以直接使用聚合函数的

【1】统计各部门年龄 30 岁以上的员工的工资，并且保留平均薪资大于1w的部门

  • 查询数据
mysql> 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)

【四】筛选条件之distinct(去重)

  • 必须是完全一样的数据才可以去重
  • 一定要注意主键的问题
  • 在主键存在的情况下是一定不可能去重的

【1】对emp表中的age和id去重

select distinct id,age from emp;
mysql> select distinct id,age from emp;
+----+-----+
| id | age |
+----+-----+
|  1 |  78 |
|  2 |  25 |
|  3 |  35 |
|  4 |  29 |
|  5 |  27 |
|  6 |  33 |
|  7 |  69 |
|  8 |  45 |
|  9 |  34 |
| 10 |  42 |
| 11 |  55 |
| 12 |  44 |
| 13 |  66 |
| 14 |  51 |
| 15 |  47 |
| 16 |  39 |
| 17 |  36 |
+----+-----+
17 rows in set (0.00 sec)

【2】只对emp表中的age去重

mysql> 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(排序)

  • order by : 默认是升序
  • asc 默认可以省略不写 ---> 修改降序
  • desc : 降序

【1】将emp表中的数据按照薪资排序(升序)

mysql> select * from emp order by salary;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_data  | post                  | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
|  7 | 娜娜     | female |  69 | 2010-03-07 | sale                  | NULL         |   300.13 |    402 |         2 |
| 10 | 亚洲     | female |  42 | 2017-02-27 | sale                  | NULL         |   320.99 |    402 |         2 |
| 15 | 李四     | male   |  47 | 2020-05-12 | operation             | NULL         |   330.62 |    403 |         3 |
|  9 | 小明     | male   |  34 | 2016-01-03 | sale                  | NULL         |   350.80 |    402 |         2 |
| 16 | 王五     | female |  39 | 2021-02-03 | operation             | NULL         |   370.98 |    403 |         3 |
| 11 | 华华     | female |  55 | 2018-03-19 | sale                  | NULL         |   380.75 |    402 |         2 |
| 17 | 赵六     | female |  36 | 2022-07-24 | operation             | NULL         |   390.15 |    403 |         3 |
|  8 | 芳芳     | male   |  45 | 2014-05-18 | sale                  | NULL         |   400.45 |    402 |         2 |
| 14 | 张三     | male   |  51 | 2019-10-01 | operation             | NULL         |   410.25 |    403 |         3 |
| 12 | 田七     | male   |  44 | 2023-08-08 | sale                  | NULL         |   420.33 |    402 |         2 |
| 13 | 大古     | female |  66 | 2018-05-09 | operation             | NULL         |   630.33 |    403 |         3 |
|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |   730.33 |    401 |         1 |
|  4 | 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 |
|  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 |
|  3 | xiaomeng | male   |  35 | 2019-06-07 | teacher               | NULL         | 15000.99 |    401 |         1 |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
17 rows in set (0.00 sec)

【2】将emp表中的数据按照薪资排序(降序)

mysql> select * from emp order by salary desc;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_data  | post                  | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
|  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 |
|  5 | 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 |
|  4 | xiaona   | female |  29 | 2018-09-06 | teacher               | NULL         | 11000.80 |    401 |         1 |
|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |   730.33 |    401 |         1 |
| 13 | 大古     | female |  66 | 2018-05-09 | operation             | NULL         |   630.33 |    403 |         3 |
| 12 | 田七     | male   |  44 | 2023-08-08 | sale                  | NULL         |   420.33 |    402 |         2 |
| 14 | 张三     | male   |  51 | 2019-10-01 | operation             | NULL         |   410.25 |    403 |         3 |
|  8 | 芳芳     | male   |  45 | 2014-05-18 | sale                  | NULL         |   400.45 |    402 |         2 |
| 17 | 赵六     | female |  36 | 2022-07-24 | operation             | NULL         |   390.15 |    403 |         3 |
| 11 | 华华     | female |  55 | 2018-03-19 | sale                  | NULL         |   380.75 |    402 |         2 |
| 16 | 王五     | female |  39 | 2021-02-03 | operation             | NULL         |   370.98 |    403 |         3 |
|  9 | 小明     | male   |  34 | 2016-01-03 | sale                  | NULL         |   350.80 |    402 |         2 |
| 15 | 李四     | male   |  47 | 2020-05-12 | operation             | NULL         |   330.62 |    403 |         3 |
| 10 | 亚洲     | female |  42 | 2017-02-27 | sale                  | NULL         |   320.99 |    402 |         2 |
|  7 | 娜娜     | female |  69 | 2010-03-07 | sale                  | NULL         |   300.13 |    402 |         2 |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
17 rows in set (0.00 sec)

【3】将emp表中的数据按照薪资(升序)和年龄(降序)排序

  • order by 后面可以跟多个参数
  • 先按照age降序排
  • 如果碰到 age 相同 ，再按照salary 升序排
mysql> select * from emp order by age desc,salary asc;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_data  | post                  | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |   730.33 |    401 |         1 |
|  7 | 娜娜     | female |  69 | 2010-03-07 | sale                  | NULL         |   300.13 |    402 |         2 |
| 13 | 大古     | female |  66 | 2018-05-09 | operation             | NULL         |   630.33 |    403 |         3 |
| 11 | 华华     | female |  55 | 2018-03-19 | sale                  | NULL         |   380.75 |    402 |         2 |
| 14 | 张三     | male   |  51 | 2019-10-01 | operation             | NULL         |   410.25 |    403 |         3 |
| 15 | 李四     | male   |  47 | 2020-05-12 | operation             | NULL         |   330.62 |    403 |         3 |
|  8 | 芳芳     | male   |  45 | 2014-05-18 | sale                  | NULL         |   400.45 |    402 |         2 |
| 12 | 田七     | male   |  44 | 2023-08-08 | sale                  | NULL         |   420.33 |    402 |         2 |
| 10 | 亚洲     | female |  42 | 2017-02-27 | sale                  | NULL         |   320.99 |    402 |         2 |
| 16 | 王五     | female |  39 | 2021-02-03 | operation             | NULL         |   370.98 |    403 |         3 |
| 17 | 赵六     | 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 | 小明     | male   |  34 | 2016-01-03 | sale                  | NULL         |   350.80 |    402 |         2 |
|  6 | 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 |
|  5 | 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 |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
17 rows in set (0.00 sec)

【4】混合排序

  • 统计各部门年龄在 30 岁以上的员工的工资，并且保留平均薪资大于1000的部门，对平均工资进行排序
mysql> select avg(salary) from emp
    ->  where age > 30
    ->  group by post
    ->  having avg(salary) > 400
    ->  order by avg(salary) desc
    ->  ;
+--------------+
| avg(salary)  |
+--------------+
| 14500.805000 |
|   730.330000 |
|   426.466000 |
+--------------+
3 rows in set (0.00 sec)

【六】筛选条件之limit（限制展示条数）

  • 针对数据太多的情况，我们大都是做分页处理
  • limit x,y : 第一个参数是起始位置，第二个是条数

【1】查询数据方式一：单数字限制

select * from emp limit 10;
mysql> select * from emp limit 10;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_data  | post                  | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |   730.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 | 娜娜     | female |  69 | 2010-03-07 | sale                  | NULL         |   300.13 |    402 |         2 |
|  8 | 芳芳     | male   |  45 | 2014-05-18 | sale                  | NULL         |   400.45 |    402 |         2 |
|  9 | 小明     | male   |  34 | 2016-01-03 | sale                  | NULL         |   350.80 |    402 |         2 |
| 10 | 亚洲     | female |  42 | 2017-02-27 | sale                  | NULL         |   320.99 |    402 |         2 |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
10 rows in set (0.00 sec)

【2】查询数据：多限制

select * from emp limit 0,6;
mysql> select * from emp limit 0,6;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_data  | post                  | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |   730.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 后面 取六条

第一个参数是起始位置，第二个是条数

【七】筛选条件之正则

【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】演示

  • 创建表
DROP TABLE IF EXISTS `person`;

mysql> 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;
Query OK, 0 rows affected (0.04 sec)

  • 插入数据

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, '男');

# 查询 name 字段以j开头的记录
select * from person where name REGEXP '^j';

select * from person where name REGEXP 'y$';

select * from person where name REGEXP 'Th*';

# SQL语句中的正则表达式并不完善，所以功能不全
select * from person where name REGEXP '....';

标签：male,MySQL,sale,过滤,female,条件,post,NULL,teacher
From： https://www.cnblogs.com/Fredette/p/18002011

相关文章

  • MySQL介绍
    一、数据库的介绍，什么是数据库数据库其实就是一块基于网络通信的应用程序每个人都有开发一块数据库的能力【1】关系型数据库MySQLOracledb2accesssqlserver这些数据库都采用关系模型来组织数据，并且支持SQL查询语言。【2】非关系型数据库RedisMongoDBMemcache......
  • 关于Qt MySQL有QMYSQL驱动，但是Driver not loaded问题。
    首先，说一下我当时的问题，控制台显示有QMYSQL驱动，但是就是检测不到报错没有驱动，数据库连接不成功。 一开始是一直在复制粘贴.dll和.lib,但是还是不行，一直试一直拖好久都没解决这个问题。因为我sqlite可以用，所以也不着急搞MySQL，但是每次一有时间，看到MySQL不能使就不舒服，就像再调......
  • mysql: error while loading shared libraries: libncurses.so.6: cannot open shared
    查找没有libncurses.so.5，可能有libncurses.so不同版本的文件，使用命令find/-name'libncurses*'复制+覆盖软链接先复制到lib64文件夹，再用软链接重新覆盖一下[root@lab-aliyunwzx]#cp/home/wll/miniconda3/lib/libncurses.so.6/lib64[root@lab-aliyunwzx]#cd/lib6......
  • 软件测试/测试开发/全日制|MySQL安装最全教程
    MySQL的下载与安装、基本使用、系统服务制作MySQL简介MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。安装与下载1、下载流程访问官方（www.mysql.com）点击‘DOWNLOADS’，进入下载界面下拉，找到‘My......
  • pandas - .shape[0] 返回匹配条件的行数
    #df=pd.read_excel(r"D:\PyCharm\年度数据处理\1月设备离线01.xlsx",sheet_name='Sheet1')#将日期列转换成pandas的datetime类型#df['解除时间']=pd.to_datetime(df['解除时间'])##统计固定日期出现的次数#target_date=pd.to_datetime('2025-01-......
  • python连接mysql8、sqlserver2012
    python连接mysql比较顺利，网上很多代码，连接sqlserver的例子少，且很多错误，尝试很多帖子，最后成功代码如下。#连接mysqlimportpymysqldefconn():try:connection=pymysql.connect(host='localhost',port=3306,user='root',password='123456',database='cl&#......
  • 安装MySQL出现由于找不到vcruntime140_1.dll,无法继续执行代码的提示
    问题描述：在安装MySQL服务的时候，执行安装命令提示如下的错误信息。解决方法：通过分析可以知道，是由于缺少了vcruntime140_1.dll动态链接库文件，这是windows缺少vc_redist.x64.exe程序导致的服务安装错误，与我们要安装的MySQL服务并没有关系。（如果您的安装过VS类型的工具，就不会提示该......
  • CentOs在线安装MySQL最新版本
    更新系统yumupdate-y清理YUM软件包管理器的缓存(包括软件包文件和元数据,可以释放磁盘空间并删除旧的缓存数据)yumcleanall添加MySQLYumRepository：MySQL提供了官方的Yum存储库，可以通过它安装最新的MySQL版本。运行以下命令添加MySQL存储库：yuminstall-y......
  • MySQL的安装目录。
    1.MySQL安装完成后，会在磁盘上生成一个目录，该目录就被称为MySQL的安装目录。主目录下面有一些子目录，为了更好的使用MySQL数据库，需要了解各级目录的功能2.目录结构如下：1)bin文件夹：用于放置一些可执行文件.exe，如mysql.exe,mysqld.exe 2)include文件夹用于放置一些头文件.......
  • Windows下修改MySQL密码。
    1.win+r  cmd回车 2.cd+MySQL中bin的路径，切换到mysql中 3.通过mysql-u用户名-p指定root用户登录mysql，回车后会提示输入密码，此时输入的是原来的旧密码 4.修改MySQL的用户密码，格式：setpasswordfor用户名@localhost=password('新密码');修改成root如下： 出......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99