您的位置：首页 → 技术开发 → 数据库教程 → sqlsever函数集合 数学函数,系统函数

sqlsever函数集合 数学函数,系统函数

时间：2011/7/9 11:12:47来源：本站原创作者：清晨我要评论(2)

第 3 页 sqlserver应用开发
《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
上一页3
本文导航

相关视频

    没有数据

相关阅读 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)安装教程