sql数据库实训.docx

sql数据库实训.docx

《sql数据库实训.docx》由会员分享，可在线阅读，更多相关《sql数据库实训.docx（14页珍藏版）》请在冰豆网上搜索。

sql数据库实训

一：

usemaster

createdatabasebbs1

usebbs1

createtable肖像

（肖像编号integeridentity（1,1）primarykey,

图片image,

描述char（10）

）

go

createtable心情图片

（心情图片编号integeridentity（1,1）primarykey,

图片image,

描述char（20）

）

go

createtable用户类别（

类别编号tinyintprimarykey,

类别名称varchar（20）notnull,

权限说明varchar（50）

）

go

createtable用户

（注册名varchar（20）primarykey,

密码varchar（20）,

性别bitcheck（性别in（1,0））default1notnull,

出生日期smalldatetimenotnull,

国家varchar（30）notnull,

省州varchar（20）,

市varchar（20）,

地址varchar（50）,

emailvarchar（30）,

爱好varchar（100）,

取回密码问题varchar（200）notnull,

取回密码问题答案varchar（50）notnull,

肖像integerreferences肖像（肖像编号）notnulldefault1,

用户类别tinyintreferences用户类别（类别编号）notnulldefault1,

主题量integer,

参与讨论的主题量integer,

登录次数integer,

上次登录时间smalldatetimedefaultgetdate（）,

IP地址char（15）

）

go

createtable版块

（

版块编号integerprimarykey,

版块名称varchar（30）notnull,

创建日期smalldatetimedefaultgetdate（）notnull,

版主注册名varchar（20）references用户（注册名）notnull,

版主任职日期smalldatetime

）

go

createtable帖子（

帖子编号bigintidentity（1,1）primarykey,

主题varchar（200）notnull,

内容text,

发帖人varchar（20）references用户（注册名）notnull,

心情图片编号integerreferences心情图片（心情图片编号）,

发帖时间smalldatetimedefaultgetdate（）,

版块编号integerreferences版块（版块编号）,

主题编号bigintreferences帖子（帖子编号）,

父贴编号bigintreferences帖子（帖子编号）,

层次integer,

人气integer,

回复数integer,

最后回复人varchar（20）references用户（注册名）notnull,

最后回复日期varchar（20）references用户（注册名）notnull,

是否被屏蔽bitdefault0,

是否精华bitdefault0

）

go

createtable短信

（短信编号bigintidentity（1,1）primarykey,

发送者varchar（20）references用户（注册名）notnull,

短信标题varchar（100）notnull,

短信内容varchar（600）,

接收者varchar（20）references用户（注册名）notnull,

接收者已查看bitdefault0,

发送日期smalldatetimedefaultgetdate（）,

发送者删除标志bitdefault0,

接收者删除标志bitdefault0,

是否发送标志bitdefault0

）

二：

1、usemaster

createdatabaseckgl

go

useckgl

createtable仓库（仓库号char（3）primarykey,

城市varchar（30）notnull,

面积decimal（10,3）check（面积>0）

）

go

createtable职工（职工号char（4）primarykey,

姓名char（8）notnull,

性别bitdefault1,

仓库号char（3）references仓库（仓库号）,

工资decimal（8,2）check（工资>0）

）

go

createtable供应商（供应商号char（3）primarykey,

供应商名varchar（50）notnull,

地址varchar（100）notnull

）

go

createtable订购单（订购单号char（4）primarykey,

职工号char（4）references职工（职工号）,

供应商号char（3）references供应商（供应商号）,

订购日期smalldatetimedefaultgetdate（）,

金额money）

2、

insertinto仓库values（'WH1','武汉',1250）

insertinto仓库values（'WH2','武汉',250）

insertinto仓库values（'WH3','广州',2500）

insertinto仓库values（'WH4','上海',2000）

insertinto仓库values（'WH6','北京',2700）

insertinto仓库values（'WH8','北京',700）

insertinto职工values（'E001','王理',1,'WH1',1250）

insertinto职工values（'E002','李度',1,'WH4',2250）

insertinto职工values（'E004','李宁',0,'WH3',3050）

insertinto职工values（'E005','李小双',1,'WH1',850）

