MySQL 参考教程

内容如下

• MySQL配置
• 存储引擎
• 数据类型
• MySQL的多客户端
• 管理员的职责
• 重点提示还有等等。。。
• 附送MySQL迷你版参考。。。

关于MySQL

MySQL的确是世界上最受欢迎的开源数据库了，它的简单易用吸引了一大批开发人员，而且功能也足够的强大，许多知名的大型网站都使用它。

 

这个教程就是让你快速的了解MySQL比较受欢迎的特性。下面我们就来讨论关于配置，管理员工具，数据库备份，SQL以及用户管理。这个教程同样的也可以作为一个MySQL简单的参考。

 

配置

MySQL支持260多个配置的参数，可以配置内存，日志，错误报表等等。这些参数可以在启动MySQL的时候，可以动态的传入。但是这样做是非常麻烦的。我们可以在my.cnf里进行配置。

my.cnf文件

my.cnf影响MySQL的范围，是和my.cnf指定的位置有关

• /etc/my.cnf (windows下是c:\my.cnf或者windows的系统目录\my.ini)。 MySQL全局配置。所有安装在此服务器的MySQL都会首先查找个配置文件。要记住在windows版本中，这个文件是唯一被MySQL引用的文件。
  
   
• --defaults-extra-file=name MySQL服务器实例的作用范围(Server-instance scope)启动实例的时候可以指定配置文件。
• ~/.my.cnf 用户指定的范围。这个文件放在用户的目录下。

 

my.cnf文件语法

my.cnf文件是个文本文件。被分为了多个部分。每个部分的配置都是针对某一个client的如mysql,mysqldump都是client.举个例子

# All clients
[client]
port = 3306

# The mysql client
[mysql]
safe-updates

# The mysqldump client
[mysqldump]
quick

以上的例子可以针对不同的MySQL client进行配置。更详细的可以看本教程下面多client一节。

提示 ：在MySQL目录下有多个配置文件的模板。模板内部都有详细的配置介绍，可以用来参考。

 

查看配置参数

可以用如下的命令查看配置参数mysqladmin -u root -p variables ,或者在进入mysql中查看如mysql>SHOW VARIABLES ;可以用mysql>SHOW VARIABLES LIKE "key%"; 查看指定的参数。这个命令比较方便。

 

 

存储引擎(STORAGE ENGINES)

详细介绍查看官方文档:http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#federated-storage-engine

 

• ARCHIVE :这个引擎是针对大数据量存储的。如果用这个ARCHIVE只能insert和select.不能delete和update.
  
• BDB :全名叫做Berkeley DB.也是MySQL第一个支持有事物功能的引擎。但是在最新的5.1版中已经移除BDB引擎 。
• BLACKHOLE :BLACKHOLE引擎允许在数据没有错误的情况下insert操作，但是并不存储数据.看起来没啥用。但是在实践中还是能用得上，一般都是减轻数据的复制。详情看文档http://dev.mysql.com/doc/refman/5.0/en/blackhole-storage-engine.html
  
• CSV :全程Comma-separated values (CSV)。这种格式被许多应用程序所支持。MySQL CSV存储引擎就用这种格式来管理数据。EXCEL可以很方便的操作这种格式的数据。
  
• EXAMPLE :对于开发者而言，这个引擎是个示例。通过这个事例可以写自己的存储引擎。
• Falcon :这是MySQL6.0新增加的存储引擎。提升了数据的大数据量的存储和获取的性能，但是也牺牲了事物和日志的特性。
  
• FEDERATED: MySQL5.0所添加的引擎。存储引擎访问在远程数据库的表中的数据，而不是本地的表。
  
• InnoDB :MySQL中最受欢迎的支持事物的存储引擎。InnoDB提供了完整的commit,rollback,错误恢复机制等。
  
• Maria :MySQL6.06中新添加的引擎。以后会作为默认的引擎。
  
