Oracle 12c中SQLPlus操作使用（包含实验二ORACLE SQL*PLUS环境与查询的详细操作解释）

54 阅读 0 评论 36 点赞

我是靠谱客的博主深情小霸王，最近开发中收集的这篇文章主要介绍Oracle 12c中SQLPlus操作使用（包含实验二ORACLE SQL*PLUS环境与查询的详细操作解释），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1. 查看sqlplus版本号

（1）cmd在dos命令中查看，输入：sqlplus -v

（2）启用sqlplus后，输入：select * from V$VERSION;

2. 进入sqlplus的方法

（1）Windows10环境在开始菜单，找到SQLPLUS单击打开

（2）Win键+R键打开运行窗口输入sqlplus

（3）Win键+R键打开运行窗口输入cmd，再启用sqlplus

3. Oracle中SYS和SYSTEM用户的区别

sys、system、sysman 这三个用户的口令是你安装的时候设置的那个口令。

sys as sysdba 就是以sysdba登录

Oracle登录身份有三种：normal 普通身份，sysdba 系统管理员身份，sysoper 系统操作员身份，每种身份对应不同的权限。

SYS用户是超级用户，有最高权限，具有SYSDBA角色，具有数据库管理员权限，可以建立数据库——有create database权限。

SYSTEM用户是管理操作员，具有SYSOPE角色，具有数据库操作员权限，不可以建立数据库——没有create database权限。

4.查看Oracle数据库全局数据库名和SID

查看全局数据库名：select name from v$database;

查看SID：select instance_name from v$instance;

5. oracle之sql中使用注释

单行注释以 --开头，例如：

--本句查看当前用户名 select user from dual;

多行注释，以/*开始其后面有一空格，以*/结束其前面有一空格，例如：

/* 多行注释，

下面语句是查看当前用户名 */

select user from dual;

6. 连接数据库

参考连接

https://docs.oracle.com/cd/E11882_01/server.112/e25494/dba.htm#ADMIN11032https://docs.oracle.com/cd/E11882_01/server.112/e25494/dba.htm#ADMIN11032

https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve015.htm#SQPUG036https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve015.htm#SQPUG036

语法

CONN[ECT] [{登录| / |代理}[AS {SYSOPER |SYSDBA |SYSASM}] [版本=值]]

其中登录具有以下语法：

用户名[/密码] [@connect_identifier]

其中代理具有以下语法：

代理用户[用户名] [/密码] [@connect_identifier]

注意：代理中用户名周围的括号是必需的语法，而不是可选术语的指示。例如，使用密码通过代理连接到用户。scotthrwelcome1连接 hr[scott]/welcome1

例子 1
这个简单的示例以用户身份连接到本地数据库。SQL*加会提示输入用户密码。SYSTEMSYSTEM
connect system
例子 2

此示例以具有特权的用户身份连接到本地数据库。SQL*加会提示输入用户密码。SYSSYSDBASYS
connect sys as sysdba
以用户身份连接时，必须连接。SYSAS SYSDBA
例子 3

此示例使用操作系统身份验证的特权进行本地连接。SYSDBA
connect / as sysdba
例子 4

此示例使用简单的连接语法以用户身份连接到在主机上运行的远程数据库。Oracle Net 侦听器（侦听器）正在侦听默认端口（1521）。数据库服务是。SQL*加会提示输入用户密码。salesadmindbhost.example.comsales.example.comsalesadmin
connect salesadmin@"dbhost.example.com/sales.example.com"
例子 5

此示例与例子4相同，只是指示了服务处理程序类型。
connect salesadmin@"dbhost.example.com/sales.example.com:dedicated"
例子 6

此示例与例子4相同，只是侦听器正在侦听非默认端口号 1522。
connect salesadmin@"dbhost.example.com:1522/sales.example.com"
例子 7

此示例与例子4相同，只是主机 IP 地址替换为主机名。
connect salesadmin@"192.0.2.5/sales.example.com"
例子 8

此示例使用 IPv6 地址进行连接。请注意括起来的方括号。
connect salesadmin@"[2001:0DB8:0:0::200C:417A]/sales.example.com"
例子 9

此示例指定要连接到的实例，并省略数据库服务名称。必须已指定缺省数据库服务，否则将生成错误。请注意，仅指定实例时，无法指定服务处理程序类型。
connect salesadmin@"dbhost.example.com//orcl"
例子 10

此示例以用户身份远程连接到由网络服务名称指定的数据库服务。SQL*加会提示输入用户密码。salesadminsales1salesadmin
connect salesadmin@sales1
例子 11

