《sqlserver应用开发》
1。返回限制行
select top 5 name...
2.列名 is null
使用表达式，函数，
3.在查询中使用函数；
1。字符串函数
部分常用的字符串函数
函数名 描述
charindex 用来寻找一个指定的字符串在另一个字符串中的起始位置
select charindex('ACCP','My Accp course',1); 返回4
len 长度 Lower 小写 upper 大写
ltrim 清除字符左边空格
Rtrim 清除字符右边空格
right 从字符串左边返回指定数目的字符
select right('买卖顺要和',3); 返回：顺要和
replace 替换一个字符串的字符 select replace('莫乐可切.杨可','可','兰'); 返回：莫乐兰切.杨兰
2.日期函数
getdate 取今天的日期 select getdate();
dateadd select dateadd(mm,4,'01/01/99')
datename 日期中指定日期部分的字符串形式 selectdatename(dw,'01/01/2000');返回：Saturday
datepart 日期中指定日期部分的整数形式 select datepart(day,'01/15/2000');返回： 15
3。数学函数
ABC绝对值
ceiling >=最小整数
floor <=最大整数
power 幂
power(5,2) 返回：25
Round 四舍五入为指定精度 Round(43.5645,1); 返回：43.5
sign 正数返回+1，负数返回-1，0返回0； sign(-43)返回：-1
sort(9) 平方根
返回：3
4。系统函数
convert(varchar(5),12345)转变数据类型 返回：字符串12345
current_user 返回当前用户的名字
datalength 返回指定表达式的字节数
host_name 返回当前用户登录的计算机名字
system_user 返回当前用户登录的用户名称
user_name 从给定用户ID返回用户名
4练习
1。把密码中的o变0，i变成1（好区别，使用函数replace）
2.编号为B01计算机坏了，请通过查询得到这台机器最近一次上机的卡号：？
3。查询24小时之内上机的人员姓名
4。查卡号第三位和第七位是‘AB’的人员(like '00[0-9][0-9]AB%')
check约束（属性中设置）
5.如何得到上一次插入的identity值(即ID自增长，不知道插入的是什么值)
查sqlserver联机从书(标识值：上一次插入的)
6.分析一个BBS系统（重要。书，117~131）
1。本月灌水的人
注册时间少于一个月，发贴量大于1500的用户
select id,count(*) from topic where DataAdd(Month,1,TTime)>GetDate() Group
by id having count(*)>1500
2.本日，本周用户发贴数排名；本日，本周版块发贴榜
本日 getDate() between(ttime+'0:0:0') and (ttime+'23:59:59')
本周 datepart(week,getDate())=datepart(week,ttime)
当天在本年第几周 日期在本年第几周 P128待续。。。。。。
《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
