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

 

EMP表

DEPT表

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';