Oracle数据库

1、.Oracle 数据库中常用的数据类型varchar2( 长度 )可变长字符串char( 长度 ) 定长number() 表示整数或者浮点数 number(8)number(8,2)clog 字符的大对象blog 二进制的大对象二.数据库查询1) SELECT 语句从表中提取查询数据.语法为SELECTDISTINCTcolu mn 1,colum n2,FROMtable nameWHEREco nditio nsGROUPBYco nditionsORDERBYexpressionsASC/DESC;FROM 子句用于指定从哪一个表或视图中检是一个闭区间。说明：SELECT子句用于指定检索

2、数据库的中哪些列，索数据。2) WHERE 子句。WHERE 子句用来选择符合条件的的记录 .like'.' 通配查询 _,%between.and., 表示结果在这之间， betweenand!= , <> , A=，这三个都可以表示不等于。in(va1,val2,.) 判断结果是否在这个集合中存在。like'.' 字符串通配查询，'%' 表示 0 或多个字符，'_' 表示一个字符。.and. 表示只有两个条件同时满足.or.表示条件只要满足其中之一就可以al l.是要求都满足条件。not ，则是可以与以上的条件产生

3、反效果。.isnull 使用来判断值是不是空。3) ORDERBY 子句ORDERBY 子句使得 SQL 在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由 ORDERBY 子句指定的表达式的值确定。ASC （默认，升序）DESC （降序）orderby 目标列名（别名）排序顺序（不写排序顺序，会默认为升序排序）例： selectfirst_namefroms_emporderbyfirst_name; selectfirst_namefroms_emporderbyfirst_namedesc;三 .SQL 常用的命令分类及例子数据定义语言： create （创建）、 alter （更

4、改）和 drop （删除）命令。数据操纵语言： insert （插入）、 select （选择） 、 delete （删除）和 update （更新）命令。事务控制语言： commit （提交）、 savepoint （保存点）和 rollback （回滚）命令。 数据控制语言： grant （授予）和 revoke （回收）。1. 数据定义语言举例：SQL>createtablemyTab（nonumber（4）,namevarchar2（20）; 创建一个名为 myTab 的表，包含两 列 no 和 name ；SQL>altertablemyTabmodify（namevar

5、char2（25）; 修改 myTab 中的 name 列，使此列能容纳 25 个字符；SQL>altertablemyTabadd（tel_novarchar2（20）; 给表 myTab 增加一列 tel_no ； SQL>altertablemyTabdropcolumntel_no;删除表 myTab 的 tel_no 列 ;SQL>droptablemyTab; 删除表 myTab ；SQL>truncatetablemyTab; 删除表 myT ab 中的所有行（截断表）,注意 :不可以回滚。2. 数据操纵语言举例：SQL>insertintomyT

6、abvalues（ 001''J,ohn ' ）;向表 myTab 中插入一行数据；SQL>selectdistinctsalary “薪水” froms_empwheresalary>1500orderbysaldesc;选择表中 salary 大于 1500 的数据，以别名“薪水”显示并按照 salary 的降序进行排列输出； SQL>createtableempaasselectempno,ename,job,salfromemp;从 emp 表中选择“ empno,ename,job,sal ”四列的数据建立新表 empa;SQL>cre

7、atetableempaasselect*fromempwhere1=2; 使用一个假条件根据现有表 emp 创建一个只包含结构的空表 empa ；SQL>deletefromempawheresal<1500;删除表 empa 中 sal 小于 1500 的行；SQL>updateempasetsal=1500wheresal<1500; 更新，将表 empa 中 sal 小于 1500 的行的 sal 值全部改为 1500 。3. 事务控制语言举例：SQL>commit; 用于提交并结束事务处理；SQL>savepointmark1; 保存点类似于标记，

8、用来标记事务中可以应用回滚的点； SQL>rollbacktosavepointmark1; 回滚到保存点 mark1 。四 .Oracle 数据库函数注意:dual表（虚表）是专门用于函数测试和运算的.1. 字符函数 字符是大小写敏感的 转小写 lower（ 字段名 ） 转大写 upper（ 字段名 ） 首字母大写 initcap（ 字段名 ） 字符串拼接 concat（ 字段 1, 字段 2）截取子串 substr（ 字段名 ,起始位置，取字符个数 ）例:selectfirst_ name,substr（first_ name,2,2）subfroms_emp;（从名字的第二个字符开

9、始取两个字符）selectfirst_name,substr（first_name,-2,2）subfroms_emp;（ 从名字的倒数第二个字符开始取两个字符）2. 数值函数四舍五入函数 round（ 数据 ,保留到小数点后几位 ）1 表示保留到小数点后一位， -1 表示保留到小数点前一位。例： selectround（15.36,1）fromdual;截取数值函数 trunc（ 数据，保留到小数点后几位 ） 例： selecttrunc(123.456,1)fromdual;截取到小数点后一位 ,注意 :与 round 函数不同 ,不会四舍五入。3. 日期函数缺省日期格式，日 -月-年 d

