home > DB > slq >

SQL語句大全中的sql語句備忘錄—sql日常使用指北

Author:[email protected] Date:

sql語句大全,備忘查詢。對于經常使用的一些sql語句分類整理,?按CRUD的的規律,對行列、數據、表 基本操作語句 范例 整理歸集。平時開發備忘速查

CRUD:增查改刪,即,create/read/update/delate 主要被用在描述軟件系統中數據庫或者持久層的基本操作功能

而一般所說的數據庫的增刪改查,即insert/delate/update/select是sql語言中的DML(data manipulation))語言

列操作

  • 查看表結構 mysql> desc myTable;  || describe myTable

  • 添加列 mysql> alter table test add(tage int(3));
  • 刪除列 mysql> alter table test drop column tage;


  • 修改原表結構 mysql> alter table test modify tage int(5) not null;
  • 修改列的默認值mysql> alter table test alter tage set default '0';
  • 去掉列的默認值 mysql> alter table test alter tage drop default; 

設置主鍵

ALTER TABLE `table_name` ADD PRIMARY KEY(`field_name`);
ALTER TABLE `table` CHANGE `field` `field_new_name` INT(4) NOT NULL DEFAULT '0' FIRST;
然后這里有個坑, id 必須反引號,不然報錯。
alter table fuck change id `id` int(4) not null defualt '0' frist; 

數據增刪改查

  • 插入數據mysql> insert into test(tid,tname,tdate) value(1,'yangjuqi','2008-03-21');
  • 刪除數據mysql> delete from test where tid='2';

  • 修改數據 mysql> update test set tname='張三' where tid='2';
  • 查詢數據 mysql> select * from test;

    • 模糊查詢 mysql> select * from test where tname like '%楊%';
    • 分頁查詢(limit 起始行,取多少行)mysql> select * from testbak limit 2,1;
    • 將查詢出的數據寫入文件 mysql> select * from user into outfile "/etc/test.txt" fields terminated by ",";

    • 排序: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

幾個高級查詢運算詞

  • UNION 運算符 
    UNION 運算符通過組合其他兩個結果表(例如 TABLE1 和 TABLE2)并消去表中任何重復行而派生出一個結果表。當 ALL 隨 UNION 一起使用時(即 UNION ALL),不消除重復行。兩種情況下,派生表的每一行不是來自 TABLE1 就是來自 TABLE2。 
  • EXCEPT 運算符 
    EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL),不消除重復行。 
  •  INTERSECT 運算符
    INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL),不消除重復行。 
注:使用運算詞的幾個查詢結果行必須是一致的。 

使用外連接 

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
right (outer) join: 
右外連接(右連接):結果集既包括連接表的匹配連接行,也包括右連接表的所有行。 
full/cross (outer) join: 

全外連接:不僅包括符號連接表的匹配行,還包括兩個連接表中的所有記錄。

分組:Group by

一張表,一旦分組 完成后,查詢后只能得到組相關的信息。

SELECT `username`,`age`,`tel`,`sex` FROM testusers GROUP BY `sex`;

GROUP_CONCAT()函數

根據sex 字段進行分組并查看username字段和age字段的詳細信息。(這下對基本用法就很好理解了。因為他默認只顯示了組中一條記錄,如果想看組內所以的用戶信息,就需要用到GROUP_CONCAT()函數。接著往下↓)

SELECT GROUP_CONCAT(username),GROUP_CONCAT(age),sex FROM testusers GROUP BY sex

統計記錄總數 COUNT()函數:

SELECT COUNT(*) AS totalUsers, GROUP_CONCAT(username) AS userDetail ,GROUP_CONCAT(age) AS userAge,sex FROM testusers GROUP BY sex;

分組結果進行二次篩選 HAVING 子句

相當于WHERE是一次篩選,HAVING是在二/再次篩選.

SELECT COUNT(*) AS totalUsers,GROUP_CONCAT(age) AS userAge,GROUP_CONCAT(username) AS userDetail,sex FROM testusers GROUP BY sex HAVING totalUsers>=5;

表操作 

  • 查看表 mysql > show tables;

  • 刪除表 mysql > drop table test;
  • 重命名表 mysql > alter table test rename testbak;

