常用的SQL
1-----------------------------------------------------------创建数据库
2create database test
3-----------------------------------------------------------选择数据库
4use test
5-----------------------------------------------------------创建表
6create table test
7(
8 id int identity(1,1) primary key ,
9 test char(50) not null
10)
11
12select * from test
13
14------------------------------------------------------------新增字段名 "wwwwww" 类型 char(10)
15ALTER TABLE dbo.test ADD
16 wwwwww char(10) NULL
17
18------------------------------------------------------------更改字段名 “wwwwww” 为“wttt”
19EXECUTE sp_rename N'dbo.test.wwwwww', N'Tmp_wttt', 'COLUMN'
20EXECUTE sp_rename N'dbo.test.Tmp_wttt', N'wttt', 'COLUMN'
21
22------------------------------------------------------------删除字段各 “wttt”
23Alter table dbo.test drop
24 COLUMN wttt
25
26------------------------------------------------------------修改字段类型
27Alter TABLE dbo.test alter column wwwwww nvarchar(255)
28
29
30
31------------------------------------------------------------查找字段中内容,转换字段类型。
32select charindex(str1,str2)
33select left(convert(nvarchar(30),createdatetime,120),10)
34
35 /* SQL交叉表实例很简单的一个东西,见网上好多朋友问“怎么实现交叉表?”,以下是我写的一个例子,数据库基于SQL SERVER 2000。
36 -- ======================================================
37 --交叉表实例
38 -- ======================================================
39 建表:
40 在查询分析器里运行:
41 */
42
43CREATE 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]
49GO
50INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)
51INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)
52INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)
53INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)
54INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)
55INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)
56INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)
57Go
58
59--交叉表语句的实现:
60--用于:交叉表的列数是确定的
61
62select 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 '语文'
65from test
66group by name
67
68
69
70--用于:交叉表的列数是不确定的
71
72declare @sql varchar(8000)
73set @sql = 'select name,'
74
75select @sql = @sql + 'sum(case subject when '''+subject+''' then source else 0 end) as '''+subject+''','
76
77 from (select distinct subject from test) as a
78
79select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
80
81select @sql
82exec(@sql)
83
84go
85
86
87/*-----------------------备份数据库---------------------------------------*/
88
89BACKUP DATABASE [数据库名] TO DISK = N'F:\data\数据库备份' WITH NOINIT , NOUNLOAD , NAME = N'数据库 备份', NOSKIP , STATS = 10, NOFORMAT
90
91declare @filename nvarchar(100)
92set @filename='E:\DataBase\back\KingCRM2008data'+convert(char(10),getdate(),112)
93print @filename
94BACKUP DATABASE [KingCRM2008] TO DISK = @filename WITH NOINIT , NOUNLOAD , NAME = N'KingCRM2008 备份', NOSKIP , STATS = 10, NOFORMAT
95
96
97use pubs
98if exists (select name from sysobjects where name='proc_test_select' and type='p')
99 drop PROC proc_test_select
100go
101create proc proc_test_select
102as
103select * from test
104go
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
125USE pubs
126IF EXISTS (SELECT name FROM sysobjects
127 WHERE name = 'au_info' AND type = 'P')
128 DROP PROCEDURE au_info
129GO
130USE pubs
131GO
132CREATE PROCEDURE au_info
133 @lastname varchar(40),
134 @firstname varchar(20)
135AS
136SELECT 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
143GO
144
145/*au_info 存储过程可以通过以下方法执行*/
146
147EXECUTE au_info 'Dull', 'Ann'
148-- Or
149EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
150
151/*--------------------使用带有通配符参数的简单过程----------------------------------*/
152
153
154USE pubs
155IF EXISTS (SELECT name FROM sysobjects
156 WHERE name = 'au_info2' AND type = 'P')
157 DROP PROCEDURE au_info2
158GO
159USE pubs
160GO
161CREATE PROCEDURE au_info2
162 @lastname varchar(30) = 'D%',
163 @firstname varchar(18) = '%'
164AS
165SELECT au_lname, au_fname, title, pub_name
166FROM 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
170WHERE au_fname LIKE @firstname
171 AND au_lname LIKE @lastname
172GO
173
174/*--------------au_info2 存储过程可以用多种组合执行。下面只列出了部分组合:-----------*/
175
176EXECUTE au_info2
177-- Or
178EXECUTE au_info2 'Wh%'
179
180/*---------------------使用 OUTPUT 参数--------------------------*/
181
182USE pubs
183GO
184IF EXISTS(SELECT name FROM sysobjects
185 WHERE name = 'titles_sum' AND type = 'P')
186 DROP PROCEDURE titles_sum
187GO
188USE pubs
189GO
190CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
191AS
192SELECT 'Title Name' = title
193FROM titles
194WHERE title LIKE @@TITLE
195SELECT @@SUM = SUM(price)
196FROM titles
197WHERE title LIKE @@TITLE
198GO
199 /*---------接下来,将该 OUTPUT 参数用于控制流语言----------*/
200DECLARE @@TOTALCOST money
201EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
202IF @@TOTALCOST < 200
203BEGIN
204 PRINT ' '
205 PRINT 'All of these titles can be purchased for less than $200.'
206END
207ELSE
208 SELECT 'The total cost of these titles is $'
209 + RTRIM(CAST(@@TOTALCOST AS varchar(20)))
210
211/*---------使用 WITH ENCRYPTION 选项----------*/
212IF EXISTS (SELECT name FROM sysobjects
213 WHERE name = 'encrypt_this' AND type = 'P')
214 DROP PROCEDURE encrypt_this
215GO
216USE pubs
217GO
218CREATE PROCEDURE encrypt_this
219WITH ENCRYPTION
220AS
221SELECT *
222FROM authors
223GO
224
225EXEC sp_helptext encrypt_this
226
227
228
229--接下来,选择加密存储过程内容的标识号和文本。
230
231SELECT c.id, c.text
232FROM syscomments c INNER JOIN sysobjects o
233 ON c.id = o.id
234WHERE o.name = 'encrypt_this'
235exec sp_decrypt encrypt_this
236
237/*---------存储过程解密(破解函数,过程,触发器,视图.仅限于SQLSERVER2000)-------------*/
238create PROCEDURE sp_decrypt(@objectName varchar(50))
239AS
240begin
241begin tran
242declare @objectname1 varchar(100),@orgvarbin varbinary(8000)
243declare @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)
244DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
245declare @i int,@status int,@type varchar(10),@parentid int
246declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int
247select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@ObjectName)
248
249create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int)
250insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments WHERE id = object_id(@objectName)
251select @number=max(number) from #temp
252set @k=0
253
254while @k<=@number
255begin
256if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
257begin
258if @type='P'
259set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
260else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS '
261end)
262
263if @type='TR'
264set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
265
266if @type='FN' or @type='TF' or @type='IF'
267set @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 '
269when 'FN' then
270'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
271when 'IF' then
272'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
273end)
274
275if @type='V'
276set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '
277
278set @q=len(@sql1)
279set @sql1=@sql1+REPLICATE('-',4000-@q)
280select @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)
281exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10)
282end
283set @k=@k+1
284end
285
286set @k=0
287while @k<=@number
288begin
289
290if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
291begin
292select @colid=max(colid) from #temp where number=@k
293set @n=1
294
295while @n<=@colid
296begin
297select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp WHERE colid=@n and number=@k
298
299SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n and number=@k)
300if @n=1
301begin
302if @type='P'
303SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
304else 'CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
305end)
306
307
308if @type='FN' or @type='TF' or @type='IF'--刚才有错改一下
309SET @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 '
311when 'FN' then
312'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
313when 'IF' then
314'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
315end)
316
317if @type='TR'
318set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
319
320if @type='V'
321set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '
322
323set @q=4000-len(@OrigSpText2)
324set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
325end
326else
327begin
328SET @OrigSpText2=REPLICATE('-', 4000)
329end
330--start counter
331SET @i=1
332--fill temporary variable
333
334SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))
335
336--loop
337WHILE @i<=datalength(@OrigSpText1)/2
338BEGIN
339
340SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
341(UNICODE(substring(@OrigSpText2, @i, 1)) ^
342UNICODE(substring(@OrigSpText3, @i, 1)))))
343SET @i=@i+1
344END
345set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))
346set @resultsp=(case when @encrypted=1
347then @resultsp
348else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end)
349end)
350print @resultsp
351--execute( @resultsp)
352set @n=@n+1
353
354end
355
356end
357set @k=@k+1
358end
359
360drop table #temp
361rollback tran
362end
363GO
364
365
366/*----------------------------------------------------------------------------------------*/
367master.dbo.xp_cmdshell 'osql -U sa -P -i e:\KingCRM\setup\setup\sql.sql'
368