mysql常用命令

常用命令

1.创建库
mysql> create database xxx;
Query OK, 1 row affected (0.00 sec)

2.创建表
mysql> use xxx ; create table t1(id int(4), name char(40));
Database changed
Query OK, 0 rows affected (0.00 sec)

3.查询库：
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| xxx |
±-------------------+
4 rows in set (0.00 sec)

4.切换库
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

5.查看库里的表
mysql> show tables;
±--------------------------+
| Tables_in_mysql |
±--------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
±--------------------------+
28 rows in set (0.00 sec)

6.查看表里的字段
mysql> desc user;
±-----------------------±----------------------------------±-----±----±----------------------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------------------±----------------------------------±-----±----±----------------------±------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum(‘N’,‘Y’) | NO | | N | |

7.查看建表语句：
mysql> show create table user\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’,

8.查看当前用户
mysql> select user();
±---------------+
| user() |
±---------------+
| root@localhost |
±---------------+
1 row in set (0.00 sec)

9.查看当前使用的数据库：
mysql>
mysql> select database();
±-----------+
| database() |
±-----------+
| mysql |
±-----------+
1 row in set (0.00 sec)

10.查看当前版本
mysql> select version();
select version();
±---------------+
| version() |
±---------------+
| 5.5.68-MariaDB |
±---------------+
1 row in set (0.00 sec)

11.查看数据库状态
mysql> show status;
±----------------------------------------------±------------+
| Variable_name | Value |
±----------------------------------------------±------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |

12.查看各参数 show variables; show variables like ‘max_connect%’
mysql> show variables like ‘max_connect%’;
±-------------------±------+
| Variable_name | Value |
±-------------------±------+
| max_connect_errors | 100 |
| max_connections | 151 |
±-------------------±------+
2 rows in set (0.00 sec)

13.修改参数：
mysql> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)

14.查看列队show processlist; show full processlist;
mysql> show processlist; show full processlist;
±—±-----±----------------±-----±--------±-----±------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±----------------±-----±--------±-----±------±-----------------+
| 2 | root | localhost | NULL | Sleep | 963 | | NULL |
| 3 | root | localhost:55913 | NULL | Sleep | 949 | | NULL |
±—±-----±----------------±-----±--------±-----±------±-----------------+
4 rows in set (0.00 sec)