oracle百分之0.01就成了.01,Oracle学习_01

创建表空间1

2

3

4

5CREATE tablespace fuziqin

datafile 'c:fuziqin.dbf'

size 100m

autoextend on

next 10m;

删除表空间1drop tablespace fuziqin

创建用户1

2

3create user fuziqin

identified by fuziqin

default tablespace fuziqin

给用户授权

–oracle数据库中常用角色

connect–连接角色，基本角色

resource–开发者角色

dba–超级管理员角色

–给fuziqin授予dba角色

grant dba to fuziqin;

–切换到fuziiqn用户下,创建一个person表1

2

3

4create table person(

pid number(20),

pname varchar2(10)

);

修改表结构1

2

3

4

5

6

7

8--添加一列

alter table person add (gender number(1));

--修改列类型

alter table person modify gender char(1);

--修改列名称

alter table person rename column gender to sex;

--删除一列

alter table person drop column sex;

查询表中记录1

2

3

4

5

6

7select * from person

--添加一条记录

insert into person (pid,pname) values

commit;

--修改一条记录

update person set pname = '王昱珩' where pid = 1;

commit;

三个删除1

2

3

4

5

6

7

8--删除表中全部记录

delete from person;

--删除表结构

drop table person;

--先删除表，再次创建表。效果等同于删除表中全部记录。

--在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。

--索引可以提供查询效率，但是会影响增删改效率。

truncate table person;

序列

序列不真的属于任何一张表,但是可以逻辑和表做绑定

序列:默认从1开始,依次递增,主要用来给主键赋值使用

dual:虚表,只是为了补全语法,没有任何意义创建序列

​ 语法 CREATE SEQUENCE 序列名 [相关参数]

参数说明

​ INCREMENT BY :序列变化的步进，负值表示递减。(默认1)

​ START WITH:序列的初始值 。(默认1)

​ MAXvalue:序列可生成的最大值。(默认不限制最大值，NOMAXVALUE)

​ MINVALUE:序列可生成的最小值。(默认不限制最小值，NOMINVALUE)

​ CYCLE:用于定义当序列产生的值达到限制值后是否循环(NOCYCLE:不循环，CYCLE:循环)。

​ CACHE:表示缓存序列的个数，数据库异常终止可能会导致序列中断不连续的情况，默认值为20，如果不使用缓存可设置NOCACHE

例

​ CREATE SEQUENCE SEQ_DEMO

​ INCREMENT BY 1

​ START WITH 1

​ NOMAXvalue

​ NOCYCLE

​ NOCACHE;1

2

3

4

5

6

7

8

9

10

11

12

13

14

15create sequence s_person;

--nextval :取得序列的下一个内容

--currval :取得序列的当前内容

select s_person.nextval from dual;

select s_person.currval from dual;

insert into person (pid ,pname) values (s_person.nextval,'华晨宇');

commit;

SELECT * from person;

nextval :取得序列的下一个内容

currval :取得序列的当前内容

select seqpersonid.nextval from dual;

select s_person.currval from dual;

用户1

2

3

4--解锁scott用户，密码tiger

alter user scott account unlock;

--解锁scott用户的密码【此句也可以用来重置密码】

alter user scott identified by tiger;

单行函数

作用于一行,返回一个值1

2

3

4

5

6

7--字符函数

select upper('yes') from dual;--YES

select lower('YES') from dual;--yes

--数值函数

select round(56.16, -2) from dual;---四舍五入，后面的参数表示保留的位数

select trunc(56.16, -1) from dual;---直接截取，不在看后面位数的数字是否大于5.

select mod(10, 3) from dual;---求余数

日期函数1

2

3

4

5

6

7

8

9

10--查询出emp表中所有员工入职距离现在几天。

select sysdate-e.hiredate from emp e;

--算出明天此刻

select sysdate+1 from dual;

--查询出emp表中所有员工入职距离现在几月。

select months_between(sysdate,e.hiredate) from emp e;

--查询出emp表中所有员工入职距离现在几年。

select months_between(sysdate,e.hiredate)/12 from emp e;

--查询出emp表中所有员工入职距离现在几周。

select round((sysdate-e.hiredate)/7) from emp e;

转换函数to_date(“要转换的字符串”,”转换的格式”) 两个参数的格式必须匹配，否则会报错。

即按照第二个参数的格式解释第一个参数。

to_char(日期,”转换格式” ) 即把给定的日期按照“转换格式”转换。

转换的格式：

表示year的：y 表示年的最后一位 yy 表示年的最后2位 yyy 表示年的最后3位 yyyy 用4位数表示年

表示month的：mm 用2位数字表示月；mon 用简写形式 比如11月或者nov ；month 用全称 比如11月或者november

表示day的：dd 表示当月第几天；ddd表示当年第几天；dy 当周第几天 简写 比如星期五或者fri；day当周第几天 全写

比如星期五或者friday。

表示hour的：hh 2位数表示小时 12进制； hh24 2位数表示小时 24小时

表示minute的：mi 2位数表示分钟

表示second的：ss 2位数表示秒 60进制

表示季度的：q 一位数 表示季度 (1-4)

另外还有ww 用来表示当年第几周 w用来表示当月第几周。1

2

3

4--日期转字符串

select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;

--字符串转日期

select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual;

通用函数1

2

3--算出emp表中所有员工的年薪

--奖金里面有null值，如果null值和任意数字做算术运算，结果都是null。

select e.sal*12+nvl(e.comm, 0) from emp e;

条件表达式

mysql和Oracle通用1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20--给emp表员工取中文名

select e.ename,

case e.ename

when 'SMITH' then '华晨宇'

when 'ALLEN' then '王昱珩'

