mysql非常有用的几个命令
?
mysql> use test;Database changedmysql> select @@version; +-----------+| @@version |+-----------+| 5.5.15 |+-----------+1 row in set (0.00 sec)mysql> select @@sql_mode;+----------------------------+| @@sql_mode |+----------------------------+| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+----------------------------+1 row in set (0.00 sec)mysql> show column from mytable;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column from mytable' at line 1mysql> show columns from mytable;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| a | int(11) | NO | PRI | NULL | || b | char(18) | NO | | NULL | |+-------+----------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> use mysql;Database changedmysql> show index from db;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| db | 0 | PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | || db | 0 | PRIMARY | 2 | Db | A | NULL | NULL | NULL | | BTREE | | || db | 0 | PRIMARY | 3 | User | A | 0 | NULL | NULL | | BTREE | | || db | 1 | User | 1 | User | A | 0 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec)mysql> show privilges;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'privilges' at line 1mysql> show create table user;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Table | Create Table |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| user | CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT '0', `max_updates` int(11) unsigned NOT NULL DEFAULT '0', `max_connections` int(11) unsigned NOT NULL DEFAULT '0', `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0', `plugin` char(64) COLLATE utf8_bin DEFAULT '', `authentication_string` text COLLATE utf8_bin, PRIMARY KEY (`Host`,`User`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)mysql> show privileges;+-------------------------+---------------------------------------+-------------------------------------------------------+| Privilege | Context | Comment |+-------------------------+---------------------------------------+-------------------------------------------------------+| Alter | Tables | To alter the table || Alter routine | Functions,Procedures | To alter or drop stored functions/procedures || Create | Databases,Tables,Indexes | To create new databases and tables || Create routine | Databases | To use CREATE FUNCTION/PROCEDURE || Create temporary tables | Databases | To use CREATE TEMPORARY TABLE || Create view | Tables | To create new views || Create user | Server Admin | To create new users || Delete | Tables | To delete existing rows || Drop | Databases,Tables | To drop databases, tables, and views || Event | Server Admin | To create, alter, drop and execute events || Execute | Functions,Procedures | To execute stored routines || File | File access on server | To read and write files on the server || Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess || Index | Tables | To create or drop indexes || Insert | Tables | To insert data into tables || Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) || Process | Server Admin | To view the plain text of currently executing queries || Proxy | Server Admin | To make proxy user possible || References | Databases,Tables | To have references on tables || Reload | Server Admin | To reload or refresh tables, logs and privileges || Replication client | Server Admin | To ask where the slave or master servers are || Replication slave | Server Admin | To read binary log events from the master || Select | Tables | To retrieve rows from table || Show databases | Server Admin | To see all databases with SHOW DATABASES || Show view | Tables | To see views with SHOW CREATE VIEW || Shutdown | Server Admin | To shut down the server || Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. || Trigger | Tables | To use triggers || Create tablespace | Server Admin | To create/alter/drop tablespaces || Update | Tables | To update existing rows || Usage | Server Admin | No privileges - allow connect only |+-------------------------+---------------------------------------+-------------------------------------------------------+31 rows in set (0.00 sec)mysql>