sql语句

2019-11-03 08:35:54

字体：大中小

来源：转载

供稿：网友

1. 说明：复制表(只复制结构，源表名：a，新表名：b) SQL:select * into bfrom awhere 1<>1;

2. 说明：拷贝表(拷贝数据，源表名：a，目标表名：b) SQL:insert into b(a, b, c)select d, e, ffrom b;

3. 说明：显示文章、提交人和最后回复时间 SQL:select a.title, a.username, b.adddatefrom table a,(select max(adddate) adddatefrom table where table.title=a.title) b 　

4. 说明：外连接查询(表名1：a，表名2：b) SQL:select a.a, a.b, a.c, b.c, b.d, b.ffrom aLEFT OUTJOIN bON a.a= b.c; 　

5. 说明：日程安排提前五分钟提醒 SQL:select * from 日程安排where datediff(’’minute’’, f开始时间,getdate())>5 　　　　

6. 说明：两张关联表，删除主表中已经在副表中没有的信息 SQL:delete from infowhere not exists(select * from infobzwhere info.infid=infobz.infid );

【SQL SERVER 数据库实用SQL语句】

1.按姓氏笔画排序:Select * From TableNameOrder By CustomerName Collate Chinese_PRC_Stroke_ci_as

2.分页SQL语句select * from(select (row_number()OVER (ORDER BY tab.IDDesc))as rownum,tab.* from 表名As tab)As twhere rownumbetween 起始位置And 结束位置

3.获取当前数据库中的所有用户表select * from sysobjectswhere xtype='U' and category=0

4.获取某一个表的所有字段select namefrom syscolumnswhere id=object_id('表名')

5.查看与某一个表相关的视图、存储过程、函数select a.* from sysobjects a, syscomments bwhere a.id= b.idand b.text like '%表名%'

6.查看当前数据库中所有存储过程select nameas 存储过程名称from sysobjectswhere xtype='P'

7.查询用户创建的所有数据库select * from master..sysdatabases Dwhere sidnot in(select sidfrom master..sysloginswhere name='sa') 或者select dbid, nameAS DB_NAME from master..sysdatabaseswhere sid<> 0x01

8.查询某一个表的字段和数据类型select column_name,data_typefrom information_schema.columnswhere table_name= '表名'

9.使用事务在使用一些对数据库表的临时的SQL语句操作时，可以采用SQL SERVER事务处理，防止对数据操作后发现误操作问题开始事务Begin tran Insert Into TableNameValues(…) SQL语句操作不正常，则回滚事务。回滚事务Rollback tran SQL语句操作正常，则提交事务，数据提交至数据库。提交事务Commit tran

10. 按全文匹配方式查询字段名LIKE N'%[^a-zA-Z0-9]China[^a-zA-Z0-9]%' OR 字段名LIKE N'%[^a-zA-Z0-9]China' OR 字段名LIKE N'China[^a-zA-Z0-9]%' OR 字段名LIKE N'China

11．计算执行SQL语句查询时间 declare @d datetime set @d=getdate() select * from SYS_ColumnProperties select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

12、说明：几个高级查询运算词

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)，不消除重复行。

计算一个库里各个表的记录总数：

select b.name,a.rowcntfrom sysindexes a,sysobjects bwhere a.id=b.idand a.indid<2 and b.xtype='u'

--统计数据库里每个表的详细情况 EXEC sp_MSforeachtable@command1="sp_spaceused'?'"-

-获得每个表的记录数和容量: EXEC sp_MSforeachtable@command1="print '?'",@command2="sp_spaceused'?'",@command3= "SELECT count(*)FROM ? "

排序问题

CREATE TABLE [t] (

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

[GUID] [uniqueidentifier] NULL

)ON [PRIMARY] GO

下面这句执行5次

insert tvalues (newid())

查看执行结果

select * from t

1、第一种

select * from torder by case id

when 4 then 1 when 5 then 2 when 1 then 3 when 2 then 4 when 3 then 5 end

2、第二种

select * from torder by (id+2)%6

3、第三种

select * from torder by charindex(cast(idas varchar),'45123')

4、第四种

select * from tWHERE idbetween 0 and 5 order by charindex(cast(idas varchar),'45123')

5、第五种

select * from torder by case when id>3 then id-5 else idend

6、第六种

select * from torder by id/ 4 desc,idasc

一条语句删除一批记录首先id列是int标识类类型，然后删除ID值为5,6,8,9,10,11的列，这里的cast函数不能用convert函数代替，而且转换的类型必须是varchar，而不能是char，否则就会执行出你不希望的结果，这里的"5,6,8,9,10,11"可以是你在页面上获取的一个chkboxlist构建成的值，然后用下面的一句就全部删除了，比循环用多条语句高效吧应该。delete from [fujian] where charindex(','+cast([id] as varchar)+',',','+'5,6,8,9,10,11,'+',')>0 还有一种就是delete from table1where idin(1,2,3,4 )

