Oracle笔记剖析.docx

Oracle笔记剖析.docx

《Oracle笔记剖析.docx》由会员分享，可在线阅读，更多相关《Oracle笔记剖析.docx（35页珍藏版）》请在冰点文库上搜索。

Oracle笔记剖析

‘2016.6.21

1、数据库的基本概念：

1、什么是数据库（dbDatabase）:

存储数据的空间（仓库）。

包括联机数据库和数据仓库。

2、数据库中存储的数据（Data）：

用于描述事物的符号，包括数值、日期、字符、图像、声音等。

3、信息（InformationInfo）：

数据经过加工后的产物。

是数据的另一种表现形式。

4、数据库的管理系统（DBMS）：

数据库的管理软件，例如：

用于对数据进行备份、还原；用户权限的管理；数据的操作等。

是数据库产品的主要标志。

5、数据库系统（DBS）：

数据库+数据库管理系统+设备硬件+用户。

数据库系统是系统软件。

2、数据库的发展历史：

1、人工管理阶段（40年代中~50年代中）：

人工使用卡片、磁带记录管理数据；

2、文件管理阶段（50年代中~60年代中后）：

人们使用文件系统存储数据；

3、数据库管理（60年代末~至今）：

使用数据库存储管理数据。

（1）层次数据库：

底层采用树数据结构实现；

（2）网状数据库：

底层采用图数据结构实现；

（3）关系型数据库：

采用关系代数的关系模型实现的数据库；

（4）面向对象数据库：

采用对象模型实现的数据库。

3、数据模型：

包括概念数据模型和物理数据模型。

1、概念数据模型：

对客观世界进行分析和抽象所得到的模型称为概念数据模型。

概念数据模型采用实体—关系图来描述，实体关系图成E-R图。

（1）矩形表示实体；

（2）椭圆表示实体属性；

（3）无方向线将实体和属性连接；

（4）实体之间的关系使用菱形表示，无方向线连接；

（5）实体与实体关系的类型：

i.一对多的联系；

ii.一对多的联系；

iii.多对多的关系。

2、物理数据模型：

反应实现世界书局特征，在计算机中形成的数据模型，DBMS实现的依据。

（1）层次模型：

用树表示各类实体与实体的关系；

（2）网状模型：

（3）关系模型：

建立在严格的数学理论基础上的；

（4）面向对象模型：

3、物理数据模型——关系模型：

关系模型组成：

数据结构+关系操作+完整性约束

1）数据结构：

采用表的结构，表具有行和列结构组成，通过实体性抽象得到；

2）列：

称为字段，通过属性抽象得到；

3）键：

用于区分实体的字段，包括候选键、主键、备选键；

候选键：

具有唯一值且不为null；

主键：

具有唯一值特征，且不为null，一般与业务无关；

备选键：

候选键——备选键

4）行：

实体属性值的集合，称为元组。

5）数据完整性（考试、面试）：

业务数据的正确性和完备性。

包括实体完整性、关联完整性、自定义完整性。

a）实体完整性：

通过主键实现完整性；

b）关联完整性（参照完整性）：

通过外键实现的数据完整性。

一个表a的主键出现在另一个表b中，在表b中称为外键，a表称为主键表，b表称为外键表，外键的值必须参照主键表中的值，即外键的值必须是主键表中所有主键值中的一个。

例：

部门信息

Deptnodnameloc

10销售部北京

20研发部上海

30财务部天津

员工信息

Empnoenamedeptno

1001小李········20

1002小王········10

1003小赵········20

1004小刘········30

4、关系型数据库设计的基本三范式（NF）：

（面试、考试）

（1）第一范式（1NF）：

表的字段是原子的，不可再分的；

（2）第二范式（2NF）：

在第一范式的基础上，存在非主属性对主属性的函数依赖关系；（表中要有主键）

（3）第三范式（3NF）：

在第二范式基础上，一个表的非主键字段不应该和主键字段同时出现在另一个表中。

（拆表解决数据冗余）

