SQL中常見語句

獵手家園發表於2016-05-02
SQL

SQL中常見語句筆記：

--替換欄位中的回車符和換行符
UPDATE [dbo].[MGoods_Test] SET GoodsName = REPLACE(GoodsName, CHAR(13) + CHAR(10), '')

--刪除表命令
DROP TABLE [dbo].[MGoods_Test]

--刪除表中資料命令
DELETE FROM [dbo].[MGoods_Test]        --逐行刪除 有日誌記錄
TRUNCATE TABLE [dbo].[MGoods_Test]    --TRUNCATE TABLE 語句是一種快速、無日誌記錄的方法。

--刪除資料庫中所有表
DECLARE cs_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE type = 'U'

DECLARE @tablename VARCHAR(100)
DECLARE @sql VARCHAR(150)
OPEN cs_cursor

FETCH NEXT FROM cs_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'DROP TABLE ' + @tablename
    EXEC(@sql)
    FETCH NEXT FROM cs_cursor INTO @tablename
END

CLOSE cs_cursor
DEALLOCATE cs_cursor

--資料庫設定為單使用者模式
--1、在物件資源管理器中，連線到 SQL Server 資料庫引擎的例項，然後展開該例項。
--2、右鍵單擊要更改的資料庫，再單擊“屬性”。
--3、在“資料庫屬性”對話方塊中，單擊“選項”頁。
--4、在“限制訪問”選項中，選擇“單使用者”。
--5、如果其他使用者連線到資料庫，將出現“開啟的連線”訊息。 若要更改屬性並關閉所有其他連線，請單擊“是”。
ALTER DATABASE [DaiGou.Log] SET SINGLE_USER    --單使用者
ALTER DATABASE [DaiGou.Log] SET MULTI_USER    --多使用者

--新增表欄位
ALTER TABLE dbo.MOrderDetailByCreated
ADD ParentId INT NOT NULL DEFAULT 0

--修改表欄位
ALTER TABLE MOrderDetailByCreated 
ALTER COLUMN ParentId INT NOT NULL

--修改預設值
--需要先刪除約束
ALTER TABLE MOrderDetailByCreated 
ADD DEFAULT (0) FOR ParentId WITH VALUES 

--修改欄位長度
ALTER TABLE dbo.MOrderDetail 
ALTER COLUMN GoodsReferrerUrl VARCHAR(200)

--看誰在連線資料庫
EXEC sys.sp_who2
--檢視資料庫資訊
EXEC sys.sp_helpdb [DaiGou.Com_Backup]

--將處理熱備狀態的資料庫手動上線使用
RESTORE DATABASE [Log_shipping_test] WITH RECOVERY

/*修復資料庫孤立使用者  開始*/
USE [DaiGou.Com_Backup]
SELECT * FROM sysusers WHERE name='m6go_user'

USE master
SELECT * FROM syslogins WHERE name='m6go_user'

--修復SQL資料庫使用者孤立問題，前提是此資料庫要有讀寫許可權。
--步驟1：
--列出當前資料庫的孤立使用者
USE [DaiGou.Com_Backup]
EXEC sp_change_users_login 'REPORT'

--步驟2：
--可以自動將使用者名稱所對應的同名登入新增到syslogins中。
USE [DaiGou.Com_Backup]
EXEC sp_change_users_login 'AUTO_FIX','USERNAME'

--步驟3:
--將使用者名稱對映為指定的登入名。
USE [DaiGou.Com_Backup]
EXEC sp_change_users_login 'UPDATE_ONE','USERNAME','DENG LU MING'
/*修復資料庫孤立使用者  結束*/

/*修復AlwaysOn孤立使用者的問題 開始*/
--1、在主伺服器上建立使用者名稱；
--2、給相關庫指定許可權；
--3、從主伺服器獲取此使用者的SID和密碼；
SELECT * FROM sys.sql_Logins WHERE name='wangjunfu'
--SID：0x4391109F4189B34A9D9E4187236579A0
--PWD：0x020043EB733E16F085F0EE08283C4F18C6E0F85BF0220479C92840919FCE4371CC812DDF921FB163A33EE6E14B882E49F86225F184221471555A4F15C41ED90CE7E80700DBA8

--4、到輔助伺服器執行下面語句進行建立使用者。
CREATE LOGIN wangjialin WITH PASSWORD = 0x020043EB733E16F085F0EE08283C4F18C6E0F85BF0220479C92840919FCE4371CC812DDF921FB163A33EE6E14B882E49F86225F184221471555A4F15C41ED90CE7E80700DBA8
HASHED, SID = 0x4391109F4189B34A9D9E4187236579A0,
CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

/*修復AlwaysOn孤立使用者的問題 結束*/

--建立索引
CREATE INDEX Index_Country ON dbo.MBigDataClear
(
    Country ASC
)

--刪除索引
DROP INDEX dbo.MBigDataClear.Index_PositionPageType

--按時間查詢
SELECT  COUNT(DISTINCT SessionId)
FROM    dbo.MBigDataCollect WITH ( NOLOCK )
WHERE   EquipmentId = 0
        AND PositionId = 0
        AND CreateTime >= DATEADD(SECOND, -210, GETDATE())
        AND CreateTime <= DATEADD(SECOND, -30, GETDATE())
        
--遞迴取出資料(有ParentId的情況下)
WITH cte AS
(
    SELECT * FROM dbo.HBrand WHERE BrandId=1 AND IsStop=0 AND IsDel=0
    UNION ALL
    SELECT b.* FROM cte c INNER JOIN dbo.HBrand b ON c.BrandId = b.ParentId
)
SELECT * FROM cte

WITH cte AS
(
    SELECT CatalogId,ParentId,CatalogName FROM dbo.MCatalog WHERE CatalogId = 253
    UNION ALL
    SELECT b.CatalogId,b.ParentId,b.CatalogName FROM cte c INNER JOIN dbo.MCatalog b ON c.ParentId = b.CatalogId
)
SELECT * FROM cte

--手動寫分頁
WITH t2 AS ( 
    SELECT TOP 20
    *
    FROM  MOrderCoupon WITH ( NOLOCK )
    WHERE Id < ( SELECT MIN(Id) FROM ( SELECT TOP 100 Id FROM dbo.MOrderCoupon WITH ( NOLOCK ) ORDER BY Id DESC ) t1 )
    ORDER BY Id DESC
)
SELECT * FROM t2;

--掃描表資訊
DBCC SHOWCONTIG (MBigDataClear)

--查詢表中有多少索引
EXEC sp_helpindex [MBigDataClear]
--重建表索引
ALTER INDEX PK_MBigDataClear ON [dbo].[MBigDataClear] REBUILD

--獲取短日期
DECLARE @dt VARCHAR(20)
SET @dt = CONVERT(VARCHAR(10),GETDATE(),120)
SELECT @dt

--建立唯一約束
ALTER TABLE dbo.HCarsBills ADD CONSTRAINT UE_BookId UNIQUE (BookId)

--將字串轉化成整數
SELECT id FROM [dbo].[HHotelCustom]  WHERE ISNUMERIC(id)<=0

相關文章