一、实验目的及要求

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）更新视图是指通过视图来插入、删除和修改数据。因为视图是不实际存储数据的虚表，因此，对视图的更新最终要转换为对表的更新通过视图更新表中的数据，有的情况下是可行的，有的情况下，则因为对视图的更新不能转换成对表的更新而无法进行。