马士兵Oracle

Client:

图形版sqlplus, 命令行sqlplus, 浏览器, plsql developer

超级管理员登录:

sqlplus / as sysdba

解锁用户:

alter user scott account unlock;

SQL(Structure Query Language)语言两套标准: SQL1992，SQL1999

SQL语言有4类需要学习，分别为，查询语句，DML语句， DDL语句以及事务控制语句。

1. 查询语句只有SELECT语句

2. DML(Data Manipulation Language)语句，数据操作语言

3. DDL(Data Defination Language)语句，数据定义语言

4. 事务控制语句

学习SELECT语句之前，需要熟悉数据，有哪些表，每张表里边是哪些内容。

desc emp; --查看字段，以及相应的类型。(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

desc dept;

(DEPTNO, DNAME, LOC)

desc salgrade;

(GRADE, LOSAL, HISAL)

select ename, sal*12 from emp;

desc dual;

select * from dual;

select 2*3 from dual;

select sysdate from dual;

select ename, sal*12 annual_sal from emp;

select ename, sal*12 "annual sal" from emp;

任何含有空值的数学表达式，最后结果都是空值。

select ename, comm from emp;

select ename, sal*12 + comm from emp;(不能这么计算，因为有的comm为空值)

字符串连接符: select ename||sal from emp; (||相当于Java中字符串连接符的+号)。

在sql语句里边，表示字符串的时候，都是这样表示的: select ename||'ajtdmw' from emp; (也就是说在sql语句里边，字符串表示，两个单引号引起来的一大串字符)

字符串里边有单引号:select ename || 'abc''def' from emp; (用两个单引号表示)

把重复的字段值去掉，只保留一个：select distinct deptno from emp;

把字段组合重复的值去掉： select distinct deptno, job from emp;

where 过滤条件： select * from emp where deptno = 10; select * from emp where ename = 'CLARK'; select ename, sal from emp where sal > 1500; select ename, sal from emp where deptno <> 10; select ename, sal, deptno from emp where deptno <> 10; select ename, sal from emp where ename > 'CDA'; (和Java的字符串比较相似) select ename, sal from emp where sal between 800 and 1500; 相当于, select ename, sal from emp where sal >= 800 and sal <= 1500;

取出某个字段为空值的记录： select ename, sal, comm from emp where comm is null; 取出某个字段不为空值的记录： select ename, sal, comm from emp where comm is not null;

in 语句： 取出薪水值为800， 1000， 或者2000，的记录： select ename, sal, comm from emp where sal in (1000, 1500, 2000); 取出员工姓名为KING, SMITH, 或者 ABC 的记录： select ename, sal, comm from emp where ename in ('KING', 'SMITH', 'ABC');

日期处理： 必须按照正确的格式写，获得日期格式： select sysdate from emp; 日期处理相关的sql语句：select ename, sal, hiredate from emp where hiredate > '10-MAR-81';

多条件查询：　 select ename, sal from emp where deptno = 10 and sal > 100; select ename, sal from emp where deptno = 10 or sal > 100;

not in 语句： select ename, sal from emp where sal not in (800, 1500);

Java通配符： '.' 代表一个字母，'*' 代表0个或多个字母，'?' 代表0个或者1个，'+' 号带表1个或者多个 SQL语言模糊查询：%代表一个或者多个，_代表一个字母 取出名字包含A的所有记录：select ename from emp where ename like '%A%'; 取出名字第二个字母是A的所有记录： select ename from emp where ename like '_A%';

取出名字中含有%号，那该怎么办呢? 可以使用转义字符来实现 select ename from emp where ename like '%\%%';

可以自己指定转义字符，比如以下sql语句指定$符号为转义字符，会取出名字中含有%号的所有记录： select ename from emp where ename like '%$%%' escape '$';

排序 ORDER BY： 默认升序排列：select * from dept order by deptno; 降序排列：select * from dept order by deptno desc; 升序排列：select empno from emp order by empno asc; ORDER BY 和WHERE 结合使用： select empno, ename from emp where deptno <> 10 order by empno asc; 先按deptno进行升序排列，再在内部按ename进行降序排列： select ename, sal, deptno from emp order by deptno asc, ename desc;

一句话概括以上内容，取出来他的名字，他的年薪，过滤条件是ename第二个字母不能是A，并且薪水值大于800，取出来之后的数据按照薪水的倒序排列： select ename, sal*12 annual_sal from emp where ename not like '_A%' and sal > 800 order by sal desc;

常见的sql函数：lower(), upper(), substr(), char(), ascii(), round()

lower(): 取出名字中第二个字母为小写a的记录(忽略大小写)： select ename from emp where lower(ename) like '_a%'; select ename from emp where ename like '_a%' or ename like '_A%';

substr()：截子串 从第二个字符开始截，一共截3个字符： select substr(ename,2,3) from emp;

char(): 把一个数字转换成为相对应的字母 把ASCII 65转换成相应的字符： select chr(65) from dual; --A

ascii(): 把字符转换成相应的ASCII select ascii('A') from dual; --65

round(): 四舍五入 select round(23.652) from dual; --24 select round(23.652,2) from dual; --23.65 select round(23.652,1) from dual; --23.7 select round(23.652,-1) from dual; --20

to_char(): 把数字或日期转换成为某种特定的格式 对数字进行处理： select to_char(sal,'$99,999.9999') from emp; -- 99后面跟了一个, 叫千分位符号，9代表的是一位数字，如果这一位上没有数字则不进行显示，但是小数点后面强制显示，如800前面没有数字，则显示结果为$800.0000， 1600则显示为$1,600.0000， 转换成为小数点后面为4位数字，小数点前面为5位数字且带有千分位符号。 select to_char(sal,'$00,000.0000') from emp; -- 0 也代表一位数字，但是如果这一位上没有也会进行显示，如800，则显示为 $00,800.0000

对日期进行处理： date类型既包含日期又包含时间。 select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp; --1980-12-17 12:00:00 select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') from dual; --2016-03-11 12:26:03 select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; --2016-03-11 00:26:41

to_date(): 把相关的特定格式的字符串转换成为日期这种类型 select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:5 6', 'YYYY-MM-DD HH24:MI:SS');

to_number(): 把特定格式的数字转换成为字符串类型 select sal from emp where sal > to_number('$1,250.00', '$9,999.99');

nvl(): 专门用来处理空值的： 含有空值的所有算数表达式，算出的结果都是空值，这是不对的： select ename, sal*12, comm from emp; 应该改成： select ename, sal*12, nvl(comm,0) from emp; 如果comm值不为空，则为comm的值，如果它的值为空，则用0作为他的值。

以上为单行函数： 只要有一条记录，就有一个输出，有多少条记录就有多少条输出。他的输入是以一条记录为单位。

多行函数：你给我一组记录，或者很多条记录，最好只有一个结果值出来。比如，请你告诉我这么多雇员中谁的薪水值最高，平均薪水值谁的最高，最低薪水值是多少。

count(): count不为空的字段值 select count(ename) from emp; --14 select count(comm) from emp; --4 select count(deptno) from emp; --14 select count(distinct deptno) from emp; --3 select count(*) from emp where deptno =10; --3

sum(): 求和 select sum(sal) from emp; select to_char(sum(sal),'9,9999.99') from emp;

max(): 求最大值 select max(sal) from emp; --5000

min(): 求最小值 select min(sal) from emp; --800

avg(): 求平均值 select avg(sal) from emp; --2073.21429 select round(avg(sal),2) from emp; --2073.21 select to_char(avg(sal),'9999.99') from emp; --2073.21 select to_char(avg(sal),'9,999.99') from emp; --2,073.21

组函数一定要记住，一共五个： min(), max(), avg(), sum(), count()

group by: 求每个部门的平均薪水值： select avg(sal) from emp group by deptno; select deptno, avg(sal) from emp group by deptno; 求每个部门每种工作的最大薪水值： select deptno, job, max(sal) from emp group by deptno, job; 右侧这种写法是不正确的： select ename, max(sal) from emp; 因为max(sal)只有一个输出值，而ename可能有多个值(可能有很多人的薪水值都为最大薪水值)。所以可以用一下这种方式写：select ename from emp where sal = (select max(sal) from emp); 规则：当某个字段出现在了select列表里，如果该字段不出现在组函数里，则必须出现在group by语句里： select deptno, max(sal) from emp group by deptno; 一下这种写法是不行的： select ename, max(sal) from emp group by deptno; 因为ename既没有出现在组函数里边，也没有出现在group by语句中。为什么是错的?因为ename如果没有出现在group by语句中，那么它可能产生不唯一的值。如果出现在group by语句中，那么结果是按照该字段进行分组的，所以其值肯定是唯一的。

使用having对group by进行限制： where 语句是对单条记录进行过滤的。在执行的时候会首先执行where语句，之后再进行分组。 求出部门平均薪水值大于两千的所有部门： select avg(sal), deptno from emp group by deptno having avg(sal) > 2000; -- 是用having 对group by进行限制。

对以上内容总结： SQL> select * from emp --首先是取数据 2 where sal > 1000 --然后对取得的数据进行过滤 3 group by deptno --过滤之后进行分组 4 having --然后对分组进行限制 5 order by --最后进行排序 牢牢记住这个顺序。

薪水大于1200的雇员，按照部门编号进行分组，分组之后的部门平均薪水必须大于1500， 查询分组之内的平均工资，按照部门平均工资的倒序进行排列。 select avg(sal) --先分析，要取的是平均薪水值 from emp --从哪张表里取 where sal > 1200 --是否有过滤条件?有，雇员薪水值大于1200 group by deptno --按照部门编号进行分组 having avg(sal) --对分组进行限制 order by avg(sal) desc; --按照部门平均工资的倒序进行排列

以上为单条select语句，必须牢牢牢牢的掌握。

子查询：在select 语句中套select语句，select语句可以出现在where语句中，也可以出现在from语句中

求有哪些人的工资，位于所有人的平均工资之上： select ename, sal from emp where sal > (select avg(sal) from emp);

求出按照部门分组之后，每个部门挣钱最多的那个人： 以下这种写法是不行的 SQL> select ename, sal, deptno from emp where sal in (select max(sal) from emp g roup by deptno); --如果3000是某个部门的最高薪水值，时如果其他部门有薪水值为3000的也会被选出来，尽管他不是最高工资。 正确的写法应该是以下这种写法： SQL> select ename, sal from emp join(select max(sal) max_sal, deptno from emp gr oup by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);

求每个部门的平均薪水的等级： 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);

