SqlServer入门之标准建库脚本

置顶包达叔

已于 2024-04-22 13:41:12 修改

阅读量1.4k

点赞数

分类专栏： SQL 文章标签： sqlserver 数据库 microsoft

于 2020-05-28 11:11:16 首次发布

本文链接：https://blog.csdn.net/zheyiw/article/details/106399669

版权

SQL 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

1，SqlServer入门之标准建库脚本

建表

-----------建表-----------------
IF OBJECT_ID ('SysUser', 'U') IS NULL
   BEGIN
      CREATE TABLE [dbo].[SysUser]
      (
         [Id]                     BIGINT IDENTITY (1, 1) NOT NULL, --主键用Id,整型,自增
         [Name]                   NVARCHAR (32) NOT NULL,
         [PhoneNumber]            NVARCHAR (32) NOT NULL,
         [Password]               NVARCHAR (128) NOT NULL,
         [UserName]               NVARCHAR (256) NOT NULL,
         [CreationTime]           DATETIME NOT NULL,
         [CreatorUserId]          BIGINT NULL,
         [LastLoginTime]          DATETIME NULL,
         [LastModificationTime]   DATETIME NULL,
         [LastModifierUserId]     BIGINT NULL,
         [IsDeleted]              BIT NOT NULL,
         [DeleterUserId]          BIGINT NULL,
         [DeletionTime]           DATETIME NULL,
         [IsActive]               BIT NOT NULL
      );

      --添加聚集索引,根据业务仔细设定
      CREATE CLUSTERED INDEX IX_SysUser_CreationTime
         ON SysUser (CreationTime);

      --添加非聚集索引
      CREATE NONCLUSTERED INDEX IX_SysUser_CreatorUserId
         ON SysUser (CreatorUserId);

      --添加默认值
      ALTER TABLE SysUser  ADD CONSTRAINT DF_SysUser_CreationTime DEFAULT GetDate() FOR CreationTime

      --添加主键
      ALTER TABLE SysUser ADD CONSTRAINT PK_SysUser   PRIMARY KEY (Id);
   END
GO

--主键用Id,整型,自增; 视业务用Int或者BigInt
--业务允许时,字段尽量设置为not null,null值用空字符串代替

修改表

-----------修改表------------------
--新增字段
If Col_Length('Bas_Student','id') Is Null
Begin
  Alter Table Bas_Student Add id Int IDENTITY(1,1) not Null
End
Go


--删除旧主键
IF EXISTS
     (SELECT *
      FROM   SYSINDEXES
      WHERE  NAME = 'PK_WMS_INV_MATERIALSTORER')
      ALTER TABLE Wms_Inv_MaterialStorer DROP CONSTRAINT PK_WMS_INV_MATERIALSTORER

--添加新主键
ALTER TABLE Wms_Inv_MaterialStorer
ADD CONSTRAINT PK_WMS_INV_MATERIALSTORER
PRIMARY KEY (CompanyID, StockID, StorerID, MaterialID, SizeID );

--添加唯一索引,来源单号
CREATE UNIQUE INDEX UI_EWS_Out_OrderMaster
  ON EWS_Out_OrderMaster(CompanyID, StockID, ExtTradeId)

其他创建

-----------建视图-----------------
------------删除对象------------------------
IF OBJECT_ID('vwStudent', 'V') IS NOT NULL
  DROP VIEW [vwStudent]
GO

----------仓储作业往来单位--------------
--Select * From vwStudent
CREATE VIEW [vwStudent]
AS
  SELECT CompanyID, StockID ConsigneeID, StockName AS ConsigneeName,
  CONVERT(INT, 1) AS ConsigneeType, Address, TelPhone
  FROM   Bas_Stock

GO

-----------对象是否存在判断-------------
----------存储过程-------------------
IF OBJECT_ID('spWMS_XXXXX','P') IS NOT NULL 
  DROP PROCEDURE [spWMS_XXXXX]
GO 
CREATE PROCEDURE [spWMS_XXXXX]
  @CompanyID VARCHAR(20), @StockID VARCHAR(20), @PersonnelID VARCHAR(20), @Operator VARCHAR(20), @ResVal INT OUTPUT
