Oracle数据库培训SQL基础篇

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]ENDDECODE函数数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));•确认表的创建建DESCRIBEdeptOracle数据库中中的表•用户表：–由用户创建建和维护的表表的集合–包含用户信信息•数据字典：：–由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<ANY(SELECTsalaryFROMemp