（4）补充范式（BC）：

4、Oracle数据库安装：

1、关系型数据库产品：

产品名称所属公司

（1）Oracleoracle

（2）DB2IBM

（3）SybaseSybase

（4）MySQLoracle

（5）SQLServerMicrosoft

2、Oracle数据库简介：

（1）Oracle产生：

（2）Oracle8i,9i,10g,11gg：

grid

（3）版本划分：

企业版、标准版、开发版

3、数据库的安装：

安装文件的目录不要有中文数据口令：

orcl

2016.6.22

1、Oracle的基本配置：

1、oracle卸载：

不要使用oracle自带的卸载工具进行卸载。

（1）停止Oracle所有服务；

（2）进入注册表，删除与oracle有关的所有键值；

运行---->regedit---->HKEY-LOCAL-MACHINE---->SOFTWARE删除oracle

HKEY-LOCAL-MACHINE---->SYSTEM---->

分别进入controlset001、controlset001--->services删除oracle有关键值

关闭注册表；

（3）删除oracle的产品安装目录

（4）删除oracle软件安装目录

（5）删除开始菜单oracle有关的内容和path路径下oracle路径配置

2、Oracle的配置：

（1）关于服务：

建议设置启动类型为手动，需要时用户来启动；

1）oracleDBConsole：

支持使用企业管理器对数据库进行远程图形界面管理的服务；

2）Listener：

监听服务；对连接请求进行监听，对连接信息进行验证，通过验证负责建立客户端与数据库服务器的连接，一个数据库服务器可以建立多个监听；

a）Oracle数据库服务器的结果：

两层结构和三层结构

补充：

两层C/S结构：

client/server，客户端直接访问数据库服务器

三层架构B/S：

Browser/Server，客户端访问应用服务器，应用服务器访问数据库服务器

b）监听服务配置：

通过图形界面或修改配置文件

I：

使用NetManager添加监听；

II：

修改NETWORK\ADMIN\LISTENER.ORA文件

III：

启动新配置的监听服务，在doc命令提示符下输入：

lsnrctl

再输入startlistenername

3）Oracle实例服务：

oracle处于练级状态必须启动此服务；

（2）Oracle用户管理：

必须具有管理权限的数据库管理员。

数据库管理员包括超级管理员sys和普通管理员system.

超级管理员在本机登录无需账号和密码，system账号本机或远程登录需要账号密码信息。

Doc命令提示符输入：

Sqlplus/nolog---启动sqlplus不登陆

Conn/assysdba

普通账户：

Sqlplussystem/orcl---system管理员账号登录

Sqlplusscott/123456---普通用户登录

登陆后的账户切换：

Conn/assysdba---切换超级管理员登陆

Connsystem/orcl---切换普通管理员登录

Connscott/1234---切换普通用户

1）用户的创建、修改和删除：

a）用户创建：

Createuser用户名identifiedby密码

【defaulttablespaceusers】【temporarytablespacetemp】

b）用户修改

i.修改用户密码：

Alteruser用户名identifiedby新密码；

ii.用户的锁定和解锁：

1.锁定：

Alteruser用户名accountlock；

2.解锁：

Alteruser用户名accountunlock；

c）用户的删除：

Dropuser用户名；

2）用户权限管理：

a）授权：

权限分为系统权限和对象权限，除了数据操作的权限，其他的都是系统权限

Grant···to用户名

案例：

给新用户授予建立连接和使用数据库服务器资源的权限

Grantcreatesession，resourceto用户名；

角色：

权限的集合，首先将权限都授予一个角色，将角色授予用户

createrolemyrole;

Grantcreatesession，resourcetomyrole；

grantmyroleto用户名;

b）取消权限：

Revoke···from用户名；

2016.6.23

2、Sql中的注释

1、行注释：

--注释内容

2、快注释：

/*……*/

3、Sql概述：

1、sql：

StructureQueryLanguage，结构化查询语言，是所有的关系型数据库支持的工业标准，用于创建修改删除表、视图用户等；用于数据的增删改查、以及权限管理等。