AS
......

---------视图-----------------------
IF OBJECT_ID('vwWMS_XXXXX', 'V') IS NOT NULL 
  DROP VIEW [dbo].[vwWMS_XXXXX]
GO 

----------临时表--------------
IF OBJECT_ID('tempdb..#XXXXX') IS NOT NULL 
DROP TABLE #XXXXX

---------函数----------------
--删除对象
IF OBJECT_ID('fnBloodPointInvSum') IS NOT NULL
  DROP FUNCTION [fnBloodPointInvSum]
GO

--点库存计算并汇总
CREATE FUNCTION fnBloodPointInvSum(@time VARCHAR(20))
  RETURNS @tableOut TABLE(VarietyId INT, VarietyName NVARCHAR(64), Volume FLOAT, Amount FLOAT, Qty INT)
AS
BEGIN
  INSERT INTO @tableOut(VarietyId, VarietyName, Volume, Qty, Amount)values(1, 2, 3, 4, 5)

  RETURN
END

------------调试模版-----------------
BEGIN TRY
  BEGIN TRAN


  ROLLBACK TRAN
END TRY
BEGIN CATCH
  SELECT ERROR_MESSAGE();
  ROLLBACK TRAN
END CATCH

2，SqlServer数据库管理

--将“恢复模式”设置为“简单”
ALTER DATABASE [btmsZsey20211021] SET RECOVERY SIMPLE 
GO
--收缩数据库
DBCC SHRINKDATABASE([btmsZsey20211021]) 
GO

表统计

SELECT  a.name AS [TABLE NAME] ,
        b.rows AS [RECORD COUNT]
FROM    sysobjects AS a
        INNER JOIN sysindexes AS b ON a.id = b.id
WHERE   ( a.type = 'u' )
        AND ( b.indid IN ( 0, 1 ) )
ORDER BY a.name ,
        b.rows DESC

谁在连接sqlserver？

--谁在连接sqlserver？
SELECT [dec].client_net_address,
       [des].[program_name],
       [des].[host_name],
       Count([dec].session_id) AS connection_count
FROM   sys.dm_exec_sessions AS [des]
       INNER JOIN sys.dm_exec_connections AS [dec]
               ON [des].session_id = [dec].session_id
GROUP  BY [dec].client_net_address,
          [des].[program_name],
          [des].[host_name]
ORDER  BY [des].[program_name],
          [dec].[client_net_address];

其他举例

