一:SQL Bisic
◆1:SQL(Structured Quary Language)特性:
a:標准化
b:非過程化的
c:可優化的
d:面向集合操作的
◆2:ASE中的數據類型
a:Numberic
b:Character
c:Date/Time
d:Lobs
◆3: convert(varchar, textColumn),如果不指定varchar(n)n那麼默認是30
◆4:where 在sql中的作用
a:過濾數據
b:做表連接(sql92以前)
c:選擇索引
◆5:whare 和 having的區別
where語句把過濾好的數據插入到work table中
having語句從work table中對數據進行在過濾以得到最後的結果。
◆6:一個select語句的執行順序
a:from clause
b:where clause
c:group by clause
d:select clause
e:having clause
f:order by clause
◆7:Union VS Union All
a:Union 會把兩個結果集排序,並且除去重復的元素(效率差,輕易不要用)
b:Union All僅僅是把兩個結果集合並,沒有排序,也不去除重復元素(效率好)
二:索引和查詢參數
◆1:ASE中有三種Access數據方式
a:clustered Index
b:nonclustered Index
c:table scan
◆2:Covered Query
一個Covered Query 僅僅從索引中得到數據,不用去掃描數據庫表,這是最快的數據查詢方式。
限制1:只能在selece中生效
限制2:所有被引用的列必須在同一個nonclustered index中
◆3:functional index
在ASE15.0以後才被支持,也就是說在ASE15.0以前的版本,下列語句是可定不會用上索引的
sql 代碼
select column1
from table1
where upper(column2) = 'IVANL'
◆4:如何查看執行計劃
sql 代碼
set showplan on
go
your sql
go
set showplan off
go
◆5: 如何查看IO
sql 代碼
set statistics io on
set statistics time on
go
you sql
go
set statistics io off
set statistics time off
go
◆6:使用Index的建議
a:使用那些經常在where語句中使用的字段做index
b:使index中包含的字段越少越好
c:drop掉沒用的index
三:表連接
◆1:什麼是表連接
表連接是從多表中查詢數據,或者是從一個表中多次取數據。
(A join is a Transanct-SQL Operation than Access rows from multi-tables or from a single talbe multi-times)
◆2:表連接的類別
a:inner join
b:outer join
c:cross join(full join)
◆3:ASE中不支持full join但是通過union可以模擬full join
sql 代碼
select t1.colu1, t2.column2
from t1, t2
where t1.id *= t2.id
union
select t1.colu1, t2.column2
from t1, t2
where t1.id =* t2.id
(不建議使用,效率很差)
◆4:ASE中最多支持50個table做表連接,ASE的查詢優化器做的不是很好,Sybase推薦join表不超過4個
◆5:數據庫中有三種方式來實現表連接
a:nested loop join
b:merge join
c:hash join
(可以使用show plan來查看數據庫選用哪種join來實現join語句)
◆6:對表連接的建議:
a:用showplan 看使用了那種用join方式
b:在join的列上加Index
c:把多表的join才分成幾個小表的join
d:避免產生笛卡兒積
四:使用Case語句
◆1:case語句的兩種形式
sql 代碼
a:
case
when search_condition then expression
[when search_condition then expression]
[else exproestion]
end
b:
case expression
when expression then expression
[when exproession then expression]
[else expression]
end
◆2:case的用途
a:decoding column
sql 代碼
select cust_id, cust_name
case cust_type
when 'R' then 'Relation'
when 'I' then 'International'
when 's' then 'Small'
else 'Other'
end as customer_type
b:conditionally displaying columns or values
sql 代碼
select title_id, total_sales,
case
when total_sales > 5000 then 'hight'
when total_sales < 100 then 'low'
else ' '
end as 'column'
c:horizontal frequency table and summary calculation
sql 代碼
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
from customer
d:updating on variable conditions
sql 代碼
update customer
set cust_charge = cust_charte + case cust_type
when 'd' then 1
when 'c' then 2
when 'e' then 3
else 0
end
[/code]
e:rules and check constraints
[code]
create table cust_order_info
(
order_num int,
order_taker int,
order_date char(7) default
case
when datepart(dw, getDate()) between 2 and 6 then 'weekday'
else 'weekend'
end
)
五:事務和鎖
◆1:ASE中有兩種事務模式
a: Chained Mode
b:unChained Mode(Sybase默認)
unchained mode顯示的開始一個事務,chained隱式的開始一個事務
unchained mode 使用'commint tran', 'rollback tran'
chained mode 使用'commint work ', 'rollback work'
unchained mode 支持嵌套事務,chained mode不支持
◆2:Locking schema
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)
c: A DataRow table will lock datpages as they are Accessed, index will not be locked(無clustered index)
◆3:Locking type
ASE中最重要的三種lock type是
a:shared locks(select , fetch)
b:update locks(fetch ,update, delete)
c:exclusive locks(insert , update, delete)
◆4:隔離級別
ASE中一共有四種隔離級別
a:isolation level 0 (read uncommited),允許脹讀
b:isolation level 1 (read comminted)(ASE DEFAULT), 不允許脹讀
c:isolation level 2 (repeatable read),可重復讀
d:isolation level 3 (serializable), 不允許幻影讀
sql 代碼
set transaction isolation level {0|1|2|3}
or
select ...
at isolation {0|1|2|3}
◇5:如何編寫高效的transaction
For OLTP transaction
a:使transaction盡可能的短
b:使用index來隨機訪問數據
c:只有在必要的時候才使用transaction
d:選取合適的Lock type和隔離級別
e:使用樂觀鎖
六:數據處理
◆1:除以0
使用coalesce()和nullif()
先使用nullif()把0轉換成null,在用coalesce()處理null的情況
sql 代碼
select coalesce(total_sales/nullif(sales,0),0)
-- coalesce(ex1, ex2,ex3...)返回第一個不是Null的表達式
-- nullif(expre, value)如果expre=value,則返回null
◆2:找到重復的數據
sql 代碼
select type, count(*)
from table
where ..
group by type
having count(*) > 1
◆3:找出重復次數最多的數據
sql 代碼
select type, count(*)
from table
where ..
group by type
having count(*) = max(count(*))
◆4:數據累加
Java 代碼
select t1.title_id, t1.advice, sum(t2.advice) as cumulative_total
from title t1, title t2
where t1.title_id >= t2.title_id
group by t1.title_id, t1.advice
◆5:ranking data
sql 代碼
select rank = identity(10), title_id, total_sales
into #top from titles
where ..
order by total_sales desc
go
select * from #top
go
drop table #top
go
◆6:conver between julian Date and gregorian date
sql 代碼
select datepart(yy, @date)*1000+datepart(dy, @date) as julina_date
select dateadd(dd, juliandate%1000, '12/31/'+convert(char(4),juliandate/1000 -1)) as gregorian_date
◆7:計算本月有多少天
sql 代碼
datepart(dd,
dateadd(dd,-1 --last day of this month
datead(mm,1 --add a month
dateadd(dd --
,
1-datepart(dd,getdate() --1-today
getDate())))) --get today
◆8:是否是閏年
sql 代碼
select datepart(dy, '03/01/'||convert(char(4),datepart(yy,getdate())))
--= 61 是閏年
--= 60 不是閏年