1、说明：创建数据库
Create DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建备份数据的 device
USE master
EXEC sp_addumpdevice ‘disk‘, ‘testBack‘, ‘c:\mssql7backup\MyNwind_1.dat‘
--- 开始备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
二、提升
复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in ‘"&Server.MapPath("."&"\data.mdb" &"‘ where..
子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b 或者: select a,b,c from a where a IN (1,2,3)
显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
在线视图查询(表名1：a
select * from (Select a,b,c FROM a) T where t.a > 1;
between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1
四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff(‘minute‘,f开始时间,getdate())>5
一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA except (select a from tableB) except (select a from tableC)
随机取出10条数据
select top 10 * from tablename order by newid()
随机选择记录
select newid()
查看重复记录
Select * From tablename where id in(Select id From tablename group by id having count(*)>1)
删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
列出数据库里所有的表名
select name from sysobjects where type=‘U‘
列出表里的所有的
select name from syscolumns where id=object_id(‘TableName‘)
列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when ‘A‘ then pcs else 0 end),sum(case vender when ‘C‘ then pcs else 0 end),sum(case vender when ‘B‘ then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机C 3
说明：初始化表table1
TRUNCATE TABLE table1
选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
25、
三、技巧
1、1=1，1=2的使用，在SQL语句组合时用的较多
“where 1=1”是表示选择全部 “where 1=2”全部不选，
如：
if @strWhere !=‘
begin
set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where ‘ + @strWhere
end
else
begin
set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]‘
end
我们可以直接写成
set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where 1=1 安定‘+ @strWhere
2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login ‘update_one‘,‘newname‘,‘oldname‘
go
5、检查备份集
RESTORE VERIFYONLY from disk=‘E:\dvbbs.bak‘
6、修复数据库
Alter DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB(‘dvbbs‘,repair_allow_data_loss) WITH TABLOCK
GO
Alter DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- 要操作的数据库名
Select @LogicalFileName = ‘tablename_log‘, -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
Select @OriginalSize = size
FROM sysfiles
Where name = @LogicalFileName
Select ‘Original Size of ‘ + db_name() + ‘ LOG is ‘ +
CONVERT(VARCHAR(30),@OriginalSize) + ‘ 8K pages or ‘ +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ‘MB‘
FROM sysfiles
Where name = @LogicalFileName
Create TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
Select @StartTime = GETDATE(),
@TruncLog = ‘BACKUP LOG ‘ + db_name() + ‘ WITH TRUNCATE_ONLY‘
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (Select size FROM sysfiles Where name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
Select @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
Insert DummyTrans VALUES (‘Fill Log‘)
Delete DummyTrans
Select @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
Select ‘Final Size of ‘ + db_name() + ‘ LOG is ‘ +
CONVERT(VARCHAR(30),size) + ‘ 8K pages or ‘ +
CONVERT(VARCHAR(30),(size*8/1024)) + ‘MB‘
FROM sysfiles
Where name = @LogicalFileName
Drop TABLE DummyTrans
SET NOCOUNT OFF
8、说明：更改某个表
exec sp_changeobjectowner ‘tablename‘,‘dbo‘
9、存储更改全部表
Create PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select ‘Name‘ = name,
‘Owner‘ = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + ‘.‘ + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
1.一道SQL语句面试题，关于group by
表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
胜负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-10','胜')
insert into #tmp values('2005-05-10','负')
insert into #tmp values('2005-05-10','负')
1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq
2) select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='胜'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col001
2.请教一个面试中遇到的SQL语句的查询问题
表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.面试题：一个日期判断的sql语句？
请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格
------------------------------------------
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格') as 英语,
from table
5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
------------------------------------------
用户临时表:create table #xx(ID int, IDValues int)
系统临时表:create table ##xx(ID int, IDValues int)
区别:
用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.
当创建它的进程消失时这个临时表就自动删除.
全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.
6.sqlserver2000是一种大型数据库，他的存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。
------------------------------------------
它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQL Server的存储容量是可以扩大的.
SQL Server 2000 数据库有三种类型的文件：
主要数据文件
主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。
次要数据文件
次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。
日志文件
日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。
7.请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。
如使用存储过程也可以。
table1
月份mon 部门dep 业绩yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门dep 部门名称dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 （result）
部门dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
1)
select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部门dep = b.部门dep and b.月份mon = '一月份' and
a.部门dep = c.部门dep and c.月份mon = '二月份' and
a.部门dep = d.部门dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep
8.华为一道面试题
一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
------------------------------------------
select id, Count（*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1
问题：假设有张学生成绩表(tb)如下:
姓名课程分数
张三语文 74
张三数学 83
张三物理 93
李四语文 74
李四数学 84
李四物理 94
想变成(得到如下结果)：
姓名语文数学物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名语文数学物理平均分总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
/**//*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名语文数学物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名课程分数
---- ---- ----
李四语文 74
李四数学 84
李四物理 94
张三语文 74
张三数学 83
张三物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/**//*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名课程 分数
---- ------ ------
李四语文 74.00
李四数学 84.00
李四物理 94.00
李四平均分 84.00
李四总分 252.00
张三语文 74.00
张三数学 83.00
张三物理 93.00
张三平均分 83.33
张三总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb
假设只有一个table，名为pages，有四个字段，id, url,title,body。里面储存了很多网页，网页的url地址，title和网页的内容，然后你用一个sql查询将url匹配的排在最前，title匹配的其次，body匹配最后，没有任何字段匹配的，不返回。
select a.[id],a.mark from
(
select [page].[id],100 as mark from [page] where [page].[url] like '%baidu%'
union
select [page].[id],50 as mark from [page] where [page].[title] like '%baidu%'
union
select [page].[id],10 as mark from [page] where [page].[body] like '%baidu%'
) as a order by mark desc
表名：成绩表
姓名 课程 分数
张三 语文 81
张三 数学 75
李四 语文 56
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 49
……
（其他用户实验的记录大家可自行插入）
给出成绩全部合格的学生信息（包含姓名、课程、分数），注：分数在60以上评为合格
select * from score
where s_name not in
(select s_name from score
where score<60)
或者：
select * from score where s_name in
(select s_name from score
group by s_name
having min(score)>=60)
（3）表名：商品表
名称 产地 进价
苹果 烟台 2.5
苹果 云南 1.9
苹果 四川 3
西瓜 江西 1.5
西瓜 北京 2.4
……
（其他用户实验的记录大家可自行插入）
给出平均进价在2元以下的商品名称
select 名称 from 商品表 group by 名称 having avg(进价) < 2
（4）表名：高考信息表
准考证号 科目 成绩
2006001 语文 119
2006001 数学 108
2006002 物理 142
2006001 化学 136
2006001 物理 127
2006002 数学 149
2006002 英语 110
2006002 语文 105
2006001 英语 98
2006002 化学 129
……
（其他用户实验的记录大家可自行插入）
给出高考总分在600以上的学生准考证号
select 准考证号 from 高考信息表 group by 准考证号 having sum(成绩) > 600
（5）表名：高考信息表
准考证号 数学 语文 英语 物理 化学
2006001 108 119 98 127 136
2006002 149 105 110 142 129
……
（其他用户实验的记录大家可自行插入）
给出高考总分在600以上的学生准考证号
select 准考证号 from 高考信息表 where (数学+语文+英语+物理+化学) > 600
(四部分)
（一）表名：club
id gender age
67 M 19
68 F 30
69 F 27
70 F 16
71 M 32
……（其余测试数据请自行插入）
查询出该俱乐部里男性会员和女性会员的总数
select gender,count(id) from club group by gender
（二）表名：team
ID(number型) Name(varchar2型)
1 a
2 b
3 b
4 a
5 c
6 c
要求:执行一个删除语句，当Name列上有相同时,只保留ID这列上值小的
例如：删除后的结果应如下:
ID(number型) Name(varchar2型)
1 a
2 b
5 c
请写出SQL语句。
delete from team where id not in
(
select min(a1.id) from team a1
where a1.name=team.name )
delete from team where id not in
(
select min(id) from team group by name)
（三）表名：student
name course score
张青语文 72
王华数学 72
张华英语 81
张青物理 67
李立化学 98
张燕物理 70
张青化学 76
查询出“张”姓学生中平均成绩大于75分的学生信息
select * from student where name in
(select name from student
where name like '张%'
group by name having avg(score) > 75)
SQL经典面试题集（一）
第一题：
为管理业务培训信息，建立3个表：
S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄
C(C#,CN)C#,CN分别代表课程编号，课程名称
SC(S#,C#,G) S#,C#,G分别代表学号，所选的课程编号，学习成绩
(1)使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?
(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?
(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
(4) 查询选修了课程的学员人数?
(5) 查询选修课程超过5门的学员学号和所属单位?
drop table S;
drop table C;
drop table SC;
create table S
(
S# varchar(10),
SN varchar (25),
SD varchar (25),
SA int
)
create table C
(
C# varchar(10),
CN varchar (25)
)
create table SC
(
S# varchar(10),
C# varchar(10),
G int
Primary Key(S#, C#)
)
insert into S values ('10001','Students1','department1',23)
insert into S values ('10002','Students2','department1',24)
insert into S values ('10003','Students3','department2',25)
insert into S values ('10004','Students4','department2',26)
insert into S values ('10005','Students5','department3',23)
insert into S values ('10006','Students6','department3',24)
insert into S values ('10007','Students7','department3',25)
insert into S values ('10008','Students8','department4',25)
insert into C values ('C1','数学')
insert into C values ('C2','物理')
insert into C values ('C3','化学')
insert into C values ('C4','英语')
insert into C values ('C5','中文')
insert into C values ('C6','税收基础')
insert into C values ('C7','传媒')
insert into C values ('C8','日语')
insert into SC values ('10001','C1',67)
insert into SC values ('10001','C2',77)
insert into SC values ('10001','C3',87)
insert into SC values ('10001','C4',97)
insert into SC values ('10001','C5',57)
insert into SC values ('10001','C6',47)
insert into SC values ('10002','C1',62)
insert into SC values ('10002','C2',72)
insert into SC values ('10002','C3',82)
insert into SC values ('10002','C4',92)
insert into SC values ('10002','C5',52)
insert into SC values ('10002','C6',42)
insert into SC values ('10004','C2',74)
insert into SC values ('10004','C5',54)
insert into SC values ('10004','C6',44)
--（1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?
--解法一:
select S#,SN from S where S# in (select S# from C, SC where C.C#=SC.C# and C.CN='税收基础')
--解法二:
select S.S#,S.SN from S inner join (select S# from C left join SC on C.C#=SC.C# where C.CN='税收基础') T on T.S#=S.S#
--(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?
--解答：
select S.SN,S.SD from S,SC where S.S#=SC.S# and SC.C#='C2'
--(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
--解答：
select distinct S.SN,S.SD from S where S.S# not in (select S.S# from S,SC where S.S#=SC.S# and SC.C#='C5')
--(4) 查询选修了课程的学员人数?
--解法一:
select 学员人数=count(distinct s#) from sc
--解法二:
select count(*) as 学员人数 from (select distinct SC.S# from SC) t
--(5) 查询选修课程超过5门的学员学号和所属单位?
--解法一:
select S#,SD from S where S.S# in (select SC.S# from SC group by SC.S# having count(*)>5)
--解法二:
select S#,SD from S where S# in(select S# from SC group by S# having count(distinct C#)>5)
第二题：
create table testtable1
(
id int IDENTITY,
department varchar(12)
)
insert into testtable1 values('设计')
insert into testtable1 values('市场')
insert into testtable1 values('售后')
结果：
id department
1 设计
2 市场
3 售后
create table testtable2
(
id int IDENTITY,
dptID int,
name varchar(12)
)
insert into testtable2 values(1,'张三')
insert into testtable2 values(1,'李四')
insert into testtable2 values(2,'王五')
insert into testtable2 values(3,'彭六')
insert into testtable2 values(4,'陈七')
insert into testtable2 values(5,'陈七')
select t2.id,t2.dptID,t1.department,t2.name from testtable2 t2 left join testtable1 t1 on t1.id=t2.dptID
select * from testtable2
用一条SQL语句，怎么显示如下结果
id dptID department name
1 1 设计 张三
2 1 设计 李四
3 2 市场 王五
4 3 售后 彭六
5 4 黑人 陈七
--解答：
--解法一:
select t2.id,t2.dptID,t1.department,t2.name from testtable2 t2 left join testtable1 t1 on t1.id=t2.dptID
--解法二:
SELECT t2.id , t2.dptID, ISNULL(t1.department,'黑人') dptName,t2.name FROM testtable1 t1 right join testtable2 t2 on t2.dptID = t1.ID
--注意下面两个语句查询结果与上面答案的区别
select t2.id,t2.dptID,t1.department,t2.name from testtable1 t1,testtable2 t2 where t1.id=t2.dptID
select t2.id,t2.dptID,t1.department,t2.name from testtable2 t2 inner join testtable1 t1 on t1.id=t2.dptID
第三题：
有表A，结构如下：
A: p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为：
p_ID s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8
其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。
create table A
(
p_ID int,
p_Num int,
s_id int
)
insert into A values(1,10,01)
insert into A values(1,12,02)
insert into A values(2,8,01)
insert into A values(3,11,01)
insert into A values(3,8,03)
--解答：
select p_id ,
sum(case when s_id=1 then p_num else 0 end) as s1_id,
sum(case when s_id=2 then p_num else 0 end) as s2_id,
sum(case when s_id=3 then p_num else 0 end) as s3_id
from A group by p_id
第四题：
--1.查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列？
create table A
(
id int IDENTITY,
Name varchar (25)
)
--1.查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列？
--解答：
select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by id ) T) order by id
第五题：
--查询A(ID,Name)表中存在ID重复三次以上的记
drop table A
create table A
(
id int,
Name varchar (25)
)
insert into A values(1,'a')
insert into A values(2,'a')
insert into A values(3,'a')
insert into A values(1,'a')
insert into A values(2,'a')
insert into A values(3,'a')
insert into A values(4,'a')
insert into A values(1,'a')
--解答：
select id,name from A where id in (select id from A group by id having count(id)>3)order by id
第六题：
原表Course:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
写出此查询语句。
create table Course(
courseid int IDENTITY,
coursename varchar (25),
score int
)
insert into Course values ( 'java',70)
insert into Course values ( 'oracle',90)
insert into Course values ( 'xml',40)
insert into Course values ( 'jsp',30)
insert into Course values ( 'servlet',80)
--解答：
--oracle：
select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course
--SQL Server：
select *, (case when score<60 then 'failed' else 'pass' end) as mark from Course
第七题：
有表：emp(id, name, age)
要求：列出所有名字重复的人的记录?
create table emp(
id int IDENTITY,
name varchar (25),
age int
)
insert into emp values('Zhang1',26)
insert into emp values('Zhang2',27)
insert into emp values('Zhang3',28)
insert into emp values('Zhang1',26)
insert into emp values('Zhang2',27)
insert into emp values('Zhang3',29)
insert into emp values('Zhang1',26)
insert into emp values('Zhang2',27)
insert into emp values('Zhang3',28)
insert into emp values('Zhang1',26)
insert into emp values('Zhang4',22)
insert into emp values('Wang1',27)
insert into emp values('wang2',28)
insert into emp values('Wang2',26)
insert into emp values('Wang1',22)
--列出所有名字重复的人的记录?
--解法一：要知道所有名字有重复人资料，首先必须知道哪个名字重复了：
select id,name,age from emp where name in (select name from emp group by name having count(*)>1)
--解法二：如果对每个名字都和原表进行比较，大于2个人名字与这条记录相同的就是合格的，就有：
select * from emp where (select count(*) from emp e where e.name=emp.name)>1
--解法三：如果有另外一个名字相同的人工号不与她他相同那么这条记录符合要求：
select * from emp where exists (select * from emp e where e.name=emp.name and e.id<>emp.id)
--或：
select distinct emp.* from emp inner join emp e on emp.name=e.name and emp.id<>e.id
第八题：
有例表：emp(name,age)
Tom 16
Sun 14
Tom 16
Tom 16
要求：过滤掉所有多余的重复记录
create table emp(
name varchar(20),
age int
)
insert into emp values('Tom',16)
insert into emp values('Sun',14)
insert into emp values('Tom',16)
insert into emp values('Tom',16)
--解法一：通过distinct、group by过滤重复：
select distinct * from emp
或
select name,age from emp group by name,age
--获得需要的数据，如果可以使用临时表就有解法：
select distinct * into #tmp from emp
delete from emp
insert into emp select * from #tmp
--但是如果不可以使用临时表，那该怎么办？
alter table emp add chk int identity(1,1)
--重复记录可以表示为：
select * from emp where (select count(*) from emp e where e.name=emp.name)>1
--要删除的是：
delete from emp where (select count(*) from emp e where e.name=emp.name and e.chk>=emp.chk)>1
--再把添加的列删掉，出现结果。
alter table emp drop column chk
--)另一个思路：视图
select min(chk) from emp group by name having count(*) >1
--获得有重复的记录chk最小的值,于是可以
delete from emp where chk not in (select min(chk) from emp group by name)
第九题：
有列表：emp（emp_no, name,age）
001 Tom 17
002 Sun 14
003 Tom 15
004 Tom 16
要求生成序列号
create table emp(
emp_no int,
name varchar(20),
age int
)
insert into emp values(001,'Tom',17)
insert into emp values(002,'Sun',14)
insert into emp values(003,'Tom',15)
insert into emp values(004,'Tom',16)
--(1)最简单的方法：
alter table emp add chk int identity(1,1)
--或
select *,identity(int,1,1) chk into #tmp from emp
select * from emp
alter table emp drop column chk
--如果需要控制顺序怎么办？
select *,identity(int,1,1) chk into #tmp from emp order by age
delete from emp
alter table emp add chk int
insert into emp select * from #tmp
select * from #tmp
drop table #tmp
--(2)假如不可以更改表结构，怎么办？
如果不可以唯一区分每条记录是没有办法的，
select emp.*,(select count(*) from emp e where e.emp_no<=emp.emp_no) from emp order by (select count(*) from emp e where e.emp_no<=emp.emp_no)
第十题：
学科表:
姓名 选课
---------------------
张三 数学
张三 物理
张三 语文
张三 化学
李四 数学
李四 化学
李四 语文
王五 数学
王五 物理
王五 语文
赵六 数学
赵六 物理
赵六 语文
周七 数学
周七 物理
问题一：只选数学，物理，语文的学生, 查询结果如下,写出相应SQL语句
姓名 选课
---------------------
王五 数学
王五 物理
王五 语文
赵六 数学
赵六 物理
赵六 语文
问题二：同时选了数学，物理，语文的学生, 查询结果如下,写出相应SQL语句
姓名 选课
---------------------
张三 数学
张三 物理
张三 语文
王五 数学
王五 物理
王五 语文
赵六 数学
赵六 物理
赵六 语文
create table course(
Name varchar(25),
CName varchar(25)
)
insert into course values ('张三','数学')
insert into course values ('张三','物理')
insert into course values ('张三','语文')
insert into course values ('张三','化学')
insert into course values ('李四','数学')
insert into course values ('李四','语文')
insert into course values ('李四','化学')
insert into course values ('王五','数学')
insert into course values ('王五','物理')
insert into course values ('王五','语文')
insert into course values ('赵四','数学')
insert into course values ('赵四','物理')
insert into course values ('赵四','语文')
insert into course values ('周七','数学')
insert into course values ('周七','物理')
select * from course
--问题一：只选数学，物理，语文的学生, 查询结果如下,写出相应SQL语句------
--解法一：
select A.Name,B.CName from
(select T.Name from (select Name,CName from Course where CName in('数学','物理','语文'))T group by Name having count(*)=3 )A,
(select Name,CName from Course where CName in('数学','物理','语文'))B
where A.Name=B.Name
and A.Name not in (select Name from Course group by Name having count(*)>3 )
--解法二：
select * from course
where name in (select name from course where CName in('数学','物理','语文') group by name having count(*)=3)
and name not in(select name from course group by name having count(*)>3)
--问题二：同时选了数学，物理，语文的学生, 查询结果如下,写出相应SQL语句---
--解法一：
select A.Name,B.CName from
(select T.Name from (select Name,CName from Course where CName in('数学','物理','语文'))T group by Name having count(*)=3 )A,
(select Name,CName from Course where CName in('数学','物理','语文'))B
where A.Name=B.Name
--解法二：
select * from course
where name in (select name from course where CName in('数学','物理','语文') group by name having count(*)=3)
SQL经典面试题集（二）
第十一题：
有表students(name,class,grade),请用标准sql语句完成
name class grade
张三 数学 81
李四 语文 70
王五 数学 90
张三 语文 60
李四 数学 100
王五 语文 90
王五 英语 81
要求: 用sql语句输出各门功课都大于80分的同学姓名?
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('张三','语文',20)
insert into students values ('张三','数学',90)
insert into students values ('张三','英语',50)
insert into students values ('李四','语文',81)
insert into students values ('李四','数学',60)
insert into students values ('李四','英语',90)
insert into students values ('王二','数学',81)
insert into students values ('王二','英语',90)
insert into students values ('李五','数学',83)
insert into students values ('李五','英语',90)
insert into students values ('李五','化学',90)
---选出所有成绩大于80分的学生姓名-----
------解法一------
select name from students group by name having min(grade)>80
------解法二------
select distinct Name from students where grade >80 and Name not in (select Name from students where grade <80)
------解法三------
select distinct name from students where name not in (select name from students where grade <=80 group by name )
-----解法四-------
select name from students group by name having name not in (select name from students where grade<=80)
第十二题：
已知一个表的结构为：
姓名 科目 成绩
张三 语文 20
张三 数学 30
张三 英语 50
李四 语文 70
李四 数学 60
李四 英语 90
怎样通过select语句把他变成以下结构：
姓名 语文 数学 英语
张三 20 30 50
李四 70 60 90
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('张三','语文',20)
insert into students values ('张三','数学',90)
insert into students values ('张三','英语',50)
insert into students values ('李四','语文',81)
insert into students values ('李四','数学',60)
insert into students values ('李四','英语',90)
--解答：
select A.Name,A.grade as 语文,B.grade as 数学,C.grade as 英语
from students A,students B,students C
where A.Name=B.Name and B.Name=C.Name
and A.class='语文' and B.class='数学'
and C.class='英语'
第十三题：
我现在有两张表个表
create table userinfo
(
id int,
username varchar(32),
u_id int
)
create table checkinfo
(
id int,
checktype varchar(32) --出勤的类型（正常，病假，事假）
u_id int
)
两张表通过u_id关联的
怎么查询出每个用户的某个月出勤的情况：
比如说，1月份，正常出勤多少天，事假多少天，病假多少天?
例如：
username 病假（天数）事假（天数）病假（天数）
张三 15 5 2
create table userinfo
(
id int,
username varchar(32),
u_id int
)
create table checkinfo
(
id int,
checktype varchar(32), --出勤的类型（正常，病假，事假）
u_id int
)
delete from userinfo
insert into userinfo values(1,'user1',1)
insert into userinfo values(2,'user2',2)
insert into userinfo values(3,'user3',3)
insert into userinfo values(4,'user4',4)
insert into checkinfo values(1,'正常',1)
insert into checkinfo values(2,'正常',1)
insert into checkinfo values(3,'病假',1)
insert into checkinfo values(4,'正常',2)
insert into checkinfo values(5,'事假',2)
insert into checkinfo values(6,'病假',2)
insert into checkinfo values(7,'正常',2)
insert into checkinfo values(8,'病假',2)
insert into checkinfo values(9,'正常',3)
insert into checkinfo values(10,'事假',3)
insert into checkinfo values(11,'病假',3)
insert into checkinfo values(12,'正常',3)
insert into checkinfo values(13,'正常',3)
insert into checkinfo values(14,'正常',3)
insert into checkinfo values(15,'正常',3)
insert into checkinfo values(16,'病假',3)
insert into checkinfo values(17,'正常',4)
insert into checkinfo values(18,'事假',4)
insert into checkinfo values(19,'病假',4)
insert into checkinfo values(20,'正常',4)
insert into checkinfo values(21,'事假',4)
insert into checkinfo values(22,'病假',4)
insert into checkinfo values(23,'事假',4)
insert into checkinfo values(24,'病假',4)
---解法一:
select b.*,m.正常,m.事假,m.病假
from userinfo b
join
(select a.u_id,
count(case when a.checktype='病假' then '1' end ) 病假 ,
count(case when a.checktype='正常' then '1' end ) 正常 ,
count(case when a.checktype='事假' then '1' end ) 事假
from checkinfo a group by a.u_id) m
on m.u_id=b.u_id
---解法二:
select b.* ,m1.正常,m2.病假,m3.事假 from userinfo b
left join (select a.u_id, count(a.checktype) 正常 from checkinfo a where a.checktype='正常' group by a.u_id ) m1 on b.u_id=m1.u_id
left join (select a.u_id, count(a.checktype) 病假 from checkinfo a where a.checktype='病假' group by a.u_id ) m2 on b.u_id=m2.u_id
left join (select a.u_id, count(a.checktype) 事假 from checkinfo a where a.checktype='事假' group by a.u_id ) m3 on b.u_id=m3.u_id
第十四题：
产品 颜色 数量
产品1 红色 100
产品1 蓝色 80
产品2 蓝色 103
产品2 红色 NULL
产品2 红色 89
产品1 红色 100
1：按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量
create table products(
name varchar(20),
color char(20),
quantities int
)
insert into products values('产品1','红色',100)
insert into products values('产品1','蓝色',80)
insert into products values('产品2','红色',null)
insert into products values('产品2','蓝色',103)
insert into products values('产品2','红色',89)
insert into products values('产品1','红色',100)
-----解答:
---第一步：查询出每种商品中兰色和红色数量及产品名称
--红色：
select name,sum(quantities) from products where color='红色' group by name
--蓝色：
select name,sum(quantities) from products where color='蓝色' group by name
---第二步：查询出要求的结果：
select t1.name,t1.x-t2.x as balance
from
(select name,sum(quantities) as x from products where color='红色' group by name) t1,
(select name,sum(quantities) as x from products where color='蓝色' group by name) t2
where t1.x >t2.x and t1.name=t2.name
第十五题：
--查询学生表中，选修课超过5门的名字!
create table students (
id int IDENTITY,
name varchar(20),
elective_course varchar(20)
)
insert into students values('student1','course1')
insert into students values('student1','course2')
insert into students values('student1','course3')
insert into students values('student1','course4')
insert into students values('student1','course6')
insert into students values('student1','course6')
insert into students values('student2','course1')
insert into students values('student2','course2')
insert into students values('student2','course3')
insert into students values('student2','course4')
insert into students values('student2','course5')
insert into students values('student3','course1')
insert into students values('student3','course2')
insert into students values('student3','course3')
insert into students values('student3','course4')
insert into students values('student4','course1')
insert into students values('student4','course2')
insert into students values('student4','course3')
insert into students values('student4','course4')
insert into students values('student4','course5')
insert into students values('student4','course6')
insert into students values('student4','course7')
insert into students values('student5','course2')
insert into students values('student5','course3')
insert into students values('student5','course4')
insert into students values('student5','course5')
insert into students values('student5','course6')
insert into students values('student5','course7')
insert into students values('student5','course8')
insert into students values('student5','course9')
insert into students values('student6','course7')
insert into students values('student6','course8')
insert into students values('student6','course9')
--解答:
select name from students group by name having count(elective_course)>=5
第十六题：
DbTable表有三列，id，name，data，其中name列里每行都含有'{data}',如第一行里为'aa{data}bb'，第二行为'abc{data}cd'，要求用对应data列的
数据替换掉'{data}'，sql怎么写？
create table DbTable (
ID int IDENTITY,
name varchar(20),
data varchar(10)
)
insert into DbTable values ('a1{data}bb','1')
insert into DbTable values ('a2{data}bb','2')
insert into DbTable values ('a3{data}bb','3')
insert into DbTable values ('a4{data}bb','4')
insert into DbTable values ('a5{data}bb','5')
--解答:
update DbTable set name=replace(name,'{data}',data)
select * from DbTable
第十七题：
存在表table（FID，FCLASS，FSSCORE），三字段分别代表姓名、班级、成绩。用最高效、最简单的SQL语句列出人数大于30的各班最高成绩的列表，显示
班级、成绩两个字段。
create table F3 (
FID varchar(20),
FLASS varchar(20),
FSSCORE int
)
insert into F3 values ('S_Name1','Class1',67)
insert into F3 values ('S_Name2','Class1',57)
insert into F3 values ('S_Name3','Class1',27)
insert into F3 values ('S_Name4','Class1',37)
insert into F3 values ('S_Name5','Class1',97)
insert into F3 values ('S_Name6','Class2',67)
insert into F3 values ('S_Name7','Class2',57)
insert into F3 values ('S_Name8','Class2',27)
insert into F3 values ('S_Name9','Class2',37)
insert into F3 values ('S_Name10','Class2',97)
insert into F3 values ('S_Name11','Class2',37)
insert into F3 values ('S_Name112','Class2',97)
insert into F3 values ('S_Name17','Class3',57)
insert into F3 values ('S_Name18','Class3',27)
insert into F3 values ('S_Name19','Class3',37)
insert into F3 values ('S_Name110','Class3',88)
insert into F3 values ('S_Name111','Class3',37)
insert into F3 values ('S_Name1112','Class3',67)
insert into F3 values ('S_Name117','Class4',57)
insert into F3 values ('S_Name118','Class4',27)
insert into F3 values ('S_Name119','Class4',37)
insert into F3 values ('S_Name1110','Class4',82)
insert into F3 values ('S_Name1111','Class4',37)
insert into F3 values ('S_Name11112','Class4',67)
insert into F3 values ('S_Name11111','Class5',37)
insert into F3 values ('S_Name111112','Class5',67)
---解答：为了便于组装测试数据，这里一以5为人数
--解法一：
select F3.FLASS, Max(FSSCORE) from F3 group by FLASS having count(*) >=5
--解法二：
--第一步：查询出人数大于5的班级--
select FLASS ,count(*) as Total from F3 group by FLASS having count(*) >= 5
--第二步：查询出所有人数大于5的班级的所有学生记录--
select * from F3 where FLASS in (select FLASS from F3 group by FLASS having count(*) >= 5 )
--第三步：通过对第二步的记录根据FCLASS分组查询--
select FLASS, Max(FSSCORE) from F3 where FLASS in (select FLASS from F3 group by FLASS having count(*) >= 5 ) group by FLASS
--解法三：
select FLASS,max(fsscore) from
(
select * from F3
where FLASS in (select FLASS from F3 group by FLASS having count(*)>=5)
) T group by FLASS
第十八题：
有一张老师表Teachers，字段是T_ID,T_NAME；有一张学生表Students，字段是S_ID,S_NAME；还有一张班级表Classes，字段是T_ID,S_ID,C_NAME，其中
C_NAME的取值只有‘大班’和‘小班’，请查询出符合条件的老师的名字，条件是老师在大班中带的学生数大于此老师在小班中带的学生数。
create table Teachers (
T_ID int,
T_NAME varchar(20)
)
create table Students (
S_ID int,
S_NAME varchar(20)
)
create table Classes (
T_ID int,
S_ID int,
C_NAME varchar(20)
)
insert into Teachers values(1,'T1')
insert into Teachers values(2,'T2')
insert into Teachers values(3,'T3')
insert into Teachers values(4,'T4')
insert into Teachers values(5,'T5')
insert into Students values(1,'S1')
insert into Students values(2,'S1')
insert into Students values(3,'S1')
insert into Students values(4,'S1')
insert into Students values(5,'S1')
insert into Students values(6,'S1')
insert into Students values(7,'S1')
insert into Students values(8,'S1')
insert into Students values(9,'S1')
insert into Students values(10,'S1')
insert into Students values(11,'S1')
insert into Students values(12,'S1')
insert into Students values(13,'S1')
insert into Students values(14,'S1')
insert into Students values(15,'S1')
insert into Students values(16,'S1')
insert into Classes values(1,1,'大班')
insert into Classes values(1,2,'大班')
insert into Classes values(1,3,'小班')
insert into Classes values(1,4,'大班')
insert into Classes values(1,13,'大班')
insert into Classes values(1,14,'大班')
insert into Classes values(1,15,'小班')
insert into Classes values(1,16,'大班')
insert into Classes values(2,1,'大班')
insert into Classes values(2,2,'小班')
insert into Classes values(2,3,'大班')
insert into Classes values(2,4,'大班')
insert into Classes values(2,16,'小班')
insert into Classes values(2,15,'小班')
insert into Classes values(2,14,'小班')
insert into Classes values(3,5,'大班')
insert into Classes values(3,6,'小班')
insert into Classes values(3,7,'大班')
insert into Classes values(4,4,'大班')
insert into Classes values(4,5,'大班')
insert into Classes values(4,6,'小班')
insert into Classes values(4,7,'小班')
insert into Classes values(4,8,'小班')
insert into Classes values(5,9,'大班')
insert into Classes values(5,10,'小班')
insert into Classes values(5,11,'小班')
insert into Classes values(5,12,'小班')
--第一步：查询出每个老师所带的小班的人数--------
select T_ID,count(*) as x from Classes where C_Name='小班' group by T_ID
--第二步：查询出每个老师所带的大班的人数--------
select T_ID,count(*) as x from Classes where C_Name='大班' group by T_ID
--第三步：在上面一二步的基础上查询出大班人数大于小班人数的老师------------
select T_NAME
from Teachers t,
(select T_ID,count(*) as x from Classes where C_Name='小班' group by T_ID) T1,
(select T_ID,count(*) as x from Classes where C_Name='大班' group by T_ID) T2
where T1.x<T2.x
and T1.T_ID=T2.T_ID and t.T_ID=T1.T_ID
--考察要点：1.分组查询. 2.把查询出来的某些结果作为表来连接查询出相关结果.
第十九题：
前提:a 部门表 b 员工表
a表字段(
id --部门编号
departmentName-部门名称
)
b表字段(
id--部门编号
employee- 员工名称
)
问题:如何一条sql语句查询出每个部门共有多少人?
*/
create table departments(
ID int IDENTITY,
Name varchar (20),
)
create table employees(
ID int,
Name varchar (20)
)
insert into departments values ('DeparmentA')
insert into departments values ('DeparmentB')
insert into departments values ('DeparmentC')
insert into departments values ('DeparmentD')
insert into departments values ('DeparmentE')
insert into employees values (1,'Zhang3')
insert into employees values (1,'Zhang4')
insert into employees values (1,'Zhang5')
insert into employees values (2,'Li3')
insert into employees values (2,'Li4')
insert into employees values (2,'Li5')
insert into employees values (2,'Li6')
insert into employees values (3,'Zhao3')
insert into employees values (3,'Zhao4')
insert into employees values (3,'Zhao5')
insert into employees values (4,'Chen4')
insert into employees values (4,'Chen5')
insert into employees values (5,'Zhu4')
--解法一----
select b.id,a.Name,count(b.id)as employeecount from departments a left join employees b on a.id=b.id group by b.id,a.Name
---或
select b.id,a.Name,count(*)as employeecount from departments a left join employees b on a.id=b.id group by b.id,a.Name
---解法二---
select t.id as 'Id', t.name as 'Name',count (*) as 'EmployeeCount' from (select d.id,d.name from departments as d inner join employees as e on d.id=e.id) t group by t.id,t.name
--解法三----
select a.id,a.Name,count(b.id)as employeecount from departments a left join employees b on a.id=b.id group by a.id,a.Name
第二十题：
在Oracle数据库中有一张表A
编号 名称
1 a
2 b
3 c
4 d
如何写一条SQL语句，显示以下结果
ab,ac,ad,bc,cd
drop table B select b.id,b.name from B b
create table B (
id int IDENTITY,
name varchar (20)
)
insert into B values ('a')
insert into B values ('b')
insert into B values ('c')
insert into B values ('d')
--SQL Server:
select a.name+b.name,a.name+c.name,a.name+d.name,b.name+c.name,c.name+d.name from B a, B b,B c,B d where a.id=1 and b.id=2 and c.id=3 and d.id=4
--Oracle:
select distinct a.name||b.name||','||a.name||c.name||','||a.name||d.name||','||b.name||c.name||','||c.name||d.name
from B a,B b,B c,B d
where a.number=1 and b.number=2 and c.number=3 and d.number=4
--其它参考：如果要求你在同一列显示呢？
ab
ac
ad
bc
bd
cd
--参考答案：
select a.name+b.name from B a,B b
where a.id<b.id group by a.name+b.name
SQL经典面试题目（三）
第二十题：
怎么样抽取重复记录
表：
id name
--------
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
2 test2
3 test3
2 test2
6 test6
查出所有有重复记录的数据，用一句sql 来实现
create table D(
id varchar (20),
name varchar (20)
)
insert into D values('1','test1')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('6','test6')
insert into D values('5','test5')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('7','test7')
insert into D values('4','test4')
insert into D values('6','test6')
insert into D values('5','test5')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('8','test8')
insert into D values('10','test4')
select * from D where
--解法一:
--查询出重复的记录
select id,name from D group by id,name having count(*)>1
--查询出重复的记录及重复次数
select a.id,a.name from D a,(select id,name from D group by id,name having count(*)>1) b where a.id=b.id and a.name=b.name
--解法二:
select t1.* from D t1,
(select sum(1) as Sig,id,name from D group by id,name) as t2
where t1.name=t2.name and t1.id=t2.id and t2.Sig>1
第二十一题：
已知原表(t_salary)
year salary
2000 1000
2001 2000
2002 3000
2003 4000
先要实现显示结果(salary为以前的工资和)
year salary
2000 1000
2001 3000
请问SQL语句怎么写?
create table t_salary(
year int,
salary int
)
select * from t_salary
insert into t_salary values(2000,1000)
insert into t_salary values(2001,2000)
insert into t_salary values(2002,3000)
insert into t_salary values(2003,4000)
--解答：
select year, (select sum(salary) from t_salary b where b.year <= a.year) salary from t_salary a group by year
第二十二题：
year month total
1996 1 3000
1996 3 4000
1996 7 5000
1997 3 4000
1997 6 3000
.
要求按如下格式输出：
year m1,m2,m3,m4
year 为年，m1等为季度，要求按上行输出
create table Outputs (
year char(4),
month int,
total int
)
insert into Outputs values ('1996',1,3000)
insert into Outputs values ('1996',3,4000)
insert into Outputs values ('1996',7,5000)
insert into Outputs values ('1997',3,4000)
insert into Outputs values ('1997',6,3000)
insert into Outputs values ('1997',2,3000)
insert into Outputs values ('1998',1,3000)
insert into Outputs values ('1998',4,3500)
select * from Outputs
----------------解答-------------------
--解法一：
select year,
sum(case when month<=3 then total else 0 end) as M1,
sum(case when month>3 and month<=6 then total else 0 end) as M2,
sum(case when month>6 and month<=9 then total else 0 end) as M3,
sum(case when month>9 and month<=12 then total else 0 end) as M2
from Outputs group by year
--解法二：
select year,
sum(case when month in(1,2,3) then total else 0 end) as M1,
sum(case when month in(4,5,6) then total else 0 end) as M2,
sum(case when month in(7,8,9) then total else 0 end) as M3,
sum(case when month in(10,11,12) then total else 0 end) as M2
from Outputs group by year
第二十三题：
普通行列转换
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
create table tb(
姓名 varchar(10) ,
课程 varchar(10) ,
分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
insert into tb values('李四' , '历史' , 94)
go
-----解法一：SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)---------
select 姓名,
max(case when 课程='语文' then 分数 end) as 语文,
max(case when 课程='数学' then 分数 end) as 数学,
max(case when 课程='物理' then 分数 end) as 物理
from tb
group by 姓名
-----解法二：--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同) ---------
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
-----解法三：SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
-----解法四：SQL SERVER 2005 静态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
-----------------------------------
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
--SQL SERVER 2000 静态SQL。
select 姓名姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
------------------
------------------
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
第二十四题：
只用一条SQL语句,要求从左表查询出右表!左表是查询表,右表是要求查询出的结果,并不是两表联合查询.
左表： 右表：
ID NAME ID NAME
---------- ------------------
1 A5 1 A5,A8,AF....
2 A8 2 B5,B3,BD....
3 AF 3 C3,CK,CI....
4 B5
5 B3
6 BD
7 C3
8 CK
9 CI
create table leftTable(
id int indentity,
name varchar(20)
)
create table rightTable(
id int indentity,
name varchar(20)
)
insert into leftTable values('A5')
insert into leftTable values('A8')
insert into leftTable values('AF')
insert into leftTable values('B5')
insert into leftTable values('B3')
insert into leftTable values('BD')
insert into leftTable values('C3')
insert into leftTable values('CK')
insert into leftTable values('CI')
--本题答案征集中................
第二十五题：
--如何删除SQL表中重复的记录,除ID值不一样外其它字段都一样,每两行记录重复
create table duplicateTable(
id int IDENTITY,
name varchar(10),
class varchar(10),
address varchar(20),
nationality varchar(30)
)
insert into duplicateTable values('name1','class1','address1','nationality1')
insert into duplicateTable values('name2','class2','address2','nationality2')
insert into duplicateTable values('name3','class3','address3','nationality3')
insert into duplicateTable values('name4','class4','address4','nationality4')
insert into duplicateTable values('name5','class5','address5','nationality5')
insert into duplicateTable values('name6','class6','address6','nationality6')
insert into duplicateTable values('name2','class2','address2','nationality2')
insert into duplicateTable values('name3','class3','address3','nationality3')
insert into duplicateTable values('name4','class4','address4','nationality4')
insert into duplicateTable values('name5','class5','address5','nationality5')
insert into duplicateTable values('name6','class6','address6','nationality6')
insert into duplicateTable values('name3','class3','address3','nationality3')
insert into duplicateTable values('name4','class4','address4','nationality4')
insert into duplicateTable values('name5','class5','address5','nationality5')
insert into duplicateTable values('name6','class6','address6','nationality6')
insert into duplicateTable values('name4','class4','address4','nationality4')
insert into duplicateTable values('name5','class5','address5','nationality5')
insert into duplicateTable values('name6','class6','address6','nationality6')
insert into duplicateTable values('name5','class5','address5','nationality5')
insert into duplicateTable values('name6','class6','address6','nationality6')
insert into duplicateTable values('name7','class7','address7','nationality7')
--解答：
delete t from duplicateTable t where exists(select 1 from duplicateTable where name=t.name and class="t".class and address=t.address and nationality=t.nationality and id>t.id)