mysql数据库

mysql数据库（精选8篇）

篇1：mysql数据库

在数据库表丢失或损坏的情况下，备份你的数据库是很重要的，如果发生系统崩溃，你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。有时，正是MySQL管理员造成破坏。管理员已经知道表已破坏，用诸如vi或Emacs等编辑器试图直接编辑它们，这对表绝对不是件好事!

备份数据库两个主要方法是用mysqldump程序或直接拷贝数据库文件(如用cp、cpio或tar等)。每种方法都有其优缺点：

mysqldump与MySQL服务器协同操作。直接拷贝方法在服务器外部进行，并且你必须采取措施保证没有客户正在修改你将拷贝的表。如果你想用文件系统备份来备份数据库，也会发生同样的问题：如果数据库表在文件系统备份过程中被修改，进入备份的表文件主语不一致的状态，而对以后的恢复表将失去意义。文件系统备份与直接拷贝文件的区别是对后者你完全控制了备份过程，这样你能采取措施确保服务器让表不受干扰。

mysqldump比直接拷贝要慢些。

mysqldump生成能够移植到其它机器的文本文件，甚至那些有不同硬件结构的机器上。直接拷贝文件不能移植到其它机器上，除非你正在拷贝的表使用MyISAM存储格式。ISAM表只能在相似的硬件结构的机器上拷贝。在MySQL3.23中引入的MyISAM表存储格式解决了该问题，因为该格式是机器无关的，所以直接拷贝文件可以移植到具有不同硬件结构的机器上。只要满足两个条件：另一台机器必须也运行MySQL3.23或以后版本，而且文件必须以MyISAM格式表示，而不是ISAM格式。

不管你使用哪种备份方法，如果你需要恢复数据库，有几个原则应该遵守，以确保最好的结果：

定期实施备份。建立一个计划并严格遵守。

让服务器执行更新日志。当你在崩溃后需要恢复数据时，更新日志将帮助你。在你用备份文件恢复数据到备份时的状态后，你可以通过运行更新日志中的查询再次运用备份后面的修改，这将数据库中的表恢复到崩溃发生时的状态。

以文件系统备份的术语讲，数据库备份文件代表完全倾倒(fulldump)，而更新日志代表渐进倾倒(incrementaldump)。

使用一种统一的和易理解的备份文件命名机制。象backup1、buckup2等不是特别有意义。当实施你的恢复时，你将浪费时间找出文件里是什么东西。你可能发觉用数据库名和日期构成备份文件名会很有用。例如：

%mysqldumpsamp_db>/usr/archives/mysql/samp_db.-10-02

%mysqldumpmenagerie>/usr/archives/mysql/menagerie.1999-10-02

你可能想在生成备份后压缩它们。备份一般都很大!你也需要让你的备份文件有过期期限以避免它们填满你的磁盘，就象你让你的日志文件过期那样。

用文件系统备份备份你的备份文件。如果遇上了一个彻底崩溃，不仅清除了你的数据目录，也清除了包含你的数据库备份的磁盘驱动器，你将真正遇上了麻烦。

也要备份你的更新日志。

将你的备份文件放在不同于用于你的数据库的文件系统上。这将降低由于生成备份而填满包含数据目录的文件系统的可能性。

用于创建备份的技术同样对拷贝数据库到另一台机器有用。最常见地，一个数据库被转移到了运行在另一台主机上的服务器，但是你也可以将数据转移到同一台主机上的另一个服务器。

1使用mysqldump备份和拷贝数据库

当你使用mysqldumo程序产生数据库备份文件时，缺省地，文件内容包含创建正在倾倒的表的CREATE语句和包含表中行数据的INSERT语句。换句话说，mysqldump产生的输出可在以后用作mysql的输入来重建数据库。

你可以将整个数据库倾倒进一个单独的文本文件中，如下：

%mysqldumpsamp_db>/usr/archives/mysql/samp_db.1999-10-02

输出文件的开头看起来象这样：

#MySQLDump6.0##Host:localhostDatabase:samp_db#-------------

---------

关 键 字：MYSQL

篇2：mysql数据库

使用phpmyadmin，这是最简单的了，修改mySQL库的user表，

不过别忘了使用PASSWORD函数。

篇3：MySQL数据库系统安全分析

My SQL是一个快速的、健壮的、多用户的、非常容易使用的、多线程的SQL数据库服务器, 它可以作为各种操作系统的关系数据库系统, 并且它是以客户机/服务器结构的形式实现的, 属于一种分布式数据库管理系统。My SQL的基本组成是:一个服务器守护程序mysqld、若干不同的客户程序和库。另外My SQL还提供多个应用程序编程接口 (API) , 能方便地开发各种客户端应用程序[1]。

在My SQL服务器中, 由它管理的所有数据都被存放在My SQL的数据目录中, (在liunx操作系统中, 为指定安装目录下的/var目录, 以下简称为Datadir) 通过一个单个实体对数据目录中的所有数据进行管理。在My SQL服务器中, 在客户机应用程序和其所需要的数据之间起着桥梁的作用的是mysqld实体。

