学习SQL查询语句 基于Oracle默认的表

desc 表名 描述表的内容      
desc emp;   
  
--加上数学表达式和列名  ""保持格式   
select ename "name space", sal*12 year_sal from emp;       
  
select 2*3 from dual;   
  
select sysdate from dual;   
  
--空值的数学表达式 结果都是空值   
select ename, sal*12 + comm from emp;      
  
--"||"字符串连接 单引号中的是字符串，字符串中的单引号，''表示   
select ename||sal from emp;        
  
--distinct 修饰两个字段   

select distinct deptno from emp;重复的数据去掉了

select distinct deptno, job from emp;   重复的组合给去掉了

where 后面跟过滤条件
select ename, sal from emp where sal between 800 and 1500;   
select ename, sal from emp where sal >= 800 and sal <= 1500;   
  
select ename, sal from emp where comm is (is not) null;   空值处理
  不等于使用<>符号                                                     
--一个事务开始于一条ddl语句，结束语rollback commit ddl数据定义语句 dcl数据控制语句语句   
--正常断开连接 提交 非正常断开连接 回滚   
  
select ename, sal from emp where sal not in (800 ,1500);   

select ename,sal,comm from emp where ename in('smith','xing','abc');
  
-- 模糊查询   
select ename, sal from emp where ename like '_A%';   
select ename, sal from emp where ename like '%\%%';   
  
--转义字符   
select ename, sal from emp where ename like '%$%%' escape '$';   
  
--排序   
select * from dept order by deptno desc;   
select ename, sal from emp order by deptno asc, ename desc;   
  
--函数   
select lower(ename) from emp;   
select substr(ename,1,3) from emp;   
select round(23.652, 1) from dual;   
select to_char(sal, '$99,999.999') from emp;   
select to_char(sal, 'L000,000.000') from emp;   
select to_char(hiredate, 'yyyy-mm-dd hh:mi:ss') from emp;   
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;   
select ename, hiredate from emp   
    where hiredate > to_date('1982-09-11 12:44:44', 'YYYY-MM-DD HH24:MI:SS');   
select sal from emp where sal > to_number('$1,250.000', '$9,999.000');   
select ename,sal*12 + nvl(comm, 0) from emp;   
  
--组函数  多行输入，只有一行输出 select ename, max(sal) from emp; 结果不能保证只有一行   
select max(sal) from emp;   
select sum(sal) from emp;   
select count(distinct deptno) from emp;   
  
--分组函数   
select deptno,avg(sal) from emp group by deptno;   
select deptno, job, max(sal) from emp group by deptno, job;   
  
--出现在select后，不是组函数的，必须出现在group by里   
--where语句对单挑记录进行过滤，分组后过滤用having   
  
--子查询 把一次查询结果当成表   
select ename from emp where sal = (select max(sal) from emp);   
select ename, sal from emp   
    join (select max(sal)max_sal, deptno from emp group) t   
    on(t.max_sal = emp.sal and t.deptno = emp.deptno);   
  
--自连接   
select t1.ename, t2.ename from emp t1, emp t2 where t1.mgr = t2.empno;   
  
  
select ename, dname, grade from emp e, dept d, salgrade s where e.deptno = d.deptno and    
  
e.sal between  
s.losal and s.hisal and job <> 'CLERK';   
  
--SQL 1999  链接条件不写在where中，where只负责过滤数据   
select ename, dname from emp cross join dept;   
select ename, dname from emp join dept on (emp.deptno = dept.deptno);   
select ename, dname from emp join dept using (deptno)；   
  
select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);   
select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);   
  
--左外连接   
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);   
--右外连接   
select ename, dname from emp e right join dept d on (e.deptno = d.deptno);   
select ename, dname from emp e full outer join dept d on (e.deptno = d.deptno);   
  
--求部门中那些人薪水最高   
select ename, sal  from emp    
join (select deptno, max(sal) max_sal from emp group by deptno) t    
on (emp.sal = t.max_sal and emp.deptno = t.deptno);   
  
--求部门平均薪水的等级   
select deptno, grade from salgrade s    
join (select avg(sal) avg_sal, deptno from emp group by deptno) t    
on (avg_sal between losal and hisal);   
  
