Transact-SQL命令语法-数据表

SQL数据表操作命令备忘

 

创建数据表语法

 CREATE TABLE [database_name.[owner].| owner.] table_name
      ( {<column_definition> |  column_name  AS  computed_column_expression  |
<table_constraint>} [,...n] )
         [ON {filegroup | DEFAULT} ]
          [TEXTIMAGE_ON {filegroup | DEFAULT} ]
<column_definition> ::= { column_name data_type }
[ [ DEFAULT constant_expression ]
      | [ IDENTITY [(seed, increment ) [NOT FOR REPLICATION] ] ] ]
[ ROWGUIDCOL ]
[ COLLATE < collation_name > ]
[ <column_constraint>] [ ...n]

创建数据表示例

if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[products3]') and OBJECTPROPERTY(id,N'IsUserTable')=1)
drop table [dbo].[products3]

create table mydb.dbo.products3 (
p_id   char(8) not null,
p_name    char(10) not null ,
price    money default 0.01 ,
quantity    smallint null ,
constraint pk_p_id primary key (p_id, p_name)
) on [primary]

 

修改数据表示例

create table order_firm (
order_firm_id char (8) primary key,
firm_name    varchar (50) not null
firm_introduce  char(50) null
) on [primary]
alter table order_firm
alter column firm_introduce varchar(250) null
add order_firm_id char(8) null
constraint fk_order_firm_id foreign key references order_firm(order_firm_id)