SQL培训 第二讲.ppt

1、SQL培训 第二讲,2011年11月3日,主要内容：,作业分析讲评 SQL中的常用函数 聚合分析与分组 存储过程 SQL触发器,作业分析讲评,以下为一表名为“职工考核信息”的表，请按照要求使用SQL语言执行相应操作： 表一：职工考核信息表,表二：销售业绩明细表,（1）从表1中查询出“工号”、“姓名”和“职称”三列的所有数据行,作业分析讲评,以下为一表名为“职工考核信息”的表，请按照要求使用SQL语言执行相应操作： 表一：职工考核信息表,表二：销售业绩明细表,（2）从表1中查询出“出生年月”在19800101以后，而且“职称”为工程师或经济师的所有数据行,作业分析讲评,以下为一表名为“职工考核信

2、息”的表，请按照要求使用SQL语言执行相应操作： 表一：职工考核信息表,表二：销售业绩明细表,（3）向表1添加一条数据,作业分析讲评,以下为一表名为“职工考核信息”的表，请按照要求使用SQL语言执行相应操作： 表一：职工考核信息表,表二：销售业绩明细表,（4）从表1中查询出职工的考核成绩大于70分的部门名称，并按成绩高低排序,作业分析讲评,以下为一表名为“职工考核信息”的表，请按照要求使用SQL语言执行相应操作： 表一：职工考核信息表,表二：销售业绩明细表,（5）将表2的销售业绩按月份、销售数量和商品的名称排名（注：销售数量由大到小），并与表1进行关联，显示工号、姓名、职称（按工号进行排序）,

3、作业分析讲评,以下为一表名为“职工考核信息”的表，请按照要求使用SQL语言执行相应操作： 表一：职工考核信息表,表二：销售业绩明细表,（6）从表2中查询姓名为“王海”的职工，如果其某个月的某种商品销售数量超过100，则对其表1中的考核成绩加5分,作业分析讲评,以下为一表名为“职工考核信息”的表，请按照要求使用SQL语言执行相应操作： 表一：职工考核信息表,表二：销售业绩明细表,（7）将2月份电视机的销售数量超过50台的职工的姓名、性别和籍贯查询出来,作业分析讲评,以下为一表名为“职工考核信息”的表，请按照要求使用SQL语言执行相应操作： 表一：职工考核信息表,表二：销售业绩明细表,（8）查询出

4、1月份没有销售空调的销售人员的姓名、年龄和籍贯,一张表有一个id字段，是integer类型的，然后要你找出id第2大的记录,select*from(selectrow_number()over(orderbyiddesc)asseq,*fromt_scrap)twhereseq=2,关于group by表内容：2005-05-09 胜2005-05-09 胜2005-05-09 负2005-05-09 负2005-05-10 胜2005-05-10 负2005-05-10 负如果要生成下列结果, 该如何写sql语句? 胜 负2005-05-09 2 22005-05-10 1 2,select

5、 rq, sum(case when shengfu=胜 then 1 else 0 end)胜 ,sum(case when shengfu=负 then 1 else 0 end)负 from #tmp group by rq,表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。,select (case when ab then a else b end ) ,(case when bc then b esle c end) from table_name,有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语

6、文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。 显示格式： 语文 数学 英语 及格 优秀 不及格,select(case when 语文=80 then 优秀 when 语文=60 then 及格else 不及格) as 语文,(case when 数学=80 then 优秀 when 数学=60 then 及格else 不及格) as 数学,(case when 英语=80 then 优秀 when 英语=60 then 及格else 不及格) as 英语,from table,S

7、QL 函数,函数,函数执行,两种 SQL 函数,函数,单行函数,多行函数,字符函数,字符函数,LOWER UPPER INITCAP,CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE,大小写控制函数,字符控制函数,函数,结果,大小写控制函数,这类函数改变字符的大小写。,LOWER(SQL Course) UPPER(SQL Course) INITCAP(SQL Course),sql course SQL COURSE Sql Course,大小写控制函数,显示员工 Higgins的信息:,SELECT employee_id, last

8、_name, department_id FROM employees WHERE last_name = higgins; no rows selected,SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = higgins;,CONCAT(Hello, World) SUBSTR(HelloWorld,1,5) LENGTH(HelloWorld) INSTR(HelloWorld, W) LPAD(salary,10,*) RPAD(salary, 10, *) TRIM

