Oracle数据库培训-SQL基础篇11288

《Oracle数据库培训-SQL基础篇11288》由会员分享，可在线阅读，更多相关《Oracle数据库培训-SQL基础篇11288（204页珍藏版）》请在装配图网上搜索。

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

2、你能够使用SELECT语句的行选择功能选择表中的行，这些行是你想要用查询返回的。你能够使用不同的标准限制你看见的行。,连接：你能够使用SELECT语句的连接功能来集合数据，这些数据被存储在不同的表中，在它们之间可以创建连接。在后面的课程中你将学到更多关于连接的内容。,,,,基本,SELECT,语句,SELECT *| {[DISTINCT],,column,|,expression,[,alias,],...},FROM,table;,,•SELECT 确定哪些列,•FROM 确定哪张表,,,,选择所有列与指定列,SELECT * FROM departments;,用跟在SELECT关键字后

3、面的星号 (*)，你能够显示表中数据的所有列。,,SELECT department_id, location_id,FROM departments;,你能够用SELECT语句来显示表的指定列，指定列名之间用逗号分隔。,,,,,写SQL 语句,•SQL 语句对大小写不敏感,•SQL 语句可以写成一行或多行,•关键字不能简写或分开折行,•子句通常放在不同的行,•缩进用于增强可读性,,,,算术表达式,用算术运算符创建数字和日期数据的表达式,,操作 说明,+,加,-,减,*,乘,/,除,,,,,使用算术运算符,SELECT last_name, salary, salary + 30

4、0,FROM employees;,优先级:,•乘法和除法比加法和减法的优先级高,•相同优先级的运算符从左到右计算,•圆括号用于强制优先计算，并且使语句更 清晰,SELECT last_name, salary, 12*salary+100,FROM employees;,,,,空值,•null 是一个未分配的、未知的，或不适用的值,•null不是0，也不是空格,•包含空值的算术表达式计算结果为空,SELECT last_name, job_id, salary,,commission_pct,FROM employees;,,,,,定义列别名,列别名：,•改变列标题的名字,•可用于计算结果

5、,•紧跟在列名后面–在列名和别名之间可以有选项AS 关键字,•如果别名中包含有空格、或者特殊字符、或者大小写敏感，要求用双引号,SELECT last_name AS name, commission_pct comm FROM employees;,,,,连字运算符,连字运算符：,•连接列或者字符串到其它的列,•用两个竖线表示(||),•构造一个字符表达式的合成列,SELECT first_name||last_name AS Employees,FROM employees;,,,文字字符串,•文字字符串是包含在,SELECT,列表中的一个字符串，一个数字或者一个日期,•日期和字符的文字字

6、符串值必须用单引号括起来,•每个文字字符串在每行输出一次,SELECT last_name||' is a '||job_id,AS "Employee Details”,FROM employees;,,,约束和排序数据,,,目标,,完成本课后, 您应当能够执行下列操作：,•用一个查询限制返回的行,•用一个查询分类返回的行,,,限制选择的行,用,WHERE,子句限制返回的行,SELECT * |{[DISTINCT],,column|expression,[,alias,],...},FROM,table,[WHERE,condition(s),];,,,WHERE子句跟着FROM子句,WH

7、ERE 限制查询满足条件的行,condition,由列名、表达式、常数和比较操作组成,WHERE子句能够比较列值、文字值、算术表达式或者函,数，WHERE子句由三个元素组成：,列名 ， 比较条件 ， 列名、常量或值列表 。,,,使用,WHERE,子句,,SELECT employee_id, last_name, job_id,,department_id],FROM employees,WHERE department_id = 90 ;,,,,字符串和日期,•字符串和日期的值放在单引号中,•字符值区分大小写，日期值是格式敏感的,•日期的默认格式是DD-MON-RR.,SELECT last

8、_name, job_id, department_id,FROM employees,WHERE last_name = 'Whalen';,,,,比较条件,运算 含义,=,等于,>,大于,>=,大于等于,<,小于,<=,小于等于,,不等于,比较条件被用于一个表达式与一个值或与另一个表达式的比较。,... WHERE hire_date='01-JAN-95',... WHERE salary>=6000,... WHERE last_name='Smith',,,其它比较条件,操作 含义,