在Datadir中, 存放有被服务器所管理的数据库和表, 它们以层次结构的方式进行存放, 这种层次结构的方法可以通过UNIX操作系统或Windows文件系统的树型结构用简单的方式来实现: (1) 让每一个数据库对应于该数据目录下的一个目录, 以数据库名存放于Datadir下。 (2) 数据库中的每一个表对应于数据库目录中的每一个文件。数据库中的每一个表在数据库目录下以3个文件存放:一个格式描述文件.FRM, 一个数据文件.MYO, 一个索引文件.MYI。[2]

2 My SQL安全机制

2.1 访问控制

My SQL提供了简单但是却十分有效的安全机制。My SQL数据库管理员有责任保护其数据库中数据的安全性, 使得只有那些经过严格认证的用户才能访问该数据库中的记录, 这种保护包括内部安全性和外部安全性两个方面。

2.1.1 My SQL内部安全机制

My SQL的内部安全机制最主要的是考虑文件系统这一级别的问题, 例如可以防止具有运行My SQL服务器权限的用户对My SQL数据目录进行攻击。但是, 如果My SQL数据库管理员对于My SQL数据目录中的文件的授权太过随便的话, 那么就可以将这些文件对应的形成一个简单的替换, 如果出现这种情况的话, My SQL内部安全机制将不能保证对客户的授权表进行准确的控制。[1]

为了能够对访问My SQL服务器中数据的网络建立可靠的安全性, My SQL数据库管理员必须做到:使该服务器上的用户对数据目录中的内容的不能直接访问。数据库管理员应该对以下内容进行必要的保护:1) 数据库文件。数据库管理员应该保证数据库中数据的专有性, 同时还必须保证数据库中的数据不会因为数据库目录的安全性能低而被泄露。2) 日志文件。由于在常规的和更新的日志文件中包含有大量的查询文件, 这些信息都是以纯文本形式进行记录, 一个对该日志具有访问权限的攻击者, 只需要在日志中对GRANT或PASSWORD等执行grep命令就可以找到他所需要的敏感信息, 另外具有访问这些日志文件权限的人都可以通过这些日志文件监控一切发生在数据库中的相关事务, 因此数据库管理员也必须要对日志文件进行必要的保护。

因此我们必须要用操作系统的相关权限对文件系统的权限进行必要的设置, 并且确保以特定的My SQL管理组管理特定的My SQL用户, 从而做到每一个数据目录仅仅只有一个用户具有它的访问权。

2.1.2 My SQL外部安全机制

My SQL外部安全机制是指阻止客户通过网络对My SQL服务器进行攻击, 我们必须设置My SQL授权表, 以保证只有合法的用户才能访问由My SQL服务器管理的数据库中的相关内容[3]。

My SQL服务器通过My SQL授权表建立了一套先进的安全的授权系统, 并且数据库管理员可以通过My SQL授权表对用户的权限进行设置和更改。虽然该授权系统没有提供通过网络访问数据库中数据的安全问题, 但是, 如果与对直接访问数据目录内容的其他用户的主机服务器, 将无法访问数据网络, 以建立良好的安全性。除非数据库管理员知道已经注册的并且正在运行的My SQL服务器只有一台, 否则他还必须注意其他用户对该数据目录进行访问的可能性。

在My SQL数据库的授权表中, 存在有user、host、db、tables_priv和columns_priv等表格, 这些授权表在首次安装My SQL的过程中就已经进行了初始化。在网络中, 连接到该My SQL服务器的所有客户机都可以通过这些授权表来控制对My SQL数据库的访问。

user表列出了能够访问该服务器的所有用户和其对应的密码, 并且还指定了这些用户分别有哪些权限。在user表中, 所有的权限都属于全局权限, 并且它的作用范围为整个数据库。

db表列出所有的数据库并且为每一个数据库指出有权限访问的用户, 在这里指定的权限, 对于数据库中所有的表都适用。

host表通常情况下和db表结合起来使用, 以达到在更高的级别上实现对特定主机的数据库访问权限进行控制, 这个表不受GRANT和REVOKE等语句的影响。

tables_priv表用来指定数据库中表的权限。在tables_priv表中指定的权限对整个表中的任何列都起作用。

columns_priv表用来指定数据库中表的每一列的权限。在columns_priv表中指定的权限只适用于该表中的某一列。

另外, 在授权表中的列有两种类型:确定某个权限什么时间有效的范围列和确定给用户给予什么权限的权限列。对于数据库和表的权限主要有CREATE (可以创建数据库和表, 但不能建立索引) , ALTER (可以用ALTER TABLE语句) , DROP (可以删除数据库和表, 但不可以删除索引) , DELETE (可以从表中删除现有的记录) , UPDATE (可以对已有的记录进行更改) , INDEX (可以创建和删除索引) , SELECT (可以通过SELECT语句从表中查询数据) 等。管理权限主要有SHUTDOWN (可以通过mysqladmin shutdown关闭服务器) , FILE (可以对服务器主机上的文件进行读写) , GRANT (可以将自己拥有的权限分给别人使用) , RELOAD (可以执行服务器的管理命令) 。

