oracle-12336621
一、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：查出来后表的显示格式不太好看 ，想调整
view plain copy
-- 设置行宽 show linesize; --先显示下行宽
-- 设置行宽 set linesize 150 --设置行宽为150个字符
-- 设置列宽 col ename format a8
-- 说明：表示设置ename这个字段的宽度为8个字符,a表示一个字符
-- 设置列宽 col sal format 9999
-- 说明：表示设置工资这个字段的宽度为4位,因为工资是数字所以用9表示一位数字
需求2：修改语句中的错误单词
view plain copy
select ename,sal form emp; -- 这里的from 敲错了
--(1)执行后出现的错误
--第 1 行出现错误:
--ORA-00923: 未找到要求的 FROM 关键字
--(2)修改错误
2 -- 输入出现错误的行号
c /form/from-- c就是change的意思(改变sql语句的错误部分)
/ --再次执行
需求3：计算员工当月的收入
view plain copy
-- null的问题, null参与运算结果都为null
select ename,sal,comm,sal+comm as 当月总收入 from emp;
说明：收入=基本工资+奖金
注意：null参与运算结果都为null
分析：上面结果有误--如果奖金comm是null值得话，和工资相加就变为null了，我们想要如果奖金为null就认为它是0
解决办法：用 nvl(comm,0) 来解决null值问题
view plain copy
select ename,sal,comm,sal+nvl(comm,0) as 当月总收入 from emp;
特点：是null的话就默认为0，否则还是其本身
需求4：修改sql语句
view plain copy
-- （1）ed 命令 将上一条sql 语句 自动粘贴到文本文件中,供我们进行再次编辑修改,然后保存
-- （2）/执行此语句
需求5：去除重复记录(distinct)
view plain copy
select distinct deptno from emp;
select distinct job,deptno from emp; -- 两个列组合起来看是没有重复得(好好体会)
说明：distinct作用于后面所有得列
三、Oracle中常用的知识
（1）伪表 dual
特点：像一张虚拟得表，它的存在只是为了sql语句语法完整。
view plain copy
select 3+2;--会报错
select 3+2 from dual;--成功执行
说明：select 3+2虽然跟表没关系,但是在Orcal中select语句后面必须要有from某张表，不写语法就报错,用伪表来代替一张表。
（2）注意：日期和字符串用单引号括起来，列的别名用双引号(或者不写)括起来
（3）关于处理字符串的一些函数(单行)
view plain copy
/
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）关于数字的处理
view plain copy
/
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
view plain copy
select sysdate from dual;
说明：默认格式是 06-2月-18
特点：没有时分秒,如果想要时分秒,可以指定日期格式
（6）需求：按照指定格式输出时间(格式化日期)
view plain copy
--格式化日期(必须转成字符串的形式) to_char()
select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') from dual;-- 24小时制
补充：日期的指定格式的处理
（6）日期可以运算可以加上天数
view plain copy
select sysdate-1 from dual;--昨天时间(单位是天)
select sysdate+1 from dual;--明天时间
注意：并不能改变系统的时间！！！
（7）计算一下员工入职时间
view plain copy
select ename,hiredate,(sysdate-hiredate)/365 as 工龄 from emp;
说明：可以根据需求保留指定的位数
注意：日期跟日期可以相减，但不能相加。
（8）杂乱(工作中常用)
view plain copy
/*
需求1：计算两个日期相差的月数 months_between()
例如 selectename,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：用 casewhenthen end语句来完成条件选择
view plain copy
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()函数来实现条件选择
view plain copy
--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）组合数
view plain copy
/组函数(相当于MySQL中的聚合函数)
max(sal) 求最大值
min(sal) 求最小值
count() 统计个数 --注意：指定字段与不指定字段的区别！！！
sum(sal) 求和
avg(sal) 求平均值*/
注意：组函数一般会自动忽略null值；
（11） groupby 和 having的分组查询
需求1：查询每个部门的总工资
view plain copy
select deptno,sum(sal) as 总工资 from emp group by deptno;
思路：先分组，再查询
需求2：查询每个部门的平均工资在两千以上的部门
view plain copy
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
思路：先分组，再查询，最后过滤
需求3：查询每个部门中不同岗位的总工资
view plain copy
select deptno,job,sum(sal) from emp group by deptno,job;
语法要求：select后面要查询的字段(除了组函数字段)，必须在 group by 后面也要有，不然语法报错
说明：分组条件可以有多个 ，这里是按部门和岗位分组的
需求4：查询 10 部门的平均工资
方式1：用where来筛选(查询时就指定条件)
view plain copy
select deptno,avg(sal) from emp where deptno=10 group by deptno;
方式2：先分组，再用having 筛选
view plain copy
select deptno,avg(sal) from emp group by deptno having deptno=10;
注意： where 和 having的区别是where 是在分组之前进行筛选, 而having 是对分组之后的结果集 进行再次筛选
需求5：按照部门统计各部门不同工种的工资情况按照下图的格式输出(group by 语句增强 )
思路：
view plain copy
--(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;
增强：
view plain copy
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
-- 思路：先按(deptno,job)分组，然后deptno分组，最后null分组
-- 逐次递减
结果：
需求：那上面的样子不好看我们可以排版，用下面一条命令
view plain copy
break on deptno skip 2；
-- break on deptno 表示相同的部门号只显示一次, skip 2 每个部门之间,相隔两个空行
/--斜杠表示执行上一条语句
（12）多表查询
（1）笛卡尔积(非条件)
需求1：查询员工的信息--员工编号、员工名称、工资、部门名称
view plain copy
select emp.empno,emp.ename,emp.sal,dept.deptno from emp,dept;
结果(未列出全表，部门展示)
特点：多张表没有条件的组合在一块，查出的数据(交叉组合)不准确也没有意义
（2）条件查询(需求同上--等值连接)
说明：员工信息在emp表，部门名称在dept表，所以需要两张表连接条件是：两张表的deptno 相等；
注意：连接条件个数=表的数量-1
（3）需求2：查询员工的工资级别(不等值连接)
分析：如果一个员工的工资大于等于下限， 小于等于上限，我们就可以知道工资的级别
view plain copy
--方式1
select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal>=salgrade.losal and emp.sal
页:
[1]