經典SQL語句大全(絕對的經典) 一、基礎 經典SQL語句大全(絕對的經典) 一、基礎 經典SQL語句大全(絕對的經典) 一、基礎 經典SQL語句大全(絕對的經典) 一、基礎 1、說明:創建資料庫 CREATE DATABASE database-name 2、說明:刪除資料庫 drop datab ...
- 經典SQL語句大全(絕對的經典)
- 一、基礎
- 1、說明:創建資料庫
- CREATE DATABASE database-name
- 2、說明:刪除資料庫
- drop database dbname
- 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
- 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)
- 刪除: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
- 11、說明:幾個高級查詢運算詞
- A: UNION 運算符
- UNION 運算符通過組合其他兩個結果表(例如 TABLE1 和 TABLE2)並消去表中任何重覆行而派生出一個結果表。當 ALL 隨 UNION一起使用時(即 UNION ALL),不消除重覆行。兩種情況下,派生表的每一行不是來自 TABLE1 就是來自 TABLE2。
- B: EXCEPT 運算符
- EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重覆行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL),不消除重覆行。
- C: INTERSECT 運算符
- INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行並消除所有重覆行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL),不消除重覆行。
- 註:使用運算詞的幾個查詢結果行必須是一致的。
- 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
- 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、說明:列出資料庫里所有的表名
- 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 SERVER中直接迴圈寫入數據
- 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:一張表有一萬多條記錄,表的第一個欄位 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……是從索引中查找,而後面的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,<