在访问My SQL服务器时, 客户机的访问控制主要分为两个阶段。第一阶段在客户机和服务器进行连接时。服务器在授权表的user表中进行查询, 看能否找到与当前的用户名和他所提供的密码一致的数据项, 若找不到, 则不能与服务器建立连接。若找到, 则可以和服务器建立连接, 并且进入第二个阶段。在这个阶段中, 对客户机发出的每一个命令请求, My SQL服务器都会通过自己的授权表进行核对以确定该用户是否有权限来执行这一个命令请求, 这种核对一直会进行到客户机与服务器断开连接为止。[1]

2.2 数据库的维护与数据库的修复

2.2.1 日志文件

My SQL服务器生成的日志文件主要有两种形式:1) 常规日志文件:可以把客户机对My SQL数据库的所有操作进行记录和跟踪。这种日志文件可以用来对服务器的活动进行跟踪:那个用户在连接服务器、从什么地方连接服务器, 在对服务器进行哪些操作。[2]2) 更新日志。它可以把对数据库进行修改的查询进行记录。所有更新的日志文件用SQL语句的形式进行书写, 这些语句用作对My SQL的输入, 当My SQL服务器瘫痪时, 可以利用该日志文件将系统恢复到崩溃时的完好状态。

为了使My SQL服务器日志文件有效, 在对My SQL服务器设置启动方式时, 应使用--log选项把常规的日志文件开启, --logupdate选项把更新的日志文件开启。在My SQL服务器运行的过程中, 为了保证不会因为产生大量的日志文件而填满整个磁盘空间, 尤其是在服务器正在对许多查询进行处理的情况下, 应当使用日志文件的循环和截止时间, 在避免产生大量的日志文件的同时保持最近的几个日志文件是联机的, 并且是可用的[2]。

2.2.2 数据库表的检查和维护

在My SQL数据库中, 对表的检查和维护的主要步骤为:[5]

1) 对发生错误的表进行检查。如果对这个表的检查能够通过的话, 则任务完成, 如果不能通过, 则必须对这个出错的表进行修复。

2) 为了防止数据丢失, 在对出错的表进行修复之前必须对这个表进行拷贝保存。

3) 对出错的表进行修复。

4) 如果对这个表的修复不成功, 则需要通过备份的数据库和更新的日志文件来恢复此表。

以上步骤中的第4部是在已经对数据库进行了备份和更新日志文件有效的前提下进行的。如果这两个条件不能满足的话, My SQL数据库系统将会有大的风险。

2.2.3 数据库备份与数据库恢复

对数据库进行备份主要有两种方法:一是用mysql dump程序进行数据备份, 这种方法需要与My SQL服务器共同进行;二是对数据库文件直接进行拷贝, 这种方法不需要mysql服务器的支持, 但是必须要保证在进行文件备份的过程中没有其他用户修改被备份的数据。在对数据进行备份应注意以下几点:1) 对数据进行备份必须要定期进行。2) 应该执行更新日志。当我们使用已经做好的备份文件将数据库中的数据还原后, 还可以执行更新日志中的相关查询操作将数据库中的相关表也还原到瘫痪时刻的状态。在文件系统的备份语言中, 数据库备份文件则表示完全转储 (full dump) , 而更新日志则表示增量转储。3) 对更新日志进行备份。如果My SQL服务器中的数据目录以及包含数据库备份的磁盘驱动器都受到了损坏, 那所造成的后果将是非常严峻的, 因此, 我们还必须对更新日志进行备份。4) 应该将备份的文件和数据库放在不同的文件系统上。通过这种方法可以极大的降低含有数据字典的文件系统被生成的备份文件填满的可能性。5) 我们应根据服务器的工作状况和为保持旧日志所提供的磁盘空间的大小来决定日志文件循环的频率和对旧日志文件进行保存的数量。

如果My SQL服务器中的数据库已经毁坏, 那么我们就需要对这个数据库进行恢复。恢复数据库的过程主要有两个途径:备份文件和更新日志。利用备份文件可以将表恢复到进行该备份时的状态, 而在备份到发生故障的这段时间内, 对表进行修改的结果将会丢失。更新日志中有对数据库中的表进行修改的所有查询结果, 我们可以利用更新日志作为对My SQL的输入来重新执行这些查询。

3 My SQL安全机制的特点

笔者经过多年的实践经验, 将My SQL和其它的大中型数据库系统的区别总结如下:

(1) 访问控制:My SQL采用灵活的、简单的、非标准化的访问控制方式, 它的控制字段可以是普通的数据库对象, 也可以是访问该服务器的客户机的IP地址。目前的一些其它的数据库系统大多采用基于角色的访问控制方式, 而My SQL没有角色这一概念, 这也是My SQL数据库系统在安全控制方面的一个缺陷, 但是可以在对应的应用程序上弥补这一缺陷。

