oracle基本知识总结

一.Oracle数据库中常用的数据类型varchar2(长度)可变长字符串char(长度)定长number()表示整数或者浮点数number(8)number(8,2)clog字符的大对象blog二进制的大对象二.数据库查询1）SELECT语句从表中提取查询数据.语法为SELECT[DISTINCT]{column1,column2,…}FROMtablenameWHERE{conditions}GROUPBY{conditions}ORDERBY{expressions}[ASC/DESC];说明：SELECT子句用于指定检索数据库的中哪些列，FROM子句用于指定从哪一个表或视图中检索数据。2）WHERE子句。WHERE子句用来选择符合条件的的记录.like'...'通配查询_,%between...and...,表示结果在这之间，betweenand是一个闭区间。!=，，^=，这三个都可以表示不等于。in(va1,val2,...)判断结果是否在这个集合中存在。like'...'字符串通配查询，'%'表示0或多个字符，'_'表示一个字符。...and...表示只有两个条件同时满足...or...表示条件只要满足其中之一就可以all...是要求都满足条件。not.....，则是可以与以上的条件产生反效果。...isnull使用来判断值是不是空。3)ORDERBY子句ORDERBY子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDERBY子句指定的表达式的值确定。ASC（默认，升序）DESC（降序）orderby目标列名（别名）排序顺序（不写排序顺序，会默认为升序排序）例：selectfirst_namefroms_emporderbyfirst_name;selectfirst_namefroms_emporderbyfirst_namedesc;三.SQL常用的命令分类及例子数据定义语言：create（创建）、alter（更改）和drop（删除）命令。数据操纵语言：insert（插入）、select（选择）、delete（删除）和update（更新）命令。事务控制语言：commit（提交）、savepoint（保存点）和rollback（回滚）命令。数据控制语言：grant（授予）和revoke（回收）。1.数据定义语言举例：SQLcreatetablemyTab(nonumber(4),namevarchar2(20));创建一个名为myTab的表，包含两列no和name；SQLaltertablemyTabmodify(namevarchar2(25));修改myTab中的name列，使此列能容纳25个字符；SQLaltertablemyTabadd(tel_novarchar2(20));给表myTab增加一列tel_no；SQLaltertablemyTabdropcolumntel_no;删除表myTab的tel_no列;SQLdroptablemyTab;删除表myTab；SQLtruncatetablemyTab;删除表myTab中的所有行（截断表）,注意:不可以回滚。2.数据操纵语言举例：SQLinsertintomyTabvalues(‘001’,’John’);向表myTab中插入一行数据；SQLselectdistinctsalary“薪水”froms_empwheresalary1500orderbysaldesc;选择表中salary大于1500的数据，以别名“薪水”显示并按照salary的降序进行排列输出；SQLcreatetableempaasselectempno,ename,job,salfromemp;从emp表中选择“empno,ename,job,sal”四列的数据建立新表empa;SQLcreatetableempaasselect*fromempwhere1=2;使用一个假条件根据现有表emp创建一个只包含结构的空表empa；SQLdeletefromempawheresal1500;删除表empa中sal小于1500的行；SQLupdateempasetsal=1500wheresal1500;更新，将表empa中sal小于1500的行的sal值全部改为1500。3.事务控制语言举例：SQLcommit;用于提交并结束事务处理；SQLsavepointmark1;保存点类似于标记，用来标记事务中可以应用回滚的点；SQLrollbacktosavepointmark1;回滚到保存点mark1。四.Oracle数据库函数注意:dual表(虚表)是专门用于函数测试和运算的.1.字符函数字符是大小写敏感的转小写lower(字段名)转大写upper(字段名)首字母大写initcap(字段名)字符串拼接concat(字段1,字段2)截取子串substr(字段名,起始位置，取字符个数)例:selectfirst_name,substr(first_name,2,2)subfroms_emp;（从名字的第二个字符开始取两个字符）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.日期函数缺省日期格式，日-月-年dd-mon-rr修改当前会话的日期格式，会按照指定的格式输出日期altersessionsetnls_date_format='yyyymmddhh24:mi:ss';返回当前日期sysdate例：selectsysdatefromdual;4.不同数据类型间转换函数将日期转成字符tochar(date,'日期格式')日期格式要用有效格式，格式大小写敏感'yyyymmddhh24:mi:ss'(标准日期格式),'year'(年的全拼),'month'(月的全拼)，'day'(星期的全拼)，'ddspth'(日期的全拼)例：selectto_char(sysdate,'yyyymmddhh24:mi:ss')fromdual;selectto_char(sysdate,'yearmonthdayddspth')fromdual;将字符串转成日期to_date('...','日期格式')例：selectto_char(to_date('20061103','yyyymmdd'),'dd-month-yy')fromdual；五.表连接（关联查询）等值连接selecttable1.column1，table2.column2fromtable1t1，table2t2wheret1.column3=t2.column4;表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用表名.字段名或表别名.字段名（列名）。当表的字段名是唯一时，可以不用加上表名或表的别名。注意：当为表起了别名，就不能再使用表名.字段名了。例如：selecte.first_name||’’||e.last_namename,d.namedept_namefroms_empe,s_deptdwheree.dept_id=d.id;非等值连接select[表别名1.字段名1]，[表别名2.字段名2],...from表1表别名1，表2表别名2where表别名1.字段名3.....表别名2.字段名4....可以使比较运算符，也可以使其他的除了'='的运算符例：selectfirst_name,salaryfroms_empwheresalarybetween1000and2000;自连接把一个表的两个字段关系转换成两个表字段之间的关系.select[表别名1.字段名1]，[表别名2.字段名2],...from表1表别名1，表1表别名2where表别名1.字段名3=表别名2.字段名4;例：selecta.first_nameename,b.first_namecnamefroms_empa,s_empbwherea.manager_id=b.id;外连接使用一张表中的所有记录去和另一张表中的记录按条件匹配(空值也会匹配)这个表中的所有记录都会显示。//想在哪边模拟记录就在哪边加上(+)1.LEFTOUTERJOIN：左外连接SELECTe.last_name,e.dept_id,d.nameFROMs_empeLEFTOUTERJOINs_deptdON(e.dept_id=d.id);等价于SELECTe.last_name,e.dept_id,d.nameFROMs_empe,s_deptdWHEREe.dept_id=d.id(+);结果为：所有员工及对应部门的记录，包括没有对应部门编号dept_id的员工记录。2.RIGHTOUTERJOIN：右外连接SELECTe.last_name,d.nameFROMs_empeRIGHTOUTERJOINs_deptdON(e.dept_id=d.id);等价于SELECTe.last_name,d.nameFROMs_empe,s_deptdWHEREe.dept_id(+)=d.id;结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。3.FULLOUTERJOIN：全外关联SELECTe.dept_id,d.idFROMs_empeFULLOUTERJOINs_deptdON(e.dept_id=d.id);结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。六.组函数groupby把select的结果集分成几个小组，这个groupby子句可以跟在select语句后或是having前面。groupby子句也会触发排序操作，会按分组字段排序。select[组函数或分组的字段名]...from表名groupby[字段名1],[字段名2],.....；例：selectavg(salary)froms_empgroupbydept_id;注意：组函数会忽略空值，但是count(*)除外，他会把空记录也记录在内。avg和sum这两个函数的参数只能是number型的。以下所提到的函数可以使用任意类型做参数。max(..),min(..)求最大值和最小值，count(*)统计表中记录数。例：selectmax(b.name),avg(a.salary),max(c.name)froms_empa,s_deptb,s_regioncwherea.dept_id=b.idandb.region_id=c.idgroupbyb.id;注意：只要写了groupby子句，select后就只能用groupby之后的字段或者是组函数。having子句可以过滤组函数结果或是分组的信息，并且写在groupby子句后。七.子查询可以嵌在sql语句中的select语句。在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值）。配合使用子查询返回的结果必须符合运算符的用法。例:selectfirst_name||''||last_namenamefroms_empwheretitlein(selecttitlefroms_empwheredept_id=42);查询和42部门员工职位相同的所有员工的姓名八.约束针对表中的字段进行定义的。primarykey（主键约束PK