第二阶段.sql

SQL Server数据库设计和高级查询[SQL Advance]

USE master
GO
/*---检查是否已存在bbsDB数据库：查询master数据库中的系统表sysdatabases---*/
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'bbsDB')
	DROP DATABASE bbsDB
GO
EXEC xp_cmdshell 'mkdir D:\project'  --调用DOS命令创建文件夹，后续章节将讲解
/*-----建库--------*/
CREATE DATABASE bbsDB
 ON 
 (
  /*----数据文件的具体描述--*/
  NAME = 'bbsDB_data', --主数据文件的逻辑名
  FILENAME = 'D:\project\bbsDB_data.mdf' , --主数据文件的物理名
  SIZE = 10 MB,  --主数据文件初始大小
  FILEGROWTH = 20%   --主数据文件的增长率
 ) 
 LOG ON 
 (
  /*----日志文件的具体描述，各参数含义同上--*/
  NAME = 'bbsDB_log', 
  FILENAME = 'D:\project\bbsDB_log.ldf' ,
  SIZE = 1MB, 
  MAXSIZE = 20MB,
  FILEGROWTH = 10%
 )
GO

/*-----建表--------*/
USE bbsDB
GO
/*---检查是否已存在表bbsUsers：查询bbsDB数据库中的系统表sysobjects---*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='bbsUsers')
  DROP TABLE bbsUsers
GO
CREATE TABLE bbsUsers 
(
   UID INT IDENTITY (1,1) NOT NULL , --自动编号，标识列
   Uname VARCHAR(15) NOT NULL ,      --呢称
   Upassword VARCHAR (10)  ,         --密码
   Uemail VARCHAR (32)  ,            --邮件
   Ubirthday DATETIME  ,             --生日
   Usex BIT NOT NULL ,               --性别
   Uclass INT   ,                    --级别（几星级）
   Uremark VARCHAR (255) ,           --备注
   UregDate DATETIME NOT NULL ,      --注册日期
   Ustate INT NULL ,                 --状态（是否禁言等）
   Upoint INT NULL                   --积分（点数）
) 
GO
SELECT * FROM bbsUsers
GO

/*--------添加约束-------*/
ALTER TABLE bbsUsers ADD CONSTRAINT PK_UID PRIMARY KEY(UID) --主键
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Upassword  DEFAULT (8888) FOR Upassword --初始密码密码为8888
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Usex DEFAULT (1) FOR Usex  --性别默认为男（1）
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Uclass DEFAULT (1) FOR Uclass --级别默认为1星级
ALTER TABLE bbsUsers ADD CONSTRAINT DF_UregDate DEFAULT (getDate( )) FOR UregDate --注册日期默认为当前日期
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Ustate DEFAULT (0) FOR Ustate --状态默认为离线
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Upoint DEFAULT (20) FOR Upoint --默认积分20点
ALTER TABLE bbsUsers ADD CONSTRAINT CK_Uemail CHECK (Uemail LIKE '%@%') --必须包含'@'字符
ALTER TABLE bbsUsers ADD CONSTRAINT CK_Upassword CHECK (LEN(Upassword) >= 6) --至少6位
GO

--插入测试数据
INSERT INTO bbsUsers (Uname,Upassword,Uemail,Ubirthday,Uremark,Upoint) 
   VALUES ('火云邪神','hyxs007','[e-mail]', '1978-7-9','快…',20000)
GO

IF NOT EXISTS (SELECT * FROM MASTER.DBO.SYSLOGINS WHERE LOGINNAME = N'SYSADMIN')
BEGIN
	DECLARE @LOGINDB NVARCHAR(132), @LOGINLANG NVARCHAR(132) SELECT @LOGINDB = N'BANKDB', @LOGINLANG = N'简体中文'
	IF @LOGINDB IS NULL OR NOT EXISTS (SELECT * FROM MASTER.DBO.SYSDATABASES WHERE NAME = @LOGINDB)
		SELECT @LOGINDB = N'MASTER'
	IF @LOGINLANG IS NULL OR (NOT EXISTS (SELECT * FROM MASTER.DBO.SYSLANGUAGES WHERE NAME = @LOGINLANG) AND @LOGINLANG <> N'US_ENGLISH')
		SELECT @LOGINLANG = @@LANGUAGE
	EXEC SP_ADDLOGIN N'SYSADMIN', NULL, @LOGINDB, @LOGINLANG
END
GO

EXEC SP_ADDSRVROLEMEMBER N'LIRONGGU\ADMINISTRATOR', SYSADMIN
GO

CREATE TABLE DBO.bbsDBREPLY (
	RID INT IDENTITY (1, 1) 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.bbsDBSECTION (
	SID INT IDENTITY (1, 1) 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.bbsDBTOPIC (
	TID INT IDENTITY (1, 1) 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



ALTER TABLE DBO.bbsDBREPLY WITH NOCHECK ADD 
	CONSTRAINT PK_bbsDBREPLY PRIMARY KEY  CLUSTERED 
	(
		RID
	)  ON PRIMARY 
GO

ALTER TABLE DBO.bbsDBSECTION WITH NOCHECK ADD 
	CONSTRAINT PK_bbsDBSECTION PRIMARY KEY  CLUSTERED 
	(
		SID
	)  ON PRIMARY 
GO

ALTER TABLE DBO.bbsDBTOPIC WITH NOCHECK ADD 
	CONSTRAINT PK_bbsDBTOPIC PRIMARY KEY  CLUSTERED 
	(
		TID
	)  ON PRIMARY 
GO



ALTER TABLE DBO.bbsDBTOPIC ADD 
	CONSTRAINT CK_bbsDBTOPIC CHECK (TLASTCLICKT > TTIME AND TLASTCLICKT <= GETDATE()),
	CONSTRAINT CK_bbsDBTOPIC_1 CHECK (NOT(TTOPIC LIKE '%''%'))
GO


ALTER TABLE DBO.bbsDBREPLY ADD 
	CONSTRAINT FK_bbsDBREPLY_bbsDBSECTION FOREIGN KEY 
	(
		RSID
	) REFERENCES DBO.bbsDBSECTION (
		SID
	),
	CONSTRAINT FK_bbsDBREPLY_bbsDBTOPIC FOREIGN KEY 
	(
		RTID
	) REFERENCES DBO.bbsDBTOPIC (
		TID
	),
	CONSTRAINT FK_bbsDBREPLY_bbsUsers FOREIGN KEY 
	(
		RUID
	) REFERENCES DBO.bbsUsers (
		UID
	)
GO

ALTER TABLE DBO.bbsDBSECTION ADD 
	CONSTRAINT FK_bbsDBSECTION_bbsUsers FOREIGN KEY 
	(
		SMASTERID
	) REFERENCES DBO.bbsUsers (
		UID
	)
GO

ALTER TABLE DBO.bbsDBTOPIC ADD 
	CONSTRAINT FK_bbsDBTOPIC_bbsDBSECTION FOREIGN KEY 
	(
		TSID
	) REFERENCES DBO.bbsDBSECTION (
		SID
	),
	CONSTRAINT FK_bbsDBTOPIC_bbsUsers FOREIGN KEY 
	(
		TUID
	) REFERENCES DBO.bbsUsers (
		UID
	)
GO