数据库实验完全版

 数据库课程设计报告

题  目：         Market数据库

院系名称：          

    专业名称：       

班    级：         

    学生姓名：            

学号（8位）：        

指导教师：            

设计起止时间：

实验1     SQL Server 2000 管理工具的使用和

创建数据库

1、实验目的

(1)熟悉SQL Sever 2000 的环境

(2)掌握企业管理起的基本使用方法，对数据库及其对象有基本了解，了解对SQL Sever 2000 进行配置的方法

(3)掌握查询分析器的基本使用方法以及在查询分析器中执行T—SQL语句的方法

(4)了解SQL Sever 2000数据库的逻辑结构和物理结构

(5)学会在企业管理器中创建数据库及查看数据库属性

(6)学会使用T—SQL语句创建数据库

2、试验内容

(1)学会使用企业管理器和查询分析器管理工具

(2)在查询分析器中使用T—SQL语句创建Market数据库

(3)查看物理磁盘目录，理解并分析SQL Sever 2000数据库的存储结构

(4)使用企业管理器查看数据库属性

(5)使用T—SQL语句或企业管理器对于建立的数据库进行修改和删除操作，并进一步查看物理磁盘目录

3、代码实现过程和具体操作

****用T—SQL语句创建Market数据库

Create database Market

On 

       (name = Market_Data,

    Filename = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL\\Market_Data.mdf',

    Size = 10,

    Maxsize  = 50,

    Filegrowth = 10%)

LOG ON

       (name = Market_Log,

    Filename = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL\\Market_Log.ldf',

    Size = 5,

    Maxsize  = 15,

    Filegrowth = 10%);

编译运行

物理磁盘目录

Market数据库的属性

用T—SQL语句删除Market数据库

删除Market数据库后。物理磁盘目录清空

实验2        定义表和数据库完整性

1、实验目的

(1)了解SQL Sever 2000的基本数据类型、空值的概念，以及表的结构特点

(2)学会使用T—SQL语句和企业管理器创建表结构和修改表结构

(3)学会使用SQL Sever 2000提供的数据完整性功能，并在创建表时定义表的数据完整性。通过实验进一步理解数据完整性的概念及分类

2、实验内容

用T—SQL语句在Market数据库中创建客户基本信息表Customers、货品信息表Goods、订单信息表Orders

3、代码实现和具体操作

*** 创建customers、goods、orders三张表

create table customers

(

    customerID int identity(1,1) primary key,

    cname varchar(8) not null,

    address varchar(50),

    city varchar(10),

    tel varchar(20) unique,

    company varchar(50),

    birthday datetime,

    type tinyint default 1,

);

create table goods

(

    goodsID  int constraint C1 primary key,

    goodsname varchar(20) not null,

    price money,

    description varchar(200),

    storage int,

    provider varchar(50),

    status tinyint default(0), 

);

create table orders

(

    orderID int identity(1,1) constraint C2 primary key,

    goodsID int not null references goods(goodsID) on delete cascade,

    customerID int not null foreign key(customerID) references customers (customerID) on delete no action,

quantity int not null constraint C3 check(quantity>0),

    ordersum money not null,

    orderdate datetime default(getdate()),

);

--ON DELETE NO ACTION

--指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则产生错误并回滚 DELETE。

实验3       表数据的插入、修改和删除

1、实验内容

掌握使用T—SQL语句和企业管理器对数据表进行插入、修改和删除数据的操作，并体会数据完整性约束的作用，加深对数据完整性及其约束性的理解。

    通过本实验，要熟练掌握INSERT、UPDATE、DELETE语句的语法和使用方法

2、实验内容

(1)分别使用T—SQL语句和企业管理器向数据库Market的各张表中输入一些记录

(2)使用T—SQL 语句进行插入、修改和删除记录的操作、

3、代码实现和具体操作

分别给三张表使用T-SQL语句在Market中输入数据

使用T-SQL语句在goods中输入数据

使用企业管理器向orders中输入数据

插入数据

向orders表中插入数据时违背数据完整性约束、因为customers表中不存在customerID为6的客户，数据不能正常插入

