MySql语句哪错了
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 (20) , –邮件
Ubirthday DATETIME , –生日
Usex BIT NOT NULL , –性别
Uclass INT , –级别(几星级)
Uremark VARCHAR (20) , –备注
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 (‘火云邪神’,’hxy123′,’hyxs@163.com’, ‘1988-7-9′,’快…’,20000)
GO
select * from bbsUsers
=======================================
/*新建bbsSection(版块表)表*/
if exists(select * from sysobjects where name = ‘bbsSection’)
drop table bbsSection
GO
create table bbsSection
(
SID INT IDENTITY (1, 1) NOT NULL ,
Sname VARCHAR (32) NOT NULL ,
UID INT NOT NULL ,
Sprofile VARCHAR (255) NULL ,
SclickCount INT NULL ,
StopicCount INT NULL
)
/*bbsSection表约束*/
alter table bbsSection add constraint PK_SID primary key (SID) –主键约束
alter table bbsSection add constraint DF_SclickCount default(0) for SclickCount –默认约束
alter table bbsSection add constraint DF_StopicCount default(0) for StopicCount –默认约束
alter table bbsSection add constraint FK_SmasterID foreign key (UID) references bbsUsers(UID)
=============================================================
/*新建bbsTopic(主贴表)表*/
if exists(select * from sysobjects where name = ‘bbsTopic’)
drop table bbsTopic
GO
create table bbsTopic
(
TID INT IDENTITY (1, 1) NOT NULL , –帖子编号
SID INT NOT NULL , –版块编号
UID INT NOT NULL ,