©2016-2024 Matools All rights reserved,
粤ICP备17059708号
一、Oracle数据库的登陆
(1)一般用户：使用Oracle自带的sqlplus，cmd--sqlplus scott/密码；
(2)管理员用户：cmd--sqlplus sys/密码 as sysdba
注意：客户端登陆的方法略
二、sqlplus的一些使用技巧
（1）host cls 清屏---屏幕信息太多
（2）spool D:\笔记.txt--可以用来保存在sqlplus中的的屏幕信息(包含自己的操作)
（3）spool off ---结束控制台的录制
注意：每次文件不能重名，否则会覆盖
（4）show user---显示当前用户
补充：在oracle中表属于某个用户，用户属于数据库 ；
（5）elect * from tab---显示当前用户下的所有的表名称
注意：不是table而是tab
需求1：查出来后表的显示格式不太好看 ，想调整
-- 设置行宽 show linesize; --先显示下行宽 -- 设置行宽 set linesize 150 --设置行宽为150个字符 -- 设置列宽
col ename format a8 -- 说明：表示设置ename这个字段的宽度为8个字符,a表示一个字符 -- 设置列宽 col sal format
9999 -- 说明：表示设置工资这个字段的宽度为4位,因为工资是数字所以用9表示一位数字
需求2：修改语句中的错误单词
select ename,sal form emp; -- 这里的from 敲错了 --(1)执行后出现的错误 --第 1 行出现错误:
--ORA-00923: 未找到要求的 FROM 关键字 --(2)修改错误 2 -- 输入出现错误的行号 c /form/from --
c就是change的意思(改变sql语句的错误部分) / --再次执行
需求3：计算员工当月的收入
-- null的问题, null参与运算结果都为null select ename,sal,comm,sal+comm as 当月总收入 from emp;
说明：收入=基本工资+奖金
注意：null参与运算结果都为null
分析：上面结果有误--如果奖金comm是null值得话，和工资相加就变为null了，我们想要如果奖金为null就认为它是0
解决办法：用 nvl(comm,0) 来解决null值问题
select ename,sal,comm,sal+nvl(comm,0) as 当月总收入 from emp;
特点：是null的话就默认为0，否则还是其本身
需求4：修改sql语句
-- （1）ed 命令 将上一条sql 语句 自动粘贴到文本文件中,供我们进行再次编辑修改,然后保存 -- （2）/执行此语句
需求5：去除重复记录(distinct)
select distinct deptno from emp; select distinct job,deptno from emp; --
两个列组合起来看是没有重复得(好好体会)
说明：distinct作用于后面所有得列
------------------------------
三、Oracle中常用的知识
（1）伪表 dual
特点：像一张虚拟得表，它的存在只是为了sql语句语法完整。
select 3+2;--会报错 select 3+2 from dual;--成功执行说明：select 3+2虽然跟表没关系,但是在Orcal中
select语句后面必须要有from某张表，不写语法就报错,用伪表来代替一张表。
（2）注意：日期和字符串用单引号括起来，列的别名用双引号(或者不写)括起来
（3）关于处理字符串的一些函数(单行)
/* 0.拼接字符串 concat() 例如 select concat('ab','cd') from dual; 1.转小写 lower() 例如
select lower('ABC') from dual; 2.转大写 upper() 例如 select upper('abc') from dual;
3.首字母大写 initcap() 例如 select initcap('abc') from dual; 4.截取字符串
substr('要截取的字符串','2'); 说明：参1:要截取的字符串, 参2 从第二个字符开始截取到末尾 例如 select
substr('abdccccc',2) from dual; 注意：参数2是1开头的不是0 5.截取字符串指定一段范围
substr('要截取的字符串',2,3); 说明：参数1:要截取的字符串、参数2:开始的位置(默认是1)、参数3:截取的长度 例如 SELECT
substr('abcdef',2,3) from dual; 6.获取字符的个数 length('abc') 例如:select length('abc')
from dual; 7.获取字符的字节数 lengthb('ab'); 例如: select length('a') from dual;
8.根据字符查找索引 instr('abc','a') 需求:查找a在abc中的索引 例如 select instr('abc','a') from
dual; 注意:索引从1开始数 9.左填充 lpad() 在字符串左端补上指定个数的字符 例如 select lpad('abc',5,'h') from
dual; 注意这个数是加上原来字符的个数 10.右填充 rpad() 在字符串有端补上指定个数的字符 例如 select rpad('abc',5,'h')
from dual; 注意这个数是加上原来字符的个数 11.去掉字符串两端指定的字符 trim(); 例如 select trim('a' from
'aabbaa') from dual; 注意语法格式 12.替换字符串 replace('abcd','c','aaaa') 需求：把abcd中的c 替换成
aaaa 例如 select replace('abcd','c','aaaa') from dual; */
说明：select 函数名(参数) from dual 来执行函数
（4）关于数字的处理
/* 1.四舍五入 round(3.14,2); 参数2:表示从小数点"四舍五入"后保留的位数 例如 select round(2.145,2) from
dual; 2.小数点后面保留几位数字 trunc(3.1415926,3) 参数：表示保留小数点位数 例如 select
trunc(3.1415926,2) from dual; */
注意：二者的区别！！！
（5）获取系统当前日期 sysdate
select sysdate from dual; 说明：默认格式是 06-2月-18 特点：没有时分秒,如果想要时分秒,可以指定日期格式
（6）需求：按照指定格式输出时间(格式化日期)
--格式化日期(必须转成字符串的形式) to_char() select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss')
from dual;-- 24小时制
补充：日期的指定格式的处理
（6）日期可以运算可以加上天数
select sysdate-1 from dual; --昨天时间(单位是天) select sysdate+1 from dual; --明天时间
注意：并不能改变系统的时间！！！
（7）计算一下员工入职时间
select ename,hiredate,(sysdate-hiredate)/365 as 工龄 from emp;
说明：可以根据需求保留指定的位数
注意：日期跟日期可以相减，但不能相加。
（8）杂乱(工作中常用)
/* 需求1：计算两个日期相差的月数 months_between() 例如 select
ename,hiredate,months_between(sysdate,hiredate) from emp; 需求2：添加月份
add_months(sysdate,12); 例如 select add_months(sysdate,12) from
dual;--一年13的月发工资吧！ 需求3：计算下一个星期几是几号 例如 select next_day(sysdate,'星期一') from
dual;--日期格式 select to_char(next_day(sysdate,'星期一'),'yyyy-mm-dd') from
dual;--字符串格式 需求4：计算这个月的最后一天是几号 last_day(sysdate) 例如 select last_day(sysdate)
from dual; 需求5：对日期的年、月进行四舍五入(不知道有啥用处？？？) round(sysdate,'year');
round(sysdate,'month'); 例如 select round(sysdate,'month') from dual;
需求6：格式化日期字符串 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day') 例如: select
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"是"day') from dual; --自定义格式 注意： "是"
字要加双引号 需求7： 把一个日期字符串转成日期 to_date() 例如: select
to_date('2018-05-12','yyyy-mm-dd') from dual; 注意：字符串的日期,跟日期格式要对应 需求8：格式化数字
to_char(sal,'L9,999.99') 说明：三位一隔(999-表示三位小数),保留两位小数('.'),L是货币代码(人民币) 例如 select
to_char(sal,'L9,999.99') from emp; 需求9：把一个字符串数字格式化成数字 to_number() 例如: select
to_number('1520.99','9999.99') from dual;--参数2：转换后的格式 需求10：通用函数nvl2(a,b,c)
特点：当a=null 时返回c,否则返回b 例如 select ename,sal,comm,sal+nvl2(comm,comm,0) from
emp;--回忆nvl(字段,0) 需求11：nullif(a,b) 特点：当a=b的时候返回null,否则返回a 例如 select
nullif('abc','abc') from dual; 用处：可以判断两个值是否相等 需求11：从左往右找到第一个不是null的值
coalesce(comm,sal) 例如: select comm,sal,coalesce(comm,sal) as "第一个不为空的值" from
emp; 注意：字段别名要用双引号引起来 */
（9）需求--如给不同职位的人加不同的工资
方式1：用 case when then end 语句来完成条件选择
select ename,sal as 涨前, case job when 'PRESIDENT' then sal+1000 when 'MANAGER'
then sal+800 ELSE sal+300 -- 注意这里的写法 end as 加后 from emp;
说明：SQL99的规范，条件语句语法，以case打头，以 end结尾
方式2：采用 decode()函数来实现条件选择
--decode(['参数1','参数2','参数3']...); --参1 判断的字段名，条件的值是什么，你想干什么，条件的值是什么，你想干什么，else
select ename,job,sal as 涨前,
decode('job','PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) as 涨后 from emp;
（10）组合数
/*组函数(相当于MySQL中的聚合函数) max(sal) 求最大值 min(sal) 求最小值 count(*) 统计个数
--注意：指定字段与不指定字段的区别！！！ sum(sal) 求和 avg(sal) 求平均值*/
注意：组函数一般会自动忽略null值；
（11） groupby 和 having的分组查询
需求1：查询每个部门的总工资
select deptno,sum(sal) as 总工资 from emp group by deptno;
思路：先分组，再查询
需求2：查询每个部门的平均工资在两千以上的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
思路：先分组，再查询，最后过滤
需求3：查询每个部门中不同岗位的总工资
select deptno,job,sum(sal) from emp group by deptno,job;
语法要求：select后面要查询的字段(除了组函数字段)，必须在 group by 后面也要有，不然语法报错
说明：分组条件可以有多个 ，这里是按部门和岗位分组的
需求4：查询 10 部门的平均工资
方式1：用where来筛选(查询时就指定条件)
select deptno,avg(sal) from emp where deptno=10 group by deptno;
方式2：先分组，再用having 筛选
select deptno,avg(sal) from emp group by deptno having deptno=10;
注意： where 和 having的区别是where 是在分组之前进行筛选, 而having 是对分组之后的结果集 进行再次筛选
需求5：按照部门统计各部门不同工种的工资情况按照下图的格式输出(group by 语句增强 )
思路：
--(1)求各部门每个工种的总工资 select deptno,job,sum(sal) from emp group by deptno,job;
--(2)求每个所有部门的总工资 select deptno,sum(sal) from emp group by deptno; --(3)求总工资
select sum(sal) from emp;
增强：
select deptno,job,sum(sal) from emp group by rollup(deptno,job); --
思路：先按(deptno,job)分组，然后deptno分组，最后null分组 -- 逐次递减
结果：
需求：那上面的样子不好看我们可以排版，用下面一条命令
break on deptno skip 2； -- break on deptno 表示相同的部门号只显示一次, skip 2 每个部门之间,相隔两个空行
/ --斜杠表示执行上一条语句
（12）多表查询
（1）笛卡尔积(非条件)
需求1：查询员工的信息--员工编号、员工名称、工资、部门名称
select emp.empno,emp.ename,emp.sal,dept.deptno from emp,dept;
结果(未列出全表，部门展示)
特点：多张表没有条件的组合在一块，查出的数据(交叉组合)不准确也没有意义
（2）条件查询(需求同上--等值连接)
说明：员工信息在emp表，部门名称在dept表，所以需要两张表连接条件是：两张表的deptno 相等；
注意：连接条件个数=表的数量-1
（3）需求2：查询员工的工资级别(不等值连接)
分析：如果一个员工的工资大于等于下限， 小于等于上限，我们就可以知道工资的级别
--方式1 select emp.ename,emp.sal,salgrade.grade from emp,salgrade where
emp.sal>=salgrade.losal and emp.sal<=salgrade.hisal; --方式2(between and) select
emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between
salgrade.losal and salgrade.hisal;
结果：
说明：用到两张表emp 表和 salgrade表
（4）需求3：查询部门号、部门名称以及这个部门有多少个人(内连接)
分析：要统计多少个人肯定要查emp表，就需要查询 emp 表和dept表
select dept.deptno,dept.dname,count(emp.empno) from dept,emp where
dept.deptno=emp.deptno group by dept.deptno,dept.dname;
结果：
思考：注意观察上面的查询结果，发现结果值罗列出了10、20、30 号3个部门的信息，但是一个40 号部门没列出来？
原因：因为40号部门没有人
内连接的特点：就是不符合条件的就不会显示出来
需求：即使这个部门没有人，我也要把部门编号和部门名称显示出来，人数以0显示
做法：那么我们就需要用外连接
（5）外连接(左外连接、右外连接)
需求1：对于某些不成立的记录（40号部门），任然希望包含在最后的结果中
左连接：
--写法：where e.deptno=d.deptno(+);当where
e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中
右连接：
--写法: where e.deptno(+)=d.deptno;--右外连接：当where
e.deptno=d.deptno不成立的时候，等号右边的表任然被包含在最后的结果中
说明：'+'号在左边表示是右连接，反之亦然
结果：
说明：上面的只是一种简便形式
方式2：
-- 采用 left outer join 表名 on 两张表的连接条件 outer 可以省略不写 select
dept.deptno,dept.dname,count(emp.empno) as 人数 from dept left join emp on
dept.deptno=emp.deptno group by dept.deptno,dept.dname;
方式1和方式2的部分对比：
--from dept,emp where dept.deptno=emp.deptno(+) --from dept left join emp on
dept.deptno=emp.deptno --说明：摘取部分(两种方式等价)
（6）需求：要查询员工姓名对应的老板姓名(自连接查询)
特点：通过表的别名给一张表起两个别名，将他视为两张表来进行查询
分析：因为这些信息都在一张表 emp中， 比如员工号7369的SMITH它对应的老板编号是(MGR)7902，而7902 又是员工FORD(7902)
那FORD对应的老板编号又是 7566，所以说一个员工既是某几个员工的老板，他也有自己的老板，所以我要查询这个员工的所对应的老板就可以使用自连接查询。
假设：有两张表一张员工表、一张老板表，如果员工的老板号=老板的员工号就表示这个员工是另外一个员工的老板
select e.ename as 员工姓名,b.ename as 老板姓名 from emp e,emp b where e.mgr=b.empno;
--注意这里给表起别名时尽量不要写as
结果：
注意：自连接不适合数据量大的表，因为自连接实际上是将一张表看成了两张表 ,那么两张表关联起来查询形成笛卡尔积那么查询的条数就很多
（7）层次查询(需求同上)
回头补充
说明：层次查询的是一张表，比用自连接查询要好
------
热门工具 换一换