Oracal 子查询 表操作

=====================================================================================================
--查询比SMITH工资高的员工
select * from emp  where sal>(select sal from emp where ename='SMITH')
--查询比SMITH工资高的员工,同时从事跟SMITH工作一样的员工
select * from emp  where sal>(select sal from emp where ename='SMITH'and job=(select job from emp where ename='SMITH')
--查询每个部门的最低工资的员工
select deptno 部门,min(sal) 最低工资 from emp group by deptno;
select * 
  from emp e,(select deptno ,min(sal) ms from emp group by deptno) d
 where e.deptno = d.deptno
 and e.sal=d.ms
 
 ----查询每个部门的最低工资的员工 以及部门信息
 ---先查询出每个部门的最低工资
 select deptno, min(sal) from emp group by deptno;
 --在查询出 每个部门最低员工的信息
 select * 
  from 
    emp e,(select deptno, min(sal) ms from emp group by deptno) s  
  where e.deptno =s.deptno
   and e.sal = s.ms
 --还缺少部门信息,需要dept表
   
 select *
  from emp e,(select deptno, min(sal)+nvl(null,0) ms from emp group by deptno) s,dept d
  where e.deptno =s.deptno
   and e.sal = s.ms
    and d.deptno = e.deptno
 
 --查询工资最高的前三名
 ---- 查询按工资排序
 select * from emp order by sal desc
 ----第二步:子查询将所有的数据根据工资排序,
 ----在将查询的结果当成一张表来看
 select rownum,t.*
  fromselect * from emp order by sal desc) t
 --结合rownum最终查出前三条数据
  select rownum,t.*
  fromselect * from emp order by sal desc) t
  where rownum<4
--查询员工大于本部门平均薪水
--先查询出各部门的平均薪水
select deptno ,avg(sal) from emp group by deptno
select * 
 from emp e, (select deptno ,avg(sal) sa from emp group by deptno) t
where e.sal>(t.sa)
and e.deptno = t.deptno
--统计每年入职员工个数
--提示:根据to_char函数可以查询到每个员工的入职的年份 如:1981
select to_char(hiredate,'yyyy') y from emp
--根据入职年份分组后,根据count统计函数得到每年入职的员工个数
select t.y,count(*)
 from (select to_char(hiredate,'yyyy') y from emp) t
  group by t.y
 
==== ======= ========================================分页==============================================================
--查询工资排序
select rownum,e.* from emp e where rownum<4;
select * 
 from (select rownum r,e.* from emp e) t
 where t.r>3 and t.r<=6
/*
最内侧:将所有数据根据工资降序排序
第二层:将所有的数据加了一个rownum并且给rownum起个别名r
最外层:就是可以使用r这一列,就可以使用大于号了
*/
select *
 from (select rownum r,t.* from(select * from emp order by sal desc) t) t1
  where 
     t1.r>3 and t1.r<=6;
/*
总结公式:
select *
from (select rownum r,t.* from(select * from emp order by sal desc) t) t1 
   where t1.r>(pageNo-1)*pageSize 
     and
   t1.r<=pageNo*pageSize;
*/

==================================================== 集合操作(了解) =====================================================

--=并集 union(去掉重复数据)
--查询工资大于1200 ,或者工作是销售的
select * from emp where sal >1200
union 
select * from emp where job = 'SALESMAN';

