1 SELECT COUNT(*)與SELECT COUNT(column_or_expression)的區別
SELECT COUNT(*):包括 NULL值;
SELECT COUNT(column_or_expression):不包括NULL值;
如果這兩個都可以滿足一需求時,并且在column上無Index時,建義用COUNT(*), 因為COUNT(*)能用到表上任意一個Index.
2, SQL JOINS(轉)
感覺這篇文章,對初學SQL的人會有一些幫助原文: Visual Representation of SQL Joins
3, SQL SERVER 通過 FOR XML PATH子句,將多行轉成一列(以逗號隔開)(轉)
原方地址: Exploring Database Schemas on SQL Server
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, STUFF (( SELECT ' , ' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME ORDER BY c.COLUMN_NAME FOR XML PATH( '' ) ), 1 , 1 , '' ) AS Columns FROM INFORMATION_SCHEMA.TABLES AS t
備注:Here we look up a list of tables and do a correlated subquery on the
COLUMNS
view to find out all the columns contained in that table.
FOR XML PATH(‘’)
causes all the results to be concatenated into a single value. The
STUFF
function simply removes the leading comma that would otherwise appear at the start of the list.
4, 獲取數據庫所有外鍵的SQL

SELECT OBJECT_SCHEMA_NAME(f.parent_object_id) AS TableNameSchema, -- this OBJECT_NAME (f.parent_object_id) AS TableName, COL_NAME (fc.parent_object_id,fc.parent_column_id) AS ColumnName, OBJECT_SCHEMA_NAME(f.referenced_object_id) AS ReferenceTableNameSchema, -- this OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, COL_NAME (fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName, f.name AS ForeignKey FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f. OBJECT_ID = fc.constraint_object_id INNER JOIN sys.objects AS o ON o. OBJECT_ID = fc.referenced_object_id
?5,SQL在線格式化工具
6,SQLSERVER恢復刪除的數據(轉dudu 實戰 SQL Server 2008 數據庫誤刪除數據的恢復 )
總共三個步驟:
- 備份當前數據庫的事務日志:BACKUP LOG [數據庫名] TO disk= N'備份文件名' WITH NORECOVERY
- 恢復一個誤刪除之前的完全備份:RESTORE DATABASE [數據庫名] FROM DISK = N'完全備份文件名' WITH NORECOVERY,? REPLACE
- 將數據庫恢復至誤刪除之前的時間點:RESTORE LOG [數據庫] FROM? DISK = N'第一步的日志備份文件名' WITH?? STOPAT = N'誤刪除之前的時間點' , RECOVERY
總的來說,在誤刪除數據后,做的第一件是就是備份數據庫操作日志,并記下刪除操作時間;然后恢復完全備份的數據庫(所以在開發時也要養成備份數據庫的習慣);最后恢復操作日志到刪除操作時間之前。
7, SQL UPDATE語句與INNER JOIN 結合
UPDATE T2SET ID = REPLACE (T.ID, ' Start ' , ' TKStart ' ) FROM T2 INNER JOIN T ON T2.ID = T.ID
?8, sql server 查詢表結構(轉自 reglong sql server 查詢表結構 )
-- 1:獲取當前數據庫中的所有用戶表 select Name from sysobjects where xtype = ' u ' and status >= 0 -- 2:獲取某一個表的所有字段 select name from syscolumns where id = object_id ( ' 表名 ' ) -- 3:查看與某一個表相關的視圖、存儲過程、函數 select a. * from sysobjects a, syscomments b where a.id = b.id and b. text like ' %表名% ' -- 4:查看當前數據庫中所有存儲過程 select name as 存儲過程名稱 from sysobjects where xtype = ' P ' -- 5:查詢用戶創建的所有數據庫 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 -- 6:查詢某一個表的字段和數據類型 select column_name,data_type from information_schema.columns where table_name = ' 表名 '
--6.1 EXAMPLE
SELECT TABLE_NAME,column_name,NUMERIC_SCALE,data_type
FROM information_schema.columns
WHERE DATA_TYPE='decimal'
ORDER BY TABLE_NAME
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
