MySQL安装与常用命令

名易软件产品

当前位置：首页>软件介绍>MySQL安装与常用命令查询：

MySQL安装与常用命令

1 MYSQL安装

先下载安装包:

mysql-5.0.27-win32.zip

mysql-noinstall-6.0.0-alpha-win32.zip 下载了2个版本:一个5.0.27安装版;一个6.0.0非安装版。本人测试用的为5.0.27安装版，安装过程不再赘述。各个操作系统平台的安装可以看MYSQL联机文档。

2 登录及一些基本操作

本章的主要目的是让我们对MYSQL的基础框架有个大概的了解。

1 连接与断开MYSQL服务器

安装完毕登陆MYSQL(有过一些其他数据库基础的人都应该很容易使用这几步): 我们可以利用如下参数查看MYSQL命令的帮助:

C:Program FilesMySQLMySQL Server 5.0in>mysql –help

联接MYSQL服务器:

C:Program FilesMySQLMySQL Server 5.0in>mysql -h localhost -uroot -p888888

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

Your MySQL connection id is 22 to server version: 5.0.27-community-nt

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| root |

+--------------------+

3 rows in set (0.08 sec)

安装完毕，都会有这几个默认的数据库。

注意到目前没有连接到任何数据库。

mysql> SELECT DATABASE();

+------------+

| database() |

+------------+

| NULL |

+------------+

1 row in set (0.78 sec)

mysql> QUIT Bye

我们也可以在连接MYSQL服务器的时候指定想要连接的数据库，如下:

C:Program FilesMySQLMySQL Server 5.0in>mysql --user=root -p mysql

Enter password: ******

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

Your MySQL connection id is 7 to server version: 5.0.27-community-nt

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> select database();

+------------+

| database() |

+------------+

| mysql |

+------------+

1 row in set (0.00 sec)

INFORMATION_SCHEMA数据库

我们在介绍MYSQL基本操作的同时顺便把INFORMATION_SCHEM数据库做个简单介绍: 类似其他数据库的数据字典，各个字典含义不做详述，以下摘自MYSQL联机文档: INFORMATION_SCHEMA提供了访问数据库元数据的方式。

元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用

于表述该信息的其他术语包括“数据词典”和

“系统目录”。INFORMATION_SCHEMA是信息数据库，其中保存着关于MySQL服务器所维护的

所有其他数据库的信息。在INFORMATION_SCHEMA

中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任

何文件。

3 使用数据库

mysql> USE INFORMATION_SCHEMA;

Database changed

mysql> SELECT DATABASE();

+--------------------+

| database() |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.00 sec)

mysql> SELECT VERSION(), CURRENT_DATE, CURDATE(), NOW(), USER();

+---------------------+--------------+------------+---------------------+------

+---------------------+--------------+------------+---------------------+------

| 5.0.27-community-nt | 2007-05-24 | 2007-05-24 | 2007-05-24 17:01:16 | root@l

ocalhost |

+---------------------+--------------+------------+---------------------+-------

1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'version';

4 MYSQL的SHOW命令

前边的例子中我们已经用过了MYSQL的SHOW命令:

mysql> SHOW DATABASES;

SHOW命令可用于获取关于INFORMATION_SCHEMA本身结构的信息。

一些SHOW语句允许使用FROM、WHERE子句，这样，在指定需要显示的行时，可更为灵活。下

边给出部分例子:

mysql> SHOW TABLES FROM MYSQL;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| func |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| proc |

| procs_priv |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+---------------------------+

17 rows in set (0.00 sec)

mysql> SHOW TABLES;

+---------------------------------------+

| Tables_in_information_schema |

+---------------------------------------+

| CHARACTER_SETS |

| COLLATIONS |

| COLLATION_CHARACTER_SET_APPLICABILITY |

| COLUMNS |

…………

16 rows in set (0.00 sec)

SHOW TABLES命令显示了当前用数据库中的数据库对象列表，而从TABLES视图的查询我们将

得到所有数据库下的对象列表。这个例子就是给出了一个查询MYSQL的表相关的系统视图，类似

ORACLE中的(DBA_TABLES、USER_TABLES)和SYBASE中的SYSOBJECTS。

33 rows in set (0.03 sec)

mysql> SHOW COLUMNS FROM TABLES;

21 rows in set (0.06 sec)

mysql> SHOW CHARACTER SET;

+----------+-----------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+-----------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | ………

36 rows in set (0.00 sec)

mysql> SHOW CHARACTER SET like 'big5';

mysql> SELECT * FROM COLLATIONS WHERE COLLATION_NAME LIKE '%big5%'; +-----------------+--------------------+----+------------+-------------+------- | COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN +-----------------+--------------------+----+------------+-------------+-------

| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |

| big5_bin | big5 | 84 | | Yes | 1 |

+-----------------+--------------------+----+------------+-------------+------- 2 rows in set (0.00 sec)

mysql> SHOW GRANTS;

| Grants for root@localhost

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*DA2 8842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB' WITH GRANT OPTION |

1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR ROOT;

| Grants for root@%

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'% ' IDENTIFIED BY PASSWORD '*DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB' WITH GRANT OPTION |

1 row in set (0.00 sec)

5 我们也可以通过查询系统表来获得用户的权限:

mysql> SELECT * FROM USER_PRIVILEGES;

下边给出了MYSQL的权限列表功参考:

Privilege Meaning

ALL [PRIVILEGES] Sets all simple privileges except GRANT OPTION

ALTER Enables use of ALTER TABLE

ALTER ROUTINE Enables stored routines to be altered or dropped CREATE Enables use of CREATE TABLE

CREATE ROUTINE Enables creation of stored routines

CREATE TEMPORARY TABLES Enables use of CREATE TEMPORARY TABLE CREATE USER Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.

CREATE VIEW Enables use of CREATE VIEW

DELETE Enables use of DELETE

DROP Enables use of DROP TABLE

EXECUTE Enables the user to run stored routines

FILE Enables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE INDEX Enables use of CREATE INDEX and DROP INDEX

INSERT Enables use of INSERT

LOCK TABLES Enables use of LOCK TABLES on tables for which you have the SELECT privilege

PROCESS Enables use of SHOW FULL PROCESSLIST

REFERENCES Not implemented

RELOAD Enables use of FLUSH

REPLICATION CLIENT Enables the user to ask where slave or master servers are REPLICATION SLAVE Needed for replication slaves (to read binary log events from the master)

SELECT Enables use of SELECT

SHOW DATABASES SHOW DATABASES shows all databases

SHOW VIEW Enables use of SHOW CREATE VIEW

SHUTDOWN Enables use of mysqladmin shutdown

SUPER Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached

UPDATE Enables use of UPDATE

USAGE Synonym for “no privileges”

GRANT OPTION Enables privileges to be granted

关于SHOW命令我们就简单介绍这么几个，如果想知道更多的SHOW命令可以得到的信息内容可

以执行如下命令来获取帮助或者参看MYSQL的联机文档第23章:INFORMATION_SCHEMA信息数据库。

mysql> HELP SHOW

Name: 'SHOW'

Description:

SHOW has many forms that provide information about databases, tables,

columns, or status information about the server. This section describes

those following:

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

SHOW CREATE DATABASE db_name

SHOW CREATE FUNCTION funcname

SHOW CREATE PROCEDURE procname

SHOW CREATE TABLE tbl_name

SHOW DATABASES [LIKE 'pattern']

SHOW ENGINE engine_name {LOGS | STATUS } SHOW [STORAGE] ENGINES

SHOW ERRORS [LIMIT [offset,] row_count] SHOW FUNCTION STATUS [LIKE 'pattern'] SHOW GRANTS FOR user

SHOW INDEX FROM tbl_name [FROM db_name] SHOW INNODB STATUS

SHOW PROCEDURE STATUS [LIKE 'pattern'] SHOW [BDB] LOGS

SHOW MUTEX STATUS

SHOW PRIVILEGES

SHOW [FULL] PROCESSLIST

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']

SHOW TRIGGERS

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']

SHOW WARNINGS [LIMIT [offset,] row_count]

The SHOW statement also has forms that provide information about replication master and slave servers and are described in [HELP PURGE MASTER LOGS]:

SHOW BINARY LOGS

SHOW BINLOG EVENTS

SHOW MASTER STATUS

SHOW SLAVE HOSTS

SHOW SLAVE STATUS

If the syntax for a given SHOW statement includes a LIKE 'pattern' part, 'pattern' is a string that can contain the SQL `%' and `_' wildcard characters. The pattern is useful for restricting statement output to matching values.

Several SHOW statements also accept a WHERE clause that provides more flexibility in specifying which rows to display. See

6 创建用户数据库

mysql> CREATE DATABASE MYTEST;

Query OK, 1 row affected (0.00 sec)

如果想改变MYSQL数据文件的默认路径，我们可以关闭MYSQL实例，修改配置文件”my.cnf”

或”my.ini”(WINDOWS系统)中的datadir对应的参数值，然后把MYSQL默认安装的datadir下的

内容拷贝到新的数据文件路径下，启动MYSQL实例这样我们再次创建数据库的时候数据文件就放

在新的路径下了。

mysql> USE MYTEST;

Database changed

mysql> SELECT DATABASE(); +------------+

| DATABASE() |

+------------+

| mytest |

+------------+

1 row in set (0.00 sec)

mysql> SHOW TABLES;

Empty set (0.01 sec)

创建数据库用户

可以用两种方式创建MySQL账户:

1、直接操作MySQL授权表user(不推荐)

2、 CREATE USER

mysql> create user test_cr identified by 'test_cr';

Query OK, 0 rows affected (0.23 sec)

3、直接使用GRANT语句

我们可以不用第二步的CREATE语法来执行，而直接GRANT就可以创建用户。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'TEST'@'LOCALHOST' IDENTIFIED BY 'TEST' WITH GRANT OPTION;

