SQL基础培训课件讲义

SQL 语言基础1SQL基础培训SQL 语言基础1SQL基础培训主要内容基本的SELECT语句约束和排序数据单行函数多表显示数据、组函数合计数据创建和管理表子查询&操纵数据内置约束创建视图其他数据库对象2SQL基础培训主要内容基本的SELECT语句2SQL基础培训基本的select语句 3SQL基础培训基本的select语句 3SQL基础培训目标完成本课后,您应当能够：列出SQL SELECT语句的功能执行基本的SELECT语句4SQL基础培训目标完成本课后,您应当能够：4SQL基础培训SQL SELECT语句的功能列选择：你能够使用SELECT语句的列选择功能选择表中的列，这些列是你想要用查询返回的。当你查询时，你能够选择你查询的表中指定的列。行选择：你能够使用SELECT语句的行选择功能选择表中的行，这些行是你想要用查询返回的。你能够使用不同的标准限制你看见的行。连接：你能够使用SELECT语句的连接功能来集合数据，这些数据被存储在不同的表中，在它们之间可以创建连接。在后面的课程中你将学到更多关于连接的内容。5SQL基础培训SQL SELECT语句的功能列选择：你能够使用SELECT基本SELECT语句 SELECT*|DISTINCT column|expressionalias,.FROM table;SELECT 确定哪些列FROM 确定哪张表 6SQL基础培训基本SELECT语句 SELECT*|DISTINC选择所有列与指定列 SELECT*FROM departments;用跟在SELECT关键字后面的星号(*)，你能够显示表中数据的所有列。SELECT department_id,location_id FROM departments;你能够用SELECT语句来显示表的指定列，指定列名之间用逗号分隔。7SQL基础培训选择所有列与指定列 SELECT*FROM dep写SQL 语句SQL 语句对大小写不敏感SQL 语句可以写成一行或多行关键字不能简写或分开折行子句通常放在不同的行缩进用于增强可读性 8SQL基础培训写SQL 语句SQL 语句对大小写不敏感8SQL基础培训算术表达式用算术运算符创建数字和日期数据的表达式 操作 说明 +加 -减 *乘 /除 9SQL基础培训算术表达式用算术运算符创建数字和日期数据的表达式9SQL基使用算术运算符 SELECT last_name,salary,salary+300 FROM employees;优先级优先级:乘法和除法比加法和减法的优先级高 相同优先级的运算符从左到右计算 圆括号用于强制优先计算，并且使语句更 清晰 SELECT last_name,salary,12*salary+100 FROM employees;10SQL基础培训使用算术运算符 SELECT last_name,s空值 null 是一个未分配的、未知的，或不适用的值 null不是0，也不是空格 包含空值的算术表达式计算结果为空 SELECT last_name,job_id,salary,commission_pct FROM employees;11SQL基础培训空值 null 是一个未分配的、未知的，或不适用的值定义列别名列别名：改变列标题的名字 可用于计算结果 紧跟在列名后面在列名和别名之间可以有选项AS 关键字 如果别名中包含有空格、或者特殊字符、或者大小写敏感，要求用双引号 SELECT last_name AS name,commission_pct comm FROM employees;12SQL基础培训定义列别名列别名：12SQL基础培训连字运算符连字运算符：连接列或者字符串到其它的列 用两个竖线表示(|)构造一个字符表达式的合成列 SELECT first_name|last_name AS Employees FROM employees;13SQL基础培训连字运算符连字运算符：13SQL基础培训文字字符串 文字字符串是包含在SELECT列表中的一个字符串，一个数字或者一个日期 日期和字符的文字字符串值必须用单引号括起来 每个文字字符串在每行输出一次 SELECT last_name|is a|job_id AS Employee Details”FROM employees;14SQL基础培训文字字符串 文字字符串是包含在SELECT列表中的一个约束和排序数据 15SQL基础培训约束和排序数据 15SQL基础培训目标完成本课后,您应当能够执行下列操作：用一个查询限制返回的行用一个查询分类返回的行 16SQL基础培训目标16SQL基础培训限制选择的行 用WHERE子句限制返回的行 SELECT*|DISTINCT column|expressionalias,.FROM table WHERE condition(s);WHERE子句跟着子句跟着FROM子句子句 WHERE 限制查询满足条件的行 condition 由列名、表达式、常数和比较操作组成 WHERE子句能够比较列值、文字值、算术表达式或者函 数，WHERE子句由三个元素组成：列名，比较条件，列名、常量或值列表。17SQL基础培训限制选择的行 用WHERE子句限制返回的行17SQL基础使用WHERE子句 SELECT employee_id,last_name,job_id,department_id FROM employees WHERE department_id=90;18SQL基础培训使用WHERE子句 SELECT employee_id字符串和日期字符串和日期的值放在单引号中字符值区分大小写，日期值是格式敏感的日期的默认格式是DD-MON-RR.SELECT last_name,job_id,department_id FROM employees WHERE last_name=Whalen;19SQL基础培训字符串和日期字符串和日期的值放在单引号中19SQL基础培训比较条件 运算 含义 =等于 大于 =大于等于 小于 =小于等于 不等于比较条件被用于一个表达式与一个值或与另一个表达式的比比较条件被用于一个表达式与一个值或与另一个表达式的比较。较。.WHERE hire_date=01-JAN-95.WHERE salary=6000.WHERE last_name=Smith 20SQL基础培训比较条件 运算 其它比较条件操作操作 含义含义 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);21SQL基础培训其它比较条件操作 其它比较条件使用使用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条件。22SQL基础培训其它比较条件使用LIKE条件22SQL基础培训逻辑条件 运算 含义 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%;23SQL基础培训逻辑条件 运算 逻辑条件使用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。24SQL基础培训逻辑条件使用OR操作：OR操作要求两者之一为真即可24SQL优先规则 求值顺序 1 算术运算 2 连字操作 3 比较操作 4 ISNOTNULL,LIKE,NOTIN 5 NOT BETWEEN 6 NOT逻辑条件 7 AND逻辑条件 8 OR逻辑条件 使用圆括号改变优先规则 25SQL基础培训优先规则 求值顺序25SQL基础培训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;26SQL基础培训ORDER BY子句用ORDER BY子句排序行26SQL单行函数 27SQL基础培训单行函数 27SQL基础培训目标完成本课后，您应当能够执行下列操作：描述在SQL 中可用的函数的变量类型在SELECT语句中使用字符，数字和日期函数描述转换函数的使用 28SQL基础培训目标完成本课后，您应当能够执行下列操作：28SQL基础培训SQL 函数函数是SQL的一个非常强有力的特性，函数能够用于下面的目的：执行数据计算 修改单个数据项 操纵输出进行行分组 格式化显示的日期和数字 转换列数据类型 SQL函数有输入参数，并且总有一个返回值。注：在本课中讲述的大多数函数是针对SQL的Oracle版的。29SQL基础培训SQL 函数29SQL基础培训SQL 函数(续)有两种截然不同的函数：单行函数 多行函数 单行函数 这些函数仅对单个行进行运算，并且每行返回一个结果。有不同类型的单行函数，本课下面的函数类型：字符 数字 日期 转换 多行函数 这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。多行函数在后面的课程中介绍。30SQL基础培训SQL 函数(续)有两种截然不同的函数：30SQL基础单行函数单行函数：操纵数据项接受多个参数并返回一个值作用于每一个返回行每行返回一个结果可以修改数据类型可以嵌套接受多个参数，参数可以是一个列或者一个表达式31SQL基础培训单行函数单行函数：31SQL基础培训单行函数（续）单行函数的特性包括：作用于查询中返回的每一行 每行返回一个结果 可能返回一个与参数不同类型的数据值 可能需要一个或多个参数 能够用在SELECT、WHERE和ORDER BY子句中，可以嵌套。function_name(arg1,arg2,.)function_name 是函数的名字。arg1,arg2 是由函数使用的任意参数，可以由一个列名或者一个表达式提供。32SQL基础培训单行函数（续）单行函数的特性包括：32SQL基础培训单行函数（续）本课包括下面的单行函数：字符函数：接受字符输入，可以返回字符或者数字值 数字函数：接受数字输入，返回数字值 日期函数：对DATE数据类型的值进行运算(除了MONTHS_BETWEEN函数返回一个数字，所有日期函数都返回一个DATE数据类型的值。)转换函数：从一个数据类型到另一个数据类型转换一个值 通用函数：NVL、NVL2、NULLIF、COALSECE、CASE、DECODE33SQL基础培训单行函数（续）本课包括下面的单行函数：33SQL基础培训字符函数 单行字符函数接受字符数据作为输入，既可以返回字符值也单行字符函数接受字符数据作为输入，既可以返回字符值也可以返回数字值。字符函数可以被分为下面两种：可以返回数字值。字符函数可以被分为下面两种：大小写处理函数 字符处理函数大小写处理函数如下：大小写处理函数如下：LOWER(column|expression)转换字符值为小写 UPPER(column|expression)转换字符值为大写 INITCAP(column|expression)转换每个单词的首字母值为大写，所有 其它值为小写 字符处理函数如下：字符处理函数如下：CONCAT(column1|expression1,column2|expression2)连接第一个字符值到第二个字符值；等价于连接运算符(|)SUBSTR(column|expression,m,n)从字符值中返回指定的字符，开始位置在 m，n字符长度(如果 m 是负数，计数从字符值末尾开始；如果 n 被忽略，返回到串结束的所有字符)。34SQL基础培训字符函数 单行字符函数接受字符数据作为输入，既可以返回字符值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)从字符串查找一个文本表达式，如果找到，用指定的值串代替它 字符函数（续）35SQL基础培训字符函数（续）35SQL基础培训大小写处理函数这些函数转换字符串的大小写这些函数转换字符串的大小写 函 数 结 果LOWER(SQL Course)sql courseUPPER(SQL Course)SQL COURSEINITCAP(SQL Course)Sql CourseLOWER：转换大小写混合的字符串为小写字符串 UPPER：转换大小写混合的字符串为大写字符串 INITCAP：将每个单词的首字母转换为大写，其他字母为小写 SELECT The job id for|UPPER(last_name)|is|LOWER(job_id)AS EMPLOYEE DETAILS FROM employees;36SQL基础培训大小写处理函数这些函数转换字符串的大小写36SQL基础培训字符处理函数 函 数 结 果CONCAT(Hello,World)HelloWorldSUBSTR(HelloWorld,1,5)HelloLENGTH(HelloWorld)10INSTR(HelloWorld,W)6LPAD(salary,10,*)*24000RPAD(salary,10,*)24000*TRIM(H FROM HelloWorld)elloWorld37SQL基础培训字符处理函数 函 数 数字函数ROUND:四舍五入指定小数的值ROUND(45.926,2)45.93TRUNC(45.926,2)45.92MOD(1600,300)100 ROUND(column|expression,n)四舍五入列、表达式或值为n位小数位，或者，如果n被忽略，无小数位。(如果n是负值，小数点左边的数被四舍五入)TRUNC(column|expression,n)截断列、表达式或值到n位小数，或者，如果n被忽略，那么n默认为0 MOD(m,n)返回m除以n的余数 38SQL基础培训数字函数ROUND:四舍五入指定小数的值38SQL基础培训使用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函数经常用于确定一个值是奇数还是偶数 39SQL基础培训使用ROUND函数39SQL基础培训日期的使用 Oracle 数据库用内部数字格式存储日期：世纪，年，月，日，小时，分钟和秒 默认日期显示格式是DD-MON-RR.SELECT last_name,hire_date FROM employees WHERE last_name like G%;SYSDATE函数返回：Date Time SYSDATE是一个日期函数，它返回当前数据库服务器的日期和时间。SELECT SYSDATE FROM DUAL;40SQL基础培训日期的使用 Oracle 数据库用内部数字格式存储日用日期计算从日期加或者减一个数，结果是一个日期值两个日期相减，得到两个日期之间的天数用小时数除以24，可以加小时到日期上 既然数据库以数字方式存储日期，你就可以用算术运算符进行计算，例如，加或减。你可以加或减数字常数以及日期。你可以进行下面的运算：运算 结果 说明 date+number 日期 加一个天数到一个日期上date-number 日期 从一个日期上减一个天数date-date 天数 用一个日期减另一个日期 date+number/24 日期 加一个小时数到一个日期上 41SQL基础培训用日期计算从日期加或者减一个数，结果是一个日期值41SQL日期函数 函 数 说 明 MONTHS_BETWEEN 两个日期之间的月数ADD_MONTHS 加日历月到日期NEXT_DAY 下个星期几是几号LAST_DAY 指定月的最后一天ROUND 四舍五入日期TRUNC 截断日期42SQL基础培训日期函数 函 数 日期函数（续）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被截断到最近的天。43SQL基础培训日期函数（续）MONTHS_BETWEEN(date1,使用日期函数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-9544SQL基础培训使用日期函数MONTHS_BETWEEN(01-SEP使用日期函数（续）假定假定SYSDATE=25-JUL-95:ROUND(SYSDATE,MONTH)01-AUG-95ROUND(SYSDATE,YEAR)01-JAN-96TRUNC(SYSDATE,MONTH)01-JUL-95 TRUNC(SYSDATE,YEAR)01-JAN-95TRUNC(TO_DATE(25-JUL-95),YEAR)01-JAN-95 45SQL基础培训使用日期函数（续）假定SYSDATE=25-JUL-9转换函数数据类型转换数据类型转换隐式数据类型转换显式数据类型转换对于直接赋值，Oracle 服务器能够自动地进行下面的转换：从 到VARCHAR2 or CHAR NUMBERVARCHAR2 or CHAR DATENUMBER VARCHAR2 DATE VARCHAR2对于表达式赋值，Oracle 服务器能自动地进行下面的转换：从从 VARCHAR2 or CHAR 到 DATE从从 VARCHAR2 or CHAR 到 NUMBER46SQL基础培训转换函数数据类型转换46SQL基础培训转换函数(续）显式数据类型转换SQL 提供三种函数来从一种数据类型转换值到另一种：TO_CHAR(number|date,fmt,nlsparams)转换一个数字或日期值为一个VARCHAR2字符串，带格式化样式fmt。数字转换：nlsparams 参数指定下面的字符，它由数字格式化元素返回：小数字符 99999.99前导0 09999 本地货币符号 L9999国际货币符号$9999如果忽略nlsparams或其它参数，该函数在会话中使用默认参数值。47SQL基础培训转换函数(续）显式数据类型转换47SQL基础培训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函数用于日期转换时的目的相同。48SQL基础培训TO_CHAR(number|date,fmt,n对日期使用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;49SQL基础培训对日期使用TO_CHAR函数TO_CHAR(date,f日期格式模板的元素YYYY 数字全写年YEAR 年的拼写MM 月的两数字值MONTH 月的全名DY 周中天的三字母缩写DAY 周中天的全名MON 月的三字母缩写DD 月的数字天 50SQL基础培训日期格式模板的元素50SQL基础培训使用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;51SQL基础培训使用TO_NUMBER和TO_DATE函数转换字符串到数字通用函数这些函数可用于任意数据类型，并且适用于空值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 返回表达式列表中的第一个非空表达式 52SQL基础培训通用函数这些函数可用于任意数据类型，并且适用于空值52SQLNVL函数转换一个空值到一个实际的值可用的数据类型可以是日期、字符和数字数据类型必须匹配：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的值53SQL基础培训NVL函数转换一个空值到一个实际的值53SQL基础培训使用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;54SQL基础培训使用NVL2函数NVL2 函数检查第一个表达式，如果第一个表使用NULLIF函数NULLIF 函数比较两个表达式，如果相等，函数返回空，如果不相等，函数返回第一个表达式。第一个表达式不能为 NULL。语法 NULLIF(expr1,expr2)在语法中：expr1 是对于 expr2 的被比较原值 expr2 是对于 expr1 的被比较原值。(如果它不等于 expr1，expr1 被返回)。Select nullif(abc,abcd)from dual;55SQL基础培训使用NULLIF函数NULLIF 函数比较两个表达式，如果相使用COALESCE函数 COALESCE函数超过NVL函数的优点是COALESCE函数 能够接受多个交替的值。如果第一个表达式非空，它返回该表达式；否则，它做一个 保留表达式的结合。COALESCE 函数返回列表中的第一个非空表达式。函数返回列表中的第一个非空表达式。语法 COALESCE(expr1,expr2,.exprn)在语法中：expr1 如果它非空，返回该表达式 expr2 如果第一个表达式为空并且该表达式非空，返回该表达式 exprn 如果前面的表达式都为空，返回该表达式 Select coalesce(,bca)from dual;56SQL基础培训使用COALESCE函数 COALESCE函数超过NVL函条件表达式在在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 END57SQL基础培训条件表达式在SQL 语句中提供IF-THEN-ELSE 逻辑DECODE函数DECODE(col|expression,search1,result1 ,search2,result2,.,default)DECODE 函数在比较表达式(expression)和每个查找(search)值后解码表达式，如果表达式与查找相同，返回结果。如果省略默认值，当没有查找值与表达式相匹配时返回一个空值。58SQL基础培训DECODE函数DECODE(col|expression,多表显示数据&组函数合计数据 59SQL基础培训多表显示数据&组函数合计数据 59SQL基础培训目标完成本课后,您应当能够执行下列操作：写SELECT 语句使用等值和非等值连接 从多个表中访问数据使用外连接查看不满足连接条件的数据识别可用的组函数描述组函数的使用用GROUP BY 子句分组数据用HAVING 子句包含或排除分组的行 60SQL基础培训目标完成本课后,您应当能够执行下列操作：60SQL基础培训笛卡尔乘积笛卡尔乘积的形成，当：一个连接条件被遗漏时 一个连接条件不正确时 在第一个表中的所有行被连接到第二个表的所有行时为了避免笛卡尔乘积的形成，在WHERE 子句中应当总是包 含正确的连接条件。61SQL基础培训笛卡尔乘积笛卡尔乘积的形成，当：61SQL基础培训用Oracle 语法连接表使用一个连接从多个表中查询数据 SELECT table1.column,table2.column FROM table1,table2 WHERE table1.column1=table2.column2;在WHERE 子句中写连接条件当多个表中有相同的列名时，将表名作为列名的前缀定义连接 当数据从多表中查询时，要使用连接(join)条件。一个表中 的行按照存在于相应列中的公值被连接到另一个表中的行，即，通常所说的主键和外键列。62SQL基础培训用Oracle 语法连接表使用一个连接从多个表中查询数据62什么是等值连接?EMPLOYEES DEPARTMENTS 63SQL基础培训什么是等值连接?EMPLOYEES 用等值连接返回记录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;64SQL基础培训用等值连接返回记录SELECT employees.empl使用表别名使用表别名简化查询使用表别名改善性能 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 语句有效。65SQL基础培训使用表别名使用表别名简化查询65SQL基础培训多于两个表的连接 EMPLOYEES DEPARTMENTS LOCATIONS66SQL基础培训多于两个表的连接 EMPLOYEES DEP非等值连接EMPLOYEES JOB_GRADES67SQL基础培训非等值连接EMPLOYEES 用非等值连接返回记录 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;68SQL基础培训用非等值连接返回记录 SELECT e.last_na外连接 DEPARTMENTS EMPLOYEES69SQL基础培训外连接 DEPARTMENTS 外连接语法你可以用一个外连接查看那些不满足连接条件的行外连接运算符是加号(+)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(+);用外连接返回不直接匹配的记录 如果在连接条件中使用外连接操作，缺少的行就可以被返 回。操作符是一个在圆括号中的加号(+)，它被放置在连接 的缺少信息的一侧。为了使来自不完善表的一行或多行能够 被连接，该操作符有产生一个或多个空行的作用。70SQL基础培训外连接语法你可以用一个外连接查看那些不满足连接条件的行70使用外连接SELECT e.last_name,e.department_id,d.department_nameFROM employees e,departments dWHERE e.department_id(+)=d.department_id;71SQL基础培训使用外连接SELECT e.last_name,e.dep什么是组函数?组函数操作行集，给出每组的结果 EMPLOYEES在EMPLOYEES表中的最高薪水72SQL基础培训什么是组函数?组函数操作行集，给出每组的结果在EMPLOYE组函数的类型AVG 平均值COUNT 计数MAX 最大值MIN 最小值STDDEV 标准差SUM 合计VARIANCE 方差 73SQL基础培训组函数的类型AVG 平均值73SQL基础培训组函数(续)每个函数接收一个参数，下面的表确定你可以在语法中使用的选项：函 数 说 明 AVG(DISTINCT|ALLn)n 的平均值，忽略空值 COUNT(*|DISTINCT|ALLexpr)用*计数所有行，包括重复和带空值 的行。expr 求除了空计算 MAX(DISTINCT|ALLexpr)expr的最大值，忽略空值 MIN(DISTINCT|ALLexpr)expr的最小值，忽略空值 STDDEV(DISTINCT|ALLx)n 的标准差，忽略空值 SUM(DISTINCT|ALLn)合计 n 的值，忽略空值 VARIANCE(DISTINCT|ALLx)n 的方差，忽略空值 74SQL基础培训组函数(续)每个函数接收一个参数，下面的表确定你可以在语组函数的语法 SELECTcolumn,group_function(column),.FROM tableWHERE conditionGROUP BYcolumnORDER BYcolumn;使用组函数的原则 DISTINCT 使得函数只考虑不重复的值；ALL 使得函数考虑每个值，包括重复值。默认值是 ALL，因此不需要指定。用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。当使用 GROUP BY 子句时，Oracle 服务器隐式以升序排序结果集。为了覆盖该默认顺序，DESC 可以被用于 ORDER BY 子句。75SQL基础培训组函数的语法 SELECTcolumn,group_f使用AVG、SUM、MIN、MAX 函数你可以使用AVG 和SUM 用于数字数据 SELECT AVG(salary),MAX(salary),MIN(salary),SUM(salary)FROM employeesWHERE job_id LIKE%REP%;76SQL基础培训使用AVG、SUM、MIN、MAX 函数你可以使用AVG 使用COUNT 函数COUNT(*)返回一个表中的行数 COUNT 函数有三中格式：COUNT(*)COUNT(expr)COUNT(DISTINCT expr)COUNT(*)返回表中满足 SELECT 语句标准的行数，包括重复行，包括有空值列的行。如果 WHERE 子句包括在 SELECT 语句中，COUNT(*)返回满足 WHERE 子句条件的行数。COUNT(expr)返回在列中的由 expr 指定的非空值的数。COUNT(DISTINCT expr)返回在列中的由 expr 指定的唯一的非空值的数。注：expr为列名77SQL基础培训使用COUNT 函数COUNT(*)返回一个表中的行数 7组函数和Null 值所有组函数忽略列中的空值。在幻灯片的例子中，平均值只基于表中的那些 COMMISSION_PCT 列的值有效的行的计算。平均值计算是用付给所有雇员的总佣金除以接受佣金的雇员数(4)。SELECT AVG(commission_pct)FROM employees;78SQL基础培训组函数和Null 值所有组函数忽略列中的空值。在幻灯片的例子在组函数中使用NVL 函数NVL 函数强制组函数包括空值。在幻灯片的例子中，平均值被基于所有表中的行来计算，不管 COMMISSION_PCT 列是否为空。平均值的计算是用付给所有雇员的总佣金除以公司的雇员总数(20)。SELECT AVG(NVL(commission_pct,0)FROM employees;79SQL基础培训在组函数中使用NVL 函数NVL 函数强制组函数包括空值。在创建数据组:GROUP BY 子句语法用GROUP BY 子句划分表中的行到较小的组中SELECT column,group_function(column)FROM tableWHERE conditionGROUP BY group_by_expressionORDER BY column;在语法中，group_by_expression 指定那些用于将行分组的列，这些列的值作为行分组的依据。使用 WHERE 子句，你可以在划分行成组以前过滤行。在 GROUP BY 子句中必须包含列。在 GROUP BY 子句中你不能用列别名。默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆盖这个默认值。如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中。如果你未能在 GROUP BY 子句中包含一个字段列表，你会收到一个错误信息。80SQL基础培训创建数据组:GROUP BY 子句语法用GROUP BY 子约束分组结果:HAVING 子句用HAVING 子句约束分组：1.行被分组2.应用组函数3.匹配HAVING 子句的组被显示 SELECT column,group_functionFROM tableWHERE conditionGROUP BY group_by_expressionHAVING group_conditionORDER BY column;81SQL基础培训约束分组结果:HAVING 子句用HAVING 子句约束分使用HAVING 子句SELECT department_id,MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary)10000;82SQL基础培训使用HAVING 子句SELECT department_i嵌套组函数显示最大平均薪水SELECT MAX(AVG(salary)FROM employeesGROUP BY department_id;83SQL基础培训嵌套组函数显示最大平均薪水83SQL基础培训创建和管理表 84SQL基础培训创建和管理表 84SQL基础培训目标完成本课后,您应当能够执行下列操作：描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除、改名和截断表 85SQL基础培训目标完成本课后,您应当能够执行下列操作：85SQL基础培训数据库对象表 基本存储单元,由行和列组成视图 逻辑地从一个或多个表中表示数据子集序列 数字值发生器索引 改善一些查询的性能同义词 给对象可选择的名字 86SQL基础培训数据库对象表 基本存储单元,由行命名规则表命名和列命名：必须以字母开始必须是130 个字符长度只能包含AZ,az,09,_,$,和#同一个用户所拥有的对象之间不能重名不能用Oracle 服务器的保留字 注：名字是大小写不敏感的，例如，EMPLOYEES 与 eMPloyees 或 eMpLOYEES 作为同一个名字来处理。87SQL基础培训命名规则表命名和列命名：87SQL基础培训CREATE TABLE语句用户必须有：CREATE TABLE权限 一个存储区域CREATE TABLE schema.table(column datatypeDEFAULT expr,.);必须指定：表名 列名、列数据类型和列的大小schema 与所有者的名字一样 table 表的名字 DEFAULT expr 指定默认值 column 列的名字 datatype 列的数据类型和长度88SQL基础培训CREATE TABLE语句用户必须有：88SQL基础培训引用另一个用户的表表属于另一个用户，不在该用户的方案中在那些表名字的前面使用所有者的名字作为 前缀 如果一个表不属于本用户，那么，其所有者的名字 必须放在表名的前面SELECT*FROM user_b.employees;89SQL基础培训引用另一个用户的表表属于另一个用户，不在该用户的方案中89创建表创建表CREATE TABLE dept(deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13);确认表的创建DESCRIBE dept 90SQL基础培训创建表创建表90SQL基础培训Oracle 数据库中的表用户表：由用户创建和维护的表的集合 包含用户信息数据字典：由Oracle 服务器创建和维护的表的集合 包含数据库信息 有四种数据字典视图，每一种有一个特定的前缀来反映其不同的目的。USER_ 这些视图包含关于用户所拥有的对象的信息。ALL_ 这些视图包含所有用户可访问的表(对象表和相关的表)的信息。DBA_ 这些视图是受限制的视图，它们只能被分配有 DBA 角色的用户所 访问。V$这些视图是动态执行的视图，包含数据库服务器的性能、存储器 和锁的信息。91SQL基础培训Oracle 数据库中的表用户表：91SQL基础培训查询数据字典查看本用户所拥有的表的名称 SELECT table_name FROM user_tables;查看本用户所拥有的不同的对象类型SELECT DISTINCT object_type FROM user_objects;查看本用户所拥有的表、视图、同义词和序列SELECT*FROM user_catalog;92SQL基础培训查询数据字典查看本用户所拥有的表的名称 92SQL基础培训数据类型 数据类型 说 明VARCHAR2(size)可变长度的字符数据CHAR(size)固定长度的字符数据NUMBER(p,s)可变长度的数字数据DATE 日期和时间值LONG 最大2G的可变长度字符数据CLOB 最大4G的字符数据RAW and LONG RAW 原始二进制数据BLOB 最大4G的二进制数据BFILE 最大4G的，存储在外部文件中的二 进制数据ROWID 一个64进制的数制系统，表示表中 一行的唯一地址 93SQL基础培训数据类型 数据类型 用子查询创建表该方法既可以创建表还可以将从子查询返回的行插入新创建的表中。CREATE TABLE dept80 AS SELECT employee_id,last_name,salary*12 ANNSAL,hire_date FROM employees WHERE department_id=80;原则 被创建的表要带指定的列名，并且由SELECT语句返回的行被插入到新表中。字段的定义只能包括列名和默认值。如果给出了指定的列，列的数目必须等于子查询的SELECT列表的列数目。如果没有给出了指定的列，表的列名应和子查询中的列名是相同的。完整性规则不会被传递到新表中，仅列的数据类型被定义。94SQL基础培训用子查询创建表该方法既可以创建表还可以将从子查询返回的行插入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);95SQL基础培训ALTER TABLE语句用ALTERTABLE语句来：95添加新列用ADD字句添加列ALTER TABLE dept80 ADD(job_id VARCHAR2(9);添加新列的原则 你可以添加或修改列。你不能指定新添加的列的位置，新列将成为最后一列。96SQL基础培训添加新列用ADD字句添加列96SQL基础培训修改列可以改变列的数据类型、大小和默认值ALTER TABLE dept80 MODIFY(last_name VARCHAR2(30);对默认值的改变只影响后来插入表中的数据原则 你可以增加宽度或一个数字列的精度。你可以增加数字列或字符列的宽度。你可以减少一个列的宽度，但仅在列中只包含空值或表中没有行时。你可以改变数据类型，但仅在列中只包含空值时。你可以转换一个CHAR列到VARCHAR2数据类型或转换一个VARCHAR2列到 CHAR 数据类型仅当列中只包含空值时，或者你不改变列的大小时。对默认值的改变仅影响以后插入的列。97SQL基础培训修改列可以改变列的数据类型、大小和默认值97SQL基础培训删除列用DROP COLUMN子句从表中删除列ALTER TABLE dept80DROP COLUMN job_id;原则 列可以有也可以没有数据。用ALTER TABLE语句，一次只能有一列被删除。表被修改后必须至少保留一列。一旦一列被删除，它不能再恢复。98SQL基础培训删除列用DROP COLUMN子句从表中删除列98SQL基础删除表在表中的所有数据和结构都被删除任何未决的事务都被提交所有的索引被删除你不能回退DROP TABLE语句DROP TABLE dept80;DROP TABLE语句删除Oracle表定义，当你删除一个表时，数据库丢失表中所有的数据，并且所有与其相关的索引也被删除。99SQL基础培训删除表在表中的所有数据和结构都被删除99SQL基础培训改变一个对象的名字执行RENAME语句，改变一个表、视图、序列或同义词RENAME dept TO detail_dept;注:你必须是对象的所有者100SQL基础培训改变一个对象的名字执行RENAME语句，改变一个表、视图、截断表TRUNCATE T