oracl

---------------------------表空间---------------
1、创建表空间+创建数据文件
create tablespace syd_space --表空间名称
datafile ‘C:\syd.dbf’ --表空间对应的数据文件
size 10M --初始大小
autoextend on --设置自动扩展
next 1M --自动扩展大小

2、创建用户
create user syd --用户名称
identified by syd --设置密码
default tablespace syd_space --用户默认操作的表空间

3、给用户赋权限
select * from session_privs;–查询当前用户的权限
Oracle提供了3个角色
dba resource connect
grant dba to syd --把dba的角色赋给了syd用户

4、创建表
create table 表名(
字段名 数据类型(长度)
)

---------Oracle常见的数据类型-----
字符型
char --255 --固定长度 name char(10) ‘tom’
varchar2 --3999 --不固定长度 name varchar2(10) ‘tom’
数值型
number

money number(3) 999
money number(3,2) --最大值 999.99 错！！！ 正确 9.99 -9.99 总长度不包括小数点和负号
money number 默认长度 8
日期型
date --相当于mysql中datetime
timestamp --时间戳 可以精确到秒小数点后9位
大数据类型
long --2G 相当于mysql中longtext
clob --4G
blob --4G

-----------------约束--------------------
– 单表约束–
主键 非空 唯一 检查(check)
drop table person;

create table person(
pid number(8),
name varchar2(10) not null,
tel varchar2(20),
gender number(1), --0女 1男
constraint pk_person_pid primary key(pid),
constraint unique_person_tel unique(tel),
constraint check_person_gender check(gender in(0,1))
)
insert into person values(1,‘小潘’,‘13812345678’,0);
insert into person values(2,‘小潘’,‘13812345679’,0);
insert into person values(3,‘大郎’,‘13812345670’,1);
insert into person values(4,‘大官人’,‘13812345672’,1);
select * from person;

----外键约束
orders --订单表
create table orders(
oid number(9) primary key,
totalprice number(9,2)
)

orderdetail–订单详情表
create table orderdetail(
odid number(9) primary key,
name varchar2(60),
price number(9,2),
oid number(9),
constraint fk_orders_oid foreign key(oid) references orders(oid)
)
select * from orders
insert into orders values(1,100);
insert into orders values(2,1000);

insert into orderdetail values(1,‘鼠标’,50,1);
insert into orderdetail values(2,‘键盘’,50,3);
delete from orders where oid=2

------修改表 表结构
–增加列
alter table person add(address varchar2(10))
–修改列的长度
alter table person modify(address varchar2(100))
–删除列
alter table person drop column address

-----删除表
drop table orderdetail

---------------DML操作数据--------------
–快速建表 scott下面emp dept
create table myemp as select * from scott.emp
create table mydept as select * from scott.dept
select * from myemp
select * from mydept
–修改数据
给NEW YORK地区的所有员工涨100工资
1、查询NEW YORK地区有哪些部门
select * from mydept where loc=‘NEW YORK’;
2、获取所有NEW YORK地区的员工
select * from myemp where deptno in (select deptno from mydept where loc=‘NEW YORK’)
3、修改工资
update myemp set sal=sal+100 where deptno in (select deptno from mydept where loc=‘NEW YORK’)

–删除数据
删除NEW YORK地区的员工
delete from myemp where deptno in (select deptno from mydept where loc=‘NEW YORK’)

–删除表中所有数据
delete from myemp和truncate table myemp
–delete all和truncate区别
1、delete只是删除数据，truncate把表摧毁又重新创建了
2、delete有可能产生磁盘碎片，truncate不会
3、delete可以回滚，truncate不可以

insert into myemp(empno) values (1)
insert into myemp(empno) values (2)
commit;
rollback;

-----------------事务保存点-------
电商项目，用户注册
点注册提交按钮
后台
1、日志记录 insert log
2、user表插入数据
3、邮箱中发送邮件
邮件发送失败事务回滚到第2步

select * from myemp for update

update myemp set ename=‘TOM’ where empno=1;
savepoint a;
update myemp set ename=‘JERRY’ where empno=1;
savepoint b;
delete from myemp where empno=1;
rollback to b; --回顾到b时的状态，ename='JERRY’时
rollback to a; --回顾到a时的状态，ename='TOM’时