Query OK, 0 rows affected (0.06 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'TEST'@'%' IDENTIFIED BY 'TEST' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON MYSQL.* TO TEST_NORMAL@'LOCALHOST' IDENTIFIED BY 'TEST_NORMAL';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON MYSQL.* TO TEST_NORMAL@'%' IDENTIFIED BY 'TEST_NORMAL';

Query OK, 0 rows affected (0.02 sec)

mysql> SELECT USER FROM MYSQL.USER; +-------------+

| USER |

+-------------+

| TEST |

| TEST_NORMAL |

| root |

| TEST |

| TEST_NORMAL |

| root |

+-------------+

6 rows in set (0.00 sec)

注意:其中两个账户有相同的用户名TEST和密码TEST。两个账户均为超级用户账户，具有完

全的权限可以做任何事情。一个账户

('TEST'@'localhost')只用于从本机连接时。另一个账户('TEST'@'%')可用于从其它主机连

接。请注意TEST的两个账户必须能

从任何主机以TEST连接。没有localhost账户，当TEST从本机连接时，mysql_install_db创

建的localhost的匿名用户账户将占先。

结果是，TEST将被视为匿名用户。原因是匿名用户账户的Host列值比'TEST'@'%'账户更具体，

这样在user表排序顺序中排在前面。

C:Program FilesMySQLMySQL Server 5.0in>mysql -uTEST -p mytest

Enter password: ****

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

Your MySQL connection id is 14 to server version: 5.0.27-community-nt

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

注意:用户名和密码区分大小写。

mysql> SELECT DATABASE();

+------------+

| DATABASE() |

+------------+

| mytest |

+------------+

1 row in set (0.00 sec)

mysql> SELECT USER();

+----------------+

| USER() |

+----------------+

| TEST@localhost |

+----------------+

1 row in set (0.00 sec)

7 创建数据库对象

1 创建表

在测试MYSQL创建的时候还遇到了一些小问题，这里整理出来供大家参考:

mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

-> MC VARCHAR(60),DT DATE DEFAULT NOW());

ERROR 1067 (42000): Invalid default value for 'DT'

注意: AUTO_INCREMENT为MYSQL的自增类型。我们可以利用如下函数查询最后一个序列号的

值:

mysql> SELECT LAST_INSERT_ID();

+------------------+

| LAST_INSERT_ID() |

+------------------+

| 3 |

+------------------+

1 row in set (0.06 sec)

本来想在创建表的时候给DT字段一个DEFAULT值(当前时间)，但是出现错误，尝试了几个MYSQL的函数(如CURDATE()等)都是如此，查阅了一下资料得出结论:

MYSQL的DEFAULT值只能是常量，如果想实现上述功能只有表中第一个TIMESTAMP类型字段可以做到。可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMESTAMP列，只有第一个自动更新。自动更新第一个TIMESTAMP列在下列任何条件下发生:

A、列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。

B、列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。(注意一个UPDATE设置一个列为它已经有的值，这将不引起TIMESTAMP列被更新，因为如果你设置一个列为它当前的值，MySQL为了效率而忽略更改。)

C、明确地设定TIMESTAMP列为NULL或NOW()。

格式:TIMESTAMP[(M)]

MySQL可以以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD、YYMMDD格式来显示TIMESTAMP值，这主要取决于M值，它们分别为14(缺省值)1286。

mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

-> MC VARCHAR(60),DT TIMESTAMP);

Query OK, 0 rows affected (1.08 sec)

mysql> SHOW COLUMNS FROM TEST;

+-------+-------------+------+-----+-------------------+----------------+

| Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+-------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | MC | varchar(60) | YES | | NULL | | | DT | timestamp | NO | | CURRENT_TIMESTAMP | | +-------+-------------+------+-----+-------------------+----------------+ 3 rows in set (0.17 sec)

mysql> insert into test(mc) values('ZhangSan');

Query OK, 1 row affected (0.13 sec)

mysql> select * from test;

+----+----------+---------------------+

| ID | MC | DT |

+----+----------+---------------------+

| 1 | ZhangSan | 2007-05-25 09:54:59 |

+----+----------+---------------------+

1 row in set (0.06 sec)

注意:为表TEST新增加TIMESTAMP类型字段，我们发现不是表的第一个TIMESTAMP类型的字段

的DEFAULT值不是系统时间。

mysql> ALTER TABLE TEST ADD RQ TIMESTAMP;

Query OK, 1 row affected (0.70 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM TEST;

| ID | int(11) | NO | PRI | NULL | auto_increment | | MC | varchar(60) | YES | | NULL | | | DT | timestamp | NO | | CURRENT_TIMESTAMP | | | RQ | timestamp | NO | | 0000-00-00 00:00:00 | | +-------+-------------+------+-----+---------------------+----------------+ 4 rows in set (0.19 sec)

mysql> INSERT INTO TEST(MC) VALUES('LiSi');

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM TEST;

+----+----------+---------------------+---------------------+ | ID | MC | DT | RQ | +----+----------+---------------------+---------------------+ | 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 | | 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 | +----+----------+---------------------+---------------------+ 2 rows in set (0.01 sec)

mysql> INSERT INTO TEST(MC,RQ) VALUES('LiSi',NOW());

Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM TEST;

+----+----------+---------------------+---------------------+ | ID | MC | DT | RQ | +----+----------+---------------------+---------------------+ | 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 | | 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 | | 3 | LiSi | 2007-05-25 10:03:29 | 2007-05-25 10:03:29 | +----+----------+---------------------+---------------------+

3 rows in set (0.00 sec)

注意:执行UPDATE操作,表中第一个TIMESTAMP字段自动修改为系统时间。

mysql> UPDATE TEST SET MC='WangWu' WHERE ID=3;

Query OK, 1 row affected (0.13 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM TEST;

+----+----------+---------------------+---------------------+ | ID | MC | DT | RQ | +----+----------+---------------------+---------------------+ | 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 | | 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 | | 3 | WangWu | 2007-05-25 10:04:01 | 2007-05-25 10:03:29 | +----+----------+---------------------+---------------------+ 1 rows in set (0.00 sec)

8 创建索引

MYSQL索引类型:

1、普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创

建:

创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);

修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列

的列表) );

2、唯一性索引

这种索引和前面的“普通索引”基本相同，但有一个区别:索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种

方式创建:

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );

3、主键

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。

主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表

的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

4、全文索引

MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型

的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER

TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引，如要了解

更多信息，请参见MySQL documentation。

__________________

www.360oracle.com 专家顾问

msn:zhouwf0726@hotmail.com mail:zhouwf0726@163.com 只看该作者

zhouwf0726

版主

精华贴数 5

个人空间 2148

技术积分 8888 (213)

社区积分 312 (2616)

注册日期 2006-2-22

论坛徽章:45

#2使用道具

发表于 2007-6-6 17:51 创建MYSQL索引

mysql> create procedure p_test()

-> begin

-> declare counter int; -> set counter = 1000; -> while counter >= 1 do

-> insert into test(id,mc) values(counter,'test');

-> set counter = counter - 1;

-> end while;

-> end;//

Query OK, 0 rows affected (0.98 sec)

mysql> call p_test();

-> //

Query OK, 1 row affected (34.48 sec)

mysql> show columns from test;

+-------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------------------+-------+ | ID | int(11) | NO | | 0 | | | MC | varchar(60) | YES | | NULL | | | DT | timestamp | NO | | 0000-00-00 00:00:00 | | | RQ | timestamp | NO | | 0000-00-00 00:00:00 | | +-------+-------------+------+-----+---------------------+-------+ 4 rows in set (0.08 sec)

mysql> select * from test where id=500;

+-----+------+---------------------+---------------------+ | ID | MC | DT | RQ | +-----+------+---------------------+---------------------+ | 500 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +-----+------+---------------------+---------------------+ 1 row in set (0.01 sec)

mysql> create index idx_test on test(id);

Query OK, 1000 rows affected (0.81 sec)

Records: 1000 Duplicates: 0 Warnings: 0

mysql> show columns from test;

+-------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------------------+-------+ | ID | int(11) | NO | MUL | 0 | | | MC | varchar(60) | YES | | NULL | | | DT | timestamp | NO | | 0000-00-00 00:00:00 | | | RQ | timestamp | NO | | 0000-00-00 00:00:00 | | +-------+-------------+------+-----+---------------------+-------+ 2 rows in set (0.00 sec)

mysql> select * from test where id=800;

+-----+------+---------------------+---------------------+ | ID | MC | DT | RQ | +-----+------+---------------------+---------------------+ | 800 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +-----+------+---------------------+---------------------+ 1 row in set (0.00 sec)

我们可以看出上边的例子创建索引前后SQL执行时间的变化(红色字体部分)。

删除索引

mysql> drop index idx_test on test;

Query OK, 3 rows affected (1.20 sec)

Records: 3 Duplicates: 0 Warnings: 0

9 创建存储过程

MYSQL存储过程大致格式如下:

CREATE PROCEDURE procedure1 /* name存储过程名*/

(IN parameter1 INTEGER) /* parameters参数*/

BEGIN /* start of block语句块头*/

DECLARE variable1 CHAR(10); /* variables变量声明*/

IF parameter1 = 17 THEN /* start of IF IF条件开始*/

SET variable1 = 'birds'; /* assignment赋值*/

ELSE

SET variable1 = 'beasts'; /* assignment赋值*/

END IF; /* end of IF IF结束*/

INSERT INTO table1 VALUES (variable1); /* statement SQL语句*/

END /* end of block语句块结束*/

DELIMITER，首先说明一点，在MYSQL的控制台执行创建过程的脚本时，要选择一个分隔符

给出一个最简单的MYSQL存储过程示例:

