前言:关系数据库的简单介绍1970.Dr.E.F.codded创建了关系数据库的模型（类似现在常用的二维表格）关系数据库由许多数据对象组成,被关系操作SQL命令管理着,数据之间有完整性的约束条件.RDBMS(relationaldatabasemanagementsystem)ORDBMS(objectrelationaldatabasemanagementsystem)ORACLERDBMS常称为ORACLESERVER它的内核是用C语言写的.
ORACLE的简单介绍ORACLE公司是一家提供综合技术产品、方案、服务的数据库公司ORACLE数据库设计成可大量存储数据，快速查询数据，保证数据的安全和一致性，跨网络的分布式管理及客户-服务器的配置等。ORACLESQL语法符合ANSI1986标准SQL和数据库交流的命令式语言SQL*PLUSORACLE的一种工具,用来运行SQL和PL/SQL语句PL/SQLORACLE的过程化编程语言
一、选择行1.简单的SELECT语句SELECT[DISTINCT]{*,COLUMN[ALIAS],……}FROMtable_name;字段的非唯一的结果集DISTINCT字段名1[,字段名2]数字类型字段名之间可以进行算术运算+-*/等例如:(字段名1*字段名2)/3合并字段内容的连接字符||字段名1||字段名2字段可以有别名SELECT字段名1[AS]'字段名1解释'FROMtable;
2.处理NULLNULL未定义，不可操作，什么都不是NULL!=0NULL!='空格'和NULL的任何运算都返回NULLORACLE里未定义的变量值都为NULLNVL函数可把NULL转换成其它类型的符号NVL(EXPR1,EXPR2)函数解释:IFEXPR1=NULLRETURNEXPR2ELSERETURNEXPR1编程技巧:NVL函数在多条件模糊查询的时候比较有用nvl('+input_value+','0')='0'orfiled_namelike'%+input_value+%'NVL函数可返回多种数据类型:返回日期NVL(start_date,'2002-02-01')
返回字符串NVL(title,'notitle')返回数字NVL(salary,1000)
3.使用SQL*PLUS（1）登录SQL*PLUS的方法用户名，密码，数据库连接字符串数据库连接字符串可以用net8easyconfiguration建立本地网络服务名配置，也可以手工编辑$ORACLE_HOME/network/admin/tnsnames.ora文件。在UNIX系统下不要用$sqlplususername/password@dbname这样别的用户用$ps命令能看出用户的密码SQL>desctable;显示表结构SQL>select*fromtab;查看用户下所有的表SQL>setpauseon;可以使大量结果集在用户按“Enter”(回车)后翻页SQL>setpagesize100;设定SQL语句返回结果集一页的行数100,默认值是14SQL>setlinesize100;设定SQL语句返回结果集一行的宽度100,默认值是80
3.使用SQL*PLUS（2）SQL*PLUS里的编辑命令最近一条SQL命令语句存在ORACLE内存（sqlbuffer）里,但SQL*PLUS命令却不会存进去。SQL*PLUS里的编辑命令:A[PPEND]text把text增加到当前行后面C[HANGE]/old/new/把当前行old符号替换成new符号,new为空，删除old符号CL[EAR]BUFF[ER]从sqlbuffer里删除所有的行DELn删除sqlbuffer里第n行I[NPUT]text在sqlbuffer后面插入textL[IST]n显示sqlbuffer里第n行n使第n行为当前行ntext第n行替换成text0text在第一行前面插入一行
3.使用SQL*PLUS（3）SQL*PLUS里的文件命令:SAV[E]filename[REP[LACE]|APP[END]]把sqlbuffer里的SQL命令存到OS下一个文件filename,默认的文件名后缀为.sql.REP[LACE]替换filename里的SQL命令APP[END]把sqlbuffer里的SQL命令添加到filename后GETfilename从filename里把SQL命令读到sqlbufferSTA[RT]filename运行上次保存到文件filename里的SQL命令@filename运行上次保存到文件filename里的SQL命令EDIT打开编辑窗口，编辑sqlbuffer里的SQL命令EDITfilename打开编辑窗口，编辑文件filename里的SQL命令SPO[OL]filename[OFF|OUT]把SQL命令结果输出到OS下一个文件filenameOFF结束输出到文件,OUT结束输出到文件并打印文件
4.SQL*PLUS里规定字段的显示格式规定数字的显示格式SQL>column字段名format99999999999;SQL>column字段名format999,999,999,999;规定字符串的显示宽度SQL>column字段名formata数字[word_wrapped];说明:一行只显示数字位的长度,超过长度折行,加word_wrapped后,单词不会折行规定long字符的显示宽度SQL>setlong200;规定字段名的显示内容SQL>column字段名heading'字段名显示内容';SQL>setheadingoff;查询时不显示字段名称规定字段的对齐方向SQL>column字段名justify[left|right|center];清除字段的格式SQL>column字段名clear;
5.SQL*PLUS里规定字段的显示格式例子SQL>columnlast_nameheading'Employee|Name'formata15;SQL>columnsalaryjustifyrightformat$99,999.99;SQL>columnstart_dateformata10null'NotHired';说明：如果start_date为null,显示字符串'NotHired'
6.判断题(T/F)(1).SQLcommandarealwaysheldinsqlbuffer.[T](2).SQL*PLUScommandassitwithquerydata.[T]SQL*PLUS命令只控制SELECT结果集的显示格式及控制文件.只有SQL命令能访问数据库.
二、限制选择行1.按指定的规则排序SELECTexprFROMtable[ORDERBY{column,expr}[ASC|DESC]];默认的排序是ASC升序(由小到大)还可以ORDERBY字段名的位置[1]|[2]ASC|DESC;当字段名很复杂或者是算术表达式时用字段名显示的位置排序很方便.
2.用WHERE限制选择行(1)比较操作符=><>=<=!=<>^=与NULL比较不能用上面的比较操作符ANYSOMEALLSQL操作符BETWEEN…AND…INLIKEISNULLNOTBETWEEN…AND…NOTINNOTLIKEISNOTNULL逻辑操作符ANDORNOT
3.用WHERE限制选择行(2)比较顺序(可以用括号改变它们的顺序)
(1).=<>>=<=inlikeisnullbetween(2).and(3).Or注意:char和varchar2的比较规则有不同:char比较时会忽略字符串后面的空格.varchar2会计算字符串后面的空格
4.LIKE操作%零到任意多个字符_一个字符例如:字段名like'M%'字段名like'%m%'字段名like'job_'如果要找含下划线的字符,要加反斜线例如:字段名like'%X/_Y%'escape'/'
5.日期字段的比较举例:日期字段betweento_date('2001-12-12','YYYY-MM-DD')andto_date('2002-02-01','YYYY-MM-DD')日期字段>to_date('2001-12-12','YYYY-MM-DD')and日期字段<=to_date('2002-02-01','YYYY-MM-DD');
6.不能用到索引的比较操作符ISNULLISNOTNULLLIKE'%m%'
三、单行函数1.数字函数ABS取绝对值POWER乘方LN10为底数取冪SQRT平方根EXPe的n次乘方LOG(m,n)m为底数n取冪数学运算函数:ACOSATANATAN2COSCOSHSIGNSINSINHTANTANHCEIL大于或等于取整数FLOOR小于或等于取整数MOD取余数ROUND(n,m)按m的位数取四舍五入值如果round(日期):中午12以后将是明天的日期.round(sysdate,'Y')是年的第一天TRUNC(n,m)按m的位数取前面的数值如果trunc(日期),确省的是去掉时间
2.字符函数CHR按数据库的字符集由数字返回字符CONCAT(c1,c2)把两个字符c1,c2组合成一个字符,和||相同REPLACE(c,s,r)把字符c里出现s的字符替换成r,返回新字符SUBSTR(c,m,n)m大于0,字符c从前面m处开始取n位字符,m等于0和1一样,m小与0,字符c从后面m处开始取n位字符TRANSLATE(c,f1,t1)字符c按f1到t1的规则转换成新的字符串INITCAP字符首字母大写,其它字符小写LOWER字符全部小写
UPPERLTRIM(c1,c2)RTRIM(c1,c2)TRIM(c1,c2)LPAD(c1,n,c2)RPAD(c1,n,c2)
字符全部大写去掉字符c1左边出现的字符c2
去掉字符c1左右两边的字符c2字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位
3.日期函数ADD_MONTHS(d,n)日期值加n月LAST_DAY(d)返回当月的最后一天的日期MONTHS_BETWEEN(d1,d2)两个日期值间的月份,d1 4.转换函数(2)to_date里日期显示格式YYYY年YEARYYYYYYQ季度MM月MONTHMONW星期(weekofmonth)WW,IW(weekofyear)(说明：周计是按ISO标准,从1月1日的星期数到后面七天为一周,不一定是从周一到周日)DD日DAYDYHH24小时HH12HHMI分钟SS秒如果想固定日期的显示格式可以在数据库的参数文件initorasid.ora里新写一行参数NLS_DATE_FORMAT=yyyy-mm-ddhh24:mi:ss可以在UNIX环境变量或者NT的注册表里的设置NLS_DATE_FORMAT=yyyy-mm-ddhh24:mi:ss
4.转换函数(3)如果想固定日期的显示格式可以用altersession命令改变SQL>altersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';它的作用顺序如下:initializationparameterEnvironmentvariableALTERSESSIONcommand
4.转换函数(4)to_char(number)里数字显示格式9数字位0数字前面补0to_char(-1200,'00000.00').小数点的位置,标记位置的逗号用在数字显示格式的左边L根据数据库字符集加货币符号to_char(-1200,'L9999.99')B把数字0显示为空格,用在数字显示格式的右边
MI右边的负数标记PR括起来的负数EEEE用指数方式表示
to_char(-1200,'9999.99MI')to_char(-1200,'9999.99PR')to_char(-1200,'9999.99EEEE')
5.输入字符,返回数字的函数instr(c1,c2)字符c2出现在c1的位置,不出现,返回0,常用于模糊查询length(c)按数据库的字符集,算出字符c的长度,跟数据库的字符集有关,一个汉字长度为1
6.有逻辑比较的函数NVL(EXPR1,EXPR2)函数解释:IFEXPR1=NULLRETURNEXPR2ELSERETURNEXPR1DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数解释:IFAA=V1THENRETURNR1IFAA=V2THENRETURNR2..…ELSERETURNNULL举例:decode(id,1,'deptsale',2,'depttech')
四、从多个表里选取数据记录1.数据表间的连接简单的连接语法:SELECT字段名1,字段名2,……FROM表名1,[表名2,……]WHERE表名1.字段名=表名2.字段名[AND……];SELECT字段名1,字段名2,……FROM表名1,[表名2,……]WHERE表名1.字段名=表名2.字段名(+)[AND……];有(+)号的字段位置自动补空值
连接的分类:等于的连接=不等于的连接!=BETWEEN…AND…IN不能一起用外连接有一个字段名(+),没有满足的条件补空值自连接同一个表自己跟自己连接例如找重复记录
注意IN和OR
2.数据表间的连接例子删除table_name表里字段名email重复的记录:SQL>deletefromtable_namet1wheret1.rowid>(selectmin(rowid)fromtable_namet2wheret1.email=t2.emailgroupbyemailhavingcount(email)>1);
找到手机用户的服务区域:SQL>selecta.handphoneno,nvl(c.name,'null'),a.totalscorefromtopscorea,chargeoperatorcc,chargeoperatorinfocwheresubstr(a.handphoneno,1,7)=cc.hpnohead(+)andcc.chargetype=c.chargetype(+)orderbya.totalscoredesc;
3.数据表间的连接技巧连接N个表,需要N-1个连接操作被连接的表最好建一个单字符的别名,字段名前加上这个单字符的别名BETWEEN..AND..比用>=AND<=要好连接操作的字段名上最好要有索引连接操作的字段最好用整数数字类型有外连接时,不能用OR或IN的比较操作
4.如何分析和执行SQL语句写多表连接SQL语句时要知道它的分析执行计划的情况.Sys用户下运行@/ORACLE_HOME/sqlplus/admin/plustrce.sql产生plustrace角色Sys用户下把此角色赋予一般用户SQL>grantplustraceto&;username;一般用户下运行@/ORACLE_HOME/rdbms/admin/utlxplan.sql产生plan_tableSQL>settimeon;说明：打开时间显示SQL>setautotraceon;说明：打开自动分析统计，并显示SQL语句的运行结果SQL>setautotracetraceonly;说明：打开自动分析统计，不显示SQL语句的运行结果接下来你就运行测试SQL语句，看到其分析统计结果了。一般来讲，我们的SQL语句应该避免大表的全表扫描。SQL>setautotraceoff;说明：关闭自动分析统计
五、集合函数经常和groupby一起使用1.集合函数列表AVG(DISTINCT|ALL|N)取平均值COUNT(DISTINCT|ALL|N|expr|*)统计数量
MAX(DISTINCT|ALLMIN(DISTINCT|SUM(DISTINCT|STDDEV(DISTINCT|果为0VARIANCE(DISTINCT|
|N)ALL|N)ALL|N)ALL|N)
取最大值取最小值取合计值取偏差值,如果组里选择的内容都相同,结
ALL|N)
取平方偏差值
2.使用集合函数的语法SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_expressionHAVINGgroup_conditionORDERBYcolumn;
3.使用count时的注意事项SELECTCOUNT(*)FROMtable;SELECTCOUNT(常量)FROMtable;
都是统计表中记录数量,如果没有PK后者要好一些SELECTCOUNT(all字段名)FROMtable;SELECTCOUNT(字段名)FROMtable;
不会统计为NULL的字段的数量SUM,AVG时都会忽略为NULL的字段4.用groupby时的限制条件SELECT字段名不能随意,要包含在GROUPBY的字段里GROUPBY后ORDERBY时不能用位置符号和别名限制GROUPBY的显示结果,用HAVING条件
5.例子SQL>selecttitle,sum(salary)payrollfroms_empwheretitlelike'VP%'groupbytitlehavingsum(salary)>5000orderbysum(salary)desc;
找出某表里字段重复的记录数,并显示SQL>select(duplicatefieldnames)fromtable_namegroupby(listoutfields)havingcount(*)>1;
6.判断题(T/F)(1)Groupfunctionsincludenullsincalculations[F](2)Usingthehavingclausetoexcluderowsfromagroupcalculation[F]解释:Groupfunction都是忽略NULL值的如果您要计算NULL值,用NVL函数Where语句在GroupBy前把结果集排除在外Having语句在GroupBy后把结果集排除在外
7.在SQL*PLUS里可使用的其它命令:
Ctrl^Cremark/*...*/HOST
终止正在运行的SQL语句-注释符号可执行的操作系统下的命令
有些unix可以用!
BREAKONcolumn_nameSKIPn[ONcolumn_nameSKIPn]按字段的名称column_name分隔显示,更清晰,SKIPn是在分隔处空行的数量nBREAKONROWSKIPn每一行间隔都放n个空行
COMPUTE集合运算符OF字段1ON字段2按字段2对字段1进行集合运算COMPUTE后面可以跟的集合运算符:SUMMINIMUMMAXIMUMAVGSTDVARIANCECOUNTNUMBER
8.在SQL*PLUS里可使用的其它命令举例:(scott用户)
BREAKONREPORTCOMPUTESUMLABELTOTALOFSALONREPORT在全部结果集后面算合计selectename,salfromempwherejob='SALESMAN';
COMPUTEAVGLABELavgOFSALONREPORT在全部结果集后面算平均值/再次执行上次的sql语句
breakonDEPTNOskip2onJOBskip1在BREAK字段结果集后面算合计COMPUTESUMOFSALONDEPTNOSELECTDEPTNO,JOB,ENAME,SALFROMEMPORDERBYDEPTNO,JOB;
SQL>CLEARBREAKS;SQL>CLEARCOMPUTES;
清除设置的BREAK条件清除设置的COMPUTE条件
六、子查询1.查询语句可以嵌套例如:SELECT……FROM(SELECT……FROM表名1,[表名2,……]WHERE条件)WHERE条件2;
2.何处可用子查询?当查询条件是不确定的条件时DML(insert,update,delete)语句里也可用子查询HAVING里也可用子查询
3.两个查询语句的结果可以做集合操作例如:并集UNION(去掉重复记录)
并集UNIONALL(不去掉重复记录)差集MINUS,交集INTERSECT
4.子查询的注意事项先执行括号里面的SQL语句，一层层到外面内部查询只执行一次如果里层的结果集返回多个，不能用=><>=<=等比较符要用IN.
5.子查询的例子(1)SQL>selecttitle,avg(salary)froms_empgroupbytitleHavingavg(salary)=(selectmin(avg(salary))froms_empgroupbytitle);找到最低平均工资的职位名称和工资
5.子查询的例子(2)子查询可以用父查询里的表名这条SQL语句是对的:SQL>selectcty_namefromcitywherest_codein(selectst_codefromstatewherest_name='TENNESSEE'andcityt_code=statet_code);说明：父查询调用子查询只执行一次．
6.取出结果集的80到100的SQL语句ORACLE处理每个结果集只有一个ROWNUM字段标明它的逻辑位置,并且只能用ROWNUM<100,不能用ROWNUM>80。以下是经过分析后较好的两种ORACLE取得结果集80到100间的SQL语句(ID是唯一关键字的字段名)：语句写法：SQL>select*from((selectrownumasnumrow,c.*from(select[field_name,...]fromtable_namewhere条件1orderby条件2)c)wherenumrow>80andnumrow<=100)orderby条件3;
七、在执行SQL语句时绑定变量1.接收和定义变量的SQL*PLUS命令ACCEPTDEFINEUNDEFINE&;
2.绑定变量SQL语句的例子(1)SQL>selectid,last_name,salaryfroms_emp
where
dept_id
=
&;department_number;Entervaluefordepartment_number:10old1:selectid,last_name,salaryfroms_empwheredept_id=&;department_number;new1:selectid,last_name,salaryfroms_empwheredept_id=10SQL>SETVERIFYOFF|ON;可以关闭和打开提示确认信息old1和new1的显示.
3.绑定变量SQL语句的例子(2)SQL>selectid,last_name,salaryfroms_empwheretitle='&;job_title';Entervalueforjob_title:StockClerk
SQL>selectid,last_name,salaryfroms_empwherehiredate>to_date('&;start_hire_date','YYYY-MM-DD');Entervalueforstart_hire_date:2001-01-01
把绑定字符串和日期类型变量时，变量外面要加单引号也可绑定变量来查询不同的字段名输入变量值的时候不要加;等其它符号
4.ACCEPT的语法和例子SQL>ACCEPTvariable[datatype][FORMAT][PROMPTtext][HIDE]说明：variable指变量名datatype指变量类型，如number,char等format指变量显示格式prompttext可自定义弹出提示符的内容texthide隐藏用户的输入符号使用ACCEPT的例子：ACCEPTp_dnamePROMPT'Providethedepartmentname:'ACCEPTp_salaryNUMBERPROMPT'Salaryamount:'ACCEPTpswdCHARPROMPT'Password:'HIDEACCEPTlow_datedateformat'YYYY-MM-DD'PROMPT“Enterthelowdaterange('YYYY-MM-DD'):”
4.DEFINE的语法和例子SQL>DEFINEvariable=value说明：variable指变量名value指变量值定义好了变良值后,执行绑定变量的SQL语句时不再提示输入变量使用DEFINE的例子：SQL>DEFINEdname=salesSQL>DEFINEdnameDEFINEdname=“sales”(CHAR)SQL>selectnamefromdeptwherelower(name)='&;dname';NAME-------------------------
salessalesSQL>UNDEFINEdnameSQL>DEFINEdnameSymboldnameisUNDEFINED
5.SQL*PLUS里传递参数到保存好的*.sql文件里SQL>@/路径名/文件名参数名1[,参数名2,….]SQL>start/路径名/文件名参数名1[,参数名2,….]
注意事项:一次最多只能获取9个&;变量,变量名称只能是从&;1,&;2到&;9变量名后不要加特殊的结束符号如果在SQL*PLUS里要把&;符号保存在ORACLE数据库里,要修改sql*plus环境变量defineSQL>setdefineoff;
八、概述数据模型和数据库设计1.系统开发的阶段:StrategyandAnalysisDesignBuildandDocumentTransitionProduction
2.数据模型Modelofsysteminclient'smindEntitymodelofclient'smodelTablemodelofentitymodelTablesondisk
3.实体关系模型(ERM)概念ERM(entityrelationshipmodeling)实体存有特定信息的目标和事件例如:客户,订单等属性描述实体的属性例如:,电话号码等关系两个实体间的关系例如:订单和产品等实体关系模型图表里的约定Dashedline(虚线)可选参数“maybe”Solidline(实线)必选参数“mustbe”Crow'sfoot(多线)程度参数“oneormore”Singleline(单线)程度参数“oneandonlyone”
4.实体关系模型例子每个订单都必须有一个或几个客户
每个客户可能是一个或几个订单的申请者
5.实体关系的类型1:1一对一M:1多对一1:M一对多
例如:的士和司机例如:乘客和飞机例如:员工和技能
6.校正实体关系的原则属性是单一值的,不会有重复属性必须依存于实体,要有唯一标记没有非唯一属性依赖于另一个非唯一的属性7.定义结构时的注意事项减少数据冗余减少完整性约束产生的问题确认省略的实体,关系和属性
8.完整性约束的要求Primarykey主关键字唯一非NULLForeignkey外键依赖于另一个Primarykey,可能为NULLColumn字段名符合定义的类型和长度Constraint约束条件用户自定义的约束条件,要符合工作流要求例如:一个销售人员的提成不能超过它的基本工资Candidatekey候选主关键字多个字段名可组成候选主关键字,其组合是唯一和非NULL的
9.把实体关系图映射到关系数据库对象的方法把简单实体映射到数据库里的表把属性映射到数据库里的表的字段,标明类型和注释把唯一标记映射到数据库里的唯一关键字把实体间的关系映射到数据库里的外键
其它的考虑:设计索引,使查询更快建立视图,使信息有不同的呈现面,减少复杂的SQL语句计划存储空间的分配重新定义完整性约束条件
10.实体关系图里符号的含义PK唯一关键字的字段FK外键的字段FK1,FK2同一个表的两个不同的外键FK1,FK1两个字段共同组成一个外键NN非null字段U唯一字段
U1,U1
两个字段共同组成一个唯一字段
11.实体关系图的例子
九、创建表1.ORACLE常用的字段类型ORACLE常用的字段类型有VARCHAR2(size)可变长度的字符串,必须规定长度CHAR(size)固定长度的字符串,不规定长度默认值为1NUMBER(p,s)数字型p是位数总长度,s是小数的长度,可存负数最长38位.不够位时会四舍五入.DATE日期和时间类型LOB超长字符,最大可达4GCLOB超长文本字符串BLOB超长二进制字符串BFILE超长二进制字符串,保存在数据库外的文件里是只读的.
数字字段类型位数及其四舍五入的结果原始数值1234567.89数字字段类型位数存储的值Number1234567.89Number(8)12345678Number(6)错Number(9,1)1234567.9Number(9,3)错Number(7,2)错Number(5,-2)1234600Number(5,-4)1230000Number(*,1)1234567.9
2.创建表时给字段加默认值和约束条件创建表时可以给字段加上默认值例如:日期字段DEFAULTSYSDATE这样每次插入和修改时,不用程序操作这个字段都能得到动作的时间
创建表时可以给字段加上约束条件例如:非空NOTNULL不允许重复UNIQUE关键字PRIMARYKEY按条件检查CHECK(条件)外键REFERENCES表名(字段名)
3.创建表的例子CREATETABLEDEPT(
EPTNODNAMELOC
NUMBER(2)CONSTRAINTPK_DEPTPRIMARYKEY,VARCHAR2(14),VARCHAR2(13));
CREATETABLEregion(IDnumber(2)NOTNULLPRIMARYKEY,postcodenumber(6)default'0'NOTNULL,areanamevarchar2(30)default''NOTNULL);
4.创建表时的命名规则和注意事项表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#大小写不区分不用SQL里的保留字,一定要用时可用双引号把字符串括起来．用和实体或属性相关的英文符号长度有一定的限制
注意事项:建表时可以用中文的字段名,但最好还是用英文的字段名创建表时要把较小的不为空的字段放在前面,可能为空的字段放在后面建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引一个表的最多字段个数也是有限制的，254个.
5.约束名的命名规则和语法约束名的命名规则约束名如果在建表的时候没有指明，系统命名规则是SYS_Cn(n是数字)约束名字符串的命名规则同于表和字段名的命名规则
6.使用约束时的注意事项约束里不能用系统函数,如SYSDATE和别的表的字段比较可以用本表内字段的比较
想在事务处理后,做约束的检查SQL>altersessionsetconstraintsdeferred.
7.由实体关系图到创建表的例子s_dept前提条件:已有region表且含唯一关键字的字段idSQL>CREATETABLEs_dept(idNUMBER(7)CONSTRAINTs_dept_id_pkPRIMARYKEY,nameVARCHAR2(25)CONSTRAINTs_dept_name_nnNOTNULL,region_idNUMBER(7)CONSTRAINTs_dept_region_id_fkREFERENCESregion(id),CONSTRAINTs_dept_name_region_id_ukUNIQUE(name,region_id));
8.较复杂的创建表例子
SQL>CREATETABLEs_emp(idNUMBER(7)CONSTRAINTs_emp_id_pkPRIMARYKEY,last_nameVARCHAR2(25)CONSTRAINTs_emp_last_name_nnNOTNULL,first_nameVARCHAR2(25),useridVARCHAR2(8)CONSTRAINTs_emp_userid_nnNOTNULLCONSTRAINTs_emp_userid_ukUNIQUE,start_dateDATEDEFAULTSYSDATE,commentsVARCHAR2(25),manager_idNUMBER(7),titleVARCHAR2(25),dept_idNUMBER(7)CONSTRAINTs_emp_dept_id_fkREFERENCESs_dept(id),salaryNUMBER(11,2),commission_pctNUMBER(4,2)CONSTRAINTs_emp_commission_pct_ckCHECK(commission_pctIN(10,12.5,15,17.5,20)));
8.通过子查询建表通过子查询建表的例子SQL>CREATETABLEemp_41ASSELECTid,last_name,userid,start_dateFROMs_empWHEREdept_id=41;
SQL>CREATETABLEAasselect*fromBwhere1=2;只要表的结构.
10.用子查询建表的注意事项可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。用子查询方式建立的表，只有非空NOTNULL的约束条件能继承过来,其它的约束条件和默认值都没有继承过来.根据需要，可以用altertableaddconstraint……再建立其它的约束条件，primarykey如等.
10.ForeignKey的可选参数ONDELETECASCADE在创建ForeignKey时可以加可选参数:ONDELETECASCADE它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除.如果没有ONDELETECASCADE参数，子表里有内容，父表里的主关键字记录不能被删除掉.
12.如果数据库表里有不满足的记录存在，建立约束条件将不会成功.
13.给表创建和删除同义词的例子SQL>CREATESYNONYMd_sum2FORdept_sum_vu;
SQL>CREATEPUBLICSYNONYM2FORalice.s_dept;
s_dept
SQL>DROPSYNONYMs_dept;
十、ORACLE里的数据字典1.什么是数据字典ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生,随着数据库的变化而变化,体现为sys用户下所有的一些表和视图.
2.数据字典里存了以下内容：用户信息用户的权限信息所有数据对象信息表的约束条件统计分析数据库的视图等不能手工修改数据字典里的信息.
3.常用的数据字典Dictionary存放所有数据表，视图，同义词名称和解释Dict_columns数据字典里字段名称的和解释Dba_users用户Dba_tablespaces表空间Dba_data_files数据库的文件Dba_free_space空闲表空间Dba_rollback_segs回滚段User_objects数据对象User_constraints约束条件User_sequences序列号User_views视图User_indexes索引User_synonyms同义词Session_roles用户的角色User_role_privs用户的角色权限User_sys_privs用户的系统权限User_tab_privs用户的表级权限V$session实时用户情况V$sysstat实时系统统计V$sesstat实时用户统计V$sgastat实时SGA使用V$locked_object实时锁V$controlfile控制文件V$logfile日志文件V$parameter参数文件
4.数据字典的分类数据字典四大类别User_用户下所有数据库对象All_用户权限范围内所有的数据库对象Dba_所有的数据库对象V$统计分析数据库的视图赋于oem_monitor权限非DBA用户也可查询V$*视图
5.查询数据字典SQL>select*fromdictionarywhereinstr(comments,'index')>0;
SQL>selectconstraint_name,constraint_type,2search_condition,r_constraint_name3fromuser_constraints4wheretable_name=‘&;table_name';
十一.控制数据1、INSERT(往数据表里插入记录的语句)SQL>insertinto表名(字段名1,字段名2,……)values(值1,值2,……);SQL>insertinto表名(字段名1,字段名2,……)select(字段名1,字段名2,……)from另外的表名where条件;可以用&;标记变量的方法多次输入记录
快速插入数据的方法,一般用于大于128M的数据转移SQL>insert/*+append*/into表名select*from另外的用户名.另外的表名WHERE条件;SQL>commit;
insertintot_sysmufudefselect'cst_demo'asusername,t.fitemcode,t.itemcode,t.itemdesc,t.itemtype,t.itemid,
t.lastmodif,t.lastmdate,t.creadtor,t.creatdatefromt_sysmufudeftwhereusername='sc0001admin'
注意事项：用INSERT/*+APPEND*/的方法会对target_tablename产生级别为6的独占锁，如果运行此命令时还有对target_tablename的DML操作会排队在它后面,对OLTP系统在用的表操作是不合适的。
2.插入字符串类型的字段的注意事项:字符串类型的字段值必须用单引号括起来,例如:’GOODDAY’如果字段值里包含单引号’需要进行字符串转换,我们把它替换成两个单引号’’字符串类型的字段值超过定义的长度会出错,最好在插入前进行长度校验‘’标记是NULL,user标明当前用户日期字段的字段值可以用当前数据库的系统时间SYSDATE,精确到秒用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)TO_DATE()还有很多种日期格式,可以参看ORACLEDOC.年-月-日小时:分钟:秒的格式YYYY-MM-DDHH24:MI:SSINSERT时最大可操作的字符串长度小于等于4000个单字节,如果要插入更长的字符串,请考虑字段用CLOB类型,方法借用ORACLE里自带的DBMS_LOB程序包.
3、UPDATE(修改数据表里记录的语句)SQL>UPDATE表名SET字段名1=值1,字段名2=值2,……WHERE条件;如果修改的值N没有赋值或定义时,将把原来的记录内容清为NULL,最好在修改前进行非空校验;值N超过定义的长度会出错,最好在插入前进行长度校验.
新功能，可以修改子查询后的结果集例子：SQL>update(select*froms_dept)setid=50whereid=60;
4、DELETE(删除数据表里记录的语句)SQL>DELETEFROM表名WHERE条件;注意：删除记录并不能释放ORACLE里被占用的数据块表空间.它只把那些被删除的数据块标成unused.如果确实要删除一个大表里的全部记录,可以用TRUNCATE命令,它可以释放占用的数据块表空间SQL>TRUNCATETABLE表名;此操作不可回退.
5、SQL语句的分类数据定义语言(DDL)：create、alter、drop（创建、修改结构、删除）（其他：rename）数据操纵语言(DML)：insert、delete、select、update（增、删、查、改）（其他：truncate）数据控制语言(DCL)：grant、revoke（授权、回收）、setrole事务控制：commit、rollback、savepoint（其他：locktable、setconstraint(s)、settransaction）审计控制：audit、noaudit系统控制：altersystem会话控制：altersession其他语句：comment（添加注释）、explainplan、analyze、validate、call
6、ORACLE里事务控制Commit提交事务Rollback回退事务Savepoint设置断点,在事务中标记位置,事务结束,断点释放事务结束的情况遇到commit或者rollback遇到DDL和DCL语句发现错误，如死锁用户退出SQL*PLUS系统重启或崩溃
6、事物控制和SAVEPOINT命令
7.DML操作的注意事项以上SQL语句对表都加上了行级锁,确认完成后,必须加上事物处理结束的命令COMMIT才能正式生效,否则改变不一定写入数据库里.行级锁也未能得到释放.如果想撤回这些操作,可以用命令ROLLBACK复原.在运行INSERT,DELETE和UPDATE语句前最好估算一下可能操作的记录范围,应该把它限定在较小(一万条记录)范围内,.否则ORACLE处理这个事物用到很大的回退段.程序响应慢甚至失去响应.如果记录数上十万以上这些操作,可以把这些SQL语句分段分次完成,其间加上COMMIT确认事物处理.太过频繁的commit不好
十二、改变表和约束条件
1.改变表的几种情况(1)运行时会加表级锁改变表的名称SQL>RENAME表名1TO表名2;SQL>ALTERTABLE表名1RENAMETO表名2;在表的后面增加一个字段SQL>ALTERTABLE表名ADD字段名字段名描述[DEFAULTexpr][NOTNULL][,字段名2……];修改表里字段的定义描述SQL>ALTERTABLE表名MODIFY字段名1字段名1描述[DEFAULTexpr][NOTNULL][,字段名2……];记录为空时，可以减少字段长度，改变字段类型修改DEFAULT值只作用于修改后的INSERT和UPDATE的记录修改NOTNULL约束只对现存含非空记录的字段起作用
1.改变表的几种情况(2)运行时会加表级锁删除表里的某个字段SQL>ALTERTABLE表名DROP字段名;给表里的字段加上/禁止/启用约束条件SQL>ALTERTABLE表名ADD|DISABLE|ENABLECONSTRAINT约束名PRIMARYKEY(字段名1[,字段名2……]);SQL>ALTERTABLE表名ADD|DISABLE|ENABLECONSTRAINT约束名UNIQUE(字段名1[,字段名2……]);加唯一关键字或者唯一约束条件时自动建立索引说明：禁止唯一关键字和唯一约束时索引仍然存在，可以被使用.
1.改变表的几种情况(3)运行时会加表级锁删除表里的约束条件SQL>ALTERTABLE表名DROPCONSTRAINTS约束名[CASCADE];会把约束相关的索引一起删除.CASCADE能同时删去外键的约束条件.把表放在或取出数据库的内存区SQL>ALTERTABLE表名CACHE;SQL>ALTERTABLE表名NOCACHE;改变表存储的表空间SQL>ALTERTABLE表名MOVETABLESPACE表空间名;注意:如果被转移表空间的表含有索引,表转移后索引变得不可用.我们要删除旧索引,建立新索引
2.删除表及表里的数据删除表SQL>DROPTABLE表名[CASCADECONSTRAINTS];清空表里的记录SQL>TRUNCATETABLE表名;按时间清空日志表里的记录，使用重新命名的方法(应用程序可能有短暂出错,可以选择在不繁忙的时间执行)按原来表A的建表语句创建新表A1,把表A重命名为A2（如果表A上有较频繁的DML操作,会对表加上行级锁，重命名过程用递归的方式循环做，直到DML操作结束，命名成功).
把创建新表A1重命名为A历史记录表A2备份或删除
3.删除表后应该注意的问题删除表后把表里的索引一起删去.删除表后会结束基于它的悬而未决的事物删除表后根据表创建的views,synonym,storedprocedure,storedfunction依然存在，但views,synonym变成非法的.需要手工找出它们并删除.如果用了CASCADECONSTRAINTS会把与它相关的约束一起删除此操作不可回退
4.给表加注释加注释的语法SQL>COMMENTONTABLE表名|COLUMN表名.字段名IS‘text‘加注释的例子SQL>commentontables_empis‘Enployeeinformation‘;SQL>commentoncolumns_emp.last_nameis‘‘;关于注释的数据库字典ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTS
十三、创建序列号1.创建序列号里各参数的解释SQL>CREATESEQUENCEname[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}]INCREMENTBYn一次增长n个数字NOMAXVALUE缺省值10E+27NOMINVALUE缺省值1NOCYCLE不循环,常用于唯一关键字CACHEn在内存里缓存n个序列,出错回退时会丢失oracle8i里默认的n是20
序列号的名称一般可以采用“表名_字段名”的命名规则
2.插入自动增长序列号字段的方法INSERT时如果要用到从1开始自动增长的数字做唯一关键字,应该先建立一个序列号.CREATESEQUENCE序列号的名称(最好是表名+序列号标记)INCREMENTBY1STARTWITH1MAXVALUE99999NOCYCLENOCACHE;其中最大的值按字段的长度来定,比如定义的自动增长的序列NUMBER(6),最大值为999999INSERT语句插入这个字段值为:序列号的名称.NEXTVAL
例子:SQL>insertintos_dept(id,name,region_id)values(s_dept_id.nextval,'finance',2);1rowcreated.只有运行了序列号的名称.nextval后序列号的名称.currval才有效才有值.
3.查询序列号的情况SQL>selectsequence_name,min_value,max_value,increment_by,last_numberfromuser_sequences;其中last_number指的是序列号的下一个值.
4.改变序列号SQL>ALTERSEQUENCEsequence[INCREMENTBYn][{MAXVALUENOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}];注意:不能改变它的起始值如果要改变序列的起始值,先把序列号删除掉,再新建一个.
n
|
5.删除序列号SQL>DROPSEQUENCEsequence;
6.不能用序列号的nextval和currval的地方
视图的查询有distinct的查询有groupby,having,orderby的查询有子查询的查询表里的缺省值
十四、创建视图1.视图的概念和优点视图是基于一个或多个表及视图的一些查询语句,它象显示数据的视窗,它本身是不存储数据的.
视图可以限制数据库的访问,更好的控制权限使用户使用简单的查询语句数据的非依赖性同一数据的不同表现形式
2.创建视图的语法SQL>CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias[,alias]...)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint]][WITHREADONLY]
参数解释:FORCE表不存在时,视图仍然可以创建成功WITHCHECKOPTION只有符合视图定义的记录才能被插入或修改WITHREADONLY不允许DML操作
Oracle8i以后创建视图可以用orderby
3.创建修改视图的例子SQL>CREATEORREPLACEVIEWsalvu41ASSELECTid,first_nameFIRST,last_nameLAST,salaryMONTHLY_SALARYFROMs_empWHEREdept_id=41;SQL>CREATEVIEWdept_sum_vu(name,minsal,maxsal,avgsal)ASSELECTd.name,MIN(e.salary),MAX(e.salary),AVG(e.salary)FROMs_empe,s_deptdWHEREe.dept_id=d.idGROUPBYd.name;注意:如果用select*fromtable_name创建的视图table_name的结构改变后view要重建或compile后才能显示新的字段内容
4.查询视图的数据字典SQL>setlong1600;SQL>selectview_name,textfromuser_views;说明:可以根据视图text_length来设置setlong数字;User_updatable_columns视图能查询视图里能被修改的字段
5.简单和复杂的视图对比特性简单视图表的数量一个有函数吗?没有有分组操作吗?没有有基于视图的DML操作吗?
复杂视图多个有有有没有
6.在视图上可以用DML命令吗?可以,但有一定的限制条件没有下面的情况,可以删除view里的记录.groupfunction,groupby,distinct没有上面和下面的情况,可以修改view里的记录.字段表达式,例如:salary*12含rownum的view没有上面两种情况,且view里含基表里所有非空字段的情况,可以往view里插入记录.
7.在视图里使用WITHCHECKOPTION约束条件SQL>createorreplaceviewempvu41asselect*froms_empwheredept_id=41withcheckoptionconstraintempvu41_ck;如果运行下面命令会出错ora-01402SQL>updateempvu41setdept_id=42whereid=16;原因:视图empvu41里规定只能看部门号为41的记录
修改后会把记录排除在视图
empvu41以外与它的约束条件冲突
8.删除视图SQL>DROPVIEWview_name;
十五、创建索引
1.索引的概念索引是数据库里的一种数据对象它利用B*树,hash,bitmap结构直接快速地访问数据它和表是分开存放的两个实体索引创建好了后,由系统自动调用和管理
2.什么时候创建索引?自动创建的索引:唯一关键字,唯一的约束条件手工需要创建的索引:大表查询时,sql语句where后经常用到的字段或字段组合字段内容差别很大有大量NULL值表很大,返回记录数较少
3.B*树索引的结构
每个索引由字段值和指针或ROWID组成
4.创建索引的语法CREATEINDEX索引名ON表名(字段1,[字段2,……])
TABLESPACE表空间名;
5.创建索引的注意事项创建索引时会加行级独占锁一个表的索引最好不要超过三个(特殊的大表除外)最好用单字段索引索引最好和表分不同的表空间存放结合SQL语句的分析执行情况,也可以建立多字段的组合索引和基于函数的索引大表的索引会占用很大的存储空间不要建唯一的索引,而应该加唯一的约束条件
6.查询索引的方法查询数据字典user_indexes和user_ind_columns
例子:SQL>SELECT23FROM4WHERE5AND
ic.index_name,ic.column_name,ic.column_positioncol_pos,ix.uniquenessuser_indexesix,user_ind_columnsicic.index_name=ix.index_nameic.table_name='S_EMP';
注意:数据字典里存放的字符都是大写的.
7.不用索引的地方表很小where后不经常使用的比较字段表被频繁修改返回记录数很多where后含ISNULL/ISNOTNULL/like‘%输入符%’等条件
8.重建索引的语法ALTERINDEX索引名REBUILDTABLESPACE原来表空间名NOLOGGING;定期重建索引可以减少索引的碎片,更有效地使用表空间.
9.删除索引SQL>dropindex索引名;SQL>altertable表名dropconstraint约束名;
十六、控制用户访问1.权限的类别系统级权限:针对整个系统操作的权限如:用户名/密码,使用表空间的限额等对象级权限:针对某个具体object操作的权限如:针对某个表,视图,表的某个字段的select,update,delete权限2.查看当前数据库的用户信息SQL>selectusername,default_tablespace,temporary_tablespacefromdba_users;查看在线用户信息SQL>selectcount(*)“number”,username“currentusername”fromv$sessiongroupbyusername;用户查看自己的缺省表空间SQL>selectusername,default_tablespacefromuser_users;
3.创建新用户SQL>createuserusernameidentifiedbypassworddefaulttablespacetablespace_nametemporarytablespacetempquotaunlimitedontablespace_namequota1konsystem[quota1konother_tablespace_name……];给用户赋权限SQL>grantconnect,resourcetousername;查看当前用户的权限角色SQL>select*fromuser_role_privs;查看当前用户的系统权限和表级权限SQL>select*fromuser_sys_privs;SQL>select*fromuser_tab_privs;
4、常用的角色及其权限CONNECT8
privs
连上Oracle,做最基本操作
RESOURCE8privs具有程序开发最的权限DBA114privs数据库管理员所有权限EXP_FULL_DATABASE5privs数据库整个备份输出的权限IMP_FULL_DATABASE64privs数据库整个备份输入的权限查看角色明细的系统权限SQL>select*fromrole_sys_privs;
5、改变老用户可以改变老用户的密码,缺省表空间,临时表空间,空间限额.SQL>alteruserusernameidentifiedbypassworddefaulttablespacetablespace_nametemporarytablespacetempquotaunlimitedontablespace_namequota1konsystem[quota1konother_tablespace_name……];撤销用户的角色或权限SQL>revokerole_nameorpriv_namefromusername;注意事项撤消用户的角色dba时,同时撤消了用户unlimitedtablespace的系统权限,切记要再次赋予resource角色给此用户SQL>grantresourcetousername;
6、删除用户如果用户下没有任何数据对象SQL>dropuserusername;如果用户下有数据对象SQL>dropuserusernamecascade;注意事项如果用户下有含clob,blob字段的表,应该先删除这些表后,才能用cascade选项完全删除.
7、角色的概念和管理角色是命名多个相关权限的组合.能把它赋于其它的用户或角色我们能创建角色,使权限管理更容易一些.
8、赋于系统的权限语法和例子语法:SQL>GRANTsys_privTO{user|role|PUBLIC}例子:SQL>GRANTcreatesessionTOsue,rich;SQL>GRANTcreatetableToscott,manager;
[WITHADMINOPTION];
注意:如果用WITHADMINOPTION通过中间用户赋于的系统权限权限仍然存在.
中间用户删除后,系统
9、赋于数据对象级的权限语法和例子语法:SQL>GRANTobject_priv[(columns)]ONobjectTO[WITHGRANTOPTION];例子:SQL>GRANTselectONs_empTOsue,rich;SQL>GRANTupdate(name,region_id)ONs_deptTOscott,manager;
{user|role|PUBLIC}
注意:如果用WITHGRANTOPTION通过中间用户赋于的对象权限权限就不存在了.
中间用户删除后,对象
