Oracle大总结

Oracle大总结

第一章 数据库简介 ......................................................................................................................... 2

1.1数据库的种类 ..................................................................................................................... 2

1.1.1数据库产品 .............................................................................................................. 2

1.1.2 数据库 ..................................................................................................................... 2

1.1.3 连接数据库 ............................................................................................................. 2

1.1.4 数据库语法 ............................................................................................................. 3

第二章 结构化查询语言(SQL) ...................................................................................................... 4

2.1结构化查询语言(SQL)....................................................................................................... 4

2.1.1 SQL(脚本script) ...................................................................................................... 4

2.2 数据定义语言(DDL(Data Defination Language)) ............................................................ 4

2.2.1 create ........................................................................................................................ 4

2.2.2 drop .......................................................................................................................... 4

2.2.3 alter........................................................................................................................... 5

2.2.4 rename…to… ........................................................................................................... 5

2.3 数据操作语言(DML(Data Manipulation Language)) ....................................................... 5

2.3.1 insert ......................................................................................................................... 5

2.3.2 update ....................................................................................................................... 6

2.3.4 delete ........................................................................................................................ 6

2.4数据查询语言(DQL(Data Query Language)) .................................................................... 6

2.4.1 select...................................................................................................................... 6

2.4.2 条件语句(where ,and ,or) ..................................................................................... 7

2.4.3 null,in,any,all ......................................................................................................... 7

2.4.4 between...and闭区间 ........................................................................................ 7

2.4.5 like: 模糊查询 ...................................................................................................... 7

2.4.6 排序order by ........................................................................................................ 8

2.4.7 不重复的distinct .................................................................................................. 8

2.4.8 group by 分组 ...................................................................................................... 8

2.4.9 联合查询 ............................................................................................................... 9

2.4.10 子查询 ............................................................................................................... 10

