一、表相关
1、创建
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
USE [test]GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Ceshi](
[id] [int] NOT NULL,
[name] [varchar(30)] NULL,
CONSTRAINT [PK_Ceshi] PRIMARY KEY CLUSTERED
( [id] 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--添加字段注释EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键一个' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Ceshi', @level2type=N'COLUMN',@level2name=N'id'
GO--修改字段注释EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'主键一个' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Ceshi', @level2type=N'COLUMN',@level2name=N'id'
GO |
2、修改
（1）修改表名：
|
1
|
EXEC sp_rename 'table_name', 'table_new_name'
|
（2）新增字段：
|
1
|
ALTER TABLE table_name ADD column_name datatype
|
（3）修改字段名：
|
1
|
EXEC sp_rename '表名.column_name','new_column_name','column'
|
（4）修改字段类型：
|
1
|
ALTER TABLE table_name ALTER COLUMN column_name datatype
|
（5）删除字段：
|
1
|
ALTER TABLE table_name DROP COLUMN column_name
|
3、删除
|
1
|
DROP TABLE `test`;
|
二、视图相关
1、创建
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
USE [test]GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO--注释卸载这儿CREATE VIEW view_name AS
SELECT column_name(s) FROM table_name
WHERE condition
GO |
2、修改
|
1
2
|
ALTER VIEW view_name AS
SELECT * FROM ceshi;
|
3、删除
|
1
|
DROP VIEW view_name
|
三、索引相关
1、创建
（1）在表上创建一个简单的索引
|
1
2
3
4
5
6
7
8
9
|
USE [test]GOCREATE NONCLUSTERED INDEX index_name ON table_name
( column_name ASC,
column_name2 DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO |
（2）在表上创建一个唯一的索引
|
1
2
3
4
5
6
7
8
|
USE [test]GOCREATE CLUSTERED INDEX index_name ON table_name
( column_name ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO |
2、删除
|
1
2
3
4
5
|
USE [test]GODROP INDEX index_name ON table_name WITH ( ONLINE = OFF )
GO |
本文转自 独孤环宇 51CTO博客，原文链接:http://blog.51cto.com/snowtiger/1929104