rollback; --回到上次事务提交时的状态

-----------------------------数据库的其他对象---------------------------------
-----------视图 view------
视图是什么：视图是一个虚表
视图有什么好处
1、可以封装复杂的sql语句
2、可以隐藏敏感列
如何使用
create [or replace] view 视图名称 as sql查询语句

create or replace view v_hire_num as
select sum(counts) “Total” ,
sum(decode(years,‘1980’,counts)) “1980”,
sum(decode(years,‘1981’,counts)) “1981”,
sum(decode(years,‘1982’,counts)) “1982”,
sum(decode(years,‘1987’,counts)) “1987”
from
(select to_char(hiredate,‘yyyy’) years,count(*) counts from myemp group by to_char(hiredate,‘yyyy’)) t

select * from v_hire_num

create or replace view v_emp as
select empno,ename,job,mgr,hiredate,deptno from myemp;
–视图也可以和其他表关联
select * from v_emp e,mydept d where e.deptno=d.deptno

select * from v_emp
update v_emp set ename=‘AAAA’ where empno=7369
select * from myemp

–创建视图，大多数创建的是只读视图
语法：
create or replace view 视图名
as sql查询语句 with read only

create or replace view v_emp2 as
select empno,ename,job,mgr,hiredate,deptno from myemp with read only
update v_emp2 set ename=‘BBBB’ where empno=7369–不让修改

------------------序列 sequence--------------
序列是什么
是独立于表之外的一个对象
序列有什么用
主要用来产生主键自增
如何使用
创建序列的语法
简单语法 create sequence 序列名
create sequence seq_ordres;
select seq_ordres.nextval from dual
select seq_ordres.currval from dual

insert into orders values(seq_ordres.nextval,100);

select * from orders

insert into myemp(empno) values(seq_ordres.nextval);

select * from myemp
—一般单表单序列使用

–创建序列复杂语法 1 3 5 7 9 11 13 15
create sequence seq_test
start with 1 --从N开始，默认值1
minvalue 1 --最小值 ，默认值1
maxvalue 15 --最大值，默认值19个9 9999999999999999999
increment by 2 --步增值，默认值1
cycle --循环，默认是nocycle
cache 5 --缓存N，缓存N个数据，默认是20
目前seq_test产生的数据是1 3 5 7 9 11 13 15 1 3 5 7 。。。。

select seq_test.nextval from dual

----------索引 index---------
什么是索引
索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构
有什么用
为了提高查询效率
怎么使用
语法：
create index 索引名称 on 表名(列名)
myemp经常会用ename做为查询条件
select * from myemp where ename=‘SCOTT’
create index index_myemp_ename on myemp(ename)
select * from myemp where ename=‘SCOTT’

一张表的主键默认就会产生索引

----测试索引
创建一个表
create table student(
sid number(10),
sname varchar2(30)
)
表放500W条数据
begin
for i in 1…5000000
loop
insert into student values(i,‘测试数据’||i);
end loop;
commit;
end;

select count(1) from student
查询其中的一条数据记录查询时间
select * from student where sname=‘测试数据4888888’–0.843
创建索引
create index index_student on student(sname)
查询其中的一条数据记录查询时间
select * from student where sname=‘测试数据4889888’–0.047

–创建索引总结
1、如果一个或者多个字段经常被查询时间，可以创建索引
2、如果一个表增加或删除或修改比查询操作多时，不建议创建索引
3、如果一个表数据量比较少时，没必要创建索引

–复合索引
create index index_myemp_job_ename on myemp(job,ename);
select * from myemp where job=’’ and ename=’’

---------同义词 synonym-------(了解)
作用：缩短对象的名称
语法：
create [public] synonym 同义词名称 for 其他对象

create public synonym d for scott.dept;

select * from d

drop public synonym d

--------------------数据库的导入导出---------------------
exp–导出
imp–导入
使用exp imp命令 电脑上需要安装oracle数据库

1、导出整个库

