《sqlserver应用开发》 1.sqlserver配置属性(对某一数据库右击) 处理器选项卡 来配置sqlserver的优先级, ^ 在windows上提升sqlserver的优先级 连接 选项卡, 来配置连接特性。 * 允许其它sqlserver...连接。。 2BBs四个表(在csdn空间3/bbs表/BBS表1,表2,表3) 建表要注意:除了字段名称,类型,主键,默认值,非空约束,还要有检查约束(设计视图中右击,check约束/新建)eg.UEMail like '%@%' len(UPassword)>=6 和字段的描述信息。 ?数据类型为'Bit'的Usex,1 ,性别,如何添加数据。。。 ?数据类型 nText 16 位;varchar 255 是长度,和位数不一样。
3.建立表之间的关联关系。 谁指向谁的哪一个字段。 这个建立后,使用hibernate可直接生成1对1,1对多,多对多等关联关系。
4。发贴排名(本日,本周,各版块本日,各版块本周) 本日的日期比较使用: GetDate() Between (TTime+'0:0:0') and (TTime='23:59:59') 判断日期是否在本周,需要使用datepart 函数返回当天是“本年的第几周”,然后再看看数据库中的 日期是“本年的第几周”。如果这两个周次相等,表明数据库中的日期在本周。 DatePart(week,GetDate())=DatePart(week,TTime);
BBS用户发帖日排名 select top 10 TUID as 用户ID,count(*) as 发帖数 from BBSTopic where Getdate() Between (TTime+'0:0:0') and (TTime='23:59:59') group by tuid order by count(*) desc BBS用户发帖周排名 select top 10 TUID as 用户ID,count(*) as 发帖数 from BBSTopic where DatePart(week,GetDate())=DatePart(week,TTime) group by TUid order by Count(*) desc
各版块用户发帖日排名 select top 10 TUID as 用户ID,TSID as 版块,count(*) as 发帖数 from BBSTopic where GetDate() Between (TTime+'0:0:0') and (TTime='23:59:59') group by TUid,TSID order by TSID,count(*) desc 各版块用户发帖周排名 select top 10 TUID as 用户ID,TSID as 版块,count(*) as 发帖数 from BBSTopic where DatePart(week,GetDate())=DatePart(week,TTime) group by TUid,TSID order by TSID,count(*) desc
附: bbs.sql if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BBSpely_BBSSection]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[BBSReply] DROP CONSTRAINT FK_BBSpely_BBSSection GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BBSTopic_BBSSection]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[BBSTopic] DROP CONSTRAINT FK_BBSTopic_BBSSection GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BBSpely_BBSTopic]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[BBSReply] DROP CONSTRAINT FK_BBSpely_BBSTopic GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BBSpely_BBSUsers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[BBSReply] DROP CONSTRAINT FK_BBSpely_BBSUsers GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BBSSection_BBSUsers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[BBSSection] DROP CONSTRAINT FK_BBSSection_BBSUsers GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BBSTopic_BBSUsers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[BBSTopic] DROP CONSTRAINT FK_BBSTopic_BBSUsers GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BBSReply]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[BBSReply] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BBSSection]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[BBSSection] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BBSTopic]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[BBSTopic] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BBSUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[BBSUsers] GO
CREATE TABLE [dbo].[BBSReply] ( [RID] [int] NOT NULL , [RNumber] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL , [RTID] [int] NOT NULL , [RSID] [int] NOT NULL , [RUID] [int] NOT NULL , [REmotion] [int] NULL , [RTopic] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL , [RContents] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL , [RTime] [datetime] NULL , [RClickCount] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
CREATE TABLE [dbo].[BBSSection] ( [SID] [int] NOT NULL , [SName] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL , [SMasterID] [int] NOT NULL , [SStatement] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL , [SClickCount] [int] NULL , [STopicCount] [int] NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[BBSTopic] ( [TID] [int] NOT NULL , [TNumber] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL , [TSID] [int] NOT NULL , [TUID] [int] NOT NULL , [TReplyCount] [int] NULL , [TEmotion] [int] NULL , [TTopic] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL , [TContents] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL , [TTime] [datetime] NULL , [TClickCount] [int] NULL , [TFlag] [int] NOT NULL , [TLastClickT] [datetime] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
CREATE TABLE [dbo].[BBSUsers] ( [UID] [int] NOT NULL , [UName] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL , [UPassword] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL , [UEmail] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL , [UBirthday] [datetime] NULL , [USex] [bit] NOT NULL , [UClass] [int] NULL , [UStatement] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL , [URegDate] [datetime] NOT NULL , [UState] [int] NULL , [UPoint] [int] NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[BBSReply] ADD CONSTRAINT [PK_BBSpely] PRIMARY KEY CLUSTERED ( [RID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[BBSSection] ADD CONSTRAINT [PK_BBSSection] PRIMARY KEY CLUSTERED ( [SID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[BBSTopic] ADD CONSTRAINT [DF_BBSTopic_TFlag] DEFAULT (1) FOR [TFlag], CONSTRAINT [PK_BBSTopic] PRIMARY KEY CLUSTERED ( [TID] ) ON [PRIMARY] , CONSTRAINT [CK_BBSTopic] CHECK ([TLastClickT] > [TTime] and [TLastClickT] < getdate()) GO
ALTER TABLE [dbo].[BBSUsers] ADD CONSTRAINT [DF_BBSUsers_YPassword] DEFAULT (8888) FOR [UPassword], CONSTRAINT [DF_BBSUsers_UEmail] DEFAULT ('P@P.COM') FOR [UEmail], CONSTRAINT [DF_BBSUsers_USex] DEFAULT (1) FOR [USex], CONSTRAINT [DF_BBSUsers_UClass] DEFAULT (1) FOR [UClass], CONSTRAINT [DF_BBSUsers_UState] DEFAULT (1) FOR [UState], CONSTRAINT [DF_BBSUsers_UPoint] DEFAULT (20) FOR [UPoint], CONSTRAINT [PK_BBSUsers] PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] , CONSTRAINT [CK_BBSUsers] CHECK ([UEMail] like '%@%'), CONSTRAINT [CK_BBSUsers_1] CHECK (len([UPassword]) >= 6) GO
ALTER TABLE [dbo].[BBSReply] ADD CONSTRAINT [FK_BBSpely_BBSSection] FOREIGN KEY ( [RSID] ) REFERENCES [dbo].[BBSSection] ( [SID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_BBSpely_BBSTopic] FOREIGN KEY ( [RTID] ) REFERENCES [dbo].[BBSTopic] ( [TID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_BBSpely_BBSUsers] FOREIGN KEY ( [RUID] ) REFERENCES [dbo].[BBSUsers] ( [UID] ) GO
ALTER TABLE [dbo].[BBSSection] ADD CONSTRAINT [FK_BBSSection_BBSUsers] FOREIGN KEY ( [SMasterID] ) REFERENCES [dbo].[BBSUsers] ( [UID] ) GO
ALTER TABLE [dbo].[BBSTopic] ADD CONSTRAINT [FK_BBSTopic_BBSSection] FOREIGN KEY ( [TSID] ) REFERENCES [dbo].[BBSSection] ( [SID] ), CONSTRAINT [FK_BBSTopic_BBSUsers] FOREIGN KEY ( [TUID] ) REFERENCES [dbo].[BBSUsers] ( [UID] ) GO
相关视频
相关阅读 lols9总决赛时间赛程 lol英雄联盟s9总决赛日期赛程Soundflower怎么使用 Soundflower使用教程lolskin怎么卸载 lolskin删除方法介绍lolskin闪退怎么办 lolskin闪退解决方法墨池镇配置要求高吗 墨池镇Truberbrook配置要求一览Truberbrook怎么调中文 墨池镇Truberbrook中文设置方法地铁逃离dlss怎么开 地铁逃离dlss光追开启方法小米9se和小米9哪个好 小米9se和小米9有什么区别
热门文章 oracle10g安装图解(wi
最新文章
数据库流行度排行2019oracle10g安装图解(wi
SQL2008全部数据导出导入两种方法SQL2005新建复制“找不到存储过程 错误:28Dos远程登录mysql数据库详细图文教程mysql怎么开启远程登录功能
人气排行 mysql自动定时备份数据库的最佳方法-支持wiVisual Foxpro 6.0安装向导图文教程SQL Server 2008 安装图文教程SQL2008全部数据导出导入两种方法SQL 2000/2005/2008 的收缩日志方法,和清理mysql出 Can't connect to MySQL server onoracle10g安装图解(win7)sql2005安装图解_(sql server2005)安装教程
查看所有2条评论>>