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
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
-
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/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 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://*.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/t-sql/statements/drop-procedure-transact-sql
DROP PROCEDURE <stored procedure name>;
GO
查询一个表有多少列
https://*.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'