SQL ´û¿î±ÏÒµÉúÐÅÏ¢¹ÜÀíϵͳÊý¾Ý¿âÉè¼Æ

USE MASTER
GO
--´´½¨Êý¾Ý¿âÎļþ´æ·ÅĿ¼
EXEC XP_CMDSHELL 'MKDIR D:\LOANSTUMIS'
IF EXISTS(SELECT *
from SYSDATABASES
WHERE NAME = 'LOANSTU')
DROP DATABASE LOANSTU
GO
--´´½¨Êý¾Ý¿â
CREATE DATABASE LOANSTU
ON
(
NAME = 'LOANSTU_DATA',
FILENAME = 'D:\LOANSTUMIS\LOANSTU_DATA.MDF',
SIZE = 10MB,
FILEGROWTH = 1MB,
MAXSIZE = 100MB
)
LOG ON
(
NAME = 'LOANSTU_LOG',
FILENAME = 'D:\LOANSTUMIS\LOANSTU_LOG.LDF',
SIZE = 10MB,
FILEGROWTH = 1MB,
MAXSIZE = 100MB
)
GO
USE LOANSTU
GO
--·µ»ØÓ°ÏìµÄÐÐÊýÌáʾ
SET NOCOUNT ON
--½¨±í
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_ADMIN')
DROP DATABASE TB_ADMIN
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_DLRZ')
DROP DATABASE TB_DLRZ
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_ZZJG')
DROP DATABASE TB_ZZJG
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_ZXXX')
DROP DATABASE TB_ZXXX
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_JTXX')
DROP DATABASE TB_JTXX
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_BYQX')
DROP DATABASE TB_BYQX
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_HTXX')
DROP DATABASE TB_HTXX
GO
--´´½¨¹ÜÀíÔ±ÐÅÏ¢±í
CREATE TABLE TB_ADMIN
(
ADMINID VARCHAR(15) PRIMARY KEY NOT NULL,
ADMINNAME VARCHAR(10) NOT NULL,
ADMINPASSWORD VARCHAR(20) DEFAULT('abc123') NOT NULL
)
--´´½¨µÇ½ÈÕÖ¾±í
CREATE TABLE TB_DLRZ
(
LOGID INT IDENTITY(1000000,1) PRIMARY KEY NOT NULL,
USERID VARCHAR(15) NOT NULL,
LOGTIME SMALLDATETIME DEFAULT(GETDATE()) NOT NULL,
IP VARCHAR(16)
)
--´´½¨×éÖ¯½á¹¹±í£¨ÔºÏµ¡¢×¨Òµ¡¢°à¼¶£©
CREATE TABLE TB_ZZJG
(
ORGID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
ORGNAME VARCHAR(30),
ORGPID INT
)
--´´½¨Ñ§ÉúÐÅÏ¢±í
CREATE TABLE TB_ZXXX
(
STUID VARCHAR(15) PRIMARY KEY NOT NULL,
STUNAME VARCHAR(10),
CLASSID INT NOT NULL,
STUPASSWORD VARCHAR(20) DEFAULT('123456') NOT NULL,
STUKH VARCHAR(20),
STUSEX CHAR(2) CHECK(STUSEX IN('ÄÐ','Å®')),