亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

sql 語句 收集

系統 1808 0

--查詢表名包含某字符

Select *
From sysobjects
Where name like '%Area%' and type='u'

?

-- 含有'XXX'字段的所有表

select a.[name] TableName from sysobjects a,
(
select [id],count(*) b from syscolumns
where [name] ='SupplierREF'
group by [id]
)
b where a.[id]=b.[id]

?

---查看數據庫表的約束

select a.name as DFName,b.Name as ColumnName
from sys.objects a inner join sys.columns b on a.object_id=b.default_object_id
where a.type='D'
and b.object_id=object_id(' 數據庫表名 ')

?

---查看數據庫表的外鍵

SELECT Table_Name as [Table], Column_Name as [Column],
Constraint_Name as [Constraint], Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where Table_Name=' 數據庫表名 '
ORDER BY [Table], [Column]

?

-----SqlServer如何查詢表的列數

select count(*) from sysobjects a join syscolumns b
on a.id=b.id
where a.name='表名'

?

------?MSSQL查詢所有沒有主鍵的表

SELECT?name?FROM?sys.tables
EXCEPT
SELECT?TABLE_NAME?FROM?INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE?CONSTRAINT_TYPE?=?'PRIMARY?KEY'
?

----?SQL只復制表結構操作 (不如直接右鍵數據表使用Creaate腳本好用)

--復制表結構有句型的
--跨數據庫
--復制結構+數據
select *? into 數據庫名.dbo.新表名?? from? 數據庫名.dbo.原表名
--只復制結構?
select *? into 數據庫名.dbo.新表名?? from? 數據庫名.dbo.原表名 where 1=0
--復制到臨時表
select *? into #temptablename?? from? 數據庫名.dbo.原表名 where 1=0?

--跨服務器數據庫
select * into 本地庫名.表名 from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=遠程ip;User ID=sa;Password=密碼'
).庫名.dbo.表名

?

----?SQL批量更新數據

UPDATE [Bms_Areas]
set ParentID =X.ParentID
,[AreaCode] = X.[AreaCode]
,[AreaName] = X.[AreaName]
,[Notes] =X.[Notes]
,[LevelNum] = X.[LevelNum]
,[IsDefault] = X.[IsDefault]
,[IsValid] = X.[IsValid]
,[IsDel] = X.[IsDel]
from [Bms_Areas] T, DGbell_erp.dbo.[Bms_Areas222] X
where T.AreaID =X.AreaID

?

-----實現刪除表中某列有重復值的數據行

delete Bms_StoppageReason from Bms_StoppageReason a
where exists(select * from Bms_StoppageReason
where id<a.id and Code=a.Code)

?

----將一個表中某個字段的值全部更新到另外一個表相應的字段

update sp set sp.ParentID =sr.ID
from Bms_StoppagePhenomenon sp join Bms_StoppageReason sr
on sr.Code=sp.Descriptio

?

-----SQL分組取最新值
select 1,'A','2011-09-08 08:38:57.870',8,null union all
select 2,'A','2011-09-20 08:38:57.870',25,null union all
select 3,'A','2011-10-03 08:38:57.870',12,null union all
select 4,'B','2011-10-15 08:38:57.870',1,null union all
select 5,'B','2011-10-19 08:38:57.870',20,null union all
select 6,'B','2011-11-02 08:38:57.870',10,null union all
select 7,'C','2011-09-20 08:38:57.870',0,null union all
select 8,'C','2011-10-03 08:38:57.870',12,null union all
select 9,'C','2011-10-15 08:38:57.870',15,null

----------- ---- ----------------------- ----------- -----------
1 A 2011-09-08 08:38:57.870 8 NULL
2 A 2011-09-20 08:38:57.870 25 NULL
3 A 2011-10-03 08:38:57.870 12 NULL
4 B 2011-10-15 08:38:57.870 1 NULL
5 B 2011-10-19 08:38:57.870 20 NULL
6 B 2011-11-02 08:38:57.870 10 NULL
7 C 2011-09-20 08:38:57.870 0 NULL
8 C 2011-10-03 08:38:57.870 12 NULL
9 C 2011-10-15 08:38:57.870 15 NULL

所需結果如下:
----------- ---- ----------------------- ----------- -----------
3 A 2011-10-03 08:38:57.870 12 NULL
6 B 2011-11-02 08:38:57.870 10 NULL
9 C 2011-10-15 08:38:57.870 15 NULL

?

select col2,col4 from @t a
WHERE col3=(SELECT max(col3) FROM @t WHERE col2=a.col2)

?

----------- ---- ----------------------- ----------- -----------

drop table #Tmp --刪除臨時表#Tmp
create table #tmp
(
[TmpName] [varchar](50) NULL,
[TmpCount] [float] NULL
)
insert into #tmp
Select 'A', 320 union all
Select 'B', 21 union all
Select 'C', 54 union all
Select 'D', 236 union all
Select 'E', 325 union all
Select 'F', 32

