sql语句

 

                            Sql语句

 

 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 完成的所有修复，包括需要较长时间的修复（如重建索引）。执行这些修复时不会有丢失数据的危险。