--创建数据库
use master
go
if exists(select * from sysdatabases where name='Shop')
drop database Shop
go
create database Shop
on primary
(
name='Shop_data',
filename='D:/Shop_data.mdf',
size=1mb
)
log on
(
name='Shop_log',
filename='D:/Shop_log.ldf',
size=1mb
)
go
-----------------------------
--角色表
create table roles
(
utId int primary key identity(1,1),
roleType varchar(16) --角色类型
)
insert into roles values('超级管理员')
insert into roles values('管理员')
insert into roles values('会员')
--管理员表
create table admins
(
aid int primary key identity(1,1),
uId int not null, --外键
rid int not null --所扮演的角色
)
GO
insert into users values('采购商','ping','123456','yes','what','公司叫什么呢?','陈先生'
,'深圳特区福田区','365315935@vip.qq.com','IC生产制造','www.goo.com','好介绍g gh ',
'不清楚','234543','234533235432','3453452','234432','123454325')
--用户表 19
create table users
(
uid int primary key identity(1,1),
userType varchar(15) not null, --用户类型
name varchar(20) not null, --用 户 名(长度在3-16个字符间(A-Z， a-z， 0-9,-和_字符，不能为为中文形式！)
password varchar(18) not null, --密 码： * (长度在6-16个字符间)
Issue varchar(20) not null, --密码提示问题： * (长度在3-20个字符间。如忘记密码,可根据此获得密码)
Answer varchar(20) not null, --密码提示答案： * (长度在3-20个字符间。如忘记密码,可根据此获得密码)
CompanyName varchar(60) not null, --公司名称： * (长度在50个字符间)
Contacts varchar(14) not null, --联 系 人：
City varchar(30) not null, --公司所在地
email varchar(20) not null, --电子邮件：
Belong varchar(20) not null, --所属行业： IC代理 IC经销商 IC信息服务 IC生产制造商 OEM生产商 研究所（院） 其他
httpAddress varchar(15) not null, --公司网址：
CompanyProfile varchar(1200) not null, --公司简介： 为了更好的宣传贵公司，请填写公司简介，最多1200字。
address varchar(80) not null, --街道地址： (至少10个字符，长度在10-80个字符间)
zip varchar(6) not null, --邮编：
phone varchar(15) not null, --电 话： （格式为：国家区号-城市区号-电话号码）
Fax varchar(20), --传 真：
msn varchar(10), --MSN
qq varchar(9) --QQ
)
go
-------------------------------------------
--信息表() 12
create table info
(
iid int primary key identity(1,1),
uid int not null, -- 询价人ID
ufid int not null, -- 被询价人ID
txt varchar(500) not null, -- 内容
Whether int, -- 是否查看
model varchar(30), -- IC型号
Manufacturers varchar(30), -- 厂家
LotNo varchar(25), -- 批号
Packaging varchar(30), -- 封装
Amount int, -- 数量
price money, -- 单价
Remarks varchar(100), -- 备注
createTime datetime -- 询价时间
)
select * from info where ufid=4 and Whether=0
alter table info
add constraint df_Whether default(0) for Whether --默认约束
-- 存储过程
--drop procedure Info_Fang
--drop procedure Info_Test
create procedure Info_Test
(
@ufid int,
@Whether varchar(100)
)
as
declare
@a int
set @a=0
select @a = count(*) from info where ufid=@ufid and Whether=@Whether
return @a
go
exec Info_Test @ufid=4,@Whether=0,@a=0
select count(*) from info where ufid=5 and Whether=0
select * from info
exec Info_SEL @ufid=2,@Whether=0
create procedure Info_Fang
(
@uid int,
@ufid int,
@txt varchar(100),
@model varchar(100),
@Manufacturers varchar(100),
@LotNo varchar(100),
@Packaging varchar(100),
@Amount int,
@price money,
@Remarks varchar(200),
@createTime datetime
)
as
insert into info(uid,ufid,txt,model,Manufacturers,
LotNo,Packaging,Amount,price,Remarks,createTime) values(
@uid,@ufid,@txt,@model,@Manufacturers,
@LotNo,@Packaging,@Amount,@price,@Remarks,getDate())
return @@rowcount
go
exec Info_Fang 4,5,'abcd','aaaa','bbbb','cccc','dddd',22,33.5,'eeee','2008-09-12 14:22'
exec Info_Fang 7,6,'abcd','aaaa','bbbb','cccc','dddd',22,33.5,'eeee','2008-09-12 14:22'
exec Info_Fang 4,2,'abcd','aaaa','bbbb','cccc','dddd',22,33.5,'eeee','2008-09-12 14:22'
exec Info_Fang 3,4,'abcd','aaaa','bbbb','cccc','dddd',22,33.5,'eeee','2008-09-12 14:22'
--发布需求
--需求表
create table demand
(
did int primary key identity(1,1),
uid int not null, -- 发布人ID
model varchar(30), -- IC型号
Manufacturers varchar(30), -- 厂家
LotNo varchar(25), -- 批号
Packaging varchar(30), -- 封装
Amount int, -- 数量
price money, -- 单价
Remarks varchar(100), -- 备注
sendTime datetime -- 发布时间
)
--drop table demand
insert into demand values(4,'maixiang','windwos','7788','99*88',100,22.2,'有的请速M',getDate())
select count(*) from Demand
select * from Demand
select max(did) from Demand
--删除300000到333305的数据
create procedure Demand_Delete
(
@No1 int,
@No2 int
)
as
declare
@a int,
@b int,
set b = 8000
begin
set @a=delete Demand where did between @a and @a+@b
end
go
delete Demand where did between 191 and 199
create procedure Info_Demand
(
@uid int,
@model varchar(100),
@Manufacturers varchar(100),
@LotNo varchar(100),
@Packaging varchar(100),
@Amount int,
@price money,
@Remarks varchar(200),
@sendTime datetime
)
as
insert into demand values(
@uid ,@model,@Manufacturers,@LotNo,@Packaging,@Amount,@price,@Remarks,@sendTime)
go
exec Info_Demand 5,'lkl','bbbb','cccc','eeee',23,2.3,'备注要不要','2008-09-11 16:22'
create table ask
{
aid identity(1,1) primary key,
iid int not null, -- 信息表
model varchar(30), -- 型号
txt varchar(150) not null, -- 报价内容
createTime dateTime not null -- 报价时间
}
select * from info
insert into info values(3,4,'来测试一下数据',0,'MaxSorry','MinKnow','4466','hwhw',20,15.5,'卖不卖',getDate())
insert into info values(3,5,'来测试一下数据','MaxSorry','MinKnow','4466','hwhw',20,15.5,'卖不卖',getDate())
insert into info values(5,6,'来测试一下数据','MaxSorry','MinKnow','4466','hwhw',20,15.5,'卖不卖',getDate())
insert into info values(6,3,'来测试一下数据','MaxSorry','MinKnow','4466','hwhw',20,15.5,'卖不卖',getDate())
insert into info values(5,2,'来测试一下数据','MaxSorry','MinKnow','4466','hwhw',20,15.5,'卖不卖',getDate())
select * from Products
select * from Products where pid in (1,3,4)
select * from info
/// <summary>
/// Method to make sure that user's inputs are not malicious
/// </summary>加密
/// <param name="text">User's Input</param>
/// <param name="maxLength">Maximum length of input</param>
/// <returns>The cleaned up version of the input</returns>
public static string InputText(string text, int maxLength) {
text = text.Trim();
if (string.IsNullOrEmpty(text))
return string.Empty;
if (text.Length > maxLength)
text = text.Substring(0, maxLength);
text = Regex.Replace(text, "[//s]{2,}", " "); //two or more spaces
text = Regex.Replace(text, "(<[b|B][r|R]/*>)+|(<[p|P](.|//n)*?>)", "/n"); //<br>
text = Regex.Replace(text, "(//s*&[n|N][b|B][s|S][p|P];//s*)+", " "); //
text = Regex.Replace(text, "<(.|//n)*?>", string.Empty); //any other tags
text = text.Replace("'", "''");
return text;
}
--产品表 10
create table Products
(
pid int primary key identity(1,1),
model varchar(20) not null, -- IC型号
uid int not null, -- 发布人
Manufacturers varchar(20) not null, -- 厂家
LotNo varchar(15) not null, -- 批号
Packaging varchar(20) not null, -- 封装
Amount int not null, -- 数量
price money not null, -- 单价
Remarks varchar(20) not null, -- 备注
createTime datetime not null -- 发布日期
)
select * from users
select * from Products where pid=2 or pid=4 or pid=7
-- 产品 降序 显示
SELECT Products.*, users.uid AS Expr1
FROM Products INNER JOIN
users ON Products.uid = users.uid
ORDER BY Expr1 DESC
select * from Products where model=null or model='MAX125CEAX+D'
insert into Products values('A3P250',2,'i know','0088','QFP144',1000,99.9,'数量/有限',getDate())
insert into Products values('CP3BT26Y98NEP',2,'idefaultow','7788','QFP144',999,66.6,'数量/无限',getDate())
select * from info
insert into info values(5,9,'来测试一下数据','MaxSorry','MinKnow','4466','hwhw',20,15.5,'卖不卖',getDate())
-- 约束
alter table users
add constraint ck_userType check(userType in('供应商','采购商'))
alter table users
add constraint fk_uId foreign key(uId) references admins(uId)
--admins主表, users外键表
alter table admins
add constraint fk_uId foreign key(uId) references users(uId)
alter table admins
add constraint fk_rid foreign key(rid) references roles(utId)
alter table products
add constraint fk_Products_uid foreign key(uid) references users(uid)
alter table users
add constraint fk_info foreign key(uid) references info(uid)
--信息表约束1
alter table info
add constraint fk_info foreign key(uid) references users(uid)
--信息表约束2
alter table info
add constraint fk_info2 foreign key(ufid) references users(uid)
--需求表
alter table demand
add constraint fk_did foreign key(uid) references users(uid)
--删除约束
-- alter table admins
-- drop constraint fk_uId
select * from users
select * from products
select * from admins
select * from roles
select * from info
-- drop table userType
-- drop table admins
-- drop table users
-- drop table products
-- drop table info
Rollback
insert into admins values(1,1)
insert into users values('供应商','ccc','1234','手机多少','13715300329','福双马技术有限公司','错先生','上海市','smqshao870@qq.com','IC信息服务','www.baidu.com','为了更好的宣传贵公司，请填写公司简介，最多1200字','岗厦村','123456','0755-42561235','15648256','514272870','365315935')
insert into users values('供应商','bbb','1234','手机多少','13715300329','福双马技术有限公司','对先生','北京市','smqshao870@qq.com','IC信息服务','www.baidu.com','为了更好的宣传贵公司，请填写公司简介，最多1200字','岗厦村','123456','0755-42561235','15648256','514272870','365315935')
insert into users values('采购商','aaa','1234','手机多少','13715300329','福双有限公司','哈先生','南宁市','smqshao@qq.com','IC信息服务','www.baidu.com','为了更好的宣传贵公司，请填写公司简介，最多1200字','岗厦村','123456','0755-42561235','15648256','514272870','365315935')
insert into users values('采购商','ddd','1234','手机多少','13715300329','福双马公司','欧阳先生','谬误市','smqshao870@qq.com','IC信息服务','www.baidu.com','为了更好的宣传贵公司，请填写公司简介，最多1200字','岗厦村','123456','0755-42561235','15648256','514272870','365315935')
insert into users values('采购商','eee','1234','手机多少','13715300329','技加达公司','晕先生','桂林市','smqshao870@qq.com','IC信息服务','www.baidu.com','为了更好的宣传贵公司，请填写公司简介，最多1200字','岗厦村','123456','0755-42561235','15648256','514272870','365315935')
insert into products values('MAX125CEAX+D',1,'MAXIM','07+','qfp100',888,112.5,'全新原装正品',getDate())
insert into products values('WAX876CEAX+D',2,'MAXIM','07+','qfp100',934,112.5,'全新原装正品',getDate())
insert into products values('WAX345CEAX+D',3,'MAXIM','07+','qfp100',374,112.5,'全新原装正品',getDate())
insert into products values('eAX125CEAX+D',4,'MAXIM','07+','qfp100',357,112.5,'全新原装正品',getDate())
insert into products values('SAX256CEAX+D',5,'MAXIM','07+','qfp100',123,112.5,'全新原装正品',getDate())
insert into products values('fAX155CEAX+D',6,'MAXIM','07+','qfp100',100,112.5,'全新原装正品',getDate())
insert into products values('DAX157CEAX+D',6,'MAXIM','07+','qfp100',999,112.5,'全新原装正品',getDate())
insert into products values('HAX223CEAX+D',4,'MAXIM','07+','qfp100',222,112.5,'全新原装正品',getDate())
insert into products values('YAX443CEAX+D',3,'MAXIM','07+','qfp100',147,112.5,'全新原装正品',getDate())
-------------------------------------------
--北京市 广东省 上海市 香港行政区 澳门行政区 台湾省 天津市 河北省 山西省 内蒙古自区 辽宁省 吉林省 黑龙江省 江苏省 浙江省 安徽省 福建省 江西省 山东省 河南省 湖北省 湖南省 广西自区 海南省 重庆市 四川省 贵州省 云南省 西藏自区 陕西省 甘肃省 青海省 宁夏自区 新疆自区 * (当国家为中国时需选择此两项)
--email本站的注册确认信、遗忘密码、邮件订阅和询/报价单等将发送到这个邮箱，务必输入您的最常用的邮件地址， 并检查输入是否正确
--(将在本站自动产生一个以上形式的二级域名地址以方便访问贵公司在本站的注册信息，建议输入和用户名相同的二级网站地址以方便记忆，长度在3-16个字符间(A-Z， a-z， 0-9,- 和 _ 字符，不能为为中文形式！ )
------------------------------存储过程分页---------------------------------------------------
/****** 对象: StoredProcedure [dbo].[PageList] 脚本日期: 09/05/2008 23:57:19 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[PageList]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
else
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder
else
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end
end
exec (@strSQL)
/// <summary>
/// 利用通用存储过程实现快速分页
/// </summary>
/// <param name="tblName">表名</param>
/// <param name="strGetFields">字段名,用逗号分隔</param>
/// <param name="fldName">排序字段</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="PageIndex">当前页</param>
/// <param name="doCount">返回记录总数, 非 0 值则返回</param>
/// <param name="OrderType">设置排序类型, 非 0 值则降序</param>
/// <param name="strWhere">查询条件 (注意: 不要加 where)</param>
/// <returns>返回DataTable</returns>
public DataTable Page_List(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere)
{
//tblName表名
//strGetFields 字段名,用逗号分隔
//fldName排序字段
//PageSize每页显示记录数
//PageIndex当前页
//doCount返回记录总数, 非 0 值则返回
//OrderType设置排序类型, 非 0 值则降序
//strWhere查询条件 (注意: 不要加 where)
//SqlDataAdapter rs = new SqlDataAdapter("PageList", conn);
//int AllCount = 0;
//AllCount = GetTableCount(tblName, strWhere);
SqlCommand myCommand = new SqlCommand("PageList", conn);
myCommand.CommandType = CommandType.StoredProcedure;//命令类型
myCommand.Parameters.Add(new SqlParameter("@tblName", SqlDbType.VarChar, 100));
myCommand.Parameters["@tblName"].Value = tblName;
myCommand.Parameters.Add(new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000));
myCommand.Parameters["@strGetFields"].Value = strGetFields;
myCommand.Parameters.Add(new SqlParameter("@fldName", SqlDbType.VarChar, 50));
myCommand.Parameters["@fldName"].Value = fldName;
myCommand.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int, 4));
myCommand.Parameters["@PageSize"].Value = PageSize;
myCommand.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int, 4));
myCommand.Parameters["@PageIndex"].Value = PageIndex;
myCommand.Parameters.Add(new SqlParameter("@doCount", SqlDbType.Int, 4));
myCommand.Parameters["@doCount"].Value = doCount;
myCommand.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.Int, 4));
myCommand.Parameters["@OrderType"].Value = OrderType;
myCommand.Parameters.Add(new SqlParameter("@strWhere", SqlDbType.VarChar, 1000));
myCommand.Parameters["@strWhere"].Value = strWhere;
DataTable dt = new DataTable("showClass");
SqlDataAdapter rs = new SqlDataAdapter(myCommand);
rs.Fill(dt);
myCommand.Dispose();
rs.Dispose();
return dt;
}
------------------------------------
--添加约束：
alter table tablename
add constraint pk_colname primary key(colname) --主建约束
alter table tablename
add constraint uq_colname unique (colname) --唯一约束
alter table tablename
add constraint df_colname default('地址不详')for colname --默认约束
alter table tablename
add constraint ck_colname check(colname between 12 and 15) --检查约束
alter table tablename
add constraint fk_colname foreign key(colname)references tablename(colname)--外建约束
alter table tablename --删除约束：
drop constraint 约束名
while(1=1)
begin
declare @exit int --判断是否还有外键约束
declare @constr_id varchar(200) --获取外键约束的ID
declare @table_id varchar(200) --获取设外键的表的ID
declare @table varchar(200) --获取设外键的表名
declare @constr_name varchar(100) --获取约束名
declare @str varchar(100) --删除表语句字符串
declare @drop_constr varchar(100) --删除约束字符串
declare @conn_str varchar(2000) --将删除语句连接起来的字符串
select top 1 @constr_id=id,@constr_name=name from sysobjects where type='f'
select @table_id=fkeyid from sysforeignkeys where constid=@constr_id
select @table=name from sysobjects where id=@table_id
select @exit=count(*) from sysobjects where type='f'
if(@exit!=0)
begin
set @str='alter table'
set @drop_constr='drop constraint'
set @conn_str=@str+' '+@table+' '+@drop_constr+' '+@constr_nameexec(@conn_str)
end
else
break
endif exists(select * from sysobjects where name='proc_drop_table' and type='p')
drop proc proc_drop_table
go
create proc proc_drop_table
@table_name varchar(200) --指定不想删除的表名
as
while(1=1)
begin
declare @str varchar(200)
declare @exit int
set @str='drop table'
select top 1 @str=@str+' '+name from sysobjects where type='u'
select @exit=count(*) from sysobjects where type='u'
if(@exit!=0)
exec(@str)
else
begin
break
return
end
end
goexec proc_drop_table a
-- 变量申明时,如果只指定VARCHAR,默认为1个字符
-- 与ORACLE不同的时,ORACLE需要显示指定一个长度.
/*删除视图*/
if exists(select * from sysobjects where name='V_RL_USER_FORM' and type='v')
drop view V_RL_USER_FORM
go
if exists(select * from sysobjects where name='v_urgency_user' and type='v')
drop view v_urgency_user
go/*删除存储过程*/if exists(select * from sysobjects where name='data' and type='p')
drop proc data
go
if exists(select * from sysobjects where name='exportCheckErr' and type='p')
drop proc exportCheckErr
go
if exists(select * from sysobjects where name='rpt' and type='p')
drop proc rpt
go
if exists(select * from sysobjects where name='rpt_net' and type='p')
drop proc rpt_net
go
557
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
