Oracle基础

**

oracle的简单查询 *

★不带条件的查询
▼查看表结构: desc 表名；
desc table_name
desc emp;
▼查看表中所有列;(尽量少用select )
select * from emp;
▼查看部分列；
select ename,sal,job from emp;
▼去掉重复的行 distinct
select distinct job,sal from emp;
★使用算术运算符
▼查询emp表中所有员工的姓名，年薪 (不考虑奖金)
select ename, sal12 from emp;
▼查询emp表中所有员工的姓名，年薪 (考虑奖金)
如下查询，结果没有奖金的人，年薪都为空。因为在oracle中任何数据和null运算都成为null
select ename,(sal+comm)*12 from emp;
正确写法 要使用nvl函数处理空值 nvl(comm,0)
select ename,(sal+nvl(comm,0))*12 from emp;
给列取别名；
select ename,(sal+nvl(comm,0))*12 as “年薪” from emp;
as 可以省略
★带条件的查询:
▼查询20号部门的员工信息
select * from emp where deptno=20;
▼查询SMITH的工作岗位，工资，部门编号
select job,sal,deptno from emp where ename=‘SMITH’;
▼查询出工资高于3000的员工姓名，工资，部门编号
select ename,sal,deptno from emp where sal>3000;
▼查询出1981.6.30号之后入职的员工信息
dd-MM-yy’
select * from emp where hiredate>‘30-6月-81’;
▼查询出年薪（计算奖金，年薪按12个月计算）高于20000的员工姓名、工资、奖金、年薪
select ename,sal,comm,(sal+nvl(comm,0))*12 as “年薪” from emp where (sal+nvl(comm,0))*12>20000;
★使用逻辑操作符: and or in != between… and…
▼查询出工资在2000–3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;
▼查询出编号为7369,7499,7788的员工信息
select * from emp where empno=7369 or empno=7499 or empno=7788;
select * from emp where empno in(7369,7788,7499);
▼查询出工作岗位不为MANAGER的员工信息
select * from emp where job!=‘MANAGER’;
select * from emp where job not in(‘MANAGER’);
▼查询出20号部门中工资高于或等于2000的员工信息
select * from emp where deptno=20 and sal>=2000;
▼查询出10号部门中的所有经理，和20号部门中不是经理但是工资高于2000的员工信息
select * from emp where deptno=10 and job=‘MANAGER’ or deptno=20 and job !=‘MANAGER’ and sal>2000;

表查询

**

Name Type Nullable Default Comments

---

EMPNO NUMBER(4) 员工的编号
ENAME VARCHAR2(10) Y 员工的姓名
JOB VARCHAR2(9) Y 员工的工作
MGR NUMBER(4) Y 上级编号
HIREDATE DATE Y 入职日期
SAL NUMBER(7,2) Y 工资
COMM NUMBER(7,2) Y 奖金
DEPTNO NUMBER(2) Y 部门的编号

Name Type Nullable Default Comments

---

DEPTNO NUMBER(2) 部门的编号
DNAME VARCHAR2(14) Y 部门的名称
LOC VARCHAR2(13) Y 部门的地址

–基本的查询操作
语法;

select [all|distinct][*|column_name] from table_name
[where expression]
[group by column_name]
[having condition]
[order by column_name] asc desc

–基本的查询（简单查询）
–显示表中所有的数据
select * from emp;
–查询员工的编号和姓名
select empno,ename from emp;
–查询所有工作，去掉重复的值
select distinct job from emp;
–表中字段的别名
–查询表中的员工姓名和工作
select ename as “姓名”,job as “工作” from emp;

–使用运算符
–查询表中员工的姓名，工作和年薪（不计算奖金）
select ename,job,sal*12 as “年薪” from emp;

–条件查询
–查询emp表中工资大于等于3000的员工信息
select * from emp where sal>=3000;
–查询emp表中工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;

between 1 and 2
select * from emp where sal between 2000 and 3000;

–模糊查询
%代表一个或多个字符
_代表一个字符
–查询emp中姓名以M开头的员工信息
select * from emp where ename like ‘M%’;
–查询emp中姓名以H结尾的员工信息
select * from emp where ename like ‘%H’;
–查询emp中姓名中含有M的员工信息
select * from emp where ename like ‘%M%’;
–查询emp中姓名的第二字母是L的员工信息
select * from emp where ename like ‘_L%’;

select * from emp where length(ename)=5;

–排序
如果希望对查询的结果进行排序，就需要使用order by子句，order by子句可以将查询的结果按照升序(asc)或者降序(desc)进行排列
–查询emp中员工的姓名，工作，工资和奖金，并按工资从高到低排序
select ename,job,sal,comm from emp order by sal desc;

–分组查询
分组的操作可以使用GROUP BY子句与HAVING子句实现，GROUP BY子句用于指定分组的列，HAVING语句用于指定分组的条件
–一般情况下，分组查询要结合oracle系统函数进行使用
–avg() 平均数
–sum() 求和
–max() 最大值
–min() 最小值
–count() 总数

–查询emp表中每个部门的平均工资与部门编号
select deptno,avg(sal) from emp group by deptno;

–查询emp表中平均工资高于2000的部门信息
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

–子查询与多表查询
如果某个查询依赖于另外一个查询语句的结果，那么这样就会形成一个子查询结构
–单行子查询
查询结果只有一行的查询
–查询emp表中ford的上级信息
select * from emp where empno=(select mgr from emp where ename=‘FORD’);
–获取到20号部门中高于员工平均工资的雇员信息
select * from emp where sal>(select avg(sal) from emp) and deptno=20;

–多行子查询
子查询的结果为多行一列
多行子查询一般结合 in any all
–查询部门名称不等于“SALES”的员工信息
emp dept 通过外键关联
select dname from dept-- sales

–查询sale