oracle基础

创建表空间
create tablespace itheima
datafile ‘c:\itheima.dbf’ 放置位置
size 100m 放置空间大小
autoextend on 自动扩展
next 10m 自动扩展的大小

删除表空间
drop tablespace itheima

创建用户
create user itheima
identified by itheima 用户密码
default tablespace itheima 为用户指定表空间

给用户授权
oracle数据库中常用角色
connect 连接角色 基本角色
resource 开发者角色
dba 超级管理员角色
给itheia用户授权
grant dba to itheima

数据类型
varchar varchar2 表示一个字符串 varchar如果存放多余可以自动

截取
number number表示一个整数
DATA 表示日期类型
CLOB 大对象 存放大文本数据类型 存4g
BLOB 大对象 存放二进制数据 存g

创建一个person表 在用户下面创建了一个表
create table person(
pid number(20),
pname varchar2(10)
);

修改表结构
添加一列 (叫gener 往table里面添加)
alter table person add (genter number(1));
修改列类型
alter table person modify genter char(1);
修改列名称
alter table person rename column genter to sex; 将

列名修改成sex
删除一列
alter table person drop colunm sex; 删除sex这一

列

添加一条记录
insert into person (pid,pname) value (1,小明);
commit; 事务!! (增删改需

要加事务 查不需要)

修改一个记录
update person set pname = ‘小马’ where pid =1;

三个删除
删除表中全部记录
delete from person;
删除表结构
drop table person;
先删除表 再次创建表,效果等同于删除表中全部记录
数据库量大的情况下, 尤其在表中带有索引的情况下,该操作效率

高.
索引可以提供查询效率,但是会影响增删改效率.
truncate table person;

序列: 默认从1开始 依次递增 主要用来给主键赋值使用. sequence!!
dual: 虚表 只是为了补全语法 没有任何意义.

create sequence s_person; 给person表添加序列
select s_person.nextval from dual; 序列下一个值

填加一条记录
insert into person (pid,pname) values (s_person.nextval,小明);
commit;

[increate by n] 每次增加多少

scott用户 密码tiger
解锁scott用户
alter user scott account unlock;
解锁scott用户的密码 [此句也可以用来重置密码]

单行函数 作用于一行 返回一个值
字符函数
select upper (‘yes’) from dual; --YES
select lower (‘YES’) from dual; --yes

数值函数
	select round(56.16,-2) from dual; 四舍五入 后面		

	参数表示保留的位数 100 
	select trunc(56.16,-1)from dual;直接截取 不再看		

	后面的位数是否大于5.  50
	select mod (10,3) from dual;  求余. 1
日期函数
	查询emp表中所有员工如何距离现在有几天?
		select sysdate-e.hiredate from emp e;
	算出明天此刻
		select systdate+1 from dual;
	查出emp表中所以员工入_职距离现在几月
select months_between(sysdate,e.hiredate) from emp e;	
	查出emp表中所以员工入_职距离现在几年
elect months_between(sysdate,e.hiredate)/12 from emp e;
	查出emp表中所以员工入_职距离现在几周	
select (sysdate,e.hiredate)/7 from emp e;	

转换函数

日期转字符串	
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss')from 

dual; fm是为了将月份前面的0去掉 hh后面24 是为了24小时制
字符串转日期
select to_char(‘2018-6-7 16:39:50’,'fm yyyy-mm-dd

hh24:mi:ss’)from dual;

通用函数
算出emp表中所有员工的年薪
奖金里面有null值. 如果null值和任意数字做算数运算,结果都是

null
select e.sal*12+nvl(e.comm,0)from e; nvl 函数如果括号

里的为空 就选0 如果不为空就选0!!!

条件表达式(等值判断)(大小判断)
给emp表中员工起中文名
select e.ename
case e.name
when ‘smiht’ then’张三’
when ‘ALLen’ then ‘李四’
else ‘王五’ else可以省略 默认空
end
from emp e;

判断emp表中员工工资,如果高于3000 显示高收入 1500到3000显示

中等收入 其余显示低收入
select e.ename
case
when e.sal>300 then’高收入’
when e.sal>1500 then ‘中等收入’
else ‘低收入’ else可以省略 默认空
end
from emp e;

oracl专用表达式 (decode!!!)
select e.ename,
decode ( e.name
‘smiht’ ,‘张三’
ALLen’ ,‘李四’
‘王五’ )
from emp e;

