SQL学习社区

　　

Sybase ASE 12.5数据库实用特性的个人总结：

内容来自Sqlclub.cn

一：SQL Bisic

◆1：SQL(Structured Quary Language)特性： 内容来自Sqlclub.cn

a：标准化 copyright Sqlclub

b：非过程化的

c：可优化的 copyright Sqlclub

d：面向集合操作的

Sqlclub

◆2：ASE中的数据类型

a：Numberic

Sqlclub.cn

b：Character

copyright Sqlclub

c：Date/Time Sqlclub.cn

d：Lobs 内容来自Sqlclub.cn

◆3: convert(varchar, textColumn),如果不指定varchar(n)n那么默认是30

Sqlclub

◆4：where 在sql中的作用 Sqlclub.cn

a：过滤数据 copyright Sqlclub

b：做表连接(sql92以前) 本文来自Sqlclub

c：选择索引 Sqlclub学习社区

◆5：whare 和 having的区别 Sqlclub学习社区

where语句把过滤好的数据插入到work table中

本文来自Sqlclub

having语句从work table中对数据进行在过滤以得到最后的结果。 内容来自Sqlclub.cn

◆6：一个select语句的执行顺序 Sqlclub学习社区

a：from clause copyright Sqlclub

b：where clause Sqlclub

c：group by clause copyright Sqlclub

d：select clause copyright Sqlclub

e：having clause copyright Sqlclub

f：order by clause Sqlclub

◆7：Union VS Union All

copyright Sqlclub

a：Union 会把两个结果集排序，并且除去重复的元素（效率差，轻易不要用） Sqlclub.cn

b：Union All仅仅是把两个结果集合并，没有排序，也不去除重复元素（效率好） copyright Sqlclub

二：索引和查询参数 Sqlclub

◆1：ASE中有三种access数据方式

本文来自Sqlclub

a：clustered Index Sqlclub.cn

b：nonclustered Index

c：table scan 本文来自Sqlclub

◆2：Covered Query 内容来自Sqlclub.cn

一个Covered Query 仅仅从索引中得到数据，不用去扫描数据库表，这是最快的数据查询方式。

限制1：只能在selece中生效 内容来自Sqlclub.cn

限制2：所有被引用的列必须在同一个nonclustered index中

◆3：functional index

Sqlclub

在ASE15.0以后才被支持，也就是说在ASE15.0以前的版本，下列语句是可定不会用上索引的

sql 代码

select column1

from table1

where upper(column2) = 'IVANL'

◆4：如何查看执行计划

Sqlclub.cn

sql 代码 本文来自Sqlclub

set showplan on 内容来自Sqlclub.cn

go

内容来自Sqlclub.cn

your sql

Sqlclub.cn

go

本文来自Sqlclub

set showplan off

Sqlclub

go Sqlclub学习社区

◆5: 如何查看IO 本文来自Sqlclub

sql 代码 Sqlclub学习社区

set statistics io on

set statistics time on Sqlclub.cn

go 内容来自Sqlclub.cn

you sql Sqlclub.cn

go 本文来自Sqlclub

set statistics io off

set statistics time off

Sqlclub学习社区

go copyright Sqlclub

◆6：使用Index的建议 本文来自Sqlclub

a：使用那些经常在where语句中使用的字段做index 内容来自Sqlclub.cn

b：使index中包含的字段越少越好

内容来自Sqlclub.cn

c：drop掉没用的index Sqlclub.cn

三：表连接

本文来自Sqlclub

◆1：什么是表连接 Sqlclub

表连接是从多表中查询数据，或者是从一个表中多次取数据。 Sqlclub.cn

(A join is a Transanct-SQL operation than access rows from multi-tables or from a single talbe multi-times)

◆2：表连接的类别 Sqlclub.cn

a：inner join

Sqlclub学习社区

b：outer join

Sqlclub学习社区

c：cross join(full join) Sqlclub

◆3：ASE中不支持full join但是通过union可以模拟full join

sql 代码

copyright Sqlclub

select t1.colu1, t2.column2 本文来自Sqlclub

from t1, t2

where t1.id *= t2.id 内容来自Sqlclub.cn

union

本文来自Sqlclub

