mysql常用命令

mysql常用命令

查看当前数据库的版本

//在客户端内部查看客户端的版本命令
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.20    |
+-----------+
1 row in set (0.00 sec)

//不在客户端内部查看版本的方式
[root@kk01 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86_64) using  EditLine wrapper

显示都有哪些数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

打开指定的数据库

mysql> use sys;
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> show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| innodb_lock_waits                             |
| io_by_thread_by_latency                       |
| io_global_by_file_by_bytes                    |
| io_global_by_file_by_latency                  |
| io_global_by_wait_by_bytes                    |
| io_global_by_wait_by_latency                  |
| latest_file_io                                |
| memory_by_host_by_current_bytes               |
| memory_by_thread_by_current_bytes             |

在一个库里想看其它数据库里的表命令

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| sys        |
+------------+
1 row in set (0.00 sec)

mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |

查看自己所在的库

mysql> select database();
+------------+
| database() |
+------------+
| sys        |
+------------+
1 row in set (0.00 sec)

数据库表的操作命令

创建表

mysql> create table he(
    -> ip int,
    -> name varchar(20));
Query OK, 0 rows affected (0.01 sec)

查看表的结构

mysql> desc he;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ip    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

表内插入数据

mysql> insert into he (ip,name) values(1,'Beijing');
Query OK, 1 row affected (0.10 sec)

mysql> insert into he (ip,name) values(2,'Shanghai');
Query OK, 1 row affected (0.00 sec)

mysql> select * from he;
+------+----------+
| ip   | name     |
+------+----------+
|    1 | Beijing  |
|    2 | Shanghai |
+------+----------+
2 rows in set (0.00 sec)

查看表里的数据

mysql> select * from he;
+------+----------+
| ip   | name     |
+------+----------+
|    1 | Beijing  |
|    2 | Shanghai |
+------+----------+
2 rows in set (0.00 sec)

对表内数据进行修改

mysql> select * from he;
+------+----------+
| ip   | name     |
+------+----------+
|    1 | Beijing  |
|    2 | Shanghai |
+------+----------+
2 rows in set (0.00 sec)

mysql> update he set name='Changzhou' where ip=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update he set name='Changji' where ip=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from he;
+------+-----------+
| ip   | name      |
+------+-----------+
|    1 | Changji   |
|    2 | Changzhou |
+------+-----------+
2 rows in set (0.00 sec)

删除表内的数据

mysql> select * from he;
+------+-----------+
| ip   | name      |
+------+-----------+
|    1 | Changji   |
|    2 | Changzhou |
|    3 | QQ        |
|    4 | OO        |
+------+-----------+
4 rows in set (0.00 sec)

mysql> delete from he where ip=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from he;
+------+-----------+
| ip   | name      |
+------+-----------+
|    1 | Changji   |
|    2 | Changzhou |
|    3 | QQ        |
+------+-----------+
3 rows in set (0.00 sec)