数据库常用语句

新建表

USE [DataBase]
GO

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[table]') AND type IN ('U'))
	DROP TABLE [dbo].[table]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [table](
	[Column1] [int] IDENTITY(1,1) NOT NULL,
	[Column2] [varchar](20) NOT NULL DEFAULT '无',
 CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED 
(
	[Column1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第一列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'[table]', @level2type=N'COLUMN',@level2name=N'[Column1]'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第二列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'[table]', @level2type=N'COLUMN',@level2name=N'[Column2]'
GO

添加列

Alter Table [table] Add
 [Column3] Decimal(18,2) null

修改列

 Alter Table [table] Alter Column
 [Column3] Bit not null

删除列

  Alter Table [table] 
  Drop Column [Column3]

添加值

  INSERT [table] VALUES('第二列',1)

修改值

  UPDATE [table] SET Column2='修改',Column3=0 where Column1=1

基础查询

SELECT 列名 FROM 表名 WHERE 判断条件

左连接 LEFT JOIN（以左表为基础表，两表数据行不相等的情况下，左表行多，则查询出来的表，右表缺失部分为NULL，右表多出部分不显示）

SELECT T1.列名,T2.列名 FROM 表名1 AS T1
LEFT JOIN 表名2 AS T2 ON T1.列名=T2.列名
WHERE 判断条件

右连接 RIGHT JOIN（以右表为基础表，两表数据行不相等的情况下，右表行多，则查询出来的表，左表缺失部分为NULL，左表多出部分不显示）

SELECT T1.列名,T2.列名 FROM 表名1 AS T1
RIGHT JOIN 表名2 AS T2 ON T1.列名=T2.列名
WHERE 判断条件

内连接 INNER JOIN（只显示两表有共同数据行的部分，缺失部分都不显示）

SELECT T1.列名,T2.列名 FROM 表名1 AS T1
INNER JOIN 表名2 AS T2 ON T1.列名=T2.列名
WHERE 判断条件

查询表是否有自增列

Select OBJECTPROPERTY(OBJECT_ID('表名'),'TableHasIdentity')