select t1.colu1, t2.column2 内容来自Sqlclub.cn

from t1, t2

where t1.id =* t2.id copyright Sqlclub

(不建议使用，效率很差) 本文来自Sqlclub

◆4：ASE中最多支持50个table做表连接，ASE的查询优化器做的不是很好，Sybase推荐join表不超过4个

◆5：数据库中有三种方式来实现表连接

内容来自Sqlclub.cn

a：nested loop join

Sqlclub学习社区

b：merge join

Sqlclub

c：hash join

(可以使用show plan来查看数据库选用哪种join来实现join语句) 本文来自Sqlclub

◆6：对表连接的建议： Sqlclub

a：用showplan 看使用了那种用join方式

Sqlclub.cn

b：在join的列上加Index Sqlclub.cn

c：把多表的join才分成几个小表的join

d：避免产生笛卡儿积

内容来自Sqlclub.cn

四：使用Case语句

◆1：case语句的两种形式 Sqlclub

sql 代码

a:

本文来自Sqlclub

case

Sqlclub

when search_condition then expression

[when search_condition then expression] copyright Sqlclub

[else exproestion]

Sqlclub.cn

end

b:

Sqlclub.cn

case expression

when expression then expression 本文来自Sqlclub

[when exproession then expression] 内容来自Sqlclub.cn

[else expression]

本文来自Sqlclub

end Sqlclub.cn

◆2：case的用途

a:decoding column

Sqlclub.cn

sql 代码 内容来自Sqlclub.cn

select cust_id, cust_name

本文来自Sqlclub

case cust_type

Sqlclub学习社区

when 'R' then 'Relation' Sqlclub学习社区

when 'I' then 'International' Sqlclub.cn

when 's' then 'Small'

Sqlclub

else 'Other' Sqlclub.cn

end as customer_type 内容来自Sqlclub.cn

b:conditionally displaying columns or values

Sqlclub学习社区

sql 代码

select title_id, total_sales,

case

Sqlclub学习社区

when total_sales > 5000 then 'hight' 本文来自Sqlclub

when total_sales < 100 then 'low' Sqlclub学习社区

else ' '

end as 'column' 本文来自Sqlclub

c:horizontal frequency table and summary calculation Sqlclub学习社区

sql 代码 Sqlclub学习社区

select sum(case type when 'adv' then 1 else 0 end ) as adv

, sum( case type when 'cus' then 1 else 0 end) as cus

内容来自Sqlclub.cn

from customer

Sqlclub学习社区

d:updating on variable conditions

Sqlclub学习社区

sql 代码 copyright Sqlclub

update customer

set cust_charge = cust_charte + case cust_type Sqlclub.cn

when 'd' then 1 Sqlclub.cn

when 'c' then 2

Sqlclub.cn

when 'e' then 3 Sqlclub.cn

else 0 Sqlclub学习社区

end

本文来自Sqlclub

[/code] Sqlclub.cn

e:rules and check constraints 内容来自Sqlclub.cn

[code]

create table cust_order_info Sqlclub学习社区

( Sqlclub.cn

order_num int, 本文来自Sqlclub

order_taker int,

copyright Sqlclub

order_date char(7) default

case 本文来自Sqlclub

when datepart(dw, getDate()) between 2 and 6 then 'weekday'

Sqlclub

else 'weekend'

本文来自Sqlclub

end

本文来自Sqlclub

)

五：事务和锁 copyright Sqlclub

◆1：ASE中有两种事务模式

本文来自Sqlclub

a: Chained Mode 本文来自Sqlclub

b：unChained Mode(Sybase默认) Sqlclub

unchained mode显示的开始一个事务，chained隐式的开始一个事务

unchained mode 使用'commint tran', 'rollback tran' 内容来自Sqlclub.cn

chained mode 使用'commint work ', 'rollback work' Sqlclub.cn

unchained mode 支持嵌套事务，chained mode不支持 内容来自Sqlclub.cn

◆2：Locking schema

Sqlclub学习社区

a: All pages table, will lock data and index as they are accessed(可以有clustered index)

b: A Datapages table will lock datpages as they are accessed, index will not be locked(无clustered index) copyright Sqlclub