when 'WARD' then '李健'

--else '付紫琴'

end

from emp e;

--判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，

--其余显示低收入

select e.sal,

case

when e.sal>3000 then '高收入'

when e.sal>1500 then '中等收入'

else '低收入'

end

from emp e;oracle中除了起别名，都用单引号。

oracle专用条件表达式1

2

3

4

5

6

7select e.ename,

decode(e.ename,

'SMITH', '华晨宇',

'ALLEN', '王昱珩',

'WARD', '李健',

'喵') "中文名"

from emp e;

多行函数

【聚合函数】：作用于多行，返回一个值。1

2

3

4

5select count(1) from emp;---查询总数量

select sum(sal) from emp;---工资总和

select max(sal) from emp;---最大工资

select min(sal) from emp;---最低工资

select avg(sal) from emp;---平均工资

分组函数

–查询出每个部门的平均工资

–分组查询中,出现group by后面的原始列,才能出现在select后面

–没有出现在group by后面的列,想在select后面,必须加上聚合函数

–聚合函数有一个特性,可以把多行记录变成一个值.1

2

3

4

5

6

7SELECT

e.deptno,

avg( e.sal )

FROM

emp e

GROUP BY

e.deptno;

–查出平均工资高于2000的部门信息1

2

3

4

5

6

7SELECT

e.DEPTNO,

avg( e.sal ) asal

FROM

emp e

GROUP BY e.DEPTNO

having avg(e.sal)>2000;

–所有条件都不能使用别名来判断.

例:select ename ,sal s from emp where sal>1500;

–查询出每个部门工资高于800的员工的平均工资1

2

3

4

5

6

7

8

9SELECT

e.DEPTNO,

avg( e.sal ) asal

FROM

emp e

WHERE

e.sal > 800

GROUP BY

e.DEPTNO;

where是过滤分组前的数据,having是过滤分组后的数据.where必须在group by之前,having是在group by 之后.

查询出每个部门高出800工资的员工的平均工资,然后再查出平均工资高于2000的部门1

2

3

4

5

6

7

8

9

10

11SELECT

e.DEPTNO,

avg( e.sal ) asal

FROM

emp e

WHERE

e.sal > 800

GROUP BY

e.DEPTNO

HAVING

avg( e.SAL ) > 2000;

多表查询

笛卡尔积1

2

3

4

5SELECT

*

FROM

emp e,

dept d ;

等值连接1

2

3

4

5

6

7SELECT

*

FROM

emp e,

dept d

WHERE

e.deptno = d.deptno;

内连接1

2

3

4

5

6

7SELECT

*

FROM

emp e,

dept d

WHERE

e.DEPTNO = d.DEPTNO;

右外连接(右边的表不加限制)

–查询出所有的部门,以及部门下的员工信息.[外连接]1

2

3

4

5SELECT

*

FROM

emp e

RIGHT JOIN dept d ON e.DEPTNO = d.DEPTNO;

左外连接(左边的表不加限制)1

2

3

4

5SELECT

*

FROM

emp e

LEFT JOIN dept d ON e.DEPTNO = d.DEPTNO;

Oracle专用外连接(+写在哪边,哪边就是匹配表)

几点注意:

1.放在等号左右都可,需要放在缺少信息的那一边

2.改符号仅适用于列,不能用在表达式上

3.不能和on和in操作符一起使用

4.全外连接不能使用1

2

3

4

5SELECT

*

FROM

emp e,dept d

where e.DEPTNO(+)=d.DEPTNO;

全外连接(左表和右表都不受限制)

所有记录都显示,两表不足的地方均为null.1

2

3

4

5SELECT

*

FROM

emp e

FULL JOIN dept d ON e.DEPTNO = d.DEPTNO;

查询出员工姓名,员工领导姓名,员工部门,员工领导部门

自连接

自连接其实就是站在不同角度把一张表看成多张表1

2

3

4

5

6

7

8

9

10

11

12SELECT

e1.ENAME,

e2.ENAME

FROM

emp e1,

emp e2,

dept d1,

dept d2

WHERE

e1.DEPTNO = d1.DEPTNO

AND e1.mgr = e2.EMPNO

AND e2.DEPTNO = d2.DEPTNO;

子查询返回一个值1

2

3

4

5

6

7

8

9

10

11

12

13--查询出工资和SCOTT一样的员工信息

SELECT

*

FROM

emp

WHERE

sal IN (

SELECT

sal

FROM

emp

WHERE

ename = 'SCOTT')返回一个集合1

2

3

4

5

6

7--查询出工资和10号部门任意员工一样的员工信息

SELECT

*

FROM

emp

WHERE

sal IN ( SELECT sal FROM emp WHERE DEPTNO = 10 );子查询返回一张表

–查询出每个部门的最低工资和最低工资员工姓名,和该员工所在部门名称先查询出每个部门最低工资

三表联查,得到最终结果1

2

3

4

5

6

7

8

9

10

11

12

13SELECT

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;

分页

rownum行号:当我们做select操作的时候,每查询出一行记录,就会在该行上加一个行号,

行号从1开始,依次递增,不能跳序1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17--排序操作会影响rownum的顺序

select rownum, e.* from emp e order by e.sal desc

--如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询。

SELECT ROWNUM

,

t.*

FROM

( SELECT ROWNUM, e.* FROM EMP e ORDER BY e.sal DESC ) t;

--emp表工资倒叙排列后，每页五条记录，查询第二页。

--rownum行号不能写上大于一个正数。

SELECT

*

FROM

( SELECT ROWNUM rn, tt.* FROM ( SELECT * FROM emp ORDER BY sal DESC ) tt WHERE ROWNUM < 11 )

WHERE

rn >5