oracle常用查询语句(更新中..)

 

EMP表

DEPT表

SALGRADE表

 

1.简单查询sql

----查询员工表中数据：
select * from emp;
----查询员工的员工编号，员工姓名，职位，工资，部门编号：
select empno,ename,job,sal,deptno from emp;
----查询员工的员工编号，员工姓名，职位，工资，部门编号：----加别名
select empno 编号 ,ename 姓名,job 职位,sal 工资,deptno 部门编号 from emp;
select empno as 编   号 ,ename as 姓名,job as 职位,sal as 工资,deptno as 部门编号 from emp;
select empno as "编   号" ,ename as "姓名",job as "职位",sal as "工资",deptno as "部门编号" from emp;
---起别名：利用  as "" ,as和""都可以省略不写，但是当别名中有特殊符号比如空格的时候，""不能省略
----查询员工的编号，姓名，年薪：---进行运算
select empno,ename,sal*12+200 年薪 from emp;
----对员工进行一个详细的介绍：
select '员工的名字是'||ename||'他的工资是：'||sal  as 员工信息介绍 from emp ;

-----where条件查询
-----查询名字是SMITH的员工的信息：
select * from emp where ename='SMITH';
----查询工资大于等于3000的员工的姓名，部门编号，工资：
select ename,deptno,sal from emp where sal>=3000; 
----查询没有津贴的员工：
select * from emp where comm is null;
----查询有津贴的员工：
select * from emp where comm is not null;
----查询津贴为0的员工：
select * from emp where comm =0 ;
----查询入职日期为1987年4,19的员工：
select * from emp where hiredate='19-4月-1987';

2.where查询深入

----where子句  指定查询条件
--1.工资不等于800的员工信息
select * from emp where sal<>800;
--2.查询部门----去重
select distinct deptno from emp;---对deptno字段进行去重
select distinct deptno,sal from emp;---对deptno和sal的整体进行去重效果 
--3.查询员工信息，按照工资排序：
select * from emp order by sal;---默认升序
select * from emp order by sal asc;---升序
select * from emp order by sal desc;----降序
select * from emp order by sal asc,deptno desc;---首先sal按照升序排列，在sal相等的情况下，deptno按照降序排列
select * from emp order by deptno asc,sal desc;
--4.工资在2000-3000之间的员工的信息
select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;
--5.职位是 SALESMAN，或者MANAGER 或者CLERK的员工的信息
select * from emp where job='SALESMAN' or job='MANAGER' or job='CLERK';
select * from emp where job in ('SALESMAN','MANAGER','CLERK');
--6.姓名中包含S
select * from emp where ename like '%S%';---like模糊查询  %：0-n个字符
--7.第一个字母是S
select * from emp where ename like 'S%';
--8.最后一个字母是S
select * from emp where ename like '%S';
--9.第二个字母是C的员工信息
select * from emp where ename like '_C%';----  _ 1个
---10.查询名字中带_的员工的信息：
select * from emp where ename like '%_%';
select * from emp for update;
select * from emp where ename like '%\_%' escape '\';

3.单行函数-字符函数，数值函数，日期函数

--dual是一个伪表
select 1+1 from dual;

--字符函数
select initcap('hello') from dual;--首字母大写 Hello

select lower('SDSFFS') from dual;--转换为小写 sdsffs
select lower('DSsfsdDSS') from dual;--dssfsddss

select upper('fsdfDSdd') from dual;--转换为大写FSDFDSDD

select trim('  fds  fsd  sd  ') from dual;--去掉首尾空格
select ltrim(' sad das ds ') from dual;--去掉左侧空格
select rtrim(' dsad dsa sa ') from dual;--去掉右侧空格

select replace('jack and jue','j','bl') from dual;--字符串替换 black and blue

select instr('worldwide','d') from dual;--查找子串位置 5

select substr('abcdefg',3,2) from dual;--取子字符串 substr(char,pos,len)  cd

select concat('Hello','world') from dual;--连接字符串

 