--求平均薪水最高的部门的部门标号   
select deptno, avg_sal from  
(select avg(sal) avg_sal, deptno from emp group by deptno)   
where avg_sal =   
(select max(avg(sal)) from emp group by deptno);   
  
--平均薪水的等级最低的部门的部门名称   
select dname, t1.deptno, grade, avg_sal from  
(  select deptno, grade, avg_sal from  
  (select deptno, avg(sal) avg_sal from emp group by deptno) t   
  join salgrade s on (t.avg_sal between s.losal and s.hisal)   
) t1   
join dept on (t1.deptno = dept.deptno)   
where t1.grade =    
(   
  select min(grade) from  
  (  select deptno, grade, avg_sal from  
    (select deptno, avg(sal) avg_sal from emp group by deptno) t   
    join salgrade s on (t.avg_sal between s.losal and s.hisal)   
  )   
)   
  
--建立视图   
create view v$_dept_avg_sal_info as  
select deptno, grade, avg_sal from  
  (select deptno, avg(sal) avg_sal from emp group by deptno) t   
join salgrade s on (t.avg_sal between s.losal and s.hisal);   
  
--变简单了   
select dname, t1.deptno, grade, avg_sal from  
v$_dept_avg_sal_info t1   
join dept on (t1.deptno = dept.deptno)   
where t1.grade =    
(   
  select min(grade) from v$_dept_avg_sal_info   
)   
  
--求部门平均薪水的等级   
select deptno, avg_sal, grade from  
  (select avg(sal) avg_sal, deptno from emp group by deptno)   
join salgrade s   
on (avg_sal between s.losal and s.hisal);   
  
--求部门平均的薪水等级   
select avg(grade), deptno from  
(   
  select deptno, ename, grade from emp   
  join salgrade s   
  on emp.sal between s.losal and s.hisal   
)    
group by deptno   
  
--雇员中有哪些人是经理人   
select ename from emp where empno in  
(select distinct mgr from emp);   
  
--不使用组函数，求薪水的最高值   
select distinct sal from emp where sal not in  
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));   
  
--平均薪水最高的部门的部门名称   
select dname from dept where deptno =   
(   
  select deptno from  
    (select avg(sal) avg_sal, deptno from emp group by deptno)   
  where avg_sal =   
  (select max(avg_sal) from  
    (select avg(sal) avg_sal, deptno from emp group by deptno)   
  )   
)   
  
--求比普通员工的最高薪水还要高的经理人名称   
select ename from emp    
where empno in (select distinct mgr from emp where mgr is not null) and sal >   
(   
  select max(sal) from emp where empno not in  
  (select distinct mgr from emp where mgr is not null)   
)   
  
--rownum只能和小于等于号联合使用   
--使用rownum 要关联表   
select ename from  
(select rownum r, ename from emp) where r > 10;   
  
--薪水最高的5个人   
select ename, sal from  
(select ename, sal from emp order by sal desc)   
where rownum <= 5;   
  
--薪水最高的第六个到第十个雇员   
select ename, sal from  
  (   
  select ename, sal, rownum r from  
    (select ename, sal from emp order by sal desc)   
  )   
where r >= 6 and r <= 10   
  
  
--http://penghao122.iteye.com/blog/54810   
--SQL面试题   
--找出没选过“黎明”老师的所有学生姓名。    
select sname from s    
join sc on (sc.sno = s.sno)   
join c on (c.cno = s.cno)    
where c.cteacher <> 'liming';   
  
--列出2门以上（含2门）不及格学生姓名及平均成绩。    
select sname where sno in    
  (   
  select sno from sc where scgrade < 60   
  group by sno having count(*) >= 2   
  )   
  
--即学过1号课程有学过2号课所有学生的姓名   
select sno from sc where cno = 1 and sno in  
(   
  select sno from sc where cno = 2   
);   
  
--一个事务开始于一条ddl语句，结束语rollback commit ddl数据定义语句 dcl数据控制语句语句   
--正常断开连接 提交 非正常断开连接 回滚