Oracle查询语句及运用语句整理

基于很久没用到Oracle了最近有点怀念，看了很多博客至此没有看到满意的故此整理，当然这是基础的另外迪卡尔集及函数会后期码上，初学Oracle数据库的人都会经常在一些书籍中或者视频课程中提到几个常用的数据库表格，如EMP/DEPT/SALGRADE等具体这几个表格是在什么地方使用呢？首先要明确这几个表格的位置：这几个表格存储在系统帐号SCOTT下，使用下面命令即可配置，小编采用的是PLSQL登录Oracle操作SQL语句激活scott用户。

SQL它是一种语言，是关系型数据库的基本操作语言（主要的关系型数据库：SQL SERVER 、MySQL、Oracle）

SQLPlus他是一种环境，是与Oracle数据库进行交互的客户端工具

SQL语句不区分大小写；关键字不能进行缩写，例如：SELECT

SQLPlus命令也不区分大小写；关键字可以缩写，例如：CONNECT --》CONN

SQL语句及SQLPlus语句常用的关键函数

--SQL语句
CREATE 创建
ALTER  修改
DROP, TRUNCATE, DELETE   删除
SELECT 查询
INSERT 插入
UPDATE 更新,修改
GRANT  REVOKE DCL定义权限
COMMIT,ROLLBACK,SAVEPOINT TCL控制语言
SQL语句需要以“;”号结束
--SQLPlus命令
Desc[RIBE] 用于列出数据库对象的定义
SHOW 用于显示环境变量值，例如show databases
Conn[ECT],COL[UMN],PASS[WORD]等

修改用户密码实战

--SQL语句
ALTER USER 用户名 IDENTIFIED BY 新密码;
--SQLPlus命令
PASSWORD:该命令用于修改用户的口令
PASSW[ORD][USERNAME],USERNAME用于指定用户名，修改注意的是如果修改其它用户口令则需要一DBA身份进行登录（SYS和SYSTEM）
举例说明：CMD输入SQLPlus--》用户名--》用户密码--》结果进入该用户

查询表中别名的表字段

--SQL语句
SELECT ENAME AS '雇员名',SAL AS '工资' FROM EMP;
--SQLPlus命令
col ename heading '雇员名'
col sal heading '工资'
select ename,sal from emp

查询出结果后证明已修改成功，当然也可以进入自己的用户在进行修改

--在scott用户下面默认的scott用户是被锁定的先解锁就能登陆上了使用下面的语句解锁scott: 
alter user scott account unlock; 
--解锁之后可能会要求你修改密码： 
alter user scott identified by newpassword; 
--再登陆： 
sqlplus scott/newpassword
SELECT * FROM EMP查询结果如下
1	7369	SMITH	CLERK	7902	1980/12/17	800.00		20
2	7499	A_LLEN	SALESMAN	7698	1981/2/20	1600.00	300.00	30
3	7521	WARD	SALESMAN	7698	1981/2/22	1250.00	500.00	30
4	7566	JONES	MANAGER	7839	1981/4/2	2975.00		20
5	7654	MARTIN	SALESMAN	7698	1981/9/28	1250.00	1400.00	30
6	7698	BLAKE	MANAGER	7839	1981/5/1	2850.00		30
7	7782	CLARK	MANAGER	7839	1981/6/9	2450.00		10
8	7788	SCOTT	ANALYST	7566	1987/4/19	3000.00		20
9	7839	KING	PRESIDENT		1981/11/17	5000.00		10
10	7844	TURNER	SALESMAN	7698	1981/9/8	1500.00	0.00	30

一、基础查询语法结构

--讲一下本文章的大纲先来熟悉一下
SELECT * |{[DISTINCT]COLUMN | EXPRESSION [ALIAS],...}
FROM TABLE [WHERE CONDITION]
TABLE  用于指定表名
COLUMN 用于指定列名
EXPRESSION 用于指定表达式
ALIAS 用于指定列的表名
CONDITION 用于指定查询条件
--特定列查询也就是常说的根据表字段查到具体的某个值
--全部列查询 这个就是不加表字段条件
--排除重复行关键字 distinct 
--条件查询：过滤数据(比较运算符的使用，范围查询，空值判断，逻辑运算符的使用)

这里先讲一下Distinct因为期初小编也给整懵了一下（这里需要的是表字段完全一致才可以否则不生效）

--排除重复行关键字 distinct
基本格式：Select  Distinct 列名  * From Table
原始数据查询：举例：SELECT deptno,job From EMP;
1	20	CLERK
2	30	SALESMAN
3	30	SALESMAN
4	20	MANAGER
5	30	CLERK
--需要注意的是当重复的数据中有空的表字段时也会查询出来
去重处理查询举例：SELECT DISTINCT(deptno),job From EMP;
1	20	CLERK
2	30	SALESMAN
3	20	MANAGER
4	30	CLERK

