mysql学习之路一（转）
原文章地址：http://www.itpub.net/thread-788049-1-1.html
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/en/extended-show.html.
