oracle常用sql三连击——02

--------------------------oracle第二天---------------

--回顾多表查询

select e1.empno,
       e1.ename,
       e1.deptno,
       e1.sal,
       s.grade,
       d.dname,
       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 >= s.losal and   e1.sal<=s.hisal
   and e1.sal between s.losal and s.hisal
   and e2.sal between s1.losal and s1.hisal

select * from salgrade

-------------------oracle第二天----------------
-------------------------子查询-------  重点
--查询比 MARTIN 工资高的员工
--分析：首先查询出MARTIN工资是多少
select sal from emp where ename='MARTIN'  --1250.00
select * from emp where sal>1250.00

select * from emp where sal>(select sal from emp where ename='MARTIN')

--查询职位是经理并且工资比7782号员工高的员工

select * from emp where job='MANAGER' 
and sal>(select sal from emp where empno=7782)

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

--查询所有部门最低工资大于30号部门最低工资的结果

select deptno,min(sal) from emp group by deptno
having min(sal)>(select min(sal) from emp where deptno=30)

--查询出和 MARTIN 同部门并且同职位的员工
select * from emp where job=(select job from emp where ename='MARTIN')
and deptno=(select deptno from emp where ename='MARTIN');

select * from emp where (job,deptno)=(select job,deptno from emp where ename='MARTIN');(算是组合字段，注意顺序必须相同。）

--查询每个部门的最低工资和最低工资所对应的雇员以及所在部门名称
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 d.deptno=t.deptno
  and t.deptno=e.deptno
  
 select * from dept;
 select deptno , min(sal) minsal from emp group by deptno;
 select * from emp;
 
---把所有表能关联上的字段全部作为条件查询
select * from emp

--查询出不是领导的员工??????????????????
--分析：查询员工的empno没有出现在mgr字段中   in
--null参与运算计算结果恒为null（注意！！！！！！！！！）
select  * from emp where empno not in(select mgr from emp where mgr is not null)（因为mgr字段中有值为null，所以需要去掉。）

---------子查询小结
1、子查询可以返回单行单列值
2、子查询可以返回单行多列值
3、子查询可以返回多行多列值（一般都会作为一个子表和其他表关联查询）
4、子查询可以提高查询效率（下面是例子：）
emp  14
dept 4
--查询30号部门的所有员工
56中找出6条数据
select * from emp e,dept d where e.deptno=d.deptno and d.deptno=30;

14条 中找出6条
select * from emp e, (select * from dept where deptno=30) d where e.deptno=d.deptno ;

-------课堂练习
-- 分页 重点
--查询员工表中工资最高的前三名  5000 3000 3000
---- 伪列：rowid，rownum
rowid----是每行数据在磁盘上的物理地址
rownum--查询时产生的序号
--order by 始终放在最后
（因为rownum查询时便产生了，所以需先产生再根据其来排序，而不能先根据其判断大小再排序（排序必须写在一个语句最后），否则结果是错的。）
select  e.*,rowid,rownum from (select * from emp order by sal desc) e where rownum<4 

---- 分页 按照每页显示5条数据  查询第一页 

select  e.*,rowid,rownum from (select * from emp order by sal desc) e where rownum<6 

--  第二页 7782  7844 7934 7521 7654
--rownum 不支持大于号
select t.* from (
select e.*, rownum r
  from (select * from emp order by sal desc) e
 where rownum < 11) t where t.r>5   --rownum要当列字段使用必须起别名,关键字是不能直接做别名的.
   --先排序,再基于排序之后的顺序得到已经符合排序要求的rownum,并将rownum转为普通字段,根据rownum普通字段顺序获得需求.
-----综上 就是oracle分页的做法(没有排序要求的分页只需两步,子查询获得rownum同时转为普通字段,外层查询按获得的新表查询.比如:select * from (select emp.*,rownum r from emp)e where r between 6 and 10;)
(错误:

--找到员工表中薪水大于本部门平均薪水的员工
select * from 
(select deptno , avg(sal) avgsal from emp group by deptno) t,
emp e
where t.deptno=e.deptno and e.sal>t.avgsal（注意：这里可以直接比较两张表的两个字段！！！）

select  * from emp;

--统计每年入职的员工个数

select to_char(hiredate, 'yyyy') years, count(*) nums
  from emp
 group by to_char(hiredate, 'yyyy')
----行转列-- （了解）
--补充别名的使用
--别名不能使用特殊字符和数字
select  job "职 位" from emp ;
select  job  职位  from emp ;（如果职位不加引号，则中间不能有空格。因为别名不能用特殊字符。）
select  job  "1999"  from emp;（别名要用数字也得加引号。）
select 10 "1981" ,1 "1981" from dual;（这个的结果是列名为1981的字段值是10，第二个列名为1981的字段值是1.数字值是语句里直接写出来的，select多少就是多少。）
--行转列语法
select sum(nums) "Total",（默认都会转成大写，要显示成Total的话就得加引号。）
       sum(decode(years, '1987', nums)) "1987",（decode函数判断years这个字段值为‘1987’的，显示nums字段。并且把所有years字段为1987的求和。最后结果取别名“1987”。）
       sum(decode(years, '1980', nums)) "1980",
       sum(decode(years, '1982', nums)) "1982",
       sum(decode(years, '1981', nums)) "1981"
  from (select to_char(hiredate, 'yyyy') years, count(*) nums
          from emp
         group by to_char(hiredate, 'yyyy')) t

--集合运算  了解

--范例：工资大于1500，或者是20号部门下的员工

select * from emp where sal > 1500
union   --并集（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'
minus --补集  差集
select * from emp where job='MANAGER' or job='PRESIDENT'

-----------------------------DDL语句-----------------------

--------------创建表空间 需要使用管理员身份  切换到system用户来操作

---创建表空间tablespace   crm项目
create tablespace crmspace --表空间的名称
datafile 'c:\crm.dbf'   --数据文件
size 100M              --指定初始文件大小（超过会报错，所以指定自动扩展功能）
autoextend on         --自动扩展
next 10M      --自动扩展大小（每次扩展的大小。最后能扩展到多大取决于数据库所在磁盘空闲空间，有1G便可到1G。）

---创建用户  user

create user crmuser  --指定用户名称
identified by itcast   --设置密码
default tablespace crmspace --指定默认要操作的表空间

--分配权限
--分配dba角色（oracle中已存在三个重要的角色：
    connect角色（授予最终用户的典型权限，最基本的），
    resource角色（授予开发人员的），
    dba角色（最高权限）。）
grant dba to crmuser

---------切换到新创建的用户  crmuser
---创建表
---数据类型
字符型
char     一个固定长度     names char(10)  是 2（中文”是“占了2个长度，剩余8个长度会用空格补足）  
varchar2 一个不固定长度   names varchar2(10)  是 2（剩余8个长度会释放出去）    最大值4000
数值型（oracle用number一个类型便能代替MySQL所有数值类型，一个number就够了。但也有别的float那些。）
number   --默认长度为38
nums number(3)  -999到999 （代表3位长，最大值999）
nums number(3,2) 999.99 错！！  -9.99到9.99（代表3位长数字，（不包括负号或者小数点），2位小数，最大值9.99）
日期型
date  相当于mysql中datetime   带时分秒（MySQL中分date和datetime）
timestamp 时间戳  精度到秒后小数9位（航空类项目或抢购项目才会必须用到这类型。）
大数据类型
long 相当于mysql中的longtext  存放大文本 2G
clob 最大可存放4G（存放文本）
blob 最大可存放4G（存放字节，byte流。比方放一个电影、图片，但一般不往数据库里放，而只存路径，放数据库里数据备份太不方便，量大，慢。）
--创建表  --  重点

create table person(
pid number ,                     --默认长度为38
pname varchar2(20),    --必须指定长度
age number(3),
birthday date,
gender number(1)   --0代表女  1代表男（国外一般不用sex，sex首先是性。）
)

--修改表
alter  table person add (address varchar2(10)) --添加字段
alter table person modify (address varchar2(100)) --修改字段长度
alter table person  drop column  address      --删除字段
alter table person  rename column  gender to sex      --修改字段名称

--删除表
drop table person;（Oracle有回收站，不小心删除了可从回收站恢复。个别可以！很多时候不可以！！！）

-------------dml语句-------  重点*****
--操作数据
插入数据    insert into

insert into person values (1,'大头儿子',8,to_date('2008-08-08','yyyy-mm-dd'),1);

insert into person (pid,pname) values(2,'隔壁老王');
commit;

select * from person

---更新数据 update
update person set age =30 where pid=2;    （update person set age =30会修改表中所有数据）

--删除数据 delete 
delete from person where pid=2;        （delete from person会删除表中所有数据。）

---事务 （了解）
 savepoint  --事务保存点(好像是在没提交的前提下）
 
insert into person (pid,pname) values(2,'隔壁老王');

 savepoint a;
 
update person set age =30 where pid=2;

 savepoint b;
 
 rollback to a;
 
 rollback;
 
 commit;
 

select * from person;

--事务的隔离性（脏读：没提交，读到了，读完之后别人还可以回滚。Oracle默认第二级别，避免了脏读，但不可重复读（即不能重复读到相同的结果，读的时候别的事务也能修改）。MySQL默认第三个级别，读的时候别人不能修改。）

 update person set age =40 where pid=2;

 
 

------------约束-----
--主键 外键 重点

主键  非空  唯一  外键  检查约束

insert into person values 
(3,'围裙妈妈',28,to_date('1989-08-08','yyyy-mm-dd'),2);

drop table teacher

create table teacher(
tid number primary key,--主键
tele varchar2(11) unique,--唯一约束
tname varchar2(10) not null,
sex number(1)  check( sex in(0,1))--检查约束   
)--这样写也行，但如果操作数据时出错你就不容易知道是哪错了。这些约束Oracle会随机起个名字，你不知道哪个是哪个。所以用下面的方法写。

create table teacher(
tid number,
tele varchar2(11),
tname varchar2(10) not null,
sex number(1),
constraint pk_tid  primary key(tid),--constraint约束，后面跟着约束的名字。
constraint unique_tele  unique(tele),
constraint check_sex check( sex in (0,1))
     
)
--测试数据：
insert into teacher values (1,'13800000000','大头',1);

select * from teacher

insert into teacher values (1,'13800000000','围裙',2);

---外键约束
orders --订单表
create table  orders (
ooid number primary key ,
tatolprice number(8,2)
)

ordersdetail --详单项表

create table ordersdetail(
did number primary key,
price number(8,2), --单价
oname varchar2(10), --商品名称
ooid number,--外键
constraint fk_orders_detail foreign key(ooid) references orders(ooid)--外键约束
)

insert into orders values(1,2000);
insert into orders values(2,5000);

insert into ordersdetail values (1,500,'鼠标',1);
insert into ordersdetail values (2,500,'鼠标1',3);--3主表里没有，此处会报错。

delete from orders where ooid=1--从表里有以1为外键的数据，此处报错。

select * from orders;

select * from ordersdetail;

--约束的作用
1、检查数据
2、保证了数据的完整性

--------------扩展
create table myemp as select * from scott.emp;    --会完全把scott用户下的emp表的表结构和表数据都克隆过来！包括表数据！
create table mydept as select * from scott.dept;

select * from myemp
select * from mydept

--给NEW YORK部门下的所有员工工资加100元

select * from mydept where loc='NEW YORK'

select * from myemp where deptno  in (select deptno from mydept where loc='NEW YORK')

update myemp
   set sal = sal + 100
 where deptno in (select deptno from mydept where loc = 'NEW YORK');

--删除
--删除某表的所有数据 teacher
delete  from teacher;
truncate table teacher;

select * from teacher;
-- delete 和 truncate的区别
1、delete有事务 truncate没有事务
2、delete删除后的空间不会被使用， truncate删除整个表，重新创建了一张表（空间可以再使用）
3、delete会产生磁盘碎片 truncate不会产生磁盘碎片
4、delete删除的可以恢复，而truncate不可以

-------------------oracle的其他对象----------------
----序列  sequence  ---重点
--独立于表之外的对象，能产生连续的值

--创建序列的简单语法
create sequence seq_order;
--使用序列（默认递增1，起始也是1）
--使用简单语法创建出来的序列 必须先执行nextval 后才能执行currval
select seq_order.nextval from dual--下一个值
select seq_order.currval from dual--当前值
create sequence seq_order1
select seq_order1.nextval from dual
select seq_order1.currval from dual

insert into orders values(seq_order.nextval,2000);    --序列的使用！！！可直接sql里用！！！

select * from orders

--序列的完整语法  3 5 7 9 11 13 15 2 4 6 8 10 12 14 2 4 6 8 10 12（循环示例。从3开始，最大15，一轮后重新开始，从最小值开始。）
create sequence seq_orders2  --序列名称
increment by 2           -- 步长，默认值 1
start with 3             -- 默认值 1 
maxvalue 15               -- 默认值 19个9 99999999999999999999
minvalue 2                 -- 默认值 1
cycle                    --是否循环，默认值 nocycle  
cache 5                --缓存，默认值20（如果是cycle的，则缓存数量必须小于一个循环！不能有重复值。）
（序列sequence是存在磁盘空间的，即dbf文件中，每次取值都会费IO。我们提前取出5个来放到内存中，即使缓存。）
（序列的应用场景：除主键外，比方循环的应用：
    电影院1号放映厅在一天中的不同时间放映电影，则电影票的座位号，是循环的，则座位号可用序列取值。
    如果1、2、3号座位默认给领导留着，则start with 4。火车票的座位号。）

----视图   view   ---重点
create [or replace] view 视图名称 as 查询语句（中括号里的内容是可有可无的。）

--视图的作用：
1、可以封装复杂的sql
create or replace view view_hirenum
as
select sum(nums) "Total",
       sum(decode(years, '1987', nums)) "1987",
       sum(decode(years, '1980', nums)) "1980",
       sum(decode(years, '1982', nums)) "1982",
       sum(decode(years, '1981', nums)) "1981"
  from (select to_char(hiredate, 'yyyy') years, count(*) nums
          from myemp
         group by to_char(hiredate, 'yyyy')) t

--怎么使用视图  
--当做普通的表使用
select * from view_hirenum

2、可以隐藏关键字段（比方让新员工去操作没有核心数据的视图表。隐藏就是创建视图的时候没有查询要隐藏的字段。）

select * from myemp

create or replace view view_emp as select empno,ename,job,mgr,deptno from myemp

select * from view_emp

update view_emp set ename='SMITH' where empno=7369    --如果不设只读，则视图数据可以修改，且修改后基表数据也会变。

select * from myemp;

--视图的数据是引用基表中的数据。不是拷贝。

----只读视图（一般都设成只读。）
-- 把20号部门的员工做成视图
create or replace view view_emp20 as
 select * from myemp where deptno=20 with read only

select * from view_emp20
update view_emp20 set ename='SMITH2' where empno=7369--(此时会报错，设只读后便不能修改了。）

---索引 index  ---重点        (如何提高表的查询效率？创建索引。）
--目的就是快速的查找数据    提高查询效率
--语法
create  index 名称  on 表名(列名1,列名2.....)
--给myemp表的ename创建索引（当myemp表中的ename经常用来做查询条件，才有必要为其创建索引。）
create index index_emp_ename on myemp(ename)
select * from myemp where ename=''

创建索引前提
1、某字段会被经常用作条件查询
2、数据量大时
3、这个表如果更新数据比较频繁时不建议创建索引

---索引是以树形的结构存放在数据库中

--复合索引
select * from myemp where ename='' and  job='';
create index index_emp_ename_job on myemp(ename,job);

create index index_emp_ename_job on myemp(job,ename);--（这个复合索引跟上面那个是不一样的，顺序不同便不同。）

--创建一个表给这个表5000000数据
create table t_test
(
tid  number,
tname varchar2(30)
)
--
create sequence seq_test

--插入数据
PLSQL  --面向过程的一种语言。sql语言的一种扩展。
begin
  for i in 1..5000000
  loop
    insert into t_test values (seq_test.nextval,'测试数据'||i);
  end loop;
 commit;
end;

select count(*) from t_test

select * from t_test where tname ='测试数据4878787'   --0.656秒

--创建索引
create index index_test on t_test(tname); 

select * from t_test where tname ='测试数据4578787'   --0.062秒（创建索引后）

--同义词  synonym   (了解)
create [public]  synonym 同义词名称 for 对象;--中括号里的可写可不写。对象是指一个表名，视图也包括。
--作用：
1、可以缩短表名
create synonym t for teacher

select * from t
 
synonym
2、可以方便访问其他用户的表

select * from scott.emp

create public synonym emp for scott.emp

select * from emp

--删除同义词
drop synonym t

drop public synonym emp --这个必须有public，因为创建的时候加了public，删除的时候也必须有。否则报不存在。