數據庫操作

  • 刷新數據庫 mysql> flush privileges;

  • 顯示數據庫版本 mysql> select version();
  • 顯示當前時間 mysql> select current_date;
  • 查看數據庫狀態 mysql> status;
  • 查看所有編碼 mysql> show variables like 'character_set_%';
  • 導入sql文件命令 mysql> source /etc/MySQL.sql;   mysqlimport -u root -p dbname filename.sql 
  • 備份數據庫: mysqldump -u root -h host -p dbname > backdb.sql
    • 備份數據庫中的某個表 mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

    • 備份多個數據庫 mysqldump -u root -h host -p --databases dbname1, dbname2 > backdb.sql

    • 備份系統中所有數據庫 mysqldump -u root -h host -p --all-databases > backdb.sql

sql操作范例

一、基礎
1、說明:創建數據庫
CREATE DATABASE database-name 
1:建立一個名為xhkdb的數據庫
   mysql> create database xhkdb;
例2:創建數據庫并分配用戶
①CREATE DATABASE 數據庫名;
②GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 數據庫名.* TO 數據庫名@localhost IDENTIFIED BY '密碼';
③SET PASSWORD FOR '數據庫名'@'localhost' = OLD_PASSWORD('密碼');
依次執行3個命令完成數據庫創建。 
2、說明:刪除數據庫
drop database dbname
例如:刪除名為 xhkdb的數據庫
mysql> drop database xhkdb; 
例子2:刪除一個不確定存在的數據庫
   mysql> drop database drop_database;
   ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
      //發生錯誤,不能刪除'drop_database'數據庫,該數據庫不存在。
   mysql> drop database if exists drop_database;
   Query OK, 0 rows affected, 1 warning (0.00 sec)//產生一個警告說明此數據庫不存在
   mysql> create database drop_database;
   Query OK, 1 row affected (0.00 sec)
   mysql> drop database if exists drop_database;//if exists 判斷數據庫是否存在,不存在也不產生錯誤
   Query OK, 0 rows affected (0.00 sec) 
3、說明:備份sql server
--- 創建 備份數據的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 開始 備份
BACKUP DATABASE pubs TO testBack 
4、說明:創建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 
根據已有的表創建新表: 
A:create table tab_new like tab_old (使用舊表創建新表)
B:create table tab_new as select col1,col2… from tab_old definition only
5、說明:刪除新表
drop table tabname 
例如:刪除表名為 MyClass 的表
   mysql> drop table MyClass;
6、說明:增加一個列
Alter table tabname add column col type
注:列增加后將不能刪除。DB2中列加上后數據類型也不能改變,唯一能改變的是增加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)
例如:往表 MyClass中插入二條記錄, 這二條記錄表示:編號為1的名為Tom的成績為96.45, 編號為2 的名為Joan 的成績為82.99, 編號為3 的名為Wang 的成績為96.5。
   mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);
刪除:delete from table1 where 范圍
  6:顯示表的結構: 
  mysql> DESDRIBE MYTBBLE; 
    7:往表中插手記錄 
  mysql> insert into MYTBBLE values ("hyq","M");

 

INSERT INTO users (firstLoadTime,loadTimeDay) VALUES ('".date('Y-m-d H:i:s',time())."','".date('Y-m-d H:i:s',time())."',0,2,'");
  8:用文本方式將數據裝入數據庫表中(例如D:/mysql.txt) 
  mysql> LOBD DBTB LODBL INFILE "D:/mysql.txt" INTO TBBLE MYTBBLE; 
  9:導入.sql文件號令(例如D:/mysql.sql) 
  mysql>use database; 
  mysql>source d:/mysql.sql;  
更新:update table1 set field1=value1 where 范圍
mysql>update MYTABLE set sex=”f” where name="hyq"
查找:select * from table1 where field1 like "%value1%" ---like的語法很精妙,查資料!

slq操作技能提升

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 &lsquo;具體數據庫&rsquo; 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 (&lsquo;值1&rsquo;,&rsquo;值2&rsquo;,&rsquo;值4&rsquo;,&rsquo;值6&rsquo;)

 

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=&rsquo;select top&rsquo;+str(@[email protected]+1)+&rsquo;+from T where rid not in(select top&rsquo;+str(@str-1)+&rsquo;Rid from T where Rid>-1)&rsquo;
  exec sp_executesql @sql

 

注意:在top后不能直接跟一個變量,所以在實際應用中只有這樣的進行特殊的處理。Rid為一個標識列,如果top后還有具體的字段,這樣做是非常有好處的。因為這樣可以避免 top的字段如果是邏輯索引的,查詢的結果后實際表中的不一致(邏輯索引中的數據有可能和數據表中的不一致,而查詢時如果處在索引則首先查詢索引)
14、說明:前10條記錄
select top 10 * form table1 where 范圍

 

15、說明:選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

 

16、說明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復行而派生出一個結果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)

 