多行函数[聚合函数] 作用于多行 返回一个值!!!
select count(1) from emp; 查询总数量 x
select sum(sal) from emp; 工资总和
select max(sal) from emp; 最大工资
select min(sal) from emp; 最低工资
select avg(sal) from emp; 平均工资

分组查询
查询出每个部门的平均工资
分组查询中,出现在group by 后面的原始列,才能出现在select后面
没有出现在group by后面的列,现在select后面出现,必须加上聚合数
聚合函数有一个特性 可以把多行记录变成一个值

查询出每个部门的平均工资
select e.deptho,avg(e.sal)
from emp e
group by e.deptho
查询出平均工资高于2000的部门信息
select e.deptho,avg(e.sal) assl sasl是avg(e.sal)列名
from emp e
group by e.deptho
having avg(e.sal)>2000; having!!!
所有条件都不能用别名判断!!!
查出每个部分工资高于800的员工的平均工资
select e.deptho,avg(e.sal)
from emp e
where e.sal>800
group by e.deptho
where是过滤分组前的数据,having是过滤分组后的数据.!!!
表现形式: where必须是在group by之前 having是在group by之后

多表查询中的概念
笛卡尔积
select *
from emp e,dept d;
等值连接
select *
from emp e,dept d
where e.deptno=d.deptno;

内连接
	select *
	from emp e innerjoin dept d
	on e.deptno=d.deptno

!!!查询出所有部门,以及部门下的员工信息[外连接分为左外右外]
select *
from emp e right join dept d
on e.deptno=d.deptno
查询所以员工信息,以及所有部门
select *
from emp e right join dept d
on e.deptno=d.deptno

!!!外连接 分为左右外连接 分别以左右表为基准!!!
oracle中专用外连接
select *
from * emp e,dept d
where e.deptno(+)=d.deptno

自连接 就是站在不同的角度把一张表看成多张表
查询出员工姓名 员工领导姓名
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno

子查询 !!!分开查询 先查前面工资和scott一样的再查员工
子查询返回一个值
查询出工资和scott一样的员工信息
select * from emp where sal in
(select sal from emp where ename = ‘scott’)
子查询返回一个集合
查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in
(select sal from emp where demp=10)

oracle中的分页
rownum行号:当我们做select操作的时候
每查出一行记录,就会在该行上加上一个rownum行号
行号从1开始,依次递增 不能跳着走

排序操作会影响rownum的顺序
select rownum ,e.* from emp e order by e.sal desc; !!! 

如果涉及到排序,但是还要使用rownum的话,我们可以再次嵌套查询!!!
select rownum t.* from(
select rownum ,e.* from emp e order by e.sal desc) t; !!!

emp表工资倒叙排列后,每页五条记录,查询第二页.
rownum行号不能写上大于一个正数!!!rownum
select * from(
select rownum rn,t.* from (
select * from emp order by sal desc desc
)t where rownum <11
) where rn>5;

视图
视图的概念: 视图就是提供一个平板查询的窗口,所有数据来自于原表.

查询语句创建表
create table emp as select * from scott.emp
select * from emp;
创建视图
create view v_emp as select ename,job from emp;!!!
查询视图
select * from v_emp;
修改视图
update v_emp set job=‘cleck’ where ename=‘ALLEN’;
commit;
创建只读视图
create view v_emp1 as select ename ,job from emp with read

only ;
视图的左右?
第一:视图可以屏蔽一些敏感的数据
第二:保证总部和分部的数据及时统一.

索引
索引的概念:索引就是在表的(列上!!!)构建一个二叉树
达到大幅度提高查询效率的目的,但是索引会影响增删改查的效率.

单列索引
创建单列索引
create index ind_ename on emp(ename);
单列索引触发规则,条件必须是索引中的原始值.
单行函数,模糊查询,都会影响单列索引的触发
select * from emp where ename=‘scott’ scott为原始值 所以触发!

复合索引
创建复合索引
create index ind_ename job on emp (ename,job);
复合索引中第一列为优先检索列
如果要触发复合索引,必须包含有优先检索列的中的原始值.
select * from emp where ename=‘scott’ and job=‘xx’ 触发符合索引
select * from emp where ename=‘scott’ 触发单列索引
select * from emp where ename=‘scott’ or job='xx’不触发索引