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 Files\MySQL\MySQL Server 5.0\bin>mysql –help

联接MYSQL服务器：

C:\Program Files\MySQL\MySQL Server 5.0\bin>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 Files\MySQL\MySQL Server 5.0\bin>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
中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

2 使用数据库

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();
--------------------- -------------- ------------ --------------------- -------
---------
| 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';
--------------- ---------------------
| Variable_name | Value |
--------------- ---------------------
| version | 5.0.27-community-nt |
--------------- ---------------------
1 row in set (0.13 sec)
3 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 |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
---------------------------------------
16 rows in set (0.00 sec)

SHOW TABLES命令显示了当前用数据库中的数据库对象列表，而从TABLES视图的查询我们将得到所有数据库下的对象列表。这个例子就是给出了一个查询MYSQL的表相关的系统视图，类似ORACLE中的(DBA_TABLES、USER_TABLES)和SYBASE中的SYSOBJECTS。

mysql> SELECT TABLE_NAME, TABLE_TYPE, ENGINE FROM TABLES;
--------------------------------------- ------------- --------
| table_name | table_type | engine |
--------------------------------------- ------------- --------
| CHARACTER_SETS | SYSTEM VIEW | MEMORY |
| COLLATIONS | SYSTEM VIEW | MEMORY |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY |
| COLUMNS | SYSTEM VIEW | MyISAM |
| COLUMN_PRIVILEGES | SYSTEM VIEW | MEMORY |
| KEY_COLUMN_USAGE | SYSTEM VIEW | MEMORY |
| ROUTINES | SYSTEM VIEW | MyISAM |
| SCHEMATA | SYSTEM VIEW | MEMORY |
| SCHEMA_PRIVILEGES | SYSTEM VIEW | MEMORY |
| STATISTICS | SYSTEM VIEW | MEMORY |
| TABLES | SYSTEM VIEW | MEMORY |
| TABLE_CONSTRAINTS | SYSTEM VIEW | MEMORY |
| TABLE_PRIVILEGES | SYSTEM VIEW | MEMORY |
| TRIGGERS | SYSTEM VIEW | MyISAM |
| USER_PRIVILEGES | SYSTEM VIEW | MEMORY |
| VIEWS | SYSTEM VIEW | MyISAM |
| columns_priv | BASE TABLE | MyISAM |
| db | BASE TABLE | MyISAM |
| func | BASE TABLE | MyISAM |
| help_category | BASE TABLE | MyISAM |
| help_keyword | BASE TABLE | MyISAM |
| help_relation | BASE TABLE | MyISAM |
| help_topic | BASE TABLE | MyISAM |
| host | BASE TABLE | MyISAM |
| proc | BASE TABLE | MyISAM |
| procs_priv | BASE TABLE | MyISAM |
| tables_priv | BASE TABLE | MyISAM |
| time_zone | BASE TABLE | MyISAM |
| time_zone_leap_second | BASE TABLE | MyISAM |
| time_zone_name | BASE TABLE | MyISAM |
| time_zone_transition | BASE TABLE | MyISAM |
| time_zone_transition_type | BASE TABLE | MyISAM |
| user | BASE TABLE | MyISAM |
--------------------------------------- ------------- --------
33 rows in set (0.03 sec)


mysql> SHOW COLUMNS FROM TABLES;
----------------- -------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
----------------- -------------- ------ ----- --------- -------
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) | YES | | NULL | |
| DATA_LENGTH | bigint(21) | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) | YES | | NULL | |
| INDEX_LENGTH | bigint(21) | YES | | NULL | |
| DATA_FREE | bigint(21) | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(64) | YES | | NULL | |
| CHECKSUM | bigint(21) | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(80) | NO | | | |
----------------- -------------- ------ ----- --------- -------
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 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
---------- ----------------------------- --------------------- --------
36 rows in set (0.00 sec)

mysql> SHOW CHARACTER SET like 'big5';
--------- -------------------------- ------------------- --------
| Charset | Description | Default collation | Maxlen |
--------- -------------------------- ------------------- --------
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
--------- -------------------------- ------------------- --------
1 row in set (0.00 sec)


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)

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

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
http://dev.mysql.com/doc/refman/5.0...nded-show.html.

4 创建用户数据库

mysql> CREATE DATABASE MYTEST;
Query OK, 1 row affected (0.00 sec)

假如想改变MYSQL数据文件的默认路径，我们可以关闭MYSQL实例，修改配置文件”my.cnf”或”my.ini”（WINDOWS系统）中的datadir对应的参数值，然后把MYSQL默认安装的datadir下的内容拷贝到新的数据文件路径下，启动MYSQL实例这样我们再次创建数据库的时候数据文件就放在新的路径下了。

mysql> SHOW DATABASES;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| mytest |
| root |
--------------------
4 rows in set (0.01 sec)

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)

5 创建数据库用户
可以用两种方式创建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 Files\MySQL\MySQL Server 5.0\bin>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)

6 创建数据库对象
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(缺省值)\12\8\6。

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;
------- ------------- ------ ----- --------------------- ----------------
| Field | Type | Null | Key | Default | Extra |
------- ------------- ------ ----- --------------------- ----------------
| 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)

2 创建索引

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。

---

创建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

3 创建存储过程

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语句块结束*/

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

给出一个最简单的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 OOP
...
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;
------ ------ --------------------- ---------------------
| 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 |
| 995 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 994 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 993 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 992 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 991 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 990 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 989 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 988 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 987 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 986 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 985 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 984 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 983 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 982 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 981 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
------ ------ --------------------- ---------------------
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)

3 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] ;

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

---

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 Files\MySQL\MySQL Server 5.0\bin>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 Files\MySQL\MySQL Server 5.0\bin>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 Files\MySQL\MySQL Server 5.0\bin>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 Files\MySQL\MySQL Server 5.0\bin>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忽略人人可写的配置文件。这是故意的，是一个安全