本示例使用外部身份验证远程连接到由网络服务名称指定的数据库服务。sales1
connect /@sales1
例子 12

此示例使用权限和外部身份验证远程连接到由网络服务名称指定的数据库服务。SYSDBAsales1
connect /@sales1 as sysdba
例子 13

此示例以用户身份远程连接到由网络服务名称指定的数据库服务。数据库会话在版本中启动。SQL*加会提示输入用户密码。salesadminsales1rev21salesadmin
connect salesadmin@sales1 edition=rev21
例子 14

要使用用户名 HR 跨 Oracle Net 连接到 Oracle Net 别名称为 FLEETDB 的数据库，请输入
CONNECT HR@FLEETDB
例子 15

要以名为 HR 的特权用户身份连接到当前节点上的实例，请输入
CONNECT HR AS SYSDBA
例子 16

要以特权默认用户身份连接到当前节点上的实例，请输入
CONNECT / AS SYSDBA
在最后两个示例中，默认架构变为 SYS。

7. 断开用户与Oracle数据库服务器的连接

exit 退出即可

8. 用户的操作使用

（1）创建表空间

create tablespace 表空间的名 datafile '数据文件' size xxxm;

数据文件：全名如d:data.dbf，表空间的存储位置

xxx：表空间的大小，m单位为兆(M)

【为用户指定表空间】

alter user 用户名 default tablespace 表空间名;

为用户指定临时表空间：

alter user 用户名 temporary tablespace 表空间名;

【删除空的表空间，但是不包含物理文件】

drop tablespace 表空间名;

删除空表空间，包含物理文件（数据文件）：

drop tablespace 表空间名 including datafiles;

如果其他表空间中的表有外键等约束关联到了本表空间中的表的列，就要加上CASCADE CONSTRAINTS，如：drop tablespace 表空间名 including datafiles cascade constraint

注意：如果多个用户使用相同的表空间，删除用户时不要删除表空间

（2）创建用户

create user 用户名 identified by 密码;

【创建用户同时可以指定表空间】

create user 用户名 identified by 密码 default tablespace 表空间名;

【删除用户及级联关系】

drop user 用户名 cascade;

指定 cascade 会删除用户下的所有对象（包括表、视图、主键、外键、索引等；但不会删除存储过程、函数、包）。如果不指定则仅仅只删除用户，一般建议指定

【查询用户信息】

oracle 查看当前用户名：

show user 或 select user from dual;

oracle 查看所有用户名：

select * from all_users;

查看数据库里面所有用户相关信息（前提是你是有dba权限的帐号，如sys、system）：

select * from dba_users;

注意SELECT查询语句中的星号*代表所有列

查看你能管理的所有用户：

select * from all_users;

查看当前用户信息：

select * from user_users;

查询用户所对应的表空间：

select username,default_tablespace from dba_users;

使用限制条件查询用户所对应的表空间：

select username, default_tablespace from dba_users where username='大写用户名';

（3）将空间分配给用户

alert user 用户名 default tablespace表空间名;

（4）给用户授权

Grant 权限 to 用户名;

创建的用户默认没有任何权限，需要分配权限。

不同用户登录到同一数据库中，可能看到不同数量的表，拥有不同的权限。Oracle 的权限分为系统权限和数据对象权限，共一百多种，如果单独对用户授权，很麻烦，有一些用户需要的权限是相同的，就把这些用户归为同一类——某种角色，通过设立一些有预定权限的角色简化和明确授权操作，角色出现的动机也就是为了简化权限管理，它是权限的集合。一般做法是：系统把权限赋给角色，然后把角色赋给用户，当然也可以直接把某权限赋给用户。Oracle 提供细粒度的权限，可以对表的某一列单独设置权限，可以对某用户查询某表自动增添 where 限制条件。

【权限分类】

系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。

实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。

【系统权限分类】

DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

【实体权限分类】

select、update、insert、alter、index、delete、all（all包括所有权限）、execute（执行存储过程权限）。

【角色】

角色是一组权限的集合，将角色赋给一个用户，这个用户就拥有了这个角色中的所有权限。

预定义角色是在数据库安装后，系统自动创建的一些常用的角色。下介简单的介绍一下这些预定角色。

查看所有角色：

select * from dba_roles;

查看角色所包含的权限可以用以下语句查询：

select * from role_sys_privs where role='角色名';

查看当前登录用户拥有的所有角色和系统权限：

select * from session_privs;

（5）然后用创建的用户登录，登录之后创建表

conn 用户名/口令;

