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