当前位置:数据库 > > 正文

sqlserver常用数据结构图(Sql Server数据库常用Transact-SQL脚本推荐)

时间:2021-10-16 00:14:52类别:数据库

sqlserver常用数据结构图

Sql Server数据库常用Transact-SQL脚本推荐

Transact-SQL

Transact-SQL(又称 T-SQL),是在 Microsoft SQL Server 和 Sybase SQL Server 上的 ANSI SQL 实现,与 Oracle 的 PL/SQL 性质相近(不只是实现 ANSI SQL,也为自身数据库系统的特性提供实现支持),在 Microsoft SQL Server 和 Sybase Adaptive Server 中仍然被使用为核心的查询语言。

数据库

1、创建数据库

  • USE master ; 
    GO 
    CREATE DATABASE Sales 
    ON 
    ( NAME = Sales_dat, 
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\saledat.mdf', 
     SIZE = 10, 
     MAXSIZE = 50, 
     FILEGROWTH = 5 ) 
    LOG ON 
    ( NAME = Sales_log, 
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\salelog.ldf', 
     SIZE = 5MB, 
     MAXSIZE = 25MB, 
     FILEGROWTH = 5MB ) ; 
    GO 
    
    
    
  • 2、查看数据库

  • SELECT name, database_id, create_date 
    FROM sys.databases ; 
    
  • 3、删除数据库

  • DROP DATABASE Sales;
    
  • 1、创建表

  • CREATE TABLE PurchaseOrderDetail 
    ( 
     ID uniqueidentifier NOT NULL 
     ,LineNumber smallint NOT NULL 
     ,ProductID int NULL 
     ,UnitPrice money NULL 
     ,OrderQty smallint NULL 
     ,ReceivedQty float NULL 
     ,RejectedQty float NULL 
     ,DueDate datetime NULL 
    ); 
    
  • 2、删除表

  • DROP TABLE dbo.PurchaseOrderDetail; 
    
  • 3、重命名表

  • EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr'; 
    
  • 1、添加列

  • ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;
    
  • 2、删除列

  • ALTER TABLE dbo.doc_exb DROP COLUMN column_b; 
    
  • 3、重命名列

  • EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN'; 
    
  • 约束

    1、主键

  • --在现有表中创建主键
    ALTER TABLE Production.TransactionHistoryArchive
     ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);
    
    --在新表中创建主键
    CREATE TABLE Production.TransactionHistoryArchive1
     (
      TransactionID int IDENTITY (1,1) NOT NULL
      , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
     )
    ;
    
    --查看主键 
    SELECT name 
    FROM sys.key_constraints 
    WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TransactionHistoryArchive'; 
    GO 
    --删除主键
    ALTER TABLE Production.TransactionHistoryArchive 
    DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID; 
    GO 
    
  • 视图

    1、创建视图

  • CREATE VIEW V_EmployeeHireDate 
    AS 
    SELECT p.FirstName, p.LastName, e.HireDate 
    FROM HumanResources.Employee AS e JOIN Person.Person AS p 
    ON e.BusinessEntityID = p.BusinessEntityID ; 
    GO 
    
  • 2、删除视图

  • DROP VIEW V_EmployeeHireDate; 
    
  • 存储过程

    1、创建存储过程

  • CREATE PROCEDURE P_UspGetEmployeesTest 
     @LastName nvarchar(50), 
     @FirstName nvarchar(50) 
    AS 
     SELECT FirstName, LastName, Department 
     FROM HumanResources.vEmployeeDepartmentHistory 
     WHERE FirstName = @FirstName AND LastName = @LastName 
     AND EndDate IS NULL; 
    GO 
    
  • 2、删除存储过程

  • DROP PROCEDURE P_UspGetEmployeesTest; 
    
  • 3、执行存储过程

  • EXEC P_UspGetEmployeesTest N'Ackerman', N'Pilar'; 
    -- Or 
    EXEC P_UspGetEmployeesTest @LastName = N'Ackerman', @FirstName = N'Pilar'; 
    GO 
    -- Or 
    EXECUTE P_UspGetEmployeesTest @FirstName = N'Pilar', @LastName = N'Ackerman'; 
    GO 
    
  • 4、重命名存储过程

  • EXEC sp_rename 'P_UspGetAllEmployeesTest', 'P_UspEveryEmployeeTest2'; 
    
  • 5、带有输出参数的存储过程

  • CREATE PROCEDURE P_UspGetEmployeeSalesYTD 
    @SalesPerson nvarchar(50), 
    @SalesYTD money OUTPUT 
    AS 
     SELECT @SalesYTD = SalesYTD 
     FROM SalesPerson AS sp 
     JOIN vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID 
     WHERE LastName = @SalesPerson; 
    RETURN 
    GO
    
    --调用
    DECLARE @SalesYTDBySalesPerson money; 
    EXECUTE P_UspGetEmployeeSalesYTD 
     N'Blythe', 
     @SalesYTD = @SalesYTDBySalesPerson OUTPUT; 
    GO 
    
  • 数据类型

    sqlserver常用数据结构图(Sql Server数据库常用Transact-SQL脚本推荐)

    总结

    以上所述是小编给大家介绍的Sql Server数据库常用Transact-SQL脚本,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对开心学习网网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

    上一篇下一篇

    猜您喜欢

    热门推荐