10、d-mon-rr修改当前会话的日期格式，会按照指定的格式输出日期altersessionsetnls_date_format='yyyymmddhh24:mi:ss'返回当前日期 sysdate例： selectsysdatefromdual;4. 不同数据类型间转换函数将日期转成字符 tochar（date,' 日期格式 '）日期格式要用有效格式，格式大小写敏感 'yyyymmddhh24:mi:ss'（ 标准日期格式 ）,'year'（ 年的全 拼 ）,'month'（ 月的全拼 ），'day'

11、（ 星期的全拼 ）， 'ddspth'（ 日期的全拼 ） 例： selectto_char（sysdate,'yyyymmddhh24:mi:ss'）fromdual; selectto_char（sysdate,'yearmonthdayddspth'）fromdual;将字符串转成日期 to_date（'.',' 日期格式 '）例： selectto_char（to_date（'20061103','yyyymmdd'）,'dd-month-yy'）fromdua

12、l ；五. 表连接（关联查询）等值连接selecttable1.column1 ， table2.column2fromtable1t1 ， table2t2wheret1.column3=t2.column4;表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用表名字段名或表别名 .字段名（列名） 。当表的字段名是唯一时，可以不用加上表名或表的别名。 注意：当为表起了别名，就不能再使用表名.字段名了。例如： selecte.first_name| '' |e.last_namename,dept_name froms_empe,s_de

13、ptd wheree.dept_id=d.id;非等值连接select 表别名 1.字段名 1， 表别名 2.字段名 2,.from 表 1 表别名 1，表 2 表别名 2where 表别名 1.字段名 3表别名 2. 字段名 4可以使比较运算符，也可以使其他的除了 '=' 的运算符例： selectfirst_name,salaryfroms_emp wheresalarybetween1000and2000;自连接 把一个表的两个字段关系转换成两个表字段之间的关系 .select 表别名 1.字段名 1， 表别名 2.字段名 2,.from 表 1 表别名 1，表 1 表别

14、名 2where 表别名 1. 字段名 3= 表别名 2.字段名 4;例： selecta.first_nameename,b.first_namecnamefroms_empa,s_empb wherea.manager_id=b.id;外连接使用一张表中的所有记录去和另一张表中的记录按条件匹配(空值也会匹配 )这个表中的所有记录都会显示。/ 想在哪边模拟记录就在哪边加上 (+)1. LEFTOUTERJOIN ：左外连接SELECTe.last_name,e.dept_id,FROMs_empeLEFTOUTERJOINs_deptdON(e.dept_id=d.id);等价于

15、SELECTe.last_name,e.dept_id,FROMs_empe,s_deptdWHEREe.dept_id=d.id(+);结果为：所有员工及对应部门的记录，包括没有对应部门编号 dept_id 的员工记录。2. RIGHTOUTERJOIN ：右外连接SELECTe.last_name,FROMs_empeRIGHTOUTERJOINs_deptdON(e.dept_id=d.id);等价于SELECTe.last_name,FROMs_empe,s_deptdWHEREe.dept_id(+)=d.id;结果为：所有员工及对应部门的记录，包

16、括没有任何员工的部门记录。3. FULLOUTERJOIN ：全外关联SELECTe.dept_id,d.idFROMs_empeFULLOUTERJOINs_deptdON(e.dept_id=d.id);结果为：所有员工及对应部门的记录，包括没有对应部门编号 department_id 的员工记录和没有 任何员工的部门记录。六. 组函数groupby 把 select 的结果集分成几个小组， 这个 groupby 子句可以跟在 select 语句后或是 having 前面。 groupby 子句也会触发排序操作，会按分组字段排序。select 组函数或分组的字段名 .from 表名 gro

17、upby 字段名 1, 字段名 2, ；例： selectavg(salary)froms_empgroupbydept_id;注意：组函数会忽略空值，但是 count(*) 除外，他会把空记录也记录在内。 avg 和 sum 这两个函 数的参数只能是 number 型的。以下所提到的函数可以使用任意类型做参数。max(.),min(.) 求最大值和最小值，count(*) 统计表中记录数。例： selectmax(),avg(a.salary),max()froms_empa,s_deptb,s_regioncwherea.dept_id=b.idandb.regio

18、n_id=c.idgroupbyb.id;注意：只要写了 groupby 子句， select 后就只能用 groupby 之后的字段或者是组函数。 having子句可以过滤组函数结果或是分组的信息，并且写在 groupby 子句后。七. 子查询可以嵌在 sql 语句中的 select 语句。在 select 语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。注意：在使用子查询时，要注意这个运算符是单行的(也就是只能是单值) ，还是多行运算符(范围，多值)。配合使用子查询返回的结果必须符合运算符的用法。例:selectfirst_name|''|last_n

