提高你的SQL能力，温馨小提示

公司组织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以前的版本，下列语句是可定不会用上索引的

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 不是闰年

评论

21 楼 maty G 2007-02-10  

楼主写的好详细啊~

20 楼 minimu 2007-02-10  

其实这个问题要分怎么看了
1，多了解一些，总是有好处的，这个无庸置疑
2，我觉得有些题过了，比如哪个日期处理的题，我想大多数的开发人员知道应该是后面的一种处理方式，但是具体的函数以及相关参数肯定记不太清楚（或者说没有必要），使用的思考参考下手册就好了，不过这样考试是肯定考不过的
3，对于数据库优化，其实有三个层面：其一，数据库全局配置的优化，肯定是DBA的活；其二，合理的表结构以及索引、视图等优化，我觉得应该是开发人员初步确定，DBA过一下（权当审核下吧）；其三，SQL语句的优化，对于简单的SQL，我想一般开发人员没有问题；对于复杂或者对性能影响比较大的SQL，一般是开发人员先不管性能先实现，然后db人员专门有针对的优化
4，对于sql的优化，不同的db差异很大的；我以前做得多点得是MS SQL和Oracle，MS SQL得优化很明显，我们专门优化得SQL语句在测试以及最后得生产系统中得性能提升比较一致；但是oracle有时候我们觉得很慢的SQL后来实际并不慢，而一些我们觉得优化很好或者按照优化工具改进得sql得效率最后反而不怎么样；这个我们当时自己解释为不同得数据库对sql得编译以及预处理不一致造成得，Oracle（本身对）的SQL优化比MS SQL好
5，对于考试，我觉得多多考试思维方面的东西好些，至于一些具体的写法什么的，参考手册好点；当然，熟能生巧，用多了就熟悉了

19 楼 西索(hisoka) 2007-02-06  

补充说明一下
我的SQL水平很差 因为接触的少 顶多是能用用中间件
只是觉得 大家都要互相好好学习 不要动气哈

18 楼 西索(hisoka) 2007-02-06  

为什么你们只讨论自己会的呢
我觉得好的程序员不轮水平高低应该是对不了解的东西眼睛发光的
特别是自己急需要用到的
至于具体技术细节是心里知道还是找来现用 都无可厚非
当然 知道的越多越好啦  只要是对的就行

17 楼 IvanLi 2007-02-06  

pf_miles 写道

不同意Ivan Li的说法，我还是觉得如果你只是一个程序员的话，最重要的是掌握标准SQL语句就行了，select, insert, update, delete, inner join,left outter join, right outter join这些基础查询技巧。做到随便给你一个查询逻辑你能用SQL表达出来。
您说要掌握基本概念，标准SQL语句就是基本概念。基本概念都是些记在心里就行了的东西，其实现在公司里的开发很少叫一般程序员直接写SQL语句的，一般都有公司自己的框架，做好ORM映射了，一般程序员只要调用接口就行了。
感觉Ivan你就像是很强调书本性质“基础”.

感觉好多人是被hibernate惯坏了，呵呵

引用

基本概念都是些记在心里就行了的东西

记在心里？我见过很多人不知道group by是怎么回事，在什么情况下要用group by

16 楼 pf_miles 2007-02-05  

不同意Ivan Li的说法，我还是觉得如果你只是一个程序员的话，最重要的是掌握标准SQL语句就行了，select, insert, update, delete, inner join,left outter join, right outter join这些基础查询技巧。做到随便给你一个查询逻辑你能用SQL表达出来。
您说要掌握基本概念，标准SQL语句就是基本概念。基本概念都是些记在心里就行了的东西，其实现在公司里的开发很少叫一般程序员直接写SQL语句的，一般都有公司自己的框架，做好ORM映射了，一般程序员只要调用接口就行了。
感觉Ivan你就像是很强调书本性质“基础”.

15 楼 jichongchong 2007-01-26  

讨厌考试

14 楼 王者之剑 2007-01-08  