---动态SQL基本语法：

1 :普通SQL语句可以用exec执行

Select * from tableNameexec('select * from tableName')exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N

2:字段名，表名，数据库名之类作为变量时，必须用动态SQL

declare @fname varchar(20)

set @fname = 'FiledName'

Select @fname from tableName-- 错误,不会提示错误，但结果为固定值FiledName,并非所要。exec('select' + @fname + ' from tableName')-- 请注意加号前后的单引号的边上加空格

当然将字符串改成变量的形式也可

declare @fname varchar(20)

set @fname = 'FiledName' --设置字段名

declare @s varchar(1000)

set @s = 'select' + @fname + ' from tableName' exec(@s)-- 成功

exec sp_executesql@s -- 此句会报错

declare @s Nvarchar(1000)-- 注意此处改为nvarchar(1000)

set @s = 'select' + @fname + ' from tableName'

exec(@s)-- 成功

exec sp_executesql@s -- 此句正确

3. 输出参数

declare @num int,@sqls nvarchar(4000)

set @sqls='select count(*) from tableName'

exec(@sqls)

--如何将exec执行结果放入变量中？

declare @num int,@sqls nvarchar(4000)

set @sqls='select @a=count(*) from tableName'

exec sp_executesql@sqls,N'@a int output',

@num outputselect @num

1 :普通SQL语句可以用Exec执行例:

Select * from tableNameExec('select * from tableName')Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N

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

法一：select * into bfrom awhere 1 <>1

法二：select top 0 * into bfrom a

2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)

insert into b(a, b, c)select d,e,ffrom a;

3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

insert into b(a, b, c)select d,e,ffrom bin ‘具体数据库’where 条件

例子：..from bin '"&Server.MapPath(".")&"/data.mdb" &"' where..

4、说明：子查询(表名1：a 表名2：b)

select a,b,cfrom awhere aIN (select dfrom b ) 或者:select a,b,cfrom awhere aIN (1,2,3)

5、说明：显示文章、提交人和最后回复时间

select a.title,a.username,b.adddatefrom table a,(select max(adddate) adddatefrom table where table.title=a.title) b

