牛客网-mysql（二）mysql基本管理

第三节 mysql基本管理

进入linux的数据库

• mysql -uroot -p

展示数据库

• SHOW DATABASES;
  mysql> SHOW DATABASES;
  +--------------------+
  | Database |
  +--------------------+
  | information_schema |
  | mysql |
  | performance_schema |
  | sys |
  +--------------------+

USE 数据库名;

• 选择要操作的数据库，使用该命令后所有Mysql命令都只针对该数据库。
  mysql> USE mysql

进入选定的数据库后,显示所有表.

使用该命令前需要使用 use 命令来选择要操作的数据库。

• SHOW TABLES;
  mysql> SHOW TABLES;
  +------------------------------------------------------+
  | Tables_in_mysql |
  +------------------------------------------------------+
  | columns_priv |
  | component |
  | db |
  | default_roles |
  | engine_cost |
  | func |
  | general_log |
  | global_grants |
  | gtid_executed |
  | help_category |
  | help_keyword |
  | help_relation |
  | help_topic |
  | innodb_index_stats |
  | innodb_table_stats |
  | password_history |
  | plugin |
  | procs_priv |
  | proxies_priv |
  | replication_asynchronous_connection_failover |
  | replication_asynchronous_connection_failover_managed |
  | replication_group_configuration_version |
  | replication_group_member_actions |
  | role_edges |
  | server_cost |
  | 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 |
  +------------------------------------------------------+
  37 rows in set (0.00 sec)

显示数据表的属性

属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

• SHOW COLUMNS FROM 数据表;
  mysql> SHOW COLUMNS FROM user;
  +--------------------------+-----------------------------------+------+-----+-----------------------+-------+
  | Field | Type | Null | Key | Default | Extra |
  +--------------------------+-----------------------------------+------+-----+-----------------------+-------+
  | Host | char(255) | NO | PRI | | |
  | User | char(32) | NO | PRI | | |
  | Select_priv | enum('N','Y') | NO | | N | |
  | Insert_priv | enum('N','Y') | NO | | N | |
  | Update_priv | enum('N','Y') | NO | | N | |
  | Delete_priv | enum('N','Y') | NO | | N | |
  | Create_priv | enum('N','Y') | NO | | N | |
  | Drop_priv | enum('N','Y') | NO | | N | |
  | Reload_priv | enum('N','Y') | NO | | N | |
  | Shutdown_priv | enum('N','Y') | NO | | N | |
  | Process_priv | enum('N','Y') | NO | | N | |
  | File_priv | enum('N','Y') | NO | | N | |
  | Grant_priv | enum('N','Y') | NO | | N | |
  | References_priv | enum('N','Y') | NO | | N | |
  | Index_priv | enum('N','Y') | NO | | N | |
  | Alter_priv | enum('N','Y') | NO | | N | |
  | Show_db_priv | enum('N','Y') | NO | | N | |
  | Super_priv | enum('N','Y') | NO | | N | |
  | Create_tmp_table_priv | enum('N','Y') | NO | | N | |
  | Lock_tables_priv | enum('N','Y') | NO | | N | |
  | Execute_priv | enum('N','Y') | NO | | N | |
  | Repl_slave_priv | enum('N','Y') | NO | | N | |
  | Repl_client_priv | enum('N','Y') | NO | | N | |
  | Create_view_priv | enum('N','Y') | NO | | N | |
  | Show_view_priv | enum('N','Y') | NO | | N | |
  | Create_routine_priv | enum('N','Y') | NO | | N | |
  | Alter_routine_priv | enum('N','Y') | NO | | N | |
  | Create_user_priv | enum('N','Y') | NO | | N | |
  | Event_priv | enum('N','Y') | NO | | N | |
  | Trigger_priv | enum('N','Y') | NO | | N | |
  | Create_tablespace_priv | enum('N','Y') | NO | | N | |
  | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
  | ssl_cipher | blob | NO | | NULL | |
  | x509_issuer | blob | NO | | NULL | |
  | x509_subject | blob | NO | | NULL | |
  | max_questions | int unsigned | NO | | 0 | |
  | max_updates | int unsigned | NO | | 0 | |
  | max_connections | int unsigned | NO | | 0 | |
  | max_user_connections | int unsigned | NO | | 0 | |
  | plugin | char(64) | NO | | caching_sha2_password | |
  | authentication_string | text | YES | | NULL | |
  | password_expired | enum('N','Y') | NO | | N | |
  | password_last_changed | timestamp | YES | | NULL | |
  | password_lifetime | smallint unsigned | YES | | NULL | |
  | account_locked | enum('N','Y') | NO | | N | |
  | Create_role_priv | enum('N','Y') | NO | | N | |
  | Drop_role_priv | enum('N','Y') | NO | | N | |
  | Password_reuse_history | smallint unsigned | YES | | NULL | |
  | Password_reuse_time | smallint unsigned | YES | | NULL | |
  | Password_require_current | enum('N','Y') | YES | | NULL | |
  | User_attributes | json | YES | | NULL | |
  +--------------------------+-----------------------------------+------+-----+-----------------------+-------+
  51 rows in set (0.00 sec)

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

• SHOW INDEX FROM 数据表;

该命令将输出Mysql数据库管理系统的性能及统计信息。

• mysql> SHOW table status from 数据库名;