1 SELECT COUNT(*)與SELECT COUNT(column_or_expression)的區(qū)別
SELECT COUNT(*):包括 NULL值;
SELECT COUNT(column_or_expression):不包括NULL值;
如果這兩個(gè)都可以滿足一需求時(shí)，并且在column上無(wú)Index時(shí)，建義用COUNT(*), 因?yàn)镃OUNT(*)能用到表上任意一個(gè)Index.
2, SQL JOINS（轉(zhuǎn)）
感覺(jué)這篇文章，對(duì)初學(xué)SQL的人會(huì)有一些幫助原文： Visual Representation of SQL Joins
3, SQL SERVER 通過(guò) FOR XML PATH子句，將多行轉(zhuǎn)成一列（以逗號(hào)隔開(kāi)）（轉(zhuǎn)）
原方地址： 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, 獲取數(shù)據(jù)庫(kù)所有外鍵的SQL
View Code
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恢復(fù)刪除的數(shù)據(jù)（轉(zhuǎn)dudu 實(shí)戰(zhàn) SQL Server 2008 數(shù)據(jù)庫(kù)誤刪除數(shù)據(jù)的恢復(fù) )
總共三個(gè)步驟：
- 備份當(dāng)前數(shù)據(jù)庫(kù)的事務(wù)日志：BACKUP LOG [數(shù)據(jù)庫(kù)名] TO disk= N'備份文件名' WITH NORECOVERY
- 恢復(fù)一個(gè)誤刪除之前的完全備份：RESTORE DATABASE [數(shù)據(jù)庫(kù)名] FROM DISK = N'完全備份文件名' WITH NORECOVERY,? REPLACE
- 將數(shù)據(jù)庫(kù)恢復(fù)至誤刪除之前的時(shí)間點(diǎn)：RESTORE LOG [數(shù)據(jù)庫(kù)] FROM? DISK = N'第一步的日志備份文件名' WITH?? STOPAT = N'誤刪除之前的時(shí)間點(diǎn)' , RECOVERY
總的來(lái)說(shuō)，在誤刪除數(shù)據(jù)后，做的第一件是就是備份數(shù)據(jù)庫(kù)操作日志，并記下刪除操作時(shí)間；然后恢復(fù)完全備份的數(shù)據(jù)庫(kù)（所以在開(kāi)發(fā)時(shí)也要養(yǎng)成備份數(shù)據(jù)庫(kù)的習(xí)慣）；最后恢復(fù)操作日志到刪除操作時(shí)間之前。
7, SQL UPDATE語(yǔ)句與INNER JOIN 結(jié)合
UPDATE
T2SET ID
=
REPLACE
(T.ID,
'
Start
'
,
'
TKStart
'
)
FROM
T2
INNER
JOIN
T
ON
T2.ID
=
T.ID
?8, sql server 查詢表結(jié)構(gòu)（轉(zhuǎn)自 reglong sql server 查詢表結(jié)構(gòu) ）
--
1：獲取當(dāng)前數(shù)據(jù)庫(kù)中的所有用戶表
select
Name
from
sysobjects
where
xtype
=
'
u
'
and
status
>=
0
--
2：獲取某一個(gè)表的所有字段
select
name
from
syscolumns
where
id
=
object_id
(
'
表名
'
)
--
3：查看與某一個(gè)表相關(guān)的視圖、存儲(chǔ)過(guò)程、函數(shù)
select
a.
*
from
sysobjects a, syscomments b
where
a.id
=
b.id
and
b.
text
like
'
%表名%
'
--
4：查看當(dāng)前數(shù)據(jù)庫(kù)中所有存儲(chǔ)過(guò)程
select
name
as
存儲(chǔ)過(guò)程名稱
from
sysobjects
where
xtype
=
'
P
'
--
5：查詢用戶創(chuàng)建的所有數(shù)據(jù)庫(kù)
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：查詢某一個(gè)表的字段和數(shù)據(jù)類型
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
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