9、BETWEEN...AND...,在两个值之间(包含),IN(set),匹配一个任意值列表,LIKE,匹配一个字符模板,IS NULL,是一个空值,使用BETWEEN条件:,,SELECT last_name, salary,FROM employees,WHERE salary BETWEEN 2500 AND 3500;,使用IN条件,,SELECT employee_id, last_name, salary, manager_id,FROM employees,WHERE manager_id IN (100, 101, 201);,,,其它比较条件,使用LIKE条件,•使用,LIKE

10、,条件执行有效搜索串值的通配符搜索,•搜索条件既可以包含文字也可以包含数字：,–,%,表示任意顺序的零个或多个字符,–,_,表示一个字符,SELECT first_name,FROM employees,WHERE first_name LIKE 'S%';,,使用NULL条件,•用IS NULL操作来测试空值,SELECT last_name, manager_id,FROM employees,WHERE manager_id IS NULL;,,NULL条件，包括IS NULL条件和IS NOT NULL条件。,,,,逻辑条件,运算

11、 含义,AND,如果两个组成部分的条件都为真，返回,TRUE,OR,如果两个组成部分中的任一个条件为真，返回,TRUE,NOT,如果跟随的条件为假，返回,TRUE,可以在WHERE子句中用AND和OR运算符使用多个条件。,使用AND操作,：AND要求两个条件同时为真,SELECT employee_id, last_name, job_id, salary,FROM employees,WHERE salary >=10000 AND job_id LIKE '%MAN%';,,,,逻辑条件,使用,OR,操作：,OR,操作要求两者之一为真即可,SELECT employee_id, las

12、t_name, job_id, salary,FROM employees,WHERE salary >= 10000 OR job_id LIKE '%MAN%';,使用,NOT,操作,SELECT last_name, job_id,FROM employees,WHERE job_id,NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');,,注：NOT运算符也可以用于另一个SQL运算符，例如，BETWEEN、LIKE、和NULL。,,,优先规则,求值顺序,1,算术运算,2,连字操作,3,比较操作,4 IS[NOT]N

13、ULL, LIKE, [NOT]IN,5 [NOT] BETWEEN,6 NOT,逻辑条件,7 AND,逻辑条件,8 OR,逻辑条件,使用圆括号改变优先规则,,,,ORDER BY,子句,•用,ORDER BY,子句排序行,–ASC: 升序排序，默认,–DESC: 降序排序,•,ORDER BY,子句在,SELECT,语句的最后,SELECT last_name, job_date,FROM employees,ORDER BY

14、 hire_date ;,语法 :,SELECT,expr,FROM,table,[WHERE,condition(s),],[ORDER BY {,column,,,expr,} [ASC|DESC]];,,,单行函数,,,目标,完成本课后，您应当能够执行下列操作：,•描述在SQL 中可用的函数的变量类型,•在,SELECT,语句中使用字符，数字和日期函数,•描述转换函数的使用,,,SQL,函数,,函数是SQL的一个非常强有力的特性，函数能够用于下面的目的：,执行数据计算,修改单个数据项,操纵输出进行行分组,格式化显示的日期和数字,转换列数据类型,SQL函数有输入参数，并且总有一个返回值。,

15、注：在本课中讲述的大多数函数是针对SQL的Oracle版的。,,,SQL,函数,(,续,),有两种截然不同的函数：,单行函数,多行函数,单行函数,这些函数仅对单个行进行运算，并且每行返回一个结果。有不同类型的单行函数，本课下面的函数类型：,字符,数字,日期,转换,多行函数,这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。多行函数在后面的课程中介绍。,,,单行函数,单行函数：,•操纵数据项,•接受多个参数并返回一个值,•作用于每一个返回行,•每行返回一个结果,•可以修改数据类型,•可以嵌套,•接受多个参数，参数可以是一个列或者一个表达式,,,,单行函数（续）,单行函数的特

