SQL Server 常用命令

Tables 

Create Tables

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-tables-database-engine

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql

CREATE TABLE dbo.PurchaseOrderDetail  
(  
    PurchaseOrderID int NOT NULL  
    ,LineNumber smallint NOT NULL  
    ,ProductID int NULL  
    ,UnitPrice money NULL  
    ,OrderQty smallint NULL  
    ,ReceivedQty float NULL  
    ,RejectedQty float NULL  
    ,DueDate datetime NULL  
);  

Delete Columns from a Table

 https://docs.microsoft.com/en-us/sql/relational-databases/tables/delete-columns-from-a-table

To delete columns

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

    

USE AdventureWorks2012;  
GO  
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;  

If the column contains constraints or other dependencies, an error message will be returned. Resolve the error by deleting the referenced constraints.

For additional examples, see ALTER TABLE (Transact-SQL).  

ALTER TABLE dbo.tbm_cta_CustomTable
DROP COLUMN EnableHistory

 

 

 

Insert 

https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql

insert into people(id,name)
values(1,'chucklu')
insert into people(id,name)
values(2,'lihu')

 

insert into toys(id,name,people_id)
values(1,'a',1)
insert into toys(id,name,people_id)
values(1,'b',2)
insert into toys(id,name,people_id)
values(1,'c',1)
insert into toys(id,name,people_id)
values(1,'d',2)
insert into toys(id,name,people_id)
values(1,'e',1)

 

User-defined Functions 

Create

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql

示例，创建一个名为HelloWorld4的函数，不需要输入参数

CREATE FUNCTION HelloWorld4()
RETURNS VARCHAR(20)
AS
BEGIN
RETURN 'Hello World!';
END

 

select dbo.helloworld4()

 

=================================

 

PostgreSQL中的CreateFunction

https://www.codewars.com/kata/580fe518cefeff16d00000c0/solutions/sql

 

 

调用自定义函数的方式

SELECT dbo.udf_GetHistoryTableNameByTableCode('MemberRole',5)

udf_GetHistoryTableNameByTableCode为函数名，后面2个是参数

 

 

查询数据库版本

select @@version

Microsoft SQL Server 2008 (SP4) - 10.0.6241.0 (X64) 

Apr 17 2015 10:56:08 
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

 

声明xml变量，并进行查询

DECLARE @OrderItems xml =N'<Orders><Order><ProductID>22440</ProductID><Quantity>1</Quantity><LanCode></LanCode><IsPromotion>0</IsPromotion></Order></Orders>'

SELECT S.value('ProductID[1]', 'int') AS ProductID ,
S.value('Quantity[1]', 'int') AS Quantity ,
S.value('LanCode[1]', 'NVARCHAR(100)') AS LanCode ,
S.value('IsPromotion[1]', 'int') AS IsPromotion
FROM @OrderItems.nodes('Orders/Order') AS T ( S )

 

 

declare @p1 xml
set @p1=convert(xml,N'<Members><Member MemberID="147"/></Members>')

SELECT T.Item.value('@MemberID', 'int') AS [MemberID]
FROM @p1.nodes('Members/Member') AS T(Item)

 

获取指定数据库的，所有用户自定义的数据表

SELECT  TABLE_NAME
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
        AND TABLE_CATALOG = 'DatabaseName'

 

Print

普通打印

Print N'Chuck'

Print无法打印出拼接的sql

     可能是因为拼接的sql中的某一个变量为空

 

 

Select Into

异常:An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

在select静态列的时候，需要给静态列起一个列名。

 

Alert 

向数据表新增一个列

https://stackoverflow.com/questions/12678208/altering-sql-table-to-add-column

IF NOT EXISTS ( SELECT 1
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
WHERE sysobjects.name = N'tbm_den_DynamicEntity'
AND syscolumns.name = N'EnableHistory'
)
ALTER TABLE [dbo].[tbm_den_DynamicEntity]
ADD EnableHistory BIT;

 

Stored Procedures

Delete

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/delete-a-stored-procedure#TsqlProcedure 

https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-procedure-transact-sql

DROP PROCEDURE <stored procedure name>;  
GO  

 

 

查询一个表有多少列

https://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server

SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

 

作者:Chuck Lu    GitHub