ORACLE sqlplus基本操作

登陆，用户解锁

      使用oracle 10g windows版本，可直接安装到windows xp操作系统之上。使用sqlplus登陆数据库。

所有程序——oracle 10g —— 应用程序开发 —— sqlplus   /   使用命令行模式  sqlplus  用户/密码@数据库名称           （数据库名称可使用配置移植工具中的net manager添加）

本地登陆使用 sqlplus / as sysdba 不需账号密码使用sys登陆到数据库。

查看用户

使用sys用户解锁scott用户（默认锁定）

登陆scott用户

查看数据字典的结构

通过数据字典中的TABLE_NAME查看包含哪些表

查看EMP表的类型结构

使用SELECT语句查看EMP中所有的数据 （通过SET LINE 120 设置每行显示最大字节为120）

查询员工中SAL大于3000的员工号和员工名

可以使用RUN和/执行上次执行的命令

使用RUN 会显示上次执行的什么语句，而/ 不显示。

也可以通过LIST只显示上次执行的语句，而不执行。

使用LIST命令之后，可以通过A对其中一行追加，DEL删除，C修改。

显示员工SAL+COMM的值，并将列名改为AA

显示员工KING的员工号

显示员工1年的工作情况

使用连接符||

如果被包含在‘’中的字符串含有‘’号，使用转义的方法去掉字符串中‘’的特殊意义。

或

表中的空值进行任何运算结果都为空

SQL 语言与 SQLPLUS 的区别

SQLPLUS是执行SQL语句的一种环境，SQLPLUS命令可以缩写，SQL语句不行，而SQLPLUS不能改变数据库的值。

查询EMP表中SAL为1000，2000，3000的员工

查询SAL在1000到2000之间的员工 

查询SAL不是1000的员工

查询员工名称中包括A的员工

 

查询员工名称中第一个字母为A的员工

查询员工名称中第二个字母为A的员工

可以使用 \ 转义下一个字符，使下一个特殊字符不包含特殊意义。

查询员工中没有COMM的员工

查询员工中COMM不是空值的员工

优先级排列顺序

算数运算符〉连接符〉比较符〉NOT，AND，OR

查询员工名称中有A的，并且SAL大于1000的员工

ORDER BY  排序，出现在语句的最后方，后跟ASC为升序DESC为降序。

查询EMP表并且以SAL为轴的升序排列（默认为ASC）

查询EMP表根据DEPTNO排序，如果DEPTNO相同，根据SAL排序

查询EMP表，以DEPTNO排序为升序，SAL降序

ABS 求绝对值  MOD  求余数

ROUND精确  四舍五入（-1为四舍五入到小数点左边一位=十位）

TRUNC截取到哪一位

SQRT平方根

LOWER 将字符串改为小写

INITCAP 将首字母改为大写

SUBSTR截取第N位之后的字符

 

截取第3位以后的字符，并且只截取两位

INSTR取位数

取从第4位起，第二个出现的L

LPAD 补齐（补齐左边）

RPAD 补齐（补齐右边）

TRIM去头尾

只去头部，或者只去尾部

REPLACE 替换

查看系统时间

TO_CHAR 数字转换为字符串

TO_DATE 将字符串转换为日期

将字符串转换为数字

将左右两边都转换为数字

以字符模式显示系统时间，并显示时分秒和周

使用TO_CHAR可以将日期数字转换为字符串

查询员工收入情况，包括SAL和COMM 使用NVL（在两个值中取第一个非空值）

取第一个不为空的值

如果两个值不相同，则返回第一个值（相同则不返回）

计算COMM，在EMP表中，不同的JOB COMM不同

JOB为MANAGER COMM=SAL*0.2 其他为SAL*0.1

等值连接  利用相同列进行连接

非等值连接

查看EMP表中的SAL在SALGRADE中的SAL等级

自连接

在EMP表中，MGR=别人的EMPNO

外连接

与等值连接相同，但要求能查询出第一张表比第二张表的差值

AVG平均值  SAM总和  COUNT 统计  MAX最大  MIN最小

COUNT（*）返回所有行数，包括空值

当使用嵌套函数，必须使用GROUD BY

子查询

查询比SCOTT SAL高的

查询跟SCOTT一个部门且SAL比他低

 

 

查询比平均SAL高的

查询SAL比SALESMAN低的员工

查询工资比SALESMAN SAL高的

查询与SCOTT职务相同，但入职更早的

 

 

 

多列查询

查询与SCOTT部门相同，职位相同

查询EMP表中 第6位到第9位的员工

集合运算

UNION 去掉重复项

UNION ALL 保留重复项

MINUS 取差值

INTERSECT  取两个结果中相同的部分

查询部门10和20所有职务

查询部门10和部门20相同的职务

查询只在DEPT出现没有在EMP出现的部门

DCL 数据库控制语言  用于控制数据库的启动和关闭

DDL 定义语言 用来创建 删除 修改数据库对象

DML 添加删除查询数据库内容

事物控制语言 实现对数据的交易过程完整控制

INSERT 插入信行

UPDATE 修改已存行

DELETE 删除已存行

INSERT INTO 表名（字段） VLUSES（表达式）

插入1行  ENAME为小李 JOB为CLERK

从另一张表中复制内容

INSERT INTO 表名（字段） SELECT （字段） FROM 表名

从EMP表中插入内容到新表MANAGER中

创建一个以2000开头增量为1的序列，最大值为9999 并且循环不缓存

使用序列

UPDATE 表名 SET 字段 = 表达式 WHERE 条件

修改时间 （ 将EMPNO为1000的员工的HIREDATE改为现在）

所有员工SAL+100

UPDATE 表名 SET （字段） = SELECT （字段） FROM 另一张表 WHERE 条件

DELETE 删除

DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名   也可删除

区别：  TRUNCATE  删除数据不删除表结构 而且不能回退

数据库事物操作

优点

1. 把逻辑相关的操作分成一个组

2.在数据永久改变之前，可预览数据变化

3.保证数据一致性

数据库事物操作分为隐示，显示

正常退出SQLPLUS 则提交，数据变化保存 =  COMMIT

不正常退出则不保存

也可将环境变量AUTOCOMMIT 状态改为ON 则每次修改都进行保存

方法： SET AUTOCOMMIT ON

建立保存点，则回退时可回退到保存点

SAVEPOINT 保存点名称； 创建保存点

使用ROLLBACK TO 保存点名称； 回到保存点

直接使用ROLLBACK； 为回退。