Oracle笔记.docx

Oracle笔记.docx

《Oracle笔记.docx》由会员分享，可在线阅读，更多相关《Oracle笔记.docx（18页珍藏版）》请在冰豆网上搜索。

Oracle笔记

Oracle笔记

学习内容

1、学会安装与卸载

2、理解关系型数据库

3、了解Oracle的版本、oracle9ioracle10goracle11g

4、为什么学习SQL数据库

5、SQL语句的分类

6.创建表

7、删除表

8、修改表

9、约束

11、DML语句insert，update，delete

12、PL/SQL编程语法

13、存储过程

14、触发器

15、视图

16、索引

17、查询（普通查询，高级查询）

18、序列

19、用户管理（权限）

20、创建数据库（表空间..）

21、SQL*PLUS工具

22.PL/SQLDeveploer第三方工具

23、PowerDesigner数据库设计工具

安装

sys与system

sys用户是超级用户，具有最高权限，具有有sysdba角色，有createdatabase的权限

system用户是操作员，权限也很大，具有sysoper角色，没有createdatabase的权限

一般，对数据库的维护，使用system用户。

连接数据库

（1）数据库的服务要启动

a、services.msc打开服务对话框

b、OracleDBConsoleorcl：

Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。

在运行EnterpriseManager（企业管理器OEM）的时候，

需要启动这个服务打开这个服务可以通过控制台来操作数据库

c、OracleServiceORCL/EX：

数据库服务（数据库实例），是Oracle核心服务该服务，是数据库启动的基础，

只有该服务启动，Oracle数据库才能正常启动.

d、OracleOraDb11g_home1TNSListener：

监听器服务，

服务只有在数据库需要远程访问的时候才需要（包括PowerDesigner数据库设计工具）。

（2）有正确的用户名和密码

SQL*PLUS,基于命令的SQL*PLUS

语法：

sqlplus用户名/密码[@hoststring][用户身份]

例如：

sqlplussys/assysdba（只要sys用户才能指明sysdba身份，必须指明）

注意：

（1）如果是sys用户，则必须指定身份，assysdba或者assysoper

（2）如果指定了sys的身份，可以不输入密码，系统也会登录数据库。

（3）输入sys用户身份，没有输入密码，可以正常登录的先决条件是：

必须系统管理员。

select*fromall_users;查看用户数；

创建用户的基本命令

（1）必须具备创建用户的权限才能创建用户；

（2）学会创建用户的命令。

语法：

createuser用户名identifiedby密码

例如;

createusersxfidentifiedbysxf；

select*fromall_users;查看所有用户

grantconnect,resourcetosxf;

如果在oracle下创建表是基于在某个用户下创建表的

（3）新创建的用户不具备任何权限，即：

不能连接数据库，必须授予权限

：

grantconnect,resourcetosxf;

（3）SQL*PLUS工具

它是提供一种连接数据库的直接执行方式，是一个客户端应用工具。

SQL语句

SQL语言大致分为如下几类：

（1）数据定义语言（DDL），如：

create,drop,rename…

（2）数据操作语言（DML），如：

insert,update,delete…

（3）数据控制语言（DCL），如：

grant,revoke

（4）查询语言，如：

select…

（5）事务控制语言（TransactionControl，TC），如：

commit,rollback,savepoint…

：

http:

//ora-查询错误提示信息的意思

select*fromuser_catalog查询当前用户下有哪些表和序列

commit：

让数据生效。

操作：

（1）显示表结构

desc表名

（2）查看缓存

l或list只能显示上一次输入的SQL语句。

（3）重新运行缓存中的SQL语句。

/或r运行上一次输入的SQL语句。

（4）n命令

把第n行设为当前行，

（5）deln删除缓存中的第n行

（6）a命令

第三章创建表与维护表

1、数据类型

（1）创建表时候，都必须为表中的每一列指定数据类型。

（2）CHAR（length）:

存储固定长度字符串。

length参数指定了字符串的长度。

如果存储的字符串长度较小，就在字符串末尾以空格补全。

（3）VARCHAR2（length）:

存储可变长度字符串。

length参数指定了字符串的长度。

即使存储字符串长度比指定长度小，

也不用在字符串末尾以空格补全。

（4）DATE:

存储日期和时间类型。

date数据类型格式：

日-月-年如：

'17-12月-14'

（5）NUMBER（m,n）:

存储浮点数，但也可以存储整数。

m代表存储数字的最大位数，

n代表小数点右边的最大位数。

如果没有指定m，也没有指定n，那么默认存储38位精度的数字。

（6）foalt:

