SQL语句创建（数据库|数据表|索引）_sql数据库创建数据表-优快云博客

本文链接：https://blog.youkuaiyun.com/RoyYeah/article/details/4598231

本文介绍了一个图书管理系统的数据库设计，包括创建数据库、数据表及其字段定义、主键和外键设置，并为提高查询效率创建了多个非聚集索引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE Table <表名>
   （[<列名1>] 类型 (长度) [缺省值][列级约束]
     [，<列名2> 数据类型[缺省值][列级约束]]….
     [，UNIQUE（列名[，列名]….）]
     [，PRIMARY KEY（列名[，列名]…）]
     [，FOREIGN  KEY （列名[，列名]…）
     REFERENCES 表名（列名[，列名]…）]
     [，CKECK (条件)] )
功能：
建立一个以<表名>为名的表。

/*创建借书还书数据库*/ create database 借书还书信息 on ( name = N'借书还书信息Data', filename = N'D:/借书还书信息.mdf', size = 20Mb, maxsize = 40Mb, filegrowth = 10Mb ) log on ( name = N'借书还书信息_Log', filename = N'D:/借书还书信息_Log.ldf', size = 20Mb, maxsize = 35Mb, filegrowth = 10Mb ) collate SQL_Latin1_General_CP1_CI_AS go /*创建数据表*/ use 借书还书信息 go create table dbo.图书 ( 图书编号 int identity(1,1) not null , 图书名称 varchar(50), 作者 varchar(20), 出版社 varchar(50), 备注 varchar(100), primary key (图书编号) ); create table dbo.用户 ( 用户编号 int identity(1,1) not null, 口令 varchar(20), 姓名 varchar(20), 性别 bit, 出生年月 datetime, 账户余额 money, primary key (用户编号) ); CREATE TABLE [dbo].[借书记录] ( [记录编号] [int] IDENTITY(1,1) NOT NULL, [用户编号] [int] NOT NULL, [图书编号] [int] NOT NULL, [借书时间] [datetime] NULL, [还书时间] [datetime] NULL, [是否欠费] [bit] NULL, [欠费金额] [bit] NULL, [是否还书] [bit] NULL, PRIMARY KEY (记录编号), foreign key (用户编号) references 用户(用户编号), foreign key (图书编号) references 图书(图书编号) ); /*创建索引*/ /****** 对象: Index [Book_ID_Name_Index] 脚本日期: 09/26/2009 17:18:09 ******/ CREATE NONCLUSTERED INDEX [Book_ID_Name_Index] ON [dbo].[图书] ( [图书编号] ASC, [图书名称] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] /****** 对象: Index [User_ID_Name_Index] 脚本日期: 09/26/2009 17:19:01 ******/ CREATE NONCLUSTERED INDEX [User_ID_Name_Index] ON [dbo].[用户] ( [用户编号] ASC, [姓名] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] /****** 对象: Index [UserId_BookId_Index] 脚本日期: 09/26/2009 17:19:43 ******/ CREATE NONCLUSTERED INDEX [UserId_BookId_Index] ON [dbo].[借书记录] ( [用户编号] ASC, [图书编号] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]