--以scott用户为例记录简单oracle操作
--简单查询
select * from emp;
--查找与部门最低工资相一致的员工信息及工资
select emp.deptno,emp.ename,emp.empno, emp.sal from emp
inner join
(select deptno,min(sal) as sal from emp
group by deptno) minsal
on emp.deptno=minsal.deptno
and emp.sal=minsal.sal
order by 1;
--查找工资最高的前三人信息
select rownum as rnum, r.* from(select emp.* from emp order by sal desc) r where rownum<=3
--查询工资高于部门平均工资的员工信息
select emp.deptno, emp.ename,emp.empno,emp.sal,temp.sal from emp
inner join
(
select deptno,avg(sal) as sal from emp
group by deptno) temp
on emp.deptno=temp.deptno
and emp.sal>temp.sal
order by 1
--decoe,行转列，每年入职员工数
select sum(temp.empNum) as Total,
sum(decode(temp.year,'1980',temp.empNum)) as "1980",
sum(decode(temp.year,'1981',temp.empNum)) as "1981",
sum(decode(temp.year,'1982',temp.empNum)) as "1982",
sum(decode(temp.year,'1987',temp.empNum)) as "1987"
from
(
select to_char(emp.hiredate,'YYYY') as year, count(distinct emp.empno) as empNum
from emp
group by to_char(emp.hiredate,'YYYY')
) temp
--每页三条数据，按员工工资降序分页查询第二页数据
select * from
(
select rownum as rnum,temp.* from
(
select * from emp order by sal desc
) temp
)rult
where rnum between 4 and 6
;
--intersect 使用，在复杂语句中添加条件查询时好用,可以不用修改前一段语句
select * from emp where sal>1200 and job='SALESMAN';
select * from emp where sal>1200
intersect
select * from emp where job='SALESMAN'
--union 去重，union all 不去重
select * from emp where sal>1200
union all
select * from emp where job='SALESMAN'
--minus 减去，求差集 ,从满足条件一的数据中减去满足条件二的数据
select * from emp where sal>1200
minus
select * from emp where job='SALESMAN';--or
select * from emp where sal>1200
and not(job='SALESMAN');
--exists,not exists用法，查询出有员工的部门
select * from dept
where exists(select 1 from emp where deptno=dept.deptno);
----单表互联，取别名，查询是别人领导的员工信息
select * from emp emp where exists(select 1 from emp mgr where emp.empno=mgr.mgr)
--concat ||
select distinct concat('员工姓名:',ename) as "员工姓名",'员工编号:'||empno as "员工编号"
from emp;
--不等于 <>; !=; not(=)
--排序 desc asc nulls last/first
select * from emp order by comm desc nulls last;
select * from emp order by sal desc,comm asc;
--日期相隔月份数，入职多少个月，三个月后转正日期
select distinct ename,empno,hirEdate,months_between(sysdate,hirEdate) as mths,add_months(hirEdate,3) as rDate
from emp;
--人数大于5人的部门信息
select dept.* from dept
where exists(
select deptno
from emp
where dept.deptno=emp.deptno
group by deptno
having(count(distinct empno))>5);
----同时查出人数
select dept.* ,count(distinct emp.empno) as empNum from
dept,emp
where dept.deptno=emp.deptno
group by dept.deptno,dept.dname,dept.loc
having(count(distinct emp.empno))>5;
/*创建操作*/
/***********/
--创建表空间
create tablespace bitest
datafile 'c:/bitest.dbf'
size 100M
autoextend on
next 10M
;
--删除表空间及物理文件,删除用户
drop tablespace bitest including contents datafiles;
drop user bitest cascade;
--创建用户,默认表空间及授权
create user bitest identified by bitest123;
default tablespace bitest;
grant dba to bitest;
--创建表
create table persion(
pid number,
pname varchar2(50)
);
--插入修改删除表数据
insert into persion(pid,pname) values(1,'aa');
commit;
update persion set pname='bb' where pid=1;
commit;
delete from persion where pid=1;
commit;
truncate table persion;
commit;
--修改表属性
alter table persion add sex number(1);--增加列
alter table persion modify sex char(2);--改列的数据类型
alter table persion rename column sex to psex;--改列名
alter table persion drop column psex;--删除列
--创建表时增加约束及主键
drop table persion;
create table persion(
pid number primary key,--此时主键名称由系统创建
pname varchar2(50),
psex number
);
create table persion(
pid number,
pname varchar2(50) not null,--非空约束/unique 唯一约束
psex number check(psex in(1,0)),--检查约束check(列名 in(值))
constraint PK_PID primary key(pid)--指定主键名称
);
--外键约束 constraint FK_约束名称 key(需加外键的字段) references 外键对应表名(外键对应表主键)
--增加数据时先主后从，删除数据时先从后主
--创建主表
create table orders(
order_id number primary key,
total_price number(6,2)
);
--创建从主
create table order_detail(
detail_id number primary key,
order_id number,
item_name varchar2(20),
constraint FK_ORDERDETAIL_ORDERID foreign key(order_id) references orders(order_id)
)
--创建视图
create or replace view v_emp
as select distinct empno,ename,hiredate from emp
with read only--加上这句就只能查询
;
--创建自增长序列
create sequence seq_e
start with 1
increment by 2
maxvalue 999--最大值
cycle--可循环
cache 5--缓存5个
/*以上为标准写法*/
create sequence seq_emp;
select seq_emp.nextval from dual;--currval
--drop sequence seq_emp;
insert into emp(empno) values(seq_emp.nextval);--使用序列
commit;
/*创建表然后循环插入数据 loop */
create table persion(
pid number,
pname varchar2(20)
);
create sequence seq_persion;--创建自增长序列
--循环插入数据
begin
for i in 1..1000 loop
insert into persion values(seq_persion.nextval,'name_'||i);
end loop;
commit;
end;
--创建索引
create index INX_PNAME on persion(pname);
/*命令执行数据导入与导出
全库操作
exp system/orcl full=y file=expdata.dmp
imp system/orcl full=y file=expdata.dmp
按用户
exp scott/tiger file=expdata.dmp full=y
imp scott/tiger file=expdata.dmp full=y
*/
--以现有表结构创建新表
create table emp_bak as select * from emp where 1=2;--不写条件则直接创建表及插入原表数据
--查询数据插入表
insert into emp_bak select * from emp;
commit;
--给表及字段增加说明
comment on table emp_bak is 'emp bak';
comment on column emp_bak.job is 'job';
--在sys下给用户授创建视图权限
grant create view to scott;
grant select any table to scott;
grant select any dictionary to scott;