Column表字段单表查询

SELECT * FROM EMP
EMPNO,ENAME,MGR,SAL 分别对应雇员编号，雇员名，工资，补助
别名 AS 后可以加''可不加 FROM 来自于哪个表
连接符 || 举例：SELECT ENAME||'的岗位是：'||JOB FROM EMP
字符串可以使SELECT列表中的字符，数字，日期，每次返回一行是字符串也会被输出一次
--应用scott.emp表查询雇员编号，雇员名，雇员名称，雇员的年收入
SELECT EMPNO,ENAME,MGR,MGR*12 AS SUM_MONKEY FROM EMP
--查看雇员编号，雇员名，年收入（年薪加半月工资）
SELECT EMPNO,ENAME,MGR*12+MGR/2 AS Year_MONKEY FROM EMP
--查看雇员编号，雇员名，加1薪后的年收入
SELECT EMPNO,ENAME,MGR*12+MGR AS Year_MONKEY FROM EMP
--查看雇员编号，雇员名，每月工资涨5200后的年收入
SELECT EMPNO,ENAME,(MGR+5200)*12 AS Year_MONKEY FROM EMP
--查询雇员编号，雇员名，工资，补助计算出实发工资的信息
SELECT EMPNO,ENAME,MGR+SAL FROM EMP --需要注意的是在算术运算中有NULL时，其显示为NULL

日期列的查询设置格式化及引入字符

--查询的数据类型为日期类型的列，如DATA类型，默认显示格式为DD--MON-RR;
alter session set nls_language='SIMPLIFIED CHINESE'; --设置简体中文格式
alter session set nls_language='AMERICAN' --设置美国英语格式
alter session set nls_date_format='YYYY/MM/DD'; --设置特定格式
--熟悉在WHERE子句中使用数字值，字符值，日期值的格式，可直接引用数字值，也可以但引号引入字符值
--关键字 WHERE
数字值举例：SELECT * FROM EMP WHERE DEPTNO=20;
字符值举例：SELECT * FROM EMP WHERE ENAME='SCOTT';
日期值举例：SELECT * FROM EMP WHERE HIREDATE='1980/12/17';

二、条件查询：过滤数据(比较运算符、范围查询，空值判断，逻辑等运算符使用)

= > < >= <= <>或!=比较运算符

--比较运算符 = > < >= <= <>或者写成!=
等于运算举例：查询EMP表中MGR等于7698的用户信息
SELECT * FROM EMP WHERE MGR=7698;
大于运算举例：查询EMP表中MGR大于等于7698的用户信息
SELECT * FROM EMP WHERE MGR>=7698;
小于运算举例：查询EMP表中MGR小于7698的用户信息
SELECT * FROM EMP WHERE MGR<7698;
大于等于举例：查询EMP表中MGR大于等于7698的用户信息
SELECT * FROM EMP WHERE MGR>=7698;
大于小于举例：查询EMP表中MGR大于等于7698的用户信息
SELECT * FROM EMP WHERE MGR>7698 AND MGR<7789;
小于等于举例：查询EMP表中MGR小于等于7698的用户信息
SELECT * FROM EMP WHERE MGR<=7698;
不等于运算举例：查询EMP表中MGR不等于7698的用户信息
SELECT * FROM EMP WHERE MGR!=7698;

Between ... And 范围查询

--范围查询 BETWEEN ... AND 用于指定俩者键包含的边界
--语法格式：BETWEEN 较小值 AND 较大值
--举例：查询出EMP表中MGR是7521-8000的用户信息
SELECT * FROM EMP WHERE MGR>=7521 AND MGR<=8000;
等价于：SELECT * FROM EMP WHERE MGR BETWEEN 7521 AND 8000;
--范围查询 IN 执行列或表达式结果匹配列表中的任意一个值
--语法格式：IN(值1,值2,...值n)
举例：查询出EMP表中JOB是MANAGER,CLERK的用户信息
SELECT * FROM EMP WHERE JOB IN('MANAGER','CLERK');
等价于：SELECT * FROM EMP WHERE JOB='MANAGER' OR JOB='CLERK';

LIKE %_以及ESCAPE模糊查询

--模糊查询LIKE 当执行模糊查询是需要使用通配符%或者_
通配符%：用于表示0个或者多个字符
通配符_：用于表示单个字符
举例说明:查询出EMP表中ENAME以S开头的用户信息
SELECT * FROM EMP WHERE ENAME LIKE 'S%';
等价于：SELECT * FROM EMP WHERE ENAME LIKE 'S____';
同理等价于：SELECT * FROM EMP WHERE ENAME LIKE 'S_%';
--模糊查询中特殊符号的处理 关键符 ESCAPE \
举例：查询出EMP表中ENAME中A开头第二个为_的用户信息 这个表中没有这个信息需要的同学UPDATA一下
UPDATE  EMP SET  ENAME='A_LLEN' WHERE ENAME='ALLEN';
--先来查看一下没有特殊处理过查询
SELECT * FROM EMP WHERE ENAME LIKE 'A_%' ESCAPE '\';
1 7499  A_LLEN  SALESMAN  7698  1981/2/20 1600.00 300.00  30
2 7876  ADAMS CLERK 7788  1987/5/23 1100.00   20
--使用特殊符号处理
SELECT * FROM EMP WHERE ENAME LIKE 'A\_%' ESCAPE '\';
1 7499  A_LLEN  SALESMAN  7698  1981/2/20 1600.00 300.00  30

