oracle总结
1.SQL与ORACLE的物理结构
总得讲，它们的物理结构很相似，SQL的数据库相当于ORACLE的模式（方案），SQL的文件组相当于ORACLE的表空间，作用都是均衡DISKI/O，SQL创建表时，可以指定表在不同的文件组，ORACLE则可以指定不同的表空间。Oracle创建表时创建到当前用户名的默认表空间中，所以创建用户时要制定默认的表空间。CREATETABLEA001（IDDECIMAL（8，0））ON[文件组]
--------------------------------------------------------------------------------------------CREATETABLEA001（IDNUMBER（8，0））TABLESPACE表空间注：以后所有示例，先SQL，后ORACLE2.SQL与ORACLE的外键约束
SQL的外键约束可以实现级联删除与级联更新，ORACLE则只充许级联删除。CREATETABLEA001（IDINTPRIMARYKEY，NAMEVARCHAR（20））CREATETABLEA002（IDINTREFERENCESA001（ID）ONDELETECASCADEONUPDATECASCADE，AGETINYINT）
CREATETABLEA001（IDINTPRIMAYKEY，NAMEVARCHAR2（20））
CREATETABLEA002（IDINTREFERENCESA001（ID）ONDELETECASCADE，AGENUMBER（2，0））
3.SQL与ORACLE的类型转换
SQL常用类型转换函数有：CAST、CONVERT、STR
ORACLE常用类型转换函数有：TO_CHAR、TO_NUMBER、TO_DATESELECTCONVERT（VARCHAR（20），GETDATE（），112）
------------------------------------------------------------------------------
SELECTTO_CHAR（SYSDATE，‘YYYYMMDD’）FROMDUAL4.SQL与ORACLE的自动编号
SQL的编号一般由IDENTITY字段来提供，可以灵活地设定种子值，增量，取值范围有BIGINT、INT、SMALLINT、TINYINT、DEIMAL等；ORACLE的编号一般由SEQUENCE来提供，由NEXTVAL与CURVAL函数从SEQUENCES取值。CREATETABLEA003（IDINTIDENTITY（-9999，9），NAMEVARCHAR（20））
---------------------------------------------------------------------------CREATESEQUENCESEQ_001START9999INCREMENTBY9CREATETABLEA004（IDINT）
INSERTINTOA004VALUES（SEQ_001.NEXTVAL）INSERTINTOA004VALUES（SEQ_001.CURVAL+1）5.SQL与ORACLE的分区表
从严格意思上来讲，SQL还没有分区表，它的分区表是以UNION为基础，将多个结果集串起来，实际上是视图；ORACLE的分区表有多种：PARTITIONBYRANGE、PARTITIONBYHASH、PARTITIONBYLIST，其它就是混合分区，以上三种基础分区的混合使用。当然ORACLE也可以象SQL那样分区视图。
CREATETABLEA1999（IDINT，NAMEVARCHAR（20））CREATETABLEA201*（IDINT，NAMEVARCHAR（20））CREATEVIEWV_PARTAS
SELECT*FROMA1999UNIONSELECT*FROMA201*
--------------------------------------------------CREATETABLEA_PART1(IDINT,NAMEVARCHAR2(20))6.7.
8.9.10.
11.
PARTITONBYRANGE(ID)(PARTITIONP1VALUESLESSTHEN(201*000)PATITIONP2VALUESLESSTHEN(MAXVALUE))CREATETABLEA_PART2(IDINT,NAMEVARCHAR2(20))
PARTITIONBYHASH(ID)PARTITIONS2STOREIN(USERS01,USERS02)CREATETABLEA_PART3(IDINT,NAMEVARCHAR2(20))PARTITIONBYLIST(ID)(
PARTIIONP1VALUES(‘01’,’03’,’05’)PARTITONP2VALUES(‘02’,’04’))SQL的字符串连接用+号，ORACLE字符串连接用||，单引号可以做转义符。SQL与ORACLE的动态SQL
SQL与ORACLE都支持动态SQL语句，SQL用EXEC（）执行的动态SQL语句，ORACLE用EXECUTEIMMEDIATE执行动态SQL。DECLARE@SQLVARCHAR(99)
SELECT@SQL=’declare@mintselect@m=count(*)fromsysobjectsselect@m’EXEC(@SQL)
--------------------------------------------DECLARE
SVARCHAR2(99);BEGIN
S:="SELECTCOUNT(*)FROM"||"USER_TABLES";EXECUTEIMMEDIATES;END;
返回记录集中前N条记录的语法？
SQL只有使用TOP，ORACLE可以使用ROWNUMSELECTTOPN*FROM记录集（表，视图，子查询）---------------------------------------------SELECT*FROM记录集WHEREROWNUMCREATESEQUENCESEQnameINCREMENTBY1STARTWITH1MAXVALUE99999999/创建触发器CREATETRIGGERTRGnameBEFOREINSERTONtable_nameREFERENCINGNEWAS:NEWFOREACHROWBeginSELECTSEQname.NEXTVALINTO:NEW.FIELDnameFROMDUAL;End;Java连接oracletry{
Class.forName("oracle.jdbc.driver.OracleDriver");Stringurl="jdbc:oracle:thin:@主机名：端口号：数据库名";//
Stringuser="xxxt";//登录数据库的用户名Stringpassword="xxx";//密码try{
con=DriverManager.getConnection(url,user,password);}catch(Exceptione){
System.out.println("error"+e.getMessage());}
}catch(Exceptione){
System.out.println("error"+e.getMessage());}
扩展阅读：Oracle总结
Oracle
一些概念
Linux中登录oracle的命令：sqlplus用户名/密码
showuser显示当前登录的身份.setpauseon（网页中分页）
setpauseoff分页显示（SQL*plus中在网页中是不分页）.oracle中默认日期和字符是左对齐，数字是右对齐tableorviewdoesnotexist;表或示图不存在
edit命令用于自动打开vi修改刚修执行过的sql的命令。
查看表的结构(包含的字段):descs_dept(控制台);descdept(浏览器)查询：selecttable_namefromuser_tables;(控制台)
一、数据库基本概念
1)数据库管理系统：一种操纵和管理数据库的大型软件,是用于建立、使用和维护数据库，Oracle、DB2、Mysql、MSServer;
2)数据库:按照数据结构来组织、存储和管理数据的仓库。3)数据：
4)表（Table）：数据库中的表与我们日常生活中使用的表格类似，它也是由行（Row）和列（Column）组成的。
5)视图：其实是一个虚拟的表，在数据库中并不实际存在。视图数据是由查询数据库表产生的，它限制了用户能看到和修改的数据。
6)索引：索引是根据指定的数据库表列建立起来的顺序。它提供了快速访问数据的途径，并且可监督表的数据，使其索引所指向的列中的数据不重复。
7)存储过程：存储过程是为完成特定的功能而汇集在一起的一组SQL程序语句，经编译后存储在数据库中的SQL程序。
8)触发器：触发器是一个用户定义的SQL事务命令的集合。当对一个表进行插入、更改、删除时，这组命令就会自动执行。9)ER图：实体关系图10)范式：
第一范式：数据库表的每一列都是不可分割的基本数据项；第二范式：要求实体的属性完全依赖于主关键字primarykey：SelectCourse表(学号,姓名,年龄,课程名称,成绩,学分)1、数据冗余2、更新异常3、插入异常4、删除异常
第三范式：数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖；
11)事务特性：原子性、一致性、隔离性、持久性
设置oracle事务隔离界别：
settransactionisolationlevelserializable;未提交读：事务A可以读取事务B未提交的数据。提交读：事务A只能读取事务B提交了的数据
可重复读：在同一个事务A里，先后执行同一个查询语句，得到的结果是一样的。
序列化读：事务A在执行的时候不允许其他事务的执行，隔离级别最高。12)SQL：结构化查询语言，是操作关系型数据库中的对象。
二、SQL语句
SQLStatement(SQL语句)
1)DDLDataDefinationLanguage数据库定义语言create,alter,drop,truncate
2)DMLDataManagementLanguage数据库操纵语言insert,upate,delete
3)DCLDataControlLanguage数据库控制语言grantrevoke
4)TCLTransactionControlLanguage)数据库事务控制语言commit,rollback,savepoint5)Select数据库查询语句Select
三、数据库查询
1、查询操作
1）投影操作只查询表的某些字段如:selectname,birthdayfromstudent;2）关联操作多个表操作
3）选择操作根据条件筛选记录，可认为是添加了where子句
2、查询语句
select[dintinct]{*,column[alias],}fromtablename;1）投影操作
①selecttable_namefromuser_tables;查询系统表②desc表名;查看表的结构③去重复数据distinct
例：selectdistinctdept_id,titlefroms_emp;
这样写的话,distinct会管到后面的所有字段,即对所有字段排序后,再排重。即排重的是dept_id和title的组合。
在select语句中可以使用数学表达式。
selectfirst_name,salary*12froms_emp;④别名，三种方法
1、selectfirst_namenamefroms_emp;2、selectfirst_nameasnamefroms_emp;
3、selectfirst_name"firstname"froms_emp;如果别名中有空格或大小写敏感,要把双引号括起来；父查询可调用子查询的别名，当前查询调用当前别名需要看执行顺序；双引号别名调用时仍需双引号但是内部字母区分大小写；且识别空格，有多少是多少；多个空格在显示时只有一个空格，无空格则不显示，这是和使用相区别；只有双引号中能添加空格；若别名中有空格则加上””⑤字符串拼接使用||符号
selectfirst_name||’’||last_namefroms_emp;（||’’||无论哪里出现空格都对拼接无障碍且无空格显示）
⑥空值的处理---nvl(column,defaultvalue)函数
selectfirst_name,12*salary*(1+nvl(commission_pct/100,0))froms_emp;nvl函数是指当第一个参数column如果为空(null值)的话，Oracle就会默认为第二个参数defaultvalue的值，如果第一个参数不为空的话，就选择第一个参数本身的值。
内容区分大小写；
2）选择操作:
①排序orderby字段|别名|字段位置升序（asc）|降序（desc）
selectfirst_name,salaryfroms_emporderbysalary;默认的是升序②排序字段的空值处理
降序时null值排在前,升序时null排在后
selectfirst_name,salaryfroms_emporderbyfirst_name,salarydesc;按first_name升序排序,如果first_name相同,再按salary排序能够触发排序的关键字:distinct、orderby、groupby
3、where子句
where子句使用在select...from...后面，用来选择所需（符合条件的）的记录where后面跟的是表达式也就是XXX=XXX，XXXbetweenXandX，XXXin（X，X，X），like"..."等
请注意selectcolumnsfromtablewhereclause
是先from表再where条件，最后select出你要的字段。非数字类单引号引用；
1)通配查询
between...and...表示结果在这区间之间，betweenand是一个闭区间，也就相当于...=...。
!=，，^=，这三个都是指不等于，且=，=，这些运算符也可以使用。
in(va1,val2,...)表示判断结果是否在这个枚举中存在，即每一个值都去匹配
like表示字符串通配查询，"%"表示任意多个字符，"_"，表示任意一个字符。注意：转义的用法：like‘S\\_%’escape‘\\’;
escape指定义什么是转义符，可以是任意的特殊符号，包括数字和字符串，但数字和字符串要是定义为转义符会不会对操作造成影响需要考虑考虑。
2）逻辑符
and表示只有两个或多个条件同时满足。or表示条件只要满足其中只一就可以。all表示条件都满足时。
not表示是可以与以上的条件产生反效果。
空值会对notin造成影响，也就是不等于任何值，但是空值例外，空值跟任何值比较都为空。notin不包含空值，null不被算在内。不包含在里面的值可能是有空值但是空值会被notin排除在。isnull表示判断值是否为空。
注意：Oracle中的字符串是严格区分大小写的。这里所谓的字符串是指数据表中的数据(信息)。
select{*,column[s][alias],...}在这里实现投影操作fromtable_name
[whereclause]查询条件注意:[]表示这部分内容可有可无[orderbycolumns(asc|desc)]排序子句
注意:[]表示这部分内容可有可无;(|)选择其一
四、单行函数
1、字符函数
lower(字段名)表示转小写，其中的参数可以是一个字符串常量或是一个字段名upper(字段名)表示转每个单词首字母大写；initcap(字段名)表示首字母大写；
concat(字段1,字段2)表示字符串拼接；（||’‘||）
substr(字段名,起始位置，取字符个数)表示截取字符子串，位置是从1开始length(字段名)表示该字段的字符长度，打出字符串长度
例：selectsubstr(first_name,-2,2)subfroms_emp;（取后两个）selectsubstr(first_name,2,2)subfroms_emp;（取前两个）使用"-"表示取后面的字符串，取的时候仍是从左往右取。
2、数值函数
round(数据,保留小数点后几位)表示四舍五入
可以用负数表示小数点前，0，表示小数点后第一位，也就是保留个位，-1表示个位（保留到十位）；小数点后面的位数可以为负数；从小数点处开始数，向左为负，向右为正。
例：selectround(15.36,1)fromdual;
trunc(数据，保留的位数（小数点后位数）)表示截取数字函数，截取个位之后补0；小数点后面的位数可以为负数；从小数点处开始数，向左为负，向右为正。在边截取整数位数字后补零；
例：selecttrunc(123.456,1)fromdual;
3、日期函数
日期格式，全日期格式世纪信息，年月日，时分秒。缺省日期格式，日-月-年dd-mon-rr
修改当前会话的日期格式，会按照指定的格式输出日期
altersessionsetnls_date_format="yyyymmddhh24:mi:ss";返回当前日期sysdate
例：selectsysdatefromdual;
selectsysdate+1fromdual;获得明天的日期，加1，单位是天日期是格式敏感的日期内置函数：
months_between（sysdate,addmonth(sysdate,5)）//两个月有多少天。add_months(sysdate,-5)在系统时间基础上延迟5月add_months(sysdate,-5*12)在系统时间基础上延迟5年last_day(sysdate)一个月最后一天
next_day(sysdate,’Friday’)下个星期星期几
round(sysdate,’day’)不是四除五入，是过了中午的留下，不过的略掉trunc(sysdate,’month’)不到一月的都省略例：
round("25-MAY-95","MONTH")01-JUN-95round("25-MAY-95","YEAR")01-JAN-95trunc("25-MAY-95","MONTH")01-MAY-95trunc("25-MAY-95","YEAR")01-JAN-95
4、不同数据类型间转换函数
将字符转换成数字to_number("...")
将数字转字符to_char(number，"fmt")fmt是数字格式将字符串转成日期to_date("...","日期格式")例：selectto_char(to_date("201*1103","yyyymmdd"),"dd-month-yy")fromdual；
selectto_char(3456.789,"9,999.999")fromdept;变成一个字符串的格式，9是一种显示格式，运行结果3,456.789除了9之外还可以写0,本位上有的话直接写，没有的话补0
selectto_char(3456.78,"000,000.000")fromdept;运行结果003,456.780selectto_char(3456.78,"fm$99,000.000")fromdept;去除前面的空格
selectto_number("123.4")+3fromdept;有默认的转换即去掉to_number，不同类型的转换可以强行也可以自动(不是所有的都可以)
格式是9时：9的整数个数不能小于数字的整数位数，小数部分没有的话会补00时：0的整数个数不能小于数字的整数位数，前后没有的都补0
改变NLS_LANG的值让输出结果的货币单位是￥或$，需修改环境变量，临时的即可
setenvNLS_LANG"SIMPLIFIEDCHINESE_CHINA.ZHS16GBK"setenvNLS_LANG"AMERICAN_AMERICA.US7ASCII"
在XP系统的浏览器或Oracle的PLSQL中可使用下面指令修改语言环境，之后并且要输入commit提交：
ALTERSESSIONSETNLS_LANGUAGE=american;
ALTERSESSIONSETNLS_LANGUAGE="SIMPLIFIEDCHINESE";5、关联操作①等值查询：表之间的连接是通过相等的字段值连接起来的查询称为等值连接查询。
查询员工的名称,部门名称,地区名称
selecte.first_name,d.name,r.namefroms_empe,s_deptd,s_regionrwheree.dept_id=d.idandd.region_id=r.id;查询ben在那个部门那个地区上班
selecte.first_name,d.name,r.namefroms_empe,s_deptd,s_regionrwheree.dept_id=d.idandd.region_id=r.idandlower(e.first_name)="ben";
②非等值连接:连接条件使用除等于运算符以外的其它比较运算符，比较被连接的列的列值。这些运算符包括>、>=、e,s_empmwheree.manager_id=m.id(+);有(+)表示本方会为对方补空值注意条件(+)跟在要全部选出的一方,即一方比一方多余的记录要输出的加（+），不能使用in和or；
fromt1,t2wheret1.c1=t2.c2(+)把t1表中匹配不上的记录重新找回来
fromt1,t2wheret1.c1(+)=t2.c2把t2表中匹配不上的记录重新找回来Selfjoin自连接Fromt1，t2
Wheret1。C1=t2。C2（innerjoin）Fromt1，t2
Wheret1。C1=t2。C2（+）（outerjoin）T1表中匹配不上的记录找回来Wheret1。C1（+）=t2。C2T2表中匹配不上的记录找回来Outerjoin=innerjoin+匹配不上来的；
五、组函数
1、groupby表示分组函数，having表示对查询结果进行过滤
where子句用来筛选from子句中指定的操作所产生的行，不能跟组函数groupby子句用来分组where子句的输出having子句用来从分组的结果中筛选行2、组函数
a)avg查询平均值
b)count查询记录条件,参数可以是任何类型，忽略空值；多少条记录不包含空值；
c)max查询最大值，字符串从左边第一个开始比，第一相同就比第二个，第一个不相同大的徘前面；d)min查询最小值
e)sum查询数字的和，貌似只用于数字，反正字符串不能用；所有组函数的计算都会忽略空值avg、sum只能用于数值类型3、注意：groupby子句也会触发排序操作，会按分组字段排序，即使用orderby查询提成平均值
selectavg(nvl(commission_pct,0))froms_emp;查询员工分布在几个不同的部门
selectcount(distinctdept_id)froms_emp;查询42部门的平均工资
selectavg(salary)salaryfroms_empwheredept_id="42";查询不同部门的平均工资
selecte.dept_id,avg(salary)froms_empegroupbye.dept_id;查询不同部门不同职位的平均工资
selecte.dept_id,e.title,avg(salary)froms_empegroupbye.dept_id,e.title;
注意:当使用groupby语句时，select后面的只能是组函数和groupby后面的字段，若不使用groupby语句时，select后面的可以是普通字段或者全部是组函数
查询不同部门不同职位的平均工资(要求显示部门名称)
selectd.name,e.title,avg(salary)froms_empe,s_deptdwheree.dept_id=d.idgroupbyd.name,e.title;
查询不同部门的平均工资(要求显示部门名称)根据部门编号和部门名称分组selecte.dept_id,d.name,avg(salary)froms_empe,s_deptdwheree.dept_id=d.idgroupbye.dept_id,d.name;求42部门的平均工资
selecte.dept_id,max(d.name),avg(salary)froms_empe,s_deptdwheree.dept_id=d.idandd.id=42groupbye.dept_id
4、SQL结构
SELECT{*,COLUMN1[ALIAS1],...}FROMTABLE_NAME
[WHERECLAUSE]查询条件GROUPBYHAVING
[ORDERBYCLAUSE]排序子句
5、子查询：就是可以嵌在任何的sql语句中的select语句，把子查询的结果当做范围再区查询一遍
在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边
注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值，in）。配合使用子查询返回的结果必须符合运算符的用法
查询和smith做同样工作的员工
selectlast_namefroms_empwheretitle=(selecttitlefroms_empwherelower(last_name)="smith")andlower(last_name)"smith";
selectlast_namefroms_empwheretitlein(selecttitlefroms_empwherelower(last_name)="smith")andlower(last_name)"smith";如果能保证子查询结果为单值可以用=,如果不确定可以用in
查询大于32部门平均工资部门的平均工资
selectdept_id,avg(salary)froms_empgroupbydept_idhavingavg(salary)>(selectavg(salary)froms_empwheredept_id=32);查询哪些人不是领导
selectfirst_namefroms_empwhereidnotin(selectmanager_idfroms_empwheremanager_idisnotnull);哪些员工的工资和本部门的平均工资一样
selectfirst_name,dept_id,salaryfroms_empwhere(dept_id,salary)in(selectdept_id,avg(salary)froms_empgroupbydept_id);
6、rownum实现分页oracle查询前十条语句select*from(
selectA.*,rowmunrnfrom(select*froms_emp)Awhererowmun=1;查询10~20条记录select*from(
selecta.*,rownumrnfrom(select*froms_emp)a)
wherernbetween10and20;
From，where后面可以跟子查询:SELECTfirst_name,salary
FROMs_empt1,(SELECTdept_id,AVG(salary)avgsalaryFROMs_empgroupbydept_id)t2WHEREt1.dept_id=t2.dept_idANDt1.salary>t2.avgsalary;
rownum：对于一个查询返回的每一行，rownum伪列返回一个数值代表的次序。rownum伪列特点：
1）要么等于1要么小于某个值，不能直接等于某个值,不能大于某个值2）常用于分页显示
返回的第一行的rownum值为1，第二行的rownum值为2，依此类推。通过使用rownum伪列，用户可以限制查询返回的行数
如：select*froms_empwhererownum引用完整性约束
2、FK(ForeignKey外键约束)至少存在两张表
把外键所在的表称子表,其引用的表称为父表,外键的一些规范约束，是在子表中设置外键，一定是与父表的主键对应先建父表,后建子表先删子表,再删父表
3、UK(UniqueKey唯一约束)唯一可以为空4、notnull非空约束
所谓的约束是指在建表的时候对字段设置，当插入(insert)数据时会根据约束对插入的数据进行检查，例如某一字段为notnull，若插入该字段的记录(数据)是一个null值，就会报擦插入数据错误。把ER图关系模式转化成表关系：
一对多多的那边做外键
多对多用中间表把两个表连起来,学生表---课程表是多对多关系,需要建立一个中间表,学生选课表,这个包含两个表的主键做为它的自己的联合主键一对一外键加个唯一约束范式
第一范式(1NF):在关系模式R中的每一种具体关系r中，如果每个属性值都是不可再分的最小数据单位，则称R是第一范式的关系。
第一范式简单的说就是要求属性具有原子性，不可以再分，第一范式面临的问题？引出第二范式
第二范式(2NF)：如果关系模式R(U,F)中的所有非主属性都完全依赖于任意一个候选关键字(可以使组合式的主键)，则称关系R是属于第二范式。
第二范式简单的说，就是每个表都有个主键，其他字段完全依赖于该主键，第二范式的问题？引出第三范式。
第三范式(3NF)：如果关系模式R(U,F)中的所有非主属性对任何候选关键字(即主键)都不存在传递依赖，则称关系R是属于第三范式。数据库定义语言
a)数据库对象：TableViewIndexSequenceb)建表定义表的数据结构数据类型varchar(n)可变类型，按实际数据的长度存储char(n)定长，按定义的长度存储
number(m,n)数值类型，可以定义宽度，也可以不定义宽度，默认缺省为38date日期类型，一定不能定义宽度
char类型是一个固定长度的类型，会补空位；varchar和varchar2都是可变的，varchar是sql的标准，再别的数据库中都有，varchar2是oracle自身的标准。char类型最多的字符是4000个！
建表的时候请注意字段与字段之间用“，”分割，最后一个字段不需要“，”标示，所谓字段如下的c1、c2等，数据是指数据表实际存在的信息，可通过insert、update等实现数据信息持久
createtablesunzw_test(-----------创建表c1varchar2(10),c2char(10),c3number(3),c4date);
insertintosunzw_testvalues("sunzw","123",200,to_date("201*11412:34:12","rrrrmmddhh24:mi:ss"));---要注意数据格式和精确度createtablesunzw_test(
c1varchar2(10)constraintssunzw_test_pk_c1primarykey,---把c1设为主键，列级约束c2char(10),c3number(3),c4date);
createtablesunzw_test(c1varchar2(10),c2char(10),c3number(3),c4date,
constraintssunzw_test_pk_c1primarykey(c1)---表级约束);
注意：定义联合主键时,只能用表级约束createtablesunzw_test(c1varchar2(10),c2char(10),c3number(3),c4date,
constraintssunzw_test_pk_c1（约束名可有可无）primarykey(c1,c2));
约束
NOTNULL约束(这个字段不允许为空)
createtablesunzw_test(c1numbernotnull,c2number);---为c1定义了非空约束
NOTNULL约束只能定义为列级约束
UNIQUE唯一约束
createtablesunzw_test(c1numberprimarykey,c2numberunique);---为c2定义唯一约束
唯一约束可以为空,唯一约束不考虑空值联合主键约束,这时只能用表级约束createtablesunzw_test(c1number,c2number,
unique(c1,c2)---c1和c2联合唯一);
PRIMARYKEY(主键约束)非空且唯一的定义
createtablesunzw_test(c1numberprimarykey,c2number);
createtablesunzw_test(c1numbernotnull,c2number,unique(c1));createtablesunzw_test(c1numberuniquenotnull,c2number);
父表:
createtablesunzw_parent(C1numberprimarykey,C2number);子表:
createtablesunzw_child(C1numberprimarykey,
C2numberreferencessunzw_parent(c1)---C2是parent表的外键);
先建父表,再建子表
错误
insertintosunzw_childvalues(1,1);---违反外键约束，因为父表中没有c1为1的记录让子表引用正确
insertintosunzw_parentvalues(1,1);---先插父表insertintosunzw_childvalues(1,1);---后插子表注意:删除表时,要先删子表,再删父表droptablesunzw_child;droptablesunzw_parent;或者
droptablesunzw_parentcascadeconstraints;级联约束，这样就删掉了父表和子表的FK约束
表级约束定义格式
createtablesunzw_child(C1numberprimarykey,C2number,
foreignkey(c2)referencessunzw_parent(c1));
级联删除约束
createtablesunzw_child(C1numberprimarykey,
C2numberreferencessunzw_parent(c1)ondeletecascade---级联删除,删除父表记录时,会级联把子表中的记录删除);C2是parent表的外键
createtablesunzw_child(C1numberprimarykey,
C2numberreferencessunzw_parent(c1)ondeletesetnull---级联删除,删除父表的记录时,会把子表中引用字段变为null);
CHECK检查约束(CK)定义一条件一约束字段列级定义:
createtablesunzw_child(C1numberprimarykey,C2numbercheck(c2>200));
表级定义:
createtablesunzw_child(C1numberprimarykey,
C2numbernotnull,---同check(c2isnotnull)和非空的效果一样check(c2>200));
创建三张表学生表,课程表,选课表
droptablesunzw_studentcascadeconstraints;droptablesunzw_coursecascadeconstraints;droptablesunzw_stu_curcascadeconstraints;createtablesunzw_student(sidnumber(10)primarykey,namevarchar2(10),agenumber(2),sexvarchar2(2),birthdaydate);
createtablesunzw_course(cidnumber(10)primarykey,namevarchar2(50));
createtablesunzw_stu_cur(sidnumber(10),cidnumber(10),scorenumber(10),
primarykey(sid,cid),
foreignkey(sid)referencessunzw_student(sid),foreignkey(cid)referencessunzw_course(cid));
用已经存在的表创建新的表createtablesunzw_empasselectid,first_name,last_name,salaryfroms_empwheredept_id=41;
只会把非空约束带过来，其他的约束在建新表时会自动消掉，所以除非空约束外，其他约束还要自己再加，符合的数据也带过来复制表的结构
createtablesunzw_empasselectid,first_name,last_name,salaryfroms_empwhere1=2;
1=2表示只取表结构，不要表中数据的建表方式，即表中的数据记录
DROPTABLE删除表
droptablesunzw_test1cascadeconstraitns;---删除表时级联删除这个表的约束
TRUNCATETABLEtablename;TRUNCATE比DROP快，在删除一个表中的全部数据时，须使用TRUNCATETABLE表名;因为用DROPTABLE或DELETEFROM表名时，TABLESPACE表空间该表的占用空间并未释放，反复几次DROP，DELETE操作后，该TABLESPACE上百兆的空间就被耗光了
truncatetable删除表中所有记录，并释放其占用的空间，执行快数据不可恢复（不能commit）
deletefrom删除表中所有记录，不释放其占用的空间，执行慢
七、数据操纵语言（DML）
1、INSERT
INSERT一次只能插一条
INSERTINTOtablenmaeVALUES(colvalue,colvalue,);
INSERT语句中可以放函数inserttablenamevalues(12,sysdate)
INSERT可以只插入某个列的值，INSERTintotablenmae(sunzw_emp（列名）)values（列值）
INSERT可能违反各种约束
2、UDPATE
UPDATEtablenameSETcolumn=value,column=valueWHEREcondition注意:如果不加where条件,修改可影响到所有的记录,所有记录将被修改只能一条一条跟新3、DELETE
DELETEFROMtablenameWHEREcondition;
用DELETE删除一张表的记录,删除之后,数据还能找回来
更多关于DML的写作规范及约束参考SQL语句.doc八、数据库事务控制语言
银行转账:A账户向B账户转账1000
Createtablesunzw_balance(idnumber(10),balancenumber(10));insertintosunzw_balancevalues(1,150000);insertintosunzw_balancevalues(2,40000);1向2转账10001如何转账:
updatesunzw_balancesetbalance=balance-1000whereid=1;updatesunzw_balancesetbalance=balance+1000whereid=2;
服务器中断服务以及其它的原因都可能导致更新一个账户后,另一个账户没有更新
如何保证两个操作同时成功,同时失败呢?要用事务来保证
updatesunzw_balancesetbalance=balance-1000whereid=1;updatesunzw_balancesetbalance=balance+1000whereid=2;commit|rollback;
commit提交将更新的数据保存到数据rollback回滚将数据恢复到修改前的状态savepoint保存点
savepointpoint1;创建一个保存点
rollbacktopoint1;回滚到指定的保存点
注意：做DML操作中注意要提交或回滚事务DDL，DCL会自动做提交操作
数据库事务是指作为单个逻辑工作单元执行的一系列操作事物的四个特性atomic原子性
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行
consistent一致性
事务在完成时，必须使所有的数据都保持一致状态insulation隔离性
由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据Duration持久性
事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持
事务能够保证相关的一组事务一起成功或一起失败有两种级别的事务：
局部事务（数据库的操作都是针对一个数据库的同一个用户）全局事务（分布式事务）（数据库操作是针对多个数据库或则一个数据库的不同用户下的表）
九、索引、序列、视图
sequence给table解决pk问题
index给table解决select效率问题view看table的子集和超集
sequence,index,view都为table服务,
如果把表删了,index就删掉了,view会变成无效
1、伪列
伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新或删除它们的值，常用的伪列：rowid和rownumrowid系统提供的伪列表示记录的真实物理位置。可以使用rowid值来定位表中的一行，通常情况下，rowid值可以唯一地标识数据库中的一行rowid伪列有以下重要用途：
1）能以最快的方式访问表中的一行2）能显示表的行是如何存储的3）可以作为表中行的唯一标识
如：selectrowid,first_namefroms_emp;createtables_info_c(idnumber(3)primarykey,namevarchar2(10),agenumber(3));
insertintos_info_cvalues(1,"zhang",23);insertintos_info_cvalues(2,"sun",24);insertintos_info_cvalues(3,"liu",30);insertintos_info_cvalues(4,"sun",23);insertintos_info_cvalues(5,"zhang",21);删除所有重复的记录
deletefroms_info_cwherenamein(
selectnamefroms_info_cgroupbynamehavingcount(*)>1);
删除重复记录并保留其中一条记录，下面的效率不高，通过自建一个副本操作，面试常考
deletefroms_info_cawhereid>(selectmin(id)froms_info_cbwherea.name=b.name);高效率
deletefroms_info_cawhererowid>(selectmin(rowid)froms_info_cbwherea.name=b.name);
rownum：对于一个查询返回的每一行，rownum伪列返回一个数值代表的次序。rownum伪列特点：1）有个特点要么等于1要么小于某个值，不能直接等于某个值,不能大于某个值
2）常用于分页显示
返回的第一行的rownum值为1，第二行的rownum值为2，依此类推。通过使用rownum伪列，用户可以限制查询返回的行数
如：select*fromempwhererownum注：PK和UK系统在建表的时候会自动建立索引
查询结果集很大重建索引
alterindexsunzw_index_namerebuild;
根据现有的索引重建一个新的索引,比删掉索引重新建立索引要快，但是有空间消耗
查询表上的索引
selectindex_name,index_type,uniquenessfromuser_indexeswheretable_name="SUNZW_STUDENT";
删除索引，例从未出现过where子句，不经常使用的最好删除，降低维护开销dropindexsunzw_index_name;
表达式(wherec1_1=1)或函数(whereround(c1)=1)会导致索引用不了，因为索引里记录的是字段的值，可以建基于函数或表达式的索引，但这样对索引做操作时目的是全表扫描
Createindextest_indexonsalary+1000
建索引createindextest_c1_indontest（c1）
FTSfulltablescan全表扫描：读出所有记录一个一个的匹配基于index的扫描
Rowid代表一条记录的物理位置（包含一条记录是属于哪张表（属于哪个Objectobject_id），是属于哪个数据文件的（file_id）是属于这个文件里德哪个数据块（block_id）在这个block中的第几条记录（row_id））
索引时是树形其中自有叶子是存放的值和rowID，查找类似二叉树通过rowid快速检索
降低IO数量
维护索引（update太多的表不宜建索引）
索引太多会降低效率因为要维护
Alterindextest_c1_indrebuild;(dropindexandcreateindex)两个效果一样，前这快但占空间大，后者慢占空间小从不用来定位的索引需删掉
如果把表删了，索引一定会被删了
PKuk上是一定会有索引的，你不建数据库会建索引里没有Null，null必须全表扫描
索引一般条件：经常会出现在连接字段例如FK，
经常出现在where后面的
取值范围特别大的(bitmapindex小指索引用得很少)字段里有很多null；
表越大结果集越小索引效果越好（大前提）
索引不一定快，索引是手段不合适条件：表比较小
不经常使用的反复修改的应该dropindex的是：从未出现在where里面的索引类型：
唯一性索引：PKUkCreateuniqueindexxxxontest（c1）对表进行更新时重复值会报错用来解决唯一性问题非唯一性索引：单列索引：
加快查询效率
索引用不了：where后面跟表达式wherec1+0=1
Where后面跟函数whereround（c1）=1实在要用可根据函数或表达式建索引
Wherec1=‘1’，c1number能用
Wherec1=1.c1varchar2(10)不能用隐式转换用到函数Wheresubstr（c1,1,2）=‘ab’不能用因为函数Wherec1like‘ab%’能用，这是范围扫描
3、VIEW视图
视图就相当于一条select语句,定义了一个视图就是定义了一个sql语句,视图不占空间,使用视图不会提高性能视图的作用：1）简化sql编写
2）限制数据库的访问权限，保证表数据安全创建视图：
createview视图名;
Createviewxxxasselect*fromtestwherec1=1;可以descviewnameSelect*fromviewname
如：
createorreplaceviewtestasselect*fromtest1wherec1=1;createorreplace:如果view存在就覆盖，不存在才创建force|noforce:基表存在时使用，不存在时则创建该表
注意:向视图中插入数据时，会直接插进基表中，查看视图中的数据时，相当于就是执行创建时的select语句删除视图:
dropview视图名;视图的约束:
withreadonly视图只读约束
withcheckoption不允许插入与where条件不符的记录，类似于check约束的功能
createviewtest_ccas
select*fromtestwherec1>10
withcheckoptionconstraintstest_check_op;联合索引：createindexxxxxxontest（c1，c2）
联合外建联合主键经常出现where。。。and。。。。需要建如果视图存在表不存视图无法使用
建表Createtabletest（c1number，c2number）Alterviewviewnamecompile自动做编译视图Selectcount（*）fromtest
SelectUser_viewsfromuser_viewswhereview_name=’viewname’可以看到视图定义
Selectobject_name，statusfromUser_objectswhereobject_name=‘viewname’可以看到视图状态
Createviews_empasselect*fromhiloo。S_emp；hiloo拥有该表的账户Grantselectons_emptosd1007（所在账户）
Default字段不会为空，你不提供他会自己添加Oltp在线处理事务系统
4、Sequence序列
createsequencesunzw_s1;
selectsunzw_s1.nextvalfromdual;selectsunzw_s1.currvalfromdual;
创建一个sequence之后,先用sunzw_s1.nextval取一次,才能取到sunzw.s1.currval;
Sequence是个可共享的对象,多个表可以共用一个,用来产生主键值createsequencenameincrementbyn//步长startwithn开始值maxvaluen最大值
cycle|nocycle表示达到最大值后从头开始cachen|nocache指定cache的值。如果指定CACHE值，oracle就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。使用cache或许会跳号，比如数据库突然不正常down掉（shutdownabort),cache中的sequence就会丢失,所以可以在createsequence的时候用nocache防止这种情况。
order;--指定排序
selectsequence_name,cache_size,last_numberfromuser_sequenceswheresequence_namelike"SUNZW_S1";
查询sequence的是不用缓存,缓存是指在sequence一次取多个数据放在内在中
修改sequence
altersequencenameincrementbyn;删除sequence
dropsequenceseqname;
隔离级别
四种读的情况：dirty-read脏读,committedread重复读，repeatableread序列读，serializableread(或是幻影phantomread)dirty-read：一个数据还没有提交就能读到
Committedread：一个事物提交了另一个事物才能读到
Repeatableread：一个事物更改了一个记录并提交后另一个事物仍然读到修改前的记录（oracle中没有隔离级别支持它！！）phantomread：一个事物提交增加了一个记录，另一事物前后两次要求读取相同oracle数据库只有serializableread和Committedread
设置更高的隔离级别---settransactionisolationlevelserializable;此时在别的事物中更改记录，改事物中记录仍是原记录
集合
union/unionall并集；union会去掉相交集合中的重复值，unionall不会去掉相交集合中的重复值，无论集合是否有交集union都会做去重的操作
selects.first_name,m.first_namefroms_empe,s_empmwheree.manager_id=m.idunionall
selectfirst_namefroms_empwheremanager_idisnullintersect交集也会自动去掉重复值minus
casewhen函数
selectfirst_name,salary,
casewhendept_id=31thensalary*1.1whendept_id=32thensalary*1.2whendept_id=33thensalary*1.3elsesalary
endasaft_salfroms_emp;
如果不在选择范围内则返回原值
decode函数
selectfirst_name,salary,
decode(dept_id,31,salary*1.1,32,salary*1.2,
33,salary*1.3)asaft_sal（可以少as）froms_emp;
如果不在选择范围内则返回空，格式:
decode(字段,条件1,结果1[,条件2,结果2])
selectfirst_name,max(salary),
max(decode(dept_id,31,salary*1.1))dept_31,max(decode(dept_id,32,salary*1.2))dept_32,max(decode(dept_id,33,salary*1.3))dept_33froms_emp
groupbyfirst_name;
EXISTS一检测到符合的就返回执行下一个manager_idSELECTlast_name,id,dept_idFROMs_empouterWHEREEXISTS(
SELECT"X"FROMS_EMP
WHEREmanager_id=outer.id);SELECTdnameFROMdeptwwWHEREEXISTS(SELECT9
FROMemp
WHEREdeptNO=ww.deptNO);
NOTEXISTS;原理与EXISTS相同有相同的就返回，只是一个是返回一个是过滤；继续执行下面的SELECTdnameFROMdeptww
WHEREEXISTS(SELECT9FROMemp
WHEREdeptNO=ww.deptNO);
差别在于带not的有结果要求全部不相同，另一个只要有一个相同的就行EXISTS一检测到符合的就返回执行下一个manager_idSELECTlast_name,id,dept_idFROMs_empouterWHEREEXISTS(
SELECT"X"FROMS_EMP
WHEREmanager_id=outer.id);SELECTdnameFROMdeptwwWHEREEXISTS(SELECT9
FROMemp
WHEREdeptNO=ww.deptNO);
NOTEXISTS;原理与EXISTS相同有相同的就返回，只是一个是返回一个是过滤；继续执行下面的SELECTdnameFROMdeptwwWHEREEXISTS(
SELECT9FROMemp
WHEREdeptNO=ww.deptNO);
差别在于带not的有结果要求全部不相同，另一个只要有一个相同的就行
字段名之间用逗号隔开表名与表名之间用逗号隔开
Selectname，jobfromyuangong，bumeng(name在yuangong表里，job在bumeng表里，两张表里都有员工号)出现笛卡尔积每张表里各取一条信息相互匹配；
重复的字段名必须指出出处，且只能用表别名，前面用表别名where处也要用表别名,FORM处不用表别名where处也可以不用；字段别名不可用
=注意等号位置等号位置和字段无关Count（*）统计记录数，有空值Count（dde）无空值记录数；有列名
Where后面只能跟单行函数，组函数不可以因为where子句过滤的是记录
Having后面跟的是组函数
先执行子查询，当子查询返回多个值时，完成去重，然后将结果返回主查询，再执行主查询设
置变量setenv
NLS_LANG.S/MPLIFID.CHINESE_CHINA.2HS16GBK?默认设置setenvNLS_LANG.USTASCⅡ
WHERE必须在GROUPBY前面SELECT后面有一个组函数其他就必须都是组函数，否则报错误；
Vsrcher2按字符串的实际长度存；字段取值不定长，一定有宽度Char按字符窜的定义长度存，不足的不空格；字段取值定长；没到长度系统会自动补
INSERT数据按四舍五入小数位，且无视小数位数，只取规定位，而且小数位四舍五入；整数位多了报错；Date一定不能定义宽度；
脚本文件：craetetabletable_namesyntax每条命令后面加“；“然后加commit
Sqlpulssd0907/se0907@test.sql最后的“0907“后面要加空格然后再写；已运行直接写文件名
Createtabletable_name()ASsubquery;(查询语句)该表直接由查询返回结果决定，拷贝的数据和非空约束其他没有；
Intsertintotablename（id，last_name,salary,title）Selectlast_name,salary,titlefroms_emp;Wherestart_date1000orderbysalarydesc,commission_pctasc（执行顺序fromwhere-select--order）null最大
Selectlower(‘SQLCourse’)froms_emp;lower小写upper大写如果用于条件语句，在条件上写即可To_number(‘ab’,’xx’)
To_char(salary,’$99.999.99’)不并长前面无0To_char(salary,’$00.000.00’)并长前面有0
$可以不写，想表示￥，写L(须改变相应的环境变量),不能直接写￥，只能是0或9
selectfirst_namefroms_empwheredept_id="42"selectfirst_namefroms_empwheredept_id=42to_number(‘42’)==42selectto_number("ab")froms_emp错误
selectto_number("ab","xx")froms_emp十六进制-十进制（171=10*16+11）
allNotbetweenNotinNotLikeIsNotNull
Where条件一AND条件2OR条件3；等价于（满足条件一和条件二的）或者满足条件3的
Where条件一AND（条件2OR条件3）等价于{（满足条件一和条件二）或者（条件一和条件三）}nvl的两个参数必须为相同类型表达字符和字符串使用‘’，
echo$ORACLE_SID.\\得到数据库实例的名字
sqlplusesd1007/esd1007数据库连接(本地方式)一定要设置环境变量sentenvPATH:$ORACLE_HOME/bin在vi（edit）中写命令时不可以加;在终端上写命令是要加;
Sqlpluscommanddesc表名=describe表名
select列名from表名；（小心分号）
selectsalary*12,first_namefroms_empwheresalary>1200先做where
selectsalary*12,first_namefroms_empwheresalary*121000这个快
where子句不可以做别名；
wheredept_idin（32，42,31）等于wheredept_id=any(32,42,31)；等于wheredept_id=32,or条件2or条件3；
友情提示：本文中关于《oracle总结》给出的范例仅供您参考拓展思维使用，oracle总结：该篇文章建议您自主创作。
来源：网络整理 免责声明：本文仅限学习分享，如产生版权问题，请联系我们及时删除。
《oracle总结》
由互联网用户整理提供,转载分享请保留原作者信息,谢谢!
http://m.bsmz.net/gongwen/671804.html
- 上一篇：201*年年度学期工作计划2
- 下一篇：大二学期总结