oracle简单语法笔记

DML Data manipulation language
SELECT	SELECT [DISTINCT] *|列xx [AS] "别名xx"[,列xx "别名xx"...]	字符串连接符 ||, 字符或日期类型的字符串用单引号’’, 列别名用双引号“”。如果别名中有空格、特殊字符或者要求区分大小写，必须用双引号。默认情况下列标题为大写，字符和日期标题靠左，数字靠右 列别名可以用在order by中，但不能用在where和group by里 SQL不区分大小写，关键字不能简写或分行写，语句结束用; ROWID,ROWNUM行标号，虚拟列 DISTINCT消除重复列
	FROM 表xx 表别名xx[，表xx 表别名xx...]
	WHERE 条件	is NOT Null，is Null,NULL是不确定的未知的值 + - * / > >= = <= < <> != and, or, not [not] between .. and In, not In Like, not like (%任意长度，_一个长度) Like 'a/%' Escape '/' = like 'a%', escape 定义转义字符/，也可以用其他字符 优先规则：算术》连字》比较》[not] null,like,in》[not] between》not》and》or
	GROUP BY 分组列 HAVING 分组条件	分组函数只在分组中使用(select,having)，不允许在where中，要指定条件用having。 Select关键字之后列出的每个统计函数之外的字段，都要出现在group by之后 用Where条件进行分组前的过滤，having进行分组后的过滤，即指定哪些分组结果被显示
	ORDER BY 排序字段 ASC|DESC	order by后可以是字段或者字段运算后的表达式，默认Asc order by可以用列别名，select中的列序号，也可以是不在select中的列
多表查询SQL:1999	select * from emp CROSS JOIN dept	交叉连接：会产生笛卡尔积
	select * from emp Netural JOIN dept	自然连接：自动关联两个表中有相同名字和数据类型的列
	select * from emp JOIN dept USING(deptno)	Using子句：直接关联列
	select * from emp e JOIN dept d ON(e.deptno=d.deptno)	On子句：用户自己编写关联条件，可以用多个join..on连接多个表
	select * from emp e RIGHT OUTER JOIN dept d ON(e.deptno=d.deptno)	右外连接：相当于where e.deptno(+)=d.deptno (+)放在连接的缺少信息的一侧的列名后面，如显示没有员工的部门，将+放在员工表后面；显示没有部门的员工，将+放在部门表后面
	select * from emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno)	左外连接：相当于 where e.deptno=d.deptno(+) 左连接就是添加回内连接从第一个表中删除的所有行
	select * from emp e FULL OUTER JOIN dept d ON(e.deptno=d.deptno)
集合操作	Select a,b from tb1 Union Select a,b from tb2	Select的数据类型必须兼容，每个结果集中列的数量和顺序必须一致 返回并集，删除重复行。
	Select a,b from tb1 Union All Select a,b from tb3	返回并集，不删除重复行
	Select a,b from tb1 INTERSECT Select a,b from tb2	返回相同部分
	Select a,b from tb1 MINUS Select a,b from tb3	返回两个结果的差集
子查询	Select * from emp	用在父查询where条件中，或者是父查询中的一列。子查询根据返回值分单列子查询，单行子查询和多行子查询。用In，Any，All指定查询的范围
	Where sal IN (select min(sal) from emp group by deptno)
	Where sal =ANY (select min(sal) from emp group by deptno)	与IN一样
	Where sal >ANY (select min(sal) from emp group by deptno)	> ANY: 比里面最小的值要大 （大于最小值）
	Where sal <ANY (select min(sal) from emp group by deptno)	< ANY: 比最大的值要小 （小于最大值）
	Where sal >ALL (select min(sal) from emp group by deptno)	> ALL: 比最大的值要大  （大于所有值，即要大于最大值）
	Where sal <ALL (select min(sal) from emp group by deptno)	<ALL: 比最小的值要小 （小于所有值，即要小于最小值）
INSERT	INSERT INTO 表xx(列xx,列xx,列xx) VALUES(值xx,值xx,值xx)
	INSERT INTO 表xx(列xx,列xx,列xx) SELECT …
	INSERT INTO 表xx(列xx,列xx,列xx) VALUES(&列xx,&列xx,&列xx)	用&xxx作为插入值时会出现交互输入界面，当输入字符时也要用''
