Oracle数据库培训-SQL基础篇

1、SQL 语言基础主要内容容基本的SELECT语句约束和排排序数据据单行函数数多表显示示数据、组函数数合计数数据创建和管管理表子查询&操纵数数据内置约束束创建视图图其他数据据库对象象基本的select语语句目标完成本课课后,您您应当当能够：列出SQLSELECT语句的功功能执行基基本的SELECT语句SQLSELECT语句的功功能列选择：你能够够使用SELECT语语句的列列选择功功能选择择表中的的列，这这些列是是你想要要用查询询返回的的。当你你查询时时，你能能够选择择你查询询的表中中指定的的列。行选择：你能够够使用SELECT语语句的行行选择功功能选择择表中的的行，这这些行是是你想要要用查询询返

2、回的的。你能能够使用用不同的的标准限限制你看看见的行行。连接：你你能够使使用SELECT语句句的连接接功能来来集合数数据，这这些数据据被存储储在不同同的表中中，在它它们之间间可以创创建连接接。在后后面的课课程中你你将学到到更多关关于连接接的内容容。基本SELECT语句SELECT*|DISTINCTcolumn|expressionalias,.FROMtable;SELECT 确定定哪些列列FROM确确定哪张张表选择所有有列与指指定列SELECT*FROM departments;用跟在SELECT关关键字后后面的星星号(*)，你能够够显示表表中数据据的所有有列。SELECTdepartme

3、nt_id,location_idFROM departments;你能够用用SELECT语句来来显示表表的指定定列，指指定列名名之间用用逗号分分隔。写SQL 语句句SQL 语句句对大小小写不敏敏感SQL 语句句可以写写成一行行或多行行关键字字不能简简写或分分开折行行子句通通常放在在不同的的行缩进用用于增强强可读性性算术表达达式用算术运运算符创创建数字字和日期期数据的的表达式式操作说说明+加-减*乘/除 使用算术术运算符符SELECTlast_name,salary, salary+ 300FROM employees;优先级:乘法和和除法比比加法和和减法的的优先级级高相同优优先级的的运算符符

4、从左到到右计算算圆括号号用于强强制优先先计算，并且使使语句更更 清晰晰SELECTlast_name,salary,12*salary+100FROM employees;空值null是是一个未未分配的的、未知知的，或或不适用用的值null不是是0，也也不是空空格包含空空值的算算术表达达式计算算结果为为空SELECTlast_name,job_id, salary,commission_pctFROM employees;定义列别别名列别名：改变列列标题的的名字可用于于计算结结果紧跟在在列名后后面在在列名和和别名之之间可以以有选项项AS关关键字字如果别别名中包包含有空空格、或或者特殊殊字符、或

5、者大大小写敏敏感，要要求用双双引号SELECTlast_name AS name, commission_pct commFROMemployees;连字运算算符连字运算算符：连接列列或者字字符串到到其它的的列用两个个竖线表表示(|)构造一一个字符符表达式式的合成成列SELECTfirst_name|last_name AS EmployeesFROM employees;文字字符符串文字字字符串是是包含在在SELECT列表中的的一个字字符串，一个数数字或者者一个日日期日期和和字符的的文字字字符串值值必须用用单引号号括起来来每个文文字字符符串在每每行输出出一次SELECTlast_name|

6、is a|job_idASEmployeeDetails”FROM employees;约束和排排序数据据目标完成本课课后,您您应当当能够执执行下列列操作：用一个个查询限限制返回回的行用一个个查询分分类返回回的行限制选择择的行用WHERE子句限制制返回的的行SELECT*|DISTINCTcolumn|expressionalias,.FROMtableWHEREcondition(s);WHERE子句句跟着FROM子句WHERE限限制查询询满足条条件的行行condition由列名、表达式式、常数数和比较较操作组组成WHERE子句句能够比比较列值值、文字字值、算算术表达达式或者者函数，WHER

7、E子句由由三个元元素组成成：列名， 比较较条件，列列名、常常量或值值列表。使用WHERE子句SELECTemployee_id, last_name,job_id,department_idFROM employeesWHEREdepartment_id =90;字符串和和日期字符串串和日期期的值放放在单引引号中字符值值区分大大小写，日期值值是格式式敏感的的日期的的默认格格式是DD-MON-RR.SELECTlast_name,job_id, department_idFROM employeesWHERElast_name=Whalen;比较条件件运算含含义=等于大于=大于等于于小于=小于等

