三、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 脚本文件代码:
-- 使用超级管理员登录
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 NUMBER2) CONSTRAINT PK_DEPT PRIMARY KEY,
dname VARCHAR214) ,
loc VARCHAR213) ) ;
CREATE TABLE emp
empno NUMBER4) CONSTRAINT PK_EMP PRIMARY KEY,
ename VARCHAR210),
job VARCHAR29),
mgr NUMBER4),
hiredate DATE,
sal NUMBER7,2),
comm NUMBER7,2),
deptno NUMBER2) CONSTRAINT FK_DEPTNO REFERENCES DEPT );
CREATE TABLE bonus
enamE VARCHAR210) ,
job VARCHAR29) ,
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 用户之中已经存在了相应的数据,开始执行查询命令:
SELECT * FROM emp;
此表示查询 emp 表中的全部数据信息。
可以发现此时的数据显示出现了折行的问题,数据的分页也出现问题,如果想要让数据变得好看一点,首先要解决屏幕的宽度问题:属性–窗口设置 。或者:
设置每行的数据显示长度:
SET LINESIZE 300;
设置每页的显示数据长度:
SET PAGESIZE 30;
3.2.2 编辑操作
在 sqlplus 之中为了方便用户编写很长的 SQL 语句,也专门提供了调用本机记事本的命令,可以在终端直接利用 ed 命令完成操作。
ed mldn
此时会直接询问用户是否需要创建一个新的 mldn.sql 文件,打开记事本后,在文本之中直接编写之前的数据的查询命令,保存退出,随后可以使用 “ @ ”标记,执行程序,输入 “ @mldn ”
3.2.3 连接操作
在 Oracle 之中有许多的用户,这些用户彼此之间是可以进行互相切换的,而进行切换的基本语法如下:
CONN 用户名/密码 [AS SYSDBA];
如果使用的是 sys 用户登录,那么请加上 AS SYSDBA 的选项
范例:使用 sys 用户登录
CONN sys change_on_install AS SYSDBA;
连接之后,如果要想知道当前是哪个用户,可以输入 “SHOW USER;”
SHOW USER;
而后如果说通过 sys 继续查询 c##scott 用户中的 emp 表,那么肯定无法查询,这时必须在表前面加上用户名,即:c##scott.emp;
SELECT * FROM c##scott.emp;
在数据库原理之中,用户名有时可以被简单的称为模式名称,所以所有的表都是具备模式名称的,即:模式名.表名称
如果现在要使用一个普通用户登录,输入命令时可以不编写 SYSDBA.
CONN c##scott tiger;
SELECT * FROM emp;
而且在一个用户下一定会存在多张数据表,可以用下面命令查看:
SELECT * FROM tab;
会发现表的格式不规范,可以使用下面命令局部格式化
COL tname FOR A20;
COL tabtype FOR A20;
COL clusterid FOR A20;
SELECT * FROM tab;
而同样道理,如果说现在要想知道某些数据表的表结构,那么可以使用DESC 命令。这是在开发中使用最多的。
范例:查看 emp 表结构
DESC emp;
在列的类型中主要有以下几种类型:
NUMBER4): 表示是数字,长度为4
VARCHAR210): 表示的是字符串,只能容纳10个长度
DATE: 表示日期
NUMBER7,2): 表示的是数字,其中小数位占2位,整数位占5位,总共是7位
在 sqlplus 中可以输入一个 “ / ” 表示重复执行上一条语句的操作。
在 sqlplus 之中除了执行自己的命令之外,也可以调用本机操作系统的命令,这时只需要在相关命令前加上 HOST 指令即可。
HOST dir;
HOST copy d:mldn.jpg d:hello.jpg;
3.3、关于原始数据问题(了解)
一直强调:现在的 Oracle 12c 中,虽然选择了要进行样本数据的创建,不过遗憾的是,发现根本就没有出现 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;
SHOW con_name;
第四步:如果未打开数据库,执行命令先打开
ALTER DATABASE pdbmldn OPEN;
如果现在不是在 PDB 容器之中,那么还需要在命令上增加一个 PLUGGABLE 。
ALTER PLUGGABLE DATABASE pdbmldn OPEN;
再次查询,就会发现 scott 和 sh 两个用户真实存在了。
第五步:查看用户
SELECT username FROM dba_users WHERE username='scott' OR username='sh';
第六步:切换回 CBD
如果要想切换回 CDB ,只需要重新登录,或者直接输入切换命令。
ALTER SESSION SET CONTAINER=cdb$root;
3.4、SQL Developer 配置
在 Oracle 的学习之中,SQL Developer 应该算是现在的重点。而这个工具是在 Oracle 11 之后开始为用户提供的。
1、首先打开 SQL Developer
开始 — 所有程序 — Oracle — 应用开发工具 — SQL Developer
这个工具依赖 Java 环境,所以需要进行 Java 相关命令的配置。
2、弹出的窗口中点击 浏览,找到以下路径:
D;apporacleuserproduce12.1.0dbhome_1jdkin
3、输入以上路径后,点击 OK ,稍等
4、弹出 “ 配置文件类型关联 ” 窗口,询问用户是否需要做文件关联,建议别做(也就是都不勾选),点击 确定 后,出现 SQL Developer 界面。
5、如果要想使用此工具,那么必须建立新的连接,而且为了后续学习方便,建议此处建立两个连接:
普通用户( c##scott/tiger )
管理员( sys/change_on_install )
6、点击连接 – 新建连接,弹窗中输入以下内容:
连接名:SCOTT_Connection
用户名:c##scott
口令:tiger
SID:mldn
口令下面勾选 “保存口令” ,点击 测试 — 保存 — 连接 ,然后工具页面 “连接” 下会出现 “ SCOTT_Connection ”,点击后在右边输入框中输入:
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 | NUMBER4) <pk> | 表示雇员编号,是唯一编号 |
2 | ENAME | VARCHAR210) | 表示雇员姓名 |
3 | JOB | VARCHAR29) | 表示工作单位 |
4 | MGR | NUMBER4) | 表示一个雇员的领导编号 |
5 | HIREDATE | DATE | 表示雇佣日期 |
6 | SAL | NUMBER7,2) | 表示月薪、工资 |
7 | COMM | NUMBER7,2) | 表示奖金,或者称为佣金 |
8 | DEPTNO | NUMBER2) <fk> | 部门编号 |
EMP 表:
EMPNO NUMBER4) <pk>
ENAME VARCHAR210)
JOB VARCHAR29)
MGR NUMBER4)
HIREDATE DATE
SAL NUMBER7,2)
COMM NUMBER7,2)
DEPTNO NUMBER2) <fk>
部门表(DEPT)
NO. | 字段 | 类型 | 描述 |
---|---|---|---|
1 | DEPTNO | NUMBER2) <fk> | 部门编号,是唯一编号 |
2 | DNAME | VARCHAR214) | 部门名称 |
3 | LOC | VARCHAR213) | 部门位置 |
DEPT 表:
DEPTNO NUMBER2) <fk>
DNAME VARCHAR214)
LOC VARCHAR213)
奖金表(BONUS)
NO. | 字段 | 类型 | 描述 |
---|---|---|---|
1 | ENAME | VARCHAR210) | |
2 | JOB | VARCHAR29) | |
3 | SAL | NUMBER | |
4 | COMM | NUMBER |
BONUS 表:
ENAME VARCHAR210)
JOB VARCHAR29)
SAL NUMBER
COMM NUMBER
工资等级表(SALGRADE)
NO. | 字段 | 类型 | 描述 |
---|---|---|---|
1 | GRADE | NUMBER | 等级名称 |
2 | LOSAL | NUMBER | 此等级的最低工资 |
3 | HISAL | NUMBER | 此等级的最高工资 |
SALGRADE 表:
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
说明:本学习资料是根据李兴华的Oracle开发实战经典整理