sqlplus 命令学习
成都创新互联于2013年开始，先为赤坎等服务建站，赤坎等地企业，进行企业商务咨询服务。为赤坎企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
$ sqlplus / as sysdba
SQL> help index 可以输入sqlplus 命令
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
sqlplus 一个允许你输入sql语句地方 sql语句控制数据库的存取
1 查出scott.emp表的编号，姓名
SQL> select empno , ename from scott.emp; 分号结束 回车执行
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
2 如何查看当前sql buffer 的语句
? 命令 -----> 看帮助
? list
SQL> ? list
LIST
----
Lists one or more lines of the most recently executed SQL command
or PL/SQL block which is stored in the SQL buffer. Enter LIST with
no clauses to list all lines. In SQL*Plus command-line you can also
use ";" to list all the lines in the SQL buffer. The buffer has no
command history list and does not record SQL*Plus commands.
L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]
输入 l
3 我想修改一buffer的语句，比如加一个条件ename ='SCOTT';
SQL> ? EDIT
EDIT
----
Invokes 调用 an operating system text editor文本编辑器 on the contents of the
specified file or on the contents of the SQL buffer. The buffer
has no command history list and does not record SQL*Plus commands.
ED[IT] [file_name[.ext]]
SQL> ed
Wrote file afiedt.buf
38
?
?
q 退出
一定在linux指定使用哪一个文本编辑器
windows用的记事本
怎么指定用那个编辑器-----》》》》？？？ 变量指定
方法1 ：临时指定
define _editor=vi
输入ed
方法2： 永久指定 把变量放在.bash_profile
[oracle@yutianedu ~]$ vim /home/oracle/.bash_profile
加入： export EDITOR=vi
[oracle@yutianedu ~]$ source /home/oracle/.bash_profile
[oracle@yutianedu ~]$ echo $EDITOR
vi
ed 修改
SQL> l 查看
1 select empno ,ename from scott.emp
2* where ename ='SCOTT'
4 如何执行buffer cache 中的语句
SQL> ? /
/ (slash)
---------
Executes the most recently executed SQL command or PL/SQL block
which is stored in the SQL buffer. Use slash (/) at the command
prompt or line number prompt in SQL*Plus command line. The buffer
has no command history and does not record SQL*Plus commands.
SQL> l
1 select empno ,ename from scott.emp
2* where ename ='SCOTT'
SQL> / 执行sql buffer 中的sql语句
EMPNO ENAME
---------- ----------
7788 SCOTT
5 保存代码一个脚本，下次执行
SQL> ? SAVE
SAVE
----
Saves the contents 内容 of the SQL buffer in a script. The
buffer has no command history list and does not record SQL*Plus commands.
SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
save 文件命令 create /replace /append
默认创建
替换
追加
注意：脚本一定存在有权限的目录（oracle用户有权限写）
save /u01/app/oracle/1.sql
6 保存脚本后，我想到操作系统去看一下，然后再回来执行sqlplus命令 （不退出sqlplus 到系统上执行命令）
SQL> ? HOST
HOST
----
Executes an operating system command without leaving SQL*Plus.
Enter HOST without command to display an operating system prompt.
You can then enter multiple operating system commands.
HO[ST] [command]
用法1 ：host 回车 ----》操作系统-----》exit -----》sqlplus
用法2： host 操作系统命令
SQL> host ls -l /u01/app/oracle
total 24
-rw-r--r-- 1 oracle oinstall 58 Oct 11 15:26 1.sql
drwxr-x--- 3 oracle oinstall 4096 Oct 11 14:10 admin
清屏clear ---->host clear
复制1.sql到/tmp目录
SQL> host cp /u01/app/oracle/1.sql /tmp
SQL> host ls -l /tmp
total 524
-rw-r--r-- 1 oracle oinstall 58 Oct 11 15:30 1.sql
删除 /tmp下的1.sql
host rm /tmp/1.sql
host 所有系统都支持
在linux平台下 可以用！替换host
7 退出sqlplus
exit
quit
8 在/u01/app/oracle 有一个sql脚本 在sqlplus 执行脚本？？？
SQL> ? @
@ ("at" sign)
-------------
Runs the SQL*Plus statements in the specified script. The script can be
called from the local file system or a web server.
或者用
SQL> ? start
START
-----
Runs the SQL*Plus statements in the specified script. The script can be
called from the local file system or a web server.
STA[RT] {url|file_name[.ext]} [arg ...]
@/u01/app/oracle/1.sql
start /u01/app/oracle/1.sql
9 会把屏幕上的输出都保存到一个文件里-----》做日志用
? spool
用法;
spool 一个文件
执行命令
spool off 结束
10 如何在用户之间切换
conn 账户名/密码
SQL> show user;
USER is "SYS"
SQL> conn scott/oracle
sConnected.
SQL>show user;
USER is "SCOTT"
SQL> conn hr/hr
Connected.
SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
sqlplus /nolog 登录需要connect 连接数据库
SQL> conn scott/oracle
Connected.
SQL> show user;
USER is "SCOTT"
