mysql8 查询关键字

君自故乡来，应知故乡事 / 2023-07-12 / 原文

一、查询关键字

1、数据准备

> create table emp(
                         ->   id int primary key  auto_increment,
                         ->   name varchar(20)  not null,
                         ->   sex enum('male','female')  not null default  'male',
                         ->   age smallint(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
                         -> );

插入数据

insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('tom','male',78,'20150302','teacher',1000000.31,401,1),#以下是教学部
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

2、查询关键字之where筛选

模糊查询:没有明确的筛选条件
关键字:like
关键符号:
　　%:匹配任意个数任意字符
　　_:匹配单个个数任意字符
show variables like '%mode%se';

1. 查询id大于等于3小于等于6的数据

> select * from emp where id >= 3 and id <=6;

> select * from emp where id between 3 and 6;

2. 查询薪资是20000或者18000或者17000的数据

> select * from emp where salary=20000 or salary = 18000 or salary  =17000;

> select * from emp where salary in (20000,18000,17000);

3. 查询员工姓名中包含o字母的员工姓名和薪资

> select name, salary from emp where name like '%o%';

> select name, salary from emp where name like 'o%';

> select name, salary from emp where name like '%o';

4. 查询员工姓名是由四个字符组成的员工姓名与其薪资

> select name, salary from emp where name like '____';

> select name, salary from emp where char_length(name)=4;

5. 查询id小于3或者大于6的数据

> select *  from emp where id < 3 or id > 6;

> select *  from emp where id not between  3 and 6;

6. 查询薪资不在20000，18000，17000范围的数据

> select *  from emp where salary  not in (20000, 18000, 17000);

7. 查询岗位描述为空的员工名与岗位名针对null不能用等号，只能用is

> select name,post from emp where post_comment = NULL;  # 查询为空

> select name,post from emp where post_comment is NULL;

> select name,post from emp where post_comment is not NULL;

'''在sql中，NULL和''不一样'''
# 假如字段想设置为空，该如何设置?
最好设置为'',不要使用NULL
尤其是两者混用

3、查询关键字之group by分组

GROUP BY 是一种用于对查询结果进行分组的语句，通常与聚合函数（如 sum max min avg count 等）一起使用，以便在每个组中计算聚合值。

当使用 GROUP BY 语句时，查询结果将根据指定的列或表达式进行分组。相同的值将被分配到同一组中。然后，可以对每个组应用聚合函数，以计算该组的聚合结果。

分组之后，得到的每一个分组中的第一条数据

1. 按部门分组

select post from emp group by post;

2. 每个部门的最低工资

> select post, min(salary) from emp group by post;

3. 每个部门的平均工资

select post, avg(salary) from emp group by post;

4. 每个部门的工资总和

select post, sum(salary) from emp group by post;

5. 每个部门的人数

> select post, count(*) from emp group by post;

> select post, count(id) from emp group by post;

> select post, count(1) from emp group by post;

注意：

* 表示取最长的一列 id 表示取id那一列 1 表示取第一列

6、配合分组使用的其他函数，分组之后只能获取到分组的依据，获取分组之外的字段

group_concat: 用在分组之后

GROUP_CONCAT() 是 MySQL 8 中的一个聚合函数，用于将多行数据中的某一列的值拼接成一个字符串。

GROUP_CONCAT([DISTINCT] expr [, expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

实例：

SELECT GROUP_CONCAT(name,age  SEPARATOR ',') AS names  FROM emp;

+---------------------------------------------------------------------------------------------------------------------------------+
| names |
+---------------------------------------------------------------------------------------------------------------------------------+
| tom78,kevin81,tony73,owen28,jack18,jenny18,sank48,哈哈48,呵呵38,西西18,乐乐18,拉拉28,僧龙28,程咬金18,程咬银18,程咬铜18,程咬铁18 |
+---------------------------------------------------------------------------------------------------------------------------------+

concat：不分组使用

CONCAT() 是 MySQL 8 中的一个字符串函数，用于将多个字符串拼接在一起

> select concat(name, sex) from emp;

> select concat(name,'|', sex) from emp;
+-----------------------+
| concat(name,'|', sex) |
+-----------------------+
| tom|male              |
| kevin|male            |
| tony|male             |
| owen|male             |
| jack|female           |
| jenny|male            |
| sank|male             |
| 哈哈|female           |
| 呵呵|female           |
| 西西|female           |
| 乐乐|female           |
| 拉拉|female           |
| 僧龙|male             |
| 程咬金|male           |
| 程咬银|female         |
| 程咬铜|male           |
| 程咬铁|female         |
+-----------------------+

concat_ws()

CONCAT_WS() 是 MySQL 8 中的一个字符串函数，用于将多个字符串拼接在一起，并使用指定的分隔符分隔它们。语法： CONCAT_WS(separator, str1, str2, ..., strN)

CONCAT_WS() 还可以用于连接表中的列, SELECT CONCAT_WS('-', first_name, last_name) AS full_name FROM employees;

示例：

> SELECT CONCAT_WS('-', name, age) AS full_name FROM emp;

+-----------+
| full_name |
+-----------+
| tom-78    |
| kevin-81  |
| tony-73   |
| owen-28   |
| jack-18   |
| jenny-18  |
| sank-48   |
| 哈哈-48   |
| 呵呵-38   |
| 西西-18   |
| 乐乐-18   |
| 拉拉-28   |
| 僧龙-28   |
| 程咬金-18 |
| 程咬银-18 |
| 程咬铜-18 |
| 程咬铁-18 |
+-----------+

select post,concat_ws('|', name, age, gender) from emp group by post;

7、查询每个部门的最高工资

> select post as '部门',max(salary) as '最高工资' from emp group by post;

+-----------+------------+
| 部门      | 最高工资   |
+-----------+------------+
| teacher   | 1000000.31 |
| sale      |    4000.33 |
| operation |   20000.0  |
+-----------+------------+

4、关键字之having过滤，

having也是用来筛选数据的，功能上跟where是一样的，where 用在分组之前，having用在分组之后再筛选

统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门。

4.1 各部门年龄在30岁以上的员工平均薪资

> select avg(salary) from emp where age >30 group by post;
+-------------+
| avg(salary) |
+-------------+
| 255450.0775 |
|   2500.24   |
+-------------+

4.2 保留平均薪资大于10000的部门(having 用在分组之后再筛选)

> select avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;

+-------------+
| avg(salary) |
+-------------+
| 255450.0775 |
+-------------+

5、关键字之distinct去重

对有重复的展示数据进行去重操作

> select distinct id,age from emp;

+----+-----+
| id | age |
+----+-----+
| 1  | 78  |
| 2  | 81  |
| 3  | 73  |
| 4  | 28  |
| 5  | 18  |
| 6  | 18  |
| 7  | 48  |
| 8  | 48  |
| 9  | 38  |
| 10 | 18  |
| 11 | 18  |
| 12 | 28  |
| 13 | 28  |
| 14 | 18  |
| 15 | 18  |
| 16 | 18  |
| 17 | 18  |
+----+-----+

> select distinct post from emp;
+-----------+
| post      |
+-----------+
| teacher   |
| sale      |
| operation |
+-----------+

6、关键字之order by排序

1、升序和降序

> select * from emp order by salary ;  # 默认就是升序ASC
> select * from emp order by salary desc;  # 降序

2、先按照age降序排，在年轻相同的情况下再按照薪资升序排

order by 排序字段1 desc, 排序字段2;

> select * from emp order by age desc, salary;

3、统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

> select  avg(salary) as avg_salary from emp where age > 10 group by post having avg(salary) > 1000  order by avg(salary) desc ;

+---------------+
| avg_salary    |
+---------------+
| 151842.901429 |
|  16800.026    |
|   2600.294    |
+---------------+

7、关键字之limit分页

查询工资最高的人的详细信息

> select * from emp order by  salary desc limit 1;

limit 的分页参数

select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5; # 第一个参数代表的是起始位置，第二个参数是总的要查条数

8、关键字之regexp正则

> select * from emp where name regexp  '^j.*(n|y)$';

+----+-------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name  | sex  | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+-------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 6  | jenny | male | 18  | 1900-03-01 | teacher | <null>       | 30000.0 | 401    | 1         |
+----+-------+------+-----+------------+---------+--------------+---------+--------+-----------+

二、

三、