16、性包括：,作用于查询中返回的每一行,每行返回一个结果,可能返回一个与参数不同类型的数据值,可能需要一个或多个参数,能够用在SELECT、WHERE和ORDER BY子句中，可以嵌套 。,function_name,[(,arg1, arg2,...,)],function_name,是函数的名字。,arg1, arg2,是由函数使用的任意参数，可以由一个列名或者一个表达式提供。,,,,单行函数（续）,本课包括下面的单行函数：,字符函数：接受字符输入，可以返回字符或者数字值,数字函数：接受数字输入，返回数字值,日期函数：对DATE数据类型的值进行运算 (除了MONTHS_BETWEEN函数返回一

17、个数字，所有日期函数都返回一个DATE数据类型的值。),转换函数：从一个数据类型到另一个数据类型转换一个值,通用函数：,,NVL 、 NVL2、 NULLIF、 COALSECE、 CASE 、 DECODE,,,字符函数,单行字符函数接受字符数据作为输入，既可以返回字符值也,可以返回数字值。字符函数可以被分为下面两种：,,大小写处理函数,字符处理函数,大小写处理函数如下：,LOWER(,column|expression,) 转换字符值为小写,UPPER(,column|expression,) 转换字符值为大写,INITCAP(,column|expression,) 转换每个单词

18、的首字母值为大写，所有,其它值为小写,字符处理函数如下：,,CONCAT(,column1|expression1 ,column2|expression2,),连接第一个字符值到第二个字符值；等价于连接运算符 (||),SUBSTR(,column|expression,m [,n],),从字符值中返回指定的字符，开始位置在,m,，,n,字符长度 (如果,m,是负数，计数从字符值末尾开始；如果,n,被忽略，返回到串结束的所有字符)。,,,,LENGTH(,column|expression,) 返回表达式中的字符数,INSTR(,column|expression, ‘string’, [,

19、m], [n],),,返回一个命名串的数字位置。随意地，你可以提供一个位置,m,作为查找的开始，在字符串中第,n,次发现的位置。,m,和,n,的默认值是1，意味着在起始开始查找，并且报告第一个发现的位置。,LPAD(,column,|,expression,,,n,,,',string,',),RPAD(,column,|,expression,,,n,,,',string,',),填充字符值左、右调节到n字符位置的总宽度,TRIM(,leading|trailing|both , trim_character FROM trim_source,),使你能够从一个字符串修整头或尾字符(或两者)

20、。如果,trim_character,或,trim_source,是字符文字，你必须放在单引号中。,REPLACE(,text, search_string, replacement_string,),从字符串查找一个文本表达式，如果找到，用指定的值串代替它,字符函数（续）,,,大小写处理函数,这些函数转换字符串的大小写,函 数 结 果,LOWER('SQL Course') sql course,UPPER('SQL Course') SQL COURSE,INITCAP('SQL Course') Sql

