sql语句大全_sql语句基础到提升

一、普通

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

注：列减少后将不许简略。db第22中学列加上后数据典型也不许变换，独一能变换的是减少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/cross （outer） join：

全外贯穿：不只囊括标记贯穿表的配合行，还囊括两个贯穿表中的一切记载。

12、分批:group by:

   一张表，一旦分批 实行后，查问后只能获得组关系的消息。

    组关系的消息：（统计消息） count,sum,max,min,avg  分批的规范)

    在sqlserver平分秋色组时：不许以text,ntext,image典型的字段动作分批按照

   在selecte统计因变量中的字段，不许和普遍的字段放在一道；

13、对数据库举行操纵：

   辨别数据库： sp_detach_db; 附加数据库：sp_attach_db 后接表白，附加须要完备的路途名

14.怎样窜改数据库的称呼:

sp_renamedb 'old_name', 'new_name'

二、提高

1、证明：复构图(只复制构造,源表名：a 新表名：b) (access可用)

法一：select * into b from a where 1<>1（仅用来sqlserver）

法二：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.排序字段

简直实行：

对于数据库分页：

  declare @start int,@end int

  @sql  nvarchar(600)

  set @sql=’select top’+str(@end-@start+1)+’+from t where rid not in(select top’+str(@str-1)+’rid from t where rid>-1)’

  exec sp_executesql @sql

提防：在top后不许径直跟一个变量，以是在本质运用中惟有如许的举行特出的处置。rid为一个标识列，即使top后再有简直的字段，如许做利害常有长处的。由于如许不妨制止 top的字段即使是论理索引的，查问的截止后本质表中的不普遍（论理索引中的数占有大概和数据表中的不普遍，而查问时即使处在索引则开始查问索引）

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、证明：简略反复记载

1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

2),select distinct * into temp from tablename

  delete from tablename

  insert into tablename select * from temp

评介： 这种操纵牵扯洪量的数据的挪动，这种做法不符合大含量但数据操纵

3),比方：在一个外部表中导出数据，因为某些因为第一次只导出了一局部，但很难确定简直场所，如许惟有鄙人一次十足导出，如许也就爆发许多反复的字段，还好吗简略反复字段

alter table tablename

--增添一个自增列

add  column_b int identity(1,1)

 delete from tablename where column_b not in(

select max(column_b)  from tablename group by column1,column2,...)

alter table tablename drop column column_b

20、证明：列出数据库里一切的表名

select name from sysobjects where type='u' // 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

案例：

犹如下表，诉求就裱中一切沒有合格的成績，在历次增長0.1的基礎上，使他們剛好合格:

    name     score

    zhangshan   80

    lishi       59

    wangwu      50

    songquan    69

while((select min(score) from tb_table)<60)

begin

update tb_table set score =score*1.01

where score<60

if  (select min(score) from tb_table)>60

  break

 else

    continue

end

数据开拓-典范

1.按姓氏笔画排序:

select * from tablename order by customername collate chinese_prc_stroke_ci_as //从少到多

2.数据库加密:

select encrypt('原始暗号')

select pwdencrypt('原始暗号')

select pwdcompare('原始暗号','加密后暗号') = 1--沟通；要不不沟通 encrypt('原始暗号')

select pwdencrypt('原始暗号')

select pwdcompare('原始暗号','加密后暗号') = 1--沟通；要不不沟通

3.取回表中字段:

declare @list varchar(1000),

@sql nvarchar(1000)

select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表a'

set @sql='select '+right(@list,len(@list)-1)+' from 表a'

exec (@sql)

4.察看硬盘分区:

exec master..xp_fixeddrives

5.比拟a,b表能否十分:

if (select checksum_agg(binary_checksum(*)) from a)

     =

    (select checksum_agg(binary_checksum(*)) from b)

print '十分'

else

print '不十分'

6.杀掉一切的事变侦视器过程:

declare hcforeach cursor global for select 'kill '+rtrim(spid) from master.dbo.sysprocesses

where program_name in('sql profiler',n'sql 事变探查器')

exec sp_msforeach_worker '?'

7.记载探求:

发端到n条记载

select top n * from 表

-------------------------------

n到m条记载(要有主索引id)

select top m-n * from 表 where id in (select top m id from 表) order by id   desc

----------------------------------

n到结果记载

select top n * from 表 order by id desc

案例

比方1：一张表有一万多条记载，表的第一个字段 recid 是自延长字段， 写一个sql语句， 找到表的第31到第40个记载。

 select top 10 recid from a where recid not  in(select top 30 recid from a)

领会：即使如许写会爆发某些题目，即使recid在表中生存论理索引。

    select top 10 recid from a where……是从索引中搜索，尔后面包车型的士select top 30 recid from a则在数据表中搜索，如许因为索引中的程序有大概和数据表中的不普遍，如许就引导查问到的不是从来的欲获得的数据。

处置计划

1， 用order by select top 30 recid from a order by ricid 即使该字段不是自延长，就会展示题目

2， 在谁人子查问中也加前提：select top 30 recid from a where recid>-1

例2：查问表中的结果以条记载，并不领会这个表公有多少量据,以及表构造。

set @s = 'select top 1 * from t   where pid not in (select top ' + str(@count-1) + ' pid  from  t)'

print @s      exec  sp_executesql  @s

9：获得暂时数据库中的一切用户表

select name from sysobjects where xtype='u' and status>=0