SQL基础培训PPT课件

1、SQL 语言基础,主要内容,基本的SELECT语句 约束和排序数据 单行函数 多表显示数据、组函数合计数据 创建和管理表 子查询 SELECT 确定哪些列 FROM 确定哪张表,选择所有列与指定列,SELECT * FROM departments; 用跟在SELECT关键字后面的星号 (*)，你能够显示表中数据的所有列。 SELECT department_id, location_id FROM departments; 你能够用SELECT语句来显示表的指定列，指定列名之间用逗号分隔。,写SQL 语句,SQL 语句对大小写不敏感 SQL 语句可以写成一行或多行 关键字不能简写或分开折行

2、子句通常放在不同的行 缩进用于增强可读性,算术表达式,用算术运算符创建数字和日期数据的表达式 操作 说明 + 加 - 减 * 乘 / 除,使用算术运算符,SELECT last_name, salary, salary + 300 FROM employees; 优先级: 乘法和除法比加法和减法的优先级高 相同优先级的运算符从左到右计算 圆括号用于强制优先计算，并且使语句更 清晰 SELECT last_name, salary, 12*salary+100 FROM employees;,空值,null 是一个未分配的、未知的，或不适用的值 null不是0，也不是空格 包含空值的算术表达式计

3、算结果为空 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

4、 AS Employees FROM employees;,文字字符串,文字字符串是包含在SELECT列表中的一个字符串，一个数字或者一个日期 日期和字符的文字字符串值必须用单引号括起来 每个文字字符串在每行输出一次 SELECT last_name| is a |job_id AS Employee Details” FROM employees;,约束和排序数据,目标,完成本课后, 您应当能够执行下列操作： 用一个查询限制返回的行 用一个查询分类返回的行,限制选择的行,用WHERE子句限制返回的行 SELECT * |DISTINCT column|expressionalias,. FR

5、OM 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 ;,字符串和日期,字符串和日期的值放在单引号中 字符值区分大小写，日期值是格

6、式敏感的 日期的默认格式是DD-MON-RR. SELECT last_name, job_id, department_id FROM employees WHERE last_name = Whalen;,比较条件,运算 含义 = 等于 大于 = 大于等于 不等于 比较条件被用于一个表达式与一个值或与另一个表达式的比较。 . WHERE hire_date=01-JAN-95 . WHERE salary=6000 . WHERE last_name=Smith,其它比较条件,操作 含义 BETWEEN.AND. 在两个值之间(包含) IN(set) 匹配一个任意值列表 LIKE 匹配一个

7、字符模板 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条件执行有效搜索串值的通配符搜索 搜索条件既可以包含文字也可以包含数字： %表示任意顺序的零个或多个字符 _表示一个字符

8、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运算符使用多个条

9、件。 使用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 em

10、ployees 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_n

11、ame, 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函数有输入参

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

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

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

15、： CONCAT(column1|expression1 ,column2|expression2) 连接第一个字符值到第二个字符值；等价于连接运算符 (|) SUBSTR(column|expression,m ,n) 从字符值中返回指定的字符，开始位置在 m，n字符长度 (如果 m 是负数，计数从字符值末尾开始；如果 n 被忽略，返回到串结束的所有字符)。,LENGTH(column|expression) 返回表达式中的字符数 INSTR(column|expression, string, ,m, n ) 返回一个命名串的数字位置。随意地，你可以提供一个位置m作为查找的开始，在字符串中

16、第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, replac

17、ement_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(

18、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 TR

19、UNC(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 TRU

20、NC(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

21、%; SYSDATE函数返回： Date Time SYSDATE是一个日期函数，它返回当前数据库服务器的日期和时间。 SELECT SYSDATE FROM DUAL;,用日期计算,从日期加或者减一个数，结果是一个日期值 两个日期相减，得到两个日期之间的天数 用小时数除以24，可以加小时到日期上 既然数据库以数字方式存储日期，你就可以用算术运算符进行计算，例如，加或减。你可以加或减数字常数以及日期。 你可以进行下面的运算： 运算 结果 说明 date + number 日期 加一个天数到一个日期上 date - number 日期 从一个日期上减一个天数 date - date 天数 用一个

22、日期减另一个日期 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个

23、日历月到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_

24、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-J

25、AN-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 提供三种

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

27、O_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) 格式模板 必须加单引号，并且区分大小写 能够包含任一有效的日期格式元

28、素 有一个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, for

29、mat_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, ., exprn) NVL 转换空值为一个实际值 NVL2 如果expr1非

30、空，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 是用于转换空

31、值的目的值 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 nul

32、l,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函数 能够接受多个交替的值。 如果第一个表达式非空，

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

34、_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) 值后解码表达式，如果表达式与查找相同，返 回结果。 如果省略默认值，当没有查找值与表达式相匹配时返回 一

