Oracle_2 随便写写

select * from emp natural join dept order by DEPTNO DESC ; --自然查询 natural join 可以自己识别出两表中 相同的字段进行关联。
select * from emp e,dept d where e.deptno=d.deptno;

select * from dept d left join emp e on e.deptno=d.deptno; --left join 左连接
select * from dept d left join emp e where e.deptno=d.deptno; --left join 不可以和where进行使用 否则提示缺少关键字 on, 需要 left join on 这是一个完整的语法。

select * from emp e right join dept d on e.deptno=d.deptno; --right join 右链接


select count(d.dname) 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;

 

select count(*) from emp; --count(求记录数)
select min(sal) Minsal ,max(sal) Maxsal ,avg(sal) Avgsal ,sum(sal) Samsal from emp where deptno=20; -- min 求最小值 max 求最大值 avg 求平均值 sum 求总和


select deptno,count(deptno) from emp group by Deptno;

select count(deptno) from emp where deptno=10;

select * from dept;

select count(d.deptno),d.deptno from dept d left join emp e on d.deptno=e.deptno where d.deptno=20 group by d.deptno;


select count(job),d.deptno,d.dname from emp e right join dept d on d.deptno=e.deptno group by d.deptno,d.dname ,e.job order by d.deptno desc;

select count(job),d.deptno,e.job from emp e right join dept d on d.deptno=e.deptno group by d.deptno ,e.job order by d.deptno desc;

select count(*), job, deptno from emp group by deptno, job order by deptno; --count* 会以job来统计记录还是以deptno来统计记录。


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

--查询平均工资大于2000的部门
select avg(sal) from emp group by deptno having avg(sal)>2000;
select count(empno) from emp e right join dept d on d.deptno=e.deptno group by d.deptno;
select count(d.deptno) from emp e right join dept d on d.deptno=e.deptno group by d.deptno;

select sum(sal),job from emp where job!='SALESMAN' and sal>2000 group by job, sal order by sal ;
select sum(sal) abc ,job from emp group by job, sal having job!='SALESMAN' and sal>2000 order by sum(sal) ;


select count(*), job, deptno from emp group by deptno, job order by deptno; --count* 会以job来统计记录还是以deptno来统计记录。

 

select e.sal
from emp e
where e.sal > (select sal from emp e1 where empno = 7654);


--查询比7654工资高 且跟7788工作职位相同。

select *
from emp t
where t.sal > (select sal from emp e where e.empno = 7654)
and t.job = (select job from emp r where r.empno = 7788);


select e.empno ,e.ename,e.job
from emp e
where e.sal>(select sal from emp q where q.empno=7654)
and e.job=(select job from emp w where w.empno=7788);




select a.sal,a.ename,a.deptno from emp a where sal in (select min(sal) from emp e group by e.deptno) ;

select * from emp w,
(select min(e.sal) eoo,e.deptno from emp e group by e.deptno) e
where w.sal=e.eoo and w.deptno=e.deptno;



select w.ename,w.sal 工资 from emp w where w.sal in(select min(e.sal) from emp e group by e.deptno);


select w.* from emp w ,( select min(e.sal) ttt from emp e group by e.deptno ) e where w.sal=e.ttt;



select d.deptno from dept d where d.deptno in(select e.deptno from emp e group by deptno); --group by 比distinct 性能要高, group by只需要读取分组的记录, distinct需要读取所有记录
select d.deptno from dept d where d.deptno not in(select distinct e.deptno from emp e );


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

--union 查询集合去并集时可以去除重复的数据
select * from emp where emp.sal>1000
union
select * from emp where emp.sal>2000;

select * from emp where emp.sal>1000 or emp.sal>2000;

--union all 查询集合去并集时 不会去除重复的数据
select * from emp where emp.sal>1000
union all
select * from emp where emp.sal>2000;


select empno,ename,job from emp where empno=7788
union
select empno,ename,job from emp where empno=7654;

--并集时 要合并的列名可以不一致,但列名的类型必须一致 并且union时 两条语句的列数必须要保持一致
select empno,job , ename from emp where empno=7788
union
select empno,ename,job from emp where empno=7654



create table myemp as select * from emp;

select * from myemp for update;

select sysdate from dual;

insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(1234,'LiMing','Sport',7788,to_date('1999-01-01','YYYY-MM-DD'),2000,200,30) --增 insert into 表名(列1,列2) values(值1,值2)