mysql> DELIMITER //

mysql> CREATE PROCEDURE P()SELECT * FROM TEST; // Query OK, 0 rows affected (0.31 sec)

mysql> DELIMITER ;

mysql> CALL P();

+----+----------+---------------------+---------------------+ | ID | MC | DT | RQ | +----+----------+---------------------+---------------------+ | 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 | | 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 | | 3 | WangWu | 2007-05-25 10:04:01 | 2007-05-25 10:03:29 | +----+----------+---------------------+---------------------+ 3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

给出一个带有输入输出参数的存储过程示例:

mysql> DELIMITER //

mysql> CREATE PROCEDURE P_WITH_PARA(IN PARA_IN INT,OUT PARA_OUT INT)

-> BEGIN

-> DECLARE PARA INT;

-> SET PARA = 20;

-> SET PARA_OUT = PARA_IN + PARA;

-> END

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> CALL P_WITH_PARA(10,@SUM);//

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @SUM//

+------+

| @SUM |

+------+

| 30 |

+------+

1 row in set (0.00 sec)

存储过程内容的查看:

mysql> select body from proc where name='P_WITH_PARA'; +---------------------------------------------------------------------------+ | body | +---------------------------------------------------------------------------+ | BEGIN

DECLARE PARA INT;

SET PARA = 20;

SET PARA_OUT = PARA_IN + PARA;

END |

+---------------------------------------------------------------------------+ 1 row in set (0.01 sec)

MYSQL存储过程中的一些常用控制结构:

选择结构:

IF ... THEN

...

ELSE

...

END IF;

CASE ...

WHEN ... THEN WHEN ... THEN END CASE;

循环结构:

WHILE ...

...

END WHILE;

LOOP_LABEL:LOOP

...

ITERATE LOOP_LABEL;

...

LEAVE LOOP_LABEL;

END LOOP;

REPEAT

...

UNTIL ... END REPEAT;

LABEL LABEL_NAME; ...

GOTO LABEL_NAME;

4 创建函数

各个数据库包括各种开发工具都为用户提供了创建函数的功能，这里关于函数的语法我们不

再做更多解释了，给出一个例子:

mysql> delimiter //

mysql> CREATE FUNCTION fn_test (n DECIMAL(3,0))

-> RETURNS DECIMAL(20,0)

-> DETERMINISTIC

-> BEGIN

-> DECLARE v_tmp DECIMAL(20,0) DEFAULT 1;

-> DECLARE counter DECIMAL(3,0);

-> SET counter = n;

-> factorial_loop: REPEAT

-> SET v_tmp = v_tmp * counter;

-> SET counter = counter - 1;

-> UNTIL counter = 1

-> END REPEAT;

-> RETURN v_tmp;

-> END

-> //

Query OK, 0 rows affected (0.23 sec)

mysql> delimiter ;

mysql> select fn_test(10);

+-------------+

| fn_test(10) |

+-------------+

| 3628800 |

+-------------+

1 row in set (0.20 sec)

mysql> select fn_test(3);

+------------+

| fn_test(3) |

+------------+

| 6 |

+------------+

1 row in set (0.00 sec)

显示一个定义好的函数的内容:

mysql> show create function fn_test;

5 创建视图

给出一个创建视图的例子:

注意:下边例子红色字体部分为MYSQL数据库取前n条记录的方法，不同于其他数据库。

mysql> select count(*) from test; +----------+

| count(*) |

+----------+

| 2001 |

+----------+

1 row in set (0.01 sec)

mysql> create view v_test as select * from test limit 20;

Query OK, 0 rows affected (0.06 sec)

mysql> select * from v_test;

20 rows in set (0.06 sec)

和显示函数内容类似，我们也可以按照如下方法获得创建视图的语法内容:

mysql> show create view v_test;

删除视图

mysql> drop view v_test;

Query OK, 0 rows affected (0.02 sec)

6 创建触发器

给出一个简单的创建触发器的例子:

mysql> delimiter //

mysql> create trigger tr_test before insert on test for each row

-> begin

-> insert into test1(id,mc) values(new.id,new.mc);

-> end

-> //

Query OK, 0 rows affected (0.17 sec) mysql> delimiter ;

mysql> select count(*) from test1; +----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

mysql> insert into test (id,mc) values(50000,'trigger_to_test1');

Query OK, 1 row affected (0.19 sec)

mysql> select id,mc from test1; +-------+------------------+ | id | mc | +-------+------------------+ | 50000 | trigger_to_test1 | +-------+------------------+ 1 row in set (0.00 sec)

MYSQL存储引擎和表类型

MYSQL支持数个存储引擎作为对不同表的类型的处理器。

mysql> SHOW ENGINES;

+------------+---------+-------------------------------------------------------

--------+

| Engine | Support | Comment

+------------+---------+-------------------------------------------------------

--------+

| MyISAM | YES | Default engine as of MySQL 3.23 with great performance

| MEMORY | YES | Hash based, stored in memory, useful for temporary tabl es |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign k eys |

| BerkeleyDB | NO | Supports transactions and page-level locking

| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disa ppears) |

| EXAMPLE | NO | Example storage engine

| ARCHIVE | YES | Archive storage engine

| CSV | NO | CSV storage engine

| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables

| FEDERATED | NO | Federated MySQL storage engine

| MRG_MYISAM | YES | Collection of identical MyISAM tables

| ISAM | NO | Obsolete storage engine

+------------+---------+-------------------------------------------------------

--------+

12 rows in set (0.00 sec)

MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎:

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因。其它数据库系统(包括大多数商业选择)仅支持一种类型的数据存储。遗憾的是，其它类型的数据库解决方案采取的“一个尺码满足一切需求”的方式意味着你要么就牺牲一些性能，要么你就用几个小时甚至几天的时间详细调整你的数据库。使用MySQL，我们仅需要修改我们使用的存储引擎就可以了。

默认存储引擎

MYSQL的默认存储引擎为:MyISAM，除非我们显示的指定存储引擎。如下例:

mysql> CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;

Query OK, 0 rows affected (0.83 sec)

为了做出选择哪一个存储引擎的决定，我们首先需要考虑每一个存储引擎提供了哪些不同的核心功能。这种功能使我们能够把不同的存储引擎区别开来。我们一般把这些核心功能分为四类:支持的字段和数据类型、锁定类型、索引和处理。一些引擎具有能过促使你做出决定的独特的功能

下边我们详细讲述一下MYSQL各个存储引擎:

1 MyISAM

每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

MyISAM引擎是大多数MySQL安装程序的默认引擎，起源于早期版本MySQL支持的ISAM引擎。这种引擎提供了最佳的性能和功能的组合，尽管它缺少事务处理功能(使用InnoDB或者BDB引擎)并且使用表级锁定。

但是执行一下查询发现，我在测试的时候使用的两个测试表在创建的时候没有指定引擎，但是发现这两个表的存储引擎都为InnoDB。(当然我们修改配职文件my.ini中的default-storage-engine=INNODB来修改)。

mysql> select table_name,engine from tables where table_name like 'test%';

+------------+--------+

| table_name | engine |

+------------+--------+

| test | InnoDB |

| test1 | InnoDB |

+------------+--------+

2 rows in set (0.08 sec)

找了一下MYSQL文档，发现如下解释:

第15章:存储引擎和表类型:当MySQL被用MySQL配置向导安装在Windows平台上，InnoDB存储

引擎替代MyISAM存储引擎作为替代，请参阅2.3.5.1节，“介绍”。

为了测试MyISAM引擎表级锁定，我们是用MyISAM引擎创建测试表TEST_ISAM。

测试中打开两个数据库连接，一个连接执行call p_tst_isam();另外一个执行单条mysql>

insert into test_isam(id,mc) values(1,'1');结果在第一个连接还没有执行完的时候，第二

个就完毕，没有发现MyISAM引擎锁表，这个问题我们暂时不再继续测试下去。测试中发现一个问

题，MyISAM引擎的表的INSERT速度远远大于InnoDB引擎:

mysql> CREATE TABLE TEST_ISAM(ID INTEGER,MC VARCHAR(60)) ENGINE=MyISAM;

Query OK, 0 rows affected (0.38 sec)

mysql> select table_name,engine from information_schema.tables where table_name like 'test%';

+------------+--------+

| table_name | engine |

+------------+--------+

| test | InnoDB |

| test1 | InnoDB |

| test_isam | MyISAM |

+------------+--------+

3 rows in set (0.00 sec)

创建存储过程p_test_isam

delimiter //

create procedure p_test_isam() begin

declare counter int;

set counter = 1000000;

while counter >= 1 do

insert into test_isam(id,mc) values(counter,'test');

set counter = counter - 1;

end while;

end

delimiter ;

我们在以前的测试例子中: InnoDB引擎 INSERT 1000条数据花费34秒

mysql> call p_test();

Query OK, 1 row affected (34.48 sec)

MyISAM引擎INSERT 1000000 条数据花费时间20多秒:

mysql> call p_test_isam();

Query OK, 1 row affected (22.95 sec)

所以我们如果在使用非事物处理的表(也就是一些只有单用户使用的表)的时候可以采用

MyISAM引擎来提高速度，当然了INSERT的时候可以利用MYSQL的BULK INSERT功能来出也是能大大

提高性能的，这些我们将在MYSQL数据库优化一章中详细说明。BULK INSERT的语法:

INSERT INTO TEST VALUES(VAL11,VAL12),(VAL21,VAL22)„„

测试完毕，翻看一下MYSQL文档，的确有下面一段话，和我们的测试结果吻合:

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。

2 MERGE引擎

MERGE引擎类型允许你把许多结构相同的表合并为一个表。然后，你可以执行查询，从多个表返回的结果就像从一个表返回的结果一样。每一个合并的表必须有同样的表定义。