求部门平均薪水等级：(分析，每个人的薪水等级，然后在平均) SQL> select avg(grade), deptno from (select ename, grade, deptno from emp e join salgrade s on (e.sal between s.losal and s.hisal)) group by deptno;

SQL1992标准是直接在where语句里边写表连接的条件，这样有一个问题连接条件和过滤条件混在一起了，如以下这条语句： SQL> select ename, dname, grade from emp e, dept d, salgrade s 2 where e.deptno = d.deptno and e.sal between s.losal and s.hisal and --连接条件 3 job <> 'CLERK'; --过滤条件 出于这样的考虑，SQL1999标准，就被定义了出来

self_table_connection (SQL1992 标准) 运用一条select语句，把这个人的名字以及他的经理人的名字求出来： select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

SQL1999多表连接的语法： SQL1992写法：select ename, dname from emp, dept; SQL1999写法：select ename, dname from emp cross join dept; --cross join：交叉连接，直接得到一个笛卡尔乘积

SQL1992写法： select ename, dname from emp, dept where emp.deptno = dept.deptno; SQL1999写法： select ename, dname from emp join dept on (emp.deptno = dept.deptno); --没有过滤条件，只有连接条件。SQL1999把连接条件与过滤条件分开了。

非等值连接写法： SQL> select ename, grade from emp z join salgrade s on(z.sal between s.losal and hisal); 三张表的连接： SQL> 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%'; 这样把每张表的连接条件也不混在一起了，和哪张表连接就写哪张表的连接条件，还有和据的过滤条件，全部都区分开来。

