oracle学习笔记(2)-SQL基础

1.DDL:create;alter;drop;audit;noaudit;revoke;grant;
2.DML:insert;update;delete
3.DQL:select
4.事务控制:将一组DML操作组合起来,形成一个事务并进行事务控制.事务提交(COMMIT),事务回滚(ROLLBACK),设置保存点(SAVEPOINT),设置事务状态(SET TRANSACTION)
5.系统控制:设置数据库系统参数,ALTER SYSTEM 
6.回话控制:设置用户会话相关参数

1).查询:
SELECT [ALL|DISTINCT]column_name[,expression…]
FROM  table1_name[,table2_name,view_name,…]
[WHERE condition]
[GROUP BY column_name1[,column_name2,…] 
[HAVING group_condition]]
[ORDER BY column_name2 [ASC|DESC]

[,column_name2,…]];

2).条件运算:

= ; < ; > ; <= ; >= ; < >; !=;

between...and...;not between...and...;
in; not in;
like; not like;
is null;is not null;

and; or;

3).统计函数:

    平均值(AVG);总个数(COUNT);总数(SUM);最大值(MAX);最小值(MIN);标准差(STDDEV);方差(VARIANCE);
    聚集函数只能出现在目标列表达式、ORDER BY子句、HAVING子句中，不能出现在WHERE子句和GROUP BY子句中。
4).分组查询:
group by ...
group by(column1,column2)
group by ROLLUP(column1,column2)------生成横向统计和不分组统计
group by CUBE(column1,column2)--------可以生成横向统计、纵向统计和不分组统计
group by GROUPING SETs(column1,column2)-将几个单独的分组查询合并成一个分组查询
5).连接查询:
查询过程: 1.首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，
2.找到后就将表1中的第1个元组与该元组拼接形成结果表中的一个元组。
3.表2全部找完后，再找表1中的第2个元组，然后再从头扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第2个元组与该元组拼接形成结果表中的一个元组。
4.重复执行，直到表1中的全部元组都处理完毕为止.
内连接: select table1.column1, table2.column2 from table1 [inner] join table2 on condition where ...;
select table1.column1, table2.column2 from table1, table2 where condition;
自身内连接:SELECT worker.empno,worker.ename, manager.empno, manager.ename FROM emp worker JOIN emp manager ON worker.mgr=manager.empno;
左连接
有链接
内连接
6).子查询:
in ----------------与子查询任意一个结果相等;
not in ------------与子查询结果都不等;
>any --------------大于子查询任意一个结果;
=any --------------等于子查询任意一个结果;
<any --------------小于子查询任意一个结果;
>all --------------大于子查询的所有结果;
<all --------------小于子查询的所有结果;
exists ------------子查询至少返回一个结果为TRUE;
not exists --------子查询没有返回结果为TRUE
例子:查询没有任何员工的部门号、部门名。
SELECT deptno,dname,loc FROM dept WHERE NOT EXISTS(SELECT * FROM emp WHERE emp.deptno=dept.deptno);
7).层次查询:又称树形查询，能够将一个表中的数据按照记录之间的联系以树状结构的形式显示出来.
SELECT [LEVEL], column[,expression...] FROM table [WHERE condition] [START WITH column = value] [CONNECT BY condition]; 
参数说明，
LEVEL：伪列，表示记录的层次；
WHERE：记录（节点）选择条件；
START WITH：层次查询的起始记录（起始节点）
CONNECT BY：指定父记录与子记录之间的关系及分支选择条件。必须使用PRIOR引用父记录，形式为PRIOR column1=column2或column1=PRIOR column2。
例子:利用分级查询显示emp表中员工与领导之间的关系（从高到低）。
SELECT empno,ename,mgr FROM emp START WITH empno=7839 CONNECT BY PRIOR empno=mgr;
8).合并查询:
SELECT query_statement1 [UNION|UNION ALL|INTERSECT|MINUS] SELECT query_statement2;
合并的各子查询必须拥有相同的列和数据类型;
排序只能在最后一个查询后面用order by;
UNION:合并去重;
UNION ALL:合并保留所有记录;
INTERSECT:返回几个查询的交集;
MINUS:返回查询之间的差集(比如第一个存在但在第二个不存在);
9).数据操纵:
a.插入数据:INSERT INTO table_name|view_name [(column1[,column2…])] VALUES(value1[,values, …]);

