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

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

———————————————————————————————— 作者：
———————————————————————————————— 日期：

19

个人收集整理 勿做商业用途
任务5 MySQL 5.1 数据库及其应用
掌握My SQL5。1、安装、基本操作,及其建表、数据库关系图、查询、视图、存储过程、函数、触发器等操作。使用Navicat工具按照网络商店数据库结构完成相关练习操作.
活动1 认识MySQL 5。1
MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQL AB公司.在2008年1月16号被Sun公司收购.而2009年,SUN又被Oracal收购.对于Mysql的前途，没有任何人抱乐观的态度。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库.
MySQL的官方网站的网址是：
MySQL的特性
　　1.使用C和C++编写，并使用了多种编译器进行测试,保证源代码的可移植性
　　2。支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wrap、Solaris、Windows等多种操作系统
　　3。为多种编程语言提供了API.这些编程语言包括C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等.
　　4.支持多线程,充分利用CPU资源
　　5.优化的SQL查询算法，有效地提高查询速度
　　6。既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中提供多语言支持，常见的编码如中文的GB 2312、BIG5,日文的Shift_JIS等都可以用作数据表名和数据列名
　　7.提供TCP/IP、ODBC和JDBC等多种数据库连接途径
　　8。提供用于管理、检查、优化数据库操作的管理工具
9。可以处理拥有上千万条记录的大型数据库
活动2 MySQL 5.1安装
u 打开安装程序首页面,如图3—72所示：

图 3-72 MySql安装首页面
u 点击“下一步”，选择典型安装，如图3—73所示。

图 3-73 MySql 安装向导界面
u 选择开始安装，如图3—74所示:

图 3-74 MySql 安装信息显示
u 选择配置MySQL 服务器，同时不选择注册服务器，如图3-75所示：

图 3-75 MySql 安装注册界面
u 选择标准配置，如图3-76所示：

图 3-76 MySql 安装配置类型
u 选择一个作为服务启动的名称,如图3-77所示：

图 3-77 MySql 服务启动配置
u 输入root管理员的密码：*＊＊*＊＊,同时必须勾选Enable root access from remote machines,Enable root access from remote machines的作用是允许用户远程登录，如图3-78所示。

图 3-78 MySql 用户管理
u 开始执行配置安装，如图3—79所示：

图 3-79 MySql 安装执行界面
u 完成安装，如图3-80所示：

图 3—80 MySql 安装完成
活动3 Navicat 安装
Navicat是一个强大的MySQL数据库管理和开发工具。Navicat为专业开发者提供了一套强大的足够尖端的工具，但它对于新用户仍然是易于学习.本书中大部分MySQL应用实例都是在这款工具中完成的。
Navicat使用了极好的图形用户界面（GUI），可以让你用一种安全和更为容易的方式快速和容易地创建、组织、存取和共享信息。基于Windows平台，为MySQL量身订作，提供类似于MsSQL的用管理界面工具。
u 进入开始安装首页面，如图3-81所示：

图 3-81 Navicat 工具安装首界面
u 选择安装路径,如图3—82所示：

图 3-82 Navicat 工具安装路径选择
u 开始安装，如图3-83所示:

图 3-83 Navicat 工具安装
u 安装完成，如图3-84所示：