update myemp set empno =1235,ename='MingLi123' ,Job='Book' where Empno=1235 --改 update 表名 set 列名= 值 where 条件

delete from myemp where empno=1235 --删除 delete from 表名 where条件 Oracle可以省略from,但mysql中必须写from,并且Oracle可以定义别名, mysql删除时表不能定义别名。

 

--事务对 增删改 都生效
commit;--事务提交 commit
rollback;--事务未提交时可进行回滚,已提交则不能回滚 rollback


create table person(
pid number,
pname varchar2(10),
birthday date
)

insert into person values(001,'张三',to_date('2020/01/01 12:59','yyyy/mm/dd hh:mi'),18)
insert into person values(001,'张三',to_date('2020/01/01 12:59','yyyy/mm/dd hh:mi'),1888)

 

select * from person;

drop table person;


alter table person add(age number); --现有表中添加 列 add

alter table person modify(age number(6,2))--现有表中的列进行修改 列的类型 modify


--截断表 会直接删除表中的数据, 不会保留执行提交或回滚的操作,直接删除。 delete删除时会进行提示和保留提交和回滚的操作。
truncate table person;

 


drop table person;
-- 定义主键 primary key 主键约束

create table person(
pid number primary key,
pname varchar2(10),
birthday date
)

insert into person values( '','张三',to_date('2020/01/01 12:59','yyyy/mm/dd hh:mi'))
--自定义主键名称 constraint 约束
create table person(
pid number ,
pname varchar2(10),
birthday date,
constraint person_person_id_pk primary key(pid)
)
--自定义多个约束的名称 constraints

-- 唯一约束 unique
drop table person;
create table person(
pid number ,
pname varchar2(10) ,
birthday date null,
constraint person_person_id_pk primary key(pid) ,
constraint person_pname_uk unique(pname)
)

insert into person values( 002,'2',to_date('2020/01/01 12:59','yyyy/mm/dd hh:mi'))

select * from person;

--非空约束 not null

drop table person;
create table person(
pid number ,
pname varchar2(10) not null,
birthday date null,
constraint person_person_id_pk primary key(pid)
)

insert into person values( 001,'1',to_date('2020/01/01 12:59','yyyy/mm/dd hh:mi'))

select * from person;

-- 唯一约束 unique
drop table person;
create table person(
pid number ,
pname varchar2(10) unique,
birthday date null,
constraint person_person_id_pk primary key(pid)
)

insert into person values( 002,'2',to_date('2020/01/01 12:59','yyyy/mm/dd hh:mi'))

select * from person;


--外键约束 foreign key references

drop table orders;
--订单表
create table orders(
order_id number(4),
order_price number(6,2),
order_time date,
constraint orders_order_id primary key(order_id)
);

drop table order_detail;
--订单明细表
create table order_detail(
detail_id number(4),
item_name varchar2(10),
price number(6,2),
quantity number(4),
order_id number(4),
constraint order_detail_detail_id_pk primary key(detail_id),
constraint order_detail_order_id_FK foreign key(order_id) references orders (order_id) on delete cascade
)


insert into orders values (001,6000,sysdate);
insert into order_detail values(1000,'iphone',2000,3,001);

 


delete from orders;
drop table orders cascade constraint; --cascade constraint


select * from order_detail;

 

select rownum-1 ,e.* from emp e;


select rownum rw, a.* from (select * from emp ) a where rownum<6;


--查询数据
-- 拿查询结果集当做一个表 然后限定条件
-- 拿上一个结果集再当一个表 之后使用 rownum的别名进行限定条件使用

select *from
(select rownum rw, a.* from (
select * from emp
) a where rownum < 11) b
where b.rw > 5

 

 

 

 


--视图
grant connect,resource,dba to scott;


create view view_emp_deptno20 as select * from emp where deptno=20;


select ename from view_emp_deptno20;
-- 创建视图 语法:create view 视图名称 as 语句
--or replace 创建视图或视图存在时直接覆盖现有视图。 创建或覆盖
--with read only 是不允许视图被修改, 如果表数据有变更,视图查询的数据也会同步。
create or replace view view_myemp20 as select * from myemp where deptno=20 with read only;

update view_myemp20 set ENAME='liming' where EMPNO=7369;

update myemp set ename='史密斯' where empno=7369;
select * from view_myemp20;

posted @ 2021-03-17 12:16  就让文谦先行  阅读(41)  评论(0编辑  收藏  举报