本部分内容主要包括：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;