图 3-84 Navicat 工具安装完成
活动4 MySQL 5.1应用
① 创建数据库
根据学习情境2,任务6 数据库的设计举例里面的活动2 E-R图的设计，完成数据表的创建工作.
数据库名称：Shopping
表名： 客户信息表（bClient）
字段名称
英文名称
类型
长度
客户编号(GUID）
User_ID
整数

客户姓名
User_Name
字符串类型
30
性别
User_Sex
字符串类型
4
出生日期
User_Birthday
日期类型

VIP客户类型
User_Type
字符串类型
50
地址
User_Address
字符串类型
100
联系电话
User_Tel
字符串类型
20
表名： 客户订单表（bOrder）
字段名称
英文名称
类型
长度
订单编号〈自动加1>
Order_ID
整数

下单日期
Order_Date
日期类型

客户编号
User_ID
整数

商品编号
Goods_ID
整数

订单金额
Order_Money
双精度数值
12，2
表名： 商品信息表(bGoods）
字段名称
英文名称
类型
长度
商品编号
Goods_ID
整数

商品名称
Goods_Name
字符串类型
50
价格
Goods_Price
双精度数值
12,2
u 通过Navicat 建立MySQL数据库连接，如下图所示。输入连接名，主机名/IP地址，端口，用户名,密码.建立数据库服务器的连接。
这里连接名用户自己根据需要来设置，主机名/IP地址里面输入MySQL数据库服务器的机器名或者数据库服务器的IP地址，端口为默认的3306，用户名和密码为安装时设置的用户名和密码。输入完成以后，选择“连接测试”,如果连接成功，会弹出对话框“连接成功”。连接测试完成以后，选择“确定"按钮,进入数据库管理系统,如图3-85所示。

图 3-85 Navicat 数据库连接界面
u 打开连接,在连接建立完成以后，选择“打开连接”打开MySQL数据库管理系统图形管理界面，如图3—86所示.

图 3-86 Navicat 工具首界面
u 连接打开以后,单击右键，选择“创建数据库"。根据需要创建Shopping数据库,如下图所示.数据库名称部分输入：Shopping，为了更好的识别汉字在字符集部分输入“utf8 -— UTF—8 Unicode”，整理部分输入“utf8_general_ci”，如图3-87所示。

图 3—87 Navicat 字符选择
u 数据库创建完成,在Shopping数据库下面新建数据表，单击右键选择“创建表”,如下图显示，如图3-88所示。

图 3-88 Navicat 创建数据表
② 进入创建数据表界面，创建客户信息表（bClient）、客户订单表（bOrder)和商品信息表（bGoods)。bClient表结构图如下3-89所示。

