oracle笔记:
- 账户
--oracle自带账户：
-- system 管理账户
--特点：具备大部分oracle的操作权限，主要用来管理普通账户及oralce的数据
--使用人：oracle数据维护工作人员
-- sys 超级管理员账户
--特点：具备system的所有权限，同时又具备其他的权限
--使用人：oracle攻城狮
--创建账户
--使用system账户，并使用dba身份，登录oracle管理系统
--创建用户
create user fly identified by fly;
--维护账户
--赋予权限 grant 权限或者角色名 to 用户名
grant connect to fly;--给用户赋予登录权限
grant resource to fly;--给用户资源操作权限
grant dba to fly;--给用户赋予dba权限
select * from scott.emp--查看其它用户的表 使用用户名.表名
--删除权限 revoke 权限或者角色名 from 用户名
revoke dba from fly;
--删除账户 drop user 用户名
drop user fly;
- 别名:
select ename 姓名,job as "工作",mgr as MGR,mgr"MGR1" from emp;
- 连接符
select ename||'的工作是:'||job as mess from emp;
- 去重
select distinct job from emp;
- 字符运算
select sal*0.8 from emp;
- oracle默认的日期格式为 日-月-年，示例'03-1月-1981'
select * from emp where hiredate>='01-12月-1981' order by hiredate;
- 转义字符 escape 定义'/'为转义符号 '/'将有转义功能
select * from emp where ename like '%/_%' escape '/'
- 单行函数:
- 字符转小写
select lower(ename),lower('ABCD') from emp; select lower('ABC') from emp;- 数值函数,使用伪表
select abs(-1),ceil(2.2),floor(3.3),power(2,3),mod(5,2),round(4.55),trunc(10/3,2) from dual
-- 1 3 向上取整 3向下取整 8 幂 1取余 5四舍五入 3.33截断
- 日期函数：
select months_between('01-1月-2018','01-1月-2017') from dual --返回两个日期间的月份数
select add_months('01-4月-2018'，-4) from dual --返回指定月数后的日期
select next_day('19-3月-2019','星期二') from dual--查询最近的星期的日期
select last_day('19-3月-2019') from dual--返回当月的最后一天的日期
select round(to_date('20-3月-2019'),'DAY') from dual--按照星期进行四舍五入
- 多行函数(max,min,avg,sum,count)
select max(sal),sal from emp--多行函数不能和字段直接混用，除非分组。
select lower(ename)，max(sal) from emp--多行函数 不能和单行函数混用，除非分组
select count(distinct job) from emp
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp
- 转换函数
--转换函数：
--to_number(数值类型的字符):将字符转换为数值
--to_char(数值或者是日期):将数值或者日期转换为字符
--to_date(日期格式的字符)：将字符转换为日期
----------------数值和字符的互转-----------------------
--字符转换为数字char---->number
select to_number('123')+2 from dual
--数字转换字符number--->char
--指定显示格式:
--9表示位置占位，例如999,999,999会将数字按照三个一组使用逗号隔开。
--L表示人民币符号，$表示美元符号
--0可以进行占位分组，但是如果真实数据位数不足，会使用0进行补位。
select to_char(12345,'$999,999,999') from dual
select to_char(12345,'L999,999,999') from dual
select to_char(12345678,'000,000,000,000.000') from dual
--查询工资大于2000的员工信息
--数值和字符之间的转换可以隐式转换。to_number可以省略不写.
select * from emp where sal>'2000';
select * from emp where sal>to_number('2000');
---------------日期和字符的互转---------------------------
--一般使用方式：新增数据使用to_date(),查询数据使用to_char()
--字符转换为日期 char--->date
--使用to_date('要转换的字符',日期格式)函数将字符转换为日期
--注意1：字符必须符合日期格式
--注意2:oralce默认的转换格式为日月年，例如'01-1月-2018' oracle认为是一个日期
--常用日期格式：
-- yyyy-mm-dd
-- yyyy/mm/dd
--查询员工入职日期在82年后的信息
select * from emp where hiredate >to_date('1982-01-01','yyyy-mm-dd')
select * from emp where hiredate >to_date('1982/01/01','yyyy/mm/dd')
select * from emp where to_char(hiredate,'yyyy-mm-dd') >'1982-01-01'
--日期转换为字符 date--->char
--使用to_char('要转换的日期',转换格式)
--注意1：如果不指名转换格式，则使用默认格式，日月年例如:'01-1月-81'
--常用转换格式：
-- yyyy-mm-dd
-- yyyy/mm/dd
--'yyyy"年"mm"月"dd"日"'
select to_char(hiredate) from emp--使用默认格式将日期转换为字符
select to_char(hiredate,'yyyy-mm-dd') from emp--使用指定格式 yyyy-mm-dd
select to_char(hiredate,'yyyy/mm/dd') from emp--使用指定格式 yyyy/mmm/dd
select to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp--使用指定格式 'yyyy"年"mm"月"dd"日"'
-----------------------------------------------------------------------------------------------------
-其他函数：
--nvl()：nvl(字段名，新的值)
--如果字段值不为null，则返回该字段的值。如果为null则返回新的值
--nvl2()：nvl2(字段名，处理1，处理2)
--如果字段值不为null，则执行处理1，为null执行处理2
--decode()：decode(字段名,值1，处理1，值2，处理2，值3，处理3,...,公共处理)
--如果字段的值和decode中的条件值相同则执行对象的处理。如果都没有则执行公共处理
---查询员工的工资信息
select ename,job,sal from emp
--查询员工的薪水信息
select comm,sal+nvl(comm,0),sal+comm,sal from emp
800 800.0
300.00 1900 1900 1600.00
500.00 1750 1750 1250.00
select ename,job,nvl2(comm,sal+comm,sal) from emp
--显示员工的职称
select ename,job,decode(job,'MANAGER','经理','PRESIDENT','董事长','SALESMAN','销售','普通员工') from emp
- 分组
--关键字：group by 分组字段名,分组字段名....
--注意1：使用了分组后，在select语句中只允许出现分组字段和多行函数。
--注意2：如果是多字段分组，则先按照第一字段分组，然后每个小组继续按照第二个字段继续分组，以此类推。
--注意3：在where子句中不允许出现多行函数。
--分组筛选
--关键字：having
--作用：针对分组进行分组后的数据筛选，允许使用多行函数。
--注意：having关键必须和分组结合使用。不允许单独使用。
--where和having的比较：
--where子句不允许出现多行函数，having允许出现多行函数
--where子句和having都可以使用普通字段直接进行筛选，但是where的效率高于having
--where执行顺序: from--->where--->group by-->select-->order by
--having执行顺序:from--->group by-->select--->having--->order by
--结论：在分组语句中，使用where进行字段级别的筛选，使用having进行多行函数的筛选。
--查询部门号大于10的不同部门的不同工作岗位的人数
--使用having关键字
select deptno ,lower(job),count(*) from emp group by deptno,job having deptno>10 order by deptno
--使用where关键字
select deptno,job,count(*) from emp where deptno>10 group by deptno,job order by deptno
- 增加数据
--insert into 表名(字段名，字段名，...)values(值1，值2，值3....);
insert into dept(deptno,dname,loc)values(60,'xxx','北京');
insert into dept values(70,'xxx','北京'); -- 全字段插入，可以省略字段名部分
- 删除数据
--删除数据
--delete from 表名 删除表中的所有记录
--truncate table 表名 删除表中的所有记录，但是效率高于delete
--delete from 表名 where 条件 删除指定的数据,只要符合条件就会删除
- 更新字段
--更新数据
--update 表名 set 字段名=新的值,字段名=新的值...(会将字段的值全部改为新的值)
--update 表名 set 字段名=新的值,字段名=新的值... where 条件(将符合条件的数据的字段改为新的值)
- 数据备份
--数据的备份
--注意：只会备份表结构和表的数据，约束不会备份。
--表级别备份
--全部备份:create table 新的表名 as select * from 备份表名
--部分备份: create table 新的表名 as select 字段名，字段名，...from 备份表名
--数据整体插入
--insert into 插入表名 select * from 表名
--注意:查询语句结果的字段数据必须和插入表名的字段数量一致，类型要一致。
create table deptBak as select * from dept--全部备份
create table deptBak2 as select deptno,dname from dept-- 部分备份
select * from deptBak2
insert into deptBak2 select deptno,dname from dept
- 联合查询
--sql92多表联合查询
--自连接:
--查询员工姓名，工作，薪资，及上级领导的名字
SELECT E1.ENAME,E1."JOB",E1.SAL,E2.ENAME FROM EMP E1,EMP E2 WHERE E1.MGR=E2.EMPNO;
--外连接
--左外连接
--查询员工姓名，工作，薪资，部门名称及没有部门的员工信息(+)表示没值也显示出来。
SELECT * FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO(+);
--查询员工姓名，工作，薪资，部门名称及没有员工信息的部门。
SELECT * FROM EMP e,DEPT d WHERE e.DEPTNO(+)=d.DEPTNO;
--右外连接
--SQL99多表查询
--注意1：依然可以给表添加别名
--注意2：如果使用on或者usering关键对结果进行筛选，必须使用inner join作用表与表的连接，其中inner可以省略
--注意3:外连接的 outer关键字可以省略不写
--注意4：依然可以继续使用分组，having ，排序等
--笛卡尔积:使用cross join 关键字
---select 内容 from 表名 cross join
select * from emp cross join dept
--筛选
--查询员工姓名，工作，薪资，部门名称
--自然连接：使用关键字 natural join
--使用：select 内容 from 表名 natural join 表名
--特点1：底层先笛卡尔积，然后按照所有的同名同值字段自动进行等值筛选。
--问题1：如果只想按照部分字段结果筛选怎么办？
--问题2：如果想按照字段名不同，但是值相同进行等值筛选怎么办？
select * from emp natural join dept
--解决1：使用using关键字
--作用1：指明使用指定的字段对联合查询的结果进行等值筛选
--注意：指明的字段必须是两表的同名同值字段
--使用:select 内容 from 表名 inner join 表名 using(字段名，字段名,....)
select * from emp inner join dept using(deptno)
--解决2：使用on关键字进行自定义连接条件筛选(等值筛选，不等值筛选)
--注意：普通筛选条件使用where进行筛选，不要使用on进行。好处：SQL语句的阅读性变强。
--使用：select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件
select * from emp inner join dept on emp.deptno=dept.deptno where sal>2000
--外连接：
--左外连接:select 内容 from 表名 left outer join 表名 on 连接条件
--查询员工姓名，工作，薪资，部门名称及没有部门的员工信息
select * from emp e left outer join dept d on e.deptno=d.deptno
--右外连接:select 内容 from 表名 right outer join 表名 on 连接条件
--查询员工姓名，工作，薪资，部门名称及没有员工的部门信息
select * from emp e right outer join dept d on e.deptno=d.deptno
--全外连接：select 内容 from 表名 full outer join 表名 on 连接条件
select * from emp e full outer join dept d on e.deptno=d.deptno
--自连接:
--查询员工及其上级领导姓名
select e1.*,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno
--完成三表联合查询
--SQL92实现：查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
--特点：易于书写，难于阅读
--缺点：92的SQL语句结构不清晰
--用法：
--select 内容 (别名，连接符，去除重复，oracle函数，逻辑运算)
--from 表名1，表名2，表名3...
--where 条件(连接条件，普通筛选条件，where子句关键字)
--group by 分组字段
--having 多行函数筛选
--order by 排序字段
select e.*,d.dname,c.cname
from emp e,dept d,city c
where (e.deptno=d.deptno and d.loc=c.cid and sal>2000) or (e.deptno=d.deptno and d.loc=c.cid and comm is not null)
order by e.sal
--SQL99实现:查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
--特点：难于书写，易于阅读
--使用：
--select 内容 from 表名1
-- inner join 表名2
-- on 连接条件
--inner join 表名3
--on 连接条件
--where 普通筛选条件
--group by 分组
--having 多行函数筛选
--order by 排序
select * from emp e
inner join dept d
on e.deptno = d.deptno
inner join city c
on d.loc =c.cid
where e.sal>2000 or e.comm is not null
order by e.sal
--单表查询：
--当需要的数据在一张表中，考虑使用单表查询
--多表联合查询：
--当需要查询的数据分布在多张表中，考虑使用多表联合
--子查询学习：
--使用时机：当查询的筛选条件不明确时，考虑使用子查询。
--单行子查询
--多行子查询
----------------------------------------------------------------
--单行子查询:
--使用时机：筛选条件不明确需要执行一次查询，并且查询结果一个字段并值只有一个
--注意：where子句中允许出现查询语句，该查询语句称为子查询
--使用：select 内容 from 表名 where 字段名 比较运算符 子查询语句
--查询所有比雇员“CLARK”工资高的员工信息
select * from emp where sal>(select sal from emp where ename ='CLARK')
--查询工资高于平均工资的员工的名字和工资
select ename,sal from emp where sal>(select avg(sal) from emp )
--查询和soctt属于同一部门且工资比他低的员工资料
select * from emp where deptno=(select deptno from emp where ename='SCOTT') and sal<(select sal from emp where ename='SCOTT')
--查询工资最高的员工资料
select * from emp where sal=(select max(sal) from emp)
--查询职务和scott相同，雇佣时间早的员工信息
select * from emp where job=(select job from emp where ename='SCOTT') and hiredate <(select hiredate from emp where ename='SCOTT')
--查询工资比scott高或者雇佣时间早的员工编号和名字
select empno,ename from emp where job=(select job from emp where ename='SCOTT') or hiredate <(select hiredate from emp where ename='SCOTT')
----------------------------------------------------------------------------
----多行子查询：
--使用:子查询的结果只有一个字段但是字段有n个值，考虑使用多行子查询，其实就是使用关键字
--关键字1:any 任意
--select 内容 from 表名 where 字段名 比较运算符 any 子查询语句
--关键字2：all 所有
--select 内容 from 表名 where 字段名 比较运算符 all 子查询语句
--关键字3：in 表示任意存在,相当于 = any
--select 内容 from 表名 where 字段名 in 子查询语句
--select 内容 from 表名 where 字段名 not in 子查询语句
--查询工资高于任意一个CLERK的所有员工信息
select * from emp where sal> any (select sal from emp where job='CLERK')
--查询工资高于所有SALESMAN的员工信息
select * from emp where sal> all (select sal from emp where job='SALESMAN')
--查询部门20中同部门10的雇员工作一样的雇员信息
select job from emp where deptno=10
select *from emp where (job='MANAGER' or job='PRESIDENT' or job='CLERK') and deptno=20
select * from emp where job in (select job from emp where deptno=10) and deptno=20
select * from emp where job = any (select job from emp where deptno=10) and deptno=20
- 约束
--创建表:
--使用：create table 表名(字段名 类型，字段名 类型，....);
--数据类型：
--number类型
--数值类型
--整数类型 number(a) 总长度为a
--浮点数类型 number(a,b) 总长度为a，小数位长度为b，小数位可以不写。
--varchar2类型
--字符类型 varchar2(ln) ln表示字符的最大长度，实际存储内存长度是根据字符大小来分配，但是最大不能超过ln
--特点：动态分配存储空间，节省空间
--char类型
--字符类型 char(ln) 不管字符数据长度是多大，直接开辟ln大小的空间存储数据
--特点：存储效率高于varchar2
--date类型
create table student(
sno number(10) primary key, --主键约束
sname varchar2(100) not null,
sage number(3) check(sage<150 and sage>0), --检测约束
ssex char(100),
sfav varchar2(500) --unique --唯一约束,
sbirth date
--, constraints pk_student_sno primary key(sno) --添加主键约束
--, constraints ck_student_sname check(sname is not null) --添加非空约束
--, cid number(10) references clazz(cno) --外键映射
)
--修改表，添加主键约束
alter table student add constraints pk_student_sno primary key(sno);
--删除主键约束
alter table student drop constraints pk_student_sno;
--修改可以级联删除
alter table student add foreign key(cid) references clazz(cno) on delete cascade;
--添加测试数据
insert into student values(1,'柳岩',18,'女','拍电影'，'01-1月-1985');
insert into student values(2,'古力娜扎',20,'女','拍电影'，to_date('1990-01-01','yyyy-mm-dd'));
select * from student
--使用外键:
--作用：当在子表中插入的数据在父表中不存在，则会自动报错。
--概念：当一张表的某个字段的值需要依赖另外一张表的某个字段的值，则使用外键约束。
--其中主动依赖的表称为子表，被依赖的表称为父表。外键加在子表中。
--使用：
--在子表中的字段后直接使用 references 父表名(字段) 例如： cid number(10) references clazz(cno)
--在创建表语句的最后面使用 constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
--在创建表后使用：alter table 表名 add constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
--删除外键：alter table 表名 drop constraints 外键约束名
--外键选取：
--一般选取父表的主键作为子表的外键。
--外键的缺点：
--无法直接删除父表数据，除非级联删除
--级联删除：在添加外键约束时，使用关键字 on delete cascade
--使用：当删除父表数据时，自动删除子表相关所有数据。
--缺点：无法保留子表历史数据。
--使用关键字 on delete set null
--删除父表数据时，将子表中的依赖字段的值设置为null。
--注意：子表依赖字段不能添加非空约束。
--二维表的维护
--添加新的字段:
--alter table 表名 add 字段名 类型
alter table student add sphone number(11)--在学生表中添加新的字段
--修改原有字段
--修改字段类型
--alter table 表名 modify 字段名 新的类型
alter table student modify sphone varchar2(11)
--修改字段名
--alter table 表名 rename column 字段名 to 新的字段名
alter table student rename column sphone to phone
--删除字段
--alter table 表名 drop column 字段名
alter table student drop column phone
--修改表名
--rename 原有表名 to 新的表名
rename student to student2
rename student2 to student
--删除表
--drop table 表名
drop table student
--oracle的序列的学习
- 序列
--创建序列
--使用 create sequence 序列名
--特点1：默认开始是没有值的，也就是指针指在了没有值的位置。
--特点2：序列名.nextval每次执行都会自增一次，默认步长为1
--特点3：序列名.currval查看当前序列的值。开始是没有的。
--作用：作为主键使用,动态的获取之间的值，这样新增数据的时候极大的避免了主键冲突
--使用的是 序列名.nextval作为主键
--注意：主键是非空唯一就可以，不需要主键的值是连续的值。
--创建默认序列
create sequence cc;--创建序列cc
select cc.currval from dual--查看序列当前值
select cc.nextval from dual--查看序列的自增后的值。
--创建自定义序列
create sequence aa--创建序列
start with 5 --设置开始位置
increment by 2 --设置步长
select aa.currval from dual
select aa.nextval from dual
--创建测试表
create table teacher(
tid number(10) primary key,
tname varchar(100) not null
)
insert into teacher values(cc.nextval,'张三');
insert into teacher values(cc.nextval,'张三');
select * from teacher
--删除序列
--drop sequence 序列名
drop sequence aa
- 索引
--索引学习：
--作用：提升查询效率
--使用索引：
--创建
create index 索引名 on 表名(字段名)
--删除索引
drop index 索引名
--特点：
--显示的创建，隐式的执行
--注意：
--oracle会自动给表的主键创建索引。
create index index_teacher_tname on teacher(tname)--创建索引
drop index index_teacher_tname--删除索引
select * from teacher where tname='张三'
select * from teacher where tid=8
- 视图
--视图学习:
--使用视图：
--创建视图
create view 视图名 as select 对外提供的内容 from 真实表名
--删除视图
drop view 视图名
--视图特点：
--特点1：保护真实表，隐藏重要字段的数据。保护数据。
--特点2：在视图中的操作会映射执行到真实表中
--特点3：可以手动开启只读模式 使用关键字 with read only
--注意：视图的创建必须拥有dba权限
create view stu as select sno,sname,sage from fly.student
create view stu2 as select sno,sname,sage from student with read only
drop view stu
select * from student
select * from stu
update stu2 set sname='wollo' where sno=1
grant dba to fly
-----oracle的分页查询
--问题：当一个表中的数据量特别大的时候，如果一次性全部显示给用户，则造成页面过于庞大，体验极差。
--解决：使用分页查询
--使用：
--rownum关键字：oracle对外提供的自动给查询结果编号的关键字，与每行的数据没有关系。
--注意：rownum关键字只能做< <=的判断，不能进行> >=的判断
select rownum ,e.* from emp e;
--查询员工信息的前5条数据 第一页数据
select rownum r,e.* from emp e where rownum <=5;
select * from (select rownum r,e.* from emp e where rownum <=5) t where r>0;
--查询员工信息的6-10条数据 第二页数据
select rownum r,e.* from emp e where rownum <=10;
select rownum,t.* from (select rownum r,e.* from emp e where rownum <=10) t where r>5;
--查询员工信息的11-15条数据 第三页数据
select rownum r,e. * from emp e where rownum<=15;
select * from (select rownum r,e. * from emp e where rownum<=15) t where r>10;
--分页规律总结：每页显示m条数据，查询第n页数据
select * from (select rownum r,e. * from 要分页的表 e where rownum<=m*n) t where r>m*n-m ;
--要分页的表既可以是真实的表，也可以是一个查询语句
--分页查询员工信息按照工资排序
select * from (select rownum r,t.* from (select * from emp order by sal) t where rownum<=10 ) where r>5
select * from t
select * from emp