甲骨文数据库oracle个人学习sql部分.docx

资源描述

甲骨文数据库oracle个人学习sql部分.docx

《甲骨文数据库oracle个人学习sql部分.docx》由会员分享，可在线阅读，更多相关《甲骨文数据库oracle个人学习sql部分.docx（21页珍藏版）》请在冰豆网上搜索。

甲骨文数据库oracle个人学习sql部分.docx

甲骨文数据库oracle个人学习sql部分

一、LESSON1

1.sql*plus&isql*plus

Oracle11g中已经去掉了isql*plus功能，即不用通过http:

//IP:

5560/isqlplus打开

2.连字运算符

||:

连字符两边的列被合并成一个单个的输出列。

selectlast_name||job_idASemployeesfromemployees;

3.文字字符串

selectlast_name||‘isa‘||job_idasemployeesfromemployees;

单引号包括起来。

4.去掉相同的行：

关键字：

distinct

selectdistinctdepartment_idfromemployees;

5.描述表

describetablename

二、Lesson2

1.where

字符串和日期，包括在单引号之中。

数字不包括在单引号之中。

所有的字符搜索是大小写敏感的。

日期格式为DD-MON-RR

别名不能用在WHERE中。

2.between

必须先指明下限，如wheredepartment_idbetween2500and3500;

3.in

如果IN条件中的成员是字符或日期，必须在单引号之中。

4.LIKE

%表示0个或多个字符

_表示一个字符。

搜索%或_字符，要用转义符”\”，如\%,\_

5.ISNULL

不能用=null来表示

6.优先规则

算术运算>连字操作>比较操作>IS（NOT）NULL,LIKE,（NOT）IN>（NOT）BETWEEN>NOT>AND>OR

7.ORDERBY

可以依别名来排序

默认是升序ASC，也可以是降序DESC

可以多列排序,逗号分隔

三、LESSON3

1.SQL单行函数

仅对单个行进行去处，并且每行返回一个结果。

包括：

字符、数字、日期、转换

接收一个或多个参数，并且对查询的每个返回行返回一个值，值可以是：

用户提供的常数、变量值、列名、表达式。

语法：

function_name[arg1,arg2]

2.字符函数

UPPER\LOWER\INITCAP

CONCAT\SUBSTR\INSTR\LPAD\RPAD\TRIM

3.数字函数

round/trunc/mod

4.DUAL虚拟表

其所有者是SYS用户，并且可以被所有用户访问，它只包含一列。

通常用于SELECT子句语法的完整。

四、lesson4

1.从多表中获得数据

1）笛卡尔乘积：

为了避免笛卡尔乘积的形成，在WHERE子句中应当总是包含正确的连接条件

2）为了连接N个表在一起，最少需要N-1个连接条件。

2.等值连接（简单连接或内连接）

3.如果列名在两个表之间不相同，就不需要限定列，但是，使用表前缀可以改善性能。

4.对于表，可以使用别名。

表别名有助于保持SQL代码较小，因此使用的存储器也少。

5.用外连接返回不直接匹配的记录。

如果一个行不满足连接条件，该行将不出现在查询结果中。

可以使用一个外连接查看那些不满足连接条件的行。

外连接运算符号是（+）

（+）放在连接的缺少信息的一侧。

6.自然连接（naturaljoin）

从两个表中选择在所有的匹配列中有相等值的行。

如果列有相同名字，但数据类型不同，则会返回一个错误。

自然连接相当于创建“等值连接”

7.USING子句

USING子句可以被用来指定哪些将被用于一个等值连接的列中的唯一列。

在USING子句中引用的列不应用在SQL语句的任何地方用表名或表别名限制（前缀）

8.ON子句

用于自然连接的连接条件

9.三向连接

连接被从左到右执行。

select1.a,2,b,3,cfrom1

join2

on1.a=2.a[using（a）]

join3

on2.b=3.b[using（b）]

10.内与外连接

连接两个表，仅返回匹配的行的连接，称为内连接

在两个表之间的连接，返回内连接的结果，同时还返回不匹配行的左（或右）表的连接，称为左（或右）连接

在两个表之间的连接，返回内连接的结果，同时还返回左和右连接，称为全连接

