用户名：scott
密码：tiger
1.解锁scott用户，要有超级管理员权限
```
alter user scott account unlock;
```
2. 解锁scott密码[此句也可以用来重置密码]
```
alter user scott identified by tiger;
```
3. 切换scott用户
4. 我们重点掌握：EMP表
7. Oracle中的函数
7.1 单行函数
7.1.1 数值函数
作用于一行，返回一个值
1. 把小写的字符转换成大小的字符
```
select upper('yes') from dual;
```
2. 把大写字符变成小写字符
```
select lower('YES') from dual;
```
3. 首字母大写函数
4.字符串连接函数
5.字符串替换函数
6.获取字符串长度函数
7.round
四舍五入，第二个参数表示保留几位小数
```
select round(26.18) from dual;
select round(26.18,1) from dual;
```
-1表示向前保留1位（整数位第一位舍入）
```
select round(26.18,-1) from dual;
```
8. trunc
直接截掉（不考虑舍入）
```
select trunc(56.16, 1) from dual;
select trunc(56.16, -1) from dual;
```
9. mod 求余数
```
select mod(10,3) from dual;
```
7.1.2 日期函数
1. 需求：查询出EMP表中所有员工入职距离现在几天
```
select sysdate-e.hiredate from emp e
```
sysdate：目前系统时间
日期可以加减
2. 算出明天此刻：明天这个时候的时间
```
select sysdate+1 from dual;
```
作用于多行，返回一个值
1.查询总数量
select count(1) from emp;
等价于
select count(empno) from emp;
效果同：
select count(*) from emp;
但是推荐写：
select count(1) from emp;
2.
```
select sum(sal) from emp; --- 查询工资总和
select max(sal) from emp; --- 查询最高工资
select min(sal) from emp; --- 最大工资
select avg(sal) from emp; --- 平均工资
```
多行函数一共就5个。
8. 条件表达式
1. 给员工表的员工起别名
```
select e.ename,
case e.ename
when 'SMITH' then '大猫'
when 'ALLEN' then '小狗'
when 'WARD' then '小牛'
else '无名'
end
from emp e;
```
1.1 else可以省略
```
select e.ename,
case e.ename
when 'SMITH' then '大猫'
when 'ALLEN' then '小狗'
when 'WARD' then '小牛'
--else '无名'
end
from emp e;
```
1.2
```
case e.ename
when 'SMITH' then '大猫'
when 'ALLEN' then '小狗'
when 'WARD' then '小牛'
--else '无名'
end
```
中间的内容没有逗号，查询的是一列的内容。
2. 判断emp表中员工工资，高于3000显示高收入，1500-3000中等，
其余低收入
```
select e.sal,
case
when e.sal>3000 then '高收入'
when e.sal>1500 then '中等收入'
else '低收入'
end
from emp e;
```
注意：
e.sal不能放在case后面，因为是范围判断，直接跟在case后面是等于判断
不满足 when e.sal>3000 then '高收入'时，才会走到下面的
when e.sal>1500 then '中等收入'
所以中等收入不用谢小于3000的条件
上面两个案例是条件表达式的通用写法，mysql和oracle通用。
3. orcle专用的条件表达式
每个字段都要加 ,
```
select e.ename,
decode(e.ename,
'SMITH', '大猫',
'ALLEN', '小狗',
'WARD' , '小牛',
'无名')
from emp e;
```
3.1 有一个问题：列名太长了
给列加一个别名
注意：别名不加引号，或者加双引号
oralce中除了起别名，其他都用单引号。
为了保证代码的可移植性强，条件表达式一般用通用的写法
9 分组查询
1. 查询出每个部门的平均工资
```
select e.deptno, avg(e.sal)
from emp e
group by e.deptno;
```
注意：
分组查询中，出现在group by后面的原始列，才能出现在select后面
没有出现在group by后面的列，想在select后面，必须加上聚合函数。
这样就是错的： e.ename原始列
```
select e.deptno, avg(e.sal), e.ename
from emp e
group by e.deptno;
```
2.查询出平均工资高于2000的部门信息
```
select e.deptno, avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal)>2000;
```
3. 别名问题：所有条件都不能使用别名判断
给平均工资取一个别名
```
select e.deptno, avg(e.sal) asal
from emp e
group by e.deptno
having avg(e.sal)>2000;
```
注意：这样是错的
所有条件都不能使用别名判断
因为：先走的是条件having asal > 2000，所以此时别名根本识别不出来。
4.查询出每个部门中工资高于800的员工的平均工资
4.1 先得到所有工资大于800的员工部门信息和工资信息
```
select e.deptno, e.sal
form emp e
where e.sal>800
```
4.2 按部门分组
每个部门都是一行，所以不能在显示单个员工的工资信息了,
要显示一个部门的总体上的属性：平均工资。
```
select e.deptno, avg(e.sal)
from emp e
where sal > 800
group by e.deptno;
```
注意：
1.where是过滤分组前的数据，
2.having是过滤后的数据
5.
查询每个部门中工资高于800的员工的平均工资，其中平均工资大于2000的部门
```
select e.deptno, avg(e.sal)
from emp e
where sal > 800
group by e.deptno
having avg(e.sal)>2000;
```
10 多表查询的一些概念
1.笛卡尔积
```
select *
from emp e, dept d;
```
查询出来的数据量是：m*n。大部分是无用的数据
2.等值连接---就是内连接的新写法。
不是为了查询某一个表的全部符合条件的信息（用外连接）。
```
select *
from emp e, dept d
where e.deptno=d.deptno;
```
3.内连接
```
select *
from emp e inner join dept d
on e.deptno = d.deptno;
```
效果同等值连接，推荐写等值连接。
3.为什么要用外连接：
因为我要查询的结果是上面两张表中的其中一张的完整结构。
4.左连接还是右连接：都行
但是要明确：
left join显示的是左侧的全部信息
right join显示的是右侧的全部信息
5. 写法
```
select *
from emp e right join dept d
on e.deptno = d.deptno;
```
10.2 要查询所有员工信息，以及员工所属的部门信息
分析一下：
因为每个员工都有部门，所以不会出现上面10.1中的有个部门没有员工的情况。
```
select *
from emp e left join dept d
on e.deptno = d.deptno;
```
10.3 Oracle专用的外连接写法
1.
```
select *
from emp e, dept d
where e.deptno(+) = d.deptno;
```
2.
```
select *
from emp e, dept d
where e.deptno = d.deptno(+);
```
2. 在得到这13个员工的部门名
```
select e1.ename 员工姓名, d1.dname 员工所属部门, e2.ename 领导名
from emp e1, emp e2, dept d1
where e1.mgr = e2.empno
and e1.deptno = d1.deptno;
```
3. 再得到13个领导的部门名
```
select e1.ename 员工姓名, d1.dname 员工所属部门, e2.ename 领导名, d2.dname 领导所属部门
from emp e1, emp e2, dept d1, dept d2
where e1.mgr = e2.empno
and e1.deptno = d1.deptno
and e2.deptno =d2.deptno;
```
4. 来看一种错误情况
```
select e1.ename 员工姓名, d.dname 员工所属部门, e2.ename 领导名, d.dname 领导所属部门
from emp e1, emp e2, dept d
where e1.mgr = e2.empno
and e1.deptno = d.deptno
and e2.deptno =d.deptno;
```
分析一下：
查询结果是11条，正确结果应该是13条。所以里面有过滤。
我们来看条件：
and e1.deptno = d.deptno
and e2.deptno =d.deptno;
那么显然这是一个并列且条件。
意思是：员工和领导的部门编号要一样的。
所以部门表也应该看成两张表来连接查询。
12 子查询
12.1 子查询返回一个值
1.案例：查询出工资和scott一样的员工信息。
1.先查询出SCOTT的工资（区分大小写的）
```
select sal from emp where ename = 'SCOTT'
```
2.查询出工资和scott一样的员工信息。
```
select * from emp where sal =
(select sal from emp where ename = 'SCOTT')
```
2. 写 = 时的隐患
我们先查询出SCOTT的工资，如果一个公司又两个人都叫做SCOTT，那么第一步
查询出来的是一个集合（多行）
那么第二不再写 = 显然是错的，所以应该写in，更保险
```
select * from emp where sal in
(select sal from emp where ename = 'SCOTT')
```
12.2 子查询返回一个集合
1.案例：查询出工资和10号部门任意员工一样的员工信息。
1. 先查询出10号部门员工的工资集合
```
select sal from emp where deptno = 10;
```
2. 查询出工资和10号部门任意员工一样的员工信息。
```
select * from emp where sal in
(select sal from emp where deptno = 10);
```
12.3 子查询返回一张表
1. 案例：查询出每个部门最低工资和最低工资员工姓名，和该员工所在部门名称
分析：
目前没有一张表直接有 部门最低工资信息，所以我们要先得到这张表。
1. 先查询每个部门最低工资
```
select deptno, min(sal)
from emp
group by deptno
```
2. 再用第1步得到的每个部门的最低工资表和部门表联合查询，得到部门信息。
```
select t.deptno, t.msal, d.dname
from(select deptno, min(sal) msal
from emp
group by deptno) t, dept d
where t.deptno = d.deptno
```
3. 用第1步得到的每个部门的最低工资表和员工表表联合查询，得到员工信息。
```
select t.deptno, t.msal, e.ename
from(select deptno, min(sal) msal
from emp
group by deptno) t, emp e
where t.msal = e.sal
```
4. 3张表进行联合查询
```
select t.deptno, t.msal, e.ename, d.dname
from (
select deptno, min(sal) msal
from emp
group by deptno) t, emp e, dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno = d.deptno --- 将两张临时表进行联合查询
```
13 分页查询
1. rownum行号
当我们做select操作的时候，没查询出一行记录，就会在该行上加上一个行号。
行号从1开始一次递增。
只有select会出来rownum。
rownum不能加表名：emp.rownum
1.1 查看一下rownum
```
select rownum,e.* from emp e
```
1.2 加上排序
```
select rownum,e.* from emp e order by e.sal desc
```
这个时候rownum是乱序的，是因为先执行的查询的同时rownum就出来了，
然后再排序，显然顺序就会被打乱。
所以排序会打乱rownum的顺序。
1.3 解决办法
先排序，在加rownum : 在将排序好的查询一遍
```
select rownum, t.*
from(
select * from emp e order by e.sal desc
) t;
```
小结：如果rownum涉及到排序，但是还要使用rownum的话，可以再次查询排序好的临时
表来的到顺序的rownum。
2. emp表工资倒序排列后每页5条记录，查询第二页。
```
select rownum, t.*
from(
select * from emp order by sal desc
) t where rownum<11 and rownum>5;
```
注意：
where先于select执行，而rownum是从1开始的。
那么就不可能同时满足rownum<11 and rownum>5这个条件。
所以最后的查询结果是个空。
结论：rownum不能写上大于一个正数。
2.2
解决办法：先查询出rownum<11的数据，这个时候rownum就已经标注上去了。
再对这个临时表加一个选择：rownum > 5。
注意：必须是临时表中的rownum。
```
select * from(
select rownum rn, t.*
from(
select * from emp order by sal desc
) t where rownum<11
)tt where rn>5;
```