8、于于不等于比较条件件被用于于一个表表达式与与一个值值或与另另一个表表达式的的比较。.WHEREhire_date=01-JAN-95.WHEREsalary=6000.WHERElast_name=Smith其它比较较条件操作含含义义BETWEEN.AND.在两个值值之间(包含)IN(set)匹配一个个任意值值列表LIKE匹配一个个字符模模板ISNULL是一个空空值使用BETWEEN条条件:SELECTlast_name,salaryFROM employeesWHEREsalaryBETWEEN2500 AND3500;使用IN条件SELECTemployee_id, last_name,s

9、alary,manager_idFROM employeesWHEREmanager_id IN (100, 101,201);其它比较较条件使用LIKE条条件使用LIKE条件执行行有效搜搜索串值值的通配配符搜索索搜索条条件既可可以包含含文字也也可以包包含数字字：%表示任意意顺序的的零个或或多个字字符_表示一个个字符SELECTfirst_nameFROM employeesWHEREfirst_name LIKES%;使用NULL条条件用IS NULL操操作来测测试空值值SELECTlast_name,manager_idFROM employeesWHEREmanager_id IS NU

10、LL;NULL条件，包括ISNULL条件和和ISNOT NULL条条件。逻辑条件件运算含含义AND如果两个个组成部部分的条条件都为为真，返返回TRUEOR如果两个个组成部部分中的的任一个个条件为为真，返返回TRUENOT如果跟随随的条件件为假，返回TRUE可以在WHERE子句句中用AND和和OR运运算符使使用多个个条件。使用AND操作作：AND要求两两个条件件同时为为真SELECTemployee_id, last_name,job_id,salaryFROM employeesWHEREsalary=10000ANDjob_id LIKE%MAN%;逻辑条件件使用OR操作：OR操作要求求两者

11、之之一为真真即可SELECTemployee_id, last_name,job_id,salaryFROM employeesWHEREsalary=10000ORjob_id LIKE%MAN%;使用NOT操作SELECTlast_name,job_idFROM employeesWHEREjob_idNOTIN(IT_PROG,ST_CLERK,SA_REP);注：NOT运算算符也可可以用于于另一个个SQL运算符符，例如如，BETWEEN、LIKE、和和NULL。优先规则则求值顺序序1算术运算算2连字操作作3比较操作作4ISNOTNULL,LIKE,NOTIN5NOT BETWEEN6N

12、OT逻辑条件件7AND逻辑条件件8OR逻辑条件件使用圆括括号改变变优先规规则 ORDERBY子句用ORDERBY子句排序序行ASC:升升序排序序，默认认DESC:降降序排排序ORDERBY子句在SELECT语句的最最后SELECTlast_name,job_dateFROM employeesORDERBYhire_date;语法:SELECTexprFROMtableWHEREcondition(s)ORDERBYcolumn,expr ASC|DESC;单行函数数目标完成本课课后，您您应当能能够执行行下列操操作：描述在在SQL 中可可用的函函数的变变量类型型在SELECT语句中使使用字符符

13、，数字字和日期期函数描述转转换函数数的使用用SQL函数函数是SQL的的一个非非常强有有力的特特性，函函数能够够用于下下面的目目的：执行数据据计算修改单个个数据项项操纵输出出进行行行分组格式化显显示的日日期和数数字转换列数数据类型型SQL函函数有输输入参数数，并且且总有一一个返回回值。注：在本本课中讲讲述的大大多数函函数是针针对SQL的Oracle版版的。SQL函数(续)有两种截截然不同同的函数数：单行函数数多行函数数单行函数数这些函数数仅对单单个行进进行运算算，并且且每行返返回一个个结果。有不同同类型的的单行函函数，本本课下面面的函数数类型：字符 数字 日期 转换 多行函数数这些函数数能够操操

14、纵成组组的行，每个行行组给出出一个结结果，这这些函数数也被称称为组函函数。多多行函数数在后面面的课程程中介绍绍。单行函数数单行函数数：操纵数数据项接受多多个参数数并返回回一个值值作用于于每一个个返回行行每行返返回一个个结果可以修修改数据据类型可以嵌嵌套接受多多个参数数，参数数可以是是一个列列或者一一个表达达式单行函数数（续）单行函数数的特性性包括：作用于查查询中返返回的每每一行每行返回回一个结结果可能返回回一个与与参数不不同类型型的数据据值可能需要要一个或或多个参参数能够用在在SELECT、WHERE和ORDER BY子句中中，可以以嵌套。function_name(arg1,arg2,.)f

