Tables

Create Tables

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

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 dbo.tbm_cta_CustomTableDROP COLUMN EnableHistory

Insert

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

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

CREATE FUNCTION HelloWorld4()RETURNS VARCHAR(20)ASBEGINRETURN 'Hello World!';END select dbo.helloworld4()

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

PostgreSQL中的CreateFunction

调用自定义函数的方式

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'
     
      
       
        22440
       
       
        1
       
       
       
        0
       
      
     '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 IsPromotionFROM @OrderItems.nodes('Orders/Order') AS T ( S )

declare @p1 xmlset @p1=convert(xml,N'
     
      
     ')SELECT T.Item.value('@MemberID', 'int') AS [MemberID]FROM @p1.nodes('Members/Member') AS T(Item)

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

SELECT  TABLE_NAMEFROM    INFORMATION_SCHEMA.TABLESWHERE   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

向数据表新增一个列

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

Stored Procedures

Delete

DROP PROCEDURE 
     
      ;  GO

查询一个表有多少列

SELECT *FROM Northwind.INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = N'Customers'

转载地址：http://rcnbm.baihongyu.com/

你可能感兴趣的文章