公司组织SQL水平考试,看了写教材,写了点总结发上来跟大家分享。
我公司使用的是Sybase ASE12.5,所以下面的一些特性是针对Sybase ASE的。
一: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以前的版本,下列语句是可定不会用上索引的
- select column1
- from table1
- where upper(column2) = 'IVANL'
4:如何查看执行计划
- set showplan on
- go
- your sql
- go
- set showplan off
- go
5: 如何查看IO
- 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
- 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语句的两种形式
- 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
- 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
- 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
- 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
- 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), 不允许幻影读
- 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的情况
- select coalesce(total_sales/nullif(sales,0),0)
- -- coalesce(ex1, ex2,ex3...)返回第一个不是Null的表达式
-- nullif(expre, value)如果expre=value,则返回null
2:找到重复的数据
- select type, count(*)
- from table
- where ..
- group by type
- having count(*) > 1
3:找出重复次数最多的数据
- select type, count(*)
- from table
- where ..
- group by type
- having count(*) = max(count(*))
4:数据累加
- 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
- 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
- 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:计算本月有多少天
- 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:是否是闰年
- select datepart(dy, '03/01/'||convert(char(4),datepart(yy,getdate())))
- --= 61 是闰年
- --= 60 不是闰年