WEB数据库原理与应用学习情境.docx

情境描述： 本情境主要让学生掌握常用的数据库管理系统，熟悉标准的T-SQL语句，能够掌握常用的数据库管理系统（Access 2003、SQL Server 2005、MySQL 5.1、Oracle 10g、IBMDB2）。能够根据数据表结构实现数据库系统，能够进行常用的数据操作。本情境参考学时40学时。教学目标：1、 能力要求u 熟练掌握标准的T-SQL语言。u 掌握常用的数据库管理系统及其基本操作。2、 知识目标u 掌握标准的T-SQL语言。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 T-SQL标准语言及其应用 任务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教学效果评价 作业(10%)、任务考核(50%)、实训(40%)参考资料李春葆;金昌;曾平.数据库原理与应用:基于SQL Server 2005.北京：清华大学出版社 2009-01（澳）威利 等著，武欣 等译.PHP和MySQL Web开发.北京：机械工业出版社 2005.06孙风栋.Oracle 10g数据库基础教程.北京：电子工业出版社 2009-07任务1 T-SQL标准语言及其应用SQL是英文Structured Query Language的缩写，意思为结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通。按照ANSI（美国国家标准协会）的规定，SQL被作为关系型数据库管理系统的标准语言。SQL语句可以用来执行各种各样的操作，例如更新数据库中的数据，从数据库中提取数据等。目前，绝大多数流行的关系型数据库管理系统，如Oracle, Sybase, Microsoft SQL Server, Access等都采用了SQL语言标准。虽然很多数据库都对SQL语句进行了再开发和扩展，但是包括Select、Insert、Update、Delete、Create，以及Drop在内的标准的SQL命令仍然可以被用来完成几乎所有的数据库操作。一个典型的关系型数据库通常由一个或多个被称作表格的对象组成。数据库中的所有数据或信息都被保存在这些数据库表格中。数据库中的每一个表格都具有自己唯一的表格名称，都是由行和列组成，其中每一列包括了该列名称，数据类型，以及列的其它属性等信息，而行则具体包含某一列的记录或数据。熟悉T-SQL 的表创建、数据查询、更新记录、常用函数的语言操作。活动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_Sexchar(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_Sexchar(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 tableADD COLUMN field type(size)CONSTRAINT index|CONSTRAINT multifieldindex|DROP COLUMN field|CONSTRAINT indexnameTable欲被ALTER的表格名称。field要被增加或删除的字段名称。 type字段数据类型。size字段大小。index对此字段的索引。例如：客户信息表(bClient)中新建一个“注册日期”的字段。ALTER TABLE bClientADD 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条件从句中可以使用以下一些运算符来设定查询标准：= 等于 大于= 大于等于= 小于等于 不等于除了上面所提到的运算符外，LIKE运算符在where条件从句中也非常重要。LIKE运算符的功能非常强大，通过使用LIKE运算符可以设定只选择与用户规定格式相同的记录。此外，我们还可以使用通配符“%”用来代替任何字符串。举例如下：Select User_ID,User_Name , User_Sex ,User_Type ,User_Address from bClient where User_Address LIKE 苏州%;（注意，字符串必须被包含在单引号内）上述SQL语句将会查询所有名称以“王”开头的姓名：Select * from bClient where User_Name like 王% ；活动3添加、删除、更新记录1、添加新记录SQL语言使用Insert语句向数据库表格中插入或添加新的数据行。Insert语句的使用格式如下：Insert into tablename (first_column,.last_column) values (first_value,.last_value);例如：Insertinto bClient(User_ID,User_Name,User_Sex,User_Birthday,User_Type,User_Address,User_Tel)values (2009010220,张三,男,1980-01-01,VIP1,简单来说，当向数据库表格中添加新记录时，在关键词Insert into后面输入所要添加的表格名称，然后在括号中列出将要添加新值的列的名称。最后，在关键词values的后面按照前面输入的列的顺序对应的输入所有要添加的记录值。2、更新记录SQL语言使用update语句更新或修改满足规定条件的现有记录。update语句的格式为：update tablename set columnname = newvalue , nextcolumn = newvalue2.where columnname OPERATOR value and|or column OPERATOR value;例如：update bClient set User_Address = 苏州经贸职业技术学院 where User_ID = 2009010220 ; 使用update语句时，关键一点就是要设定好用于进行判断的where条件从句。3、删除记录SQL语言使用delete语句删除数据库表格中的行或记录。Delete语句的格式为：delete from tablename where columnname OPERATOR value and|or column OPERATOR value;例如：delete from bClient where User_ID = 2009010220 ;简单来说，当需要删除某一行或某个记录时，在delete from关键词之后输入表格名称，然后在where从句中设定删除记录的判断条件。注意，如果用户在使用delete语句时不设定where从句，则表格中的所有记录将全部被删除。活动4复杂SQL举例1、较完整的Select语句SQL的语句，可以说是SQL语法的主体，用来对某一个特定的数据库发出指示，并返回相关的数据，而SQL的语法结构，基本上可以利用下面的式子来表示：命令条件子句例如：SELECT * FROM TAB WHERE TAB.NAME =A下面通过一个完整的Select语句来进一步理解SQL用法。 SELECTpredicate*|table.*|table.field ,table.field2,. AS alias1 ,alias2,.FROM tableexpression ,.WHERE.GROUP BY.HAVING.ORDER BY.WITH OWNERACCESS OPTION SELECT 语句包括下面几个部分u Predicate ：如前面所述，包括了ALL，DISTINCT,DISTINCTROW,与TOP 我们可以利用这样的语句去限制查询后所得的结果。 u * ：从指定表格中指定所有的字段。 u Table：针对被选择出的记录的字段，所指定表格的名称。 u field1,field2：想要读取数据的字段名称，如果包含了一个以上的字段，会依照列出的顺序来读取数据。 u alias1,alias2：用来替代在表格实际字段名称的化名。 u tableexpression：表格名称或包含我们所想要的数据的表格。 u externaldatabase：若使用到不是目前的数据库则将其名字定义在externaldatabase当中。 u ALL,DISTINCT,DISTINCTROW,TOP属性词用法ALL若是您不指定任何的字段数据，则Microsoft Jet数据库引擎（database engine）将会选择所有的字段，并依据所定的条件查询出需求数据集。例如，下面这两个例子将会具有相同的效果，都会从客户信息表中返回所有字段的数据。Select * frombClient; 或者 Select all * from bClient;例如，可以获得年龄最大的前10名客户的记录：Selecttop 10 * from bClientorder by User_Birthday DESC 若是我们要查询出客户信息表中的所有记录，可以通过下面的语句来完成。而语句：SELECT distinct * FROM bClient 则对某个表格所选择的字段数据，略过重复的情况，也就是说，针对某个字段查询出来的记录结果是唯一的。例如有许多存放在职员表格的职员数据，也许会具有相同的姓名，所以若是我们用SQL语句中的SELECT DISTINCT，则查询出来的结果将会针对不一样的姓名加以筛选。若是您把DISTINCT 加以省略，则这样的查询会显示所有的记录。 ORDER BY条件语句此条件子句，通常与SELECT语句合并使用目的是将查询的结果，依照指定字段加以排序。其中可以与ALL，DISTINCT，DISINCTROW，或TOP一起来使用。ASC表示递增顺序类别。(默认值) ，而DESC表示递减顺序类别。例如：SELECT TOP 25 User_NameFORM bClientWHERE year(User_Birthday)=1984ORDER BY User_Type ASC如果您没有加上ORDER BY 这行条件的话，您所得到的数据，将会随机的数据。u HAVING 条件子句指定一特定的分组记录，并满足HAVING 所指定的条件或状态，但条件是针对分组的条件设置。HAVING跟WHERE 的用法相当类似，不同之处在于HAVING必须用于GROUP之后的分组数据上。GROUP BY 条件子句依据指定的字段，将具有相同数值的记录合并成一条。分组记录的字段名称，至多10个字段，而这些字段的顺序决定最高到最低的分组层次。例如：SELECTUser_Sex,count(User_ID) FROM bClient GROUP BY User_SexHAVING count(User_ID)100 AND User_Type =”VIP1”；u FROM 条件子句 指定表格名称或是查询，其中包含列在SELECT语句的字段数据。u BETWEEN.AND 运算符决定某一人数值是否介于特定的范围之内，此运算符只可以用在SQL的语句中。例如：若是要从客户信息表中查询出所有出生日期在1970年1月1日到1979年12月31日的客户信息，可以利用下面的程序来做。SELECT * from bClientwhere User_Birthday BETWEEN 1970-01-01 AND 1979-12-31；u LIKE 操作数用来将一字符串与另一特定字符串样式(pattern)比较，并将符合该字符串样式的记录过滤出来。2、 应用举例根据2-5中的数据表结构描述，实现如下SQL语句。u 多表查询和笛卡尔乘积查询VIP客户类型为VIP1的用户订单信息。Select bClient.*,bOrder.订单编号, bOrder.订单金额From bClient left join bOrder on bClient.客户编号= bOrder.客户编号.Where bClient. VIP客户类型=VIP1u 使用表格别名查询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客户类型=VIP1u 使用统计函数统计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客户类型=VIP1and (BOR1.下单日期 between 2010-06-01 and 2010-10-01)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客户类型=VIP1and (BOR1.下单日期 between 2010-06-01 and 2010-10-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 命名规范可以参考 附录一 “T-SQL 命名规范”任务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万条左右的时候性能就会急剧下降！活动2ACCESS2003 应用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,如下图3-2所示：图3-2 Access 2003 新建数据库u 点击创建按钮，出现如下界面图3-3所示：图 3-3 Access 2003 新建表u 双击“使用设计器创建表”以后如下图3-4显示。图3-4 Access 2003 新建表结构u 使用英文名称输入客户信息如下图3-5所示：图3-5 Access 2003 字段属性u 将User_ID设置为主键如下图3-6所示：图3-6 Access 2003 设置主键u 修改数据类型如下图3-7所示：图3-7 Access 2003 设置出生日期字段日期属性u 有些数据类型需要修改字段属性，如下图3-8所示：图3-8 Access 2003 修改字段属性u 选择工具栏中的保存按钮来保存表，表名为bClient。如下图3-9所示：图3-9 Access 2003 保存表然后点击确定，客户信息表就创建完成。u 在表中输入数据，如下图3-10所示。图3-10 Access 2003 打开表信息输入数据，图示如下图3-11所示。图3-11 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,张三,男,1980-01-01点击查询，设计视图中创建查询，如图3-12所示：图3-12 Access 2003 新建查询选择需要操作的表进行添加，然后关闭“显示表”窗体，如图3-13所示：图3-13 Access 2003 查询里面显示表选择左上角的工具栏视图并选择SQL视图，如下图3-14所示：图3-14 Access 2003 SQL 视图然后在出现的对话框中输入增加数据的SQL脚本，如图3-15所示：图3-15 Access 2003 SQL 视图 编辑SQL脚本点击工具栏中的运行按钮，可以完成新增操作。u 在客户信息表中更新编号为2009010221的电话号码u 在客户信息表中删除编号为2009010220的信息delete from bClient where User_ID=2009010220u 在客户信息表中查询编号为2009010221的信息Select*from bClient where User_ID=20090102214 ACCESS的视图查询操作将用户表bClient 、商品表bGoods、订单表bOrder，建立关联查询操作。点击 “在设计视图中创建查询”按钮，如图3-12所示。添加三个表并设置关联。设置操作如图3-16所示：图3-16 Access 2003 视图 可视化编辑在SQL视图中查看用户表bClient 、商品表bGoods、订单表bOrder，建立关联查询SQL操作的相应SQL脚本：SELECT bOrder.Order_ID, bOrder.Order_Date, bOrder.User_ID, bClient.User_Name, bClient.User_Tel, FROM (bOrder LEFT JOIN bClient ON bOrder.User_ID = bClient.User_ID) LEFT JOIN bGoods ON bOrder.Goods_ID = bGoods.Goods_ID;最后将查询结果保存，保存的查询名称为yhddan