两个常用的SQL语句
在写信息管理系统的过程中，SQL语句的使用是最频繁的，只要活用了SQL语句，可以完成很多特殊的功能，下面是积累的一些SQL语句。
1、在已有表中添加新的字段
使用Alter语句，基本语法为
ALTER TABLE tablename
ADD fieldname datatype(20)
其中tablename是数据库中表名，fieldname是要新增的字段名，datatype是数据类型，后面括号中的是字段长度。
例如要在表Employeeinfo中新增字段Salary，类型为Money(这个数据类型不需要长度)，则代码如下：
ALTER TABLE Employeeinfo
ADD Salary money
Alter还可以修改已有的字段和删除已有的字段，详情请参看下面的Transact-SQL的ALTER语法：
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE <> ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]
| ADD
{ [ <> ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ <> } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}
<> ::=
{ column_name data_type }
[ [ DEFAULT constant_expression ] [ WITH VALUES ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL ]
[ COLLATE <> ]
[ <> ] [ ...n ]
<> ::=
[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}
<> ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression
[ FOR column ] [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
2、比较数据库中两张表中记录的差异
这也是比较常用的，可以比较出两张表中相同类型字段的差异，比如有表A的字段x和表B的字段y都是同以类型，则可以比较出两张表中这个字段记录的差异，语句如下：
select * from A where checksum(x) not in (select checksum(y) from B)
也可以把checksum(x)中的x换为*，这样就比较的是表中的所有字段。该语句返回的是表A的所有字段形成的记录。
