shopping.sql

用存储过程写建的数据库

--========================================创建数据库========================================

USE master
GO

--删除shopping数据库
IF EXISTS (SELECT * FROM sysdatabases WHERE NAME = 'shopping')
DROP DATABASE shopping
GO

--创建shopping数据库
CREATE DATABASE shopping
ON PRIMARY (
    NAME = 'shopping',--逻辑文件
    FILENAME = 'F:\Microsoft SQL Server\MSSQL\data\shopping.mdf',--实际文件名
    SIZE = 3,--初始容量
    MAXSIZE = 100,--最大容量
    FILEGROWTH = 2--自动增长量
)
log ON (
    NAME = 'shopping_log',--日志文件
    FILENAME = 'F:\Microsoft SQL Server\MSSQL\data\shopping_log.LDF',--实际文件名
    SIZE = 2,--初始容量
    MAXSIZE = 20,--最大容量
    FILEGROWTH = 15%--自动增长比例
)
GO

USE shopping
GO

--=========================================================================================



--======================================会员/管理员信息=====================================

--删除会员信息表
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[Users]')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE [dbo].[Users]
GO

--创建会员信息表
CREATE TABLE Users (
    [username] varchar(20) PRIMARY KEY,          --会员/管理员帐号
    [password] varchar(25) not null,             --会员/管理员密码
    [e_mail] varchar(30) not null,               --电子邮箱
    [id_card] varchar(18) not null,              --身份证号码
    [phone] varchar(20) not null,                --手机号码
    [sex] varchar(4) not null,                   --性别
    [question] varchar(50) not null,             --提示问题
    [answer] varchar(25) not null,             --提示答案
    [level] int not null DEFAULT 0               --会员等级
)
GO

--用户表插入触发器
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[trgInsertUsers]')
and objectproperty(ID,N'IsTrigger') = 1)
DROP TRIGGER trgInsertUsers
GO

CREATE TRIGGER trgInsertUsers
ON Users
FOR INSERT
AS

DECLARE @UserName varchar(20)--用户帐号
DECLARE @PassWord varchar(25)--用户密码
DECLARE @E_Mail varchar(30)--电子邮箱
DECLARE @c nvarchar(1)--记录@username的字符
DECLARE @i int
SET @i = 1

SELECT @UserName = Inserted.username,@PassWord = Inserted.[password],
@E_Mail = Inserted.e_mail
FROM Users,Inserted
WHERE Users.username = Inserted.username

WHILE @i <= len(@UserName)
BEGIN
    SET @c = substring(@UserName,@i,1)
    IF @c not like '[1-9]' and @c not like '[a-z]' and @c <> '_'
    BEGIN
        PRINT '账号必须是由字母、数字、下划线组成。'
        ROLLBACK TRANSACTION
        BREAK
    END
    SET @i = @i + 1
END

IF (len(@PassWord)<6 or len(@PassWord)>25)
BEGIN
    PRINT '密码必须在6~25个字符之间。'
    ROLLBACK TRANSACTION
END
GO

--【用户登录】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_Logon')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_Logon]
GO

CREATE PROC [dbo].[pro_Logon]
@UserName varchar(20),--用户帐号
@PassWord varchar(25)--用户密码
AS
BEGIN
    SELECT * FROM Users
    WHERE [username] = @UserName and [password] = @PassWord
END
GO

--【验证账号是否成重复】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_VerifyUser')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_VerifyUser]
GO

CREATE PROC [dbo].[pro_VerifyUser]
@UserName varchar(20)--用户帐号
AS
BEGIN
    SELECT * FROM Users WHERE [username] = @UserName
END
GO

--【用户注册】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_Register')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_Register]
GO

CREATE PROC [dbo].[pro_Register]
@UserName varchar(20),--用户帐号
@PassWord varchar(25),--用户密码
@E_Mail varchar(30),--电子邮箱
@ID_Card varchar(18),--身份证号码
@Phone varchar(20),--手机号码
@Sex varchar(4),--性别
@Question varchar(50),--提示问题
@Answer varchar(25)--提示答案
AS
BEGIN
    IF @UserName not in (SELECT username FROM Users)
    BEGIN
        BEGIN TRAN
        INSERT INTO Users ([username],[password],[e_mail],
        [id_card],[phone],[sex],[question],[answer])
        VALUES (@UserName,@PassWord,@E_Mail,@ID_Card,@Phone,
        @Sex,@Question,@Answer)

        IF @UserName not in(SELECT username FROM Users)
        BEGIN
            ROLLBACK TRAN
            RETURN 0--返回“0”插入失败
        END
        ELSE
        BEGIN
            COMMIT TRAN
            RETURN 1--返回“1”插入成功
        END 
    END
    ELSE
    BEGIN
        RETURN 0--返回“0”插入失败
    END
