【SQL开源代码栏目提醒】:文章导读:在新的一年中,各位网友都进入紧张的学习或是工作阶段。网学会员整理了SQL开源代码-digitalshop.sql的相关内容供大家参考,祝大家在新的一年里工作和学习顺利!
USE master;
-- create the database
IF NOT EXISTS (SELECT dbid FROM sysdatabases WHERE name = 'DigitalShop')
CREATE DATABASE DigitalShop;
GO
-------------------------------------------------------------------------------
--
-- Database Tables
--
-------------------------------------------------------------------------------
-- switch to the .
NET DigitalShop database
USE DigitalShop;
GO
-------------------------------------------------------------------------------
-- Supplier Table
-------------------------------------------------------------------------------
CREATE TABLE Supplier
(
suppid int NOT NULL,
[name] varchar(80) NOT NULL,
status char(2) NOT NULL,
addr1 varchar(80) NULL,
addr2 varchar(80) NULL,
city varchar(80) NULL,
state varchar(80) NULL,
zip char(5) NULL,
phone varchar(80) NULL
);
-- add the primary key constraints
ALTER TABLE Supplier ADD
CONSTRAINT PK_Supplier
PRIMARY KEY CLUSTERED (suppid);
-- grant access
GRANT ALL ON Supplier TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Orders Table
-------------------------------------------------------------------------------
CREATE TABLE Orders
(
orderid int IDENTITY NOT NULL,
userid varchar(80) NOT NULL,
orderdate datetime NOT NULL,
shipaddr1 varchar(80) NOT NULL,
shipaddr2 varchar(80) NULL,
shipcity varchar(80) NOT NULL,
shipstate varchar(80) NOT NULL,
shipzip varchar(20) NOT NULL,
shipcountry varchar(20) NOT NULL,
billaddr1 varchar(80) NOT NULL,
billaddr2 varchar(80) NULL,
billcity varchar(80) NOT NULL,
billstate varchar(80) NOT NULL,
billzip varchar(20) NOT NULL,
billcountry varchar(20) NOT NULL,
courier varchar(80) NOT NULL,
totalprice numeric(10, 2) NOT NULL,
billtofirstname varchar(80) NOT NULL,
billtolastname varchar(80) NOT NULL,
shiptofirstname varchar(80) NOT NULL,
shiptolastname varchar(80) NOT NULL,
creditcard varchar(80) NOT NULL,
exprdate char(7) NOT NULL,
cardtype varchar(80) NOT NULL,
locale varchar(20) NOT NULL
);
-- add the primary key constraints
ALTER TABLE Orders ADD
CONSTRAINT PK_Orders
PRIMARY KEY CLUSTERED (orderid);
-- grant access
GRANT ALL ON Orders TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- OrderStatus Table
-------------------------------------------------------------------------------
CREATE TABLE OrderStatus
(
orderid int NOT NULL,
linenum int NOT NULL,
[timestamp] datetime NOT NULL,
status char(2) NOT NULL
);
-- add the primary key constraints
ALTER TABLE OrderStatus ADD
CONSTRAINT PK_OrderStatus
PRIMARY KEY CLUSTERED (orderid);
-- add the foreign key constraints
ALTER TABLE OrderStatus ADD
CONSTRAINT FK_OrderStatus_Orders FOREIGN KEY (orderid)
REFERENCES Orders (orderid);
-- grant access
GRANT ALL ON OrderStatus TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Category Table
-------------------------------------------------------------------------------
CREATE TABLE Category
(
catid char(10) NOT NULL,
[name] varchar(80) NULL,
descn varchar(255) NULL
);
-- add the primary key
ALTER TABLE Category ADD
CONSTRAINT PK_Category
PRIMARY KEY CLUSTERED (catid);
-- grant access
GRANT ALL ON Category TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Product Table
-------------------------------------------------------------------------------
CREATE TABLE Product
(
productid char(10) NOT NULL,
category char(10) NOT NULL,
[name] varchar(80) NULL,
descn varchar(255) NULL
);
-- add the primary key constraints
ALTER TABLE Product ADD
CONSTRAINT PK_Product
PRIMARY KEY CLUSTERED (productid);
-- add the foreign key constraints
ALTER TABLE Product ADD
CONSTRAINT FK_Product_Category FOREIGN KEY (category)
REFERENCES category (catid);
-- grant access
GRANT ALL ON Product TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Signon Table
-------------------------------------------------------------------------------
CREATE TABLE Signon
(
username varchar(80) NOT NULL,
pass
word varchar(25) NOT NULL,
);
-- add the primary key constraints
ALTER TABLE Signon ADD
CONSTRAINT PK_Sign