(2) 审计功能:对于大中型的数据库系统而言, 审计功能是必要的。但My SQL数据库系统不具有审计的功能。但是My SQL数据库系统中的日志文件对数据库的一切操作都进行了记录, 因此, 利用My SQL日志文件和操作系统日志文件相结合的方式也可以方便的实现一些基本的审计功能。

(3) My SQL4.0版本可以支持两种不同类型的表:transcation_safe tables类型和transcation_unsafe类型。transcation_safe类型的表不再以简单的、单一的文件方式表示关系, 而是采用自己的缓冲池高速缓存数据。而transcation_unsafe类型不支持事务处理, 采用原子操作。笔者觉得在某些事务处理比较繁杂的系统中, 可使用transcation_safe table类型来维护系统数据完备性。而对于普通系统, 为了提高查询的速度, 可以使用transcatioan_unsafe table类型。

4 结束语

如今, My SQL数据库系统的发展越来越完善, 已经有了一套比较完善的安全机制。利用这些安全机制, 一方面可以极大的提高数据库系统的安全性, 另一方面对系统的存取效率也有一定的影响。但总的来说, 由于My SQL数据库系统具有不同于其他的数据库系统的控制方式, 并且My SQL数据库系统也容易扩展, 与Linux系统可以灵活方便的结合, 使得其前景越来越广阔。

参考文献

[1]Paul DuBios, MYSQL网络数据库指南[M].北京:机械工业出版社, 2001.

[2]mysgl官方网站[EB/OL].www.mysgl.com.

[3]朱鲁华, 陈荣良.数据库加密系统的设计与实现[J].计算机工程, 2002, 28 (8) .

[4]王晓峰, 王尚平, 秦波.数据库加密方法研究[J].西安理工大学学报, 2008 (3) .

篇4：mysql数据库

关键词：触发器；trigger；MySQL

中图分类号：TP309

1 触发器的概念

触发器是一个被指定关联到一个表的数据库对象，当对一个表的特定事件出现时，它将被激活。触发器可以看作是特殊的存储过程，不同的是，执行存储过程要使用call语句来调用，只要当一个预定义的事件发生的时候，触发主体动作就会被MySQL自动调用。具体而言，触发器就是MySQL响应insert、update和delete语句而自动执行的一条或一组MySQL语句。

2 触发器的用途

（1）它可用于检查插入到表中的值。例如：每当增加一个学生到数据库的学生基本信息表时，都检查其电话号码的格式是否正确。（2）它能对更新涉及的值进行计算。例如：每当客户订购一个产品时，都从产品库存表中将库存量减去订购的数量。（3）用于创建审计跟踪，也就是可使用触发器把表的更改状态以及之前和之后的状态记录到另外一张数据表中。正确的使用触发器，这能保障数据库中数据的完整性正确性，以及多个表之间数据的一致性。

3 触发器的创建

语法格式：Create [DEFINER={user|CURRENT_USER}] TRIGGER t_name t_time t_event ON tbl_name FOR EACH ROW t_body

其说明如下：DEFINER从句：指定当触发器被触发时满足事件执行权限的安全上下文，为可选项。t_name：触发器的名称，触发器在当前数据库中必须具有唯一的名称。t_time：触发器被触发的时机，有before和after两个选项。t_event：触发事件，包括insert、update和delete，指定激活触发器的语句种类。tbl_name：标识建立触发器的表名。FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器。t_body：指定触发器被触发后执行的语句，称为触发器主体。当触发器主体有多条语句时，将要执行的多条语句放begin和end語句之间，各语句之间要用分隔符“；”隔开。

4 触发器的查看

查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等。查看当前数据库中的触发器语句为：“show TRIGGERS”，但执行该语句显示的触发器信息较为混乱，可以在show TRIGGERS命令后加“＼G”，这样显示的信息就相对更有条理。在MySQL数据库中，所有的触发器定义都存放在系统自带的数据库information_schema的triggers表中，所以可以通过语句“select * from information_schema.triggers where trigger_name=触发器名称”查看满足指定条件的触发器，该方法更为实用。

5 触发器的使用

触发器是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。根据触发的事件，触发器可分为insert、delete和update触发器。

5.1 insert触发器。insert触发器可在insert语句执行之前或之后执行。需要注意以下几点：在insert触发器代码内，可引用一个名为new的虚拟表，来访问被插入的行。在before insert触发器中，new中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。对于auto_increment列，new在insert执行之前包含的是0值，在insert执行之后将包含新的自动生成值。

5.2 delete触发器。delete触发器可在delete语句执行之前或之后执行。在delete触发器代码内，可以引用一个名为old的虚拟表，来访问被删除的行。但old中的值全部是只读的，不能被更新。

