澳门在线威尼斯官方 > 电脑数据库 > 经典SQL语句大全,绝对的经典

原标题:经典SQL语句大全,绝对的经典

浏览次数:136 时间:2019-09-25

一、基础
1、表明:创立数据库

 

CREATE DATABASE database-name
  1. 优秀SQL语句大全(相对的经文)  
  2.   
  3.   
  4. 一、基础   

 
2、表明:删除数据库

 

drop database dbname 

 

3、说明:备份sql server

  1. 1、表明:创立数据库  
  2. CREATE DATABASE database-name   
  3. 2、表达:删除数据库  
  4. drop database dbname  
  5. 3、说明:备份sql server  
  6. --- 创造 备份数据的 device  
  7. USE master  
  8. EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'  
  9. --- 开始 备份  
  10. BACKUP DATABASE pubs TO testBack   
  11. 4、表达:创立新表  
  12. create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)  
  13.   
  14. 依照已有的表成立新表:   
  15. A:create table tab_new like tab_old (使用旧表创制新表)  
  16. B:create table tab_new as select col1,col2… from tab_old definition only  
  17. 5、表明:删除新表  
  18. drop table tabname   
  19. 6、表达:扩展三个列  
  20. Alter table tabname add column col type  
  21. 注:列扩展后将不能够去除。DB第22中学列加上后数据类型也不能够改造,独一能退换的是扩充varchar类型的尺寸。  
  22. 7、表达:增加主键: Alter table tabname add primary key(col)   
  23. 证实:删除主键: Alter table tabname drop primary key(col)   
  24. 8、表明:创设索引:create [unique] index idxname on tabname(col….)   
  25. 剔除索引:drop index idxname  
  26. 注:索引是不行更换的,想改变必需删除重新建。  
  27. 9、表明:创设视图:create view viewname as select statement   
  28. 删去视图:drop view viewname  
  29. 10、表明:多少个轻松的主导的sql语句  
  30. 选择:select * from table1 where 范围  
  31. 插入:insert into table1(field1,field2) values(value1,value2)  
  32. 删除:delete from table1 where 范围  
  33. 更新:update table1 set field1=value1 where 范围  
  34. 查找:select * from table1 where 田野1 like ’%value1%’ ---like的语法很精美,查资料!  
  35. 排序:select * from table1 order by field1,field2 [desc]  
  36. 总数:select count as totalcount from table1  
  37. 求和:select sum(field1) as sumvalue from table1  
  38. 平均:select avg(field1) as avgvalue from table1  
  39. 最大:select max(field1) as maxvalue from table1  
  40. 最小:select min(field1) as minvalue from table1  
  41. 11、表达:多少个高等查询运算词  
  42. A: UNION 运算符   
  43. UNION 运算符通过整合别的七个结实表(举例 TABLE1 和 TABLE2)并消去表中其余重复行而派生出二个结实表。当 ALL 随 UNION一齐利用时(即 UNION ALL),不化解重复行。二种情形下,派生表的每一行不是发源 TABLE1 正是根源 TABLE2。   
  44. B: EXCEPT 运算符   
  45. EXCEPT 运算符通过富含全部在 TABLE1 中但不在 TABLE2 中的行并消除全体重复行而派生出多个结实表。当 ALL 随 EXCEPT 一同行使时 (EXCEPT ALL),不排除重复行。   
  46. C: INTERSECT 运算符  
  47. INTE中华VSECT 运算符通过只蕴涵 TABLE1 和 TABLE2 中都一些行并消除全数重复行而派生出一个结出表。当 ALL 随 INTE福睿斯SECT 一同使用时 (INTEHavalSECT ALL),不免除重复行。   
  48. 注:使用运算词的多少个查询结果行必需是一模二样的。   
  49. 12、表达:使用外连接   
  50. A、left (outer) join:   
  51. 左外连接(左连接):结果集几包括连接表的相配行,也囊括左连接表的富有行。   
  52. 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  
  53. B:right (outer) join:   
  54. 右外连接(右连接):结果集既包含连接表的相配连接行,也囊括右连接表的装有行。   
  55. C:full/cross (outer) join:   
  56. 全外连接:不仅仅囊括符号连接表的相称行,还包括四个三番五次表中的全部记录。  
  57. 12、分组:Group by:  
  58.   一张表,一旦分组 达成后,查询后只好博取组相关的音讯。  
  59. 组相关的音信:(总括消息) count,sum,max,min,avg 分组的正统)  
  60.  在SQLServer中分组时:无法以text,ntext,image类型的字段作为分组依赖  
  61. 在selecte总计函数中的字段,不能够和一般的字段放在一块儿;  
  62.   
  63. 13、对数据库实行操作:  
  64. 分开数据库: sp_detach_db; 附加数据库:sp_attach_db 后接证明,附加必要完整的门径名  
  65. 14.哪些修改数据库的称呼:  
  66. sp_renamedb 'old_name', 'new_name'  
  67.   
  68.   
  69.   
  70. 二、提升  
  71.   
  72. 1、表达:复制表(只复制结构,源表名:a 新表名:b) (Access可用)  
  73. 法一:select * into b from a where 1<>1(仅用于SQlServer)  
  74. 法二:select top 0 * into b from a  
  75. 2、表明:拷贝表(拷贝数据,源表名:a 指标表名:b) (Access可用)  
  76. insert into b(a, b, c) select d,e,f from b;  
  77.   
  78. 3、表明:跨数据库之间表的正片(具体多少接纳绝对路线) (Access可用)  
  79. insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件  
  80. 例子:..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..  
  81.   
  82. 4、说明:子查询(表名1:a 表名2:b)  
  83. 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)  
  84.   
  85. 5、表明:呈现作品、提交人和终极回复时间  
  86. select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b  
  87.   
  88. 6、表明:外接连查询(表名1:a 表名2:b)  
  89. select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c  
  90.   
  91. 7、表达:在线视图查询(表名1:a )  
  92. select * from (SELECT a,b,c FROM a) T where t.a > 1;  
  93.   
  94. 8、说明:between的用法,between限制查询数据范围时包罗了边界值,not between不蕴含  
  95. select * from table1 where time between time1 and time2  
  96. select a,b,c, from table1 where a not between 数值1 and 数值2  
  97.   
  98. 9、表达:in 的采纳格局  
  99. select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)  
  100.   
  101. 10、表达:两张关联表,删除主表中曾在副表中尚无的新闻   
  102. delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )  
  103.   
  104. 11、表明:四表联合检查难题:  
  105. 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 .....  
  106.   
  107. 12、表达:日程安插超前四分钟提示   
  108. SQL: select * from 日程安顿 where datediff('minute',f发轫时间,getdate())>5  
  109.   
  110. 13、表明:一条sql 语句解决数据库分页  
  111. select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段  
  112. 切切实实达成:  
  113. 有关数据库分页:  
  114.   
  115. declare @start int,@end int  
  116.   
  117.  @sql nvarchar(600)  
  118.   
  119.  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)’  
  120.   
  121. exec sp_executesql @sql  
  122.   
  123.   
  124. 注意:在top后不能够直接跟三个变量,所以在事实上行使中唯有那样的扩充特别的拍卖。Rid为三个标志列,假设top后还应该有具体的字段,那样做是老大有实益的。因为如此能够幸免 top的字段假如是逻辑索引的,查询的结果后实在表中的不平等(逻辑索引中的数据有相当大希望和数据表中的不均等,而查询时倘若处在索引则率先查询索引)  
  125.   
  126. 14、说明:前10条记录  
  127. select top 10 * form table1 where 范围  
  128.   
  129. 15、表达:选用在每一组b值一样的数额中对应的a最大的记录的有所音信(类似这样的用法能够用来论坛每月排名的榜单,每月热销产品深入分析,按学科战绩排名,等等.)  
  130. select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)  
  131.   
  132. 16、表达:包含富有在 TableA 中但不在 TableB和TableC 中的行并消除全部重复行而派生出一个结实表  
  133. (select a from tableA ) except (select a from tableB) except (select a from tableC)  
  134.   
  135. 17、表达:随机抽取10条数据  
  136. select top 10 * from tablename order by newid()  
  137.   
  138. 18、表明:随机挑选记录  
  139. select newid()  
  140.   
  141. 19、表明:删除重复记录  
  142. 1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)  
  143. 2),select distinct * into temp from tablename  
  144.  delete from tablename  
  145.  insert into tablename select * from temp  
  146. 评价: 这种操作牵连大气的数码的活动,这种做法不切合大体积但数额操作  
  147. 3),譬如:在四个外界表中程导弹入数据,由于有些原因首先次只导入了一片段,但很难肯定具体地方,那样独有在下三次全体导入,那样也就产生众多再一次的字段,怎么着删除重复字段  
  148.   
  149. alter table tablename  
  150. --加多二个自增列  
  151. add column_b int identity(1,1)  
  152.  delete from tablename where column_b not in(  
  153. select max(column_b) from tablename group by column1,column2,...)  
  154. alter table tablename drop column column_b  
  155.   
  156. 20、表明:列出数据Curry具备的表名  
  157. select name from sysobjects where type='U' // U代表客商  
  158.   
  159. 21、表明:列出表里的装有的列名  
  160. select name from syscolumns where id=object_id('TableName')  
  161.   
  162. 22、表明:列示type、vender、pcs字段,以type字段排列,case能够一本万利地促成多种选用,类似select 中的case。  
  163. 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  
  164. 展现结果:  
  165. type vender pcs  
  166. 电脑 A 1  
  167. 电脑 A 1  
  168. 光盘 B 2  
  169. 光盘 A 2  
  170. 手机 B 3  
  171. 手机 C 3  
  172.   
  173. 23、说明:伊始化表table1  
  174.   
  175. TRUNCATE TABLE table1  
  176.   
  177. 24、说明:选择从10到15的记录  
  178. select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc  
  179.   
  180. 三、技巧  
  181.   
  182. 1、1=1,1=2的利用,在SQL语句组合时用的比较多  
  183.   
  184. “where 1=1” 是表示选拔一切    “where 1=2”全体不选,  
  185. 如:  
  186. if @strWhere !=''   
  187. begin  
  188. set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere   
  189. end  
  190. else   
  191. begin  
  192. set @strSQL = 'select count(*) as Total from [' + @tblName + ']'   
  193. end  
  194.   
  195. 我们得以一向写成  
  196.   
  197. 不当!未找到目录项。  
  198. set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、缩短数据库  
  199. --重新建立索引  
  200. DBCC REINDEX  
  201. DBCC INDEXDEFRAG  
  202. --减少数据和日志  
  203. DBCC SHRINKDB  
  204. DBCC SHRINKFILE  
  205.   
  206. 3、压缩数据库  
  207. dbcc shrinkdatabase(dbname)  
  208.   
  209. 4、转移数据库给新顾客以已存在客商权限  
  210. exec sp_change_users_login 'update_one','newname','oldname'  
  211. go  
  212.   
  213. 5、检查备份集  
  214. RESTORE VERIFYONLY from disk='E:dvbbs.bak'  
  215.   
  216. 6、修复数据库  
  217. ALTER DATABASE [dvbbs] SET SINGLE_USER  
  218. GO  
  219. DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK  
  220. GO  
  221. ALTER DATABASE [dvbbs] SET MULTI_USER  
  222. GO  
  223.   
  224. 7、日志清除  
  225. SET NOCOUNT ON  
  226. DECLARE @LogicalFileName sysname,  
  227.  @MaxMinutes INT,  
  228.  @NewSize INT  
  229.   
  230.   
  231. USE tablename -- 要操作的数据库名  
  232. SELECT  @LogicalFileName = 'tablename_log', -- 日志文件名  
  233. @MaxMinutes = 10, -- Limit on time allowed to wrap log.  
  234.  @NewSize = 1  -- 你想设定的日志文件的深浅(M)  
  235.   
  236. Setup / initialize  
  237. DECLARE @OriginalSize int  
  238. SELECT @OriginalSize = size   
  239.  FROM sysfiles  
  240.  WHERE name = @LogicalFileName  
  241. SELECT 'Original Size of ' + db_name() + ' LOG is ' +   
  242.  CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +   
  243.  CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'  
  244.  FROM sysfiles  
  245.  WHERE name = @LogicalFileName  
  246. CREATE TABLE DummyTrans  
  247.  (DummyColumn char (8000) not null)  
  248.   
  249.   
  250. DECLARE @Counter    INT,  
  251.  @StartTime DATETIME,  
  252.  @TruncLog   VARCHAR(255)  
  253. SELECT @StartTime = GETDATE(),  
  254.  @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'  
  255.   
  256. DBCC SHRINKFILE (@LogicalFileName, @NewSize)  
  257. EXEC (@TruncLog)  
  258. -- Wrap the log if necessary.  
  259. WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired  
  260.  AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)    
  261.  AND (@OriginalSize * 8 /1024) > @NewSize    
  262.  BEGIN -- Outer loop.  
  263. SELECT @Counter = 0  
  264.  WHILE   ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))  
  265.  BEGIN -- update  
  266.  INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans  
  267.  SELECT @Counter = @Counter + 1  
  268.  END  
  269.  EXEC (@TruncLog)    
  270.  END  
  271. SELECT 'Final Size of ' + db_name() + ' LOG is ' +  
  272.  CONVERT(VARCHAR(30),size) + ' 8K pages or ' +   
  273.  CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'  
  274.  FROM sysfiles   
  275.  WHERE name = @LogicalFileName  
  276. DROP TABLE DummyTrans  
  277. SET NOCOUNT OFF  
  278.   
  279. 8、表达:改动有些表  
  280. exec sp_changeobjectowner 'tablename','dbo'  
  281.   
  282. 9、存款和储蓄更动全体表  
  283.   
  284. CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch  
  285. @OldOwner as NVARCHAR(128),  
  286. @NewOwner as NVARCHAR(128)  
  287. AS  
  288.   
  289. DECLARE @Name    as NVARCHAR(128)  
  290. DECLARE @Owner   as NVARCHAR(128)  
  291. DECLARE @OwnerName   as NVARCHAR(128)  
  292.   
  293. DECLARE curObject CURSOR FOR   
  294. select 'Name'    = name,  
  295.    'Owner'    = user_name(uid)  
  296. from sysobjects  
  297. where user_name(uid)=@OldOwner  
  298. order by name  
  299.   
  300. OPEN   curObject  
  301. FETCH NEXT FROM curObject INTO @Name, @Owner  
  302. WHILE(@@FETCH_STATUS=0)  
  303. BEGIN       
  304. if @Owner=@OldOwner   
  305. begin  
  306.    set @OwnerName = @OldOwner + '.' + rtrim(@Name)  
  307.    exec sp_changeobjectowner @OwnerName, @NewOwner  
  308. end  
  309. -- select @name,@NewOwner,@OldOwner  
  310.   
  311. FETCH NEXT FROM curObject INTO @Name, @Owner  
  312. END  
  313.   
  314. close curObject  
  315. deallocate curObject  
  316. GO  
  317.   
  318.   
  319. 10、SQL SELacrosseVE传祺中一贯循环写入数据  
  320. declare @i int  
  321. set @i=1  
  322. while @i<30  
  323. begin  
  324.   insert into test (userid) values(@i)  
  325.   set @i=@i+1  
  326. end  
  327. 案例:  
  328. 如同下表,须求就裱中兼有沒有及格的成績,在历次增長0.1的基礎上,使他們剛好及格:  
  329.   
  330. Name score  
  331.   
  332. Zhangshan 80  
  333.   
  334. Lishi  59  
  335.   
  336. Wangwu  50  
  337.   
  338. Songquan 69  
  339.   
  340. while((select min(score) from tb_table)<60)  
  341.   
  342. begin  
  343.   
  344. update tb_table set score =score*1.01  
  345.   
  346. where score<60  
  347.   
  348. if (select min(score) from tb_table)>60  
  349.   
  350.  break  
  351.   
  352.  else  
  353.   
  354.  continue  
  355.   
  356. end  
  357.   
  358.   
  359.   
  360. 数量开垦-杰出  
  361.   
  362.   
  363. 1.按姓氏笔画排序:  
  364. Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多  
  365.   
  366. 2.数据库加密:  
  367. select encrypt('原始密码')  
  368. select pwdencrypt('原始密码')  
  369. select pwdcompare('原始密码','加密后密码') = 1--同样;不然不一样等 encrypt('原始密码')  
  370. select pwdencrypt('原始密码')  
  371. select pwdcompare('原始密码','加密后密码') = 1--一样;不然分化  
  372.   
  373. 3.取回表中字段:  
  374. declare @list varchar(1000),  
  375. @sql nvarchar(1000)   
  376. select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'  
  377. set @sql='select '+right(@list,len(@list)-1)+' from 表A'   
  378. exec (@sql)  
  379.   
  380. 4.翻看硬盘分区:  
  381. EXEC master..xp_fixeddrives  
  382.   
  383. 5.相比较A,B表是还是不是等于:  
  384. if (select checksum_agg(binary_checksum(*)) from A)  
  385.      =  
  386.     (select checksum_agg(binary_checksum(*)) from B)  
  387. print '相等'  
  388. else  
  389. print '不相等'  
  390.   
  391. 6.杀掉全体的平地风波探察器进度:  
  392. DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses  
  393. WHERE program_name IN('SQL profiler',N'SQL 事件探查器')  
  394. EXEC sp_msforeach_worker '?'  
  395.   
  396. 7.笔录找寻:  
  397. 开头到N条记录  
  398. Select Top N * From 表  
  399. -------------------------------  
  400. N到M条记录(要有主索引ID)  
  401. Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID   Desc  
  402. ----------------------------------  
  403. N到最终记录  
  404. Select Top N * From 表 Order by ID Desc  
  405. 案例  
  406. 举个例子1:一张表有20000多条记下,表的率先个字段 RecID 是自增进字段, 写一个SQL语句, 寻找表的第31到第三十四个记录。  
  407.   
  408. select top 10 recid from A where recid not in(select top 30 recid from A)  
  409.   
  410. 浅析:假设那样写会产生一些难点,若是recid在表中留存逻辑索引。  
  411.   
  412. select top 10 recid from A where……是从索引中追寻,而背后的select top 30 recid from A则在数据表中查找,那样由于索引中的顺序有极大希望和多少表中的不平等,那样就导致查询到的不是理之当然的欲获得的数码。  
  413.   
  414. 消除方案  
  415.   
  416. 1, 用order by select top 30 recid from A order by ricid 假设该字段不是自增加,就能够现出难题  
  417.   
  418. 2, 在丰硕子查询中也加条件:select top 30 recid from A where recid>-1  
  419.   
  420. 例2:查询表中的最终以条记下,并不知道那么些表共有多少多少,以及表结构。  
  421. set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from  T)'  
  422.   
  423. print @s exec sp_executesql @s  
  424.   
  425. 9:获取当前数据库中的全数客商表  
  426. select Name from sysobjects where xtype='u' and status>=0  
  427.   
  428. 10:获取某几个表的兼具字段  
  429. select name from syscolumns where id=object_id('表名')  
  430.   
  431. select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')  
  432.   
  433. 二种办法的意义一样  
  434.   
  435. 11:查看与某叁个表相关的视图、存款和储蓄进程、函数  
  436. select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'  
  437.   
  438. 12:查看当前数据库中保有存款和储蓄进度  
  439. select name as 存款和储蓄进度名称 from sysobjects where xtype='P'  
  440.   
  441. 13:查询客商创制的具有数据库  
  442. select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')  
  443. 或者  
  444. select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01  
  445.   
  446. 14:查询某三个表的字段和数据类型  
  447. select column_name,data_type from information_schema.columns  
  448. where table_name = '表名'  
  449.   
  450. 15:不一样服务器数据库之间的多少操作  
  451.   
  452. --创立链接服务器  
  453.   
  454. exec sp_addlinkedserver 'ITSV ', ' ', 'SQ英雄联盟EDB ', '远程服务器名或ip地址 '  
  455.   
  456. exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '  
  457.   
  458. --查询示例  
  459.   
  460. select * from ITSV.数据库名.dbo.表名  
  461.   
  462. --导入示例  
  463.   
  464. select * into 表 from ITSV.数据库名.dbo.表名  
  465.   
  466. --今后不再利用时去除链接服务器  
  467.   
  468. exec sp_dropserver 'ITSV ', 'droplogins '  
  469.   
  470.   
  471.   
  472. --连接远程/局域网数据(openrowset/openquery/opendatasource)  
  473.   
  474. --1、openrowset  
  475.   
  476. --查询示例  
  477.   
  478. select * from openrowset( 'SQ英雄结盟EDB ', 'sql服务器名 '; '客商名 '; '密码 ',数据库名.dbo.表名)  
  479.   
  480. --生开支地球表面  
  481.   
  482. select * into 表 from openrowset( 'SQ英雄联盟EDB ', 'sql服务器名 '; '客商名 '; '密码 ',数据库名.dbo.表名)  
  483.   
  484.   
  485.   
  486. --把本地表导入远程表  
  487.   
  488. insert openrowset( 'SQ英雄结盟EDB ', 'sql服务器名 '; '顾客名 '; '密码 ',数据库名.dbo.表名)  
  489.   
  490. select *from 本地表  
  491.   
  492. --更新本地球表面  
  493.   
  494. update b  
  495.   
  496. set b.列A=a.列A  
  497.   
  498.  from openrowset( 'SQ英雄结盟EDB ', 'sql服务器名 '; '顾客名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b  
  499.   
  500. on a.column1=b.column1  
  501.   
  502. --openquery用法须求创建三个总是  
  503.   
  504. --首先创立一个一而再创立链接服务器  
  505.   
  506. exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '  
  507.   
  508. --查询  
  509.   
  510. select *  
  511.   
  512. FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')  
  513.   
  514. --把地面表导入远程表  
  515.   
  516. insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')  
  517.   
  518. select * from 本地表  
  519.   
  520. --更新本地表  
  521.   
  522. update b  
  523.   
  524. set b.列B=a.列B  
  525.   
  526. FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a   
  527.   
  528. inner join 本地表 b on a.列A=b.列A  
  529.   
  530.   
  531.   
  532. --3、opendatasource/openrowset  
  533.   
  534. SELECT *  
  535.   
  536. FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta  
  537.   
  538. --把地面表导入远程表  
  539.   
  540. insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名  
  541.   
  542. select * from 本地表   
  543.   
  544. SQL Server基本函数  
  545.   
  546. SQL Server基本函数  
  547.   
  548. 1.字符串函数 长度与剖判用  
  549.   
  550. 1,datalength(Char_expr) 再次来到字符串包罗字符数,但不富含前面包车型地铁空格  
  551. 2,substring(expression,start,length) 取子串,字符串的下标是从“1”,start为发端地点,length为字符串长度,实际利用中以len(expression)获得其长度  
  552. 3,right(char_expr,int_expr) 再次回到字符串右侧第int_expr个字符,还用left于之相反  
  553. 4,isnull( check_expression , replacement_value )如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类  
  554.   
  555. 5,Sp_addtype 自定義數據類型  
  556. 例如:EXEC sp_addtype birthday, datetime, 'NULL'  
  557. 6,set nocount {on|off}  
  558. 使重回的结果中不富含关于受 Transact-SQL 语句影响的行数的新闻。假诺存款和储蓄进程中带有的有个别言语并不回来多数实际的多寡,则该装置由于大气压缩了网络流量,由此可眼看巩固品质。SET NOCOUNT 设置是在执行或运转时设置,而不是在分析时设置。  
  559. SET NOCOUNT 为 ON 时,不回来计数(表示受 Transact-SQL 语句影响的行数)。  
  560.   
  561. SET NOCOUNT 为 OFF 时,重回计数  
  562. 常识  
  563.    
  564. 在SQL查询中:from后最多能够跟多少张表或视图:256  
  565. 在SQL语句中出现 Order by,查询时,先排序,后取  
  566. 在SQL中,贰个字段的最大容积是7000,而对于nvarchar(四千),由于nvarchar是Unicode码。   
  567.    
  568. SQLServer三千同步复制技艺完成步骤  
  569. 一、 预备专门的学业  
  570. 1.宣布服务器,订阅服务器都创建三个同名的windows客商,并设置同样的密码,做为公布快速照相文件夹的得力访问客商  
  571. --管理工科具  
  572. --Computer管理  
  573. --顾客和组  
  574. --右键客商  
  575. --新建顾客  
  576. --创建贰个直属于administrator组的登入windows的客商(SynUser)  
  577. 2.在公布服务器上,新建多少个分享目录,做为公布的快速照相文件的贮存目录,操作:  
  578. 小编的微机--D: 新建二个索引,名叫: PUB  
  579. --右键那几个新建的目录  
  580. --属性--共享  
  581. --选拔"共享该文件夹"  
  582. --通过"权限"按纽来设置具体的客户权限,有限支撑第一步中开创的顾客(SynUser) 具备对该公文夹的有着权限  
  583.    
  584. --确定  
  585. 3.安装SQL代理(SQLSE索罗德VERAGENT)服务的起步客户(公布/订阅服务器均做此设置)  
  586. 起首--程序--管理工科具--服务  
  587. --右键SQLSERVERAGENT  
  588. --属性--登陆--选择"此账户"  
  589. --输入或许选拔第一步中开创的windows登入顾客名(SynUser)  
  590. --"密码"中输入该客户的密码  
  591. 4.装置SQL Server身份验证格局,化解连接时的权能难题(发布/订阅服务器均做此设置)  
  592. 信用合作社处理器  
  593. --右键SQL实例--属性  
  594. --安全性--身份验证  
  595. --选择"SQL Server 和 Windows"  
  596. --确定  
  597. 5.在发表服务器和订阅服务器上相互注册  
  598. 商场管理器  
  599. --右键SQL Server组  
  600. --新建SQL Server注册...  
  601. --下一步--可用的服务器中,输入你要注册的长距离服务器名 --增添  
  602. --下一步--连接使用,接纳第一个"SQL Server身份验证"  
  603. --下一步--输入客商名和密码(SynUser)  
  604. --下一步--选择SQL Server组,也得以创建一个新组  
  605. --下一步--完成  
  606. 6.对于只可以用IP,无法用Computer名的,为其注册服务器外号(此步在实践中没用到)  
  607.  (在连接端配置,举个例子,在订阅服务器上配备来说,服务器名称中输入的是发布服务器的IP)  
  608. 开班--程序--Microsoft SQL Server--客商端互连网实用工具  
  609. --别名--添加  
  610. --网络库选拔"tcp/ip"--服务器小名输入SQL服务器名  
  611. --连接参数--服务器名称中输入SQL服务器ip地址  
  612. --若是你改改了SQL的端口,撤消选取"动态调整端口",并输入相应的端口号  
  613. 二、 正式配置  
  614. 1、配置发表服务器  
  615. 开采企管器,在发布服务器(B、C、D)上执行以下步骤:  
  616. (1) 从[工具]下拉菜单的[复制]子菜单中采取[铺排发表、订阅服务器和分发]并宣布局公布和分发向导   
  617. (2) [下一步] 选用分发服务器 能够挑选把公布服务器自个儿当作分发服务器只怕别的sql的服务器(选拔本身)  
  618. (3) [下一步] 设置快速照相文件夹  
  619. 使用私下认可\servernamePub  
  620. (4) [下一步] 自定义配置   
  621. 能够选取:是,让本身设置分发数据库属性启用揭橥服务器或安装公布设置  
  622. 否,使用下列暗许设置(推荐)  
  623. (5) [下一步] 设置分发数据库名称和职务 选择默许值  
  624. (6) [下一步] 启用发布服务器 采用作为公布的服务器  
  625. (7) [下一步] 选择须求公布的数据库和宣布项目  
  626. (8) [下一步] 选用注册订阅服务器  
  627. (9) [下一步] 实现都部队署  
  628. 2、缔造出版物  
  629. 颁发服务器B、C、D上  
  630. (1)从[工具]菜单的[复制]子菜单中选拔[开创和保管公布]命令  
  631. (2)选取要开创下版物的数据库,然后单击[创制宣布]  
  632. (3)在[成立发布起头]的升迁对话框中单击[下一步]系统就能够弹出贰个对话框。对话框上的剧情是复制的多少个种类。大家明天选第四个也正是暗中认可的快速照相揭橥(别的多少个大家能够去走访扶助)  
  633. (4)单击[下一步]系统须求钦点可以订阅该布告的数据库服务器类型,  
  634. SQLSE奥迪Q5VE牧马人允许在差别的数据库如 orACLE或ACCESS之间打开数据复制。  
  635. 唯独在这里大家选拔运转"SQL SEPRADOVELX570 三千"的数据库服务器  
  636. (5)单击[下一步]系统就弹出七个概念小说的对话框也正是选择要出版的表  
  637. 留心: 倘使日前选拔了事情发表 则再这一步中只可以选用带有主键的表  
  638. (6)采纳公布名称和描述  
  639. (7)自定义宣布属性 向导提供的取舍:  
  640. 是 笔者将自定义数据筛选,启用无名订阅和或任何自定义属性  
  641. 否 依据钦点情势开创发布 (提议接纳自定义的办法)  
  642. (8)[下一步] 选用筛选公布的秘籍   
  643. (9)[下一步] 能够选取是还是不是允许佚名订阅  
  644. 1)如若采纳具名订阅,则必要在发表服务器上增多订阅服务器  
  645. 方法: [工具]->[复制]->[安插发表、订阅服务器和分发的质量]->[订阅服务器] 中添加  
  646. 否则在订阅服务器上呼吁订阅时会出现的升迁:改发表区别意佚名订阅  
  647. 只要依旧供给无名订阅则用以下化解办法   
  648. [集团管理器]->[复制]->[揭露内容]->[属性]->[订阅选项] 选用允许无名氏须求订阅  
  649. 2)假诺选取匿名订阅,则配备订阅服务器时不会现出上述提醒  
  650. (10)[下一步] 设置快速照相 代理程序调整  
  651. (11)[下一步] 完毕布署  
  652. 当成功出版物的创办后创建出版物的数据库也就造成了二个分享数据库  
  653. 有数据   
  654. srv1.库名..author有字段:id,name,phone,   
  655. srv2.库名..author有字段:id,name,telphone,adress   
  656.    
  657. 要求:   
  658. srv1.库名..author扩充记录则srv1.库名..author记录扩大   
  659. srv1.库名..author的phone字段更新,则srv1.库名..author对应字段telphone更新   
  660. --*/   
  661.    
  662. --大致的管理步骤   
  663. --1.在 srv1 上创立连接服务器,以便在 srv1 中操作 srv2,完成同步   
  664. exec sp_addlinkedserver 'srv2','','SQ英雄联盟EDB','srv2的sql实例名或ip'   
  665. exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'   
  666. go  
  667. --2.在 srv1 和 srv2 这两台计算机中,运维 msdtc(布满式事务管理服务),而且安装为自动运转  
  668. 。笔者的微型Computer--调节面板--管理工科具--服务--右键 Distributed Transaction Coordinator--属性--运营--并将起动项目设置为全自动运营   
  669. go   
  670.    
  671.    
  672. --然后创建一个作业按时调用地点的一齐管理存储进程就行了   
  673.    
  674. 供销合作社管理器   
  675. --管理   
  676. --SQL Server代理   
  677. --右键作业   
  678. --新建作业   
  679. --"常规"项中输入作业名称   
  680. --"步骤"项   
  681. --新建   
  682. --"步骤名"中输入步骤名   
  683. --"类型"中选择"Transact-SQL 脚本(TSQL)"   
  684. --"数据库"选用推行命令的数据库   
  685. --"命令"中输入要施行的说话: exec p_process   
  686. --确定   
  687. --"调度"项   
  688. --新建调节   
  689. --"名称"中输入调整名称   
  690. --"调整项目"中接纳你的功课推行安顿   
  691. --如若选用"一再出现"   
  692. --点"改造"来设置你的年华安顿   
  693.    
  694.    
  695. 然后将SQL Agent服务运维,并设置为自行运维,不然你的学业不会被试行   
  696.    
  697. 安装方法:   
  698. 自个儿的微管理器--调整面板--管理工科具--服务--右键 SQLSE奥迪Q3VERAGENT--属性--运行项目--采用"自动运营"--确定.   
  699.    
  700.    
  701. --3.完成同步管理的方法2,按时同步   
  702.    
  703. 威尼斯澳门在线 ,--在srv第11中学创立如下的同步管理存款和储蓄进程   
  704. create proc p_process   
  705. as   
  706. --更新修改过的多寡   
  707. update b set name=i.name,telphone=i.telphone   
  708. from srv2.库名.dbo.author b,author i   
  709. where b.id=i.id and  
  710. (b.name <> i.name or b.telphone <> i.telphone)   
  711.    
  712. --插入新添的数量   
  713. insert srv2.库名.dbo.author(id,name,telphone)   
  714. select id,name,telphone from author i   
  715. where not exists(   
  716. select * from srv2.库名.dbo.author where id=i.id)   
  717.    
  718. --删除已经删除的数码(就算供给的话)   
  719. delete b   
  720. from srv2.库名.dbo.author b   
  721. where not exists(   
  722. select * from author where id=b.id)  
  723. go  
--- 创建 备份数据的 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],..) 

基于已部分表创设新表:
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语句

威尼斯澳门在线 1)

选择: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 

威尼斯澳门在线 2)

 

11、表达:多少个高档查询运算词
A: UNION 运算符
UNION 运算符通过结合别的三个结实表(比如 TABLE1 和 TABLE2)并消去表中别的重复行而派生出贰个结实表。当 ALL 随 UNION 一齐利用时(即 UNION ALL),不化解重复行。二种情形下,派生表的每一行不是缘于 TABLE1 就是源于 TABLE2。
B: EXCEPT 运算符
EXCEPT 运算符通过富含具备在 TABLE1 中但不在 TABLE2中的行并化解全数重复行而派生出一个结实表。当 ALL 随 EXCEPT 一同行使时 (EXCEPT ALL),不排除重复行。
C: INTERSECT 运算符
INTE奔驰M级SECT 运算符通过只囊括 TABLE1 和 TABLE第22中学都一些行并解决全部重复行而派生出贰个结出表。当 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
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

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:经典SQL语句大全,绝对的经典

关键词:

上一篇:没有了

下一篇:没有了