?

SELECT a.[TmpName], a.[TmpCount] ,(a.[TmpCount]/b.T) as TmpPercent
FROM #tmp a, (SELECT SUM([TmpCount]) as T from #tmp )as b

?

-----------------------------sql中分組序號

例如:
111111 123
111111 345
222222 aaaa
222222 bbbb
222222 ccccc
變成: 111111 1 123
111111 2 345
222222 1 aaaa
222222 2 bbbb
222222 3 ccccc
每個小組有次序號。

----------》》

假設表名為 test1, 字段名為 a, b

SQL 語句如下:

select t.a, t.b, (SELECT COUNT(*) FROM test1 WHERE a = t.a AND b <= t.b) AS xuhao FROM test1 t ORDER BY t.a

?

?

?

----------------------------- 刪除交集,我想在table1中刪除table1和table2的交集

delete a
from table1 as a
inner join??
table2 as b on a.[field*]=b.[field*]

?

----------------按行號刪除

delete T from (select *,row_number() over(order by ProductID,SHID) as RowIndex

from [Bms_Store_Current] ) ?T?where T.RowIndex>2988

?

?

    假設將table1表的column1改名為column2,可以使用如下:
    
sp_rename 'table1.column1','column2','column'


SQL Server批量刪除數據庫表------------------------------------------

DECLARE @Table NVARCHAR(30)
DECLARE tmpCur CURSOR FOR
SELECT name FROM sys.objects WHERE TYPE='U' AND name LIKE N'Bms_%'
OPEN tmpCur
FETCH NEXT FROM tmpCur INTO @Table

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql VARCHAR(100)
SELECT @sql = 'drop table ' + @Table
EXEC(@sql)
FETCH NEXT FROM tmpCur INTO @Table
END
CLOSE tmpCur
DEALLOCATE tmpCur

    
先找出你的外鍵所在用下面的語句:--查詢一個表的所有外鍵:------------------------------------------

SELECT 主鍵列ID=b.rkey
,主鍵列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
,外鍵表ID=b.fkeyid
,外鍵表名稱=object_name(b.fkeyid)
,外鍵列ID=b.fkey
,外鍵列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
,級聯更新=ObjectProperty(a.id,'CnstIsUpdateCascade')
,級聯刪除=ObjectProperty(a.id,'CnstIsDeleteCascade')
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
and object_name(b.rkeyid) LIKE N'Bms_%'

?

-----------------比較兩個數據庫表的架構-------------------------

select name, schema_name(schema_id) [schema] from db1.sys.objects where type = 'U'
except
select name, schema_name(schema_id) [schema] from db2.sys.objects where type = 'U'

?

----------------- 相同username 找id小的 -------------------------

select a.* from ttt_test2 as a inner join
(
select user_name,min(id) as minid From ttt_test2 Group by user_name
) as b
on a.user_name=b.user_name and id=b.minid

?

-----------------使用SQL語句獲得服務器名稱和IP地址-------------------------

SELECT SERVERPROPERTY( 'MachineName' )
select @@SERVERNAME
select HOST_NAME()
?
SELECT SERVERNAME = CONVERT (NVARCHAR(128),SERVERPROPERTY( 'SERVERNAME' ))
,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'
,CLIENT_NET_ADDRESS AS 'ClientIPAddress'
? FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID

sql 語句 收集


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

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

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 奇米影视777中文久久爱 | 夜夜躁日日躁狠狠久久 | 国产欧美日韩在线观看 | 国产精品国偷自产在线 | 欧美成人免费在线视频 | 国产91小视频 | 91九色国产 | 在线久综合色手机在线播放 | 久久久噜久噜久久gif动图 | 99久久免费国内精品 | 欧美性www| 中文字幕在线播 | 久久久视频6r | 又黄又爽又色的免费毛片 | 天天干 夜夜操 | 免费夜色污私人影院网站 | 91在线视频观看 | 九一视频在线免费观看 | 2022国产男人亚洲欧美天堂 | 日韩我不卡 | 99一级毛片 | 干美女在线视频 | 久久久久欧美精品三级 | 26uuu另类欧美亚洲曰本 | 亚洲日本aⅴ片在线观看香蕉 | 久久99精品综合国产首页 | 一a一片一级一片啪啪 | 精品国产96亚洲一区二区三区 | 欧美日韩高清在线观看一区二区 | 午夜a一级毛片一.成 | 一级成人毛片 | 国产亚洲精品久久久久久久软件 | 亚洲午夜一区 | 四虎精品永久在线 | 中国特级黄一级真人毛片 | 久久午夜国产片 | 四虎4hutv永久地址公告 | 日本激情啪啪 | 天天操天天干天天摸 | 日本高清在线观看天码888 | 亚洲视频99|