Oracle学习之基础

1.如何查看Oracle中的表呢

select * from tab;

2.

使用“/”是执行上一条语句，使用;号也是可以执行的

3.

select empno,ename,sal,job,mgr,hiredate,comm,deptno

from emp;

该条语句是查看这个表的empno,ename,sal,job,mgr,hiredate,comm,deptno

4.

也可以查看几个属性

empno,ename,sal,job

from emp;

这个语句就是查看EMP这张表的ename,sal,job属性

5.

select empno,ename,sal,sal*12

from emp;

上面的语句是查看EMP表中的员工编号，姓名，月薪，年薪。

6.

select empno,ename,sal,sal*12,comm,sal*12+comm

from emp;

这条语句是说，去EMP这张表中查看编号，姓名，月薪，年薪，奖金，年收入

可是这条语句有缺陷，什么缺陷呢，就是有些员工没有奖金，但是其他的年收入

都没有出现了，这样导致数据出错，为了避免数据出错我么需要判断是否是空值

7.

select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)

from emp;

这条语句是说，去EMP这张表中查看编号，姓名，月薪，年薪，奖金，年收入

如果奖金为NULL，那么我么赋值为0，这样即使没有奖金，我么也会出现年收入

从而解决了数据丢失的缺陷

8.

select *

from emp

where comm=null

这句话意思是查看 emp表中 comm为null的语句，但是，我么这条语句是有缺陷的

应为 =null是找不到的，而是需要 is null 才能呢

9.

select *

form emp

where comm is null;

这句话意思是查看 emp表中 comm为null的语句，就是打印出为null的数据

10.

select empno as "员工号",ename "姓名",sal 月薪

from emp

/

这条语句是给属性取个别名，as可以省掉不写的

所以打印出来的是员工号姓名月薪

11.

select deptno from emp;

这句话是查看这个表中的depton，我么查看得知有很多重复的

所以出现了DISTINCT去除重复值

select DISTINCT deptno from emp;

这句话就是去除掉EMP表单deptno重复的值，留下不重复的

12.

select concat('hello','world')from emp;

这句话是说连接此字符串，其中concat(,)就是连接

但是其中有个个缺陷就是,我么连接字符串和EMP表没半毛钱关系

所以我么改进。

select concat('hello','world')from dual;

这样，在和没有与任何一张表中产生关系的时候，我么就可以使用dual

并且还有一个好处就是，只是打印出一次hello,world。

13.

这里简单的介绍一下dual，我么称之为 “伪表” 看其名，知其意，就是一张假表

所以，在没和其他表产生关系，我么就可以使用“伪表”

14.

select 3+2 from dual

这句话意思是 3+2 悲催吧，但是我么指向的是一张“伪表”

15.

select 'hello' || 'world' one from dual;

想要看明白就要知道||是什么意思，||在Oracle数据库中的作用是

字符串连接符号，即打印 helloworld,其中one是它么的名字，指向

一张伪表。

16.

select ename || '薪水是'||sal one

from emp;

这句话意思是查看EMP表中的名字薪水是￥

ONE

——————

base薪水是1000000

这就是打印的结果了

17.

select *

from emp

where deptno=10;

这句话的意思是查看EMP表中编号为10的全部输出

18.

select *

from emp

where ename='KING';

这句话是在EMP表中查找'KING'这个人，但是Oracle区分大小写

如果cname是大写，你输入小写那就会找不到的

19.

select *

from emp

where hiredate='17-11月-81';

这句话是查找EMP表中的出生为17-11-81的人，其实这个日期我么是可以修改的

但是系统默认是这个

20.

select sysdate from dual;

这句话是查看本地系统的时间配置

默认是24-7月-13

21.

alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

首先我么需要执行这句话，此语句修改当前时间的会话。

select * from emp where hiredate='1981-11-17';

当会话修改后我么就可以使用上面的日期就不会报错了

22.

select *

from emp

where sal beween 2000 and 3000;

这句话意思是EMP表中查询薪水在2000~3000

并输出

23.

select *

from emp

where deptno in(10,20);

这句话意思是查找EMP表中的编号为10或者20的员工

如果没有就显示为选定行。

select *

from emp

where deptno in(5,10);

这条语句会不会报错，是不会的，只是从查找有没有编号有没有

5这个编号的员工，或者10这个编号的员工。

24.

select *

from emp

where deptno not in(10,20);

这个语句意思是，查找EMP表中不是编号 10，20的员工

即编号30,40,…N0的员工都会被显示。

25.

select *

from emp

where ename like 'S%';

这句话意思是在EMP表中如果存在开头是S的姓名

like:意思是模糊查询

%：意思是0个或多个字符(任意字符)

26.

select *

from emp

where ename like'____'

这句话的意思就是，在EMP表中，就查找为4个字符的员工名字

_:意思是：代表一个字符

27.

select *

from emp

where ename like '%_%';

这句话是非常有缺陷的，哪个员工的名字都会被打印出来的

我么的_是一个字符，这个字符并没有被转意，所以每个员工

姓名都会被答应，怎么解决呢?

select *

from emp

wherer ename like '%\_%' eacape '\'

/

eacape是将字符转义，我么将转义的字符插在_前面

即\_如果名字带_下划线的员工才会被输出

28.

select *

from emp

order by sal;

这句话的意思是按照sal进行排序

order by:排序

order by sal; 后面更的是子句结尾,sal即为子句

29.

select *

from emp

order by sal desc

这句话是薪水进行降序，进行的排序

desc:降序

select *

from emp

order by sal asc

这句话意思是薪水进行升序，进行排序

select *

from emp

order by sal;

默认的情况下使用升序进行排序

30.

select ename,sal,sal*12

from emp

order by sal*12 desc;

这句话意思是，查找EMP表中姓名，月薪，年薪

并且是以年薪进行降序的

31.

