05.单表查询关键字

 

数据准备

        今天通过一张表格来详细讲解各类单表查询关键字。首先来制作一张表格。

　　这张表格中要插入很多的数据，在cmd里一行一行手打显然是不太好用了，所以还是推荐先在文档里将需要输入的命令都写下来然后一起复制进cmd里。

　　提示：涉及到使用命令操作的时候，最好在写完每行命令后检查一下。

　　插入数据之后可以检查一下：

　　在cmd窗口不够大时，表格的篇幅被压缩导致看起来格式紊乱，其实只要放大窗口就没问题了（前提是显示屏足够大）。或者也可以让表中的数据分段呈现。

select * from emp\G;  # \G可以让数据分段呈现

　　准备工作做好了，接下来可以正式讲解了。

 

单表查询关键字

1.where筛选

　　第一个要讲解的是老熟人"where"。where的作用是用于查询指定的数据，其查询模式也有多种。

1.查找id大于等于3小于等于6的数据（范围查询/精确查询）

select * from emp where id>=3 and id<=6;
select * from emp where id between 3 and 6; # 也可以使用between and来做范围查询的关键字

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);  # （类似python中的成员运算）

　　不难看出，两种SQL语句，得出的答案却是一样的。

　　这也是计算机编程的一大魅力：相同的结果，方式可以不一样。

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

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

　　这里可以展开说说，模糊查询需要用到关键字like，之前在讲解如何开启严格模式的时候也用到过。

　　配合like语句使用的还有其他关键符号：

　　%：匹配任意个数的任意字符

例：找一本三开头的书

三%

查找到：三国演义、三国志、三毛流浪记……

 

_：匹配单个个数的任意字符

例：找一本三开头的书

三_

查找到：三体

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 not between 3 and 6;

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

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

7.查询岗位描述为空的员工名与岗位名

select * from emp where post_comment=null;
select * from emp where post_comment is null;

2.group by分组

　　分组是指按照指定条件将单个单个的个体分成一个个整体。

　　例如：

按照性别将人分为男性和女性

按照部门将员工分为多个部门

按照年龄将人分为各个年龄段

1.按部门分组

select * from emp group by post;

　　真奇怪，输入命令后返回的结果只显示每个组的第一条数据，后面的数据都没有了。

　　注意：分组之后默认只可以直接获取到分组的头条数据，无法获取内部单个个体的数据。如果想要获取单个个体数据，需要借助于其他方法。

　　另外，在5.7之后的版本自带严格模式，打这条命令就会报错。

select post from emp group by post;

　　通过分组，可以执行很多操作，不过这些操作大多数不属于分组的范畴，而是称为聚合函数。

聚合函数（配合group by使用）

 

　　聚合函数主要用于分组之后的数据处理。其实也都是些熟面孔，在excel中常常用到。

 

max最大值
min最小值
avg平均值
sum求和
count计数

1.获取每个部门的最高薪资

 

　　诀窍：在查询题目时，需不需要分组其实是有关键字的，最典型的题眼就是“每个部门、每个国家、每个省份”，分组就按照每个后面的词来分。

select post,max(salary) 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(id) from emp group by post;

 

 

　　在使用count函数时，推荐使用主键字段，非主键字段有时候可能会显示奇怪的计数。

 

3.having过滤

 

　　having与where的功能都是筛选数据。区别在于：

where用于分组之前的筛选

having用于分组之后的过滤

 

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

 

# 1.统计各部门平均工资
select post,avg(salary) from emp group by post;
# 2.在分组之前先对数据进行筛选
select post,avg(salary) from emp where age > 30 group by post;
# 3.分组之后还要对数据进行过滤
select post,avg(salary) from emp
where age > 30
group by post
having avg(salary) > 10000;

 

 

4.distinct去重

 

　　去重就是删除重复项。去重的前提是数据必须一模一样才可以。

 

select distinct * from emp;  # 此时没有重复元素，只会把列表重新打印一遍
select distinct id,age from emp;
select distinct post from emp;

 

5.order by排序

 

1.将员工按照薪资排序

 

select * from emp order by salary;  # 默认是升序（从小到大，关键字asc）
select * from emp order by salary desc;  # 降序

 

2.先将员工按年龄升序排列，然后按薪资降序排列

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

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

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

6.limit分页

select * from emp limit 5;  # 单个数字表示只展示5条数据
select * from emp limt 5,10;  # 第一个数字表示起始位置（实际上可以看成一个等差数列），第二个数字表示条数

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

select * from emp order by salary desc limit 1;

　　可以发现，不分组时聚合函数依然可以用，因为不分组时整个表就被视为一个组。所以解题思路就是先按照工资降序排序，然后使用limit限制取第一条。

7.regexp正则

　　通过使用一些特殊符号的组合取字符串中筛选出符合条件的数据（爬虫大量使用，现在先只做了解）

eg:
select * from emp where name regexp '^j.*(n|y)$';  # 查询以字母j开头字母n或y结尾的姓名