自连接： select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);

外连接： 左边这张表的不能和另外一张表产生连接的数据给拿出来 SQL> select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empn o);

右连接： SQL> select ename, dname from emp e right join dept d on (e.deptno = d.deptno);

全外连接： 左右两边多余的数据全部拿出来 select ename, dname from emp e full join dept d on (e.deptno = d.deptno);

求出哪些人事经理人： select ename from emp where empno in (select distinct mgr from emp);

不要使用组函数，求薪水的最高值：(分析，自连接，连接条件为e1.sal < e2.sal， 连接不上的e1.sal就是最大值) SQL> select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));

平均薪水最高的部门的部门编号： SQL> select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal = (select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno) );

第二种写法：(embedded_group_function), 组函数最多只能嵌套两层，想想为什么?因为第一个组函数可能产生多行，第二个组函数之后，一定只产生一行，所以嵌套两层之后，不能再嵌套第三层了，第三层没法嵌套了，因为你只有一个值，组函数多行输入只产生一个值，你只有一个值了，就没必要嵌套第三层了。 select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal = (select max(avg(sal)) from emp group by deptno);

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

求平均薪水等级最低的部门的部门名称：(分析，求出每个人的薪水等级，再对等级进行平均，从而得到每个部门的平均薪水等级，然后把它看成一张表，) select deptno, dname from dept where deptno = ( select deptno from (select deptno, avg(grade) avg_grade from (select deptno, sal, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) group by deptno) where avg_grade = (select min(avg_grade) from (select deptno, avg(grade) avg_grade from (select deptno, sal, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) group by deptno) ) );