5.3 update触发器。相比insert触发器和delete触发器，update触发器既可使用new虚拟表，也可以使用old虚拟表。new虚拟表可以访问更新后的值，而old虚拟表可以访问（update语句执行前）的值。同样，old中的值全部是只读的，不能被更新。特别要注意的是，当触发器涉及对触发表自身的更新操作时，只能使用before update触发器，而after update触发器将不被允许。

5.4 触发器应用举例。某进销存数据库（db_jxc）中有业务统计表（persons）和销售额表（sales）等表。表person有销售人姓名、销售数量字段。表sales有姓名、销售金额字段。要求每更新一次person表后，都要更新sales表对应的销售金额字段（销售金额=销售数量*20）。

经分析，可用触发器来实现，操作步骤如下：（1）创建数据库：create database db_jxc；（2）选择数据库：use db_jxc；（3）创建表：Create table persons（name char（10），nun int）；Create table sales（name char（10），sum int）；（4）创建触发器：Create TRIGGER nun_sum after insert on persons FOR EACH ROW INSERT into sales values（new.name，20*new.nun）；（5）向表persons中插入记录：Insert into persons values（‘jack，50），（‘luck，47）；（6）检查触发器的执行情况，如图1所示。

6 触发器的删除

删除触发器指删除原来已经在某个数据库中创建的触发器，与MySQL中删除数据库的命令相似。

格式：drop TRIGGER [if exists] [schema_name.]trigger_name；

If exists为可选项，用于避免删除没有的数据库。可选项schema_name用于指定触发器所在的数据库的名称，若没有指定，则默认为当前数据库。trigger_name指定要删除的触发器的名称。当删除一个表的同时，也会自动地删除该表上的触发器。

另外，触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。在应用完触发器后，切记一定要将触发器删除，否则在执行某些数据库操作时，会造成数据的变化。

7 使用触发器的注意事项

在使用触发器的时候需要注意：（1）在触发器的创建过程中，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器，即insert、update和delete的之前与之后。（2）及时删除不再需要的触发器。触发器定义之后，每次执行触发事件，都会激活触发器并执行触发的主体动作。如果需求发生变化，而触发器没有进行相应的改变或删除，则触发器仍然会执行旧的语句，从而影响新的数据的完整性。

8 结束语

本文介绍了触发器的用途、创建、使用方法等，案例代码经多次调试运行无错误。触发器能有效地保护数据库中的数据，能解决一些复杂的问题。正确使用触发器是MySQL数据库编程人员必备的素质。

参考文献：

[1]MySQL数据库程序设计[M].北京：高等教育出版社，2013.

[2]刘增杰，张少军.MySQL5.5从零开始学[M].北京：清华大学出版社，2012.

作者简介：徐安令（1979-），云南宣威人，讲师，计算机应用技术工程硕士，研究方向：数据库应用与程序设计。

篇5：mysql数据库

mySQL> GRANT USAGE ON *.* TO jeffrey@“%” IDENTIFIED BY iscuit

这里PASSWORD函数是不必要的，也不需要使用FLUSH PRIVILEGES。

注意： PASSWORD() [不是]以在Unix口令加密的同样方法施行口令加密。

篇6：mysql数据库

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快，因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

2、使用连接(JOIN)来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

DELETE FROM customerinfo

WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN).. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT * FROM customerinfo

WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用连接(JOIN).. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT * FROM customerinfo

LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.

CustomerID

WHERE salesinfo.CustomerID IS NULL

连接(JOIN).. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3、使用联合(UNION)来代替手动创建的临时表

MySQL 从 4.0 的版本开始支持 UNION 查询，它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用 UNION 来创建查询的时候，我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了，要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。

SELECT Name, Phone FROM client

UNION

SELECT Name, BirthDate FROM author

UNION

SELECT Name, Supplier FROM product

4、事务

尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一

篇7：mysql数据库常用语句

SQL分类：

DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)

首先,简要介绍基础语句：

1、说明：创建数据库

CREATE DATABASE database-name

2、说明：删除数据库 drop database dbname

3、说明：备份sql server---创建 备份数据的 device USE master EXEC sp_addumpdevice ‟disk‟, ‟testBack‟, ‟c:mssql7backupMyNwind_1.dat‟---开始 备份

BACKUP DATABASE pubs TO testBack

4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根据已有的表创建新表：

A：create table tab_new like tab_old(使用旧表创建新表)B：create table tab_new as select col1,col2… from tab_old definition only

5、说明：

删除新表：drop table tabname

6、说明：

增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：

添加主键：Alter table tabname add primary key(col)说明：

删除主键：Alter table tabname drop primary key(col)

8、说明：

创建索引：create [unique] index idxname on tabname(col….)删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。

9、说明：

创建视图：create view viewname as select statement 删除视图：drop view viewname

10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围

插入：insert into table1(field1,field2)values(value1,value2)删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ‟%value1%‟---like的语法很精妙，查资料!排序：select * from table1 order by field1,field2 [desc] 总数：select count * as totalcount from table1 求和：select sum(field1)as sumvalue from table1平均：select avg(field1)as avgvalue from table1 最大：select max(field1)as maxvalue from table1 最小：select min(field1)as minvalue from table1

