SQL学习笔记03-关联函数，子查询，分页，mysql 日期

关联查询 
       在使用数据库查询语句时，单表的查询有时候不能满足项目的业务需求，在项目开发过程中，有很多需求都是要涉及到多表的连接查询，需要用到多表关联查询。

 

 92 语法：

/* 
等值连接,通过表中相同的列名进行关联
select table1.colume table2.colume
from table1,table2
where table1.colume1 = table2.colume2
*/
select * from emp,dept where emp.deptno = dept.deptno;

/*
非等值连接，两个表中没有相同的列名，
但是某一个列在另一张表的列的范围之中
<,>,<=,>=,!=连接时称非等值连接
*/
select e.ename, e.sal, sg.grade
  from emp e, salgrade sg
 where e.sal between sg.losal and sg.hisal;
 
/*
外连接，为了在操作时能保持这些将被舍弃的元组，提出了外连接的概念，使用外连接
可以看到不满足连接条件的记录 –外连接运算符是(+)
–有左外连接和右外连接
左外连接显示左边表的全部行
右外连接显示右边表的全部行
*/
--左外连接
select * from emp e,dept d where e.deptno = d.deptno(+); 
-- 右外连接
select * from emp e,dept d where e.deptno(+) = d.deptno; 

/*
自连接,将一张表当成不同的表来看待，自己关联自己
*/
--将雇员和他经理的名称查出来
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;

/*
笛卡尔积,当关联多张表，但是不指定连接条件的时候，会进行笛卡尔积，
关联后的总记录条数为M*n，一般不要使用
*/
select * from emp e,dept d;

--92的表连接语法有什么问题？？？？
--在92语法中，多张表的连接条件会方法where子句中，同时where需要对表进行条件过滤
--因此，相当于将过滤条件和连接条件揉到一起，太乱了，因此出现了99语法

 

99语法：

/*
cross join 等同于笛卡尔积
*/
select * from emp cross join dept;
/*natural join  相当于是等值连接，但是注意，不需要写连接条件，
会从两张表中找到相同的列做连接,并且相同的列会合并成一列。
当两张表中不具有相同的列名的时候，会进行笛卡儿积操作,自然连接跟92语法的自连接没有任何关系
*/
select * from emp e natural join dept d;
select * from emp e natural join salgrade sg;

/*
on 子句，用来添加任意的连接条件
*/
select * from emp e join dept d on e.deptno = d.deptno;
select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;

/*
left outer join ,会把左表中的全部数据正常显示，右表没有对应的数据直接显示空即可
相当于92语法的左外连接
*/
select * from emp e left outer join dept d on e.deptno = d.deptno;

/*
right outer join ,会把右表中的全部数据正常显示，左表中没有对应的记录的话显示空即可
*/
select * from emp e right outer join dept d on e.deptno = d.deptno;

--full outer join ,相当于左外连接和右外连接的合集
select * from emp e full outer join dept d on e.deptno = d.deptno;

--inner outer join，两张表的连接查询，只会查询出有匹配记录的数据
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e.deptno = d.deptno;

--using,除了可以使用on表示连接条件之外，也可以使用using作为连接条件,此时连接条件的列不再归属于任何一张表
select deptno from emp e join dept d using(deptno);
select e.deptno,d.deptno from emp e join dept d on e.deptno = d.deptno;

--检索雇员名字、所在单位、薪水等级
select e.ename, d.Dname, s.Grade
  from emp e
  join dept d
    on e.deptno = d.deptno
  join salgrade s
    on e.sal between s.losal and s.hisal;

子查询：

子查询：
    嵌套再其他sql语句中的完整sql语句，可以称之为子查询
分类：
    单行子查询  子查询的结果只有一行，使用单行记录比较运算符 =，>,>=,<,<=,<>
    多行子查询  子查询的结果有多行，使用集合比较运算符 some all in