insertinto职工values（'E006','陈晨',0,'WH8',5850）

insertinto职工values（'E008','张三',0,'WH2',5850）

insertinto职工values（'E009','张缇',0,'WH4',5850）

insertinto职工values（'E010','宫新',1,'WH3',5850）

insertinto供应商values（'S01','自强工贸公司','北京市朝阳区东长安街3211号'）

insertinto供应商values（'S02','新星玩具厂','沈阳市铁西区皇冈街512号'）

insertinto供应商values（'S03','布来卡电子厂','武汉市武昌区盐湖路12号'）

insertinto供应商values（'S04','重果仪器设备公司','武汉市汉口解放大道125号'）

insertinto供应商values（'S06','纯迪机电公司','重庆市奉节县春风大道125号'）

insertinto订购单values（'P001','E002','S03','2000.3.5',15435.63）

insertinto订购单values（'P002','E001','S04','2000.5.5',20000.00）

insertinto订购单values（'P003','E001','S03','2000.1.5',30000.00）

insertinto订购单values（'P004','E002','S06','2001.11.5',60000.00）

insertinto订购单values（'P005','E001','S06','2001.7.5',30000.00）

insertinto订购单values（'P006','E008','S01','2002.7.5',80000.00）

insertinto订购单values（'P007','E006','S01','2002.7.5',80000.00）

insertinto订购单values（'P008','E005','S03','2002.7.5',110000.00）

insertinto订购单values（'P009','E004','S02','2002.7.15',120000.00）

insertinto订购单values（'P010','E009','S04','2002.7.15',125000.00）

insertinto订购单values（'P011','E001','S01','2003.1.15',125000.00）

insertinto订购单values（'P012','E002','S06','2003.1.15',128000.00）

insertinto订购单values（'P013','E001','S02','2004.6.15',128000.00）

insertinto订购单values（'P014','E009','S03','2004.8.15',128000.00）

insertinto订购单values（'P015','E010','S02','2005.8.15',130000.00）

insertinto订购单values（'P016','E004','S01','2005.8.5',135000.00）

3、

（1）查询职工“张三”的工资；

select工资from职工where姓名='张三'

（2）查询在“上海”的仓库的信息;

select*from仓库where城市='上海'

（3）查询供应商为公司的供应商信息；

select*from供应商where供应商名like'%公司'

（4）查询各员工的工资总额、平均工资、最高工资、最低工资；

selectsum（工资）,avg（工资）,max（工资）,min（工资）from职工

（5）查询仓库总数和总面积；

selectcount（*）,sum（面积）from仓库

（6）查询有职工工资超过5000元的仓库个数；

selectcount（distinct仓库号）from职工where工资>2500

（7）查询在各城市的仓库数及其总面积；

select城市,count（*）,sum（面积）from仓库groupby城市

（8）查询有哪些城市的仓库面积之和大于500平方米（例如：

武汉有三个仓库，面积和为850m2）；

select城市from仓库groupby城市havingsum（面积）>500

（9）查询各个仓库中工资多于1200元的职工个数;

select仓库号,count（*）from职工where工资>1200groupby仓库号

（10）查询向供应商“S03”发过订单的职工号和他所在的仓库号;

select职工号,仓库号from职工where职工号in（select职工号from订购单where供应商号='S03'）

（11）查询职工“张三”所签发的所有订单的订单号及其订购日期；

select订购单号,订购日期from订购单,职工where职工.职工号=订购单.职工号and姓名='张三'

（12）查询和职工E001和E005都签订过订单的供应商信息;

select*from供应商where供应商号in（select供应商号from订购单where职工号='E001'）and供应商号in（select供应商号from订购单where职工号='E005'）

（13）查询供应商“S03”的历年的订购单个数和订购总金额，并按年份升序排列；（提示：

使用groupbyyear（订购日期））

selectyear（订购日期）,count（*）,sum（金额）from订购单where供应商号='S03'groupbyyear（订购日期）orderbyyear（订购日期）

（14）查询哪些城市至少有一个仓库的职工的工资低于800元；

selectdistinct城市from仓库where仓库号in（select仓库号from职工where工资<800）