求平均薪水的等级最低的部门的部门名称： 方法1： select t1.deptno, dname, grade, 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) ) t1 join dept on (t1.deptno = dept.deptno) where 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) ) );

以上的sql有很多重复的代码，可以通过视图来简化代码, 视图就是一张表，就是一个子查询。视图的名字通常是以v$符号开头。 赋创建视图的权限： exit sqlplus / as sysdba grant create table, create view to scott; exit 重新登录 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 t1.deptno, dname, grade, avg_sal from( select deptno, avg_sal, grade from v$_dept_avg_sal_info ) t1 join dept on (t1.deptno = dept.deptno) where grade = ( select min(grade) from( select deptno, avg_sal, grade from v$_dept_avg_sal_info ) );

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

部门经理人中，平均薪水最低的部门名称(思考题)： select t.deptno, dname, avg_sal from ( select deptno, avg(sal) avg_sal from ( select * from emp where empno in (select distinct mgr from emp where mgr is not null) ) group by deptno ) t join dept d on (t.deptno = d.deptno) where avg_sal = ( select min(sal_avg) from (select deptno, avg(sal) sal_avg from ( select * from emp where empno in (select distinct mgr from emp where mgr is not null) ) group by deptno) );

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

求出每个部门，工资最高的前两名： select deptno, sal from (select deptno, sal, row_number() over(partition by deptno order by sal desc) as rn from emp) t where t.rn <=2;

求出每个部门中，经理人工资最高的前两名： select deptno, sal, rn from (select deptno, sal, row_number() over(partition by deptno order by sal desc) as rn from (select * from emp where empno in (select distinct mgr from emp where mgr is not null))) where rn <=2;