• MEMORY :把数据存放在内存中。方便数据快速读取。
  
• MERGE :这个引擎适用于访问一组具有相同数据结构的表。比如销售的数据，按月的名称来划分多个表。对这种引擎就比较适用了。
  
• MyISAM: MyISAM是MySQL默认的存储引擎。虽然不支持事物，但是这种引擎很方便管理和使用。
  

 

数据类型

Date和Time类型

1. DATE ：DATE类型的格式是YYYY-MM-DD。范围是1000-01-01到9999-12-31
2. DATETIME ：日期和时间的结合类型。格式是YYYY-MM-DD HH:MM:SS。范围1000-01-01 00:00:00 到 9999-12-31 23:59:59
3. TIME ：时间类型。范围-838:59-59 到 838:59:59
4. TIMESTAMP： 很像DATETIME类型。范围是1970-01-01 00:00:01 UTC 到 2038-01-09
   03:14:07。和DATETIME不同的是，它会自动被赋予一个当前的时间以及在INSERT和UPDATE的时候，也会自动更新。
5. YEAR ：年的类型。支持 YY和YYYY。YY格式的范围是70 (1970) 到69 (2069)。YYYY格式类型范围是1901
   到2155

Numeric 类型

1. BIGINT ：整型。范围很大。-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
2. BIT ：存取二进制。1 到64 bits
3. DECIMAL ：存取精确的数值。如货币。
4. FLOAT ：存取近似的数值。如FLOAT(5,3).存取11.1256的时候，就存为11.126.因为精度设为了3.
5. INT ：整型。范围-2,147,483,648 到2,147,483,647.
6. MEDIUMINT ：整型。范围-8,388,608 到8,388,607
7. SMALLINT ：整型。范围32,768 到32,767
8. TINYINT ：整型。范围-128 and 127.

String 类型

• BINARY：支持最高存储255bytes。和操作CHAR类型一样，只不过这个是个二进制字符串。
• BLOB /LONGBLOB /MEDIUMBLOB /TINYBLOB:存储二进制文件或者图片。限制为65545
• CHAR：存储0到255个字符。CHAR会提前分配内存大小，而不管存放的字符大小。如char(10)。会提前为10个字符分配内存空间。而不管实际存放的字符数。
• ENUM：枚举。按照预定义枚举类型来存储数据。预定义的最大元素个素是65535.分配元素的值可以是''和NULL.
• SET:SET很像ENUM.SET 列最多可以有64 个成员.字符串对象可以有零个或多个值.
• TEXT /LONGTEXT /MEDIUMTEXT /TINYTEXT:分别存储大小为65,534, 4,294,967,295, 16,777,215, 和 255 字符。
• VARBINARY：最高存储65,535bytes.和VARCHAR操作一样。只不过操作的是二进制字符串。
• VARCHAR：最高存储65,535字符。不像CHAR，它的内存分配和实际存储的字符数有关。

 

流行的管理工具

1. PHPMyAdmin http://www.phpmyadmin.net/
2. MySQL Administrator http://www.mysql.com/products/tools/administrator/
3. SQLyog http://www.webyog.com/en/

 

常见的MySQL Clients

MySQL提供了多种实用的Client.比如备份，管理MySQL服务器，转换表格式，压力测试等等。这部分就介绍下常用的Client.