Oracle10g提供一种新数据类型，用于存储一个单精度的32位浮点数

（7）double:

Oracle10g提供一种新数据类型，用于存储一个双精度的64位浮点数。

selectsysdatefromdual;查看系统当前日期；dual：

虚表（不存在的表）可以其他表代替

sysdate：

显示当前系统时间的函数。

2.创建、修改和删除表

（1）语法格式：

createtable表名

（列名数据类型[default默认值][,…]）

例如：

createtablestudent（

idnumber（5）,--学生编号

namevarchar2（10）--学生姓名（注释））

（2）Oracle数据库中的表和列名，其命名规范：

（a）必须以英文字母开头，之后跟大写或小写英文字母，或数字，或#，或$，或_。

（b）名字的长度最短为一个字符，最长为30个字符

（c）一定不能与Oracle数据库系统的保留关键字相同

（3）复制表

语法格式：

createtable表名as子查询

例如：

createtableworkerasselect*fromemp

（4）（复制表）利用子查询来创建表需要注意如下事项：

（a）Oracle使用指定的列和表名来创建表，所有的行（记录）由子查询得到，并插入到该表中

（b）指定的列名的个数一定要和子查询的个数相同

（c）指定每列的数据类型必须与子查询的每列数据类型相匹配

（d）指定列名必须符合列的命名规范

（e）如果没有给出列名，列名与子查询中的列名相同，而且列的个数和子查询列的个数相同

（5）在表中增加新列

altertable表名add（列名数据类型[default默认值][,列名…]）

（6）在表中修改已存在的列

altertable表名modify（列名数据类型,default默认值，.....）

修改表中的列注意事项：

a：

可以增加字符类型的列的宽度

b：

可以增加数字类型的列的宽度和精度

c：

只有当所有列的值都为空或者表中没有数据时，才可以减少列的宽度

d：

只有当所有列的值都为空时，才可以改变某一列的数据类型

e：

如果改变某一列的默认值，该默认值只能影响以后的操作

f：

只有当某一列没有改变该列的大小的情况下，才可以把char类型的列改变成varchar2类型的列，

或者把varchar2类型的列改变成char类型的列

（7）删除表的列

altertable表名dropcolumn列名（同时删除数据）

删除表中的列要注意如下事项：

a：

使用altertable语句对列进行删除，一次只能删除二列，需要注意一点：

altertable表名drop（列名，列名,…）。

去掉column关键字，且多列用括号括起来

b：

使用altertable语句对列删除过后，该表至少还有一列

c：

因为altertable语句是DDL语句，所以删除的列是无法恢复的

d：

所删除的列可以包含数据，也可以不包含数据

（8）删除表

A.droptable表名

a、如果你不但要删除表中的数据而且还要删除表的结构，请使用droptable语句，

b、droptable语句有如下特征：

（a）它删除表中所有的数据行和表结构

（b）它也删除表的所有索引

（c）如果没有备份的话，所删除的表无法恢复

（d）它提交所有的挂起的事务

（f）所有基于该表的视图和别名依然保留但已无效

c.注意:

通过drop删除表时，从Oracle10g开始，被删除的表放入回收站，这称为闪回删除（flashbackdrop），这样可以恢复被删除的表。

如果你不想将表放入回收站可以增加一个“purge”。

格式：

droptable表名[cascadeconstraints][purge]

*如果两张表之间有外键（约束）关系，那么删除表的时候Oracle要求删除所有具有cascadeconstraints

恢复已删除的表：

falshbacktable表名tobeforedrop;

查看回收站

showrecyclebin

清空回收站

purgerecyclebin

B。

当一个表中数据已经不再需要时，可以使用truncatetable语句将它们全部删除掉（截断）

格式：

truncatetable表名

a、truncatetable语句有如下特性

（a）它删除表中所有的数据，但保留表结构

（b）如果没有备份的话，删除的数据行无法恢复

（c）该语句释放表所占用的磁盘空间

3、插入数据

*当往一个表中添加一行新的数据时，需要使用DML语言中的insert语句，

格式：

insertinto表名[（列名，列名…）]values（数值，数值…）（列名可以省略）

4、更新数据

*如果要对数据库中某一行或是多行更新数据，可以使用UPDATE语句

格式：

update表名set列名=数值[,列名=数值][where条件]

selectrownumn,[列名....]from表名，给表中的每一行添加行号,

5、删除数据

*删除数据通过DELETE语句来完成

格式：

deletefrom表名where【条件】；

删除后需要commit提交，不然不会真正删除，可以使用rollback来回滚恢复。

