ORACLE笔记

1.Oracle 工具:sqlplus

Sqlplus / as sysdba

Shutdown immediate(关闭数据库)

Startup(启动数据库)

注意：数据库开启才可以进行操作

Select username,account_status from dba_users;(查询数据库中所有的用户名称与用户状态) Alter user scott identified by tiger account unlock;(scott用户解锁)

Sqlplus scott/tiger(使用SCOTT 密码为tiger登陆ORACLE数据库)

Connect scott/tiger (在SQL>中直接使用SCOTT用户连接数据库)

Show all;(看所有变量)

Set sqlprompt “_user>”(设置sqlplus环境下面的提示符用自身用户显示)

注意：如要变量下次重启生效必须把变量写入

/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql中

Host(回到系统)

Help index（查看所有命令）

Help+命令(类似于LINUX中的MAN,查看命令的格式与用法)

Disconnect(关闭SQLPLUS工具但是不离开数据库)

Describe emp(查看EMP表)

Define(定义变量,常用与写脚本用)

如：DEFINE _EDITOR =”vi” (CHAR) (定义VI编辑器用于保存最后一条执行的SQL语句) Save /u01/app/oracle/aa.sql (保存SQL语句)

Get /u01/app/oracle/aa.sql (调用保存的SQL语句)

2.select（select 查询语句是ORACLE中最常用的语句）

DML语句包括（insert 写入，update改变，delect删除，merge两张表同步）

注意：merge常用在ETL（数据仓库）底下

DDL数据定义语句包括（create创建对象，alter修改，drop删除对象，rename改名，truncate 删除整个表）

TCL事务处理语句（commit提交立刻生效，rollback回滚，savepoint保存点，savepoint(保存点)

DCL权限语句（grant赋予权限，revoke(收回权限)

ORACLE的表称之为堆表（keep table）

SELECT

(1) writing basic SQL select statemanes

1. selecting all columns

SCOTT>select * from dept;

SCOTT>select dname,deptno,loc from dept;

Basic SELECT Statement

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table;

2. selecting specific columns

SCOTT>select deptno,dname from dept;

SCOTT>select ename,sal from emp;

3. writing SQL statements

SQL statements are not case sensitive.

SQL statements can be on one or more lines.

Keywords cannot be abbreviated or split across lines.

Clauses are usually placed on separate lines.

Indents are used to enhance readability.

select * FROM EMP;

SELECT *

FROM emp;

4. Arithmetic Expressions

An arithmetic expression can contain column names, constant numeric values, and the arithmetic operators.

+ - * /

arithmetic operators in any clause of a SQL statement except in the FROM clause

select ename,sal+300 from emp;

Operator Precedence

Multiplication(*) and division(/) take priority over addition(+) and subtraction(-).

Operators of the same priority are evaluated from left to right.

Parentheses(...) are used to force prioritized evaluation and to clarify statements.

select ename,sal,12*sal+100 from emp;

select ename,sal,12*(sal+100) from emp;

5. defing a NULL value

A null is a value that is unavailable, unassigned, unknown, or inapplicable.

A null is not the same as zero or a blank space.

SCOTT>select ename,sal,comm from emp;

SCOTT>select ename,sal,12*sal*comm from emp;

Arithmetic expressions containing a null value evaluate to null.(空值和任何数字计算等于空值)