Oracle数据库培训PPT课件.ppt 203页

'SQL语言基础 主要内容基本的SELECT语句约束和排序数据单行函数多表显示数据、组函数合计数据创建和管理表子查询&操纵数据内置约束创建视图其他数据库对象 基本的select语句 目标完成本课后,您应当能够：•列出SQLSELECT语句的功能•执行基本的SELECT语句 SQLSELECT语句的功能列选择：你能够使用SELECT语句的列选择功能选择表中的列，这些列是你想要用查询返回的。当你查询时，你能够选择你查询的表中指定的列。行选择：你能够使用SELECT语句的行选择功能选择表中的行，这些行是你想要用查询返回的。你能够使用不同的标准限制你看见的行。连接：你能够使用SELECT语句的连接功能来集合数据，这些数据被存储在不同的表中，在它们之间可以创建连接。在后面的课程中你将学到更多关于连接的内容。 基本SELECT语句SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable;•SELECT确定哪些列•FROM确定哪张表 选择所有列与指定列SELECT*FROMdepartments;用跟在SELECT关键字后面的星号(*)，你能够显示表中数据的所有列。SELECTdepartment_id,location_idFROMdepartments;你能够用SELECT语句来显示表的指定列，指定列名之间用逗号分隔。 写SQL语句•SQL语句对大小写不敏感•SQL语句可以写成一行或多行•关键字不能简写或分开折行•子句通常放在不同的行•缩进用于增强可读性 算术表达式用算术运算符创建数字和日期数据的表达式操作说明+加-减*乘/除 使用算术运算符SELECTlast_name,salary,salary+300FROMemployees;优先级:•乘法和除法比加法和减法的优先级高•相同优先级的运算符从左到右计算•圆括号用于强制优先计算，并且使语句更清晰SELECTlast_name,salary,12*salary+100FROMemployees; 空值•null是一个未分配的、未知的，或不适用的值•null不是0，也不是空格•包含空值的算术表达式计算结果为空SELECTlast_name,job_id,salary,commission_pctFROMemployees; 定义列别名列别名：•改变列标题的名字•可用于计算结果•紧跟在列名后面–在列名和别名之间可以有选项AS关键字•如果别名中包含有空格、或者特殊字符、或者大小写敏感，要求用双引号SELECTlast_nameASname,commission_pctcommFROMemployees; 连字运算符连字运算符：•连接列或者字符串到其它的列•用两个竖线表示(||)•构造一个字符表达式的合成列SELECTfirst_name||last_nameASEmployeesFROMemployees; 文字字符串•文字字符串是包含在SELECT列表中的一个字符串，一个数字或者一个日期•日期和字符的文字字符串值必须用单引号括起来•每个文字字符串在每行输出一次SELECTlast_name||"isa"||job_idAS"EmployeeDetails”FROMemployees; 约束和排序数据 目标完成本课后,您应当能够执行下列操作：•用一个查询限制返回的行•用一个查询分类返回的行 限制选择的行用WHERE子句限制返回的行SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable[WHEREcondition(s)];WHERE子句跟着FROM子句WHERE限制查询满足条件的行condition由列名、表达式、常数和比较操作组成WHERE子句能够比较列值、文字值、算术表达式或者函数，WHERE子句由三个元素组成：列名，比较条件，列名、常量或值列表。 使用WHERE子句SELECTemployee_id,last_name,job_id,department_id]FROMemployeesWHEREdepartment_id=90; 字符串和日期•字符串和日期的值放在单引号中•字符值区分大小写，日期值是格式敏感的•日期的默认格式是DD-MON-RR.SELECTlast_name,job_id,department_idFROMemployeesWHERElast_name="Whalen"; 比较条件运算含义=等于>大于>=大于等于<小于<=小于等于<>不等于比较条件被用于一个表达式与一个值或与另一个表达式的比较。...WHEREhire_date="01-JAN-95"...WHEREsalary>=6000...WHERElast_name="Smith" 其它比较条件操作含义BETWEEN...AND...在两个值之间(包含)IN(set)匹配一个任意值列表LIKE匹配一个字符模板ISNULL是一个空值使用BETWEEN条件:SELECTlast_name,salaryFROMemployeesWHEREsalaryBETWEEN2500AND3500;使用IN条件SELECTemployee_id,last_name,salary,manager_idFROMemployeesWHEREmanager_idIN(100,101,201); 其它比较条件使用LIKE条件•使用LIKE条件执行有效搜索串值的通配符搜索•搜索条件既可以包含文字也可以包含数字：–%表示任意顺序的零个或多个字符–_表示一个字符SELECTfirst_nameFROMemployeesWHEREfirst_nameLIKE"S%";使用NULL条件•用ISNULL操作来测试空值SELECTlast_name,manager_idFROMemployeesWHEREmanager_idISNULL;NULL条件，包括ISNULL条件和ISNOTNULL条件。 逻辑条件运算含义AND如果两个组成部分的条件都为真，返回TRUEOR如果两个组成部分中的任一个条件为真，返回TRUENOT如果跟随的条件为假，返回TRUE可以在WHERE子句中用AND和OR运算符使用多个条件。使用AND操作：AND要求两个条件同时为真SELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary>=10000ANDjob_idLIKE"%MAN%"; 逻辑条件使用OR操作：OR操作要求两者之一为真即可SELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary>=10000ORjob_idLIKE"%MAN%";使用NOT操作SELECTlast_name,job_idFROMemployeesWHEREjob_idNOTIN("IT_PROG","ST_CLERK","SA_REP");注：NOT运算符也可以用于另一个SQL运算符，例如，BETWEEN、LIKE、和NULL。 优先规则求值顺序1算术运算2连字操作3比较操作4IS[NOT]NULL,LIKE,[NOT]IN5[NOT]BETWEEN6NOT逻辑条件7AND逻辑条件8OR逻辑条件使用圆括号改变优先规则 ORDERBY子句•用ORDERBY子句排序行–ASC:升序排序，默认–DESC:降序排序•ORDERBY子句在SELECT语句的最后SELECTlast_name,job_dateFROMemployeesORDERBYhire_date;语法:SELECTexprFROMtable[WHEREcondition(s)][ORDERBY{column,expr}[ASC|DESC]]; 单行函数 目标完成本课后，您应当能够执行下列操作：•描述在SQL中可用的函数的变量类型•在SELECT语句中使用字符，数字和日期函数•描述转换函数的使用 SQL函数函数是SQL的一个非常强有力的特性，函数能够用于下面的目的：执行数据计算修改单个数据项操纵输出进行行分组格式化显示的日期和数字转换列数据类型SQL函数有输入参数，并且总有一个返回值。注：在本课中讲述的大多数函数是针对SQL的Oracle版的。 SQL函数(续)有两种截然不同的函数：单行函数多行函数单行函数这些函数仅对单个行进行运算，并且每行返回一个结果。有不同类型的单行函数，本课下面的函数类型：字符数字日期转换多行函数这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。多行函数在后面的课程中介绍。 单行函数单行函数：•操纵数据项•接受多个参数并返回一个值•作用于每一个返回行•每行返回一个结果•可以修改数据类型•可以嵌套•接受多个参数，参数可以是一个列或者一个表达式 单行函数（续）单行函数的特性包括：作用于查询中返回的每一行每行返回一个结果可能返回一个与参数不同类型的数据值可能需要一个或多个参数能够用在SELECT、WHERE和ORDERBY子句中，可以嵌套。function_name[(arg1,arg2,...)]function_name是函数的名字。arg1,arg2是由函数使用的任意参数，可以由一个列名或者一个表达式提供。 单行函数（续）本课包括下面的单行函数：字符函数：接受字符输入，可以返回字符或者数字值数字函数：接受数字输入，返回数字值日期函数：对DATE数据类型的值进行运算(除了MONTHS_BETWEEN函数返回一个数字，所有日期函数都返回一个DATE数据类型的值。)转换函数：从一个数据类型到另一个数据类型转换一个值通用函数：NVL、NVL2、NULLIF、COALSECE、CASE、DECODE 字符函数单行字符函数接受字符数据作为输入，既可以返回字符值也可以返回数字值。字符函数可以被分为下面两种：大小写处理函数字符处理函数大小写处理函数如下：LOWER(column|expression)转换字符值为小写UPPER(column|expression)转换字符值为大写INITCAP(column|expression)转换每个单词的首字母值为大写，所有其它值为小写字符处理函数如下：CONCAT(column1|expression1,column2|expression2)连接第一个字符值到第二个字符值；等价于连接运算符(||)SUBSTR(column|expression,m[,n])从字符值中返回指定的字符，开始位置在m，n字符长度(如果m是负数，计数从字符值末尾开始；如果n被忽略，返回到串结束的所有字符)。 LENGTH(column|expression)返回表达式中的字符数INSTR(column|expression,‘string’,[,m],[n])返回一个命名串的数字位置。随意地，你可以提供一个位置m作为查找的开始，在字符串中第n次发现的位置。m和n的默认值是1，意味着在起始开始查找，并且报告第一个发现的位置。LPAD(column|expression,n,"string")RPAD(column|expression,n,"string")填充字符值左、右调节到n字符位置的总宽度TRIM(leading|trailing|both,trim_characterFROMtrim_source)使你能够从一个字符串修整头或尾字符(或两者)。如果trim_character或trim_source是字符文字，你必须放在单引号中。REPLACE(text,search_string,replacement_string)从字符串查找一个文本表达式，如果找到，用指定的值串代替它字符函数（续） 大小写处理函数这些函数转换字符串的大小写函数结果LOWER("SQLCourse")sqlcourseUPPER("SQLCourse")SQLCOURSEINITCAP("SQLCourse")SqlCourseLOWER：转换大小写混合的字符串为小写字符串UPPER：转换大小写混合的字符串为大写字符串INITCAP：将每个单词的首字母转换为大写，其他字母为小写SELECT‘Thejobidfor’||UPPER(last_name)||‘is’||LOWER(job_id)AS"EMPLOYEEDETAILS"FROMemployees; 字符处理函数函数结果CONCAT("Hello","World")HelloWorldSUBSTR("HelloWorld",1,5)HelloLENGTH("HelloWorld")10INSTR("HelloWorld","W")6LPAD(salary,10,"*")*****24000RPAD(salary,10,"*")24000*****TRIM("H"FROM"HelloWorld")elloWorld 数字函数ROUND:四舍五入指定小数的值ROUND(45.926,2)45.93TRUNC(45.926,2)45.92MOD(1600,300)100ROUND(column|expression,n)四舍五入列、表达式或值为n位小数位，或者，如果n被忽略，无小数位。(如果n是负值，小数点左边的数被四舍五入)TRUNC(column|expression,n)截断列、表达式或值到n位小数，或者，如果n被忽略，那么n默认为0MOD(m,n)返回m除以n的余数 使用ROUND函数SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;使用TRUNC函数SELECTTRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROMDUAL;使用MOD函数SELECTlast_name,salary,MOD(salary,5000)FROMemployeesWHEREjob_id="SA_REP";注：MOD函数经常用于确定一个值是奇数还是偶数 日期的使用•Oracle数据库用内部数字格式存储日期：世纪，年，月，日，小时，分钟和秒•默认日期显示格式是DD-MON-RR.SELECTlast_name,hire_dateFROMemployeesWHERElast_namelike""G%";";SYSDATE函数返回：•Date•TimeSYSDATE是一个日期函数，它返回当前数据库服务器的日期和时间。SELECTSYSDATEFROMDUAL; 用日期计算•从日期加或者减一个数，结果是一个日期值•两个日期相减，得到两个日期之间的天数•用小时数除以24，可以加小时到日期上既然数据库以数字方式存储日期，你就可以用算术运算符进行计算，例如，加或减。你可以加或减数字常数以及日期。你可以进行下面的运算：运算结果说明date+number日期加一个天数到一个日期上date-number日期从一个日期上减一个天数date-date天数用一个日期减另一个日期date+number/24日期加一个小时数到一个日期上 日期函数函数说明MONTHS_BETWEEN两个日期之间的月数ADD_MONTHS加日历月到日期NEXT_DAY下个星期几是几号LAST_DAY指定月的最后一天ROUND四舍五入日期TRUNC截断日期 日期函数（续）MONTHS_BETWEEN(date1,date2)：计算date1和date2之间的月数，其结果可以是正的也可以是负的。如果date1大于date2，结果是正的，反之，结果是负的。结果的小数部分表示月的一部分。ADD_MONTHS(date,n)：添加n个日历月到date。n的值必须是整数，但可以是负的。NEXT_DAY(date,‘char’)：计算在date之后的下一个周(‘char’)指定天的日期。char的值可能是一个表示一天的数或者是一个字符串。LAST_DAY(date)：计算包含date的月的最后一天的日期ROUND(date[,‘fmt’])：返回用格式化模式fmt四舍五入到指定单位的date，如果格式模式fmt被忽略，date被四舍五入到最近的天。TRUNC(date[,‘fmt’])：返回用格式化模式fmt截断到指定单位的带天的时间部分的date，如果格式模式fmt被忽略，date被截断到最近的天。 使用日期函数•MONTHS_BETWEEN(‘01-SEP-95’,‘11-JAN-94’)19.6774194•ADD_MONTHS(‘11-JAN-94’,6)"11-JUL-94"•NEXT_DAY(‘01-SEP-95’,‘2’)下个星期五是几号"08-SEP-95"•LAST_DAY("01-FEB-95")"28-FEB-95" 使用日期函数（续）假定SYSDATE="25-JUL-95":•ROUND(SYSDATE,"MONTH")01-AUG-95•ROUND(SYSDATE,"YEAR")01-JAN-96•TRUNC(SYSDATE,"MONTH")01-JUL-95•TRUNC(SYSDATE,"YEAR")01-JAN-95•TRUNC(TO_DATE("25-JUL-95"),"YEAR")01-JAN-95 转换函数数据类型转换•隐式数据类型转换•显式数据类型转换对于直接赋值，Oracle服务器能够自动地进行下面的转换：从到VARCHAR2orCHARNUMBERVARCHAR2orCHARDATENUMBERVARCHAR2DATEVARCHAR2对于表达式赋值，Oracle服务器能自动地进行下面的转换：从VARCHAR2orCHAR到DATE从VARCHAR2orCHAR到NUMBER 转换函数(续）显式数据类型转换SQL提供三种函数来从一种数据类型转换值到另一种：TO_CHAR(number|date,[fmt],[nlsparams])转换一个数字或日期值为一个VARCHAR2字符串，带格式化样式fmt。数字转换：nlsparams参数指定下面的字符，它由数字格式化元素返回：小数字符99999.99前导009999本地货币符号L9999国际货币符号$9999如果忽略nlsparams或其它参数，该函数在会话中使用默认参数值。 TO_CHAR(number|date,[fmt],[nlsparams])指定返回的月和日名字及其缩写的语言。如果忽略该参数，该函数在会话中使用默认日期语言。TO_NUMBER(char,[fmt],[nlsparams])用由可选格式化样式fmt指定的格式转换包含数字的字符串为一个数字。Nlsparams参数在该函数中的目的与TO_CHAR函数用于数字转换的目的相同。TO_DATE(char,[fmt],[nlsparams])按照fmt指定的格式转换表示日期的字符串为日期值。如果忽略fmt，格式是DD-MON-YY。Nlsparams参数的目的与TO_CHAR函数用于日期转换时的目的相同。 对日期使用TO_CHAR函数TO_CHAR(date,"format_model")格式模板•必须加单引号，并且区分大小写•能够包含任一有效的日期格式元素•有一个fm元素用来删除填补的空，或者前导零•用一个逗号与日期值分开SELECTemployee_id,TO_CHAR(hire_date,"MM/YY")Month_HiredFROMemployeesWHERElast_name="Higgins"; 日期格式模板的元素YYYY数字全写年YEAR年的拼写MM月的两数字值MONTH月的全名DY周中天的三字母缩写DAY周中天的全名MON月的三字母缩写DD月的数字天 使用TO_NUMBER和TO_DATE函数•转换字符串到数字，用TO_NUMBER函数格式化：TO_NUMBER(char[,"format_model"])•转换字符串到日期，用TO_DATE函数格式化：TO_DATE(char[,"format_model"])Selectto_number(‘12345’)fromdual;Selectto_date(‘20000810’,’yyyy-mm-dd’fromdual; 通用函数这些函数可用于任意数据类型，并且适用于空值•NVL(expr1,expr2)•NVL2(expr1,expr2,expr3)•NULLIF(expr1,expr2)•COALESCE(expr1,expr2,...,exprn)NVL转换空值为一个实际值NVL2如果expr1非空，NVL2返回expr2；如果expr1为空，NVL2返回expr3。参数expr1可以是任意数据类型NULLIF比较两个表达式，如果相等返回空；如果不相等，返回第一个表达式COALESCE返回表达式列表中的第一个非空表达式 NVL函数转换一个空值到一个实际的值•可用的数据类型可以是日期、字符和数字•数据类型必须匹配：–NVL(commission_pct,0)–NVL(hire_date,"01-JAN-97")–NVL(job_id,"NoJobYet")语法:NVL(expr1,expr2)在语法中：expr1是包含空值的源值或者表达式expr2是用于转换空值的目的值Selectnvl(‘1’,’notnull’fromdual;注:如果expr1为空则返回expr2r的值 使用NVL2函数NVL2函数检查第一个表达式，如果第一个表达式不为空，那么NVL2函数返回第二个表达式；如果第一个表达式为空，那么第三个表达式被返回。expr2expr1非空时的返回值语法NVL2(expr1,expr2,expr3)在语法中：expr1是可能包含空的源值或表达式expr3expr1为空时的返回值SelectNVL2(‘1’,’notnull’,’null’)fromdual;SelectNVL2(‘1’,’notnull’,’null’)fromdual; 使用NULLIF函数NULLIF函数比较两个表达式，如果相等，函数返回空，如果不相等，函数返回第一个表达式。第一个表达式不能为NULL。语法NULLIF(expr1,expr2)在语法中：expr1是对于expr2的被比较原值expr2是对于expr1的被比较原值。(如果它不等于expr1，expr1被返回)。Selectnullif(‘abc’,’abcd’)fromdual; 使用COALESCE函数•COALESCE函数超过NVL函数的优点是COALESCE函数能够接受多个交替的值。•如果第一个表达式非空，它返回该表达式；否则，它做一个保留表达式的结合。COALESCE函数返回列表中的第一个非空表达式。语法COALESCE(expr1,expr2,...exprn)在语法中：expr1如果它非空，返回该表达式expr2如果第一个表达式为空并且该表达式非空，返回该表达式exprn如果前面的表达式都为空，返回该表达式Selectcoalesce(‘",‘","bca")fromdual; 条件表达式在SQL语句中提供IF-THEN-ELSE逻辑的使用。两种用法：–CASE表达式–DECODE函数CASE表达式CASEexprWHENcomparison_expr1THENreturn_expr1WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]END DECODE函数DECODE(col|expression,search1,result1[,search2,result2,...,][,default])DECODE函数在比较表达式(expression)和每个查找(search)值后解码表达式，如果表达式与查找相同，返回结果。如果省略默认值，当没有查找值与表达式相匹配时返回一个空值。 多表显示数据&组函数合计数据 目标完成本课后,您应当能够执行下列操作：•写SELECT语句使用等值和非等值连接从多个表中访问数据•使用外连接查看不满足连接条件的数据•识别可用的组函数•描述组函数的使用•用GROUPBY子句分组数据•用HAVING子句包含或排除分组的行 笛卡尔乘积•笛卡尔乘积的形成，当：–一个连接条件被遗漏时–一个连接条件不正确时–在第一个表中的所有行被连接到第二个表的所有行时•为了避免笛卡尔乘积的形成，在WHERE子句中应当总是包含正确的连接条件。 用Oracle语法连接表使用一个连接从多个表中查询数据SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;•在WHERE子句中写连接条件•当多个表中有相同的列名时，将表名作为列名的前缀定义连接•当数据从多表中查询时，要使用连接(join)条件。一个表中的行按照存在于相应列中的公值被连接到另一个表中的行，即，通常所说的主键和外键列。 什么是等值连接?EMPLOYEESDEPARTMENTS 用等值连接返回记录SELECTemployees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_idFROMemployees,departmentsWHEREemployees.department_id=departments.department_id; 使用表别名•使用表别名简化查询•使用表别名改善性能SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id;原则表别名最多可以有30个字符，但短一些更好。如果在FROM子句中表别名被用于指定的表，那么在整个SELECT语句中都要使用表别名。表别名应该是有意义的。表别名只对当前的SELECT语句有效。 多于两个表的连接EMPLOYEESDEPARTMENTSLOCATIONS 非等值连接EMPLOYEESJOB_GRADES 用非等值连接返回记录SELECTe.last_name,e.salary,j.grade_levelFROMemployeese,job_gradesjWHEREe.salaryBETWEENj.lowest_salANDj.highest_sal; 外连接DEPARTMENTSEMPLOYEES 外连接语法•你可以用一个外连接查看那些不满足连接条件的行•外连接运算符是加号(+)SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column=table2.column(+);•用外连接返回不直接匹配的记录•如果在连接条件中使用外连接操作，缺少的行就可以被返回。操作符是一个在圆括号中的加号(+)，它被放置在连接的缺少信息的一侧。为了使来自不完善表的一行或多行能够被连接，该操作符有产生一个或多个空行的作用。 使用外连接SELECTe.last_name,e.department_id,d.department_nameFROMemployeese,departmentsdWHEREe.department_id(+)=d.department_id; 什么是组函数?组函数操作行集，给出每组的结果EMPLOYEES在EMPLOYEES表中的最高薪水 组函数的类型•AVG平均值•COUNT计数•MAX最大值•MIN最小值•STDDEV标准差•SUM合计•VARIANCE方差 组函数(续)每个函数接收一个参数，下面的表确定你可以在语法中使用的选项：函数说明AVG([DISTINCT|ALL]n)n的平均值，忽略空值COUNT({*|[DISTINCT|ALL]expr})用*计数所有行，包括重复和带空值的行。expr求除了空计算MAX([DISTINCT|ALL]expr)expr的最大值，忽略空值MIN([DISTINCT|ALL]expr)expr的最小值，忽略空值STDDEV([DISTINCT|ALL]x)n的标准差，忽略空值SUM([DISTINCT|ALL]n)合计n的值，忽略空值VARIANCE([DISTINCT|ALL]x)n的方差，忽略空值 组函数的语法SELECT[column,]group_function(column),...FROMtable[WHEREcondition][GROUPBYcolumn][ORDERBYcolumn];使用组函数的原则DISTINCT使得函数只考虑不重复的值；ALL使得函数考虑每个值，包括重复值。默认值是ALL，因此不需要指定。用于函数的参数的数据类型可以是CHAR、VARCHAR2、NUMBER或DATE。所有组函数忽略空值。为了用一个值代替空值，用NVL、NVL2或COALESCE函数。当使用GROUPBY子句时，Oracle服务器隐式以升序排序结果集。为了覆盖该默认顺序，DESC可以被用于ORDERBY子句。 使用AVG、SUM、MIN、MAX函数你可以使用AVG和SUM用于数字数据SELECTAVG(salary),MAX(salary),MIN(salary),SUM(salary)FROMemployeesWHEREjob_idLIKE"%REP%"; 使用COUNT函数COUNT(*)返回一个表中的行数COUNT函数有三中格式：COUNT(*)COUNT(expr)COUNT(DISTINCTexpr)COUNT(*)返回表中满足SELECT语句标准的行数，包括重复行，包括有空值列的行。如果WHERE子句包括在SELECT语句中，COUNT(*)返回满足WHERE子句条件的行数。COUNT(expr)返回在列中的由expr指定的非空值的数。COUNT(DISTINCTexpr)返回在列中的由expr指定的唯一的非空值的数。注：expr为列名 组函数和Null值所有组函数忽略列中的空值。在幻灯片的例子中，平均值只基于表中的那些COMMISSION_PCT列的值有效的行的计算。平均值计算是用付给所有雇员的总佣金除以接受佣金的雇员数(4)。SELECTAVG(commission_pct)FROMemployees; 在组函数中使用NVL函数NVL函数强制组函数包括空值。在幻灯片的例子中，平均值被基于所有表中的行来计算，不管COMMISSION_PCT列是否为空。平均值的计算是用付给所有雇员的总佣金除以公司的雇员总数(20)。SELECTAVG(NVL(commission_pct,0))FROMemployees; 创建数据组:GROUPBY子句语法用GROUPBY子句划分表中的行到较小的组中SELECTcolumn,group_function(column)FROMtable[WHEREcondition][GROUPBYgroup_by_expression][ORDERBYcolumn];在语法中，group_by_expression指定那些用于将行分组的列，这些列的值作为行分组的依据。使用WHERE子句，你可以在划分行成组以前过滤行。在GROUPBY子句中必须包含列。在GROUPBY子句中你不能用列别名。默认情况下，行以包含在GROUPBY列表中的字段的升序排序。你可以用ORDERBY子句覆盖这个默认值。如果在SELECT子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在GROUPBY子句中。如果你未能在GROUPBY子句中包含一个字段列表，你会收到一个错误信息。 约束分组结果:HAVING子句用HAVING子句约束分组：1.行被分组2.应用组函数3.匹配HAVING子句的组被显示SELECTcolumn,group_functionFROMtable[WHEREcondition][GROUPBYgroup_by_expression][HAVINGgroup_condition][ORDERBYcolumn]; 使用HAVING子句SELECTdepartment_id,MAX(salary)FROMemployeesGROUPBYdepartment_idHAVINGMAX(salary)>10000; 嵌套组函数显示最大平均薪水SELECTMAX(AVG(salary))FROMemployeesGROUPBYdepartment_id; 创建和管理表 目标完成本课后,您应当能够执行下列操作：•描述主要数据库对象•创建表•描述列定义时可用的数据类型•改变表的定义•删除、改名和截断表 数据库对象表基本存储单元,由行和列组成视图逻辑地从一个或多个表中表示数据子集序列数字值发生器索引改善一些查询的性能同义词给对象可选择的名字 命名规则表命名和列命名：•必须以字母开始•必须是1–30个字符长度•只能包含A–Z,a–z,0–9,_,$,和#•同一个用户所拥有的对象之间不能重名•不能用Oracle服务器的保留字注：名字是大小写不敏感的，例如，EMPLOYEES与eMPloyees或eMpLOYEES作为同一个名字来处理。 CREATETABLE语句•用户必须有：–CREATETABLE权限–一个存储区域CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][,...]);•必须指定：–表名–列名、列数据类型和列的大小schema与所有者的名字一样table表的名字DEFAULTexpr指定默认值column列的名字datatype列的数据类型和长度 引用另一个用户的表•表属于另一个用户，不在该用户的方案中•在那些表名字的前面使用所有者的名字作为前缀如果一个表不属于本用户，那么，其所有者的名字必须放在表名的前面SELECT*FROMuser_b.employees; 创建表•创建表CREATETABLEdept(deptnoNUMBER(2),dnameVARCHAR2(14),locVARCHAR2(13));•确认表的创建DESCRIBEdept Oracle数据库中的表•用户表：–由用户创建和维护的表的集合–包含用户信息•数据字典：–由Oracle服务器创建和维护的表的集合–包含数据库信息有四种数据字典视图，每一种有一个特定的前缀来反映其不同的目的。USER_这些视图包含关于用户所拥有的对象的信息。ALL_这些视图包含所有用户可访问的表(对象表和相关的表)的信息。DBA_这些视图是受限制的视图，它们只能被分配有DBA角色的用户所访问。V$这些视图是动态执行的视图，包含数据库服务器的性能、存储器和锁的信息。 查询数据字典•查看本用户所拥有的表的名称SELECTtable_nameFROMuser_tables;•查看本用户所拥有的不同的对象类型SELECTDISTINCTobject_typeFROMuser_objects;•查看本用户所拥有的表、视图、同义词和序列SELECT*FROMuser_catalog; 数据类型数据类型说明VARCHAR2(size)可变长度的字符数据CHAR(size)固定长度的字符数据NUMBER(p,s)可变长度的数字数据DATE日期和时间值LONG最大2G的可变长度字符数据CLOB最大4G的字符数据RAWandLONGRAW原始二进制数据BLOB最大4G的二进制数据BFILE最大4G的，存储在外部文件中的二进制数据ROWID一个64进制的数制系统，表示表中一行的唯一地址 用子查询创建表该方法既可以创建表还可以将从子查询返回的行插入新创建的表中。CREATETABLEdept80ASSELECTemployee_id,last_name,salary*12ANNSAL,hire_dateFROMemployeesWHEREdepartment_id=80;原则被创建的表要带指定的列名，并且由SELECT语句返回的行被插入到新表中。字段的定义只能包括列名和默认值。如果给出了指定的列，列的数目必须等于子查询的SELECT列表的列数目。如果没有给出了指定的列，表的列名应和子查询中的列名是相同的。完整性规则不会被传递到新表中，仅列的数据类型被定义。 ALTERTABLE语句用ALTERTABLE语句来：•添加一个新列•修改一个已存在的列•为新列定义一个默认值•删除一个列添加列ALTERTABLEtableADD(columndatatype[DEFAULTexpr][,columndatatype]...);修改列ALTERTABLEtableMODIFY(columndatatype[DEFAULTexpr][,columndatatype]...);删除列ALTERTABLEtableDROP(column); 添加新列•用ADD字句添加列ALTERTABLEdept80ADD(job_idVARCHAR2(9));添加新列的原则你可以添加或修改列。你不能指定新添加的列的位置，新列将成为最后一列。 修改列•可以改变列的数据类型、大小和默认值ALTERTABLEdept80MODIFY(last_nameVARCHAR2(30));•对默认值的改变只影响后来插入表中的数据原则你可以增加宽度或一个数字列的精度。你可以增加数字列或字符列的宽度。你可以减少一个列的宽度，但仅在列中只包含空值或表中没有行时。你可以改变数据类型，但仅在列中只包含空值时。你可以转换一个CHAR列到VARCHAR2数据类型或转换一个VARCHAR2列到CHAR数据类型仅当列中只包含空值时，或者你不改变列的大小时。对默认值的改变仅影响以后插入的列。 删除列用DROPCOLUMN子句从表中删除列ALTERTABLEdept80DROPCOLUMNjob_id;原则列可以有也可以没有数据。用ALTERTABLE语句，一次只能有一列被删除。表被修改后必须至少保留一列。一旦一列被删除，它不能再恢复。 删除表•在表中的所有数据和结构都被删除•任何未决的事务都被提交•所有的索引被删除•你不能回退DROPTABLE语句DROPTABLEdept80;DROPTABLE语句删除Oracle表定义，当你删除一个表时，数据库丢失表中所有的数据，并且所有与其相关的索引也被删除。 改变一个对象的名字•执行RENAME语句，改变一个表、视图、序列或同义词RENAMEdeptTOdetail_dept;注:你必须是对象的所有者 截断表•TRUNCATETABLE语句：–删除表中所有的行–释放该表所使用的存储空间TRUNCATETABLEdetail_dept;•不能回退用TRUNCATE删除的行•作为选择，可以用DELETE语句删除行 子查询&操纵数据 目标完成本课后,您应当能够执行下列操作：•描述子查询能够解决的问题类型•定义子查询•列出子查询的类型•写单行和多行子查询•描述每个DML语句•插入行到表中•更新表中的行•从表中删除行•控制事务 用子查询解决问题用子查询解决问题假想你想要写一个查询来找出挣钱比Abel的薪水还多的人。为了解决这个问题，你需要两个查询：一个找出Abel的收入，第二个查询找出收入高于Abel的人。你可以用组合两个查询的方法解决这个问题，放置一个查询到另一个查询中。内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。 子查询语法SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable);•子查询(内查询)在主查询之前执行一次•子查询的结果被用于主查询(外查询)子查询可以被放在CREATEVIEW语句中、CREATETABLE语句、UPDATE语句、INSERT语句的INTO子句和UPDATE语句的SET子句中。 使用子查询SELECTlast_nameFROMemployeesWHEREsalary>(SELECTsalaryFROMemployeesWHERElast_name="Abel"); 使用子查询的原则•子查询放在圆括号中•将子查询放在比较条件的右边•在子查询中的ORDERBY子句一般不需要。•在单行子查询中用单行运算符，在多行子查询中用多行运算符。子查询的类型•单行子查询：从内SELECT语句只返回一行的查询•多行子查询：从内SELECT语句返回多行的查询 单行子查询•仅返回一行•使用单行比较符运算符含义=等于>大于>=大于或等于<小于<=小于或等于<>不等于 执行单行子查询SELECTlast_name,job_id,salaryFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=141)ANDsalary>(SELECTsalaryFROMemployeesWHEREemployee_id=143); 多行子查询•返回多于一行•使用多行比较符操作含义IN等于列表中的任何成员ANY比较子查询返回的每个值ALL比较子查询返回的全部值 在多行子查询中使用ANY运算符SELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary"IT_PROG"; 在多行子查询中使用ALL运算符SELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary"IT_PROG"; 数据操纵语言•数据操纵语言(DatamanipulationlanguageDML)是SQL的一个核心部分•当你做下面操作时，DML语句被执行：–添加新行到表中–修改表中的行–删除表中的行•事务由DML语句的集合组成，它组成工作的逻辑单元 INSERT语句语法•使用INSERT语句添加新行到表中INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);在语法中：table是表的名字column是表中的列名value是列的相应值•用该语法一次只能插入一行 插入新行•插入一个包含每一个列值的新行•值以表中列的默认顺序列表•在INSERT子句中字段可以随意列表INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,"PublicRelations",100,1700);•字符和日期要用单引号括起来 插入带空值的行•隐式方法:省略字段列表中的列INSERTINTOdepartments(department_id,epartment_name)VALUES(30,"Purchasing");•显式方法:在VALUES子句中指定NULL关键字INSERTINTOdepartmentsVALUES(100,"Finance",NULL,NULL); 使用替换变量•在SQL语句中用&替换变量提示用户输入值•&是一个用于变量值的占位符INSERTINTOdepartments(department_id,department_name,location_id)VALUES(&department_id,"&department_name",&location); 从另一个表中复制行•用一个子查询写INSERT语句INSERTINTOsales_reps(id,name,salary,commission_pct)SELECTemployee_id,last_name,salary,commission_pctFROMemployeesWHEREjob_idLIKE"%REP%";•不用VALUES子句•在子查询中列的数目要匹配INSERT子句中列的数目 在INSERT语句中使用子查询INSERTINTO(SELECTemployee_id,last_name,email,hire_date,job_id,salary,department_idFROMemployeesWHEREdepartment_id=50)VALUES(99999,"Taylor","DTAYLOR",TO_DATE("07-JUN-99","DD-MON-RR"),"ST_CLERK",5000,50); 改变表中的数据•用UPDATE语句修改已存在的行UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];table是表的名字column是表中列的名字value是相应的值或对应列的子查询condition确定要被更新的行，由列名、表达式、常数和比较操作符组成。用查询表来显示受更新的行以确认更新操作。•如果需要，一次更新多行 更新表中的行•如果使用了WHERE子句，指定的一行或多行将被修改UPDATEemployeesSETdepartment_id=70WHEREemployee_id=113;•如果遗漏WHERE子句，表中所有的行都会被修改UPDATEcopy_empSETdepartment_id=110; 用子查询更新两列更新雇员114的工作和薪水，使其和雇员205相同UPDATEemployeesSETjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=205),salary=(SELECTsalaryFROMemployeesWHEREemployee_id=205)WHEREemployee_id=114; 更新基于另一个表的行在UPDATE语句中用子查询来更新基于另一个表中值的那些行。UPDATEcopy_empSETdepartment_id=(SELECTdepartment_idFROMemployeesWHEREemployee_id=100)WHEREjob_id=any(SELECTjob_idFROMemployeesWHEREemployee_id=200); 从表中删除行使用DELETE语句从表中删除已存在的行•如果指定了WHERE子句，则指定的行被删除DELETEFROMdepartmentsWHEREdepartment_name="Finance";•如果遗漏了WHERE子句，表中所有的行都被删除DELETEFROMcopy_emp;注：删除所有行后，表的数据结构被保留。建议的方法是：Truncatetablecopy_emp; 删除基于另一个表的行在DELETE语句中用子查询来删除表中的基于另一个表中值的行。DELETEFROMemployeesWHEREdepartment_id=any(SELECTdepartment_idFROMdepartmentsWHEREdepartment_nameLIKE"%Public%"); MERGE语句•提供有条件地更新和插入数据到数据库表中的能力•如果行存在，执行UPDATE；如果是一个新行，执行INSERT：–避免分散更新–增进性能和易用性–在数据仓库应用中有用 MERGE语句的语法你能够用MERGE语句有条件地插入或更新表中的行MERGEINTOtable_nametablealiasUSING(table|view|sub_query)aliasON(joincondition)WHENMATCHEDTHENUPDATESETcol1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values); MERGE语句的语法（续）•INTO子句指定你正在更新或插入的目的表•USING子句指定数据源要被更新或插入的数据的源；可以是一个表、视图或者子查询。•ON子句是一个条件，在此条件上MERGE操作即可以更新也可以插入•WHENMATCHED|通知服务器怎样响应连接条件的结果 MERGE语句的例子MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESETc.first_name=e.first_name,c.last_name=e.last_name,c.email=e.email,c.phone_number=e.phone_number,c.hire_date=e.hire_date,c.job_id=e.job_id,c.salary=e.salary,c.commission_pct=e.commission_pct,c.manager_id=e.manager_id,c.department_id=e.department_idWHENNOTMATCHEDTHENINSERTVALUES(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,e.commission_pct,e.manager_id,e.department_id); 数据库事务处理Oracle服务器基于事务处理确保数据的一致性。在改变数据时，事务给你更多的灵活性和可控性，如果用户程序失败或者系统失败，事务可以确保数据的一致性。数据库事务处理由下面的语句组成：•（数据操纵语言）DML语句，对数据进行永久的改变•（数据定义语言）DDL语句•（数据控制语言）DCL语句 数据库事务处理（续）•执行第一个DMLSQL语句时开始•遇到下面事件之一结束：–一个COMMIT或ROLLBACK语句被发布–一个DDL或DCL语句执行(自动提交)–用户退出iSQL*Plus–系统崩溃 COMMIT和ROLLBACK语句优点用COMMIT和ROLLBACK语句，你能够：•确保数据的一致性•在数据永久改变之前进行预览•分组逻辑相关的操作 控制事务你能够用COMMIT、SAVEPOINT和ROLLBACK语句控制事务逻辑。COMMIT结束当前事务，使得所有未决的数据永久改变。SAVEPOINTname在当前事务中标记保存点。ROLLBACK结束当前事务，丢弃所有未决的数据改变。ROLLBACKTOSAVEPOINTname回滚当前事务到指定的保存点，从而丢弃保存点创建后的任何改变。如果忽略了TOSAVEPOINT子句，ROLLBACK语句回滚整个事务。由于保存点是逻辑的，因此，没有办法列出已经创建的保存点。 回退改变到一个标记•用SAVEPOINT语句在当前事务中创建一个标记•用ROLLBACKTOSAVEPOINT语句回退到该标记UPDATE...SAVEPOINTupdate_done;Savepointcreated.INSERT...ROLLBACKTOupdate_done;Rollbackcomplete. 隐式事务处理•在下面的情况下，一个自动提交发生：–DDL语句被发送–DCL语句被发送–正常退出iSQL*Plus，没有明确地发送COMMIT或ROLLBACK语句•当iSQL*Plus非正常退出时，或者发生系统故障时，一个自动回退发生 COMMIT或ROLLBACK之前数据的状态•以前的数据状态能够被恢复•当前用户能用SELECT语句查看DML操作的结果•其他用户不能观察当前用户DML语句的结果•受影响的行被锁定，其他用户不能改变受影响的行中数据。 在COMMIT之后数据的状态•数据在数据库中被永久地改变•数据的以前状态被永久地丢失•所有用户都能观察该结果•受影响行的锁定被释放，其它用户可以操纵那些行•所有保存点被擦除 提交改变•产生改变DELETEFROMemployeesWHEREemployee_id=99999;INSERTINTOdepartmentsVALUES(290,"CorporateTax",NULL,1700);•提交改变COMMIT; ROLLBACK后的数据状态用ROLLBACK语句丢弃所有未决的改变：•数据的改变被撤消•数据的以前状态被恢复•受影响行的锁定被释放DELETEFROMcopy_emp;ROLLBACK; 读一致性目的是确保每个用户看到的数据和他最后一次提交，并且在一个DML操作开始之前的数据一样。•读一致性在所有时间保证对数据的一致的观察•一个用户所做的改变不与另一个用户所做的改变冲突•读一致性确保下面的操作有同样的数据：–读者不等待写者–写者不等待读者•数据库用户用两种方法访问数据库：读操作(SELECT语句)写操作(插入、更新、删除语句) 锁定锁是防止访问相同资源的事务之间的破坏性交互的机制。既可以是用户对象(例如表或行)，也可以是对用户不可见的系统对象(例如共享数据结构和数据字典行)。在Oracle数据库中，锁：•在并发事务之间防止破坏性的交互作用•不需要用户的动作•自动使用最低的限制级别•在事务处理期间保持•有两种类型：显式锁定和隐式锁定 隐式锁定•两种锁模式：–独占锁：不允许其他用户访问–共享所：允许其他用户访问•高级数据并发操作：–DML:表共享，行独占–查询:不需要锁–DDL:保护定义对象•锁保持直到commit或rollback 内置约束 目标完成本课后,您应当能够执行下列操作：•描述约束•创建和维护约束 什么是约束?•约束强制规则在表级•如果有从属关系，约束防止表的删除•下面的约束类型是有效的：–NOTNULL–UNIQUE–PRIMARYKEY–FOREIGNKEY–CHECK•Oracle服务器用约束(constraints)来防止无效数据输入到表中。•你可以使用约束做下面的事：在插入、更新行或者从表中删除行的时候强制表中的数据遵循规则。对于成功的操作，约束必须被满足。如果表之间有依赖关系，防止表的删除。 数据一致性约束约束说明NOTNULL指定列不能包含空值UNIQUE指定列的值或者列的组合的值对于表中所有的行必须是唯一的PRIMARYKEY表的每行的唯一性标识FOREIGNKEY在列和引用表的一个列之间建立并且强制一个外键关系CHECK指定一个必须为真的条件 约束原则•命名一个约束，或者由Oracle用SYS_Cn格式产生一个名字•创建一个约束：–在创建表的同时–在创建表之后•在列或者表级定义一个约束•在数据字典中查看约束 定义约束CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][column_constraint],...[table_constraint][,...]);CREATETABLEemployees(employee_idNUMBER(6),first_nameVARCHAR2(20),...job_idVARCHAR2(10)NOTNULL,CONSTRAINTemp_emp_id_pkPRIMARYKEY(EMPLOYEE_ID));在语法中：schema与所有者同名table表的名字DEFAULTexpr指定一个默认值。如果在插入语句中省略了一个值，在省略处使用该默认值column列的名字datatype列的数据类型和长度column_constraint是一个作为列定义一部分的完整性约束table_constraint是一个作为表定义一部分的完整性约束 定义约束（续）•列级约束：只涉及一个单个的列，对于该列用规范定义；能够定义完整性约束的任何类型column[CONSTRAINTconstraint_name]constraint_type,•表级约束涉及一个或多个列，表中的列被分别定义；除了NOTNULL，能够定义任意约束column,...[CONSTRAINTconstraint_name]constraint_type(column,...), NOTNULL约束确保某些列不允许空值：定义在列级CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATECONSTRAINTemp_hire_date_nnNOTNULL);由系统指定约束名字由用户指定约束名字 UNIQUE约束UNIQUE键完整性约束，要求列或者列的组合中(键)的每个值是唯一的，既在表中指定的列或列组合中不能有两行有相同的值。定义UNIQUE键约束的列(或列组合)被称为唯一键(uniquekey)。UNIQUE约束既可以定义在表级也可以定义在列级：CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,...CONSTRAINTemp_email_ukUNIQUE(email)); PRIMARYKEY约束PRIMARYKEY约束为表创建一个主键。每个表只能创建一个主键。PRIMARYKEY约束是表中的对行唯一标识的一个列或者列组合，该约束强制列或列组合的唯一性，并且确保作为主键一部分的列不能包含空值。既可以定义在表级也可以定义在列级：CREATETABLEdepartments(department_idNUMBER(4),department_nameVARCHAR2(30)CONSTRAINTdept_name_nnNOTNULL,manager_idNUMBER(6),location_idNUMBER(4),CONSTRAINTdept_id_pkPRIMARYKEY(department_id)); FOREIGNKEY约束DEPARTMENTSEMPLOYEESPRIMARYKEYFOREIGNKEYINSERTINTO不允许（9不存在）允许 FOREIGNKEY约束（语法）既可以定义在表级也可以定义在列级：CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,...department_idNUMBER(4),CONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id),CONSTRAINTemp_email_ukUNIQUE(email)); FOREIGNKEY约束(续)外键被定义在子表中，包含引用列的表是父表。外键用下面关键字的组合定义：FOREIGNKEY被用于在表约束级定义子表中的列。REFERENCES确定父表中的表和列。ONDELETECASCADE指出当父表中的行被删除时，子表中相依赖的行也将被级联删除。ONDELETESETNULL当父表的值被删除时，转换外键值为空。默认行为被称为约束规则，该规则不允许引用数据的更新或删除。无ONDELETECASCADE或ONDELETESETNULL选项，如果父表中的行在子表中引用，则它不能被删除 CHECK约束•定义每行必须满足的条件•CHECK约束能够被定义在列级或表级。...,salaryNUMBER(2)CONSTRAINTemp_salary_minCHECK(salary>0),... 添加约束语法用ALTERTABLE语句：•添加或删除约束，但不修改它的结构•启用或禁用约束•用MODIFY子句添加一个NOTNULL约束ALTERTABLEtableADD[CONSTRAINTconstraint]type(column);table是表的名字constraint是约束的名字type是约束的类型column是受约束影响的列的名字 删除约束•从EMPLOYEES表中删除经理约束ALTERTABLEemployeesDROPCONSTRAINTemp_manager_fk;•删除DEPARTMENTS表上的PRIMARYKEY约束，并且删除相关联的在EMPLOYEES.DEPARTMENT_ID列上的FOREIGNKEY约束。ALTERTABLEdepartmentsDROPPRIMARYKEYCASCADE; 禁用约束•执行ALTERTABLE语句的DISABLE子句来禁用完整性约束。•应用CASCADE选项禁用相依赖的完整性约束ALTERTABLEemployeesDISABLECONSTRAINTemp_emp_id_pkCASCADE;原则你即可以在CREATETABLE语句也可以在ALTERTABLE语句中使用DISABLE子句。CASCADE子句禁用相依赖的完整性约束。禁用唯一或主键约束会移除唯一性索引。 启用约束•用ENABLE字句启用一个在表中定义的当前禁用的完整性约束。ALTERTABLEemployeesENABLECONSTRAINTemp_emp_id_pk;•如果启用一个UNIQUE键或PRIMARYKEY约束一个UNIQUE或PRIMARYKEY索引被自动创建。原则：如果启用一个约束，约束将应用于表中所有的数据，所有在表中的数据都必须适合该约束。如果你启用一个UNIQUE键或者PRIMARYKEY约束，一个UNIQUE或PRIMARYKEY索引将被自动地创建。你即可以CREATETABLE语句也可以在ALTERTABLE语句中使用ENABLE子句。启用一个带CASCADE选项的被禁用的主键约束不会起用任何依赖于该主键的外键。 查看约束查询USER_CONSTRAINTS表来查看所有约束定义和命名。SELECTconstraint_name,constraint_type,search_conditionFROMuser_constraintsWHEREtable_name="EMPLOYEES";注：那些没有被表的所有者命名的约束将收到系统指定的约束名。在约束类型中，C代表CHECK，P代表PRIMARYKEY，R代表引用完整性，U代表UNIQUE键。注意NOTNULL约束实际上是一个CHECK约束。 查看约束关联的列观察在USER_CONS_COLUMNS视图中与约束名关联的列SELECTconstraint_name,column_nameFROMuser_cons_columnsWHEREtable_name="EMPLOYEES"; 创建视图 目标完成本课后,您应当能够执行下列操作：•描述视图•创建视图，修改视图的定义，删除视图•通过视图取回数据•通过视图插入、更新和删除数据•创建和使用内嵌视图•执行“Top-N”分析 数据库对象对象说明Table(表)基本存储单元，由行和列组成View(视图)数据来自一个或者多个表的数据子集的逻辑表示Sequence(序列)产生主键的值Index(索引)改善某些查询的性能Synonym(同义词)一个对象的替换名字 什么是视图?你可以通过创建表的视图来表现数据的逻辑子集或数据的组合。视图是基于表或另一个视图的逻辑表，一个视图并不包含它自己的数据，它象一个窗口，通过该窗口可以查看或改变表中的数据。视图基于其上的表称为基表。视图在数据字典中作为一个SELECT语句存储。为什么用视图?•限制数据访问•使得复杂的查询容易•提供数据的独立性•表现相同数据的不同观察 简单视图和复杂视图下面是简单视图：数据仅来自一个表不包含函数或数据分组能通过视图执行DML操作下面是复杂视图：数据来自多个表包含函数或数据分组不总是允许通过视图进行DML操作 创建视图创建一个视图，EMPVU80，其中包含了在部门80中雇员的详细信息。CREATEVIEWempvu80ASSELECTemployee_id,last_name,salaryFROMemployeesWHEREdepartment_id=80;查看视图的结构DESCRIBEempvu80 创建视图•用子查询中的列别名创建视图CREATEVIEWsalvu50ASSELECTemployee_idID_NUMBER,last_nameNAME,salary*12ANN_SALARYFROMemployeesWHEREdepartment_id=50;或CREATEVIEWsalvu50(ID_NUMBER,NAME,ANN_SALARY)ASSELECTemployee_id,last_name,salary*12FROMemployeesWHEREdepartment_id=50;•从该视图中选择列，视图中的列使用别名命名DESCRIBESQLVU50 查询视图•一旦视图被创建，你就可以查询数据字典视图USER_VIEWS来看视图的名字和视图定义。构成视图的SELECT语句的文本被存储在一个LONG列中。•用视图存取数据当你用视图存取数据时，Oracle服务器执行下面的操作：从数据字典表USER_VIEWS中取回视图定义。检查对视图的基表的数据存取权限。转换视图查询为一个在基表或表上的等价操作，换句话说，数据从基表得到，或更新基表。 修改视图•用CREATEORREPLACEVIEW子句，为每个列添加一个别名CREATEORREPLACEVIEWempvu80(id_number,name,sal,department_id)ASSELECTemployee_id,first_name||""||last_name,salary,department_idFROMemployeesWHEREdepartment_id=80;•在CREATEVIEW子句中的字段别名列表，按照与子查询中的字段相同的顺序排列 创建复杂视图创建包含组函数的复杂视图，以从两个表中显示值CREATEVIEWdept_sum_vu(name,minsal,maxsal,avgsal)ASSELECTd.department_name,MIN(e.salary),MAX(e.salary),AVG(e.salary)FROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYd.department_name; 视图中DML操作的执行规则•只能在简单视图上执行DML操作•如果视图中包含下面的部分就不能删除行：–组函数–GROUPBY子句–DISTINCT关键字–伪列ROWNUM关键字 视图中DML操作的执行规则如果视图中包含下面的部分就不能修改数据：•组函数•GROUPBY子句•DISTINCT关键字•伪列ROWNUM关键字•用表达式定义的列 WITHCHECKOPTION子句•你可以确保DML操作在视图上被执行，用WITHCHECKOPTION子句检查视图中的域。CREATEORREPLACEVIEWempvu20ASSELECT*FROMemployeesWHEREdepartment_id=20WITHCHECKOPTIONCONSTRAINTempvu20_ck;•任何改变视图的任意行中部门号的企图都会失败，因为它违反了WITHCHECKOPTION约束。可以通过视图执行引用完整性检查，你也可以在数据库级别强制约束。•添加WITHREADONLY选项到视图定义中，能够确保无DML操作发生。 删除视图删除视图不会丢失数据，因为视图是基于数据库中的基本表的。DROPVIEWempvu80; 内建视图•内建视图是一个带有别名(或相关名)的可以在SQL语句中使用的子查询。•一个主查询的在FROM子句中指定的子查询就是一个内建视图的离子。•内建子查询不是方案对象SELECTa.last_name,a.salary,a.department_id,b.maxsalFROMemployeesa,(SELECTdepartment_id,max(salary)maxsalFROMemployeesGROUPBYdepartment_id)bWHEREa.department_id=b.department_idANDa.salary