`
SonofGod
  • 浏览: 20759 次
  • 性别: Icon_minigender_1
  • 来自: 南京
社区版块
存档分类
最新评论

sql学习大全

    博客分类:
  • sql
阅读更多

--建表 (雇员表)
create table EMP
(
  EMPNO    NUMBER(5),--员工号
  ENAME    VARCHAR2(10),--员工名称
  JOB      VARCHAR2(30),--职位
  MGR      NUMBER(5),--上级的员工号
  HIREDATE DATE,--入职时间
  SAL      NUMBER(8,2),--工资
  COMM     NUMBER(8,2),--奖金
  DEPTNO   NUMBER(6)--部门编号
)
--部门表
create table DEPT
(
  DEPTNO NUMBER(2),--部门编号
  DNAME  VARCHAR2(14),--部门名称
  LOC    VARCHAR2(13)--部门地址
)

--工资等级表
create table SALGRADE
(
  GRADE NUMBER,--工资等级编号
  LOSAL NUMBER,--最低数
  HISAL NUMBER--最高数
)

--奖金表
create table BONUS
(
  ENAME VARCHAR2(10),
  JOB   VARCHAR2(20),
  SAL   NUMBER,
  COMM  NUMBER
)



/*基础查询语句*/

--部门一中的所有员工
select * from emp where deptno=1
--找出职位为中级工程师的所有员工
select * from emp where job='中级工程师'
--找出奖金高于工资的员工
select * from emp where comm>sal
--找出每个员工工资和奖金的总和
select t.*,(t.sal+t.comm) totalM from emp t
--找出部门1中的项目经理和部门2 中的初级工程师
select t.* from emp t where (t.deptno=1 and t.job = '项目经理') or (t.job ='初级工程师' and t.deptno =2)
--找出部门1中既不是项目经理也不是初级工程师,而且工资大于等于3000 的员工
select t.* from emp t where t.deptno =1 and t.job !='项目经理' and t.job !='初级工程师' and t.sal>3000
--找出有奖金的员工的不同工作
select distinct(t.job) from emp t where t.comm !=0
--找出没有奖金或者奖金低于600 的员工
select t.* from emp t where t.comm<600 or t.comm = 0
--显示雇员姓名,根据其服务年限,将最老的雇员排在最前面
select t.ename,t.hiredate from emp t order by t.hiredate

/*单行函数用法*/

--找出每个月倒数第三天受雇的员工 *(用到last_day函数)
select * from emp where last_day(hiredate)-3 = hiredate 
--找出1年前雇的员工 *(用到add_months函数)
select * from emp where hiredate<=add_months(sysdate,-1*12)
--所有员工名字前加上Dear ,并且名字首字母大写 *(用到last_day函数)
select 'Dear '|| initcap(t.ename) from emp t
--找出姓名为5 个字母的员工
select * from emp t where length(t.ename) = 3
--找出姓名中不带'张'这个字的员工
select * from emp where ename not like '%张%'
--显示所有员工的姓名的第一个字
select substr(ename,0,1) from emp
--显示所有员工的姓
select distinct(substr(ename,0,1)) from emp
--显示所有员工,按名字降序排列,若相同,则按工资升序排序
select * from emp order by ename desc,sal
--假设一个月为30 天,找出所有员工的日薪,不计小数 *(去掉小数点floor,四舍五入:round  直接去小数点后几位:trunc)
select t.*,floor(t.sal/30,1) ri from emp t
select t.*,round(t.sal/30,1) ri from emp t
select t.*,trunc(t.sal/30,1) ri from emp t
--找到1月份受雇的员工*(to_char的用法)
select * from emp where to_char(hiredate,'fmmm')='1'
--列出员工加入公司的天数(四舍五入)
select t.*,round(sysdate-t.hiredate) from emp t
--分别用case 和decode 函数列出员工所在的部门,deptno=10 显示'部门10' 用2种方法实现
select t.ename,t.empno,(case when t.deptno='1' then '部门1' when t.deptno='2' then '部门2' else '管理部门' end) 部门 from emp t order by t.deptno
select t.ename,t.empno,decode(t.deptno,'1','部门1','2','部门2','管理部门') 所在部门 from emp t order by t.deptno


/*分组函数用法*/

---分组统计各部门下工资>5000 的员工的平均工资
select round(avg(t.sal)) from emp t where t.sal>5000 group by t.deptno
--统计各部门下平均工资大于500 的部门
select avg(t.sal) avgsal from emp t where t.sal>5000 group by t.deptno having avg(t.sal)>7000
       --或
select t.* from (select t.deptno,avg(t.sal) avgsal from emp t where t.sal>5000 group by t.deptno) t where t.avgsal>7000
--算出部门1中得到最多奖金的员工奖金
select max(comm) from emp where deptno = 1
select t.* from (select t.* from emp t order by t.comm desc) t where rownum<2 and t.deptno =2
--算出部门30 中得到最多奖金的员工姓名
select * from emp t1 where t1.comm = (select max(comm) from emp where deptno = 1) and t1.deptno =1
       --或
select t.* from (select t.* from emp t order by t.comm desc) t where rownum<2 and t.deptno =2
--算出每个职位的员工数和最低工资
select job,count(*),min(sal) from emp group by job
--算出每个部门,每个职位的平均工资和平均奖金(平均值包括没有奖金),如果平均奖金大于300,显示“奖
--   金不错”,如果平均奖金100 到300,显示“奖金一般”,如果平均奖金小于100,显示“基本没有奖金”,
--   按部门编号降序,平均工资降序排列
select t.deptno,t.job,avg(sal),avg(comm),(case when avg(comm)>800 then '奖金不错'
when 500<=avg(comm) and 800>= avg(comm) then '奖金一般' else '基本没有奖金' end)
奖金情况 from emp t group by t.deptno,t.job order by t.deptno desc,avg(sal) desc
--列出员工表中每个部门的员工数,和部门no
select t.deptno,count(*) from emp t group by t.deptno
--得到工资大于自己部门平均工资的员工信息
select t.* from emp t,(select avg(t.sal) pj,t.deptno from emp t group by t.deptno) t1 where t.sal>t1.pj and t1.deptno = t.deptno
--分组统计每个部门下,每种职位的平均奖金(也要算没奖金的人)和总工资(包括奖金) *(函数nvl 没有就算是0)
select deptno,job,avg(nvl(comm,0)),sum(sal+nvl(comm,0)) from emp group by deptno,job;


/*多表查询*/

--列出员工表中每个部门的员工数,和部门no
select count(*),deptno from emp group by deptno
--列出员工表中每个部门的员工数(员工数必须大于3),和部门名称
select d.* ,ed.cou from dept d,(select deptno,count(*) cou from emp group by deptno having count(*)>3) ed where d.deptno=ed.deptno;
--找出工资比张老大 多的员工
select t2.* from emp t1,emp t2 where t1.ename='张老大' and t1.sal<t2.sal
       --或
select * from emp t1,(select sal from emp where ename='张老大') t where t1.sal>t.sal
--列出所有员工的姓名和其上级的姓名
select * from emp t1,(select empno,ename from emp) t where t1.mgr = t.empno
--以职位分组,找出平均工资最高的两种职位
select t1.* from (select job,avg(sal) jabsal from emp group by job order by avg(sal) desc) t1 where rownum<3
--查找出不在部门2,且比部门2中任何一个人工资都高的员工姓名、部门名称
select * from emp t1,(select max(sal) maxsal from emp where deptno = '2') t where t1.sal>t.maxsal and t1.deptno !='2'
--得到平均工资大于8000 的工作职种
select job,avg(sal) jobsal from emp group by job having(avg(sal)>8000)
--分部门得到工资大于4000 的所有员工的平均工资,并且平均工资还要大于7500
select deptno,avg(sal) depavgsal from emp where sal>4000 group by deptno having(avg(sal)>7500)
--得到每个月工资总数最少的那个部门的部门编号,部门名称,部门位置
select t1.*
  from dept t1,
       (select e.deptno, sals
          from (select deptno, sum(sal) sals
                  from emp
                 group by deptno
                 order by sum(sal)) e
         where rownum = 1) t
 where t1.deptno = t.deptno
--分部门得到平均工资等级为2 级(等级表)的部门编号
select t.deptno from (select deptno,avg(sal) avgsal from emp group by deptno) t,(select losal,hisal from salgrade where GRADE = 2) t1
where t1.losal<t.avgsal and t1.hisal>t.avgsal
--查找出部门10 和部门20 中,工资最高第3 名到工资第5 名的员工的员工名字,部门名字,部门位置

     /*对于rownum的重要说明:rownum是对结果集加的一个伪列,即先查到结果集之后再加上去的一个列 (强调:先要有结果集)。
      简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1,而有其他大于1的值。
      另外还要注意:rownum不能以任何基表的名称作为前缀。*/

select t.*
  from (select rownum rn, t.*
          from (select empno, sal, deptno
                  from emp
                 where deptno = '1'
                    or deptno = '2'
                 order by sal desc) t) t
 where t.rn > 2
   and t.rn < 6
   --或
select emp.ename, emp.empno, emp.sal, dept.dname, dept.loc
  from emp,
       dept,
       (select rownum no, new.*
          from (select *
                  from emp
                 where emp.deptno = 1
                    or deptno = 2
                 order by emp.sal desc) new) e
 where emp.deptno = dept.deptno
   and e.no >= 3
   and e.no <= 5
   and e.empno = emp.empno;
--查找出收入(工资加上奖金),下级比自己上级还高的员工编号,员工名字,员工收入
select e.ename, e.empno, e.sal + nvl(e.comm, 0)
  from emp e, emp m
 where e.mgr = m.empno
   and (e.sal + nvl(e.comm, 0)) > (m.sal + nvl(m.comm, 0));
--查找出工资等级不为4 级的员工的员工名字,部门名字,部门位置
select t.ename, t.empno, t.sal, t.deptno, d.DNAME, d.loc
  from emp t, (select * from salgrade where grade = 4) t1, dept d
 where t.deptno = d.deptno and (t.sal > t1.hisal or t.sal < t1.losal)
--查找出职位和'张小' 或者'李三'一样的员工的平均工资
select avg(t1.sal) from emp t1,(select job from emp where ename='张小' or ename='李三') t where t1.job in t.job
   --或
select avg(sal) from emp where job in (select job from emp where ename='张小' or ename='李三');
--查找出不属于任何部门的员工
select * from emp where deptno is null or deptno not in(select deptno from dept)
--按部门统计员工数,查处员工数最多的部门的第二名到第五名(列出部门名字,部门位置)
select t.counts,d.dname,d.loc from dept d,
(select rownum nos,t.* from (select deptno,count(*) counts from emp group by deptno order by count(*) desc) t) t
where d.deptno = t.deptno and (t.nos<5 and t.nos>1)
   --或
select dept.dname, dept.loc
from (select rownum no, deptno from
(select count(*) employeeSum, deptno from emp group by deptno order by employeeSum
desc)) e,dept where e.no between 2 and 5 and e.deptno = dept.deptno;
--查询出张小所在部门的部门号\部门名称\部门人数  ****
select count(*),deptno from emp where deptno in (select deptno from emp where ename='张小') group by deptno

select t.countno, d.dname, d.loc
  from dept d,
       (select count(*) countno, deptno
          from emp
         where deptno in (select deptno from emp where ename = '张小')
         group by deptno) t
 where d.deptno = t.deptno;
--查询出张小所在部门的工作年限最大的员工名字
select ename, hiredate
  from emp
 where hiredate in
       (select min(hiredate)
          from emp
         where deptno in (select deptno from emp where ename = '张小'));
--查询出工资成本最高的部门的部门号和部门名称
select deptno,total from
(select rownum rw,t.deptno,t.total from (select sum(sal) total,deptno from emp group by deptno order by sum(sal) desc) t) t
where t.rw=1
--或
select d.deptno, d.dname, t.sum_sal
  from dept d,
       (
       select deptno, sum(sal) sum_sal
          from emp
         group by deptno
        having sum(sal) = (select max(sum(sal)) from emp group by deptno)
        ) t
 where d.deptno = t.deptno
 
 
 
--随机返回5 条记录
select *
  from (select ename, job from emp order by dbms_random.value())
 where rownum <= 5
--查询跳过表中的偶数行
select ename from (select row_number() over (order by ename) rn,ename
from emp) x where mod(rn,2)=1;
--查询员工信息与其中工资最高最低员工
select ename,sal,max(sal) over(), min(sal) over() from emp;
--连续求和
select ename,sal,sum(sal) over(), sum(sal) over(order by ename) from
emp;
--分部门连续求和
select deptno, sal, sum(sal) over(partition by deptno order by ename) as s
  from emp
--查询EMP 员工表下每个部门工资前二名的员工信息
select deptno, ename, sal
from emp e1
where
(select count(1)
from emp e2
where e2.deptno=e1.deptno and e2.ename!=e1.ename and e2.sal>e1.sal)
<2
order by deptno, sal desc;

1
3
分享到:
评论
1 楼 Pillar_zhang 2013-03-19  

相关推荐

    SQL学习大全.pdf

    ### SQL学习大全知识点详解 #### 一、SQL基础概述与应用 **标题**: SQL学习大全 **描述**: 本资料提供了全面的SQL学习资源,帮助读者掌握SQL的基础知识及其高级特性。 #### 二、SQL语句基本语法 SQL是Structured ...

    sql sql sql sqlsql 语句学习 sql sql sql

    sql 语句学习 sql sql sqlsql 语句学习 sql sql sql

    经典SQL语句大全 SQL学习

    通过"经典SQL语句大全"的学习,你不仅可以理解SQL的基本语法,还能深入理解如何在实际项目中高效地使用SQL进行数据管理、分析和查询。无论你是初级开发者还是经验丰富的专业人士,这份资源都能成为你宝贵的参考资料...

    sql语句学习大全

    本资源包"sql语句学习大全"显然是一个面向初学者和进阶者的学习资料集合,包括PDF文档和练习题及答案,旨在帮助用户全面掌握SQL知识。 1. **SQL基础**:SQL的基础包括数据定义(DDL,Data Definition Language),...

    SQL SERVER 2008 学习计划

    SQL SERVER 2008 学习计划 SQL Server 2008 是一个功能强大且功能齐全的关系数据库管理系统。本学习计划旨在帮助学习者快速掌握 SQL Server 2008 的基本概念、安装、配置、数据库设计、数据操作、存储过程、触发器...

    learning Sql学习指南

    《SQL学习指南》是一本专为SQL初学者和进阶者设计的学习资料,包含了中文版和英文原版两部分,旨在帮助读者全面理解和掌握SQL语言的核心概念与实用技巧。SQL,全称Structured Query Language(结构化查询语言),是...

    sql学习的好书

    标题中的“sql学习的好书”指的是SQL(Structured Query Language)的学习资源,这是一门用于管理和处理关系数据库的强大语言。在描述中提到的“学sql的不错的两个help文件”,表明我们有两个帮助文档,分别是“SQL2...

    sql学习 查询语句

    sql学习sql学习sql学习sql学习sql学习sql学习sql学习sql学习sql学习sql学习sql学习

    SQL数据库学习大全

    《SQL数据库学习大全》是一份综合性的学习资源,包含了对SQL语言的全面解析以及实践教程。这份资料集合了SQL书籍的精华内容与教学PPT,旨在帮助从零基础到进阶提升的数据库学习者深入理解和掌握SQL语言的核心概念、...

    sql 学习全实例 SQL STUDENT

    sql 学习全实例 SQL STUDENT sql 学习全实例 SQL STUDENT sql 学习全实例 SQL STUDENT sql 学习全实例 SQL STUDENT sql 学习全实例 SQL STUDENT sql 学习全实例 SQL STUDENT

    SQL教案例题文章sql学习

    讲解sql的资料 学习 研究sql 编程的讲解sql的资料 学习 研究sql 编程的讲解sql的资料 学习 讲解sql的资料学习 研究sql 编程的讲解sql的资料 学习 研究sql 编程的 每一个张杰的都有例题家今年过节简介纤细的sql质料

    sql学习资料 SQL自学通 SQL语句集锦 sql基础到高级 SQLServer实例 MySQL中文参考手册 数据库语言SQL必学

    sql学习资料 SQL自学通 SQL语句集锦 sql基础到高级 SQLServer实例 MySQL中文参考手册 数据库语言SQL必学 MySQL中文参考手册.chm SQL21自学通.pdf SQL查询答案.doc sql基础到高级.doc SQL语句集锦.txt 多个 SQLServer...

    机器学习检测SQL注入.zip

    机器学习检测SQL注入# ML-for-SQL-Injection机器学习检测SQL注入本项目是使用机器学习算法来分类SQL注入语句与正常语句:使用了SVM,Adaboost,决策树,随机森林,逻辑斯蒂回归,KNN,贝叶斯等算法分别对SQL注入语句...

    sql经典大全(适用初学者)

    《SQL经典大全(适用初学者)》是一套专为初学者设计的全面SQL学习资源,旨在帮助新手快速掌握SQL的基本概念、语法和实际应用。这个资料包包含了多种类型的文件,如电子书、教程文档和实例手册,以多角度、全方位的...

    SQL 语句 大全 chm

    本资源包含两个CHM(Compiled Help Manual)文件,分别是“SQL语言参考大全(CHM版).chm”和“SQL 安装.chm”,它们提供了丰富的SQL语句学习资料和SQL Server 2000安装过程中的问题解决方案。 “SQL语言参考大全...

    SQL学习指南(第二版)书中的sql初始脚本

    SQL学习指南(第二版)书中的sql初始脚本,此脚本直接运行即可,含有数据。

    SQL语句大全-SQL学习者必备

    这份“SQL语句大全”旨在为SQL学习者提供全面的参考资料,帮助他们理解和运用各种SQL语句。 在SQL中,主要分为四大类语句:数据查询语言(DQL)、数据操纵语言(DML)、数据定义语言(DDL)和数据控制语言(DCL)。...

    SQL教程大全

    本“SQL教程大全”旨在提供一个全面的学习路径,涵盖了从基础到高级的各种SQL概念和技术,包括MySQL、SQL Server以及PHP与SQL的集成应用。 首先,SQL基础是学习的起点。这一部分将讲解SQL的基本语法,如创建数据库...

Global site tag (gtag.js) - Google Analytics