#数据库:超市销售系统的数据库建立与查询实验
一、实验目的及要求1.掌握数据库、表、视图建立、修改、删除的命令和操作;2.掌握单表查询的命令和操作;3.掌握连接查询、嵌套查询的命令和操作;4.掌握数据库更新的操作命令。二、实验设备及环境1.PC机一台;2.SQL Server数据库管理系统。三、实验内容1.构建超市销售数据库。2.建立5个超市销售数据库的基本表;3.填写必要的数据;4.设计数据库查询操作的命令;5.设计数据库更新的操作命令;6
一、实验目的及要求
1.掌握数据库、表、视图建立、修改、删除的命令和操作;
2.掌握单表查询的命令和操作;
3.掌握连接查询、嵌套查询的命令和操作;
4.掌握数据库更新的操作命令。
二、实验设备及环境
1.PC机一台;
2.SQL Server数据库管理系统。
三、实验内容
1.构建超市销售数据库。
2.建立5个超市销售数据库的基本表;
3.填写必要的数据;
4.设计数据库查询操作的命令;
5.设计数据库更新的操作命令;
6.设计必要的视图
四、实验过程与结果
1.建立数据库
2.设计数据库表
(1)会员表
(2)商品表
(3)销售单表
(4)销售单明细表
(5)员工表
3.设计数据库关系图
4.设计查询命令(不少于10种类型)
4.1单表查询
(1)查询在柜台号为2上操作的所有员工编号及会员号。
命令:select 票号,员工编号,会员号
from 销售单表
where 柜台号 ='2'
结果:
(2)查询员工中大于等于25岁的员工信息。
命令:select 员工姓名,员工编号,性别,年龄
from 员工表
where 年龄>=25
结果:
(3)查询所有积分小于600的姓周的会员。
命令:select *
from 会员表
where like '周%' and 积分< 600结果:
结果:
4.2连接查询
(1)等值内连接:商品表,销售单明细表和销售单表的连接。
命令:select *
from 销售单明细表,销售单表,商品表
where 销售单明细表.票号=销售单表.票号 and 销售单明细表.商品编号=商品表.商品编号
结果:
(2)等值外连接:商品表和销售单明细表的连接。
命令:select *
from 销售单明细表 inner join
商品表 on 销售单明细表.商品编号=商品表.商品编号
结果:
(3)在连接的基础上查询:查询姓名为魏丽的员工完成的任务,并按会员号排序。
命令:select 员工姓名,票号,会员号
from 员工表,销售单表
where 员工表.员工编号=销售单表.员工编号 and 员工姓名= '魏丽'
order by 票号 desc
结果:
4.3嵌套查询
(1)通过员工表在销售单表中查询姓名为魏丽的员工工作过的柜台号,票号和会员号。
命令:select 柜台号,票号,会员号
from 销售单表
where 员工编号 in (select 员工编号 from 员工表 where 员工姓名='魏丽')
结果:
(2)在会员表中查询积分小于400的会员的姓名,会员号以及积分。
命令:select 会员号,姓名,积分
from 会员表
where 积分 <all( select 积分
from 会员表
where 积分>400)
结果:
4.4基于派生表的查询
(1)集合查询-并运算:查询购买了商品编号为000003或000004的物品的票号。
命令:select 票号
from 销售单明细表
where 商品编号 = '000003'
union
select 票号
from 销售单明细表
where 商品编号 = '000004'
结果:
(2)集合查询-交运算:查询既购买了商品编号为000001又购买了商品编号为000003的票号。
命令:select 票号
from 销售单明细表
where 商品编号 = '000001'
intersect
select 票号
from 销售单明细表
where 商品编号 = '000003'
结果:
(3)集合查询-差运算:查询购买了商品编号为000004的商品但没购买商品编号为000005的商品的票号。
命令:select 票号
from 销售单明细表
where 商品编号 = '000004'
except
select 票号
from 销售单明细表
where 商品编号 = '000005'
结果:
5.设计数据库更新命令(删除、修改、插入每种类型不少于2个)
5.1删除
(1)从销售单明细表中删除数量大于5的信息。
命令:delete
from 销售单明细表
where 数量>5
结果:
(2)从销售单明细表中删除票号大于等于107的信息。
命令:delete
from 销售单明细表
where 票号>='107'
结果:
5.2修改
(1)将会员号为0001的会员的已用积分修改为500.
命令:update 会员表
set 已用积分=500
where 会员号 = '0001'
结果:
(2)将所有员工的年龄都加一岁。
命令:update 员工表
Set 年龄=年龄+1
结果:
5.3插入
(1)向会员表中插入一行
命令:insert 会员表(会员号, 姓名, 积分, 已用积分)
values(0011, '孙时尔', 33, 20 );
结果:
(2)向员工表中插入一行。
命令:insert 员工表(员工编号,员工姓名,性别,年龄,类别)
values ( 05,'张三' ,'男',30,'收银员')
结果:
6.设计视图(不少于3个)
(1) 建立一个明细表与其票号的销售单明细表,销售单表的对照表。
命令: create view 明细表(票号,商品编号,售价,数量,柜台号,员工编号,时间
as
select 销售单明细表.票号, 商品编号,售价,数量,柜台号,员工编号,时间
from 销售单表,销售单明细表
where 销售单明细表.票号=销售单表.票号
结果:
(2)建立一个反映会员剩余积分的视图
命令:create view 剩余积分(会员号,姓名,剩余积分)
as
select 会员号,姓名,积分-已用积分
from 会员表
结果:
(3)创建一个小票单,反应会员的购物信息。
命令:create view 小票单(票号,会员号,商品编号,时间,金额)
as
select 销售单表.票号,销售单表.会员号,商品编号,销售单表.时间,(售价*数量) as 金额
from 销售单表,销售单明细表
where 销售单表.票号=销售单明细表.票号
结果:
五、实验总结
5.1建立数据库
(1)数据库的特点:数据结构化。数据库面向全组织,具有整体的结构化。 描述数据时不仅描述数据本身, 还描述数据之间的联系。 数据的共享性高,冗余度低,易扩充。数据库从整体角度看待和描述数据,数据面向整个系统,可被多个用户共享,减少了数据冗余,节约了存储空间,避免了数据之间的不一致性(数据的不一致性指同一数据不同拷贝的值不一样)。数据库由数据库管理系统统一管理和控制。数据独立性高。
5.2设计数据库表
(1)数据库的逻辑模型是数据库系统的核心和基础。组成要素包括:数据结构。
数据库的组成对象以及对象之间联系的描述。是对系统静态特性的描述;
数据操作。数据库中的各种对象执行的操作的集合。是对系统动态特性的描述;
完整性约束。一组完整性规则。 用以限定符合数据模型的数据库状态以及状态的变化,以保证数据的正确、有效、相容。
(2)关系的主要性质:列是同质的。即同一列中的数据,其数据类型相同。分量(列)必须取原子值。列的次序无所谓。行的顺序无所谓。任意两行不能完全相同。
5.3数据库查询
(1)如果一个查询涉及两个或两个以上的表中的数据,可采用连接查询命令。
(2)步骤:1.将两个或若干个表连接起来,形成一个中间结果表。
2 .在上述基础上,依据单表查询知识进行查询。
(3) 等值连接:连接一般通过一个表的外码与另一个表的主码之间的关系进行。
左外连接就好像为右边的表增加一个“万能”行,该行全部由空值组成,它可以和左边表中的所有不满足连接条件的元组进行连接。在本例中,如果某学生的元组与选修表中的任何一个元组都不能连接,就与该万能行连接。
右外连接的写法: right outer join
左、右外连接的写法:full outer join
(4)嵌套查询: 一个查询嵌套在另一个查询的where子句或having短语的条件中。
父查询(外查询)与子查询(内查询) :上层查询称为父查询。下层查询称为子查询。
注意:1) 子查询必须用括号括起来。
2) 子查询中不能使用 order by 子句(也不需用)。
不相关子查询:子查询的条件中不依赖于父查询。这种子查询只执行一次。
带有any、all的运算符:( 运算符 <any : 只要小于结果集合中的某个值就为真。 <all : 只有小于结果集合中的所有值才为真。)
( =,>,<,>=,<=,<>,!>,!<)+ any
( =,>,<,>=,<=,<>,!>,!<)+ all
exists : 代表存在。带有该运算符的子查询不返回任何数据,只产生“真”、“假”值。使用存在量词exists后,若内层查询结果非空,则为真,否则,为假。所以子查询中的列表中用 ‘*’ 即可。
not exists与其判断结果相反。
(5)每个select命令的结果都是一个表。每个表都是一个集合。表中的一行就是一个集合元素。所以,可以对多个select命令的执行结果进行集合运算。
运算符:
并:union
交:intersect
差:expect
5.4数据库修改
(1)语句格式:
update 表名(只允许针对一个表)
set 列名1=表达式1,列名2=表达式2....
where 条件
5.5数据库插入
(1)列名表可省,但值的顺序要与表中列的顺序一致。
(2)但如果约束条件不允许该列取空值的话,此插入就是非法的,拒绝执行。
5.6数据库删除
(1)命令格式:
delete
from 表名(只允许针对一个表)
where 条件
(2)使用insert、update、delete命令,可将实际中的数据变化反映到数据库中。
5.7视图
(1) 视图是从一个或几个表或已经建立的视图中, 通过select 命令导出的表。它与表不同,是虚表。每个视图都对应一个select命令,也称子查询。子查询可以很复杂,但不允许有order by语句。
(2)数据库中只存放视图的定义(即select命令),不存放视图对应的数据。从视图中取数据,实际上是从表中取数据。视图中的数据就是select命令查询(3)视图一经定义,就可以和表一样被查询、删除,也可以在视图上定义新的视图。但对视图的更新有一定的限制。出的数据。所以,表中的数据发生变化,从视图中查询出来的数据也随之变化。
(4)视图名后的列表省略,表示使用子查询的列名。
(5)更新视图是指通过视图来插入、删除和修改数据。因为视图是不实际存储数据的虚表,因此,对视图的更新最终要转换为对表的更新通过视图更新表中的数据,有的情况下是可行的,有的情况下,则因为对视图的更新不能转换成对表的更新而无法进行。
更多推荐
所有评论(0)