1. my_print_defaults:输出my.conf的参数。
2. myisam_ftdump:显示MyISAM引擎表中的FULLTEXT索引信息。
3. myisamchk:查看，修复，优化MyISAM引擎表。
4. myisamlog:查看MyISAM日志文件。
5. myisampack:压缩MyISAM表。提高读的性能。
6. mysql:Mysql client.管理数据库，表，数据。调整MySQL的功能和性能。
7. mysql_config:编译MySQL的时候，用的上。
8. mysql_convert_table_format：改变表的存储引擎。
9. mysql_fix_extensions：转为标准的MyISAM引擎。在数据库操作系统移植中比较有用。
10. mysql_setpermission：封装了设置MySQL的权限。
11. mysqlaccess:查看User权限。
12. mysqladmin:管理员工具。
13. mysqlbinlog：用来查看二进制日志文件。
14. mysqlcheck：封装了Sql中的CHECK TABLE,REPAIR TABLE,ANAYE TABLE,和OPTIMIZE TABLE.
15. mysqldump：常用来备份。
16. mysqlhotcopy：常用来备份。
17. mysqlimport：封装了LOAT DATA INFILE SQL语句。
18. mysqlshow：封装了 show table语句。
19. mysqlslap:测试性能工具。
20. perror：了解错误信息。

管理员任务

登录MySQL 服务器

用mysql client登录MySQL服务器。一般都要提供MySQL的账户和密码

 

 

 

%>mysql -u username -p

Enter password: *****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.22-rc-community MySQL Community

Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear

the buffer.

mysql>

 

 

登陆成功后，可以选择一个数据库或者执行其他的任务。为了节省你的时间，你可以登录服务器的时候就选择数据库。

%>mysql -u username -p database_name

 

如果你连接的远程数据库，填写hostname或者IP地址。命令如下

%>mysql -h hostname -u username -p database_name

 

登出MySQL服务器用如下命令

mysql>quit

 

Bye

%>

 

 

修改MySQL 提示符

MySQL的提示符会提示你已经登录到MySQL的服务器了。你也可以修改这个提示符。命令如下：

 

mysql>prompt mysql (\U)>

mysql (root@localhost)>

 

 

参数如下

 

1. \c 计数器。记录你当前操作的次数。
2. \d 当前的数据库
3. \D 当前的日期
4. \h 服务器Host
5. \u 用户名称
6. \U 你的 username@hostname

数据库

• 创建数据库：1.mysql>CREATE DATABASE test;2. %>mysqladmin -u root -p create test
• 选择数据库：USE test
• 删除数据库：DROP DATABASE test

表

 

• 新建表：CREATE TABLE table_name (column1 definition,column2 definition,...columnN definition);
• 显示表结构：mysql>DESCRIBE table_name;
• 列出表：mysql>SHOW TABLES;或者mysql>SHOW TABLES FROM database_name;
• 修改表结构：
• 1.添加列：mysql>ALTER TABLE table_name ADD COLUMN column_name column_type           column_type_attributes;
• 2.删除列：mysql>ALTER TABLE table_name DROP COLUMN column_name;
• 3.修改列：mysql>ALTER TABLE table_name CHANGE COLUMN column_name column_name column_type column_type_attributes;
• 删除表：mysql>DROP TABLE table_name;
• 表的重命名：mysql>ALTER TABLE table_name RENAME new_table_name;

用户管理

MySQL有强大的安全模型。能控制用户任意的操作行为。比如用户能执行的命令和在一小时之内，限制用户查询的次数。这个模型是两步操作。

1. 验证身份(Authentication)。首先要检查主机，用户名，密码。如果验证通过，就会被授予权限。如果验证失败，用户连接MySQL就失败了。
2. 权限(Authorization)。用户执行的命令会被权限检查。如果用户有此权限，就可以执行，否则就被拒绝。

 

创建新用户

创建新用户有多种方法。最简单以及最不容易出错的方法就是用GRANT命令。命令如下

mysql>GRANT privilege1, privilege2, privilegeN ON database_name.*
->TO 'username'@'host' IDENTIFIED BY 'password';

 

示例,以下的命令创建一个名为nicholas的用户。并且授予对test数据库所有表的SELECT,INSERT,UPDATE权限。

mysql>GRANT SELECT, INSERT, UPDATE ON test.*
->TO 'nicholas'@'192.168.1.142' IDENTIFIED BY 'secret';

 

删除用户

删除用户有两种方法。使用哪一种方法，要看要删除什么样的内容。如果仅仅是删除用户的权限。可以使用REVOKE命令。