select *

from emp

order by deptno,sal;

这句话的意思是，如果编号一样，那么久以薪水进行排序。

32.

select *

from emp

order by deptno desc,sal,desc

/

这句话它是更具那个进行排序的？

里desc最近的一个子句进行排序的

也就是说，该排序是按照编号进行排序

33.

笛卡尔积，只要记住列*列，行+行

EMP

empno ename sal deptno

1001   Tom   5000 1

1002   Jos   5000 2

1003   Base 5000 1

1004   Helen 5000 1

BuMen

depton dname

1 行政部

2 开发部

这两张表存在关系，就形成笛卡尔积

empno ename sal deptno depton dname

1001   Tom   5000 1   1     行政部

1002   Jos   5000 2   1     行政部

1003   Base 5000 1   1     行政部

1004   Helen 5000 1   1     行政部

1001   Tom   5000 1   2     开发部

1002   Jos   5000 2   2     开发部

1003   Base 5000 1   2     开发部

1004   Helen 5000 1   2     开发部

这样就形成了很多重复，并且有些还是错误的值

这就是笛卡尔积。怎么去除笛卡尔积呢？

用EMP的deptno = BuMen的depton

如果这两个编号相等就是成立的

否则就不成立

34.

select lower('HELLO WORLD')转小写,upper('hello world')转大写,

initcap('hello world')首字母大写

from dual;

这条语句意思是将字符串大写，小写以及首字母大写

lower:将字符串进行小写

upper:将字符串进行大写

initcap:将字符串首字母大写

35.

select substr('Hello World',3) from dual;

这条语句就是截取字符串

从第3个字符串截取到最后

36.

select substr('hello world',2,4)from dual;

这句话意思是从第二个字符截取到第4个字符串

37.

select lpad('abc',10,'*')左填充,rpad('abcd',10,'*')右边

from dual;

这句话的结果就是*******abc abcd******缺了几个位就补*

——————————————————————————————-

由于时间不充足的关系只能写上这个含义

38.

ROUND:四舍五入

TRUNC:截断

MOD：求余

39:

lower('Hello WOrld') 转小写

upper('Hello WOrld') 转大写,

initcap('hello world') 首字母大写

trim：默认去掉字符串两边的空格，如果给定参数就去除给定参数值

40.

ADD_MONTHS:向指定日期中加上若干月数

LAST_DAY:本月的最后一天

NEXT_DAY:指定日期的下一个日期

MONTHS_BETWEEN:两个日期相差的月数

41.

group by

对其分组

===============================================================================================

2013-7-25

1.

select sal from emp where ename='SOCTT';

这句话的意思是说，我么在EMP表中查找名字叫

SOCTT这个人的薪水

2.

select * from emp where sal > 3000

这句话意思是说，在EMP表中，如果有比这个

薪水高的人就打印出来

3.

select *

from emp

sal > (select sal from emp where ename='SCOTT');

这句话的意思就是，先执行子查询语句，查找到name为SCOTT

获取到这个员工的薪水，然后在遍历EMP表中所有员工的薪水

薪水值大于SCOTT这个人的员工

子查询的作用范围以及注意事项:

   1.可以在主查询的where select from having 放置子查询

   2.不可以在主查询的group by后面放置子查询

   3.单独强调from后面的子查询

   4.主查询和子查询可以不是同一张表，只要子查询返回的结果主查询可以使用即可

   5.一般，先执行子查询再执行主查询；但相关子查询除外

   6.一般不在子查询中使用order by;但在Top-N分析问题中，必须使用order by

   7.单行子查询只能使用单行操作符多行子查询只能使用多行操作符

   8.子查询中的null

4.

select ename,sal,(select job from emp where empno=7839)一列

from emp;

这句话的意思就是，先看子查询的内容就是，查找EMP表中编号为7839这个职位

然后外面有select就是查找 ename,sal,job了

这样我么拆分出来看是非常容易理解的。

5.

select *

from(select ename,sal from emp);

这句话的意思是，子查询语句的结果是查询EMP表中的 ename，sal

外层的*代表所有的员工ename，sal

6.

select *

from (select ename ,sal,sal*12 annlsal from emp);

这句话的意思是说，查找EMP表中的ename,sal,年薪

首先查看下子查询，意思是在EMP表中查找ename,sal,sal*12,其中annlsal只是取个名字而已

外层的*是代表输出所有的员工 ename,sal,sal*12

7.

select *

from emp

where deptno = (select deptno

from dept

where dname='SALES');

首先我么分析子查询的意思，查询DEPT这张表部门名字为SALES编号

如果找到了，就把编号赋值给了外层的EMP表的deptno，如此一来，打印的都是

同一个编号部门咯，这里我么就运用到了两张表了

8.

select e.*

from emp e,dept d

where e.deptno=d.deptno and d.dname='SALES';

.代表所有列,这里的判断条件是如果员工的编号 = 部门编号成立

和部门=‘SALES’

如果我么找到了，那么就输出是此部门的员工

9.

select *

from emp

where deptno in(select deptno from dept where dname='SALES' or dname='ACCOUNTING');

这句话的意思是：

我么查找dept表中的编号，但是条件是，部门需要是‘SALES’，或者是ACCOUNTING

的部门下的才会被输出

其中这个in:等于列表中的任何一个

10.

select *

from emp

where sal > any (select sal from emp where deptno=30);

先来解释一下any：和子查询集合中的值任意进行比较

这句话的意思是说，查询工资比30号部门任意一个员工高的员工信息

所以我么这里应该找到一个最小值进行比较，这样就会高出来了

最后在重员工表中遍历出所有员工的sal进行比较，如果比any的值大

则输出

11.

select *

from emp

where sal > (select min(sal) from emp where deptno=30)

/

这句话的意思是，查找EMP表中编号为30的最少sal，找到了