11、说明：几个高级查询运算词 A： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时(EXCEPT ALL)，不消除重复行。C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时(INTERSECT ALL)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

12、说明：使用外连接 A、left outer join：

左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c B：right outer join:

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。C：full outer join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

其次，大家来看一些不错的sql语句

1、说明：复制表(只复制结构,源表名：a 新表名：b)(Access可用)

法一：select * into b from a where 1<>1

法二：select top 0 * into b from a

2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b)(Access可用)insert into b(a, b, c)select d,e,f from b;

3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)(Access可用)insert into b(a, b, c)select d,e,f from b in „具体数据库‟ where 条件 例子：..from b in ‟“&Server.MapPath(”.“)&”data.mdb“ &”‟ where..4、说明：子查询(表名1：a 表名2：b)select a,b,c from a where a IN(select d from b)或者: select a,b,c from a where a IN(1,2,3)

5、说明：显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate)adddate from table where table.title=a.title)b

6、说明：外连接查询(表名1：a 表名2：b)select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、说明：在线视图查询(表名1：a)select * from(SELECT a,b,c FROM a)T where t.a > 1;

8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括 select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 数值1 and 数值2

9、说明：in 的使用方法

select * from table1 where a [not] in(„值1‟,‟值2‟,‟值4‟,‟值6‟)

10、说明：两张关联表，删除主表中已经在副表中没有的信息

delete from table1 where not exists(select * from table2 where table1.field1=table2.field1)

11、说明：四表联查问题：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where.....12、说明：日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff(‟minute‟,f开始时间,getdate())>5

13、说明：一条sql 语句搞定数据库分页

select top 10 b.* from(select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc)a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

14、说明：前10条记录

select top 10 * form table1 where 范围

15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)select a,b,c from tablename ta where a=(select max(a)from tablename tb where tb.b=ta.b)

16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA)except(select a from tableB)except(select a from tableC)

17、说明：随机取出10条数据

select top 10 * from tablename order by newid()

18、说明：随机选择记录 select newid()

19、说明：删除重复记录

Delete from tablename where id not in(select max(id)from tablename group by col1,col2,...)20、说明：列出数据库里所有的表名 select name from sysobjects where type=‟U‟

21、说明：列出表里的所有的

select name from syscolumns where id=object_id(‟TableName‟)

22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

select type,sum(case vender when ‟A‟ then pcs else 0 end),sum(case vender when ‟C‟ then pcs else 0 end),sum(case vender when ‟B‟ then pcs else 0 end)FROM tablename group by type 显示结果：

type

vender pcs 电脑

A 电脑

A 光盘

B 光盘

A 手机

B 手机

C

23、说明：初始化表table1 TRUNCATE TABLE table1

24、说明：选择从10到15的记录

select top 5 * from(select top 15 * from table order by id asc)table_别名 order by id desc 随机选择数据库记录的方法（使用Randomize函数，通过SQL语句实现）

对存储在数据库中的数据来说，随机数特性能给出上面的效果，但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决方案是建立如下所示的循环： Randomize RNumber = Int(Rnd*499)+1 While Not objRec.EOF If objRec(“ID”)= RNumber THEN...这里是执行脚本...end if objRec.MoveNext Wend

这很容易理解。首先，你取出1到500范围之内的一个随机数（假设500就是数据库内记录的总数）。然后，你遍历每一记录来测试ID 的值、检查其是否匹配RNumber。满足条件的话就执行由THEN 关键字开始的那一块代码。假如你的RNumber 等于495，那么要循环一遍数据库花的时间可就长了。虽然500这个数字看起来大了些，但相比更为稳固的企业解决方案这还是个小型数据库了，后者通常在一 个数据库内就包含了成千上万条记录。这时候不就死定了？

采用SQL，你就可以很快地找出准确的记录并且打开一个只包含该记录的recordset，如下所示： Randomize RNumber = Int(Rnd*499)+ 1

SQL = “SELECT * FROM Customers WHERE ID = ” & RNumber

set objRec = ObjConn.Execute(SQL)Response.WriteRNumber & “ = ” & objRec(“ID”)& “ ” & objRec(“c_email”)

不必写出RNumber 和ID，你只需要检查匹配情况即可。只要你对以上代码的工作满意，你自可按需操作“随机”记录。Recordset没有包含其他内容，因此你很快就能找到你需要的记录这样就大大降低了处理时间。

再谈随机数

现在你下定决心要榨干Random 函数的最后一滴油，那么你可能会一次取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random 示例扩展一下就可以用SQL应对上面两种情况了。

为了取出几条随机选择的记录并存放在同一recordset内，你可以存储三个随机数，然后查询数据库获得匹配这些数字的记录：