9. 创建一个表空间进行SQL语句实践

由system系统管理员账号登录

（1）创建表空间SKspace：

格式: create tablespace 表间名 datafile '数据文件名' size 表空间大小表空间数据文件自动扩展

create tablespace SKspace datafile 'C:Oracle12CMBspce1.dbf' size 20M autoextend on;

（2）创建用户C##suke密码为suke123，设置默认表空间是SKspace，临时表空间是temp：

格式: create user 用户名 identified by 密码 default tablespace 表空间表;

create user C##suke identified by suke123 default tablespace SKspace temporary tablespace temp;

错误：公用用户名或角色名无效

因为此时在CDB中创建，而CDB中创建用户必须在用户名的前面添加C#或者C##

（3）创建的用户默认没有任何权限，需要分配权限。

这里为了方便，将管理员角色（dba）权限授权给C##suke用户：

grant dba to C##suke;

--表示把 connect,resource权限授予study用户

grant connect,resource to study;

--表示把 dba权限授予给 study

grant dba to study;

（4）现在可以用创建的用户（用前面创建的用户C##suke密码是suke123）登录：

connect C##suke/suke123;

（5）登录之后创建表：

CREATE TABLE job( id NUMBER(6),code VARCHAR2(10), name VARCHAR2(10));

（6）为已创建（已存在）的表插入表数据插入单条记录：

INSERT INTO job(id,code,name) VALUES(1,'NO1','测试');

（7）为已创建（已存在）的表插入表数据插多单条记录：

INSERT ALL

INTO job(id,code,name) VALUES(2,'NO2','开发')

INTO job(id,code,name) VALUES(3,'NO3','运维')

SELECT 1 FROM dual;

（8）查询表数据：

SELECT * FROM job where id=1;

SELECT code,name FROM job where code='NO2';

（9）查询用户创建的表：

SELECT table_name FROM ALL_TABLES WHERE OWNER='C##suke';

未选定行

开始以为是查询语句写错了，左改右改结果还是这样。

后来到网上一查，原来“未选定行”这个不是错误，是因为数据库中没有和查询语句相符的结果。

select count(table_name) from all_tables where owner='C##suke';

看来遇问题不能先入为主，我一开始以为这是语句错误，而老是想找出这个语句哪里错了。其实这不是错误，只是数据库中数据的问题。

（10）查询用户创建的表结构：

desc job;

10. Oracle删除表空间语句

（1）删除空的表空间，但是不包含物理文件
        drop tablespace tablespace_name;

（2）删除非空表空间，但是不包含物理文件
        drop tablespace tablespace_name including contents;
（3）删除空表空间，包含物理文件
        drop tablespace tablespace_name including datafiles;
（4）删除非空表空间，包含物理文件
        drop tablespace tablespace_name including contents and datafiles;
（5）如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上         CASCADE CONSTRAINTS
        drop tablespace tablespace_name including contents and datafiles CASCADE         CONSTRAINTS;

11. 实验二ORACLE SQL*PLUS环境与查询

注1：如果你想使用如此图的EMP表可查询实验二 Oracle SQL*PLUS环境与查询

注2：你找不到此图的EMP：Oracle设置和修改system和scott的口令，并且如何连接到system和scott模式下

使用scott内置账户登录：conn scott/口令可以查找到此图的EMP表

注3：你发现你内置账户scott不存在 / 连接不上

此链接可以解决你的问题：Oracle数据库中scott用户不存在的解决方法

注4：你发现注3 还是无法连接scott账户，你也不想麻烦操作了

此链接可以粗暴解决问题：重建用户scott以及内容表

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

（1）SQL*Plus的启动

继续上述实例 9 的步骤操作

进入sqlplus环境：sqlplus

输入用户名：C##suke

输入口令：suke123

（2）断开数据库连接与重新连接数据库

断开数据库连接：DISCONECT

重新连接数据库：conn 用户名/口令：conn C##suke/suke123

（3）退出SQL*Plus：exit

（4）查看当前用户名

show user;

select user from dual;

（5）查询表job表结构：desc job;

（6）查询表job表中的数据

select * from job;

select id,code,name from job where id < 2;

（7）列出缓冲区的内容：LIST

（8）change 修正错误

查询命令时候误将code打错成cose：select id,cose,name from job;

补充：第几行出现错误，出入错误行的数字，就可以单独显示出错误行代码。

使用change修正错误代码：change /cose/code;

重新运行：run

（10）input 添加一行查询条件

查询信息：SQL> select id,code,name from job;

