oracle的学习、包含SQL的内容

内容"/>

oracle的学习、包含SQL的内容

文章目录

• • 理论方面
  • 实操方面
  • • DDL
  • DML 增删改
  • 增加
  • • 批量增加
    • 删除数据
    • 修改
    • 查询
    • • 例子：
    • 运算符：
    • 修改日期格式
    • 排序 order by 列名,表达式 ,序号 默认按照升序排序
    • • • 多列排序
    • 单行函数 操作一行，返回一行
    • • 字符函数
      • 数值函数
    • 日期函数
    • 通用函数
    • 隐式转换
    • 子查询
    • • 子查询和主查询可以不是同一张表
      • MySql：
      • 外连接 (看from后面的表的顺序)

理论方面

Oracle服务器 ： 一个Oracle数据库和多个Oracle实例组成。
实例： 存储在内存当中
数据库 ： 以二进制的形式保存在硬盘中。

网络服务
服务名
使用Oracle ，首先开服务 services.msc

本地登录： sqlplus 用户名/密码
远程登录 ：sqlplus 用户名/密码@IP地址：端口号/服务

解锁账户
以超级管理员登录sqlplus / as sysdba
解锁账户 alter user scott account unlock;
修改scott账户的密码 alter user scott identified by tiger;
将账户从超级管理员切换到 scott conn scott/tiger

显示当前用户 show user
注释： – /* */
退出数据库 exit;

查看当前用户所包含的表： select * from tab;
实体： java中的类
记录： java中的对象
字段：类的属性
表： 同一个实体的所有的记录

显示行宽 show linesize set linesize 大小
显示页面大小 show pagesize set pagesize 大小
col ename for a9;
col sal for 9999;
起别名： as 可以省略 双引号也可以省略
select empNo as “员工编号”,ename as"员工姓名" from emp;

SQL语言的类型：
DQL （data query language）数据查询语言 select
DML(data manipulation language) 数据操作语言 insert update delete
DDL(data defination language)数据定义语言 create drop alter
DCL（data control language）数据控制语言 grant(授权) revoke（撤销授权）

使用超级管理员
创建用户： create user myuser identified by 123;
进行授权 grant CREATE SESSION to myuser;

实操方面

DDL

1.创建表： 必须要包含创建表的权利 有足够的内存空间
create table 表名(
字段名 类型,
字段名 类型,
…
字段名 类型
);
create table mytest(
tid number,
tname varchar2(20),
hiredate date
);
修改表
1.追加新列 add

alter table mytest add pic blob;

2.修改列 modify
修改字段的长度

alter table mytest modify mycomment varchar2(30);

修改字段的类型

alter table mytest modify myComment number;

3.删除列 drop column
alter table mytest drop column pic;
4.给列重命名

 alter table mytest rename column myComment to comm;

给表重命名 rename mytest to mytest2;

删除表 drop

drop table mytest2;

访问已经删除了的表mytest2

select * from "BIN$11AfULoQTF6C3EczlbWSXg==$0";

显示回收站中的表

show recyclebin;

清空回收站

purge recyclebin;

DML 增删改

增加

在已经存在的表中添加数据
insert into 表名(字段名1，字段名2，字段名3…) values(字段1的值，字段2的值，字段3的值…);

create table myemp(
empNo number,
ename varchar2(20)
)
insert into myemp(empNo,ename) values(1,'zs');
insert into myemp values(2,'ls');
insert into myemp(ename,empNo) values('ww',3);
insert into myemp(ename) values('zl');

批量增加

1.创建新表的时候 增加数据

create table emp2 
as  select * from myemp;create table emp3 
as select   empNo,ename,sal ,comm from emp;create table emp4
as select empNo,ename,sal,comm from emp where sal<2000;

创建一个与emp表结构相同的空表

create table emp5
as select * from emp 
where 1=2;

删除数据

delete from 表名 (where 条件)
delete from emp4 where empNo = 7369;
truncate table 表名
delete 和truncate 区别
delete 逐条删除，它并不是真正的删除 ，把数据存放在某个位置，可以回滚，不会释放空间，不会产生碎片。
truncate 先删除表，再重建。不能回滚 。会释放空间，会产生碎片。