2.5 事务控制语言(TCL(Transaction Control Language) ...................................................... 12

2.6 SQL命令与SQL语句 ..................................................................................................... 12

第三章 函数 ................................................................................................................................... 12

3.1单行函数 ........................................................................................................................... 12

3.1 通用函数 ................................................................................................................ 13

2.2 字符函数 ............................................................................................................. 14

3.3 数字函数 ................................................................................................................ 15

3.4 日期函数 ................................................................................................................ 16

3.5 转换函数 ................................................................................................................ 17

3.2 分组函数 .......................................................................................................................... 19

第四章 事务 ................................................................................................................................... 19

第五章 约束条件 ........................................................................................................................... 20

第六章 数据库中的对象 ............................................................................................................... 21

第一章 数据库简介

1.1数据库的种类

1.1.1数据库产品

大型: Oracle(oracle公司) db2(IBM) Sybase(Sybase公司)

中型: sql server(Microsoft) mysql(Oracle公司)

小型: access (Microsoft)

自己安装oracle数据库:(在账户管理步骤解锁scott)

Oracle 9i / 10g / 11g

Oracle 11g 不要安装在win7操作系统下.

1.1.2 数据库

Database:数据库: 保存数据,管理数据,维护数据,是有效组织在一起的数据集合.

RDBMS: 关系型数据库管理系统:(Relation Database Management System) 包含两方面: 数据库 和 管理系统

基本存储单元: 二维表(只有一个主键)

--虚表dual, 测试函数. oracle数据库独有.

表由行和列组成.

行: row, record(记录)

列: column, field(域)

--表示为数据库中的注释

关系型数据库表的特征:

行唯一 / 列唯一 / 同一列的数据类型相同 /

单元(cell)不可分割.

1.1.3 连接数据库

1.登录到服务器:

登录服务器: telnet 192.168.0.40

帐号/密码: openlab/open123

2.登录到Oracle数据库

sqlplus: 数据库提供的连接和操作客户端工具

sqlplus csoracle/tarena

sqlplus: 数据库提供的连接和操作工具

1.1.4 数据库语法

1.1.4.1 SQL语法

SQL语言大小写都可以,可以写在一行或多行

关键字不能缩写也不能分行,使用缩进可以提高可读性

空值:不是空格或者0或者‘null‘(这是个字符串)

1.1.4.2 数据类型

数据类型

数据类型 描述

varchar2(size) （字符型的）可变长字符数据(oracle特有) varchar(size) （字符型的）mysql里有可变长字符数据

nvarchar(size) Unicode字符型mysql里有长度是所输入字符个数的两倍 nvarchar2(size) Unicode字符型的长度是所输入字符个数的两倍(oracle特有) char(size) 定长字符数据

number或number(int)或number(p,s) 可变长数值数据

date 日期型数据

long 可变长字符数据，最大可达到2G

clob 字符数据，最大可达到4G

raw and long raw 裸二进制数据

blob 二进制数据，最大可达到4G

bfile 存储外部文件的二进制数据，最大可达到4G rowid 行地址

第二章 结构化查询语言(SQL)

2.1结构化查询语言(SQL)

2.1.1 SQL(脚本script)

SQL(Structured Query Language):

大多数数据库都采用相同或者相似的语言(SQL)来操作和维护数据库.

2.2 数据定义语言(DDL(Data Defination Language))

用来创建结构/删除结构/修改结构

2.2.1 create

1创建用户表users

create table users (id number(4) primary key, password char(10), name char(20) ); --PK primary key表示主键,非空且不重复(唯一) 一个表只有一个PK --出现Table created表示表创建成功.

常见问题----有时出现表名已经存在,则需要换个表名

--主键可以有两列如下：

create table tab(

id1 number(2) --primary key列级约束

id2 number(4)

credit number(8)

constraint pk_tab primary key (id1,id2) --主键 表级约束

数据库会为主键自动创建对应的唯一索引

2 复制表

create table emp1 as select * from emp;

3 create sequence myseq; 创建序列

4 设置默认值 create table beyond(id number default 1);

5 create user root（用户名） identified by pwd（密码）;创建一个用户

2.2.2 drop

Drop table tablename 删除表,不可以回滚,撤底删除了

Drop view viewname 删除视图

2.2.3 alter

--增加一个新列（是表的最后一列）

Alter table beyond add name char(8);

--修改列的类型，大小,默认值等

Alter table beyond modify id char(2);

--删除一列

Alter table beyond drop column 列名;

--修改列名

alter table 表名 rename column 旧列名 to 新列名

2.2.4 rename…to…

执行rename语句改变表, 视图, 序列, 或同义词的名称

Rename beyond to emp;

2.3 数据操作语言(DML(Data Manipulation Language))

用来新增数据 / 修改数据 / 删除数据

2.3.1 insert

1新增用户数据到表中(一次只能增加一条记录.)

insert into users(id, password, name) values(1001, '1234', 'liucs');

如果插入的是全部列的数据可以简写成:

insert into users values(1001,‘1234‘,‘liucs‘);

还可以指明要插入哪几列数据:

insert into users(id,name) values(1001,‘liucs‘);

--如果出现1 row created 表示新增数据成功.

sql server中into可以省略,而oracle不可以

insert into emp (mpno, name, hiredate) values(1234, upper('sql'), sysdate); 2 insert into ...(查询语句):一次增加多条记录

先新建一个表emp1 (结构和emp相同)把emp表中所有记录都插入emp1表中 insert into emp1 (select * from emp);

--将查询语句的结果增加到emp1表中

新建一个表beyond表,有id,name

insert into beyond(id,name) (select empnumber, name from emp);

2.3.2 update

修改1001号学生的姓名为beyond

update tablename

set name =‘beyond‘,salary=salary*1.2,

hiredate = to_date('2011/06/05','yyyy/mm/dd') where id = 1001;

2.3.4 delete

删除表中的数据(记录)

truncate table tablename清空表，不能回滚,释放表的存储空间和delete差不多

delete tablename可以回滚,清空表，删除所有行

delete from emp where department is null;

delete emp where id=1; 删除表中的记录,就是行

delect from beyond where id=1;

使用truncate语句在功能上与不带where子句的delete语句相同，但truncate语句 比delete快,delete语句以物理方式一次删除一行，并在事务日志中记录每个删除行； 而truncate通过释放存储表数据所用的页来删除数据，并且只在事务日志记录页中释放 ，因此在执行truncate之前应先对数据库备份，否则被删除的数据将不能被恢复．

2.4数据查询语言(DQL(Data Query Language))

用来返回符合条件的数据记录

2.4.1 select

1 查询表中的数据

select * from users;------------*表示所有列

2查询指定列:id, password

select id, password from users

3查询有没有id:1001,password:1234的用户?

select * from users

where id = 1001 and password = '1234';

4 数字列做+-*/运算

Select c1,c2*12 from users

5 定义列的别名

Select c1,c2*8 name from users或

Select c1,c2*8 as name form users

6 空值 null 表示暂未提供数据. null值(要在前面)和任何数据做算术操作都得到null 任何数据类型(数字/日期/字符)都可以是null

假设b 为null—算术运算时null列要放在前面

Select a , b,b+a from beyond

2.4.2 条件语句(where ,and ,or)

select 列名 / 表达 from 表名 where 条件

select name, salary, job from emp where salary > 7000 and department = 10;

select name, salary, job from emp where department = 10 or department = 20;

select name, salary, job, department from emp where (department = 10 or departmetn = 20) and salary > 7000;

--and的优先级比or高, 可以用()改变优先级.

2.4.3 null,in,any,all，union(all)

Null:值最大，排序时

--数据库中的null值不能使用=比较

--可以使用is null(是空) 或者 is not null(不是空)

In:在列表中,（not in）不在列表中

--查出部门10或者部门20的员工

select name, salary, department from emp where department in (10, 20);

any: 和子查询返回的任意一个值比较

all: 和子查询返回的所有值比较

union:去掉重复的行

union all：把重复的也显示出来了

select * from beyond where id>5 union select * from beyond where id>2;

select * from a where id>5 union all select * from a where id>2;

2.4.4 between...and闭区间

(not between and)不在．．．．区间

Select age from users where age between 20 and 30;(包括20和30)

--查询在09年5月1日到10年5月1日入职的员工

select name, job, hiredatfrom emp where hiredate between

to_date('2009-05-01', 'yyyy-mm-dd')

and

to_date('2010-05-01', 'yyyy-mm-dd');

2.4.5 like: 模糊查询

%: 通配符, 任意字符

_ : 通配符,表示一位字符.

--名字的第三个字符开始是ang,后边字符不限

select namfrom emp where name like '___ang%';

可以使用escape 标识符 选择‘%’和 ‘_’ 符号。

2.4.6 排序order by

--表里的数据本身是无序的，不是按第一列排序的，是按照插入的顺序排列的

-- null值被视作最大，order by短语永远在语句的最后

默认是升序,也可以用asc(从低到高)

Desc: 降序排列(高到低) --数字,字符和日期都能排序.

select * from users order by age desc;

--用来排序的字段不一定出现在select短语中.

--可以使用列或列的别名或者表达式排序.

select name,age, age * 12 from users by age* 12;

select name ,age from users by 1;--第一列排序

--多个列排序:

先按部门排序,同一部门按薪水由高到低排序

select name, salary, department from emp order by department, salary desc;

2.4.7 不重复的distinct

Distinct 只能放在select后面 不要重复的 all与它刚好相反

--机构下的职位有哪些?

select distinct job from emp;

--各部门不重复的职位有哪些?

--distinct指后边的列组合起来不重复.

select distinct department, job from emp;

2.4.8 group by 分组

--凡是出现在select短语后的非分组函数的列,必须出现在group by后面

--错误的例子:

select job, count(*) from emp;

--但是出现在group by后的列,不一定要出现在select后,没有错但不这样用

--没有语法错误,但信息不全.

select count(*) from emp group by job;

having 只紧跟出现在group by后面进行条件筛选 可以用分组函数条件也

可以用一般条件表达式条件

where 一定要出现在group by的前面 where不能用分组函数条件,只能用一般表达式条件 where短语中不允许出现组函数.

例如:select department, sum(salary) from emp where department is not null

group by department having sum(salary) > 2000 order by department;

细化分组:group by后可以接多个,按先后顺序分组

select department, job, count(*)from emp group by department, job;

--计算各个部门的薪水总和, 返回薪水总和大于2000元的部门?

select department, sum(salary)from emp

where department is not null或(in (10,20)) group by department

having sum(salary) > 2000 order by department;

2.4.9 联合查询

--FK: foreign key 外键, 参照另一个列的值.

外键关系表达的是一种一对多的表间关系.

Emp表的department列参照department表的department列,

emp表叫做子表(从表多的一方), (被参照的表)

department表叫父表(主表一的一方).

例: select name, d.location, e.department from department d

Inner(可以省略默认是内连接) join emp e on e.department = d.department; --t1叫驱动表, t2叫匹配表, 驱动表和匹配表可以互换

一般为效率把小数据的做驱动表

1 (内连接)等值连接:

t1 join t2 on t1.c1 = t2.c2

t2 join t1 on t1.c1 = t2.c2

驱动表和匹配表可以互换位置，结果相同。

t1: 1w条， t2: 100w条

t1 join t2 on t1.c1 = t2.c2; （建议）

t2 join t1 on t1.c1 = t2.c2;

--查询职位是manager的员工都是哪些部门的?

select e.name, d.name, e.job from emp e

join department d on e.department = d.department and e.job = 'manager'; 2(内连接)非等值连接 :原理和等值连接相同.

select e.name, e.salary, s.grade from emp e

join salarygrade s on e.salary between s.lowsalary and s.hightsalary; 3 笛卡尔积.

--如果没有连接条件,结果是两个表记录的组合,

select e.name, d.name, e.job from emp e , department d

4 (内连接)自关联(自连接):一个表的字段取值参考自己的主键列 select e.empno, e.ename, m.empno, m.ename

from emp e join emp m on e.manager = m.empnuber;

5 外连接:

驱动表和匹配表不能互换.(主要是将内连接的不匹配的数据显示) 外连接的结果集 = 内连接的结果集 + 驱动表中在匹配表中 找不到匹配记录的数据和null值的组合。

--t1是驱动表

t1 left outer join t2 on t1.c1 = t2.c2;

t2 right outer join t1 on t1.c1 = t2.c2;

--t2是驱动表

t2 left outer join t1 on t1.c1 = t2.c2;

t1 right outer join t2 on t1.c1 = t2.c2;

--哪些部门没有员工?

--用匹配表的主键是null,返回那些驱动表中没有匹配记录的数据.

select e.name, d.empnumber, d.namefrom emp e

right outer join department d on e.department = d.department

where e.empnuber is null;

6 全外连接 full outer join 驱动表和匹配表可以互换,结果相同.

select e.name, d.name from emp e

full outer join department d on e.department = d.department;

2.4.10 子查询

--谁的薪水是最低的?

select name, salary from emp where salary = (select min(salary) from emp); -- = > < <>符号是单行比较运算符,不能用在比较多个数据上. 替换为in --哪个部门赚最少钱的是谁?

select name, salary, department from emp\

where (department, salary) in (

select department, min(salary) from emp group by department);

--哪个部门的人数比部门20人多?

select department, count(*) from emp group by department

having count(*) > (select count(*)

from emp where department = 20);

--所在部门的平均薪水高于6000的员工名字和薪水.

select name, salary, department from emp where department in (

select department from emp group by department having avg(salary) > 6000); --哪个部门的平均薪水是最高的? 列出部门编码和平均薪水.

max(avg(salary)) 最多只能嵌套一层.这是最多的了

select department, avg(salary) from emp group by department

having avg(salary) = (select max(avg(salary))from emp group by department); --关联子查询

--查询从主查询开始,遍历主查询中的表,取出其中的数据,传递到子查询中, 计算子查询的结果,再参与到主查询中.

--哪些员工的薪水比本部门的平均薪水高?

select name, salary, department from emp o

where salary > (select avg(salary) from emp where department = o.department); select name from emp o where(not) exists

(select ?x‘(*) from emp where manager = o.empartment);

--非关联子查询

--查询出所有有下属的员工(是其他人的经理)

select empnuber, name from emp where empnumber in (select distinct manager from emp where manager is not null);

--使用自关联实现:

select distinct m.empnuber, m.name from emp w

join emp m on w.manager = m.empnumber;

2.4.11 rownum伪列

rownum可以查询前m条,不能查询第n条以后的数据.

select * from beyond where rownum<10;查询前10行的

select rownum , id,name from beyond where rownum<50;

select id from beyond where rownum>2;没有结果显示，只能显示从１开始的数据 --a,b为（行内视图，匿名视图，临时视图）

先按id排好序给个行号放在视图里a，再从视图a里选出前１００条放在b视图里, 再从b视中选出７５到１００的行（a,b可以省略）

select id,name rn from

(select id ,name ,rownum rn from

(select id,name from beyond order by id) a

where rownum<=100) b

where rn>=75;．

rownum oracle独有的

rowid标识一条记录的物理位置oracle独有

这样不能得出想要的结果，取薪水最少的前１０个：

select rownum, name, salary from emp where rownum < 10 order by salary;

上面代码：是先给了表行号１,２.．．rownum，再去排序，所以排序后行号是乱的 是按salary排序的，行号乱七八糟的

select ename,salary from (select ename ,salary from beyond

where salary is not null order by salary desc) where rownum<4;

这样能查询出薪水最高的前三位

--[m, n] 闭区间

select id, name, rn from(select id, name, rownum rn from

(select id, name from beyond order by id)

where rownum <= n ) where rn >= m;

查询m到n行

rowid标识一条记录的物理位置oracle独有的

select rownum , rowid from beyond;每行物理位置有顺序

--借助rowid伪列去掉重复记录

--借此了解rowid伪列的作用.删除重复行的：

delete from beyond where rowid not in

(select min/max(rowid) from beyond group by id);

TOP-N分析:

查出职员表中薪水最高的三个人

--先取前三个数据,再按薪水排序.得不到结果.

select name, salary from emp

where rownum < 4 order by salary desc;

--先排序,得到行内视图(匿名视图),再取前三个.就可以得到结果

select name, salary from(select name, salary from emp

where salary is not null order by salary desc) where rownum < 4;

2.5 事务控制语言(TCL(Transaction Control Language)

Commit;确认前面的操作,提交

Rollback或rollback to A回滚

Savepoint A;设置断点,好回滚 详细请看事务

2.6 SQL命令与SQL语句

1 sql命令:可以加分号,也可以不加 以下都是的:

host cls(自已的) host clear(服务器的) 清屏

/ :表示执行上一条语句.

Ed;或edit;：打开了一个纪事本修改你前面打的sql语句，保存 关闭后，在sqlplus里敲个／（反斜杠）可以执行纪事本里的sql 语句．

Ed/或deit/:也可以打开一个纪事本，但没什么用

Desc或describe:

desc tablename(表名) 或desc tablename;可不写分号 查看表的结构

column 列名 format a10把列的长度设为10,方便显示好看,关了就没用了 set echo on/off show echo

2 sql语句:必须加分号 以下都是的:

Commit;提交 确认之前对数据库所有操作

sql命令: 只在sqlplus中生效,只能当前使用,关闭当前窗口就没有作用了

sql语句:一直有用

第三章 函数

3.1单行函数

F3(F2(F1(col,arg1),arg2),arg3)单行函数可以嵌套n层

--虚表dual, 测试函数. oracle数据库独有.

--单行函数: 函数加工的每一行数据都产生一行结果.

3.1 通用函数

3.1.1 nvl函数

nvl(p1, p2) : 返回第一个非空表达式，是oracle数据库特有的

if (p1 == null) return p2;

else return p1;

例:如果薪水为null则用0替换

select salary , nvl(salary,0) from emp;

nvl(job, 'programmer')- -工作(job)为null用(程序员)programmer替换

nvl(hiredate, '11-APR-11')或nvl(hiredate,‘01-6月-11)--入职时间(hiredate)为null 用后面的日期替换

Nvl2(p1,p2,p3):如果p1不是空，则执行p2,否则执行p3

3.1.2 Coalesce

coalesce(p1,p2)或coalesce(p1,p2,p3,…..) 返回第一个非空表达式

select coalesce(2,3,4) from dual;结果:2

3.1.3 Decode

1 分支语句:job中可能有空格要去掉(trim),如果是analyst,salary*1.2否则如果

是programmer,salary*1.15,…….都不是则直接是salary

select name, job, salary,

decode(trim(job), 'analyst', salary * 1.2,

'programmer', salary * 1.15,

'salesman', salary * 1.05,

salary) new_salary

from emp;

2 Case …when…then…else…end分支语句

select name, job, salary,

case job when 'analyst' then salary*1.2

when 'programer' then salary*1.15

when 'salesman' then salary*1.05

else salary end new_salary（别名）

from emp;

2.2 字符函数

2.2.1 initcap

把字符串首字母改成大写

select initcap('beyond') from dual;结果:Beyond

2.2.2 concat和||

字符串连接(concat和 || ):连接字符串

Select concat(?bey‘,‘ond‘) from dual;

select ?bey‘ || ?ond‘ from dual;

可以合成列:select id || name from beyond;

2.2.3 Length:

求字符串长度 Select name,length(name) from users;

2.2.4 lpad 和 rpad

--rpad(name,10,‘$‘): 右补位, 将name列补齐10位,不足10位的,

在右边用'$'(也可以是其他字符)补齐.

select name, rpad(name, 10, '$') from users;

--lpad(name,10,‘a‘): 左补位, 将name列补齐10位,不足10位的, 在左边用'a'(也可以是其他字符)补齐.

select name, lpad(name, 10, 'a') from users;

2.2.5 lower和upper

--大写转换函数upper(name) :select name from users

where upper(name)=‘BEYOND‘;

--小写转换函数lower(name):select name from users

Where lower(name)=‘beyond‘;

错误的例子:

select name from users where upper(name)=‘beyond‘;

select name from users where lower(name)=‘BEYOND‘;

--常量每一行都输出一次.

Select lower('SqlPlus') from dual;

2.2.6 trim

去掉前后的空,不去中间的(去掉首尾的空(空格,制表符…))

Ltrim 去掉左边的空 rtrim 去掉右边的空

select trim(' alfdj alfd ') from dual;结果: alfdj alfd

select trim('b' from 'beyond') from dual;结果:eyond

select trim('d' from 'beyond') from dual;结果：beyon

select trim('e' from 'beyond') from dual;结果:beyond（只去掉首尾的）

2.2.7 replace (string, if, then)

用 0 或其他字符或字符串代替字符串中的字符。―if‖是字符或字符串，对于每个出现在―string‖中的―if‖，都用―then‖的内容代替。

select replace('aabcdddab','a',9) from dual;结果: 99bcddd9b

select replace('aabcdddab','ab','ooo') from dual;结果: aooocdddooo

2.2.8 instr (string,substring)

默认1开始或(string, substring,int ): 该命令―string‖中从int位置开始查找字符集合的位置，返回substring第一次出现在位置。―int‖的值也可以是负数，代表从字符串结尾开始向反方向搜索。该函数也用于数字和日期数据类型。

select instr('aadfalfdhaf','f',3) from dual;结果:4

select instr('kafhal','f') from dual;结果:3

select instr('aadfalfdhaf','f',5) from dual;结果:7

2.2.9 substr(String,int,length)

截取从指定位置int开始到多长length(下标从1开始的)

select substr('kafhal',2,3) from dual;结果:afh

select substr('kafhal', 3) from dual;结果:fhal(从3开始到最后)

select substr('kafhal',0,3) from dual; 结果:afh

select substr('kafhal',-1,3) from dual;结果:l

select substr('kafhal',-2,3) from dual;结果:al

select substr('kafhal',0,3) from dual;结果:hal

3.3 数字函数

3.3.1 round

round:四舍五入

select round(123456.789, 2) from dual;结果:123456.79

select round(123456.789) from dual;默认为0结果:123457 select round(123456.789, 0) from dual;结果:123457

select round(123456.789, -1) from dual;结果:123460

select round(123456.789, -2) from dual;结果:123500

3.3.2 trunc

trunc: 截取

select trunc(1234.999) from dual;默认是0 结果:1234

select trunc(1234.999, 0) from dual;结果:1234

select trunc(1234.999, 2) from dual;结果:1234.99

select trunc(1234.999, -2) from dual;结果:1200

区别: round(data, 0) trunc(data, 0)

1234.567 1235 1234

1234.123 1234 1234

1234.999 1235 1234

3.3.3 mod

mod:取余数

Select mod(10,3) from dual;结果:1

Select mod(8,2) from dual:结果:0

3.3.4 abs

Select abs(-4) from dual;绝对值

select abs(+-0.0) from dual;结果：０

select abs(0.1) from dual;结果：.1

select abs(+-1.0) from dual;结果：1

3.4 日期函数

3.4.1 sysdate

取系统时间的函数: sysdate:

--默认的日期显示形式: DD-MON-RR

select sysdate from dual;结果:03-jun-11或 03- 6月 -11版本不同

--日期 (+-) 数字(天) = 另一个日期

select sysdate + 6 from dual; 6天以后的日期

select sysdate - 3 from dual; 3天前的日期

--两个日期相减: 数字,表示间隔多少天.

select hiredate(入职时间), round(sysdate – hiredate) from emp;

sysdate – hiredate是有小数的(天数要四舍五入)

3.4.2 months_between(date1,date2)

--两个日期间隔多少个月: months_between 返回带小数的数字值,四舍五入. select hiredate(入职时间),round(months_between(sysdate, hiredate) ) from emp;

3.4.3 last_day

某个月的最后一天

select last_day(sysdate) from dual;本月最后一天的日期

3.4.4 next_day

下一个周几的日期

select next_day(sysdate, '星期天') from dual;或 版本不同

select next_day(sysdate, 'SUNDAY') from dual;

3.4.5 add_months

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

add_months ('11-JAN-12',6)-? 11-JUL-12

add_months ('11-1月-12',３)-? 11-4月 -1２

3.5 转换函数

to_char to_number

日期 --------------->字符串-------------->数字

<---------------- <--------------

to_date to_char

3.5.1 to_char:

1.1把日期数据转换为字符数据 或 1.2数字转换为字符串

1.1 格式: to_char(日期数据, '格式')

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual; /*日期格式数据:

yyyy : 四位数字的年

year : 英文全拼的年

mm: 两位数字的月

month: 英文全拼的月

dd: 两位数字的天

day: 全拼的周几

dy: 简拼的周几

hh24: 24小时制的小时

hh12: 12小时制的小时

am : 上午或下午

mi: 分钟

ss: 秒

*/

select to_char(sysdate, 'year month day dy hh12:mi:ss am') from dual; 结果: twenty eleven 6月 星期六 星期六 12:02:49 上午

select to_char(sysdate, 'yyyy "年" mm "月" dd "日" ') from dual; 结果: 2011 年 06 月 04 日

1.2 格式: to_char(数字, '格式'): 数字转换为字符串

select to_char(1234567.89, 'L9,999,999.99') from dual;

结果显示: $1,234,567.89或￥1,234,567.89(字符串)

3.5.2 to_date

to_date(字符数据, '格式'): 把字符数据转换为日期数据

select to_date('2011-05-02', 'yyyy-mm-dd') from dual;

--用处:将日期数据插入到数据表中:

3.5.3 to_number

to_number(字符数据,'格式'):字符串转换为数字

select to_number('$1,234,567.89','$999G999G999D00') from dual; select to_number('$1,234,567.89','$99G999G999D00') from dual; select to_number('$1,234,567.89','$9G999G999D00') from dual; 都对:

结果显示: 1234567.89(数字)

-- 字符和数字转换时的格式:

9: 对应位的数字有,原样显示,没有,不显示.

0: 对应位的数字有,原样显示,没有,显示0

,: 千位符, G

.: 小数点, D

$: 美元符号

L: Locale, 本地货币, RMB,￥

-- *****格式的长度 >= 数据的长度 一定要注意的

to_char(数据, 格式):

select to_char(13234567.89, 'L9,999,999.99') from dual;结果是########### to_number(数据, 格式):

select to_number('$1,234,567.89','$9G99(这里少一位)G999D00') from dual;出错

select to_number('$13,234,567.89','$9(这里少一位)G999G999D00') from dual;出错 to_date(数据, 格式):

select to_date('2011-05-02', 'yyy-mm-dd') from dual;出错

3.2 分组函数

max(avg(salary)) 分组函数最多只能嵌套一层

1 count(总数)

Count(*) 或 count(列名) 针对所有类型数据,计算数据表的行数

select count(*) from emp;

--多行数据参与函数运算,最后返回一行结果.

2 sum(求和)

--计算总和的sum(列名) 只针对数字

select sum(salary) from emp;

3 avg(平均值)

--只针对数字

select avg(salary) from emp;

4 max / min(最大值/最小值)

--可以针对数字,字符,日期

select max(salary) ,min(salary), from emp;

sum(salary) max(salary) min(salary) avg(salary)(忽略空值)

count(*)(不忽略有空的行的) count(salary)忽略空值

select count(*) from emp;--行中有空的也算所有的行

select count(bonus) from emp;--bonus中有空的不算这一行

第四章 事务

1. 事务 : Transaction:

一组DML操作的集合. 或者一起成功,或一起失败.

事务的起点: 第一条DML语句.

事务的终点: commit; rollback;

在事务中,数据的状态:

--在事务中数据的变化外界不可见.

使用DML事务(挂起)会锁住,其他的不能DML操作

select * from beyond for update;查询时加锁，查询的结果要更新时

--事务内部变化的数据加锁,事务之外不能对这些数据操作.直到事务终止,锁被释放. --可以通过rollback回滚这些操作.(终止事务)

释放锁.所有的改动被回退. 缓存被清空.

--可以通过commit确认这些操作.(终止事务)

释放锁. 所有的改动被确认,其他事务能看到这种改动.缓存被清空.

--DDL操作会隐式的提交事务.

断电事务会回滚

------------事务的开始-------------

Update emp set name = 'tom'

where id = 2;--加锁

delete from myemp;

select * from beyond for update;查询时加锁，查询的结果要更新时

.....

------------事务的终止-------------

commit; 或者rollback;

create table mytemp(id number);

insert into mytemp values(1);

savepoint A;

insert into mytemp values(2);

savepoint B;

insert into mytemp values(3);

savepoint C;

insert into mytemp values(4);

savepoint D;

rollback to B;之后的C D都没有插入,不能再rollback to C了

第五章 约束条件

1 对数据完整性的保证.

主键 / 外键 / 非空 / 唯一 / 检查

--非空:不能为空,但可以重复.

--唯一:不能重复,可以为空.

--主键 = 非空 + 唯一.

create table student (id number primary key,

name char(10) not null,

email char(20) unique);

insert into student values(1, 'scott', 'scott@1.com');

insert into student (id, email) values(2, 'tiger@1.com'); --错不能为空

insert into student values(3, 'tiger', 'scott@1.com');--错唯一的

--检查约束

create table student (id number primary key,name char(10) not null,

sex char);

create table beyond(sex char(1) check(sex in('m','f')));建表时加性别约束 --在建表之后增加约束条件.

alter table student add constraint stu_sex_ck (约束名随便起只要不重复) check (sex in ('F', 'M')); --增加记录,性别中能是F,M

alter table student add constraint a_ck check(id>0 and id<1000);

删除约束 alter table student drop constraint a_ck;

--复制表,约束条件不复制.(复制过来的表没有主键了)

增加外键约束

alter table emp1 add constraint emp1_department_fk(约束名)

foreign key (department) references emp1(departmentnumer);

增加主键约束

alter table emp1 add constraint emp1_emp1_pk primary key (emp1);

有约束条件不能直接修改或删除的

删除约束 alter table emp1 drop constraint emp1_emp1_pk;

第六章 数据库中的对象

视图 / 序列 / 索引 / 同义词/过程 / 函数 / 包 / 触发器

user_tables: 用户名下所有的表.数据字典

user_objects : 用户名下所有的对象

user_catalog: 查看用户定义的表, 视图, 同义词和序列

select * from usert_ables;

select distinct object_type from user_objects;

select * from user_catalog;

1.视图 view

--创建视图 基于一个或多个基本表

create view v_emp as select empnumber, name, salary from emp;

create or replace view a as Select * from beyond;

简化查询

create or replace view v_emp1 as select departmentnumber, job,count(*) count_s, avg(salary) avg_s, sum(salary) sum_s, max(salary) max_s, min(salary) min_s from emp group by departmentnumber, job order by departmentnumber, job; --查看视图结构:

desc v_emp

--查看视图的数据,实际是基表的数据

--修改，删除视图数据时，每一次更新操作只能影响一个基本表

--当视图依赖多个基本表时，不能向视图插入数据，因为会影响多个基本表

select * from v_emp;

--delete from v_emp或delete v_emp删除视图中的数据,基表的数据也删除了 删除视图数据时，对于依赖多个基本表的视图不能使用delete语句 update v_emp set id=100 where id=5;修改视图，基表的数据也改变了 drop view v_emp;

--删除视图

删除视图只是删除视图的定义，并不会删除基表的数据

--删除了视图，基表不会被删除的

2 序列 sequence

提供主键自增长功能.默认从1开始,步进是1

create sequence myseq;

create sequence myseq start with 2000 increment by 2;

insert into beyond(id, name)values(myseq.nextval, 'scott');

--序列产生的唯一值可以为多个表提供主键.

--序列删除以后对已产生的数据没有任何影响.

修改序列

修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内

存

alter sequence my_seq increment by 20 maxvalue 999999

nocache nocache;

删除序列 drop sequence my_seq;

3 索引index

创建索引 create index aaa_index on beyond(id);

删除索引 drop index aaa_index;

3 管理权限

Grant 用于把权限授予某一用户

grant select on beyond to root（用户）;授予查询的权力

grant connect,resource to root；授予连接资源的权力

grant create table to root;授予创建表的权力

Deny 用来禁止用户对某一对象的权限

Revoke 撤销用户对某对象的权限

revoke select on beyond from root; 撤销查询的权力

revoke connect,resource from root; 撤销连接资源的权力

revoke create table from root; 撤销创建表的权力