修改数据

将orders表中quantity数据类型改为int

删除数据

不能删除customers表、因为他在orders表中受到外键约束

实验4      数据库的简单查询和连接查询

1、实验目的

数据查询是数据库中最基本的操作，也是使用最频繁的操作，因此必须掌握SQL的查询语句（SELECT语句）的使用方法。本实验要求掌握简单表的数据查询、数据排序和多表操作时数据连接查询的操作方法

2、实验内容

(1)简单查询操作。该实验包括投影、选择条件表达式、数据排序等。

(2)连接查询操作。

3、代码实现和具体操作

(1)查找所有上海客户的信息

(2)查找所有商品的名称、库存量、价格及折价25%后的价格，并使用别名“Discount”标识折扣价。结构按价格由低到高排序

(3)查找商品名中包含“Computer”的商品的编号、名称及价格

(4)查找库存量大于100小于500的商品的编号、名称及价格

(5)查找2007年7月1日至2007年12 月31日期间，订购金额大于30000的所有订单的客户姓名，商品名称、单价、订货数量和订货金额

实验5       数据库的复杂查询

1、实验目的

进一步掌握SELECT语句的使用方法，通过实验能够熟练地使用SELECT语句。本实验要求掌握嵌套查询和统计查询的操作方法。

2、实验内容

    对数据库Market中的三张表进行复杂查询

3、代码实现和具体操作

(1)查找订单编号、商品编号和客户编号，要求按日期对订单进行分组，并只显示订货数量超过200的订单信息

(2)查找所有北京客户的订单信息，要求用不相关子查询完成

(3)查找不是天津客户的订单信息

(4)查找所有西安客户的相关信息以及他们的订单情况

实验6           索引和视图

1、实验目的

(1)理解索引和视图的概念

(2)掌握索引的使用方法

(3)掌握视图的定义和使用方法

2、实验内容

    在Market数据库中建立视图。然后在改视图上完成查询操作

3、代码实现和具体操作

(1)请为北京客户建立一个订单情况的视图，包括订单编号、商品名称、订货数量、客户编号、客户名称

create view Bj_orders(ordersID,goodsname,quantity,customerID, cname)     

as

    select orderID,goodsname,quantity,orders.customerID,cname

    from orders,customers,goods

    where customers.customerID = orders.customerID 

        and  goods.goodsID = orders.goodsID 

        and city='北京'

Bj_orders视图：

(2)针对上面的视图，完成查询，查找客户张三的所有订单信息

select *

from Bj_orders

where cname='张三'

实验7          存储过程

1、实验目的

理解存储过程的概念，掌握存储过程的使用方法。

2、实验内容

    存储过程是一系列预先编译好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在服务器上。

    创建用户自定义存储过程既可以在查询分析器中用SQL语句完成，也可以用企业管理器完成。在企业管理器中书写存储过程，是在“新建存储过程”对话框中的“文本”框中输入创建存储过程的SQL语句

    在Market数据库中创建存储过程的操作。并使用EXEC语句调用这些存储过程执行，观察他们的执行结构。

3、代码实现和具体操作

(1)编写存储过程shanghai，查看上海客户的信息。

create procedure shanghai

as

    select *

    from customers

    where city='上海'                                    

execute shanghai

(2)编写存储过程Goods，查看指定商品的信息，商品编号作为输入参数。

create procedure  goods_

@goodsID  char(20)

as

select *

from goods

where goodsID = @goodsID

execute goods_ @goodsID=2

(3)编写存储过程GoodsSum，查看指定客户的所有订单的订货总金额数，客户编号作为输入参数，订货总金额作为输出参数。

create procedure goodsum

@cnum   char(6),@snum  real  output

as

select @snum =sum(ordersum)

from orders

where customerID= @cnum

declare @snum real

execute goodsum 1,@snum output

select 'the result is: ',@snum

(4)编写存储过程insertGoods，向Goods表中插入一条记录

create procedure insertgoods

@goodnum char(6), @goodname char(20), @gprice real, @gdescription char(20),@gstroage int, @gprovider char(20),@gstatus int

as

insert into goods

