WEB数据库原理与应用-学习情境3(1).doc

WEB数据库原理与应用-学习情境3(1) 作者： 日期：21 个人收集整理 勿做商业用途学习情境3 数据库应用与操作情境描述: 本情境主要让学生掌握常用的数据库管理系统，熟悉标准的TSQL语句，能够掌握常用的数据库管理系统（Access 2003、SQL Server 2005、MySQL 5。1、Oracle 10g、IBMDB2）。能够根据数据表结构实现数据库系统，能够进行常用的数据操作。本情境参考学时40学时。教学目标:1、 能力要求u 熟练掌握标准的T-SQL语言.u 掌握常用的数据库管理系统及其基本操作。2、 知识目标u 掌握标准的TSQL语言.u 掌握Access 2003数据库及其应用.u 掌握SQL Server 2005数据库及其应用.u 掌握MySQL 5.1 For Windows 数据库及其应用.u 掌握Oracle 10g数据库及其应用。u 掌握在虚拟机中安装Windows 2003 操作系统u 了解IBM DB2 数据库及其应用。3、 素质目标u 锻炼学生自主学习、举一反三的能力.u 培养学生操作和使用各种数据库的能力。工作任务：任务1 TSQL标准语言及其应用 任务2 Access 2003数据库及其应用 任务3 SQL Server 2005数据库及其应用 任务4 Oracle 10g 数据库及其应用 任务5 MySQL 5。1 数据库及其应用任务6 DB2 数据库及其应用任务7 练习与实践教学策略与教学方法总体考虑 讲授法、案例驱动法、现场演示法、操练法教学手段 多媒体课件、案例、实训设备、工具和材料 计算机，因特网、Access2003、 SQL Server 2005、MySQL 5。1、Oracle 10g、DB2 9。7教学效果评价 作业(10%)、任务考核（50）、实训（40%)参考资料李春葆;金昌;曾平.数据库原理与应用：基于SQL Server 2005.北京：清华大学出版社 200901（澳）威利 等著，武欣 等译。PHP和MySQL Web开发。北京：机械工业出版社 2005。06孙风栋。Oracle 10g数据库基础教程.北京:电子工业出版社 200907任务1 TSQL标准语言及其应用SQL是英文Structured Query Language的缩写，意思为结构化查询语言.SQL语言的主要功能就是同各种数据库建立联系，进行沟通。按照ANSI（美国国家标准协会)的规定，SQL被作为关系型数据库管理系统的标准语言。SQL语句可以用来执行各种各样的操作，例如更新数据库中的数据，从数据库中提取数据等。目前，绝大多数流行的关系型数据库管理系统，如Oracle， Sybase, Microsoft SQL Server， Access等都采用了SQL语言标准。虽然很多数据库都对SQL语句进行了再开发和扩展，但是包括Select、Insert、Update、Delete、Create，以及Drop在内的标准的SQL命令仍然可以被用来完成几乎所有的数据库操作。一个典型的关系型数据库通常由一个或多个被称作表格的对象组成.数据库中的所有数据或信息都被保存在这些数据库表格中。数据库中的每一个表格都具有自己唯一的表格名称，都是由行和列组成,其中每一列包括了该列名称，数据类型，以及列的其它属性等信息，而行则具体包含某一列的记录或数据。熟悉TSQL 的表创建、数据查询、更新记录、常用函数的语言操作.活动1 表创建和删除1、表的创建SQL语言中的create table语句被用来建立新的数据库表格.create table语句的使用格式如下:create table tablename （column1 data type,column2 data type， column3 data type);如果用户希望在建立新表格时规定列的限制条件,可以使用可选的条件选项:create table tablename (column1 data type constraint,column2 data type constraint,column3 data type constraint)；举例如下，创建客户信息表(bClient）：create table bClient(User_ID varchar(50）， User_Name varchar(30), User_Sex char(4）， User_Birthday datetime, User_Type varchar(50), User_Address varchar（100)， User_Tel varchar(20）；简单来说，创建新表格时，在关键词create table后面加入所要建立的表格的名称,然后在括号内顺次设定各列的名称，数据类型，以及可选的限制条件等。注意,所有的SQL语句在结尾处都要使用“;”符号。使用SQL语句创建的数据库表格和表格中列的名称必须以字母开头，后面可以使用字母，数字或下划线,名称的长度不能超过30个字符.注意，用户在选择表格名称时不要使用SQL语言中的保留关键词,如Select， create, Insert等，作为表格或列的名称。数据类型用来设定某一个具体列中数据的类型。例如，在姓名列中只能采用varchar或char的数据类型，而不能使用number的数据类型。SQL语言中较为常用的数据类型为：char(size）:固定长度字符串,其中括号中的size用来设定字符串的最大长度。Char类型的最大长度为255字节。number(size):数字类型，其中数字的最大位数由size设定。Date：日期类型。number(size,d）：数字类型,size决定该数字总的最大位数，而d则用于设定该数字在小数点后的位数。最后，在创建新表格时需要注意的一点就是表格中列的限制条件。所谓限制条件就是当向特定列输入数据时所必须遵守的规则.例如,unique这一限制条件要求某一列中不能存在两个值相同的记录，所有记录的值都必须是唯一的。除unique之外，较为常用的列的限制条件还包括not null和primary key等。not null用来规定表格中某一列的值不能为空。primary key则为表格中的所有记录规定了唯一的标识符。create table bClient（User_ID varchar(50） not NULL unique， User_Name varchar(30） not NULL， User_Sex char（4)， User_Birthday datetime, User_Type varchar(50）, User_Address varchar（100）， User_Tel varchar（20);alter table bClient add constraint PK_ User_ID primary key （User_ID）； - User_ID主键约束2、表的删除在SQL语言中使用drop table命令删除某个表格以及该表格中的所有记录。drop table命令的使用格式为：drop table tablename；例如：drop table bClient;如果用户希望将某个数据库表格完全删除,只需要在drop table命令后输入希望删除的表格名称即可。drop table命令的作用与删除表格中的所有记录不同。删除表格中的全部记录之后，该表格仍然存在，而且表格中列的信息不会改变.而使用drop table命令则会将整个数据库表格的所有信息全部删除。3、表的修改ALTER语句：一个表格被建立之后，利用ALTER语句，我们可以去修改表格的字段设计。ALTER TABLE table ADD COLUMN field type(size）CONSTRAINT index|CONSTRAINT multifieldindex|DROP COLUMN fieldCONSTRAINT indexnameTable 欲被ALTER的表格名称。field要被增加或删除的字段名称。 type字段数据类型。size字段大小。index对此字段的索引。例如：客户信息表(bClient)中新建一个“注册日期”的字段.ALTER TABLE bClient ADD COLUMN reg_datetime datetime;例如：在客户信息表（bClient)中删除一个“注册日期”的字段。ALTER TABLE bClient DROP COLUMN reg_datetime;活动2 数据查询在众多的SQL命令中，Select语句应该算是使用最频繁的。Select语句主要被用来对数据库进行查询并返回符合用户查询标准的结果数据。Select语句的语法格式如下：Select column1 , column2，etc from tablename where condition； （ 表示可选项）Select语句中位于Select关键词之后的列名用来决定那些列将作为查询结果返回。用户可以按照自己的需要选择任意列，还可以使用通配符“来设定返回表格中的所有列。Select语句中位于from关键词之后的表格名称用来决定将要进行查询操作的目标表格。Select语句中的where可选从句用来规定哪些数据值或哪些行将被作为查询结果返回或显示。T-SQL语句是不区分大小写的.在where条件从句中可以使用以下一些运算符来设定查询标准：= 等于 大于 小于= 大于等于= 小于等于100 AND User_Type =VIP1”；u FROM 条件子句 指定表格名称或是查询,其中包含列在SELECT语句的字段数据。u BETWEEN.。AND 运算符决定某一人数值是否介于特定的范围之内，此运算符只可以用在SQL的语句中。例如：若是要从客户信息表中查询出所有出生日期在1970年1月1日到1979年12月31日的客户信息，可以利用下面的程序来做.SELECT from bClient where User_Birthday BETWEEN 19700101 AND 1979-12-31；u LIKE 操作数用来将一字符串与另一特定字符串样式（pattern)比较，并将符合该字符串样式的记录过滤出来。2、 应用举例根据25中的数据表结构描述，实现如下SQL语句。u 多表查询和笛卡尔乘积查询VIP客户类型为VIP1的用户订单信息.Select bClient.*,bOrder.订单编号， bOrder。订单金额From bClient left join bOrder on bClient。客户编号= bOrder。客户编号.Where bClient. VIP客户类型=VIP1 u 使用表格别名查询VIP客户类型为VIP1的用户订单信息，采用别名对表进行定义。设置bClient表的别名为BCL1,bOrder 表的别名为BOR1。Select BCL1。，bOrder.。订单编号， BOR1。订单金额From bClient AS BCL1 left join bOrder AS BOR1 on BCL1。客户编号= BOR1 .客户编号.Where bClient。 VIP客户类型=VIP1 u 使用统计函数统计VIP客户类型为VIP1的所有客户在2010年6月1日到2010年10月1日的订单总额。Select count(BOR1.订单金额) as 订单总额From bClient AS BCL1 left join bOrder AS BOR1 on BCL1.客户编号= BOR1 .客户编号.Where bClient. VIP客户类型=VIP1 and (BOR1。下单日期 between 2010-06-01 and 2010-1001)u 使用GROUP BY 子句统计所有VIP客户类型在2010年6月1日到2010年10月1日的订单总额,进行分组排列显示。Select bClient .VIP客户类型 , count（BOR1.。订单金额） as 订单总额From bClient AS BCL1 left join bOrder AS BOR1 on BCL1。客户编号= BOR1 .客户编号。Where bClient. VIP客户类型=VIP1 and （BOR1.下单日期 between 20100601 and 201010-01）Group by bClient .VIP客户类型u 使用嵌套查询在实际中经常用到嵌套查询，嵌套查询要求服务器在处理剩下的查询工作之前先生成一个结果集,然后根据当前的查询结果集再继续下面的查询工作。例如：根据商品信息表和库存表查询库存数量大于0的所有商品信息。Select From bGoodsWhere bGoods.商品编号 in （Select商品编号 From bStorageWhere bStorage。 数量0）活动5 函数1、AVG：算数平均数AVG(expr) 例如：若要性别为男的,所有客户的平均年龄，可以利用下面的SQL语句来完成。Select AVG(DATEDIFF(year，User_Birthday,GETDATE（）) as 平均年龄FROM bClientWHERE User_Sex=男2、COUNT：计算记录条数COUNT（expr) 例如：若要性别为男的，所有客户的数量，可以利用下面的程序。Select Count(*） AS 人数FROM bClientWHERE User_Sex=男；3、MAX，与MIN:返回某字段的最大值与最小值。max(expr)min(expr） 例如：查询客户信息表里面年龄最大的和年龄最小的客户信息,可以利用下面的查询方式。SELECT Max(User_Birthday），min(User_Birthday) FROM bClient4、SUM：返回某特定字段或是运算的总和数值。SUM（expr) 例如：要计算所有商品信息表中所有商品信息的总价格。SELECTSum(Goods_Price） AS 商品总价格 FROM bGoods5、T-SQL 命名规范可以参考 附录一 “TSQL 命名规范”任务2 Access 2003数据库及其应用掌握Access2003基本操作，及其建表、查询、视图、窗体等基本操作，熟悉桌面数据库系统的基本操作，完成活动1 认识ACCESS2003 Access 是微软公司推出的基于Windows的桌面关系数据库管理系统(RDBMS，即Relational Database Management System），是Office系列应用软件之一.它提供了表、查询、窗体、报表、页、宏、模块7种用来建立数据库系统的对象；提供了多种向导、生成器、模板，把数据存储、数据查询、界面设计、报表生成等操作规范化；为建立功能完善的数据库管理系统提供了方便,也使得普通用户不必编写代码，就可以完成大部分数据管理的任务。Access的优点（1）存储方式单一 Access管理的对象有表、查询、窗体、报表、页、宏和模块,以上对象都存放在后缀为(.mdb)的数据库文件种，便于用户的操作和管理. (2)面向对象 Access是一个面向对象的开发工具，利用面向对象的方式将数据库系统中的各种功能对象化，将数据库管理的各种功能封装在各类对象中。它将一个应用系统当作是由一系列对象组成的,对每个对象它都定义一组方法和属性，以定义该对象的行为和属性，用户还可以按需要给对象扩展方法和属性。通过对象的方法、属性完成数据库的操作和管理,极大地简化了用户的开发工作。同时，这种基于面向对象的开发方式，使得开发应用程序更为简便。 (3）界面友好、易操作 Access是一个可视化工具，是风格与Windows完全一样,用户想要生成对象并应用，只要使用鼠标进行拖放即可,非常直观方便.系统还提供了表生成器、查询生成器、报表设计器以及数据库向导、表向导、查询向导、窗体向导、报表向导等工具，使得操作简便，容易使用和掌握。 （4)集成环境、处理多种数据信息 Access基于Windows操作系统下的集成开发环境，该环境集成了各种向导和生成器工具，极大地提高了开发人员的工作效率，使得建立数据库、创建表、设计用户界面、设计数据查询、报表打印等可以方便有序地进行。 （5）Access支持ODBC（开发数据库互连，Open Data Base Connectivity），利用Access强大的DDE（动态数据交换）和OLE（对象的联接和嵌入）特性，可以在一个数据表中嵌入位图、声音、Excel表格、Word文档,还可以建立动态的数据库报表和窗体等。Access还可以将程序应用于网络，并与网络上的动态数据相联接。利用数据库访问页对象生成HTML文件，轻松构建Internet/Intranet的应用。ACCESS的缺点ACCESS是小型数据库,既然是小型就有他根本的局限性，以下几种情况下数据库基本上会吃不消：1.数据库过大，一般ACCESS数据库达到50M左右的时候性能会急剧下降！2.网站访问频繁，经常达到100人左右的在线。3.记录数过多,一般记录数达到10万条左右的时候性能就会急剧下降!活动2 ACCESS2003 应用1 新建Access文件资源管理器中的某一个硬盘中建立一个工作文件夹，单击右键选择新建/Microsoft Office Access应用程序，根据自己的需要建立文件名。2 建立数据表 创建数据库和数据表根据学习情境2，任务6 数据库的设计举例里面的活动2 E-R图的设计,完成数据表的创建工作。文件名称:Shopping表名： 客户信息表(bClient）字段名称英文名称类型长度客户编号（GUID)User_ID文本50客户姓名User_Name文本30性别User_Sex文本4出生日期User_Birthday日期类型VIP客户类型User_Type文本50地址User_Address文本100联系电话User_Tel文本表名： 客户订单表(bOrder)字段名称英文名称类型长度订单编号Order_ID文本50下单日期Order_Date日期类型客户编号User_ID文本50商品编号Goods_ID文本50订单金额Order_Money数字12,2表名: 商品信息表(bGoods）字段名称英文名称类型长度商品编号Goods_ID文本50商品名称Goods_Name文本50价格Goods_Price数字12，2u 在菜单中打开ACCESS2003数据库，如图3-1所示:图 3-1 Access 2003 打开界面u 创建文件名为Shopping.mdb的ACCESS2003,如下图32所示：图 3-2 Access 2003 新建数据库u 点击创建按钮，出现如下界面图33所示：图 33 Access 2003 新建表u 双击“使用设计器创建表”以后如下图34显示.图 34 Access 2003 新建表结构u 使用英文名称输入客户信息如下图3-5所示:图 35 Access 2003 字段属性u 将User_ID设置为主键如下图3-6所示：图 36 Access 2003 设置主键u 修改数据类型如下图37所示:图 3-7 Access 2003 设置出生日期字段日期属性u 有些数据类型需要修改字段属性，如下图38所示：图 38 Access 2003 修改字段属性u 选择工具栏中的保存按钮来保存表，表名为bClient。如下图3-9所示：图 39 Access 2003 保存表然后点击确定，客户信息表就创建完成。u 在表中输入数据，如下图310所示。图 310 Access 2003 打开表信息输入数据，图示如下图311所示。图 311 Access 2003 新增人员信息客户订单表（bOrder)和商品信息表(bGoods)的创建方法和客户信息表的创建方法一样。3 ACCESS2003基本操作u 在客户信息表中新增客户编号为2009010221的数据，SQL脚本如下。Insert into bClient(User_ID,User_Name，User_Sex，User_Birthday,User_Type,User_Address，User_Tel) values (2009010221,张三，男，198001-01,VIP1，苏州国际教育园北区学府路号，13911111111）点击查询,设计视图中创建查询,如图3-12所示：图 312 Access 2003 新建查询选择需要操作的表进行添加,然后关闭“显示表”窗体，如图313所示：图 3-13 Access 2003 查询里面显示表选择左上角的工具栏视图并选择SQL视图，如下图3-14所示：图 3-14 Access 2003 SQL 视图然后在出现的对话框中输入增加数据的SQL脚本，如图3-15所示:图 315 Access 2003 SQL 视图 编辑SQL脚本点击工具栏中的运行按钮,可以完成新增操作。u 在客户信息表中更新编号为2009010221的电话号码update bClient set User_Tel=13915611111 where User_ID=2009010221u 在客户信息表中删除编号为2009010220的信息delete from bClient where User_ID=2009010220u 在客户信息表中查询编号为2009010221的信息Selectfrom bClient where User_ID=20090102214 ACCESS的视图查询操作将用户表bClient 、商品表bGoods、订单表bOrder,建立关联查询操作.点击 “在设计视图中创建查询”按钮,如图312所示。添加三个表并设置关联。设置操作如图316所示:图 316 Access 2003 视图 可视化编辑在SQL视图中查看用户表bClient 、商品表bGoods、订单表bOrder，建立关联查询SQL操作的相应SQL脚本：SELECT bOrder。Order_ID, bOrder。Order_Date， bOrder。User_ID， bClient。User_Name， bClient。User_Tel, bClient。User_Address， bGoods。Goods_Name， bOrder。Order_MoneyFROM （bOrder LEFT JOIN bClient ON bOrder。User_ID = bClient。User_ID) LEFT JOIN bGoods ON bOrder。Goods_ID = bGoods.Goods_ID；最后将查询结果保存，保存的查询名称为yhddan