2、Sql分类：

（1）数据的定义语言：

DDL，包括的语句：

create、alter、drop。

（2）数据的查询语言：

DQL，包括：

select

（3）数据的操纵语言：

DML，包括：

insert（添加），update（修改），delete（删除）

（4）数据的控制语言：

DCL，包括：

grand，revoke

（5）数据的事务语言：

DTL，包括：

begintransaction、savepoint、commit、rollback

（3）和

（2）行业术语是C（reate，insert）R（ead，select）U（pdate，update）D（elete，delete）

4、Oracle的数据类型：

1、数值型：

number

（1）整数型：

number或number（n）

1）number：

表示一个整类型，长度默认38为；

2）Number（n）：

n表示数字的个数或位数。

（2）浮点型：

number（m,n）：

m表示数字的个数，n表示小数点后数字的个数

（3）字符型：

1）char（n）：

定长的字符型，若分配的空间没有用尽，剩余空间使用空格，n最大4000；

2）varchar2（n）：

不定长字符型，若分配的空间没有用尽，剩余空间被收回，n>=4000；

3）nchar（n）：

与char类似，n取值减半，2000；

4）nvarchar2（n）：

与varchar2类似，n取值减半，2000；

（4）日期型：

date，oracle中默认合法日期格式：

‘日-数字+月-yyyy’

（5）时间戳型：

timestamp，包含完整的日期时间

（6）大对象类型：

分为字符型和二进制（字节）

1）字符大对象：

clob，用于存储字符型数据，最多4G；

2）字符大对象：

long，用于存储字符型数据，最多2G；

3）二进制大对象：

blob（binarylargeobject），最多4G。

5、Oracle中的标识符：

合法，尽量规避oracle中的关键字

6、运算符：

1、算数：

+、—、*、/、mod（m,n）

2、关系：

>、>=、<、<=、=、!

=或<>

3、逻辑：

and、or、not

4、字符连接：

||

5、其他运输符：

（1）like：

像、相似，与%和_连用，进行模糊比较。

%表示0个或多个任意字符；_表示任意一个字符。

like的运算数都是字符；

like‘张_%’

（2）In或notin：

取列表中的某一个值，或不取列表中的某一值；

（3）between……and（包括边界值）或notbetween……and（不包括边界值）

（4）isnull或isnotnull

7、PL/SQL简介：

ProsedureLanguage/SQL，由于SQL是所有关系型数据库支持的工业标准，只描述了指令能做什么，怎么做的过程和细节无法更改，各个数据库厂商根据自己产品特点，都添加了近似于第三代编程语言的特征的过程化编程语言，用于加强数据处理的能力，orcale添加PL/SQL，DB2添加SQL/PL，Server添加了T-SQL.

8、Oracle中的表：

1、表的创建：

表由行和列组成，一个表至少要包含一个列。

（1）创建语法：

Createtable[dbuser.]tablename

（

Colname1datatype,

Colname2datatype,

……

Comnamendatatype

）

案例：

创建一个学生表t_stu1，包含字段有：

stuid，stuname，stuage，stusex

createtablet_stu1

（

stuidnumber（4）,

stunamevarchar2（20）,

stuagenum

（2）,

stusexchar（6）

）

案例：

创建一个学生表t_xsxx，字段：

xsbh，xsxm，xsnl，xsxb

（2）创建表时给表字段添加默认值属性。

语法：

colnamedatatypedefaultval

案例：

创建学生信息表，性别添加默认值1

（3）

2、实现数据的完整性：

（1）业务数据的正确性和完备性。

包括实体完整性、关联完整性、自定义完整性。

通过约束实现。

1）非空约束：

notnull

2）主键约束：

primarykey

3）唯一之约束：

unique

4）检查约束：

check（自定义完整性）

5）外检约束：

foreignkey

（2）注意：

在表中实现约束可能涉及一个列，称为行级约束，可能涉及多个列，称为表级约束，表级约束放在所有列之后定义，行级约束可以直接跟在列之后。