9、(H FROM HelloWorld),HelloWorld Hello 10 6 *24000 24000* elloWorld,函数,结果,字符控制函数,这类函数控制字符:,SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, a) Contains a? FROM employees WHERE SUBSTR(job_id, 4) = REP;,字符控制函数,1,2,3,1,2,3,数字函数,ROUND: 四舍五入 ROUND(45.926,

10、 2)45.93 TRUNC: 截断 TRUNC(45.926, 2)45.92 MOD: 求余 MOD(1600, 300)100,SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;,ROUND 函数,DUAL 是一个伪表，可以用来测试函数和表达式。,1,2,3,3,1,2,SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;,TRUNC 函数,3,1,2,1,2,3,SELECT last_name, salary, MOD(

11、salary, 5000) FROM employees WHERE job_id = SA_REP;,MOD 函数,日期,Oracle 内部使用数字存储日期: 世纪,年,月,日,小时,分钟,秒。 默认的日期格式是 DD-MON-RR. 可以只指定年的后两位在20世纪存放21世纪的日期。 同样可以在21世纪存放20世纪的日期。,SELECT last_name, hire_date FROM employees WHERE last_name like G%;,日期,函数SYSDATE 返回: 日期 时间,日期的数学运算,在日期上加上或减去一个数字结果仍为日期。 两个日期相减返回日期之间相差的

12、天数。 可以用数字除24来向日期中加上或减去小时。,日期的数学运算,SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90;,日期函数,两个日期相差的月数,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,向指定日期中加上若干月数,指定日期的下一个日期,本月的最后一天,日期四舍五入,日期截断,函数,描述,MONTHS_BETWEEN (01-SEP-95,11-JAN-94),日期函数,ADD_MONTHS (1

13、1-JAN-94,6),NEXT_DAY (01-SEP-95,FRIDAY),LAST_DAY(01-FEB-95),19.6774194,11-JUL-94,08-SEP-95,28-FEB-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,日期函数,Assume SYSDATE = 25-JUL-95:,转换函数,隐性,显性,数据类型转换,隐式数据类型转换,Oracle 自动完成下列转

14、换：,VARCHAR2 or CHAR,源数据类型,目标数据类型,VARCHAR2 or CHAR,NUMBER,DATE,NUMBER,DATE,VARCHAR2,VARCHAR2,隐式数据类型转换,表达式计算中, Oracle 自动完成下列转换:,VARCHAR2 or CHAR,源数据类型,目标数据类型,VARCHAR2 or CHAR,NUMBER,DATE,显式数据类型转换,NUMBER,CHARACTER,TO_CHAR,TO_CHAR 函数对日期的转换,格式: 必须包含在单引号中而且大小写敏感。 可以包含任意的有效的日期格式。 可以使用 fm 去掉多余的空格或者前导零。 与日期指

15、用逗号隔开。,TO_CHAR(date, format_model),YYYY,日期格式的元素,YEAR,MM,MONTH,DY,DAY,2004,TWO THOUSAND AND FOUR,02,MON,MONDAY,JULY,MON,JUL,DD,02,日期格式的元素,时间格式 使用双引号向日期中添加字符 日期在月份中的位置,TO_CHAR 函数对日期的转换,SELECT last_name, TO_CHAR(hire_date, fmDD Month YYYY) AS HIREDATE FROM employees;,TO_CHAR 函数对数字的转换,下面是在TO_CHAR 函数中经常使

16、用的几种格式:,TO_CHAR(number, format_model),9,0,$,L,.,数字,零,美元符,本地货币符号,小数点,千位符,SELECT TO_CHAR(salary, $99,999.00) SALARY FROM employees WHERE last_name = Ernst;,TO_CHAR函数对数字的转换,TO_NUMBER 和 TO_DATE 函数,使用 TO_NUMBER 函数将字符转换成数字: 使用 TO_DATE 函数将字符转换成日期: 这些函数可以使用fx 修饰符。 n,TO_NUMBER(char, format_model),TO_DATE(cha

17、r, format_model),TO_NUMBER 和 TO_DATE 函数,使用 TO_NUMBER 函数将字符转换成数字: 使用 TO_DATE 函数将字符转换成日期: 这些函数可以使用 fx 修饰符。,TO_NUMBER(char, format_model),TO_DATE(char, format_model),RR 日期格式,当前年 1995 1995 2001 2001,日期 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95,RR 格式 1995 2017 2017 1995,YY 格式 1995 1917 2017 2095,当前的年份:,049

