MySQL数据库毕业论文.doc

浅谈MySQL数据库计算机与信息工程学院 指导教师 讲师摘要 本论文结合设计的Jsp/Servlet网上拍卖系统论述了目前比较流行的MySQL数据库，从MySQL数据库的权限、用户、建立表格时用到的主要的参数和数据库的连接技术等方面进行详述的。关键词 MySQL用户 ; MySQL参数 ; MySQL连接MySQL作为最受欢迎的开源SQL数据库管理系统，得到了广泛应用。MySQL具有最小粒度可达列、功能齐全、查询迅捷等优点。所以我们在本次网上拍卖系统中用的数据库管理系统就是使用了MySQL管理系统。我做的部分是数据库的部分。下面从权限、用户、参数和连接等几个方面来详细分析MySQL数据库管理系统。1 MySQL 的权限概述MySQL 共有select、insert、update、delete、index、alter、create、drop、grant、reload、shutdown、process 和file 等13 种权限。其中select 权限用于控制用户对数据库表的select 操作，insert 权限用于控制用户对数据库表的insert 操作，update 权限用于控制用户对数据库表update 操作，delete 权限用于控制用户对数据库表的delete 操作，index 权限用于控制用户对数据库表建立或删除索引，alter 权限用于控制用户对数据库表的alter 操作，create 权限用于控制用户建立数据库、数据库表和索引，drop 权限用于控制用户删除数据库或表，grant 权限用于向特定用户授予各种权限，reload 权限用于控制用户对服务器的flush 操作，shudown 权限用于控制用户关闭数据库服务器，process 权限用于控制用户查看和杀死数据库服务器的线程，file 权限用于控制用户存取服务器上的权限。MySQL的13 种权限中，select、insert、update、delete 等4 种权限为数据操作权限，用于数据查询、插入、修改和删除，其他权限则主要用于数据管理。对于这4 种数据操作权限，MySQL 允许数据库管理员（DBA）将这4 种权限建立在数据库、表、列等数据级别上赋予特定用户。若将这4 种权限建立在一数据库上赋予一用户，则该用户拥有该数据库中所有表上所有列的相应权限；若将这4 种权限建立在一数据库表上赋予一用户，则该用户拥有该数据库表所有列上的相应权限；若将这4 种权限建立在一数据库表的某列上赋予一用户，则该用户仅拥有对该列上的相应权限。这4 种权限可同时建立在数据库、表和列上，因此可能出现同一用户在数据库、表和列3 个数据粒度上的权限发生冲突的现象，此时MySQL 采用“数据库级权限< 表级权限< 列级权限”规则处理该冲突。2 MySQL 的用户MySQL 的用户由用户名标识，与DB2 不同，MySQL 独立管理自己的用户名，而不使用操作系统自身的用户名，同时MySQL 独立管理个用户的密码。此外，MySQL 还将用户名与用户登录的主机实现绑定，即MySQL 允许数据库管理员对特定主机上的特定用户进行授权。特定主机由主机名或主机I P 地址标识，且支持通配符：% 代表任意字符、_ 代表任意一个字符。比如允许用户L a n U s e r 在81.34.17.1 81.34.17.254 等主机上登录，则数据库管理员授权时，可使用主机通配符81.34.17. %。若LanUser 用户在81.34.18.22 上登录，则MYSQL 数据库服务器将拒绝之。三、创建数据表时所用到的主要的参数选项3.1 Primary Key具有Primary Key限制条件的字段用于区分同一个数据表中的不同记录。因为同一个数据表中不会存在两个具有相同值的Primary Key字段，所以对于那些需要严格区分不同记录的数据表来说，Primary Key具有相当重要的作用。3.2 Auto_Increment具有Auto_Increment限制条件的字段值从1开始，每增加一条新记录，值就会相应地增加1。一般来说，我们可以把Auto_Increment字段作为数据表中每一条记录的标识字段。3.3 NOT NULLNOT NULL限制条件规定用户不得在该字段中插入空值。 其它数据表命令除了创建新的数据表之外，MySQL数据库还提供了其它许多非常实用的以数据表作为操作对象的命令。例如： 显示数据表命令 mysql> show tables; 该命令将会列出当前数据库下的所有数据表；显示字段命令mysql> show columns from tablename; 该命令将会返回指定数据表的所有字段和字段相关信息。3.4 Jsp/Servlet网上拍卖系统系统中用到的数据库表格本系统跟需求所建立的数据库表有如下：users用户信息表 FieldTypeNullKeyDefaultCommentuIdbigintPRI用户编号telephonevarcharYes手机号nickNamevarcharYes用户的姓名passwordvarcharYes用户密码usernamevarcharYes用户名addressvarcharYes地址emailvarcharYes邮箱表中uId为主键并被设置为IDENTITY (1, 1)，这样当插入一条记录且uId字段的数据为空（null）时，新记录的uId值将由系统自动给出（若是一张新表，则从1开始）。这样可以保证整个表中的uId字段在其数据类型允许的范围之内没有重复的值。管理员信息表Field待添加的隐藏文字内容3TypeNullKeyDefaultCommentadminidintPRI管理员标识admin_namevarchar管理员名字admin_pwdvarcharYes管理员的密码分组varchar分组isLoginInsmallint0检看登录情况所示为管理员信息表，记录了系统管理员的相关信息。表中adminid为主键且设为自动增长，priv为管理员的权限字段代表着此管理员在系统中可以管理的模块，isLoginIn用来临时记录管理员的登录信息，当用户登录系统后此字段被设置为1，退出系统时重置为0，防止用户重复登录。商品分类FieldTypeNullKeyDefaultCommentlidintpri类标识 lnamevarchar类名introducevarchar类介绍商品信息表FieldTypeNullKeyDefaultCommentcidbigintPRI主键，递增uidintFK0发布者的编号识cnamevarchar0商品名称cmainintFK0商品类别cpicvarcharYes商品图片地址ctimedatetimeYes发布商品的时间cdaysint0出售的天数 statusint0商品情况startbidint0开始价introducevarchar0商品介绍如表所示为记录用户所发布的商品的详细信息的表。表中cid为主键设为自动增长。uId为users表中uid的外键，cmain为sort表中sid的外键，csub为subsort表中ssid的外键，这些外键能保证商品的完整性。cend表示商品的拍卖是否已经结束。climit用来限制出价用户的等级也就是说只是当用户的好评数达到climit的限制时才能对商品出价，这样可以维护卖方的利益。model代表商品出售的方式是拍卖还是一价成交的方式。chot用来记录商品被用户浏览的次数，作为热点商品的参考。用户出价信息表FieldTypeNullKeyDefaultCommentbidint-PRI-标识cidint-FK-商品标识uidint-FK-用户IDbtimedatetime-出价的时间howmuchfloat-出了多少钱 statusint-0审核表中pid为主键设为自动增长。cid为商品表cat中cid的外键，这样可以保证用户的出价都是针对已经发布的商品发出的。username为出价的用户名称。howmuch代表用户对此商品出了多少钱。四、数据操作4.1 MySQL的数据操作对MySQL数据库中数据的操作可以划分为四种不同的类型，分别是添加、删除、修改和查询。MySQL数据库所采用的SQL语言同其它绝大多数计算机编程语言一样，对命令的语法格式有严格的规定。任何语法格式上的错误，例如不正确的使用括号、逗号或分号等都可能导致命令执行过程中的错误。添加记录指用户可以使用INSERT命令向数据库中添加新的记录。 例如： mysql> INSERT INTO test VALUESmysql> (John, carrots, mysql> 5554321,NULL)。首先，所有的字符类型数据都必须使用单引号括起来。其次，NULL关键字与AUTO_INCREMENT限制条件相结合可以为字段自动赋值。最后，也是最重要的一点就是新记录的字段值必须与数据表中的原字段相对应，如果原数据表中有4个字段，而用户所添加的记录包含3个或5个字段的话都会导致错误出现。MySQL数据库的一个非常显著的优势就是可以对整数、字符串和日期数据进行自动转换。因此，用户在添加新记录时就不必担心因为数据类型不相符而出现错误。查询数据指的是如果我们无法从数据库中查找和读取数据的话，数据库就丧失了其存在和使用的价值。在MySql数据库中，用户可以使用SELECT命令进行数据的查询。例如：mysql> SELECT * FROM test mysql> WHERE(name = John); 上述命令会返回如下结果：name email phoneID John carrots 5554321 1。删除数据是指用户除了可以向数据表中添加新的记录之外，还可以删除数据表中的已有记录。删除记录可以使用DELETE命令。修改数据指MySQL数据库还支持用户对已经输入到数据表中的数据进行修改。修改记录可以使用UPDATE命令。事实上，不仅MySQL数据库，还有SQL server reportingservice，PostgreSQL、SAPDB、Firebird等数据库产品都像一颗颗璀璨的星星闪耀在夜空一样，他们所支持的SQL语言具有非常丰富和强大的数据操作功能，这不仅方便了我们的学习和生活，更重要的是数据库真正做到了网络技术与电子技术的紧密结合，令21世纪的人们可以在电子时代的海洋中尽情畅游。4.2 Jsp/Servlet网上拍卖系统中的数据操作 下面我用数据库的ER图来表现出Jsp/Servlet网上拍卖系统中的数据操作和表格之间的关系。用户信息表定单表商品信息表商品分类表购买属于查看发布用户ID表ID类ID商品ID5 数据库连接1) 在JSP、Servlet 和J avaBean 中建立数据库的连接.2) 执行对数据库SQL 的操作.3) 断开数据库连接.使用这种模式进行开发时, 存在的问题如下: 第一, 我们要为每一次访问数据库的请求建立一次数据库连接, 在系统用户量小和操作次数少的应用来说, 可能察觉不到系统的开销. 但是, 当用户量很大, 可能In ternet 上的很多用户都在提出访问数据库的请求, 那么就要建立很多次连接. 在这种情况下, 系统开销是非常大的. 事实上, 在一个基于数据库的W eb 操作中, 建立数据库连接的操作将是系统开销最大的操作之一, 很多情况下, 这就成为网站性能的瓶颈所在. 第二, 传统情况下, 必须人工管理每一个连接, 在完成对数据库的访问之后, 必须确保所有的连接被正确地关闭. 如果存在一些异常导致数据库连接没有被正常关闭, 将导致数据库系统内存泄露, 这种情况下, 就必须重新启动数据库.针对以上问题, 一种方法就是考虑建立一个公共的数据库连接类connect ion, 在创建了它的对象之后, 我们就通过这个连接对象来进行对数据库的访问, 在访问结束后, 也不用考虑去关闭它, 让它长时间的生存, 以后对数据库的访问请求我们也是通过它来实现. 这样, 就不用每次考虑创建连接和关闭连接的问题了. 但是, 同一个连接使用的次数过多, 将导致连接的不稳定, 进而导致W eb Server 的频频重启. 所以, 这种方法也是不可取得. 实际上, 可以使用连接池的思想来解决上述问题.5.1数据库连接池的基本概念及原理对于共享资源, 有一个很著名的设计模式: 资源池(Resou rce Poo l). 该模式正是为了解决资源的频繁分配、释放所造成的问题. 为解决上述问题, 可以采用数据库连接池技术. 连接池的基本思想就是为数据库连接建立一个“资源池”. 预先在这个资源池中放入一定数量的连接, 当系统应用需要访问数据库时, 就从这个资源池中取得一个空闲的连接, 待对数据库的访问结束后, 就释放连接到资源池中.通过对系统使用情况的衡量, 可以设定一个比较合理的连接池中连接数的最大值, 以限制系统过度的与数据库的连接. 并且, 我们还可以通过连接池的管理机制来对连接池内的连接进行监督和管理. 为系统开发、测试和调整提供依据.数据库连接池负责分配、管理和释放数据库连接, 它允许应用程序重复使用一个现有的数据库连接, 而再不是重新建立一个; 释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏. 这项技术能明显提高对数据库操作的性能.数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中, 这些数据库连接的数量是由最小数据库连接数来设定的. 无论这些数据库连接是否被使用, 连接池都将一直保证至少拥有这么多的连接数量. 连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数, 当应用程序向连接池请求的连接数超过最大连接数量时, 这些请求将被加入到等待队列中.数据库连接池的最小连接数和最大连接数的设置要考虑到下列几个因素:1) 最小连接数是连接池一直保持的数据库连接, 所以如果应用程序对数据库连接的使用量不大, 将会有大量的数据库连接资源被浪费;2) 最大连接数是连接池能申请的最大连接数, 如果数据库连接请求超过此数, 后面的数据库连接请求将被加入到等待队列中, 这会影响之后的数据库操作;3) 如果最小连接数与最大连接数相差太大, 那么最先的连接请求将会获利, 之后超过最小连接数量的连接请求等价于建立一个新的数据库连接. 不过, 这些大于最小连接数的数据库连接在使用完不会马上被释放, 它将被放到连接池中等待重复使用或是空闲超时后被释放.数据库连接池的工作原理如下图所示:5.2 连接池关键问题分析5.2.1并发问题为了使连接管理服务具有最大的通用性, 必须考虑多线程环境, 即并发问题. 这个问题相对比较好解决, 因为J ava 语言自身提供了对并发管理的支持, 使用synch ron ized 关键字即可确保线程是同步的.5.2. 2多数据库服务器和多用户对于大型的企业级应用, 常常需要同时连接不同的数据库(如连接O racle 和Sybase). 我们采用的策略是: 设计一个符合单例模式的连接池管理类, 在连接池管理类的唯一实例被创建时读取一个资源文件, 其中资源文件中存放着多个数据库的u rl 地址、用户名、密码等信息. 根据资源文件提供的信息,创建多个连接池类的实例, 每一个实例都是一个特定数据库的连接池. 连接池管理类实例为每个连接池实例取一个名字, 通过不同的名字来管理不同的连接池.5.2.3 事务处理我们知道, 事务具有原子性, 对于一组SQL 语句要么全做, 要么全不做. 在Jva 语言中, Connec2属性为false, 然后显式的调用commit 或rollback 方法来实现. 但要高效的进行Connection 复用, 就必须提供相应的事务支持机制. 可采用每一个事务独占一个连接来实现, 这种方法可以大大降低事务管理的复杂性.5.2.4连接池的分配与释放连接池的分配与释放, 对系统的性能有很大的影响. 合理的分配与释放, 可以提高连接的复用度,从而降低建立新连接的开销, 同时还可以加快用户的访问速度.对于连接的管理可使用空闲池. 即把已经创建但尚未分配出去的连接按创建时间存放到一个空闲池中. 每当用户请求一个连接时, 系统首先检查空闲池内有没有空闲连接. 如果有就把建立时间最长(通过容器的顺序存放实现) 的那个连接分配给他(实际是先做连接是否有效的判断, 如果可用就分配给用户, 如不可用就把这个连接从空闲池删掉, 重新检测空闲池是否还有连接) ; 如果没有则检查当前所开连接池是否达到连接池所允许的最大连接数(m axConn) , 如果没有达到, 就新建一个连接, 如果已经达到, 就等待一定的时间( t im eou t).如果在等待的时间内有连接被释放出来就可以把这个连接分配给等待的用户, 如果等待时间超过预定时间t im eou t, 则返回空值(nu ll). 系统对已经分配出去正在使用的连接只做计数, 当使用完后再返还给空闲池. 对于空闲连接的状态, 可开辟专门的线程定时检测, 这样会花费一定的系统开销, 但可以保证较快的响应速度. 也可采取不开辟专门线程, 只是在分配前检测的方法.5.2.5连接池的配置与维护连接池中所存在的连接个数的保持遵循下列的原则: 系统可采取设置最小连接数(m inConn) 和最大连接数(m axConn) 来控制连接池中的连接. 最小连接数是系统启动时连接池所创建的连接数. 如果创建过多, 则系统启动就慢, 但创建后系统的响应速度会很快; 如果创建过少, 则系统启动的很快,响应起来却慢. 这样, 可以在开发时, 设置较小的最小连接数, 开发起来会快, 而在系统实际使用时设置较大的, 因为这样对访问客户来说速度会快些. 最大连接数是连接池中允许连接的最大数目, 具体设置多少, 要看系统的访问量, 可通过反复测试, 找到最佳点.确保连接池中的最小连接数有动态和静态两种策略. 动态即每隔一定时间就对连接池进行检测,如果发现连接数量小于最小连接数, 则补充相应数量的新连接, 以保证连接池的正常运转. 静态是发现空闲连接不够时再去检查.6.3 数据库连接池的实现