约束都是有名字，不能重复，当没起名时，DBMS自动命名

表级约束语法：

Constraintscconstraintsnameconstraintstype（col_list）

（3）Notnull行级约束：

直接跟在列之后

（4）Primarykey主键约束：

一个表中有且只有一个主键约束，可以是表级约束，也可以是行级约束，主键字段默认不允许为null，值不能重复

案例：

创建学生表t_stu2,包含：

stuid，stuname，idcard，添加主键和非空约束

案例：

课程表t_course，字段：

cid，cname，grade

案例：

创建选课表，字段：

cid，stuid，score

练习：

创建员工表，字段：

工号，姓名，职务，入职时间，工资，奖金

主键约束，非空约束

2016.6.24

1、表约束：

1、notnull约束

2、primarykey

3、unique唯一值约束：

具有唯一值约束的列允许为null，一个表可有多个唯一值约束。

一旦添加数据，唯一值约束的列不能重复。

unique是行级约束，或者表级约束

案例：

创建学生表：

其中身份证号具有唯一值约束

4、check：

检查约束，用于完成列值域的限定，用于完成自定义完整性。

与like、关系、逻辑、in、between…and等联合使用

案例：

创建学生表，给主页字段添加check约束

5、Foreignkey：

实现关联完整性或参照完整性，一个表A的主键出现在另一个表B中，该字段作为B标的外检键，用于实现B表与A表的关联，具有外键约束的B是外键表，A表是主键表。

注意：

1）外键字段的值必须出现在主键表A表的主键值；

2）主键表有限创建，再创建外键表；

3）添加数据先添加主键表数据，再添加外键表；

4）删除时先删除外键表或数据，再删除主键表或数据，外键字段类型必须与主键表主键字段一致，列名可以不一致；

5）建立外键约束时可以指定级联删除，可以先删除主键表数据，关联的表或数据一同删除；

6）外键字段的值允许为null.

案例：

创建部门表和员工表，并建立外键约束。

6、

2、修改表：

可以修改列的数据类型，可以添加删除列，添加删除约束，重命名列

1、修改列的数据类型：

若表中有数据，必须遵循数据类型和长度，长度只能改大不能改小，

Altertabletablenamemodefycolnamedatatype

2、添加列：

不能在中间插入，只能追加。

Altertabletablenameaddcolnamedatatypeconstraints……

3、删除列：

Altertabletablenamedropcolumncolname

4、添加约束：

Altertabletablenameaddconstraintsc_namec_type（colname,……）

5、删除约束：

通过约束名字进行删除

Altertabletablenamedropconstraintsc_name

6、重命名列：

Altertabletablenamerenamecolumn=旧名字to新名字

3、重命名表：

Renameoldnametonewname

4、删除表：

Droptabletablename

一、数据查询：

1、数据查询：

使用select…from…语句

2、查询简单所有数据

3、补充：

数据的导出与导入

i.导出：

exp，输入选项设置多数是默认

ii.导入：

impadminname/adminpwdfromuser=usernametouserusernamefile=c:

/pubs.dmp

Impsystem/orclfromuser=satouser=scottfile=c:

/pubs.dmp

2016.6.27

1、数据查询回顾：

1、select*fromtablename

2、Selectcol_listfromtablename

2、在select语句中使用表达式运算：

1、查询的信息不在表或视图中：

案例：

查询5+3的值；

2、查询呢的数据在表中：

案例：

查询工资增加20%后的员工的姓名和新工资信息

3、在select子句中使用友好列名：

1、selectcolnamenewname，colnamenewname1…fromtablename||dual||view_name

2、selectcolnameasnewname，colnameasnewname1…fromtablename||dual||view_name

3、selectcolnameas“newname”…fromtablename||dual||view_name

4、使用distinct去掉查询中的重复记录：

Selectdistincttype_fromtitles

案例：

查询所有图书的类型

5、结果集的选择：

