Oracle数据库之SQLPLUS详解

2022-08-18 16:44:43 浏览数 (1)

三、SQLPLUS

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

3.1、SQLPlus设置

​ 如果要想了解 sqlplus 命令，首先要解决一个新的问题：数据问题。

​ 在 Oracle 12C 中由于存在了 CDB 和 PDB 概念，所以所谓的测试数据，默认是找不到的，所以下面必须首先针对数据做一个恢复。

​ 在 D:appOracleuserproduct12.1.0dbhome_1RDBMSADMIN 目录之中提供有一个 scott.sql 的创建脚本，但是这个脚本需要修改，而且要想会修改肯定要学习完后面的高级内容，所以为了方便学习，这里提供了一个 c##scott.sql 的文件，虽然名称是 c##scott ，本质还是 scott 。

c##scott.sql 脚本文件代码：

代码语言：javascript复制
-- 使用超级管理员登录
CONN sys/change_on_install AS SYSDBA ;

-- 创建c##scott用户
CREATE USER c##scott IDENTIFIED BY tiger ;

-- 为用户授权
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO c##scott CONTAINER=ALL ;

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

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

-- 删除数据表
DROP TABLE emp  PURGE ;
DROP TABLE dept PURGE ;
DROP TABLE bonus PURGE ;
DROP TABLE salgrade PURGE ;

-- 创建数据表
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;

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

1、首先打开 sqlplus.exe :

​ 运行 -> 输入：sqlplus /nolog

2、执行 c##scott.sql 文件（一段一段执行）

​ 打开窗口 - 属性 - 选项页面勾选快速编辑模式 - 颜色页面可以更改背景及文字颜色。

​ 然后将c##scott.sql 文件中的代码一段一段复制到 sqlplus 里执行即可。

​ 以上程序执行完毕后，就可以进行后面的命令讲解了。

3.2、SQLPlus 常用命令
  • 设置每行显示的记录长度：SET LINESIZE 300;
  • 设置每页显示的记录长度：SET PAGESIZE 30;
  • 用户连接数据库：CONN 用户名/密码 [AS SYSDBA];
  • 取得当前用户的全部数据对象：SELECT * FROM tab;
  • 查看表结构：DESC 表名称;
  • 使用本机的操作系统命令：HOST 命令...; 以上给出的是几个最为基本的常用操作命令，下面进行详细介绍。
3.2.1 格式化命令

​ 现在在 c##scott 用户之中已经存在了相应的数据，开始执行查询命令：

代码语言：javascript复制
SELECT * FROM emp;

​ 此表示查询 emp 表中的全部数据信息。

​ 可以发现此时的数据显示出现了折行的问题，数据的分页也出现问题，如果想要让数据变得好看一点，首先要解决屏幕的宽度问题：属性--窗口设置 。或者：

​ 设置每行的数据显示长度：

代码语言：javascript复制
SET LINESIZE 300;

​ 设置每页的显示数据长度：

代码语言：javascript复制
SET PAGESIZE 30;
3.2.2 编辑操作

​ 在 sqlplus 之中为了方便用户编写很长的 SQL 语句，也专门提供了调用本机记事本的命令，可以在终端直接利用 ed 命令完成操作。

代码语言：javascript复制
ed mldn

​ 此时会直接询问用户是否需要创建一个新的 mldn.sql 文件，打开记事本后，在文本之中直接编写之前的数据的查询命令，保存退出，随后可以使用 “ @ ”标记，执行程序，输入 “ @mldn ”

3.2.3 连接操作

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

代码语言：javascript复制
CONN 用户名/密码 [AS SYSDBA];

​ 如果使用的是 sys 用户登录，那么请加上 AS SYSDBA 的选项

范例：使用 sys 用户登录

代码语言：javascript复制
CONN sys change_on_install AS SYSDBA;

​ 连接之后，如果要想知道当前是哪个用户，可以输入 “SHOW USER;”

代码语言：javascript复制
SHOW USER;

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

代码语言：javascript复制
SELECT * FROM c##scott.emp;

​ 在数据库原理之中，用户名有时可以被简单的称为模式名称，所以所有的表都是具备模式名称的，即：模式名.表名称

​ 如果现在要使用一个普通用户登录，输入命令时可以不编写 SYSDBA.

代码语言：javascript复制
CONN c##scott tiger;
SELECT * FROM emp;

​ 而且在一个用户下一定会存在多张数据表，可以用下面命令查看：

代码语言：javascript复制
SELECT * FROM tab;

​ 会发现表的格式不规范，可以使用下面命令局部格式化

代码语言：javascript复制
COL tname FOR A20;
COL tabtype FOR A20;
COL clusterid FOR A20;
SELECT * FROM tab;

​ 而同样道理，如果说现在要想知道某些数据表的表结构，那么可以使用DESC 命令。这是在开发中使用最多的。

范例：查看 emp 表结构

代码语言：javascript复制
DESC emp;
  • 在列的类型中主要有以下几种类型：
    • NUMBER(4): 表示是数字，长度为4
    • VARCHAR2(10): 表示的是字符串，只能容纳10个长度
    • DATE: 表示日期
      • NUMBER(7,2): 表示的是数字，其中小数位占2位，整数位占5位，总共是7位
  • 在 sqlplus 中可以输入一个 “ / ” 表示重复执行上一条语句的操作。

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

代码语言：javascript复制
HOST dir;   
HOST copy d:mldn.jpg d:hello.jpg;
3.3、关于原始数据问题（了解）