--=并集 union all( 满足两个查询条件的数据全部显示
select * from emp where sal >1200
union all
select * from emp where job = 'SALESMAN';

--交集
select * from emp where sal >1200
intersect
select * from emp where job = 'SALESMAN';


--差集
select * from emp where sal >1200
minus
select * from emp where job = 'SALESMAN';

=========================================== ======== ==== DML  ===========================================================
DML(数据增删改查,表结构的创建)
数据类型:
        字符类型:
         char :固定长度字符类型  举例:name char(10)  存值:张三  取出后是占10个长度
         varchar2:可变长度字符类型
        数值类型:
         number
         整数:  number(3): 999
         小数: number(3,2): 9.99
        日期类型:
         date:在MySQL中date类型精确到日月年, 在oracle数据库中,精确到时分秒
          timestampt:精确到秒的后9 位。<秒杀专用>
        大数据类型:
         long :在MySQL中表示长整型,在oracle中表示字符类型大数据类型。2G
         Clob:大数据类型,存字符的,最多存放4G
         Blob:大数据类型,存二进制数据,最多存4G 数据     


====================================== ======== === 创建表空间(了解) ======================================================

oracle只有一个全局数据库

表空间是数据存储逻辑单位,真正数据存在数据文件中(数据文件就是在磁盘体现出来 xxx.dbf)
创建表空间,代表的项目的开始。

--如何创建表空间(让用户有dba角色来执行创建)

语法:
create tablespace 表空间名称
datafile 'c:\space.dbf'     --指定数据文件位置
size  100m       --指定数据文件大小
autoextend on   --设置自动扩展
next 10m;     --每次增长多少

--语法中顺序不能改变  
==========================切换到system用户

create tablespace wangkaike
datafile 'c:\wang.dbf'
size 100m
autoextend on
next 10m;

--用户来管理表空间
--创建用户
create user wangkaike   --指定用户名
identified by admin    --指定密码
default tablespace wangkaike   --指定用户管理哪个表空间

--给用户赋予 权限 (三种角色:connect,resource,dba)
grant connect,resource to wangkaike;

revoke connect,resource from wangkaike; --回收权限

--使用-------------------------------wangkaike登陆
--创建表
create table person(
  id number(9),
  name varchar2(200),
  gender number(1)
);
--添加数据: oracle中事务需要手动提交
insert into person values(1,'张三',1);
insert into person values(2,'lisu',1);
insert into person values(3,'王五',1);
commit;
--删除表
drop table person;

--修改表属性(添加一列)
alter table person add address varchar2(200);
--修改列属性
alter table person modify address varchar2(500);
--修改列名
alter table person rename  column address to address01;
--删除列名
alter table person drop  column address01;


--更新数据
update person set name ='赵六' where id = 2;
commit;

--删除数据
delete from person where id = 3;
--摧毁表结构,再重构表,跟delete from 比较truncate效率很高,不能有条件的删除数据(不建议使用)
truncate table person;

=================================================约束(重点掌握主键,外键)================================================

--主键约束(掌握)
drop table person;
create table person(
  id number(9)  primary key,
  name varchar2(200),
  gender number(1) default 1
);
insert into person values(1,'张三',1);
--非空约束--唯一约束
create table person(
  id number(9) primary key,
  name varchar2(200)  unique not null,
  gender number(1) default 1
);
insert into person values(1,'张三',1);
insert into person values(2,null,1);  --违反了非空约束

--外键约束(掌握)
create table orders(
 order_id number(9),
 total_price number(9,2),
 order_date date,
  constraint pk_order_id primary key(order_id)
);

create table order_item(
  item_id number(9) primary key,
  name varchar2(200),
  order_id number(9),
   constraint  fk_orders_order_id  foreign key (order_id)  references  orders(order_id)
);

insert into orders values(1,198.88,sysdate);

insert into order_item values(1,'笔记本',1);
insert into order_item values(2,'键盘',1);
commit; 

insert into order_item values(3,'键盘',2);  --违反了外键约束

--删除有主外键的数据
  1,删除从表数据 2,删除主表的数据

--强制删除主表结构(不推荐使用)
drop table orders cascade constraint;

--实现级联删除(删除主表中的数据,如果从表有关联,也同样删除,不推荐使用)
create table orders(
 order_id number(9),
 total_price number(9,2),
 order_date date,
 constraint pk_order_id primary key(order_id)  --设置主键约束等同于在主键列上加 primary key
);

drop table order_item;

--实现级联删除
create table order_item(
  item_id number(9) primary key,
  name varchar2(200),
  order_id number(9),
  constraint fk_orders_order_id foreign key(order_id) references orders(order_id)  on delete cascade  
                 --on delete cascade级联删除
);

select * from orders;
select * from order_item;

delete from orders where order_id = 1;

--检查约束
drop table person;

create table person(
  id number(9) primary key,
  name varchar2(200),
  gender number(1)  check(gender in(1,2))  --设置性别只能是1,2
);

insert into person values(1,'张三',1);
insert into person values(2,'李四',2);   --违反了检查约束
commit;


====================================================== 事务 ==========================================================
--oracle事务需要手动提交  提交:commit  回滚:rollback;
--保存点 svaepoint
update person set name = '赵四' where id = 1;
savepoint a1;
update person set name = '王大拿' where id = 2;
rollback to a1;
select * from person;  

================================================== 视图 -(了解) =========================================================

只有dba才能创建视图 .可以查看
 

视图:就是一张虚拟表,本身不存放数据,数据来源于原始表
语法:
     create view 视图名称  as 查询语句;


--使用system用户登录,给scott赋予权限(默认角色connect,resource)
 grant dba to scott;
--------------------切换到scott
create view v_emp as select * from emp;
select * from v_emp;
--创建视图(查询某些列)
--只能看到的部分数据
create view v_emp1 as select empno,ename,job,hiredate from emp;
select * from v_emp1;
--修改视图数据
update v_emp1 set ename ='smith' where empno = 7369;
select * from v_emp1;
--再次看emp表中数据 (修改视图中数据,原始的数据也会改变)
select * from emp;
--创建一个只读视图
create view v_emp2 as select empno,ename,job,hiredate from emp with read only;
update v_emp2 set ename ='SMITH' where empno = 7369;   --只读视图不能修改   

============================================ 序列(次重点掌握简单序列创建的语法即可) ========================================
-- 可以在object中查看
 
作用:生成一个主键自增效果
语法:create sequence 序列名称;
*/
create table person1(
  id number(9primary key,
  name varchar2(200),
  gender number(1check(gender in(1,2))
);
--创建序列<可以在 Object --sequence 查看 >
create sequence seq_person;
--使用(查看序列的值)--每次自增一,
select seq_person.nextval from dual;
select seq_person.currval from dual;
insert into person1 values(seq_person.nextval,'张三',1);
insert into person1 values(seq_person.nextval,'李四',1);
insert into person1 values(seq_person.nextval,'王五',1);
commit;
select seq_person.nextval from dual;
insert into person1 values(seq_person.nextval,'小七5',1);
commit;
--删除序列
drop sequence seq_person;
--出现序列裂缝
---1,发生异常,
---2,多张表统一用一个序列  一般一张表对应一个序列
create table person2(
  id number(9primary key,
  name varchar2(200),
  gender number(1check(gender in(1,2))
);
insert into person2 values(seq_person.nextval,'aaaa',1);
commit;
--创建序列完整的语法(了解)
create sequence seq_test
increment by 2
start with 5
maxvalue 20
cycle      --循环
cache 5;    --默认缓存20个
select seq_test.nextval from dual;
--创建一个序列,必须先执行nextval,再执行currval
select * from person1;
select * from person2;   



=========================================================== 索引 ======================================================



提高检索的速度。
语法:create index 索引名称  on 表名(列名1,列名2);

--索引创建的原则:
1,经常用到查询的列
2,在大数据表上创建序列
3,索引创建层数不要超过四层,为多个列创建索引,不要超过四个列

--添加一百万条数据
select sys_guid() from dual;  --生成一个不重复的字符串

declare 
 pname varchar2(200);
 begin
  for i in 1..1000000 loop
  select sys_guid() into pname from dual;
  insert into person2 values(seq_person.nextval,pname,1)
  end loop;
  commit
 end
select * from person where name =
   'CF0D579363514B3D9854C329ECD294B2';  --0.577s
--为person表创建一个索引(单列索引)
create index idx_person on person2(name);
select * from person where name = '5EC01B5DC37E4BFFB39AE74C36CCCB95';  --0.016s


======================================================== 同义词 ======================================================

--创建一个同义词
create synonym myperson1 for itcast.person;
select * from myperson1;
delete from person;   









 
SELECT * FROM EMP;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值