MERGE存储引擎在下面这种使用场合会最为有用，如果需要把日志纪录不停的录入MySQL数据库，并且每天、每周或者每个月都创建一个单一的表，而且要制作来自多个表的合计查询，MERGE表这时会非常有效。然而，这项功能有局限性。你只能合并MyISAM表而且必须严格遵守相同的表定义的限制。虽然这看起来好像是一个大问题，但是，如果你使用另外一种表类型(例如InnoDB)，这种合并可能就不需要了。

3 MEMORY(内存)存储引擎

MEMORY(内存)存储引擎(以前称作HEAP存储引擎)在内存中存储全部数据。一旦MySQL服务器关闭，存储在内存中的任何信息都将丢失。然而，单个表的格式将保留，使你能够创建一个用于存储信息的临时表。这样，每次数据库服务器启动时，你不需要重新创新这个表就可以快速地访问信息。

长期使用MEMORY存储引擎一般来说不是一个好主意，因为数据很容易丢失。然而，如果你有足够的内存，使用基于MEMORY的表在大型数据集中执行复杂的查询是一种非常有效的方法，它能够很大程度的提高性能。

使用MEMORY表的最佳方法是使用一个“select”语句从你原来的基于磁盘的表中选择一个大型的数据集，然后对你需要的具体部分进一步分析那些信息。我过去曾经使用这个技术提取了一个月的网络记录数据，实际上就是从使用ARCHIVE存储引擎制作的表中提取的数据，然后对具体的URL、网站和其它重点进行查询。

4 EXAMPLE引擎

EXAMPLE引擎实际上是一个存储引擎编程的例子，能够用作MySQL系统中其它引擎的基础。

EXAMPLE不支持数据插入，对于任何形式的数据库访问来说也不是一个实用的引擎。然而，EXAMPLE是一个很好的指南，指导你如何开发自己的存储引擎，因此对于程序员来说是一个有效的引擎。

5 InnoDB存储引擎

InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表,索引在一个表空间中，表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。

InnoDB被用来在众多需要高性能的大型数据库站点上产生。著名的Internet新闻站点Slashdot.org运行在InnoDB上。Mytrix, Inc.在InnoDB上存储超过1TB的数据，还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的负荷。

给出一个事物控制的例子:

mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.30 sec)

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');

Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.03 sec)

mysql> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (15, 'John');

Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;

Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM CUSTOMER;

+------+--------+

| A | B |

+------+--------+

| 10 | Heikki |

+------+--------+

1 row in set (0.00 sec)

InnoDB存储引擎相关的内容非常复杂涉及到事物处理、日志、备份和恢复、锁定、多版本、性能、表和索引的结构、磁盘IO等很多方面的知识，我们将在以后使用中逐步研究。

的存储引擎有个基本的认识。我们以上只讲述了MYSQL的几个存储引擎，使我们能够对MYSQL

MYSQL还提供了BDB (BerkeleyDB)存储引擎、FEDERATED存储引擎、ARCHIVE存储引擎、CSV存储引擎、BLACKHOLE存储引擎等，这里就不再详细说明了。更多详细信息参看MYSQL联机文档第15章:存储引擎和表类型。

4 MYSQL的SQL语法和常用函数

1 数据类型

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

数据类型描述字节推荐使用

SMALLINT 整数，从-32000到 +32000范围 2 存储相对比较小的整数。比如: 年纪，数量

INT 整数，从-2000000000 到 +2000000000 范围 4 存储中等整数例如: 距离

BIGINT 不能用SMALLINT 或 INT描述的超大整数。 8 存储超大的整数例如: 科学/数学数据

FLOAT 单精度浮点型数据 4 存储小数数据例如:测量，温度

DOUBLE 双精度浮点型数据 8 需要双精度存储的小数数据例如:科学数据

DECIMAL 用户自定义精度的浮点型数据变量;取决于精度与长度以特别高的精度存储小数数据。例如:货币数额，科学数据

CHAR 固定长度的字符串特定字符串长度(高达255字符) 存储通常包含预定义字符串的变量例如: 定期航线，国家或邮编

VARCHAR 具有最大限制的可变长度的字符串变量; 1 + 实际字符串长度 (高达 255 字符) 存储不同长度的字符串值(高达一个特定的最大限度).例如:名字，密码，短文标签

TEXT 没有最大长度限制的可变长度的字符串 Variable; 2 +聽 actual string length 存储大型文本数据例如: 新闻故事，产品描述

BLOB 二进制字符串变量;2 + 实际字符串长度存储二进制数据例如:图片，附件，二进制文档

DATE 以 yyyy-mm-dd格式的日期 3 存储日期例如:生日，产品满期

TIME 以 hh:mm:ss格式的时间 3 存储时间或时间间隔例如:报警声，两时间之间的间隔，任务开始/结束时间

DATETIME 以yyyy-mm-ddhh:mm:ss格式结合日期和时间 8 存储包含日期和时间的数据例如:提醒的人，事件

TIMESTAMP 以yyyy-mm-ddhh:mm:ss格式结合日期和时间 4 记录即时时间例如:事件提醒器，“最后进入”的时间标记

YEAR 以 yyyy格式的年份 1 存储年份例如:毕业年，出生年

ENUM 一组数据，用户可从中选择其中一个 1或 2个字节存储字符属性，只能从中选择之一例如:布尔量选择，如性别

SET 一组数据，用户可从中选择其中0，1或更多。从1到8字节;取决于设置的大小存储字符属性，可从中选择多个字符的联合。例如:多选项选择，比如业余爱好和兴趣。

2 字符串函数

CHARSET(str) //返回字串字符集

CONCAT (string2 [,„ ]) //连接字串，注意不要沿用ORACLE习惯

mysql> select concat('a','b');

+-----------------+

| concat('a','b') |

+-----------------+

| ab |

+-----------------+

1 row in set (0.03 sec)

mysql> select 'a'||'b';

+----------+

| 'a'||'b' |

+----------+

| 0 |

+----------+

1 row in set, 2 warnings (0.00 sec)

INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存

在返回0

LCASE (string2 ) //转换成小写 LEFT (string2 ,length ) //从string2中的左起取length个字符 LENGTH (string ) //string长度 LOAD_FILE (file_name ) //从文件读取内容

LOCATE (substring , string [,start_position ] )

//同INSTR,但可指定开始位置

LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length

LTRIM (string2 ) //去除前端空格 REPEAT (string2 ,count ) //重复count次

REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str

RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度length RTRIM (string2 ) //去除后端空格 STRCMP (string1 ,string2 ) //逐字符比较两字串大小,

SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符

注:mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1 mysql> select substring('abcd',0,2); +-----------------------+

| substring('abcd',0,2) |

+-----------------------+

| |

+-----------------------+

1 row in set (0.06 sec)

mysql> select substring('abcd',1,2); +-----------------------+

| substring('abcd',1,2) |

+-----------------------+

| ab |

+-----------------------+

1 row in set (0.00 sec)

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2)

//去除指定位置的指定字符

UCASE (string2 ) //转换成大写 RIGHT(string2,length) //取string2最后length个字符 SPACE(count) //生成count个空格

3 数学函数

ABS (number2 ) //绝对值 BIN (decimal_number ) //十进制转二进制 CEILING (number2 ) //向上取整 CONV(number2,from_base,to_base) //进制转换

FLOOR (number2 ) //向下取整

FORMAT (number,decimal_places ) //保留小数位数

HEX (DecimalNumber ) //转十六进制

HEX()中可传入字符串，则返回其ASC-11码，如HEX(’DEF’)返回4142143 注:

也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19

LEAST (number , number2 [,..]) //求最小值

MOD (numerator ,denominator ) //求余 POWER (number ,power ) //求指数 RAND([seed]) //随机数

ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

注:返回类型并非均为整数，如:

(1)默认变为整形值

mysql> select round(1.23); +————-+

| round(1.23) |

+————-+

| 1 |

+————-+

1 row in set (0.00 sec) mysql> select round(1.56); +————-+

| round(1.56) |

+————-+

| 2 |

+————-+

1 row in set (0.00 sec) (2)可以设定小数位数，返回浮点型数据

mysql> select round(1.567,2); +—————-+

| round(1.567,2) |

+—————-+

| 1.57 |

+—————-+

1 row in set (0.00 sec)

SIGN (number2 ) //返回符号,正负或0

SQRT(number2) //开平方

4 日期函数

ADDTIME (date2 ,time_interval ) //将time_interval加到date2

CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区

CURRENT_DATE ( ) //当前日期

CURRENT_TIME ( ) //当前时间

CURRENT_TIMESTAMP ( ) //当前时间戳

DATE (datetime ) //返回datetime的日期部分

DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间

DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime

DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间

DATEDIFF (date1 ,date2 ) //两个日期差

DAY (date ) //

返回日期的天

DAYNAME (date ) //英文星期

DAYOFWEEK (date ) //星期(1-7) ,1为星期天

DAYOFYEAR (date ) //一年中的第几天

EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分

MAKEDATE (year ,day ) //给出年及年中的第几天,生成日

期串

MAKETIME (hour ,minute ,second ) //生成时间串

MONTHNAME (date ) //英文月份名

NOW ( )

//当前时间

SEC_TO_TIME (seconds ) //秒数转成时间 STR_TO_DATE (string ,format ) //字串转成时间, format格式

显示

TIMEDIFF (datetime1 ,datetime2 ) //两个时间差

TIME_TO_SEC (time ) //时间转秒

数]

WEEK (date_time [,start_of_week ]) //第几周

YEAR (datetime ) //年份 DAYOFMONTH(datetime) //月的第几天 HOUR(datetime) //小时 LAST_DAY(date) //date的

月的最后日期

MICROSECOND(datetime) //微秒 MONTH(datetime) //月 MINUTE(datetime) //分

