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

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,SQL 语言基础,主要内容,基本的,SELECT,语句,约束和排序数据,单行函数,多表显示数据、组函数合计数据,创建和管理表,子查询&操纵数据,内置约束,创建视图,其他数据库对象,基本的select语句,目标,完成本课后, 您应当能够：,列出SQL,SELECT,语句的功能,执行基本的,SELECT,语句,SQL,SELECT,语句的功能,列选择：你能够使用SELECT语句的列选择功能选择表中的列，这些列是你想要用查询返回的。当你查询时，你能够选择你查询的表中指定的列。,行选择：你能够使用SELECT语句的行选择功能选择表中的行，这些行是你想要用查询返回的。你能够使用不同的标准限制你看见的行。,连接：你能够使用SELECT语句的连接功能来集合数据，这些数据被存储在不同的表中，在它们之间可以创建连接。在后面的课程中你将学到更多关于连接的内容。,基本,SELECT,语句,SELECT *| DISTINCT,column,|,expression,alias,.,FROM,table;,SELECT 确定哪些列,FROM 确定哪张表,选择所有列与指定列,SELECT * FROM departments;,用跟在SELECT关键字后面的星号 (*)，你能够显示表中数据的所有列。,SELECT department_id, location_id,FROM departments;,你能够用SELECT语句来显示表的指定列，指定列名之间用逗号分隔。,写SQL 语句,SQL 语句对大小写不敏感,SQL 语句可以写成一行或多行,关键字不能简写或分开折行,子句通常放在不同的行,缩进用于增强可读性,算术表达式,用算术运算符创建数字和日期数据的表达式,操作 说明,+,加,-,减,*,乘,/,除,使用算术运算符,SELECT last_name, salary, salary + 300,FROM employees;,优先级:,乘法和除法比加法和减法的优先级高,相同优先级的运算符从左到右计算,圆括号用于强制优先计算，并且使语句更 清晰,SELECT last_name, salary, 12*salary+100,FROM employees;,空值,null 是一个未分配的、未知的，或不适用的值,null不是0，也不是空格,包含空值的算术表达式计算结果为空,SELECT last_name, job_id, salary,commission_pct,FROM employees;,定义列别名,列别名：,改变列标题的名字,可用于计算结果,紧跟在列名后面在列名和别名之间可以有选项AS 关键字,如果别名中包含有空格、或者特殊字符、或者大小写敏感，要求用双引号,SELECT last_name AS name, commission_pct comm FROM employees;,连字运算符,连字运算符：,连接列或者字符串到其它的列,用两个竖线表示(|),构造一个字符表达式的合成列,SELECT first_name|last_name AS Employees,FROM employees;,文字字符串,文字字符串是包含在,SELECT,列表中的一个字符串，一个数字或者一个日期,日期和字符的文字字符串值必须用单引号括起来,每个文字字符串在每行输出一次,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子句,WHERE 限制查询满足条件的行,condition,由列名、表达式、常数和比较操作组成,WHERE子句能够比较列值、文字值、算术表达式或者函,数，WHERE子句由三个元素组成：,列名 ， 比较条件 ， 列名、常量或值列表 。,使用,WHERE,子句,SELECT employee_id, last_name, job_id,department_id,FROM employees,WHERE department_id = 90 ;,字符串和日期,字符串和日期的值放在单引号中,字符值区分大小写，日期值是格式敏感的,日期的默认格式是DD-MON-RR.,SELECT last_name, job_id, department_id,FROM employees,WHERE last_name = Whalen;,比较条件,运算 含义,=,等于,大于,=,大于等于,小于,=6000,. WHERE last_name=Smith,其它比较条件,操作 含义,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,条件执行有效搜索串值的通配符搜索,搜索条件既可以包含文字也可以包含数字：,%,表示任意顺序的零个或多个字符,_,表示一个字符,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条件。,逻辑条件,运算 含义,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, last_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 ISNOTNULL, LIKE, NOTIN,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 hire_date ;,语法 :,SELECT,expr,FROM,table,WHERE,condition(s),ORDER BY ,column,expr, ASC|DESC;,单行函数,目标,完成本课后，您应当能够执行下列操作：,描述在SQL 中可用的函数的变量类型,在,SELECT,语句中使用字符，数字和日期函数,描述转换函数的使用,SQL,函数,函数是SQL的一个非常强有力的特性，函数能够用于下面的目的：,执行数据计算,修改单个数据项,操纵输出进行行分组,格式化显示的日期和数字,转换列数据类型,SQL函数有输入参数，并且总有一个返回值。,注：在本课中讲述的大多数函数是针对SQL的Oracle版的。,SQL,函数,(,续,),有两种截然不同的函数：,单行函数,多行函数,单行函数,这些函数仅对单个行进行运算，并且每行返回一个结果。有不同类型的单行函数，本课下面的函数类型：,字符,数字,日期,转换,多行函数,这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。多行函数在后面的课程中介绍。,单行函数,单行函数：,操纵数据项,接受多个参数并返回一个值,作用于每一个返回行,每行返回一个结果,可以修改数据类型,可以嵌套,接受多个参数，参数可以是一个列或者一个表达式,单行函数（续）,单行函数的特性包括：,作用于查询中返回的每一行,每行返回一个结果,可能返回一个与参数不同类型的数据值,可能需要一个或多个参数,能够用在SELECT、WHERE和ORDER BY子句中，可以嵌套 。,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_character FROM trim_source,),使你能够从一个字符串修整头或尾字符(或两者)。如果,trim_character,或,trim_source,是字符文字，你必须放在单引号中。,REPLACE(,text, search_string, replacement_string,),从字符串查找一个文本表达式，如果找到，用指定的值串代替它,字符函数（续）,大小写处理函数,这些函数转换字符串的大小写,函 数 结 果,LOWER(SQL Course) sql course,UPPER(SQL Course) SQL COURSE,INITCAP(SQL Course) Sql 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(HelloWorld,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.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,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 数据库用内部数字格式存储日期：世纪，年，月，日，小时，分钟和秒,默认日期显示格式是DD-MON-RR.,SELECT last_name, hire_date,FROM employees,WHERE last_name like G%;,SYSDATE,函数返回：,Date,Time,SYSDATE是一个日期函数，它返回当前数据库服务器的日期和时间。,SELECT SYSDATE FROM DUAL;,用日期计算,从日期加或者减一个数，结果是一个日期值,两个日期相减，得到两个日期之间的天数,用小时数除以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 服务器能够自动地进行下面,的转换：,从 到,VARCHAR2 or CHAR NUMBER,VARCHAR2 or CHAR DATE,NUMBER VARCHAR2,DATE VARCHAR2,对于表达式赋值，Oracle 服务器能自动地进行下面的转换：,从 VARCHAR2 or CHAR,到,DATE,从 VARCHAR2 or CHAR,到,NUMBER,转换函数(续）,显式数据类型转换,SQL 提供三种函数来从一种数据类型转换值到另一种：,TO_CHAR(,number,|,date,fmt,nlsparams,),转换一个数字或日期值为一个VARCHAR2字符串，带格式,化样式,fmt,。,数字转换：nlsparams 参数指定下面的字符，它由数字格式,化元素返回：,小数字符 99999.99,前导0 09999,本地货币符号 L9999,国际货币符号 $9999,如果忽略nlsparams或其它参数，该函数在会话中使用默认参数值。,TO_CHAR(,number,|,date,fmt, ,nlsparams,),指定返回的月和日名字及其缩写的语言。如果忽略该参数，,该函数在会话中使用默认日期语言 。,TO_NUMBER(,char,fmt, nlsparams,),用由可选格式化样式,fmt,指定的格式转换包含数字的字符串为,一个数字。Nlsparams参数在该函数中的目的与TO_CHAR,函数用于数字转换的目的相同 。,TO_DATE(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) 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, ,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；如果expr1为空,，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,是用于转换空值的目的值,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;,Select 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,函数超过,NVL,函数的优点是,COALESCE,函数,能够接受多个交替的值。,如果第一个表达式非空，它返回该表达式；否则，它做一个,保留表达式的结合 。,COALESCE 函数返回列表中的第一个非空表达式。,语法,COALESCE (,expr1,expr2, . exprn,),在语法中：,expr1,如果它非空，返回该表达式,expr2,如果第一个表达式为空并且该表达式非空，返回该表达式,exprn,如果前面的表达式都为空，返回该表达式,Select coalesce( , ,bca) from dual;,条件表达式,在SQL 语句中提供IF-THEN-ELSE 逻辑的使用。,两种用法：,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,),DECODE 函数在比较表达式,(expression),和每个查找,(search),值后解码表达式，如果表达式与查找相同，返,回结果。,如果省略默认值，当没有查找值与表达式相匹配时返回,一个空值。,多表显示数据&组函数合计数据,目标,完成本课后, 您应当能够执行下列操作：,写SELECT 语句使用等值和非等值连接,从多个表中访问数据,使用外连接查看不满足连接条件的数据,识别可用的组函数,描述组函数的使用,用GROUP BY 子句分组数据,用HAVING 子句包含或排除分组的行,笛卡尔乘积,笛卡尔乘积的形成，当：,一个连接条件被遗漏时,一个连接条件不正确时,在第一个表中的所有行被连接到第二个表的所有行时,为了避免笛卡尔乘积的形成，在WHERE 子句中应当总是包,含正确的连接条件 。,用Oracle 语法连接表,使用一个连接从多个表中查询数据,SELECT,table1.column, table2.column,FROM,table1, table2,WHERE,table1.column1,=,table2.column2;,在WHERE 子句中写连接条件,当多个表中有相同的列名时，将表名作为列名的前缀定义连接,当数据从多表中查询时，要使用连接 (,join,) 条件。一个表中,的行按照存在于相应列中的公值被连接到另一个表中的行，,即，通常所说的主键和外键列,。,什么是等值连接?,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;,使用表别名,使用表别名简化查询,使用表别名改善性能,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 语句有效。,多于两个表的连接,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,外连接语法,你可以用一个外连接查看那些不满足连接条件的行,外连接运算符是加号(+),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(+);,用外连接返回不直接匹配的记录,如果在连接条件中使用外连接操作，缺少的行就可以被返,回。操作符是一个在圆括号中的加号 (+)，它被放置在连接,的缺少信息的一侧。为了使来自不完善表的一行或多行能够,被连接，该操作符有产生一个或多个空行的作用。,使用外连接,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,最小值,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), .,FROM,table,WHERE,condition,GROUP BY,column,ORDER BY,column,;,使用组函数的原则,DISTINCT 使得函数只考虑不重复的值；ALL 使得函数考虑每个值，包括重复值。默认值是 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),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,) 返回在列中的由,expr,指定的唯一的非空值的数。,注：expr为列名,组函数和Null 值,所有组函数忽略列中的空值。在幻灯片的例子中，,平均值只基于表中的那些 COMMISSION_PCT 列,的值有效的行的计算。平均值计算是用付给所有雇,员的总佣金除以接受佣金的雇员数 (4)。,SELECT AVG(commission_pct),FROM employees;,在组函数中使用NVL 函数,NVL 函数强制组函数包括空值。在幻灯片的例子中，平均值,被基于所有表中的行来计算，不管 COMMISSION_PCT 列,是否为空。平均值的计算是用付给所有雇员的总佣金除以公,司的雇员总数 (20)。,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 子句，你可以在划分行成组以前过滤行。,在 GROUP BY 子句中必须包含列。,在 GROUP BY 子句中你不能用列别名。,默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆盖这个默认值。,如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中。如果你未能在 GROUP BY 子句中包含一个字段列表，你会收到一个错误信息。,约束分组结果: HAVING 子句,用HAVING 子句约束分组：,1.行被分组,2.应用组函数,3.匹配HAVING 子句的组被显示,SELECT,column,group_function,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 department_id;,创建和管理表,目标,完成本课后, 您应当能够执行下列操作：,描述主要数据库对象,创建表,描述列定义时可用的数据类型,改变表的定义,删除、改名和截断表,数据库对象,表 基本存储单元, 由行和列组成,视图 逻辑地从一个或多个表中表示数据子集,序列 数字值发生器,索引 改善一些查询的性能,同义词 给对象可选择的名字,命名规则,表命名和列命名：,必须以字母开始,必须是130 个字符长度,只能包含AZ, az, 09, _, $, 和#,同一个用户所拥有的对象之间不能重名,不能用Oracle 服务器的保留字,注：名字是大小写不敏感的，例如， EMPLOYEES 与,eMPloyees 或 eMpLOYEES 作为同一个名字来处理。,CREATE TABLE,语句,用户必须有：,CREATE TABLE,权限,一个存储区域,CREATE TABLE ,schema,.,table,(,column datatype,DEFAULT,expr, .);,必须指定：,表名,列名、列数据类型和列的大小,schema,与所有者的名字一样,table,表的名字,DEFAULT,expr,指定默认值,column,列的名字,datatype,列的数据类型和长度,引用另一个用户的表,表属于另一个用户，不在该用户的方案中,在那些表名字的前面使用所有者的名字作为,前缀,如果一个表不属于本用户，那么，其所有者的名字,必须放在表名的前面,SELECT *,FROM user_b.employees;,创建表,创建表,CREATE TABLE dept(,deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13);,确认表的创建,DESCRIBE dept,Oracle 数据库中的表,用户表：,由用户创建和维护的表的集合,包含用户信息,数据字典：,由Oracle 服务器创建和维护的表的集合,包含数据库信息,有四种数据字典视图，每一种有一个特定的前缀来反映其不,同的目的。,USER_ 这些视图包含关于用户所拥有的对象的信息。,ALL_ 这些视图包含所有用户可访问的表 (对象表和相关的表) 的信息。,DBA_ 这些视图是受限制的视图，它们只能被分配有 DBA 角色的用户所,访问。,V$ 这些视图是动态执行的视图，包含数据库服务器的性能、存储器,和锁的信息。,查询数据字典,查看本用户所拥有的表的名称,SELECT table_name,FROM user_tables ;,查看本用户所拥有的不同的对象类型,SELECT DISTINCT object_type,FROM user_objects ;,查看本用户所拥有的表、视图、同义词和序列,SELECT * FROM user_catalog ;,数据类型,数据类型 说 明,VARCHAR2(,size,) 可变长度的字符数据,CHAR(,size,) 固定长度的字符数据,NUMBER(,p,s),可变长度的数字数据,DATE 日期和时间值,LONG 最大2G的可变长度字符数据,CLOB 最大4G的字符数据,RAW and LONG RAW 原始二进制数据,BLOB 最大4G的二进制数据,BFILE 最大4G的，存储在外部文件中的二,进制数据,ROWID 一个64进制的数制系统，表示表中,一行的唯一地址,用子查询创建表,该方法既可以创建表还可以将从子查询返回的行插入新创建,的表中。,CREATE TABLE dept80 AS,SELECT employee_id, last_name, salary*12,ANNSAL, hire_date,FROM employees,WHERE department_id = 80;,原则,被创建的表要带指定的列名，并且由SELECT语句返回的行被插入到新表中。,字段的定义只能包括列名和默认值。,如果给出了指定的列，列的数目必须等于子查询的SELECT列表的列数目。,如果没有给出了指定的列，表的列名应和子查询中的列名是相同的。,完整性规则不会被传递到新表中，仅列的数据类型被定义。,ALTER TABLE,语句,用,ALTERTABLE,语句来：,添加一个新列,修改一个已存在的列,为新列定义一个默认值,删除一个列,添加列,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_id VARCHAR2(9);,添加新列的原则,你可以添加或修改列。,你不能指定新添加的列的位置，新列将成为最后一列。,修改列,可以改变列的数据类型、大小和默认值,ALTER TABLE dept80 MODIFY(last_name VARCHAR2(30);,对默认值的改变只影响后来插入表中的数据,原则,你可以增加宽度或一个数字列的精度。,你可以增加数字列或字符列的宽度。,你可以减少一个列的宽度，但仅在列中只包含空值或表中没有行时。,你可以改变数据类型，但仅在列中只包含空值时。,你可以转换一个CHAR列到VARCHAR2数据类型或转换一个VARCHAR2列到 CHAR 数据类型仅当列中只包含空值时，或者你不改变列的大小时。,对默认值的改变仅影响以后插入的列。,删除列,用,DROP COLUMN,子句从表中删除列,ALTER TABLE dept80,DROP COLUMN job_id;,原则,列可以有也可以没有数据。,用ALTER TABLE语句，一次只能有一列被删除。,表被修改后必须至少保留一列。,一旦一列被删除，它不能再恢复。,删除表,在表中的所有数据和结构都被删除,任何未决的事务都被提交,所有的索引被删除,你不能回退,DROP TABLE,语句,DROP TABLE dept80;,DROP TABLE语句删除Oracle表定义，当你删除一个表时，,数据库丢失表中所有的数据，并且所有与其相关的索引也被,删除。,改变一个对象的名字,执行,RENAME,语句，改变一个表、视图、序列或,同义词,RENAME dept TO detail_dept;,注:你必须是对象的所有者,截断表,TRUNCATE TABLE,语句：,删除表中所有的行,释放该表所使用的存储空间,TRUNCATE TABLE detail_dept;,不能回退用,TRUNCATE,删除的行,作为选择，可以用,DELETE,语句删除行,子查询&操纵数据,目标,完成本课后, 您应当能够执行下列操作：,描述子查询能够解决的问题类型,定义子查询,列出子查询的类型,写单行和多行子查询,描述每个DML 语句,插入行到表中,更新表中的行,从表中删除行,控制事务,用子查询解决问题,用子查询解决问题,假想你想要写一个查询来找出挣钱比 Abel 的薪水还多的人。为了解决这个问题，你需要两个查询：一个找出 Abel 的收入，第二个查询找出收入高于 Abel 的人。,你可以用组合两个查询的方法解决这个问题，放置一个查询到另一个查询中