|
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安裝版，安裝過程不再贅述。各個操作系統(tǒng)平臺的安裝可以看MYSQL聯(lián)機(jī)文檔。 2 登錄及一些基本操作 本章的主要目的是讓我們對MYSQL的基礎(chǔ)框架有個大概的了解。 1 連接與斷開MYSQL服務(wù)器 安裝完畢登陸MYSQL(有過一些其他數(shù)據(jù)庫基礎(chǔ)的人都應(yīng)該很容易使用這幾步): 我們可以利用如下參數(shù)查看MYSQL命令的幫助: C:Program FilesMySQLMySQL Server 5.0in>mysql –help 聯(lián)接MYSQL服務(wù)器: C:Program FilesMySQLMySQL Server 5.0in>mysql -h localhost -uroot -p888888 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 22 to server version: 5.0.27-community-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | root | +--------------------+ 3 rows in set (0.08 sec) 安裝完畢，都會有這幾個默認(rèn)的數(shù)據(jù)庫。 注意到目前沒有連接到任何數(shù)據(jù)庫。 mysql> SELECT DATABASE(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.78 sec) mysql> QUIT Bye 我們也可以在連接MYSQL服務(wù)器的時候指定想要連接的數(shù)據(jù)庫，如下: C:Program FilesMySQLMySQL Server 5.0in>mysql --user=root -p mysql Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 7 to server version: 5.0.27-community-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec) INFORMATION_SCHEMA數(shù)據(jù)庫 我們在介紹MYSQL基本操作的同時順便把INFORMATION_SCHEM數(shù)據(jù)庫做個簡單介紹: 類似其他數(shù)據(jù)庫的數(shù)據(jù)字典，各個字典含義不做詳述，以下摘自MYSQL聯(lián)機(jī)文檔: INFORMATION_SCHEMA提供了訪問數(shù)據(jù)庫元數(shù)據(jù)的方式。 元數(shù)據(jù)是關(guān)于數(shù)據(jù)的數(shù)據(jù)，如數(shù)據(jù)庫名或表名，列的數(shù)據(jù)類型，或訪問權(quán)限等。有些時候用 于表述該信息的其他術(shù)語包括“數(shù)據(jù)詞典”和 “系統(tǒng)目錄”。INFORMATION_SCHEMA是信息數(shù)據(jù)庫，其中保存著關(guān)于MySQL服務(wù)器所維護(hù)的 所有其他數(shù)據(jù)庫的信息。在INFORMATION_SCHEMA 中，有數(shù)個只讀表。它們實(shí)際上是視圖，而不是基本表，因此，你將無法看到與之相關(guān)的任 何文件。 3 使用數(shù)據(jù)庫 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) 4 MYSQL的SHOW命令 前邊的例子中我們已經(jīng)用過了MYSQL的SHOW命令: mysql> SHOW DATABASES; SHOW命令可用于獲取關(guān)于INFORMATION_SCHEMA本身結(jié)構(gòu)的信息。 一些SHOW語句允許使用FROM、WHERE子句，這樣，在指定需要顯示的行時，可更為靈活。下 邊給出部分例子: mysql> SHOW TABLES FROM MYSQL; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | proc | | procs_priv | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 17 rows in set (0.00 sec) mysql> SHOW TABLES; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | ………… 16 rows in set (0.00 sec) SHOW TABLES命令顯示了當(dāng)前用數(shù)據(jù)庫中的數(shù)據(jù)庫對象列表，而從TABLES視圖的查詢我們將 得到所有數(shù)據(jù)庫下的對象列表。這個例子就是給出了一個查詢MYSQL的表相關(guān)的系統(tǒng)視圖，類似 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 | …… 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 | | …… 21 rows in set (0.06 sec) mysql> SHOW CHARACTER SET; +----------+-----------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+-----------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | ……… 36 rows in set (0.00 sec) mysql> SHOW CHARACTER SET like 'big5'; +---------+--------------------------+-------------------+--------+ | 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) 5 我們也可以通過查詢系統(tǒng)表來獲得用戶的權(quán)限: mysql> SELECT * FROM USER_PRIVILEGES; 下邊給出了MYSQL的權(quán)限列表功參考: 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 關(guān)于SHOW命令我們就簡單介紹這么幾個，如果想知道更多的SHOW命令可以得到的信息內(nèi)容可 以執(zhí)行如下命令來獲取幫助或者參看MYSQL的聯(lián)機(jī)文檔第23章:INFORMATION_SCHEMA信息數(shù)據(jù)庫。 mysql> HELP SHOW Name: 'SHOW' Description: SHOW has many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following: SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern'] SHOW CREATE DATABASE db_name SHOW CREATE FUNCTION funcname SHOW CREATE PROCEDURE procname SHOW CREATE TABLE tbl_name SHOW DATABASES [LIKE 'pattern'] SHOW ENGINE engine_name {LOGS | STATUS } SHOW [STORAGE] ENGINES SHOW ERRORS [LIMIT [offset,] row_count] SHOW FUNCTION STATUS [LIKE 'pattern'] SHOW GRANTS FOR user SHOW INDEX FROM tbl_name [FROM db_name] SHOW INNODB STATUS SHOW PROCEDURE STATUS [LIKE 'pattern'] SHOW [BDB] LOGS SHOW MUTEX STATUS SHOW PRIVILEGES SHOW [FULL] PROCESSLIST SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern'] SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] SHOW TRIGGERS SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern'] SHOW WARNINGS [LIMIT [offset,] row_count] The SHOW statement also has forms that provide information about replication master and slave servers and are described in [HELP PURGE MASTER LOGS]: SHOW BINARY LOGS SHOW BINLOG EVENTS SHOW MASTER STATUS SHOW SLAVE HOSTS SHOW SLAVE STATUS If the syntax for a given SHOW statement includes a LIKE 'pattern' part, 'pattern' is a string that can contain the SQL `%' and `_' wildcard characters. The pattern is useful for restricting statement output to matching values. Several SHOW statements also accept a WHERE clause that provides more flexibility in specifying which rows to display. See 6 創(chuàng)建用戶數(shù)據(jù)庫 mysql> CREATE DATABASE MYTEST; Query OK, 1 row affected (0.00 sec) 如果想改變MYSQL數(shù)據(jù)文件的默認(rèn)路徑，我們可以關(guān)閉MYSQL實(shí)例，修改配置文件”my.cnf” 或”my.ini”(WINDOWS系統(tǒng))中的datadir對應(yīng)的參數(shù)值，然后把MYSQL默認(rèn)安裝的datadir下的 內(nèi)容拷貝到新的數(shù)據(jù)文件路徑下，啟動MYSQL實(shí)例這樣我們再次創(chuàng)建數(shù)據(jù)庫的時候數(shù)據(jù)文件就放 在新的路徑下了。 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) 創(chuàng)建數(shù)據(jù)庫用戶 可以用兩種方式創(chuàng)建MySQL賬戶: 1、 直接操作MySQL授權(quán)表user(不推薦) 2、 CREATE USER mysql> create user test_cr identified by 'test_cr'; Query OK, 0 rows affected (0.23 sec) 3、直接使用GRANT語句 我們可以不用第二步的CREATE語法來執(zhí)行，而直接GRANT就可以創(chuàng)建用戶。 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。兩個賬戶均為超級用戶賬戶，具有完 全的權(quán)限可以做任何事情。一個賬戶 ('TEST'@'localhost')只用于從本機(jī)連接時。另一個賬戶('TEST'@'%')可用于從其它主機(jī)連 接。請注意TEST的兩個賬戶必須能 從任何主機(jī)以TEST連接。沒有l(wèi)ocalhost賬戶，當(dāng)TEST從本機(jī)連接時，mysql_install_db創(chuàng) 建的localhost的匿名用戶賬戶將占先。 結(jié)果是，TEST將被視為匿名用戶。原因是匿名用戶賬戶的Host列值比'TEST'@'%'賬戶更具體， 這樣在user表排序順序中排在前面。 C:Program FilesMySQLMySQL Server 5.0in>mysql -uTEST -p mytest Enter password: **** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 14 to server version: 5.0.27-community-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. 注意:用戶名和密碼區(qū)分大小寫。 mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | mytest | +------------+ 1 row in set (0.00 sec) mysql> SELECT USER(); +----------------+ | USER() | +----------------+ | TEST@localhost | +----------------+ 1 row in set (0.00 sec) 7 創(chuàng)建數(shù)據(jù)庫對象 1 創(chuàng)建表 在測試MYSQL創(chuàng)建的時候還遇到了一些小問題，這里整理出來供大家參考: 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的自增類型。我們可以利用如下函數(shù)查詢最后一個序列號的 值: mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 3 | +------------------+ 1 row in set (0.06 sec) 本來想在創(chuàng)建表的時候給DT字段一個DEFAULT值(當(dāng)前時間)，但是出現(xiàn)錯誤，嘗試了幾個MYSQL的函數(shù)(如CURDATE()等)都是如此，查閱了一下資料得出結(jié)論: MYSQL的DEFAULT值只能是常量，如果想實(shí)現(xiàn)上述功能只有表中第一個TIMESTAMP類型字段可以做到?？梢允褂盟詣拥赜卯?dāng)前的日期和時間標(biāo)記INSERT或UPDATE的操作。如果你有多個TIMESTAMP列，只有第一個自動更新。自動更新第一個TIMESTAMP列在下列任何條件下發(fā)生: A、列沒有明確地在一個INSERT或LOAD DATA INFILE語句中指定。 B、列沒有明確地在一個UPDATE語句中指定且一些另外的列改變值。(注意一個UPDATE設(shè)置一個列為它已經(jīng)有的值，這將不引起TIMESTAMP列被更新，因?yàn)槿绻阍O(shè)置一個列為它當(dāng)前的值，MySQL為了效率而忽略更改。) C、明確地設(shè)定TIMESTAMP列為NULL或NOW()。 格式:TIMESTAMP[(M)] MySQL可以以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD、YYMMDD格式來顯示TIMESTAMP值，這主要取決于M值，它們分別為14(缺省值)1286。 mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -> MC VARCHAR(60),DT TIMESTAMP); Query OK, 0 rows affected (1.08 sec) mysql> SHOW COLUMNS FROM TEST; +-------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+-------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | MC | varchar(60) | YES | | NULL | | | DT | timestamp | NO | | CURRENT_TIMESTAMP | | +-------+-------------+------+-----+-------------------+----------------+ 3 rows in set (0.17 sec) mysql> insert into test(mc) values('ZhangSan'); Query OK, 1 row affected (0.13 sec) mysql> select * from test; +----+----------+---------------------+ | ID | MC | DT | +----+----------+---------------------+ | 1 | ZhangSan | 2007-05-25 09:54:59 | +----+----------+---------------------+ 1 row in set (0.06 sec) 注意:為表TEST新增加TIMESTAMP類型字段，我們發(fā)現(xiàn)不是表的第一個TIMESTAMP類型的字段 的DEFAULT值不是系統(tǒng)時間。 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) 注意:執(zhí)行UPDATE操作,表中第一個TIMESTAMP字段自動修改為系統(tǒng)時間。 mysql> UPDATE TEST SET MC='WangWu' WHERE ID=3; Query OK, 1 row affected (0.13 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM TEST; +----+----------+---------------------+---------------------+ | ID | MC | DT | RQ | +----+----------+---------------------+---------------------+ | 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 | | 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 | | 3 | WangWu | 2007-05-25 10:04:01 | 2007-05-25 10:03:29 | +----+----------+---------------------+---------------------+ 1 rows in set (0.00 sec) 8 創(chuàng)建索引 MYSQL索引類型: 1、普通索引 這是最基本的索引類型，而且它沒有唯一性之類的限制。普通索引可以通過以下幾種方式創(chuàng) 建: 創(chuàng)建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表); 修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表); 創(chuàng)建表的時候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列 的列表) ); 2、唯一性索引 這種索引和前面的“普通索引”基本相同，但有一個區(qū)別:索引列的所有值都只能出現(xiàn)一次，即必須唯一。唯一性索引可以用以下幾種 方式創(chuàng)建: 創(chuàng)建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表); 修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表); 創(chuàng)建表的時候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) ); 3、主鍵 主鍵是一種唯一性索引，但它必須指定為“PRIMARY KEY”。如果你曾經(jīng)用過AUTO_INCREMENT類型的列，你可能已經(jīng)熟悉主鍵之類的概念了。 主鍵一般在創(chuàng)建表的時候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我們也可以通過修改表 的方式加入主鍵，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每個表只能有一個主鍵。 4、全文索引 MySQL從3.23.23版開始支持全文索引和全文檢索。在MySQL中，全文索引的索引類型為FULLTEXT。全文索引可以在VARCHAR或者TEXT類型 的列上創(chuàng)建。它可以通過CREATE TABLE命令創(chuàng)建，也可以通過ALTER TABLE或CREATE INDEX命令創(chuàng)建。對于大規(guī)模的數(shù)據(jù)集，通過ALTER TABLE(或者CREATE INDEX)命令創(chuàng)建全文索引要比把記錄插入帶有全文索引的空表更快。本文下面的討論不再涉及全文索引，如要了解 更多信息，請參見MySQL documentation。 __________________ www.360oracle.com 專家顧問 msn:[email protected] mail:[email protected] 只看該作者 zhouwf0726 版主 精華貼數(shù) 5 個人空間 2148 技術(shù)積分 8888 (213) 社區(qū)積分 312 (2616) 注冊日期 2006-2-22 論壇徽章:45 #2使用道具 發(fā)表于 2007-6-6 17:51 創(chuàng)建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) 我們可以看出上邊的例子創(chuàng)建索引前后SQL執(zhí)行時間的變化(紅色字體部分)。 刪除索引 mysql> drop index idx_test on test; Query OK, 3 rows affected (1.20 sec) Records: 3 Duplicates: 0 Warnings: 0 9 創(chuàng)建存儲過程 MYSQL存儲過程大致格式如下: CREATE PROCEDURE procedure1 /* name存儲過程名*/ (IN parameter1 INTEGER) /* parameters參數(shù)*/ 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結(jié)束*/ INSERT INTO table1 VALUES (variable1); /* statement SQL語句*/ END /* end of block語句塊結(jié)束*/ DELIMITER， 首先說明一點(diǎn)，在MYSQL的控制臺執(zhí)行創(chuàng)建過程的腳本時，要選擇一個分隔符 給出一個最簡單的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) 給出一個帶有輸入輸出參數(shù)的存儲過程示例: 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) 存儲過程內(nèi)容的查看: 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存儲過程中的一些常用控制結(jié)構(gòu): 選擇結(jié)構(gòu): IF ... THEN ... ELSE ... END IF; CASE ... WHEN ... THEN WHEN ... THEN END CASE; 循環(huán)結(jié)構(gòu): 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 創(chuàng)建函數(shù) 各個數(shù)據(jù)庫包括各種開發(fā)工具都為用戶提供了創(chuàng)建函數(shù)的功能，這里關(guān)于函數(shù)的語法我們不 再做更多解釋了，給出一個例子: 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) 顯示一個定義好的函數(shù)的內(nèi)容: mysql> show create function fn_test; 5 創(chuàng)建視圖 給出一個創(chuàng)建視圖的例子: 注意:下邊例子紅色字體部分為MYSQL數(shù)據(jù)庫取前n條記錄的方法，不同于其他數(shù)據(jù)庫。 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 | …… 20 rows in set (0.06 sec) 和顯示函數(shù)內(nèi)容類似，我們也可以按照如下方法獲得創(chuàng)建視圖的語法內(nèi)容: mysql> show create view v_test; 刪除視圖 mysql> drop view v_test; Query OK, 0 rows affected (0.02 sec) 6 創(chuàng)建觸發(fā)器 給出一個簡單的創(chuàng)建觸發(fā)器的例子: mysql> delimiter // mysql> create trigger tr_test before insert on test for each row -> begin -> insert into test1(id,mc) values(new.id,new.mc); -> end -> // Query OK, 0 rows affected (0.17 sec) mysql> delimiter ; mysql> select count(*) from test1; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> insert into test (id,mc) values(50000,'trigger_to_test1'); Query OK, 1 row affected (0.19 sec) mysql> select id,mc from test1; +-------+------------------+ | id | mc | +-------+------------------+ | 50000 | trigger_to_test1 | +-------+------------------+ 1 row in set (0.00 sec) MYSQL存儲引擎和表類型 MYSQL支持?jǐn)?shù)個存儲引擎作為對不同表的類型的處理器。 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存儲引擎包括處理事務(wù)安全表的引擎和處理非事務(wù)安全表的引擎: MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件(或者內(nèi)存)中。這些技術(shù)中的每一種技術(shù)都使用不同的存儲機(jī)制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術(shù)，你能夠獲得額外的速度或者功能，從而改善你的應(yīng)用的整體功能。 例如，如果你在研究大量的臨時數(shù)據(jù)，你也許需要使用內(nèi)存存儲引擎。內(nèi)存存儲引擎能夠在內(nèi)存中存儲所有的表格數(shù)據(jù)。又或者，你也許需要一個支持事務(wù)處理的數(shù)據(jù)庫(以確保事務(wù)處理不成功時數(shù)據(jù)的回退能力)。 這些不同的技術(shù)以及配套的相關(guān)功能在MySQL中被稱作存儲引擎(也稱作表類型)。MySQL默認(rèn)配置了許多不同的存儲引擎，可以預(yù)先設(shè)置或者在MySQL服務(wù)器中啟用。你可以選擇適用于服務(wù)器、數(shù)據(jù)庫和表格的存儲引擎，以便在選擇如何存儲你的信息、如何檢索這些信息以及你需要你的數(shù)據(jù)結(jié)合什么性能和功能的時候?yàn)槟闾峁┳畲蟮撵`活性。 選擇如何存儲和檢索你的數(shù)據(jù)的這種靈活性是MySQL為什么如此受歡迎的主要原因。其它數(shù)據(jù)庫系統(tǒng)(包括大多數(shù)商業(yè)選擇)僅支持一種類型的數(shù)據(jù)存儲。遺憾的是，其它類型的數(shù)據(jù)庫解決方案采取的“一個尺碼滿足一切需求”的方式意味著你要么就犧牲一些性能，要么你就用幾個小時甚至幾天的時間詳細(xì)調(diào)整你的數(shù)據(jù)庫。使用MySQL，我們僅需要修改我們使用的存儲引擎就可以了。 默認(rèn)存儲引擎 MYSQL的默認(rèn)存儲引擎為:MyISAM，除非我們顯示的指定存儲引擎。如下例: mysql> CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB; Query OK, 0 rows affected (0.83 sec) 為了做出選擇哪一個存儲引擎的決定，我們首先需要考慮每一個存儲引擎提供了哪些不同的核心功能。這種功能使我們能夠把不同的存儲引擎區(qū)別開來。我們一般把這些核心功能分為四類:支持的字段和數(shù)據(jù)類型、鎖定類型、索引和處理。一些引擎具有能過促使你做出決定的獨(dú)特的功能 下邊我們詳細(xì)講述一下MYSQL各個存儲引擎: 1 MyISAM 每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴(kuò)展名指出文件類型。.frm文件存儲表定義。數(shù)據(jù)文件的擴(kuò)展名為.MYD (MYData)。索引文件的擴(kuò)展名是.MYI (MYIndex)。 MyISAM引擎是大多數(shù)MySQL安裝程序的默認(rèn)引擎，起源于早期版本MySQL支持的ISAM引擎。這種引擎提供了最佳的性能和功能的組合，盡管它缺少事務(wù)處理功能(使用InnoDB或者BDB引擎)并且使用表級鎖定。 但是執(zhí)行一下查詢發(fā)現(xiàn)，我在測試的時候使用的兩個測試表在創(chuàng)建的時候沒有指定引擎，但是發(fā)現(xiàn)這兩個表的存儲引擎都為InnoDB。(當(dāng)然我們修改配職文件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文檔，發(fā)現(xiàn)如下解釋: 第15章:存儲引擎和表類型:當(dāng)MySQL被用MySQL配置向?qū)О惭b在Windows平臺上，InnoDB存儲 引擎替代MyISAM存儲引擎作為替代，請參閱2.3.5.1節(jié)，“介紹”。 為了測試MyISAM引擎表級鎖定，我們是用MyISAM引擎創(chuàng)建測試表TEST_ISAM。 測試中打開兩個數(shù)據(jù)庫連接，一個連接執(zhí)行call p_tst_isam();另外一個執(zhí)行單條mysql> insert into test_isam(id,mc) values(1,'1');結(jié)果在第一個連接還沒有執(zhí)行完的時候，第二 個就完畢，沒有發(fā)現(xiàn)MyISAM引擎鎖表，這個問題我們暫時不再繼續(xù)測試下去。測試中發(fā)現(xiàn)一個問 題，MyISAM引擎的表的INSERT速度遠(yuǎn)遠(yuǎn)大于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) 創(chuàng)建存儲過程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 ; 我們在以前的測試?yán)又? InnoDB引擎 INSERT 1000條數(shù)據(jù)花費(fèi)34秒 mysql> call p_test(); Query OK, 1 row affected (34.48 sec) MyISAM引擎INSERT 1000000 條數(shù)據(jù)花費(fèi)時間20多秒: mysql> call p_test_isam(); Query OK, 1 row affected (22.95 sec) 所以我們?nèi)绻谑褂梅鞘挛锾幚淼谋?也就是一些只有單用戶使用的表)的時候可以采用 MyISAM引擎來提高速度，當(dāng)然了INSERT的時候可以利用MYSQL的BULK INSERT功能來出也是能大大 提高性能的，這些我們將在MYSQL數(shù)據(jù)庫優(yōu)化一章中詳細(xì)說明。BULK INSERT的語法: INSERT INTO TEST VALUES(VAL11,VAL12),(VAL21,VAL22)?? 測試完畢，翻看一下MYSQL文檔，的確有下面一段話，和我們的測試結(jié)果吻合: MyISAM管理非事務(wù)表。它提供高速存儲和檢索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默認(rèn)的存儲引擎，除非你配置MySQL默認(rèn)使用另外一個引擎。 2 MERGE引擎 MERGE引擎類型允許你把許多結(jié)構(gòu)相同的表合并為一個表。然后，你可以執(zhí)行查詢，從多個表返回的結(jié)果就像從一個表返回的結(jié)果一樣。每一個合并的表必須有同樣的表定義。 MERGE存儲引擎在下面這種使用場合會最為有用，如果需要把日志紀(jì)錄不停的錄入MySQL數(shù)據(jù)庫，并且每天、每周或者每個月都創(chuàng)建一個單一的表，而且要制作來自多個表的合計查詢，MERGE表這時會非常有效。然而，這項功能有局限性。你只能合并MyISAM表而且必須嚴(yán)格遵守相同的表定義的限制。雖然這看起來好像是一個大問題，但是，如果你使用另外一種表類型(例如InnoDB)，這種合并可能就不需要了。 3 MEMORY(內(nèi)存)存儲引擎 MEMORY(內(nèi)存)存儲引擎(以前稱作HEAP存儲引擎)在內(nèi)存中存儲全部數(shù)據(jù)。一旦MySQL服務(wù)器關(guān)閉，存儲在內(nèi)存中的任何信息都將丟失。然而，單個表的格式將保留，使你能夠創(chuàng)建一個用于存儲信息的臨時表。這樣，每次數(shù)據(jù)庫服務(wù)器啟動時，你不需要重新創(chuàng)新這個表就可以快速地訪問信息。 長期使用MEMORY存儲引擎一般來說不是一個好主意，因?yàn)閿?shù)據(jù)很容易丟失。然而，如果你有足夠的內(nèi)存，使用基于MEMORY的表在大型數(shù)據(jù)集中執(zhí)行復(fù)雜的查詢是一種非常有效的方法，它能夠很大程度的提高性能。 使用MEMORY表的最佳方法是使用一個“select”語句從你原來的基于磁盤的表中選擇一個大型的數(shù)據(jù)集，然后對你需要的具體部分進(jìn)一步分析那些信息。我過去曾經(jīng)使用這個技術(shù)提取了一個月的網(wǎng)絡(luò)記錄數(shù)據(jù)，實(shí)際上就是從使用ARCHIVE存儲引擎制作的表中提取的數(shù)據(jù)，然后對具體的URL、網(wǎng)站和其它重點(diǎn)進(jìn)行查詢。 4 EXAMPLE引擎 EXAMPLE引擎實(shí)際上是一個存儲引擎編程的例子，能夠用作MySQL系統(tǒng)中其它引擎的基礎(chǔ)。 EXAMPLE不支持?jǐn)?shù)據(jù)插入，對于任何形式的數(shù)據(jù)庫訪問來說也不是一個實(shí)用的引擎。然而，EXAMPLE是一個很好的指南，指導(dǎo)你如何開發(fā)自己的存儲引擎，因此對于程序員來說是一個有效的引擎。 5 InnoDB存儲引擎 InnoDB給MySQL提供了具有提交，回滾和崩潰恢復(fù)能力的事務(wù)安全(ACID兼容)存儲引擎。InnoDB鎖定在行級并且也在SELECT語句提供一個Oracle風(fēng)格一致的非鎖定讀。這些特色增加了多用戶部署和性能。沒有在InnoDB中擴(kuò)大鎖定的需要，因?yàn)樵贗nnoDB中行級鎖定適合非常小的空間。InnoDB也支持FOREIGN KEY強(qiáng)制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。 InnoDB是為處理巨大數(shù)據(jù)量時的最大性能設(shè)計。它的CPU效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能匹敵的。 InnoDB存儲引擎被完全與MySQL服務(wù)器整合，InnoDB存儲引擎為在主內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。InnoDB存儲它的表,索引在一個表空間中，表空間可以包含數(shù)個文件(或原始磁盤分區(qū))。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統(tǒng)上。 InnoDB默認(rèn)地被包含在MySQL二進(jìn)制分發(fā)中。Windows Essentials installer使InnoDB成為Windows上MySQL的默認(rèn)表。 InnoDB被用來在眾多需要高性能的大型數(shù)據(jù)庫站點(diǎn)上產(chǎn)生。著名的Internet新聞?wù)军c(diǎn)Slashdot.org運(yùn)行在InnoDB上。Mytrix, Inc.在InnoDB上存儲超過1TB的數(shù)據(jù)，還有一些其它站點(diǎn)在InnoDB上處理平均每秒800次插入/更新的負(fù)荷。 給出一個事物控制的例子: 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存儲引擎相關(guān)的內(nèi)容非常復(fù)雜涉及到事物處理、日志、備份和恢復(fù)、鎖定、多版本、性能、表和索引的結(jié)構(gòu)、磁盤IO等很多方面的知識，我們將在以后使用中逐步研究。 的存儲引擎有個基本的認(rèn)識。我們以上只講述了MYSQL的幾個存儲引擎，使我們能夠?qū)YSQL MYSQL還提供了BDB (BerkeleyDB)存儲引擎、FEDERATED存儲引擎、ARCHIVE存儲引擎、CSV存儲引擎、BLACKHOLE存儲引擎等，這里就不再詳細(xì)說明了。更多詳細(xì)信息參看MYSQL聯(lián)機(jī)文檔第15章:存儲引擎和表類型。 4 MYSQL的SQL語法和常用函數(shù) 1 數(shù)據(jù)類型 MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。這些類型包括嚴(yán)格數(shù)值數(shù)據(jù)類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數(shù)值數(shù)據(jù)類型(FLOAT、REAL和DOUBLE PRECISION)。關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞。 數(shù)據(jù)類型 描述 字節(jié) 推薦使用 SMALLINT 整數(shù)，從-32000到 +32000范圍 2 存儲相對比較小的整數(shù)。比如: 年紀(jì)，數(shù)量 INT 整數(shù)，從-2000000000 到 +2000000000 范圍 4 存儲中等整數(shù)例如: 距離 BIGINT 不能用SMALLINT 或 INT描述的超大整數(shù)。 8 存儲超大的整數(shù)例如: 科學(xué)/數(shù)學(xué)數(shù)據(jù) FLOAT 單精度浮點(diǎn)型數(shù)據(jù) 4 存儲小數(shù)數(shù)據(jù)例如:測量，溫度 DOUBLE 雙精度浮點(diǎn)型數(shù)據(jù) 8 需要雙精度存儲的小數(shù)數(shù)據(jù)例如:科學(xué)數(shù)據(jù) DECIMAL 用戶自定義精度的浮點(diǎn)型數(shù)據(jù) 變量;取決于精度與長度 以特別高的精度存儲小數(shù)數(shù)據(jù)。例如:貨幣數(shù)額，科學(xué)數(shù)據(jù) CHAR 固定長度的字符串 特定字符串長度(高達(dá)255字符) 存儲通常包含預(yù)定義字符串的變量例如: 定期航線，國家或郵編 VARCHAR 具有最大限制的可變長度的字符串 變量; 1 + 實(shí)際字符串長度 (高達(dá) 255 字符) 存儲不同長度的字符串值(高達(dá)一個特定的最大限度).例如:名字，密碼，短文標(biāo)簽 TEXT 沒有最大長度限制的可變長度的字符串 Variable; 2 +聽 actual string length 存儲大型文本數(shù)據(jù)例如: 新聞故事，產(chǎn)品描述 BLOB 二進(jìn)制字符串 變量;2 + 實(shí)際字符串長度 存儲二進(jìn)制數(shù)據(jù)例如:圖片，附件，二進(jìn)制文檔 DATE 以 yyyy-mm-dd格式的日期 3 存儲日期例如:生日，產(chǎn)品滿期 TIME 以 hh:mm:ss格式的時間 3 存儲時間或時間間隔例如:報警聲，兩時間之間的間隔，任務(wù)開始/結(jié)束時間 DATETIME 以yyyy-mm-ddhh:mm:ss格式結(jié)合日期和時間 8 存儲包含日期和時間的數(shù)據(jù)例如:提醒的人，事件 TIMESTAMP 以yyyy-mm-ddhh:mm:ss格式結(jié)合日期和時間 4 記錄即時時間例如:事件提醒器，“最后進(jìn)入”的時間標(biāo)記 YEAR 以 yyyy格式的年份 1 存儲年份例如:畢業(yè)年，出生年 ENUM 一組數(shù)據(jù)，用戶可從中選擇其中一個 1或 2個字節(jié) 存儲字符屬性，只能從中選擇之一例如:布爾量選擇，如性別 SET 一組數(shù)據(jù)，用戶可從中選擇其中0，1或更多。 從1到8字節(jié);取決于設(shè)置的大小 存儲字符屬性，可從中選擇多個字符的聯(lián)合。例如:多選項選擇，比如業(yè)余愛好和興趣。 2 字符串函數(shù) CHARSET(str) //返回字串字符集 CONCAT (string2 [,? ]) //連接字串，注意不要沿用ORACLE習(xí)慣 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中出現(xiàn)的位置,不存 在返回0 LCASE (string2 ) //轉(zhuǎn)換成小寫 LEFT (string2 ,length ) //從string2中的左起取length個字符 LENGTH (string ) //string長度 LOAD_FILE (file_name ) //從文件讀取內(nèi)容 LOCATE (substring , string [,start_position ] ) //同INSTR,但可指定開始位置 LPAD (string2 ,length ,pad ) //重復(fù)用pad加在string開頭,直到字串長度 為length LTRIM (string2 ) //去除前端空格 REPEAT (string2 ,count ) //重復(fù)count次 REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替換search_str RPAD (string2 ,length ,pad) //在str后用pad補(bǔ)充,直到長度length RTRIM (string2 ) //去除后端空格 STRCMP (string1 ,string2 ) //逐字符比較兩字串大小, SUBSTRING (str , position [,length ]) //從str的position開始,取length個字符 注:mysql中處理字符串時，默認(rèn)第一個字符下標(biāo)為1，即參數(shù)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 ) //轉(zhuǎn)換成大寫 RIGHT(string2,length) //取string2最后length個字符 SPACE(count) //生成count個空格 3 數(shù)學(xué)函數(shù) ABS (number2 ) //絕對值 BIN (decimal_number ) //十進(jìn)制轉(zhuǎn)二進(jìn)制 CEILING (number2 ) //向上取整 CONV(number2,from_base,to_base) //進(jìn)制轉(zhuǎn)換 FLOOR (number2 ) //向下取整 FORMAT (number,decimal_places ) //保留小數(shù)位數(shù) HEX (DecimalNumber ) //轉(zhuǎn)十六進(jìn)制 HEX()中可傳入字符串，則返回其ASC-11碼，如HEX(’DEF’)返回4142143 注: 也可以傳入十進(jìn)制整數(shù)，返回其十六進(jìn)制編碼，如HEX(25)返回19 LEAST (number , number2 [,..]) //求最小值 MOD (numerator ,denominator ) //求余 POWER (number ,power ) //求指數(shù) RAND([seed]) //隨機(jī)數(shù) ROUND (number [,decimals ]) //四舍五入,decimals為小數(shù)位數(shù)] 注:返回類型并非均為整數(shù)，如: (1)默認(rèn)變?yōu)檎沃?nbsp; 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)可以設(shè)定小數(shù)位數(shù)，返回浮點(diǎn)型數(shù)據(jù) mysql> select round(1.567,2); +—————-+ | round(1.567,2) | +—————-+ | 1.57 | +—————-+ 1 row in set (0.00 sec) SIGN (number2 ) //返回符號,正負(fù)或0 SQRT(number2) //開平方 4 日期函數(shù) ADDTIME (date2 ,time_interval ) //將time_interval加到date2 CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //轉(zhuǎn)換時區(qū) CURRENT_DATE ( ) //當(dāng)前日期 CURRENT_TIME ( ) //當(dāng)前時間 CURRENT_TIMESTAMP ( ) //當(dāng)前時間戳 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 ( ) //當(dāng)前時間 SEC_TO_TIME (seconds ) //秒數(shù)轉(zhuǎn)成時間 STR_TO_DATE (string ,format ) //字串轉(zhuǎn)成時間, format格式 顯示 TIMEDIFF (datetime1 ,datetime2 ) //兩個時間差 TIME_TO_SEC (time ) //時間轉(zhuǎn)秒 數(shù)] WEEK (date_time [,start_of_week ]) //第幾周 YEAR (datetime ) //年份 DAYOFMONTH(datetime) //月的第幾天 HOUR(datetime) //小時 LAST_DAY(date) //date的 月的最后日期 MICROSECOND(datetime) //微秒 MONTH(datetime) //月 MINUTE(datetime) //分 5 控制結(jié)構(gòu) 1.區(qū)塊定義，常用 begin ?? end; 也可以給區(qū)塊起別名，如: lable:begin ???.. end lable; 可以用leave lable;跳出區(qū)塊，執(zhí)行區(qū)塊以后的代碼 2.條件語句 if 條件 then statement else statement end if; 3.循環(huán)語句 (1).while循環(huán) [label:] WHILE expression DO statements END WHILE [label] ; (2).loop循環(huán) [label:] LOOP statements END LOOP [label]; (3).repeat until循環(huán) [label:] REPEAT statements UNTIL expression END REPEAT [label] ; ......待續(xù)...... __________________ www.360oracle.com 專家顧問 msn:[email protected] mail:[email protected] 只看該作者 zhouwf0726 版主 精華貼數(shù) 5 個人空間 2148 技術(shù)積分 8888 (213) 社區(qū)積分 312 (2616) 注冊日期 2006-2-22 論壇徽章:45 #3使用道具 發(fā)表于 2007-6-8 15:05 今天新加一些內(nèi)容 5 MYSQL數(shù)據(jù)庫管理 我們在第二章已經(jīng)簡單介紹了一些MYSQL數(shù)據(jù)庫的基本操作，這一章我們將針對MYSQL數(shù)據(jù)庫 管理員詳細(xì)介紹下MYSQL數(shù)據(jù)庫的常用管理內(nèi)容。 1 MYSQL界面管理工具 MYSQL的管理工具很多，我自己從網(wǎng)上下載了一個: mysql-gui-tools-noinstall-5.0-r12-win32，是一個不用安裝的管理軟件，包含四個基本 工具: MySQLAdministrator MySQLMigrationTool MySQLQueryBrowser MySQLSystemTrayMonitor 這些工具的具體使用都很簡單，操作比較靈活，這里就不對這些工具的功能做詳細(xì)介紹了， 如果有興趣大家可以自己下載下來，多使用幾次就熟悉了。 2 MYSQL命令行管理工具 a、首先介紹幾個MYSQL命令行工具: mysqld_safe、mysql.server和mysqld_multi是服務(wù)器啟動腳本 注意:在Windows中不使用服務(wù)器啟動腳本，我們可以使用WINDOWS命令來 啟動MYSQL服務(wù):net start mysql 停止MYSQL服務(wù): net stop mysql 或者: C:Program FilesMySQLMySQL Server 5.0in>mysqladmin -uroot -p shutdown Enter password: ****** mysqld是MySQL服務(wù)器 下面的表格列舉了幾種WINDOS平臺支持的MYSQL服務(wù)器: mysqld- debug 已經(jīng)編譯過，可以進(jìn)行完全調(diào)試和自動內(nèi)存分配檢查，用于表InnoDB 和BDB。 mysqld 優(yōu)化的二進(jìn)制，支持InnoDB。 mysqld-nt 優(yōu)化的二進(jìn)制，支持Windows NT，2000 XP，有命名管道。 mysqld-max 優(yōu)化的二進(jìn)制，支持InnoDB和BDB表。 mysqld-max-nt 同mysqld-max，但是已經(jīng)編譯過，支持命名管道。 WINDOWS下執(zhí)行二進(jìn)制安裝后，在WINDOWS服務(wù)中默認(rèn)的就是使用的mysqld-nt服務(wù)器。 這個命令的使用我們將在后邊的MYSQL服務(wù)器中詳細(xì)講解。 mysql_install_db初始化數(shù)據(jù)目錄和初始數(shù)據(jù)庫 mysql是一個命令行客戶程序，用于交互式或以批處理模式執(zhí)行SQL語句 這個命令我們在前邊的例子中已經(jīng)有很多應(yīng)用了，這里就不多講述了。我們給出一個例子: C:Program FilesMySQLMySQL Server 5.0in>mysql -u root -p -e "SELECT * FROM TEST limit 5;select * from test where id=1000" mytest Enter password: ****** +------+------+---------------------+---------------------+ | ID | MC | DT | RQ | +------+------+---------------------+---------------------+ | 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 999 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 998 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 997 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 996 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +------+------+---------------------+---------------------+ +------+------+---------------------+---------------------+ | ID | MC | DT | RQ | +------+------+---------------------+---------------------+ | 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +------+------+---------------------+---------------------+ mysqladmin是用于管理功能的客戶程序 mysqlcheck執(zhí)行表維護(hù)操作 C:Program FilesMySQLMySQL Server 5.0in>mysqlcheck -uroot -p mytest Enter password: ****** mytest.aaa OK mytest.customer OK mytest.mytable OK mytest.sys_tests OK mytest.test OK mytest.test1 OK mytest.test_isam OK mysqldump數(shù)據(jù)庫備份 mysqlhotcopy數(shù)據(jù)庫備份 mysqlimport導(dǎo)入數(shù)據(jù)文件 這幾個命令工具我們將在后邊的備份恢復(fù)中詳細(xì)講解。 mysqlshow顯示信息數(shù)據(jù)庫和表的相關(guān)信息 C:Program FilesMySQLMySQL Server 5.0in>mysqlshow -uroot -p mytest Enter password: ****** Database: mytest +-----------+ | Tables | +-----------+ | aa | | aaa | | customer | | mytable | | sys_tests | | test | | test1 | | test_isam | +-----------+ 以下是幾個可以獨(dú)立于MYSQL服務(wù)器(客戶端可以執(zhí)行)進(jìn)行操作的工作 myisamchk執(zhí)行表維護(hù)操作 myisampack產(chǎn)生壓縮、只讀的表 mysqlbinlog是處理二進(jìn)制日志文件的實(shí)用工具 perror顯示錯誤代碼的含義 我們這里對MYSQL的命令做了個簡單介紹，如果要看更多內(nèi)容，幾乎所有MYSQL命令我們都可以用—help來獲得幫助，另外我們可以從MYSQL聯(lián)機(jī)文檔中獲得更多信息。 b、使用選項文件 MySQL程序可以從選項文件(有時也稱為配置文件)讀取啟動選項。選項文件提供了一種很方便的方式來指定常用的選項，因此不需要每次運(yùn)行程序時從命令行輸入。 下面的程序支持選項文件:myisamchk、myisampack、mysql、mysql.server、mysqladmin、mysqlbinlog、mysqlcc、mysqlcheck、mysqld_safe、mysqldump、mysqld、mysqlhotcopy、mysqlimport和mysqlshow。我們在前邊講述創(chuàng)建用戶數(shù)據(jù)庫的時候已經(jīng)提到過修改MYSQL的配置文件來修改數(shù)據(jù)文件的路徑:配置文件”my.cnf”或”my.ini”(WINDOWS系統(tǒng)) 注釋:在Unix平臺上，MySQL忽略人人可寫的配置文件。這是故意的，是一個安全措施。這 句話是在查閱資料時看到的，未加驗(yàn)證。 c、用環(huán)境變量指定選項 C:Program FilesMySQLMySQL Server 5.0in>set user=TEST C:Program FilesMySQLMySQL Server 5.0in>mysql -p mytest Enter password: **** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 5 to server version: 5.0.27-community-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> select user(); +----------------+ | user() | +----------------+ | TEST@localhost | +----------------+ 1 row in set (0.06 sec) d、使用選項設(shè)置程序變量 shell> mysql --max_allowed_packet= 2097152 shell> mysql --max_allowed_packet=2M 3 MYSQL實(shí)例管理器mysqlmanager 該工具軟件需要單獨(dú)下載: 該軟件官方網(wǎng)站: 關(guān)于MYSQL實(shí)例管理器我們暫時不做太多解釋。 4 MySQL服務(wù)器 WINDOWS下執(zhí)行二進(jìn)制安裝后WINDOWS服務(wù)中默認(rèn)的調(diào)用的是mysqld-nt服務(wù)器，我們可以在 WINDOWS的MySql服務(wù)的屬性中看到: "C:Program FilesMySQLMySQL Server 5.0inmysqld-nt" --defaults-file ="C:Program FilesMySQLMySQL Server 5.0my.ini" MySQL 我們也可以不用WINDOWS的默認(rèn)MYSQL服務(wù)器，而啟用mysqld服務(wù)器來 啟動MYSQL: C:Program FilesMySQLMySQL Server 5.0in>mysqld --console 070608 14:12:04 [Warning] Changed limits: max_open_files: 2048 max_connections: 800 table_cache: 619 070608 14:12:05 InnoDB: Started; log sequence number 0 956199 070608 14:12:05 [Note] mysqld: ready for connections. Version: '5.0.27-community' socket: '' port: 3306 MySQL Community Edition (GP L) 如果省略--console選項，服務(wù)器向數(shù)據(jù)目錄(默認(rèn)為C:Program FilesMySQL MySQL Server 5.1data)中的錯誤日志寫入診斷輸出。錯誤日志文件的擴(kuò)展名為.err。 C:Program FilesMySQLMySQL Server 5.0in>mysqld --defaults-file = "C:Program FilesMySQLMySQL Server 5.0my.ini" 070608 9:32:34 [Warning] Changed limits: max_open_files: 2048 max_connections: 800 table_cache: 619 如果我們沒有執(zhí)行環(huán)境變量和程序變量，mysqld和mysqld-nt都是從配置文件中的[mysqld] 和[server]組讀取選項。 我們可以執(zhí)行以下命令來獲得MYSQL數(shù)據(jù)庫服務(wù)器的配置參數(shù)列表: C:Program FilesMySQLMySQL Server 5.0in> mysqld --verbose –help 在MYSQL數(shù)據(jù)庫中查看各個變量設(shè)置: mysql> SHOW VARIABLES; mysql> SHOW VARIABLES LIKE 'key_buffer_size'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | key_buffer_size | 32505856 | +-----------------+----------+ 1 row in set (0.06 sec) 我們可以通過以下命令后的運(yùn)行的數(shù)據(jù)庫服務(wù)器的統(tǒng)計和狀態(tài)指標(biāo): mysql> SHOW STATUS; +-----------------------------------+-----------+ | Variable_name | Value | +-----------------------------------+-----------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 144 | | Bytes_sent | 13078 | mysql> show variables like 'sort_buffer%'; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | sort_buffer_size | 10485760 | +------------------+----------+ 1 row in set (0.09 sec) mysql> SET sort_buffer_size = 5 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'sort_buffer%'; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | sort_buffer_size | 5242880 | +------------------+---------+ 1 row in set (0.00 sec) MYSQL的配置文件可以設(shè)置的項目很多，我們這里不做太多解釋，在以后的優(yōu)化等章節(jié)中我 們將會詳細(xì)講述一些我們常用的配置參數(shù)及調(diào)整。 停止MYSQL: C:Program FilesMySQLMySQL Server 5.0in>mysqladmin -uroot -p shutdown Enter password: ****** 發(fā)表于 2007-6-9 22:36 MYSQL數(shù)據(jù)庫管理部分新增加四節(jié) 5 MYSQL高速緩存管理 查詢緩存存儲SELECT查詢的文本以及發(fā)送給客戶端的相應(yīng)結(jié)果。如果隨后收到一個相同的查詢，服務(wù)器從查詢緩存中重新得到查詢結(jié)果，而不再需要解析和執(zhí)行查詢。通過在configure中使用--without-query-cache選項，可以從服務(wù)器中徹底去除查詢緩存能力。 查詢解析之前進(jìn)行比較，因此下面的兩個查詢被查詢緩存認(rèn)為是不相同的: SELECT * FROM tbl_name Select * from tbl_name 查詢必須是完全相同的(逐字節(jié)相同)才能夠被認(rèn)為是相同的。另外，同樣的查詢字符串由于其它原因可能認(rèn)為是不同的。使用不同的數(shù)據(jù)庫、不同的協(xié)議版本或者不同 默認(rèn)字符集的查詢被認(rèn)為是不同的查詢并且分別進(jìn)行緩存。 如果一個表被更改了，那么使用那個表的所有緩沖查詢將不再有效，并且從緩沖區(qū)中移出。這包括那些映射到改變了的表的使用MERGE表的查詢。一個表可以被許多類型的語句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。 查詢告訴緩存的大小 mysql> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | query_cache_size | 23068672 | +------------------+----------+ 1 row in set (0.01 sec) 修改告訴緩存的大小 mysql> SET GLOBAL query_cache_size = 31457280; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | query_cache_size | 31457280 | +------------------+----------+ 1 row in set (0.00 sec) 查看高速緩存的類型 mysql> SHOW VARIABLES LIKE 'query_cache_type'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_type | ON | +------------------+-------+ 1 row in set (0.00 sec) 高速緩存三種類型介紹 A、0或OFF將阻止緩存或查詢緩存結(jié)果。 B、1或ON將允許緩存，以SELECT SQL_NO_CACHE開始的查詢語句除外。 C、2或DEMAND，僅對以SELECT SQL_CACHE開始的那些查詢語句啟用緩存。 設(shè)置query_cache_type變量的GLOBAL值將決定更改后所有連接的緩存行為。具體SESSION可 以通過設(shè)置query_cache_type變量的會話值控制它們本身連接的緩存行為。例如，一個SESSION 可以禁用自己的查詢緩存，方法如下: mysql> SET SESSION query_cache_type = OFF; Query OK, 0 rows affected (0.00 sec) query_cache_limit 如果我們要控制可以被緩存的具體查詢結(jié)果的最大值，應(yīng)設(shè)置query_cache_limit變量。 默認(rèn)值是1MB。 mysql> SHOW VARIABLES LIKE 'query_cache_limit'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | query_cache_limit | 1048576 | +-------------------+---------+ 1 row in set (0.00 sec) query_cache_min_res_unit 當(dāng)一個查詢結(jié)果(返回給客戶端的數(shù)據(jù))從查詢緩沖中提取期間，它在查詢緩存中排序。因此，數(shù)據(jù)通常不在大的數(shù)據(jù)塊中處理。查詢緩存根據(jù)數(shù)據(jù)排序要求分配數(shù)據(jù)塊，因此，當(dāng)一個數(shù)據(jù)塊用完后分配一個新的數(shù)據(jù)塊。因?yàn)閮?nèi)存分配操作是昂貴的(費(fèi)時的)，所以通過query_cache_min_res_unit系統(tǒng)變量給查詢緩存分配最小值。當(dāng)查詢執(zhí)行時，最新的結(jié)果數(shù)據(jù)塊根據(jù)實(shí)際數(shù)據(jù)大小來確定，因此可以釋放不使用的內(nèi)存。根據(jù)服務(wù)器執(zhí)行查詢的類型，我們會發(fā)現(xiàn)調(diào)整query_cache_min_res_unit變量的值是有用的:query_cache_min_res_unit默認(rèn)值是4KB。這應(yīng)該適合大部分情況。 如果有大量返回小結(jié)果數(shù)據(jù)的查詢，默認(rèn)數(shù)據(jù)塊大小可能會導(dǎo)致內(nèi)存碎片，顯示為大量空閑內(nèi)存塊。由于缺少內(nèi)存，內(nèi)存碎片會強(qiáng)制查詢緩存從緩存內(nèi)存中修整(刪除)查詢。這時，應(yīng)該減少query_cache_min_res_unit變量的值?？臻e塊和由于修整而移出的查詢的數(shù)量通過 Qcache_free_blocks和Qcache_lowmem_prunes變量的值給出。 如果大量查詢返回大結(jié)果(檢查 Qcache_total_blocks和Qcache_queries_in_cache狀態(tài)變量)，可以通過增加query_cache_min_res_unit變量的值來提高性能。但是，注意不要使它變得太大(參見前面的條目)。 查詢高速緩沖狀態(tài)和維護(hù) 可以使用FLUSH QUERY CACHE語句來清理查詢緩存碎片以提高內(nèi)存使用性能。該語句不從緩存中移出任何查詢。 RESET QUERY CACHE語句從查詢緩存中移出所有查詢。FLUSH TABLES語句也執(zhí)行同樣的工作。 為了監(jiān)視查詢緩存性能，使用SHOW STATUS查看緩存狀態(tài)變量: mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 31448464 | | Qcache_hits | 0 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 490 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ 8 rows in set (0.52 sec) SELECT查詢的總數(shù)量等價于: Com_select + Qcache_hits + queries with errors found by parser Com_select的值等價于: Qcache_inserts + Qcache_not_cached + queries with errors found during columns/rights check 查詢緩存使用長度可變塊，因此Qcache_total_blocks和Qcache_free_blocks可以顯示查詢緩存內(nèi)存碎片。執(zhí)行FLUSH QUERY CACHE后，只保留一個空閑塊。 每個緩存查詢至少需要兩個塊(一個塊用于查詢文本，一個或多個塊用于查詢結(jié)果)。并且，每一個查詢使用的每個表需要一個塊。但是，如果兩個或多個查詢使用相同的表，僅需要分配一個塊。 Qcache_lowmem_prunes狀態(tài)變量提供的信息能夠幫助你你調(diào)整查詢緩存的大小。它計算為了緩存新的查詢而從查詢緩沖區(qū)中移出到自由內(nèi)存中的查詢的數(shù)目。查詢緩沖區(qū)使用最近最少使用(LRU)策略來確定哪些查詢從緩沖區(qū)中移出。 6 MYSQL數(shù)據(jù)庫用戶管理 1 創(chuàng)建數(shù)據(jù)庫用戶 mysql> create user 'zhouwf'@'localhost' identified by 'zhouwf'; Query OK, 0 rows affected (0.00 sec) mysql> create user 'zhouwf'@'%' identified by 'zhouwf'; Query OK, 0 rows affected (0.00 sec) 2 給用戶授權(quán) mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON mytest.* TO 'zhouwf'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON mytest.* TO 'zhouwf'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 3 刪除用戶 mysql> drop user 'zhouwf'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> drop user 'zhouwf'@'%'; Query OK, 0 rows affected (0.00 sec) 4 修改用戶密碼 A、利用mysqladmin工具 C:Program FilesMySQLMySQL Server 5.0in>mysqladmin -uTEST -p password TTTT Enter password: **** C:Program FilesMySQLMySQL Server 5.0in>mysql -uTEST -pTTTT mytest Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 68 to server version: 5.0.27-community-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. B、使用MYSQL數(shù)據(jù)庫命令SET: mysql> SET PASSWORD FOR 'TEST'@'LOCALHOST' = PASSWORD('TEST'); Query OK, 0 rows affected (0.00 sec) C、使用MYSQL數(shù)據(jù)庫命令GRANT IDENTIFIED BY: mysql> grant usage on mytest.* to 'TEST'@'LOCALHOST' identified by 'TTTT'; Query OK, 0 rows affected (0.00 sec) 當(dāng)然我們還可以利用REPLACE命令修改mysql.user表的內(nèi)容的方式來修改密碼，我們不建議 這么做，這里也就不給出例子了。 7 MYSQL數(shù)據(jù)庫權(quán)限管理 MySQL存取控制包含2個階段: 階段1:服務(wù)器檢查是否允許你連接。 階段2:假定你能連接，服務(wù)器檢查你發(fā)出的每個請求?？茨闶欠裼凶銐虻臋?quán)限 實(shí)施它。例如，如果你從數(shù)據(jù)庫表中選擇(select)行或從數(shù)據(jù)庫刪除表，服務(wù)器確定你對表 有SELECT權(quán)限或?qū)?shù)據(jù)庫有DROP權(quán)限。 在這里我們不對MYSQL的各個權(quán)限做詳細(xì)的解釋，我們在使用的時候可以從MYSQL聯(lián)機(jī)文檔中 獲得更多的詳細(xì)信息。 8 MYSQL數(shù)據(jù)庫備份與恢復(fù) mysqlhotcopy 其命令格式如下(摘自MYSQL文檔): shell> mysqlhotcopy db_name /path/to/some/dir 只要服務(wù)器不再進(jìn)行更新，還可以只復(fù)制所有表文件(*.frm、*.MYD和*.MYI文件)。mysqlhotcopy腳本使用該方法。(但請注意如果數(shù)據(jù)庫包含InnoDB表，這些方法不工作。InnoDB不將表的內(nèi)容保存到數(shù)據(jù)庫目錄中，mysqlhotcopy只適合MyISAM表)。 mysqldump mysqldump提供在線邏輯備份，我們在備份的時候使用single-transaction參數(shù)的話，MYSQL為我們提供一致性地讀，并且保證mysqldump所看見的數(shù)據(jù)不會更改。(其它客戶端對InnoDB表進(jìn)行的更改不會被mysqldump進(jìn)程看見)該參數(shù)自動關(guān)閉--lock-tables。如果我們還有其它類型的表，我們必須假定在備份過程中它們不會更改。例如，對于mysql數(shù)據(jù)庫中的MyISAM表，我們必須假定在備份過程中沒有對MySQL賬戶進(jìn)行管理更改。mysqldump命令產(chǎn)生的.sql文件包含一系列SQL INSERT語句，可以用來重載轉(zhuǎn)儲的表。 C:Program FilesMySQLMySQL Server 5.0in>mysqldump -uroot -p mytest >mytest.sql Enter password: ****** 生成的mytest.sql文件的示例內(nèi)容如下: -- MySQL dump 10.10 -- -- Host: localhost Database: mytest -- ------------------------------------------------------ -- Server version 5.0.27-community-nt /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `aaa` -- DROP TABLE IF EXISTS `aaa`; CREATE TABLE `aaa` ( `id` decimal(18,2) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `aaa` -- LOCK TABLES `aaa` WRITE; /*!40000 ALTER TABLE `aaa` DISABLE KEYS */; INSERT INTO `aaa` VALUES ('3000.00'); /*!40000 ALTER TABLE `aaa` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customer` -- DROP TABLE IF EXISTS `customer`; CREATE TABLE `customer` ( `A` int(11) default NULL, `B` char(20) default NULL, KEY `A` (`A`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ?? LOCK TABLES `test_isam` WRITE; /*!40000 ALTER TABLE `test_isam` DISABLE KEYS */; INSERT INTO `test_isam` VALUES (999013,'test'),(999014,'test'),(999015,'test'),(999016,'test'),(999017,'test'),(99 9018,'test'),(999019,'test'),(999020,'test') ,(999265,'test'),(999266,'test') ?? mysqldump增量備份 要想進(jìn)行增量備份，我們需要保存增量更改。應(yīng)使用--log-bin選項啟動MySQL服務(wù)器，以便更新數(shù)據(jù)時將這些更改保存到文件中。該選項啟用二進(jìn)制日志，因此服務(wù)器寫將每個更新數(shù)據(jù)的SQL語句寫入MySQL二進(jìn)制日志。 C:Program FilesMySQLMySQL Server 5.0in>mysqld --defaults-file="C:Program FilesMySQLMySQL Server 5.0my.ini" --log-bin 070609 17:58:42 [Warning] Changed limits: max_open_files: 2048 max_connections: 800 table_cache: 619 我們可以在basedir找到以下MySQL二進(jìn)制日志文件: mochasof-8ed6b1-bin.000001 mochasof-8ed6b1-bin.000002 mochasof-8ed6b1-bin.000003 每次重啟，MySQL服務(wù)器用序列中的下一個編號創(chuàng)建一個新的二進(jìn)制日志文件。當(dāng)服務(wù)器運(yùn) 行時，我們還可以通過執(zhí)行FLUSH LOGS語句或mysqladmin flush-logs命令，告訴服務(wù)器關(guān)閉當(dāng) 前的二進(jìn)制日志文件并創(chuàng)建一個新文件。 C:Program FilesMySQLMySQL Server 5.0in>mysqladmin -uroot -p flush-logs Enter password: ****** 該命令類似于ORACLE的alter system switch logfile命令(日志切換)。mysqldump也有一個 選項來清空日志。數(shù)據(jù)目錄中的.index文件包含該目錄下所有MySQL二進(jìn)制日志的清單。該文件 用于復(fù)制。 恢復(fù)時MySQL二進(jìn)制日志很重要，因?yàn)樗鼈兪窃隽總浞荨Ｈ绻M(jìn)行完全備份時確保清空了日 志，則后面創(chuàng)建的二進(jìn)制日志文件包含了備份后的所有數(shù)據(jù)更改。讓我們稍稍修改前面的 mysqldump命令，讓它在完全備份時能夠清空 MySQL二進(jìn)制日志，以便轉(zhuǎn)儲文件包含包含新的當(dāng) 前的二進(jìn)制日志，我們在basedir下看到MYSQL產(chǎn)生的最新的日志文件 mochasof-8ed6b1-bin.000004: C:Program FilesMySQLMySQL Server 5.0in>mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases> backup_all.sql Enter password: ****** Backup.sql文件包含下列行: -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mochasof-8ed6b1-bin.000004', MASTER_LOG_POS=98; -- -- Current Database: `mysql` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `mysql`; --master-data參數(shù): 這個參數(shù)把二進(jìn)制日志的位置和文件名寫入備份文件中，如果該值等于1，在備份文件中將 會打印一個CHANGE MASTER命令;如果該值等于2，在備份文件中將會給出一個包括CHANGE MASTER 的注釋，這個操作將會打開--lock-all-tables選項開關(guān)，除非和我們前邊提到過的 --single-transaction參數(shù)選項一起結(jié)合使用，這時--single-transaction優(yōu)先級高于 master-data，在備份期間人戶引起日志改變的操作都自動將--lock-tables關(guān)閉。以下是從MYSQL 的help中獲得的信息。 --master-data[=#] This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn --lock-all-tables on, unless --single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump - don't forget to read about --single-transaction below). In all cases any action on logs will happen at the exact moment of the dump.Option automatically turns --lock-tables off. 因?yàn)閙ysqldump命令可以執(zhí)行完全備份，這些行表示兩件事情: A、backup.sql文件包含所有寫入mochasof-8ed6b1-bin.000004二進(jìn)制日志文 件或最新的文件之前的更改。 B、備份后所記錄的所有數(shù)據(jù)更改不出現(xiàn)在backup.sql中，但出現(xiàn)在 mochasof-8ed6b1-bin.000004二進(jìn)制日志文件或最新的文件中。 接下來我們可以清空日志開始新的二進(jìn)制日志文件來創(chuàng)建增量備份。例如，執(zhí)行mysqladmin flush-logs命令創(chuàng)建mochasof-8ed6b1-bin.000005。完全備份之后所有更改為文件 mochasof-8ed6b1-bin.000004。該增量備份很重要，因此最好將它復(fù)制到安全的地方。(例如， 備份到磁帶或DVD上，或復(fù)制到另一臺機(jī)器上)。以后我們執(zhí)行另一個mysqladmin flush-logs命 令創(chuàng)建mochasof-8ed6b1-bin.000006，mochasof-8ed6b1-bin.000005也應(yīng)復(fù)制到某個安全的地 方。 MySQL二進(jìn)制日志占據(jù)硬盤空間。要想釋放空間，應(yīng)隨時清空。操作方法是刪掉不再使用的 二進(jìn)制日志，例如進(jìn)行完全備份時: C:Program FilesMySQLMySQL Server 5.0in>mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --delete-master-logs mytest > mytest_new.sql Enter password: ****** 注釋:如果你的服務(wù)器為復(fù)制主服務(wù)器，用mysqldump --delete-master-logs刪掉MySQL二 進(jìn)制日志很危險，因?yàn)閺姆?wù)器可能還沒有完全處理該二進(jìn)制日志的內(nèi)容。 PURGE MASTER LOGS語句的描述中解釋了為什么在刪掉MySQL二進(jìn)制日志之前應(yīng)進(jìn)行確認(rèn)。 一個簡單的恢復(fù)測試 做全庫備份: C:Program FilesMySQLMySQL Server 5.0in>mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 mytest > backup_mytest.sql Enter password: ****** 備份后創(chuàng)建新的測試表并INSERT測試數(shù)據(jù): C:Program FilesMySQLMySQL Server 5.0in>mysql -uTEST -p mytest Enter password: **** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 10 to server version: 5.0.27-community-log Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> SHOW TABLES; +------------------+ | Tables_in_mytest | +------------------+ | aaa | | customer | | mytable | | sys_tests | | test | | test1 | | test_isam | +------------------+ 7 rows in set (0.00 sec) mysql> CREATE TABLE NEW_TABLE(ID VARCHAR(20),MC VARCHAR(60)); Query OK, 0 rows affected (0.28 sec) mysql> INSERT INTO NEW_TABLE VALUES('1','111'); Query OK, 1 row affected (0.23 sec) mysql> SELECT * FROM NEW_TABLE; +------+------+ | ID | MC | +------+------+ | 1 | 111 | +------+------+ 1 row in set (0.00 sec) mysql> FLUSH LOGS; Query OK, 0 rows affected (0.53 sec) 刪除所有TABLE后: mysql> SHOW TABLES; Empty set (0.00 sec) 我們從全庫備份文件執(zhí)行恢復(fù)操作: C:Program FilesMySQLMySQL Server 5.0in>mysql -uroot -p mytest < backup_mytest.sql Enter password: ****** C:Program FilesMySQLMySQL Server 5.0in>mysql -uTEST -p mytest Enter password: **** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 19 to server version: 5.0.27-community-log Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> SHOW TABLES; +------------------+ | Tables_in_mytest | +------------------+ | aaa | | customer | | mytable | | sys_tests | | test | | test1 | | test_isam | +------------------+ 7 rows in set (0.01 sec) 這時我們發(fā)現(xiàn)新創(chuàng)建的測試表丟失,我們將應(yīng)用增量備份來恢復(fù)最新數(shù)據(jù) C:Program FilesMySQLMySQL Server 5.0in>mysqlbinlog -uroot -p E:MySqlDatamochasof-8ed6b1-bin.000003 | mysql -uTEST -p mytest Enter password: Enter password: ****** **** C:Program FilesMySQLMySQL Server 5.0in>mysql -uTEST -p mytest Enter password: **** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 24 to server version: 5.0.27-community-log Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> SHOW TABLES; +------------------+ | Tables_in_mytest | +------------------+ | aaa | | customer | | mytable | | new_table | | sys_tests | | test | | test1 | | test_isam | +------------------+ 8 rows in set (0.02 sec) mysql> SELECT * FROM NEW_TABLE; +------+------+ | ID | MC | +------+------+ | 1 | 111 | +------+------+ 1 row in set (0.00 sec) 下面就介紹一下這幾個目錄。 1、數(shù)據(jù)庫目錄 /var/lib/mysql/ 2、配置文件 /usr/share/mysql(mysql.server命令及配置文件) 3、相關(guān)命令 /usr/bin(mysqladmin mysqldump等命令) 4、啟動腳本 /etc/rc.d/init.d/(啟動腳本文件mysql的目錄) 五、修改登錄密碼 MySQL默認(rèn)沒有密碼，安裝完畢增加密碼的重要性是不言而喻的。 1、命令 usr/bin/mysqladmin -u root password 'new-password' 格式:mysqladmin -u用戶名 -p舊密碼 password 新密碼 2、例子 例1:給root加個密碼123456。 鍵入以下命令 : [root@test1 local]# /usr/bin/mysqladmin -u root password 123456 注:因?yàn)殚_始時root沒有密碼，所以-p舊密碼一項就可以省略了。 3、測試是否修改成功 1)不用密碼登錄 [root@test1 local]# mysql ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) 顯示錯誤，說明密碼已經(jīng)修改。 2)用修改后的密碼登錄 [root@test1 local]# mysql -u root -p Enter password: (輸入修改后的密碼123456) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 4.0.16-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> 成功～ 這是通過mysqladmin命令修改口令，也可通過修改庫來更改口令。 六、啟動與停止 1、啟動 MySQL安裝完成后啟動文件mysql在/etc/init.d目錄下，在需要啟動時運(yùn)行下面命令 即可。 [root@test1 init.d]# /etc/init.d/mysql start mysqladmin -u root password root 2、停止 /usr/bin/mysqladmin -u root -p shutdown 3、自動啟動 1)察看mysql是否在自動啟動列表中 [root@test1 local]# /sbin/chkconfig –list 2)把MySQL添加到你系統(tǒng)的啟動服務(wù)組里面去 [root@test1 local]# /sbin/chkconfig – add mysql 3)把MySQL從啟動服務(wù)組里面刪除。 [root@test1 local]# /sbin/chkconfig – del mysql 七、更改MySQL目錄 MySQL默認(rèn)的數(shù)據(jù)文件存儲目錄為/var/lib/mysql。假如要把目錄移到/home/data下 需要進(jìn)行下面幾步: 1、home目錄下建立data目錄 cd /home mkdir data 2、把MySQL服務(wù)進(jìn)程停掉: mysqladmin -u root -p shutdown 3、把/var/lib/mysql整個目錄移到/home/data mv /var/lib/mysql /home/data/ 這樣就把MySQL的數(shù)據(jù)文件移動到了/home/data/mysql下 4、找到my.cnf配置文件 如果/etc/目錄下沒有my.cnf配置文件，請到/usr/share/mysql/下找到*.cnf文件， 拷貝其中一個到/etc/并改名為my.cnf)中。命令如下: [root@test1 mysql]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf 5、編輯MySQL的配置文件/etc/my.cnf 為保證MySQL能夠正常工作，需要指明mysql.sock文件的產(chǎn)生位置。 修改 socket=/var/lib/mysql/mysql.sock一行中等號右邊的值為:/home/mysql/mysql.sock 。操作如下: vimy.cnf (用vi工具編輯my.cnf文件，找到下列數(shù)據(jù)修改之) # The MySQL server [mysqld] port = 3306 #socket= /var/lib/mysql/mysql.sock(原內(nèi)容，為了更穩(wěn)妥用“#”注釋此行) socket= /home/data/mysql/mysql.sock (加上此行) 6、修改MySQL啟動腳本/etc/rc.d/init.d/mysql 最后，需要修改MySQL啟動腳本/etc/rc.d/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等號右邊的路徑改成你現(xiàn)在的實(shí)際存放路徑:home/data/mysql。 [root@test1 etc]# vi /etc/rc.d/init.d/mysql #datadir=/var/lib/mysql (注釋此行) datadir=/home/data/mysql (加上此行) 7、重新啟動MySQL服務(wù) /etc/rc.d/init.d/mysql start 或用reboot命令重啟Linux 如果工作正常移動就成功了，否則對照前面的7步再檢查一下。 八、MySQL的常用操作 注意:MySQL中每個命令后都要以分號;結(jié)尾。 1、顯示數(shù)據(jù)庫 mysql> show databases; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.04 sec) Mysql剛安裝完有兩個數(shù)據(jù)庫:mysql和test。mysql庫非常重要，它里面有MySQL的系 統(tǒng)信息，我們改密碼和新增用戶，實(shí)際上就是用這個庫中的相關(guān)表進(jìn)行操作。 2、顯示數(shù)據(jù)庫中的表 mysql> use mysql; (打開庫，對每個庫進(jìn)行操作就要打開此庫，類似于foxpro ) Database changed mysql> show tables; +-----------------+ | Tables_in_mysql | +-----------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +-----------------+ 6 rows in set (0.01 sec) 3、顯示數(shù)據(jù)表的結(jié)構(gòu): describe 表名; 4、顯示表中的記錄: select * from 表名; 例如:顯示mysql庫中user表中的紀(jì)錄。所有能對MySQL用戶操作的用戶都在此表中。 Select * from user; 5、建庫: create database 庫名; 例如:創(chuàng)建一個名字位aaa的庫 mysql> create databases aaa; 6、建表: use 庫名; create table 表名 (字段設(shè)定列表); 例如:在剛創(chuàng)建的aaa庫中建立表name,表中有id(序號，自動增長)，xm(姓名),xb(性 別),csny(出身年月)四個字段 use aaa; mysql> create table name (id int(3) auto_increment not null primary key, xm char(8),xb char(2),csny date); 可以用describe命令察看剛建立的表結(jié)構(gòu)。 mysql> describe name; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(3) | | PRI | NULL | auto_increment | | xm | char(8) | YES | | NULL | | | xb | char(2) | YES | | NULL | | | csny | date | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 7、增加記錄 例如:增加幾條相關(guān)紀(jì)錄。 mysql> insert into name values('','張三','男','1971-10-01'); mysql> insert into name values('','白云','女','1972-05-20'); 可用select命令來驗(yàn)證結(jié)果。 mysql> select * from name; +----+------+------+------------+ | id | xm| xb| csny | +----+------+------+------------+ | 1 | 張三 | 男| 1971-10-01 | | 2 | 白云 | 女| 1972-05-20 | +----+------+------+------------+ 8、修改紀(jì)錄 例如:將張三的出生年月改為1971-01-10 mysql> update name set csny='1971-01-10' where xm='張三'; 9、刪除紀(jì)錄 例如:刪除張三的紀(jì)錄。 mysql> delete from name where xm='張三'; 10、刪庫和刪表 drop database 庫名; drop table 表名; 九、增加MySQL用戶 格式:grant select on 數(shù)據(jù)庫.* to 用戶名@登錄主機(jī) identified by "密碼" 例1、增加一個用戶user_1密碼為123，讓他可以在任何主機(jī)上登錄，并對所有數(shù)據(jù)庫有查詢、插入、修改、刪除的權(quán)限。首先用以root用戶連入MySQL，然后鍵入以下命令: mysql> grant select,insert,update,delete on *.* to user_1@"%" Identified by "123"; 例1增加的用戶是十分危險的，如果知道了user_1的密碼，那么他就可以在網(wǎng)上的任何一臺電腦上登錄你的MySQL數(shù)據(jù)庫并對你的數(shù)據(jù)為所欲為了，解決辦法見例2。 例2、增加一個用戶user_2密碼為123,讓此用戶只可以在localhost上登錄，并可以對數(shù)據(jù)庫aaa進(jìn)行查詢、插入、修改、刪除的操作(localhost指本地主機(jī)，即MySQL數(shù)據(jù)庫所在的那臺主機(jī))，這樣用戶即使用知道user_2的密碼，他也無法從網(wǎng)上直接訪問數(shù)據(jù)庫，只能通過 MYSQL主機(jī)來操作aaa庫。 mysql>grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123"; 用新增的用戶如果登錄不了MySQL，在登錄時用如下命令: mysql -u user_1 -p -h 192.168.113.50 (-h后跟的是要登錄主機(jī)的ip地址) 十、備份與恢復(fù) 1、備份 例如:將上例創(chuàng)建的aaa庫備份到文件back_aaa中 [root@test1 root]# cd /home/data/mysql (進(jìn)入到庫目錄，本例庫已由 val/lib/mysql轉(zhuǎn)到/home/data/mysql，見上述第七部分內(nèi)容) [root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa 2、恢復(fù) [root@test mysql]# mysql -u root -p ccc < back_aaa 修改數(shù)據(jù)庫的字符集 mysql>use mydb mysql>alter database mydb character set utf-8; 創(chuàng)建數(shù)據(jù)庫指定數(shù)據(jù)庫的字符集 mysql>create database mydb character set utf-8; 通過配置文件修改: 修改/var/lib/mysql/mydb/db.opt default-character-set=latin1 default-collation=latin1_swedish_ci 為 default-character-set=utf8 default-collation=utf8_general_ci 重起MySQL: [root@bogon ~]# /etc/rc.d/init.d/mysql restart 查看Mysql 的相關(guān)編碼類型; mysql> show variables like 'character\_set\_%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | utf8 | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | +--------------------------+-------+ 通過MySQL命令行修改: set character_set_client=utf8; set character_set_connection=utf8; set character_set_database=utf8; set character_set_results=utf8; set character_set_server=utf8; set character_set_system=utf8; set character_set_filesystem=utf8; set collation_connection=utf8; set collation_database=utf8; set collation_server=utf8; set character_set_filesystem=utf-8; 8 rows in set (0.03 sec) mysql> show variables like 'collation_%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.04 sec) [root@localhost /]#service mysql stop; [root@localhost /]#service mysql start; 1、準(zhǔn)備安裝程序(官方網(wǎng)站下載) 服務(wù)端:MySQL-server-community-5.1.44-1.rhel4.i386.rpm 客戶端:MySQL-client-community-5.1.44-1.rhel4.i386.rpm 2、安裝(打印信息略) [root@localhost /]#rpm -ivm MySQL-server-community-5.1.44-1.rhel4.i386.rpm; [root@localhost /]#rpm -ivm MySQL-client-community-5.1.44-1.rhel4.i386.rpm; 3、查看安裝是否成功 [root@localhost /]#netstat -ntpl; 顯示3306端口開放表示服務(wù)安裝成功 4、測試連接 [root@localhost /]#mysql 出現(xiàn)mysql>表示登陸成功 5、對mysql用戶設(shè)置遠(yuǎn)程訪問權(quán)限 方法1、改表法:登陸mysql后，更改“mysql”數(shù)據(jù)庫里的“user”表里的“host”項，將“l(fā)ocalhost”改“%” mysql>use mysql; mysql>update user set host ='%' where user ='root'; mysql>select host,user from user; mysql>FLUSH PRIVILEGES 方法2、授權(quán)法:假設(shè)允許用戶username通過密碼password從遠(yuǎn)程連接到mysql服務(wù)器 mysql>GRANT ALL RRIVILEGES ON *.* TO username@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; mysql>FLUSH PRIVILEGES; 6、安裝目錄結(jié)構(gòu) 數(shù)據(jù)庫目錄:/var/lib/mysql/ 配置文件:/usr/share/mysql(mysql.server命令及配置文件) 相關(guān)命令:/usr/bin(mysqladmin、mysqldump等命令)(*mysql的一種安全啟動方式:/usr/bin/mysqld_safe --user=root &) 啟動腳本:/etc/rc.d/init.d/ 7、數(shù)據(jù)庫目錄轉(zhuǎn)移(未實(shí)驗(yàn)過) 在根目錄下新建文件夾 [root@localhost /]#mkdir mysqldata 停止MYSQL服務(wù)器 service mysql stop 復(fù)制/var/lib/mysql下所有文件到/mysqldata 查看原數(shù)據(jù)庫文件夾下各文件權(quán)限默認(rèn)為mysql 更改/mysqldata下各文件權(quán)限 [root@localhost /]#chgrp -R mysql ./mysqldata && chown -R mysql ./mysqldata 檢查權(quán)限 [root@localhost /]#ls -l 配置my.cnf文件 復(fù)制并更改文件到/etc目錄下 [root@localhost mysql]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf 修改my.cnf #The MySQL server [client] #password = your_password port = 3306 #socket = /var/lib/mysql/mysql.sock socket =/mysqldata/mysql.sock --增加此行，并配置數(shù)據(jù)庫目錄 [mysqld] port = 3306 #socket = /var/lib/mysql/mysql.sock socket =/mysqldata/mysql.sock --增加此行，并配置數(shù)據(jù)庫目錄 修改啟動文件 修改/etc/rc.d/init.d/mysql 找到datadir位置，修改內(nèi)容如下 #Set some defaults pid_file= server_pid_file= use_mysqld_safe=1 user=mysql if test -z "$basedir" then basedir=/ bindir=/usr/bin if test -z "$datadir" then #datadir=/var/lib/mysql --修改前內(nèi)容 datadir=/mysqldata --修改后內(nèi)容 fi sbindir=/usr/sbin 8、停止/啟動mysql服務(wù) [root@localhost /]#service mysql stop; [root@localhost /]#service mysql start; 9、修改字符編碼 查看字符編碼: [root@localhost /]#show variables like 'character\_set\_%'; [root@localhost /]#show variables like 'collation_%'; 停止mysql服務(wù) 將目錄/usr/share/mysql下的文件my-medium.cnf拷貝到/etc/下并改名為my.cnf 打開my.cnf在[client]和[mysqld]下面均加上default-character-set=utf8,并保存 重啟mysql服務(wù)
信息發(fā)布：廣州名易軟件有限公司 http://m.jetlc.com
|
名易MyOA協(xié)同綜合辦公平臺
名易MyCRM客戶關(guān)系管理平臺
名易MyHR人力資源管理平臺
名易MyIDP智能開發(fā)平臺
名易MyIBP保險業(yè)務(wù)管理平臺
名易MyHMS酒店綜合管理系統(tǒng)
名易MyPM項目管理平臺
名易MyTMS物流運(yùn)輸管理平臺
名易MyVMS汽車綜合管理系統(tǒng)
名易MyIMS貸款管理系統(tǒng)
名易MyEDU教育管理平臺
名易MyPCS生產(chǎn)事務(wù)協(xié)調(diào)系統(tǒng)
名易MyWIDP微信智能開發(fā)平臺