5 控制结构

1.区块定义，常用

begin

„„

end;

也可以给区块起别名，如:

lable:begin

„„„..

end lable;

可以用leave lable;跳出区块，执行区块以后的代码

2.条件语句

if 条件 then

statement

else

statement

end if;

3.循环语句

(1).while循环

[label:] WHILE expression DO

statements

END WHILE [label] ;

(2).loop循环

[label:] LOOP

statements

END LOOP [label]; (3).repeat until循环

[label:] REPEAT

statements

UNTIL expression

END REPEAT [label] ;

......待续......

__________________ www.360oracle.com 专家顾问

msn:zhouwf0726@hotmail.com mail:zhouwf0726@163.com 只看该作者

zhouwf0726

版主

精华贴数 5

个人空间 2148

技术积分 8888 (213)

社区积分 312 (2616)

注册日期 2006-2-22

论坛徽章:45

#3使用道具

发表于 2007-6-8 15:05 今天新加一些内容

5 MYSQL数据库管理

我们在第二章已经简单介绍了一些MYSQL数据库的基本操作，这一章我们将针对MYSQL数据库

管理员详细介绍下MYSQL数据库的常用管理内容。 1 MYSQL界面管理工具

MYSQL的管理工具很多，我自己从网上下载了一个: mysql-gui-tools-noinstall-5.0-r12-win32，是一个不用安装的管理软件，包含四个基本

工具:

MySQLAdministrator

MySQLMigrationTool

MySQLQueryBrowser

MySQLSystemTrayMonitor

这些工具的具体使用都很简单，操作比较灵活，这里就不对这些工具的功能做详细介绍了，

如果有兴趣大家可以自己下载下来，多使用几次就熟悉了。

2 MYSQL命令行管理工具

a、首先介绍几个MYSQL命令行工具:

mysqld_safe、mysql.server和mysqld_multi是服务器启动脚本

注意:在Windows中不使用服务器启动脚本，我们可以使用WINDOWS命令来启动MYSQL服务:net start mysql

停止MYSQL服务: net stop mysql 或者:

C:Program FilesMySQLMySQL Server 5.0in>mysqladmin -uroot -p shutdown

Enter password: ******

mysqld是MySQL服务器

下面的表格列举了几种WINDOS平台支持的MYSQL服务器:

mysqld- debug 已经编译过，可以进行完全调试和自动内存分配检查，用于表InnoDB

和BDB。

mysqld 优化的二进制，支持InnoDB。

mysqld-nt 优化的二进制，支持Windows NT，2000 XP，有命名管道。 mysqld-max 优化的二进制，支持InnoDB和BDB表。

mysqld-max-nt 同mysqld-max，但是已经编译过，支持命名管道。

WINDOWS下执行二进制安装后，在WINDOWS服务中默认的就是使用的mysqld-nt服务器。

这个命令的使用我们将在后边的MYSQL服务器中详细讲解。

mysql_install_db初始化数据目录和初始数据库

mysql是一个命令行客户程序，用于交互式或以批处理模式执行SQL语句

这个命令我们在前边的例子中已经有很多应用了，这里就不多讲述了。我们给出一个例子:

C:Program FilesMySQLMySQL Server 5.0in>mysql -u root -p -e "SELECT * FROM TEST

limit 5;select * from test where id=1000" mytest

Enter password: ******

+------+------+---------------------+---------------------+ | ID | MC | DT | RQ | +------+------+---------------------+---------------------+ | 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 999 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 998 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 997 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 996 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +------+------+---------------------+---------------------+ +------+------+---------------------+---------------------+ | ID | MC | DT | RQ | +------+------+---------------------+---------------------+ | 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +------+------+---------------------+---------------------+

mysqladmin是用于管理功能的客户程序

mysqlcheck执行表维护操作

C:Program FilesMySQLMySQL Server 5.0in>mysqlcheck -uroot -p mytest

Enter password: ******

mytest.aaa OK mytest.customer OK mytest.mytable OK mytest.sys_tests OK mytest.test OK mytest.test1 OK mytest.test_isam OK

mysqldump数据库备份

mysqlhotcopy数据库备份

mysqlimport导入数据文件

这几个命令工具我们将在后边的备份恢复中详细讲解。

mysqlshow显示信息数据库和表的相关信息

C:Program FilesMySQLMySQL Server 5.0in>mysqlshow -uroot -p mytest

Enter password: ******

Database: mytest

+-----------+

| Tables |

+-----------+

| aa |

| aaa |

| customer |

| mytable |

| sys_tests |

| test |

| test1 |

| test_isam |

+-----------+

以下是几个可以独立于MYSQL服务器(客户端可以执行)进行操作的工作

myisamchk执行表维护操作

myisampack产生压缩、只读的表

mysqlbinlog是处理二进制日志文件的实用工具

perror显示错误代码的含义

我们这里对MYSQL的命令做了个简单介绍，如果要看更多内容，几乎所有MYSQL命令我们都可以用—help来获得帮助，另外我们可以从MYSQL联机文档中获得更多信息。

b、使用选项文件

MySQL程序可以从选项文件(有时也称为配置文件)读取启动选项。选项文件提供了一种很方便的方式来指定常用的选项，因此不需要每次运行程序时从命令行输入。

下面的程序支持选项文件:myisamchk、myisampack、mysql、mysql.server、mysqladmin、mysqlbinlog、mysqlcc、mysqlcheck、mysqld_safe、mysqldump、mysqld、mysqlhotcopy、mysqlimport和mysqlshow。我们在前边讲述创建用户数据库的时候已经提到过修改MYSQL的配置文件来修改数据文件的路径:配置文件”my.cnf”或”my.ini”(WINDOWS系统)

注释:在Unix平台上，MySQL忽略人人可写的配置文件。这是故意的，是一个安全措施。这

句话是在查阅资料时看到的，未加验证。

c、用环境变量指定选项

C:Program FilesMySQLMySQL Server 5.0in>set user=TEST

C:Program FilesMySQLMySQL Server 5.0in>mysql -p mytest Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 5 to server version: 5.0.27-community-nt

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> select user();

+----------------+

| user() |

+----------------+

| TEST@localhost |

+----------------+

1 row in set (0.06 sec)

d、使用选项设置程序变量

shell> mysql --max_allowed_packet= 2097152

shell> mysql --max_allowed_packet=2M

3 MYSQL实例管理器mysqlmanager

该工具软件需要单独下载:

该软件官方网站:

关于MYSQL实例管理器我们暂时不做太多解释。

4 MySQL服务器

WINDOWS下执行二进制安装后WINDOWS服务中默认的调用的是mysqld-nt服务器，我们可以在

WINDOWS的MySql服务的属性中看到:

"C:Program FilesMySQLMySQL Server 5.0inmysqld-nt" --defaults-file ="C:Program FilesMySQLMySQL Server 5.0my.ini" MySQL

我们也可以不用WINDOWS的默认MYSQL服务器，而启用mysqld服务器来

启动MYSQL:

C:Program FilesMySQLMySQL Server 5.0in>mysqld --console

070608 14:12:04 [Warning] Changed limits: max_open_files: 2048 max_connections: 800 table_cache: 619

070608 14:12:05 InnoDB: Started; log sequence number 0 956199 070608 14:12:05 [Note] mysqld: ready for connections.

Version: '5.0.27-community' socket: '' port: 3306 MySQL Community Edition (GP L)

如果省略--console选项，服务器向数据目录(默认为C:Program FilesMySQL MySQL

Server 5.1data)中的错误日志写入诊断输出。错误日志文件的扩展名为.err。

C:Program FilesMySQLMySQL Server 5.0in>mysqld --defaults-file = "C:Program FilesMySQLMySQL Server 5.0my.ini"

070608 9:32:34 [Warning] Changed limits: max_open_files: 2048 max_connections: 800 table_cache: 619

如果我们没有执行环境变量和程序变量，mysqld和mysqld-nt都是从配置文件中的[mysqld]

和[server]组读取选项。

我们可以执行以下命令来获得MYSQL数据库服务器的配置参数列表:

C:Program FilesMySQLMySQL Server 5.0in> mysqld --verbose –help

在MYSQL数据库中查看各个变量设置:

mysql> SHOW VARIABLES;

mysql> SHOW VARIABLES LIKE 'key_buffer_size'; +-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| key_buffer_size | 32505856 |

+-----------------+----------+

1 row in set (0.06 sec)

我们可以通过以下命令后的运行的数据库服务器的统计和状态指标:

mysql> SHOW STATUS;

+-----------------------------------+-----------+ | Variable_name | Value | +-----------------------------------+-----------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 144 | | Bytes_sent | 13078 |

mysql> show variables like 'sort_buffer%'; +------------------+----------+

| Variable_name | Value |

+------------------+----------+

| sort_buffer_size | 10485760 |

+------------------+----------+

1 row in set (0.09 sec)

mysql> SET sort_buffer_size = 5 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sort_buffer%'; +------------------+---------+

| Variable_name | Value |

+------------------+---------+

| sort_buffer_size | 5242880 |

+------------------+---------+

1 row in set (0.00 sec)

MYSQL的配置文件可以设置的项目很多，我们这里不做太多解释，在以后的优化等章节中我

们将会详细讲述一些我们常用的配置参数及调整。

停止MYSQL:

C:Program FilesMySQLMySQL Server 5.0in>mysqladmin -uroot -p shutdown

Enter password: ******

发表于 2007-6-9 22:36

MYSQL数据库管理部分新增加四节

5 MYSQL高速缓存管理

查询缓存存储SELECT查询的文本以及发送给客户端的相应结果。如果随后收到一个相同的查询，服务器从查询缓存中重新得到查询结果，而不再需要解析和执行查询。通过在configure中使用--without-query-cache选项，可以从服务器中彻底去除查询缓存能力。

