What is a single table query?
- 藏色散人Original
- 2020-07-01 09:23:255887browse
Single table query refers to querying data in one table. Its execution order is "from->where->group by->having->distinct->order by- >limit->select".
In database operations, single table query is to query data in one table. Its detailed syntax is:
select distinct 字段1，字段2... from 表名 where 分组之前的过滤条件 group by 分组字段 having 分组之后的过滤条件 order by 排序字段 limit 显示的条数;
The syntax is in this order, but its execution order is not based on the order of the syntax, but in this order.
from--->where--->group by--->having-->distinct--->order by--->limit--- >select
As for the reason for such an execution sequence, I won’t say it, and I don’t have the confidence to explain it clearly. If you are a novice, you only need to remember this execution sequence. If you have to get to the bottom of it, you can go to Google.
Before understanding the single table query, we first create an employee table:
emp表： 员工id id int 姓名 emp_name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 职位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int
Build the table:
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 );
Insert data:
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('niange','male',23,'20170301','manager',15000,401,1), ('monicx','male',23,'20150302','teacher',16000,401,1), ('wupeiqi','male',25,'20130305','teacher',8300,401,1), ('yuanhao','male',34,'20140701','teacher',3500,401,1), ('anny','female',48,'20150311','sale',3000.13,402,2), ('monke','female',38,'20101101','sale',2000.35,402,2), ('sandy','female',18,'20110312','sale',1000.37,402,2), ('chermy','female',18,'20130311','operation',19000,403,3), ('bailes','male',18,'20150411','operation',18000,403,3), ('omg','female',18,'20140512','operation',17000,403,3);
where conditional filtering
Can be used in where clauses:
1. Comparison operators: >, 95ec6993dc754240360e28e0de8de30a=, 9bfa661c0ebd62c5f7d180e0bd343b78 ,!=.
2. between 1 and 5 The value is between 1 and 5.
3. in(1,3,8) value is 1 or 3 or 8.
4. like 'monicx%'
% represents any number of characters
_ represents one character
5. Logical operators: Logical operators can be used directly in multiple conditions and, or, not.
6, Regular expression
Find the names of employees with IDs between 2 and 5:
Query the names of employees whose names contain the letter y and their salaries:
Query the names of employees whose names are composed of four characters Its salary:
Query the employee name and position name with empty job description:
Search for employees whose names start with the letter m and end with the letter e or x! You can use regular expressions at this time. Mysql provides regexp to express regular expressions.
group by group
First set the sql_mode of mysq to only_full_group_by, which means that in the future, only the basis for grouping can be obtained .
set global sql_mode="strict_trans_tables,only_full_group_by";
The grouping occurs after where, that is, the grouping is based on the records obtained after where.
Grouping refers to classifying all records according to a certain same field, such as grouping positions in employee information tables, or grouping according to gender, etc.
How to group?
For example: take the highest salary of each department.
For example: Get the number of employees in each department.
The field after the word "every'" is the basis for our grouping.
Note: We can group by any field, but after grouping, such as group by post, we can only view the post field.
But if you want to view the information within the group, you need to use Aggregation (gather together to synthesize one content)Function
每个部门的最高工资 select post,max(salary) from emp group by post; 每个部门的最底工资 select post,min(salary) from emp group by post; 每个部门的平均工资 select post,avg(salary) from emp group by post; 每个部门的工资总合 select post,sum(salary) from emp group by post; 每个部门的总人数 select post,count(id) from emp group by post;
group_concat (Used after grouping to obtain the contents of the fields in the group.)
And it can also be like this Sub-Usage:
You can try it yourself with the following code:
select post,group_concat(name) from emp group by post; select post,group_concat(name,"_NB") from emp group by post; select post,group_concat(name,": ",salary) from emp group by post; select post,group_concat(salary) from emp group by post;
Wise classmates will say that it can also be used without grouping. ? no! But mysql provides another way to operate. It is concat.
# 补充as语法 mysql> select emp.id,emp.name from emp as t1; # 报错 mysql> select t1.id,t1.name from emp as t1;
group by That’s all. If you still don’t understand, you can do the following small exercises.
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post;
3. 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;
8、统计各部门年龄在30岁以上的员工平均工资
select post,avg(salary) from emp where age >= 30 group by post;having filter
The syntax format of having is exactly the same as where, except that having is further filtered after grouping.
where不能用聚合函数，但having是可以用聚合函数，这也是它们最大的区别。
统计各部门年龄在24岁以上的员工平均工资，并且保留平均工资大于4000的部门。
注意：having只能与 select 语句一起使用。
having通常在 group by 子句中使用。
如果不使用 group by子句，不会报错，但会出现以下的情况。
distinct去重
order by 排序
select * from emp order by salary asc; #默认升序排 select * from emp order by salary desc; #降序排 select * from emp order by age desc; #降序排 select * from emp order by age desc,salary asc; #先按照age降序排，再按照薪资升序排
limit 限制显示条数
如查要获取工资最高的员工的信息，我们可以用order by和limit也可以做到。
如果查一个表数据量大的话可以用limit分页显示。
select * from emp limit 0,5;
select * from emp limit 5,5;
ps:看到这里如果上面的东西你都明白的话，单表查询你基本上已经熟悉它了。
The above is the detailed content of What is a single table query?. For more information, please follow other related articles on the PHP Chinese website!