USE MASTER
go
-- =============================================
-- ylb: 3g版网易邮箱
-- url: http://m.mail.163.com/
-- devloper：ylb,tech
-- author: YuanBo
-- date: 11:11 2012-07-05
-- =============================================
IF EXISTS (SELECT * 
       FROM   master..sysdatabases 
       WHERE  name = N'_3g163EMail')
    DROP DATABASE _3g163EMail
GO

CREATE DATABASE _3g163EMail
GO

USE _3g163EMail
GO
-- =============================================
-- ylb: 1.1邮箱帐户表
-- =============================================
create table MailUsers
(
mailUser varchar(100) primary key,    --帐号名称【PK】
pwd varchar(100) not null,            --密码
mobileNum varchar(100)                --手机号
)


GO
-- =============================================
-- ylb: 2,邮件
-- =============================================
create table EMail
(
eMailId int primary key identity(100,1),    --编号【PK】
[subject] varchar(200) not null,            --标题
content varchar(5000),                        --正文
sendDate datetime default(getdate()),        --发件时间|接受时间
sendMail varchar(200) not null,    --发送电子邮箱地址

toMail varchar(200) not null,    --接受电子邮箱地址
att varchar(200),        --附件地址
flagRead int default(0) check(flagRead in(0,1)),    --邮件是否被读 0:未读：1：已读
flagState int, --邮件状态 0：已发送；1：保存
mailUser varchar(100) --帐号名称【FK】
)

GO
-- =============================================
-- ylb: 3,联系人组
-- =============================================
create table ContactGroup
(
contactGroupId int primary key identity(10,1),    --编号【PK】
contactGroupName varchar(100) not null,            --分组名称
mailUser varchar(100) not null                            --邮箱帐号【FK】
)
go
/*
insert into ContactGroup(contactGroupName,mailUser) values('朋友','')
insert into ContactGroup(contactGroupName,mailUser) values('同事','')
insert into ContactGroup(contactGroupName,mailUser) values('亲人','')
insert into ContactGroup(contactGroupName,mailUser) values('网友','')
insert into ContactGroup(contactGroupName,mailUser) values('经常联系人','')
insert into ContactGroup(contactGroupName,mailUser) values('','')
select @@identity

insert into ContactGroup(contactGroupName,mailUser) values('朋友','rain@163.com')

--insert into ContactGroup(contactGroupName,mailUser) ({'亲人','rain@163.com'},{'同事','rain@163.com'})
--一种类似的形式
*/
go
--2,GetAll
select contactGroupId,contactGroupName from ContactGroup where mailUser='' order by contactGroupId desc


GO
-- =============================================
-- ylb: 3.2,联系人
-- =============================================
create table Contact
(
contactId int primary key identity(100,1),        --编号【PK】
name varchar(100) not null,                --姓名
emailPref varchar(100),                                --电子邮箱
telCell varchar(100),                --电话|移动电话号
contactGroupIds varchar(100),        --联系人分组编号【FK】
mailUser varchar(100) not null        --邮箱帐号【FK】
)
GO
-- =============================================
-- ylb: 3.3,联系人与分组关系
-- =============================================
create table ContactGroupContact
(
contactId int not null,            --联系人（UserMails）编号【FK】
contactGroupId int not null,    --联系人分组编号（ContactGroup）【FK】
mailUser varchar(100) not null    --邮箱帐户（Contact）编号【FK】
)

GO
-- =============================================
-- ylb: 4,邮件附件
-- =============================================
create table Attachments
(
attachmentId int primary key identity(1,1),    --编号【PK】
attachmentUrl varchar(200) not null,                --附件保存地址
[filename] varchar(200),                --文件名称
filesize varchar(100),                    --文件大小
emailId int,                    --邮件编号【FK-Email】
tempId varchar(200), --临时的保存标识【当邮箱没保存的时候】
mailUser varchar(100) not null    --邮箱帐户（Contact）编号【FK】
)

go
select * from MailUsers


GO
-- =============================================
-- ylb: 5,文件中心
-- =============================================
create table [File]
(
fileId int primary key identity(1,1),    --编号【PK】
[fileName] varchar(200) not null,        --文件名称
baseId int default(0),                    --上级编号 0：无上级；
fileType int,                    --文件类型 1:文件夹；2：非文件夹
fileUrl varchar(100),                    --（非文件时）文件保存位置
fileSize int,                        --文件大小
mailUser varchar(100) not null    --邮箱帐户（Contact）编号【FK】
)

GO

use _3g163Email
go
-- =============================================
-- ylb: 3g版网易邮箱
-- url: http://m.mail.163.com/
-- devloper：ylb,tech
-- author: YuanBo
-- date: 11:11 2012-07-05
-- =============================================
-- =============================================
-- ylb-menu: 1, 邮箱帐户表的操作与步骤
-- =============================================

GO
-- =============================================
-- ylb: 1,用户登录
-- =============================================
select COUNT(*) from MailUsers where mailUser='yb@163.com' and pwd='m123456'

GO
-- =============================================
-- ylb: 2,注册用户
-- =============================================
--2.1,验证“电子邮箱名称”是否已被注册
SELECT COUNT(*) FROM MailUsers where mailUser='yb@163.com'
--2.2,注册邮箱
insert into MailUsers(mailUser,pwd,mobileNum) values('yb@163.com','m123456','13520698733')

use _3g163EMail

go
-- =============================================
-- ylb: 1,发邮件
-- =============================================