UPDATE	UPDATE 表xxxx SET 列xx=xx... WHERE ....
DELETE	DELETE FROM 表xxx WHERE 条件;
MERGE	MERGE INTO table_name table_alias   USING (table|view|sub_query) alias   ON (join condition)   WHEN MATCHED THEN     UPDATE SET     col1 = col_val1,     col2 = col2_val   WHEN NOT MATCHED THEN     INSERT (column_list)     VALUES (column_values);	将using后的表中数据插入到表table_name，如果满足条件join condition，执行update set操作，不满足则insert
select for update	select xxx from xxx FOR UPDATE;	在PL/SQL Developer中可以用for update打开查询表进行DML增删改操作，还可以从其他表格中拷贝数据
DDL Data definition language
CREATE	CREATE TABLE 表xx AS SELECT xxx	只在Oracle中有，如果where 1=2则只复制结构
	CREATE TABLE [schema.]表xx (列xx 类型 [DEFAULT 默认值]， 列xx 类型 [DEFAULT 默认值]， ….)
	分区表
	CREATE [UNIQUE] INDEX 索引名 ON 表名 (列名1 [ASC|DESC] [,列名2 [ASC|DESC]…]) [NOLOGGING] TABLESPACE 表空间名	1.根据索引的逻辑结构分单列、复合和函数索引。索引一般不对列关键字的值是否允许重复加以限制，若要加则是唯一性索引unique 2. 在线重定义index？ 3.建议index/table<=4，特别是对DML常用表，有index会使更新速度变慢 4.用哪个index由oracle决定，分CBO(cost-based)和RBO(rule)，9i之前用RBO多，9i两种都有，>=10g以CBO为主，CBO会分析数据分布，取I/O,CPU最小的方法 5.Oracle的B树索引：更新成本低，适合OLTP；位图索引，可列举的有限项，若新增项则更新量较大，全更新完才能用，更新成本高，适合数据仓库 6.PL/SQL Developer的explain plan（F5）查看语句执行是否用到了索引，用到了哪些索引
	CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图xxx[(col1,col2...)] AS 子查询 [WITH CHECK OPTION [CONSTRAINT 约束名]];	1.视图就是封装了一个查询语句 2.FORCE:强制创建一个视图，不管基表是否存在或者创建者对基表是否有select权限。但是只有在基表存在时才可用 3.NOFORCE：只在基表存在的情况下创建视图，默认值 4.子查询：不能用order by，可以有列别名 5.with check option：限制在试图上进行的修改insert update都要符合子查询中定义的限制条件 6.如果子查询中有1)group by,2)rownum,3)distinct,4)组函数,5)表达式列,6)基表中not null的列不在视图中。1-4）不能通过视图delete，1-5）不能update，1-6）不能insert
	CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图xxx AS SELECT xxx [WITH READ ONLY [CONSTRAINT 约束名]];	with readonly只读
	oracle物化视图	数据仓库中，将结果放某空间，定期刷新，供多人查询，加快效率
	CREATE SYNONYM 同义词xx FOR 用户xx.表xx
	CREATE SEQUENCE 序列名 [increment by n][start with n] [{maxvalue n|nomaxvalue}] [{minvalue n|nominvalue}] [{cycle|nocycle}] [{cache n|nocache}];	取值： 序列名.NEXTVALL 序列名.CURRVAL 多个进程在查序列号，可能丢号，所以关键数据尽量不用
ALTER	ALTER TABLE 表xx ADD(列xx 类型 [NOT NULL] [DEFAULT 默认值]…)
	ALTER TABLE 表xx MODIFY(列xx 类型 [NOT NULL] [DEFAULT 默认值]…)
	ALTER TABLE 表xx DROP COLUMN 列xxx
	ALTER TABLE 表xx RENAME COLUMN 列xx TO 列xxx
	ALTER INDEX 索引xxx REBUILD|ONLINE
	ALTER INDEX 索引xxx MONITORING USAGE
