ORACLE数据库开发笔记

第一章：数据库介绍

主要内容：

1. 数据库设计需要考虑的事项

1.1创建数据库的目标：

有关的业务功能；

客户的期望；

1.2应用中数据库的性能：

数据库是否易于维护；

创建数据库带来的开销；

万一设计后期有所变动时，数据库是否易于更新；

2. 实体关系（ER）图

确定各实体；

实体是一组带有易于识别的属性的对象，可以是物体、场地、人或记录数据的活动。

确定实体的属性；

属性是实体的特征，正是属性的信息使得一个实体有别于另一个实体。

确定实体之间的关系。

关系是实体之间的联系。

3. 把ER图转换成表的规则

3.1能独立存在的实体我们称之为正则实体，这样的实体务必转换成一个表；

3.2表示在ER图中的属性转换为相应的表的属性或列；

3.3若两个表关系是一对一的关系，可以在两表中互设外键，

（如果两个实体之间存在部分关系时）可将两表合并成一个表。

3.4若两表的关系是一对多的关系，则第一个表的主键可作为另一个表的外键存储。

3.5若两表的关系时多对多的关系，总会有一些属性与联系本身有关，因此，除了原来的两个表

外，联系本身也应转换成一个表，原来的两个表的主键在这个新表中作外键。

3.6为了易于辨识存储在这些属性中的内容的类型，我们在属性名的前面

加了c,v,d和n等前缀，前缀字母c，v，d和n分别代表

char，varchar(2)，日期和数据类型。

4. SQL语句介绍：

4.1 数据定义语言（DDL）：创建对象（create）、改变对象（alter）、撤消对象（drop）；

4.2数据操纵语言（DML）：存储数据（insert）、更新数据（update）、删除数据（delete）；

4.3 SELECT语句

第二章：查询数据库

主要内容：

1. 显示格式化的查询输出

1.1显示指定的列标题

select VLASTNAME "name"

from employee

where cemployeecode = '000005';

1.2 显示非重复行（distinct）

select distinct ccity

from externalcandidate;

2. 识别不同类型的运算符

2.1算术运算符：*、/、+、-；（%：是另一种运算符，返回除数的余数）;

select sysdate from dual;

select sysdate+3from dual;

2.2比较运算符：=、>、<、>=、<=、!=;

select VLASTNAME

from employee

where cemployeecode = '000005';

2.3并置运算符:||

select'Mr.'||vfirstname ||'-' || vlastname "name" from externalcandidate;

2.4逻辑运算符:and、or、not

select vfirstname , ntestscore

from externalcandidate

where ntestscore >75and csex='M';

2.5其他运算符：between...and、in

select corderno,ntotalcost

from orders

where ntotalcost between50and120;

3. 检索带匹配模式的数据

3.1通配符

select vfirstname ||'-' || vlastname "name" from externalcandidate

where vfirstname like'B%';

3.2定位符

select vToyname ,vToyDescription

from Toys

where vToyname like'_a%';

4. 检索带遗漏值的数据

select vfirstname,vlastname

from externalcandidate

where dinterviewDate is null;

5. 以特定的顺序检索数据(降序：DESC，升序：ASC) select cpositioncode,vdescription

from position

order by cpositioncode desc;

第三章：使用函数

主要内容：

1. 在select语句中使用函数

1.1字符函数：Character函数

INITCAP：（首字母大写）

select Initcap('tom')from dual;

select Initcap(vfirstname) from employee;

LOWER：（全部小写）

select lower(vfirstname) from employee;

UPPER：（全部大写）

select upper(vfirstname) from employee;

LTRIM：（去掉左边空格）

select ltrim(' tom ')from dual;

select ltrim(vfirstname) from employee; select ltrim ('abcdefg','abc') from dual;

RTRIM：（去掉右边空格）

select Rtrim(' tom ')from dual;

select Rtrim(vfirstname) from employee;

1.2数值函数：

ABS：（绝对值）

select abs(-25) from dual;

CEIL：（大于参数的最小整数）

select ceil(25.345) from dual;

FLOOR：（接收参数的整数部分）

select floor(24.56) from dual;

POWER：（乘方）

select power(4,3) from dual;

MOD：（余数）

select mod(5,2)from dual;

select mod(-5,2)from dual;

select mod(5,-2)from dual;

select mod(-5,-2)from dual;

ROUND：（对十进制值进行四舍五入）

select round (sysdate+5,'month') from dual;

select round(123.7423,0)from dual;

select round(126.123234,-1)from dual;

TRUNC：（截断取整）

select trunc(123.7423,0)from dual;

SQRT：（开方）

select sqrt(144)from dual;

1.3日期函数：

ADD_MONTHS：(增加月份)

select add_months(sysdate,18) from dual;

