Shulou(Shulou.com)05/31 Report--

This article mainly introduces the Sql Server database commonly used Transact-SQL script sample analysis, has a certain reference value, interested friends can refer to, I hope you read this article after a lot of gains, the following let Xiaobian with everyone to understand.

Transact-SQL

Transact-SQL (also known as T-SQL) is an ANSI SQL implementation on Microsoft SQL Server and Sybase SQL Server, similar in nature to Oracle PL/SQL (not only implementing ANSI SQL, but also providing implementation support for its own database system features), and is still used as the core query language in Microsoft SQL Server and Sybase Adaptive Server.

database

1. Create a database

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. View the database

SELECT name, database_id, create_date FROM sys.databases ;

3. Delete database

DROP DATABASE Sales;

table

1. Create a table

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. Delete table

DROP TABLE dbo.PurchaseOrderDetail;

3. Renaming tables

EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';

column

1. Add columns

ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;

2. Delete columns

ALTER TABLE dbo.doc_exb DROP COLUMN column_b;

3. Renaming columns

EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';

constraint

1. Primary key

--Create a primary key in an existing table ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);--Create primary key in new table CREATE TABLE Production.TransactionHistoryArchive1 ( TransactionID int IDENTITY (1,1) NOT NULL , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);--View primary key SELECT name FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TransactionHistoryArchive'; GO --Delete primary key ALTER TABLE Production.TransactionHistoryArchive DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID; GO

view

1. Create a view

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. Delete View

DROP VIEW V_EmployeeHireDate;

stored procedure

1. Create a stored procedure

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. Delete stored procedures

DROP PROCEDURE P_UspGetEmployeesTest;

3. Execute the storage process

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. Rename stored procedures

EXEC sp_rename 'P_UspGetAllEmployeesTest', 'P_UspEveryEmployeeTest2';

5. Stored procedure with output parameters

CREATE PROCEDURE P_UspGetEmployee SalesYTD @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--invoke DECLARE @SalesYTDBySalesPerson money; EXECUTE P_UspGetEmployee SalesYTD N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT; GO

data type

Thank you for reading this article carefully. I hope that Xiaobian's "Sample Analysis of Transact-SQL Scripts Commonly Used in Sql Server Database" will be helpful to everyone. At the same time, I hope that everyone will support it more. Pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.