sqlplus常用操作命令2
2024-10-15 03:18:22
常用编辑命令：
A[ppend] text 将text附加到当前行之后
C[hange] /old /new 将当前行中的old替换为new
CLear] buff[er] 清除缓冲区中的所有行
DEL 删除当前行
DEL x 删除第x行
L[ist] 列出缓冲区中所有行
L[ist] x 列出缓冲区中第x行
R[un]或/ 运行缓冲区中保存的语句
x 将第x行作为当前行
SQL> select product_id,name
from
products; PRODUCT_ID NAME
---------- ------------------------------
Modern Science
Chemistry
Supernova
Tank War
Z Files
: The Return
Space Force
From Another Planet
Classical Music
Pop
Creative Yell
My Front Line rows selected. SQL> 1 --输入1显示第1行
* select product_id,name
SQL> append ,price --在第1行增加列,price
* select product_id,name,price
SQL> list --列出缓冲区中所有行
select product_id,name,price
from
* products
SQL> 1 --指定要修改的行
* select product_id,name,price
SQL> change /price / description --将列price替换为description
SP2-: String not found.
SQL> change /price /description
* select product_id,name,description
SQL> list
select product_id,name,description
from
* products
SQL> / --执行缓冲区中保存的语句 PRODUCT_ID NAME DESCRIPTION
---------- ------------------------------ --------------------------------------------------
Modern Science A description of modern science
Chemistry Introduction to Chemistry
Supernova A star explodes
Tank War Action movie about a future war
Z Files Series on mysterious activities
: The Return Aliens return
Space Force Adventures of heroes
From Another Planet Alien from another planet lands on Earth
Classical Music The best classical music
Pop The best popular music
Creative Yell Debut album
My Front Line Their greatest hits rows selected. SQL> run --同上，执行缓冲区中保存的语句
select product_id,name,description
from
* products PRODUCT_ID NAME DESCRIPTION
---------- ------------------------------ --------------------------------------------------
Modern Science A description of modern science
Chemistry Introduction to Chemistry
Supernova A star explodes
Tank War Action movie about a future war
Z Files Series on mysterious activities
: The Return Aliens return
Space Force Adventures of heroes
From Another Planet Alien from another planet lands on Earth
Classical Music The best classical music
Pop The best popular music
Creative Yell Debut album
My Front Line Their greatest hits rows selected. SQL>
二、保存、检索并运行文件
SQL> list --列出缓冲区中的内容
select product_id,name,description
from
* products SQL> save /home/oracle/test_save.sql --保存缓冲区中的内容到指定路径指定的文件中
Created file /home/oracle/test_save.sql
SQL> get test_save.sql
select product_id,name,description
from
* products
SQL> start test_save.sql --将保存的文件内容读入缓冲区并执行 PRODUCT_ID NAME DESCRIPTION
---------- ------------------------------ --------------------------------------------------
Modern Science A description of modern science
Chemistry Introduction to Chemistry
Supernova A star explodes
Tank War Action movie about a future war
Z Files Series on mysterious activities
: The Return Aliens return
Space Force Adventures of heroes
From Another Planet Alien from another planet lands on Earth
Classical Music The best classical music
Pop The best popular music
Creative Yell Debut album
My Front Line Their greatest hits rows selected. SQL> @test_save.sql --同start PRODUCT_ID NAME DESCRIPTION
---------- ------------------------------ --------------------------------------------------
Modern Science A description of modern science
Chemistry Introduction to Chemistry
Supernova A star explodes
Tank War Action movie about a future war
Z Files Series on mysterious activities
: The Return Aliens return
Space Force Adventures of heroes
From Another Planet Alien from another planet lands on Earth
Classical Music The best classical music
Pop The best popular music
Creative Yell Debut album
My Front Line Their greatest hits rows selected. SQL> spool /home/oracle/select.txt --将以下输出的结果保存到指定文件中
SQL> / PRODUCT_ID NAME DESCRIPTION
---------- ------------------------------ --------------------------------------------------
Modern Science A description of modern science
Chemistry Introduction to Chemistry
Supernova A star explodes
Tank War Action movie about a future war
Z Files Series on mysterious activities
: The Return Aliens return
Space Force Adventures of heroes
From Another Planet Alien from another planet lands on Earth
Classical Music The best classical music
Pop The best popular music
Creative Yell Debut album
My Front Line Their greatest hits rows selected. SQL> spool off --停止输出结果，并关闭该文件，如果不spoo off，则后面输入的所有结果都会写入到上面的select.txt文件
SQL>
[root@rhel201 oracle]# ls
afiedt.buf oradiag_oracle select.txt testbak test_save.sql test.sql
[root@rhel201 oracle]# cat test_save.sql
select product_id,name,description
from
products
/
[root@rhel201 oracle]# cat select.txt
SQL> / PRODUCT_ID NAME DESCRIPTION
---------- ------------------------------ --------------------------------------------------
Modern Science A description of modern science
Chemistry Introduction to Chemistry
Supernova A star explodes
Tank War Action movie about a future war
Z Files Series on mysterious activities
: The Return Aliens return
Space Force Adventures of heroes
From Another Planet Alien from another planet lands on Earth
Classical Music The best classical music
Pop The best popular music
Creative Yell Debut album
My Front Line Their greatest hits rows selected. SQL> spool off
[root@rhel201 oracle]#
三、格式化列
SQL> / PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE
---------- --------------- ------------------------------ -------------------------------------------------- ----------
Modern Science A description of modern science 19.95
Chemistry Introduction to Chemistry
Supernova A star explodes 25.99
Tank War Action movie about a future war 13.95
Z Files Series on mysterious activities 49.99
: The Return Aliens return 14.95
Space Force Adventures of heroes 13.49
From Another Planet Alien from another planet lands on Earth 12.99
Classical Music The best classical music 10.99
Pop The best popular music 15.99
Creative Yell Debut album 14.99
My Front Line Their greatest hits 13.49 rows selected. SQL> col product_id format 99 --两位数字
SQL> col name heading product_name format a13 word_wrapped --将列name显示为product_name,13个字符串，自动换行
SQL> col descripton format a20 word_wrapped --20个字符串，自动换行
SQL> col proce format $99.99 --以该格式显示price列内容
SQL> / PRODUCT_ID PRODUCT_TYPE_ID product_name DESCRIPTION PRICE
---------- --------------- ------------- -------------------------------------------------- ----------
Modern A description of modern science 19.95
Science Chemistry Introduction to Chemistry
Supernova A star explodes 25.99
Tank War Action movie about a future war 13.95
Z Files Series on mysterious activities 49.99
: The Aliens return 14.95
Return Space Force Adventures of heroes 13.49
From Another Alien from another planet lands on Earth 12.99
Planet Classical The best classical music 10.99
Music Pop The best popular music 15.99
Creative Yell Debut album 14.99
My Front Line Their greatest hits 13.49 rows selected. SQL> -----------
SQL> show linesize
linesize 200
SQL> show pagesize
pagesize 50
SQL> set linesize 150
SQL> set pagesize 80
SQL> SQL> set verify on --启用old行和new行的输出
SQL> /
Enter value for test:
old : select * from products where product_type_id=&test
new : select * from products where product_type_id= PRODUCT_ID PRODUCT_TYPE_ID product_name DESCRIPTION PRICE
---------- --------------- ------------- -------------------------------------------------- ----------
Modern A description of modern science 19.95
Science Chemistry Introduction to Chemistry SQL> set verify off --禁用old和new
SQL> /
Enter value for test: PRODUCT_ID PRODUCT_TYPE_ID product_name DESCRIPTION PRICE
---------- --------------- ------------- -------------------------------------------------- ----------
Supernova A star explodes 25.99
Tank War Action movie about a future war 13.95
Z Files Series on mysterious activities 49.99
: The Aliens return 14.95
Return SQL>
说明：
save filename 保存内容，保存时加上路径
save filename replace/apend 保存并替换/追加
get filename 打开filename文件
start filename 打开filename文件并执行
@filename 同上
edit filename 使用记事本或vi打开filename文件进行编辑，如果不指定filename，则打开当前缓冲区中的内容
define _edit='editor'或define _edit='vi'定义默认编辑器
spool filename 将输出结果保存到指定文件
spool off 结束结果输出
最新文章
- 【NLP】基于机器学习角度谈谈CRF（三）
- WORD中字数和字符
- iOS-----类和对象,nil/Nil/NULL的区别
- mysql的group by应用
- HW3.4
- 清理SQL多余登录信息
- 20165235 Java第一周学习总结
- 【ML入门系列】（二）分类与回归
- ni_set()函数的使用 以及 post_max_size,upload_max_filesize的修改方法
- 创建DataSnap Server
- SecureCRT和乱码
- JavaWeb基础—JavaBean
- UWP Test Webview JS interact, new window open (新窗口打开)
- 有关ros::spin()和ros::spinonce()若干感受
- intellij自动生成java代码注释(java文件注释和方法注释)
- 多媒体开发之rtsp---rtsp client 端的实现
- 亿级 Elasticsearch 性能优化
- mysql错误10060
- decimal与 float的区别
- C#log4net引入配置文件后，数据库连接找不到并且有很多 未能找到元素“appender”的架构信息