select add_months(to_date('2012-12-23','yyyy-mm-

dd'),4)from dual;

select add_months('12-apr-2012',4) from dual;

GREATEST：（比较两个日期，返回较大的）

select greatest('2012-1-10','2012-2-12') from dual; LEAST：（比较两个日期，返回较小的）

select least('2012-1-10','2012-2-12') from dual;

LAST_DAY：（显示某月最后一天）

select last_day(sysdate) from dual;

MONTHS_BETWEEN：（比较两个日期，返回相隔月份）

select months_between(sysdate,to_date('2002-02-

12','yyyy-mm-dd')) from dual;

NEXT_DAY：（返回当前日期的下一天）

select next_day('20-jan-00','monday')from dual;

2. 数据类型转换

2.1隐式转换：

select lpad(upper(dBirthdate),26,'date of birth

is')from externalcandidate;

2.2显式转换：

2.2.1日期格式化函数：

to_char:

select to_char(sysdate,'yyyy-mon') from dual;

select to_char(sysdate,'yyyy-mm') from dual;

select to_char(sysdate,'yyyy-mm-dd') from dual;

select to_char(sysdate,'yyyy-mon-d') from dual;

select to_char(sysdate,'yyyy-mon-day') from dual;

select to_char(sysdate,'yyyy/mm/dd') from dual;

select to_char(sysdate,'"今天是："yyyy"年"mm"月"dd"日"day-"现在是北京时间："am

hh24:mi:ss') from dual;

to_date:

select to_date('2012 2 3','yyyy mm dd')from dual;

round:

select round (sysdate+5,'month') from dual;

select round (to_date('17-aug-00','dd-mon-

yy'),'year') from dual;

trunc:

2.2.2数值格式化函数：

to_number:

select to_number(cemployeecode)from employee where

vfirstname='angela';

3. 通用格式化函数

select

rpad(lpad(substr(initcap(lower(upper(concat('good','morning' dual;

select vfirstname "firstname",nvl(vemailid,'noth') "email" from externalcandidate;

4. 条件表达式

第四章：查询多表中数据

主要内容：

1.等值连接

select e.vfirstname,a.nannualsalary

from employee e join annualsalary a

on e.cemployeecode=a.cemployeecode

order by a.nannualsalary desc;

2.自然连接

select cemployeecode,vfirstname,nannualsalary,nyear

from employee

natural join annualsalary

where nannualsalary between30000and40000;

3.交叉连接

select *

from requisition cross join position;

4.外部连接

select vfirstname "andidate name",cname "Recruitment

Agency"

from externalcandidate

left outer join recruitmentagencies

on

externalcandidate.cagencycode=Recruitmentagencies.Cagencycode 5.自连接

第五章：使用多行函数

1.多行函数

avg函数

select avg(ntestscore)

from externalcandidate

where ntestscore>80;

sum函数

select sum(ntestscore)

from externalcandidate

where ntestscore>80;

count函数

select count(ntestscore)

from externalcandidate;

select count(*)

from externalcandidate;

max函数

select max(ntestscore)

from externalcandidate;

min函数

select min(ntestscore)

from externalcandidate;

stddev函数（标准差）

variance函数（方差）

2.group by语句

select vfirstname,cstate

from externalcandidate

group by cstate,vfirstname;

select vfirstname,cstate

from externalcandidate

order by cstate,vfirstname;

select cstate,count(cstate)

from externalcandidate

group by cstate;

3.having by语句：

where子句是先根据where子句指定的条件进行筛选，然后再按group by

子句里指定的列进行分组；而having 子句则是先按group by子句里指定的列对数据

进行分组，然后再应用having子句中条件。

select cstate, avg(ntestscore)

from externalcandidate

group by cstate

having avg(ntestscore)>80;

select https://www.360docs.net/doc/b55700993.html,ewspapername,count(*)

from externalcandidate e , newsad n ,newspaper p where https://www.360docs.net/doc/b55700993.html,ewsadno=https://www.360docs.net/doc/b55700993.html,ewsadno and

https://www.360docs.net/doc/b55700993.html,ewspapercode=https://www.360docs.net/doc/b55700993.html,ewspapercode

group by https://www.360docs.net/doc/b55700993.html,ewspapername

having count(*)>3;

第六章：使用子查询

1.单行子查询：只返回单行或记录的查询

select vfirstname,ntestscore

from externalcandidate

where ntestscore=(select max(ntestscore) from externalcandidate);

with ag as (select cstate,avg(ntestscore) as

avg_score from externalcandidate group by cstate) select cstate, avg_score from ag where avg_score= (select max(avg_score) from ag);

2.多行子查询

select vfirstname,ntestscore

from externalcandidate

where ntestscore in (70,80,96);

select vfirstname,ntestscore

from externalcandidate

where ntestscore >any (70,80,96);

select vfirstname,ntestscore

from externalcandidate

where ntestscore >all (70,80,93);

select vfirstname,ntestscore

from externalcandidate

where ntestscore not in (70,80,96);

3.嵌套子查询

select vfirstname ,ntestscore

from externalcandidate

where ntestscore >all(select ntestscore from externalcandidate where cstate='Georgia');

第七章：创建和管理表

主要内容：

1.数据库对象的类型

表、视图、序列、索引、同义词

2.创建表：

drop table t_student;

create table t_student(

cstu_code char(8),

cname char(8),

csex char(2),

nage number(2),

dbrithday date,

vaddress varchar(200),

constraint pk_student primary key (cstu_code) );

create table t_student1(

cstu_code char(8) primary key,

cname char(8),

csex char(2),

nage number(2),

dbrithday date,

vaddress varchar(200),

);

create table t_computer(

ccom_id char(8) primary key,

ccode char(5) not null,

cstu_code char(8),

constraint com_stu_fk foreign key (cstu_code) references t_student(cstu_code)

);

3.更改表：

alter table employeereferrals add csex char(1);

alter table employeeskill add cdepartmentcode

char(5);

4.撤销表：

drop table employee;

第八章：管理表和实施数据完整性

1.添加表中行：

insert into t_computer

value ('1','com2','1');

2.更新表中行：

update employee

set cdepartmentcode='0003'

where cemployeecode='000015';

3.删除表中行：

delete from internalcandidate where

dtestdate

4.数据完整性：

4.1 实体完整性：主键唯一标示且不为null；

4.2域完整性：保证列中只能存储合法范围内的值；

4.3引用完整性：保证一个表中的列值与另一表中的相关列值的匹配；

4.4用户定义完整性：用户自己设定的一组规则。

5.定义约束：

5.1 主键约束：primary key约束用来保证表里存在唯一的记录，主键唯一且不为空。

5.2外键约束：与另一个表里的primary key想对应。

5.3唯一性约束：它允许有null，值唯一。

5.4检查约束：特定条件的约束。

5.5not null约束：如果没有输入某列的值，则默认为null；此约定使得每列的值都不为空。

第十章：使用视图（整理笔记）

主要内容：

1.创建视图；

2.更改、重命名和撤消视图

3.使用视图修改数据

4.使用内联视图。

5.使用Top-N方法。

视图是从一个或多个表中导出的行或列的子集。视图本身不包含数据，它只

起到一个窗口的作用，由此可以观看或修改基表的数据。数据字典把视图作为一条select语句加以保存。

视图优点：

1.视图可用来检索表中所选的列。（防止数据丢失）

2.可创建简单的查询，检索需要频繁调看的结果。

3.可用来从多个表中检索数据。

4.不需要时可以删除，不影响数据库。

//1创建视图

注意事项：

1.包含连接、组合和子查询的select语句可被包含在定义视图的子查询内。

2.创建视图时不能使用orderby子句，但为检索视图中数据，可用这条语句。

3.or replace选项用于修改视图定义，而无需撤消或重新创建它。//1.1创建简单视图

create view emp

as

select vfirstname,vlastname

from employee;

create view v_address

as

select

vfirstname,vlastname,cphone,vemailid,ntestscore from externalcandidate;

select * from v_address

where vlastname='Jones';

create view v_score

as

select vfirstname,ntestscore from externalcandidate where ntestscore>='60';

//force强制创建视图

create force view v_test

as

select * from t_test;

//or replace创建或替代视图

create or replace view v_country

as

select vfirstname,ccountry

from externalcandidate natural join country;

//1.2创建复杂视图

create view emp_dept_vu

as select vfirstname,vlastname,vdepartmentname from employee

join department on

employee.cdepartmentcode=department.cdepartmentcode; //2更改、重命名和撤消视图

//2.1更改视图

create or replace view emp_dept_vu

as select

vfirstname,vlastname,vdepartmentname,cCurrentPosition from employee

join department on

employee.cdepartmentcode=department.cdepartmentcode; //2.2重命名视图

rename emp_dept_vu to empdeptvu;

//2.3撤消视图

drop view empdeptvu;

select * from emp_dept_vu;

//3使用视图修改数据

//不能同时修改两个表里面的数据，如果修改必须在两个表里面分别修改

update emp_dept_vu

set vfirstname='Hello'

where vlastname='Smith';

//4使用内联视图：它是SQL语句的子查询，在主查询的from子句使用它吧，通过别名被引用它。、

select

e.vfirstname,e.nrating,e.cpositioncode,m.maxrate from externalcandidate e,(select

cpositioncode,max(nrating) maxrate

from externalcandidate group by cpositioncode) m where e.cpositioncode=m.cpositioncode and

e.nrating

select *

from (select vfirstname,ntestscore from externalcandidate);

//5使用Top-N分析法

//rownum是伪列，本来没有，加上的。

select rownum, vfirstname from externalcandidate; select vfirstname,ntestscore from externalcandidate order by ntestscore desc;

//同上比较，rownum能精确的查询到前几位，rownum只能是小于等于某个数。它往往用于分页查询

//查询前三行

select rownum vfirstname,ntestscore

from (select vfirstname,ntestscore from externalcandidate order by ntestscore desc)

where rownum<=3;

//分页查询

//查询6到10行

select * from

(select rownum r, vfirstname,ntestscore

from (select vfirstname,ntestscore from externalcandidate order by ntestscore desc))

where r between6and10;

第十一章：使用其他数据库对象（整理笔记）主要内容：（序列是重点，索引了解概念，同义词知道就行）

1.创建序列。

2.管理索引。

3.管理同义词。

//1.1创建序列，常常以seq为前缀

注意事项：

1.如果序列用于生成主键值，不要使用CYCLE选项；

2.按用途命名序列；

3.按递减顺序创建序列时，可以对INCREMENT BY子句指定一个负数值；

create sequence seq_student

increment by1

start with1;

create sequence seq_empid

increment by1

start with1

maxvalue100

nominvalue

nocycle

nocache;

create sequence seq_student

increment by3

start with9

maxvalue20

minvalue -3;

//1.2用insert语句插入

insert into student

values (seq_student.nextval,25);

//1.3查询序列

select seq_student.nextval from dual;

select seq_student.currval from dual;

//1.4更改序列

注意事项：

1.只有序列属主或有alter权限的用户才能修改序列；

2.不能使用alter sequence语句更改现有的序列整数，start with选项里的值；

alter sequence seq_student

maxvalue1000;

//1.5撤消序列

drop sequence seq_student;

//2管理索引

使用索引的优点：

2.1查询执行快；

2.2实施数据的唯一性；

2.3多表检索数据的过程快；

创建索引的规则：

1.取值范围很大的列；

2.有很多空值的列；

3.在where子句或联接里使用的列；

4.预期查询返回的行数小于表的总行数的2%。

索引类型：

唯一索引：带primary key或unique key约束时，Oracle系统自动创建唯一索引。

非唯一索引：人为手工创建。

//2.1创建索引

create index student

on employee(vfirstname);

select vfirstname from employee;

//2.2撤消索引

drop index student;

create user test1

identified by abc123

default tablespace users;

grant create session to test;

grant create table to test;

grant unlimited tablespace to test;

grant create session,create table,unlimited tablespace

to test;

grant select on user1.employee

to test;

grant update on user1.employee

to test;

select * from user1.employee;

//修改用户密码：

alter user test

identified by xyz456;

//收回权限

revoke update

on user1.employee

from test;

第十二章：管理用户访问（整理笔记）

主要内容：

1.创建用户

2.给用户管理权限

3.撤消用户

4.废除用户访问权限

5.创建角色

P12.11练习题

create user Jim

identified by manager;

grant create session to Jim;//授予连接权限（有此语句才可用通过用户名和密码来连接数据库）

//修改用户密码（方法一）

alter user Jim

identified by hello;//此时，密码不再是manager而是hello //修改用户密码（方法二）

在OracleSQL*plus窗口的SQL提示符后输入Password来修改密码。//1.创建用户和密码

create user Hello

identified by hello;

//2.给用户授予系统权限

grant create session,create table,create view,create sequence

to Hello;

//2.针对某一个表或视图给用户授予对象权限

grant select,alter,delete,index,insert

on user1.employee

to Hello;

//3.撤消用户:cascade是完全删除用户，加上他之后，可以连带用户创建的对象一同删掉

drop user Hello cascade;

//4.废除用户访问:此处create session权限是通过with admin option被授予的。

revoke create session

from Hello;

//5.能授予用户的有名的权限组合称为角色,角色可以是权限的组合，也可以是权限的组合。

CONNECT Role：分配给临时用户的角色；

RESOURCE Role：这个角色分配给常规用户；

DBA Role：这个角色拥有一切系统权限，包括不加限制的表空间配额。

//5.1CREATE ROLE语句适用于创建角色，创建角色时可以加密码也可以不加。

create role hello;或create role hello identified hello;

//5.2ALTER ROLE语句可用来为角色分配密码。

alter role hello identified world;

//5.3使用grant语句为角色授予权限。

grant create session

to hello;

alter user hello default role hello;

set role hello;

//set role none语句可以用来冻结一个用户

set role none;

//5.4撤消角色

drop role hello;