脱离企业战略 IT曲线救国也能成功-信息化专区

脱离企业战略 IT曲线救国也能成功

作者：IT168 周卫丰编辑：赵建凯 2007-06-29 12:28

一、MySQL安装

1. MYSQL命令规则介绍

MySQL的命名机制使用由3个数字和一个后缀组成的版本号。例如，像mysql-5.0.27-beta的版本号这样解释：

第1个数字(5)是主版本号，描述了文件格式。所有版本5的发行都有相同的文
件格式。
第2个数字(0)是发行级别。主版本号和发行级别组合便构成了发行序列号。
第3个数字(9)是在此发行系列的版本号，随每个新分发版递增。通常你需要已
经选择的发行(release)的最新版本(版本)。

2. MYSQL安装

先下载安装包(MySQL AB编译的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联机文档。

3. 安装后文件布局

在Windows中，MySQL 5.1的默认安装目录是C:\Program Files\MySQL\MySQL Server 5.1。(一些Windows用户宁愿安装到原来的默认安装目录 C：\mysql。然而，子目录布局仍然相同)。安装目录包括以下子目录：

二、登录及一些基本操作

本章的主要目的是让我们对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的权限列表功参考：

关于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/en/extended-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（不推荐）

mysql>INSERT INTO mysql.user VALUES('localhost','zhouwf', PASSWORD ('zho uwf'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

（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表排序顺序中排在前面。

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

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)

创建临时表

MYSQL临时表是SESSION级别的，并且程序退出临表自动删除。

mysql> CREATE TEMPORARY TABLE TEMP(ID VARCHAR(20),MC VARCHAR(60));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into temp values('111','111111');
Query OK, 1 row affected (0.08 sec)

mysql> select * from temp;
+------+--------+
| ID   | MC     |
+------+--------+
| 111  | 111111 |
+------+--------+
1 row in set (0.00 sec)

（2）创建索引

MYSQL索引类型：

普通索引

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

创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

唯一性索引

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

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );

主键

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。
主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表
的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

全文索引

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 |       |
+-------+-------------+------+-----+---------------------+-------+
1    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: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;
+------+------+---------------------+---------------------+
| 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)

关注我们