一些关键的SQL语句及ALTER TABLE
1.查询MSSQL系统版本: select @@version
2.日志压缩删除到1MB
use rtool_1;
BACKUP LOG rtool_1 WITH NO_LOG;
DBCC SHRINKDATABASE (rtool_1);
3.备份数据库
backup database RTool to disk='D:/rtool.bak'
4.恢复数据库
restore database RTool FROM disk='D:/rtool.bak'
5.日期时间函数应用
SELECT year(getdate()),month(getdate()),day(getdate())
SELECT DATEADD (datepart , number, date )
SELECT DATEDIFF(datepart, startDate, endDate)
DATEPART(标志量):year,quarter(季度),month,dayofyear,day,week,weekday,hour,minute,second,millisecond(毫),microsecond(微),nanosecond(纳)
http://msdn.microsoft.com/zh-cn/library/ms186724.aspx
6.union all 投影(左右的字段一致)
7.ALTER语法:
--表中增加一列
ALTER TABLE table_name ADD column_name datatype
--表中一列删除
ALTER TABLE table_name DROP COLUMN column_name
--表中一列增加主键约束
ALTER TABLE rtoolLog ADD CONSTRAINT PK_rtoolLog PRIMARY KEY(rtlIndex)
--表中一列删除主键约束
ALTER TABLE rtoolLog DROP CONSTRAINT PK_rtoolLog WITH (ONLINE = ON);
--表中一列修改属性
ALTER TABLE table_name ALTER COLUMN column_name datatype
--表中非聚集索引创建
CREATE NONCLUSTERED INDEX [PHM_syoukyaku_idx1] ON [dbo].[PHM_syoukyaku]
(
[HMsyo_ScenarioId] ASC,
[HMsyo_BranchNo] ASC,
[HMsyo_CustomerNo] ASC,
[HMsyo_KijyunNengetu] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
--表中索引删除
USE [RTool_1]
GO
DROP INDEX PHM_syoukyaku_idx1 ON PHM_syoukyaku
--表中一列修改为自动增长字段(先删除约束,再删除字段,再次创建字段)
ALTER TABLE rtoolLog DROP CONSTRAINT PK_rtoolLog WITH (ONLINE = ON);
ALTER TABLE rtoolLog drop column rtlIndex
ALTER TABLE rtoolLog ADD rtlIndex bigint not null IDENTITY(1,1) CONSTRAINT PK_rtoolLog PRIMARY KEY(rtlIndex)
ALTER TABLE (Transact-SQL)
更新日期: 2008 年 12 月 4 日
通过更改、添加或删除列和约束,重新分配分区,或者启用或禁用约束和触发器,从而修改表的定义。
语法
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
ALTER COLUMN column_name
{
[ type_schema_name. ] type_name [ ( { precision [ , scale ]
| max | xml_schema_collection } ) ]
[ COLLATE collation_name ]
[ SPARSE | NULL | NOT NULL ]
| {ADD | DROP }
{ ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
}
| [ WITH { CHECK | NOCHECK } ]
| ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
| <column_set_definition>
} [ ,...n ]
| DROP
{
[ CONSTRAINT ] constraint_name
[ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
| COLUMN column_name
} [ ,...n ]
| [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
| { ENABLE | DISABLE } CHANGE_TRACKING
[ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]
| SWITCH [ PARTITION source_partition_number_expression ]
TO target_table
[ PARTITION target_partition_number_expression ]
| SET ( FILESTREAM_ON = { partition_scheme_name | filegroup |
"default" | "NULL" } )
| REBUILD
[