* 说明:复制表(只复制结构,源表名:a 新表名:b)
select
*
into b
from a
where
1
<>
1
* 说明:拷贝表(拷贝数据,源表名:a 目标表名:b)
insert
into b(a, b, c)
select d,e,f
from b;
* 说明:显示文章、提交人和最后回复时间
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
* 说明:日程安排提前五分钟提醒
select
*
from 日程安排
where
datediff(
'
minute
',f开始时间,
getdate())
>
5
* 说明:两张关联表,删除主表中已经在副表中没有的信息
delete
from info
where
not
exists (
select
*
from infobz
where info.infid
=infobz.infid )
* 说明:
--
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (
SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,
'
YYYY/MM
')
= TO_CHAR(SYSDATE,
'
YYYY/MM
')) X,
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,
'
YYYY/MM
')
=
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,
'
YYYY/MM
')
||
'
/01
',
'
YYYY/MM/DD
')
-
1,
'
YYYY/MM
') ) Y,
WHERE X.NUM
= Y.NUM (
+)
AND X.INBOUND_QTY
+ NVL(Y.STOCK_ONHAND,
0)
<> X.STOCK_ONHAND ) B
WHERE A.NUM
= B.NUM
* 说明:
--
select
*
from studentinfo
where
not
exists(
select
*
from student
where studentinfo.id
=student.id)
and 系名称
=
'
"&strdepartmentname&"
'
and 专业名称
=
'
"&strprofessionname&"
'
order
by 性别,生源地,高考总成绩
* 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源)
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate,
'
yyyy
')
AS telyear,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
01
', a.factration))
AS JAN,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
02
', a.factration))
AS FRI,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
03
', a.factration))
AS MAR,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
04
', a.factration))
AS APR,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
05
', a.factration))
AS MAY,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
06
', a.factration))
AS JUE,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
07
', a.factration))
AS JUL,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
08
', a.factration))
AS AGU,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
09
', a.factration))
AS SEP,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
10
', a.factration))
AS OCT,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
11
', a.factration))
AS NOV,
SUM(decode(TO_CHAR(a.telfeedate,
'
mm
'),
'
12
', a.factration))
AS
DEC
FROM (
SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel
= b.telfax) a
GROUP
BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate,
'
yyyy
')
* 说明:四表联查问题
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
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
..
* 说明:得到表中最小的未使用的ID号
*
SELECT (
CASE
WHEN
EXISTS(
SELECT
*
FROM Handle b
WHERE b.HandleID
=
1)
THEN
MIN(HandleID)
+
1
ELSE
1
END)
as HandleID
FROM Handle
WHERE
NOT HandleID
IN (
SELECT a.HandleID
-
1
FROM Handle a)
* 一个SQL语句的问题:行列转换
select
*
from v_temp
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
上面的视图结果如下:
user_name role_name
--
-----------------------
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
系统管理员 管理员
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
feng 管理员
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
feng 一般用户
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
test 一般用户
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
想把结果变成这样:
user_name role_name
--
-------------------------
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
系统管理员 管理员
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
feng 管理员,一般用户
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
test 一般用户
===================
create
table a_test(name
varchar(
20),role2
varchar(
20))
insert
into a_test
values(
'
李
',
'
管理员
')
insert
into a_test
values(
'
张
',
'
管理员
')
insert
into a_test
values(
'
张
',
'
一般用户
')
insert
into a_test
values(
'
常
',
'
一般用户
')
create
function join_str(
@content
varchar(
100))
returns
varchar(
2000)
as
begin
declare
@str
varchar(
2000)
set
@str
=
''
select
@str
=
@str
+
'
,
'
+
rtrim(role2)
from a_test
where
[
name
]
=
@content
select
@str
=
right(
@str,
len(
@str)
-
1)
return
@str
end
go
--
调用:
select
[
name
],dbo.join_str(
[
name
]) role2
from a_test
group
by
[
name
]
--
select distinct name,dbo.uf_test(name) from a_test
* 快速比较结构相同的两表
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
结构相同的两表,一表有记录3万条左右,一表有记录2万条左右,我怎样快速查找两表的不同记录?
============================
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
给你一个测试方法,从northwind中的orders表取数据。
select
*
into n1
from orders
select
*
into n2
from orders
select
*
from n1
select
*
from n2
--
添加主键,然后修改n1中若干字段的若干条
alter
table n1
add
constraint pk_n1_id
primary
key (OrderID)
alter
table n2
add
constraint pk_n2_id
primary
key (OrderID)
select OrderID
from (
select
*
from n1
union
select
*
from n2) a
group
by OrderID
having
count(
*)
>
1
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
应该可以,而且将不同的记录的ID显示出来。
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
下面的适用于双方记录一样的情况,
select
*
from n1
where orderid
in (
select OrderID
from (
select
*
from n1
union
select
*
from n2) a
group
by OrderID
having
count(
*)
>
1)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
至于双方互不存在的记录是比较好处理的
--
删除n1,n2中若干条记录
delete
from n1
where orderID
in (
'
10728
',
'
10730
')
delete
from n2
where orderID
in (
'
11000
',
'
11001
')
--
*************************************************************
--
双方都有该记录却不完全相同
select
*
from n1
where orderid
in(
select OrderID
from (
select
*
from n1
union
select
*
from n2) a
group
by OrderID
having
count(
*)
>
1)
union
--
n2中存在但在n1中不存的在10728,10730
select
*
from n1
where OrderID
not
in (
select OrderID
from n2)
union
--
n1中存在但在n2中不存的在11000,11001
select
*
from n2
where OrderID
not
in (
select OrderID
from n1)
* 四种方法取表里n到m条纪录:
1.
select
top m
*
into 临时表(或表变量)
from tablename
order
by columnname
--
将top m笔插入
set
rowcount n
select
*
from 表变量
order
by columnname
desc
2.
select
top n
*
from (
select
top m
*
from tablename
order
by columnname) a
order
by columnname
desc
3.如果tablename里没有其他identity列,那么:
select
identity(
int) id0,
*
into #
temp
from tablename
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
取n到m条的语句为:
select
*
from #
temp
where id0
>=n
and id0
<= m
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
如果你在执行select
identity(
int) id0,
*
into #
temp
from tablename这条语句的时候报错,那是因为你的DB中间的select
into
/bulkcopy属性没有打开要先执行:
exec sp_dboption 你的DB名字,
'
select into/bulkcopy
',true
4.如果表里有identity属性,那么简单:
select
*
from tablename
where
identitycol
between n
and m
* 如何删除一个表中重复的记录?
create
table a_dist(id
int,name
varchar(
20))
insert
into a_dist
values(
1,
'
abc
')
insert
into a_dist
values(
1,
'
abc
')
insert
into a_dist
values(
1,
'
abc
')
insert
into a_dist
values(
1,
'
abc
')
exec up_distinct
'
a_dist
',
'
id
'
select
*
from a_dist
create
procedure up_distinct(
@t_name
varchar(
30),
@f_key
varchar(
30))
--
f_key表示是分组字段﹐即主键字段
as
begin
declare
@max
integer,
@id
varchar(
30) ,
@sql
varchar(
7999) ,
@type
integer
select
@sql
=
'
declare cur_rows cursor for select
'
+
@f_key
+
'
,count(*) from
'
+
@t_name
+
'
group by
'
+
@f_key
+
'
having count(*) > 1
'
exec(
@sql)
open cur_rows
fetch cur_rows
into
@id,
@max
while
@@fetch_status
=
0
begin
select
@max
=
@max
-
1
set
rowcount
@max
select
@type
= xtype
from syscolumns
where id
=
object_id(
@t_name)
and name
=
@f_key
if
@type
=
56
select
@sql
=
'
delete from
'
+
@t_name
+
'
where
'
+
@f_key
+
'
=
'
+
@id
if
@type
=
167
select
@sql
=
'
delete from
'
+
@t_name
+
'
where
'
+
@f_key
+
'
=
'
+
''''
+
@id
+
''''
exec(
@sql)
fetch cur_rows
into
@id,
@max
end
close cur_rows
deallocate cur_rows
set
rowcount
0
end
select
*
from systypes
select
*
from syscolumns
where id
=
object_id(
'
a_dist
')
* 查询数据的最大排序问题(只能用一条语句写)
CREATE
TABLE hard (qu
char (
11) ,co
char (
11) ,je numeric(
3,
0))
insert
into hard
values (
'
A
',
'
1
',
3)
insert
into hard
values (
'
A
',
'
2
',
4)
insert
into hard
values (
'
A
',
'
4
',
2)
insert
into hard
values (
'
A
',
'
6
',
9)
insert
into hard
values (
'
B
',
'
1
',
4)
insert
into hard
values (
'
B
',
'
2
',
5)
insert
into hard
values (
'
B
',
'
3
',
6)
insert
into hard
values (
'
C
',
'
3
',
4)
insert
into hard
values (
'
C
',
'
6
',
7)
insert
into hard
values (
'
C
',
'
2
',
3)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
要求查询出来的结果如下:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
qu co je
--
--------- ----------- -----
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A
6
9
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A
2
4
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
B
3
6
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
B
2
5
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
C
6
7
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
C
3
4
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
就是要按qu分组,每组中取je最大的前2位!!
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
而且只能用一句sql语句!!!
select
*
from hard a
where je
in (
select
top
2 je
from hard b
where a.qu
=b.qu
order
by je)
* 求删除重复记录的sql语句?
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
怎样把具有相同字段的纪录删除,只留下一条。
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
例如,表test里有id,name字段
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
如果有name相同的记录 只留下一条,其余的删除。
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
name的内容不定,相同的记录数不定。
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
有没有这样的sql语句?
==============================
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A:一个完整的解决方案:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
将重复的记录记入temp1表:
select
[
标志字段id
],
count(
*)
into temp1
from
[
表名
]
group
by
[
标志字段id
]
having
count(
*)
>
1
2、将不重复的记录记入temp1表:
insert temp1
select
[
标志字段id
],
count(
*)
from
[
表名
]
group
by
[
标志字段id
]
having
count(
*)
=
1
3、作一个包含所有不重复记录的表:
select
*
into temp2
from
[
表名
]
where 标志字段id
in(
select 标志字段id
from temp1)
4、删除重复表:
delete
[
表名
]
5、恢复表:
insert
[
表名
]
select
*
from temp2
6、删除临时表:
drop
table temp1
drop
table temp2
================================
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
B:
create
table a_dist(id
int,name
varchar(
20))
insert
into a_dist
values(
1,
'
abc
')
insert
into a_dist
values(
1,
'
abc
')
insert
into a_dist
values(
1,
'
abc
')
insert
into a_dist
values(
1,
'
abc
')
exec up_distinct
'
a_dist
',
'
id
'
select
*
from a_dist
create
procedure up_distinct(
@t_name
varchar(
30),
@f_key
varchar(
30))
--
f_key表示是分组字段﹐即主键字段
as
begin
declare
@max
integer,
@id
varchar(
30) ,
@sql
varchar(
7999) ,
@type
integer
select
@sql
=
'
declare cur_rows cursor for select
'
+
@f_key
+
'
,count(*) from
'
+
@t_name
+
'
group by
'
+
@f_key
+
'
having count(*) > 1
'
exec(
@sql)
open cur_rows
fetch cur_rows
into
@id,
@max
while
@@fetch_status
=
0
begin
select
@max
=
@max
-
1
set
rowcount
@max
select
@type
= xtype
from syscolumns
where id
=
object_id(
@t_name)
and name
=
@f_key
if
@type
=
56
select
@sql
=
'
delete from
'
+
@t_name
+
'
where
'
+
@f_key
+
'
=
'
+
@id
if
@type
=
167
select
@sql
=
'
delete from
'
+
@t_name
+
'
where
'
+
@f_key
+
'
=
'
+
''''
+
@id
+
''''
exec(
@sql)
fetch cur_rows
into
@id,
@max
end
close cur_rows
deallocate cur_rows
set
rowcount
0
end
select
*
from systypes
select
*
from syscolumns
where id
=
object_id(
'
a_dist
')
* 行列转换
--
普通
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
假设有张学生成绩表(CJ)如下
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Name Subject Result
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
张三 语文
80
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
张三 数学
90
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
张三 物理
85
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
李四 语文
85
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
李四 数学
92
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
李四 物理
82
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
想变成
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
姓名 语文 数学 物理
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
张三
80
90
85
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
李四
85
92
82
declare
@sql
varchar(
4000)
set
@sql
=
'
select Name
'
select
@sql
=
@sql
+
'
,sum(case Subject when
'''
+Subject
+
'''
then Result end) [
'
+Subject
+
'
]
'
from (
select
distinct Subject
from CJ)
as a
select
@sql
=
@sql
+
'
from test group by name
'
exec(
@sql)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
行列转换
--
合并
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
有表A,
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
id pid
1
1
1
2
1
3
2
1
2
2
3
1
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
如何化成表B:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
id pid
1
1,
2,
3
2
1,
2
3
1
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
创建一个合并的函数
create
function fmerg(
@id
int)
returns
varchar(
8000)
as
begin
declare
@str
varchar(
8000)
set
@str
=
''
select
@str
=
@str
+
'
,
'
+
cast(pid
as
varchar)
from 表A
where id
=
@id
set
@str
=
right(
@str,
len(
@str)
-
1)
return(
@str)
End
go
--
调用自定义函数得到结果
select
distinct id,dbo.fmerg(id)
from 表A
* 如何取得一个数据表的所有列名
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
方法如下:先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL语句如下:
declare
@objid
int,
@objname
char(
40)
set
@objname
=
'
tablename
'
select
@objid
= id
from sysobjects
where id
=
object_id(
@objname)
select
'
Column_name
'
= name
from syscolumns
where id
=
@objid
order
by colid
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
或
SELECT
*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME
=
'
users
'
* 通过SQL语句来更改用户的密码
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
修改别人的,需要sysadmin role
EXEC sp_password
NULL,
'
newpassword
',
'
User
'
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
如果帐号为SA执行EXEC sp_password
NULL,
'
newpassword
', sa
* 怎么判断出一个表的哪些字段不允许为空?
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE
=
'
NO
'
and TABLE_NAME
=tablename
* 如何在数据库里找到含有相同字段的表?
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
a. 查已知列名的情况
SELECT b.name
as TableName,a.name
as columnname
From syscolumns a
INNER
JOIN sysobjects b
ON a.id
=b.id
AND b.type
=
'
U
'
AND a.name
=
'
你的字段名字
'
* 未知列名查所有在不同表出现过的列名
Select o.name
As tablename,s1.name
As columnname
From syscolumns s1, sysobjects o
Where s1.id
= o.id
And o.type
=
'
U
'
And
Exists (
Select
1
From syscolumns s2
Where s1.name
= s2.name
And s1.id
<> s2.id
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
)
* 查询第xxx行数据
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
假设id是主键:
select
*
from (
select
top xxx
*
from yourtable) aa
where
not
exists(
select
1
from (
select
top xxx
-
1
*
from yourtable) bb
where aa.id
=bb.id)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
如果使用游标也是可以的
fetch absolute
[
number
]
from
[
cursor_name
]
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
行数为绝对行数
* SQL Server日期计算
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
a. 一个月的第一天
SELECT
DATEADD(mm,
DATEDIFF(mm,
0,
getdate()),
0)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
b. 本周的星期一
SELECT
DATEADD(wk,
DATEDIFF(wk,
0,
getdate()),
0)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
c. 一年的第一天
SELECT
DATEADD(yy,
DATEDIFF(yy,
0,
getdate()),
0)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
d. 季度的第一天
SELECT
DATEADD(qq,
DATEDIFF(qq,
0,
getdate()),
0)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
e. 上个月的最后一天
SELECT
dateadd(ms,
-
3,
DATEADD(mm,
DATEDIFF(mm,
0,
getdate()),
0))
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
f. 去年的最后一天
SELECT
dateadd(ms,
-
3,
DATEADD(yy,
DATEDIFF(yy,
0,
getdate()),
0))
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
g. 本月的最后一天
SELECT
dateadd(ms,
-
3,
DATEADD(mm,
DATEDIFF(m,
0,
getdate())
+
1,
0))
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
h. 本月的第一个星期一
select
DATEADD(wk,
DATEDIFF(wk,
0,
dateadd(dd,
6
-
datepart(
day,
getdate()),
getdate())
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
),
0)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
i. 本年的最后一天
SELECT
dateadd(ms,
-
3,
DATEADD(yy,
DATEDIFF(yy,
0,
getdate())
+
1,
0))。
* 获取表结构
[
把 'sysobjects' 替换 成 'tablename' 即可
]
SELECT
CASE
IsNull(I.name,
'')
When
''
Then
''
Else
'
*
'
End
as IsPK,
Object_Name(A.id)
as t_name,
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A.name
as c_name,
IsNull(
SubString(M.
text,
1,
254),
'')
as pbc_init,
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
T.name
as F_DataType,
CASE
IsNull(
TYPEPROPERTY(T.name,
'
Scale
'),
'')
WHEN
''
Then
Cast(A.prec
as
varchar)
ELSE
Cast(A.prec
as
varchar)
+
'
,
'
+
Cast(A.scale
as
varchar)
END
as F_Scale,
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A.isnullable
as F_isNullAble
FROM Syscolumns
as A
JOIN Systypes
as T
ON (A.xType
= T.xUserType
AND A.Id
=
Object_id(
'
sysobjects
') )
LEFT
JOIN ( SysIndexes
as I
JOIN Syscolumns
as A1
ON ( I.id
= A1.id
and A1.id
=
object_id(
'
sysobjects
')
and (I.status
&
0x800)
=
0x800
AND A1.colid
<= I.keycnt) )
ON ( A.id
= I.id
AND A.name
=
index_col(
'
sysobjects
', I.indid, A1.colid) )
LEFT
JOIN SysComments
as M
ON ( M.id
= A.cdefault
and
ObjectProperty(A.cdefault,
'
IsConstraint
')
=
1 )
ORDER
BY A.Colid
ASC
* 提取数据库内所有表的字段详细说明的SQL语句
SELECT
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
case
when a.colorder
=
1
then d.name
else
''
end) N
'
表名
',
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
a.colorder N
'
字段序号
',
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
a.name N
'
字段名
',
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
case
when
COLUMNPROPERTY( a.id,a.name,
'
IsIdentity
')
=
1
then
'
√
'
else
''
end) N
'
标识
',
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
case
when (
SELECT
count(
*)
FROM sysobjects
WHERE (name
in
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
SELECT name
FROM sysindexes
WHERE (id
= a.id)
AND (indid
in
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
SELECT indid
FROM sysindexkeys
WHERE (id
= a.id)
AND (colid
in
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
SELECT colid
FROM syscolumns
WHERE (id
= a.id)
AND (name
= a.name)))))))
AND
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(xtype
=
'
PK
'))
>
0
then
'
√
'
else
''
end) N
'
主键
',
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
b.name N
'
类型
',
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
a.length N
'
占用字节数
',
COLUMNPROPERTY(a.id,a.name,
'
PRECISION
')
as N
'
长度
',
isnull(
COLUMNPROPERTY(a.id,a.name,
'
Scale
'),
0)
as N
'
小数位数
',
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
case
when a.isnullable
=
1
then
'
√
'
else
''
end) N
'
允许空
',
isnull(e.
text,
'') N
'
默认值
',
isnull(g.
[
value
],
'')
AS N
'
字段说明
'
FROM syscolumns a
left
join systypes b
on a.xtype
=b.xusertype
inner
join sysobjects d
on a.id
=d.id
and d.xtype
=
'
U
'
and d.name
<>
'
dtproperties
'
left
join syscomments e
on a.cdefault
=e.id
left
join sysproperties g
on a.id
=g.id
AND a.colid
= g.smallid
order
by
object_name(a.id),a.colorder
* 快速获取表test的记录总数
[
对大容量表非常有效
]
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
快速获取表test的记录总数:
select rows
from sysindexes
where id
=
object_id(
'
test
')
and indid
in (
0,
1)
update
2
set KHXH
=(ID
+
1)\
2 2行递增编号
update
[
23
]
set id1
=
'
No.
'
+
right(
'
00000000
'
+id,
6)
where id
not
like
'
No%
'
//递增
update
[
23
]
set id1
=
'
No.
'
+
right(
'
00000000
'
+
replace(id1,
'
No.
',
''),
6)
//补位递增
delete
from
[
1
]
where (id
%
2)
=
1
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
奇数
* 替换表名字段
update
[
1
]
set domurl
=
replace(domurl,
'
Upload/Imgswf/
',
'
Upload/Photo/
')
where domurl
like
'
%Upload/Imgswf/%
'
* 截位
SELECT
LEFT(表名,
5)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
熟悉SQL SERVER 2000的数据库管理员都知道,其DTS可以进行数据的导入导出,其实,我们也可以使用Transact
-SQL语句进行导入导出操作。在 Transact
-SQL语句中,我们主要使用OpenDataSource函数、
OPENROWSET 函数,关于函数的详细说明,请参考SQL联机帮助。利用下述方法,可以十分容易地实现SQL SERVER、ACCESS、EXCEL数据转换,详细说明如下:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
一、SQL SERVER 和ACCESS的数据导入导出
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
常规的数据导入导出:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
使用DTS向导迁移你的Access数据到SQL Server,你可以使用这些步骤:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
○1在SQL SERVER企业管理器中的Tools(工具)菜单上,选择Data Transformation
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
○2Services(数据转换服务),然后选择 czdImport Data(导入数据)。
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
○3在Choose a Data Source(选择数据源)对话框中选择Microsoft Access
as the Source,然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
○4在Choose a Destination(选择目标)对话框中,选择Microsoft OLE DB Prov ider
for SQL Server,选择数据库服务器,然后单击必要的验证方式。
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
○5在Specify
Table Copy(指定表格复制)或Query(查询)对话框中,单击Copy tables(复制表格)。
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
○6在Select Source Tables(选择源表格)对话框中,单击Select
All(全部选定)。下一步,完成。
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Transact
-SQL语句进行导入导出:
1.在SQL SERVER里查询access数据:
SELECT
*
FROM
OpenDataSource(
'
Microsoft.Jet.OLEDB.4.0
',
'
Data Source="c:\DB.mdb";User ID=Admin;Password=
')
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
表名
2.将access导入SQL server
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
在SQL SERVER 里运行:
SELECT
*
INTO newtable
FROM
OPENDATASOURCE (
'
Microsoft.Jet.OLEDB.4.0
',
'
Data Source="c:\DB.mdb";User ID=Admin;Password=
' )
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
表名
3.将SQL SERVER表里的数据插入到Access表中
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
在SQL SERVER 里运行:
insert
into
OpenDataSource(
'
Microsoft.Jet.OLEDB.4.0
',
'
Data Source=" c:\DB.mdb";User ID=Admin;Password=
')
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
表名 (列名1,列名2)
select 列名1,列名2
from sql表
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
实例:
insert
into
OPENROWSET(
'
Microsoft.Jet.OLEDB.4.0
',
'
C:\db.mdb
';
'
admin
';
'', Test)
select id,name
from Test
INSERT
INTO
OPENROWSET(
'
Microsoft.Jet.OLEDB.4.0
',
'
c:\trade.mdb
';
'
admin
';
'', 表名)
SELECT
*
FROM sqltablename
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
二、SQL SERVER 和EXCEL的数据导入导出
1、在SQL SERVER里查询Excel数据:
SELECT
*
FROM
OpenDataSource(
'
Microsoft.Jet.OLEDB.4.0
',
'
Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0
')
[
Sheet1$
]
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT
*
FROM
OpenDataSource (
'
Microsoft.Jet.OLEDB.4.0
',
'
Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0
')
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
xactions
2、将Excel的数据导入SQL server :
SELECT
*
into newtable
FROM
OpenDataSource(
'
Microsoft.Jet.OLEDB.4.0
',
'
Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0
')
[
Sheet1$
]
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
实例:
SELECT
*
into newtable
FROM
OpenDataSource(
'
Microsoft.Jet.OLEDB.4.0
',
'
Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0
')
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
xactions
3、将SQL SERVER中查询到的数据导成一个Excel文件
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
T
-SQL代码:
EXEC master..xp_cmdshell
'
bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""
'
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
参数:S 是SQL服务器名;U是用户;P是密码
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
说明:还可以导出文本文件等多种格式
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
实例:
EXEC master..xp_cmdshell
'
bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"
'
EXEC master..xp_cmdshell
'
bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword
'
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
在VB6中应用ADO导出EXCEL文件代码:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Dim cn
As New ADODB.Connection
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
cn.
open "Driver
={SQL Server};Server
=WEBSVR;
DataBase
=WebMis;UID
=sa;WD
=
123;"
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
cn.
execute "master..xp_cmdshell
'
bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword
'"
4、在SQL SERVER里往Excel插入数据:
insert
into
OpenDataSource(
'
Microsoft.Jet.OLEDB.4.0
',
'
Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0
')
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
table1 (A1,A2,A3)
values (
1,
2,
3)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
T
-SQL代码:
INSERT
INTO
OPENDATASOURCE(
'
Microsoft.JET.OLEDB.4.0
',
'
Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls
')
[
Filiale1$
] (bestand, produkt)
VALUES (
20,
'
Test
')