小编给大家分享一下sqlserver的表、视图、索引如何实现创建、修改、删除操作,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!
让客户满意是我们工作的目标,不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户,将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴,公司提供的服务项目有:主机域名、网络空间、营销软件、网站建设、古交网站维护、网站推广。
一、表相关
1、创建
USE [test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE 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)修改表名:
EXEC sp_rename 'table_name', 'table_new_name'
(2)新增字段:
ALTER TABLE table_name ADD column_name datatype
(3)修改字段名:
EXEC sp_rename '表名.column_name','new_column_name','column'
(4)修改字段类型:
ALTER TABLE table_name ALTER COLUMN column_name datatype
(5)删除字段:
ALTER TABLE table_name DROP COLUMN column_name
3、删除
DROP TABLE `test`;
二、视图相关
1、创建
USE [test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --注释卸载这儿 CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition GO
2、修改
ALTER VIEW view_name AS SELECT * FROM ceshi;
3、删除
DROP VIEW view_name
三、索引相关
1、创建
(1)在表上创建一个简单的索引
USE [test] GO CREATE 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)在表上创建一个唯一的索引
USE [test] GO CREATE 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、删除
USE [test] GO DROP INDEX index_name ON table_name WITH ( ONLINE = OFF ) GO
以上是“sqlserver的表、视图、索引如何实现创建、修改、删除操作”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联行业资讯频道!