values(@goodnum, @goodname, @gprice, @gdescription, @gstroage,@gprovider,@gstatus)

execute insertgoods 5,'电视',4000,'电视机',500,'华为',0

(5)创建一个存储过程Goods_Orders1，查看任何指定货品的订单情况，包括订单号、订货客户的姓名以及订货数量等（要使用输入参数）。

create procedure goods_orders1

@goods_name char(20),

@order_ID int output,@goods_customer_name char(20) output,@order_quantity int output

as

    select @order_ID=orderID,@goods_customer_name=cname,@order_quantity=quantity

    from customers,goods,orders

    where customers.customerID=orders.customerID and goods.goodsID=orders.goodsID

        and goodsname=@goods_name

(6)执行存储过程Goods_Orders1时，如果没有给出参数，则系统会报错，如果希望不给出参数时能查出所有货品的订单，则可以用默认参数值来实现。为此要求创建另一个存储过程Goods_Orders2来完成此功能。

create procedure goods_orders2

@goods_name char(20) = null,

@order_ID int output,@goods_customer_name char(20) output,@order_quantity int output

as

    if @goods_name is null

    begin

        select @order_ID=orderID,@goods_customer_name=cname,@order_quantity=quantity

        from customers,goods,orders

        where customers.customerID=orders.customerID and goods.goodsID=orders.goodsID

    end

    else

    begin

        select @order_ID=orderID,@goods_customer_name=cname,@order_quantity=quantity

        from customers,goods,orders

        where customers.customerID=orders.customerID and goods.goodsID=orders.goodsID

            and goodsname=@goods_name

    end

(7)创建一个存储过程Goods_OrderSum，来获得某个货品的订单总额（要求使用输入以及输出参数）。

create procedure goods_ordersum

@goods_num   char(6),@goods_order_num  real  output

as

select @goods_order_num =sum(ordersum)

from orders

where goodsID= @goods_num

实验8              触发器

1、实验目的

    学习触发器的使用，体会触发器执行的时机，加深触发器功能和作用的理解

2、实验内容

    在Market数据库中，完成创建触发器的操作，然后再相关的表上执行INSERT、UPDATE或DELETE语句，观察他们的执行结果

3、代码实现和具体操作

(1)在Customers表上建立删除触发器，实现Customers表和Orders表的级联删除。

create trigger customer_delete

on customers after delete

as

    delete from orders

        where customerID in

         (select customerID 

           from deleted)

先执行下列语句删除外键约束

alter table orders

drop constraint FK__orders__customer__7F60ED59

删除customers=2的客户信息

delete customers where customerID=2

执行前

执行后

(2)在Orders表上建立插入触发器，当想表中添加一条订货记录时，若订单中的商品状态为整理（Status=1），则不能插入该条记录。

create trigger goods_insert 

on orders after insert

as

    if (select status  

    from goods,inserted

    where goods.goodsID=inserted.goodsID)=1

        begin

            print '不能插入'

            Rollback transaction

        end

(3)在Orders上建立插入触发器，当添加订单时，减少Goods表中相应商品的库存量。

create trigger orders_insert 

on orders after insert

as

    update goods

        set  storage=storage - (select quantity                 

             from inserted)

      where  goodsID in 

         (select goodsID from inserted)

执行下面代码

insert 

into orders

values (4,3,50,10000,'')

(4)在Orders表上建立触发器，不允许对订单日期进行修改。

create trigger goods_update 

on orders after update

as

     if update(orderdate)

     begin

        print '不能更新日期'

        Rollback transaction

        End

update orders set orderdate='2010-01-01 00:00:00.000' where orderID=1and orderdate='2008-01-01 00:00:00.000'

(5)建立触发器，实现参照完整性约束，即若在Orders表中添加一条订单记录时，则该订单中的商品必须在Goods表中存在，否则不允许添加该记录。

create trigger orders_insert_ 

on orders after insert

as

    if( select count(*)  from goods, inserted

       where goods.goodsID=inserted.goodsid)=0

         begin

             print '该商品不存在'

             Rollback transaction

          end

调试情况，设计技巧及体会

参考文献