web-3g-(163)网易-邮箱-记事本-数据库设计

ylbtech-DatabaseDesgin:ylbtech-cnblogs(博客园)-数据库设计-2,Admin(用户后台)

DatabaseName:cnblogs(博客园)

Model:Admin 用户后台管理数据设计

Type:专业技术网站

1.A，数据库关系图(Database Diagram)

1.B，数据库设计脚本(Database Design Script)

1,/Notebook163-Basic.sql

-- ============================================= -- Menu:仿网易(Netease)邮箱记事本 -- KeyWord:SQL Server, Notebook -- -- ylb: ylb,tech -- 23:20 2012/2/2 -- 更多资源请访问:http://user.qzone.qq.com/750833968 -- ============================================= USE master GO  -- Drop the database if it already exists IF  EXISTS (     SELECT name          FROM sys.databases          WHERE name = N'Notebook163' ) DROP DATABASE Notebook163 GO  CREATE DATABASE Notebook163 GO use Notebook163 go -- ============================================= -- ylb: 1,分类 -- ============================================= create table Categories ( cateID int primary key identity, cateName varchar(200) not null, number int default(0),    -- 本类记事本的数量 pubdate datetime default(getdate()) )  go -- ============================================= -- ylb: 2,记事 -- ============================================= create table Notebook ( noteID int primary key identity, theme varchar(200) not null, content varchar(5000), pubdate datetime default(getdate()), flag int check(flag in(0,1)),    --待办记事 0：不是；1：是 --6, cateID int references Categories(cateID) )  go -- ============================================= -- ylbTest:1,插入测试数据 -- ============================================= insert into Categories(cateName) values('未分类')

View Code

2,1,/select/Categories.sql

-- ============================================= -- ylb: 仿网易记事本 -- 1，对"Categories"操作 -- ============================================= use Notebook163 go --1,Add insert into Categories(cateName) values('未分类') select @@IDENTITY  go --2,Get all select cateID,cateName,number from Categories order by pubdate asc

View Code

3,2,/select/Notebook.sql

-- ============================================= -- ylb: 仿网易记事本 -- 2，对"Notebook"操作 -- ============================================= use Notebook163 go --1,Add insert into Notebook(theme,content,flag,cateID) values('I love you.','Love you forever',0,1)  go --2,Get all --？多表连接 select noteID,theme,content,pubdate,flag,cateID from Notebook order by pubdate desc  go --3,Delete by ID delete Notebook where noteID=0  go --4,Get a notebook by ID select noteID,theme,content,pubdate,flag,cateID from Notebook where noteID=1  go --5,Update by ID update Notebook set theme='',content='',pubdate='',flag=1, cateID=1 where noteID=0

View Code

1.C，功能实现代码(Function Implementation Code)