图 3—89 Navicat 数据表字段编辑
客户信息表（bClient）、客户订单表(bOrder)和商品信息表(bGoods）对应的SQL脚本如下所示
CREATE TABLE `bclient` （
`User_ID` int(11) NOT NULL AUTO_INCREMENT,
`User_Name` varchar(50) DEFAULT NULL,
`User_Sex` varchar（4) DEFAULT NULL，
`User_Birthday` date DEFAULT NULL，
`User_Type` varchar(50) DEFAULT NULL，
`User_Address` varchar(100） DEFAULT NULL COMMENT ’联系地址'，
`User_Tel` varchar（20） DEFAULT NULL COMMENT '联系电话’，
PRIMARY KEY (`User_ID`）
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

CREATE TABLE `bgoods` (
`Goods_ID` int(11) NOT NULL,
`Goods_Name` varchar(50） DEFAULT NULL,
`Goods_Price` decimal（15,8） DEFAULT NULL，
PRIMARY KEY (`Goods_ID`）
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `border` (
`Order_ID` int（11） NOT NULL AUTO_INCREMENT，
`Order_Date` date DEFAULT NULL,
`User_ID` int（11） DEFAULT NULL，
`Goods_ID` int(11) DEFAULT NULL，
`Order_Money` decimal(15,8) DEFAULT NULL,
PRIMARY KEY （`Order_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
③ 创建视图
例如：创建用户订单视图
订单视图结构，需要显示 {订单号码、下单日期、用户编号、用户姓名、用户电话、用户地址、产品名称、订单金额}
SQL 脚本如下。
create view 客户订单 as
Select
`border`.`Order_ID` AS `订单号码`,
`border`。`Order_Date` AS `下单日期`，
`border`.`User_ID` AS `用户编号`，
`bclient`。`User_Name` AS `用户姓名`,
`bclient`。`User_Tel` AS `用户电话`，
`bclient`。`User_Address` AS `用户地址`,
`bgoods`。`Goods_Name` AS `商品名称`,
`border`。`Order_Money` AS `订单金额`
from
(（`border` left join `bclient` on（(`bclient`.`User_ID` = `border`。`User_ID`))）
left join `bgoods` on（（`border`。`Goods_ID` = `bgoods`.`Goods_ID`））)
与SQL Server 2005类似，Navicat 也设置了视图图形界面，如下图3-90所示。

图 3-90 Navicat 创建视图
④ 数据表基本操作
u 用户表插入新用户
Insert
into bClient
（User_Name,User_Sex，User_Type,User_Address，
User_Tel,User_Birthday）
values
(’张三’，'男’，'VIP1',
'苏州国际教育园北区学府路号'，’13911111111'，'1979—12-25’)
u 商品表增加商品
Insert
into bGoods(Goods_ID，Goods_Name，Goods_Price）
values (1,’联想笔记本',2800)；

Insert
into bGoods（Goods_ID,Goods_Name,Goods_Price)
values （2，’苹果MP3’,120）；
u 用户订单表增加用户新的订单
Insert
into bOrder(Order_Date，User_ID,Goods_ID,Order_Money)
values ('2010-04-29’，1，1，160。66)
u 修改用户订单表里面订单编号为“1"的商品的价格为180元
update bOrder
set Order_Money=180
where Order_ID=1
u 删除用户订单表里面订单编号为“1"的订单信息
delete from bOrder where Order_ID=1
u 查询视图“用户订单",里面订单号码为“1”的订单
Select * from 用户订单 where 订单号码=2
⑤ 创建存储过程
一个存储程序是可以被存储在服务器中的一套SQL语句。一旦它被存储了,客户端不需要再重新发布单独的语句，而是可以引用存储程序来替代。
下面一些情况下存储程序尤其有用：
当用不同语言编写多客户应用程序，或多客户应用程序在不同平台上运行且需要执行相同的数据库操作之时。
安全极为重要之时。比如,银行对所有普通操作使用存储程序。这提供一个坚固而安全的环境，程序可以确保每一个操作都被妥善记入日志。在这样一个设置中，应用程序和用户不可能直接访问数据库表，但是仅可以执行指定的存储程序。
u 修改用户编号为“1”的姓名为”李四”，同时把该用户的所有订单在现在的订单费用的基础上减少10％，并显示订单视图中用户编号是 “ 1"的信息.
CREATE procedure update_Fun
（
p_User_ID varchar（50),
P_UserName varchar(50)
)
begin
START TRANSACTION;
update bClient set User_Name=P_UserName where User_ID=p_User_ID ;
update bOrder set Order_Money=Order_Money＊0.9 where User_ID=p_User_ID ；
COMMIT；
Select 用户编号,用户姓名,sum(订单金额) as 订单总金额
from 用户订单
where 用户姓名=P_UserName
group by 用户编号，用户姓名；
end
调用存储过程操作如下。
CALL update_Fun (’1’，'王五’)；
⑥ 创建触发器
对用户订单表(border）建立触发操作，监视订单表的新增、删除和更新操作，并把结果插入到监控表.
监控表结构如下所示。
表名： 用户订单监控表（bwatch_Goods）
字段名称
英文名称
类型
长度
自动编号
AUTO_ID
int

触发时间
Op_date
date

监控内容
Op_content
字符串
100
表结构SQL脚本如下。
CREATE TABLE `bwatch_goods` （
`AUTO_ID` int（11） NOT NULL AUTO_INCREMENT,
`Op_date` datetime DEFAULT NULL，
`Op_content` varchar(100） DEFAULT NULL，
PRIMARY KEY （`AUTO_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
对用户订单表创建新增、更新、删除触发器，监视对用户订单表的所有操作,把监视到的结果增加到订单监控表（bwatch_Goods）中.
CREATE TRIGGER testref BEFORE INSERT ON border
FOR EACH ROW BEGIN
Insert into bwatch_goods（op_date,op_content）
values （now(),’新增操作'）；
END

CREATE TRIGGER testref2 BEFORE UPDATE ON border
FOR EACH ROW BEGIN
Insert into bwatch_goods（op_date，op_content）
values （now（），’更新操作'）;
END

CREATE TRIGGER testref2 BEFORE DELETE ON border
FOR EACH ROW BEGIN
Insert into bwatch_goods（op_date，op_content）
values (now（)，'删除操作'）；
END
删除触发器使用如下SQL语句。
DROP TRIGGER 触发器名称
在Navicat中查看触发器，如下图3-91所示.

图 3—91 Navicat 创建触发器
Mysql 5。1参考手册在http://dev.mysql。com/doc/refman/5。1/zh/preface。html，有兴趣的读者可以进行更加深入的学习.