oracle---数据的显示、查询、排序 和 常用SQLPlus命令

首先以scott用户登录

1.Oracle 的书写规则

关键字不能缩写
为了增加可读性，SQL 语句关键字大写 ， 其它小写
为了增加可读性，SQL 语句每一个子句放在一行上
为了增加可读性，最好以缩进法书写 SQL

2.数据的显示

列标题的默认格式 :
    日期、字符 数据 --- 左对齐
    数值 数据 --- 右对齐
    默认大写 

日期显示（美式日期和中式日期） 

alter session set
    NLS_DATE_LANGUAGE='american';
alter session set
    NLS_DATE_LANGUAGE='simplified chinese';

设置列表的行宽和列宽 

col hiredate for a15  // 显示宽度为15个字符
col ename for a8
col empno for 99999   // 显示宽度为5个数字位
set linesize 100      // 设置行宽不超过100个字符

算数表达式的使用  

select empno,ename,sal,500+sal from emp;
select empno,ename,sal,500+sal*12 from emp;
select empno,ename,sal,(500+sal)*12 from emp;

给列名替换别名 

select empno as "Employee Number", ename name, (500+sal)*12 "Annual Salary"
    from emp;

连接运算符 ||

如果连接的对象不是列名或列名表达式，而是字符、日期类型(文字字符串)，必须用单引号括起来 

select ename||' annual salary is '||(500+sal)*12 "Employee's Salary"
    from emp;

oracle单引号和双引号的区别

双引号引住关键字：select "sysdate" from dual; 等同于 select sysdate from dual; 
双引号提示oracle严格区分大小写：
select * * from emp where "ENAME" = = scott; ("ENAME"换成小写不行)