4.单行函数-转换函数 

----1.number转换为character
select to_char(1234.56,'9999.99') from dual;
select to_char(1234.56,'9999999.999999') from dual;
select to_char(1234.56,'999.9') from dual;--######
select to_char(4354351234.56,'$0000,000,000,000,000.00') from dual;
select to_char(4354351234.56,'L0000,000,000,000,000.00') from dual;
select to_char(1234.56,'00000000.000000') from dual;
select to_char(1234.56,'000.0') from dual;---######
---查询员工的姓名和工资：
select ename,to_char(sal,'$99999.999') from emp;
----2.character转换为number
select to_number('1234.56','99999.99') from dual;
----3.character转换date
select to_date('19900305','YYYYMMDD') from dual;
---计算你出生了多少天：
select  ceil(sysdate- to_date('19900305','YYYYMMDD')) from dual;
-----4.date转换character
select to_char(sysdate,'YYYY-MM-DD') from dual;
select to_char(sysdate,'YYYY"年"MM"月"DD"日"') from dual;
----查询员工的员工编号，姓名，职位，入职日期：
select empno,ename,job,to_char(hiredate,'YYYY"年"MM"月"DD"日"') 入职日期 from emp;
--2019年03月11日 16:27:52--
select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') from dual;

5.函数练习

--by cw  
--选择部门 20 中的所有员工。 
select * from emp where deptno=20;
--列出所有职位为业务员(CLERK)的编号、部门编号及姓名。 
SELECT EMPNO,DEPTNO,ENAME FROM EMP WHERE JOB='CLERK';
--找出没有奖金或奖金低于800的员工。
SELECT * FROM EMP WHERE COMM IS NULL OR COMM <800;
--显示不带有“M”的员工的姓名。
SELECT ENAME FROM EMP WHERE ENAME NOT LIKE  '%M%';
--显示年薪水（包括奖金）超过30000的员工的姓名和受雇日期。
SELECT ENAME,HIREDATE FROM EMP WHERE SAL*12+NVL(COMM,0) > 30000;--NVL(A,B)如果A为空，则返回B
--显示姓名包含"M"但不包含“S”的所有员工的资料。
SELECT * FROM EMP WHERE ENAME LIKE '%M%' AND ENAME NOT LIKE '%S%';
--查看dept表中所有记录的信息。
SELECT * FROM DEPT;
--查看emp表中所有记录的信息。
 SELECT * FROM EMP;
--查看emp表中部门编号等于10的记录所有信息。
SELECT * FROM EMP WHERE DEPTNO =10;
--查看emp表中薪水大于1500的记录所有信息。
 SELECT * FROM EMP WHERE SAL>1500;
--查看emp表中姓名等于CLARK的记录信息。
  SELECT * FROM EMP WHERE ENAME ='CLARK';
--查看emp表中部门编号不等于10的记录所有信息。
SELECT * FROM EMP WHERE DEPTNO <>10;
--查看emp表中工资在800至1500之间的记录所有信息。
   SELECT * FROM EMP WHERE SAL BETWEEN 800 AND 1500;
--查看emp表中津贴等于null的所有记录。
 SELECT * FROM EMP WHERE COMM IS NULL;
--查看emp表中津贴不等于null的所有记录。
  SELECT * FROM EMP WHERE COMM IS NOT NULL;
--查看emp表中薪水是(800或1500或2000)的记录所有信息。
SELECT * FROM EMP WHERE SAL IN (800,1500,2000);
--查看emp表中姓名是(SMITH或KING或CBA)的记录所有信息。
SELECT * FROM EMP WHERE ENAME IN ('SMITH','KING','CBA');
--查看emp表中雇佣日期为1981年2月20日之后入职的员工。
SELECT * FROM emp WHERE HIREDATE > TO_DATE('19810220', 'YYYYMMDD');
--查看emp表中部门编号等于10，而且薪水大于1000的员工信息。  
SELECT * FROM EMP WHERE DEPTNO=10 AND SAL>1000;
--查看emp表中部门编号等于10或薪水大于1000的员工信息。   
SELECT * FROM EMP WHERE DEPTNO=10 OR SAL>1000;
--查看emp表中薪水不是(800或1500)的员工信息。
  SELECT * FROM EMP WHERE SAL<>800 AND SAL<>1500;
