资源描述
Sybae数据库使用和存储过程编写总结张浩博客http:/zhanghao007008.blog.163.comsql语句部分1. 新型企业级智能数据管理系统-Sybase ASE 12.5（Adaptive Server Enterprise）2. * 17260, Cant run %1! from within a transaction.中间德 1 什么意思？3. 在创建存储过程 CREAT之前编写存储过程德 错误编号说明4. charindex(sa_role, show_role() 查看在show_role() 这个串中的sa_role在这个串中是第几个 字符5. begin/* 17230, You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure. */raiserror 17230return 2end其中的raiserror是什么意思6. trancount 这个是事务嵌套计数，它的值跟踪记录了事务嵌套的级次。他在第一个begin 突然语句后值为1。7. set chained off这是事务有关的语句set chained on之后，需要显式的commit；默认的是off，SQL92标准默认是on.如果是on,那么会为一个SQL语句块隐式开启一个事务。如果是off，除非你使用了begin tran，它不会主动为你开启一个事务。8. SET TRANSACTION ISOLATION LEVEL 1 设置事务的隔离1级水平9. 存储过程的递归调用 在存储过程内部实现了游标的编写，在游标中用EXEC 执行存储过程本身实现递归调用存储过程本身10. 存储过程的调用 exec retstat = sp_ha_check_certified sp_adduser, HA_CERTIFIED11. Sybase命令 1.disk mirror 创建在主设备发生故障时立即接替运行得软件镜像。2.disk refit 通过sysdevices中的信息重建master数据库的sysusages和sysdatabases系统表3.diskreinit 重建master数据库的sysdevices系统表。将disk reinit作为过程的一部分使用，以便恢复master数据库。4.disk remirror在磁盘镜像因镜像设备故障而停止或者被disk unmirror命令临时禁用后重新启动它。5.disk resize 动态添加Adaptive Server使用的设备的大小。例子：disk resizeName = “test_dev”Size = “10M”6.disk unmirror 挂起用disk mirror命令启动的磁盘镜像，以允许硬件维护或硬件设备的更改。7.drop database 删除一个或多个数据库。8.drop default 删除用户定义的缺省值。例子 ：删除数据库中用户定义的缺省datedefaultDrop default datedefault9.drop function 删除SQL函数10.drop index 从当前数据库的表中删除索引。例子：从authors表中删除au_id_indDrop index authors. au_id_ind11.drop procedure 删除存储过程。例子:删除存储过程shoindDrop procedure shoind12.drop role删除用户定义角色13.drop rule 删除用户定义的规则。14.drop table 从数据库中删除表定义及其所有的数据、索引、触发器和权限。15.drop trigger 删除触发器。16.drop view 从当前数据库中删除一个或多个视图。12. 日志空间满，引起无法给表添加新字段，日志满问题， 今天给一张表添加新字段，怎么也添加不上去，后来用sql语句写，看了报错信息才知道是日志空间满了。所以收集了一些资料，如下：SYBASE ASE的每一个数据库，无论是系统数据库（master,model, sybsystemprocs, tempdb），还是用户数据库，都有自己的transaction log，每个库都有syslogs表。Log记录用户对数据库修改的操作，所以如果不用命令清除， log会一直增长直至占满空间。清除log可用dump transaction 命令；或者开放数据库选项trunc log on chkpt，数据库会每隔一段间隔自动清除log。管理好数据库log是用户操作数据库必须考虑的一面。 Server Cache Memory中日志页总是先写于数据页：如果数据库开放trunc log on chkpt选项，则ASE在数据库系统执行checkpoint时自动清除log。但用户自己写入执行的checkpoint命令并不清除log，即使trunc log on chkpt选项开放。只有在trunc log on chkpt选项开放时，ASE自动执行checkpoint动作，才能自动清除log 。数据库的log是不断增长的，必须在它占满空间之前清除。前面已经讨论过，清除log可以开放数据库选项trunc log on chkpt，使数据库系统每隔一段时间间隔自动清除log，还可以执行命令dump transaction 来清除log.trunc log on chkpt 选项同dump transaction with truncate_only 命令一样，只是清除log而不保留log到备份设备上。所以如果只想清除log而不做备份，可以使用trunc log on chkpt 选项及dump transaction with truncate_only,dump transaction with no_log 命令。若想备份，应做dump transaction database_name to dumpdevice。1.dbcc checktable (syslogs) 可以看见日志情况：* 注意：日志段上保留的空间是 2 页 (0.03 MB)，0.16%。* 注意：日志段上的可用空间是 1250 页 (19.53 MB)，97.66%。表包含 1041 数据行。2.用快速方法来判断transaction log 满的程度。 select data_pgs(8，doampg) from sysindexes where id=83.只清除log不备份，开放trunc log on chkpt选项（或者右击数据库，选择属性，再选择选项）数据库每隔一段时间自动清除log。sp_dboption ttms_upload ,trunc,true例子：开启自动清理数据库功能，在checkpoint时清理USE mastergoEXEC sp_dboption smart,trunc log on chkpt,truegoUSE smartgoCHECKPOINT 清除日志（只有trunc log on chkpt打开时管用）go4.清除LOG日志dump transaction dbname with truncate_only6.使用delete table 要记录很多log，使用truncate table tablename 只记录空间回收情况，不记录表中每一行的操作。7.sp_helpdb 查看数据库中各个库的属性go13. 关于Create的使用 1.create function 通过将SQL包装加入到JAVA静态方法中,创建用户定义的函数.可返回一由该方法定义的值.2.create index 创建表中一个或多个列的索引.例子1.在authors表的au_id列上创建名为au_id_ind的索引:Create index au_id_ind on authors(au_id)例子2.在authors表的au_id列上创建名为au_id_ind的唯一聚族索引:Create unique clustered index au_id_ind on authors(au_id)例子3 在authors表的au_id和title_id列上创建名为ind1的索引Create index ind1 on authors (au_id, title_id)3.create plan 创建抽象计划例子 为指定查询创建抽象计划.Create plan “select * from titles ” “(t_scan titles)”4.create procedure 创建存储过程或可以带一个或多个用户提供的参数的扩展存储过程.例子 create procedure showed(name varchar(100)asselect * from authors where name=name下面是执行showid的可接受的语法形式”Execute showed titles或execute showed name=”tiltes”5.create role 创建用户定义角色.指定允许用于所创建的特定角色的口令有效期,最小口令长度和最大失败登陆次数.例子1 创建名为doctor_role的角色Create role doctor_role例子2. 创建名为doctor_role且口令为haha的角色Create role doctor_role with passwd “haha”例子3 设置intern_role的有效期Create role intern_role , with passwd “haha”, passwd ecpiration 6例子4 设置intern_role的最大失败登陆次数Create role intern_role , with passwd “haha”, max failed_logins 20例子5 设置intern_role的最短口令长度Create role intern_role , with passwd “haha”, min passwd length 06.create rule 为特定列或属于用户定义数据的任意列指定可接受值的域并创建访问规则.例子1 创建一个名为limit的规则,该规则限制advance的值小于1000Create rule linitAs advance 1000例子2 创建名为pubid_rule的规则,该规则限制pub_id的值为13,14或15Create rule pubid_ruleAs pub_id in (13,14,1514. case 可以用于任何可以使用值表达式的情况，case可以代替cusor游标（使用sybase12.0以上）例子1:把表查询结果中name为C的改为fffSELECT id ,case when name=C then fff else nameend from A注意: end不能忘, else name如果不要,其他的name都为null了例子2：使用临时表加case代替游标cusor优点：结构清晰、语句简单；普通sql不能胜任时使用；缺点：低效率；串行处理、不能利用数据库的多引擎并行处理结构，反复处理导致等多锁；case优点：可以替代游标（包含判断和分支逻辑）；缺点：代码更加简洁；总结：游标代码简单时可以很方便的使用case代替；若游标做了较多事情，可以分为几步使用多个sql语句，甚至多个case结合临时表，这样的开销比使用游标成千上万记录做循环处理效率还高多；15. coalesce 它的作用和isnull一样 Coalesce(expression,expression1,expression1.)例子: select id ,coalesce(name,yun,dddd),isnull(name,yun) from A说明:字段name为null,就用yun的字段代替,如果yun的字段也为null,用ddd代替16. 在数据库设备mydb 上加50M数据空间 Create database mydb on mydate =50-(在数据库设备mydb 上加50M数据空间，)log on mylog=10 -(在数据库设备mylog上加10M日志空间，)17. 用一个表的数据,更新另一个表的数据有A（id,部门）、B（id，部门）两个表，他们的id相互关联，现要用B的部门更新A的，请写一个sql语法实现。解决办法:-建表create table A (id int,name varchar(20)create table B(id int,name varchar(20)insert into A values(1,hao)insert into A values(2,xue)insert into A values(3,zhang)insert into A values(4,lei)insert into B values(1,C)insert into B values(2,D)insert into B values(3,F)select * from Aselect * from Bupdate Aset A.name=B.namefrom Bwhere A.ID=B.ID18. 选择前50条数据(sybase)得到前50条数据set rowcount 50select * from sysmessagesset rowcount 0注意:最后必须设置为0,否则下次无聊查询多少条,他都显示50条19. keyAscii码表目前计算机中用得最广泛的字符集及其编码，是由美国国家标准局(ANSI)制定的ASCII码（American Standard Code for Information Interchange，美国标准信息交换码），它已被国际标准化组织（ISO）定为国际标准，称为ISO 646标准。适用于所有拉丁文字字母，ASCII码有7位码和8位码两种形式。 因为1位二进制数可以表示（21=）2种状态：0、1；而2位二进制数可以表示（22）=4种状态：00、01、10、11；依次类推，7位二进制数可以表示（27=）128种状态，每种状态都唯一地编为一个7位的二进制码，对应一个字符（或控制码），这些码可以排列成一个十进制序号0127。所以，7位ASCII码是用七位二进制数进行编码的，可以表示128个字符。 第032号及第127号(共34个)是控制字符或通讯专用字符，如控制符：LF（换行）、CR（回车）、FF（换页）、DEL（删除）、BEL（振铃）等；通讯专用字符：SOH（文头）、EOT（文尾）、ACK（确认）等； 第33126号(共94个)是字符，其中第4857号为09十个阿拉伯数字；6590号为26个大写英文字母，97122号为26个小写英文字母，其余为一些标点符号、运算符号等。 注意：在计算机的存储单元中，一个ASCII码值占一个字节(8个二进制位)，其最高位(b7)用作奇偶校验位。所谓奇偶校验，是指在代码传送过程中用来检验是否出现错误的一种方法，一般分奇校验和偶校验两种。奇校验规定：正确的代码一个字节中1的个数必须是奇数，若非奇数，则在最高位b7添1；偶校验规定：正确的代码一个字节中1的个数必须是偶数，若非偶数，则在最高位b7添1。 为了便于查询，以下列出ASCII码表：常用ASCII 码对照表 第128255号为扩展字符（不常用），如需要请下载：完整的8位ASCII字符表 ASCII码键盘ASCII 码键盘ASCII 码键盘ASCII 码键盘27ESC32SPACE33!3435#36$37%38&3940(41)42*43+4445-46.47/48049150251352453554655756857958:59;6063?6465A66B67C68D69E70F71G72H73I74J75K76L77M78N79O80P81Q82R83S84T85U86V87W88X89Y90Z9192939495_9697a98b99c100d101e102f103g104h105i106j107k108l109m110n111o112p113q114r115s116t117u118v119w120x121y122z123124|125126Chr(32) Chr(33) !Chr(34) Chr(35) #Chr(36) $Chr(37) %Chr(37) %Chr(38) &Chr(39) Chr(40) (Chr(41) )Chr(42) *Chr(42) *Chr(43) +Chr(44) ,Chr(45) -Chr(46) .Chr(47) /Chr(47) /Chr(48) 0Chr(49) 1Chr(50) 2Chr(51) 3Chr(52) 4Chr(52) 4Chr(53) 5Chr(54) 6Chr(55) 7Chr(56) 8Chr(57) 9Chr(57) 9Chr(58) :Chr(59) ;Chr(60) Chr(62) Chr(63) ?Chr(64) Chr(65) AChr(66) BChr(67) CChr(67) CChr(68) DChr(69) EChr(70) FChr(71) GChr(72) HChr(72) HChr(73) IChr(74) JChr(75) KChr(76) LChr(77) MChr(77) MChr(78) NChr(79) OChr(80) PChr(81) QChr(82) RChr(82) RChr(83) SChr(84) TChr(85) UChr(86) VChr(87) WChr(87) WChr(88) XChr(89) YChr(90) ZChr(91) Chr(92) Chr(92) Chr(93) Chr(94) Chr(95) _Chr(96) Chr(97) aChr(97) aChr(98) bChr(99) cChr(100) dChr(101) eChr(102) fChr(102) fChr(103) gChr(104) hChr(105) iChr(106) jChr(107) kChr(107) kChr(108) lChr(109) mChr(110) nChr(111) oChr(112) pChr(112) pChr(113) qChr(114) rChr(115) sChr(116) tChr(117) uChr(117) uChr(118) vChr(119) wChr(120) xChr(121) yChr(122) zChr(122) zChr(123) Chr(124) |Chr(125) Chr(126) Chr(127) Chr(127) Chr(128) ?Chr(129) Chr(130) Chr(131) 20. If判断的使用方法1 if loginame = guest and not exists(select namefrom master.dbo.syslogins (index ncsyslogins)where name = loginame)使用if 判断变量 和是否存在21. If判断的使用方法 if suid is NULL22. Select语句的Where的使用1select grpid = gid from sysusers where name = grpname and ( (uid = 0) or (uid between mingroupid and maxgroupid)and not exists (select name from master.dbo.syssrvroles where name = grpname)“灵活”使用Where 中的条件 可以使用where1=2 查询处表格的各个列的详细信息23. SQL中CONVERT转化函数的用法（时间的用法）安装 SYBASE 在线帮助在安装好 SYBASE 后，在安装目录下有一文件：./scripts/ins_syn_sql，在服务器上执行该脚本：Sybase for Unix版： ./isql -Usa -P -i./scripts/ins_syn_sqlSybase for Winnt版： isql -Usa -P -isybasescriptsins_syn_sql执行完毕后，即可在任意的 SYBASE 客户端上连接上 SQL SERVER ，在线取得任意命令的帮助： sp_syntax 关键字如： sp_syntax alter 即可列出所有包含alter字符的命令-Style(2位表示年份)|Style(4位表示年份)|输入输出格式-|0or100|monddyyyyhh:miAM(或PM)-1|101|mm/dd/yy-2|102|yy-mm-dd-3|103|dd/mm/yy-4|104|dd-mm-yy-5|105|dd-mm-yy-6|106|ddmonyy-7|107|mondd,yy-8|108|hh:mm:ss-|9or109|monddyyyyhh:mi:ss:mmmmAM(或PM)-10|110|mm-dd-yy-11|111|yy/mm/dd-12|112|yymmdd-|13or113|ddmonyyyyhh:mi:ss:mmm(24小时制)-14|114|hh:mi:ss:mmm(24小时制)-|20or120|yyyy-mm-ddhh:mi:ss(24小时制)-|21or121|yyyy-mm-ddhh:mi:ss:mmm(24小时制)例select convert(char(10),getdate(),111)+ +convert(char(10),getdate(),108)结果：2009/04/01 14:06:53 24. 在sql语句中替换Not In 的方法 前言： 今天在写一条sql查询语句，其需要从一个表A中返回所有A不再表B中的结果集，当然，这种实现最方便的方法就是用NOT IN。 如：select a.* from a where a.id not in (select id from b where.) 我们大家都知道很多sql方面的文章都建议大家尽量不要使用NOT IN的方法，因为这种方法的效率不高。那有没有替代的办法呢？（声明因为当时的情况要求不能使用储存过程，所以只有写sql语句）和同事实验了一下，结果用以下方法实现了。 目的： 替换NOT IN 方法。 说明： 在单条SQL语句中，不使用储存过程，不使用临时表。使用存储过程和临时表不再本文的讨论范围中。 实现： 例： 表aa：结构 idvalue 1a 2 b 3 c 4 d 5 e 6 f - 表bb:结构 id 2 4 6 现在我要取表aa里的所有字段，条件是aa的id值不在bb的id值当中（not in）。也就是应该返回所有id为奇数的字段 使用NOT IN的SQL: select * from aa where id not in(select id from bb) 就一条语句，简单明了，可惜效率不高，而且公司规范要求尽量不用NOT IN，害我费了好大事crying 改造后的SQL: select cc.id,cc.value from (select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id) as cc where cc.tempcolum is null 解释一下。在开始时候我最早想用内联表的方式，可是无论如何也每找到一个好的办法，干脆就是实现不了。（大家有好办法指教先。） 后来自己考虑了一下NOT IN的逻辑，A NOT IN B就是说A是主体，B起到的之不过是一个判断作用，我们可以先把所有符合条件的A记录全部查询出来而不管他是否属于B，然后再从这里剔除值同时属于B的部分。 Select aa.* from aa 但是仅仅这样是不够的，我们无法利用这个返回的结果集判断是否属于B并排除它，为此，我想到构造一个临时的列，这个列的值应该是在A的结果集范围内，所有在B中的值。而这个结果集的主体应该是所有满足先决条件的A，然后加上满足条件的B，而不满足条件的B值则不再考虑范围内，所以用了left join。 这一段是关键，不知道我阐述清楚了没有，没明白的继续看 于是就出来这一句。 select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id 没看明白上面的看结果集就明白了 id value tempcolum - 1a NULL 2b 2 3c NULL 4d 4 5e NULL 6f 6 看到这个结果集我想大家都明白我的意思了吧。对了，我们就是要对这个结果集进行二次操作。 相信大家都看到了，生成的这个结果集包含了所有符合条件的表aa字段和bb的id，如果aa中的值在bb中，则tempcolum的值就不会为null，如果不在就是null，这样我们只需要从这个结果集里查询所有tempcolum值为null的就可以满足我们的要求了 所以最终的sql出来了 select cc.id,cc.value from (select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id) as cc where cc.tempcolum is null 我们只需要id和value两个字段，其他的就不要了。 结果 id value - 1a 3c 5 e ok，实现了，希望对大家有帮助。例：select top 1 -v_cashbox_now_count=convert(varchar(20),c.left_count) , v_cashbox_type_id=c.cashbox_type_id, v_cashbox_type_name=t.cashbox_type_name, v_cashbox_parvalue=isnull(convert(varchar(20),c.parvalue),-), v_cashbox_content=isnull(convert(varchar(20), d.content),-), v_cashbox_now_count=isnull(convert(varchar(20), c.left_count),-) from SMT_cashbox_use_record c left join SMT_cashbox_define d on c.cashbox_id=d.cashbox_id left join SMT_cashbox_type_define t on d.cashbox_type_id=t.cashbox_type_id where c.station_telecode=station_telecode and c.terminal_id=terminal_id and c.cashbox_id=v_cashbox_id and c.use_flag=1 order by date_time desc 25. SQL中IN,NOT IN,EXISTS,NOT EXISTS的用法和差别SQL中IN,NOT IN,EXISTS,NOT EXISTS的用法和差别: IN:确定给定的值是否与子查询或列表中的值相匹配。 IN 关键字使您得以选择与列表中的任意一个值匹配的行。 当要获得居住在 California、Indiana 或 Maryland 州的所有作者的姓名和州的列表时，就需要下列查询： SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5 然而，如果使用 IN，少键入一些字符也可以得到同样的结果： SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID IN (1, 4, 5) IN 关键字之后的项目必须用逗号隔开，并且括在括号中。 下列查询在 titleauthor 表中查找在任一种书中得到的版税少于 50% 的所有作者的 au_id，然后从 authors 表中选择 au_id 与 titleauthor 查询结果匹配的所有作者的姓名： SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE royaltyper 50) 结果显示有一些作者属于少于 50% 的一类。 NOT IN:通过 NOT IN 关键字引入的子查询也返回一列零值或更多值。 以下查询查找没有出版过商业书籍的出版商的名称。 SELECT pub_name FROM publishers WHERE pub_id NOT IN (SELECT pub_id FROM titles WHERE type = business) 使用 EXISTS 和 NOT EXISTS 引入的子查询可用于两种集合原理的操作：交集与差集。两个集合的交集包含同时属于两个原集合的所有元素。 差集包含只属于两个集合中的第一个集合的元素。 EXISTS:指定一个子查询，检测行的存在。 本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名： SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = business) SELECT distinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = business) 两者的区别: EXISTS:后面可以是整句的查询语句如:SELECT * FROM titles IN:后面只能是对单列:SELECT pub_id FROM titles NOT EXISTS: 例如，要查找不出版商业书籍的出版商的名称： SELECT pub_name FROM publishers WHERE NOT EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = business) 下面的查询查找已经不销售的书的名称: SELECT title FROM titles WHERE NOT EXISTS (SELECT title_id FROM sales WHERE title_id = titles.title_id)26. Sql2000字符串函数datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格 substring(expression,start,length) 不多说了,取子串 right(char_expr,int_expr) 返回字符串右边int_expr个字符 字符操作类 upper(char_expr) 转为大写 lower(char_expr) 转为小写 space(int_expr) 生成int_expr个空格 replicate(char_expr,int_expr)复制字符串int_expr次 reverse(char_expr) 反转字符串 stuff(char_expr1,start,length,char_expr2) 将字符串char_expr1中的从 start开始的length个字符用char_expr2代替 ltrim(char_expr) rtrim(char_expr) 取掉空格 ascii(char) char(ascii) 两函数对应,取ascii码,根据ascii吗取字符 replace使用规则：repalce（要置换的字段，需要替代的字符，替换后的字符）如将bankmember表 intro字段的 单引号，替换为'update bankmember set intro=replace(cast(intro as varchar(8000),')字符串查找 charindex(char_expr,expression) 返回char_expr的起始位置 patindex(%pattern%,expression) 返回指定模式的起始位置,否则为0 注：varchar长度问题，sybase11.9最长是255 sybase12.5最长超过255，最大是800027. Sql2000数学函数abs(numeric_expr) 求绝对值 ceiling(numeric_expr) 取大于等于指定值的最小整数 exp(float_expr) 取指数 floor(numeric_expr) 小于等于指定值得最大整数 pi() 3.1415926. power(numeric_expr,power) 返回power次方 rand(int_expr) 随机数产生器 round(numeric_expr,int_expr) 安int_expr规定的精度四舍五入 sign(int_expr) 根据正数,0,负数,返回+1,0,-1 sqrt(float_expr) 平方根 28. Sql2000日期函数getdate() 返回日期 datename(datepart,date_expr) 返回名称如 June datepart(datepart,date_expr) 取日期一部份 datediff(datepart,date_expr1.dateexpr2) 日期差 例：datediff(s,newtime,oldtime) 这个函数的结果是用oldtime的时间减掉newtime的时间,所得到的是秒数. datediff(d,newtime,oldtime)此处也可以改成d,得到的是天数dateadd(datepart,number,date_expr) 返回日期加上 number 上述函数中datepart的 写法 取值和意义 yy 1753-9999 年份 qq 1-4 刻 mm 1-12 月 dy 1-366 日 dd 1-31 日 wk 1-54 周 dw 1-7 周几 hh 0-23 小时 mi 0-59 分钟 ss 0-59 秒 ms 0-999 毫秒 日期转换 convert() 返回系统日志中的一部分年 year(getdate() 月 month(getdate() 日 day(getdate()29. 返回20071211073305926格式的时间declareSELECT a=substring(convert(char(10),getdate(),112),1,8)+substring(convert(varchar,getdate(),8),1,2)+substring(convert(varchar,getdate(),8),4,2)+substring(convert(varchar,getdate(),8),7,2)+substring(convert(varchar,getdate(),9),22,3)30. Sql2000系统函数suser_name()用户登录名u
展开阅读全文