oracle 视频笔记1(select)

下面是用scott用户测试的。

如果不知道当前用户是什么用户,可以输入: show user ,这样它就显示出当前用户是什么用户了.
如果想编辑已经输入到sqlplus中的代码,可以输入“ed”，然后回车就能弹出一个记事本，记事本里面有上一步输入的代码,修改代码并保存后关闭记事本,最后在sqlplus中输入“/”并击回车就执行了。

1.不等于号是:<>
如:select ename, sal from emp where deptno <> 10;

2.SQL中的字符串大小写不一样
如:select ename ,sal from emp where ename = 'CLASD';
与 select ename ,sal from emp where ename = 'clasd';
是不一样的.

3.between 与 >=和<=
如: select ename, sal from emp where sal between 800 and 1500; 的意思与
select ename, sal from emp where sal >= 800 and sal <= 1500; 一样

4.把空值选出来
如: select ename, sal, comm from emp where comm is null;

5.把非空值选出来
如: select ename, sal, comm from emp where comm is not null;

6.in的用法
如:select ename, sal, comm from emp where sal in (800, 1500, 2000); 意思是把sal等于800 或者1500或者2000的 ename、sal、comm 拿出来。
select ename, sal, comm from emp where sal not in (800, 1500, 2000); 意思是把sal不等
800或者1500或者2000的ename、sl、comm 拿出来。

7.日期:1981年2月20日以后的表示为: select ename, sal, hiredate from emp where hiredate > '20-2月-81'; 或:select ename, sal, hiredate from emp where hiredate > '20-2月-1981';
先转换格式再取日期与时间:select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');

8.或者的连接用"or"
如:select ename, sal, comm from emp where sal>1000 or deptno=10;

9.模糊查找:like表示模糊查找,"%"表示零个或多个字母,"_"代表一个字母.
如:select ename from emp where ename like '%ALL%'; 表示查找符合ALL前面和后面有零个或多 个字母的ename;
select ename from emp where ename like '_A%';表示查找符合每二个字母是A的ename;
查找带有%的字符串,用转义字符"\",如:select ename from emp where ename like '%\%%';表示查找 带有"%"的字符串。也可以自己设置 转义字符，如：select ename from emp where ename like '%$%' escape '$'; 表示用$作为转义字 符.

10.排序:order by 。
降序：desc， 如：select * from dept order by deptno desc;
升序：asc， 默认情况下是升序，如：select empno, ename from emp order by empno asc;
或：select empno, ename from emp；
升降综合：select ename, sal, deptno from emp order by deptno asc, ename desc;表示先按 deptno的升序,如果有相同的deptno,再按照ename的降序排序。

11.select lower(ename) from emp; 表示取出的ename的字段的字母都是小写字母。

12.取子字符串(substr)。如：select substr(ename, 2, 3) from emp;表示取出ename中的从每2个字母开始的3个字母的字符串。

13.将chr形转换成ascii码。如：select chr(65) from dual; 表示将65转换成ascii码（A）。
将ascii形转换成chr码。如：select ascii('A') from dual; 表示将ascii码（A）转换成65。

14.select round(25.562) from dual;表示取到25.562的整数部分
select round(25.562, 2) from dual;表示取到25.562的两位小数
select round(25.562, -1) from dual;表示取到25.562的十位

15.to_char：
如：select to_char(sysdate, 'YYYY-HH-DD HH:MI:SS') from dual;表示将sysdate时间格式转化为YYYY-HH-DD HH:MI:SS
select to_char(sysdate, 'YYYY-HH-DD HH24:MI:SS') from dual;表示将sysdate时间格式转化为YYYY-HH-DD HH:MI:SS 其中时间是24小时制

16.select ename, sal*12 + comm from emp;这句SQL语句中,如果comm是空值,则加起来的结果也是空值 。为了避免这种情况,可以select ename, sal*12 + nvl(comm, 0) from emp; nvl(comm, 0)的意思是如果comm是空值,则用0来取代它.

17.求最大值(max)。如：select max(sal) from emp; 表示从emp中求出最大的sal.

18.求最小值(min)。如：select min(sal) from emp; 表示从emp中求出最小的sal.

19.求平均值(avg)。如：select avg(sal) from emp; 表示从emp中求出平均的sal.

20.精确到小数点后两位。如：select to_char(avg(sal), '99999999.99') from emp;

21.四舍五入到小数点后两位。如：select round(avg(sal), 2) from emp;

22.求和。如：select sum(sal) from emp; 表示sal的和为sum(sal)。

23.求出表中一共有多少条记录。
如：select count(*) from emp; 表示emp表中一共有多少条记录。
select count(*) from emp where deptno=10; 表示求出emp表中deptno=10的记录一共有多少条。
select count(*) from emp; 与select count(comm) from emp; 是不一样的。前一个*表示所有的记录；后面一个表示comm的记录。
select count(distinct deptno) from emp; 表示除去重复的deptno后一共有多少条记录。

