Oracle（一）数据查询

参考网址
https://blog.csdn.net/wzj_110/article/details/80443385

一、简单查询

1.清屏：HOST CLS
2.显示当前用户：SHOW USER
3.显示当前用户下的所有的表名称:SELECT * FROM tab
4.设置表的显示格式：

查询列宽：SHOW LINESIZE;
设置列宽：SET LINESIZE 300;
设置每页显示的数据量：SET PAGESIZE 30

5.查询内容

SELECT * FROM emp;		--查询表的所有字段
SELECT ename,job,sal FROM emp;		--查询表的enam,job sal字段
SELECT ename AS 姓名,job AS 职位,sal AS 薪资 FROM emp;		--查询表的enam,job sal字段，并修改成中文名字输出
SELECT ename 姓名,job 职位,sal 薪资 FROM emp;		--查询表的enam,job sal字段，并修改成中文名字输出
SELECT job FROM emp;		--查询job字段，按照表格原本样式输出，未做删除重复项处理
SELECT DISTINCT job FROM emp;		--查询job字段，删除重复项后输出
SELECT DISTINCT job,sal FROM emp;		--查询job,sal字段，删除job和sal字段均重复的数据后输出

//可以对数字类型的字段使用四则运算
SELECT ename,sal*12 年薪 FROM emp;		--计算基本年薪

注意：null参与运算结果都为null 
SELECT ename,sal,comm,sal+comm 月薪 FROM emp;		--基本薪资+绩效，计算月薪，但是comm为空的数据计算的月薪结果也为空
SELECT ename,sal,comm,sal+nvl(comm,0) 月薪 FROM emp;		--如果comm为空，则作为0计算

6.修改语句的错误单词

SELECT * FORM emp;		--FROM错写成了FORM
C /FORM/FROM		--C就是change的意思
/		--再次执行

二、限定查询（开发中最常用）

基本语法：
SELECT *
	FROM 数据来源，数据来源...
		WHERE 过滤条件

（一）关系运算符

1.基本工资高于1500的雇员的信息

SELECT * FROM emp WHERE sal>1500;

2.查询smith的完整信息
注意：如果数据表中smith是大写，那么用小写的smith是无法查询到信息的，大小写需要一直保持一致

SELECT * FROM emp WHERE ename='SMITH';

3.查询职位不是销售人员的雇员编号、姓名、职位

SELECT empno,ename,job FROM emp WHERE JOB!='SALESMAN';
SELECT empno,ename,job FROM emp WHERE JOB<>'SALESMAN';

注意，不可以使用IS NOT
SELECT empno,ename,job FROM emp WHERE JOB IS NOT 'SALESMAN';

（二）逻辑运算符

1.并且关系
查询工资在1500~3000之间的雇员信息
BETWEEN AND 可以用来判断数字或日期

SELECT * FROM emp WHERE sal>=1500 AND sal<=3000;
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;

2.或者关系
使用UNION ALL可以将两个查询结果并到一起显示（但是这两个查询的关系只能是或者【or】关系），一般用来代替OR条件查询，可以避免索引失效

（1）查询工资大于2000或者职位是办事员的信息

SELECT * FROM emp WHERE sal>2000 OR job='CLERK';

SELECT * FROM emp WHERE sal>2000 
UNION ALL SELECT * FROM emp WHERE job='CLERK'; 

（2）查询所有在1981年入职的雇员的信息

SELECT * FROM emp WHERE hiredate>='01-1月-81' AND hiredate<='31-12月-81';
SELECT * FROM emp WHERE hiredate BETWEEN '01-1月-81' AND '31-12月-81';

3.空判断IS NOT null
（1）查询所有领取佣金的雇员信息
查询没有佣金的雇员的信息

SELECT * FROM emp WHERE comm IS NOT null;
SELECT * FROM emp WHERE comm IS null;

4.IN查询（满足多个条件查询）
查询出编号是7369，7765，7788的员工的信息

SELECT * FROM emp WHERE empno=7369 OR empno=7765 OR empno= 7788;

SELECT * FROM emp WHERE empno=7369
UNION ALL SELECT * FROM emp WHERE empno=7765
UNION ALL SELECT * FROM emp WHERE empno=7788;

SELECT * FROM emp WHERE empno IN(7369,7765,7788);		--编号是7369，7765，7788
SELECT * FROM emp WHERE empno NOT IN(7369,7765,7788);		--编号不是7369，7765，7788