mysql>REVOKE ALL PRIVILEGES FROM 'nicholas'@'192.168.1.142';

 

要把用户彻底的删除用DROP USER命令。

mysql>DROP USER 'nicholas'@'192.168.1.142';

 

修改密码

使用SET PASSWORD命令。

mysql>SET PASSWORD FOR 'nicholas'@'192.168.1.142' = PASSWORD('supersecret');

 

授予权限

授予权限使用GRANT命令。

mysql>GRANT DELETE ON test.* TO 'nicholas'@'192.168.1.142';

 

删除权限

用REVOKE命令删除权限

mysql>REVOKE DELETE, UPDATE FROM 'nicholas'@'192.168.1.142';

 

授予表级别和列级别的权限

用GRANK和REVOKE命令可以控制表和列级别的权限。下面就是授予nicholas.test库中user表的INSERT权限。

mysql>GRANT INSERT ON test.user TO 'nicholas’@’192.168.1.142';

 

下面是列级别的示例.授予用户对user表age字段的INSERT和SELECT权限。

mysql>GRANT INSERT (age), SELECT (age) ON test.user
->TO 'nicholas'@'192.168.1.142';

对用户重命名

mysql>RENAME USER 'nicholas'@'192.168.1.142' TO 'xyf'@'192.168.1.142';

 

 

 

关键的SQL任务

基本的SQL对老程序员来说很简单了。但MySQL最新的特性可能理解起来就不那么顺畅了。一下将说下存储程序，视图和触发器。给大家做个指引。详细文档：http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html

 

存储程序

存储程序就是存储过程和存储函数的总称。用语法CALL来调用存储过程以及可以返回变量。但是存储函数可以像其他的MySQL函数一样直接调用。

 

创建函数

语法是使用CREATE FUNCTION 命令。

 

mysql>DELIMITER $$

mysql>CREATE FUNCTION calculate_bonus

    ->(employee_id INTEGER) RETURNS DECIMAL(5,2)

    ->BEGIN

    ->DECLARE article_count INTEGER;

    ->DECLARE bonus DECIMAL(10,2);

    ->SELECT count(id) AS article_count FROM articles 

    ->WHERE author_id = employee_id;

    ->SET bonus = article_count * 10;

    ->RETURN bonus;

    ->END;

    ->$$

mysql>DELIMITER ;

创建成功后直接调用。

 

mysql>SELECT name, phone, calculate_bonus(id) FROM authors;

 

修改函数

语法是ALTER FUNCTION

 

mysql>DELIMITER $$

mysql>ALTER FUNCTION calculate_bonus

    ->MODIFIED FUNCTION BODY...

    ->$$

mysql>DELIMITER $$

 

 

删除函数

语法是DROP FUNCTION 

mysql>DROP FUNCTION calculate_bonus;

 

 

视图

视图可以很方便的执行和管理复杂的查询。

 

创建视图

命令是 CREATE VIEW

 

mysql>CREATE VIEW author_view AS

    ->SELECT name, e-mail, phone FROM authors ORDER BY email ASC;

 

使用方法如下

mysql>SELECT * FROM author_view;

 

视图的查询

和普通表查询一样。示例如下

mysql>SELECT * FROM author_view WHERE email = "[e-mail]";

 

查看视图

可以用DESCRIBE语句查看视图有哪些列。

mysql>DESCRIBE author_view;

 

查看视图的语法。用SHOW CREATE VIEW

mysql>SHOW CREATE VIEW author_view;

 

修改视图

修改视图用ALTER VIEW

 

mysql>ALTER VIEW author_view AS

    ->SELECT name, phone FROM authors ORDER BY phone;

 

 

删除视图

DROP VIEW

mysql>DROP VIEW author_view;

 

触发器

指定表的相关事件触发的时候，触发器会自动的执行。一般用作当一个表更新的时候，另一个表也要更新。

 

