=====================================================================================================
![]()
![]()
--查询比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.*
from( select * from emp order by sal desc) t
--结合rownum最终查出前三条数据
select rownum,t.*
from( select * 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(9) primary key,
name varchar2(200),
gender number(1) check(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(9) primary key,
name varchar2(200),
gender number(1) check(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;
扫一扫
802
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
