oracle命令
常用命令
- 查看数据库版本
select * from v$version
- 清屏
host cls;
- sqlplus登录
sqlplus 用户名/密码 as sysdba; -- 数据管理员
sqlplus 用户名/密码; -- 普通用户
- 查看用户
show user;
- 查看系统表:
select * from tab;
select *from emp;(用户自带表)
-- 拉长cmd窗口查看表结构
set linesize 300; -- 设置行长
-- 设置页码大小
set pageSize 30;
- 查看语句
--select *from 表明(查询语句);
select * from empt;
select empno,ename,job from emp;
- 起别名
-- 起别名
select empno as "姓名", job as 工作, sal 月薪 from emp;
注意:
如果在起别名时遇到特殊符号,则需要使用引号引起来。(必须加引号情况:有空格,有from)
避免出现问题,建议都加上引号。
- 查看表结构
desc emp; -- 查看表结构
desc dept;
范围查询
-- between 小 and 大 从小到大
select *from emp where sal between 6000 and 10000;
-- between 一般时数字,日期
select * from emp where sal hiredate between '17-11月-81' and '17-09月-81';
注意:between 包含两头,between 6000 and 10000 等同于 6000 <= x <= 10000
模糊查询-Like
like
-
like 一般配合通配符使用:下划线_(任意一个字符),百分号%(任意个字符)
-- like 一般配合通配符使用:下划线_(任意一个字符),百分号%(任意个字符) select * from emp where Ename like '%C%'
-
like 支持字符,数字,日期
-- 姓名中第二个字母是M的员工信息: select *from emp where ename like '_M%'; -- 姓名中包含M的员工信息: select * from emp where ename like '%M%'; -- 姓名中包含下划线的 select * from emp where ename like '%a_%' escape 'a';
not in
not in 语法要求不能出现null,如果出现null结果为空
select * from emp where deptno not in(10,20,30,null);
-- 等价
select * from emp where deptno !=10 and depton !=20 and depton !=30 and depton !=null;
排序
order by 字段名|表达式|序号 (默认升序asc ,降序desc)
-
查询工资
-- 升序查询工资 select * from emp order by sal; -- 降序查询工资(名称) select empno ,ename,sal from emp order by sal desc; ---- 降序查询工资(序号) select empno ,ename,sal from emp order by 3 desc; -- 降序查询工资,null排在最后 select empno ,ename,sal from emp order by 3 desc nulls last; -- 多列排序 select * from emp order by sal desc , hiredate asc ;
自带函数
单行函数:一次操作一条单行数据
多行函数:一次操作多条多行函数
转换大小写
-- lower()转小写
select lower(job) from emp ;
-- upper()转大写
select upper(job) from emp;
-- nitcap()单词首字母转大写
select initcap('hello world') from dual;
统计数量
select count(*) from emp;
字符串操作
-- substr(str,begin,len)字符串截取
select substr('hello',4,2) from dual;
-- instr(a,b)a中找b
select instr('heloworld') from dual;
-- length()字符 lengthb()字节
-- utf-8编码格式:一个汉字/符号 占三个字节
-- gbk编码格式:一个汉字/符号 占两个字节
select length('西安') , lengthb('西安') from dual;
--lpad()rpad()左右填充
select lpad('hello',10,'a') 左, rpad('hello',10,'a') 右 from dual;
-- trim()去除任意字符
select trim( 'X' from 'XXXXXTIMEXXXXXX') from dual;
--- replace()替换
select replace( 'Hello' , '1' ,'a') from dual;
-- 日期格式 xxxx-xx-xxx xx:xx:ss
select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
数值型
-
round(数字,N位数)四舍五入保留N位小数
select round(67.183,2)一,round(67.1541,1)二,round(2471.14,0)三 from dual;
-
trunc(数字,N位数)舍尾
select trunc(67.183,2)一,trunc(67.1541,1)二,trunc(2471.14,0)三 from dual;
-
mod()求余
select mode(13,3) from dual;
日期函数
-
sysdate日期函数
select sysdate from dual;
-
to_char(日期,格式)
select to_char(sysdate ,'yyyy-mm-dd') from dual;
-
sysdate日期±数字
select sysdate+1 from dual; select ename,(sysdate-hiredate) from emp;
-
months_between(日期1,日期2)相差几月
select ename,months_between(sysdate,hiredate) from emp;
-
add_months(日期1,增加几月)增加月份
select add_months(sysdate,12) from dual;
-
last_day()当月最后一天
select last_day(sysdate) from dual;
-
next_day()下星期几是哪一天
select next_day(sysdate,'星期五') from dual;
-
round(sysdate,‘month’)年月四舍五入
select round(sysdate,'month') ,round(sysdate,'year') from dual;
-
修改日期
select * from emp where hiredate = '17-12月-80'; select * from v$nls_parameters; alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd';
通用函数
判断相等
-
nval/nvl2
-
nullif(a,b)
-- a和b是否相等 不相等返回a select nullif('abc','abcxxxx') from dual;
coalesce
coalesce () - 从左到右,找第一个不为null
-- 从左往右 找第一个不为null的
select comm,sal,coalesce(comm,sal) from emp;
条件判断函数
decode函数
select ename ,job ,sal 涨前,decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+500, sal+300) 涨后 from emp;
-- 统计男女人数
select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') from employees;
case when表达式
- 涨工资
select ename ,job ,sal 涨前,case job when 'PRESIDENT' then sal+1000 when 'MANAGER' then sal+500 else sal +300 end 涨后 from emp;
- 统计男女人数
-- 统计男女人数
SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1为男生,2位女生*/
ELSE NULL
END) 男生数,
COUNT (CASE WHEN sex = 2 THEN 1
ELSE NULL
END) 女生数
FROM students GROUP BY grade;
- 两表联查
-- 两表联查
SELECT T2.*, T1.*
FROM T1, T2
WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
T1.SOME_TYPE LIKE 'NOTHING%'
THEN 1
WHEN T2.COMPARE_TYPE != 'A' AND
T1.SOME_TYPE NOT LIKE 'NOTHING%'
THEN 1
ELSE 0
END) = 1
- group by 用法
-- group by case when 用法
SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class, -- 别名命名
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;
转换函数
隐式转换
源类型 | 目标类型 |
---|---|
nvchar2/nchar 等字符 | number/date |
number/date | nvarchar2/nchar 等字符 |
-- 字符转数据
select * from emp where empno =7788;
select * from emp where empno ='7788';
-- 日期转换
select * from emp where hiredate ='17-12月-80';
显示类型转换
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D95RNXFD-1641452031415)(…/Oracle.assets/image-20211223160022798.png)]
-
to_date()
格式 简介 示例 yyyy 数字表示年 2021 year 英文标识年 twenty twelve mm 数字表示月 04 month 月(与系统语言相关) 9月 dy 星期几(与系统语言相关) 星期一 day 星期几(与系统语言相关) 星期一 dd 每个月的第几天 02
-- 字符转日期
select to_date('2021-04-23', 'yyyy-mm-dd') from dual;
-- 日期转字符
select to_char(sysdate,'yyyy') from dual;
select to_number(to_char(sysdate,'yyyy'),9999) from dual;
-
to_number()
格式 说明 9 一位数字 0 0 $ 美元符号 L 本地货币符号(¥) . 小数点 , 千位符 -- 字符转数字(注意保持一一对应) select to_number('¥123,456.7','L999,999.9') from dual;
-
to_char()
-- 数字转字符 select to_char( 123456.7 ,'L999,9999.9' ) from dual;
多行函数
多行变成一行 别名:组函数,聚合函数
-
count()
select deptno from emp; select depto from emp where deptno is not null; -- (自动排空) select count(deptno) from emp; -- (distinct去重) select count (distinct deptno) from emp; select count (distinct deptno) from emp;(distinct去重)
-
max()
select max(sal) 最大工资 from emp;
-
min()
select min(sal) 最小工资 from emp
-
avg()
select avg(sal)平均工资 from emp;
-
sum()
select sum(sal) 总工资 from emp;
-
整合
select count(*) 员工总数,sum(sal) 总工资,max(sal) 最大工资,min(sal) 最小工资,avg(sal) 平均工资 from emp;
分组函数
-
group by
-- 求各个部门总工资:对部门分组 dept 10 20 30 40 select deptno,sum(sal) from emp group by deptno; -- 求各个部门平均工资 select deptno ,job,avg(sal) from emp group by deptno ,job;
-
having
select deptno ,job,avg(sal) from emp group by deptno ,job having avg(sal)>5000;
列查询
-- 列的计算
select empno ,ename ,sal ,sal*12 from emp;
-- 控制行和列 注意:字符串/字符 日期需要用单引号引住。
select empno,ename from emp;
select empno,job,sal from emp where sal>=1000 and sal<=5000;
运算符
-
操作运算符
操作运算符:+ - * / % -
关系运算符
关系运算符:> >= < <=
=(等于符号有三种含义):
判断是否等于
用来赋值
!=或<>(这两个都表示不等于)注意这两个符号只能判断非null,如果是null必须用isnull或者is not null
select * from emp where mgr = null;
逻辑运算符
and
select * from emp where mgr =7788 and job = 'CLERK';
or
select * from emp where mgr =7788 or job = 'CLERK';
not
select * from emp where not (mgr = 7788 and job = 'CLERK');
-
where 执行顺序
从右往左
- null的计算
注意:任何数据和null计算,结果为null
需要对null进行处理 null变0
-
nvl(需要处理的字段,处理为)
-
nvl2(需要处理的字段,需要处理的结果,处理为)
select empno ,ename ,sal ,comm ,sal*12 +comm from emp where empno >=7499; select ename ,comm from emp; select ename ,nvl(comm,0) from emp;
nvl、nvl2
类似为java中的if语句翻译为如果comm为null变为0
select ename ,nvl(comm,0) from emp;
select ename,nvl2(comm,1000,0) from emp;
连接符
select concat('Hello','World') from emp;
-- oracle 学习时提供。他是一个单行单列的零时表
select concat('Hello','World') from dual;
连接
交叉连接(笛卡尔积)
select * from emp ,edpt;
内连接
符合条件留下,不符合条件删除
select * from emp e,dept d where e.deptno = d.deptno;
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e,dept d where e.deptno != d.deptno;
外连接
左外连接
左外连接:以左表为基准(左全显示),去匹配右表数据,如果匹配成功,则显示,匹配不成功,显示部分(无数据部分 用Null填充)。
oracle 独有(左外连接+号写在右边)
select * from emp e left outer join dept d on e.deptno = d.deptno;
select * from emp e,dept d where e.deptno = d.deptno(+);
右外连接
右外连接:以右表为基准(右全显示),去匹配左表数据,如果匹配成功,则显示,匹配不成功,显示部分(无数据部分 用Null填充)。
例:select * from emp e right outer join dept d on e.deptno = d.deptno;
oracle 独有(右外连接+号写在左边)
select * from emp e,dept d where e.deptno(+) = d.deptno;
全外连接
全外连接=左外连接+右外连接 -去重
自连接
自连接:将一张表通过别名“视为”不同的表
select e.ename ,b.ename from emp e,emp b where e.mgr = b.empno;
问题:查询员工姓名,以及该员工的的领导姓名
select level ,empno,ename ,mgr from emp connect by prior empno = mgr start with mgr is null order by level;
缺点:耗费性能
如何优化:层次连接
子查询
-- 比scott工资高的员工信息->scott工资->比他高的员工信息
select sal from emp where ename='SCOTT' ;
select sal from emp where sal>3000;
-- 合并两条语句
select sal from emp where sal>(select sal from emp where ename='SCOTT');
注意:
-
子查询可以出现的位置:where ,select ,having ,from ;不能写在group by后面
-- where select sal from emp where sal>(select sal from emp where ename='SCOTT'); -- select select empno 第一列 ,ename 第二列 ,(select job from emp where empno = 7369) 第三列 from emp; -- having 查询最低工资比10号部门最低工资高的部门编号 select min(sal) from emp where deptno = 10; select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno =10); -- from 相当于修改了表结构 select * from (select empno,ename,sal*12 from emp);
-
主查询和子查询可以是也可以不是同一张表
select deptno from dept where dname = 'DNAME'; select * from emp where deptno =(select deptno from dept where dname='SALES');
-
子查询可以使用单行操作符(大于,等于,小于…),多行操作符(in)
-- 查询工资比30号部门中,任何其中一个员工高的员工信息 select min(sal) from emp where deptno = 30; select * from emp where sal > (select min(sal) from emp where deptno = 30); -- any:只要有一个 select * from emp where sal >any(select sal from emp); -- 查询工资比30号部门中,全部员工高的员工信息 select max(sal) from emp where deptno = 30; select * from emp where sal > (select max(sal) from emp where deptno = 30); -- all:全部 select * from emp where sal >all(select sal from emp); select * from emp where deptno in (select deptno from dept where dname='SALES' or dname ='ACCOUNTING');
any
-- any:只要有一个
-- -- 查询工资比30号部门中,任何其中一个员工高的员工信息
select * from emp where sal >any(select sal from emp);
all
-- all:全部
-- 查询工资比30号部门中,全部员工高的员工信息
select * from emp where sal >all(select sal from emp);
注意事项
大小写问题
select <u>ename,empno</u> from emp;
select <u>ename,empno</u> from emp where sal =1600;
注意:
-
如果是命令/关键字:不敏感
-
如果是数据:敏感
null 的问题
select * from emp where empno not in(select mgr from emp where mgr is not null);
注意事项:
-
查询领导为指定编号的员工信息
-- null查询不出来 select * from emp where mgr in(7566,7698,NULL); -- 相等于 select * from emp where mgr = 7566 or mgr = 7698 or mgr = NULL;
注意:
a. in : = or = or
b.null的判断是is null
-
not in : !=or
select * from emp where mgr ! =7698 and mgr != 7566 and mgr !=NULL;
NULL:自身特性:如果是!=NULL则查询不出任何数据
is NULL
is not NULL
rowid和rownum伪列
伪列:不存于任何一张表但是会被所有表共享
rownum :逻辑伪列:1 2 3…
rowid:物理伪列:真实的物理硬盘存放位置
18为组成 :
前6位:数据对象编号
依次往后数3位:数据文件编号
依次往后数6位:数据块编号
依次往后数3位:数据行号
rownum
-
不同sql语句(查询的结果数据不一样)执行时rownum值不一致
-
在相同sql语句(查询的结果数据一样)执行时rownum值不变(再一次查询时,第一次产生,之后保持不变)。
-- 工资最好的前三的员工信息
错例: ---emp表取前三 ---临时表
select ename ,sal from emp where rownum <=3 order by sal desc;
select ename ,sal from emp order by sal desc having rownum <= 3;
错误分析
select ename ,sal from emp where rownum <=3;
rownum | ename | sal |
---|---|---|
1 | zs | 7000 |
2 | ls | 6000 |
3 | ww | 5000 |
4 | zl | 9000 |
--产生临时表进行排序
order by sal desc ;
rownum | ename | sal |
---|---|---|
4 | zl | 9000 |
1 | zs | 7000 |
2 | ls | 6000 |
3 | ww | 5000 |
解决办法:
-- 工资最好的前三的员工信息
-- 方式一
select * from emp order by sal desc;
select rownum ,ename ,sal from (select * from emp order by sal desc ) where rownum <=3;
-- 方式二:
select * from (select ename ,sal from emp order by sal desc) where rownum <= 3;
删除重复数据
分析:
rownum 和rowid 都可以
问题:一条SQL语句去掉重复数据
思路:根据编号分组(将重复数据分组),然后在每组中保留一个(最小值or或最大)
-- 通过rowid和rownum只适合少量数据
delete from emp where rowid =1;
delete from emp where rownum =1;
-- 使用所有场景
delete from mystudent where rowid not in(select stuno,min(rowid) from mystudent group by stuno);
删除重复数据案例
-- 创建表
create table mystudent
(
stuno number,
stuname varchar(10),
age number
);
insert into mystudent values(1,'zs',24);
insert into mystudent values(1,'zs',24);
insert into mystudent values(2,'ls',23);
insert into mystudent values(2,'ls',23);
insert into mystudent values(3,'ww',25);
insert into mystudent values(3,'ww',25);
insert into mystudent values(4,'zl',24);
-- 使用所有场景
delete from mystudent where rowid not in(select stuno,min(rowid) from mystudent group by stuno);
delete from mystudent where rownum not in(select stuno,max(rownum) from mystudent group by stuno);
rowid
意义:rowid
规律:根据插入顺序依次递增
练习题
集合运算:
-
union(并集):返回各个查询的所有记录,不包括重复记录;
-
union all(并集):返回各个查询的所有记录,包括重复记录;
-
intersect(交集):返回两个查询的共有的记录;
-
minus(补集):返回包含在第一个查询中,但不包含在第二个查询中的记录;
-- 查询所有员工的年工资,所在部门的名称,按年薪从低往高排序 select sal*12+nvl(comm,0) 年工资,dname 部门名称 from emp,dept where emp.deptno = dept.deptno order by sal*12+nvl(comm,0) asc; select sal*12+nvl(comm,0) 年工资 ,dname 部门名称 from emp e ,dept d where e.deptno = d.deptno order by 年工资 asc;
-
查询所有员工的编号,姓名,及其上级领导的编号,姓名。显示结果按领导的年工资降序;
select * from emp e1, emp e2 where e1.mgr = e2.empno; select e1.empno 员工编号,e1.ename 员工姓名,e2.empno 领导编号,e2.ename 领导姓名,e2.sal*12+nvl(e2.comm,0) 领导年工资 from emp e1 left join emp e2 on e1.mgr = e2.empno order by e2.sal*12+nvl(e2.comm,0) desc; select e1.empno 员工编号,e1.ename 员工姓名,e2.empno 领导编号,e2.ename 领导姓名 e2.sal*12+nvl(e2.comm,0) 领导年工资 from emp e1, emp e2 where e1.mgr = e2.empno(+) order by e2.sal*12+nvl(e2.comm,0) desc;
-
查询所有非销售人员:工作名称,以及从事同一工作员工的月工资之和,要求月工资之和大于5000;输出结果按月工资之和降序排列;
分析:
分析要查询那些字段,表
job sum(sal)
group by job
having sum(sal) > 5000
order by sum(sal) desc
2.分析连接条件
没有select job 工作 ,sum(sal) 总工资 from emp where job != 'SALESMAN' group by job having sum(sal) > 5000 order by sum(sal) desc;
-
查询所有领到奖金和没领到奖金的员工人数,平均工资
-- 领到奖金 select count(*),avg(sal) from emp where comm is not null and comm > 0 -- 未领到奖金 select count(*),avg(sal) from emp where comm is null or comm = 0; -- 连接 select count(*),avg(sal) from emp where comm is not null and comm > 0 union select count(*),avg(sal) from emp where comm is null or comm = 0;
-
报表:查询总工资,各个部门的总工资,各个部门中各个工作的总工资
select null,sum(sal) from emp; select deptNo,sum(sal) from emp group by deptno; select deptNo,sum(sal) from emp group by deptno,job; select null,sum(sal) from emp union select deptNo,sum(sal) from emp group by deptno union select deptNo,sum(sal) from emp group by deptno,job;
-
查询每种工作的最低工资,以及领取该工资的员工姓名;
-- select 后的字段,如果不在聚合函数中,就必须出现在group by后面 select min(sal),ename from emp group by job; -- 错误sql, select min(sal),job from emp group by job; -- 正确 -- 查询每种工作最低工资 select min(sal),job from emp group by job; select t.job,t.minSal,e.ename from emp e, (select min(sal) minSal,job from emp group by job) t where t.minSal = e.sal and t.job = e.job;
-
查询工资不超过2500的人数最多的部门名称
-- 查询部门名称 select deptno,dname from dept; -- 关联员工表查询员工信息 select empno,ename,d.deptno,d.dname from emp e ,dept d where e.deptno = d.deptno; -- 查询各个部门中工资不超过2500人数 select count(*) from emp where sal<= 2500 group by deptno; select d.deptno,d.dname from emp e ,dept d where e.deptno = d.deptno and e.sal < 2500 group by d.deptno,d.dname having count(*) = (select max(count(*)) from emp where sal <= 2500 group by deptno);
-
查询出 管理员工人数最多的人的名字和他管理的人的名字。
--1.查询管人最多的人 --a.首先根据mgr分组:根据领导分组 select count(*)from emp group by mgr; --b.分组里面取最大值 select max() --c.合并 select max(cn) from (select count(*) cn from emp group by mgr) ; --2.查询管人最多的人的姓名 --a.筛选出人数最多分组的管理者编号 --人数最多= select max(cn) from (select count(*) cn from emp group by mgr) ; select mgr from emp group by mgr having count(*) = (select max(cn) from (select count(*) cn from emp group by mgr));--7698 --b.求出管理者姓名,被管理者名字 select ename from emp; select b.ename,e.ename from emp e inner join emp b on e.mgr = b.empno where e.mgr = (select mgr from emp group by mgr having count(MGR) = (select max(cn) from (select count(MGR) cn from emp group by mgr))); -- 管理员工人数最多的人的名字 select * from emp where empno = (select mgr from emp group by mgr having count(*) = (select max(count(*)) from emp group by mgr)); -- 他管理的人的名字。 select * from emp where mgr = (select mgr from emp group by mgr having count(*) = (select max(count(*)) from emp group by mgr));
-
统计各个年份的入职人数,以及总入职人数
--1.各个年份获取 --hiredate:只取年 date ->varchar select to_char(hiredate,'yyyy') from emp; --2.各个年份入职人数 --case ... decode 语法:decode(条件,值1,返回值1,值2,返回值2...else) --1980年入职人数 --decode(to_char(hiredate,'yyyy'),1980,1,0) --1981年入职人数 --decode(to_char(hiredate,'yyyy'),1981,1,0) --1987年入职人数 --decode(to_char(hiredate,'yyyy'),1987,1,0) --3.入职总人数 --select count(*) select count(*) 总人数 ,sum(decode(to_char(hiredate,'yyyy'),1980,1,0)),'1980',sum(decode(to_char(hiredate,'yyyy'),1981,1,0)),'1981',sum(decode(to_char(hiredate,'yyyy'),1987,1,0)),'1987' from emp;