SQL基础培训PPT课件.ppt

SQL语言基础 1 主要内容 基本的SELECT语句约束和排序数据单行函数多表显示数据 组函数合计数据创建和管理表子查询 操纵数据内置约束创建视图其他数据库对象 2 基本的select语句 3 目标 完成本课后 您应当能够 列出SQLSELECT语句的功能 执行基本的SELECT语句 4 SQLSELECT语句的功能 列选择 你能够使用SELECT语句的列选择功能选择表中的列 这些列是你想要用查询返回的 当你查询时 你能够选择你查询的表中指定的列 行选择 你能够使用SELECT语句的行选择功能选择表中的行 这些行是你想要用查询返回的 你能够使用不同的标准限制你看见的行 连接 你能够使用SELECT语句的连接功能来集合数据 这些数据被存储在不同的表中 在它们之间可以创建连接 在后面的课程中你将学到更多关于连接的内容 5 基本SELECT语句 SELECT DISTINCT column expression alias FROMtable SELECT确定哪些列 FROM确定哪张表 6 选择所有列与指定列 SELECT FROMdepartments 用跟在SELECT关键字后面的星号 你能够显示表中数据的所有列 SELECTdepartment id location idFROMdepartments 你能够用SELECT语句来显示表的指定列 指定列名之间用逗号分隔 7 写SQL语句 SQL语句对大小写不敏感 SQL语句可以写成一行或多行 关键字不能简写或分开折行 子句通常放在不同的行 缩进用于增强可读性 8 算术表达式 用算术运算符创建数字和日期数据的表达式操作说明 加 减 乘 除 9 使用算术运算符 SELECTlast name salary salary 300FROMemployees 优先级 乘法和除法比加法和减法的优先级高 相同优先级的运算符从左到右计算 圆括号用于强制优先计算 并且使语句更清晰SELECTlast name salary 12 salary 100FROMemployees 10 空值 null是一个未分配的 未知的 或不适用的值 null不是0 也不是空格 包含空值的算术表达式计算结果为空SELECTlast name job id salary commission pctFROMemployees 11 定义列别名 列别名 改变列标题的名字 可用于计算结果 紧跟在列名后面 在列名和别名之间可以有选项AS关键字 如果别名中包含有空格 或者特殊字符 或者大小写敏感 要求用双引号SELECTlast nameASname commission pctcommFROMemployees 12 连字运算符 连字运算符 连接列或者字符串到其它的列 用两个竖线表示 构造一个字符表达式的合成列SELECTfirst name last nameASEmployeesFROMemployees 13 文字字符串 文字字符串是包含在SELECT列表中的一个字符串 一个数字或者一个日期 日期和字符的文字字符串值必须用单引号括起来 每个文字字符串在每行输出一次SELECTlast name isa job idAS EmployeeDetails FROMemployees 14 约束和排序数据 15 目标 完成本课后 您应当能够执行下列操作 用一个查询限制返回的行 用一个查询分类返回的行 16 限制选择的行 用WHERE子句限制返回的行SELECT DISTINCT column expression alias FROMtable WHEREcondition s WHERE子句跟着FROM子句WHERE限制查询满足条件的行condition由列名 表达式 常数和比较操作组成WHERE子句能够比较列值 文字值 算术表达式或者函数 WHERE子句由三个元素组成 列名 比较条件 列名 常量或值列表 17 使用WHERE子句 SELECTemployee id last name job id department id FROMemployeesWHEREdepartment id 90 18 字符串和日期 字符串和日期的值放在单引号中 字符值区分大小写 日期值是格式敏感的 日期的默认格式是DD MON RR SELECTlast name job id department idFROMemployeesWHERElast name Whalen 19 比较条件 运算含义 等于 大于 大于等于不等于比较条件被用于一个表达式与一个值或与另一个表达式的比较 WHEREhire date 01 JAN 95 WHEREsalary 6000 WHERElast name Smith 20 其它比较条件 操作含义BETWEEN AND 在两个值之间 包含 IN set 匹配一个任意值列表LIKE匹配一个字符模板ISNULL是一个空值使用BETWEEN条件 SELECTlast name salaryFROMemployeesWHEREsalaryBETWEEN2500AND3500 使用IN条件SELECTemployee id last name salary manager idFROMemployeesWHEREmanager idIN 100 101 201 21 其它比较条件 使用LIKE条件 使用LIKE条件执行有效搜索串值的通配符搜索 搜索条件既可以包含文字也可以包含数字 表示任意顺序的零个或多个字符 表示一个字符SELECTfirst nameFROMemployeesWHEREfirst nameLIKE S 使用NULL条件 用ISNULL操作来测试空值SELECTlast name manager idFROMemployeesWHEREmanager idISNULL NULL条件 包括ISNULL条件和ISNOTNULL条件 22 逻辑条件 运算含义AND如果两个组成部分的条件都为真 返回TRUEOR如果两个组成部分中的任一个条件为真 返回TRUENOT如果跟随的条件为假 返回TRUE可以在WHERE子句中用AND和OR运算符使用多个条件 使用AND操作 AND要求两个条件同时为真SELECTemployee id last name job id salaryFROMemployeesWHEREsalary 10000ANDjob idLIKE MAN 23 逻辑条件 使用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 24 优先规则 求值顺序1算术运算2连字操作3比较操作4IS NOT NULL LIKE NOT IN5 NOT BETWEEN6NOT逻辑条件7AND逻辑条件8OR逻辑条件使用圆括号改变优先规则 25 ORDERBY子句 用ORDERBY子句排序行 ASC 升序排序 默认 DESC 降序排序 ORDERBY子句在SELECT语句的最后SELECTlast name job dateFROMemployeesORDERBYhire date 语法 SELECTexprFROMtable WHEREcondition s ORDERBY column expr ASC DESC 26 单行函数 27 目标 完成本课后 您应当能够执行下列操作 描述在SQL中可用的函数的变量类型 在SELECT语句中使用字符 数字和日期函数 描述转换函数的使用 28 SQL函数 函数是SQL的一个非常强有力的特性 函数能够用于下面的目的 执行数据计算修改单个数据项操纵输出进行行分组格式化显示的日期和数字转换列数据类型SQL函数有输入参数 并且总有一个返回值 注 在本课中讲述的大多数函数是针对SQL的Oracle版的 29 SQL函数 续 有两种截然不同的函数 单行函数多行函数单行函数这些函数仅对单个行进行运算 并且每行返回一个结果 有不同类型的单行函数 本课下面的函数类型 字符数字日期转换多行函数这些函数能够操纵成组的行 每个行组给出一个结果 这些函数也被称为组函数 多行函数在后面的课程中介绍 30 单行函数 单行函数 操纵数据项 接受多个参数并返回一个值 作用于每一个返回行 每行返回一个结果 可以修改数据类型 可以嵌套 接受多个参数 参数可以是一个列或者一个表达式 31 单行函数 续 单行函数的特性包括 作用于查询中返回的每一行每行返回一个结果可能返回一个与参数不同类型的数据值可能需要一个或多个参数能够用在SELECT WHERE和ORDERBY子句中 可以嵌套 function name arg1 arg2 function name是函数的名字 arg1 arg2是由函数使用的任意参数 可以由一个列名或者一个表达式提供 32 单行函数 续 本课包括下面的单行函数 字符函数 接受字符输入 可以返回字符或者数字值数字函数 接受数字输入 返回数字值日期函数 对DATE数据类型的值进行运算 除了MONTHS BETWEEN函数返回一个数字 所有日期函数都返回一个DATE数据类型的值 转换函数 从一个数据类型到另一个数据类型转换一个值通用函数 NVL NVL2 NULLIF COALSECE CASE DECODE 33 字符函数 单行字符函数接受字符数据作为输入 既可以返回字符值也可以返回数字值 字符函数可以被分为下面两种 大小写处理函数字符处理函数大小写处理函数如下 LOWER column expression 转换字符值为小写UPPER column expression 转换字符值为大写INITCAP column expression 转换每个单词的首字母值为大写 所有其它值为小写字符处理函数如下 CONCAT column1 expression1 column2 expression2 连接第一个字符值到第二个字符值 等价于连接运算符 SUBSTR column expression m n 从字符值中返回指定的字符 开始位置在m n字符长度 如果m是负数 计数从字符值末尾开始 如果n被忽略 返回到串结束的所有字符 34 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 从字符串查找一个文本表达式 如果找到 用指定的值串代替它 字符函数 续 35 大小写处理函数 这些函数转换字符串的大小写函数结果LOWER SQLCourse sqlcourseUPPER SQLCourse SQLCOURSEINITCAP SQLCourse SqlCourseLOWER 转换大小写混合的字符串为小写字符串UPPER 转换大小写混合的字符串为大写字符串INITCAP 将每个单词的首字母转换为大写 其他字母为小写SELECT Thejobidfor UPPER last name is LOWER job id AS EMPLOYEEDETAILS FROMemployees 36 字符处理函数 函数结果CONCAT Hello World HelloWorldSUBSTR HelloWorld 1 5 HelloLENGTH HelloWorld 10INSTR HelloWorld W 6LPAD salary 10 24000RPAD salary 10 24000 TRIM H FROM HelloWorld elloWorld 37 数字函数 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的余数 38 使用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函数经常用于确定一个值是奇数还是偶数 39 日期的使用 Oracle数据库用内部数字格式存储日期 世纪 年 月 日 小时 分钟和秒 默认日期显示格式是DD MON RR SELECTlast name hire dateFROMemployeesWHERElast namelike G SYSDATE函数返回 Date TimeSYSDATE是一个日期函数 它返回当前数据库服务器的日期和时间 SELECTSYSDATEFROMDUAL 40 用日期计算 从日期加或者减一个数 结果是一个日期值 两个日期相减 得到两个日期之间的天数 用小时数除以24 可以加小时到日期上既然数据库以数字方式存储日期 你就可以用算术运算符进行计算 例如 加或减 你可以加或减数字常数以及日期 你可以进行下面的运算 运算结果说明date number日期加一个天数到一个日期上date number日期从一个日期上减一个天数date date天数用一个日期减另一个日期date number 24日期加一个小时数到一个日期上 41 日期函数 函数说明MONTHS BETWEEN两个日期之间的月数ADD MONTHS加日历月到日期NEXT DAY下个星期几是几号LAST DAY指定月的最后一天ROUND四舍五入日期TRUNC截断日期 42 日期函数 续 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被截断到最近的天 43 使用日期函数 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 44 使用日期函数 续 假定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 45 转换函数 数据类型转换 隐式数据类型转换 显式数据类型转换对于直接赋值 Oracle服务器能够自动地进行下面的转换 从到VARCHAR2orCHARNUMBERVARCHAR2orCHARDATENUMBERVARCHAR2DATEVARCHAR2对于表达式赋值 Oracle服务器能自动地进行下面的转换 从VARCHAR2orCHAR到DATE从VARCHAR2orCHAR到NUMBER 46 转换函数 续 显式数据类型转换SQL提供三种函数来从一种数据类型转换值到另一种 TO CHAR number date fmt nlsparams 转换一个数字或日期值为一个VARCHAR2字符串 带格式化样式fmt 数字转换 nlsparams参数指定下面的字符 它由数字格式化元素返回 小数字符99999 99前导009999本地货币符号L9999国际货币符号 9999如果忽略nlsparams或其它参数 该函数在会话中使用默认参数值 47 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函数用于日期转换时的目的相同 48 对日期使用TO CHAR函数 TO CHAR date format model 格式模板 必须加单引号 并且区分大小写 能够包含任一有效的日期格式元素 有一个fm元素用来删除填补的空 或者前导零 用一个逗号与日期值分开SELECTemployee id TO CHAR hire date MM YY Month HiredFROMemployeesWHERElast name Higgins 49 日期格式模板的元素YYYY数字全写年YEAR年的拼写MM月的两数字值MONTH月的全名DY周中天的三字母缩写DAY周中天的全名MON月的三字母缩写DD月的数字天 50 使用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 51 通用函数 这些函数可用于任意数据类型 并且适用于空值 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返回表达式列表中的第一个非空表达式 52 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的值 53 使用NVL2函数 NVL2函数检查第一个表达式 如果第一个表达式不为空 那么NVL2函数返回第二个表达式 如果第一个表达式为空 那么第三个表达式被返回 expr2expr1非空时的返回值语法NVL2 expr1 expr2 expr3 在语法中 expr1是可能包含空的源值或表达式expr3expr1为空时的返回值SelectNVL2 1 notnull null fromdual SelectNVL2 1 notnull null fromdual 54 使用NULLIF函数 NULLIF函数比较两个表达式 如果相等 函数返回空 如果不相等 函数返回第一个表达式 第一个表达式不能为NULL 语法NULLIF expr1 expr2 在语法中 expr1是对于expr2的被比较原值expr2是对于expr1的被比较原值 如果它不等于expr1 expr1被返回 Selectnullif abc abcd fromdual 55 使用COALESCE函数 COALESCE函数超过NVL函数的优点是COALESCE函数能够接受多个交替的值 如果第一个表达式非空 它返回该表达式 否则 它做一个保留表达式的结合 COALESCE函数返回列表中的第一个非空表达式 语法COALESCE expr1 expr2 exprn 在语法中 expr1如果它非空 返回该表达式expr2如果第一个表达式为空并且该表达式非空 返回该表达式exprn如果前面的表达式都为空 返回该表达式Selectcoalesce bca fromdual 56 条件表达式 在SQL语句中提供IF THEN ELSE逻辑的使用 两种用法 CASE表达式 DECODE函数CASE表达式CASEexprWHENcomparison expr1THENreturn expr1WHENcomparison expr2THENreturn expr2WHENcomparison exprnTHENreturn exprnELSEelse expr END 57 DECODE函数 DECODE col expression search1 result1 search2 result2 default DECODE函数在比较表达式 expression 和每个查找 search 值后解码表达式 如果表达式与查找相同 返回结果 如果省略默认值 当没有查找值与表达式相匹配时返回一个空值 58 多表显示数据 组函数合计数据 59 目标 完成本课后 您应当能够执行下列操作 写SELECT语句使用等值和非等值连接从多个表中访问数据 使用外连接查看不满足连接条件的数据 识别可用的组函数 描述组函数的使用 用GROUPBY子句分组数据 用HAVING子句包含或排除分组的行 60 笛卡尔乘积 笛卡尔乘积的形成 当 一个连接条件被遗漏时 一个连接条件不正确时 在第一个表中的所有行被连接到第二个表的所有行时 为了避免笛卡尔乘积的形成 在WHERE子句中应当总是包含正确的连接条件 61 用Oracle语法连接表 使用一个连接从多个表中查询数据SELECTtable1 column table2 columnFROMtable1 table2WHEREtable1 column1 table2 column2 在WHERE子句中写连接条件 当多个表中有相同的列名时 将表名作为列名的前缀定义连接 当数据从多表中查询时 要使用连接 join 条件 一个表中的行按照存在于相应列中的公值被连接到另一个表中的行 即 通常所说的主键和外键列 62 什么是等值连接 EMPLOYEESDEPARTMENTS 63 用等值连接返回记录 SELECTemployees employee id employees last name employees department id departments department id departments location idFROMemployees departmentsWHEREemployees department id departments department id 64 使用表别名 使用表别名简化查询 使用表别名改善性能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语句有效 65 多于两个表的连接 EMPLOYEESDEPARTMENTSLOCATIONS 66 非等值连接 EMPLOYEESJOB GRADES 67 用非等值连接返回记录 SELECTe last name e salary j grade levelFROMemployeese job gradesjWHEREe salaryBETWEENj lowest salANDj highest sal 68 外连接 DEPARTMENTSEMPLOYEES 69 外连接语法 你可以用一个外连接查看那些不满足连接条件的行 外连接运算符是加号 SELECTtable1 column table2 columnFROMtable1 table2WHEREtable1 column table2 column SELECTtable1 column table2 columnFROMtable1 table2WHEREtable1 column table2 column 用外连接返回不直接匹配的记录 如果在连接条件中使用外连接操作 缺少的行就可以被返回 操作符是一个在圆括号中的加号 它被放置在连接的缺少信息的一侧 为了使来自不完善表的一行或多行能够被连接 该操作符有产生一个或多个空行的作用 70 使用外连接 SELECTe last name e department id d department nameFROMemployeese departmentsdWHEREe department id d department id 71 什么是组函数 组函数操作行集 给出每组的结果EMPLOYEES 在EMPLOYEES表中的最高薪水 72 组函数的类型 AVG平均值 COUNT计数 MAX最大值 MIN最小值 STDDEV标准差 SUM合计 VARIANCE方差 73 组函数 续 每个函数接收一个参数 下面的表确定你可以在语法中使用的选项 函数说明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的方差 忽略空值 74 组函数的语法 SELECT column group function column FROMtable WHEREcondition GROUPBYcolumn ORDERBYcolumn 使用组函数的原则DISTINCT使得函数只考虑不重复的值 ALL使得函数考虑每个值 包括重复值 默认值是ALL 因此不需要指定 用于函数的参数的数据类型可以是CHAR VARCHAR2 NUMBER或DATE 所有组函数忽略空值 为了用一个值代替空值 用NVL NVL2或COALESCE函数 当使用GROUPBY子句时 Oracle服务器隐式以升序排序结果集 为了覆盖该默认顺序 DESC可以被用于ORDERBY子句 75 使用AVG SUM MIN MAX函数 你可以使用AVG和SUM用于数字数据SELECTAVG salary MAX salary MIN salary SUM salary FROMemployeesWHEREjob idLIKE REP 76 使用COUNT函数 COUNT 返回一个表中的行数COUNT函数有三中格式 COUNT COUNT expr COUNT DISTINCTexpr COUNT 返回表中满足SELECT语句标准的行数 包括重复行 包括有空值列的行 如果WHERE子句包括在SELECT语句中 COUNT 返回满足WHERE子句条件的行数 COUNT expr 返回在列中的由expr指定的非空值的数 COUNT DISTINCTexpr 返回在列中的由expr指定的唯一的非空值的数 注 expr为列名 77 组函数和Null值 所有组函数忽略列中的空值 在幻灯片的例子中 平均值只基于表中的那些COMMISSION PCT列的值有效的行的计算 平均值计算是用付给所有雇员的总佣金除以接受佣金的雇员数 4 SELECTAVG commission pct FROMemployees 78 在组函数中使用NVL函数 NVL函数强制组函数包括空值 在幻灯片的例子中 平均值被基于所有表中的行来计算 不管COMMISSION PCT列是否为空 平均值的计算是用付给所有雇员的总佣金除以公司的雇员总数 20 SELECTAVG NVL commission pct 0 FROMemployees 79 创建数据组 GROUPBY子句语法 用GROUPBY子句划分表中的行到较小的组中SELECTcolumn group function column FROMtable WHEREcondition GROUPBYgroup by expression ORDERBYcolumn 在语法中 group by expression指定那些用于将行分组的列 这些列的值作为行分组的依据 使用WHERE子句 你可以在划分行成组以前过滤行 在GROUPBY子句中必须包含列 在GROUPBY子句中你不能用列别名 默认情况下 行以包含在GROUPBY列表中的字段的升序排序 你可以用ORDERBY子句覆盖这个默认值 如果在SELECT子句中包含了组函数 就不能选择单独的结果 除非单独的列出现在GROUPBY子句中 如果你未能在GROUPBY子句中包含一个字段列表 你会收到一个错误信息 80 约束分组结果 HAVING子句 用HAVING子句约束分组 1 行被分组2 应用组函数3 匹配HAVING子句的组被显示SELECTcolumn group functionFROMtable WHEREcondition GROUPBYgroup by expression HAVINGgroup condition ORDERBYcolumn 81 使用HAVING子句 SELECTdepartment id MAX salary FROMemployeesGROUPBYdepartment idHAVINGMAX salary 10000 82 嵌套组函数 显示最大平均薪水SELECTMAX AVG salary FROMemployeesGROUPBYdepartment id 83 创建和管理表 84 目标 完成本课后 您应当能够执行下列操作 描述主要数据库对象 创建表 描述列定义时可用的数据类型 改变表的定义 删除 改名和截断表 85 数据库对象 表基本存储单元 由行和列组成视图逻辑地从一个或多个表中表示数据子集序列数字值发生器索引改善一些查询的性能同义词给对象可选择的名字 86 命名规则 表命名和列命名 必须以字母开始 必须是1 30个字符长度 只能包含A Z a z 0 9 和 同一个用户所拥有的对象之间不能重名 不能用Oracle服务器的保留字注 名字是大小写不敏感的 例如 EMPLOYEES与eMPloyees或eMpLOYEES作为同一个名字来处理 87 CREATETABLE语句 用户必须有 CREATETABLE权限 一个存储区域CREATETABLE schema table columndatatype DEFAULTexpr 必须指定 表名 列名 列数据类型和列的大小schema与所有者的名字一样table表的名字DEFAULTexpr指定默认值column列的名字datatype列的数据类型和长度 88 引用另一个用户的表 表属于另一个用户 不在该用户的方案中 在那些表名字的前面使用所有者的名字作为前缀如果一个表不属于本用户 那么 其所有者的名字必须放在表名的前面SELECT FROMuser b employees 89 创建表 创建表CREATETABLEdept deptnoNUMBER 2 dnameVARCHAR2 14 locVARCHAR2 13 确认表的创建DESCRIBEdept 90 Oracle数据库中的表 用户表 由用户创建和维护的表的集合 包含用户信息 数据字典 由Oracle服务器创建和维护的表的集合 包含数据库信息有四种数据字典视图 每一种有一个特定的前缀来反映其不同的目的 USER 这些视图包含关于用户所拥有的对象的信息 ALL 这些视图包含所有用户可访问的表 对象表和相关的表 的信息 DBA 这些视图是受限制的视图 它们只能被分配有DBA角色的用户所访问 V 这些视图是动态执行的视图 包含数据库服务器的性能 存储器和锁的信息 91 查询数据字典 查看本用户所拥有的表的名称SELECTtable nameFROMuser tables 查看本用户所拥有的不同的对象类型SELECTDISTINCTobject typeFROMuser objects 查看本用户所拥有的表 视图 同义词和序列SELECT FROMuser catalog 92 数据类型 数据类型说明VARCHAR2 size 可变长度的字符数据CHAR size 固定长度的字符数据NUMBER p s 可变长度的数字数据DATE日期和时间值LONG最大2G的可变长度字符数据CLOB最大4G的字符数据RAWandLONGRAW原始二进制数据BLOB最大4G的二进制数据BFILE最大4G的 存储在外部文件中的二进制数据ROWID一个64进制的数制系统 表示表中一行的唯一地址 93 用子查询创建表 该方法既可以创建表还可以将从子查询返回的行插入新创建的表中 CREATETABLEdept80ASSELECTemployee id last name salary 12ANNSAL hire dateFROMemployeesWHEREdepartment id 80 原则被创建的表要带指定的列名 并且由SELECT语句返回的行被插入到新表中 字段的定义只能包括列名和默认值 如果给出了指定的列 列的数目必须等于子查询的SELECT列表的列数目 如果没有给出了指定的列 表的列名应和子查询中的列名是相同的 完整性规则不会被传递到新表中 仅列的数据类型被定义 94 ALTERTABLE语句 用ALTERTABLE语句来 添加一个新列 修改一个已存在的列 为新列定义一个默认值 删除一个列添加列ALTERTABLEtableADD columndatatype DEFAULTexpr columndatatype 修改列ALTERTABLEtableMODIFY columndatatype DEFAULTexpr columndatatype 删除列ALTERTABLEtableDROP column 95 添加新列 用ADD字句添加列ALTERTABLEdept80ADD job idVARCHAR2 9 添加新列的原则你可以添加或修改列 你不能指定新添加的列的位置 新列将成为最后一列 96 修改列 可以改变列的数据类型 大小和默认值ALTERTABLEdept80MODIFY last nameVARCHAR2 30 对默认值的改变只影响后来插入表中的数据原则你可以增加宽度或一个数字列的精度 你可以增加数字列或字符列的宽度 你可以减少一个列的宽度 但仅在列中只包含空值或表中没有行时 你可以改变数据类型 但仅在列中只包含空值时 你可以转换一个CHAR列到VARCHAR2数据类型或转换一个VARCHAR2列到CHAR数据类型仅当列中只包含空值时 或者你不改变列的大小时 对默认值的改变仅影响以后插入的列 97 删除列 用DROPCOLUMN子句从表中删除列ALTERTABLEdept80DROPCOLUMNjob id 原则列可以有也可以没有数据 用ALTERTABLE语句 一次只能有一列被删除 表被修改后必须至少保留一列 一旦一列被删除 它不能再恢复 98 删除表 在表中的所有数据和结构都被删除 任何未决的事务都被提交 所有的索引被删除 你不能回退DROPTABLE语句DROPTABLEdept80 DROPTABLE语句删除Oracle表定义 当你删除一个表时 数据库丢失表中所有的数据 并且所有与其相关的索引也被删除 99 改变一个对象的名字 执行RENAME语句 改变一个表 视图 序列或同义词RENAMEdeptTOdetail dept 注 你必须是对象的所有者 100 截断表 TRUNCATETABLE语句 删除表中所有的行 释放该表所使用的存储空间TRUNCATETABLEdetail dept 不能回退用TRUNCATE删除的行 作为选择 可以用DELETE语句删除行 101 子查询 操纵数据 102 目标 完成本课后 您应当能够执行下列操作 描述子查询能够解决的问题类型 定义子查询 列出子查询的类型 写单行和多行子查询 描述每个DML语句 插入行到表中 更新表中的行 从表中删除行 控制事务 103 用子查询解决问题 用子查询解决问题假想你想要写一个查询来找出挣钱比Abel的薪水还多的人 为了解决这个问题 你需要两个查询 一个找出Abel的收入 第二个查询找出收入高于Abel的人 你可以用组合两个查询的方法解决这个问题 放置一个查询到另一个查询中 内查询或子查询返回一个值给外查询或主查询 使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值 104 子查询语法 SELECTselect listFROMtableWHEREexproperator SELECTselect listFROMtable 子查询 内查询 在主查询之前执行一次 子查询的结果被用于主查询 外查询 子查询可以被放在CREATEVIEW语句中 CREATETABLE语句 UPDATE语句 INSERT语句的INTO子句和UPDATE语句的SET子句中 105 使用子查询 SELECTlast nameFROMemployeesWHEREsalary SELECTsalaryFROMemployeesWHERElast name Abel 106 使用子查询的原则 子查询放在圆括号中 将子查询放在比较条件的右边 在子查询中的ORDERBY子句一般不需要 在单行子查询中用单行运算符 在多行子查询中用多行运算符 子查询的类型 单行子查询 从内SELECT语句只返回一行的查询 多行子查询 从内SELECT语句返回多行的查询 107 单行子查询 仅返回一行 使用单行比较符运算符含义 等于 大于 大于或等于不等于 108 执行单行子查询 SELECTlast name job id salaryFROMemployeesWHEREjob id SELECTjob idFROMemployeesWHEREemployee id 141 ANDsalary SELECTsalaryFROMemployeesWHEREemployee id 143 109 多行子查询 返回多于一行 使用多行比较符操作含义IN等于列表中的任何成员ANY比较子查询返回的每个值ALL比较子查询返回的全部值 110 在多行子查询中使用ANY运算符 SELECTemployee id last name job id salaryFROMemployeesWHEREsalary IT PROG 111 在多行子查询中使用ALL运算符 SELECTemployee id last name job id salaryFROMemployeesWHEREsalary IT PROG 112 数据操纵语言 数据操纵语言 DatamanipulationlanguageDML 是SQL的一个核心部分 当你做下面操作时 DML语句被执行 添加新行到表中 修改表中的行 删除表中的行 事务由DML语句的集合组成 它组成工作的逻辑单元 113 INSERT语句语法 使用INSERT语句添加新行到表中INSERTINTOtable column column VALUES value value 在语法中 table是表的名字column是表中的列名value是列的相应值 用该语法一次只能插入一行 114 插入新行 插入一个包含每一个列值的新行 值以表中列的默认顺序列表 在INSERT子句中字段可以随意列表INSERTINTOdepartments department id department name manager id location id VALUES 70 PublicRelations 100 1700 字符和日期要用单引号括起来 115 插入带空值的行 隐式方法 省略字段列表中的列INSERTINTOdepartments department id epartment name VALUES 30 Purchasing 显式方法 在VALUES子句中指定NULL关键字INSERTINTOdepartmentsVALUES 100 Finance NULL NULL