35、个空值。,多表显示数据 在WHERE 子句中写连接条件 当多个表中有相同的列名时，将表名作为列名的前缀定义连接 当数据从多表中查询时，要使用连接 (join) 条件。一个表中 的行按照存在于相应列中的公值被连接到另一个表中的行， 即，通常所说的主键和外键列。,什么是等值连接?,EMPLOYEES DEPARTMENTS,用等值连接返回记录,SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FR

36、OM 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 个字符，但短一些更好。 如果在 F

37、ROM 子句中表别名被用于指定的表，那么在整个 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;,外连接,DEPARTMENT

38、S 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(+); 用外连接返回不直接匹配的记录 如果在连接条件中使用外连接操作，缺少的行就可以被返 回。操作符是一个在圆括号中的加

39、号 (+)，它被放置在连接 的缺少信息的一侧。为了使来自不完善表的一行或多行能够 被连接，该操作符有产生一个或多个空行的作用。,使用外连接,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 最小值 STDD

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

41、值 VARIANCE(DISTINCT|ALLx) n 的方差，忽略空值,组函数的语法,SELECTcolumn, group_function(column), . FROM table WHERE condition GROUP BYcolumn ORDER BYcolumn; 使用组函数的原则 DISTINCT 使得函数只考虑不重复的值；ALL 使得函数考虑每个值，包括重复值。默认值是 ALL ，因此不需要指定。 用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。 所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE

42、函数。 当使用 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 ex

43、pr) COUNT(*) 返回表中满足 SELECT 语句标准的行数，包括重复行，包括有空值列的行。如果 WHERE 子句包括在 SELECT 语句中，COUNT(*) 返回满足 WHERE 子句条件的行数。 COUNT(expr) 返回在列中的由 expr 指定的非空值的数。 COUNT(DISTINCT expr) 返回在列中的由 expr 指定的唯一的非空值的数。 注：expr为列名,组函数和Null 值,所有组函数忽略列中的空值。在幻灯片的例子中， 平均值只基于表中的那些 COMMISSION_PCT 列 的值有效的行的计算。平均值计算是用付给所有雇 员的总佣金除以接受佣金的雇员数 (

44、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

45、 table WHERE condition GROUP BY group_by_expression ORDER BY column; 在语法中， group_by_expression 指定那些用于将行分组的 列，这些列的值作为行分组的依据。 使用 WHERE 子句，你可以在划分行成组以前过滤行。 在 GROUP BY 子句中必须包含列。 在 GROUP BY 子句中你不能用列别名。 默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆盖这个默认值。 如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROU

46、P 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 employ

47、ees GROUP BY department_id HAVING MAX(salary)10000 ;,嵌套组函数,显示最大平均薪水 SELECT MAX(AVG(salary) FROM employees GROUP BY department_id;,创建和管理表,目标,完成本课后, 您应当能够执行下列操作： 描述主要数据库对象 创建表 描述列定义时可用的数据类型 改变表的定义 删除、改名和截断表,数据库对象,表 基本存储单元, 由行和列组成 视图 逻辑地从一个或多个表中表示数据子集 序列 数字值发生器 索引 改善一些查询的性能 同义词 给对象可选择的名字,命名规则,表命名和列命名：

48、必须以字母开始 必须是130 个字符长度 只能包含AZ, az, 09, _, $, 和# 同一个用户所拥有的对象之间不能重名 不能用Oracle 服务器的保留字 注：名字是大小写不敏感的，例如， EMPLOYEES 与 eMPloyees 或 eMpLOYEES 作为同一个名字来处理。,CREATE TABLE语句,用户必须有： CREATE TABLE权限 一个存储区域 CREATE TABLE schema.table (column datatypeDEFAULT expr, .); 必须指定： 表名 列名、列数据类型和列的大小 schema 与所有者的名字一样 table 表的名字

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

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

51、ELECT 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

52、一个64进制的数制系统，表示表中 一行的唯一地址,用子查询创建表,该方法既可以创建表还可以将从子查询返回的行插入新创建 的表中。 CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80; 原则 被创建的表要带指定的列名，并且由SELECT语句返回的行被插入到新表中。 字段的定义只能包括列名和默认值。 如果给出了指定的列，列的数目必须等于子查询的SELECT列表的列数目。 如果没有给出了指定的列，表的列名应和子

53、查询中的列名是相同的。 完整性规则不会被传递到新表中，仅列的数据类型被定义。,ALTER TABLE语句,用ALTERTABLE语句来： 添加一个新列 修改一个已存在的列 为新列定义一个默认值 删除一个列 添加列 ALTER TABLE table ADD (column datatypeDEFAULT expr, column datatype.); 修改列 ALTER TABLE table MODIFY (column datatypeDEFAULT expr, column datatype.); 删除列 ALTER TABLE table DROP(column);,添加新列,用AD

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

55、数据类型或转换一个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删除的行