查询解析之前进行比较，因此下面的两个查询被查询缓存认为是不相同的:

SELECT * FROM tbl_name

Select * from tbl_name

查询必须是完全相同的(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同默认字符集的查询被认为是不同的查询并且分别进行缓存。

如果一个表被更改了，那么使用那个表的所有缓冲查询将不再有效，并且从缓冲区中移出。这包括那些映射到改变了的表的使用MERGE表的查询。一个表可以被许多类型的语句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。

查询告诉缓存的大小

mysql> SHOW VARIABLES LIKE 'query_cache_size';

+------------------+----------+

| Variable_name | Value |

+------------------+----------+

| query_cache_size | 23068672 |

+------------------+----------+

1 row in set (0.01 sec)

修改告诉缓存的大小

mysql> SET GLOBAL query_cache_size = 31457280; Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+----------+

| Variable_name | Value |

+------------------+----------+

| query_cache_size | 31457280 |

+------------------+----------+

1 row in set (0.00 sec)

查看高速缓存的类型

mysql> SHOW VARIABLES LIKE 'query_cache_type'; +------------------+-------+

| Variable_name | Value |

+------------------+-------+

| query_cache_type | ON |

+------------------+-------+

1 row in set (0.00 sec)

高速缓存三种类型介绍

A、0或OFF将阻止缓存或查询缓存结果。

B、1或ON将允许缓存，以SELECT SQL_NO_CACHE开始的查询语句除外。

C、2或DEMAND，仅对以SELECT SQL_CACHE开始的那些查询语句启用缓存。

设置query_cache_type变量的GLOBAL值将决定更改后所有连接的缓存行为。具体SESSION可

以通过设置query_cache_type变量的会话值控制它们本身连接的缓存行为。例如，一个SESSION

可以禁用自己的查询缓存，方法如下:

mysql> SET SESSION query_cache_type = OFF;

Query OK, 0 rows affected (0.00 sec)

query_cache_limit

如果我们要控制可以被缓存的具体查询结果的最大值，应设置query_cache_limit变量。默认值是1MB。

mysql> SHOW VARIABLES LIKE 'query_cache_limit';

+-------------------+---------+

| Variable_name | Value |

+-------------------+---------+

| query_cache_limit | 1048576 |

+-------------------+---------+

1 row in set (0.00 sec)

query_cache_min_res_unit

当一个查询结果(返回给客户端的数据)从查询缓冲中提取期间，它在查询缓存中排序。因此，数据通常不在大的数据块中处理。查询缓存根据数据排序要求分配数据块，因此，当一个数据块用完后分配一个新的数据块。因为内存分配操作是昂贵的(费时的)，所以通过query_cache_min_res_unit系统变量给查询缓存分配最小值。当查询执行时，最新的结果数据块根据实际数据大小来确定，因此可以释放不使用的内存。根据服务器执行查询的类型，我们会发现调整query_cache_min_res_unit变量的值是有用的:query_cache_min_res_unit默认值是4KB。这应该适合大部分情况。

如果有大量返回小结果数据的查询，默认数据块大小可能会导致内存碎片，显示为大量空闲内存块。由于缺少内存，内存碎片会强制查询缓存从缓存内存中修整(删除)查询。这时，应该减少query_cache_min_res_unit变量的值。空闲块和由于修整而移出的查询的数量通过

Qcache_free_blocks和Qcache_lowmem_prunes变量的值给出。

如果大量查询返回大结果(检查 Qcache_total_blocks和Qcache_queries_in_cache状态变量)，可以通过增加query_cache_min_res_unit变量的值来提高性能。但是，注意不要使它变得太大(参见前面的条目)。

查询高速缓冲状态和维护

可以使用FLUSH QUERY CACHE语句来清理查询缓存碎片以提高内存使用性能。该语句不从缓存中移出任何查询。

RESET QUERY CACHE语句从查询缓存中移出所有查询。FLUSH TABLES语句也执行同样的工作。

为了监视查询缓存性能，使用SHOW STATUS查看缓存状态变量:

mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 31448464 | | Qcache_hits | 0 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 490 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ 8 rows in set (0.52 sec)

SELECT查询的总数量等价于:

Com_select

+ Qcache_hits

+ queries with errors found by parser

Com_select的值等价于:

Qcache_inserts

+ Qcache_not_cached

+ queries with errors found during columns/rights check

查询缓存使用长度可变块，因此Qcache_total_blocks和Qcache_free_blocks可以显示查询缓存内存碎片。执行FLUSH QUERY CACHE后，只保留一个空闲块。

每个缓存查询至少需要两个块(一个块用于查询文本，一个或多个块用于查询结果)。并且，每一个查询使用的每个表需要一个块。但是，如果两个或多个查询使用相同的表，仅需要分配一个块。

Qcache_lowmem_prunes状态变量提供的信息能够帮助你你调整查询缓存的大小。它计算为了缓存新的查询而从查询缓冲区中移出到自由内存中的查询的数目。查询缓冲区使用最近最少使用(LRU)策略来确定哪些查询从缓冲区中移出。

6 MYSQL数据库用户管理

1 创建数据库用户

mysql> create user 'zhouwf'@'localhost' identified by 'zhouwf';

Query OK, 0 rows affected (0.00 sec)

mysql> create user 'zhouwf'@'%' identified by 'zhouwf';

Query OK, 0 rows affected (0.00 sec)

2 给用户授权

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON mytest.* TO 'zhouwf'@'localhost'; Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON mytest.* TO 'zhouwf'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

3 删除用户

mysql> drop user 'zhouwf'@'localhost'; Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'zhouwf'@'%';

Query OK, 0 rows affected (0.00 sec)

4 修改用户密码

A、利用mysqladmin工具

C:Program FilesMySQLMySQL Server 5.0in>mysqladmin -uTEST -p password TTTT

Enter password: ****

C:Program FilesMySQLMySQL Server 5.0in>mysql -uTEST -pTTTT mytest Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 68 to server version: 5.0.27-community-nt

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

B、使用MYSQL数据库命令SET:

mysql> SET PASSWORD FOR 'TEST'@'LOCALHOST' = PASSWORD('TEST'); Query OK, 0 rows affected (0.00 sec)

C、使用MYSQL数据库命令GRANT IDENTIFIED BY:

mysql> grant usage on mytest.* to 'TEST'@'LOCALHOST' identified by 'TTTT'; Query OK, 0 rows affected (0.00 sec)

当然我们还可以利用REPLACE命令修改mysql.user表的内容的方式来修改密码，我们不建议

这么做，这里也就不给出例子了。

7 MYSQL数据库权限管理

MySQL存取控制包含2个阶段:

阶段1:服务器检查是否允许你连接。

阶段2:假定你能连接，服务器检查你发出的每个请求。看你是否有足够的权限

实施它。例如，如果你从数据库表中选择(select)行或从数据库删除表，服务器确定你对表

有SELECT权限或对数据库有DROP权限。

在这里我们不对MYSQL的各个权限做详细的解释，我们在使用的时候可以从MYSQL联机文档中

获得更多的详细信息。

8 MYSQL数据库备份与恢复

mysqlhotcopy

其命令格式如下(摘自MYSQL文档):

shell> mysqlhotcopy db_name /path/to/some/dir

只要服务器不再进行更新，还可以只复制所有表文件(*.frm、*.MYD和*.MYI文件)。mysqlhotcopy脚本使用该方法。(但请注意如果数据库包含InnoDB表，这些方法不工作。InnoDB不将表的内容保存到数据库目录中，mysqlhotcopy只适合MyISAM表)。

mysqldump

mysqldump提供在线逻辑备份，我们在备份的时候使用single-transaction参数的话，MYSQL为我们提供一致性地读，并且保证mysqldump所看见的数据不会更改。(其它客户端对InnoDB表进行的更改不会被mysqldump进程看见)该参数自动关闭--lock-tables。如果我们还有其它类型的表，我们必须假定在备份过程中它们不会更改。例如，对于mysql数据库中的MyISAM表，我们必须假定在备份过程中没有对MySQL账户进行管理更改。mysqldump命令产生的.sql文件包含一系列SQL INSERT语句，可以用来重载转储的表。

C:Program FilesMySQLMySQL Server 5.0in>mysqldump -uroot -p mytest >mytest.sql

Enter password: ******

生成的mytest.sql文件的示例内容如下:

-- MySQL dump 10.10

-- Host: localhost Database: mytest

-- ------------------------------------------------------

-- Server version 5.0.27-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0

*/;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Table structure for table `aaa`

DROP TABLE IF EXISTS `aaa`;

CREATE TABLE `aaa` (

`id` decimal(18,2) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `aaa`

LOCK TABLES `aaa` WRITE;

/*!40000 ALTER TABLE `aaa` DISABLE KEYS */;

INSERT INTO `aaa` VALUES ('3000.00');

/*!40000 ALTER TABLE `aaa` ENABLE KEYS */;

UNLOCK TABLES;

-- Table structure for table `customer`

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (

`A` int(11) default NULL,

`B` char(20) default NULL,

KEY `A` (`A`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

„„

LOCK TABLES `test_isam` WRITE;

/*!40000 ALTER TABLE `test_isam` DISABLE KEYS */;

INSERT INTO `test_isam` VALUES

(999013,'test'),(999014,'test'),(999015,'test'),(999016,'test'),(999017,'test'),(99

9018,'test'),(999019,'test'),(999020,'test') ,(999265,'test'),(999266,'test')

„„

mysqldump增量备份

要想进行增量备份，我们需要保存增量更改。应使用--log-bin选项启动MySQL服务器，以便更新数据时将这些更改保存到文件中。该选项启用二进制日志，因此服务器写将每个更新数据的SQL语句写入MySQL二进制日志。

C:Program FilesMySQLMySQL Server 5.0in>mysqld --defaults-file="C:Program

FilesMySQLMySQL Server 5.0my.ini" --log-bin

070609 17:58:42 [Warning] Changed limits: max_open_files: 2048 max_connections:

800 table_cache: 619

我们可以在basedir找到以下MySQL二进制日志文件:

mochasof-8ed6b1-bin.000001

mochasof-8ed6b1-bin.000002

mochasof-8ed6b1-bin.000003

每次重启，MySQL服务器用序列中的下一个编号创建一个新的二进制日志文件。当服务器运

行时，我们还可以通过执行FLUSH LOGS语句或mysqladmin flush-logs命令，告诉服务器关闭当

前的二进制日志文件并创建一个新文件。

C:Program FilesMySQLMySQL Server 5.0in>mysqladmin -uroot -p flush-logs

Enter password: ******

该命令类似于ORACLE的alter system switch logfile命令(日志切换)。mysqldump也有一个

选项来清空日志。数据目录中的.index文件包含该目录下所有MySQL二进制日志的清单。该文件

用于复制。

恢复时MySQL二进制日志很重要，因为它们是增量备份。如果进行完全备份时确保清空了日

志，则后面创建的二进制日志文件包含了备份后的所有数据更改。让我们稍稍修改前面的

mysqldump命令，让它在完全备份时能够清空 MySQL二进制日志，以便转储文件包含包含新的当

前的二进制日志，我们在basedir下看到MYSQL产生的最新的日志文件

mochasof-8ed6b1-bin.000004:

C:Program FilesMySQLMySQL Server 5.0in>mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases> backup_all.sql

Enter password: ******

Backup.sql文件包含下列行:

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='mochasof-8ed6b1-bin.000004', MASTER_LOG_POS=98;

-- Current Database: `mysql`

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `mysql`;

--master-data参数:

这个参数把二进制日志的位置和文件名写入备份文件中，如果该值等于1，在备份文件中将

会打印一个CHANGE MASTER命令;如果该值等于2，在备份文件中将会给出一个包括CHANGE MASTER

的注释，这个操作将会打开--lock-all-tables选项开关，除非和我们前边提到过的

--single-transaction参数选项一起结合使用，这时--single-transaction优先级高于

master-data，在备份期间人户引起日志改变的操作都自动将--lock-tables关闭。以下是从MYSQL

的help中获得的信息。

--master-data[=#] This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn

--lock-all-tables on, unless --single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump - don't forget to read about --single-transaction below). In all cases any action on logs will happen at the exact moment of the dump.Option automatically turns --lock-tables off.

因为mysqldump命令可以执行完全备份，这些行表示两件事情:

A、backup.sql文件包含所有写入mochasof-8ed6b1-bin.000004二进制日志文

件或最新的文件之前的更改。

B、备份后所记录的所有数据更改不出现在backup.sql中，但出现在

mochasof-8ed6b1-bin.000004二进制日志文件或最新的文件中。

接下来我们可以清空日志开始新的二进制日志文件来创建增量备份。例如，执行mysqladmin

flush-logs命令创建mochasof-8ed6b1-bin.000005。完全备份之后所有更改为文件

mochasof-8ed6b1-bin.000004。该增量备份很重要，因此最好将它复制到安全的地方。(例如，

备份到磁带或DVD上，或复制到另一台机器上)。以后我们执行另一个mysqladmin flush-logs命

令创建mochasof-8ed6b1-bin.000006，mochasof-8ed6b1-bin.000005也应复制到某个安全的地

方。

MySQL二进制日志占据硬盘空间。要想释放空间，应随时清空。操作方法是删掉不再使用的

二进制日志，例如进行完全备份时:

C:Program FilesMySQLMySQL Server 5.0in>mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --delete-master-logs mytest > mytest_new.sql

Enter password: ******

注释:如果你的服务器为复制主服务器，用mysqldump --delete-master-logs删掉MySQL二

进制日志很危险，因为从服务器可能还没有完全处理该二进制日志的内容。

PURGE MASTER LOGS语句的描述中解释了为什么在删掉MySQL二进制日志之前应进行确认。

一个简单的恢复测试

做全库备份:

C:Program FilesMySQLMySQL Server 5.0in>mysqldump -uroot -p

--single-transaction --flush-logs --master-data=2 mytest > backup_mytest.sql Enter password: ******

备份后创建新的测试表并INSERT测试数据:

C:Program FilesMySQLMySQL Server 5.0in>mysql -uTEST -p mytest Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 10 to server version: 5.0.27-community-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> SHOW TABLES;

+------------------+

| Tables_in_mytest |

+------------------+

| aaa |

| customer |

| mytable |

| sys_tests |

| test |

| test1 |

| test_isam |

+------------------+

7 rows in set (0.00 sec)

mysql> CREATE TABLE NEW_TABLE(ID VARCHAR(20),MC VARCHAR(60));

Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO NEW_TABLE VALUES('1','111'); Query OK, 1 row affected (0.23 sec)

mysql> SELECT * FROM NEW_TABLE;

+------+------+

| ID | MC |

+------+------+

| 1 | 111 |

+------+------+

1 row in set (0.00 sec)

mysql> FLUSH LOGS;

Query OK, 0 rows affected (0.53 sec)

删除所有TABLE后:

mysql> SHOW TABLES;

Empty set (0.00 sec)

我们从全库备份文件执行恢复操作:

C:Program FilesMySQLMySQL Server 5.0in>mysql -uroot -p mytest <

backup_mytest.sql

Enter password: ******

C:Program FilesMySQLMySQL Server 5.0in>mysql -uTEST -p mytest

Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 19 to server version: 5.0.27-community-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> SHOW TABLES;

+------------------+

| Tables_in_mytest |

+------------------+

| aaa |

| customer |

| mytable |

| sys_tests |

| test |

| test1 |

| test_isam |

+------------------+

7 rows in set (0.01 sec)

这时我们发现新创建的测试表丢失,我们将应用增量备份来恢复最新数据

C:Program FilesMySQLMySQL Server 5.0in>mysqlbinlog -uroot -p

E:MySqlDatamochasof-8ed6b1-bin.000003 | mysql -uTEST -p mytest Enter password: Enter password: ******

****

C:Program FilesMySQLMySQL Server 5.0in>mysql -uTEST -p mytest Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 24 to server version: 5.0.27-community-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> SELECT * FROM NEW_TABLE;

+------+------+

| ID | MC |

+------+------+

| 1 | 111 |

+------+------+

1 row in set (0.00 sec)

下面就介绍一下这几个目录。

1、数据库目录

/var/lib/mysql/

2、配置文件

/usr/share/mysql(mysql.server命令及配置文件)

3、相关命令

/usr/bin(mysqladmin mysqldump等命令)

4、启动脚本

/etc/rc.d/init.d/(启动脚本文件mysql的目录)

五、修改登录密码

MySQL默认没有密码，安装完毕增加密码的重要性是不言而喻的。

1、命令

usr/bin/mysqladmin -u root password 'new-password'

格式:mysqladmin -u用户名 -p旧密码 password 新密码

2、例子

例1:给root加个密码123456。

键入以下命令 :

[root@test1 local]# /usr/bin/mysqladmin -u root password 123456

注:因为开始时root没有密码，所以-p旧密码一项就可以省略了。

3、测试是否修改成功

1)不用密码登录

[root@test1 local]# mysql

ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

显示错误，说明密码已经修改。

2)用修改后的密码登录

[root@test1 local]# mysql -u root -p

Enter password: (输入修改后的密码123456)

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

Your MySQL connection id is 4 to server version: 4.0.16-standard

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

mysql>

成功～

这是通过mysqladmin命令修改口令，也可通过修改库来更改口令。

六、启动与停止

1、启动

MySQL安装完成后启动文件mysql在/etc/init.d目录下，在需要启动时运行下面命令

即可。

[root@test1 init.d]# /etc/init.d/mysql start

mysqladmin -u root password root

2、停止

/usr/bin/mysqladmin -u root -p shutdown

3、自动启动

1)察看mysql是否在自动启动列表中

[root@test1 local]# /sbin/chkconfig –list

2)把MySQL添加到你系统的启动服务组里面去

[root@test1 local]# /sbin/chkconfig – add mysql

3)把MySQL从启动服务组里面删除。

