在线音乐欣赏系统 之 数据库设计

USE [MusicOnline] GO /****** 对象: StoredProcedure [dbo].[pro__User_GetIdByName] 脚本日期: 04/27/2010 20:43:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: LWK -- Create date: 2010-04-17 -- Description: 获取用户ID -- Result: 正确返回ID值，错误返回0 -- ============================================= CREATE PROCEDURE [dbo].[pro__User_GetIdByName] @uName VARCHAR(128), @@uId INT output AS BEGIN SET NOCOUNT ON; if exists (SELECT * FROM [tb_UserInfo] WHERE [uName]=@uName) SELECT @@uId=[uID] FROM [tb_UserInfo] WHERE [uName]=@uName; else set @@uId = 0; return @@uId; END GO /****** 对象: StoredProcedure [dbo].[pro_User_Add] 脚本日期: 04/27/2010 20:43:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: LWK -- Create date: 2010-04-17 -- Description: 增加用户 -- Input： 用户名，密码，类名，昵称，E-Mail,生日 -- return: 成功返回用户Id,失败返回<0 返回-1,表示用户组不存在 -- ============================================= CREATE PROCEDURE [dbo].[pro_User_Add] @uName VARCHAR(128), @uPwd VARCHAR(256), @uGroupName VARCHAR(64), @uNiName VARCHAR(128) = NULL, @uEmail VARCHAR(128) = NULL, @uBirthday DATETIME = NULL AS BEGIN SET NOCOUNT ON; declare @@uGroupId INT; declare @uId INT; exec pro_UserGroup_GetIdByName @uGroupName, @@uGroupId output; if(@@uGroupId = 0) begin SELECT '错误:用户组不存在!'; return -1; end if exists (SELECT * FROM [tb_UserInfo] WHERE [uName]=@uName) begin SELECT '错误:该用户名已存在!'; return -2; end INSERT INTO [tb_UserInfo](uName,uPwd,uGroupId,uNiName,uEmail,uBirthday,uRegTime) VALUES (@uName,@uPwd,@@uGroupId,@uNiName,@uEmail,@uBirthday,GetDate()); if exists (SELECT * FROM [tb_UserInfo] WHERE [uName]=@uName) begin SELECT @uId=[uId] FROM [tb_UserInfo] WHERE [uName]=@uName; return @uId; end else begin Set @uId = 0; SELECT '数据库插入异常!请检查所填格式是否有错误!'; return -3; end END GO /****** 对象: StoredProcedure [dbo].[pro__UserGroup_GetIdByName] 脚本日期: 04/27/2010 20:43:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: LWK -- Create date: 2010-04-17 -- Description: 获取用户组ID -- Result: 正确返回ID值，错误返回0 -- ============================================= CREATE PROCEDURE [dbo].[pro__UserGroup_GetIdByName] @uGroupName VARCHAR(128), @@uGroupId INT output AS BEGIN SET NOCOUNT ON; if exists (SELECT * FROM [tb_UserGroup] WHERE [ugName]=@uGroupName) SELECT @@uGroupId=[ugId] FROM [tb_UserGroup] WHERE [ugName]=@uGroupName; else SET @@uGroupId = 0; return @@uGroupId; END GO /****** 对象: StoredProcedure [dbo].[pro_User_Modify] 脚本日期: 04/27/2010 20:43:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: LWK -- Create date: 2010-04-24 -- Description: 修改用户信息 -- Result: 成功>0,失败<0并返回错误信息 -- ============================================= CREATE PROCEDURE [dbo].[pro_User_Modify] @uId INT, @uName VARCHAR(128), @uPwd VARCHAR(256), @uGroupName VARCHAR(64), @uScroe INT, @uNiName VARCHAR(128) = NULL, @uEmail VARCHAR(128) = NULL, @uBirthday DATETIME = NULL AS BEGIN SET NOCOUNT ON; if( @uId=0 and @uName='' ) begin select '用户Id和用户名不能同时为空！'; return -1; end declare @@ugId INT; if exists( select * from [tb_UserInfo] where [uId]=@uId or [uName]=@uName ) begin exec pro_UserGroup_GetIdByName @uGroupName,@@ugId output if(@@ugId=0) begin select '用户组不存在'; return -2; end update [tb_UserInfo] set [uPwd] = @uPwd, [uGroupId] = @@ugId, [uNiName] = @uNiName, [uEmail] = @uEmail, [uBirthday] = @uBirthday, [uScore] = @uScroe where [uId]=@uId or [uName]=@uName; select * from [tb_UserInfo] where [uId]=@uId or [uName]=@uName return 1; end else begin select '该用户不存在'; return -3; end END GO /****** 对象: StoredProcedure [dbo].[pro__Common_Query] 脚本日期: 04/27/2010 20:43:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: lwk -- Create date: 2010-04-24 -- Description: 通用查询 -- Result: 正常返回1,失败返回<0 -- ============================================= CREATE PROCEDURE [dbo].[pro__Common_Query] @tb_name VARCHAR(50), @where VARCHAR(256), @topCount INT = 0, @DISTINCT BIT = 0, @orderBy VARCHAR(32) = '', @groupBy VARCHAR(32) = '', @having VARCHAR(30) = '', @ASCorDESC VARCHAR(5) = '' AS BEGIN SET NOCOUNT ON; if( @tb_name = '' or @tb_name = NULL) begin select '表名不能为空！'; return -1; end declare @queryWords VARCHAR(320); set @queryWords = 'select '; if(@DISTINCT <> 0 )set @queryWords = @queryWords + ' DISTINCT '; if(@topCount <> 0 ) set @queryWords = @queryWords + ' top ' + cast(@topCount as VARCHAR(10)); set @queryWords = @queryWords + ' * from ' + @tb_name; if(@where <> '') set @queryWords = @queryWords + ' where ' + @where; if(@groupBy <> '') begin set @queryWords = @queryWords + ' group by ' + @groupBy; if(@having <> '') set @queryWords = @queryWords + ' having ' + @having; end if(@orderBy <> '') begin set @queryWords = @queryWords + ' order by ' + @orderBy; if(@ASCorDESC <> '') set @queryWords = @queryWords + ' ' + @ASCorDESC; end print @queryWords EXECUTE (@queryWords); return 1; END GO /****** 对象: StoredProcedure [dbo].[pro_User_Query] 脚本日期: 04/27/2010 20:43:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: lwk -- Create date: 2010-04-24 -- Description: 查询用户信息 -- Result: 1 -- ============================================= CREATE PROCEDURE [dbo].[pro_User_Query] @condition VARCHAR(256), @topCount INT = 0 AS BEGIN SET NOCOUNT ON; declare @result int; exec @result = pro__Common_Query 'tb_UserInfo',@condition, @topCount ; return @result; END GO /****** 对象: StoredProcedure [dbo].[pro_User_Delete] 脚本日期: 04/27/2010 20:43:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: lwk -- Create date: 2010-04-24 -- Description: 删除用户 -- Result: 成功>0,失败<0 -- ============================================= CREATE PROCEDURE [dbo].[pro_User_Delete] @uId INT = 0, @uName VARCHAR(128) = '' AS BEGIN SET NOCOUNT ON; if(@uId=0 and @uName='' ) begin select '用户Id和用户名不能同时为空！'; return -1; end if not exists( select * from [tb_UserInfo] where [uId]=@uId or [uName]=@uName ) begin select '该用户不存在！'; return -2; end delete from [tb_UserInfo] where [uId]=@uId or [uName]=@uName; return 1; END GO /****** 对象: StoredProcedure [dbo].[pro_UserGroup_Add] 脚本日期: 04/27/2010 20:43:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: LWK -- Create date: 2010-04-24 -- Description: 添加用户组 -- Result: 成功返回ID,失败返回<0 -- ============================================= CREATE PROCEDURE [dbo].[pro_UserGroup_Add] @ugName VARCHAR(64) = '', @ugDscrp VARCHAR(128) AS BEGIN SET NOCOUNT ON; if (@ugName = '') begin select '用户名不能为空!'; return -1; end if exists ( select * from [tb_UserGroup] where [ugName]=@ugName ) begin select '该用户组存已存在!'; return -2; end insert into [tb_UserGroup](ugName,ugDscrp,ugCreateTime) values (@ugName,@ugDscrp,GetDate()); declare @ugId INT; select @ugId=[ugId] from [tb_UserGroup] where [ugName]=@ugName; return @ugId; END GO /****** 对象: Store