accp4.0 S2 SQL Server课件和所有代码

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','hyxs@HotMail.com', '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