USE master;
GO
IF DB_ID (N'MicroblogDB') IS NOT NULL
--N '.... '代表是Unicode 字符串
/*
删除数据库 数据库名一般放在master的sysdatabases中。
方法一 用exists(存在)
use master
go
if exists(select * from sysdatabases where name = '数据库名')
drop database 数据库名
go
方法二 db_id存在sqlserver2000以后的版本
use master
go
if db_id(N'数据库名') is not
drop database 数据库名
go
*/
DROP DATABASE MicroblogDB;
GO
-- Get the SQL Server data path.
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute the CREATE DATABASE statement.
EXECUTE ('CREATE DATABASE MicroblogDB
ON
(
NAME = MicroblogDB_data
,FILENAME = ''' + @data_path + 'MicroblogDB_data.mdf''
,SIZE = 10MB
,MAXSIZE = 50MB
,FILEGROWTH = 15%
)
LOG ON
(
NAME = Microblog_log
,FILENAME = ''' + @data_path + 'MicroblogDB_log.ldf''
,SIZE = 5MB
,MAXSIZE = 25MB
,FILEGROWTH = 5MB
)'
);
GO
USE MicroblogDB;
GO
CREATE TABLE tbUser--用户表
(
UserID nvarchar(64)PRIMARY KEY,--用户ID,主键
Password nvarchar(64),--密码
Question nvarchar(64),--密码问题
Answer nvarchar(64),--密码问题答案
NickName nvarchar(64),--昵称
TrueName nvarchar(64),--真实姓名
Sex nvarchar(8) check (sex in ('男','女')),--性别
Province nvarchar(16),--省份
City nvarchar(16),--市
BlogAddress nvarchar(64),--博客地址
Email nvarchar(64),--电子邮件
QQ nvarchar(32),--QQ
MSN nvarchar(64),--MSN
SelfIntroduction nvarchar(1024),--自我介绍
)
ALTER TABLE tbUser ADD Regdate datetime default NULL --给表添加一个字段
INSERT INTO tbUser
(
UserID,
Password,
Question,
Answer,
NickName,
TrueName,
Sex,
Province,
City,
BlogAddress,
Email,
QQ,
MSN,
SelfIntroduction
)
VALUES
(
'lianqidi'/* UserID */,
'123456'/* Password */,
'我叫什么?'/* Question */,
'连齐俤?'/* Answer */,
'独钓寒江雪'/* NickName */,
'连齐俤'/* TrueName */,
'男'/* Sex */,
'福建省'/* Province */,
'福州市'/* City */,
'http://user.qzone.qq.com/89745607/'/* BlogAddress */,