数据库一系列查询函数

以表msg为源数据库进行操作

1、where

• select *from msg where id=3;
• select name,age from msg where id in(3,5); //id=3,id=5的
• select name,age from msg where id between 3 and 5;（包括边界，3和5之间）
• select name,age from msg where salary<95 or salary >110;
• 模糊查询：select id,name,salary from msg where name like(“血%”);通配任意字符

2、group

• 找出薪水最高的人：select max(salary) from msg;
  注意：select id,name,max(salary) from msg; //是错误的，只能查出薪水最高的值，并不能和id，name对应。
• select age,max(salary) from msg group by age;
• 查出薪水总和:select sum(salary) from msg;
• 查出行数：select count(*) from msg;
• 查出平均薪水：select avg(salary)from msg ; select sum(salary)/count(*) from msg;
• -

3.having

    数据在表中，表在硬盘或者内存以文件形式存在，
    where就是针对文件发挥作用的
    查询出的结果也可以看成一张表，其文件一般临时存在于缓冲区
    having 就是针对查询的结果发挥作用

• 查询出薪水比市场价格低多少，并且把低于200元以下的选出来

(1)select id,name,email,avg_salary-salary as sheng from php313;
(2) select id,name,email,avg_salary-salary as sheng from php313 where sheng >200; //错误 ,要对结果进行查询，需要having
修正：select id,name,email,avg_salary-salary as sheng from php313 where avg_salary-salary <200;
或者：select id,name,email,ang_salary-salary as sheng from php313 having sheng <200;

• 查询年龄=89的薪水比市场价均低于1000的

select id,name,email,avg_salary-salary as sheng from php313 where age=89 having sheng<1000;

4 order by,可以根据字段来排序，默认是升序，可以自己调整

• select name,age ,email ,salary from php313 where age=89
  order by salary;（隐式升序，也可以加asc显式）
• select name,age ,email ,salary from php313 where age=89
  order by salary desc;
• 多字段排序: select name,age ,email ,salary from php313 where age <> 89
  order by salary,age;（优先排序salary，再在salary相同的内部按照age分）

5 limit 限制条目
select id,name,salary from msg order by salary limit 2,3；
得到salary第二高开始的3个条目

应用举例：
问题1、只用一次select完成下面操作！

错误方法：select name,count(score<60) as k,avg(score) from stu group by name having k>=2;//注意：count是计算总的行数
正确解答：
1、找出每个人不及格科目数
select name,sum(score<60) as k from stu group by name having k>=2;
2、取出平均值
select name,avg(score) from stu group by name;
合成一个select语句就是：
select name,avg(score),sum(score<60) as k group by name having k>=2;

问题2、查询每个栏目下最高薪水的人
select *from (select id,name,email,age,salary from php313 order by age asc, salary desc) as temp group by age;

查询顺序总结

FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY