SQL二〇一〇语句大全,语句查询表结构

一、基础

 SQL3000系统表的应用 

1、表达:创建数据库
CREATE DATABASE database-name
2、表达:删除数据库
drop database dbname
3、说明:备份sql server
--- 创制 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明:创制新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

–1:获取当前数据库中的全体客户表

一、基础
1、表明:创立数据库
CREATE DATABASE 数据库名
2、表达:删除数据库
drop database 数据库名
3、说明:备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、表明:成立新表
create table 表名(字段1 类型1 [not null] [primary key],字段2 类型2 [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 表名
6、表明:扩大三个列
Alter table 表名 add 字段名 col 类型
注:列增添后将无法去除。DB2中列加上后数据类型也不能够改变,独一能退换的是加多varchar类型的长度。
7、表达:增添主键: Alter table 表名 add constraint 约束名 primary key(字段)
申明:删除主键: Alter table 表名 drop primary key(字段)
8、表明:创集中建索引:create [unique] [clustered]|nonclustered] index 索引名 on 表名(字段)
Unique→是或不是是独一索引; nonclustered→非聚焦索引;clustered→聚焦索引
删去索引:drop index 索引名
注:索引是不足退换的,想更动必须删除重新建。
9、表明:成立视图:create view 视图名 as select语句
去除视图:drop view 视图名
10、表明:多少个简易的询问sql语句
规范查询:select * from 表名 where 范围(条件)
插入:insert into 表名(字段1,字段2……) values (值1,值2……)
删除钦赐条件的数据:delete from 表名 where 范围(条件)
更新:update 表名 set 字段1=值1,字段2=值2…… where 范围(条件)
混淆查找:select * from 表名 where 字段 like ’%value1%’ ---like的语法不大巧,查资料!
Select * from 表名 where 字段 like ‘value%’
Select * from 表名 where 字段 like ‘%value’
排序:select * from 表名 order by 字段1,字段2 [desc]
Desc→降序排列;asc→升序排列
总数:select count(*) from 表名
求和:select sum(字段) as 总计 from 表名
平均:select avg(字段) as 平均 from 表名
最大:select max(字段) as 最大 from 表名
最小:select min(字段) as 最小 from 表名
11、表达:多少个高档查询运算词
A: UNION 运算符
UNION 运算符通过整合其余七个结果表(举例 TABLE1 和 TABLE2)并消去表中另外重复行而派生出一个结出表。当 ALL 随 UNION 一齐使用时(即 UNION ALL),不拔除重复行。三种情景下,派生表的每一行不是源于 TABLE1 正是源于 TABLE2。
B: EXCEPT 运算符
EXCEPT 运算符通过包罗持有在 TABLE1 中但不在 TABLE第22中学的行并消除全数重复行而派生出二个结果表。当 ALL 随 EXCEPT 一同利用时 (EXCEPT ALL),不拔除重复行。
C: INTERSECT 运算符
INTEENCORESECT 运算符通过只囊括 TABLE1 和 TABLE2中都局地行并消除全部重复行而派生出叁个结实表。当 ALL 随 INTE昂科拉SECT 一同行使时 (INTE冠道SECT 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
只顾:out是足以简轻便单的
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'

基于已有些表创制新表:
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 田野(field)1 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 中但不在 TABLE第22中学的行并消除全部重复行而派生出多个结出表。当 ALL 随 EXCEPT 一齐使用时 (EXCEPT ALL),不排除重复行。
C: INTERSECT 运算符
INTEWranglerSECT运算符通过只囊括 TABLE1 和 TABLE第22中学都部分行并解决全数重复行而派生出两个结实表。当 ALL随 INTECRUISERSECT 一同利用时 (INTEMuranoSECT 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总计函数中的字段,不能够和平常的字段放在一块儿;

select Name from sysobjects where xtype=’u’ and status>=0

二、提升
1、表明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)(把a表的组织复制到b表)
法一:select * into b from a where 1<>1(仅用于SQlServer)
法二:select top 0 * into b from a
Select * into b from a 将表中a的多寡和结构全复制到b表中
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

13、对数据库实行操作:
暌违数据库: sp_detach_db;附加数据库:sp_attach_db 后接评释,附加须要完整的路线名
14.哪些修改数据库的名目:
sp_renamedb 'old_name', 'new_name'

–2:获取某一个表的拥有字段

注意:在top后不能一直跟三个变量,所以在骨子里运用中唯有这么的展开非常规的管理。Rid为多少个标志列,如若top后还会有具体的字段,那样做是万分有补益的。因为这么能够制止top的字段如果是逻辑索引的,查询的结果后其实表中的不一致样(逻辑索引中的数据有希望和数据表中的不雷同,而查询时假诺处在索引则率先查询索引)
14、说明:前10条记录
select top 10 * form 表名 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、表达:列出数据Curry有所的表名
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

二、提升

select name from syscolumns where id=object_id(‘表名’)

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)

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:查看与某一个表相关的视图、存款和储蓄进度、函数

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

3、表明:跨数据库之间表的正片(具体数目应用相对路线) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子:..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like ‘%表名%’

10、SQL SEEscortVEEscort中一向循环写入数据
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

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)

–4:查看当前数据库中具有存款和储蓄进度

数量开辟-精粹

5、表明:呈现小说、提交人和终极回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

select name as 存款和储蓄进度名称 from sysobjects where xtype=’P’

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表是不是等于:

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

–5:查询客商创设的全部数据库

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条记录

7、表明:在线视图查询(表名1:a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;

select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name=’sa’)

Select Top N * From 表

N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by

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

或者

ID Desc

N到结尾记录
Select Top N * From 表 Order by ID Desc
案例
举个例子说1:一张表有10000多条记下,表的率先个字段 RecID 是自增加字段, 写叁个SQL语句, 寻觅表的第31到第叁十五个记录。
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
10:获取某贰个表的兼具字段
select name from syscolumns where id=object_id('表名')
select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')
三种办法的成效等同
11:查看与某三个表相关的视图、存款和储蓄进度、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
12:查看当前数据库中持有存款和储蓄进程
select name as 存款和储蓄进程名称 from sysobjects where xtype='P'
13:查询客商创造的保有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
14:查询某叁个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
15:差别服务器数据库之间的多少操作
--创立链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQ英雄联盟EDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '
--查询示例
select * from ITSV.数据库名.dbo.表名
--导入示例
select * into 表 from ITSV.数据库名.dbo.表名
--以后不再行使时去除链接服务器
exec sp_dropserver 'ITSV ', 'droplogins '

--连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset
--查询示例
select * from openrowset( 'SQ英雄结盟EDB ', 'sql服务器名 '; '客户名 '; '密码 ',数据库名.dbo.表名)
--生开支地球表面
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '客户名 '; '密码 ',数据库名.dbo.表名)

--把地方表导入远程表
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '顾客名 '; '密码 ',数据库名.dbo.表名)
select *from 本地表
--更新本地球表面
update b
set b.列A=a.列A
from openrowset( 'SQ英雄联盟EDB ', 'sql服务器名 '; '顾客名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地球表面 b
on a.column1=b.column1
--openquery用法需求创建八个老是
--首先创立叁个连接成立链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQ英雄联盟EDB ', '远程服务器名或ip地址 '
--查询
select *
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
--把地点表导入远程表
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
select * from 本地表
--更新本地球表面
update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A

--3、opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta
--把地点表导入远程表
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
select * from 本地表
SQL Server基本函数
SQL Server基本函数
1.字符串函数 长度与深入分析用
1,datalength(Char_expr) 再次来到字符串满含字符数,但不分包后边的空格
2,substring(expression,start,length) 取子串,字符串的下标是从“1”,start为开第三地点,length为字符串长度,实际运用中以len(expression)猎取其尺寸
3,right(char_expr,int_expr) 重临字符串左侧第int_expr个字符,还用left于之相反
4,isnull( check_expression , replacement_value )如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类
5,Sp_addtype 自定義數據類型
例如:EXEC sp_addtype birthday, datetime, 'NULL'
6,set nocount {on|off}
使重返的结果中不含有关于受 Transact-SQL 语句影响的行数的新闻。要是存款和储蓄进度中包涵的部分说话并不回来非常多实际的数量,则该装置由于大气削减了网络流量,因而可眼看提升品质。SET NOCOUNT 设置是在奉行或运转时设置,并不是在深入分析时设置。
SET NOCOUNT 为 ON 时,不回去计数(表示受 Transact-SQL 语句影响的行数)。
SET NOCOUNT 为 OFF 时,再次来到计数
常识

在SQL查询中:from后最多能够跟多少张表或视图:256
在SQL语句中出现 Order by,查询时,先排序,后取
在SQL中,一个字段的最大体量是柒仟,而对于nvarchar(伍仟),由于nvarchar是Unicode码。

SQLServer3000同步复制技巧完成步骤
一、 预备职业
1.公布服务器,订阅服务器都成立贰个同名的windows客商,并安装一样的密码,做为公布快速照相文件夹的实惠访问客户
--管理工具
--计算机管理
--客户和组
--右键客户
--新建客户
--创立贰个附属于administrator组的登陆windows的客户(SynUser)
2.在揭发服务器上,新建贰个分享目录,做为揭橥的快速照相文件的贮存目录,操作:
本身的管理器--D: 新建三个目录,名叫: PUB
--右键那一个新建的目录
--属性--共享
--采取"分享该公文夹"
--通过"权限"按纽来设置具体的客户权限,保障第一步中创制的顾客(SynUser) 具备对该文件夹的保有权限

--确定
3.装置SQL代理(SQLSESportageVERAGENT)服务的开发银行客户(发表/订阅服务器均做此设置)
开端--程序--管理工科具--服务
--右键SQLSERVERAGENT
--属性--登陆--选择"此账户"
--输入也许选取第一步中成立的windows登入客户名(SynUser)
--"密码"中输入该客户的密码
4.装置SQL Server身份验证格局,消除连接时的权杖难点(公布/订阅服务器均做此设置)
商厦管理器
--右键SQL实例--属性
--安全性--身份验证
--选择"SQL Server 和 Windows"
--确定
5.在发布服务器和订阅服务器上互相注册
商家管理器
--右键SQL Server组
--新建SQL Server注册...
--下一步--可用的服务器中,输入你要登记的中远间隔服务器名 --增多
--下一步--连接使用,选拔第3个"SQL Server身份验证"
--下一步--输入顾客名和密码(SynUser)
--下一步--采取SQL Server组,也得以创设三个新组
--下一步--完成
6.对此只好用IP,不能够用Computer名的,为其登记服务器小名(此步在施行中没用到)
(在连接端配置,例如,在订阅服务器上安插来说,服务器名称中输入的是发布服务器的IP)
开班--程序--Microsoft SQL Server--客商端网络实用工具
--别名--添加
--网络库选取"tcp/ip"--服务器外号输入SQL服务器名
--连接参数--服务器名称中输入SQL服务器ip地址
--假诺您改改了SQL的端口,打消接纳"动态调节端口",并输入相应的端口号
二、 正式配置
1、配置公布服务器
开垦集团管理器,在发表服务器(B、C、D)上实行以下步骤:
(1) 从[工具]下拉菜单的[复制]子菜单中挑选[配备发表、订阅服务器和散发]出现布局公布和分发向导
(2) [下一步] 接纳分发服务器 能够选拔把宣布服务器自个儿视作分发服务器也许别的sql的服务器(采纳本人)
(3) [下一步] 设置快速照相文件夹
接纳默许\servernamePub
(4) [下一步] 自定义配置
能够接纳:是,让自己设置分发数据库属性启用发布服务器或设置公布设置
否,使用下列暗中认可设置(推荐)
(5) [下一步] 设置分发数据库名称和岗位 选取暗中同意值
(6) [下一步] 启用发布服务器 选拔作为公布的服务器
(7) [下一步] 接纳须要公布的数据库和公布项目
(8) [下一步] 选拔注册订阅服务器
(9) [下一步] 实现布局
2、成立出版物
发表服务器B、C、D上
(1)从[工具]菜单的[复制]子菜单中挑选[创设和保管宣布]命令
(2)采取要成立出版物的数据库,然后单击[创立发布]
(3)在[创办公布早先]的唤醒对话框中单击[下一步]系统就能够弹出三个对话框。对话框上的原委是复制的多个种类。我们后天选第贰个相当于私下认可的快速照相发表(其他多个我们可以去探视协助)
(4)单击[下一步]系统要求钦赐能够订阅该布告的数据库服务器类型,
SQLSE本田CR-VVE路虎极光允许在分歧的数据库如 orACLE或ACCESS之间开展多少复制。
只是在这里间大家选择运营"SQL SELANDVE奥德赛 三千"的数据库服务器
(5)单击[下一步]系统就弹出七个定义作品的对话框约等于接纳要出版的表
瞩目: 借使前方选拔了作业宣布 则再这一步中不得不采用带有主键的表
(6)选拔公布名称和陈述
(7)自定义发表属性 向导提供的采取:
是 小编将自定义数据筛选,启用佚名订阅和或别的自定义属性
否 遵照钦命形式创建公布 (建议利用自定义的点子)
(8)[下一步] 接纳筛选宣布的章程
(9)[下一步] 能够选拔是或不是允许佚名订阅
1)假诺选用具名订阅,则供给在发布服务器上增添订阅服务器
方法: [工具]->[复制]->[安顿公布、订阅服务器和分发的属性]->[订阅服务器] 中添加
要不在订阅服务器上呼吁订阅时会出现的晋升:改宣布分裂意无名订阅
若是依旧须要佚名订阅则用以下消除办法
[集团管理器]->[复制]->[颁发内容]->[属性]->[订阅选项] 选拔允许无名乞请订阅
2)假诺接纳佚名订阅,则配备订阅服务器时不会出现上述提醒
(10)[下一步] 设置快速照相 代理程序调治
(11)[下一步] 达成布局
当成功出版物的创始后创立出版物的数据库也就成为了八个分享数据库
有数据
srv1.库名..author有字段:id,name,phone,
srv2.库名..author有字段:id,name,telphone,adress

要求:
srv1.库名..author增添记录则srv1.库名..author记录扩大
srv1.库名..author的phone字段更新,则srv1.库名..author对应字段telphone更新
--*/

--大概的管理步骤
--1.在 srv1 上创办连接服务器,以便在 srv1 中操作 srv2,完结共同
exec sp_addlinkedserver 'srv2','','SQ英雄结盟EDB','srv2的sql实例名或ip'
exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'
go
--2.在 srv1 和 srv2 这两台Computer中,运行msdtc(分布式事务管理服务),何况安装为活动运营
。作者的Computer--调节面板--管理工科具--服务--右键 Distributed Transaction Coordinator--属性--运转--并将开发银行项目设置为自动运行
go

--然后创建贰个功课定时调用地点的共同管理存款和储蓄进度就行了

供销合作社管理器
--管理
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)"
--"数据库"选择实施命令的数据库
--"命令"中输入要实行的口舌: exec p_process
--确定
--"调度"项
--新建调节
--"名称"中输入调节名称
--"调整项目"中选用你的功课实施布置
--假诺选用"一再出现"
--点"改动"来设置你的日子铺排

下一场将SQL Agent服务运转,并安装为机关运行,不然你的课业不会被实行

设置情势:
本身的Computer--调控面板--处理工科具--服务--右键 SQLSE福特ExplorerVERAGENT--属性--运行项目--选取"自动运行"--鲜明.

--3.贯彻联机管理的方法2,定期同步

--在srv第11中学开创如下的同步处理存款和储蓄进程
create proc p_process
as
--更新修改过的多寡
update b set name=i.name,telphone=i.telphone
from srv2.库名.dbo.author b,author i
where b.id=i.id and
(b.name <> i.name or b.telphone <> i.telphone)

--插入新添的数量
insert srv2.库名.dbo.author(id,name,telphone)
select id,name,telphone from author i
where not exists(
select * from srv2.库名.dbo.author where id=i.id)

--删除已经去除的数码(借使需求的话)
delete b
from srv2.库名.dbo.author b
where not exists(
select * from author where id=b.id)
go

9、表达:in 的施用办法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

10、表达:两张关联表,删除主表中曾经在副表中从未的新闻
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

–6:查询某一个表的字段和数据类型

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 .....

select column_name,data_type from information_schema.columns 
where table_name = ‘表名’

12、表明:日程布置提早五分钟提醒
SQL: select * from 日程安插 where datediff('minute',f开端时间,getdate())>5

–7:获得表字段的汇报 
select name, 
(select value from sysproperties where id = syscolumns.id and smallid=syscolumns.colid) as 描述 
from syscolumns where id=object_id(‘表名’)

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

1、表明:创造数据库

@sql nvarchar(600)

  CREATE DATABASE database-name

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)’

  2、表达:删除数据库

exec sp_executesql @sql

  drop database dbname

在乎:在top后无法平昔跟四个变量,所以在实际上采纳中独有这么的进行超过常规规的管理。Rid为四个标记列,假若top后还应该有具体的字段,那样做是拾壹分有收益的。因为那样能够幸免top的字段假设是逻辑索引的,查询的结果后实在表中的不均等(逻辑索引中的数占有相当的大希望和数目表中的不雷同,而查询时一旦处在索引则第一查询索引)

  3、说明:备份sql server

14、说明:前10条记录
select top 10 * form table1 where 范围

  --- 创设 备份数据的 device

15、表达:选拔在每一组b值同样的数量中对应的a最大的笔录的具备新闻(类似这样的用法能够用来论坛每月排名榜,每月抢手产品分析,按学科战绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

  USE master

16、表达:包罗富有在 TableA中但不在 TableB和TableC中的行并化解所有重复行而派生出二个结出表
(select a from tableA ) except (select a from tableB) except (select a from tableC)

  EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'

17、表明:随机抽出10条数据
select top 10 * from tablename order by newid()

  --- 开始 备份

18、表明:随机选拔记录
select newid()

  BACKUP DATABASE pubs TO testBack

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),比方:在八个表面表中程导弹入数据,由于一些原因首先次只导入了一部分,但很难料定具体地方,那样独有在下二次全部导入,那样也就生出不菲双重的字段,怎么着删除重复字段

  4、表明:创设新表

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

  create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

20、表达:列出数据Curry有所的表名
select name from sysobjects where type='U' // U代表客商

  依照已部分表创立新表:

21、表明:列出表里的具备的列名
select name from syscolumns where id=object_id('TableName')

  A:create table tab_new like tab_old (使用旧表创设新表)

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

  B:create table tab_new as select col1,col2… from tab_old definition only

23、表达:起头化表table1

  5、表明:删除新表

TRUNCATE TABLE table1

  drop table tabname

24、说明:选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

  6、表达:增添三个列

三、技巧

  Alter table tabname add column col type

1、1=1,1=2的行使,在SQL语句组适那时候用的相当多

  注:列扩大后将无法去除。DB第22中学列加上后数据类型也无法改造,独一能改动的是扩张varchar类型的尺寸。

“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

  7、表达:加多主键: Alter table tabname add primary key(col)

大家得以一贯写成

  表明:删除主键: Alter table tabname drop primary key(col)

错误!未找到目录项。
set @strSQL = 'select count(*) as Total from [' @tblName '] where 1=1 安定 ' @strWhere 2、收缩数据库
--重新建立索引
DBCC REINDEX
DBCC INDEXDEFRAG
--减少数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE

  8、表达:创设索引:create [unique] index idxname on tabname(col….)

3、压缩数据库
dbcc shrinkdatabase(dbname)

  删除索引:drop index idxname

4、转移数据库给新顾客以已存在客户权限
exec sp_change_users_login 'update_one','newname','oldname'
go

  注:索引是不可改造的,想改造必得删除重新建。

5、检查备份集
RESTORE VERIFYONLY from disk='E:dvbbs.bak'

  9、表明:成立视图:create view viewname as select statement

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

  删除视图:drop view viewname

7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

  10、表明:多少个简易的着力的sql语句

USE tablename -- 要操作的多少库名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日记文件的尺寸(M)

  选择:select * from table1 where 范围

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)

  插入:insert into table1(field1,field2) values(value1,value2)

DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' db_name() ' WITH TRUNCATE_ONLY'

  删除:delete from table1 where 范围

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

  更新:update table1 set field1=value1 where 范围

8、表达:退换有些表
exec sp_changeobjectowner 'tablename','dbo'

  查找:select * from table1 where 田野同志1 like ’%value1%’ ---like的语法很精细,查资料!

9、存款和储蓄改造全体表

  排序:select * from table1 order by field1,field2 [desc]

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS

  总数:select count as totalcount from table1

DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)

  求和:select sum(field1) as sumvalue from table1

DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name

  平均:select avg(field1) as avgvalue from table1

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

  最大:select max(field1) as maxvalue from table1

FETCH NEXT FROM curObject INTO @Name, @Owner
END

  最小:select min(field1) as minvalue from table1

close curObject
deallocate curObject
GO

  11、表明:多少个高等查询运算词

10、SQL SE途睿欧VE奥迪Q5中向来循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i 1
end
案例:
就像下表,须求就裱中颇负沒有及格的成績,在每一次增長0.1的基礎上,使他們剛好及格:

  A: UNION 运算符

Name     score

Zhangshan   80

Lishi       59

Wangwu      50

Songquan    69

  UNION 运算符通过整合其余三个结果表(比方 TABLE1 和 TABLE2)并消去表中其余重复行而派生出叁个结果表。当 ALL 随 UNION 一齐利用时(即 UNION ALL),不化解重复行。三种情状下,派生表的每一行不是根源 TABLE1 便是根源 TABLE2。

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

  B: EXCEPT 运算符

begin

  EXCEPT 运算符通过满含全部在 TABLE1 中但不在 TABLE第22中学的行并解决全数重复行而派生出叁个结实表。当 ALL 随 EXCEPT 一起行使时 (EXCEPT ALL),不排除重复行。

update tb_table set score =score*1.01

  C: INTERSECT 运算符

where score<60

  INTE安德拉SECT 运算符通过只囊括 TABLE1 和 TABLE第22中学都一些行并化解全部重复行而派生出叁个结出表。当 ALL 随 INTEQashqaiSECT 一齐使用时 (INTE中华VSECT ALL),不清除重复行。

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

  注:使用运算词的多少个查询结果行必得是千篇一律的。

break

  12、表达:使用外接连

else

  A、left (outer) join:

continue

  左外连接(左连接):结果集几归纳连接表的相称行,也席卷左连接表的享有行。

end

  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:

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--同样;不然不相同样

  C:full/cross (outer) join:

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

  12、分组:Group by:

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条记录

  组相关的音信:(计算音信) count,sum,max,min,avg 分组的正儿八经)

Select Top N * From 表

N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by

  在SQLServer中分组时:不可能以text,ntext,image类型的字段作为分组借助

ID Desc

N到最后记录
Select Top N * From 表 Order by ID Desc
案例
比方说1:一张表有两千0多条记下,表的首先个字段 RecID 是自增进字段, 写贰个SQL语句, 寻觅表的第31到第叁14个记录。

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

10:获取某三个表的具备字段
select name from syscolumns where id=object_id('表名')

select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')

三种办法的成效等同

11:查看与某四个表相关的视图、存款和储蓄进程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

12:查看当前数据库中兼有存款和储蓄进程
select name as 存款和储蓄进程名称 from sysobjects where xtype='P'

13:查询客商创设的富有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

14:查询某二个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'

15:差别服务器数据库之间的数额操作

--创建链接服务器

exec sp_addlinkedserver 'ITSV ', ' ', 'SQ英雄联盟EDB ', '远程服务器名或ip地址 '

exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '

--查询示例

select * from ITSV.数据库名.dbo.表名

--导入示例

select * into 表 from ITSV.数据库名.dbo.表名

--以往不再使用时去除链接服务器

exec sp_dropserver 'ITSV ', 'droplogins '

--连接远程/局域网数据(openrowset/openquery/opendatasource)

--1、openrowset

--查询示例

select * from openrowset( 'SQ英雄缔盟EDB ', 'sql服务器名 '; '顾客名 '; '密码 ',数据库名.dbo.表名)

--生花费地表

select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '客户名 '; '密码 ',数据库名.dbo.表名)

--把本地球表面导入远程表

insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '客户名 '; '密码 ',数据库名.dbo.表名)

select *from 本地表

--更新本地球表面

update b

set b.列A=a.列A

from openrowset( 'SQ英雄联盟EDB ', 'sql服务器名 '; '顾客名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地球表面 b

on a.column1=b.column1

--openquery用法须要创立贰个三回九转

--首先成立贰个连续创造链接服务器

exec sp_addlinkedserver 'ITSV ', ' ', 'SQ英雄联盟EDB ', '远程服务器名或ip地址 '

--查询

select *

FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

--把本地球表面导入远程表

insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

select * from 本地表

--更新本地球表面

update b

set b.列B=a.列B

FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a

inner join 本地表 b on a.列A=b.列A

--3、opendatasource/openrowset

SELECT *

FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta

--把当地球表面导入远程表

insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名

select * from 本地表

SQL Server基本函数

SQL Server基本函数

1.字符串函数 长度与解析用

1,datalength(Char_expr) 重临字符串包蕴字符数,但不含有后边的空格
2,substring(expression,start,length) 取子串,字符串的下标是从“1”,start为发端地点,length为字符串长度,实际利用中以len(expression)取得其长度
3,right(char_expr,int_expr) 重回字符串侧面第int_expr个字符,还用left于之相反
4,isnull( check_expression , replacement_value )如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类

5,Sp_addtype自定義數據類型
例如:EXEC sp_addtype birthday, datetime, 'NULL'
6,set nocount {on|off}

使再次回到的结果中不含有关于受 Transact-SQL 语句影响的行数的音讯。假如存款和储蓄进程中包蕴的部分言语并不回来大多事实上的数据,则该装置由于大气回降了网络流量,因而可分明提升质量。SET NOCOUNT 设置是在推行或运转时设置,并非在条分缕析时设置。SET NOCOUNT 为 ON 时,不回去计数(表示受 Transact-SQL 语句影响的行数)。

SET NOCOUNT

为 OFF 时,重返计数
常识

在SQL查询中:from后最多能够跟多少张表或视图:256在SQL语句中冒出 Order by,查询时,先排序,后取在SQL中,一个字段的最大体量是七千,而对此nvarchar(5000),由于nvarchar是Unicode码。

SQLServer2000

协助举行理并答复制技巧完结步骤
一、 预备职业

1.通知服务器,订阅服务器都创设二个同名的windows客户,并安装一样的密码,做为公布快速照相文件夹的实惠访问顾客--管理工科具--Computer管理--顾客和组--右键客户--新建客户--创立八个专门项目于administrator组的登入windows的顾客(SynUser)2.在文告服务器上,新建二个分享目录,做为发布的快速照相文件的贮存目录,操作:
自家的微型Computer--D: 新建一个目录,名称为: PUB

--右键这几个新建的目录--属性--分享--选取"分享该公文夹"--通过"权限"按纽来设置具体的顾客权限,保险第一步中创制的客商(SynUser) 具有对该文件夹的装有权限

--分明3.设置SQL代理(SQLSE悍马H2VERAGENT)服务的启航客户(发表/订阅服务器均做此设置)
始发--程序--管理工科具--服务

--右键SQLSE奥迪Q5VERAGENT--属性--登录--选取"此账户"--输入大概选择第一步中创制的windows登入客商名(SynUser)--"密码"中输入该顾客的密码4.装置SQL Server身份验证形式,消除连接时的权能难题(发表/订阅服务器均做此设置)
集团管理器

--右键SQL实例--属性--安全性--身份验证--选取"SQL Server 和 Windows"--鲜明5.在昭示服务器和订阅服务器上互动注册
商厦管理器

--右键SQL Server组--新建SQL Server注册...--下一步--可用的服务器中,输入你要登记的远程服务器名 --增多--下一步--连接使用,采用第二个"SQL Server身份验证"--下一步--输入客户名和密码(SynUser)--下一步--选拔SQL Server组,也足以创制一个新组--下一步--完结6.对此只好用IP,不可能用Computer名的,为其登记服务器别名(此步在奉行中没用到) (在连接端配置,比方,在订阅服务器上配置来讲,服务器名称中输入的是揭穿服务器的IP)
开班--程序--Microsoft SQL Server--客商端网络实用工具

--外号--加多--网络库选用"tcp/ip"--服务器别称输入SQL服务器名--连接参数--服务器名称中输入SQL服务器ip地址--要是您改改了SQL的端口,撤消选拔"动态调节端口",并输入相应的端口号
二、 正式配置

1、配置发布服务器
开搜罗团管理器,在发表服务器(B、C、D)上实践以下步骤:

(1) 从[工具]下拉菜单的[复制]子菜单中选择[布局发表、订阅服务器和散发]出现布局宣布和散发向导(2) [下一步] 选拔分发服务器 可以选择把发布服务器本身看成分发服务器可能别的sql的服务器(选取本身)(3) [下一步] 设置快速照相文件夹
使用暗中同意servernamePub

(4) [下一步] 自定义配置
能够挑选:是,让本人设置分发数据库属性启用发布服务器或安装公布设置
否,使用下列私下认可设置(推荐)

(5) [下一步] 设置分发数据库名称和职责 采取默许值(6) [下一步] 启用公布服务器 选拔作为揭橥的服务器(7) [下一步] 选拔需求表露的数据库和公布项目(8) [下一步] 选取注册订阅服务器(9) [下一步] 实现布局2、创造出版物
揭露服务器B、C、D上

(1)从[工具]菜单的[复制]子菜单中采纳[创设和管理宣布]命令(2)接纳要开创下版物的数据库,然后单击创立公布在[创造公布开头]的提醒对话框中单击[下一步]系统就能够弹出四个对话框。对话框上的剧情是复制的八个品类。大家前日选第3个约等于私下认可的快照发表(其余四个我们能够去寻访支持)(4)单击[下一步]系统需求钦定能够订阅该布告的数据库服务器类型,SQLSEOdysseyVE逍客允许在不一样的数据库如 orACLE或ACCESS之间开展多少复制。
可是在此大家采纳运维"SQL SEEvoqueVERAV4 3000"的数据库服务器

(5)单击[下一步]系统就弹出一个定义小说的对话框也等于选项要出版的表
专心: 纵然前方选拔了政工公布 则再这一步中不得不接纳带有主键的表

(6)选拔公布名称和描述(7)自定义宣布属性 向导提供的选项:
是 笔者将自定义数据筛选,启用无名氏订阅和或另外自定义属性
否 依据钦赐格局开创公布 (建议采纳自定义的章程)

(8)[下一步] 选拔筛选揭橥的不二等秘书诀(9)[下一步] 能够接纳是还是不是允许佚名订阅1)若是采纳签名订阅,则必要在发表服务器上增多订阅服务器
方法: [工具]->[复制]->[安顿公布、订阅服务器和分发的属性]->[订阅服务器] 中添加
要不在订阅服务器上呼吁订阅时会出现的升迁:改发布差别意无名订阅
一经依然要求无名氏订阅则用以下消除办法

[供销合作社管理器]->[复制]->[宣告内容]->[属性]->[订阅选项] 选拔允许佚名乞请订阅2)如果选用佚名订阅,则布置订阅服务器时不会产出上述提示(10)[下一步] 设置快速照相 代理程序调解(11)[下一步] 实现布局
当成功出版物的创始后成立出版物的数据库也就改成了一个分享数据库
有数据

srv1.库名..author有字段:id,name,phone, srv2.库名..author有字段:id,name,telphone,adress

要求:

srv1.库名..author扩大记录则srv1.库名..author记录扩大srv1.库名..author的phone字段更新,则srv1.库名..author对应字段telphone更新
--*/

--大约的拍卖步骤--1.在 srv1 上创办连接服务器,以便在 srv1 中操作 srv2,达成同步exec sp_addlinkedserver 'srv2','','SQ英雄联盟EDB','srv2的sql实例名或ip' exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'
go

--2.在 srv1 和 srv2 这两台计算机中,运营msdtc(遍布式事务管理服务),并且安装为机关运行
。小编的Computer--调控面板--管理工科具--服务--右键 Distributed Transaction Coordinator--属性--运转--并将开行项目设置为自动运营
go

--然后创建贰个功课定时调用地点的同台管理存款和储蓄进程就行了

商家管理器

--管理--SQL Server代理--右键作业--新建作业--"常规"项中输入作业名称--"步骤"项--新建--"步骤名"中输入步骤名--"类型"中挑选"Transact-SQL 脚本(TSQL)" --"数据库"选用实践命令的数据库--"命令"中输入要实行的言辞: exec p_process --明确--"调整"项--新建调整--"名称"中输入调治名称--"调整项目"中选拔你的功课实践安插--假诺选用"一再出现" --点"更动"来安装你的小运安排

接下来将SQL Agent服务运行,并安装为自动运行,不然你的课业不会被施行

设置方法:
作者的Computer--调整面板--处理工科具--服务--右键 SQLSETucsonVERAGENT--属性--运维项目--选拔"自动运营"--分明.

--3.贯彻共同处理的方法2,按时同步

--在srv第11中学创建如下的联手管理存储进度
create proc p_process
as

--更新修改过的数码
update b set name=i.name,telphone=i.telphone

from srv2.库名.dbo.author b,author i
where b.id=i.id and
(b.name <> i.name or b.telphone <> i.telphone)

--插入新扩展的多少insert srv2.库名.dbo.author(id,name,telphone)
select id,name,telphone from author i
where not exists(

select * from srv2.库名.dbo.author where id=i.id)

--删除已经去除的数目(纵然供给的话)
delete b

from srv2.库名.dbo.author b
where not exists(
select * from author where id=b.id)

