Oracle学习笔记 下载本文

十三、 多表查询 ......................................................................................................................... 23

13.1按范式要求设计表结构 ................................................................................................. 23 13.2多表连接的种类 ............................................................................................................. 23 13.3交叉连接 ......................................................................................................................... 23 13.4内连接 ............................................................................................................................. 23 13.5外连接 ............................................................................................................................. 25 13.6非等值连接 ..................................................................................................................... 27 13.7表连接总结 ..................................................................................................................... 27 十四、 集合 ................................................................................................................................. 28

14.1表连接主要解决的问题 ................................................................................................. 28 14.2集合运算 ......................................................................................................................... 28 14.3集合运算符 ..................................................................................................................... 28 14.4子查询、连接、集合总结 ............................................................................................. 29 十五、 排名分页问题 ................................................................................................................. 30

15.1什么是rownum .............................................................................................................. 30 15.2 where rownum<=5的执行过程 ..................................................................................... 30 15.3 where rownum=5的执行过程 ....................................................................................... 30 十六、 约束constraint ................................................................................................................ 31

16.1约束的类型 ..................................................................................................................... 31 16.2 primary key:主键约束 ................................................................................................. 31 16.3 not null:非空约束 ........................................................................................................ 31 16.4 unique key:唯一建约束 ............................................................................................... 31 16.5 references foreign key:外键约束 ................................................................................. 32 16.6 check:检查约束 ........................................................................................................... 34 十七、 事务 ................................................................................................................................. 35

17.1 transaction ....................................................................................................................... 35 17.2定义 ................................................................................................................................. 35 17.3事务的特性:ACID ....................................................................................................... 35 17.4事务的隔离级别 ............................................................................................................. 35 17.5数据库开发的关键挑战 ................................................................................................. 35 17.6锁的概念 ......................................................................................................................... 36 17.7 Oracle的锁机制 ............................................................................................................. 36 17.8事务不提交的后果 ......................................................................................................... 36 17.9回滚事务rollback ........................................................................................................... 36 17.10保留点savepoint .......................................................................................................... 36 十八、 数据库对象:视图view ................................................................................................ 37

18.1带子查询的create table ................................................................................................. 37 18.2带子查询的insert ........................................................................................................... 37 18.3定义缺省值:default ...................................................................................................... 37 18.4 视图view ....................................................................................................................... 38 18.5视图的应用场景 ............................................................................................................. 38 18.6视图的分类 ..................................................................................................................... 39 18.7视图的维护 ..................................................................................................................... 39 十九、 数据库对象:索引index ............................................................................................... 41

3

19.1创建index ....................................................................................................................... 41 19.2扫描表的方式 ................................................................................................................. 41 19.3索引的结构 ..................................................................................................................... 41 19.4为什么要使用索引 ......................................................................................................... 42 19.5哪些列适合建索引 ......................................................................................................... 42 19.6索引的类型 ..................................................................................................................... 42 19.7哪些写法会导致索引用不了 ......................................................................................... 43 二十、 数据库对象:序列号sequence ...................................................................................... 44

20.1什么是sequence ............................................................................................................. 44 20.2创建sequence ................................................................................................................. 44 20.3缺省是nocycle(不循环) ........................................................................................... 44 20.4缺省cache 20 .................................................................................................................. 44 二十一、 其他注意事项 ............................................................................................................. 46

21.1删除表,删除列,删除列中的值 ................................................................................. 46 21.2多对多关系的实现 ......................................................................................................... 46 21.3一对多(两张表) ......................................................................................................... 46 21.4一对一 ............................................................................................................................. 46 21.5数据库对象 ..................................................................................................................... 46 12.6缺省(默认)总结: ..................................................................................................... 46

4

勿传网上!严禁谋利! Oracle学习笔记

常彦博

一、数据库介绍

1.1表是数据库中存储数据的基本单位 1.2数据库标准语言

结构化查询语言SQL:Structureed Query Language 1)数据定义语言DDL:Data Definition Language create table列表结构、alter table修改列、drop table删除列 2)数据操作语言DML:Data Manipulation Language insert增加一行,某些列插入值、update修改一行,这一行的某些列、delete删除一行,跟列无关

3)事务控制语言TCL:Transaction Conrtol Language commit确认,提交(入库)、rollback取消,回滚,撤销 4)数据查询语言DQL:Data Query Language select语句

5)数据控制语言DCL:Data Control Language 系统为多用户系统因此有隐私权限问题:grant 授权、revoke回收权限 1.3数据库(DB)

DATABASE 关系数据库使用关系或二维表存储信息。 关系型数据库管理系统(EDBMS):Relationship Database Management System是一套软件,用于在数据库中存储数据、维护数据、查询数据等。 1.4数据库种类

Oracle 10g(Oracle)、DB2(IBM)、SQL SERVER(MS)

1.5数据库中如何定义表

先画列即表头(列名,数据类型及长度,约束);数据类型有字符、数值number、日期date。

1.6 create database dbname的含义

创建数据库即创建可用空间,创建出一堆数据文件data file

1.7安装DBMS

职位:DBA 数据库管理员(DataBase Administrator)

1.8宏观上是数据-->database

开发流程:create tabale DML TCL -> DQL select

1.9远程登录:telnet IP地址

sql developer在linux系统--->连接--->database在solaris系统

1

勿传网上!严禁谋利! Oracle学习笔记

常彦博

1.10 TCP/IP通信协议

两台机器上的两个应用程序要通信,必须依赖网络,依赖TCP/IP通信协议。 IP:IP协议包中提供要连接机器的IP地址,用于标识机器。

TCP:TCP协议包中提供与机器上的哪个具体应用程序通信,通过端口号实现,oracle数据库服务缺省端口为1521,用于标识Oracle此数据库应用。

1.11数据库建连接必须提供以下信息

ip地址(确认机器)、port号(确认进程(程序)确认Oracle) SID:一个端口可以为多个oracle数据库提供监听,因此还需要提供具体的数据库名。(确认数据库里的哪个数据库) username、password:要想访问数据库,必须是该数据库上一个有效的用户。(确认身份) 1.12一台机器可跑几个数据库,主要受内存大小影响 1.13源表和结果集 源表:被查询的表 结果集:select语句的查询结果 1.14几个简单命令 show user:查看当前用户 desc 表名:查看表结构 drop table 表名 purge;删除表,Oracle中删除表不是真正的删除,而是占空间的移动到别的地方,因为为了不占空间,真正的删除需要用purge。 delete from 表名:删除表中所有值;若加上where 列名=value则删除某列中的值 1.15 tarena给jsd1304授权 connect tarena/tarena grant select on account to jsd1304; grant select on service to jsd1304; grant select on cost to jsd1304; jsd1304 select tarena的表 connect jsd1304/jsd1304 create synonym 创建同义词 create synonym account for tarena.account; create synonym service for tarena.service; create synonym cost for tarena.cost; 2