sql数据库实训.docx

1、sql数据库实训一：use mastercreate database bbs1use bbs1create table 肖像(肖像编号 integer identity(1,1) primary key ,图片 image,描述 char(10)gocreate table 心情图片(心情图片编号 integer identity(1,1) primary key,图片 image,描述 char(20)gocreate table 用户类别(类别编号 tinyint primary key,类别名称 varchar(20) not null,权限说明 varchar(50)gocreate

2、 table 用户(注册名 varchar(20) primary key,密码 varchar(20),性别 bit check(性别 in (1,0) default 1 not null,出生日期 smalldatetime not null,国家 varchar(30) not null,省州 varchar(20),市 varchar(20),地址 varchar(50),email varchar(30),爱好 varchar(100),取回密码问题 varchar(200) not null,取回密码问题答案 varchar(50) not null,肖像 integer ref

3、erences 肖像(肖像编号) not null default 1,用户类别 tinyint references 用户类别(类别编号) not null default 1,主题量 integer,参与讨论的主题量 integer,登录次数 integer,上次登录时间 smalldatetime default getdate(),IP地址 char(15)gocreate table 版块(版块编号 integer primary key,版块名称 varchar(30) not null,创建日期 smalldatetime default getdate() not null,版

4、主注册名 varchar(20) references 用户(注册名) not null,版主任职日期 smalldatetime)gocreate table 帖子(帖子编号 bigint identity(1,1) primary key,主题 varchar(200) not null,内容 text,发帖人 varchar(20) references 用户(注册名) not null,心情图片编号 integer references 心情图片(心情图片编号),发帖时间 smalldatetime default getdate(),版块编号 integer references 版

5、块(版块编号),主题编号 bigint references 帖子(帖子编号),父贴编号 bigint references 帖子(帖子编号),层次 integer,人气 integer,回复数 integer,最后回复人 varchar(20) references 用户(注册名) not null,最后回复日期 varchar(20) references 用户(注册名) not null,是否被屏蔽 bit default 0,是否精华 bit default 0)gocreate table 短信( 短信编号 bigint identity(1,1) primary key,发送者 v

6、archar(20) references 用户(注册名) not null,短信标题 varchar(100) not null,短信内容 varchar(600),接收者 varchar(20) references 用户(注册名) not null,接收者已查看 bit default 0,发送日期 smalldatetime default getdate(),发送者删除标志 bit default 0,接收者删除标志 bit default 0,是否发送标志 bit default 0)二：1、use mastercreate database ckglgouse ckglcreat

7、e table 仓库(仓库号 char(3) primary key,城市 varchar(30) not null,面积 decimal(10,3) check(面积0)gocreate table 职工(职工号 char(4) primary key,姓名 char(8) not null,性别 bit default 1,仓库号 char(3) references 仓库(仓库号),工资 decimal(8,2) check(工资0)gocreate table 供应商(供应商号 char(3) primary key,供应商名 varchar(50) not null,地址 varch

8、ar(100) not null)gocreate table 订购单(订购单号 char(4) primary key,职工号 char(4) references 职工(职工号),供应商号 char(3) references 供应商(供应商号),订购日期 smalldatetime default getdate(),金额 money)2、insert into 仓库 values(WH1,武汉,1250)insert into 仓库 values(WH2,武汉,250)insert into 仓库 values(WH3,广州,2500)insert into 仓库 values(WH4

9、,上海,2000)insert into 仓库 values(WH6,北京,2700)insert into 仓库 values(WH8,北京,700)insert into 职工 values(E001,王理,1,WH1,1250)insert into 职工 values(E002,李度,1,WH4,2250)insert into 职工 values(E004,李宁,0,WH3,3050)insert into 职工 values(E005,李小双,1,WH1,850)insert into 职工 values(E006,陈晨,0,WH8,5850)insert into 职工 valu

10、es(E008,张三,0,WH2,5850)insert into 职工 values(E009,张缇,0,WH4,5850)insert into 职工 values(E010,宫新,1,WH3,5850)insert into 供应商 values(S01,自强工贸公司,北京市朝阳区东长安街3211号)insert into 供应商 values(S02,新星玩具厂,沈阳市铁西区皇冈街512号)insert into 供应商 values(S03,布来卡电子厂,武汉市武昌区盐湖路12号)insert into 供应商 values(S04,重果仪器设备公司,武汉市汉口解放大道125号)in

11、sert into 供应商 values(S06,纯迪机电公司,重庆市奉节县春风大道125号)insert into 订购单 values(P001,E002,S03,2000.3.5,15435.63)insert into 订购单 values(P002,E001,S04,2000.5.5,20000.00)insert into 订购单 values(P003,E001,S03,2000.1.5,30000.00)insert into 订购单 values(P004,E002,S06,2001.11.5,60000.00)insert into 订购单 values(P005,E001

12、,S06,2001.7.5,30000.00)insert into 订购单 values(P006,E008,S01,2002.7.5,80000.00)insert into 订购单 values(P007,E006,S01,2002.7.5,80000.00)insert into 订购单 values(P008,E005,S03,2002.7.5,110000.00)insert into 订购单 values(P009,E004,S02,2002.7.15,120000.00)insert into 订购单 values(P010,E009,S04,2002.7.15,125000.

13、00)insert into 订购单 values(P011,E001,S01,2003.1.15,125000.00)insert into 订购单 values(P012,E002,S06,2003.1.15,128000.00)insert into 订购单 values(P013,E001,S02,2004.6.15,128000.00)insert into 订购单 values(P014,E009,S03,2004.8.15,128000.00)insert into 订购单 values(P015,E010,S02,2005.8.15,130000.00)insert into

14、订购单 values(P016,E004,S01,2005.8.5,135000.00)3、(1)查询职工“张三”的工资；select 工资 from 职工 where 姓名=张三(2)查询在“上海”的仓库的信息;select * from 仓库 where 城市=上海(3)查询供应商为公司的供应商信息；select * from 供应商 where 供应商名 like %公司(4)查询各员工的工资总额、平均工资、最高工资、最低工资；select sum(工资),avg(工资),max(工资),min(工资) from 职工(5)查询仓库总数和总面积；select count(*),sum(面

15、积) from 仓库(6)查询有职工工资超过5000元的仓库个数；select count(distinct 仓库号) from 职工 where 工资2500(7)查询在各城市的仓库数及其总面积；select 城市,count(*),sum(面积) from 仓库 group by 城市(8)查询有哪些城市的仓库面积之和大于500平方米（例如：武汉有三个仓库，面积和为850m2）；select 城市 from 仓库 group by 城市 having sum(面积)500(9)查询各个仓库中工资多于1200元的职工个数;select 仓库号,count(*) from 职工 where 工

16、资1200 group by 仓库号(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

17、 职工号=E001) and 供应商号 in(select 供应商号 from 订购单 where 职工号=E005)(13)查询供应商“S03”的历年的订购单个数和订购总金额，并按年份升序排列；（提示：使用 group by year(订购日期)）select year(订购日期),count(*),sum(金额) from 订购单 where 供应商号=S03 group by year(订购日期) order by year(订购日期)(14)查询哪些城市至少有一个仓库的职工的工资低于800元；select distinct 城市 from 仓库 where 仓库号 in (select

18、 仓库号 from 职工 where 工资800)(15)查询所有职工的工资都多于1210元的仓库的信息；select * from 仓库 where 仓库号 not in (select 仓库号 from 职工 where 工资=all(select 金额 from 订购单 innerTable where innertable.职工号=outerTable.职工号 ) order by 职工号(18)查询给所有供应商都发过订单的员工的信息。select * from 职工 E where not exists(select * from 供应商 S where 供应商号 not in (s

19、elect 供应商号 from 订购单 where 职工号=E.职工号 and 供应商号=S.供应商号) )(19)为仓库号为“W01”的仓库增加一名职工“张三”，其工资为1000，其职工号为“E025”；insert into 职工 values(E025,张三,1,W01,1000)(20)将工资在1000元以下的职工的工资增加10%，在1000元以上的增加8%；update 职工 set 工资=工资*1.08 where 工资=1000update 职工 set 工资=工资*1.10 where 工资=1000(21)将上一年度签订的订购单的总金额位居前3名的职工的工资增加500元；up

20、date 职工 set 工资=工资+500 where 职工号 in (select top 3 职工号 from 订购单 group by 职工号 order by sum(金额) desc )(22)将供应商”S03”的名称改为”鹏达电子元件公司”，地址该位“武汉市解放大道234号”；update 供应商 set 供应商名=鹏达电子元件公司,地址=武汉市解放大道234号 where 供应商号=S03(23)删除职工“E001”在2001年5月12日签发的订购单；delete from 订购单 where 职工号=E001 and 订购日期=2001.5.12(24)删除最近五年内没有签发订

21、单的客户的订购单信息以及供应商资料；delete from 订购单 where 供应商号 not in (select 供应商号 from 订购单 where year(getdate()-year(订购日期)5 )delete from 供应商 where 供应商号 not in (select 供应商号 from 订购单 where year(getdate()-year(订购日期)5 )4、create view vTotalValue asselect 职工号,sum(金额) from 订购单 group by 职工号5、CREATE FUNCTION dbo.ifincrease(s

22、upplierID char(3)RETURNS bit ASBEGINdeclare value1 money,value2 money -用来存储上一条记录和当前记录的年度总金额declare ret bitset ret=1declare annualCur cursor forselect sum(金额) from 订购单 where 供应商号=supplierID group by year(订购日期) order by year(订购日期)open annualCurfetch next from annualCur into value1while Fetch_status=0

23、beginfetch next from annualCur into value2if value2value1 beginset ret=0 -若当前年度的总金额小于上一年度，则为非递增，返回0breakendset value1=value2endclose annualCurdeallocate annualCurreturn retEND-查询select 供应商号,dbo.ifIncrease(供应商号) from 供应商-其他的使用例子select 结果=dbo.ifincrease(S01) -测试一select 结果=case dbo.ifincrease(S01) -测试二

24、when 1 then 是when 0 then 否end6.create procedure DeleteAndSavesupplierID char(3)asbeginif exists(select * from sysobjects where xtype=U and name=被删订购单)insert 被删订购单 select * from 订购单 where 供应商号=supplierIDelseselect * into 被删订购单 from 订购单 where 供应商号=supplierIDdelete from 订购单 where 供应商号=supplierIDendgo-测

25、试例子execute DeleteAndSave S02execute DeleteAndSave S047. 方法一：用可视化界面：在“约束表达式”输入框中输入：工资 between 500 and 800000在“默认值”输入框中输入：getdate()方法二：用SQL语句：alter table 职工 add constraint salrange check (工资 between 500 and 800000)create default today as getdate()goexec sp_bindefault today,订购单.订购日期三、1.注册信息输入页面代码（部分信息）register.htm：无标题文档用户名：密码：密码确认：XX文库 - 让每个人平等地提升自我真实名：性别：男女email:td height=27 colsp