面试常考的sql题

问题：sql2000 自动增长id，怎样更新重用被删除过的id

方法：

---创建临时表
create table #(id int)

---插入10条记录
declare @id int
set @id = 1
while @id<= 10
begin
    insert # values(@id)
    set @id = @id + 1
end

---删除其中id为1和5的记录
delete # where id IN(1,5)

---重置id（从1开始递增1）
update t
set id = (select count(1) from # where id <= t.id) 
from # t

 

 问题：用一条SQL语句 查询出每门课都大于80分的学生姓名

方法：

CREATE TABLE [dbo].[Score](
    [student] [varchar](10) NOT NULL,
    [class] [varchar](10) NOT NULL,
    [score] [decimal](9, 2) NULL,
 CONSTRAINT [PK_Score] PRIMARY KEY CLUSTERED 
(
    [student] ASC,
    [class] ASC
)
)

select distinct Student from Score a where not exists(select 1 from Score where score<=80 and student=a.student)

 

问题：一张表(表名：OrderDistribution)中有个id标识列，int型，种子为1，以增量值1递增，但最大id不超过100，经常有增删改查操作。用一条SQL语句 查询出被删掉的id。

方法：

WITH idTable AS(
SELECT ROW_NUMBER()over(order by getdate()) AS id
from sys.all_columns
)
SELECT b.id FROM dbo.OrderDistribution a RIGHT JOIN idTable b ON a.id=b.id
WHERE b.id<=100 AND a.id IS NULL

------或者

WITH idTable AS(
SELECT TOP 100 ROW_NUMBER()over(order by getdate()) AS id
from sys.all_columns
)
SELECT b.id FROM dbo.OrderDistribution a RIGHT JOIN idTable b ON a.id=b.id
WHERE a.id IS NULL

 

数据库设计 之 表关系设计

表关系（关联）：1-1 1-n m-n

1-n通过外键实现

1-1不常见， 适用场景？如分离扩展字段，临时属性，等

m-n 需要定义第三个表，称为结合表，它的主键由 A 表和 B 表的外部键组成。如学生成绩表，一些mapping表

见http://blog.csdn.net/ago52030/article/details/1721033