MySQL数据库连接与管理（四）

使用Linux连接mysql服务器

从命令行中本地连接mysql服务器

[root@hhost]# mysql -u root -p sh

远程登陆MySQL

[root@hhost]# mysql -h 服务器IP -u root

登录成功后会出现 mysql> 命令提示窗口

显示所有数据库，注意后面的；

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.10 sec)

连接数据库

use 数据库名

查看表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| runoob_tbl     |
| sys_menu       |
+----------------+
2 rows in set (0.00 sec)

查表所有内容

select * from  表名;

显示表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息

show columns from 数据表;

mysql> show columns from runoob_tbl;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| runoob_id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| runoob_title    | varchar(100)     | NO   |     | NULL    |                |
| runoob_author   | varchar(40)      | NO   |     | NULL    |                |
| submission_date | date             | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+
4 rows in set (0.06 sec)

显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

show index from runoob_tbl;

mysql> show index from runoob_tbl;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| runoob_tbl |          0 | PRIMARY  |            1 | runoob_id   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

显示数据库test中的所有表信息

show table status from 库名;

mysql> show table status from test;
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| runoob_tbl | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2019-06-05 14:57:14 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| sys_menu   | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2019-06-03 09:54:08 | NULL        | NULL       | utf8_general_ci |     NULL |                | 菜单    |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.00 sec)

表名以runoob开头的表的信息

show table status from 库名 like '表名%';

mysql> show table status from test like 'runoob%';
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| runoob_tbl | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2019-06-05 14:57:14 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

加上 \G，查询结果按列打印

show table status from 库名 like '表名%'\G;

mysql> show table status from test like 'runoob%'\G;
*************************** 1. row ***************************
           Name: runoob_tbl
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-06-05 14:57:14
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified