本机连接oracle 数据库？
打开cmd ----> 不同的用户有不同的用户名密码 ----> sqlplus scott/tiger(安装oracle数据库预留的用户)
---- 回车 ---- 提示连接成功…………………………
《基本查询以及过滤查询》
1、oracle sql 优化原则？
1.尽量使用列名代替*；
2.where 判断的顺序：右 ----> 左；
3.理论上，尽量使用多表查询；
4.尽量不好使用集合运算；
2、mysql 与 oracle 开启事务的区别？
mysql 开启事务是 start transaction；
oracle 自动开启事务。
3、什么是sql 与sqlplus？
sql 是 不可缩写的关键字；
sqlplus 是可以缩写的。
4、order by 作用于后面所有的列 desc只作用于离他最近的一列
order by 后面 + 列、表达式、别名、序号
如：select empno,ename,sal,sal*12 年薪 from emp order by 年薪 desc;
select empno,ename,sal,sal*12 年薪 from emp order by 4 desc; 根据查询的字段名的顺序，
若字段名总共有四个，写成第五个则会报错(ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目 )。
select * from emp order by deptno,sal desc; 多个列排序。
5.host cls ---- 清屏
6.展示行宽 ---- show linesize ---- 展示效果linesize 80
设置行宽 ---- set linesize 120
设置列宽 ---- col ename for a8 员工姓名这个列是8个字符
col sal for 9999 一个9代表一个字符
7.sql 中null值问题？
1.包含null值得表达式都为null；
如：select * from empno,ename,sal,sal*12,sal*12+nvl(comm,0) from emp;
sal*12+nvl(comm,0)这一列 comm 为null的值，查出来的结果不正确。
2.null 永远 != null；
如：select * from emp where comm=null;
正确写法：select * from emp where comm is null;
3.如果集合中含有null，不能使用not in ，但可以使用in。
如：select * from emp where deptno not in(10,20,null); 运行结果会报 未选定行。
select * from emp where deptno in(10,20,null);
4.null 的排序。
如：select * from emp order by comm asc; 带有null的值的排序，null值显示在最后。
select * from emp order by comm desc; 降序 null在最上面，有值的在最下面，不符合要求，修改为
select * from emp order by comm desc nulls last;
原因：oracle中null值最大。
5.组合函数（多行函数）自动滤空，可以用嵌套滤空函数来屏蔽他的滤空功能。
如：select sum(comm)/count(*) 一，sum(comm)/count(comm) 二,avg(comm) 三
from emp ; count(*) 与 count(comm) 值不相同。
select sum(comm)/count(*) 一，sum(comm)/count(nvl(comm,0)) 二
from emp;
8.nvl 表达式？
nvl(num,0) as "别名" ---- 若num不为null值为num,否则值为0。
nvl2(a,b,c) 当a=null时候，返回c,否则返回b.
select sal*12 + nvl2(comm,comm,0) from emp;
9.distinct 去掉重复记录
distinct作用于后面所有的列
如：select distinct deptno from emp;
10.日期格式 select * from v$nls_parameters;
alter session set NLS_DATE_FOTMAT='yyy-mm-dd';
alter session set NLS_DATE_FORMAT='DD-MON-RR';
11. between and 在什么什么之间
如：select * from where sal between 1000 and 2000;
1.含有边界
2.小值在前，大值在后
12.in(a,b,c) 是a和b和c
如：select * from emp where deptno not in(10,20)
13.like 模糊查询 %
如：select * from emp where ename like 'S%'; 查询以S打头的员工。
select * from emp where ename like '____';查询名字是四个字的员工。
select * from emp where ename like '%_%';查询名字中含有下划线的员工。
《单行函数》
1.字符函数
(1)lower()--转小写,upper()--转大写,initcap()--首字母大写
如：select lower('HELLO WORLD!') 转小写,
upper('hello')转大写,
initcap('hello world!')首字母转大写
from dual;
(2)substr(a,b) 从a 中第b位开始截取。
如：select substr('Hello World',3) 子串 from dual;
结果为：llo World。
substr(a,b,c) 从a中，第b位开始取，取c位即可。
如：select substr('Hello World',3,4) 子串 from dual;
结果：llo 。
(3)length 字符数 lengthb 字节数
如：select length('Hello World') 字符,lengthb('Hello World') 字节
from dual;
结果：11 11
select length('北京') 字符,lengthb('北京') 字节 from dual;
结果：2,4
(4)inst(a,b) 在a中查找b，找到返回下标，否则返回0。
(5)lpad()左填充 rpad()右填充
如：select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;
结果：******abcd ,abcd******
(6)trim() 去掉前后指定的字符
如：select trim('H' from 'Hello WorldH') from dual;
结果：ello World
(7)replace() 替换
如：select replace('Hello World','l','*') from dual;
结果：He**o Wor*d
(8)round() 四舍五入
如：select round(45.926,2) 一，round(45.926,1) 二，
round(45.926,0) 三，round(45.926,-1) 四，
round(45.926,-2) 五
from dual;
结果： 45.93 ， 45.9 ，46 ，50 ，0
(9)trunc() 截断
如：select trunc(45.926,2) 一, trunc(45.926,1) 二,
trunc(45.926,0) 三,trunc(45.926,-1) 四,
trunc(45.926,-2) 五 from dual;
2.
(1) 查询当前系统的时间
select sysdate form dual; 16-7月 -16
(2) 格式化显示时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
(3) 昨天、今天、明天
select (sysdate - 1) 昨天, sysdate 今天,(sysdate+1) 明天 from dual;
(4) 计算员工的工龄：天 星期 月 年
select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,
(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
from emp;
(5)注：日期不允许+日期 ，否则报错。
(6)months_between 相差的月数，是一个准确值
如：select ename,hiredate,(sysdate-hiredate)/30 近似值，
months_between(sysdate,hiredate) 准确值
from emp;
(7)add_months() 向指定的日期上加上相应的月数
select add_months(sysdate,73) from dual; 当前日期73个月之后。
(8) last_day 月的最后一天
select last_day(sysdate) from dual;
(9) next_day() 指定的下一个星期几
select next_day(sysdate,'星期六') from dual; 今天是星期六，下一个星期六就是下周六所在日期。
select next_day(sysdate,'星期日') from dual; 今天是星期六，下一个星期日就是明天所在的日期。
(10) next_day的应用：每个星期一自动备份表中的数据
1>分布式数据库
2>快照
(11) 年月的round 和 trunc
select round(sysdate,'month'),round(sysdate,'year') from dual;
3.转换函数
显示转换：
NUMBER -- to_char -- CHARACTER ;
CHARACTER -- to_number -- NUMBER ;
CHARACTER -- to_date -- DATE;
DATE -- to_char -- CHARACTER
(1) 显示：年月日 时分秒 今天是星期几
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
(2) 查询员工薪水：俩位小数 千位符 本地货币代码
select to_char(sal,'L9,999.99') from emp;
* to_char()函数经常使用的几种格式：
** 9 -- 数字
** 0 -- 零
** $ -- 美元符
** L -- 本地货币符号
** . -- 小数点
** , -- 千位符 意思是每三位加个逗号,
(3) nullif(a,b) 当a=b的时候，返回null,否则返回a。
select nullif('abc','abc') 值 from dual;
(4) coalesce 从左到右找到第一个不为null的值
select comm,sal,coalesce(comm,sal) “第一个不为null的值”;
(5) case end：以case开头以end结束, 就是if - then -else
涨工资，总裁1000 经理800 其他400
select ename ,job ,sal 涨前，
case job when 'PERSIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后
from emp;
(6) descode： 和case 实现的功能一样
select ename,job,sal 涨前,
decode(job,'PERSIDENT',sal+1000,
'MANAGER',sal+800,
sal+400) 涨后
from emp;
《多行函数》
1.工资总额
select sum(sal) from emp;
2.人数
select count(*) from emp;
3.平均工资
select sum(sal)/count(*) 一，avg(sal) 二 from emp;
4.平均奖金
select sum(comm)/count(*) 一，sum(comm)/count(comm) 二,avg(comm) 三
from emp ;
第一个的平均成绩和后面的俩不一样。原因是：count(*) 和 count(comm) 的值不相同。
注：null值 组合函数（多行函数）自动滤空，可以用嵌套滤空函数来屏蔽他的滤空功能
select sum(comm)/count(*) 一，sum(comm)/count(nvl(comm,0)) 二
from emp;
5.group by 分组。
求每个部门的平均成绩
select deptno,avg(sal) from emp group by deptno;
6.多个列的分组。
select deptno,job,sum(sal) from emp group by deptno,job order by 1;
注：select 列表中所有未包含在组函数中的列都应该包含在group by 子句中；
包含在group by 子句中的列 不必包含在select 列表中。
7.求部门工资大于2000的部门
select deptno ,avg(sal) from emp group by deptno having avg(sal) > 2000;
注：where 后面不能有多行函数(即组合函数)。
8.求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 * from tab;
查询表结构：desc 表名
emp 表：
empno,ename,job,mgr(number),hiredate(入职日期)，sal(月薪)，comm(奖金)，
deptno(部门编号)
dept 表：
deptno(部门编号),dname(部门名称)，loc
salgrade表：
grade ,losal ,hisal
1.等值连接
查询员工信息 ： 员工号 姓名 月薪 部门编号
select e.empno,e.ename,e.sal,d.dname from emp e,dept d
where e.deptno = d.deptno;
2.不等值连接
查询员工信息 ：员工号 姓名 月薪 工资级别
select e.empno,e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
3.外连接
按部门统计员工人数：部门号 部门名称 人数
select d.deptno 部门编号,d.name 部门名称,count(e.empno) 人数
from emp e,dept d
where e.deptno = d.deptno
group by d.deptno,d.name;
4.希望：对于某些不成立的记录，仍然希望包含在最后的结果中
左外连接：
当where e.deptno=d.deptno 不成立的时候，等号左边的表仍然被包含在最后的结果中。
写法：where e.deptno=d.deptno(+)
右外连接：
当where e.deptno=d.deptno不成立的时候，等号右边的表仍然被包含在最后的结果中。
写法：where e.deptno(+)=d.deptno
select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname;
5.自连接
注：通过表的别名，将同一张表视为多张表。自连接不适合操作比较大的表。
查询员工信息 ： 员工姓名 员工老板的姓名
select e.ename 员工姓名,b.ename 老板姓名 from emp e,emp b
where e.mgr=b.empno;
《子查询》
* 为什么要学习子查询？
** 不能一步求解。
子查询注意的问题：
1.括号
2.合理的书写风格
3.可以在主查询的where select having from 后面都可以防止子查询
4.不可以在group by 后面放置子查询
5.强调from 后面的子查询
6.主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用即可
7.一般不在子查询中排序，但在top-n分析问题中，必须对子查询排序
8.一般先执行子查询，再执行主查询；但相关子查询例外
9.单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
若子查询返回结果为单行即为单行子查询；若返回结果为多行即为多行子查询
10.子查询中的null值问题
-- 查询工资比SCOTT高的员工
--SCOTT 的工资
select sal from emp where ename = 'SCOTT'
--比3000高的员工
select * from emp where sal > 3000
select * from emp
where sal > (select sal from emp where ename='SCOTT');
-- 3.可以在主查询的where， select ，having ，from 后面都可以放置子查询
select empno,ename,sal,(select job from emp where empno=7839) 第四列 from emp;
select deptno,MIN(sal)
from emp
group by deptno
having MIN(sal) > (select MIN(sal)
from emp
where deptno = 10 );
-- 5.强调from 后面的子查询
-- 查询员工信息：员工号 姓名 月薪
select *
from (select empno,ename,sal from emp);
-- 查询员工信息：员工号 姓名 月薪 年薪
select *
from (select empno,ename,sal,sal*12 annsal from emp);
--6.主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用即可
-- 查询部门名称是sales的员工
select *
from emp
where deptno=(select deptno from dept where dname='sales');
或
select e.*
from emp e,dept d
where e.deptno=d.deptno and d.name='sales';
注：理论上，尽量使用多表查询。
-- 7.一般不在子查询中排序，但在top-n分析问题中，必须对子查询排序
top-n分析问题：按顺序排序（不论是升序或降序）取前n条，即是top-n分析问题。
-- 8.一般先执行子查询，再执行主查询；但相关子查询例外。
相关子查询：
-- 9.单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
select ename ,job,sal
from emp
where job = (select job from emp where empno=7566)
and sal > (select sal from emp where empno=7782);
注：1.单行子查询只能使用单行操作符 = > ……
2.主查询和子查询的关系是一对多的关系 。
select ename ,job,sal from emp where sal = (
select MIN(sal) from emp);
多行操作符：in ,any ,all
-in ：在集合中
-- 查询部门名称是sales 和 accounting
select * from emp
where deptno in (
select deptno from dept where dname='sales' or d.name='accounting')
select * from emp ,dept d
where e.deptno=d.deptno and (dname='sales' or d.name='accounting')
-any 和集合中的任意一个值比较
-- 查询工资比30号部门任意一个员工高的员工信息
select * from emp
where sal > any(select sal from emp where deptno=30);
或
where sal > (select min(sal) from emp where deptno=30);
-all 和集合中的所有值比较
-- 查询工资比30号部门所有员工高的员工信息
select * from emp
where sal > all(select sal from emp where deptno=30);
-- 多行子查询中的null
-- 查询是老板的员工
select * from emp
where empno in (select mgr from emp);
-- 查询不是老板的员工
select * from emp
where empno not in(select mgr from emp where mgr is not null);
《集合运算》
--查询部门号10和20的员工
1.select * from where deptno in(10,20);
2.select * from where deptno = 10 or deptno = 20;
3.集合运算
select * from emp where deptno = 10;
+
select * from emp where deptno = 20;
注意：集合运算符
1.union/union all 相当于数学中的并集。
union:若A、B有交集，交集的部分只取一次。
union all :若A、B有交集，交集的部分取俩次。
注：若A、B无交集，则union 与 union all 是一样的。
上面的集合运算可替换为：
select * from emp where deptno = 10;
union/union all
select * from emp where deptno = 20;
注意：1.参与运算的各个集合必须列数相同且类型一致
2.采用第一个集合作为最后的表头
3.order by 永远在最后
4.括号
-- sql 执行时间的开关
打开：set timing on
关闭：set timing off
-- sql前的提示符，显示相应的当前时间。
-- set time on
-- set time off