Oracle学习(3)

1* select * from dept SQL> /

6. Break: 分组， Syntax: break on 分组列 skip n //n为每两组之间间隔的空行 /* 例：

SQL> break on job

SQL> select * from emp order by job; // 此处务必按分组列排序 SQL> break on job skip 1 SQL> / */

7. Btitle, Ttitle:设置表尾、表头 SQL> Ttitle '表头' SQL> Btitle '表尾' SQL> Ttitle off //失效

8. Change: 写错了改正, Syntax: C/old/new /* 例：

SQL> select * from detp; select * from detp SQL> c/tp/pt

1* select * from dept SQL> /

9. Clear: 清除

SQL> clear break // 清除前面的break设置

SQL> clear buff // 清除SQL*Plus缓冲区中的SQL语句 10. Column: 列定义，用于定义列的显示格式 语法：SQL> column 列名 format 格式 例：SQL> Column sal format $99.999999 11. computer: 统计计算

12. connect: 从一个用户退出，直接进入另一个用户 13. disconnect: 中断当前用户的连接

14. copy: 远程复制（用的较少，一般数据链路) 15. define:定义

16. undefine:取消定义 17. del: 删除当前行

18. describe（简写desc): 显示表结构 例：SQL> desc emp 19. document: 文档注释

20. edit(简化ed):调入外部编辑器编辑缓冲区中的SQL语句，保存后，可以直接通过“/”运行编辑过的SQL 语句

21. get: 将外部文件调入不执行，只供显示 例：SQL> get c:\\sqlplus.ora 22. host: 执行操作系统命令 例：SQL> host dir

23. input(简化为I): 行插入，在当前行的后面插入。

24. List(简化为L): 列示 25. Pause: 设置屏幕暂停 SQL> set pause on //暂停 SQL> set pause off //不暂停 SQL> set 'more' pause on [???] 26. Quit: 退出 27. Remark: 注释 28. Run(R): 类似“/”

29. Save: 将已经运行过的SQL命令保存到磁盘 例：SQL> save c:\\run.sql replace //覆盖保存 append //追加 30. set: 设置

31. show: 显示环境变量的值 32. Spool: 显示跟踪

例：SQL> spool c:\\文件名 SQL> spool off //停止记录

33. Start: 类似@，执行外部的SQL文件

33. Timing: 服务器端某一命令的执行时间跟踪

数据字典：描述系统信息的表、视图、同义词等，由系统自动维护。 例：SQL>select * from tabs;

1. 显示当前所登录的用户：select * from all_users;

2. user_xxx:描述用户创建的对象，如：user_tables，user_views，user_indexes 3. dba_XXX:数据库管理员(sys, system)专用数据字典

4. all_XXX: 描述用户创建的对象，其他用户授权可以存取的对象。 all_objects: 全部的数据字典 1. 查询全表的数据： SQL> select * from emp;

2. 查询某（几）个列：SQL> select ename, sal from emp; 3. DISTINCT 标识：只显示不相同的列 例：

SQL> select job from emp; // 显示结果中有相同的记录 SQL> select distinct job from emp; // 只显示不相同的职业 4. 使用order by将显示结果排序：ASC升序（默认），DESC降序 5. 使用where指出查询条件：

SQL> select * from emp where sal > 2000;

[字符型或日期型用单引号括起来，并区分大小写] 6. 设置日期显示格式：

(1) 确定日历格式：设置参数nls_calendar。

SQL>alter session set nls_calendar='Japanese Imperial'; //设置为日本日历 SQL>alter session set nls_calendar='ROC official'; //设置为台湾日历

SQL>alter session set nls_calendar='Gregorian'; //设置为格林尼治日历，这是我们要设置成的日历。

SQL> select sysdate from dual; //查询系统时间 (2) 确定日期格式：参数nls_date_format

常用的日期格式： yyyy.mm.dd yyyy-mm-dd yyyy/mm/dd

yyyy\年\月\日\如果要显示汉字，请用双引号括起来 yyyy\年\月\日\表示星期 命令格式：

SQL>alter session set nls_date_format='格式';

[此命令只修改前端的显示，退出后即失效，要想永久有效，要修改注册表，在注册表中 HKLM->Software->Oracle->Home0增加字符串关键字nls_date_format，键值为日期格式，此处的格式不需要 用单引号括起来。]

例：SQL>alter session set nls_date_format='yyyy\年\月\日\

7. Oracle登录自动执行文件glogin.sql:每次用户登录时自动执行该文件，可以将一些环境变量（如

linesize）的设置命令或者其它命令放入该文件中。 glogin.sql文件路径：

Windows下：d:\\oracle\\ora81\\sqlplus\\admin\\glogin.sql

UNIX: /u01/app/oracle/product/8.1.6/sqlplus/admin/glogin.sql 二、运算符与谓词

1、算术运算符：+, -, *, /

SQL> select sal, sal*12 from emp; //计算工资及年薪。

SQL> select sal, sal+comm from emp; //错误语句，因为comm中存在空值，会使运算结果也会出现空值。

SQL> select sal, sal + NVL(comm,0) from emp; // 正确语句，NVL为空值运算函数，当comm为空值时，

该函数返回第二个值0。

2、逻辑运算符：NOT, AND, OR，三个运算符的优先级别依次降低。 3、比较运算符：>, <, =, >=, <=, != 4、谓词：

(1) IN(或NOT IN):等于（不等于）列表中的任意值。

SQL> select * from emp where job in ('MANAGER', 'CLERK')

(2) (NOT) BETWEEN AND : 表示从小到大的一个范围。[必须是从小到大] SQL> select * from emp where sal not between 2000 and 3000; (3) LIKE:模式匹配

SQL> select * from emp where ename like 'S%'; //寻找ename为S打头的记录 %: 表示任意字符串

_(下划线): 表示一个任意字符

SQL> select * from emp where ename like '李%'; //可以使用中文

SQL> select * from emp where ename like 'S_I%'; //寻找ename为S打头，第三个字母为I的记录

SQL> select object_name from all_objects where object_name like 'DBA%'; //搜索数据字典中与dba

有关系的对象

(4) (NOT) NULL(空值):

SQL> select * from emp where comm is NULL; //查询哪些人没有资金。 5. 伪列：

(1) rowid: 唯一行标识，行被删除之后，rowid不变。

(2) rownum: 行号，一行被删除之后，后面的行号会随之改变 SQL> select empno, ename, rowid, rownum from emp; [查询SQL语句的相关数据字典]

（1）SQL> select sql_text from v$sqlarea; //查询以前使用过的SQL语句 （2）查询当前连接用户的SQL语句：

SQL> select user_name,sql_text from v$open_cursor; //需要用sys或system用户执行 三、列名别名

SQL> select ename, sal as Salary from emp;

SQL> select ename 职工姓名, sal 工资 from emp;

* # / \\ select 都不允许做别名，如果一定要用，用双引号括起来。 数据操纵语言(DML) 一、数据插入：

1. 对于表中全部列插入

语法：SQL> insert into 表名 values(值表达式);

例：SQL> Insert into dept values(51, '软件开发部', '北京');

[*] 用desc显示表结构、数据类型、顺序 SQL> desc dept //注意，不要将linesize设置的太大，否则看不

到表结构，设置成100即可。 [*] 所插数据必须与目标列一致。 [*] 字符与日期数据使用单引号 2. 对于表中部分列插入

语法：SQL> insert into 表名（列名1，列名2...) values(值表达式); [*]对于表中的非空列必须插入数据

SQL> insert into emp(empno, ename, job, hiredate,deptno) values(1234, '李大力', '工程师', sysdate - 30, 10);

/* sys 或 system 用户访问其它用户的表的方法 SQL> connect system/ab@ora44 已连接。

SQL> select * from user01.emp */

