mysql 15 命令_mysql常用命令

1.查询库有哪些数据库 show databases;

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00 sec)

2.切换库 use mysql;

mysql> use mysql;

Database changed

3.查看库里的表 show tables;

mysql> show tables;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| event |

| func |

| general_log

4.查看表里的字段 desc user;

mysql> desc user;

+------------------------+-----------------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------------

5.查看建表语句 show create table tb_name\G;

mysql> show create table user\G; // \G竖排显示

1. row

Table: user

Create Table: 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‘,

6.查看当前用户 select user();

mysql> select user();

+----------------+

| user() |

+----------------+

| [email protected] |//就是ip是127.0.0.1

+----------------+

1 row in set (0.54 sec)

7.记录mysql的历史命令

[[email protected] ~]# pwd

/root

[[email protected] ~]# ll -a .mysql_history

-rw------- 1 root root 994 12月 25 08:25 .mysql_history

8.查看当前使用的数据库 select databsase();

mysql> use mysql;

Database changed

mysql> select database();

+------------+

| database() |

+------------+

| mysql |

+------------+

1 row in set (0.00 sec)

9.创建库 create database db1;

mysql> create database db1;

Query OK, 1 row affected (0.57 sec)

use db1;

mysql> use db1; //切换到db1

Database changed

10.创建表 create table t1(id int(4), name char(40));

mysql> create table t1(id int(4),name char(40)); //创建表

Query OK, 0 rows affected (0.38 sec)

11.mysql> show create table t1\G; //查看创建表的语句是什么

1. row

Table: t1

Create Table: CREATE TABLE t1 (

id int(4) DEFAULT NULL,

name char(40) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 //默认InnoDB引擎 字符集是Latin1

1 row in set (0.57 sec)

12.mysql> create table t1(id int(4),name char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; //创建表时自定义字符集

Query OK, 0 rows affected (0.55 sec)

mysql> drop table t1; //删除表t1

Query OK, 0 rows affected (0.64 sec)

13.查看当前数据库版本 select version();

14.查看数据库状态 show status;

15.查看各参数(如my.cnf) show variables;

show variables like ‘max_connect%‘; //%通配 查看指定的参数

show variables like ‘max_connect%‘;

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| max_connect_errors | 10 |

| max_connections | 151 |

+--------------------+-------+

2 rows in set (0.00 sec)

16.命令行修改参数 set global max_connect_errors=1000; //内存中生效。如果想要它重启还是1000需要改配置文件vi /etc/my.cnf

17.查看队列 show processlist; //相当于linux ps或者top

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+------+---------+------+-------+------------------+

| 11 | root | localhost | NULL | Query | 0 | NULL | show processlist |

+----+------+-----------+------+---------+------+-------+------------------+

1 row in set (0.00 sec)

show full processlist; //最后一列非常完整。如哪些用户在连接

mysql> show full processlist;

+----+------+-----------+------+---------+------+-------+-----------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+------+---------+------+-------+-----------------------+

| 11 | root | localhost | NULL | Query | 0 | NULL | show full processlist |

+----+------+-----------+------+---------+------+-------+-----------------------+

1 row in set (0.00 sec)

原文：http://blog.51cto.com/lsxme/2061002