18、,049,5099,5099,The return date is in the current century,The return date is in the century after the current one,The return date is in the century before the current one,The return date is in the current century,指定的年份:,RR 日期格式,SELECT last_name, TO_CHAR(hire_date, DD-Mon-YYYY) FROM employees WHERE hi

19、re_date TO_DATE(01-Jan-90, DD-Mon-RR);,使用RR日期格式查找雇佣日期在1990年之前的员工， 在1999或现在使用下面的命令会产生相同的结果:,嵌套函数,单行函数可以嵌套。 嵌套函数的执行顺序是由内到外。,F3(F2(F1(col,arg1),arg2),arg3),步骤1 = 结果1,步骤2 =结果2,步骤3 =结果3,SELECT last_name, NVL(TO_CHAR(manager_id), No Manager) FROM employees WHERE manager_id IS NULL;,嵌套函数,通用函数,这些函数适用于任何数据类型

20、，同时也适用于空值： NVL (expr1, expr2) NVL2 (expr1, expr2, expr3) NULLIF (expr1, expr2) COALESCE (expr1, expr2, ., exprn),NVL 函数,将空值转换成一个已知的值： 可以使用的数据类型有日期、字符、数字。 函数的一般形式: NVL(commission_pct,0) NVL(hire_date,01-JAN-97) NVL(job_id,No Job Yet),SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (sa

21、lary*12*NVL(commission_pct, 0) AN_SAL FROM employees;,使用NVL函数,1,2,1,2,SELECT last_name, salary, commission_pct, NVL2(commission_pct, SAL+COMM, SAL) income FROM employees WHERE department_id IN (50, 80);,使用 NVL2 函数,1,2,1,2,SELECT first_name, LENGTH(first_name) expr1, last_name, LENGTH(last_name) expr

22、2, NULLIF(LENGTH(first_name), LENGTH(last_name) result FROM employees;,使用 NULLIF 函数,1,2,3,1,2,3,使用 COALESCE 函数,COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。 如果第一个表达式费空,则返回这个表达式，对其他的参数进行COALESCE 。,SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct;,使用 C

23、OALESCE 函数,条件表达式,在 SQL 语句中使用IF-THEN-ELSE 逻辑。 使用两种方法: CASE 表达式 DECODE 函数,CASE 表达式,在需要使用 IF-THEN-ELSE 逻辑时:,CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr END,SELECT last_name, job_id, salary, CASE job_id

24、WHEN IT_PROG THEN 1.10*salary WHEN ST_CLERK THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END REVISED_SALARY FROM employees;,CASE 表达式,下面是使用case表达式的一个例子：,DECODE 函数,在需要使用 IF-THEN-ELSE 逻辑时:,DECODE(col|expression, search1, result1 , search2, result2,., , default),DECODE 函数,SELECT last_name,

25、job_id, salary, DECODE(job_id, IT_PROG, 1.10*salary, ST_CLERK, 1.15*salary, SA_REP, 1.20*salary, salary) REVISED_SALARY FROM employees;,DECODE 函数,SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATE FROM employees WHER

26、E department_id = 80;,使用decode函数的一个例子：,聚合分析与分组,SQL提供了一些列的聚集函数对表中的数据进行统计分析，在SQL中可以使用分组命令将列中的数据按照一定的条件进行分组。一般只将聚合函数作用在满足条件的分组上。因此聚合函数与分组命令一般结合使用.常见的聚合函数有sum（），max（），min( ),avg( ),count( ) 常用的分组函数有GROUP BY(),存储过程,将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。,

27、存储过程的优点,1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。,创建存储过程,语法 CREATE PROC EDURE owner. procedure_name ; number parameter da

28、ta_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n ,参数 owner 拥有存储过程的用户 ID 的名称。owner 必须是当前用户的名称或当前用户所属的角色的名称。 procedure_name 新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。 ;number 是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。

29、例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。,参数 parameter 过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值，或者该值设置为等于另一个参数）。存储过程最多可以有 2.100 个参数。 使用 符号作为第一个字符来指定参数名称。参数名称必须符合

30、标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。 data_type 参数的数据类型。除 table 之外的其他所有数据类型均可以用作存储过程的参数。但是，cursor 数据类型只能用于 OUTPUT 参数。如果指定 cursor 数据类型，则还必须指定 VARYING 和 OUTPUT 关键字。对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。 VARYING 指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。 default 参数的

31、默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、 和 ）。 OUTPUT 表明参数是返回参数。该选项的值可以返回给 EXECUTE。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符,参数 n 表示最多可以指定 2.100 个参数的占位符。 RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION RECOMPILE 表明

32、SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。 ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 FOR REPLICATION 指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH

33、RECOMPILE 选项一起使用。 AS 指定过程要执行的操作。 sql_statement 过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。 n 是表示此过程可以包含多条 Transact-SQL 语句的占位符。,SQL触发器,触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。 CREATE PROCEDURE 或 CREATE TRIGGER 语句不能跨越批处理。即存储过程或触发器始终只能在一个批处理中创建并编译到一个执行计划中。 用触发器还可以强制执行业务规则 Microsoft SQ

34、L Server 2000 提供了两种主要机制来强制业务规则和数据完整性：约束和触发器。触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。唤醒调用触发器以响应 INSERT、UPDATE 或 DELETE 语句。触发器可以查询其它表，并可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误（例如，磁盘空间不足），则整个事务即自动回滚。,优点,触发器可通过数据库中的相关表实现级联更改；不过，通过级联引用完整性约束可以更有效地执行这些更改。 触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。 与

35、 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。 一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。,SQL触发器语法,CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT DELETE UPDATE WITH AP

36、PEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) updated_bitmask ) column_bitmask .n sql_statement .n ,参数,trigger_name 是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。 Table | view 是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。 WITH ENC