Declare @DayOne DateTime--按天,今天
SET @DayOne = DATEADD (dd, DATEDIFF (dd, 0, getdate ()), 0)--按周,周的第一天
SET @DayOne = DATEADD (wk, DATEDIFF (wk, 0, getdate ()), 0)--按月,月的第一天
SET @DayOne = DATEADD (mm, DATEDIFF (mm, 0, getdate ()), 0)--连接删除:
Delete aFrom tableA aLeft Join TableB bOn a.XXX = b.XXXWhere a.YYY='yyy'--连接更新:
UPDATE aSET    a.password = 1FROM   sys_user a
--分页查询
SELECT *    FROM (       SELECT ROW_NUMBER () OVER (ORDER BY a.CardCode) AS rownum,                 a.CompanyID,                 a.CustomerID,                 a.CustomerName            FROM vwCRM_Bas_CardCustomerInfo a           WHERE a.Tel = '33' AND a.CustomerName = '包子'     ) AS data   WHERE data.rownum BETWEEN '1' AND '10';  
--字符串处理right(str,length) 取右边length长度的子字符串ltrim() 函数从字符串左侧删除空格或其他预定义字符, 还可以将数字转换成字符。
--由'hk0000000182'  --> 0000000183
declare @LogID varchar(20)declare @maxNum intset @LogID = 'hk0000000182'Select @maxNum = right(@LogID,10)select @maxNumselect right('0000000000'+ltrim(@maxNum+1),10)
--注意以下两句的区别
select  right('00000' + ltrim( '003'+ 3), 5) Sequence --> 00006select  right('00000' + ( '003'+ 3), 5) Sequence  --> 6
--根据行号生成序号 : 00001, 00002 等Right('00000' + ltrim(ROW_NUMBER() OVER (ORDER BY WaveBillNo)), 5) Sequence,拼接列值  Select ID + ',' From UserInfo FOR XML PATH('')  
建表例1:(自增,多主键,默认值)    
CREATE TABLE CSM_OTH_VipBabyInfo  (     CompanyID   INT IDENTITY (1, 1),     NewVipID    VARCHAR (20),     OldVipID    VARCHAR (20),     ModifyDTM   DATETIME DEFAULT getdate () NOT NULL,    CONSTRAINT pk PRIMARY KEY (CompanyID, NewVipID, OldVipID) )  
建表例2:
IF OBJECT_ID ('SD_Inv_BillSign', 'U') IS NULL   
BEGIN      CREATE TABLE SD_Inv_BillSign      (         [SignID]        INT IDENTITY (1, 1) NOT NULL,         [SignTime]      DATETIME DEFAULT GetDate () NOT NULL,         [CompanyID]     VARCHAR (99) NOT NULL,         [BillNo]        VARCHAR (99) NOT NULL,         [BillTypeID]    VARCHAR (99) NOT NULL,         [ShopID]        VARCHAR (99) NULL,         [StockID]       VARCHAR (99) NULL,         [BoxCount]      INT NULL,         [Operator]      VARCHAR (99) NULL,         [PersonnelID]   VARCHAR (99) NULL,         [Remark]        NVARCHAR (2000) NULL      );      
--添加聚集索引      
CREATE CLUSTERED INDEX IDX_SD_Inv_BillSign_SignTime         ON SD_Inv_BillSign (SignTime);      
--添加非聚集索引      
CREATE NONCLUSTERED INDEX IDX_SD_Inv_BillSign_BillNo         ON SD_Inv_BillSign (CompanyID, BillNo, BillTypeID);
--添加主键
ALTER TABLE SD_Inv_BillSign ADD CONSTRAINT PK_SD_Inv_BillSign   PRIMARY KEY (SignID);   
END
GO
带输入输出的存储过程的执行示例:  
declare @RetVal int  exec spCSM_ChangeVipCard 'hk','a',@RetVal output  
select @RetVal  DECLARE @MaxBatchNo INT = 0;
EXEC spCRM_GetMaxNum @ComCode = @CompanyID, @MaxNum = @MaxBatchNo output
SELECT @MaxBatchNo1.inner join on  内部连接 两表都满足的组合
2.full outer 全连 两表相同的组合在一起，A表有，B表没有的数据（显示为null）,同样B表有，A表没有的显示为(null)3.A表 left join  B表 左连,以A表为基础，A表的全部数据，B表有的组合。没有的为null4.A表 right join B表 右连,以B表为基础，B表的全部数据，A表的有的组合。没有的为null5.cross join : 笛卡尔积
DML（data manipulation language）：数据操作语言包括命令：SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。 
DDL（data definition language）：数据定义语言通常是数据库管理系统的一部分，用于定义数据库的所有特性和属性，尤其是行布局、列定义、键列（有时是选键方法）、文件位置和存储策略。 包括命令：DROP,CREATE,ALTER,GRANT,REVOKE, TRUNCATESQL Server 支持临时表。临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表，SQL Server 将自动除去临时表。临时表不存储在当前数据库内，而是存储在系统数据库 tempdb 内。  
临时表有两种类型：    
本地临时表    以一个井号 (#) 开头的那些表名。只有在创建本地临时表的连接上才能看到这些表。  全局临时表    以两个井号 (##) 开头的那些表名。在所有连接上都能看到全局临时表。如果在创建全局临时表的连接断开前没有显式地除去这些表，那么只要所有其它任务停止引用它们，这些表即被除去。当创建全局临时表的连接断开后，新的任务不能再引用它们。当前的语句一执行完，任务与表之间的关联即被除去；因此通常情况下，只要创建全局临时表的连接断开，全局临时表即被除去。