15、unction_name是函数的的名字。arg1,arg2是由函数数使用的的任意参参数，可可以由一一个列名名或者一一个表达达式提供供。单行函数数（续）本课包括括下面的的单行函函数：字符函数数：接受受字符输输入，可可以返回回字符或或者数字字值数字函数数：接受受数字输输入，返返回数字字值日期函数数：对DATE数据类类型的值值进行运运算(除了MONTHS_BETWEEN函数数返回一一个数字字，所有有日期函函数都返返回一个个DATE数据据类型的的值。)转换函数数：从一一个数据据类型到到另一个个数据类类型转换换一个值值通用函数数：NVL、NVL2、NULLIF、 COALSECE、CASE 、DECOD

16、E字符函数数单行字符符函数接接受字符符数据作作为输入入，既可可以返回回字符值值也可以返回回数字值值。字符符函数可可以被分分为下面面两种：大小写处处理函数数字符处理理函数大小写处处理函数数如下：LOWER(column|expression)转转换字字符值为为小写UPPER(column|expression)转转换字字符值为为大写INITCAP(column|expression) 转换换每个单单词的首首字母值值为大写写，所有有其它值为为小写字符处理理函数如如下：CONCAT(column1|expression1 ,column2|expression2)连接第一一个字符符值到第第二个字字符

17、值；等价于于连接运运算符(|)SUBSTR(column|expression,m ,n)从字符值值中返回回指定的的字符，开始位位置在m，n字符长度度 (如如果m是负数，计数从从字符值值末尾开开始；如如果n被忽略，返回到到串结束束的所有有字符)。LENGTH(column|expression) 返回回表达式式中的字字符数INSTR(column|expression,string, ,m, n)返回一个个命名串串的数字字位置。随意地地，你可可以提供供一个位位置m作为查找找的开始始，在字字符串中中第n次发现的的位置。m和n的默认值值是1，意味着着在起始始开始查查找，并并且报告告第一个个发现的的

18、位置。LPAD(column|expression,n,string)RPAD(column|expression,n,string)填充字符符值左、右调节节到n字字符位置置的总宽宽度TRIM(leading|trailing|both,trim_characterFROM trim_source)使你能够够从一个个字符串串修整头头或尾字字符(或或两者)。如果果trim_character或trim_source是字符文文字，你你必须放放在单引引号中。REPLACE(text,search_string,replacement_string)从字符串串查找一一个文本本表达式式，如果果找到，用指

19、定定的值串串代替它它字符函数数（续）大小写处处理函数数这些函数数转换字字符串的的大小写写函 数结结果果LOWER(SQL Course)sqlcourseUPPER(SQL Course)SQLCOURSEINITCAP(SQLCourse)SqlCourseLOWER：转转换大小小写混合合的字符符串为小小写字符符串UPPER：转转换大小小写混合合的字符符串为大大写字符符串INITCAP：将每每个单词词的首字字母转换换为大写写，其他他字母为为小写SELECTThejobidfor |UPPER(last_name)|is|LOWER(job_id)ASEMPLOYEE DETAILSFROM

20、employees;字符处理理函数函数数结结果果CONCAT(Hello,World)HelloWorldSUBSTR(HelloWorld,1,5)HelloLENGTH(HelloWorld)10INSTR(HelloWorld, W)6LPAD(salary,10,*)*24000RPAD(salary,10, *)24000*TRIM(HFROM HelloWorld)elloWorld数字函数数ROUND:四舍五入入指定小小数的值值ROUND(45.926, 2)45.93TRUNC(45.926, 2)45.92MOD(1600,300)100ROUND(column|expres

21、sion,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)FROM DUAL;使用TRUNC函数SELECTTRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROMDUAL;使用MOD函数

22、SELECTlast_name,salary, MOD(salary, 5000) FROMemployees WHERE job_id= SA_REP;注：MOD函数数经常用用于确定定一个值值是奇数数还是偶偶数日期的使使用Oracle 数据据库用内内部数字字格式存存储日期期：世纪纪，年，月，日日，小时时，分钟钟和秒默认日日期显示示格式是是DD-MON-RR.SELECTlast_name,hire_dateFROM employeesWHERElast_namelikeG%;SYSDATE函数返回回：DateTimeSYSDATE是一个个日期函函数，它它返回当当前数据据库服务务器的日日期和时

