复杂sql语句和存储过程

1、SQL语句,一、简单sql语句 二、复杂sql语句 三、综合应用 四、SQL编程 五、存储过程,内容大纲,基本结构 SELECT distinct *, column alias, FROM TABLE ORDER BY column SELECT: 选择要查询的数据列 *: 选择所有的列 COLUMN:选择的列名 FROM: 数据列所在的表 ALIAS: 给选定的列或表一个别名（sql和oracle区别）,简单的数据查询,查询结果排序 使用ORDER BY子句对查询返回的结果按一列或多列排序。ORDER BY子句的语法格式为：ORDER BY column_name ASC|DESC ,n其

2、中ASC表示升序，为默认值，DESC为降序。ORDER BY不能按ntext、text和image数据类型进行排序。例如：Select *FROM usertableORDER BY age desc,userid ASC另外，可以根据表达式进行排序。,简单的数据查询,distinct 删除所有重复的值，排序操作增加系统开销。注意 查表的前N行记录 select top n * from table_name select * from table_name where rownumn 查询表结构 desc table_name exec sp_help table_name,简单的数据查询,

3、DML语句 插入：INSERT 更新：UPDATE 删除：DELETE,简单的数据查询,DDL语句 Create Alter Drop Truncate (区别，立即提交、不回滚) 数据定义语言DDL用来创建数据库中的各种对象 -表、视图、索引、同义词、聚簇等如 CREATE TABLE/VIEW/INDEX/SYN/CLUSTER,简单的数据查询,DQL语言 DCL 数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制 数据库操纵事务发生的时间及效果，对数据库实行监视等。如： 1) GRANT：授权。 2) ROLLBACK WORK TO SAVEPOINT：回退到某一点。 回滚-

4、ROLLBACK 回滚命令使数据库状态回到上次最后提交的状态。其格式为： SQLROLLBACK; 3) COMMIT WORK：提交。 在数据库的插入、删除和修改操作时，只有当事务在提交到数据 库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看 到所做的事情，别人只有在最后提交完成后才可以看到。,简单的数据查询,操作符 算术操作符 关系操作符 比较操作符 逻辑操作符,简单的数据查询,算术操作符 加：+ 减：- 乘：* 除：/ Sql：% 取模 oracle：Mod,简单的数据查询,关系操作符 主要用于条件判断语句或用于where子串中，关系操作符检查条件和结果是否为true或fal

5、se 大于操作符 = 大于或等于操作符 = 等于操作符 != 不等于操作符 := 赋值操作符 （oracle） 日期、字符串比较例子,简单的数据查询,比较操作符 IS NULL 如果操作数为NULL返回TRUE LIKE 比较字符串值 (通配符，走索引？) BETWEEN 验证值是否在范围之内 IN,NOT IN,EXISTS,NOT EXISTS验证操作数在设定的一系列值中 在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT

6、,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1,简单的数据查询,逻辑操作符 AND 两个条件都必须满足 OR 只要满足两个条件中的一个 NOT 取反 优先（例）,简单的数据查询,操作数字型数据 SELECT FUNETION_NAME(PARAMETERS) FUNCTION 参数 结果 1.绝对值 S:select abs(-1) value O:select abs(-1) value

7、from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual 3.取整（小） S:select floor(-1.001) value O:select floor(-1.001) value from dual 4.取整（截取） S:select cast(-1.002 as int) value O:select trunc(-1.002) value from dual 5.四舍五入 S:select round(1.23456,4) value 1.23460O:select

8、 round(1.23456,4) value from dual,简单的数据查询,字符串处理 SUBSTRING，SUBSTR （区别） CONCAT() 字符串可以做最简单的“加”法，即合并运算：| 例： SELECT column1|.|column2 |$ FROM tablename STUFF() Replace() Len,datalength length lower,upper ltrim,rtrim ltrim,rtrim,trim,简单的数据查询,操作日期型数据 系统时间 S:select getdate() value O:select sysdate value fr

9、om dual 求日期 S:select convert(char(10),getdate(),20) value O:select trunc(sysdate) value from dual select to_char(sysdate,yyyy-mm-dd) value from dual 前后几日 直接与整数相加减 求时间 S:select convert(char(8),getdate(),108) value O:select to_char(sysdate,hh24:mm:ss) value from dual,简单的数据查询,空处理 NVL: 在数据列的值为空时（并非为零），以

10、真实的值进行替代处理 例： NVL (base_salary, 2000) 注：任何未被声明为NOT NULL 或PRIMARY KEY 的列都可包含空值，注意，空值就是空值，并不是值为零或空格，零是一个数值，而空格是一个字符。,简单的数据查询,数据类型转换 CAST ( expression AS data_type ) CONVERT (data_type(length), expression , style) (r9) CHAR | TO_DATE TO_NUMBER DATE | TO_CHAR NUMBER | TO_CHAR TO_DATE HEXTORAW CHARTOROWI

