【SQL Server 数据库导入导出部分】
1、在查询分析器下查询Excel文档
SELECT * FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:/测试.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
2、从数据库中导出数据并存到文件中
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:/temp1.xls -c -q -S"." -U"sa" -P""'
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:/temp1.txt -c -q -S"." -U"sa" -P""'
3、从文件中导入数据到数据库对应表中
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN in c:/temp1.xls -c -q -S"." -U"sa" -P""'
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN in c:/temp1.txt -c -q -S"." -U"sa" -P""'
一、基础
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：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
二、提升
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1 <>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath("."&"/data.mdb" &"' where..
4、说明：子查询(表名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)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名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
7、说明：在线视图查询(表名1：a
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：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
9、说明：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
11、说明：四表联查问题：
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 .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前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)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示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
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
三、技巧
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
一个题目涉及到的50个Sql语句
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
from SC where C#='002') b
where a.score>b.score and a.s#=b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩；
select S#,avg(score)
from sc
group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname
4、查询姓“李”的老师的个数；
select count(distinct(Tname))
from Teacher
where Tname like '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名；
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='叶平'));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;
9、查询所有课程成绩小于60分的同学的学号、姓名；
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查询没有学全所有课的同学的学号、姓名；
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select S# from SC where C# in (select C# from SC where S#='1002')
group by S# having count(*)=(select count(*) from SC where S#='1002');
15、删除学习“叶平”老师课的SC表记录；
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、
号课的平均成绩；
Insert SC select S#,'002',(Select avg(score)
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
SELECT S# as 学生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS IL,Student AS IM
WHERE L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#
);
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC
21、查询不同老师所教不同课程平均分从高到低显示
SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
SELECT DISTINCT top 3
SC.S# As 学生学号,
Student.Sname AS 学生姓名 ,
T1.score AS 企业管理,
T2.score AS 马克思,
T3.score AS UML,
T4.score AS 数据库,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = '001'
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = '002'
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = '003'
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = '004'
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = 'k1'
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = 'k2'
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = 'k3'
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = 'k4'
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT SC.C# as 课程ID, Cname as 课程名称
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;
24、查询学生平均成绩及其名次
SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) AS 平均成绩
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成绩 > T2.平均成绩) as 名次,
S# as 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成绩 desc;
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
26、查询每门课程被选修的学生数
select c#,count(S#) from sc group by C#;
27、查询出只选修了一门课程的全部学生的学号和姓名
select SC.S#,Student.Sname,count(C#) AS 选课数
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
28、查询男生、女生人数
Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男';
Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女'；
29、查询姓“张”的学生名单
SELECT Sname FROM Student WHERE Sname like '张%';
30、查询同名同性学生名单，并统计同名人数
select Sname,count(*) from Student group by Sname having count(*)>1;;
31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
from student
where CONVERT(char(11),DATEPART(year,Sage))='1981';
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select Sname,SC.S# ,avg(score)
from Student,SC
where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;
34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
Select Sname,isnull(score,0)
from Student,SC,Course
where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score <60;
35、查询所有学生的选课情况；
SELECT SC.S#,SC.C#,Sname,Cname
FROM SC,Student,Course
where SC.S#=Student.S# and SC.C#=Course.C# ;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT distinct student.S#,student.Sname,SC.C#,SC.score
FROM student,Sc
WHERE SC.score>=70 AND SC.S#=student.S#;
37、查询不及格的课程，并按课程号从大到小排列
select c# from sc where scor e <60 order by C# ;
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';
39、求选了课程的学生人数
select count(*) from sc;
40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select Student.Sname,score
from Student,SC,Course C,Teacher
where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );
41、查询各个课程及相应的选修人数
select count(*) from sc group by C#;
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;
43、查询每门功成绩最好的前两名
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列
select C# as 课程号,count(*) as 人数
from sc
group by C#
order by count(*) desc,c#
45、检索至少选修两门课程的学生学号
select S#
from sc
group by s#
having count(*) > = 2
46、查询全部学生都选修的课程的课程号和课程名
select C#,Cname
from Course
where C# in (select c# from sc group by c#)
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');
48、查询两门以上不及格课程的同学的学号及其平均成绩
select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;
49、检索“004”课程分数小于60，按分数降序排列的同学学号
select S# from SC where C#='004'and score <60 order by score desc;
50、删除“002”同学的“001”课程的成绩
delete from Sc where S#='001'and C#='001';
经典SQL语句
1. CREATE DATABASE database_name [WITH LOG IN “pathname”] 创建数据库。 database_name：数据库名称。 “pathname”：事务处理日志文件。 创建一database_name.dbs目录，存取权限由GRANT设定，无日志文件就不能使用 BEGIN WORK等事务语句（可用START DATABASE语句来改变）。 可选定当前数据库的日志文件。 如：select dirpath form systables where tabtype = “L”; 例：create databse customerdb with log in “/usr/john/log/customer.log”; DATABASE databse-name [EXCLUSIVE] 选择数据库。 database_name：数据库名称。 EXCLUSIVE：独占状态。 存取当前目录和DBPATH中指定的目录下的数据库，事务中处理过程中不要使用此语句。 例：dtabase customerdb;
3. CLOSE DATABASE 关闭当前数据库。 database_name：数据库名称。 此语句之后，只有下列语句合法： CREATE DATABASE； DATABASE； DROP DATABSE； ROLLFORWARD DATABASE； 删除数据库前必须使用此语句。 例：close database;
4. DROP DATABASE database_name 删除指定数据库。 database_name：数据库名称。 用户是DBA或所有表的拥有者；删除所有文件，但不包括数据库目录；不允许删除当前数据库（须先关闭当前数据库）；事务中处理过程中不能使用此语句，通过ROLLBACK WORK 也不可将数据库恢复。 例：drop databse customerdb;
5. CREATE [TEMP] TABLE table-name (column_name datatype [NOT NULL], …) [IN “pathname”] 创建表或临时表。 table-name ：表名称。 column_name：字段名称。 data-type：字段数据类型。 path-name：指定表的存放位置 TEMP用于指定建立临时表；表名要唯一，字段要唯一；有CONNECT权限的用户可建立临时表；创建的表缺省允许CONNECT用户存取，但不可以ALTER。 例：create table user ( c0 serial not null, c1 char (10), c2 char(2), c3 smallint, c4 decimal(6,3), c5 date ) in “usr/john/customer.dbs/user;
6. ALTER TABLE ALTER TABLE table-name {ADD (newcol_name newcol_type [BEFORE oldcol_name], …) | DROP (oldcol_name, …) | MODIFY (oldcol_name newcol_type [NOT NULL], … )}, … 修改表结构。 table-name：表名称。 newcol_name：新字段名称 newcol_type：新字段类型 oldcol_name：老字段名称 可以使用单个或多个ADD子句、DROP子句、MODIFY子句，但某个字句失败，操作即中止；原字段是NULL，不允许MODIFY为NOT NULL，除非所有NULL字段中均非空，反之可以；ALTER使用者是表的拥有者或拥有DBA权限，或被授权；事务中处理过程中不要使用此语句。 例：alter table user add ( c6 char(20) before c5);
7. RENAME TABLE oldname TO newname 修改表名。 oldname：原名称。 newname：新名称。 RENAME使用者是表的拥有者或拥有DBA权限，或被授权；事务中处理过程中不要使用此语句。 例：rename user to bbb;
8. DROP TABLE table-name 删除表。 table-name：表名称。 删除表意味着删除其中所有数据、各字段上的索引及对表的赋权、视图等；用户不能删除任何系统目录表；语句使用者是表拥有者或拥有DBA权限，事务中处理过程中不要使用此语句。
9. RENAME COLUMN table.oldcolumn， TO newcolumn 修改字段名。 table.oldcolumn：表名及原字段名称 newcolumn：新字段名称。 语句使用者是表的拥有者或拥有DBA权限或有ALTER权限的用户，事务中处理过程中不要使用此语句。 例：rename column user.c6 to c7;
10. CREATE VIEW view-name column-list CREATE VIEW view-name column-list AS select_statement [WITH CHECK OPTION] 创建视图。 view-name：视图名称。 column-list：字段列表。 select_statement：SELECT语句。 以下语句不使用视图：ALTER TABLE，DROP INDEX，ALTER INDEX，LOCK TABLE，CREATE INDEX， RENAME TABLE；视图将延用基表的字段名，对表达式等虚字段和多表间字段重名必须指明标识其字段名；若对视图中某些字段命名，则所有字段都必须命名；视图中数据类型延用基表中的数据类型，虚字段起诀于表达式；不能使用ORDER BY和UNION子句；对视图中所有的字段要有SELECT权限；事务中处理过程中使用此语句，即使事务回滚，视图也将建立，不能恢复。 例：create view v_user as select * from user where c1 = “B1”;
11. DROP VIEW view-name 删除视图。 view-name：视图名称。 用户可删除自己建立的视图；视图的后代视图也被删除；事务中处理中不要使用此语句。 例：drop view v_user;
12. CREATE INDEX CREATE [UNIQUE/DISTINCT] [CLUSTER] INDEX index_name ON table_name ([column_name ASC/DESC],…) 创建索引。 index_name：索引名称。 table_name：表名称。 column_name：字段名称。 UNIQUE/DISTINCT：唯一索引。 CLUSTER：使表的物理存放顺序按索引排列。 ASC/DESC：升序或降序，缺省升序。 语句执行时，将表的状态置为EXCLUSIVE；复合索引最多包含8个字段，所有字段长度和不得大于120字节；事务中处理过程中使用此语句，即使事务回滚，索引将建立，不能恢复。 例：create cluster index ix_user on user(c5);
13. ALTER INDEX index-name TO [NOT] CLUSTER 修改索引性质。 index-name：索引名称。 TO [NOT] CLUSTER：去掉或加上CLUSTER属性。 语句执行时，将表的状态置为EXCLUSIVE；事务中处理过程中使用此语句，即使事务回滚，索引性质将改变，不能恢复。 例：alter index ix_user to not cluster;
14. DROP INDEX index-name 删除索引。 index-name：索引名称。 语句使用者是索引的拥有者或拥有DBA权限，事务中处理过程中不要使用此语句，否则事务无法恢复。 例：drop index ix_user;
15. CREATE SYNONYM synonym FOR table-name 创建同义名。 synonym：同义名 table-name：表名称 数据库的创建者可以使用同义名；没有赋予同义名权限的用户不能使用同义名；同义名不能和表名相同；事务中处理过程中不要使用此语句。 例：create synonym user_alias for user;
16. DROP SYNONYM synonym 删除同义名。 synonym：同义名 可以删除自己建立的同义名；事务中处理过程中不要使用此语句，否则无法恢复。 例：drop synonym user_alias;
17. UPDATE STATISTICS [FOR TABLE table-name] 更新数据库的统计数字。 table-name：表名称 此语句仅作用于当前数据库；可提高查询效率；只有执行此语句，才改变统计数据。 例：update statistics for table user; GRANT {DBA|RESOURCE|CONNECT} TO {PUBLIC|user-list} 授权命令。 PUBLIC|user-list：全部或指定的用户。 三种权限居且仅居其一，事务处理过程中不要执行GRANT语句。 例：grant resource to pulbic; GRANT tab-privilege ON table-name TO {PUBLIC|user-list} [WITH GRANT OPTION] 授表级权限。 tab-privilege：表级权限。 table-name：表名称。 PUBLIC|user-list：全部或指定的用户。 [WITH GRANT OPTION]：表示被授权用户有否权限进行二次授权。 用户可以在自己建立表达式或被[WITH GRANT OPTION]准许的表中进行赋权；限定越多的权限优先级越高。 例：grant update(c1,c6) on user to dick with grant option; 附（INFORMIX的权限） (1) 数据库的权限（控制对数据库的访问以及数据库中表的创建和删除） DBA权限：全部权利，修改系统表，建立和删除表与索引、增加和恢复表数据，以及授予其他用户数据库权限等； RESOURCE权限：允许对数据库表中的数据进行存取，建立永久性表以及索引。 CONNECT权限：只允许对数据库表中的数据进行存取，建立和删除视图与临时表。 (2)表级权限（对表的建立、修改、检索和更新等权限） ALTER：更改权限 DELETE：删除权限 INDEX：索引权限 INSERT：插入权限 SELECT [(cols)]：指定字段或所有字段上的查询权限，不指明字段缺省为所有字段。 UPDATE [(cols)] ：指定字段或所有字段上的更新权限，不指明字段缺省为所有字段。 ALL [PRIVILEGES]：以上所有表级权限 19. REVOKE {DBA|RESOURCE|CONNECT} FROM {PUBLIC|user-list} 收权命令。 PUBLIC|user-list：全部或指定的用户。 三种权限居且仅居其一，事务处理过程中不要执行GRANT语句。 例：revoke resource from john; REVOKE tab-privilege ON table-name FROM {PUBLIC|user-list} 收表级权限。 tab-privilege：表级权限。 table-name：表名称。 PUBLIC|user-list：全部或指定的用户。 [WITH GRANT OPTION]：表示被授权用户有否权限进行二次授权。 用户只能取消由其本人赋予其他用户的表级存取权限；不能取消自己的权限，对SELECT和UPDATE作取消时，将取消所有表中字段的SELECT 和UPDATE权限。 例；revoke update on user from dick; LOCK TABLE table-name IN {SHARE|EXCLUSIVE} MODE 记录级加锁和表级加锁或文件加锁。 table-name：表名称。 SHARE：允许读表中数据，但不允许作任何修改 EXCLUSIVE：禁止其他任何形式访问表 每次只能对表琐定一次；事务处理过程中，BEGIN WORK后立即执行LOCK TABLE以取代记录级加锁，COMMIT WORK和 ROLLBACK WORK语句取消所有对表的加锁；若没有事务处理，锁将保持到用户退出或执行UNLOCK为止。 例：lock table user in exclusive mode; UNLOCK TABLE table-name 取消记录级加锁和表级加锁或文件加锁。 table-name：表名称。 例：unlock user; SET LOCK MODE TO [NOT] WAIT 改变锁定状态。 TO [NOT]：等待解锁，有可能被死锁或不等待并提示错误信息，表示此记录被锁，缺省值。 访问一个EXCLUSIVE状态下的记录，将返回一个错误。 START DATABSE db_name [WITH LOG IN “pathname”] 启动事务处理。 “pathname”：事务处理日志文件。 执行该语句前，需要先关闭当前数据库。 例；clost database; start databse customer with log in “/usr/john/log/customer.log”; BEGIN WORK 开始事务。例：begin work; COMMIT WORK 提交（正常结束）事务。例：commit work; ROLLBACK WORK 回滚（非正常结束）事务。例：rollback work; SELECT SELECT select_list FROM tab_name|view_name WHERE condition GROUP BY column_name HAVING condition ORDER BY column_list INTO TEMP table_name 查询语句。 select_list：选择表或* tab_name：表名称 view_name：视图名称。 condition：查询条件，可使用BETWEEN、IN、LIKE、IS NULL、LIKE、MATCHES、NOT、 AND、OR、=、!=或 <>、>、 >= 、 <=、 <、ALL、ANY、SOME column_name：分组字段名称 condition：群聚条件 column_list：排序字段列表，缺省ASC，可指定DSC；排序时，NULL值小于非零值。 table_name：临时表名称 例：略 附（常用函数） (1)集合函数： count(*)、 sum(数据项/表达式)、avg(数据项/表达式)、max(数据项/表达式)、min(数据项/表达式) count(distinct 数据项/表达式)、sum(distinct数据项/表达式)、avg(distinct数据项/表达式) (2)代数函数和三角函数 HEX(数据项/表达式)、ROUND(数据项/表达式)、TRUNC(数据项/表达式)、 TAN(数据项/表达式)、ABS(数据项/表达式)、MOD(被除数,除数) (3)统计函数 标准差，stdev()、方差，variance()、范围，rang() (4)时间函数 DAY(日期/时间表达式)：返回数字型 MONTH(日期/时间表达式)：返回整数 WEEKDAY(日期/时间表达式)：0
create table #student(UserName varchar(20),Subject varchar(20),Score int)
insert into #student values('张三','语文',74)
insert into #student values('张三','数学',83)
insert into #student values('张三','物理',93)
insert into #student values('李四','语文',74)
insert into #student values('李四','数学',84)
insert into #student values('李四','物理',94)
/*
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
*/
declare @Sql varchar(1000)
set @sql='select b.UserName'
select @sql=@sql+' , sum(case when b.Subject= '''+Subject+''' then Score else 0 end)['+Subject+'] '
from (select distinct(Subject) from #student) b
set @sql=@sql+' from #student b group by b.UserName'
exec(@sql)
(2)
CREATE TABLE #TA(n1 NVARCHAR(2),n2 NVARCHAR(5),n3 NVARCHAR(2),n4 INT,n5 INT)
INSERT #TA
SELECT N'毛巾',N'哈尔滨',N'条',10,1 UNION ALL
SELECT N'水杯',N'哈尔滨',N'个',20,2
CREATE TABLE #TB(M1 NVARCHAR(5),M2 SMALLDATETIME,M3 INT,M4 INT)
INSERT #TB
SELECT N'一门店','20080101',20,1 UNION ALL
SELECT N'二门店','20080101',30,1 UNION ALL
SELECT N'一门店','20080101',40,2 UNION ALL
SELECT N'二门店','20080101',50,2 UNION ALL
SELECT N'一门店','20080102',60,1 UNION ALL
SELECT N'二门店','20080102',70,1 UNION ALL
SELECT N'一门店','20080102',80,2 UNION ALL
SELECT N'二门店','20080102',90,2
/*
想求出每个商品的现在有库存、每个门店的现有库存、总库存，如：
品名 产地 单位 数量 一门店 二门店 …… 总库存
毛巾 哈尔滨 条 10 60 70 ……
水杯 哈尔滨 个 20 80 90 ……
*/
declare @sql varchar(1000)
set @sql='select a.n1,a.n2,a.n3,a.n4'
select @sql=@sql+ ' ,sum(case when b.M1= '''+M1+''' then b.M3 else null end) ['+M1+']'
from (select distinct (M1) from #TB) a
set @sql=@Sql+ ' ,sum(M3) from #TA a,#TB b where a.n5=b.M4 group by a.n1,a.n2,a.n3,a.n4'
exec(@sql)
/*合并法系列*/
--合并法(函数)
create table tb1
(
col1 varchar(10),
col2 int
)
insert into tb1 select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'b',3
create function dbo.FC_Str(@col1 varchar(100))
returns varchar(100)
as
begin
declare @i varchar(100)
set @i=''
select @i=@i+','+cast(col2 as varchar) from tb1 where col1=@col1
return(stuff(@i,1,1,''))
end
select col1,dbo.FC_Str(col1) from tb1 group by col1
--固定行合并法
create table #tb
(
col1 varchar(10),
col2 int
)
insert into #tb select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'c',3
select col1,
col2=cast(min(col2) as varchar)+
case when count(*)=1 then ''
else +','+cast(max(col2) as varchar) end from #tb
group by col1
--临时表合并法
if object_id('tb') is not null drop table tb
create table tb
(
col1 varchar(10),
col2 int
)
insert into tb select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'b',3
select col1,col2=cast(col2 as varchar(100)) into #t1 from tb order by col1,col2
declare @col1 varchar(20)
declare @col2 varchar(100)
update #t1 set @col2=
case when @col1=col1 then @col2+ ',' +col2
else col2 end,
@col1=col1,
col2=@col2
select * from #t1
select col1,col2=max(col2) from #t1 group by col1
---1字符串拆分
/*
有字符串1,23,a
要求按逗号拆分为表
1
23
a
*/
--(1)第一种方法(循环截取法)
create function FC_SlpitStr(@Str nvarchar(4000),@Split nvarchar(100))
returns @R table (Col nvarchar(100))
as
begin
declare @StrLen int
set @StrLen=len(@Str)
while charindex(@Split,@StrLen)>0
begin
insert into @R values(left(@Str,charindex(@Split,@StrLen)-1))
set @Str=stuff(@Str,1,charindex(@Split,@StrLen),'')
end
insert into @R values(@Str)
return
end
--print dbo.FC_SlpitStr('1,23,a',',')
--[database_name.]owner_name.function_name ([argument_expr][,...])
declare @a nvarchar(4000)
set @a='1,23,a'
select dbo.FC_SlpitStr(@a,',')
--(2)动态T-sql语句
declare @S varchar(100)
set @s='1,23,a'
declare @sql varchar(100)
set @sql='select col='''+replace(@S, ',' , ''' union all select ''')+''''
print(@sql)
---1字符串拆分
/*
有字符串1,23,a
要求按逗号拆分为表
1
23
a
*/
--(1)第一种方法(循环截取法)
create function FC_SlpitStr(@Str nvarchar(4000),@Split nvarchar(100))
returns @R table (Col nvarchar(100))
as
begin
declare @StrLen int
set @StrLen=len(@Str)
while charindex(@Split,@StrLen)>0
begin
insert into @R values(left(@Str,charindex(@Split,@StrLen)-1))
set @Str=stuff(@Str,1,charindex(@Split,@StrLen),'')
end
insert into @R values(@Str)
return
end
declare @a nvarchar(4000)
set @a='1,23,a'
select dbo.FC_SlpitStr(@a,',')
--(2)动态T-sql语句
declare @S varchar(100)
set @s='1,23,a'
declare @sql varchar(100)
set @sql='select col='''+replace(@S, ',' , ''' union all select ''')+''''
print(@sql)
设置内存选项:
--设置 min server memory 配置项
EXEC sp_configure N'min server memory (MB)', 0
--设置 max server memory 配置项
EXEC sp_configure N'max server memory (MB)', 256
--使更新生效
RECONFIGURE WITH OVERRIDE
使用文件及文件组：
/*--功能说明
下面的代码是在SQL Server 2000上创建名为 MyDB 的数据库
该数据库包括1个主要数据文件、3个用户定义的文件组和1个日志文件
ALTER DATABASE语句将用户定义文件组指定为默认文件组。
之后，通过指默认的文件组来创建表，并且将图像数据和索引放到指定的文件组中。
最后，将文件组中的指定数据文件删除
--*/
--切换到 master 数据库
USE master
GO
--A. 创建数据库 MyDB
CREATE DATABASE MyDB
ON PRIMARY --主文件组和主要数据文件
( NAME='MyDB_Primary',
FILENAME= 'c:/MyDB_Prm.mdf'),
FILEGROUP MyDB_FG1 --用户定义文件组1
( NAME = 'MyDB_FG1_Dat1',
FILENAME = 'c:/MyDB_FG1_1.ndf'), --次要数据文件1
( NAME = 'MyDB_FG1_Dat2',
FILENAME = 'd:/MyDB_FG1_2.ndf'), --次要数据文件2
FILEGROUP MyDB_FG2 --用户定义文件组2
( NAME = 'MyDB_FG1_Dat',
FILENAME = 'e:/MyDB_FG2.ndf') --次要数据文件
LOG ON --日志文件
( NAME='MyDB_log',
FILENAME ='d:/MyDB.ldf')
GO
--B. 修改默认数据文件组
ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULT
GO
--切换到新建的数据库 MyDB
USE MyDB
--C. 在默认文件组MyDB_FG1创建表,并且指定图像数据保存在用户定义文件组MMyDB_FG2
CREATE TABLE MyTable
( cola int PRIMARY KEY ,
colb char(8) ,
colc image )
TEXTIMAGE_ON MyDB_FG2
--在用户定义文件组MyDB_FG2上创建索引
CREATE INDEX IX_MyTable ON MyTable(cola) ON MyDB_FG2
GO
--D. 将要删除数据文件MyDB_FG1_Dat1上的数据转移到其他数据文件中，并且清空数据文件MyDB_FG1_Dat1
DBCC SHRINKFILE(MyDB_FG1_Dat1,EMPTYFILE)
--删除数据文件MyDB_FG1_Dat1
ALTER DATABASE MyDB REMOVE FILE MyDB_FG1_Dat1
调整tempdb数据库的文件属性：
--A. 将tempdb数据库的主数据文件大小设置为10MB。
ALTER DATABASE tempdb
MODIFY FILE(
name=tempdev,
size=100 MB)
GO
--B. 将tempdb数据库的主数据文件移动到指定的磁盘分区上，并且为其添加一个数据文件。
--移动主数据文件
ALTER DATABASE tempdb MODIFY FILE
( NAME='tempdev',
FILENAME ='d:/tempdb.mdf')
--添加次要数据文件
ALTER DATABASE tempdb ADD FILE
( NAME='tempdata_1',
FILENAME ='d:/tempdb_data_1.ndf')
日期概念理解中的一些测试：
--A. 测试 datetime 精度问题
DECLARE @t TABLE(date char(21))
INSERT @t SELECT '1900-1-1 00:00:00.000'
INSERT @t SELECT '1900-1-1 00:00:00.001'
INSERT @t SELECT '1900-1-1 00:00:00.009'
INSERT @t SELECT '1900-1-1 00:00:00.002'
INSERT @t SELECT '1900-1-1 00:00:00.003'
INSERT @t SELECT '1900-1-1 00:00:00.004'
INSERT @t SELECT '1900-1-1 00:00:00.005'
INSERT @t SELECT '1900-1-1 00:00:00.006'
INSERT @t SELECT '1900-1-1 00:00:00.007'
INSERT @t SELECT '1900-1-1 00:00:00.008'
SELECT date,转换后的日期=CAST(date as datetime) FROM @t
/*--结果
date 转换后的日期
--------------------- --------------------------
1900-1-1 00:00:00.000 1900-01-01 00:00:00.000
1900-1-1 00:00:00.001 1900-01-01 00:00:00.000
1900-1-1 00:00:00.009 1900-01-01 00:00:00.010
1900-1-1 00:00:00.002 1900-01-01 00:00:00.003
1900-1-1 00:00:00.003 1900-01-01 00:00:00.003
1900-1-1 00:00:00.004 1900-01-01 00:00:00.003
1900-1-1 00:00:00.005 1900-01-01 00:00:00.007
1900-1-1 00:00:00.006 1900-01-01 00:00:00.007
1900-1-1 00:00:00.007 1900-01-01 00:00:00.007
1900-1-1 00:00:00.008 1900-01-01 00:00:00.007
（所影响的行数为 10 行）
--*/
GO
--B. 对于 datetime 类型的纯日期和时间的十六进制表示
DECLARE @dt datetime
--单纯的日期
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(8))
--结果: 0x0000000100000000
--单纯的时间
SET @dt='00:00:01'
SELECT CAST(@dt as binary(8))
--结果: 0x000000000000012C
GO
--C. 对于 smalldatetime 类型的纯日期和时间的十六进制表示
DECLARE @dt smalldatetime
--单纯的日期
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(4))
--结果: 0x00010000
--单纯的时间
SET @dt='00:10'
SELECT CAST(@dt as binary(4))
--结果: 0x0000000A
CONVERT在日期转换中的使用示例：
--字符转换为日期时,Style的使用
--1. Style=101时,表示日期字符串为:mm/dd/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',101)
--结果:2003-11-01 00:00:00.000
--2. Style=101时,表示日期字符串为:dd/mm/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',103)
--结果:2003-01-11 00:00:00.000
/*== 日期转换为字符串 ==*/
DECLARE @dt datetime
SET @dt='2003-1-11'
--1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式
SELECT CONVERT(varchar,@dt,101)
--结果:01/11/2003
--2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式
SELECT CONVERT(varchar,@dt,103)
--结果:11/01/2003
/*== 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 ==*/
SELECT CONVERT(varchar,'2003-1-11',101)
--结果:2003-1-11
SET DATEFORMAT对日期处理的影响：
--1. /*--说明
SET DATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响
但不影响明确指定了style的CONVERT处理。
--*/
--示例 ，在下面的示例中，第一个CONVERT转换未指定style，转换的结果受SET DATAFORMAT的影响，第二个CONVERT转换指定了style，转换结果受style的影响。
--设置输入日期顺序为 日/月/年
SET DATEFORMAT DMY
--不指定Style参数的CONVERT转换将受到SET DATEFORMAT的影响
SELECT CONVERT(datetime,'2-1-2005')
--结果: 2005-01-02 00:00:00.000
--指定Style参数的CONVERT转换不受SET DATEFORMAT的影响
SELECT CONVERT(datetime,'2-1-2005',101)
--结果: 2005-02-01 00:00:00.000
GO
--2.
/*--说明
如果输入的日期包含了世纪部分，则对日期进行解释处理时
年份的解释不受SET DATEFORMAT设置的影响。
--*/
--示例，在下面的代码中，同样的SET DATEFORMAT设置，输入日期的世纪部分与不输入日期的世纪部分，解释的日期结果不同。
DECLARE @dt datetime
--设置SET DATEFORMAT为:月日年
SET DATEFORMAT MDY
--输入的日期中指定世纪部分
SET @dt='01-2002-03'
SELECT @dt
--结果: 2002-01-03 00:00:00.000
--输入的日期中不指定世纪部分
SET @dt='01-02-03'
SELECT @dt
--结果: 2003-01-02 00:00:00.000
GO
--3.
/*--说明
如果输入的日期不包含日期分隔符，那么SQL Server在对日期进行解释时
将忽略SET DATEFORMAT的设置。
--*/
--示例，在下面的代码中，不包含日期分隔符的字符日期，在不同的SET DATEFORMAT设置下，其解释的结果是一样的。
DECLARE @dt datetime
--设置SET DATEFORMAT为:月日年
SET DATEFORMAT MDY
SET @dt='010203'
SELECT @dt
--结果: 2001-02-03 00:00:00.000
--设置SET DATEFORMAT为:日月年
SET DATEFORMAT DMY
SET @dt='010203'
SELECT @dt
--结果: 2001-02-03 00:00:00.000
--输入的日期中包含日期分隔符
SET @dt='01-02-03'
SELECT @dt
--结果: 2003-02-01 00:00:00.000
SET LANGUAGE对日期处理的影响示例：
--以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。
USE master
--设置会话的语言环境为: English
SET LANGUAGE N'English'
SELECT
DATENAME(Month,GETDATE()) AS [Month],
DATENAME(Weekday,GETDATE()) AS [Weekday],
CONVERT(varchar,GETDATE(),109) AS [CONVERT]
/*--结果:
Month Weekday CONVERT
------------- -------------- -------------------------------
March Tuesday Mar 15 2005 8:59PM
--*/
--设置会话的语言环境为: 简体中文
SET LANGUAGE N'简体中文'
SELECT
DATENAME(Month,GETDATE()) AS [Month],
DATENAME(Weekday,GETDATE()) AS [Weekday],
CONVERT(varchar,GETDATE(),109) AS [CONVERT]
/*--结果
Month Weekday CONVERT
------------- --------------- -----------------------------------------
05 星期四 05 19 2005 2:49:20:607PM
--*/
日期格式化处理：
DECLARE @dt datetime
SET @dt=GETDATE()
--1．短日期格式：yyyy-m-d
SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')
--2．长日期格式：yyyy年mm月dd日
--A. 方法1
SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'
--B. 方法2
SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'
--3．长日期格式：yyyy年m月d日
SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日'
--4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm
SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)
日期推算处理：
DECLARE @dt datetime
SET @dt=GETDATE()
DECLARE @number int
SET @number=3
--1．指定日期该年的第一天或最后一天
--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+'1-1'
--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+'12-31'
--2．指定日期所在季度的第一天或最后一天
--A. 季度的第一天
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt)-2,
@dt),
120)+'1')
--B. 季度的最后一天（CASE判断法）
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)
+CASE WHEN DATEPART(Quarter,@dt) in(1,4)
THEN '31'ELSE '30' END)
--C. 季度的最后一天（直接推算法）
SELECT DATEADD(Day,-1,
CONVERT(char(8),
DATEADD(Month,
1+DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)+'1')
--3．指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')
--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
--C. 月的最后一天（容易使用的错误方法）
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))
--4．指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
--5．指定日期所在周的任意星期几
--A. 星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
--B. 星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
特殊日期加减函数：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_DateADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_DateADD]
GO
/*--特殊日期加减函数
对于日期指定部分的加减，使用DATEADD函数就可以轻松实现。
在实际的处理中，还有一种比较另类的日期加减处理
就是在指定的日期中，加上（或者减去）多个日期部分
比如将2005年3月11日，加上1年3个月11天2小时。
对于这种日期的加减处理，DATEADD函数的力量就显得有点不够。
本函数实现这样格式的日期字符串加减处理：
y-m-d h:m:s.m | -y-m-d h:m:s.m
说明：
要加减的日期字符输入方式与日期字符串相同。日期与时间部分用空格分隔
最前面一个字符如果是减号（-）的话，表示做减法处理，否则做加法处理。
如果日期字符只包含数字，则视为日期字符中，仅包含天的信息。
--*/
/*--调用示例
SELECT dbo.f_DateADD(GETDATE(),'11:10')
--*/
CREATE FUNCTION dbo.f_DateADD(
@Date datetime,
@DateStr varchar(23)
)RETURNS datetime
AS
BEGIN
DECLARE @bz int,@s varchar(12),@i int
IF @DateStr IS NULL OR @Date IS NULL
OR(CHARINDEX('.',@DateStr)>0
AND @DateStr NOT LIKE '%[:]%[:]%.%')
RETURN(NULL)
IF @DateStr='' RETURN(@Date)
SELECT @bz=CASE
WHEN LEFT(@DateStr,1)='-' THEN -1
ELSE 1 END,
@DateStr=CASE
WHEN LEFT(@Date,1)='-'
THEN STUFF(RTRIM(LTRIM(@DateStr)),1,1,'')
ELSE RTRIM(LTRIM(@DateStr)) END
IF CHARINDEX(' ',@DateStr)>1
OR CHARINDEX('-',@DateStr)>1
OR(CHARINDEX('.',@DateStr)=0
AND CHARINDEX(':',@DateStr)=0)
BEGIN
SELECT @i=CHARINDEX(' ',@DateStr+' ')
,@s=REVERSE(LEFT(@DateStr,@i-1))+'-'
,@DateStr=STUFF(@DateStr,1,@i,'')
,@i=0
WHILE @s>'' and @i<3
SELECT @Date=CASE @i
WHEN 0 THEN DATEADD(Day,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)
WHEN 1 THEN DATEADD(Month,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)
WHEN 2 THEN DATEADD(Year,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)
END,
@s=STUFF(@s,1,CHARINDEX('-',@s),''),
@i=@i+1
END
IF @DateStr>''
BEGIN
IF CHARINDEX('.',@DateStr)>0
SELECT @Date=DATEADD(Millisecond
,@bz*STUFF(@DateStr,1,CHARINDEX('.',@DateStr),''),
@Date),
@DateStr=LEFT(@DateStr,CHARINDEX('.',@DateStr)-1)+':',
@i=0
ELSE
SELECT @DateStr=@DateStr+':',@i=0
WHILE @DateStr>'' and @i<3
SELECT @Date=CASE @i
WHEN 0 THEN DATEADD(Hour,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
WHEN 1 THEN DATEADD(Minute,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
WHEN 2 THEN DATEADD(Second,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
END,
@DateStr=STUFF(@DateStr,1,CHARINDEX(':',@DateStr),''),
@i=@i+1
END
RETURN(@Date)
END
GO
查询指定日期段内过生日的人员：
--测试数据
DECLARE @t TABLE(ID int,Name varchar(10),Birthday datetime)
INSERT @t SELECT 1,'aa','1999-01-01'
UNION ALL SELECT 2,'bb','1996-02-29'
UNION ALL SELECT 3,'bb','1934-03-01'
UNION ALL SELECT 4,'bb','1966-04-01'
UNION ALL SELECT 5,'bb','1997-05-01'
UNION ALL SELECT 6,'bb','1922-11-21'
UNION ALL SELECT 7,'bb','1989-12-11'
DECLARE @dt1 datetime,@dt2 datetime
--查询 2003-12-05 至 2004-02-28 生日的记录
SELECT @dt1='2003-12-05',@dt2='2004-02-28'
SELECT * FROM @t
WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
BETWEEN @dt1 AND @dt2
OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
BETWEEN @dt1 AND @dt2
/*--结果
ID Name Birthday
---------------- ---------------- --------------------------
1 aa 1999-01-01 00:00:00.000
7 bb 1989-12-11 00:00:00.000
--*/
--查询 2003-12-05 至 2006-02-28 生日的记录
SET @dt2='2006-02-28'
SELECT * FROM @t
WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
BETWEEN @dt1 AND @dt2
OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
BETWEEN @dt1 AND @dt2
/*--查询结果
ID Name Birthday
---------------- ----------------- --------------------------
1 aa 1999-01-01 00:00:00.000
2 bb 1996-02-29 00:00:00.000
7 bb 1989-12-11 00:00:00.000
--*/
生成日期列表的函数：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getdate]
GO
/*--生成日期列表
生成指定年份的工作日/休息日列表
--邹建 2003.12(引用请保留此信息)--*/
/*--调用示例
--查询 2003 年的工作日列表
SELECT * FROM dbo.f_getdate(2003,0)
--查询 2003 年的休息日列表
SELECT * FROM dbo.f_getdate(2003,1)
--查询 2003 年全部日期列表
SELECT * FROM dbo.f_getdate(2003,NULL)
--*/
CREATE FUNCTION dbo.f_getdate(
@year int, --要查询的年份
@bz bit --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
)RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
AS
BEGIN
DECLARE @tb TABLE(ID int IDENTITY(0,1),Date datetime)
INSERT INTO @tb(Date) SELECT TOP 366 DATEADD(Year,@YEAR-1900,'1900-1-1')
FROM sysobjects a ,sysobjects b
UPDATE @tb SET Date=DATEADD(DAY,id,Date)
DELETE FROM @tb WHERE Date>DATEADD(Year,@YEAR-1900,'1900-12-31')
IF @bz=0
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM @tb
WHERE (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
ELSE IF @bz=1
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM @tb
WHERE (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 IN (0,6)
ELSE
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM @tb
RETURN
END
GO
/*====================================================================*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getdate]
GO
/*--生成列表
生成指定日期段的日期列表
--邹建 2005.03(引用请保留此信息)--*/
/*--调用示例
--查询工作日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0)
--查询休息日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',1)
--查询全部日期
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',NULL)
--*/
CREATE FUNCTION dbo.f_getdate(
@begin_date Datetime, --要查询的开始日期
@end_date Datetime, --要查询的结束日期
@bz bit --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
)RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
AS
BEGIN
DECLARE @tb TABLE(ID int IDENTITY(0,1),a bit)
INSERT INTO @tb(a) SELECT TOP 366 0
FROM sysobjects a ,sysobjects b
IF @bz=0
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
SET @begin_date=DATEADD(Day,366,@begin_date)
END
ELSE IF @bz=1
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 in(0,6)
SET @begin_date=DATEADD(Day,366,@begin_date)
END
ELSE
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
SET @begin_date=DATEADD(Day,366,@begin_date)
END
RETURN
END
GO
工作日处理函数(标准节假日)：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO
--计算两个日期相差的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime, --计算的开始日期
@dt_end datetime --计算的结束日期
)RETURNS int
AS
BEGIN
DECLARE @workday int,@i int,@bz bit,@dt datetime
IF @dt_begin>@dt_end
SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt
ELSE
SET @bz=0
SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,
@workday=@i/7*5,
@dt_begin=DATEADD(Day,@i/7*7,@dt_begin)
WHILE @dt_begin<=@dt_end
BEGIN
SELECT @workday=CASE
WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5
THEN @workday+1 ELSE @workday END,
@dt_begin=@dt_begin+1
END
RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
END
GO
/*=================================================================*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDayADD]
GO
--在指定日期上,增加指定工作天数后的日期
CREATE FUNCTION f_WorkDayADD(
@date datetime, --基础日期
@workday int --要增加的工作日数
)RETURNS datetime
AS
BEGIN
DECLARE @bz int
--增加整周的天数
SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END
,@date=DATEADD(Week,@workday/5,@date)
,@workday=@workday%5
--增加不是整周的工作天数
WHILE @workday<>0
SELECT @date=DATEADD(Day,@bz,@date),
@workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5
THEN @workday-@bz ELSE @workday END
--避免处理后的日期停留在非工作日上
WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6)
SET @date=DATEADD(Day,@bz,@date)
RETURN(@date)
END
工作日处理函数(自定义节假日)：
if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb_Holiday]
GO
--定义节假日表
CREATE TABLE tb_Holiday(
HDate smalldatetime primary key clustered, --节假日期
Name nvarchar(50) not null) --假日名称
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO
--计算两个日期之间的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime, --计算的开始日期
@dt_end datetime --计算的结束日期
)RETURNS int
AS
BEGIN
IF @dt_begin>@dt_end
RETURN(DATEDIFF(Day,@dt_begin,@dt_end)
+1-(
SELECT COUNT(*) FROM tb_Holiday
WHERE HDate BETWEEN @dt_begin AND @dt_end))
RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)
+1-(
SELECT COUNT(*) FROM tb_Holiday
WHERE HDate BETWEEN @dt_end AND @dt_begin)))
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDayADD]
GO
--在指定日期上增加工作天数
CREATE FUNCTION f_WorkDayADD(
@date datetime, --基础日期
@workday int --要增加的工作日数
)RETURNS datetime
AS
BEGIN
IF @workday>0
WHILE @workday>0
SELECT @date=@date+@workday,@workday=count(*)
FROM tb_Holiday
WHERE HDate BETWEEN @date AND @date+@workday
ELSE
WHILE @workday<0
SELECT @date=@date+@workday,@workday=-count(*)
FROM tb_Holiday
WHERE HDate BETWEEN @date AND @date+@workday
RETURN(@date)
END
计算工作时间的函数：
|
扫一扫
1962
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
