常用的SQL

-----------------------------------------------------------创建数据库
2

create database test
3

-----------------------------------------------------------选择数据库
4

use test
5

-----------------------------------------------------------创建表
6

create table test
7

(
8

id int identity(1,1) primary key ,
9

test char(50) not null
10

)
11

select * from test
13

------------------------------------------------------------新增字段名 "wwwwww" 类型 char(10)
15

ALTER TABLE dbo.test ADD
16

wwwwww char(10) NULL
17

------------------------------------------------------------更改字段名 “wwwwww” 为“wttt”
19

EXECUTE sp_rename N'dbo.test.wwwwww', N'Tmp_wttt', 'COLUMN'
20

EXECUTE sp_rename N'dbo.test.Tmp_wttt', N'wttt', 'COLUMN'
21

------------------------------------------------------------删除字段各 “wttt”
23

Alter table dbo.test drop
24

COLUMN wttt
25

------------------------------------------------------------修改字段类型
27

Alter TABLE dbo.test alter column wwwwww nvarchar(255)
28

------------------------------------------------------------查找字段中内容，转换字段类型。
32

select charindex(str1,str2)
33

select left(convert(nvarchar(30),createdatetime,120),10)
34

/* SQL交叉表实例很简单的一个东西，见网上好多朋友问“怎么实现交叉表？”，以下是我写的一个例子，数据库基于SQL SERVER 2000。
36

-- ======================================================
37

--交叉表实例
38

-- ======================================================
39

建表：
40

在查询分析器里运行：
41

*/
42

CREATE TABLE [Test] (
44

[id] [int] IDENTITY (1, 1) NOT NULL ,
45

[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
46

[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
47

[Source] [numeric](18, 0) NULL
48

) ON [PRIMARY]
49

GO
50

INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)
51

INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)
52

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)
53

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)
54

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)
55

INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)
56

INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)
57

Go
58

--交叉表语句的实现：
60

--用于：交叉表的列数是确定的
61

select name,sum(case subject when '数学' then source else 0 end) as '数学',
63

sum(case subject when '英语' then source else 0 end) as '英语',
64

sum(case subject when '语文' then source else 0 end) as '语文'
65

from test
66

group by name
67

--用于：交叉表的列数是不确定的
71

declare @sql varchar(8000)
73

set @sql = 'select name,'
74