我支持楼主的观点，好像大家见过的不会考试的强人或者只会考试的弱人太多了似的。
网上铺天盖地都是这样的评论，甚至有的逃学犯动不动就拿自己和比尔相比。
凤舞凰扬说的并没有错，但是你说的和楼主说的完全是两回事，根本没有对立性，不能拿来做辩论，而楼主提出论题再先，所以我支持楼主。
还有一点，楼主说的即使是错话，但是也没有废话。
将楼主的话改成废话如下：
》》1：基础都不会如何做优化，连上面的那些都不清楚，你给开发人员一个查询计划，我不信他能看懂。
基础都不太会如何很好地做优化，连上面的那些都不太清楚，你给开发人员一个查询计划，我不信他能完全看懂。
》》2：基本的东西都不懂，怎么能保证写出结构和性能良好的SQL？
基本的东西都不太懂，怎么才能保证写出结构和性能良好的SQL？
》》3：上面的东西如果不懂，我敢说一定不是一个合格的Sybase开发者，在Sybase上的开发能力一定很差。
上面的东西如果一点都不懂，我不敢说一定不是一个合格的Sybase开发者，但在Sybase上的开发能力一定很一般。
》》4：Sybase分析器是做的不好，建议4个表以内的连接，而DB2,Oracle就没有这么说，随便连，如果这个不知道，那么在Sybase上跑的sql，搞了十几个表连接，你还在那里优化什么？(在Sybase里最好分开写)
优化起来肯定要麻烦得多。
》》5：如果基础都不会，怎么会保证写出的SQL能够用好索引，保证速度？
如果基础都不会，怎么会保证写出的SQL能够用好索引，这要是碰到大的企业应用怎么保证速度？
》》6：基本概念是提高能力的基础，基础不懂，去搞优化，简直是开玩笑！
基本概念是提高能力的基础，基础不太懂，去搞优化，除非是怪才，否则简直是开玩笑！
凤舞凰扬的意思我想可能是，一般的人让他写SQL得了，十个有八个胡乱写也不会是有问题了，真要出了问题，自然有SM级的去搞定。
还有凤舞凰扬本身已到SM级，一招既出，即含36种变化，要去仔细回想诸般变化，确实是一件无聊无趣的事。

13 楼 IvanLi 2006-12-31  

凤舞凰扬 写道

Ivan Li 写道

1：基础都不会如何做优化，连上面的那些都不清楚，你给开发人员一个查询计划，我不信他能看懂。
2：基本的东西都不懂，怎么能保证写出结构和性能良好的SQL？
3：上面的东西如果不懂，我敢说一定不是一个合格的Sybase开发者，在Sybase上的开发能力一定很差。
4：Sybase分析器是做的不好，建议4个表以内的连接，而DB2,Oracle就没有这么说，随便连，如果这个不知道，那么在Sybase上跑的sql，搞了十几个表连接，你还在那里优化什么？(在Sybase里最好分开写)
5：如果基础都不会，怎么会保证写出的SQL能够用好索引，保证速度？
6：基本概念是提高能力的基础，基础不懂，去搞优化，简直是开玩笑！

   嘿嘿，解释两点。第一，知道你所谓的基础并不是不好，或者说一个优秀的数据库开发人员应该有意识地去提高这样的能力。第二，这样的东西作为对非底层系统开发优化的一般开发人员来说，切入点并不恰当，而且作为考试去评估就更加不合适了。这样的题目考了高分，没有太多意义的。我敢说60％考了高分的都会写出相当烂的SQL，而30％考不了高分的做基本的开发效率绝对也不差。
   在培训及评估定位时，一般都需要把SQL的开发编程与数据库原理特性分开的。在做这样的考试评估时，首先要清楚的是开发人员最欠缺的，也最容易识别水平高低的东西在哪里。不是拿几本数据库的书看一看，背一背，得了高分能力就有了。

这种讨论没有意义了，你觉得不重要完全可以不看，我认为对我有用，所以我看了，我觉得对我有提高，也可能对别人有提高，所以我贴出来了！
你要是觉得不爽就不要来看，谢谢！
我们不要程序员相轻，好吗？来这里提高是目的！

12 楼 凤舞凰扬 2006-12-30  

Ivan Li 写道

1：基础都不会如何做优化，连上面的那些都不清楚，你给开发人员一个查询计划，我不信他能看懂。
2：基本的东西都不懂，怎么能保证写出结构和性能良好的SQL？
3：上面的东西如果不懂，我敢说一定不是一个合格的Sybase开发者，在Sybase上的开发能力一定很差。
4：Sybase分析器是做的不好，建议4个表以内的连接，而DB2,Oracle就没有这么说，随便连，如果这个不知道，那么在Sybase上跑的sql，搞了十几个表连接，你还在那里优化什么？(在Sybase里最好分开写)
5：如果基础都不会，怎么会保证写出的SQL能够用好索引，保证速度？
6：基本概念是提高能力的基础，基础不懂，去搞优化，简直是开玩笑！

   嘿嘿，解释两点。第一，知道你所谓的基础并不是不好，或者说一个优秀的数据库开发人员应该有意识地去提高这样的能力。第二，这样的东西作为对非底层系统开发优化的一般开发人员来说，切入点并不恰当，而且作为考试去评估就更加不合适了。这样的题目考了高分，没有太多意义的。我敢说60％考了高分的都会写出相当烂的SQL，而30％考不了高分的做基本的开发效率绝对也不差。
   在培训及评估定位时，一般都需要把SQL的开发编程与数据库原理特性分开的。在做这样的考试评估时，首先要清楚的是开发人员最欠缺的，也最容易识别水平高低的东西在哪里。不是拿几本数据库的书看一看，背一背，得了高分能力就有了。