单引号在 Oracle 中有三种身份：引用一个字符串常量、转义符、表示它本身
举例：select '''' from dual; 
第1 、 4个单引号表示引用一个字符串常量，中间是一对单引号， 此对中的第一个是转义符，对第二个进行转义， 结果是只有一个单引号字符 。

3.简单查询和限定性查询

//选择
select * from emp;
//投影
select empno,ename,sal from emp;

distinct 关键字

select deptno from emp;
select distinct deptno from emp;

WHERE 关键字

SELECT empno, ename, sal
FROM emp
WHERE sal>=1500;

// BETWEEN AND 比较运算符
SELECT empno, ename, sal
FROM emp
WHERE sal BETWEEN 1500 AND 2900;

SELECT empno, ename, sal
FROM emp
WHERE sal NOT BETWEEN 1500 AND 2900;

// IN 比较运算符
SELECT empno, ename, sal,job
FROM emp
WHERE job IN ('SALESMAN', 'CLERK', 'MANAGER');

SELECT empno, ename, sal,job
FROM emp
WHERE job NOT IN ('ANALYST','PRESIDENT');

// LIKE 比较运算符(“%”代表0个或多个字符、“_”代表1个字符)
SELECT empno,ename,sal,job
FROM emp
WHERE job LIKE 'SAL%';

SELECT empno, ename, sal,job
FROM emp
WHERE job LIKE 'S_L_S%' ;

// 转义关键字ESCAPE(如果要查询的字符串中含有“_”或“%”)
SELECT *
FROM dept_temp
WHERE dname LIKE 'IT\_%' ESCAPE '\';

4.数据的排序

// ORDER BY，默认升序
SELECT empno,ename,sal
FROM emp
WHERE sal >=1500
ORDER BY sal;

// 降序
SELECT empno,ename,sal
FROM emp
WHERE sal>=1500
ORDER BY sal DESC;

// 在ORDER BY中可以使用别名
SELECT empno AS "Employee Number", ename name, (500+sal)*12 "Annual Salary"
FROM emp
ORDER BY "Annual Salary" DESC;

// 在ORDER BY中也可以使用表达式
SELECT empno AS "Employee Number", ename name, (500+sal)*12 "Annual Salary"
FROM emp
ORDER BY (500+sal)*12 DESC;

// 在ORDER BY中也可以使用列号
SELECT empno AS "Employee Number", ename name, (500+sal)*12 "Annual Salary"
FROM emp
ORDER BY 3 DESC;

// 在ORDER BY中也可以按照多列排序
SELECT ename,job,sal
FROM emp
ORDER BY job,sal DESC;

5.Oracle 服务

Oracle ORCL VSS Writer Service ：Oracle卷映射拷贝写入服务
OracleDBConsoleorcl：Oracle 数据库控制台服务，orcl 是 Oracle 的实例标识
OracleJobSchedulerORCL：Oracle 作业调度(定时器) 服务，ORCL是 Oracle 实例标识
OracleMTSRecoveryService：服务端控制
OracleOraDb11g_home1ClrAgent：Oracle 数据库， .NET 扩展服务的一部分。
OracleOraDb11g_home1TNSListener ：监听器服务，只有在数据库需要远程访问的时候才需要
OracleServiceORCL：数据库服务，是数据库启动的基础(必须启动)

6.常用SQLPlus命令

SQL*Plus是与oracle进行交互的客户端工具。在 SQL*Plus中，可以输入SQL语句。
SQL*Plus将用户输入的SQL语句发送给Oracle服务器，服务器执行SQL 语句将结果返回SQL*Plus 。
SQL语句执行完后，被保存在一个被称为sql buffer的内存缓冲区中，且只保存一条最近执行的SQL 语句。
与 SQL 语句不同， SQL*Plus 命令并不存入SQL缓冲区，在使用时可以缩写。

L[IST] ： 显示 SQL缓冲区中的内容
n text ： 修改 SQL缓冲区中第n行文本为 text 
重新运行SQL 缓冲区中的 SQL：/ 或者 RUN

SELECT empno,ename,job
FROM dept
WHERE sal>=1500
ORDER BY job,sal DESC;

LIST
2 FROM emp
LIST

/

n ： 指定缓冲区中的第n行为当前行
A[PPEND] ： 给缓冲区中当前行追加内容

SELECT empno FROM emp;

LIST
1
A ,ename,job
LIST

DEL ： 删除缓冲区中的1行或若干行

DEL 3
LIST

C[HANGE]：修改缓冲区中的当前行

SELECT empno,ename,job
FROM dept
WHERE sal>=1500
ORDER BY job,sal DESC;

2
C /dept/emp
LIST
/

SAVE 命令 ： 把 SQL 缓冲区中的内容存入指定的文件
GET ： 将脚本文件装入 SQL 缓冲区
ED[IT] ： 直接编辑脚本文件

SELECT empno,ename,job
FROM emp
WHERE sal>=1500
ORDER BY job,sal DESC;

SAVE d:\sample.sql
GET d:\sample.sql
EDIT d:\sample.sql

@(START) ： 将指定脚本文件的内容装入 SQL 缓冲区并运行

@ d:\sample.sql
START d:\sample.sql

SPOOL ： 将把一句或者多句sql语句输出保存为列表文件。当要用 SQL 语句生成大的报表时，很有用

SPOOL d:\output.lst
SELECT empno,ename,job,sal
FROM emp
WHERE sal>=1500
ORDER BY job,sal DESC;
SELECT * FROM dept;
SPOOL OFF;

格式化指令

// COLUMN 列名 for 格式化模式
COLUMN sal for $99,999.99
SELECT ename,job,sal FROM emp WHERE sal>=1500;
COLUMN sal for L99,999.99
SELECT ename,job,sal FROM emp WHERE sal>=1500;

// COLUMN 列名 heading '列标题'
SELECT * FROM dept;
COLUMN loc heading '地点'
SELECT * FROM dept;

// 一旦设置了某一列的显示格式和显示标题，这些设置就一直保留在SQL*Plus中，直到退出SQL*Plus为止
// 查看列的显示格式和显示标题
COLUMN 列名

// 将列的属性重新
COLUMN 列名 clear

获取 SQL*Plus 的帮助信息 

help 命令名

7.SQLPlus环境变量

设置环境变量：SET 环境变量 变量的值
显示环境变量：SHOW 环境变量 |ALL
①环境变量 echo

SET echo off
SELECT * FROM salgrade;
save d:\sql\echo_sample.sql
@ d:\sql\echo_sample.sql // 只返回查询结果
SET echo on
@ d:\sql\echo_sample.sql // 返回查询结果和查询命令

②环境变量 feedback
SET feedback 命令的格式 ： SET feedback {n|OFF|ON}
set feedback n:当一条sql语句返回n或者大于n行记录的时候，就会有feedback

③其他环境变量

SET hea[ding] { ON |OFF}    // 是否显示列的标题
SET array[size] { 20 |n}    // 每次从数据库中获取的行数 ， 最多为 5000 行
SET line[size] { 80 |n}     // 每行的字符数
SET page[size] { 24 |n}     // 每页的行数