SQL = “SELECT * FROM Customers WHERE ID = ” & RNumber & “ OR ID = ” & RNumber2 & “ OR ID = ” & RNumber3

假如你想选出10条记录（也许是每次页面装载时的10条链接的列表），你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这一操作可以通过好几种方式来完成，但是 SELECT 语句只显示一种可能（这里的ID 是自动生成的号码）：

SQL = “SELECT * FROM Customers WHERE ID BETWEEN ” & RNumber & “ AND ” & RNumber & “+ 9”

注意：以上代码的执行目的不是检查数据库内是否有9条并发记录。

随机读取若干条记录，测试过

Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id)Sql server:select top n * from 表名 order by newid()mysql select * From 表名 Order By rand()Limit n

Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试, 现在记下以备后查)

语法 select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where...使用SQL语句 用...代替过长的字符串显示

语法：

SQL数据库：select case when len(field)>10 then left(field,10)+‟...‟ else field end as news_name,news_id from tablename

Access数据库：SELECT iif(len(field)>2,left(field,2)+‟...‟,field)FROM tablename;

Conn.Execute说明

Execute方法

该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集，该方法的使用格式分为以下两种：

1．执行SQL查询语句时，将返回查询得到的记录集。用法为：

Set 对象变量名=连接对象.Execute(“SQL 查询语言”)

Execute方法调用后，会自动创建记录集对象，并将查询结果存储在该记录对象中，通过Set方法，将记录集赋给指定的对象保存，以后对象变量就代表了该记录集对象。

2．执行SQL的操作性语言时，没有记录集的返回。此时用法为：

连接对象.Execute “SQL 操作性语句” [, RecordAffected][, Option] ·RecordAffected 为可选项，此出可放置一个变量，SQL语句执行后，所生效的记录数会自动保存到该变量中。通过访问该变量，就可知道SQL语句队多少条记录进行了操作。

·Option 可选项，该参数的取值通常为adCMDText，它用于告诉ADO，应该将Execute方法之后的第一个字符解释为命令文本。通过指定该参数，可使执行更高效。

·BeginTrans、RollbackTrans、CommitTrans方法

这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开始一个事物；RollbackTrans用于回滚事务；CommitTrans用于提交所有的事务处理结果，即确认事务的处理。

事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并恢复到处里前的状态。

篇8：基于MySQL数据库的优化

1 基础优化

1.1 My SQL存储引擎的选择

My SQL默认配置了许多不同的存储引擎[1], 可以预先设置或者在My SQL服务器中启用。其类型有:My ISAM、Inno DB、MERGE、MEMORY (HEAP) 、BDB (Berkeley DB) 、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。每一种类型, 都有其特别之处, 这里对几种常用的存储引擎进行分析其余的都略作解释:

My ISAM[2]。适用于以读为主, 数据一致性要求不是太高。

(1) 不支持事务。表级锁定:其锁定机制是表级索引, 这虽然可以让锁定的实现成本很小但也同时大幅降低了其并发性能。读写互相阻塞:不仅会在写入时阻塞读取, My ISAM还会在读取的时候阻塞写入, 但读本身并不会阻塞另外的读。Inno DB:行级锁定对高并发有较好的适应能力, 但需要确保查询是通过索引完成, 适用于数据更新较为频繁的场景。具有较好的事务支持:支持4个事务隔离级别, 支持多版本读。行级锁定:通过索引实现, 全表扫描仍然会是表锁, 注意间隙锁的影响。

(2) 读写阻塞与事务隔离级别相关。具有高效的缓存特性:能缓存索引, 也能缓存数据。BDB:可替代Inno DB的事务引擎, 支持COMMIT、ROLLBACK和其他事务特性。Memory:将所有数据保存在RAM中, 在需要快速查找引用和其他类似数据的环境下, 可提供快速的访问。Merge:允许My SQL DBA或开发人员将一系列等同的My ISAM表以逻辑方式组合在一起, 并作为1个对象引用它们。对于诸如数据仓储等VLDB环境较适合。Archive:为大量较少引用的历史、归档、或安全审计信息的存储和检索提供了良好的解决方案。Federated:能将多个分离的My SQL服务器链接起来, 从多个物理服务器创建一个逻辑数据库。适合于分布式环境或数据集市环境。Cluster/NDB:My SQL的簇式数据库引擎, 尤其适合于具有高性能查找要求的应用程序, 这类查找需求还要求具有最高的正常工作时间和可用性。Other:其他存储引擎包括CSV, Blackhole, 以及Example引擎。

显然My SQL为对应各种不同的状况, 其本身便已划分了很多存储引擎, 选择合适的存储引擎不仅可以获得稳定的服务还能额外增长数据库处理数据的速度

1.2索引优化

My SQL建有索引机制, 而所谓的索引就是用于快速地寻找那些具有特定值的记录, 所以My SQL索引都以B-树的形式保存。如图1所示。

