sql2000不常用的一些功能

建立链接

exec   sp_dropserver 'ExcelSource','droplogins'

EXEC sp_addlinkedserver 'ExcelSource',
                        'Jet 4.0',
                        'Microsoft.Jet.OLEDB.4.0',
                        'D:/STATUSWEBPM.xls',
                        NULL,
                        'Excel 5.0'

SELECT * FROM ExcelSource...[STATUSWEBPM$]

--EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'SA', 'Admin', NULL

--表名加$

到xml

create table class (id varchar(5), title varchar(10))
insert into class select 1,   '美国'
union all select 2,   '中国'

create table item(id varchar(5), classid varchar(5),  title varchar(10))
insert into item select 1,   1,        'KFC'
union all select 2,   1,        '麦当'
union all select 3,   2,        '合和'

--自定义
select tag ,parent, [row!1],[id!2],[title!3],[item!4],[id!5],[title!6]
from(
select 1 tag,null parent,null [row!1],null [id!2],null [title!3],null [item!4]
,null [id!5],null [title!6],id from class
union all select 2,1,null,id,null,null,null,null,id from class
union all select 3,1,null,null,title,null,null,null,id from class
union all select 4,1,null,null,null,null,null,null,classid+id from item
union all select 5,4,null,null,null,null,id,null,classid+id from item
union all select 6,4,null,null,null,null,null,title,classid+id from item
)a
order by id,tag
for xml explicit

--默认

SELECT
 ID = row.ID,
 title = row.title,
 [ID] = item.id,
 [title] = item.title
FROM class row, item
WHERE row.id = item.classid
FOR XML AUTO, ELEMENTS

--------------------
<row>
  <id>1</id>
  <title>美国</title>
  <item>
    <id>1</id>
    <title>KFC</title>
  </item>
  <item>
    <id>2</id>
    <title>麦当</title>
  </item>
</row>

<row>
  <id>2</id>
  <title>中国</title>
  <item>
    <id>3</id>
    <title>合和</title>
  </item>
</row>

约束操作


create table aa(id int primary Key,name varchar(10))
insert into aa select 1,'select'


create table bb
(
id int CONSTRAINT
 FK__bb__id__03F0984C Foreign Key  References aa(id)
         ON UPDATE CASCADE
  ON DELETE CASCADE--Action
,name varchar(10)
)
insert into bb select 1,'*'


ALTER   TABLE   bb   NOCHECK   CONSTRAINT   FK__bb__id__03F0984C
insert into bb select 2,'*'
update bb set id=1 where id<>1
ALTER   TABLE   bb   CHECK   CONSTRAINT   FK__bb__id__03F0984C

ALTER TABLE dbo.bb
 DROP CONSTRAINT FK__bb__id__03F0984C


drop table aa,bb

全角半角

UNICODE('~')-UNICODE('~')=65248
nchar(65281-65248)='!'

自定义系统函数

CREATE FUNCTION fn_ame()--fn_开头
RETURNS varchar(10)
AS
begin
RETURN 'myname'
end
go
exec sp_configure 'allow updates',1--allow updates用户可否修改系统表
reconfigure with override
go
exec sp_mschangeobjectowner 'fn_ame','system_function_schema'--设所有者
go
exec sp_configure 'allow updates',0
reconfigure with override

go
select fn_ame()

go
exec sp_configure 'allow updates',1
reconfigure with override
go
update sysobjects set uid = 1 where name = 'fn_ame'
go
exec sp_configure 'allow updates',0
reconfigure with override

go
drop FUNCTION fn_ame

不解发事务下的删表数据

truncate table tablename

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值