IS [NOT] NULL判断运算符

判断空值 IS NULL 用于判断列或表达式结果是否为空可以用IS NULL 或IS NOT NULL但是不能使用=NULL或!=NULLlai1判断
举例说明：查询出EMP表中COMM字段为空的信息
SELECT * FROM EMP WHERE COMM IS NULL;
举例说明：查询出EMP表中COMM字段不为空的信息
SELECT * FROM EMP WHERE COMM IS NOT NULL;

NOT/AND/OR逻辑运算符

--逻辑运算符 AND OR NOT分别对应的是并且,或,否
--AND:主要指定同时满足的俩个条件或多个条件
--OR:主要用于多个条件只要满足其中一个即可
--NOT：用于指定相反的条件
--AND逻辑操作符举例说明：查询出EMP表中JOB为MANAGER,并且DEPTNO=10的用户信息
SELECT * FROM EMP WHERE JOB='MANAGER' AND DEPTNO=10;
--OR逻辑操作符举例说明：查询出EMP表中JOB为MANAGER 或者SAL>3000的用户信息
SELECT * FROM EMP WHERE JOB='MANAGER' OR SAL>3000;
--NOT逻辑操作符举例说明：查询出EMP表中JOB除了CLERK,SALESMAN之外的用户信息
SELECT * FROM EMP WHERE JOB NOT IN('CLERK','SALESMAN');
--这里回顾一下IN操作符：查询出EMP表中JOB值为CLERK,SALESMAN的用户信息
SELECT * FROM EMP WHERE JOB IN('CLERK','SALESMAN');
--总结一下这三个操作符的优先级：NOT优先级最高，AND其次，OR优先级最低
--举例说明：查询出EMP表中SAL>200或者DEPTNO=30的，并且JOB不是CLERK,SALESMAN的用户信息
SELECT * FROM EMP WHERE (SAL>2000 OR DEPTNO=30)AND JOB NOT IN('CLERK','SALESMAN');

*/+-算术运算符

--算数运算符
alter user scott account unlock;
alter user scott identified by QING0201; 
SELECT * FROM EMP
--应用scott.emp表查询雇员编号，雇员名，雇员名称，雇员的年收入
SELECT EMPNO,ENAME,MGR,MGR*12 AS SUM_MONKEY FROM EMP
--查看雇员编号，雇员名，年收入（年薪加半月工资）
SELECT EMPNO,ENAME,MGR*12+MGR/2 AS Year_MONKEY FROM EMP
--查看雇员编号，雇员名，加1薪后的年收入
SELECT EMPNO,ENAME,MGR*12+MGR AS Year_MONKEY FROM EMP
--查看雇员编号，雇员名，每月工资涨5200后的年收入
SELECT EMPNO,ENAME,(MGR+5200)*12 AS Year_MONKEY FROM EMP
--查询雇员编号，雇员名，工资，补助计算出实发工资的信息
SELECT EMPNO,ENAME,MGR+SAL FROM EMP --需要注意的是在算术运算中有NULL时，其显示为NULL
SELECT ENAME||'的岗位是：'||JOB FROM EMP

ASC/DESC排序数据

--排序数据 关键字ORDER BY  ASC升序 DESC倒序 默认不加的时候为升序
--语法结构：SELECT * |COLUMN[,COLUMN...]FROM TABLE [WHERE CODITION] [ORDER BY COLUMN [ASC|DESC]]
--单列升序举例说明：查询出EMP表中所有的SAL的ENAME,SAL信息并以SAL升序显示
SELECT ENAME,SAL FROM EMP ORDER BY SAL ASC;
--等价于默认不加的时候为升序举例说明：
SELECT ENAME,SAL FROM EMP ORDER BY SAL;
1	KING	3000.00
2	FORD	5000.00
--单列降序举例说明：
SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC;
1	KING	5000.00
2	FORD	3000.00
--空值升降序举例说明：采用EMP表如果没有该表的同学自己创建吧，空值默认为最大
SELECT COMM FROM EMP ORDER BY COMM;
SELECT COMM FROM EMP ORDER BY COMM ASC ;
1	0.00
2	NULL
SELECT COMM FROM EMP ORDER BY COMM DESC ;
1	NULL
2	0.00