mysql多条件分组并排序_三、p18-28条件查询、分组聚合、排序where/group by/having/order by...

本部分内容主要包括：MySQL中常用的条件查询语句(where)、模糊查询语句(like)、分组语句(group by)、聚合函数(having)、排序语句(order by)。

P18 简单查询语句

1.查询指定的列的数据：

select 列名1,列名2,… From 表名;注：select * from table student;不对，select不跟table关键字

2.条件查询：在查询时给出where子句，在where子句中可以使用一些运算符号及关键字；

SELECT * from student WHERE id = 1001;

SELECT * from student WHERE id =1001 AND stu_age = 3;

P19 灵活运用上述判断字符：

• select * from student where stu_gender = '男' and stu_age = 20;

• select * from student where stu_gender = 1001 or stu_name = 'zs';

• select * from student where id =1001 or id=1002 or id=1003;

或者 select * from student where id between 1001 and 1003;【包括头尾】

或者 select * from student where id in(1001,1002,1003);

• select * from student where stu_age is NULL;数值型缺省时是null，字符型缺省时是空字符串' '和null不一样；

• select * from student where stu_age >=18 and stu_age<=20;

或者select * from student where stu_age between 18 and 20;

• select * from student where gender != '男';

• select * from student where stu_name is not null;

查询空字符串的记录：select * from student where stu_name = ' ';s

P20 模糊查询(给一个通配符可以代替一些字符)查询姓名由五个字母构成的学生记录：

select * from student where stu_name like '_____';

• 查询姓名由5个字符构成且第五个字符为e的学生记录

select * from student where stu_name like '____e';

• 查询姓名以“m”开头的学生记录

select * from student where stu_name like 'm%';

• 查询姓名中第二个字母为u的学生记录

select * from student where stu_name like '_u%';

• 查询姓名中包含“s”字母的学生记录

select * from student where stu_name like "%s%"；

P21 字段控制查询去重查询：select distinct stu_name from student;把年龄和分数相加：

select stu_age,stu_score,stu_age+stu_score from student;对字段ifnull(XXX,某个值)处理之后：

select stu_age,stu_score,ifnull(stu_age,1)+ifnull(stu_score,0) FROM student;做完处理之后列名太长作为一个别名：

select stu_age,stu_score,ifnull(stu_age,1)+ifnull(stu_score,0) as res FROM student;

P22 排序语句让选出来的数据从小到大的升序排列：SELECT * from employee ORDER BY salary;让选出来的数据从大到小的降序排列：select * from employee order by salary desc;两层排序：查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

select * from employee order by salary desc, id asc;

P23 聚合函数(对查询的结果进行统计)-进行统计时注意使用ifnull：查询employee表中记录数(返回一个数字)：select count(*) from employee;查询employee表中绩效manage不为空的记录数：

select count(manage) from employee where manage is not null;统计员工表中月薪大于2500的人数：

SELECT COUNT(*) from employee WHERE salary > 2500;统计月薪和绩效之和大于5000的人数：

select count(*) from employee WHERE salary+ifnull(performance,0)>2500;最后一个：select count(manage), count(performance) from employee;

• select sum(salary) from employee;

• select sum(salary),sum(performance) from employee;

• select sum(ifnull(salary,0)+ifmull(performance,0)) from employee;

• select avg(ifnull(salary,0)+ifnull(performance,0)) as avga from employee;

最大工资和最小工资：select max(salary),min(salary) from employee;

P25 分组操作(将查询结果按一个或多个字段分组，字段值相同分为一组)

原来的表：

• 希望把记录按照男女去分组：

select * from employee group by gender;单独使用只会出现第一条记录；

• 按照性别分组之后，这些人的名字分别叫什么：

select group_concat(name) from employee group by gender;

P26 分组与聚合函数【重点关注】按照男女分组出来姓名和薪水(此时还是只是简单地堆积在一起)再用聚合函数进行统计(是按组里面的统计的)：查询每个部门部门名称及每个部门人数：【切记看到“每个”这个字眼后面的被修饰词就是要分组】：

也可以写成：select department, group_concat(name), count(*) from employee group by department;查询每个部门的部门名称及每个部门薪资大于1500的人数：【count(*)，sum(*)不对，sum只能针对某个字段】

select department,group_concat(salary) from employee where salary>1500 group by department;

P27 group by 加上having【对分组后的结果再进行筛选,having不能离开group by】；where在group by之前

select department,group_concat(salary),sum(salary) from employee where salary>1500 group by department;对分组后的结果再进行筛选：

select department,group_concat(salary),sum(salary) from employee where salary>1500 group by department having sum(salary)>9000;

P28 having和where的区别

注意点!!!：where后面不能用聚合函数【where sum(salary)>900错】，having后可以；查询工资大于2000的，工资总和大于6000的部门名称以及工资和：

select department, sum(salary) from employee where salary>2000 group by department having sum(salary)>6000;还可以在后面加上order by；

select department, sum(salary) from employee where salary>2000 group by department having sum(salary)>6000 ORDER BY SUM(salary) DESC;