-- =============================================-- DatabaseName:YinTai-- desc:銀泰網（電子商務）-- pubdate:09:52 2014-07-18-- author:ylbtech-- http://m.yintai.com-- =============================================

1.B.1,

1.B.1.1, sql-basic.sql

use mastergo-- =============================================-- DatabaseName:銀泰網-- pubdate:09:52 2014-07-18-- author:ylbtech-- http://m.yintai.com/-- =============================================IF EXISTS (SELECT *        FROM   master..sysdatabases        WHERE  name = N'YinTai')    DROP DATABASE YinTaiGOCREATE DATABASE YinTaiGOuse YinTaigo-- =============================================-- table:賬戶-- 只做電子郵箱驗證注冊-- =============================================create table Account(accountId int identity(100001,1) PRimary key,    --編號【PK】nickname varchar(20) not null,    --昵稱email varchar(200),    --E-Mailphone varchar(200),    --手機號[passWord] varchar(20) not null,        --密碼[type] int,        --類型 0：手機號；1：電子郵箱validEmail bit,    --驗證validPhone bit,    --驗證pubdate datetime default(getdate()),    --注冊時間[enable] bit --狀態 0：正常；1：禁用)goinsert into Account(nickname,email,phone,[password],[type],validEmail,validPhone,pubdate,[enable])values('750833968','750833968@QQ.com','','123456',1,0,0,getdate(),0)go-- =============================================-- table:用戶中心[積分、銀元]-- =============================================--create table UserCenter--(--)go--drop table Address-- =============================================-- table:地址-- =============================================create table [Address](addressId int primary key identity(1000,1),    --編號【PK,ID】fullName varchar(200),    --收貨人mobile varchar(40),    --聯系電話province varchar(40),    --省city varchar(40),    --市area varchar(40),    --區縣addressDetail varchar(200),    --詳細地址accountId int references Account(accountId),    --賬號ID【FK】)goinsert into [Address](fullName,mobile,province,city,area,addressDetail,accountId)values('rain','123434546456','beijing','beijing','haidian','long range',100001)insert into [Address](fullName,mobile,province,city,area,addressDetail,accountId)values('rain','123434546456','beijing','beijing','haidian','long range',100001)-- ---------公共表------------------------------go-- =============================================-- table:組表（大分類）-男裝、女裝-- =============================================create table [Group](groupName varchar(200) primary key    --組名)insert into [Group](groupName) values('男裝')insert into [Group](groupName) values('女裝')go-- =============================================-- table:分類 男裝、女裝-- =============================================create table Category(categoryId int primary key identity(101,1),    --編號[name] varchar(200),    --分類名稱searchCondition int,    --查找條件parentId int,    --0:父；其他：父級別條件，即searchCondition[type] int    --0:文本；1：超鏈接（僅限二級使用）)insert into Category([name],searchCondition,parentId,[type]) values('女裝',100,0,0)insert into Category([name],searchCondition,parentId,[type]) values('男裝',101,0,0)goinsert into Category([name],searchCondition,parentId,[type]) values('上裝',10101,101,0)goinsert into Category([name],searchCondition,parentId,[type]) values('POLO衫',-1,10101,0)insert into Category([name],searchCondition,parentId,[type]) values('背心',-1,10101,0)goinsert into Category([name],searchCondition,parentId,[type]) values('套裝',10102,101,0)insert into Category([name],searchCondition,parentId,[type]) values('下裝',10103,101,0)goinsert into Category([name],searchCondition,parentId,[type]) values('美妝',102,0,0)insert into Category([name],searchCondition,parentId,[type]) values('家居',103,0,0)insert into Category([name],searchCondition,parentId,[type]) values('運動',104,0,0)go-- =============================================-- table:品牌  PeaceBird、LEE-- =============================================create table Brand(brandId int identity(10001,1),    --編號【PK,ID】[name] varchar(200) primary key,    --名稱searchCondition int,    --查找條件categoryId int    --類別ID)goinsert into Brand([name],searchCondition,categoryId) values('Peace Bird',100,101)insert into Brand([name],searchCondition,categoryId) values('Lee',100,101)go-- =============================================-- table:反饋-- =============================================create table Feedback(feedbackId int primary key identity(1000,1),    --編號【PK,ID】phoneModel varchar(200),    --電話型號contact varchar(200),        --聯系方式content varchar(200),    --意見network varchar(200),    --網絡信息browser varchar(200),    --瀏覽器questionType varchar(200),    --問題類型pubdate datetime default(getdate()),    --時間[type] int,                            --類型[status] int,                            --狀態replyContent varchar(200),                --回復內容replyPubdate datetime,            --回復日期accountType int,        --用戶類型accountId int foreign key references Account(accountId)   --賬戶ID【FK】 )go--drop table Recommend-- =============================================-- table:應用推薦-- =============================================create table Recommend(recommendId int primary key identity(100,1),    --編號【PK,ID】[name] varchar(200),    --應用名稱introduce varchar(700),    --說明downloadUrl varchar(500),    --下載地址icon varchar(500),    --圖標[type] int    --類型)goinsert into Recommend([name],introduce,downloadUrl,icon,[type])values('','','https://itunes.apple.com/cn/app/tian-qi-yi-bao/id663183019?mt=8','http://yrs.yintai.com/rs/img/AppCMS/20131209162750_tianqi.jpg',1)insert into Recommend([name],introduce,downloadUrl,icon,[type])values('','','https://itunes.apple.com/cn/app/tian-qi-yi-bao/id663183019?mt=8','http://yrs.yintai.com/rs/img/AppCMS/20131209162750_tianqi.jpg',1)goinsert into Recommend([name],introduce,downloadUrl,icon,[type])values('哈秀畫報（安卓版）','教你扮靚，女生必備！','http://www.haxiu.com/azapp/download_android.php?form=8','http://yrs.yintai.com/rs/img/AppCMS/20131209153301_114haxu.jpg',2)insert into Recommend([name],introduce,downloadUrl,icon,[type])values('哈秀畫報（安卓版）','教你扮靚，女生必備！','http://www.haxiu.com/azapp/download_android.php?form=8','http://yrs.yintai.com/rs/img/AppCMS/20131209153301_114haxu.jpg',2)insert into Recommend([name],introduce,downloadUrl,icon,[type])values('哈秀畫報（安卓版）','教你扮靚，女生必備！','http://www.haxiu.com/azapp/download_android.php?form=8','http://yrs.yintai.com/rs/img/AppCMS/20131209153301_114haxu.jpg',2)go-- drop table Activity-- =============================================-- table:限時特賣-- =============================================create table Activity([id] int,    --編號【FK】title varchar(200),    --標題imageUrl varchar(500),    --圖片地址isLabel int,    --1[name] varchar(200),    --品牌名稱logoImage varchar(500),    --Logo圖片discount varchar(200),    --折扣startTime datetime,    --開始時間endTime datetime,    --結束時間type int    -- -1=籌備中 0=已加入列表)goinsert into Activity(title,imageUrl,isLabel,[name],logoImage,discount,startTime,endTime,type)values('Anta','http://r.ytrss.com/rs/img/product/bargainproduct/bargain_APPTheme130504119744399979.jpg',0,'Anta','http://p10.ytrss.com/brand/brandlist/brand-615.jpg','2.4-4.4','2014-08-23','2014-09-23',0)goinsert into Activity(title,imageUrl,isLabel,[name],logoImage,discount,startTime,endTime,type)values('Ling-ning','http://r.ytrss.com/rs/img/product/bargainproduct/bargain_AppTheme130504119744399979.jpg',0,'Ling-ning','http://p10.ytrss.com/brand/brandlist/brand-615.jpg','