select @sql = @sql + 'sum(case subject when '''+subject+''' then source else 0 end) as '''+subject+''','
76

from (select distinct subject from test) as a
78

select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
80

select @sql
82

exec(@sql)
83

go
85

/*-----------------------备份数据库---------------------------------------*/
88

BACKUP DATABASE [数据库名] TO DISK = N'F:\data\数据库备份' WITH NOINIT , NOUNLOAD , NAME = N'数据库备份', NOSKIP , STATS = 10, NOFORMAT
90

declare @filename nvarchar(100)
92

set @filename='E:\DataBase\back\KingCRM2008data'+convert(char(10),getdate(),112)
93

print @filename
94

BACKUP DATABASE [KingCRM2008] TO DISK = @filename WITH NOINIT , NOUNLOAD , NAME = N'KingCRM2008 备份', NOSKIP , STATS = 10, NOFORMAT
95

use pubs
98

if exists (select name from sysobjects where name='proc_test_select' and type='p')
99

drop PROC proc_test_select
100

go
101

create proc proc_test_select
102

as
103

select * from test
104

go
105

106

/*----------------------使用带有复杂 SELECT 语句的简单过程------------------------*/
107

USE pubs
108

IF EXISTS (SELECT name FROM sysobjects
109

WHERE name = 'au_info_all' AND type = 'P')
110

DROP PROCEDURE au_info_all
111

GO
112

CREATE PROCEDURE au_info_all
113

AS
114

SELECT au_lname, au_fname, title, pub_name
115

FROM authors a INNER JOIN titleauthor ta
116

ON a.au_id = ta.au_id INNER JOIN titles t
117

ON t.title_id = ta.title_id INNER JOIN publishers p
118

ON t.pub_id = p.pub_id
119

GO
120

/* end */
121

exec au_info_all
122

123

/*-----------------------使用带有参数的简单过程 --------------------------------*/
124

125

USE pubs
126

IF EXISTS (SELECT name FROM sysobjects
127

WHERE name = 'au_info' AND type = 'P')
128

DROP PROCEDURE au_info
129

GO
130

USE pubs
131

GO
132

CREATE PROCEDURE au_info
133

@lastname varchar(40),
134

@firstname varchar(20)
135

AS
136

SELECT au_lname, au_fname, title, pub_name
137

FROM authors a INNER JOIN titleauthor ta
138

ON a.au_id = ta.au_id INNER JOIN titles t
139

ON t.title_id = ta.title_id INNER JOIN publishers p
140

ON t.pub_id = p.pub_id
141

WHERE au_fname = @firstname
142

AND au_lname = @lastname
143

GO
144

145

/*au_info 存储过程可以通过以下方法执行*/
146

147

EXECUTE au_info 'Dull', 'Ann'
148

-- Or
149

EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
150

151

/*--------------------使用带有通配符参数的简单过程----------------------------------*/
152

153

154

USE pubs
155

IF EXISTS (SELECT name FROM sysobjects
156

WHERE name = 'au_info2' AND type = 'P')
157

DROP PROCEDURE au_info2
158

GO
159

USE pubs
160

GO
161

CREATE PROCEDURE au_info2
162

@lastname varchar(30) = 'D%',
163

@firstname varchar(18) = '%'
164

AS
165

SELECT au_lname, au_fname, title, pub_name
166

FROM authors a INNER JOIN titleauthor ta
167

ON a.au_id = ta.au_id INNER JOIN titles t
168

ON t.title_id = ta.title_id INNER JOIN publishers p
169

ON t.pub_id = p.pub_id
170

WHERE au_fname LIKE @firstname
171

AND au_lname LIKE @lastname
172

GO
173

174

/*--------------au_info2 存储过程可以用多种组合执行。下面只列出了部分组合：-----------*/
175

176

EXECUTE au_info2
177

-- Or
178

EXECUTE au_info2 'Wh%'
179

180

/*---------------------使用 OUTPUT 参数--------------------------*/
181

182

USE pubs
183

GO
184

IF EXISTS(SELECT name FROM sysobjects
185

WHERE name = 'titles_sum' AND type = 'P')
186

DROP PROCEDURE titles_sum
187

GO
188

USE pubs
189

GO
190

CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
191

AS
192

SELECT 'Title Name' = title
193

FROM titles
194

WHERE title LIKE @@TITLE
195

SELECT @@SUM = SUM(price)
196

FROM titles
197

WHERE title LIKE @@TITLE
198

GO
199

/*---------接下来，将该 OUTPUT 参数用于控制流语言----------*/
200

DECLARE @@TOTALCOST money
201

EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
202

IF @@TOTALCOST < 200
203

BEGIN
204

PRINT ' '
205

PRINT 'All of these titles can be purchased for less than $200.'
206

END
207

ELSE
208

SELECT 'The total cost of these titles is $'
209

+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
210

211

/*---------使用 WITH ENCRYPTION 选项----------*/
212

IF EXISTS (SELECT name FROM sysobjects
213

WHERE name = 'encrypt_this' AND type = 'P')
214

DROP PROCEDURE encrypt_this
215

GO
216

USE pubs
217

GO
218

CREATE PROCEDURE encrypt_this
219

WITH ENCRYPTION
220

AS
221

SELECT *
222

FROM authors
223

GO
224

225

EXEC sp_helptext encrypt_this
226

227

228

229

--接下来，选择加密存储过程内容的标识号和文本。
230

231

SELECT c.id, c.text
232

FROM syscomments c INNER JOIN sysobjects o
233

ON c.id = o.id
234

WHERE o.name = 'encrypt_this'
235

exec sp_decrypt encrypt_this
236

237

/*---------存储过程解密(破解函数,过程,触发器,视图.仅限于SQLSERVER2000)-------------*/
238

create PROCEDURE sp_decrypt(@objectName varchar(50))
239

AS
240

begin
241

begin tran
242

declare @objectname1 varchar(100),@orgvarbin varbinary(8000)
243

declare @sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000),@sql5 nvarchar(4000),@sql6 nvarchar(4000),@sql7 nvarchar(4000),@sql8 nvarchar(4000),@sql9 nvarchar(4000),@sql10 nvarchar(4000)
244

DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
245

declare @i int,@status int,@type varchar(10),@parentid int
246

declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int
247

select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@ObjectName)
248

249

create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int)
250

insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments WHERE id = object_id(@objectName)
251

select @number=max(number) from #temp
252

set @k=0
253

254

while @k<=@number
255

begin
256

if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
257

begin
258

if @type='P'
259

set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
260

else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS '
261

end)
262

263

if @type='TR'
264

set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
265

266

if @type='FN' or @type='TF' or @type='IF'
267

set @sql1=(case @type when 'TF' then
268

'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
269

when 'FN' then
270

'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
271

when 'IF' then
272

'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
273

end)
274

275

if @type='V'
276

set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '
277

278

set @q=len(@sql1)
279

set @sql1=@sql1+REPLICATE('-',4000-@q)
280

select @sql2=REPLICATE('-',4000),@sql3=REPLICATE('-',4000),@sql4=REPLICATE('-',4000),@sql5=REPLICATE('-',4000),@sql6=REPLICATE('-',4000),@sql7=REPLICATE('-',4000),@sql8=REPLICATE('-',4000),@sql9=REPLICATE('-',4000),@sql10=REPLICATE('-',4000)
281

exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10)
282

end
283

set @k=@k+1
284

end
285

286

set @k=0
287

while @k<=@number
288

begin
289

290

if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
291

begin
292

select @colid=max(colid) from #temp where number=@k
293

set @n=1
294

295

while @n<=@colid
296

begin
297

select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp WHERE colid=@n and number=@k
298

299

SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n and number=@k)
300

if @n=1
301

begin
302

if @type='P'
303

SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
304

else 'CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
305

end)
306

307

308

if @type='FN' or @type='TF' or @type='IF'--刚才有错改一下
309

SET @OrigSpText2=(case @type when 'TF' then
310

'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
311

when 'FN' then
312

'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
313

when 'IF' then
314

'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
315

end)
316

317

if @type='TR'
318

set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
319

320

if @type='V'
321

set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '
322

323

set @q=4000-len(@OrigSpText2)
324

set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
325

end
326

else
327

begin
328

SET @OrigSpText2=REPLICATE('-', 4000)
329

end
330

--start counter
331

SET @i=1
332

--fill temporary variable
333

334

SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))
335

336

--loop
337

WHILE @i<=datalength(@OrigSpText1)/2
338

BEGIN
339

340

SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
341

(UNICODE(substring(@OrigSpText2, @i, 1)) ^
342

UNICODE(substring(@OrigSpText3, @i, 1)))))
343

SET @i=@i+1
344

END
345

set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))
346

set @resultsp=(case when @encrypted=1
347

then @resultsp
348

else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end)
349

end)
350

print @resultsp
351

--execute( @resultsp)
352

set @n=@n+1
353

354

end
355

356

end
357

set @k=@k+1
358

end
359

360

drop table #temp
361

rollback tran
362

end
363

GO
364

365

366

/*----------------------------------------------------------------------------------------*/
367

master.dbo.xp_cmdshell 'osql -U sa -P -i e:\KingCRM\setup\setup\sql.sql'
368

posted @ 2006-07-31 17:28 ssihc 阅读(293) 评论(0) 收藏举报

刷新页面返回顶部

常用的SQL

公告