查询时结果集中保留有用的记录，不符合条件的记录将被排除。

1、语法：

Selectcolname_listfromwherecondition;

2、执行：

对根据condition对每条记录都进行比较运算，当condition返回true时，记录将出现在结果集中。

3、案例：

6、排序结果集：

1、语法：

Selectcol_list

fromtablename

wherecondition

orderbycolname1[，colname2…]asc升序desc降序

2、语法说明：

（1）orderby排序关键字

（2）排序字段：

可以是select子句中出现的列名或友好列名或直接表达式，也可以是表中没有出现在select子句的列名，还可以写字段在select出现的索引值，第一个字段的索引值是1

（3）排序规则：

先按照主排序字段排序，若主排序值有重复，按照此排序字段的值排序，若都相同按照表中顺序排序

（4）Asc表示升序，desc表示降序

（5）Orderby最后执行部分

（6）null按照最大值处理

3、案例

7、在查询中使用oracle的系统函数，单行函数和聚合函数

1、单行函数：

包括数学方面、字符方面、日期方面、通用方面、转换方面

对于每一行的列进行操作，会返回一个结果。

单行函数可作为表达式或select子句的一部分。

2、数学函数：

（1）mod（m,n）：

取模运算

案例：

（2）round（m,n）：

四舍五入，m操作数，n精度，0保留整数位，若是-1保留到十位，若是1保留到小数点后一位

案例：

（3）trunc（m,n）：

数字截断，n可正可负，类似round。

3、字符函数：

大小写，字符操作

（1）大小写：

i.upper（charvalue）

ii.lower（charvalue）

iii.initcap（charvalue）：

单词第一个字母大写

（2）操作

i.concat（ch1,ch2）

ii.trim（ch）：

去掉收尾空格；或trim（‘H’from‘HelloHHHH’）去掉收尾指定字符

iii.substr（ch,i）：

字符串截取；或substr（ch,i,n）从ch字符串的第i个开始截n个

iv.replace（str,oldchar,newchat）：

v.lpad（ch,len,newchar）：

vi.rpad（ch,len,newchar）：

4、日期函数：

日期可以与数字进行加减运算

案例：

查询所有员工入职前五天与后五天的日期

（1）返回当前服务器的系统日期：

sysdate

Selectsysdatefromdual

（2）返回本周或下周星期几的指定日期：

next_day（日期,星期）

根据指定的日期和星期，若星期已过返回下周的星期对应的日期，否则返回本周的星期对应的日期。

案例：

查询周一的日期

（3）MONTHS_BETWEEN（date1,date2）：

返回指定日期所经历的的月数

案例：

查询所有员工入职月数

（4）返回n个月后的日期：

add_months（date，n）

案例：

查询5个月后的日期

（5）日期四舍五入：

round（date，month|year）

当根据月份四舍五入时，看天数，若天数>=16，返回下月第一天，否则返回本月第一天；按年四舍五入，看月份，若月份>6，返回下一年的第一天

（6）日期截断：

参数与round相同

（7）返回年或月份数字的函数：

extract

5、转换函数：

可以实现类型的转化或根据格式进行转换

（1）数字转字符：

to_char

（2）字符转数字：

to_number

（3）字符转日期：

to_date

（4）日期转字符：

to_char

1）数字与字符的转换：

I、to_number（data，format）

数字的格式：

$：

美元

L：

区域货币

数字：

0或9格式中支持使用千分位分隔符‘，’

II、to_char（data,format）

2）日期与字符的转换：

I、字符转日期：

to_date（data,format）

Format:

年：

yyyy、yy、rr

月：

MM

日：

D、DD、DDD

时：

HH或HH24

分：

MI

秒：

SS

注意：

若数据中含有其他非西文字符，使用””括起来

II、日期转字符：

to_char（data,format）

6、通用函数：

与null值有关的函数，一般情况下null与非布尔型运算，结果都为null，可以使用通用函数对null进行设置

（1）nvl（colname,value）:

当colname为null，返回val