24.分组函数（group)。
如：select deptno, avg(sal) from emp group by deptno; 表示按deptno分组显示sal的平均值。
select deptno, job, max(sal) from emp group by deptno, job; 表示按照deptno和job的组合分组显示deptno, job, max(sal) 。
select ename, max(sal) from emp;这句SQL是不对的，因为如果最大的sal有不同的ename,那么就会出错（由于输出是只有一条数据的）；所以要改成select ename from emp where sal = (select max(sal) from emp);

25.having。where是对单条数据进行限制的，如果要限制查询出来后的多条数据，一般用having。如果一条SQL中有where和其他限制条件，那么先执行where限制条件。
如：select avg(sal), deptno from emp group by deptno having avg(sal) >2000; 表示通过deptno取出acv(sal)和deptno后，再从取出的数据中查询出avg(sal) 大于2000的数据。

26.限制词的执行顺序：where --> group by--> having --> order by
如：select deptno, avg(sal) from emp where sal > 1200 group by deptno having avg(sal) > 1500 order by avg(sal) desc; 表示按组deptno 取出sal > 1200 的deptno, avg(sal)，然后选出avg(sal)大于1500的deptno, avg(sal)，最后按agv(sal)的倒序排列。

27.select ename, sal from emp join (select max(sal) max_sal, deptno from emp group by deptno) t on ( emp.sal = t.max_sal and emp.deptno = t.deptno); 表示取出各组（deptno)中sal最大的ename和sal。

28.自连接,给一张表起两个别名,把一张表当成两张表用。
如：select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

29.左外连接(left (outer)) outer可以省略。
如：select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.deptno);
表示把左边e1的没有符合连接的数据也拿出来（全部数据）。

30.右外连接（right (outer)) outer可以省略。
如：select ename, dname from emp e right outer join dept d on (e.deptno = d.deptno); 表示把右边d的没有符合连接的数据也拿出来（全部数据）。

31.全外连接(full)。
如：select ename, dname from emp e full join dept d on (e.deptno = d.deptno); 表示把表e表d的两个没有正确连接的数据全部取出来。

32.部门平均薪水的等级：select deptno, avg_sal, grade from (select avg(sal) avg_sal, deptno
from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal
and s.hisal);
部门平均的薪水等级：select deptno, avg(grade) from (select deptno, grade, sal from emp join
salgrade s on (emp.sal between s.losal and s.hisal)) t group by deptno

33.三个表建立关系。
如：select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%'；

34.雇员中有哪些人是经理人：select ename from emp where empno in (select distinct mgr from emp);

35.不用组函数求最高薪水
select distinct sal from emp where sal not in (select distinct e1.sal from
emp e1 join emp e2 on (e1.sal < e2.sal));

36.求出平均薪水最高的部门编号
写法一：select deptno from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal, deptno from emp group by deptno)
);
写法二：select deptno from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno);

37.求出平均薪水最高的部门名称
select dname from dept where deptno =
(
select deptno from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal, deptno from emp group by deptno)
)
)

38.平均薪水的等级最低的部门名称
方法一：select dname, t1.deptno, grade, avg_sal from
(
select deptno, avg_sal, grade from
(select deptno deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on
(t.avg_sal between s.losal and s.hisal)
) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
(
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on
(t.avg_sal between s.losal and s.hisal)
)
)

方法二：select dname from dept where deptno =
(
select deptno from
(select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on
(t.avg_sal between s.losal and s.hisal)
)
where avg_sal =
(select min(avg_sal) from
(select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on
(t.avg_sal between s.losal and s.hisal)
)
)
)

39.使用视图简化SQL
将下面这段代码简化:
平均薪水的等级最低的部门名称
方法一：select dname, t1.deptno, grade, avg_sal from
(
select deptno, avg_sal, grade from
(select deptno deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on
(t.avg_sal between s.losal and s.hisal)
) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
(
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on
(t.avg_sal between s.losal and s.hisal)
)
)
简化为:授权
conn sys/520419 as sysdba
grant create table, create view to scott;
conn scott/tiger
创作视图
create view v$_dept_avg_sal_info as
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on
(t.avg_sal between s.losal and s.hisal)

简化:
select dname, t1.deptno, grade, avg_sal from
v$_dept_avg_sal_info t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from v$_dept_avg_sal_info
)

40.比普通员工的最高薪水还要高的经理人名称
select ename from emp where empno in (select mgr from emp where mgr is not null)
and sal >
(
select max(sal) from emp where empno not in
(select mgr from emp where mgr is not null)
)

41.rownum,其实每个表中都有一个隐藏的列，是用来标记行号的，但是它只能与<、<=在一起用，不能与>、>=、=在一起用。
如：select empno, ename from emp where rownum <=5;

42.求薪水最高的前5名雇员。
方法一：select ename, sal from
(select ename, sal from emp order by sal desc)
where rownum <= 5
方法二：select ename, sal, r from
(
select ename, sal, rownum r from
(select ename, sal from emp order by sal desc)
)
where r <=5

43.求薪水最高的第6到每10名雇员（重点掌握）。
select ename, sal, r from
(
select ename, sal, rownum r from
(select ename, sal from emp order by sal desc)
)
where r >=6 and r<=10