我们所有使用关系数据库的人都知道(或正在学习)SQL是不同的。获得期望的结果,并有效地这样做,涉及到一个乏味的过程,其部分特征是学习不熟悉的范例,并发现一些我们最熟悉的编程模式在这里不起作用。常见的反模式是什么?


当前回答

重新使用一个“死”字段来做一些它不打算做的事情(例如在“传真”字段中存储用户数据)-尽管作为一个快速修复非常诱人!

其他回答

临时表滥用。

特别是这类事情:

SELECT personid, firstname, lastname, age
INTO #tmpPeople
FROM People
WHERE lastname like 's%'

DELETE FROM #tmpPeople
WHERE firstname = 'John'

DELETE FROM #tmpPeople
WHERE firstname = 'Jon'

DELETE FROM #tmpPeople
WHERE age > 35

UPDATE People
SET firstname = 'Fred'
WHERE personid IN (SELECT personid from #tmpPeople)

不要从查询中构建临时表,只是为了删除不需要的行。

是的,我在生产db中看到过这种形式的代码页。

在他们职业生涯的前6个月学习SQL,在接下来的10年里从不学习其他任何东西。特别是没有学习或有效地使用窗口/分析SQL特性。特别是over()和partition by的使用。

窗口函数,比如聚合 函数时,对对象进行聚合 定义的行集(组),但是 而不是返回一个值 组,窗口函数可以返回 每个组有多个值。

请参阅O'Reilly SQL Cookbook附录A,以获得窗口函数的良好概述。

使用无意义的表别名:

from employee t1,
department t2,
job t3,
...

使得阅读一个大的SQL语句比它需要的要困难得多

SELECT FirstName + ' ' + LastName as "Full Name", case UserRole when 2 then "Admin" when 1 then "Moderator" else "User" end as "User's Role", case SignedIn when 0 then "Logged in" else "Logged out" end as "User signed in?", Convert(varchar(100), LastSignOn, 101) as "Last Sign On", DateDiff('d', LastSignOn, getDate()) as "Days since last sign on", AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' + City + ', ' + State + ' ' + Zip as "Address", 'XXX-XX-' + Substring(Convert(varchar(9), SSN), 6, 4) as "Social Security #" FROM Users

或者,把所有内容都塞进一行。

Human readable password fields, egad. Self explanatory. Using LIKE against indexed columns, and I'm almost tempted to just say LIKE in general. Recycling SQL-generated PK values. Surprise nobody mentioned the god-table yet. Nothing says "organic" like 100 columns of bit flags, large strings and integers. Then there's the "I miss .ini files" pattern: storing CSVs, pipe delimited strings or other parse required data in large text fields. And for MS SQL server the use of cursors at all. There's a better way to do any given cursor task.

编辑是因为有太多了!