[root@test1 local]# /sbin/chkconfig – del mysql

七、更改MySQL目录

MySQL默认的数据文件存储目录为/var/lib/mysql。假如要把目录移到/home/data下

需要进行下面几步:

cd /home

mkdir data

2、把MySQL服务进程停掉:

mysqladmin -u root -p shutdown

3、把/var/lib/mysql整个目录移到/home/data

mv /var/lib/mysql /home/data/

这样就把MySQL的数据文件移动到了/home/data/mysql下

4、找到my.cnf配置文件

如果/etc/目录下没有my.cnf配置文件，请到/usr/share/mysql/下找到*.cnf文件，

拷贝其中一个到/etc/并改名为my.cnf)中。命令如下:

[root@test1 mysql]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

5、编辑MySQL的配置文件/etc/my.cnf

为保证MySQL能够正常工作，需要指明mysql.sock文件的产生位置。修改

socket=/var/lib/mysql/mysql.sock一行中等号右边的值为:/home/mysql/mysql.sock 。操作如下:

vimy.cnf (用vi工具编辑my.cnf文件，找到下列数据修改之)

# The MySQL server

[mysqld]

port = 3306

#socket= /var/lib/mysql/mysql.sock(原内容，为了更稳妥用“#”注释此行)

socket= /home/data/mysql/mysql.sock (加上此行)

6、修改MySQL启动脚本/etc/rc.d/init.d/mysql

最后，需要修改MySQL启动脚本/etc/rc.d/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等号右边的路径改成你现在的实际存放路径:home/data/mysql。

