【SQL】常用命令

备份数据库

BACKUP DATABASE data_name TO DISK='[disk_path]

恢复数据库

RESTORE DATABASE data_name FROM DISK='[disk_path]'

创建数据库

CREATE DATABASE database_name
ON 
<filespec> [ , <filespec>,... ]
[, FILEGROUP filegroup_name <filespec> [ , <filespec>,... ] ]
[LOG ON <filespec> [ , <filespec>,... ] ]

例：

CREATE DATABASE Stu_Course1 ON
(
      NAME = 'Stu_Course1_data',
      FILENAME = 'c:\program files\microsoft sql server\mssql\data\Stu_Course1_data.mdf',
      SIZE = 3MB,
      MAXSIZE = 10MB,
      FILEGROWTH = 10%
)
LOG ON
(
      NAME = 'Stu_Course1_log',
      FILENAME = 'c:\program files\microsoft sql server\mssql\data\Stu_Course1_log.ldf',
      SIZE = 1MB,
      MAXSIZE = 5MB,
      FILEGROWTH = 1MB
)

修改数据库

ALTER DATABASE data_name
{ ADD FILE < filespec > [ ,…n ] [ TO FILEGROUP filegroup_name ]
                      /*在文件组中增加数据文件*/
| ADD LOG FILE < filespec > [ ,…n ]
                      /*增加日志文件*/
| MODIFY FILE <filespec>
                      /*更改文件属性*/
……
}

删除数据库

DROP DATABASE data_name

创建表

CREATE TABLE [schema_name].table_name 
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )

修改表结构

ALTER TABLE [schema_name.]table_name 
{ ALTER COLUMN column_name <data_type> [ NULL | NOT NULL ]
 | ADD <column_definition> | <computed_column_definition> | <table_constraint> 
 | DROP [ CONSTRAINT ] constraint_name  | COLUMN column_name }
 

删除表

DROP TABLE [schema_name].table_name

插入语句

INSERT INTO [schema_name.]table_name [( column_list )] 
VALUES({expression | DEFAULT | NULL } [ ,...n ]) 

更新语句

UPDATE [schema_name.]table_name
SET column_name = { expression | DEFAULT | NULL } [ ,...n ] 
[ WHERE <search_condition> ]

删除语句

DELETE [FROM] [schema_name].table_name
[WHERE <search_condition> ]