三、模糊查询LIKE

_表示任意一位字符
%表示任意位数的任意字符
LIKE 关键字实现模糊查询

格式：
SELECT * FROM 数据来源 WHERE 模糊查询的字段 LIKE 模糊查询的关键字;

SELECT * FROM emp WHERE ename LIKE 'A%';		--查询姓名是以A开头的雇员信息
SELECT * FROM emp WHERE ename LIKE '_A%';		--查询姓名的第二个字母是A的员工信息
SELECT * FROM emp WHERE ename LIKE '%a%';		--查询姓名包含A的雇员的信息

四、排序查询ORDER BY

将查询的结果按照指定的字段进行升序或者降序显示，这个指定的字段必须是数字或者日期才有意义。ORDER BY关键字

格式：
SELECT *
FROM 数据来源
WHERE 过滤条件
ORDER BY 排序的字段 ASC | DESC

SELECT * FROM emp ORDER BY sal DESC;		--查询所有的员工信息，按照薪资降序排列
SELECT * FROM emp WHERE job='SALESMAN' ORDER BY hiredate ASC;		--查询所有的销售人员信息，按照雇佣日期从早到晚排列
SELECT empno,ename,sal*12 年薪 FROM emp ORDER BY 年薪;		--查询每个的编号、姓名、年薪，按照年薪升序排序

五、多表查询（连接查询）

注意：数据量大的时候不要用连接查询，会产生很大的笛卡尔积导致性能耗费。数据量小时可使用，方便。

格式：
SELECT * 
FROM 数据来源，数据来源...
WHERE 过滤条件
ORDER BY 排序的字段ASC | DESC;

SELECT COUNT(*) AS 数据条 FROM emp;		--统计emp表中的数据量，使用统计函数COUNT(*|字段)
SELECT COUNT(*) FROM dept;		--统计dept表中的数据量
SELECT * FROM emp,dept;		--查询员工的姓名薪资等信息，以及部门名称和地址信息（错误查询，结果共14*4条，有笛卡尔积现象）
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;		--对上条需求的正确示范一
SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;		--对上条需求的正确示范二（别名的使用，不可以用AS，例如emp AS e这样）

//查询雇员的编号、姓名、职位、薪资、所在部门和位置和部门编号
SELECT empno,ename,job,sal,dname,loc,deptno FROM emp e,dept d WHERE e.deptno=d.deptno;		--错误示范，SELECT子句的deptno未指明来自于哪个表，导致错误
SELECT empno,ename,job,sal,dname,loc,e.deptno FROM emp e,dept d WHERE e.deptno=d.deptno;		--正确示范

六、连接查询（内连接和外连接）

内连接查询：只有满足条件的数据才会被显示
外连接查询：可以控制不满足条件的数据是否显示
左外连接查询：可以让左表不满足条件的也显示
右外连接查询：可以让右表不满足条件的也显示表
全外连接查询：让左表和右表不满足条件的数据都显示

INSERT INTO emp(empno,ename,job,sal) VALUES(1001,'张三','清洁工',3000.00);		--添加一条新的数据
COMMIT;		--提交
SELECT empno,ename,job,sal,loc,d.deptno FROM emp e,dept d WHERE e.deptno=d.deptno;		--内连接查询（方式一）
SELECT empno,ename,job,sal,loc,d.deptno FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;		--内连接查询（方式二）

SELECT empno,ename,job,sal,loc,d.deptno FROM emp e,dept d WHERE e.deptno=d.deptno(+);		--左外连接查询，Oracle独有(+)
SELECT empno,ename,job,sal,loc,d.deptno FROM emp e,dept d WHERE e.deptno(+)=d.deptno;		--右外连接查询，Oracle独有(+)
SELECT empno,ename,job,sal,loc,d.deptno FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;		--左外连接，数据库通用
SELECT empno,ename,job,sal,loc,d.deptno FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;		--右外连接，数据库通用
SELECT empno,ename,job,sal,loc,d.deptno FROM emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno;		--全外连接，数据库通用

七、Oracle其他

1.dual伪表
类似于虚拟的表，存在是为了让SQL语法完整，因为SELECT语句后面必须要有FROM 表

SELECT 3+2;		--报错
SELECT 3+2 FROM DUAL;		--正常执行

2.日期和字符串用单引号括起来，类的别名可以用双引号括起来，也可以不写
3.处理字符串的一些函数