[root@test1 etc]# vi /etc/rc.d/init.d/mysql

#datadir=/var/lib/mysql (注释此行)

datadir=/home/data/mysql (加上此行)

7、重新启动MySQL服务

/etc/rc.d/init.d/mysql start

或用reboot命令重启Linux

如果工作正常移动就成功了，否则对照前面的7步再检查一下。

八、MySQL的常用操作

注意:MySQL中每个命令后都要以分号;结尾。

1、显示数据库

mysql> show databases;

+----------+

| Database |

+----------+

| mysql |

| test |

+----------+

2 rows in set (0.04 sec)

Mysql刚安装完有两个数据库:mysql和test。mysql库非常重要，它里面有MySQL的系

统信息，我们改密码和新增用户，实际上就是用这个库中的相关表进行操作。

2、显示数据库中的表

mysql> use mysql; (打开库，对每个库进行操作就要打开此库，类似于foxpro )

Database changed

mysql> show tables;

+-----------------+

| Tables_in_mysql |

+-----------------+

| columns_priv |

| db |

| func |

| host |

| tables_priv |

| user |

+-----------------+

6 rows in set (0.01 sec)

3、显示数据表的结构:

describe 表名;

4、显示表中的记录:

select * from 表名;

例如:显示mysql库中user表中的纪录。所有能对MySQL用户操作的用户都在此表中。

Select * from user;

5、建库:

create database 库名;

例如:创建一个名字位aaa的库

mysql> create databases aaa;

6、建表:

use 库名;

create table 表名 (字段设定列表);

例如:在刚创建的aaa库中建立表name,表中有id(序号，自动增长)，xm(姓名),xb(性

别),csny(出身年月)四个字段

use aaa;

mysql> create table name (id int(3) auto_increment not null primary key, xm

char(8),xb char(2),csny date);

可以用describe命令察看刚建立的表结构。

mysql> describe name;

+-------+---------+------+-----+---------+----------------+

+-------+---------+------+-----+---------+----------------+

| id | int(3) | | PRI | NULL | auto_increment |

| xm | char(8) | YES | | NULL | |

| xb | char(2) | YES | | NULL | |

+-------+---------+------+-----+---------+----------------+

7、增加记录

例如:增加几条相关纪录。

mysql> insert into name values('','张三','男','1971-10-01');

mysql> insert into name values('','白云','女','1972-05-20');

可用select命令来验证结果。

mysql> select * from name;

+----+------+------+------------+

| id | xm| xb| csny |

+----+------+------+------------+

| 1 | 张三 | 男| 1971-10-01 |

| 2 | 白云 | 女| 1972-05-20 |

+----+------+------+------------+

8、修改纪录

例如:将张三的出生年月改为1971-01-10

mysql> update name set csny='1971-01-10' where xm='张三';

9、删除纪录

例如:删除张三的纪录。

mysql> delete from name where xm='张三';

10、删库和删表

drop database 库名;

drop table 表名;

九、增加MySQL用户

格式:grant select on 数据库.* to 用户名@登录主机 identified by "密码"

例1、增加一个用户user_1密码为123，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL，然后键入以下命令:

mysql> grant select,insert,update,delete on *.* to user_1@"%" Identified by

"123";

例1增加的用户是十分危险的，如果知道了user_1的密码，那么他就可以在网上的任何一台电脑上登录你的MySQL数据库并对你的数据为所欲为了，解决办法见例2。

例2、增加一个用户user_2密码为123,让此用户只可以在localhost上登录，并可以对数据库aaa进行查询、插入、修改、删除的操作(localhost指本地主机，即MySQL数据库所在的那台主机)，这样用户即使用知道user_2的密码，他也无法从网上直接访问数据库，只能通过 MYSQL主机来操作aaa库。

mysql>grant select,insert,update,delete on aaa.* to user_2@localhost

identified by "123";

用新增的用户如果登录不了MySQL，在登录时用如下命令:

mysql -u user_1 -p -h 192.168.113.50 (-h后跟的是要登录主机的ip地址)

十、备份与恢复

1、备份

例如:将上例创建的aaa库备份到文件back_aaa中

[root@test1 root]# cd /home/data/mysql (进入到库目录，本例库已由

val/lib/mysql转到/home/data/mysql，见上述第七部分内容)

[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa

2、恢复

[root@test mysql]# mysql -u root -p ccc < back_aaa

修改数据库的字符集

mysql>use mydb

mysql>alter database mydb character set utf-8; 创建数据库指定数据库的字符集

mysql>create database mydb character set utf-8;

通过配置文件修改:

修改/var/lib/mysql/mydb/db.opt

default-character-set=latin1

default-collation=latin1_swedish_ci

为

default-character-set=utf8

default-collation=utf8_general_ci

重起MySQL:

[root@bogon ~]# /etc/rc.d/init.d/mysql restart

查看Mysql 的相关编码类型;

mysql> show variables like 'character\_set\_%';

+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | utf8 | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | +--------------------------+-------+

通过MySQL命令行修改:

set character_set_client=utf8;

set character_set_connection=utf8; set character_set_database=utf8;

set character_set_results=utf8; set character_set_server=utf8; set character_set_system=utf8; set character_set_filesystem=utf8; set collation_connection=utf8;

set collation_database=utf8;

set collation_server=utf8;

set character_set_filesystem=utf-8;

8 rows in set (0.03 sec)

mysql> show variables like 'collation_%';

+----------------------+-----------------+

| Variable_name | Value |

+----------------------+-----------------+

| collation_connection | utf8_general_ci |

| collation_database | utf8_general_ci |

| collation_server | utf8_general_ci |

+----------------------+-----------------+ 3 rows in set (0.04 sec)

[root@localhost /]#service mysql stop;

[root@localhost /]#service mysql start;

1、准备安装程序(官方网站下载)

服务端:MySQL-server-community-5.1.44-1.rhel4.i386.rpm 客户端:MySQL-client-community-5.1.44-1.rhel4.i386.rpm 2、安装(打印信息略)

[root@localhost /]#rpm -ivm MySQL-server-community-5.1.44-1.rhel4.i386.rpm;

[root@localhost /]#rpm -ivm MySQL-client-community-5.1.44-1.rhel4.i386.rpm;

3、查看安装是否成功

[root@localhost /]#netstat -ntpl;

显示3306端口开放表示服务安装成功

4、测试连接

[root@localhost /]#mysql

出现mysql>表示登陆成功

5、对mysql用户设置远程访问权限

方法1、改表法:登陆mysql后，更改“mysql”数据库里的“user”表里的“host”项，将“localhost”改“%”

mysql>use mysql;

mysql>update user set host ='%' where user ='root';

mysql>select host,user from user;

mysql>FLUSH PRIVILEGES

方法2、授权法:假设允许用户username通过密码password从远程连接到mysql服务器 mysql>GRANT ALL RRIVILEGES ON *.* TO username@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

mysql>FLUSH PRIVILEGES;

6、安装目录结构

数据库目录:/var/lib/mysql/

配置文件:/usr/share/mysql(mysql.server命令及配置文件)

相关命令:/usr/bin(mysqladmin、mysqldump等命令)(*mysql的一种安全启动方式:/usr/bin/mysqld_safe --user=root &)

启动脚本:/etc/rc.d/init.d/

7、数据库目录转移(未实验过)

在根目录下新建文件夹

[root@localhost /]#mkdir mysqldata 停止MYSQL服务器 service mysql stop

复制/var/lib/mysql下所有文件到/mysqldata

查看原数据库文件夹下各文件权限默认为mysql

更改/mysqldata下各文件权限

[root@localhost /]#chgrp -R mysql ./mysqldata && chown -R mysql ./mysqldata

检查权限 [root@localhost /]#ls -l

配置my.cnf文件

复制并更改文件到/etc目录下

[root@localhost mysql]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

修改my.cnf

#The MySQL server

[client]

#password = your_password port = 3306

#socket = /var/lib/mysql/mysql.sock

socket =/mysqldata/mysql.sock --增加此行，并配置数据库目录 [mysqld]

port = 3306

#socket = /var/lib/mysql/mysql.sock

socket =/mysqldata/mysql.sock --增加此行，并配置数据库目录修改启动文件

修改/etc/rc.d/init.d/mysql 找到datadir位置，修改内容如下

#Set some defaults

pid_file=

server_pid_file=

use_mysqld_safe=1

user=mysql

if test -z "$basedir"

then

basedir=/

bindir=/usr/bin

if test -z "$datadir"

then

#datadir=/var/lib/mysql --修改前内容

datadir=/mysqldata --修改后内容

sbindir=/usr/sbin

8、停止/启动mysql服务

[root@localhost /]#service mysql stop; [root@localhost /]#service mysql start; 9、修改字符编码

查看字符编码:

[root@localhost /]#show variables like 'character\_set\_%';

[root@localhost /]#show variables like 'collation_%';

停止mysql服务

将目录/usr/share/mysql下的文件my-medium.cnf拷贝到/etc/下并改名为my.cnf 打开my.cnf在[client]和[mysqld]下面均加上default-character-set=utf8,并保存重启mysql服务

Infobright基于MySQL的数据仓库		MySQL常用命令
MySQL数据库同步		MySQL常用语句
常用基本MySQL语句		MySQL创建用户
MySQL学习分享		MySQL常用命令用法
MySQL的部分用法		MySQL读写分离Amoeba的实现
MySQL的分页的优化		MySQL正则表达式的描述
MySQL的命名规则		MySQL的information_schema数据库介绍
MySQL数据库知识详解		MySQL LAST_INSERT_ID详解

信息发布：广州名易软件有限公司 http://www.myidp.net


版权所有：广州名易软件有限公司（www.myidp.net）销售热线：19927081598 技术支持：13332852796 粤ICP备16028553号