创建名为mydatabase的数据库中结构如下的表student_info
编号 | 字段名称 | 字段类型 | 字段宽度 | 说明 | 是否空 |
1 | studentno | int | 4 | 自动从1增长的序号 | not null |
2 | studenID | int | 4 | 学号,主键 | not null |
3 | name | varchar | 40 | 姓名 | not null |
4 | sex | char | 2 | 性别 | not null |
5 | type | varchar | 20 | 类别 | not null |
6 | birthday | date | 8 | 出生日期 | |
7 | classno | dmallint | 2 | 班机编号 | not null |
8 | major | varchar | 40 | 专业 | not null |
9 | departmentno | smallint | 2 | 所在院系 | not null |
10 | photo | p_w_picpath | 16 | 照片 |
/*如果数据库mydatabase已经存在则删除,然后重新创建数据库*/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'mydatabase')
DROP DATABASE [mydatabase]
GO
CREATE DATABASE [mydatabase]
ON (NAME = N'mydatabase_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mydatabase_Data2.MDF' ,
SIZE = 1,
FILEGROWTH = 10%)
LOG ON (NAME = N'mydatabase_Log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mydatabase_Log2.LDF' ,
SIZE = 1,
FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO
/*使用数据库mydatabase*/
use [mydatabase]
GO
/*如果名称为student_info 的数据表已经存在则删除*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student_info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[student_info]
GO
/*创建数据表student_info*/
CREATE TABLE [dbo].[student_info] (
[studentno] [int] IDENTITY (1, 1) NOT NULL ,
[studentid] [int] NOT NULL PRIMARY KEY,
[name] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[sex] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[type] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[birthday] [datetime] NULL ,
[classno] [smallint] NOT NULL ,
[major] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[departmentno] [smallint] NOT NULL ,
[photo] [p_w_picpath] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/*调用系统存储过程sp_help 来查看创建的数据表的结果信息*/
EXEC sp_help [student_info]
GO
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'mydatabase')
DROP DATABASE [mydatabase]
GO
CREATE DATABASE [mydatabase]
ON (NAME = N'mydatabase_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mydatabase_Data2.MDF' ,
SIZE = 1,
FILEGROWTH = 10%)
LOG ON (NAME = N'mydatabase_Log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mydatabase_Log2.LDF' ,
SIZE = 1,
FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO
/*使用数据库mydatabase*/
use [mydatabase]
GO
/*如果名称为student_info 的数据表已经存在则删除*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student_info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[student_info]
GO
/*创建数据表student_info*/
CREATE TABLE [dbo].[student_info] (
[studentno] [int] IDENTITY (1, 1) NOT NULL ,
[studentid] [int] NOT NULL PRIMARY KEY,
[name] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[sex] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[type] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[birthday] [datetime] NULL ,
[classno] [smallint] NOT NULL ,
[major] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[departmentno] [smallint] NOT NULL ,
[photo] [p_w_picpath] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/*调用系统存储过程sp_help 来查看创建的数据表的结果信息*/
EXEC sp_help [student_info]
GO
转载于:https://blog.51cto.com/daiwei/50695