--查看emp表中姓名中包含ALL的员工。 
SELECT * FROM EMP WHERE ENAME LIKE '%ALL%';
--查看emp表中姓名中第二个字符为A的员工。
SELECT * FROM EMP WHERE ENAME LIKE '_A%';
--查看emp表中姓名中包含%的员工。
SELECT * FROM EMP WHERE ENAME LIKE '%\%%' ESCAPE '\';
SELECT * FROM EMP FOR UPDATE;
--将员工的名字全部变为大写
SELECT UPPER(ENAME) FROM EMP;
--计算你出生了多少天
SELECT CEIL(SYSDATE-TO_DATE('19970314','YYYYMMDD')) FROM DUAL;
--查询员工的姓名，工资（以本地货币符号显示），入职日期（年-月-日）
SELECT ENAME,TO_CHAR(SAL,'L99999.99'),TO_CHAR(HIREDATE,'YYYY-MM-DD') FROM EMP;
--将员工按照工资升序排列
SELECT * FROM EMP ORDER BY SAL ASC;
--将员工的工资用人民币表示
SELECT ENAME 员工姓名,TO_CHAR(SAL,'L999999.99') 员工工资 FROM EMP;

6.其它常用函数

------1.查询员工的月薪（包含奖金）
select empno,ename,sal,comm,sal+nvl(comm,0) 月薪 from emp;
select empno,ename,sal,comm,sal+comm 月薪 from emp;
nvl(a,b) 假如a是null的，那么按照b处理
nvl(comm,0) 假如comm字段是null的，那么按照0处理
------2.查询员工的月薪（包含奖金）
select empno,ename,sal,comm,nvl2(comm,sal+comm,sal) 月薪 from emp;
nvl2(a,b,c) 假如a是null的，按照c处理，不是null，按照b处理
nvl2(comm,sal+comm,sal) 假如comm是null的，按照sal处理，不是null，按照sal+comm处理
------3.查询员工的职位：
select job from emp;
select decode(job,'CLERK','职员','SALESMAN','销售','PRESIDENT','董事长','普通岗位') 职位
from emp;

7.多行函数

------用一句话表示全部的多行函数：
select sum(sal),min(sal),max(sal),count(*),avg(sal),sum(sal)/count(*) from emp;
sum,avg函数 只适用于数值型。
max,min适用于所有类型，但是有些类型没有实际意义
count 计数：
select count(ename) from emp;
select count(comm) from emp;
select count(*) from emp;
select min(sal),ename from emp;---多行函数不能和普通字段结合使用 
select min(sal),lower(ename) from emp;---多行函数不能和单行函数结合使用

8.增删改基础语句

增删改查：
---查询
select * from  emp; 
---增加
insert into emp 
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(8888,'lili','SALESMAN',7782,to_date('19930507','YYYYMMDD'),3950,null,30) ;
insert into emp 
(empno,ename,job,mgr,deptno)
values
(7777,'lili','SALESMAN',7782,30) ;
当添加的字段是表的全部字段的时候，values前面的()可以省略不写
insert into emp 
values
(4444,'lili','SALESMAN',7782,to_date('19930507','YYYYMMDD'),3950,null,30) ;
---删除
delete from emp where empno=4444;
delete emp where sal>=3000;
---修改
update emp set job='boss',sal=5000 where ename='lili';

9.分组函数