select * from EMail

select subject,content,sendMail,toMail,att,flagRead,flagState,mailUser from EMail

--insert into EMail(subject,content,sendMail,toMail,att,flagRead,flagState) values()
select @@identity

go
-- =============================================
-- ylb: 2,查邮件，根据编号
-- =============================================

select eMailId,subject,content,sendDate,sendMail
,toMail,att,flagRead,flagState,mailUser from EMail
where eMailId=0

go
-- =============================================
-- ylb: 2.2,上一封|下一封
-- =============================================
-- 上一封
select top 1 * from EMail where eMailId >2 order by email asc
go
-- 下一封
select top 1 * from EMail where eMailId <2 order by email desc


go
-- =============================================
-- ylb: 2,查邮件，根据编号
-- =============================================

use _3g163EMail
go

select * from Contact

GO
-- =============================================
-- ylb: 1，添加一个联系人
-- =============================================
select contactId,name,telCell,emailPref from Contact where contactId=0
go
--insert into Contact() values()
go
select contactGroupId from ContactGroupContact where contactId=0
go
--insert into ContactGroupContact(contactId,contactGroupId,mailUser) values()
GO
-- =============================================
-- ylb: 2,获取所有联系人
-- =============================================
select contactId,name,telCell,emailPref from Contact where mailUser='' order by contactId desc

GO
-- =============================================
-- ylb: 3,更新一个联系人
-- =============================================
select contactId,name,telCell,emailPref from Contact where contactId=0
go
--update Contact set where contactId=0
go
--先删除，在添加分组
delete ContactGroupContact where contactId=0
--
insert into ContactGroupContact(contactId,contactGroupId,mailUser) values()
--
GO

-- =============================================
-- ylb: 4,批量删除联系人
-- =============================================
delete Contact where mailUser='' and contactId in(0,0)

use _3g163EMail
go

select * from Contact

GO
-- =============================================
-- ylb: 1，添加一个联系人
-- =============================================
select contactId,name,telCell,emailPref from Contact where contactId=0
go
--insert into Contact() values()
go
select contactGroupId from ContactGroupContact where contactId=0
go
--insert into ContactGroupContact(contactId,contactGroupId,mailUser) values()
GO
-- =============================================
-- ylb: 2,获取所有联系人
-- =============================================
select contactId,name,telCell,emailPref from Contact where mailUser='' order by contactId desc

GO
-- =============================================
-- ylb: 3,更新一个联系人
-- =============================================
select contactId,name,telCell,emailPref from Contact where contactId=0
go
--update Contact set where contactId=0
go
--先删除，在添加分组
delete ContactGroupContact where contactId=0
--
insert into ContactGroupContact(contactId,contactGroupId,mailUser) values()
--
GO

-- =============================================
-- ylb: 3,
-- =============================================
select * from ContactGroupContact

use _3g163EMail
go

select cg.contactGroupId,contactGroupName,COUNT(*) 'quantity' from ContactGroup cg 
inner join ContactGroupContact cgc on cg.contactGroupId=cgc.contactGroupId

group by cg.contactGroupId,cg.contactGroupName
having cgc.mailUser=''


select contactGroupId,contactGroupName,(select COUNT(*) from ContactGroupContact cgc where cgc.contactGroupId=cg.contactGroupId) 'quantity' from ContactGroup cg
 where mailUser=@mailUser order by contactGroupId desc

go

select contactGroupId,contactGroupName from ContactGroup cg

select contactGroupId,COUNT(*) from ContactGroupContact

group by contactGroupId
having mailUser=''

use _3g163EMail

go
-- =============================================
-- ylb: 1,添加附件
-- =============================================
select * from Attachments
insert into Attachments(attachmentUrl,[filename],filesize,emailId,mailUser,tempId) values()
select attachmentUrl,[filename],filesize,emailId,mailUser,tempId from Attachments

go
-- =============================================
-- ylb: 2,查询附件，根据tempId
-- =============================================
select attachmentId,attachmentUrl,[filename],filesize,emailId,mailUser,tempId from Attachments where tempId=''

go
-- =============================================
-- ylb: 3,删除一个附件
-- =============================================
delete Attachments where attachmentId=0


go
-- =============================================
-- ylb: 1,发邮件
-- =============================================

use _3g163EMail
GO
-- =============================================
-- ylb: 5,文件中心
-- =============================================

GO
--1,新建文件夹
insert into [File]([fileName],baseId,fileType,mailUser) values()

go
--1.2,上传文件
insert into [File]([fileName],baseId,fileType,fileUrl,mailUser,fileSize) values()

go
--2,批量删除文件
delete [File] where mailUser='rain@163.com' and fileId in(0,1)

--3,查询文件列表

select fileId,[fileName],fileType,fileUrl from [File] where mailUser='rain@163.com' and baseId=0
order by fileId desc
go
select * from [File]

go
--4,查询文件
select [fileName],fileUrl,fileSize from [File] where fileType=-1 and mailUser='rain@163.com' and fileId=0

go
--5,查文件夹名称
select [fileName] from [File] where fileType=1 and mailUser='rain@163.com' and fileId=0

--6,查一个文件夹下包含的文件数（注释：不包括文件夹数量）
--6.1,用户包含的,网盘，只查下一层的文件数
select count(*) from [File] where fileType=-1 and mailUser='rain@163.com' and baseId=0

--6.2,递归去查
select count(*) from [File] where fileType=-1 and mailUser='rain@163.com' and baseId=0