19、amenamefroms_empwheretitlein(selecttitlefroms_empwheredept_id=42);查询和 42 部门员工职位相同的所有员工的姓名八. 约束针对表中的字段进行定义的。primarykey (主键约束 PK )保证实体的完整性，保证记录的唯一 主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键时，将两个字 段放在一起唯一标识记录，叫做联合主键。主键约束的定义：第一种定义形式：createtabletest(cnumberprimarykey);列级约束第二种定义形式：createtabletest(cnumber,prima

20、rykey(c);表级约束createtabletest(c1numberconstraintspk_c1primarykey);此约束有名字 :pk_c1createtabletest(cnumber,c2number,primarykey(c,c1);用表级约束可以实现联合主键foreignkey （外键约束 FK ）保证引用的完整性，外键约束，外键的取值是受另外一张表中的主键 或唯一键的约束， 不能够取其他值， 只能够引用主键或唯一键的值， 被引用的表， 叫做 parenttable （父表），引用方的表叫做 childtable （子表），要想创建子表，就要先创建父表，记录的插入也是

21、如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。（可以通过使用cascadeconstraints 选项来删除父表 )caretetableparent(c1numberprimarykey); createtablechild(cnumberprimarykey,c2numberreferencesparent(c1);或表级约束定义 :createtablechild(cnumberprimarykey,c2number,foreignkey(c2)referencesparent(c1

22、);非空约束( notnull )这是一个列级约束 ,在建表时 ,在数据类型的后面加上 notnull ，也就是在插入 时不允许插入空值。例：createtablestudent(idnumberprimarykey,namevarchar2(32)notnull,addressvarchar2(32);unique 唯一约束唯一约束，允许为空，要求插入的记录中的值是唯一的。例 ： createtablestudent(idnumber ， namevarchar2(32),addressvarchar2(32),primarykey(id),unique(address);check 约束检

23、查约束，可以按照指定条件，检查记录的插入。check 中不能使用伪列，不能使用函数，不能引用其他字段。例： createtablesal(a1number,check(a1>1000);九. 数据字典 数据字典是由系统维护的，包含数据库的信息 数据字典示图user_XXXXX 用户示图all_XXXXX 所有示图dba_XXXXX 数据库中所有示图v$_XXXXX 动态性能示图dict 或 dictionary 表示数据字典的数据字典。user_constraints 用户的表中约束的表其中有 constraints_name 字段存放的是约束名， constraint_type 字段存

24、放的是约束的类 型 ,r_constraints_name 字段表示外键引用自何处 .user_cons_column 表，是 用户 的列 级约 束表 ,column_name 字 段存 放的 是约 束字 段的 名 字 ,position 字段存放的是约束在联合键中的位置 .十 .事务 transaction原子操作，也就是不可分割的操作，必须一起成功一起失败。 事务的结束动作就是 commit ， DDL,DCL 语句执行会自动提交 commit 。sqlplus 正常退出是会做提交动作的 commit; ，当系统异常推出是，会执行回滚操作 rollback; 。 一个没有结束的事务， 叫做

25、活动的事务 (activetransaction), 活动的事务中修改的数据， 只有本会话 才能看见。十一 .Oracle 中的伪列伪列就像 Oracle 中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插 入、更新或删除它们的值。常用的伪列： rowid 和 rownum 。rowid ：数据库中的每一行都有一个行地址， rowid 伪列返回该行地址。可以使用 rowid 值来定位 表中的一行。通常情况下， rowid 值可以唯一地标识数据库中的一行。rowid 伪列有以下重要用途：1）能以最快的方式访问表中的一行；2）能显示表的行是如何存储的。3）可以作为表中行的唯一标

26、识。如： SQL>selectrowid,enamefromemp;rownum ：对于一个查询返回的每一行， rownum 伪列返回一个数值代表的次序。rownum 伪列特点：1）有个特点要么等于 1 要么小于某个值，不能直接等于某个值,不能大于某个值。2）常用于分页显示。伪列，20返回的第一行的 rownum 值为 1 ，第二行的 rownum 值为 2 ，依此类推。通过使用 rownum 用户可以限制查询返回的行数。如： SQL>select*fromempwhererownum<11; 从 emp 表中提取 10 条记录。十二 .序列 （sequence）createsequence 序列名 ;（不带参数时默认为从 1 开始每次递增 1， oracle 中为了提高产生序列的效率一般一次性产生 个序列放入当前会话的序列池中备用以加快效率 ）sequence 的参数：incrementbyn 起始值startwithn 递增量maxvaluen 最大值minvaluen 最小值cycle|nocycle 循环cachen 缓存 （第一次取时会一次取多少个 id 存起来 ）查看 sequence 示图： descuser_sequences;selectsequence_name,cache_size,last_numberfromuser_s