@[toc]
理论方面
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;
==== 还没完成后续继续。。。 ==========
