SQLPlus

3.3、SQLPlus

对于Oracle的前台工具而言，个人有一些看法：

Oracle 9i：OEM（Oracle的企业管理器），sqlplusw.exe（采用命令行窗口的方式显示，这种方式可以自己控制），sqlplus.exe（是sqlplusw的命令行版）

Oracle 10g：sqlplusw.exe，sqlplus.exe，EM（在线的企业管理器）

Oracle 11g：sqlplus.exe，SQL Developer

Oracle 12c：sqlplus.exe，SQL Developer（比较成熟）

对于sqlplus而言，是从最早一直延续到今天的习惯，所以，以后的开发之中对于这个工具的一些命令需要清楚。

2.SQLPlus简介

SQLPlus是Oracle数据库提供的一个专门用于数据库管理的交互式工具，使用SQLPlus通过命令行的方式管理Oracle数据库的所有任务，也可以通过SQLPlus执行SQL语句的操作。

如果想讲解sqlplus命令，那么首先还必须解决一个新的问题，数据问题。

在Oracle 12c之中由于存在了CDB和PDB概念，所以所谓的测试数据默认是找不到的。所以首先针对于数据做一个恢复。在Oracle安装文件下找到scott.sql脚本，然后对它修改。所以为了方便，提供了c##scott.sql文件（由于CDB和PDB，所以用户名前必须加c##）。虽然名称是c##scott，但是本质还是scott。

如果想进行数据的配置，执行的顺序如下：

1.首先打开sqlplus.exe：在cmd里输入sqlplus /nolog（直接在cmd里输入sqlplus，是要输入用户名和密码的）

2.执行c##scott.sql文件

下面是c##scott.sql文件内容：

-- 使用超级管理员登录
CONN sys/change_on_install AS SYSDBA ;

-- 创建c##scott用户　　（identified 被识别）
CREATE USER c##scott IDENTIFIED BY tiger ;　　　　

-- 为用户授权　　（grant 授予，允许　　container 集装箱，容器）
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO c##scott CONTAINER=ALL ;

-- 设置用户使用的表空间　　（alter 更改）
ALTER USER c##scott DEFAULT TABLESPACE USERS;
ALTER USER c##scott TEMPORARY TABLESPACE TEMP;

-- 使用c##scott用户登录
CONNECT c##scott/tiger

-- 删除数据表　　（purge 净化，清洗　　salgrade 工资等级表）
DROP TABLE emp  PURGE ;
DROP TABLE dept PURGE ;
DROP TABLE bonus PURGE ;
DROP TABLE salgrade PURGE ;

-- 创建数据表　　（constraint 约束，强制　　primary 主要的，初级的　　bonus 奖金）
CREATE TABLE dept (
    deptno        NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    dname        VARCHAR2(14) ,
    loc        VARCHAR2(13) ) ;
CREATE TABLE emp (
    empno        NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ename        VARCHAR2(10),
    job        VARCHAR2(9),
    mgr        NUMBER(4),
    hiredate    DATE,
    sal        NUMBER(7,2),
    comm        NUMBER(7,2),
    deptno        NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT );
CREATE TABLE bonus (
    enamE        VARCHAR2(10)    ,
    job        VARCHAR2(9)  ,
    sal        NUMBER,
    comm        NUMBER ) ;
CREATE TABLE salgrade ( 
    grade        NUMBER,
    losal        NUMBER,
    hisal        NUMBER );