2、按照用户
场景：把scott用户下的所有对象导入到syd用户下
dos命令窗口
exp scott/tiger@192.168.5.10:1521/orcl file=c:\scott.dmp
imp syd/syd@192.168.5.10:1521/orcl file=c:\scott.dmp full=y

3、导出某些表

-------------小结--------------
1、创建表空间、创建用户、赋权限（语句保存起来）
2、操作表结构
create(重点)
alter
drop
3、操作数据
insert into
update
delete
4、事务、事务保存点（了解）

5、数据库其他对象
视图(重点)
序列(重点) 是oracle独有的
索引(重点)
索引(重点)
同义词(了解)
6、导出导入 （语句保存起来）

–范例：查询员工表和部门表
select * from emp;–14
select * from dept;–4
–笛卡尔积 对于sql查询没有意义
select * from emp e,dept d;–56=14*4
select * from emp e,dept d where e.deptno=d.deptno

–范例：查询出雇员的编号，姓名，部门的编号和名称，地址
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno

–范例：查询出每个员工的上级领导
–(员工编号、员工姓名、员工部门编号、员工工资、领导编号、领导姓名、领导工资)
select e1.empno,e1.ename,e1.deptno,e1.sal,e2.empno,e2.ename,e2.sal from
emp e1,–员工表
emp e2–领导表
where e1.mgr=e2.empno

–范例: 在上一个例子的基础上查询该员工的部门名称
select e1.empno,e1.ename,e1.deptno,e1.sal,d.dname,e2.empno,e2.ename,e2.sal from
emp e1,–员工表
emp e2,–领导表
dept d
where e1.mgr=e2.empno and e1.deptno=d.deptno

–范例：在上一个例子的基础上查询出每个员工编号，姓名，部门名称，
– 工资等级和他的上级领导的姓名，工资等级
select * from salgrade

select e1.empno,e1.ename,e1.deptno,e1.sal,d.dname,s.grade,e2.empno,e2.ename,e2.sal,s1.grade from
emp e1,–员工表
emp e2,–领导表
dept d,
salgrade s,
salgrade s1
where e1.mgr=e2.empno and e1.deptno=d.deptno and e1.sal between s.losal and s.hisal
and e2.sal between s1.losal and s1.hisal

--------------------外链接---------
left join on right join on
–范例：查询出所有员工的上级领导
select e1.empno,e1.ename,e1.deptno,e1.sal,e2.empno,e2.ename,e2.sal from
emp e1,–员工表
emp e2–领导表
where e1.mgr=e2.empno(+)
emp e1,–员工表 全量表
emp e2–领导表 非全量表 (+)

–范例：查询出所有的部门下的员工，要求把没有员工的部门也展示出来

select * from emp;–非全量表
select * from dept;–全量表

select * from dept d,emp e where e.deptno(+)=d.deptno;

select * from dept d left join emp e on e.deptno=d.deptno;

–范例：查询每个部门员工数量，显示部门名称
–select后面出现的物理列，group by后面也必须出现
select d.deptno,d.dname ,count(e.deptno) from dept d,emp e
where e.deptno(+)=d.deptno group by d.deptno,d.dname

-------------------子查询-------------
–查询比SCOTT工资高的员工
select * from emp where sal>(select sal from emp where ename=‘SCOTT’)

–查询职位是MANAGER并且工资比7782号员工高的员工
select * from emp where job=‘MANAGER’ and sal>(select sal from emp where empno=7782)

–查询工资最低的员工
select min(sal) from emp
select * from emp where sal=(select min(sal) from emp)

–查询每个部门最低工资 大于30号部门最低工资 的结果
select e.deptno, min(sal)
from emp e
group by e.deptno
having min(sal) > (select min(sal) from emp where deptno = 30)

–查询出和scott同部门并且同职位的员工
select * from emp where (deptno,job)=(select deptno,job from emp where ename=‘SCOTT’)
–and ename<>‘SCOTT’
and ename!=‘SCOTT’

–查询每个部门的最低工资 和最低工资的雇员和部门名称
每个部门的最低工资
select deptno,min(sal) from emp group by deptno

select e.*,d.dname from (select deptno,min(sal) minsal from emp group by deptno) t,
emp e ,dept d where t.minsal=e.sal and t.deptno=e.deptno and e.deptno=d.deptno