17、說明:隨機取出10條數據
select top 10 * from tablename order by newid()

 

18、說明:隨機選擇記錄
select newid()

 

19、說明:刪除重復記錄
1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
2),select distinct * into temp from tablename
  delete from tablename
  insert into tablename select * from temp
評價: 這種操作牽連大量的數據的移動,這種做法不適合大容量但數據操作
3),例如:在一個外部表中導入數據,由于某些原因第一次只導入了一部分,但很難判斷具體位置,這樣只有在下一次全部導入,這樣也就產生好多重復的字段,怎樣刪除重復字段

 

alter table tablename
--添加一個自增列
add  column_b int identity(1,1)
 delete from tablename where column_b not in(
select max(column_b)  from tablename group by column1,column2,...)
alter table tablename drop column column_b

 

20、說明:列出數據庫里所有的表名
select name from sysobjects where type='U' // U代表用戶

 

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

 

22、說明:列示type、vender、pcs字段,以type字段排列,case可以方便地實現多重選擇,類似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
顯示結果:
type vender pcs
電腦 A 1
電腦 A 1
光盤 B 2
光盤 A 2
手機 B 3
手機 C 3

 

23、說明:初始化表table1

 

TRUNCATE TABLE table1

 

24、說明:選擇從10到15的記錄
select top 5 * from (select top 15 * from table order by id asc) table_別名 order by id desc

 

sql操作技巧 

1、1=1,1=2的使用,在SQL語句組合時用的較多

 

&ldquo;where 1=1&rdquo; 是表示選擇全部    &ldquo;where 1=2&rdquo;全部不選,
如:
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)[email protected]
order by name

 

OPEN   curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN     
if @[email protected] 
begin
   set @OwnerName = @OldOwner + '.' + rtrim(@Name)
   exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner

 

FETCH NEXT FROM curObject INTO @Name, @Owner
END

 

close curObject
deallocate curObject
GO

  

10、SQL SERVER中直接循環寫入數據
declare @i int
set @i=1
while @i<30
begin
    insert into test (userid) values(@i)
    set @[email protected]+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

  

數據開發-經典sql語句

  

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 @[email protected]+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A' 
exec (@sql)

 

4.查看硬盤分區:
EXEC master..xp_fixeddrives

 

5.比較A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
     =
    (select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'

 

6.殺掉所有的事件探察器進程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'

 

7.記錄搜索:
開頭到N條記錄
Select Top N * From 表
-------------------------------
N到M條記錄(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID   Desc
----------------------------------
N到結尾記錄
Select Top N * From 表 Order by ID Desc
案例
例如1:一張表有一萬多條記錄,表的第一個字段 RecID 是自增長字段, 寫一個SQL語句, 找出表的第31到第40個記錄。
 select top 10 recid from A where recid not  in(select top 30 recid from A)
分析:如果這樣寫會產生某些問題,如果recid在表中存在邏輯索引。
    select top 10 recid from A where&hellip;&hellip;是從索引中查找,而后面的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 ', ' ', 'SQLOLEDB ', '遠程服務器名或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( 'SQLOLEDB ', '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( 'SQLOLEDB ', 'sql服務器名 '; '用戶名 '; '密碼 ',數據庫名.dbo.表名)as a inner join 本地表 b

 

on a.column1=b.column1

 

--openquery用法需要創建一個連接

 

--首先創建一個連接創建鏈接服務器

 

exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '遠程服務器名或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) 取子串,字符串的下標是從&ldquo;1&rdquo;,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 語句影響的行數)。

轉載本站文章《SQL語句大全中的sql語句備忘錄—sql日常使用指北》,
請注明出處:http://www.qsexmk.tw/html/DB/sql/2015_0821_242.html