左连接：

leftouterjoinbon（a.1=b.1）相当于：

wherea.1=b.1（+）

右连接：

rightouterjoinbon（a.1=b.1）相当于：

wherea.1（+）=b.1

全连接：

fullouterjoinbon（a.1=b.1）相当于：

wherea.1=b.1（+）union{select..from..}wherea.1（+）=b.1

五、Lesson5

1.groupby语句

把表中的行划分为组，再可以用组函数返回每一组的摘要信息。

在GROUPBY子句中不能使用列别名。

在GROUPBY列表中的字段的升序排序。

groupby中的列不必在SELECT列表中。

在同一个SELECT语句中使用单独的列和组函数的混合时，必须包括一个GROUPBY子句来指定单独的列。

不能用WHERE子句来约束分组，但可以用HAVING子句来约束分组。

建议先用GROUPBY子句，再调用HAVING子句，更合逻辑。

六、LESSON6子查询

1.子查询的原则

1）子查询放在圆括号中

2）将子查询放在比较条件的右边。

3）在子查询中不需要ORDERBY子句，除非正在执行TOP-N分析

4）先执行内查询（子查询），再执行外查询，将内查询作为外查询的条件。

2.单行子查询

1）仅返回一行。

2）使用单行比较符（＝、>,<,>=,<=,<>）

3）避免子查询错误：

在单行子查询中返回了多行结果。

如

SELECTemployee_id,last_nameFROMemployeesWHEREsalary=（SELECTMIN（salary）FROMemployeesGROUPBYdepartment_id）;

3.带子查询的HAVING子句

1）首先执行子查询，返回结果到主查询的HAVING子句中。

4.多行子查询。

用IN,ANY,ALL多行比较符。

any大于最小值

all大于最大值

=any相当于in

如果子查询返回的结果可能返回有空值，则不能使用NOTIN比较符。

其相当于<>all。

比较一个空值的所有条件结果还是空。

七、lesson7

八、lesson8操纵数据

1.insertintotablename（column1,column2..）values（value1,value2..）

ORACLE服务器自动强制所有数据类型、数据范围和数据完整性约束。

在插入新行时，任何没有被明确列出的字段都将获得一个空值。

在SQL语句中使用&替换变量提示用户输入值。

运行脚本文件，可以使用&变量

2.updatetablenamesetcolumn1=value1wherecondition

如果省略了where语句，会更新所有行。

可以在set中使用子查询，将子查询得到的结果返回给setvalue中。

3.delete&truncate

1）deletefromtablenamewherecondition

如果没有where语句，将删除表中所有行。

2）delete语句删除后，只有表的数据结构被保留，清空表的另一个更有效的方法是用truncate。

truncate快速删除表中的所有行。

不产生回退信息。

3）注意删除时完整性约束。

不能删除包括主键的行，该主键被用来另一个表的外键。

如果完整性约束包含了ondeletecascade选项，那么可以删除行，并且所有相关的子表记录都被删除。

4.merge语句

1）可以有条件地更新或插入行到表中，这样就避免多重UPDATE语句。

是执行更新操作还是执行插入操作，取决于ON子句中的条件。

2）同一个MERGE语句中，不能多次更新目的表中相同的行。

3）mergeintotable_name

using（table|view|sub_query）

on（joincondition）

whenmatchedthen

updateset

col1=col_val1,col2=col_val2…

whennotmatchedthen

insert（column_list）

values（column_values）;

4）数据库事务处理

A.DML数据库操纵语言：

由许多DML语句组成。

ORACLE服务器将他们视为一个单个整体或一个逻辑工作单元。

对数据进行永久的改变。

B.DDL：

数据定义语言，由单个的DDL语句组成

DDL不能回滚

C.DCL：

数据控制语言，由单个DCL语句组成。

D.下列情况事务结束：

commit或rollback语句发布

ddl或dcl语句发布

E.显示控制事务语句

commit

savepointname

rollback

rollbacktosavepointname

F.隐式事务处理

如果automaticcommit命令被设置为ON，每个单个的DML语句在执行后被立即提交，不能回滚所做的改变。

如果automatic命令被设置为off，commit仍然能够被显式地发布。

