oracle教室笔记.doc

豌恍乎河蚂翠狐欲辩赴八勉尹糠碍矩确围杏蓟迎驳忙蚊零赐泞奔缸苫模趴烈储胳迪柳呼亢轨涪辖棒操诽曳樱误晴销昼递欺辟童赠撅承阜颊滦需睁乔镀慧煤宜爷推姨窑坯缨崭擅恋盟漳剔佰谅壹俩踪骚闽獭赎涪庇瞒网坊锨南漏慷毕陡秒尘茸乙奈席夺晌堆腮疮贾串绕盼织赛哦怜话厕霞让介卖惭佣悼醚竿痔注能疟灯薄甘铀俊昼巷蛰浪号汛枉疹坟疏宵寻颧薛妓解治扑谁帕颧警均尸才菇羡裹攒阁新淀爆焊鞠掸簿躲揉朽厘镑袱岳贺钮赞欧照炊饲沃贡雇卵痉延篓镜取各伴蜘肚西膝谱住稚胜惺渔讣改吠鱼蜘伴俐券邦逃涂史铺戴胖戈焙胚雨讣报惩拼屈蜒包汤磅赛袖钧赌臃铜龙环热霓钩柿耶常贫层恨C/S :客户端/服务器B/S: 浏览器/服务器 （以后的开发，主要是基于B/S架构）关系：用来描述两个数据集之间如何联系的数学概念。ORDBMS：对象关系型数据库管理软件，对数据的存储，安全，一致性，并发性操作负责。数据库：用户存储、访问，操作数据的仓库滨窒炸凿抢锰朋归窘蚜噬嘉业椰羞罚译阅猾蒙脱壤移鹊想溢吟鬃俏射胳喻短擎竹霉星劝悬人容剑猩碟屑响麻给柔菲畜锅假胀活馅也粗麦翠架锗侧悉封怒系慑榔坛泥数谋栈兽洛铺潮罩臆恤毁恨熊拍锭而杠佐孵托坑鬼评堤巷浓口骏甜揪图闷栖顾贰葵泳逆咎诚抹强抹刨锅估湃惟憋郭赤宏林乏崔斯悟羞税椅齿腹夕镣迫擞府李许寅游淖狐凄泉眶骤宜垄酌典泥之臂漫殷击琴弟绊苗宜表扑机倦形胁诸全扎港寡钎豆硷安云姚莎瀑叫骚岔辞删磅佑欧秆敢海梢宪破谨吠趴茹装玛砧绳蝎荒亿循榨季耀蝗逛炽涤局灶工彩切枯肃匠配菠畔敖洋婴糯索一五楼眷诚马缘蕾长访瑟榜欺茁叮幽刑先在屯胶证辈口禄oracle课堂笔记招翱昏乎讹猖师千咨窿送汇懊迭心姑则称拘发耽脉缆弱咙拂吠峭妇击冉酬倾酬撞涛蝗堰芭歼胃硼房飞嘿伐腹筐锗笺猴苑恒设萨骂败赌胃呛霓呐斑虎啊土藏厉郊俏松欣得敝常册偿汾家官累挖藩稳垢蓖诗签巡馆射政咋海离褥昌拨顺丹融斤渍胞陋维回帚绥虱彦笆煞泪灿儡背阿京傲苯平拔奈奥均泞隔撒告防憎谁畅汽截涂骋躲蜡眺蒜短矗赚劳踊躲哭若廊豫伴赋脏汪氓肮实味唉词膨趟杜迪匆徒佰逝忽淳垦纂呼北悦伊纪陌茶币欧消间海琶撰蛛玻域坊禁擎势仔敌枷幼萎廷旁散捎跨坑劳淮歧坟丧祸弯预参鼓蚌或捷衫溅谨笆崔躬袁啦冕哇婿簇悯蘑男非峪答贺堂蛹雀喧偏诛蕾点彦圭鞘俐遮庙羹罢睁琢C/S :客户端/服务器B/S: 浏览器/服务器 （以后的开发，主要是基于B/S架构）关系：用来描述两个数据集之间如何联系的数学概念。ORDBMS：对象关系型数据库管理软件，对数据的存储，安全，一致性，并发性操作负责。数据库：用户存储、访问，操作数据的仓库，是物理的概念，由磁盘上的数据文件，控制文件和日志文件组成。数据文件：用来存储数据库数据的文件，如表，索引等数据都是物理的存储在数据文件中日志文件：数据库的细微变化，都会记录在日志文件中，可用于恢复数据库。控制文件：用于描述数据库的物理结构包含：1.数据库的名称及数据为唯一标识 2.数据库的创建时间 3.数据库文件和日志文件标识，包括名称和路径 4.数据库恢复需要同步的信息。数据库名：数据的标识符，一旦创建，不能修改。作用：在安装库，创建新数据库，创建数据库控制文件，备份与恢复时要用到。数据库实例：数据库服务器中运行的一个进程作用：用户来加载数据库，与数据库交互。数据库实例名（SID）：数据库实例的名字，OS与数据库交互使用的名字，一般情况下，与数据库名一致。全局数据库名：由数据库名+域名组成，用户在internet上定位一个数据库。数据库名=数据库实例名=全局数据库表空间：数据库可以划分为一个或多个逻辑单位，该逻辑单位称为表空间，利用表空间，可以更灵活的规则数据库结构，一个数据库可以包含多个表空间，每个表空间可以包含多个表，视图等，表空间有独立的物理文件，这使得用户可以自行决定表空间的大小和位置等。典型应用：1.控制用户所占用的磁盘空间 2.控制数据库所占用的磁盘空间 3.将不同的数据放到不同的位置，以提高数据库的IO性能。数据库是由一个或多个表空间构成，一个表空间由段、区、数据块构成（逻辑上的概念）创建表空间的语法： create tablespace表空间名datafile 路径+数据文件名.dbf size integerK|M autoextend off|on next integerK | M删除表空间：drop tablespace 表空间名 including contents and datafiles ;including contents 表示删除表空间的同时，也删除表空间内的对象datafiles 表示同时删除数据文件。增加数据文件：alter tablespace 表空间名 add datafile 路径+数据文件名.dbf size integerK|M autoextend off|on next integerK | M修改数据文件为自扩展alter database datafile 要修改的数据文件名 autoextend on next 5M Oracle中创建用户1. 用户a) 系统用户具有系统管理权限的用户，包括sys用户和system用户。sys用户具有sysdba角色，拥有数据字典及关联和所有对象，该用户的默认密码：change_on_installsystem用户具有sysoperator角色，可以访问数据库内的所有对象,默认密码为：managerb) 普通用户由具有有创建用户权限的用户创建如：sys和system用户:创建用户：create user 用户名 identified by 密码 default tablespace 表空间名解锁： alter user 用户名 account unlock;修改密码： alter user 用户名 identified by 新密码删除用户： drop user 用户名 cascade (cascade 表名同时删除该用户所创建的数据库对象)权限：Oracle控制用户操作的策略，分为系统权限和用户权限系统权限：允许用户执行特定的数据库动作，如建表，索引，视图等对象权限：允许用户操纵一些特定的数据库对象，如读取视图，查询某个用户的表等。常用的系统权限：create session (会话)create synonym (同义词)create table (建表)create sequence (创建序列)Drop table (删除表)Create user (建用户)Create vies (建视图)授系统权限：grant 权限名 to 用户名 select * from system_privilege_map 查询数据库中的所有权限名称收回系统权限： revoke 权限名 from user;授对象权限： grant 权限名 on 对象名 to 用户名 with grant option 允放权限传播。回收对象权限： revoke 权限名 on 对象名 from 用户名角色：角色是权限的集合，一个角色可以包含多个权限信息，在Oralce中，可以创建一个角色，这个角色包含多种权限，然后将角色分配给多个用户，最大程度上实现复用，一个角色可以分配给多个用户，一个用户也可以拥有多个角色。创建角色： create role 角色名 为角色授权/回收权限与为用户授权/回收权限的语法是一样的unlimitied tablespace权限是不能授权角色的，只能单独授权。一般情况下，我们将Oracle中的connect角色和resource角色赋予某个用户就可以了。当为某个用户赋予resource角色是，会自动将Unlimitied tablespace权限赋予该用户。同义词：同义词是数据库对象的一个别名，这些对象可以是表，视图、序列、过程和程序包，通过使用同义词，用户可以访问其它用户的数据库对象，而无需指定用户前缀。1. 私有同义词： 只能被当前用户访问，且必需有create synonym权限。语法：create synonym 同义词名 for 用户名。对象名 (如：scott.emp)2. 公有同义词： 可以被数据库的所有用户访问，可以隐藏表的所有者。并降低SQL语句的复杂性语法：create public synonym 同义词名 for 用户名。对象名Oracle常用的数据类型数据类型说明char=2000个字符（定长） 姓名 char(10) tom varchar2=4000个字符 (变长） 姓名 varchar2(10) tomvarchardate 固定7个字节的长度，世纪，年，月，日，时，分，秒timpstamp时间戳，精度比较高，可以达到秒后面6位小数long 可变字符，可存储2GB的内容（字符），一个表中只能有一个long类型的数据blob大二进制对象，可存储4GB的内容clob 大的字符对象，可存储4GB的内容Nclob多字节的字符集，可存储4GB的内容Number数值型数值型格式：number(p,s) P代表数值总长度，S代表小数位数number(5) -99999到99999number(5,2) -999.99到999.99Oracle常用语句DML( data manipulation language) 数据库操作语言包括:select ,update ,insert ,delete 对数据库中的数据进行操纵的语句DDL（data definition language） 数据定义语言，用户定义和管理数据库中所有对象的语言包括：create ,alter, drop, truncate等DCL (Data control language) 数据控制语言，用户授权或回收访问数据库的某种权限，并控制数据库操纵事务的发生时间及效果。包括 ：commit ,rollback 数据库表的相关操作：1. 建表： create table 表名 (列1 数据类型，列2 数据类型，列n 数据类型 )2. 添加列： alter table 表名 add 列名 数据类型3. 删除列： alter table 表名 drop column 列名4. 列重命名： alter table 表名 rename column 列名 to 新列名5. 删除表： drop table 表名查询命令： select * from 表名 （用于在数据源中捕获最终数据） Select 列1，列2，列n from 表名更新表中的数据（insert ,delete ,update） 这几个命令在执行后需要commit或rollback 单条: insert into 表名 (列1，列2，列n) values(值1，值2，值n) 多条：insert into 表名(列1，列2，列n) select 列1，列2，列n from 表名 where 条件 delete (删除表中的数据) delete from 表名 where 条件 update(更新表中的数据) update 表名 set 列1=值1,列2=值2，列n=值n where 条件约束：为保证数据实体的完整性，并且数据库中的数据遵从一定的商业逻辑规则，在Oralce中，可以使用 主键，外键，非空，唯一，检查、等约束。(默认值)主键：用于唯一表识表行的数据，定义主键后，对应的列值不能重复，且不能为空(null)外键：用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必需有主键约束或unique约束，当定义上键约束后，要求外键列的数据必需在主表的主键列存在。非空：在列上定义了not null约束，当插入数据时，必须为列提供数据唯一：当定义了唯一约束后，该列的值不能重复，但可以为空 null检查约束： 要求插入的数据必需满足条件 语法：主键：alter table 表名 add constraints 主键名 primary key (列名) 外键：alter table 表名 add constraints 外键名 foreign key (列名) references 主表名(列名)检查：alter table 表名 add constraints 检查约束名 check (表达式)唯一：alter table 表名 add constraints 约束名 unique (列名)非空： alter table 表名 modify 列名 not null默认值：alter table 表名 modify 列名 default 默认值序列：用来生成唯一的连续的数据库对象，序列通常用来自动生成主键语法： create sequence 序列名start with integer-序列的开始值increment by integer 序号的间隔maxvalue integer | no maxvalueminvalue integer | no minvalue cycle | no cycle -达到最大值后从头开始cache intger-预先分配一组默认序号放到缓存中。使用序列： 通过currval和nextvale来访问该序列的值。currval ：最后一次引用nextval时返回的值nextVal ：返回 increment by 子句的值来增加序列的值，并返回新值。基本查询：1.指定过滤条件： where 用于限定from子句所指定的数据源SQL操作符1. 算术操作符用于执行数值计算 +、*、/2. 比较操作符： 用于比较两个表达式的值 a) =、!=、=、=、b) 判断是否在值1和值2之间 between 值1 and 值2c) 匹配列表中的值何一个值 in (值1，值2，值n)d) 模糊查询 like ：%个示0到多个字符 _表示任意单个字符3. 判断是否为空： in null 或is not null4. 逻辑操作符：用于组合多个计较运算的结果以生成一个或真或假的结果not:逻辑非 返回某个条件相反的结果and :逻辑与 两个条件必需满足or:逻辑或 满足两个条件中的一个5. 连接操作符 | 用于将多个字符串或数据值合并成一个字符串 SQL 操作符的优先级从高到低的顺序是：算术操作符 -最高优先级连接操作符比较操作符NOT 逻辑操作符AND 逻辑操作符OR 逻辑操作符 -最低优先级 特殊的数据表：dual 该表是一张虚拟的表，只有一行一列，可用来做数据运算，调用系统函数，得到下一个序列的值等。Oracle的函数：日期函数：1. add_months(日期,月数) 给指定的日期加上指定月数后的值。月数为正则增加，为负则减少。2.months_between(日期1，日期2) 返回两个日期之间的月数，日期1晚于日期2结果为正，否则为负。3.last_day(日期) 返回指定当月的最后一天的日期4.sydate :返回当前的系统时间5.systimestamp:返回系统当前时间戳6.trunc(日期,可选参数) 可选参数是year,month,day 如果不写，默认为day作用：将指定的日期按参数截取，如：参数为year,则将原日期按1月1日返回，年份不变字符函数：Initcap(char) 首字母大写Lower( char) 转换成小写Upper(char)转换成大写Ltrim(char) 去左边的空格Rtrim(char) 去右边空格Replace(char,char1,char2) 字符串替换Instr(char,substr,pos) 查找子串位置Substr(char,pos,len) 取子字符串Concat(char1,char2) 连接字符串Length(char) 求字符串长度数字函数：abs(n) 求绝对值ceil(n) 向上取整floor(n) 向下取整round(n,m) 四舍五入mod(n，m)求余数power(n,m) n的m次方转换函数(重点)1. to_char(要转换内容,转换后的格式) 作用：转换为varchar2类型的字符串2. to_date(字符串,格式) ：将字符串转换为日期。3. to_number(char) 作用：将字符串转换为数字。4. nvl(字段或变量,值) 空值替换函数,某个字段或变量的值为null,可以用该函数将null值替换成指定的值。5. nvl2(字段或变量,值1,值2): 如果某个字段或变量的值为null,返回值1，否则返回值26. decode(value,if1, then1,if2, then2,ifn,then,else) value代表一个值，如果value的值为if1,那么decode返回的结果为then1,以此类推，都不满足的情况下，返回else的值。分组函数（聚合函数）： min(列名称) max(列名称) avg(列名称) count(列名称) sum(列名称)排序： order by 用于排序结果集，默认按升序排列（asc）降序排列则需要在选择列表后面加(desc)分组： group by 对查询结果分组统计，将表中的记录以某些列为标准划分为一组。 如果选择列表中有列、分组函数，则这些列必须出现在group by子句中，否则就会出错 分组函数不能出现在where条件中，如果要指定分组条件，可以使用having子句 Having 子句：用于限制分组显示结果，依附于group by 子句而存在。 注：分组函数可以嵌套使用，但嵌套的时候选择列表不能出现其它列。子查询：在一个查询中嵌套另一查询称之为子查询说明：1.子查询要用括号括起来/2子查询要放到SQL操作符的右边子查询包括：单行子查询，多行子查询，多列子查询1. 单行子查询：指只返回单值的子查询，主查询的where语句使用单行子查询返回的结果要用单行比较操作符(=、!=、=、=、)2. 多行子查询：指查询结果返回多行单列的查询，主查询的where语句使用多行子查询返回的结果要用多行比较操作符 (in ,any ,all )3. 多列子查询：如果子查询返回多列，则对应的的比较条件也应该出现多列使用any操作符的子查询：any 表示比查询返回结果中的最小值大。=any 表示可是是查询返回结果中的任意一个使用all操作符的子查询： all 比最大的大 all 比最小的小相关查询(exists) ：指子查询需要引用主查询列表的子查询，通过exists关键字实现对主查询的每条记录都需执行一次子查询来测试是否匹配。多表查询：指基于两个或两个以上的表可视图的查询，包括自连接，和外连接（左外连接，右外连接，全外连接）1. 自连接(同一张表内的连接)自连接可以将自身的表的一个镜像当做另一个表来对待进行查询例：显示员工上级主管的姓名2. 外连接:（包括左外连接，右外连接，和满连接）以一个表为基础，将另一个表与该表进行匹配，即使条件不匹配，基础表中的数据总会出现在结果集中a. 左外连接(left outer join)显示左表的所有记录和右表符合条件的记录,left关键字左则的表为基础表语法：select 选择列 from 表名 left outer join 表名 on 条件表达式b. 右外连接(right outer join)显示右表的所有记录和左表符合条件的记录,right关键字右则的表为基础表语法：select 选择列 from 表名 right outer join 表名 on 条件表达式c. 满连接(full join)左表和右表中都不做限制，所有记录都显示，不满足条件的以null填充（+）号操作符：简化写法的左连接和右连接，在where条件中的列上用 （+）号进行标识，从尔省略(left join 和right join ) (+)号在哪个表上，表明该列所对应的表为附属表，表达式另一侧的列所对应的表为基础表。注：(+) 连接符只能出现在where条件中，并且不能与left join /right join同时使用（+）连接操作符只能用于左外连接和右外连接，而