SQL Server 2008(数据库创建)

--检查存在数据库删除重建

IF EXISTS(SELECT * FROM sysdatabases WHERE name='StuInfo')
DROP DATABASE StuInfo

--建立数据库

CREATE DATABASE StuInfo
ON PRIMARY
(
  name='StuInfo_data',
  filename='D:/zhaoxu/DataBase/StuInfo_data.mdf',
  size=10MB,
  maxsize=unlimited,
  filegrowth=10%
)
LOG ON
(
  name='StuInfo_log',
  filename='D:/zhaoxu/DataBase/StuInfo_log.ldf',
  size=3MB,
  maxsize=100MB,
  filegrowth=1MB
)

GO

--建表StuInfo

USE StuInfo

--检查是否存在

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='StuInfo')
DROP TABLE StuInfo
--创建数据表
CREATE TABLE StuInfo
(
  StuID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
  StuName VARCHAR(50) NOT NULL CHECK(LEN(StuName) >= 2),
  StuAge INT NOT NULL CHECK(StuAge >= 18 AND StuAge <= 120),
  StuAddress VARCHAR(50) NULL DEFAULT('地址不详'),
  StuIDCard VARCHAR(18) NOT NULL CHECK(LEN(StuIDCard) = 15 OR LEN(StuIDCard) = 18),
  StuGender CHAR(2) NOT NULL
)

CREATE TABLE StudentInfo
(
  StuID INT IDENTITY(1,1) NOT NULL,
  StuName VARCHAR(50) NOT NULL ,
  StuAge INT NOT NULL ,
  StuAddress VARCHAR(50) NULL ,
  StuIDCard VARCHAR(18) NOT NULL ,
  StuGender CHAR(2) NOT NULL
)
--添加主键约束
ALTER TABLE StudentInfo
ADD CONSTRAINT PK_StudentInfo PRIMARY KEY(StuID),
    CONSTRAINT CK_StuAge CHECK(StuAge >= 18 AND StuAge <= 120),
    CONSTRAINT DF_StuAddress DEFAULT('地址不详') FOR StuAddress,
    CONSTRAINT UQ_StuIDCard UNIQUE(StuIDCard)

--添加检查约束

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='ScoreInfo')
DROP TABLE ScoreInfo
CREATE TABLE ScoreInfo
(
 ScoreID INT PRIMARY KEY NOT NULL,
 StuID INT NOT NULL,
 Score FLOAT NOT NULL
)

ALTER TABLE ScoreInfo
ADD CONSTRAINT FK_StudentInfo_ScoreInfo FOREIGN KEY(StuID) REFERENCES StudentInfo(StuID)

--创建登录账户

--添加windows账户
exec sp_grantlogin 'cs231/guoxipeng'
--添加SQLServer身份验证
exec sp_addlogin 'haoxiong','123'

USE StuInfo
exec sp_grantdbaccess 'haoxiong','haoxiongDBUser'

--  grant 权限 [on 表名] to 数据库用户名

grant create table to haoxiongDBUser
grant select,insert,update on StudentInfo to haoxiongDBUser