–查询出不是领导的员工??? 7369 7499
查询员工编号没有出现在mgr列中
select *
from emp
where empno not in (select distinct mgr from emp where mgr is not null)

–课堂练习
–查询员工表中工资最高的前三名 5000 3000 3000
select * from emp order by sal desc
伪列
rowid:是每行数据指向磁盘的物理地址
rownum:查询时对每行数据上贴的序号

select t.,rownum from (select e. from emp e order by sal desc) t where rownum<4

rownum不支持大于号
select *
from (select t., rownum r
from (select e. from emp e order by sal desc) t
where rownum < 7) t1 where t1.r>3
select *
from (select t., rownum r
from (select e. from emp e order by sal desc) t
) t1 where t1.r>3 and t1.r<7

–找到员工表中薪水 大于本部门平均薪水 的员工
每部门平均薪水

select deptno,avg(sal) from emp group by deptno

select * from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) t
where e.deptno=t.deptno and e.sal>t.avgsal

–统计每年入职的员工个数
select to_char(hiredate,‘yyyy’),count() from emp group by to_char(hiredate,‘yyyy’)
–行转列
别名
select 10 “1981” from dual
decode(列名，值，显示数据)
select sum(counts) “Total” ,
sum(decode(years,‘1980’,counts)) “1980”,
sum(decode(years,‘1981’,counts)) “1981”,
sum(decode(years,‘1982’,counts)) “1982”,
sum(decode(years,‘1987’,counts)) “1987”
from
(select to_char(hiredate,‘yyyy’) years,count() counts from emp group by to_char(hiredate,‘yyyy’)) t

子查询总结：
子查询可以返回单行单列值
子查询可以返回单行多列值
子查询可以返回多行多列值（当成子表使用）
子查询可以提高查询效率

范例：查询10号部门的员工
emp 10000
dept 1000
产生1000W条数据，从1000W数据中过滤
select * from emp e , dept d where e.deptno=d.deptno and d.deptno=10

如果改为子查询
相当于从1W条数据中过滤
select * from emp e , (select * from dept where deptno=10 ) d where e.deptno=d.deptno

------------------------------以上都是重点--------------------------------
exists
语法：
select * from 表名 where exists (子查询)
exists (子查询) 如果子查询没有结果 exists (子查询) 返回false
exists (子查询) 如果子查询有结果 exists (子查询) 返回true

select * from emp where exists (select * from dept where deptno=50)
select * from emp where 1=2
select * from emp where exists (select * from dept where deptno=10)
select * from emp where 1=1

查询没有员工的部门信息

select * from dept d where exists (select * from emp e where e.deptno=d.deptno)

–集合运算

–范例：工资大于1500，或者是20号部门下的员工
select * from emp where sal>1500
union --all --并集
select * from emp where deptno=20;
select * from emp where sal>1500 or deptno=20;

–范例：工资大于1500，并且是20号部门下的员工
select * from emp where sal>1500
intersect–交集
select * from emp where deptno=20;

select * from emp where sal>1500 and deptno=20;

–范例：1981年入职的普通员工（不包括总裁和经理）
select * from emp where to_char(hiredate,‘yyyy’)=‘1981’ --1981年入职的所有员工
minus --差集 补集
select * from emp where job =‘MANAGER’ or job=‘PRESIDENT’

—集合运算：如果两个结果集的列数和对应的数据类型一致就可以做集合运算
select empno,ename from emp
union
select deptno,dname from dept

全国行政单位

id name pid
0 中华人民共和国 -1
1 北京市 0
2 河北省 0
3 石家庄 2
4 保定 2
5 长安区 3
6 赵县 3
7

递归查询语法 Oracle独有的
select * from 表名
start with 条件1
connect by prior 条件2

查询7566号员工的所有下属
select * from emp

select * from emp
start with empno=7566
connect by prior empno=mgr

查询7788号员工的所有领导
select * from emp
start with empno=7788
connect by prior mgr=empno

count 效率
select count(*) from emp; 最慢
select count(ename) from emp; 较慢
select count(empno) from emp; 最快
select count(1) from emp; 最快
select count(comm) from emp; 错误，因为count会跳过null