创建触发器

命令是 CREATE TRIGGER 。下面的实例描述的是，当某一类别的文章添加数据库使，更新这个类别文章的数目。

 

mysql>DELIMITER $$

mysql>CREATE TRIGGER article_counter

    ->AFTER INSERT ON articles

    ->FOR EACH ROW BEGIN

    ->UPDATE categories SET counter = counter + 1 WHERE id = NEW.category_id;

    ->END;

    ->$$

mysql>DELIMITER ;

 

修改触发器

暂时不能修改触发器，可以先删除再创建。

 

删除触发器

DROP TRIGGER 

mysql>DROP TRIGGER pay_author;

 

 

 

数据库备份

MySQL提供了多种多样的备份方式。

• 复制文件 ：如果数据库存储引擎用的是MyISAM.可以通过复制文件的方式完成备份。要记住的是，首先要锁定表(LOCK TABLES).锁定之后，这个些表只能读取。然后再执行FLUSH TABLE。通过这两步操作就可以复制表文件了。当复制结束后，通过UNLOCK TABLES解除表的锁定。
• 在MySQL Client下备份 ：使用 SELECT INTO OUTFILE 。示例：mysql>SELECT * INTO OUTFILE 'authors090308.sql' FROM authors;
• 使用mysqldump ：这个非常方便，支持全部的数据库引擎。也不用关心锁定表之类的细节问题。mysqldump支持多种参数。下面就简单介绍基本用法。
• 备份单个数据库 ：语法如下%>mysqldump [options] database_name > backup0903.sql。当然要注意的是数据库的权限。示例：%>mysqldump -u root -p database_name > backup0903.sql
• 备份指定表 ：语法如下：%>mysqldump [options] database_name table_name [table_
  name2...] > backupfle.sql
• 备份所有数据库 ：%>mysqldump [options] --all-databases > backupfle.sql
• 使用mysqlhotcopy ：如果要备份的表是MyISAM引擎，可以考虑用这个方式备份。%>mysqlhotcopy -u root -p dzone /home/jason/backups。要备份多个数据库，%>mysqlhotcopy -u root -p dzone wjgilmore /home/jason/backups。

如果对以上的备份还不满意，可以选择其他的解决方案。如mylvmbackup(http://lenz.homelinux.org/mylvmbackup/)。另一个比较不错的是Zmanda (http://www.zmanda.com/)。这两个都支持社区版本和企业版。

 

MySQL 日期和时间的特性

MySQL大概支持50多的操作日期的函数。下面就列举一些常用的。

• 查询最近24小时内的增加的行。mysql>SELECT * FROM entries WHERE entry_date >
      ->UNIX_TIMESTAMP(NOW()) - 86400;
• 查询今天所增加的行。mysql>SELECT * FROM entries WHERE date(entry_date) =
      ->date(NOW());
• 查看最近的工作日。mysql>SELECT DAYNAME(MAX(entry_date)) AS day FROM entries;
• 计算用户的平均年龄。mysql>SELECT AVG(YEAR(CURDATE()) - YEAR(birthdate)) -
      ->(RIGHT(CURDATE(),5) < RIGHT(birthdate,5)))
      ->AS age FROM users;
• 计算最新博客发布的天数。mysql>SELECT TO_DAYS(NOW()) - TO_DAYS(MAX(entry_date))
      ->FROM posts;
  
• 计算最新博客发布的周数.mysql>SELECT (TO_DAYS(NOW()) - TO_DAYS(MAX(entry_date)))
      ->/ 7 FROM posts;
• 查看用户生日提前一周的日期。mysql>SELECT DATE_SUB(CONCAT(YEAR(NOW()),"-",MONTH(birthdate),
       ->"-",DAYOFMONTH(birthdate)), INTERVAL 7 DAY) as 
      ->one_week_prior FROM users;

最后附图一张。。方便大家查询