oracle学习笔记

最新推荐文章于 2023-10-08 00:33:36 发布

lyue2018

最新推荐文章于 2023-10-08 00:33:36 发布

阅读量340

点赞数

本文链接：https://blog.csdn.net/qq_42873827/article/details/90278658

版权

orcale
sql简单查询
【2控制要显示的数据列】select[distinct] * |列名称
【1确定数据来源】from 表名称 [别名]
执行顺序：首先执行的时from字句，因为必须通过from字句确定数据的来源，而后要针对于数据的筛选操作，就通过select字句完成

set linesize 300; //设置行显示的长度

set pagesize 30; //设置列显示的长度
直接查询常量
select ‘雇员’,empno,ename from emp;
在进行简单查询操作里面，如果有需要也可以select字句里面使用“||”链接查询结果
select ‘雇员编号:’||empno||’,姓名:’||ename||’,收入:’||sal*12 from emp infor; //在查询的时候加入常量
2、限定查询
【3控制要显示的数据列】select[distinct] * |列名称
【1确定数据来源】、from 表名称 [别名]
【2确定满足条件的数据行】[where 过滤条件（s）]
限定查询符号
1、关系运算符：>、<、>=、<=、<>(!=)不等于
2、逻辑运算符：and、or、not
3、范围运算符：between…and
4、谓词范围：in、not in
5、空判断:is null、is not null
6、模糊查询：like
select *
from emp
where sal >1500;查询工作大于1500的员

select *
from emp
where sal>=1500 and sal<=3000; 查询工资在1500-3000之内的

select *
from emp
where sal>2000 or job=‘CLERLK’;查询工资大于2000的员或职位是办事员

select *
from emp
where not sal>=2000;查询工资小于2000

select *
from emp
where sal between 1500 and 2000;查询工资在1500-2000之内的
发现“=”可以在数字上使用，也可以在字符串上使用，在oracle之中为了翻遍开发所有的运算符可以不区分数据类型
select *
from emp
where hiredate between ‘01-1月-81’ and ‘31-12月-81’;

select * from emp where comm is not null;查询comm部位空的员工信息
select * from emp where not comm is null;

select *
from emp
where empno in(7369,7566,7788,9999);查询雇员编号在(7369,7566,7788,9999)之内的雇员信息

select *
from emp
where empno not in(7369,7566,7788,9999);查询雇员编号不是(7369,7566,7788,9999)的雇员
在使用NOT IN 进行范围判断的时候，如果范围里包含有NULL,那么不会有任何结果返回，使用 NOT IN的目的是为了查询部分数据行，但是如果有了NULL（某些数据用远不可能为空）接变成了查询全部了。

select *
from emp
where hiredate between ‘01-1月-81’ and ‘31-12月-81’;查询在81年雇佣的员工

3、模糊查询
1、“—”：匹配任意的以为字符；
2、“%”:匹配任意的零位，一位或多为字符；

select * from emp where ename like ‘A%’ ;查询以字母A开头的雇员姓名

select * from emp where ename like ‘_A%’ ;查询第二位是字母A的雇员姓名

select * from emp where ename like ‘%A%’ ;查询姓名中带有字母A的雇员姓名

说明一：like可以应用在各种数据类型上

select * from emp where sal like ‘%9%’;

说明二：在使用like模糊查询的时候，如果不设置查询关键字，那么表示查询全部

select * from emp where ename like ‘%%’;

