Oracle数据库学习笔记Word文件下载.docx
《Oracle数据库学习笔记Word文件下载.docx》由会员分享,可在线阅读,更多相关《Oracle数据库学习笔记Word文件下载.docx(19页珍藏版)》请在冰豆网上搜索。
逻辑运算符:
not
and
or
使用orderby子句进行排序:
升序:
asc默认的,可以不写
降序:
desc
orderby子句位于select语句最后
替代变量:
使用&
或&
&
临时存储值到一个变量中。
使用define命令:
1、用于创建变量,并赋值;
2、undefine删除define定义的变量;
使用verify命令:
用于是否显示替代变量前后值得一个开关。
启用:
setverifyon
关闭:
setverifyoff
显示:
showverify
第三章使用单行函数定制输出
Sql函数:
通过向SQL函数中输入变量,然后经过SQL函数处理,得出输出常量
两种类型:
1、单行函数(数字函数、字符函数、日期函数)
2、多行函数
字符函数:
英文字母大小写转换:
lower
upper
initcap
字符处理:
concat--------------||
substr
length
instr
lpad|rpad
trim
replace
数字函数:
round
trunc
mod
处理日期:
RR日期格式,可以表示前一个世纪、当前世纪、下一个世纪的时间。
注意RR和YY的区别。
日期函数:
Months_between
Add_months
Next_day
Last_day
第4章使用转换函数和条件表达式
数据类型转换:
显式(to_charto_numberto_date)
隐式(字符<
-数字字符<
-日期)
to_char函数:
日期转换为字符
TO_CHAR(date,'
format_model'
)
格式串的特点:
1、必须放在单引号中
2、大小写敏感
3、可以包含任何有效的日期元素
4、可以增加fm,去除无效的空格和零
5、和日期参数用逗号隔开
数字转换为字符
TO_CHAR(number,'
to_number和to_date
TO_NUMBER(char[,'
])
TO_DATE(char[,'
嵌套函数:
1、单行函数可以嵌套到任意层
2、嵌套函数计算的顺序是从内到外
Oracle中常规函数:
nvl
1、参数类型可以是字符、日期、数字
2、两个参数的数据类型必须匹配
nvl2
nullif
coalesce
条件表达式:
(条件判断使用的,类似于if……then…..else)
case:
CASEexpr
WHENcomparison_expr1THENreturn_expr1
[WHENcomparison_expr2THENreturn_expr2
WHENcomparison_exprnTHENreturn_exprn
ELSEelse_expr]
END
Decode:
第5章使用组函数聚集数据
常见的组函数:
AVG求平均值
COUNT求行数
MAX求最大值
MIN求最小值
SUM求和
STDDEV求标准差
VARIANCE求方差
组函数语法:
SELECTgroup_function(column),...
[WHEREcondition]
[ORDERBYcolumn];
MIN和MAX组函数对数字、字符、日期都可以使用。
COUNT函数:
COUNT(*)可以返回某个表的行数。
也可以使用COUNT
(1)
1、COUNT(expr)将返回expr为非空值的行的数量
2、COUNT(distinctexpr)将返回expr不同非空值得数量
对行进行分组:
使用groupby和having子句
可以使用groupby子句将表中的行分成较小的组。
SELECTcolumn,group_function(column)
[GROUPBYgroup_by_expression]
1、SELECT列表中未出现在组函数中的所有列都必须包含在
GROUPBY子句中。
2、GROUPBY列不一定要出现在SELECT列表中
3、不能使用where子句限定组,只能使用having子句去限定组
Having子句:
SELECTcolumn,group_function
[HAVINGgroup_condition]
嵌套组函数:
如:
求所有部门最大的平均工资:
SQL>
SELECTMAX(AVG(salary))
2FROMemployees
3GROUPBYdepartment_id;
MAX(AVG(SALARY))
----------------
19333.3333
第6章使用联接显示多个表中的数据
联接的类型:
1、自然联接
Naturaljoin子句
Using子句
On子句
2、外连接
左连接
又连接
全连接
3、交叉连接
连接的语法:
SELECTtable1.column,table2.column
FROMtable1
[NATURALJOINtable2]|
[JOINtable2USING(column_name)]|
[JOINtable2
ON(table1.column_name=table2.column_name)]|
[LEFT|RIGHT|FULLOUTERJOINtable2
[CROSSJOINtable2];
限定不确定的列名:
1、使用表前缀限定在多个表中都存在的列名;
2、使用表前缀可以提高查询的性能;
3、表前缀可以使用表的别名;
4、使用列别名可以区分不同表中相同名称的列;
自然连接:
使用AND子句或WHERE子句可应用附加条件
自连接:
selecte.LAST_NAMEemp_name,e.MANAGER_ID,m.LAST_NAMEmgr_name
2fromemployeese,employeesm
3wheree.MANAGER_ID=m.EMPLOYEE_ID
4ande.department_id=20;
EMP_NAMEMANAGER_IDMGR_NAME
------------------------------------------------------------
Hartstein100King
Fay201Hartstein
非等值连接:
selectename,sal,grade
2fromemp,salgrade
3whereemp.salbetweensalgrade.LOSALandsalgrade.HISAL;
外连接:
左连接:
右连接:
全连接:
笛卡尔积:
(交叉连接)
注:
如果要避免生成笛卡尔积,请始终包括有效的联接条件
第7章使用子查询来解决查询
子查询的语法:
SELECTselect_list
WHEREexproperator
(SELECTselect_list
FROMtable);
1、先执行子查询,再执行主查询;
2、主查询使用子查询的结果;
使用子查询的准则:
1、子查询必须要放在括号中;
2、一般子查询放在比较操作符的右侧;
3、单行子查询使用单行比较操作符,多行子查询使用多行比较操作符;
子查询类型:
1、单行子查询;
2、多行子查询;
单行比较操作符:
多行比较操作符:
In
Any
All
any和all需要和单行比较操作符一起使用
1、<
any小于列表中的最大值
2、>
any大于列表中的最小值
3、<
all小于列表中的最小值
4、>
all大于列表中的最大值
使用exists运算符:
可以用in替代
子查询中的空值:
如果notin(子查询)中的子查询包含空值,那么主查询查询不出任何行。
第8章使用集合运算符
集合运算符:
Unionall:
并集(包含重复的行)
Union:
并集(去除重复的行)
Intersect:
交集
Minus:
差集
准则:
1、每个子集的select语句列表达式的数量必须一致;
2、每个子集的select语句列表达式的类型必须一致;
3、可以使用括号改变执行的顺序;
4、Orderby子集只能出现在集合运算语句的末尾;
5、除unionall外,重复的行会被去除;
6、第一个查询子集的列名将显示在结果中;
7、除unionall外,查询出的结果按第一列升序排列;
匹配select语句:
主要是为了确保所有子集查询的列个数和列类型保持一致,否则不能进行集合运算。
集合运算中使用orderby子句:
1、orderby子句只能在集合运算的末尾出现一次;
2、不能有多个orderby子句;
3、Orderby子句只识别第一个查询中的列名或列别名;
4、除unionall以外,默认是按第一个查询中的第一列进行升序排列;
第9章处理数据
DML语句:
对表进行增、删、改的操作。
事务:
由构成一个逻辑操作单元的DML语句组成。
Insert语句:
INSERTINTOtable[(column[,column...])]
VALUES(value[,value...]);
该语法一次只能插入一行数据。
插入带有空值的行:
可以采用两种方式:
1、在insertinto子句中定义只插入值得列名;
2、在values子句中输入null值,确保列数和表一致;
插入特殊值:
1、Sysdate返回数据库服务器当前的日期和时间的内部函数。
2、或者使用转换函数
3、使用替代变量
从其它表中复制行数据:
使用带有select子查询的insert语句进行复制。
insertinto<
select…..from<
other_tab_name>
where…;
1、没有values关键字;
2、Insert子句中的列数必须和select子句中的列数匹配,数据类型也需要一致;
3、select子查询的行将都被插入到insert子句中的表里面;
更改表中的数据:
(update)
UPDATEtable
SETcolumn=value[,column=value,...]
[WHEREcondition];
可以使用子查询更新表中的列:
子查询是一个单行子查询。
从表中删除已经存在的行数据:
(delete和truncate)
(delete)
DELETE[FROM]table
(truncate)
TRUNCATETABLEtable_name;
1、truncate是删除表中所有的行,并保留表的结构;
2、truncate是DDL,而不是一个DML语句;
数据库事务处理:
事务处理由以下语句之一组成:
1、DML语句;
insert、update、delete、merge
2、DDL语句;
create、drop、alter、truncate
3、DCL语句;
grant、revoke
事务的开始和结束:
开始:
事务从执行第一条DML语句开始。
结束:
1、commit和rollback
2、执行DDL或DCL语句,隐式提交
3、用户退出SQLPLUS,自动提交
4、系统崩溃,自动回滚
Commit和rollback语句的优点:
1、确保数据的一致性;
2、在使改变变为永久改变之前预览数据;
3、按逻辑关系对相关操作进行分组;
显示的事务控制:
Commit
Rollback
Savepoint:
保留一个还原点,从而可以使事务回退到指定的还原点,而不是全部回退。
回退到指定的标记:
rollbacktosavepoint<
point_name>
;
执行commit和rollback之前的数据状态:
1、可以将数据还原到原来的状态;
2、当前用户可以查到DML操作的结果;
3、其它用户不能查看到DML操作的结果;
4、手影响的行数据被锁定,其它会话不能修改;
执行commit之后的数据状态:
1、数据更改永久保存到数据库中;
2、已改写以前的数据状态;
3、所有会话都能看到DML操作的结果;
4、受影响行的锁被释放,其它会话可以对改行进行DML操作;
5、所有savepoint保存点都被清除;
执行rollback之后的数据状态:
1、数据更改被取消;
2、数据回退到事务开始以前的状态;
3、受影响行的锁也会被释放;
读一致性:
1、读一致性可以确保用户所看到的数据始终是一致的。
2、一个用户进行更改不会与另一个用户更改相冲突;
3、读写不冲突,写写是冲突的;
Select语句中的forupdate操作
1、可以手动锁定表中查询出来的数据行;
2、仅在发出rollback和commit才释放锁;
3、如果select出来的某些行已经被其它会话锁定,那么select…forupdate需要等待其它会话释放锁;
可以对多个表连接的select语句使用forupdate:
1、它会锁定连接的所有表的所有行;
2、可以使用forupdateof<
column>
来限定修改的具体列,这是该会话只是锁定了存在该列的表中所有的数据。
第10章使用DDL语句创建和管理表
数据库对象:
1、表table
2、视图view
3、序列sequence
4、索引index
5、同义词synonym
表名和列名的命名规则:
1、必须以字母开头;
2、长度是1---30个字符;
3、只能包含A-Z,a-z,0-9,_,$,#;
4、不能和本用户下的其它对象重名;
5、不能使Oracle数据库的保留关键字;
创建表:
createtable
用户必须具有以下权限:
1、createtable
2、拥有存储权限
CREATETABLE[schema.]table
(columndatatype[DEFAULTexpr][,...]);
引用另一个用户下的表:
Select*fromschema.<
1、在本用户方案中没有其它用户创建的表;
2、应该使用表所有者的名称作为前缀来查询;
Default选项:
指定表中列的默认值;
1、文字、表达式、函数都是合法的;
2、其它列名或伪列是非法的;
3、默认值的数据类型必须和列的定义一致;
Desc<
字段的数据类型:
Varchar2(size)
Char(size)
Number(p,s)
date
long---2G
clob—4G
raw、longraw----二进制的数据,2G
blob----二进制数据4G
bfile----存储外部文件中的二进制数据
rowed---表中行数据唯一的物理地址,16进制编码
日期时间数据类型:
timestamp
intervalyeartomonth
intervaldaytosecond
约束条件:
常见的约束类型:
Notnull
unique
primarykey
foreignkey
check
1、约束可以手工命名,也可以由oracle自动命名,自动命名的格式SYS_Cnnnnnn
2、约束可以在创建表时或创建表后去定义
3、如果约束在创建表时定义,那么可以在列级别或表级别去定义
4、约束定义以后,我们可以在数据库数据字典中查询约束
定义约束语法:
(columndatatype[DEFAULTexpr]
[column_constraint],----------列级别定义
...
[table_constraint][,...]);
---------------表级别定义
Notnull约束:
确保列不为空值。
Unique约束:
唯一键,确保列值不重复
Primarykey约束:
主键,确保主键值唯一、且不为空
Foreignkey约束:
外键,键值是引用的另一个表的主键值
ONDELETECASCADE:
删除父表中的行时还删除
子表中的相关行
ONDELETESETNULL:
将相关外键值转换为空值
Check约束:
定义列每行数据值都必须满足的一个条件。
使用子查询创建表:
Createtable<
As
Select子查询;
Altertable语句:
1、添加新列
2、修改列
3、定义列的默认值
4、删除列
5、重命名列
6、将表修改为只读
删除表:
Droptable<
[purge];
第11章创建其它方案对象
视图的优点:
简单视图和复杂视图
创建视图的语法:
CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview
[(alias[,alias]...)]
ASsubquery
[WITHCHECKOPTION[CONSTRAINTconstraint]]
[WITHREADONLY[CONSTRAINTconstraint]];
视图DML操作的规则:
1、删除行的规则
2、更新行的规则
3、插入行的规则
使用withcheckoption子句:
定义了一个约束,视图做DML操作时,不能违反select子查询中where条件的定义。
Withreadonly左右:
使视图变为只读,不能对视图做任何的DML操作。
删除视图:
Dropview<
view_name>
删除视图,不会删除视图基表上的数据。
序列:
sequence
创建的语法:
CREATESEQUENCEsequence
[INCREMENTBYn]
[STARTWITHn]
[{MAXVALUEn|NOMAXVALUE}]
[{MINVALUEn|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHEn|NOCACHE}];
Nextval和currval两个伪列,分别表示序列的下一个值和当前值。
修改序列:
Altersequence
更改增量值、最大值、最小值、循环选项或高速缓存选项。
删除序列:
Dropsequence
索引index:
1、是一个对象;
2、通过索引加快select查询数据的速度;
3、可以减少磁盘的I/O;
4、与建立的索引表无关;
5、索引是由oracle自动使用和维护的;
创建的方式:
1、自动创建;
(primarykey和uniquekey)
2、手动创建;
手动创建索引的语法:
CREATE[UNIQUE][BITMAP]INDEXindex
ONtable(column[,column]...);
删除索引:
Dropindex<
index_name>
删除索引,需要时索引的拥有者或者具有dropanyindex权限。
同义词synonmy:
1、方便的引用其他用户的对象;
2、给名称冗长的对象一个别名;
CREATE[PUBLIC]SYNONYMsynonym
FORobject;
删除:
Drop[public]synonym<
synonym_name>