b.利用子查询插入数据:INSERT INTO table_name|view_name [(column1[,column2,…]) subquery;

例子:向emp表中插入一行记录，其员工名为FAN，员工号为1235，其他信息与员工名为SCOTT的员工信息相同。

INSERT INTO emp SELECT 1235, 'FAN',job,mgr,hiredate,sal,comm,deptno FROM emp WHERE ename='SCOTT';

复制emp表中empno，ename，sal，deptno四列的值，并插入到new_emp表中，语句为：

INSERT /*+APPEND*/ INTO new_emp(empno,ename,sal,deptno) SELECT empno,ename,sal,deptno from emp; 

c.有条件多表插入语法为
INSERT ALL|FIRST WHEN condition1 THEN INTO table1(column1[,…]) WHEN condition2 THEN INTO table2(column1[,…]) …… ELSE INTO tablen(column1[,…]) subquery;
将emp表中的员工信息按不同部门号分别复制到emp10，emp20，emp30和emp_other表中。 
INSERT FIRST WHEN deptno=10 THEN INTO emp10 WHEN deptno=20 THEN INTO emp20 WHEN deptno=30 THEN INTO emp30 ELSE INTO emp_other SELECT * FROM emp;
d.利用MERGE语句更新数据:
利用MERGE语句可以同时完成数据的插入与更新操作。
将源表的数据分别与目标表中的数据根据特性条件进行比较（每次只比较一条记录），如果匹配，则利用源表中的记录更新目标表中的记录，如果不匹配，则将源表中的记录插入目标表中。
使用MERGE语句操作时，用户需要具有源表的SELECT对象权限以及目标表的INSERT，UPDATE对象权限。
MERGE语句的基本语法为：
MERGE INTO [schema.]target_table [target_alias] 
USING [schema.]source_table|source_view|
source_subquery [source_alias]
ON (condition)
WHEN MATCHED THEN UPDATE SET
column1=expression1 
[,column2=expression2 …]
[where_clause][DELETE where_clause]
WHEN NOT MATCHED THEN 
INSERT [(column2[,column2…])] 
VALUES  (expresstion1[,expression2…]) 
[where_clause];
参数说明
INTO：指定进行数据更新或插入的目标表；
USING：指定用于目标表数据更新或插入的源表或视图或子查询；
ON：决定MERGE语句执行更新操作还是插入操作的条件。对于目标表中满足条件的记录，则利用源表中的相应记录进行更新；而源表中不满条件的记录将被插入目标表中；
where_clause：只有当该条件为真时才进行数据的更新或插入操作；

DELETE where_clause：当目标表中更新后的记录满足该条件时，则删除该记录。 

例子:使用“雇员”表（别名e）作为数据源，在该表 的副本“副本_雇员”表（别名c）中插入并更新行。
MERGE INTO 副本_雇员 c USING 雇员e
ON (c.雇员标识= e.雇员标识)
WHEN MATCHED THEN UPDATE
SET
c.姓氏 = e.姓氏,
c.部门标识 = e.部门标识
WHEN NOT MATCHED THEN INSERT 
VALUES (e.雇员标识, e.姓氏, e.部门标识);

e.删除数据

TRUNCATE TABLE table_name;

TRUNCATE 和 DELETE的区别:

释放存储空间;

不写入日志文件，因此执行效率较高，但该操作不可回滚。

10).事务控制:
四大特性:ACID(原子性,一致性,隔离性,持久性);
隔离级别:
Oracle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。所以Oracle不支持脏读;
SQL标准所定义的默认事务隔离级别是SERIALIZABLE，但是Oracle 默认使用的是READ COMMITTED.
如果数据库中具有大量并发事务，并且应用程序的事务处理能力和响应速度是关键因素，则READ COMMITED隔离级比较合适。
如果数据库中多个事务并发访问数据的概率很低，并且大部分的事务都会持续执行很长时间，这时应用程序更加适合使用SERIALIZABLE隔离级。
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL =READ COMMITTED;
事务提交:用户显式commit;
当执行CREATE，ALTER，DROP，RENAME，REVOKE，GRANT，CONNECT，DISCONNECT等命令时，系统将自动提交
事务回滚方式
事务全部回滚 ROLLBACK
事务部分回滚 SAVEPOINT X; ROLLBACK TO X