MySql经典笔记.doc

好久没有整理东西，感觉自己越来越懒，正好近期有个MYSQL的项目（一个ORACLE的应用迁移到MYSQL），以前没有接触过MYSQL，但是也知道MYSQL数据库目前广泛的应用在各种个人、商务系统中，各种技术都比较成熟。把自己学习的一些过程总结一下，该文章设计到的内容都没有做太详细的阐述，只是一个简单的入门手册，如果想看更多内容可以参看MYSQL的联机手册。-zhouwf0726 2007-06-5第一章 MYSQL安装MYSQL命令规则介绍：MySQL的命名机制使用由3个数字和一个后缀组成的版本号。例如，像mysql-5.0.27-beta的版本号这样解释：第1个数字(5)是主版本号，描述了文件格式。所有版本5的发行都有相同的文件格式。第2个数字(0)是发行级别。主版本号和发行级别组合便构成了发行序列号。第3个数字(9)是在此发行系列的版本号，随每个新分发版递增。通常你需要已经选择的发行(release)的最新版本(版本)。MYSQL安装：先下载安装包(MySQL AB编译的MySQL二进制版本)：mysql-5.0.27-win32.zipmysql-noinstall-6.0.0-alpha-win32.zip下载了2个版本：一个5.0.27安装版；一个6.0.0非安装版。本人测试用的为5.0.27安装版，安装过程不再赘述。各个操作系统平台的安装可以看MYSQL联机文档。安装后文件布局：在Windows中，MySQL 5.1的默认安装目录是C:Program FilesMySQLMySQL Server 5.1。(一些Windows用户宁愿安装到原来的默认安装目录 C：mysql。然而，子目录布局仍然相同)。安装目录包括以下子目录：目录目录内容bin客户端程序和mysqld服务器data日志文件，数据库Docs文档examples示例程序和脚本include包含(头)文件lib库scripts实用工具脚本share错误消息文件第二章 登录及一些基本操作本章的主要目的是让我们对MYSQL的基础框架有个大概的了解。1 连接与断开MYSQL服务器安装完毕登陆MYSQL(有过一些其他数据库基础的人都应该很容易使用这几步)：我们可以利用如下参数查看MYSQL命令的帮助：C:Program FilesMySQLMySQL Server 5.0binmysql help联接MYSQL服务器：C:Program FilesMySQLMySQL Server 5.0binmysql -h localhost -uroot -p888888Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 22 to server version: 5.0.27-community-ntType 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 QUITBye我们也可以在连接MYSQL服务器的时候指定想要连接的数据库，如下：C:Program FilesMySQLMySQL Server 5.0binmysql -user=root -p mysqlEnter password: *Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 7 to server version: 5.0.27-community-ntType 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 changedmysql 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 | rootlocalhost |+-+-+-+-+-+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 rootlocalhost |+-+| GRANT ALL PRIVILEGES ON *.* TO rootlocalhost IDENTIFIED BY PASSWORD *DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB 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 GRANTOPTION |+-+1 row in set (0.00 sec)我们也可以通过查询系统表来获得用户的权限：mysql SELECT * FROM USER_PRIVILEGES;下边给出了MYSQL的权限列表功参考：PrivilegeMeaningALL PRIVILEGESSets all simple privileges except GRANT OPTION ALTEREnables use of ALTER TABLE ALTER ROUTINEEnables stored routines to be altered or droppedCREATEEnables use of CREATE TABLE CREATE ROUTINEEnables creation of stored routinesCREATE TEMPORARY TABLESEnables use of CREATE TEMPORARY TABLE CREATE USEREnables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.CREATE VIEWEnables use of CREATE VIEW DELETEEnables use of DELETE DROPEnables use of DROP TABLE EXECUTEEnables the user to run stored routinesFILEEnables use of SELECT . INTO OUTFILE and LOAD DATA INFILE INDEXEnables use of CREATE INDEX and DROP INDEX INSERTEnables use of INSERT LOCK TABLESEnables use of LOCK TABLES on tables for which you have the SELECT privilegePROCESSEnables use of SHOW FULL PROCESSLIST REFERENCESNot implementedRELOADEnables use of FLUSH REPLICATION CLIENTEnables the user to ask where slave or master servers areREPLICATION SLAVENeeded for replication slaves (to read binary log events from the master)SELECTEnables use of SELECT SHOW DATABASESSHOW DATABASES shows all databasesSHOW VIEWEnables use of SHOW CREATE VIEW SHUTDOWNEnables use of mysqladmin shutdown SUPEREnables 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 reachedUPDATEEnables use of UPDATE USAGESynonym for “no privileges”GRANT OPTIONEnables privileges to be granted关于SHOW命令我们就简单介绍这么几个，如果想知道更多的SHOW命令可以得到的信息内容可以执行如下命令来获取帮助或者参看MYSQL的联机文档第23章：INFORMATION_SCHEMA信息数据库。mysql HELP SHOWName: SHOWDescription:SHOW has many forms that provide information about databases, tables,columns, or status information about the server. This section describesthose following:SHOW FULL COLUMNS FROM tbl_name FROM db_name LIKE patternSHOW CREATE DATABASE db_nameSHOW CREATE FUNCTION funcnameSHOW CREATE PROCEDURE procnameSHOW CREATE TABLE tbl_nameSHOW DATABASES LIKE patternSHOW ENGINE engine_name LOGS | STATUS SHOW STORAGE ENGINESSHOW ERRORS LIMIT offset, row_countSHOW FUNCTION STATUS LIKE patternSHOW GRANTS FOR u