23、时间。SELECTSYSDATEFROMDUAL;用日期计计算从日期期加或者者减一个个数，结结果是一一个日期期值两个日日期相减减，得到到两个日日期之间间的天数数用小时时数除以以24，可以加加小时到到日期上上既然数据据库以数数字方式式存储日日期，你你就可以以用算术术运算符符进行计计算，例例如，加加或减。你可以以加或减减数字常常数以及及日期。你可以进进行下面面的运算算：运算结结果说说明date +number日日期加加一一个天数数到一个个日期上上date -number日日期从从一一个日期期上减一一个天数数date -date天天数用用一个日日期减另另一个日日期date +number/24日日期

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的值必须须是

25、整数数，但可可以是负负的。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被截断到到最近的的天

26、。使用日期期函数MONTHS_BETWEEN(01-SEP-95,11-JAN-94)19.6774194ADD_MONTHS (11-JAN-94,6)11-JUL-94NEXT_DAY(01-SEP-95,2)下个星期期五是几几号08-SEP-95LAST_DAY(01-FEB-95)28-FEB-95使用日期期函数（续）假定SYSDATE=25-JUL-95:ROUND(SYSDATE,MONTH)01-AUG-95ROUND(SYSDATE ,YEAR)01-JAN-96TRUNC(SYSDATE ,MONTH)01-JUL-95TRUNC(SYSDATE ,YEAR)01-JAN-9

27、5TRUNC(TO_DATE(25-JUL-95),YEAR)01-JAN-95转换函数数数据类型型转换隐式数数据类型型转换显式数数据类型型转换对于直接接赋值，Oracle 服务务器能够够自动地地进行下下面的转换：从到到VARCHAR2orCHARNUMBERVARCHAR2orCHARDATENUMBERVARCHAR2DATEVARCHAR2对于表达达式赋值值，Oracle服服务器能能自动地地进行下下面的转转换：从 VARCHAR2 or CHAR到DATE从 VARCHAR2 or CHAR到NUMBER转换函数数(续）显式数据据类型转转换SQL提提供三三种函数数来从一一种数据据类型转转

28、换值到到另一种种：TO_CHAR(number|date,fmt,nlsparams)转换一个个数字或或日期值值为一个个VARCHAR2字字符串，带格式式化样式fmt。数字转换换：nlsparams参参数指定定下面的的字符，它由数数字格式式化元素返返回：小数字符符99999.99前导009999本地货币币符号L9999国际货币币符号$9999如果忽略略nlsparams或其它它参数，该函数数在会话话中使用用默认参参数值。TO_CHAR(number|date,fmt,nlsparams)指定返回回的月和和日名字字及其缩缩写的语语言。如如果忽略略该参数数，该函数在在会话中中使用默默认日期期语言。

29、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)格式模板板必须加加单引号号，

30、并且且区分大大小写能够包包含任一一有效的的日期格格式元素素有一个个fm元素用来来删除填填补的空空，或者者前导零零用一个个逗号与与日期值值分开SELECTemployee_id,TO_CHAR(hire_date,MM/YY)Month_HiredFROM employeesWHERElast_name=Higgins;日期格式式模板的的元素YYYY数字全写写年YEAR年的拼写写MM月的两数数字值MONTH月的全名名DY周中天的的三字母母缩写DAY周中天的的全名MON月的三字字母缩写写DD月的数字字天使用TO_NUMBER和TO_DATE函数转换字字符串到到数字，用TO_NUMBER函数格式式化