37、RYPTION 加密 syscomments 表中包含 CREATE TRIGGER 语句文本的条目。使用 WITH ENCRYPTION 可防止将触发器作为 SQL Server 复制的一部分发布。 AFTER 指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。 如果仅指定 FOR 关键字，则 AFTER 是默认设置。 不能在视图上定义 AFTER 触发器。,参数,INSTEAD OF 指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。 在表或视图上，每个 INSERT、UPDATE 或

38、DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有 INSTEAD OF 触发器的视图上定义视图。 INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果向指定了 WITH CHECK OPTION 选项的可更新视图添加 INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。 DELETE , INSERT , UPDATE 是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定

39、义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。 对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。,参数,WITH APPEND 指定应该添加现有类型的其它触发器。只有当兼容级别是 65 或更低时，才需要使用该可选子句。如果兼容级别是 70 或更高，则不必使用 WITH APPEND 子句添加现有类型的其它触发器（这是兼容级别设置为 70 或更高的 CREATE TRIGGER 的默认行为）。有关更

40、多信息，请参见 sp_dbcmptlevel。 WITH APPEND 不能与 INSTEAD OF 触发器一起使用，或者，如果显式声明 AFTER 触发器，也不能使用该子句。只有当出于向后兼容而指定 FOR 时（没有 INSTEAD OF 或 AFTER），才能使用 WITH APPEND。以后的版本将不支持 WITH APPEND 和 FOR（将被解释为 AFTER）。 NOT FOR REPLICATION 表示当复制进程更改触发器所涉及的表时，不应执行该触发器。 AS 是触发器要执行的操作。,参数,sql_statement 是触发器的条件和操作。触发器条件指定其它准则，以确定 DEL

41、ETE、INSERT 或 UPDATE 语句是否导致执行触发器操作。 当尝试 DELETE、INSERT 或 UPDATE 操作时，Transact-SQL语句中指定的触发器操作将生效。 触发器可以包含任意数量和种类的 Transact-SQL 语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的 Transact-SQL 语句常常包含控制流语言。CREATE TRIGGER 语句中使用几个特殊的表： * deleted 和 inserted 是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的

42、旧值或新值。例如，若要检索 deleted 表中的所有值，请使用： SELECT * FROM deleted * 如果兼容级别等于 70，那么在 DELETE、INSERT 或 UPDATE 触发器中，SQL Server 将不允许引用 inserted 和 deleted 表中的 text、ntext 或 image 列。不能访问 inserted 和 deleted 表中的 text、ntext 和 image 值。若要在 INSERT 或 UPDATE 触发器中检索新值，请将 inserted 表与原始更新表联接。当兼容级别是 65 或更低时，对 inserted 或 deleted 表中允许空值的text、ntext 或 image 列，将返回空值；如果这些列不可为空，则返回零长度字符串。 当兼容级别是 80 或更高时，SQL Server 允许在表或视图上通过 INSTEAD OF 触发器更新 text、ntext 或 image 列。,参数,n 是表示触发器中可以包含多条 Transact-SQL 语句的占位符。对于 IF UPDATE (column) 语句，可以通过重复 UPDATE (column)