----分组函数
----显示所有员工最高工资，人数
select max(sal) 最高工资,count(*) 人数 from emp;
----显示每个部门的最高工资，人数
select max(sal),count(*) from emp group by deptno;--对
select max(sal),count(*),deptno from emp group by deptno;--对
select max(sal),count(*),ename from emp group by deptno;--错误
多行函数不能和普通字段进行组合查询，但是假如这个字段是分组group by后面的字段的话，就可以组合查询
select max(sal),count(*),deptno from emp group by deptno order by deptno;
select max(sal),count(*),deptno from emp group by deptno order by  max(sal);
select max(sal)*12,count(*),deptno from emp group by deptno order by  max(sal)*12;
----求出每个工作岗位的员工薪水最大值，每组人数
select job,max(sal),count(*) from emp group by job order by count(*) desc ;
----假如工作在数据库中有大小写之分(例如salesman和SALESMAN)，我现在想按照工作分组，求出每组的工资最大值，和人数
select lower(job),max(sal),count(*) from emp group by lower(job) order by count(*) desc ;
---全部转为大写或者全部转为小写，就可以做到忽略大小写了！
----显示每个部门的不同岗位的人数
select deptno,lower(job),count(*) from emp group by deptno,lower(job) order by deptno;

10.having子句

--显示所有部门的人数
select deptno,count(*) from emp group by deptno;
--显示部门编号大于等于20的部门的人数
---方法1：先过滤，再分组
select deptno,count(*) from emp where deptno>=20 group by deptno;
---方法2：先分组，再过滤
select deptno,count(*) from emp group by deptno having deptno>=20 ;
--显示部门人数超过4人的部门的人数
---方法1：
select count(*) from emp where count(*)>4;---错误，没有办法用这个解决
---方法2：先分组，再过滤
select deptno,count(*) from emp group by deptno having count(*)>4;
练习：
--(1)工资最小值小于两千的职位
--(2)平均工资大于1200的部门和工作搭配组合
--(3)统计人数小于4的部门的平均工资
--(4)统计各部门的最高工资，排除最高工资小于3000的部门
 select deptno,max(sal),min(sal) from emp where sal>=2500 
 group by deptno having max(sal)>=3000 order by deptno;
 执行顺序：
 --->from-->where---->group by--->having---->select--->order by 

<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>] 

SELECT语句定义 
一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下： 
SQL代码 

<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>] 
SELECT子句是必选的，其它子句如WHERE子句、GROUP BY子句等是可选的。 
一个SELECT语句中，子句的顺序是固定的。例如GROUP BY子句不会位于WHERE子句的前面。 

SELECT语句执行顺序 
SELECT语句中子句的执行顺序与SELECT语句中子句的输入顺序是不一样的，所以并不是从SELECT子句开始执行的，而是按照下面的顺序执行： 
开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果 
每个子句执行后都会产生一个中间结果，供接下来的子句使用，如果不存在某个子句，就跳过 
对比了一下，mysql和sql执行顺序基本是一样的, 标准顺序的 SQL 语句为: 

select 考生姓名, max(总成绩) as max总成绩 
 
from tb_Grade 
 
where 考生姓名 is not null 
 
group by 考生姓名 
 
having max(总成绩) > 600 
 
order by max总成绩 

 在上面的示例中 SQL 语句的执行顺序如下: 

　　 (1). 首先执行 FROM 子句, 从 tb_Grade 表组装数据源的数据 

　　 (2). 执行 WHERE 子句, 筛选 tb_Grade 表中所有数据不为 NULL 的数据 

　　 (3). 执行 GROUP BY 子句, 把 tb_Grade 表按 "学生姓名" 列进行分组(注：这一步开始才可以使用select中的别名，他返回的是一个游标，而不是一个表，所以在where中不可以使用select中的别名，而having却可以使用，感谢网友  zyt1369  提出这个问题)
　　 (4). 计算 max() 聚集函数, 按 "总成绩" 求出总成绩中最大的一些数值 

　　 (5). 执行 HAVING 子句, 筛选课程的总成绩大于 600 分的. 

