SQL*Plus命令
2023-09-27 14:21:40 时间
SQL*Plus命令
前言
一:SQL*Plus 与数据库的交互
二:设置SQL* Plus的运行环境
二 - 1 :SET命令概述
二 - 2 :使用SET命令设置运行环境
二 - 2 ____1:Pagesize 变量
View Code1 SYS@orcl> show pagesize 2 pagesize 14 3 SYS@orcl> set pagesize 18 4 SYS@orcl> show pagesize 5 pagesize 18 6 SYS@orcl>
二 - 2 ____2:newpage 变量
View Code1 2 SYS@orcl> show newpage 3 newpage 1 4 SYS@orcl> set newpage 5 5 SYS@orcl> show newpage 6 newpage 5 7 SYS@orcl>
二 - 2 ____3:linesize 变量
View Code1 SYS@orcl> show linesize 2 linesize 80 3 SYS@orcl> set linesize 3000 4 SYS@orcl> show linesize 5 linesize 3000 6 SYS@orcl>
二 - 2 ____4:pause 变量
View Code1 SYS@orcl> show pause 2 PAUSE is OFF 3 SYS@orcl> set pause on 4 SYS@orcl> show pause 5 PAUSE is ON and set to "" 6 SYS@orcl>121
二 - 2 ____5:numformat 变量
View Code1 SYS@orcl> select ename,job,sal from scott.emp; 2 3 4 5 6 7 8 ENAME JOB SAL 9 ---------- --------- ---------- 10 SMITH CLERK 800 11 ALLEN SALESMAN 1600 12 WARD SALESMAN 1250 13 JONES MANAGER 2975 14 MARTIN SALESMAN 1250 15 BLAKE MANAGER 2850 16 CLARK MANAGER 2450 17 SCOTT ANALYST 3000 18 KING PRESIDENT 5000 19 TURNER SALESMAN 1500 20 ADAMS CLERK 1100 21 22 23 24 25 26 27 ENAME JOB SAL 28 ---------- --------- ---------- 29 JAMES CLERK 950 30 FORD ANALYST 3000 31 MILLER CLERK 1300 32 33 14 rows selected. 34 35 SYS@orcl> set pagesize 500 36 SYS@orcl> show pagesize 37 pagesize 500 38 SYS@orcl> set numformat $999,999,999.00 39 SYS@orcl> select ename,job,sal from scott.emp; 40 41 42 43 44 45 46 ENAME JOB SAL 47 ---------- --------- ---------------- 48 SMITH CLERK $800.00 49 ALLEN SALESMAN $1,600.00 50 WARD SALESMAN $1,250.00 51 JONES MANAGER $2,975.00 52 MARTIN SALESMAN $1,250.00 53 BLAKE MANAGER $2,850.00 54 CLARK MANAGER $2,450.00 55 SCOTT ANALYST $3,000.00 56 KING PRESIDENT $5,000.00 57 TURNER SALESMAN $1,500.00 58 ADAMS CLERK $1,100.00 59 JAMES CLERK $950.00 60 FORD ANALYST $3,000.00 61 MILLER CLERK $1,300.00 62 63 14 rows selected. 64
三:常用SQL*Plus 命令
三 - 1 : hlep 命令
三 - 2 : describe 命令
三 - 3 :spool命令
三 - 4: define 命令
View Code1 SYS@orcl> define vjob='sajob' 2 SYS@orcl> define vjob 3 DEFINE VJOB = "sajob" (CHAR) 4 SYS@orcl>
三 - 5: show 命令
三 - 6: edit命令
三 - 7: save 命令
三 - 8: get命令
三 - 9: start 和 @ 命令
四:格式化查询结果
四 - 1: column 命令
四 - 1 ____1:format 变量
四 - 1 ____2:heading 变量
View Code1 2 00:05:44 SYS@orcl> col empno heading 雇员编号 3 00:06:07 SYS@orcl> col ename heading 雇员姓名 4 00:06:17 SYS@orcl> col sal heading 雇员工资 5 00:06:28 SYS@orcl> select empno,ename,sal from scott.emp; 6 7 8 9 10 11 12 雇员编号 雇员姓名 雇员工资 13 ---------------- ---------- ------------ 14 $7,369.00 SMITH $8,00 15 $7,499.00 ALLEN $16,00 16 $7,521.00 WARD $12,50 17 $7,566.00 JONES $29,75 18 $7,654.00 MARTIN $12,50 19 $7,698.00 BLAKE $28,50 20 $7,782.00 CLARK $24,50 21 $7,788.00 SCOTT $30,00 22 $7,839.00 KING $50,00 23 $7,844.00 TURNER $15,00 24 $7,876.00 ADAMS $11,00 25 $7,900.00 JAMES $9,50 26 $7,902.00 FORD $30,00 27 $7,934.00 MILLER $13,00 28 29 14 rows selected. 30 31 00:06:47 SYS@orcl>
四 - 1 ____3:null 变量
View Code1 2 00:08:39 SYS@orcl> col comm null '空值' 3 00:08:57 SYS@orcl> select empno,ename,comm from scott.emp where comm is null; 4 5 6 7 8 9 10 雇员编号 雇员姓名 COMM 11 ---------------- ---------- ---------------- 12 $7,369.00 SMITH 空值 13 $7,566.00 JONES 空值 14 $7,698.00 BLAKE 空值 15 $7,782.00 CLARK 空值 16 $7,788.00 SCOTT 空值 17 $7,839.00 KING 空值 18 $7,876.00 ADAMS 空值 19 $7,900.00 JAMES 空值 20 $7,902.00 FORD 空值 21 $7,934.00 MILLER 空值 22 23 10 rows selected. 24 25 00:09:19 SYS@orcl>
四 - 1 ____4: ON|OFF选项
四 - 2: tttle 和 btitle 命令
View Code1 00:16:38 SYS@orcl> set pagesize 8 2 00:16:43 SYS@orcl> ttitle left '销售情况排行表' 3 00:16:59 SYS@orcl> btitle left '打印日期:2017 /12/24 打印人: 一品堂' 4 00:17:39 SYS@orcl> select * from scott.salgrade; 5 6 7 8 9 10 11 销售情况排行表 12 GRADE LOSAL HISAL 13 ---------------- ---------------- ---------------- 14 $1.00 $700.00 $1,200.00 15 ‘打印日期:201712/24 打印人:烈黄诽 16
View Code1 2 00:19:18 SYS@orcl> set pagesize 8; 3 00:19:24 SYS@orcl> define varT= ' 雇员信息表' 4 00:19:40 SYS@orcl> define var8=' 操作人:一品堂' 5 00:20:02 SYS@orcl> ttitle left varT; 6 00:20:45 SYS@orcl> btitle left var8; 7 00:20:56 SYS@orcl> select empno,ename,job from scott.emp; 8 9 10 11 12 13 14 雇员信息表 15 雇员编号 雇员姓名 JOB 16 ---------------- ---------- --------- 17 $7,369.00 SMITH CLERK 18 操作人:一品堂‘ 19
————————————————————————————————————————————————————————————————
————————————————————————————————————————————————————————————————
相关文章
- Hive on Spark和Spark sql on Hive,你能分的清楚么
- mysql的sql执行计划详解(非常有用)
- 优化SQL语句
- 在SQL Server中执行动态SQL命令
- SQL字符串函数
- sql基本命令-存储过程
- SP2-0642: SQL*Plus internal error state 2130, context 0:0:0
- 百万用户使用的插件 WordPress NextGEN Gallery出现SQL注入漏洞 站长们赶紧自查
- 【译文】怎样在R语言中使用SQL命令
- JAVAWEB dbutils执行sql命令并遍历结果集时不能查到内容的原因
- sql server中QUOTENAME()函数的用法
- SQL Server数据库SP命令祥解
- 【甄选靶场】Vulnhub百个项目渗透——项目七:DerpNStink-1(sql注入,流量分析)
- sql语句备份/导入 mysql数据库或表命令
- MSSQL - 架构分析 - 从SQL Server 2017发布看SQL Server架构的演变
- mysql命令:set sql_log_bin=on/off