突然想排序操作，又不想重新输入全部命令，使用input添加以id列的排序条件：

SQL> input
2 order by id;

（11）append 给上一行结尾添加查询条件

继步骤（10）看完正序后，由于强迫症想看倒叙怎么办？

首先列出缓冲区的内容：list

给上一行结尾添加查询条件：append desc

注意：append desc 中间是两个空格，如果你只留一个空格，如下图错误显示

重新运行：run

（12）del 删除最后一行查询条件

继步骤（11）看完倒叙后，由于脑子有病，就想看原始数据怎么办？

首先列出缓冲区的内容：list

删除上一行查询条件：del

重新运行：run

（13）显示job表中所有的信息：select * from job;

（14）算术运算符的使用

使id+2*3显示：select id+2*3,code,name from job;

（15）连字符的使用

将单位编号和单位名字连接起来：select code || name codename from job;

将单位编号和单位名字中间用 ‘-’ 连接起来，并输出 ‘WORKS IN DEPARTMENT’：

select id,code||'-'||name codename,'work in department' from job;

（16）DISTINCT子句消除重复
由于我们这里的数据本身没有重复值，所以我们需要再添加一些重复数据：

INSERT ALL

INTO job(id,code,name) VALUES(1,'NO2','前端')

INTO job(id,code,name) VALUES(2,'NO2','后端')

INTO job(id,code,name) VALUES(3,'NO3','物联网')

SELECT 1 FROM dual;

如果列举出job表中所有单位id序号：select id from job;

从上表中可以看出单位id序号之间存在重复，可以用DISTINCT子句来消除重复的：

select distinct id from job;

（17）排序（默认正序）

按单个字段 id 排序：select * from job order by id;

按多个字段排序：如按单位序号id升序，按单位名字name降序排序：

方法一：回顾知识点 list 和 append 用法

方法二：select * from job order by id,name desc;

（19）带条件的查询
查询单位序号 id 是 2 的所有单位序号、单位编号、单位名字信息：

SQL> select * from job where id = 2;

从 job 表中查询单位序号大于1的单位名字：

SQL> select id,name from job where id > 1;

复合条件查询：查询单位序号大于 1 并且单位编号是'NO3'，或者单位名字是'前端'的所有单位信息：SQL> select * from job where id > 1 and code = 'NO3' or name = '前端';

（20）操作符的应用

BETWEEN 的应用：查询单位序号 id 在 2 到 3 之间的所有单位信息：

SQL> select * from job where id between 2 and 3;

IN 的应用：查询单位编号code有 'NO2'，'NO3' 二个之一的所有单位信息：

SQL> select * from job where code in ('NO2','NO3');

LIKE 的应用：

查询名字以 '运' 字开始的所有单位信息：SQL> select * from job where name like '运%';

查询名字只有三个字符的所有单位信息：SQL> select * from job where name like '___';

IS NULL的应用：查询没有单位名字 name 的所有单位信息：

先添加没有单位名字的信息：SQL> INSERT INTO job(id,code) VALUES(1,'NO1');

查询没有单位名字 name 的所有单位信息：SQL> select * from job where name is null;

（21）单&号替代变量

数字变量输入：

SQL> select * from job where &job_id;
输入 job_id 的值: id = 2

字符串变量输入：

SQL> select id*10,code,name from job where code = '&job_code';
输入 job_code 的值: NO3

（22）数据类型转换

TO_NUMBER 字符串数据转换为数字：SQL> select to_number('33') from dual;

TO_CHAR 数字数据转换为字符串：SQL> select to_char(2) from job;

TO_CHAR 字符串数据转换为日期数据：

SQL> select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual;



TO_DATE 字符串数据转换为日期数据：

SQL> select to_date('2005-12-25,13:25:59','yyyy-mm-dd,hh24:mi:ss') from dual;

（23）分组函数的应用

求平均值：SQL> select avg(id) from job;

求最小值：SQL> select min(id) from job where code = 'NO3';



求数目：SQL> select count(*) from job where id = 2;

注意：由于我这job单位表不适合下列查询，所以拿别人的图来展示

GROUP BY子句：求每个部门中的平均工资：

HAVING子句：查询人数超过3人的部门中的平均工资：

（24）连接

再次注意：我这job单位表同样不适合下列查询，再次拿别人的图来展示

从EMP和DEPT中查询出职工名字、工作和部门名称：

（25）子查询的应用

再次注意：我这job单位表同样不适合下列查询，再次拿别人的图来展示

从EMP中查询出工资最低的职工：

从EMP中查询出每个部门工资最低的职工：