提示:此文章下的code示例皆在cmd下运行,未借助可视化工具
文章目录
一、Note
1、SQL*PLUS
-
一个由Oracle数据库管理系统提供的 操作数据库的 命令行工具
-
在SQL*PLUS环境下由很多特殊的命令,这些命令不属于标准SQL
-
在 命令提示符 或 终端 中执行 sqlplus 命令即可进入到SQL*PLUS环境
-
方式一:
>sqlplus
,直接回车,之后提示输入用户名和密码,可直接在用户名里输:用户名/密码 [as 角色]- 本地用户可以免密码,直接回车进入
- 注意: 以sys用户登陆的话 必须要加上 as sysdba 子句
Enter user-name:sys
Enter password:password as sysdba
-
方式二:
>sqlplus /nolog
,回车- /nolog是不登录到数据库服务器,即进入了sql*plus环境但不会提示输入用户名和密码
connect 用户名/密码 [as 角色]
,之后用此语句登录数据库服务器
-
方式三:
>sqlplus 用户名/密码 [as 角色]
-
方式四:
sqlplus / as sysdba
(以操作系统权限认证的oracle sys管理员登陆)- 在本机以超级管理员sys登陆,采用的是操作系统验证的方式,所以用户名/密码输与不输入是一样的
sqlplus sys/password@orcl as sysdba
(管理员用户使用tns别名登陆)
-
方式五:
sqlplus scott/tiger
(非管理员用户登陆) -
方式六:
sqlplus scott/tiger@orcl
(非管理员用户使用tns别名登陆)
-
-
输入命令
exit
可退出SQL*Plus环境 -
输入命令
edit
或ed
可打开afiedt.buf文件,里面存放最后一次执行过的命令和/符号,之后输入/
可执行此命令 -
CLEAR SCREEN
; 可用于清除屏幕
2、关于用户
-
用户名不区分大小写,SQL关键字推荐大写
-
用来通过 数据库管理系统 管理数据库中的数据
-
每个用户都单独拥有自身所创建的数据库对象
- 一个表、视图、索引、约束、序列都可以当作一个数据库的对象(不与另外一个数据库共享)
- 用户之间都是相互独立的,有自己的数据库对象
-
内置用户
- SYS、SYSTEM、DBSNMP、SYSMAN
-
创建用户
-
必须保证拥有创建用户的权限
- 比如,使用 SYS 用户以 SYSDBA 角色登录
- 在SYS用以SYSDBA角色登录后即可创建新用户
-
-
创建用户
CREATE USER 用户名 IDENTIFIED BY 密码;
create user prl identified by 123;
- 更改用户密码
ALTER USER 用户名 IDENTIFIED BY 新密码;
-
删除用户
-
必须保证拥有删除用户的权限
-
删除语句
DROP USER 用户名 [CASCADE];
- CASCADE级联删除:把用户拥有的所有东西一起删除
drop user prl cascade;
-
-
显示当前连接用户
show user
select user form dual ;
-
显示所有用户
select * from all_users;
-
让当前用户断开链接
disconnect
-
查看所有用户实例
select instance_name from v$instance;
select name from v$database;
3、查看数据库
-
查看当前所有的数据库
select name from v$database;
-
显示当前数据库
show database
4、用户权限
- 表示用户可以完成什么操作
- 比如
create table
、create view
、create index
、create session
等权限
5、角色
-
多个权限集合在一起就形成一个角色
-
一个角色包含多个权限,一个用户可充当多个角色
-
比如
connect
角色、resourse
角色- connect角色拥有创建会话的权限,即create session权限,用户需要有这个权限才能登录
6、用户授权
-
让某个用户拥有 某个权限 或某个角色的权限
-
授权语句
GRANT 权限名称或角色名称 TO 用户名;
grant connect,resource to prl;
7、用户收权
-
为某个用户剔除权限
-
回收权限语句
REVOKE 权限名称或角色名称 FROM 用户名;
revoke connect,resource from prl;
8、导入脚本
-
为了能够让某个用户可以直接拥有一些数据库对象,以便于我们学习,我们准备采用 执行SQL脚本的方式导入部分数据(为用户创造数据库对象)
-
在 Oracle 的 SQL*Plus 环境下,可以使用 start 或 @ 来执行指定的 脚本文件(SQL脚本文件)
-
start xxx.sql
- (.sql脚本文件在命令行当前所在目录下)
-
@ .sql文件的完整路径/xxx.sql
- (.sql脚本文件的绝对路径)
-
-
切记,在执行 SQL脚本文件时,一定要在自己的用户中完成,不要在SYS用户中完成!!否则数据库对象就导入到SYS用户里了
9、sysdate和current_date对比
current_date返回的是当前会话时间,而sysdate返回的是服务器时间,是运行器使用的函数;
current_date有时比sysdate快一秒,这可能是四舍五入的结果;
如果修改当前会话的时区,比如将中国的时区为东八区,修改为东九区,则current_date显示的时间为东九区时间, 根据东加西减的原则,current_date应该比sysdate快一小时。
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select current_date,sysdate from dual;
-- 结果
CURRENT_DATE SYSDATE
------------------- -------------------
2009-03-10 20:01:38 2009-03-10 20:01:37
-- 修改当前时区为东九区
alter session set time_zone='+09:00' ;
select current_date,sysdate from dual ;
-- 结果
CURRENT_DATE SYSDATE
------------------- -------------------
2009-03-10 21:04:22 2009-03-10 20:04:22
10、默认规律
-
Oracle自带表中的字符数据默认大写;(当我们用小写自定义约束名时,存入oracle的数据字典后会变大写)
-
Oracle数据库对大小写不敏感,但字符型数据出现在比较表达式中时,要严格区分大小写,字符型数据要用单引号括起来,若用双引号,则表达式无效。
扩展:
UPPER(string) 大写转换函数
LOWER(string) 小写转换函数
SQL> -- 字符型数据用"",出错
SQL> select table_name,column_name,constraint_name from user_cons_columns where table_name="P1";
第 1 行出现错误:
ORA-00904: "P1": 标识符无效
SQL>
SQL>
SQL> -- Oracle自带表中的字符数据默认大写,字符型数据出现在比较表达式中时,要严格区分大小写,而我们查询的p1为小写,与user_cons_columns表中的字符型数据不匹配
SQL> select table_name,column_name,constraint_name from user_cons_columns where table_name='p1';
未选定行
SQL>
SQL> -- 也可以用 where lower(table_name)='p1' ;
SQL> -- 也可以用 where table_name=upper('p1') ;
SQL> select table_name,column_name,constraint_name from user_cons_columns where table_name='P1';
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
------------------------------ -------------------------------------------------- ------------------------------
P1 SEX P1_SEX_UK
二、Code
1、sqlplus环境创建用户并授权及登录
sqlplus /nolog
进入sqlplus环境但不登录数据库服务器show user;
查询当前用户名称connect sys/ as sysdba
本地用户可免密登录,直接回车不用输密码CREATE USER yelaoshi IDENTIFIED BY yeshu ;
在SYS用以SYSDBA角色登录后即可创建新用户GRANT connect , resource TO yelaoshi ;
给新建用户授权connect yelaoshi/yeshu
新建用户可登录
Microsoft Windows [版本 10.0.17133.1]
(c) 2018 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 17 16:55:33 2020
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> show user;
USER 为 ""
SQL> -- connect 用户名/密码 as 角色
SQL> connect sys/ as sysdba
输入口令:
已连接。
SQL> show user ;
USER 为 "SYS"
SQL> select sysdate from dual ;
SYSDATE
--------------
17-6月 -20
SQL> CREATE USER yelaoshi IDENTIFIED BY yeshu ;
用户已创建。
SQL> GRANT connect , resource TO yelaoshi ;
授权成功。
SQL> -- 用 yelaoshi 连接数据库
SQL> connect yelaoshi/yeshu
已连接。
SQL> show user ;
USER 为 "YELAOSHI"
SQL> -- 查询当前用户拥有那些表
SQL> SELECT tname FROM tab ;
未选定行
SQL> -- 或者使用 user_tables 来查询
SQL> SELECT table_name FROM user_tables ;
未选定行
SQL>
2、查询当前用户相关信息
SELECT sysdate FROM dual ;
查询当前日期(借助虚表dual)- 可以使用
/
直接执行最后一条SQL SELECT tname FROM tab ;
查询当前用户所拥有的表 ( 通过 tab 表来查询 )SELECT table_name FROM user_tables ;
查询当前用户所拥有的表 ( 通过 user_tables 表来查询 )disconnect
让当前用户断开链接- 用spool命令记录操作:先输入
spool 绝对路径/prl.txt
,回车后开始记录,spool off
结束记录- 若要保存操作的.txt文件在 命令行所在目录下,可直接
spool prl.txt
- 若要保存操作的.txt文件在 命令行所在目录下,可直接
SQL> -- 查询当前用户名称
SQL> show user ;
USER 为 "YELAOSHI"
SQL>
SQL> -- 查询当前日期
SQL> SELECT sysdate FROM dual ;
SYSDATE
--------------
17-6月 -20
SQL> -- 可能会遇到以下情况,这就是换行继续输入
SQL> SELECT sysdate
2 FROM dual
3 ;
SYSDATE
--------------
17-6月 -20
SQL> -- 可以使用 / 直接执行最后一条SQL
SQL> /
SYSDATE
--------------
17-6月 -20
SQL>
SQL> -- 查询当前用户所拥有的表 ( 通过 tab 来查询 )
SQL> SELECT tname FROM tab ;
未选定行
SQL> -- 查询当前用户所拥有的表 ( 通过 user_tables 来查询 )
SQL> SELECT table_name FROM user_tables ;
未选定行
SQL> -- 查询当前用户
SQL> show user
USER 为 "YELAOSHI"
SQL>
SQL>
SQL> -- 让当前用户断开链接
SQL> disconnect
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
SQL>
SQL>
SQL> -- 查询当前用户
SQL> show user
USER 为 ""
SQL>
SQL>
SQL> -- 再次使用 yelaoshi/yeshu 登录
SQL> connect yelaoshi/yeshu
已连接。
SQL> -- 再次查看当前用户
SQL> show user
USER 为 "YELAOSHI"
SQL> spool off
3、 执行SQL脚本(为自己的用户导入数据)
start first_zh_cn.sql
- 当前命令行在D:/SQL目录
- 使用 SQL*Plus 环境下的 start 命令执行在当前目录下 ( 即 D:/SQL 目录 ) 存在的 first_zh_cn.sql
@ D:/SQL/second_zh_cn.sql
- 使用 @ 命令执行 指定目录下的指定SQL脚本 second_zh_cn.sql
select count(table_name) from user_tables ;
统计表的个数select count(object_name) from user_objects ;
统计数据库对象的个数
C:\Users\Administrator>d:
D:\>cd SQL
D:\SQL>dir
驱动器 D 中的卷没有标签。
卷的序列号是 228B-E496
D:\SQL 的目录
2020/06/17 17:19 <DIR> .
2020/06/17 17:19 <DIR> ..
2020/06/17 17:14 1,704 SQL02.txt
1 个文件 1,704 字节
2 个目录 27,496,308,736 可用字节
D:\SQL>sqlplus sys/ as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 6月 18 10:38:13 2020
Copyright (c) 1982, 2010, Oracle. All rights reserved.
输入口令:
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user ;
USER 为 "SYS"
SQL> CREATE USER ecuter IDENTIFIED BY ecuter ;
用户已创建。
SQL> GRANT connect , resource TO ecuter ;
授权成功。
SQL> disconnect
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
SQL>
SQL>
SQL>
SQL>
SQL> -- 通过查询当前用户来确保当前用户不是 SYS 之类的内置用户
SQL> show user ;
USER 为 ""
SQL>
SQL> -- 使用 ecuter 登录 ( 使用 ecuter 连接到 Oracle )
SQL> connect ecuter/ecuter
已连接。
SQL>
SQL> show user ;
USER 为 "ECUTER"
SQL>
SQL> -- 查询当前用户所拥有的所有的数据库对象
SQL> select object_name from user_objects ;
未选定行
SQL>
SQL>
SQL> -- 查询当前用户所拥有的所有的表
SQL> select table_name from user_tables ;
未选定行
SQL> select tname from tab ;
未选定行
SQL>
SQL> -- 为用户ECUTER导入sql文件,创建数据库对象
SQL> -- 使用 SQL*Plus 环境下的 start 命令执行在当前目录下 ( 即 D:/SQL 目录 ) 存在的 first_zh_cn.sql
SQL>
SQL> start first_zh_cn.sql
Building demonstration tables. Please wait.
Demonstration table build is complete.
SQL>
SQL>
SQL> -- 查询当前用户所拥有的所有的表
SQL> select table_name from user_tables ;
TABLE_NAME
------------------------------
DUMMY
SALGRADE
BONUS
DEPT
EMP
SQL> -- 查询当前用户所拥有的所有的数据库对象
SQL> select object_name from user_objects ;
OBJECT_NAME
--------------------------------------------------------------------------------
EMP
DEPT
BONUS
SALGRADE
DUMMY
SQL>
SQL> -- 使用 @ 命令执行 指定目录下的指定SQL脚本
SQL> @ D:/SQL/second_zh_cn.sql
Creating and populating tables and sequences. Please wait.
Tables and sequences created and populated.
SQL>
SQL> -- 查询当前用户所拥有的所有的表
SQL> select table_name from user_tables ;
TABLE_NAME
------------------------------
S_WAREHOUSE
S_TITLE
S_REGION
S_PRODUCT
S_ORD
S_LONGTEXT
S_ITEM
S_INVENTORY
S_IMAGE
S_EMP
S_DEPT
TABLE_NAME
------------------------------
S_CUSTOMER
DUMMY
SALGRADE
BONUS
DEPT
EMP
已选择17行。
SQL> -- 查询当前用户所拥有的所有的数据库对象
SQL> select object_name from user_objects ;
OBJECT_NAME
--------------------------------------------------------------------------------
EMP
DEPT
BONUS
SALGRADE
DUMMY
S_CUSTOMER_ID
S_DEPT_ID
S_EMP_ID
S_IMAGE_ID
S_LONGTEXT_ID
S_ORD_ID
OBJECT_NAME
--------------------------------------------------------------------------------
S_PRODUCT_ID
S_REGION_ID
S_WAREHOUSE_ID
S_CUSTOMER
S_CUSTOMER_ID_PK
S_DEPT
S_DEPT_ID_PK
S_DEPT_NAME_REGION_ID_UK
S_EMP
S_EMP_ID_PK
S_EMP_USERID_UK
OBJECT_NAME
--------------------------------------------------------------------------------
S_IMAGE
S_IMAGE_ID_PK
S_INVENTORY
S_INVENTORY_PRODID_WARID_PK
S_ITEM
S_ITEM_ORDID_ITEMID_PK
S_ITEM_ORDID_PRODID_UK
S_LONGTEXT
S_LONGTEXT_ID_PK
S_ORD
S_ORD_ID_PK
OBJECT_NAME
--------------------------------------------------------------------------------
S_PRODUCT
S_PRODUCT_ID_PK
S_PRODUCT_NAME_UK
S_REGION
S_REGION_ID_PK
S_REGION_NAME_UK
S_TITLE
S_TITLE_TITLE_PK
S_WAREHOUSE
S_WAREHOUSE_ID_PK
已选择43行。
SQL> -- 说明我们已经在ECUTER用户下创建了17章表,43个数据库对象
SQL>
SQL> show user ;
USER 为 "ECUTER"
SQL>
SQL> -- 统计表的个数
SQL> select count(table_name) from user_tables ;
COUNT(TABLE_NAME)
-----------------
17
已选择 1 行。
SQL>
SQL> -- 统计数据库对象的个数
SQL> select count(object_name) from user_objects ;
COUNT(OBJECT_NAME)
------------------
43
已选择 1 行。
SQL> -- 这个文件不是用 spool 录制的,而是手动复制的
SQL>
4、练习-用户创建授权收权
- sys用户的密码是在安装oracle时自己设定的密码
SQL> show user
USER 为 ""
SQL>
SQL>
SQL> -- 本地用户sys可免密登录
SQL> connect sys/ as sysdba
已连接。
SQL>
SQL> -- 断开当前用户sys
SQL> disconnect
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
SQL>
SQL> -- 本地用户sys可用密码登录
SQL> -- connect sys/Admin2020 as sysdba
SQL> connect sys/Admin2020 as sysdba
已连接。
SQL>
SQL>
SQL> -- 在有创建用户权限的内置用户,如sys用户登录后,就可以创建我们自己的用户了
SQL> create user prl identified by 123
2 ;
用户已创建。
SQL> -- 给我们创建的用户授予权限
SQL> --注!如果没有授予权限如connect,那prl用户就没有connect角色下的create session创建会话权限
SQL> --那么之后就不能 connect prl/123 来登录
SQL> grant connect,resource to prl;
授权成功。
SQL> -- 收回给用户prl授予的权限,哦对了,connect和resource是角色,我们授予了角色的权限给用户prl
SQL> revoke connect,resource from prl;
撤销成功。
SQL> -- 删除我们创建的用户prl
SQL> drop user prl;
用户已删除。
SQL> spool off