（15）查询所有职工的工资都多于1210元的仓库的信息；

select*from仓库where仓库号notin（select仓库号from职工where工资<1210）

（16）查询和职工”E004”挣同样多工资的所有职工；

select职工号,姓名from职工where工资=（select工资from职工where职工号='E004'）and职工号!

='E004'

（17）查询每个职工经手的具有最高总金额的订购单信息,并按职工号升序排列。

select*from订购单outerTablewhere金额>=all（select金额from订购单innerTablewhereinnertable.职工号=outerTable.职工号）orderby职工号

（18）查询给所有供应商都发过订单的员工的信息。

select*from职工Ewherenotexists（select*from供应商Swhere供应商号notin（select供应商号from订购单where职工号=E.职工号and供应商号=S.供应商号））

（19）为仓库号为“W01”的仓库增加一名职工“张三”，其工资为1000，其职工号为“E025”；

insertinto职工values（'E025','张三',1,'W01',1000）

（20）将工资在1000元以下的职工的工资增加10%，在1000元以上的增加8%；

update职工set工资=工资*1.08where工资>=1000

update职工set工资=工资*1.10where工资<=1000

（21）将上一年度签订的订购单的总金额位居前3名的职工的工资增加500元；

update职工set工资=工资+500where职工号in（selecttop3职工号from订购单groupby职工号orderbysum（金额）desc）

（22）将供应商”S03”的名称改为”鹏达电子元件公司”，地址该位“武汉市解放大道234号”；

update供应商set供应商名='鹏达电子元件公司',地址='武汉市解放大道234号'where供应商号='S03'

（23）删除职工“E001”在2001年5月12日签发的订购单；

deletefrom订购单where职工号='E001'and订购日期='2001.5.12'

（24）删除最近五年内没有签发订单的客户的订购单信息以及供应商资料；

deletefrom订购单where供应商号notin（select供应商号from订购单whereyear（getdate（））-year（订购日期）<5）

deletefrom供应商where供应商号notin（select供应商号from订购单whereyear（getdate（））-year（订购日期）<5）

4、createviewvTotalValueas

select职工号,sum（金额）from订购单groupby职工号

5、

CREATEFUNCTIONdbo.ifincrease（@supplierIDchar（3））

RETURNSbitAS

BEGIN

declare@value1money,@value2money--用来存储上一条记录和当前记录的年度总金额

declare@retbit

set@ret=1

declareannualCurcursorfor

selectsum（金额）from订购单where供应商号=@supplierIDgroupbyyear（订购日期）orderbyyear（订购日期）

openannualCur

fetchnextfromannualCurinto@value1

while@@Fetch_status=0begin

fetchnextfromannualCurinto@value2

if@value2<@value1begin

set@ret=0--若当前年度的总金额小于上一年度，则为非递增，返回0

break

end

set@value1=@value2

end

closeannualCur

deallocateannualCur

return@ret

END

--查询

select供应商号,dbo.ifIncrease（供应商号）from供应商

--其他的使用例子

select结果=dbo.ifincrease（'S01'）--测试一

select结果=casedbo.ifincrease（'S01'）--测试二

when1then'是'

when0then'否'

end

6.

createprocedureDeleteAndSave

@supplierIDchar（3）

as

begin

ifexists（select*fromsysobjectswherextype='U'andname='被删订购单'）

insert被删订购单select*from订购单where供应商号=@supplierID

else

select*into被删订购单from订购单where供应商号=@supplierID

deletefrom订购单where供应商号=@supplierID

end

go

--测试例子

executeDeleteAndSave'S02'

executeDeleteAndSave'S04'

7.方法一：

用可视化界面：

在“约束表达式”输入框中输入：

工资between500and800000

在“默认值”输入框中输入：

getdate（）

方法二：

用SQL语句：

altertable职工addconstraintsalrangecheck（工资between500and800000）

createdefaulttodayasgetdate（）

go

execsp_bindefault'today','订购单.订购日期'

三、

1.注册信息输入页面代码（部分信息）register.htm：

DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.01Transitional//EN"

"http:

//www.w3.org/TR/html4/loose.dtd">

--

.style5{color:

#FFFF00;}

-->

用户名：