--有哪些人的薪水是在整个雇员的平均薪水之上的
--1、先求平均薪水
select avg(e.sal) from emp e;
--2、把所有人的薪水与平均薪水比较
select * from emp e where e.sal > (select avg(e.sal) from emp e); 
--我们要查在雇员中有哪些人是经理人
--1、查询所有的经理人编号
select distinct e.mgr from emp e;
--2、再雇员表中过滤这些编号即可
select * from emp e where e.empno in (select distinct e.mgr from emp e);
--每个部门平均薪水的等级
--1、先求出部门的平均薪水
select e.deptno,avg(e.sal) from emp e group by e.deptno; 
--2、跟薪水登记表做关联，求出平均薪水的等级
select t.deptno, sg.grade
  from salgrade sg
  join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
    on t.vsal between sg.losal and sg.hisal;

分页：

分页：
Mysql使用limit分页
   select * from stu limit m, n; //m其实行，n为结束行

Oracle使用rownum分页
select * from (
select rownum rn,a.* from table_name a where rownum <= x  //结束行
)
where rn >= y;   //起始行
因为先查询后排序的，所以rownum不能直接使用，需要嵌套使用

    
--1  求平均薪水最高的部门的部门编号
-- 求平均每个部门的薪水
select deptno,avg(sal) from emp group by deptno;
--  求平均薪水最高的部门
select max(t.avg) from (select e.deptno,avg(sal) avg from emp e group by deptno) t;
--求部门编号
select t.dp
  from (select e.deptno dp, avg(sal) avg from emp e group by deptno) t
 where t.avg =
       (select max(t.avg)
          from (select e.deptno, avg(sal) avg from emp e group by deptno) t);

--2、求部门平均薪水的等级
-- 求出部门平均薪水
select deptno,avg(sal) from emp group by deptno;
--求平均薪水的等级
select t.dpt, s.grade
  from salgrade s
  join (select deptno dpt, avg(sal) avg from emp group by deptno) t
    on t.avg between s.losal and s.hisal;
--3、求部门平均的薪水等级
--求部门每个人的薪水等级
select e.deptno, sg.grade
  from emp e
  join salgrade sg
    on e.sal between sg.losal and sg.hisal;
--按照部门求平均等级
select t.deptno, avg(t.grade)
  from (select e.deptno, sg.grade
          from emp e
          join salgrade sg
            on e.sal between sg.losal and sg.hisal) t
 group by t.deptno;
--4、求薪水最高的前5名雇员 
select rownum rn,e.ename,e.sal from emp e where rownum <= 5 order by sal desc; -- 错误示例
select rownum ,t1.ename,t1.sal
  from (select * from emp e order by e.sal desc) t1
 where rownum <= 5;

--5、求薪水最高的第6到10名雇员
select rownum ,t1.ename,t1.sal
  from (select * from emp e order by e.sal desc) t1
 where rownum <= 10;

select rownum, t1.ename, t1.sal
  from (select rownum rn, t1.ename, t1.sal
          from (select * from emp e order by e.sal desc) t1
         where rownum <= 10) t1
 where t1.rn <= 10
   and t1.rn >= 5

mysql日期：

/*
mysql日期和字符相互转换方法
date_format(date,'%Y-%m-%d')-------------->oracle中的to_char();
str_to_date(‘date’,’%Y-%m-%d')-------------->oracle中的to_date();
%Y：代表4位的年份
%y：代表2为的年份
%m：代表月,格式为(01……12)
%c：代表月,格式为(1……12)
%d：代表月份中的天数,格式为(00……31)
%e：代表月份中的天数,格式为(0……31)
%H：代表小时,格式为(00……23)
%k：代表小时,格式为(0……23)
%h：代表小时,格式为(01……12)
%I：代表小时,格式为(01……12)
%l：代表小时,格式为(1……12)
%i：代表分钟,格式为(00……59)
%r：代表时间,格式为12小时(hh:mm:ss[AP]M)
%T：代表时间,格式为24小时(hh:mm:ss)
%S：代表秒,格式为(00……59)
%s：代表秒,格式为(00……59)
*/

select date_format(now(),'%Y');
select date_format(now(),'%Y-%c-%d%h:%i:%s');
SELECT STR_TO_DATE('Jul 20 2013 7:49:14:610AM','%b %d %Y %h:%i:%s:%f%p') from DUAL;
/*--执行后得到结果:
'2013-07-2007:49:14.610000‘
具体可参考：
▪http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date 
▪http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format 
▪http://dev.mysql.com/doc/refman/5.7/en/
*/