END
GO

--插入会员信息
BEGIN TRAN
    --插入管理员信息
    INSERT INTO Users ([username],[password],[e_mail],
    [id_card],[phone],[sex],[question],[answer],[level])
    VALUES ('admin','123456','[e-mail]','43310119871206053X',
    '15974201933','男','你最喜欢的休闲运动','漫步',1)

    --插入用户信息
    EXEC pro_Register 'hao','123456','haohao@163','43310119871206053X',
    '15974201933','男','你最喜欢的休闲运动','远足'
COMMIT TRAN
GO

--=========================================================================================



--=========================================商品主类=========================================

--删除商品主类表
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.MainSort')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE dbo.MainSort
GO 

--创建流水账号
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.f_NextMA'))
DROP FUNCTION dbo.f_NextMA
GO

CREATE FUNCTION f_NextMA()
RETURNS char(6)
AS
BEGIN
    RETURN(SELECT 'MA' + right(10001 + isnull(right(max(id),4),0),4)--以“MA”开头，后接4个数字
    FROM MainSort WITH (XLOCK,PAGLOCK))
END
GO

--创建商品主类表
CREATE TABLE MainSort (
    [id] char(6) PRIMARY KEY
    DEFAULT dbo.f_NextMA(),                      --主类编号
    [sort] varchar(20) UNIQUE not null,          --种类
)
GO

--【查询商品主类】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_SelectMainSort')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_SelectMainSort]
GO

CREATE PROC pro_SelectMainSort
AS
BEGIN
    DECLARE @SQL varchar(1000)
    SET @SQL = 'SELECT id,sort FROM MainSort ORDER BY id ASC'
    EXEC (@SQL)
END
GO

--【插入商品主类】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_MainSort')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_MainSort]
GO

CREATE PROC [dbo].[pro_MainSort]
@Sort varchar(20)
AS
BEGIN
    IF @Sort not in (SELECT sort FROM MainSort)
    BEGIN
        BEGIN TRAN
        INSERT INTO MainSort (sort) VALUES (@Sort)

        IF @Sort not in (SELECT sort FROM MainSort)
        BEGIN
            ROLLBACK TRAN
            RETURN 0--返回值为“0”插入失败
        END
        ELSE
        BEGIN
            COMMIT TRAN
            RETURN 1--返回值为“1”插入成功
        END
    END
    ELSE
    BEGIN
        RETURN 0--返回值为“0”插入失败
    END
END
GO

--插入商品主类
BEGIN TRAN
    EXEC pro_MainSort '水果'
    EXEC pro_MainSort '蔬菜'
    EXEC pro_MainSort '肉类'
COMMIT TRAN
GO
--=========================================================================================



--=========================================商品副类=========================================

--删除商品副类表
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.SecondarySort')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE dbo.SecondarySort
GO 

--创建流水账号
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.f_NextSE'))
DROP FUNCTION dbo.f_NextSE
GO

CREATE FUNCTION f_NextSE()
RETURNS char(6)
AS
BEGIN
    RETURN(SELECT 'SE' + right(10001 + isnull(right(max(id),4),0),4)
    FROM SecondarySort WITH (XLOCK,PAGLOCK))
END
GO

--创建商品副类表
CREATE TABLE SecondarySort (
    [id] char(6) PRIMARY KEY
    DEFAULT dbo.f_NextSE(),                      --副类编号
    [sort] varchar(20) UNIQUE not null,          --种类
    [mainid] char(6) REFERENCES MainSort(id)     --主类编号
)
GO

--【查询商品副类】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_SelectSecond')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_SelectSecond]
GO

CREATE PROC pro_SelectSecond
AS
BEGIN
    DECLARE @SQL varchar(1000)
    SET @SQL = 'SELECT * FROM SecondarySort'
    EXEC (@SQL)
END
GO

--【根据主类，查询相关的副类】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_MainOfSecond')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_MainOfSecond]