外面在进行遍历，如果低于编号30员工的薪水则不输出

12.

select *

from emp

where sal > (select max(sal) from emp where deptno=30)

/

这句话的意思是：查找EMP表中编号为30的最高sal，如果低于

编号30的员工薪水则不输出.

13.

slect *

from emp

where empno not in(select mgr from emp);

这句话的意思就是说，查询不是老板的员工，如果是为选定行就是没找到

14.

select *

from emp

where empno in(select mgr from emp);

这句话的意思就是说，查询是老板的员工。

在第13—14 这两条sql语句输出的结果大家可能看的有些奇怪，其实，数据都是加载到内存中

内存中的数据都是 “树”形结构，一层一层的关系，所以只要它的父节点是子节点就不用被输出

可参见图：“关系图”画出的关系观看

下面是几个课堂练习“引发”出的血案

15.

select rownum,empno,ename,sal

from emp;

这句话的意思是说，遍历出EMP表中rownum,empno,ename,sal

上面的语句只是简单的输出全部的，而我么只需要前3名的sal

增强版本:

select rownum,empno,sal,ename

from emp

where rownum<=3

order by sal desc;

这里就有明显的改变了哈，但是还是没有觉得，因为rownum是乱的。。。。

怎么办啊？卧槽，各种压力，看我怎么破解这个命案，真相只有一个

终极版解决:

select rownum,ename,empno,sal

from (select * from emp order by sal)

where rownum<=3;

这句话，首先执行子查询，按照sal排序，接着遍历输出，但是，此时

我么已经限定了rownum它只能在3行内，所以，最后是有顺序，并且还是在

3行内的.

关于rownum

rownum永远按照默认的顺序生成

rownum只能使用< <=, 不能使用> >=

哈哈哈哈哈~~，这个命案的手法被我名侦探“李小贱”解决了把。

16.

在来看看我么如果使用>=的rownum把，见证奇迹的时刻到了

select rownum,eanme,empno,sal

from emp

where rownum>=5 and rownum<=8;

这句话将会导致什么结果呢？本世纪最牛的“魔术师”为你揭晓

答案是：为选定行，哈哈，找不到

没办法都说了不行，可是你还要测试，“犯贱”吗？哈哈哈~

但是，如果按照常理来说，我么有1~14个数据，但是怎么就是没显示出来呢？

答案在上面我已经说过了

分页:

select *

from (select rownum r,el.*

      from (select * from emp order by sal)el

      where rownum<=8

      )

where r>=5;

沃勒了个去，这个手法有些棘手，看我是怎么来推理的把

首先我么先来到现场看到地上的前由高到底进行排序的

并且还了el的名字，继续观察发现，摆放的rownum并为超过8行

这就证明凶手的口袋装不下了，e.*是遍历rownum的所有列

哈哈，有14列，但是只剩下8列，看来凶手不是贪心之人

最后外层进行判断r即rownum，是>=5开始的。

由此，内层行号<=8，外层重行5开始进行

也就是形成了 5，6，7，8进行了分页

17.

select empno,ename,sal,avgsal

from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d

where e.deptno = d.deptno and e.sal>d.avgsal;

分析一下：

   我么先看子查询语句，查找EMP表的编号和平均工资，并且是以编号进行排序的

   外层的select语句，where进行过滤，如果e(EMP).deptno = d(EMP)from内的

   并且e.sal > d.avgsal，EMP表的工资大于from内的平均工资

18.

select empno,enaem,sal,(select avg(sal) from emp where deptno=e.deptno)avgsal

from emp e

where sal > (select avg(sal) from emp where deptno=e.deptno);

这个看起来比较麻烦，如果去看呢？我么先看子查询的把

这个是求出每个部门的平均工资

出了子查询就是遍历EMP表

进行判断，如果 sal (个人sal)> (员工的部门的平均工资)

则输出

19.

select deptno,wm_concat(ename) names

from emp

group by deptno;

这几句的意思是做什么的呢？有我来说明下把

先来了解一下 wm_concat()这个函数是行转列函数

好把，我么先来第一句

查找编号，并把名字排成一列

在EMP表中查询

如果相同的编号则视为一个编号

最后答案

————————————————-

10   CLARK,KING,MILLER

20   SMITH,FORD,ADAMS,SCOTT,JONES

30   ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

————————————————-

显然的，我么的名字是在一列上，并且相同的编号视为同一编号

20.

select * from emp where deptno=10

union

select * from emp where deptno=20

查找编号为10或者20的员工

union : 并集,去掉重复元素后的所有记录

21.

select deptno,job,sum(sal) from emp group by deptno,job

union

select deptno,to_char(null),sum(sal) from emp group by deptno

union

select to_number(null),to_char(null),sum(sal) from emp;

注意的问题

1. 参与运算的各个集合必须列数相同，且类型一致

2. 采用第一个集合的表头作为最后表头

3. 如果排序，必须在每个集合后使用相同的order by

4. 括号

22.

select deptno,job,sum(sal) from emp group by deptno,job

union

select deptno,to_char(null),sum(sal) from emp group by deptno

union

select to_number(null),to_char(null),sum(sal) from emp

这几句的意思是，查找EMP表的编号，职位，和薪水

使用union进行并集，去掉重复的在显示

23.

select count(*) Total

sum(decode(to_char(hiredate,'YY'),'80',1,0))"1980",

sum(decode(to_char(hiredate,'YY'),'81',1,0))"1981",

sum(decode(to_char(hiredate,'YY'),'82',1,0))"1982",

sum(decode(to_char(hiredate,'YY'),'87',1,0))"1987"

from emp

/

这个意思是：统计所有员工是什么日期入职的