4、查询排序ORDER BY
【3控制要显示的数据列】select[distinct] * |列名称
【1确定数据来源】、from 表名称 [别名]
【2确定满足条件的数据行】[where 过滤条件（s）]
【4针对查询结果进行排序】[order by 字段[ASC|DESC],字段[ASC|DESC]
order by可以使用select定义的别名（只有order by可以调用select别名）
ASC:按照升序的方式排列

select * from emp order by empno desc;雇员编号由高到低排序

单行函数

1、字符串函数

转大写 upper（）

select upper(‘hello’) from dual; //转大写

select lower(ename) from emp; //转小写

select * from emp where ename=upper(’&inputname’); //查询雇员姓名由用户自己输入

select ename,initcap(ename) from emp; //查询首字母转大写

select * from emp where length(ename)=5; //查询雇员名字长度为5的人

select substr(‘helloworld’,5) from dual;从第五位开始截取到最后

在oracle数据库中开始索引从1开始
select substr(‘helloworld’,0,5) from dual;
select substr(‘helloworld’,1,5) from dual;

select ename,substr(ename,-3) from emp;截取字符串后三位
select ename,substr(ename,length(ename)-2) from emp; 截取字符串后三位

2、数值函数

No 函数名称功能
1 数字round(列|数字[，可选的小数位] 实现数据的四舍五入
2 数字trunc(列|数字[，可选的小数位] 实现数据的截取，不进位
3 数字mod(列|数字,列|数字) 求模
select round(789.654666) from dual; //四舍五入

select round(789.654666,3) from dual; //四舍五入保留2位小数

select round(789.654666,-2) from dual; //保留整数，进行四舍五入

select mod(10,3) from dual;求模

3、日期函数

函数名称	功能

如果要想处理任何日期，那么都有一个基本的前提，必须知道当前的日期是什么
oracle提供了一个伪列“sysdate”

操作公式：
日期 + 数字 = 日期表示若干天之后的日期
日期 - 数字 = 日期表示若干天之前的日期
日期 - 日期 = 数字表示天数

select sysdate from dual;当前日期

select systimestamp from dual；当前时间戳

select add_months(sysdate,4) from dual;四个月之后的日期

select last_day(sysdate) from dual;显示当前日期的最后一天

select empno,ename,hiredate,months_between(sysdate,hiredate) from emp;员工雇佣的月数

select next_day(sysdate,‘星期一’) from dual;下一个星期一

4、转换函数

函数名称	功能

1 字符串to_char(列|日期|数字，转换格式）将日期或数字格式化
2 日期to_date(列|日期,转换格式) 按照指定的转换格式编写字符串后将其变为日期型数据
3 数字to_number(列|字符串）将字符串变为数字

日期：年（yyyy）、月(mm)、日(dd)
时间：时（hh、hh24）、分(mi)、秒(ss)
数字：任意数字（9），货币符号（l）

select to_char(sysdate,‘yyyy-mm-dd’) from dual;初始化日期

select to_char(sysdate,‘yyyy’),to_char(sysdate,‘mm’),to_char(sysdate,‘dd’) from dual;拆分日期

select to_char(697598763579678,‘l999,999,999,999,999,999’) from dual;格式化数字

多表查询
1.1 多表查询的基本概念
如果现在需要同时从多张数据表中取出数据，那么就属于多表查询，在from字句后面设置多张数据表
【3控制要显示的数据列】select[distinct] * |列名称
【1确定数据来源】、from 表名称 [别名]，表名称[别名]
【2确定满足条件的数据行】[where 过滤条件（s）]
【4针对查询结果进行排序】[order by 字段[ASC|DESC],字段[ASC|DESC]

多表查询会出现笛卡尔积，要想消除笛卡尔积的问题，那么久必须想办法为两张表设置关系（设置关联字段）

由于在进行多表查询的时候有可能不同的表会存在相同的列名称，那么在这种情况下，那么就需要在列前面加上表名称
例如：
select *
from emp,dept
where emp.deptno=dept.deptno;
以上的代码只是显示的消除了笛卡尔积，而在数据库的多表查询之中，笛卡尔积会一直存在，只要是存在数据表，那么一定会存在有笛卡尔积
原则：多表查询性能一定是很差的，在开发之中应该尽可能回避
任何情况下，如果要实现多表查询操作，永远都有一个前提：查询的数据表一定要存在关联字段，或者是关联条件
范例：要求显示每个雇员的编号、姓名、职位、工资、部门名称、位置
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;
范例：要求显示每个雇员的编号、姓名、职位、工资、工资等级
Select e.empno,e.ename,e.job,e.sal,s.grade
From emp e,salgrade s
Where e.sal between s.losal and s.hisal;
范例：要求显示每个雇员的编号、姓名、职位、工资、工资等级、部门名称
select e.empno,e.ename,e.job,e.sal,s.grade,d.dname
from emp e,salgrade s,dept d
where e.sal between s.losal and s.hisal and e.deptno=d.deptno;
一般情况下如果是多个消除笛卡尔积的条件都会使用and进行连接
多表查询的使用：
1、没有关联字段或者关联条件的两张数据表是永远不可能实现多表查询的；
2、在进行多表查询这样的复杂查询操作时，强烈建议分步骤解决问题；
1.2 表的连接
现在既然可以连接多张数据表进行数据查询，那么在进行表连接的时候就需要设置一个数据的主控方，对于连接主要分为两种形式：
.内连接（等值连接）：所有满足条件的数据都会被显示出来
.外连接（左外链接、右外连接、全外连接）：控制左表、右表是否全部显示
1、等值连接：
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;
现在的代码是属于只有当e.deptno=d.deptno 这个条件满足之后才会显示出对应的数据，如果此时数据为空，那么此条件无法满足，则内容不能显示
2、外连接
让等值判断左右两边有一边的数据全部显示出来，如果要想实现外连接，则需要使用“（+）”的语法完成：
.左外链接：字段 = 字段（+）；
.右外连接：字段（+） = 字段；
观察左外链接：
select e.ename,e.ename,e.job,d.deptno,d.loc
from emp e,dept d
where e.deptno=d.deptno (+);
观察右外连接：
select e.ename,e.ename,e.job,d.deptno,d.loc
from emp e,dept d
where e.deptno(+)=d.deptno;
在实现外连接的过程之中，强烈不建议大家去刻意的区分左右外连接，如果发现数据的查询结果之中缺少到了内容，那么就利用外联接来控制。
范例：查询雇员的姓名、职位、领导姓名；
select e.ename,e.job,m.ename
from emp e,emp m
where e.mgr=m.empno(+);
1.3 SQL：1999

Select [distincet] * |列 [别名]
From 表名称1[cross join 表名称2]
[natuarl join 表名称2]
[join 表名称 on(条件) | using (字段)]
[left |right | full outer join 表名称2]；
1、交叉连接：cross join
Select * from emp cross join dept;
2、自然连接：natural join
Select * from emp natural join dept;
3、using:设置关联字段
Select * from emp join dept using(deptno);
4、on 字句：没有关联字段是可以用on设置关联字段
Select * from emp e,join salgrade s on(e.sal between s.losal and s.hisal);
5、外连接：
左外链接：Select * from emp left outer join dept using(deptno);
右外连接：Select * from emp right outer join dept using(deptno);
全外连接：Select * from emp full outer join dept using(deptno);

1.4 数据集合操作
之前都属于多张数据表的关联操作，但是数据的集合操作主要负责连接的时查询结果。对于集合提供有四种操作符：union、union all、interaect、minus,利用这几个符号可以实现多个查询语句的连接。
【3控制要显示的数据列】select[distinct] * |列名称
【1确定数据来源】、from 表名称 [别名]，表名称[别名]
【2确定满足条件的数据行】[where 过滤条件（s）]
【4针对查询结果进行排序】[order by 字段[ASC|DESC],字段[ASC|DESC]
Union | union all | intsert | minus
【3控制要显示的数据列】select[distinct] * |列名称
【1确定数据来源】、from 表名称 [别名]，表名称[别名]
【2确定满足条件的数据行】[where 过滤条件（s）]
【4针对查询结果进行排序】[order by 字段[ASC|DESC],字段[ASC|DESC]
要求：多个查询结果返回列的结构必须相同

范例：验证union
select * from emp
union
select * from emp where deptno=10;

范例：验证union all
select * from emp
union all
select * from emp where deptno=10;
范例：interset
select empno,ename,job,deptno from emp where deptno=10
intersect
select empno,ename,job,deptno from emp;

范例：验证minus 返回结果差
select empno,ename,job,deptno from emp
minus
select empno,ename,job,deptno from emp where deptno=10;

分组统计查询

1.1 统计函数

Oracle 五个常用的统计函数：
.统计个数：count(*|字段|[distinct 字段])
.max(字段)
.min(字段)
.sum(数字字段)
.avg(数字字段)

范例：查询最高和最低工资
select max(sal),min(sal) from emp;

范例：查询工资总和及平均值
select sum(sal),avg(sal) from emp;

1.2 分组统计查询
【5控制要显示的数据列】select[distinct] 分组字段[别名]，统计函数
【1确定数据来源】、from 表名称 [别名]，表名称[别名]
【2确定满足条件的数据行】[where 过滤条件（s）]
【3 group by 分组字段，分组字段…】
【4针对于分组后的数据进行筛选】having 分组后的过滤条件
【6针对查询结果进行排序】[order by 字段[ASC|DESC],字段[ASC|DESC]

限制一：在没有group by 字句的时候（全部分组），那么select字句之中只允许出现统计函数，不允许出现任何的其他字段
范例：
正确代码：select count(empno) from emp;
错误代码：select count(empno),ename from emp;

限制二：在使用group by字句分组的时候，select字句之中只允许出现分组字段与统计函数，其他字段不允许出现
正确代码：select job,count(empno) from emp group by job;
错误代码：selct job,count(empno),ename from emp group by job;

限制三：统计函数允许出现嵌套查询，但是嵌套后的统计查询中，select字句里面不允许再出现任何其他字段，包括分组字段，只能够使用嵌套的统计函数
正确代码：select max(avg(sal)) from emp group by deptno;
错误代码：select deptno,max(avg(sal)) from emp group by deptno;

限制四：where字句不能使用统计函数。之所以不能够使用，是因为统计的操作属于group by之后的范畴了，而where 是在group by 操作之前使用的，所以此时如果要想针对于分组后的数据进行过滤，那么只能够使用having字句完成。

范例：查询部门名称、统计雇员编号、平均工资

select d.dname,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.dname;

范例：查询平均工资高于2000的职位名称
select job,avg(sal)
from emp
group by job
having avg(sal)>2000;

注意：关于where与having的区别？
.where发生在group by操作之前，属于分组前的数据筛选，即：从所有的数据之后筛选出可以分组的数据
.having发生在group by操作之后，是针对于分组后的数据进行筛选；可以使用统计函数

子查询
所谓的子查询指的就是在一个查询里面继续嵌套其他的查询语句。
子查询的用法：
1、where子句：子查询一般会返回单行单列、单行多列、多行多列
数据
2、having字句：子查询会返回单行单列，同时表示要使用统计函数
3、from字句：子查询返回多行多列数据（表结构）
4、select字句：返回单行单列，一般不使用

1.1 在where字句里使用子查询

范例：查询出低于公司平均工资的雇员信息
Select * from emp where sal < (select avg(sal) from emp);

范例：查询出最早雇佣的员工
select *
from emp
where hiredate=(select min(hiredate)
from emp);

范例：查询与SCOTT从事同一工作，并且工资相同的雇员信息
select * from emp
where (job,sal)=(
select job,sal
from emp
where ename=‘SCOTT’)
and ename<>‘SCOTT’;
三个操作符：in any all

Any :
=any:和in功能一样

any:比子查询最小值大
<any:比子查询最大值要小
范例：
//=any是和in的功能一样
select * from emp
where sal =any(select sal from emp where job=‘MANAGER’);

//<any比子查询的最大值要小
select * from emp
where sal <any(select sal from emp where job=‘MANAGER’);

//>any 比子查询的最小值要大
select * from emp
where sal >any(select sal from emp where job=‘MANAGER’);

all:

all:比子查询返回的最大值要大
<all:比子查询返回的最小值要小
范例：
//>all 比子查询的最大值要大
select * from emp
where sal >all(select sal from emp where job=‘MANAGER’);

//<all比子查询的最小值要小
select * from emp
where sal <all(select sal from emp where job=‘MANAGER’);

1.2 在having字句里面使用子查询

范例：查询出高于公司平均工资的职位名称、职位人数、平均工资
select job,count(empno) ,avg(sal)
from emp
group by job
having avg(sal)>(select avg(sal) from emp);

1.3 在from字句里使用子查询
Select d.dname,d.loc,tmp count
From dept d,(select deptno,count(empno) count
From emp
Grou by deptno) temp
Where d.deptno = temp.deptno(+);

子查询的主要目的是解决对表查询所带来的的性能问题

表的创建于管理

1.1 常见的数据类型

类型	作用

1 Varchar2（n）表示字符串,其中n表示长度，一般长度比较小的的内容都可以使用此类型(200字以内)
2 Number(n,m) Number(n):表示整数数据，最多不能超过n个长度
Number(n,m):表示小数位占m位
3 date 保存日期时间
4 clob 大文本数据，最多保存4G的文字
5 blob 二进制数据，最多保存4G内容、文字、图片、电影
1.2 创建数据表
Create table 表名称（列名称类型 [default 默认值]…）;
create table member(
mid number,
name varchar2(20) not null,
email varchar2(30),
constraint uk_email unique(email)
);

向表中增加数据：
insert into member( mid,name,age,birthday,note)
values (1,‘张三’,22,to_date(‘1996-10-10’,‘yyyy-mm-dd’),‘是个人’);

1.3 数据表的重命名
数据字典：
.用户级别：user_*开头，指的是一个用户可以使用的数据字典；
.管理员级别：dba_*开头，指的是由数据库管理员使用的数据字典；
.全部级别：all_*开头，表示不管是用户还是管理员都可以使用。
语法：
Rename 旧的表名称 to 新的表名称