-- 插入测试数据 —— dept
INSERT INTO dept VALUES    (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES    (30,'SALES','CHICAGO');
INSERT INTO dept VALUES    (40,'OPERATIONS','BOSTON');
-- 插入测试数据 —— emp
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('19-04-1987','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('23-05-1987','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
-- 插入测试数据 —— salgrade
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

-- 事务提交
COMMIT;

3.SQLPlus常用命令

• 设置每行显示的记录长度：SET LINESIZE 300;
• 设置每页显示的记录长度：SET PAGESIZE 30;
• 使用ed命令调用本机记事本程序
• 用户连接数据库：CONN 用户名/密码 [AS SYSDBA];
• 取的当前用户的全部数据对象：SELECT * FROM tab;
• 查看表结构：DESC 表名称;
• 使用本机的操作系统命令：HOST 命令 ...;

1.格式化命令：

SELECT * FROM emp　　# 查询emp表中的全部数据信息

在结果中可以看到，数据显示出现折行的问题。数据的分页也出现了问题。如果要想让数据变的好看一些，首先解决的是屏幕的宽度问题（在命令行的属性中设置），然后使用下面的两个格式化命令。

解决折行问题，设置每行显示300个字符：SET LINESIZE 300;

解决分页问题，设置每页显示数据长度30：SET PAGESIZE 30;

2.在sqlplus中，为了方便编写很长的SQL语句，专门提供了调用本机记事本的命令。可以利用ed命令完成操作。（比如：执行ed.mldn后，因为没有mldn.sql文件，会询问是否创建该文件），选择是，然后打开记事本，在文本之中直接编写之前的数据的查询命令，保存退出，随后可以使用“@”标记，执行程序，输入“@mldn”。 但是实际操作中是在记事本上编写完成后，然后复制粘贴到Oracle中。

3.连接操作

在Oracle中有许多的用户，这些用户彼此之间是可以相互切换的。而切换的基本语法：

CONN 用户名密码 [AS SYSDBA]

如果使用的是sys用户，那么要加上AS SYSDBA的选项。

范例：连接数据库，使用sys用户登录

CONN sys/change_on_install AS SYSDBA;

连接之后，可以输入“SHOW USER”命令来查看当前用户。

如果说通过sys继续查询c##scott用户中的emp表，那么肯定无法查询，这个时候就必须在表前加上用户名，即：c##scott.emp

SELECT * FROM c##scott.emp;

用户名有时候可以被简单的称为模式名称，所以所有的表都具备模式名称的。

即：模式名.表名称。

如果现在要想使用一个普通的用户登录，那么可以在输入命令的时候不编写SYSDBA了。

CONN c##scott/tiger;

范例：一个用户下一定会存在多张数据表，查看所有的表。

SELECT * FROM tab;

从中可以看出c##scott用户中有奖金表（bonus），部门表（dept），职员表（emp），工资等级表（salgrade）。

COL tname FOR A20;    //COL表示局部格式化
COL tabtype FOR A20;
COL clusterid FOR A20;
SELECT * FROM tab;

范例：查看emp表结构

DESC emp

在sqlplus之中除了执行自己的命令之外，也可以调用本机操作系统的命令，那么只需要在相关命令前加上HOST指令即可。

HOST dir

执行这个命令会将用户目录下的所有结构列出来。

HOST copy d:mldn.jpg d:hello.jpg

该命令表示将D盘中的mldn.jpg复制到D盘中的hello.jpg。

3.4、关于原始问题（不重要）

现在的Oracle12c中，虽然选择了要进行样本数据的创建，不过遗憾的是，发现根本就不会出现scott、sh用户。

scott和sh用户真实存在，也在数据库中，可是默认情况下用户所有操作的数据都保存在CDB，而且在这里的用户名称必须以c##开头，例如：c##scott。

第一步：需要使用sys登录

CONN sys/change_on_install AS SYSDBA;

第二步：查看现在的容器名称

SHOW con_name;

可以清楚的发现，现在返回的是一个“CDB$ROOT”，表示的是一个CDB容器。

第三步：改变容器为PDB

ALTER SESSION SET CONTAINER=pdbmldn;

第四步：如果未打开数据库，则先打开

ALTER DATABASE pdbmldn OPEN

如果现在不是在PDB容器之中，那么还需要在命令上增加一个PLUGGABLE，即：

ALTER PLUGGABLE DATABASE pdbmldn OPEN

第五步：查看用户

SELECT username FROM dba_users WHERE usename='SCOTT' OR usename='SH';

第六步：切换回CDB

如果想切换回CDB，只需要重新登陆，或者直接输入切换命令。

ALTER SESSION SET CONTAINER=cdb$root;

3.5、SQL Developer配置

在Oracle的学习中，SQL Developer应用算是现在的重点。这个工具是在Oracle11g后开始提供的。

这个工具依赖Java环境，所以需要进行Java相关命令的配置，路径：D:apporacleuserproduct12.1.0dbhome_1jdkin

而后，首先询问用户是否需要做文件关联，建议别作。

如果要想使用此工具，那么就必须建立新的连接。为了后续学习方便，建议此处建立两个连接：普通用户（c##scott/tiger）、管理员（sys/change_on_install）。 

可以发现在SQL Developer之中具备了格式化的显示功能，所以一些sqlplus中的格式化命令意义就不大了。

3.6、c##scott用户表

在以后讲解的数据库知识中，c##scott用户是主要的操作用户，那么在这个用户之中所存在的几张数据表需要清楚（背）。

部门表：dept

No.	字段名称	类型	描述
1	DEPTNO	NUMBER(2)	表示的是部门的编号，部门的编号由两位数字组成
2	DNAME	VARCHAR2(14)	部门名称，最多是14个字符长度
3	LOC	VARCHAR2(13)	部门位置，最多是13个字符长度

deptno	dname	loc
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

雇员表：emp

No.	字段名称	类型	描述
1	EMPNO	NUMBER(4)	雇员的编号，由4位数字组成
2	ENAME	VARCHAR2(10)	雇员姓名，由10个字符组成
3	JOB	VARCHAR2(9)	职位（工作），由9个字符组成
4	MGR	NUMBER(4)	一个雇员对应的领导的标号，领导也是雇员
5	HIREDATE	DATE	表示的是雇用日期，存放的是日期型数据
6	SAL	NUMBER(7， 2)	基本工资，由2位小数，和5位整数组成，一共7位
7	COMM	NUMBER(7， 2)	佣金（奖金），销售人员可以领取
8	DEPTNO	NUMBER(2)	雇员所属的部门编号，与dept表对应

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800		20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7566	JONES	MANAGER	7839	1981-04-20	2975		20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7689	BLAKE	MANAGER	7839	1981-05-01	2850		30
7782	CLARK	MANAGER	7839	1987-06-09	2450		10
7788	SCOTT	ANALYST	7566	1981-04-19	3000		20
7839	KING	PRESIDENT		1981-11-17	5000		10
7844	TURNER	SALESMAN	7689	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1987-05-23	1100		20
7900	JAMES	CLERK	7698	1981-12-03	950		30
7902	FORD	ANALYST	7566	1981-12-03	3000		20
7934	MILLER	CLERK	7782	1982-01-23	1300		10

工资等级表：salgrade

No.	字段名称	类型	描述
1	GRADE	NUMBER	工资的等级编号
2	LOSAL	NUMBER	此等级的最低工资
3	HISAL	NUMBER	此等级的最高工资

grade	losal	hisal
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

工资补贴条：

No.	字段名称	类型	描述
1	ENAME	VARCHAR2(10)	雇员姓名
2	JOB	VARCHAR2(9)	工作
3	SAL	NUMBER	基本工资
4	COMM	NUMBER	佣金

在给出的四张数据表之中，只有工资表是没有数据的。

小结：

Oracle数据库中的四个主要用户：

超级管理员：sys/change_on_install;

普通管理员：system/manager;

普通用户：scott/tiger（Oracle 12c之后改为c##scott）

海量数据用户：sh/sh;

Oracle安装之中最重要的两个服务是监听和数据库实例服务。