go

  在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、表明:列出数据Curry全部的表名

  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 SEEnclaveVE凯雷德中一贯循环写入数据

  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:一张表有两千0多条记下,表的首先个字段 RecID 是自增进字段, 写三个SQL语句, 搜索表的第31到第肆拾个记录。

  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

  10:获取某三个表的全部字段

  select name from syscolumns where id=object_id('表名')

  select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')

  二种方法的遵守同样

  11:查看与某贰个表相关的视图、存款和储蓄进程、函数

  select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

  12:查看当前数据库中有所存款和储蓄进程

  select name as 存款和储蓄进度名称 from sysobjects where xtype='P'

  13:查询客户创制的享有数据库

  select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')

  或者

  select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

  14:查询某二个表的字段和数据类型

  select column_name,data_type from information_schema.columns

  where table_name = '表名'

  15:不一样服务器数据库之间的多寡操作

  --成立链接服务器

  exec sp_addlinkedserver 'ITSV ', ' ', 'SQ英雄缔盟EDB ', '远程服务器名或ip地址 '

  exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '

  --查询示例

  select * from ITSV.数据库名.dbo.表名

  --导入示例

  select * into 表 from ITSV.数据库名.dbo.表名

  --未来不再使用时去除链接服务器

  exec sp_dropserver 'ITSV ', 'droplogins '

  --连接远程/局域网数据(openrowset/openquery/opendatasource)

  --1、openrowset

  --查询示例

  select * from openrowset( 'SQ英雄结盟EDB ', 'sql服务器名 '; '客商名 '; '密码 ',数据库名.dbo.表名)

  --生费用地球表面

  select * into 表 from openrowset( 'SQ英雄联盟EDB ', 'sql服务器名 '; '客户名 '; '密码 ',数据库名.dbo.表名)

  --把地方表导入远程表

  insert openrowset( 'SQ英雄联盟EDB ', 'sql服务器名 '; '客商名 '; '密码 ',数据库名.dbo.表名)

  select *from 本地表

  --更新本地球表面

  update b

  set b.列A=a.列A

  from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '客户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b

  on a.column1=b.column1

  --openquery用法供给制造多个连接

  --首先创设贰个总是创造链接服务器

  exec sp_addlinkedserver 'ITSV ', ' ', 'SQ英雄联盟EDB ', '远程服务器名或ip地址 '

  --查询

  select *

  FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

  --把本地表导入远程表

  insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

  select * from 本地表

  --更新本地球表面

  update b

  set b.列B=a.列B

  FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a

  inner join 本地表 b on a.列A=b.列A

  --3、opendatasource/openrowset

  SELECT *

  FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta

  --把本地球表面导入远程表

  insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名

  select * from 本地表

  SQL Server基本函数

  SQL Server基本函数

  1.字符串函数 长度与深入分析用

  1,datalength(Char_expr) 再次回到字符串富含字符数,但不带有前面的空格

  2,substring(expression,start,length) 取子串,字符串的下标是从“1”,start为发端地方,length为字符串长度,实际行使中以len(expression)获得其长度

  3,right(char_expr,int_expr) 重返字符串侧边第int_expr个字符,还用left于之相反

  4,isnull( check_expression , replacement_value )如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类

  5,Sp_addtype 自定義數據類型

  例如:EXEC sp_addtype birthday, datetime, 'NULL'

  6,set nocount {on|off}

  使再次回到的结果中不带有关于受 Transact-SQL 语句影响的行数的音信。倘诺存款和储蓄进程中含有的局地讲话并不回去许多实际上的多寡,则该装置由于多量削减了互连网流量,因而可鲜明坚实质量。SET NOCOUNT 设置是在实行或运维时设置,并不是在条分缕析时设置。

  SET NOCOUNT 为 ON 时,不回来计数(表示受 Transact-SQL 语句影响的行数)。

  SET NOCOUNT 为 OFF 时,重返计数

  常识

  在SQL查询中:from后最多可以跟多少张表或视图:256

  在SQL语句中冒出 Order by,查询时,先排序,后取

  在SQL中,三个字段的最大容积是八千,而对于nvarchar(伍仟),由于nvarchar是Unicode码。

  SQLServer3000同步复制手艺实现步骤

  一、 预备工作

  1.发布服务器,订阅服务器都创立二个同名的windows顾客,并设置同一的密码,做为发表快速照相文件夹的有用访问客商

  --管理工科具

  --Computer管理

  --客商和组

  --右键客商

  --新建客商

  --营造二个附属于administrator组的登入windows的客商(SynUser)

  2.在宣布服务器上,新建三个分享目录,做为公布的快速照相文件的存放目录,操作:

  小编的Computer--D: 新建多少个索引,名字为: PUB

  --右键这一个新建的目录

  --属性--共享

  --选用"分享该文件夹"

  --通过"权限"按纽来设置具体的客户权限,保险第一步中创立的顾客(SynUser) 具备对该文件夹的有所权力

  --确定

  3.设置SQL代理(SQLSEOdysseyVERAGENT)服务的开行客户(发表/订阅服务器均做此设置)

  开首--程序--管理工科具--服务

  --右键SQLSERVERAGENT

  --属性--登陆--选择"此账户"

  --输入可能选拔第一步中创建的windows登入客户名(SynUser)

  --"密码"中输入该客户的密码

  4.安装SQL Server身份验证情势,化解连接时的权柄难题(发表/订阅服务器均做此设置)

  集团管理器

  --右键SQL实例--属性

  --安全性--身份验证

  --选择"SQL Server 和 Windows"

  --确定

  5.在布告服务器和订阅服务器上相互注册

  公司管理器

  --右键SQL Server组

  --新建SQL Server注册...

  --下一步--可用的服务器中,输入你要登记的中远间隔服务器名 --增加

  --下一步--连接使用,选拔第三个"SQL Server身份验证"

  --下一步--输入客商名和密码(SynUser)

  --下一步--选用SQL Server组,也得以成立一个新组

  --下一步--完成

  6.对于只可以用IP,不可能用计算机名的,为其注册服务器外号(此步在举行中没用到)

  (在连接端配置,例如,在订阅服务器上配备来讲,服务器名称中输入的是公布服务器的IP)

  早先--程序--Microsoft SQL Server--客户端互联网实用工具

  --别名--添加

  --网络库选用"tcp/ip"--服务器别称输入SQL服务器名

  --连接参数--服务器名称中输入SQL服务器ip地址

  --假如你改改了SQL的端口,撤除选拔"动态调节端口",并输入相应的端口号

  二、 正式配置

  1、配置发表服务器

  展开企管器,在发表服务器(B、C、D)上进行以下步骤:

  (1) 从[工具]下拉菜单的[复制]子菜单中精选[安顿揭橥、订阅服务器和分发]出现布局宣布和分发向导

  (2) [下一步] 选拔分发服务器 能够选拔把揭橥服务器自个儿看成分发服务器也许其余sql的服务器(采用自个儿)

  (3) [下一步] 设置快速照相文件夹

  选择暗许\servernamePub

  (4) [下一步] 自定义配置

  能够挑选:是,让小编设置分发数据库属性启用发表服务器或安装公布设置

  否,使用下列默许设置(推荐)

  (5) [下一步] 设置分发数据库名称和地点 选取私下认可值

  (6) [下一步] 启用公布服务器 选拔作为揭橥的服务器

  (7) [下一步] 采纳需求透露的数据库和发布项目

  (8) [下一步] 选择注册订阅服务器

  (9) [下一步] 达成安排

  2、创立出版物

  发布服务器B、C、D上

  (1)从[工具]菜单的[复制]子菜单中甄选[创建和治本公布]命令

  (2)选用要开创下版物的数据库,然后单击[创立发布]

  (3)在[创办宣布初步]的提示对话框中单击[下一步]系统就能够弹出一个对话框。对话框上的开始和结果是复制的四个门类。大家今天选第叁个也等于默许的快速照相发表(其余四个大家能够去看看帮衬)

  (4)单击[下一步]系统要求钦命能够订阅该公告的数据库服务器类型,

  SQLSETucsonVE奥德赛允许在差异的数据库如 orACLE或ACCESS之间张开数量复制。

  可是在那处大家挑选运转"SQL SERubiconVETiguan 3000"的数据库服务器

  (5)单击[下一步]系统就弹出多少个定义文章的对话框也等于选取要出版的表

  注意: 如若前边选用了事情发布 则再这一步中只可以选取带有主键的表

  (6)选拔发布名称和描述

  (7)自定义发表属性 向导提供的挑选:

  是 小编将自定义数据筛选,启用无名订阅和或别的自定义属性

  否 依据钦定形式创制发布 (提出采用自定义的形式)

  (8)[下一步] 选拔筛选发表的法子

  (9)[下一步] 能够挑选是或不是允许无名氏订阅

  1)要是选择签字订阅,则必要在颁发服务器上增多订阅服务器

  方法: [工具]->[复制]->[安排公布、订阅服务器和分发的性能]->[订阅服务器] 中添加

  不然在订阅服务器上呼吁订阅时会出现的唤醒:改公布不容许无名氏订阅

  假设依然必要佚名订阅则用以下化解办法

  [市肆管理器]->[复制]->[颁发内容]->[属性]->[订阅选项] 选取允许无名氏伏乞订阅

  2)若是选拔无名订阅,则配备订阅服务器时不会现出上述提醒

  (10)[下一步] 设置快速照相 代理程序调节

  (11)[下一步] 完毕安插

  当成功出版物的创始后创建出版物的数据库也就成为了多少个分享数据库

  有数据

  srv1.库名..author有字段:id,name,phone,

  srv2.库名..author有字段:id,name,telphone,adress

  要求:

  srv1.库名..author扩充记录则srv1.库名..author记录扩充

  srv1.库名..author的phone字段更新,则srv1.库名..author对应字段telphone更新

  --*/

  --大约的管理步骤

  --1.在 srv1 上创制连接服务器,以便在 srv1 中操作 srv2,实现协同

  exec sp_addlinkedserver 'srv2','','SQ英雄结盟EDB','srv2的sql实例名或ip'

  exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'

  go

  --2.在 srv1 和 srv2 这两台电脑中,运转msdtc(分布式事务管理服务),并且安装为机关运营

  。作者的微型Computer--控制面板--管理工科具--服务--右键 Distributed Transaction Coordinator--属性--运营--并将运营项目设置为自动运维

  go

  --然后创设一个作业定时调用地点的共同管理存款和储蓄进度就行了

  公司管理器

  --管理

  --SQL Server代理

  --右键作业

  --新建作业

  --"常规"项中输入作业名称

  --"步骤"项

  --新建

  --"步骤名"中输入步骤名

  --"类型"中选择"Transact-SQL 脚本(TSQL)"

  --"数据库"接纳施行命令的数据库

  --"命令"中输入要施行的口舌: exec p_process

  --确定

  --"调度"项

  --新建调解

  --"名称"中输入调解名称

  --"调节项目"中甄选你的功课实施安顿

  --假使采用"一再出现"

  --点"更动"来设置你的时间安排

  然后将SQL Agent服务运维,并设置为机关运转,不然你的学业不会被试行

  设置方法:

  笔者的微型计算机--调整面板--管理工具--服务--右键 SQLSE安德拉VERAGENT--属性--运营项目--选拔"自动运营"--分明.

  --3.贯彻同步管理的方法2,定时同步

  --在srv第11中学创制如下的同步管理存款和储蓄进程

  create proc p_process

  as

  --更新修改过的多寡

  update b set name=i.name,telphone=i.telphone

  from srv2.库名.dbo.author b,author i

  where b.id=i.id and

  (b.name <> i.name or b.telphone <> i.telphone)

  --插入新添的数量

  insert srv2.库名.dbo.author(id,name,telphone)

  select id,name,telphone from author i

  where not exists(

  select * from srv2.库名.dbo.author where id=i.id)

  --删除已经删除的数码(如若要求的话)

  delete b

  from srv2.库名.dbo.author b

  where not exists(

  select * from author where id=b.id)

  go

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:SQL二〇一〇语句大全,语句查询表结构

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。