6、约束

（1）非空（NOTNULL）约束：

所定义的列决不能为空

（2）唯一（UNIQUE）约束：

在表中每一行中所定义的列，其列值不能相同

（3）主键（PRIMARYKEY）约束：

唯一标识表中的一行

（4）外键（FOREIGNKEY）约束：

用来维护子表（ChildTable）和父表（ParentTable）之间的引用完整性

（5）条件（CHECK）约束：

表中每行都要满足该约束条件

*引入约束的目的就是防止那些无效或有问题的数据输入表中，维护数据库数据的唯一性。

Oracle中约束关键字constraint

*完整性约束（即：

主键约束（primarykey）），要实现主键约束有以下2种常用的方法。

（1）在创建表的时候，在列中添加primarykey

（2）为以创建好的表中，通过关键字constraint为表中的某一列添加约束

例如：

createtablec（

2idvarchar2（10）,

3namevarchar2（10）,

4constraintpk_c_idprimarykey（id）

5）;

：

把表建好之后再创建主键

altertableAaddconstraintPK_A_IDprimarykey（id）

*外键（foreignkey）

a。

外键允许为空。

b。

向子表的外键插入数据时，其值必须在父表中能够找到。

第一种方法：

语法：

constraint外键名称foreignkey（子表列名）references父表名（父表主键）

例如：

Createtablestudent（--学生表

S_idnumber（5）primarykey,

S_namevarchar2（20）,

t_idvarchar2（5）,

constraintfk_stuforeignkey（t_id）referencesteam（t_id）（建立外键约束）

）；

Createtableteam（--班级表

t_idvarchar2（5）primarykey,

t_namevarchar2（20）

）

第二种方法：

把表建好之后再创建外键

altertable子表名称addconstraintfk_stuforeignkey（子表名称）references父表名（父表主键）；

*删除外键方法：

altertable子表名称dropconstraint外键名称；

*非空（notnull）

例如：

Createtablestudent（

idnumber（5）primarykey,

namevarchar2（20）notnull（非空）

）

另一种方式非空约束实现方式：

altertablestudentmodifynamenotnull

altertable表名modify列名notnull。

*唯一（unique）

例如：

唯一约束的实现

createtablestudent（

idnumber

（2）primarykey,

namevarchar2（10）unique--设置列“name”唯一约束

）

另一种方式实现唯一约束：

altertablestudentaddconstraintuk_stuunique（name）

altertable表名addconstraint约束名称unique（列名）

*条件约束（check）

语法：

constraint约束名称check（约束条件）

例如：

createtableasd（

2idintprimarykey,

3namevarchar2（20）,

4ageinteger,

5sexvarchar2（5）,

6constraintasd_ck_agecheck（agebetween15and34）,（条件约束）

7constraintasd_ck_sexcheck（sex='女'orsex='男'）（条件约束）

8）;

*查看约束

colcolumn_namefora10

colownerfora10;（格式化显示数据）

1、利用数据字典user_constraints可以查看用户模式下的表的约束信息

语句：

selectowner,constraint_name,constraint_type,table_namefromuser_constraints;

C（CHECK）条件约束

R（foreignkey）外键约束（fk）

P（pirmarykey）主键约束（pk）

2、利用数据字典user_cons_columns可以查看每张表定义的约束是在哪一列上的

语句：

selectowner,constraint_name,table_name,column_namefromuser_cons_columns;

第四章SQL查询语句基础

1.一般SELECT语句的格式如下：

select*|{[distinct]列名，列名，…}from表名

[where条件]

[groupby分组列名]

[having聚合函数]

[orderby排序列名ASC|DESC]

*在SQL语句中可以使用算数表达式：

+、-、*、/。

例如：

selectempno,ename,sal+500fromemp；（不会改变原表中的数据）

*为表中的列与表添加别名

selectempno员工编号,ename员工名称fromemp（为列取别名）

selectempno,enamefromempa（为表取别名）

*连接运算符

在SQL查询语句中可以将两列查询的结果连接在一起，通过“||”实现。

例如：

selectempno||ename（可以添加别名）fromemp;

*如果查询的结果中有重复的值，可以使用DISTINCT对重复值进行过滤。

例如：

selectdistinctdeptnofromemp;

*WHERE子句

在SELECT语句中使用WHERE子句来过滤查询出来的结果。

WHERE子句的格式：

WHERE列名比较操作符数值|表达式|列名

Oracle提供了如下常用的比较操作符：

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

=（不等于）、between…and、in、like

*AND、OR、NOT使用

