本文详细介绍了在macOS环境下使用MySQL8.0.23进行数据库管理的基本操作，包括登录、查看版本、显示数据库、切换数据库、查看表、显示表结构、查看系统变量等关键步骤，帮助用户熟悉MySQL命令行工具的使用。
电脑系统：macOS
软件：mysql 8.0.23
注：mysql语法对关键字的大小写要求是都可以使用，关键字可以用全部大写，可以全部小写，建议关键字全部大写，建议表名、列名全部小写。
1、登录数据库命令 /usr/local/MySQL/bin/mysql -u root -p
MacBook-Air ~ % /usr/local/MySQL/bin/mysql -u root -p之后输入root的登录密码即可。
2、查看mysql版本命令 ：select vesion();
select version();3、显示所有数据库：show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| book | |
| information_schema |
| jd_demo |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)4、进入指定的数据库：use database_name;
例如进入数据库名字是sys的数据库：
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 changed5、显示具体数据库中的所有表：show tables;
显示sys数据库中所有表：
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys |
+-----------------------------------------------+
| host_summary |
| host_summary_by_file_io |
...
| x$wait_classes_global_by_latency |
| x$waits_by_host_by_latency |
| x$waits_by_user_by_latency |
| x$waits_global_by_latency |
+-----------------------------------------------+
101 rows in set (0.00 sec)注：想要显示某个数据库中的所有表，需要先进入该数据库中。
6、查看其他数据库中所有表：show tables from databse_name;
例如，我们进入了非mysql库，想查询mysql库中所有表，可以使用下面语句：
mysql> show tables from mysql;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
...
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
35 rows in set (0.00 sec)7、查看表的创建语句：show create table table_name;
例如，我们现在使用use mysql先进入数据库名为mysql的数据库中，然后查看其中表名为func的创建语句：
mysql> show create table func;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| func | CREATE TABLE `func` (
`name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`ret` tinyint NOT NULL DEFAULT '0',
`dl` char(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`type` enum('function','aggregate') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`name`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='User defined functions' |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)8、查看表的结构：desc table_name;
例如，我们现在在数据库中名字为mysql的数据库中，查看func表的表结构：
mysql> desc func;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(64) | NO | PRI | | |
| ret | tinyint | NO | | 0 | |
| dl | char(128) | NO | | | |
| type | enum('function','aggregate') | NO | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)9、查看当前所在库：select database();
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)通过上面的查询，我们可以发现，当前所在的数据库名称为mysql。
10、查看当前mysql支持的存储引擎：show engines;
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)11、查看系统变量及其值：show variables;
mysql> show variables;
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| activate_all_roles_on_login | OFF |
| admin_address | |
| admin_port | 33062 |
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key |
...
| version_compile_zlib | 1.2.11 |
| wait_timeout | 28800 |
| warning_count | 0 |
| windowing_use_high_precision | ON |
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
608 rows in set (0.03 sec)12、查看某个系统变量：show variables like 'variable_name';
例如查看waring_count变量：
mysql> show variables like 'warning_count';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| warning_count | 0 |
+---------------+-------+
1 row in set (0.01 sec)
扫一扫
1975
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
