Oracle数据库创建用户、授权、多表联合查询、语句调优
1. 创建用户和授权
其中的路径一定要存在
/*分为四步 */
/*第1步：创建临时表空间 */
create temporary tablespace user_temp
tempfile 'C:\app\user10\oradata\orcl\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第2步：创建数据表空间 */
create tablespace user_data
logging
datafile 'C:\app\user10\oradata\orcl\user_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第3步：创建用户并指定表空间 */
create user xpf identified by 123456 //创建xpf用户，密码为 123456
/*
create user 用户名 identified by 密码;//在管理员帐户下，创建用户
alert user scott identified by tiger;//修改密码
*/
default tablespace user_data
temporary tablespace user_temp;
/*如果临时表空间和用户表空间存在，则省略第一第二步*/
grant 权限列表，.. to username [with admin option 同时获得权限分配权];
revoke 权限列表，.. from usernam;
a.常见的系统权限
CREATE SESSION 创建会话
CREATE SEQUENCE 创建序列
CREATE SYNONYM 创建同名对象
CREATE TABLE 在用户模式中创建表
CREATE ANY TABLE 在任何模式中创建表
DROP TABLE 在用户模式中删除表
DROP ANY TABLE 在任何模式中删除表
CREATE PROCEDURE 创建存储过程
EXECUTE ANY PROCEDURE 执行任何模式的存储过程
CREATE USER 创建用户
DROP USER 删除用户
//grant CREATE TABLE to xpf 赋予xpf用户创建表的权限
//revoke CREATE TABLE from xpf 取消xpf用户创建表的权限
//grant select, insert on emp to xpf 给xpf用户赋予emp表查询、插入权限
//revoke insert on emp from xpf 撤销给xpf用户针对emp表的插入权限
2. 多表联合查询
-
外连接
//左外连接（左边的表是主表）：left outer join...on select * from emp LEFT OUTER join dept on emp.deptno=dept.deptno; //右外连接（右边的表示主表）：right outer join...on select * from emp RTGHT OUTER join dept on emp.deptno=dept.deptno; //全连接 select * from emp FULL OUTER join dept on emp.deptno=dept.deptno; -
左右连接
/* 左连接：左边的表示主表，右连接：右边的表示主表 如果（+）在右边，是左连接（左边的表示主表）； 如果（+）在左边，是右连接（右边的表示主表） */ select e.ename,e.job,em.ename from emp e,emp em where em.empne = e.mgr(+); -
交叉连接
//用于产生笛卡尔积 select * from emp cross join dept; 等同于： select * from emp,dept -
自然连接(有约束条件时使用)
//自动寻找两/多个表与表之间的外键关联关系（消除笛卡尔积） select * from emp NATURAL join dept; 等同于： select * from empt,dept where emp.deptno = dept.deptno -
JOIN USING子句，相同字段名称可以使用
select * from emp join dept using (deptno); //用户自己指定一个消除笛卡尔积的关联字段 -
JOIN ON子句，不相同字段名称也可以使用
select * from emp join dept on (emp.deptno = dept.deptno); //用户指定一个消除笛卡尔积的关联条件 -
统计函数
/* 在之前学习过一个count()函数，此函数的功能可以统计出表中的数据量，实际上这个就是一个统计函数，而常用的统计函数有如下几个： COUNT():查询表中的数据记录 AVG():求出平均值 SUM():求和 MAX():求出最大值 MIN():求出最小值 练：统计出公司的所有雇员，每个月支付的平均工资及总工资 */ select trunc(AVG(sal)) avg, sum(sal) from emp; -
分组查询
/* 首先必须先明确一点，什么情况下可能分组，例如： >>公司的所有雇员，要求男性一组，女性一组，之后可以统计男性和女性的 数量 >>按照年龄分组，18岁以上的分一组，18岁以下的分一组 >>按照地区分组：北京人一组，上海人一组，四川人一组 如果这些信息都保存了数据库之中，肯定在数据的某一列上回存在重复的内 容，例如；按照性别分组的时候，性别肯定有重复（男和女），按照年龄分组 （有一个范围的重复），按照地区分组（有一个地区的信息重复） 所以，分组之中有一个不成文的规定：当数据重复的时候分组才有意义，以 为一个人也可以一组（没什么意义） 分组查询语句（group by 放在最后写）： 如果要对分组后的数据进行再次过滤，则使用having子句完成 select ... from ... where ... group by ... having 分组后的过滤条件（可以使用统计函数） order by 排序字段 ASC|DESC */ //where和having的区别 //where：是在执行group by操作之前进行的过滤，表示从全部数据中筛选出部分的数据，在where之中不能使用统计函数。 //having：是在group by分组之后的再次过滤，可以在having子句中使用统计函数 /** 写的顺序： select...from...where...group by...having...order by.. 执行顺序： from...where...group by...having...select...order by.. */
3. 语句调优
-
where条件中语句的放置顺序：
Oracle是自后向前解析，所以要把能过滤掉最大记录数的条件放在最后面。例如“主键ID=？”这样的条件。
-
select语句中不要使用*：
Oracle在解析时，会把 * 转换成所有的列名，这个工作是通过查询数据字典完成的，这会耗费更多的时间。
-
使用索引：
使用索引的好处：可提高查询的效率
使用索引的坏处：索引需要空间来存储，也需要花费代价来维护；每当有记录增减时，索引本身也需要被修改，所以不必要的索引反而使反应时间变慢。
使用索引需要注意的地方：
1）避免在索引列上使用NOT或函数
Not的副作用和函数相同，Oracle系统发现Not，就会停止使用索引 而执行全表扫描。
2）避免在索引列上使用计算
例如：我们对sal列建立了索引
低效：select … from dept where sal * 12 < 25000;
高效：select … from dept where sal < 25000 / 12;
如果对索引列使用计算，Oracle将不使用索引而使用全表扫描 3）避免在索引列上使用is null或is not null
不要对可以为空的列建立索引。
如果我们对可以为空的列建立了索引，那么Oracle的索引对于该条 为null的记录将不起作用：
a) 对于单列索引，如果包含null值，索引中将不存在此记录
b) 对于多列索引，如果至少有一列不为null, 则该条记录将记录在索 引中，且Oracle不允许插入重复的记录。
例如，A,B两列作为索引，表中存在一条记录(123, null)，Oracle将 不再接受下一条记录(123, null). 但如果表中已有记录(null, null), 则 Oracle允许下一条记录(null, null)，因为Oracle规定null != null.
一句话概括，null值不会存储在索引中。
低效：（索引失效）：select … from dept where dept_code is not null;
高效：select … from dept where dept_code >= 0; 4) 使用通配符%会使Oracle索引失效
低效：（索引失效）：select … from dept where dept_code like ‘%12345%’;
高效：select … from dept where dept_code like ‘12345’; 5) 避免改变索引的类型：
如果dept_code是数值型
select … from emp where empno = ‘123’;
实际上，Oracle会转换成：select … from emp where empno = TO_NUMBER(‘123’), 这样幸运的是，转换函数没有发生在索引列 上，所以索引仍然有效。
但对于：emp_type是varchar2类型的时候：
select … from emp where emp_type=123 将被换换成： select … from emp where TO_NUMBER(emp_type)=123; 这时索引将失 效，因为对于Oracle, 当字符和数值比较时，会优先将字符型转成数 值型。 6）如果查询的数据量超过全表数据的30%, 那么即使使用索引也没有 显著的提高
除了使用索引，我们还有其他能减少资源消耗的方法：
-
用exist替换distinct
低效：select distinct dept_no, dept_name from dept d, emp e where d.dept_no=e.dept_no and e.sex=man;
高效：select dept_no, dept_name from dept d where exist (
select ‘X’ from emp e where e.dept_no=d.dept_no and e.sex=man
);
对于"一对多"的表,如（部门表和员工表），exist效率更高，因为oracle将在子查询条件一旦满足后，立刻返回结果。 -
用(union) union all 替换 or（只适用于多个索引列）
低效：select loc_id, loc_desc, region from location where loc_id=10 or region=‘beijing’;
高效：select loc_id, loc_desc, region from location where loc_id=10 union all
select loc_id, loc_desc, region from location where region=‘beijing’;当loc_id和retion是联合索引的时候，如果使用or，索引将失效。
-
order by要加在索引列上，最好是主键上
例如：dept_code是索引列
低效：select dept_code from dept order by dept_type;
高效：select dept_code from dept order by dept_code; -
避免使用耗费资源的操作
如果SQL中带有distinct, union, minus, intersect，则会启动SQL引擎，执行耗费资源的排序功能。
-
使用where替代having（如果可以的话）
低效：select job, avg(sal) from emp group by job having job=‘aaa’ and avg(sal)>100;
高效：select job, avg(sal) from emp where job=‘aaa’ group by job having avg(sal)>100;
在执行group by之前，先用where过滤掉一部分记录。 -
尽量避免使用子查询
因为子查询的代价是比较昂贵的。
-
2502
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
