操作数据和管理表
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
学习目标：
1.熟练使用SQl DML语句对表进行增删和改的操作
2.了解并会使用事务
3.能使用SQl DDL语句创建并管理表
4.对表创建并维护约束
创建表：
CREATETABLE[schema.]table
(
Col1type1[DEFAULT1],
Col2type2[DEFAULT2],
....
Colntype2[DEFAULTn]
)
实例
createtable depts
(
dept_id int,
dept_name varchar(20),
managet_fname varchar(20),
dept_loc varchar(30)
);
createtable departments
(
department_id int,
department_name varchar(20),
manager_id int,
location_id int
);
Schema(方案)是对象的集合，方案对象直接反映数据在数据库中的逻辑结构，方案对象包括表，视图，同义词，序列，存储过程，索引，集群等。默认情况下，方案名等于用户名。
INSERT语句
INSER INTO table[(column1,column2...)]
VALUES (values1,values2...)
insertinto departments
(department_id,department_name,manager_id,location_id)
values(280,'Development',103,1700)
select*from departments where manager_id=1700;
数据库修改
ALTERTABLEtable
{[ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ]) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
|{ADD|DROP}ROWGUIDCOL}
]
|ADD
{[ < column_definition > ]
|column_name AS computed_column_expression
}[ ,...n ]
|[ WITH CHECK | WITH NOCHECK ]ADD
{
|DROP
{[ CONSTRAINT ]constraint_name
|COLUMNcolumn}[ ,...n ]
|{CHECK|NOCHECK}CONSTRAINT
{ALL|constraint_name[ ,...n ]}
|{ENABLE|DISABLE}TRIGGER
{ALL|trigger_name[ ,...n ]}
}
{column_namedata_type}
[ [ DEFAULT constant_expression ][ WITH VALUES ]
|[ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ] ]
[ ROWGUIDCOL ]
[ COLLATE < collation_name > ]
[ < column_constraint > ][ ...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 } ]
]
|FOREIGNKEY
[ ( 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)
}
A. 更改表以添加新列
下例添加一个允许空值的列，而且没有通过 DEFAULT 定义提供值。各行的新列中的值将为 NULL。
CREATETABLE doc_exa(column_a INT)
GO
ALTERTABLE doc_exa ADD
column_b VARCHAR(20)NULL
GO