数据库部分（MySql）_2

分组查询

　　分组查询通常和聚合函数结合使用，查询条件中每个XXX就以XXX为分组的条件；

　　格式：每个A的平均B

　　　　select avg(B) from 表名 group by A;

having

　　在where后面只能写普通字段的条件；在having后面写聚合函数的条件；having写在group by的后面，where写在最前面；

　　案例： 查询每个部门的平均工资，只查询平均工资大于2000的。
　　select deptno,avg(sal) a from emp group by deptno having a>2000;

SQL中各个关键字的顺序

　　select ... from 表名 where ... group by ... having ... order by ... limit ... ;

子查询

　　嵌套在SQL语句中的查询SQL语句称之为子查询；子查询可以由多层嵌套；子查询可写在where和having后面作为查询条件的值；

　　写在创建表的时候  create table NEW表名 as (select X,Y from 表名);

　　写在from后面当成一张新表使用（必须有别名）

　　　　select X,Y from (select * from EMP) T;

　　子查询格式：　　select 字段名,字段名 from 表名1 字段名=(select avg(字段名） from 表名2);

　　 案例：查询部门平均工资最高的部门信息
     1）得到平均最高工资
     select avg(工资) from EMP group by 部门编号 order by avg(工资) desc limit 0,1;
     2）得到部门信息
     select 部门编号 from EMP group by 部门编号 having avg(工资)=(select avg(工资) from EMP group by 部门编号 order by avg(工资) desc limit 0,1);
     3）得到部门信息
     select *from dept where 部门编号 in (select 部门编号 from EMP group by 部门编号 having avg(工资)=(select avg(工资) from EMP group by 部门编号 order by avg(工资) desc limit 0,1));

 

关联查询

　　同时查询多张表的数据称为关联查询。

案例：
    1）查询每个员工的姓名和相对应的部门名称
    select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
    2）查询每个商品的标题、商品单价和对应的分类名称
    select t1,title,t1.price,t2.name from t_item t1,t_item_category t2 where t1.category_id=t2.id;
    3）查询在new york工作的所有员工信息
    select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='new york';
    4）查询所有的员工信息和对应的部门地点
    select e.*,d.loc from emp e,dept d where e.deptno=d.deptno;
    5）查询员工信息、员工工资、对应的部门名称，要求工资大于2000
    select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>2000;

笛卡尔积

　　关联查询如果不写关联关系，则查询结果为两张表的乘积；笛卡尔积是一种错误的查询结果，在工作中不能出现。

等值连接和内连接

　　这两种连接方式都是关联查询的查询方式，效率相同；

　　等值连接： select * from A,B where A.X=B.X and A.Y=XXX;

　　内连接： select * from A join B on A.X=B.X where A.Y=XXX;

　　外连接： select * from A left/right join B on A.X=B.X where A.Y=XXX;

　　左外连接：以join右侧表为主表查询其所有数据和右侧表的交际数据；

　　右外连接：以jion右侧表为朱彪查询其所有和左侧表的交集数据。

    案例：查询所有员工的名字对应的部门名
    select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;