MySQL 参考教程

xiaoxiao2026-03-11 12

内容如下

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类型

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

Numeric 类型

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

String 类型

BINARY：支持最高存储255bytes。和操作CHAR类型一样，只不过这个是个二进制字符串。BLOB /LONGBLOB /MEDIUMBLOB /TINYBLOB:存储二进制文件或者图片。限制为65545CHAR：存储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，它的内存分配和实际存储的字符数有关。

流行的管理工具

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

常见的MySQL Clients

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

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

参数如下

\c 计数器。记录你当前操作的次数。\d 当前的数据库\D 当前的日期\h 服务器Host\u 用户名称\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有强大的安全模型。能控制用户任意的操作行为。比如用户能执行的命令和在一小时之内，限制用户查询的次数。这个模型是两步操作。

验证身份(Authentication)。首先要检查主机，用户名，密码。如果验证通过，就会被授予权限。如果验证失败，用户连接MySQL就失败了。权限(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 = "nicholas@example.com";

查看视图

可以用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;

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

转载请注明原文地址: https://www.6miu.com/read-5045694.html

Java

最新回复(0)