现有100个学生的成绩, 当执行Select语句查询不及格的学生有多少时, 若没有索引, My SQL便会遍历全表, 逐个比较。但若在Achievement上建立索引, My SQL便会先查询索引里符合条件的同学, 然后再执行数据库读取指定的学生数据, 也就是说, 数据库的读写操作次数变少了。当然在表格数据较少的情况下, 区别不大。

但若假设现在数据库中建有50万张数据的表, 如果没有索引, My SQL将会从头到尾检索一次, 这样的执行效率将会相当低。可见索引的存在将会大幅的提高检索效率, 当然索引也存在一些缺点。索引文件要占磁盘空间。如果有大量的索引, 索引文件可能会比数据文件更快地达到最大的文件尺寸。其次, 索引文件降低了大多数涉及写入的操作的时间, 因为写操作不仅涉及数据行, 而且还常涉及索引。

1.3 存储过程

存储过程[3]即为以后使用而保存一条或多条My SQL语句的集合, 即批文件。使用存储过程的优点即简单、安全、高性能, 而且重要的是与基本的SQL语句相比, 它的处理速度更快, 但其编写较SQL语句更复杂, 对编写者有较高的要求。

1.4 硬件上的提升

以空间换时间, 或以时间换空间, 可谓是伴随着计算机诞生以来一直存在的事物。如果只是学习, 任何一台老式计算机做服务器都没有问题, 因为不必担心处理数据耗费的时间问题, 但是作为一个高访问量的网站, 一台专门的数据库服务器是必要的。以更大的内存节省计算机计算时间, 以更出色的硬件性能来完成数据库的存储问题。

2 Sharding

Sharding意为数据切分, 涉及的具体步骤是分库分表。数据库的Sharding分为2种, 水平切分和垂直切分[4]。前者适合表不多, 但每张表的数据较多, 后者适合因为表多而导致数据也多, 当然多数时候两者可以结合, 如图2所示, DB指数据库端, Shard指切分后的数据库。

2.1 垂直切分

所谓垂直切分是将组成数据库的表垂直的切开, 然后将其分散到多台数据库主机上。例如现有3张表, Student, Teacher, Achievement。那么Student和Achievement需分到一个库, Teacher分到另一库。这样分的原因是表关联是无法在数据库级别完成的, 所以垂直切分中分库的表都是没有关联的。

2.2 水平切分

水平切分是给一个表规定适合的记录数, 把超过记录数的记录放入新的表中。例如现在有一张记有500万数据量的单张数据表, 切分成10份, 分别放入Table1, Table2以此类推至Table10, 那么当往表里再更新数据时, 就是往任一一个只有50万的数据表中插入数据, 此时更改的索引量与原来500万相比将呈指数级下降。实际上便是分表操作。

当然水平切分主要有两种思路[5]:一种是使用MD5哈希, 做法是对UID进行md5加密, 取前几位, 然后就可以将不同的UID哈希到不同的用户表 (user_xx) 中, 如图3所示。

然而这种算法无法扩展表, 一旦用户数量过多, 则会造成单张表单数据量过大的问题。另一种则是使用移位, 方法如图4所示。

如图所示, 其意思是将uid向右移动20位, 这样就可以将约前100万的用户数据放在第一个表user_000, 第二个100万的用户数据放在第二个表user_001中, 如此循环。直到user_999, 可分1 000张, 若不够, 则改后缀即可。

3 集群

集群 (Cluster) 技术是使用特定的连接方式, 将价格相对较低的硬件设备结合起来, 同时也能提供高性能相当的任务处理能力。

如上文提到的提升硬件性能, 但是多数时候单台计算机已无法满足软件的需求, 便引入集群的概念, 即AP+DB的架构:AP指应用程序, DB指数据库端, AP放在一个服务器上, DB放在另一个服务器上, 又或者是多台服务器运行一个DB, 集群利用的是将一台计算机需要做的事, 分散到多台上去完成, 就可以更高效地完成单台计算机无法完成的事。

4 结束语

虽然数据库的优化手段有多种, 但在面对大流量、高并发量的数据时, 有时依然不能很好地解决问题, 硬件的读写速度从某种角度讲较大程度上限制了数据库的性能, 但以上的优化手段还是可以很好地提升其服务性能。

参考文献

[1]SABA P.深入理解MySQL核心技术[M].李芳, 于红芸, 邵健, 译.北京:中国电力出版社, 2009.

[2]简朝阳.MySQL数据库性能优化之存储引擎选择[EB/OL]. (2012-09-10) [2012-11-05]http://isky000.com/database/mysql-performance-tuning-storage-engine.

[3]刘晓霞, 钟鸣.MySQL必知必会[M].北京:人民邮电出版社, 2009.

[4]简朝阳.MySQL性能调优与架构设计[M].北京:电子工业出版社, 2012.

[5]Veda.数据库水平切分的两个思路[EB/OL]. (2012-03-02) [2012-11-07]http://www.nowamagic.net/librarys/veda/detail/1528.

[6]王威.MySQL数据源代码分析及存储引擎的设计[D].南京邮电大学, 2012.