21、 Course,LOWER：转换大小写混合的字符串为小写字符串,UPPER：转换大小写混合的字符串为大写字符串,INITCAP：将每个单词的首字母转换为大写，其他字母为小写,SELECT ‘The job id for ’||UPPER(last_name)||‘ is ’,||LOWER(job_id) AS "EMPLOYEE DETAILS",FROM employees;,,,字符处理函数,函 数 结 果,CONCAT('Hello', 'World') HelloWorld,SUBSTR('HelloWor

22、ld',1,5) Hello,LENGTH('HelloWorld') 10,INSTR('HelloWorld', 'W') 6,LPAD(salary, 10,'*') *****24000,RPAD(salary, 10, '*') 24000 *****,TRIM('H' FROM 'HelloWorld') elloWorld,,,,数字函数,ROUND:,四舍五入指定小数的值,ROUND(45.926, 2) 45.

23、93,TRUNC(45.926, 2) 45.92,MOD(1600, 300) 100,,ROUND(,column,|,expression,,,n,),四舍五入列、表达式或值为,n,位小数位，或者，如果,n,被忽略，无小数位。(如果,n,是负值，小数点左边的数被四舍五入),TRUNC(,column,|,expression,,,n,),截断列、表达式或值到,n,位小数，或者，如果,n,被忽略，那么,n,默认为0,MOD(,m,,,n,) 返回,m,除以,n,的余数,,,使用,ROUND,函数,SELECT ROUND(45.923

24、,2), ROUND(45.923,0),ROUND(45.923,-1) FROM DUAL;,使用,TRUNC,函数,SELECT TRUNC(45.923,2), TRUNC(45.923),TRUNC(45.923,-2) FROM DUAL;,使用,MOD,函数,SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP';,注：MOD函数经常用于确定一个值是奇数还是偶数,,,,,日期的使用,•Oracle 数据库用内部数字格式存储日期：世纪，年，月，日，小时，分钟和秒,•默认日期

25、显示格式是DD-MON-RR.,SELECT last_name, hire_date,FROM employees,WHERE last_name like ''G%';';,SYSDATE,函数返回：,•Date,•Time,SYSDATE是一个日期函数，它返回当前数据库服务器的日期和时间。,SELECT SYSDATE FROM DUAL;,,,用日期计算,•从日期加或者减一个数，结果是一个日期值,•两个日期相减，得到两个日期之间的天数,•用小时数除以24，可以加小时到日期上,既然数据库以数字方式存储日期，你就可以用算术运算符进行计算，例如，加或减。你可以加或减数字常数以及日期。,你可

26、以进行下面的运算：,运算 结果 说明,date + number 日期 加一个天数到一个日期上,date - number 日期 从一个日期上减一个天数,date - date 天数 用一个日期减另一个日期,date + number/24 日期 加一个小时数到一个日期上,,,日期函数,函 数 说 明,MONTHS_BETWEEN,两个日期之间的月数,ADD_MONTHS,加日历月到日期,NEXT_DAY,下个星期几是几号,LAST_DAY,指

27、定月的最后一天,ROUND,四舍五入日期,TRUNC,截断日期,,,,日期函数（续）,MONTHS_BETWEEN(,date1, date2,)：计算,date1,和,date2,之间的月数，其结果可以是正的也可以是负的。如果,date1,大于,date2,，结果是正的，反之，结果是负的。结果的小数部分表示月的一部分。,ADD_MONTHS(,date, n,)：添加,n,个日历月到,date,。,n,的值必须是整数，但可以是负的。,NEXT_DAY(,date,,‘,char,’)：计算在,date,之后的下一个周(‘,char,’)指定天的日期。,char,的值可能是一个表示一天的数或者

28、是一个字符串。,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_MONTH

29、S (‘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')

30、 01-JAN-95,•,TRUNC(TO_DATE('25-JUL-95') ,'YEAR'),01-JAN-95,,,转换函数,数据类型转换,•隐式数据类型转换,•显式数据类型转换,对于直接赋值，Oracle 服务器能够自动地进行下面,的转换：,从 到,VARCHAR2 or CHAR NUMBER,VARCHAR2 or CHAR DATE,NUMBER VARCHAR2,DATE

31、 VARCHAR2,对于表达式赋值，Oracle 服务器能自动地进行下面的转换：,从 VARCHAR2 or CHAR,到,DATE,从 VARCHAR2 or CHAR,到,NUMBER,,,,转换函数(续）,显式数据类型转换,SQL 提供三种函数来从一种数据类型转换值到另一种：,TO_CHAR(,number,|,date,,[,fmt,],,[nlsparams,]),转换一个数字或日期值为一个VARCHAR2字符串，带格式,化样式,fmt,。,数字转换：nlsparams 参数指定下面的字符，它由数字格式,化元素返回：,小数字符 99999.

32、99,前导0 09999,本地货币符号 L9999,国际货币符号 $9999,如果忽略nlsparams或其它参数，该函数在会话中使用默认参数值。,,,TO_CHAR(,number,|,date,,[,fmt,], [,nlsparams,]),指定返回的月和日名字及其缩写的语言。如果忽略该参数，,该函数在会话中使用默认日期语言 。,TO_NUMBER(,char,[fmt], [nlsparams],),用由可选格式化样式,fmt,指定的格式转换包含数字的字符串为,一个数字。Nlsparams参数在该函数中的目的与TO_CHAR,函数用于数字转换的目的相同 。,TO_D

33、ATE(c,har,,[,fmt,],[,nlsparams,]),按照,fmt,指定的格式转换表示日期的字符串为日期值。如果忽,略,fmt,，格式是 DD-MON-YY。Nlsparams参数的目的与,TO_CHAR函数用于日期转换时的目的相同。,,,对日期使用,TO_CHAR,函数,TO_CHAR(,date,,',format_model,'),格式模板,•必须加单引号，并且区分大小写,•能够包含任一有效的日期格式元素,•有一个,fm,元素用来删除填补的空，或者前导零,•用一个逗号与日期值分开,SELECT employee_id,,TO_CHAR(hire_date, 'MM/YY')

34、 Month_Hired,FROM employees,WHERE last_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,[,

35、',format_model,']),Select to_number(‘12345’),from dual;,Select to_date(‘20000810’,’yyyy-mm-dd’,fromdual;,,,通用函数,这些函数可用于任意数据类型，并且适用于空值,•,NVL (expr1, expr2),•,NVL2 (expr1, expr2, expr3),•,NULLIF (expr1, expr2),•,COALESCE (expr1, expr2, ..., expr,n,),,NVL 转换空值为一个实际值,NVL2 如果expr1非空，NVL2返回expr2；如果exp

36、r1为空,，NVL2返回expr3。参数expr1可以是任意数据类型,NULLIF 比较两个表达式，如果相等返回空；如果不相等,，返回第一个表达式,COALESCE 返回表达式列表中的第一个非空表达式,,,NVL,函数,转换一个空值到一个实际的值,•可用的数据类型可以是日期、字符和数字,•数据类型必须匹配：,–,NVL(commission_pct,0),–,NVL(hire_date,'01-JAN-97'),–,NVL(job_id,'No Job Yet'),语法:,NVL (,expr1,,,expr2,),,在语法中：,expr1,是包含空值的源值或者表达式,expr2,是用于转换空

37、值的目的值,Select nvl(‘1’,’not null’ from dual;,注:如果expr1为空则返回expr2r的值,,,使用,NVL2,函数,NVL2 函数检查第一个表达式，如果第一个表达式不为空，,那么 NVL2 函数返回第二个表达式；如果第一个表达式为,空，那么第三个表达式被返回。,expr2 expr1,非空时的返回值,语法,NVL2(,expr1,,,expr2, expr3,),在语法中：,expr1,是可能包含空的源值或表达式,expr3 expr1,为空时的返回值,Select NVL2(‘1’,’not null’,’null’) from dual;,Se

38、lect NVL2(‘1’,’not null’,’null’) from dual;,,,使用,NULLIF,函数,NULLIF 函数比较两个表达式，如果相等，函数返回空，如果不相等，,函数返回第一个表达式。第一个表达式不能为 NULL。,语法,NULLIF (,expr1,,,expr2,),在语法中：,expr1,是对于,expr2,的被比较原值,expr2,是对于,expr1,的被比较原值。(如果它不等于,expr1,，,expr1,被返回)。,,Select nullif(‘abc’,’abcd’) from dual;,,,使用,COALESCE,函数,•,COALESCE,函数

39、超过,NVL,函数的优点是,COALESCE,函数,能够接受多个交替的值。,•如果第一个表达式非空，它返回该表达式；否则，它做一个,保留表达式的结合 。,COALESCE 函数返回列表中的第一个非空表达式。,,语法,COALESCE (,expr1,,,expr2, ... exprn,),在语法中：,expr1,如果它非空，返回该表达式,expr2,如果第一个表达式为空并且该表达式非空，返回该表达式,exprn,如果前面的表达式都为空，返回该表达式,Select coalesce(‘ ',‘ ','bca') from dual;,,,条件表达式,在SQL 语句中提供IF-THEN-ELSE

40、 逻辑的使用。,两种用法：,–,CASE,表达式,–,DECODE,函数,,CASE,表达式,CASE,expr,WHEN,comparison_expr1,THEN,return_expr1,,WHEN,comparison_expr2,THEN,return_expr2,,WHEN,comparison_exprn,THEN,return_exprn,,ELSE,else_expr,],END,,,,DECODE,函数,DECODE(,col|expression, search1, result1,,[,, search2, result2,...,,][,, default,]),DE

41、CODE 函数在比较表达式,(expression),和每个查找,(search),值后解码表达式，如果表达式与查找相同，返,回结果。,如果省略默认值，当没有查找值与表达式相匹配时返回,一个空值。,,,多表显示数据&组函数合计数据,,,目标,完成本课后, 您应当能够执行下列操作：,•写SELECT 语句使用等值和非等值连接,从多个表中访问数据,•使用外连接查看不满足连接条件的数据,•识别可用的组函数,•描述组函数的使用,•用GROUP BY 子句分组数据,•用HAVING 子句包含或排除分组的行,,,,,笛卡尔乘积,•笛卡尔乘积的形成，当：,–一个连接条件被遗漏时,–一个连接条件不正确时,–在

42、第一个表中的所有行被连接到第二个表的所有行时,•为了避免笛卡尔乘积的形成，在WHERE 子句中应当总是包,含正确的连接条件 。,,,,,用Oracle 语法连接表,使用一个连接从多个表中查询数据,SELECT,table1.column, table2.column,FROM,table1, table2,WHERE,table1.column1,=,table2.column2;,•在WHERE 子句中写连接条件,•当多个表中有相同的列名时，将表名作为列名的前缀定义连接,•当数据从多表中查询时，要使用连接 (,join,) 条件。一个表中,的行按照存在于相应列中的公值被连接到另一个表中的行，

43、,即，通常所说的主键和外键列,。,,,什么是等值连接?,EMPLOYEES DEPARTMENTS,,,,用等值连接返回记录,SELECT employees.employee_id, employees.last_name,,employees.department_id, departments.department_id,,departments.location_id,FROM employees, departments,WHERE employees.department_id = departments.department_id;,,,,,,,,,,

44、使用表别名,•使用表别名简化查询,•使用表别名改善性能,SELECT e.employee_id, e.last_name, e.department_id,,d.department_id, d.location_id,FROM employees e , departments d,WHERE e.department_id = d.department_id;,原则,表别名最多可以有 30 个字符，但短一些更好。,如果在 FROM 子句中表别名被用于指定的表，那么在整个 SELECT 语句中都要使用表别名。,表别名应该是有意义的。,表别名只对当前的 SELECT 语句有效。,,,,多于两

45、个表的连接,EMPLOYEES DEPARTMENTS LOCATIONS,,,,非等值连接,EMPLOYEES JOB_GRADES,,,,用非等值连接返回记录,SELECT e.last_name, e.salary, j.grade_level,FROM employees e, job_grades j,WHERE e.salary,BETWEEN j.lowest_sal AND j.highest_sal;,,,,外连接,DEPARTMENTS EMPLOYEES,,,外连接语法,•你可

46、以用一个外连接查看那些不满足连接条件的行,•外连接运算符是加号(+),SELECT,table1.column, table2.column,FROM,table1, table2,WHERE,table1.column(+),=,table2.column;,SELECT,table1.column, table2.column,FROM,table1, table2,WHERE,table1.column,=,table2.column(+);,•用外连接返回不直接匹配的记录,•如果在连接条件中使用外连接操作，缺少的行就可以被返,回。操作符是一个在圆括号中的加号 (+)，它被放置在连接,的

47、缺少信息的一侧。为了使来自不完善表的一行或多行能够,被连接，该操作符有产生一个或多个空行的作用。,,,使用外连接,SELECT e.last_name, e.department_id, d.department_name,FROM employees e, departments d,WHERE e.department_id (+) = d.department_id ;,,,,什么是组函数?,组函数操作行集，给出每组的结果,EMPLOYEES,,在,EMPLOYEES,表中的最高薪水,,,组函数的类型,•,AVG,平均值,•,COUNT,计数,•,MAX,最大值,•,MIN,最小值,•,

48、STDDEV,标准差,•,SUM,合计,•,VARIANCE,方差,,,,组函数,(,续,),每个函数接收一个参数，下面的表确定你可以在语法中使用的选项：,函 数 说 明,AVG([DISTINCT|ALL],n,),n,的平均值，忽略空值,COUNT({*|[DISTINCT|ALL],expr,}) 用 * 计数所有行，包括重复和带空值,的行。,expr,求除了空计算,MAX([DISTINCT|ALL],expr,),expr,的最大值，忽略空值,MIN([DISTINCT|ALL],

49、expr,),expr,的最小值，忽略空值,STDDEV([DISTINCT|ALL],x,),n,的标准差，忽略空值,SUM([DISTINCT|ALL],n,) 合计,n,的值，忽略空值,VARIANCE([DISTINCT|ALL],x,),n,的方差，忽略空值,,,,组函数的语法,SELECT[,column,,],group_function(column), ...,FROM,table,[WHERE,condition,],[GROUP BY,column,],[ORDER BY,column,];,使用组函数的原则,DISTINCT 使得函数只考虑不重复的值；ALL 使得

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

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

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

53、。,SELECT AVG(NVL(commission_pct, 0)),FROM employees;,,,,创建数据组:GROUP BY 子句语法,用GROUP BY 子句划分表中的行到较小的组中,SELECT,column,,,group_function(column),FROM,table,[WHERE,condition,],[GROUP BY,group_by_expression,],[ORDER BY,column,];,在语法中，,group_by_expression,指定那些用于将行分组的,列，这些列的值作为行分组的依据。,使用 WHERE 子句，你可以在划分行成组以前

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

55、on,FROM,table,[WHERE,condition,],[GROUP BY,group_by_expression,],[HAVING,group_condition,],[ORDER BY,column,];,,,,使用HAVING 子句,SELECT department_id, MAX(salary),FROM employees,GROUP BY department_id,HAVING MAX(salary)>10000 ;,,,,嵌套组函数,显示最大平均薪水,SELECT MAX(AVG(salary)),FROM employees,GROUP BY departme

56、nt_id;,,,,创建和管理表,,,目标,完成本课后, 您应当能够执行下列操作：,•描述主要数据库对象,•创建表,•描述列定义时可用的数据类型,•改变表的定义,•删除、改名和截断表,,,,数据库对象,表 基本存储单元, 由行和列组成,视图 逻辑地从一个或多个表中表示数据子集,序列 数字值发生器,索引 改善一些查询的性能,同义词 给对象可选择的名字,,,命名规则,表命名和列命名：,•必须以字母开始,•必须是1–30 个字符长度,•只能包含A–Z, a–z, 0–9, _, $, 和#,•同一个用户所拥有的

57、对象之间不能重名,•不能用Oracle 服务器的保留字,注：名字是大小写不敏感的，例如， EMPLOYEES 与,eMPloyees 或 eMpLOYEES 作为同一个名字来处理。,,,,CREATE TABLE,语句,•用户必须有：,–,CREATE TABLE,权限,–一个存储区域,CREATE TABLE [,schema,.],table,(,column datatype,[DEFAULT,expr,][, ...]);,•必须指定：,–表名,–列名、列数据类型和列的大小,schema,与所有者的名字一样,table,表的名字,DEFAULT,expr,指定默认值,column,列的

58、名字,datatype,列的数据类型和长度,,,引用另一个用户的表,•表属于另一个用户，不在该用户的方案中,•在那些表名字的前面使用所有者的名字作为,前缀,如果一个表不属于本用户，那么，其所有者的名字,必须放在表名的前面,SELECT *,FROM user_b.employees;,,,创建表,•创建表,CREATE TABLE dept(,deptno NUMBER(2),,dname VARCHAR2(14),,loc VARCHAR2(13));,•,确认表的创建,DESCRIBE dept,,,,Oracle 数据库中的表,•用户表：,–由用户创建和维护的表的集合,–包含用户信息,•

59、数据字典：,–由Oracle 服务器创建和维护的表的集合,–包含数据库信息,有四种数据字典视图，每一种有一个特定的前缀来反映其不,同的目的。,USER_ 这些视图包含关于用户所拥有的对象的信息。,ALL_ 这些视图包含所有用户可访问的表 (对象表和相关的表) 的信息。,DBA_ 这些视图是受限制的视图，它们只能被分配有 DBA 角色的用户所,访问。,V$ 这些视图是动态执行的视图，包含数据库服务器的性能、存储器,和锁的信息。,,,,查询数据字典,•查看本用户所拥有的表的名称,SELECT table_name,FROM user_tables ;,•查看本用户所拥有的

60、不同的对象类型,SELECT DISTINCT object_type,FROM user_objects ;,•查看本用户所拥有的表、视图、同义词和序列,SELECT * FROM user_catalog ;,,,数据类型,数据类型 说 明,VARCHAR2(,size,) 可变长度的字符数据,CHAR(,size,) 固定长度的字符数据,NUMBER(,p,,,s),可变长度的数字数据,DATE 日期和时间值,LONG

61、 最大2G的可变长度字符数据,CLOB 最大4G的字符数据,RAW and LONG RAW 原始二进制数据,BLOB 最大4G的二进制数据,BFILE 最大4G的，存储在外部文件中的二,进制数据,ROWID 一个64进制的数制系统，表示表中,一行的唯一地址,,,,用子查询创建表,该方法既可以创建表还可以将从子查询返回的行插入新创建,的表中。,CREATE

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

63、：,•添加一个新列,•修改一个已存在的列,•为新列定义一个默认值,•删除一个列,添加列,ALTER TABLE,table,ADD,(,column datatype,[DEFAULT,expr,][,,column datatype,]...);,修改列,ALTER TABLE,table,MODIFY,(,column datatype,[DEFAULT,expr,][,,column datatype,]...);,删除列,ALTER TABLE,table,DROP(,column,);,,,添加新列,•用,ADD,字句添加列,ALTER TABLE dept80 ADD (job_i

64、d VARCHAR2(9));,添加新列的原则,你可以添加或修改列。,你不能指定新添加的列的位置，新列将成为最后一列。,,,修改列,•可以改变列的数据类型、大小和默认值,ALTER TABLE dept80 MODIFY(last_name VARCHAR2(30));,•对默认值的改变只影响后来插入表中的数据,原则,你可以增加宽度或一个数字列的精度。,你可以增加数字列或字符列的宽度。,你可以减少一个列的宽度，但仅在列中只包含空值或表中没有行时。,你可以改变数据类型，但仅在列中只包含空值时。,你可以转换一个CHAR列到VARCHAR2数据类型或转换一个VARCHAR2列到 CHAR 数据类型仅

65、当列中只包含空值时，或者你不改变列的大小时。,对默认值的改变仅影响以后插入的列。,,,删除列,用,DROP COLUMN,子句从表中删除列,ALTER TABLE dept80,DROP COLUMN job_id;,原则,列可以有也可以没有数据。,用ALTER TABLE语句，一次只能有一列被删除。,表被修改后必须至少保留一列。,一旦一列被删除，它不能再恢复。,,,删除表,•在表中的所有数据和结构都被删除,•任何未决的事务都被提交,•所有的索引被删除,•你不能回退,DROP TABLE,语句,DROP TABLE dept80;,DROP TABLE语句删除Oracle表定义，当你删除一个表

66、时，,数据库丢失表中所有的数据，并且所有与其相关的索引也被,删除。,,,,改变一个对象的名字,•执行,RENAME,语句，改变一个表、视图、序列或,同义词,RENAME dept TO detail_dept;,注:你必须是对象的所有者,,,,截断表,•,TRUNCATE TABLE,语句：,–删除表中所有的行,–释放该表所使用的存储空间,TRUNCATE TABLE detail_dept;,•不能回退用,TRUNCATE,删除的行,•作为选择，可以用,DELETE,语句删除行,,,子查询&操纵数据,,,目标,完成本课后, 您应当能够执行下列操作：,•描述子查询能够解决的问题类型,•定义子查询,•列出子查询的类型,•写单行和多行子查询,•描述每个DML 语句,•插入行到表中,•更新表中的行,•从表中删除行,•控制事务,,,用子查询解决问题,用子查询解决问题,假想你想要写一个查询来找出挣钱比 Abel 的薪水还多的人。为了解决这个问题，你需要两个查询：一个找出 Abel 的收入，第二个查询找出收入高于 Abel 的人。,你可以用组合两个查询的方法解决这个问题，放置一个查询到另一个查询中