CONSTRAINT	数据完整性指数据的一致性和准确性，分行完整性（主键约束），引用完整性（外键），和列完整性
	列xx 类型 PRIMARY KEY	主键约束PRIMARY KEY，表示唯一的标识，不能为空。一个表只能有一个主键约束
	CONSTRAINT 约束名pk_表xx_列xx PRIMARY KEY(列xx)
	列xx 类型 CONSTRAINT  约束名 PRIMARY KEY
	列xx 类型 [NOT NULL]	非空约束NOT NULL
	列xx 类型 UNIQUE	唯一约束UNIQUE，该列或列的组合不允许重复
	CONSTRAINT 约束名uk_表xx_列xx UNIQUE(列xx)
	列xx 类型 CHECK(列需满足的条件)	检查约束CHECK：检查列值的合法性
	CONSTRAINT 约束名ck_表xx_列xx CHECK(条件)
	CONSTRAINT 约束名fk_父表xx_子表xx_列xx FOREIGN KEY(列) REFERENCES 父表xx(列xx)	主外键约束FOREIGN KEY：子表中设置的外键必须是父表中的主键（必须存在对应的记录）
	CONSTRAINT 约束名fk_父表xx_子表xx_列xx FOREIGN KEY(列) REFERENCES 父表xx(列xx) ON DELETE CASCADE	级联删除：希望删除一个数据时，自动删除对应的子表记录
	DROP TABLE 子表xx CASCADE CONSTRAINT	有外键，删除时必须先删除子表，再删除父表，或者用强制删除
	ALTER TABLE 表xx ADD [CONSTRAINT 约束名] 约束类型（列或条件）	添加约束
	ALTER TABLE 表xx DROP CONSTRAINT 约束名 [cascade]	删除约束
	ALTER TABLE 表xx DROP PRIMARY KEY|UNIQUE(col)
	ALTER TABLE 表xx DISABLE CONSTRAINT 约束名	禁用约束
	ALTER TABLE 表xx ENABLE CONSTRAINT 约束名	启用约束
DROP	DROP TABLE 表xx
	DROP INDEX 索引xx
	DROP VIEW 视图xx
	DROP SYNONYM 同义词xx	只在Oracle中
	DROP SEQUENCE 序列名
RENAME	RENAME 表/视图/序列/同义词xx TO 新名xx	只在Oracle中
TRUNCATE	TRUNCATE TABLE 表xx	Truncate删数据，释放磁盘空间，不删表结构，不能回滚 Delete  删数据，不释放磁盘空间，不删表结构和索引，能回滚 Drop    删数据，删表结构及上面的索引 如果是引用完整性的父表，不能用truncate table
Transaction control
COMMIT		保证数据操作的完整性，所有操作要么成功，要么同时失败
ROLLBACK	rollback或者rollback to save_point
SAVEPOINT
DCL Data control language
GRANT	GRANT 权限xx… TO 用户	权限：create session, connect, resources,select, delete…
	GRANT select,delete ON 用户xx.表xx TO 用户
REVOKE	REVOKE 权限xx ON 用户xx.表xx FROM 用户
	CREATE USER 用户名 IDENTIFIED BY 密码
	ALTER USER 用户名 IDENTIFIED BY 密码
	ALTER USER 用户名 PASSWORD EXPIRE	密码失效
	ALTER USER 用户名 ACCOUNT LOCK	锁住用户
	ALTER USER 用户名 ACCOUNT UNLOCK	解锁
PL/SQL Programming
	DECLARE   deptno dept.dept_id%type;intuserid int;...   error_handle EXCEPTION; BEGIN   …   intuserid :=1;   select col1 INTO var1,... from tb where condition;   dbms_output.put_line(intuserid);   execute immediate xxx;   FOR i IN lowid .. hiid LOOP        ...   END　LOOP;   IF ... THEN...END IF;   RAISE error_handle; EXCEPTION   when error_handle then ...   when others then   rollback;outvarmsg:=sqlerrm; END;