修改

update 表名 set 字段名 =字段值，字段名2 = 字段2值（ where 条件）.

update emp3 set sal = 2000 where empNo = 7369;

查询

select 列名，… 列名 from 表名 where 条件；
select * from emp; 查询全部

select empno,ename,sal from emp;

例子：

1. 查询出工资为1300的员工的 编号，姓名，工资，奖金

select empNo,ename,sal ,comm
from emp
where sal = 1300;

**2. 查询出名字为KING的员工信息 **

select *
from emp
where ename ='KING';

注：大小写： 命令不敏感，数据敏感

运算符：

关系运算符： > < >= <= != =

判断null is null is not null

查询出每个员工的 总工资
select ename,sal,comm,sal*12+comm as "总工资" from emp 结果错误
任何包含null 的运算，结果都为null

nvl() if  ----->nvl() 相当于 if

select  ename,sal,comm,sal*12+nvl(comm,0) as "总工资" from emp

nvl2() if-else    ------> nvl2()  相当于 if - else

select  ename,sal,comm,sal*12+nvl2(comm,comm,0) as "总工资" from emp

查询员工奖金为300或者500的员工信息

select * from emp where comm in(300,500);

查询出员工的部门编号。

select  deptNo from emp;

去重 distinct
select distinct deptNo from emp;(去重的);
伪表 单行单列 dual
拼接 concat() ||

select concat('hello' ,'world') as "helloworld" from dual;

员工姓名 的薪水是 XXX

select ename || "的薪水"||sal as "一句话"
from  emp;

==== 更新=============

修改日期格式

查询 select * from v$nls_parameters;
修改 alter session set NLS_DATE_FORMAT = ‘yyyy-mm-dd’;

范围查询 between … and …(数字和日期)
查询工资在2000-3000之间的员工信息
select *
from emp
where sal between 2000 and 3000;

select * from emp where hiredate between ‘1981-2-20’ and ‘1981-05-01’;

模糊查询 like
% ： 表示任意个字符，包括零个
_: 表示一个任意字符

查询姓名中包含LL的 员工的信息 LLaaa aaLLaa aaaLL
select *
from emp
where ename like ‘%LL%’;

查询员工姓名中第二个字母为M的 员工信息
select *
from emp
where ename like ‘_M%’;

查询姓名的长度>=6的员工信息
select * from emp where ename like ‘______%’;

AA_BB
查询名字中包含下划线的员工信息
select * from emp where ename like ‘%_%’ escape ‘’;

排序 order by 列名,表达式 ,序号 默认按照升序排序

查询所有员工的信息并根据员工的工资排序
select *
from emp
order by sal;

降序排序 desc
select * from emp order by sal desc;

查询所有员工的信息按照总工资排序
select * from emp order by sal*12+nvl(comm,0);

查询所有员工的信息按照员工编号排序
select * from emp order by 1;

多列排序

查询员工信息 先根据deptNo,再根据 sal
select * from emp order by empNo,sal;
查询员工信息 先根据deptNo,再根据 sal 降序排序
select * from emp order by empNo desc,sal desc;
查询员工信息 先根据deptNo 升序排序,再根据 sal 降序排序
select * from emp order by empNo asc,sal desc;

根据奖金排序 null最大
select * from emp order by comm;

nulls last 无论什么排序，null都在最后面
select * from emp order by comm desc nulls last;

nulls first 无论什么排序，null都在最前面

将所有的业务员CLERK的信息 按照工资从低到高的输出
select *
from emp
where job=‘CLERK’
order by sal asc;

单行函数 操作一行，返回一行

1.字符函数
2.数值函数
3.日期函数
4.转换函数
5.通用函数

字符函数

lower upper initcap 小写/ 大写 / 首字母大写
heLLoworld
select lower(‘heLLoworld’) ,upper(‘heLLoworld’) ,initcap(‘heLLoworld’) from dual;
substr(str,begin) 截取从beigin开始 的字符串 从1开始
select substr(‘heLLoworld’,2) from dual;
substr(str,begin,len)
select substr(‘heLLworld’,2,3) from dual;
length 字符数 lengthb 字节数
select length(‘helloworld’) 字符，lengthb(‘helloworld’) 字节数 from dual;