11、D,简单的数据查询,子查询 in,exists not in, not exists,复杂sql语句,结果的并、交、差运算 union union all UNION 会去掉结果集中的重复记录，而 UNION ALL 会返回合并后的全部结果集。 (a union b a中重复记录也去掉) Intersect minus （例）,复杂sql语句,集合函数 AVG求平均值 （整型） COUNT值的计数 MAX求最大值 （字符串） MIN求最小值 SUM求和,复杂sql语句,分组 group by,having GROUP BY的特征：能按列或表达式分组,一般同集合函数一起用,为每组产生一个值 ro

12、llup,cube 对于GROUP BY语句来说，只能返回对于每个GROUP BY分组的汇总数据。使用ROLLUP除了返回分组汇总数据外，还可以返回对于分组汇总的汇总值。CUBE操作除了提供ROLLUP的功能外，还可以对统计的其他维度进行汇总计算。 grouping 函数 （例子） compute by 可以统计排序中结果完全相同的列，统计值作为查询结果以附加行的形式显示,复杂sql语句,多表查询 自连接查询，对同一个表进行连接操作 内连接查询， 外连接查询， 交叉连接查询，也作无条件查询。 联合查询,复杂sql语句,自连接查询 一个表自己与自己建立连接称为自连接或自身连接 select x.

13、* from sclass x,sclass y where o=101 and x.degreey.degree and y.sno=9505201 and o=101 order by x.degree desc,复杂sql语句,内连接(INNER JOIN) 它只返回两个数据集合之间匹配关系的那些行.将位于两个互相交叉的数据集合中重叠部分以内的那些数据行连接起来 select 字段名列表 from 表名 inner join 表名 on 连接条件 where 条件表达式 等值连接 不等值连接 自然连接,复杂sql语句,外连接(OUTER JOIN) 左外连接(LEFT OUTER JOI

14、N或LEFT JOIN) 右外连接(RIGHT OUTER JOIN或RIGHT JOIN) 全外连接(FULL OUTER JOIN或FULL JOIN) 替换in，not in (例),复杂sql语句,交叉连接 交叉连接不使用任何连接条件来限制结果集合,将各表的记录以“笛卡尔”积的方式组合起来 select * from student,sclass select * from student a CROSS JOIN sclass ORDER BY a.sno,复杂sql语句,复制表结构 行列转换 找含匹配列的行 创建复杂的视图,综合应用,程序段 SQLSERVER BEGIN END

15、ORACLE declare 变量声明； begin 语句部分 EXCEPTION -exception statements end;,Sql编程,控制结构 顺序结构 选择结构 循环结构,Sql编程,选择结构 sqlserver if 条件 begin SQL语句 end else if begin SQL语句 end else begin SQL语句 End,Sql编程,oracle if then end if; if then else End if; if then elsif then end if; case语句,Sql编程,例子： declare szbz int set sz

16、bz=3 if szbz=1 select szbz=1 else if szbz=2 select szbz=2 else select szbz=3,Sql编程,set serveroutput on declare szbz int; begin szbz:=3; if szbz=1 then dbms_output.put_line(szbz=1); elsif szbz=2 then dbms_output.put_line(szbz=2); else dbms_output.put_line(szbz=3); end if; end; /,Sql编程,case语句 case nwh

17、en 1 then Action1; when 2 then Action2; when 3 then Action3; else ActionOther;end case; Case -CASE 表达式 when n = 1 then Action1;when n = 2 then Action2;when n = 3 then Action3;when ( n 3 and n 8 ) then Action4;else ActionOther;end case;,Sql编程,CASE_NOT_FOUND 异常 .p:=0; q:=0; r:=0; case when p = 1 then

18、Action1; when r = 2 then Action2; when q 1 then Action3; end case; exception when case_not_found then Dbms_Output.Put_Line ( Trapped:case_not_found );.,Sql编程,例子 declare i int; begin i:=2; case i when 0 then dbms_output.put_line(i=0); when 1 then dbms_output.put_line(i=1); else dbms_output.put_line(i

19、=2); end case; end; /,Sql编程,循环结构 sql while 语句 declare count int set count = 0 while(count 10) begin set count = count + 1 print count end 在循环中常用的语句有break和continue, break为跳出while,而continue为跳出当前循环，进入下一循环。 有时候也用到return和goto语句 Oracle PL/SQL EXIT 关键字等同。Oracle 没有 CONTINUE 关键字,Sql编程,ORACLE loop if then exi

20、t; end loop; loop exit when ; end loop; while loop end loop; for 变量 in 下界上界 loop end loop;,Sql编程,游标 定义 属性 打开 提取 关闭 参数化游标 游标变量,Sql编程,游标定义 sql DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n INSENSITIVE对基本表的修改并不影响游标提取的数据,默认可修改 SCROLL表明所有的提取操