当一个事务被系统故障中断时，整个事务被自动回滚。

返回表到他们上一次提交时的状态。

G.commit或rollback提交之前数据的状态

数据操纵操作首先影响数据库缓冲区，因此，数据以前的状态可以被恢复。

当前用户可以查询表观察到数据操纵操作的结果。

其他用户不能观察到当前用户所做的数据操纵操作的结果。

Oracle服务器用读一致性来确保每个用户看到的数据和上次提交时相同。

受影响的行被锁定；其他用户不能改变受影响的行中的数据。

H.commit提交之后数据的状态

数据的改变被写到数据库中。

数据以前状态永久地丢失。

所有用户都可以观察到事务的结果。

受影响的行上的所被释放；其他用户现在可以对行进行新的数据改变。

所有保存点被释放。

I.rollback提交之后数据的状态

数据的改变被还原

数据以前的状态被恢复

受影响的行上的锁被释放

如果一个语句的执行错误被发现，一个事务的一部分可以用隐式的回退丢弃。

其他已经被执行的DML语句不能被隐式丢弃，可以由用户显示地提交或回滚。

5）读一致性

其目的是确保每个用户看到的数据和他最后一次提交，并且在一个DML操作开始之前的数据一样。

在对数据库进行一个插入、更新或删除时，ORACLE服务器在数据改变之前获得相关数据的拷贝，并且将这些数据写到一个回退段（undosegment），除了修改者看到的是自己修改后的结果，其他用户都看到的是这个回退段的快照。

只有要修改者提交这个操作后，修改才对所有人可见。

6）隐式锁定

A.两种锁模式：

独占锁

共享锁

B.高级数据并发操作：

DML:

表共享，行独占

查询：

不需要锁

DDL:

保护对象定义

C.锁保持址到commit或rollback

九、lesson9表操作

1.oracle数据库中的表

1）用户表

2）数据字典

由ORACL服务器创建和维护的表的集合。

包括数据库信息

四种常见的数据字典视图：

A.USER_,（user_tables,user_objects,user_catalog）

a）user_objects当前用户所拥有的所有对象，不包含建立的PUBLIC对象。

b）user_tables你自己的表，所拥有的表。

B.ALL_,

C.DBA_,

D.V$（动态执行的视图，包含数据库服务器的性能、存储器和锁的信息。

2.用子查询创建表

1）createtabletable_nameas

select…fromanother_table_name[where…]

2）可以用下列语句创建一个结构与目标表一样的表，但没有任何数据。

createtabletable_nameas

select…fromanother_table_name[where1=2]（where语句条件判断为假）

3.修改表的结构

altertabletable_namedropcolumn删除列

altertabletable_namesetunusedcolumn标记一个或多个未使用的列，但未释放占用的磁盘空间。

altertabletable_namedropunusedcolumn删除未使用的列，并收回额外的磁盘空间。

4.删除表

droptabletable_name

5.改变一个对象的名字

renameold_nametonew_name

6.截断表

table_name

truncate语句是数据定义语句，并且不产生回滚信息。

不能截断一个引用完整性约束的父表。

deletetable也可以从表中删除所有的行，但它不能释放存储空间。

truncate可以释放存储空间，且更快些。

7.添加注释到表中

commentontabletable_nameIS‘text’

commentoncolumntable_name.column_nameIS‘TEXT’

最多一个对象加一个2K字节的注释。

注释存储在数据字典中。

all_col_comments

user_col_comments

all_tab_comments

user_tab_comments

一十、lesson10约束

1.数据一致性约束

1）notnull。

只能在列级，不能在表级。

2）unique。

除非对相同的列定义的notnull约束，UNIQUE约束允许输入空值

既可以在表级，也可以在列级定义UNIQUE约束

oracle服务器在唯一键列或组合列上隐式地创建一个唯一索引强制UNIQUE约束。

3）primarykey

是表中的对行唯一标识的一个列或者列组合。

强制唯一。

不包含空值

既可以在表级，也可以在列级定义。

一个表可以有多个unique约束，只能有一个primarykey。

4）foreignkey

不能创建不存在的主键值的外键。

constraintconstraint_nameforeignkey（column_name）referencesanother_table_name（column_name）