​ 一直强调：现在的 Oracle 12c 中，虽然选择了要进行样本数据的创建，不过遗憾的是，发现根本就没有出现 scott 和 sh 用户。

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

  • 第一步：需要使用 sys 登录
代码语言：javascript复制
CONN sys change_on_install AS SYSDBA;
  • 第二步：查看现在的容器名称
代码语言：javascript复制
SHOW con_name;

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

  • 第三步：改变容器为 PDB
代码语言：javascript复制
ALTER SESSION SET CONTAINER=pdbmldn;
SHOW con_name;
  • 第四步：如果未打开数据库，执行命令先打开
代码语言：javascript复制
ALTER DATABASE pdbmldn OPEN;

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

代码语言：javascript复制
ALTER PLUGGABLE DATABASE pdbmldn OPEN;

​ 再次查询，就会发现 scott 和 sh 两个用户真实存在了。

  • 第五步：查看用户
代码语言：javascript复制
SELECT username FROM dba_users WHERE username='scott' OR username='sh';
  • 第六步：切换回 CBD 如果要想切换回 CDB ，只需要重新登录，或者直接输入切换命令。
代码语言：javascript复制
ALTER SESSION SET CONTAINER=cdb$root;
3.4、SQL Developer 配置

​ 在 Oracle 的学习之中，SQL Developer 应该算是现在的重点。而这个工具是在 Oracle 11 之后开始为用户提供的。

​ 1、首先打开 SQL Developer

​ 开始 -- 所有程序 -- Oracle -- 应用开发工具 -- SQL Developer

​ 这个工具依赖 Java 环境，所以需要进行 Java 相关命令的配置。

​ 2、弹出的窗口中点击 浏览，找到以下路径：

代码语言：javascript复制
D;apporacleuserproduce12.1.0dbhome_1jdkbin

​ 3、输入以上路径后，点击 OK ，稍等

​ 4、弹出 “ 配置文件类型关联 ” 窗口，询问用户是否��要做文件关联，建议别做（也就是都不勾选），点击 确定 后，出现 SQL Developer 界面。

​ 5、如果要想使用此工具，那么必须建立新的连接，而且为了后续学习方便，建议此处建立两个连接：

​ 普通用户（ c##scott/tiger ）

​ 管理员（ sys/change_on_install ）

​ 6、点击连接 - 新建连接，弹窗中输入以下内容：

​ 连接名：SCOTT_Connection

​ 用户名：c##scott

​ 口令：tiger

​ SID：mldn

​ 口令下面勾选 “保存口令” ，点击 测试 -- 保存 -- 连接 ，然后工具页面 “连接” 下会出现 “ SCOTT_Connection ”，点击后在右边输入框中输入：

代码语言：javascript复制
SELECT * FROM emp;

​ 然后点击执行按钮（绿色三角形），下面就会以表格形式显示结果。

​ 7、新建另一个，输入以下内容：

​ 连接名：DBA_Connection

​ 用户名：sys

​ 口令：change_on_install

​ SID：mldn

​ 口令下面勾选 “保存口令” ，连接类型选择 “基本”，角色选择 “SYSDBA”，点击 测试 -- 保存 -- 连接 ，然后工具页面 “连接” 下会出现 “ DBA_Connection ”，此时，管理员连接也创建成功了。

​

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

3.5、c##scott 用户表（背）

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

​ 雇员表（EMP）

NO.

字段

类型

描述

1

EMPNO

NUMBER(4) <pk>

表示雇员编号，是唯一编号

2

ENAME

VARCHAR2(10)

表示雇员姓名

3

JOB

VARCHAR2(9)

表示工作单位

4

MGR

NUMBER(4)

表示一个雇员的领导编号

5

HIREDATE

DATE

表示雇佣日期

6

SAL

NUMBER(7,2)

表示月薪、工资

7

COMM

NUMBER(7,2)

表示奖金，或者称为佣金

8

DEPTNO

NUMBER(2) <fk>

部门编号

代码语言：javascript复制
            EMP 表：
EMPNO        NUMBER(4)         <pk>
ENAME        VARCHAR2(10)
JOB              VARCHAR2(9)
MGR             NUMBER(4)
HIREDATE    DATE
SAL               NUMBER(7,2)
COMM          NUMBER(7,2)
DEPTNO       NUMBER(2)         <fk>

​ 部门表（DEPT）

NO.

字段

类型

描述

1

DEPTNO

NUMBER(2) <fk>

部门编号，是唯一编号

2

DNAME

VARCHAR2(14)

部门名称

3

LOC

VARCHAR2(13)

部门位置

代码语言：javascript复制
            DEPT 表：
DEPTNO    NUMBER(2)            <fk>
DNAME     VARCHAR2(14)
LOC           VARCHAR2(13)

​ 奖金表（BONUS）

NO.

字段

类型

描述

1

ENAME

VARCHAR2(10)

2

JOB

VARCHAR2(9)

3

SAL

NUMBER

4

COMM

NUMBER

代码语言：javascript复制
            BONUS 表：
ENAME     VARCHAR2(10)
JOB           VARCHAR2(9)
SAL           NUMBER
COMM      NUMBER

​ 工资等级表（SALGRADE）

NO.

字段

类型

描述

1

GRADE

NUMBER

等级名称

2

LOSAL

NUMBER

此等级的最低工资

3

HISAL

NUMBER

此等级的最高工资

代码语言：javascript复制
            SALGRADE 表：
GRADE   NUMBER
LOSAL    NUMBER
HISAL     NUMBER

说明：本学习资料是根据李兴华的Oracle开发实战经典整理

c# oracle sql 数据库

0 人点赞