6、说明：外连接查询(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.ffrom aLEFT OUTJOIN bON a.a= b.C

7、说明：在线视图查询(表名1：a )

select * from (SELECT a,b,cFROM a) Twhere t.a> 1;

8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1where timebetween time1

9、说明：in 的使用方法select * from table1where a[not] in (‘值1’,’值2’,’值4’,’值6’)

10、说明：两张关联表，删除主表中已经在副表中没有的信息

delete from table1where not exists (select * from table2where table1.field1=table2.field1 )

11、说明：四表联查问题：

select * from aleft inner join bon a.a=b.bright inner join con a.a=c.cinner join don a.a=d.dwhere .....

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,表名 bwhere b.主键字段= a.主键字段order by a.排序字段

14、说明：前10条记录

select top 10 * form table1where 范围

15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,cfrom tablename tawhere a=(select max(a)from tablename tbwhere tb.b=ta.b)

16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 (select afrom tableA )except (select afrom tableB)except (select afrom tableC)

17、说明：随机取出10条数据

select top 10 * from tablenameorder by newid()

18、说明：随机选择记录

select newid()

19、说明：删除重复记录

Delete from tablenamewhere idnot in (select max(id)from tablenamegroup by col1,col2,...)

20、说明：列出数据库里所有的表名

select namefrom sysobjectswhere type='U'

21、说明：列出表里的所有的列

select namefrom syscolumnswhere id=object_id('TableName')

22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似

select 中的case。select type,sum(case venderwhen 'A' then pcselse 0 end),sum(case venderwhen 'C' then pcselse 0 end),sum(case venderwhen 'B' then pcselse 0 end)FROM tablenamegroup by type 显示结果： type vender pcs 电脑 A1 电脑 A1 光盘 B2 光盘 A2 手机 B3 手机 C3

23、说明：初始化表

table1TRUNCATE TABLE table1

24、说明：选择从10到15的记录

select top 5 * from (select top 15 * from table order by idasc) table_别名order by iddesc declare @a varchar(100),@b varchar(20)select @a='abcdefbcmnbcde',@b='bc' select (len(@a)-len(replace(@a,@b,'')))/len(@b)

说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,cfrom tablename tawhere a=(select max(a)from tablename tbwhere tb.b=ta.b)

一年中所有为星期二的日期

select dateadd(day,x,col),'星期二' from

(

select cast('2006-1-1' as datetime)as col )across join

(SELECT top 365 b8.i+b7.i+ b6.i+ b5.i+ b4.i+b3.i+b2.i+ b1.i+ b0.i x

FROM(SELECT 0 iUNION ALL SELECT 1) b0CROSS JOIN(SELECT 0 iUNION ALL SELECT 2) b1CROSS JOIN(SELECT 0 iUNION ALL SELECT 4) b2CROSS JOIN(SELECT 0 iUNION ALL SELECT 8) b3CROSS JOIN(SELECT 0 iUNION ALL SELECT 16) b4CROSS JOIN(SELECT 0 iUNION ALL SELECT 32) b5CROSS JOIN(SELECT 0 iUNION ALL SELECT 64) b6CROSS JOIN(SELECT 0 iUNION ALL SELECT 128) b7CROSS JOIN(SELECT 0 iUNION ALL SELECT 256) b8order by 1

)bwhere datepart(dw,dateadd(day,x,col))=3

1. 查看数据库的版本

select @@version

2. 查看数据库所在机器操作系统参数

exec master..xp_msver

3. 查看数据库启动的参数

sp_configure

4. 查看数据库启动时间

select convert(varchar(30),login_time,120)from master..sysprocesseswhere spid=1

查看数据库服务器名和实例名print 'Server Name...............:' + convert(varchar(30),@@SERVERNAME)print 'Instance..................:' + convert(varchar(30),@@SERVICENAME)

5. 查看所有数据库名称及大小

sp_helpdb 重命名数据库用的SQL sp_renamedb'old_dbname','new_dbname'

6. 查看所有数据库用户登录信息 sp_helplogins 查看所有数据库用户所属的角色信息 sp_helpsrvrolemember 修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程更改某个数据对象的用户属主 sp_changeobjectowner[@objectname =] 'object',[@newowner =] 'owner' 注意: 更改对象名的任一部分都可能破坏脚本和存储过程。把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本

7. 查看链接服务器 sp_helplinkedsrvlogin 查看远端数据库用户登录信息 sp_helpremotelogin

8.查看某数据库下某个数据对象的大小 sp_spaceused@objname 还可以用sp_toptables过程看最大的N(默认为50)个表查看某数据库下某个数据对象的索引信息 sp_helpindex@objname 还可以用SP_NChelpindex过程查看更详细的索引情况 SP_NChelpindex@objname clustered索引是把记录按物理顺序排列的，索引占的空间比较少。对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。查看某数据库下某个数据对象的的约束信息 sp_helpconstraint@objname 9.查看数据库里所有的存储过程和函数use @database_name sp_stored_procedures 查看存储过程和函数的源代码 sp_helptext'@procedure_name' 查看包含某个字符串@str的数据对象名称 select distinct object_name(id)from syscommentswhere text like '%@str%' 创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数解密加密过的存储过程和函数可以用sp_decrypt过程

10.查看数据库里用户和进程的信息 sp_who 查看SQL Server数据库里的活动用户和进程的信息 sp_who'active' 查看SQL Server数据库里的锁的情况 sp_lock 进程号1--50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.spid是进程编号,dbid是数据库编号,objid是数据对象编号查看进程正在执行的SQL语句dbcc inputbuffer () 推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句 sp_who3 检查死锁用sp_who_lock过程 sp_who_lock

11.收缩数据库日志文件的方法收缩简单恢复模式数据库日志，收缩后@database_name_log的大小单位为M backup log @database_name with no_logdbcc shrinkfile (@database_name_log,5)

12.分析SQL Server SQL 语句的方法:set statistics time {on | off}set statistics io {on | off} 图形方式显示查询执行计划在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形文本方式显示查询执行计划set showplan_all {on | off}set showplan_text {on | off }set statistics profile {on | off }

13.出现不一致错误时，NT事件查看器里出3624号错误，修复数据库的方法先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然后做修复操作

alter database [@error_database_name] set single_user

修复出现不一致错误的表

dbcc checktable('@error_table_name',repair_allow_data_loss)

或者可惜选择修复出现不一致错误的小型数据库名

dbcc checkdb('@error_database_name',repair_allow_data_loss)alter database [@error_database_name] set multi_user CHECKDB 有3个参数: repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，以及删除已损坏的文本对象，这些修复可能会导致一些数据丢失。修复操作可以在用户事务下完成以允许用户回滚所做的更改。如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。修复完成后，请备份数据库。 repair_fast 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。这些修复可以很快完成，并且不会有丢失数据的危险。 repair_rebuild 执行由 repair_fast 完成的所有修复，包括需要较长时间的修复（如重建索引）。执行这些修复时不会有丢失数据的危险。

上一篇：SQL Server数据库开发的二十一条军规(SQL收藏)

下一篇： MSSQL数据库嵌套存储过程的事务控制