31、：TO_NUMBER(char,format_model)转换字字符串到到日期，用TO_DATE函数格式式化：TO_DATE(char,format_model)Selectto_number(12345)from dual;Selectto_date(20000810,yyyy-mm-ddfromdual;通用函数数这些函数数可用于于任意数数据类型型，并且且适用于于空值NVL(expr1,expr2)NVL2 (expr1,expr2, expr3)NULLIF(expr1,expr2)COALESCE(expr1, expr2,., exprn)NVL转转换空空值为一一个实际际值NVL2如

32、如果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是包含空空

33、值的源源值或者者表达式式expr2是用于转转换空值值的目的的值Selectnvl(1,notnullfrom dual;注:如果果expr1为为空则返返回expr2r的值值使用NVL2函数NVL2 函数数检查第第一个表表达式，如果第第一个表表达式不不为空，那么NVL2 函数数返回第第二个表表达式；如果第第一个表表达式为为空，那么么第三个个表达式式被返回回。expr2expr1非空时的的返回值值语法 NVL2(expr1,expr2,expr3)在语法中中：expr1是可能包包含空的的源值或或表达式式expr3expr1为空时的的返回值值SelectNVL2(1,notnull,null)fro

34、m dual;SelectNVL2(1,notnull,null)from dual;使用NULLIF函数NULLIF函函数比比较两个个表达式式，如果果相等，函数返返回空，如果不不相等，函数返回回第一个个表达式式。第一一个表达达式不能能为NULL。语法 NULLIF(expr1,expr2)在语法中中：expr1是对于expr2的被比较较原值expr2是对于expr1的被比较较原值。(如果果它不等等于expr1，expr1被返回)。Selectnullif(abc,abcd)fromdual;使用COALESCE函数COALESCE函数超过过NVL函数的优优点是COALESCE函数能够接受受多

35、个交交替的值值。如果第第一个表表达式非非空，它它返回该该表达式式；否则则，它做做一个保留表达达式的结结合。COALESCE函函数返回回列表中中的第一一个非空空表达式式。语法 COALESCE(expr1,expr2,. exprn)在语法中中：expr1如果它非非空，返返回该表表达式expr2如果第一一个表达达式为空空并且该该表达式式非空，返回该该表达式式exprn如果前面面的表达达式都为为空，返返回该表表达式Selectcoalesce(,bca)fromdual;条件表达达式在SQL 语句句中提供供IF-THEN-ELSE 逻辑辑的使用用。两种用法法：CASE表达式DECODE函数CASE

36、表达式CASEexprWHENcomparison_expr1THENreturn_expr1WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_exprENDDECODE函数DECODE(col|expression,search1,result1, search2, result2,., default)DECODE函函数在在比较表表达式(expression)和每个查查找(search)值后解码码表达式式，如果果表达式式与查找找相同，返回结果。如果省略略默认值值，当没没有查找找值

37、与表表达式相相匹配时时返回一个空值值。多表显示示数据&组函数数合计数数据目标完成本课课后,您您应当当能够执执行下列列操作：写SELECT语语句使用用等值和和非等值值连接从多个表表中访问问数据使用外外连接查查看不满满足连接接条件的的数据识别可可用的组组函数描述组组函数的的使用用GROUP BY 子句句分组数数据用HAVING子子句包含含或排除除分组的的行笛卡尔乘乘积笛卡尔尔乘积的的形成，当：一个连连接条件件被遗漏漏时一个连连接条件件不正确确时在第一一个表中中的所有有行被连连接到第第二个表表的所有有行时为了避避免笛卡卡尔乘积积的形成成，在WHERE子子句中应应当总是是包含正确的的连接条条件。用Or

38、acle 语法法连接表表使用一个个连接从从多个表表中查询询数据SELECTtable1.column,table2.columnFROMtable1, table2WHEREtable1.column1=table2.column2;在WHERE 子句句中写连连接条件件当多个个表中有有相同的的列名时时，将表表名作为为列名的的前缀定定义连接接当数据据从多表表中查询询时，要要使用连连接(join) 条件件。一个个表中的行按照照存在于于相应列列中的公公值被连连接到另另一个表表中的行行，即，通常常所说的的主键和和外键列列。什么是等等值连接接?EMPLOYEESDEPARTMENTS用等值连连接返回回记

39、录SELECTemployees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_idFROM employees,departmentsWHEREemployees.department_id= departments.department_id;使用表别别名使用表表别名简简化查询询使用表表别名改改善性能能SELECTe.employee_id,e.last_name, e.department_id,d.department_id,

40、d.location_idFROM employeese ,departmentsdWHEREe.department_id=d.department_id;原则 表别名最最多可以以有30个个字符，但短一一些更好好。如果在FROM子子句中表表别名被被用于指指定的表表，那么么在整个个 SELECT语语句中都都要使用用表别名名。表别名应应该是有有意义的的。表别名只只对当前前的SELECT语语句有有效。多于两个个表的连连接EMPLOYEESDEPARTMENTSLOCATIONS非等值连连接EMPLOYEESJOB_GRADES用非等值值连接返返回记录录SELECTe.last_name, e.sa

41、lary,j.grade_levelFROM employeese,job_grades jWHEREe.salaryBETWEEN j.lowest_salANDj.highest_sal;外连接DEPARTMENTSEMPLOYEES外连接语语法你可以以用一个个外连接接查看那那些不满满足连接接条件的的行外连接接运算符符是加号号(+)SELECTtable1.column,table2.columnFROMtable1, table2WHEREtable1.column(+)=table2.column;SELECTtable1.column,table2.columnFROMtable1,

42、 table2WHEREtable1.column=table2.column(+);用外连连接返回回不直接接匹配的的记录如果在在连接条条件中使使用外连连接操作作，缺少少的行就就可以被被返回。操作作符是一一个在圆圆括号中中的加号号 (+)，它它被放置置在连接接的缺少信信息的一一侧。为为了使来来自不完完善表的的一行或或多行能能够被连接，该操作作符有产产生一个个或多个个空行的的作用。使用外连连接SELECTe.last_name, e.department_id,d.department_nameFROM employeese,departmentsdWHEREe.department_id(+)

43、 =d.department_id ;什么是组组函数?组函数操操作行集集，给出出每组的的结果EMPLOYEES在EMPLOYEES表中的最最高薪水水组函数的的类型AVG平均值COUNT计数MAX最大值MIN最小值STDDEV标准差SUM合计VARIANCE方差组函数(续)每个函数数接收一一个参数数，下面面的表确确定你可可以在语语法中使使用的选选项：函数数说说明明AVG(DISTINCT|ALLn)n的平均值值，忽略略空值COUNT(*|DISTINCT|ALLexpr)用用* 计数数所有行行，包括括重复和和带空值值的行。expr求除了空空计算MAX(DISTINCT|ALLexpr)expr的

44、最大值值，忽略略空值MIN(DISTINCT|ALLexpr)expr的最小值值，忽略略空值STDDEV(DISTINCT|ALLx)n的标准差差，忽略略空值SUM(DISTINCT|ALLn)合合计计n的值，忽忽略空值值VARIANCE(DISTINCT|ALLx)n的方差，忽略空空值组函数的的语法SELECTcolumn,group_function(column),.FROMtableWHEREconditionGROUPBYcolumnORDERBYcolumn;使用组函函数的原原则DISTINCT使使得函数数只考虑虑不重复复的值；ALL 使得得函数考考虑每个个值，包包括重复复值。默默

45、认值是是 ALL，因此不不需要指指定。用于函数数的参数数的数据据类型可可以是CHAR、VARCHAR2、NUMBER或或DATE。所有组函函数忽略略空值。为了用用一个值值代替空空值，用用 NVL、NVL2 或COALESCE函函数。当使用GROUPBY子子句时时，Oracle服服务器隐隐式以升升序排序序结果集集。为了了覆盖该该默认顺顺序，DESC 可以以被用于于 ORDER BY 子句句。使用AVG、SUM、MIN、MAX函函数你可以使使用AVG和和SUM 用于于数字数数据SELECTAVG(salary), MAX(salary),MIN(salary),SUM(salary)FROM em

46、ployeesWHEREjob_idLIKE%REP%;使用COUNT 函数数COUNT(*)返返回一个个表中的的行数COUNT函函数有三三中格式式：COUNT(*)COUNT(expr)COUNT(DISTINCTexpr)COUNT(*)返返回表中中满足SELECT 语句句标准的的行数，包括重重复行，包括有有空值列列的行。如果WHERE子子句包包括在SELECT 语句句中，COUNT(*)返返回满足足 WHERE 子句句条件的的行数。COUNT(expr) 返回回在列中中的由expr指定的非非空值的的数。COUNT(DISTINCTexpr) 返回回在列中中的由expr指定的唯唯一的非非空

47、值的的数。注：expr为为列名组函数和和Null值值所有组函函数忽略略列中的的空值。在幻灯灯片的例例子中，平均值只只基于表表中的那那些COMMISSION_PCT列列的值有效效的行的的计算。平均值值计算是是用付给给所有雇雇员的总佣佣金除以以接受佣佣金的雇雇员数(4)。SELECTAVG(commission_pct)FROM employees;在组函数数中使用用NVL 函数数NVL函函数强强制组函函数包括括空值。在幻灯灯片的例例子中，平均值值被基于所所有表中中的行来来计算，不管COMMISSION_PCT列列是否为空空。平均均值的计计算是用用付给所所有雇员员的总佣佣金除以以公司的雇员员总数(

48、20)。SELECTAVG(NVL(commission_pct,0)FROM employees;创建数据据组:GROUPBY子子句语法法用GROUPBY子子句划划分表中中的行到到较小的的组中SELECTcolumn,group_function(column)FROMtableWHEREconditionGROUPBYgroup_by_expressionORDERBYcolumn;在语法中中，group_by_expression指定那些些用于将将行分组组的列，这些些列的值值作为行行分组的的依据。使用WHERE子子句，你你可以在在划分行行成组以以前过滤滤行。在 GROUP BY 子句句中

49、必须须包含列列。在 GROUP BY 子句句中你不不能用列列别名。默认情况况下，行行以包含含在GROUPBY列列表中的的字段的的升序排排序。你你可以用用 ORDER BY 子句句覆盖这这个默认认值。如果在SELECT 子句句中包含含了组函函数，就就不能选选择单独独的结果果，除非非单独的的列出现现在GROUPBY子子句中。如果你你未能在在 GROUP BY 子句句中包含含一个字字段列表表，你会会收到一一个错误误信息。约束分组组结果: HAVING子子句用HAVING 子句句约束分分组：1.行被被分组2.应用用组函数数3.匹配配HAVING 子句句的组被被显示SELECTcolumn,group_

50、functionFROMtableWHEREconditionGROUPBYgroup_by_expressionHAVINGgroup_conditionORDERBYcolumn;使用HAVING子子句SELECTdepartment_id,MAX(salary)FROM employeesGROUPBYdepartment_idHAVINGMAX(salary)10000;嵌套组函函数显示最大大平均薪薪水SELECTMAX(AVG(salary)FROM employeesGROUPBYdepartment_id;创建和管管理表目标完成本课课后,您您应当当能够执执行下列列操作：描述主主要

51、数据据库对象象创建表表描述列列定义时时可用的的数据类类型改变表表的定义义删除、改名和和截断表表数据库对对象表基基本存储储单元, 由行行和列组组成视图逻逻辑地从从一个或或多个表表中表示示数据子子集序列数数字值发发生器索引改改善一些些查询的的性能同义词给给对象可可选择的的名字命名规则则表命名和和列命名名：必须以以字母开开始必须是是130个个字符长长度只能包包含AZ,az,09, _, $, 和#同一个个用户所所拥有的的对象之之间不能能重名不能用用Oracle 服务务器的保保留字注：名字字是大小小写不敏敏感的，例如， EMPLOYEES与与eMPloyees或或eMpLOYEES作作为同同一个名名字

52、来处处理。CREATETABLE语句用户必必须有：CREATETABLE权限一个存存储区域域CREATETABLEschema.table(columndatatypeDEFAULTexpr,.);必须指指定：表名列名、列数据据类型和和列的大大小schema与所有者者的名字字一样table表的名字字DEFAULTexpr指定默认认值column列的名字字datatype列的数据据类型和和长度引用另一一个用户户的表表属于于另一个个用户，不在该该用户的的方案中中在那些些表名字字的前面面使用所所有者的的名字作作为前缀如果一个个表不属属于本用用户，那那么，其其所有者者的名字字必须放在在表名的的前面SE

53、LECT*FROM user_b.employees;创建表创建表表CREATETABLEdept(deptnoNUMBER(2),dnameVARCHAR2(14),locVARCHAR2(13);确认表的的创建DESCRIBEdeptOracle数数据库库中的表表用户表表：由用户户创建和和维护的的表的集集合包含用用户信息息数据字字典：由Oracle服服务器创创建和维维护的表表的集合合包含数数据库信信息有四种数数据字典典视图，每一种种有一个个特定的的前缀来来反映其其不同的目的的。USER_这这些视视图包含含关于用用户所拥拥有的对对象的信信息。ALL_这这些视图图包含所所有用户户可访问问的表(

54、对象象表和相相关的表表)的的信息。DBA_这这些视视图是受受限制的的视图，它们只只能被分分配有DBA 角色色的用户户所访问。V$这这些视图图是动态态执行的的视图，包含数数据库服服务器的的性能、存储器器和锁的信信息。查询数据据字典查看本本用户所所拥有的的表的名名称SELECTtable_nameFROM user_tables ;查看本本用户所所拥有的的不同的的对象类类型SELECTDISTINCTobject_typeFROM user_objects;查看本本用户所所拥有的的表、视视图、同同义词和和序列SELECT*FROM user_catalog;数据类型型数据类型型说说明明VARCHA

55、R2(size)可可变长度度的字符符数据CHAR(size)固固定长长度的字字符数据据NUMBER(p,s)可变长度度的数字字数据DATE日日期和和时间值值LONG最最大2G的可可变长度度字符数数据CLOB最最大4G的字字符数据据RAWand LONGRAW 原始始二进制制数据BLOB最最大4G的二二进制数数据BFILE最最大大4G的的，存储储在外部部文件中中的二进制数据据ROWID一一个64进制的的数制系系统，表表示表中中一行的唯唯一地址址用子查询询创建表表该方法既既可以创创建表还还可以将将从子查查询返回回的行插插入新创创建的表中。CREATETABLEdept80 ASSELECTempl

56、oyee_id, last_name,salary*12ANNSAL, hire_dateFROM employeesWHEREdepartment_id =80;原则 被创建的的表要带带指定的的列名，并且由由SELECT语句返返回的行行被插入入到新表表中。字段的定定义只能能包括列列名和默默认值。如果给出出了指定定的列，列的数数目必须须等于子子查询的的SELECT列表的的列数目目。如果没有有给出了了指定的的列，表表的列名名应和子子查询中中的列名名是相同同的。完整性规规则不会会被传递递到新表表中，仅仅列的数数据类型型被定义义。ALTERTABLE语句用ALTERTABLE语句来：添加一一个新列列

57、修改一一个已存存在的列列为新列列定义一一个默认认值删除一一个列添加列ALTERTABLEtableADD(columndatatypeDEFAULTexpr,columndatatype.);修改列ALTERTABLEtableMODIFY(columndatatypeDEFAULTexpr,columndatatype.);删除列ALTERTABLEtableDROP(column);添加新列列用ADD字句添加加列ALTERTABLEdept80ADD (job_idVARCHAR2(9);添加新列列的原则则你可以添添加或修修改列。你不能指指定新添添加的列列的位置置，新列列将成为为最后一一列

58、。修改列可以改改变列的的数据类类型、大大小和默默认值ALTERTABLEdept80MODIFY(last_nameVARCHAR2(30);对默认认值的改改变只影影响后来来插入表表中的数数据原则 你可以增增加宽度度或一个个数字列列的精度度。你可以增增加数字字列或字字符列的的宽度。你可以减减少一个个列的宽宽度，但但仅在列列中只包包含空值值或表中中没有行行时。你可以改改变数据据类型，但仅在在列中只只包含空空值时。你可以转转换一个个CHAR列到到VARCHAR2数数据类型型或转换换一个VARCHAR2列到到 CHAR数数据类类型仅当当列中只只包含空空值时，或者你你不改变变列的大大小时。对默认值值的

59、改变变仅影响响以后插插入的列列。删除列用DROP COLUMN子句从表表中删除除列ALTERTABLEdept80DROP COLUMNjob_id;原则 列可以有有也可以以没有数数据。用ALTERTABLE语语句，一一次只能能有一列列被删除除。表被修改改后必须须至少保保留一列列。一旦一列列被删除除，它不不能再恢恢复。删除表在表中中的所有有数据和和结构都都被删除除任何未未决的事事务都被被提交所有的的索引被被删除你不能能回退DROP TABLE语句DROP TABLE dept80;DROP TABLE语句删删除Oracle表定定义，当当你删除除一个表表时，数据库丢丢失表中中所有的的数据，并且所

60、所有与其其相关的的索引也也被删除。改变一个个对象的的名字执行RENAME语句，改改变一个个表、视视图、序序列或同义词RENAMEdeptTOdetail_dept;注:你必必须是对对象的所所有者截断表TRUNCATETABLE语句：删除表表中所有有的行释放该该表所使使用的存存储空间间TRUNCATETABLEdetail_dept;不能回回退用TRUNCATE删除的行行作为选选择，可可以用DELETE语句删除除行子查询&操纵数数据目标完成本课课后,您您应当当能够执执行下列列操作：描述子子查询能能够解决决的问题题类型定义子子查询列出子子查询的的类型写单行行和多行行子查询询描述每每个DML语语句插