游标cursor	CURSOR cur_name IS SELECT col1,col2 xxxx; OPEN cur_name; WHILE cur_name %FOUND LOOP    FETCH cur_name INTO var1,var2    … END LOOP FOR cur IN cur_name LOOP   cur.col1=… END LOOP; CLOSE cur_name;	有时需要对查询结果进行批量处理，这时Oracle将分配一片内存区域用以保存结果集，而游标就是一个指向此类内存区域的指针。通过使用游标并建立循环结构，可以依次处理结果集中的各行数据。游标使用一般分四步：声明游标，打开游标，提取数据，关闭游标
Procedure	CREATE [OR REPLACE] PROCEDURE pro_name(var1 type,var2 type...) AS BEGIN  ……; END pro_name; /	调用： 1. PL/SQL Developer的test window 2. PL/SQL Developer的command window: execute immediate sql 3. begin … pro_name(var1,var2);… end
function	CREATE [OR REPLACE] FUNCTION fun_name(var1 type,var2 type...) RETURN datatype AS BEGIN  ……; END fun_name; /
Trigger	create trigger trigger_test  before insert on student begin   null; end; /	在特定事件出现时自动执行的程序代码块。启用触发器可以限制对表的修改，制作自增字段，强制实现数据的一致性以及启用复杂的业务逻辑。触发事件包括插入删除更新列。还可以设定“逐行触发”
SQL Plus命令
	SQL PLUS中，输入的sql语句将存储在一个叫sql buffer的内存区域，保存在sql buffer中的sql语句可以使用sql plus命令调出修改和执行。也可以使用sql plus命令执行一个sql脚本文件
	startup	启动数据库
	shutdown	关闭数据库
	connect/conn 用户名/密码 [as sysdba|sysoper]	连接数据库,默认scott/tiger, sys/change_on_install, system/manager
	disconnect	断开连接
	set	设置SQL PLUS的系统变量与环境变量
	set linesize xxx
	set pagesize xxx
	spool	将窗口上显示的内容输出到文件
	edit	对内存区域中的SQL语句加以编辑修改，内存区域仅保留上一个语句的第一行
	ed 文件名	编辑文件
	run	执行内存区域中的SQL语句
	/	执行上一次运行的SQL语句
	start	执行一个SQL脚本文件
	@文件名	执行文件
	col	格式化列的显示形式
	clear	清除缓冲区或屏幕内容
	edit	退出SQL PLUS
	show user	显示当前用户
	describe 表名/视图名	查看表结构
	select * from tab	查看当前用户的所有表

http://www.cnoug.org/
关闭数据库		/admin/xxx/bdump/alert_xxx警告日志
shutdown normal	应用关闭选项后，不允许有新的连接，所有与数据库连接的用户必须在关闭数据库之前断开连接	1. 数据库被关闭后不允许新用户建立连接 2. 数据库被卸载后所有已经连接的用户可以继续工作，等待全部断开连接 3. 关闭数据库，卸载例程，释放内存，关闭例程
shutdown immediate	立即关闭，当前的客户机SQL语句立即终止	1. 不允许新用户建立连接 2. 立即关闭所有已经连接的用户 3. 回滚没有完成的事务 4. 关闭数据库，卸载例程，释放内存，关闭例程
shutdown abord	中止正在被Oracle数据库服务器处理的当前客户机SQL语句	1. 中止所有正在执行的SQL语句 2. 断开所有已经连接的用户 3. 立即中止例程 4. 不回滚未提交的事务。如果数据库在中止关闭后启动，oracle必须使用联机重做日志文件回滚未提交的事务。
shutdown transactional	提供一个指定的时间长度，关闭数据库前在该时间内完成事务处理，直到最后一个事务处理完成后才关闭数据库	1. 不允许新用户建立连接 2. 不允许数据库有新事务，如果用户企图开始新的事务，将自动断开会话 3. 等待用户回滚或者提交未完成的事务 4. 关闭数据库，卸载例程，把所有事务完成后关闭例程
启动数据库
startup	正常启动	1. 按照配置的参数分配内存、分配系统进程 2. 装载数据库 3. 打开数据库，正常启动后，用户可以连接数据库进行访问
startup nomount	不装载启动	使用配置的参数来分配内存空间和进程，未打开数据库。当DBA需要重新创建控制文件或数据库时必须将数据库切换到未装载状态
startup mount	装载启动	启动例程并装载数据库，但不打开数据库。用于更改数据库的归档状态或者执行恢复操作
startup force	强制启动	用于在其他方法无法正常打开数据库时，尝试启动数据库
归档模式
select member from v$logfile;	查看log文件位置
archive log list;
shutdown immediate;
startup mount;
alter database archivelog;	改为归档模式
alter database open;
archive log start;	启用归档
alter system switch logfile;
备份和恢复
	recover datafile 'xxx.dbf';
1.物理备份：
1）冷备份	直接拷贝数据文件，重做日志文件和控制文件
2）rman备份 recovery manager	在线增量备份
3）热备份-联机备份
2.逻辑备份
1）exp,imp
2) spool导出文本文件
3) sqlldr SQL Loader导入文本文件