Oracle小总结

浏览数：31 / 时间：2015年06月12日

关于Oracle的权限：

　　create table 创建表
create table A as select * from B;

alter table      add/modify/drop
alter table table_name set unused (column) column_name;

rename table_name to table_name2; 重命名

truncate table table_name ; 删除表数据
drop table_name 删除表结构

comment on table table_name is‘‘;   添加注释

创建用户：
create user user_name identified by password;
create user guoxiang identified by 123123;

create session\table\sequence\view\procedure   创建会话、表、序列、视图、过程

赋予系统权限
GRANT create session, create table, create sequence, create view TO scott;
创建角色：
create ROLE manager;
为角色赋予权限
GRANT create table,create view To manager;
将角色赋予用户
GRANT manager To DEHAAN,KOCHHAR;

DBA 可以创建用户和修改密码
用户本人也可以使用alter user 语句修改密码
alter user scott inentified by lion;

分配对象权限
分配表employees的查询权限
GRANT select ON    employees    TO        su,rich;
分配表中各个列的更新权限：
   GRANT update (department_name, location_id) ON departments TO scott,manager

WITH GRANT   OPTION 使用户同样具有分配权限的权限
GRANT    select ,insert ON departments TO scott WITH GRANT OPTION;
向数据库中所有用户分配权限
GRANT select ON alice.departments TO PUBLIC;

收回对象权限
使用revoke语句收回
使用with grant option子句所分配的权限同样被收回
REVOKE select, insert ON    departments FROM scott;

数据库联接
创建数据库连接
CREATE PUBLIC DATABASE LINK hp.acme.com USING ‘sales‘;
使用   sql访问远程数据库
SELECT * FROM [email protected];

多表查询：

外连接：使用外连接可以查询不满足连接条件的数据。
               外连接的符号是（+）
select table1.column,table2.column from table1,table2 where    table1.column(+)=table2.column;

select table1.column,table2.column from table1,table2 where table1.column= table2.column(+);

使用sql：1999语法连接
使用连接从多个表中查询数据
select table1.column, table2.column from table1
   [CROSS JOIN table2]   |
   [NATURAL JOIN table2]   |
   [JOIN    table2 USING (column_name)]   |
   [JOIN       table2
                   ON(table1.column_name = table2.column_name)]   |
                   [LEFT|RIGHT|FULL OUTER JOIN table2
                   ON (table1.column_name = table2.column_name)]‘
叉集       : 使用CROSS JOIN子句使连接的表产生叉集
自然连接： NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。
                   在表中查询满足等值条件的数据。
                   如果只是列名相同而数据类型不同，则会产生错误。
select department_id, department_name, location_id, city from departments NATURAL JOIN location;

使用USING子句创建连接
在   NATURAL   JOIN 子句创建等值连接时，可是使用USING子句指定等值连接中需要用到的列。
使用USING 可以在多个满足条件时进行选择。
不要给选中的列中加上表名前缀或者别名。
NATURAL JOIN    和 USING   子句经常同时使用；
select e.employee_id, e.last_name, d.location_id FROM employee e JOIN department d USING (department_id);
使用ON子句创建连接
自然连接是以具有相同名字的列为连接条件的。
可以使用ON子句指定额外的连接条件
这个连接条件是与其他条件分开的。
ON子句使语句具有更高的易读性。
select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
From employees e JOIN departments d
ON (e.department_id = d.department_id)

内连接和外连接
在SQL： 1999中，内连接只返回满足连接条件的数据。
两个表在连接过程中除了返回满足连接条件的行外还返回左（或右）表中不满足条件的行，这种连接叫做左（或右）连接外连接。
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接成为满外连接。
左外连接：
select e.last_name, e.department_id,d.department_name from employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
右外连接
select e.last_name, e.department_id,d.department_name from employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
满连接：
select e.last_name, e.department_id, d.department_name from employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
增加连接条件：
select e,last_name,e.department_id,d.deparment_name from employees e JOIN departments d
ON (e.empartment_id = d.empartment_id) AND manager_id = 149;

子查询：
SELECT select_list    FROM    table WHERE expr opertor
(SELECT select_list FROM   table);
子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询使用（外查询）；

select last_name from employees where salary > (select salary from employees where last_name = ‘ABel‘);
注意事项：
1、子查询要包含在括号内。
2、将子查询放在比较条件的右侧
3、除非进行Top-N分析，否则不要在子查询中使用ORDER BY子句。
4、单行操作对应单行子查询，多行操作对应多行子查询。

单行子查询：
只返回一行，
使用单行比较操作符
= \>\>=\<\<=\<>
多行子查询
返回多行、使用多行比较操作符。
in       等于列表中任何一个
any       和子查询返回的任意一个值比较
all   和子查询返回的所有值比较

DML 数据控制语言
插入数据修改数据删除数据
事务是由完成若干项工作的DML语句组成的。
Insert into table() values ();
这种方式只能插入一条数据;
字符和日期型数据用包含在单引号中
创建脚本：
在SQL语句中使用& 变量指定列值。
&变量放在VALUES子句中。
INSERT INTO departments(department_id,department_name,location_id) VALUES (&department_id,‘department_name‘,
&location_id);
从其他表中复制数据
INSERT INTO sales_reps(id,name,salary,commission_pct)
SELECT employee_id,last_name,salary,commission_pct FROM employees WHERE job_id like ‘_REp%‘;
不用书写values 列名要对应

更新数据：UPDATE table set column = value WHERE ^

UNION 操作符返回的是两个查询的结果的并集
select employee_id,job_id from employees
UNION
select employee_id,job_id from job_history;
UNION ALL返回的是两个查询结果的并集以及两个结果的重复部分(不去重)
select employee_id,job_id from employees
UNION ALL
select employee_id,job_id from job_history;

INTERSECT 操作符返回两个结果集的交集
select employee_id,job_id from employees
INTERSECT
select employee_id,job_id from job_history;
MINUS返回两个结果集的补集
select employee_id,job_id from employees
MINUS
select employee_id,job_id from job_history;