11 楼 IvanLi 2006-12-28  

凤舞凰扬 写道

   我们一般分application dba和system dba，两者的角色与分工是不同的。
    象楼上所列，什么Locking schema , locking type，什么AES最多支持50个表连接，查询分析器做的不好。这对应用程序开发人员有很多意义么？ 要是不熟悉AES数据库特性的开发人员去贵公司参加考试不及格，是不是意味着数据库开发能力很差呢？
    做一个良好的数据库开发应用人员，最为重要的是懂得如何写出结构与性能良好的SQL，而不是搞一堆概念和数据库特性的考试。
    当然，我并不是说懂这些不好。而是认为作为所谓的考试并不合适，而且所谓的考试成绩更加没有太多的衡量意义。

1：基础都不会如何做优化，连上面的那些都不清楚，你给开发人员一个查询计划，我不信他能看懂。
2：基本的东西都不懂，怎么能保证写出结构和性能良好的SQL？
3：上面的东西如果不懂，我敢说一定不是一个合格的Sybase开发者，在Sybase上的开发能力一定很差。
4：Sybase分析器是做的不好，建议4个表以内的连接，而DB2,Oracle就没有这么说，随便连，如果这个不知道，那么在Sybase上跑的sql，搞了十几个表连接，你还在那里优化什么？(在Sybase里最好分开写)
5：如果基础都不会，怎么会保证写出的SQL能够用好索引，保证速度？
6：基本概念是提高能力的基础，基础不懂，去搞优化，简直是开玩笑！

10 楼 together 2006-12-28  

回一个，如果想全面提高SQL能力的话，把HIBERNATE的源码研究一遍吧。
GAVIN的SQL水准真的是很高。

9 楼 凤舞凰扬 2006-12-28  

   我们一般分application dba和system dba，两者的角色与分工是不同的。
    象楼上所列，什么Locking schema , locking type，什么AES最多支持50个表连接，查询分析器做的不好。这对应用程序开发人员有很多意义么？ 要是不熟悉AES数据库特性的开发人员去贵公司参加考试不及格，是不是意味着数据库开发能力很差呢？
    做一个良好的数据库开发应用人员，最为重要的是懂得如何写出结构与性能良好的SQL，而不是搞一堆概念和数据库特性的考试。
    当然，我并不是说懂这些不好。而是认为作为所谓的考试并不合适，而且所谓的考试成绩更加没有太多的衡量意义。

8 楼 IvanLi 2006-12-27  

凤舞凰扬 写道

   其实我想问的是谁来考？
    如果是开发设计人员，而非DBA考，简直就是无聊。难道会有人觉得考了这个高分就代表数据库编程能力高？
    与其让开发人员参加这样的考试来给个评定的成绩，远不如找些常见并复杂的SQL进行基于优化的讨论。那种受益更大也更加广泛。

这个是每个开发人员必须掌握的内容only level 1
优化要level 2才涉及到
你们公司dba只会这些就够了？远远不够吧！而且这里的好多根本不是dba需要关心的

7 楼 凤舞凰扬 2006-12-26  

   其实我想问的是谁来考？
    如果是开发设计人员，而非DBA考，简直就是无聊。难道会有人觉得考了这个高分就代表数据库编程能力高？
    与其让开发人员参加这样的考试来给个评定的成绩，远不如找些常见并复杂的SQL进行基于优化的讨论。那种受益更大也更加广泛。

6 楼 zaife 2006-12-21  

希望有人能贴出oracle的相关知识

5 楼 ahuaxuan 2006-12-15  

希望有人能贴出oracle的相关知识

4 楼 宏基小键盘 2006-12-08  

<p>好久没考过试了。。。</p>

3 楼 IvanLi 2006-12-06  

再次提出Datetime类型在数据库中是按照二进制储存的，共8个字节，4个字节表示Date, 4个直接表示Time，所以在SQL中不能使用字符串来处理时间类型，下面的sql是错误的

select @month_begin  = convert(datetime,@month+'.01')
select @m = datepart(mm,@date_flag)+1,@year = datepart(yy,@date_flag)
select @month_end = convert(datetime,convert(char(4),@year)+'.'+convert(varchar(2),@m)+'.01')

如果@date_flag是12月的话，就会发生错误，但是这个sql在平时是能够正确执行的，所以错误很难被发现
正确的做法应该是

select @month_begin = dateadd(dd,1-datepart(dd,@date_flag),@date_flag)
select @month_end = dateadd(mm,1,@month_begin)

2 楼 dogstar 2006-12-05  

总共5个人？

哈哈。开玩笑了。。莫怪