/******************** commit 及 Rollback用法:下例是Rollback的一个例子。 SQL> delete dept where deptno = 51; 已删除 1 行。

SQL> select * from dept; SQL> rollback;

SQL> select * from dept; DEPTNO DNAME LOC

---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO

40 OPERATIONS BOSTON 50 COMPUTER BEIJING 51 软件开发部 北京 已选择6行。

*************************************************/ 3. 使用参数(变量)，临时输入值

SQL> insert into 表名(列名1,列名2, ...) values(&x1, &x2, ...); SQL> insert dept(deptno, dname, loc) values(&x1, '&x2', '&x3')

SQL> insert into dept values(&x1, '&x2', '&x3'); // 在命令行中给字符或日期型参数加上单引号，则

下面输入时不再需要输入单引号 输入 x1 的值: 53 输入 x2 的值: dd 输入 x3 的值: ee

原值 1: insert into dept values(&x1, '&x2', '&x3') 新值 1: insert into dept values(53, 'dd', 'ee') // define x = 7788

// 也可以这样用：select &x1, &x2 from emp; 4. 使用子查询从另一个表中复制数据 语法：Insert into 表名 select 子句;

SQL> insert to emp(empno, hiredate, deptno) select deptno+7300, sysdate, deptno from dept; SQL> insert to emp(empno, hiredate, deptno)

select deptno+7300, sysdate, deptno from dept@数据库链路名; //表示dept这个表在网络上的其它服务

器中，注意：@之后不是连接串。

SQL> create table pay as select ename, sal from emp; // 用复制的方法创建表

SQL> insert into pay select * from pay; //自己复制自己，可以使一个表飞速扩大。 二、数据更新

语法：SQL> Update 表名 set 列名=值表达式 where 条件; 例：

SQL> update emp set sal = sal + 100 where sal < 2000;

SQL>update emp set sal = 5000, job='MANAGER' where ename = 'SMITH'; SQL> update emp set comm = 100 where comm is null; 三、数据删除

语法： SQL> Delete from 表名 where 条件; //删除数据保留结构，可以回退 SQL>Truncate table 表名; //删除数据保留结构，不可以回退，效率高 SQL>Drop table 表名; //删除数据及结构，不可以回退 数据控制语言(DCL) 一、事务提交：对于数据的插入、更新、删除，只有提交后，数据才真正改变，在提交之前，只有修改了数

据的用户才可以看到数据的改变，而其他用户看不到数据的改变。 1. 显式数据提交语法：SQL> commit;

/* 某一个用户对表进行更新等写操作之后，如果没有commit，那么其它用户不能对表进行