mysql常用操作命令

====================================================================================
列出数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.03 sec)
选择数据库
mysql> use test;
Database changed
列出表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mytable        |
+----------------+
1 row in set (0.00 sec)
列出表结构
mysql> describe mytable;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int(4)      | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)
列出表内容
mysql> select * from mytable;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | aaa  |   22 |


|  9 | ggg  |   20 |
+----+------+------+
8 rows in set (0.00 sec)
====================================================================================
创建数据库
mysql> create   database db_test;
Query OK, 1 row affected (0.03 sec)
选择数据库
mysql> use db_test
Database changed
删除数据库
mysql> mysql> drop database db_test;
Query OK, 1 row affected (0.06 sec)
Query OK, 0 rows affected (0.17 sec)
====================================================================================
在表中插入数据
mysql> insert into table_test values (1,"abc");
Query OK, 1 row affected (0.03 sec)
mysql> insert into table_test values (3,"xyz");
Query OK, 1 row affected (0.03 sec)
mysql> insert into table_test values (2,"ddd");
Query OK, 1 row affected (0.03 sec)
清空表内容
mysql> delete from table_test;
Query OK, 3 rows affected (0.05 sec)
删除表
mysql> drop table table_test;
Query OK, 0 rows affected (0.02 sec)
====================================================================================
选择数据库
mysql> use test;
Database changed
显示选中的数据库
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
====================================================================================
备份数据库中test数据表。
G:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump --opt -u root -p123 test > g:\test_back.sql
====================================================================================
查找前3个数据
mysql> select * from mytable limit 3;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | aaa  |   22 |
|  2 | xxx  |   26 |
|  4 | bbb  |   13 |
+----+------+------+
3 rows in set (0.00 sec)
排序并显示前3个数据
mysql> select * from mytable order by age limit 3;
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | bbb  |   13 |
|  5 | ccc  |   14 |
|  6 | ddd  |   15 |
+----+------+------+

3 rows in set (0.00 sec)

====================================================================================
查看mysql连接数，状态，变量。
mysql> show processlist;
+----+------+----------------+---------+---------+------+-------------------+---
-----------------------------------------------+
| Id | User | Host           | db      | Command | Time | State             | In
fo                                             |
+----+------+----------------+---------+---------+------+-------------------+---
-----------------------------------------------+
| 30 | root | localhost:3142 | db_test | Query   | 8918 | copy to tmp table | cr
eate index birthdayIndex on tb_test (birthday) |
| 33 | root | localhost:1691 | NULL    | Query   |    0 | NULL              | sh
ow processlist                                 |
+----+------+----------------+---------+---------+------+-------------------+---
-----------------------------------------------+
2 rows in set (0.00 sec)


mysql> show status like "open%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Open_files               | 0     |
| Open_streams             | 0     |
| Open_table_definitions   | 23    |
| Open_tables              | 3     |
| Opened_files             | 238   |
| Opened_table_definitions | 0     |
| Opened_tables            | 0     |
+--------------------------+-------+
7 rows in set (0.00 sec)


mysql> show variables like "ver%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.1.40-community             |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | ia32                         |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)
====================================================================================
表中数据条数
mysql> select count(id) from tb_test;
+-----------+
| count(id) |
+-----------+
|  10000000 |
+-----------+
1 row in set (11.25 sec)