/*

SQL的类型:

   1. DML语句(Data Manipulation Language 数据操作语言): insert update delete select

   2. DDL语句(Data Definition Language 数据定义语言):create/drop/alter/truncate table

                               create/drop view,sequence,index,synonym

   3. DCL语句(Data Control Language 数据控制语句): commit rollback

我么先来了解数据操作语言

insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10);

创建一行

隐式/显式插入空值

地址符 &

insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);

输入 empno 的值: 1002

输入 ename 的值: 'Mary'

输入 sal 的值: 5000

输入 deptno 的值: 20

原值 1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)

新值 1: insert into emp(empno,ename,sal,deptno) values(1002,'Mary',5000,20)

-批处理

create table emp10 as select * from emp where 1=2;

其中这里只是把emp表的结构赋值给了emp10这个表，里面并没数据

insert into emp10

select * from emp where deptno=10

这句话的意思是说，把emp表中为编号10的员工值赋值给emp10表中为10编号的员工

delete和truncate的区别

1. delete逐条删除 truncate先摧毁再重建

2.*** delete是DML语句(可以回滚) truncate 是DDL语句(不可以回滚)

3. delete不会释放空间 truncate会

4. delete会产生碎片 truncate不会

5. delete可以闪回 truncate不可以

oracle中的事务

1. 起始标志: DML语句

2. 结束标志: 提交显式–> commit

                  隐式–> 正常退出exit ,DDL语句

                  回滚显式–> rollback

          隐式–> 非正常退出,停电

create table testsavepoint

(tid number,tname varchar(20));

这里就创建了一张表了，并且设置了一个tid为number类型和tname为varchar类型

insert into testsavepoint values(1,'Tom');

创建数据

savepoint a;

创建保存点

insert into testsavepoint values(2,'Mike');

insert into testsavepoint values(3,'Mary');

rollback to savepoint a

回退到保存点

select * from testsavepoint;

TID TNAME

——————————

1 Tom

这样就是回退到保存点

DDL:

alter table test1 add photo blob;

修改表结构，

alter table test1 修改的是哪张表

add ptoto blob 添加什么数据是什么数据类型

alter table test1 rename column tname to username

把tname换成username

drop table 表名

删除一张表，其实并没有删除，只是放在了回收站而已

show recyclebin

查看回收站

当把一张表删除的时候在查看就是一堆看不懂的乱码了，我么还可以使用

select * from "被删除的文件表码"

一定要加""否则出错

purger recyclebin;

清空回收站

———————————————————————–

其他数据库对象

create view empinfoview

as

select e.empno,e.ename,e.sal,e.sal*12 annlsal,d.dname

from emp e, dept d

where e.deptno=d.deptno

上面是创建一个视图，可是我么没有权限？

如何获取到权限呢？让管理员给你权限把

1–>sqlplus / as sysdba

2–>进入到管理员输入 grant create view to scott;

3–>授权成功

select * from empinfoview

查看内容，其实操作视图和操作表是一样的

create or replace view empinfoview

as

select e.empno,e.ename,e.sal,e.sal*12 annlsal,d.dname

from emp e,dept d

where e.deptno=d.deptno

with read only;

其中 with read only 只做查询操作

序列:

create sequence myseq;

序列被创建

create table tableA

(

tid number,

tname varchar2(20)

);

创建表

select myseq.nextval from dual;

nextval : 返回下一个有效值

这句话是说，查看下一个值，和任何表都没关系，从而使用一张伪表

insert into tableA values(myseq.nextval,'base');

/

/

执行两次后，使用commit进行提交

select * from tableA;

输出

TID       TNAME

———————

2       base

3       base

4       base

insert into tableA values(myseq.nextval,'base');

/

/

rollback;

进行回退

insert into tableA values(myseq.nextval,'xss');

插入一条数据

select * from tableA

结果

   TID       TNAME

—————————————–

   2       base

   3       base

   4       base

   7       aa

他不会因为回退而改变数。

select count(*) from hr.EMPLOYEES

查看HR账户的EMPLOYEES表，但是这样会错误，没有视图或者表

因为自己更本就没有权限，所以需要进行授权

来到HR—>grant select on EMPLOYEES to scott

这样就授权给了scott

1.如何查看Oracle中的表呢

select * from tab;

2.

使用“/”是执行上一条语句，使用;号也是可以执行的

3.

select empno,ename,sal,job,mgr,hiredate,comm,deptno

from emp;

该条语句是查看这个表的empno,ename,sal,job,mgr,hiredate,comm,deptno

4.

也可以查看几个属性

empno,ename,sal,job

from emp;

这个语句就是查看EMP这张表的ename,sal,job属性

5.

select empno,ename,sal,sal*12

from emp;

上面的语句是查看EMP表中的员工编号，姓名，月薪，年薪。

6.

select empno,ename,sal,sal*12,comm,sal*12+comm

from emp;

7.

select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)

from emp;

9.

select *

form emp

where comm is null;

这句话意思是查看 emp表中 comm为null的语句，就是打印出为null的数据

10.

select empno as "员工号",ename "姓名",sal 月薪

from emp

/

这条语句是给属性取个别名，as可以省掉不写的

所以打印出来的是员工号姓名月薪

11.

select deptno from emp;

这句话是查看这个表中的depton，我么查看得知有很多重复的

所以出现了DISTINCT去除重复值

select DISTINCT deptno from emp;

这句话就是去除掉EMP表单deptno重复的值，留下不重复的

13.

这里简单的介绍一下dual，我么称之为 “伪表” 看其名，知其意，就是一张假表

所以，在没和其他表产生关系，我么就可以使用“伪表”

14.

select 3+2 from dual

这句话意思是 3+2 悲催吧，但是我么指向的是一张“伪表”

15.

select 'hello' || 'world' one from dual;

想要看明白就要知道||是什么意思，||在Oracle数据库中的作用是

字符串连接符号，即打印 helloworld,其中one是它么的名字，指向

一张伪表。

16.

select ename || '薪水是'||sal one

from emp;

这句话意思是查看EMP表中的名字薪水是￥

ONE

——————

base薪水是1000000

这就是打印的结果了

17.

select *

from emp

where deptno=10;

这句话的意思是查看EMP表中编号为10的全部输出

18.

select *

from emp

where ename='KING';

这句话是在EMP表中查找'KING'这个人，但是Oracle区分大小写

如果cname是大写，你输入小写那就会找不到的

19.

select *

from emp

where hiredate='17-11月-81';

这句话是查找EMP表中的出生为17-11-81的人，其实这个日期我么是可以修改的

但是系统默认是这个

20.

select sysdate from dual;

这句话是查看本地系统的时间配置

默认是24-7月-13

21.

alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

首先我么需要执行这句话，此语句修改当前时间的会话。

select * from emp where hiredate='1981-11-17';

当会话修改后我么就可以使用上面的日期就不会报错了

22.

select *

from emp

where sal beween 2000 and 3000;

这句话意思是EMP表中查询薪水在2000~3000

并输出

24.

select *

from emp

where deptno not in(10,20);

这个语句意思是，查找EMP表中不是编号 10，20的员工

即编号30,40,…N0的员工都会被显示。

25.

select *

from emp

where ename like 'S%';

这句话意思是在EMP表中如果存在开头是S的姓名

like:意思是模糊查询

%：意思是0个或多个字符(任意字符)

26.

select *

from emp

where ename like'____'

这句话的意思就是，在EMP表中，就查找为4个字符的员工名字

_:意思是：代表一个字符

select *

from emp

wherer ename like '%\_%' eacape '\'

/

eacape是将字符转义，我么将转义的字符插在_前面

即\_如果名字带_下划线的员工才会被输出

28.

select *

from emp

order by sal;

这句话的意思是按照sal进行排序

order by:排序

order by sal; 后面更的是子句结尾,sal即为子句

29.

select *

from emp

order by sal desc

这句话是薪水进行降序，进行的排序

desc:降序

select *

from emp

order by sal asc

这句话意思是薪水进行升序，进行排序

select *

from emp

order by sal;

默认的情况下使用升序进行排序

30.

select ename,sal,sal*12

from emp

order by sal*12 desc;

这句话意思是，查找EMP表中姓名，月薪，年薪

并且是以年薪进行降序的

31.

select *

from emp

order by deptno,sal;

这句话的意思是，如果编号一样，那么久以薪水进行排序。

33.

笛卡尔积，只要记住列*列，行+行

EMP

empno ename sal deptno

1001   Tom   5000 1

1002   Jos   5000 2

1003   Base 5000 1

1004   Helen 5000 1

BuMen

depton dname

1 行政部

2 开发部

这两张表存在关系，就形成笛卡尔积

empno ename sal deptno depton dname

1001   Tom   5000 1   1     行政部

1002   Jos   5000 2   1     行政部

1003   Base 5000 1   1     行政部

1004   Helen 5000 1   1     行政部

1001   Tom   5000 1   2     开发部

1002   Jos   5000 2   2     开发部

1003   Base 5000 1   2     开发部

1004   Helen 5000 1   2     开发部

这样就形成了很多重复，并且有些还是错误的值

这就是笛卡尔积。怎么去除笛卡尔积呢？

用EMP的deptno = BuMen的depton

如果这两个编号相等就是成立的

否则就不成立

35.

select substr('Hello World',3) from dual;

这条语句就是截取字符串

从第3个字符串截取到最后

36.

select substr('hello world',2,4)from dual;

这句话意思是从第二个字符截取到第4个字符串

37.

select lpad('abc',10,'*')左填充,rpad('abcd',10,'*')右边

from dual;

这句话的结果就是*******abc abcd******缺了几个位就补*

——————————————————————————————-

由于时间不充足的关系只能写上这个含义

38.

ROUND:四舍五入

TRUNC:截断

MOD：求余

39:

lower('Hello WOrld') 转小写

upper('Hello WOrld') 转大写,

initcap('hello world') 首字母大写

trim：默认去掉字符串两边的空格，如果给定参数就去除给定参数值

40.

ADD_MONTHS:向指定日期中加上若干月数

LAST_DAY:本月的最后一天

NEXT_DAY:指定日期的下一个日期

MONTHS_BETWEEN:两个日期相差的月数

41.

group by

对其分组

===============================================================================================

2013-7-25

1.

select sal from emp where ename='SOCTT';

这句话的意思是说，我么在EMP表中查找名字叫

SOCTT这个人的薪水

4.

select ename,sal,(select job from emp where empno=7839)一列

from emp;

5.

select *

from(select ename,sal from emp);

这句话的意思是，子查询语句的结果是查询EMP表中的 ename，sal

外层的*代表所有的员工ename，sal

6.

select *

from (select ename ,sal,sal*12 annlsal from emp);

7.

select *

from emp

where deptno = (select deptno

from dept

where dname='SALES');

8.

select e.*

from emp e,dept d

where e.deptno=d.deptno and d.dname='SALES';

.代表所有列,这里的判断条件是如果员工的编号 = 部门编号成立

和部门=‘SALES’

如果我么找到了，那么就输出是此部门的员工

9.

select *

from emp

where deptno in(select deptno from dept where dname='SALES' or dname='ACCOUNTING');

这句话的意思是：

我么查找dept表中的编号，但是条件是，部门需要是‘SALES’，或者是ACCOUNTING

的部门下的才会被输出

其中这个in:等于列表中的任何一个

10.

select *

from emp

where sal > any (select sal from emp where deptno=30);

11.

select *

from emp

where sal > (select min(sal) from emp where deptno=30)

/

这句话的意思是，查找EMP表中编号为30的最少sal，找到了

外面在进行遍历，如果低于编号30员工的薪水则不输出

12.

select *

from emp

where sal > (select max(sal) from emp where deptno=30)

/

这句话的意思是：查找EMP表中编号为30的最高sal，如果低于

编号30的员工薪水则不输出.

13.

slect *

from emp

where empno not in(select mgr from emp);

这句话的意思就是说，查询不是老板的员工，如果是为选定行就是没找到

14.

select *

from emp

where empno in(select mgr from emp);

这句话的意思就是说，查询是老板的员工。

下面是几个课堂练习“引发”出的血案

15.

select rownum,empno,ename,sal

from emp;

这句话的意思是说，遍历出EMP表中rownum,empno,ename,sal

上面的语句只是简单的输出全部的，而我么只需要前3名的sal

增强版本:

select rownum,empno,sal,ename

from emp

where rownum<=3

order by sal desc;

这里就有明显的改变了哈，但是还是没有觉得，因为rownum是乱的。。。。

怎么办啊？卧槽，各种压力，看我怎么破解这个命案，真相只有一个

关于rownum

rownum永远按照默认的顺序生成

rownum只能使用< <=, 不能使用> >=

哈哈哈哈哈~~，这个命案的手法被我名侦探“李小贱”解决了把。

16.

在来看看我么如果使用>=的rownum把，见证奇迹的时刻到了

select rownum,eanme,empno,sal

from emp

where rownum>=5 and rownum<=8;

这句话将会导致什么结果呢？本世纪最牛的“魔术师”为你揭晓

答案是：为选定行，哈哈，找不到

分页:

select *

from (select rownum r,el.*

      from (select * from emp order by sal)el

      where rownum<=8

      )

where r>=5;

17.

select empno,ename,sal,avgsal

from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d

where e.deptno = d.deptno and e.sal>d.avgsal;

出了子查询就是遍历EMP表

进行判断，如果 sal (个人sal)> (员工的部门的平均工资)

则输出

19.

select deptno,wm_concat(ename) names

from emp

group by deptno;

这几句的意思是做什么的呢？有我来说明下把

先来了解一下 wm_concat()这个函数是行转列函数

显然的，我么的名字是在一列上，并且相同的编号视为同一编号

20.

select * from emp where deptno=10

union

select * from emp where deptno=20

查找编号为10或者20的员工

union : 并集,去掉重复元素后的所有记录

21.

select deptno,job,sum(sal) from emp group by deptno,job

union

select deptno,to_char(null),sum(sal) from emp group by deptno

union

select to_number(null),to_char(null),sum(sal) from emp;

from emp

/

这个意思是：统计所有员工是什么日期入职的

我么先来了解数据操作语言

insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10);

创建一行

隐式/显式插入空值

-批处理

create table emp10 as select * from emp where 1=2;

其中这里只是把emp表的结构赋值给了emp10这个表，里面并没数据

insert into emp10

select * from emp where deptno=10

这句话的意思是说，把emp表中为编号10的员工值赋值给emp10表中为10编号的员工

savepoint a;

创建保存点

insert into testsavepoint values(2,'Mike');

insert into testsavepoint values(3,'Mary');

rollback to savepoint a

回退到保存点

select * from testsavepoint;

TID TNAME

——————————

1 Tom

这样就是回退到保存点

DDL:

alter table test1 add photo blob;

修改表结构，

alter table test1 修改的是哪张表

add ptoto blob 添加什么数据是什么数据类型

alter table test1 rename column tname to username

把tname换成username

drop table 表名

删除一张表，其实并没有删除，只是放在了回收站而已

show recyclebin

查看回收站

当把一张表删除的时候在查看就是一堆看不懂的乱码了，我么还可以使用

select * from "被删除的文件表码"

一定要加""否则出错

purger recyclebin;

清空回收站

———————————————————————–

其他数据库对象

1–>sqlplus / as sysdba

2–>进入到管理员输入 grant create view to scott;

3–>授权成功

select * from empinfoview

查看内容，其实操作视图和操作表是一样的

create or replace view empinfoview

as

select e.empno,e.ename,e.sal,e.sal*12 annlsal,d.dname

from emp e,dept d

where e.deptno=d.deptno

with read only;

其中 with read only 只做查询操作

序列:

create sequence myseq;

序列被创建

create table tableA

(

tid number,

tname varchar2(20)

);

创建表

select myseq.nextval from dual;

nextval : 返回下一个有效值

这句话是说，查看下一个值，和任何表都没关系，从而使用一张伪表

insert into tableA values(myseq.nextval,'base');

/

/

执行两次后，使用commit进行提交

select * from tableA;

输出

TID       TNAME

———————

2       base

3       base

4       base

insert into tableA values(myseq.nextval,'base');

/

/

rollback;

进行回退

insert into tableA values(myseq.nextval,'xss');

插入一条数据

select * from tableA

结果

   TID       TNAME

—————————————–

   2       base

   3       base

   4       base

   7       aa

他不会因为回退而改变数。

select count(*) from hr.EMPLOYEES

查看HR账户的EMPLOYEES表，但是这样会错误，没有视图或者表

因为自己更本就没有权限，所以需要进行授权

来到HR—>grant select on EMPLOYEES to scott

这样就授权给了scott

1.如何查看Oracle中的表呢

select * from tab;

2.

使用“/”是执行上一条语句，使用;号也是可以执行的

3.

select empno,ename,sal,job,mgr,hiredate,comm,deptno

from emp;

该条语句是查看这个表的empno,ename,sal,job,mgr,hiredate,comm,deptno

4.

也可以查看几个属性

empno,ename,sal,job

from emp;

这个语句就是查看EMP这张表的ename,sal,job属性

5.

select empno,ename,sal,sal*12

from emp;

上面的语句是查看EMP表中的员工编号，姓名，月薪，年薪。

6.

select empno,ename,sal,sal*12,comm,sal*12+comm

from emp;

7.

select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)

from emp;

9.

select *

form emp

where comm is null;

这句话意思是查看 emp表中 comm为null的语句，就是打印出为null的数据

10.

select empno as "员工号",ename "姓名",sal 月薪

from emp

/

这条语句是给属性取个别名，as可以省掉不写的

所以打印出来的是员工号姓名月薪

11.

select deptno from emp;

这句话是查看这个表中的depton，我么查看得知有很多重复的

所以出现了DISTINCT去除重复值

select DISTINCT deptno from emp;

这句话就是去除掉EMP表单deptno重复的值，留下不重复的

13.

这里简单的介绍一下dual，我么称之为 “伪表” 看其名，知其意，就是一张假表

所以，在没和其他表产生关系，我么就可以使用“伪表”

14.

select 3+2 from dual

这句话意思是 3+2 悲催吧，但是我么指向的是一张“伪表”

15.

select 'hello' || 'world' one from dual;

想要看明白就要知道||是什么意思，||在Oracle数据库中的作用是

字符串连接符号，即打印 helloworld,其中one是它么的名字，指向

一张伪表。

16.

select ename || '薪水是'||sal one

from emp;

这句话意思是查看EMP表中的名字薪水是￥

ONE

——————

base薪水是1000000

这就是打印的结果了

17.

select *

from emp

where deptno=10;

这句话的意思是查看EMP表中编号为10的全部输出

18.

select *

from emp

where ename='KING';

这句话是在EMP表中查找'KING'这个人，但是Oracle区分大小写

如果cname是大写，你输入小写那就会找不到的

19.

select *

from emp

where hiredate='17-11月-81';

这句话是查找EMP表中的出生为17-11-81的人，其实这个日期我么是可以修改的

但是系统默认是这个

20.

select sysdate from dual;

这句话是查看本地系统的时间配置

默认是24-7月-13

21.

alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

首先我么需要执行这句话，此语句修改当前时间的会话。

select * from emp where hiredate='1981-11-17';

当会话修改后我么就可以使用上面的日期就不会报错了

22.

select *

from emp

where sal beween 2000 and 3000;

这句话意思是EMP表中查询薪水在2000~3000

并输出

24.

select *

from emp

where deptno not in(10,20);

这个语句意思是，查找EMP表中不是编号 10，20的员工

即编号30,40,…N0的员工都会被显示。

25.

select *

from emp

where ename like 'S%';

这句话意思是在EMP表中如果存在开头是S的姓名

like:意思是模糊查询

%：意思是0个或多个字符(任意字符)

26.

select *

from emp

where ename like'____'

这句话的意思就是，在EMP表中，就查找为4个字符的员工名字

_:意思是：代表一个字符

select *

from emp

wherer ename like '%\_%' eacape '\'

/

eacape是将字符转义，我么将转义的字符插在_前面

即\_如果名字带_下划线的员工才会被输出

28.

select *

from emp

order by sal;

这句话的意思是按照sal进行排序

order by:排序

order by sal; 后面更的是子句结尾,sal即为子句

29.

select *

from emp

order by sal desc

这句话是薪水进行降序，进行的排序

desc:降序

select *

from emp

order by sal asc

这句话意思是薪水进行升序，进行排序

select *

from emp

order by sal;

默认的情况下使用升序进行排序

30.

select ename,sal,sal*12

from emp

order by sal*12 desc;

这句话意思是，查找EMP表中姓名，月薪，年薪

并且是以年薪进行降序的

31.

select *

from emp

order by deptno,sal;

这句话的意思是，如果编号一样，那么久以薪水进行排序。

33.

笛卡尔积，只要记住列*列，行+行

EMP

empno ename sal deptno

1001   Tom   5000 1

1002   Jos   5000 2

1003   Base 5000 1

1004   Helen 5000 1

BuMen

depton dname

1 行政部

2 开发部

这两张表存在关系，就形成笛卡尔积

empno ename sal deptno depton dname

1001   Tom   5000 1   1     行政部

1002   Jos   5000 2   1     行政部

1003   Base 5000 1   1     行政部

1004   Helen 5000 1   1     行政部

1001   Tom   5000 1   2     开发部

1002   Jos   5000 2   2     开发部

1003   Base 5000 1   2     开发部

1004   Helen 5000 1   2     开发部

这样就形成了很多重复，并且有些还是错误的值

这就是笛卡尔积。怎么去除笛卡尔积呢？

用EMP的deptno = BuMen的depton

如果这两个编号相等就是成立的

否则就不成立

35.

select substr('Hello World',3) from dual;

这条语句就是截取字符串

从第3个字符串截取到最后

36.

select substr('hello world',2,4)from dual;

这句话意思是从第二个字符截取到第4个字符串

37.

select lpad('abc',10,'*')左填充,rpad('abcd',10,'*')右边

from dual;

这句话的结果就是*******abc abcd******缺了几个位就补*

——————————————————————————————-

由于时间不充足的关系只能写上这个含义

38.

ROUND:四舍五入

TRUNC:截断

MOD：求余

39:

lower('Hello WOrld') 转小写

upper('Hello WOrld') 转大写,

initcap('hello world') 首字母大写

trim：默认去掉字符串两边的空格，如果给定参数就去除给定参数值

40.

ADD_MONTHS:向指定日期中加上若干月数

LAST_DAY:本月的最后一天

NEXT_DAY:指定日期的下一个日期

MONTHS_BETWEEN:两个日期相差的月数

41.

group by

对其分组

===============================================================================================

2013-7-25

1.

select sal from emp where ename='SOCTT';

这句话的意思是说，我么在EMP表中查找名字叫

SOCTT这个人的薪水

4.

select ename,sal,(select job from emp where empno=7839)一列

from emp;

5.

select *

from(select ename,sal from emp);

这句话的意思是，子查询语句的结果是查询EMP表中的 ename，sal

外层的*代表所有的员工ename，sal

6.

select *

from (select ename ,sal,sal*12 annlsal from emp);

7.

select *

from emp

where deptno = (select deptno

from dept

where dname='SALES');

8.

select e.*

from emp e,dept d

where e.deptno=d.deptno and d.dname='SALES';

.代表所有列,这里的判断条件是如果员工的编号 = 部门编号成立

和部门=‘SALES’

如果我么找到了，那么就输出是此部门的员工

9.

select *

from emp

where deptno in(select deptno from dept where dname='SALES' or dname='ACCOUNTING');

这句话的意思是：

我么查找dept表中的编号，但是条件是，部门需要是‘SALES’，或者是ACCOUNTING

的部门下的才会被输出

其中这个in:等于列表中的任何一个

10.

select *

from emp

where sal > any (select sal from emp where deptno=30);

11.

select *

from emp

where sal > (select min(sal) from emp where deptno=30)

/

这句话的意思是，查找EMP表中编号为30的最少sal，找到了

外面在进行遍历，如果低于编号30员工的薪水则不输出

12.

select *

from emp

where sal > (select max(sal) from emp where deptno=30)

/

这句话的意思是：查找EMP表中编号为30的最高sal，如果低于

编号30的员工薪水则不输出.

13.

slect *

from emp

where empno not in(select mgr from emp);

这句话的意思就是说，查询不是老板的员工，如果是为选定行就是没找到

14.

select *

from emp

where empno in(select mgr from emp);

这句话的意思就是说，查询是老板的员工。

下面是几个课堂练习“引发”出的血案

15.

select rownum,empno,ename,sal

from emp;

这句话的意思是说，遍历出EMP表中rownum,empno,ename,sal

上面的语句只是简单的输出全部的，而我么只需要前3名的sal

增强版本:

select rownum,empno,sal,ename

from emp

where rownum<=3

order by sal desc;

这里就有明显的改变了哈，但是还是没有觉得，因为rownum是乱的。。。。

怎么办啊？卧槽，各种压力，看我怎么破解这个命案，真相只有一个

关于rownum

rownum永远按照默认的顺序生成

rownum只能使用< <=, 不能使用> >=

哈哈哈哈哈~~，这个命案的手法被我名侦探“李小贱”解决了把。

16.

在来看看我么如果使用>=的rownum把，见证奇迹的时刻到了

select rownum,eanme,empno,sal

from emp

where rownum>=5 and rownum<=8;

这句话将会导致什么结果呢？本世纪最牛的“魔术师”为你揭晓

答案是：为选定行，哈哈，找不到

分页:

select *

from (select rownum r,el.*

      from (select * from emp order by sal)el

      where rownum<=8

      )

where r>=5;

17.

select empno,ename,sal,avgsal

from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d

where e.deptno = d.deptno and e.sal>d.avgsal;

出了子查询就是遍历EMP表

进行判断，如果 sal (个人sal)> (员工的部门的平均工资)

则输出

19.

select deptno,wm_concat(ename) names

from emp

group by deptno;

这几句的意思是做什么的呢？有我来说明下把

先来了解一下 wm_concat()这个函数是行转列函数

显然的，我么的名字是在一列上，并且相同的编号视为同一编号

20.

select * from emp where deptno=10

union

select * from emp where deptno=20

查找编号为10或者20的员工

union : 并集,去掉重复元素后的所有记录

21.

select deptno,job,sum(sal) from emp group by deptno,job

union

select deptno,to_char(null),sum(sal) from emp group by deptno

union

select to_number(null),to_char(null),sum(sal) from emp;

from emp

/

这个意思是：统计所有员工是什么日期入职的

我么先来了解数据操作语言

insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10);

创建一行

隐式/显式插入空值

-批处理

create table emp10 as select * from emp where 1=2;

其中这里只是把emp表的结构赋值给了emp10这个表，里面并没数据

insert into emp10

select * from emp where deptno=10

这句话的意思是说，把emp表中为编号10的员工值赋值给emp10表中为10编号的员工

savepoint a;

创建保存点

insert into testsavepoint values(2,'Mike');

insert into testsavepoint values(3,'Mary');

rollback to savepoint a

回退到保存点

select * from testsavepoint;

TID TNAME

——————————

1 Tom

这样就是回退到保存点

DDL:

alter table test1 add photo blob;

修改表结构，

alter table test1 修改的是哪张表

add ptoto blob 添加什么数据是什么数据类型

alter table test1 rename column tname to username

把tname换成username

drop table 表名

删除一张表，其实并没有删除，只是放在了回收站而已

show recyclebin

查看回收站

当把一张表删除的时候在查看就是一堆看不懂的乱码了，我么还可以使用

select * from "被删除的文件表码"

一定要加""否则出错

purger recyclebin;

清空回收站

———————————————————————–

其他数据库对象

1–>sqlplus / as sysdba

2–>进入到管理员输入 grant create view to scott;

3–>授权成功

select * from empinfoview

查看内容，其实操作视图和操作表是一样的

create or replace view empinfoview

as

select e.empno,e.ename,e.sal,e.sal*12 annlsal,d.dname

from emp e,dept d

where e.deptno=d.deptno

with read only;

其中 with read only 只做查询操作

序列:

create sequence myseq;

序列被创建

create table tableA

(

tid number,

tname varchar2(20)

);

创建表

select myseq.nextval from dual;

nextval : 返回下一个有效值

这句话是说，查看下一个值，和任何表都没关系，从而使用一张伪表

insert into tableA values(myseq.nextval,'base');

/

/

执行两次后，使用commit进行提交

select * from tableA;

输出

TID       TNAME

———————

2       base

3       base

4       base

insert into tableA values(myseq.nextval,'base');

/

/

rollback;

进行回退

insert into tableA values(myseq.nextval,'xss');

插入一条数据