　　 (7). 执行 ORDER BY 子句, 把最后的结果按 "Max 成绩" 进行排序. 
--------------------- 
作者：猪哥66 
来源：CSDN 
原文：https://blog.csdn.net/u014044812/article/details/51004754 
版权声明：本文为博主原创文章，转载请附上博文链接！

-----------------------------------------------------92语法------------------------------------------------------------

11.笛卡尔积，等值连接，非等值连接 

select * from emp;---14条
 select * from dept;---4条
 ---将两张表进行关联：
 select * from emp,dept;---56条数据：  56=14*4   笛卡尔积  ----但是有好多数据是无效的
 想让数据有效，进行有效的连接，
 ---等值连接：
 select * from emp,dept where emp.deptno=dept.deptno;
 select * from emp e,dept d where e.deptno=d.deptno;---deptno字段可以展示两次
 ---过滤其中我想要的某些字段：
  ---查询员工的姓名，职位，薪水，部门名称。
  select ename,job,sal,dname from emp e,dept d where e.deptno=d.deptno; 
  ---查询员工的姓名，职位，薪水，部门名称,部门编号。
  select ename,job,sal,dname,d.deptno from emp e,dept d where e.deptno=d.deptno; 
  建议你所有字段都加上所属的表：更加精确的查询，效率更高 
 select e.ename,e.job,e.sal,d.dname,d.deptno from emp e,dept d where e.deptno=d.deptno; 
 
 
 ----查询员工的工资，和工资对应的等级：
 
 select * from emp;
 select * from salgrade;
 ----非等值连接：
 select * from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal ;

12.外连接，自连接

 -----外连接：
 左外连接，右外连接：（哪个表数据是全的，就是什么连接）
---右外连接
select * from emp e,dept d where e.deptno(+)=d.deptno ;
---左外连接
select * from emp e,dept d where e.deptno=d.deptno(+) ;
----全外连接：  （92语法中没有全外连接）
select * from emp e,dept d where e.deptno(+)=d.deptno(+) ;
----自连接：
----查看员工表：
select * from emp for update;
----查看员工编号，员工职位，员工领导的编号，员工领导的职位
select e1.empno,e1.job,e1.mgr,e2.empno,e2.job from emp e1,emp e2 where e1.mgr=e2.empno;


-----出题：
查询员工的姓名，员工编号，职位，部门编号，部门名称，工资，工资等级。
select e.ename,e.empno,e.job,e.deptno,d.dname,e.sal,s.grade,s.losal,s.hisal from emp e,dept d,salgrade s 
where e.deptno=d.deptno and e.sal>=s.losal and e.sal<=s.hisal; 

---------------------------------------------------99语法------------------------------------------------------

13.99语法版连接

---92:两表关联：
select * from emp e,dept d;---笛卡尔积
---99：两表关联：
select * from emp cross join dept;---交叉连接
---92:两表连接：等值连接
select * from emp e,dept d where e.deptno=d.deptno; deptno展示两次
---99:两表连接：自然连接
select * from emp natural join dept;
99特点：
【1】deptno字段展示1次
【2】自然连接，会自动连接两个表名字相同，类型相同的字段
【3】匹配所有的相同的字段。
比如现在我就想匹配某一个字段，怎么办呢？
select * from emp inner join dept using (deptno);--你指定哪个字段，底层执行就去关联哪个字段！
比如现在我想要关联两张表，但是两个表的关联字段的名字不一样。
select * from 表1 inner join 表2 on 表1.字段名 =表2.字段名 where 过滤条件;
on:后面加的是连接表的过滤条件
where ：在全是有效数据的基础上，展示的行的过滤的条件
---92：外连接：
select * from emp e,dept d where e.deptno(+)=d.deptno;
select * from emp e,dept d where e.deptno=d.deptno(+);
select * from emp e,dept d where e.deptno(+)=d.deptno(+);--错误
----99：
select * from emp e left join dept d on e.deptno=d.deptno;
select * from emp e right join dept d on e.deptno=d.deptno;
select * from emp e full join dept d on e.deptno=d.deptno;