21、作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE (+ -)、 ABSOLUTE(+ -)）都可用，如果不使用该保留字，那么只能进行NEXT 提取操作,Sql编程,oracle cursor 游标名 is select ; 属性 %FOUND: 判断当前游标是否指向有效的一条行记录. 是则返回TRUE, 否则返回FALSE %NOTFOUND: 与%FOUND 恰好相反. %ISOPEN: 判断游标是否打开, 打开则返回TRUE, 否则返回FALSE. %ROWCOUNT: 判断当前游标在所指向的结果集中提取的行数. 并非所有总记录数,Sql编程,游标打开 open游

22、标名; 提取游标 Fetch next from 游标名 into 变量(sql) Fetch 游标名 into 变量;(ora) 关闭游标 close 游标名; deallocate游标名 (sql用),Sql编程,例子 sql declare zffsdm nvarchar(20) -定义变量来保存ID号 declare mycursor cursor for select zffsdm from zb_zffs where kjnd=2009 and gsdm=000 -为所获得的数据集指定游标 open mycursor -打开游标 fetch next from mycursor i

23、nto zffsdm -开始抓第一条数据 while(fetch_status=0) -如果数据集里一直有数据 begin select zffsdm,zffsmc from zb_zffs where zffsdm = zffsdm and kjnd=2009 and gsdm=000 fetch next from mycursor into zffsdm -跳到下一条数据 end close mycursor -关闭游标 deallocate mycursor -删除游标,Sql编程,Oracle set serveroutput on declare szzffsdm varchar2

24、(20); -定义变量来保存ID号 szdm varchar2(20); szmc varchar2(20); cursor mycursor is select zffsdm from zb_zffs where kjnd=2008 and gsdm=111001 ; -为所获得的数据集指定游标 begin open mycursor; -打开游标 loop fetch mycursor into szzffsdm; exit when mycursor%notfound ; select zffsdm,zffsmc into szdm,szmc from zb_zffs where zff

25、sdm = szzffsdm and kjnd=2008 and gsdm=111001; dbms_output.put_line(szdm| = |szmc); end loop ; close mycursor; end;,Sql编程,参数化游标（oracle才有） 声明 cursor c_KMXX( cKMXZ char) is select kmdm,kmmc from gl_kmxx where kmxz= cKMXZ; 打开 open c_KMXX(1);,Sql编程,事务控制 事务的概念 事务可以看作是由对数据库的若干操作组成的一个单元，这些操作要么都完成，要么都取消，从而保证

26、数据满足一致性的要求。事务的一个典型例子是银行中的转帐操作，帐户A把一定数量的款项转到帐户B上，这个操作包括两个步骤，一个是从帐户A上把存款减去一定数量，二是在帐户B上把存款加上相同的数量。这两个步骤显然要么都完成，要么都取消，否则银行就会受损失。显然，这个转帐操作中的两个步骤就构成一个事务。,Sql编程,语法 oracle commit; rollback; savepoint 点名; rollback to savepoint 点名; sql Begin Transaction Commit Transaction | Rollback Transaction,Sql编程,事务设置及类型的

27、区别sqlserver 隐式事务（Set Implicit_Transactions On命令设置的事务类型 ） 显式事务 自动提交事务 （默认） Oracle中没有SQL Server的这些事务类型，缺省情况下任何一个DML语句都会开始一个事务，直到用户发出Commit或Rollback操作，这个事务才会结束，这与SQL Server的隐式事务模式相似。(锁问题),Sql编程,DDL语句对事务的影响 在Oracle中，执行DDL语句(如Create Table、Create View等)时，会在执行之前自动发出一个Commit命令，并在随后发出一个Commit或者Rollback命令，也就是

28、说，DDL会象如下伪码一样执行 Commit; DDL_Statement; If (Error) then Rollback; Else Commit; End if;,Sql编程,例子 Insert into some_table values(Before); Create table T(x int); Insert into some_table values(After); Rollback; 在SQL Server中，隐式事务时DDL语句对事务的影响与其他DML语句相同，也就是说，在DML语句发出之前或之后，都不会自动发出Commit命令。 用户断开数据库连接对事务的影响 Ora是commit，sql是rollback隐式有影响,Sql编程,异常处理 oracle 声明 系统预定义异常 如：被0除，约束破坏，数据类型转换失败等 用户自定义异常 declare epError Exception; Sqlserver2005 try. cache.方式,Sql编程,异常的抛出 用户自定义的异常由raise语句触发 如：raise epError; 系统预定义的异常自动触发 处理 Exception when 异常名1 then 处理语句序列; when 异常名2 then 处理语句序列; when others then 处理语句序列;,S