c: A DataRow table will lock datpages as they are accessed, index will not be locked(无clustered index)

Sqlclub

◆3：Locking type

ASE中最重要的三种lock type是 copyright Sqlclub

a:shared locks(select , fetch)

copyright Sqlclub

b:update locks(fetch ,update, delete)

c:exclusive locks(insert , update, delete) Sqlclub.cn

◆4：隔离级别 copyright Sqlclub

ASE中一共有四种隔离级别

Sqlclub.cn

a:isolation level 0 (read uncommited),允许胀读 Sqlclub.cn

b:isolation level 1 (read comminted)(ASE DEFAULT), 不允许胀读 Sqlclub学习社区

c:isolation level 2 (repeatable read),可重复读

d:isolation level 3 (serializable), 不允许幻影读 Sqlclub.cn

sql 代码 内容来自Sqlclub.cn

set transaction isolation level {0|1|2|3} Sqlclub.cn

or Sqlclub学习社区

select ...

at isolation {0|1|2|3} Sqlclub学习社区

◇5：如何编写高效的transaction Sqlclub.cn

For OLTP transaction

a:使transaction尽可能的短 Sqlclub.cn

b:使用index来随机访问数据 本文来自Sqlclub

c:只有在必要的时候才使用transaction 内容来自Sqlclub.cn

d:选取合适的Lock type和隔离级别 本文来自Sqlclub

e:使用乐观锁

六：数据处理

Sqlclub.cn

◆1：除以0

使用coalesce()和nullif()

先使用nullif()把0转换成null,在用coalesce()处理null的情况

sql 代码 本文来自Sqlclub

select coalesce(total_sales/nullif(sales,0),0) Sqlclub

-- coalesce(ex1, ex2,ex3...)返回第一个不是Null的表达式

-- nullif(expre, value)如果expre=value,则返回null

◆2:找到重复的数据 Sqlclub.cn

sql 代码

select type, count(*)

from table

内容来自Sqlclub.cn

where .. Sqlclub.cn

group by type

having count(*) > 1

◆3:找出重复次数最多的数据 本文来自Sqlclub

sql 代码 内容来自Sqlclub.cn

select type, count(*)

本文来自Sqlclub

from table

Sqlclub

where ..

group by type

having count(*) = max(count(*)) Sqlclub

◆4:数据累加 Sqlclub.cn

java 代码 copyright Sqlclub

select t1.title_id, t1.advice, sum(t2.advice) as cumulative_total Sqlclub学习社区

from title t1, title t2 copyright Sqlclub

where t1.title_id >= t2.title_id

group by t1.title_id, t1.advice

◆5:ranking data Sqlclub学习社区

sql 代码 copyright Sqlclub

select rank = identity(10), title_id, total_sales Sqlclub.cn

into #top from titles

where ..

order by total_sales desc 本文来自Sqlclub

go

内容来自Sqlclub.cn

select * from #top 内容来自Sqlclub.cn

go

drop table #top

go

copyright Sqlclub

◆6:conver between julian Date and gregorian date

sql 代码 内容来自Sqlclub.cn

select datepart(yy, @date)*1000+datepart(dy, @date) as julina_date

内容来自Sqlclub.cn

select dateadd(dd, juliandate%1000, '12/31/'+convert(char(4),juliandate/1000 -1)) as gregorian_date

本文来自Sqlclub

◆7:计算本月有多少天 copyright Sqlclub

sql 代码

内容来自Sqlclub.cn

datepart(dd, 本文来自Sqlclub

dateadd(dd,-1 --last day of this month

本文来自Sqlclub

datead(mm,1 --add a month Sqlclub

dateadd(dd --

Sqlclub学习社区

, Sqlclub学习社区

1-datepart(dd,getdate() --1-today copyright Sqlclub

getDate())))) --get today

本文来自Sqlclub

◆8:是否是闰年 Sqlclub.cn

sql 代码

本文来自Sqlclub

select datepart(dy, '03/01/'||convert(char(4),datepart(yy,getdate())))

--= 61 是闰年 Sqlclub.cn

--= 60 不是闰年 Sqlclub

  内容来自Sqlclub.cn

  Sqlclub.cn

Sqlclub学习社区