sql数据库实训.docx
《sql数据库实训.docx》由会员分享,可在线阅读,更多相关《sql数据库实训.docx(14页珍藏版)》请在冰豆网上搜索。
![sql数据库实训.docx](https://file1.bdocx.com/fileroot1/2022-12/8/cc843d5b-28e0-4134-9a68-dfd890933811/cc843d5b-28e0-4134-9a68-dfd8909338111.gif)
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;}
-->
用户名:
|
密码:
|
密码确认:
|
XX文库-让每个人平等地提升自我真实名:
|
性别:
男
女 |
email:
|
展开阅读全文
相关搜索
|