AND、OR、NOT是作为Oracle中的3个逻辑运算符（操作符）。

它们可以把搜索条件中的各个部分组合起来。

*LIKE比较运算符

LIKE比较运算符也可以称为通配符。

LIKE运算符可以使用以下两个通配符“%”和“_”，

其中“%”代表零个或多个字符，“_”代表一个且只能是一个字符。

*转义字符

可以使用转义（escape）关键字来完成此任务，为此先创建一个临时的表，

之后再往该表中插入1行记录，其值包含包含通配符

执行语句：

（1）createtabledept_tempasselect*fromdept。

（2）insertintodept_tempvalues（88,‘IT_RESEARCH’,’BEIJING’）。

（3）select*fromdept_tempwherednamelike‘IT\_%’escape‘\’。

解释：

定义’\’为转义（escape）符，即在’\’之后的’_’字符已经不是通配符，而是它本来的含义，即下划线

*ISNULL使用

*Orderby子句

利用ORDERBY子句对查询的结果进行排序。

ASC（ascendingorder）为升序排序，也是系统默认的排序。

DESC（descendingorder）为降序排序。

语法：

SELECTselect_listFROMtablename[ORDERBYcolumn_name][ASC]or[DESC]

2.SQL函数

字符函数：

主要是操纵字符串

数字函数：

主要是操纵数字类型的数值

转换函数：

这些函数将数据从一种数据类型转换为另一种数据类型

日期函数：

处理日期和时间

正则表达式函数：

这些函数使用正则表达式搜索数据

*字符函数

（1）CONCAT（m,n）将m和n连接起来，并返回连接后的字符串

（2）INITCAP（n）将n中的第一个字母转换成大写

（3）INSTR（x,y,m,n）在x中查找字符串y出现的位置。

其中m,n是可选项，m代表从字符串x的第m个位置开始查找。

n代表字符串y出现次数

（4）LENGTH（n）求字符串n的长度

（5）LOWER（n）将字符串n中各个字符转换成小写

（6）LPAD（x,n,y）在字符串x的左边补齐空格，得到总长为n个字符的字符串。

其中y是可选项，这个参数用于指定左边补齐的字符串

（7）RPAD（x,n,y）类似于LPAD，只是在字符串x的右边补齐

（8）TRIM（[leading|trailing|both [trim_character] ]FORM x）

默认在x左边和右边截去指定字符，其中leading、trailing、both、trim_character都是可选项。

如果指定参数leading，则截去x左边的指定字符。

如果指定参数trailing，则截去x右边的指定字符。

如果指定参数both，则截去x左右两边的指定字符。

参数trim_character指定要截去的字符

（9）LTRIM（x,y）在x左边截去指定的字符。

指定字符可以通过参数y来表示。

如果没有指定要截去字符，则默认截去空格

（10）RTRIM（x,y）类似于LTRIM，只是在字符串x的右边截去指定字符

（11）NVL（x,y）如果x为NULL，则结果返回y；否则结果返回x

（12）NVL2（x,y,z）如果x不为NULL，则结果返回y；否则结果返回z

（13）NANVL（x,y）如果x的值是NAN（非数字），则结果返回y；否则结果返回x。

Oracle10g新增特性

（14）REPLACE（x,y,z）在x中将字符或是字符串y，替换为z

（15）UPPER（x）将字符串n中各个字符转换成大写

（16）SUBSTR（x,m,n）从字符x的m位置开始取得长度为n的一个子字符串，其中n可以省略不写，

那么取得子字符串就是从m开始到字符串的结尾

*数字函数

（1）ABS（X）求x的绝对值

（2）FLOOR向下取整（=

（3）ceil向上取整（>=x）

（4）ROUND（x,y）对x进行取整，y是可选参数，表示对第几位小数进行取整。

如果y为正数，则从x的小数点右边第y位开始取整；如果y为负数，则从x的小数点左边第y位开始取整

（5）TRUNC（x,y）对x进行截断，y是可选参数，表示对第几位小数进行截断。

如果y为正数，

则从x的小数点右边第y位开始截断；如果y为负数，则从x的小数点左边第y位开始截断

（6）MOD（x,y）求x除以y的余数

*日期函数

（1）ADD_MONTHS（x,y）月+y

（2）last_day（x）求出本月的最后一天

（3）MONTHS_BETWEEN（x,y）返回x和y之间的差值（y

（4）NEXT_DAY（x,day）返回从x开始，下一个day的日期

（5）ROUND（x[,y]）对x进行取整

（6）TRUNC（x[,y]）对x进行截断

*转换函数

（1）BIN_TO