select length(‘西安’) 字符，lengthb(‘西安’) 字节数 from dual;
一个汉字 UTF-8 占3个字节
GBK 占2个字节
lartin 占1个字节。

instr(a,b) 在a中查找b
select instr(‘helloworld’,‘ll’) from dual;

select instr(‘helloworld’,‘lll’) from dual;

左填充 lpad 右填充rpad
select lpad(‘hello’,10,‘‘),rpad(‘hello’,10,’’) from dual;
trim 去空格
select trim(’ hello ') from dual;

去掉Y
select trim(‘Y’ from ‘YYYhelloYYY’) from dual;

replace
select replace(‘helloworld’,‘l’,‘o’) from dual;

数值函数

round (数字, n) 四舍五入

select round(12.123,2) ,round(12.123,1),round(12.123,0),round(12.123,-1),round(12.123,-2) from dual;
trunc(数字，n) 截取
select trunc(12.123,2), trunc(12.123,1),trunc(12.123,0),trunc(12.123,-1),trunc(12.123,-2) from dual;
mod(a,b)
select mod(12,5) from dual;

日期函数

sysdate 当前时间

格式化时间 to_char(时间，格式)
select to_char(sysdate,‘yyyy-mm-dd’) from dual;
日期进行运算 + -
select (sysdate-1) 昨天,sysdate 今天, (sysdate+1) 明天 from dual;

计算出员工的工龄 年 月 日 星期
select ename,hiredate,sysdate,(sysdate - hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30月,
(sysdate-hiredate)/365 年 from emp;

months_between(日期1，日期2) 计算出日期1 和日期2相差多少月
select ename,hiredate,(sysdate-hiredate)/30,months_between(sysdate,hiredate) from emp;

add_months(时间，月数) 显示 月数后的时间
select add_months(sysdate,12) from dual;

last_day:当月的最后一天
select last_day(sysdate) from dual;

next_day(时间，‘星期n’) 下一个星期n是哪一天 不能使用这个周的时间
select next_day(sysdate,‘星期五’) from dual;

round 四舍五入
select round(sysdate,‘month’),round(sysdate,‘year’) from dual;

通用函数

nvl if
nvl2 if-else
coalesce 从左向右 找到第一个不为null 的值

select ename ,sal,comm,coalesce(comm,sal) from emp;

条件判断函数
decode(字段，条件1，表达式1，条件2，表达式2…)
查询出员工姓名 工作 涨前工资 涨后工资
select ename,job,sal “涨前工资”,decode(job,
‘ANALYST’，sal+1000,
‘PRESIDENT’，sal+ 800,
sal+300
) "涨后工资 "
from emp

case 表达式
case 字段 when 条件1 then 表达式1
when 条件2 then 表达式2
else 表达式3 end

select ename,job ,sal “涨前工资”, case job
when ‘ANALYST’ then sal+1000
when ‘PRESIDENT’ then sal+800
else sal+300
end
"涨后工资 " from emp;

转换函数 字符 <----> 数字/日期

隐式转换

字符 --> 数字
查询员工编号为7369的员工信息
select *
from emp
where empNo= ‘7369’;
字符 -->日期
select * from emp where hiredate =‘01-5月-81’;
日期 --> 字符
select sysdate,instr(sysdate,‘20’) from dual;

数字 -->字符
select * from emp where empNo = 7369;

显示转换
字符 --> 数字 to_number(字符，格式)
select to_number(‘￥123,456.7’,‘L999,999.9’) from dual;

数字 -->字符 to_char(数字，格式)
select to_char(‘123456.7’,‘L999,999.9’) from dual;

日期 -->字符 to_char(字符，格式)
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;

字符 -->日期 to_date(字符，格式)
select to_date(‘2016-09-19’,‘yyyy-mm-dd’) from dual;

多行函数 （组函数）操作多行，只返回一行
count() 总数
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
查询工作数量
select count(distinct job) from emp;

查询员工的工资总和
select sum(sal) from emp;

查询出入职时间最早和最晚的

select min(hiredate) 最早,max(hiredate) 最晚 from emp;

select min(comm),max(comm),avg(comm),sum(comm) from emp;
组函数在使用的时候自动滤空

分组 ： group by
select …from …where … group by …having… order by…
查询出各部门的平均工资
select avg(sal)
from emp
group by deptNo;

select deptNo,avg(sal)
from emp
group by deptNo;

先按照部门分组，再按照工作分组

select deptNo,avg(sal)
from emp
group by deptNo,job;

select deptNo,job,avg(sal)
from emp
group by deptNo,job;

查询出平均工资大于2000的部门
select deptNo,avg(sal)
from emp
where avg(sal)> 2000
group by deptNo;

where 中不能出现组函数

select deptNo,avg(sal)
from emp
group by deptNo
having avg(sal) > 2000;

分组查询时， 没在组函数中的列，必须放在group by 中。

查询 10号部门的平均工资
select deptNo,avg(sal)
from emp
group by deptNo
having deptNo = 10;

select deptNo,avg(sal)
from emp
where deptNo = 10
group by deptNo ;

计算出不同部门的不同工作的总工资
select deptNo,job ,sum(sal) from emp group by deptNo,job;
计算出不同部门的总工资
select deptNo,sum(sal) from emp group by deptNo;
计算 出所有员工的总工资
select sum(sal) from emp;

增强group by rollup(a,b)
= group by (a,b)
+group by a

• group by null

select deptNo,job,sum(sal) from emp
group by rollup(deptNo,job);

break on 字段 skip 数字 如果字段是重复的，只出现一次。
break on deptNo skip 1;
break on null;

子查询

查询工资比SCOTT高的员工信息

查询SCOTT的工资

select * from emp
where sal > (select sal
from emp
where ename=‘SCOTT’);

子查询 出现的位置： where select having from 不能够出现在group by 后面
select empNo,ename,(select job from emp where empNo= 7900) from emp;

查询出最低工资比20号部门的最低工资高的 部门编号
select deptNo,min(sal)
from emp
group by deptNo
having min(sal)> (select min(sal) from emp where deptNo = 20);

查询20号部门的最低工资
select min(sal) from emp where deptNo = 20

查询出比20号部门的最低工资高的部门的每个员工的工资
查询20号部门的最低工资
select min(sal) from emp group by deptNo having deptNo = 20;

查询出比20号部门最低工资高的部门编号
select deptNo,sal
from emp
where deptNo in(
select deptNo from emp group by deptNo having min(sal)>(
select min(sal) from emp group by deptNo having deptNo = 20));

子查询可以在from后面
select empNo,ename,sal from emp where sal >2500

select * from (select empNo,ename,sal from emp where sal >2500);

子查询和主查询可以不是同一张表

查询SALES部门的员工信息
select * from emp where deptNo = (select deptNo from dept where dname = ‘SALES’);
查找SALES部门的部门编号
select deptNo from dept where dname = ‘SALES’

查询出与SALES或者ACCOUNTING在同一部门的员工信息
select * from emp where deptNo in (select deptNo from dept where dname = ‘SALES’ or dname=‘ACCOUNTING’);

子查询中的运算符 any all
any 和集合中的任何一个值比较
查询工资比30号部门中的其中一个员工都高的员工信息
select * from emp
where sal>any (select sal from emp where deptNo = 30);
all 和集合中的所有值比较
查询工资比30号部门所有员工都高的员工信息
select * from emp
where sal>all(select sal from emp where deptNo = 30);

查询普通员工的信息
select * from emp where empNo not in(select mgr from emp )
not in 中不能包含null

select * from emp where empNo not in(select mgr from emp where mgr is not null )

查询老板的信息
select * from emp where empNo not in(select empNo from emp where mgr is not null);
伪列
rownum 数据的逻辑地址，在初始化的时候自动生成 不能使用大于等于
rowid 数据的物理地址

select e.rownum,e.rowid ,ename from emp e ;
伪列不属于任何一张表

create table mystudent(
stuNo number,
stuName varchar2(20),
stuAge number);

insert into mystudent values(1,‘zs’,11);
insert into mystudent values(1,‘zs’,11);
insert into mystudent values(2,‘zs’,21);
insert into mystudent values(2,‘zs’,21);
insert into mystudent values(3,‘zs’,31);

delete from mystudent
where rownum not in(
select min(rownum)
from mystudent
group by stuNo,stuName,stuAge);

delete from mystudent
where rowid not in(
select min(rowid)
from mystudent
group by stuNo,stuName,stuAge);

显示工资最高的前三条数据

select rownum,ename,sal from emp where rownum <=3 order by sal desc;
查询结果是错误的

select rownum,empNo,sal
from (select * from emp order by sal desc)
where rownum<= 3;

显示工资最高的7-9条数据 3
select rownum,empNo,sal
from (select * from emp order by sal desc)
where rownum<=9 andrownum >= 7;

select * from
(select rownum r,empNo,sal
from (select * from emp order by sal desc)
where rownum<=9) where r >=7;

表的别名可以被自身列使用，但是列的别名不能被自身表使用。
编写一个分页的步骤：
1，将数据排序
2.增加伪列rownum 起别名
3.将伪列和表看成一张新表。

假设每页显示10条数据，查询第n页显示的数据

第1页 1-10 010+1- 110
第2页 11-20 10+1 - 210
第3页 21-30 102+1 -310
第4页 31-40 103+1 -4*10

10（当前页数 -1）+1 - 当前页数*10*

select * from
(select rownum r,empNo,sal
from (select * from emp order by sal desc)
where rownum<=当前页数页面大小) where r >=页面大小（当前页数-1）+1;

当前页数 currentPage
页面大小 pageSize

MySql：

select * from 表名 limit (当前页数-1)*页面大小，页面大小；

交叉连接
针对两张表 列相加 行相乘
create table emp2(
empNo number,
ename varchar2(20),
deptNo number
);

insert into emp2 values(1,‘Jack’,1);
insert into emp2 values(2,‘Jane’,1);
insert into emp2 values(3,‘Tom’,2);

create table dept2(
deptNo number,
dname varchar2(20)
);
insert into dept2 values(1,‘aa’);
insert into dept2 values(2,‘bb’);

内连接 ：将笛卡尔集中的有意义的数据筛选出来就是内连接。(inner) join on
等值连接
select e.empNo,e.ename,d.deptNo,d.dName
from emp2 e inner join dept2 d
on e.deptNo = d.deptNo;
不等值连接
select e.empNo,e.ename,d.deptNo,d.dName
from emp2 e join dept2 d
on e.deptNo != d.deptNo;

外连接 (看from后面的表的顺序)

左外链接 以左表为基准，要求左表的数据全部出现，右表的数据进行匹配

select e.empNo,e.ename,e.deptNo,d.dName
from emp2 e left outer join dept2 d
on e.deptNo = d.deptNo;

select e.empNo,e.ename,e.deptNo,d.dName
from dept2 d,emp2 e
where d.deptNo = e.deptNo(+);

select e.empNo,e.ename,e.deptNo,d.dName
from emp2 e,dept2 d
where d.deptNo = e.deptNo(+);

右外连接
select e.empNo,e.ename,e.deptNo,d.dName
from dept2 d right outer join emp2 e
on d.deptNo = e.deptNo;

select e.empNo,e.ename,e.deptNo,d.dName
from emp2 e, dept2 d
where d.deptNo(+)=e.deptNo;

全外连接 = 左外 + 右外+去重
select e.empNo,e.ename,d.deptNo,d.dName
from emp2 e full outer join dept2 d
on e.deptNo = d.deptNo;

自连接 将同一张表通过加别名视为不同的表。
查询员工的姓名和他的领导的姓名 emp
select e1.ename “员工姓名”,e2.ename “领导姓名”
from emp e1,emp e2
where e1.mgr = e2.empNo;

select e1.ename “员工姓名”,e2.ename “领导姓名”
from emp e1 inner join emp e2
on e1.mgr = e2.empNo;

查询10号部门和20号部门的员工信息
select * from emp where deptNo = 10
UNION
select * from emp where deptNo = 20;

select deptNo,job,sum(sal) from emp group by deptNo,job
UNION
select deptNo,null,sum(sal) from emp group by deptNo
UNION
select null,null,sum(sal) from emp;

==== 还没完成后续继续。。。 ==========