--检查存在数据库删除重建
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