一些关键的SQL语句及ALTER TABLE

一些关键的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
      [